Class CodeGenDatabaseProviderAbstract

Returns the batch separator for the database platform. SQL Server: 'GO' PostgreSQL: '' (empty string, uses semicolons)

The SQL dialect instance for this provider.

Whether this platform needs explicit view refresh after schema changes. SQL Server: true (uses sp_refreshview) PostgreSQL: false (views resolve at query time)

Whether this platform needs a post-sync fix for virtual field nullability. SQL Server: false PostgreSQL: true (PG view columns always report attnotnull=false)

The database platform key (e.g., 'sqlserver', 'postgresql').

Returns the native timestamp-with-timezone type name for this platform. SQL Server: DATETIMEOFFSET PostgreSQL: TIMESTAMPTZ

Generates ALTER TABLE ... ADD COLUMN SQL. SQL Server: ALTER TABLE [schema].[table] ADD colName TYPE [NOT] NULL [DEFAULT expr] PostgreSQL: ALTER TABLE schema."table" ADD COLUMN "colName" TYPE [NOT] NULL [DEFAULT expr]

Generates SQL to add a default constraint/value to a column. SQL Server: ALTER TABLE ... ADD CONSTRAINT DF_name DEFAULT expr FOR [col] PostgreSQL: ALTER TABLE ... ALTER COLUMN "col" SET DEFAULT expr

Generates ALTER TABLE ... ALTER COLUMN to change type and nullability. SQL Server: ALTER TABLE ... ALTER COLUMN col TYPE NULL|NOT NULL PostgreSQL: ALTER TABLE ... ALTER COLUMN "col" TYPE type, ALTER COLUMN "col" SET|DROP NOT NULL

Builds primary key variable declarations, select fields, fetch-into, and SP param strings for use in cursor-based cascade operations.

Generates SQL to invoke a stored procedure or function. SQL Server: EXEC [schema].[spName] @Param1='val1', @Param2='val2' PostgreSQL: SELECT * FROM schema."spName"('val1', 'val2')

Whether this dialect can handle a base view that LEFT-JOINs itself to read a virtual computed column (e.g. vwRecordChanges joining to itself for the RestoredFromID virtual NameField lookup).

Default: false. No shipped provider currently supports this pattern:

• PostgreSQL: CREATE OR REPLACE VIEW resolves view names against catalog state at parse time, so a self-reference fails with 42P01 undefined_table. A CREATE OR REPLACE retry against a NULL-typed stub then fails with cannot change data type of view column ... from text to character varying(N) because PG enforces strict column-type compat.
• SQL Server: the base view emitter uses DROP VIEW then CREATE VIEW, and SQL Server resolves view-body references at parse/bind time — there is no deferred name resolution for view bodies the way there is for stored procedures. After the DROP, the post-DROP self-reference fails with error 208 "Invalid object name".

With the default of false, sql_codegen.ts skips the self-virtual- NameField join entirely for self-FK + virtual-NameField cases. The trade- off: the corresponding virtual lookup column (e.g. RestoredFrom on vwRecordChanges, or Parent on a vwTags-style view if the Name Field were computed) is not emitted on the base view. Matches the baseline- shipped view shapes.

Subclasses can override to return true if a future dialect (or a provider that switches to a different emit pattern, e.g. stub-then-alter) can support the self-reference. The fix for the underlying conflation between SQL Server computed columns and view-only columns under IsVirtual = 1 would let the join target the base table instead, removing the need for this capability flag entirely.

Compares two data type names, accounting for platform-specific aliases. For example, PostgreSQL reports timestamp with time zone in information_schema but DDL uses timestamptz.

Generates a conditional INSERT statement (insert only if not exists). SQL Server: IF NOT EXISTS (checkQuery) BEGIN insertSQL END PostgreSQL: DO $$ BEGIN IF NOT EXISTS (checkQuery) THEN insertSQL; END IF; END $$

Generates SQL to drop an existing default constraint from a column. SQL Server: Dynamic lookup of constraint name from sys catalog + DROP. PostgreSQL: Dynamic lookup from pg_catalog + ALTER COLUMN DROP DEFAULT.

Generates a DROP statement for a database object (view/procedure/function). SQL Server: IF OBJECT_ID('...', 'P') IS NOT NULL DROP PROCEDURE ... PostgreSQL: DROP FUNCTION IF EXISTS ... CASCADE

Note: This differs from generateDropGuard() which is used for CREATE OR REPLACE patterns. This method is used for cleanup operations.

Optional — dialect-specific phased execution of a single entity's full CodeGen SQL package (view, CRUD functions, permissions) for the main per-entity run path.

Default path concatenates all the SQL and hands it to the shell executor, which runs it as a single multi-statement query. When any statement fails, pg's simple-query protocol aborts the rest of the batch — so a view that fails 42P16 silently blocks the CREATE FUNCTIONs that follow for the same entity.

Providers that implement this method run the pieces in separate phases so a failure in phase 1 prevents phase 2 from producing functions that reference a missing or stale view. PG's implementation additionally routes the view phase through the 42P16 capture/restore fallback.

Phasing contract: Phase 0 = TVF DDL (tvfSQL) — root-ID functions for recursive FKs that the base view references. Must run before phase 1 or PG rejects the view with function does not exist. Phase 1 = view DDL (viewSQL) — may invoke provider-specific recovery. Phase 2 = CRUD function DDL — ONLY runs if phase 1 succeeded. Phase 3 = view permissions (viewPermSQL) — runs only if phase 2 succeeded. The success/phase pair in the result identifies exactly where things fell over so the caller doesn't have to bisect.

Executes a SQL file using the platform's native CLI tool (sqlcmd/psql). The implementation is responsible for reading connection configuration from the environment or config objects.

Formats a default value for use in generated SQL. Handles SQL functions (GETUTCDATE, gen_random_uuid, etc.) and literal values.

Generates a comment header for the combined all-entities SQL file.

Generates the complete base view DDL for an entity, including the DROP guard. The orchestrator provides pre-computed context (related fields, joins, parent joins, etc.) so the provider only needs to assemble the platform-specific SQL.

Generates the CREATE stored procedure or function for an entity. SQL Server: CREATE PROCEDURE [schema].[spCreate...] PostgreSQL: CREATE OR REPLACE FUNCTION schema.fn_create_...()

Generates the DELETE stored procedure or function for an entity. Handles both hard and soft delete types, and includes cascade delete logic.

Generates the parameter list string for a CRUD routine with tolerant SP signatures (Pillar 1 of the cross-app migration architecture).

Structural logic — what's required, what's optional, when a _Clear companion appears — lives here in the base class and is shared across dialects. The dialect-specific syntax (parameter prefix, default keyword, type rendering) is delegated to:

• Dialect.ParameterRef(name) — @Name vs. p_name
• Dialect.ParameterDefault(value) — = NULL vs. DEFAULT NULL
• Dialect.NullLiteral — NULL literal
• renderParameterType(ef) — type formatting (T-SQL native vs. PG-mapped)

Subclasses can override the hooks above without re-implementing the full method. Subclasses can also override the method itself if their dialect imposes additional constraints not modelable through hooks (e.g. PostgreSQL's "all params after the first DEFAULT must also have DEFAULTs" rule, which the PostgreSQL provider handles via override).

Generates GRANT EXECUTE permission for a CRUD routine.

Generates the UPDATE stored procedure or function for an entity. Includes the updated-at trigger generation.

Generates a conditional DROP + CREATE guard for a database object. For SQL Server: IF OBJECT_ID(...) IS NOT NULL DROP ... For PostgreSQL: DROP ... IF EXISTS ... or CREATE OR REPLACE ...

Generates CREATE INDEX statements for all foreign key columns on an entity. Returns an array of individual index DDL strings.

This is a template method: every dialect-INDEPENDENT decision (which fields qualify as indexable foreign keys, and how the index name is composed and truncated) lives here so it cannot drift between providers. Dialects supply only the genuinely different pieces via formatIndexStatement, tableToken, columnToken, indexPrefix, and maxIdentifierLength.

Previously this was abstract, so each provider reimplemented the whole thing and they drifted in a way that was not dialect-specific — SQL Server omitted the primary-key/virtual-field exclusions that PostgreSQL had. See isIndexableForeignKey.

Generates full-text search infrastructure for an entity: SQL Server: FULLTEXT CATALOG + INDEX + inline TVF PostgreSQL: tsvector column + GIN index + trigger + search function

Generates GRANT EXECUTE permission for a full-text search function.

Generates the column-list or value-list portion of an INSERT statement, depending on whether prefix is empty (column names) or non-empty (parameter values).

Empty prefix — produces dialect-quoted column names suitable for the INSERT INTO ... (col1, col2) clause.

Non-empty prefix — produces the parameter-value list suitable for the VALUES (...) clause, with tolerant-SP behavior:

• Special-date fields are substituted with the dialect's CurrentTimestampUTC() (created/updated) or NullLiteral (deleted-at).
• GUID fields with database defaults emit a CASE that detects the empty-GUID sentinel and falls back to the database default; otherwise wraps with IsNull.
• Non-nullable fields with defaults are wrapped in IsNull(@Param, default).
• Nullable fields with non-NULL defaults emit a _Clear companion CASE so callers can distinguish "leave default" from "explicitly NULL."
• Plain nullable fields with no default pass the parameter reference through directly (NULL flows through).

Skips auto-increment, virtual, and non-updatable fields. The PK column can be optionally excluded (used by the two-branch GUID-PK insert pattern in generateCRUDCreate).

Dialect-specific syntax is fully delegated to:

• Dialect.QuoteIdentifier, Dialect.ParameterRef,
• Dialect.IsNull, Dialect.NullLiteral,
• Dialect.CurrentTimestampUTC, Dialect.EmptyUUIDLiteral,
• formatInsertDefaultValue(ef) render hook (for type-strict dialects that need to massage default values).

Generates the JOIN clause for a root ID function in a base view. SQL Server: OUTER APPLY [schema].[fnTable_GetRootID](t.ID) AS rootFn PostgreSQL: LEFT JOIN LATERAL schema.fn_table_get_root_id(t."ID") AS root_fn ON true

Generates the SELECT expression for a root ID field in a base view. SQL Server: rootFn.RootID AS [FieldRoot...] PostgreSQL: root_fn.root_id AS "FieldRoot..."

Generates a recursive root-ID function for a self-referencing FK field. SQL Server: inline TVF with recursive CTE + OUTER APPLY in view. PostgreSQL: scalar function with recursive CTE + LEFT JOIN LATERAL in view.

Generates the cascade delete/update SQL for a single related entity. Called by the orchestrator for each FK relationship when CascadeDeletes is true.

Generates a comment header for a generated SQL file.

Generates ALTER TABLE statements to add __mj_CreatedAt and __mj_UpdatedAt columns.

Generates the __mj_UpdatedAt timestamp trigger for an entity. SQL Server: single AFTER UPDATE trigger. PostgreSQL: companion function + BEFORE UPDATE trigger.

Generates the SET clause body for an UPDATE statement with tolerant merge semantics. Each non-PK column wraps the parameter with the dialect's null-coalescing call against the column's existing value (SET [Col] = ISNULL(@Param, [Col]) on SQL Server, SET "col" = COALESCE(p_col, "col") on PostgreSQL) so omitting a parameter preserves the existing row value. Nullable columns whose database default is non-NULL also emit a _Clear companion branch that lets callers distinguish "leave unchanged" from "explicitly NULL."

The full structural logic lives here in the base class; only the per-line render is dialect-specific and that's resolved through the Dialect accessor's helpers (QuoteIdentifier, ParameterRef, IsNull, NullLiteral). Subclasses can override to customize line formatting if a future dialect needs something different.

Generates GRANT SELECT permission for a base view.

Generates SQL to refresh/recompile a view. SQL Server: EXEC sp_refreshview 'schema.viewName'; PostgreSQL: returns empty string (no-op).

Generates a simple test query to validate a view is functional. SQL Server: SELECT TOP 1 * FROM [schema].[viewName] PostgreSQL: SELECT * FROM "schema"."viewName" LIMIT 1

Returns an additional WHERE clause fragment for the check-constraints query. SQL Server: WHERE SchemaName NOT IN (...) when excludeSchemas is provided. PostgreSQL: empty string (the PG view already handles schema filtering).

Returns a SQL query string to check if a given column is part of a composite unique constraint. The query should accept the schema, table, and column name as parameters (platform-specific).

The orchestrator checks result.recordset.length > 0 to determine if the column participates in a multi-column unique index.

Note: Implementations should return the SQL query string. The orchestrator is responsible for executing the query with proper parameterization for the target database platform.

Returns the name of the CRUD routine for an entity. SQL Server: spCreateEntityName PostgreSQL: fn_create_entity_name

Returns an additional WHERE clause fragment for the missing-base-tables query. SQL Server: WHERE VirtualEntity=0 PostgreSQL: empty string (PG query doesn't need this filter).

Generates SQL to fix virtual field nullability after metadata sync. PostgreSQL: Updates AllowsNull for virtual fields based on the FK column's nullability. SQL Server: returns empty string (no fix needed).

Returns a SQL query string to check if a foreign key index already exists. Used by the orchestrator to conditionally create FK indexes. SQL Server: queries sys.indexes with OBJECT_ID PostgreSQL: queries pg_indexes

The result set should return rows if the index exists (length > 0 means exists).

Returns DDL that creates/replaces the platform's metadata-management support objects (introspection views and the routines manage-metadata invokes via callRoutineSQL), or null when the platform ships them through migrations instead.

These objects are CodeGen's own machinery — only CodeGen calls them — so platforms that return DDL here get it executed (idempotently) at the start of every manageMetadata run. That guarantees the objects can never be missing or version-skewed relative to the CodeGenLib code that calls them.

SQL Server: returns null (objects ship in the baseline migrations). PostgreSQL: returns the full support-object DDL.

Generates the SQL to retrieve pending entity fields that exist in the database but not yet in the MJ metadata. This is a large, platform-specific query.

Returns a SQL query string to retrieve the primary key index name for a table. SQL Server: queries sys.indexes + sys.key_constraints PostgreSQL: queries pg_index + pg_class

The result set must include a column named IndexName.

Returns a SQL query that lists every stored procedure / function name in the given schemas. Used by the post-run CRUD validator to diff expected vs actual routine presence in one round trip.

The result set must contain exactly two columns: schema_name — the schema the routine lives in routine_name — the proc/function name as stored in the catalog

SQL Server: queries sys.objects for procedures and functions. PostgreSQL: queries pg_proc joined to pg_namespace.

Default: returns empty string. Providers that don't implement this opt out of the post-run CRUD validator (the validator returns no missing when this returns empty), preserving backwards compatibility for downstream subclasses that haven't been updated.

Returns an array of system schema names that should be excluded from metadata synchronization. Empty array if the platform has no system schemas that need excluding. SQL Server: [] (no system schemas need excluding) PostgreSQL: ['information_schema', 'pg_catalog', 'pg_toast', ...]

Returns SQL to get column metadata for a view or table. Result columns: FieldName, Type, Length, Precision, Scale, AllowsNull.

Returns a SQL query string to retrieve the current view definition from the database. SQL Server: SELECT OBJECT_DEFINITION(OBJECT_ID('[schema].[viewName]')) AS ViewDefinition PostgreSQL: SELECT pg_get_viewdef('"schema"."viewName"'::regclass, true) AS "ViewDefinition"

The result set must include a column named ViewDefinition.

Returns SQL to check if a view exists. The query uses @ViewName and @SchemaName as named parameters. Returns 1 row if the view exists.

Parses a raw default-value string from the database catalog into a clean value. SQL Server: strips wrapping parens and N'' prefix, e.g. (getdate()) → getdate() PostgreSQL: strips ::type casts, recognizes nextval() as auto-increment (returns null).

Quotes mixed-case identifiers in a raw SQL string for execution. SQL Server: returns the SQL unchanged (case-insensitive identifiers). PostgreSQL: double-quotes PascalCase identifiers to preserve case.

Optional — dialect-specific fast path for regenerating a single entity's base view with recovery logic.

When provided, the orchestration layer (sql.ts regenerateFailedBaseViews) will route regeneration through this method instead of the generic write-temp-file-and-shell-out path. Implementations can add capture/ recovery behavior around the CREATE OR REPLACE VIEW — e.g. PG's 42P16 capture-and-restore fallback that preserves dependent views, functions, grants, comments, and ownership across the unavoidable DROP CASCADE.

Set up the per-platform data source for a CodeGen run: open a connection pool, configure the metadata provider, build the CodeGenConnection, and resolve the audit user.

Where this lives and why — the orchestrator (RunCodeGenBase) used to switch on configInfo.dbPlatform inline and call private setupSQLServerDataSource() / setupPostgreSQLDataSource() methods. That bypassed the existing factory pattern (see e.g. manage-metadata.ts's get dbProvider(), which dispatches via MJGlobal.Instance.ClassFactory.CreateInstance(CodeGenDatabaseProvider, platform)) and meant adding a third platform would require touching the orchestrator. Moving the setup here puts it behind the same factory: subclasses register via @RegisterClass(CodeGenDatabaseProvider, '<platform>') and the orchestrator just resolves + calls.

Subclasses are responsible for:

• opening their pool (typically via a module-cached helper such as MSSQLConnection() / PGConnection() so multiple setupDataSource() calls reuse the same pool)
• configuring + setting their metadata provider via SetProvider(...) when appropriate
• loading the audit user (currently SQL Server uses UserCache, PostgreSQL hand-queries vwUsers/vwUserRoles — see implementations for the deliberate asymmetry note)
• emitting spinner status via status_logging helpers when desired

Cross-checks the entity-level AllowAPI/spGenerated/sp* configuration against the routines actually present in the database after CodeGen finishes. Returns one entry per missing routine.

Why this exists: silent generation gaps (e.g. an entity dropped because an upstream batch errored, or stale entity-field metadata causing the PK check to fail) historically reported success at the pipeline level while leaving runtime CRUD broken. This validator turns that into a loud, actionable failure list before the install pipeline exits.

What's expected per entity:

• Skip virtual entities (no DB-backed routines).
• For each of Create / Update / Delete:
  • Skip when the corresponding Allow{Type}API flag is false.
  • Look up the routine name via getCRUDRoutineName (which honors entity.spCreate/spUpdate/spDelete overrides; otherwise returns the dialect-generated default).
  • Report it as missing when not found in the DB catalog.

Schema-level case sensitivity: SQL Server is case-insensitive, PostgreSQL is case-preserving. Both lookups normalize via lowercase to keep the validator dialect-agnostic.

Default implementation works for both dialects via the getRoutineNamesBySchemaSQL helper. Providers may override to add platform-specific shortcuts (e.g. checking only sys.procedures on SQL Server) but the default is fine for all current dialects.

Wraps an INSERT statement with a conditional existence check at the statement level. SQL Server: Adds IF NOT EXISTS (...) BEGIN prefix and END suffix. PostgreSQL: Adds ON CONFLICT DO NOTHING suffix (no prefix needed).