CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE IF NOT EXISTS "Reihe" (
	"ID"	INTEGER,
	"Titel"	TEXT NOT NULL,
	"Verlag"	TEXT,
	PRIMARY KEY("ID" AUTOINCREMENT),
	FOREIGN KEY("Verlag") REFERENCES "Verlag"("ID")
);
CREATE TABLE IF NOT EXISTS "Text_Genre" (
	"Text"	INTEGER,
	"Genre"	INTEGER,
	PRIMARY KEY("Text","Genre"),
	FOREIGN KEY("Genre") REFERENCES "Genre"("ID"),
	FOREIGN KEY("Text") REFERENCES "Text"("ID")
);
CREATE TABLE IF NOT EXISTS "Titelbild" (
	"ID"	INTEGER,
	"Mimetype"	TEXT NOT NULL,
	"Dateiname"	TEXT NOT NULL,
	"Dateigroesse"	INTEGER NOT NULL,
	"Breite"	INTEGER NOT NULL,
	"Hoehe"	INTEGER NOT NULL,
	"Bild"	BLOB NOT NULL,
	"Thumbnail"	BLOB NOT NULL,
	"sha256"	TEXT NOT NULL UNIQUE,
	PRIMARY KEY("ID" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Genre" (
	"ID"	INTEGER,
	"Genre"	TEXT NOT NULL UNIQUE,
	PRIMARY KEY("ID" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Herausgeber" (
	"ID"	INTEGER,
	"Name"	TEXT NOT NULL UNIQUE,
	PRIMARY KEY("ID" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Pseudonym" (
	"ID"	INTEGER,
	"Pseudonym"	TEXT NOT NULL UNIQUE,
	PRIMARY KEY("ID" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Sprache" (
	"ID"	INTEGER,
	"Sprache"	TEXT NOT NULL UNIQUE,
	PRIMARY KEY("ID" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Textform" (
	"ID"	INTEGER,
	"Textform"	TEXT NOT NULL UNIQUE,
	PRIMARY KEY("ID" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Verlag" (
	"ID"	INTEGER,
	"Verlag"	TEXT NOT NULL UNIQUE,
	PRIMARY KEY("ID" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Werksform" (
	"ID"	INTEGER,
	"Werksform"	TEXT NOT NULL UNIQUE,
	PRIMARY KEY("ID" AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Text" (
	"ID"	INTEGER,
	"Titel"	TEXT NOT NULL,
	"Untertitel"	TEXT,
	"Reihe"	INTEGER,
	"Textform"	INTEGER NOT NULL,
	"Sprache"	INTEGER NOT NULL,
	PRIMARY KEY("ID" AUTOINCREMENT),
	FOREIGN KEY("Reihe") REFERENCES "Reihe"("ID"),
	FOREIGN KEY("Sprache") REFERENCES "Sprache"("ID"),
	FOREIGN KEY("Textform") REFERENCES "Textform"("ID")
);
CREATE TABLE IF NOT EXISTS "Werk" (
	"ID"	INTEGER,
	"Titel"	TEXT NOT NULL,
	"Untertitel"	TEXT,
	"Werksform"	INTEGER NOT NULL,
	"Verlag"	INTEGER,
	"Reihe"	INTEGER,
	"Reihennummer"	TEXT,
	"Erscheinungsdatum"	TEXT,
	"ISBN_13"	TEXT,
	"ISBN_10"	TEXT,
	"ISSN"	TEXT,
	"Preis"	TEXT,
	"Titelbild"	INTEGER,
	"Klappentext"	TEXT,
	"Anmerkungen"	TEXT,
	PRIMARY KEY("ID" AUTOINCREMENT),
	FOREIGN KEY("Reihe") REFERENCES "Reihe"("ID"),
	FOREIGN KEY("Titelbild") REFERENCES "Titelbild"("ID"),
	FOREIGN KEY("Verlag") REFERENCES "Verlag"("ID"),
	FOREIGN KEY("Werksform") REFERENCES "Werksform"("ID")
);
CREATE TABLE IF NOT EXISTS "Werk_Genre" (
	"Werk"	INTEGER,
	"Genre"	INTEGER,
	PRIMARY KEY("Werk","Genre"),
	FOREIGN KEY("Genre") REFERENCES "Genre"("ID"),
	FOREIGN KEY("Werk") REFERENCES "Werk"("ID")
);
CREATE TABLE IF NOT EXISTS "Werk_Herausgeber" (
	"Herausgeber"	INTEGER,
	"Werk"	INTEGER,
	PRIMARY KEY("Herausgeber","Werk"),
	FOREIGN KEY("Herausgeber") REFERENCES "Herausgeber"("ID"),
	FOREIGN KEY("Werk") REFERENCES "Werk"("ID")
);
CREATE TABLE IF NOT EXISTS "Veroeffentlichung" (
	"ID"	INTEGER,
	"Text"	INTEGER NOT NULL,
	"Werk"	INTEGER NOT NULL,
	"AltTitel"	TEXT,
	"AltUntertitel"	TEXT,
	"Pseudonym"	INTEGER NOT NULL,
	PRIMARY KEY("ID" AUTOINCREMENT),
	FOREIGN KEY("Pseudonym") REFERENCES "Pseudonym"("ID"),
	FOREIGN KEY("Text") REFERENCES "Text"("ID"),
	FOREIGN KEY("Werk") REFERENCES "Werk"("ID")
);
CREATE TABLE IF NOT EXISTS "Ausgabe" (
	"ID"	INTEGER,
	"Werk"	INTEGER NOT NULL,
	"Werksform"	INTEGER NOT NULL,
	"Verlag"	INTEGER,
	"Erscheinungsdatum"	TEXT,
	"ISBN_13"	TEXT,
	"ISBN_10"	TEXT,
	"ISSN"	TEXT,
	"Preis"	TEXT,
	"Titelbild"	INTEGER,
	"Klappentext"	TEXT,
	"Anmerkungen"	TEXT,
	PRIMARY KEY("ID"),
	FOREIGN KEY("Titelbild") REFERENCES "Titelbild"("ID"),
	FOREIGN KEY("Verlag") REFERENCES "Verlag"("ID"),
	FOREIGN KEY("Werk") REFERENCES "Werk"("ID"),
	FOREIGN KEY("Werksform") REFERENCES "Werksform"("ID")
);