summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rwxr-xr-xsql/Pg-central.sql78
-rw-r--r--sql/Pg-database.sql76
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,