sql/update10x-200.sql

   1 -- Updatescript von Version 1.02/1.03 auf 2.00
   2 -- H.Lindemann Lx-System GbR
   3 -- info@lx-system.de
   4 -- Version: 1.0.0
   5 BEGIN;
   6 LOCK TABLE gl IN ACCESS EXCLUSIVE MODE;
   7 LOCK TABLE ar IN ACCESS EXCLUSIVE MODE;
   8 LOCK TABLE ap IN ACCESS EXCLUSIVE MODE;
   9
  10 CREATE SEQUENCE glid start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;
  11
  12 CREATE FUNCTION _glid()
  13 RETURNS text
  14 AS 'DECLARE lv record;
  15 BEGIN
  16 SELECT INTO lv last_value from id;
  17 execute ''SELECT pg_catalog.setval(''''glid'''', '' || lv.last_value || '' , true)'';
  18 return cast(lv.last_value as text);
  19 END;'
  20 LANGUAGE 'plpgsql';
  21 select _glid();
  22 drop function _glid();
  23
  24 --execute ''CREATE SEQUENCE glid start '' || lv.last_value || ''increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1'';
  25 --update glid set last_value = (select last_value from id);
  26
  27 \echo acc_trans
  28 ALTER TABLE acc_trans ADD COLUMN gldate date;
  29 ALTER TABLE acc_trans ALTER COLUMN gldate SET DEFAULT date('now'::text);
  30
  31 \echo gl
  32 ALTER TABLE gl ALTER COLUMN id SET DEFAULT nextval('glid'::text);
  33 ALTER TABLE gl ADD COLUMN gldate date;
  34 ALTER TABLE gl ALTER COLUMN gldate SET DEFAULT date('now'::text);
  35 ALTER TABLE gl ADD COLUMN taxincluded boolean;
  36
  37 \echo ar
  38 ALTER TABLE ar ALTER COLUMN id SET DEFAULT nextval('glid'::text);
  39 ALTER TABLE ar ADD COLUMN gldate date;
  40 ALTER TABLE ar ALTER COLUMN gldate SET DEFAULT date('now'::text);
  41
  42 \echo ap
  43 ALTER TABLE ap ALTER COLUMN id SET DEFAULT nextval('glid'::text);
  44 ALTER TABLE ap ADD COLUMN gldate date;
  45 ALTER TABLE ap ALTER COLUMN gldate SET DEFAULT date('now'::text);
  46
  47 \echo parts
  48 ALTER TABLE parts ADD COLUMN shop boolean;
  49 ALTER TABLE parts ALTER COLUMN shop SET DEFAULT false;
  50
  51 update defaults set version = '2.0.0';
  52
  53
  54 END;
  55
  56 \echo idexes
  57 CREATE INDEX acc_trans_trans_id_key ON acc_trans USING btree (trans_id);
  58
  59 CREATE INDEX acc_trans_chart_id_key ON acc_trans USING btree (chart_id);
  60
  61 CREATE INDEX acc_trans_transdate_key ON acc_trans USING btree (transdate);
  62
  63 CREATE INDEX acc_trans_source_key ON acc_trans USING btree (lower(source));
  64
  65 CREATE INDEX ap_id_key ON ap USING btree (id);
  66
  67 CREATE INDEX ap_transdate_key ON ap USING btree (transdate);
  68
  69 CREATE INDEX ap_invnumber_key ON ap USING btree (lower(invnumber));
  70
  71 CREATE INDEX ap_ordnumber_key ON ap USING btree (lower(ordnumber));
  72
  73 CREATE INDEX ap_vendor_id_key ON ap USING btree (vendor_id);
  74
  75 CREATE INDEX ap_employee_id_key ON ap USING btree (employee_id);
  76
  77 CREATE INDEX ar_id_key ON ar USING btree (id);
  78
  79 CREATE INDEX ar_transdate_key ON ar USING btree (transdate);
  80
  81 CREATE INDEX ar_invnumber_key ON ar USING btree (lower(invnumber));
  82
  83 CREATE INDEX ar_ordnumber_key ON ar USING btree (lower(ordnumber));
  84
  85 CREATE INDEX ar_customer_id_key ON ar USING btree (customer_id);
  86
  87 CREATE INDEX ar_employee_id_key ON ar USING btree (employee_id);
  88
  89 CREATE INDEX assembly_id_key ON assembly USING btree (id);
  90
  91 CREATE INDEX chart_id_key ON chart USING btree (id);
  92
  93 CREATE UNIQUE INDEX chart_accno_key ON chart USING btree (accno);
  94
  95 CREATE INDEX chart_category_key ON chart USING btree (category);
  96
  97 CREATE INDEX chart_link_key ON chart USING btree (link);
  98
  99 CREATE INDEX chart_gifi_accno_key ON chart USING btree (gifi_accno);
 100
 101 CREATE INDEX customer_id_key ON customer USING btree (id);
 102
 103 CREATE INDEX customer_customer_id_key ON customertax USING btree (customer_id);
 104
 105 CREATE INDEX customer_customernumber_key ON customer USING btree (customernumber);
 106
 107 CREATE INDEX customer_name_key ON customer USING btree (name);
 108
 109 CREATE INDEX customer_contact_key ON customer USING btree (contact);
 110
 111 CREATE INDEX employee_id_key ON employee USING btree (id);
 112
 113 CREATE UNIQUE INDEX employee_login_key ON employee USING btree (login);
 114
 115 CREATE INDEX employee_name_key ON employee USING btree (name);
 116
 117 CREATE INDEX exchangerate_ct_key ON exchangerate USING btree (curr, transdate);
 118
 119 CREATE UNIQUE INDEX gifi_accno_key ON gifi USING btree (accno);
 120
 121 CREATE INDEX gl_id_key ON gl USING btree (id);
 122
 123 CREATE INDEX gl_transdate_key ON gl USING btree (transdate);
 124
 125 CREATE INDEX gl_reference_key ON gl USING btree (lower(reference));
 126
 127 CREATE INDEX gl_description_key ON gl USING btree (lower(description));
 128
 129 CREATE INDEX gl_employee_id_key ON gl USING btree (employee_id);
 130
 131 CREATE INDEX invoice_id_key ON invoice USING btree (id);
 132
 133 CREATE INDEX invoice_trans_id_key ON invoice USING btree (trans_id);
 134
 135 CREATE INDEX oe_id_key ON oe USING btree (id);
 136
 137 CREATE INDEX oe_transdate_key ON oe USING btree (transdate);
 138
 139 CREATE INDEX oe_ordnumber_key ON oe USING btree (lower(ordnumber));
 140
 141 CREATE INDEX oe_employee_id_key ON oe USING btree (employee_id);
 142
 143 CREATE INDEX orderitems_trans_id_key ON orderitems USING btree (trans_id);
 144
 145 CREATE INDEX parts_id_key ON parts USING btree (id);
 146
 147 CREATE INDEX parts_partnumber_key ON parts USING btree (lower(partnumber));
 148
 149 CREATE INDEX parts_description_key ON parts USING btree (lower(description));
 150
 151 CREATE INDEX partstax_parts_id_key ON partstax USING btree (parts_id);
 152
 153 CREATE INDEX vendor_id_key ON vendor USING btree (id);
 154
 155 CREATE INDEX vendor_name_key ON vendor USING btree (name);
 156
 157 CREATE INDEX vendor_vendornumber_key ON vendor USING btree (vendornumber);
 158
 159 CREATE INDEX vendor_contact_key ON vendor USING btree (contact);
 160
 161 CREATE INDEX vendortax_vendor_id_key ON vendortax USING btree (vendor_id);
 162
 163 CREATE INDEX shipto_trans_id_key ON shipto USING btree (trans_id);
 164
 165 CREATE INDEX project_id_key ON project USING btree (id);
 166
 167 CREATE INDEX ar_quonumber_key ON ar USING btree (lower(quonumber));
 168
 169 CREATE INDEX ap_quonumber_key ON ap USING btree (lower(quonumber));
 170
 171 CREATE INDEX makemodel_parts_id_key ON makemodel USING btree (parts_id);
 172
 173 CREATE INDEX makemodel_make_key ON makemodel USING btree (lower(make));
 174
 175 CREATE INDEX makemodel_model_key ON makemodel USING btree (lower(model));
 176
 177 CREATE INDEX status_trans_id_key ON status USING btree (trans_id);
 178
 179 CREATE INDEX department_id_key ON department USING btree (id);
 180
 181 CREATE INDEX orderitems_id_key ON orderitems USING btree (id);
 182
 183 CREATE INDEX contact_name_key ON contacts USING btree (cp_name);
 184
 185 \echo functions
 186 SET check_function_bodies = false;
 187
 188 CREATE FUNCTION del_yearend() RETURNS "trigger"
 189     AS '
 190 begin
 191   delete from yearend where trans_id = old.id;
 192   return NULL;
 193 end;
 194 '
 195 LANGUAGE plpgsql;
 196
 197 CREATE FUNCTION del_department() RETURNS "trigger"
 198     AS '
 199 begin
 200   delete from dpt_trans where trans_id = old.id;
 201   return NULL;
 202 end;
 203 '
 204 LANGUAGE plpgsql;
 205
 206 CREATE FUNCTION del_customer() RETURNS "trigger"
 207     AS '
 208 begin
 209   delete from shipto where trans_id = old.id;
 210   delete from customertax where customer_id = old.id;
 211   return NULL;
 212 end;
 213 '
 214 LANGUAGE plpgsql;
 215
 216 CREATE FUNCTION del_vendor() RETURNS "trigger"
 217     AS '
 218 begin
 219   delete from shipto where trans_id = old.id;
 220   delete from vendortax where vendor_id = old.id;
 221   return NULL;
 222 end;
 223 '
 224 LANGUAGE plpgsql;
 225
 226 CREATE FUNCTION del_exchangerate() RETURNS "trigger"
 227     AS '
 228 declare
 229   t_transdate date;
 230   t_curr char(3);
 231   t_id int;
 232   d_curr text;
 233 begin
 234   select into d_curr substr(curr,1,3) from defaults;
 235   if TG_RELNAME = ''ar'' then
 236     select into t_curr, t_transdate curr, transdate from ar where id = old.id;
 237   end if;
 238   if TG_RELNAME = ''ap'' then
 239     select into t_curr, t_transdate curr, transdate from ap where id = old.id;
 240   end if;
 241   if TG_RELNAME = ''oe'' then
 242     select into t_curr, t_transdate curr, transdate from oe where id = old.id;
 243   end if;
 244   if d_curr != t_curr then
 245     select into t_id a.id from acc_trans ac
 246     join ar a on (a.id = ac.trans_id)
 247     where a.curr = t_curr
 248     and ac.transdate = t_transdate
 249     except select a.id from ar a where a.id = old.id
 250     union
 251     select a.id from acc_trans ac
 252     join ap a on (a.id = ac.trans_id)
 253     where a.curr = t_curr
 254     and ac.transdate = t_transdate
 255     except select a.id from ap a where a.id = old.id
 256     union
 257     select o.id from oe o
 258     where o.curr = t_curr
 259     and o.transdate = t_transdate
 260     except select o.id from oe o where o.id = old.id;
 261     if not found then
 262       delete from exchangerate where curr = t_curr and transdate = t_transdate;
 263     end if;
 264   end if;
 265 return old;
 266 end;
 267 '
 268 LANGUAGE plpgsql;
 269
 270 CREATE FUNCTION check_inventory() RETURNS "trigger"
 271     AS '
 272 declare
 273   itemid int;
 274   row_data inventory%rowtype;
 275 begin
 276   if not old.quotation then
 277     for row_data in select * from inventory where oe_id = old.id loop
 278       select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
 279       if itemid is null then
 280         delete from inventory where oe_id = old.id and orderitems_id = row_data.orderitems_id;
 281       end if;
 282     end loop;
 283   end if;
 284   return old;
 285 end;
 286 '
 287 LANGUAGE plpgsql;
 288
 289 CREATE FUNCTION check_department() RETURNS "trigger"
 290     AS '
 291 declare
 292   dpt_id int;
 293 begin
 294   if new.department_id = 0 then
 295     delete from dpt_trans where trans_id = new.id;
 296     return NULL;
 297   end if;
 298   select into dpt_id trans_id from dpt_trans where trans_id = new.id;
 299   if dpt_id > 0 then
 300     update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
 301   else
 302     insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
 303   end if;
 304 return NULL;
 305 end;
 306 '
 307 LANGUAGE plpgsql;
 308
 309 \echo trigger
 310 CREATE TRIGGER del_yearend
 311     AFTER DELETE ON gl
 312     FOR EACH ROW
 313     EXECUTE PROCEDURE del_yearend();
 314
 315 CREATE TRIGGER del_department
 316     AFTER DELETE ON ar
 317     FOR EACH ROW
 318     EXECUTE PROCEDURE del_department();
 319
 320 CREATE TRIGGER del_department
 321     AFTER DELETE ON ap
 322     FOR EACH ROW
 323     EXECUTE PROCEDURE del_department();
 324
 325 CREATE TRIGGER del_department
 326     AFTER DELETE ON gl
 327     FOR EACH ROW
 328     EXECUTE PROCEDURE del_department();
 329
 330 CREATE TRIGGER del_department
 331     AFTER DELETE ON oe
 332     FOR EACH ROW
 333     EXECUTE PROCEDURE del_department();
 334
 335 CREATE TRIGGER del_customer
 336     AFTER DELETE ON customer
 337     FOR EACH ROW
 338     EXECUTE PROCEDURE del_customer();
 339
 340 CREATE TRIGGER del_vendor
 341     AFTER DELETE ON vendor
 342     FOR EACH ROW
 343     EXECUTE PROCEDURE del_vendor();
 344
 345 CREATE TRIGGER del_exchangerate
 346     BEFORE DELETE ON ar
 347     FOR EACH ROW
 348     EXECUTE PROCEDURE del_exchangerate();
 349
 350 CREATE TRIGGER del_exchangerate
 351     BEFORE DELETE ON ap
 352     FOR EACH ROW
 353     EXECUTE PROCEDURE del_exchangerate();
 354
 355 CREATE TRIGGER del_exchangerate
 356     BEFORE DELETE ON oe
 357     FOR EACH ROW
 358     EXECUTE PROCEDURE del_exchangerate();
 359
 360 CREATE TRIGGER check_inventory
 361     AFTER UPDATE ON oe
 362     FOR EACH ROW
 363     EXECUTE PROCEDURE check_inventory();
 364
 365 CREATE TRIGGER check_department
 366     AFTER INSERT OR UPDATE ON ar
 367     FOR EACH ROW
 368     EXECUTE PROCEDURE check_department();
 369
 370 CREATE TRIGGER check_department
 371     AFTER INSERT OR UPDATE ON ap
 372     FOR EACH ROW
 373     EXECUTE PROCEDURE check_department();
 374
 375 CREATE TRIGGER check_department
 376     AFTER INSERT OR UPDATE ON gl
 377     FOR EACH ROW
 378     EXECUTE PROCEDURE check_department();
 379
 380 CREATE TRIGGER check_department
 381     AFTER INSERT OR UPDATE ON oe
 382     FOR EACH ROW
 383     EXECUTE PROCEDURE check_department();