New cinema db

[soerenmeier]

This db might be less efficient in queries than the current one, but it is more logical and better uses the strenghts of a relation db. This adds a few new fields like description, poster, background, rating, duration, resolution, categories and more.

 entry                                                                                                       
+-------------------+--------------------------------+                                                       
| id                | unique_id                      | # primary key                                         
+-------------------+--------------------------------+                                                       
| tmdb_id           | i64?                           | # index                                               
+-------------------+--------------------------------+                                                       
| type              | 0: movie, 1: series            |                                                       
+-------------------+--------------------------------+                                                       
| name              | text                           |                                                       
+-------------------+--------------------------------+                                                       
| original_name     | text?                          |                                                       
+-------------------+--------------------------------+                                                       
| description       | text?                          |                                                       
+-------------------+--------------------------------+                                                       
| poster            | text?                          | # filename of the poster                              
+-------------------+--------------------------------+                                                       
| background        | text?                          | # filename of the background                          
+-------------------+--------------------------------+                                                       
| rating            | float?                         |                                                       
+-------------------+--------------------------------+                                                       
| duration          | i32?                           | # only filled out for movies                          
+-------------------+--------------------------------+                                                       
| first_publication | i16                            | # year                                                
+-------------------+--------------------------------+                                                       
| created_on        | datetime                       | # the time this was created in the db                 
+-------------------+--------------------------------+                                                       
| last_updated      | datetime                       | # the time for example an episode was added           
+-------------------+--------------------------------+                                                       

 season                                                # should season also have a description?              
+-------------------+--------------------------------+                                                       
| id                | unique_id                      | # primary key                                         
+-------------------+--------------------------------+                                                       
| entry_id          | unique_id                      | # index foreign key entry                             
+-------------------+--------------------------------+                                                       
| season            | i16                            | # unique entry_id                                     
+-------------------+--------------------------------+                                                       
| name              | text                           |                                                       
+-------------------+--------------------------------+                                                       
| original_name     | text?                          |                                                       
+-------------------+--------------------------------+                                                       
| created_on        | datetime                       |                                                       
+-------------------+--------------------------------+                                                       

 episode                                                                                                     
+-------------------+--------------------------------+                                                       
| id                | unique_id                      | # primary key                                         
+-------------------+--------------------------------+                                                       
| season_id         | unique_id                      | # index foreign key season                            
+-------------------+--------------------------------+                                                       
| episode           | i16                            | # unique season_id                                    
+-------------------+--------------------------------+                                                       
| name              | text                           |                                                       
+-------------------+--------------------------------+                                                       
| original_name     | text?                          |                                                       
+-------------------+--------------------------------+                                                       
| publication_year  | i16?                           |                                                       
+-------------------+--------------------------------+                                                       
| created_on        | datetime                       |                                                       
+-------------------+--------------------------------+                                                       
| description       | text                           |                                                       
+-------------------+--------------------------------+                                                       
| duration          | i32?                           | # duration in seconds might not match exactly the file
+-------------------+--------------------------------+                                                       

 media files                                                                                                 
+-------------------+--------------------------------+                                                       
| id                | unique_id                      | # primary key                                         
+-------------------+--------------------------------+                                                       
| entry_id          | unique_id?                     | # index foreign entry                                 
+-------------------+--------------------------------+                                                       
| episode_id        | unique_id?                     | # index foreign episode                               
+-------------------+--------------------------------+                                                       
| name              | text                           |                                                       
+-------------------+--------------------------------+                                                       
| size              | i32                            | # size in Bytes                                       
+-------------------+--------------------------------+                                                       
| width             | i16?                           | # size in pixel                                       
+-------------------+--------------------------------+                                                       
| height            | i16?                           | # size in pixel                                       
+-------------------+--------------------------------+                                                       
| intro_time        | i32?                           | # time in secs when the intro ends                    
+-------------------+--------------------------------+                                                       
| outro_time        | i32?                           | # time in secs when the outro starts                  
+-------------------+--------------------------------+                                                       
| created_on        | datetime                       |                                                       
+-------------------+--------------------------------+                                                       

 entry_genres      # categories are hardcoded based on tmdb                                                  
+-------------------+--------------------------------+                                                       
| entry_id          | unique_id                      | # primary key                                         
+-------------------+--------------------------------+                                                       
| genres_id         | i32                            | # index                                               
+-------------------+--------------------------------+                                                       

 progress                                                                                                    
+-------------------+--------------------------------+                                                       
| entry_id          | unique_id?                     | # primary key                                         
+-------------------+--------------------------------+                                                       
| episode_id        | unique_id?                     | # primary key                                         
+-------------------+--------------------------------+                                                       
| user_id           | unique_id                      | # primary key                                         
+-------------------+--------------------------------+                                                       
| progress          | f32                            |                                                       
+-------------------+--------------------------------+                                                       
| created_on        | datetime                       |                                                       
+-------------------+--------------------------------+                                                       
| updated_on        | datetime                       |                                                       
+-------------------+--------------------------------+                                                       
| last_watch        | datetime?                      |                                                       
+-------------------+--------------------------------+                                                       

[soerenmeier]

CREATE TABLE entry (
    id TEXT PRIMARY KEY,
    tmdb_id BIGINT,
    type SMALLINT CHECK (type IN (0, 1)),
    name TEXT NOT NULL,
    original_name TEXT,
    description TEXT,
    poster TEXT,
    background TEXT,
    rating FLOAT,
    duration INTEGER,
    first_publication SMALLINT NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_updated TIMESTAMP NOT NULL,
    INDEX (tmdb_id)
);

CREATE TABLE season (
    id TEXT PRIMARY KEY,
    entry_id TEXT NOT NULL REFERENCES entry(id),
    season SMALLINT NOT NULL,
    name TEXT NOT NULL,
    original_name TEXT,
    created_on TIMESTAMP NOT NULL,
    UNIQUE (entry_id, season)
);

CREATE TABLE episode (
    id TEXT PRIMARY KEY,
    season_id TEXT NOT NULL REFERENCES season(id),
    episode SMALLINT NOT NULL,
    name TEXT NOT NULL,
    original_name TEXT,
    publication_year SMALLINT,
    created_on TIMESTAMP NOT NULL,
    description TEXT,
    duration INTEGER,
    UNIQUE (season_id, episode)
);

CREATE TABLE media_files (
    id TEXT PRIMARY KEY,
    entry_id TEXT REFERENCES entry(id),
    episode_id TEXT REFERENCES episode(id),
    name TEXT NOT NULL,
    size INTEGER NOT NULL,
    width SMALLINT,
    height SMALLINT,
    intro_time INTEGER,
    outro_time INTEGER,
    created_on TIMESTAMP NOT NULL
);

CREATE TABLE entry_genres (
    entry_id TEXT NOT NULL REFERENCES entry(id),
    genres_id INTEGER NOT NULL,
    PRIMARY KEY (entry_id, genres_id)
);

CREATE TABLE progress (
    entry_id TEXT REFERENCES entry(id),
    episode_id TEXT REFERENCES episode(id),
    user_id TEXT NOT NULL,
    progress FLOAT NOT NULL,
    created_on TIMESTAMP NOT NULL,
    updated_on TIMESTAMP NOT NULL,
    last_watch TIMESTAMP,
    PRIMARY KEY (entry_id, episode_id, user_id)
);