When db:connection-resource says sdbc:embedded:hsqldb or
sdbc:embedded:firebird, the database itself is in the package’s database/ folder. The
files belong to the two engines, not to OpenDocument; the manifest lists them with no media type. What follows was
read from LibreOffice’s own test documents (dbaccess/qa/unit/data in its source), the HSQLDB files
being worked out by reading those samples against their known contents (HSQLDB’s source was not read).
HSQLDB 1.8
LibreOffice’s embedded engine for its older files is HSQLDB 1.8.0.10. The folder holds up to five files.
| File | What it is |
|---|---|
database/script | Text, one SQL statement a line: how to rebuild the schema and the rows of memory tables. Non-ASCII characters are written \uXXXX. |
database/properties | A Java properties file: two comment lines (the engine name and version, and the date it was written), then key=value. |
database/data | Binary: the rows of the CACHED tables (the default for tables LibreOffice creates). |
database/backup | A zlib-compressed copy of data, made at the last checkpoint. |
database/log | Text: statements run since the last checkpoint, not yet merged into script and data. |
The script
Statements the samples hold, in file order:
SET DATABASE COLLATION "Hungarian"(only when a collation was chosen);CREATE SCHEMA PUBLIC AUTHORIZATION DBA;CREATE CACHED TABLE "Name"("ID" INTEGER NOT NULL PRIMARY KEY,"Power_value" DECIMAL(100,2),…):CACHEDtables keep their rows indata,MEMORYtables (andTEXTtables, whose rows are a text file named bySET TABLE … SOURCE) in the script. Identity columns readGENERATED BY DEFAULT AS IDENTITY(START WITH 0); a foreign key is aCONSTRAINT "name" FOREIGN KEY(…) REFERENCES …item;SET TABLE "Name" INDEX'96 0': the position of the root of each index of a cached table, then the last identity value (see below);CREATE USER SA PASSWORD "",GRANT DBA TO SA,SET WRITE_DELAY 60;INSERT INTO "Name" VALUES(1,'text',NULL): one for each row of a memory table. A string’s quote is doubled.
Properties
Keys the samples hold: hsqldb.script_format, runtime.gc_interval, sql.enforce_strict_size,
hsqldb.cache_size_scale, readonly, hsqldb.nio_data_file, hsqldb.cache_scale,
version, hsqldb.default_table_type (cached), hsqldb.cache_file_scale,
hsqldb.lock_file, hsqldb.log_size, modified (yes when the files
were left open), hsqldb.cache_version, hsqldb.original_version and
hsqldb.compatible_version. The explorer reads hsqldb.cache_file_scale (the number of bytes
a position counts for, 1 in the samples) to find rows.
The data file
Worked out from hsqldb_migration_test.odb (three rows of five columns, whose contents LibreOffice’s tests
give), fdo84315.odb and tdf126268.odb. Numbers are big-endian.
- The file starts with 32 bytes of header (zeros in the samples, bar the end-of-file position at
0x10). Rows follow from0x20. - A row is stored at a byte position (position ×
hsqldb.cache_file_scale). It begins with a 4-byte size of the whole row. - Then one 16-byte node for each index of the table: balance, left, right and parent, each a 4-byte position (0 for none). The nodes are those of an AVL tree. The first index belongs to the primary key (or a hidden row id) and its tree holds every row, so reading it in order, from the root the script gives, reaches every row.
- Then the columns in order. Each starts with a byte, 0 for a NULL (nothing follows) or 1 for a value, then the value by type:
TINYINT,SMALLINTandINTEGERfour bytes;BIGINTeight;REAL,FLOATandDOUBLEeight bytes (IEEE 754);DECIMALandNUMERICa 4-byte length, that many bytes of two’s-complement integer, and a 4-byte scale; the character types a 4-byte length in bytes and the UTF-8 text;BOOLEANone byte;DATEandTIMEeight bytes of milliseconds (a date is local midnight of the machine that wrote it);TIMESTAMPeight bytes of milliseconds and four of nanoseconds; the binary types a 4-byte length and the bytes. - Rows are padded to a multiple of 8 bytes in the samples.
The explorer lists each table’s rows from the script or from this file, and says where each came from.
Backup and log
backup is a zlib stream (78 01 …) that inflates to the bytes of data; the explorer reports its inflated size. log holds statements, some prefixed by a session comment such as /*C2*/; they are applied to the script’s state when the database is opened.
Firebird
Newer files hold database/firebird.fdb, a Firebird database in its own page format (the on-disk structure,
ODS), and sometimes database/firebird.fbk, a gbak backup LibreOffice makes when it migrates an older
file. The file is a sequence of pages of one size; every page starts with a 16-byte page header. The layouts here come from
Firebird’s src/jrd/ods.h and relations.h (single raw files of the B3_0_Release branch,
for ODS 12, and of master, for ODS 13 and later) and were checked against
firebird_integer_ods12.odb. All numbers are little-endian.
Pages
| Offset | Size | Field of every page |
|---|---|---|
0 | 1 | Page type: 1 header, 2 page inventory, 3 transactions, 4 pointer, 5 data, 6 index root, 7 index, 8 blob, 9 generators, 10 SCN (the sample has 1, 1, 1, 37, 91, 37, 62, 2, 1 and 1 of them; page 0 is the header page). |
1 | 1 | Page flags (0x80: encrypted page). |
4 | 4 | Generation. |
8 | 4 | SCN. |
12 | 4 | The page’s own number, for validation. |
Header page
| Offset (ODS 12) | Offset (ODS 13+) | Size | Field |
|---|---|---|---|
16 | 16 | 2 | Page size in bytes (0x1000 = 4,096 in the sample). |
18 | 18 | 2 | ODS major version: the low 15 bits are the number, the top bit marks a Firebird (rather than InterBase) file (0x800C = ODS 12). |
20 | 28 | 4 | hdr_PAGES: the page of the RDB$PAGES relation (3 in the sample). |
28, 32, 36 | 48, 56, 40 (8 bytes each) | 4 | Oldest transaction, oldest active transaction, next transaction. |
42 | 22 | 2 | Flags: 0x1 active shadow, 0x2 forced writes, 0x4 encryption in process, 0x8 no space reserved, 0x10 SQL dialect 3, 0x20 read only, 0x40 encrypted. |
44 | 100 | 8 | Creation date: a day number (days since 17 November 1858) and a time in ten-thousandths of a second. |
64 | 20 | 2 | ODS minor version. |
68, 72 | 32, 64 | 4 | Page buffers; oldest snapshot. |
| none | 84 | 16 | Database GUID. |
The file size divided by the page size is the page count (240 pages of 4,096 bytes in the sample).
Data pages and records
A data page (type 5) holds the rows of one relation: 16 (4 bytes) the page’s sequence number within the relation, 20
(2) the relation number, 22 (2) the number of slots; from 24, each slot is an offset (2) and a length (2) of a
record in the page (offset 0 is a free slot). A pointer page (type 4) lists a relation’s data pages: 16 sequence,
20 next pointer page, 24 count (2), 26 relation (2), 32 the page numbers. A record starts with:
| Offset | Size | Field |
|---|---|---|
0 | 4 | Number of the transaction that wrote this version. |
4, 8 | 4, 2 | Page and slot of the previous (back) version. |
10 | 2 | Flags: 1 deleted, 2 old version, 4 fragment, 8 incomplete (the rest is in a fragment), 16 blob, 32 delta or stream blob, 64 large, 128 damaged, 256 garbage collection active, 512 unique key modified, 1024 long transaction number, 2048 not packed. |
12 | 1 | Number of the record format (the layout of the fields). |
13 (16 with flag 1024; 22 for an incomplete record or a fragment, with the next fragment’s page at 16 and slot at 20) | rest | The data, run-length packed unless flag 2048 is set: a positive signed control byte n is followed by n literal bytes; a negative one by one byte repeated −n times. Trailing zero bytes are left out. |
The schema
The schema is stored in ordinary relations whose numbers are fixed: 0 RDB$PAGES, 1 RDB$DATABASE, 2 RDB$FIELDS,
3 RDB$INDEX_SEGMENTS, 4 RDB$INDICES, 5 RDB$RELATION_FIELDS, 6 RDB$RELATIONS, 7 RDB$VIEW_RELATIONS,
8 RDB$FORMATS, and so on (the names the sample’s RDB$RELATIONS holds for numbers 0 to 49); user tables start at 128.
A record’s fields are found through the format of its relation. In the sample the system relations’ own formats are not stored, but
the user table’s is, in RDB$FORMATS. A record is a null bitmap of ((n + 32) & ~31) / 8 bytes (one bit per field: set means NULL; n = 6 gives 4 bytes), then the fields at
the offsets the format gives.
| Relation | Field and offset in the unpacked record (ODS 12) |
|---|---|
RDB$RELATIONS (6) | relation id 32 (2), system flag 34, format number 38, number of fields 40, name 42 (CHAR(31), space padded), security class 73. The field order is the one in relations.h (three blob ids, five shorts, the name, …); blob ids sit at multiples of 8. |
RDB$RELATION_FIELDS (5) | field name 4, relation name 35, field source 66 (each CHAR(31)), field position 290 (2), nullable flag 304, field id 306 (2). The position is the column’s order in CREATE TABLE; the field id is the index of its descriptor in the format. |
RDB$FORMATS (8) | relation id 4 (2), format number 6 (2), descriptor blob id 8 (8 bytes: relation number (2) and record number (4 at 4)). |
The descriptor blob (a blob record: header with the level at 12, the length at 20, the sub type at 24, level 0 data inline from 28)
holds a record length (2), a field count (2) and then 12 bytes per field: type (1), scale (1, signed), length (2), sub type (2), flags (2), offset in the record (4).
Types: 1 CHAR, 2 CSTRING, 3 VARCHAR (a 2-byte length then the text), 6 packed, 7 byte, 8 SMALLINT, 9 INTEGER, 10 quad, 11 FLOAT, 12 DOUBLE PRECISION,
13 d_float, 14 DATE (days since 17 November 1858), 15 TIME (ten-thousandths of a second), 16 TIMESTAMP (date then time), 17 BLOB, 18 array, 19 BIGINT, 20 db key, 21 BOOLEAN
(values of dsc_pub.h). A scale below 0 in an integer field means that many decimal digits.
The sample’s TESTTABLE (relation 128, six columns, one row: 5, −30000, −2100000000, −9000000000000000000, “5”, “5”) decodes completely from these rules.
Other schema relations, blob levels and a second file
Read from the field order of relations.h and checked on the ODS 12 sample and on a second real file, core1331.fdb of Firebird’s own
firebird-qa repository (ODS 11.0, Firebird 2.5, 4,096-byte pages): in every system relation the names are CHAR(31)
at 4 and 35 and the numbers after them.
| Relation | Field and offset in the unpacked record |
|---|---|
RDB$INDICES (4) | index name 4, relation name 35, index id 66 (2), unique flag 68 (2, 1 for unique). |
RDB$INDEX_SEGMENTS (3) | index name 4, field name 35, position in the index 66 (2). |
RDB$RELATION_CONSTRAINTS (22) | constraint name 4, type 35 (CHAR(11): PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL), relation name 46. Seen on the ODS 12 file (29 system constraints, all UNIQUE); the ODS 11 file has none. |
RDB$TRIGGERS (12) | trigger name 4, relation name 35, sequence 66 (2), type 68 (2, ODS 11) or 72 (8, from ODS 12, a BIGINT). Type 1 is before insert, 2 after insert, 3 before update, 4 after update, 5 before delete, 6 after delete; a trigger for several actions sets the bit for before (1) or after (0) and puts each action (1 insert, 2 update, 3 delete) in two bits from (type + 1) >> 1, (type + 1) >> 3 and (type + 1) >> 5; 8192 and up are database events. The same three system triggers read as before update, before delete and before insert on both files. |
RDB$RELATIONS (6), views | the first field, RDB$VIEW_BLR (a blob id at 8), is NULL for a table: bit 0 of the null bitmap; RDB$VIEW_SOURCE is the blob id at 16. Not seen on a real file (neither sample has a view). |
The two files differ in where fields sit only after the name: the viewer finds the name’s offset from the file (the text RDB$PAGES in the first
relation’s record) and reads the id, system flag, format and field count at fixed distances before it, which is how ODS 13 is read. On the ODS 11 file the
user table’s format blob does not parse as an ODS 12 descriptor stream (its first words are 0c 00 03 00, then 92 01 04 00 00 00 04 00),
so that file’s T1 shows no types.
A blob record that is not at level 0 lists page numbers (4 bytes each) from offset 28: at level 1 they are blob pages holding the data, at level 2
blob pages that hold the list of data pages. A blob page (type 8) has the length of its bytes (2) at 24 and the bytes, or the next level’s page numbers, from 28.
Segmented blobs have a 2-byte length before each segment, which is dropped when the lengths add up exactly to the data. This was checked on a constructed file only.
Not described
The two engines’ formats are their projects’ own; this page records what the explorer reads.
Known, not yet covered
- Reference: Firebird: index root and index pages (type 6 and 7), the page inventory, transaction and generator pages are named but their layouts are not written down; arrays, the offsets of fields in
RDB$FIELDSand the other schema relations not listed above (generators, domains, procedures), how a transaction decides which record version it sees (the transaction inventory), delta records, the ODS 11 descriptor stream, ODS 13 and later record compression and the layout of its system relations (the ODS 12 layout was checked on a real file and the system relations on an ODS 11 one; ODS 13 only against the header inods.h, no file of that version was available), blob levels 1 and 2 on a real file (checked on a constructed one), and the gbak backup format. - Reference: HSQLDB 1.8’s binary layout beyond the samples: other index kinds, deleted rows, rows larger than a block, LOB storage, and the effect of a scale other than 1.
- Viewer: Firebird: only the newest stored version of each record is read, without asking which transactions committed; only ODS 12 files get table names, columns and rows (an ODS 11 file gets names, ids, indexes, constraints and triggers, and its user tables’ rows show that their format could not be decoded; an ODS 13 file is read at offsets found from the file, untested); blobs of level 1 and 2 are read; text is shown as Latin-1 whatever the column’s character set; views, indexes, constraints and triggers are listed, generators and domains are not;
firebird.fbkis not read. HSQLDB text tables (SET TABLE … SOURCE) are listed but their external source file is not read.
Unknown
- Reference: the meaning of every key in
database/propertiesbeyond those named above, and whether LibreOffice writes HSQLDB 2.x files in any version.