diff options
Diffstat (limited to 'sql')
| -rwxr-xr-x | sql/Pg-central.sql | 78 | ||||
| -rw-r--r-- | sql/Pg-database.sql | 76 |
2 files changed, 76 insertions, 78 deletions
diff --git a/sql/Pg-central.sql b/sql/Pg-central.sql deleted file mode 100755 index 15c16ad7..00000000 --- a/sql/Pg-central.sql +++ /dev/null @@ -1,78 +0,0 @@ -begin; --- Central DB structure --- This is the central database stuff which is used across all datasets --- in the ledger-smb.conf it is called 'ledgersmb' by default, but obviously --- can be named anything. - --- USERS stuff -- -CREATE TABLE users (id serial UNIQUE, username varchar(30) primary key); -COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$; -CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred, - acs text, - address text, - businessnumber text, - company text, - countrycode text, - currency text, - dateformat text, - dbdriver text default 'Pg', - dbhost text default 'localhost', - dbname text, - dboptions text, - dbpasswd text, - dbport text, - dbuser text, - email text, - fax text, - menuwidth text, - name text, - numberformat text, - password varchar(32) check(length(password) = 32), - crypted_password text, - print text, - printer text, - role text, - sid text, - signature text, - stylesheet text, - tel text, - templates text, - timeout numeric, - vclimit numeric); - -COMMENT 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'; -COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct'; -COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()'; - --- The two below statements must be run from a single session -INSERT INTO users(username) VALUES ('admin'); -INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL); - - -CREATE OR REPLACE FUNCTION create_user(text) RETURNS bigint AS $$ - INSERT INTO users(username) VALUES ($1); - SELECT currval('users_id_seq'); - $$ LANGUAGE 'SQL'; - -COMMENT ON FUNCTION create_user(text) IS $$ Function to create user. Returns users.id if successful, else it is an error. $$; - -CREATE OR REPLACE FUNCTION update_user(int4,text) RETURNS int4 AS $$ - UPDATE users SET username = $2 WHERE id = $1; - SELECT 1; - $$ LANGUAGE 'SQL'; - -COMMENT 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 $$; - - --- Session tracking table - - -CREATE TABLE session( -session_id serial PRIMARY KEY, -token VARCHAR(32) CHECK(length(token) = 32), -last_used TIMESTAMP default now(), -users_id INTEGER NOT NULL references users(id), -transaction_id INTEGER NOT NULL -); - -commit; diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql index 131ad2b6..6bf243f8 100644 --- a/sql/Pg-database.sql +++ b/sql/Pg-database.sql @@ -1,4 +1,80 @@ begin; +-- Central DB structure +-- This is the central database stuff which is used across all datasets +-- in the ledger-smb.conf it is called 'ledgersmb' by default, but obviously +-- can be named anything. + +-- USERS stuff -- +CREATE TABLE users (id serial UNIQUE, username varchar(30) primary key); +COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$; +CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred, + acs text, + address text, + businessnumber text, + company text, + countrycode text, + currency text, + dateformat text, + dbdriver text default 'Pg', + dbhost text default 'localhost', + dbname text, + dboptions text, + dbpasswd text, + dbport text, + dbuser text, + email text, + fax text, + menuwidth text, + name text, + numberformat text, + password varchar(32) check(length(password) = 32), + crypted_password text, + print text, + printer text, + role text, + sid text, + signature text, + stylesheet text, + tel text, + templates text, + timeout numeric, + vclimit numeric); + +COMMENT 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'; +COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct'; +COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()'; + +-- The two below statements must be run from a single session +INSERT INTO users(username) VALUES ('admin'); +INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL); + + +CREATE OR REPLACE FUNCTION create_user(text) RETURNS bigint AS $$ + INSERT INTO users(username) VALUES ($1); + SELECT currval('users_id_seq'); + $$ LANGUAGE 'SQL'; + +COMMENT ON FUNCTION create_user(text) IS $$ Function to create user. Returns users.id if successful, else it is an error. $$; + +CREATE OR REPLACE FUNCTION update_user(int4,text) RETURNS int4 AS $$ + UPDATE users SET username = $2 WHERE id = $1; + SELECT 1; + $$ LANGUAGE 'SQL'; + +COMMENT 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 $$; + + +-- Session tracking table + + +CREATE TABLE session( +session_id serial PRIMARY KEY, +token VARCHAR(32) CHECK(length(token) = 32), +last_used TIMESTAMP default now(), +users_id INTEGER NOT NULL references users(id), +transaction_id INTEGER NOT NULL +); + -- CREATE TABLE transactions ( id int PRIMARY KEY, |