"Fossies" - the Fresh Open Source Software Archive

Member "absence-v2.1/db/create_tables.sql" (10 Jun 2012, 8183 Bytes) of package /linux/www/web-absence-2.1.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) PL/SQL source code syntax highlighting (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file.

    1 /*************************************************************************
    2  ** SQL script to create all tables, views and functions used by
    3  ** absence.
    4  **
    5  ** $Id: create_tables.sql 93 2009-07-14 23:40:07Z urban $
    6  *************************************************************************/
    7 
    8 /********************
    9  ** GROUP
   10  ********************/
   11 /*
   12 drop table c_group;
   13 drop sequence c_group_id_seq;
   14 */
   15 CREATE TABLE c_group
   16 (
   17     id              SERIAL NOT NULL PRIMARY KEY,
   18     name            VARCHAR(64) NOT NULL,
   19     password        VARCHAR(64),
   20     description     VARCHAR(256),
   21     invalidated     TIMESTAMP WITH TIME ZONE,
   22     UNIQUE ( name )
   23 );
   24 
   25 CREATE VIEW v_group AS SELECT * FROM c_group WHERE invalidated IS NULL;
   26 
   27 /********************
   28  ** COUNTRY
   29  ********************/
   30 CREATE TABLE c_country
   31 (
   32     id              SERIAL NOT NULL PRIMARY KEY,
   33     code            VARCHAR(2),     /* ISO 2-letter country-code */
   34     name            VARCHAR(64)     /* long name */
   35 );
   36 
   37 /********************
   38  ** REGION
   39  ** a region is a part of a country, where has legal holidays that
   40  ** are valid only for that region
   41  ********************/
   42 CREATE TABLE c_region
   43 (
   44     id              SERIAL NOT NULL PRIMARY KEY,
   45     name            VARCHAR(64),            /* name of region */
   46     invalidated     TIMESTAMP WITH TIME ZONE,
   47     description     VARCHAR(256)
   48 );
   49 
   50 CREATE VIEW v_region AS SELECT * FROM c_region WHERE invalidated IS NULL;
   51 
   52 /********************
   53  ** OBJECT (usually people)
   54  ********************/
   55 /*
   56 drop table object;
   57 drop sequence object_id_seq;
   58 */
   59 CREATE TABLE c_object
   60 (
   61     id              SERIAL NOT NULL PRIMARY KEY,
   62     name            VARCHAR(64),
   63     email           VARCHAR(64),
   64     description     VARCHAR(256),
   65     invalidated     TIMESTAMP WITH TIME ZONE,
   66     country_id      INTEGER REFERENCES c_country(id),
   67     region_id       INTEGER REFERENCES c_region(id),
   68     UNIQUE ( name )
   69 );
   70 
   71 CREATE VIEW v_object AS SELECT * FROM c_object WHERE invalidated IS NULL;
   72 
   73 /********************
   74  ** GROUP_OBJECT
   75  ** links objects (people) to groups
   76  ********************/
   77 CREATE TABLE c_group_object
   78 (
   79     id              SERIAL NOT NULL PRIMARY KEY,
   80     group_id        INTEGER NOT NULL REFERENCES c_group(id),
   81     object_id       INTEGER NOT NULL REFERENCES c_object(id),
   82     UNIQUE ( group_id, object_id )
   83 );
   84 
   85 CREATE INDEX c_group_object_group_id_idx on c_group_object(group_id);
   86 CREATE INDEX c_group_object_object_id_idx on c_group_object(object_id);
   87 
   88 /********************
   89  ** USER
   90  ********************/
   91 /*
   92 drop table c_user;
   93 drop sequence c_user_id_seq;
   94 */
   95 CREATE TABLE c_user
   96 (
   97     id              SERIAL NOT NULL PRIMARY KEY,
   98     object_id       INTEGER REFERENCES c_object(id),
   99     username        VARCHAR(32),
  100     password        VARCHAR(64),
  101     pacl            VARCHAR(256),
  102     gacl            VARCHAR(256),
  103     invalidated     TIMESTAMP WITH TIME ZONE,
  104     UNIQUE ( username )
  105 );
  106 
  107 CREATE VIEW v_user AS SELECT * FROM c_user WHERE invalidated IS NULL;
  108 
  109 CREATE INDEX c_user_username_idx on c_user(username);
  110 
  111 /********************
  112  ** ACCESS
  113  **
  114  ** provides access-control when using authorization
  115  ** 'level' is an integer with following permitted values
  116  **     1 - read
  117  **     2 - write
  118  **     4 - admin
  119  **
  120  ** additionally, a group_id of zero implies *all* groups
  121  ** %% UNUSED CURRENTLY! %%
  122  ********************/
  123 CREATE TABLE c_access
  124 (
  125     id              SERIAL NOT NULL PRIMARY KEY,
  126     user_id         INTEGER NOT NULL REFERENCES c_user(id),
  127     ref_id          INTEGER,
  128     type            VARCHAR(10) NOT NULL
  129         CHECK (type = 'object' OR type = 'group'),
  130     magic           VARCHAR(10)
  131         CHECK (magic = 'self' OR magic = 'all'),
  132     level           INTEGER NOT NULL
  133 );
  134 
  135 CREATE INDEX c_access_user_id_idx on c_access(user_id);
  136 
  137 /********************
  138  ** RESERVATION_TYPE
  139  ********************/
  140 /*
  141 drop table c_reservation_type;
  142 drop sequence c_reservation_type_id_seq;
  143 */
  144 CREATE TABLE c_reservation_type
  145 (
  146     id              SERIAL NOT NULL PRIMARY KEY,
  147     name            VARCHAR(64) NOT NULL,   /* label to be displayed    */
  148     height          VARCHAR(16)         /* for overlapping reservations */
  149         CHECK (height IN ('block', 'full', 'half', 'quarter')),
  150     priority        INTEGER,            /* position within person-bar   */
  151     overlap_group   INTEGER,            /* determines which can overlap */
  152     color_red       INTEGER,            /* 0-255 */
  153     color_green     INTEGER,            /* 0-255 */
  154     color_blue      INTEGER,            /* 0-255 */
  155     transparency    INTEGER,            /* 0-127 */
  156     default_type    BOOLEAN,            /* only one can be marked default */
  157     skip_non_workdays   BOOLEAN,        /* do not draw reservations on we/hol */
  158     description     VARCHAR(256),
  159     invalidated     TIMESTAMP WITH TIME ZONE,
  160     UNIQUE ( name )
  161 );
  162 
  163 CREATE VIEW v_reservation_type AS SELECT * FROM c_reservation_type WHERE invalidated IS NULL;
  164 
  165 /********************
  166  ** NO_OVERLAP
  167  ** designates which types CANNOT overlap
  168  ********************/
  169 
  170 CREATE TABLE c_no_coincidence
  171 (
  172     id              SERIAL NOT NULL PRIMARY KEY,
  173     type_id1        INTEGER NOT NULL REFERENCES c_reservation_type(id),
  174     type_id2        INTEGER NOT NULL REFERENCES c_reservation_type(id)
  175 );
  176 
  177 /********************
  178  ** RESERVATION
  179  ********************/
  180 /*
  181 drop table d_reservation;
  182 drop sequence d_reservation_id_seq;
  183 */
  184 CREATE TABLE d_reservation
  185 (
  186     id              SERIAL NOT NULL PRIMARY KEY,
  187     object_id       INTEGER NOT NULL REFERENCES c_object(id),
  188     type_id         INTEGER NOT NULL REFERENCES c_reservation_type(id),
  189     start           DATE,
  190     finish          DATE,
  191     description     VARCHAR(256),
  192     misc            VARCHAR(256),
  193     invalidated     TIMESTAMP WITH TIME ZONE
  194 );
  195 
  196 CREATE VIEW v_reservation AS SELECT * FROM d_reservation WHERE invalidated IS NULL;
  197 
  198 CREATE INDEX d_reservation_start_idx on d_reservation(start);
  199 CREATE INDEX d_reservation_finish on d_reservation(finish);
  200 CREATE INDEX d_reservation_object_id on d_reservation(object_id);
  201 
  202 /********************
  203  ** GROUP_MOD_TIME
  204  ** epoch-time when group was last modified for specified month/year
  205  ** i.e., ist group-image still up to date?
  206  ********************/
  207 CREATE TABLE d_group_mod_time
  208 (
  209     id              SERIAL NOT NULL PRIMARY KEY,
  210     group_id        INTEGER NOT NULL REFERENCES c_group(id),
  211     month           INTEGER,
  212     year            INTEGER,
  213     mod_time        INTEGER,
  214     image_height    INTEGER,
  215     image_width     INTEGER,
  216     UNIQUE ( group_id, month, year )
  217 );
  218 CREATE INDEX d_group_mod_time_group_id_idx on d_group_mod_time(group_id);
  219 CREATE INDEX d_group_mod_time_month_idx on d_group_mod_time(month);
  220 CREATE INDEX d_group_mod_time_year_idx on d_group_mod_time(year);
  221 
  222 /********************
  223  ** CONFIG
  224  ********************/
  225 CREATE TABLE c_config
  226 (
  227     id              SERIAL NOT NULL PRIMARY KEY,
  228     name            VARCHAR(64),
  229     content         VARCHAR(128)
  230     /*
  231     value_type      INTEGER,
  232     int_value       INTEGER,
  233     str_value       VARCHAR(256),
  234     */
  235 );
  236 
  237 CREATE INDEX c_config_name_idx on c_config(name);
  238 
  239 /********************
  240  ** HOLIDAY
  241  ********************/
  242 CREATE TABLE c_holiday
  243 (
  244     id              SERIAL NOT NULL PRIMARY KEY,
  245     country_id      INTEGER REFERENCES c_country(id),
  246     region_id       INTEGER REFERENCES c_region(id),
  247     day             DATE,
  248     description     VARCHAR(256)
  249 );
  250 
  251 CREATE INDEX c_holiday_day_idx on c_holiday(day);
  252 
  253 /********************
  254  ** SESSION
  255  ********************/
  256 CREATE TABLE d_session
  257 (
  258     id              SERIAL NOT NULL PRIMARY KEY,
  259     user_id         INTEGER NOT NULL REFERENCES c_user(id),
  260     ip_addr         INET,
  261     t_stamp         TIMESTAMP WITH TIME ZONE
  262 );
  263 
  264 /********************
  265  ** RESERVATION-REGION
  266  ********************/
  267 CREATE TABLE d_reservation_region
  268 (
  269     id              SERIAL NOT NULL PRIMARY KEY,
  270     reservation_id  INTEGER NOT NULL REFERENCES d_reservation(id),
  271     priority        INTEGER,
  272     x1              INTEGER,
  273     y1              INTEGER,
  274     x2              INTEGER,
  275     y2              INTEGER
  276 );
  277 CREATE INDEX d_reservation_region_x1_idx on d_reservation_region(x1);
  278 CREATE INDEX d_reservation_region_y1_idx on d_reservation_region(y1);
  279 CREATE INDEX d_reservation_region_x2_idx on d_reservation_region(x2);
  280 CREATE INDEX d_reservation_region_y2_idx on d_reservation_region(y2);
  281 
  282 
  283 /********************
  284  ** SESSIONS (for CGI::Session)
  285  ********************/
  286 CREATE TABLE sessions (
  287     id              CHAR(32) NOT NULL PRIMARY KEY,
  288     a_session       BYTEA NOT NULL
  289 );
  290 
  291 /*=====================================================================
  292   = functions
  293   =====================================================================*/
  294 
  295 /* convert a unix epoch-time to postgres timestamp datatype */
  296 create function int2tsz(INTEGER) returns TIMESTAMP WITH TIME ZONE as '
  297 SELECT ( TIMESTAMP WITH TIME ZONE ''epoch'' + $1 * INTERVAL ''1
  298 second'')::TIMESTAMP WITH TIME ZONE;
  299 ' language sql;
  300 
  301 /* convert a postgres timestamp datatype to unix epoch-time */
  302 create function tsz2int(TIMESTAMP WITH TIME ZONE) returns int as '
  303 select extract( ''epoch'' from $1)::INTEGER;
  304 ' language sql;