Contents

  1. What is the Updater?
  2. What is a Package?
  3. The structure of an Updater Package
    1. Getting started building an Updater Package
    2. package.xml
    3. Prerequisite Checks
    4. Most things are described in external files
    5. A few things are described in the file itself
    6. Creating the Package itself
  4. Packaging for xTuple ERP releases
    1. Make a directory to hold the package contents
    2. Collect the update scripts and changed dbscripts
    3. Copy the updatescripts subdirectory
    4. Copy changed report definitions
    5. Construct package.xml
    6. Create and test the package itself
  5. Packages for Add-ons to xTuple ERP
    1. General Guidelines
    2. First Release
    3. Subsequent Releases
    4. Dependencies
  6. Internal Details
    1. Loading Sequence
    2. package.xml Reference
    3. Database Structure

What is the Updater?

The Updater for xTuple ERP (PostBooks, Standard Edition, and OpenMFG Edition) is an application which enables you to update your databases from one release to the next, to upgrade from one xTuple ERP edition to another, such as from PostBooks to OpenMFG, and to load supplements developed by third parties into your xTuple database. The Updater reads and processes upgrade scripts or packages, which are collections of files bundled together into .gz files. This page documents how to create update packages.

Here are some other related documents:

What is a Package?

A Package is a compressed collection of files which may contain either a) custom software designed to extend the functionality of xTuple ERP without touching the core xTuple ERP source code or b) upgrade bundles designed to migrate xTuple ERP databases from one version to the next. xTuple creates packages to upgrade databases from one release to the next and to upgrade from one edition to another, such as from PostBooks to OpenMFG. Using Packages, third-party software developers can add features without needing C++ programming skills. And these features can be integrated seamlessly into xTuple ERP independent of the release cycles and source code requirements defined by xTuple. In short, Packages are flexible. And they make it easy for software developers to add new features--or even just simple tweaks--to customize and enhance the xTuple ERP experience.

Here are some other related documents:

The structure of an Updater Package

At the most basic level, Updater Packages are compressed tar files. This format has been adopted as one of several standards for bundling files for distribution over the Internet.

The contents of the tar file have a fairly uniform structure. At the top level is a single directory. Inside this directory there must be a file called either contents.xml (for Updater versions 1.x) or package.xml (for Updater versions 2.x). This file describes to the Updater how to load the rest of the package. The other files included in the package are either SQL scripts to run to modify the database or any of the following items to load into the database:

These may all be directly in the top level directory of the package or arranged in subdirectories.

Getting started building an Updater Package

The first thing you need to do when building an Updater package is to create a directory: 

$ mkdir sampledir

All of the command line examples in this document assume that you are working in a UNIX-like environment. That could be a Terminal window on the Macintosh, a Shell or Console window on Linux, or a Cygwin window on Windows. Things that the user types will always be preceded by '$ ', indicating a shell prompt. Lines without this prompt are sample output lines.

Everything that you want to include in this package should eventually be put into this directory. The first thing you should put in this directory is a package.xml file. Building a package is an iterative process of adding files to the directory, modifying package.xml, bundling everything together, and testing. So don't worry yet about having all of the pieces together.

package.xml

The package.xml file contains the information required to orchestrate a database upgrade or package addition to an xTuple database. The syntax is described in full below. The file is, as its name suggests, an XML file. The root element must be <package>. Attributes of the <package> element name the package, provide a consistency check with the package's top-level directory, and let the creator specify a minimum Updater version to use when loading the package: 

<package id="sampledir"
         name="samplepackage"
         developer="xTuple"
         descrip="Sample described on CreateUpdaterPackages wiki page."
         updater="2.0.0">
  <pkgnotes>
    This is an example for the wiki page describing the
    packaging functionality in xTuple ERP's Updater utility.
  </pkgnotes>
  ...
</package>

In order of appearance, here's what this all means:

The file contents.xml, used by Updater versions 1.x to describe package contents, has been deprecated. Please do not create new packages using it.

Prerequisite Checks

The id and updater attributes of the <package> element allow for simplistic internal consistency checking: Does this package contain the expected directory and are you running a version of the Updater that can load this package. This is rarely sufficient for loading a package of any real complexity.

The Updater lets you provide database queries to make more interesting checks. xTuple uses these Query prerequisites extensively in upgrade packages to confirm that the starting database version for the upgrade matches expectations and to check for data that need to be fixed before the upgrade will be successful. The sole criterion for writing a correct prerequisite query is that it return an SQL boolean TRUE value if it is safe for the Updater to load this package.

Here is an example: 

 ...
 <prerequisite type="Query" name="Checking SQL DB Server" >
  <query>SELECT metric_value = 'PostBooks'
         FROM metric
         WHERE metric_name = 'Application';</query>
  <message>
    This package requires that it be applied against a PostBooks database.
  </message>
 </prerequisite>
 ...

All of the prerequisites are checked before the Updater returns from this phase of processing. This lets the user learn about all of the problems that s/he should fix before trying to run the Updater again to load this package.

There is also a license prerequisite, which allows the package developer to present the user with an end-user license agreement. The user must click an Accept button before the Updater will allow the package to be installed. This license is not written anywhere by the Updater. The license text in the <message> element may contain rich text, as defined by Trolltech: 

 ...
 <prerequisite type="license" name="sampleLicense" >
   <message>
     &lt;p>This is the content of a &lt;i>rich text&lt;/i> license.
     &lt;hr/>
     You must either accept or not.&lt;/p>
   </message>
 </prerequisite>
...

Notice the &lt; in several places. Because this is an XML file, the < character used to mark the beginnings of rich text tags must be escaped so they are not treated as part of the <package> element tree. xTuple has tested the Updater using &lt; substitution for <. Using XML CDATA sections should also work but xTuple has not tested this.

Most things are described in external files

Now that the preliminaries have been dispensed with, how do we actually load things into the database? For the most part by creating small files that do (or contain) just one thing.

You can create arbitrary database scripts. If you have several changes that you want to make to the database that are all related, they should go in a single script file. If there are several unrelated changes, create several different files. Put these script files into the package directory and add a line to package.xml for each: 

 ...
 <script         file="runFirst.sql" />
 <script         file="runSecond.sql" />
 ...

The Updater has several different elements for specific kinds of database object. These can be used for either update or add-on packages; the syntax was specifically designed to support the bookkeeping necessary for add-on packages but may also be used in database upgrade or xTuple ERP edition upgrade packages. Put a script file for each of the following sample lines into the package directory: 

 ...
 <createtable    file="pkgtest.sql" name="pkgtest" />
 <createfunction file="pkgtesttriggerfunction.sql" name="_pkgtestbefore" />
 <createtrigger  file="pkgtesttrigger.sql" name="pkgtestbefore" />
 <createview     file="pkgtestview.sql" name="pkgtestview" />
 ...

The package creator has to decide whether to use the specific <createstuff> elements or <script>. If the package is a non-core feature contribution outside the application core, then the decision is easy to make: Use <createstuff> because these elements automatically do the proper bookkeeping for storing the package as a unit in the database and reporting the package contents accurately. When building upgrades to the core, however, the choice is less clear. One of the properties of the <createstuff> elements is that the Updater processes them in a particular order to minimize conflict. If this default order does not work for this particular upgrade, however, the package creator must use <script> elements to enforce a sequence that will work.

The Updater also has different elements for xTuple ERP objects. These are stored in special tables used by the xTuple ERP database. The files used for the following elements are not database scripts. Instead they hold the actual objects themselves. 

 ...
 <loadmetasql    file="sampleQuery.mql" />
 <loadreport     file="sampleReport.xml" />
 <loadappscript  file="sampleScreen.script" name="sampleScreen"/>
 <loadappui      file="sampleScreen.ui" />
 <loadimage      file="sampleImage.png" name="aPicture" />
...

A few things are described in the file itself

You could create new privileges and custom commands using <script> elements. However, these things are so simple and so common that the Updater has direct support for creating them without SQL scripts.

To create a new privilege: 

 ...
 <loadpriv      name="ViewSampleScreen" module="System">
   Allowed to view the sample screen
 </loadpriv>
 ...

This creates a privilege named ViewSampleScreen. It can be assigned to users or groups of users by selecting System from the Module combo box on the User Information or Group windows.

To create a new custom command: 

 ...
 <loadcmd       name="SampleCMD" title="Sample Screen"
                privname="ViewSampleScreen" module="System"
                executable="!customuiform">
    Sample Screen Custom Command Description - view mode
    <arg value="uiform=SampleScreen"/>
    <arg value="mode=view"/>
  </loadcmd>
...

This creates a new custom command called SampleCMD, which appears in the menu system as System -> Custom Commands -> Sample Screen. When the user selects this menu item, the application opens the User Interface form Sample Screen, which was loaded earlier, and passes it the argument mode=view.

Creating the Package itself

After you have copied all of the scripts, .ui files, .mql files, OpenRPT .xml files, etc. to your package directory, you can bundle all of the pieces together with a single command: 

tar czf packagedir.gz packagedir

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 OpenMFG 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:

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 PostBooks and OpenMFG Applications. 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?

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('OpenMFGServerVersion', '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:

Now manually perform the following steps: 

<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 = 'OpenMFGServerVersion') 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:

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.

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.

Packages for Add-ons to xTuple ERP

The Updater has been used by xTuple for a long time to manage database upgrades. With the addition of application scripting and site-specific forms came the desire to bundle pieces of non-core functionality so they could be installed together to extend the xTuple ERP functionalty. Issue 6908 and the associated PackageManager specification expanded the role of the Updater to include the ability to load this non-core functionality.

The general process of packaging add-ons is similar to that of packing xTuple ERP upgrades but differs in some details and in the range of package syntax used. For example, the Updater used the name attribute of the root-level <package> element in package.xml to distinguish general updates from add-on packages. This name is used to create a separate schema in the xTuple ERP database, where all of the pieces of your package get stored (see below for details).

General Guidelines

Here are some general rules you should follow if you are creating an add-on package for xTuple ERP:

First Release

Creating a package for the first time is relatively simple because you have no backwards compatibility issues. This will certainly crop up if you need to upgrade your package contents in the future. For now, however, let's just talk about first-time package creation.

So, how do you start? Like with the xTuple ERP upgrades, you need to collect individual files, each of which contains one piece of data or performs one basic task. Here are the kinds of things you might want to include in your package:

Create a directory to hold all of these pieces. It would be best to give this the same name you want your package to have. As you work on the components of your package, you should put them in this directory. xTuple recommends that you use a source code control system such as CVS or SVN to manage this directory and its components. If the package is relatively small then you can put all of the pieces directly into this directory. If the package grows too large it might be easier to create subdirectories for QtScript files, .ui files, report definitions, etc.

Once you have a directory to hold this stuff, create an essentially empty package.xml file. Create the <package> element with its id, name, developer, and version attributes and <pkgnotes> children to describe at a high level the package and its contents. The id attribute must match the directory name; otherwise the user will get an error when s/he tries to install your package. The version attribute can be whatever you like but it would be best to start at 0.1 or 1.0.

Use Qt Designer to generate the screen definitions. If you want to use xTuple's custom widgets you'll need to install the shared library that comes with xTuple ERP in the Designer plugins directory before you start designing your windows. Watch the DevelopmentEnvSetupGuide for changes to this but as of this writing (September, 2008, during the final phases of releasing xTuple ERP version 3.1.0) please use the Qt 4.3.2 version of Designer. Save the .ui files to your package directory and add <loadappui> elements to your package.xml file as you create new ones.

As you write QtScript to drive your windows, place them in individual files as well, one for each window that they will control. Add <loadappscript> elements to package.xml.

When you find that you need to add custom commands and privileges to your package, you can write these directly into the package.xml. See the syntax summary for details or the privilege and command examples above.

You can use OpenRPT utilities to build and test reports and MetaSQL definitions, then save them to your package directory. The openrpt binary has a report editor that can save reports either to a database or to a text file. The metasql application has a simple text editor you can use to create the statement as well as the ability to pass parameter values to your MetaSQL statement and execute the result against the database.

Image files can simply be copied to your directory. These can either be binary image files or uuencoded versions of those images.

Remember to explicitly add each file you create, including package.xml, to the source control system. Some, like RCS and CVS, will need to be told when you are adding binary files; others, like SVN, can figure this out on their own.

If you find that there is something you cannot add using these tags, then use the <script> and <finalscript> tags to write arbitrary SQL statements. What's the difference between <script> and <finalscript>? <script> lines are executed first, before any other tags (except <prerequisite>s of course), while <finalscript>s are executed last.

Subsequent Releases

Just like any other software product, you'll probably have to update your add-on package to add new functionality or make it run with updated versions of xTuple ERP. When you create update packages you might have to stretch some of the guidelines a bit. For example, you might want to create a package that upgrades existing old versions of your package but installs fresh if the database does not have a copy of your package already installed.

To illustrate some of the potential problems, let's talk about a package that contains a simple form, script, and table. In version 1.0 of your package the table had two columns and for version 1.1 you realized that you needed to add a column. The form and script don't cause any issues because the Updater just replaces the old 1.0 versions with the new 1.1 versions of these. They're just data stored in a table and the Updater knows to do an UPDATE rather than an INSERT for these.

The table change, however, will cause problems. For new installations of your package you want the table to be CREATEd while for existing installations you want it to be ALTERed. The package.xml line is the same in either case and, in fact, is exactly the same in both versions 1.0 and 1.1 of package.xml: 

<createtable name="myTable" file="myTable.sql" />

In version 1.0 you might have written myTable.sql like so: 

-- myTable.sql script for package version 1.0
CREATE TABLE myTable (
    my_id     SERIAL,
    my_value  INTEGER
);
REVOKE ALL ON TABLE myTable FROM PUBLIC;
GRANT  ALL ON TABLE myTable TO   admin;
GRANT  ALL ON TABLE myTable TO   GROUP openmfg;

REVOKE ALL ON TABLE myTable_myTable_id_seq FROM PUBLIC;
GRANT  ALL ON TABLE myTable_myTable_id_seq TO   admin;
GRANT  ALL ON TABLE myTable_myTable_id_seq TO   GROUP openmfg;

This creates the table with two columns and makes sure that all xTuple ERP users but only xTuple ERP users can see the table and the sequence that controls the id column. The trick for the upgrade is to write myTable.sql so it can handle both the case where the table already exists and where the table does not. It might look something like this: 

CREATE FUNCTION createMyTable() RETURNS INTEGER AS $$
DECLARE
  _statement TEXT := '';
  _version   TEXT := '';
BEGIN
  IF (EXISTS(SELECT relname
               FROM pg_class, pg_namespace
              WHERE relname='mytable'
                AND relnamespace=pg_namespace.oid
                AND nspname='mypackage')) THEN
    _statement = 'ALTER TABLE myTable ADD my_comment TEXT;';
  ELSE
    _statement = 'CREATE TABLE myTable (' ||
                 '  my_id      SERIAL,'   ||
                 '  my_value   INTEGER,'  ||
                 '  my_comment TEXT'      ||
                 ');';
  END IF;

  EXECUTE _statement;

  RETURN 0;
END;
$$ LANGUAGE 'plpgsql';

SELECT createMyTable();
DROP FUNCTION createMyTable();

REVOKE ALL ON TABLE myTable FROM PUBLIC;
GRANT  ALL ON TABLE myTable TO   admin;
GRANT  ALL ON TABLE myTable TO   GROUP openmfg;

REVOKE ALL ON TABLE myTable_myTable_id_seq FROM PUBLIC;
GRANT  ALL ON TABLE myTable_myTable_id_seq TO   admin;
GRANT  ALL ON TABLE myTable_myTable_id_seq TO   GROUP openmfg;

Note that this revised script does exactly one thing as its net effect: It ensures that the table exists with the desired format. If the table already exists, then it gets updated. If the table does not exist then it gets created. A stored procedure is created to do this work, gets executed, and is finally dropped.

Dependencies

If you followed the advice above and split your add-on into several packages, where one package depends on another to work properly, you'll have to make sure that the Updater recognizes this dependency. The package.xml supports a special <prerequisite> description for tracking dependencies. To pass the prerequisite check, the package described in the <dependson> element of the <prerequisite> must be installed:

Here is a sample dependency prerequisite specification: 

<prerequisite type="dependency" >
  <dependson name="parentpkg" version="1.1" developer="myCompany"/>
  <message>
    parentpkg, written by myCompany, contains features my package needs.
    Specific aspects of my package require version 1.1 of parentpkg.
  </message>
 </prerequisite>

Internal Details

Loading Sequence

When the Updater first opens a package the following things happen:

At this point the user has the option to process the update, open a different package, or quit. If s/he chooses to process the update, then s/he should click the Start Update button. When the Start Update button is pressed the Updater does the following in this order:

Different types of element are loaded in the order listed above. Within a single type, elements are loaded in the order they are found in the package.xml file.

It is important to note the differences between named and unnamed packages. They are distinguished by whether the <package> element in the package description has a name attribute or not. If it has a name then the expectation is that this is an application add-on. If the package does not have a name then the expectation is that this is a database upgrade. Database upgrades are all processed with the default schema path, so changes are applied to the public schema unless the individual script explicitly changes this. Add-on packages are stored in their own schemas. As mentioned above the Updater creates a new schema named after the package and changes the run-time schema path. The Updater creates several tables in this new schema to hold schema-specific data:

These are all created as child tables of tables in the public schema, which makes for seamless integration from the application users' perspective. It also allows administrators to disable or enable packages - internally this is done by breaking or re-establishing the inheritance relationships.

package.xml Reference

Syntax of package.xml

Here is an approximate XML Schema representation of the package file format: 

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:simpleType name="fullversionnumber">
  <xsd:restriction base="xsd:string">
    <xsd:pattern value="\d+.\d+.\d+((wip|alpha|beta|rc)(\d)?)?" />
  </xsd:restriction>
</xsd:simpleType>

<xsd:simpleType name="onerror">
  <xsd:restriction base="xsd:string">
    <xsd:pattern value="(Default|Stop|Prompt|Ignore)" />
  </xsd:restriction>
</xsd:simpleType>

<xsd:simpleType name="grade">
  <xsd:restriction base="xsd:string">
    <xsd:pattern value="(highest|\d+|lowest)" />
  </xsd:restriction>
</xsd:simpleType>

<xsd:simpleType name="enabled">
  <xsd:restriction base="xsd:string">
    <xsd:pattern value="(t(rue)?|f(alse)?|T(RUE)?|F(ALSE)?)" />
  </xsd:restriction>
</xsd:simpleType>

<xsd:complexType name="dbobj" mixed="true" >
  <xsd:complexType mixed="true"> <!-- if it isn't an attribute it's a comment -->
    <xsd:sequence>
      <xsd:attribute name="name"    use="required" />
      <xsd:attribute name="file"    use="required" />
      <xsd:attribute name="onerror" use="optional" type="onerror" />
      <xsd:element minOccurs="0" /> <!-- text of comment -->
    </xsd:sequence>
  </xsd:complexType>
</xsd:complexType>

<xsd:element name="package">
  <xsd:sequence>
    <xsd:attribute name="id"        use="required" />
    <xsd:attribute name="name"      use="optional" />
    <xsd:attribute name="developer" use="optional" />
    <xsd:attribute name="version"   use="optional" type="xsd:decimal"/>
    <xsd:attribute name="descrip"   use="optional" />
    <xsd:attribute name="updater"   use="optional" type="fullversionnumber" />

    <xsd:element name="pkgnotes" minOccurs="0" maxOccurs="unbounded" />

    <xsd:element name="prerequisite">
      <xsd:sequence>
        <xsd:attribute name="name"  use="optional" \>
        <xsd:attribute name="type" type="(query|license|dependency)" />
        <xsd:element name="query" />
        <xsd:element name="dependson">
          <xsd:sequence>
            <xsd:attribute name="name" \>
            <xsd:attribute name="version" type="decimal" \>
            <xsd:attribute name="developer" \>
          </xsd:sequence>
        </xsd:element>
        <xsd:element name="message" />
        <xsd:element name="providedby" >
          <xsd:sequence>
            <xsd:attribute name="name" />
          </xsd:sequence>
        </xsd:element>
      </xsd:sequence>
    </xsd:element>

    <xsd:element name="script" minOccurs="0" maxOccurs="unbounded">
      <xsd:sequence>
        <xsd:attribute name="file"    use="optional" /> <!-- name and file are equiv, one is required -->
        <xsd:attribute name="name"    use="optional" /> <!-- file takes precedence   -->
        <xsd:attribute name="onerror" use="optional" type="onerror" />
      </xsd:sequence>
    </xsd:element>

    <xsd:element name="createfunction" minOccurs="0" maxOccurs="unbounded" type="dbobj" />
    <xsd:element name="createtable"    minOccurs="0" maxOccurs="unbounded" type="dbobj" />
    <xsd:element name="createtrigger"  minOccurs="0" maxOccurs="unbounded" type="dbobj" />
    <xsd:element name="createview"     minOccurs="0" maxOccurs="unbounded" type="dbobj" />

    <xsd:element name="loadmetasql" minOccurs="0" maxOccurs="unbounded">
      <xsd:complexType mixed="true"> <!-- if it isn't an attribute it's a comment -->
        <xsd:sequence>
          <xsd:attribute name="file"    use="required" />
          <xsd:attribute name="name"    use="optional" />
          <xsd:attribute name="group"   use="optional" />
          <xsd:attribute name="onerror" use="optional" type="onerror" />
          </xsd:sequence>
      </xsd:complexType>
    </xsd:element>

    <xsd:element name="loadpriv" minOccurs="0" maxOccurs="unbounded">
      <xsd:complexType mixed="true"> <!-- if it isn't an attribute it's a comment -->
        <xsd:sequence>
          <xsd:attribute name="name"    use="required" />
          <xsd:attribute name="module"  use="optional" /> <!-- defaults to Custom -->
          <xsd:attribute name="onerror" use="optional" type="onerror" />
      </xsd:sequence>
    </xsd:element>

    <xsd:element name="loadreport" minOccurs="0" maxOccurs="unbounded">
      <xsd:complexType mixed="true"> <!-- if it isn't an attribute it's a comment -->
        <xsd:sequence>
          <xsd:attribute name="file"    use="required" />
          <xsd:attribute name="name"    use="optional" />
          <xsd:attribute name="onerror" use="optional" type="onerror" />
          <xsd:attribute name="grade"   use="optional" type="grade" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>

    <xsd:element name="loadappui" minOccurs="0" maxOccurs="unbounded">
      <xsd:complexType mixed="true"> <!-- if it isn't an attribute it's a comment -->
        <xsd:sequence>
          <xsd:attribute name="file"    use="required" />
          <xsd:attribute name="name"    use="optional" />
          <xsd:attribute name="onerror" use="optional" type="onerror" />
          <xsd:attribute name="order"   use="optional" type="grade" />
          <xsd:attribute name="enabled" use="optional" type="enabled" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>

    <xsd:element name="loadappscript" minOccurs="0" maxOccurs="unbounded">
      <xsd:complexType mixed="true"> <!-- if it isn't an attribute it's a comment -->
        <xsd:sequence>
          <xsd:attribute name="file"    use="required" />
          <xsd:attribute name="name"    use="required" />
          <xsd:attribute name="onerror" use="optional" type="onerror" />
          <xsd:attribute name="order"   use="optional" type="grade" />
          <xsd:attribute name="enabled" use="optional" type="enabled" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>

    <xsd:element name="loadcmd" minOccurs="0" maxOccurs="unbounded">
      <xsd:complexType mixed="true"> <!-- if it isn't an attribute or an <arg> child it's a comment -->
        <xsd:sequence>
          <xsd:attribute name="name"       use="required" />
          <xsd:attribute name="title"      use="required" />
          <xsd:attribute name="execitable" use="required" />
          <xsd:attribute name="module"     use="required" />
          <xsd:attribute name="privname"   use="optional" />
          <xsd:attribute name="onerror" use="optional" type="onerror" />
          <xsd:element name="arg" minOccurs="0" maxOccurs="unbounded" >
            <xsd:sequence>
              <xsd:attribute name="value" use="required" />
            <xsd:sequence>
          </xsd:element>
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>

    <xsd:element name="loadimage" minOccurs="0" maxOccurs="unbounded">
      <xsd:complexType mixed="true"> <!-- if it isn't an attribute it's a comment -->
        <xsd:sequence>
          <xsd:attribute name="file"    use="required" />
          <xsd:attribute name="name"    use="required" />
          <xsd:attribute name="onerror" use="optional" type="onerror" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>


    <xsd:element name="finalscript" minOccurs="0" maxOccurs="unbounded">
      <xsd:sequence>
        <xsd:attribute name="file"    use="required" /> <!-- name and file are equiv, one is required -->
        <xsd:attribute name="name"    use="optional" /> <!-- file takes precedence   -->
        <xsd:attribute name="onerror" use="optional" type="onerror" />
      </xsd:sequence>
    </xsd:element>

  </xsd:sequence>
</xsd:element>
</xsd:schema>

Notice the attribute onerror on most of the elements in the package description. This attribute lets you specify whether an error which occurs during processing should stop the update immediately, be ignored, be automatically retried, or if the Updater should ask the user which of these paths to take. The default is stop, which causes the upgrade to roll back. Be very careful if you decide to override this default for several reasons:

Special notes on report definitions

The name of the report in the <loadreport> element's name attribute is overridden by the content of the report file's <name> element. The description of the report in the <loadreport>'s comment text is overridden by the content of the report file's <description> element.

If a report's grade attribute is set to 'lowest', then the grade is set to the smallest unused grade for reports with this name. if the grade attribute is set to 'highest', then the grade is set to one greater than the highest used report_grade for reports with this name.

Special notes on MetaSQL definitions

The group, name, and comments assigned to a MetaSQL statement in the <loadmetasql> element are overridden by the contents of the loaded file. If the file has a comment on a line by itself that starts with the word group: (case insensitive) then this is the group value used for the MetaSQL statement. If the file has a name: comment then this is the name value used for the MetaSQL statement. If the file has a notes: comment then this is the comment value used for the MetaSQL statement. All comment lines following the notes: comment are included. For example: 

   -- Group:display
--naMe:   fillList
SELECT * FROM cohead WHERE cohead_id = <? value("cohead_id") ?>;
 -- NOTES: This
-- is a
          --strange comment

will create an entry in the metasql table like this:

metasql_group

display

metqsql_name

fillList

metasql_notes

This is a strange comment

Special notes on.UI files

The order attribute of <loadappui> elements is handled much like grades are for reports. The difference is that uiforms may have multiple rows with the same order, so there is no special processing to avoid duplicates.

If the .ui file named in the file attribute does not have <ui> as the document's root element, then the load fails. The value given to the name attribute is overridden by the the name of the first <class> element in the .ui file.

Special notes on Application Scripts

The order attribute of <loadappscript> elements is handled the same was as for <loadappui> elements.

Special notes on Images

The Updater looks at the first few characters of image files before loading them into the database. If an individual file is a raw graphics file, then it is uuencoded before loading into the db. If the file is already uuencoded, then it gets loaded as is. The Updater supports the same image formats as the main xTuple ERP application does, which in turn is dependent on how Qt was built. See the DevEnvSetupGuide for Qt configuration options, including supported graphics formats.

Database Structure

The xTuple ERP application uses primarily the public schema in the application database. Add-on packages are written to their own schemas, named after the package itself. When the Updater scans package.xml it checks to see if this is a named add-on package or not. If it is an add-on, then it creates a schema to hold the package. If a schema with that name doesn't already exist, then it changes the search path to put this package-specific schema first. Any database objects that get created by the update, like tables and views, are then created in this package schema rather than in public.

The Updater uses a number of tables in the database. Most tables are only used by particular features of the Updater. As long as these tables exist, the Updater itself will work. If you don't use a certain feature then the Updater won't use that particular table. Here is a list of tables directly accessed by the Updater:

Table

Used By

Comments

pkghead

all parts

only for add-on packages

pkgitem

all parts

only for add-on packages

cmd *

<loadcmd>

has sequence cmd_cmd_id_seq

cmdarg *

<loadcmd><arg>

has sequence cmdarg_cmdarg_id_seq

image *

<loadimage>

has sequence image_image_id_seq

metasql *

<loadmetasql>

has sequence metasql_metasql_id_seq and accessed through a stored procedure savemetasql(TEXT, TEXT, TEXT, TEXT, BOOLEAN) RETURNS INTEGER

priv *

<loadpriv>

has sequence priv_priv_id_seq

report *

<loadreport>

has sequence report_report_id_seq

script *

<loadappscript>

has sequence script_script_id_seq

uiform *

<loadappui>

has sequence uiform_uiform_id_seq

All of the tables marked with an asterisk (*) have the following in common:

There are two reasons for using schemas to hold add-on packages:

CreateUpdaterPackages (last edited 2008-12-31 21:38:56 by ptyler)