DBUPDGEN

DBUPDGEN generates a Perl script that updates one or more subsystems in a database by comparing two SourceSafe versions of the subsystem(s) and includes all changed files in the update script. If a file with a table has changed, DBUPDGEN generates SQL code to move data from the old definition of the table to the new definition.

You can also feed DBUPDGEN a list of selected files, if you want to script to deploy a patch for a subsystem.

DBUPDGEN can read the scripts it generates, and regenerate them while preserving manual changes you have made to the script.

To get an overview of the overall process where DBUPDGEN fits in, see the page Life-cycle of a database.

Contents:
   Command-Line Syntax
   Command-Line Syntax for the Update Script
   Output from an Update Script
   Notes on Regular Update Scripts
   Patch Scripts
   Checking Subsystems and Their Versions
   Inside the Update Script and How it is Updated
   Table Updates
   Advanced Topics

Command-Line Syntax

To create a new script:

   dbupdgen -config config-file | -VSS SS-path [-subsystem subsys]
            -from from-version [-to to-version] [-checkedout]
            [-patch patch-file] [-listonly] script-file

To update an existing script:

   dbupdgen [-to to-version] [-checkedout] [-listonly] script-file

`-config`	Instructs DBUPDGEN to build the update script for the subsystems in config-file. DBUPDGEN ignores any configuration options in the config-file and only cares about the SS-paths of the subsystems. If the SS-path for a subsystem does not end in SQL, DBUPDGEN tacks on /SQL to the path. If a subsystem does not have an SQL project in SourceSafe, DBUPDGEN emits a warning. When you create a new script, you must specify one of `-config` and `-VSS`. When you update an existing script, DBUPDGEN reads this information from the script.
`-VSS`	Instructs DBUPDGEN to build the update script for the a single subsystem, located in SS-path. Normally you also specify the subsystem name with `-subsystem`. If you do not specify a name, DBUPDGEN will use NAMELESS when it generates the script. (Which may not match the target database.) When you create a new script, you must specify one of `-config` and `-VSS`. When you update an existing script, DBUPDGEN reads this information from the script.
`-subsystem`	With `-config`, you use `-subsystem` to restrict the script to only include this subsystem. With `-VSS`, you use `-subsystem` to specify the name of the single subsystem DBUPDGEN is to generate the update script for. When you update an existing script, DBUPDGEN reads the information from the script.
`-from`	Which SourceSafe version of the subsystem(s) that DBUPDGEN is to use as the baseline for the update script. For a config-file, from-version is a version number, date or label for the config-file, which in its turn determines which version of the projects to use, as described on the config-file page. For a singular project that you specify with `-VSS`, from-version is a version number, date or label of that project. You should use a label on the form LetterMajor.Middle.Minor and in databases where the subsystem ABAPERLS is installed, this format is mandatory. When you create a new script, you must specify `-from`. When you update an existing script, DBUPDGEN reads this information from the script.
`-to`	Which SourceSafe version of the subsystem(s) that the upgrade script is intended to update the database to. For a config-file, to-version is a version number, date or label for the config-file, which in its turn determines which version of the projects to use. If you omit to-version, DBUPDGEN will work with the most recently labelled version of each subsystem, unless they have explicit version-designators in the config-file. You can also specify to-version as LATEST, in which case DBUPDGEN will work with the most recently checked-in version of each file in all subsystems. For a singular project that you specify with `-VSS`, to-version is a version number, date or label of that project. You should use a label on the form LetterMajor.Middle.Minor and in databases where the subsystem ABAPERLS is installed, this format is mandatory. You can also omit `-to`, in which case DBUPDGEN will work with the most recently checked-in version of each file in the subsystem. You can specify `-to` when you generate a new script, or when you update an old script. When you update an existing script, you typically provide a later label than what you specified when you originally generated the script.
`-patch`	Creates an update script that only includes the file specified in patch-file. See further below under Patch Files about the format. When you update an existing script, DBUPDGEN reads this information from the script.
`-checkedout`	Includes also files that are checked out, even if they are unchanged between from-version and to-version. This is handy if you work with a big update script with table changes, and you want to test the script before you check in your changes. For the time being, DBUPDGEN includes all checked-out files, not only those you have checked out. While it is permitted, you typically do not specify a to-version other than LATEST and `-checkedout`.
`-listonly`	Instructs DBUPDGEN to list which projects and which versions of the projects that will be processed and then terminate, without searching SourceSafe and without generating any script.
script-file	Determines the name of the script. Typically the name ends in .PL, but DBUPDGEN does not provide this as a default.

Command-Line Syntax for the Update Script

   perl script-file
        {-database db [-Server server] [-Password pwd] [-save file]}
        | -noexec
        [-get | -noVSS] [-[no]use_disk] [-log file] [-force]
        [-charset CP] [-[no]crypt] [-[no]subscriber] [-site site-id:s]
        [-[no]Adefaults] [-[no]Anulls] [-[no]Apadding] [-[no]Aquoted]
        [-[no]quoterepl]
        [-Macro &macro=value [...]] [-undef &macro [...]]

As with DBBUILD, you must specify one of -db and -noexec, and you cannot specify both of them.

Database switches
script-file	The name of the script that you generated with DBUPDGEN. Typically the name ends in .PL.
`-database`	Name of the database to update.
`-Server`	The server on which the database resides. Default is the local server. The environment variable ABAPERLS_CTIMEOUT determines how log to wait for a connection. The default is 5 seconds. Note that if you specify the server instance by port number, you must enclose the server specifcation in quotes, for instance `-S "127.0.0.1,2093"`.
`-User -Password`	Which login and password to use with SQL authentication. If you leave out both username and password, AbaPerls will use Windows authentication. If you specify `-password` only, AbaPerls will login as sa. It's illegal to specify a user without a password. If you have a blank password, you need to change this before you can use AbaPerls. No matter if you use SQL authentication or Windows authentication, the login you use must map to a database user with dbo as the default schema, or AbaPerls will not let you proceed. Obviously, the user needs permission to perform all actions implied by AbaPerls and the script you run.
`-save`	Name of the file into which te update script is to save the SQL code it generates. If you do not provide an extension, the script adds .SQL. If you do not specify `-save`, the update script saves the SQL code in script.SQL.There is no way to specify that you do not want an SQL file to be generated. The SQL file can be useful if some part of the update script fails, typically a table update. Rather than restoring a backup and run the entire script again, you may be able repair what is broken and the extract the problematic part from the SQL file to rerun only that part. (Obviously such a manœuvre requires that you are fully confident with it.) Note: the generated SQL code depends on the database you run the script against. Two different databases could very well result in different code being generated, and the code generated for one database, may fail in another database.
`-noexec`	Instructs the update script to get the files and them through Preppis to get all include files referred to, but not proceed with further processing. You normally use `-noexec` with `-get` to create kits for installation at customer sites. When you specify `-noexec`, you cannot specify `-database`, `-Server`, `-Password` or `-save`.
SourceSafe switches You cannot specify `-config`, `-VSS`, `-subsystem` or `-label` to an update script, as the value for these were determined by DBUPDGEN and hard-wired into the update script.
`-get`	With `-get`, the update script creates, in the current directory, a directory for each subsystem included in the update script. In each subsystem directory, the script gets the files, placing them in an AbaPerls SQL directory structure. Files with objects that are to be dropped from the database are placed in a separate AbaPerls SQL directory structure under OBSOLETE-FILES in the subsystem directory. Normally you use `-get` together with `-noexec` to create kits for installation at customer sites. You cannot specify `-get` together with `-use_disk` or `-noVSS`.
`-noVSS`	Instructs the update script to not read from SourceSafe, but get all files from disk. You use this when installing at a customer site without access to SourceSafe. `-noVSS` implies `-use_disk`.
`-use_disk`	Without `-noVSS`, `-use_disk` instructs the update script to first look for the files on disk, before it looks in SourceSafe, according to AbaPerls file-lookup order. This is handy when you want to test an upgrade script without checking in changes that might be experimental. With `-noVSS`, `-use_disk` is implied, and the update script looks for the file in the structure created with `-get`. The default for `-use_disk` depends on whether you specified the option `-checkedout` to DBUPDGEN. If you specified `-checkedout`, `-use_disk` is the default, and if you don't want the script to read from disk you must use `-nouse_disk` to override. If you did not specify `-checkedout`, `-nouse_disk` is the default. Bewildering? The idea is that when you are testing out a script and include files that are checked out, you also want the update script to read them from the disk. But once everything is checked in, and the script is regenerated without `-checkedout`, you don't want the update script to read from disk by mistake.
Miscellaneous
`-log`	Specifies the name for the log file, to which all output from the update script is written. If you do not specify `-log`, the update script will log to a file named db.LOG. For more infor about the log, see the section Output from Update Scripts.
`-force`	Specifies that if there is a conflict between the configuration options saved in the database and the configuration options on the command-line, the command-line takes precedence. If you do not specify `-force`, and the is a conflict between the database and the command-line, the update script will abort with an error message, and no file will be loaded. Only use `-force` when you really need to override the configuration saved in the database.
Configuration options
`-Adefaults -Anulls -Apadding -Aquoted -charset -crypt -Macro -quoterepl -site -subscriber -undef`	These switches are configuration options. The setting of a configuration option is primarily taken from the database according to the precedence rules for configuration options. If the the database defines a setting for an option and you specify a different setting, you must use `-force` to override.

Output from an Update Script

An update script produces two forms of output: it prints to the command-line window, and it produces a log file.

The output to the command-line window only serves the purpose of giving progress information and there is no need to monitor it. You see the name of each file being processed. In case of batches of SQL code for table-updates and similar, you see the first line of the SQL batch. (Often this is a descriptive comment.) Notice that a table-update may take several hours to run and during this time, you will see no output in the command-line window.

On the other hand, it is essential that you check the log file, because if a file or a batch produces errors when the update script runs it, the errors are written to the log only. The log file includes a full list of files that the update script has loaded.

First in the log file, you find a header which reports who run the script when with which command-line options. For each subsystem, there is a header with the configuration options that the update script used for that subsystem. AbaPerls reports only the configuration options that were set explicitly, by command-line switches, or settings read from the database.

You can use LISTERRS to read the log to only see the problematic parts. However, when you develop a script and add complex table-updates. you should probably review the complete log for the table-updates to verify that the updates works as you intended.

Note: There is one category of messages that are only printed to the command-line window: warnings and errors from Perl itself. To get Perl warnings you must specify the -w option to Perl. Warnings or errors from Perl could be an indication of a problem in your update script, if you have edited the output from DBUPDGEN. They could also be an indication of a bug in AbaPerls (or Perl itself). A Perl error is usually fatal and terminates the script.

Notes on Regular Update Scripts

These are update scripts generated without the -patch switch.

Version of Projects

For these scripts DBUPDGEN has two versions to play with for each subsystem. If you specified -VSS, this is from-version and to-version that you specified on the command line. If you specified -config, then from-version and to-version on the command line gives the from- and to-version for each subsystem, through the config-file, according to the rules for version-designators on the config-file page.

Determining if a File Has Changed

To determine whether a file may have changed between two versions, DBUPDGEN compares the version numbers of the file in the two SourceSafe versions. If the are the same, the file is obviously unchanged. If they are different, DBUPDGEN continues to see what actions that has caused the new version(s). For instance Branch, always create a new version, but it does not change the file. The only actions that in the eyes of DBUPDGEN could lead to a change are Check-in, Add and Rollback. Note that DBUPDGEN never go as far as actually comparing the text of the two SourceSafe-versions of the file.

Include-Files

In addition to the directly changed files, DBUPDGEN scans all files that can be referred through $INCLUDE or $REQUIRE for $USEDBY directives, and adds all referring files to the update script, even if they are unchanged, since they are affected by the change in the file on which they depend..

Assembly Files

DBUPDGEN scans the changed .ASSEM files in the ASSEMBLIES directory for $USEDBY directives, and adds all referring files to the update script, even if they are unchanged, in case the assembly must be dropped and all dependent objects with it. For more information about the CLR in AbaPerls, see the CLR page.

Site-specific Files

In the same vein, if there are site-specific versions of a file, and the main file has changed, then all site-specific versions are also included in the update script. The reverse does not apply. Example: there are there files: SOME_SP.SP, SOME_SP%ABC.SP and SOME_SP%XYZ.SP. If SOME_SP.SP has changed, DBUPDGEN includes also SOME_SP%ABC.SP and SOME_SP%XYZ.SP in the update script, even if they are unchanged. On the other hand, say that SOME_SP%ABC.SP has changed while the other two files have not. In this case, only SOME_SP%ABC_SP will appear in the update script.

Obsolete Files

All files that may have changed according to the rules above, as well as files present only in to-version are included in the update script. Files that are only in from-version end up in a special section of the script for obsolete files, so that the objects in the files will be dropped from the database. If a file has been renamed between from-version and to-version it will appear both among the files to load, and among the obsolete files, so that the object with the old name will be dropped from the database.

There is one case you need to watch out for: DBUPDGEN puts the section for obsolete files before the stored procedures, but after the table-updates. This means that if you have a renamed file which appears in the update script before the obsolete-file section, and the object(s) in the file has not changed names, the objects could be dropped. Since object-name and file-name should agree, this is not very often an issue.

Table Files

If a file with the extension .TBL has changed, DBUPDGEN creates SQL code to implement the change, by moving the data from the old schema of the table to the new schema. DBUPDGEN also includes other files related to the table, even if these files are changed. See further the section Table Updates below for a complete discussion.

If DBUPDGEN finds a file with any of the extensions .TRI, .IX and .FKEY that does not have a matching .TBL file, DBUPDGEN always adds such a file to the update script, even if the file is unchanged. This is a safety precaution, in case the table has been changed. In such a case the update script for the subsystem is likely to include a table-update that drops the old definition of the table, this means that the triggers, indexes and foreign keys in referring subsystems are not restored. Therefore the update scripts for these referring subsystem better include triggers, indexes and foreign keys just in case. Please not that this does not apply to INSERT-files, since a default table-update will reload the data.

View Files

If a file with the extension .VIEW has changed, DBUPDGEN also includes any files with indexes and triggers on the view.

Two Version-Subprojects for a Subsystem

When you specify a config-file, a subsystem may have different SourceSafe projects in the two versions of the config-file, for instance $/SPAREPARTS/4.30 and $/SPAREPARTS/4.40. DBUPDGEN accepts this (for the time being), but treats it the same way as a above. That is, if a certain file has version 15 in both projects that leads DBUPDGEN to consider the file as unchanged. The author leaves it as an exercise for the reader to determine when this assumption is correct, and when it can lead to DBUPDGEN failing to include changed files in the update script. This usage is certainly not encouraged, anyway.

Subsystem is Only in One Version of a Config-file

Two different versions of a config-file may include a different set of subsystems. If a subsystem is only present in the to-version of the config-file, then that subsystem is added to the database and all files in that subsystem will be included in the update script. If a subsystem is present only in the from-version of the config-file, DBUPDGEN will place all files in the section for files to be removed, and the script will also include code to delete the subsystem from the database.

Using `-checkout`

You can instruct DBUPDGEN to work from the most recently checked-in of all subsystems by omitting -to with -VSS or specify -to LATEST with -config. For a regular update script, this is useful when you develop an update script and want to test it. But you should never run such a script in a database that is to sustain, as you will then miss out on the version checks that the update scripts perform, as we discuss in the section Checking Subsystems and Their Versions. (For a patch script, this may be a perfectly normal thing to do.)

Performance Considerations

Traversing SourceSafe projects is alas a slow procedure, so DBUPDGEN may need to run for over 30 minutes for a large project. To keep you awake, DBUPDGEN occasionally reports which file it is processing. Since it traverses each subsystem twice, you are likely to see the same file mentioned twice too.

Note, there are actions that you could take to decrease the execution time for DBUPDGEN:

Convert the SourceSafe database to VSS6 format.
Archive and delete ("destroy permanently") old version-subprojects, no longer in use.
Defragment the disk where the SourceSafe database resides.

Patch Scripts

With -patch, DBUPDGEN reads only from patch-file, so this is a much speedier operation. -patch is intended for the case when you need to ship a handful of files, typically for a certain function in the application. You still need to specify -from, so that the update script can verify that the version in the database is correct. (More about this later.)

This is the format for the patch file:

Text after -- up to end-of-line is a comment.
A line is either subsystem specification, or a file specification.
A subsystem specification is a subsystem name within brackets, e.g. [ABABOS], and specifies the subsystem for the subsequent files. The subsystem must appear in config-file or be specified with -subsystem.
A file specification refers to a file in the most recently specified subsystem in patch-file. (NAMELESS, if no subsystem have been specified.) The file must be with the AbaPerls SQL directory structure. You can specify an explicit version for the file, with version number, date or label. If you do not specify a version, the version is determined by to-version, or, if you did not specify -to, the latest version of the file.

Example::

   -- This is a comment
   [TEST]
   ael_get_sp.sp
   ael_handle_sp.sp   3
   abaeventlog.tbl
   [TEST2]
   sub/nisse_sp.sp  5

In this example four files are to be included in the update script. Three of these are in the subsystem TEST, and the fourth one is in TEST2. For two of the files, there are explicit version numbers. For the other two, to-version applies.

The example includes a table. When a table is included in a patch-script, DBUPDGEN does not generate SQL code to move data between the old and new schema of the table, and neither are other files for the table included.

Checking Subsystems and Their Versions

This section discusses how the update script checks subsystems and their versions. The section applies to both regular update scripts and patch scripts.

Without the AbaPerls System Tables

If the ABAPERLS subsystem and with it the AbaPerls system tables is not installed in the database, the update script will run the update for all subsystems are are included in the script. (Thus, you must verify that the update script does not include subsystems which are not in the database.) The script writes a row to abainstallinfo, when it begins and finishes updating a subsystem. None of the checks described in the subsequent sections are performed.

Which Subsystems are Updated?

If the ABAPERLS subsystem is present in the database, things becomes a lot more interesting. The update script will then perform a number of checks to simplify management of updates, and to minimize the risk for errors, and subsequent sections covers this in detail.

The first check that the update script performs is to read which subsystem that are installed in the database, and then compare with the subsystems in the script. The update script will then only run the updates for the subsystem that are installed in the database, and the other subsystems in the script are ignored. Likewise, the update script ignores subsystems in the database not present in the script.

Checking the Version-Designators

Next, the update script performs a number of checks for the versions of the subsystems. There are three version- designators to check against each other: that of the database (abasubsystems.ss_label) and the from- and to-versions in the script. (These were derived directly or indirectly from the -from and -to switches you specified to DBUPDGEN.) The purpose with these tests is find out which subsystems that are to be updated, and to verify that no update is missing in a chain of updates.

The first check is to verify whether these three versions are on the format LetterMajor.Middle.Minor, e.g. L4.20.0020. Then the following rules apply:

If the version-designator of the database is not on this format � it is is blank, an explicit version number, a date or a label on some other format � this means "anything goes", and no further tests are performed for this subsystem.
If the from-version is not on this format, but the database version is, this is an error, and execution of the upgrade script is aborted, and no subsystem is updated. That is, if the database version is on a correct format, we want know what the database is being updated with.
If the to-version is specified, and the database version is on this format, the to-version must also be on this format. However, it is permitted for the to-version to be blank.

The From-Version and the Database Version

Next question is whether the from-version is in sync with the database version. This question is answered in the affirmative, if any of the following are true:

Major and Middle of the two versions are equal and Minor of the database version >= Minor for the from-version.
Major.Middle of the from-version is greater than Major.Middle of the database version and Minor is = 1 for the from-version and >= 1000 for the database version according to the label conventions for AbaPerls.

If neother of these conditions are true, this is an error and execution is aborted, and no subsystems are updated.

Some examples:

Database version	From-version	OK?	Comment
L4.40.0120	L4.40.0120	Yes	The standard case. The script starts on the database version.
L4.40.0120	L4.40.0100	Yes	The script starts a few versions before the database version. AbaPerls assumes that the changes between L4.40.0100 and L4.40.0120 can be re-run.
L4.40.0120	L4.40.0140	No	The script starts on a later version than the database version. AbaPerls cannot permit the changes between L4.40.0120 and L4.40.0140 to be lost.
L4.40.0120	L4.50.0001	No	The database version has not yet arrived at L4.40.1000, which, by AbaPerls label convention, is the point where 4.50 is assumed to have been created by sharing 4.40.
L4.40.1200	L4.50.0001	Yes	The database version is later than the divisive point at L4.40.1000, and also includes the changes from L4.40.1000 to L4.40.1200. The script presumably includes these changes too, but AbaPerls assumes that it is OK to run these changes twice.
L4.40.1000	L4.50.0010	No	In this case the changes from L4.50.0001 and L4.50.0010 are missing.
L4.40.0120	L4.30.1200	No	You can never go back to an earlier version.
L4.90.1000	L7.20.0001	Yes	One can assume that many versions are being lost here, but when performing this check, AbaPerls has no idea of which version-subprojects there have been along the way, so it have no other choice than to accept that this is OK.

The To-Version

The update script checks the to-version against the database version, and if they are the same, the subsystem is apparently already updated, and the update script prints an informational message without touching the subsystem. This permits you to first update a certain subsystem separately, and yet later run a multi-subsystem script which includes the subsystem.

The update script also checks the to-version against the from-version, and the simple rule is that the to-version must be ahead of the from-version. If not, this is an error, and execution is aborted, and no subsystem is updated.

If the to-version is blank, AbaPerls does not check it against anything.

Updating the AbaPerls System Tables

When the update scripts begins to process a subsystem, it writes a START row to abainstallhistory.

For each file that is loaded, the update scripts updates abasysobjects according the regular AbaPerls file-loading process.

When the update script has processed all files for the subsystem, it writes a STOP row to abainstallhistory. Furthermore, the script sets abasubsystems.ss_label to the to-version (which may be blank), unless the script was created with -patch. Thus, here is a decisive difference between regular update scripts and patch scripts: the latter does not change the version of the subsystem in the database. (Note that all other version checks that we have described here, are performed also by patch scripts.)

For the rare case that you are removing a subsystem, the update script will write a REMOVE row, rather than a STOP row to abainstallhistory. The subsystem will not be deleted from abasubsystems (as there may be historic references to the subsystem), but the update scripts sets the column deregdate to mark that the subsystem is no longer present in the database.

Inside the Update Script and How it is Updated

This section describes how the update script looks on the inside, and how DBUPDGEN updates it. Recall that even if the script is generated, it is not meant to a one-size-fits-all solution, but cover the plain-vanilla database changes like adding new stored procedures and simple table changes. The wilder the changes are, the more likely is it that you have to edit the script. Particularly this is true if you change many tables, and the table updates depends on each other.

The Setup Part

The first part of the update script, up to the first subsystem header (see below), is something that is regenerated each time you run DBUPDGEN. This part contains initialization, definition of which subsystems that the script covers and other internal business. Obviously, since this part is always regenerated, you are not supposed to meddle here.

Very first in the script, you find a header which looks like this:

   #---------------------------------------------------------------------
   # $Header: $
   #
   # DBUPDGENver:    <3.3>
   # Project:        <ababos32/$/ababos/4.80/sql>
   # Startlabel:     <L4.80.0001>
   # Patchfile:      <>
   # Last generated: 2000-12-14 10:53:23.
   # AbaPerls Level: <1>
   #
   # Handled subsystems: <ABABOS>
   #
   # $History: dbupdgen.html $
   #
   # $NoKeywords: $
   #----------------------------------------------------------------------

Header:, History: and NoKeywords: are SourceSafe macros that are useful when you check in the script. (NoKeywords prevents SourceSafe performing expansion of SourceSafe macros further down in the file, would they occur.)

When updating an existing script, DBUPDGEN reads the rows labelled Project, Startlabel, Patchfile and Handled subsystems to determine the values of -config, -VSS, -from, -patch and -subsystem respectively. The value for AbaPerls Level is assigned to an AbaPerls attribute in the startup section of the script, and is then used by the AbaPerls file-loading process to determine whether certain checks are to be carried out. The value is set by DBUPDGEN for new scripts, and then carried over if the script is generated over. This is to permit new checks affecting old scripts.

DBUPDGENver gives the version of the format for the script. DBUPDGEN use this information to determine whether it is able to read the script at all. The version of DBUPDGEN that creates script in format 3.3, also knows how to read scripts in format 3.2 and 3.0, but not in 2.x, 3.4 and 4.x. When the changes in the script format are too radical so that the first part of the version number must be incremented, the old version of DBUPDGEN may be available as DBUPDGEN.OLD or similar.

After the header there is a setup part where the update script sets up command-line options, which subsystems to work with, performs the subsystem and version checks discussed in the previous section. Then the script proceedes to loop through the subsystems and calls a Perl procedure for each subsystem to perform the update for the subsystem. After this loop the update script terminates.

The update script checks both before and after the update that there are no objects with names starting with old_ in the database. If such objects are detected before the update starts, the script terminates. This is because such objects are likely to be remnants of unsuccessful table-updates that should be sorted out. If such names are found after the update, the update script can do no better than leave a warning in the log. In either case, when AbaPerls finds old_ objects, the update script terminates with a non-zero status. (That is, %ERRORLEVEL% on DOS level.)

To repeat: the setup part is completely internal to AbaPerls, and DBUPDGEN regenerates it each time and you should edit it. (Well, there is one thing that is permissible to do: say that you specified -VSS to DBUPDGEN but forgot -subsystem. Rather than to delete the script and run DBUPDGEN anew, you can use a find/replace function to change all occurrences of NAMELESS in the update script to the actual subsystem name.)

The Subsystem Parts

After the setup part, the subsystems come. Notice that it does not matter in which order the subsystems appear in the script. This is order is determined by a Perl array, which is set up in the first part of the script.

The first thing you see of a subsystem, is the subsystem header. It looks like this:

   #**********************************************************************
   #                              PAYMENT
   #**********************************************************************

Don't touch it.

For each subsystem, there are a number of sections. All sections have a name. Section names may only contain uppercase characters, digits, underscore(_) and hyphen(-). All sections generated by DBUPDGEN, with exception of the table-updates, have a predefined name. Here are the sections listed in the order that DBUPDGEN generates them:

PROJECT-INIT. Sets the SourceSafe project and current directory (for -get and -noVSS) for the subsystem. Beware that this affects the AbaPerls file-lookup order. Writes START to abainstallinfo/abainstallhistory. Normally you have no reason to fiddle in this section.
MESSAGE. .SQL files in the MESSAGE directory.
ASSEMBLIES. .ASSEM files in the ASSEMBLIES directory. (.DLL files appear to, but they are always commented.)
TYPE. User-defined data types.
Table-update sections, one for each table where the .TBL file itself has changed. The tables appear in alphabetic order with one twist: final -s is stripped when sorting. The idea is that a main table for a concept ends in -s, which is stripped in the names of the subtables for the concept. E.g. instruments and instrumentnames.
OBSOLETE-FILES. Files that are no longer part of the subsystem, or which has been renamed. The update script will run the old versions of these files, so that the objects in these files are dropped from the database. The order of this section is the reverse of the loading order, so that obsolete types come last.
VIEW. Views, including indexes and triggers on views. (This is not entirely optimal, as a trigger may refer to a stored procedure which comes later in the script.)
FUNCTIONS. User-defined functions.
SP. Stored procedures.
TBL. Table files � triggers, INSERT-files, indexes and foreign-key files � for tables where the .TBL file is unchanged.
POSTSQL. .POSTSQL files in the MESSAGE directory.
EPILOGUE. Restores the current directory. Writes STOP (or REMOVE) to abainstallinfo/abainstallhistory. You may need to insert your own cleanup code here. In that case, insert it before the generated code.

When not otherwise noted, the order within a section is alphabetic.

The sections are delimited with lines like the one below.

   pr "#=========== TYPE ===================";

The pr command will also print the header to the log of the update script, to make it easier to see the various sections.

You can change the order of the sections, and DBUPDGEN will respect this if you regenerate a script. However, you should not move the pre-defined sections around at whim. The sections where you really may need to move around are the table-update sections, as they may be dependent of each other. You can also insert your own sections to include code for special fixes. If you remove an empty predefined section, DBUPDGEN will put it back next time you regenerate the script. (DBUPDGEN does suppress some sections if they are empty, but not all.)

In most of the sections you find calls to the Perl routine abasqlfile() for each changed file. abasqlfile() is the core routine of AbaPerls that implements the AbaPerls file-loading process, and is also used by ABASQL and DBBUILD. In the OBSOLETE-FILES section you may see two names being passed in the same call. This happens when a file has been renamed between the from-version and the to-version in which case both the old and new name are passed to abasqlfile(). (This is because SourceSafe tends to only find the file under the new name.)

DBUPDGEN prepends ;; to all calls to abasqlfile() it generates, as well as to a few more lines. Semi-colon is a statement terminator in Perl, and the double semi-colons do thus not affect the execution of the update-script as such. The ;; comes into play when you regenerate a script. DBUPDGEN then simply deletes all lines that start with ;; while retaining other lines. The retained lines will appear before the newly generated lines in each section. When DBUPDGEN regenerates a script it does not generate any calls to abasqlfile() for files for which there already are calls to abasqlfile() in the update script.

The logic of the following paragraph has some important consequences. If you find that DBUPDGEN has included a file in the update script, which you for some reason do not want to include, you should not remove the file from the script, because then the file will reappear when you regenerate the script. Instead, add a comment character (that's # in Perl) before the call to abasqlfile, as also a commented call to abasqlfile() is a call to abasqlfile() in the book of DBUPDGEN

If you change the load order of two files, remove the ;;, so that the changed order is retained when you regenerate the script. By the way, when moving files, you are free to move the files between the sections as you see fit. Just watch out that you don't move into files into OBSOLETE-FILES unless you really want to drop the objects in them.

Table Updates

If a stored procedure has changed, the update is simple: drop the old version and loads the new, possibly with help of ALTER PROCEDURE. This tactics is not feasable when a table has changed, because you want to retain the data. Therefore, when DBUPDGEN sees that a .TBL file has changed between from-version and to-version, it generates special code to this end.

SQL Server offers the command ALTER TABLE to add, drop or alter columns as well as constraints. However, DBUPDGEN makes no use of this. Rather the table-updates generated by DBUPDGEN always go the long way: rename the existing table, create the new version, move the data over, move the referencing foreign keys, recreate indexes, triggers and foreign keys anew for the table, and finally drop the old table. While simplicity of implementation is not an ignorable factor, there are several other good reasons for this:

By always running the .TBL files for the table, you know that these are correct.
You get the same column order in all databases, so that you easily can move data with BCP.
You get the columns in the order you want, so that output from SELECT * is easier to read, which is important in debugging situations, not the least when you are debugging problems in a live system.
Not all updates are possible to carry out with ALTER TABLE anyway, so going the long way is a general method that always works.

If you want to use ALTER TABLE neverthesless, see the section Using ALTER TABLE in the Advanced Topics.

It is important to understand that the code that DBUPDGEN generates for a table-update, is a template. There are situations when the template is perfectly usable as-is, and you don't have to change a single thing in it. The most crucial thing to review in a script that DBUPDGEN has generated, is the section where data is moved from the old schema of the table to the new schema. DBUPDGEN generates this SQL code, solely from the old version of the table, and assumes that you want to carry over all columns. This means, if the changes are restricted to the following, the data-move in the template is likely to be usable as-is:

You are only changing a constraint.
You are only adding columns that are nullable, have a default value or the IDENTITY property.
You are changing the column order.

Then again, here are examples of cases where you need to change the generated code:

You have dropped or renamed one or more columns.
You have added a non-nullable column that requires explicit initialization.

The table-updates that DBUPDGEN generates follow a basic theme, but there are some variations due to the conclusions that DBUPDGEN is able to draw from the .TBL file. (Which is the old version of the .TBL file.) Sometimes the generated code is overly complex, and if you feel like simplifying, feel free to do so. You know the data, DBUPDGEN does not. Particularly, DBUPDGEN does not know whether it deals with a 5-row lookup table, or a table with hundreds of millions of rows.

To describe how a generated table-update looks like, let's look at an annotated example that follows below. The numbers in the right margin refers to the comments that follows the code. The table in the example, bankholidays, has a two-column primary key consisting of the columns coucode and bhodate

   pr "#=========== BANKHOLIDAYS ===================";
   {                                                           <-- 1 ---
   my($sql, $tbl, $old_tbl, $old_no_of_rows, $fkey,
      $tmptbl, $at_a_time, $batchcol, $stat);
   $stat = 1;
   $X->{Time_calls} = 1;
   $tbl = 'bankholidays';
   $sql = aba_tblrename($tbl, \$old_tbl);
   abasql($sql);
   abasqlfile('bankholidays.tbl');                             <-- 1 ---
   abasqlfile('bankholidays.tri');                             <-- 2, 3 ---
   #----------- Data shuffling starts here -------------
   $fkey = aba_tblfkey($tbl, $old_tbl, "coucode, bhodate");    <-- 4 ---
   $at_a_time = 25000;                                         <-- 5 ---
   $batchcol = 'tmpid';
   $tmptbl = '#pk_bho';                                        <-- 6 ---
   $sql = <<SQLEND;
   CREATE TABLE $tmptbl(
      tmpid int IDENTITY(1, 1) NOT NULL,
      coucode aba_coucode NOT NULL,
      bhodate aba_date NOT NULL,
      PRIMARY KEY(tmpid))
   SQLEND
   abasqltempobject($sql);

   $sql = <<SQLEND;
   DECLARE \@msg varchar(255)
   INSERT $tmptbl (coucode, bhodate)
      SELECT coucode, bhodate
      FROM $old_tbl
   SELECT \@msg = str(\@\@rowcount) + ' values copied to $tmptbl.'
   RAISERROR('%s', 8, 1, \@msg) WITH NOWAIT
   go
   SQLEND
   abasql($sql);                                               <-- 6 ---

   $sql = <<SQLEND;
   EXEC sp_unbindrule '$tbl.reguser'                           <-- 7 ---
   go
   DECLARE \@tmpid int,                                        <-- 8 ---
           \@err int,
           \@rowc int,
           \@rowctot int,
           \@msg varchar(255),
           \@time char(8),
           \@rowcold int

   SELECT \@err = 0, \@rowctot = 0
   SELECT \@rowcold = COUNT(*) FROM $old_tbl
   SELECT \@msg = 'Copying of ' + ltrim(str(\@rowcold)) +
                  ' rows in $tbl started ' + convert(char(8), getdate(), 8)
   RAISERROR('%s', 8, 1, \@msg) WITH NOWAIT

   SELECT \@tmpid = MIN($batchcol) FROM $tmptbl
   WHILE \@err = 0 AND \@tmpid IS NOT NULL
   BEGIN                                                       <-- 8 ---
      INSERT $tbl (coucode, bhodate, reguser, moddate)         <-- 9 ---
         SELECT old.coucode, old.bhodate, old.reguser, old.moddate
         FROM $old_tbl old
         JOIN $tmptbl tmp ON tmp.coucode = old.coucode
          AND tmp.bhodate = old.bhodate
         WHERE tmp.$batchcol BETWEEN \@tmpid AND \@tmpid + $at_a_time
      SELECT \@err = \@\@error, \@rowc = \@\@rowcount
      SELECT \@rowctot = \@rowctot + \@rowc, \@time = convert(char(8), getdate(), 8)
      SELECT \@msg = str(\@rowctot) + ' row(s) copied. ' + \@time
      RAISERROR('%s', 0, 1, \@msg) WITH NOWAIT

      SELECT \@tmpid = MIN($batchcol) FROM $tmptbl
      WHERE $batchcol > \@tmpid + $at_a_time                       <-- 9 ---
   END

   IF \@err = 0 AND \@rowctot <> \@rowcold                     <-- 10 ---
      RAISERROR('There are %d rows in $old_tbl, but %d rows in "$tbl"!',
                 16, 1, \@rowcold, \@rowctot)                  <-- 10 ---
   SQLEND
   $stat = abasql($sql);                                       <-- 11 ---

   $stat = abasql($fkey) if $stat;                             <-- 11 ---

   $sql = <<SQLEND;                                            <-- 12 ---
   DROP TABLE $tmptbl
   UPDATE STATISTICS bankholidays
   EXEC sp_recompile 'bankholidays'
   EXEC sp_bindrule 'aba_upduser_rule', '$tbl.reguser'
   SQLEND
   $stat *= abasql($sql);                                      <-- 12 ---
   #----------- End of data shuffling -------------

   abasqlfile('bankholidays.ix');                              <-- 13 ---
   $stat *= abasqlfile('bankholidays.fkey');                   <-- 14 ---
   abasql("DROP TABLE $old_tbl") if $stat;                     <-- 15 ---
   $X->{Time_calls} = 0;
   } # End of update of bankholidays

First some explanations for the reader who is not well-acquainted with Perl.

When you see $sql = <<SQLEND; this means that all text which follows up to the next next SQLEND (which must be in the leftmost column) is a string that is assigned into the Perl variable $sql. $sql is then passed to abasql(), which is a routine that simply executes the SQL batch, without performing the tricks of abasqlfile(), save one: abasql() honours the setting of the configuration option -quoterepl, and replaces double quotes with single quotes if this option is active.
The \@ may confuse you, but the \ only serves as escape character to prevent Perl from applying its own interpretation of @. Thus, \@tmpid is the T-SQL variable @tmpid.
When you see things like $old_tbl in the SQL code, this is a reference to the Perl variable with that name; Perl will subsitute the variable with its actual value when evaluating the string.

Now over to the specific comments to the various passages.

First there is some Perl initialization. Next follows a call to aba_tblrename() that generates SQL code when executed renames the table and all its constraints by prepending old_ to their names. That is bankholidays becomes old_bankholidays, the primary-key constraint pk_bho becomes old_pk_bho etc. (This is why, the first part of the update script checks whether there are any objects with names starting with old_ in the database, and terminates if there are.) Once the old version of table has been renamed, the new one is created by a call to abasqlfile().
If there are triggers on the table, DBUPDGEN inserts a call to abasqlfile() to load the triggers here. The idea is that the trigger could perform validations that detects error in the data-move, but sometimes you may prefer to move this line to after the data-move (at point 13):
- The trigger performs cascade updates, which already have been run and cannot be re-run.
- The trigger performs checks to disallow certain values on newly-inserted data, that are perfectly legal when you move over existing data from the old version of the table.
- The trigger is too expensive in terms of performance.
As an alternative to moving the trigger, you can test for this condition in the trigger for the parts that cannot be run in a data-move.
```
   IF object_id('tempdb..#update$script') IS NULL
```
The update script creates this table when it starts up for the sole purpose to permit you to disable parts of triggers.
(Not shown in the example.) If there is an INSERT-file for the table, DBUPDGEN reads this file to find calls to stored procedures, and DBUPDGEN adds a call to abasqlfile() to load any such procedure at this point, so that the INSERT-file, added at point 12, can be loaded successfully. DBUPDGEN assumes that the procedure resides in file with the same name as the procedure and has the extension .SP. If this for some reason is not the case, you need to edit the call to abasqlfile().
The routine aba_tblfkey() returns SQL code that moves the foreign keys referencing the old table to refer to the new table. This SQL code is executed at point 11. Normally, you don't need to bother about this call, but there are few situations when you need to:
- You are changing in column names in the primary key. The third argument a string with the PK columns in the old table definition
- The table has both a PRIMARY KEY and a UNIQUE constraint, and there are foreign-key references to both. In this case, change aba_tblfkey(), to aba_tblfkey2() which only takes two parameters, $tbl and $old_tbl. Beware that aba_tblfkey2() does not transfer ON DELETE/UPDATE CASCADE, which aba_tblfkey() does. Also, note that aba_tblfkey2() will not work if you have renamed any key columns.
- The table does not have any PRIMARY KEY constraint. In this case, just remove the call, as well as the call abasql($fkey) at point 11. There are no referencing foreign keys to move anyway.
When the code is run later on, the actual statements to move the foreign keys are printed to the log. This can be helpful is a foreign-key move unexpectly fails.
The variable $at_a_time determines how many rows, approxamitely, that will be affected in one INSERT statement in the INSERT-loop. The variable $batchcol designates which column which is to be the control column for the loop. More on this later.
This sequence of statements creates a temp table with all values of the primary key in the table. The temp table has an IDENTITY column, and DBUPDGEN uses this key to move portions of the table in an INSERT-loop. DBUPDGEN does not create such a temp table if it believes that the data-move can be performed without it. Here are the cases when DBUPDGEN does not create a temp table:
- The table does not have a primary key at all; in this case the entire table is copied without a loop, assuming that the table is small.
- The table has a one-column key, and this key is short: tinyint, smallint, varchar(5)/char(5) or shorter. In this case too, the table is copied without a loop.
- The first column in the primary key is numeric. In this case, that key is used as a counter. This often yields good results, but if the values are sparse, some iterations in the INSERT-loop may comprise very few rows (but at least one row is copied each time).
For big tables, you often need to review this step to speed up the data-move. It is often a good idea to copy the rows in the order defined by the clustered index on the table. See below how $batchcol is useful in this case.
The temp table is created through a special routine, abasqltempobject(). This is a special version of abasqlfile() for loading temp tables. It performs most of parts of the AbaPerls file-loading process, most importantly it replaces user-defined datatypes with their definitions, but it skips actions that are not appropriate for temp tables, for instance saving information to abasysobjects.
To print a diagnostic message, the code uses RAISERROR WITH NOWAIT, to flush the message directly to the log. The messsage has severity level 8, so if you use LISTERRS with a second parameter of 8, you will see this message, which can be good to find out if the scripts does what you expect it to.
If a column in the table has a datatype that ends in _upduser, DBUPDGEN assumes that this column has a rule that requires the value of the column be the name of the current user. Since we want to keep the existing value, DBUPDGEN inserts a call to sp_unbindrule for this column. In the same vein, not shown in this example, if the table has an IDENTITY column, DBUPDGEN inserts a SET IDENTITY_INSERT ON for the table at this point.
This is the initialization of the INSERT-loop. As the start value for the iteration, DBUPDGEN uses the smallest value in the temp table, or the smallest value of the primary key in the old version of the table when there is no temp table. DBUPDGEN also prints a message of how many rows there are to copy.
Again, the script uses RAISERROR WITH NOWAIT to force the message to the log directly, and the severith level 8 to make the whole data-move section to be included in the output, if you run LISTERRS with level 8 (9 is the default for update scripts).
This is the data-move itself. Here you often need to supplement the generated code, depending on which changes you have made to the table. DBUPDGEN does not examine which changes you have made, but bases the copynig solely from the old definition of the table.
   As you can see, the INSERT-loop copies a batch a time from the old table; the rows in the batch is determined by an interval over the numeric key in the temp table as here, or by the numeric key in the old version of the table. The interval of the key is determined by $at_a_time, see below for a further discussion.
   After the INSERT statement, there are checks for error status, and the code prints a message on how many rows were copied, and when this INSERT statement completed, so you can see in the log how fast (or slow) the INSERT-loop proceedes. (Note, though, that when running a long-running data move, SQL Server does not flush to the client immediately, so the log is only updated occasionally while a data-move is executing.)
   Finally, @tmpid is advanced to the next interval to perform the copy for.
When INSERT-loop has completed, there is a check that the number of rows are the same in the old table as in the new table. It may be the case that you change the table in a way that you consiously add or remove rows from the table. In this case you should remove the check or modify it to agree with the changes you are making.
Here is where the data-move is actually executed. The Perl variable $stat will be set to 1 if the data-move completes without error, else 0. Next, if the data-move was successful, the code to move the referencing foreign keys in $fkey (see step 4) is executed. The foreign keys are not moved in case of an error in the data-move, since in such case the foreign-key move usually fails too and litters the log.
This code completes the data-move. The temp table is dropped, we update statistics for the new table, the rule for any _upduser is restored (the rule is assumed to have the same name as the type with _rule tacked on.) If the table has an IDENTITY column, you see a SET IDENTITY_INSERT OFF here.
Here is where DBUPDGEN inserts loading of .INS, .IX and .FKEY files. The script does not check for the return status when the index file is loaded, as an error for an index file is not fatal. On the other hand, errors when loading the foreign keys for the table are probably an indication that the data-move was not correctly implemented, so errors here are cared about. The notation $stat *= means that if the return value of abasqlfile() is multiplied with $stat. That is, if $stat is 0 because of a failed data-move, it retains this value.
Finally, the old version of the table is dropped, but only if $stat is > 0. Thus, if something goes wrong, the old version of table is left around so that you can investigate the problem. This will also exhibit as a warning in the end of the log of the update script.

As you can see there are no ;; in this code, thus everything will remain if DBUPDGEN regenerates the script. Occasionally you may want to delete the data-move, because you don't want to preserve old data at all. Then you can delete everything between these two lines, but preserve the lines themselves.

   #----------- Data shuffling starts here -------------
   #----------- End of data shuffling -------------

If you delete these lines, DBUPDGEN will regenerate the data-move.

We need to say some more on $at_a_time and $batchcol. When DBUPDGEN has generated a temp table, $at_a_time is also likely to be number of rows copied each iteration of the loop. But when the iterator is the first column in the primary key of the table itself, the number of rows copied per iteration may be both greater and smaller than $at_a_time. It will be smaller if there are gaps in the values. If the gaps are many and regular, you should probably increase $at_a_time in the assignment in step 4. For instance, if the numeric key contains a check digit, only at most one value per decade will appear, thus 500000 may be appropriate value. The case when number of rows copied exceeds $at_a_time, is when the table has a multi-column primary key, and there thus are multiple values per the column which is the control variable for the loop. DBUPDGEN handles this, by setting $at_a_time to lower value. 50000 is the normal value for a one-column table, but DBUPDGEN divides this with the number of keys.

What is actually a good value for $at_a_time depends on available disk space etc. The idea is to avoid of cramming too much into the transaction log at one time, but too small iteration intervals may result in unacceptable execution times. It may certainly be beneficial to increase $at_a_time, even if your keys are dense.

As noted above, the best way to run the INSERT-loop is use the clustered index, unfortunately DBUPDGEN does not (yet) generate the loop this way by default. For a small table, it may not matter, for a table with millions of rows it can make huge difference in execution time to use the clustered index. If DBUPDGEN went for a non-clustered primary key, you can change $batchcol to hold the value of the first column in the clustered index, and also adapt $at_a_time accordingly. Note that if $batchcol is of a different data type, for instance datetime, you also have change the data type of the variable @tmpid in the INSERT-loop. You may also have to review the expression at point 9 in the loop.

Advanced Topics

In this section we refer to some Perl constructs that the reader may not be acquainted with. In interest of brevity, I am explaining them very briefely or not at all. Please see suitable Perl documentation, if you need to know any details.

Raising Messages and Errors

You can freely use RAISERROR and PRINT to produce errors, warning and informational messages. See for instance point 10, there is the example update above. There is one thing to keep in mind: do not specify severity level 10 if you want to emit a warning. SQL Server changes this to severity level 0, so it's the same as a PRINT statement. This matters if you want to use LISTERRS to scan the log. LISTERRS looks for the severity level of the messages, but if the severity level is 0, AbaPerls takes this as an informational message, and does not print the severity level, so LISTERRS cannot find it. Use 9 instead, and LISTERRS includes messages with level 9 by default for update scripts. You could also use 8, if you only want to see the messages when you make a closer inspection.

Final Epilogue

If you add a Perl sub by the name of final_epilogue, the update script will call this procedure. This can be useful when you have an update script that encompasses several subsystems, and you need to perform some cleanup at the end of the script and which can not be put into the epilogue of a certain subsystem. See below under Dropping a Data Type, for cases where you may need this.

Datatype Changes

If a datatype changes between from-version and to-version this is what the upgrade script needs to do:

Rename the existing type by prefixing old_ to the type name leave room for the new type.
Run the .TYP file to load the new definition of the type.
Run a table-update for all referencing tables.
Reload all views with columns of the type.
Reload all stored procedures and user-defined functions with parameters of this type.
Reload all stored procedures, triggers and user-defined functions with temp tables or table variables that use this type. Recall that AbaPerls replaces user-defined types with their definition in temp tables and table variables.
Drop the old definition of the type (see below under Dropping a Data Type.)

DBUPDGEN does not generate this for you! Type changes simply occur too rarely (at least in my world) to be worth the effort. Besides, if a .TYP file has changed it could be that only the rule or the default for the type has changed, in which case loading the .TYP file is all that needs to be done. No need to reload tables etc.

Thus, if you have changed a datatype, you need take care of the above yourself. The best approach for the tables is probably to make a dummy check-in, and then let DBUPDGEN pick up the tables. As for the stored procedures, functions and views, you could do the same, or add them manually to the update script.

To rename the type you insert a call to sp_rename in the TYPE section like this:

   pr "#=========== TYPE ===================";
   abasql("EXEC sp_rename aba_evtcode, old_aba_evtcode")
   ;;abasqlfile('aba_evtcode.typ');

If the type is used for a primary key in one table and foreign keys in one or more table, you cannot move foreign keys for the main table directly, as foreign-key constraints requires the columns to have the same type. If you simply skip the fkey-move, and reload all referencing tables, you should be table to drop the old-table at the end of the script, either in the epilogue of the subsystem, or in a final epilogue of the script.

Using ALTER TABLE

If you want to implement a table-update with ALTER TABLE to save execution time, your table-update should look as in this example:

   pr "#=========== ACCOUNTVALUES ===================";
   {
   my($sql, $tbl, $stat);
   $stat = 1;
   $X->{Time_calls} = 1;
   $tbl = 'accountvalues';
   $sql = aba_tblrename($tbl, \$old_tbl);
   #abasql($sql);                                        <-- 1 ---
   abasqlfile('accountvalues.tbl', undef, undef, 1);     <-- 2 ---
   #----------- Data shuffling starts here -------------
   abasql(<<SQLEND);                                     <-- 3 ---
   ALTER TABLE accountvalues ADD accday_owncapital float NULL
   ALTER TABLE accountvalues ADD settleday_owncapital float NULL
   SQLEND
   #----------- End of data shuffling -------------

   abasqlfile('accountvalues.ix');
   $stat *= abasqlfile('accountvalues.fkey');
   $X->{Time_calls} = 0;
   } # End of update of accountvalues

Comment the call to abasql() to run the table rename. Don't comment the code to set up the table-rename, as DBUPDGEN then will not find that part and regenerate it.
Add a fourth parameter in the call to abasqlfile() with a value of 1. This parameter $onlymetadata instructs AbaPerls to skip loadin the file in SQL Server but update abasysobjects with the new version information for the file.
Rip out all generated code in the data-shuffling section in favour of your ALTER TABLE commands.

It goes without saying that it is your responsibility to make sure that you have changed the .TBL file so it agrees with the result of the ALTER TABLE commands.

Dropping a Data Type

When you drop a data type, you may not be able to drop it in OBSOLETE-FILES, because there are views, stored procedures or functions that refers to the type in columns/parameters (local variables does not matter), and the views/procedures/functions appear later in the update script. In this case, you may prefer to move the call to abasqlfile() to drop the type to the EPILOGUE. Then you need to copy the preamble and the conclusion of the OBSOLETE-FILES to the EPILOGUE.

In the case that the type is referenced by other subsystems, you could build an update script that covers all subsystems, and use a final epilogue where you drop the type. As above you would copy parts of OBSOLETE-FILES, but you would have to set the subsystem, and the paths accordingly.

Checking if a Table-Update Has Already Been Run in a Database

Sometimes an update script includes a table-update that may already have been carried out in some databases in which the script is to be run. This could be because that the change was also checked into an earlier version-project as well. In this case you may not want to run the table-update anew, since you could lose data, or get run-time errors when the old table does not look like the script expects it to. To make this situation easier to handle, AbaPerls offers the utility function aba_check_column(). This function takes five parameters:

`$tbl`	Name of table, mandatory.
`$col`	Name of column, mandatory.
`$usertype`	Name of user-defined type, for instance ap_subsystem.
`$phystype`	Name of physical datatype, for instance float, varchar(16).
`$property`	A property accepted by the SQL function objectproperty(), and for which the value may be 0 or 1.

The function returns 1 if the column $col exists in the table $tbl. If you have specified $usertype or $phystype, then $col must have this datatype for aba_check_column() to return 1. You can also pass a property for aba_check_column() to check for. Example of such properties are AllowsNull and IsIdentity.

The idea is that you can conclude whether a table-update already has been run, by checking a column affected by the update.

The simplest way to use this check is simply to insert a test at point 1 (see the example above) in the table-update:

   pr "#=========== BANKHOLIDAYS ===================";
   if (not aba_check_column('bankholidays', 'newcol')) {
   my($tbl, $old_tbl, $old_no_of_rows, $tmptbl, $fkey, $sql,
   ....
   }   # End of update of bankholidays
   else {
      abasqlfile('bankholidays.tri');
      abasqlfile('bankholidays.ix');
      abasqlfile('bankholidays.fkey');
   }

The purpose of the else branch is that by reloading the trigger, index and foreign-key files, you know that you are loading the definitions that goes along with the version the update script runs to; these files may have been changed since you ran the update script.

aba_check_column always return 0 when -noexec is in effect, as in this case, you always want all files to be extracted.

Getting Data Back from SQL Server to the Update Script

You have seen that you can execute a loose batch of SQL statements with the routine abasql() and abasql() will return 0 if the batch completes without errors. If the batch produces a result set, the result set will be printed in a very simple way.

But what if you need to get data back from the database to use in the Perl script? Here is an example:

  use vars qw($ost_ityid, $hasactive);
  abasqlfile('zz_ins_from_postype_t_sp.sp');
  if ($opt_exec) {
     my $mssql = $X->{MSSQL_handle};
     $mssql->sql_sp('zz_ins_from_postype_t_sp', [\$ost_ityid, \$hasactive]);
     if ($hasactive) {
        print $LOG "Oops! There are active postype-t in this database!\n";
     }
   }

The example calls a stored procedure that returns data in two output parameters. The data is placed in the parameters $ost_ityid and $hasactive. They are declared with use vars, as the update script may need to refer to them further down the track.

The check for $opt_exec is an important matter, so we don't try to call the procedure when we are running with -noexec.

The line

   my $mssql = $X->{MSSQL_handle};

retrieves a database handle of the type Win32::SqlServer, which is a general library for accessing SQL Server from Perl. We then use the function sql_sp() to call the procedure. For further details on Win32::SqlServer, please see the documentation for this library which you can find at this external link. (You should have this documentation at your site as well, since Win32::SqlServer is a prerequisite for running AbaPerls.) Win32::SqlServer offers many ways to get data back, but I leave to the reader to explore those possibilities on your own. Here is one more example, which I leave uncommented:

   my $has_i2c = 0;
   if ($opt_exec) {
      my $mssql = $X->{MSSQL_handle};
      $has_i2c = $mssql->sql_one(<<SQLEND, Win32::SqlServer::SCALAR);
         SELECT COUNT(*)
         FROM   syscolumns
         WHERE  id = object_id('$old_tbl')
           AND  name = 'i2ccode'
   SQLEND
   }
   if ($has_i2c) {
      # Do something

Note: you could do all sorts of wild things once you have gotten hold of the $mssql object, but don't expect everything you do be supported by AbaPerls. Handle with care.

Printing Messages to the Log

In the previous section you saw a reference to a variable $LOG. This is the log that the update script produces. You can print to this log as shown in the example above. If you want to print an error message, so that it is displayed by LISTERRS, then format beginning of the message as an SQL message, for instance:

   Msg 0, Severity 11, dbupdate-7.10.pl
   There's too much coffee in this database to be healthy.

Suppressing the name-clash check

Normally, AbaPerls does not permit you to create an object with a name that is already in use by another subsystem. This is a protection against unintended name clashes. However, occassionally you may want to move objects from one subsystem to another, in which case you may need to turn of this check. This is the way to do it:

   $X->{Subsys_check_disabled} = 1;
   abasqlfile('filetomove1.sp');
   abasqlfile('filetomove2.sp');
   ...
   $X->{Subsys_check_disabled} = 0;

Forcing the Install of a Lower Version in a Production Database

If the database has been labelled as a production database with the config-option -envrionment, an update script will normally skip to load a file, if the version that already is in the database has a higher version number than the version that update script would load. You can override this by setting the AbaPerls property Low_version_force:

   $X->{Low_version_force} = 1;

Set it back to 0, if you only want to do this for some files.

Checking the SQL Server Version

Do like this:

   if (AbaPerls::VersionCmp::version_ge($X->{SQL_version}, 8))

The value of $X->{SQL_version} is the same as the Preppis macro &SQL_version, and the same comparison rules apply as do for the macro. There are no predefined &SQL8 etc available, though. And you cannot use >, >= etc for comparison, but you have to use version_gt(), version_ge() etc.

The $X Object

In the previous sections you have seen $X appear in a lot of places. $X is declared in the first part of the update script, and is an AbaPerls::AbaSql object. As you might guess, $X gives you access to the very core of AbaPerls. And update script is codewise just as much part of AbaPerls as are DBBUILD and ABASQL.

That does not mean that any usage of $X is permissible. Consider usage which you have not seen described on this page, or elsewhere in the AbaPerls documentation, as undocumented, even if it appears in a script that DBUPDGEN has