From bfa0d5065fd260096ad21c2878e6107600f90298 Mon Sep 17 00:00:00 2001 From: dbs Date: Tue, 24 Mar 2009 19:27:37 +0000 Subject: [PATCH] Increase support for serials in Evergreen. The attached patch does the following: Extends and modifies the serials schema: * adds a serial.record_entry (sre) and associated serial.full_rec table for storing MFHD records and their corresponding flattened representation; sre currently links to a bib record * makes serial.subscription.end_date nullable to represent current subscriptions in a simpler fashion; whereas the intent expressed at http://open-ils.org/dokuwiki/doku.php?id=acq:serials:model had been to have one subscription per year, it might be easier (at least for migrating existing data) to generate a single subscription record with a start_date representing the start of holdings and end_date null to indicate that the subscription is current (still receiving issues) * adds serial.issuance.location to give issuances the ability to have a shelving location without having a barcode * links the serial.*_summary tables to serial.subscription rather than to asset.call_number; serial.subscription already has a link to asset.call_number, and the existing DDL actually references serial.subscription even though the column name is "call_number" * adds IDL for the serials schema * adds a vestigial OpenILS::Application::Ingest::Serial package for ingesting serial records * adds a marc2sre.pl import script for generating sre JSON records, based on the MFHD generated by our system git-svn-id: svn://svn.open-ils.org/ILS/trunk@12655 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 131 +++++++++++ Open-ILS/src/extras/import/direct_ingest.pl | 24 +- Open-ILS/src/extras/import/marc2sre.pl | 133 +++++++++++ .../src/perlmods/OpenILS/Application/Actor.pm | 2 +- .../perlmods/OpenILS/Application/Ingest.pm | 216 ++++++++++++++++-- Open-ILS/src/sql/Pg/210.schema.serials.sql | 45 +++- 6 files changed, 514 insertions(+), 37 deletions(-) create mode 100755 Open-ILS/src/extras/import/marc2sre.pl diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 2ff0fa0807..ba05f940ce 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -2534,6 +2534,137 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/extras/import/direct_ingest.pl b/Open-ILS/src/extras/import/direct_ingest.pl index e46de4a2a4..6defcd0ebf 100755 --- a/Open-ILS/src/extras/import/direct_ingest.pl +++ b/Open-ILS/src/extras/import/direct_ingest.pl @@ -26,12 +26,13 @@ use MARC::Charset; MARC::Charset->ignore_errors(1); -my ($max_uri, $max_cn, $auth, $config, $quiet) = - (0, 0, 0, '/openils/conf/opensrf_core.xml'); +my ($max_uri, $max_cn, $auth, $mfhd, $config, $quiet) = + (0, 0, 0, 0, '/openils/conf/opensrf_core.xml'); GetOptions( 'config=s' => \$config, 'authority' => \$auth, + 'serial' => \$mfhd, 'quiet' => \$quiet, 'max_uri=i' => \$max_uri, 'max_cn=i' => \$max_cn, @@ -52,6 +53,7 @@ OpenILS::Application::Ingest->use; my $meth = 'open-ils.ingest.full.biblio.object.readonly'; $meth = 'open-ils.ingest.full.authority.object.readonly' if ($auth); +$meth = 'open-ils.ingest.full.serial.object.readonly' if ($mfhd); $meth = OpenILS::Application::Ingest->method_lookup( $meth ); @@ -84,19 +86,23 @@ while (my $rec = <>) { sub postprocess { my $data = shift; + my ($field_entries, $fp, $rd, $uri); + my $bib = $data->{bib}; my $full_rec = $data->{ingest_data}->{full_rec}; - my $field_entries = $data->{ingest_data}->{field_entries} unless ($auth); - my $fp = $data->{ingest_data}->{fingerprint} unless ($auth); - my $rd = $data->{ingest_data}->{descriptor} unless ($auth); - my $uri = $data->{ingest_data}->{uri} unless ($auth); + if (!$auth && !$mfhd) { + $field_entries = $data->{ingest_data}->{field_entries}; + $fp = $data->{ingest_data}->{fingerprint}; + $rd = $data->{ingest_data}->{descriptor}; + $uri = $data->{ingest_data}->{uri}; - $bib->fingerprint( $fp->{fingerprint} ) unless ($auth); - $bib->quality( $fp->{quality} ) unless ($auth); + $bib->fingerprint( $fp->{fingerprint} ); + $bib->quality( $fp->{quality} ); + } print( OpenSRF::Utils::JSON->perl2JSON($bib)."\n" ); - unless ($auth) { + if (!$auth && !$mfhd) { print( OpenSRF::Utils::JSON->perl2JSON($rd)."\n" ); print( OpenSRF::Utils::JSON->perl2JSON($_)."\n" ) for (@$field_entries); for my $u (@$uri) { diff --git a/Open-ILS/src/extras/import/marc2sre.pl b/Open-ILS/src/extras/import/marc2sre.pl new file mode 100755 index 0000000000..83de43abdb --- /dev/null +++ b/Open-ILS/src/extras/import/marc2sre.pl @@ -0,0 +1,133 @@ +#!/usr/bin/perl +use strict; +use warnings; + +use lib '/openils/lib/perl5/'; + +use OpenSRF::System; +use OpenSRF::Application; +use OpenSRF::EX qw/:try/; +use OpenSRF::AppSession; +use OpenSRF::MultiSession; +use OpenSRF::Utils::SettingsClient; +use OpenILS::Application::AppUtils; +use OpenILS::Utils::Fieldmapper; +use Digest::MD5 qw/md5_hex/; +use OpenSRF::Utils::JSON; +use Data::Dumper; +use Unicode::Normalize; + +use Time::HiRes qw/time/; +use Getopt::Long; +use MARC::Batch; +use MARC::File::XML ( BinaryEncoding => 'utf-8' ); +use MARC::Charset; + +MARC::Charset->ignore_errors(1); + +my ($idfield, $count, $user, $password, $config, $marctype, @files, @trash_fields, $quiet) = + ('001', 1, 'admin', 'open-ils', '/openils/conf/opensrf_core.xml', 'USMARC'); + +GetOptions( + 'idfield=i' => \$idfield, + 'startid=i' => \$count, + 'user=s' => \$user, + 'password=s' => \$password, + 'config=s' => \$config, + 'marctype=s' => \$marctype, + 'file=s' => \@files, + 'quiet' => \$quiet, +); + +@files = @ARGV if (!@files); + +my @ses; +my @req; +my %processing_cache; + +OpenSRF::System->bootstrap_client( config_file => $config ); +Fieldmapper->import(IDL => OpenSRF::Utils::SettingsClient->new->config_value("IDL")); + +$user = OpenILS::Application::AppUtils->check_user_session( login($user,$password) )->id; + +select STDERR; $| = 1; +select STDOUT; $| = 1; + +my $batch = new MARC::Batch ( $marctype, @files ); +$batch->strict_off(); +$batch->warnings_off(); + +my $starttime = time; +my $rec; +while ( try { $rec = $batch->next } otherwise { $rec = -1 } ) { + next if ($rec == -1); + my $id = $count; + my $record_field = $rec->field($idfield); + my $record = $count; + + # On some systems, the 001 actually points to the record ID + # We need to attach to the call number to handle holdings in different libraries + # but we can work out call numbers later in SQL by the record ID + call number text + if ($record_field) { + $record = $record_field->data; + $record =~ s/(\d+)/$1/; + } + + (my $xml = $rec->as_xml_record()) =~ s/\n//sog; + $xml =~ s/^<\?xml.+\?\s*>//go; + $xml =~ s/>\s+entityize($xml,'D'); + $xml =~ s/[\x00-\x1f]//go; + + my $bib = new Fieldmapper::serial::record_entry; + $bib->id($id); + $bib->record($record); + $bib->active('t'); + $bib->deleted('f'); + $bib->marc($xml); + $bib->creator($user); + $bib->create_date('now'); + $bib->editor($user); + $bib->edit_date('now'); + $bib->last_xact_id('IMPORT-'.$starttime); + + print OpenSRF::Utils::JSON->perl2JSON($bib)."\n"; + + $count++; + + if (!$quiet && !($count % 20)) { + print STDERR "\r$count\t". $count / (time - $starttime); + } +} + +sub login { + my( $username, $password, $type ) = @_; + + $type |= "staff"; + + my $seed = OpenILS::Application::AppUtils->simplereq( + 'open-ils.auth', + 'open-ils.auth.authenticate.init', + $username + ); + + die("No auth seed. Couldn't talk to the auth server") unless $seed; + + my $response = OpenILS::Application::AppUtils->simplereq( + 'open-ils.auth', + 'open-ils.auth.authenticate.complete', + { username => $username, + password => md5_hex($seed . md5_hex($password)), + type => $type }); + + die("No auth response returned on login.") unless $response; + + my $authtime = $response->{payload}->{authtime}; + my $authtoken = $response->{payload}->{authtoken}; + + die("Login failed for user $username!") unless $authtoken; + + return $authtoken; +} + diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Actor.pm b/Open-ILS/src/perlmods/OpenILS/Application/Actor.pm index c0d9aa62b0..2091198b71 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Actor.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Actor.pm @@ -1017,7 +1017,7 @@ __PACKAGE__->register_method( sub get_org_tree { my $self = shift; my $client = shift; - return $U->get_org_tree($client->session->session_locale); + return $U->get_org_tree($client->session->session_locale); } diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Ingest.pm b/Open-ILS/src/perlmods/OpenILS/Application/Ingest.pm index 972953224a..5b957722b1 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Ingest.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Ingest.pm @@ -9,6 +9,7 @@ use OpenSRF::AppSession; use OpenSRF::Utils::SettingsClient; use OpenSRF::Utils::Logger qw/:level/; +use OpenILS::Application::AppUtils; use OpenILS::Utils::ScriptRunner; use OpenILS::Utils::Fieldmapper; use OpenSRF::Utils::JSON; @@ -101,20 +102,6 @@ sub post_init { } } -sub entityize { - my $stuff = shift; - my $form = shift; - - if ($form eq 'D') { - $stuff = NFD($stuff); - } else { - $stuff = NFC($stuff); - } - - $stuff =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe; - return $stuff; -} - # -------------------------------------------------------------------------------- # Biblio ingest @@ -407,7 +394,7 @@ sub ro_biblio_ingest_single_object { my $self = shift; my $client = shift; my $bib = shift; - my $xml = OpenILS::Application::Ingest::entityize($bib->marc); + my $xml = OpenILS::Application::AppUtils->entityize($bib->marc); my $max_cn = shift; my $max_uri = shift; @@ -449,7 +436,7 @@ __PACKAGE__->register_method( sub ro_biblio_ingest_single_xml { my $self = shift; my $client = shift; - my $xml = OpenILS::Application::Ingest::entityize(shift); + my $xml = OpenILS::Application::AppUtils->entityize(shift); my $document = $parser->parse_string($xml); @@ -597,7 +584,7 @@ sub ro_authority_ingest_single_object { my $self = shift; my $client = shift; my $bib = shift; - my $xml = OpenILS::Application::Ingest::entityize($bib->marc); + my $xml = OpenILS::Application::AppUtils->entityize($bib->marc); my $document = $parser->parse_string($xml); @@ -613,11 +600,18 @@ __PACKAGE__->register_method( api_level => 1, argc => 1, ); +__PACKAGE__->register_method( + api_name => "open-ils.ingest.full.serial.object.readonly", + method => "ro_authority_ingest_single_object", + api_level => 1, + argc => 1, +); + sub ro_authority_ingest_single_xml { my $self = shift; my $client = shift; - my $xml = OpenILS::Application::Ingest::entityize(shift); + my $xml = OpenILS::Application::AppUtils->entityize(shift); my $document = $parser->parse_string($xml); @@ -747,6 +741,167 @@ __PACKAGE__->register_method( stream => 1, ); +# -------------------------------------------------------------------------------- +# Serial ingest + +package OpenILS::Application::Ingest::Serial; +use base qw/OpenILS::Application::Ingest/; +use Unicode::Normalize; + +sub ro_serial_ingest_single_object { + my $self = shift; + my $client = shift; + my $bib = shift; + my $xml = OpenILS::Application::AppUtils->entityize($bib->marc); + + my $document = $parser->parse_string($xml); + + my @mfr = $self->method_lookup("open-ils.ingest.flat_marc.serial.xml")->run($document); + + $_->record($bib->id) for (@mfr); + + return { full_rec => \@mfr }; +} +__PACKAGE__->register_method( + api_name => "open-ils.ingest.full.serial.object.readonly", + method => "ro_serial_ingest_single_object", + api_level => 1, + argc => 1, +); + +sub ro_serial_ingest_single_xml { + my $self = shift; + my $client = shift; + my $xml = OpenILS::Application::AppUtils->entityize(shift); + + my $document = $parser->parse_string($xml); + + my @mfr = $self->method_lookup("open-ils.ingest.flat_marc.serial.xml")->run($document); + + return { full_rec => \@mfr }; +} +__PACKAGE__->register_method( + api_name => "open-ils.ingest.full.serial.xml.readonly", + method => "ro_serial_ingest_single_xml", + api_level => 1, + argc => 1, +); + +sub ro_serial_ingest_single_record { + my $self = shift; + my $client = shift; + my $rec = shift; + + OpenILS::Application::Ingest->post_init(); + my $r = OpenSRF::AppSession + ->create('open-ils.cstore') + ->request( 'open-ils.cstore.direct.serial.record_entry.retrieve' => $rec ) + ->gather(1); + + return undef unless ($r and @$r); + + my ($res) = $self->method_lookup("open-ils.ingest.full.serial.xml.readonly")->run($r->marc); + + $_->record($rec) for (@{$res->{full_rec}}); + $res->{descriptor}->record($rec); + + return $res; +} +__PACKAGE__->register_method( + api_name => "open-ils.ingest.full.serial.record.readonly", + method => "ro_serial_ingest_single_record", + api_level => 1, + argc => 1, +); + +sub ro_serial_ingest_stream_record { + my $self = shift; + my $client = shift; + + OpenILS::Application::Ingest->post_init(); + + my $ses = OpenSRF::AppSession->create('open-ils.cstore'); + + while (my ($resp) = $client->recv( count => 1, timeout => 5 )) { + + my $rec = $resp->content; + last unless (defined $rec); + + $log->debug("Running open-ils.ingest.full.serial.record.readonly ..."); + my ($res) = $self->method_lookup("open-ils.ingest.full.serial.record.readonly")->run($rec); + + $_->record($rec) for (@{$res->{full_rec}}); + + $client->respond( $res ); + } + + return undef; +} +__PACKAGE__->register_method( + api_name => "open-ils.ingest.full.serial.record_stream.readonly", + method => "ro_serial_ingest_stream_record", + api_level => 1, + stream => 1, +); + +sub ro_serial_ingest_stream_xml { + my $self = shift; + my $client = shift; + + OpenILS::Application::Ingest->post_init(); + + my $ses = OpenSRF::AppSession->create('open-ils.cstore'); + + while (my ($resp) = $client->recv( count => 1, timeout => 5 )) { + + my $xml = $resp->content; + last unless (defined $xml); + + $log->debug("Running open-ils.ingest.full.serial.xml.readonly ..."); + my ($res) = $self->method_lookup("open-ils.ingest.full.serial.xml.readonly")->run($xml); + + $client->respond( $res ); + } + + return undef; +} +__PACKAGE__->register_method( + api_name => "open-ils.ingest.full.serial.xml_stream.readonly", + method => "ro_serial_ingest_stream_xml", + api_level => 1, + stream => 1, +); + +sub rw_serial_ingest_stream_import { + my $self = shift; + my $client = shift; + + OpenILS::Application::Ingest->post_init(); + + my $ses = OpenSRF::AppSession->create('open-ils.cstore'); + + while (my ($resp) = $client->recv( count => 1, timeout => 5 )) { + + my $bib = $resp->content; + last unless (defined $bib); + + $log->debug("Running open-ils.ingest.full.serial.xml.readonly ..."); + my ($res) = $self->method_lookup("open-ils.ingest.full.serial.xml.readonly")->run($bib->marc); + + $_->record($bib->id) for (@{$res->{full_rec}}); + + $client->respond( $res ); + } + + return undef; +} +__PACKAGE__->register_method( + api_name => "open-ils.ingest.full.serial.bib_stream.import", + method => "rw_serial_ingest_stream_import", + api_level => 1, + stream => 1, +); + # -------------------------------------------------------------------------------- # MARC index extraction @@ -798,7 +953,7 @@ sub class_index_string_xml { my @classes = @_; OpenILS::Application::Ingest->post_init(); - $xml = $parser->parse_string(OpenILS::Application::Ingest::entityize($xml)) unless (ref $xml); + $xml = $parser->parse_string(OpenILS::Application::AppUtils->entityize($xml)) unless (ref $xml); my %transform_cache; @@ -1037,10 +1192,11 @@ sub flat_marc_xml { $log->debug("processing [$xml]"); - $xml = $parser->parse_string(OpenILS::Application::Ingest::entityize($xml)) unless (ref $xml); + $xml = $parser->parse_string(OpenILS::Application::AppUtils->entityize($xml)) unless (ref $xml); my $type = 'metabib'; $type = 'authority' if ($self->api_name =~ /authority/o); + $type = 'serial' if ($self->api_name =~ /serial/o); OpenILS::Application::Ingest->post_init(); @@ -1061,6 +1217,13 @@ __PACKAGE__->register_method( argc => 1, stream => 1, ); +__PACKAGE__->register_method( + api_name => "open-ils.ingest.flat_marc.serial.xml", + method => "flat_marc_xml", + api_level => 1, + argc => 1, + stream => 1, +); sub flat_marc_record { my $self = shift; @@ -1069,6 +1232,7 @@ sub flat_marc_record { my $type = 'biblio'; $type = 'authority' if ($self->api_name =~ /authority/o); + $type = 'serial' if ($self->api_name =~ /serial/o); OpenILS::Application::Ingest->post_init(); my $r = OpenSRF::AppSession @@ -1100,7 +1264,13 @@ __PACKAGE__->register_method( argc => 1, stream => 1, ); - +__PACKAGE__->register_method( + api_name => "open-ils.ingest.flat_marc.serial.record_entry", + method => "flat_marc_record", + api_level => 1, + argc => 1, + stream => 1, +); # -------------------------------------------------------------------------------- # URI extraction @@ -1289,7 +1459,7 @@ our $fp_script; sub biblio_fingerprint { my $self = shift; my $client = shift; - my $xml = OpenILS::Application::Ingest::entityize(shift); + my $xml = OpenILS::Application::AppUtils->entityize(shift); $log->internal("Got MARC [$xml]"); @@ -1327,7 +1497,7 @@ our $rd_script; sub biblio_descriptor { my $self = shift; my $client = shift; - my $xml = OpenILS::Application::Ingest::entityize(shift); + my $xml = OpenILS::Application::AppUtils->entityize(shift); $log->internal("Got MARC [$xml]"); diff --git a/Open-ILS/src/sql/Pg/210.schema.serials.sql b/Open-ILS/src/sql/Pg/210.schema.serials.sql index 03245f5a05..43e8c568c0 100644 --- a/Open-ILS/src/sql/Pg/210.schema.serials.sql +++ b/Open-ILS/src/sql/Pg/210.schema.serials.sql @@ -6,12 +6,48 @@ BEGIN; CREATE SCHEMA serial; +CREATE TABLE serial.record_entry ( + id BIGSERIAL PRIMARY KEY, + record BIGINT REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + creator INT NOT NULL DEFAULT 1, + editor INT NOT NULL DEFAULT 1, + source INT, + create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), + edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(), + active BOOL NOT NULL DEFAULT TRUE, + deleted BOOL NOT NULL DEFAULT FALSE, + marc TEXT NOT NULL, + last_xact_id TEXT NOT NULL +); +CREATE INDEX serial_record_entry_creator_idx ON serial.record_entry ( creator ); +CREATE INDEX serial_record_entry_editor_idx ON serial.record_entry ( editor ); + +CREATE TABLE serial.full_rec ( + id BIGSERIAL PRIMARY KEY, + record BIGINT NOT NULL REFERENCES serial.record_entry(id) DEFERRABLE INITIALLY DEFERRED, + tag CHAR(3) NOT NULL, + ind1 TEXT, + ind2 TEXT, + subfield TEXT, + value TEXT NOT NULL, + index_vector tsvector NOT NULL +); +CREATE INDEX serial_full_rec_record_idx ON serial.full_rec (record); +CREATE INDEX serial_full_rec_tag_part_idx ON serial.full_rec (SUBSTRING(tag FROM 2)); +CREATE TRIGGER serial_full_rec_fti_trigger + BEFORE UPDATE OR INSERT ON serial.full_rec + FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); + +CREATE INDEX serial_full_rec_index_vector_idx ON serial.full_rec USING GIST (index_vector); +/* Enable LIKE to use an index for database clusters with locales other than C or POSIX */ +CREATE INDEX serial_full_rec_value_tpo_index ON serial.full_rec (value text_pattern_ops); + CREATE TABLE serial.subscription ( id SERIAL PRIMARY KEY, callnumber BIGINT REFERENCES asset.call_number (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, uri INT REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, start_date DATE NOT NULL, - end_date DATE NOT NULL + end_date DATE -- interpret NULL as current subscription ); CREATE TABLE serial.binding_unit ( @@ -25,27 +61,28 @@ CREATE TABLE serial.issuance ( id SERIAL PRIMARY KEY, subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, target_copy BIGINT REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + location BIGINT REFERENCES asset.copy_location(id) DEFERRABLE INITIALLY DEFERRED, binding_unit INT REFERENCES serial.binding_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, label TEXT ); CREATE TABLE serial.bib_summary ( id SERIAL PRIMARY KEY, - call_number INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, generated_coverage TEXT NOT NULL, textual_holdings TEXT ); CREATE TABLE serial.sup_summary ( id SERIAL PRIMARY KEY, - call_number INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, generated_coverage TEXT NOT NULL, textual_holdings TEXT ); CREATE TABLE serial.index_summary ( id SERIAL PRIMARY KEY, - call_number INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, generated_coverage TEXT NOT NULL, textual_holdings TEXT ); -- 2.43.2