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..." |
No edit summary |
||
| Line 2: | Line 2: | ||
== movies == | == movies == | ||
Contains the informations of each movie. | |||
{| class="wikitable" | {| class="wikitable" | ||
| Line 11: | Line 13: | ||
| id | | id | ||
| INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | ||
| | | '''id_movie''' | ||
|- | |- | ||
| title | | title | ||
| Line 77: | Line 79: | ||
| id | | id | ||
| INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | ||
| | | '''id_people''' | ||
|- | |- | ||
| name | | name | ||
| Line 93: | Line 95: | ||
== movies_people == | == movies_people == | ||
Bind people to movies, here is defined the type of people (director, actor, ...) | |||
{| class="wikitable" | {| class="wikitable" | ||
| Line 106: | Line 110: | ||
| 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 133: | Line 137: | ||
| id | | id | ||
| INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | ||
| | | '''id_tag''' | ||
|- | |- | ||
| name | | name | ||
| Line 153: | Line 157: | ||
| 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 175: | Line 179: | ||
| id | | id | ||
| INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | ||
| | | '''id_playlist''' | ||
|- | |- | ||
| name | | name | ||
| Line 203: | Line 207: | ||
| 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 217: | Line 221: | ||
== paths_list == | == paths_list == | ||
The paths where to look for movies files | |||
{| class="wikitable" | {| class="wikitable" | ||
|- | |- | ||
| Line 237: | Line 244: | ||
== config == | == config == | ||
This table is here for updates purpose. | |||
{| class="wikitable" | {| class="wikitable" | ||
Revision as of 14:31, 23 April 2015
movies
Contains the informations of each movie.
peoples
| 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 |
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_people |
| name | VARCHAR(200) NOT NULL | |
| birthday | VARCHAR(10) | |
| biography | TEXT |
tags
| 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 | ||
movies_tags
| Column Name | Type | Foreign key |
|---|---|---|
| id | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | id_tag |
| name | VARCHAR(255) UNIQUE NOT NULL |
playlists
| 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 | ||
movies_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 |
paths_list
The paths where to look for movies files
| 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" | ||
config
This table is here for updates purpose.
| Column Name | Type | Foreign key |
|---|---|---|
| id | INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE | |
| movies_path | VARCHAR(255) UNIQUE | |
| imported | BOOLEAN DEFAULT 0 |
| Column Name | Type | Foreign key |
|---|---|---|
| db_version | INTERGER |