CHARP SQL Configuration Definitions - pupitetris/charp GitHub Wiki

Some parameters for the database will differ from one installation to the other. You may have a dblink connection and want your development box to connect to a testing database during development, and then have the production server connect to another production database with different connection parameters.

To allow for the centralization of such parameters on a single file and to reduce the amount of code that differs from development to QA to production installations, CHARP supports a simple system of macro definitions, using m4.

Table of Contents Basic Configuration Preexisting configuration Advanced configuration Using Configuration Definitions inside the SQL files. Example

Basic Configuration

The M4 configuration definitions are stored in the conf/config.m4 file. You can edit this file to add your own definitions, with the following format:

 DEFINE(varname, value)

The syntax is pretty straightforward, but these are the actual rules, which are inherited from M4's parsing rules:

There cannot be any spacing between DEFINE and the parenthesis.
varname may contain any character, except comma and «.
Spaces between the parenthesis and varname will be ignored.
Spaces between varname and the comma will be part of varname.
Spaces between the comma and value will be ignored.
value may contain spacing (including new-lines) and any other character, except for comma, « and ).
Spaces between value and the closing parenthesis will be part of value.
varname and value can be surrounded by « and » to escape #, ignored spaces, comma and parenthesis.
Contents is ignored from hash (#) to the end-of-line.
All spacing outside definitions will be ignored.

Preexisting configuration

You will find that conf/config.m4 will contain the following definitions from source:

 DEFINE(user,    CONF_USER)
 DEFINE(dbname,  CONF_DATABASE)
 DEFINE(locale,  CONF_LOCALE)
 DEFINE(sqldir,  CONF_SQLDIR)

These CONF_* values are defined into M4 on invocation by psql_filter, and contain the corresponding values you set in your ~/.bash_profile for the project (check Project Setup - Shell configuration for reference). These definitions should normally not be altered or removed from the configuration file.

Advanced configuration

If you are familiar with m4, you may check conf/config_init.m4 to see how we prepare M4 for this configuration file. conf/config_end.m4 prepares M4 to parse the SQL files with minimum interference.

You can use M4_DEFN(varname) inside values to expand the value of a previously defined varname.

You can define M4 macros here as well, and m4_include other files, and so on.

Using Configuration Definitions inside the SQL files.

Through the use of clever definitions and configurations, M4 will interfere very little with the SQL code. If you stick to basic configuration, the only reserved words that M4 will catch are M4_DEFN and m4_defn (called by M4_DEFN).

Example

Suppose you are going to make a series of SELECTs in different parts of your code, and you don't want to repeat the same connection string over and over, and you want to have this parameter stored in a central location for easy configuration. You can then use an SQL Configuration Definition, and so edit conf/config.m4 with something like this:

 DEFINE(otherDB, 'dbname=myOtherDB')

And then somewhere in your SQL code (probably sql/05-functions.sql):

 SELECT * FROM dblink(M4_DEFN(otherDB), 
                     $$ SELECT id, value 
                               FROM my_table $$) 
                    AS t1(id int, value varchar);

So, anywhere you put M4_DEFN(otherDB), M4 will replace that with 'dbname=myOtherDB', quotes included.