mod_sqltemplate :: Code :: A Mind Less Ordinary

`mod_sqltemplate` Apache module

This module provides a way to template your configuration files, in a similar way to mod_macro by Fabien Coelho. This project was inspired by mod_macro, and uses some code from it.

The mod_sqltemplate module for Apache 2.2 allows you to repeat any fragment of configuration directives based on values from a database. Note that this requires Apache to be built with DBD support and the appropriate database drivers. For the moment, it also requires apr-util version 1.3 or better compiled with DSO support.

Please send bug reports, comments, suggestions, etc to mod_sqltemplate@[this domain].

Known issues

Bugs are currently tracked via GitHub.

Please note: This module currently will not work with the binary apr-util packages provided by many distributions, as they use version 1.2 and also may not include DSO support.

In addition, mod_php causes problems due to some reported conflicts, and bugs have been opened. These are both things that I aim to fix with the next version, but it will take a fair amount of work, so please bear with me.

Note that PHP via mod_suphp or CGI/FastCGI/fcgi should work fine.

Example

Suppose you have a large number of sites to host, but you don't want to generate or maintain individual configuration files for them, either manually or via a script. You've looked at various mass virtualhosting modules (e.g. mod_vhost_dbd) but they don't give you the power and flexibility you want. You might store your virtual hosts in a hosts table like this:

ID	hostname	domain	docroot	server_admin
1	test	example.com	subdomains/test	server_admin@example.com
2	security	example.com	subdomains/security
3	foo	example.net	htdocs
4	bar	example.net	htdocs/bar
6	www	example.org	htdocs	webmaster@example.org
…	…	…	…	…

With the document roots relative to /home/domain. In addition to this, you have a list of server aliases in a server_aliases table:

host_id	alias
1	*.example.com
1	example.com
1	test.example.org
3	foo.example.*
4	bar.example.*
…	…

You might write scripts to generate the virtual host files. Or you could use mod_sqltemplate:

SQLTemplateDBDriver "mysql"
SQLTemplateDBParams "host=localhost,user=db-username-here,pass=password-goes-here,dbname=database-name"

<SQLRepeat "SELECT * FROM hosts WHERE is_ssl=0">
  <VirtualHost *:80>
    ServerName $hostname.$domain
    <SQLSimpleIf "${server_admin}">
      ServerAdmin "$server_admin"
    </SQLSimpleIf>
    <SQLSimpleIf "!${server_admin}">
      ServerAdmin default_admin@example.net
    </SQLSimpleIf>
    DocumentRoot "/home/${domain}/$docroot"
    <Directory "/home/$domain/${docroot}">
      Order deny,allow
      Allow from all
      AllowOverride All
      Options +FollowSymlinks
    </Directory>

    <SQLCatSet " " "SELECT * FROM server_aliases WHERE host_id=${id}">
      ServerAlias \$alias
    </SQLCatSet>

  </VirtualHost>
</SQLRepeat>

This keeps all of your configuration in one central location, where you would expect your Apache configuration files to be. Note that the sample configuration file is annotated with comments describing the details of how to use this module.

Download

The source code is released under a BSD-like licence, and is available from GitHub:

git clone git://github.com/dingram/mod_sqltemplate.git mod_sqltemplate

An ebuild is also available for Gentoo systems – see below for information on using it.

mod_sqltemplate is currently in use on this webserver.

Installation

Simply run:

apxs -a -c -i mod_sqltemplate.c

from the directory containing mod_sqltemplate.c as root to compile and install the module, and add the appropriate LoadModule line to httpd.conf.

Gentoo ebuild

In order to use the ebuild, follow these steps:

Download the ebuild, Manifest and the additional config file.
If you have a Portage overlay directory, then skip to the next step, otherwise:
1. Create the overlay directory /usr/portage/local/overlay
2. Add this line to the end of your /etc/make.conf:
```
PORTDIR_OVERLAY="${PORTDIR_OVERLAY} /usr/portage/local/overlay
```
Place the files into your Portage overlay directory, with the ebuild and manifest in
```
/path/to/overlay/www-apache/mod_sqltemplate/
```
and the config file in
```
/path/to/overlay/www-apache/mod_sqltemplate/files/
```
Add www-apache/mod_sqltemplate ~x86 to your /etc/portage/package.keywords file.
Install the module using emerge -av mod_sqltemplate.
Read through and edit the configuration file /etc/apache2/modules.d/99_mod_sqltemplate.conf as required. It contains some examples that are very unlikely to work!
Activate the module by including -DSQLTEMPLATE in the APACHE_OPTS variable in /etc/conf.d/apache2
Restart Apache: /etc/init.d/apache2 restart

Troubleshooting

If you encounter issues with the module, such as segmentation faults, there are a few things to check:

Ensure that Apache has been compiled with DBD support (i.e. mod_dbd exists, although it is not required for this module)
Make sure that the APR driver for the database you want to access is available
Check that all of the directives are correct, especially CustomLog/ErrorLog paths
If using Gentoo, enable the debug use flag and recompile
If not using Gentoo, recompile the module with apxs -c -i -D _DEBUG_SQLTPL=1. This will output the configuration after replacements have been made, so you can check that everything has happened as expected. The debug level can be increased to get progressively more information about the module's internal workings.

If you still encounter problems, please email me on the address above and I'll do what I can to help. Information about your Apache version and compilation options, as well as a list of the other modules you are using would be useful.

Please note that bugs are currently tracked via GitHub.

A Mind Less Ordinary