sources/uSQL.py - main - VolcanoByte/vbNotes

sources/uSQL.py
157 строк4 KB
Eugene Gavrilyuk
timestamp changes
07 янв 2026, 18:05
07 янв 2026, 18:059a4c530
"""
Константы SQL запросов.
"""


# Команды создания таблиц
SQL_CREATE_TABLE_NOTES = """
CREATE TABLE tblNotes (
    fldNoteId          INTEGER PRIMARY KEY AUTOINCREMENT,
    fldNoteTitle       TEXT,
    fldNoteDescription TEXT,
    fldNoteCreatedAt   DOUBLE,
    fldNoteUpdatedAt   DOUBLE,
    fldNoteText        TEXT
);
"""

SQL_CREATE_TABLE_TAGS = """
create table if not exists tblTags (
    fldTagId        INTEGER CONSTRAINT tblTags_pk PRIMARY KEY AUTOINCREMENT,
    fldTagName      TEXT,
    fldTagUpperName TEXT,
    fldTagCount     INTEGER
);
"""

SQL_CREATE_TABLE_NOTE_TAG = """
create table if not exists tblNoteTag (
    fldNTNoteId INTEGER REFERENCES tblNotes (fldNoteId) ON DELETE CASCADE,
    fldNTTagId  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, tblNoteTag
    WHERE fldTagId=fldNTTagID
    GROUP BY fldNTTagId
"""

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

SQL_TAG_SET_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 = """
SELECT fldTagId, fldTagName, fldTagUpperName, 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_NOTE_SELECT = """
SELECT * FROM tblNotes
    WHERE fldNoteId = :noteId"""

SQL_NOTE_SELECT_ALL = """
SELECT fldNoteId, fldNoteTitle, fldNoteUpdatedAt
    FROM tblNotes
    ORDER BY fldNoteUpdatedAt DESC"""

SQL_NOTE_DELETE = """
DELETE FROM tblNotes
    WHERE fldNoteId = :noteId"""


# Команды Заметка-Тег
SQL_NOTE_TAG_SELECT = """
SELECT * FROM tblNoteTag WHERE fldNTNoteId = :noteId AND
    fldNTTagId = :tagId"""

SQL_NOTE_TAG_INSERT = """
INSERT INTO tblNoteTag (fldNTNoteId, fldNTTagId)
    VALUES (:noteId, :tagId)"""

SQL_NOTE_TAG_SELECT_NAMES = """
SELECT fldTagName FROM tblTags, tblNoteTag
    WHERE fldNTNoteId = :noteId AND fldNTTagId=fldTagId"""

SQL_NOTE_TAG_DELETE = """
DELETE FROM tblNoteTag
    WHERE fldNTNoteId = :noteId AND fldNTTagId = :tagId"""

SQL_NOTE_TAG_DELETE_ALL_TAGS = """
DELETE FROM tblNoteTag
    WHERE fldNTNoteId = :noteId"""

SQL_NOTE_TAG_SELECT_TAGS = """
SELECT fldTagId, fldTagName from tblTags, tblNoteTag
    WHERE fldTagId=fldNTTagId AND fldNTNoteId in (%s)
    GROUP BY fldTagId, fldTagCount
    ORDER BY fldTagCount DESC"""

SQL_NOTE_TAG_SELECT_NOTES = """
SELECT fldNoteId, fldNoteTitle, fldNoteUpdatedAt FROM (
    SELECT *, count(fldNoteId) as count FROM tblNoteTag, tblNotes
    WHERE fldNoteId = fldNTNoteId AND fldNTTagId IN (%s)
    GROUP BY fldNTNoteId)
    WHERE count = %d
    ORDER BY fldNoteUpdatedAt DESC"""