DBIStoreContrib database schemas

The following schemas are designed for use with different database implementations. The default schema shipped with the DBIStoreContrib is designed for use with SQLite3 and PostgreSQL, using the non-ANSI TEXT type to store character data. While this will work with many database implementations, it will not work with all. Further, several plugins create meta-data that is not present in the base schemas. Which the DBIStoreContrib can be configured to automatically reconfigure the database for these missing columns, it is not very efficient to do so and it's better to pre-declare the schemas for these extra tables.

Base Schemas

Note that the field serialised is reserved in the FILEATTACHMENT table for future use. However it should not be added to the schema yet!

SQLite3

Reference: https://www.sqlite.org/datatype3.html

{
    _DEFAULT  => { type => 'TEXT' }, # Arbitrary unicode
    _WEE8     => { type => 'TEXT' }, # Up to 255 8-bit characters
    _WEEU     => { type => 'TEXT' }, # Up to 255 unicode characters
    _MED8     => { type => 'TEXT' }, # Up to 165535 8-bit characters
    _MEDU     => { type => 'TEXT' }, # Up to 165535 unicode characters
    _BIGU     => { type => 'TEXT' }, # As big as you can go unicode
    _WEB      => { type => 'TEXT' }, # Unicode web path
    _TOPIC    => { type => 'TEXT' }, # Unicode topic name
    _WEBTOPIC => { type => 'TEXT' }, # _WEB + _TOPIC
    _DATE     => { type => 'TEXT' }, # 8-bit date
    _USER     => { type => 'TEXT' }, # Unicode user
    _ATTNAME  => { type => 'TEXT' }, # Unicode attachment name
    _ATTPATH  => { type => 'TEXT' }, # Unicode attachment path
    
    topic => {
        tid  => { type => 'INT', primary => 1 },
        web  => { basetype => '_WEB', index => 1, unique => 'webtopic' },
        name => { basetype => '_TOPIC', index => 1, unique => 'webtopic' },
        text => '_BIGU',
        raw  => '_BIGU'
    },
    metatypes => {
        name => { basetype => '_WEE8', primary => 1 },
    },
    TOPICINFO => {
        tid  => { type => 'INT', unique => 'onetopicinfo' },
        author => { basetype => '_USER', index => 1 },
        version => '_WEE8',
        date => '_DATE',
        format => '_WEE8',
        reprev => '_WEE8',
        rev => '_WEE8',
        comment => '_MEDU'
    },
    TOPICMOVED => {
        tid  => { type => 'INT' },
        from => '_WEBTOPIC',
        to => '_WEBTOPIC',
        by => '_USER',
        date => '_DATE',
    },
    TOPICPARENT => {
        tid  => { type => 'INT', unique => 'oneparent' },
        name => { basetype => '_WEBTOPIC', index => 1 }
    },
    FILEATTACHMENT => {
        tid  => { type => 'INT', unique => 'onename' },
        name => { basetype => '_ATTNAME', index => 1, unique => 'onename' },
        version => '_WEE8',
        path => { basetype => '_ATTPATH' },
        size => '_WEE8',
        date => '_DATE',
        user => { basetype => '_USER', index => 1 },
        comment => '_MEDU',
        attr => '_WEE8'
    },
    FORM => {
        tid  => { type => 'INT' },
        name => { basetype => '_WEEU', index => 1 },
    },
    FIELD => {
        tid  => { type => 'INT' },
        name => { basetype => '_WEEU', index => 1 },
        value => '_BIGU',
        title => '_WEEU'
    },
    PREFERENCE => {
        tid  => { type => 'INT', unique => 'onepref' },
        name => { basetype => '_WEEU', index => 1, unique => 'onepref' },
        value => '_BIGU',
        type => '_WEE8',
    }
}

PostgreSQL

The database should be created to use the UTF8 character set. The default schema (as described for SQLite3) should work well with PostgreSQL.

Reference: https://www.postgresql.org/docs/9.1/static/datatype-character.html

MySQL

MySQL supports several different types of TEXT, so care must be taken to select the one that works best for each field.

    _DEFAULT  => { type => 'LONGTEXT' }, # Arbitrary unicode
    _WEE8     => { type => 'TINYTEXT' }, # Up to 255 8-bit characters
    _WEEU     => { type => 'TINYTEXT' }, # Up to 255 unicode characters
    _MED8     => { type => 'MEDIUMTEXT' }, # Up to 165535 8-bit characters
    _MEDU     => { type => 'MEDIUMTEXT' }, # Up to 165535 unicode characters
    _BIGU     => { type => 'LONGTEXT' }, # As big as you can go unicode
    _WEB      => { type => 'MEDIUMTEXT' }, # Unicode web path
    _TOPIC    => { type => 'TINYTEXT' }, # Unicode topic name
    _WEBTOPIC => { type => 'MEDIUMTEXT' }, # _WEB + _TOPIC
    _DATE     => { type => 'TINYTEXT' }, # 8-bit date
    _USER     => { type => 'TINYTEXT' }, # Unicode user
    _ATTNAME  => { type => 'TINYTEXT' }, # Unicode attachment name
    _ATTPATH  => { type => 'MEDIUMTEXT' }, # Unicode attachment path

SQL Server

Perhaps some expert can advise if VARCHAR(N) can be replaced with VARCHAR(MAX) to avoid the risk of overflow/truncation?

    _DEFAULT  => { type => 'NVARCHAR(MAX)' }, # Arbitrary unicode
    _WEE8     => { type => 'VARCHAR' }, # Up to 255 8-bit characters
    _WEEU     => { type => 'NVARCHAR' }, # Up to 255 unicode characters
    _MED8     => { type => 'VARCHAR' }, # Up to 165535 8-bit characters
    _MEDU     => { type => 'NVARCHAR' }, # Up to 165535 unicode characters
    _BIGU     => { type => 'NVARCHAR(MAX)' }, # As big as you can go unicode
    _WEB      => { type => 'NVARCHAR' }, # Unicode web path
    _TOPIC    => { type => 'NVARCHAR' }, # Unicode topic name
    _WEBTOPIC => { type => 'NVARCHAR' }, # _WEB + _TOPIC
    _DATE     => { type => 'VARCHAR' }, # 8-bit date
    _USER     => { type => 'NVARCHAR' }, # Unicode user
    _ATTNAME  => { type => 'NVARCHAR' }, # Unicode attachment name
    _ATTPATH  => { type => 'NVARCHAR' }, # Unicode attachment path

Schema Extensions

WorkflowPlugin

Because of the way this plugin abuses META, you have to run with {AutoAddUnknownFields} enabled, even if the schema is extended as described.

 WORKFLOW => {
    name => '_TOPIC'
 },
 WORKFLOWHISTORY => {
   name => '_TOPIC',
   value => '_DEFAULT',
   date => '_DATE'
 }

MetaCommentPlugin

COMMENT => {
  name => '_DEFAULT',
  author => '_USERNAME',
  date => '_DATE',
  fingerPrint => '_DEFAULT',
  modified => '_DATE',
  read => '_USERNAME',
  ref => '_DEFAULT',
  state => '_DEFAULT',
  text => '_DEFAULT',
  title => '_DEFAULT'
}