Database¶

TDatabase is used to create a extremely basic single table database.

Despite being basic it’s very effective and fast and you can use this when you have a large set of data and need to retrieve values very fast.

To make this possible TDatabase creates several maps between the data and their respective row placement, so to use this you need to have your data prepared, split into database columns saved in different files and each “row” should be in orderder between the files.

Credits: Torwent

Your data has to be split into columns, each saved to its own file, with every row on the same line of each file.

Let’s use for example a little bit of what is used for the items database:

+-------------------+-------+------------+
| items             | id    | hash       |
+-------------------+-------+------------+
| dwarf remains     | 0     | 069702cc6  |
| toolkit           | 1     | ff026bd51  |
| cannonball        | 2     | 0224ca70f  |
| nulodion's notes  | 3     | 047160bd7  |
| ammo mould        | 4     | f986eb373  |
| ...               | ...   | ...        |
| abyssal whip      | 4151  | 02f197ebf  |
+-------------------+-------+------------+

The data in this example should be saved to 3 files with the column name (item.txt, id.txt, hash.txt), and line N of each file belongs to row N.

TDatabase keeps every column in that original row order, so once you know a row number, reading any column at that row is a single array access.

To find the row of a value, each column you search gets an index: a map from every distinct value in the column to the first row holding it, and for every row, the next row holding the same value (or -1). Using the table above:

+-------------------+-----------+       +-------+-----------+
| item index        | first row |       | row   | next row  |
+-------------------+-----------+       +-------+-----------+
| abyssal whip      | 3551      |       | 0     | -1        |
| ammo mould        | 4         |       | 1     | -1        |
| cannonball        | 2         |       | ...   | ...       |
| ...               | ...       |       | 3551  | -1        |
+-------------------+-----------+       +-------+-----------+

So to get the abyssal whip id:

Look abyssal whip up in the “item” index, which gives row 3551.
Read the “id” column at row 3551, which is 4151.

Finding every row with a value just follows “next row” from the first one until it reaches -1, so rows always come back in ascending order.

An index is only built the first time a column is searched, so columns you only read values from never pay for one.

TDatabase.Setup¶

procedure TDatabase.Setup(name: String; columns: TStringArray; path, version: String);

Sets up a new TDatabase, loading one <column>.txt file per column from path.

If rows were saved with Database.Insert, the column files in the cache directory for this version are loaded instead. Cache directories of other versions are deleted.

Every column must have the same number of rows.

TDatabase._ColumnIndex¶

function TDatabase._ColumnIndex(column: String): Integer;

Returns the position of column in TDatabase.Columns, raising if the database has no such column.

TDatabase._BuildIndex¶

procedure TDatabase._BuildIndex(columnIndex: Integer);

Builds the index of the column at columnIndex if it was not built yet.

Rows are walked from last to first so that every value ends up pointing at its lowest row and each “next row” chain is in ascending order.

TDatabase._FirstRow¶

function TDatabase._FirstRow(value, column: String): Integer;

Returns the lowest row where column holds value, or -1 if no row does.

Querying Data¶

A database is only useful if you can query data from it. Below in this page you will see several examples of how to use TDatabase and these examples will be accompanied with a SQL equivalent.

One thing to note is that TDatabase is a “single table” database. This is also not a thing in real databases so in the following SQL examples we will be using TDatabaseTable as our imaginary table.

Database.Contains¶

function TDatabase.Contains(value, column: String): Boolean;

Returns true/false if the specified value exists on the specified column.

Example:

database.Contains('abyssal whip', 'item');

The SQL equivalent would be something like this:

SELECT EXISTS (
  SELECT 1
  FROM "TDatabaseTable"
  WHERE item = 'abyssal whip'
);

Database.Get¶

function TDatabase.Get(value, column_name, result_column: String): String;

Returns the data at result_column where the column_name column has the specified value. If several rows match, the lowest one is used. If none does, an empty string is returned.

Example:

WriteLn database.Get('abyssal whip', 'item', 'id');

The SQL equivalent would be something like this:

SELECT id
FROM "TDatabaseTable"
WHERE item = 'abyssal whip'
LIMIT 1;

Database.GetAll¶

function TDatabase.GetAll(value, column_name, result_column: String): TStringArray;

Same as Database.Get but will return all existing values instead of just one, in row order.

Example:

WriteLn database.GetAll('abyssal whip', 'item', 'id');

The SQL equivalent would be something like this:

SELECT id
FROM "TDatabaseTable"
WHERE item = 'abyssal whip';

Basically the same as Database.Get SQL example without the LIMIT.

Database.Insert¶

procedure TDatabase.Insert(values: TStringArray; saveCache: Boolean = False);

Inserts a row of data. values must have a value for each of TDatabase.Columns, in the same order.

By default, this data insertion only stays in memory, if you want to save it to disk, you need to set saveCache to True. Saved rows are loaded by TDatabase.Setup until the database version changes.

Example:

database.Insert(['Custom Item', '99999', 'abcdef']);

The SQL equivalent would be something like this:

INSERT INTO "TDatabaseTable" (item, id, hash)
VALUES ('Custom Item', '99999', 'abcdef');