In part one, I translated one sentence. That proved ColdFusion could reach a model, which is only slightly more impressive than proving a browser can reach the internet. It didn’t tell me which content needed translation. I could loop over every string on a page and send the whole collection every time somebody clicks Save. That would translate content that hasn’t changed, overwrite work somebody already reviewed, and make my provider’s billing department unreasonably fond of me. Their affection would not be reciprocated.
I need an inventory: one record for each translatable field and target language. Each record should tell me what the source said when I last scanned it, whether it has a translation, and whether the source has changed since that translation was made. Without one, I’m asking the model to compensate for information I never bothered to record. I’ve worked on projects that operated this way. They had meetings about it.
For this example, I’ll use a small PostgreSQL page table with two plain-text fields. Your application will have its own content tables. The part worth taking with you is the scanner and its state rules, not my profound choice of column names.
Create the tables:
CREATE TABLE site_page (
site_id uuid NOT NULL,
id uuid NOT NULL,
source_locale varchar(20) NOT NULL,
title text NOT NULL DEFAULT '',
summary text NOT NULL DEFAULT '',
PRIMARY KEY (site_id, id)
);
CREATE TABLE translation_item (
site_id uuid NOT NULL,
page_id uuid NOT NULL,
field_name varchar(30) NOT NULL
CHECK (field_name IN ('title', 'summary')),
source_locale varchar(20) NOT NULL,
target_locale varchar(20) NOT NULL,
source_text text NOT NULL,
source_hash char(64) NOT NULL,
translated_text text,
status varchar(20) NOT NULL DEFAULT 'missing'
CHECK (status IN ('missing', 'draft', 'complete', 'stale')),
is_active boolean NOT NULL DEFAULT true,
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (site_id, page_id, field_name, target_locale),
FOREIGN KEY (site_id, page_id)
REFERENCES site_page (site_id, id)
ON DELETE CASCADE
);The pair of site_id and page_id is deliberate. A translation item can’t point to a page belonging to some other site. That doesn’t replace authorisation in the application, but it gives the database an opportunity to object before a mistake becomes an incident report with my name at the top.
The four statuses have narrow meanings. missing needs a translation. draft has text that hasn’t been approved. complete has been reviewed. stale still has its old translation, but the source has changed. Publication will get its own gate later; I don’t want “the model returned something” and “the public can see it” sharing a status by accident. One is a result. The other is a decision I’ll have to explain to people.
Next create components/TranslationInventory.cfc:
component {
public translationInventory function init( required string datasource ) {
variables.datasource = arguments.datasource;
return this;
}
public void function scanPage(
required string siteId,
required string pageId,
required string targetLocale
) {
var pageParams = {
siteId: { cfsqltype: "varchar", value: arguments.siteId },
pageId: { cfsqltype: "varchar", value: arguments.pageId }
};
transaction {
var pages = queryExecute(
"
SELECT
source_locale,
title,
summary
FROM
site_page
WHERE
site_id = CAST(:siteId AS uuid)
AND id = CAST(:pageId AS uuid)
FOR SHARE
",
pageParams,
{ datasource: variables.datasource, returnType: "array" }
);
if ( !arrayLen( pages ) ) {
throw(
type="Translation.PageNotFound",
message="The page was not found for this site."
);
}
var page = pages[ 1 ];
if ( compareNoCase( page.source_locale, arguments.targetLocale ) == 0 ) {
throw(
type="Translation.InvalidLocale",
message="The target locale must differ from the source locale."
);
}
var fields = [
{ name: "title", text: page.title },
{ name: "summary", text: page.summary }
];
for ( var field in fields ) {
var sourceText = toString( field.text );
var normalized = trim(
replace( sourceText, chr( 13 ) & chr( 10 ), chr( 10 ), "all" )
);
var sourceHash = lCase(
hash(
lCase( page.source_locale ) & ":" & normalized,
"SHA-256",
"UTF-8"
)
);
var itemParams = duplicate( pageParams );
itemParams.fieldName = { cfsqltype: "varchar", value: field.name };
itemParams.sourceLocale = { cfsqltype: "varchar", value: page.source_locale };
itemParams.targetLocale = { cfsqltype: "varchar", value: arguments.targetLocale };
itemParams.sourceText = { cfsqltype: "longvarchar", value: sourceText };
itemParams.sourceHash = { cfsqltype: "varchar", value: sourceHash };
itemParams.isActive = { cfsqltype: "bit", value: len( trim( sourceText ) ) > 0 };
queryExecute(
sql = "
INSERT INTO translation_item (
site_id,
page_id,
field_name,
source_locale,
target_locale,
source_text,
source_hash,
is_active
) VALUES (
CAST(:siteId AS uuid),
CAST(:pageId AS uuid),
:fieldName,
:sourceLocale,
:targetLocale,
:sourceText,
:sourceHash,
:isActive
)
ON CONFLICT (site_id, page_id, field_name, target_locale)
DO UPDATE SET
source_locale = EXCLUDED.source_locale,
source_text = EXCLUDED.source_text,
source_hash = EXCLUDED.source_hash,
is_active = EXCLUDED.is_active,
status = CASE
WHEN translation_item.source_hash = EXCLUDED.source_hash
THEN translation_item.status
WHEN translation_item.translated_text IS NULL
OR btrim( translation_item.translated_text ) = ''
THEN 'missing'
ELSE 'stale'
END,
updated_at = now()
",
params = itemParams,
options = { datasource: variables.datasource }
);
}
}
}
public array function pendingForPage(
required string siteId,
required string pageId,
required string targetLocale
) {
return queryExecute(
sql = "
SELECT
field_name,
source_locale,
target_locale,
source_text,
source_hash,
status
FROM
translation_item
WHERE
site_id = CAST(:siteId AS uuid)
AND page_id = CAST(:pageId AS uuid)
AND target_locale = :targetLocale
AND is_active = true
AND status IN ('missing', 'stale')
ORDER BY
field_name
",
params = {
siteId: { cfsqltype: "varchar", value: arguments.siteId },
pageId: { cfsqltype: "varchar", value: arguments.pageId },
targetLocale: { cfsqltype: "varchar", value: arguments.targetLocale }
},
options = { datasource: variables.datasource, returnType: "array" }
);
}
}The scanner reads the page from the database. It doesn’t accept a conveniently supplied collection of strings from the browser and trust that they belong to the requested page. It also names the two fields it will translate. A generic loop over every column would eventually send identifiers, addresses, internal notes, or something even more embarrassing to the model. I have enough ways to create incidents without adding a for loop.
For each field, I store a SHA-256 fingerprint of its source language and normalized text. That fingerprint is a change detector, not encryption; the original source text is still in the table. Including the source language matters because the same text interpreted under a different source language may need a new translation. Calling the fingerprint “encryption” during a security review would be an excellent way to learn how quietly a reviewer can judge me. ColdFusion’s hash() supports the algorithm and encoding used here. See Adobe’s hash() reference for details.
The database operation is an upsert. Don’t know what an upsert is? Neither does my spellchecker. It’s an insert if the row doesn’t exist, or an update if it does. A new field starts as missing. Scanning unchanged source preserves its status and any existing translation. Changed source becomes stale if translated text exists, or missing if it doesn’t. The scanner never changes translated_text. That old translation may be wrong now, but keeping it gives the reviewer something to compare against instead of sending them into the backups with a torch and a shovel. PostgreSQL’s ON CONFLICT DO UPDATE makes each insert-or-update atomic. Check out PostgreSQL’s INSERT documentation.
A blank field stays in the inventory but becomes inactive, so pendingForPage() won’t send it to the model. If somebody puts content back later, the next scan activates it again. Keeping the row lets me retain the previous translation for comparison without paying a model to contemplate the profound meaning of an empty summary.
To exercise it, insert a sample page into the PostgreSQL datasource you’ve named translationDemo:
INSERT INTO site_page (
site_id,
id,
source_locale,
title,
summary
) VALUES (
'11111111-1111-4111-8111-111111111111',
'22222222-2222-4222-8222-222222222222',
'en-CA',
'Welcome',
'Find out what is happening in our community.'
);Then run the scanner from a local diagnostic page:
<cfscript>
inventory = new components.TranslationInventory( "translationDemo" );
siteId = "11111111-1111-4111-8111-111111111111";
pageId = "22222222-2222-4222-8222-222222222222";
inventory.scanPage(
siteId=siteId,
pageId=pageId,
targetLocale="fr-CA"
);
writeDump(
inventory.pendingForPage(
siteId=siteId,
pageId=pageId,
targetLocale="fr-CA"
)
);
</cfscript>You should see title and summary, both marked missing. Run the scan again and you should still have exactly two items. The model hasn’t been called, and nothing has been translated twice. This is the exciting part of infrastructure work: doing it correctly produces almost nothing to look at.
In a real application, call scanPage() only after confirming the current user can manage the site and the target language is enabled for it. Remove or protect the diagnostic page after testing. A public page that dumps your content inventory is a generous feature, just not for your users. Strangers enjoy documentation too. It gives them things to play with.
I now have a repeatable answer to “what needs translation?” In part three, I’ll feed those eligible fields to ColdFusion’s native AI framework and deal with the less pleasant question: whether the answer it gives me is one I can safely use. The model will sound confident either way. I know the technique; I used it throughout my twenties. Usually when I was trying to bluff my way through something.