mod_dbd: Saying technically

Hello,
Two previous articles—“Introduction to database functionality of Helicon Ape” and “How to connect mod_dbd with various databases” were aimed at every customer and they are enough if you don’t want to deepen into this matter. But in case you’re such a guy who wants to learn the internals of mod_dbd—please feel comfortable. In this article you’ll find what you need.
One little note: the text below requires basic knowledge of .NET Framework.

What’s inside

As you may guess mod_dbd completely relies on ADO.NET. This is a most common way for .NET applications. For more information on ADO.NET please follow the references below:

• Overview of ADO.NET
• ADO.NET in Wikipedia

In other words mod_dbd loads any database provider through ADO.NET. Here is a scheme which illustrates the idea:

Database providers for Microsoft SQL Server and Oracle as well as ODBC and OLEDB interfaces were included in .NET Framework, so we support them natively and you don’t need to install additional software. However any other ADO.NET compatible provider may be used because mod_dbd is able to load external .NET assemblies. We’ll talk about that a little later. Now let’s take a look on connection pooling.

Connection pooling

mod_dbd in Apache supports connection pooling for threaded platforms. It has its own implementation with the help of APR library. We have considered to implement something similar but in a different way. ADO.NET kindly provides developers with connection pooling features, so mod_dbd uses nothing more. As opposed to Apache guys we have the fixed platform—Windows, so that’s pretty natural to use ADO.NET internal features.
Below we presume that you’re familiar with the connection pooling idea. If you’re not, please read the following great article by Dino Esposito: “The Connections of ADO.NET Connection Pooling”.

In ADO.NET connection pooling works very transparently. It’s configured through special connection string arguments. The following idea lays in the basis—connections with equal connection strings are put into one connection pool unless this breaks the connection pool rules.

So generally mod_dbd directs its directives values to particular connection string arguments. They are as follows:

• DBDPersist → Pooling
• DBDExptime → Connection Lifetime
• DBDMax → Max Pool Size
• DBDMin → Min Pool Size

Loading external providers

As we've mentioned before, mod_dbd is capable of loading ADO.NET providers from external .NET assemblies. Let’s consider one example which is probably better than dumb bare strings.
We’re going to use PostgreSQL connector. After downloading and extracting you will see Npgsql.dll assembly within bin folder. This assembly should be copied into GAC (C:\windows\assembly) or put near Helicon.Ape.dll. We’ve copied ours to GAC and opened the assembly’s properties:

The properties highlighted in red should be specified in DBDriver directive as follows:

DBDriver adonet "Npgsql.NpgsqlConnection, Npgsql, 
  Version=2.0.8.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"

We also have to specify a connection string, so here is the final configuration:

DBDriver adonet "Npgsql.NpgsqlConnection, Npgsql, 
  Version=2.0.8.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" 
DBDParams "Data Source=server;Initial Catalog=database;
  User ID=user;Password=password"

Quite easy, isn’t it? You are probably wondering whether this hinders the performance. Well, assembly loading isn’t a quick process, however mod_dbd does it only when configuration file changes. Usually it’s rare operation and you shouldn't worry about performance slump.

Summary

We’ve explained the internal organization of mod_dbd, described connection pooling implementation and provided you with an example of loading external database provider. This knowledge should help you in setting up your web-applications along with Helicon Ape in a very
efficient way.

Yours sincerely,
Helicon Tech team.

How to connect mod_dbd with various databases

Hello,
This article is briefly describing the basic steps you should follow to configure mod_dbd and make it “understand” different databases.
The article mostly goes by those who don't want to know mod_dbd deeply but only have a desire to get Ape database modules working. More specific information you may find in “Introduction to database functionality of Helicon Ape” and “mod_dbd: Saying technically”.

Where and how to write mod_dbd configuration

The module directives are almost compatible with Apache mod_dbd but we've extended their scope and, as opposed to Apache, you may write mod_dbd directives in any configuration context: within <Directory>, <Location> and <VirtualHost> statements, in .htaccess and httpd.conf files.

Only two directives are required—DBDriver and DBDParams. Both are essential for creating a database connection and you will just have an error if mod_dbd can't retrieve one of these directives values. By the way, error.log is very useful in such cases. It may give you a hint why something isn't working.

The other mod_dbd directives may be omitted and then the default values are applied. We've taken them from original mod_dbd:

DBDPersist on
DBDExptime 300
DBDMax 10
DBDMin 1

Technical details of these directives are provided in “mod_dbd: Saying technically” article.

Note regarding DBDParams. mod_dbd doesn't parse directive's parameter but simply passes it to a database driver. It means that the directive doesn't have any special syntax for connection strings, instead you should use database-specific connection string arguments.

Also please don't use the following connection pooling arguments: Pooling, Connection Lifetime, Max Pool Size and Min Pool Size. They are automatically supplied by mod_dbd through its directives.

Here is a web-site which might be very helpful in connection string design: http://www.connectionstrings.com.

Microsoft SQL Server

mod_dbd natively supports SQL Server version 7.0 or higher. It's not required to install any additional database providers, so the piece of code as follows would be enough:

DBDriver mssql
DBDParams "Data Source=server;Initial Catalog=database;
  User ID=database;Password=password"

You may use the same connection string as in your web-site engine. Please refer to Microsoft SQL Server documentation for more details.

Oracle

Oracle database is natively supported. No additional software required. The following example illustrates a general way to connect to Oracle database:

DBDriver oracle
DBDParams "Data Source=server;Initial Catalog=database;
  User ID=database;Password=password"

Note! mod_dbd basically uses Microsoft drivers for Oracle, however it's possible to use Oracle drivers. Please find more information in the “Others” section below.

MySQL

There is no native support for MySQL in Helicon Ape. However it's very easy to get mod_dbd working with this database. The best way is to install MySQL Connector for .NET. After installation please open C:\Windows\assembly folder and find MySql.Data assembly. You should open its properties dialog to get a few important values:

These values should be passed into DBDriver directive as a second argument with the following syntax:

DBDriver mysql "MySql.Data, Version=6.2.2.0, 
  Culture=neutral, PublicKeyToken=c5687fc88969c44d"
DBDParams "Data Source=server;Initial Catalog=database;
  User ID=database;Password=password"

This is how to make mod_dbd know about MySql.Data assembly. It provides interface to MySQL database through ADO.NET technology.
Although on a shared hosting you may copy MySql.Data.dll into the same location as Helicon.Ape.dll, then your configuration would be just like this:

DBDriver mysql
DBDParams "Data Source=server;Initial Catalog=database;
  User ID=database;Password=password"

In spite of the fact that the method above is much better, you have another option. It also requires additional software—MySQL ODBC connector. ODBC works much slower and we don't recommend this approach, however if you prefer it—here is an example:

DBDriver odbc
DBDParams "Driver={MySQL ODBC 5.1 Driver};Server=server;Database=database;
  User=root;Password=password;"

Please pay attention to the mandatory argument Driver. Your version may be different.

Others

We have considered only three database systems, however this isn't a limit for mod_dbd. It's capable of working with almost every database which supports ADO.NET, ODBC or OLEDB interface. And here is some explanation:

ADO.NET

This way requires a bit of software development knowledge! If you have ADO.NET connector for one database system, knowing a full name of a connection class it provides you may load the connector as follows:

DBDriver adonet "System.Data.SqlClient.SqlConnection, System.Data,
  Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

The code above loads Microsoft SQL Server connector.

ODBC

For this approach you need to install ODBC provider and know its name. The following example demonstrates connection to MySQL through ODBC:

DBDriver odbc
DBDParams "Driver={MySQL ODBC 5.1 Driver};Server=server;Database=database;
  User=root;Password=password;"

OLEDB

Like in case with ODBC you should have OLEDB provider installed. The following example demonstrates connection to Microsoft SQL Server through OLEDB:

DBDriver oledb
DBDParams "Provider=SQLOLEDB;Data Source=server;
  User ID=user;Initial Catalog=password"

Summary

We've described different approaches to connection mod_dbd with various database systems. Please notice that internal behavior and connection string format are very dependant on a particular database driver. However such feature as connection pooling works in any case because it's a natural capability of mod_dbd.

Yours sincerely,
Helicon Tech team.

Introduction to database functionality of Helicon Ape

Hello,
New release of Helicon Ape has come out right after winter holidays. We wanted to fix a few found bugs and satisfy users' feature requests. However we also had a desire to bring something new and useful. So here you go—Helicon Ape v3.0.0.30 obtained database functionality. This means Ape has a potential to work with almost any RDBMS. Let’s
provide you with a little explanation. Please note, this article isn’t aimed to give you deep technical information or instruction steps. If you want these please consider “mod_dbd: Saying technically” and “How to connect mod_dbd with various databases”.

mod_dbd

As well as Apache, Ape uses mod_dbd module as an interface to databases. The module organizes interaction between Ape and database drivers through which Ape modules may operate.
Though such database-driven modules are going to be your tools for doing some magic stuff on your server, you should configure mod_dbd to specify database driver, connection options and setup connection pooling. “How to connect mod_dbd with various databases” article will help you in this. Here are the basic features of mod_dbd:

• Compatibility with original Apache mod_dbd module;
• Configurable connection pooling which allows organizing connections in a very efficient way;
• Native support for Microsoft SQL Server and Oracle;
• ODBC and OLEDB interfaces support;
• Possibility to load any ADO.NET compatible connector.

The following sections describe the modules we have already bound with mod_dbd.

mod_authn_dbd

This module is basically the same as mod_authn_file. The only difference as you may guess is that mod_authn_dbd retrieves credentials from a database. To make it working you should specify an SQL query because we don’t know your database structure and it’s not supposed to be limited to mod_authn_dbd needs.
The configuration below demonstrates mod_authn_dbd in action:

DBDriver mssql
DBDParams "Data Source=db_server;Initial Catalog=users_db;
  Persist Security Info=True;User ID=sa;Password=your_password"

# core authentication and mod_auth_basic configuration
# for mod_authn_dbd
AuthType Basic
AuthName "My Server"
AuthBasicProvider dbd

# core authorization configuration
Require valid-user

# mod_authn_dbd SQL query to authenticate a user
AuthDBDUserPWQuery "SELECT password FROM users_table 
  WHERE user = @USERNAME"

As you may notice the code starts with two mod_dbd directives—DBDriver and DBDParams. Both are required for mod_dbd to work correctly, whereas others may be omitted.
These mod_dbd directives are needed to specify desired database driver and connection string. In the example above we’re connecting to Microsoft SQL Server. AuthBasicProvider dbd points to database-based authentication and finally SQL query is set through a special mod_authn_dbd directive AuthDBDUserPWQuery.

Map-files in mod_rewrite

This is probably one of the most exciting features. Before you would tell your web-site engine to regenerate a map-file according to database content. Now you have a possibility to appeal to the database directly and this is very easy to do:

DBDriver mssql
DBDParams "Data Source=server;Initial Catalog=database;
  User ID=user;Password=password"
DBDPrepareSQL "select OriginalURL from seo_mapping 
  where `SEO_URL`=@KEY" seo_map_select

RewriteEngine On
RewriteMap map_dbd dbd:seo_map_select
RewriteCond ${map_dbd:$1|NOT_FOUND} (.*)
RewriteCond %1 !NOT_FOUND 
RewriteRule (.+) %1 [L]

As opposed to mod_authn_dbd, mod_rewrite doesn’t introduce any special directive for SQL query setup. Instead it uses DBDPrepareSQL which belongs to mod_dbd. DBDPrepareSQL links the query with the unique name and you should use this name in RewriteMap declaration. The rest of the rules should be familiar to you if you worked with map-files before.

Summary

We have considered a brand new feature of Ape—database connectivity. Along with core database module—mod_dbd—we've also touched upon mod_authn_dbd and mod_rewrite. Frankly saying this is not a limit and hopefully Ape will have loads of others database-oriented features. We’ll be accumulating customers' requests and will try and implement the desired functionality. Thanks for your cooperation.

Yours sincerely,
Helicon Tech team.

Titbits: dynamic mapfiles names

Not so long ago one of our clients urged us to test an interesting situation we've never tried before. The result was positive and saved him a lot of lines in the config file. Now we want to share this experience with you.

So, the conditions were:

• about 300 mapfiles with the first part of name being the name of the city, e.g. london-map.txt, paris-map.txt, moscow-map.txt etc.;
• the requests are made to the subdomains having the names of the cities, e.g. london.domain.com, paris.domain.com, moscow.domain.com etc.;
• requests to SEO-friendly URLs should be rewritten to the .asp page with the "id" taken from the corresponding mapfile and the "city" parameter being the city name.

Solution
To accomplish the above task with the least effort, we need the following.

Mapfiles:

london-map.txt
london_seo_value1 11
london_seo_value2 22
london_seo_value3 33
etc.

paris-map.txt
paris_seo_value1 44
paris_seo_value2 55
paris_seo_value3 66
etc.

moscow-map.txt
moscow_seo_value1 77
moscow_seo_value2 88
moscow_seo_value3 99
etc.

Config:

RewriteBase /

RewriteMap london-map txt:london-map.txt [NC]
RewriteMap paris-map paris-map.txt [NC]
RewriteMap moscow-map moscow-map.txt [NC]
#etc.

RewriteCond %{HTTP_HOST} ^(?!www\.)([^.]+)\.domain\.com$ [NC]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond ${%1-map:$1|NOT_FOUND} !NOT_FOUND
RewriteRule ^([^/]+)/?$ city.asp?city=%1&id=${%1-map:$1} [NC,L]

The idea is that the first part of mapfile name is dynamic and depends on the name of the subdomain being requested.

Now upon request to http://paris.domain.com/paris_seo_value1 you'll be shown the content of http://www.domain.com/city.asp?city=paris&id=44. And upon request to http://moscow.domain.com/moscow_seo_value3 you'll be shown the content of http://www.domain.com/city.asp?city=moscow&id=99.

Resume
As you can see we have just one rule (instead of 300) which cares about all possible requests and deals with all existing mapfiles.

Helicon Ape 3.0.0.30. Happy new build!

Hello, everyone!
Happy holidays!
We've got some good news for you—the new build of Helicon Ape 3 is released and available for download.
And it's not just a couple of bug-fixes; we've introduced:

• three new modules:
  
  • mod_dbd which allows creation and management of SQL database connections;
  • mod_authn_dbd which provides user authentication by searching users in SQL tables; and
  • mod_dir which cares about "trailing slash" redirects and directory index files handling;
• really important and highly anticipated by the clients ErrorDocument directive that gives a possibility to set custom error pages upon error;
• two parameters for Options directive:
  
  • ShowErrorInResponse which tells Ape to show detailed error explanation or omit it;
  • StopOnError which regulates whether to ignore unknown directives or give out 500 error;
• some important fixes, in mod_linkfreeze in particular.