Adding date format conversion to upgrade script.

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

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'