[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2421] trunk/sql/Pg-database.sql
- Subject: SF.net SVN: ledger-smb:[2421] trunk/sql/Pg-database.sql
- From: ..hidden..
- Date: Tue, 25 Nov 2008 18:53:02 +0000
Revision: 2421
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2421&view=rev
Author: einhverfr
Date: 2008-11-25 18:53:02 +0000 (Tue, 25 Nov 2008)
Log Message:
-----------
undoing accidental deletion to good chunk of Pg-database.sql
Modified Paths:
--------------
trunk/sql/Pg-database.sql
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2008-11-25 06:53:38 UTC (rev 2420)
+++ trunk/sql/Pg-database.sql 2008-11-25 18:53:02 UTC (rev 2421)
@@ -1,4 +1,426 @@
-defaults (
+begin;
+CREATE SEQUENCE id;
+-- As of 1.3 there is no central db anymore. --CT
+
+CREATE TABLE chart (
+ id serial PRIMARY KEY,
+ accno text NOT NULL,
+ description text,
+ charttype char(1) DEFAULT 'A',
+ category char(1),
+ link text,
+ gifi_accno text,
+ contra bool DEFAULT 'f'
+);
+--
+-- pricegroup added here due to references
+CREATE TABLE pricegroup (
+ id serial PRIMARY KEY,
+ pricegroup text
+);
+
+-- BEGIN new entity management
+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));
+
+CREATE TABLE entity (
+ id serial UNIQUE,
+ name text check (name ~ '[[:alnum:]_]'),
+ entity_class integer references entity_class(id) not null ,
+ created date not null default current_date,
+ control_code text,
+ PRIMARY KEY(control_code, 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 $$;
+
+
+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 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$$;
+
+-- Session tracking table
+
+
+CREATE TABLE session(
+session_id serial PRIMARY KEY,
+token VARCHAR(32) CHECK(length(token) = 32),
+last_used TIMESTAMP default now(),
+ttl int default 3600 not null,
+users_id INTEGER NOT NULL references users(id),
+transaction_id INTEGER NOT NULL
+);
+
+CREATE TABLE open_forms (
+id SERIAL PRIMARY KEY,
+session_id int REFERENCES session(session_id) ON DELETE CASCADE
+);
+
+--
+CREATE TABLE transactions (
+ id int PRIMARY KEY,
+ table_name text,
+ locked_by int references "session" (session_id) ON DELETE SET NULL,
+ approved_by int references entity (id),
+ approved_at timestamp
+);
+
+COMMENT on TABLE transactions IS
+$$ This table tracks basic transactions across AR, AP, and GL related tables.
+It provies a referential integrity enforcement mechanism for the financial data
+and also some common features such as discretionary (and pessimistic) locking
+for long batch workflows. $$;
+
+CREATE OR REPLACE FUNCTION lock_record (int, int) returns bool as
+$$
+declare
+ locked int;
+begin
+ SELECT locked_by into locked from transactions where id = $1;
+ IF NOT FOUND THEN
+ RETURN FALSE;
+ ELSEIF locked is not null AND locked <> $2 THEN
+ RETURN FALSE;
+ END IF;
+ UPDATE transactions set locked_by = $2 where id = $1;
+ RETURN TRUE;
+end;
+$$ language plpgsql;
+
+COMMENT ON column transactions.locked_by IS
+$$ This should only be used in pessimistic locking measures as required by large
+batch work flows. $$;
+
+-- LOCATION AND COUNTRY
+CREATE TABLE country (
+ id serial PRIMARY KEY,
+ name text check (name ~ '[[:alnum:]_]') NOT NULL,
+ short_name text check (short_name ~ '[[:alnum:]_]') NOT NULL,
+ itu text);
+
+COMMENT ON COLUMN country.itu IS $$ The ITU Telecommunication Standardization Sector code for calling internationally. For example, the US is 1, Great Britain is 44 $$;
+
+CREATE UNIQUE INDEX country_name_idx on country(lower(name));
+
+CREATE TABLE location_class (
+ id serial UNIQUE,
+ class text check (class ~ '[[:alnum:]_]') not null,
+ authoritative boolean not null,
+ PRIMARY KEY (class,authoritative));
+
+CREATE UNIQUE INDEX lower_class_unique ON location_class(lower(class));
+
+INSERT INTO location_class(id,class,authoritative) VALUES ('1','Billing',TRUE);
+INSERT INTO location_class(id,class,authoritative) VALUES ('2','Sales',TRUE);
+INSERT INTO location_class(id,class,authoritative) VALUES ('3','Shipping',TRUE);
+
+SELECT SETVAL('location_class_id_seq',4);
+
+CREATE TABLE location (
+ id serial PRIMARY KEY,
+ line_one text check (line_one ~ '[[:alnum:]_]') NOT NULL,
+ line_two text,
+ line_three text,
+ city text check (city ~ '[[:alnum:]_]') NOT NULL,
+ state text check(state ~ '[[:alnum:]_]'),
+ country_id integer not null REFERENCES country(id),
+ mail_code text not null check (mail_code ~ '[[:alnum:]_]'),
+ created date not null default now(),
+ inactive_date timestamp default null,
+ active boolean not null default TRUE
+);
+
+CREATE TABLE company (
+ id serial UNIQUE,
+ entity_id integer not null references entity(id),
+ legal_name text check (legal_name ~ '[[:alnum:]_]'),
+ tax_id text,
+ created date default current_date not null,
+ PRIMARY KEY (entity_id,legal_name));
+
+COMMENT ON COLUMN company.tax_id IS $$ In the US this would be a EIN. $$;
+
+CREATE TABLE company_to_location (
+ location_id integer references location(id) not null,
+ location_class integer not null references location_class(id),
+ company_id integer not null references company(id) ON DELETE CASCADE,
+ PRIMARY KEY(location_id,company_id, location_class));
+
+COMMENT ON TABLE company_to_location IS
+$$ This table is used for locations generic to companies. For contract-bound
+addresses, use eca_to_location instead $$;
+
+CREATE TABLE salutation (
+ id serial unique,
+ salutation text primary key);
+
+INSERT INTO salutation (id,salutation) VALUES ('1','Dr.');
+INSERT INTO salutation (id,salutation) VALUES ('2','Miss.');
+INSERT INTO salutation (id,salutation) VALUES ('3','Mr.');
+INSERT INTO salutation (id,salutation) VALUES ('4','Mrs.');
+INSERT INTO salutation (id,salutation) VALUES ('5','Ms.');
+INSERT INTO salutation (id,salutation) VALUES ('6','Sir.');
+
+SELECT SETVAL('salutation_id_seq',7);
+
+CREATE TABLE person (
+ id serial PRIMARY KEY,
+ entity_id integer references entity(id) not null,
+ salutation_id integer references salutation(id),
+ first_name text check (first_name ~ '[[:alnum:]_]') NOT NULL,
+ middle_name text,
+ last_name text check (last_name ~ '[[:alnum:]_]') NOT NULL,
+ created date not null default current_date
+ );
+
+COMMENT ON TABLE person IS $$ Every person, must have an entity to derive a common or display name. The correct way to get class information on a person would be person.entity_id->entity_class_to_entity.entity_id. $$;
+
+create table entity_employee (
+
+ person_id integer references person(id) not null,
+ entity_id integer references entity(id) not null unique,
+ startdate date not null default current_date,
+ enddate date,
+ role varchar(20),
+ ssn text,
+ sales bool default 'f',
+ manager_id integer references entity(id),
+ employeenumber varchar(32),
+ dob date,
+ PRIMARY KEY (person_id, entity_id)
+);
+
+CREATE TABLE person_to_location (
+ location_id integer not null references location(id),
+ location_class integer not null references location_class(id),
+ person_id integer not null references person(id) ON DELETE CASCADE,
+ PRIMARY KEY (location_id,person_id));
+
+CREATE TABLE person_to_company (
+ location_id integer references location(id) not null,
+ person_id integer not null references person(id) ON DELETE CASCADE,
+ company_id integer not null references company(id) ON DELETE CASCADE,
+ PRIMARY KEY (location_id,person_id));
+
+CREATE TABLE entity_other_name (
+ entity_id integer not null references entity(id) ON DELETE CASCADE,
+ other_name text check (other_name ~ '[[:alnum:]_]'),
+ PRIMARY KEY (other_name, entity_id));
+
+COMMENT ON TABLE entity_other_name IS $$ Similar to company_other_name, a person may be jd, Joshua Drake, linuxpoet... all are the same person. $$;
+
+CREATE TABLE person_to_entity (
+ person_id integer not null references person(id) ON DELETE CASCADE,
+ entity_id integer not null check (entity_id != person_id) references entity(id) ON DELETE CASCADE,
+ related_how text,
+ created date not null default current_date,
+ PRIMARY KEY (person_id,entity_id));
+
+CREATE TABLE company_to_entity (
+ company_id integer not null references company(id) ON DELETE CASCADE,
+ entity_id integer check (company_id != entity_id) not null references entity(id) ON DELETE CASCADE,
+ related_how text,
+ created date not null default current_date,
+ PRIMARY KEY (company_id,entity_id));
+
+CREATE TABLE contact_class (
+ id serial UNIQUE,
+ class text check (class ~ '[[:alnum:]_]') NOT NULL,
+ PRIMARY KEY (class));
+
+CREATE UNIQUE INDEX contact_class_class_idx ON contact_class(lower(class));
+
+INSERT INTO contact_class (id,class) values (1,'Primary Phone');
+INSERT INTO contact_class (id,class) values (2,'Secondary Phone');
+INSERT INTO contact_class (id,class) values (3,'Cell Phone');
+INSERT INTO contact_class (id,class) values (4,'AIM');
+INSERT INTO contact_class (id,class) values (5,'Yahoo');
+INSERT INTO contact_class (id,class) values (6,'Gtalk');
+INSERT INTO contact_class (id,class) values (7,'MSN');
+INSERT INTO contact_class (id,class) values (8,'IRC');
+INSERT INTO contact_class (id,class) values (9,'Fax');
+INSERT INTO contact_class (id,class) values (10,'Generic Jabber');
+INSERT INTO contact_class (id,class) values (11,'Home Phone');
+INSERT INTO contact_class (id,class) values (12,'Email');
+
+SELECT SETVAL('contact_class_id_seq',12);
+
+CREATE TABLE person_to_contact (
+ person_id integer not null references person(id) ON DELETE CASCADE,
+ contact_class_id integer references contact_class(id) not null,
+ contact text check(contact ~ '[[:alnum:]_]') not null,
+ PRIMARY KEY (person_id,contact_class_id,contact));
+
+COMMENT ON TABLE person_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single individual $$;
+
+CREATE TABLE company_to_contact (
+ company_id integer not null references company(id) ON DELETE CASCADE,
+ contact_class_id integer references contact_class(id) not null,
+ contact text check(contact ~ '[[:alnum:]_]') not null,
+ description text,
+ PRIMARY KEY (company_id, contact_class_id, contact));
+
+COMMENT ON TABLE company_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single company $$;
+
+CREATE TABLE entity_bank_account (
+ id serial not null,
+ entity_id int not null references entity(id) ON DELETE CASCADE,
+ bic varchar,
+ iban varchar,
+ UNIQUE (id),
+ PRIMARY KEY (entity_id, bic, iban)
+);
+
+CREATE TABLE entity_credit_account (
+ id serial not null unique,
+ entity_id int not null references entity(id) ON DELETE CASCADE,
+ entity_class int not null references entity_class(id) check ( entity_class in (1,2) ),
+ discount numeric,
+ description text,
+ discount_terms int default 0,
+ discount_account_id int references chart(id),
+ taxincluded bool default 'f',
+ creditlimit NUMERIC default 0,
+ terms int2 default 0,
+ meta_number varchar(32),
+ cc text,
+ bcc text,
+ business_id int,
+ language_code varchar(6),
+ pricegroup_id int references pricegroup(id),
+ curr char(3),
+ startdate date DEFAULT CURRENT_DATE,
+ enddate date,
+ threshold numeric default 0,
+ employee_id int references entity_employee(entity_id),
+ primary_contact int references person(id),
+ ar_ap_account_id int references chart(id),
+ cash_account_id int references chart(id),
+ bank_account int references entity_bank_account(id),
+ PRIMARY KEY(entity_id, meta_number, entity_class)
+);
+
+CREATE UNIQUE INDEX entity_credit_ar_accno_idx_u
+ON entity_credit_account(meta_number)
+WHERE entity_class = 2;
+
+COMMENT ON INDEX entity_credit_ar_accno_idx_u IS
+$$This index is used to ensure that AR accounts are not reused.$$;
+
+CREATE TABLE eca_to_contact (
+ credit_id integer not null references entity_credit_account(id)
+ ON DELETE CASCADE,
+ contact_class_id integer references contact_class(id) not null,
+ contact text check(contact ~ '[[:alnum:]_]') not null,
+ description text,
+ PRIMARY KEY (credit_id, contact_class_id, contact));
+
+COMMENT ON TABLE eca_to_contact IS $$ To keep track of the relationship between multiple contact methods and a single vendor or customer account. For generic
+contacts, use company_to_contact or person_to_contact instead.$$;
+
+CREATE TABLE eca_to_location (
+ location_id integer references location(id) not null,
+ location_class integer not null references location_class(id),
+ credit_id integer not null references entity_credit_account(id)
+ ON DELETE CASCADE,
+ PRIMARY KEY(location_id,credit_id));
+
+CREATE UNIQUE INDEX eca_to_location_billing_u ON eca_to_location(credit_id)
+ WHERE location_class = 1;
+
+COMMENT ON TABLE eca_to_location IS
+$$ This table is used for locations bound to contracts. For generic contact
+addresses, use company_to_location instead $$;
+
+-- Begin rocking notes interface
+-- Begin rocking notes interface
+CREATE TABLE note_class(id serial primary key, class text not null check (class ~ '[[:alnum:]_]'));
+INSERT INTO note_class(id,class) VALUES (1,'Entity');
+INSERT INTO note_class(id,class) VALUES (2,'Invoice');
+INSERT INTO note_class(id,class) VALUES (3,'Entity Credit Account');
+CREATE UNIQUE INDEX note_class_idx ON note_class(lower(class));
+
+CREATE TABLE note (id serial primary key, note_class integer not null references note_class(id),
+ note text not null, vector tsvector not null,
+ created timestamp not null default now(),
+ created_by text DEFAULT SESSION_USER,
+ ref_key integer not null);
+
+CREATE TABLE entity_note(entity_id int references entity(id)) INHERITS (note);
+ALTER TABLE entity_note ADD CHECK (note_class = 1);
+ALTER TABLE entity_note ADD FOREIGN KEY (ref_key) REFERENCES entity(id) ON DELETE CASCADE;
+CREATE INDEX entity_note_id_idx ON entity_note(id);
+CREATE UNIQUE INDEX entity_note_class_idx ON note_class(lower(class));
+CREATE INDEX entity_note_vectors_idx ON entity_note USING gist(vector);
+CREATE TABLE invoice_note() INHERITS (note);
+CREATE INDEX invoice_note_id_idx ON invoice_note(id);
+CREATE UNIQUE INDEX invoice_note_class_idx ON note_class(lower(class));
+CREATE INDEX invoice_note_vectors_idx ON invoice_note USING gist(vector);
+
+CREATE TABLE eca_note()
+ INHERITS (note);
+ALTER TABLE eca_note ADD CHECK (note_class = 3);
+ALTER TABLE eca_note ADD FOREIGN KEY (ref_key)
+ REFERENCES entity_credit_account(id)
+ ON DELETE CASCADE;
+
+-- END entity
+
+--
+CREATE TABLE makemodel (
+ parts_id int PRIMARY KEY,
+ make text,
+ model text
+);
+--
+CREATE TABLE gl (
+ id int DEFAULT nextval ( 'id' ) PRIMARY KEY REFERENCES transactions(id),
+ reference text,
+ description text,
+ transdate date DEFAULT current_date,
+ person_id integer references person(id),
+ notes text,
+ approved bool default true,
+ department_id int default 0
+);
+--
+CREATE TABLE gifi (
+ accno text PRIMARY KEY,
+ description text
+);
+--
+CREATE TABLE defaults (
setting_key text primary key,
value text
);
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.