Data, database and content
ffDB_Sqlfor SQL,Storagefor the multi-driver abstraction, the vgallery model for content. And fourffDB_Sqlpitfalls 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 calledinsertID(): 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 anffDataobject. Forgetting it leads to comparisons that never match.- Compute dates in PHP, not SQL: the server's
@@time_zoneis typicallySYSTEM, soCURDATE()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 theSELECTis grouped, aggregated, or depends on the grouped primary key (MySQL recognizes it even throughJOINs on the key). The old idiomSELECT * FROM (... ORDER BY x) AS tbl_src GROUP BY IDto "keep the first row" is forbidden (#1055, and MariaDB ignores the subquery'sORDER BYanyway): you order the flat query and keep the first row per key in PHP (library/gallery/process/init_modules.php), orROW_NUMBER() OVER (...)(conf/gallery/manage/block/modify.php), or aJOINonMIN(ID)(library/gallery/common/get_layout_settings.php).DISTINCTwithORDER BY: the ordering columns must be in theSELECT(#3065). Here functional dependencies don't count: if you order byvgallery_fields.order_thumb, select it.- New columns:
NOT NULLalways with aDEFAULT(#1364), datesNULL DEFAULT NULL. - Empty dates:
NULL, never'0000-00-00'.toSql()of an empty date already returnsnull; in comparisons you writeIS 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 textffData, the variant that knows the column:$db->toSqlColumn("tabella", "colonna", $v)(''→0, empty date →NULL).ffRecord,ffDetailsandStoragealready use it. Careful:toSql($ffDataTesto, "Number")reads the value as an unset number and writes0. - Auto-increment key:
NULLin theINSERT, not''. - Long texts: a value longer than the column is rejected (#1406), not truncated. The form field carries
$oField->properties["maxlength"]. ANSIdialect (Aiven): withPIPES_AS_CONCAT,||concatenates instead of doing OR, without an error, and withANSI_QUOTESa string in double quotes becomes a column name. In SQL you writeOR, and strings go in single quotes, the waytoSql()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:
| Service | Driver |
|---|---|
sql_mysql.php | MySQL/MariaDB |
nosql_mongodb.php | MongoDB (vendor/mongodb/ is there) |
fs_json.php, fs_xml.php, fs_php.php | files 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.
Storagepays 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, directffDB_Sqlstays 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.
| Table | Meaning |
|---|---|
vgallery | the collection/section (rules, levels, multilingual, e-commerce) |
vgallery_nodes | the node skeleton: page or folder (is_dir). Hierarchy via parent (path, not ID) + name; permalink, order, visibility, basic SEO |
vgallery_type + vgallery_fields | the dynamic schema of fields per content type |
extended_type | the field's edit/render widget (String, Text, TextBB, Selection, GMap, UploadImage…) — separates data from interface |
vgallery_rel_nodes_fields | the actual content: one row per (node × field × language) |
vgallery_nodes_rel_languages | per-language SEO metadata |
users_rel_vgallery | user↔node permissions, with cascading inheritance |
rel_nodes | node↔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
| Table | Use |
|---|---|
access_users, access_groups, access_users_groups | accounts, groups, membership (admin is ID=1 in the seed) |
anagraph, anagraph_company | person profile (via anagraph.ID_user) and company data |
access_users.locale, access_users.ID_lang, anagraph.ID_lang | the 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_domains | the managed domain (ID=1 in the seed), company_name |
cm_mod_security_domains_fields | services configuration: (field, group, value) with group = service name |
ff_languages, ff_international | active languages (status) and dictionary (word_code → text per language) |
layout, layout_path, layout_location | page blocks; layout_path.visible turns header (ID 2) and footer (ID 3) on/off |
cm_mod_security_users | second legacy users table: it exists, contains the same admin, and is not the active login path |
cm_api_client, cm_api_token | public 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):
- write the migration in
migrations/schema_000N_<nome>.sqlor.php, with N the new schema, idempotent:MigrationsRunner(library/updater/MigrationsRunner.php) runs it from the updater, fromphp bin/update.php --migrations-only --vaior from/restricted/updater, and records it injc_migrations; - copy the same
CREATE TABLE/ALTER, or the same rows, to the end ofdatabase.sql; - bump
JC_SCHEMA_VERSION(ininstall/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; - note it in
CHANGELOG.mdunder [Unreleased], in the### Schemagroup: 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. utf8mb4on new tables holding free text: historical tables areutf8mb3and 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/NOTIFYconstants all point to the same DB.
5. Import, export, backup
library/gallery/common/ already contains the tools:
| File | What it does |
|---|---|
import.php, importcsv.php | content import, from CSV too |
export.php | export |
clone_by_schema.php | duplicating structures according to the schema |
convert_db.php | schema conversions |
check_fs.php, check_system.php | filesystem 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.