DBIPlugin Perl API

internal package Foswiki::DBI

Interface for Foswiki DBI developers

StaticMethod getDB() → $database

Creates a database connection to the configured implementation, connects to it and loads the base schema.

StaticMethod loadSchema($schemaBase) → $database

Loads a database schema and returns the db. The $schemaBase is the base perl class of the caller. The real database schema being loaded resides in a sub class of the $schemaBase according to the database implementation of the system.

For example a MyPlugin must provide the following classes to support SQLite and MariaDB:

Foswiki::Plugins::MyPlugin::Schema
Foswiki::Plugins::MyPlugin::Schema::MariaDB
Foswiki::Plugins::MyPlugin::Schema::SQLite

The schema is then called using:

my $db = Foswiki::DBI::loadSchema("Foswiki::Plugins::MyPlugin::Schema");

Given MariaDB is the current database implementation, it actually loads the schema Foswiki::Plugins::MyPlugin::Schema::MariaDB and returns a singleton database object of type Foswiki::DBI::Database::MariaDB. This singleton object is shared among all subsystems connecting to the database.

StaticMethod finish()

Close any database connection being made during the session

internal package Foswiki::DBI::Database

abstract class for any type of database connecting to foswiki

ClassMethod new()

Constructs a Foswiki::DBI::Database object. This class is mostly subclassed by the acutal database implementation being configured, such as Foswiki::DBI::Database::MariaDB. Subclasses need to specify the actual DBD driver to connect to the database.

ObjectMethod getClassName() → $string

Returns the base name of this database representing the current implementation, such as MariaDB

ObjectMethod applySchema($schema)

Applies the Schema to the connected database. this is called only once when the database is connected. note that the schema must test for existing tables and indexes on its own.

ObjectMethod schemaVersion($type, $version) → $version

getter/setter for the schema version meta data

ObjectMethod handler() → $dbh

Returns the DBD handler that this class is delegating all work to

ObjectMethod connect()

Connects to the database if not already done so and returns a DBI::db handler. this method is called automatically when the db handler is established

ObjectMethod finish()

Called solely by Foswikik::DBI::finish() to finalize the database connection and close any open sockets.

ObjectMethod eachRow($tableName, %params) → $iterator

Returns an object of class Foswiki::Iterator::DBIterator for the given parameters. This is a convenience wrapper for

my $it = Foswiki::Iterator::DBIterator->new($dbh, $stm);

The statement handler is created based on the parameters provided. The %params parameter is a hash with the following values:

• columns: list of columns to return, defaults to "*"
• avg: column to compute an average for
• sum: column to compute the sum for
• having: "HAVING" clause SQL statement
• groupBy: groupBy "GROUP BY" clause
• sort, orderBy: "SORT" clause
• filter: "WHERE" clause
• groupConcat: "GROUP_CONCAT(DISTINCT ...)" clause
• count: if defined adds a "COUNT(*) clause, if count is prefixed with "unique" will add a "COUNT(DISTINCT ...)"
• <colName>: <colVal> ... will add a "colName1='colVal1'" to the "WHERE" clause

Note that all parameters except $tableName are optional.

Example:

my $it = Foswiki::DBI::getDB->eachRow("SomeTable", 
  count => "*"
  firstName => "Michael"
);

while ($it->hasNext) {
  my $row = $it->next();

  my $firstName = $row->{firstName};
  my $middleName = $row->{middleName};
  my $lastName = $row->{lastName};
  my $count = $row->{count};

  ...
}

internal package Foswiki::DBI::Schema

A schema is used to define tables and indexes in an SQL database. Plugins may define a schema for their own needs by subclassing this class. In general a plugin must define a schema class for each database vendor it desires to support, such as SQLite, MariaDB, Oracle etc.

Two functions need to be implemented: getType() and getDefinition() The schema base is then passed on to Foswiki::DBI::loadSchema() See Foswiki::DBI for more information.

ClassMethod new()

Constructs an object of this type.

ObjectMethod getType() → $string

Returns a string representing the type of this schema. For example Foswiki::Plugins::LikePlugin::Schema::getType() returns the string "LikePlugin". This string may be used in the schema definition using the "%prefix%" placeholder.

ObjectMethod getDefinition() → $array

Returns an array of arrays of SQL statements to define the schema. Each SQL statement may contain the %prefix% placeholder being replaced by the value of getType()

For example, the $array returned by the subclass may look like this:

sub getDefinition {
  return [[
      'CREATE TABLE IF NOT EXISTS %prefix%likes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        web VARCHAR(255),
        topic VARCHAR(255),
        meta_type CHAR(20), 
        meta_id VARCHAR(255),
        username VARCHAR(255),
        like_count INTEGER DEFAULT 0,
        dislike_count INTEGER DEFAULT 0,
        timestamp INTEGER
      )',

      'CREATE UNIQUE INDEX IF NOT EXISTS %prefix%_idx_likes on %prefix%likes (web, topic, username, meta_type, meta_id)'
  ], [
    "ALTER TABLE %prefix%likes ..."
  ]];
}

In a first version of the schema definition, it create a table LikePlugin_likes and an index LikePlugin_idx_likes. Later on during the life span of the LikePlugin a modification to the initial definition is required. That's why there is a second element with an "ALTER TABLE" clause to update any preexisting SQL structure incrementally. This approach migrates a table structure seamlessly as required. The required updates are tracked by the schema loader of DBIPlugin. The version of the schema is being tracked in a separate table db_meta. In the above example an entry will be added to the db_meta table for the "LikePlugin" schema being of version 2 (as there are two elements in the returned $array.

internal package Foswiki::Iterator::DBIterator is a Foswiki::Iterator

ClassMethod new($dbh, $select, $values, $process)

Constructs a Foswiki::DBI::DBIterator object. Parameters are:

• $dbh: the database being used to connect to the actual database (mandatory)
• $select: SQL select clause that is being prepared using the $dbh (mandatory)
• $values: array reference of values being used when executing the statement; this must match the "?" placeholders in the select clause (optional)
• $process: function reference that is called when iterator fetched the next value from the database, see next() below (optional)

A DBIterator may be used in its own but is mostly created as part of Foswiki::DBI::Database::eachRow().

Example use:

my $it = Foswiki::Iterator::DBIterator($dbh, "select * from ... where ...");

while ($it->hasNext) {
  my $row = $it->next();

  my $firstName = $row->{firstName};
  my $middleName = $row->{middleName};
  my $lastName = $row->{lastName};

  ...
}

ObjectMethod hasNext() → $boolean

returns true if the iterator still has values to be returened by next().

ObjectMethod next() → $row

returns the next row available in the result set of the select statement. The $row return value is a hash reference as being created by DBI::fetchrow_hashref

ObjectMethod count() → $integer

returns the number of rows affected by this iterator

ObjectMethod reset()

resets the iterator to restart the search to the beginning. note that the select statement (provided to the constructor) will be prepared and executed once again