This repository has been archived on 2025-02-12. You can view files and clone it, but cannot push or open issues or pull requests.
NeoStats/doc/README.sqlsrv
2004-01-14 11:36:37 +00:00

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.
**************************************************************/