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 with Visual SourceSafe as the version-control system. AbaPerls also includes tools for searching and replacing in SourceSafe that are useful for any SourceSafe 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 that uses the same environment as AbaSec. But at the same time AbaPerls does not have any the pretention to be full-fledged product. It has been designed as an in-house development tool, with the toolsmith in reach. A primary aim in the development of AbaPerls has been to keep down development time, development time of AbaPerls that is. So in places AbaPerls makes assumptions or imposes limitations that fits 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 Query Analyzer or OSQL. 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, as these versions of SQL Server defer such checks until run-time.
• 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 store your code encrypted 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 truly mix collations on a server.

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.

Building and Updating Databases

To build an empty database AbaPerls provides the tool DBBUILD. DBBUILD finds the SQL files in a SourceSafe project and its subprojects and load these into database in a well-defined order. DBBUILD loads all files with the AbaPerls file-loading process, thus 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 compares two SourceSafe versions of a project tree, and from the differences it generates an update script, that will update the database from one version to the other. These upgrade scripts use the AbaPerls file-loading process, so again the full power of ABASQL is available.

To facilitate things for DBBUILD, DBUPDGEN as well as other tools, there is the AbaPerls SQL directory structure, which maps file to various directories depending on their file extension.

A Module-Based Database

The product I work with consists of several modules, some large and some small, and each costumer typically has his 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 the subsystem configuration for a database in a configuration file (or config-file for short). The config-file does not only define which subsystems to include in the database, but you can also specify configuration options, either on database level or on subsystem level.

Provided that you include the ABAPERLS subsystem (which includes the AbaPerls system tables) in the database, DBBUILD saves the configuration options in the database. Then ABASQL and the update scripts generated by DBUPDGEN read these, and acts 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 stored encrypted, and then 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 INSERT-files.

SourceSafe Search and Replace

Two very handy tools for finding things in SourceSafe:

• SSGREP searches SourceSafe for one or more regular expressions (Perl syntax). For commonly used file types (SQL, C/C++ and VB), AbaPerls knows how separate code from comments, and SSGREP normally 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 SourceSafe and checks out the files with matches and edits them. Like SSGREP, SSREPLACE normally works outside comments only. (And for the nervous reader, we should hasten to add that SSREPLACE does not check things in on its own.)

Other SQL Utilities

• DOBCP performs BCP in or out of all tables in a database.
• TBLFIX reads a SQL script for a database, and extracts all table and type definitions into one to three files per table, according to the structure that AbaPerls mandates for table files. The current version is fairly limited, as only output from PowerDesigner 6.1 and Enterprise Manager 6.5 is handled correctly.
• SPFIX is a similar tool for stored procedures.
• RUNSPS reads a file - typically an comma-separated file composed with Excel - and runs the stored procedures with parameters in the file. A handy test tool.
• TBLCNT counts records in all tables. This tool is handy when moving data, for instance with DOBCP.

Non-SQL Tools

• PDREP95 and PDREPDIV read an HTML report produced by PowerDesigner 9.5 and 6.1 respectively to make the reports produced by PowerDesigner more manageable.

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 are not mandatory; you need to explicitly include the ABAPERLS subsystem in your database to make use of them. But without them, you will miss some of the functionality 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 versions 2000 and later and Visual SourceSafe versions 6 and 2005. You need Perl 5.8 to run AbaPerls, but Perl is free software. 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 SourceSafe-Only Track

The tools SSGREP and SSREPLACE 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. 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 and SSREPLACE.
4. SourceSafe concepts, the section on SS-paths only.
5. Configuration files, if you want to o be able to search/replace in more than one hierarchy at the same time. When you come to the section "The Version-Designator", 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 themselves 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 is doing behind your back.
5. The command description for ABASQL.
6. Using ABASQL with TextPad.

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. Read about RUNSPS, so you know about this tool when you need to test.
8. SSGREP and SSREPLACE, see also the SourceSafe-only track above.
9. You can defer reading about INSFILGEN until you actually need to use it.

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, both the top-level about version-subprojects and the AbaPerls SQL directory structure.
4. SourceSafe concepts, to learn how AbaPerls uses paths, labels and pinning.
5. Configuration 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.
6. The command LISTCONFIG, so that you can play with configuration files.
7. How AbaPerls loads a file, to understand what AbaPerls is doing behind your back with the SQL code you feed it.
8. The command description for ABASQL, so that you can start playing with single files.
9. Using ABASQL with TextPad
10. The command descriptions for SSGREP and SSREPLACE.

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

10. The life-cycle of a database, to get an overview on how to use DBBUILD and the update scripts generated by DBUPDGEN.
11. 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.
12. Installation at a customer site, the parts that apply to DBBUILD.
13. The Preppis preprocessor, at least a cursory glance since INSFILGEN uses Preppis.
14. The command description for INSFILGEN, as must for a DBA who prepares INSERT-files. (A strict CM- person may read this more cursory.)
15. DOBCP and TBLCNT to move data.
16. 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!
17. Installation at a customer site, to read the parts on update scripts generated by DBUPDGEN and repeat the parts on DBBUILD.
18. 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.
19. Known issues, so that you know about all sorts of restrictions that comes with AbaPerls.

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

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 not have been possible.

License

Perl is free software, available under the http://www.perl.com/pub/a/language/misc/Artistic.html.

Contact Information

Erland Sommarskog, esquel@sommarskog.se.