Database portability

System: every INACT instance must run on MySQL, PostgreSQL and SQL Server from one code base. That is the point of INA01, "Refactor INACT For Database Abstraction". The instances really do differ: Medco and Tomori run SQL Server, the rest MySQL, and master's QA runs all three. MySQL-only SQL breaks silently on the other engines.

How this page was verified

The PDO layer, the driver mapping and the legacy helper were read on master on 2026-09-06. Which forks still have a working legacy helper was checked the same day.

The layers

Piece	What it is
DB_CONNECTION in .env	mysql, postgres or mssql.
DB_DRIVER constant, db_config.php	The PDO driver name: mysql, pgsql or sqlsrv. This is what code branches on.
InactPDO::connect(), libraries/InactPDO.php	Builds the DSN per driver and opens a plain PDO.
$pdo = new PDOLogActivity(), libraries/PDOLogActivity.php	The global handle. A wrapper that forwards every PDO method through __call() and adds ->log($text): $pdo->log("...")->exec($sql) writes an activity-log line for inserts, updates and deletes. ->save() and ->writeLog() are the pieces behind it.
phinx, phinx.php	Schema management. It reads the same .env, so migrations are written once in the phinx DSL and run on all three engines.

InactPDO::getLimit() and InactPDO::getAggregatedColumnNames() are the portability helpers.

The legacy helper is gone

iw_mysql_query() in libraries/inweb.lib.php is decommissioned: its first line is throw new Exception("Error Processing Request", 1). mysqli_* calls and $dbLink are dead with it. That is true in master, Jadestone, Medco and Timas. JOTRE still has the working version, which is why several legacy screens run there and nowhere else.

So a feature can look present and be dead. Before assuming anything works, check that the live path uses $pdo. The module pages list the dead paths per module. A screen that fails with "Error Processing Request" reached the legacy helper.

Writing portable SQL

Patterns that work on all three engines:

Do	Instead of	Why
$pdo->query($sql), then $stmt->fetch(), while ($r = $stmt->fetch()), $stmt->rowCount(); $pdo->exec($sql) for writes; $pdo->prepare($sql)->execute([...]) for values	iw_mysql_query(), mysqli_fetch_array()	the legacy helper throws
$pdo->quote($v)	'$v' with mysqli_real_escape_string()	quote() returns the value with its quotes; do not add your own
AND	&&	in PostgreSQL && is the array-overlap operator: an error, not a warning
$limit = InactPDO::getLimit(1) after an ORDER BY	LIMIT 1	SQL Server needs OFFSET 0 ROWS FETCH NEXT n ROWS ONLY, and requires the ORDER BY
COALESCE()	IFNULL()	MySQL only
plain identifiers	backticks	MySQL only
driver-aware date expressions	NOW(), DATE_ADD(... INTERVAL ...), date(now())	not portable; the code branches on DB_DRIVER for these
quote values compared against varchar columns: rout_indicate IN ('11','15','16')	IN (11,15,16)	PostgreSQL refuses character varying = integer. INA01-751 fixed exactly this in the sign-off close rule.
explicit GROUP BY of every selected column	loose GROUP BY with SET sql_mode = ''	the sql_mode trick is a MySQL 5.7 artifact
if (DB_DRIVER == 'sqlsrv') { ... TOP n ... } else if (DB_DRIVER == 'pgsql') { ... CAST(...) ... }	one string	the existing code's branching pattern; copy it

Engine limits to keep in mind:

Engine	Production version	Limits
MySQL	5.7.44	no CTEs (WITH), no window functions; LIMIT a,b syntax is MySQL only
PostgreSQL	14.22	strict typing; &&; identifier case folding
SQL Server	2019	no LIMIT; TOP n or OFFSET ... FETCH; ntext columns need care; identity inserts need SET IDENTITY_INSERT ... ON

Identity inserts differ too: SET IDENTITY_INSERT on SQL Server, OVERRIDING SYSTEM VALUE on PostgreSQL. Seeds that insert explicit ids have to handle both.

Fetch modes

$pdo->query() returns a plain PDOStatement; fetch() gives both numeric and associative keys unless you pass PDO::FETCH_ASSOC. Code that used mysqli_fetch_array() relied on the same dual keys, which is why most ports kept the default. Prepared statements on SQL Server sometimes need PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL to make rowCount() meaningful; the Document Library handler shows the pattern.

Verifying a change on three engines

Reasoning about dialects is not proof. The master test database inact-v3-eris exists on all three engines on the shared dev host (MySQL on port 3357, PostgreSQL on 54314, SQL Server on 1433). Write one PHP script that:

1. connects to each engine through PDO with the same DSN rules as InactPDO::connect();
2. seeds the same rows into inact-v3-eris on each;
3. runs the old query and the new query and prints both;
4. deletes the test rows.

That catches the identity-insert differences and the type-strictness differences at the same time. For a bug that depends on real data, run the new query on Medco's SQL Server snapshot and Jadestone's MySQL snapshot as well; the master databases are nearly empty.

Gotchas

• quote() includes the quotes. WHERE x = '" . $pdo->quote($v) . "' produces ''value''.
• rowCount() after SELECT is reliable on MySQL, unreliable elsewhere. Count in SQL or fetch and count.
• lastInsertId() needs the sequence name on PostgreSQL for some tables. Check how the neighbouring code does it before copying a MySQL-style call.
• Dates come back as strings, in the engine's format. Format on the way out, do not compare raw.
• The PDOLogActivity wrapper hides method names from static analysis. $pdo->log()->exec() is real; your IDE may not think so.