sources/uSQL.py - master - VolcanoByte/vbCinema

sources/uSQL.py
224 строки5 KB
Johnny McLaren
fix: sync & some bugs
25 янв 2026, 09:49
25 янв 2026, 09:4983652ff
"""
Константы SQL запросов.
"""

TABLE_MOVIES_FIELD = """
fldMovieID, fldMovieIMDBID, fldMovieOriginalTitle, fldMovieRussianTitle, 
fldMovieYear, fldMovieLength, fldMovieRatingKinopoisk, fldMovieRatingIMDB, 
fldMovieDesc, fldMovieWatched, fldMovieWanted, fldMovieAddedAt, 
fldMovieWatchedAt, fldMovieSequenceNumber
"""
# Команды создания таблиц
SQL_CREATE_TABLE_MOVIES = """
CREATE TABLE tblMovies (
    fldMovieID              INTEGER PRIMARY KEY ON CONFLICT FAIL,
    fldMovieIMDBID          TEXT,
    fldMovieOriginalTitle   TEXT,
    fldMovieRussianTitle    TEXT,
    fldMovieYear            INTEGER DEFAULT (0),
    fldMovieLength          INTEGER DEFAULT (0),
    fldMovieRatingKinopoisk TEXT    DEFAULT (0),
    fldMovieRatingIMDB      TEXT    DEFAULT (0),
    fldMovieDesc            TEXT,
    fldMovieWatched         BOOLEAN DEFAULT (0),
    fldMovieWanted          BOOLEAN DEFAULT (0),
    fldMovieAddedAt         DOUBLE,
    fldMovieWatchedAt       DOUBLE,
    fldMovieSequenceNumber  INTEGER DEFAULT (0) 
);

"""

SQL_CREATE_TABLE_TAGS = """
CREATE TABLE tblTags (
    fldTagId        INTEGER PRIMARY KEY,
    fldTagName      TEXT    UNIQUE ON CONFLICT FAIL
                            NOT NULL ON CONFLICT FAIL,
    fldTagUpperName TEXT,
    fldTagCount     INTEGER
);
"""

SQL_CREATE_TABLE_MOVIE_TAG = """
CREATE TABLE tblMovieTag (
    fldMTID      INTEGER PRIMARY KEY AUTOINCREMENT,
    fldMTMovieID INTEGER REFERENCES tblMovies (fldMovieID) ON DELETE CASCADE,
    fldMTTagID   INTEGER REFERENCES tblTags (fldTagId) ON DELETE CASCADE
);
"""

SQL_CREATE_TABLE_PROPERTIES = """
CREATE TABLE tblProperties (
    fldPropertyTimestamp    DOUBLE,
    fldPropertySyncId       TEXT,
    fldPropertySyncURL      TEXT,
    fldPropertySyncUsername TEXT,
    fldPropertySyncPassword TEXT
);
"""

# Вспомогательные команды
SQL_GET_REC_COUNT = """
SELECT COUNT(*)
    FROM %s
"""

# Работа с таблицей свойств
SQL_PROPERTIES_ADD_ROW = """
INSERT 
    INTO tblProperties (fldPropertySyncId) 
    VALUES (:syncId)
"""

SQL_PROPERTIES_GET = """
SELECT %s
    FROM tblProperties"""

SQL_PROPERTIES_SET = """
UPDATE tblProperties
    SET %s = '%s'
"""

#  Работа с тегами
SQL_TAG_CALC_COUNT = """
SELECT fldTagName, COUNT(*) AS count_tag
    FROM tblTags, tblMovieTag
    WHERE fldTagId=fldMTTagID
    GROUP BY fldMTTagID
"""

SQL_TAG_CLEAR_COUNT = """
UPDATE tblTags
    SET fldTagCount = 0
    WHERE fldTagCount > 0
"""

SQL_TAG_SET_COUNT_UPPER_NAME = """
UPDATE tblTags
    SET fldTagCount = :fldTagCount, fldTagUpperName = :fldTagUpperName
    WHERE fldTagName = :fldTagName
"""

SQL_TAG_DELETE_EMPTY = """
DELETE FROM tblTags
    WHERE fldTagCount = 0 OR fldTagCount IS NULL
"""

SQL_TAG_SELECT_ALL_BY_NAME = """
SELECT fldTagId, fldTagName
    FROM tblTags
    ORDER BY fldTagUpperName
"""
SQL_TAG_SELECT_ALL_BY_COUNT = """
SELECT fldTagId, fldTagName, fldTagCount
    FROM tblTags
    ORDER BY fldTagCount DESC
"""

SQL_TAG_SELECT_BY_NAME = """
SELECT fldTagId 
    FROM tblTags 
    WHERE fldTagUpperName = :upperName
"""

SQL_TAG_SELECT_BY_ID = """
SELECT fldTagName 
    FROM tblTags 
    WHERE  fldTagId = :id
"""

SQL_TAG_ADD = """
INSERT 
    INTO tblTags 
        (fldTagName, fldTagUpperName) 
        VALUES (:name, :upperName)
"""

SQL_TAG_DELETE_BY_NAME = """
DELETE 
    FROM tblTags 
    WHERE fldTagUpperName = :upperName
"""

SQL_TAG_DELETE_BY_ID = """
DELETE 
    FROM tblTags 
    WHERE fldTagId = :id
"""

# Команды для работы с фильмами
SQL_MOVIE_SELECT_ALL = """
SELECT %s 
    FROM tblMovies
"""

# SQL_MOVIE_SELECT_
SQL_MOVIE_SELECT = """
SELECT * 
    FROM tblMovies
    WHERE fldMovieID = :movieId
"""

SQL_MOVIE_DELETE = """
DELETE 
    FROM tblMovies
    WHERE fldMovieID = :movieId
"""

SQL_MOVIE_WATCHED_AT = """
UPDATE tblMovies SET fldMovieWatchedAt = :watchedAt
    WHERE fldMovieID = :movieId
"""

SQL_MOVIE_SET_WANTED = """
UPDATE tblMovies SET fldMovieWanted = :wanted
    WHERE fldMovieID = :movieId
"""

SQL_MOVIE_SET_WATCHED = """
UPDATE tblMovies SET fldMovieWatched = :watched
    WHERE fldMovieID = :movieId
"""

# Команды Заметка-Тег
SQL_MOVIE_TAG_SELECT = """
SELECT * 
    FROM tblMovieTag WHERE fldMTMovieID = :movieId AND
    fldMTTagID = :tagId
"""

SQL_MOVIE_TAG_INSERT = """
INSERT INTO tblMovieTag (fldMTMovieID, fldMTTagID)
    VALUES (:movieId, :tagId)
"""

SQL_MOVIE_TAG_SELECT_NAMES = """
SELECT fldTagName FROM tblTags, tblMovieTag
    WHERE fldMTMovieID = :movieId AND fldMTTagID=fldTagId
"""

SQL_MOVIE_TAG_DELETE = """
DELETE FROM tblMovieTag
    WHERE fldMTMovieID = :movieId AND fldMTTagID = :tagId
"""

SQL_MOVIE_TAG_DELETE_ALL_TAGS = """
DELETE FROM tblMovieTag
    WHERE fldMTMovieID = :movieId
"""

SQL_MOVIE_TAG_SELECT_TAGS = """
SELECT fldTagId, fldTagName 
    FROM tblTags, tblMovieTag
    WHERE fldTagId=fldMTTagID AND tblMovieTag.fldMTMovieID in (%s)
    GROUP BY fldTagId, fldTagCount
    ORDER BY fldTagCount DESC
"""

SQL_MOVIE_TAG_SELECT_MOVIES = """
SELECT %s FROM (
    SELECT *, count(fldMovieID) as count FROM tblMovieTag, tblMovies
    WHERE fldMovieID = fldMTMovieID AND fldMTTagID IN (%s)
    GROUP BY fldMTMovieID)
    WHERE count = %d
"""