INSFILGEN

INSFILGEN reads an Excel workbook and generates a file of stored- procedures calls to install pre-loaded data. In the workbook you can specify that calls are to be executed or not depending on Preppis macros.

INSFILGEN has a special mode for so-called name tables. This is very AbaSec-specific, and thus this mode is called LANGINSGEN, and is described on its own page.

Contents:
    Command-Line Syntax
    Background and Examples
    Overview of the Excel Book
    The Config Sheet
    The Definition Sheet
    The Macros Sheet
    The Values Sheet
    How to Retrieve the Example

Command-Line Syntax

Call:

insfilgen file.xls

file.XLS should be an Excel workbook of which we detail the layout in coming sections. The output from INSFILGEN is a text file with the name file.INS and has a standard SourceSafe header. If there is an existing file with this name, INSFILGEN maintains the SourceSafe header from the file, and regenerates the rest of the file. No backup copy of the old file is saved.

Background and Examples

Before we proceed to describe the Excel workbook, it may be in place an explanation of what this tool is good for. The description of the sheets in the workbook will refer back to the example in this section. (At the end of this document you find the instructions to retrieve to all files involved in this example.)

Assume that in your system that you have two tables functions and functionsettings:

CREATE TABLE functions (functionid   int          NOT NULL,
                        functionname varchar(50)  NOT NULL,
                        extfile      varchar(255) NULL,
                        CONSTRAINT pk_functions PRIMARY KEY (functionid))

CREATE TABLE functionsettings
             (functionid int         NOT NULL,
              setting    varchar(15) NOT NULL,
              intvalue   int         NULL,
              charvalue  char(6)     NULL,
              CONSTRAINT pk_settings PRIMARY KEY (functionid, setting))

ALTER TABLE functionsettings ADD
   CONSTRAINT fk_settings FOREIGN KEY (functionid)
      REFERENCES functions (functionid)

functions defines the GUI functions in your system, and a function will only be available if it is listed in functions. Whether a function is available is customer-specific, depending on what they have paid for. functionsettings defines optional behaviour within a function. A setting could for instance determine whether a certain checkbox is visible or not. Again, this is customer-specific. The setting can be an integer value, a character value, or a combination of both. The column functions.extfile specifies that this particular function is not included in the main executable, but in a separate DLL; this is a custom-independent attribute of the function.

To load these two tables you one would have INSERT-file for each table, FUNCTIONS.INS and FUNCTIONSETTINGS.INS, so that they would be loaded when you build an empty database with DBBUILD. To control at which customer sites a function is loaded, you would use Preppis macros.

Your first idea of such a file may be just a bunch of INSERT statements. That works the first time you build the database, but when you run it later on to add new function, you will get a lot of primary-key violations. Of course you could use IF EXISTS to prevent this, but there is still the issue how you propagate changes in functionname or extfile. Or how you delete a function, when it is now longer in use.

So in the end it is better to have a stored procedure do to the job. This SP would look something like this:

CREATE FUNCTION fun_install_sp @functionid  int,
                               @functioname varchar(50),
                               @extsys      varchar(255) = NULL AS
IF NOT EXISTS (SELECT * FROM functions ...)
   INSERT functions ...
ELSE
   UPDATE functions ...

(Follow the link if you wish to see the full text of the procedure.)

Thus FUNCTIONS.INS would contain a number of stored-procedure calls like:

$IFDEF &all_customers
   EXEC fun_install_sp @functionid   = 1,
                       @functionname = 'Add user'
$ENDIF

$IFDEF &cust_HP or &cust_IBM
   EXEC fun_install_sp @functionid   = 191,
                       @functionname = 'Kitchen sink',
                       @extfile      = 'KITCHEN.DLL'
$ENDIF

By using $IFDEF you control which customers that get which functions.

For FUNCTIONSETTINGS.INS it is a good idea to have on SP to install the setting itself, and this SP is smart enough to not attempt to install a setting if the function is not present in the database. Then you use another SP to set the customer-specific values. The file could look like this:

$REQUIRE functions.ins

EXEC funset_install_sp @functionid = 1,
                       @setting    = 'SHOW_ADDGROUP'

EXEC funset_install_sp @functionid = 191,
                       @setting    = 'MATERIAL',
                       @charvalue  = 'STAINLESS'

$IFDEF &cust_Dell
    funset_set_value_sp @functionid = 1,
                        @setting    = 'SHOW_ADDGROUP',
                        @intvalue   = 1
$ENDIF

$IFDEF &cust_HP
    funset_set_value_sp @functionid = 191,
                        @setting    = 'MATERIAL',
                        @intvalue   = 4711,
                        @charvalue  = 'CHROME'
$ENDIF

The purpose of the $REQUIRE directive, is ensure that we have correct contents in functions before we start. As a consequence of this, FUNCTIONS.INS needs to include a $USEDBY directive for FUNCTIONSETTINGS.INS.

Let's add one more dimenstion to this. HP is interested in function 165, Bill of materials, but they want to evaluate it before they buy it. So you agree to install the function in their test environment. One way to address would be to have a macro &cust_HP_test, but this doubles the number of macros to keep track of, or even worse if a customer has more than one test environment. So rather you have a general evaluation macro &EVAL which you use like this:

$IFDEF &cust_Dell or (&cust_HP and &EVAL) or &cust_IBM
   EXEC fun_install_sp @functionid   = 165,
                       @functionname = 'Bill of materials'
$ENDIF

Likewise, if it is only a setting of a certain function they are evaluating:

$IFDEF &cust_HP
    funset_set_value_sp @functionid = 1,
                        @setting    = 'SHOW_ADDGROUP',
         $IFDEF &EVAL
                        @intvalue   = 1
         $ELSE
                        @intvalue   = 0
         $ENDIF
$ENDIF

Here HP gets the SHOW_ADDGROUP setting for evaluation only.

There is one final thing that you need to account for: a function may be taken out of service, either completely or at a certain customer site, for instance because the customer declined after evaluation. Thus your FUNCTIONS.INS would also include these lines.

$IFDEF not (&cust_Dell or (&cust_HP and &EVAL) or &cust_IBM)
   EXEC fun_delete_sp @functionid = 165
$ENDIF

EXEC fun_delete_sp @functionid = 90000

fun_delete_sp would be intelligent to also delete the function from functionsettings. A twist of this is to have a @delflag parameter to fun_install_sp and have the DELETE logic within this SP, and you can see this in funset_install_sp.

You have now seen the framework to handle this situation. But as you may imagine, it's getting difficult to maintain these files manually if there are a lot of functions and/or customers. An Excel workbook gives better overview over which customers that have which functions and which settings. Then you generate the INSERT-files with the calls to the stored procedures with all the $IFDEFs and macros from the Excel sheet with INSFILGEN.

Overview of the Excel Book

When INSFILGEN reads an Excel book, it looks for these four sheets:

Config	Defines the names of the various stored procedures, key parameters etc. This sheet is mandatory.
Definition	In this mandatory sheet you enter all rows that are to be loaded for the table in question. From this data INSFILGEN generates procedure calls to install and delete the rows.
Macros	In this sheet you define which macros that are to apply to which procedure calls. This sheet is optional. If there is no Macros sheet, INSFILGEN will generate the procedure calls without $IFDEFs.
Values	In this sheet you define the settings that are dependent on macros. This data results in calls similar to funset_set_value_sp in the example in the previous section. This sheet is optional.

Normally you would not have both a Macros and a Values sheet, but there is no law against it.

You may have other sheets in the book beside these four. INSFILGEN will ignore these sheets. However, if you only have Config and Definition and there are other sheets, INSFILGEN will warn you, in case there is a typo.

The sheet names are case-insensitive. The order of the sheets have no importance.

In all four sheets there should be a cell which includes the word START (that and only that, and all uppercase). The actual data for the sheet starts in the cell to the right of START. This permits you to add comments in the rows above the START row and in the columns to the left of START, including the START column itself.

The Config Sheet

In the Config sheet you define a number of properties as well as maximum lengths for character columns. The sheet has two sections identified by START and MAXLEN. In the START section, the properties appear in the column to the right of the START column, and the values of the properties follow in the columns further to the right. Some properties are scalar, for which INSFILGEN only looks in the first column to the right of the property name. Other properties are lists, for which INSFILGEN reads all columns until it finds an empty cell. The START section cannot have any blank lines; as soon as INSFILGEN sees a blank cell in the properties column, it stops reading properties. The MAXLEN section starts with the word MAXLEN in the START column, and first column is the name of a character parameter and in the second the maximum permitted value for that parameter. As for START there cannot be any blank lines.

Here is how the Config sheet for FUNCTIONS.XLS looks like:

In this example, START, MAXLEN and the property and column names are in bold face. However, this has no importance for INSFILGEN.

Property names are case-insenstive. For all properties that are parameter names, you can omit the leading @.

Here are the properties you can define. All properites are optional, unless otherwise noted.

Scalar properties
Install_sp	Name of the SP that inserts/updates one row into the table. This property is mandatory.
Delete_sp	Name of the SP that deletes one row from the table. If you do not specify Delete_sp, INSFILGEN uses Install_sp also to delete a row. In this case the Install_sp must take a parameter of which the name agrees with the value of the property Delflag.
Value_sp	Name of the SP that sets the value for a row. This property is mandatory if the book contains a Values sheet.
Delflag	This property holds the name of the parameter that controls whether Install_sp inserts/updates a row or deletes it. Even if the Install_sp does not have such a parameter, it must still appear in the Definition sheet. The default value for this parameter is @delflag.
Optionalmacros	When this property is not set, all rows in the Definition sheet, save those that are marked as deleted, must have a corresponding entry in the Macros sheet, if this sheet exists. When set, this requirement is waived. By default, this property is not set, and all entries in Definition must appear in Macros.
Isnametable	If this property is set, the table is a name table,which is handled by LANGINSGEN, a sub-function of INSFILGEN.
List properties
Keys	This mandatory property defines the key parameters for Install_sp, Delete_sp and Value_sp. There is no way to specify that a parameter for the Install_sp is mandatory but not a key, so it might be a good idea to define such a parameter as a key.
Sortorder	This property defines in which order INSFILGEN should sort the procedures calls. You use this property when a table has a foreign-key relation with itself, so that rows that are referred to by other rows that are loaded first or deleted last.
Sortfactor	This property is mandatory if you have specified Sortorder. There should be one cell in Sortfactor for each cell in Sortorder. A sortfactor should be 1 or -1 to specify whether sorting is ascending or descending.
Depends_on	List of INSERT-files that this INSERT-file needs to $REQUIRE.
Referred_by	List of INSERT-files that $REQUIRE this file. This property results in one or more $USEDBY directives.
Prelude	List of lines of SQL code to be added first in the INSERT-file (after any $REQUIRE). For instance, in some INSERT-files, you may prefer to delete all existing rows before you load the rows. In this case you add a DELETE statement to the Prelude property.
Postlude	List of lines of SQL code to be added last in the INSERT-file.

The MAXLEN Section

Currently the MAXLEN section is not mandatory, however it is strongly recommended that you include one, and eventually MAXLEN will become mandatory. (MAXLEN was not in the original version of INSFILGEN, which is why is not mandatory now.) Once you have a MAXLEN section, all parameters in the Definition sheet with character data must appear in this section, else INSFILGEN will fail.

You can leave out the @ in parameter names.

When INSFILGEN generates the INSERT-file, it checks the string values against the declare string lengths, and if any string exceeds the length, the generation fails.

Obviously the maximum lengths you declare should correspond to those of the parameters in the stored procedures in Install_sp. The purpose is to avoid that you enter data that is truncated when you run the INSERT-file.

The Definition Sheet

On the START row you define all parameters that the Install_sp takes. The parameters defined by the Keys property must appear first (except for comments, see below), and in that order. If you have defined a Sortorder, INSFILGEN checks that these parameters appear, but there is no requirement on their order. Finally, the parameter defined by the Delflag property (default: @delflag) must be present, even if you use a separate Delete_sp and your Install_sp does not have such a parameter. The Delflag parameter can appear anywhere after the keys, but conventionally this is the last parameter. INSFILGEN stops reading the START row as soon as it finds an empty cell.

You can omit the @ in parameter names. (To enter a name starting with @ in an Excel cell, you will have to insert a single quote (') as an escape character, to prevent Excel from applying its own meaning of @.)

If a cell on the START row has a name starting with #, this column is a comment column. The comments that appear in this column will be included in the generated file. The comment columns may be mixed with the columns for the Keys.

On the rows following the START row, you define the table rows to be loaded. If a cell in the first column right of the START column is blank, that row is skipped. INSFILGEN stops reading rows when it has found five consecutive rows with empty cells in the key column. This permits you to insert dividers in the Excel sheet, as shown in this example, which is the Definition sheet for the functions table.

If you leave a cell blank, INSFILGEN will not include that parameter in the call to the Install_sp, but assume that there is a default value. For string data, you can surround the data with '', but this is optional in most cases. To wit, if INSFILGEN finds a non-numeric value in a column, it will assume that all values for that column are strings. However, I recommend that you always surround date and time values in single quotes, to prevent Excel from playing tricks with these. Since ' first in an Excel cell is an escape character, you actually have to enter two single quotes to get the opening string delimiter.

Restrictions:

You can not specify binary, money, decimal or float values, as INSFILGEN will consider such values as strings.
You cannot explicitly specify that a parameter should be set to NULL. INSFILGEN interprets NULL as a the string literal 'NULL'.
You cannot use double quotes to delimit strings. INSFILGEN will surround such strings in single quotes.

The Macros Sheet

On the START row you first list the parameters defined by the Keys property. As in the Definition sheet, these columns must appear first and in the order as they are defined in the property. You can leave out the leading @ in the names. Columns that are not part of Keys must not appear in Macros.

After the parameters in Keys, macro names follow. You can leave out leading & in macro names; INSFILGEN will add them. We call these macros column macros.

Here is how the Macros sheet for the functions table looks like:

In the rows below the START row follow the same values for the Keys as in the Definition sheet. (It may be a good idea to have formulas that refers to the Definition sheet, e.g. "=Definition!C5".) As in Definition, you can leave the first cell blank on a row to insert a divider. When INSFILGEN has found five consecutive blank rows, it stops reading rows.

You can include comment columns with names starting with # anywhere. INSFILGEN simply discards these columns.

In the cells in columns headed by column macros, three are three possibilities:

The cell is blank or 0. In this case the macro does not apply to the row and the cell does not yield any expression.
The cell is non-blank, non-zero and neither starts with an & followed by a letter. In this case the column macro applies unconditionally to this row and the resulting expression from this cell is the macro itself.
The cell starts with an & followed by a letter. In this case, the cell is assumed to contain a macro expression that will be and:ed with the column macro. We call this a cell macro expression (or just cell macro for short). If you go back to the example above, you see the effect of the &EVAL macro.

The call to insert a row will be within an $IFDEF of which the argument is an expression with all the cell expressions or:ed together. There will be a complementary call to delete the row with an $IFDEF that performs a not on the same expression.

All values that appears in Keys columns on the Macros sheet must also appear in the Definition sheet. Unless the property Optionalmacros is set, the reverse is also true. That is, all Keys values in the Definition sheet, must also appear in the Macros sheet with the exception of those with a 1 in Delflag.

When would you use Optionalmacros and when would you not? For a table like functions it is probably a bad idea to enable Optionalmacros. Say that someone adds a new function designed for Dell to the Definition sheet, but forgets to add it to Macros. The result is that all customers get the function, although only Dell pays for it.

On the other hand for a table where 95% of the rows are installed at all customers, it might more palatable to only add the odd customer-specific rows to the Macros sheet.

The Values Sheet

The START row of the Values sheet is similar to the START row for the Macros sheet. First you list the parameters of the Keys property. Next comes a a column with the name %param and nothing else. After %param, the macro names follow. You can leave out @ in the Keys, and the & in the macros, but you cannot leave out the % in %param. You can insert a comment column with a name starting with # anywhere. The comments are not included in the INSERT-file.

In the data rows, the values for the Keys in Definition reappear. In %param you specify the name of a non-key parameter defined in the Definition sheet. If you want the generated call to the Value_sp include more than one parameter, you enter the key values for that row twice, with different values in the %param column.

In the macro columns, you specify which value the parameter in the %param column is to have when that macro is set. As in the Definition sheet, a blank cell means that the parameter is omitted. In the INSERT-file there is one call to the Value_sp for the key-value for each macro that has at least one value defined for it.

All values that appears in Keys columns on the Values sheet must also appear in the Definition sheet, but in difference to the Macros sheet, there is no reciprocal requirement. For a table like functionsettings the default value for all settings would be "unavailable" or "standard" and then you specify the luxury stuff on the Values sheet.

Here is how the Values sheet for functionsettings look like:

(See these links for the Config and Definition sheet for functionsettings.)

As in Macros, you can use cell-macro expressions, as you can see in the &cust_HP cell for function 1. The format for this is:

&macro # value [# &macro2 # value2 ...] [ # else-value ]

That is macro expressions interleaved with values, closed with a final value to apply when none of the macro-expressions are true. # separates the different parts. If you leave a value empty or leave it out completly, INSFILGEN will generate DEFAULT as the parameter value. Thus if the parameter does not have a default value, the INSERT-file will fail when run with this combination of macros.

Note: if you would like to include # in one of the values, you can't. There is currently no escape mechanism. On the other hand, if you wish the value of a cell to be &abc # 4, this is doable; just include the value in quotes.

How to Retrieve the Example

If you wish to study the example closer, you can look in this directory where you find an AbaPerls SQL directory structure with the two tables functions and functionsettings, the stored procedures fun_install_sp, fun_install_sp, funset_install_sp and funset_set_value_sp as well as the complete Excel books for the two tables and the generated INSERT files. You can also get the files from this zip archive.

If you wish to build the database, extract the zip archive, and in the directory which you extracted, run

dbbuild -subsys INSFILGEN-DEMO -d testdb -Macro="&all_customers=1"

Add -S and/or -P to specify server and password if necessary. You can replace &all_customers with for instance &cust_HP, and compare the contents of the two tables in the two cases.