From b25dcd79fad7b40f8facc6d63decad2fdad74b9b Mon Sep 17 00:00:00 2001 From: Jason Etheridge Date: Mon, 18 Nov 2019 12:11:10 -0500 Subject: [PATCH] m_ prefix for staging tables --- mig-sql/init/020_common_tables.sql | 2 +- mig-xml/evergreen_staged_report.xml | 344 +++++++++++++++++----------------- mig-xml/mapping_reports.xml | 158 ++++++++-------- sql/base/00-infrastructure.sql | 16 +- 4 files changed, 260 insertions(+), 260 deletions(-) diff --git a/mig-sql/init/020_common_tables.sql b/mig-sql/init/020_common_tables.sql index 1ad339d..99b3ce4 100644 --- a/mig-sql/init/020_common_tables.sql +++ b/mig-sql/init/020_common_tables.sql @@ -158,7 +158,7 @@ CREATE TABLE map_create_shelving_location ( ,l_note TEXT ,x_migrate BOOLEAN NOT NULL DEFAULT TRUE ,x_shelf INTEGER -) INHERITS (asset_copy_location); +) INHERITS (m_asset_copy_location); INSERT INTO gsheet_tracked_table (sheet_name,table_name,tab_name,created) diff --git a/mig-xml/evergreen_staged_report.xml b/mig-xml/evergreen_staged_report.xml index d3a4cf6..eb40428 100644 --- a/mig-xml/evergreen_staged_report.xml +++ b/mig-xml/evergreen_staged_report.xml @@ -34,7 +34,7 @@ money arbitrary tags, each should go in separate tag Migrated Billings title used in the asciidoc output Number of Billings.Migrated.Total period delimited - SELECT COUNT(id),x_migrate::TEXT,SUM(amount) FROM money_billing_legacy GROUP BY 2; query itself, will be replaced + SELECT COUNT(id),x_migrate::TEXT,SUM(amount) FROM m_money_billing_legacy GROUP BY 2; query itself, will be replaced Arbitrary note that can be included in the entries. --> @@ -46,8 +46,8 @@ 0 Migrated Circulations Circulation Status.Count of Circs - SELECT 'Closed Circulations', COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NOT NULL AND x_migrate - UNION ALL SELECT 'Open Circulations', COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate + SELECT 'Closed Circulations', COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NOT NULL AND x_migrate + UNION ALL SELECT 'Open Circulations', COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate @@ -56,8 +56,8 @@ 0 Open Un-migrated Circulations Circulation Status.Count of Circs - SELECT 'No Matching User', COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate = FALSE AND usr IS NULL - UNION ALL SELECT 'No Matching Item', COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate = FALSE AND target_copy IS NULL + SELECT 'No Matching User', COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate = FALSE AND usr IS NULL + UNION ALL SELECT 'No Matching Item', COUNT(id) FROM m_action_circulation_legacy WHERE xact_finish IS NULL AND x_migrate = FALSE AND target_copy IS NULL @@ -66,7 +66,7 @@ 1 Open Circulations Circulation Status.Count of Circs - SELECT 'Closed Circulations', COUNT(id) FROM action_circulation WHERE xact_finish IS NOT NULL UNION ALL SELECT 'Open Circulations', COUNT(id) FROM action_circulation WHERE xact_finish IS NULL + SELECT 'Closed Circulations', COUNT(id) FROM m_action_circulation WHERE xact_finish IS NOT NULL UNION ALL SELECT 'Open Circulations', COUNT(id) FROM m_action_circulation WHERE xact_finish IS NULL @@ -84,7 +84,7 @@ 0 Circulations by Org Unit Circulations Count.Migrated.Org Unit - SELECT COUNT(acirc.id), acirc.x_migrate::TEXT, aou.name FROM action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2, 3 + SELECT COUNT(acirc.id), acirc.x_migrate::TEXT, aou.name FROM m_action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2, 3 @@ -93,7 +93,7 @@ 1 Circulations by Org Unit Circulations Count.Org Unit - SELECT COUNT(acirc.id), aou.name FROM action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2 + SELECT COUNT(acirc.id), aou.name FROM m_action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2 @@ -102,7 +102,7 @@ 0 Migrated Circulations by Duration, Fine and Max Fine Count of Circs.Duration.Fine.Max Fine.Migrated - SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule, x_migrate::TEXT FROM action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4, 5 + SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule, x_migrate::TEXT FROM m_action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4, 5 @@ -111,7 +111,7 @@ 1 Circulations by Duration, Fine and Max Fine Count of Circs.Duration.Fine.Max Fine - SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule FROM action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 + SELECT COUNT(id), duration_rule, recurring_fine_rule, max_fine_rule FROM m_action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 @@ -120,7 +120,7 @@ 0 Circulations by Rules and Patron Group Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints - SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name, x.buildrows FROM action_circulation_legacy acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 + SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name, x.buildrows FROM m_action_circulation_legacy acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM m_action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 @@ -129,7 +129,7 @@ 1 Circulations by Rules and Patron Group Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints - SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name, x.buildrows FROM action_circulation acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM action_circulation acirc) x ON x.id = acirc.id GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 + SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, pgt.name, x.buildrows FROM m_action_circulation acirc JOIN actor.usr au ON au.id = acirc.usr JOIN permission.grp_tree pgt ON pgt.id = au.profile JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM m_action_circulation acirc) x ON x.id = acirc.id GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 @@ -138,7 +138,7 @@ 0 Circulations by Rules and Circulation Modifier Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints - SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier, x.buildrows FROM action_circulation_legacy acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 + SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier, x.buildrows FROM m_action_circulation_legacy acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM m_action_circulation_legacy acirc WHERE acirc.x_migrate = TRUE) x ON x.id = acirc.id WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 @@ -147,7 +147,7 @@ 1 Circulations by Rules and Circulation Modifier Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints - SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier, x.buildrows FROM action_circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM action_circulation acirc) x ON x.id = acirc.id + SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, ac.circ_modifier, x.buildrows FROM m_action_circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(find_cmm(acirc.id),',') AS buildrows FROM m_action_circulation acirc) x ON x.id = acirc.id GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 @@ -157,7 +157,7 @@ 0 Circulations by Rules and Org Unit Count of Circs.Duration.Fine.Max Fine.Library Branch - SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, aou.name FROM action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5 + SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, aou.name FROM m_action_circulation_legacy acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.x_migrate = TRUE GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5 @@ -166,7 +166,7 @@ 1 Circulations by Rules and Org Unit Count of Circs.Duration.Fine.Max Fine.Library Branch - SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, aou.name FROM action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5 + SELECT COUNT(acirc.id), acirc.duration_rule, acirc.recurring_fine_rule, acirc.max_fine_rule, aou.name FROM m_action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib GROUP BY 2, 3, 4, 5 ORDER BY 2, 3, 4, 5 @@ -175,7 +175,7 @@ 0 Non-Cataloged Circulation Circulations Count.Migrated - SELECT COUNT(id), x_migrate::TEXT FROM action_non_cataloged_circulation_legacy GROUP BY 2 + SELECT COUNT(id), x_migrate::TEXT FROM m_action_non_cataloged_circulation_legacy GROUP BY 2 @@ -184,7 +184,7 @@ 1 Non-Cataloged Circulation Circulations Count - SELECT COUNT(id) FROM action_non_cataloged_circulation + SELECT COUNT(id) FROM m_action_non_cataloged_circulation @@ -193,7 +193,7 @@ 0 In House Use In House Use Records.Migrated - SELECT COUNT(id), x_migrate::TEXT FROM action_in_house_use_legacy GROUP BY 2 + SELECT COUNT(id), x_migrate::TEXT FROM m_action_in_house_use_legacy GROUP BY 2 @@ -202,7 +202,7 @@ 1 In House Use In House Use Records - SELECT COUNT(id) FROM action_in_house_use + SELECT COUNT(id) FROM m_action_in_house_use @@ -211,12 +211,12 @@ 1 Circs Missing Rules Count.Field Missing - SELECT COUNT(id), 'Duration Rule Value' FROM action_circulation WHERE duration IS NULL - UNION ALL SELECT COUNT(id), 'Recurring Fine Rule Value' FROM action_circulation WHERE recurring_fine IS NULL - UNION ALL SELECT COUNT(id), 'Max Fine Rule Value' FROM action_circulation WHERE max_fine IS NULL - UNION ALL SELECT COUNT(id), 'Duration Rule' FROM action_circulation WHERE duration_rule IS NULL - UNION ALL SELECT COUNT(id), 'Recurring Fine Rule' FROM action_circulation WHERE recurring_fine_rule IS NULL - UNION ALL SELECT COUNT(id), 'Max Fine Rule' FROM action_circulation WHERE max_fine_rule IS NULL + SELECT COUNT(id), 'Duration Rule Value' FROM m_action_circulation WHERE duration IS NULL + UNION ALL SELECT COUNT(id), 'Recurring Fine Rule Value' FROM m_action_circulation WHERE recurring_fine IS NULL + UNION ALL SELECT COUNT(id), 'Max Fine Rule Value' FROM m_action_circulation WHERE max_fine IS NULL + UNION ALL SELECT COUNT(id), 'Duration Rule' FROM m_action_circulation WHERE duration_rule IS NULL + UNION ALL SELECT COUNT(id), 'Recurring Fine Rule' FROM m_action_circulation WHERE recurring_fine_rule IS NULL + UNION ALL SELECT COUNT(id), 'Max Fine Rule' FROM m_action_circulation WHERE max_fine_rule IS NULL @@ -226,7 +226,7 @@ 0 Open Circulation and Status of Linked Items Count.Status - SELECT COUNT(acirc.id), ccs.name FROM action.circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE acirc.xact_finish IS NULL AND acirc.checkin_time IS NULL AND acirc.id IN (SELECT id FROM action_circulation) GROUP BY 2 ORDER BY 2 + SELECT COUNT(acirc.id), ccs.name FROM action.circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE acirc.xact_finish IS NULL AND acirc.checkin_time IS NULL AND acirc.id IN (SELECT id FROM m_action_circulation) GROUP BY 2 ORDER BY 2 @@ -237,7 +237,7 @@ 0 Migrated Holds Hold Type.Hold Count.Migrated - SELECT 'Closed Holds', COUNT(id), x_migrate::TEXT FROM action_hold_request_legacy WHERE (expire_time::TIMESTAMP < now()) OR cancel_time IS NOT NULL OR fulfillment_time IS NOT NULL GROUP BY 3 UNION ALL SELECT 'Open Holds', COUNT(id), x_migrate::TEXT FROM action_hold_request_legacy WHERE (expire_time IS NULL OR expire_time::TIMESTAMP > now()) AND cancel_time IS NULL AND fulfillment_time IS NULL GROUP BY 3 + SELECT 'Closed Holds', COUNT(id), x_migrate::TEXT FROM m_action_hold_request_legacy WHERE (expire_time::TIMESTAMP < now()) OR cancel_time IS NOT NULL OR fulfillment_time IS NOT NULL GROUP BY 3 UNION ALL SELECT 'Open Holds', COUNT(id), x_migrate::TEXT FROM m_action_hold_request_legacy WHERE (expire_time IS NULL OR expire_time::TIMESTAMP > now()) AND cancel_time IS NULL AND fulfillment_time IS NULL GROUP BY 3 @@ -246,7 +246,7 @@ 0 Migrated Holds By Type Hold Type.Hold Count.Migrated - SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM action_hold_request_legacy GROUP BY 1, 3 + SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM m_action_hold_request_legacy GROUP BY 1, 3 @@ -255,7 +255,7 @@ 0 Transit Copy Records and Status of Linked Items Count.Status - SELECT COUNT(atc.id), ccs.name FROM action.transit_copy atc JOIN asset.copy ac ON ac.id = atc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE atc.id IN (SELECT id FROM action_transit_copy) AND atc.dest_recv_time IS NULL GROUP BY 2 ORDER BY 2 + SELECT COUNT(atc.id), ccs.name FROM action.transit_copy atc JOIN asset.copy ac ON ac.id = atc.target_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE atc.id IN (SELECT id FROM m_action_transit_copy) AND atc.dest_recv_time IS NULL GROUP BY 2 ORDER BY 2 @@ -264,7 +264,7 @@ 0 Status of Items with Count of Open In Transits Count.Status.Count of Open Transits - SELECT COUNT(ac.id), ccs.name, SUM(CASE WHEN atc.id IS NULL THEN 0 ELSE 1 END) FROM asset.copy ac JOIN config.copy_status ccs ON ccs.id = ac.status LEFT JOIN (SELECT * FROM action.transit_copy WHERE id IN (SELECT id FROM action_transit_copy) AND dest_recv_time IS NULL) atc ON atc.target_copy = ac.id WHERE ac.id IN (SELECT id from asset_copy) GROUP BY 2 ORDER BY 2 + SELECT COUNT(ac.id), ccs.name, SUM(CASE WHEN atc.id IS NULL THEN 0 ELSE 1 END) FROM asset.copy ac JOIN config.copy_status ccs ON ccs.id = ac.status LEFT JOIN (SELECT * FROM action.transit_copy WHERE id IN (SELECT id FROM m_action_transit_copy) AND dest_recv_time IS NULL) atc ON atc.target_copy = ac.id WHERE ac.id IN (SELECT id from m_asset_copy) GROUP BY 2 ORDER BY 2 @@ -273,7 +273,7 @@ 0 Captured Holds with Status of Items Count of Captured Hold.Status of Item - SELECT COUNT(ahr.id), ccs.name FROM action.hold_request ahr JOIN asset.copy ac ON ac.id = ahr.current_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE ahr.capture_time IS NOT NULL AND ahr.fulfillment_time IS NULL and ahr.cancel_time IS NULL AND ahr.id IN (SELECT id FROM action_hold_request) GROUP BY 2 ORDER By 2 + SELECT COUNT(ahr.id), ccs.name FROM action.hold_request ahr JOIN asset.copy ac ON ac.id = ahr.current_copy JOIN config.copy_status ccs ON ccs.id = ac.status WHERE ahr.capture_time IS NOT NULL AND ahr.fulfillment_time IS NULL and ahr.cancel_time IS NULL AND ahr.id IN (SELECT id FROM m_action_hold_request) GROUP BY 2 ORDER By 2 @@ -282,7 +282,7 @@ 0 Depth of Unfilled Holds Count.Depth - SELECT COUNT(ahr.id), ahr.selection_depth FROM action.hold_request ahr WHERE ahr.id IN (SELECT id FROM action_hold_request) AND ahr.cancel_time IS NULL AND ahr.capture_time IS NULL AND ahr.fulfillment_time IS NULL GROUP BY 2 ORDER BY 2 + SELECT COUNT(ahr.id), ahr.selection_depth FROM action.hold_request ahr WHERE ahr.id IN (SELECT id FROM m_action_hold_request) AND ahr.cancel_time IS NULL AND ahr.capture_time IS NULL AND ahr.fulfillment_time IS NULL GROUP BY 2 ORDER BY 2 @@ -292,7 +292,7 @@ 0 Holds by Title Matchs Count.Number of Matches - SELECT COUNT(*), x_title_matches FROM action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() GROUP BY 2 ORDER BY 2 + SELECT COUNT(*), x_title_matches FROM m_action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() GROUP BY 2 ORDER BY 2 @@ -301,7 +301,7 @@ 0 Sample of Holds with No Matches Borrower Barcode.Title.Request Date - SELECT l_borrower_id, l_title, l_request_date FROM action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() AND x_title_matches = 0 AND x_migrate LIMIT 20 + SELECT l_borrower_id, l_title, l_request_date FROM m_action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() AND x_title_matches = 0 AND x_migrate LIMIT 20 @@ -310,376 +310,376 @@ 0 Sample of Holds with More Than One Title Match Borrower Barcode.Title.Request Date - SELECT l_borrower_id, l_title, l_request_date FROM action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() AND x_title_matches > 1 AND x_migrate LIMIT 20 + SELECT l_borrower_id, l_title, l_request_date FROM m_action_hold_request_legacy WHERE TO_DATE(l_expire_date,'MM/DD/YYYY') > NOW() AND x_title_matches > 1 AND x_migrate LIMIT 20 - asset_copy_count + m_asset_copy_count Count of Copies by Library assets 0 Copy Count.Library.Migrated - SELECT COUNT(ac.id), aou.name, ac.x_migrate::TEXT FROM asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(ac.id), aou.name, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3 ORDER BY 2, 3 - asset_deleted_copies + m_asset_deleted_copies Deleted Copies assets 0 Copy Count.Deleted.Migrated - SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM asset_copy_legacy ac GROUP BY 2, 3 + SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac GROUP BY 2, 3 - asset_copies_by_status + m_asset_copies_by_status Copies by Status assets 0 Copy Count.Status.Migrated - SELECT COUNT(ac.id), cs.name, ac.x_migrate::TEXT FROM asset_copy_legacy ac JOIN config.copy_status cs ON cs.id = ac.status GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(ac.id), cs.name, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac JOIN config.copy_status cs ON cs.id = ac.status GROUP BY 2, 3 ORDER BY 2, 3 - asset_precat + m_asset_precat Precats (AKA Copies Without Bibs) assets 0 Copy Count.Migating - SELECT COUNT(ac.id), ac.x_migrate::TEXT FROM asset_copy_legacy ac WHERE ac.x_bre_id = -1 GROUP BY 2 + SELECT COUNT(ac.id), ac.x_migrate::TEXT FROM m_asset_copy_legacy ac WHERE ac.x_bre_id = -1 GROUP BY 2 - asset_circ_mod_copies_count + m_asset_circ_mod_copies_count Copies by Circulation Modifier assets 0 Copy Count.Circulation Modifier.Migrated - SELECT COUNT(ac.id), ac.circ_modifier, ac.x_migrate::TEXT FROM asset_copy_legacy ac GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(ac.id), ac.circ_modifier, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac GROUP BY 2, 3 ORDER BY 2, 3 - asset_copy_notes + m_asset_copy_notes Copy Notes assets 0 Note Count.Public.Migrated - SELECT COUNT(acnote.id), acnote.pub::TEXT, acnote.x_migrate::TEXT FROM asset_copy_note_legacy acnote GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(acnote.id), acnote.pub::TEXT, acnote.x_migrate::TEXT FROM m_asset_copy_note_legacy acnote GROUP BY 2, 3 ORDER BY 2, 3 - asset_copy_notes + m_asset_copy_notes Copy Notes assets 1 Note Count.Public - SELECT COUNT(acnote.id), acnote.pub::TEXT FROM asset_copy_note acnote GROUP BY 2 ORDER BY 2 + SELECT COUNT(acnote.id), acnote.pub::TEXT FROM m_asset_copy_note acnote GROUP BY 2 ORDER BY 2 - asset_vols_by_lib + m_asset_vols_by_lib Volumes by Library assets 0 Volume Count.Library.Migrated - SELECT COUNT(acn.id), aou.name, acn.x_migrate::TEXT FROM asset_call_number_legacy acn JOIN actor_org_unit_legacy aou ON aou.id = acn.owning_lib GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(acn.id), aou.name, acn.x_migrate::TEXT FROM m_asset_call_number_legacy acn JOIN m_actor_org_unit_legacy aou ON aou.id = acn.owning_lib GROUP BY 2, 3 ORDER BY 2, 3 - asset_vols_by_lib + m_asset_vols_by_lib Volumes by Library assets 1 Volume Count.Library - SELECT COUNT(acn.id), aou.name FROM asset_call_number acn JOIN actor.org_unit aou ON aou.id = acn.owning_lib GROUP BY 2 ORDER BY 2 + SELECT COUNT(acn.id), aou.name FROM m_asset_call_number acn JOIN actor.org_unit aou ON aou.id = acn.owning_lib GROUP BY 2 ORDER BY 2 - asset_cops_by_loc_and_org + m_asset_cops_by_loc_and_org Copies by Location assets 0 Copy Count.Library.Circ Library.Migrated - SELECT COUNT(ac.id), acl.name, aou.name, ac.x_migrate::TEXT FROM asset_copy_legacy ac JOIN asset.copy_location acl ON acl.id = ac.location JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 + SELECT COUNT(ac.id), acl.name, aou.name, ac.x_migrate::TEXT FROM m_asset_copy_legacy ac JOIN asset.copy_location acl ON acl.id = ac.location JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 - asset_cops_w_loc_one + m_asset_cops_w_loc_one Copies with a Location of Stacks assets 0 Barcode - SELECT barcode FROM asset_copy_legacy WHERE location = 1 AND x_migrate + SELECT barcode FROM m_asset_copy_legacy WHERE location = 1 AND x_migrate - asset_no_barcode + m_asset_no_barcode Items Without Barcodes assets 0 Assigned Barcode - SELECT barcode FROM asset_copy_legacy WHERE barcode ~* 'no_barocde' AND x_migrate + SELECT barcode FROM m_asset_copy_legacy WHERE barcode ~* 'no_barocde' AND x_migrate - asset_barcode_patterns + m_asset_barcode_patterns Common Barcode Starting Patterns assets 0 Count of Barcodes (greater than 10).Left 60% of Characters - SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 + SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 - asset_barcode_collisions + m_asset_barcode_collisions Copy Barcode Collisions assets 0 Collision Count - SELECT COUNT(id) FROM asset_copy_legacy WHERE x_migrate = TRUE AND barcode LIKE 'x_%' + SELECT COUNT(id) FROM m_asset_copy_legacy WHERE x_migrate = TRUE AND barcode LIKE 'x_%' Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database. - asset_barcode_collisions + m_asset_barcode_collisions Copy Barcode Collisions assets 1 Collision Count - SELECT COUNT(id) FROM asset_copy WHERE barcode LIKE 'x_%' + SELECT COUNT(id) FROM m_asset_copy WHERE barcode LIKE 'x_%' Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database. - asset_barcode_collisions_shortlist + m_asset_barcode_collisions_shortlist Copy Barcode Collisions (first 20) assets 0 Collision List - SELECT ac.barcode FROM asset_copy_legacy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20 + SELECT ac.barcode FROM m_asset_copy_legacy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20 This is a shortlist of copy barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. - asset_barcode_collisions_shortlist + m_asset_barcode_collisions_shortlist Copy Barcode Collisions (first 20) assets 1 Collision List - SELECT ac.barcode FROM asset_copy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20 + SELECT ac.barcode FROM m_asset_copy ac WHERE ac.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20 This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. - asset_barcode_collision_patterns + m_asset_barcode_collision_patterns Common Copy Barcode Collision Patterns assets 0 Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters - SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM asset_copy_legacy ac WHERE barcode LIKE 'x_%' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 + SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy_legacy ac WHERE barcode LIKE 'x_%' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 - asset_barcode_collision_patterns + m_asset_barcode_collision_patterns Common Copy Barcode Collision Patterns assets 1 Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters - SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM asset_copy ac WHERE barcode LIKE 'x_%' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 + SELECT COUNT(ac.id), LEFT(ac.barcode,(ROUND(LENGTH(ac.barcode)*.6))::INT) FROM m_asset_copy ac WHERE barcode LIKE 'x_%' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 - asset_stat_cats + m_asset_stat_cats Copy Statistical Categories assets 0 Stat Cat Count.Library.Statistical Category - SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM asset_stat_cat_legacy ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 + SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM m_asset_stat_cat_legacy ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 - asset_stat_cats + m_asset_stat_cats Copy Statistical Categories assets 1 Stat Cat Count.Library.Statistical Category - SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM asset_stat_cat ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 + SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM m_asset_stat_cat ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 - asset_stat_cat_entries + m_asset_stat_cat_entries Copy Stat Cat User Entries assets 0 Copy Stat Count.Library.Statistical Category - SELECT COUNT(map.id), aou.name, ac_sc.name FROM asset_stat_cat_entry_copy_map_legacy map JOIN asset_stat_cat_legacy ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 + SELECT COUNT(map.id), aou.name, ac_sc.name FROM m_asset_stat_cat_entry_copy_map_legacy map JOIN m_asset_stat_cat_legacy ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 - asset_stat_cat_entries + m_asset_stat_cat_entries Copy Stat Cat User Entries assets 1 Copy Stat Count.Library.Statistical Category - SELECT COUNT(map.id), aou.name, ac_sc.name FROM asset_stat_cat_entry_copy_map map JOIN asset_stat_cat ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou. + SELECT COUNT(map.id), aou.name, ac_sc.name FROM m_asset_stat_cat_entry_copy_map map JOIN m_asset_stat_cat ac_sc ON ac_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou. id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 - asset_copy_tags + m_asset_copy_tags Copy Tags assets 0 Tag Count.Copy Tag Type.Copy Tag Label.Staff Note.Public - SELECT COUNT(map.id), tag.tag_type, tag.label, tag.staff_note, tag.pub FROM asset_copy_tag tag JOIN asset_copy_tag_copy_map map ON map.tag = tag.id GROUP BY 2,3,4,5 ORDER BY 2,3 + SELECT COUNT(map.id), tag.tag_type, tag.label, tag.staff_note, tag.pub FROM m_asset_copy_tag tag JOIN m_asset_copy_tag_copy_map map ON map.tag = tag.id GROUP BY 2,3,4,5 ORDER BY 2,3 - asset_copy_alerts + m_asset_copy_alerts Copy Alerts assets 0 Alert Count.Alert Type - SELECT COUNT(*), cat.name FROM asset_copy_alert aca JOIN config.copy_alert_type cat ON cat.id = aca.alert_type GROUP BY 2 + SELECT COUNT(*), cat.name FROM m_asset_copy_alert aca JOIN config.copy_alert_type cat ON cat.id = aca.alert_type GROUP BY 2 - money_billing_voided + m_money_billing_voided Bills Voided And Not money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_billing_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_billing_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - money_billing_voided + m_money_billing_voided Bills Voided And Not money 1 Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_billing a GROUP BY 2 ORDER BY 2, 3 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_billing a GROUP BY 2 ORDER BY 2, 3 - money_billing_by_type + m_money_billing_by_type Bills by Type money 0 Count.Billing Type.Migrated - SELECT COUNT(a.id), a.billing_type, a.x_migrate::TEXT FROM money_billing_legacy a GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(a.id), a.billing_type, a.x_migrate::TEXT FROM m_money_billing_legacy a GROUP BY 2, 3 ORDER BY 2, 3 - money_billing_by_type + m_money_billing_by_type Bills by Type money 1 Count.Billing Type - SELECT COUNT(a.id), a.billing_type FROM money_billing a GROUP BY 2 ORDER BY 2 + SELECT COUNT(a.id), a.billing_type FROM m_money_billing a GROUP BY 2 ORDER BY 2 - money_cash_payment + m_money_cash_payment Cash Payments money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_cash_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_cash_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - money_cash_payment + m_money_cash_payment Cash Payments money 1 Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_cash_payment a GROUP BY 2 ORDER BY 2 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_cash_payment a GROUP BY 2 ORDER BY 2 - money_check_payment + m_money_check_payment Check Payments money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_check_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_check_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - money_forgive_payment + m_money_forgive_payment Forgive Payments money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_forgive_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_forgive_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - money_forgive_payment + m_money_forgive_payment Forgive Payments money 1 Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_forgive_paymen a GROUP BY 2 ORDER BY 2 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM m_money_forgive_paymen a GROUP BY 2 ORDER BY 2 - money_goods_payment + m_money_goods_payment Goods Payments money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_goods_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_goods_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - money_work_payment + m_money_work_payment Work Payments money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_work_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_work_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - money_credit_card_payment + m_money_credit_card_payment Credit Card Payments money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_credit_card_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_credit_card_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 - money_credit_payment + m_money_credit_payment Credit Payments money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM money_credit_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM m_money_credit_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 @@ -691,7 +691,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 0 Count - SELECT COUNT(bre.id) FROM biblio_record_entry_legacy bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1 + SELECT COUNT(bre.id) FROM m_biblio_record_entry_legacy bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1 @@ -700,7 +700,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 1 Count - SELECT COUNT(bre.id) FROM biblio_record_entry bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1 + SELECT COUNT(bre.id) FROM m_biblio_record_entry bre WHERE id IN (SELECT id FROM biblio.record_entry) ORDER BY 1 @@ -709,7 +709,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 0 Count - SELECT COUNT(id) FROM biblio_record_entry where id NOT IN (SELECT DISTINCT record FROM asset_call_number) + SELECT COUNT(id) FROM m_biblio_record_entry where id NOT IN (SELECT DISTINCT record FROM m_asset_call_number) These records would not have been loaded. @@ -719,7 +719,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 0 Count - SELECT COUNT(id) FROM biblio_record_entry where id IN (SELECT record FROM asset.call_number WHERE label ~* '##URI##') + SELECT COUNT(id) FROM m_biblio_record_entry where id IN (SELECT record FROM asset.call_number WHERE label ~* '##URI##') @@ -728,7 +728,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 0 Count.Original Search Format.New Search Format - SELECT COUNT(*), ARRAY_TO_STRING(x_search_format,','), ARRAY_TO_STRING(x_after_search_format,',') FROM biblio_record_entry_legacy WHERE x_migrate AND x_after_search_format IS NOT NULL GROUP BY 2, 3 ORDER BY 3,2 + SELECT COUNT(*), ARRAY_TO_STRING(x_search_format,','), ARRAY_TO_STRING(x_after_search_format,',') FROM m_biblio_record_entry_legacy WHERE x_migrate AND x_after_search_format IS NOT NULL GROUP BY 2, 3 ORDER BY 3,2 @@ -737,7 +737,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 0 Count.Migrated - SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_record_note_legacy b GROUP BY 2 + SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_record_note_legacy b GROUP BY 2 @@ -746,7 +746,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 1 Count - SELECT COUNT(b.id) FROM biblio_record_note b + SELECT COUNT(b.id) FROM m_biblio_record_note b @@ -755,7 +755,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 0 Count.Migrated - SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_peer_bib_copy_map_legacy b GROUP BY 2 + SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_peer_bib_copy_map_legacy b GROUP BY 2 @@ -764,7 +764,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 1 Count - SELECT COUNT(b.id) FROM biblio_peer_bib_copy_map b + SELECT COUNT(b.id) FROM m_biblio_peer_bib_copy_map b @@ -773,7 +773,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 0 Count.Migrated - SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_monograph_part_legacy b GROUP BY 2 + SELECT COUNT(b.id), b.x_migrate::TEXT FROM m_biblio_monograph_part_legacy b GROUP BY 2 @@ -782,7 +782,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 1 Count - SELECT COUNT(b.id) FROM biblio_monograph_part b + SELECT COUNT(b.id) FROM m_biblio_monograph_part b @@ -812,7 +812,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 SELECT LEFT(ARRAY_TO_STRING(oils_xpath( '//*[@tag="245"]/*[@code="a"]/text()', a.marc),''),50) AS "Title", LEFT(ARRAY_TO_STRING(oils_xpath( '//*[@tag="100"]/*[@code="a"]/text()', a.marc),''),50) AS "Author", ARRAY_TO_STRING(oils_xpath( '//*[@tag="001"]/text()', a.marc),'') AS "001" - FROM biblio_record_entry a LEFT JOIN biblio.record_entry b ON b.id = a.id WHERE b.id IS NULL AND a.x_migrate + FROM m_biblio_record_entry a LEFT JOIN biblio.record_entry b ON b.id = a.id WHERE b.id IS NULL AND a.x_migrate @@ -825,7 +825,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Count.Library.Deleted.Migrated - SELECT COUNT(au.id), aou.name, au.deleted::TEXT, au.x_migrate::TEXT FROM actor_usr_legacy au JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 + SELECT COUNT(au.id), aou.name, au.deleted::TEXT, au.x_migrate::TEXT FROM m_actor_usr_legacy au JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 @@ -834,7 +834,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Count.Permission Group.Migrated - SELECT COUNT(au.id), pgt.name, au.x_migrate::TEXT FROM actor_usr_legacy au JOIN permission.grp_tree pgt ON pgt.id = au.profile GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(au.id), pgt.name, au.x_migrate::TEXT FROM m_actor_usr_legacy au JOIN permission.grp_tree pgt ON pgt.id = au.profile GROUP BY 2, 3 ORDER BY 2, 3 @@ -843,7 +843,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Count of Users.Active.Migrated - SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_usr_legacy GROUP BY 2, 3 + SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM m_actor_usr_legacy GROUP BY 2, 3 @@ -852,7 +852,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Count of Users.Active - SELECT COUNT(id), active::TEXT FROM actor_usr GROUP BY 2 + SELECT COUNT(id), active::TEXT FROM m_actor_usr GROUP BY 2 @@ -861,7 +861,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Count of Barcodes.Active.Migrated - SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_card_legacy GROUP BY 2, 3 + SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM m_actor_card_legacy GROUP BY 2, 3 @@ -870,7 +870,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Count of Barcodes.Active - SELECT COUNT(id), active::TEXT FROM actor_card GROUP BY 2 + SELECT COUNT(id), active::TEXT FROM m_actor_card GROUP BY 2 @@ -897,7 +897,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Count of Barcodes (greater than 10).Left 60% of Characters - SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card_legacy acard WHERE acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 + SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM m_actor_card_legacy acard WHERE acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 @@ -906,7 +906,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Count of Barcodes (greater than 10).Left 60% of Characters - SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card acard GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 + SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM m_actor_card acard GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 @@ -915,7 +915,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Collision Count - SELECT COUNT(acard.id) FROM actor_card_legacy acard WHERE barcode LIKE 'x_%' AND x_migrate = TRUE + SELECT COUNT(acard.id) FROM m_actor_card_legacy acard WHERE barcode LIKE 'x_%' AND x_migrate = TRUE @@ -924,7 +924,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Collision Count - SELECT COUNT(acard.id) FROM actor_card acard WHERE barcode LIKE 'x_%' + SELECT COUNT(acard.id) FROM m_actor_card acard WHERE barcode LIKE 'x_%' @@ -933,7 +933,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Collision List - SELECT acard.barcode FROM actor_card_legacy acard WHERE acard.barcode LIKE 'x_' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20 + SELECT acard.barcode FROM m_actor_card_legacy acard WHERE acard.barcode LIKE 'x_' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20 This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. In some cases we may flag individual accounts to not migrate. @@ -943,7 +943,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Collision List - SELECT acard.barcode FROM actor_card acard WHERE acard.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20 + SELECT acard.barcode FROM m_actor_card acard WHERE acard.barcode LIKE 'x_%' ORDER BY 1 LIMIT 20 This is a shortlist of patron barcode collisions that maxes out at 20. If there are more collisions we will need to run a custom report. In some cases we may flag individual accounts to not migrate. @@ -953,7 +953,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters - SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card_legacy acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') AND acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 + SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM m_actor_card_legacy acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') AND acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 @@ -962,7 +962,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Number of Barcodes Matching Pattern Greater than 10.Left 60% of Characters - SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM actor_card acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 + SELECT COUNT(acard.id), LEFT(acard.barcode,(ROUND(LENGTH(acard.barcode)*.6))::INT) FROM m_actor_card acard WHERE (acard.barcode ~* 'collision' OR acard.barcode ~* '^x_') GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 @@ -971,7 +971,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Count.Valid.Migrated - SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3 + SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM m_actor_usr_address_legacy aua GROUP BY 2, 3 @@ -980,7 +980,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Count.Valid - SELECT COUNT(aua.id), valid::TEXT FROM actor_usr_address aua GROUP BY 2 + SELECT COUNT(aua.id), valid::TEXT FROM m_actor_usr_address aua GROUP BY 2 @@ -989,7 +989,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Count of Addresses.Pending.Migrated - SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3 + SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM m_actor_usr_address_legacy aua GROUP BY 2, 3 @@ -998,7 +998,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Count of Addresses.Pending - SELECT COUNT(aua.id), pending::TEXT FROM actor_usr_address aua GROUP BY 2 + SELECT COUNT(aua.id), pending::TEXT FROM m_actor_usr_address aua GROUP BY 2 @@ -1007,7 +1007,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Count.Deleted.Migrated - SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM actor_usr_message_legacy aum GROUP BY 2, 3 + SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM m_actor_usr_message_legacy aum GROUP BY 2, 3 @@ -1016,7 +1016,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Count.Deleted - SELECT COUNT(aum.id), deleted::TEXT FROM actor_usr_message_legacy aum GROUP BY 2 + SELECT COUNT(aum.id), deleted::TEXT FROM m_actor_usr_message_legacy aum GROUP BY 2 @@ -1025,7 +1025,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Count.Public.Migrated - SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM actor_usr_note_legacy aun GROUP BY 2, 3 + SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM m_actor_usr_note_legacy aun GROUP BY 2, 3 @@ -1034,7 +1034,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Count.Public - SELECT COUNT(aun.id), pub::TEXT FROM actor_usr_note aun GROUP BY 2 + SELECT COUNT(aun.id), pub::TEXT FROM m_actor_usr_note aun GROUP BY 2 @@ -1043,7 +1043,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Stat Cat Count.Library.Statistical Category.Migrated - SELECT COUNT(au_sc.id), aou.name, au_sc.name, au_sc.x_migrate::TEXT FROM actor_stat_cat_legacy au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 + SELECT COUNT(au_sc.id), aou.name, au_sc.name, au_sc.x_migrate::TEXT FROM m_actor_stat_cat_legacy au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 @@ -1052,7 +1052,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Stat Cat Count.Library.Statistical Category - SELECT COUNT(au_sc.id), aou.name, au_sc.name FROM actor_stat_cat au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(au_sc.id), aou.name, au_sc.name FROM m_actor_stat_cat au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2, 3 @@ -1061,7 +1061,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Patron Stat Count.Library.Statistical Category.Migrated - SELECT COUNT(map.id), aou.name, au_sc.name, map.x_migrate::TEXT FROM actor_stat_cat_entry_usr_map_legacy map JOIN actor_stat_cat_legacy au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2,3, 4 + SELECT COUNT(map.id), aou.name, au_sc.name, map.x_migrate::TEXT FROM m_actor_stat_cat_entry_usr_map_legacy map JOIN m_actor_stat_cat_legacy au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3, 4 ORDER BY 2,3, 4 @@ -1070,7 +1070,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Patron Stat Count.Library.Statistical Category - SELECT COUNT(map.id), aou.name, au_sc.name FROM actor_stat_cat_entry_usr_map map JOIN actor_stat_cat au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2,3 + SELECT COUNT(map.id), aou.name, au_sc.name FROM m_actor_stat_cat_entry_usr_map map JOIN m_actor_stat_cat au_sc ON au_sc.id = map.stat_cat JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2,3 @@ -1090,9 +1090,9 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Count.Setting.Value - SELECT COUNT(*), name, 'User''s Phone' FROM actor_usr_setting WHERE name IN ('opac.default_phone') GROUP BY 2, 3 - UNION ALL SELECT COUNT(*), name, value FROM actor_usr_setting WHERE name IN ('opac.hold_notify') GROUP BY 2, 3 - UNION ALL SELECT COUNT(*), a.name, aou.shortname FROM actor_usr_setting a JOIN actor.org_unit aou ON aou.id = REPLACE(a.value,'"','')::INTEGER + SELECT COUNT(*), name, 'User''s Phone' FROM m_actor_usr_setting WHERE name IN ('opac.default_phone') GROUP BY 2, 3 + UNION ALL SELECT COUNT(*), name, value FROM m_actor_usr_setting WHERE name IN ('opac.hold_notify') GROUP BY 2, 3 + UNION ALL SELECT COUNT(*), a.name, aou.shortname FROM m_actor_usr_setting a JOIN actor.org_unit aou ON aou.id = REPLACE(a.value,'"','')::INTEGER WHERE a.name IN ('opac.default_pickup_location','opac.default_search_location') GROUP BY 2, 3 ORDER BY 2, 3; @@ -1103,7 +1103,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 acq Migrated Funds Number of Funds.Migrated - SELECT COUNT(id), x_migrate::TEXT FROM acq_fund_legacy GROUP BY 2; + SELECT COUNT(id), x_migrate::TEXT FROM m_acq_fund_legacy GROUP BY 2; @@ -1112,7 +1112,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 acq Migrated Funds Number of Funds - SELECT COUNT(id) FROM acq_fund; + SELECT COUNT(id) FROM m_acq_fund; @@ -1121,7 +1121,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 acq Migrated Invoices Number of Invoices.Migrated - SELECT COUNT(id), x_migrate::TEXT FROM acq_invoice_legacy GROUP BY 2; + SELECT COUNT(id), x_migrate::TEXT FROM m_acq_invoice_legacy GROUP BY 2; @@ -1130,7 +1130,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 acq Migrated Invoices Number of Funds - SELECT COUNT(id) FROM acq_invoice; + SELECT COUNT(id) FROM m_acq_invoice; @@ -1140,7 +1140,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 0 Migrated Serial MFHDs Number of MFHDs - SELECT COUNT(id) FROM serial_record_entry + SELECT COUNT(id) FROM m_serial_record_entry @@ -1208,10 +1208,10 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 Action Triggers Setup for Notices ID.Active.Owner.Name SELECT ed.id, ed.active, aou.shortname, ed.name - FROM action_trigger.event_definition ed + FROM m_action_trigger.event_definition ed JOIN actor.org_unit aou ON aou.id = ed.owner - WHERE ed.owner IN (SELECT DISTINCT home_ou FROM actor_usr) - OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM actor_usr)); + WHERE ed.owner IN (SELECT DISTINCT home_ou FROM m_actor_usr) + OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM m_actor_usr)); @@ -1222,11 +1222,11 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 Count of Notices Run with State Count of Notices.State.ID.Owner.Name SELECT COUNT(ate.id), ate.state, ed.id, aou.shortname, ed.name - FROM action_trigger.event_definition ed + FROM m_action_trigger.event_definition ed JOIN actor.org_unit aou ON aou.id = ed.owner - JOIN action_trigger.event ate ON ate.event_def = ed.id - WHERE ed.owner IN (SELECT DISTINCT home_ou FROM actor_usr) - OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM actor_usr)) + JOIN m_action_trigger.event ate ON ate.event_def = ed.id + WHERE ed.owner IN (SELECT DISTINCT home_ou FROM m_actor_usr) + OR ed.owner IN (SELECT DISTINCT parent_ou FROM actor.org_unit WHERE id in (SELECT DISTINCT home_ou FROM m_actor_usr)) GROUP BY 2,3,4; diff --git a/mig-xml/mapping_reports.xml b/mig-xml/mapping_reports.xml index 55c12be..47f07e6 100644 --- a/mig-xml/mapping_reports.xml +++ b/mig-xml/mapping_reports.xml @@ -6,7 +6,7 @@ money arbitrary tags, each should go in separate tag Migrated Billings title used in the asciidoc output Number of Billings.Migrated.Total period delimited - SELECT COUNT(id),x_migrate,SUM(amount) FROM money_billing_legacy GROUP BY 2; query itself, will be replaced + SELECT COUNT(id),x_migrate,SUM(amount) FROM m_money_billing_legacy GROUP BY 2; query itself, will be replaced Arbitrary note that can be included in the entries. --> @@ -14,31 +14,31 @@ - evg_asset_copy_statuses + evg_m_asset_copy_statuses Statuses evergreen 0 Copy Count.Migrating Status.New Evergreen Status.Notes - SELECT COUNT(ac.l_id), cs.l_name FROM asset_copy_legacy ac JOIN config_copy_status_legacy cs ON cs.l_id = ac.l_status GROUP BY 2 ORDER BY 2 + SELECT COUNT(ac.l_id), cs.l_name FROM m_asset_copy_legacy ac JOIN config_copy_status_legacy cs ON cs.l_id = ac.l_status GROUP BY 2 ORDER BY 2 You only need to fill this sheet out if you use custom statuses that need to be migrated. - evg_asset_circ_mods + evg_m_asset_circ_mods Circulation Modifiers evergreen 0 Copy Count.Migrating Circ Mod.New Circ Mod.Notes - SELECT COUNT(ac.l_id), ac.l_circ_modifier FROM asset_copy_legacy ac GROUP BY 2 ORDER BY 2 + SELECT COUNT(ac.l_id), ac.l_circ_modifier FROM m_asset_copy_legacy ac GROUP BY 2 ORDER BY 2 - evg_asset_copy_locs + evg_m_asset_copy_locs Copy Locations evergreen 0 Count.Library.Migrating Copy Location.New Copy Location.Notes - SELECT COUNT(ac.l_id), aou.l_name, acl.l_name FROM asset_copy_location_legacy acl JOIN actor_org_unit_legacy aou ON aou.l_id = acl.l_owning_lib JOIN asset_copy_legacy ac ON ac.l_location = acl.l_id GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(ac.l_id), aou.l_name, acl.l_name FROM m_asset_copy_location_legacy acl JOIN m_actor_org_unit_legacy aou ON aou.l_id = acl.l_owning_lib JOIN m_asset_copy_legacy ac ON ac.l_location = acl.l_id GROUP BY 2, 3 ORDER BY 2, 3 Any locations not mapped can be moved over as their existing locations. @@ -48,7 +48,7 @@ evergreen 0 Count.Migrating Permission Group.New Permission Group.Notes - SELECT COUNT(au.l_id), pgt.l_name FROM actor_usr_legacy au JOIN permission_grp_tree_legacy pgt ON pgt.l_id = au.l_profile GROUP BY 2 ORDER BY 2 + SELECT COUNT(au.l_id), pgt.l_name FROM m_actor_usr_legacy au JOIN permission_grp_tree_legacy pgt ON pgt.l_id = au.l_profile GROUP BY 2 ORDER BY 2 @@ -62,7 +62,7 @@ - tlc_load_asset_notes + tlc_load_m_asset_notes Item Notes tlc 0 @@ -77,7 +77,7 @@ - tlc_load_asset_holdings_codes + tlc_load_m_asset_holdings_codes Holdings Codes tlc 0 @@ -86,7 +86,7 @@ - tlc_load_money_migrating_bills + tlc_load_m_money_migrating_bills Migrating Bills By Bill Type tlc 0 @@ -308,7 +308,7 @@ - destiny_load_asset_categories + destiny_load_m_asset_categories Count of Categories destiny 0 @@ -317,7 +317,7 @@ - destiny_load_asset_notes + destiny_load_m_asset_notes Copies by Note Types destiny 0 @@ -327,7 +327,7 @@ - destiny_load_asset_sublocation + destiny_load_m_asset_sublocation Copies by Sub Location destiny 0 @@ -336,7 +336,7 @@ - destiny_load_asset_vendor + destiny_load_m_asset_vendor Copies by Vendor destiny 0 @@ -346,7 +346,7 @@ - destiny_load_asset_descriptions + destiny_load_m_asset_descriptions Copies with Description Fields destiny 0 @@ -373,7 +373,7 @@ apollo 0 Count.Status.Type - SELECT COUNT(id), l_status, l_type FROM action_circulation_legacy GROUP BY 2, 3 + SELECT COUNT(id), l_status, l_type FROM m_action_circulation_legacy GROUP BY 2, 3 Circulations will only not be migrated if they can't be attached to a migrated patron and holding. @@ -383,37 +383,37 @@ apollo 0 Count.Status - SELECT COUNT(id), l_status FROM action_hold_request_legacy GROUP BY 2 + SELECT COUNT(id), l_status FROM m_action_hold_request_legacy GROUP BY 2 Only unfilled holds are being migrated. - asset_pending_bibs + m_asset_pending_bibs Pending Records apollo 0 Count.Year of Last Edit.Count of Copies Attached - SELECT COUNT(bre.id), CASE WHEN LENGTH(bre.l_edited) > 1 THEN EXTRACT(YEAR FROM bre.l_edited::TIMESTAMP) ELSE '1900' END, COUNT(ac.id) FROM biblio_record_entry_legacy bre LEFT JOIN asset_copy_legacy ac ON ac.l_biblio = bre.l_id WHERE bre.l_status = 'pending' GROUP BY 2 ORDER BY 2 + SELECT COUNT(bre.id), CASE WHEN LENGTH(bre.l_edited) > 1 THEN EXTRACT(YEAR FROM bre.l_edited::TIMESTAMP) ELSE '1900' END, COUNT(ac.id) FROM m_biblio_record_entry_legacy bre LEFT JOIN m_asset_copy_legacy ac ON ac.l_biblio = bre.l_id WHERE bre.l_status = 'pending' GROUP BY 2 ORDER BY 2 - asset_copies_by_status + m_asset_copies_by_status Copies by Status apollo 0 Count.Status - SELECT COUNT(id), l_status FROM asset_copy_legacy GROUP BY 2 ORDER BY 2 + SELECT COUNT(id), l_status FROM m_asset_copy_legacy GROUP BY 2 ORDER BY 2 Deleted copies with a delete date were not exported additionally those with the deleted status won't get loaded. - asset_pending_copies + m_asset_pending_copies Pending Copies by Last Edit apollo 0 Count.Last Edited - SELECT COUNT(id), CASE WHEN LENGTH(l_edited) > 1 THEN EXTRACT(YEAR FROM l_edited::TIMESTAMP) ELSE '1900' END FROM asset_copy_legacy WHERE l_status = 'pending' GROUP BY 2 ORDER BY 2 + SELECT COUNT(id), CASE WHEN LENGTH(l_edited) > 1 THEN EXTRACT(YEAR FROM l_edited::TIMESTAMP) ELSE '1900' END FROM m_asset_copy_legacy WHERE l_status = 'pending' GROUP BY 2 ORDER BY 2 @@ -422,56 +422,56 @@ apollo 0 Count.Membership Number.Membership Name - SELECT COUNT(ac.id), acl.l_membership_number, acl.l_membership_name FROM asset_copy_legacy ac JOIN asset_copy_location_legacy acl ON acl.l_membership_name = ac.l_memberships GROUP BY 2,3 ORDER BY 2 + SELECT COUNT(ac.id), acl.l_membership_number, acl.l_membership_name FROM m_asset_copy_legacy ac JOIN m_asset_copy_location_legacy acl ON acl.l_membership_name = ac.l_memberships GROUP BY 2,3 ORDER BY 2 - money_bills + m_money_bills Bills apollo 0 Count.Status - SELECT COUNT(id), l_status FROM money_billing_legacy GROUP BY 2 ORDER BY 2 + SELECT COUNT(id), l_status FROM m_money_billing_legacy GROUP BY 2 ORDER BY 2 Unless there is a good reason to do so forgiven and paid bills will not be migrated. - actor_groups + m_actor_groups Patron Membership Groups apollo 0 Membership List Name.Membership List ID.Membership Number.Membership Name.Membership Length in Monthst - SELECT l_membership_list_name, l_membership_list_id, l_membership_number, l_membership_name, l_membership_length_months FROM actor_usr_legacy_groups_tsv ORDER BY 1, 3 + SELECT l_membership_list_name, l_membership_list_id, l_membership_number, l_membership_name, l_membership_length_months FROM m_actor_usr_legacy_groups_tsv ORDER BY 1, 3 Age looks like a good target for a stat cat and / or juvenile setting while the patron type can map to profiles. - actor_by_groups + m_actor_by_groups Patrons by Membership Groups apollo 0 Count.Membership List Name.Membership Number - SELECT COUNT(*), l_membership_list_name, l_membership_number FROM (SELECT id, UNNEST(STRING_TO_ARRAY(l_memberships,'|')) AS m FROM actor_usr_legacy ) x JOIN actor_usr_legacy_groups_tsv t ON t.l_membership_name = x.m GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(*), l_membership_list_name, l_membership_number FROM (SELECT id, UNNEST(STRING_TO_ARRAY(l_memberships,'|')) AS m FROM m_actor_usr_legacy ) x JOIN m_actor_usr_legacy_groups_tsv t ON t.l_membership_name = x.m GROUP BY 2, 3 ORDER BY 2, 3 - actor_addresses_nulls + m_actor_addresses_nulls Patron Addresses apollo 0 Address Field.Nulls - SELECT 'Street Address', COUNT(id) FROM actor_usr_address_legacy WHERE l_lines IS NULL UNION ALL SELECT 'City', COUNT(id) FROM actor_usr_address_legacy UNION ALL SELECT 'State', COUNT(id) FROM actor_usr_address_legacy WHERE l_country_division IS NULL UNION ALL SELECT 'Postal Code', COUNT(id) FROM actor_usr_address_legacy WHERE l_postal_code IS NULL + SELECT 'Street Address', COUNT(id) FROM m_actor_usr_address_legacy WHERE l_lines IS NULL UNION ALL SELECT 'City', COUNT(id) FROM m_actor_usr_address_legacy UNION ALL SELECT 'State', COUNT(id) FROM m_actor_usr_address_legacy WHERE l_country_division IS NULL UNION ALL SELECT 'Postal Code', COUNT(id) FROM m_actor_usr_address_legacy WHERE l_postal_code IS NULL If any of these fields are null then we need defaults to fill in, note the extract had no city data. - actor_phones + m_actor_phones Patron Phones apollo 0 Count.Type - SELECT COUNT(*), l_type FROM actor_usr_phones_tsv GROUP BY 2 ORDER BY 2 + SELECT COUNT(*), l_type FROM m_actor_usr_phones_tsv GROUP BY 2 ORDER BY 2 These need to be mapped to Evergreen phone types. @@ -484,7 +484,7 @@ horizon 0 Count.Borrower Type - SELECT COUNT(id), l_borrower_btype FROM actor_usr_legacy GROUP BY 2 ORDER BY 2; + SELECT COUNT(id), l_borrower_btype FROM m_actor_usr_legacy GROUP BY 2 ORDER BY 2; @@ -502,7 +502,7 @@ horizon 0 Count of Migratable Passwords / PINs - SELECT COUNT(l_borrower_pin) FROM actor_usr_legacy WHERE LENGTH(l_borrower_pin) > 1; + SELECT COUNT(l_borrower_pin) FROM m_actor_usr_legacy WHERE LENGTH(l_borrower_pin) > 1; @@ -529,7 +529,7 @@ horizon 0 Sample of Migratable Notes - SELECT l_borrower_borrower_note FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1 LIMIT 20; + SELECT l_borrower_borrower_note FROM m_actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1 LIMIT 20; @@ -547,7 +547,7 @@ horizon 0 Count - SELECT COUNT(l_borrower_borrower_note) FROM actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1; + SELECT COUNT(l_borrower_borrower_note) FROM m_actor_usr_legacy WHERE LENGTH(l_borrower_borrower_note) > 1; @@ -565,7 +565,7 @@ horizon 0 Count - SELECT l_borrower_note2 FROM actor_usr_legacy WHERE LENGTH(l_borrower_note2) > 1 LIMIT 20; + SELECT l_borrower_note2 FROM m_actor_usr_legacy WHERE LENGTH(l_borrower_note2) > 1 LIMIT 20; @@ -574,7 +574,7 @@ horizon 0 Count - SELECT l_borrower_note3 FROM actor_usr_legacy WHERE LENGTH(l_borrower_note3) > 1 LIMIT 20; + SELECT l_borrower_note3 FROM m_actor_usr_legacy WHERE LENGTH(l_borrower_note3) > 1 LIMIT 20; @@ -585,10 +585,10 @@ Count.Borrower Phone Type SELECT COUNT(*), b - FROM (SELECT l_borrower_phone_1_phone_type AS b FROM actor_usr_legacy - UNION ALL SELECT l_borrower_phone_2_phone_type AS b FROM actor_usr_legacy - UNION ALL SELECT l_borrower_phone_3_phone_type AS b FROM actor_usr_legacy - UNION ALL SELECT l_borrower_phone_4_phone_type AS b FROM actor_usr_legacy) x + FROM (SELECT l_borrower_phone_1_phone_type AS b FROM m_actor_usr_legacy + UNION ALL SELECT l_borrower_phone_2_phone_type AS b FROM m_actor_usr_legacy + UNION ALL SELECT l_borrower_phone_3_phone_type AS b FROM m_actor_usr_legacy + UNION ALL SELECT l_borrower_phone_4_phone_type AS b FROM m_actor_usr_legacy) x GROUP BY 2 ORDER BY 2 @@ -615,11 +615,11 @@ 0 Count.BStat SELECT COUNT(*), b - FROM (SELECT l_borrower_bstat_1_bstat AS b FROM actor_usr_legacy + FROM (SELECT l_borrower_bstat_1_bstat AS b FROM m_actor_usr_legacy UNION ALL - SELECT l_borrower_bstat_2_bstat AS b FROM actor_usr_legacy + SELECT l_borrower_bstat_2_bstat AS b FROM m_actor_usr_legacy UNION ALL - SELECT l_borrower_bstat_3_bstat AS b FROM actor_usr_legacy) x + SELECT l_borrower_bstat_3_bstat AS b FROM m_actor_usr_legacy) x GROUP BY 2 ORDER BY 1; @@ -640,7 +640,7 @@ horizon 0 Count.Collection - SELECT COUNT(id), l_collection FROM asset_copy_legacy GROUP BY 2 ORDER BY 2; + SELECT COUNT(id), l_collection FROM m_asset_copy_legacy GROUP BY 2 ORDER BY 2; @@ -658,7 +658,7 @@ horizon 0 Count.Item Type (itype) - SELECT COUNT(id), l_itype FROM asset_copy_legacy GROUP BY 2 ORDER BY 2; + SELECT COUNT(id), l_itype FROM m_asset_copy_legacy GROUP BY 2 ORDER BY 2; @@ -676,7 +676,7 @@ horizon 0 Count - SELECT COUNT(l_internal_note) FROM asset_copy_legacy WHERE LENGTH(l_internal_note) > 1; + SELECT COUNT(l_internal_note) FROM m_asset_copy_legacy WHERE LENGTH(l_internal_note) > 1; @@ -694,7 +694,7 @@ horizon 0 Count - SELECT l_internal_note FROM asset_copy_legacy WHERE LENGTH(l_internal_note) > 1 LIMIT 20; + SELECT l_internal_note FROM m_asset_copy_legacy WHERE LENGTH(l_internal_note) > 1 LIMIT 20; @@ -714,96 +714,96 @@ 0 Circs by Status Count of Circs.Status - SELECT COUNT(id), l_is_checked_out FROM asset_copy_legacy GROUP BY 2 + SELECT COUNT(id), l_is_checked_out FROM m_asset_copy_legacy GROUP BY 2 - rm_load_asset_by_resource_type + rm_load_m_asset_by_resource_type Resource Type rm 0 Count.Resource Type - SELECT COUNT(*), l_resource_type FROM asset_copy_legacy GROUP BY 2 ORDER BY 2 + SELECT COUNT(*), l_resource_type FROM m_asset_copy_legacy GROUP BY 2 ORDER BY 2 - rm_load_asset_by_location + rm_load_m_asset_by_location Copies by Location rm 0 Count.Location - SELECT COUNT(*), l_location FROM asset_copy_legacy GROUP BY 2 ORDER BY 2 + SELECT COUNT(*), l_location FROM m_asset_copy_legacy GROUP BY 2 ORDER BY 2 - rm_load_asset_by_category + rm_load_m_asset_by_category Copies by Category rm 0 Count.Category - SELECT COUNT(*), l_category FROM asset_copy_legacy GROUP BY 2 ORDER BY 2 + SELECT COUNT(*), l_category FROM m_asset_copy_legacy GROUP BY 2 ORDER BY 2 - rm_load_asset_by_status + rm_load_m_asset_by_status Copies by Status rm 0 Count.Status - SELECT COUNT(*), l_status FROM asset_copy_legacy GROUP BY 2 ORDER BY 2 + SELECT COUNT(*), l_status FROM m_asset_copy_legacy GROUP BY 2 ORDER BY 2 - rm_actor_groups + rm_m_actor_groups Patrons by User Groups rm 0 Count.Group - SELECT COUNT(id), l_user_group FROM actor_usr_legacy GROUP BY 2 ORDER BY 2; + SELECT COUNT(id), l_user_group FROM m_actor_usr_legacy GROUP BY 2 ORDER BY 2; - rm_actor_access + rm_m_actor_access Patrons by Access Field rm 0 Count.Access - SELECT COUNT(id), l_access_if_applicable FROM actor_usr_legacy GROUP BY 2 ORDER BY 2; + SELECT COUNT(id), l_access_if_applicable FROM m_actor_usr_legacy GROUP BY 2 ORDER BY 2; - rm_actor_comments + rm_m_actor_comments Patron Comments rm 0 Count.Sample - SELECT COUNT(id), 'All Comments' FROM actor_usr_legacy WHERE LENGTH(l_comments) > 1 - UNION ALL SELECT NULL, l_comments FROM actor_usr_legacy WHERE LENGTH(l_comments) > 1 LIMIT 10 + SELECT COUNT(id), 'All Comments' FROM m_actor_usr_legacy WHERE LENGTH(l_comments) > 1 + UNION ALL SELECT NULL, l_comments FROM m_actor_usr_legacy WHERE LENGTH(l_comments) > 1 LIMIT 10 - rm_actor_circulation_note + rm_m_actor_circulation_note Patron Circ Notes rm 0 Count.Sample - SELECT COUNT(id), 'All Notes' FROM actor_usr_legacy WHERE LENGTH(l_circulation_note) > 1 - UNION ALL SELECT NULL, l_circulation_note FROM actor_usr_legacy WHERE LENGTH(l_circulation_note) > 1 LIMIT 10 + SELECT COUNT(id), 'All Notes' FROM m_actor_usr_legacy WHERE LENGTH(l_circulation_note) > 1 + UNION ALL SELECT NULL, l_circulation_note FROM m_actor_usr_legacy WHERE LENGTH(l_circulation_note) > 1 LIMIT 10 diff --git a/sql/base/00-infrastructure.sql b/sql/base/00-infrastructure.sql index ea79446..4bd25cf 100644 --- a/sql/base/00-infrastructure.sql +++ b/sql/base/00-infrastructure.sql @@ -62,13 +62,13 @@ CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$ BEGIN SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables; PERFORM migration_tools.build_base_staging_tables(migration_schema,production_tables); - EXECUTE 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );'; - EXECUTE 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );'; - EXECUTE 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );'; - EXECUTE 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );'; - EXECUTE 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );'; - EXECUTE 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );'; - EXECUTE 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label,prefix,suffix );'; + EXECUTE 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.m_actor_card ( barcode );'; + EXECUTE 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.m_actor_usr ( usrname );'; + EXECUTE 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.m_asset_copy ( barcode );'; + EXECUTE 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.m_asset_copy ( id );'; + EXECUTE 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.m_asset_call_number ( record );'; + EXECUTE 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.m_asset_call_number ( UPPER(label),id,owning_lib );'; + EXECUTE 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.m_asset_call_number ( record,owning_lib,label,prefix,suffix );'; END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; @@ -90,7 +90,7 @@ CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TE base_staging_table TEXT; columns RECORD; BEGIN - base_staging_table = REPLACE( production_table, '.', '_' ); + base_staging_table = REPLACE( 'm_' || production_table, '.', '_' ); EXECUTE 'CREATE TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );'; FOR columns IN SELECT table_schema, table_name, column_name, data_type -- 1.7.2.5