summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rwxr-xr-xsql/Pg-tables.sql88
-rw-r--r--sql/Pg-upgrade-2.6.17-2.6.18.sql62
2 files changed, 106 insertions, 44 deletions
diff --git a/sql/Pg-tables.sql b/sql/Pg-tables.sql
index 3f9b2a24..c826d357 100755
--- a/sql/Pg-tables.sql
+++ b/sql/Pg-tables.sql
@@ -75,7 +75,7 @@ INSERT INTO defaults (version) VALUES ('2.6.17');
CREATE TABLE acc_trans (
trans_id int,
chart_id int NOT NULL,
- amount float,
+ amount numeric(10,2),
transdate date DEFAULT current_date,
source text,
cleared bool DEFAULT 'f',
@@ -90,11 +90,11 @@ CREATE TABLE invoice (
trans_id int,
parts_id int,
description text,
- qty float4,
- allocated float4,
- sellprice float,
- fxsellprice float,
- discount float4,
+ qty integer,
+ allocated integer,
+ sellprice numeric(10,2),
+ fxsellprice numeric(10,2),
+ discount float4, -- jd: check into this
assemblyitem bool DEFAULT 'f',
unit varchar(5),
project_id int,
@@ -117,9 +117,9 @@ CREATE TABLE customer (
fax varchar(20),
email text,
notes text,
- discount float4,
+ discount numeric,
taxincluded bool default 'f',
- creditlimit float default 0,
+ creditlimit numeric(10,2) default 0,
terms int2 default 0,
customernumber varchar(32),
cc text,
@@ -143,17 +143,17 @@ CREATE TABLE parts (
partnumber text,
description text,
unit varchar(5),
- listprice float,
- sellprice float,
- lastcost float,
+ listprice numeric(10,2),
+ sellprice numeric(10,2),
+ lastcost numeric(10,2),
priceupdate date DEFAULT current_date,
- weight float4,
- onhand float4 DEFAULT 0,
+ weight numeric,
+ onhand numeric DEFAULT 0,
notes text,
makemodel bool DEFAULT 'f',
assembly bool DEFAULT 'f',
alternate bool DEFAULT 'f',
- rop float4,
+ rop float4, -- jd: what is this
inventory_accno_id int,
income_accno_id int,
expense_accno_id int,
@@ -165,13 +165,13 @@ CREATE TABLE parts (
microfiche text,
partsgroup_id int,
project_id int,
- avgcost float
+ avgcost numeric(10,2)
);
--
CREATE TABLE assembly (
id int,
parts_id int,
- qty float,
+ qty numeric,
bom bool,
adj bool
) WITH OIDS;
@@ -182,9 +182,9 @@ CREATE TABLE ar (
transdate date DEFAULT current_date,
customer_id int,
taxincluded bool,
- amount float,
- netamount float,
- paid float,
+ amount numeric(10,2),
+ netamount numeric(10,2),
+ paid numeric(10,2),
datepaid date,
duedate date,
invoice bool DEFAULT 'f',
@@ -209,9 +209,9 @@ CREATE TABLE ap (
transdate date DEFAULT current_date,
vendor_id int,
taxincluded bool DEFAULT 'f',
- amount float,
- netamount float,
- paid float,
+ amount numeric(10,2),
+ netamount numeric(10,2),
+ paid numeric(10,2),
datepaid date,
duedate date,
invoice bool DEFAULT 'f',
@@ -237,7 +237,7 @@ CREATE TABLE partstax (
--
CREATE TABLE tax (
chart_id int,
- rate float,
+ rate numeric,
taxnumber text,
validto date
);
@@ -258,8 +258,8 @@ CREATE TABLE oe (
transdate date default current_date,
vendor_id int,
customer_id int,
- amount float8,
- netamount float8,
+ amount numeric(10,2),
+ netamount numeric(10,2),
reqdate date,
taxincluded bool,
shippingpoint text,
@@ -282,13 +282,13 @@ CREATE TABLE orderitems (
trans_id int,
parts_id int,
description text,
- qty float4,
- sellprice float8,
- discount float4,
+ qty numeric,
+ sellprice numeric(10,2),
+ discount numeric,
unit varchar(5),
project_id int,
reqdate date,
- ship float4,
+ ship numeric,
serialnumber text,
notes text
) WITH OIDS;
@@ -296,8 +296,8 @@ CREATE TABLE orderitems (
CREATE TABLE exchangerate (
curr char(3),
transdate date,
- buy float8,
- sell float8
+ buy numeric,
+ sell numeric
);
--
create table employee (
@@ -364,8 +364,8 @@ CREATE TABLE vendor (
business_id int,
taxnumber varchar(32),
sic_code varchar(6),
- discount float4,
- creditlimit float default 0,
+ discount numeric,
+ creditlimit numeric default 0,
iban varchar(34),
bic varchar(11),
employee_id int,
@@ -383,8 +383,8 @@ CREATE TABLE project (
startdate date,
enddate date,
parts_id int,
- production float default 0,
- completed float default 0,
+ production numeric default 0,
+ completed numeric default 0,
customer_id int
);
--
@@ -417,7 +417,7 @@ CREATE TABLE dpt_trans (
CREATE TABLE business (
id int default nextval('id'),
description text,
- discount float4
+ discount numeric
);
--
-- SIC
@@ -437,7 +437,7 @@ CREATE TABLE inventory (
parts_id int,
trans_id int,
orderitems_id int,
- qty float4,
+ qty numeric,
shippingdate date,
employee_id int
) WITH OIDS;
@@ -452,7 +452,7 @@ CREATE TABLE partsvendor (
parts_id int,
partnumber text,
leadtime int2,
- lastcost float,
+ lastcost numeric(10,2),
curr char(3)
);
--
@@ -465,8 +465,8 @@ CREATE TABLE partscustomer (
parts_id int,
customer_id int,
pricegroup_id int,
- pricebreak float4,
- sellprice float,
+ pricebreak numeric,
+ sellprice numeric(10,2),
validfrom date,
validto date,
curr char(3)
@@ -524,10 +524,10 @@ CREATE TABLE jcitems (
project_id int,
parts_id int,
description text,
- qty float4,
- allocated float4,
- sellprice float8,
- fxsellprice float8,
+ qty numeric,
+ allocated numeric,
+ sellprice numeric(10,2),
+ fxsellprice numeric(10,2),
serialnumber text,
checkedin timestamp with time zone,
checkedout timestamp with time zone,
diff --git a/sql/Pg-upgrade-2.6.17-2.6.18.sql b/sql/Pg-upgrade-2.6.17-2.6.18.sql
index 4cf7c9e0..a0ad36d5 100644
--- a/sql/Pg-upgrade-2.6.17-2.6.18.sql
+++ b/sql/Pg-upgrade-2.6.17-2.6.18.sql
@@ -11,6 +11,68 @@ ALTER TABLE acc_trans ALTER COLUMN entry_id SET DEFAULT nextval('acctrans_entry_
UPDATE acc_trans SET entry_id = nextval('acctrans_entry_id_seq');
ALTER TABLE acc_trans ADD PRIMARY key (entry_id);
+-- Start changing floats
+ALTER TABLE acc_trans ALTER COLUMN amount TYPE numeric(10,2);
+
+-- 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(10,2);
+ALTER TABLE invoice ALTER COLUMN fxsellprice TYPE numeric(10,2);
+
+ALTER TABLE customer ALTER COLUMN discount TYPE numeric;
+ALTER TABLE customer ALTER COLUMN creditlimit TYPE numeric(10,2);
+
+ALTER TABLE parts ALTER COLUMN listprice TYPE numeric(10,2);
+ALTER TABLE parts ALTER COLUMN sellprice TYPE numeric(10,2);
+ALTER TABLE parts ALTER COLUMN lastcost TYPE numeric(10,2);
+ALTER TABLE parts ALTER COLUMN weight TYPE numeric;
+ALTER TABLE parts ALTER COLUMN onhand TYPE numeric;
+ALTER TABLE parts ALTER COLUMN avgcost TYPE numeric(10,2);
+
+ALTER TABLE assembly ALTER COLUMN qty TYPE numeric;
+
+ALTER TABLE ar ALTER COLUMN amount TYPE numeric(10,2);
+ALTER TABLE ar ALTER COLUMN netamount TYPE numeric(10,2);
+ALTER TABLE ar ALTER COLUMN paid TYPE numeric(10,2);
+
+ALTER TABLE ap ALTER COLUMN amount TYPE numeric(10,2);
+ALTER TABLE ap ALTER COLUMN netamount TYPE numeric(10,2);
+ALTER TABLE ap ALTER COLUMN paid TYPE numeric(10,2);
+
+ALTER TABLE tax ALTER COLUMN rate TYPE numeric;
+
+ALTER TABLE oe ALTER COLUMN amount TYPE numeric(10,2);
+ALTER TABLE oe ALTER COLUMN netamount TYPE numeric(10,2);
+
+ALTER TABLE orderitems ALTER COLUMN qty TYPE numeric;
+ALTER TABLE orderitems ALTER COLUMN sellprice TYPE numeric(10,2);
+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(10,2);
+
+ALTER TABLE partscustomer ALTER COLUMN pricebreak TYPE numeric;
+ALTER TABLE partscustomer ALTER COLUMN sellprice TYPE numeric(10,2);
+
+ALTER TABLE jcitems ALTER COLUMN qty TYPE numeric;
+ALTER TABLE jcitems ALTER COLUMN allocated TYPE numeric;
+ALTER TABLE jcitems ALTER COLUMN sellprice TYPE numeric(10,2);
+ALTER TABLE jcitems ALTER COLUMN fxsellprice TYPE numeric(10,2);
-- The query rewrite rule necessary to notify the email app that a new report
-- needs to be sent to the designated administrator.