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 );
  37
  38
  39
  40
  41 CREATE TABLE container.call_number_bucket (
  42         id      SERIAL  PRIMARY KEY,
  43         owner   INT     NOT NULL
  44                         REFERENCES actor.usr (id)
  45                                 ON DELETE CASCADE
  46                                 ON UPDATE CASCADE
  47                                 DEFERRABLE
  48                                 INITIALLY DEFERRED,
  49         name    TEXT    NOT NULL,
  50         btype   TEXT    NOT NULL DEFAULT 'misc',
  51         pub     BOOL    NOT NULL DEFAULT FALSE,
  52         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  53         CONSTRAINT cnb_name_once_per_owner UNIQUE (owner,name,btype)
  54 );
  55
  56 CREATE TABLE container.call_number_bucket_item (
  57         id              SERIAL  PRIMARY KEY,
  58         bucket          INT     NOT NULL
  59                                 REFERENCES container.call_number_bucket (id)
  60                                         ON DELETE CASCADE
  61                                         ON UPDATE CASCADE
  62                                         DEFERRABLE
  63                                         INITIALLY DEFERRED,
  64         target_call_number      INT     NOT NULL
  65                                 REFERENCES asset.call_number (id)
  66                                         ON DELETE CASCADE
  67                                         ON UPDATE CASCADE
  68                                         DEFERRABLE
  69                                         INITIALLY DEFERRED,
  70         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
  71 );
  72
  73
  74
  75
  76 CREATE TABLE container.biblio_record_entry_bucket (
  77         id      SERIAL  PRIMARY KEY,
  78         owner   INT     NOT NULL
  79                         REFERENCES actor.usr (id)
  80                                 ON DELETE CASCADE
  81                                 ON UPDATE CASCADE
  82                                 DEFERRABLE
  83                                 INITIALLY DEFERRED,
  84         name    TEXT    NOT NULL,
  85         btype   TEXT    NOT NULL DEFAULT 'misc',
  86         pub     BOOL    NOT NULL DEFAULT FALSE,
  87         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  88         CONSTRAINT breb_name_once_per_owner UNIQUE (owner,name,btype)
  89 );
  90
  91 CREATE TABLE container.biblio_record_entry_bucket_item (
  92         id                              SERIAL  PRIMARY KEY,
  93         bucket                          INT     NOT NULL
  94                                                 REFERENCES container.biblio_record_entry_bucket (id)
  95                                                         ON DELETE CASCADE
  96                                                         ON UPDATE CASCADE
  97                                                         DEFERRABLE
  98                                                         INITIALLY DEFERRED,
  99         target_biblio_record_entry      INT     NOT NULL
 100                                                 REFERENCES biblio.record_entry (id)
 101                                                         ON DELETE CASCADE
 102                                                         ON UPDATE CASCADE
 103                                                         DEFERRABLE
 104                                                         INITIALLY DEFERRED,
 105         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 106 );
 107
 108
 109
 110
 111 CREATE TABLE container.user_bucket (
 112         id      SERIAL  PRIMARY KEY,
 113         owner   INT     NOT NULL
 114                         REFERENCES actor.usr (id)
 115                                 ON DELETE CASCADE
 116                                 ON UPDATE CASCADE
 117                                 DEFERRABLE
 118                                 INITIALLY DEFERRED,
 119         name    TEXT    NOT NULL,
 120         btype   TEXT    NOT NULL DEFAULT 'misc',
 121         pub     BOOL    NOT NULL DEFAULT FALSE,
 122         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
 123         CONSTRAINT ub_name_once_per_owner UNIQUE (owner,name,btype)
 124 );
 125
 126 CREATE TABLE container.user_bucket_item (
 127         id              SERIAL  PRIMARY KEY,
 128         bucket          INT     NOT NULL
 129                                 REFERENCES container.user_bucket (id)
 130                                         ON DELETE CASCADE
 131                                         ON UPDATE CASCADE
 132                                         DEFERRABLE
 133                                         INITIALLY DEFERRED,
 134         target_user     INT     NOT NULL
 135                                 REFERENCES actor.usr (id)
 136                                         ON DELETE CASCADE
 137                                         ON UPDATE CASCADE
 138                                         DEFERRABLE
 139                                         INITIALLY DEFERRED,
 140         create_time     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW()
 141 );
 142
 143 COMMIT;