SQLite Database Specifications

MajorBBS & WorldGroup modules make heavy use of Btrieve databases.

Before continuing, please read information about the Btrieve database format located here.

Conversion

When a module attempts to load a Btrieve database for the first time, MBBSEmu loads the associated database .DAT file. It then reads in the entire database and converts it to a SQLite database with the same name but with a .DB extension.

The database is created with three tables that describe all the data within it

metadata_t
keys_t
data_t

The 'metadata_t' table

This table contains metadata information about the file, analogous to the file header in the Btrieve .DAT file. The schema is as follows

CREATE TABLE metadata_t(record_length INTEGER NOT NULL, physical_record_length INTEGER NOT NULL, page_length INTEGER NOT NULL, variable_length_records INTEGER NOT NULL, version INTEGER NOT NULL, acs_name STRING, acs BLOB)

The record_length field is the length of all the records stored in the database in the data column in the data_t table. For variable length records, this is the length of the fixed portion, so every record will be at least this large and of course could be much larger.

The physical_record_length is the length of each fixed physical record in the Btrieve file, and stored for informational purposes and not used by MBBSEmu.

The page_length is the length of each page in the Btrieve file, and likewise stored only for information purposes and not used by MBBSEmu.

variable_length_records indicates whether the records in this database are of variable length. Each record has a minimum length of record_length.

version specifies the schema version of this database. It is used for schema upgrades, for example if MBBSEmu has an update that changes the schema number and adds/removes database fields, MBBSEmu can examine the version field and automatically upgrade the database if necessary.

acs_name and acs are related fields and contain information about the Alternate Collating Sequence. ACS is used to alter string keys in the database. The acs table is a byte array of size 256 which performs a simple string mapping for keys. ACS tables are often used for implementing case-insensitive string keys - for example the ACS table would map lower case characters to upper case and the engine treats the key as all upper case.

acs_name and acs are both null if ACS tables are not used for the database. Otherwise acs_name is used for informational purposes only, and acs is a blob of length 256.

There is only one row in this table which contains the metadata for the entire database.

The 'keys_t' table

This table contains information about all the keys in the database. Each Btrieve database defines at least one key and these are all stored in keys_t

The schema is as follows:

CREATE TABLE keys_t(id INTEGER PRIMARY KEY, number INTEGER NOT NULL, segment INTEGER NOT NULL, attributes INTEGER NOT NULL, data_type INTEGER NOT NULL, offset INTEGER NOT NULL, length INTEGER NOT NULL, null_value INTEGER NOT NULL, UNIQUE(number, segment));

The number field is the key number. Keys are numbered 0 -> N.

The segment field is the segment of the key, numbered 0 -> N. A key may contain multiple segments and these are known as composite keys. Composite keys would have the same number field but incrementing segment fields.

The attributes field is analogous to the Btrieve attributes. See here

The data_type field is analogous to the Btrieve data type. See here

The offset field is the 0-based offset where the key data appears in the record.

The length field is the length of the key data in bytes.

The null_value field is the null value of the key, if the attributes specifies that the field is nullable.

There could be multiple rows in this table depending on how many keys are in the original Btrieve database.

The 'data_t' table

This table contains all the data/records in the database, one row for each record.

The schema is dynamic based on the key, but always contains at least the following:

The id field is the unique identifier of the data, corresponding to the physical offset in the original Btrieve database. Physical offset aren't imported so the id values you will see are typically autoincremented starting with 1. They just need to be unique from one another.

The data field is the actual data for the record. Btrieve stores records as a blob of bytes and this is it. The key values are embedded inside this data field as well.

Each key_<number> field corresponds to each unique key number in keys_t - one for each key. Depending on the key attributes, it could be NOT NULL or UNIQUE. This key value contains the raw key data to be used during queries. This data will match the data in the data field, and duplicates the data, but by having the data as a separate column in the database, we can query/sort on it, which is a key feature used by modules.

Caveats

SQLite allows multiple processes to access a database at the same time, but MBBSEmu expects exclusive access. If you try to edit a SQLite database while MBBSEmu is running, MBBSEmu may crash and/or the modules may crash. To ensure safety, completely shutdown MBBSEmu before messing with the databases.

Interoperability

The databases created are standard sqlite databases, and as such can be modified by other programs. You can back them up or edit them as you see fit. The sqlite3 program can open them up and inspect/edit them.

Say you want to edit your character in MajorMUD. You can do that! Try this sample program which gives 1 copper, 1 silver, 1 gold and 1 platinum on MMUD 1.11g-WG

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('WCCUSERS.DB')

c = conn.cursor()
t = ('Paladine',)
c.execute('SELECT data FROM data_t WHERE key_0=?', t)

data = c.fetchone()

print data[0]

b = bytearray(data[0])
b[0x60C] = 0;
b[0x60D] = 0;
b[0x60E] = 0;
b[0x60F] = 0x1; # copper

b[0x608] = 0;
b[0x609] = 0;
b[0x60A] = 0;
b[0x60B] = 0x1; # silver

b[0x607] = 0x1; # gold

b[0x603] = 0x1; # platinum

# and write it back
t = (buffer(b), 'Paladine',)
c = conn.cursor()
c.execute('UPDATE data_t SET data=? WHERE key_0=?', t)
conn.commit()

This program dumps out your user data, so you can pipe it to a file, and then edits your character and saves it. You'll have to execute the program when MBBSEmu is offline

The MajorBBS Emulation Project Wiki

Table of Contents