118 lines
5.5 KiB
Text
Executable file
118 lines
5.5 KiB
Text
Executable file
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 <user>!<pass>@<host>"
|
|
Where:
|
|
<user> is the Postgres User you wish to authenticate with.
|
|
<pass> is the password you will use to authenticate with.
|
|
<host> 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 <port>"
|
|
where:
|
|
<port> is the port number you wish to use.
|
|
|
|
4) Start NeoStats as normal, and check neostats-<date>.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 <neostats BINDTO variable, or IP> -p <portnumber of SQL Server> -U
|
|
<username>" 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-<version>/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.
|
|
**************************************************************/
|