CREATE TABLE version ( version INTEGER NOT NULL DEFAULT 0, dirty BOOL NOT NULL DEFAULT false, _only_row BOOL NOT NULL UNIQUE CHECK(_only_row) DEFAULT true ); INSERT INTO version (version, dirty) VALUES (0, false); CREATE TABLE person( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, firstname TEXT NOT NULL, lastname TEXT NOT NULL, username TEXT NOT NULL, email TEXT NOT NULL DEFAULT '', password TEXT NOT NULL DEFAULT '', -- CHECK (CASE when email is null then password is not null when password is null then email is not null), password_attempts_remaining INT, email_confirmed BOOL NOT NULL DEFAULT false, approved BOOL NOT NULL DEFAULT false, max_businesses INT ); CREATE TRIGGER IF NOT EXISTS person_upd_trig AFTER UPDATE ON person BEGIN update person SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE person_session( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, token TEXT NOT NULL, expires_at INTEGER NOT NULL, person_id INTEGER REFERENCES person(id) ); CREATE TRIGGER IF NOT EXISTS person_session_upd_trig AFTER UPDATE ON person_session BEGIN update person_session SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE person_request( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- {user_approve|password_reset|change_email|confirm_email} request_type TEXT NOT NULL, request_value TEXT NOT NULL DEFAULT '', request_text TEXT NOT NULL DEFAULT '', response_text TEXT NOT NULL DEFAULT '', -- e.g. for confirm email challenge_code TEXT NOT NULL DEFAULT '', -- {'requested'|'granted'|'denied'|'more_info_needed'} response_status TEXT NOT NULL DEFAULT 'requested', person_id INTEGER REFERENCES person(id) ); CREATE TRIGGER IF NOT EXISTS person_request_upd_trig AFTER UPDATE ON person_request BEGIN update person_request SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE role( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- business_owner|new_person_approver|site_admin name TEXT NOT NULL ); INSERT INTO role (name) VALUES ('business_owner'), ('new_person_approver'), ('site_admin'); CREATE TRIGGER IF NOT EXISTS role_upd_trig AFTER UPDATE ON role BEGIN update role SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE person_role( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, person_id INTEGER REFERENCES person(id), role_id INTEGER REFERENCES role(id) ); CREATE TRIGGER IF NOT EXISTS person_role_upd_trig AFTER UPDATE ON person_role BEGIN update person_role SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE social_template ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- e.g. youtube, facebook, instagram name TEXT NOT NULL UNIQUE, template TEXT NOT NULL, svg_b64 TEXT NOT NULL DEFAULT '', svg_color TEXT NOT NULL DEFAULT '' ); CREATE TRIGGER IF NOT EXISTS social_template_upd_trig AFTER UPDATE ON social_template BEGIN update social_template SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE business ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name TEXT NOT NULL CHECK (length(name)>0), slug TEXT NOT NULL, address TEXT NOT NULL DEFAULT '', summary TEXT NOT NULL DEFAULT '', phone_number TEXT NOT NULL DEFAULT '', email TEXT NOT NULL DEFAULT '', external_link TEXT NOT NULL DEFAULT '', background_pic BYTEA, -- <binary picture blob, not > 5MB (resize) > header_pic BYTEA, -- <binary picture blob, not > 5MB (resize) > background_pic_location TEXT NOT NULL DEFAULT '', background_color TEXT NOT NULL DEFAULT '', text_color TEXT NOT NULL DEFAULT '', header_color TEXT NOT NULL DEFAULT '', show_person_name BOOL NOT NULL DEFAULT false, person_id INTEGER REFERENCES person(id), UNIQUE(name, person_id), UNIQUE(slug, person_id) ); -- note on storage: -- 10MB per business * 100 businesses = 1GB CREATE TRIGGER IF NOT EXISTS business_upd_trig AFTER UPDATE ON business BEGIN update business SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE business_social( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, business_id INTEGER REFERENCES business(id), social_template_id INTEGER REFERENCES social_template(id), social_tag_name TEXT NOT NULL, UNIQUE(business_id, social_template_id, social_tag_name) ); CREATE TRIGGER IF NOT EXISTS business_social_upd_trig AFTER UPDATE ON business_social BEGIN update business_social SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE site_config ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, title TEXT NOT NULL DEFAULT 'Bizdex', support_email TEXT NOT NULL DEFAULT '', support_phone TEXT NOT NULL DEFAULT '', purpose TEXT NOT NULL DEFAULT '', disclaimer TEXT NOT NULL DEFAULT '', max_default_businesses INTEGER NOT NULL DEFAULT 5, listen_address TEXT NOT NULL DEFAULT '0.0.0.0', port INTEGER NOT NULL DEFAULT 8080, signup_needs_approve BOOL NOT NULL DEFAULT true, locale_map_pic BYTEA, session_length_max INTEGER NOT NULL DEFAULT 86400, email_confirm BOOL NOT NULL DEFAULT false, email_required BOOL NOT NULL DEFAULT false, email_regex TEXT NOT NULL DEFAULT '.@.', -- fips140 requires 16 bytes or more password_salt_length INTEGER NOT NULL DEFAULT 32 CHECK(password_salt_length >= 16), -- fips140 requires 14 bytes or more password_key_length INTEGER NOT NULL DEFAULT 32 CHECK(password_key_length >= 14), password_rounds INTEGER NOT NULL DEFAULT 1000, -- 'SHA2_256', 'SHA3_256', 'SHA3_512' supported -- (32 bytes), (32 bytes), (64 bytes) password_hash_strategy TEXT NOT NULL DEFAULT 'SHA2_256', password_attempts INTEGER NOT NULL DEFAULT 20, password_min INTEGER NOT NULL DEFAULT 8, password_regex TEXT NOT NULL DEFAULT '[A-Za-z]{1,}@@@[0-9]{1,}@@@[!@#$%^&*().,<>;:{}?]{1,}', password_help TEXT NOT NULL DEFAULT 'password must have 8 characters, one uppercase, one lowercase, one number, and one special character ("!", "@", "#", "$", "%", "^", "&", "*", "(", ")", ".", ",", "<", ">", ";", ":", "{", "}", "?")', password_required BOOL NOT NULL DEFAULT false, log_level TEXT NOT NULL DEFAULT 'info', log_level_key TEXT NOT NULL DEFAULT 'lvl', log_level_overrides TEXT NOT NULL DEFAULT '', log_time_format TEXT NOT NULL DEFAULT '', log_time_key TEXT NOT NULL DEFAULT '', log_add_source BOOL NOT NULL DEFAULT false, person_style_text_color TEXT NOT NULL DEFAULT '#000000', person_style_text_dark_color TEXT NOT NULL DEFAULT '#FFFFFF', person_style_background_color TEXT NOT NULL DEFAULT '#FFFFFF', person_style_background_dark_color TEXT NOT NULL DEFAULT '#000000', -- once daily to minimize load config_reload_seconds INTEGER NOT NULL DEFAULT 86400, _only_row BOOL NOT NULL UNIQUE CHECK(_only_row) DEFAULT true ); INSERT INTO site_config (_only_row) VALUES (true); CREATE TRIGGER IF NOT EXISTS site_config_upd_trig AFTER UPDATE ON site_config BEGIN update site_config SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE site_usage_limits ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name TEXT NOT NULL DEFAULT '', noun TEXT NOT NULL DEFAULT '', verb TEXT NOT NULL DEFAULT '', subject TEXT NOT NULL DEFAULT '', -- e.g. -- drop_blocks: '45.45.45.0/24,86.86.0.0/16' extra TEXT NOT NULL DEFAULT '', -- e.g. drop_blocks extra_type TEXT NOT NULL DEFAULT '', -- form tbd, possibly a json string windows TEXT NOT NULL DEFAULT '', UNIQUE(name) ); CREATE TRIGGER IF NOT EXISTS site_usage_limits_upd_trig AFTER UPDATE ON site_usage_limits BEGIN update site_usage_limits SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE business_taxonomy_a ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name UNQIUE ); CREATE TRIGGER IF NOT EXISTS business_taxonomy_a_upd_trig AFTER UPDATE ON business_taxonomy_a BEGIN update business_taxonomy_a SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE business_taxonomy_b ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name TEXT NOT NULL, business_taxonomy_a_id INTEGER REFERENCES business_taxonomy_a(id), UNIQUE(name, business_taxonomy_a_id) ); CREATE TRIGGER IF NOT EXISTS business_taxonomy_b_upd_trig AFTER UPDATE ON business_taxonomy_b BEGIN update business_taxonomy_b SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE business_taxonomy ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, business_id INTEGER REFERENCES business(id), business_taxonomy_b_id INTEGER REFERENCES business_taxonomy_b(id), UNIQUE(business_id, business_taxonomy_b_id) ); CREATE TRIGGER IF NOT EXISTS business_taxonomy_upd_trig AFTER UPDATE ON business_taxonomy BEGIN update business_taxonomy SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; -- Site admin should create locales for all the places of interest to support -- Business should only add locales in which they perform services -- E.g. for West Texas, this could be something like -- Alpine -- Balmorhea -- Fort Davis -- Fort Stockton -- Marathon -- Marfa -- Pecos -- Presidio -- Sanderson -- Terlingua -- Valentine -- Van Horn CREATE TABLE locale ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, name TEXT NOT NULL UNIQUE, -- 0,0 == top left of image locale_map_pic_x INT NOT NULL DEFAULT 0, locale_map_pic_y INT NOT NULL DEFAULT 0, locale_maps_link TEXT NOT NULL DEFAULT '' ); CREATE TRIGGER IF NOT EXISTS locale_upd_trig AFTER UPDATE ON locale BEGIN update locale SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE business_locale ( id INTEGER PRIMARY KEY, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, business_id INTEGER NOT NULL REFERENCES business(id), locale_id INTEGER NOT NULL REFERENCES locale(id), -- e.g. business is allowed to be in multiple locales, but not same locale twice UNIQUE(business_id, locale_id) ); CREATE TRIGGER IF NOT EXISTS business_locale_upd_trig AFTER UPDATE ON business_locale BEGIN update business_locale SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; UPDATE version SET version=1, dirty=false; -- DOWN: -- DROP TABLE IF EXISTS business_locale; -- DROP TABLE IF EXISTS locale; -- DROP TABLE IF EXISTS business_taxonomy; -- DROP TABLE IF EXISTS business_taxonomy_b; -- DROP TABLE IF EXISTS business_taxonomy_a; -- DROP TABLE IF EXISTS site_usage_limits; -- DROP TABLE IF EXISTS site_config; -- DROP TABLE IF EXISTS business_social; -- DROP TABLE IF EXISTS business; -- DROP TABLE IF EXISTS social_template; -- DROP TABLE IF EXISTS person_role; -- DROP TABLE IF EXISTS role; -- DROP TABLE IF EXISTS person_request; -- DROP TABLE IF EXISTS person; -- DROP TABLE IF EXISTS version;