TerabyteTerminator/DB/shema.sql

-- Main Tables:

CREATE TABLE File (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	Name TEXT NOT NULL,						-- bis zu 65535 Zeichen;
	Folder TEXT NOT NULL,					-- bis zu 65535 Zeichen; Pfad = $Folder/$Name
	MD5 VARCHAR(32),
	FT_ID INTEGER DEFAULT 0,
	Size INTEGER,							-- KB (max 2TB anggebbar)
	TTSync INTEGER DEFAULT 0,
	TTCheck DATE DEFAULT NULL
 );

CREATE TABLE VideoFile (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	F_ID INTEGER NOT NULL,
	M_ID INTEGER,
	Quality VARCHAR(100),
	Name VARCHAR(100),
	Duration TIME,
	Bitrate INTEGER
);

CREATE TABLE Universum (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	Name VARCHAR(100),
	Cover INTEGER,
	Description TEXT
);

CREATE TABLE Movie (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	IMDb_ID VARCHAR(100),
	U_ID INTEGER,
	Name VARCHAR(100),
	Description TEXT,
	Cover INTEGER,
	FSK INTEGER,
	Website VARCHAR(100),
	Runtime TIME
);

-- Relations, Additional Informations

CREATE TABLE F_Type (
    ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	Name TEXT NOT NULL,
	Description TEXT NOT NULL
);

CREATE TABLE M_Gren (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	M_ID INTEGER NOT NULL,
	G_ID INTEGER NOT NULL
);

CREATE TABLE M_Seasoninfo (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	F_ID INTEGER NOT NULL,
	MAL_ID INTEGER,				-- MyAnimeList ID
	Staffeln INTEGER
);

CREATE TABLE M_Title (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	F_ID INTEGER NOT NULL,
	L_ID INTEGER NOT NULL,
	Value VARCHAR(100)
);

CREATE TABLE VF_Lang (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	L_ID INTEGER NOT NULL,
	VF_ID INTEGER NOT NULL
);

CREATE TABLE VF_Sub (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	L_ID INTEGER NOT NULL,
	VF_ID INTEGER NOT NULL
);

CREATE TABLE VF_Seasoninfo (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	VF_ID INTEGER NOT NULL,
	Season INTEGER NOT NULL,
	Episode INTEGER NOT NULL
);

-- Help Tables

CREATE TABLE Gren (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	Name VARCHAR(100) NOT NULL
);

CREATE TABLE Language (
	ID INTEGER PRIMARY KEY AUTO_INCREMENT,
	TLD VARCHAR(2),
	Name VARCHAR(100) NOT NULL
);