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_type (
  24         code    TEXT    PRIMARY KEY,
  25         label   TEXT    NOT NULL UNIQUE
  26 );
  27
  28 CREATE TABLE container.copy_bucket (
  29         id              SERIAL                          PRIMARY KEY,
  30         owner           INT                             NOT NULL
  31                                                         REFERENCES actor.usr (id)
  32                                                                 ON DELETE CASCADE
  33                                                                 ON UPDATE CASCADE
  34                                                                 DEFERRABLE
  35                                                                 INITIALLY DEFERRED,
  36         name            TEXT                            NOT NULL,
  37         btype           TEXT                            NOT NULL DEFAULT 'misc' REFERENCES container.copy_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
  38         pub             BOOL                            NOT NULL DEFAULT FALSE,
  39         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  40         CONSTRAINT cb_name_once_per_owner UNIQUE (owner,name,btype)
  41 );
  42
  43 CREATE TABLE container.copy_bucket_item (
  44         id              SERIAL  PRIMARY KEY,
  45         bucket          INT     NOT NULL
  46                                 REFERENCES container.copy_bucket (id)
  47                                         ON DELETE CASCADE
  48                                         ON UPDATE CASCADE
  49                                         DEFERRABLE
  50                                         INITIALLY DEFERRED,
  51         target_copy     INT     NOT NULL
  52                                 REFERENCES asset."copy" (id)
  53                                         ON DELETE CASCADE
  54                                         ON UPDATE CASCADE
  55                                         DEFERRABLE
  56                                         INITIALLY DEFERRED,
  57         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  58 );
  59
  60
  61
  62
  63 CREATE TABLE container.call_number_bucket_type (
  64         code    TEXT    PRIMARY KEY,
  65         label   TEXT    NOT NULL UNIQUE
  66 );
  67
  68 CREATE TABLE container.call_number_bucket (
  69         id      SERIAL  PRIMARY KEY,
  70         owner   INT     NOT NULL
  71                         REFERENCES actor.usr (id)
  72                                 ON DELETE CASCADE
  73                                 ON UPDATE CASCADE
  74                                 DEFERRABLE
  75                                 INITIALLY DEFERRED,
  76         name    TEXT    NOT NULL,
  77         btype   TEXT    NOT NULL DEFAULT 'misc' REFERENCES container.call_number_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
  78         pub     BOOL    NOT NULL DEFAULT FALSE,
  79         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  80         CONSTRAINT cnb_name_once_per_owner UNIQUE (owner,name,btype)
  81 );
  82
  83 CREATE TABLE container.call_number_bucket_item (
  84         id              SERIAL  PRIMARY KEY,
  85         bucket          INT     NOT NULL
  86                                 REFERENCES container.call_number_bucket (id)
  87                                         ON DELETE CASCADE
  88                                         ON UPDATE CASCADE
  89                                         DEFERRABLE
  90                                         INITIALLY DEFERRED,
  91         target_call_number      INT     NOT NULL
  92                                 REFERENCES asset.call_number (id)
  93                                         ON DELETE CASCADE
  94                                         ON UPDATE CASCADE
  95                                         DEFERRABLE
  96                                         INITIALLY DEFERRED,
  97         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  98 );
  99
 100
 101
 102 CREATE TABLE container.biblio_record_entry_bucket_type (
 103         code    TEXT    PRIMARY KEY,
 104         label   TEXT    NOT NULL UNIQUE
 105 );
 106
 107
 108 CREATE TABLE container.biblio_record_entry_bucket (
 109         id      SERIAL  PRIMARY KEY,
 110         owner   INT     NOT NULL
 111                         REFERENCES actor.usr (id)
 112                                 ON DELETE CASCADE
 113                                 ON UPDATE CASCADE
 114                                 DEFERRABLE
 115                                 INITIALLY DEFERRED,
 116         name    TEXT    NOT NULL,
 117         btype   TEXT    NOT NULL DEFAULT 'misc' REFERENCES container.biblio_record_entry_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
 118         pub     BOOL    NOT NULL DEFAULT FALSE,
 119         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 120         CONSTRAINT breb_name_once_per_owner UNIQUE (owner,name,btype)
 121 );
 122
 123 CREATE TABLE container.biblio_record_entry_bucket_item (
 124         id                              SERIAL  PRIMARY KEY,
 125         bucket                          INT     NOT NULL
 126                                                 REFERENCES container.biblio_record_entry_bucket (id)
 127                                                         ON DELETE CASCADE
 128                                                         ON UPDATE CASCADE
 129                                                         DEFERRABLE
 130                                                         INITIALLY DEFERRED,
 131         target_biblio_record_entry      INT     NOT NULL
 132                                                 REFERENCES biblio.record_entry (id)
 133                                                         ON DELETE CASCADE
 134                                                         ON UPDATE CASCADE
 135                                                         DEFERRABLE
 136                                                         INITIALLY DEFERRED,
 137         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 138 );
 139
 140
 141
 142 CREATE TABLE container.user_bucket_type (
 143         code    TEXT    PRIMARY KEY,
 144         label   TEXT    NOT NULL UNIQUE
 145 );
 146
 147 CREATE TABLE container.user_bucket (
 148         id      SERIAL  PRIMARY KEY,
 149         owner   INT     NOT NULL
 150                         REFERENCES actor.usr (id)
 151                                 ON DELETE CASCADE
 152                                 ON UPDATE CASCADE
 153                                 DEFERRABLE
 154                                 INITIALLY DEFERRED,
 155         name    TEXT    NOT NULL,
 156         btype   TEXT    NOT NULL DEFAULT 'misc' REFERENCES container.user_bucket_type (code) DEFERRABLE INITIALLY DEFERRED,
 157         pub     BOOL    NOT NULL DEFAULT FALSE,
 158         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 159         CONSTRAINT ub_name_once_per_owner UNIQUE (owner,name,btype)
 160 );
 161
 162 CREATE TABLE container.user_bucket_item (
 163         id              SERIAL  PRIMARY KEY,
 164         bucket          INT     NOT NULL
 165                                 REFERENCES container.user_bucket (id)
 166                                         ON DELETE CASCADE
 167                                         ON UPDATE CASCADE
 168                                         DEFERRABLE
 169                                         INITIALLY DEFERRED,
 170         target_user     INT     NOT NULL
 171                                 REFERENCES actor.usr (id)
 172                                         ON DELETE CASCADE
 173                                         ON UPDATE CASCADE
 174                                         DEFERRABLE
 175                                         INITIALLY DEFERRED,
 176         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 177 );
 178
 179 COMMIT;