7 use OpenSRF::EX qw/:try/;
8 use OpenSRF::Utils::Logger qw/$logger/;
10 use OpenSRF::AppSession;
11 use OpenSRF::Utils::SettingsClient;
13 my ($config, $chunk_size, $output) = ('/openils/conf/opensrf_core.xml', 0, 'reingest-1.6-2.0.sql');
16 "config:s" => \$config,
17 "chunk_size:i" => \$chunk_size,
18 "output:s" => \$output,
21 print qq{$0: generate SQL script to reingest bibs during an upgrade to Evergreen 2.0
23 By default, the script writes to the file reingest-1.6-2.0.sql. To modify
24 this script's behavior, you can supply the following options:
26 --config /path/to/opensrf_core.xml used to get connection information to
27 the Evergreen database
28 --chunk_size n number of bibs to reingest in a chunk;
29 specify if you don't want all of the
30 bibs in the database to be reindexes
31 in a single transaction
32 --output /path/to/output_file.sql path of output SQL file
36 open OUT, ">$output" or die "$0: cannot open output file $output: $!\n";
37 print "Writing output to file $output\n";
39 $num_bibs = fetch_num_bibs_from_database($config) if $chunk_size;
42 body($num_bibs, $chunk_size);
46 SQL script complete. To perform the reingest, please run the script using
47 the psql program, e.g.,
49 psql {connection parameters} < $output
51 If you are running a large Evergreen installation, it is recommend that you
52 examine the script first; note that a reingest of a large Evergreen database
53 can take several hours.
56 sub fetch_num_bibs_from_database {
58 OpenSRF::System->bootstrap_client( config_file => $config );
59 my $sc = OpenSRF::Utils::SettingsClient->new;
60 my $db_driver = $sc->config_value( reporter => setup => database => 'driver' );
61 my $db_host = $sc->config_value( reporter => setup => database => 'host' );
62 my $db_port = $sc->config_value( reporter => setup => database => 'port' );
63 my $db_name = $sc->config_value( reporter => setup => database => 'db' );
65 $db_name = $sc->config_value( reporter => setup => database => 'name' );
67 my $db_user = $sc->config_value( reporter => setup => database => 'user' );
68 my $db_pw = $sc->config_value( reporter => setup => database => 'pw' );
69 die "Unable to retrieve database connection information from the settings server"
70 unless ($db_driver && $db_host && $db_port && $db_name && $db_user);
72 my $dsn = "dbi:" . $db_driver . ":dbname=" . $db_name .';host=' . $db_host . ';port=' . $db_port;
73 my $dbh = DBI->connect($dsn, $db_user, $db_pw, {AutoCommit => 1, pg_enable_utf8 => 1, RaiseError => 1});
74 my $count = $dbh->selectrow_array('SELECT COUNT(*) FROM biblio.record_entry WHERE id > -1 AND NOT deleted');
80 \qecho First, make sure that the rows needed for title sorting are
84 DELETE FROM metabib.real_full_rec WHERE tag = 'tnf';
85 INSERT INTO metabib.real_full_rec (record, tag, subfield, value)
89 SUBSTRING(value, COALESCE(NULLIF(REGEXP_REPLACE(ind2,'[^0-9]','','g'),''),'0')::int + 1)
90 FROM metabib.real_full_rec
95 \qecho Do a partial reingest to fully populate metabib.facet_entry
96 \qecho and update the keyword indexes to reflect changes in the default
97 \qecho NACO normalization. This can be time consuming on large databases.
99 \qecho public.upgrade_2_0_partial_bib_reingest is a thin wrapper around
100 \qecho metabib.reingest_metabib_field_entries, which does the actual
101 \qecho work; its main purpose is to suggest a means of doing the reingest
102 \qecho piecemeal if there isn't sufficient time in the upgrade window
103 \qecho to do it in one fell swoop.
104 CREATE OR REPLACE FUNCTION public.upgrade_2_0_partial_bib_reingest( l_offset BIGINT, l_limit BIGINT ) RETURNS VOID AS $func$
106 PERFORM metabib.reingest_metabib_field_entries(id)
107 FROM biblio.record_entry
110 FROM biblio.record_entry
118 $func$ LANGUAGE PLPGSQL;
123 my ($num_bibs, $chunk_size) = @_;
124 if ($num_bibs && $chunk_size) {
125 for (my $i = 0; $i <= $num_bibs / $chunk_size; $i++) {
126 print OUT qq{SELECT public.upgrade_2_0_partial_bib_reingest($i * $chunk_size, $chunk_size);\n};
130 SELECT public.upgrade_2_0_partial_bib_reingest(0, (SELECT COUNT(*) FROM biblio.record_entry WHERE id > -1 AND NOT deleted));
137 -- clean up after ourselves
138 DROP FUNCTION public.upgrade_2_0_partial_bib_reingest(BIGINT, BIGINT);
140 VACUUM ANALYZE metabib.keyword_field_entry;
141 VACUUM ANALYZE metabib.author_field_entry;
142 VACUUM ANALYZE metabib.subject_field_entry;
143 VACUUM ANALYZE metabib.title_field_entry;
144 VACUUM ANALYZE metabib.series_field_entry;
145 VACUUM ANALYZE metabib.identifier_field_entry;
146 VACUUM ANALYZE metabib.facet_entry;