Macaw-Movies/Development/Database schema: Difference between revisions

From KDE Community Wiki
(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