Under the Hood: Resource Files

What are Resource Files

Qt resource files, managed through the Resource Collection Compiler (rcc), provide a way for Qt applications like Password to bundle images, icons, UI definitions, and other static assets directly into the executable. Instead of relying on external files that must be shipped and located at runtime, a .qrc file allows these resources to be embedded as part of the application itself, ensuring they are always available and cannot be accidentally moved or deleted. This approach simplifies deployment, improves reliability, and keeps all required assets packaged neatly within the application’s binary.

Database Schema

Here's the complete database schema for Passwords to create an empty database for this version of Passwords.

CREATE TABLE application (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER,
    application_name TEXT,
    data TEXT,
    created INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);

-- application_tokens: add FK with cascade
CREATE TABLE application_tokens (
    application_id INTEGER NOT NULL,
    token_hash TEXT NOT NULL,
    FOREIGN KEY(application_id) REFERENCES application(id) ON DELETE CASCADE
);

-- application_views: add FK with cascade (keeps existing PK)
CREATE TABLE application_views (
    application_id INTEGER PRIMARY KEY,
    view_count INTEGER DEFAULT 0,
    dt INTEGER,
    user TEXT,
    host TEXT,
    FOREIGN KEY(application_id) REFERENCES application(id) ON DELETE CASCADE
);

-- application_views_audit: add FK with cascade
CREATE TABLE application_views_audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    application_id INTEGER NOT NULL,
    dt INTEGER,
    user TEXT,
    host TEXT,
    action TEXT,
    audit_time INTEGER,
    FOREIGN KEY(application_id) REFERENCES application(id) ON DELETE CASCADE
);


CREATE TABLE note (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER,
    note_title TEXT,
    data TEXT,
    created INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);

CREATE TABLE credit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER,
    card_name TEXT,
    data TEXT,
    created INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);

CREATE TABLE file (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER,
    file_name TEXT,
    data BLOB,
    created INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);


-- note_tokens
CREATE TABLE note_tokens (
    note_id INTEGER NOT NULL,
    token_hash TEXT NOT NULL,
    FOREIGN KEY(note_id) REFERENCES note(id) ON DELETE CASCADE
);

-- credit_tokens
CREATE TABLE credit_tokens (
    credit_id INTEGER NOT NULL,
    token_hash TEXT NOT NULL,
    FOREIGN KEY(credit_id) REFERENCES credit(id) ON DELETE CASCADE
);

-- file_tokens
CREATE TABLE file_tokens (
    file_id INTEGER NOT NULL,
    token_hash TEXT NOT NULL,
    FOREIGN KEY(file_id) REFERENCES file(id) ON DELETE CASCADE
);


-- note_views
CREATE TABLE note_views (
    note_id INTEGER PRIMARY KEY,
    view_count INTEGER DEFAULT 0,
    dt INTEGER,
    user TEXT,
    host TEXT,
    FOREIGN KEY(note_id) REFERENCES note(id) ON DELETE CASCADE
);

-- credit_views
CREATE TABLE credit_views (
    credit_id INTEGER PRIMARY KEY,
    view_count INTEGER DEFAULT 0,
    dt INTEGER,
    user TEXT,
    host TEXT,
    FOREIGN KEY(credit_id) REFERENCES credit(id) ON DELETE CASCADE
);

-- file_views
CREATE TABLE file_views (
    file_id INTEGER PRIMARY KEY,
    view_count INTEGER DEFAULT 0,
    dt INTEGER,
    user TEXT,
    host TEXT,
    FOREIGN KEY(file_id) REFERENCES file(id) ON DELETE CASCADE
);


-- note_views_audit
CREATE TABLE note_views_audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    note_id INTEGER NOT NULL,
    dt INTEGER,
    user TEXT,
    host TEXT,
    action TEXT,
    audit_time INTEGER,
    FOREIGN KEY(note_id) REFERENCES note(id) ON DELETE CASCADE
);

-- credit_views_audit
CREATE TABLE credit_views_audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    credit_id INTEGER NOT NULL,
    dt INTEGER,
    user TEXT,
    host TEXT,
    action TEXT,
    audit_time INTEGER,
    FOREIGN KEY(credit_id) REFERENCES credit(id) ON DELETE CASCADE
);

-- file_views_audit
CREATE TABLE file_views_audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    file_id INTEGER NOT NULL,
    dt INTEGER,
    user TEXT,
    host TEXT,
    action TEXT,
    audit_time INTEGER,
    FOREIGN KEY(file_id) REFERENCES file(id) ON DELETE CASCADE
);
CREATE TABLE app_info (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    parent_id INTEGER DEFAULT NULL,
    text TEXT NOT NULL,
    UNIQUE(parent_id, text),
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE
);

CREATE TABLE favourite (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    application_id INTEGER NOT NULL,
    username TEXT NOT NULL,
    FOREIGN KEY(application_id) REFERENCES application(id) ON DELETE CASCADE,
    UNIQUE(application_id, username)
    FOREIGN KEY(application_id) REFERENCES application(id) ON DELETE CASCADE
);


CREATE TABLE keys (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    label TEXT NOT NULL UNIQUE,
    key TEXT NOT NULL UNIQUE
);

CREATE INDEX idx_note_category ON note(category_id);
CREATE INDEX idx_credit_category ON credit(category_id);
CREATE INDEX idx_file_category ON file(category_id);
CREATE INDEX idx_note_created ON note(created);
CREATE INDEX idx_credit_created ON credit(created);
CREATE INDEX idx_file_created ON file(created);
CREATE INDEX idx_favourite_username ON favourite(username);
CREATE INDEX idx_favourite_application ON favourite(application_id);


CREATE TRIGGER application_views_insert_audit
AFTER INSERT ON application_views
BEGIN
    INSERT INTO application_views_audit(application_id, dt, user, host, action, audit_time)
    VALUES (NEW.application_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER application_views_update_audit
AFTER UPDATE ON application_views
BEGIN
    INSERT INTO application_views_audit(application_id, dt, user, host, action, audit_time)
    VALUES (NEW.application_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER note_views_insert_audit
AFTER INSERT ON note_views
BEGIN
    INSERT INTO note_views_audit(note_id, dt, user, host, action, audit_time)
    VALUES (NEW.note_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER note_views_update_audit
AFTER UPDATE ON note_views
BEGIN
    INSERT INTO note_views_audit(note_id, dt, user, host, action, audit_time)
    VALUES (NEW.note_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER credit_views_insert_audit
AFTER INSERT ON credit_views
BEGIN
    INSERT INTO credit_views_audit(credit_id, dt, user, host, action, audit_time)
    VALUES (NEW.credit_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER credit_views_update_audit
AFTER UPDATE ON credit_views
BEGIN
    INSERT INTO credit_views_audit(credit_id, dt, user, host, action, audit_time)
    VALUES (NEW.credit_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER file_views_insert_audit
AFTER INSERT ON file_views
BEGIN
    INSERT INTO file_views_audit(file_id, dt, user, host, action, audit_time)
    VALUES (NEW.file_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER file_views_update_audit
AFTER UPDATE ON file_views
BEGIN
    INSERT INTO file_views_audit(file_id, dt, user, host, action, audit_time)
    VALUES (NEW.file_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE VIEW all_objects AS
SELECT 'application' AS type, id, category_id, created
FROM application
UNION ALL
SELECT 'note' AS type, id, category_id, created
FROM note
UNION ALL
SELECT 'credit' AS type, id, category_id, created
FROM credit
UNION ALL
SELECT 'file' AS type, id, category_id, created
FROM file;

INSERT OR IGNORE INTO app_info (key, value)
VALUES ('app_signature', 'Asteraceae');

INSERT OR IGNORE INTO app_info (key, value)
VALUES ('schema_version', '1');