1

Database upgrade

Pierrick Le Gall edited this page 2023-05-04 18:25:46 +02:00

Sometimes a new feature requires modification in the database. A new table for instance. Since Novembre 24th 2005, a system make it possible to automatically send database upgrade scripts to other developers : upgrade_feed.php and install/db/*-database.php. You must not forget to report the database modification in install/phpwebgallery_structure.sql for new installations.

IMPORTANT: on a stable branch, once the first release has been "released",
database upgrades must be avoided unless there is no other workaround.
A database upgrade is a complicated work for users and a very complicated
work for release packager, often generating errors in the last release build
steps. Take database upgrades on stable branches very seriously.

1. find next available upgrade identifier in your install/db directory, let's say 43, and create install/db/43-database.php. On trunk branch, upgrade identifiers are ascending integers. On a stable branch (branches/1.4, branches/1.5), identifier is the the concatenation of the identifier of the last upgrade script on trunk before fork point. If the last upgrade id before branch 1.6 is 14, then upgrade identifiers on branch 1.6 will be 14.1, 14.2 and so on. To make it clear, an example graph might be useful

          +--------------+
          | branch trunk |
          +--------------+
                 |
                 |     +------------+
                 |     | branch 1.6 |
           43 => |     +------------+
           44 => |           |
                 +-----------+
                 |           | <= 44.1
                 |           |
           45 => |           |
                 |           | <= 44.2
           46 => |           |
                 |           |
                 |           | <= 44.3
                 |           |
           47 => |           |
                 |           |
                 |           |
                 |           |
           48 => |           | <= 44.4
                 |           | <= 44.5
           49 => |           
                 |
           50 => |
                 |     +------------+
           51 => |     | branch 1.7 |
           52 => |     +------------+
           53 => |           |
                 +-----------+
           54 => |           | <= 53.1
                 |           |
           55 => |           | <= 53.2
                 |           |
                 |           |
                 |           | <= 53.3
                 |           
                 |
   

   • script 43-database.php is a PHP script, and not a SQL only script. This mean you can do much more advanced operation than SQL let you do. Take ''1-database.php'' as template:

<?php
// [standard header]

if (!defined('PHPWG_ROOT_PATH'))
{
  die('Hacking attempt!');
}

$upgrade_description = 'Just a beginning test';

// +-----------------------------------------------------------------------+
// | Upgrade content                                                       |
// +-----------------------------------------------------------------------+

list($now) = mysql_fetch_row(pwg_query('SELECT NOW()'));

echo $now."\n".'This upgrade script is for test purpose only' ."\n" ;
?>

1. modify install/phpwebgallery_structure.sql consequently. Do it by dumping your database structure

# "prefixTable" is the prefix you use for tables in your personal database
$ mysqldump --user=<user> --password=<password> --add-drop-table --no-data <my database name> \
    | perl -pe 's{prefixTable}{piwigo_}g' \
    > install/piwigo_structure.sql

2. add/commit files to Git