This is an old revision of the document!
Table of Contents
SQLite Database Sepcifications
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 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);
The record_length field is the length of all the records stored in the database, in the data column in the data_t table.
The physical_record_length is the length of each 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.
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 table
This table contains all the data/records in the database.
The schema is dynamic based on the key, but always contains at least the following:
CREATE TABLE data_t(id INTEGER PRIMARY KEY, data BLOB NOT NULL, key_0 <type> |NOT NULL| |UNIQUE|, ..., key_n <type> |NOT NULL| |UNIQUE|);
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