summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authoreinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2007-09-14 16:37:42 +0000
committereinhverfr <einhverfr@4979c152-3d1c-0410-bac9-87ea11338e46>2007-09-14 16:37:42 +0000
commit805f56b774cad6bfd788112841ff60d35bbf53f1 (patch)
tree48ed2500b8feb79d24b0a38f35653e60d0d702dc /sql
parent900bc54e6f3dafc2531afd89a187404cfe538217 (diff)
Applying David Morar's db script fixes
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1598 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'sql')
-rw-r--r--sql/Pg-database.sql115
1 files changed, 64 insertions, 51 deletions
diff --git a/sql/Pg-database.sql b/sql/Pg-database.sql
index 18cfb1d2..88e0d32f 100644
--- a/sql/Pg-database.sql
+++ b/sql/Pg-database.sql
@@ -5,24 +5,68 @@ CREATE SEQUENCE id;
-- in the ledger-smb.conf it is called 'ledgersmb' by default, but obviously
-- can be named anything.
+
+-- BEGIN new entity management
+CREATE TABLE entity (
+ id serial UNIQUE,
+ name text check (name ~ '[[:alnum:]_]'),
+ entity_class integer not null,
+ created date not null default current_date,
+ PRIMARY KEY(name,entity_class));
+
+COMMENT ON TABLE entity IS $$ The primary entity table to map to all contacts $$;
+COMMENT ON COLUMN entity.name IS $$ This is the common name of an entity. If it was a person it may be Joshua Drake, a company Acme Corp. You may also choose to use a domain such as commandprompt.com $$;
+
+CREATE TABLE entity_class (
+ id serial primary key,
+ class text check (class ~ '[[:alnum:]_]') NOT NULL,
+ active boolean not null default TRUE);
+
+COMMENT ON TABLE entity_class IS $$ Defines the class type such as vendor, customer, contact, employee $$;
+COMMENT ON COLUMN entity_class.id IS $$ The first 7 values are reserved and permanent $$;
+
+CREATE index entity_class_idx ON entity_class(lower(class));
+
+
+ALTER TABLE entity ADD FOREIGN KEY (entity_class) REFERENCES entity_class(id);
+
+INSERT INTO entity_class (id,class) VALUES (1,'Vendor');
+INSERT INTO entity_class (id,class) VALUES (2,'Customer');
+INSERT INTO entity_class (id,class) VALUES (3,'Employee');
+INSERT INTO entity_class (id,class) VALUES (4,'Contact');
+INSERT INTO entity_class (id,class) VALUES (5,'Lead');
+INSERT INTO entity_class (id,class) VALUES (6,'Referral');
+
+SELECT setval('entity_class_id_seq',7);
+
+CREATE TABLE entity_class_to_entity (
+ entity_class_id integer not null references entity_class(id) ON DELETE CASCADE,
+ entity_id integer not null references entity(id) ON DELETE CASCADE,
+ PRIMARY KEY(entity_class_id,entity_id)
+ );
+
+COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$;
+
+
+
-- USERS stuff --
CREATE TABLE users (
id serial UNIQUE,
- username varchar(30) primary key
- entity_id not null references entity(id) on delete cascade
+ username varchar(30) primary key,
+ entity_id int not null references entity(id) on delete cascade
);
COMMENT ON TABLE users IS $$username is the actual primary key here because we do not want duplicate users$$;
create table user_connection (
- user_id int not null references user(id) on delete cascade,
+ user_id int not null references users(id) on delete cascade,
dbname text not null,
host text not null default 'localhost',
port int not null default '5432'
);
-
+/*
CREATE VIEW users_conf as
select
- user.id,
+ users.id,
loc.address1 || '\n'|| loc.address2 ||'\n' || loc.address3,
em.employeenumber,
company,
@@ -30,20 +74,20 @@ CREATE VIEW users_conf as
currency,
dateformat,
'Pg',
- u_cx.host
+ u_cx.host,
u_cx.dbname,
u_cx.dbport,
- user.username,
+ users.username,
p.email,
p.fax,
50,
p.first_name || ' ' || p.last_name,
p.number_format,
- '', -- password
+ '', -- password
;
-
+*/
CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred,
acs text,
address text,
@@ -82,7 +126,16 @@ COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(
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(username) VALUES ('admin'); -- entity_id is declared NOT NULL !!
+
+
+%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%*/
+
+
+
INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL);
@@ -146,47 +199,7 @@ COMMENT ON column transactions.locked_by IS
$$ This should only be used in pessimistic locking measures as required by large
batch work flows. $$;
--- BEGIN new entity management
-CREATE TABLE entity (
- id serial UNIQUE,
- name text check (name ~ '[[:alnum:]_]'),
- entity_class integer not null,
- created date not null default current_date,
- PRIMARY KEY(name,entity_class));
-
-COMMENT ON TABLE entity IS $$ The primary entity table to map to all contacts $$;
-COMMENT ON COLUMN entity.name IS $$ This is the common name of an entity. If it was a person it may be Joshua Drake, a company Acme Corp. You may also choose to use a domain such as commandprompt.com $$;
-
-CREATE TABLE entity_class (
- id serial primary key,
- class text check (class ~ '[[:alnum:]_]') NOT NULL,
- active boolean not null default TRUE);
-
-COMMENT ON TABLE entity_class IS $$ Defines the class type such as vendor, customer, contact, employee $$;
-COMMENT ON COLUMN entity_class.id IS $$ The first 7 values are reserved and permanent $$;
-
-CREATE index entity_class_idx ON entity_class(lower(class));
-
-
-ALTER TABLE entity ADD FOREIGN KEY (entity_class) REFERENCES entity_class(id);
-
-INSERT INTO entity_class (id,class) VALUES (1,'Vendor');
-INSERT INTO entity_class (id,class) VALUES (2,'Customer');
-INSERT INTO entity_class (id,class) VALUES (3,'Employee');
-INSERT INTO entity_class (id,class) VALUES (4,'Contact');
-INSERT INTO entity_class (id,class) VALUES (5,'Lead');
-INSERT INTO entity_class (id,class) VALUES (6,'Referral');
-
-SELECT setval('entity_class_id_seq',7);
-
-CREATE TABLE entity_class_to_entity (
- entity_class_id integer not null references entity_class(id) ON DELETE CASCADE,
- entity_id integer not null references entity(id) ON DELETE CASCADE,
- PRIMARY KEY(entity_class_id,entity_id)
- );
-
-COMMENT ON TABLE entity_class_to_entity IS $$ Relation builder for classes to entity $$;
-
+-- LOCATION AND COUNTRY
CREATE TABLE country (
id serial PRIMARY KEY,
name text check (name ~ '[[:alnum:]_]') NOT NULL,