head	1.4;
access;
symbols
	AS_AFTER_RESTRUCTURING:1.4
	AS_PRODUCTION:1.1;
locks; strict;
comment	@# @;


1.4
date	2002.09.25.15.11.16;	author rse;	state Exp;
branches;
next	1.3;

1.3
date	2002.09.17.19.34.42;	author rse;	state Exp;
branches;
next	1.2;

1.2
date	2002.09.16.13.27.52;	author rse;	state Exp;
branches;
next	1.1;

1.1
date	2002.09.13.14.32.44;	author rse;	state Exp;
branches;
next	;


desc
@@


1.4
log
@flush pending changes from today
@
text
@
@


1.3
log
@split data modell into own SQL file for direct use in PostgreSQL
@
text
@a1 87
Dialogs
=======

- Accounting
  - 
- Reporting
  - Current State (Last Timeframe)
    - per employee
    - per team
    - per division
  - Progression State (History)
    - per employee
    - per team
    - per division
- Administration
  - New User
  - Edit User
  - Delete User
  - Switch User
  - History Log
- Session
  - login or logout via username,password
  - change password
  - change profile

Access
======

- who is allowed to login to system
- who is allowed to add/remove/rename accounts
- who is allowed to see reports about which accounts
- who is allowed to account onto what

Accounts
========

Abstract

is
is.com

Real:

is.com.troubleshooting
is.com.meeting
is.com.support.internal (support for internal teams)
is.com.support.external (support for external teams, customers, etc.)
is.com.training.visit   (visit   Trainings, Circular Lessons, Educational Services)  
is.com.training.perform (perform Trainings, Circular Lessons, Educational Services)  

is.man.meeting          (Management Meeting)
is.man.mor.tk           (Ticket Flow Reporting Project)
is.man.mor.ru           (Rack Utilization Reporting Project)
is.prj.hh.meeting       (Conference, Meeting) 
is.prj.hh.migration     (Customer Migration Work)

is.hosting.isod         (ISOD Tasks)
is.hosting.isow         (ISOW Tasks)

is.dev.ossp             (OSSP component development)

is.dev.openpkg.pmod     (OpenPKG PMOD/PSOD Tasks)
is.dev.openpkg.re       (OpenPKG release engineering)
is.dev.openpkg.dev      (OpenPKG package development)

Virtual:

com.meeting              *.com.meeting
com.support              *.com.support
com.support.internal     *.com.support.internal
com.support.external     *.com.support.external
prj.openpkg              is.dev.openpkg.*,!is.dev.openpkg.dev

Database Usage
==============

-- INSERT INTO log (entry) 
      VALUES ('...entry text...');
-- SELECT time,entry 
      FROM log 
      WHERE time >= 'yyyy-mm-dd' AND time < 'yyyy-mm-dd' 
      ORDER BY time DESC;
-- SELECT time,entry 
      FROM log 
      ORDER BY time DESC
      LIMIT 20 OFFSET 10;

@


1.2
log
@flush pending stuff
@
text
@d75 2
a76 79
Database
========

CREATE TABLE as_config (
    name         TEXT PRIMARY KEY,  -- variable name
    value        TEXT               -- variable value
);

CREATE TABLE as_user (
    id           SERIAL PRIMARY KEY,-- id (unique) [rse]
    name         TEXT,              -- name [Ralf S. Engelschall]
    passwd       TEXT,              -- password [qwert]
    group        TEXT,              -- group membership [development]
    email        TEXT,              -- email address [rse@@de.cw.com]
    home         TEXT,              -- homepage URL [http://dev.de.cw.net/~rse/]
    theme        TEXT,              -- WebUI theme [default]
    workhours    TEXT,              -- daily working hours [8]
    workdays     TEXT,              -- weekly working days [5]
    notify       TEXT,              -- whether user is notified about missing accounting [yes]
    notify_after TEXT,              -- hours after which user is notified [12]
    leader       TEXT,              -- leader of his group(s) [yes]
    admin        TEXT,              -- administrator of group (s) [yes]
);

CREATE TABLE as_theme (
    name         TEXT,              -- name of theme
    color_1_bg   CHAR(6),           -- RGB of color level #1 (background)
    color_1_fg   CHAR(6),           -- RGB of color level #1 (foreground)
    color_2_bg   CHAR(6),           -- RGB of color level #2 (background)
    color_2_fg   CHAR(6),           -- RGB of color level #2 (foreground)
    color_3_bg   CHAR(6),           -- RGB of color level #3 (background)
    color_3_fg   CHAR(6),           -- RGB of color level #3 (foreground)
    color_4_bg   CHAR(6),           -- RGB of color level #4 (background)
    color_4_fg   CHAR(6),           -- RGB of color level #4 (foreground)
    color_5_bg   CHAR(6),           -- RGB of color level #5 (background)
    color_5_fg   CHAR(6),           -- RGB of color level #5 (foreground)
);

CREATE TABLE as_session (
    session_id   TEXT PRIMARY KEY,  -- session id
    user_id      INTEGER NOT NULL,  -- associated user
    expire_time  TIMESTAMP (0) WITHOUT TIME ZONE NOT NULL -- expire time
);


CREATE TABLE as_reporting (
    id           TEXT PRIMARY KEY,  -- user id
    transitive   TEXT,              -- report receiver is allowed to re-report
    other        TEXT,              -- user id of receiver user
    account      TEXT,              -- root of reported accounts
);

CREATE TABLE as_group (
    id           TEXT PRIMARY KEY,   -- id (unique)
    name         TEXT,               -- short description of group
);

CREATE TABLE as_account (
    id           TEXT PRIMARY KEY,   -- unique key of account
    parent       TEXT,               -- parent account (account.id)
    name         TEXT,               -- name of account (non-unique)
    description  TEXT,               -- account description text
    active       TEXT,               -- whether account is active or not
    type         TEXT,               -- type of account: abstract, real, virtual
);

CREATE TABLE as_event (
    id           SERIAL PRIMARY KEY, -- auto-incrementing unique key of event
    date         DATE NOT NULL,      -- date of accounting
    minutes      INTEGER NOT NULL,   -- number of minutes accounted
    userid       INTEGER NOT NULL,   -- user submitting the event (user.id)
    account      INTEGER NOT NULL,   -- account receiving the event (account.id)
    remark       TEXT                -- optional remarks for accounted event
);

CREATE TABLE as_log (
    time         TIMESTAMP (2) WITHOUT TIME ZONE DEFAULT 'now', -- log time
    entry        TEXT NOT NULL       -- log entry
);
a87 1

@


1.1
log
@initial files
@
text
@d78 1
a78 1
CREATE TABLE config (
d83 2
a84 2
CREATE TABLE user (
    id           TEXT PRIMARY KEY,  -- id (unique) [rse]
d94 3
a96 3
    notify_after TEXT,              -- hours after which user is notified
    leader       TEXT,              -- leader of his group(s)
    admin        TEXT,              -- administrator of group (s)
d99 22
a120 1
CREATE TABLE reporting (
d127 39
a165 29
CREATE TABLE group (
    id           TEXT PRIMARY KEY,  -- id (unique)
    name         TEXT,              -- short description of group
);

CREATE TABLE account (
    id           TEXT PRIMARY KEY,  -- unique key of account
    parent       TEXT,              -- parent account (account.id)
    name         TEXT,              -- name of account (non-unique)
    description  TEXT,              -- account description text
    active       TEXT,              -- whether account is active or not
    type         TEXT,              -- type of account: abstract, real, virtual
);

CREATE TABLE events (
    id           TEXT PRIMARY KEY,  -- auto-incrementing unique key of event
    date         TEXT,              -- timestamp of event
    minutes      TEXT,              -- number of minutes accounted
    user         TEXT,              -- user submitting the event (user.id)
    account      TEXT,              -- account receiving the event (account.id)
);

CREATE TABLE history (
    id           TEXT PRIMARY KEY,  -- history id
    table        TEXT,              -- affected table
    key          TEXT,              -- affected row
    field        TEXT,              -- affected column
    value        TEXT,              -- affected value (new)
);
@