Packaging for xTuple ERP releases

 

This section documents the process xTuple uses to build an Updater package for new releases of xTuple ERP, specifically for PostBooks. The process is similar for the xTuple ERP Standard and Manufacturing Editions, but the packages for these two editions require data not available in the SourceForge repositories.

The xtupleserver portion of the SVN repository has three subdirectories:

  • dbscripts holds view, function, MetaSQL, trigger, and PostgreSQL type definitions.

  • updatescripts contains directories for each incremental release, including all alpha releases, all beta releases, all release candidates, etc.; each of these directories in turn has a number of scripts to make incremental changes to the database schema or data to support bug fixes and feature implementations.

  • packages holds the actual .gz files issued as part of the releases.

Packages are created from the contents of the dbscripts and updatescripts directories. First a holding directory needs to be created. Then the contents of the appropriate updatescripts directory and the changes to the dbscripts directory get copied. Next the package.xml file must be constructed. Finally, the holding directory gets bundled into a .gz file and tested. The Updater is platform-independent so these steps only need to be done once per release, not once per supported platform.

Make a directory to hold the package contents

The first step is to create a directory to hold the contents of the update package.

$ cd .../xtupleserver/trunk/packages
$ mkdir OLDtoNEW

The OLD portion of the directory name describes the database version to which this upgrade package must be applied. The NEW portion of the directory name describes the database version which will be the result of applying this upgrade. Remember that the xTuple ERP client application must be run with a database of the same version. Otherwise you risk corrupting your data. The conventions for OLD and NEW are described in the Developers' Guide to Creating Releases of the xTuple ERP Application. For purposes of creating Updater package directories the resulting name should be all lower-case and without any spaces or punctuation; e.g. '2.3.2 Beta2' should be changed to '232beta2'.

Collect the update scripts and changed dbscripts

The second step is to update the appropriate updatescripts subdirectory with the changed dbscripts and create a few additional files necessary for upgrading. The hard part here is finding what changed but SVN can help with this.

The precise steps differ depending on how you've checked out the xtupleserver code. Try the following:

$ cd .../xtupleserver
$ svn update
$ ls

If the output of ls is the three directories branches, tags, and trunk then you can run the following command to find out what's changed and copy it to the proper updatescripts subdirectory:

$ cd .../xtupleserver/trunk/dbscripts
$ diff -x .svn -r -q ../../tags/ROLD/dbscripts . | \
    awk '/^Files .* differ$/ {print $4; next }
         /Only in \./ {sub(":", "/", $3) ; print $3 $NF; next }
         {print}' | \
    xargs file | \
    grep -vw directory | \
    cut -f1 -d: | \
    xargs tar cf - | \
    (cd ../updatescripts/from_OLD ; tar xvf - )

Note that this is all two commands. One is a cd to put you in the right starting place. The second is a pipeline of commands that copies the changed files into place. The '\' at the end of the lines is an indication that the command carries over to another line of text. You have the option of typing the '\' at the end of the line and hitting RETURN or ENTER to continue with another line or not typing the '\' and just continuing typing. What does this long mess of a command do?

  • Compare the contents of the old tagged version of the dbscripts directory with the current directory. The cd command before the diff makes sure that the current directory is the version of dbscripts on the trunk.

    • The -x .svn option says to exclude the .svn subdirectory (we know this has changed and we don't care because it's just there for bookkeeping your checkout).

    • -r says to compare the files within any subdirectories that are found.

    • -q tells diff to summarize the output

  • The awk command converts the diff output into a list of files. The command has three parts:

    • extract the file name for any line of diff output that says the file changed

    • extract the file name for any line of diff output that says the file has been added

    • print in its entirety any line that doesn't match either of the two preceding rules

  • The result of the awk command then gets passed to the file command, which identifies the type of the file.

  • The names of changed directories get stripped out by grep. We don't want the changed directories because we're going to bundle the changed files in a moment to copy them; we want only the changed files, not the entire directory contents.

  • The cut command then strips off the file type.

  • Then we call tar to bundle all of these files together to copy them en masse.

  • Finally we change to the updatescripts directory and unbundle the resulting files. The parentheses around the last part of this command are extremely important.

If the output of the ls command in the xtupleserver directory gives a different result then you'll have to do things a bit differently. Make sure that you have a checkout of the current trunk. Then also check out the tagged version of xtupleserver somewhere else. Then you can use the same pipeline described above with slight modifications for the changed directory paths.

With the changed dbscripts files copied to the updatescripts/from_OLDVER directory, you can now proceed to cleaning up the files that are here, adding a new one, and committing them all to the repository.

To clean up the existing update scripts, open each one in a text editor. Remove any BEGIN and COMMIT statements from these files - the Updater wraps the entire update in a single transaction and we don't want the individual pieces of the package to disrupt that. The result would be at best a spurious error message and at worst a corrupted database.

$ cd updatescripts/from_OLDVER
$ vi *     # or whatever your editor of choice is

Create a setVersion script containing the following SQL statement:

SELECT setMetric('ServerVersion', 'NEWVER');

where NEWVER is the destination version number of the database upgrade. This will cause the Updater to change the version number of the database to NEWVER at the end of the upgrade.

Now tell SVN that you want it to store all of the changed dbscript files and the new setVersion script in this updatescripts subdirectory:

$ svn add setVersion
$ svn add api functions metasql triggers types views
$ svn commit -m "copied over for release"

Copy the updatescripts subdirectory

The naming convention for the package directory differs from the naming for the updatescripts subdirectory, and the update package itself gets checked in somewhere else. To make these adjustments:

$ cd .../xtupleserver/trunk/packages
$ mkdir OLDVERtoNEWVER
$ cp -r .../xtupleserver/trunk/updatescripts/from_OLDVER/* OLDVERtoNEWVER

This creates a new directory for you to work in to adjust the contents of the Updater package.

Copy changed report definitions

Now add to that working directory the reports which have changed between the previous release and the one you're working on:

$ cd .../xtuple/trunk/share
$ diff -q .../xtuple/tags/ROLDVER/share/reports reports | \
    awk '/^Files .* differ$/ {print $4; next }
         /Only in reports/ {sub(":", "/", $3) ; print $3 $NF; next }
         {print}' | \
    xargs tar cf - | \
    (cd .../xtupleserver/trunk/packages/OLDVERtoNEWVER ; tar xvf - )

Construct package.xml

Create an initUpgrade script. This is a precautionary measure that allows you to check if an upgrade was partially committed. This was a particular problem in version 1.0 of the Updater (an older version which has been superceded by newer versions) but can still happen if the update scripts have transaction handling code in them that wasn't removed earlier. This is a copy of the setVersion.sql script you created earlier with a minor change:

$ cp setVersion.sql initUpgrade
$ edit initUpgrade

Change the version number in the second argument to the stored procedure so it has a '.' (period), followed by the old version, '-', and the new version. For example, change 3.1.0Beta2 to .3.1.0Beta-3.1.0Beta2.

You can now use the contents of this working directory to help create the package.xml:

$ find * -type f | \
  sort -f | \
  awk '/[Rr]eport.*xml/ { printf "  <loadreport  file=\"%s\" grade=\"0\"     />\n", $0; next }
       /package.xml/    { next }
       /metasql.*mql/   { printf "  <loadmetasql file=\"%s\"     />\n", $0; next }
       /setVersion.sql/ { printf "  <finalscript file=\"%s\"     />\n", $0; next }
                        { printf "  <script      file=\"%s\"     />\n", $0; next }' \
  > package.xml

This pipeline does the following:

  • Searches for all of the files in the current directory.
  • Sorts the list.
  • Wraps each line in a best-guess at an appropriate XML element.

  • Writes the output to package.xml.

Now manually perform the following steps:

  • Find the line containing initUpgrade and move it to the top of the file.

  • Find the line containing setVersion.sql, move it to the end of the file.

  • Wrap the entire contents of the file in a <package> element. At the end of the file add a line containing just </package> and insert the following at the top of the file:

<package version="1.1" id="OLDVERtoNEWVER" developer="xTuple" updater="2.0.0BETA3">
  <prerequisite type="Query" name="Checking PostBooks Server Version" >
    <query>SELECT TRUE
           FROM metric
           WHERE metric_name = 'Application' AND metric_value = 'PostBooks';
    </query>
    <message>
      This package requires that it be applied against the PostBooks Server Database.
    </message>
  </prerequisite>
  <prerequisite type="Query" name="Checking PostBooks Server Version" >
    <query>SELECT TRUE
           FROM metric
           WHERE (metric_name = 'ServerVersion') AND
                 (metric_value IN ('NEWVER' OR '.OLDVER-NEWVER');
    </query>
    <message>
      This package requires that it be applied against the OLDVER version of the PostBooks Server Database.
    </message>
  </prerequisite>

Make sure you replace OLDVER and NEWVER with appropriate values.

You're almost done creating the file. Look for a line containing Precheck. If there is one then remove it and look at the contents of the Precheck file. Convert any queries you find there into <prerequisite> checks.

If you haven't already, save the file and exit the editor.

Create and test the package itself

Now that you have a collection of files to perform the upgrade, it's time to create the Updater .gz file and test it. There are two aspects to testing:

  • Ensuring that the upgrade script runs without errors.
  • Ensuring that the resulting database contains the desired structure.

First, however, you have to create the Updater package. Change to the parent directory, bundle the files, and save the work you've done so far:

$ cd ..
$ tar czf pbOLDVERtoNEWVER.gz OLDVERtoNEWVER
$ svn add pbOLDVERtoNEWVER.gz
$ svn commit -m "untested update script pbOLDVERtoNEWVER.gz

Using a backup of a database from the OLDVER, create a new database to practice upgrading. For example, get a copy of the demo database backup from the previous version, create a database, and restore the demo backup to it.

Start the Updater and open the new package. If any of the prerequisite checks fail, fix the problems in the database and reopen the new package. Once all of the prerequisite checks pass, start the update.

If you are running on a Mac and the Updater does not recognize the .gz file, try using a different version of tar to build it:

$ gnutar czf pgOLDVERtoNEWVER.gz --format gnu OLDVERtoNEWVER

Repeatedly fix problems with the package until you think all of the problems are resolved. You may need to change the order in which scripts are run or make changes to the scripts themselves. If you have to change individual scripts, don't forget to change them in updatescripts/from_OLDVER and dbscripts before copying them to the package directory and rebuilding the package.

Drop and reload the practice database and run the upgrade script all the way through again to make sure it runs cleanly.

Check in the revised package and any files you changed in updatescripts/from_OLDVER and dbscripts. This completes the first phase of making sure that the upgrade script runs without errors.

Now test that the upgraded database has the desired structure. The way xTuple has traditionally done this is by comparing the schema of the practice upgraded database with that of the dev database. This dev database is shared by the developers during the development cycle and is in essence the master reference database for the expected current schema.

Open the practice copy of the upgraded demo database with psql and run the \z command. This lists all of the tables and their access privileges. Look for lines with blank privileges. Fix the appropriate table creation scripts, copy them over, rebuild the package, and retest. In the dev database check for tables that are not owned by the admin user. If you find any, either delete them because they are not supposed to be part of the database or change the owner with ALTER TABLE.

Repeat this testing with the empty and quickstart databases.

Compare the sample databases with the latest version of dev. Generate HTML descriptions of the dev database and the upgraded demo, empty, and quickstart databases with postgresql_autodoc. There is an HTML template (currently stored in a proprietary CVS repository) which xTuple has developed to simplify the comparison process.

postgresql_autodoc --password -u admin -t html -d testdb
postgresql_autodoc --password -u admin -t html -d dev

Remove any ^Ms from the generated output then compare the generated HTML. Resolve the differences, if there are any. Edit the scripts and rebuild the package as necessary. You may have to ask other developers for help understanding why some differences exist. Repeat until the differences have been minimized.

Check in any changes you made.

Test one final time by upgrading each of the three reference databases, generating the postgresql_autodoc for the reference databases and dev, and looking at the differences between the reference databases and dev.