diff options
Diffstat (limited to 'sql/legacy')
| -rw-r--r-- | sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql | 73 |
1 files changed, 4 insertions, 69 deletions
diff --git a/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql b/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql index 32e6dc5d..020738a1 100644 --- a/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql +++ b/sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql @@ -34,6 +34,9 @@ ALTER TABLE makemodel ADD PRIMARY KEY (parts_id); ALTER TABLE oe ADD PRIMARY KEY (id); +SELECT setval('orderitemsid', 1); +UPDATE orderitems SET id = nextval('orderitemsid'); + ALTER TABLE orderitems ADD PRIMARY KEY (id); ALTER TABLE parts ADD PRIMARY KEY (id); @@ -153,74 +156,6 @@ 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 -- ---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 ( @@ -256,7 +191,7 @@ SELECT 'version', '1.2.0'::text UNION SELECT 'curr', curr::text FROM old_defaults UNION -SELECT 'closedto', closedto::text FROM old_defaults +SELECT 'closedto', to_char(closedto, 'YYYY-MM-DD') FROM old_defaults UNION SELECT 'revtrans', (CASE WHEN revtrans IS NULL THEN NULL WHEN revtrans THEN '1' |