X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=mig-sql%2Finit%2F020_common_tables.sql;h=1ad339df20498e0121436bd7df0e471b676b8c66;hp=ec69043fe2243cff76bb99fb97ec1b1977eb2970;hb=8f3c70aa5aaeb1c4742c4d15da7fc535035b3a2e;hpb=fca643f7be010d29c08d481fa0bd794382088b9c diff --git a/mig-sql/init/020_common_tables.sql b/mig-sql/init/020_common_tables.sql index ec69043..1ad339d 100644 --- a/mig-sql/init/020_common_tables.sql +++ b/mig-sql/init/020_common_tables.sql @@ -1,68 +1,96 @@ CREATE TABLE map_hold_policies ( - l_org_unit TEXT - ,l_user_group TEXT - ,l_circ_mod TEXT - ,l_holdable TEXT - ,l_max_holds TEXT - ,l_include_frozen TEXT - ,l_transit_range TEXT - ,l_range_from TEXT - ,l_notes TEXT - ,x_org_unit INTEGER - ,x_grp_id INTEGER - ,x_migrate BOOLEAN DEFAULT TRUE -) INHERITS (config.hold_matrix_matchpoint); + l_user_home_ou TEXT + ,l_request_ou TEXT + ,l_item_owning_ou TEXT + ,l_item_circ_ou TEXT + ,l_requestor_grp TEXT + ,l_circ_modifier TEXT + ,l_active TEXT + ,l_holdable TEXT + ,l_max_holds TEXT + ,l_includes_frozen_holds TEXT + ,l_distance_is_from_owner TEXT + ,l_transit_range TEXT + ,l_usr_grp TEXT + ,x_user_home_ou INTEGER + ,x_request_ou INTEGER + ,x_item_owning_ou INTEGER + ,x_item_circ_ou INTEGER + ,x_requestor_grp INTEGER + ,x_circ_modifier TEXT + ,x_active BOOLEAN + ,x_holdable BOOLEAN + ,x_max_holds INTEGER + ,x_includes_frozen_holds BOOLEAN + ,x_distance_is_from_owner BOOLEAN + ,x_transit_range INTEGER + ,x_usr_grp INTEGER + ,x_migrate BOOLEAN DEFAULT TRUE +); INSERT INTO gsheet_tracked_table - (table_name,tab_name,created) + (sheet_name,table_name,tab_name,created) VALUES - ('map_hold_policies','Hold Policies',NOW()) + ((SELECT SPLIT_PART(reset_val,',',1) FROM pg_settings WHERE name = 'search_path'),'map_hold_policies','Hold Policies',NOW()) ; INSERT INTO gsheet_tracked_column (table_id,column_name) VALUES - ((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_org_unit') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_user_group') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_circ_mod') + ((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_user_home_ou') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_request_ou') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_item_owning_ou') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_item_circ_ou') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_requestor_grp') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_circ_modifier') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_active') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_holdable') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_max_holds') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_include_frozen') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_includes_frozen_holds') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_distance_is_from_owner') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_transit_range') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_range_from') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_notes') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Hold Policies'),'l_usr_grp') ; + CREATE TABLE map_circ_policies ( l_org_unit TEXT ,l_user_group TEXT + ,l_copy_owning_lib TEXT + ,l_user_home_lib TEXT ,l_circ_mod TEXT ,l_copy_location TEXT ,l_circulate TEXT ,l_circ_limit_set TEXT ,l_duration_rule TEXT - ,l_renewals TEXT - ,l_fine TEXT - ,l_fine_interval TEXT - ,l_grace_period TEXT - ,l_max_fine TEXT + ,l_fine_rule TEXT ,l_grace_override TEXT - ,l_renewal_boolean TEXT + ,l_max_fine TEXT ,l_notes TEXT ,x_org_unit INTEGER - ,x_grp_id INTEGER + ,x_user_group INTEGER + ,x_copy_owning_lib INTEGER + ,x_user_home_lib INTEGER + ,x_circ_mod TEXT ,x_copy_location INTEGER - ,x_circ_limit INTEGER - ,x_duration INTEGER + ,x_circulate BOOLEAN + ,x_circ_limit_set INTEGER + ,x_duration_rule INTEGER + ,x_fine_rule INTEGER + ,x_grace_override INTERVAL ,x_max_fine INTEGER - ,x_fine_interval INTEGER - ,x_migrate INTEGER -) INHERITS (config.circ_matrix_matchpoint); + ,x_circ_limit_quantity INTEGER + ,x_circ_limit_parts INTEGER + ,x_circ_limit_ou_name TEXT + ,x_circ_limit_ou_id INTEGER + ,x_circ_limit_id INTEGER + ,x_migrate BOOLEAN DEFAULT FALSE +); INSERT INTO gsheet_tracked_table - (table_name,tab_name,created) + (sheet_name,table_name,tab_name,created) VALUES - ('map_circ_policies','Circ Policies',NOW()) + ((SELECT SPLIT_PART(reset_val,',',1) FROM pg_settings WHERE name = 'search_path'),'map_circ_policies','Circ Policies',NOW()) ; INSERT INTO gsheet_tracked_column @@ -70,21 +98,54 @@ INSERT INTO gsheet_tracked_column VALUES ((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_org_unit') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_user_group') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_copy_owning') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_user_home_lib') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_circ_mod') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_copy_location') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_circulate') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_circ_limit_set') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_duration_rule') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_renewals') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_fine') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_fine_interval') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_grace_period') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_max_fine') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_fine_rule') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_grace_override') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_renewal_boolean') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_max_fine') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Policies'),'l_notes') ; +CREATE TABLE map_circ_limit_sets ( + l_owning_lib TEXT + ,l_name TEXT + ,l_items_out TEXT + ,l_depth TEXT + ,l_global TEXT + ,l_description TEXT + ,l_circ_mod TEXT + ,l_copy_loc TEXT + ,x_owning_lib INTEGER + ,x_name TEXT + ,x_items_out INTEGER + ,x_global BOOLEAN + ,x_depth INTEGER + ,x_description TEXT +); + +INSERT INTO gsheet_tracked_table + (sheet_name,table_name,tab_name,created) +VALUES + ((SELECT SPLIT_PART(reset_val,',',1) FROM pg_settings WHERE name = 'search_path'),'map_circ_limit_sets','Circ Limit Sets',NOW()) +; + +INSERT INTO gsheet_tracked_column + (table_id,column_name) +VALUES + ((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Limit Sets'),'l_owning_lib') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Limit Sets'),'l_name') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Limit Sets'),'l_items_out') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Limit Sets'),'l_depth') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Limit Sets'),'l_global') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Limit Sets'),'l_description') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Limit Sets'),'l_circ_mod') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Circ Limit Sets'),'l_copy_loc') +; CREATE TABLE map_create_shelving_location ( l_id SERIAL @@ -100,9 +161,9 @@ CREATE TABLE map_create_shelving_location ( ) INHERITS (asset_copy_location); INSERT INTO gsheet_tracked_table - (table_name,tab_name,created) + (sheet_name,table_name,tab_name,created) VALUES - ('map_create_shelving_location','New Copy Locations',NOW()) + ((SELECT SPLIT_PART(reset_val,',',1) FROM pg_settings WHERE name = 'search_path'),'map_create_shelving_location','New Copy Locations',NOW()) ; INSERT INTO gsheet_tracked_column @@ -119,6 +180,7 @@ VALUES CREATE TABLE map_create_account ( l_id SERIAL + ,l_barcode TEXT ,l_usrname TEXT ,l_first_name TEXT ,l_family_name TEXT @@ -128,22 +190,34 @@ CREATE TABLE map_create_account ( ,l_profile1 TEXT ,l_profile2 TEXT ,l_profile3 TEXT - ,l_work_ou TEXT + ,l_work_ou1 TEXT + ,l_work_ou2 TEXT + ,l_work_ou3 TEXT + ,l_work_ou4 TEXT + ,l_work_ou5 TEXT + ,l_work_ou6 TEXT + ,l_work_ou7 TEXT + ,l_work_ou8 TEXT + ,l_work_ou9 TEXT + ,l_work_ou10 TEXT + ,l_work_ou11 TEXT + ,l_work_ou12 TEXT + ,l_work_ou13 TEXT ,l_note TEXT - ,l_note2 TEXT ,x_migrate BOOLEAN NOT NULL DEFAULT TRUE -) INHERITS (actor_usr); +); INSERT INTO gsheet_tracked_table - (table_name,tab_name,created) + (sheet_name,table_name,tab_name,created) VALUES - ('map_create_account','New Accounts',NOW()) + ((SELECT SPLIT_PART(reset_val,',',1) FROM pg_settings WHERE name = 'search_path'),'map_create_account','New Accounts',NOW()) ; INSERT INTO gsheet_tracked_column (table_id,column_name) VALUES ((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_usrname') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_barcode') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_first_name') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_family_name') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_email') @@ -152,14 +226,25 @@ VALUES ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_profile1') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_profile2') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_profile3') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou1') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou2') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou3') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou4') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou5') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou6') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou7') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou8') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou9') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou10') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou11') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou12') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_work_ou13') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_note') - ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'New Accounts'),'l_note2') ; - CREATE TABLE map_threshold ( id SERIAL + ,library TEXT ,profile TEXT ,checkout_threshold TEXT ,fine_threshold TEXT @@ -168,15 +253,16 @@ CREATE TABLE map_threshold ( ); INSERT INTO gsheet_tracked_table - (table_name,tab_name,created) + (sheet_name,table_name,tab_name,created) VALUES - ('map_threshold','Patron Thresholds',NOW()) + ((SELECT SPLIT_PART(reset_val,',',1) FROM pg_settings WHERE name = 'search_path'),'map_threshold','Patron Thresholds',NOW()) ; INSERT INTO gsheet_tracked_column (table_id,column_name) VALUES ((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Patron Thresholds'),'profile') + ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Patron Thresholds'),'library') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Patron Thresholds'),'checkout_threshold') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Patron Thresholds'),'fine_threshold') ,((SELECT id FROM gsheet_tracked_table WHERE tab_name = 'Patron Thresholds'),'overdue_threshold') @@ -194,9 +280,9 @@ CREATE TABLE map_misc ( ); INSERT INTO gsheet_tracked_table - (table_name,tab_name,created) + (sheet_name,table_name,tab_name,created) VALUES - ('map_misc','Miscellaneous Options',NOW()) + ((SELECT SPLIT_PART(reset_val,',',1) FROM pg_settings WHERE name = 'search_path'),'map_misc','Miscellaneous Options',NOW()) ; INSERT INTO gsheet_tracked_column @@ -220,9 +306,9 @@ CREATE TABLE map_org_setting ( ); INSERT INTO gsheet_tracked_table - (table_name,tab_name,created) + (sheet_name,table_name,tab_name,created) VALUES - ('map_org_setting','Org Settings',NOW()) + ((SELECT SPLIT_PART(reset_val,',',1) FROM pg_settings WHERE name = 'search_path'),'map_org_setting','Org Settings',NOW()) ; INSERT INTO gsheet_tracked_column @@ -248,9 +334,9 @@ CREATE TABLE map_bib_manipulations ( ); INSERT INTO gsheet_tracked_table - (table_name,tab_name,created) + (sheet_name,table_name,tab_name,created) VALUES - ('map_bib_manipulations','Bib Records',NOW()) + ((SELECT SPLIT_PART(reset_val,',',1) FROM pg_settings WHERE name = 'search_path'),'map_bib_manipulations','Bib Records',NOW()) ; INSERT INTO gsheet_tracked_column