SQLite for Warcraft 3 FT simple Mini-HOWTO

Read log files for errors! If this fails, come here for help

Moderator: Forum Moderator

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

SQLite for Warcraft 3 FT simple Mini-HOWTO

Post by Lazarus Long » Tue Aug 23, 2005 6:02 am

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

    II - [goto=a2]Warcraft 3 FT[/goto]
    1. [goto=a2d1]Stopping the server[/goto]
    2. [goto=a2d2]Configuring Warcraft 3 FT[/goto]
    III - [goto=a3]Converting XP to SQLite[/goto]
    1. [goto=a3d1]Skipping (for fresh server installation)[/goto]
    2. [goto=a3d2]Preparing the database[/goto]
    3. [goto=a3d3]Converting Warcraft 3 FT XP[/goto]
      1. [goto=a3d3d1]Converting from Files[/goto]
      2. [goto=a3d3d2]Converting from MySQL[/goto]
    IV - [goto=a4]Wrap[/goto]
    1. [goto=a4d1]Starting the server[/goto]
[anchor]a1[/anchor]I - Prerequisites:
  1. [anchor]a1d1[/anchor]You have a working Warcraft 3 FT server installation using either the vault.ini file or the MySQL server for XP storage (later on you'll be [goto=a3]converting it[/goto]).
  2. [anchor]a1d2[/anchor]You have the latest version (post 1.55) of AMX Mod X or, if you are running versions 1.50/1.55, you have installed the SQLite pack for Warcraft 3 FT (please pay attention, since this is different from what is required in the following point).
  3. [anchor]a1d3[/anchor]You have read, followed and completed the SQLite for AMX Mod X simple Mini-HOWTO (The simple Mini-HOWTO series structure is being modularized so this document you are reading contains only partial information, you will not be able to complete it without following the above link).
[anchor]a2[/anchor]II - Warcraft 3 FT:
  1. [anchor]a2d1[/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]a2d2[/anchor]You now have to configure Warcraft 3 FT to access the SQLite database:
    1. [anchor]a2d2d1[/anchor]The Warcraft 3 FT configuration file is a text file and you will be editing it 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]a2d2d2[/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]a2d2d2d1[/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, either "Counter-Strike\cstrike", "Condition Zero\czero", "Day of Defeat\dod", "Dedicated Server\cstrike", "Dedicated Server\czero" or "Dedicated Server\dod")

        Code: Select all

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

        Code: Select all

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

        Code: Select all

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

        Code: Select all

        vi war3FT.cfg
      3. [anchor]a2d2d3d3[/anchor]Change the variable FT_mysql_db (yes FT_mysql_db is correct, all SQL related variables in Warcarft 3 FT reflect their inheritance of former MySQL only support) 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 the following snippet of a changed war3FT.cfg (you can leave FT_mysql_host, FT_mysql_user and FT_mysql_pass with their default values since they are ignored by the SQLite module but you should set both mp_savexp and sv_mysql, you might also want to have a look at sv_mysql_save_end_round and/or at sv_mysql_auto_pruning and sv_daysbeforedelete)

        Code: Select all

        // Warcraft 3 Frozen Throne Configuration File
// Note: NO MATTER WHAT, you must have [color=green]a database[/color] module running
// even if you're not using it

[color=green]mp_savexp		1[/color]		// Enables saving of experience (uses a vault, default is 0)
[color=green]sv_mysql		1[/color]		// this will save w/MySQL (note: to use this mp_savexp must also be 1, default is 0)
FT_saveby		0		// What should I save this as?  steam id = 0, IP = 1, name = 2 (works for vault and mysql, default is 0)

// MySQL configuration
FT_mysql_host		"127.0.0.1"	// Host Name
FT_mysql_user		"root"		// User Name
FT_mysql_pass		""		// Password
[color=green]FT_mysql_db		"addons/amxmodx/data/amxx.db"[/color]		// Database Name
sv_mysql_auto_pruning	0		// Automatically prunes the database of old users at a mapchange occuring between 5:36 to 5:59 AM (default is 0, doesn't work for vault)
sv_daysbeforedelete	31		// However many days before deleting XP or pruning from MySQL database (default is 31)
sv_mysqltablename	"war3users"	// Table Name, no need to change (default is war3users)
sv_mysql_save_end_round 0		// Save Users at the end of each round (causes slight lag, default is 0)
FT_disable_savexp	0		// Set this to 1 to disable users typing /savexp (default is 0)
[anchor]a3[/anchor]III - Converting XP to SQLite:
  1. [anchor]a3d1[/anchor]If you don't have any Warcraft 3 FT XP you would like to convert to SQLite database format you can [goto=a4]wrap it up[/goto].
  2. [anchor]a3d2[/anchor]Preparing the database:
    1. [anchor]a3d2d1[/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=a2d2d3d3]FT_mysql_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]a3d2d2[/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=a2d2d3d3]FT_mysql_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]a3d2d3[/anchor]Create the Warcraft 3 FT XP table by issuing the following command in the SQLite interactive shell

      Code: Select all

      CREATE TABLE `war3users` (`playerid` VARCHAR(35) NOT NULL DEFAULT '', `playername` VARCHAR(35) NOT NULL DEFAULT '', `xp` INT(11) NOT NULL DEFAULT 0, `race` TINYINT(4) NOT NULL DEFAULT 0, `skill1` TINYINT(4) NOT NULL DEFAULT 0, `skill2` TINYINT(4) NOT NULL DEFAULT 0, `skill3` TINYINT(4) NOT NULL DEFAULT 0, `skill4` TINYINT(4) NOT NULL DEFAULT 0, `time` TIMESTAMP(14) NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`playerid`, `race`)) ;
  3. [anchor]a3d3[/anchor]Converting Warcraft 3 FT XP:
    1. [anchor]a3d3d1[/anchor]Converting from Files:
      1. [anchor]a3d3d1d1[/anchor]Populate the Warcraft 3 FT XP table with your players XP (you can find the values to enter in the following SQL statements in the vault.ini file in your AMX Mod X data directory)
        1. [anchor]a3d3d1d1d1[/anchor]If your server is running on Microsoft Windows you are really out of luck, you will have to do this by hand (and if so be aware that there might be hundreds of entries in your vault.ini) so issue the following commands in the SQLite interactive shell

          Code: Select all

          REPLACE INTO `war3users` (`playerid`, `playername`, `xp`, `race`, `skill1`, `skill2`, `skill3`, `skill4`) VALUES ('<1st user id>', '<1st user name>', <1st user XP>, <1st user race>, <1st user skill1 level>, <1st user skill2 level>, <1st user skill3 level>, <1st user skill4 level>) ;

REPLACE INTO `war3users` (`playerid`, `playername`, `xp`, `race`, `skill1`, `skill2`, `skill3`, `skill4`) VALUES ('<2nd user id>', '<2nd user name>', <2nd user XP>, <2nd user race>, <2nd user skill1 level>, <2nd user skill2 level>, <2nd user skill3 level>, <2nd user skill4 level>) ;
...
REPLACE INTO `war3users` (`playerid`, `playername`, `xp`, `race`, `skill1`, `skill2`, `skill3`, `skill4`) VALUES ('<last user id>', '<last user name>', <last user XP>, <last user race>, <last user skill1 level>, <last user skill2 level>, <last user skill3 level>, <last user skill4 level>) ;
        2. [anchor]a3d3d1d1d2[/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 players) or you may exit the SQLite interactive shell (as indicated in the [goto=a3d3d1d2]next point[/goto]) and issue the following command in a shell (note that you must point to the exact same path where the [goto=a2d2d3d3]FT_mysql_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 '{ if (/;|#|^ *$|\/\//) next ; if (NF != 14) next ; printf "REPLACE INTO `war3users` (`playerid`, `playername`, `xp`, `race`, `skill1`, `skill2`, `skill3`, `skill4`) VALUES (\"%s\", \"%s\", %s, %s, %s, %s, %s, %s);\n", substr($(NF-13), 0, length($(NF-13))-2), $(NF-5), $(NF-11), $(NF-10), $(NF-9), $(NF-8), $(NF-7), $(NF-6)}' <../data/vault.ini ||sqlite3 "<base dir>/<game mod>/addons/amxmodx/data/amxx.db"
      2. [anchor]a3d3d1d2[/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]a3d3d2[/anchor]Converting from MySQL:
      1. [anchor]a3d3d2d1[/anchor]If you had your game server previously configured to save/restore your players XP to/from a database on a MySQL server you can convert it to SQLite:
        1. [anchor]a3d3d2d1d1[/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]a3d3d2d1d2[/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=a2d2d3d3]FT_mysql_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> war3users |sqlite3 "<steam folder>\SteamApps\<steam account>\<game mod folder>\addons\amxmodx\data\amxx.db"
        3. [anchor]a3d3d2d1d3[/anchor]For GNU Linux issue the following command in a shell (note that you must point to the exact same path where the [goto=a2d2d3d3]FT_mysql_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> war3users |sqlite3 "<base dir>/<game mod>/addons/amxmodx/data/amxx.db"
[anchor]a4[/anchor]IV - Wrap:
  1. [anchor]a4d1[/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 typos in hyperlinks
  • 2005-09-03 - changed the awk script to allow the ' character in names, by sugestion of anders_dog
  • 2005-10-02 - changed the SQL CREATE statement to mirror the one in the plugin itself
  • 2005-10-02 - added reference paths for a dedicated server install under Microsoft Windows
Lazarus
Top
Locked

Return to “Help/Support”