Bookdata-tools/viaf-index.sql

CREATE INDEX viaf_marc_field_rec_idx ON viaf_marc_field (rec_id);

CREATE VIEW viaf_record_codes
  AS SELECT rec_id,
       SUBSTR(contents, 6, 1) AS status,
       SUBSTR(contents, 7, 1) AS rec_type,
       substr(CONTENTS, 8, 1) AS bib_level
  FROM viaf_marc_field WHERE tag = 'LDR';
CREATE MATERIALIZED VIEW viaf_marc_cn
  AS SELECT rec_id, trim(contents) AS control
  FROM viaf_marc_field
  WHERE tag = '001';
CREATE INDEX viaf_marc_cn_rec_idx ON viaf_marc_cn (rec_id);
ANALYZE viaf_marc_cn;
CREATE MATERIALIZED VIEW viaf_isbn
AS SELECT rec_id, TRIM(contents) AS isbn
   FROM viaf_marc_field WHERE tag = '901' AND sf_code = 'a';
CREATE INDEX viaf_isbn_rec_idx ON viaf_isbn (rec_id);
CREATE INDEX viaf_isbn_isbn_idx ON viaf_isbn (isbn);

DROP TABLE IF EXISTS viaf_author_name CASCADE;
CREATE TABLE viaf_author_name (
  rec_id INTEGER NOT NULL,
  ind VARCHAR(1) NOT NULL,
  name VARCHAR NOT NULL
);
INSERT INTO viaf_author_name
  SELECT rec_id, ind1, regexp_replace(contents, '\W+$', '') AS name
  FROM viaf_marc_field
  WHERE TAG = '700' AND sf_code = 'a';
CREATE INDEX viaf_author_rec_idx ON viaf_author_name (rec_id);
CREATE INDEX viaf_author_name_idx ON viaf_author_name (name);
INSERT INTO viaf_author_name
  SELECT rec_id, 'S', regexp_replace(name, '^(.*), (.*)', '\2 \1')
  FROM viaf_author_name
  WHERE ind = '1';

CREATE MATERIALIZED VIEW viaf_author_gender
  AS SELECT rec_id, contents AS gender
  FROM viaf_marc_field
  WHERE TAG = '375' AND sf_code = 'a';
CREATE INDEX viaf_gender_rec_idx ON viaf_author_gender (rec_id);

-- CREATE INDEX viaf_author_name_id_idx ON viaf_author_name (viaf_au_id);
-- CREATE INDEX viaf_author_name_idx ON viaf_author_name (viaf_au_name);
-- ALTER TABLE viaf_author_name ADD CONSTRAINT viaf_au_name_fk FOREIGN KEY (viaf_au_id) REFERENCES viaf_author;

-- DELETE FROM viaf_author_name WHERE viaf_au_name_source = 'SYNTH';
-- INSERT INTO viaf_author_name (viaf_au_id, viaf_au_name, viaf_au_name_source, viaf_au_name_dates)
-- SELECT viaf_au_id, regexp_replace(regexp_replace(viaf_au_name, ',$', ''), '^(.*), (.*)', '\2 \1'), 'SYNTH', viaf_au_name_dates
-- FROM viaf_author_name
-- WHERE viaf_au_name LIKE '%,%';

-- CREATE INDEX viaf_gender_id_idx ON viaf_author_gender (viaf_au_id);
-- ALTER TABLE viaf_author_gender ADD CONSTRAINT viaf_au_id_fk FOREIGN KEY (viaf_au_id) REFERENCES viaf_author;