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

   1 DROP SCHEMA container CASCADE;
   2
   3 BEGIN;
   4 CREATE SCHEMA container;
   5
   6 CREATE TABLE container.copy_bucket (
   7         id      SERIAL  PRIMARY KEY,
   8         owner   INT     NOT NULL
   9                         REFERENCES actor.usr (id)
  10                                 ON DELETE CASCADE
  11                                 ON UPDATE CASCADE
  12                                 DEFERRABLE
  13                                 INITIALLY DEFERRED,
  14         name    TEXT    NOT NULL,
  15         btype   TEXT    NOT NULL DEFAULT 'misc',
  16         CONSTRAINT cb_name_once_per_owner UNIQUE (owner,name,btype)
  17 );
  18
  19 CREATE TABLE container.copy_bucket_item (
  20         id              SERIAL  PRIMARY KEY,
  21         bucket          INT     NOT NULL
  22                                 REFERENCES container.copy_bucket (id)
  23                                         ON DELETE CASCADE
  24                                         ON UPDATE CASCADE
  25                                         DEFERRABLE
  26                                         INITIALLY DEFERRED,
  27         target_copy     INT     NOT NULL
  28                                 REFERENCES asset."copy" (id)
  29                                         ON DELETE CASCADE
  30                                         ON UPDATE CASCADE
  31                                         DEFERRABLE
  32                                         INITIALLY DEFERRED,
  33         CONSTRAINT cb_cb_item_once_per_bucket UNIQUE (bucket,target_copy)
  34 );
  35
  36
  37
  38
  39 CREATE TABLE container.call_number_bucket (
  40         id      SERIAL  PRIMARY KEY,
  41         owner   INT     NOT NULL
  42                         REFERENCES actor.usr (id)
  43                                 ON DELETE CASCADE
  44                                 ON UPDATE CASCADE
  45                                 DEFERRABLE
  46                                 INITIALLY DEFERRED,
  47         name    TEXT    NOT NULL,
  48         btype   TEXT    NOT NULL DEFAULT 'misc',
  49         CONSTRAINT cnb_name_once_per_owner UNIQUE (owner,name,btype)
  50 );
  51
  52 CREATE TABLE container.call_number_bucket_item (
  53         id              SERIAL  PRIMARY KEY,
  54         bucket          INT     NOT NULL
  55                                 REFERENCES container.call_number_bucket (id)
  56                                         ON DELETE CASCADE
  57                                         ON UPDATE CASCADE
  58                                         DEFERRABLE
  59                                         INITIALLY DEFERRED,
  60         target_call_number      INT     NOT NULL
  61                                 REFERENCES asset.call_number (id)
  62                                         ON DELETE CASCADE
  63                                         ON UPDATE CASCADE
  64                                         DEFERRABLE
  65                                         INITIALLY DEFERRED,
  66         CONSTRAINT cnb_item_once_per_bucket UNIQUE (bucket,target_call_number)
  67 );
  68
  69
  70
  71
  72 CREATE TABLE container.biblio_record_entry_bucket (
  73         id      SERIAL  PRIMARY KEY,
  74         owner   INT     NOT NULL
  75                         REFERENCES actor.usr (id)
  76                                 ON DELETE CASCADE
  77                                 ON UPDATE CASCADE
  78                                 DEFERRABLE
  79                                 INITIALLY DEFERRED,
  80         name    TEXT    NOT NULL,
  81         btype   TEXT    NOT NULL DEFAULT 'misc',
  82         CONSTRAINT breb_name_once_per_owner UNIQUE (owner,name,btype)
  83 );
  84
  85 CREATE TABLE container.biblio_record_entry_bucket_item (
  86         id                              SERIAL  PRIMARY KEY,
  87         bucket                          INT     NOT NULL
  88                                                 REFERENCES container.biblio_record_entry_bucket (id)
  89                                                         ON DELETE CASCADE
  90                                                         ON UPDATE CASCADE
  91                                                         DEFERRABLE
  92                                                         INITIALLY DEFERRED,
  93         target_biblio_record_entry      INT     NOT NULL
  94                                                 REFERENCES biblio.record_entry (id)
  95                                                         ON DELETE CASCADE
  96                                                         ON UPDATE CASCADE
  97                                                         DEFERRABLE
  98                                                         INITIALLY DEFERRED,
  99         CONSTRAINT breb_item_once_per_bucket UNIQUE (bucket,target_biblio_record_entry)
 100 );
 101
 102
 103
 104
 105 CREATE TABLE container.user_bucket (
 106         id      SERIAL  PRIMARY KEY,
 107         owner   INT     NOT NULL
 108                         REFERENCES actor.usr (id)
 109                                 ON DELETE CASCADE
 110                                 ON UPDATE CASCADE
 111                                 DEFERRABLE
 112                                 INITIALLY DEFERRED,
 113         name    TEXT    NOT NULL,
 114         btype   TEXT    NOT NULL DEFAULT 'misc',
 115         CONSTRAINT ub_name_once_per_owner UNIQUE (owner,name,btype)
 116 );
 117
 118 CREATE TABLE container.user_bucket_item (
 119         id              SERIAL  PRIMARY KEY,
 120         bucket          INT     NOT NULL
 121                                 REFERENCES container.user_bucket (id)
 122                                         ON DELETE CASCADE
 123                                         ON UPDATE CASCADE
 124                                         DEFERRABLE
 125                                         INITIALLY DEFERRED,
 126         target_user     INT     NOT NULL
 127                                 REFERENCES actor.usr (id)
 128                                         ON DELETE CASCADE
 129                                         ON UPDATE CASCADE
 130                                         DEFERRABLE
 131                                         INITIALLY DEFERRED,
 132         CONSTRAINT ub_item_once_per_bucket UNIQUE (bucket,target_user)
 133 );
 134
 135 COMMIT;