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