Evergreen DokuWiki

User Tools

Site Tools

You are here: start » acq » serials » basic_predicting_and_receiving
acq:serials:basic_predicting_and_receiving

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
acq:serials:basic_predicting_and_receiving [2010/05/13 11:14] – expected_date_offset now an INTERVAL dbw2acq:serials:basic_predicting_and_receiving [2022/02/10 13:34] (current) – external edit 127.0.0.1
Line 1: Line 1:
 ====== Proposal: Serials - Basic Predicting and Receiving ====== ====== Proposal: Serials - Basic Predicting and Receiving ======
 FIXME This proposal is still in progress, please do not edit yet FIXME FIXME This proposal is still in progress, please do not edit yet FIXME
 +
 +Okay - but let's keep a link to the [[https://docs.google.com/drawings/view?id=196Cyl9CAIzZ33cOVt0AphvUSbAwc9kfRGRB5Nz-5tTI&hl=en&pli=1|latest schema diagram]] handy, at least.
 +
 ===== Summary ===== ===== Summary =====
 Now that MFHD records can be added and are linked to bib records, the next step is to start populating the issuance table in order to predict and receive issues. As issues are received the MFHD record must be updated accordingly.  This proposal will focus on fleshing-out the database schema and defining how the MFHD record will hook in. Now that MFHD records can be added and are linked to bib records, the next step is to start populating the issuance table in order to predict and receive issues. As issues are received the MFHD record must be updated accordingly.  This proposal will focus on fleshing-out the database schema and defining how the MFHD record will hook in.
Line 15: Line 18:
  id BIGSERIAL PRIMARY KEY,  id BIGSERIAL PRIMARY KEY,
  record BIGINT REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,  record BIGINT REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
- owning_lib INT NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + owning_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, 
- creator INT NOT NULL DEFAULT 1, + creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 
- editor INT NOT NULL DEFAULT 1,+ editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  source INT,  source INT,
  create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),  create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
Line 48: Line 51:
  holding_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,  holding_lib INT REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
         label TEXT    NOT NULL,         label TEXT    NOT NULL,
- default_call_number BIGINT REFERENCES asset.call_number (id) DEFERRABLE INITIALLY DEFERRED, + receive_call_number BIGINT REFERENCES asset.call_number (id) DEFERRABLE INITIALLY DEFERRED, 
- -- the copy_transparency table was meant for something else, but may serve us here + receive_unit_template INT REFERENCES asset.copy_template (id) DEFERRABLE INITIALLY DEFERRED, 
- -- ultimately, we need some sort of shared/global copy template (current copy templates are stored as a user setting), + bind_call_number BIGINT REFERENCES asset.call_number (id) DEFERRABLE INITIALLY DEFERRED, 
- -- ideally one which also stores location (rendering the default_location column obsolete) + bind_unit_template INT REFERENCES asset.copy_template (id) DEFERRABLE INITIALLY DEFERRED,
- -- copy_transparency INT REFERENCES asset.copy_transparency (id) DEFERRABLE INITIALLY DEFERRED, +
- default_location INT REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,+
         unit_label_base TEXT    DEFAULT NULL,         unit_label_base TEXT    DEFAULT NULL,
         unit_label_suffix TEXT    DEFAULT NULL         unit_label_suffix TEXT    DEFAULT NULL
Line 65: Line 66:
 CREATE TABLE serial.issuance ( CREATE TABLE serial.issuance (
  id SERIAL PRIMARY KEY,  id SERIAL PRIMARY KEY,
- creator INT NOT NULL DEFAULT 1, + creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 
-        editor INT NOT NULL DEFAULT 1,+        editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
         create_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),         create_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
         edit_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),         edit_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
Line 73: Line 74:
         date_published TIMESTAMP WITH TIME ZONE DEFAULT NULL,         date_published TIMESTAMP WITH TIME ZONE DEFAULT NULL,
         holding_code TEXT DEFAULT NULL,         holding_code TEXT DEFAULT NULL,
-        holding_type TEXT DEFAULT NULL CHECK (holding_type IN (NULL,'basic','supplement','index')),+        holding_type TEXT DEFAULT NULL CHECK ( holding_type IS NULL OR holding_type IN ('basic','supplement','index') ),
         holding_link_id INT DEFAULT NULL         holding_link_id INT DEFAULT NULL
  -- TODO: add columns for separate enumeration/chronology values  -- TODO: add columns for separate enumeration/chronology values
Line 80: Line 81:
 CREATE TABLE serial.item ( CREATE TABLE serial.item (
  id SERIAL PRIMARY KEY,  id SERIAL PRIMARY KEY,
- creator INT NOT NULL DEFAULT 1, + creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 
-        editor INT NOT NULL DEFAULT 1,+        editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
         create_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),         create_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
         edit_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),         edit_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
Line 101: Line 102:
  id SERIAL PRIMARY KEY,  id SERIAL PRIMARY KEY,
  item INT NOT NULL REFERENCES serial.item (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,  item INT NOT NULL REFERENCES serial.item (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
- creator INT NOT NULL DEFAULT 1,+ creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),  create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  pub BOOL NOT NULL DEFAULT FALSE,  pub BOOL NOT NULL DEFAULT FALSE,
Line 129: Line 130:
 ); );
  
-COMMIT;</code>+COMMIT; 
 + 
 +-- to be moved to asset schema file 
 +CREATE TABLE asset.copy_template ( 
 + id SERIAL PRIMARY KEY, 
 + owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 
 + creator BIGINT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 
 + editor BIGINT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 
 + create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), 
 + edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), 
 + name TEXT NOT NULL, 
 + -- columns above this point are attributes of the template itself 
 + -- columns after this point are attributes of the copy this template modifies/creates 
 + circ_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, 
 + status INT, 
 + location INT, 
 + loan_duration INT CHECK ( loan_duration IS NULL OR loan_duration IN (1,2,3) ), 
 + fine_level INT CHECK ( loan_duration IS NULL OR loan_duration IN (1,2,3) ), 
 + age_protect INT, 
 + circulate BOOL, 
 + deposit BOOL, 
 + ref BOOL, 
 + holdable BOOL, 
 + deposit_amount NUMERIC(6,2), 
 + price NUMERIC(8,2), 
 + circ_modifier TEXT, 
 + circ_as_type TEXT, 
 + alert_message TEXT, 
 + opac_visible BOOL, 
 + floating BOOL, 
 + mint_condition BOOL 
 +); 
 + 
 +</code>
 ==== Schema ==== ==== Schema ====
 === Problems and Solutions === === Problems and Solutions ===
Line 144: Line 178:
  id BIGSERIAL PRIMARY KEY,  id BIGSERIAL PRIMARY KEY,
  record BIGINT REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,  record BIGINT REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
- owning_lib INT NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + owning_lib INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, 
- creator INT NOT NULL DEFAULT 1, + creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 
- editor INT NOT NULL DEFAULT 1,+ editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  source INT,  source INT,
  create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),  create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
Line 199: Line 233:
 CREATE TABLE serial.issuance ( CREATE TABLE serial.issuance (
  id SERIAL PRIMARY KEY,  id SERIAL PRIMARY KEY,
- creator INT NOT NULL DEFAULT 1, + creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 
-        editor INT NOT NULL DEFAULT 1,+        editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
         create_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),         create_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
         edit_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),         edit_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
Line 228: Line 262:
  id SERIAL PRIMARY KEY,  id SERIAL PRIMARY KEY,
  issuance INT NOT NULL REFERENCES serial.issuance (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,  issuance INT NOT NULL REFERENCES serial.issuance (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
- creator INT NOT NULL DEFAULT 1,+ creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),  create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  pub BOOL NOT NULL DEFAULT FALSE,  pub BOOL NOT NULL DEFAULT FALSE,
Line 237: Line 271:
 CREATE TABLE serial.claim ( CREATE TABLE serial.claim (
  id SERIAL PRIMARY KEY,  id SERIAL PRIMARY KEY,
- creator INT NOT NULL DEFAULT 1, + creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 
-        editor INT NOT NULL DEFAULT 1,+        editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
         create_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),         create_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
         edit_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),         edit_date TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
Line 287: Line 321:
   - The system will then temporarily reinstantiate the last available issuance for the journal in question, predict another issuance based on this instance, then add the new issuance to the database.   - The system will then temporarily reinstantiate the last available issuance for the journal in question, predict another issuance based on this instance, then add the new issuance to the database.
 It is also desirable for the user to have the ability to 'regenerate all predictions' for any given serial. It is also desirable for the user to have the ability to 'regenerate all predictions' for any given serial.
 +
 +===== Historical info of potential interest =====
 +[[acq:serials:proto_schema_geometry|Proto-schema using geometric datatypes for issuance tracking and indexing]]
  
 ===== Status Updates ===== ===== Status Updates =====
 +  * 2010-05-17: Added historical proto-schema
   * 2010-05-03: Schema and diagram updated   * 2010-05-03: Schema and diagram updated
   * 2010-03-03: Schema proposal update, diagram added   * 2010-03-03: Schema proposal update, diagram added
   * 2009-11: Initial preview   * 2009-11: Initial preview
acq/serials/basic_predicting_and_receiving.1273763693.txt.gz · Last modified: 2022/02/10 13:33 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki

© 2008-2022 GPLS and others. Evergreen is open source software, freely licensed under GNU GPLv2 or later.
The Evergreen Project is a U.S. 501(c)3 non-profit organization.