head 1.33;
access;
symbols
AS_AFTER_RESTRUCTURING:1.17;
locks; strict;
comment @# @;
1.33
date 2003.05.06.15.16.34; author rse; state Exp;
branches;
next 1.32;
1.32
date 2003.05.05.11.56.18; author rse; state Exp;
branches;
next 1.31;
1.31
date 2003.05.05.11.16.08; author rse; state Exp;
branches;
next 1.30;
1.30
date 2003.05.05.08.36.31; author rse; state Exp;
branches;
next 1.29;
1.29
date 2003.04.26.20.27.46; author rse; state Exp;
branches;
next 1.28;
1.28
date 2003.04.26.18.47.54; author rse; state Exp;
branches;
next 1.27;
1.27
date 2003.04.24.11.17.16; author rse; state Exp;
branches;
next 1.26;
1.26
date 2003.04.23.09.04.07; author rse; state Exp;
branches;
next 1.25;
1.25
date 2003.03.26.15.49.16; author rse; state Exp;
branches;
next 1.24;
1.24
date 2003.03.07.20.30.45; author rse; state Exp;
branches;
next 1.23;
1.23
date 2003.03.06.20.09.57; author rse; state Exp;
branches;
next 1.22;
1.22
date 2003.03.06.19.53.34; author rse; state Exp;
branches;
next 1.21;
1.21
date 2003.03.06.19.34.25; author rse; state Exp;
branches;
next 1.20;
1.20
date 2003.03.06.19.13.31; author rse; state Exp;
branches;
next 1.19;
1.19
date 2003.03.06.13.15.33; author rse; state Exp;
branches;
next 1.18;
1.18
date 2003.03.06.13.11.41; author rse; state Exp;
branches;
next 1.17;
1.17
date 2002.10.28.15.21.23; author rse; state Exp;
branches;
next 1.16;
1.16
date 2002.10.28.15.06.14; author rse; state Exp;
branches;
next 1.15;
1.15
date 2002.10.28.10.20.26; author rse; state Exp;
branches;
next 1.14;
1.14
date 2002.10.05.16.10.15; author rse; state Exp;
branches;
next 1.13;
1.13
date 2002.10.02.19.00.59; author rse; state Exp;
branches;
next 1.12;
1.12
date 2002.10.02.18.01.04; author rse; state Exp;
branches;
next 1.11;
1.11
date 2002.10.02.14.18.16; author rse; state Exp;
branches;
next 1.10;
1.10
date 2002.10.02.13.35.45; author rse; state Exp;
branches;
next 1.9;
1.9
date 2002.10.02.13.16.59; author rse; state Exp;
branches;
next 1.8;
1.8
date 2002.10.02.13.12.26; author rse; state Exp;
branches;
next 1.7;
1.7
date 2002.10.01.16.06.30; author rse; state Exp;
branches;
next 1.6;
1.6
date 2002.10.01.10.03.44; author rse; state Exp;
branches;
next 1.5;
1.5
date 2002.09.30.15.21.22; author rse; state Exp;
branches;
next 1.4;
1.4
date 2002.09.29.10.11.04; author rse; state Exp;
branches;
next 1.3;
1.3
date 2002.09.29.09.31.18; author rse; state Exp;
branches;
next 1.2;
1.2
date 2002.09.25.15.12.30; author rse; state Exp;
branches;
next 1.1;
1.1
date 2002.09.17.19.34.42; author rse; state Exp;
branches;
next ;
desc
@@
1.33
log
@flush pending changes from today's schema-full-work-off
@
text
@--
-- AS -- Accounting System
-- Copyright (c) 2002-2003 Cable & Wireless Deutschland
-- Copyright (c) 2002-2003 Ralf S. Engelschall
--
-- This file is part of AS, an accounting system which can be
-- found at http://as.is.eu.cw.com/
--
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License
-- as published by the Free Software Foundation; either version
-- 2.0 of the License, or (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
-- USA, or contact The OSSP Project .
--
-- as_db.sql -- Accounting System Database Definition
--
--
-- Notice: Although this is mostly SQL/92, it is intended
-- for use with the PostgreSQL RDBMS only.
--
-- Notice: SQL comments below are converted into SQL
-- "COMMENT ON" statements on-the-fly, so make sure
-- that they are properly aligned.
--
-- System Global Configuration
CREATE TABLE as_config (
cf_var TEXT
NOT NULL
CHECK(cf_var ~ '^(version|created)$'),
-- configuration variable name
-- [version] (rw)
cf_val TEXT
NOT NULL
-- configuration variable value
-- [0.9.0] (rw)
);
-- System Global Object Identifiers
CREATE TABLE as_oid (
id_oid BIGSERIAL
UNIQUE NOT NULL
PRIMARY KEY,
-- unique identifier of object
-- [42] (ro)
id_type VARCHAR(20)
NOT NULL
CHECK(id_type ~ '^(locality|holiday|user|group|account)$'),
-- type of object (means: references as_._oid)
-- [user] (rw)
id_freeze TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT '-infinity'
-- object is frozen before (exclusive) this time
-- [2002-01-01 00:00:00] (rw)
);
-- System Global Epoch Sequence
CREATE TABLE as_epoch (
ep_epoch BIGSERIAL
UNIQUE NOT NULL
PRIMARY KEY,
-- unique system epoch number
-- [42] (ro)
ep_begin TIMESTAMP (4) WITH TIME ZONE
NOT NULL
-- begin of epoch in world time
-- [2002-01-01 00:00:00.0000] (rw)
);
-- System Localities
CREATE TABLE as_locality (
lo_oid BIGINT
UNIQUE NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE
PRIMARY KEY,
-- unique identifier of locality
-- [1] (ro)
lo_name VARCHAR(80)
NOT NULL,
-- name of locality
-- [Germany/Bavaria] (re)
lo_timezone INTEGER
NOT NULL
CHECK(lo_timezone >= -12 AND lo_timezone <= +12),
-- timezone offset of locality
-- [2] (re)
lo_valid_beg TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'now',
-- begin real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
lo_valid_end TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'infinity',
-- end real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
lo_epoch_beg BIGINT
NOT NULL
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE
-- begin system epoch of record creation (inclusive)
-- [2] (ro)
);
-- System Locality Holidays
CREATE TABLE as_holiday (
hd_oid BIGINT
UNIQUE NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE
PRIMARY KEY,
-- unique identifier of holiday
-- [1] (ro)
hd_date DATE
NOT NULL,
-- date of locality holiday
-- [2002-10-02] (re)
hd_valid_beg TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'now',
-- begin real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
hd_valid_end TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'infinity',
-- end real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
hd_epoch_beg BIGINT
NOT NULL
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE
-- begin system epoch of record creation (inclusive)
-- [2] (ro)
);
-- System Users
CREATE TABLE as_user (
us_oid BIGINT
UNIQUE NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE
PRIMARY KEY,
-- globally unique object identifier
-- [42] (ro)
us_name VARCHAR(30)
NOT NULL
CHECK(us_name ~ '^[a-zA-Z][a-zA-Z0-9_-]+$'),
-- system name
-- [rse] (re)
us_realname VARCHAR(60)
NOT NULL,
-- real name
-- [Ralf S. Engelschall] (re)
us_password VARCHAR(30)
NOT NULL
CHECK(us_password ~ '^$1$.+'),
-- system password (MD5-based crypt format)
-- [$1$xxx...] (re)
us_email VARCHAR(60)
NOT NULL
CHECK(us_email ~ '^.+@@[^.]+(\.[^.]+)+$'),
-- email address
-- [rse@@de.cw.com] (re)
us_home VARCHAR(128),
CHECK(us_home ~ '^|(https?://.+)$'),
-- homepage URL
-- [http://dev.de.cw.net/~rse/] (re)
us_workhours INTEGER
NOT NULL
CHECK(us_workhours > 0 AND us_workhours <= 16),
-- contract-based daily working hours
-- [8] (re)
us_workdays INTEGER
NOT NULL
CHECK(us_workdays > 0 AND us_workdays <= 7),
-- contract-based weekly working days
-- [5] (re)
us_notify BOOLEAN
NOT NULL
DEFAULT 'true',
-- whether user is notified about outstanding accounting
-- [true] (re)
us_notify_min INTEGER
NOT NULL
DEFAULT '40'
CHECK(us_notify_min >= 0 AND us_notify_min <= 100),
-- percent of covered minimum workhours for notification
-- [40] (re)
us_notify_after INTEGER
NOT NULL
DEFAULT '24'
CHECK(us_notify_after >= 0),
-- hours after end of day for which user is notified
-- [12] (re)
us_enabled BOOLEAN
NOT NULL
DEFAULT 'true',
-- whether user is enabled or not
-- [true] (re)
us_valid_beg TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'now',
-- begin real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
us_valid_end TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'infinity',
-- end real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
us_epoch_beg BIGINT
NOT NULL
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE
-- begin system epoch of record creation (inclusive)
-- [2] (ro)
);
-- System Groups
CREATE TABLE as_group (
gr_oid BIGINT
UNIQUE NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE
PRIMARY KEY,
-- globally unique object identifier
-- [66] (ro)
gr_name VARCHAR(30),
-- system name of group
-- [dev] (re)
gr_realname VARCHAR(60),
-- real name of group
-- [Development Team] (re)
gr_enabled BOOLEAN
NOT NULL
DEFAULT 'true',
-- whether group is enabled or not
-- [true] (re)
gr_valid_beg TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'now',
-- begin real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
gr_valid_end TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'infinity',
-- end real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
gr_epoch_beg BIGINT
NOT NULL
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE
-- begin system epoch of record creation (inclusive)
-- [2] (ro)
);
-- Available Accounts
CREATE TABLE as_account (
ac_oid BIGINT
UNIQUE NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE
PRIMARY KEY,
-- globally unique object identifier
-- [7] (ro)
ac_name VARCHAR(30)
NOT NULL,
-- name of account (locally unique below parent only)
-- [pmod] (re)
ac_description VARCHAR(80)
DEFAULT '',
-- short description of account
-- [OpenPKG PMOD/PSOD Tasks] (re)
ac_enabled BOOLEAN
NOT NULL
DEFAULT 'true',
-- whether account is enabled or not
-- [true] (re)
ac_abstract BOOLEAN
NOT NULL
DEFAULT 'true',
-- whether account exists for structuring reasons only
-- [true] (re)
ac_type INTEGER
NOT NULL
DEFAULT 0
CHECK(ac_type >= 0 AND ac_type <= 2),
-- type of account: 0=absence 1=internal?! 2=earning
-- [0] (re)
ac_valid_beg TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'now',
-- begin real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
ac_valid_end TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'infinity',
-- end real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
ac_epoch_beg BIGINT
NOT NULL
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE
-- begin system epoch of record creation (inclusive)
-- [2] (ro)
);
-- Accounting Events
CREATE TABLE as_event (
ev_uuid CHAR(36)
PRIMARY KEY,
-- ISO-11578 Universally Unique Identifier (UUID)
-- [f81d4fae-7dec-11d0-a765-00a0c91e6bf6] (rw)
ev_user BIGINT
NOT NULL
REFERENCES as_user (us_oid)
MATCH FULL DEFERRABLE,
-- reference to user submitting the event
-- [42] (rw)
ev_date DATE
NOT NULL,
-- date of accounting
-- [2002-10-01] (rw)
ev_time_begin TIME (0) WITH TIME ZONE
NOT NULL
DEFAULT '00:00:00',
-- time range begin of accounting (inclusive)
-- [09:00:00] (rw)
ev_time_end TIME (0) WITH TIME ZONE
NOT NULL
DEFAULT '00:00:00',
-- time range end of accounting (exclusive)
-- [17:00:00] (rw)
ev_time_amount INTERVAL (0)
NOT NULL,
-- number of seconds accounted within time range
-- [1800] (rw)
ev_account BIGINT
NOT NULL
REFERENCES as_account (ac_oid)
MATCH FULL DEFERRABLE,
-- reference to account receiving the event
-- [7] (rw)
ev_remark TEXT
DEFAULT '',
-- optional remark describing event in detail
-- [vim, screen, bash] (rw)
ev_status VARCHAR(3)
NOT NULL
CHECK(ev_status ~ '^(new|mod|del)$'),
-- update status of event
-- [new] (rw)
ev_revision INTEGER
NOT NULL
DEFAULT 0
-- revision counter of event (for detecting update collisions)
-- [0] (rw)
);
-- System Logbook
CREATE TABLE as_log (
lg_time TIMESTAMP (2) WITHOUT TIME ZONE
DEFAULT 'now',
-- logging time
-- [2002-10-01 14:42:00] (ro)
lg_entry TEXT
NOT NULL
-- logging entry
-- [login by user rse from dt1.dev.de.cw.net] (rw)
);
-- Object Relationships: Structure
CREATE TABLE as_rel_struct (
rs_src BIGINT
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE,
-- reference to source object having relationship
-- [64] (re)
rs_rel VARCHAR(20)
NOT NULL
CHECK(rs_rel ~ '^(is-valid-for|is-located-in|has-parent|diverts-to|is-member-of|is-proxy-for|reports-to)$'),
-- name of relationship between source and destination object
-- [member-of] (re)
rs_dst BIGINT
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE,
-- reference to destination object having relationship
-- [66] (re)
rs_valid_beg TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'now',
-- begin real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
rs_valid_end TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'infinity',
-- end real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
rs_epoch_beg BIGINT
NOT NULL
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE
-- begin system epoch of record creation (inclusive)
-- [2] (ro)
);
-- Object Relationships: Access Control
CREATE TABLE as_rel_access (
ra_src BIGINT
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE,
-- reference to source object (having access rights)
-- [64] (re)
ra_src_depth INTEGER
NOT NULL
DEFAULT '-1',
-- graph depth starting from source object for security equivalence
-- [0] (re)
ra_rel VARCHAR(20)
NOT NULL
CHECK(ra_rel ~ '^(read|write)$'),
-- name of relation between source and destination object (i.e. right to grant)
-- [read] (re)
ra_dst_depth INTEGER
NOT NULL
DEFAULT '-1',
-- graph depth ending at destination object for application scope
-- [0] (re)
ra_dst BIGINT
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE,
-- reference to destination object (receiving access rights)
-- [66] (re)
ra_valid_beg TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'now',
-- begin real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
ra_valid_end TIMESTAMP (0) WITH TIME ZONE
NOT NULL
DEFAULT 'infinity',
-- end real time of record validity range (inclusive)
-- [2002-01-01 00:00:00] (re)
ra_epoch_beg BIGINT
NOT NULL
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE
-- begin system epoch of record creation (inclusive)
-- [2] (ro)
);
-- Object Relationships: Effective [EXTERNALLY CALCULATED]
CREATE TABLE as_rel_effective (
re_src BIGINT
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE,
-- reference to source object
-- [64] (rw)
re_rel VARCHAR(20)
NOT NULL,
-- name of relation between source and destination object
-- [read] (rw)
re_dst BIGINT
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE
-- reference to destination object
-- [64] (rw)
);
-- Stored Procedure: Maximum Epoch Number
CREATE FUNCTION as_epoch_max ()
RETURNS as_epoch_ep_epoch_seq.max_value%TYPE
LANGUAGE 'sql' IMMUTABLE AS '
SELECT max_value FROM as_epoch_ep_epoch_seq;
';
-- Stored Procedure: Current Epoch Number
CREATE FUNCTION as_epoch_current ()
RETURNS as_epoch_ep_epoch_seq.max_value%TYPE
LANGUAGE 'sql' STABLE AS '
SELECT currval(''as_epoch_ep_epoch_seq'');
';
-- Stored Procedure: Current OID Number
CREATE FUNCTION as_oid_current ()
RETURNS as_oid_id_oid_seq.max_value%TYPE
LANGUAGE 'sql' STABLE AS '
SELECT currval(''as_oid_id_oid_seq'');
';
-- Stored Procedure: Map Account Absolute Name to Account OID
CREATE FUNCTION as_account_name2oid(TEXT, TIMESTAMP (0) WITH TIME ZONE)
RETURNS BIGINT LANGUAGE 'plpgsql' STABLE AS '
DECLARE
comp TEXT;
path TEXT;
time TIMESTAMP (0) WITH TIME ZONE;
oid BIGINT;
BEGIN
-- RAISE NOTICE ''as_account_name2oid(%,%)'', $1, $2;
path := $1;
time := $2;
IF substr(path, 1, 1) != ''/'' THEN
RAISE EXCEPTION ''as_account_name2oid(%,%): path not starting with slash'', $1, $2;
END IF;
oid := (SELECT ac_oid FROM as_account
WHERE ac_name = ''''
AND ac_parent = ac_oid
AND (time BETWEEN ac_valid_beg AND ac_valid_end));
IF oid IS NULL THEN
RAISE EXCEPTION ''as_account_name2oid(%,%): root path component not found'', $1, $2;
END IF;
path := substr(path, 2);
-- RAISE NOTICE ''as_account_name2oid: "/" -> %'', oid;
WHILE path != '''' LOOP
comp := substring(path from ''^([^/]+)/*'');
path := substring(path from ''^[^/]+/*(.*)$'');
oid := (SELECT ac_oid FROM as_account
WHERE ac_name = comp
AND ac_parent = oid
AND epoch BETWEEN ac_epoch_start AND ac_epoch_end);
IF oid IS NULL THEN
RAISE EXCEPTION ''as_account_name2oid(%,%): path component "%" not found'', $1, $2, comp;
END IF;
-- RAISE NOTICE ''as_account_name2oid: "%" -> %'', comp, oid;
END LOOP;
-- RAISE NOTICE ''as_account_name2oid(%,%) = %'', $1, $2, oid;
RETURN oid;
END;
';
-- Stored Procedure: Map Account OID to Account Absolute Name
CREATE FUNCTION as_account_oid2name(TEXT, TIMESTAMP (0) WITH TIME ZONE)
RETURNS TEXT LANGUAGE 'plpgsql' STABLE AS '
DECLARE
oid BIGINT;
name TEXT;
time TIMESTAMP (0) WITH TIME ZONE;
obj RECORD;
BEGIN
-- RAISE NOTICE ''as_account_oid2name(%,%)'', $1, $2;
oid := $1;
time := $2;
name := '''';
LOOP
SELECT * INTO obj FROM as_account
WHERE ac_oid = oid);
AND (epoch BETWEEN ac_epoch_start AND ac_epoch_end);
IF NOT FOUND THEN
RAISE EXCEPTION ''as_account_oid2name(%,%): object % not found'', $1, $2, oid;
END IF;
-- RAISE NOTICE ''as_account_oid2name: % -> "%"'', oid, obj.ac_name;
IF obj.ac_parent = oid THEN
EXIT;
END IF;
name := ''/'' || obj.ac_name || name;
oid := obj.ac_parent;
END LOOP;
IF name = '''' THEN
name = ''/'';
END IF;
-- RAISE NOTICE ''as_account_oid2name(%,%) = %'', $1, $2, name;
RETURN name;
END;
';
@
1.32
log
@add epoch support
@
text
@d33 1
a33 1
-- that they are exactly positioned.
d42 1
a42 1
-- [version]
d46 1
a46 1
-- [0.9.0]
d55 1
a55 1
-- [42]
d58 1
a58 1
CHECK(id_type ~ '^(locality|holiday|user|group|account)$')
d60 6
a65 1
-- [user]
d74 1
a74 1
-- [42]
d78 1
a78 1
-- [2002-01-01 00:00:00.0000]
d89 1
a89 7
-- [1]
lo_parent BIGINT
NOT NULL
REFERENCES as_locality (lo_oid)
MATCH FULL DEFERRABLE,
-- reference to parent locality (self-reference for no parent)
-- [6]
d93 1
a93 1
-- [Germany/Bavaria]
d98 7
a104 2
-- [2]
lo_epoch_start BIGINT
d106 4
a109 5
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE,
-- start epoch this record is valid (including)
-- [1]
lo_epoch_end BIGINT
d113 2
a114 2
-- end epoch this record is valid (excluding)
-- [2]
d125 1
a125 7
-- [1]
hd_locality BIGINT
NOT NULL
REFERENCES as_locality (lo_oid)
MATCH FULL DEFERRABLE,
-- reference to locality
-- [1]
d129 7
a135 2
-- [2002-10-02]
hd_epoch_start BIGINT
d137 4
a140 5
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE,
-- start epoch this record is valid (including)
-- [1]
hd_epoch_end BIGINT
d144 2
a145 2
-- end epoch this record is valid (excluding)
-- [2]
d156 1
a156 1
-- [42]
d161 1
a161 1
-- [rse]
d165 1
a165 1
-- [Ralf S. Engelschall]
d170 1
a170 1
-- [$1$xxx...]
d175 1
a175 1
-- [rse@@de.cw.com]
d179 1
a179 1
-- [http://dev.de.cw.net/~rse/]
d184 1
a184 1
-- [8]
d189 1
a189 7
-- [5]
us_locality BIGINT
NOT NULL
REFERENCES as_locality (lo_oid)
MATCH FULL DEFERRABLE,
-- reference to locality where user is located
-- [1]
d194 1
a194 1
-- [true]
d200 1
a200 1
-- [40]
d206 1
a206 1
-- [12]
d211 7
a217 2
-- [true]
us_epoch_start BIGINT
d219 4
a222 5
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE,
-- start epoch this record is valid (including)
-- [1]
us_epoch_end BIGINT
d226 2
a227 2
-- end epoch this record is valid (excluding)
-- [2]
d238 1
a238 1
-- [66]
d241 1
a241 1
-- [dev]
d244 1
a244 1
-- [Development Team]
d249 7
a255 2
-- [true]
gr_epoch_start BIGINT
d257 4
a260 5
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE,
-- start epoch this record is valid (including)
-- [1]
gr_epoch_end BIGINT
d264 2
a265 2
-- end epoch this record is valid (excluding)
-- [2]
d276 1
a276 7
-- [7]
ac_parent BIGINT
NOT NULL
REFERENCES as_account (ac_oid)
MATCH FULL DEFERRABLE,
-- reference to parent account (self-reference for root)
-- [6]
d280 1
a280 1
-- [pmod]
d284 1
a284 6
-- [OpenPKG PMOD/PSOD Tasks]
ac_zombie BOOLEAN
NOT NULL
DEFAULT 'false',
-- whether account is a zombie (has successors)
-- [false]
d289 1
a289 1
-- [true]
d294 1
a294 1
-- [true]
d300 8
a307 12
-- [0]
ac_diversion BIGINT
NOT NULL
REFERENCES as_account (ac_oid)
MATCH FULL DEFERRABLE,
-- reference to account for write diversion (self-reference for none)
-- [8]
ac_not_before TIMESTAMP (0) WITH TIME ZONE
DEFAULT '-infinity',
-- automatic active off->on toggle time
-- [2002-01-01 00:00:00]
ac_not_after TIMESTAMP (0) WITH TIME ZONE
d309 3
a311 14
-- automatic active on->off toggle time
-- [2002-01-01 00:00:00]
ac_floating INTERVAL (0)
NOT NULL
DEFAULT '31536000',
-- number of seconds of floating (events on account are locked after this duration)
-- [31536000]
ac_epoch_start BIGINT
NOT NULL
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE,
-- start epoch this record is valid (including)
-- [1]
ac_epoch_end BIGINT
d315 2
a316 2
-- end epoch this record is valid (excluding)
-- [2]
d324 1
a324 1
-- [f81d4fae-7dec-11d0-a765-00a0c91e6bf6]
d330 1
a330 1
-- [42]
d334 1
a334 1
-- [2002-10-01]
d338 2
a339 2
-- time range begin of accounting
-- [09:00:00+1]
d344 1
a344 1
-- [17:00:00+1]
d348 1
a348 1
-- [1800]
d354 1
a354 1
-- [7]
d358 2
a359 2
-- [vim, screen, bash]
ev_lock_acc BOOLEAN
d361 4
a364 4
DEFAULT 'false',
-- whether event is locked (forever) for accounting
-- [false]
ev_lock_rep INTEGER
d366 3
a368 9
DEFAULT '-1',
-- whether event is locked (value 0) or can still be moved within parents sub-tree
-- [-1]
ev_epoch_start BIGINT
NOT NULL
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE
-- start epoch this record is valid
-- [1]
d376 1
a376 1
-- [2002-10-01 14:42:00]
d380 1
a380 1
-- [login by user rse from dt1.dev.de.cw.net]
d385 1
a385 1
re_src BIGINT
d390 2
a391 2
-- [64]
re_rel VARCHAR(20)
d393 1
a393 1
CHECK(re_rel ~ '^(member-of|proxy-of|reports-to)$'),
d395 2
a396 2
-- [member-of]
re_dst BIGINT
d401 2
a402 2
-- [66]
re_epoch_start BIGINT
d404 9
a412 5
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE,
-- start epoch this record is valid (including)
-- [1]
re_epoch_end BIGINT
d416 2
a417 2
-- end epoch this record is valid (excluding)
-- [2]
d422 1
a422 1
re_src BIGINT
d427 2
a428 2
-- [64]
re_src_depth INTEGER
d432 2
a433 2
-- [0]
re_rel VARCHAR(20)
d435 1
a435 1
CHECK(re_rel ~ '^(read|write)$'),
d437 2
a438 2
-- [read]
re_dst_depth INTEGER
d442 2
a443 2
-- [0]
re_dst BIGINT
d448 7
a454 2
-- [66]
re_epoch_start BIGINT
d456 4
a459 5
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE,
-- start epoch this record is valid (including)
-- [1]
re_epoch_end BIGINT
d463 2
a464 2
-- end epoch this record is valid (excluding)
-- [2]
d474 1
a474 1
-- [64]
d478 1
a478 1
-- [read]
d482 1
a482 1
MATCH FULL DEFERRABLE,
d484 1
a484 13
-- [64],
re_epoch_start BIGINT
NOT NULL
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE,
-- start epoch this record is valid (including)
-- [1]
re_epoch_end BIGINT
NOT NULL
REFERENCES as_epoch (ep_epoch)
MATCH FULL DEFERRABLE
-- end epoch this record is valid (excluding)
-- [2]
d509 1
a509 1
CREATE FUNCTION as_account_name2oid(TEXT, BIGINT)
d514 1
a514 1
epoch BIGINT;
d519 1
a519 1
epoch := $2;
d526 1
a526 1
AND (epoch BETWEEN ac_epoch_start AND ac_epoch_end));
d550 1
a550 1
CREATE FUNCTION as_account_oid2name(TEXT, BIGINT)
d555 1
a555 1
epoch BIGINT;
d560 1
a560 1
epoch := $2;
@
1.31
log
@fix field names
@
text
@d542 1
a542 1
CREATE FUNCTION as_account_name2oid(TEXT)
d547 1
d550 1
a550 1
-- RAISE NOTICE ''as_account_name2oid(%)'', $1;
d552 1
d554 1
a554 1
RAISE EXCEPTION ''as_account_name2oid(%): path not starting with slash'', $1;
d557 3
a559 1
WHERE ac_name = '''' AND ac_parent = ac_oid);
d561 1
a561 1
RAISE EXCEPTION ''as_account_name2oid(%): root path component not found'', $1;
d569 3
a571 1
WHERE ac_name = comp AND ac_parent = oid);
d573 1
a573 1
RAISE EXCEPTION ''as_account_name2oid(%): path component "%" not found'', $1, comp, path;
d577 1
a577 1
-- RAISE NOTICE ''as_account_name2oid(%) = %'', $1, oid;
d583 1
a583 1
CREATE FUNCTION as_account_oid2name(TEXT)
d588 1
d591 1
a591 1
-- RAISE NOTICE ''as_account_oid2name(%)'', $1;
d593 1
d596 3
a598 1
SELECT * INTO obj FROM as_account WHERE ac_oid = oid;
d600 1
a600 1
RAISE EXCEPTION ''as_account_oid2name(%): object % not found'', $1, oid;
d612 1
a612 1
-- RAISE NOTICE ''as_account_oid2name(%) = %'', $1, name;
@
1.30
log
@mark functions with return value lifetime
@
text
@d554 2
a555 2
oid := (SELECT as_oid FROM as_account
WHERE as_name = '''' AND as_parent = as_oid);
d564 2
a565 2
oid := (SELECT as_oid FROM as_account
WHERE as_name = comp AND as_parent = oid);
d588 1
a588 1
SELECT * INTO obj FROM as_account WHERE as_oid = oid;
d592 2
a593 2
-- RAISE NOTICE ''as_account_oid2name: % -> "%"'', oid, obj.as_name;
IF obj.as_parent = oid THEN
d596 2
a597 2
name := ''/'' || obj.as_name || name;
oid := obj.as_parent;
@
1.29
log
@just flush my work on AS DB of this evening
@
text
@d523 1
a523 1
LANGUAGE 'sql' AS '
d530 1
a530 1
LANGUAGE 'sql' AS '
d537 1
a537 1
LANGUAGE 'sql' AS '
d543 1
a543 1
RETURNS BIGINT LANGUAGE 'plpgsql' AS '
d578 1
a578 1
RETURNS TEXT LANGUAGE 'plpgsql' AS '
@
1.28
log
@comment PL/pgSQL handler and add the new name <-> OID mapping stored procedures
@
text
@d78 1
a78 1
lo_oid INTEGER
d85 1
a85 1
lo_parent INTEGER
d100 2
a101 2
lo_epoch_start INTEGER
UNIQUE NOT NULL
d106 2
a107 2
lo_epoch_end INTEGER
UNIQUE NOT NULL
d116 1
a116 1
hd_oid INTEGER
d123 1
a123 1
hd_locality INTEGER
d133 2
a134 2
hd_epoch_start INTEGER
UNIQUE NOT NULL
d139 2
a140 2
hd_epoch_end INTEGER
UNIQUE NOT NULL
d149 1
a149 1
us_oid INTEGER
d189 1
a189 1
us_locality INTEGER
d217 2
a218 2
us_epoch_start INTEGER
UNIQUE NOT NULL
d223 2
a224 2
us_epoch_end INTEGER
UNIQUE NOT NULL
d233 1
a233 1
gr_oid INTEGER
d251 2
a252 2
gr_epoch_start INTEGER
UNIQUE NOT NULL
d257 2
a258 2
gr_epoch_end INTEGER
UNIQUE NOT NULL
d267 1
a267 1
ac_oid INTEGER
d274 1
a274 1
ac_parent INTEGER
d309 1
a309 1
ac_diversion INTEGER
d315 2
a316 1
ac_not_before TIMESTAMP (0) WITH TIME ZONE,
d319 2
a320 1
ac_not_after TIMESTAMP (0) WITH TIME ZONE,
d328 2
a329 2
ac_epoch_start INTEGER
UNIQUE NOT NULL
d334 2
a335 2
ac_epoch_end INTEGER
UNIQUE NOT NULL
d348 1
a348 1
ev_user INTEGER
d372 1
a372 1
ev_account INTEGER
d392 2
a393 2
ev_epoch_start INTEGER
UNIQUE NOT NULL
d414 1
a414 1
re_src INTEGER
d425 1
a425 1
re_dst INTEGER
d431 2
a432 2
re_epoch_start INTEGER
UNIQUE NOT NULL
d437 2
a438 2
re_epoch_end INTEGER
UNIQUE NOT NULL
d447 1
a447 1
re_src INTEGER
d468 1
a468 1
re_dst INTEGER
d474 2
a475 2
re_epoch_start INTEGER
UNIQUE NOT NULL
d480 2
a481 2
re_epoch_end INTEGER
UNIQUE NOT NULL
d490 1
a490 1
re_src INTEGER
d500 1
a500 1
re_dst INTEGER
d506 2
a507 2
re_epoch_start INTEGER
UNIQUE NOT NULL
d512 2
a513 2
re_epoch_end INTEGER
UNIQUE NOT NULL
d525 14
@
1.27
log
@activate PL/pgSQL; create new as_epoch_max() stored procedure; convert to BIGSERIAL
@
text
@d521 68
a588 7
LANGUAGE 'plpgsql' AS '
DECLARE
res RECORD;
BEGIN
SELECT INTO res max_value FROM as_epoch_ep_epoch_seq;
RETURN res.max_value;
END;
@
1.26
log
@flush pending changes
@
text
@d40 1
a40 1
CHECK(cf_var ~ '^(version|release)$'),
d51 2
a52 1
id_oid SERIAL
d65 2
a66 1
ep_epoch SERIAL
d333 1
a333 1
UNIQUE
d517 12
@
1.25
log
@add OID to holiday table, too
@
text
@a271 6
ac_predecessor INTEGER
NOT NULL
REFERENCES as_account (ac_oid)
MATCH FULL DEFERRABLE,
-- reference to predecessor account (self-reference for initial)
-- [7]
d331 1
a331 1
UNIQUE NOT NULL
@
1.24
log
@fully work off the AS DB API architecture by introducing a Perl
management API for the AS DB which currently at least performs all the
schema creation/destruction the old executable as_db.pl did. The new
as_db.pl now just calls this Perl API.
@
text
@d57 1
a57 1
CHECK(id_type ~ '^(locality|user|group|account)$')
d114 7
@
1.23
log
@ok, add epoch support to get full history in DB
@
text
@d26 2
d31 4
a34 25
-- connect as super-user to standard database
\connect @@su_database@@ @@su_username@@;
-- create new AS user account
DROP USER "@@as_username@@";
CREATE USER "@@as_username@@" WITH
VALID UNTIL '@@as_expiring@@'
ENCRYPTED PASSWORD '@@as_password@@'
NOCREATEDB NOCREATEUSER;
-- create AS database
DROP DATABASE "@@as_database@@";
CREATE DATABASE "@@as_database@@";
\connect @@as_database@@ @@su_username@@;
COMMENT ON DATABASE "@@as_database@@"
IS 'Accounting System';
-- connect as AS user to AS database
\connect @@as_database@@ @@as_username@@;
-- start definitions of AS database tables
-- (notice that SQL comments below are converted
-- into SQL COMMENT statements by as_db.pl, so
-- make sure that they are exactly positioned!)
@
1.22
log
@cleanup the relationships by using just two managed tables plus one generated one
@
text
@d114 1
a114 1
CHECK(lo_timezone >= -12 AND lo_timezone <= +12)
d117 12
d140 1
a140 1
NOT NULL
d143 12
d224 1
a224 1
DEFAULT 'true'
d227 12
d258 1
a258 1
DEFAULT 'true'
d261 12
d339 1
a339 1
DEFAULT '31536000'
d342 12
d403 1
a403 1
DEFAULT '-1'
d406 6
d436 1
a436 1
CHECK(re_right ~ '^(member-of|proxy-of|reports-to)$'),
d442 1
a442 1
MATCH FULL DEFERRABLE
d445 12
d474 1
a474 1
CHECK(re_right ~ '^(read|write)$'),
d485 1
a485 1
MATCH FULL DEFERRABLE
d488 12
d517 12
d530 2
a531 2
-- reference to destination object
-- [64]
@
1.21
log
@add epoch table
@
text
@a226 79
-- System Relationship Member
CREATE TABLE as_relation_member (
rm_source INTEGER
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE,
-- reference to source object having membership
-- [64]
rm_target INTEGER
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE
-- reference to target object proving membership
-- [66]
);
-- System Relationship Proxy
CREATE TABLE as_relation_proxy (
rp_source INTEGER
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE,
-- reference to source object acting as proxy
-- [64]
rp_target INTEGER
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE
-- reference to target object being proxied
-- [66]
);
-- System Relationship Reporting
CREATE TABLE as_relation_reporting (
rr_source INTEGER
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE,
-- reference to source object doing reporting
-- [64]
rr_target INTEGER
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE
-- reference to target object receiving reports
-- [66]
);
-- System Access Control
CREATE TABLE as_sacl (
sa_src INTEGER
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE,
-- reference to source object having access rights
-- [64]
sa_src_depth INTEGER
NOT NULL
DEFAULT '-1',
-- graph depth starting from source object for security equivalence
-- [0]
sa_right TEXT
NOT NULL
CHECK(sa_right ~ '^(read|write)$'),
-- name of right to grant
-- [read]
sa_tgt_depth INTEGER
NOT NULL
DEFAULT '-1',
-- graph depth ending at target object for application scope
-- [0]
sa_tgt INTEGER
NOT NULL
REFERENCES as_oid (id_oid)
MATCH FULL DEFERRABLE
-- reference to target object receiving access rights
-- [66]
);
d360 55
a414 3
-- System Effective Object Relationships [GENERATED]
CREATE TABLE as_er (
er_source INTEGER
d418 1
a418 1
-- identifier of source object
d420 1
a420 1
er_relation TEXT
d422 1
a422 1
-- name of relation
d424 1
a424 1
er_target INTEGER
d428 1
a428 1
-- identifier of source object
@
1.20
log
@bump copyright year
@
text
@d81 12
@
1.19
log
@be more pedantic logically and reference OID table from ER table
@
text
@d3 2
a4 2
-- Copyright (c) 2002 Cable & Wireless Deutschland
-- Copyright (c) 2002 Ralf S. Engelschall
@
1.18
log
@finally take feedback from DB team into account
@
text
@d430 3
a432 1
NOT NULL,
d441 2
@
1.17
log
@add version
@
text
@d57 1
a57 1
cf_version VARCHAR(5)
d59 6
a64 1
-- version of database model
d74 1
a74 1
id_type TEXT
d277 2
a278 1
NOT NULL,
@
1.16
log
@final super-draft version ;-) of data model
@
text
@d55 8
@
1.15
log
@switch from local serial-based ids to global object ids
@
text
@d56 11
a66 2
CREATE SEQUENCE as_oid
INCREMENT 1 MINVALUE 1 START 1;
d70 4
a73 1
lo_id SERIAL
d79 1
a79 1
REFERENCES as_locality (lo_id)
d98 1
a98 1
REFERENCES as_locality (lo_id)
d112 2
a113 1
DEFAULT nextval('as_oid')
d152 1
a152 1
REFERENCES as_locality (lo_id)
d184 2
a185 1
DEFAULT nextval('as_oid')
d202 9
a210 3
-- System Membership (User-in-Groups)
CREATE TABLE as_membership_ug (
mu_member INTEGER
d212 27
a238 1
REFERENCES as_user (us_oid)
d240 1
a240 1
-- reference to user having membership
d242 1
a242 1
mu_group INTEGER
d244 1
a244 1
REFERENCES as_group (gr_oid)
d246 1
a246 1
-- reference to group proving membership
d250 3
a252 3
-- System Membership (Group-in-Groups)
CREATE TABLE as_membership_gg (
mg_member INTEGER
d254 1
a254 1
REFERENCES as_group (gr_oid)
d256 1
a256 1
-- reference to group having membership
d258 10
a267 1
mg_group INTEGER
d269 6
a274 1
REFERENCES as_group (gr_oid)
d276 1
a276 1
-- reference to group proving membership
d284 2
a285 1
DEFAULT nextval('as_oid')
d289 6
d309 5
d339 1
a339 1
ac_not_after TIMESTAMP (0) WITH TIME ZONE
d342 5
d386 1
a386 1
DEFAULT ''
d389 10
d411 16
@
1.14
log
@add MOF-related type
@
text
@d55 4
d98 3
a100 1
us_id SERIAL
d102 1
a102 1
-- unique identifier
d169 5
a173 3
gr_id SERIAL
PRIMARY KEY,
-- unique identifier of group
d190 1
a190 1
mu_user INTEGER
d192 1
a192 1
REFERENCES as_user (us_id)
d195 1
a195 1
-- [42]
d198 1
a198 1
REFERENCES as_group (gr_id)
d206 1
a206 1
mg_user INTEGER
d208 1
a208 1
REFERENCES as_group (gr_id)
d214 1
a214 1
REFERENCES as_group (gr_id)
d222 3
a224 1
ac_id SERIAL
d226 1
a226 1
-- unique identifier of account
d230 1
a230 1
REFERENCES as_account (ac_id)
d260 1
a260 1
REFERENCES as_account (ac_id)
d280 1
a280 1
REFERENCES as_user (us_id)
d304 1
a304 1
REFERENCES as_account (ac_id)
@
1.13
log
@fix syntax and fix ordering
@
text
@d242 6
@
1.12
log
@Add support for localities and their holidays. For redundancy reduction
reasons, localities can be established hierarchically. Here localities
inherit the holidays of their parent locality. Think about German
holidays and Bavarian ones to better understand this.
@
text
@a54 14
-- System Locality Holidays
CREATE as_holiday (
hd_locality INTEGER
NOT NULL
REFERENCES as_locality (lo_id)
MATCH FULL DEFERRABLE
-- reference to locality
-- [1]
hd_date DATE
NOT NULL
-- date of locality holiday
-- [2002-10-02]
);
d56 1
a56 1
CREATE as_locality (
d73 1
a73 1
CHECK(lo_timezone >= -12 AND lo_timezone <= +12),
d78 14
d134 1
a134 1
MATCH FULL DEFERRABLE
@
1.11
log
@finish user/group attributes
@
text
@d55 37
d131 6
@
1.10
log
@- move out dummy tables into 00BRAINSTORM for now until they are needed
- create first cut for user-in-group and group-in-group tables for THL
- some more cleanups
@
text
@d59 1
a59 1
-- unique identifier of user
d61 4
a64 2
us_name VARCHAR(30),
-- system name of user
d66 3
a68 2
us_realname VARCHAR(60),
-- real name of user
d70 3
a72 1
us_password VARCHAR(30),
d75 36
a110 18
us_email TEXT,
-- email address [rse@@de.cw.com]
us_home TEXT,
-- homepage URL [http://dev.de.cw.net/~rse/]
us_theme TEXT,
-- WebUI theme [default]
us_workhours TEXT,
-- daily working hours [8]
us_workdays TEXT,
-- weekly working days [5]
us_notify TEXT,
-- whether user is notified about missing accounting [yes]
us_notify_after TEXT,
-- hours after which user is notified [12]
us_leader TEXT,
-- leader of his group(s) [yes]
us_admin TEXT
-- administrator of group (s) [yes]
d112 2
a113 1
DEFAULT 'true',
d131 2
a132 1
DEFAULT 'true',
d137 1
a137 1
-- System User-in-Groups Membership
d148 1
a148 1
MATCH FULL DEFERRABLE,
d153 1
a153 1
-- System Group-in-Groups Membership
d164 1
a164 1
MATCH FULL DEFERRABLE,
d190 1
d195 1
@
1.9
log
@remember self-reference issue
@
text
@d50 4
a53 68
-- System Configuration
CREATE TABLE as_config (
cf_version TEXT,
-- data model version
cf_name TEXT PRIMARY KEY,
-- variable name
cf_value TEXT
-- variable value
);
-- External Resource
CREATE TABLE as_resource (
rs_id TEXT,
rs_url TEXT
);
-- System Themes
CREATE TABLE as_theme (
th_name TEXT,
-- name of theme
th_color_1_bg CHAR(6),
-- RGB of color level #1 (background)
th_color_1_fg CHAR(6),
-- RGB of color level #1 (foreground)
th_color_2_bg CHAR(6),
-- RGB of color level #2 (background)
th_color_2_fg CHAR(6),
-- RGB of color level #2 (foreground)
th_color_3_bg CHAR(6),
-- RGB of color level #3 (background)
th_color_3_fg CHAR(6),
-- RGB of color level #3 (foreground)
th_color_4_bg CHAR(6),
-- RGB of color level #4 (background)
th_color_4_fg CHAR(6),
-- RGB of color level #4 (foreground)
th_color_5_bg CHAR(6),
-- RGB of color level #5 (background)
th_color_5_fg CHAR(6)
-- RGB of color level #5 (foreground)
);
-- System Sessions
CREATE TABLE as_session (
se_session_id TEXT PRIMARY KEY,
-- session id
se_user_id INTEGER NOT NULL,
-- associated user
se_expire_time TIMESTAMP (0)
WITHOUT TIME ZONE
NOT NULL
-- expire time
);
-- Reporting
CREATE TABLE as_reporting (
re_id TEXT PRIMARY KEY,
-- user id
re_transitive TEXT,
-- report receiver is allowed to re-report
re_other TEXT,
-- user id of receiver user
re_account TEXT,
-- root of reported accounts
re_read_accu TEXT
-- read accumulation
-- [is.dev.meeting,is.hst.meeting,is.prg.*.meeting,!is.dev.meeting.foo]
);
d61 1
a61 1
us_username VARCHAR(30),
a69 2
us_group INTEGER,
-- group membership [development]
d88 4
d96 46
a141 4
gr_id TEXT PRIMARY KEY,
-- id (unique)
gr_name TEXT
-- short description of group
d164 1
a164 1
ac_active BOOLEAN
d166 1
a166 1
-- whether account is active or not
@
1.8
log
@fix syntax
@
text
@d174 1
a174 1
-- reference to parent account
d196 1
a196 1
-- reference to account for write diversion
@
1.7
log
@more work on the data model
@
text
@d184 1
a184 1
ac_active BOOLEAN,
d198 1
a198 1
ac_not_before TIMESTAMP (0) WITH TIME ZONE
d243 1
a243 1
DEFAULT '',
@
1.6
log
@ok, we know use UUIDs directly in the database
@
text
@d52 2
a65 38
-- System Users
CREATE TABLE as_user (
us_id SERIAL PRIMARY KEY,
-- id (unique) [rse]
us_name TEXT,
-- name [Ralf S. Engelschall]
us_passwd TEXT,
-- password [qwert]
us_group TEXT,
-- group membership [development]
us_email TEXT,
-- email address [rse@@de.cw.com]
us_home TEXT,
-- homepage URL [http://dev.de.cw.net/~rse/]
us_theme TEXT,
-- WebUI theme [default]
us_workhours TEXT,
-- daily working hours [8]
us_workdays TEXT,
-- weekly working days [5]
us_notify TEXT,
-- whether user is notified about missing accounting [yes]
us_notify_after TEXT,
-- hours after which user is notified [12]
us_leader TEXT,
-- leader of his group(s) [yes]
us_admin TEXT
-- administrator of group (s) [yes]
);
-- System Groups
CREATE TABLE as_group (
gr_id TEXT PRIMARY KEY,
-- id (unique)
gr_name TEXT
-- short description of group
);
d112 1
a112 1
re_account TEXT
d114 48
d166 20
a185 9
ac_id SERIAL PRIMARY KEY,
-- unique key of account
ac_parent TEXT,
-- parent account (account.id)
ac_name TEXT,
-- name of account (non-unique)
ac_description TEXT,
-- account description text
ac_active TEXT,
d187 17
a203 6
ac_not_before TEXT,
-- automatic deactive->active toggle time
ac_not_after TEXT,
-- automatic active->deactive toggle time
ac_type TEXT
-- type of account: abstract, real, virtual
d210 8
a217 1
-- ISO-11578 Universally Unique Identifiers (UUID)
d220 2
a221 1
-- date of accounting
d225 2
a226 1
-- time range begin
d230 2
a231 1
-- time range end (exclusive with rollover)
d234 5
a238 6
-- number of minutes accounted
ev_remark TEXT,
-- optional remarks for accounted event
ev_user INTEGER
NOT NULL
REFERENCES as_user (us_id)
d240 6
a245 6
-- user submitting the event
ev_account INTEGER
NOT NULL
REFERENCES as_account (ac_id)
MATCH FULL DEFERRABLE
-- account receiving the event
d248 1
a248 1
-- System Logging Messages
d253 1
d257 1
@
1.5
log
@flush work of this afternoon
@
text
@d174 1
a174 1
ev_id SERIAL
d176 1
a176 1
-- auto-incrementing unique key of event
@
1.4
log
@cleanup the data model stuff
@
text
@d174 29
a202 16
ev_id SERIAL PRIMARY KEY,
-- auto-incrementing unique key of event
ev_date DATE NOT NULL,
-- date of accounting
ev_minutes INTEGER NOT NULL,
-- number of minutes accounted
ev_remark TEXT,
-- optional remarks for accounted event
ev_user INTEGER NOT NULL
REFERENCES as_user (us_id)
MATCH FULL DEFERRABLE,
-- user submitting the event
ev_account INTEGER NOT NULL
REFERENCES as_account (ac_id)
MATCH FULL DEFERRABLE
-- account receiving the event
d207 6
a212 5
lg_time TIMESTAMP (2) WITHOUT TIME ZONE
DEFAULT 'now',
-- logging time
lg_entry TEXT NOT NULL
-- logging entry
@
1.3
log
@add DBI-based database creation scripts
@
text
@d30 1
a30 1
-- create user account
d32 2
d40 1
a40 1
-- create database
d46 2
d152 1
a152 1
-- table describing the existing accounts
d154 1
a154 1
ac_id TEXT PRIMARY KEY,
@
1.2
log
@we also need external resources
@
text
@d2 22
a25 3
-- Copyright (c) 2002 Cable & Wireless
-- Copyright (c) 2002 Ralf S. Engelschall
--
d30 15
a44 4
DROP DATABASE as;
CREATE DATABASE as;
\connect as as;
COMMENT ON DATABASE as IS "Accounting System";
d46 1
a46 1
-- System Configuration
d54 1
a54 1
-- External Resource
d57 1
a57 1
rs_url TEXT,
d60 1
a60 1
-- System Users
d86 1
a86 1
us_admin TEXT,
d90 1
a90 1
-- System Groups
d94 1
a94 1
gr_name TEXT,
d98 1
a98 1
-- System Themes
d120 1
a120 1
th_color_5_fg CHAR(6),
d124 1
a124 1
-- System Sessions
d136 1
a136 1
-- Reporting
d144 1
a144 1
re_account TEXT,
d148 1
a148 1
-- table describing the existing accounts
d164 1
a164 1
ac_type TEXT,
d168 1
a168 1
-- Accounting Events
d188 1
a188 1
-- System Logging Messages
@
1.1
log
@split data modell into own SQL file for direct use in PostgreSQL
@
text
@d24 6
@