[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb: [420] trunk/sql
- Subject: SF.net SVN: ledger-smb: [420] trunk/sql
- From: ..hidden..
- Date: Mon, 30 Oct 2006 21:43:11 -0800
Revision: 420
http://svn.sourceforge.net/ledger-smb/?rev=420&view=rev
Author: einhverfr
Date: 2006-10-30 21:42:52 -0800 (Mon, 30 Oct 2006)
Log Message:
-----------
Moving Pg-upgrade* into sql/legacy directory
Added Paths:
-----------
trunk/sql/legacy/
trunk/sql/legacy/Pg-upgrade-1.2.6-1.2.7.sql
trunk/sql/legacy/Pg-upgrade-1.2.7-1.4.0.sql
trunk/sql/legacy/Pg-upgrade-1.4.0-1.6.0.sql
trunk/sql/legacy/Pg-upgrade-1.6.0-1.8.0.sql
trunk/sql/legacy/Pg-upgrade-1.8.0-1.8.4.sql
trunk/sql/legacy/Pg-upgrade-1.8.4-1.8.5.sql
trunk/sql/legacy/Pg-upgrade-1.8.5-2.0.0.sql
trunk/sql/legacy/Pg-upgrade-2.0.0-2.0.8.sql
trunk/sql/legacy/Pg-upgrade-2.0.8-2.2.0.sql
trunk/sql/legacy/Pg-upgrade-2.2.0-2.3.0.sql
trunk/sql/legacy/Pg-upgrade-2.3.0-2.3.1.sql
trunk/sql/legacy/Pg-upgrade-2.3.1-2.3.3.sql
trunk/sql/legacy/Pg-upgrade-2.3.3-2.3.4.sql
trunk/sql/legacy/Pg-upgrade-2.3.4-2.3.5.sql
trunk/sql/legacy/Pg-upgrade-2.3.5-2.3.6.sql
trunk/sql/legacy/Pg-upgrade-2.3.6-2.3.7.sql
trunk/sql/legacy/Pg-upgrade-2.3.7-2.3.8.sql
trunk/sql/legacy/Pg-upgrade-2.3.8-2.3.9.sql
trunk/sql/legacy/Pg-upgrade-2.3.9-2.4.2.sql
trunk/sql/legacy/Pg-upgrade-2.4.2-2.4.3.sql
trunk/sql/legacy/Pg-upgrade-2.4.3-2.4.4.sql
trunk/sql/legacy/Pg-upgrade-2.4.4-2.5.0.sql
trunk/sql/legacy/Pg-upgrade-2.5.0-2.5.2.sql
trunk/sql/legacy/Pg-upgrade-2.5.2-2.6.0.sql
trunk/sql/legacy/Pg-upgrade-2.6.0-2.6.1.sql
trunk/sql/legacy/Pg-upgrade-2.6.1-2.6.2.sql
trunk/sql/legacy/Pg-upgrade-2.6.12-2.6.17.sql
trunk/sql/legacy/Pg-upgrade-2.6.17-2.6.18.sql
trunk/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql
trunk/sql/legacy/Pg-upgrade-2.6.2-2.6.3.sql
trunk/sql/legacy/Pg-upgrade-2.6.3-2.6.4.sql
trunk/sql/legacy/Pg-upgrade-2.6.4-2.6.7.sql
trunk/sql/legacy/Pg-upgrade-2.6.7-2.6.12.sql
Removed Paths:
-------------
trunk/sql/Pg-upgrade-1.2.6-1.2.7.sql
trunk/sql/Pg-upgrade-1.2.7-1.4.0.sql
trunk/sql/Pg-upgrade-1.4.0-1.6.0.sql
trunk/sql/Pg-upgrade-1.6.0-1.8.0.sql
trunk/sql/Pg-upgrade-1.8.0-1.8.4.sql
trunk/sql/Pg-upgrade-1.8.4-1.8.5.sql
trunk/sql/Pg-upgrade-1.8.5-2.0.0.sql
trunk/sql/Pg-upgrade-2.0.0-2.0.8.sql
trunk/sql/Pg-upgrade-2.0.8-2.2.0.sql
trunk/sql/Pg-upgrade-2.2.0-2.3.0.sql
trunk/sql/Pg-upgrade-2.3.0-2.3.1.sql
trunk/sql/Pg-upgrade-2.3.1-2.3.3.sql
trunk/sql/Pg-upgrade-2.3.3-2.3.4.sql
trunk/sql/Pg-upgrade-2.3.4-2.3.5.sql
trunk/sql/Pg-upgrade-2.3.5-2.3.6.sql
trunk/sql/Pg-upgrade-2.3.6-2.3.7.sql
trunk/sql/Pg-upgrade-2.3.7-2.3.8.sql
trunk/sql/Pg-upgrade-2.3.8-2.3.9.sql
trunk/sql/Pg-upgrade-2.3.9-2.4.2.sql
trunk/sql/Pg-upgrade-2.4.2-2.4.3.sql
trunk/sql/Pg-upgrade-2.4.3-2.4.4.sql
trunk/sql/Pg-upgrade-2.4.4-2.5.0.sql
trunk/sql/Pg-upgrade-2.5.0-2.5.2.sql
trunk/sql/Pg-upgrade-2.5.2-2.6.0.sql
trunk/sql/Pg-upgrade-2.6.0-2.6.1.sql
trunk/sql/Pg-upgrade-2.6.1-2.6.2.sql
trunk/sql/Pg-upgrade-2.6.12-2.6.17.sql
trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql
trunk/sql/Pg-upgrade-2.6.18-2.6.19.sql
trunk/sql/Pg-upgrade-2.6.2-2.6.3.sql
trunk/sql/Pg-upgrade-2.6.3-2.6.4.sql
trunk/sql/Pg-upgrade-2.6.4-2.6.7.sql
trunk/sql/Pg-upgrade-2.6.7-2.6.12.sql
Deleted: trunk/sql/Pg-upgrade-1.2.6-1.2.7.sql
===================================================================
--- trunk/sql/Pg-upgrade-1.2.6-1.2.7.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-1.2.6-1.2.7.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,4 +0,0 @@
---
--- add the field shiptoemail to the customer table
---
-alter table customer add column shiptoemail text;
Deleted: trunk/sql/Pg-upgrade-1.2.7-1.4.0.sql
===================================================================
--- trunk/sql/Pg-upgrade-1.2.7-1.4.0.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-1.2.7-1.4.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,173 +0,0 @@
---
-CREATE TABLE newap (
- id int DEFAULT nextval ( 'id' ),
- invnumber text,
- transdate date DEFAULT current_date,
- vendor int,
- taxincluded bool DEFAULT FALSE,
- amount float,
- netamount float,
- paid float,
- datepaid date,
- duedate date,
- invoice bool DEFAULT FALSE,
- ordnumber text
-);
---
-INSERT INTO newap (id, invnumber, transdate, vendor, amount, netamount, paid,
-datepaid, duedate, invoice, ordnumber)
-SELECT id, invnumber, transdate, vendor, amount, netamount, paid,
-datepaid, duedate, invoice, ordnumber
-FROM ap;
---
-DROP TABLE ap;
-ALTER TABLE newap RENAME TO ap;
---
-CREATE TABLE newar (
- id int DEFAULT nextval ( 'id' ),
- invnumber text,
- transdate date DEFAULT current_date,
- customer int,
- taxincluded bool DEFAULT FALSE,
- amount float,
- netamount float,
- paid float,
- datepaid date,
- duedate date,
- invoice bool DEFAULT FALSE,
- shippingpoint text,
- terms int2,
- notes text
-);
---
-INSERT INTO newar (id, invnumber, transdate, customer, amount, netamount, paid,
-datepaid, duedate, invoice, shippingpoint, terms, notes)
-SELECT id, invnumber, transdate, customer, amount, netamount, paid,
-datepaid, duedate, invoice, shippingpoint, terms, notes
-FROM ar;
---
-DROP TABLE ar;
-ALTER TABLE newar RENAME TO ar;
---
-CREATE TABLE newcustomer (
- id int DEFAULT nextval ( 'id' ),
- name varchar(35),
- addr1 varchar(35),
- addr2 varchar(35),
- addr3 varchar(35),
- contact varchar(35),
- phone varchar(20),
- fax varchar(20),
- email text,
- notes text,
- ytd float,
- discount float4,
- taxincluded bool,
- creditlimit float,
- terms int2,
- shiptoname varchar(35),
- shiptoaddr1 varchar(35),
- shiptoaddr2 varchar(35),
- shiptoaddr3 varchar(35),
- shiptocontact varchar(20),
- shiptophone varchar(20),
- shiptofax varchar(20),
- shiptoemail text
-);
-INSERT INTO newcustomer (
-id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, ytd,
-discount, creditlimit, terms, shiptoname, shiptoaddr1, shiptoaddr2,
-shiptoaddr3, shiptocontact, shiptophone, shiptofax, shiptoemail )
-SELECT id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, ytd,
-discount, creditlimit, terms, shiptoname, shiptoaddr1, shiptoaddr2,
-shiptoaddr3, shiptocontact, shiptophone, shiptofax, shiptoemail
-FROM customer;
---
-DROP TABLE customer;
-ALTER TABLE newcustomer RENAME TO customer;
---
-CREATE TABLE customertax (
- customer_id int,
- chart_id int
-);
---
-CREATE TABLE newdefaults (
- inventory_accno int,
- income_accno int,
- expense_accno int,
- invnumber text,
- ponumber text,
- yearend varchar(5),
- nativecurr varchar(3),
- weightunit varchar(5)
-);
---
-INSERT INTO newdefaults (
-inventory_accno, income_accno, expense_accno, invnumber, ponumber)
-SELECT inventory_accno, income_accno, expense_accno, invnumber, ponumber
-FROM defaults;
---
-DROP TABLE defaults;
-ALTER TABLE newdefaults RENAME TO defaults;
-UPDATE defaults SET yearend = '1/31', nativecurr = 'CAD', weightunit = 'kg';
---
-CREATE TABLE partstax (
- parts_id int,
- chart_id int
-);
---
-CREATE TABLE tax (
- chart_id int,
- rate float,
- number text
-);
---
-CREATE TABLE newvendor (
- id int DEFAULT nextval ( 'id' ),
- name varchar(35),
- addr1 varchar(35),
- addr2 varchar(35),
- addr3 varchar(35),
- contact varchar(35),
- phone varchar(20),
- fax varchar(20),
- email text,
- notes text,
- ytd float,
- discount float4,
- taxincluded bool,
- creditlimit float,
- terms int2
-);
---
-INSERT INTO newvendor (
-id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, ytd )
-SELECT id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, ytd
-FROM vendor;
---
-DROP TABLE vendor;
-ALTER TABLE newvendor RENAME TO vendor;
---
-CREATE TABLE vendortax (
- vendor_id int,
- chart_id int
-);
---
-ALTER TABLE chart RENAME TO oldchart;
---
-CREATE TABLE chart (
- id int DEFAULT nextval( 'id' ),
- accno int UNIQUE,
- description text,
- balance float,
- type char(1),
- gifi int,
- category char(1),
- link text
-);
---
-INSERT INTO chart SELECT * FROM oldchart;
---
-DROP TABLE oldchart;
---
-
Deleted: trunk/sql/Pg-upgrade-1.4.0-1.6.0.sql
===================================================================
--- trunk/sql/Pg-upgrade-1.4.0-1.6.0.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-1.4.0-1.6.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,126 +0,0 @@
-alter table acc_trans rename column accno to chart_id;
-update acc_trans set chart_id =
- (select id from chart where accno = acc_trans.chart_id);
---
-alter table parts rename column inventory_accno to inventory_accno_id;
-alter table parts rename column income_accno to income_accno_id;
-alter table parts rename column expense_accno to expense_accno_id;
-alter table parts rename column number to partnumber;
-update parts set inventory_accno_id =
- (select id from chart where chart.accno = parts.inventory_accno_id);
-update parts set income_accno_id =
- (select id from chart where chart.accno = parts.income_accno_id);
-update parts set expense_accno_id =
- (select id from chart where chart.accno = parts.expense_accno_id);
---
-create table assembly (id int, parts_id int, qty float);
---
-alter table defaults rename column inventory_accno to inventory_accno_id;
-alter table defaults rename column income_accno to income_accno_id;
-alter table defaults rename column expense_accno to expense_accno_id;
-alter table defaults add column businessnumber text;
-alter table defaults add column version varchar(8);
-update defaults set inventory_accno_id =
- (select id from chart where chart.accno = defaults.inventory_accno_id);
-update defaults set income_accno_id =
- (select id from chart where chart.accno = defaults.income_accno_id);
-update defaults set expense_accno_id =
- (select id from chart where chart.accno = defaults.expense_accno_id);
-update defaults set version = '1.6.0';
---
-alter table invoice rename column inventory_accno to inventory_accno_id;
-alter table invoice rename column income_accno to income_accno_id;
-alter table invoice rename column expense_accno to expense_accno_id;
-alter table invoice rename column number to partnumber;
-alter table invoice add column assemblyitem bool;
-update invoice set assemblyitem = 'f';
-update invoice set inventory_accno_id =
- (select id from chart where invoice.inventory_accno_id = chart.accno);
-update invoice set income_accno_id =
- (select id from chart where invoice.income_accno_id = chart.accno);
-update invoice set expense_accno_id =
- (select id from chart where invoice.expense_accno_id = chart.accno);
---
-alter table gl rename column comment to description;
---
-create table newvendor (
- id int default nextval ( 'id' ),
- 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 text,
- notes text,
- terms int2,
- taxincluded bool
-);
-insert into newvendor (
- id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, terms,
- taxincluded)
- select
- id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, terms,
- taxincluded from vendor;
-drop table vendor;
-alter table newvendor rename to vendor;
---
-create table newcustomer (
- id int default nextval ( 'id' ),
- 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 text,
- notes text,
- discount float4,
- taxincluded bool,
- creditlimit float,
- terms int2,
- shiptoname varchar(35),
- shiptoaddr1 varchar(35),
- shiptoaddr2 varchar(35),
- shiptoaddr3 varchar(35),
- shiptoaddr4 varchar(35),
- shiptocontact varchar(20),
- shiptophone varchar(20),
- shiptofax varchar(20),
- shiptoemail text
-);
-insert into newcustomer (
- id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, discount,
- taxincluded, creditlimit, terms, shiptoname, shiptoaddr1, shiptoaddr2,
- shiptoaddr3, shiptocontact, shiptophone, shiptofax, shiptoemail
- )
- select
- id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, discount,
- taxincluded, creditlimit, terms, shiptoname, shiptoaddr1, shiptoaddr2,
- shiptoaddr3, shiptocontact, shiptophone, shiptofax, shiptoemail
- from customer;
-drop table customer;
-alter table newcustomer rename to customer;
---
-drop index chart_accno_key;
-alter table chart rename to oldchart;
-create table chart (
- id int default nextval('id'),
- accno int unique,
- description text,
- charttype char(1),
- gifi int,
- category char(1),
- link text
-);
-insert into chart (id, accno, description, charttype, gifi, category, link)
- select id, accno, description, type, gifi, category, link from oldchart;
-drop table oldchart;
---
-alter table tax rename column number to taxnumber;
---
--- apply
Deleted: trunk/sql/Pg-upgrade-1.6.0-1.8.0.sql
===================================================================
--- trunk/sql/Pg-upgrade-1.6.0-1.8.0.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-1.6.0-1.8.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,104 +0,0 @@
---
-create table def (
- inventory_accno_id int,
- income_accno_id int,
- expense_accno_id int,
- fxgain_accno_id int,
- fxloss_accno_id int,
- invnumber text,
- ordnumber text,
- yearend varchar(5),
- weightunit varchar(5),
- businessnumber text,
- version varchar(8),
- curr text
-);
-insert into def (inventory_accno_id, income_accno_id, expense_accno_id, invnumber, ordnumber, yearend, weightunit, businessnumber, version, curr) select inventory_accno_id, income_accno_id, expense_accno_id, invnumber, ponumber, yearend, weightunit, businessnumber, version, nativecurr from defaults;
-drop table defaults;
-alter table def rename to defaults;
-update defaults set version = '1.8.0';
---
--- create a default accno for exchange rate gain and loss
---
-select accno into temp from chart where category = 'I' order by accno desc limit 1;
-update temp set accno = accno + 1;
-insert into chart (accno) select accno from temp;
-update chart set description = 'Foreign Exchange Gain', category = 'I', charttype = 'A' where accno = (select accno from temp);
-update defaults set fxgain_accno_id = (select id from chart where chart.accno = temp.accno);
-drop table temp;
-select accno into temp from chart where category = 'E' order by accno desc limit 1;
-update temp set accno = accno + 1;
-insert into chart (accno) select accno from temp;
-update chart set description = 'Foreign Exchange Loss', category = 'E', charttype = 'A' where accno = (select accno from temp);
-update defaults set fxloss_accno_id = (select id from chart where chart.accno = temp.accno);
-drop table temp;
---
-alter table parts add column bin text;
-alter table parts alter column onhand set default 0;
-update parts set onhand = 0 where onhand = NULL;
-alter table parts add column obsolete bool;
-alter table parts alter column obsolete set default 'f';
-update parts set obsolete = 'f';
---
-alter table ap rename column vendor to vendor_id;
-alter table ap add column curr char(3);
---
-alter table ar rename column customer to customer_id;
-alter table ar add column curr char(3);
-alter table ar add column ordnumber text;
---
-alter table acc_trans add column source text;
-alter table acc_trans add column cleared bool;
-alter table acc_trans alter column cleared set default 'f';
-alter table acc_trans add column fx_transaction bool;
-alter table acc_trans alter column fx_transaction set default 'f';
-update acc_trans set cleared = 'f', fx_transaction = 'f';
---
-create table oe (
- id int default nextval('id'),
- ordnumber text,
- transdate date default current_date,
- vendor_id int,
- customer_id int,
- amount float8,
- netamount float8,
- reqdate date,
- taxincluded bool,
- shippingpoint text,
- notes text,
- curr char(3)
-);
---
-create table orderitems (
- trans_id int,
- parts_id int,
- description text,
- qty float4,
- sellprice float8,
- discount float4
-);
---
-alter table invoice rename to invoiceold;
-create table invoice (
- id int default nextval('id'),
- trans_id int,
- parts_id int,
- description text,
- qty float4,
- allocated float4,
- sellprice float8,
- fxsellprice float8,
- discount float4,
- assemblyitem bool default 'f'
-);
-insert into invoice (id, trans_id, parts_id, description, qty, allocated, sellprice, fxsellprice, discount, assemblyitem) select id, trans_id, parts_id, description, qty, allocated, sellprice, sellprice, discount, assemblyitem from invoiceold;
-update invoice set assemblyitem = 'f' where assemblyitem = NULL;
-drop table invoiceold;
---
-create table exchangerate (
- curr char(3),
- transdate date,
- buy float8,
- sell float8
-);
---
Deleted: trunk/sql/Pg-upgrade-1.8.0-1.8.4.sql
===================================================================
--- trunk/sql/Pg-upgrade-1.8.0-1.8.4.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-1.8.0-1.8.4.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,21 +0,0 @@
---
-alter table chart add column gifi_accno text;
---
-create table gifi (accno text, description text);
-create unique index gifi_accno_key on gifi (accno);
---
-create table mtemp (parts_id int, name text);
-insert into mtemp select parts_id, name from makemodel;
-drop table makemodel;
-alter table mtemp rename to makemodel;
---
-alter table defaults add column closedto date;
-alter table defaults add column revtrans bool;
---
-alter table ap add column notes text;
---
-alter table customer add column businessnumber text;
-alter table vendor add column businessnumber text;
---
-update defaults set version = '1.8.4', revtrans = 'f';
---
Deleted: trunk/sql/Pg-upgrade-1.8.4-1.8.5.sql
===================================================================
--- trunk/sql/Pg-upgrade-1.8.4-1.8.5.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-1.8.4-1.8.5.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,63 +0,0 @@
---
-alter table customer rename column businessnumber to customernumber;
-create index customer_customernumber_key on customer (customernumber);
-alter table vendor rename column businessnumber to vendornumber;
-create index vendor_vendornumber_key on vendor (vendornumber);
---
-CREATE TABLE employee (
- id int DEFAULT nextval ('id'),
- login text,
- name varchar(35),
- addr1 varchar(35),
- addr2 varchar(35),
- addr3 varchar(35),
- addr4 varchar(35),
- workphone varchar(20),
- homephone varchar(20),
- startdate date default current_date,
- enddate date,
- notes text
-);
---
-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 column employee_id int;
-create index gl_employee_id_key on gl (employee_id);
-alter table ar add column employee_id int;
-create index ar_employee_id_key on ar (employee_id);
-alter table ap add column employee_id int;
-create index ap_employee_id_key on ap (employee_id);
-alter table oe add column employee_id int;
-create index oe_employee_id_key on oe (employee_id);
---
-alter table invoice add column unit varchar(5);
-alter table orderitems add column unit varchar(5);
---
-update chart set gifi_accno = '' where gifi_accno = NULL;
-alter table chart rename to chartold;
-CREATE TABLE chart (
- id int DEFAULT nextval ('id'),
- accno text NOT NULL,
- description text,
- charttype char(1) DEFAULT 'A',
- category char(1),
- link text,
- gifi_accno text
-);
-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 alter column inventory_accno_id drop default;
---
-alter table defaults rename ordnumber to sonumber;
-alter table defaults add column ponumber text;
---
-update defaults set version = '1.8.5', ponumber = sonumber;
---
Deleted: trunk/sql/Pg-upgrade-1.8.5-2.0.0.sql
===================================================================
--- trunk/sql/Pg-upgrade-1.8.5-2.0.0.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-1.8.5-2.0.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,92 +0,0 @@
---
-alter table customer add column cc text;
-alter table customer add column bcc text;
---
-alter table vendor add column cc text;
-alter table vendor add column bcc text;
---
-create table shipto (
- trans_id int,
- 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 text
-);
---
-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 int default nextval('id'),
- 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 text,
- notes text,
- discount float4,
- taxincluded bool,
- creditlimit float DEFAULT 0,
- terms int2 DEFAULT 0,
- customernumber text,
- cc text,
- bcc text
-);
-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 column bom boolean;
-alter table parts alter column bom set default 'f';
-update parts set bom = 'f';
-update parts set bom = 't' where assembly;
-alter table parts add column image text;
-alter table parts add column drawing text;
-alter table parts add column microfiche text;
---
-alter table gl add column notes text;
---
-alter table oe add column closed bool;
-alter table oe alter column closed set default 'f';
-update oe set closed = 'f';
---
-create table project (
- id int default nextval('id'),
- projectnumber text,
- description text
-);
---
-create index project_id_key on project (id);
---
-alter table acc_trans add column project_id int;
-update acc_trans set cleared = '0' where cleared = '1';
---
-alter table invoice add column project_id int;
-alter table invoice add column deliverydate date;
-alter table orderitems add column project_id int;
-alter table orderitems add column reqdate date;
---
-alter table gl rename source to reference;
-create index gl_reference_key on gl (reference);
-create index acc_trans_source_key on acc_trans (lower(source));
---
-update defaults set version = '2.0.0';
---
Deleted: trunk/sql/Pg-upgrade-2.0.0-2.0.8.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.0.0-2.0.8.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.0.0-2.0.8.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,12 +0,0 @@
---
-create table partsgroup (id int default nextval('id'), partsgroup text);
-create index partsgroup_id_key on partsgroup (id);
---
-alter table parts add partsgroup_id int;
---
-alter table assembly add bom bool;
-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';
---
-update defaults set version = '2.0.8';
---
Deleted: trunk/sql/Pg-upgrade-2.0.8-2.2.0.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.0.8-2.2.0.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.0.8-2.2.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -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 varchar(20);
-alter table ap add till varchar(20);
---
-update defaults set version = '2.2.0';
---
Deleted: trunk/sql/Pg-upgrade-2.2.0-2.3.0.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.2.0-2.3.0.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.2.0-2.3.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,59 +0,0 @@
---
-alter table oe add column quotation bool;
-alter table oe alter column quotation set default 'f';
-update oe set quotation = '0';
-alter table oe add column quonumber text;
---
-alter table defaults add column sqnumber text;
-alter table defaults add column rfqnumber text;
---
-alter table invoice add column serialnumber text;
---
-alter table ar add column quonumber text;
-create index ar_quonumber_key on ar (lower(quonumber));
-alter table ap add column quonumber text;
-create index ap_quonumber_key on ap (lower(quonumber));
---
-alter table employee add role text;
---
-alter table makemodel add column make text;
-alter table makemodel add column model text;
-update makemodel set make = substr(name,1,strpos(name,':')-1);
-update makemodel set model = substr(name,strpos(name,':')+1);
-create table temp (parts_id int,make text,model text);
-insert into temp (parts_id,make,model) select parts_id,make,model from makemodel;
-drop table makemodel;
-alter table temp rename to makemodel;
---
-create index makemodel_parts_id_key on makemodel (parts_id);
-create index makemodel_make_key on makemodel (lower(make));
-create index makemodel_model_key on makemodel (lower(model));
---
-create table status (trans_id int, formname text, printed bool default 'f', emailed bool default 'f', spoolfile text, chart_id int);
-create index status_trans_id_key on status (trans_id);
---
-create sequence invoiceid;
-select setval('invoiceid', (select max(id) from invoice));
-alter table invoice alter column id set default nextval('invoiceid');
---
-alter table ar add column intnotes text;
-alter table ap add column intnotes text;
-alter table oe add column intnotes text;
---
-create table department (id int default nextval('id'), description text, role char(1) default 'P');
-create index department_id_key on department (id);
---
-alter table ar add column department_id int;
-alter table ar alter column department_id set default 0;
-update ar set department_id = 0;
-alter table ap add column department_id int;
-alter table ap alter column department_id set default 0;
-update ap set department_id = 0;
-alter table gl add column department_id int;
-alter table gl alter column department_id set default 0;
-update gl set department_id = 0;
-alter table oe add column department_id int;
-alter table oe alter column department_id set default 0;
-update oe set department_id = 0;
---
-update defaults set version = '2.3.0';
Deleted: trunk/sql/Pg-upgrade-2.3.0-2.3.1.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.3.0-2.3.1.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.3.0-2.3.1.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,388 +0,0 @@
--- function check_department
-create function check_department() returns opaque as '
-
-declare
- dpt_id int;
-
-begin
-
- if new.department_id = 0 then
- delete from dpt_trans where trans_id = new.id;
- return NULL;
- end if;
-
- select into dpt_id trans_id from dpt_trans where trans_id = new.id;
-
- if dpt_id > 0 then
- update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
- else
- insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
- end if;
-return NULL;
-
-end;
-' language 'plpgsql';
--- end function
-
--- department transaction table
-create table dpt_trans (trans_id int, department_id int);
-
--- function del_department
-create function del_department() returns opaque as '
-begin
- delete from dpt_trans where trans_id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
--- end function
-
--- triggers
---
-create trigger check_department after insert or update on ar for each row execute procedure check_department();
--- end trigger
-create trigger check_department after insert or update on ap for each row execute procedure check_department();
--- end trigger
-create trigger check_department after insert or update on gl for each row execute procedure check_department();
--- end trigger
-create trigger check_department after insert or update on oe for each row execute procedure check_department();
--- end trigger
---
---
-create trigger del_department after delete on ar for each row execute procedure del_department();
--- end trigger
-create trigger del_department after delete on ap for each row execute procedure del_department();
--- end trigger
-create trigger del_department after delete on gl for each row execute procedure del_department();
--- end trigger
-create trigger del_department after delete on oe for each row execute procedure del_department();
--- end trigger
---
-
--- business table
-create table business (id int default nextval('id'), description text, discount float4);
---
--- SIC
-create table sic (code text, sictype char(1), description text);
---
-alter table vendor add column gifi_accno text;
-alter table vendor add column business_id int;
-alter table vendor add column taxnumber text;
-alter table vendor add column sic_code text;
---
-alter table customer add column business_id int;
-alter table customer add column taxnumber text;
-alter table customer add column sic_code text;
---
-create function del_customer() returns opaque as '
-begin
- delete from shipto where trans_id = old.id;
- delete from customertax where customer_id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-create function del_vendor() returns opaque as '
-begin
- delete from shipto where trans_id = old.id;
- delete from vendortax where vendor_id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-create trigger del_customer after delete on customer for each row execute procedure del_customer();
--- end trigger
-create trigger del_vendor after delete on vendor for each row execute procedure del_vendor();
--- end trigger
---
-alter table acc_trans add column memo text;
---
-alter table employee add column sales bool;
-alter table employee alter column sales set default 't';
---
-alter table vendor add discount float4;
-alter table vendor add creditlimit float;
---
--- function del_exchangerate
-create function del_exchangerate() returns opaque as '
-
-declare
- t_transdate date;
- t_curr char(3);
- t_id int;
- d_curr text;
-
-begin
-
- select into d_curr substr(curr,1,3) from defaults;
-
- if TG_RELNAME = ''ar'' then
- select into t_curr, t_transdate curr, transdate from ar where id = old.id;
- end if;
- if TG_RELNAME = ''ap'' then
- select into t_curr, t_transdate curr, transdate from ap where id = old.id;
- end if;
- if TG_RELNAME = ''oe'' then
- select into t_curr, t_transdate curr, transdate from oe where id = old.id;
- end if;
-
- if d_curr != t_curr then
-
- select into t_id a.id from acc_trans ac
- join ar a on (a.id = ac.trans_id)
- where a.curr = t_curr
- and ac.transdate = t_transdate
-
- except select a.id from ar a where a.id = old.id
-
- union
-
- select a.id from acc_trans ac
- join ap a on (a.id = ac.trans_id)
- where a.curr = t_curr
- and ac.transdate = t_transdate
-
- except select a.id from ap a where a.id = old.id
-
- union
-
- select o.id from oe o
- where o.curr = t_curr
- and o.transdate = t_transdate
-
- except select o.id from oe o where o.id = old.id;
-
- if not found then
- delete from exchangerate where curr = t_curr and transdate = t_transdate;
- end if;
- end if;
-return old;
-
-end;
-' language 'plpgsql';
--- end function
---
--- triggers
---
-create trigger del_exchangerate before delete on ar for each row execute procedure del_exchangerate();
--- end trigger
---
-create trigger del_exchangerate before delete on ap for each row execute procedure del_exchangerate();
--- end trigger
---
-create trigger del_exchangerate before delete on oe for each row execute procedure del_exchangerate();
--- end trigger
---
---
-alter table orderitems add ship float4;
-alter table orderitems add serialnumber text;
---
---
-create sequence orderitemsid maxvalue 100000 cycle;
-alter table orderitems add id int;
-alter table orderitems alter id set default nextval('orderitemsid');
---
-create table warehouse (id int default nextval('id'), description text);
---
-create table inventory (warehouse_id int, parts_id int, oe_id int, orderitems_id int, qty float4, shippingdate date);
---
--- update orderitems, fill in id
-create table temp (id int default nextval('orderitemsid'), tempid oid);
-insert into temp (tempid) select oid from orderitems;
-update orderitems set id = temp.id from temp where orderitems.oid = temp.tempid;
-drop table temp;
---
-create index orderitems_id_key on orderitems (id);
---
-alter table ar add shipvia text;
-alter table ap add shipvia text;
-alter table oe add shipvia text;
---
---
-alter table inventory add employee_id int;
---
---
-create function check_inventory() returns opaque as '
-
-declare
- itemid int;
- row_data inventory%rowtype;
-
-begin
-
- if not old.quotation then
- for row_data in select * from inventory where oe_id = old.id loop
- select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
-
- if itemid is null then
- delete from inventory where oe_id = old.id and orderitems_id = row_data.orderitems_id;
- end if;
- end loop;
- end if;
- return old;
-end;
-' language 'plpgsql';
--- end function
---
-create trigger check_inventory after update on oe for each row execute procedure check_inventory();
--- end trigger
---
---
-create table yearend (
- trans_id int,
- transdate date
-);
---
--- function del_yearend
-create function del_yearend() returns opaque as '
-begin
- delete from yearend where trans_id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
--- end function
-
--- triggers
---
-create trigger del_yearend after delete on gl for each row execute procedure del_yearend();
--- end trigger
---
---
-create table temp (
- id int default nextval('id'),
- name varchar(64),
- addr1 varchar(64),
- addr2 varchar(64),
- addr3 varchar(64),
- addr4 varchar(64),
- contact varchar(64),
- phone varchar(20),
- fax varchar(20),
- email text,
- notes text,
- discount float4,
- taxincluded bool,
- creditlimit float default 0,
- terms int2 default 0,
- customernumber varchar(64),
- cc text,
- bcc text,
- business_id int,
- taxnumber varchar(64),
- sic_code varchar(6),
- iban varchar(34),
- bic varchar(11)
-);
-insert into temp (id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, customernumber, cc, bcc, business_id, taxnumber, sic_code) select id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, customernumber, cc, bcc, business_id, taxnumber, sic_code from customer;
---
-drop table customer;
---
-alter table temp rename to customer;
---
-create index customer_id_key on 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 trigger del_customer after delete on customer for each row execute procedure del_customer();
--- end trigger
---
-create table temp (
- id int default nextval('id'),
- name varchar(64),
- addr1 varchar(64),
- addr2 varchar(64),
- addr3 varchar(64),
- addr4 varchar(64),
- contact varchar(64),
- phone varchar(20),
- fax varchar(20),
- email text,
- notes text,
- terms int2 default 0,
- taxincluded bool,
- vendornumber varchar(64),
- cc text,
- bcc text,
- gifi_accno varchar(30),
- business_id int,
- taxnumber varchar(64),
- sic_code varchar(6),
- discount float4,
- creditlimit float default 0,
- iban varchar(34),
- bic varchar(11)
-);
-insert into temp (id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, vendornumber, cc, bcc, business_id, taxnumber, sic_code) select id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, vendornumber, cc, bcc, business_id, taxnumber, sic_code from vendor;
---
-drop table vendor;
---
-alter table temp rename to vendor;
---
-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 trigger del_vendor after delete on vendor for each row execute procedure del_vendor();
--- end trigger
---
-create table temp (
- code varchar(6),
- sictype char(1),
- description text
-);
-insert into temp (code, sictype, description) select code, sictype, description from sic;
-drop table sic;
-alter table temp rename to sic;
---
-create table temp (
- trans_id int,
- shiptoname varchar(64),
- shiptoaddr1 varchar(64),
- shiptoaddr2 varchar(64),
- shiptoaddr3 varchar(64),
- shiptoaddr4 varchar(64),
- shiptocontact varchar(64),
- shiptophone varchar(20),
- shiptofax varchar(20),
- shiptoemail text
-);
-insert into temp (trans_id, shiptoname, shiptoaddr1, shiptoaddr2, shiptoaddr3, shiptoaddr4, shiptocontact, shiptophone, shiptofax, shiptoemail) select trans_id, shiptoname, shiptoaddr1, shiptoaddr2, shiptoaddr3, shiptoaddr4, shiptocontact, shiptophone, shiptofax, shiptoemail from shipto;
-drop table shipto;
-alter table temp rename to shipto;
-create index shipto_trans_id_key on shipto (trans_id);
---
---
-create table temp (
- id int default nextval('id'),
- login text,
- name varchar(64),
- addr1 varchar(64),
- addr2 varchar(64),
- addr3 varchar(64),
- addr4 varchar(64),
- workphone varchar(20),
- homephone varchar(20),
- startdate date default current_date,
- enddate date,
- notes text,
- role varchar(20),
- sales bool,
- email text,
- sin varchar(20),
- iban varchar(34),
- bic varchar(11)
-);
-insert into temp (id,login,name,addr1,addr2,addr3,addr4,workphone,homephone,startdate,enddate,notes,role,sales) select id,login,name,addr1,addr2,addr3,addr4,workphone,homephone,startdate,enddate,notes,role,sales from employee;
---
-drop table employee;
-alter table temp rename to employee;
---
-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);
---
-update defaults set version = '2.3.1';
-
Deleted: trunk/sql/Pg-upgrade-2.3.1-2.3.3.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.3.1-2.3.3.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.3.1-2.3.3.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,9 +0,0 @@
---
-create table partsvendor (vendor_id int, parts_id int, partnumber text, leadtime int2, lastcost float, curr char(3));
-create index partsvendor_vendor_id_key on partsvendor (vendor_id);
-create index partsvendor_parts_id_key on partsvendor (parts_id);
---
-alter table assembly add column adj bool;
-update assembly set adj = 't';
---
-update defaults set version = '2.3.3';
Deleted: trunk/sql/Pg-upgrade-2.3.3-2.3.4.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.3.3-2.3.4.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.3.3-2.3.4.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,6 +0,0 @@
---
-alter table customer add employee_id int;
-alter table vendor add employee_id int;
-alter table employee add managerid int;
---
-update defaults set version = '2.3.4';
Deleted: trunk/sql/Pg-upgrade-2.3.4-2.3.5.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.3.4-2.3.5.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.3.4-2.3.5.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,142 +0,0 @@
---
-create table temp (
- id int default nextval('id'),
- name varchar(64),
- address1 varchar(32),
- address2 varchar(32),
- city varchar(32),
- state varchar(32),
- zipcode varchar(10),
- country varchar(32),
- contact varchar(64),
- phone varchar(20),
- fax varchar(20),
- email text,
- notes text,
- discount float4,
- taxincluded bool default 'f',
- creditlimit float default 0,
- terms int2 default 0,
- customernumber varchar(32),
- cc text,
- bcc text,
- business_id int,
- taxnumber varchar(32),
- sic_code varchar(6),
- iban varchar(34),
- bic varchar(11),
- employee_id int
-);
---
-insert into temp (id,name,address1,city,country,state,contact,phone,fax,email,notes,discount,taxincluded,creditlimit,terms,customernumber,cc,bcc,business_id,taxnumber,sic_code,iban,bic,employee_id) select id,name,substr(addr1,1,32),substr(addr2,1,32),substr(addr3,1,32),substr(addr4,1,32),contact,phone,fax,email,notes,discount,taxincluded,creditlimit,terms,substr(customernumber,1,32),cc,bcc,business_id,substr(taxnumber,1,32),sic_code,iban,bic,employee_id from customer;
---
-drop table customer;
-alter table temp rename to customer;
---
-create index customer_id_key on 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 trigger del_customer after delete on customer for each row execute procedure del_customer();
--- end trigger
---
-create table temp (
- id int default nextval('id'),
- name varchar(64),
- address1 varchar(32),
- address2 varchar(32),
- city varchar(32),
- state varchar(32),
- zipcode varchar(10),
- country varchar(32),
- contact varchar(64),
- phone varchar(20),
- fax varchar(20),
- email text,
- notes text,
- terms int2 default 0,
- taxincluded bool default 'f',
- vendornumber varchar(32),
- cc text,
- bcc text,
- gifi_accno varchar(30),
- business_id int,
- taxnumber varchar(32),
- sic_code varchar(6),
- discount float4,
- creditlimit float default 0,
- iban varchar(34),
- bic varchar(11),
- employee_id int
-);
---
-insert into temp (id,name,address1,city,country,state,contact,phone,fax,email,notes,terms,taxincluded,vendornumber,cc,bcc,gifi_accno,business_id,taxnumber,sic_code,discount,creditlimit,iban,bic,employee_id) select id,name,substr(addr1,1,32),substr(addr2,1,32),substr(addr3,1,32),substr(addr4,1,32),contact,phone,fax,email,notes,terms,taxincluded,substr(vendornumber,1,32),cc,bcc,gifi_accno,business_id,substr(taxnumber,1,32),sic_code,discount,creditlimit,iban,bic,employee_id from vendor;
---
-drop table vendor;
-alter table temp rename to vendor;
---
-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 trigger del_vendor after delete on vendor for each row execute procedure del_vendor();
--- end trigger
---
-create table temp (
- trans_id int,
- shiptoname varchar(64),
- shiptoaddress1 varchar(32),
- shiptoaddress2 varchar(32),
- shiptocity varchar(32),
- shiptostate varchar(32),
- shiptozipcode varchar(10),
- shiptocountry varchar(32),
- shiptocontact varchar(64),
- shiptophone varchar(20),
- shiptofax varchar(20),
- shiptoemail text
-);
---
-insert into temp (trans_id,shiptoname,shiptoaddress1,shiptocity,shiptocountry,shiptostate,shiptocontact,shiptophone,shiptofax,shiptoemail) select trans_id,shiptoname,substr(shiptoaddr1,1,32),substr(shiptoaddr2,1,32),substr(shiptoaddr3,1,32),substr(shiptoaddr4,1,32),shiptocontact,shiptophone,shiptofax,shiptoemail from shipto;
---
-drop table shipto;
-alter table temp rename to shipto;
-create index shipto_trans_id_key on shipto (trans_id);
---
-create table temp (
- id int default nextval('id'),
- login text,
- name varchar(64),
- address1 varchar(32),
- address2 varchar(32),
- city varchar(32),
- state varchar(32),
- zipcode varchar(10),
- country varchar(32),
- workphone varchar(20),
- homephone varchar(20),
- startdate date default current_date,
- enddate date,
- notes text,
- role varchar(20),
- sales bool default 'f',
- email text,
- sin varchar(20),
- iban varchar(34),
- bic varchar(11),
- managerid int
-);
---
-insert into temp (id,login,name,address1,city,country,state,workphone,homephone,startdate,enddate,notes,role,sales,email,sin,iban,bic,managerid) select id,login,name,substr(addr1,1,32),substr(addr2,1,32),substr(addr3,1,32),substr(addr4,1,32),workphone,homephone,startdate,enddate,notes,role,sales,email,sin,iban,bic,managerid from employee;
---
-drop table employee;
-alter table temp rename to employee;
---
-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);
---
-update defaults set version = '2.3.5';
-
Deleted: trunk/sql/Pg-upgrade-2.3.5-2.3.6.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.3.5-2.3.6.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.3.5-2.3.6.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,15 +0,0 @@
---
-create table pricegroup (id int default nextval('id'), pricegroup text);
-create index pricegroup_pricegroup_key on pricegroup (pricegroup);
-create index pricegroup_id_key on pricegroup (id);
---
-create table partscustomer (parts_id int, customer_id int, pricegroup_id int, pricebreak float4, sellprice float, validfrom date, validto date);
---
-create table language (code varchar(6), description text);
-alter table customer add language_code varchar(6);
-alter table customer add pricegroup_id int;
---
-alter table vendor add language_code varchar(6);
-alter table vendor add pricegroup_id int;
---
-update defaults set version = '2.3.6';
Deleted: trunk/sql/Pg-upgrade-2.3.6-2.3.7.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.3.6-2.3.7.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.3.6-2.3.7.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,6 +0,0 @@
---
-alter table partscustomer add curr char(3);
-alter table customer add curr char(3);
-alter table vendor add curr char(3);
---
-update defaults set version = '2.3.7';
Deleted: trunk/sql/Pg-upgrade-2.3.7-2.3.8.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.3.7-2.3.8.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.3.7-2.3.8.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,16 +0,0 @@
---
-create table audittrail (
- trans_id int,
- tablename text,
- reference text,
- formname text,
- action text,
- transdate timestamp default current_timestamp,
- employee_id int
-);
-create index audittrail_trans_id_key on audittrail (trans_id);
---
-alter table defaults add audittrail bool;
-alter table defaults alter audittrail set default '0';
---
-update defaults set version = '2.3.8', audittrail = '0';
Deleted: trunk/sql/Pg-upgrade-2.3.8-2.3.9.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.3.8-2.3.9.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.3.8-2.3.9.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,15 +0,0 @@
---
-create table translation (
- trans_id int,
- language_code varchar(6),
- description text
-);
-create index translation_trans_id_key on translation (trans_id);
---
-alter table ar add language_code varchar(6);
-alter table ap add language_code varchar(6);
-alter table oe add language_code varchar(6);
---
-create unique index language_code_key on language (code);
---
-update defaults set version = '2.3.9';
Deleted: trunk/sql/Pg-upgrade-2.3.9-2.4.2.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.3.9-2.4.2.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.3.9-2.4.2.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,34 +0,0 @@
---
-drop trigger del_customer on customer;
-drop trigger del_vendor on vendor;
-drop function del_customer();
-drop function del_vendor();
---
-create function del_customer() returns opaque as '
-begin
- delete from shipto where trans_id = old.id;
- delete from customertax where customer_id = old.id;
- delete from partscustomer where customer_id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-create trigger del_customer after delete on customer for each row execute procedure del_customer();
--- end trigger
---
-create function del_vendor() returns opaque as '
-begin
- delete from shipto where trans_id = old.id;
- delete from vendortax where vendor_id = old.id;
- delete from partsvendor where vendor_id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-create trigger del_vendor after delete on vendor for each row execute procedure del_vendor();
--- end trigger
---
-update defaults set version = '2.4.2';
-
Deleted: trunk/sql/Pg-upgrade-2.4.2-2.4.3.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.4.2-2.4.3.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.4.2-2.4.3.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,17 +0,0 @@
---
-alter table defaults rename invnumber to sinumber;
-alter table defaults add vinumber text;
-alter table defaults add employeenumber text;
-alter table defaults add partnumber text;
-alter table defaults add customernumber text;
-alter table defaults add vendornumber text;
---
-alter table employee add employeenumber varchar(32);
---
-alter table customer add startdate date;
-alter table customer add enddate date;
---
-alter table vendor add startdate date;
-alter table vendor add enddate date;
---
-update defaults set version = '2.4.3';
Deleted: trunk/sql/Pg-upgrade-2.4.3-2.4.4.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.4.3-2.4.4.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.4.3-2.4.4.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,5 +0,0 @@
---
-alter table employee add dob date;
-alter table employee rename sin to ssn;
---
-update defaults set version = '2.4.4';
Deleted: trunk/sql/Pg-upgrade-2.4.4-2.5.0.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.4.4-2.5.0.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.4.4-2.5.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,31 +0,0 @@
---
-alter table ar add ponumber text;
-alter table ap add ponumber text;
-alter table oe add ponumber text;
---
-alter table project add startdate date;
-alter table project add enddate date;
---
-create table recurring (id int, reference text, startdate date, nextdate date, enddate date, repeat int2, unit varchar(6), howmany int, payment bool default 'f');
-create table recurringemail (id int, formname text, format text, message text);
-create table recurringprint (id int, formname text, format text, printer text);
---
-create function del_recurring() returns opaque as '
-begin
- delete from recurring where id = old.id;
- delete from recurringemail where id = old.id;
- delete from recurringprint where id = old.id;
- return NULL;
-end;
-' language 'plpgsql';
---end function
-create trigger del_recurring after delete on ar for each row execute procedure del_recurring();
--- end trigger
-create trigger del_recurring after delete on ap for each row execute procedure del_recurring();
--- end trigger
-create trigger del_recurring after delete on gl for each row execute procedure del_recurring();
--- end trigger
-create trigger del_recurring after delete on oe for each row execute procedure del_recurring();
--- end trigger
---
-update defaults set version = '2.5.0';
Deleted: trunk/sql/Pg-upgrade-2.5.0-2.5.2.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.5.0-2.5.2.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.5.0-2.5.2.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,136 +0,0 @@
---
-create sequence jcitemsid;
-create table jcitems (id int default nextval('jcitemsid'), project_id int, parts_id int, description text, qty float4, allocated float4, sellprice float8, fxsellprice float8, serialnumber text, checkedin timestamp with time zone, checkedout timestamp with time zone, employee_id int);
-create index jcitems_id_key on jcitems (id);
---
-alter table project add parts_id int;
-alter table project add production float;
-alter table project add completed float;
-alter table project add customer_id int;
-alter table project alter production set default 0;
-alter table project alter completed set default 0;
-update project set production = 0, completed = 0;
---
-alter table parts add project_id int;
---
-alter table parts add avgcost float;
---
-create function avgcost(int) returns float as '
-
-declare
-
-v_cost float;
-v_qty float;
-v_parts_id alias for $1;
-
-begin
-
- select into v_cost, v_qty sum(i.sellprice * i.qty), sum(i.qty)
- from invoice i
- join ap a on (a.id = i.trans_id)
- where i.parts_id = v_parts_id;
-
- if not v_qty is null then
- v_cost := v_cost/v_qty;
- end if;
-
- if v_cost is null then
- v_cost := 0;
- end if;
-
-return v_cost;
-
-end;
-' language 'plpgsql';
--- end function
---
-create function lastcost(int) returns float as '
-
-declare
-
-v_cost float;
-v_parts_id alias for $1;
-
-begin
-
- select into v_cost sellprice from invoice i
- join ap a on (a.id = i.trans_id)
- where i.parts_id = v_parts_id
- order by a.transdate desc
- limit 1;
-
- if v_cost is null then
- v_cost := 0;
- end if;
-
-return v_cost;
-
-end;
-' language 'plpgsql';
--- end function
---
-alter table inventory rename oe_id to trans_id;
---
-alter table ap add shippingpoint text;
-alter table ap add terms int2;
---
-drop trigger check_inventory on oe;
-drop function check_inventory();
-create function check_inventory() returns opaque as '
-
-declare
- itemid int;
- row_data inventory%rowtype;
-
-begin
-
- if not old.quotation then
- for row_data in select * from inventory where trans_id = old.id loop
- select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
-
- if itemid is null then
- delete from inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id;
- end if;
- end loop;
- end if;
-return old;
-end;
-' language 'plpgsql';
--- end function
---
-create trigger check_inventory after update on oe for each row execute procedure check_inventory();
--- end trigger
---
-alter table orderitems alter id drop default;
---
-create function temp() returns int as '
-
-declare
- v_last int;
-
-begin
-
- SELECT INTO v_last last_value FROM orderitemsid;
- drop sequence orderitemsid;
- create sequence orderitemsid;
- perform setval(''orderitemsid'', v_last);
-
-return NULL;
-end;
-' language 'plpgsql';
--- end function
---
-select temp();
-drop function temp();
---
-alter table orderitems alter id set default nextval('orderitemsid');
---
-alter table chart add contra boolean;
-alter table chart alter contra set default 'f';
-update chart set category = 'A', contra = '1' where category = 'C';
-update chart set contra = '0' where contra is null;
---
-alter table defaults add glnumber text;
---
-update defaults set version = '2.5.2';
-
Deleted: trunk/sql/Pg-upgrade-2.5.2-2.6.0.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.5.2-2.6.0.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.5.2-2.6.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,9 +0,0 @@
---
-alter table oe add terms smallint;
-alter table oe alter terms set default 0;
---
-alter table ap alter terms set default 0;
---
-delete from inventory where warehouse_id = 0;
---
-update defaults set version = '2.6.0';
Deleted: trunk/sql/Pg-upgrade-2.6.0-2.6.1.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.6.0-2.6.1.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.6.0-2.6.1.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,5 +0,0 @@
---
-alter table jcitems add notes text;
---
-update defaults set version = '2.6.1';
-
Deleted: trunk/sql/Pg-upgrade-2.6.1-2.6.2.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.6.1-2.6.2.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.6.1-2.6.2.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,60 +0,0 @@
---
-drop function avgcost(int);
-CREATE FUNCTION avgcost(int) RETURNS FLOAT AS '
-
-DECLARE
-
-v_cost float;
-v_qty float;
-v_parts_id alias for $1;
-
-BEGIN
-
- SELECT INTO v_cost, v_qty SUM(i.sellprice * i.qty), SUM(i.qty)
- FROM invoice i
- JOIN ap a ON (a.id = i.trans_id)
- WHERE i.parts_id = v_parts_id;
-
- IF v_cost IS NULL THEN
- v_cost := 0;
- END IF;
-
- IF NOT v_qty IS NULL THEN
- IF v_qty = 0 THEN
- v_cost := 0;
- ELSE
- v_cost := v_cost/v_qty;
- END IF;
- END IF;
-
-RETURN v_cost;
-END;
-' language 'plpgsql';
--- end function
---
-drop function lastcost(int);
-CREATE FUNCTION lastcost(int) RETURNS FLOAT AS '
-
-DECLARE
-
-v_cost float;
-v_parts_id alias for $1;
-
-BEGIN
-
- SELECT INTO v_cost sellprice FROM invoice i
- JOIN ap a ON (a.id = i.trans_id)
- WHERE i.parts_id = v_parts_id
- ORDER BY a.transdate desc, a.id desc
- LIMIT 1;
-
- IF v_cost IS NULL THEN
- v_cost := 0;
- END IF;
-
-RETURN v_cost;
-END;
-' language 'plpgsql';
--- end function
---
-update defaults set version = '2.6.2';
Deleted: trunk/sql/Pg-upgrade-2.6.12-2.6.17.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.6.12-2.6.17.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.6.12-2.6.17.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,19 +0,0 @@
-CREATE SEQUENCE session_session_id_seq;
-
-CREATE TABLE session(
-session_id INTEGER PRIMARY KEY DEFAULT nextval('session_session_id_seq'),
-sl_login VARCHAR(50),
-token CHAR(32),
-last_used TIMESTAMP default now()
-);
-
--- LOCK TABLE acc_trans;
-ALTER TABLE acc_trans ALTER COLUMN chart_id SET NOT NULL;
-
--- For older versions pre 8.0.3
-ALTER TABLE acc_trans ADD COLUMN amount2 NUMERIC;
-UPDATE acc_trans set amount2 = amount;
-ALTER TABLE acc_trans DROP COLUMN amount;
-ALTER TABLE acc_trans RENAME column amount2 TO amount;
-
-UPDATE defaults SET version = '2.6.17';
Deleted: trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,287 +0,0 @@
-ALTER TABLE chart ADD PRIMARY KEY (id);
--- linuxpoet:
--- adding primary key to acc_trans
--- We are using standard postgresql names for the sequence for consistency as we move forward
--- Do everything in a transaction in case it blows up
-
-LOCK acc_trans in EXCLUSIVE mode;
-ALTER TABLE acc_trans ADD COLUMN entry_id bigint;
-CREATE SEQUENCE acctrans_entry_id_seq;
-ALTER TABLE acc_trans ALTER COLUMN entry_id SET DEFAULT nextval('acctrans_entry_id_seq');
-UPDATE acc_trans SET entry_id = nextval('acctrans_entry_id_seq');
-ALTER TABLE acc_trans ADD PRIMARY key (entry_id);
-
--- We should probably add a foreign key to chart.id
-ALTER TABLE acc_trans ADD FOREIGN KEY (chart_id) REFERENCES chart (id);
-
--- Start changing floats
-ALTER TABLE acc_trans ALTER COLUMN amount TYPE NUMERIC;
-
--- This may break someone if they for some reason have an actual float type in the qty column
-ALTER TABLE invoice ALTER COLUMN qty TYPE numeric;
-
-ALTER TABLE invoice ALTER COLUMN allocated TYPE numeric;
-ALTER TABLE invoice ALTER COLUMN sellprice TYPE NUMERIC;
-ALTER TABLE invoice ALTER COLUMN fxsellprice TYPE NUMERIC;
-
-ALTER TABLE customer ALTER COLUMN discount TYPE numeric;
-ALTER TABLE customer ALTER COLUMN creditlimit TYPE NUMERIC;
-
-ALTER TABLE parts ALTER COLUMN listprice TYPE NUMERIC;
-ALTER TABLE parts ALTER COLUMN sellprice TYPE NUMERIC;
-ALTER TABLE parts ALTER COLUMN lastcost TYPE NUMERIC;
-ALTER TABLE parts ALTER COLUMN weight TYPE numeric;
-ALTER TABLE parts ALTER COLUMN onhand TYPE numeric;
-ALTER TABLE parts ALTER COLUMN avgcost TYPE NUMERIC;
-
-ALTER TABLE assembly ALTER COLUMN qty TYPE numeric;
-
-ALTER TABLE ar ALTER COLUMN amount TYPE NUMERIC;
-ALTER TABLE ar ALTER COLUMN netamount TYPE NUMERIC;
-ALTER TABLE ar ALTER COLUMN paid TYPE NUMERIC;
-
-ALTER TABLE ap ALTER COLUMN amount TYPE NUMERIC;
-ALTER TABLE ap ALTER COLUMN netamount TYPE NUMERIC;
-ALTER TABLE ap ALTER COLUMN paid TYPE NUMERIC;
-
-ALTER TABLE tax ALTER COLUMN rate TYPE numeric;
-
-ALTER TABLE oe ALTER COLUMN amount TYPE NUMERIC;
-ALTER TABLE oe ALTER COLUMN netamount TYPE NUMERIC;
-
-ALTER TABLE orderitems ALTER COLUMN qty TYPE numeric;
-ALTER TABLE orderitems ALTER COLUMN sellprice TYPE NUMERIC;
-ALTER TABLE orderitems ALTER COLUMN discount TYPE numeric;
-ALTER TABLE orderitems ALTER COLUMN ship TYPE numeric;
-
-ALTER TABLE exchangerate ALTER COLUMN buy TYPE numeric;
-ALTER TABLE exchangerate ALTER COLUMN sell TYPE numeric;
-
-ALTER TABLE vendor ALTER COLUMN discount TYPE numeric;
-ALTER TABLE vendor ALTER COLUMN creditlimit TYPE numeric;
-
-ALTER TABLE project ALTER COLUMN production TYPE numeric;
-ALTER TABLE project ALTER COLUMN completed TYPE numeric;
-
-ALTER TABLE business ALTER COLUMN discount TYPE numeric;
-
-ALTER TABLE inventory ALTER COLUMN qty TYPE numeric;
-
-ALTER TABLE partsvendor ALTER COLUMN lastcost TYPE NUMERIC;
-
-ALTER TABLE partscustomer ALTER COLUMN pricebreak TYPE numeric;
-ALTER TABLE partscustomer ALTER COLUMN sellprice TYPE NUMERIC;
-
-ALTER TABLE jcitems ALTER COLUMN qty TYPE numeric;
-ALTER TABLE jcitems ALTER COLUMN allocated TYPE numeric;
-ALTER TABLE jcitems ALTER COLUMN sellprice TYPE NUMERIC;
-ALTER TABLE jcitems ALTER COLUMN fxsellprice TYPE NUMERIC;
-
--- The query rewrite rule necessary to notify the email app that a new report
--- needs to be sent to the designated administrator.
--- By Chris Travers
--- ..hidden..
--- Licensed under the GNU GPL 2.0 or later at your option. See accompanying
--- GPL.txt
-
-CREATE OR REPLACE FUNCTION trigger_parts_short() RETURNS TRIGGER
-AS
-'
-BEGIN
- IF NEW.onhand >= NEW.rop THEN
- NOTIFY parts_short;
- END IF;
- RETURN NEW;
-END;
-' LANGUAGE PLPGSQL;
--- end function
-
-CREATE TRIGGER parts_short AFTER UPDATE ON parts
-FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short();
-
-create table transactions (
- id int PRIMARY KEY,
- table_name text
-);
-
-insert into transactions (id, table_name) SELECT id, 'ap' FROM ap;
-
-CREATE RULE ap_id_track_i AS ON insert TO ap
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ap');
-
-CREATE RULE ap_id_track_u AS ON update TO ap
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-insert into transactions (id, table_name) SELECT id, 'ar' FROM ar;
-
-CREATE RULE ar_id_track_i AS ON insert TO ar
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ar');
-
-CREATE RULE ar_id_track_u AS ON update TO ar
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'business' FROM business;
-
-CREATE RULE business_id_track_i AS ON insert TO business
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'business');
-
-CREATE RULE business_id_track_u AS ON update TO business
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'chart' FROM chart;
-
-CREATE RULE chart_id_track_i AS ON insert TO chart
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'chart');
-
-CREATE RULE chart_id_track_u AS ON update TO chart
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'customer' FROM customer;
-
-CREATE RULE customer_id_track_i AS ON insert TO customer
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'customer');
-
-CREATE RULE customer_id_track_u AS ON update TO customer
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'department' FROM department;
-
-CREATE RULE department_id_track_i AS ON insert TO department
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'department');
-
-CREATE RULE department_id_track_u AS ON update TO department
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'employee' FROM employee;
-
-CREATE RULE employee_id_track_i AS ON insert TO employee
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee');
-
-CREATE RULE employee_id_track_u AS ON update TO employee
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl;
-
-CREATE RULE gl_id_track_i AS ON insert TO gl
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'gl');
-
-CREATE RULE gl_id_track_u AS ON update TO gl
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'oe' FROM oe;
-
-CREATE RULE oe_id_track_i AS ON insert TO oe
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'oe');
-
-CREATE RULE oe_id_track_u AS ON update TO oe
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'parts' FROM parts;
-
-CREATE RULE parts_id_track_i AS ON insert TO parts
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'parts');
-
-CREATE RULE parts_id_track_u AS ON update TO parts
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'partsgroup' FROM partsgroup;
-
-CREATE RULE partsgroup_id_track_i AS ON insert TO partsgroup
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'partsgroup');
-
-CREATE RULE partsgroup_id_track_u AS ON update TO partsgroup
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'pricegroup' FROM pricegroup;
-
-CREATE RULE pricegroup_id_track_i AS ON insert TO pricegroup
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'pricegroup');
-
-CREATE RULE pricegroup_id_track_u AS ON update TO pricegroup
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'project' FROM project;
-
-CREATE RULE project_id_track_i AS ON insert TO project
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'project');
-
-CREATE RULE project_id_track_u AS ON update TO project
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'vendor' FROM vendor;
-
-CREATE RULE vendor_id_track_i AS ON insert TO vendor
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'vendor');
-
-CREATE RULE employee_id_track_u AS ON update TO vendor
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse;
-
-CREATE RULE warehouse_id_track_i AS ON insert TO warehouse
-DO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee');
-
-CREATE RULE warehouse_id_track_u AS ON update TO warehouse
-DO UPDATE transactions SET id = new.id WHERE id = old.id;
-
-
-CREATE TABLE custom_table_catalog (
-table_id SERIAL PRIMARY KEY,
-extends TEXT,
-table_name TEXT
-);
-
-CREATE TABLE custom_field_catalog (
-field_id SERIAL PRIMARY KEY,
-table_id INT REFERENCES custom_table_catalog,
-field_name TEXT
-);
-CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR)
-RETURNS BOOL AS
-'
-DECLARE
-table_name ALIAS FOR $1;
-new_field_name ALIAS FOR $2;
-field_datatype ALIAS FOR $3;
-
-BEGIN
- EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog
- WHERE extends = '''''' || table_name || '''''' '';
- IF NOT FOUND THEN
- BEGIN
- INSERT INTO custom_table_catalog (extends) VALUES (table_name);
- EXECUTE ''CREATE TABLE custom_''||table_name ||
- '' (row_id INT)'';
- EXCEPTION WHEN duplicate_table THEN
- -- do nothing
- END;
- END IF;
- EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id)
- VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog
- WHERE extends = ''''''|| table_name || ''''''))'';
- EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN ''
- || new_field_name || '' '' || field_datatype;
- RETURN TRUE;
-END;
-' LANGUAGE PLPGSQL;
--- end function
-
-CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR)
-RETURNS BOOL AS
-'
-DECLARE
-table_name ALIAS FOR $1;
-custom_field_name ALIAS FOR $2;
-BEGIN
- DELETE FROM custom_field_catalog
- WHERE field_name = custom_field_name AND
- table_id = (SELECT table_id FROM custom_table_catalog
- WHERE extends = table_name);
- EXECUTE ''ALTER TABLE custom_'' || table_name ||
- '' DROP COLUMN '' || custom_field_name;
- RETURN TRUE;
-END;
-' LANGUAGE PLPGSQL;
--- end function
-
-UPDATE defaults SET version = '2.6.18';
Deleted: trunk/sql/Pg-upgrade-2.6.18-2.6.19.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.6.18-2.6.19.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.6.18-2.6.19.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,277 +0,0 @@
-BEGIN;
-
-ALTER TABLE ap ADD PRIMARY KEY (id);
-
-ALTER TABLE ar ADD PRIMARY KEY (id);
-
-ALTER TABLE assembly ADD PRIMARY KEY (id, parts_id);
-
-ALTER TABLE business ADD PRIMARY KEY (id);
-
-ALTER TABLE customer ADD PRIMARY KEY (id);
-
-ALTER TABLE customertax ADD PRIMARY KEY (customer_id, chart_id);
-
-ALTER TABLE defaults ADD PRIMARY KEY (version);
-
-ALTER TABLE department ADD PRIMARY KEY (id);
-
-ALTER TABLE dpt_trans ADD PRIMARY KEY (trans_id);
-
-ALTER TABLE employee ADD PRIMARY KEY (id);
-
-ALTER TABLE exchangerate ADD PRIMARY KEY (curr, transdate);
-
-ALTER TABLE gifi ADD PRIMARY KEY (accno);
-
-ALTER TABLE gl ADD PRIMARY KEY (id);
-
-ALTER TABLE invoice ADD PRIMARY KEY (id);
-
-ALTER TABLE jcitems ADD PRIMARY KEY (id);
-
-ALTER TABLE language ADD PRIMARY KEY (code);
-
-ALTER TABLE makemodel ADD PRIMARY KEY (parts_id);
-
-ALTER TABLE oe ADD PRIMARY KEY (id);
-
-ALTER TABLE orderitems ADD PRIMARY KEY (id);
-
-ALTER TABLE parts ADD PRIMARY KEY (id);
-
-ALTER TABLE partsgroup ADD PRIMARY KEY (id);
-
-ALTER TABLE partstax ADD PRIMARY KEY (parts_id, chart_id);
-
-ALTER TABLE pricegroup ADD PRIMARY KEY (id);
-
-ALTER TABLE project ADD PRIMARY KEY (id);
-
-ALTER TABLE recurringemail ADD PRIMARY KEY (id);
-
-ALTER TABLE recurring ADD PRIMARY KEY (id);
-
-ALTER TABLE recurringprint ADD PRIMARY KEY (id);
-
-ALTER TABLE sic ADD PRIMARY KEY (code);
-
-ALTER TABLE status ADD PRIMARY KEY (trans_id);
-
-ALTER TABLE tax ADD PRIMARY KEY (chart_id);
-ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart (id);
-
-ALTER TABLE translation ADD PRIMARY KEY (trans_id, language_code);
-
-ALTER TABLE vendor ADD PRIMARY KEY (id);
-
-ALTER TABLE vendortax ADD PRIMARY KEY (vendor_id, chart_id);
-
-ALTER TABLE warehouse ADD PRIMARY KEY (id);
-
-ALTER TABLE yearend ADD PRIMARY KEY (trans_id);
-
-LOCK inventory in EXCLUSIVE mode;
-ALTER TABLE inventory ADD COLUMN entry_id bigint;
-CREATE SEQUENCE inventory_entry_id_seq;
-
-ALTER TABLE inventory ALTER COLUMN entry_id
-SET DEFAULT nextval('inventory_entry_id_seq');
-
-UPDATE inventory SET entry_id = nextval('inventory_entry_id_seq');
-ALTER TABLE inventory ADD PRIMARY key (entry_id);
-
-LOCK partscustomer IN EXCLUSIVE MODE;
-ALTER TABLE partscustomer ADD COLUMN entry_id int;
-CREATE SEQUENCE partscustomer_entry_id_seq;
-
-ALTER TABLE partscustomer ALTER COLUMN entry_id
-SET DEFAULT nextval('partscustomer_entry_id_seq');
-
-UPDATE partscustomer SET entry_id = nextval('partscustomer_entry_id_seq');
-ALTER TABLE partscustomer ADD PRIMARY KEY (entry_id);
-
-LOCK partsvendor IN EXCLUSIVE MODE;
-ALTER TABLE partsvendor ADD COLUMN entry_id int;
-CREATE SEQUENCE partsvendor_entry_id_seq;
-
-ALTER TABLE partsvendor ALTER COLUMN entry_id
-SET DEFAULT nextval('partsvendor_entry_id_seq');
-
-UPDATE partsvendor SET entry_id = nextval('partsvendor_entry_id_seq');
-ALTER TABLE partsvendor ADD PRIMARY KEY (entry_id);
-
-LOCK audittrail IN EXCLUSIVE MODE;
-ALTER TABLE audittrail ADD COLUMN entry_id int;
-CREATE SEQUENCE audittrail_entry_id_seq ;
-
-ALTER TABLE audittrail ALTER COLUMN entry_id
-SET DEFAULT nextval('audittrail_entry_id_seq');
-
-UPDATE audittrail SET entry_id = nextval('audittrail_entry_id_seq');
-ALTER TABLE audittrail ADD PRIMARY KEY (entry_id);
-
-LOCK shipto IN EXCLUSIVE MODE;
-ALTER TABLE shipto ADD COLUMN entry_id int;
-CREATE SEQUENCE shipto_entry_id_seq ;
-
-ALTER TABLE shipto ALTER COLUMN entry_id
-SET DEFAULT nextval('shipto_entry_id_seq');
-
-UPDATE shipto SET entry_id = nextval('shipto_entry_id_seq');
-ALTER TABLE shipto ADD PRIMARY KEY (entry_id);
-
-CREATE TABLE taxmodule (
- taxmodule_id serial PRIMARY KEY,
- taxmodulename text NOT NULL
-);
-
-INSERT INTO taxmodule (
- taxmodule_id, taxmodulename
- ) VALUES (
- 1, 'Simple'
-);
-
-LOCK tax IN EXCLUSIVE MODE;
-ALTER TABLE tax ADD COLUMN pass int DEFAULT 0;
-UPDATE tax SET pass = 0;
-ALTER TABLE tax ALTER COLUMN pass SET NOT NULL;
-
-ALTER TABLE tax ADD COLUMN taxmodule_id int REFERENCES taxmodule DEFAULT 1;
-UPDATE tax SET taxmodule_id = 1;
-ALTER TABLE tax ALTER COLUMN taxmodule_id SET NOT NULL;
-
--- Fixed session table and add users table --
-BEGIN;
-LOCK session in EXCLUSIVE MODE;
-ALTER TABLE session ADD CONSTRAINT session_token_check check (length(token::text) = 32);
-ALTER TABLE session ADD column user_id integer not null references users(id);
-LOCK users in EXCLUSIVE MODE;
-CREATE TABLE users (id serial UNIQUE, username varchar(30) PRIMARY KEY);
-COMMENT ON TABLE users 'username is the primary key because we don't want duplicate users';
-LOCK users_conf in EXCLUSIVE MODE;
-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,
- dbconnect 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),
- 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()';
-COMMIT;
-
--- Admin user --
-BEGIN;
-INSERT INTO users(username) VALUES ('admin');
-INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL);
-COMMIT;
-
--- Functions
-
-CREATE FUNCTION create_user(text) RETURNS int4 AS $$
- INSERT INTO users(username) VALUES ('$1');
- SELECT currval('user_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 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 $$;
-
-ALTER TABLE defaults RENAME TO old_defaults;
-
-CREATE TABLE defaults (
- setting_key TEXT PRIMARY KEY,
- value TEXT
-);
-
-COMMENT ON TABLE defaults IS $$This table replaces the old one column per value system with a simple key => value table$$;
-
-
-INSERT INTO defaults (setting_key, value)
-SELECT 'inventory_accno_id', inventory_accno_id FROM old_defaults
-UNION
-SELECT 'income_accno_id', income_accno_id FROM old_defaults
-UNION
-SELECT 'expense_accno_id', expense_accno_id FROM old_defaults
-UNION
-SELECT 'fxloss_accno_id', fxloss_accno_id FROM old_defaults
-UNION
-SELECT 'fxgain_accno_id', fxgain_accno_id FROM old_defaults
-UNION
-SELECT 'sinumber', sinumber FROM old_defaults
-UNION
-SELECT 'sonumber', sonumber FROM old_defaults
-UNION
-SELECT 'yearend', yearend FROM old_defaults
-UNION
-SELECT 'weightunit', weightunit FROM old_defaults
-UNION
-SELECT 'businessnumber', businessnumber FROM old_defaults
-UNION
-SELECT 'version', '1.2.0'
-UNION
-SELECT 'curr', curr FROM old_defaults
-UNION
-SELECT 'closedto', closedto FROM old_defaults
-UNION
-SELECT 'revtrans', revtrans FROM old_defaults
-UNION
-SELECT 'ponumber', ponumber FROM old_defaults
-UNION
-SELECT 'sqnumber', sqnumber FROM old_defaults
-UNION
-SELECT 'rfqnumber', rfqnumber FROM old_defaults
-UNION
-SELECT 'audittrail', audittrail FROM old_defaults
-UNION
-SELECT 'vinumber', vinumber FROM old_defaults
-UNION
-SELECT 'employeenumber', employeenumber FROM old_defaults
-UNION
-SELECT 'partnumber', partnumber FROM old_defaults
-UNION
-SELECT 'customernumber', customernumber FROM old_defaults
-UNION
-SELECT 'vendornumber', vendornumber FROM old_defaults
-UNION
-SELECT 'glnumber', glnumber FROM old_defaults
-UNION
-SELECT 'projectnumber', projectnumber FROM old_defaults
-UNION
-SELECT 'appname', 'LedgerSMB';
-
-DROP TABLE old_defaults;
-
-COMMIT;
Deleted: trunk/sql/Pg-upgrade-2.6.2-2.6.3.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.6.2-2.6.3.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.6.2-2.6.3.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,10 +0,0 @@
---
-delete from status where formname = 'receipt';
-delete from status where formname = 'check';
-create table statu (trans_id int, formname text, printed bool default 'f', emailed bool default 'f', spoolfile text);
-insert into statu select trans_id, formname, printed, emailed, spoolfile from status;
-drop table status;
-alter table statu rename to status;
-create index status_trans_id_key on status (trans_id);
---
-update defaults set version = '2.6.3';
Deleted: trunk/sql/Pg-upgrade-2.6.3-2.6.4.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.6.3-2.6.4.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.6.3-2.6.4.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,6 +0,0 @@
---
-alter table orderitems add notes text;
-alter table invoice add notes text;
-alter table acc_trans add invoice_id int;
---
-update defaults set version = '2.6.4';
Deleted: trunk/sql/Pg-upgrade-2.6.4-2.6.7.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.6.4-2.6.7.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.6.4-2.6.7.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,4 +0,0 @@
---
-alter table defaults add projectnumber text;
---
-update defaults set version = '2.6.7';
Deleted: trunk/sql/Pg-upgrade-2.6.7-2.6.12.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.6.7-2.6.12.sql 2006-10-31 04:47:13 UTC (rev 419)
+++ trunk/sql/Pg-upgrade-2.6.7-2.6.12.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -1,4 +0,0 @@
---
-alter table tax add validto date;
---
-update defaults set version = '2.6.12';
Copied: trunk/sql/legacy/Pg-upgrade-1.2.6-1.2.7.sql (from rev 413, trunk/sql/Pg-upgrade-1.2.6-1.2.7.sql)
===================================================================
--- trunk/sql/legacy/Pg-upgrade-1.2.6-1.2.7.sql (rev 0)
+++ trunk/sql/legacy/Pg-upgrade-1.2.6-1.2.7.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -0,0 +1,4 @@
+--
+-- add the field shiptoemail to the customer table
+--
+alter table customer add column shiptoemail text;
Copied: trunk/sql/legacy/Pg-upgrade-1.2.7-1.4.0.sql (from rev 413, trunk/sql/Pg-upgrade-1.2.7-1.4.0.sql)
===================================================================
--- trunk/sql/legacy/Pg-upgrade-1.2.7-1.4.0.sql (rev 0)
+++ trunk/sql/legacy/Pg-upgrade-1.2.7-1.4.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -0,0 +1,173 @@
+--
+CREATE TABLE newap (
+ id int DEFAULT nextval ( 'id' ),
+ invnumber text,
+ transdate date DEFAULT current_date,
+ vendor int,
+ taxincluded bool DEFAULT FALSE,
+ amount float,
+ netamount float,
+ paid float,
+ datepaid date,
+ duedate date,
+ invoice bool DEFAULT FALSE,
+ ordnumber text
+);
+--
+INSERT INTO newap (id, invnumber, transdate, vendor, amount, netamount, paid,
+datepaid, duedate, invoice, ordnumber)
+SELECT id, invnumber, transdate, vendor, amount, netamount, paid,
+datepaid, duedate, invoice, ordnumber
+FROM ap;
+--
+DROP TABLE ap;
+ALTER TABLE newap RENAME TO ap;
+--
+CREATE TABLE newar (
+ id int DEFAULT nextval ( 'id' ),
+ invnumber text,
+ transdate date DEFAULT current_date,
+ customer int,
+ taxincluded bool DEFAULT FALSE,
+ amount float,
+ netamount float,
+ paid float,
+ datepaid date,
+ duedate date,
+ invoice bool DEFAULT FALSE,
+ shippingpoint text,
+ terms int2,
+ notes text
+);
+--
+INSERT INTO newar (id, invnumber, transdate, customer, amount, netamount, paid,
+datepaid, duedate, invoice, shippingpoint, terms, notes)
+SELECT id, invnumber, transdate, customer, amount, netamount, paid,
+datepaid, duedate, invoice, shippingpoint, terms, notes
+FROM ar;
+--
+DROP TABLE ar;
+ALTER TABLE newar RENAME TO ar;
+--
+CREATE TABLE newcustomer (
+ id int DEFAULT nextval ( 'id' ),
+ name varchar(35),
+ addr1 varchar(35),
+ addr2 varchar(35),
+ addr3 varchar(35),
+ contact varchar(35),
+ phone varchar(20),
+ fax varchar(20),
+ email text,
+ notes text,
+ ytd float,
+ discount float4,
+ taxincluded bool,
+ creditlimit float,
+ terms int2,
+ shiptoname varchar(35),
+ shiptoaddr1 varchar(35),
+ shiptoaddr2 varchar(35),
+ shiptoaddr3 varchar(35),
+ shiptocontact varchar(20),
+ shiptophone varchar(20),
+ shiptofax varchar(20),
+ shiptoemail text
+);
+INSERT INTO newcustomer (
+id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, ytd,
+discount, creditlimit, terms, shiptoname, shiptoaddr1, shiptoaddr2,
+shiptoaddr3, shiptocontact, shiptophone, shiptofax, shiptoemail )
+SELECT id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, ytd,
+discount, creditlimit, terms, shiptoname, shiptoaddr1, shiptoaddr2,
+shiptoaddr3, shiptocontact, shiptophone, shiptofax, shiptoemail
+FROM customer;
+--
+DROP TABLE customer;
+ALTER TABLE newcustomer RENAME TO customer;
+--
+CREATE TABLE customertax (
+ customer_id int,
+ chart_id int
+);
+--
+CREATE TABLE newdefaults (
+ inventory_accno int,
+ income_accno int,
+ expense_accno int,
+ invnumber text,
+ ponumber text,
+ yearend varchar(5),
+ nativecurr varchar(3),
+ weightunit varchar(5)
+);
+--
+INSERT INTO newdefaults (
+inventory_accno, income_accno, expense_accno, invnumber, ponumber)
+SELECT inventory_accno, income_accno, expense_accno, invnumber, ponumber
+FROM defaults;
+--
+DROP TABLE defaults;
+ALTER TABLE newdefaults RENAME TO defaults;
+UPDATE defaults SET yearend = '1/31', nativecurr = 'CAD', weightunit = 'kg';
+--
+CREATE TABLE partstax (
+ parts_id int,
+ chart_id int
+);
+--
+CREATE TABLE tax (
+ chart_id int,
+ rate float,
+ number text
+);
+--
+CREATE TABLE newvendor (
+ id int DEFAULT nextval ( 'id' ),
+ name varchar(35),
+ addr1 varchar(35),
+ addr2 varchar(35),
+ addr3 varchar(35),
+ contact varchar(35),
+ phone varchar(20),
+ fax varchar(20),
+ email text,
+ notes text,
+ ytd float,
+ discount float4,
+ taxincluded bool,
+ creditlimit float,
+ terms int2
+);
+--
+INSERT INTO newvendor (
+id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, ytd )
+SELECT id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, ytd
+FROM vendor;
+--
+DROP TABLE vendor;
+ALTER TABLE newvendor RENAME TO vendor;
+--
+CREATE TABLE vendortax (
+ vendor_id int,
+ chart_id int
+);
+--
+ALTER TABLE chart RENAME TO oldchart;
+--
+CREATE TABLE chart (
+ id int DEFAULT nextval( 'id' ),
+ accno int UNIQUE,
+ description text,
+ balance float,
+ type char(1),
+ gifi int,
+ category char(1),
+ link text
+);
+--
+INSERT INTO chart SELECT * FROM oldchart;
+--
+DROP TABLE oldchart;
+--
+
Copied: trunk/sql/legacy/Pg-upgrade-1.4.0-1.6.0.sql (from rev 413, trunk/sql/Pg-upgrade-1.4.0-1.6.0.sql)
===================================================================
--- trunk/sql/legacy/Pg-upgrade-1.4.0-1.6.0.sql (rev 0)
+++ trunk/sql/legacy/Pg-upgrade-1.4.0-1.6.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -0,0 +1,126 @@
+alter table acc_trans rename column accno to chart_id;
+update acc_trans set chart_id =
+ (select id from chart where accno = acc_trans.chart_id);
+--
+alter table parts rename column inventory_accno to inventory_accno_id;
+alter table parts rename column income_accno to income_accno_id;
+alter table parts rename column expense_accno to expense_accno_id;
+alter table parts rename column number to partnumber;
+update parts set inventory_accno_id =
+ (select id from chart where chart.accno = parts.inventory_accno_id);
+update parts set income_accno_id =
+ (select id from chart where chart.accno = parts.income_accno_id);
+update parts set expense_accno_id =
+ (select id from chart where chart.accno = parts.expense_accno_id);
+--
+create table assembly (id int, parts_id int, qty float);
+--
+alter table defaults rename column inventory_accno to inventory_accno_id;
+alter table defaults rename column income_accno to income_accno_id;
+alter table defaults rename column expense_accno to expense_accno_id;
+alter table defaults add column businessnumber text;
+alter table defaults add column version varchar(8);
+update defaults set inventory_accno_id =
+ (select id from chart where chart.accno = defaults.inventory_accno_id);
+update defaults set income_accno_id =
+ (select id from chart where chart.accno = defaults.income_accno_id);
+update defaults set expense_accno_id =
+ (select id from chart where chart.accno = defaults.expense_accno_id);
+update defaults set version = '1.6.0';
+--
+alter table invoice rename column inventory_accno to inventory_accno_id;
+alter table invoice rename column income_accno to income_accno_id;
+alter table invoice rename column expense_accno to expense_accno_id;
+alter table invoice rename column number to partnumber;
+alter table invoice add column assemblyitem bool;
+update invoice set assemblyitem = 'f';
+update invoice set inventory_accno_id =
+ (select id from chart where invoice.inventory_accno_id = chart.accno);
+update invoice set income_accno_id =
+ (select id from chart where invoice.income_accno_id = chart.accno);
+update invoice set expense_accno_id =
+ (select id from chart where invoice.expense_accno_id = chart.accno);
+--
+alter table gl rename column comment to description;
+--
+create table newvendor (
+ id int default nextval ( 'id' ),
+ 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 text,
+ notes text,
+ terms int2,
+ taxincluded bool
+);
+insert into newvendor (
+ id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, terms,
+ taxincluded)
+ select
+ id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, terms,
+ taxincluded from vendor;
+drop table vendor;
+alter table newvendor rename to vendor;
+--
+create table newcustomer (
+ id int default nextval ( 'id' ),
+ 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 text,
+ notes text,
+ discount float4,
+ taxincluded bool,
+ creditlimit float,
+ terms int2,
+ shiptoname varchar(35),
+ shiptoaddr1 varchar(35),
+ shiptoaddr2 varchar(35),
+ shiptoaddr3 varchar(35),
+ shiptoaddr4 varchar(35),
+ shiptocontact varchar(20),
+ shiptophone varchar(20),
+ shiptofax varchar(20),
+ shiptoemail text
+);
+insert into newcustomer (
+ id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, discount,
+ taxincluded, creditlimit, terms, shiptoname, shiptoaddr1, shiptoaddr2,
+ shiptoaddr3, shiptocontact, shiptophone, shiptofax, shiptoemail
+ )
+ select
+ id, name, addr1, addr2, addr3, contact, phone, fax, email, notes, discount,
+ taxincluded, creditlimit, terms, shiptoname, shiptoaddr1, shiptoaddr2,
+ shiptoaddr3, shiptocontact, shiptophone, shiptofax, shiptoemail
+ from customer;
+drop table customer;
+alter table newcustomer rename to customer;
+--
+drop index chart_accno_key;
+alter table chart rename to oldchart;
+create table chart (
+ id int default nextval('id'),
+ accno int unique,
+ description text,
+ charttype char(1),
+ gifi int,
+ category char(1),
+ link text
+);
+insert into chart (id, accno, description, charttype, gifi, category, link)
+ select id, accno, description, type, gifi, category, link from oldchart;
+drop table oldchart;
+--
+alter table tax rename column number to taxnumber;
+--
+-- apply
Copied: trunk/sql/legacy/Pg-upgrade-1.6.0-1.8.0.sql (from rev 413, trunk/sql/Pg-upgrade-1.6.0-1.8.0.sql)
===================================================================
--- trunk/sql/legacy/Pg-upgrade-1.6.0-1.8.0.sql (rev 0)
+++ trunk/sql/legacy/Pg-upgrade-1.6.0-1.8.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -0,0 +1,104 @@
+--
+create table def (
+ inventory_accno_id int,
+ income_accno_id int,
+ expense_accno_id int,
+ fxgain_accno_id int,
+ fxloss_accno_id int,
+ invnumber text,
+ ordnumber text,
+ yearend varchar(5),
+ weightunit varchar(5),
+ businessnumber text,
+ version varchar(8),
+ curr text
+);
+insert into def (inventory_accno_id, income_accno_id, expense_accno_id, invnumber, ordnumber, yearend, weightunit, businessnumber, version, curr) select inventory_accno_id, income_accno_id, expense_accno_id, invnumber, ponumber, yearend, weightunit, businessnumber, version, nativecurr from defaults;
+drop table defaults;
+alter table def rename to defaults;
+update defaults set version = '1.8.0';
+--
+-- create a default accno for exchange rate gain and loss
+--
+select accno into temp from chart where category = 'I' order by accno desc limit 1;
+update temp set accno = accno + 1;
+insert into chart (accno) select accno from temp;
+update chart set description = 'Foreign Exchange Gain', category = 'I', charttype = 'A' where accno = (select accno from temp);
+update defaults set fxgain_accno_id = (select id from chart where chart.accno = temp.accno);
+drop table temp;
+select accno into temp from chart where category = 'E' order by accno desc limit 1;
+update temp set accno = accno + 1;
+insert into chart (accno) select accno from temp;
+update chart set description = 'Foreign Exchange Loss', category = 'E', charttype = 'A' where accno = (select accno from temp);
+update defaults set fxloss_accno_id = (select id from chart where chart.accno = temp.accno);
+drop table temp;
+--
+alter table parts add column bin text;
+alter table parts alter column onhand set default 0;
+update parts set onhand = 0 where onhand = NULL;
+alter table parts add column obsolete bool;
+alter table parts alter column obsolete set default 'f';
+update parts set obsolete = 'f';
+--
+alter table ap rename column vendor to vendor_id;
+alter table ap add column curr char(3);
+--
+alter table ar rename column customer to customer_id;
+alter table ar add column curr char(3);
+alter table ar add column ordnumber text;
+--
+alter table acc_trans add column source text;
+alter table acc_trans add column cleared bool;
+alter table acc_trans alter column cleared set default 'f';
+alter table acc_trans add column fx_transaction bool;
+alter table acc_trans alter column fx_transaction set default 'f';
+update acc_trans set cleared = 'f', fx_transaction = 'f';
+--
+create table oe (
+ id int default nextval('id'),
+ ordnumber text,
+ transdate date default current_date,
+ vendor_id int,
+ customer_id int,
+ amount float8,
+ netamount float8,
+ reqdate date,
+ taxincluded bool,
+ shippingpoint text,
+ notes text,
+ curr char(3)
+);
+--
+create table orderitems (
+ trans_id int,
+ parts_id int,
+ description text,
+ qty float4,
+ sellprice float8,
+ discount float4
+);
+--
+alter table invoice rename to invoiceold;
+create table invoice (
+ id int default nextval('id'),
+ trans_id int,
+ parts_id int,
+ description text,
+ qty float4,
+ allocated float4,
+ sellprice float8,
+ fxsellprice float8,
+ discount float4,
+ assemblyitem bool default 'f'
+);
+insert into invoice (id, trans_id, parts_id, description, qty, allocated, sellprice, fxsellprice, discount, assemblyitem) select id, trans_id, parts_id, description, qty, allocated, sellprice, sellprice, discount, assemblyitem from invoiceold;
+update invoice set assemblyitem = 'f' where assemblyitem = NULL;
+drop table invoiceold;
+--
+create table exchangerate (
+ curr char(3),
+ transdate date,
+ buy float8,
+ sell float8
+);
+--
Copied: trunk/sql/legacy/Pg-upgrade-1.8.0-1.8.4.sql (from rev 413, trunk/sql/Pg-upgrade-1.8.0-1.8.4.sql)
===================================================================
--- trunk/sql/legacy/Pg-upgrade-1.8.0-1.8.4.sql (rev 0)
+++ trunk/sql/legacy/Pg-upgrade-1.8.0-1.8.4.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -0,0 +1,21 @@
+--
+alter table chart add column gifi_accno text;
+--
+create table gifi (accno text, description text);
+create unique index gifi_accno_key on gifi (accno);
+--
+create table mtemp (parts_id int, name text);
+insert into mtemp select parts_id, name from makemodel;
+drop table makemodel;
+alter table mtemp rename to makemodel;
+--
+alter table defaults add column closedto date;
+alter table defaults add column revtrans bool;
+--
+alter table ap add column notes text;
+--
+alter table customer add column businessnumber text;
+alter table vendor add column businessnumber text;
+--
+update defaults set version = '1.8.4', revtrans = 'f';
+--
Copied: trunk/sql/legacy/Pg-upgrade-1.8.4-1.8.5.sql (from rev 413, trunk/sql/Pg-upgrade-1.8.4-1.8.5.sql)
===================================================================
--- trunk/sql/legacy/Pg-upgrade-1.8.4-1.8.5.sql (rev 0)
+++ trunk/sql/legacy/Pg-upgrade-1.8.4-1.8.5.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -0,0 +1,63 @@
+--
+alter table customer rename column businessnumber to customernumber;
+create index customer_customernumber_key on customer (customernumber);
+alter table vendor rename column businessnumber to vendornumber;
+create index vendor_vendornumber_key on vendor (vendornumber);
+--
+CREATE TABLE employee (
+ id int DEFAULT nextval ('id'),
+ login text,
+ name varchar(35),
+ addr1 varchar(35),
+ addr2 varchar(35),
+ addr3 varchar(35),
+ addr4 varchar(35),
+ workphone varchar(20),
+ homephone varchar(20),
+ startdate date default current_date,
+ enddate date,
+ notes text
+);
+--
+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 column employee_id int;
+create index gl_employee_id_key on gl (employee_id);
+alter table ar add column employee_id int;
+create index ar_employee_id_key on ar (employee_id);
+alter table ap add column employee_id int;
+create index ap_employee_id_key on ap (employee_id);
+alter table oe add column employee_id int;
+create index oe_employee_id_key on oe (employee_id);
+--
+alter table invoice add column unit varchar(5);
+alter table orderitems add column unit varchar(5);
+--
+update chart set gifi_accno = '' where gifi_accno = NULL;
+alter table chart rename to chartold;
+CREATE TABLE chart (
+ id int DEFAULT nextval ('id'),
+ accno text NOT NULL,
+ description text,
+ charttype char(1) DEFAULT 'A',
+ category char(1),
+ link text,
+ gifi_accno text
+);
+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 alter column inventory_accno_id drop default;
+--
+alter table defaults rename ordnumber to sonumber;
+alter table defaults add column ponumber text;
+--
+update defaults set version = '1.8.5', ponumber = sonumber;
+--
Copied: trunk/sql/legacy/Pg-upgrade-1.8.5-2.0.0.sql (from rev 413, trunk/sql/Pg-upgrade-1.8.5-2.0.0.sql)
===================================================================
--- trunk/sql/legacy/Pg-upgrade-1.8.5-2.0.0.sql (rev 0)
+++ trunk/sql/legacy/Pg-upgrade-1.8.5-2.0.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -0,0 +1,92 @@
+--
+alter table customer add column cc text;
+alter table customer add column bcc text;
+--
+alter table vendor add column cc text;
+alter table vendor add column bcc text;
+--
+create table shipto (
+ trans_id int,
+ 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 text
+);
+--
+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 int default nextval('id'),
+ 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 text,
+ notes text,
+ discount float4,
+ taxincluded bool,
+ creditlimit float DEFAULT 0,
+ terms int2 DEFAULT 0,
+ customernumber text,
+ cc text,
+ bcc text
+);
+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 column bom boolean;
+alter table parts alter column bom set default 'f';
+update parts set bom = 'f';
+update parts set bom = 't' where assembly;
+alter table parts add column image text;
+alter table parts add column drawing text;
+alter table parts add column microfiche text;
+--
+alter table gl add column notes text;
+--
+alter table oe add column closed bool;
+alter table oe alter column closed set default 'f';
+update oe set closed = 'f';
+--
+create table project (
+ id int default nextval('id'),
+ projectnumber text,
+ description text
+);
+--
+create index project_id_key on project (id);
+--
+alter table acc_trans add column project_id int;
+update acc_trans set cleared = '0' where cleared = '1';
+--
+alter table invoice add column project_id int;
+alter table invoice add column deliverydate date;
+alter table orderitems add column project_id int;
+alter table orderitems add column reqdate date;
+--
+alter table gl rename source to reference;
+create index gl_reference_key on gl (reference);
+create index acc_trans_source_key on acc_trans (lower(source));
+--
+update defaults set version = '2.0.0';
+--
Copied: trunk/sql/legacy/Pg-upgrade-2.0.0-2.0.8.sql (from rev 413, trunk/sql/Pg-upgrade-2.0.0-2.0.8.sql)
===================================================================
--- trunk/sql/legacy/Pg-upgrade-2.0.0-2.0.8.sql (rev 0)
+++ trunk/sql/legacy/Pg-upgrade-2.0.0-2.0.8.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -0,0 +1,12 @@
+--
+create table partsgroup (id int default nextval('id'), partsgroup text);
+create index partsgroup_id_key on partsgroup (id);
+--
+alter table parts add partsgroup_id int;
+--
+alter table assembly add bom bool;
+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';
+--
+update defaults set version = '2.0.8';
+--
Copied: trunk/sql/legacy/Pg-upgrade-2.0.8-2.2.0.sql (from rev 413, trunk/sql/Pg-upgrade-2.0.8-2.2.0.sql)
===================================================================
--- trunk/sql/legacy/Pg-upgrade-2.0.8-2.2.0.sql (rev 0)
+++ trunk/sql/legacy/Pg-upgrade-2.0.8-2.2.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -0,0 +1,9 @@
+--
+create unique index projectnumber_key on project (projectnumber);
+create unique index partsgroup_key on partsgroup (partsgroup);
+--
+alter table ar add till varchar(20);
+alter table ap add till varchar(20);
+--
+update defaults set version = '2.2.0';
+--
Copied: trunk/sql/legacy/Pg-upgrade-2.2.0-2.3.0.sql (from rev 413, trunk/sql/Pg-upgrade-2.2.0-2.3.0.sql)
===================================================================
--- trunk/sql/legacy/Pg-upgrade-2.2.0-2.3.0.sql (rev 0)
+++ trunk/sql/legacy/Pg-upgrade-2.2.0-2.3.0.sql 2006-10-31 05:42:52 UTC (rev 420)
@@ -0,0 +1,59 @@
+--
+alter table oe add column quotation bool;
+alter table oe alter column quotation set default 'f';
+update oe set quotation = '0';
+alter table oe add column quonumber text;
+--
+alter table defaults add column sqnumber text;
+alter table defaults add column rfqnumber text;
+--
+alter table invoice add column serialnumber text;
+--
+alter table ar add column quonumber text;
+create index ar_quonumber_key on ar (lower(quonumber));
+alter table ap add column quonumber text;
+create index ap_quonumber_key on ap (lower(quonumber));
+--
+alter table employee add role text;
+--
+alter table makemodel add column make text;
+alter table makemodel add column model text;
+update makemodel set make = substr(name,1,strpos(name,':')-1);
+update makemodel set model = substr(name,strpos(name,':')+1);
+create table temp (parts_id int,make text,model text);
+insert into temp (parts_id,make,model) select parts_id,make,model from makemodel;
+drop table makemodel;
+alter table temp rename to makemodel;
+--
@@ Diff output truncated at 100000 characters. @@
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.