From 6a7ef0fd22203e27845e65ba20c960d0bf4ec85e Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 18 Aug 2008 15:30:55 +0000 Subject: [PATCH] script, based on old final.load-bib.sql, to clear and (if modified so) to reload the db --- clear-db.sql | 125 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 125 insertions(+), 0 deletions(-) create mode 100644 clear-db.sql diff --git a/clear-db.sql b/clear-db.sql new file mode 100644 index 0000000..e945049 --- /dev/null +++ b/clear-db.sql @@ -0,0 +1,125 @@ +SET CLIENT_ENCODING TO 'UNICODE'; + +BEGIN; + +SELECT reporter.disable_materialized_simple_record_trigger(); + +CREATE TEMP TABLE dummy_bib AS SELECT * FROM biblio.record_entry WHERE id = -1; +CREATE TEMP TABLE dummy_cn AS SELECT * FROM asset.call_number WHERE id = -1; + +CREATE TEMP TABLE backup_loc AS SELECT * FROM asset.copy_location WHERE id = 1; +CREATE TEMP TABLE backup_usr AS SELECT * FROM actor.usr WHERE id = 1; +CREATE TEMP TABLE backup_card AS SELECT * FROM actor.card WHERE usr = 1; + +UPDATE backup_usr SET card = NULL; + +DROP INDEX metabib.metabib_full_rec_index_vector_idx; +DROP INDEX metabib.metabib_title_field_entry_index_vector_idx; +DROP INDEX metabib.metabib_author_field_entry_index_vector_idx; +DROP INDEX metabib.metabib_subject_field_entry_index_vector_idx; +DROP INDEX metabib.metabib_keyword_field_entry_index_vector_idx; +DROP INDEX metabib.metabib_series_field_entry_index_vector_idx; + +TRUNCATE biblio.record_entry CASCADE; +TRUNCATE metabib.full_rec; +TRUNCATE metabib.rec_descriptor; +TRUNCATE metabib.title_field_entry; +TRUNCATE metabib.author_field_entry; +TRUNCATE metabib.subject_field_entry; +TRUNCATE metabib.keyword_field_entry; +TRUNCATE metabib.series_field_entry; +TRUNCATE auditor.biblio_record_entry_history; + +TRUNCATE actor.usr CASCADE; +TRUNCATE actor.card CASCADE; +TRUNCATE actor.usr_address CASCADE; +TRUNCATE actor.stat_cat CASCADE; +TRUNCATE actor.stat_cat_entry_usr_map CASCADE; +TRUNCATE money.grocery CASCADE; +TRUNCATE money.billing CASCADE; +TRUNCATE action.circulation CASCADE; +TRUNCATE action.hold_request CASCADE; + +SELECT SETVAL('biblio.record_entry_id_seq', 1); + +SELECT SETVAL('money.billable_xact_id_seq', 1); +SELECT SETVAL('money.billing_id_seq', 1); + +SELECT SETVAL('action.hold_request_id_seq', 1); + +SELECT SETVAL('asset.call_number_id_seq', 2); +SELECT SETVAL('asset.copy_id_seq', 1); +SELECT SETVAL('asset.copy_location_id_seq', 2); +SELECT SETVAL('asset.stat_cat_id_seq', 1); +SELECT SETVAL('asset.stat_cat_entry_id_seq', 1); +SELECT SETVAL('asset.stat_cat_entry_copy_map_id_seq', 1); + +SELECT SETVAL('actor.usr_id_seq', 2); +SELECT SETVAL('actor.card_id_seq', 2); +SELECT SETVAL('actor.usr_address_id_seq', 2); +SELECT SETVAL('actor.stat_cat_id_seq', 1); +SELECT SETVAL('actor.stat_cat_entry_id_seq', 1); +SELECT SETVAL('actor.stat_cat_entry_usr_map_id_seq', 1); + +SELECT SETVAL('metabib.full_rec_id_seq', 1); +SELECT SETVAL('metabib.rec_descriptor_id_seq', 1); +SELECT SETVAL('metabib.title_field_entry_id_seq', 1); +SELECT SETVAL('metabib.author_field_entry_id_seq', 1); +SELECT SETVAL('metabib.subject_field_entry_id_seq', 1); +SELECT SETVAL('metabib.keyword_field_entry_id_seq', 1); +SELECT SETVAL('metabib.series_field_entry_id_seq', 1); +SELECT SETVAL('metabib.metarecord_id_seq', 1); +SELECT SETVAL('metabib.metarecord_source_map_id_seq', 1); + +INSERT INTO biblio.record_entry SELECT * FROM dummy_bib; +INSERT INTO asset.call_number SELECT * FROM dummy_cn; +INSERT INTO asset.copy_location SELECT * FROM backup_loc; +INSERT INTO actor.usr SELECT * FROM backup_usr; +INSERT INTO actor.card SELECT * FROM backup_card; +UPDATE actor.usr SET card = actor.card.id FROM actor.card WHERE actor.usr.id = actor.card.usr; + +-- Put any scripts that reload bibs/items/etc here. Example included. +/* + +\i incumbent.sql +\i incoming.sql + +*/ + +CREATE INDEX metabib_title_field_entry_index_vector_idx ON metabib.title_field_entry USING GIST (index_vector); +CREATE INDEX metabib_author_field_entry_index_vector_idx ON metabib.author_field_entry USING GIST (index_vector); +CREATE INDEX metabib_subject_field_entry_index_vector_idx ON metabib.subject_field_entry USING GIST (index_vector); +CREATE INDEX metabib_keyword_field_entry_index_vector_idx ON metabib.keyword_field_entry USING GIST (index_vector); +CREATE INDEX metabib_series_field_entry_index_vector_idx ON metabib.series_field_entry USING GIST (index_vector); +CREATE INDEX metabib_full_rec_index_vector_idx ON metabib.full_rec USING GIST (index_vector); +CREATE INDEX metabib_full_rec_tag_subfield_idx ON metabib.full_rec (tag,subfield); +CREATE INDEX metabib_full_value_idx ON metabib.full_rec (value); + +ALTER TABLE metabib.metarecord_source_map DROP CONSTRAINT metabib_metarecord_source_map_metarecord_fkey; + +TRUNCATE metabib.metarecord; +TRUNCATE metabib.metarecord_source_map; + +INSERT INTO metabib.metarecord (fingerprint,master_record) + SELECT fingerprint,id + FROM (SELECT DISTINCT ON (fingerprint) + fingerprint, id, quality + FROM biblio.record_entry + ORDER BY fingerprint, quality desc) AS x + WHERE fingerprint IS NOT NULL; + +INSERT INTO metabib.metarecord_source_map (metarecord,source) + SELECT m.id, b.id + FROM biblio.record_entry b + JOIN metabib.metarecord m ON (m.fingerprint = b.fingerprint); + +ALTER TABLE metabib.metarecord_source_map + ADD CONSTRAINT metabib_metarecord_source_map_metarecord_fkey + FOREIGN KEY (metarecord) REFERENCES metabib.metarecord (id) DEFERRABLE INITIALLY DEFERRED; + +SELECT reporter.enable_materialized_simple_record_trigger(); + + +-- COMMIT; +-- VACUUM FULL; + -- 1.7.2.5