From 91f3fc3c89d088788f7064bc06764ff83a40e607 Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Sat, 3 Mar 2018 05:09:49 -0500 Subject: [PATCH] adding search path to dbi connect and stripping out schema defs from xml --- mig-bin/Mig.pm | 1 + mig-bin/mig-reporter | 11 +- mig-xml/evergreen_staged_report.xml | 226 +++++++++++++++++----------------- 3 files changed, 118 insertions(+), 120 deletions(-) diff --git a/mig-bin/Mig.pm b/mig-bin/Mig.pm index 6600cfd..46aa1d1 100644 --- a/mig-bin/Mig.pm +++ b/mig-bin/Mig.pm @@ -24,6 +24,7 @@ sub db_connect { ,$PGUSER ,undef ) || die "Unable to connect to $PGHOST:$PGPORT:$PGDATABASE:$PGUSER : $!\n"; + $dbh->do("SET search_path TO $MIGSCHEMA, evergreen, pg_catalog, public"); return $dbh; } sub db_disconnect { diff --git a/mig-bin/mig-reporter b/mig-bin/mig-reporter index 21fa117..cb7e4e6 100755 --- a/mig-bin/mig-reporter +++ b/mig-bin/mig-reporter @@ -94,7 +94,7 @@ foreach my $arg (@ARGV) { } } -if (!defined $tags) {$tags = 'Circs.Holds.Actors.Bibs.Assets.Money'}; +if (!defined $tags) {$tags = 'circs.holds.actors.bibs.assets.money'}; if (!defined $analyst) { abort('--analyst must be supplied'); } if (!defined $report_title) { abort('--report_title must be supplied'); } @@ -114,7 +114,6 @@ if ($MIGSCHEMA eq 'full') { $MIGSCHEMA = ''; } my $dbh = Mig::db_connect(); my $report_file = create_report_name($report_title); $report_file = $MIGGITDIR . $report_file; -my $mig_func_schema = $MIGSCHEMA; open(my $fh, '>', $report_file) or die "Could not open output file!"; @@ -136,8 +135,6 @@ foreach my $func ($dom->findnodes('//function')) { my $fdrop = $func->findvalue('./drop'); my $fcreate = $func->findvalue('./create'); my $fname = $func->findvalue('./name'); - $fdrop =~ s/mig_func_schema/$mig_func_schema/g; - $fcreate =~ s/mig_func_schema/$mig_func_schema/g; my $sdrop = $dbh->prepare($fdrop); my $screate = $dbh->prepare($fcreate); print "dropping function $fname ... "; @@ -186,11 +183,12 @@ foreach my $t (@report_tags) { } } } -# end of main logic print "\n"; close $fh; +############ end of main logic + sub find_report { my $dom = shift; my $tag = shift; @@ -291,8 +289,7 @@ sub print_query { my $fh = shift; my %report = @_; my $query = $report{query}; - $query =~ s/MIGSCHEMA/$MIGSCHEMA/g; - $query =~ s/mig_func_schema/$mig_func_schema/g; + print $query; my $sth = $dbh->prepare($query); $sth->execute(); diff --git a/mig-xml/evergreen_staged_report.xml b/mig-xml/evergreen_staged_report.xml index 8d37e60..31fca03 100644 --- a/mig-xml/evergreen_staged_report.xml +++ b/mig-xml/evergreen_staged_report.xml @@ -1,9 +1,9 @@ find_cmm - DROP FUNCTION IF EXISTS mig_func_schema.find_cmm(BIGINT) + DROP FUNCTION IF EXISTS find_cmm(BIGINT) - CREATE OR REPLACE FUNCTION mig_func_schema.find_cmm(circ_id BIGINT) + CREATE OR REPLACE FUNCTION find_cmm(circ_id BIGINT) RETURNS SETOF INTEGER[] LANGUAGE plpgsql AS $function$ @@ -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 MIGSCHEMA.money_billing_legacy GROUP BY 2; query itself, MIGSCHEMA will be replaced + SELECT COUNT(id),x_migrate::TEXT,SUM(amount) FROM money_billing_legacy GROUP BY 2; query itself, will be replaced Arbitrary note that can be included in the entries. --> @@ -46,7 +46,7 @@ 0 Open Circulations Circulation Status.Migrated.Count of Circs - SELECT 'Closed Circulations', x_migrate::TEXT, COUNT(id) FROM MIGSCHEMA.action_circulation_legacy WHERE xact_finish IS NOT NULL GROUP BY 2 UNION ALL SELECT 'Open Circulations', x_migrate::TEXT, COUNT(id) FROM MIGSCHEMA.action_circulation_legacy WHERE xact_finish IS NULL GROUP BY 2 + SELECT 'Closed Circulations', x_migrate::TEXT, COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NOT NULL GROUP BY 2 UNION ALL SELECT 'Open Circulations', x_migrate::TEXT, COUNT(id) FROM action_circulation_legacy WHERE xact_finish IS NULL GROUP BY 2 @@ -55,7 +55,7 @@ 1 Open Circulations Circulation Status.Count of Circs - SELECT 'Closed Circulations', COUNT(id) FROM MIGSCHEMA.action_circulation WHERE xact_finish IS NOT NULL UNION ALL SELECT 'Open Circulations', COUNT(id) FROM MIGSCHEMA.action_circulation WHERE xact_finish IS NULL + 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 @@ -73,7 +73,7 @@ 0 Circulations by Org Unit Circulations Count.Migrated.Org Unit - SELECT COUNT(acirc.id), acirc.x_migrate::TEXT, aou.name FROM MIGSCHEMA.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 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 @@ -82,7 +82,7 @@ 1 Circulations by Org Unit Circulations Count.Org Unit - SELECT COUNT(acirc.id), aou.name FROM MIGSCHEMA.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 action_circulation acirc JOIN actor.org_unit aou ON aou.id = acirc.circ_lib WHERE acirc.xact_finish IS NULL GROUP BY 2 @@ -91,7 +91,7 @@ 0 Migrated Circulations by Duration, Fine and Max Fine Count of Circs.Duration.Fine.Max Fine.Migrated - SELECT COUNT(id), duration, recurring_fine, max_fine, x_migrate::TEXT FROM MIGSCHEMA.action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4, 5 + SELECT COUNT(id), duration, recurring_fine, max_fine, x_migrate::TEXT FROM action_circulation_legacy WHERE x_migrate = TRUE GROUP BY 2, 3, 4, 5 @@ -100,7 +100,7 @@ 1 Circulations by Duration, Fine and Max Fine Count of Circs.Duration.Fine.Max Fine - SELECT COUNT(id), duration, recurring_fine, max_fine FROM MIGSCHEMA.action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 + SELECT COUNT(id), duration, recurring_fine, max_fine FROM action_circulation GROUP BY 2, 3, 4 ORDER BY 2, 3, 4 @@ -109,7 +109,7 @@ 0 Circulations by Rules and Patron Group Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints - SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, pgt.name, x.buildrows FROM MIGSCHEMA.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(mig_func_schema.find_cmm(acirc.id),',') AS buildrows FROM MIGSCHEMA.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, acirc.recurring_fine, acirc.max_fine, 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 @@ -118,7 +118,7 @@ 1 Circulations by Rules and Patron Group Count of Circs.Duration.Fine.Max Fine.User Group.Matchpoints - SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, pgt.name, x.buildrows FROM MIGSCHEMA.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(mig_func_schema.find_cmm(acirc.id),',') AS buildrows FROM MIGSCHEMA.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, acirc.recurring_fine, acirc.max_fine, 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 @@ -127,7 +127,7 @@ 0 Circulations by Rules and Circulation Modifier Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints - SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, ac.circ_modifier, x.buildrows FROM MIGSCHEMA.action_circulation_legacy acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(mig_func_schema.find_cmm(acirc.id),',') AS buildrows FROM MIGSCHEMA.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, acirc.recurring_fine, acirc.max_fine, 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 @@ -136,7 +136,7 @@ 1 Circulations by Rules and Circulation Modifier Count of Circs.Duration.Fine.Max Fine.Circulation Modifier.Matchpoints - SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, ac.circ_modifier, x.buildrows FROM MIGSCHEMA.action_circulation acirc JOIN asset.copy ac ON ac.id = acirc.target_copy JOIN (SELECT acirc.id, ARRAY_TO_STRING(mig_func_schema.find_cmm(acirc.id),',') AS buildrows FROM MIGSCHEMA.action_circulation acirc) x ON x.id = acirc.id + SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, 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 GROUP BY 2, 3, 4, 5, 6 ORDER BY 2, 3, 4, 5, 6 @@ -146,7 +146,7 @@ 0 Circulations by Rules and Org Unit Count of Circs.Duration.Fine.Max Fine.Library Branch - SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, aou.name FROM MIGSCHEMA.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, acirc.recurring_fine, acirc.max_fine, 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 @@ -155,7 +155,7 @@ 1 Circulations by Rules and Org Unit Count of Circs.Duration.Fine.Max Fine.Library Branch - SELECT COUNT(acirc.id), acirc.duration, acirc.recurring_fine, acirc.max_fine, aou.name FROM MIGSCHEMA.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, acirc.recurring_fine, acirc.max_fine, 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 @@ -164,7 +164,7 @@ 0 Non-Cataloged Circulation Circulations Count.Migrated - SELECT COUNT(id), x_migrate::TEXT FROM MIGSCHEMA.action_non_cataloged_circulation_legacy GROUP BY 2 + SELECT COUNT(id), x_migrate::TEXT FROM action_non_cataloged_circulation_legacy GROUP BY 2 @@ -173,7 +173,7 @@ 1 Non-Cataloged Circulation Circulations Count - SELECT COUNT(id) FROM MIGSCHEMA.action_non_cataloged_circulation + SELECT COUNT(id) FROM action_non_cataloged_circulation @@ -182,7 +182,7 @@ 0 In House Use In House Use Records.Migrated - SELECT COUNT(id), x_migrate::TEXT FROM MIGSCHEMA.action_in_house_use_legacy GROUP BY 2 + SELECT COUNT(id), x_migrate::TEXT FROM action_in_house_use_legacy GROUP BY 2 @@ -191,7 +191,7 @@ 1 In House Use In House Use Records - SELECT COUNT(id) FROM MIGSCHEMA.action_in_house_use + SELECT COUNT(id) FROM action_in_house_use @@ -200,12 +200,12 @@ 1 Circs Missing Rules Count.Field Missing - SELECT COUNT(id), 'Duration Rule Value' FROM MIGSCHEMA.action_circulation WHERE duration IS NULL - UNION ALL SELECT COUNT(id), 'Recurring Fine Rule Value' FROM MIGSCHEMA.action_circulation WHERE recurring_fine IS NULL - UNION ALL SELECT COUNT(id), 'Max Fine Rule Value' FROM MIGSCHEMA.action_circulation WHERE max_fine IS NULL - UNION ALL SELECT COUNT(id), 'Duration Rule' FROM MIGSCHEMA.action_circulation WHERE duration_rule IS NULL - UNION ALL SELECT COUNT(id), 'Recurring Fine Rule' FROM MIGSCHEMA.action_circulation WHERE recurring_fine_rule IS NULL - UNION ALL SELECT COUNT(id), 'Max Fine Rule' FROM MIGSCHEMA.action_circulation WHERE max_fine_rule IS NULL + 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 @@ -215,7 +215,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 MIGSCHEMA.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 action_circulation) GROUP BY 2 ORDER BY 2 @@ -226,7 +226,7 @@ 0 Migrated Holds Hold Type.Hold Count.Migrated - SELECT 'Closed Holds', COUNT(id), x_migrate::TEXT FROM MIGSCHEMA.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 MIGSCHEMA.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 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 @@ -235,7 +235,7 @@ 0 Migrated Holds By Type Hold Type.Hold Count.Migrated - SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM MIGSCHEMA.action_hold_request_legacy GROUP BY 1, 3 + SELECT hold_type as "Hold Type", COUNT(id), x_migrate::TEXT FROM action_hold_request_legacy GROUP BY 1, 3 @@ -244,7 +244,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 MIGSCHEMA.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 action_transit_copy) AND atc.dest_recv_time IS NULL GROUP BY 2 ORDER BY 2 @@ -253,7 +253,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 MIGSCHEMA.action_transit_copy) AND dest_recv_time IS NULL) atc ON atc.target_copy = ac.id WHERE ac.id IN (SELECT id from MIGSCHEMA.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 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 @@ -262,7 +262,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 MIGSCHEMA.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 action_hold_request) GROUP BY 2 ORDER By 2 @@ -271,7 +271,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 MIGSCHEMA.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 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 @@ -282,7 +282,7 @@ assets 0 Copy Count.Library.Migrated - SELECT COUNT(ac.id), aou.name, ac.x_migrate::TEXT FROM MIGSCHEMA.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 asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib GROUP BY 2, 3 ORDER BY 2, 3 @@ -291,7 +291,7 @@ assets 0 Copy Count.Deleted.Migrated - SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM MIGSCHEMA.asset_copy_legacy ac GROUP BY 2, 3 + SELECT COUNT(ac.id), ac.deleted::TEXT, ac.x_migrate::TEXT FROM asset_copy_legacy ac GROUP BY 2, 3 @@ -300,7 +300,7 @@ assets 0 Copy Count.Status.Migrated - SELECT COUNT(ac.id), cs.name, ac.x_migrate::TEXT FROM MIGSCHEMA.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 asset_copy_legacy ac JOIN config.copy_status cs ON cs.id = ac.status GROUP BY 2, 3 ORDER BY 2, 3 @@ -309,7 +309,7 @@ assets 0 Copy Count.Circulation Modifier.Migrated - SELECT COUNT(ac.id), ac.circ_modifier, ac.x_migrate::TEXT FROM MIGSCHEMA.asset_copy_legacy ac GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(ac.id), ac.circ_modifier, ac.x_migrate::TEXT FROM asset_copy_legacy ac GROUP BY 2, 3 ORDER BY 2, 3 @@ -318,7 +318,7 @@ assets 0 Note Count.Public.Migrated - SELECT COUNT(acnote.id), acnote.pub::TEXT, acnote.x_migrate::TEXT FROM MIGSCHEMA.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 asset_copy_note_legacy acnote GROUP BY 2, 3 ORDER BY 2, 3 @@ -327,7 +327,7 @@ assets 1 Note Count.Public - SELECT COUNT(acnote.id), acnote.pub::TEXT FROM MIGSCHEMA.asset_copy_note acnote GROUP BY 2 ORDER BY 2 + SELECT COUNT(acnote.id), acnote.pub::TEXT FROM asset_copy_note acnote GROUP BY 2 ORDER BY 2 @@ -336,7 +336,7 @@ assets 0 Volume Count.Library.Migrated - SELECT COUNT(acn.id), aou.name, acn.x_migrate::TEXT FROM MIGSCHEMA.asset_call_number_legacy acn JOIN MIGSCHEMA.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 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 @@ -345,7 +345,7 @@ assets 1 Volume Count.Library - SELECT COUNT(acn.id), aou.name FROM MIGSCHEMA.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 asset_call_number acn JOIN actor.org_unit aou ON aou.id = acn.owning_lib GROUP BY 2 ORDER BY 2 @@ -363,7 +363,7 @@ assets 0 Copy Count.Library.Circ Library.Migrated - SELECT COUNT(ac.id), acl.name, aou.name, ac.x_migrate::TEXT FROM MIGSCHEMA.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 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 @@ -372,7 +372,7 @@ assets 0 Count of Barcode.Barcode Length.Library - SELECT COUNT(ac.id), LENGTH(ac.barcode), aou.name FROM MIGSCHEMA.asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib WHERE ac.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 3, 2 + SELECT COUNT(ac.id), LENGTH(ac.barcode), aou.name FROM asset_copy_legacy ac JOIN actor.org_unit aou ON aou.id = ac.circ_lib WHERE ac.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 3, 2 @@ -381,7 +381,7 @@ 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 MIGSCHEMA.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 asset_copy_legacy ac WHERE ac.x_migrate = TRUE GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 @@ -390,7 +390,7 @@ assets 0 Collision Count - SELECT COUNT(id) FROM MIGSCHEMA.asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'incumbent' + SELECT COUNT(id) FROM asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'incumbent' Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database. @@ -400,7 +400,7 @@ assets 1 Collision Count - SELECT COUNT(id) FROM MIGSCHEMA.asset_copy WHERE barcode ~* 'collision' and barcode ~* 'incumbent' + SELECT COUNT(id) FROM asset_copy WHERE barcode ~* 'collision' and barcode ~* 'incumbent' Incumbent collisions are those where the migrated barcodes collide with existing barcodes in the database. @@ -410,7 +410,7 @@ assets 0 Collision Count - SELECT COUNT(id) FROM MIGSCHEMA.asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'internal' + SELECT COUNT(id) FROM asset_copy_legacy WHERE x_migrate = TRUE AND barcode ~* 'collision' and barcode ~* 'internal' Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes. @@ -420,7 +420,7 @@ assets 1 Collision Count - SELECT COUNT(id) FROM MIGSCHEMA.asset_copy WHERE barcode ~* 'collision' and barcode ~* 'internal' + SELECT COUNT(id) FROM asset_copy WHERE barcode ~* 'collision' and barcode ~* 'internal' Internal collisions are those where the migrated barcodes have conflicts within their list of barcodes. @@ -430,7 +430,7 @@ assets 0 Collision List - SELECT ac.barcode FROM MIGSCHEMA.asset_copy_legacy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20 + SELECT ac.barcode FROM asset_copy_legacy ac WHERE ac.barcode ~* 'collision' 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. @@ -440,7 +440,7 @@ assets 1 Collision List - SELECT ac.barcode FROM MIGSCHEMA.asset_copy ac WHERE ac.barcode ~* 'collision' ORDER BY 1 LIMIT 20 + SELECT ac.barcode FROM asset_copy ac WHERE ac.barcode ~* 'collision' 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. @@ -450,7 +450,7 @@ 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 MIGSCHEMA.asset_copy_legacy ac WHERE barcode ~* 'collision' 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 asset_copy_legacy ac WHERE barcode ~* 'collision' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 @@ -459,7 +459,7 @@ 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 MIGSCHEMA.asset_copy ac WHERE barcode ~* 'collision' 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 asset_copy ac WHERE barcode ~* 'collision' GROUP BY 2 HAVING COUNT(ac.id) > 10 ORDER BY 2 @@ -468,7 +468,7 @@ assets 0 Stat Cat Count.Library.Statistical Category - SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM MIGSCHEMA.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 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 @@ -477,7 +477,7 @@ assets 1 Stat Cat Count.Library.Statistical Category - SELECT COUNT(ac_sc.id), aou.name, ac_sc.name FROM MIGSCHEMA.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 asset_stat_cat ac_sc JOIN actor.org_unit aou ON aou.id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 @@ -486,7 +486,7 @@ assets 0 Copy Stat Count.Library.Statistical Category - SELECT COUNT(map.id), aou.name, ac_sc.name FROM MIGSCHEMA.asset_stat_cat_entry_copy_map_legacy map JOIN MIGSCHEMA.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 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 @@ -495,7 +495,7 @@ assets 1 Copy Stat Count.Library.Statistical Category - SELECT COUNT(map.id), aou.name, ac_sc.name FROM MIGSCHEMA.asset_stat_cat_entry_copy_map map JOIN MIGSCHEMA.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 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. id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 @@ -505,7 +505,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 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 MIGSCHEMA.asset_copy_tag tag JOIN MIGSCHEMA.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 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 @@ -516,7 +516,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM MIGSCHEMA.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 money_billing_legacy a GROUP BY 2, 4 ORDER BY 2, 4 @@ -525,7 +525,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 1 Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM MIGSCHEMA.money_billing a GROUP BY 2 ORDER BY 2, 3 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_billing a GROUP BY 2 ORDER BY 2, 3 @@ -534,7 +534,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 0 Count.Billing Type.Migrated - SELECT COUNT(a.id), a.billing_type, a.x_migrate::TEXT FROM MIGSCHEMA.money_billing_legacy a GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(a.id), a.billing_type, a.x_migrate::TEXT FROM money_billing_legacy a GROUP BY 2, 3 ORDER BY 2, 3 @@ -543,7 +543,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 1 Count.Billing Type - SELECT COUNT(a.id), a.billing_type FROM MIGSCHEMA.money_billing a GROUP BY 2 ORDER BY 2 + SELECT COUNT(a.id), a.billing_type FROM money_billing a GROUP BY 2 ORDER BY 2 @@ -552,7 +552,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM MIGSCHEMA.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 money_cash_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 @@ -561,7 +561,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 1 Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM MIGSCHEMA.money_cash_payment a GROUP BY 2 ORDER BY 2 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_cash_payment a GROUP BY 2 ORDER BY 2 @@ -570,7 +570,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM MIGSCHEMA.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 money_check_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 @@ -579,7 +579,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM MIGSCHEMA.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 money_forgive_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 @@ -588,7 +588,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 1 Count.Voided.Sum - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM MIGSCHEMA.money_forgive_paymen a GROUP BY 2 ORDER BY 2 + SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount) FROM money_forgive_paymen a GROUP BY 2 ORDER BY 2 @@ -597,7 +597,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM MIGSCHEMA.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 money_goods_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 @@ -606,7 +606,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM MIGSCHEMA.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 money_work_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 @@ -615,7 +615,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM MIGSCHEMA.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 money_credit_card_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 @@ -624,7 +624,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 money 0 Count.Voided.Sum.Migrated - SELECT COUNT(a.id), a.voided::TEXT, SUM(a.amount), a.x_migrate::TEXT FROM MIGSCHEMA.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 money_credit_payment_legacy a GROUP BY 2, 4 ORDER BY 2, 4 @@ -636,7 +636,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 0 Count.Deleted.Migrated - SELECT COUNT(bre.id), bre.deleted::TEXT, bre.x_migrate::TEXT FROM MIGSCHEMA.biblio_record_entry_legacy bre GROUP BY 2, 3 ORDER BY 2, 3 + SELECT COUNT(bre.id), bre.deleted::TEXT, bre.x_migrate::TEXT FROM biblio_record_entry_legacy bre GROUP BY 2, 3 ORDER BY 2, 3 False means the records are not deleted. @@ -646,7 +646,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 1 Count - SELECT COUNT(eg) FROM MIGSCHEMA.bib_id_map + SELECT COUNT(eg) FROM bib_id_map @@ -655,7 +655,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 0 Count - SELECT COUNT(eg) FROM MIGSCHEMA.bib_id_map where eg::INTEGER NOT IN (SELECT DISTINCT record FROM MIGSCHEMA.asset_call_number) + SELECT COUNT(eg) FROM bib_id_map where eg::INTEGER NOT IN (SELECT DISTINCT record FROM asset_call_number) @@ -664,7 +664,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 MIGSCHEMA.biblio_record_note_legacy b GROUP BY 2 + SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_record_note_legacy b GROUP BY 2 @@ -673,7 +673,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 1 Count - SELECT COUNT(b.id) FROM MIGSCHEMA.biblio_record_note b + SELECT COUNT(b.id) FROM biblio_record_note b @@ -682,7 +682,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 MIGSCHEMA.biblio_peer_bib_copy_map_legacy b GROUP BY 2 + SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_peer_bib_copy_map_legacy b GROUP BY 2 @@ -691,7 +691,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 1 Count - SELECT COUNT(b.id) FROM MIGSCHEMA.biblio_peer_bib_copy_map b + SELECT COUNT(b.id) FROM biblio_peer_bib_copy_map b @@ -700,7 +700,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 MIGSCHEMA.biblio_monograph_part_legacy b GROUP BY 2 + SELECT COUNT(b.id), b.x_migrate::TEXT FROM biblio_monograph_part_legacy b GROUP BY 2 @@ -709,7 +709,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 bibs 1 Count - SELECT COUNT(b.id) FROM MIGSCHEMA.biblio_monograph_part b + SELECT COUNT(b.id) FROM biblio_monograph_part b @@ -720,7 +720,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 MIGSCHEMA.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 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 @@ -729,7 +729,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 MIGSCHEMA.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 actor_usr_legacy au JOIN permission.grp_tree pgt ON pgt.id = au.profile GROUP BY 2, 3 ORDER BY 2, 3 @@ -738,7 +738,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 MIGSCHEMA.actor_usr_legacy GROUP BY 2, 3 + SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_usr_legacy GROUP BY 2, 3 @@ -747,7 +747,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 MIGSCHEMA.actor_usr GROUP BY 2 + SELECT COUNT(id), active::TEXT FROM actor_usr GROUP BY 2 @@ -756,7 +756,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 MIGSCHEMA.actor_card_legacy GROUP BY 2, 3 + SELECT COUNT(id), active::TEXT, x_migrate::TEXT FROM actor_card_legacy GROUP BY 2, 3 @@ -765,7 +765,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 MIGSCHEMA.actor_card GROUP BY 2 + SELECT COUNT(id), active::TEXT FROM actor_card GROUP BY 2 @@ -774,7 +774,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Count of Barcode.Barcode Length.Library - SELECT COUNT(acard.id), LENGTH(acard.barcode), aou.name FROM MIGSCHEMA.actor_card_legacy acard JOIN MIGSCHEMA.actor_usr_legacy au ON au.id = acard.usr JOIN actor.org_unit aou ON aou.id = au.home_ou WHERE acard.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 3, 2 + SELECT COUNT(acard.id), LENGTH(acard.barcode), aou.name FROM actor_card_legacy acard JOIN actor_usr_legacy au ON au.id = acard.usr JOIN actor.org_unit aou ON aou.id = au.home_ou WHERE acard.x_migrate = TRUE GROUP BY 2, 3 ORDER BY 3, 2 @@ -783,7 +783,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Count of Barcode.Barcode Length.Library - SELECT COUNT(acard.id), LENGTH(acard.barcode), aou.name FROM MIGSCHEMA.actor_card acard JOIN MIGSCHEMA.actor_usr au ON au.id = acard.usr JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3 ORDER BY 3, 2 + SELECT COUNT(acard.id), LENGTH(acard.barcode), aou.name FROM actor_card acard JOIN actor_usr au ON au.id = acard.usr JOIN actor.org_unit aou ON aou.id = au.home_ou GROUP BY 2, 3 ORDER BY 3, 2 @@ -792,7 +792,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 MIGSCHEMA.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 actor_card_legacy acard WHERE acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 @@ -801,7 +801,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 MIGSCHEMA.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 actor_card acard GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 @@ -810,7 +810,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Collision Count - SELECT COUNT(acard.id) FROM MIGSCHEMA.actor_card_legacy acard WHERE barcode ~* 'collision' AND x_migrate = TRUE + SELECT COUNT(acard.id) FROM actor_card_legacy acard WHERE barcode ~* 'collision' AND x_migrate = TRUE @@ -819,7 +819,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Collision Count - SELECT COUNT(acard.id) FROM MIGSCHEMA.actor_card acard WHERE barcode ~* 'collision' + SELECT COUNT(acard.id) FROM actor_card acard WHERE barcode ~* 'collision' @@ -828,7 +828,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 0 Collision List - SELECT acard.barcode FROM MIGSCHEMA.actor_card_legacy acard WHERE acard.barcode ~* 'collision' AND acard.x_migrate = TRUE ORDER BY 1 LIMIT 20 + SELECT acard.barcode FROM actor_card_legacy acard WHERE acard.barcode ~* 'collision' 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. @@ -838,7 +838,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Collision List - SELECT acard.barcode FROM MIGSCHEMA.actor_card acard WHERE acard.barcode ~* 'collision' ORDER BY 1 LIMIT 20 + SELECT acard.barcode FROM actor_card acard WHERE acard.barcode ~* 'collision' 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. @@ -848,7 +848,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 MIGSCHEMA.actor_card_legacy acard WHERE acard.barcode ~* 'collision' 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 actor_card_legacy acard WHERE acard.barcode ~* 'collision' AND acard.x_migrate = TRUE GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 @@ -857,7 +857,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 MIGSCHEMA.actor_card acard WHERE acard.barcode ~* 'collision' 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 actor_card acard WHERE acard.barcode ~* 'collision' GROUP BY 2 HAVING COUNT(acard.id) > 10 ORDER BY 2 @@ -866,7 +866,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 MIGSCHEMA.actor_usr_address_legacy aua GROUP BY 2, 3 + SELECT COUNT(aua.id), valid::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3 @@ -875,7 +875,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Count.Valid - SELECT COUNT(aua.id), valid::TEXT FROM MIGSCHEMA.actor_usr_address aua GROUP BY 2 + SELECT COUNT(aua.id), valid::TEXT FROM actor_usr_address aua GROUP BY 2 @@ -884,7 +884,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 MIGSCHEMA.actor_usr_address_legacy aua GROUP BY 2, 3 + SELECT COUNT(aua.id), pending::TEXT, x_migrate::TEXT FROM actor_usr_address_legacy aua GROUP BY 2, 3 @@ -893,7 +893,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 MIGSCHEMA.actor_usr_address aua GROUP BY 2 + SELECT COUNT(aua.id), pending::TEXT FROM actor_usr_address aua GROUP BY 2 @@ -902,7 +902,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 MIGSCHEMA.actor_usr_message_legacy aum GROUP BY 2, 3 + SELECT COUNT(aum.id), deleted::TEXT, x_migrate::TEXT FROM actor_usr_message_legacy aum GROUP BY 2, 3 @@ -911,7 +911,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Count.Deleted - SELECT COUNT(aum.id), deleted::TEXT FROM MIGSCHEMA.actor_usr_message_legacy aum GROUP BY 2 + SELECT COUNT(aum.id), deleted::TEXT FROM actor_usr_message_legacy aum GROUP BY 2 @@ -920,7 +920,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 MIGSCHEMA.actor_usr_note_legacy aun GROUP BY 2, 3 + SELECT COUNT(aun.id), pub::TEXT, x_migrate::TEXT FROM actor_usr_note_legacy aun GROUP BY 2, 3 @@ -929,7 +929,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 actors 1 Count.Public - SELECT COUNT(aun.id), pub::TEXT FROM MIGSCHEMA.actor_usr_note aun GROUP BY 2 + SELECT COUNT(aun.id), pub::TEXT FROM actor_usr_note aun GROUP BY 2 @@ -938,7 +938,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 MIGSCHEMA.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 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 @@ -947,7 +947,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 MIGSCHEMA.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 actor_stat_cat au_sc JOIN actor.org_unit aou ON aou.id = au_sc.owner GROUP BY 2, 3 ORDER BY 2, 3 @@ -956,7 +956,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 MIGSCHEMA.actor_stat_cat_entry_usr_map_legacy map JOIN MIGSCHEMA.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 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 @@ -965,7 +965,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 MIGSCHEMA.actor_stat_cat_entry_usr_map map JOIN MIGSCHEMA.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 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 @@ -975,7 +975,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 MIGSCHEMA.acq_fund_legacy GROUP BY 2; + SELECT COUNT(id), x_migrate::TEXT FROM acq_fund_legacy GROUP BY 2; @@ -984,7 +984,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 acq Migrated Funds Number of Funds - SELECT COUNT(id) FROM MIGSCHEMA.acq_fund; + SELECT COUNT(id) FROM acq_fund; @@ -993,7 +993,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 MIGSCHEMA.acq_invoice_legacy GROUP BY 2; + SELECT COUNT(id), x_migrate::TEXT FROM acq_invoice_legacy GROUP BY 2; @@ -1002,7 +1002,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 acq Migrated Invoices Number of Funds - SELECT COUNT(id) FROM MIGSCHEMA.acq_invoice; + SELECT COUNT(id) FROM acq_invoice; @@ -1012,7 +1012,7 @@ id = ac_sc.owner GROUP BY 2,3 ORDER BY 2,3 0 Migrated Serial MFHDs Number of MFHDs - SELECT COUNT(id) FROM MIGSCHEMA.seriarecord_entry + SELECT COUNT(id) FROM seriarecord_entry -- 1.7.2.5