From 05cf6c2ce357a792927c19e2b1cc8a8f45c375e9 Mon Sep 17 00:00:00 2001 From: einhverfr Date: Tue, 6 Feb 2007 05:21:08 +0000 Subject: 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 --- CONTRIBUTORS | 3 +- sl2ls.sh | 63 +++++++++++++++++++++ sql/legacy/Pg-upgrade-2.6.18-2.6.19.sql | 98 ++++++++++++++++----------------- 3 files changed, 114 insertions(+), 50 deletions(-) create mode 100644 sl2ls.sh diff --git a/CONTRIBUTORS b/CONTRIBUTORS index 276006cc..6925be20 100644 --- a/CONTRIBUTORS +++ b/CONTRIBUTORS @@ -44,7 +44,8 @@ Tony Fraser provided some database fixes. John Hasler 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 $$; -- cgit v1.2.3