Macaw-Movies/Development/Database schema: Difference between revisions
Created page with " == movies == {| class="wikitable" |- ! Column Name ! Type ! Foreign key |- | id | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | |- | title | VARCHAR(255) NOT NULL | |- | origi..." |
Add field series too movie |
||
| (6 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
= List of the tables = | |||
== movies == | == movies == | ||
Contains the informations of each movie. | |||
{| class="wikitable" | {| class="wikitable" | ||
| Line 11: | Line 14: | ||
| id | | id | ||
| INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | ||
| | | '''id_movie''' | ||
|- | |- | ||
| title | | title | ||
| Line 64: | Line 67: | ||
| BOOLEAN | | BOOLEAN | ||
| | | | ||
|-} | |- | ||
| series | |||
| BOOLEAN | |||
|} | |||
== peoples == | |||
Contains informations about people, without affecting any type/role. | |||
{| class="wikitable" | {| class="wikitable" | ||
| Line 77: | Line 84: | ||
| id | | id | ||
| INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | ||
| | | '''id_people''' | ||
|- | |- | ||
| name | | name | ||
| Line 90: | Line 97: | ||
| TEXT | | TEXT | ||
| | | | ||
| | |} | ||
== movies_people == | == movies_people == | ||
Bind people to movies, here is defined the type of people (director, actor, ...) | |||
{| class="wikitable" | {| class="wikitable" | ||
| Line 106: | Line 115: | ||
| id_movie | | id_movie | ||
| INTEGER NOT NULL | | INTEGER NOT NULL | ||
| | | '''id_movie''' | ||
|- | |- | ||
| id_people | | id_people | ||
| INTEGER NOT NULL | | INTEGER NOT NULL | ||
| | | '''id_people''' | ||
|- | |- | ||
| type | | type | ||
| Line 121: | Line 130: | ||
|- | |- | ||
| colspan=3 | FOREIGN KEY(id_people) REFERENCES people ON DELETE CASCADE | | colspan=3 | FOREIGN KEY(id_people) REFERENCES people ON DELETE CASCADE | ||
| | |} | ||
| Line 133: | Line 142: | ||
| id | | id | ||
| INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | ||
| | | '''id_tag''' | ||
|- | |- | ||
| name | | name | ||
| VARCHAR(255) UNIQUE NOT NULL | | VARCHAR(255) UNIQUE NOT NULL | ||
| | | | ||
| | |} | ||
== movies_tags == | == movies_tags == | ||
| Line 153: | Line 162: | ||
| id_movie | | id_movie | ||
| INTEGER NOT NULL | | INTEGER NOT NULL | ||
| | | '''id_movie''' | ||
|- | |- | ||
| id_tag | | id_tag | ||
| INTEGER NOT NULL | | INTEGER NOT NULL | ||
| | | '''id_tag''' | ||
|- | |- | ||
| colspan=3 | UNIQUE (id_tag, id_movie) ON CONFLICT IGNORE | | colspan=3 | UNIQUE (id_tag, id_movie) ON CONFLICT IGNORE | ||
| Line 164: | Line 173: | ||
|- | |- | ||
| colspan=3 | FOREIGN KEY(id_tag) REFERENCES tags ON DELETE CASCADE | | colspan=3 | FOREIGN KEY(id_tag) REFERENCES tags ON DELETE CASCADE | ||
| | |} | ||
== playlists == | == playlists == | ||
| Line 175: | Line 184: | ||
| id | | id | ||
| INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | ||
| | | '''id_playlist''' | ||
|- | |- | ||
| name | | name | ||
| Line 188: | Line 197: | ||
| INT | | INT | ||
| | | | ||
| | |} | ||
== movies_playlists == | == movies_playlists == | ||
| Line 203: | Line 212: | ||
| id_movie | | id_movie | ||
| INTEGER NOT NULL | | INTEGER NOT NULL | ||
| | | '''id_movie''' | ||
|- | |- | ||
| id_playlist | | id_playlist | ||
| INTEGER NOT NULL | | INTEGER NOT NULL | ||
| | | '''id_playlist''' | ||
|- | |- | ||
| colspan=3 | UNIQUE (id_playlist, id_movie) ON CONFLICT IGNORE | | colspan=3 | UNIQUE (id_playlist, id_movie) ON CONFLICT IGNORE | ||
| Line 214: | Line 223: | ||
|- | |- | ||
| colspan=3 | FOREIGN KEY(id_playlist) REFERENCES playlists ON DELETE CASCADE" | | colspan=3 | FOREIGN KEY(id_playlist) REFERENCES playlists ON DELETE CASCADE" | ||
| | |} | ||
== paths_list == | == paths_list == | ||
The paths where to look for movies files | |||
{| class="wikitable" | {| class="wikitable" | ||
|- | |- | ||
| Line 234: | Line 246: | ||
| BOOLEAN DEFAULT 0 | | BOOLEAN DEFAULT 0 | ||
| | | | ||
| | |} | ||
== config == | == config == | ||
This table is here for update purposes. | |||
{| class="wikitable" | {| class="wikitable" | ||
| Line 247: | Line 261: | ||
| INTERGER | | INTERGER | ||
| | | | ||
| | |} | ||
Latest revision as of 18:29, 18 July 2015
List of the tables
movies
Contains the informations of each movie.
| Column Name | Type | Foreign key |
|---|---|---|
| id | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | id_movie |
| title | VARCHAR(255) NOT NULL | |
| original_title | VARCHAR(255) | |
| release_date | VARCHAR(10) | |
| country | VARCHAR(50) | |
| duration | INTEGER | |
| synopsis | TEXT | |
| file_path | VARCHAR(255) UNIQUE NOT NULL | |
| poster_path | VARCHAR(255) | |
| colored | BOOLEAN | |
| format | VARCHAR(10) | |
| suffix | VARCHAR(10) | |
| rank | INTEGER | |
| imported | BOOLEAN | |
| series | BOOLEAN |
peoples
Contains informations about people, without affecting any type/role.
| Column Name | Type | Foreign key |
|---|---|---|
| id | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | id_people |
| name | VARCHAR(200) NOT NULL | |
| birthday | VARCHAR(10) | |
| biography | TEXT |
movies_people
Bind people to movies, here is defined the type of people (director, actor, ...)
| Column Name | Type | Foreign key |
|---|---|---|
| id | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | |
| id_movie | INTEGER NOT NULL | id_movie |
| id_people | INTEGER NOT NULL | id_people |
| type | INTEGER NOT NULL | |
| UNIQUE (id_people, id_movie, type) ON CONFLICT IGNORE | ||
| FOREIGN KEY(id_movie) REFERENCES movies ON DELETE CASCADE | ||
| FOREIGN KEY(id_people) REFERENCES people ON DELETE CASCADE | ||
tags
| Column Name | Type | Foreign key |
|---|---|---|
| id | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | id_tag |
| name | VARCHAR(255) UNIQUE NOT NULL |
movies_tags
| Column Name | Type | Foreign key |
|---|---|---|
| id | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | |
| id_movie | INTEGER NOT NULL | id_movie |
| id_tag | INTEGER NOT NULL | id_tag |
| UNIQUE (id_tag, id_movie) ON CONFLICT IGNORE | ||
| FOREIGN KEY(id_movie) REFERENCES movies ON DELETE CASCADE | ||
| FOREIGN KEY(id_tag) REFERENCES tags ON DELETE CASCADE | ||
playlists
| Column Name | Type | Foreign key |
|---|---|---|
| id | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | id_playlist |
| name | VARCHAR(255) UNIQUE NOT NULL | |
| rate | INTEGER | |
| creation_date | INT |
movies_playlists
| Column Name | Type | Foreign key |
|---|---|---|
| id | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | |
| id_movie | INTEGER NOT NULL | id_movie |
| id_playlist | INTEGER NOT NULL | id_playlist |
| UNIQUE (id_playlist, id_movie) ON CONFLICT IGNORE | ||
| FOREIGN KEY(id_movie) REFERENCES movies ON DELETE CASCADE | ||
| FOREIGN KEY(id_playlist) REFERENCES playlists ON DELETE CASCADE" | ||
paths_list
The paths where to look for movies files
| Column Name | Type | Foreign key |
|---|---|---|
| id | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | |
| movies_path | VARCHAR(255) UNIQUE | |
| imported | BOOLEAN DEFAULT 0 |
config
This table is here for update purposes.
| Column Name | Type | Foreign key |
|---|---|---|
| db_version | INTERGER |