Index of /ports/www/mod_sqlinclude/work/mod_sqlinclude-1.4

      Name                    Last modified       Size  Description


[DIR] Parent Directory        21-Jan-2008 12:05      -  
[TXT] CHANGES.txt             13-Feb-2002 15:39     2k  
[TXT] LICENSE.txt             13-Feb-2002 15:33     1k  
[   ] Makefile                10-Jun-2001 09:32     1k  
[   ] mod_sqlinclude.c        13-Feb-2002 15:19    13k  
[   ] mod_sqlinclude.o        21-Jan-2008 12:04    10k  
[   ] test.sql                14-Jan-2002 14:05     1k  

$Id: README.txt,v 1.11 2002/01/20 19:01:52 carlos Exp $

------------------------------------------------------------
                 SQL Include Apache module
             (C) 2001-2002 by Marcin Orlowski
                        Webnet ISP
------------------------------------------------------------
    carlos@webnet.pl          http://webnet.pl/~carlos/
------------------------------------------------------------
  SF Home: http://sourceforge.net/projects/mod-sqlinclude/
  FM Page: 
------------------------------------------------------------
                         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
   (the file named mod_sqlinclude.c)
 - type 'make' (w/o quotes) and press ENTER/Return
 - become root (required, otherwise you won't be able to install the
   module and then use it
 - type 'make install'
 - restart apache server ("apachectl restart" or "make restart")
 - enjoy.

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: Imagine you got 120 virtual hosts 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 (one for each vhost) for further
   easy meainteance (i.e. removing, editing - you just play with single
   record, not with a portion of it). If you are dare, you can of
   course put all the 120 VHosts into one big record. It will work.
   But what you cannot have is to fraction configuration of one VHost
   among records.  Every database record is treated separately, so
   the server won't be able to 'continue' the configuration you started
   in previous record. 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' item. The simplest rule
   of the thumb to avoid problems: keep all logical blocks in 
   single record. Alternatively 1 - 1..n (1 record for 1 or more
   logical blocks).

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> command does. And Apache process the data exactly
   the same way as it does for disk config files. 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'.

Q: Is it SQLInclude limited to act on vhosts only or I can have
   put any Apache configuration directives in my SQL database?
A: No, SQLInclude is NOT limited in any way. You can include
   whatever you want, as long as Apache does not mind itself.
   I repeat: it works the same way ordinary, file-based Include
   command work. If Appache let you move any part of your configuration
   to external source I see no reason why you could not be able
   to include it using SQLInclude.


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!
     	

NOTE: The way mod_sqlinclude handles 2 column queries will change in the
      next release. Recent implementation is not intuitive and simply
      sucks.


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! SourceForge keeps
bugracker for this and I'd stronly insist to use them at module
home page: http://sourceforge.net/projects/mod-sqlinclude/