49 lines
2.1 KiB
SQL
49 lines
2.1 KiB
SQL
-- main.setup.db.sqlite.sql:
|
|
-- file that contains the sqlite SQL used to setup the database.
|
|
-- idea is to have the sqlite SQL in a separate ideally small file to
|
|
-- a) be able to tweak/see SQL database setup without necessarily force recompilation
|
|
-- b) change for syntax highlighting...
|
|
-- tradeoff challenges assumed are issues with:
|
|
-- a) split of individual SQL commands and
|
|
-- b) handling of comments
|
|
CREATE TABLE IF NOT EXISTS sources (
|
|
id INTEGER PRIMARY KEY,
|
|
parent_id INTEGER REFERENCES sources(id),
|
|
name TEXT,
|
|
timestamp INTEGER,
|
|
UNIQUE (parent_id,name) ON CONFLICT IGNORE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS sources_index__timestamp ON sources(timestamp);
|
|
CREATE INDEX IF NOT EXISTS sources_index__parent_id ON sources(parent_id);
|
|
CREATE INDEX IF NOT EXISTS sources_index__name ON sources(name);
|
|
CREATE INDEX IF NOT EXISTS sources_index__parent_id__name ON sources(parent_id,name);
|
|
--CREATE INDEX IF NOT EXISTS sources_index__parent_id__name__timestamp ON sources(parent_id,name,timestamp);
|
|
CREATE INDEX IF NOT EXISTS sources_index__parent_id__name ON sources(parent_id,name);
|
|
|
|
INSERT OR IGNORE INTO sources (id,parent_id,name,timestamp) VALUES (0, 0, '/', 0);
|
|
-- files
|
|
CREATE TABLE IF NOT EXISTS files(
|
|
id INTEGER PRIMARY KEY,
|
|
source_id INTEGER REFERENCES sources(id),
|
|
name TEXT,
|
|
size INTEGER,
|
|
mtime INTEGER,
|
|
timestamp INTEGER,
|
|
hash BLOB,
|
|
UNIQUE (source_id,name,size,mtime) ON CONFLICT IGNORE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS files_index__timestamp ON files(timestamp);
|
|
CREATE INDEX IF NOT EXISTS files_index__hash ON files(hash);
|
|
-- for stmt_select_file_id
|
|
CREATE INDEX IF NOT EXISTS files_index__source_id__name__timestamp ON files(source_id,name,timestamp);
|
|
CREATE INDEX IF NOT EXISTS files_index__source_id__name__size__mtime ON files(source_id,name,size,mtime);
|
|
|
|
-- hashes
|
|
-- CREATE TABLE IF NOT EXISTS hashes(
|
|
-- id INTEGER PRIMARY KEY,
|
|
-- hash BLOB,
|
|
-- timestamp INTEGER,
|
|
-- UNIQUE (hash) ON CONFLICT IGNORE
|
|
-- );
|
|
-- CREATE INDEX IF NOT EXISTS hashes_index__timestamp ON hashes(timestamp);
|
|
-- CREATE INDEX IF NOT EXISTS hashes_index__hash ON hashes(hash);
|