[rt18107] added branch syncing

[sitka/sitka-tools.git] / reindexing / reindex.sql

DROP TABLE reindex.to_do;
CREATE TABLE reindex.to_do (id int);

DROP TABLE reindex.failed;
CREATE TABLE reindex.failed (id int, reason TEXT);

-- INSERT ALL THE BIBS
--INSERT INTO reindex.to_do SELECT id from biblio.record_entry where not deleted;

CREATE OR REPLACE FUNCTION reindex.cleanup_marc(TEXT) RETURNS TEXT as $func$
use MARC::Record;
use MARC::File::XML (BinaryEncoding => 'UTF-8');
use MARC::Charset;
use Unicode::Normalize;
use strict;

MARC::Charset->assume_unicode(1);

my $xml = shift;
my $r = MARC::Record->new_from_xml( $xml );

for my $f ( $r->field('901') ) {
    $r->delete_field($f)
}   

$xml = $r->as_xml_record();

$xml =~ s/\n//sgo;
$xml =~ s/^<\?xml.+\?\s*>//go;
$xml =~ s/>\s+</></go;
$xml =~ s/\p{Cc}//go;

$xml = decode_utf8($xml);

$xml = NFC($xml);

# Convert raw ampersands to entities
$xml =~ s/&(?!\S+;)/&amp;/gso;

# Convert Unicode characters to entities
$xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;

$xml =~ s/[\x00-\x1f]//go;


return $xml;
$func$ LANGUAGE PLPERLU;

CREATE OR REPLACE FUNCTION reindex.reindex(count INTEGER) RETURNS timestamp AS $$
DECLARE
    myid INTEGER := 0;
    i INTEGER := 0;
BEGIN
    FOR myid IN SELECT reindex.to_do.id FROM reindex.to_do LIMIT count LOOP
        i = i + 1;
        BEGIN
            RAISE NOTICE 'Reindexing id: % -- % of % records', myid, i, count;
            DELETE FROM reindex.to_do WHERE reindex.to_do.id = myid;
            -- change this or add more functions as needed
            UPDATE biblio.record_entry set marc = public.entityize(reindex.cleanup_marc(marc)) where biblio.record_entry.id = myid;
        EXCEPTION WHEN unique_violation THEN
            INSERT INTO reindex.failed (id, reason) VALUES (myid, 'unique violation');
            DELETE FROM reindex.to_do WHERE reindex.to_do.id = myid;
            RAISE NOTICE '% had a unique key violation and could not be reindexed!', myid;
        EXCEPTION WHEN OTHERS THEN
            INSERT INTO reindex.failed (id, reason) VALUES (myid, 'unhandled error');
            DELETE FROM reindex.to_do WHERE reindex.to_do.id = myid;
            RAISE NOTICE '% had a unique key violation and could not be reindexed!', myid;
        END;
    END LOOP;
    RETURN now();
    
END;
$$ LANGUAGE plpgsql;