CockroachDB: define adapter-specific sqlTypes in migrator

[bpamiri]

Summary

CockroachDBMigrator has no sqlTypes struct — inherits PostgreSQL's type mappings which may not be optimal.

Details

• CockroachDB prefers STRING over VARCHAR (both work but STRING is native)
• INT preferred over INTEGER
• BOOL preferred over BOOLEAN
• Every other adapter defines its own sqlTypes (PostgreSQL: 12, MySQL: 19, Oracle: 22)

Expected

variables.sqlTypes['boolean'] = {name = 'BOOL'};
variables.sqlTypes['integer'] = {name = 'INT'};
variables.sqlTypes['string'] = {name = 'STRING', limit = 255};
variables.sqlTypes['text'] = {name = 'STRING'};
// etc.

Files

• vendor/wheels/databaseAdapters/CockroachDB/CockroachDBMigrator.cfc

[bpamiri]

Analysis

Synopsis

CockroachDBMigrator has no sqlTypes struct and inherits PostgreSQL's type mappings. CockroachDB has preferred native types (STRING, INT, BOOL) that differ from PostgreSQL's equivalents.

Merit Assessment

Recommendation: Proceed with modifications

• Every other adapter defines its own sqlTypes (MySQL: 18 types, MSSQL: 17, Oracle: 13, H2: 13, SQLite: 19, PostgreSQL: 12) — CockroachDB inheriting PostgreSQL's is inconsistent
• However, CockroachDB actually accepts PostgreSQL type names (VARCHAR, INTEGER, BOOLEAN, TEXT, BYTEA) as aliases — they work fine
• The native types (STRING, INT, BOOL) are preferred in CockroachDB docs but not strictly required
• This is an enhancement for correctness and idiomatic DDL, not a bug fix

Implementation Plan

1. Add sqlTypes struct to vendor/wheels/databaseAdapters/CockroachDB/CockroachDBMigrator.cfc
2. Recommended mappings (based on CockroachDB type docs):

variables.sqlTypes = {};
variables.sqlTypes['binary'] = {name = 'BYTES'};
variables.sqlTypes['boolean'] = {name = 'BOOL'};
variables.sqlTypes['date'] = {name = 'DATE'};
variables.sqlTypes['datetime'] = {name = 'TIMESTAMP'};
variables.sqlTypes['decimal'] = {name = 'DECIMAL'};
variables.sqlTypes['float'] = {name = 'FLOAT8'};
variables.sqlTypes['integer'] = {name = 'INT'};
variables.sqlTypes['string'] = {name = 'STRING', limit = 255};
variables.sqlTypes['text'] = {name = 'STRING'};
variables.sqlTypes['time'] = {name = 'TIME'};
variables.sqlTypes['timestamp'] = {name = 'TIMESTAMPTZ'};
variables.sqlTypes['uuid'] = {name = 'UUID'};

3. Notable differences from PostgreSQL:
   • binary → BYTES (not BYTEA)
   • text/string → STRING (not VARCHAR/TEXT)
   • uuid → native UUID type (CockroachDB has first-class UUID support)
   • timestamp → TIMESTAMPTZ (CockroachDB best practice — timezone-aware)
4. Estimated complexity: Low — declarative config, no logic changes

Dependencies & Risks

• Related: CockroachDB: addPrimaryKeyOptions uses SERIAL (unsupported) #1970 (the integer → INT mapping here should be consistent with what addPrimaryKeyOptions() generates), CockroachDB: expand $getType() coverage in model adapter #1974 (the model $getType() must map these types back correctly)
• Breaking change risk: None — new adapter, no existing CockroachDB migrations in the wild
• Modification: Consider whether TIMESTAMPTZ vs TIMESTAMP is the right default. CockroachDB recommends TIMESTAMPTZ but PostgreSQL adapter uses TIMESTAMP. If the Wheels test suite assumes timezone-naive timestamps, use TIMESTAMP instead.

---

Automated analysis by @claude — review and adjust as needed.