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