CAcert Code Development list.

[Wytze van der Raay <wytze AT cacert.org>]

Hi Michael,

On 14.01.2014 23:31, Michael Tänzer wrote:
> We have a fix for https://bugs.cacert.org/view.php?id=1135
> "Extend database table AdminLog et al"
> 
> The fix was reviewed by Benny Baumann (BenBE) and me (NEOatNHNG) and
> tested by testing the other dependent bugs.
> 
> Diff is attached.
> 
> Please execute the database migration scripts in numerical order and
> before applying the other patches.
> 
> New files:
> /scripts/db_migrations/version3.sh
> /scripts/db_migrations/version4.sh

The fix has been installed on the production server on January 15, 2014,
and the two new scripts have been executed to migrate the production database
to version 4. See also the attached log message.

Regards,
-- wytze

--- Begin Message ---

• From: Wytze van der Raay <wytze AT cacert.org>
• To: cacert-systemlog AT lists.cacert.org
• Subject: Fwd: cvs.cacert.org checkin notification
• Date: Wed, 15 Jan 2014 15:56:17 +0100
• Organization: CAcert

Fix for https://bugs.cacert.org/view.php?id=1135
"Extend database table AdminLog et al"

The two new scripts have been executed to migrate
the production database to version 4.

In conjunction with the attached CVS changes a new tarball has been
made available incorporating all updates. The new tarball is available
through http://www.cacert.org/src-lic.php

-- end

--- Begin Message ---

• From: "root" <root AT cvs.cacert.org>
• To: critical-admin AT cacert.org
• Subject: cvs.cacert.org checkin notification
• Date: Wed, 15 Jan 2014 15:54:01 +0100 (CET)

uid=0(root) gid=0(root) groups=0(root)
version3.sh NONE 1.1 version4.sh NONE 1.1
Wed Jan 15 15:54:01 CET 2014
Update of /var/lib/cvs/cacert/scripts/db_migrations
In directory webdb:/home/cacert/www/scripts/db_migrations

Added Files:
version3.sh version4.sh
Log Message:
Fix for https://bugs.cacert.org/view.php?id=1135
"Extend database table AdminLog et al"

===================================================================
RCS file: /var/lib/cvs/cacert/scripts/db_migrations/version4.sh,v -->
standard output
revision 1.1
#!/bin/sh
# LibreSSL - CAcert web application
# Copyright (C) 2004-2011 CAcert Inc.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301
USA



# script to do database migrations

# This particular version migrates from the preversioned state to version 1
# If you want to reuse it for further migrations you probably should pay
special
# attention because you have to adjust it a bit

set -e # script fails if any command fails

STDIN=0
STDOUT=1
STDERR=2

if [ "$1" = "--help" ]; then
cat >&$STDERR <<- USAGE
Usage: $0 [MYSQL_OPTIONS]
You have to specify all options needed by "mysql" as if you
had started
the MySQL command line client directly (including the name of
the
database to operate on). The MySQL user used has to have
enough
privileges to do all necessary operations (among others
CREATE, ALTER,
DROP, UPDATE, INSERT, DELETE).
You might need to enter the mysql password multiple times if
you
specify the -p option.
USAGE
exit 1
fi

mysql_opt=" --batch --skip-column-names $@"

schema_version=$( mysql $mysql_opt <<- 'SQL'

SELECT MAX(`version`) FROM `schema_version`;
SQL
)
if [ $schema_version != 3 ]; then
cat >&$STDERR <<- ERROR
Error: database schema is not in the right version to do the
migration!
Expected version: 3
ERROR
exit 2
fi

mysql $mysql_opt <<- 'SQL'

-- dump table AdminLog
SELECT *
INTO OUTFILE "adminlog_table_backup_1135"
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM `adminlog`;
SQL


echo "Dump table create in adminlog_table_backup_1135"


mysql $mysql_opt <<- 'SQL'
-- update table admin log

UPDATE `adminlog` SET `type` = 'old name or dob change',
`information` = 'see adminlog_table_backup_1135';

-- alter table admin log

ALTER TABLE `adminlog`
DROP `old-lname`,
DROP `old-dob`,
DROP `new-lname`,
DROP `new-dob`;


-- Update schema version number
INSERT INTO `schema_version`
(`version`, `when`) VALUES
('4' , NOW() );
SQL


echo "Database successfully migrated to version 4"
exit 0

===================================================================
RCS file: /var/lib/cvs/cacert/scripts/db_migrations/version3.sh,v -->
standard output
revision 1.1
#!/bin/sh
# LibreSSL - CAcert web application
# Copyright (C) 2004-2011 CAcert Inc.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301
USA



# script to do database migrations

# This particular version migrates from the preversioned state to version 1
# If you want to reuse it for further migrations you probably should pay
special
# attention because you have to adjust it a bit

set -e # script fails if any command fails

STDIN=0
STDOUT=1
STDERR=2

if [ "$1" = "--help" ]; then
cat >&$STDERR <<- USAGE
Usage: $0 [MYSQL_OPTIONS]
You have to specify all options needed by "mysql" as if you
had started
the MySQL command line client directly (including the name of
the
database to operate on). The MySQL user used has to have
enough
privileges to do all necessary operations (among others
CREATE, ALTER,
DROP, UPDATE, INSERT, DELETE).
You might need to enter the mysql password multiple times if
you
specify the -p option.
USAGE
exit 1
fi

mysql_opt=" --batch --skip-column-names $@"

schema_version=$( mysql $mysql_opt <<- 'SQL'

SELECT MAX(`version`) FROM `schema_version`;
SQL
)
if [ $schema_version != 2 ]; then
cat >&$STDERR <<- ERROR
Error: database schema is not in the right version to do the
migration!
Expected version: 2
ERROR
exit 2
fi

mysql $mysql_opt <<- 'SQL'


-- alter table Admin log

ALTER TABLE `adminlog` ADD `type` VARCHAR( 50 ) NOT NULL ,
ADD `information` VARCHAR( 50 ) NOT NULL;

-- create new table OrgAdminLog

CREATE TABLE IF NOT EXISTS `orgadminlog` (
`when` datetime NOT NULL,
`oid` int(11) NOT NULL,
`adminid` int(11) NOT NULL,
`type` varchar(50) NOT NULL,
`information` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- alter table OrgDomainCerts
ALTER TABLE `orgdomaincerts` ADD orgadminid int(11) NULL,
ADD revokeorgadminid int(11) NULL;

-- alter table OrgEmailCerts
ALTER TABLE `orgemailcerts` ADD orgadminid int(11) NULL,
ADD revokeorgadminid int(11) NULL;



-- Update schema version number
INSERT INTO `schema_version`
(`version`, `when`) VALUES
('3' , NOW() );
SQL


echo "Database successfully migrated to version 3"
exit 0


--- End Message ---

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature

--- End Message ---

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature