SoldatSOCI - database library

[Mr]

SOCI is a database abstraction layer originally written for C++, but fortunately the original author has written a C interface which works very well with Soldat's script core. I have ported the required header files to Pascal.

We already have a database library which SyavX wrote, but I really like SOCI more. TurboPascal's database library unfortunately lacks basic functionality such as escaping input, prepared statements or transactions.

Here is a code snippet of what it can do (error handling stripped):

Code: [Select]

stmt := SociCreateStatement(database);
sql := 'SELECT kills, deaths FROM stats WHERE name = :name';
SociUseString(stmt, 'name');
name := IDToName(ID);
SociSetUseString(stmt, 'name', name);
SociIntoInt(stmt);
SociIntoInt(stmt);
SociPrepare(stmt, sql);
if SociExecute(stmt, 1) <> SOCI_OK then begin
    WriteConsole(0, name + ' has no kills or deaths yet.', console_message_color);
    SociDestroyStatement(stmt);
    exit;
end;
kills := SociGetIntoInt(stmt, 0);
deaths := SociGetIntoInt(stmt, 1);
SociDestroyStatement(stmt);
if deaths = 0 then begin
    WriteConsole(0, name + ' has ' + IntToStr(kills) + ' kills and did not die - yet.', console_message_color);
    exit;
end;
ratio := Double(kills) / Double(deaths);
WriteConsole(0, name + '''s K/D ratio is ' + Round2(ratio) + ' (' + IntToStr(kills) + '/' + IntToStr(deaths) + ')', console_message_color);


Windows

Libraries used for the Windows build:
- SOCI, snapshot from 2012-11-11
- SQLite 3.7.14.1
- MySQL Connector C 6.0.2
- PostgreSQL 9.2.1
- OpenSSL 1.0.1c

First, download and install the Visual Studio 2010 SP1 Redistributable Package:

https://www.microsoft.com/en-us/download/details.aspx?id=8328

Next, download the following archive and unpack it to your dedicated server's root directory, so that the DLL files are on the same level as soldatserver.exe:

https://dl.dropbox.com/u/8261396/dev/soldat/soldatsoci-win32.zip

Use the file soldatserver-safe0.bat to start your server.


Linux

I have precompiled and bundled SOCI 3.1 with libraries for connecting to MySQL and sqlite3 databases:

https://dl.dropbox.com/u/8261396/dev/soldat/soldatsoci-linux32.tar.gz

Extract them to /opt/soldatsoci:

Code: [Select]

cd /tmp && wget https://dl.dropbox.com/u/8261396/dev/soldat/soldatsoci-linux32.tar.gz && cd /opt && sudo tar xf /tmp/soldatsoci-linux32.tar.gz
and launch your Soldat dedicated server like this:

Code: [Select]

LD_LIBRARY_PATH=/opt/soldatsoci SOCI_BACKENDS_PATH=/opt/soldatsoci ./soldatserver -safe 0
I have built and tested this bundle on Ubuntu 12.04 LTS 64-bit. If this works out-of-the-box, consider playing the lottery and have fun with this script.

Didn't work? Ouch. Read on. Long story short, you will have to build SOCI from source, and this is a mess. Not many Linux distros have SOCI in their repositories. If you are using Arch Linux, lucky you! Just install the soci package from AUR. Ubuntu/Debian unfortunately have outdated builds of SOCI which we cannot use here. But have no fear! You really only have to copy a few commands. If everything goes well, you are done in 10 minutes.

Here is a short and simple command to build and install SOCI with sqlite3 and MySQL support on Ubuntu/Debian. You will be prompted for your password for installing dependencies and copying the bundled files to /opt:

Code: [Select]

( cd /tmp && rm -rf soldatsoci-build && if [ $(uname -m) = 'x86_64' ]; then sudo apt-get -y install git unzip cmake build-essential patch gcc-multilib g++-multilib ia32-libs && cd /tmp && mkdir -p soldatsoci-build && cd soldatsoci-build && echo -e "set(CMAKE_SYSTEM_NAME Linux)\nset(CMAKE_C_COMPILER gcc -m32)\nset(CMAKE_CXX_COMPILER g++ -m32)" > toolchain-linux32.cmake && TOOLCHAIN_CMAKE_OPTIONS=-DCMAKE_TOOLCHAIN_FILE=$PWD/toolchain-linux32.cmake && TOOLCHAIN_GCC_OPTIONS=-m32; else sudo apt-get -y install git unzip cmake build-essential patch; fi && cd /tmp && mkdir -p soldatsoci-build/downloads && mkdir -p soldatsoci-build/sqlite3-src && cd soldatsoci-build/downloads && wget http://sqlite.org/sqlite-amalgamation-3071401.zip && unzip -j sqlite-amalgamation-3071401.zip -d ../sqlite3-src && cd ../sqlite3-src && gcc $TOOLCHAIN_GCC_OPTIONS -O2 -c -fpic sqlite3.c && gcc $TOOLCHAIN_GCC_OPTIONS -shared -o libsqlite3.so sqlite3.o && rm sqlite3.o && mkdir -p ../sqlite3 && mkdir -p ../sqlite3/include && mkdir -p ../sqlite3/lib && mv libsqlite3.so ../sqlite3/lib/ && cp *.h ../sqlite3/include/ && cd /tmp && cd soldatsoci-build && wget -O downloads/mysql-connector-c-6.0.2.tar.gz https://dev.mysql.com/get/Downloads/Connector-C/mysql-connector-c-6.0.2.tar.gz/from/http://cdn.mysql.com/ && mkdir -p mysql-connector-src && cd mysql-connector-src && tar --strip-components=1 -xvf ../downloads/mysql-connector-c-6.0.2.tar.gz && wget https://dl.dropbox.com/u/8261396/dev/mysql-connector-c-6.0.2-cmake.patch && patch -p1 < mysql-connector-c-6.0.2-cmake.patch && cd .. && mkdir -p mysql-connector-build && cd mysql-connector-build && cmake $TOOLCHAIN_CMAKE_OPTIONS -DCMAKE_INSTALL_PREFIX=$PWD/../mysql-connector -DHAVE_RINT=ON ../mysql-connector-src/ && cp include/* ../mysql-connector-src/include/ && make && make install && cd /tmp && cd soldatsoci-build && git clone git://git.code.sf.net/p/soci/code-0 soci-src && mkdir -p soci-build && cd soci-build && cmake $TOOLCHAIN_CMAKE_OPTIONS -DCMAKE_INSTALL_PREFIX=$PWD/../soci -DMYSQL_INCLUDE_DIR=$PWD/../mysql-connector/include -DMYSQL_LIBRARIES=$PWD/../mysql-connector/lib/libmysql.so -DSQLITE3_INCLUDE_DIR=$PWD/../sqlite3/include -DSQLITE3_LIBRARY=$PWD/../sqlite3/lib/libsqlite3.so -DSOCI_EMPTY=OFF -DWITH_BOOST=OFF -DSOCI_TESTS=OFF ../soci-src/src/ && make && make install && cd /tmp && cd soldatsoci-build && mkdir soldatsoci && cp -a sqlite3/lib/* soldatsoci && cp -a mysql-connector/lib/libmysql.* soldatsoci && cp -a soci/lib/libsoci_*.so* soldatsoci && chmod +x soldatsoci/* && sudo rm -rf /opt/soldatsoci && sudo cp -a soldatsoci /opt && echo 'Done!' )
Check that everything worked fine:

Code: [Select]

ubuntu@ubuntu:~$ ls /opt/soldatsoci
libmysql.so         libsoci_core.so        libsoci_mysql.so        libsoci_sqlite3.so        libsqlite3.so
libmysql.so.16      libsoci_core.so.3.1    libsoci_mysql.so.3.1    libsoci_sqlite3.so.3.1
libmysql.so.16.0.0  libsoci_core.so.3.1.0  libsoci_mysql.so.3.1.0  libsoci_sqlite3.so.3.1.0
Remove the build directory:

Code: [Select]

rm -rf /tmp/soldatsoci-build
Run your Soldat dedicated server like this:

Code: [Select]

LD_LIBRARY_PATH=/opt/soldatsoci SOCI_BACKENDS_PATH=/opt/soldatsoci ./soldatserver -safe 0
Long version:
https://dl.dropbox.com/u/8261396/dev/soldat/soldatsoci-compile.txt


Examples

An example script is included in the download which covers creating tables, reading a single or a set of rows, editing the database and using prepared statements. In fact it's a fully functional system that keeps track of all player's kills, provides a top-10 list and shows the player's ratio not only for the current map but for everything he has ever killed and died for. While this might of course not be the best stats system, it makes a great example others can base their project on. Have fun!

Windows: https://dl.dropbox.com/u/8261396/dev/soldat/soldatsoci-script-win32.zip
Linux: https://dl.dropbox.com/u/8261396/dev/soldat/soldatsoci-script-linux32.zip


Known issues

Using the /recompile command might mess things up since the script has no chance to close the database handle yet. According to our awesome devs this will be fixed in the next Soldat release (which will provide an event that will be called when a script is unloaded).


This is a script to access databases via Soldat's script core, not an anti-cheat. Don't ask.

[SyavX]

Good job! Looking forward to new scripts

[Mighty]

Good job! Looking forward to new scripts

Let's do this!

[Illuminatus]

This is a script to access databases via Soldat's script core, not an anti-cheat. Don't ask.

[CheeSeMan.]

This is a script to access databases via Soldat's script core, not an anti-cheat. Don't ask.

[Mighty]

Debian i686.

Since the easy way didn't work, I've been going through the long way from the .txt file you've linked there: click.
Everything was fine until I tried "make" in the "download the SOCI source code":

Code: [Select]

root@vps47446:~/soldatsoci-build/soci-build# make
[ 23%] Built target soci_core
[ 47%] Built target soci_core-static
[ 60%] Built target soci_sqlite3
[ 73%] Built target soci_sqlite3-static
[ 75%] Building CXX object backends/mysql/CMakeFiles/soci_mysql.dir/vector-use-type.cpp.o
In file included from /root/soldatsoci-build/soci-src/src/backends/mysql/vector-use-type.cpp:10:
/root/soldatsoci-build/soci-src/src/backends/mysql/soci-mysql.h:31:35: error: mysql.h: No such file or directory
In file included from /root/soldatsoci-build/soci-src/src/backends/mysql/vector-use-type.cpp:10:
/root/soldatsoci-build/soci-src/src/backends/mysql/soci-mysql.h:170: error: ISO C++ forbids declaration of 'MYSQL_RES' with no type
/root/soldatsoci-build/soci-src/src/backends/mysql/soci-mysql.h:170: error: expected ';' before '*' token
/root/soldatsoci-build/soci-src/src/backends/mysql/soci-mysql.h:188: error: 'MYSQL_ROW_OFFSET' was not declared in this scope
/root/soldatsoci-build/soci-src/src/backends/mysql/soci-mysql.h:188: error: template argument 1 is invalid
/root/soldatsoci-build/soci-src/src/backends/mysql/soci-mysql.h:188: error: template argument 2 is invalid
/root/soldatsoci-build/soci-src/src/backends/mysql/soci-mysql.h:247: error: ISO C++ forbids declaration of 'MYSQL' with no type
/root/soldatsoci-build/soci-src/src/backends/mysql/soci-mysql.h:247: error: expected ';' before '*' token
In file included from /root/soldatsoci-build/soci-src/src/backends/mysql/vector-use-type.cpp:11:
/root/soldatsoci-build/soci-src/src/backends/mysql/common.h:42: error: 'MYSQL' was not declared in this scope
/root/soldatsoci-build/soci-src/src/backends/mysql/common.h:42: error: 'conn' was not declared in this scope
/root/soldatsoci-build/soci-src/src/backends/mysql/common.h:42: error: expected primary-expression before 'const'
/root/soldatsoci-build/soci-src/src/backends/mysql/common.h:42: error: expected primary-expression before 'int'
/root/soldatsoci-build/soci-src/src/backends/mysql/common.h:42: error: initializer expression list treated as compound expression
/root/soldatsoci-build/soci-src/src/backends/mysql/vector-use-type.cpp: In member function 'virtual void soci::mysql_vector_use_type_backend::pre_use(const soci::indicator*)':
/root/soldatsoci-build/soci-src/src/backends/mysql/vector-use-type.cpp:74: error: 'struct soci::mysql_session_backend' has no member named 'conn_'
/root/soldatsoci-build/soci-src/src/backends/mysql/vector-use-type.cpp:74: error: 'soci::details::mysql::quote' cannot be used as a function
/root/soldatsoci-build/soci-src/src/backends/mysql/vector-use-type.cpp:83: error: 'struct soci::mysql_session_backend' has no member named 'conn_'
/root/soldatsoci-build/soci-src/src/backends/mysql/vector-use-type.cpp:84: error: 'soci::details::mysql::quote' cannot be used as a function
make[2]: *** [backends/mysql/CMakeFiles/soci_mysql.dir/vector-use-type.cpp.o] Error 1
make[1]: *** [backends/mysql/CMakeFiles/soci_mysql.dir/all] Error 2
make: *** [all] Error 2

Here's the previous command output if u need it:

Code: [Select]

-- Configuring SOCI:
-- SOCI_VERSION                             = 3.1.0
-- SOCI_ABI_VERSION                         = 3.1
-- SOCI_PLATFORM_NAME                       = x86
-- SOCI_COMPILER_NAME                       = gcc-4.4.5
-- SOCI_TESTS                               = OFF
-- Looking for SOCI dependencies:
-- Boost:disabled, since WITH_BOOST=OFF
-- MySQL:
-- Found MySQL: /root/soldatsoci-build/mysql-connector/include, /root/soldatsoci-build/mysql-connector/lib/libmysql.so
-- MySQL Embedded not found.
-- MYSQL_INCLUDE_DIR                        = /root/soldatsoci-build/mysql-connector/include
-- MYSQL_LIBRARIES                          = /root/soldatsoci-build/mysql-connector/lib/libmysql.so
-- ODBC:
-- WARNING:
-- ODBC not found, some libraries or features will be disabled.
-- See the documentation for ODBC or manually set these variables:
-- ODBC_INCLUDE_DIRECTORIES                 = ODBC_INCLUDE_DIRECTORIES-NOTFOUND
-- ODBC_LIBRARIES                           = ODBC_LIBRARY-NOTFOUND
-- Oracle:
-- WARNING:
-- Oracle not found, some libraries or features will be disabled.
-- See the documentation for Oracle or manually set these variables:
-- ORACLE_INCLUDE_DIR                       = 
-- ORACLE_LIBRARIES                         = 
-- PostgreSQL:
-- WARNING:
-- PostgreSQL not found, some libraries or features will be disabled.
-- See the documentation for PostgreSQL or manually set these variables:
-- POSTGRESQL_INCLUDE_DIR                   = POSTGRESQL_INCLUDE_DIR-NOTFOUND
-- POSTGRESQL_LIBRARIES                     = POSTGRESQL_LIBRARIES-NOTFOUND
-- POSTGRESQL_VERSION                       = unknown
-- SQLite3:
-- SQLITE3_INCLUDE_DIR                      = /root/soldatsoci-build/sqlite3/include
-- SQLITE3_LIBRARIES                        = /root/soldatsoci-build/sqlite3/lib/libsqlite3.so
-- Configuring SOCI core library:
-- SOCI_CORE_TARGET                         = soci_core
-- SOCI_CORE_TARGET_OUTPUT_NAME             = soci_core
-- SOCI_CORE_DEPENDENCIES                   = -lpthread /usr/lib/libdl.so
-- WITH_BOOST                               = OFF
-- COMPILE_DEFINITIONS                      = SOCI_ABI_VERSION="3.1" HAVE_DL=1 SOCI_LIB_PREFIX="libsoci_" SOCI_LIB_SUFFIX=".so"
-- Configuring SOCI database backends:
-- Found MySQL: /root/soldatsoci-build/mysql-connector/include, /root/soldatsoci-build/mysql-connector/lib/libmysql.so
-- MySQL Embedded not found.
-- SQLite3 - SOCI backend for SQLite 3 database engine
-- SOCI_SQLITE3                             = ON
-- SOCI_SQLITE3_TARGET                      = soci_sqlite3
-- SOCI_SQLITE3_TARGET_OUTPUT_NAME          = soci_sqlite3
-- SOCI_SQLITE3_HEADERS                     = soci-sqlite3.h common.h
-- COMPILE_DEFINITIONS                      = SOCI_ABI_VERSION="3.1" HAVE_SQLITE3=1
-- Empty - SOCI backend skeleton for development of new backends
-- Empty backend disabled, since
-- SOCI_EMPTY                               = OFF
-- MySQL - SOCI backend for MySQL database engine
-- SOCI_MYSQL                               = ON
-- SOCI_MYSQL_TARGET                        = soci_mysql
-- SOCI_MYSQL_TARGET_OUTPUT_NAME            = soci_mysql
-- SOCI_MYSQL_HEADERS                       = soci-mysql.h common.h
-- COMPILE_DEFINITIONS                      = SOCI_ABI_VERSION="3.1" HAVE_MYSQL=1
--
-- Configuring done
-- Generating done
-- Build files have been written to: /root/soldatsoci-build/soci-build

Also, on the other server the script worked well for like a week, and after another recompile it stopped working. "Unable to find variable" all day long.

[Mr]

Hey, it looks like your ~/soldatsoci-build/mysql-connector/ directory does not exist or is empty as the header file ~/soldatsoci-build/mysql-connector/include/mysql.h cannot be found.

I am not sure whether you skipped building the MySQL connector or building it failed. If you decided to skip MySQL support, you need to remove the mysql options from the SOCI cmake command.
First, clean the soci build directory (rm -rf soci-build && mkdir soci-build && cd soci-build) and run this altered command without MySQL support:

Code: [Select]

cmake $TOOLCHAIN_CMAKE_OPTIONS -DCMAKE_INSTALL_PREFIX=$PWD/../soci -DSQLITE3_INCLUDE_DIR=$PWD/../sqlite3/include -DSQLITE3_LIBRARY=$PWD/../sqlite3/lib/libsqlite3.so -DSOCI_EMPTY=OFF -DWITH_BOOST=OFF -DSOCI_TESTS=OFF ../soci-src/src/
If you left MySQL support enabled, it is possible that building the MySQL connector failed entirely (run "make" in the mysql-connector-build directory and check if the last lines say something that looks like an error message), or you forgot to run "make install" in the MySQL connector build directory.


Like I noted in the first post, recompiling is not reliable with the current script core, the "Unable to find variable" error you get might be related to this. I do not exactly know what the "Unable to find variable" error means - I got it once when the Soldat server was unable to load the SOCI shared library, but I cannot think of a reason why this would happen during recompilation of scripts (the environment variables do not change, do they)? Either way, restarting the Soldat server using the command line from the first post should fix it.

[Mighty]

Hey,

I skipped MySQL, SQLite is all I needed. On the second attempt though I tried running all commands listed, including MySQL section.
Line you gave me worked fine. I've completed the longer route then (2nd time now), but I still get the "Unable to find variable".

Maybe that helps?

root@xxx:/opt/soldatsoci# ls
libmysql.so     libmysql.so.16.0.0  libsoci_mysql.a    libsqlite3.so
libmysql.so.16  libsoci_core.a      libsoci_sqlite3.a

Either way, restarting the Soldat server using the command line from the first post should fix it.

Yeah, the funny thing is, it doesn't help. I can't start the server, each time I try it gives me... Unable to find variable

Edit: do you need anything from me? Running a specific command or something that would give you an idea of what's wrong?

[Mr]

root@xxx:/opt/soldatsoci# ls
libmysql.so     libmysql.so.16.0.0  libsoci_mysql.a    libsqlite3.so
libmysql.so.16  libsoci_core.a      libsoci_sqlite3.a

You have a typo in the commands creating the bundle for /opt, or your shell is weird - in this case instead of "cp -a soci/lib/!(*.a) soldatsoci" something like "cp -a soci/lib/*.a soldatsoci" has been used (only the *.a files are present, but they should have been skipped and only the .so files from soci/lib/ should have been copied).

Yeah, the funny thing is, it doesn't help. I can't start the server, each time I try it gives me... Unable to find variable

Please post the output of the following command:
ls -la /opt/soldatsoci

[Mighty]

Yea I knew I forgot about one command I changed. My bash cried about "cp -a soci/lib/!(*.a) soldatsoci" --> "-bash: !: event not found", so I thought I'd remove !( )  (I didn't know what it does). Yea, stupid, I know, sorry for that :< . Ok, I've done that more manually.

Works now. God damn, I've nearly given up...
Thank you so much.

Anyway, the cmd...

Code: [Select]

root@xxx:/opt/soldatsoci# ls -la /opt/soldatsoci
total 4540
drwxr-xr-x 2 root root    4096 Dec 12 17:25 .
drwxr-xr-x 3 root root    4096 Dec 12 16:56 ..
lrwxrwxrwx 1 root root      14 Dec 12 16:51 libmysql.so -> libmysql.so.16
lrwxrwxrwx 1 root root      18 Dec 12 16:51 libmysql.so.16 -> libmysql.so.16.0.0
-rwxr-xr-x 1 root root 2405301 Dec 12 11:20 libmysql.so.16.0.0
lrwxrwxrwx 1 root root      19 Dec 12 09:15 libsoci_core.so -> libsoci_core.so.3.1
lrwxrwxrwx 1 root root      21 Dec 12 09:15 libsoci_core.so.3.1 -> libsoci_core.so.3.1.0
-rw-r--r-- 1 root root 1111399 Dec 12 16:53 libsoci_core.so.3.1.0
lrwxrwxrwx 1 root root      20 Dec 12 16:53 libsoci_mysql.so -> libsoci_mysql.so.3.1
lrwxrwxrwx 1 root root      22 Dec 12 16:53 libsoci_mysql.so.3.1 -> libsoci_mysql.so.3.1.0
-rw-r--r-- 1 root root  286691 Dec 12 16:53 libsoci_mysql.so.3.1.0
lrwxrwxrwx 1 root root      22 Dec 12 09:15 libsoci_sqlite3.so -> libsoci_sqlite3.so.3.1
lrwxrwxrwx 1 root root      24 Dec 12 09:15 libsoci_sqlite3.so.3.1 -> libsoci_sqlite3.so.3.1.0
-rw-r--r-- 1 root root  259723 Dec 12 16:53 libsoci_sqlite3.so.3.1.0
-rwxr-xr-x 1 root root  569089 Dec 12 11:16 libsqlite3.so


BTW the "broke-after-recompile" thing was on another host and still occurs, but I'll get back to it if I have to, only.

[Mr]

EDIT 2: I just noticed the execute bit is missing for all library files. That might be the issue. You probably forgot running "chmod +x soldatsoci/*", give that a try.

That looks good so far; here's some more, please post the output (EDIT: on the server where SOCI compiled but does not work due to that mysterious "Unable to find variable" error after recompiling):

uname -a
file /opt/soldatsoci/libsoci_core.so.3.1.0
file /opt/soldatsoci/libsoci_mysql.so.3.1.0
file /opt/soldatsoci/libsoci_sqlite3.so.3.1.0

Also, please post the exact command line you use to start your Soldat server (do not alter anything to match the paths we use here, I need the original command).

(It looks like extended globbing is disabled by default on your system. "shopt -s extglob" enables it. I will adjust the instructions not to use extended globbing in case that someone else uses a similar setup.)

[Mighty]

Oh, I did chmod +x, nvm, server works (did chmod again though, x is there now)

I was starting server with the command you gave here, exact copy paste, no room for mistakes.
I will post results when I'll get access to the machine again (if I do so).

Thank you again for all the help