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