removing "char" from the schema...
authormiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Mon, 26 Jun 2006 23:24:18 +0000 (23:24 +0000)
committermiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Mon, 26 Jun 2006 23:24:18 +0000 (23:24 +0000)
git-svn-id: svn://svn.open-ils.org/ILS/trunk@4794 dcc99617-32d9-48b4-a31d-7c20da2025e4

Open-ILS/src/sql/Pg/002.schema.config.sql
Open-ILS/src/sql/Pg/011.schema.authority.sql
Open-ILS/src/sql/Pg/030.schema.metabib.sql
Open-ILS/src/sql/Pg/090.schema.action.sql
Open-ILS/src/sql/Pg/900.audit-tables.sql

index f97ab38..fad7748 100644 (file)
@@ -451,7 +451,7 @@ INSERT INTO config.net_access_level (name) VALUES ('Full');
 INSERT INTO config.net_access_level (name) VALUES ('None');
 
 CREATE TABLE config.audience_map (
-       code            "char"  PRIMARY KEY,
+       code            TEXT    PRIMARY KEY,
        value           TEXT    NOT NULL,
        description     TEXT
 );
@@ -470,7 +470,7 @@ j   Juvenile        The item is intended for children and young people, approximate ages
 
 
 CREATE TABLE config.lit_form_map (
-       code            "char"  PRIMARY KEY,
+       code            TEXT    PRIMARY KEY,
        value           TEXT    NOT NULL,
        description     TEXT
 );
index cc64c08..89e87e1 100644 (file)
@@ -37,8 +37,8 @@ CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor
 CREATE TABLE authority.rec_descriptor (
        id              BIGSERIAL PRIMARY KEY,
        record          BIGINT,
-       record_status   "char",
-       char_encoding   "char"
+       record_status   TEXT,
+       char_encoding   TEXT
 );
 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
 
@@ -46,9 +46,9 @@ CREATE TABLE authority.full_rec (
        id              BIGSERIAL       PRIMARY KEY,
        record          BIGINT          NOT NULL,
        tag             CHAR(3)         NOT NULL,
-       ind1            "char",
-       ind2            "char",
-       subfield        "char",
+       ind1            TEXT,
+       ind2            TEXT,
+       subfield        TEXT,
        value           TEXT            NOT NULL,
        index_vector    tsvector        NOT NULL
 );
@@ -60,4 +60,29 @@ CREATE TRIGGER authority_full_rec_fti_trigger
 
 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
 
+CREATE OR REPLACE VIEW authority.tracing_links AS
+       SELECT  main.record AS record,
+               main.id AS main_id,
+               main.tag AS main_tag,
+               main.value AS main_value,
+               substr(link.value,1,1) AS relationship,
+               substr(link.value,2,1) AS use_restriction,
+               substr(link.value,3,1) AS deprecation,
+               substr(link.value,4,1) AS display_restriction,
+               link_value.id AS link_id,
+               link_value.tag AS link_tag,
+               link_value.value AS link_value
+         FROM  authority.full_rec main
+               JOIN authority.full_rec link
+                       ON (    link.record = main.record
+                               AND link.tag in ((main.tag::int + 400)::text, (main.tag::int + 300)::text)
+                               AND link.subfield = 'w' )
+               JOIN authority.full_rec link_value
+                       ON (    link_value.record = main.record
+                               AND link_value.tag = link.tag
+                               AND link_value.subfield = 'a' )
+         WHERE main.tag IN ('100','110','111','130','150','151','155','180','181','182','185')
+               AND main.subfield = 'a';
+
+
 COMMIT;
index 0468781..c61bcfe 100644 (file)
@@ -85,15 +85,15 @@ CREATE INDEX metabib_series_field_entry_index_vector_idx ON metabib.series_field
 CREATE TABLE metabib.rec_descriptor (
        id              BIGSERIAL PRIMARY KEY,
        record          BIGINT,
-       item_type       "char",
-       item_form       "char",
-       bib_level       "char",
-       control_type    "char",
-       char_encoding   "char",
-       enc_level       "char",
-       audience        "char",
-       lit_form        "char",
-       type_mat        "char",
+       item_type       TEXT,
+       item_form       TEXT,
+       bib_level       TEXT,
+       control_type    TEXT,
+       char_encoding   TEXT,
+       enc_level       TEXT,
+       audience        TEXT,
+       lit_form        TEXT,
+       type_mat        TEXT,
        cat_form        TEXT,
        pub_status      TEXT,
        item_lang       TEXT
@@ -119,9 +119,9 @@ CREATE TABLE metabib.full_rec (
        id              BIGSERIAL       PRIMARY KEY,
        record          BIGINT          NOT NULL,
        tag             CHAR(3)         NOT NULL,
-       ind1            "char",
-       ind2            "char",
-       subfield        "char",
+       ind1            TEXT,
+       ind2            TEXT,
+       subfield        TEXT,
        value           TEXT            NOT NULL,
        index_vector    tsvector        NOT NULL
 );
index 3a3c95f..6172646 100644 (file)
@@ -163,7 +163,7 @@ CREATE TABLE action.hold_request (
        selection_ou            INT                             NOT NULL,
        selection_depth         INT                             NOT NULL DEFAULT 0,
        pickup_lib              INT                             NOT NULL REFERENCES actor.org_unit,
-       hold_type               "char"                          NOT NULL CHECK (hold_type IN ('M','T','V','C')),
+       hold_type               TEXT                            NOT NULL CHECK (hold_type IN ('M','T','V','C')),
        holdable_formats        TEXT,
        phone_notify            TEXT,
        email_notify            BOOL                            NOT NULL DEFAULT TRUE
index 5cac260..e1c0d6c 100644 (file)
@@ -9,7 +9,7 @@ BEGIN
        EXECUTE $$
                        CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history (
                                audit_time      TIMESTAMP WITH TIME ZONE        NOT NULL,
-                               audit_action    "char"                          NOT NULL,
+                               audit_action    TEXT                            NOT NULL,
                                LIKE $$ || sch || $$.$$ || tbl || $$
                        );
        $$;