def movie_schema(): movie = Database('sqlite', database=settings.MOVIE_DB_PATH) movie.add_schema([ [1, 'CREATE TABLE movie('\ 'id INTEGER PRIMARY KEY,'\ 'title TEXT NOT NULL UNIQUE,'\ 'year INT NOT NULL,'\ 'url TEXT NOT NULL UNIQUE,'\ 'date_added TIMESTAMP NOT NULL)'], [2, 'CREATE TABLE image('\ 'id INTEGER PRIMARY KEY,'\ 'movie_id INTEGER NOT NULL,'\ 'path TEXT NOT NULL UNIQUE,'\ 'FOREIGN KEY(movie_id) REFERENCES movie(id))'], [3, 'CREATE TABLE detail_queue_status(' 'id INTEGER PRIMARY KEY,' 'status TEXT NOT NULL UNIQUE)'], [4, "INSERT INTO detail_queue_status(status) VALUES('NEW')"], [5, "INSERT INTO detail_queue_status(status) VALUES('COMPLETE')"], [6, "INSERT INTO detail_queue_status(status) VALUES('ERROR')"], [7, 'CREATE TABLE detail_queue(' 'id INTEGER PRIMARY KEY,' 'movie_id INTEGER NOT NULL,' 'detail_queue_status_id INTEGER NOT NULL DEFAULT 1,' 'FOREIGN KEY (detail_queue_status_id) REFERENCES detail_queue_status(id) ON UPDATE CASCADE ON DELETE CASCADE,'\ 'FOREIGN KEY (movie_id) REFERENCES movie(id) ON UPDATE CASCADE ON DELETE CASCADE)'], [8, "CREATE TABLE detail(" 'id INTEGER PRIMARY KEY', 'movie_id INT NOT NULL', 'imdb_key VARCHAR NOT NULL', 'released_date TIMESTAMP NOT NULL', 'plot VARCHAR NOT NULL', 'imdb_rating REAL NOT NULL', 'FOREIGN_KEY(movie_id) REFERENCES movie(id))'], ])
def create_movie_db(): """ Create Movie database """ log.debug("creating movie database") db = Database('sqlite', database=settings.MOVIE_DB) db.initialise()
def database_init(): db = Database('postgresql', database=settings.DB_NAME, user=settings.DB_USER, password=settings.DB_PASSWORD, host=settings.DB_HOST, port=settings.DB_PORT) db.initialise()
def create_general_db(): """ Create general database to house global settings for letmenotifyu """ log.debug("creating general database") db = Database('sqlite', database=settings.GENERAL_DB) db.initialise()
def create_series_db(): """ Create series database """ log.debug("creating series database") db = Database('sqlite', database=settings.SERIES_DB) db.initialise()
def general_migration(): log.debug("applying migrations for general database") db = Database('sqlite', database=settings.GENERAL_DB) db.add_schema([ [1, 'CREATE TABLE config(' 'id INTEGER PRIMARY KEY,' 'key TEXT NOT NULL,' 'value TEXT NOT NULL,' 'UNIQUE(key,value)) '], [2, "INSERT INTO config(key,value) VALUES('update_interval', '3600')"], [3, "INSERT INTO config(key,value) VALUES('transmission_host','127.0.0.1')"], [4, "INSERT INTO config(key,value) VALUES('transmission_port','9091')"], ])
def database_change(): db = Database('postgresql', database=settings.DB_NAME, user=settings.DB_USER, password=settings.DB_PASSWORD, host=settings.DB_HOST, port=settings.DB_PORT) db.add_schema([ [1, 'CREATE TABLE config(' \ 'id SERIAL PRIMARY KEY, ' \ 'key TEXT NOT NULL,' \ 'value TEXT NOT NULL,'\ 'UNIQUE(key,value))'], [2, "INSERT INTO config(key,value) VALUES('update_interval','3600')"], [3, "INSERT INTO config(key,value) VALUES('movie_process_interval', '15')"], [4, "INSERT INTO config(key,value) VALUES('series_process_interval','15')"], [5, "INSERT INTO config(key,value) VALUES('movie_duration','7')"], [6, "INSERT INTO config(key,value) VALUES('series_duration','7')"], [7, "INSERT INTO config(key,value) VALUES('movie_quality','720p')"], [8, "INSERT INTO config(key,value) VALUES('max_movie_results','50')"], [9, "CREATE TABLE genre(" \ "id SERIAL PRIMARY KEY," \ "genre TEXT UNIQUE NOT NULL )"], [10, 'CREATE TABLE movies(' \ 'id SERIAL PRIMARY KEY,' \ 'yify_id INT UNIQUE NOT NULL,'\ 'genre_id INT NOT NULL,' \ 'title TEXT UNIQUE NOT NULL,' \ 'link TEXT NOT NULL,' \ 'date_added TIMESTAMP NOT NULL,' \ 'year INT NOT NULL,'\ 'FOREIGN KEY(genre_id) REFERENCES genre(id) ON UPDATE CASCADE ON DELETE CASCADE)'], [11, "CREATE TABLE movie_details("\ 'id SERIAL PRIMARY KEY NOT NULL,'\ 'movie_id INT UNIQUE NOT NULL,'\ 'language TEXT NOT NULL,'\ 'movie_rating REAL NOT NULL,'\ 'youtube_url TEXT NOT NULL,'\ 'description TEXT NOT NULL,'\ 'FOREIGN KEY(movie_id) REFERENCES movies(id) ON UPDATE CASCADE ON DELETE CASCADE)'], [12, 'CREATE table movie_images(' \ 'id serial PRIMARY KEY,' \ 'title TEXT NOT NULL,'\ 'path TEXT NOT NULL,'\ 'UNIQUE(title,path))' ], [13, "CREATE TABLE movie_torrent_links("\ 'id SERIAL PRIMARY KEY,'\ 'movie_id INT UNIQUE NOT NULL,'\ 'link TEXT NOT NULL,'\ 'hash_sum TEXT NOT NULL,' 'FOREIGN KEY(movie_id) REFERENCES movies(id) ON UPDATE CASCADE ON DELETE CASCADE)'], [14, "CREATE TABLE upcoming_movies("\ 'id SERIAL PRIMARY KEY,'\ 'title TEXT UNIQUE NOT NULL,'\ 'link TEXT UNIQUE NOT NULL,'\ 'UNIQUE(title,link))'], [15, 'CREATE TABLE series(' \ 'id SERIAL PRIMARY KEY,' \ 'title TEXT NOT NULL,' \ 'series_link TEXT UNIQUE NOT NULL,' \ 'number_of_episodes INT NOT NULL,' \ 'number_of_seasons INT NOT NULL,' \ 'current_season INT NOT NULL,' \ 'last_update TIMESTAMP NOT NULL,' \ 'status BOOLEAN NOT NULL,'\ 'watch BOOLEAN NOT NULL)'], [16, 'CREATE table series_images(' \ 'id SERIAL PRIMARY KEY,' \ 'series_id INT NOT NULL,' 'path TEXT UNIQUE NOT NULL,' \ 'FOREIGN KEY (series_id) REFERENCES series(id) ON UPDATE CASCADE ON DELETE CASCADE)'], [17, 'CREATE TABLE episodes(' \ 'id SERIAL PRIMARY KEY,' \ 'series_id INT NOT NULL,' \ 'episode_name TEXT NOT NULL,' \ 'episode_number TEXT NOT NULL,'\ 'episode_link TEXT UNIQUE NOT NULL,' \ 'date TIMESTAMP,' \ ' FOREIGN KEY (series_id) REFERENCES series(id) ON DELETE CASCADE ON UPDATE CASCADE)'], [18, "CREATE TABLE watch_queue_status("\ "id SERIAL PRIMARY KEY,"\ "name TEXT UNIQUE NOT NULL)"], [19, "INSERT INTO watch_queue_status(name) VALUES('new')"], [20, "INSERT INTO watch_queue_status(name) VALUES('torrent downloaded')"], [21, "INSERT INTO watch_queue_status(name) VALUES('downloading')"], [22, "INSERT INTO watch_queue_status(name) VALUES('complete')"], [23, "INSERT INTO watch_queue_status(name) VALUES('error downloading')"], [24, "CREATE TABLE series_queue(" \ 'id SERIAL PRIMARY KEY,' \ 'series_id INT NOT NULL,' \ 'episode_id INT UNIQUE NOT NULL,' \ 'episode_name TEXT NOT NULL,' \ 'watch_queue_status_id INT NOT NULL,'\ 'FOREIGN KEY(series_id) REFERENCES series(id) ON DELETE CASCADE ON UPDATE CASCADE,'\ 'FOREIGN KEY(episode_id) REFERENCES episodes(id),'\ 'FOREIGN KEY(watch_queue_status_id) REFERENCES watch_queue_status(id))'], [25, "CREATE TABLE series_torrent_links("\ 'id SERIAL PRIMARY KEY,'\ 'series_queue_id INT UNIQUE NOT NULL,'\ 'link TEXT NOT NULL,'\ 'FOREIGN KEY(series_queue_id) REFERENCES series_queue(id) ON UPDATE CASCADE ON DELETE CASCADE)'], [26, 'CREATE TABLE torrents(' \ 'Id SERIAL PRIMARY KEY,' \ 'name TEXT UNIQUE NOT NULL,' \ 'link TEXT NOT NULL)' ], [27, "CREATE TABLE movie_queue("\ 'id SERIAL PRIMARY KEY,'\ 'movie_id INT UNIQUE NOT NULL,'\ 'watch_queue_status_id INT NOT NULL,'\ 'FOREIGN KEY(movie_id) REFERENCES movies(id) ON DELETE CASCADE ON UPDATE CASCADE,'\ 'FOREIGN KEY(watch_queue_status_id) REFERENCES watch_queue_status(id))'], [28, "CREATE TABLE upcoming_queue("\ 'id SERIAL PRIMARY KEY,'\ 'title TEXT UNIQUE NOT NULL,'\ 'FOREIGN KEY(title) REFERENCES upcoming_movies(title) ON DELETE CASCADE ON UPDATE CASCADE)'], [29, "CREATE TABLE actors("\ 'id SERIAL PRIMARY KEY,' \ 'name TEXT UNIQUE NOT NULL)'], [30, "CREATE TABLE actors_movies("\ 'id SERIAL PRIMARY KEY,'\ 'actor_id INTEGER NOT NULL,'\ 'movie_id INTEGER NOT NULL,'\ 'UNIQUE(actor_id,movie_id),'\ 'FOREIGN KEY(movie_id) REFERENCES movies(id))'], [31, "INSERT INTO config(key,value) VALUES('imdb_url','http://www.imdb.com/title/')"], [32, "INSERT INTO config(key,value) VALUES('youtube_url','https://www.youtube.com/watch?v=')"], [33, "ALTER TABLE series_torrent_links ADD COLUMN torrent_hash TEXT DEFAULT '0'"], [34, "ALTER TABLE movie_torrent_links ADD COLUMN transmission_hash TEXT DEFAULT '0'"], [35, "ALTER TABLE movie_torrent_links ADD COLUMN torrent_name TEXT DEFAULT '0'"], [36, "ALTER TABLE series_torrent_links ADD COLUMN transmission_hash TEXT DEFAULT '0'"], [37, "ALTER TABLE series_torrent_links ADD COLUMN torrent_namennnnn TEXT DEFAULT '0'"], [38, "INSERT INTO config(key,value) VALUES('transmission_host','127.0.0.1')"], [39, "INSERT INTO config(key,value) VALUES('transmission_port','9091')"], [40, "ALTER TABLE series_torrent_links DROP COLUMN torrent_hash"], [41, "DROP TABLE upcoming_movies CASCADE"], [42, "DROP TABLE upcoming_queue"], [43, "ALTER TABLE series_queue ALTER COLUMN watch_queue_status_id SET DEFAULT 1"], [44, "ALTER TABLE movie_queue ALTER COLUMN watch_queue_status_id SET DEFAULT 1"], [45, "DROP TABLE torrents"], [46, "ALTER TABLE series ALTER COLUMN watch SET DEFAULT '1'"], [47, "ALTER TABLE series ALTER COLUMN status SET DEFAULT '1'"], ])
def create_db(db_name): db = Database('sqlite', database=db_name) db.initialise()
def movie_migration(): """ Database changes for movie database """ log.debug("applying migrations for movie database") db = Database('sqlite', database=settings.MOVIE_DB) db.add_schema([ [1, 'CREATE TABLE config(' 'id INTEGER PRIMARY KEY,' 'key TEXT NOT NULL,' 'value TEXT NOT NULL,' 'UNIQUE(key,value)) '], [2, "INSERT INTO config(key, value) VALUES('movie_process_interval', '15')"], [3, "INSERT INTO config(key,value) VALUES('movie_duration','7')"], [4, "INSERT INTO config(key,value) VALUES('movie_quality','720p')"], [5, "INSERT INTO config(key,value) VALUES('max_movie_results','50')"], [6, "INSERT INTO config(key,value) VALUES('update_interval','3600')"], [7, "INSERT INTO config(key,value) VALUES('imdb_url','http://www.imdb.com/title/')"], [8, "INSERT INTO config(key,value) VALUES('youtube_url','https://www.youtube.com/watch?v=')"], [9, "INSERT INTO config(key,value) VALUES('transmission_host','127.0.0.1')"], [10, "INSERT INTO config(key,value) VALUES('transmission_port','9091')"], [11, "CREATE TABLE genre(" "id INTEGER PRIMARY KEY," "genre TEXT UNIQUE NOT NULL )"], [12, 'CREATE TABLE movies(' 'id INTEGER PRIMARY KEY,' 'yify_id INT UNIQUE NOT NULL,' 'genre_id INT NOT NULL,' 'title TEXT UNIQUE NOT NULL,' 'link TEXT NOT NULL,' 'date_added TIMESTAMP NOT NULL,' 'year INT NOT NULL,' 'FOREIGN KEY(genre_id) REFERENCES genre(id)' ' ON UPDATE CASCADE ON DELETE CASCADE)'], [13, "CREATE TABLE movie_details(" 'id INTEGER PRIMARY KEY NOT NULL,' 'movie_id INT UNIQUE NOT NULL,' 'language TEXT NOT NULL,' 'movie_rating REAL NOT NULL,' 'youtube_url TEXT NOT NULL,' 'description TEXT NOT NULL,' 'FOREIGN KEY(movie_id) REFERENCES movies(id)' 'ON UPDATE CASCADE ON DELETE CASCADE)'], [14, 'CREATE table movie_images(' 'id INTEGER PRIMARY KEY,' 'movie_id INT UNIQUE NOT NULL,' 'path TEXT NOT NULL,' 'FOREIGN KEY(movie_id) REFERENCES movies(id) ON UPDATE CASCADE ON DELETE CASCADE)' ], [15, "CREATE TABLE movie_torrent_links(" 'id INTEGER PRIMARY KEY,' 'movie_id INT UNIQUE NOT NULL,' 'link TEXT NOT NULL,' 'hash_sum TEXT NOT NULL,' 'transmission_hash TEXT DEFAULT 0,' 'torrent_name TEXT DEFAULT 0,' 'FOREIGN KEY(movie_id) REFERENCES movies(id) ON UPDATE CASCADE ON DELETE CASCADE)'], [16, "CREATE TABLE watch_queue_status(" "id INTEGER PRIMARY KEY," "name TEXT UNIQUE NOT NULL)"], [17, "INSERT INTO watch_queue_status(name) VALUES('new')"], [18, "INSERT INTO watch_queue_status(name) VALUES('torrent downloaded')"], [19, "INSERT INTO watch_queue_status(name) VALUES('downloading')"], [20, "INSERT INTO watch_queue_status(name) VALUES('complete')"], [21, "INSERT INTO watch_queue_status(name) VALUES('error downloading')"], [22, "CREATE TABLE movie_queue(" 'id INTEGER PRIMARY KEY,' 'movie_id INT UNIQUE NOT NULL,' 'watch_queue_status_id INT NOT NULL DEFAULT 1,' 'FOREIGN KEY(movie_id) REFERENCES movies(id) ON DELETE CASCADE ON UPDATE CASCADE,' 'FOREIGN KEY(watch_queue_status_id) REFERENCES watch_queue_status(id))'], [23, "CREATE TABLE actors(" 'id INTEGER PRIMARY KEY,' 'name TEXT UNIQUE NOT NULL)'], [24, "CREATE TABLE actors_movies(" 'id INTEGER PRIMARY KEY,' 'actor_id INT NOT NULL,' 'movie_id INT NOT NULL,' 'UNIQUE(actor_id,movie_id),' 'FOREIGN KEY(movie_id) REFERENCES movies(id) ON DELETE CASCADE ON UPDATE CASCADE,' 'FOREIGN KEY(actor_id) REFERENCES actors(id) ON DELETE CASCADE ON UPDATE CASCADE)'], [25, "DELETE FROM config WHERE key='transmission_host'"], [26, "DELETE FROM config WHERE key='transmission_port'"], ]) return
def series_migration(): """ Database changes for series database """ log.debug("applying migrations for series database") db = Database('sqlite', database=settings.SERIES_DB) db.add_schema([ [1, 'CREATE TABLE config(' 'id INTEGER PRIMARY KEY, ' 'key TEXT NOT NULL,' 'value TEXT NOT NULL,' 'UNIQUE(key,value))'], [2, "INSERT INTO config(key,value) VALUES('update_interval','3600')"], [3, "INSERT INTO config(key,value) VALUES('series_process_interval','15')"], [4, "INSERT INTO config(key,value) VALUES('series_duration','7')"], [5, "INSERT INTO config(key,value) VALUES('transmission_host','127.0.0.1')"], [6, "INSERT INTO config(key,value) VALUES('transmission_port','9091')"], [7, 'CREATE TABLE series(' 'id INTEGER PRIMARY KEY,' 'title TEXT NOT NULL,' 'series_link TEXT UNIQUE NOT NULL,' 'number_of_episodes INT NOT NULL,' 'number_of_seasons INT NOT NULL,' 'current_season INT NOT NULL,' 'last_update TIMESTAMP NOT NULL,' 'status BOOLEAN NOT NULL DEFAULT t,' 'watch BOOLEAN NOT NULL DEFAULT t)'], [8, 'CREATE table series_images(' 'id INTEGER PRIMARY KEY,' 'series_id INT NOT NULL,' 'path TEXT UNIQUE NOT NULL,' 'FOREIGN KEY (series_id) REFERENCES series(id) ON UPDATE CASCADE ON DELETE CASCADE)'], [9, 'CREATE TABLE episodes(' 'id INTEGER PRIMARY KEY,' 'series_id INT NOT NULL,' 'episode_name TEXT NOT NULL,' 'episode_number TEXT NOT NULL,' 'episode_link TEXT UNIQUE NOT NULL,' 'date TIMESTAMP,' ' FOREIGN KEY (series_id) REFERENCES series(id) ON DELETE CASCADE ON UPDATE CASCADE)'], [10, "CREATE TABLE watch_queue_status(" "id INTEGER PRIMARY KEY," "name TEXT UNIQUE NOT NULL)"], [11, "INSERT INTO watch_queue_status(name) VALUES('new')"], [12, "INSERT INTO watch_queue_status(name) VALUES('torrent downloaded')"], [13, "INSERT INTO watch_queue_status(name) VALUES('downloading')"], [14, "INSERT INTO watch_queue_status(name) VALUES('complete')"], [15, "INSERT INTO watch_queue_status(name) VALUES('error downloading')"], [16, "CREATE TABLE series_queue(" 'id INTEGER PRIMARY KEY,' 'series_id INT NOT NULL,' 'episode_id INT UNIQUE NOT NULL,' 'episode_name TEXT NOT NULL,' 'watch_queue_status_id INT NOT NULL DEFAULT 1,' 'FOREIGN KEY(series_id) REFERENCES series(id) ON DELETE CASCADE ON UPDATE CASCADE,' 'FOREIGN KEY(episode_id) REFERENCES episodes(id),' 'FOREIGN KEY(watch_queue_status_id) REFERENCES watch_queue_status(id))'], [17, "CREATE TABLE series_torrent_links(" 'id INTEGER PRIMARY KEY,' 'series_queue_id INT UNIQUE NOT NULL,' 'link TEXT NOT NULL,' 'transmission_hash TEXT DEFAULT 0,' 'torrent_name TEXT DEFAULT 0,' 'FOREIGN KEY(series_queue_id) REFERENCES series_queue(id) ON UPDATE CASCADE ON DELETE CASCADE)'], [18, "INSERT INTO config(key,value) VALUES('imdb_url','http://www.imdb.com/title/')"], [19, "INSERT INTO config(key,value) VALUES('youtube_url','https://www.youtube.com/watch?v=')"], ])