. '''To [[Software|Software]]''' - '''To [[OverviewProjectsBoard|Overview Projects Board]]''' - '''To [[SystemAdministration/Systems/Webdb|Webdb Sys Administration]]''' - '''To [[Software/Webdb|Webdb Handbook]]'''

----

= Software: Webdb Documentation - Maintenance - Database Upgrades =

 * Database upgrades follows a recommendation by Software developers or the Software-Assessment team
 * Database upgrades needs to be documented thru the bugs system with a bug number
 * Database upgrades will be handled thru a script, prepared by the Software-Assessment team and executed by the Crtiical team


== Pre-requisites ==

 1. A new bug number to create under bugs.cacert.org
  * if the proposed table upgrade relates to an open bug, a new bug # should be opened to handle all database upgrades individualy with their own bug# and later reference to this database upgrade bug#
 1. The proposed table upgrades needs to be described formal
  * define structure field names, datatypes, table names
 1. Software-Assessment team deploys an update script
  * the script is the source for the upgrade process
  * the script has to handle the database revision #
 1. Software-Assessment team tests upgrade script on testserver
 1. Software testers tests functions in the webdb that are affected by the database upgrade (optional)
  * test reports to add under the bug#
  * new bugs related to the table upgrade process may result in new addtl. bug numbers that needs to be linked to the database upgrade bug # or can be handled within the same bug number
 1. database upgrade to schedule with proposed downtime
 1. Before starting an upgrade script, a database backup needs to be executed by Critical team
 1. The upgrade script to be executed by the Critical team
 1. Exec steps to document under the bug #

== Involved teams ==

 1. Software developers
 1. Software Assessment
 1. Software testers
 1. Critical team

== Downtime ==

 * backup of the database took less than two minutes (2011-11-23)
 * running the version1.sh script slightly over three minutes (2011-11-23)

== Permissions ==

 * Database upgrades can be executed with mysql user 'cacert' from the console
  * The cacert user has sufficient permissions to perform the required operations

== Database revision number ==

 . In August 2011, the project "Database upgrade" has been started by the Software-Assessment project team following requests from software developers who needs some table structure upgrades and new tables.
 . To be audit and SP conform, not only software updates needs to be handled under SP. Also database upgrades needs to be handled SP konform.
 . A process has been deployed under the sample [[https://bugs.cacert.org/view.php?id=976|bug #976]] that can be seen as the initialy database upgrade path.
  1. to describe the proposed database upgrades by software developers
  1. Software-Assessors to review the proposed database upgrade
  1. deployment of an upgrade script, that Critical team can execute later on the critical system
  1. the script needs to be tested on local testserver images and on the central testserver
  1. software testers that tests functions of the webdb that might be involved by the database upgrades
  1. Software-Assessment team writes instructions for the critical team how to execute the upgrade script
  1. Critical team execute then the script on the production system

 * to keep the database in a known structure a database revision number has been implemented into the webdb database that has been introduced under [[https://bugs.cacert.org/view.php?id=976|bug #976]]

  {{{
	CREATE TABLE IF NOT EXISTS `schema_version` (
		`id` int(11) PRIMARY KEY auto_increment,
		`version` int(11) NOT NULL UNIQUE,
		`when` datetime NOT NULL
	) DEFAULT CHARSET=latin1;
}}}

 * The database revision has been set to '''1''' under [[https://bugs.cacert.org/view.php?id=976|bug #976]]
  {{{
	-- Update schema version number
	INSERT INTO `schema_version`
		(`version`, `when`) VALUES
		('1'      , NOW() );
}}}

== History of database upgrades ==

 || Revision # || Bug # || When || Blog ||
 || 1          || [[https://bugs.cacert.org/view.php?id=976|bug #976]] || 2011-11-23 11:13:19 || [[http://blog.cacert.org/2011/11/533.html|Scheduled 2011-11-23]] ||


----
 . CategorySoftware
 . CategorySoftwareWebdb
 . CategorySystems