Setting up Admin Mod with MySQL

General Information

To run Admin Mod in combination with a MySQL database you have to setup the database and then configure Admin Mod to use it. The information that Admin Mod can retrieve from a database is the information normally kept in the files users.ini, models.ini, ips.ini, wordlist.txt and plugins.ini. You can still use the files with the MySQL version of Admin Mod. You can mix file and database operation but the database tables, if specified, will take precedence. Data is read only from tables or files, not both. If Admin Mod fails to read data from the specified MySQL table it will fall back to using files. Please read the manual first to understand how to set up Admin Mod in general and what the configuration files are about and their contents.

MySQL database setup

On your MySQL server you will have to set up a database to store the data for Admin Mod to use. What name you give to the database and what tables it contains is up to you. You can also have more fields in tables than those described below. This document only describes the maximum set of tables and columns available for a complete Admin Mod setup. You can leave out tables which you don't need or add other tables unrelated to Admin Mod. The names for the tables are also free. The only thing which you cannot choose freely are the column names. But we get to that later.

The database

Let's start with the database. For this example we choose to call it adminmod. You can choose another name. If you haven't created a database, yet, do so now with the CREATE DATABASE command. Refer to the MySQL manual for any details concerning MySQL commands and syntax.

mysql> CREATE DATABASE adminmod;

The users table

Next we create the table holding the admin users. We choose to call it users. You can give it another name. The users table has three columns: nick, pass and access. These columns correspond to the three fields for entries in the file specifies by the users_file cvar. nick is the nickname of the user, pass is his password and access his access level.

mysql> CREATE TABLE users( nick VARCHAR(30) PRIMARY KEY NOT NULL, 
   --> pass VARCHAR(20) NOT NULL, access INTEGER UNSIGED NOT NULL);



mysql> DESCRIBE users;



+--------+-------------------+------+-----+---------+-------+
| Field  | Type              | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| nick   | varchar(30)       |      | PRI |         |       |
| pass   | varchar(20)       |      |     |         |       |
| access | int(10) unsigned  |      |     | 0       |       |
+--------+-------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

The tags table

The tags table has the same layout as the users table. We choose to make the field for the tag shorter than that for the nicknames since we expect a tag regex to use less space. The tags table is used to hold clan tags in addition to user nicknames in the users table. As with all tables, you don't have to create this table if you have no need for it. The reason for the tags table is that clan tags are usually matched against with a regular expression. The MySQL REGEX function is very slow. If the users table was matched with REGEX that would take a long time if the users table was large. Since Admin Mod is also used in large envirounments with table sizes of over 10,000 entries, the tags table has been seperated from the users table. The columns correspond to the three columns in the users table. The tag column holds a clan tag or a regex to match against, pass is the password required, and access the access level attached to it.

mysql> CREATE TABLE tags( tag VARCHAR(30) PRIMARY KEY NOT NULL,
   --> pass VARCHAR(20) NOT NULL, access INTEGER UNSIGNED NOT NULL );
Query OK, 0 rows affected (0.00 sec)
 



mysql> DESCRIBE tags;



+--------+-------------------+------+-----+---------+-------+
| Field  | Type              | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| tag    | varchar(30)       |      | PRI |         |       |
| pass   | varchar(20)       |      |     |         |       |
| access | int(10) unsigned  |      |     | 0       |       |
+--------+-------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

The models table

The models table has two columns: nick and pass. These two columns correspond to the fields in an entry of the file specified by the models_file cvar. nick is the name of the model and pass is the assigned password. We choose to call it models. You can choose a different name.

mysql> CREATE TABLE models( nick VARCHAR(20) PRIMARY KEY NOT NULL,
   --> pass VARCHAR(20) NOT NULL );



mysql> DESCRIBE models;



+--------+-------------------+------+-----+---------+-------+
| Field  | Type              | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| nick   | varchar(20)       |      | PRI |         |       |
| pass   | varchar(20)       |      |     |         |       |
+--------+-------------------+------+-----+---------+-------+
2 rows in set (0.00 sec) 

The ips table

The table to hold the IPs corresponds to the file specified by the ips_file cvar and has only one column called ip. We choose to name the table ips. You can give it a different name.

mysql> CREATE TABLE ips( ip VARCHAR(15) NOT NULL );



mysql> DESCRIBE ips;



+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ip    | varchar(15) |      |     |         |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

The words table

The words table holds the words to be filtered from player chat. It corresponds to the file specified by the words_file cvar and has only one column called word. We choose to name the table words. You can give it another name.

mysql> CREATE TABLE words ( word VARCHAR(30) NOT NULL );



mysql> DESCRIBE words;



+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| word    | varchar(30) |      |     |         |       |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

The plugins table

This table lists the plugins to be used on the server. It has only one column called plugin. It corresponds to the file specified by the admin_plugins_file cvar. We choose to call it plugins. You can give it another name.

mysql> CREATE TABLE plugins( plugin VARCHAR(60) NOT NULL );



mysql> DESCRIBE plugins;



+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| plugin   | varchar(60) |      |     |         |       |
+----------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

Admin Mod variables setup

To use Admin Mod with the database that you just set up you have to tell it how to connect to the MySQL server and what database and tables to use. Admin Mod provides you with a set of cvars to do so. You set these cvars in the server.cfg file just like all the other cvars.

Connecting to the MySQL server

To connect to the server you need to specify the address of the server, the user to access it as and the password to use.

• mysql_host <string>

  This variable specifies the address of the machine that the MySQL server is running on.
  Examples:

  	mysql_host "192.168.2.1"
  	mysql_host "localhost"
  	mysql_host "some.machine.net"

• mysql_user <string>

  This variable specifies the username under which Admin Mod should connect to the MySQL server.
  Examples:

  	mysql_user "adminmod"
  	mysql_user "someclient"

• mysql_pass <string>

  This variable specifies the password of the user to connect to the MySQL server.
  Examples:

  mysql_pass "somepass"

Retrieving data from tables

The following variables are used to specify which database and which table to use. The format is "database.table". The list should be self-explanatory. The examples use the database and table names which were used in the examples above.

• mysql_dbtable_users <string>

  The table for admin users
  Examples:

  mysql_dbtable_users "adminmod.users"

• mysql_dbtable_models <string>

  The table for models
  Example:

  mysql_dbtable_models "adminmod.models"

• mysql_dbtable_ips <string>>

  The table for IPs
  Example:

  mysql_dbtable_ips "adminmod.ips"

• mysql_dbtable_words <string>

  The table for censored words
  Example:

  mysql_dbtable_words "adminmod.word"

• mysql_dbtable_plugins <string>

  The table for plugins
  Example:

  mysql_dbtable_plugins "adminmod.plugins"

You don't have to use all available tables. As an example you can choose to only use the users and plugins tables from the database. The reserved models will be read from a local file and you don't want to use the IPs. That would result in the following setup:

It is a good idea to have users.ini and plugins.ini files, too. That way Admin Mod can fall back to those files if it is unable to read from the MySQL tables.

• mysql_database <string>

  The database. If you have all the tables you use stored in one database, you can also set the following variable. Now this database will be used for every query that Admin Mod makes. You then do not need to specify the database in the table cvars anymore.
  Example:

  	mysql_database "adminmod"
  	mysql_dbtable_users "users"
  	mysql_dbtable_plugins "plugins"

Other MySQl Cvars

The following variables are used to give a little more flexibility when using Admin mod with MySQL.

• mysql_preload <1|0>

  This variable controls whether Admin Mod will load each table into memory on startup. Loading the table into memory removes the need to query the database during the round. A significant number of queries are made per second so loading the table into memory may help with the performance of your server. The tradeoff is the memory required to load a complete table into memory. If you have more than 1,000 users defined in your users table then consider disabling this option. This Cvars defaults to 1.

   

  Example:
  

  mysql_preload "1"





• mysql_users_sql <string>

  This variable allows you to customise the SQL command Admin Mod uses to retrieve a users password and access value. The default statement will work with the setup instructions described in this document. This variable can be altered to allow you to get Admin Mod passwords and access levels from an existing database, such as a web based forum, which may have different column names than the default ones mentioned above.

   

  Note: This cvar will only be used if mysql_preload is set to 0

   

  Valid values:
  any valid SQL query that returns a password in the first column and an access number (in integer format) in the second column. It must also contain 3 "%s" symbols that are filled in by Admin Mod by (in this order) the mysql_dbtable_users value, the name of the user and the authentication id (i.e. WONID or SteamID) of the player.

   

  Example:

  mysql_users_sql "SELECT password,access FROM %s where username='%s' or username='%s'"





• mysql_tags_sql <string>

  This query is identical to the mysql_users_sql query except it is used to match user tags rather than complete user names. A tag is a substring contained within a username, such as the clan tags used by many people. This query uses the "REGEX" functionality of MySQL which means the "nick" column of this database must be valid REGEX expressions. This query is seperate to the mysql_users_sql query as the "REGEX" function of MySQL is extremely slow, so the size of this table should be extremely limited to maintain server performance (i.e keep the number of rows below 100 or so). First exact username matches are attempted using the "mysql_users_sql" query, if that doesn't return any results the "mysql_tags_sql" query is run in an attempt to get the password and access level for any tags contained within the username string. This function allows you to grant access to certain commands to people in a clan, or to stop people using a particular tag without the correct password.

   

  Valid values:
  as for mysql_users_sql variable.

   

  Example:

  mysql_tags_sql "SELECT password,access FROM %s where tag REGEXP '%s' or tag='%s'"