Multi-Database Dialect Support

Purpose and Scope

APIJSON provides robust multi-database dialect support, allowing a single query protocol to target relational, non-relational, search, and time-series databases. This page details the architectural implementation of database identification constants, dialect flag methods defined in the SQLConfig interface, dialect-specific SQL generation strategies, and advanced multi-compatibility handling via KingbaseSQLDialect.

1. Database Constants and Dialect Flags (SQLConfig)

The SQLConfig interface defines standardized string constants for supported database engines and boolean accessor methods to check the active dialect configuration at runtime.

Database identifiers such as DATABASE_MYSQL, DATABASE_POSTGRESQL, DATABASE_ORACLE, and specialized variants like DATABASE_KINGBASE_MYSQL are enumerated in APIJSONORM/src/main/java/apijson/orm/SQLConfig.java20-59.

To inspect or condition SQL generation on the target database, implementations provide dialect flag methods (isMySQL(), isPostgreSQL(), isKingBase(), etc.) defined in [APIJSONORM/src/main/java/apijson/orm/SQLConfig.java:87-130].

Figure 1: Mapping natural database targets to SQLConfig constants and evaluation methods.

Sources: APIJSONORM/src/main/java/apijson/orm/SQLConfig.java20-130

2. Dialect-Specific Pagination, Upsert, and Formatting

Different SQL dialects manage query pagination, limit clauses in Data Manipulation Language (DML) statements, regular expression functions, and identifier quoting rules differently. AbstractSQLConfig and concrete implementations adapt SQL generation based on the flags returned by SQLConfig methods.

Key dialect-dependent variations include:

• Identifier Quoting: MySQL uses backticks (`), while PostgreSQL and SQL Server typically use double quotes (").
• DML Limits: Some dialects support LIMIT clauses on UPDATE and DELETE statements (e.g., MySQL syntax), while others require subquery or window-based approaches.
• Pagination Clauses: Standard LIMIT / OFFSET versus Oracle's ROWNUM nesting or SQL Server's OFFSET ... ROWS FETCH NEXT ... ROWS ONLY.

Sources: APIJSONORM/src/main/java/apijson/orm/SQLConfig.java20-130, APIJSONORM/src/main/java/apijson/orm/KingbaseSQLDialect.java8-86

3. KingbaseES Multi-Compatibility Support (KingbaseSQLDialect)

KingbaseES is a relational database management system that provides multiple compatibility modes (MySQL, Oracle, and SQL Server). APIJSON encapsulates these capabilities using the KingbaseSQLDialect enum located at APIJSONORM/src/main/java/apijson/orm/KingbaseSQLDialect.java8-86.

The enum defines five distinct dialect states:

• NONE: Not a Kingbase database.
• LEGACY: Standard Kingbase mode (APIJSONORM/src/main/java/apijson/orm/KingbaseSQLDialect.java10).
• MYSQL: Kingbase running in MySQL compatibility mode, utilizing backtick identifiers and supporting DML limits (APIJSONORM/src/main/java/apijson/orm/KingbaseSQLDialect.java11).
• ORACLE: Kingbase running in Oracle compatibility mode (APIJSONORM/src/main/java/apijson/orm/KingbaseSQLDialect.java12).
• SQLSERVER: Kingbase running in SQL Server compatibility mode (APIJSONORM/src/main/java/apijson/orm/KingbaseSQLDialect.java13).

Figure 2: Resolution flow for KingbaseES compatibility modes and SQL formatting functions.

Methods on KingbaseSQLDialect dictate how clauses are constructed:

• getSelectLimit(int offset, int count): Returns SQL Server-specific OFFSET ... ROWS FETCH NEXT ... ROWS ONLY syntax when operating in SQL Server compatibility mode (APIJSONORM/src/main/java/apijson/orm/KingbaseSQLDialect.java63-68).
• getExplainPrefix(): Returns EXPLAIN PLAN FOR for Oracle mode and EXPLAIN for general Kingbase modes (APIJSONORM/src/main/java/apijson/orm/KingbaseSQLDialect.java76-81).

Sources: APIJSONORM/src/main/java/apijson/orm/KingbaseSQLDialect.java8-86

4. Testing and Verification

Multi-database dialect logic and compatibility modes are rigorously verified via unit tests. For example, KingbaseCompatibilityTest validates correct parsing of database compatibility strings, identifier quotes, regex string generations, and full CRUD statement generation for Kingbase variants.

Tests such as recognizesAllCompatibilityModes() and generatesKingbaseMySQLCrudDialect() verify that SQLConfig and KingbaseSQLDialect correctly output dialect-specific SQL strings (APIJSONORM/src/test/java/apijson/orm/KingbaseCompatibilityTest.java109-179).

Sources: APIJSONORM/src/test/java/apijson/orm/KingbaseCompatibilityTest.java109-179