SQL Emulation Readme File 16/12/2004 ==================================================================== NeoStats now has the ability to emulate a Postgres SQL server natively, what this means is that you don't need to operate a external SQL server (such as Postgres or MySql) and export data to that Server. NeoStats looks and acts like the real thing. This means that you can write PHP/Perl scripts to access data contained within neostats in real time, and do not have to wait for NeoStats to update a external SQL Server. Those people farmilar with software such as Thales, or Anope with SQL support will already be farmilar with the concept of storing information about your IRC network in a SQL database already. The differing factor between NeoStats and these implementation(1) is that NeoStats is real time. There are some limitations to the SQL language though: 1) Function calls such as MAX(), COUNT() etc are not implemented, and your query will return a error if you try to use them. 2) No Sorting is available. So you can't say, "Select * from users order by server". The order that the data is returned to you can seem completely Random. 3) No Joins are available. You can't do "Select * from users, chans". Again just returns a error. 4) the output buffer is currently limited. On my network with about 500+ users, I can't do a "Select * from users" as its just too much data. This will be fixed in a future version. See point 5 for a work around. 5) Limit and offset keywords work. Eg: "Select * from users limit 100, offset 100" will only output 100 records, starting at record 100. 6) basic comparision and math functions are available. eg: "Select * from chans where nomems > 10", "Select * from users where nick="Fish";" 7) obviously, things like "Delete from users" or "Insert into users" isn't functioning. While the RTA library enables us to make "update" statements work, all the tables exported are readonly. In the future, we may enable modules such as "HostServ" to have a update ability via this interface, but that may be a long way into the future. Getting Started: ================ 1) make sure you configure NeoStats with the option --enable-sqlsrv 2) Follow the normal installation instructions found in the main NeoStats README. 3) before starting NeoStats, the following must be added to neostats.cfg: "SQLSRV_AUTH !@" Where: is the Postgres User you wish to authenticate with. is the password you will use to authenticate with. is the hostname (IP address only) that you will connect with. Wildcards are accepted. (Eg: 202.181.* or 127.0.0.1) The default port for the SQL Server emulation is port 8888 (and if you have configured the BIND_TO config variable, it will be that port, otherwise it will attempt to bind to port 8888 on all interfaces/ip). If port 8888 is not available, you can configure a different port by using the option: "SQLSRV_PORT " where: is the port number you wish to use. 4) Start NeoStats as normal, and check neostats-.log for any errors about unable to start the SQL Server Emulation. Errors about starting the SQL Server usually are a result of the port already being in use. 5) if you have the postgresql client tools available on the host you configured in part (3), you can test from the commandline with the following syntax: "psql -h -p -U " and it should prompt you for a password. Enter your configured password. Once in psql, you can verify that everything is functioning correctly by issueing the following command: "select * from rta_tables;" (include the ;, but not the ") This command will return a list of tables that you can query. included in the NeoStats-/tools/sqlphp/ directory are two example PHP scripts that you can try from a PHP enabled WebServer. You must first edit these scripts to configure the required usernames, hostnames and passwords. Optionally, if your PHP uses external modules (as some RedHat PHP RPMS do), you should ensure that the 'dl("psql.so");' lines are uncommented. Note, YOU MUST HAVE A POSTGRESQL ENABLED COPY OF PHP INSTALLED. Its outside the scope of this Readme file on compiling/configuring PHP, so you should consult the PHP manual, or your System Administrator. Once you have edited those two files, drop them into a Web directory, and point your browser at rta_tables.php. It should give you a list of available tables, and hyperlinks to view more information. Currently, the Core NeoStats and StatServ export data via this SQL emulation layer. We will be slowly upgrading more modules to export more data. If your a decent PHP coder, and want to contribute back to the NeoStats community (Pleeeeeaaaaaase!), consider writting a PHP application that does some "Fancy" stuff with this interface, and we will include it in this distribution (and of course, list you as the Developer of the app!). CREDITS: ======== The library was modified slightly to work with NeoStats, but much of the core implementation comes from the original. The Sql Emulation library was writteen by Robert W Smith (bsmith@linuxtoys.org), and is known as "Run Time Access" Please see http://www.linuxappliancedesign.com for more information and a working example. /*************************************************************** * Run Time Access * Copyright (C) 2004 Robert W Smith (bsmith@linuxtoys.org) * * This program is distributed under the terms of the GNU LGPL. * See the file COPYING file. **************************************************************/