kivitendo/sql/Pg-upgrade/Pg-upgrade-2.2.0.3-2.2.0.4.sql @ 30fb3005
| 54e4131e | Moritz Bunkus | CREATE TABLE units (
|
|
name varchar(20) NOT NULL,
|
|||
base_unit varchar(20),
|
|||
factor bigint,
|
|||
PRIMARY KEY (name),
|
|||
FOREIGN KEY (base_unit) REFERENCES units (name)
|
|||
);
|
|||
INSERT INTO units (name, base_unit, factor) VALUES ('mg', NULL, NULL);
|
|||
INSERT INTO units (name, base_unit, factor) VALUES ('g', 'mg', 1000);
|
|||
INSERT INTO units (name, base_unit, factor) VALUES ('kg', 'g', 1000);
|
|||
INSERT INTO units (name, base_unit, factor) VALUES ('t', 'kg', 1000);
|
|||
INSERT INTO units (name, base_unit, factor) VALUES ('ml', NULL, NULL);
|
|||
INSERT INTO units (name, base_unit, factor) VALUES ('L', 'ml', 1000);
|
|||
INSERT INTO units (name, base_unit, factor) VALUES ('Stck', NULL, NULL);
|
|||
ALTER TABLE units ADD COLUMN active boolean;
|
|||
UPDATE units SET active = 't';
|
|||
ALTER TABLE units ALTER COLUMN active SET DEFAULT 't';
|
|||
ALTER TABLE units ALTER COLUMN active SET NOT NULL;
|
|||
ALTER TABLE units ADD COLUMN tmp numeric(20, 5);
|
|||
UPDATE units SET tmp = factor;
|
|||
ALTER TABLE units DROP COLUMN factor;
|
|||
ALTER TABLE units RENAME tmp TO factor;
|
|||
ALTER TABLE units ADD COLUMN type varchar(20);
|
|||
UPDATE units SET type = 'dimension';
|
|||
ALTER TABLE units ALTER COLUMN type SET NOT NULL;
|
|||
-- Einheitennamen duerfen 20 Zeichen lang sein.
|
|||
ALTER TABLE parts ADD COLUMN tmp varchar(20);
|
|||
UPDATE parts SET tmp = unit;
|
|||
ALTER TABLE parts DROP COLUMN unit;
|
|||
ALTER TABLE parts RENAME tmp TO unit;
|
|||
ALTER TABLE invoice ADD COLUMN tmp varchar(20);
|
|||
UPDATE invoice SET tmp = unit;
|
|||
ALTER TABLE invoice DROP COLUMN unit;
|
|||
ALTER TABLE invoice RENAME tmp TO unit;
|
|||
ALTER TABLE orderitems ADD COLUMN tmp varchar(20);
|
|||
UPDATE orderitems SET tmp = unit;
|
|||
ALTER TABLE orderitems DROP COLUMN unit;
|
|||
ALTER TABLE orderitems RENAME tmp TO unit;
|
|||
-- Spalte "active" wird nicht mehr benoetigt, weil Einheiten nicht mehr deaktiviert
|
|||
-- werden koennen.
|
|||
ALTER TABLE units DROP COLUMN active;
|