Skip to content
JoeCMS Download 3.1.5
Manual · 07

Data, database and content

ffDB_Sql for SQL, Storage for the multi-driver abstraction, the vgallery model for content. And four ffDB_Sql pitfalls that cost half a day to anyone who doesn't know them.

1. ffDB_Sql: database access

$db = ffDB_Sql::factory();

$db->query("SELECT ID, nome FROM tabella WHERE ID_utente = " . $db->toSql($id, "Number"));
while ($db->nextRecord()) {
    $nome = $db->getField("nome", "Text", true);      // true = PHP value, not an ffData object
}

$db->execute("INSERT INTO tabella (nome) VALUES (" . $db->toSql($nome, "Text") . ")");
$nuovo = $db->getInsertID(true);

The four pitfalls

1.1 execute() to write, query() to read ⚠️

affectedRows() and getInsertID() don't work after query(). With FF_DB_MYSQLI_SHARELINK on (default true, ff/classes/ffDb_Sql/ffDb_Sql_mysqli.php:14) both read buffered properties that only execute() sets. After an INSERT done with query(), getInsertID() returns a stale or null value — and no error.

Make the distinction structural: a helper that writes uses execute(), helpers that read use query(). Don't leave it to the memory of whoever writes the line.

The method is getInsertID(). Other projects in the ecosystem have a wrapper called insertID(): it's not the same name, and the difference shows up as a fatal error.

1.2 factory() returns a new instance

new static() on every call (line ~189), with the connection shared. Each instance has its own recordset: a query nested inside a nextRecord() loop doesn't lose rows if it uses a new handle. The classic bug comes from reusing the same $db variable in the inner loop.

1.3 toSql() already adds the quotes

$db->toSql($v, "Text") produces 'value'. Don't add quotes by hand. With transform_null = true (default) an empty string stays '', it doesn't become NULL.

1.4 Dates

The driver's locale is ISO9075, so the format is Y-m-d H:i:s. It's still best to pass them as "Text": with "DateTime" an empty value becomes '0000-00-00 00:00:00', which MariaDB in strict mode rejects. For an empty date field write a literal NULL.

Other useful notes

  • getField($name, $type, true) — the third parameter returns the PHP value instead of an ffData object. Forgetting it leads to comparisons that never match.
  • Compute dates in PHP, not SQL: the server's @@time_zone is typically SYSTEM, so CURDATE() isn't the "day" the application means. If the time zone matters (daily resets, time windows), compute in PHP with an explicit time zone.

Strict servers: the rules

The code runs with the default sql_mode of modern servers (MySQL 8, Aiven, MariaDB): ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_DATE. The sql_mode is not relaxed: you fix the query or the schema. The test bench is a MySQL 8 with its default.

  • GROUP BY: every column in the SELECT is grouped, aggregated, or depends on the grouped primary key (MySQL recognizes it even through JOINs on the key). The old idiom SELECT * FROM (... ORDER BY x) AS tbl_src GROUP BY ID to "keep the first row" is forbidden (#1055, and MariaDB ignores the subquery's ORDER BY anyway): you order the flat query and keep the first row per key in PHP (library/gallery/process/init_modules.php), or ROW_NUMBER() OVER (...) (conf/gallery/manage/block/modify.php), or a JOIN on MIN(ID) (library/gallery/common/get_layout_settings.php).
  • DISTINCT with ORDER BY: the ordering columns must be in the SELECT (#3065). Here functional dependencies don't count: if you order by vgallery_fields.order_thumb, select it.
  • New columns: NOT NULL always with a DEFAULT (#1364), dates NULL DEFAULT NULL.
  • Empty dates: NULL, never '0000-00-00'. toSql() of an empty date already returns null; in comparisons you write IS NULL (= '0000-00-00' is an error even when reading, #1525).
  • Empty values in numeric columns (#1366): in hand-written queries you pass the type, toSql($v, "Number"), or, if the value may be a text ffData, the variant that knows the column: $db->toSqlColumn("tabella", "colonna", $v) ('' → 0, empty date → NULL). ffRecord, ffDetails and Storage already use it. Careful: toSql($ffDataTesto, "Number") reads the value as an unset number and writes 0.
  • Auto-increment key: NULL in the INSERT, not ''.
  • Long texts: a value longer than the column is rejected (#1406), not truncated. The form field carries $oField->properties["maxlength"].
  • ANSI dialect (Aiven): with PIPES_AS_CONCAT, || concatenates instead of doing OR, without an error, and with ANSI_QUOTES a string in double quotes becomes a column name. In SQL you write OR, and strings go in single quotes, the way toSql() writes them.

2. Storage: the same API on different drivers

vendor/wolfgan/storage/Storage.php abstracts persistence behind a single interface (lookup, find, read, insert, update, write, delete, cmd) over several services:

ServiceDriver
sql_mysql.phpMySQL/MariaDB
nosql_mongodb.phpMongoDB (vendor/mongodb/ is there)
fs_json.php, fs_xml.php, fs_php.phpfiles on the filesystem
$st = Storage::getInstance("sql_mysql");
$st->setTable("tabella");
$rows = $st->read(array("ID_utente" => $id), array("ID", "nome"), array("nome" => "ASC"), 50);

It also includes conversion between formats (convertData, convertWhere) and an internal query cache.

When the store isn't there. Storage doesn't throw exceptions: if the driver can't open the device, controller_nosql() writes a line in cache/logs/AAAA-MM-GG_storage_nosql.txt (with the requested action) and the call returns nothing. Before, with DEBUG_MODE on, it did exit inside vgCommon::isError(): an accessory service like the page statistics took the whole page down. The SQL branch instead stays blocking — a missing relational database is a configuration error that has to show.

When to use it. Storage pays off when data can live on different media (a configuration in a file, a log in Mongo) or when you want a uniform model. For ordinary application SQL, direct ffDB_Sql stays more readable and easier to profile — and it's what the rest of the CMS uses.

3. The content model (vgallery)

This is the part to understand before designing anything on top of this CMS.

TableMeaning
vgallerythe collection/section (rules, levels, multilingual, e-commerce)
vgallery_nodesthe node skeleton: page or folder (is_dir). Hierarchy via parent (path, not ID) + name; permalink, order, visibility, basic SEO
vgallery_type + vgallery_fieldsthe dynamic schema of fields per content type
extended_typethe field's edit/render widget (String, Text, TextBB, Selection, GMap, UploadImage…) — separates data from interface
vgallery_rel_nodes_fieldsthe actual content: one row per (node × field × language)
vgallery_nodes_rel_languagesper-language SEO metadata
users_rel_vgalleryuser↔node permissions, with cascading inheritance
rel_nodesnode↔node relations

Architectural consequence: there are no tables per content type. An "article" or a "product" is a type with a set of fields; the model doesn't change. Adding a content type is configuration (from the backend), not code.

The flip side: a query "give me all articles with field X" is a join on vgallery_rel_nodes_fields, not a SELECT * FROM articles. For heavy reads go through the helpers (library/gallery/common/get_node_by_permalink.php, get_vgallery_card.php, get_schema_fields_by_type.php…) and the cache.

Writing a node

⚠️ Don't reimplement saving. update_vgallery (in library/gallery/common/) handles relations, permalink/smart_url, multilingual and cache invalidation. The principle adopted for the new admin area is "new reading, reused writing": a new UI POSTs with the same names the existing controllers expect. See VGALLERY_NEXT_PIANO.md.

Other tables to know

TableUse
access_users, access_groups, access_users_groupsaccounts, groups, membership (admin is ID=1 in the seed)
anagraph, anagraph_companyperson profile (via anagraph.ID_user) and company data
access_users.locale, access_users.ID_lang, anagraph.ID_langthe user's language preference: three columns for the same information, written together by language_pref_write_db(). NULL means "never chose", which is different from "chose the default language" (see 06 §2)
cm_mod_security_domainsthe managed domain (ID=1 in the seed), company_name
cm_mod_security_domains_fieldsservices configuration: (field, group, value) with group = service name
ff_languages, ff_internationalactive languages (status) and dictionary (word_code → text per language)
layout, layout_path, layout_locationpage blocks; layout_path.visible turns header (ID 2) and footer (ID 3) on/off
cm_mod_security_userssecond legacy users table: it exists, contains the same admin, and is not the active login path
cm_api_client, cm_api_tokenpublic API credentials and tokens

4. Migrations and database.sql

Iron rule of the project: every migration must also go into the root database.sql.

The root dump is the installer seed (218 CREATE TABLE, 90 INSERT) and must stay self-sufficient: DB-agnostic (no CREATE DATABASE/USE), without DELIMITER, triggers or views, so importing it is trivial on any hosting.

Procedure (since 3.0.3; before, migrations lived in docs/sql/ and were applied by hand):

  1. write the migration in migrations/schema_000N_<nome>.sql or .php, with N the new schema, idempotent: MigrationsRunner (library/updater/MigrationsRunner.php) runs it from the updater, from php bin/update.php --migrations-only --vai or from /restricted/updater, and records it in jc_migrations;
  2. copy the same CREATE TABLE / ALTER, or the same rows, to the end of database.sql;
  3. bump JC_SCHEMA_VERSION (in install/lib.php): a new installation starts with that number in its config, and on its first run the runner marks the migrations up to it as already applied;
  4. note it in CHANGELOG.md under [Unreleased], in the ### Schema group: at release time it is what makes joecms.it warn about the database update, and without it the build stops (14 §5).

Data rows in the seed count too: new dictionary entries change database.sql, hence the schema. The migration does not insert them with fixed IDs, which on a live site may already be taken by the placeholders ffTranslator creates: it fills those and inserts only what is missing, without touching the translations written by the administrator (migrations/schema_0005_testi_multilingua.php).

Exception: schema 4 (strict servers) has no SQL file but a tool, bin/db-allinea-schema.php, because it has to read the actual columns of each installation (old ones have types different from database.sql, and the tool changes none of them). Dry run by default, --vai to execute, --config <file> for another config; idempotent. If the connection drops midway it reconnects once, and if it doesn't come back it stops: run again, it resumes from where it got to. On an existing site it runs before uploading the new code (see 14 §4).

Exception: a migration that only repairs data on existing installations, without touching the schema, doesn't go into database.sql and doesn't raise JC_SCHEMA_VERSION, because a new installation has no data to repair. The file says so at the top. Example: docs/sql/2026-09_lingua-profili-allineamento.sql, which realigns the language on profiles created before the registration fix.

Code depending on a migration must be defensive: a ..._has_schema() function checking the tables, and a 503 (API) or a logged skip (cron) instead of a fatal error. A half-done deploy diagnoses itself.

Schema conventions

  • No FOREIGN KEY, consistent with the 218 existing tables; indexes yes, all of them.
  • utf8mb4 on new tables holding free text: historical tables are utf8mb3 and truncate emoji and CJK.
  • Recognisable prefix for application tables (cm_mod_<project>_...), so a dump is readable and a clean-up doesn't touch the CMS.
  • Single database: the ANAGRAPH / TRACE / NOTIFY constants all point to the same DB.

5. Import, export, backup

library/gallery/common/ already contains the tools:

FileWhat it does
import.php, importcsv.phpcontent import, from CSV too
export.phpexport
clone_by_schema.phpduplicating structures according to the schema
convert_db.phpschema conversions
check_fs.php, check_system.phpfilesystem and system checks

Plus conf/gallery/backup/ and conf/gallery/import/ on the backend side. They're upstream tools: test them on your own schema before relying on them for a real migration.