AbaPerls is a collection of tools written in Perl that started its life in 1996 to support the development and deployment of the a product known as AbaSec that I worked with at the time – and still do for that matter.
The main focus for AbaPerls is development, configuration-management and installation of MS SQL Server databases that are maintained in a version-control system. The supported version-control systems are Team Foundation Server and Visual SourceSafe. AbaPerls also includes tools for searching and replacing in version-control that are useful for any user, even if you do not use SQL Server. While designed with the aim to support the development of a specific product, AbaPerls is not hard-wired to AbaSec, but can be used for other development efforts for SQL Server. AbaPerls aims to be a product, but it will have to be admitted that there are some rough edges in that regard. It was designed as an in-house development tool, with the toolsmith in reach. A primary aim in the development of AbaPerls has always been to keep down development time, the development time of AbaPerls that is. So in places AbaPerls makes assumptions or imposes limitations that fit well with how we work with AbaSec, but may appear utterly confusing or inconvenient to you. (Hey, even my colleagues thought some of it was funny. They got used to it...)
And just a word of warning if you are expecting nice-looking Windows dialogues: The AbaPerls tools are all command-line tools only. There is no GUI.
AbaSec developers should be aware of that AbaPerls very much is a moving target, and tools are changed or added every now and then.
The prime developer component of AbaPerls is the load tool ABASQL. ABASQL is a wrapper on the AbaPerls file-loading process, which goes far beyond what you get through SQL Server Management Studio. Here is a sample of what you get:
‑crypt
, that will insert WITH ENCRYPTION into the code.Typically you invoke ABASQL from a external text editor that permits you execute a command-line tool from the editor. In the AbaSec group we use TextPad and Visual Studio.
To build an empty database AbaPerls provides the tool DBBUILD. DBBUILD finds the SQL files in a number of version-control directories and loads the files into database in a well-defined order using the AbaPerls file-loading process. That is, everything you get with ABASQL, you get with DBBUILD. Since the log from a database build can become huge, AbaPerls offers LISTERRS that extracts the interesting part of the log, that is, the errors and warnings.
DBUPDGEN is a tool that compares two labels for a directory tree in the version-control system, and from the differences it generates an update script, that will update the database from one label to the other. These upgrade scripts use the AbaPerls file-loading process, so again the full power of ABASQL is available.
AbaPerls is strict on mapping object types to certain file extensions, so that table definitions are in .tbl files, stored procedures in .sp files and so on. AbaPerls is also strict that the name of the file must match the name of the object. Furthermore, there is the AbaPerls SQL directory structure, which says that a certain file extension should be in a certain directory.
Once you have decided to use AbaPerls, all developers should use ABASQL load database objects. In order to enforce this, AbaPerls install a DDL trigger to disallow objects from being modified outside of AbaPerls, for instance from SQL Server Management Studio. Sometimes, though, it may be necessary in a production environment to deploy an urgent fix, and the DDL trigger can be bypassed, in which case the action is still logged.
The product I work with consists of several modules, some large and some small, and each costumer typically has their configuration. Unless there is some special reason, all modules reside in the same database, as high-level modules typically refer to tables and stored procedures in the base modules.
These modules are in AbaPerls called subsystems. A subsystem is set of database objects – types, tables, stored procedures etc – that you build or update together. A database can consist of one or more subsystems. You define your system (which can be spread over several databases) in a system-definition file (or sysdef-file for short) which lists your subsystems and where they are located in version-control. You define the subsystem configuration for a specific database in a configuration file (or config-file for short) which defines which subsystems that are to be included in a specific database. The config-file typically inherits from the sysdef-file and adds configuration options, either on database level or on subsystem level.
Provided that you include the ABAPERLS subsystem, which is the database part of AbaPerls and includes the AbaPerls system tables in the database, DBBUILD saves the configuration options in the database. This permits ABASQL and the update scripts generated by DBUPDGEN to act as if these options had been specified on the command line. For instance, in the config-file you can specify that all stored procedures, triggers and views are to be obfuscated, whereupon ABASQL will automatically add WITH ENCRYPTION when it loads the objects.
DBUPDGEN can also work with a config-file and generate an update script for many subsystems. When you run the update script, it checks the database for available subsystems, and ignores the subsystems in the script not present in the database.
Sometimes you may want to see what a config-file maps to. To this end there is the tool LISTCONFIG.
In many databases, there are tables into which you need load with data, before the application can start working. AbaPerls includes the tool INSFILGEN which reads an Excel workbook and from this generates an INSERT-file.
AbaPerls includes three tools to search and make automatic edits for files under version control, be that TFS or SourceSafe:
These tools are used to enforce AbaPerls's label rules in the version control system, and they are not very useful on their own.
AbaPerls has its own set of tables to keep track of versions of subsystems and objects loaded in the database. These tables, and the stored procedures that goes with them, constitute a subsystem of its own, called ABAPERLS. The use of these tables is not mandatory; you need to explicitly include the ABAPERLS subsystem in your database to make use of them. But without them, you will be not be using the full power of AbaPerls. For instance, in a database which has the ABAPERLS subsystem installed, you can use the stored procedure ap_sob_report_suspects_sp to see if anyone has loaded files outside the regular installation scripts.
AbaPerls runs on Windows and supports SQL Server 2005 and later, Visual SourceSafe 2005 and Team Foundation Server 2010 or later. You need Perl to run AbaPerls, but Perl is is bundled with the AbaPerls download. For further information, see Downloading and installing AbaPerls.
Over the years, more and more features and concepts have been added to AbaPerls, so it does take more than a coffee break to learn it. The good news is that you don't have to learn everything to start using some of the AbaPerls tools. Here I try to outline three different "tracks" to get started with AbaPerls.
The tools SSGREP, SSREPLACE and VCDBLOAD are powerful enough to themselves be interest for shops that do not use MS SQL Server, or are not interested in the developer and CM support offered by AbaPerls but who use SourceSafe or Team Foundation Server. For this track you may want to read:
This track is for SQL developers who write stored procedures and such, but do not build or update databases. They will in many cases use only ABASQL. Occasionally they may use SSGREP, SSREPLACE, INSFILGEN and RUNSPS.
The recommended documents for this track are:
This far, you are ready to use ABASQL. Later on, you may also want to read these documents:
This track includes the full set of AbaPerls tools and concepts. Even a DBA or a CM person should probably start with the plain-developer track, and play a little with ABASQL before moving on.
Here is a complete suggestion of a reading order for a DBA or a CM-person which mainly goes into topics first, before arriving at the command descriptions.
This far, this is mainly an extended developer track. We now move to more central topics for a DBA/CM-person.
As for the remaining commands and topics, I have not listed them in any reading-order; explore them at your own pace.
This section makes an attempt to list resitrctions with AbaPerls, that is unsupported features etc. It is not likely to be complete. When it doubt, test to see if it works or not.
There are many features in SQL Server for which AbaPerls does not have any direct support. For some object types that does not have a dedicate file type, like users, certificates etc, you can easily work around this by defining them in .sql or .postsql files. In fact that is more or less the intention. For other features, you are more or less tied to what AbaPerls supports. Here is a list of such hard restrictions, not likely to be exhaustive:
Since AbaPerls aims at building and maintaining a database, there is not much support for server-level objects like extended events. Rather AbaPerls stays away from the server level, with the exception of creating keys and logins to grant modules server-level permission.
Also beware that AbaPerls may be sensitive to formatting or be confused by what is actually legal syntax, as discussed in the section Pre-SQL Analysis: Creating and recreating objects on the file-load page.
Generally AbaPerls supports Unicode for the contents of files as well as the in the names of tiles and objects. However, observe the following.
AbaPerls makes a half-hearted attempt to support identifiers quoted with
""
or []
, but more work needs to be done
before AbaPerls can be said to fully support this. One known restriction is that names including a single quote, e.g. [Jane's table]
do not
work and yields an error message Overall, testing has not been extensive in this area.
Note also that by default, AbaPerls replaces all occurrences of double quotes
as string delimiter with single quotes, so if you wish to use double quotes to
delimit identifiers (which is ANSI compatible in difference to []
which is SQL
Server-specific), you must use the configuration option
‑noquoterepl
.
Through the years, several of my co-workers have provided valuable input and ideas, and I particularly like to mention Leif Jettman and Jan Afrell who have suffered from bugs due to quick check-ins, less ideal design decisions etc.
And of course, due credit should go to Larry Wall who originally designed
Perl. Without this powerful language AbaPerls would never have come into existence.
I would also like to thank Jan Dubois who wrote
Perl is free software, available under the Perl Artistic Licensce.
Erland Sommarskog, esquel@sommarskog.se.
Copyright © 1996-2017,
Erland Sommarskog SQL‑Konsult AB.
All rights reserved. AbaPerls is available under
Perl Artistic License
This page last updated 17-06-24 14:22