746e5d6b5c
* Schema revisions for new import flow and thumbnails * WIP settings * WIP quick schema fix * gallery: Image search using ML embeddings Still very rough around the edges, but basically works. 'uv' gets auto-installed, but currently requires restarting Timelinize before it can be used. Lots of tunings and optimizations are needed. There is much room for improvement. Still migrating from imports -> jobs, so that part of the code and schema is still a mess. * Implement search for similar items * Finish import/planning rewrite; it compiles and tests pass * Fix some bugs, probably introduce other bugs * WIP new import planning page * Fix Google Photos and Twitter recognition * Finish most of import page UI; start button still WIP * WIP: Start Import button * Fixes to jobs, thumbnail job, import job, etc. * Implement proper checkpointing support; jobs fixes
51 lines
2.4 KiB
SQL
51 lines
2.4 KiB
SQL
/*
|
|
Timelinize
|
|
Copyright (c) 2013 Matthew Holt
|
|
|
|
This program is free software: you can redistribute it and/or modify
|
|
it under the terms of the GNU Affero General Public License as published
|
|
by the Free Software Foundation, either version 3 of the License, or
|
|
(at your option) any later version.
|
|
|
|
This program is distributed in the hope that it will be useful,
|
|
but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
GNU Affero General Public License for more details.
|
|
|
|
You should have received a copy of the GNU Affero General Public License
|
|
along with this program. If not, see <https://www.gnu.org/licenses/>.
|
|
*/
|
|
|
|
-- This database is only for storing thumbnails (small previews) of items in the
|
|
-- timeline. It is ancillary to the timeline, and deleting this database is not
|
|
-- harmful to the integrity of the timeline as it can be regenerated.
|
|
|
|
-- likely to perform faster for medium-large blobs: https://www.sqlite.org/intern-v-extern-blob.html
|
|
PRAGMA page_size = 16384;
|
|
|
|
-- consisting of a single row, the ID of the repo this belongs to
|
|
CREATE TABLE IF NOT EXISTS "repo_link" (
|
|
"repo_id" TEXT PRIMARY KEY
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "thumbnails" (
|
|
"id" INTEGER PRIMARY KEY,
|
|
|
|
-- only one of the following two columns should have a value:
|
|
"data_file" TEXT UNIQUE COLLATE NOCASE, -- the data file in the repo this is a preview for (same as items.data_file in the main schema)
|
|
"item_data_id" INTEGER UNIQUE, -- only used if the item does not have a data file (same as item_data.id in the main schema)
|
|
|
|
"generated" INTEGER NOT NULL DEFAULT (unixepoch()), -- when the thumbnail was generated (timestamp in unix seconds UTC)
|
|
"mime_type" TEXT NOT NULL, -- since we don't have file extensions to give us a hint, the content-type of this thumbnail/preview
|
|
"content" BLOB NOT NULL-- the actual bytes of the thumbnail/preview
|
|
);
|
|
|
|
-- ensure this DB remains linked to only one timeline repo
|
|
-- TODO: Ensure this trigger is correct; doing BEFORE INSERT results in errors because it happens before the primary keys is checked (even with OR IGNORE), but is raising AFTER INSERT sufficient to prevent/undo the insert when the table already has a different entry?
|
|
CREATE TRIGGER IF NOT EXISTS only_one_linked_repo
|
|
AFTER INSERT ON repo_link
|
|
FOR EACH ROW
|
|
BEGIN
|
|
SELECT RAISE(ABORT, 'cannot link more than one timeline repository')
|
|
WHERE (SELECT count() FROM repo_link) > 1;
|
|
END;
|