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...")
 
(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 ==


== 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
Retrieved from "https://community.kde.org/index.php?title=Macaw-Movies/Development/Database_schema&oldid=43190"