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