Introduction to AbaPerls

Background

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.

Feature Overview

SQL Development

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:

• The preprocessor, Preppis, which is loosely modelled on the C processor, permitting you to use conditional compilation and include-files.
• ABASQL checks that you are not referring to non-existing objects, thereby neutralising deferred named resolution, the biggest misfeature of SQL Server.
• ABASQL checks your stored procedure calls, so that you don't omit mandatory parameters, specify parameters that do not exist and errors of that kind.
• ABASQL relieves you from having to include IF EXISTS ... DROP in your files, as well as the need to include explicit GRANT statements.
• If you want to obfuscate code that you store in customer databases, ABASQL has a switch, ‑crypt, that will insert WITH ENCRYPTION into the code.
• In temp tables and table variables, AbaPerls replaces user-defined types with their definitions, so you don't need to have any types in tempdb. Likewise AbaPerls adds COLLATE clauses to character columns in temp tables and table variables, which permits you to mix collations on a server without thinking of it.
• AbaPerls makes it easy for you to grant permissions through stored procedures that are not grantable through ownership chaining, for instance permissions for dynamic SQL and BULK INSERT.
• AbaPerls supports using the CLR in SQL Server. You can either provide an assembly built elsewhere, or feed AbaPerls source code written in C# or VB .NET.

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.

Building and Updating Databases

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.

A Module-Based Database

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.

Preparing Pre-Loaded Tables

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.

Search and Replace in Version-Control

AbaPerls includes three tools to search and make automatic edits for files under version control, be that TFS or SourceSafe:

• SSGREP searches the version-control repository for one or more regular expressions (Perl syntax). For commonly used file types (SQL, C/C++/C# and VB), AbaPerls knows how separate code from comments, and by default SSGREP ignore comments when searching, which often is very helpful to reduce the number of false hits. SSGREP can output the result in plain text or in HTML.
• SSREPLACE takes a list of substitutions (regexps in Perl syntax) and searches the version-control system. It checks out matching files with matches and edits them. However, it does not check in the files automatically. Like SSGREP, SSREPLACE by default operate only outside comments.
• VCDBLOAD loads all source files under version-control into an SQL Server database and creates a full-text index on the data, to permit for fast searches in a large volume of source code.

Other SQL Utilities

• DOBCP performs BCP in or out of all tables in a database.
• RUNSPS reads an Excel file and runs the stored procedures with parameters in the file. A handy test tool.
• TBLCNT counts the rows in all tables. This tool is handy when moving data, for instance with DOBCP.

Tools for Sybase PowerDesigner

• TBLFIX reads an SQL script generated by PowerDesigner, and extracts all table and type definitions into one to three files per table, according to the structure that AbaPerls mandates for table files. (Why not a script generated by SQL Server Management Studio? We never the need for that, so I have not come around to it.)
• PDREP reads an HTML report produced by PowerDesigner and produces a modified version which is less noisy.

Version-control Tools

These tools are used to enforce AbaPerls's label rules in the version control system, and they are not very useful on their own.

• NEWLABEL sets a label on the special AbaPerls format LetterMajor.Middle.Minor on a directory under a version control. You can specify an explicit label, an increment, or just let NEWLABEL determine the next label for you.
• TFSLABELFIX changes the scopes of labels in TFS to agree with the folder they were set for. (If you use Team Explorer to set labels, the scope is always the team project, which does not fit well with AbaPerls.)

AbaPerls System Tables

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. 

Supported Platforms

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.

Getting Started with 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 Find/Replace Track

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:

1. Downloading and installing AbaPerls. You only need to read this, if AbaPerls is not already available at your site.
2. Running AbaPerls on your machine, so that you can setup AbaPerls to run on your machine.
3. The command descriptions for SSGREP, SSREPLACE and VCDBLOAD.
4. Version-control concepts, the section on VC-paths only.
5. Sysdef-files and Config-files, if you want to be able to search/replace in more than one hierarchy at the same time. You mainly need to read the part about config-files and when you come to the section The Version Specification, you can stop or take a brief look at the examples at the end of that page.

The Plain-Developer Track

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:

1. Downloading and installing AbaPerls. You only need to read this, if AbaPerls is not already available at your site.
2. Running AbaPerls on your machine, so that you can setup AbaPerls to run on your machine.
3. The AbaPerls subsystem structure, particularly the second part, The AbaPerls SQL directory structure.
4. How AbaPerls loads a file, to understand what ABASQL does behind your back.
5. The command description for ABASQL.
6. Using ABASQL with TextPad or Visual Studio so you can learn to use ABASQL from your text editor.

This far, you are ready to use ABASQL. Later on, you may also want to read these documents:

6. The Preppis preprocessor, if you want to use the power of this tool.
7. Learn about Object Checking, so that you understand what extra checks of your code that ABASQL performs.
8. Read about RUNSPS, so you know about this tool when you need to test.
9. SSGREP, SSREPLACE and VCDBLOAD, see also the Find/Replace track above.
10. You can defer reading about INSFILGEN until you actually need to use it.
11. Read the CLR page, to learn how to use the CLR page with AbaPerls.
12. If you want to grant permissions through stored procedures beyond ownership chaining, read the page Modules with Special Permissions.

The DBA/Configuration-Manager Track

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.

1. Downloading and installing AbaPerls.
2. Running AbaPerls on your machine.
3. The AbaPerls subsystem structure, to learn how AbaPerls look at subsystems and versions, and how AbaPerls maps SQL files into the AbaPerls SQL directory structure.
4. Version-control concepts, to learn about the general model AbaPerls applies for version-control systems.
5. Depending on which version-control system you use, read Notes on SourceSafe or Notes on Team Foundation Server.
6. Sysdef-files and Config-files and Configuration options. Even if you don't plan to divide your database in subsystems, you still need to grasp the subsystem concept and the configuration options.
7. The command LISTCONFIG, so that you can play with configuration files.
8. How AbaPerls loads a file, to understand what AbaPerls is doing behind your back with the SQL code you feed it.
9. Learn about Object Checking, so that you understand what extra checks of your code that ABASQL performs.
10. The command description for ABASQL, so that you can start playing with single files.
11. Using ABASQL with TextPad or Visual Studio, so you can use ABASQL from your text editor.
12. The command descriptions for SSGREP, SSREPLACE and VCDBLOAD.

This far, this is mainly an extended developer track. We now move to more central topics for a DBA/CM-person.

11. The life-cycle of a database, to get an overview on how to use DBBUILD and the update scripts generated by DBUPDGEN.
12. The command descriptions for DBBUILD and LISTERRS. This may be a good point for composing a sample structure, or simply build a database with the ABAPERLS subsystem only.
13. Installation at a customer site, the parts that apply to DBBUILD.
14. You need to give The Preppis preprocessor at least a cursory glance since INSFILGEN uses Preppis.
15. The command description for INSFILGEN, as must for a DBA who prepares INSERT-files. (A strict CM-person may read this more cursory.)
16. DOBCP and TBLCNT to move data.
17. The command description for DBUPDGEN. Many readers will be interested to read this directly after DBBUILD, and that's alright. I've deferred it a bit in the reading list, as this is by far the longest document in the set, and the tool is also the most complex of them all – but it is powerful!
18. Installation at a customer site, to read the parts on update scripts generated by DBUPDGEN and repeat the parts on DBBUILD.
19. Browse the documentation of the AbaPerls system tables. Pay particular interest to the table abasysobjects and the stored procedure ap_sob_report_suspects_sp (with which you can trace objects that somehow has not been loaded the "proper" way) and ap_zz_sob_load_sp (with you can initialize abasysobjects if in you install AbaPerls in an existing database. Of special interest is also the documentation for abaddltribypasslog where you can learn how AbaPerls prevents casual use of SSMS and other tools to modify objects outside AbaPerls and how you can bypass the DDL trigger.

As for the remaining commands and topics, I have not listed them in any reading-order; explore them at your own pace.

Limitations and Restrictions

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.

General Restrictions

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:

• Defining objects in any other schema than dbo.
• Version numbers on stored procedures. (This is a deprecated feature in SQL Server.)
• Sequences.
• User-defined data types in the CLR.
• CLR triggers.
• Fulltext indexes.
• XML indexes and spatial indexes are recognised, but since they are always recreated, the support is not practically useful.
• Replication and Change Data Capture.
• Change Tracking.
• File tables.
• In-Memory OLTP. (That is, memory-optimized tables and natively compiled modules.)
• Temporal tables.
• Always Encrypted.

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.

AbaPerls and Unicode

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 relies on the presence of the byte-order mark (BOM) to detect Unicode. AbaPerls is not able to an identify that a file is UTF-8, unless there is BOM in the file. AbaPerls maybe able to detect that a file is encoded in UTF16-LE (which is the default Unicode format on Windows), but it is a matter of guessworks.
• An exception to the previous point: when reading from TFS, AbaPerls relies on the encoding as stated by TFS.
• With SourceSafe you cannot use Unicode names for files and object, since SourceSafe does not support Unicode names.
• Unicode is not supported for file and object names for BCP files in DOBCP and DBBUILD, since AbaPerls uses Windows Command-line which is not Unicode aware.

Quoted Identifiers

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. 

Credits

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 Win32::OLE without which the connection to SourceSafe would not have been possible and Toshiyuki Yamato who wrote Win32::CLR that made the connection to TFS possible.

License

Perl is free software, available under the Perl Artistic Licensce.

Contact Information

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