Macaw-Movies/Development/Database schema

From KDE Community Wiki

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