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',
56 'asset.copy_vis_attr_cache',
57 'authority.rec_descriptor',
58 'authority.simple_heading',
60 'authority.authority_linking',
61 'actor.org_unit_proximity',
62 'config.org_unit_setting_type_log',
63 'config.xml_transform',
64 'money.materialized_billable_xact_summary',
65 'serial.materialized_holding_code',
66 'vandelay.queued_bib_record_attr',
67 'config.print_template',
68 'config.workstation_setting_type',
69 'permission.grp_perm_map',
78 # call number data can include ##URI##,
79 # which biblio.record_entry triggers create, so, they go first
82 'asset.uri_call_number_map',
83 'biblio.record_entry',
84 'biblio.monograph_part',
88 'acq.lineitem_detail',
91 'asset.copy_location',
94 'authority.record_entry',
96 'money.billable_xact',
99 # needs to come before actor.workstation
100 'money.bnm_desk_payment',
103 # a mechanism to call out special tables where we don't want the default behavior
104 # here we can teach this software which columns need to be used for comparison
105 # to seed data, and which columns we need to output into our SQL load file
106 our %tableColumnOverride = (
108 # the id column isn't important,
109 # we need to ensure that we abide by the unique constraints
110 # comp is "comparison", these columns are what we use to dedupe from seed data
111 # load is "load these columns only", any other columns in the table will receive PG defaults
112 'actor.org_unit_setting' => {
113 'comp' => [ 'org_unit', 'name' ],
114 'load' => [ 'org_unit', 'name', 'value' ]
118 our $help = "Usage: ./make_concerto_from_evergreen_db.pl [OPTION]...
120 This program automates the process of making a new dataset for the Evergreen code repository.
121 We need connection details to a postgres database. The provided database user needs to have
122 permissions to create databases.
124 This code will accept a pre-created seed database or it can create it's own. A blank \"seed\"
125 Evergreen database is needed for comparison reasons. It uses this as a reference to determine
126 which data is seed data and which data is not.
129 --db-host postgresql server hostname/IP
130 --db-user Database Username to connect
131 --db-pass Database password to connect
132 --db-port Database port to connect
133 --evergreen-repo Folder path to the root of the Evergreen git repository
134 --output-folder Folder for our generated output
137 --perform-upgrade This routine will restore previously generated dataset and upgrade it to match the
138 provided Evergreen repository version.
139 --non-interactive Suppress user input prompts
140 --db-name Enhanced Concerto source postgres database name if you're generating a new dataset.
141 --test-restore This option will cause the software to create a new database and populate it with the previously generated dataset
142 --debug Set debug mode for more verbose output.
143 --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.
144 --seed-from-egdbid Supply the software the database ID number form which to create the seed database (used in conjunction with --create-seed-db)
145 --seed-db-name Evergreen database name for the seed database, created with --create-database --create-schema, NOT --load-all-sample
146 If you don't provide this, we will attempt to create one based upon a previously generated dataset located in
147 --output-folder. However, this will be required if you do not have a previously generated dataset.
150 Generate new dataset from existing DB:
151 ./make_concerto_from_evergreen_db.pl \
152 --db-host localhost \
153 --db-user evergreen \
154 --db-pass evergreen \
156 --db-name eg_enhanced \
157 --output-folder output \
158 --seed-db-name seed_from_1326 \
159 --evergreen-repo /home/opensrf/repos/Evergreen
161 If you don't have a seed database, you can omit it, and we'll make one based
162 upon the version we find in the file <output_folder>/config.upgrade_log.sql
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 --evergreen-repo /home/opensrf/repos/Evergreen
172 Or, you can have this software make a seed DB, and that's all it will do.
173 The version of Evergreen it will use will be found in <output_folder>/config.upgrade_log.sql
174 ./make_concerto_from_evergreen_db.pl \
175 --db-host localhost \
176 --db-user evergreen \
177 --db-pass evergreen \
179 --output-folder output \
180 --evergreen-repo /home/opensrf/repos/Evergreen \
183 Or, you can have this software make a seed DB based on your specified version of Evergreen
184 ./make_concerto_from_evergreen_db.pl \
185 --db-host localhost \
186 --db-user evergreen \
187 --db-pass evergreen \
189 --output-folder output \
190 --evergreen-repo /home/opensrf/repos/Evergreen \
192 --seed-from-egdbid 1350
194 Upgrade a previously-created dataset. Use this when cutting new releases of Evergreen and you want to include
195 the enhanced dataset to match. It will use the current git branch found in the provided path to the EG repo.
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 \
205 Test the existing dataset. Create a new database and restore the dataset.
206 The software will first create a database that matches the version of Evergreen in the
207 dataset output folder, then restore the dataset into the newly created database.
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 \
220 "db-host=s" => \$dbhost,
221 "db-name=s" => \$databaseName,
222 "db-user=s" => \$dbuser,
223 "db-pass=s" => \$dbpass,
224 "db-port=s" => \$dbport,
225 "seed-db-name=s" => \$seedDBName,
226 "output-folder=s" => \$outputFolder,
228 "evergreen-repo=s" => \$egRepoPath,
229 "perform-upgrade" => \$doUpgrade,
230 "test-restore" => \$doTestRestore,
231 "non-interactive" => \$nonInteractive,
232 "create-seed-db" => \$doSeedOnly,
233 "seed-from-egdbid=s" => \$seedFrom,
240 createSeedDB() if $doSeedOnly;
242 start() if !$doUpgrade && !$doTestRestore;
244 upgrade() if $doUpgrade;
246 testRestore() if $doTestRestore;
250 # make the output folder if it doesn't exist
256 ) if ( !( -e $outputFolder ) );
258 my $tempDB = checkSeed();
260 # Gather a list of Evergreen Tables to process
261 my @evergreenTables = @{ getSchemaTables() };
263 "BEGIN;\n\n-- stop on error\n\\set ON_ERROR_STOP on\n\n"
264 . "-- Ignore constraints until we're done\nSET CONSTRAINTS ALL DEFERRED;\n\n";
266 while ( $#evergreenTables > -1 ) {
267 my $thisTable = shift @evergreenTables;
268 my $columnRef = shift @evergreenTables;
269 if ( checkTableForInclusion($thisTable) ) {
270 my $thisFile = $outputFolder . "/$thisTable.sql";
271 print "Processing $thisTable > $thisFile\n";
272 unlink $thisFile if -e $thisFile;
274 open( $thisFhandle, '>> ' . $thisFile );
275 binmode( $thisFhandle, ":utf8" );
276 my $lines = tableHandler( $thisTable, $columnRef, $thisFhandle );
278 unlink $thisFile if ( -e $thisFile && $lines == 0 );
280 # upgrade_log is written as part of the dataset, but not loaded.
281 # it's used purely to figure out what version of Evergreen we are on
282 # during this execution. So, later, when we run the upgrade procedure, we
283 # can figure out where we were when this was generated.
284 push( @loadTables, $thisTable )
285 if ( -e $thisFile && $lines > 0 && !( $thisFile =~ /config\.upgrade_log/ ) );
289 print "Skipping: $thisTable\n" if $debug;
292 $loadAll = loadTableOrderMaker( $loadAll, \@loadTables );
294 $loadAll .= "SELECT SETVAL('money.billable_xact_id_seq', (SELECT MAX(id) FROM money.billing));\n\n";
295 $loadAll .= "SELECT SETVAL('config.remote_account_id_seq', (SELECT MAX(id) FROM config.remote_account));\n\n";
296 $loadAll .= "SELECT SETVAL('money.payment_id_seq', (SELECT MAX(id) FROM money.payment));\n\n";
297 $loadAll .= "SELECT SETVAL('asset.copy_id_seq', (SELECT MAX(id) FROM asset.copy));\n\n";
298 $loadAll .= "SELECT SETVAL('vandelay.queue_id_seq', (SELECT MAX(id) FROM vandelay.queue));\n\n";
299 $loadAll .= "SELECT SETVAL('vandelay.queued_record_id_seq', (SELECT MAX(id) FROM vandelay.queued_record));\n\n";
301 $loadAll .= "COMMIT;\n";
302 print "Writing loader > $outputFolder/load_all.sql\n";
303 open( OUT, "> $outputFolder/load_all.sql" );
304 binmode( OUT, ":utf8" );
307 $dbHandler->disconnect;
308 $dbHandlerSeed->disconnect;
309 dropDB($tempDB) if $tempDB;
314 my $restoreDBName = getNextAvailableDBName();
315 print "Using database name: $restoreDBName\n" if $debug;
316 my $currentEGDBVersionNum = getLastEGDBVersionFromOutput();
317 userInput("Found this DB version from output: $currentEGDBVersionNum");
318 my $previousGitBranch = checkoutEGMatchingGitVersion($currentEGDBVersionNum);
319 populateDBFromCurrentGitBranch( $restoreDBName, 0 );
321 # now we have a temp database full of our concerto set
322 # created by the version of Evergreen that origainally made the dataset
323 # Now, swtich the repo back to the original branch that the user had
324 gitCheckoutBranch( $previousGitBranch, 0, 1 );
325 loadThisDataset($restoreDBName);
326 upgradeDB( $restoreDBName, $currentEGDBVersionNum );
327 $seedDBName = getNextAvailableDBName();
328 populateDBFromCurrentGitBranch( $seedDBName, 0 );
330 dbhandler_setupConnection( $restoreDBName, $dbconf{"dbhost"}, $dbconf{"dbuser"}, $dbconf{"dbpass"},
332 dbhandler_setupConnection( $seedDBName, $dbconf{"dbhost"}, $dbconf{"dbuser"}, $dbconf{"dbpass"}, $dbconf{"port"},
335 userInput( "Done! If you'd like, you can pause here and take a peek at the generated databases before "
336 . "I drop them:\nFull DB: $restoreDBName\nSeed: $seedDBName" );
337 $dbHandler->disconnect;
338 $dbHandlerSeed->disconnect;
339 dropDB($restoreDBName);
345 my $restoreDB = checkSeed(1);
346 loadThisDataset($restoreDB);
347 print "Created database: $restoreDB from provided output folder: $outputFolder\n";
352 my $currentEGDBVersionNum = $seedFrom ? $seedFrom : getLastEGDBVersionFromOutput();
353 my $restoreDBName = getNextAvailableDBName( "seed_db_$currentEGDBVersionNum" . "_" );
354 print "Using database name: $restoreDBName\n" if $debug;
355 my $previousGitBranch = checkoutEGMatchingGitVersion($currentEGDBVersionNum);
356 populateDBFromCurrentGitBranch( $restoreDBName, 0 );
357 gitCheckoutBranch( $previousGitBranch, 0, 1 );
358 print "Created a fresh seed DB from Evergreen Version: $currentEGDBVersionNum\n" . "DB name: $restoreDBName\n";
363 my $forceNewDB = shift || 0;
369 # a sanity check to make sure we can connect to the database and run a query
370 my @res = @{ dbhandler_query( "SELECT MAX(id) FROM biblio.record_entry", undef, 1 ) };
371 $valid = 1 if ( $#res > -1 );
374 # Seed database is missing, let's create one
375 if ( !$valid || $forceNewDB ) {
376 $seedDBName = getNextAvailableDBName();
377 $createdDB = $seedDBName;
378 populateDBFromCurrentGitBranch( $seedDBName, 0 );
379 dbhandler_setupConnection( $seedDBName, $dbconf{"dbhost"}, $dbconf{"dbuser"}, $dbconf{"dbpass"},
380 $dbconf{"port"}, 1 );
385 sub loadTableOrderMaker {
386 my $loadString = shift;
387 my $includedTablesRef = shift;
388 my @includedTables = @{$includedTablesRef};
391 # Loop through the pre-defined order, and check those off
392 foreach (@loadOrder) {
395 foreach (@includedTables) {
396 if ( $includedTables[$pos] eq $otable ) {
397 $loadString .= makeLoaderLine($_);
407 foreach (@includedTables) {
408 if ( not defined $used{$pos} ) {
409 $loadString .= makeLoaderLine($_);
421 my $ret = "\\echo loading $table\n";
422 $ret .= "\\i $table.sql\n\n";
428 my $tableColumnRef = shift;
430 my $funcHandler = $table;
432 $funcHandler =~ s/\./_/g;
433 $funcHandler .= '_handler';
435 # if some tables need handled special, make a sub with the table name AKA sub biblio_record_entry_handler
436 if ( functionExists($funcHandler) ) {
437 my $perlcode = '$rowCount = ' . $funcHandler . '($table, $tableColumnRef, $fHandle);';
441 $rowCount = standardHandler( $table, $tableColumnRef, $fHandle );
448 my %columns = %{$colRef};
452 while ( ( my $colname, my $colpos ) = each(%columns) ) {
453 push( @order, $colpos );
455 @order = sort { $a <=> $b } @order;
458 while ( ( my $colname, my $colpos ) = each(%columns) ) {
459 if ( $colpos == $thisPOS ) {
460 push( @ret, $colname );
471 my $funcname = shift;
472 return \&{$funcname} if defined &{$funcname};
479 $query .= "\nLIMIT $CHUNKSIZE OFFSET $offset\n";
480 print $query if $debug;
481 my @results = @{ dbhandler_query($query) };
485 sub standardHandler {
487 my $tableColumnRef = shift;
489 my $omitColumnsRef = shift;
490 my %omitColumn = %{$omitColumnsRef} if $omitColumnsRef;
491 my $query = "SELECT ";
492 my $sqlOutTop = "COPY $table (";
493 my $order = "ORDER BY ";
494 my @colOrder = @{ columnOrder($tableColumnRef) };
498 foreach (@colOrder) {
501 if ( $tableColumnOverride{$table} ) {
502 foreach ( @{ $tableColumnOverride{$table}{'load'} } ) {
503 $include = 1 if ( $_ eq $currentCol );
507 # if the calling code wants to remove some columns, we skip them here
508 if ( ( !$omitColumnsRef ) || ( not defined $omitColumn{$currentCol} ) ) {
512 print "removing column: $table.$currentCol\n" if $debug;
516 $query .= "$currentCol, ";
517 $sqlOutTop .= "$currentCol, ";
518 $order .= "$colCount, ";
523 $query = substr( $query, 0, -2 ); # remove the trailing comma+space
524 $sqlOutTop = substr( $sqlOutTop, 0, -2 ); # remove the trailing comma+space
525 $order = substr( $order, 0, -2 ); # remove the trailing comma+space
526 $query .= " FROM ONLY $table \n$order";
528 # makes it possible to not have to quote strings, dates, etc.
529 $sqlOutTop .= ") FROM stdin;\n";
532 my @data = @{ getDataChunk( $query, $offset ) };
534 while ( $#data > 0 ) #skipping column def metadata at the end of the array
536 my $sqlOut = $sqlOutTop;
537 my @differencesFromSeed = @{ removeDuplicateStockData( \@data, $table, $firstTime ) };
540 foreach (@differencesFromSeed) {
544 foreach ( @{$row} ) {
549 # escape reserved tokens
550 $_ =~ s/\\/\\\\/g; # all backslashes need escaped
551 $_ =~ s/\n/\\n/g; # newline
552 $_ =~ s/\r/\\r/g; # carriage return
553 $_ =~ s/\t/\\t/g; # tab
554 $_ =~ s/\v/\\v/g; # vertical tab
555 $_ =~ s/\f/\\f/g; # form feed
559 $sqlOut = substr( $sqlOut, 0, -1 );
562 print $fHandle $sqlOut if $outCount > 0;
564 # postgres sql syntax for finish of stdin
565 print $fHandle "\\.\n\n" if $outCount > 0;
569 $offset += $CHUNKSIZE;
570 @data = @{ getDataChunk( $query, $offset ) };
572 print $fHandle injectSequenceUpdate($table);
581 sub biblio_record_entry_handler {
583 my $tableColumnRef = shift;
585 my %omitColumns = ( 'vis_attr_vector' => 1 );
586 return standardHandler( $table, $tableColumnRef, $fHandle, \%omitColumns );
589 sub actor_workstation_handler {
591 my $tableColumnRef = shift;
593 my $lines = standardHandler( $table, $tableColumnRef, $fHandle, undef );
594 print $fHandle <<'splitter';
596 -- a case where the deleted workstation had payments
597 INSERT INTO actor.workstation(id,name,owning_lib)
598 SELECT missingworkstation.id, aou.shortname||FLOOR(RANDOM() * 100 + 1)::INT, 1
602 DISTINCT mbdp.cash_drawer AS id
604 money.bnm_desk_payment mbdp
605 LEFT JOIN actor.workstation aw ON (mbdp.cash_drawer = aw.id)
609 JOIN actor.org_unit aou ON (aou.id=1);
611 -- anonymize workstation names
614 SET name=aou.shortname||'-'||aw.id
615 FROM actor.org_unit aou
617 aou.id=aw.owning_lib;
623 sub injectSequenceUpdate {
625 my @schema = split( /\./, $table );
626 my $schemaName = @schema[0];
628 my $query = <<'splitter';
631 SELECT t.oid::regclass AS table_name,
632 a.attname AS column_name,
633 s.relname AS sequence_name
635 JOIN pg_attribute AS a
636 ON a.attrelid = t.oid
638 ON d.refobjid = t.oid
639 AND d.refobjsubid = a.attnum
642 WHERE d.classid = 'pg_catalog.pg_class'::regclass
643 AND d.refclassid = 'pg_catalog.pg_class'::regclass
644 AND d.deptype IN ('i', 'a')
645 AND t.relkind IN ('r', 'P')
649 a.table_name = '!!tbname!!'::regclass
652 $query =~ s/!!tbname!!/$table/g;
653 my @results = @{ dbhandler_query($query) };
654 while ( $#results > 0 ) {
655 my $this = shift @results;
657 my $colname = @row[1];
658 my $seqname = @row[2];
659 $ret .= "\\echo sequence update column: !!colname!!\n";
660 $ret .= "SELECT SETVAL('!!seqname!!', (SELECT MAX(!!colname!!) FROM !!tbname!!));\n";
661 $ret =~ s/!!tbname!!/$table/g;
662 $ret =~ s/!!colname!!/$colname/g;
663 $ret =~ s/!!seqname!!/$schemaName.$seqname/g;
671 sub removeDuplicateStockData {
672 my $resultsRef = shift;
674 my $firstTime = shift;
675 $seedTableRowCount = getTableRowCount( $table, 1 ) if ($firstTime);
676 $seedTableUsedRowCount = 0 if ($firstTime);
678 my @results = @{$resultsRef};
679 my $colRef = @results[$#results];
680 my %columns = %{$colRef};
686 last if $resultsPOS == $#results;
689 # don't bother if we know we've already used up the seed data table
690 if ( $seedTableUsedRowCount < $seedTableRowCount ) {
691 my @row = @{$rowRef};
694 my $select = "SELECT ";
695 my $where = "WHERE 1=1";
697 # special handler for some tables
698 if ( $tableColumnOverride{$table} ) {
699 foreach ( @{ $tableColumnOverride{$table}{'comp'} } ) {
701 my $colpos = $columns{$colname};
702 $select .= "$colname, ";
703 if ( defined @row[$colpos] ) {
705 $where .= " AND $colname = \$$pos";
706 push( @vals, @row[$colpos] );
709 $where .= " AND $colname is null";
714 while ( ( my $colname, my $colpos ) = each(%columns) ) {
716 # compare ID numbers when there is an ID column, otherwise, compare the rest of the columns
717 if ( ( $colname ne 'id' && not defined $columns{'id'} ) || $colname eq 'id' ) {
719 $select .= "$colname, ";
721 # if it's null data, the SQL needs to be "is null", not "="
722 if ( defined @row[$colpos] ) {
724 $where .= " AND $colname = \$$pos";
725 push( @vals, @row[$colpos] );
728 $where .= " AND $colname is null";
734 # remove the trailing comma+space
735 $select = substr( $select, 0, -2 );
736 $select .= "\nFROM ONLY $table\n$where\n";
737 print $select if $debug;
738 print Dumper( \@vals ) if $debug;
739 my @res = @{ dbhandler_query( $select, \@vals, 1 ) };
741 # seed data doesn't have a match, want this row for our new dataset
743 push( @ret, $rowRef );
748 # Each time we match seed data, we count. If the number of rows found equals the
749 # number of total rows, we don't need to keep checking back on the seed database
750 $seedTableUsedRowCount++;
759 # exhausted the seed database table rows, this data can just blindly get added to the
761 push( @ret, $rowRef );
765 print "Removed $removeCount rows (exists in seed data)\n" if $removeCount;
769 sub getSchemaTables {
772 my $query = <<'splitter';
773 SELECT schemaname||'.'||tablename
774 FROM pg_catalog.pg_tables
776 schemaname NOT IN('pg_catalog','information_schema')
781 my @results = @{ dbhandler_query($query) };
786 if ( getTableRowCount( @row[0] ) > 0 ) {
787 push( @ret, lc @row[0] );
788 push( @ret, getTableColumnNames( @row[0] ) );
791 print "no rows in @row[0]\n" if $debug;
794 last if $#results == $resultPos; # ignore the column header metadata
802 sub getTableRowCount {
806 my $query = "SELECT count(*) FROM ONLY $table";
807 my @results = @{ dbhandler_query( $query, undef, $seed ) };
812 last; # ignore column header metadata
818 sub getTableColumnNames {
821 my $query = "SELECT * FROM $table LIMIT 1";
822 my @results = @{ dbhandler_query($query) };
827 sub checkTableForInclusion {
829 my @schema = split( /\./, $table );
830 foreach (@skipTables) {
831 return 0 if ( lc $table eq lc $_ );
833 my @thisSchema = split( /\./, $_ );
834 return 0 if ( lc @schema[0] eq lc @thisSchema[0] );
840 sub logfile_readFile {
846 #print "Attempting open\n";
848 my $worked = open( inputfile, '< ' . $file );
850 print "******************Failed to read file*************\n";
852 binmode( inputfile, ":utf8" );
853 while ( !( open( inputfile, '< ' . $file ) ) && $trys < 100 ) {
854 print "Trying again attempt $trys\n";
860 #print "Finally worked... now reading\n";
861 @lines = <inputfile>;
865 print "Attempted $trys times. COULD NOT READ FILE: $file\n";
870 print "File does not exist: $file\n";
876 my $restoreDBName = shift;
877 my $currentEGDBVersionNum = shift;
878 print "Upgrading DB: $restoreDBName, starting from stamp: $currentEGDBVersionNum\n" if $debug;
879 my @upgradeScripts = @{ findAllUpgradeScriptsAfterSpecifiedVersion($currentEGDBVersionNum) };
880 foreach (@upgradeScripts) {
881 execPSQLCMD( "-t -v eg_version=\"'enhanced_concerto_script'\" -f '$_'", $restoreDBName );
887 execPSQLCMD( "-c 'DROP DATABASE IF EXISTS $dbname'", "postgres" );
890 sub getNextAvailableDBName {
891 my $prefDBNamePrefix = shift || "concertoscript";
892 my $query = "SELECT datname FROM pg_database WHERE datistemplate = false";
893 my @results = @{ dbhandler_query($query) };
895 # remove column defs, we don't need them here
900 $names{ @row[0] } = 1;
903 $loop++ while ( $names{ $prefDBNamePrefix . $loop } );
904 return $prefDBNamePrefix . $loop;
907 sub getLastEGDBVersionFromOutput {
908 print "Reading previously generated data from $outputFolder/config.upgrade_log.sql\n" if $debug;
909 my $file = $outputFolder . "/config.upgrade_log.sql";
910 my @lines = @{ logfile_readFile($file) };
912 my $tableLine = shift @lines;
913 $tableLine =~ s/^[^\(]*?\(([^\)]*?)\).*$/$1/g;
915 #Hunt down the column for "version"
916 my @cols = split( /,/, $tableLine );
917 my $versionColPOS = 0;
922 $versionColPOS = $pos if ( lc $test eq 'version' );
927 my @cols = split( /\t/, $_ );
928 my $thisVersion = @cols[$versionColPOS];
929 next if ( $thisVersion =~ /\D/ ); # ignore if there are non-numerics
930 $highestNum = $thisVersion if ( $thisVersion + 0 > $highestNum );
932 print "Result: $highestNum\n" if $debug;
936 sub checkoutEGMatchingGitVersion {
937 my $versionNum = shift;
938 my $versionFile = findMatchingUpgradeFile($versionNum);
939 print $versionFile . "\n";
940 my $commit = findGitCommitForFile($versionFile);
941 userInput("Found this Evergreen git commit: $commit");
942 return gitCheckoutBranch( $commit, 1, 0 ); # stash anything pending, and don't apply after branch switch
945 sub findMatchingUpgradeFile {
946 my $upgradeNum = shift;
947 my $egUpgradeFolder = "Open-ILS/src/sql/Pg/upgrade";
949 my $folder = $egRepoPath . '/' . $egUpgradeFolder;
950 opendir( my $dh, $folder ) || die "Can't open $folder: $!";
951 while ( readdir $dh ) {
952 if ( $_ =~ /^$upgradeNum.*?\.sql/ ) {
953 $ret = $egUpgradeFolder . '/' . $_;
960 sub findAllUpgradeScriptsAfterSpecifiedVersion {
961 my $upgradeNum = shift;
962 my $egUpgradeFolder = "Open-ILS/src/sql/Pg/upgrade";
966 my $folder = $egRepoPath . '/' . $egUpgradeFolder;
967 opendir( my $dh, $folder ) || die "Can't open $folder: $!";
968 while ( readdir $dh ) {
969 if ( $_ ne '.' && $_ ne '..' && $_ =~ /^\d+\..*/ ) {
971 $thisStamp =~ s/^([^\.]*)\..*$/$1/g;
972 if ( $thisStamp + 0 > $upgradeNum + 0 ) {
973 push( @sortme, $thisStamp );
974 $map{$thisStamp} = $egRepoPath . '/' . $egUpgradeFolder . '/' . $_;
979 @sortme = sort { $a <=> $b } @sortme;
980 push( @ret, $map{$_} ) foreach (@sortme);
984 sub findGitCommitForFile {
987 my $exec = "cd '$egRepoPath' && git log $file";
989 my $return = execSystemCMDWithReturn($exec);
990 my @retLines = split( /\n/, $return );
991 foreach (@retLines) {
992 if ( $_ =~ /^\s*commit\s+[^\s]*$/ ) {
994 $ret =~ s/^\s*commit\s+([^\s]*)$/$1/g;
997 print "Found commit: $ret\n" if $debug;
1001 sub gitCheckoutBranch {
1002 print "Headed into gitCheckoutBranch()\n" if $debug;
1004 my $stash = shift || 0;
1005 my $restoreStash = shift || 0;
1007 # get the current branch so we can switch back
1008 my $exec = "cd '$egRepoPath' && git rev-parse --abbrev-ref HEAD";
1009 my $ret = execSystemCMDWithReturn($exec);
1010 $exec = "cd '$egRepoPath'";
1011 $exec .= " && git stash" if $stash;
1012 $exec .= " && git checkout $branch";
1013 $exec .= " && git stash apply" if $restoreStash;
1014 userInput("Executing: '$exec'");
1015 execSystemCMD( $exec, 1 );
1016 userInput("Done Executing: '$exec'");
1017 print "Done with gitCheckoutBranch()\n" if $debug;
1021 sub populateDBFromCurrentGitBranch {
1023 my $loadConcerto = shift || 0;
1024 my $eg_db_config_stock = "Open-ILS/src/support-scripts/eg_db_config.in";
1025 my $eg_db_config_temp = "Open-ILS/src/support-scripts/eg_db_config";
1026 my $eg_config_stock = "Open-ILS/src/extras/eg_config.in";
1027 my $eg_config_temp = "Open-ILS/src/extras/eg_config";
1028 fix_eg_config( $egRepoPath . "/$eg_db_config_stock", $egRepoPath . "/$eg_db_config_temp" );
1029 fix_eg_config( $egRepoPath . "/$eg_config_stock", $egRepoPath . "/$eg_config_temp" );
1030 my $exec = "cd '$egRepoPath' && perl '$eg_db_config_temp'";
1031 $exec .= " --create-database --create-schema";
1032 $exec .= " --user " . $dbconf{"dbuser"};
1033 $exec .= " --password " . $dbconf{"dbpass"};
1034 $exec .= " --hostname " . $dbconf{"dbhost"};
1035 $exec .= " --port " . $dbconf{"port"};
1036 $exec .= " --database $db";
1037 execSystemCMD($exec);
1038 loadThisDataset($db) if $loadConcerto;
1041 sub loadThisDataset {
1043 chdir($outputFolder);
1044 print "LOADING DATA\nThis can take a few minutes...\n";
1045 execPSQLCMD( "-f load_all.sql", $db );
1051 my $outputFile = shift;
1053 unlink $outputFile if -e $outputFile;
1055 open( $outHandle, '>> ' . $outputFile );
1056 binmode( $outHandle, ":utf8" );
1058 my @lines = @{ logfile_readFile($inFile) };
1060 '\@prefix\@' => '/openils',
1061 '\@datarootdir\@' => '${prefix}/share',
1062 '\@BUILDILSCORE_TRUE\@' => '',
1063 '\@BUILDILSWEB_TRUE\@' => '',
1064 '\@BUILDILSREPORTER_TRUE\@' => '',
1065 '\@BUILDILSCLIENT_TRUE\@' => '',
1066 '\@PACKAGE_STRING\@' => '',
1067 '\@bindir\@' => '${exec_prefix}/bin',
1068 '\@libdir\@' => '${exec_prefix}/lib',
1069 '\@TMP\@' => '/tmp',
1070 '\@includedir\@' => '${prefix}/include',
1072 '\@sysconfdir\@' => '/openils/conf',
1073 '\@LIBXML2_HEADERS\@' => '',
1074 '\@APR_HEADERS\@' => '',
1075 '\@APACHE2_HEADERS\@' => '',
1076 '\@localstatedir\@' => '',
1083 # this file has some placeholders. We're not going to make use of
1084 # this feature in the script, but it won't run unless those are populated
1085 while ( ( my $key, my $value ) = each(%replaceMap) ) {
1086 $line =~ s/$key/$value/g;
1088 print $outHandle $line;
1090 chmod( 0755, $outHandle );
1096 my $ignoreErrors = shift;
1097 print "executing $cmd\n" if $debug;
1099 if ( !$ignoreErrors && ( $? == -1 ) ) {
1100 die "system '$cmd' failed: $?";
1102 print "Done executing $cmd\n" if $debug;
1105 sub execSystemCMDWithReturn {
1107 my $dont_trim = shift;
1109 print "executing $cmd\n" if $debug;
1110 open( DATA, $cmd . '|' );
1112 while ( $read = <DATA> ) {
1116 return 0 unless $ret;
1117 $ret = substr( $ret, 0, -1 ) unless $dont_trim; #remove the last character of output.
1118 print "Done executing $cmd\n" if $debug;
1125 $ENV{'PGUSER'} = $dbconf{"dbuser"};
1126 $ENV{'PGPASSWORD'} = $dbconf{"dbpass"};
1127 $ENV{'PGPORT'} = $dbconf{"port"};
1128 $ENV{'PGHOST'} = $dbconf{"dbhost"};
1129 $ENV{'PGDATABASE'} = $db;
1130 my $pcmd = "psql $cmd"; #2>&1";
1131 print "Running:\n$pcmd\n";
1135 sub dbhandler_setupConnection {
1143 undef $dbHandlerSeed;
1144 our $dbHandlerSeed = DBI->connect(
1145 "DBI:Pg:dbname=$dbname;host=$host;port=$port",
1149 post_connect_sql => "SET CLIENT_ENCODING TO 'UTF8'",
1150 pg_utf8_strings => 1
1156 our $dbHandler = DBI->connect(
1157 "DBI:Pg:dbname=$dbname;host=$host;port=$port",
1161 post_connect_sql => "SET CLIENT_ENCODING TO 'UTF8'",
1162 pg_utf8_strings => 1
1168 sub dbhandler_query {
1169 my $querystring = shift;
1170 my $valuesRef = shift;
1172 my @values = $valuesRef ? @{$valuesRef} : ();
1176 $query = $dbHandler->prepare($querystring) if ( !$seed );
1177 $query = $dbHandlerSeed->prepare($querystring) if ($seed);
1180 $query->bind_param( $i, $_ );
1184 my @columnNames = @{ $query->{NAME} };
1187 foreach (@columnNames) {
1193 while ( my $row = $query->fetchrow_arrayref() ) {
1195 foreach ( @{$row} ) {
1197 if ( ref $thisCol eq 'ARRAY' ) # handle [] datatypes
1199 my $t = join( ',', @{$thisCol} );
1200 if ( isStringArray($thisCol) == 1 ) {
1201 $t = join( "','", @{$thisCol} );
1203 push( @pushData, "{$t}" );
1207 push( @pushData, $thisCol );
1211 push( @ret, \@pushData );
1213 undef($querystring);
1214 push( @ret, \%colPos );
1220 my $arrayRef = shift;
1221 my @array = @{$arrayRef};
1223 if ( $_ =~ m/[^\-^0-9^\.]/g ) {
1232 'dbuser' => $dbuser,
1233 'dbpass' => $dbpass,
1235 'dbhost' => $dbhost,
1236 'db' => $databaseName,
1238 dbhandler_setupConnection( $dbconf{"db"}, $dbconf{"dbhost"}, $dbconf{"dbuser"}, $dbconf{"dbpass"},
1241 dbhandler_setupConnection( $seedDBName, $dbconf{"dbhost"}, $dbconf{"dbuser"}, $dbconf{"dbpass"}, $dbconf{"port"},
1246 print "Checking command line arguments...\n" if ($debug);
1249 print "Please provide the postgres database hostname/IP via --db-host\n";
1254 print "Please provide the postgres database username via --db-user\n";
1259 print "Please provide the postgres database password via --db-pass\n";
1264 print "Please provide the postgres database port via --db-port\n";
1268 if ( !$databaseName && ( !$doUpgrade || !$doTestRestore ) ) {
1269 print "Please provide the postgres database name via --db-name\n";
1273 if ( $outputFolder eq '' ) {
1274 print "Output folder not provided. Please pass in a command line path argument with --output-folder\n";
1277 if ( !$egRepoPath ) {
1278 print "You didn't include a path to the Evergreen repository --evergreen-repo\n";
1281 if ( !-e $egRepoPath ) {
1282 print "The path to the Evergreen repository --evergreen-repo does not exist\n";
1285 if ( !-e ( $egRepoPath . '/.git' ) ) {
1286 print "The path to the Evergreen repository is not a git repository\n";
1289 if ( $doUpgrade && ( !-e ( $outputFolder . '/config.upgrade_log.sql' ) ) ) {
1291 "You've spcified the upgrade option but the output folder doesn't contain a previously generated dataset. "
1292 . "I need to know what version of Evergreen this dataset was created from. I use 'config.upgrade_log.sql' to figure that out\n";
1295 if ( !$seedDBName && ( !-e ( $outputFolder . '/config.upgrade_log.sql' ) ) ) {
1297 "Please provide the name of the Evergreen seed database and/or an output folder that contains a previously generated dataset. "
1298 . "Please pass in a command line path argument with --seed-db-name\n";
1303 "You've chosen to perform an upgrade. FYI, the output folder SQL files will get overwritten with the upgraded version\n"
1307 # Trim any trailing / on path
1308 $outputFolder =~ s/\/$//g;
1309 $egRepoPath =~ s/\/$//g;
1315 if ( !$nonInteractive ) {
1316 print $prompt. "\n";
1317 print "Press Enter to continue or CTRL+C to stop now\n";