This is an old revision of the document!
<markdown> #SQLite Database Specifications
MajorBBS & WorldGroup modules make heavy use of Btrieve databases.
Before continuing, please read information about the [Btrieve database format located here.](https://github.com/enusbaum/MBBSEmu/wiki/Btrieve-File-Format])
# 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](https://www.sqlite.org/index.html) 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](https://github.com/enusbaum/MBBSEmu/wiki/Btrieve-File-Format#key-attributes) The `data_type` field is analogous to the Btrieve data type. [See here](https://github.com/enusbaum/MBBSEmu/wiki/Btrieve-File-Format#key-data-types) 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:
`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 </markdow n>