SQLite for Warcraft 3 XP simple Mini-HOWTO

This is the place for general discussion about Warcraft3 XP, including install 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 XP simple Mini-HOWTO

Post by Lazarus Long » Sun Aug 28, 2005 2:05 pm

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

    II - [goto=a2]Converting XP points to SQLite[/goto]
    1. [goto=a2d1]Skipping (for fresh server installation)[/goto]
    2. [goto=a2d2]Preparing the database[/goto]
    3. [goto=a2d3]Converting Warcraft 3 XP points[/goto]
      1. [goto=a2d3d1]Converting from Files[/goto]
      2. [goto=a2d3d2]Converting from MySQL[/goto]
    III – [goto=a3]Wrap[/goto]
    1. [goto=a3d1]Starting the server[/goto]
[anchor]a1[/anchor]I - Prerequisites:
  1. [anchor]a1d1[/anchor]You have a working Warcraft 3 XP server installation using either the vault.ini file or the MySQL server for XP storage (later on you'll be [goto=a2]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 XP (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 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 - Converting XP to SQLite:
  1. [anchor]a2d1[/anchor]If you don't have any Warcraft 3 XP points you would like to convert to SQLite database format you can [goto=a3]wrap it up[/goto].
  2. [anchor]a2d2[/anchor]Preparing the database:
    1. [anchor]a2d2d1[/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 [gotopost=2226,a3d2d3d3]amx_sql_db is pointing to[/gotopost], 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]a2d2d2[/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 [gotopost=2226,a3d2d3d3]amx_sql_db is pointing to[/gotopost], 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]a2d2d3[/anchor]Create the Warcraft 3 XP points 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]a2d3[/anchor]Converting Warcraft 3 XP points:
    1. [anchor]a2d3d1[/anchor]Converting from Files:
      1. [anchor]a2d3d1d1[/anchor]Populate the Warcraft 3 XP points 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). 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]a2d3d1d2[/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]a2d3d2[/anchor]Converting from MySQL:
      1. [anchor]a2d3d2d1[/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]a2d3d2d1d1[/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]a2d3d2d1d2[/anchor]For Microsoft Windows issue the following command in a Command Prompt (note that you must point to the exact same path where the [gotopost=2226,a3d2d3d3]amx_sql_db is pointing to[/gotopost], 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]a2d3d2d1d3[/anchor]For GNU Linux issue the following command in a shell (note that you must point to the exact same path where the [gotopost=2226,a3d2d3d3]amx_sql_db is pointing to[/gotopost], 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]a3[/anchor]III - Wrap:
  1. [anchor]a3d1[/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-10-02 - changed the SQL CREATE statement to mirror the one in the plugin itself
  • 2005-10-02 - removed the awk script useage for GNU Linux since it was broken[/i]
Lazarus

Locked