SPM

What is SPM

SPM is the macroprocessor to develop stored procedures, functions, triggers and views. SPM is able

  • to group the source files into the project and to manage the project;
  • to translate SQL-code to the database server with returning of error messaging;
  • to support meta-programming, that is use of the simple macro language to use the same parts of SQL-code more than once (functional decomposition in such declarative languages as SQL can extremely reduce the productivity and many DBMS don't support the user defined functions).

SPM is a free and open source software which is distributed under the GNU GPL license.

Short history

Since 1996 I use Microsoft SQL Server in general and others DBMS in background, for example, InterBase/Firebird, which is open and free for today. In my previous and current projects I need to write a lot of a server-side program code such as stored procedures, triggers e.t.c. To support big volumes of such code (thousand and tens thousand lines) it is necessary to have the tool which could made all of described below.

To solve this problems I created the tool which has received name SPM (Stored Procedures Macroprocessor). The first version supported only MS SQL. However, I had the necessity to work with InterBase and Sybase soon and the support for theses DBMS has been added. To simplify the SPM's development and support, I
decided to make SPM independent of DBMS type using ODBC. As I was required the more and more complex macros, I have decided to take for a basis the macroprocessor GNU m4, having left for SPM only the functions of the project file manager and the translator of a code to the database server. Next step was to use ISQL proper to DBMS and XML as project configuration file.

How it works

SPM takes source files that are

  • SQL-macro source files (can have *.SQM extension to distingue them from SQL file) which contains the SQL source code with zero or many macros
  • macro definition files (can have the *.SQH extension) that contains the commonly used macro-definitions. Particular definitions you can place directly in SQM file.

SPM starts M4 that produces pure *.SQL files ready to translate to SGBD. SPM can translate these SQL files and/or generate common script SQL containing all files of the project.

SPM creates the file Error.log if some errors occurred.


Pic.1. How SPM works

SPM returns the status value (errorlevel). If some errors occurred, the status is "1" and "0" if succeed. This feature is used in command-line batches, for example:

spm2 sample
if errorlevel 1 goto errors_occured
echo all done
goto end
:errors_occured
echo errors occurred
:end      

Macro samples

Many information about M4 options and useful examples of macros are in M4 home site. By default SPM use minimum of necessary options to run M4 but you can add any other options in section or passing them in command line.

Simple macro:

define({RC_ERROR},   {-1})
define({RC_SUCCESS}, {0})

Macro with parameters calling other macro:

define({ReturnIfError}, {if ($1 != RC_SUCCESS) return RC_ERROR})

Macro with parameters calling other macro with parameters.

define({RaiseSysError}, {raiserror($1, SYS_ERROR_SEVERITY, 1)})
define({CheckReturnError}, {if ($1 != RC_SUCCESS) BEGIN RaiseSysError({'Error status returned. ifelse($2,{},{},$2)'}) END})

Project file

The project file contains following information:

  • how to start M4
  • how to start ISQL
  • where the definitions of macro are located
  • where the source files are located and what to do with them

This file has an extension *.spm2 (2 seems SPM version 2 because SPM 1.x had other project file format).

Here is an example:

<?xml version="1.0" encoding="utf-8"?>
<spm2>
  <m4Info>
    <application>%M4_BIN%\m4.exe</application>
  </m4Info>
 
  <!-- Interactive SQL (ISQL) tool info -->
  <isqlInfo>
    <!-- ISQL should return exit code to check errors.
         You can use system or custom environement variables like %MyVar%, %PATH% etc.
         Variable #FILE_NAME# is used as name for currently processed source file (see section sourceFiles)
    -->
    <commandLine>osql -b -n -S%SERVER_NAME% -d%DATABASE_NAME% -E -i#FILE_NAME#</commandLine>
  </isqlInfo>
 
  <!-- Paths to search included files (usually common macro definitions) -->
  <includes>
    <include>..\..\..\Include</include>
  </includes>
 
  <sourceFiles>
  <!-- Default file settings (change if need): 
       compile = "true"
       script = "true"
  -->
    <file compile="false" script="false">CreateCatalog.sqm</file>
    <file>Utils.sqm</file>
    <file>SalesForecast.sqm</file>
  </sourceFiles>
</spm2>

How to start with SPM

Download SPM (all-in-one package including samples) and unpack it into the folder like SPM2 or another. You can include SPM2\bin directory in the PATH variable but it is not necessary.

I hope that the client software like ISQL (SQLCMD for MS SQL) to connect the database server is already installed. SPM was tested with the following DBMS:

  • MS SQL 6.5, 7, 2000, 2005, 2008, 2012, 2014 and so on
  • Sybase ASE 12.0 и 12.5
  • InterBase 6 / FireBird 2.x

But there is no restriction to use SPM with any other, ISQL tool is required only.

Run command line window, go to the directory where your SQL-project file is located and start the command:

spm[.exe] <project file> [options]

SPM options are listed in the following table.

Option Description
/script SMP will do only macro processing into the generated files, none will be changed in database
/build SPM will regenerate all files and translate them to the database server
/debug SMP will define macro SPM_DEBUG that you can use for conditional translation, i.e.:
ifdef({SPM_DEBUG}, {SELECT 'Debug mode' AS mode})
/m4:<M4 command line parameter> Pass to M4 command line parameter. You can define more than one /m4

Version and changes information

The SPM package includes the following files:

changes.txt contains changes list
license.txt license information

Download SPM

Download SPM package (included M4 and template files)