Open-ILS/src/sql/Pg/070.schema.container.sql

   1 /*
   2  * Copyright (C) 2004-2008  Georgia Public Library Service
   3  * Copyright (C) 2007-2008  Equinox Software, Inc.
   4  * Mike Rylander <miker@esilibrary.com>
   5  *
   6  * This program is free software; you can redistribute it and/or
   7  * modify it under the terms of the GNU General Public License
   8  * as published by the Free Software Foundation; either version 2
   9  * of the License, or (at your option) any later version.
  10  *
  11  * This program is distributed in the hope that it will be useful,
  12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14  * GNU General Public License for more details.
  15  *
  16  */
  17
  18 DROP SCHEMA container CASCADE;
  19
  20 BEGIN;
  21 CREATE SCHEMA container;
  22
  23 CREATE TABLE container.copy_bucket (
  24         id              SERIAL                          PRIMARY KEY,
  25         owner           INT                             NOT NULL
  26                                                         REFERENCES actor.usr (id)
  27                                                                 ON DELETE CASCADE
  28                                                                 ON UPDATE CASCADE
  29                                                                 DEFERRABLE
  30                                                                 INITIALLY DEFERRED,
  31         name            TEXT                            NOT NULL,
  32         btype           TEXT                            NOT NULL DEFAULT 'misc',
  33         pub             BOOL                            NOT NULL DEFAULT FALSE,
  34         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  35         CONSTRAINT cb_name_once_per_owner UNIQUE (owner,name,btype)
  36 );
  37
  38 CREATE TABLE container.copy_bucket_item (
  39         id              SERIAL  PRIMARY KEY,
  40         bucket          INT     NOT NULL
  41                                 REFERENCES container.copy_bucket (id)
  42                                         ON DELETE CASCADE
  43                                         ON UPDATE CASCADE
  44                                         DEFERRABLE
  45                                         INITIALLY DEFERRED,
  46         target_copy     INT     NOT NULL
  47                                 REFERENCES asset."copy" (id)
  48                                         ON DELETE CASCADE
  49                                         ON UPDATE CASCADE
  50                                         DEFERRABLE
  51                                         INITIALLY DEFERRED,
  52         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  53 );
  54
  55
  56
  57
  58 CREATE TABLE container.call_number_bucket (
  59         id      SERIAL  PRIMARY KEY,
  60         owner   INT     NOT NULL
  61                         REFERENCES actor.usr (id)
  62                                 ON DELETE CASCADE
  63                                 ON UPDATE CASCADE
  64                                 DEFERRABLE
  65                                 INITIALLY DEFERRED,
  66         name    TEXT    NOT NULL,
  67         btype   TEXT    NOT NULL DEFAULT 'misc',
  68         pub     BOOL    NOT NULL DEFAULT FALSE,
  69         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  70         CONSTRAINT cnb_name_once_per_owner UNIQUE (owner,name,btype)
  71 );
  72
  73 CREATE TABLE container.call_number_bucket_item (
  74         id              SERIAL  PRIMARY KEY,
  75         bucket          INT     NOT NULL
  76                                 REFERENCES container.call_number_bucket (id)
  77                                         ON DELETE CASCADE
  78                                         ON UPDATE CASCADE
  79                                         DEFERRABLE
  80                                         INITIALLY DEFERRED,
  81         target_call_number      INT     NOT NULL
  82                                 REFERENCES asset.call_number (id)
  83                                         ON DELETE CASCADE
  84                                         ON UPDATE CASCADE
  85                                         DEFERRABLE
  86                                         INITIALLY DEFERRED,
  87         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  88 );
  89
  90
  91
  92
  93 CREATE TABLE container.biblio_record_entry_bucket (
  94         id      SERIAL  PRIMARY KEY,
  95         owner   INT     NOT NULL
  96                         REFERENCES actor.usr (id)
  97                                 ON DELETE CASCADE
  98                                 ON UPDATE CASCADE
  99                                 DEFERRABLE
 100                                 INITIALLY DEFERRED,
 101         name    TEXT    NOT NULL,
 102         btype   TEXT    NOT NULL DEFAULT 'misc',
 103         pub     BOOL    NOT NULL DEFAULT FALSE,
 104         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 105         CONSTRAINT breb_name_once_per_owner UNIQUE (owner,name,btype)
 106 );
 107
 108 CREATE TABLE container.biblio_record_entry_bucket_item (
 109         id                              SERIAL  PRIMARY KEY,
 110         bucket                          INT     NOT NULL
 111                                                 REFERENCES container.biblio_record_entry_bucket (id)
 112                                                         ON DELETE CASCADE
 113                                                         ON UPDATE CASCADE
 114                                                         DEFERRABLE
 115                                                         INITIALLY DEFERRED,
 116         target_biblio_record_entry      INT     NOT NULL
 117                                                 REFERENCES biblio.record_entry (id)
 118                                                         ON DELETE CASCADE
 119                                                         ON UPDATE CASCADE
 120                                                         DEFERRABLE
 121                                                         INITIALLY DEFERRED,
 122         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 123 );
 124
 125
 126
 127
 128 CREATE TABLE container.user_bucket (
 129         id      SERIAL  PRIMARY KEY,
 130         owner   INT     NOT NULL
 131                         REFERENCES actor.usr (id)
 132                                 ON DELETE CASCADE
 133                                 ON UPDATE CASCADE
 134                                 DEFERRABLE
 135                                 INITIALLY DEFERRED,
 136         name    TEXT    NOT NULL,
 137         btype   TEXT    NOT NULL DEFAULT 'misc',
 138         pub     BOOL    NOT NULL DEFAULT FALSE,
 139         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 140         CONSTRAINT ub_name_once_per_owner UNIQUE (owner,name,btype)
 141 );
 142
 143 CREATE TABLE container.user_bucket_item (
 144         id              SERIAL  PRIMARY KEY,
 145         bucket          INT     NOT NULL
 146                                 REFERENCES container.user_bucket (id)
 147                                         ON DELETE CASCADE
 148                                         ON UPDATE CASCADE
 149                                         DEFERRABLE
 150                                         INITIALLY DEFERRED,
 151         target_user     INT     NOT NULL
 152                                 REFERENCES actor.usr (id)
 153                                         ON DELETE CASCADE
 154                                         ON UPDATE CASCADE
 155                                         DEFERRABLE
 156                                         INITIALLY DEFERRED,
 157         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 158 );
 159
 160 COMMIT;