This page describes how you should structure your SourceSafe projects so that they fit into the mind-set of AbaPerls. There are two "levels" in this structure: the upper level with version-subprojects for the subsystems and the lower level, the AbaPerls SQL Directory Structure, which is a foundation for the AbaPerls tools that load SQL objects.
Contents:
Subsystems and
Version-Subprojects
Subsystems
Version-Subprojects
Label Conventions
How to
Create a New Version-Subproject
The AbaPerls SQL Directory
Structure
The Top
Directory, SQL
MESSAGE
INCLUDE
TYPE
TBL
SP
FUNCTIONS
VIEW
ASSEMBLIES
SCRIPTS
A subsystem is a component, module or similar that is part of your system. Since AbaPerls mainly focuses on SQL code, AbaPerls is not so concerned whether your subsystem also results in a DLL, an executable or something else. For AbaPerls a subsystem is most of all a module within a database.
As a simple example, take an order system with three basic concepts: customers, products and orders. Then there is an optional component for automatic ordering from suppliers when stocks are running low which requires gateways to communicate with the systems of the various suppliers. There might be other components for accounting etc.
Not all customers who buy this system need all these components, and there are also different teams working with the development of the various components. Still the optional components are heavily dependent on the table and stored procedure for the basic concepts, so they need to be in the same database. Thus one way to partition the database is to have one subystem that includes the the basic concepts, the component for automatic ordering would be another subsystem, and the gateways would be subsystems of their own (because which gateways you install is site-dependent). The accounting component would be another subsystem etc.
All the subsystems for a certain database are gathered in a config-file for that database, and in the database, you find which subsystems that are installed in the table abasubsystems - that is, provided that you have installed the subsystem ABAPERLS with the AbaPerls system tables. As for a list of all possible subsystems to install in your database, AbaPerls does not maintain such information.
This picture illustrates the structure implied by AbaPerls for projects in SourceSafe.
Under the top project $/, there are a number of projects,
and these would typically be your subsystems. A subsystem-project
usually have sub-projects that are the main versions of the
subsystem, so we will refer to these
projects as version-subprojects . (However, having versions is no
requirement of AbaPerls; but usually the development cycle mandates it. If you
have a subsystem which is largely static, a version-subproject may not be called
for.)
The version subprojects are named on the form Major.Middle as illustrated in the picture to the right. The structure below each version subproject is typically resemblant between versions, because when a new version subproject is created (a procedure detailed below), all files are initially shared with the previous version-subproject.
AbaPerls does currently not impose any certain structure on this level but in one case: SQL files should be in a project called SQL, and this project should conform to the AbaPerls SQL directory structure which several of the AbaPerls tools are closely integrated with.
However, note that there is no requirement that the SQL project is right below the project carrying the version number, and neither must there be exactly one SQL project in a version-subproject. It is perfectly permissible to have $/SUBSYS/4.10/THIS/SQL and $/SUBSYS/4.10/THAT/SQL. And they would in fact when you compose your config-file be two different subsystem entries.
Why would you do this? Say that the order system above exposes an API for third-party vendors (e.g. developers of web interfaces). The API contains stored procedures for accessing the basic concepts, as well as the optional components. But you cannot install the stored procedures that accesses the optional components at a site which does not have them. Thus, you need to divide the API in several subsystems database-wise, but you still want to keep them as one hierarchy in SourceSafe.
When you need to create a new version for a subsystem, you use the tool NEWSUBSYSVER. This tool performs performs the same Share operation you would invoke directly from the SourceSafe GUI, but also labels projects according to the labels conventions described in the next section.
During the lifetime of a version-subproject you are likely to label it many times. AbaPerls imposes a certain format of the labels which is used by DBUPDGEN and the update script it generates. By adhering to this convention, you can get the update scripts to check whether they are in sync with the database they are about to update.
This label format is LetterMajor.Middle.Minor. An example is L4.60.0012. Major.Middle should agree with the name of the version-subproject, and Minor is a number you assign to your labels. When NEWSUBSYSVER creates a new version-subproject it labels the new project with Minor = 0001, for instance L4.60.0001, and the old version-subproject is labelled with Minor = 1000, for instance L4.45.1000. This has great importance when you work with update scripts generated by DBUPDGEN, as they assume two labels with different Major.Middle to be equivalent if Minor = 1000 and Minor = 0001 for the smaller and the larger of the two Major.Middle respectively. See further on the DBUPDGEN page on how the update scripts check the subsystems. If you create
The letter in the label does not carry any meaning to AbaPerls, and in fact, AbaPerls will think that L4.60.0012 and S4.60.0012 are the same label. (The letter is only there to prevent SourceSafe from interpreting the label as a date, see the SourceSafe page.) In the same vein, AbaPerls ignores leading zeroes, so L1.1.1 is the same label as L1.01.0001. The combination of Major. Middle and Minor must be unique for a subsystem; thus have you used L1.1.1 for a subsystem, you cannot use S1.01.0001 for the same subsystem (but you can use for others). Note: if the version of the ABAPERLS subsystem in the database is L1.0.0010 or earlier, the uniqueness requirement for the triplet (Major, Middle, Minor) applies to the entire database.
See the page SourceSafe concepts, for discussion on other SourceSafe concepts that are important to AbaPerls.
If the upper level of the subsystem structure is somewhat loose, the lower part, the SQL structure is not. This structure maps files to various directories in the structure according to their file extension, and this is central feature of the AbaPerls file-lookup order that several tools rely on. This structure exists both in SourceSafe in terms of projects and on disk in terms of folders. In the AbaPerls documentation we just call it all directories.
This picture illustrates the structure, and which file extension that goes into which directory.
The names of the eight directories in this picture are special, and the AbaPerls tools will assume that they are inside an AbaPerls SQL directory structure, if they find any on these eight names in the path to the current directory.
All directories can have subdirectories, so you if you have a group of stored procedure which serve some special function (but yet not special enough to warrant a subsystem of its own), you can put these in a subdirectory. Beware that a subdirectory cannot have the same name as any of the eight special names in the directory structure.
You may have other directories in the SQL directory; AbaPerls ignores these. The same is true for any extraneous files you may put in the top directory.
If you use a file with an unknown extension, AbaPerls will not be able to
load it. If you place a file in the wrong directory, AbaPerls will not find
the file. If you place an object in a file which at extension which is not
intended for the type of the object, AbaPerls issues a error message and
refuses to load
the file. Likewise, for most object types, as detailed below, AbaPerls checks
that the file name agrees with the object name and issues an error message in case of
a mismatch and does not load the file. The last check is overridable for
stored procedures and user-defined functions, by using the
Note that an object name cannot appear in more than one subsystem. That is, if subsystem A creates the stored procedure some_sp, you get an error if you try to create some_sp in subsystem B as well. (This can be can overridden only in an update script generated by DBUPDGEN, see the section Suppressing the Name-Clash Check.)
Here is a description of what all directories are supposed to contain.
The name of the top directory should be SQL and nothing else. Beside the eight main subdirectories SQL is the home for one file: GRANT.TEMPLATE. This files specifies the permissions that are to be granted or denied on the objects in the database. The format of this file is described on the page for the AbaPerls file-loading process.
The MESSAGE directory includes files with the extensions .SQL and .POSTSQL. When you build an empty database with DBBUILD, the .SQL files are loaded before any other file in the subsystem whereas .POSTSQL files are loaded after all other files.
In an .SQL file you typically create basic objects that
AbaPerls does not track specifically. Typical examples are
definition user-defined messages added with sp_addmessage and creation of users, groups and
roles. This is also where you would put creation of Service Broker objects such
as message types, contracts, queues etc, as AbaPerls have no particular support
for this type of objects.
In .SYNO files you define synonyms. See further the page Using Synonyms for details. You can only use .SYNO files if the ABAPERLS subsystem is installed in the database.
In .POSTSQL files you put things that requires objects in the subsystem to be present, for instance binding a Service Broker queue to a stored procedure, or loading startup data.
The milseading directory name stems from the fact that originally user-defined messages was all that was in it.
The INCLUDE directory holds files with extension .SQLINC. These files are included by other files with the Preppis preprocessor. The main purpose of include-files is to share temp tables between stored procedure and/or functions, but that does not exclude other uses for include files. See further about include-files on the Preppis page.
The TYPE directory holds files with the extension .TYP. Such a file defines a
user-defined datatype with EXEC sp_addtype, and can optionally also define a
default and/or a rule with CREATE DEFAULT and CREATE RULE and bind that
default/rule to that type with EXEC sp_bindefault and EXEC sp_bindrule.
The convention is that the name of the file agrees with the name of the type, and if it does not AbaPerls issue an error message when you load the file.
You form the name of the default by adding _default as a suffix on the type name, and likewise you form the name for a rule by adding _rule to the type name.
One class of type names are special: these are the type names ending in _moduser. DBUPDGEN assumes that such a type has a rule bound to it, and that this rule requires the contents of a column with this type to agree with the current user. See further about table-updates on the DBUPDGEN page.
As for rules and defaults, AbaPerls defines no space for rules and defaults that are not bound to types. There is not really any point with such rules and defaults as it is better to define CHECK and DEFAULT constraints on table columns. (The SQL Server literature is overall somewhat lukewarm towards rules and defaults, as they are not ANSI. However, if the same constraint appears in five table columns, a user-defined type with a rule/default bound to it is much better from a maintenance perspective.)
The TBL directory contains files for tables. There can be up to six files for one single table:
| tablename.TBL | The table definition with CREATE TABLE . |
| tablename.FKEY | All foreign-key constraints for the table. |
| tablename.TRI | All triggers for the table. |
| tablename.IX | Indexes and statistics for the table that are not PRIMARY KEY or UNIQUE constraints. |
| tablename.INS | INSERT-files that loads the table with predefined data. |
| tablename.XLS | An Excel workbook from you create tablename.INS with INSFILGEN. |
tablename.TBL should include one statement: CREATE TABLE which should include the definition of any PRIMARY KEY and UNIQUE constraints the table has. (A table should normally have a primary key; not defining one would be an exceptional thing to do.) The .TBL file is also the preferable place to put CHECK and DEFAULT constraints. You should not put FOREIGN KEY constraints in this file. While not a requirement from AbaPerls, it is a strong recommendation to name all your constraints. You are advised to know that AbaPerls thinks that it is good style to have one column per line, and that all columns should have a NULL/NOT NULL indicator.
If the name of the table does not agree with the file (case-sensitive), AbaPerls issues an error message when you load the file.
tablename.TRI include triggers for the table. In difference to stored procedures, you don't have one file per trigger, but you gather the triggers for a table in one file. Within, we should hasten to add, in a single subsystem. That is, say that subsystem A includes MYTABLE.TBL as well as some triggers in MYTABLE.TRI. Say now that subsystem B, which refers to A, wants to add its own validation or cascading update. It is perfectly legal for B to add triggers in its own MYTABLE.TRI. Therer are couple of caveats associated with this, though, whereof some are related to SQL Server and others to AbaPerls:If the name of the table does not agree with the file, AbaPerls issues an error message when you load the file.
tablename.FKEY should include one ALTER TABLE statement for the table that adds all FOREIGN KEY constraints for the table. You may also opt to put CHECK and DEFAULT constraints in this file. All constraints in the .FKEY file must be named. The reason that foreign keys are in a file of their own, is that DBBUILD creates the tables in strictly alphabetic order, and .FKEY files in a later round, and in this way DBBUILD evades the problem with creating tables in dependency order. (Why would you put CHECK and DEFAULT constraints in the .FKEY file? Well, if you need to change them, re-loading an .FKEY file is a much smaller affair than re-loading a .TBL file. See more on the DBUPDGEN page for this.)
As for triggers, a referring subsystem can add its own foreign keys to a table in a lower subsystem, although this may rarely be practically useful. The first caveat for triggers applies to foreign keys in referring subsystems as well.
If the name of the table does not agree with the file, AbaPerls issues an error message when you load the file.
tablename.IX should include all indexes for the table (except for PRIMARY KEY and UNIQUE constraints). This is also where you put CREATE STATISTICS statements for the table. A good advice is to put the clustered index first in the file, when none of the PRIMARY KEY and UNIQUE constraints are clustered, as SQL Server rebuilds all non-clustered indexes when you create or drop a clustered index.
Warning: SQL Server supports the use of a WITH clause for the CREATE INDEX and STATISTICS statements, and for CREATE INDEX also an ON clause. These clauses are poorly supported by AbaPerls. If you use them, you must not start these clauses on a new line, but they must follow in entirety after the closing parenthesis of the column list for the index/statistics.
As for triggers, a referring subsystem can add its own indexes to a table in a lower subsystem. The first caveat for triggers applies to indexes in referring subsystems as well.
If the name of the table does not agree with the file, AbaPerls issues an error message when you load the file.
tablename.INS inserts or updates pre-defined data in a table. There may be several reasons why you want to have such a file. One common case is a table that the users never modify, but which holds data on which the system depends. For instance, a table that holds fixed choices for combo-boxes, or a table that defines which functions that are available in the system.
Beware that since you may need to run INSERT-file more than once (for instance to add new pre-defined data), it is not a very good idea to have bare INSERT statements, but rather you should call a stored procedure which inserts or updates the table, so you can keep the INSERT-file itself free from bulky SQL syntax. But there is no such requirement from AbaPerls. Overall, .INS files are files where you, like .SQL files, can put whatever that does not fit in any of the other file extensions.
As you might have guessed by now, a referring subsystem can add its own INSERT-file for a table, and this is often very useful. The trigger caveat does not apply, as a default table-update generated by DBUPDGEN copies existing data. On the other hand, the update for the referring subsystem will only the INSERT-file if the file has actually changed.
The page for INSFILGEN includes a good example of an INSERT-file.
For INSERT-files, AbaPerls does not perform any checks that file name agrees with any object name.
tablename.XLS is used as input to INSFILGEN to generate INSERT-files. Many tables with pre-defined data are small; maybe only 3-4 columns and some 10-15 rows. For them a plain INSERT-file with calls to a stored procedure is fairly simple to manage. But if you have 30 columns and 300 rows to insert, and what to insert depends on $IFDEF directives, then a plain file is getting very cumbersome to maintain. Therefore you can define the pre-defined data in an Excel workbook and generate the .INS file with INSFILGEN.
not-a-table.INS. Sometimes you have a need when you build an empty database with DBBUILD to load data in some tables before any other tables, because other INSERT-files refers to this data. In this case you can use a "fake" INSERT-file, that is an INSERT-file where the name does not coincide with a table. For instance, in AbaSec, there is a file AAAA-BOOTSTRAP.INS. (Recall that DBBUILD loads files in alphabetic order.) Likewise, you could have a file ZZZZ-WRAPUP.INS which performs some commands that rely on that all predefined data is present. You can also use .POSTSQL files in the MESSAGE directory for this.
The SP directory contains files with the extension .SP. Typically you have one stored procedure per file with the the file and the procedure having the same name, and if the name of the procedure does not agree with the file, AbaPerls issues an error message when you load the file.
Beside the CREATE PROCEDURE statement itself, an .SP file may include creation of temp tables (preferably through include-files) that the stored procedure refers to, but does not create itself. Very occasionally you may include GRANT statements as discussed on the file-loading page. You should never include explicit code for dropping the stored procedure; AbaPerls takes care of this.
The FUNCTIONS directory holds files with the extension .SQLFUN which contains user-defined functions created with CREATE FUNCTION. The same notes as for .SP files apply.
The VIEW directory contains files for views. There can be up to three files for one view:
| viewname.VIEW | The table definition with CREATE VIEW . |
| viewname.VTRI | All triggers for the view. |
| viewname.VIX | Indexes and statistics for the view. |
For all files, AbaPerls emits an error message if the name of the file does not agree with the view. As for tables, you have all triggers and indexes for the view in the same file.
In difference to tables, AbaPerls does not really support that a referring subsystem adds its own triggers or indexes for a view. Since you can only have INSTEAD OF triggers on views, and SQL Server only permits one INSTEAD OF trigger per action, it would be less meaningful anyway.)
The ASSEMBLIES directory holds files with the extensions .ASSEM for the assembly definitions and .DLL for the assemblies themselves. For more details on using CLR assemblies with AbaPerls, see the page Using the CLR with AbaPerls.
The SCRIPTS directory is different from the other directories, because it is not included in the AbaPerls file-lookup order. SCRIPTS is a holding area for miscellaneous files here that you want to have around without being included in the build process. This is where you put the update scripts generated by DBUPDGEN with or without manual improvements. (You should certainly keep you update scripts under version-control as well.) You can also have various Perl, .BAT or .SQL files for various fixes you may apply at times.
For two tools SQL/SCRIPTS is indeed special. To wit, SSGREP and SSREPLACE ignores directories with this name. (SSGREP can be persuaded to look here, but not SSREPLACE.)