internal package Foswiki::DBI::Schema

See PublishedAPI for packages intended to be used by Plugin and Contrib authors, or browse all packages.
See also Developing plugins, Developer's Bible, Technical Overview

Perl Module:

Child packagesChild packages

• Foswiki::DBI::Schema::Generic
• Foswiki::DBI::Schema::MariaDB
• Foswiki::DBI::Schema::MySQL
• Foswiki::DBI::Schema::PostgreSQL
• Foswiki::DBI::Schema::SQLite

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.