$Id: README.txt,v 1.8 2001/10/21 21:04:36 carlos Exp $

------------------------------------------------------------
                 SQL Include Apache module
                (C) 2001 by Marcin Orlowski
                        Webnet ISP
------------------------------------------------------------
  carlos@amiga.com.pl          http://webnet.pl/~carlos/
------------------------------------------------------------
                         W.F.M.H.


What is it?
------------

mod_sqlinclude is an Apache module which implements Include-alike
command, which, as its origin, includes the external data into
Apache configuration file, but instead of taking the real files
SQLInclude fetches and includes data from MySQL database.


Requirements
--------------

 - MySQL database server (http://mysql.com/)
 - Apache web server (http://apache.org/)

The modules was written on, and tested against

 - Server version: Apache/1.3.19 (Unix) Debian/GNU (and later)
 - mysql  Ver 11.15 Distrib 3.23.37 (and later),

under GNU Debian/Linux, but it should run with other (older and newer)
versions too, as it does not use any version specific features.


How to compile and install the module?
---------------------------------------

To compile the module you will have to have developer files for mysql
and apache installed (under debian look for apache-dev and mysql-dev
packages)

 - unpack the archive
 - enter into the directory that holds
   the upacked source code (mod_sqlinclude.c)
 - type make
 - type make install  (as root)
 - restart apache ("apachectl restart" or "make restart")

from now you shall have mod_sqlinclude ready up and running.


Troubleshoting
----------------

Q: I can't compile
A: Make sure you have apache and mysql development files. These are mosly
   packed in NAME-dev.xxx (xxx is deb, rpm or...) packages, so just install 
	them

Q: Apache can't see the modules (it rejects the commands)
A: Make sure it's properly installed. In particular check the httpd.conf
   and see if LoadModule entry is present, and if it points to the module.
   On my config this line looks like:

   LoadModule sqlinclude_module /usr/lib/apache/1.3/mod_sqlinclude.so

Q: Apache reports errors 'unknown command' on all the SQL_xxx commands?
A: Make sure you have above LoadModule line present. It's module task
   to process own commands, not Apache's.

Q: When the module connects to the database?
A: It connects on SQL_Include command, process all the data and disconnects.
   SQL_Include shall be the last of SQL_xxx commands in your config.

Q: The module works, but it can't connect to the database
A: Make sure the user/pass you want the module to authenticate is valid
   MySQL user/pass (read: it exists in mysql.user table). Example SQL
   user setup might look as follow (assuming you use table include
   in DBName database, and user is apache with 'pass' password):
	
   GRANT SELECT ON DBName.include TO apache@localhost IDENTIFIED BY 'pass';
	
   If you insert users with INSERT, don't forget to FLUSH PRIVILEGES.
   For more details read the MySQL manual.
	
   Other cause of unsucessful connection is restricted host access for 
   that user. Make sure your the machine apache runs on is allowed to 
   connect to MySQL AND authenticate as the given user (check the Host 
   column in your mysql.user table). If unsure either put '%' (which is
   the wildcard matching any host - in above GRANT, user apache can
   connect only from localhost) or put the apache's exact host name
   there (i.e. myserver.com).

Q: There's no command to specify which database to use, why?
A: It ain't necessary. All, including database name can be specified
   in SQL query, so I decided to avoid commands that are useless.
   See the examples for more details.
   [ UPDATE: there's already SQL_SQLDB command since v1.2 for 
     specifying database name you want the module to connect to, 
     but I decided to remain this Question for those who don't
     know that they can query without that ;) ]

Q: I installed newer Apache and now module refuses to work
A: Didn't you forgot to recompile the module by chance? You need
   to do this after every apache update.

Q: Can I span configuration i.e. VHosts directives among many records?
A: If, for instance, you got 120 virtual hosts you want and you
   want to store configuration of each of them in the database
   it's then strongly recommened to split all the data into 120
   separate records for easy meainteance. But you cannot have 
   configuration of one VHost spread among records, as every record 
   is treated separated and Apache report config errors if it won't find
   i.e. closing </VirtualHost> (no matter it exists somewhere
   else. It does not exists in that 'config'. The simplest rule
   of the thumb to avoid problems: keep all logical blocks in 
   single record.

Q: Ok, I can't split, but can I merge?
A: Sure. As long as the above rule is fulfilled. Another words, you
   can have 10 VHosts definitions in one record, but you still can't
   have 10,5.

Q: The below DB example records use "\n". What is that for?
A: Always remember that mod_sqlinclude acts same way the ordinary
   <Include> does. And Apache process the data it feeds in exactly
   the same way. That means that your SQL stored config has to match 
   normal Apache syntax rules. As you noticed, you mostly put
   one command per line. New line delimiter (LF, ASCII code 10) is
   usually written as '\n'.


How to use
-----------

Once you have it complied and installed, modify your httpd.conf file
(usually resides in /etc/apache). Here are the config commands that
mod_sqlinclude implements and handles. Since Apache process config
line by line, you will have to specify all the connection parameter
before you do the SQLInclude:

 - SQL_ServerName "your_sql_server_name.com"
     cannonical name of your MySQL server you want to connect to
     If not specified, "localhost" will be used.

 - SQL_ServerPort number
     specifies the port number on which your SQL server installed
     on SQL_ServerName specified host listens for incomming
     connection. When not specified, default MySQL port is used
     (it's recommended to not use this command unless really needed).

 - SQL_SQLUser sql_user_name
     specifies MySQL user name the module shall authenticate as
     while connecting to the SQL server. When not given, the
     "sqlinclude" will be taken.

 - SQL_SQLPassword password
     specifies the SQL password for SQL_SQLUser. No default,
     so make sure you give one.

     NOTE: The password is stored in plain text, so make sure
           the httpd.conf is readable to noone except root!

 - SQL_Include sql_query
     this command does the thing. When invoked mod_sqlinclude module
     tries to connect to the server as specified using previously
     described command, and the perform the sql_query. The 1st
     column of returned rows will then be taken and passed to Apache
     for further processing.

     NOTE: as the module ignores other than 1st column, doing SELECTs
           like "SELECT * ..." instead if "SELECT column ..." just
           wastes your resources and makes NO real adventages.

 - SQL_AbortOnError On/Off
     When set to 'On' any apache syntax error (in included data) will
     cause module to abort further row processing. The default is 'Off'
     which will tell module to process all the data without taking much 
     respect for Apache opinion about that data

 - SQL_UseNameColumn On/Off
     When set to 'On', the mod_sqlinclude will treat row[1] of returned
     data as the descriptive string of the record (I suggest to make
     it unique). This increases readability of syntax error messages,
     as it become easier to find out which exactly record is buggy.
     Default is 'Off', which makes the mod to generate 'SQLxxx' name
     with xxx being replaced with row number. It may be useful only
     if order of your data is always the same (say you use ORDER BY)

     NOTE: make sure your SELECT clause returns TWO columns when using
           this option. Module simply expects it to be there and will
           try to use it *WITHOUT* any checks!
     	


Usage example
------------------

Example SQL database and records:

CREATE TABLE include (
   data text,
   name varchar(32) default 'Unnamed'
)

INSERT INTO include VALUES ('<VirtualHost 192.168.0.10>\nServerName
test1.server.com\nDocumentRoot
/home/carlos/test1\n</VirtualHost>','Server 1');
INSERT INTO include VALUES ('<VirtualHost 192.168.0.10>\nServerName
test2.server.com\nDocumentRoot
/home/carlos/test2\n</VirtualHost>','Server 2');

Above SQL code defines example table for use with mod_sqlinclude
and puts 2 virtual hosts definitions into the table. Now we want to 
tell the Apache to fetch and use the data

[snipplet of example httpd.conf - takes data from MyDB database]

SQL_ServerName  my.server.com
SQL_SQLUser     apache_sql
SQL_SQLPassword open_sessame
SQL_Include "SELECT data FROM MyDB.include WHERE ip='192.168.0.10'"

or alternatively:

SQL_ServerName  my.server.com
SQL_SQLUser     apache_sql
SQL_SQLPassword open_sessame
SQL_SQLDB       MyDB
SQL_Include "SELECT data FROM include WHERE ip='192.168.0.10'"


[another snipplet. example of using SQL_UseNameColumn]

SQL_ServerName  my.server.com
SQL_SQLUser     apache_sql
SQL_SQLPassword open_sessame
SQL_UseNameColumn On
SQL_Include "SELECT data,name FROM include"

License
----------

mod_sqlinclude is licensed under BSD license.


Bugs
----------

None known. The module will be revised soon, but as for now, I haven't
seen anything critical. Did you? Let me know!
