SQLite for AMX Mod X simple Mini-HOWTO

General discussion about Warcraft MODs for CS/CZ/DOD

Moderator: Forum Moderator

Locked
User avatar
Lazarus Long
Lead PITA SOB
Posts: 618
Joined: Tue Jul 05, 2005 9:24 pm

SQLite for AMX Mod X simple Mini-HOWTO

Post by Lazarus Long » Mon Aug 22, 2005 8:26 pm

[anchor]a0[/anchor]Index:
  1. I - [goto=a1]Prerequisites[/goto]

    II - [goto=a2]SQLite[/goto]
    1. [goto=a2d1]Command-line program installation[/goto]
      1. [goto=a2d1d1]Microsoft Windows[/goto]
      2. [goto=a2d1d2]GNU Linux[/goto]
    III - [goto=a3]AMX Mod X[/goto]
    1. [goto=a3d1]Stopping the server[/goto]
    2. [goto=a3d2]Configuring AMX Mod X[/goto]
    IV - [goto=a4]Converting to SQLite and creating admins[/goto]
    1. [goto=a4d1]Skipping (for fresh server installation)[/goto]
    2. [goto=a4d2]Preparing the database[/goto]
    3. [goto=a4d3]Converting AMX Mod X admins[/goto]
      1. [goto=a4d3d1]Converting from Files[/goto]
      2. [goto=a4d3d2]Converting from MySQL[/goto]
    4. [goto=a4d4]Creating AMX Mod X admins[/goto]
    V - [goto=a5]Wrap[/goto]
    1. [goto=a5d1]Starting the server[/goto]
[anchor]a1[/anchor]I - Prerequisites:
  1. [anchor]a1d1[/anchor]You are running the server (dedicated or listen) under Microsoft Windows or GNU Linux (The author has no experience with the *BSD flavors, although the Linux instructions might apply).
  2. [anchor]a1d2[/anchor]You have a working AMX Mod X enabled server using the latest version (at least 1.55).
    1. [anchor]a1d2da[/anchor]If you don't have a working AMX Mod X enabled server you may want to read the Installation and Configuration chapters of the AMX Mod X Documentation.
[anchor]a2[/anchor]II - SQLite:
  1. [anchor]a2d1[/anchor]Install the SQLite command-line program:
    1. [anchor]a2d1d1[/anchor]Microsoft Windows:
      1. [anchor]a2d1d1d1[/anchor]Download the latest 3.x version (at least 3.2.4) of the SQLite command-line program.
        1. [anchor]a2d1d1d1da[/anchor]You are advised to check the SQLite Downloads Page and download the latest version from the Precompiled Binaries For Windows section, since it is often updated.
      2. [anchor]a2d1d1d2[/anchor]Extract the content of the downloaded archive in a folder on your system. You can extract it anywhere, but for convenience you should be doing it in a folder that is in your PATH environment variable. For the rest of this document it is assumed you done the extraction in your System folder ("%SystemRoot%\system" for Microsoft Windows 9.x and ME or "%SystemRoot%\system32" for Microsoft Windows NT, 2000, XP and 2003).
      3. [anchor]a2d1d1d3[/anchor]Issue the following on a Command Prompt

        Code: Select all

        sqlite3
      4. [anchor]a2d1d1d4[/anchor]On the resulting interactive shell that you can identify as

        Code: Select all

        SQLite version 3.2.4
        Enter ".help" for instructions
        sqlite> 
        issue the following (note the dot in the beginning of the command)

        Code: Select all

        .quit
      5. [anchor]a2d1d1d5[/anchor]Congratulations, you got the SQLite command-line program installed. You can now [goto=a3]proceed to configure AMX Mod X[/goto].
    2. [anchor]a2d1d2[/anchor]GNU Linux:
      1. [anchor]a2d1d2d1[/anchor]There are a great number of GNU Linux distributions out there. Some have, others don't, the SQLite command-line program (also called a shell) available using each distribution native packaging system. Furthermore the distributions that carry the SQLite command-line program, might have the 2.x version packages or the newer 3.x or even both. Due to this you must consult your own package installation system and check first if your distribution carries the SQLite command-line program, and if so which version or versions.
        1. [anchor]a2d1d2d1d1[/anchor]If your GNU Linux distribution carries the 3.x version of the SQLite command-line program and it is equal or newer than 3.2.4, it's a no brainier, just stick with it and [goto=a2d1d2d2]proceed to test it out[/goto].
        2. [anchor]a2d1d2d1d2[/anchor]If on the other hand, your GNU Linux distribution doesn't carry the SQLite command-line program, or it does but a version older than 3.2.4 or you simply want the bleeding edge version of the program, download the latest 3.x version (at least 3.2.4) of the SQLite command-line program.
          1. [anchor]a2d1d2d1d2da[/anchor]You are advised to check the SQLite Downloads Page and download the latest version from the Precompiled Binaries For Linux section, since it is often updated.
        3. [anchor]a2d1d2d1d3[/anchor]Extract the content of the downloaded archive in a directory of your system. You can do this in two ways, [goto=a2d1d2d1d3d1]system wide[/goto] or for the [goto=a2d1d2d1d3d2]current user only[/goto]. Supposing you downloaded the SQLite command-line program in the "/tmp dir:
          1. [anchor]a2d1d2d1d3d1[/anchor]To install the binary system wide you must be logged as root. In a shell issue the following set of command one a time (replace the x.x.x with the version you downloaded)

            Code: Select all

            mkdir -p /usr/local/bin
            gunzip -c /tmp/sqlite3-x.x.x.bin.gz >/usr/local/bin/sqlite3-x.x.x.bin
            chmod 755 /usr/local/bin/sqlite3-x.x.x.bin
            ln -fs sqlite3-x.x.x.bin /usr/local/bin/sqlite3
          2. [anchor]a2d1d2d1d3d2[/anchor]To install the binary for the current user only, in a shell issue the following set of command one a time (replace the x.x.x with the version you downloaded)

            Code: Select all

            mkdir -p ~/bin
            gunzip -c /tmp/sqlite3-x.x.x.bin.gz >~/bin/sqlite3-x.x.x.bin
            chmod 755 ~/bin/sqlite3-x.x.x.bin
            ln -fs sqlite3-x.x.x.bin ~/bin/sqlite3
      2. [anchor]a2d1d2d2[/anchor]Issue the following in a shell

        Code: Select all

        sqlite3
        if you have downloaded and installed the binary yourself and the above command issued a "command not found" error retry it as

        Code: Select all

        /usr/local/bin/sqlite3
        if you installed system wide, or as

        Code: Select all

        ~/bin/sqlite3
        if you installed it for the current user only.
      3. [anchor]a2d1d2d3[/anchor]On the resulting interactive shell that you can identify as

        Code: Select all

        SQLite version 3.2.4
        Enter ".help" for instructions
        sqlite> 
        issue the following (note the dot in the beginning of the command)

        Code: Select all

        .quit
      4. [anchor]a2d1d2d4[/anchor]Congratulations, you got the SQLite command-line program installed. You can now [goto=a3]proceed to configure AMX Mod X[/goto].
[anchor]a3[/anchor]III - AMX Mod X:
  1. [anchor]a3d1[/anchor]Start by stopping your game server in the usual way for your system. If you are running a listen server simply quit the game, if you are running a dedicated server you may have several ways to stop it, if everything else fails issue the command

    Code: Select all

    quit
    in the game console.
  2. [anchor]a3d2[/anchor]You now have to configure AMX Mod X to access the SQLite database:

    1. [anchor]a3d2d1[/anchor]The AMX Mod X configuration files are text files and you will be editing them with your system text editor, either Notepad for Microsoft Windows, or vi for GNU Linux (your default editor might not be vi, so use the one you are used to, I'll be using vi for the remaining of this Mini-HOWTO, so replace where appropriate).
    2. [anchor]a3d2d2[/anchor]Locate your base directory for the game (don't ask me where it is, you installed the thing) and change to the AMX Mod X configuration folder.
      1. [anchor]a3d2d2d1[/anchor]Issue the following command in a Command Prompt for Microsoft Windows (replace <steam account> with your Steam login name and <game mod folder> with the game you are running)

        Code: Select all

        cd "SteamApps\<steam account>\<game mod folder>\addons\amxmodx\configs"
      2. [anchor]a3d2d2d2[/anchor]Issue the following command in a shell for GNU Linux (replace <game mod> with the game you are running)

        Code: Select all

        cd <game mod>/addons/amxmodx/configs
    3. [anchor]a3d2d3[/anchor]Edit the sql.cfg configuration file.
      1. [anchor]a3d2d3d1[/anchor]Issue the following command in a Command Prompt for Microsoft Windows

        Code: Select all

        notepad sql.cfg
      2. [anchor]a3d2d3d2[/anchor]Issue the following command in a shell for GNU Linux

        Code: Select all

        vi sql.cfg
      3. [anchor]a3d2d3d3[/anchor]Change the variable amx_sql_db to point to where you want the database file to live (don't worry if it doesn't exist yet it will be created automatically). It is highly recommended to keep it in the "addons\amxmodx\data" directory, so edit the file to read as follows (you can leave amx_sql_host, amx_sql_user, amx_sql_pass, and amx_sql_table with their default values since they are ignored by the SQLite module)

        Code: Select all

        // SQL configuration file
        // File location: $moddir/addons/amxmodx/configs/sql.cfg
        
        
        // *NOTE* Linux users may encounter problems if they specify "localhost" instead of "127.0.0.1"
        // We recommend using your server IP address instead of its name
        
        amx_sql_host	"127.0.0.1"
        amx_sql_user	"root"
        amx_sql_pass	""
        [color=green]amx_sql_db	"addons/amxmodx/data/amxx.db"[/color]
        amx_sql_table	"admins"
    4. [anchor]a3d2d4[/anchor]Edit the modules.ini file.
      1. [anchor]a3d2d4d1[/anchor]Issue the following command in a Command Prompt for Microsoft Windows

        Code: Select all

        notepad modules.ini
      2. [anchor]a3d2d4d2[/anchor]Issue the following command in a shell for GNU Linux

        Code: Select all

        vi modules.ini
      3. [anchor]a3d2d4d3[/anchor]Uncomment the MySQL modules (make absolutely sure you leave the other database modules commented), the database section must look like

        Code: Select all

        ; -------------------------------------------
        
        ; Database Access - only enable one of these
        ; -------------------------------------------
        ; MySQL
        ;mysql_amxx_i386.so
        ;mysql_amxx.dll
        ;mysql_amxx_amd64.so
        ; PostgreSQL
        ;pgsql_amxx_i386.so
        ;pgsql_amxx.dll
        ; Microsoft SQL
        ;mssql_amxx.dll
        ; SQLite
        [color=green]sqlite_amxx.dll
        sqlite_amxx_i386.so
        sqlite_amxx_amd64.so[/color]
    5. [anchor]a3d2d5[/anchor]Edit the plugins.ini file.
      1. [anchor]a3d2d5d1[/anchor]Issue the following command in a Command Prompt for Microsoft Windows

        Code: Select all

        notepad plugins.ini
      2. [anchor]a3d2d5d2[/anchor]Issue the following command in a shell for GNU Linux

        Code: Select all

        vi plugins.ini
        [anchor]a3d2d5d3[/anchor]Uncomment the SQL Admin Base plugin and make sure to comment the non-SQL one, the admin base section must look like

        Code: Select all

        ; AMX Mod X plugins
        
        ; Admin Base - Always one has to be activated
        ;admin.amxx		; admin base (required for any admin-related)
        [color=green]admin_sql.amxx[/color]		; admin base - SQL version (comment admin.amxx)
[anchor]a4[/anchor]IV - Converting to SQLite and creating admins:
  1. [anchor]a4d1[/anchor]If you don't have any AMX Mod X admins you would like to insert or convert to or SQLite database format you can [goto=a5]wrap it up[/goto].
  2. [anchor]a4d2[/anchor]Preparing the database:
    1. [anchor]a4d2d1[/anchor]For Microsoft Windows start up the SQLite command-line program by issuing the following command in a Command Prompt (note that you must point to the exact same path where the [goto=a3d2d3d3]amx_sql_db is pointing to[/goto], replace <steam folder> with the base path where you installed Steam, <steam account> with your Steam login name and <game mod folder> with the game you are running)

      Code: Select all

      sqlite3 "<steam folder>\SteamApps\<steam account>\<game mod folder>\addons\amxmodx\data\amxx.db"
    2. [anchor]a4d2d2[/anchor]For GNU Linux start up the SQLite command-line program by issuing the following command in a shell (note that you must point to the exact same path where the [goto=a3d2d3d3]amx_sql_db is pointing to[/goto], replace <base dir> with the base path where you installed your dedicated server and <game mod> with the game you are running)

      Code: Select all

      sqlite3 "<base dir>/<game mod>/addons/amxmodx/data/amxx.db"
    3. [anchor]a4d2d3[/anchor]Create the AMX Mod X admins table by issuing the following command in the SQLite interactive shell

      Code: Select all

      CREATE TABLE `admins` (`auth` VARCHAR(64) NOT NULL DEFAULT '', `password` VARCHAR(64) NOT NULL DEFAULT '', `access` VARCHAR(64) NOT NULL DEFAULT '', `flags` VARCHAR(64) NOT NULL DEFAULT '', PRIMARY KEY (`auth`)) ;
  3. [anchor]a4d3[/anchor]Converting AMX Mod X admins:
    1. [anchor]a4d3d1[/anchor]Converting from Files:
      1. [anchor]a4d3d1d1[/anchor]Populate the AMX Mod X admins table with your admins (you can find the values to enter in the following SQL statements in the users.ini file in your AMX Mod X configuration directory)
        1. [anchor]a4d3d1d1d1[/anchor]If your server is running on Microsoft Windows you are out of luck, you will have to do this by hand so issue the following commands in the SQLite interactive shell (the admin ID can be any one of IP, STEAM ID or player name)

          Code: Select all

          REPLACE INTO `admins` (`auth`, `password`, `access`, `flags`) VALUES ('<first admin ID>', '<first admin password>', '<first admin access levels>', '<first admin flags>');
          REPLACE INTO `admins` (`auth`, `password`, `access`, `flags`) VALUES ('<second admin ID>', '<second admin password>', '<second admin access levels>', '<second admin flags>');
          ...
          REPLACE INTO `admins` (`auth`, `password`, `access`, `flags`) VALUES ('<last admin ID>', '<last admin password>', '<last admin access levels>', '<last admin flags>');
        2. [anchor]a4d3d1d1d2[/anchor]If your server is running on GNU Linux you can either do as above for Microsoft Windows (recommended if you have 1 or 2 admins) or you may exit the SQLite interactive shell (as indicated in the [goto=a4d3d1d2]next point[/goto]) and issue the following command in a shell (note that you must point to the exact same path where the [goto=a3d2d3d3]amx_sql_db is pointing to[/goto], replace <base dir> with the base path where you installed your dedicated server and <game mod> with the game you are running)

          Code: Select all

          awk -F \" '{ if (/;|#|^ *$|\/\//) next ; if (NF != 9) next ; printf "REPLACE INTO `admins` (`auth`, `password`, `access`, `flags`) VALUES ( \"%s\", \x27%s\x27, \x27%s\x27, \x27%s\x27 );\n", $(NF-7), $(NF-5), $(NF-3), $(NF-1) }' <users.ini |sqlite3 "<base dir>/<game mod>/addons/amxmodx/data/amxx.db"
      2. [anchor]a4d3d1d2[/anchor]Exit the SQLite interactive shell by issuing the following command (note the dot in the beginning of the command)

        Code: Select all

        .quit
    2. [anchor]a4d3d2[/anchor]Converting from MySQL:
      1. [anchor]a4d3d2d1[/anchor]If you had your game server previously configured to read your admins from a database on a MySQL server you can convert it to SQLite:
        1. [anchor]a4d3d2d1d1[/anchor]Exit the SQLite interactive shell by issuing the following command (note the dot in the beginning of the command)

          Code: Select all

          .quit
        2. [anchor]a4d3d2d1d2[/anchor]For Microsoft Windows issue the following command in a Command Prompt (note that you must point to the exact same path where the [goto=a3d2d3d3]amx_sql_db is pointing to[/goto], replace <steam folder> with the base path where you installed Steam, <steam account> with your Steam login name and <game mod folder> with the game you are running, replace <amxx user> with the username you configured AMX Mod X to access MySQL, <amxx user password> with the password you set for that user, <mysql hostname or IP> with the MySQL server IP or name and <amxx database> with the name of the database you have configured for the above user)

          Code: Select all

          mysqldump --compact --skip-opt --complete-insert --no-create-info --user=<amxx user> --password=<amxx user password> --host=<mysql hostname or IP> <amxx database> admins |sqlite3 "<steam folder>\SteamApps\<steam account>\<game mod folder>\addons\amxmodx\data\amxx.db"
        3. [anchor]a4d3d2d1d3[/anchor]For GNU Linux issue the following command in a shell (note that you must point to the exact same path where the [goto=a3d2d3d3]amx_sql_db is pointing to[/goto], replace <base dir> with the base path where you installed your dedicated server and <game mod> with the game you are running, replace <amxx user> with the username you configured AMX Mod X to access MySQL, <amxx user password> with the password you set for that user, <mysql hostname or IP> with the MySQL server IP or name and <amxx database> with the name of the database you have configured for the above user)

          Code: Select all

          mysqldump --compact --skip-opt --complete-insert --no-create-info --user=<amxx user> --password=<amxx user password> --host=<mysql hostname or IP> <amxx database> admins |sqlite3 "<base dir>/<game mod>/addons/amxmodx/data/amxx.db"
  4. [anchor]a4d4[/anchor]Creating AMX Mod X admins:
    1. [anchor]a4d4d1[/anchor]For Microsoft Windows start up the SQLite command-line program by issuing the following command in a Command Prompt (note that you must point to the exact same path where the [goto=a3d2d3d3]amx_sql_db is pointing to[/goto], replace <steam folder> with the base path where you installed Steam, <steam account> with your Steam login name and <game mod folder> with the game you are running)

      Code: Select all

      sqlite3 "<steam folder>\SteamApps\<steam account>\<game mod folder>\addons\amxmodx\data\amxx.db"
    2. [anchor]a4d4d2[/anchor]For GNU Linux start up the SQLite command-line program by issuing the following command in a shell (note that you must point to the exact same path where the [goto=a3d2d3d3]amx_sql_db is pointing to[/goto], replace <base dir> with the base path where you installed your dedicated server and <game mod> with the game you are running)

      Code: Select all

      sqlite3 "<base dir>/<game mod>/addons/amxmodx/data/amxx.db"
    3. [anchor]a4d4d3[/anchor]Issue the following command in the SQLite interactive shell (the admin ID can be any one of IP, STEAM ID or player name, the possible values to use on the rest of the fields are explained in the Configuration: Admins chapter of the AMX Mod X Documentation, you can repeat this command with different values until you are out of admins)

      Code: Select all

      REPLACE INTO `admins` (`auth`, `password`, `access`, `flags`) VALUES ('<admin ID>', '<admin password>', '<admin access levels>', '<admin flags>');
    4. [anchor]a4d4d4[/anchor]One admin entry, which gives full admin rights to the game console itself, you most likely should be entering is

      Code: Select all

      REPLACE INTO `admins` (`auth`, `password`, `access`, `flags`) VALUES ('loopback','','abcdefghijklmnopqrstu','de');
[anchor]a5[/anchor]V - Wrap:
  1. [anchor]a5d1[/anchor]You are done, it's now time to start your game server in the usual way for your system.
OK, I hope I didn't make any big mistake, or forget something important, if so, feel free to correct me, and I'll try to update this Mini-HOWTO. Thanks for reading this far.


Edits:
  • 2005-08-28 - fixed a bunch of small typos
  • 2005-09-03 - changed the awk script to allow the ' character in names, by sugestion of anders_dog
  • 2005-09-10 - Updated the SQL CREATE statement to the new format with primary key
Lazarus

Locked