Moving Pg-upgrade* into sql/legacy directory

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@420 4979c152-3d1c-0410-bac9-87ea11338e46

1 files changed, 136 insertions, 0 deletions

diff --git a/sql/legacy/Pg-upgrade-2.5.0-2.5.2.sql b/sql/legacy/Pg-upgrade-2.5.0-2.5.2.sql
new file mode 100755
index 00000000..c6c9d641
--- /dev/null
+++ b/sql/legacy/Pg-upgrade-2.5.0-2.5.2.sql
@@ -0,0 +1,136 @@
+--
+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';
+