Accepting LBMoore's upgrade fixes

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

3 files changed, 114 insertions, 50 deletions

diff --git a/CONTRIBUTORS b/CONTRIBUTORS
index 276006cc..6925be20 100644
--- a/CONTRIBUTORS
+++ b/CONTRIBUTORS
@@ -44,7 +44,8 @@ Tony Fraser <tony @ sybaspace.com> provided some database fixes.
 John Hasler <john @ dhh.gt.org> has provided some double-entry information for
 the manual and miscellaneous documentation corrections.
 
-Louis B. Moore has provided the documentation on the command-line API.
+Louis B. Moore has provided the documentation on the command-line API and helped
+with upgrade scripts.
 
 Original Authors of SQL-Ledger:
 ===================================
diff --git a/sl2ls.sh b/sl2ls.sh
new file mode 100644
index 00000000..fcbf031f
--- /dev/null
+++ b/sl2ls.sh
@@ -0,0 +1,63 @@
+#

+#

+#

+# SQL-Ledger Dataset name and Dataset Owner

+#

+SLDB=sqlledger

+SLOWN=SQL-Ledger_Owner

+

+#

+# Ledger-SMB Dataset name and Dataset Owner

+#

+LSDB=lsmbprod

+LSOWN=ledgersmb

+

+

+psql template1 -c "DROP DATABASE ${LSDB};"

+

+pg_dump ${SLDB} > sl2ls.sql

+

+sed -i -e "s/${SLOWN}/${LSOWN}/" sl2ls.sql

+sed -i -e "s/SQL_ASCII/LATIN1/" sl2ls.sql

+

+createdb -O ${LSOWN} ${LSDB}

+

+psql ${LSDB} ${LSOWN} -c "\i /usr/local/ledger-smb/sql/Pg-central.sql"

+

+psql template1 -c "ALTER USER ${LSOWN} WITH superuser;"

+

+psql ${LSDB} ${LSOWN} -c "\i sl2ls.sql"

+

+psql template1 -c "ALTER USER ${LSOWN} WITH nosuperuser;"

+

+cd /usr/local/ledger-smb/sql/legacy/

+

+psql ${LSDB} ${LSOWN} -c "SELECT version FROM defaults;"

+

+psql ${LSDB} ${LSOWN} -c "\i Pg-upgrade-2.6.12-2.6.17.sql"

+

+psql ${LSDB} ${LSOWN} -c "SELECT version FROM defaults;"

+

+psql ${LSDB} ${LSOWN} -c "\i Pg-upgrade-2.6.17-2.6.18.sql"

+

+psql ${LSDB} ${LSOWN} -c "SELECT version FROM defaults;"

+

+psql ${LSDB} ${LSOWN} -c "\i Pg-upgrade-2.6.18-2.6.19.sql"

+

+echo '###############################################################'

+echo

+echo 'Should error with--> ERROR:  column "version" does not exist'

+echo

+echo '###############################################################'

+

+psql ${LSDB} ${LSOWN} -c "SELECT version FROM defaults;"

+

+psql ${LSDB} ${LSOWN} -c "update users_conf set password = md5('apasswrd');"

+

+cd /usr/local/ledger-smb

+

+./import_members.pl users/members

+

+psql ${LSDB} ${LSOWN} -c "SELECT * FROM users;"

+

+exit

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 5bed6ec9..5b18932c 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
@@ -150,70 +150,70 @@ 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);
+--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);
+--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);
+--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;
+--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);
+--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';
+--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';
+--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 $$;