From f9534d319eb45cadefa9a2227eca7b3d56c0a597 Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Fri, 16 Feb 2018 11:08:08 -0500 Subject: [PATCH] common tables that will be used by gsheet --- mig-sql/init/020_common_tables.sql | 146 ++++++++++++++++++++++++++++++++++++ 1 files changed, 146 insertions(+), 0 deletions(-) create mode 100644 mig-sql/init/020_common_tables.sql diff --git a/mig-sql/init/020_common_tables.sql b/mig-sql/init/020_common_tables.sql new file mode 100644 index 0000000..7a375a6 --- /dev/null +++ b/mig-sql/init/020_common_tables.sql @@ -0,0 +1,146 @@ +CREATE TABLE map_create_shelving_location ( + id SERIAL + ,owning_lib TEXT + ,location_name TEXT + ,opac_visible TEXT + ,checkin_alert TEXT + ,holdable TEXT + ,circulate TEXT + ,note TEXT +); + +INSERT INTO gsheet_tracked_table + (worksheet_name,table_name,tab_name,created) +VALUES + ('mig_schema','map_create_shelving_location','New Copy Locations',NOW()) +; + +INSERT INTO gsheet_tracked_column + (table_id,column_name) +VALUES + ((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Copy Locations'),'owning_lib') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Copy Locations'),'location_name') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Copy Locations'),'opac_visible') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Copy Locations'),'checkin_alert') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Copy Locations'),'holdable') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Copy Locations'),'circulate') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Copy Locations'),'note') +; + +CREATE TABLE map_create_account ( + id SERIAL + ,usrname TEXT + ,first_name TEXT + ,family_name TEXT + ,email TEXT + ,password TEXT + ,home_library TEXT + ,profile1 TEXT + ,profile2 TEXT + ,profile3 TEXT + ,work_ou TEXT + ,note TEXT + ,note2 TEXT +); + +INSERT INTO gsheet_tracked_table + (worksheet_name,table_name,tab_name,created) +VALUES + ('mig_schema','map_create_account','New Accounts',NOW()) +; + +INSERT INTO gsheet_tracked_column + (table_id,column_name) +VALUES + ((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Accounts'),'usrname') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Accounts'),'first_name') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Accounts'),'family_name') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Accounts'),'email') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Accounts'),'password') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Accounts'),'home_library') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Accounts'),'profile1') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Accounts'),'profile2') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Accounts'),'profile3') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Accounts'),'work_ou') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Accounts'),'note') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'New Accounts'),'note2') +; + + +CREATE TABLE map_threshold ( + id SERIAL + ,profile TEXT + ,checkout_threshold TEXT + ,fine_threshold TEXT + ,overdue_threshold TEXT + ,note TEXT +); + +INSERT INTO gsheet_tracked_table + (worksheet_name,table_name,tab_name,created) +VALUES + ('mig_schema','map_threshold','Patron Thresholds',NOW()) +; + +INSERT INTO gsheet_tracked_column + (table_id,column_name) +VALUES + ((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Patron Thresholds'),'profile') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Patron Thresholds'),'checkout_threshold') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Patron Thresholds'),'fine_threshold') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Patron Thresholds'),'overdue_threshold') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Patron Thresholds'),'note') +; + + +CREATE TABLE map_misc ( + id SERIAL + ,x_count TEXT + ,option TEXT + ,choice TEXT + ,value TEXT + ,note TEXT +); + +INSERT INTO gsheet_tracked_table + (worksheet_name,table_name,tab_name,created) +VALUES + ('mig_schema','map_misc','Miscellaneous Options',NOW()) +; + +INSERT INTO gsheet_tracked_column + (table_id,column_name) +VALUES + ((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Miscellaneous Options'),'x_count') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Miscellaneous Options'),'option') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Miscellaneous Options'),'Choice') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Miscellaneous Options'),'value') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Miscellaneous Options'),'note') +; + +CREATE TABLE map_org_setting ( + id SERIAL + ,name TEXT + ,label TEXT + ,entry_type TEXT + ,org_unit TEXT + ,value TEXT + ,note TEXT +); + +INSERT INTO gsheet_tracked_table + (worksheet_name,table_name,tab_name,created) +VALUES + ('mig_schema','map_org_setting','Org Settings',NOW()) +; + +INSERT INTO gsheet_tracked_column + (table_id,column_name) +VALUES + ((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Org Settings'),'name') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Org Settings'),'label') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Org Settings'),'entry_type') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Org Settings'),'org_unit') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Org Settings'),'value') + ,((SELECT id FROM gsheet_tracked_table WHERE worksheet_name = 'mig_schema' AND tab_name = 'Org Settings'),'note') +; -- 1.7.2.5