photoprism/internal/dbbuilder/sqlite3.sql

CREATE TABLE IF NOT EXISTS "files_share" ("file_id" integer,"service_id" integer,"remote_name" VARBINARY(255),"status" VARBINARY(16),"error" VARBINARY(512),"errors" integer,"created_at" datetime,"updated_at" datetime , PRIMARY KEY ("file_id","service_id","remote_name"));
CREATE TABLE IF NOT EXISTS "photos_labels" ("photo_id" integer,"label_id" integer,"label_src" VARBINARY(8),"uncertainty" SMALLINT , PRIMARY KEY ("photo_id","label_id"));
CREATE INDEX idx_photos_labels_label_id ON "photos_labels"(label_id) ;
CREATE TABLE IF NOT EXISTS "services" ("id" integer primary key autoincrement,"acc_name" VARCHAR(255),"acc_owner" VARCHAR(255),"acc_url" VARBINARY(512),"acc_type" VARBINARY(255),"acc_key" VARBINARY(255),"acc_user" VARBINARY(255),"acc_pass" VARBINARY(255),"acc_error" VARBINARY(512),"acc_errors" integer,"acc_share" bool,"acc_sync" bool,"retry_limit" integer,"share_path" VARBINARY(500),"share_size" VARBINARY(16),"share_expires" integer,"sync_path" VARBINARY(500),"sync_status" VARBINARY(16),"sync_interval" integer,"sync_date" datetime,"sync_upload" bool,"sync_download" bool,"sync_filenames" bool,"sync_raw" bool,"created_at" datetime,"updated_at" datetime,"deleted_at" datetime , "acc_timeout" VARBINARY(16));
CREATE INDEX idx_accounts_deleted_at ON "services"(deleted_at) ;
CREATE TABLE IF NOT EXISTS "photos" ("id" integer primary key autoincrement,"uuid" VARBINARY(42),"taken_at" datetime,"taken_at_local" datetime,"taken_src" VARBINARY(8),"photo_uid" VARBINARY(42),"photo_type" VARBINARY(8) DEFAULT 'image',"type_src" VARBINARY(8),"photo_title" VARCHAR(255),"title_src" VARBINARY(8),"photo_description" TEXT,"description_src" VARBINARY(8),"photo_path" VARBINARY(500),"photo_name" VARBINARY(255),"original_name" VARBINARY(755),"photo_stack" integer,"photo_favorite" bool,"photo_private" bool,"photo_scan" bool,"photo_panorama" bool,"time_zone" VARBINARY(64),"place_id" VARBINARY(42) DEFAULT 'zz',"place_src" VARBINARY(8),"cell_id" VARBINARY(42) DEFAULT 'zz',"cell_accuracy" integer,"photo_altitude" integer,"photo_lat" FLOAT,"photo_lng" FLOAT,"photo_country" VARBINARY(2) DEFAULT 'zz',"photo_year" integer,"photo_month" integer,"photo_day" integer,"photo_iso" integer,"photo_exposure" VARBINARY(64),"photo_f_number" FLOAT,"photo_focal_length" integer,"photo_quality" SMALLINT,"photo_resolution" SMALLINT,"photo_color" integer,"camera_id" integer DEFAULT 1,"camera_serial" VARBINARY(255),"camera_src" VARBINARY(8),"lens_id" integer DEFAULT 1,"created_at" datetime,"updated_at" datetime,"edited_at" datetime,"checked_at" datetime,"deleted_at" datetime , "photo_faces" integer, "estimated_at" datetime, "photo_duration" bigint, "created_by" VARBINARY(42), "published_at" datetime);
CREATE INDEX idx_photos_taken_uid ON "photos"(taken_at, photo_uid) ;
CREATE INDEX idx_photos_cell_id ON "photos"(cell_id) ;
CREATE INDEX idx_photos_photo_lat ON "photos"(photo_lat) ;
CREATE INDEX idx_photos_photo_lng ON "photos"(photo_lng) ;
CREATE INDEX idx_photos_country_year_month ON "photos"(photo_country, photo_year, photo_month) ;
CREATE INDEX idx_photos_checked_at ON "photos"(checked_at) ;
CREATE INDEX idx_photos_deleted_at ON "photos"(deleted_at) ;
CREATE INDEX idx_photos_uuid ON "photos"("uuid") ;
CREATE INDEX idx_photos_path_name ON "photos"(photo_path, photo_name) ;
CREATE INDEX idx_photos_place_id ON "photos"(place_id) ;
CREATE INDEX idx_photos_camera_lens ON "photos"(camera_id, lens_id) ;
CREATE UNIQUE INDEX uix_photos_photo_uid ON "photos"(photo_uid) ;
CREATE TABLE IF NOT EXISTS "details" ("photo_id" integer,"keywords" TEXT,"keywords_src" VARBINARY(8),"notes" TEXT,"notes_src" VARBINARY(8),"subject" VARCHAR(255),"subject_src" VARBINARY(8),"artist" VARCHAR(255),"artist_src" VARBINARY(8),"copyright" VARCHAR(255),"copyright_src" VARBINARY(8),"license" VARCHAR(255),"license_src" VARBINARY(8),"created_at" datetime,"updated_at" datetime , "software" VARCHAR(1024), "software_src" VARBINARY(8), PRIMARY KEY ("photo_id"));
CREATE TABLE IF NOT EXISTS "lenses" ("id" integer primary key autoincrement,"lens_slug" VARBINARY(255),"lens_name" VARCHAR(255),"lens_make" VARCHAR(255),"lens_model" VARCHAR(255),"lens_type" VARCHAR(255),"lens_description" TEXT,"lens_notes" TEXT,"created_at" datetime,"updated_at" datetime,"deleted_at" datetime );
CREATE INDEX idx_lenses_deleted_at ON "lenses"(deleted_at) ;
CREATE UNIQUE INDEX uix_lenses_lens_slug ON "lenses"(lens_slug) ;
CREATE TABLE IF NOT EXISTS "countries" ("id" VARBINARY(2),"country_slug" VARBINARY(255),"country_name" varchar(255),"country_description" TEXT,"country_notes" TEXT,"country_photo_id" integer , PRIMARY KEY ("id"));
CREATE UNIQUE INDEX uix_countries_country_slug ON "countries"(country_slug) ;
CREATE TABLE IF NOT EXISTS "photos_albums" ("photo_uid" VARBINARY(42),"album_uid" VARBINARY(42),"order" integer,"hidden" bool,"missing" bool,"created_at" datetime,"updated_at" datetime , PRIMARY KEY ("photo_uid","album_uid"));
CREATE INDEX idx_photos_albums_album_uid ON "photos_albums"(album_uid) ;
CREATE TABLE IF NOT EXISTS "categories" ("label_id" integer,"category_id" integer, PRIMARY KEY ("label_id","category_id"));
CREATE TABLE IF NOT EXISTS "labels" ("id" integer primary key autoincrement,"label_uid" VARBINARY(42),"label_slug" VARBINARY(255),"custom_slug" VARBINARY(255),"label_name" VARCHAR(255),"label_priority" integer,"label_favorite" bool,"label_description" TEXT,"label_notes" TEXT,"photo_count" integer DEFAULT 1,"created_at" datetime,"updated_at" datetime,"deleted_at" datetime , "thumb" VARBINARY(128) DEFAULT '', "thumb_src" VARBINARY(8) DEFAULT '', "published_at" datetime);
CREATE INDEX idx_labels_custom_slug ON "labels"(custom_slug) ;
CREATE INDEX idx_labels_deleted_at ON "labels"(deleted_at) ;
CREATE UNIQUE INDEX uix_labels_label_uid ON "labels"(label_uid) ;
CREATE UNIQUE INDEX uix_labels_label_slug ON "labels"(label_slug) ;
CREATE TABLE IF NOT EXISTS "photos_keywords" ("photo_id" integer,"keyword_id" integer , PRIMARY KEY ("photo_id","keyword_id"));
CREATE INDEX idx_photos_keywords_keyword_id ON "photos_keywords"(keyword_id) ;
CREATE TABLE IF NOT EXISTS "passwords" ("uid" VARBINARY(255),"hash" VARBINARY(255),"created_at" datetime,"updated_at" datetime , PRIMARY KEY ("uid"));
CREATE TABLE IF NOT EXISTS "duplicates" ("file_name" VARBINARY(755),"file_root" VARBINARY(16) DEFAULT '/',"file_hash" VARBINARY(128) DEFAULT '',"file_size" bigint,"mod_time" bigint , PRIMARY KEY ("file_name","file_root"));
CREATE INDEX idx_duplicates_file_hash ON "duplicates"(file_hash) ;
CREATE TABLE IF NOT EXISTS "places" ("id" VARBINARY(42),"place_label" VARBINARY(755),"place_city" VARCHAR(255),"place_state" VARCHAR(255),"place_country" VARBINARY(2),"place_keywords" VARCHAR(255),"place_favorite" bool,"photo_count" integer DEFAULT 1,"created_at" datetime,"updated_at" datetime , "place_district" VARCHAR(100), PRIMARY KEY ("id"));
CREATE TABLE IF NOT EXISTS "cameras" ("id" integer primary key autoincrement,"camera_slug" VARBINARY(255),"camera_name" VARCHAR(255),"camera_make" VARCHAR(255),"camera_model" VARCHAR(255),"camera_type" VARCHAR(255),"camera_description" TEXT,"camera_notes" TEXT,"created_at" datetime,"updated_at" datetime,"deleted_at" datetime );
CREATE INDEX idx_cameras_deleted_at ON "cameras"(deleted_at) ;
CREATE UNIQUE INDEX uix_cameras_camera_slug ON "cameras"(camera_slug) ;
CREATE TABLE IF NOT EXISTS "keywords" ("id" integer primary key autoincrement,"keyword" VARCHAR(64),"skip" bool );
CREATE INDEX idx_keywords_keyword ON "keywords"("keyword") ;
CREATE TABLE IF NOT EXISTS "folders" ("path" VARBINARY(500),"root" VARBINARY(16) DEFAULT '',"folder_uid" VARBINARY(42),"folder_type" VARBINARY(16),"folder_title" VARCHAR(255),"folder_category" VARCHAR(255),"folder_description" TEXT,"folder_order" VARBINARY(32),"folder_country" VARBINARY(2) DEFAULT 'zz',"folder_year" integer,"folder_month" integer,"folder_day" integer,"folder_favorite" bool,"folder_private" bool,"folder_ignore" bool,"folder_watch" bool,"created_at" datetime,"updated_at" datetime,"modified_at" datetime,"deleted_at" datetime , "published_at" datetime, PRIMARY KEY ("folder_uid"));
CREATE INDEX idx_folders_folder_category ON "folders"(folder_category) ;
CREATE INDEX idx_folders_country_year_month ON "folders"(folder_country, folder_year, folder_month) ;
CREATE INDEX idx_folders_deleted_at ON "folders"(deleted_at) ;
CREATE UNIQUE INDEX idx_folders_path_root ON "folders"("path", "root") ;
CREATE TABLE IF NOT EXISTS "users" ("id" integer primary key autoincrement,"address_id" integer DEFAULT 1,"user_uid" VARBINARY(42),"mother_uid" VARBINARY(42),"father_uid" VARBINARY(42),"global_uid" VARBINARY(42),"full_name" varchar(128),"nick_name" varchar(64),"maiden_name" varchar(64),"artist_name" varchar(64),"user_name" varchar(64),"user_status" varchar(32),"user_disabled" bool,"user_settings" LONGTEXT,"primary_email" varchar(255),"email_confirmed" bool,"backup_email" varchar(255),"person_url" VARBINARY(255),"person_phone" varchar(32),"person_status" varchar(32),"person_avatar" VARBINARY(255),"person_location" varchar(128),"person_bio" TEXT,"person_accounts" LONGTEXT,"business_url" VARBINARY(255),"business_phone" varchar(32),"business_email" varchar(255),"company_name" varchar(128),"department_name" varchar(128),"job_title" varchar(64),"birth_year" integer,"birth_month" integer,"birth_day" integer,"terms_accepted" bool,"is_artist" bool,"is_subject" bool,"role_admin" bool,"role_guest" bool,"role_child" bool,"role_family" bool,"role_friend" bool,"webdav" bool,"storage_path" VARBINARY(500),"can_invite" bool,"invite_token" VARBINARY(32),"invited_by" VARBINARY(32),"confirm_token" VARBINARY(64),"reset_token" VARBINARY(64),"api_token" VARBINARY(128),"api_secret" VARBINARY(128),"login_attempts" integer,"login_at" datetime,"created_at" datetime,"updated_at" datetime,"deleted_at" datetime , "external_id" varchar(255));
CREATE INDEX idx_users_deleted_at ON "users"(deleted_at) ;
CREATE INDEX idx_users_global_uid ON "users"(global_uid) ;
CREATE INDEX idx_users_primary_email ON "users"(primary_email) ;
CREATE UNIQUE INDEX uix_users_user_uid ON "users"(user_uid) ;
CREATE TABLE IF NOT EXISTS "files" ("id" integer primary key autoincrement,"photo_id" integer,"photo_uid" VARBINARY(42),"instance_id" VARBINARY(42),"file_uid" VARBINARY(42),"file_name" VARBINARY(755),"file_root" VARBINARY(16) DEFAULT '/',"original_name" VARBINARY(755),"file_hash" VARBINARY(128),"file_size" bigint,"file_codec" VARBINARY(32),"file_type" VARBINARY(32),"file_mime" VARBINARY(64),"file_primary" bool,"file_sidecar" bool,"file_missing" bool,"file_portrait" bool,"file_video" bool,"file_duration" bigint,"file_width" integer,"file_height" integer,"file_orientation" integer,"file_projection" VARBINARY(16),"file_aspect_ratio" FLOAT,"file_main_color" VARBINARY(16),"file_colors" VARBINARY(9),"file_luminance" VARBINARY(9),"file_diff" integer,"file_chroma" integer,"file_error" VARBINARY(512),"mod_time" bigint,"created_at" datetime,"created_in" bigint,"updated_at" datetime,"updated_in" bigint,"deleted_at" datetime , "photo_taken_at" DATETIME, "time_index" VARBINARY(48), "media_id" VARBINARY(32), "media_utc" bigint, "media_type" VARBINARY(16), "file_fps" real, "file_frames" integer, "file_hdr" bool, "file_watermark" bool, "file_color_profile" VARBINARY(64), "file_software" VARCHAR(64), "published_at" datetime, "file_orientation_src" VARBINARY(8) DEFAULT '');
CREATE INDEX idx_files_instance_id ON "files"(instance_id) ;
CREATE INDEX idx_files_file_hash ON "files"(file_hash) ;
CREATE INDEX idx_files_file_main_color ON "files"(file_main_color) ;
CREATE INDEX idx_files_deleted_at ON "files"(deleted_at) ;
CREATE INDEX idx_files_photo_id ON "files"(photo_id) ;
CREATE INDEX idx_files_photo_uid ON "files"(photo_uid) ;
CREATE UNIQUE INDEX uix_files_file_uid ON "files"(file_uid) ;
CREATE UNIQUE INDEX idx_files_name_root ON "files"(file_name, file_root) ;
CREATE TABLE IF NOT EXISTS "files_sync" ("remote_name" VARBINARY(255),"service_id" integer,"file_id" integer,"remote_date" datetime,"remote_size" bigint,"status" VARBINARY(16),"error" VARBINARY(512),"errors" integer,"created_at" datetime,"updated_at" datetime , PRIMARY KEY ("remote_name","service_id"));
CREATE INDEX idx_files_sync_file_id ON "files_sync"(file_id) ;
CREATE TABLE IF NOT EXISTS "cells" ("id" VARBINARY(42),"cell_name" VARCHAR(255),"cell_category" VARCHAR(64),"place_id" VARBINARY(42) DEFAULT 'zz',"created_at" datetime,"updated_at" datetime , "cell_street" VARCHAR(100), "cell_postcode" VARCHAR(50), PRIMARY KEY ("id"));
CREATE TABLE IF NOT EXISTS "albums" ("id" integer primary key autoincrement,"album_uid" VARBINARY(42),"cover_uid" VARBINARY(42),"folder_uid" VARBINARY(42),"album_slug" VARBINARY(255),"album_path" VARBINARY(500),"album_type" VARBINARY(8) DEFAULT 'album',"album_title" VARCHAR(255),"album_location" VARCHAR(255),"album_category" VARCHAR(255),"album_caption" TEXT,"album_description" TEXT,"album_notes" TEXT,"album_filter" VARBINARY(1024),"album_order" VARBINARY(32),"album_template" VARBINARY(255),"album_country" VARBINARY(2) DEFAULT 'zz',"album_year" integer,"album_month" integer,"album_day" integer,"album_favorite" bool,"album_private" bool,"created_at" datetime,"updated_at" datetime,"deleted_at" datetime , "parent_uid" VARBINARY(42) DEFAULT '', "thumb" VARBINARY(128) DEFAULT '', "thumb_src" VARBINARY(8) DEFAULT '', "album_state" VARCHAR(100), "created_by" VARBINARY(42), "published_at" datetime);
CREATE INDEX idx_albums_album_category ON "albums"(album_category) ;
CREATE INDEX idx_albums_country_year_month ON "albums"(album_country, album_year, album_month) ;
CREATE INDEX idx_albums_deleted_at ON "albums"(deleted_at) ;
CREATE INDEX idx_albums_folder_uid ON "albums"(folder_uid) ;
CREATE INDEX idx_albums_album_slug ON "albums"(album_slug) ;
CREATE INDEX idx_albums_album_path ON "albums"(album_path) ;
CREATE UNIQUE INDEX uix_albums_album_uid ON "albums"(album_uid) ;
CREATE TABLE IF NOT EXISTS "links" ("link_uid" VARBINARY(42),"share_uid" VARBINARY(42),"share_slug" VARBINARY(255),"link_token" VARBINARY(255),"link_expires" integer,"link_views" integer,"max_views" integer,"has_password" bool,"can_comment" bool,"can_edit" bool,"created_at" datetime,"modified_at" datetime , "comment" varchar(512), "perm" integer, "ref_id" VARBINARY(16), "created_by" VARBINARY(42), PRIMARY KEY ("link_uid"));
CREATE INDEX idx_links_share_slug ON "links"(share_slug) ;
CREATE UNIQUE INDEX idx_links_uid_token ON "links"(share_uid, link_token) ;
CREATE TABLE IF NOT EXISTS "errors" ("id" integer primary key autoincrement,"error_time" datetime,"error_level" VARBINARY(32),"error_message" VARBINARY(2048) );
CREATE INDEX idx_errors_error_time ON "errors"(error_time) ;
CREATE INDEX idx_labels_thumb ON "labels"("thumb") ;
CREATE TABLE IF NOT EXISTS "markers" ("marker_uid" VARBINARY(42),"file_uid" VARBINARY(42) DEFAULT '',"marker_type" VARBINARY(8) DEFAULT '',"marker_src" VARBINARY(8) DEFAULT '',"marker_name" VARCHAR(255),"marker_review" bool,"marker_invalid" bool,"subj_uid" VARBINARY(42),"subj_src" VARBINARY(8) DEFAULT '',"face_id" VARBINARY(42),"face_dist" real DEFAULT -1,"embeddings_json" MEDIUMBLOB,"landmarks_json" MEDIUMBLOB,"x" FLOAT,"y" FLOAT,"w" FLOAT,"h" FLOAT,"q" integer,"size" integer DEFAULT -1,"score" SMALLINT,"thumb" VARBINARY(128) DEFAULT '',"matched_at" datetime,"created_at" datetime,"updated_at" datetime , PRIMARY KEY ("marker_uid"));
CREATE INDEX idx_markers_subj_uid_src ON "markers"(subj_uid, subj_src) ;
CREATE INDEX idx_markers_face_id ON "markers"(face_id) ;
CREATE INDEX idx_markers_thumb ON "markers"("thumb") ;
CREATE INDEX idx_markers_matched_at ON "markers"(matched_at) ;
CREATE INDEX idx_markers_file_uid ON "markers"(file_uid) ;
CREATE INDEX idx_photos_ymd ON "photos"(photo_day) ;
CREATE INDEX idx_albums_thumb ON "albums"("thumb") ;
CREATE INDEX idx_albums_album_title ON "albums"(album_title) ;
CREATE INDEX idx_albums_ymd ON "albums"(album_day) ;
CREATE TABLE IF NOT EXISTS "subjects" ("subj_uid" VARBINARY(42),"subj_type" VARBINARY(8) DEFAULT '',"subj_src" VARBINARY(8) DEFAULT '',"subj_slug" VARBINARY(255) DEFAULT '',"subj_name" VARCHAR(255) DEFAULT '',"subj_alias" VARCHAR(255) DEFAULT '',"subj_bio" TEXT,"subj_notes" TEXT,"subj_favorite" bool DEFAULT false,"subj_private" bool DEFAULT false,"subj_excluded" bool DEFAULT false,"file_count" integer DEFAULT 0,"thumb" VARBINARY(128) DEFAULT '',"thumb_src" VARBINARY(8) DEFAULT '',"metadata_json" MEDIUMBLOB,"created_at" datetime,"updated_at" datetime,"deleted_at" datetime , "subj_hidden" bool DEFAULT false, "photo_count" integer DEFAULT 0, "subj_about" varchar(512), PRIMARY KEY ("subj_uid"));
CREATE INDEX idx_subjects_subj_slug ON "subjects"(subj_slug) ;
CREATE INDEX idx_subjects_thumb ON "subjects"("thumb") ;
CREATE INDEX idx_subjects_deleted_at ON "subjects"(deleted_at) ;
CREATE UNIQUE INDEX uix_subjects_subj_name ON "subjects"(subj_name) ;
CREATE TABLE IF NOT EXISTS "faces" ("id" VARBINARY(42),"face_src" VARBINARY(8),"face_hidden" bool,"subj_uid" VARBINARY(42) DEFAULT '',"samples" integer,"sample_radius" real,"collisions" integer,"collision_radius" real,"embedding_json" MEDIUMBLOB,"matched_at" datetime,"created_at" datetime,"updated_at" datetime , "face_kind" integer, PRIMARY KEY ("id"));
CREATE INDEX idx_faces_subj_uid ON "faces"(subj_uid) ;
CREATE TABLE IF NOT EXISTS "migrations" ("id" varchar(16),"dialect" varchar(16),"error" varchar(255),"source" varchar(16),"started_at" datetime,"finished_at" datetime , "stage" varchar(16), PRIMARY KEY ("id"));
CREATE INDEX idx_places_place_district ON "places"(place_district) ;
CREATE INDEX idx_places_place_city ON "places"(place_city) ;
CREATE INDEX idx_places_place_state ON "places"(place_state) ;
CREATE INDEX idx_albums_album_state ON "albums"(album_state) ;
CREATE INDEX idx_files_photo_taken_at ON "files"(photo_taken_at) ;
CREATE INDEX idx_files_media_utc ON "files"(media_utc) ;
CREATE INDEX idx_albums_album_filter ON albums (album_filter);
CREATE UNIQUE INDEX idx_files_search_media ON files (media_id);
CREATE UNIQUE INDEX idx_files_search_timeline ON files (time_index);
CREATE INDEX idx_services_deleted_at ON "services"(deleted_at) ;
CREATE TABLE IF NOT EXISTS "photos_users" ("uid" VARBINARY(42),"user_uid" VARBINARY(42),"team_uid" VARBINARY(42),"perm" integer , PRIMARY KEY ("uid","user_uid"));
CREATE INDEX idx_photos_users_user_uid ON "photos_users"(user_uid) ;
CREATE INDEX idx_photos_users_team_uid ON "photos_users"(team_uid) ;
CREATE TABLE IF NOT EXISTS "auth_users_shares" ("user_uid" VARBINARY(42),"share_uid" VARBINARY(42),"link_uid" VARBINARY(42),"expires_at" datetime,"comment" varchar(512),"perm" integer,"ref_id" VARBINARY(16),"created_at" datetime,"updated_at" datetime , PRIMARY KEY ("user_uid","share_uid"));
CREATE INDEX idx_auth_users_shares_share_uid ON "auth_users_shares"(share_uid) ;
CREATE INDEX idx_auth_users_shares_expires_at ON "auth_users_shares"(expires_at) ;
CREATE TABLE IF NOT EXISTS "auth_users_details" ("user_uid" VARBINARY(42),"subj_uid" VARBINARY(42),"subj_src" VARBINARY(8) DEFAULT '',"place_id" VARBINARY(42) DEFAULT 'zz',"place_src" VARBINARY(8),"cell_id" VARBINARY(42) DEFAULT 'zz',"birth_year" integer,"birth_month" integer,"birth_day" integer,"name_title" varchar(32),"given_name" varchar(64),"middle_name" varchar(64),"family_name" varchar(64),"name_suffix" varchar(32),"nick_name" varchar(64),"name_src" VARBINARY(8),"user_gender" varchar(16),"user_about" varchar(512),"user_bio" varchar(512),"user_location" varchar(512),"user_country" VARBINARY(2),"user_phone" varchar(32),"site_url" VARBINARY(512),"profile_url" VARBINARY(512),"feed_url" VARBINARY(512),"avatar_url" VARBINARY(512),"org_title" varchar(64),"org_name" varchar(128),"org_email" varchar(255),"org_phone" varchar(32),"org_url" VARBINARY(512),"id_url" VARBINARY(512),"created_at" datetime,"updated_at" datetime , PRIMARY KEY ("user_uid"));
CREATE INDEX idx_auth_users_details_subj_uid ON "auth_users_details"(subj_uid) ;
CREATE INDEX idx_auth_users_details_place_id ON "auth_users_details"(place_id) ;
CREATE INDEX idx_auth_users_details_cell_id ON "auth_users_details"(cell_id) ;
CREATE INDEX idx_auth_users_details_org_email ON "auth_users_details"(org_email) ;
CREATE TABLE IF NOT EXISTS "auth_sessions" ("id" VARBINARY(2048),"client_ip" varchar(64),"user_uid" VARBINARY(42) DEFAULT '',"user_name" varchar(64),"auth_provider" VARBINARY(128) DEFAULT '',"auth_method" VARBINARY(128) DEFAULT '',"auth_domain" VARBINARY(255) DEFAULT '',"auth_id" VARBINARY(128) DEFAULT '',"auth_scope" varchar(1024) DEFAULT '',"last_active" bigint,"sess_expires" bigint,"sess_timeout" bigint,"preview_token" VARBINARY(64) DEFAULT '',"download_token" VARBINARY(64) DEFAULT '',"access_token" VARBINARY(4096) DEFAULT '',"refresh_token" VARBINARY(512) DEFAULT '',"id_token" VARBINARY(1024) DEFAULT '',"user_agent" varchar(512),"data_json" VARBINARY(4096),"ref_id" VARBINARY(16) DEFAULT '',"login_ip" varchar(64),"login_at" datetime,"created_at" datetime,"updated_at" datetime , "client_uid" VARBINARY(42) DEFAULT '', "client_name" varchar(200) DEFAULT '', "grant_type" VARBINARY(64) DEFAULT '', PRIMARY KEY ("id"));
CREATE INDEX idx_auth_sessions_client_ip ON "auth_sessions"(client_ip) ;
CREATE INDEX idx_auth_sessions_user_uid ON "auth_sessions"(user_uid) ;
CREATE INDEX idx_auth_sessions_user_name ON "auth_sessions"(user_name) ;
CREATE INDEX idx_auth_sessions_auth_id ON "auth_sessions"(auth_id) ;
CREATE INDEX idx_auth_sessions_sess_expires ON "auth_sessions"(sess_expires) ;
CREATE TABLE IF NOT EXISTS "auth_users_settings" ("user_uid" VARBINARY(42),"ui_theme" VARBINARY(32),"ui_language" VARBINARY(32),"ui_time_zone" VARBINARY(64),"maps_style" VARBINARY(32),"maps_animate" integer,"index_path" VARBINARY(1024),"index_rescan" integer,"import_path" VARBINARY(1024),"import_move" integer,"upload_path" VARBINARY(1024),"default_page" VARBINARY(128),"created_at" datetime,"updated_at" datetime , "download_originals" integer DEFAULT 0, "download_media_raw" integer DEFAULT 0, "download_media_sidecar" integer DEFAULT 0, PRIMARY KEY ("user_uid"));
CREATE INDEX idx_photos_published_at ON "photos"(published_at) ;
CREATE INDEX idx_photos_created_by ON "photos"(created_by) ;
CREATE TABLE IF NOT EXISTS "auth_users" ("id" integer primary key autoincrement,"user_uuid" VARBINARY(64),"user_uid" VARBINARY(42),"auth_provider" VARBINARY(128) DEFAULT '',"auth_method" VARBINARY(128) DEFAULT '',"auth_issuer" VARBINARY(255) DEFAULT '',"auth_id" VARBINARY(128) DEFAULT '',"user_name" varchar(64),"display_name" varchar(200),"user_email" varchar(255),"backup_email" varchar(255),"user_role" varchar(64) DEFAULT '',"user_attr" varchar(1024),"super_admin" bool,"can_login" bool,"login_at" datetime,"expires_at" datetime,"webdav" bool,"base_path" VARBINARY(1024),"upload_path" VARBINARY(1024),"can_invite" bool,"invite_token" VARBINARY(64),"invited_by" varchar(64),"verify_token" VARBINARY(64),"verified_at" datetime,"consent_at" datetime,"born_at" datetime,"reset_token" VARBINARY(64),"preview_token" VARBINARY(64),"download_token" VARBINARY(64),"thumb" VARBINARY(128) DEFAULT '',"thumb_src" VARBINARY(8) DEFAULT '',"ref_id" VARBINARY(16),"created_at" datetime,"updated_at" datetime,"deleted_at" datetime);
CREATE INDEX idx_auth_users_auth_id ON "auth_users"(auth_id) ;
CREATE INDEX idx_auth_users_user_email ON "auth_users"(user_email) ;
CREATE INDEX idx_auth_users_invite_token ON "auth_users"(invite_token) ;
CREATE INDEX idx_auth_users_born_at ON "auth_users"(born_at) ;
CREATE INDEX idx_auth_users_thumb ON "auth_users"("thumb") ;
CREATE INDEX idx_auth_users_deleted_at ON "auth_users"(deleted_at) ;
CREATE INDEX idx_auth_users_user_uuid ON "auth_users"(user_uuid) ;
CREATE INDEX idx_auth_users_expires_at ON "auth_users"(expires_at) ;
CREATE INDEX idx_auth_users_user_name ON "auth_users"(user_name) ;
CREATE UNIQUE INDEX uix_auth_users_user_uid ON "auth_users"(user_uid) ;
CREATE INDEX idx_files_published_at ON "files"(published_at) ;
CREATE TABLE IF NOT EXISTS "reactions" ("uid" VARBINARY(42),"user_uid" VARBINARY(42),"reaction" VARBINARY(64),"reacted" integer,"reacted_at" datetime , PRIMARY KEY ("uid","user_uid","reaction"));
CREATE INDEX idx_reactions_reacted_at ON "reactions"(reacted_at) ;
CREATE INDEX idx_folders_published_at ON "folders"(published_at) ;
CREATE INDEX idx_labels_published_at ON "labels"(published_at) ;
CREATE INDEX idx_albums_created_by ON "albums"(created_by) ;
CREATE INDEX idx_albums_published_at ON "albums"(published_at) ;
CREATE TABLE IF NOT EXISTS "albums_users" ("uid" VARBINARY(42),"user_uid" VARBINARY(42),"team_uid" VARBINARY(42),"perm" integer , PRIMARY KEY ("uid","user_uid"));
CREATE INDEX idx_albums_users_user_uid ON "albums_users"(user_uid) ;
CREATE INDEX idx_albums_users_team_uid ON "albums_users"(team_uid) ;
CREATE INDEX idx_links_created_by ON "links"(created_by) ;
CREATE INDEX idx_files_missing_root ON files (file_missing, file_root);
CREATE TABLE IF NOT EXISTS "versions" ("id" integer primary key autoincrement,"version" varchar(255),"edition" varchar(255),"error" varchar(255),"created_at" datetime,"updated_at" datetime,"migrated_at" datetime );
CREATE UNIQUE INDEX idx_version_edition ON "versions"("version", "edition") ;
CREATE INDEX idx_files_file_error ON "files"(file_error) ;
CREATE TABLE IF NOT EXISTS "auth_clients" ("client_uid" VARBINARY(42),"user_uid" VARBINARY(42) DEFAULT '',"user_name" varchar(200),"client_name" varchar(200),"client_role" varchar(64) DEFAULT '',"client_type" VARBINARY(16),"client_url" VARBINARY(255) DEFAULT '',"callback_url" VARBINARY(255) DEFAULT '',"auth_provider" VARBINARY(128) DEFAULT '',"auth_method" VARBINARY(128) DEFAULT '',"auth_scope" varchar(1024) DEFAULT '',"auth_expires" bigint,"auth_tokens" bigint,"auth_enabled" bool,"last_active" bigint,"created_at" datetime,"updated_at" datetime , PRIMARY KEY ("client_uid"));
CREATE INDEX idx_auth_clients_user_name ON "auth_clients"(user_name) ;
CREATE INDEX idx_auth_clients_user_uid ON "auth_clients"(user_uid) ;
CREATE TABLE IF NOT EXISTS "passcodes" ("uid" VARBINARY(255),"key_type" varchar(64) DEFAULT '',"key_url" varchar(2048) DEFAULT '',"recovery_code" varchar(255) DEFAULT '',"verified_at" datetime,"activated_at" datetime,"created_at" datetime,"updated_at" datetime , PRIMARY KEY ("uid","key_type"));
CREATE INDEX idx_auth_sessions_client_uid ON "auth_sessions"(client_uid) ;