Database

flatbb runs on MySQL 5.7+ / MariaDB 10.2+ (utf8mb4, the installer default) or SQLite 3 (data/flatbb-<random>.sqlite). The same code path serves both; only core/db.php knows the dialect.

Portability rules#

• Quote identifiers with backticks. Bind values with ?.
• Types come from db_types():

• Never index a string/text column on MySQL; use key for anything that needs an index or unique constraint.
• Timestamps are unix seconds in uint. Booleans are 0/1. JSON is stored as text (json_encode_value() / json_decode_array()), never as a JSON column.
• Avoid: CTEs, window functions, INSERT … ON DUPLICATE KEY UPDATE written by hand (use db_upsert()), LIMIT in subqueries with IN on MySQL, RETURNING, ILIKE, || concatenation (use CONCAT on MySQL / || on SQLite → avoid both, concatenate in PHP), GREATEST (use db_greatest()), RANDOM()/RAND() (use db_random()).
• Full-text search is abstracted in core/search.php; do not query fb_search_fts directly.
• LIKE needs ESCAPE '!' and db_like() for user input (never a backslash as escape character: MySQL treats it as a string escape and fails with error 1064).

Helpers#

db_create_table('plugin_x_items', ['id' => 'id', 'name' => 'key', 'body' => 'text', 'created_at' => 'uint']);
db_ensure_columns('plugin_x_items', ['score' => 'int']);      // adds missing columns only
db_create_index('plugin_x_items', 'ux_x_items_name', ['name'], true);
db_drop_index(...); db_drop_column(...); db_drop_table(...);
$id = db_insert('t', [...]);  db_update('t', [...], 'id=?', [$id]);  db_delete('t', 'id=?', [$id]);
db_upsert('t', ['k' => 'a', 'v' => 1], ['k']);   db_insert_ignore('t', [...]);   db_increment('t', 'views', 1, 'id=?', [$id]);
rows_by_ids('t', $ids, 'id,name');               // keyed by id, chunked
tx(function () { ... });

Core tables (fb_ prefix)#

Full definitions: schema_tables() and schema_indexes() in core/schema.php. schema_install() is idempotent and is what the installer, php flatbb schema:upgrade and Admin → Tools run.

Migrations#

Core: bump SCHEMA_VERSION, add columns to schema_tables() (they are added with db_ensure_columns() on upgrade), add indexes to schema_indexes(). Data migrations go into schema_install() guarded by the stored schema_version setting.

Plugins: change install (it runs again when the manifest version changes) and keep it idempotent.

Moving from SQLite to MySQL#

1. Install a fresh FlatBB of the same version on MySQL. Copy plugins/ and uploads/ from the old site and enable the same plugins (so their tables exist).
2. Copy the old data/flatbb.sqlite somewhere the new server can read, then either run

```bash
php flatbb migrate:import /path/to/flatbb.sqlite
```

or use Admin → Tools → Import from SQLite.

1. Every fb_* and plugin_* table is emptied and refilled with the SQLite rows; ids are preserved, MySQL auto-increment counters are advanced, the search index and counters are rebuilt. Site settings that belong to the new install (rewrite, installed_at) are kept.

The importer lives in core/migrate.php and also works SQLite → SQLite (restore from a copy).