From ac5b087ea2d9ba7428d367aaeb288534158fee9a Mon Sep 17 00:00:00 2001 From: christopherm Date: Fri, 1 Sep 2006 01:16:38 +0000 Subject: Initial Import git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/ledger-smb@1 4979c152-3d1c-0410-bac9-87ea11338e46 --- sql/DB2-tables.sql | 511 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 511 insertions(+) create mode 100755 sql/DB2-tables.sql (limited to 'sql/DB2-tables.sql') diff --git a/sql/DB2-tables.sql b/sql/DB2-tables.sql new file mode 100755 index 00000000..3ac77999 --- /dev/null +++ b/sql/DB2-tables.sql @@ -0,0 +1,511 @@ +-- DB2-tables.sql +-- Bill Ott modified from Oracle tables, March 02, 2002 +-- +-- Jim Rawlings modified for use with SL 2.0.8 and DB2 v7.2 +-- and higher August 27, 2003 +-- +-- +--------------------------------------------------------- +-- DDL Statements for sequence id +--------------------------------------------------------- +CREATE SEQUENCE id AS INTEGER START WITH 10000 +INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 5 +@ +--------------------------------------------------------- +-- DDL Statements for table makemodel +--------------------------------------------------------- +CREATE TABLE makemodel ( + parts_id INTEGER, + name VARCHAR(100) +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table gl +--------------------------------------------------------- +CREATE TABLE gl ( + id INTEGER, + reference VARCHAR(50), + description VARCHAR(100), + transdate DATE WITH DEFAULT current date, + employee_id INTEGER, + notes VARCHAR(4000) +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table chart +--------------------------------------------------------- +CREATE TABLE chart ( + id INTEGER, + accno VARCHAR(20) NOT NULL, + description VARCHAR(100), + charttype CHAR(1) WITH DEFAULT 'A', + category CHAR(1), + link VARCHAR(100), + gifi_accno VARCHAR(20) +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table gifi +--------------------------------------------------------- +CREATE TABLE gifi ( + accno VARCHAR(20), + description VARCHAR(100) +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table defaults +--------------------------------------------------------- +CREATE TABLE defaults ( + inventory_accno_id INTEGER, + income_accno_id INTEGER, + expense_accno_id INTEGER, + fxgain_accno_id INTEGER, + fxloss_accno_id INTEGER, + invnumber VARCHAR(30), + sonumber VARCHAR(30), + yearend VARCHAR(5), + weightunit VARCHAR(5), + businessnumber VARCHAR(30), + version VARCHAR(8), + curr VARCHAR(500), + closedto DATE, + revtrans CHAR(1) WITH DEFAULT '0', + ponumber VARCHAR(30) +) IN LEDGER_TS +@ +INSERT INTO defaults (version) VALUES ('2.0.10') +@ +--------------------------------------------------------- +-- DDL Statements for table acc_trans +--------------------------------------------------------- +CREATE TABLE acc_trans ( + trans_id INTEGER, + chart_id INTEGER, + amount FLOAT, + transdate DATE WITH DEFAULT current date, + source VARCHAR(20), + cleared CHAR(1) WITH DEFAULT '0', + fx_transaction CHAR(1) WITH DEFAULT '0', + project_id INTEGER +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table invoice +--------------------------------------------------------- +CREATE TABLE invoice ( + id INTEGER, + trans_id INTEGER, + parts_id INTEGER, + description VARCHAR(4000), + qty FLOAT, + allocated FLOAT, + sellprice FLOAT, + fxsellprice FLOAT, + discount FLOAT, + assemblyitem CHAR(1) WITH DEFAULT '0', + unit VARCHAR(5), + project_id INTEGER, + deliverydate DATE +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table vendor +--------------------------------------------------------- +CREATE TABLE vendor ( + id INTEGER, + name VARCHAR(35), + addr1 VARCHAR(35), + addr2 VARCHAR(35), + addr3 VARCHAR(35), + addr4 VARCHAR(35), + contact VARCHAR(35), + phone VARCHAR(20), + fax VARCHAR(20), + email VARCHAR(50), + notes VARCHAR(4000), + terms INTEGER WITH DEFAULT, + taxincluded CHAR(1), + vendornumber VARCHAR(40), + cc VARCHAR(50), + bcc VARCHAR(50) +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table customer +--------------------------------------------------------- +CREATE TABLE customer ( + id INTEGER, + name VARCHAR(35), + addr1 VARCHAR(35), + addr2 VARCHAR(35), + addr3 VARCHAR(35), + addr4 VARCHAR(35), + contact VARCHAR(35), + phone VARCHAR(20), + fax VARCHAR(20), + email VARCHAR(50), + notes VARCHAR(4000), + discount FLOAT, + taxincluded CHAR(1), + creditlimit FLOAT, + terms INTEGER WITH DEFAULT, + customernumber VARCHAR(40), + cc VARCHAR(50), + bcc VARCHAR(50) +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table parts +--------------------------------------------------------- +CREATE TABLE parts ( + id INTEGER, + partnumber VARCHAR(30), + description VARCHAR(4000), + unit VARCHAR(5), + listprice FLOAT, + sellprice FLOAT, + lastcost FLOAT, + priceupdate DATE WITH DEFAULT current date, + weight FLOAT, + onhand FLOAT WITH DEFAULT 0, + notes VARCHAR(1500), + makemodel CHAR(1) WITH DEFAULT '0', + assembly CHAR(1) WITH DEFAULT '0', + alternate CHAR(1) WITH DEFAULT '0', + rop FLOAT, + inventory_accno_id INTEGER, + income_accno_id INTEGER, + expense_accno_id INTEGER, + bin VARCHAR(20), + obsolete CHAR(1) WITH DEFAULT '0', + bom CHAR(1) WITH DEFAULT '0', + image VARCHAR(100), + drawing VARCHAR(100), + microfiche VARCHAR(100), + partsgroup_id INTEGER +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table assembly +--------------------------------------------------------- +CREATE TABLE assembly ( + id INTEGER, + parts_id INTEGER, + qty FLOAT, + bom CHAR(1) +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table ar +--------------------------------------------------------- +CREATE TABLE ar ( + id INTEGER, + invnumber VARCHAR(30), + transdate DATE WITH DEFAULT current date, + customer_id INTEGER, + taxincluded CHAR(1), + amount FLOAT, + netamount FLOAT, + paid FLOAT, + datepaid DATE, + duedate DATE, + invoice CHAR(1) WITH DEFAULT '0', + shippingpoint VARCHAR(100), + terms INTEGER WITH DEFAULT 0, + notes VARCHAR(4000), + curr CHAR(3), + ordnumber VARCHAR(30), + employee_id INTEGER +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table ap +--------------------------------------------------------- +CREATE TABLE ap ( + id INTEGER, + invnumber VARCHAR(30), + transdate DATE WITH DEFAULT current date, + vendor_id INTEGER, + taxincluded CHAR(1) WITH DEFAULT '0', + amount FLOAT, + netamount FLOAT, + paid FLOAT, + datepaid DATE, + duedate DATE, + invoice CHAR(1) WITH DEFAULT '0', + ordnumber VARCHAR(30), + curr CHAR(3), + notes VARCHAR(4000), + employee_id INTEGER +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table partstax +--------------------------------------------------------- +CREATE TABLE partstax ( + parts_id INTEGER, + chart_id INTEGER +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table tax +--------------------------------------------------------- +CREATE TABLE tax ( + chart_id INTEGER, + rate FLOAT, + taxnumber VARCHAR(30) +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table customertax +--------------------------------------------------------- +CREATE TABLE customertax ( + customer_id INTEGER, + chart_id INTEGER +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table vendortax +--------------------------------------------------------- +CREATE TABLE vendortax ( + vendor_id INTEGER, + chart_id INTEGER +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table oe +--------------------------------------------------------- +CREATE TABLE oe ( + id INTEGER, + ordnumber VARCHAR(30), + transdate DATE WITH DEFAULT current date, + vendor_id INTEGER, + customer_id INTEGER, + amount FLOAT, + netamount FLOAT, + reqdate DATE, + taxincluded CHAR(1), + shippingpoint VARCHAR(100), + notes VARCHAR(4000), + curr CHAR(3), + employee_id INTEGER, + closed CHAR(1) WITH DEFAULT '0' +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table orderitems +--------------------------------------------------------- +CREATE TABLE orderitems ( + trans_id INTEGER, + parts_id INTEGER, + description VARCHAR(4000), + qty FLOAT, + sellprice FLOAT, + discount FLOAT, + unit VARCHAR(5), + project_id INTEGER, + reqdate DATE +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table exchangerate +--------------------------------------------------------- +CREATE TABLE exchangerate ( + curr CHAR(3), + transdate DATE, + buy FLOAT, + sell FLOAT +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table employee +--------------------------------------------------------- +CREATE TABLE employee ( + id INTEGER, + login VARCHAR(20), + name VARCHAR(35), + addr1 VARCHAR(35), + addr2 VARCHAR(35), + addr3 VARCHAR(35), + addr4 VARCHAR(35), + workphone VARCHAR(20), + homephone VARCHAR(20), + startdate DATE WITH DEFAULT current date, + enddate DATE, + notes VARCHAR(4000) +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table shipto +--------------------------------------------------------- +CREATE TABLE shipto ( + trans_id INTEGER, + shiptoname VARCHAR(35), + shiptoaddr1 VARCHAR(35), + shiptoaddr2 VARCHAR(35), + shiptoaddr3 VARCHAR(35), + shiptoaddr4 VARCHAR(35), + shiptocontact VARCHAR(35), + shiptophone VARCHAR(20), + shiptofax VARCHAR(20), + shiptoemail VARCHAR(50) +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table project +--------------------------------------------------------- +CREATE TABLE project ( + id INTEGER, + projectnumber VARCHAR(50), + description VARCHAR(4000) +) IN LEDGER_TS +@ +--------------------------------------------------------- +-- DDL Statements for table partsgroup +--------------------------------------------------------- +CREATE TABLE partsgroup ( + id INTEGER, + partsgroup VARCHAR(100) +) IN LEDGER_TS +@ +--------------------------------------------------------- +--!# +--!# functions N/A +--!# +--------------------------------------------------------- +--!# +--!# triggers +--!# +--------------------------------------------------------- +-- DDL Statements for trigger glid +--------------------------------------------------------- +CREATE TRIGGER glid +NO CASCADE BEFORE INSERT ON gl +REFERENCING NEW AS new_id +FOR EACH ROW MODE DB2SQL +BEGIN ATOMIC +set new_id.id = NEXTVAL FOR id; +END +@ +--------------------------------------------------------- +-- DDL Statements for trigger chartid +--------------------------------------------------------- +CREATE TRIGGER chartid +NO CASCADE BEFORE INSERT ON chart +REFERENCING NEW AS new_id +FOR EACH ROW MODE DB2SQL +BEGIN ATOMIC +set new_id.id = NEXTVAL FOR id; +END +@ +--------------------------------------------------------- +-- DDL Statements for trigger invoiceid +--------------------------------------------------------- +CREATE TRIGGER invoiceid +NO CASCADE BEFORE INSERT ON invoice +REFERENCING NEW AS new_id +FOR EACH ROW MODE DB2SQL +BEGIN ATOMIC +set new_id.id = NEXTVAL FOR id; +END +@ +--------------------------------------------------------- +-- DDL Statements for trigger vendorid +--------------------------------------------------------- +CREATE TRIGGER vendorid +NO CASCADE BEFORE INSERT ON vendor +REFERENCING NEW AS new_id +FOR EACH ROW MODE DB2SQL +BEGIN ATOMIC +set new_id.id = NEXTVAL FOR id; +END +@ +--------------------------------------------------------- +-- DDL Statements for trigger customerid +--------------------------------------------------------- +CREATE TRIGGER customerid +NO CASCADE BEFORE INSERT ON customer +REFERENCING NEW AS new_id +FOR EACH ROW MODE DB2SQL +BEGIN ATOMIC +set new_id.id = NEXTVAL FOR id; +END +@ +--------------------------------------------------------- +-- DDL Statements for trigger partsid +--------------------------------------------------------- +CREATE TRIGGER partsid +NO CASCADE BEFORE INSERT ON parts +REFERENCING NEW AS new_id +FOR EACH ROW MODE DB2SQL +BEGIN ATOMIC +set new_id.id = NEXTVAL FOR id; +END +@ +--------------------------------------------------------- +-- DDL Statements for trigger arid +--------------------------------------------------------- +CREATE TRIGGER arid +NO CASCADE BEFORE INSERT ON ar +REFERENCING NEW AS new_id +FOR EACH ROW MODE DB2SQL +BEGIN ATOMIC +set new_id.id = NEXTVAL FOR id; +END +@ +--------------------------------------------------------- +-- DDL Statements for trigger apid +--------------------------------------------------------- +CREATE TRIGGER apid +NO CASCADE BEFORE INSERT ON ap +REFERENCING NEW AS new_id +FOR EACH ROW MODE DB2SQL +BEGIN ATOMIC +set new_id.id = NEXTVAL FOR id; +END +@ +--------------------------------------------------------- +-- DDL Statements for trigger oeid +--------------------------------------------------------- +CREATE TRIGGER oeid +NO CASCADE BEFORE INSERT ON oe +REFERENCING NEW AS new_id +FOR EACH ROW MODE DB2SQL +BEGIN ATOMIC +set new_id.id = NEXTVAL FOR id; +END +@ +--------------------------------------------------------- +-- DDL Statements for trigger employeeid +--------------------------------------------------------- +CREATE TRIGGER employeeid +NO CASCADE BEFORE INSERT ON employee +REFERENCING NEW AS new_id +FOR EACH ROW MODE DB2SQL +BEGIN ATOMIC +set new_id.id = NEXTVAL FOR id; +END +@ +--------------------------------------------------------- +-- DDL Statements for trigger projectid +--------------------------------------------------------- +CREATE TRIGGER projectid +NO CASCADE BEFORE INSERT ON project +REFERENCING NEW AS new_id +FOR EACH ROW MODE DB2SQL +BEGIN ATOMIC +set new_id.id = NEXTVAL FOR id; +END +@ +--------------------------------------------------------- +-- DDL Statements for trigger partsgroupid +--------------------------------------------------------- +CREATE TRIGGER partsgroupid +NO CASCADE BEFORE INSERT ON partsgroup +REFERENCING NEW AS new_id +FOR EACH ROW MODE DB2SQL +BEGIN ATOMIC +set new_id.id = NEXTVAL FOR id; +END@ + + -- cgit v1.2.3