Damien De Paoli
56771308a6
update amendments in tables.sql to include job_id in entry_ammendment added amend.py to move amendment-related code into its own file when we create a job (NewJob) and that job matches an amendmentType (via job_name or job_name:amt <- where amt relates to how we do a transform_image), then we enter a new EntryAmendment pa_job_mgr knows when a Transform job ends, and removes relevant EntryAmendment files*.js use EntryAmendment data to render thumbnails with relevant AmendmentType if a normal page load (like /files_ip), and there is an EntryAmendment, mark up the thumb, run the check jobs to look for completion of the job, removeal of the EntryAmendment and update the entry based on 'transformed' image OVERALL: this is a functioning version that uses EntryAmendments and can handle loading a new page with outstanding amendments and 'deals' with it. This is a good base, but does not cater for remove_files or move_files
210 lines
14 KiB
SQL
210 lines
14 KiB
SQL
ALTER DATABASE pa SET TIMEZONE TO 'Australia/Victoria';
|
|
|
|
CREATE SEQUENCE pa_user_id_seq;
|
|
CREATE SEQUENCE pa_user_state_id_seq;
|
|
CREATE SEQUENCE face_id_seq;
|
|
CREATE SEQUENCE path_id_seq;
|
|
CREATE SEQUENCE path_type_id_seq;
|
|
CREATE SEQUENCE file_id_seq;
|
|
CREATE SEQUENCE file_type_id_seq;
|
|
CREATE SEQUENCE jobextra_id_seq;
|
|
CREATE SEQUENCE joblog_id_seq;
|
|
CREATE SEQUENCE job_id_seq;
|
|
CREATE SEQUENCE person_id_seq;
|
|
CREATE SEQUENCE refimg_id_seq;
|
|
CREATE SEQUENCE settings_id_seq;
|
|
CREATE SEQUENCE pa_job_manager_id_seq;
|
|
CREATE SEQUENCE pa_job_manager_fe_message_id_seq;
|
|
CREATE SEQUENCE face_override_type_id_seq;
|
|
CREATE SEQUENCE face_override_id_seq;
|
|
CREATE SEQUENCE query_id_seq;
|
|
|
|
-- these are hard-coded at present, not sure I can reflexively find models from API?
|
|
CREATE TABLE ai_model ( id INTEGER, name VARCHAR(24), description VARCHAR(80), CONSTRAINT pk_ai_model PRIMARY KEY(id) );
|
|
INSERT INTO ai_model VALUES ( 1, 'hog', 'normal' );
|
|
INSERT INTO ai_model VALUES ( 2, 'cnn', 'more accurate / much slower' );
|
|
|
|
CREATE TABLE settings(
|
|
id INTEGER,
|
|
base_path VARCHAR, import_path VARCHAR, storage_path VARCHAR, recycle_bin_path VARCHAR, metadata_path VARCHAR,
|
|
auto_rotate BOOLEAN,
|
|
default_refimg_model INTEGER, default_scan_model INTEGER, default_threshold FLOAT,
|
|
face_size_limit INTEGER,
|
|
scheduled_import_scan INTEGER, scheduled_storage_scan INTEGER,
|
|
scheduled_bin_cleanup INTEGER, bin_cleanup_file_age INTEGER,
|
|
job_archive_age INTEGER,
|
|
CONSTRAINT pk_settings_id PRIMARY KEY(id),
|
|
CONSTRAINT fk_default_refimg_model FOREIGN KEY (default_refimg_model) REFERENCES ai_model(id),
|
|
CONSTRAINT fk_default_scan_model FOREIGN KEY (default_scan_model) REFERENCES ai_model(id) );
|
|
|
|
CREATE TABLE pa_user(
|
|
id INTEGER,
|
|
dn VARCHAR UNIQUE,
|
|
default_import_noo VARCHAR,
|
|
default_storage_noo VARCHAR,
|
|
default_search_noo VARCHAR,
|
|
default_grouping VARCHAR(16),
|
|
default_how_many INTEGER,
|
|
default_size INTEGER,
|
|
default_import_folders BOOLEAN,
|
|
default_storage_folders BOOLEAN,
|
|
CONSTRAINT pk_pa_user_id PRIMARY KEY(id) );
|
|
|
|
-- FIXME: NEED TO RETHINK THIS, not sure this even needs to be in the DB
|
|
CREATE TABLE pa_user_state ( id INTEGER, pa_user_dn VARCHAR(128), path_type VARCHAR(16),
|
|
noo VARCHAR(16), grouping VARCHAR(16), how_many INTEGER, size INTEGER, folders BOOLEAN,
|
|
root VARCHAR, cwd VARCHAR,
|
|
CONSTRAINT fk_pa_user_dn FOREIGN KEY (pa_user_dn) REFERENCES pa_user(dn),
|
|
CONSTRAINT pk_pa_user_states_id PRIMARY KEY(id ) );
|
|
|
|
CREATE TABLE query ( id INTEGER, path_type VARCHAR(16), noo VARCHAR(16), grouping VARCHAR(16), q_offset INTEGER,
|
|
entry_list VARCHAR, folders BOOLEAN, root VARCHAR, cwd VARCHAR, search_term VARCHAR, current INTEGER,
|
|
created TIMESTAMPTZ,
|
|
CONSTRAINT pk_query_id PRIMARY KEY(id ) );
|
|
|
|
|
|
CREATE TABLE file_type ( id INTEGER, name VARCHAR(32) UNIQUE, CONSTRAINT pk_file_type_id PRIMARY KEY(id) );
|
|
|
|
CREATE TABLE path_type ( id INTEGER, name VARCHAR(16) UNIQUE, CONSTRAINT pk_path_type_id PRIMARY KEY(id) );
|
|
|
|
CREATE TABLE path ( id INTEGER, type_id INTEGER, path_prefix VARCHAR(1024), num_files INTEGER,
|
|
CONSTRAINT pk_path_id PRIMARY KEY(id),
|
|
CONSTRAINT fk_path_type_type_id FOREIGN KEY (type_id) REFERENCES path_type(id) );
|
|
|
|
CREATE TABLE entry( id INTEGER, name VARCHAR(128), type_id INTEGER, exists_on_fs BOOLEAN,
|
|
CONSTRAINT pk_entry_id PRIMARY KEY(id),
|
|
CONSTRAINT fk_file_type_type_id FOREIGN KEY (type_id) REFERENCES file_type(id) );
|
|
|
|
CREATE TABLE file ( eid INTEGER, size_mb INTEGER, hash VARCHAR(34), thumbnail VARCHAR, faces_created_on FLOAT, last_hash_date FLOAT, last_ai_scan FLOAT, year INTEGER, month INTEGER, day INTEGER, woy INTEGER,
|
|
CONSTRAINT pk_file_id PRIMARY KEY(eid),
|
|
CONSTRAINT fk_file_entry_id FOREIGN KEY (eid) REFERENCES entry(id) );
|
|
|
|
CREATE TABLE del_file ( file_eid INTEGER, orig_path_prefix VARCHAR(1024), CONSTRAINT pk_del_file_file_eid PRIMARY KEY (file_eid),
|
|
CONSTRAINT fk_entry_id FOREIGN KEY (file_eid) REFERENCES file(eid) );
|
|
|
|
CREATE TABLE dir ( eid INTEGER, rel_path VARCHAR(256), num_files INTEGER, last_import_date FLOAT,
|
|
CONSTRAINT pk_dir_eid PRIMARY KEY(eid),
|
|
CONSTRAINT fk_dir_entry_id FOREIGN KEY (eid) REFERENCES entry(id) );
|
|
|
|
CREATE TABLE path_dir_link ( PATH_ID INTEGER, DIR_EID INTEGER,
|
|
CONSTRAINT pk_pdl_PATH_ID_DIR_EID PRIMARY KEY (PATH_ID, DIR_EID),
|
|
CONSTRAINT fk_pdl_path_id FOREIGN KEY (path_id) REFERENCES path(id),
|
|
CONSTRAINT fk_pdl_dir_eid FOREIGN KEY (dir_eid) REFERENCES dir(eid) );
|
|
|
|
CREATE TABLE entry_dir_link ( ENTRY_ID INTEGER, DIR_EID INTEGER,
|
|
CONSTRAINT pk_edl_ENTRY_ID_DIR_EID PRIMARY KEY (ENTRY_ID, DIR_EID),
|
|
CONSTRAINT fk_edl_entry_id FOREIGN KEY (entry_id) REFERENCES entry(id),
|
|
CONSTRAINT fk_edl_dir_eid FOREIGN KEY (dir_eid) REFERENCES dir(eid) );
|
|
|
|
CREATE TABLE person ( id INTEGER DEFAULT NEXTVAL('person_id_seq'), tag VARCHAR(48), firstname VARCHAR(48), surname VARCHAR(48),
|
|
CONSTRAINT pk_person_id PRIMARY KEY(id) );
|
|
ALTER SEQUENCE person_id_seq OWNED BY person.id;
|
|
|
|
|
|
CREATE TABLE refimg ( id INTEGER, fname VARCHAR(128), face BYTEA, orig_w INTEGER, orig_h INTEGER,
|
|
face_top INTEGER, face_right INTEGER, face_bottom INTEGER, face_left INTEGER, created_on FLOAT, thumbnail VARCHAR, model_used INTEGER,
|
|
CONSTRAINT pk_refimg_id PRIMARY KEY(id),
|
|
CONSTRAINT fk_refimg_model_used FOREIGN KEY (model_used) REFERENCES ai_model(id) );
|
|
ALTER SEQUENCE refimg_id_seq OWNED BY refimg.id;
|
|
|
|
CREATE TABLE face( id INTEGER, face BYTEA, face_top INTEGER, face_right INTEGER, face_bottom INTEGER, face_left INTEGER,
|
|
w INTEGER, h INTEGER, CONSTRAINT pk_face_id PRIMARY KEY(id) );
|
|
|
|
CREATE TABLE face_file_link( face_id INTEGER, file_eid INTEGER, model_used INTEGER,
|
|
CONSTRAINT pk_ffl_face_id_file_id PRIMARY KEY(face_id, file_eid),
|
|
CONSTRAINT fk_ffl_face_id FOREIGN KEY (face_id) REFERENCES face(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_ffl_file_eid FOREIGN KEY (file_eid) REFERENCES file(eid),
|
|
CONSTRAINT fk_ffl_model_used FOREIGN KEY (model_used) REFERENCES ai_model(id) );
|
|
|
|
CREATE TABLE face_refimg_link( face_id INTEGER, refimg_id INTEGER, face_distance FLOAT,
|
|
CONSTRAINT pk_frl_face_id_refimg_id PRIMARY KEY(face_id, refimg_id),
|
|
CONSTRAINT fk_frl_face_id FOREIGN KEY (face_id) REFERENCES face(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_frl_refimg_id FOREIGN KEY (refimg_id) REFERENCES refimg(id) );
|
|
|
|
CREATE TABLE face_override_type ( id INTEGER, name VARCHAR UNIQUE, CONSTRAINT pk_face_override_type_id PRIMARY KEY(id) );
|
|
INSERT INTO face_override_type VALUES ( (SELECT NEXTVAL('face_override_type_id_seq')), 'Manual match to existing person' );
|
|
INSERT INTO face_override_type VALUES ( (SELECT NEXTVAL('face_override_type_id_seq')), 'Not a face' );
|
|
INSERT INTO face_override_type VALUES ( (SELECT NEXTVAL('face_override_type_id_seq')), 'Too young' );
|
|
INSERT INTO face_override_type VALUES ( (SELECT NEXTVAL('face_override_type_id_seq')), 'Ignore face' );
|
|
|
|
-- keep non-redundant FACE because, when we rebuild data we may have a null FACE_ID, but still want to connect to this override
|
|
-- from a previous AI pass... (would happen if we delete a file and then reimport/scan it), OR, more likely we change (say) a threshold, etc.
|
|
-- any reordering of faces, generates new face_ids... (but if the face data was the same, then this override should stand)
|
|
CREATE TABLE face_no_match_override ( id INTEGER, face_id INTEGER, type_id INTEGER,
|
|
CONSTRAINT fk_fnmo_face_id FOREIGN KEY (face_id) REFERENCES face(id),
|
|
CONSTRAINT fk_fnmo_type FOREIGN KEY (type_id) REFERENCES face_override_type(id),
|
|
CONSTRAINT pk_fnmo_id PRIMARY KEY(id) );
|
|
|
|
-- manual match goes to person not refimg, so on search, etc. we deal with this anomaly (via sql not ORM)
|
|
CREATE TABLE face_force_match_override ( id INTEGER, face_id INTEGER, person_id INTEGER, CONSTRAINT pk_face_force_match_override_id PRIMARY KEY(id) );
|
|
|
|
CREATE TABLE disconnected_no_match_override ( face BYTEA, type_id INTEGER,
|
|
CONSTRAINT fk_dnmo_type_id FOREIGN KEY (type_id) REFERENCES face_override_type(id),
|
|
CONSTRAINT pk_dnmo_face PRIMARY KEY (face) );
|
|
|
|
CREATE TABLE disconnected_force_match_override ( face BYTEA, person_id INTEGER,
|
|
CONSTRAINT fk_dfmo_person_id FOREIGN KEY (person_id) REFERENCES person(id),
|
|
CONSTRAINT pk_dfmo_face PRIMARY KEY (face) );
|
|
|
|
CREATE TABLE person_refimg_link ( person_id INTEGER, refimg_id INTEGER,
|
|
CONSTRAINT pk_prl PRIMARY KEY(person_id, refimg_id),
|
|
CONSTRAINT fk_prl_person_id FOREIGN KEY (person_id) REFERENCES person(id),
|
|
CONSTRAINT fk_prl_refimg_id FOREIGN KEY (refimg_id) REFERENCES refimg(id),
|
|
CONSTRAINT u_prl_refimg_id UNIQUE(refimg_id) );
|
|
|
|
CREATE TABLE job (
|
|
id INTEGER, start_time TIMESTAMPTZ, last_update TIMESTAMPTZ, name VARCHAR(64), state VARCHAR(128),
|
|
num_files INTEGER, current_file_num INTEGER, current_file VARCHAR(256), wait_for INTEGER, pa_job_state VARCHAR(48),
|
|
CONSTRAINT pk_job_id PRIMARY KEY(id) );
|
|
|
|
-- used to pass / keep extra values, e.g. num_files for jobs that have sets of files, or out* for adding output from jobs that you want to pass to next job in the chain
|
|
CREATE TABLE jobextra ( id INTEGER, job_id INTEGER, name VARCHAR(32), value VARCHAR,
|
|
CONSTRAINT pk_jobextra_id PRIMARY KEY(id), CONSTRAINT fk_jobextra_job_id FOREIGN KEY(job_id) REFERENCES job(id) );
|
|
|
|
CREATE TABLE joblog ( id INTEGER, job_id INTEGER, log_date TIMESTAMPTZ, log VARCHAR,
|
|
CONSTRAINT pk_jl_id PRIMARY KEY(id), CONSTRAINT fk_jl_job_id FOREIGN KEY(job_id) REFERENCES job(id) );
|
|
|
|
CREATE TABLE pa_job_manager_fe_message ( id INTEGER, job_id INTEGER, level VARCHAR(16), message VARCHAR(8192), persistent BOOLEAN, cant_close BOOLEAN,
|
|
CONSTRAINT pk_pa_job_manager_fe_acks_id PRIMARY KEY(id),
|
|
CONSTRAINT fk_pa_job_manager_fe_message_job_id FOREIGN KEY(job_id) REFERENCES job(id) );
|
|
|
|
CREATE TABLE amendment_type ( id INTEGER, job_name VARCHAR(64), which VARCHAR(8), what VARCHAR(32), colour VARCHAR(32),
|
|
CONSTRAINT pk_amendment_type_id PRIMARY KEY(id) );
|
|
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 1, 'delete_files', 'icon', 'trash', 'var(--bs-danger)' );
|
|
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 2, 'restore_files', 'icon', 'trash', 'var(--bs-success)' );
|
|
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 3, 'transform_image:90', 'img', 'rot90.png', '#009EFF' );
|
|
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 4, 'transform_image:180', 'img', 'rot180.png', '#009EFF' );
|
|
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 5, 'transform_image:270', 'img', 'rot270.png', '#009EFF' );
|
|
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 6, 'transform_image:fliph', 'icon', 'flip_h', '#009EFF' );
|
|
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 7, 'transform_image:flipv', 'icon', 'flip_v', '#009EFF' );
|
|
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 8, 'move_files', 'icon', 'folder_plus', 'var(--bs-primary)' );
|
|
|
|
CREATE TABLE entry_amendment ( amend_type INTEGER, eid INTEGER, job_id INTEGER,
|
|
CONSTRAINT pk_entry_amendment_eid_job_id PRIMARY KEY(eid,job_id),
|
|
CONSTRAINT fk_entry_amendment_amendment_type FOREIGN KEY(amend_type) REFERENCES amendment_type(id),
|
|
CONSTRAINT fk_entry_amendment_job_id FOREIGN KEY(job_id) REFERENCES job(id) );
|
|
|
|
-- default data for types of paths
|
|
INSERT INTO path_type VALUES ( (SELECT NEXTVAL('path_type_id_seq')), 'Import' );
|
|
INSERT INTO path_type VALUES ( (SELECT NEXTVAL('path_type_id_seq')), 'Storage' );
|
|
INSERT INTO path_type VALUES ( (SELECT NEXTVAL('path_type_id_seq')), 'Bin' );
|
|
INSERT INTO path_type VALUES ( (SELECT NEXTVAL('path_type_id_seq')), 'Metadata' );
|
|
|
|
-- default data for types of files
|
|
INSERT INTO file_type VALUES ( (SELECT NEXTVAL('file_type_id_seq')), 'Image' );
|
|
INSERT INTO file_type VALUES ( (SELECT NEXTVAL('file_type_id_seq')), 'Video' );
|
|
INSERT INTO file_type VALUES ( (SELECT NEXTVAL('file_type_id_seq')), 'Directory' );
|
|
INSERT INTO file_type VALUES ( (SELECT NEXTVAL('file_type_id_seq')), 'Unknown' );
|
|
|
|
-- fake data only for making testing easier
|
|
--INSERT INTO person VALUES ( (SELECT NEXTVAL('person_id_seq')), 'dad', 'Damien', 'De Paoli' );
|
|
--INSERT INTO person VALUES ( (SELECT NEXTVAL('person_id_seq')), 'mum', 'Mandy', 'De Paoli' );
|
|
--INSERT INTO person VALUES ( (SELECT NEXTVAL('person_id_seq')), 'cam', 'Cameron', 'De Paoli' );
|
|
--INSERT INTO person VALUES ( (SELECT NEXTVAL('person_id_seq')), 'mich', 'Michelle', 'De Paoli' );
|
|
-- DEV(ddp):
|
|
INSERT INTO settings ( id, base_path, import_path, storage_path, recycle_bin_path, metadata_path, auto_rotate, default_refimg_model, default_scan_model, default_threshold, face_size_limit, scheduled_import_scan, scheduled_storage_scan, scheduled_bin_cleanup, bin_cleanup_file_age, job_archive_age ) VALUES ( (SELECT NEXTVAL('settings_id_seq')), '/home/ddp/src/photoassistant/', 'images_to_process/', 'photos/', '.pa_bin/', '.pa_metadata/', true, 1, 1, '0.55', 43, 1, 1, 7, 30, 3 );
|
|
-- DEV(cam):
|
|
--INSERT INTO settings ( id, base_path, import_path, storage_path, recycle_bin_path, metadata_path, auto_rotate, default_refimg_model, default_scan_model, default_threshold, face_size_limit, scheduled_import_scan, scheduled_storage_scan, scheduled_bin_cleanup, bin_cleanup_file_age, job_archive_age ) VALUES ( (select nextval('SETTINGS_ID_SEQ')), 'c:/Users/cam/Desktop/code/python/photoassistant/', 'c:\images_to_process', 'photos/', '.pa_bin/', '.pa_metadata/', TRUE, 1, 1, '0.55', 43, 1, 1, 7, 30, 3 );
|
|
-- PROD:
|
|
--INSERT INTO settings ( id, base_path, import_path, storage_path, recycle_bin_path, metadata_path, auto_rotate, default_refimg_model, default_scan_model, default_threshold, face_size_limit, scheduled_import_scan, scheduled_storage_scan, scheduled_bin_cleanup, bin_cleanup_file_age, job_archive_age ) VALUES ( (SELECT NEXTVAL('settings_id_seq')), '/export/docker/storage/', 'Camera_uploads/', 'photos/', '.pa_bin/', '.pa_metadata/', TRUE, 1, 1, '0.55', 43, 1, 1, 7, 30, 4 );
|