1begin;
2-- Central DB structure
3-- This is the central database stuff which is used across all datasets
4-- in the ledgersmb.conf it is called 'ledgersmb' by default, but obviously
5-- can be named anything.
6
7-- USERS stuff --
8CREATE TABLE users (id serial UNIQUE, username varchar(30) primary key);
9COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$;
10CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred,
11                        acs text,
12                        address text,
13                        businessnumber text,
14                        company text,
15                        countrycode text,
16                        currency text,
17                        dateformat text,
18                        dbdriver text default 'Pg',
19                        dbhost text default 'localhost',
20                        dbname text,
21                        dboptions text,
22                        dbpasswd text,
23                        dbport text,
24                        dbuser text,
25                        email text,
26                        fax text,
27                        menuwidth text,
28                        name text,
29                        numberformat text,
30                        password varchar(32) check(length(password) = 32),
31						crypted_password text,
32                        print text,
33                        printer text,
34                        role text,
35                        sid text,
36                        signature text,
37                        stylesheet text,
38                        tel text,
39                        templates text,
40                        timeout numeric,
41                        vclimit numeric);
42
43COMMENT ON TABLE users_conf IS 'This is a completely dumb table that is a place holder to get usersconf into the database. Next major release will have a much more sane implementation';
44COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct';
45COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()';
46
47-- The two below statements must be run from a single session
48INSERT INTO users(username) VALUES ('admin');
49INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL);
50
51
52CREATE OR REPLACE FUNCTION create_user(text) RETURNS bigint AS $$
53   INSERT INTO users(username) VALUES ($1);
54   SELECT currval('users_id_seq');
55   $$ LANGUAGE 'SQL';
56
57COMMENT ON FUNCTION create_user(text) IS $$ Function to create user. Returns users.id if successful, else it is an error. $$;
58
59CREATE OR REPLACE FUNCTION update_user(int4,text) RETURNS int4 AS $$
60   UPDATE users SET username = $2 WHERE id = $1;
61   SELECT 1;
62   $$ LANGUAGE 'SQL';
63
64COMMENT ON FUNCTION update_user(int4,text) IS $$ Takes int4 which is users.id and text which is username. Will update username based on id. Username is unique $$;
65
66
67-- Session tracking table
68
69
70CREATE TABLE session(
71session_id serial PRIMARY KEY,
72token VARCHAR(32) CHECK(length(token) = 32),
73last_used TIMESTAMP default now(),
74users_id INTEGER NOT NULL references users(id),
75transaction_id INTEGER NOT NULL
76);
77
78commit;
79