path: root/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql

BEGIN;

ALTER TABLE ap ADD PRIMARY KEY (id);

ALTER TABLE ar ADD PRIMARY KEY (id);

ALTER TABLE assembly ADD PRIMARY KEY (id, parts_id);

ALTER TABLE business ADD PRIMARY KEY (id);

ALTER TABLE customer ADD PRIMARY KEY (id);

ALTER TABLE customertax ADD PRIMARY KEY (customer_id, chart_id);

ALTER TABLE department ADD PRIMARY KEY (id);

ALTER TABLE dpt_trans ADD PRIMARY KEY (trans_id);

ALTER TABLE employee ADD PRIMARY KEY (id);

ALTER TABLE exchangerate ADD PRIMARY KEY (curr, transdate);

ALTER TABLE gifi ADD PRIMARY KEY (accno);

ALTER TABLE gl ADD PRIMARY KEY (id);

ALTER TABLE invoice ADD PRIMARY KEY (id);

ALTER TABLE jcitems ADD PRIMARY KEY (id);

ALTER TABLE language ADD PRIMARY KEY (code);

ALTER TABLE makemodel ADD PRIMARY KEY (parts_id);

ALTER TABLE oe ADD PRIMARY KEY (id);

ALTER TABLE orderitems ADD PRIMARY KEY (id);

ALTER TABLE parts ADD PRIMARY KEY (id);

ALTER TABLE partsgroup ADD PRIMARY KEY (id);

ALTER TABLE partstax ADD PRIMARY KEY (parts_id, chart_id);
ALTER TABLE partstax ADD FOREIGN KEY (chart_id) REFERENCES chart (id);
ALTER TABLE partstax ADD FOREIGN KEY (parts_id) REFERENCES parts (id);

ALTER TABLE pricegroup ADD PRIMARY KEY (id);

ALTER TABLE project ADD PRIMARY KEY (id);

ALTER TABLE recurringemail ADD PRIMARY KEY (id);

ALTER TABLE recurring ADD PRIMARY KEY (id);

ALTER TABLE recurringprint ADD PRIMARY KEY (id);

ALTER TABLE sic ADD PRIMARY KEY (code);

ALTER TABLE status ADD PRIMARY KEY (trans_id);

ALTER TABLE tax ADD PRIMARY KEY (chart_id);
ALTER TABLE tax ADD FOREIGN KEY (chart_id) REFERENCES chart (id);

ALTER TABLE translation ADD PRIMARY KEY (trans_id, language_code);

ALTER TABLE vendor ADD PRIMARY KEY (id);

ALTER TABLE vendortax ADD PRIMARY KEY (vendor_id, chart_id);

ALTER TABLE warehouse ADD PRIMARY KEY (id);

ALTER TABLE yearend ADD PRIMARY KEY (trans_id);

LOCK inventory in EXCLUSIVE mode;
ALTER TABLE inventory ADD COLUMN entry_id bigint;
CREATE SEQUENCE inventory_entry_id_seq;

ALTER TABLE inventory ALTER COLUMN entry_id 
SET DEFAULT nextval('inventory_entry_id_seq');

UPDATE inventory SET entry_id = nextval('inventory_entry_id_seq');
ALTER TABLE inventory ADD PRIMARY key (entry_id);

LOCK partscustomer IN EXCLUSIVE MODE;
ALTER TABLE partscustomer ADD COLUMN entry_id int;
CREATE SEQUENCE partscustomer_entry_id_seq;

ALTER TABLE partscustomer ALTER COLUMN entry_id 
SET DEFAULT nextval('partscustomer_entry_id_seq');

UPDATE partscustomer SET entry_id = nextval('partscustomer_entry_id_seq');
ALTER TABLE partscustomer ADD PRIMARY KEY (entry_id);

LOCK partsvendor IN EXCLUSIVE MODE;
ALTER TABLE partsvendor ADD COLUMN entry_id int;
CREATE SEQUENCE partsvendor_entry_id_seq;

ALTER TABLE partsvendor ALTER COLUMN entry_id 
SET DEFAULT nextval('partsvendor_entry_id_seq');

UPDATE partsvendor SET entry_id = nextval('partsvendor_entry_id_seq');
ALTER TABLE partsvendor ADD PRIMARY KEY (entry_id);

LOCK audittrail IN EXCLUSIVE MODE;
ALTER TABLE audittrail ADD COLUMN entry_id int;
CREATE SEQUENCE audittrail_entry_id_seq ;

ALTER TABLE audittrail ALTER COLUMN entry_id 
SET DEFAULT nextval('audittrail_entry_id_seq');

UPDATE audittrail SET entry_id = nextval('audittrail_entry_id_seq');
ALTER TABLE audittrail ADD PRIMARY KEY (entry_id);

LOCK shipto IN EXCLUSIVE MODE;
ALTER TABLE shipto ADD COLUMN entry_id int;
CREATE SEQUENCE shipto_entry_id_seq ;

ALTER TABLE shipto ALTER COLUMN entry_id 
SET DEFAULT nextval('shipto_entry_id_seq');

UPDATE shipto SET entry_id = nextval('shipto_entry_id_seq');
ALTER TABLE shipto ADD PRIMARY KEY (entry_id);

CREATE TABLE taxmodule (
  taxmodule_id serial PRIMARY KEY,
  taxmodulename text NOT NULL
);

INSERT INTO taxmodule (
  taxmodule_id, taxmodulename
  ) VALUES (
  1, 'Simple'
);

CREATE TABLE taxcategory (
  taxcategory_id serial PRIMARY KEY,
  taxcategoryname text NOT NULL,
  taxmodule_id int NOT NULL REFERENCES taxmodule (taxmodule_id)
);

ALTER TABLE partstax ADD COLUMN taxcategory_id int REFERENCES taxcategory (taxcategory_id);

LOCK tax IN EXCLUSIVE MODE;
ALTER TABLE tax ADD COLUMN pass int DEFAULT 0;
UPDATE tax SET pass = 0;
ALTER TABLE tax ALTER COLUMN pass SET NOT NULL;

ALTER TABLE tax ADD COLUMN taxmodule_id int REFERENCES taxmodule DEFAULT 1;
UPDATE tax SET taxmodule_id = 1;
ALTER TABLE tax ALTER COLUMN taxmodule_id SET NOT NULL;

-- Fixed session table and add users table --
CREATE TABLE users (id serial UNIQUE, username varchar(30) PRIMARY KEY);
COMMENT ON TABLE users IS 'username is the primary key because we don\'t want duplicate users';
CREATE TABLE users_conf(id integer primary key references users(id) deferrable initially deferred,
                        acs text,
                        address text,
                        businessnumber text,
                        company text,
                        countrycode text,
                        currency text,
                        dateformat text,
                        dbconnect text,
                        dbdriver text default 'Pg',
                        dbhost text default 'localhost',
                        dbname text,
                        dboptions text,
                        dbpasswd text,
                        dbport text,
                        dbuser text,
                        email text,
                        fax text,
                        menuwidth text,
                        name text,
                        numberformat text,
                        password varchar(32) check(length(password) = 32),
                        print text,
                        printer text,
                        role text,
                        sid text,
                        signature text,
                        stylesheet text,
                        tel text,
                        templates text,
            crypted_password text,
                        timeout numeric,
                        vclimit numeric);
COMMENT ON TABLE users_conf IS 'This is a completely dumb table that is a place holder to get usersconf into the database. Next major release will have a much more sane implementation';
COMMENT ON COLUMN users_conf.id IS 'Yes primary key with a FOREIGN KEY to users(id) is correct';
COMMENT ON COLUMN users_conf.password IS 'This means we have to get rid of the current password stuff and move to presumably md5()';

LOCK session in EXCLUSIVE MODE;
DELETE FROM session;
ALTER TABLE session ADD CONSTRAINT session_token_check check (length(token::text) = 32);
ALTER TABLE session ADD column user_id integer not null references users(id);

-- comment this out when user db is working:
ALTER TABLE session ALTER COLUMN user_id DROP NOT NULL;

-- Admin user --
BEGIN;
INSERT INTO users(username) VALUES ('admin');
INSERT INTO users_conf(id,password) VALUES (currval('users_id_seq'),NULL);

-- Functions

CREATE FUNCTION create_user(text) RETURNS bigint AS $$
   INSERT INTO users(username) VALUES ('$1');
   SELECT currval('users_id_seq');
   $$ LANGUAGE 'SQL';

COMMENT ON FUNCTION create_user(text) IS $$ Function to create user Returns users.id if successful, else it is an error. $$;

CREATE FUNCTION update_user(int4,text) RETURNS int4 AS $$
   UPDATE users SET username = '$2' WHERE id = $1;
   SELECT 1;
   $$ LANGUAGE 'SQL';

COMMENT ON FUNCTION update_user(int4,text) IS $$ Takes int4 which is users.id and text which is username. Will update username based on id. Username is unique $$;

ALTER TABLE defaults RENAME TO old_defaults;

CREATE TABLE defaults (
    setting_key TEXT PRIMARY KEY,
    value TEXT
);

COMMENT ON TABLE defaults IS $$This table replaces the old one column per value system with a simple key => value table$$;


INSERT INTO defaults (setting_key, value) 
SELECT 'inventory_accno_id', inventory_accno_id::text FROM old_defaults
UNION
SELECT 'income_accno_id', income_accno_id::text FROM old_defaults
UNION
SELECT 'expense_accno_id', expense_accno_id::text FROM old_defaults
UNION
SELECT 'fxloss_accno_id', fxloss_accno_id::text FROM old_defaults
UNION
SELECT 'fxgain_accno_id', fxgain_accno_id::text FROM old_defaults
UNION
SELECT 'sinumber', sinumber::text FROM old_defaults
UNION
SELECT 'sonumber', sonumber::text FROM old_defaults
UNION
SELECT 'yearend', yearend::text FROM old_defaults
UNION
SELECT 'weightunit', weightunit::text FROM old_defaults
UNION
SELECT 'businessnumber', businessnumber::text FROM old_defaults
UNION
SELECT 'version', '1.2.0'::text
UNION
SELECT 'curr', curr::text FROM old_defaults
UNION
SELECT 'closedto', closedto::text FROM old_defaults
UNION
SELECT 'revtrans', (CASE WHEN revtrans IS NULL THEN NULL
            WHEN revtrans THEN '1' 
            ELSE '0' END) FROM old_defaults
UNION
SELECT 'ponumber', ponumber::text FROM old_defaults
UNION
SELECT 'sqnumber', sqnumber::text FROM old_defaults
UNION
SELECT 'rfqnumber', rfqnumber::text FROM old_defaults
UNION
SELECT 'audittrail', (CASE WHEN audittrail IS NULL THEN NULL
            WHEN audittrail THEN '1' 
            ELSE '0' END) FROM old_defaults
UNION
SELECT 'vinumber', vinumber::text FROM old_defaults
UNION
SELECT 'employeenumber', employeenumber::text FROM old_defaults
UNION
SELECT 'partnumber', partnumber::text FROM old_defaults
UNION
SELECT 'customernumber', customernumber::text FROM old_defaults
UNION
SELECT 'vendornumber', vendornumber::text FROM old_defaults
UNION
SELECT 'glnumber', glnumber::text FROM old_defaults
UNION
SELECT 'projectnumber', projectnumber::text FROM old_defaults
UNION
SELECT 'appname', 'LedgerSMB'::text;

DROP TABLE old_defaults;

CREATE OR REPLACE FUNCTION del_exchangerate() RETURNS TRIGGER AS '

declare
  t_transdate date;
  t_curr char(3);
  t_id int;
  d_curr text;

begin

  select into d_curr substr(value,1,3) from defaults where setting_key = ''curr'';
  
  if TG_RELNAME = ''ar'' then
    select into t_curr, t_transdate curr, transdate from ar where id = old.id;
  end if;
  if TG_RELNAME = ''ap'' then
    select into t_curr, t_transdate curr, transdate from ap where id = old.id;
  end if;
  if TG_RELNAME = ''oe'' then
    select into t_curr, t_transdate curr, transdate from oe where id = old.id;
  end if;

  if d_curr != t_curr then

    select into t_id a.id from acc_trans ac
    join ar a on (a.id = ac.trans_id)
    where a.curr = t_curr
    and ac.transdate = t_transdate

    except select a.id from ar a where a.id = old.id
    
    union
    
    select a.id from acc_trans ac
    join ap a on (a.id = ac.trans_id)
    where a.curr = t_curr
    and ac.transdate = t_transdate
    
    except select a.id from ap a where a.id = old.id
    
    union
    
    select o.id from oe o
    where o.curr = t_curr
    and o.transdate = t_transdate
    
    except select o.id from oe o where o.id = old.id;

    if not found then
      delete from exchangerate where curr = t_curr and transdate = t_transdate;
    end if;
  end if;
return old;

end;
' language 'plpgsql';

CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR)
RETURNS BOOL AS
'BEGIN
        EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog
                WHERE extends = '''''' || table_name || '''''' '';
        IF NOT FOUND THEN
                BEGIN
                        INSERT INTO custom_table_catalog (extends)
                                VALUES (table_name);
                        EXECUTE ''CREATE TABLE custom_''||table_name ||
                                '' (row_id INT PRIMARY KEY)'';
                EXCEPTION WHEN duplicate_table THEN
                        -- do nothing
                END;
        END IF;
        EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id)
        VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog
                WHERE extends = ''''''|| table_name || ''''''))'';
        EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN ''
                || new_field_name || '' '' || field_datatype;
        RETURN TRUE;
END;
' LANGUAGE PLPGSQL;

COMMIT;