The Life-Cycle of a Database

This page gives an overview on how DBBUILD, ABASQL and the update scripts generated by DBUPDGEN operate on a database during its life. Please see pages for the concerned commands and concepts, mentioned tables and stored procedures for full details.

Contents:
   Defining the System and Creating the Database
   Adding Subsystems
   Changing Configuration Options
   Loading Single Objects
   Updating Your Database
   Removing a Subsystem
   Upgrading to a New Version of SQL Server
   Auditing Your Database
      Audit on object level
      Audit on subsystem level
      General disclaimer

Defining the System and Creating the Database

When you plan to start a new database application, the first step is to set up the system-definition file where you list the subsystem you plan to have. There is no requirement to have a sysdef-file, but it can make things easier for developers, particularly if you are using TFS, since this permits AbaPerls to automatically determine the subsystem from the current Windows directory.

If you plan to have several databases in your application, you should add the subsystems for all database to the same sysdef-file.

As your system evolves, you can add more subsystems to the sysdef-file. Be careful to add them in build-order.

There is no support for creating the database as such in AbaPerls, but you use the regular CREATE DATABASE command or use the GUI in SQL Server Management Studio.

Adding Subsystems

Once you have created a database, the next step is to add one or more subsystems with DBBUILD. To fully use the features of AbaPerls, the first subsystem you add should be ABAPERLS, which contains the AbaPerls system tables and associated stored procedures. You can still use the AbaPerls tools, even if the ABAPERLS subsystem is not present in the database, but some features will not be available. The life-cycle detailed here presumes that the AbaPerls system tables are present from the very start. (See however the very end of this page about abainstallinfo, a table that AbaPerls creates when you do not install ABAPERLS.)

To bulid the a database you typically use a config-file where you specify the subsystems, and configuration options for the database, either global or on subsystem level. If you use a sysdef-file, the subsystems are drawn from this file. If you later need to add another subsystem, you add it to the config-file and run DBBUILD anew. DBBUILD will ignore the subsystems in the config-file that already are in the database, and build only the new subsystem.

An alternative method is to use DBBUILD with the ‑VC and ‑subsystem switches and add the subsystems one by one. When you do this, you can still save the configuration options in the database, by specifying them on the command line. Keep in mind, that the precedence rules for configuration options say that these options will always apply to the database as a whole, and never per individual subsystem.

How would you divide your database into subsystems, you may ask? In AbaSec, we introduced subsystems because we have components that are only relevant to some customers, and our database had grown too large in number of SQL objects to make it convenient to always ship everything. This should give you a clue. If your database only has some fifty-some tables, or you always install all tables and procedures (for instance, because your database is specific to solve the needs of your enterprise), then you probably only need to have two subsystems: ABAPERLS and your own database. And in this case, maintaining a config-file is probably not worth the effort.

(You may wonder how subsystems are difference from schemas in SQL Server, and the answer is that they are to a great extent the same idea. Subsystems were added in AbaPerls long before schemas became useful in SQL Server. Alas, AbaPerls does not really understand schemas in SQL Server, but assumes that you always everything in dbo.)

Changing Configuration Options

If you want to change the settings for a database, either on global level or for a specific subsystem, there are basically two ways to go, depending on whether you want existing objects to be affected or not:

To change existing objects: Use DBBUILD with the ‑rebuild switch. This will reload all objects, excepting types and tables, in the subsystems specified in the config-file. The settings saved in the database will be scrapped in favour of those in the config-file. You can also use ‑VC and ‑subsystem in place of a config-file, which will rebuild that particular subsystem and replace its settings, but leave the settings on global level unchanged.

Without affect existing objects: You can update the table abaconfigsettings directly. You can use the SP ap_cos_insert_sp to add a setting not previously defined. Specify NULL for the parameter @subsystem to add a global setting. One case when this is useful is when you plan to start to use a new macro in some of the files, and this macro should be set for this database. Since this macro has not been in use before, there is no need to run DBBUILD ‑rebuild. (On the other hand, if you later decide to change the setting of the macro for a database, then you should probably use DBBUILD ‑rebuild.)

Loading Single Objects

When you want to load a single object you use ABASQL. ABASQL reads the settings from the database, so if you used ‑crypt when you created the database, the objects that you change or add with ABASQL will be automatically obfuscated. ABASQL permits you to specify which subsystem the object belongs to, and will then use settings for that subsystem.

Note however, that ABASQL is mainly a tool for developers to be used in development databases. That is, in a perfectly maintained production database, you would only load SQL objects with DBBUILD and the update scripts generated by DBUPDGEN. In real life, every now and then, the need for an immediate fix arises, and you may even need to test various variants of the fix on the production data (because that's the only place where you can reproduce the problem prompting the fix). Thus, here you would need to use ABASQL.

Updating Your Database

The regular way to update the database with new/changed tables and stored procedures is to build an update script with DBUPDGEN. The big advantage of this is you can keep track of what you have updated in the database and you have not. In the table abasubsystems AbaPerls keeps track of the version-control label for the subsystem.

DBUPDGEN works by comparing two versions in the version-control system against each other, and it includes all changed files in the update script, and, in the case of tables, files that needs to be reloaded when the table is rebuilt. When you run the script, the script updates the database with the current version of the changed scripts, and the script also updates the labels for the subsystem in abasubsystems.

An update script updates the subsystem from one version to another. To make your updates safer, the update script verifies that it fits with the subsystem. Say that you have a database where a certain subsystem is at version L5.30.1200, and you have an update script that updates the subsystem from L5.30.1300 to L5.30.1500. This update script will refuse to run in this database, because you first need to run a script that updates the subsystem to L5.30.1300. On the other hand, if the update script would find that the subsystem is already on L5.30.1500, the update script would tell you that there is no need to run the script again.

When you generate an update script, you can start from two versions of a config-file and get a script that comprises many subsystems. When you run the script, the script examines which subsystems that are present in the database and skips the parts of that pertains to the absent subsystems as well as subsystems that are already up to date.

Occasionally you may want to ship a small handful of files without changing the labels of the subsystem. You can do this with ABASQL, but you can also use DBUPDGEN with the ‑patch option. There are several advantages with this: 1) You get a single Perl script that has all the changes. 2) The script verifies its start label fits with the database. 3) The execution of the scripts is logged to abainstallhistory, more about this table in the section on auditing below.

Just like ABASQL, the update script reads the configuration options from the database, so when you run a script on a database, you never have to remember whether this was a database you should obfuscate stored procedures, or have a certain macro set. But you can never change the configuration options with an update script and nor can you add any. To that end you use DBBUILD.

Removing a Subsystem

The way to remove a subsystem is to generate an update script with DBUPDGEN from a config-file. The subsystem to be removed should be present in the earlier version of the config-file, but not in the later. DBUPDGEN will then include all objects of this subsystem in the update script, but for deletion only. The update script will not only drop all objects, but also deactivate the subsystem in abasubsystems.

Upgrading to a New Version of SQL Server

When you upgrade you database to a new major version of SQL Server, you should always rebuild the ABAPERLS subsystem with DBBUILD ‑rebuild. This is because the stored procedures in AbaPerls make generous use of conditional compilation depending on SQL Server version. Thus, you need to rebuild ABAPERLS to get full support for new features in SQL Server.

Auditing Your Database

The AbaPerls system tables provide information that permits you to see what subsystems and objects that are installed in the database through AbaPerls. Obviously, AbaPerls cannot track objects that are loaded to the database outside of AbaPerls, for instance through SQL Server Management Studio. However, you can find out whether such changes have occurred.

Audit on object level

The table abasysobjects holds all objects that currently are loaded in the database according to AbaPerls. The table abahistsysobjects holds the complete installation history for each object. The stored procedure ap_sob_report_suspects_sp reports all deviations from the normal installation procedures. This procedure list objects in the database not in abasysobjects and vice versa. It also lists objects for which the entry in abasysobjects does not reflect the most recent CREATE/ALTER of the object in the database; that is, objects that have been loaded outside AbaPerls. You can also see objects that have been loaded directly from disc without a proper version-control label.

Audit on subsystem level

The table abainstallhistory includes all executions of DBBUILD and update scripts generated by DBUPDGEN (both regular update scripts and patch scripts). For all normal executions there is both a START and a STOP row. The entries are per subsystem, so if you run DBBUILD in a new database with a config-file that includes five subsystems you get ten rows in abainstallhistory.

This functionality is available to you, even if you do not install the ABAPERLS subsystem. In this case AbaPerls silently creates the table abainstallinfo to which DBBUILD and the update scripts generated by DBUPDGEN write START and STOP rows. If you later install the ABAPERLS subsystem, AbaPerls automatically converts abainstallinfo to abainstallhistory.

General disclaimer

The auditing features of AbaPerls are intended to catch cases where developers or administrators bypass the regular procedures because of ignorance, laziness or uttermost emergency. A malicious user who has the rights to create objects will of course have the rights to manipulate the AbaPerls system tables and be able to cover his tracks.