How AbaPerls Loads a File

This page describes the AbaPerls file-loading process which is activated each time you load a file with AbaPerls. There are three ways you can load database files with AbaPerls: You use the tool ABASQL to load one (or a handful) files at time; you use DBBUILD to build an entire database or to add a subsystem to a database; you use update scripts generated by DBUPDGEN to update the database from one version to another. No matter which tool you use, AbaPerls uses the same mechanisms to locate and load the file. You need to have an understanding of what AbaPerls does behind your back – at least when AbaPerls does something you did not expect. (And that will happen sooner or later. Trust me.)

Contents:
   Overall Loading Procedure
   The AbaPerls File-Lookup Order
   Messages from SQL Server
   Emitting SET commands
   Version Checks for Production and Test Databases
   Pre-SQL Analysis: Creating and Recreating Objects
      Checks on Conformance to the Subsystem Structure
   Pre-SQL Analysis: Temp Tables and Table Variables
   Pre-SQL Analysis: Reloading a Table File
   Pre-SQL Analysis: Supplemental SQL Checks
      Use of TOP with INSERT, UPDATE, DELETE and MERGE
      SET ROWCOUNT
      = NULL
      Checks for CREATE TABLE
      EXECUTE AS
      THROW
      Private Names
      Check that $DEPENDSON is Present
   Pre-SQL Analysis: Permissions on Objects
   Pre-SQL Analysis: Other Miscellaneous Fixes
   Retrieving Name of Current Subsystem

Overall Loading Procedure

1. Looks up the file through the AbaPerls file-lookup order.
2. Reads the file and strips it of comments.
3. For production and test databases, performs a version check against what's in the database.
4. Runs the file through the Preppis preprocessor.
5. Pre-SQL Analysis, detailed in the several sections on this page, and which may add extra code before the file (pre-work) and after the file (post-work), as well alter the code of the file itself.
6. Puts back the comments that were stripped, unless the ‑crypt configuration option is in force.
7. Starts a transaction and sends the generated pre-work code to SQL Server.
8. Sends the file itself to SQL Server.
9. Performs Object Checking. This is described in a separate topic.
10. Sends post-work code to SQL Server, for instance GRANT statements.
11. Update the AbaPerls system tables, if the ABAPERLS subsystem is installed.
12. Commits the transaction started in point 7.

If an error occurs in any of the steps, the remaining steps are not performed. If errors occurs in step 7 or later, the transaction is rolled back.

When you use the ‑noexec switch, AbaPerls only performs steps 1, 2 and a modified version of step 4: Preppis processes only the directives $INCLUDE, $REQUIRE, $KEYFILE, $COMPILE, $DLLINCLUDE, EXTERNAL NAME and $USEDBY to retrieve all dependent files and to create DLLs from source files written in .Net languages. You combine ‑noexec with ‑get to create a kit for installation at a customer site and you want the include files and the DLL files created by $COMPILE in the kit too.

AbaPerls starts a user-defined transaction to maintain the integrity between AbaPerls own tables and the SQL Server system tables. Thus, if an error occurs in any of the steps 7 to 11, AbaPerls will roll everything back. However, a file may include a command that is not permitted to appear in a user-defined transaction, for instance a call to the system procedure sp_tableoption. In this case, AbaPerls restarts the operation on step 7, but without starting a transaction. The error is not reported.

A general disclaimer about Pre-SQL Analysis: AbaPerls does not include a full-blown SQL parser, but the pieces of AbaPerls that parse SQL code make some "reasonable assumptions" about formatting and syntax in order to cut down development time. In the various sections, I try to document what these assumptions are and which restrictions you face.

The AbaPerls File-Lookup Order

Introduction

When AbaPerls is fed a filename to process, AbaPerls may look for the file in these locations:

1. On disk.
   1. With the filename as given.
   2. In the appropriate directory in an AbaPerls SQL directory structure.
   3. For a file that is checked out, the path for the local file according to the version-control system.
2. On disk in an AbaPerls SQL directory structure in a subsystem subdirectory.
3. In version-control according to the AbaPerls SQL directory structure.

AbaPerls will never attempt all these locations. Instead AbaPerls identifies the situation in which you use AbaPerls, by asking these questions: 1) Is version-control active? 2) Is ‑use_disk active? 3) Is there a directory subsystem/SQL visible in the current folder? 4) Are the special files SS‑FILES.LIS and SUBSYSTEMS.LIS present in the current directory? Depending on the answers, AbaPerls identifies any of these four situations:

Version-Control Only. This occurs when the answers are 1) Yes. 2) No. 3) Don't care. 4) Don't care. In this situation, AbaPerls only reads from location 3 in the list above.

An AbaPerls Install Kit. This situation is identified by these answers: 1) No. 2) Yes. 3) Don't care. 4) Yes. In this situation, AbaPerls only reads from location 2 in the list above.

A Pseudo-kit. This situation is identified by these answers: 1) No. 2) Yes. 3) Yes. 4) No. In this situation, AbaPerls only reads from location 2 in the list above.

Developer Environment. The answers are: 1) Don't care. 2) Yes. 3) No. 4) No. In this case, AbaPerls attempts the steps 1a, 1b, 1c and 2 in that order, with these qualifications:

• If version-control is not active, AbaPerls will not attempt 2, obviously.
• If version-control is active, and AbaPerls is able to find the item in version-control in the given VC-path, AbaPerls will only attempt step 1a to 1c if you have the file checked out. Note here that it does not matter on which computer you performed the checkout; AbaPerls will consider all your checkouts.

The reader is likely to quite confused at this point by all these possibilities. For this reason I like to butt in that this is designed to do what you want. Before I explain this a little closer, let's revisit the defaults for the different AbaPerls tools. Two general rules: a) if version-control is not active, ‑use_disk is in force. b) ‑get implies ‑nouse_disk.

Let's look at the four situations again, but in different order. In Developer Environment, you typically use ABASQL to load individual files. You mainly work from disk, but you are likely to take files like grant.template or include-files from version-control. If the item is in version-control but not checked-out by you, AbaPerls avoids your disk copy, since it may be out of date. The search order permits you to load a stored-procedure file while in the TBL directory or vice versa.  In this situation, you may also want to use DBBUILD and update scripts for test builds. However, these tools are geared for the other three situations.

The situation Version-control only is mainly used by the build master who wants to build or upgrade a database in a controlled way, or create an install kit for a remote site with ‑noexec ‑get. In this situation, local files on disk should not interfere. You may also want to use ABASQL in this situation to deliver a single fix.

The situation AbaPerls Install Kit is the result of Version-control only with ‑noexec ‑get and the net result for the database is the same: all files will appear to have been loaded from version-control. The reason for this two-step operation is that when you are at a customer site, you are not likely to have access to your version-control system. See the page Installation at a Customer Site for a longer discussion.

The Pseudo-kit situation is not as common as the other three. In this situation, you have a structure which is similar to the install kit you get with ‑noexec ‑get – except that the files SS‑FILES.LIS and SUBSYSTEMS.LIS are not there and in the database and there is no connection to version-control whatsoever. You may set this because you want to experiment. For instance, you may want to test out a restructuring of your current system.

Here follows a more detailed specification for each step:

1a. On disk with the filename as given

If you specify a full path such as C:\Temp\nisse_sp.sp that is the file, and AbaPerls will not try anything else. If you say nisse_sp.sp, and there is a nisse_sp.sp in the current directory, this is the file. The same is true, if you specify sub\nisse_sp.sp if there is a sub-directory sub in the current directory, and in sub there is a nisse_sp.sp.

1b. In the appropriate directory in an AbaPerls SQL directory structure.

If step 1a yields no file, AbaPerls examines whether the current directory in part of an AbaPerls SQL directory structure. This is the case, if the current path includes any of the special names in this structure that is Message, Type, Tbl, Servicebroker, SP, Functions, Include, View, Assemblies or Scripts. In this case, AbaPerls looks for the file in the directory that maps to the extension for the file to load. If AbaPerls finds itself in the SQL directory, AbaPerls will look for the file in the subdirectory that the extension maps to.

1c. For a file that is checked out, the path for the local file according to the version-control system.

Note here that if you have multiple checkouts from different computers or different workspaces on the same computer, it is not predictable which version AbaPerls will use; it uses the first it is served from the version-control system. Checkouts in this context include pending Add operations in TFS, which supports this concept.

2. On disk in an AbaPerls SQL directory structure in a subsystem subdirectory.

Say that there is your current directory, there is a directory SUBSYS1 which in its turn has a subdirectory SQL, and you specify ‑subsystem SUBSYS1 and nisse_sp.sp as the file. AbaPerls will then look for SUBSYS1/SQL/SP/nisse_sp.sp.

3. In version-control according to the AbaPerls SQL directory structure

If the VC-path does not end in /SQL, AbaPerls adds this to the VC-path, and then uses the file extension to determine in which subdirectory in the structure to look for the file. If you somewhere have specified a label (or a date or version number), AbaPerls will retrieve the corresponding version of the file.

An Example

Assume that your current directory is C:\projects\abc\sql\tbl. You give this command:

abasql -d mydatabase -VC C:\SourceSafe/$/abc/3.20 some_sp.sp

This file includes these two lines:

$INCLUDE this_include.sqlinc
$INCLUDE sub/that_include.sqlinc

You have some_sp.sp checked out to C:\projects\abc\3.20\sql\sp. $/abc/3.20/SQL/include/this_include.sqlinc exists in SourceSafe, but you don't have it checked out. There is no file $/abc/3.20/SQL/include/sub/that_include.sqlinc in SourceSafe.

AbaPerls first looks up some_sp.sp in $/abc/3.20/sql/SP/some_sp.sp and finds that it exists you have the file checked out, AbaPerls will search the locations in this order:

1a. C:\projects\abc\sql\tbl\some_sp.sp. (Local directory.)
1b. C:\projects\abc\sql\SP\some_sp.sp. (The directory for stored procedures.)
1c. C:\projects\abc\3.20\sql\SP\some_sp.sp. (Checkout location.)
3. $/abc/3.20/sql/SP/some_sp.sp (Path in SourceSafe.)

AbaPerls will look for this_include.sqlinc in one single place: $/abc/3.20/sql/include/this_include.sqlinc. (Because you don't have the file checked out.)

AbaPerls will first look for that_include.sqlinc in $/abc/3.20/sql/include/sub/that_include.sqlinc and since this item does not exist, AbaPerls will try these two locations:

1a. C:\projects\abc\sql\tbl\sub\that_include.sqlinc.
1b. C:\projects\abc\sql\include\sub\that_include.sqlinc.

Note here that sub is included in both cases.

Another Example

You are in C:\Build and there is a structure created with ‑noexec get with three subsystems: ABAPERLS, SUBSYS1 and SUBSYS2. You run this command:

abasql -d somedb -subsystem SUBSYS1 some_udf.sqlfun

AbaPerls will attempt to load C:\Build\SUBSYS1\SQL\Functions\some_udf.sqlfun.

You now run this command:

abasql -d somedb -subsystem SUBSYSTEM2 someother_udf.sqlfun

This will yield an error, because there is no subsystem with that name in SUBSYSTEMS.LIS.

Assume now that the structure is the same, but there is no SUBSYSTEMS.LIS or SS‑FILES.LIS. The first command will have the same result; AbaPerls will attempt to load C:\Build\SUBSYS1\SQL\Functions\some_udf.sqlfun. However, the second command till have different effect. AbaPerls will now attempt load C:\Build\some_udf.sqlfun (and actually also C:\Build\Functions\some_udf.sqlfun), as there is no way for AbaPerls to detect the incorrect subsystem name. If any of these files would happen to exist, the load succeeds.

Messages from SQL Server

Messages from SQL Server stemming from the actual file you submitted, are likely to be syntax errors or warnings that relate to the SQL code you submitted to AbaPerls. AbaPerls prints a message that includes the message number, severity level, line number, file name and message text. If the message number is 0 or negative, this is a message from AbaPerls itself. (E.g. errors found by Object Checking.)

The line number SQL Server reports relates to the batch, but AbaPerls maps this to the correct file (it could be a file included with $INCLUDE) and line in the file (which could the definition of a long macro). (Provided that is, that the line number that SQL Server supplies is correct, which unfortunately is not always the case, although SQL Server has improved over the years.)

If errors occur in any of the pre-work or post-work code that AbaPerls adds before and after the file, AbaPerls prints the entire text of the complete SQL batch that failed together with line numbers directly related to that batch. The most likely user-error to cause this is an incorrect CREATE TABLE for a temp table or a bad grant.template, see below about permission on objects. Other errors are even less anticipated; would they occur it could be a bug in AbaPerls. (But there are a few evil things you could do mess things up for AbaPerls.)

Emitting SET commands

For each file, AbaPerls emits a bunch of SET commands that always are the same:

 SET ANSI_DEFAULTS ON
SET IMPLICIT_TRANSACTIONS OFF
SET CURSOR_CLOSE_ON_COMMIT OFF
SET ARITHABORT ON
SET DEADLOCK_PRIORITY LOW
SET NOCOUNT ON
SET NUMERIC_ROUNDABORT OFF
SET XACT_ABORT OFF

Note: older versions of AbaPerls permitted you use config-options to specify that some ANSI options should be OFF. These options have been dropped, and AbaPerls always run with all ANSI options on (with the exception of IMPLICIT_TRANSACTIONS and CURSOR_CLOSE_ON_COMMIT) in agreement with defaults for SQL Server Management Studio and all modern client APIs.

You can of course use SET commands within stored procedures, triggers and functions. However, if you are considering putting a SET command in a file outside a module definition, there is all reason to think twice or even thrice whether you really must. This is not supported by AbaPerls, and it can lead to nasty surprises.

Version Checks for Production and Test Databases

With the config-option ‑environment, it's possible to define a database as a development, test or production database. For the latter two, AbaPerls performs tests of the objects being loaded versus the information stored about them in the system table abasysobjects. This section does not apply to databases marked DEV.

The ultimate purpose of these tests is to handle the situation that a developer uses ABASQL to load a new version of an object into a test or production database. This could be an urgent fix in a stored procedure, or a configuration change through an INSERT-file. Later, when an update script is run against this database, the update script happens to include an earlier version of that file. In a production environment, you probably don't want that newer version to be over-written, as this could reintroduce the bug that was fixed, or revert the configuration. In a test environment, you are caught between two wills: on the one hand, you may want to keep that fix, but on the other, you should actually test what you have built and may eventually ship into production. (By overwriting the file, you will be made aware of that what you installed, does not include that fix.)

This boils down to a couple of rules that depend on the environment and what tool you are using. On the one hand is ABASQL for manual loads, and on the other is DBBUILD ‑rebuild and update scripts, below referred to as "build tools". None of these checks can be overridden by ‑force. These are the checks:

• All tools, PROD: For existing files, when there is a value in the column ss_version in the table abasysobjects, i.e. the file was previously loaded from version-control (i.e. TFS or SourceSafe), you must load the file from version-control, or you will get an error message. If you were to load from disk, an update script would not be able to conclude whether the database has an older or newer version of the file. The check is not performed for new files (as sometimes you need to play with test versions, even in a production database), or for files with no value in ss_version (damage already done).
• All tools, TEST: If you load from disk, you will get a warning recommending you to load from version-control.
• ABASQL, TEST and PROD: When you load a file from version control with the ‑VC option, you must load it from the same directory in version-control that the most recent upgrade script for subsystem was run from. The check is not performed for new files. Note: If the version of the ABAPERLS subsystem is L1.0.0080 or earlier, this check is instead performed against the version-control path the file itself most recently was loaded from, and which may be different from the subsystem. You can suppress this older check by clearing abasysobjects.ss_version to force a load from the
• ABASQL, TEST and PROD: If you load a file from version-control, and you explicitly specify a version with the ‑label option and you load an older version than what's currently in the database, you will get a warning. (The higher version might have been installed by mistake – or you are making a mistake now.)
• Build tools, TEST and PROD. If there is an MD5 hash stored for the file in abasysobjects, and this hash matches the MD5 hash for the file, the file is always loaded, and the checks listed below are not performed. See further below about the MD5 hash.
• Build tools, TEST and PROD: If the there is no version-control number for the file in abasysobjects, you get a warning. (As this unknown version could be ahead of or behind the version from the build tool.)
• Build tools, TEST and PROD: If the version number in abasysobjects is higher than the version number that the build tool is about to load, but the version-control directory in abasysobjects.path is different from what the build tool uses, you get a warning. (This may be OK, if there were more check-ins in the old version directory than in the new.)
• Build tools, PROD: If the version number in abasysobjects is higher than the version number that the build tool wants to load, and the version-control directories are the same, the build tool ignores the file and does not load it. The tool leaves a message with level 9 in the log file. For update scripts you can override this by setting the AbaSql property Low_version_force, see the DBUPDGEN page for details. (To do the same with DBBUILD ‑rebuild, simply specify ‑environment DEV together with ‑force, and then restore the setting when you are done.)
• Build tools, TEST: If the version number in abasysobjects is higher than the version number that the build tool wants to load, and the version-control directories are the same, the build tool will install the lower version, but leave a warning with level 10 in the log file.
• Update scripts, TEST and PROD. Say that you have an update script for subsystem ALHPA, and this script includes a load of a file from BETA, using the syntax abasqlfile('BETA!other_sp.sp'). This load is only performed if the MD5 hash for other_sp.sp matches the hash stored in abasysobjects. If they do not match, this results in an error (i.e., the severity level is > 10.) Presumably, other_sp.sp was included in the script, because other_sp depends on a file in ALPHA, for instance an include-file and needs to be refreshed. In this case, it would not be safe to load a different version of the file, because you may not be running an update script for BETA, and a different version of other_sp could break functionality in BETA.

Note: "loading from version-control", includes loading from a structure previously created with ‑noexec ‑get.

For most file types, AbaPerls performs this check as soon it has looked up the file in version-control, looking up the information in abasysobjects with the file name as key. If a file appears in a $REQUIRE macro, AbaPerls checks that file separately, and this can lead to that the $REQUIREd file is skipped, while the calling file is still loaded. (Recall that the purpose of $REQUIRE is to ensure that a dependency is loaded first.) This does not extend to files loaded through $INCLUDE; for these AbaPerls does not perform any version checks. 

For assemblies, AbaPerls version-checks both the .assem file and the input for the assembly, that is, the DLL or the source file. See the CLR Page for more details.

For files of the types .sp (stored procedures) and .sqlfun (user-defined function) AbaPerls defers the check until it has examined the code to see what object the code defines, and looks up abasysobjects.ss_version using the object name as key. This permits you to enter a test version of a procedure and load it directly from disk. (Note that you will need to use the ‑force option in this case, as AbaPerls normally requires the object name to match with the file name.) This does not apply when a file is loaded through $REQUIRE; for the $REQUIRE the look-up is always on file name.

When verifying that the directories in version-control are the same, AbaPerls checks the path to the repository and the path within the repository separately. The latter must be the same. The path to the repository is a different matter, since the same repository can be referred to in a multitude of ways. For instance, one developer may refer to a SourceSafe database using a mapped drive, whereas another developer uses a UNC path. In order to verify that these paths are the same, AbaPerls tracks an ID for every repository. The ID is derived from within the repository itself. The ability to track repositories by IDs was added to AbaPerls in version L1.0.0250, and ID may therefore be missing from the database, or from older versions of the files SS‑FILES.LIS and SUBSYSTEMS.LIS. If any of the IDs are missing, AbaPerls assumes the best and compare the paths within the repository only.

As noted above, AbaPerls stores an MD5 hash in abasysobjects. This was introduced since with TFS, the checks based on version number and paths cam lead to a large number of false positives with merging forth and back. Observe the following:

• The MD5 hash in abasysobjects was introduced in L1.0.0313 of AbaPerls, and for files loaded before this version there is no MD5 hash to compare with.
• The MD5 hash for a file will typically be different if the file was loaded directly from version-control or if it was bounced over disk with ‑noexec ‑get.
• ABASQL does not look at the MD5 hash, but always performs the checks above.
• If you use SourceSafe keywords in your file, they are likely to expand to different values in different versions why the hash will always be different.

Pre-SQL Analysis: Creating and Recreating Objects

AbaPerls does not permit two subsystems to use the same name for an object. That is, if you first create a stored procedure some_sp in subsystem A and then try to use the same name in subsystem B, AbaPerls regards this as error, and does not override the definition of some_sp in A. There is a way to suppress this check, but only in update scripts.

This table shows exactly what AbaPerls performs for each command. Note: when I say that AbaPerls generates a DROP, it is tacitly understood that the DROP is only executed if the object exists.

Commands not listed in this table are passed as-is to SQL Server.

If AbaPerls in the same file finds ALTER TABLE, CREATE TRIGGER or CREATE INDEX that applies to different tables, this is a breach against the rules for table files in the AbaPerls SQL directory structure. This is a fatal error and AbaPerls does not process the file any further.

Formatting rules:

• The commands, together with the following name, must be on one line and be the first words on that line. (Leading white-space permitted.)
• ...except for ALTER TABLE tbl ADD CONSTRAINT where CONSTRAINT should appear first on the next line.
• Names must not include schema or database components. All objects must be in the dbo schema in the current database.
• You are advised to restrict yourself to names with regular SQL identifiers, and avoid names quoted with "" or []. AbaPerls attempts to handle them, but testing has not been rigorous, and there may still be holes. (When you define CLR object, you may want to use quoted names in the EXTERNAL NAME clause. This is tested and tried.)
• In SQL Server, it depends on the database collation whether identifiers are case-insensitive or not; the default is case-insensitive. This is different with AbaPerls. AbaPerls is strictly case-sensitive when it analyses code or matches identifiers with file names. Similarly, AbaPerls thinks that quoting with "" or [] should be applied consistently.
• SQL Server permits you to use AS in the definition of procedure and function parameters as in @par1 AS int. Don't use this with AbaPerls, who thinks that AS is a great place to insert a SET NOCOUNT ON or WITH ENCRYPTION. (The syntax errors you get are very mystifying.)
• For CLR objects where AS is permitted in the syntax, you must supply AS and it must be on the same line as EXTERNAL NAME. (AbaPerls will give you an explicit error if it finds EXTERNAL NAME without an preceding AS.)
• Note that AbaPerls has not been updated for all syntax added in SQL 2014 and later versions, and you cannot expect such features to be handled properly. This does not always mean that you will get an error message, but AbaPerls may simply do the wrong thing. Newer features that are supported includes temporal tables and the index option OPTIMIZE_FOR_SEQUENTIAL_KEY.

Here are some examples of what AbaPerls understands:

CREATE PROCEDURE my_sp @a int,
                       @b datetime AS

CREATE TRIGGER ap_sla_ins_upd_tri
              ON abasslabels FOR INSERT, UPDATE AS

ALTER TABLE abainstallhistory  ADD
   CONSTRAINT ap_inh_ap_sla FOREIGN KEY (ss_label)
      REFERENCES abasslabels (ss_label)

CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS

And here are some examples of what will confuse AbaPerls:

CREATE PROCEDURE
            name_on_next_line AS

CREATE FUNCTION dbo.SalesByStore (@storeid varchar(30))
-- "dbo." should not be there.
RETURNS TABLE
AS

CREATE INDEX one_ix ON Sometable (somecol)
go
CREATE INDEX two_ix ON SomeTable (othercol)
-- AbaPerls considers "Sometable" and "SomeTable" to be different
-- tables and will flag this is an error.

Don't expect any useful error message if you don't obey to the formatting rules. You may not even see an error message first-time round.

Checks on Conformance to the Subsystem Structure

AbaPerls verifies that the object(s) in the file agree with the name of the file. This check is case sensitive, so if you have a file named my_own_sp.sp, and the file includes:

CREATE PROCEDURE My_own_sp AS

Msg 0 Level 16, Line 1, E:\TEMP\My_own_sp.sp
Object name 'my_own_sp' does not match file name My_own_sp.sp. Use -force to override.
AbaPerls SQL Analysis of E:\TEMP\My_own_sp.sp resulted in 1 error.

You can override this check for stored procedures and user-defined function by using the ‑force option. You will still get a warning, but the object is created. For other type of objects, the file name and the object name must match, period, and you cannot use ‑force to override.

If you quote your identifiers (despite the caveat above), you should not include the quote in the file name. That is, if you have:

CREATE TABLE [Order Details] (...

The name of the file should be Order Details.tbl.

For complete information on these checks, see the page on the AbaPerls SQL directory structure.

In the same vein, AbaPerls issues an error if an object appears in a file of which the extension does not match with the type of the object according to the AbaPerls SQL directory structure. You cannot override this check with ‑force.

AbaPerls also checks that a file which is supposed to define an object actually does so. If the file is non-blank after stripping comments and preprocessing, but there are no objects defined in it, this is an error.

Pre-SQL Analysis: Temp Tables and Table Variables

When AbaPerls finds a temp table, a table variable, the declaration of a table type or a schema declaration for OPENXML, AbaPerls makes two modifications to the table definition:

• All user-defined data types are replaced with their definitions (sans rules and defaults).
• For columns with a character datatype (char, varchar, nchar, nvarchar, text, ntext), AbaPerls adds the clause COLLATE database_default, unless there already is a COLLATE clause for the column.

Example: this temp table comes from the AbaPerls procedure ap_sob_report_suspects_sp:

CREATE TABLE #missinginsys(name      sysname         NOT NULL,
                           objtype   ap_description  NOT NULL,
                           loadtime  datetime        NOT NULL,
                           subsystem ap_subsystem    NULL,
                           ss_label  ap_sslabel      NULL,
                           username  ap_ntname       NOT NULL,
                           hostname  ap_ntname       NOT NULL)

This is how the table looks after AbaPerls have replaced the datatypes and added COLLATE clauses.

  CREATE TABLE #missinginsys ( name nvarchar(128) COLLATE database_default NOT NULL ,
 objtype varchar(30) COLLATE database_default NOT NULL ,
 loadtime datetime NOT NULL ,
 subsystem varchar(30) COLLATE database_default NULL ,
 ss_label varchar(15) COLLATE database_default NULL ,
 username nvarchar(40) COLLATE database_default NOT NULL ,
 hostname nvarchar(40) COLLATE database_default NOT NULL ) 

The first modification makes it possible for you to use user-defined data types in temp tables and table variables without restrictions. The insertion of the COLLATE clause makes you virtually independent of the server collation, so you can mix databases with different collations on the same server and still use temp tables without problem. (The lost indentation is due to how AbaPerls parses and reconstructs table definitions.)

Furthermore, when AbaPerls finds a temp-table creation – with CREATE TABLE or SELECT INTO – AbaPerls extracts the temp-table definition and adds the creation staftement to the pre-work code, as well as a matching DROP statement to the post-work code. This nullifies the effect of deferred name resolution a serious misfeature in SQL Server where SQL Server stays silent at compile time if a table in an SQL statement is missing. Furthermore, missing tables often prevent from SQL Server from investigate other errors. For instance, through SSMS, you create a stored procedure and there is a join between a temp table and a permanent table, and you have misspelled a column name in the permanent table. SQL Server will not report the missing column due to that the temp table is non-existing at the time you create the procedure. By adding creating the temp table before issuing the CREATE PROCEDURE statement, AbaPerls lures SQL Server to perform full checks on queries with temp tables.

Please observe that while extracting CREATE TABLE is a simple game for AbaPerls, extracting SELECT INTO is considerably more difficult and this is more prone to fail, resulting in an error message which includes the full SELECT INTO statement as AbaPerls extracted it. For instance, this happens if the query includes a table variable or a CTE (Common Table Expression). If you run into such a problem, your best bet is to replace SELECT INTO with an explicit CREATE TABLE + INSERT. (Which is probably what you should have written in the first place.)

Pre-SQL Analysis: Reloading a Table File

When you load a .tbl file and there is data in the existing table, it is of course not feasible to drop and recreate the table. The same applies when the table is referred to by a foreign-key constraint or by a function or a view created WITH SCHEMABINDING. (SQL Server will not permit the table to be dropped in this case.)

If ABAPERLS subsystem is installed in the database, and the version is at least L1.0.0270, AbaPerls will change the CREATE TABLE statement so that the table is created in a temporary schema to be able to compare the definition in the source file with the database definition. If they are different, AbaPerls investigates whether it is possible to use ALTER TABLE or other statements to make the existing table to align with the definition in the file. If this is not possible, AbaPerls consider this to be an error, as it is not able to fulfil the contract. This may be slightly irritating when you attempt to load a .tbl file through ABASQL, since the difference is not necessarily a user error. But it is essential that you get an error message when the .tbl file is loaded by an update script or through DBBUILD with one of the options ‑rebuild and ‑restruct, so that you see the error when you view the log with LISTERRS. Note that if you get an error message about the tables being different or ALTER TABLE failing, this gives you an acknowledgement that the .tbl is correct syntactically, as else AbaPerls would have been able to compare the two definitions.

AbaPerls prints all statements – ALTER TABLE – and others, it executes to implement the change so that you seem in the log. An error message follows directly on the statement that failed.

There are many properties associated with tables, and AbaPerls only considers properties that AbaPerls expects to appear in a .tbl file. The main meat is of course columns and constraints, save for FOREIGN KEY constraints (since foreign key constraints should be defined in .fkey files and are not permitted in .tbl files) AbaPerls also understands various storage settings for filegroup, compression etc. As this is a fairly advanced topic, this is discussed on a separate page, Storage Settings for Tables and Indexes, and not further discussed here. AbaPerls does not consider settings for Change Tracking, Change Data Capture, replication or anything else discussed in this topic or the storage-settings page.

AbaPerls first checks if the column names are the same in the two tables, and whether the order is the same. If this not is the case, the following applies:

• If the column order is different, this is an error, as ALTER TABLE cannot be used to change column order.
• If there are columns that are unique to both sides (for instance column c1 is only in the new definition and column c2 is only in the old definition), this yields an error, as AbaPerls cannot determine whether columns have been both added and dropped, or whether you intend to rename a column.
• If there are columns that exist only in the new definition, and they are the last columns in that table, AbaPerls will add these columns with ALTER TABLE. If the new columns are elsewhere in the table, this is a failure.
• If there are columns that exist only in the old definition, AbaPerls will issue an ALTER TABLE DROP COLUMN command for these columns. Note that this command will fail if there is a dependency on this column, for instance an index. (Since the index is defined in a different file, AbaPerls cannot drop the index at this point.)
• If the setting of ANSI_NULLS is different from when the table was created, this is an error.

When adding a column, AbaPerls considers all properties that can be added with ALTER TABLE ADD with exception for the NOT FOR REPLICATION clause.

AbaPerls compares all columns to see if any has changed. AbaPerls considers the column properties below. If the item is followed by ALTER, this means that AbaPerls will attempt to use ALTER TABLE to transfer the difference, Error means that AbaPerls will give up and raise an error, typically because ALTER TABLE is not possible.

• Data type, including length, precision and scale. (ALTER)
• Schema collection and DOCUMENT/CONTENT property for an xml column. (ALTER)
• Collation. (ALTER)
• Nullability for a regular column. (ALTER)
• Nullability for a computed column. (Error)
• IDENTITY, including seed and increment. (Error)
• ROWGUIDCOL property. (Error)
• SPARSE property. (ALTER)
• FILESTREAM property. (Error)
• Regular/computed. (Error)
• Definition of a computed column. (Error, see also below)
• PERSISTED property for a computed column. (ALTER)
• Change to/from being a column set for sparse columns (Error)
• Different settings of ANSI_PADDING when the two tables were created. (Error)
• HIDDEN property for period columns in a temporal table. (ALTER)
• Adding or dropping a period for SYSTEM_TIME. (ALTER)

If the altered column is referred to by a CHECK or DEFAULT constraint, AbaPerls drops and recreates the constraint. If the column is indexed, AbaPerls does not drop and recreate the index, as this could be a costly operation. AbaPerls does not consider properties related to replication. That is, AbaPerls will not detect any differences in such settings.

Note: you may find that AbaPerls errors out, saying that a computed column has changed despite you have not touched it. This can happen if the column was created under compatibility level 100 (SQL 2008) or lower, and the compat level now is higher. This is because SQL Server does not save the expression for the computed column as-is, but normalises the expressions. AbaPerls tries to cater for this by adding the column in the original table as a temporary column in the table in the temporary schema so it can compare the text. However, this may still fail if the computed column uses convert(). Say that you have a column with the definition col AS convert(bit, 1). SQL 2008 stores this as (CONVERT([bit],(1),0)) where as in SQL 2012 stores it as (CONVERT([bit],(1))). Note that ,0 is missing here. However, if the ,0 is included, SQL 2012 does not remove it. For this reason, you can in most cases avoid the load error by adding the convert style ,0 to the table definition. The exception is the types date, time, datetime2 and datetimeoffset when the source is a string, since in this case the conversion produces different result with or without ,0.

As with columns, AbaPerls compares CHECK, DEFAULT, PRIMARY KEY and UNIQUE constraints and drops them and creates if they have changed. The following notes apply:

• An unnamed CHECK constraint will be dropped and recreated every time, since the system-generated name is different each time. AbaPerls thinks that you should name all your constraints explicitly.
• AbaPerls adds all CHECK constraints as enabled and uses the WITH CHECK clause to validate the constraints, no matter their current status. This includes constraints that are dropped and re-added because of a changed column.
• NOT FOR REPLICATION is not considered for CHECK constraints.
• An unnamed PRIMARY KEY or UNIQUE constraint will not be dropped and recreated each time – AbaPerls compares them by their definition.
• If the only difference for a PK/UNIQUE constraint is the name, AbaPerls uses sp_rename to change the name, and avoids dropping the index.
• When comparing PK/UNIQUE constraints AbaPerls considers the columns and the ASC/DESC setting for each column.
• As for the options controlled by the WITH and ON clauses for a key constraints, see the page Storage Settings for Tables and Indexes.
• Note that the change of the columns in a PK/UNIQUE constraint will fail if there are referencing foreign keys.
• If you change the setting for the option IGNORE_DUP_KEY, this yields an error, as SQL Server does not permit this setting to change for a key, only regular indexes.
• If the constraint in the existing table was added by AbaPerls through a different file than the .tbl file that created the table itself, AbaPerls will not drop that constraint. Note that if the constraint was created entirely outside AbaPerls and not recorded in abasysobjects, AbaPerls will drop the constraint.

If the table reload does not succeed, you can use a update script generated by DBUPDGEN to bring the database in line with your source file.

For details specific to temporal tables, see the page for temporal tables.

Note: as noted above, ALTER TABLE is not employed if the table is empty and not referred to by foreign keys. Sometimes you may want to force an attempt ALTER TABLE to verify that your change is actually compatible with AbaPerls. This is possible in update scripts generated by DBUPDGEN, see further the topic Forcing ALTER TABLE in abasqlfile on this page.

Pre-SQL Analysis: Supplemental SQL Checks

AbaPerls performs a couple of supplemental checks for best practice or to enforce conformance with AbaPerls conventions. Depending on the check, AbaPerls flags the violation in one of the three ways below:

• Warning only. All tools give a warning with level 9, meaning that a developer who uses ABASQL will be nagged, but the file will load successfully. When you use LISTERRS to read a log from DBBUILD or update scripts from DBUPDGEN, the warning will appear depending on what you specify for the second argument, min-severity. The default is 10 for logs from DBBUILD and 9 for logs from DBUPDGEN; thus by default you will not see warnings in logs from DBBUILD, but well in logs from update scripts. AbaPerls uses advisory warnings for bad practice that is only mildly harmful.
• Always error. AbaPerls uses this for constructs that are outright inappropriate or which would violate the presumptions AbaPerls works from.
• Error or warning. You get an error with ABASQL which you can change into a warning by using the ‑force switch. You also get an error with update scripts generated by DBUPDGEN after the check was introduced. With DBBUILD and update scripts generated by DBUPDGEN before the check was introduced, you get a warning with level 9. Note that if an existing script is regenerated with DBUPDGEN, it still falls under the latter category; that is, the check yields a warning. AbaPerls uses this model for new checks to give you a grace period so that you can change existing code. You can count on that in some future version of AbaPerls, that the syntax in question will yield an error with all tools.

Use of TOP with INSERT, UPDATE, DELETE and MERGE

Starting with SQL 2005, SQL Server permits to use the TOP keyword with DML statements (INSERT, UPDATE, DELETE and MERGE). For instance you can say:

UPDATE TOP (@rows) tbl
SET    col =
FROM ....

If you attempt to use this syntax, AbaPerls produces an error message for two reasons:

1. This is a feature of dubious value, because there is no way to control which rows that get updated.
2. The syntax makes it very difficult for AbaPerls to find the target table of the statement and verify its existence.

If you want to perform a batched INSERT, you should put the TOP clause in the SELECT statement instead. For an UPDATE, DELETE or MERGE you could use the pattern below:

WITH CTE AS (
     SELECT col, ... rowno = row_number() OVER(ORDER BY yourcriteria)
)
UPDATE CTE
SET   col = ...
WHERE rowno <= @rows

to get a predictable statement.

Note: This check is performed as part of Object Checking, and AbaPerls will only flag this as an error if SQL Server accepts the file. However, in difference to other errors reported by Object Checking, this error is not downgradable with the ‑force option.

SET ROWCOUNT

Microsoft has deprecated the use of SET ROWCOUNT in combination with INSERT, UPDATE, DELETE and MERGE but curiously not with SELECT. AbaPerls is one step ahead and deprecates SET ROWCOUNT across the board. To permit a smooth transition, AbaPerls produces an error or a warning as discussed above whenever SET ROWCOUNT is encountered.

Instead of SET ROWCOUNT, use SELECT TOP. Note that to use a variable with SELECT TOP, you need to enclose it in parenthesis:

SELECT TOP(@noofrows) col1, col2 FROM tbl WHERE ...

= NULL

While this it's legal SQL to say IF @x = NULL, it's rarely meaningful. Since NULL is an unknown value NULL can never be equal to anything, not even another NULL. Or for that matter unequal of anything. Per the tri-state logic of SQL, all comparisons with NULL have the value UNKNOWN.

Since this is a common mistake by inexperienced SQL programmers, AbaPerls checks for this mistake and always flags it as an error.

Note: this check is performed as part of Object Checking, why AbaPerls will not flag incorrect NULL usage, if SQL Server finds errors in the file. However, in difference to other errors reported by Object Checking, this error is not downgradable with the ‑force option.

Checks for CREATE TABLE

AbaPerls performs a number of checks for table definitions, both CREATE TABLE for permanent tables and temp tables, and table variable and return tables for table-valued functions:

• Constraints in permanent tables should be named. This is an error or warning depending on the tool. (Unnamed constraints will result in unnecessary noise in the output from ap_sob_report_suspects_sp).
• Constraints in non-permanent tables should not have a name. This is an error or a warning depending on the tool. (Naming a constraint in a temp table, means that only once instance of the procedure can execute at any one time. SQL Server 2012 considers this to be an error if the database is partially contained.)
• Foreign-key definitions must not appear in table definitions at all, as AbaPerls wants them to be in separate .fkey files. This is always an error.
• A column should always have a NULL or NOT NULL indicator, since the default whether a column is nullable depends on SET options. (IDENTITY and SPARSE columns are exempted, since IDENTITY implies NOT NULL and SPARSE implies NULL. This is always flagged as a warning.
• For columns of the types char, nchar, varchar, nvarchar, binary and varbinary, the maximum length should always be explicitly declared. It is a common error by inexperienced SQL programmers to leave out the length, thinking the length is unlimited, when default is 1. This is a warning or an error, depending on the tool.
• Columns of the types decimal and numeric should always have explicit precision and scale. Again, inexperienced SQL programmers may not be aware of that the defaults are and 18 and 0 respectively and improper use could lead to data loss. This is an error or warning, depending on the tool.
• Columns of the types datetime2, time and datetimeoffset should always have an explicit scale. This is always a warning. (The default is 7, so there is no risk for data loss, but columns may take up more space than needed. As long as you don't get timestamps from outside SQL Server, you don't have much use for a scale > 3.)

EXECUTE AS

SQL 2005 permits you to add the clause WITH EXECUTE AS to a stored procedure or any other module. This causes the procedure to execute in the context of some other user. This is a very dangerous clause, as it can wreak havoc of auditing and row-permissions schemes based on SYSTEM_USER and similar functions. For this reason AbaPerls does not permit the use of this clause at all.

Note: EXECUTE AS can also be a statement or appear as a part some Service Broker statements, e.g. ALTER QUEUE. All such usages are permitted. It's only WITH EXECUTE AS in the declaration of stored procedures and other modules that is not permitted.

THROW

If AbaPerls sees BEGIN/SAVE/COMMIT/ROLLBACK TRANSACTION THROW, AbaPerls calls this an error, since most likely you have forgotten to insert a semicolon before the THROW statement. If you intended to actually call your transaction THROW, you will need to think of a different name to use.

Private Names

AbaPerls considers a name that starts with a $ character and then includes one more $ character to be a private name. The second $ may be the last character in the name, or there may be a suffix.

A private name may only occur in a file that defines a stored procedure or a user-defined function and the part of the private name between the two $ characters must agree with the name of the procedure/function. Private names are intended for table types or XML schema collections to be used by one single stored procedure for a parameter. AbaPerls vigorously checks that these names are not mistakenly used in other modules, as this could cause problems in update scripts.

That is, this is not legal:

CREATE PROCEDURE my_proc AS
DECLARE @tbl [$her_proc$]
INSERT @tbl ...
EXEC her_proc @tbl

If you want to use the same table type in two stored procedures, you should declare it in a .tbltyp file that you put in the TYPE directory.

Check that $DEPENDSON is Present

AbaPerls checks if $DEPENDSON is present in the cases below:

• A stored procedure of a function that takes a table-valued parameter that is defined in a .tbltyp file, should have $DEPENDSON to that file.
• A file defining triggers, indexes of foreign keys on a table in a different subsystem, should have a $DEPENDSON to the .tbl file defining the table.
• An object defined WITH SCHEMABINDING should have $DEPENDSON (or $REQUIRE) to all objects it is dependent on.

If AbaPerls finds that $DEPENDSON is missing, it issues an error or warning depending on the tool.

Pre-SQL Analysis: Permissions on Objects

Normally you do not put GRANT statements directly in the .sp, .tbl files etc. AbaPerls permits you to set up rules for permissions in files called grant.template. A file with this name contains GRANT, REVOKE and DENY statements where you can replace the object name with a Perl regular expression. The GRANT/REVOKE/DENY statement should be followed by a comment that contains the keyword for the type of objects the line applies to. AbaPerls executes all statements where the current object matches the regular expression and the object type. Here is a simple example:

GRANT SELECT  ON .* TO dvp --  TABLE
GRANT SELECT  ON .* TO dvp --  VIEW
GRANT EXECUTE ON .* TO dvp --  PROC
GRANT EXECUTE ON .* TO dvp --  FUNCTION

This file gives the group dvp SELECT permission on all tables and views and EXECUTE permission on all procedures and functions. The dot before the asterisk means "any character". The asterisk means "zero or more times".

If more than one line that matches the same object, AbaPerls executes the lines in the order they appear in the file. The effect of this example is that the group fond_usr gets EXECUTE permissions on all stored procedures, save those that start with zz_:

GRANT  EXECUTE ON .*    TO   fond_usr
REVOKE EXECUTE ON zz_.* FROM fond_usr

As you can see from this example, PROC is the default for the object type if you leave it out.

This system is devised mainly to support simplistic permission models, but by the use of regular expressions, there is space for greater flexibility. You find a crash-course in regular expressions on the page for SSREPLACE. A few notes about the regular expression that are specific to grant.template:

• AbaPerls adds an initial ^ and a closing $ to the regular expression. They match start and end respectively, so that the pattern you specify must match the entire object name, and not only a substring. For instance, the pattern zz_.* above matches zz_cleanup_sp, but does not match fuzz_sp.
• The regular expression must not include spaces. If you want to match space, you can use \s (which matches any white-space) or \_ which is a special AbaPerls convention).

To grant permission on some type of objects, SQL Server requires you prefix the object name with the "object class". This applies to for instance to (table) types and XML schema collections. That is, the SQL syntax to grant EXECUTE permission on a table type is:

GRANT EXECUTE ON TYPE::mytabletype TO some_user

However, in grant.template you should not include this prefix; AbaPerls will add it for you. (If you add it, AbaPerls will not be able to match the line.) Thus, the line in grant.template should read something like:

GRANT EXECUTE ON .* TO some_user  -- TYPE

When you load a file in a subsystem, AbaPerls looks for grant.template in two places: 1) The version directory (global). 2) In the top directory of the AbaPerls SQL directory structure (local). AbaPerls looks up these files according to the AbaPerls file-lookup order, with one twist: when reading from version control, AbaPerls always gets the latest version of the files, no matter which label you load the subsystem from.

If there is both a global and a local grant.template, AbaPerls will read both files and apply them as they were a single file, with the lines from global file first. If you have a version-before-subsystem structure, this permits you to have a single grant.template that is common for all subsystems. If a particular subsystem needs different permissions, you can supplement the rules in the global file in a local grant.template.

Say that there is a system in $/Project/OurSystem/1.00 and there is a file $/Project/OurSystem/1.00/grant.template which contains this line:

GRANT SELECT ON .* TO our_users -- TABLE

That is, generally the role our_users has SELECT permission on tables. In the subsystem MAIN and EXTRA and there is no $/Project/OurSystem/1.00/Main/SQL/grant.template or $/Project/OurSystem/1.00/Extra/SQL/grant.template. However, you don't want users to be able to access table in the AUDIT subsystem, why there is a $/Project/OurSystem/1.00/Audit/SQL/grant.template containing this line:

REVOKE SELECT ON .* TO our_users -- TABLE

That is, by using REVOKE you can undo the permissions in the global grant.template.

It might be the case that the subsystem is so special that you don't want the permissions in the global grant.template to apply at all. In this case you can put this line first in the local grant.template:

$NOINHERIT

This instructs AbaPerls to ignore the global file.

While $NOINHERIT may look like a Preppis macro, it really isn't. More specifically, neither of the two grant.template are run through Preppis, so you cannot use $IFDEF etc in these files. Note that $NOINHERIT must appear on the very first line in the file; it cannot be preceded by comments or blank lines.

AbaPerls thinks that using a global grant.template is a good idea, so if there is no file in the version root, you will get a warning. You will not get a warning if there is no local file.

When you have a install kit created with ‑noexec ‑get, there is no version root. In such a kit, the global file exists directly under /subsystem and thus is duplicated. Since this is a generated structure, this should not be much of a problem. If you want to manually set up a pseudo kit as discussed under the AbaPerls File-Lookup Process above, you will need to duplicate the file in the same way.

Caveat: if you remove a line from grant.template, this will have no effect when you reload existing procedures, functions and views with ABASQL, since AbaPerls changes CREATE to ALTER, in which case all existing permissions are retained. However, you can use DBBUILD ‑rebuild with the ‑revokeall to implement the change.

Pre-SQL Analysis: Other Miscellaneous Fixes

• If AbaPerls finds a CREATE TABLE for a temp table outside a procedure or a trigger, AbaPerls adds an explicit DROP to the post-work code so that the temp table does not hang round when you run DBBUILD or an update script generated by DBUPDGEN.
• If AbaPerls finds the string suser_name(), AbaPerls will replace this with SYSTEM_USER, since suser_name() at least for a while was deprecated and could return NULL.
• For triggers (with the exception of DDL triggers), AbaPerls inserts IF rowcount_big() = 0 RETURN directly after the first AS it sees to prevent the trigger code from running with no actual work to do. If you for some reason want the trigger code to always run, you can override this by adding REVERT as the first command after AS on the same line. If AbaPerls sees this combination, it will not add this quick exit. (The REVERT command is a no-op, when there has not been an EXECUTE AS before in the same scope.) Note that this check can still cause a trigger to run with no data when fired by a MERGE statement, since with MERGE, rowcount_big() returns the total number of rows affected.
• AbaPerls inserts SET NOCOUNT ON as the first command in a stored procedure and as the second statement trigger (after the check on rowcount_big() in the previous point.). This is because most clients makes no use of the DONE_IN_PROC messages (which corresponds to the "rows affected" lines you see in a query window), that are generated when NOCOUNT is OFF. They cause an extra load on client, server and network. They may also confuse ADO clients that have not foreseen them. Thus, if you want this "rows affected" messages sent back to the client, you need to insert an explicit SET NOCOUNT OFF in your code. AbaPerls will remove any SET NOCOUNT ON there already is in the procedure, unless it has seen SET NOCOUNT OFF.
• If the configuration option ‑crypt is active, AbaPerls inserts WITH ENCRYPTION in the definition of stored procedures, views, triggers and functions. This is not applied for CLR objects.
• If the configuration option ‑quoterepl is in effect, AbaPerls replaces double quotes that are string delimiters with single quotes. (See the description of ‑quoterepl for details.).

Retrieving Name of Current Subsystem

AbaPerls creates the temp table #current$subsystem:

CREATE TABLE #current$subsystem(subsystem varchar(80) NULL)

There is one row in this table. The value of the column subsystem is the name of the current subsystem or NULL, if none has been defined. You can use this table to retrieve the name of the current subsystem in your T‑SQL code. The most likely use for this is in an INSERT-file. Say that you have a pre-loaded table to which several subsystems add data. To keep track of which subsystems which own which rows, you decide to add a subsystem column to this table, and with help of this temp table you can arrange for this column being filled in automatically. (The best way to do this would be to have a common stored procedure that loads data into this table; see the page for INSFILGEN for an example.)

Copyright © 1996-2021, Erland Sommarskog SQL-Konsult AB.
All rights reserved. AbaPerls is available under Perl Artistic License
This page last updated 21-12-29 18:02