3 # Copyright (C) 2022 MOBIUS
4 # Author: Blake Graham-Henderson <blake@mobiusconsortium.org>
6 # This program is free software; you can redistribute it and/or
7 # modify it under the terms of the GNU General Public License
8 # as published by the Free Software Foundation; either version 2
9 # of the License, or (at your option) any later version.
11 # This program is distributed in the hope that it will be useful,
12 # but WITHOUT ANY WARRANTY; without even the implied warranty of
13 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 # GNU General Public License for more details.
15 # ---------------------------------------------------------------
21 use File::Path qw(make_path);
22 use Cwd qw/abs_path getcwd/;
29 our $databaseName = 'postgres';
39 our $seedTableUsedRowCount = 0;
40 our $seedTableRowCount = 100000;
42 our $doTestRestore = 0;
45 our $nonInteractive = 0;
47 our $egRepoDestBranch = 'master';
53 'actor.workstation_setting',
55 'acq.acq_lineitem_history',
56 'acq.acq_purchase_order_history',
58 'asset.copy_vis_attr_cache',
59 'authority.rec_descriptor',
60 'authority.simple_heading',
62 'authority.authority_linking',
63 'actor.org_unit_proximity',
64 'config.org_unit_setting_type_log',
65 'config.xml_transform',
66 'money.materialized_billable_xact_summary',
67 'serial.materialized_holding_code',
68 'vandelay.queued_bib_record_attr',
69 'config.print_template',
70 'config.workstation_setting_type',
71 'permission.grp_perm_map',
72 'permission.perm_list',
81 # call number data can include ##URI##,
82 # which biblio.record_entry triggers create, so, they go first
85 'asset.uri_call_number_map',
86 'biblio.record_entry',
87 'biblio.monograph_part',
91 'acq.lineitem_detail',
94 'asset.copy_location',
97 'authority.record_entry',
99 'money.billable_xact',
102 # needs to come before actor.workstation
103 'money.bnm_desk_payment',
106 # a mechanism to call out special tables where we don't want the default behavior
107 # here we can teach this software which columns need to be used for comparison
108 # to seed data, and which columns we need to output into our SQL load file
109 our %tableColumnOverride = (
111 # the id column isn't important,
112 # we need to ensure that we abide by the unique constraints
113 # comp is "comparison", these columns are what we use to dedupe from seed data
114 # load is "load these columns only", any other columns in the table will receive PG defaults
115 'actor.org_unit_setting' => {
116 'comp' => [ 'org_unit', 'name' ],
117 'load' => [ 'org_unit', 'name', 'value' ]
119 'config.metabib_class' => {
120 'comp' => [ 'name', 'label' ]
122 'config.org_unit_setting_type' => {
123 'comp' => [ 'name', 'label' ]
125 'config.global_flag' => {
130 our $help = "Usage: ./make_concerto_from_evergreen_db.pl [OPTION]...
132 This program automates the process of making a new dataset for the Evergreen code repository.
133 We need connection details to a postgres database. The provided database user needs to have
134 permissions to create databases.
136 This code will accept a pre-created seed database or it can create it's own. A blank \"seed\"
137 Evergreen database is needed for comparison reasons. It uses this as a reference to determine
138 which data is seed data and which data is not.
141 --db-host postgresql server hostname/IP
142 --db-user Database Username to connect
143 --db-pass Database password to connect
144 --db-port Database port to connect
145 --evergreen-repo Folder path to the root of the Evergreen git repository
146 --output-folder Folder for our generated output
149 --perform-upgrade This routine will restore previously generated dataset and upgrade it to match the
150 provided Evergreen repository version.
151 --non-interactive Suppress user input prompts
152 --db-name Enhanced Concerto source postgres database name if you're generating a new dataset.
153 --test-restore This option will cause the software to create a new database and populate it with the previously generated dataset
154 --debug Set debug mode for more verbose output.
155 --create-seed-db This option will create a new DB from the version of Evergreen that the dataset was from. Just the seed DB, no data.
156 --seed-from-egdbid Supply the software the database ID number form which to create the seed database (used in conjunction with --create-seed-db)
157 --seed-db-name Evergreen database name for the seed database, created with --create-database --create-schema, NOT --load-all-sample
158 If you don't provide this, we will attempt to create one based upon a previously generated dataset located in
159 --output-folder. However, this will be required if you do not have a previously generated dataset.
162 Generate new dataset from existing DB:
163 ./make_concerto_from_evergreen_db.pl \
164 --db-host localhost \
165 --db-user evergreen \
166 --db-pass evergreen \
168 --db-name eg_enhanced \
169 --output-folder output \
170 --seed-db-name seed_from_1326 \
171 --evergreen-repo /home/opensrf/repos/Evergreen
173 If you don't have a seed database, you can omit it, and we'll make one based
174 upon the version we find in the file <output_folder>/config.upgrade_log.sql
175 ./make_concerto_from_evergreen_db.pl \
176 --db-host localhost \
177 --db-user evergreen \
178 --db-pass evergreen \
180 --db-name eg_enhanced \
181 --output-folder output \
182 --evergreen-repo /home/opensrf/repos/Evergreen
184 Or, you can have this software make a seed DB, and that's all it will do.
185 The version of Evergreen it will use will be found in <output_folder>/config.upgrade_log.sql
186 ./make_concerto_from_evergreen_db.pl \
187 --db-host localhost \
188 --db-user evergreen \
189 --db-pass evergreen \
191 --output-folder output \
192 --evergreen-repo /home/opensrf/repos/Evergreen \
195 Or, you can have this software make a seed DB based on your specified version of Evergreen
196 ./make_concerto_from_evergreen_db.pl \
197 --db-host localhost \
198 --db-user evergreen \
199 --db-pass evergreen \
201 --output-folder output \
202 --evergreen-repo /home/opensrf/repos/Evergreen \
204 --seed-from-egdbid 1350
206 Upgrade a previously-created dataset. Use this when cutting new releases of Evergreen and you want to include
207 the enhanced dataset to match. It will use the current git branch found in the provided path to the EG repo.
208 ./make_concerto_from_evergreen_db.pl \
209 --db-host localhost \
210 --db-user evergreen \
211 --db-pass evergreen \
213 --output-folder output \
214 --evergreen-repo /home/opensrf/repos/Evergreen \
217 Test the existing dataset. Create a new database and restore the dataset.
218 The software will first create a database that matches the version of Evergreen in the
219 dataset output folder, then restore the dataset into the newly created database.
220 ./make_concerto_from_evergreen_db.pl \
221 --db-host localhost \
222 --db-user evergreen \
223 --db-pass evergreen \
225 --output-folder output \
226 --evergreen-repo /home/opensrf/repos/Evergreen \
232 "db-host=s" => \$dbhost,
233 "db-name=s" => \$databaseName,
234 "db-user=s" => \$dbuser,
235 "db-pass=s" => \$dbpass,
236 "db-port=s" => \$dbport,
237 "seed-db-name=s" => \$seedDBName,
238 "output-folder=s" => \$outputFolder,
240 "evergreen-repo=s" => \$egRepoPath,
241 "perform-upgrade" => \$doUpgrade,
242 "test-restore" => \$doTestRestore,
243 "non-interactive" => \$nonInteractive,
244 "create-seed-db" => \$doSeedOnly,
245 "seed-from-egdbid=s" => \$seedFrom,
252 createSeedDB() if $doSeedOnly;
254 start() if !$doUpgrade && !$doTestRestore;
256 upgrade() if $doUpgrade;
258 testRestore() if $doTestRestore;
262 # make the output folder if it doesn't exist
268 ) if ( !( -e $outputFolder ) );
270 my $currentEGDBVersionNum = getLastEGDBVersionFromOutput();
271 my $previousGitBranch = checkoutEGMatchingGitVersion($currentEGDBVersionNum);
272 my $tempDB = checkSeed();
273 gitCheckoutBranch( $previousGitBranch, 0, 1 );
275 # Gather a list of Evergreen Tables to process
276 my @evergreenTables = @{ getSchemaTables() };
278 "BEGIN;\n\n-- stop on error\n\\set ON_ERROR_STOP on\n\n"
279 . "-- Ignore constraints until we're done\nSET CONSTRAINTS ALL DEFERRED;\n\n";
281 while ( $#evergreenTables > -1 ) {
282 my $thisTable = shift @evergreenTables;
283 my $columnRef = shift @evergreenTables;
284 if ( checkTableForInclusion($thisTable) ) {
285 my $thisFile = $outputFolder . "/$thisTable.sql";
286 print "Processing $thisTable > $thisFile\n";
287 unlink $thisFile if -e $thisFile;
289 open( $thisFhandle, '>> ' . $thisFile );
290 binmode( $thisFhandle, ":utf8" );
291 my $lines = tableHandler( $thisTable, $columnRef, $thisFhandle );
293 unlink $thisFile if ( -e $thisFile && $lines == 0 );
295 # upgrade_log is written as part of the dataset, but not loaded.
296 # it's used purely to figure out what version of Evergreen we are on
297 # during this execution. So, later, when we run the upgrade procedure, we
298 # can figure out where we were when this was generated.
299 push( @loadTables, $thisTable )
300 if ( -e $thisFile && $lines > 0 && !( $thisFile =~ /config\.upgrade_log/ ) );
304 print "Skipping: $thisTable\n" if $debug;
307 $loadAll = loadTableOrderMaker( $loadAll, \@loadTables );
309 $loadAll .= "SELECT SETVAL('money.billable_xact_id_seq', (SELECT MAX(id) FROM money.billing));\n\n";
310 $loadAll .= "SELECT SETVAL('config.remote_account_id_seq', (SELECT MAX(id) FROM config.remote_account));\n\n";
311 $loadAll .= "SELECT SETVAL('money.payment_id_seq', (SELECT MAX(id) FROM money.payment));\n\n";
312 $loadAll .= "SELECT SETVAL('asset.copy_id_seq', (SELECT MAX(id) FROM asset.copy));\n\n";
313 $loadAll .= "SELECT SETVAL('vandelay.queue_id_seq', (SELECT MAX(id) FROM vandelay.queue));\n\n";
314 $loadAll .= "SELECT SETVAL('vandelay.queued_record_id_seq', (SELECT MAX(id) FROM vandelay.queued_record));\n\n";
315 $loadAll .= "SELECT SETVAL('acq.acq_lineitem_pkey_seq', (SELECT MAX(audit_id) FROM acq.acq_lineitem_history));\n\n";
316 $loadAll .= "SELECT SETVAL('acq.acq_purchase_order_pkey_seq', (SELECT MAX(audit_id) FROM acq.acq_purchase_order_history));\n\n";
317 $loadAll .= "SELECT SETVAL('actor.workstation_id_seq', (SELECT MAX(id) FROM actor.workstation_setting));\n\n";
318 $loadAll .= "SELECT SETVAL('actor.org_unit_id_seq', (SELECT MAX(id) FROM actor.org_unit));\n\n";
319 $loadAll .= "SELECT SETVAL('actor.usr_standing_penalty_id_seq', (SELECT MAX(id) FROM actor.usr_standing_penalty));\n\n";
320 $loadAll .= "SELECT SETVAL('actor.usr_message_id_seq', (SELECT MAX(id) FROM (SELECT MAX(id) \"id\" FROM actor.usr_standing_penalty UNION ALL SELECT MAX(id) \"id\" FROM actor.usr_message) AS a));\n\n";
322 $loadAll .= "COMMIT;\n";
323 $loadAll .= loaderDateCarryForward() . "\n";
325 print "Writing loader > $outputFolder/load_all.sql\n";
326 open( OUT, "> $outputFolder/load_all.sql" );
327 binmode( OUT, ":utf8" );
330 $dbHandler->disconnect;
331 $dbHandlerSeed->disconnect;
332 dropDB($tempDB) if $tempDB;
337 my $restoreDBName = getNextAvailableDBName();
338 print "Using database name: $restoreDBName\n" if $debug;
339 my $currentEGDBVersionNum = getLastEGDBVersionFromOutput();
340 userInput("Found this DB version from output: $currentEGDBVersionNum");
341 my $previousGitBranch = checkoutEGMatchingGitVersion($currentEGDBVersionNum);
342 populateDBFromCurrentGitBranch( $restoreDBName, 0 );
344 # now we have a temp database full of our concerto set
345 # created by the version of Evergreen that origainally made the dataset
346 # Now, swtich the repo back to the original branch that the user had
347 gitCheckoutBranch( $previousGitBranch, 0, 1 );
348 loadThisDataset( $restoreDBName, 1 );
349 upgradeDB( $restoreDBName, $currentEGDBVersionNum );
350 $seedDBName = getNextAvailableDBName();
351 populateDBFromCurrentGitBranch( $seedDBName, 0 );
353 dbhandler_setupConnection( $restoreDBName, $dbconf{"dbhost"}, $dbconf{"dbuser"}, $dbconf{"dbpass"},
355 dbhandler_setupConnection( $seedDBName, $dbconf{"dbhost"}, $dbconf{"dbuser"}, $dbconf{"dbpass"}, $dbconf{"port"},
358 userInput( "Done! If you'd like, you can pause here and take a peek at the generated databases before "
359 . "I drop them:\nFull DB: $restoreDBName\nSeed: $seedDBName" );
360 $dbHandler->disconnect;
361 $dbHandlerSeed->disconnect;
362 dropDB($restoreDBName);
368 my $currentEGDBVersionNum = getLastEGDBVersionFromOutput();
369 my $previousGitBranch = checkoutEGMatchingGitVersion($currentEGDBVersionNum);
370 my $restoreDB = checkSeed(1);
371 gitCheckoutBranch( $previousGitBranch, 0, 1 );
372 loadThisDataset( $restoreDB, 0 );
373 print "Created database: $restoreDB from provided output folder: $outputFolder\n";
378 my $currentEGDBVersionNum = $seedFrom ? $seedFrom : getLastEGDBVersionFromOutput();
379 my $restoreDBName = getNextAvailableDBName( "seed_db_$currentEGDBVersionNum" . "_" );
380 print "Using database name: $restoreDBName\n" if $debug;
381 my $previousGitBranch = checkoutEGMatchingGitVersion($currentEGDBVersionNum);
382 populateDBFromCurrentGitBranch( $restoreDBName, 0 );
383 gitCheckoutBranch( $previousGitBranch, 0, 1 );
384 print "Created a fresh seed DB from Evergreen Version: $currentEGDBVersionNum\n" . "DB name: $restoreDBName\n";
389 my $forceNewDB = shift || 0;
395 # a sanity check to make sure we can connect to the database and run a query
396 my @res = @{ dbhandler_query( "SELECT MAX(id) FROM biblio.record_entry", undef, 1 ) };
397 $valid = 1 if ( $#res > -1 );
400 # Seed database is missing, let's create one
401 if ( !$valid || $forceNewDB ) {
402 $seedDBName = getNextAvailableDBName();
403 $createdDB = $seedDBName;
404 populateDBFromCurrentGitBranch( $seedDBName, 0 );
405 dbhandler_setupConnection( $seedDBName, $dbconf{"dbhost"}, $dbconf{"dbuser"}, $dbconf{"dbpass"},
406 $dbconf{"port"}, 1 );
411 sub loadTableOrderMaker {
412 my $loadString = shift;
413 my $includedTablesRef = shift;
414 my @includedTables = @{$includedTablesRef};
417 # Loop through the pre-defined order, and check those off
418 foreach (@loadOrder) {
421 foreach (@includedTables) {
422 if ( $includedTables[$pos] eq $otable ) {
423 $loadString .= makeLoaderLine($_);
433 foreach (@includedTables) {
434 if ( not defined $used{$pos} ) {
435 $loadString .= makeLoaderLine($_);
447 my $ret = "\\echo loading $table\n";
448 $ret .= "\\i $table.sql\n\n";
452 sub loaderDateCarryForward {
453 my $ret = <<'splitter';
455 CREATE OR REPLACE FUNCTION evergreen.concerto_date_carry_tbl_col(tbl TEXT, col TEXT, datecarry INTERVAL)
456 RETURNS void AS $func$
462 current_offset BIGINT := 0;
463 chunk_size INT := 500;
464 max_rows BIGINT := 0;
468 squery := $$SELECT COUNT(*) FROM $$ || tbl;
470 EXECUTE squery INTO max_rows;
472 WHILE ucount > 0 LOOP
474 squery := $$UPDATE $$ || tbl || $$ o SET $$ || col || $$ = $$ || col || $$ + '$$ || datecarry || $$'::INTERVAL
475 FROM (SELECT id FROM $$ || tbl || $$ WHERE $$ || col || $$ IS NOT NULL ORDER BY id LIMIT $$ || chunk_size || $$ OFFSET $$ || current_offset || $$ ) AS j
478 -- Display what we're about to work on
479 -- SELECT INTO debug_output $$ $$ || squery || $$ $$
480 -- FROM biblio.record_entry LIMIT 1;
481 -- RAISE NOTICE '%', debug_output;
486 current_offset = current_offset + chunk_size;
488 squery := $$ SELECT COUNT(*) FROM (SELECT id FROM $$ || tbl || $$ ORDER BY id LIMIT $$ || chunk_size || $$ OFFSET $$ || current_offset || $$) a $$;
491 -- SELECT INTO debug_output $$ $$ || squery || $$ $$
492 -- FROM biblio.record_entry LIMIT 1;
493 -- RAISE NOTICE '%', debug_output;
495 EXECUTE squery INTO ucount;
497 RAISE NOTICE 'date carry forward: %.% % / %', tbl, col, current_offset, max_rows;
503 $func$ LANGUAGE plpgsql VOLATILE;
505 CREATE OR REPLACE FUNCTION evergreen.concerto_date_carry_all( skip_date_carry BOOLEAN DEFAULT FALSE )
512 IF NOT skip_date_carry THEN
514 SELECT INTO datediff (SELECT now() - lowdate FROM (SELECT MIN(create_date) lowdate FROM asset.call_number) as a);
517 PERFORM evergreen.concerto_date_carry_tbl_col('acq.claim_event', 'event_date', datediff);
519 -- acq.fund_allocation
520 PERFORM evergreen.concerto_date_carry_tbl_col('acq.fund_allocation', 'create_time', datediff);
523 PERFORM evergreen.concerto_date_carry_tbl_col('acq.fund_debit', 'create_time', datediff);
526 PERFORM evergreen.concerto_date_carry_tbl_col('acq.fund_transfer', 'transfer_time', datediff);
528 -- acq.funding_source_credit
529 PERFORM evergreen.concerto_date_carry_tbl_col('acq.funding_source_credit', 'deadline_date', datediff);
530 PERFORM evergreen.concerto_date_carry_tbl_col('acq.funding_source_credit', 'effective_date', datediff);
533 PERFORM evergreen.concerto_date_carry_tbl_col('acq.invoice', 'recv_date', datediff);
534 PERFORM evergreen.concerto_date_carry_tbl_col('acq.invoice', 'close_date', datediff);
537 PERFORM evergreen.concerto_date_carry_tbl_col('acq.lineitem', 'expected_recv_time', datediff);
538 PERFORM evergreen.concerto_date_carry_tbl_col('acq.lineitem', 'create_time', datediff);
539 PERFORM evergreen.concerto_date_carry_tbl_col('acq.lineitem', 'edit_time', datediff);
541 -- acq.lineitem_detail
542 PERFORM evergreen.concerto_date_carry_tbl_col('acq.lineitem_detail', 'recv_time', datediff);
544 -- acq.purchase_order
545 PERFORM evergreen.concerto_date_carry_tbl_col('acq.purchase_order', 'create_time', datediff);
546 PERFORM evergreen.concerto_date_carry_tbl_col('acq.purchase_order', 'edit_time', datediff);
547 PERFORM evergreen.concerto_date_carry_tbl_col('acq.purchase_order', 'order_date', datediff);
549 -- action.circulation
550 -- relying on action.push_circ_due_time() to take care of the 1 second before midnight logic
551 -- Omitting xact_start and xact_finish because those are going to get updated when the parent table is updated
552 PERFORM evergreen.concerto_date_carry_tbl_col('action.circulation', 'due_date', datediff);
553 PERFORM evergreen.concerto_date_carry_tbl_col('action.circulation', 'create_time', datediff);
554 PERFORM evergreen.concerto_date_carry_tbl_col('action.circulation', 'stop_fines_time', datediff);
555 PERFORM evergreen.concerto_date_carry_tbl_col('action.circulation', 'checkin_time', datediff);
557 -- action.hold_request
558 PERFORM evergreen.concerto_date_carry_tbl_col('action.hold_request', 'request_time', datediff);
559 PERFORM evergreen.concerto_date_carry_tbl_col('action.hold_request', 'capture_time', datediff);
560 PERFORM evergreen.concerto_date_carry_tbl_col('action.hold_request', 'fulfillment_time', datediff);
561 PERFORM evergreen.concerto_date_carry_tbl_col('action.hold_request', 'checkin_time', datediff);
562 PERFORM evergreen.concerto_date_carry_tbl_col('action.hold_request', 'return_time', datediff);
563 PERFORM evergreen.concerto_date_carry_tbl_col('action.hold_request', 'prev_check_time', datediff);
564 PERFORM evergreen.concerto_date_carry_tbl_col('action.hold_request', 'expire_time', datediff);
565 PERFORM evergreen.concerto_date_carry_tbl_col('action.hold_request', 'cancel_time', datediff);
566 PERFORM evergreen.concerto_date_carry_tbl_col('action.hold_request', 'thaw_date', datediff);
567 PERFORM evergreen.concerto_date_carry_tbl_col('action.hold_request', 'shelf_time', datediff);
568 PERFORM evergreen.concerto_date_carry_tbl_col('action.hold_request', 'shelf_expire_time', datediff);
571 PERFORM evergreen.concerto_date_carry_tbl_col('action.survey', 'start_date', datediff);
572 PERFORM evergreen.concerto_date_carry_tbl_col('action.survey', 'end_date', datediff);
574 -- action.survey_response
575 PERFORM evergreen.concerto_date_carry_tbl_col('action.survey_response', 'answer_date', datediff);
576 PERFORM evergreen.concerto_date_carry_tbl_col('action.survey_response', 'effective_date', datediff);
578 -- action.unfulfilled_hold_list
579 PERFORM evergreen.concerto_date_carry_tbl_col('action.unfulfilled_hold_list', 'fail_time', datediff);
581 -- actor.org_unit_closed
582 PERFORM evergreen.concerto_date_carry_tbl_col('actor.org_unit_closed', 'close_start', datediff);
583 PERFORM evergreen.concerto_date_carry_tbl_col('actor.org_unit_closed', 'close_end', datediff);
586 PERFORM evergreen.concerto_date_carry_tbl_col('actor.passwd', 'create_date', datediff);
587 PERFORM evergreen.concerto_date_carry_tbl_col('actor.passwd', 'edit_date', datediff);
590 PERFORM evergreen.concerto_date_carry_tbl_col('actor.usr', 'create_date', datediff);
591 PERFORM evergreen.concerto_date_carry_tbl_col('actor.usr', 'expire_date', datediff);
593 -- actor.usr_activity
594 PERFORM evergreen.concerto_date_carry_tbl_col('actor.usr_activity', 'event_time', datediff);
596 -- actor.usr_standing_penalty
597 PERFORM evergreen.concerto_date_carry_tbl_col('actor.usr_standing_penalty', 'set_date', datediff);
598 PERFORM evergreen.concerto_date_carry_tbl_col('actor.usr_standing_penalty', 'stop_date', datediff);
601 PERFORM evergreen.concerto_date_carry_tbl_col('asset.call_number', 'create_date', datediff);
604 PERFORM evergreen.concerto_date_carry_tbl_col('asset.copy', 'create_date', datediff);
605 PERFORM evergreen.concerto_date_carry_tbl_col('asset.copy', 'edit_date', datediff);
606 PERFORM evergreen.concerto_date_carry_tbl_col('asset.copy', 'status_changed_time', datediff);
607 PERFORM evergreen.concerto_date_carry_tbl_col('asset.copy', 'active_date', datediff);
610 PERFORM evergreen.concerto_date_carry_tbl_col('asset.copy_note', 'create_date', datediff);
612 -- authority.record_entry
613 PERFORM evergreen.concerto_date_carry_tbl_col('authority.record_entry', 'create_date', datediff);
614 PERFORM evergreen.concerto_date_carry_tbl_col('authority.record_entry', 'edit_date', datediff);
616 -- biblio.record_entry
617 PERFORM evergreen.concerto_date_carry_tbl_col('biblio.record_entry', 'create_date', datediff);
618 PERFORM evergreen.concerto_date_carry_tbl_col('biblio.record_entry', 'edit_date', datediff);
619 PERFORM evergreen.concerto_date_carry_tbl_col('biblio.record_entry', 'merge_date', datediff);
621 -- booking.reservation
622 PERFORM evergreen.concerto_date_carry_tbl_col('booking.reservation', 'request_time', datediff);
623 PERFORM evergreen.concerto_date_carry_tbl_col('booking.reservation', 'start_time', datediff);
624 PERFORM evergreen.concerto_date_carry_tbl_col('booking.reservation', 'end_time', datediff);
625 PERFORM evergreen.concerto_date_carry_tbl_col('booking.reservation', 'capture_time', datediff);
626 PERFORM evergreen.concerto_date_carry_tbl_col('booking.reservation', 'cancel_time', datediff);
627 PERFORM evergreen.concerto_date_carry_tbl_col('booking.reservation', 'pickup_time', datediff);
628 PERFORM evergreen.concerto_date_carry_tbl_col('booking.reservation', 'return_time', datediff);
630 -- container.biblio_record_entry_bucket
631 PERFORM evergreen.concerto_date_carry_tbl_col('container.biblio_record_entry_bucket', 'create_time', datediff);
633 -- container.carousel
634 PERFORM evergreen.concerto_date_carry_tbl_col('container.carousel', 'create_time', datediff);
635 PERFORM evergreen.concerto_date_carry_tbl_col('container.carousel', 'edit_time', datediff);
636 PERFORM evergreen.concerto_date_carry_tbl_col('container.carousel', 'last_refresh_time', datediff);
638 -- container.user_bucket
639 PERFORM evergreen.concerto_date_carry_tbl_col('container.user_bucket', 'create_time', datediff);
641 -- container.user_bucket_item
642 PERFORM evergreen.concerto_date_carry_tbl_col('container.user_bucket', 'create_time', datediff);
644 -- money.billable_xact
645 PERFORM evergreen.concerto_date_carry_tbl_col('money.billable_xact', 'xact_start', datediff);
646 PERFORM evergreen.concerto_date_carry_tbl_col('money.billable_xact', 'xact_finish', datediff);
649 ALTER TABLE money.billing DISABLE TRIGGER maintain_billing_ts_tgr;
650 ALTER TABLE money.billing DISABLE TRIGGER mat_summary_upd_tgr;
651 ALTER TABLE money.billing DROP CONSTRAINT billing_btype_fkey;
653 PERFORM evergreen.concerto_date_carry_tbl_col('money.billing', 'billing_ts', datediff);
654 PERFORM evergreen.concerto_date_carry_tbl_col('money.billing', 'void_time', datediff);
655 PERFORM evergreen.concerto_date_carry_tbl_col('money.billing', 'create_date', datediff);
656 PERFORM evergreen.concerto_date_carry_tbl_col('money.billing', 'period_start', datediff);
657 PERFORM evergreen.concerto_date_carry_tbl_col('money.billing', 'period_end', datediff);
659 ALTER TABLE money.billing ENABLE TRIGGER maintain_billing_ts_tgr;
660 ALTER TABLE money.billing ENABLE TRIGGER mat_summary_upd_tgr;
661 ALTER TABLE money.billing ADD CONSTRAINT billing_btype_fkey FOREIGN KEY (btype)
662 REFERENCES config.billing_type (id) MATCH SIMPLE
663 ON UPDATE NO ACTION ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED;
666 PERFORM evergreen.concerto_date_carry_tbl_col('money.payment', 'payment_ts', datediff);
668 -- serial.caption_and_pattern
669 PERFORM evergreen.concerto_date_carry_tbl_col('serial.caption_and_pattern', 'create_date', datediff);
670 PERFORM evergreen.concerto_date_carry_tbl_col('serial.caption_and_pattern', 'start_date', datediff);
671 PERFORM evergreen.concerto_date_carry_tbl_col('serial.caption_and_pattern', 'end_date', datediff);
674 PERFORM evergreen.concerto_date_carry_tbl_col('serial.issuance', 'create_date', datediff);
675 PERFORM evergreen.concerto_date_carry_tbl_col('serial.issuance', 'edit_date', datediff);
676 PERFORM evergreen.concerto_date_carry_tbl_col('serial.issuance', 'date_published', datediff);
679 PERFORM evergreen.concerto_date_carry_tbl_col('serial.item', 'create_date', datediff);
680 PERFORM evergreen.concerto_date_carry_tbl_col('serial.item', 'edit_date', datediff);
681 PERFORM evergreen.concerto_date_carry_tbl_col('serial.item', 'date_expected', datediff);
682 PERFORM evergreen.concerto_date_carry_tbl_col('serial.item', 'date_received', datediff);
684 -- serial.subscription
685 PERFORM evergreen.concerto_date_carry_tbl_col('serial.subscription', 'start_date', datediff);
686 PERFORM evergreen.concerto_date_carry_tbl_col('serial.subscription', 'end_date', datediff);
688 -- vandelay.queued_record
689 PERFORM evergreen.concerto_date_carry_tbl_col('vandelay.queued_record', 'create_time', datediff);
690 PERFORM evergreen.concerto_date_carry_tbl_col('vandelay.queued_record', 'import_time', datediff);
692 -- vandelay.session_tracker
693 PERFORM evergreen.concerto_date_carry_tbl_col('vandelay.session_tracker', 'create_time', datediff);
694 PERFORM evergreen.concerto_date_carry_tbl_col('vandelay.session_tracker', 'update_time', datediff);
700 \set ON_ERROR_STOP off
702 CREATE TABLE IF NOT EXISTS evergreen.tvar_carry_date(tvar BOOLEAN);
703 INSERT INTO evergreen.tvar_carry_date(tvar)
704 VALUES(:skip_date_carry::boolean);
709 DECLARE skip BOOLEAN;
712 SELECT INTO skip tvar FROM evergreen.tvar_carry_date LIMIT 1;
713 IF NOT FOUND THEN skip = FALSE;
716 PERFORM evergreen.concerto_date_carry_all(skip);
724 DROP FUNCTION evergreen.concerto_date_carry_all(BOOLEAN);
725 DROP FUNCTION evergreen.concerto_date_carry_tbl_col(TEXT, TEXT, INTERVAL);
727 DROP TABLE IF EXISTS evergreen.tvar_carry_date;
736 my $tableColumnRef = shift;
738 my $funcHandler = $table;
740 $funcHandler =~ s/\./_/g;
741 $funcHandler .= '_handler';
743 # if some tables need handled special, make a sub with the table name AKA sub biblio_record_entry_handler
744 if ( functionExists($funcHandler) ) {
745 my $perlcode = '$rowCount = ' . $funcHandler . '($table, $tableColumnRef, $fHandle);';
749 $rowCount = standardHandler( $table, $tableColumnRef, $fHandle );
756 my %columns = %{$colRef};
760 while ( ( my $colname, my $colpos ) = each(%columns) ) {
761 push( @order, $colpos );
763 @order = sort { $a <=> $b } @order;
766 while ( ( my $colname, my $colpos ) = each(%columns) ) {
767 if ( $colpos == $thisPOS ) {
768 push( @ret, $colname );
779 my $funcname = shift;
780 return \&{$funcname} if defined &{$funcname};
787 $query .= "\nLIMIT $CHUNKSIZE OFFSET $offset\n";
788 print $query if $debug;
789 my @results = @{ dbhandler_query($query) };
793 sub standardHandler {
795 my $tableColumnRef = shift;
797 my $omitColumnsRef = shift;
798 my %omitColumn = %{$omitColumnsRef} if $omitColumnsRef;
799 my $query = "SELECT ";
800 my $sqlOutTop = "COPY $table (";
801 my $order = "ORDER BY ";
802 my @colOrder = @{ columnOrder($tableColumnRef) };
806 foreach (@colOrder) {
809 if ( $tableColumnOverride{$table} && $tableColumnOverride{$table}{'load'} ) {
810 foreach ( @{ $tableColumnOverride{$table}{'load'} } ) {
811 $include = 1 if ( $_ eq $currentCol );
815 # if the calling code wants to remove some columns, we skip them here
816 if ( ( !$omitColumnsRef ) || ( not defined $omitColumn{$currentCol} ) ) {
820 print "removing column: $table.$currentCol\n" if $debug;
824 $query .= "$currentCol, ";
825 $sqlOutTop .= "$currentCol, ";
826 $order .= "$colCount, ";
831 $query = substr( $query, 0, -2 ); # remove the trailing comma+space
832 $sqlOutTop = substr( $sqlOutTop, 0, -2 ); # remove the trailing comma+space
833 $order = substr( $order, 0, -2 ); # remove the trailing comma+space
834 $query .= " FROM ONLY $table \n$order";
836 # makes it possible to not have to quote strings, dates, etc.
837 $sqlOutTop .= ") FROM stdin;\n";
840 my @data = @{ getDataChunk( $query, $offset ) };
842 while ( $#data > 0 ) #skipping column def metadata at the end of the array
844 my $sqlOut = $sqlOutTop;
845 my @differencesFromSeed = @{ removeDuplicateStockData( \@data, $table, $firstTime ) };
848 foreach (@differencesFromSeed) {
852 foreach ( @{$row} ) {
857 # escape reserved tokens
858 $_ =~ s/\\/\\\\/g; # all backslashes need escaped
859 $_ =~ s/\n/\\n/g; # newline
860 $_ =~ s/\r/\\r/g; # carriage return
861 $_ =~ s/\t/\\t/g; # tab
862 $_ =~ s/\v/\\v/g; # vertical tab
863 $_ =~ s/\f/\\f/g; # form feed
867 $sqlOut = substr( $sqlOut, 0, -1 );
870 print $fHandle $sqlOut if $outCount > 0;
872 # postgres sql syntax for finish of stdin
873 print $fHandle "\\.\n\n" if $outCount > 0;
877 $offset += $CHUNKSIZE;
878 @data = @{ getDataChunk( $query, $offset ) };
880 print $fHandle injectSequenceUpdate($table);
889 sub biblio_record_entry_handler {
891 my $tableColumnRef = shift;
893 my %omitColumns = ( 'vis_attr_vector' => 1 );
894 return standardHandler( $table, $tableColumnRef, $fHandle, \%omitColumns );
897 sub actor_workstation_handler {
899 my $tableColumnRef = shift;
901 my $lines = standardHandler( $table, $tableColumnRef, $fHandle, undef );
902 print $fHandle <<'splitter';
904 -- a case where the deleted workstation had payments
905 INSERT INTO actor.workstation(id,name,owning_lib)
906 SELECT missingworkstation.id, aou.shortname||FLOOR(RANDOM() * 100 + 1)::INT, 1
910 DISTINCT mbdp.cash_drawer AS id
912 money.bnm_desk_payment mbdp
913 LEFT JOIN actor.workstation aw ON (mbdp.cash_drawer = aw.id)
917 JOIN actor.org_unit aou ON (aou.id=1);
919 -- anonymize workstation names
922 SET name=aou.shortname||'-'||aw.id
923 FROM actor.org_unit aou
925 aou.id=aw.owning_lib;
931 sub injectSequenceUpdate {
933 my @schema = split( /\./, $table );
934 my $schemaName = @schema[0];
936 my $query = <<'splitter';
939 SELECT t.oid::regclass AS table_name,
940 a.attname AS column_name,
941 s.relname AS sequence_name
943 JOIN pg_attribute AS a
944 ON a.attrelid = t.oid
946 ON d.refobjid = t.oid
947 AND d.refobjsubid = a.attnum
950 WHERE d.classid = 'pg_catalog.pg_class'::regclass
951 AND d.refclassid = 'pg_catalog.pg_class'::regclass
952 AND d.deptype IN ('i', 'a')
953 AND t.relkind IN ('r', 'P')
957 a.table_name = '!!tbname!!'::regclass
960 $query =~ s/!!tbname!!/$table/g;
961 my @results = @{ dbhandler_query($query) };
962 while ( $#results > 0 ) {
963 my $this = shift @results;
965 my $colname = @row[1];
966 my $seqname = @row[2];
967 $ret .= "\\echo sequence update column: !!colname!!\n";
968 $ret .= "SELECT SETVAL('!!seqname!!', (SELECT MAX(!!colname!!) FROM !!tbname!!));\n";
969 $ret =~ s/!!tbname!!/$table/g;
970 $ret =~ s/!!colname!!/$colname/g;
971 $ret =~ s/!!seqname!!/$schemaName.$seqname/g;
979 sub removeDuplicateStockData {
980 my $resultsRef = shift;
982 my $firstTime = shift;
983 $seedTableRowCount = getTableRowCount( $table, 1 ) if ($firstTime);
984 $seedTableUsedRowCount = 0 if ($firstTime);
986 my @results = @{$resultsRef};
987 my $colRef = @results[$#results];
988 my %columns = %{$colRef};
994 last if $resultsPOS == $#results;
997 # don't bother if we know we've already used up the seed data table
998 if ( $seedTableUsedRowCount < $seedTableRowCount ) {
999 my @row = @{$rowRef};
1002 my $select = "SELECT ";
1003 my $where = "WHERE 1=1";
1005 # special handler for some tables
1006 if ( $tableColumnOverride{$table} && $tableColumnOverride{$table}{'comp'} ) {
1007 foreach ( @{ $tableColumnOverride{$table}{'comp'} } ) {
1009 my $colpos = $columns{$colname};
1010 $select .= "$colname, ";
1011 if ( defined @row[$colpos] ) {
1013 $where .= " AND $colname = \$$pos";
1014 push( @vals, @row[$colpos] );
1017 $where .= " AND $colname is null";
1022 while ( ( my $colname, my $colpos ) = each(%columns) ) {
1024 # compare ID numbers when there is an ID column, otherwise, compare the rest of the columns
1025 if ( ( $colname ne 'id' && not defined $columns{'id'} ) || $colname eq 'id' ) {
1027 $select .= "$colname, ";
1029 # if it's null data, the SQL needs to be "is null", not "="
1030 if ( defined @row[$colpos] ) {
1032 $where .= " AND $colname = \$$pos";
1033 push( @vals, @row[$colpos] );
1036 $where .= " AND $colname is null";
1042 # remove the trailing comma+space
1043 $select = substr( $select, 0, -2 );
1044 $select .= "\nFROM ONLY $table\n$where\n";
1045 print $select if $debug;
1046 print Dumper( \@vals ) if $debug;
1047 my @res = @{ dbhandler_query( $select, \@vals, 1 ) };
1049 # seed data doesn't have a match, want this row for our new dataset
1051 push( @ret, $rowRef );
1056 # Each time we match seed data, we count. If the number of rows found equals the
1057 # number of total rows, we don't need to keep checking back on the seed database
1058 $seedTableUsedRowCount++;
1067 # exhausted the seed database table rows, this data can just blindly get added to the
1069 push( @ret, $rowRef );
1073 print "Removed $removeCount rows (exists in seed data)\n" if $removeCount;
1077 sub getSchemaTables {
1080 my $query = <<'splitter';
1081 SELECT schemaname||'.'||tablename
1082 FROM pg_catalog.pg_tables
1084 schemaname NOT IN('pg_catalog','information_schema')
1089 my @results = @{ dbhandler_query($query) };
1091 foreach (@results) {
1094 if ( getTableRowCount( @row[0] ) > 0 ) {
1095 push( @ret, lc @row[0] );
1096 push( @ret, getTableColumnNames( @row[0] ) );
1099 print "no rows in @row[0]\n" if $debug;
1102 last if $#results == $resultPos; # ignore the column header metadata
1110 sub getTableRowCount {
1114 my $query = "SELECT count(*) FROM ONLY $table";
1115 my @results = @{ dbhandler_query( $query, undef, $seed ) };
1116 foreach (@results) {
1120 last; # ignore column header metadata
1126 sub getTableColumnNames {
1129 my $query = "SELECT * FROM $table LIMIT 1";
1130 my @results = @{ dbhandler_query($query) };
1131 $ret = pop @results;
1135 sub checkTableForInclusion {
1137 my @schema = split( /\./, $table );
1138 foreach (@skipTables) {
1139 return 0 if ( lc $table eq lc $_ );
1140 if ( $_ =~ /\*$/ ) {
1141 my @thisSchema = split( /\./, $_ );
1142 return 0 if ( lc @schema[0] eq lc @thisSchema[0] );
1148 sub logfile_readFile {
1154 #print "Attempting open\n";
1156 my $worked = open( inputfile, '< ' . $file );
1158 print "******************Failed to read file*************\n";
1160 binmode( inputfile, ":utf8" );
1161 while ( !( open( inputfile, '< ' . $file ) ) && $trys < 100 ) {
1162 print "Trying again attempt $trys\n";
1166 if ( $trys < 100 ) {
1168 #print "Finally worked... now reading\n";
1169 @lines = <inputfile>;
1173 print "Attempted $trys times. COULD NOT READ FILE: $file\n";
1178 print "File does not exist: $file\n";
1184 my $restoreDBName = shift;
1185 my $currentEGDBVersionNum = shift;
1186 print "Upgrading DB: $restoreDBName, starting from stamp: $currentEGDBVersionNum\n" if $debug;
1187 my @upgradeScripts = @{ findAllUpgradeScriptsAfterSpecifiedVersion($currentEGDBVersionNum) };
1188 foreach (@upgradeScripts) {
1189 execPSQLCMD( "-t -v eg_version=\"'enhanced_concerto_script'\" -f '$_'", $restoreDBName );
1195 execPSQLCMD( "-c 'DROP DATABASE IF EXISTS $dbname'", "postgres" );
1198 sub getNextAvailableDBName {
1199 my $prefDBNamePrefix = shift || "concertoscript";
1200 my $query = "SELECT datname FROM pg_database WHERE datistemplate = false";
1201 my @results = @{ dbhandler_query($query) };
1203 # remove column defs, we don't need them here
1206 foreach (@results) {
1208 $names{ @row[0] } = 1;
1211 $loop++ while ( $names{ $prefDBNamePrefix . $loop } );
1212 return $prefDBNamePrefix . $loop;
1215 sub getLastEGDBVersionFromOutput {
1216 print "Reading previously generated data from $outputFolder/config.upgrade_log.sql\n" if $debug;
1217 my $file = $outputFolder . "/config.upgrade_log.sql";
1218 my @lines = @{ logfile_readFile($file) };
1220 my $tableLine = shift @lines;
1221 $tableLine =~ s/^[^\(]*?\(([^\)]*?)\).*$/$1/g;
1223 #Hunt down the column for "version"
1224 my @cols = split( /,/, $tableLine );
1225 my $versionColPOS = 0;
1230 $versionColPOS = $pos if ( lc $test eq 'version' );
1235 my @cols = split( /\t/, $_ );
1236 my $thisVersion = @cols[$versionColPOS];
1237 next if ( $thisVersion =~ /\D/ ); # ignore if there are non-numerics
1238 $highestNum = $thisVersion if ( $thisVersion + 0 > $highestNum );
1240 print "Result: $highestNum\n" if $debug;
1244 sub checkoutEGMatchingGitVersion {
1245 my $versionNum = shift;
1246 my $versionFile = findMatchingUpgradeFile($versionNum);
1247 print $versionFile . "\n";
1248 my $commit = findGitCommitForFile($versionFile);
1249 userInput("Found this Evergreen git commit: $commit");
1250 return gitCheckoutBranch( $commit, 1, 0 ); # stash anything pending, and don't apply after branch switch
1253 sub findMatchingUpgradeFile {
1254 my $upgradeNum = shift;
1255 my $egUpgradeFolder = "Open-ILS/src/sql/Pg/upgrade";
1257 my $folder = $egRepoPath . '/' . $egUpgradeFolder;
1258 opendir( my $dh, $folder ) || die "Can't open $folder: $!";
1259 while ( readdir $dh ) {
1260 if ( $_ =~ /^$upgradeNum.*?\.sql/ ) {
1261 $ret = $egUpgradeFolder . '/' . $_;
1268 sub findAllUpgradeScriptsAfterSpecifiedVersion {
1269 my $upgradeNum = shift;
1270 my $egUpgradeFolder = "Open-ILS/src/sql/Pg/upgrade";
1274 my $folder = $egRepoPath . '/' . $egUpgradeFolder;
1275 opendir( my $dh, $folder ) || die "Can't open $folder: $!";
1276 while ( readdir $dh ) {
1277 if ( $_ ne '.' && $_ ne '..' && $_ =~ /^\d+\..*/ ) {
1279 $thisStamp =~ s/^([^\.]*)\..*$/$1/g;
1280 if ( $thisStamp + 0 > $upgradeNum + 0 ) {
1281 push( @sortme, $thisStamp );
1282 $map{$thisStamp} = $egRepoPath . '/' . $egUpgradeFolder . '/' . $_;
1287 @sortme = sort { $a <=> $b } @sortme;
1288 push( @ret, $map{$_} ) foreach (@sortme);
1292 sub findGitCommitForFile {
1295 my $exec = "cd '$egRepoPath' && git log $file";
1297 my $return = execSystemCMDWithReturn($exec);
1298 my @retLines = split( /\n/, $return );
1299 foreach (@retLines) {
1300 if ( $_ =~ /^\s*commit\s+[^\s]*$/ ) {
1302 $ret =~ s/^\s*commit\s+([^\s]*)$/$1/g;
1305 print "Found commit: $ret\n" if $debug;
1309 sub gitCheckoutBranch {
1310 print "Headed into gitCheckoutBranch()\n" if $debug;
1312 my $stash = shift || 0;
1313 my $restoreStash = shift || 0;
1315 # get the current branch so we can switch back
1316 my $exec = "cd '$egRepoPath' && git rev-parse --abbrev-ref HEAD";
1317 my $ret = execSystemCMDWithReturn($exec);
1318 $exec = "cd '$egRepoPath'";
1319 $exec .= " && git stash" if $stash;
1320 $exec .= " && git checkout $branch";
1321 $exec .= " && git stash apply" if $restoreStash;
1322 userInput("Executing: '$exec'");
1323 execSystemCMD( $exec, 1 );
1324 userInput("Done Executing: '$exec'");
1325 print "Done with gitCheckoutBranch()\n" if $debug;
1329 sub populateDBFromCurrentGitBranch {
1331 my $loadConcerto = shift || 0;
1332 my $eg_db_config_stock = "Open-ILS/src/support-scripts/eg_db_config.in";
1333 my $eg_db_config_temp = "Open-ILS/src/support-scripts/eg_db_config";
1334 my $eg_config_stock = "Open-ILS/src/extras/eg_config.in";
1335 my $eg_config_temp = "Open-ILS/src/extras/eg_config";
1336 fix_eg_config( $egRepoPath . "/$eg_db_config_stock", $egRepoPath . "/$eg_db_config_temp" );
1337 fix_eg_config( $egRepoPath . "/$eg_config_stock", $egRepoPath . "/$eg_config_temp" );
1338 my $exec = "cd '$egRepoPath' && perl '$eg_db_config_temp'";
1339 $exec .= " --create-database --create-schema";
1340 $exec .= " --user " . $dbconf{"dbuser"};
1341 $exec .= " --password " . $dbconf{"dbpass"};
1342 $exec .= " --hostname " . $dbconf{"dbhost"};
1343 $exec .= " --port " . $dbconf{"port"};
1344 $exec .= " --database $db";
1345 execSystemCMD($exec);
1346 loadThisDataset( $db, 1 ) if $loadConcerto;
1349 sub loadThisDataset {
1351 my $skipDateCarry = shift || 0;
1352 $skipDateCarry = $skipDateCarry ? "-v skip_date_carry='1'" : '';
1354 chdir($outputFolder);
1355 print "LOADING DATA\nThis can take a few minutes...\n";
1356 execPSQLCMD( "$skipDateCarry -f load_all.sql", $db );
1362 my $outputFile = shift;
1364 unlink $outputFile if -e $outputFile;
1366 open( $outHandle, '>> ' . $outputFile );
1367 binmode( $outHandle, ":utf8" );
1369 my @lines = @{ logfile_readFile($inFile) };
1371 '\@prefix\@' => '/openils',
1372 '\@datarootdir\@' => '${prefix}/share',
1373 '\@BUILDILSCORE_TRUE\@' => '',
1374 '\@BUILDILSWEB_TRUE\@' => '',
1375 '\@BUILDILSREPORTER_TRUE\@' => '',
1376 '\@BUILDILSCLIENT_TRUE\@' => '',
1377 '\@PACKAGE_STRING\@' => '',
1378 '\@bindir\@' => '${exec_prefix}/bin',
1379 '\@libdir\@' => '${exec_prefix}/lib',
1380 '\@TMP\@' => '/tmp',
1381 '\@includedir\@' => '${prefix}/include',
1383 '\@sysconfdir\@' => '/openils/conf',
1384 '\@LIBXML2_HEADERS\@' => '',
1385 '\@APR_HEADERS\@' => '',
1386 '\@APACHE2_HEADERS\@' => '',
1387 '\@localstatedir\@' => '',
1394 # this file has some placeholders. We're not going to make use of
1395 # this feature in the script, but it won't run unless those are populated
1396 while ( ( my $key, my $value ) = each(%replaceMap) ) {
1397 $line =~ s/$key/$value/g;
1399 print $outHandle $line;
1401 chmod( 0755, $outHandle );
1407 my $ignoreErrors = shift;
1408 print "executing $cmd\n" if $debug;
1410 if ( !$ignoreErrors && ( $? == -1 ) ) {
1411 die "system '$cmd' failed: $?";
1413 print "Done executing $cmd\n" if $debug;
1416 sub execSystemCMDWithReturn {
1418 my $dont_trim = shift;
1420 print "executing $cmd\n" if $debug;
1421 open( DATA, $cmd . '|' );
1423 while ( $read = <DATA> ) {
1427 return 0 unless $ret;
1428 $ret = substr( $ret, 0, -1 ) unless $dont_trim; #remove the last character of output.
1429 print "Done executing $cmd\n" if $debug;
1436 $ENV{'PGUSER'} = $dbconf{"dbuser"};
1437 $ENV{'PGPASSWORD'} = $dbconf{"dbpass"};
1438 $ENV{'PGPORT'} = $dbconf{"port"};
1439 $ENV{'PGHOST'} = $dbconf{"dbhost"};
1440 $ENV{'PGDATABASE'} = $db;
1441 my $pcmd = "psql $cmd"; #2>&1";
1442 print "Running:\n$pcmd\n";
1446 sub dbhandler_setupConnection {
1454 undef $dbHandlerSeed;
1455 our $dbHandlerSeed = DBI->connect(
1456 "DBI:Pg:dbname=$dbname;host=$host;port=$port",
1460 post_connect_sql => "SET CLIENT_ENCODING TO 'UTF8'",
1461 pg_utf8_strings => 1
1467 our $dbHandler = DBI->connect(
1468 "DBI:Pg:dbname=$dbname;host=$host;port=$port",
1472 post_connect_sql => "SET CLIENT_ENCODING TO 'UTF8'",
1473 pg_utf8_strings => 1
1479 sub dbhandler_query {
1480 my $querystring = shift;
1481 my $valuesRef = shift;
1483 my @values = $valuesRef ? @{$valuesRef} : ();
1487 $query = $dbHandler->prepare($querystring) if ( !$seed );
1488 $query = $dbHandlerSeed->prepare($querystring) if ($seed);
1491 $query->bind_param( $i, $_ );
1495 my @columnNames = @{ $query->{NAME} };
1498 foreach (@columnNames) {
1504 while ( my $row = $query->fetchrow_arrayref() ) {
1506 foreach ( @{$row} ) {
1508 if ( ref $thisCol eq 'ARRAY' ) # handle [] datatypes
1510 my $t = join( ',', @{$thisCol} );
1511 if ( isStringArray($thisCol) == 1 ) {
1512 $t = join( "','", @{$thisCol} );
1514 push( @pushData, "{$t}" );
1518 push( @pushData, $thisCol );
1522 push( @ret, \@pushData );
1524 undef($querystring);
1525 push( @ret, \%colPos );
1531 my $arrayRef = shift;
1532 my @array = @{$arrayRef};
1534 if ( $_ =~ m/[^\-^0-9^\.]/g ) {
1543 'dbuser' => $dbuser,
1544 'dbpass' => $dbpass,
1546 'dbhost' => $dbhost,
1547 'db' => $databaseName,
1549 dbhandler_setupConnection( $dbconf{"db"}, $dbconf{"dbhost"}, $dbconf{"dbuser"}, $dbconf{"dbpass"},
1552 dbhandler_setupConnection( $seedDBName, $dbconf{"dbhost"}, $dbconf{"dbuser"}, $dbconf{"dbpass"}, $dbconf{"port"},
1557 print "Checking command line arguments...\n" if ($debug);
1560 print "Please provide the postgres database hostname/IP via --db-host\n";
1565 print "Please provide the postgres database username via --db-user\n";
1570 print "Please provide the postgres database password via --db-pass\n";
1575 print "Please provide the postgres database port via --db-port\n";
1579 if ( !$databaseName && ( !$doUpgrade || !$doTestRestore ) ) {
1580 print "Please provide the postgres database name via --db-name\n";
1584 if ( $outputFolder eq '' ) {
1585 print "Output folder not provided. Please pass in a command line path argument with --output-folder\n";
1588 if ( !$egRepoPath ) {
1589 print "You didn't include a path to the Evergreen repository --evergreen-repo\n";
1592 if ( !-e $egRepoPath ) {
1593 print "The path to the Evergreen repository --evergreen-repo does not exist\n";
1596 if ( !-e ( $egRepoPath . '/.git' ) ) {
1597 print "The path to the Evergreen repository is not a git repository\n";
1600 if ( $doUpgrade && ( !-e ( $outputFolder . '/config.upgrade_log.sql' ) ) ) {
1602 "You've spcified the upgrade option but the output folder doesn't contain a previously generated dataset. "
1603 . "I need to know what version of Evergreen this dataset was created from. I use 'config.upgrade_log.sql' to figure that out\n";
1606 if ( !$seedDBName && ( !-e ( $outputFolder . '/config.upgrade_log.sql' ) ) ) {
1608 "Please provide the name of the Evergreen seed database and/or an output folder that contains a previously generated dataset. "
1609 . "Please pass in a command line path argument with --seed-db-name\n";
1614 "You've chosen to perform an upgrade. FYI, the output folder SQL files will get overwritten with the upgraded version\n"
1618 # Trim any trailing / on path
1619 $outputFolder =~ s/\/$//g;
1620 $egRepoPath =~ s/\/$//g;
1626 if ( !$nonInteractive ) {
1627 print $prompt. "\n";
1628 print "Press Enter to continue or CTRL+C to stop now\n";