[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [47] trunk/sql
- Subject: SF.net SVN: ledger-smb: [47] trunk/sql
- From: ..hidden..
- Date: Sat, 09 Sep 2006 10:20:14 -0700
Revision: 47
http://svn.sourceforge.net/ledger-smb/?rev=47&view=rev
Author: linuxpoet
Date: 2006-09-09 10:20:12 -0700 (Sat, 09 Sep 2006)
Log Message:
-----------
Remove all non Pg Ports, they were unsupported and LedgerSMB is a Pg only project now.
Removed Paths:
-------------
trunk/sql/DB2-create.sql
trunk/sql/DB2-indices.sql
trunk/sql/DB2-remove.sql
trunk/sql/DB2-set.sql
trunk/sql/DB2-sql-ledger.order
trunk/sql/DB2-tables.sql
trunk/sql/Oracle-indices.sql
trunk/sql/Oracle-tables.sql
trunk/sql/Oracle-upgrade-1.8.0-1.8.4.sql
trunk/sql/Oracle-upgrade-1.8.4-1.8.5.sql
trunk/sql/Oracle-upgrade-1.8.5-2.0.0.sql
trunk/sql/Oracle-upgrade-2.0.0-2.0.8.sql
trunk/sql/Oracle-upgrade-2.0.8-2.2.0.sql
Deleted: trunk/sql/DB2-create.sql
===================================================================
--- trunk/sql/DB2-create.sql 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/DB2-create.sql 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,8 +0,0 @@
-CONNECT RESET@
-ATTACH TO db2inst1@
-CREATE DATABASE LEDGER USING CODESET UTF-8 TERRITORY US DFT_EXTENT_SZ 16@
-CONNECT TO LEDGER@
-CREATE BUFFERPOOL LEDGERBP_8K IMMEDIATE SIZE 1000 PAGESIZE 8192@
-CREATE TABLESPACE LEDGER_TS PAGESIZE 8K MANAGED BY SYSTEM USING ('LEDGER_TS') PREFETCHSIZE 8 BUFFERPOOL LEDGERBP_8K@
-CREATE TEMPORARY TABLESPACE LEDGER_TMP_8K PAGESIZE 8K MANAGED BY SYSTEM USING ('LEDGER_TMP_8K') BUFFERPOOL LEDGERBP_8K@
-CONNECT RESET@
Deleted: trunk/sql/DB2-indices.sql
===================================================================
--- trunk/sql/DB2-indices.sql 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/DB2-indices.sql 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,57 +0,0 @@
-create index ac_trns_trans_id on acc_trans (trans_id)@
-create index ac_trns_chart_id on acc_trans (chart_id)@
-create index ac_trns_transdate on acc_trans (transdate)@
-create index ac_trns_source on acc_trans (source)@
-create index ap_id_x on ap (id)@
-create index ap_transdate_x on ap (transdate)@
-create index ap_invnumber_x on ap (invnumber)@
-create index ap_ordnumber_x on ap (ordnumber)@
-create index ap_vendor_id_x on ap (vendor_id)@
-create index ap_emp_id_x on ap (employee_id)@
-create index ar_id_x on ar (id)@
-create index ar_transdate_x on ar (transdate)@
-create index ar_invnumber_x on ar (invnumber)@
-create index ar_ordnumber_x on ar (ordnumber)@
-create index ar_cust_id_x on ar (customer_id)@
-create index ar_emp_id_x on ar (employee_id)@
-create index assembly_id_x on assembly (id)@
-create index chart_id_x on chart (id)@
-create unique index chart_accno_x on chart (accno)@
-create index chart_category_x on chart (category)@
-create index chart_link_x on chart (link)@
-create index chart_gifi_accno on chart (gifi_accno)@
-create index cust_id_x on customer (id)@
-create index cust_customer_id_x on customertax (customer_id)@
-create index cust_custnum_x on customer (customernumber)@
-create index cust_name_x on customer (name)@
-create index cust_contact_x on customer (contact)@
-create index employee_id_x on employee (id)@
-create unique index employee_login_x on employee (login)@
-create index employee_name_x on employee (name)@
-create index exchrate_ct_x on exchangerate (curr, transdate)@
-create unique index gifi_accno_x on gifi (accno)@
-create index gl_id_x on gl (id)@
-create index gl_transdate_x on gl (transdate)@
-create index gl_reference_x on gl (reference)@
-create index gl_description_x on gl (description)@
-create index gl_employee_id_x on gl (employee_id)@
-create index invoi_id_x on invoice (id)@
-create index invoi_trans_id_x on invoice (trans_id)@
-create index makmod_parts_id on makemodel (parts_id)@
-create index oe_id_x on oe (id)@
-create index oe_transdate_x on oe (transdate)@
-create index oe_ordnumber_x on oe (ordnumber)@
-create index oe_emp_id_x on oe (employee_id)@
-create index orditems_trans_id on orderitems (trans_id)@
-create index parts_id_x on parts (id)@
-create index parts_partnumber on parts (partnumber)@
--- create index parts_desc on parts (description)@
-create index partstax_parts_id on partstax (parts_id)@
-create index vend_id_x on vendor (id)@
-create index vend_name_x on vendor (name)@
-create index vend_vendnum_x on vendor (vendornumber)@
-create index vend_contact_x on vendor (contact)@
-create index vendtax_vendor_id on vendortax (vendor_id)@
-create index shipto_trans_id on shipto (trans_id)@
-create index project_id_x on project (id)@
-create index partsgroup_id_x on partsgroup (id)@
Deleted: trunk/sql/DB2-remove.sql
===================================================================
--- trunk/sql/DB2-remove.sql 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/DB2-remove.sql 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,47 +0,0 @@
--- DB2-remove.sql
---
---
--- Jim Rawlings modified for use with SL 2.0.8 and DB2 v7.2
--- and higher August 27, 2003
---
---
----------------------------------------------------------
--- DDL Statements for object removal
----------------------------------------------------------
-DROP TRIGGER partsgroupid;
-DROP TRIGGER projectid;
-DROP TRIGGER employeeid;
-DROP TRIGGER oeid;
-DROP TRIGGER apid;
-DROP TRIGGER arid;
-DROP TRIGGER partsid;
-DROP TRIGGER customerid;
-DROP TRIGGER vendorid;
-DROP TRIGGER invoiceid;
-DROP TRIGGER chartid;
-DROP TRIGGER glid;
-DROP SEQUENCE id RESTRICT;
-DROP TABLE partsgroup;
-DROP TABLE project;
-DROP TABLE shipto;
-DROP TABLE employee;
-DROP TABLE exchangerate;
-DROP TABLE orderitems;
-DROP TABLE oe;
-DROP TABLE vendortax;
-DROP TABLE customertax;
-DROP TABLE tax;
-DROP TABLE partstax;
-DROP TABLE ap;
-DROP TABLE ar;
-DROP TABLE assembly;
-DROP TABLE parts;
-DROP TABLE customer;
-DROP TABLE vendor;
-DROP TABLE invoice;
-DROP TABLE acc_trans;
-DROP TABLE defaults;
-DROP TABLE gifi;
-DROP TABLE chart;
-DROP TABLE gl;
-DROP TABLE makemodel;
Deleted: trunk/sql/DB2-set.sql
===================================================================
--- trunk/sql/DB2-set.sql 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/DB2-set.sql 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,4 +0,0 @@
-ATTACH TO db2inst1@
-! db2set DB2DBDFT=LEDGER@
-! db2empfa LEDGER@
-DETACH@
Deleted: trunk/sql/DB2-sql-ledger.order
===================================================================
--- trunk/sql/DB2-sql-ledger.order 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/DB2-sql-ledger.order 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,5 +0,0 @@
-db2 -td@ -vf DB2-create.sql
-db2 -td@ -vf DB2-set.sql
-db2 -td@ -vf DB2-tables.sql
-db2 -td@ -vf DB2-indices.sql
-db2 -tvf Default-chart.sql
Deleted: trunk/sql/DB2-tables.sql
===================================================================
--- trunk/sql/DB2-tables.sql 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/DB2-tables.sql 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,511 +0,0 @@
--- 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@
-
-
Deleted: trunk/sql/Oracle-indices.sql
===================================================================
--- trunk/sql/Oracle-indices.sql 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/Oracle-indices.sql 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,65 +0,0 @@
-create index acc_trans_trans_id_key on acc_trans (trans_id);
-create index acc_trans_chart_id_key on acc_trans (chart_id);
-create index acc_trans_transdate_key on acc_trans (transdate);
-create index acc_trans_source_key on acc_trans (source);
-create index ap_id_key on ap (id);
-create index ap_transdate_key on ap (transdate);
-create index ap_invnumber_key on ap (invnumber);
-create index ap_ordnumber_key on ap (ordnumber);
-create index ap_vendor_id_key on ap (vendor_id);
-create index ap_employee_id_key on ap (employee_id);
-create index ap_quonumber_key on ap (quonumber);
-create index ar_id_key on ar (id);
-create index ar_transdate_key on ar (transdate);
-create index ar_invnumber_key on ar (invnumber);
-create index ar_ordnumber_key on ar (ordnumber);
-create index ar_customer_id_key on ar (customer_id);
-create index ar_employee_id_key on ar (employee_id);
-create index ar_quonumber_key on ar (quonumber);
-create index assembly_id_key on assembly (id);
-create index chart_id_key on chart (id);
-create unique index chart_accno_key on chart (accno);
-create index chart_category_key on chart (category);
-create index chart_link_key on chart (link);
-create index chart_gifi_accno_key on chart (gifi_accno);
-create index customer_id_key on customer (id);
-create index customer_customer_id_key on customertax (customer_id);
-create index customer_customernumber_key on customer (customernumber);
-create index customer_name_key on customer (name);
-create index customer_contact_key on customer (contact);
-create index employee_id_key on employee (id);
-create unique index employee_login_key on employee (login);
-create index employee_name_key on employee (name);
-create index exchangerate_ct_key on exchangerate (curr, transdate);
-create unique index gifi_accno_key on gifi (accno);
-create index gl_id_key on gl (id);
-create index gl_transdate_key on gl (transdate);
-create index gl_reference_key on gl (reference);
-create index gl_description_key on gl (description);
-create index gl_employee_id_key on gl (employee_id);
-create index invoice_id_key on invoice (id);
-create index invoice_trans_id_key on invoice (trans_id);
-create index makemodel_parts_id_key on makemodel (parts_id);
-create index makemodel_make_key on makemodel (make);
-create index makemodel_model_key on makemodel (model);
-create index oe_id_key on oe (id);
-create index oe_transdate_key on oe (transdate);
-create index oe_ordnumber_key on oe (ordnumber);
-create index oe_employee_id_key on oe (employee_id);
-create index orderitems_trans_id_key on orderitems (trans_id);
-create index parts_id_key on parts (id);
-create index parts_partnumber_key on parts (partnumber);
-create index parts_description_key on parts (description);
-create index partstax_parts_id_key on partstax (parts_id);
-create index vendor_id_key on vendor (id);
-create index vendor_name_key on vendor (name);
-create index vendor_vendornumber_key on vendor (vendornumber);
-create index vendor_contact_key on vendor (contact);
-create index vendortax_vendor_id_key on vendortax (vendor_id);
-create index shipto_trans_id_key on shipto (trans_id);
-create index project_id_key on project (id);
-create unique index projectnumber_key on project (projectnumber);
-create index partsgroup_id_key on partsgroup (id);
-create unique index partsgroup_key on partsgroup (partsgroup);
-create index status_trans_id_key on status (trans_id);
-create index department_id_key on department (id);
Deleted: trunk/sql/Oracle-tables.sql
===================================================================
--- trunk/sql/Oracle-tables.sql 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/Oracle-tables.sql 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,424 +0,0 @@
--- Oracle-tables.sql
--- Paulo Rodrigues: added functions and triggers, Oct. 31, 2001
---
--- Modified for use with SL 2.0 and Oracle 9i2, Dec 13, 2002
--- Updated to 2.3.0, Dec 18, 2003
---
-ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
---
-CREATE SEQUENCE id START WITH 10000 INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 2;
-SELECT ID.NEXTVAL FROM DUAL;
---
-CREATE SEQUENCE invoiceid START WITH 1 INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 2;
-SELECT INVOICEID.NEXTVAL FROM DUAL;
---
-CREATE TABLE makemodel (
- parts_id INTEGER,
- make VARCHAR2(64),
- model VARCHAR2(64)
-);
---
-CREATE TABLE gl (
- id INTEGER,
- reference VARCHAR2(50),
- description VARCHAR2(100),
- transdate DATE DEFAULT SYSDATE,
- employee_id INTEGER,
- notes VARCHAR2(4000),
- department_id INTEGER DEFAULT 0
-);
---
-CREATE TABLE chart (
- id INTEGER,
- accno VARCHAR2(20) NOT NULL,
- description VARCHAR2(100),
- charttype CHAR(1) DEFAULT 'A',
- category CHAR(1),
- link VARCHAR2(100),
- gifi_accno VARCHAR2(20)
-);
---
-CREATE TABLE gifi (
- accno VARCHAR2(20),
- description VARCHAR2(100)
-);
---
-CREATE TABLE defaults (
- inventory_accno_id INTEGER,
- income_accno_id INTEGER,
- expense_accno_id INTEGER,
- fxgain_accno_id INTEGER,
- fxloss_accno_id INTEGER,
- invnumber VARCHAR2(30),
- sonumber VARCHAR2(30),
- yearend VARCHAR2(5),
- weightunit VARCHAR2(5),
- businessnumber VARCHAR2(30),
- version VARCHAR2(8),
- curr VARCHAR2(500),
- closedto DATE,
- revtrans CHAR(1) DEFAULT '0',
- ponumber VARCHAR2(30),
- sqnumber VARCHAR2(30),
- rfqnumber VARCHAR2(30)
-);
-INSERT INTO defaults (version) VALUES ('2.3.0');
---
-CREATE TABLE acc_trans (
- trans_id INTEGER,
- chart_id INTEGER,
- amount FLOAT,
- transdate DATE DEFAULT SYSDATE,
- source VARCHAR2(20),
- cleared CHAR(1) DEFAULT '0',
- fx_transaction CHAR(1) DEFAULT '0',
- project_id INTEGER
-);
---
-CREATE TABLE invoice (
- id INTEGER,
- trans_id INTEGER,
- parts_id INTEGER,
- description VARCHAR2(4000),
- qty FLOAT,
- allocated FLOAT,
- sellprice FLOAT,
- fxsellprice FLOAT,
- discount FLOAT,
- assemblyitem CHAR(1) DEFAULT '0',
- unit VARCHAR2(5),
- project_id INTEGER,
- deliverydate DATE,
- serialnumber VARCHAR2(200)
-);
---
-CREATE TABLE vendor (
- id INTEGER,
- name VARCHAR2(35),
- addr1 VARCHAR2(35),
- addr2 VARCHAR2(35),
- addr3 VARCHAR2(35),
- addr4 VARCHAR2(35),
- contact VARCHAR2(35),
- phone VARCHAR2(20),
- fax VARCHAR2(20),
- email VARCHAR2(50),
- notes VARCHAR2(4000),
- terms INTEGER DEFAULT 0,
- taxincluded CHAR(1),
- vendornumber VARCHAR2(40),
- cc VARCHAR2(50),
- bcc VARCHAR2(50)
-);
---
-CREATE TABLE customer (
- id INTEGER,
- name VARCHAR2(35),
- addr1 VARCHAR2(35),
- addr2 VARCHAR2(35),
- addr3 VARCHAR2(35),
- addr4 VARCHAR2(35),
- contact VARCHAR2(35),
- phone VARCHAR2(20),
- fax VARCHAR2(20),
- email VARCHAR2(50),
- notes VARCHAR2(4000),
- discount FLOAT,
- taxincluded CHAR(1),
- creditlimit FLOAT,
- terms INTEGER DEFAULT 0,
- customernumber VARCHAR2(40),
- cc VARCHAR2(50),
- bcc VARCHAR2(50)
-);
---
-CREATE TABLE parts (
- id INTEGER,
- partnumber VARCHAR2(30),
- description VARCHAR2(4000),
- unit VARCHAR2(5),
- listprice FLOAT,
- sellprice FLOAT,
- lastcost FLOAT,
- priceupdate DATE DEFAULT SYSDATE,
- weight FLOAT,
- onhand FLOAT DEFAULT 0,
- notes VARCHAR2(4000),
- makemodel CHAR(1) DEFAULT '0',
- assembly CHAR(1) DEFAULT '0',
- alternate CHAR(1) DEFAULT '0',
- rop FLOAT,
- inventory_accno_id INTEGER,
- income_accno_id INTEGER,
- expense_accno_id INTEGER,
- bin VARCHAR2(20),
- obsolete CHAR(1) DEFAULT '0',
- bom CHAR(1) DEFAULT '0',
- image VARCHAR2(100),
- drawing VARCHAR2(100),
- microfiche VARCHAR2(100),
- partsgroup_id INTEGER
-);
---
-CREATE TABLE assembly (
- id INTEGER,
- parts_id INTEGER,
- qty FLOAT,
- bom char(1)
-);
---
-CREATE TABLE ar (
- id INTEGER,
- invnumber VARCHAR2(30),
- transdate DATE DEFAULT SYSDATE,
- customer_id INTEGER,
- taxincluded CHAR(1),
- amount FLOAT,
- netamount FLOAT,
- paid FLOAT,
- datepaid DATE,
- duedate DATE,
- invoice CHAR(1) DEFAULT '0',
- shippingpoint VARCHAR2(100),
- terms INTEGER DEFAULT 0,
- notes VARCHAR2(4000),
- curr CHAR(3),
- ordnumber VARCHAR2(30),
- employee_id INTEGER,
- till VARCHAR2(20),
- quonumber VARCHAR2(30),
- intnotes VARCHAR2(4000),
- department_id INTEGER DEFAULT 0
-);
---
-CREATE TABLE ap (
- id INTEGER,
- invnumber VARCHAR2(30),
- transdate DATE DEFAULT SYSDATE,
- vendor_id INTEGER,
- taxincluded CHAR(1) DEFAULT '0',
- amount FLOAT,
- netamount FLOAT,
- paid FLOAT,
- datepaid DATE,
- duedate DATE,
- invoice CHAR(1) DEFAULT '0',
- ordnumber VARCHAR2(30),
- curr CHAR(3),
- notes VARCHAR2(4000),
- employee_id INTEGER,
- till VARCHAR2(20),
- quonumber VARCHAR2(30),
- intnotes VARCHAR2(4000),
- department_id INTEGER DEFAULT 0
-);
---
-CREATE TABLE partstax (
- parts_id INTEGER,
- chart_id INTEGER
-);
---
-CREATE TABLE tax (
- chart_id INTEGER,
- rate FLOAT,
- taxnumber VARCHAR2(30)
-);
---
-CREATE TABLE customertax (
- customer_id INTEGER,
- chart_id INTEGER
-);
---
-CREATE TABLE vendortax (
- vendor_id INTEGER,
- chart_id INTEGER
-);
---
-CREATE TABLE oe (
- id INTEGER,
- ordnumber VARCHAR2(30),
- transdate DATE DEFAULT SYSDATE,
- vendor_id INTEGER,
- customer_id INTEGER,
- amount FLOAT,
- netamount FLOAT,
- reqdate DATE,
- taxincluded CHAR(1),
- shippingpoint VARCHAR2(100),
- notes VARCHAR2(4000),
- curr CHAR(3),
- employee_id INTEGER,
- closed CHAR(1) DEFAULT '0',
- quotation CHAR(1) DEFAULT '0',
- quonumber VARCHAR2(30),
- intnotes VARCHAR2(4000),
- department_id INTEGER DEFAULT 0
-);
---
-CREATE TABLE orderitems (
- trans_id INTEGER,
- parts_id INTEGER,
- description VARCHAR2(4000),
- qty FLOAT,
- sellprice FLOAT,
- discount FLOAT,
- unit VARCHAR2(5),
- project_id INTEGER,
- reqdate DATE
-);
---
-CREATE TABLE exchangerate (
- curr CHAR(3),
- transdate DATE,
- buy FLOAT,
- sell FLOAT
-);
---
-CREATE TABLE employee (
- id INTEGER,
- login VARCHAR2(20),
- name VARCHAR2(35),
- addr1 VARCHAR2(35),
- addr2 VARCHAR2(35),
- addr3 VARCHAR2(35),
- addr4 VARCHAR2(35),
- workphone VARCHAR2(20),
- homephone VARCHAR2(20),
- startdate DATE DEFAULT SYSDATE,
- enddate DATE,
- notes VARCHAR2(4000),
- role VARCHAR2(30)
-);
---
-CREATE TABLE shipto (
- trans_id INTEGER,
- shiptoname VARCHAR2(35),
- shiptoaddr1 VARCHAR2(35),
- shiptoaddr2 VARCHAR2(35),
- shiptoaddr3 VARCHAR2(35),
- shiptoaddr4 VARCHAR2(35),
- shiptocontact VARCHAR2(35),
- shiptophone VARCHAR2(20),
- shiptofax VARCHAR2(20),
- shiptoemail VARCHAR2(50)
-);
---
-CREATE TABLE project (
- id INTEGER,
- projectnumber VARCHAR2(50),
- description VARCHAR2(4000)
-);
---
-CREATE TABLE partsgroup (
- id INTEGER,
- partsgroup VARCHAR2(100)
-);
---
-CREATE TABLE status (
- trans_id INTEGER,
- formname VARCHAR2(30),
- printed CHAR(1) DEFAULT 0,
- emailed CHAR(1) DEFAULT 0,
- spoolfile VARCHAR2(20),
- chart_id INTEGER
-);
---
-CREATE TABLE department (
- id INTEGER,
- description VARCHAR2(100),
- role CHAR(1) DEFAULT 'P'
-);
---
--- functions
---
-CREATE OR REPLACE FUNCTION current_date RETURN date AS
-BEGIN
- return(sysdate);--
-END;;
---
--- triggers
---
-CREATE OR REPLACE TRIGGER glid BEFORE INSERT ON gl FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-CREATE OR REPLACE TRIGGER chartid BEFORE INSERT ON chart FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-CREATE OR REPLACE TRIGGER invoiceid BEFORE INSERT ON invoice FOR EACH ROW
-BEGIN
- SELECT invoiceid.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-CREATE OR REPLACE TRIGGER vendorid BEFORE INSERT ON vendor FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-CREATE OR REPLACE TRIGGER customerid BEFORE INSERT ON customer FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-CREATE OR REPLACE TRIGGER partsid BEFORE INSERT ON parts FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-CREATE OR REPLACE TRIGGER arid BEFORE INSERT ON ar FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-CREATE OR REPLACE TRIGGER apid BEFORE INSERT ON ap FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-CREATE OR REPLACE TRIGGER oeid BEFORE INSERT ON oe FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-CREATE OR REPLACE TRIGGER employeeid BEFORE INSERT ON employee FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-CREATE OR REPLACE TRIGGER projectid BEFORE INSERT ON project FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-CREATE OR REPLACE TRIGGER partsgroupid BEFORE INSERT ON partsgroup FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
-FROM DUAL;--
-END;;
---
Deleted: trunk/sql/Oracle-upgrade-1.8.0-1.8.4.sql
===================================================================
--- trunk/sql/Oracle-upgrade-1.8.0-1.8.4.sql 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/Oracle-upgrade-1.8.0-1.8.4.sql 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,28 +0,0 @@
---
-ALTER TABLE chart ADD gifi_accno VARCHAR2(20);
---
-CREATE TABLE gifi (
- accno VARCHAR2(20),
- description VARCHAR2(100)
-);
---
-CREATE UNIQUE INDEX chart_accno_key ON chart (accno);
---
-CREATE TABLE mtemp (
- parts_id INTEGER,
- name VARCHAR2(100)
-);
-INSERT INTO mtemp SELECT parts_id, name FROM makemodel;
-DROP TABLE makemodel;
-ALTER TABLE mtemp RENAME TO makemodel;
---
-ALTER TABLE defaults ADD closedto DATE;
-ALTER TABLE defaults ADD revtrans CHAR(1);
---
-ALTER TABLE ap ADD notes VARCHAR2(4000);
---
-ALTER TABLE customer ADD businessnumber VARCHAR2(40);
-ALTER TABLE vendor ADD businessnumber VARCHAR2(40);
---
-UPDATE defaults SET version = '1.8.4', revtrans = '0';
---
Deleted: trunk/sql/Oracle-upgrade-1.8.4-1.8.5.sql
===================================================================
--- trunk/sql/Oracle-upgrade-1.8.4-1.8.5.sql 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/Oracle-upgrade-1.8.4-1.8.5.sql 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,77 +0,0 @@
---
-ALTER TABLE customer ADD (customernumber VARCHAR2(40));
-UPDATE customer SET customernumber = businessnumber;
-ALTER TABLE customer DROP COLUMN businessnumber;
-CREATE INDEX customer_customernumber_key ON customer (customernumber);
---
-ALTER TABLE vendor ADD (vendornumber VARCHAR2(40));
-UPDATE vendor SET vendornumber = businessnumber;
-ALTER TABLE vendor DROP COLUMN businessnumber;
-CREATE INDEX vendor_vendornumber_key ON vendor (vendornumber);
---
-CREATE TABLE employee (
- id INTEGER,
- login VARCHAR2(20),
- name VARCHAR2(35),
- addr1 VARCHAR2(35),
- addr2 VARCHAR2(35),
- addr3 VARCHAR2(35),
- addr4 VARCHAR2(35),
- workphone VARCHAR2(20),
- homephone VARCHAR2(20),
- startdate DATE DEFAULT SYSDATE,
- enddate DATE,
- notes VARCHAR2(4000)
-);
---
-CREATE OR REPLACE TRIGGER employeeid BEFORE INSERT ON employee FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-CREATE INDEX employee_id_key ON employee (id);
-CREATE UNIQUE INDEX employee_login_key ON employee (login);
-CREATE INDEX employee_name_key ON employee (name);
---
-ALTER TABLE gl ADD (employee_id INTEGER);
-CREATE INDEX gl_employee_id_key ON gl (employee_id);
-ALTER TABLE ar ADD (employee_id INTEGER);
-CREATE INDEX ar_employee_id_key ON ar (employee_id);
-ALTER TABLE ap ADD (employee_id INTEGER);
-CREATE INDEX ap_employee_id_key ON ap (employee_id);
-ALTER TABLE oe ADD (employee_id INTEGER);
-CREATE INDEX oe_employee_id_key ON oe (employee_id);
---
-ALTER TABLE invoice ADD (unit VARCHAR2(5));
-ALTER TABLE orderitems ADD (unit VARCHAR2(5));
---
-UPDATE chart SET gifi_accno = '' WHERE gifi_accno = NULL;
-ALTER TABLE chart RENAME TO chartold;
-CREATE TABLE chart (
- id INTEGER,
- accno VARCHAR2(20) NOT NULL,
- description VARCHAR2(100),
- charttype CHAR(1) DEFAULT 'A',
- category CHAR(1),
- link VARCHAR2(100),
- gifi_accno VARCHAR2(20)
-);
-INSERT INTO chart (id, accno, description, charttype, category, link, gifi_accno) SELECT id, accno, description, charttype, category, link, gifi_accno from chartold;
-DROP TABLE chartold;
-CREATE INDEX chart_id_key ON chart (id);
-CREATE UNIQUE INDEX chart_accno_key ON chart (accno);
-CREATE INDEX chart_category_key ON chart (category);
-CREATE INDEX chart_link_key ON chart (link);
-CREATE INDEX chart_gifi_accno_key ON chart (gifi_accno);
---
-ALTER TABLE parts MODIFY inventory_accno_id;
---
-ALTER TABLE defaults ADD (sonumber VARCHAR2(30));
-UPDATE defaults SET sonumber = ordnumber;
-ALTER TABLE defaults DROP COLUMN ordnumber;
-ALTER TABLE defaults ADD (ponumber VARCHAR2(30));
---
-UPDATE defaults SET version = '1.8.5';
---
Deleted: trunk/sql/Oracle-upgrade-1.8.5-2.0.0.sql
===================================================================
--- trunk/sql/Oracle-upgrade-1.8.5-2.0.0.sql 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/Oracle-upgrade-1.8.5-2.0.0.sql 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,100 +0,0 @@
---
-alter table customer add (cc VARCHAR2(50));
-alter table customer add (bcc VARCHAR2(50));
---
-alter table vendor add (cc VARCHAR2(50));
-alter table vendor add (bcc VARCHAR2(50));
---
-create table shipto (
- trans_id integer,
- shiptoname varchar2(35),
- shiptoaddr1 varchar2(35),
- shiptoaddr2 varchar2(35),
- shiptoaddr3 varchar2(35),
- shiptoaddr4 varchar2(35),
- shiptocontact varchar2(35),
- shiptophone varchar2(20),
- shiptofax varchar2(20),
- shiptoemail VARCHAR2(50)
-);
---
-insert into shipto (trans_id, shiptoname, shiptoaddr1, shiptoaddr2, shiptoaddr3, shiptoaddr4, shiptocontact, shiptophone, shiptofax, shiptoemail) select id, shiptoname, shiptoaddr1, shiptoaddr2, shiptoaddr3, shiptoaddr4, shiptocontact, shiptophone, shiptofax, shiptoemail from customer where shiptoname != '' or shiptoname is not null;
---
-insert into shipto (trans_id, shiptoname, shiptoaddr1, shiptoaddr2, shiptoaddr3, shiptoaddr4, shiptocontact, shiptophone, shiptofax, shiptoemail) select distinct on (a.id) a.id, c.shiptoname, c.shiptoaddr1, c.shiptoaddr2, c.shiptoaddr3, c.shiptoaddr4, c.shiptocontact, c.shiptophone, c.shiptofax, c.shiptoemail from customer c, ar a where a.customer_id = c.id;
---
-insert into shipto (trans_id, shiptoname, shiptoaddr1, shiptoaddr2, shiptoaddr3, shiptoaddr4, shiptocontact, shiptophone, shiptofax, shiptoemail) select distinct on (o.id) o.id, c.shiptoname, c.shiptoaddr1, c.shiptoaddr2, c.shiptoaddr3, c.shiptoaddr4, c.shiptocontact, c.shiptophone, c.shiptofax, c.shiptoemail from customer c, oe o where o.customer_id = c.id;
---
-create index shipto_trans_id_key on shipto (trans_id);
---
-create table custome (
- id integer,
- name varchar2(35),
- addr1 varchar2(35),
- addr2 varchar2(35),
- addr3 varchar2(35),
- addr4 varchar2(35),
- contact varchar2(35),
- phone varchar2(20),
- fax varchar2(20),
- email VARCHAR2(50),
- notes RCHAR2(4000),
- discount float,
- taxincluded char(1),
- creditlimit float DEFAULT 0,
- terms integer DEFAULT 0,
- customernumber VARCHAR2(40),
- cc VARCHAR2(50),
- bcc VARCHAR2(50)
-);
-insert into custome (id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, customernumber) select id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, customernumber from customer;
---
-drop table customer;
-alter table custome rename to customer;
-create index customer_id_key on customer (id);
-create index customer_name_key on customer (name);
-create index customer_contact_key on customer (contact);
---
-alter table parts add (bom char(1) default '0');
-update parts set bom = '0';
-update parts set bom = '1' where assembly = '1';
-alter table parts add (image VARCHAR2(100));
-alter table parts add (drawing VARCHAR2(100));
-alter table parts add (microfiche VARCHAR2(100));
---
-alter table gl add (notes VARCHAR2(4000));
---
-alter table oe add (closed char(1) default '0');
-update oe set closed = '0';
---
-create table project (
- id integer,
- projectnumber VARCHAR2(50),
- description VARCHAR2(4000)
-);
---
-create index project_id_key on project (id);
---
-alter table acc_trans add (project_id integer);
-update acc_trans set cleared = '0' where cleared = '1';
---
-alter table invoice add (project_id integer);
-alter table invoice add (deliverydate date);
-alter table orderitems add (project_id integer);
-alter table orderitems add (reqdate date);
---
-CREATE OR REPLACE TRIGGER projectid BEFORE INSERT ON project FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
- FROM DUAL;--
-END;;
---
-alter table gl add (reference VARCHAR2(50));
-update gl set reference = source;
-alter table gl drop column source;
---
-create index gl_reference_key on gl (reference);
-create index acc_trans_source_key on acc_trans (source);
---
-update defaults set version = '2.0.0';
---
Deleted: trunk/sql/Oracle-upgrade-2.0.0-2.0.8.sql
===================================================================
--- trunk/sql/Oracle-upgrade-2.0.0-2.0.8.sql 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/Oracle-upgrade-2.0.0-2.0.8.sql 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,19 +0,0 @@
---
-create table partsgroup (id integer, partsgroup varchar2(100));
-create index partsgroup_id_key on partsgroup (id);
---
-alter table parts add (partsgroup_id integer);
---
-alter table assembly add (bom char(1));
-update assembly set bom = '0' where assembly.id = parts.id and parts.bom = '0';
-update assembly set bom = '1' where assembly.id = parts.id and parts.bom = '1';
---
-CREATE OR REPLACE TRIGGER partsgroupid BEFORE INSERT ON partsgroup FOR EACH ROW
-BEGIN
- SELECT id.nextval
- INTO :new.id
-FROM DUAL;--
-END;;
---
-update defaults set version = '2.0.8';
---
Deleted: trunk/sql/Oracle-upgrade-2.0.8-2.2.0.sql
===================================================================
--- trunk/sql/Oracle-upgrade-2.0.8-2.2.0.sql 2006-09-09 07:48:09 UTC (rev 46)
+++ trunk/sql/Oracle-upgrade-2.0.8-2.2.0.sql 2006-09-09 17:20:12 UTC (rev 47)
@@ -1,9 +0,0 @@
---
-create unique index projectnumber_key on project (projectnumber);
-create unique index partsgroup_key on partsgroup (partsgroup);
---
-alter table ar add (till varchar2(20));
-alter table ap add (till varchar2(20));
---
-update defaults set version = '2.2.0';
---
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.