[Mod_log_sql] LogSQLMassVirtualHosting and mod_vhost_alias

[Dice R. Random]

On 1/22/06, Jorey Bump <list at joreybump.com> wrote:
> This use case is common, but the fix isn't flexible enough for all kinds
> of mass virtual hosting. For example, one might want to aggregate the
> results for www.example.net & www.example.com, or www.example.com &
> www.example.co.uk. Counting back from the TLD seems insufficient.
>
> There should be support for arbitrarily combining results based on any
> combination of parts from the hostname in the request (select all
> requests to .com domains, or to any host with "example" as part of the
> FQDN).
>
> How are people aggregating results with the current system (assuming
> separate tables for hosts)? Should there be an index table that hashes
> the parts of hostnames on the system, for easy discovery of table names
> to use in queries? What internal support does Apache provide that can be
> used to achieve this, such as  Directory Name Interpolation?
>
>   http://httpd.apache.org/docs/2.0/mod/mod_vhost_alias.html#interpol
>
> The solution to this problem should at least be as flexible as is
> possible with mass virtual hosting (and then some), and should attempt
> to match or leverage Apache internals, as well.

I think you'd run into the same sorts of problems using an
interpolation routine as we've been discussing here, or at least I
haven't been able to think of a way to get a mod_vhost_alias
interpolation that doesn't exhibit simmilar problems.  For instance,
we're currently using the following:

VirtualDocumentRoot /var/vhosts/%-1/%-2.1/%-2.2/%-2.3/%-2/htdocs/%-3+/

So www.example.somesite.com would have its DocumentRoot in
/var/vhosts/com/s/o/m/somesite/htdocs/www.example/ .  As I discovered
during my recent testing of .co.uk sites, www.somesite.co.uk ends up
having its DocumentRoot in
/var/vhosts/uk/c/o/_/co/htdocs/www.somesite/ .  My point here is that
there's no real way to tell the interpolation routine how to skip back
however far it needs to for the xLD.  You could, of course, go with
something along the lines of:

VirtualDocumentRoot /var/vhosts/%0

But then you run into the problem of having potentially thousands of
directories, one for each subdomain, all making a huge mess under
/var/vhosts.

I think the best solution to aggregating the data in different tables
would simply be to have a LogSQLAggregateTables option, something
like:

LogSQLAggregateTables example.com example.net example.co.uk example

Would aggregate all the data that would go into access_example_com,
access_example_net, example_co_uk into the table access_example.  It
would mean that you'd have to manually add entries for hosts that
wanted aggregate data, but I think it's better to handle it explicitly
since there could very well be different people who owned example.com
and example.net who due to some obscure chance just happen to both be
hosting with you.