SQL Module

From Botnix
Jump to: navigation, search

Overview

The sql "service provider" module provides other modules with an interface to a (hopefully) arbitrary database backend using Perl's DBI database abstraction modules.

Connection objects are shared, so if you have ten modules all using the same database then the sql module will give them all the same db object, for this reason modules should never call disconnect() on the object they are given.

It also handles database configuration blocks in the config file, you may have as many database blocks as you wish in your config file.

Backends tested so far (tested with the Seen Module and with the Infobot Module module)

  • DBD::MySQL
  • DBD::SQLite

DBD is an abbreviation for Database Driver


Windows user can install the needed Database Drivers by typing in the console:

ppm
install dbi
install dbd-mysql
install dbd-sqlite

Configuration

The format of the database configuration blocks is as follows:

db "Database Title" {
	engine "mysql"
	host "localhost"
	name "database"
	user "username"
	pass "password"
}
  • engine - The DBI driver to use, this must be installed on your system, defaults to 'mysql' if not defined.
  • host - The database's hostname, most likely localhost, defaults to an empty string
  • name - The name of the database to be selected, defaults to an empty string
  • user - The user to connect to the database as, defaults to an empty string
  • pass - The password to use to connect, defaults to an empty string

The first block in the config file is the default block (for more details of when this is used see below)

Developers

Perl code

To use the sql provider module your module simply needs to use the following code to get a DBI object you can execute queries with:

my $mydbobject = modules::db::sql::db "Blockname";

The parameter may be ommited if you want to use the default database.

You should also check that the SQL module is loaded before you try to use it. If you check you can print an error and stop the module being loaded but allow the bot to start normally, but if you just assume it's loaded then the entire bot will die on startup with an error like:

Undefined subroutine &modules::db::sql::db called at modules/irc/seen.pm line 47.

You can check the SQL module is loaded in your module's init() function, here's an example of how you can check:

($_ eq 'modules::db::sql') and return foreach (@main::modules);
main::lprint $self . ": Unable to obtain db object, do you have modules/db/sql.pm present and loaded?";
die;

die()ing inside your module's init() function causes the module not to be loaded.

Important: You CANNOT obtain a database object inside your module's init() function, at this point the configuration has not been parsed by the sql module and it is unable to give you a valid object. Use after_configure() instead as the earliest point at which you can obtain a DBI object.

A mistake which was present in the earlier versions of the seen module is code like this:

my $query = $db->prepare("SELECT * FROM...");

$query->execute();

if($query->rows)
{
   # Use result
   my $row = $query->fetchrow_hashref();
}
else
{
   # No results
}

$query->finish();

This seems to work fine with MySQL, but fails with SQLite.

A example of code that works with both MySQL and SQLite:

my $query = $db->prepare("SELECT * FROM seen...");
	
$query->execute();

if(my $row = $query->fetchrow_hashref())
{
   # Use $row
}
else
{
   # No results
}

SQL

In order to make modules using this module be as portable to as many other DBI backends as possible then you should try to use standard SQL

Some common mistakes

  • Do not use backticks (`) around column/table names, this is a MySQL-only feature and is not portable
  • Do not use english words for column/table names, many such words are reserved by database engines

Links

DBI MySQL Documentation

DBI SQLite Documentation

Latest versions of all my modules (including the SQL module)

Author

The SQL module is written and maintained by Om