LizardBot/MySQL

Note: Some experience in the PHP programming language - which this bot is written in - is recommended for using this bot. The documentation on this site assumes that you are familiar with at least the basics of PHP (such as declaring variables, how to quote strings, etc.). That said, this is only a recommendation - if you don't know PHP at all, you are welcome to try out LizardBot, and do ask for help on our IRC channel or elsewhere if you need it.

As of LizardBot version 7.0.0.0 beta (SVN revision 64), LizardBot is capable of using a MySQL database to store certain data. It allows the host of the database to be configured, allowing for an off-site server, as well as other parameters. This guide will explain how to set up the MySQL database used by LizardBot, configure LizardBot to use MySQL, and other aspects of care and feeding of a MySQL-enabled LizardBot.

Note that at this time, MySQL is the only database system that LizardBot supports.

Setting up MySQL

The first thing you need to do is create a user for LizardBot to access your MySQL server as (or, at your option, you can use an already existing user, but this is not recommended). Use the CREATE USER command to create a new user on your server, and make sure you give it a strong password. Next, grant ALL privileges to the user you create for the database you plan to use for LizardBot. Yes, all privileges; it makes database updates easier. Note the username and password you use. Also, don't worry about actually creating a database for LizardBot if you plan to use one that doesn't exist yet - we'll handle that later with a script.

Configuring LizardBot

Next you need to do is configure LizardBot to use MySQL. This is pretty basic, and takes place in the normal config file.

• $setEnableMySQL: Boolean. This variable controls whether or not MySQL support is enabled, and, by extension, controls whether or not all MySQL-needing commands are enabled or disabled. False by default, but you need to set this to true to enable MySQL support.
• $setMySQLUserName: String. This is where you put the username you want LizardBot to use to connect to the MySQL server. This is a required setting.
• $setMySQLPassword: String. This is where you put the password for the user you want LizardBot to use to connect to the MySQL server. This is a required setting.

  WARNING: THE PASSWORD YOU ENTER HERE WILL BE STORED IN CLEAR TEXT! IT WILL BE VISIBLE TO THE ENTIRE WORLD UNLESS YOU TAKE STEPS TO PREVENT OTHERS FROM SEEING IT! ON UNIX-BASED MACHINES, USE THE COMMAND chmod 600 lizardbot.conf.php (replace lizardbot.conf.php with the name of your config file as necessary) IN THE TERMINAL TO PREVENT OTHERS FROM READING IT. IF YOU RUN WINDOWS, MAKE SURE A PASSWORD IS SET WITH YOUR USER ACCOUNT AND USE WINDOWS EXPLORER TO MARK THE CONFIGURATION FILE AS 'PRIVATE'.

• $setMySQLHost: String. This is where you enter the address of the MySQL server. For most people, it will be the default, localhost. But, if you're using a server that's not local, this is where you say where it is. This is a required setting.
• $setMySQLPort: Integer. This is where you enter the port that the MySQL server listens on. For almost everyone, it will be the default, 3306. But, if for whatever reason you're using a MySQL server that listens on a non-standard port, this is where you specify that port. This isn't a required setting, since LizardBot will default to port 3306, but it will throw a warning if you don't specify this setting.
• $setMySQLDB: String. This is the database you (plan to) use for LizardBot. It can be preexisting, or nonexistent (it will be created later if this is the case). This is a required setting.
• $setMySQLTablePre: String. This is the prefix you want LizardBot to use in its table names. This allows you to run multiple instances of LizardBot that use the same database without them conflicting with each other. The default setting is to use the prefix 'lb' (the _ between the prefix and the table name is automatically added), although you can leave this blank and LizardBot will work with no table prefix. It will complain about this, however. It is also strongly not recommended that you run multiple LizardBots off the same database and same tables (i.e., they both use the same database and have the same table prefix setting).

Creating the Tables

Now that LizardBot is configured, you can now go ahead and create the tables LizardBot needs. In a command prompt or terminal window, navigate to where you put LizardBot. Run the command php mkdb.php. The script will automatically create the database you intend to use for LizardBot (unless it already exists), then creates the tables that LizardBot uses. It automatically uses the MySQL settings in your LizardBot configuration file. Note that, if you want it to use a different configuration file (i.e., different from the default of lizardbot.conf.php), you can specify a custom configuration file as the first argument after the command - e.g., php mkdb.php my.custom.config.file.php. The script will also let you know if there are any errors in the MySQL connection information you configured the bot with.

A note about updates

If the LizardBot table layout is ever updated, you will only have to re-run mkdb.php to update all existing table layouts and create any new required tables. This will be noted in the change log when it occurs.

You're done!

If the mkdb.php script executes successfully, LizardBot is now ready to be used with MySQL! Aside from any new commands that enabling MySQL enables, the bot will otherwise work exactly the same as it did before MySQL was enabled.

Caveats

Since LizardBot version 7.1.0.0b (SVN r69), it is possible to start LizardBot with MySQL explicitly disabled in the config file and to then enable it while the bot is running without crashing the bot. However, you should be aware that, if this is done, LizardBot will not verify that the MySQL configuration settings in its configuration file are correct. Normally, when you start the bot with MySQL support enabled, it establishes a test connection, just to make sure it works before continuing to start up. However, having MySQL disabled at bot startup then enabling it later bypasses this check, so if you aren't sure that your connection information is right, you definitely should not do this, as it may cause the bot to crash anyway - or at least the MySQL-based commands to fail.

Database Layout

Following is the current layout of the tables LizardBot uses.

mysql> USE `lizardbot`;
Database changed

mysql> SHOW TABLES;
+---------------------+
| Tables_in_lizardbot |
+---------------------+
| reminders           |
+---------------------+
1 row in set (0.00 sec)

mysql> DESC `reminders`;
+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| reminder_id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| reminder_text        | varchar(255)     | NO   |     | NULL    |                |
| reminder_time        | datetime         | NO   |     | NULL    |                |
| reminder_requester   | varchar(255)     | NO   |     | NULL    |                |
| reminder_target_nick | varchar(64)      | NO   |     | NULL    |                |
+----------------------+------------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

Remember that database names and table names may be different depending on your LizardBot configuration. Generally, your tables will be prefixed with lb_ (e.g., reminders becomes lb_reminders).