[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

SF.net SVN: ledger-smb: [62] trunk/sql



Revision: 62
          http://svn.sourceforge.net/ledger-smb/?rev=62&view=rev
Author:   linuxpoet
Date:     2006-09-10 17:13:32 -0700 (Sun, 10 Sep 2006)

Log Message:
-----------
Rather large amount of changes to Pg-tables.sql and the 2.6.17-2.6.18 upgrade script. Changed all floats to numeric. If the float was a percent, it is just numeric. If the float was a cost, it is (10,2)

Modified Paths:
--------------
    trunk/sql/Pg-tables.sql
    trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql

Modified: trunk/sql/Pg-tables.sql
===================================================================
--- trunk/sql/Pg-tables.sql	2006-09-10 22:50:19 UTC (rev 61)
+++ trunk/sql/Pg-tables.sql	2006-09-11 00:13:32 UTC (rev 62)
@@ -75,7 +75,7 @@
 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 @@
   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 @@
   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 @@
   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 @@
   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 @@
   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 @@
   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 tax (
   chart_id int,
-  rate float,
+  rate numeric,
   taxnumber text,
   validto date
 );
@@ -258,8 +258,8 @@
   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 @@
   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 exchangerate (
   curr char(3),
   transdate date,
-  buy float8,
-  sell float8
+  buy numeric,
+  sell numeric
 );
 --
 create table employee (
@@ -364,8 +364,8 @@
   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 @@
   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 business (
   id int default nextval('id'),
   description text,
-  discount float4
+  discount numeric
 );
 --
 -- SIC
@@ -437,7 +437,7 @@
   parts_id int,
   trans_id int,
   orderitems_id int,
-  qty float4,
+  qty numeric,
   shippingdate date,
   employee_id int
 ) WITH OIDS;
@@ -452,7 +452,7 @@
   parts_id int,
   partnumber text,
   leadtime int2,
-  lastcost float,
+  lastcost numeric(10,2),
   curr char(3)
 );
 --
@@ -465,8 +465,8 @@
   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 @@
   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,

Modified: trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql
===================================================================
--- trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql	2006-09-10 22:50:19 UTC (rev 61)
+++ trunk/sql/Pg-upgrade-2.6.17-2.6.18.sql	2006-09-11 00:13:32 UTC (rev 62)
@@ -11,7 +11,69 @@
 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.
 -- By Chris Travers


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.