A data query adaptation method, system and electronic equipment compatible with wide and narrow table dual-mode storage

Abstract

The application relates to the field of computer software, and provides a data query adaptation method and system compatible with wide-narrow table dual-mode storage and electronic equipment, the system of which comprises a database query compatibility adaptation module, a parser traverses a table reference list of a custom query statement to extract a table reference, corresponding wide-narrow table branch processing is performed on the custom query statement according to the storage mode of a table single entity corresponding to the table reference, when the narrow table storage mode is adopted, the table reference is replaced by a physical table name of the narrow table to obtain a final custom query statement, when the wide table storage mode is adopted, a system common table expression in which general columns are mapped into business column names is generated according to the field definition of a table single entity corresponding to the table reference, the system common table expression is injected into the custom query statement for updating, and the final custom query statement is executed. Wide tables and narrow tables are simultaneously supported in the storage modes, wide tables and narrow tables are simultaneously processed in the same custom query statement, and data query of the wide-narrow table dual-mode storage is realized.

Description

Technical Field

[0001] This application relates to the field of computer software, and in particular to a data query adaptation method, system, and electronic device compatible with wide and narrow table dual-mode storage. Background Technology

[0002] Low-code development platforms, as efficient reference building tools, allow users to define forms (i.e., business data models) through visual modeling. Each form can contain several custom fields, and the number, type, and structure of the fields can be flexibly adjusted at runtime, greatly reducing the threshold for reference development. They have been widely used in computer software, enterprise management, data processing, and many other fields.

[0003] To adapt to the dynamic data storage needs of multiple users and multiple forms, mainstream low-code platforms in the industry (such as Salesforce) generally adopt a "shared wide table" storage solution (see US patents US8112445B2 and US9092501B2). The core of this solution is to build a shared data table containing a large number of common columns. All user-defined form data is stored in this table, and the specific fields of the form are mapped to the common columns of the shared table through metadata. This solution does not require creating a separate physical table for each form, has extremely strong storage flexibility, and can efficiently support the dynamic form management needs in a SaaS multi-tenant environment, thus it is widely used in the industry. However, the single architecture of "shared wide table" does not support the migration of form data from the shared wide table to an independent narrow table, and there is no scenario where wide and narrow tables coexist. Therefore, it does not address the query adaptation problem under a hybrid storage mode and cannot meet the storage optimization needs of a private environment.

[0004] Current mainstream ORM (Object-Relational Mapping) frameworks only support mapping relationships between objects and single-type data tables (wide or narrow tables). They cannot handle mixed reference scenarios of wide tables (which require mapping of common columns to business fields through metadata) and narrow tables (directly using physical column names) within the same query, causing cross-table queries under hybrid storage architectures to fail. Even if virtual views are created on top of wide tables to simulate narrow table structures, the view definition is static and fixed. It cannot dynamically switch query objects at runtime based on the storage mode state of the form entities (wide or narrow table storage), and cannot automatically determine whether to generate a virtual view to adapt to wide table queries or directly reference physical table names to adapt to narrow table queries, lacking dynamic adaptation capabilities.

[0005] Therefore, existing low-code platform storage and query technologies cannot meet the query compatibility requirements when wide and narrow tables coexist in private deployment scenarios. This leads to problems such as query failures, code refactoring, and performance degradation when switching deployment environments, limiting the deployment flexibility and application scope of low-code platforms. Therefore, there is an urgent need for a query processing solution that can achieve transparency in the underlying storage model and automatically adapt to mixed wide and narrow table scenarios. Summary of the Invention

[0006] This application provides a data query adaptation method, system, and electronic device that is compatible with both wide and narrow table dual-mode storage. It supports both wide and narrow table storage modes, processes both wide and narrow tables in the same custom query statement, generates a system-shared table expression for the wide table, and generates a physical table name for the narrow table, thereby realizing data querying with both wide and narrow table dual-mode storage.

[0007] The first aspect of this application provides a data query adaptation system compatible with both wide and narrow table dual-mode storage, the system comprising: The database query compatibility and adaptation module is used to obtain custom query statements from the business application layer. It extracts the table reference list, column reference set, table alias mapping set, and user-shared table expression name set by traversing the preprocessed custom query statements through the parser. It finds the corresponding form entity based on the business component identifier and form identifier of each table reference in the table reference list, and performs the corresponding wide and narrow table branch processing according to the storage mode defined by the form entity. Specifically, when the storage mode is a narrow table, the wide-narrow table branch processing includes mapping the table reference to the physical table name of the narrow table; when the storage mode is a wide table, the wide-narrow table branch processing includes dynamically constructing a system common table expression that maps the general columns of the wide table to the business semantic column names based on the field definitions of the form entity, and injecting the system common table expression definition into the custom query statement.

[0008] By employing the above technical solution, the parser identifies the table reference list, column reference set, table alias mapping set, and user-shared table expression name set in the custom query statement. The table reference list contains at least one table reference, which references form data from the low-code platform via a logical table. Based on the business component identifier and form identifier of each table reference in the table reference list, the corresponding form entity is found. According to the storage mode status flag of each form entity, the wide or narrow table branch is dynamically selected for processing at runtime. If the form entity's storage mode is narrow, the table reference is mapped to the narrow table's physical table name. If the form entity's storage mode is wide, a system-shared table expression, also known as a system CTE virtual view, is generated based on the form entity's field definitions to complete the mapping conversion from wide table general fields to business semantic fields. This ensures that the ultimately executed custom query statement can correctly access the underlying physical storage while remaining completely transparent to the user. Here, the form entity resides on the low-code platform.

[0009] When the storage mode is wide table, the system ensures query correctness through user-defined CTE virtual views. When the storage mode is narrow table, the system skips the generation of system-shared table expressions, and queries are executed directly on the physical tables, eliminating the query plan overhead caused by system-shared table expressions and improving the response speed of narrow table queries. Logical tables include physical tables in narrow table storage mode and user-shared table expressions in wide table storage mode. The format of logical table names is "Business Component Identifier".Form Identifier".

[0010] Optionally, before traversing the custom query statement through the parser, the colon prefix of the named parameters of the custom query statement is replaced with a placeholder without special characters. Before the custom query statement is executed, the placeholder is restored to the colon prefix of the named parameters. The table reference list includes all table references extracted from the FROM and JOIN clauses; the column reference set includes the referenced column names extracted from the SELECT clause; the table alias mapping set includes the mapping between table aliases and canonical table names extracted from the AS clause; and the user-shared table expression name set includes the defined user-shared table expression names extracted from the WITH clause.

[0011] By employing the above technical solution, the colon prefix in the named parameters is replaced with a placeholder through preprocessing, preventing the parser from misinterpreting the colon prefix as a syntax element and ensuring the correctness of subsequent parsing. Simultaneously, this temporarily replaced placeholder is restored to the original named parameter format before executing the custom query statement. The parser then parses the custom query statement into an abstract syntax tree, and the accessor traverses the abstract syntax tree to extract the table reference list, column reference set, table alias mapping set, and user-shared table expression name set.

[0012] Optionally, based on the field definitions of the form entities, dynamically construct system-shared table expressions that map wide table generic columns to business semantic column names, including: Use table aliases to qualify the selected basic system fields; Generate column alias mappings for plain text fields, generate type conversion expressions that include null values ​​and empty strings for numeric fields, generate mappings between value columns and pointer label columns for reference fields, generate left joins with auxiliary text storage tables for long text fields, and generate mappings between value columns and date label columns for date fields. Add filtering conditions to ensure that the system's shared table expressions only contain data from the current form; Use the format "Business Component Identifier_Form Identifier" to name system-shared table expressions.

[0013] By adopting the above technical solutions, the generation of the system's CTE virtual view (which maps the general columns of wide tables to the system's shared table expressions with business semantic column names) automatically handles a variety of complex mappings unique to low-code platforms: VARCHAR to DECIMAL conversion for numeric types (including safe handling of null values ​​and empty strings), cross-table LEFT JOIN joins for long text fields, two-column mapping of value columns and pointer label columns for reference-type fields, two-column mapping of value columns and date label columns for date-type fields, and column selection optimization (mapping only the columns actually referenced by the user). The automated processing of these mapping logics makes storage mode switching completely transparent to the upper layer.

[0014] Optionally, system-shared table expression definitions can be injected into custom query statements, including: Replace all table references in the custom query statement with system-shared table expression names; If the custom query statement does not contain a WITH clause, add the "WITH" keyword and all generated system-shared table expression definitions before the custom query statement; if the custom query statement contains a WITH clause, insert the system-shared table expression definitions before the user-shared table expression definitions of the WITH clause.

[0015] By adopting the above technical solution, the field definitions in the form entities corresponding to each table reference in the table reference list are used to construct the generated system shared table expression (also known as the system CTE virtual view). The user shared table expression (i.e., the user CTE virtual view) definition generated based on the WITH clause is inserted before the shared table expression (i.e., the system CTE virtual view) definition to ensure that the two coexist without any conflicts in the same WITH clause, thus guaranteeing the conflict-free compatibility of the shared table expression.

[0016] Optionally, the system may also include: The query token mapping module is used to obtain platform query statements from the business application layer and map the field tokens in the platform query statements to the physical column names corresponding to the storage mode based on the field type. Field tokens include filtering components, sorting components, search components, column selection components, aggregation components, and grouping components. Field types include ordinary fields, cascading selection fields, tree selection fields, single-selection reference fields, multi-selection reference fields, long text fields, and system reserved fields.

[0017] By adopting the above technical solution, the query token mapping module covers six types of query operations: filtering, sorting, searching, aggregation, grouping, and column selection. It employs a differentiated mapping strategy for different fields (ordinary fields, cascading selection fields, tree selection fields, single-selection reference fields, multi-selection reference fields, long text fields, and system-reserved fields) to ensure that all query operations can be executed correctly in both wide and narrow table modes.

[0018] Optionally, the system may also include: The table structure evolution module is used to convert camelCase naming to snakeCase naming after environmental checks and table name validation are passed when narrow table conversion is triggered. It establishes a mapping between field types and database types, generates mappings between value columns and reference label columns for reference-type fields, and between value columns and date label columns for date-type fields. It executes the data definition language through the database change management engine, updates the three-level status flags of form entities, and adjusts the storage mode to narrow table when independent tables are enabled, narrow table mode is enabled, or a narrow table has been created. The execution of the data definition language through the database change management engine includes generating a table creation script when the table does not exist, generating an incremental script for adding columns from the existing column set when the table exists, and adding a prefix to the snakeCase naming when it conflicts with the database's reserved keywords.

[0019] By adopting the above technical solution, the table structure evolution module dynamically creates or updates the physical structure of the narrow table at runtime based on the form field definitions.

[0020] Optionally, the system may also include: The dual-mode CRUD engine module identifies the storage mode, three-level status flags, and physical table name of the form entity corresponding to an INSERT or DELETE statement, dynamically switching between wide and narrow table storage paths. If the three-level status flag indicates that an independent table is started, the narrow table name is returned as the physical table name; otherwise, the shared wide table name is returned. In narrow table storage mode, standardized storage fields are used as parameter keys to write data to metadata fields; in wide table storage mode, storage fields are used as parameter keys to write data to metadata fields. Metadata fields in different storage modes have the same column names; and / or... The multi-tenant data source routing module is used to switch to the corresponding database of a tenant based on the tenant project identifier. It determines the physical data table to be accessed based on the independent identifier and independent table name of the form entity. The physical data tables in the database include shared wide tables, independent wide tables, and narrow tables.

[0021] By adopting the above technical solutions, the dual-mode CRUD engine module dynamically switches between wide and narrow table storage paths based on the storage mode status flags of form entities. The multi-tenant data source routing module dynamically switches database connections based on the abstract routing data source and the tenant project identifier.

[0022] A second aspect of this application provides a data query adaptation method compatible with dual-mode storage of wide and narrow tables, the method comprising the following steps: Retrieve custom query statements from the business application layer; The parser parses the preprocessed custom query statement into an abstract syntax tree, and traverses the abstract syntax tree to extract the table reference list, column reference set, table alias mapping set, and user-shared table expression name set. For each table reference in the table reference list, query the form entity based on the business component identifier and form identifier in each table reference. Execute the corresponding wide or narrow table branch processing according to the storage mode defined in the form entity. When the storage mode is narrow table, map the table reference to the physical table name of the narrow table. When the storage mode is wide table, dynamically generate a system common table expression based on the field definition of the form entity. The system common table expression indicates that the wide table general column is mapped to the business semantic column name. Inject the shared table expression definition into the custom query statement; Execute the injected custom query and return the results.

[0023] Optionally, traversing the abstract syntax tree includes: distinguishing between identifiers and column name identifiers by maintaining the context state flags of the FROM clause; recursively accessing the query body corresponding to the WITH clause for user shared table expressions in the WITH clause, and adding the user shared table expressions to the shared table expression name set; and identifying identifiers in the ON condition of the JOIN clause as column names. Before the parser parses the custom query statement into an abstract syntax tree, the method also includes replacing the colon prefix of the named parameters of the custom query statement with a placeholder without special characters; before executing the injected custom query statement and returning the result, the method also includes restoring the placeholder to the named parameters containing colons. The method also includes: obtaining platform query statements from the business application layer, and mapping the field tokens in the platform query statements to physical column names corresponding to the storage mode based on the field type. The field tokens include filtering components, sorting components, search components, column selection components, aggregation components, and grouping components. The field types include ordinary fields, cascading selection fields, tree selection fields, single-selection reference fields, multi-selection reference fields, long text fields, and system reserved fields.

[0024] A third aspect of this application discloses an electronic device, including a processor and a memory, wherein the memory stores one or more computer programs, the one or more computer programs including instructions that, when executed by the electronic device, cause the electronic device to perform the aforementioned data query adaptation method for compatible wide and narrow table dual-mode storage. Attached Figure Description

[0025] Figure 1 This is a schematic diagram of the structure of a data query adaptation system compatible with both wide and narrow table dual-mode storage in the first embodiment of this application.

[0026] Figure 2This is a flowchart of the wide-narrow table branch processing A corresponding to the narrow table storage mode.

[0027] Figure 3 This is a flowchart of the wide and narrow table branch processing B corresponding to the wide table storage mode.

[0028] Figure 4 yes Figure 3 The flowchart for step 23.

[0029] Figure 5 yes Figure 3 The flowchart for step 24.

[0030] Figure 6 yes Figure 1 The flowchart shows the operation of the query token mapping module.

[0031] Figure 7 yes Figure 6 The flowchart for step 32.

[0032] Figure 8 yes Figure 1 A flowchart of the table structure evolution module during operation.

[0033] Figure 9 This is a flowchart of a data query adaptation method compatible with dual-mode storage of wide and narrow tables, provided by the third case of the second embodiment of this application.

[0034] Explanation of reference numerals in the attached figures: 10. Functional statement input module; 20. Database query compatibility and adaptation module; 30. Query token mapping module; 40. Dual-mode CRUD engine module; 50. Table structure evolution module; 60. Multi-tenant data source routing module; 70. Database; 71. Wide table; 72. Narrow table; 73. Auxiliary table; 80. Data query adaptation system. Detailed Implementation

[0035] The present application will be further described in detail below with reference to the accompanying drawings. It should be understood that the specific embodiments described herein are merely illustrative of the application and are not intended to limit the scope of the application.

[0036] In the following description, numerous specific details are set forth for illustrative purposes in order to provide a thorough understanding of the concept. As part of this specification, some of the accompanying drawings of this disclosure are block diagrams illustrating structures and devices to avoid complicating the disclosed principles. For clarity, not all features of the actual embodiment need to be described. References to “an embodiment” or “an embodiment” in this disclosure mean that a particular feature, structure, or characteristic described in connection with that embodiment is included in at least one embodiment, and multiple references to “an embodiment” or “an embodiment” should not be construed as necessarily referring to the same embodiment.

[0037] Unless explicitly defined, the terms “a,” “an,” and “the” are not intended to refer to a singular entity, but rather to include a general category whose specific examples can be used for illustration. Therefore, the use of the terms “a” or “an” can mean any number of at least one, including “a,” “one or more,” “at least one,” and “one or more.” The term “or” means any of the options and any combination of the options, including all options unless explicitly indicated that the options are mutually exclusive. The phrase “at least one of” when combined with a list of items refers to a single item in the list or any combination of items in the list. The phrase does not require all items listed unless explicitly defined as such.

[0038] The first embodiment of this application discloses a data query adaptation system compatible with both wide and narrow table dual-mode storage. (See reference...) Figure 1 As shown, the data query adaptation system 80 includes: The function statement input module 10 is used to input function statements in the application business layer. The function statements include query statements, add statements, delete statements and narrow table conversion statements. The custom query statements include custom query statements and platform query statements.

[0039] The database query compatibility and adaptation module 20, connected to the function statement input module 10, is used to extract the table reference list, column reference set, table alias mapping set, and user-shared table expression name set by traversing the custom query statement through the parser. Based on the business component identifier and form identifier of each table reference in the table reference list, it finds the corresponding form entity and executes the corresponding wide / narrow table branch processing according to the storage mode defined for each form entity. The wide / narrow table branch processing includes: mapping table references to the physical table name of the narrow table when the storage mode is narrow, and executing the custom query statement; and generating a system-shared table expression that maps the general columns of the wide table to business semantic column names based on the field definitions of the form entity when the storage mode is wide, injecting the system-shared table expression definition into the custom query statement, and executing the custom query statement.

[0040] The query token mapping module 30 is connected to the function statement input module 10. It constructs a mapping table tokenFieldMap from field tokens to field definitions, identifies the storage mode of the form entity corresponding to the platform query statement, and maps the field tokens in the platform query statement to the physical column names corresponding to the storage mode according to the field type. The field tokens include the filter component FilterModel, the sorting component SortModel, the search component SearchModel, the column selection component SelectColId, the aggregation component AggregationModel, and the grouping component GroupByModel. The field types include ordinary fields, cascading selection fields, tree selection fields, single-selection reference fields, multi-selection reference fields, long text fields, and system reserved fields.

[0041] The dual-mode CRUD engine module 40, connected to the function statement input module 10, identifies the storage mode, three-level status flag, and physical table name of the form entity corresponding to the add and / or delete statements. When the storage mode is a narrow table, it uses the normalizedStorageField as the parameter key to write the metadata fields from the add statement to the narrow table corresponding to the physical table name, or to delete the metadata fields from the narrow table corresponding to the physical table name. When the storage mode is a wide table, it uses the storageField as the parameter key to write the metadata fields from the add statement to the narrow table corresponding to the physical table name, or to delete the metadata fields from the narrow table corresponding to the physical table name. The metadata fields in different storage modes have the same column name. If the three-level status flag indicates that an independent table is started (and the current environment check allows it), the narrow table name is returned as the physical table name; otherwise, the shared wide table name "form_entity_data" is returned as the physical table name. Standardized storage fields include "employee_name", storage fields include "value1", and metadata fields include form_entity_id, deleted, created_time, etc.

[0042] The table structure evolution module 50, located in the data storage layer and connected to the function statement input module 10, triggers narrow table conversion based on the narrow table conversion statement. It determines that the environment check passes when the read configuration switch indicates that the current environment supports creating a narrow table, and that the table name verification passes when there is no conflict between the target table name and the reserved table name. It converts camelCase naming in the original statement indicated by the narrow table conversion statement to snakeCase naming. When snakeCase naming conflicts with reserved keywords, it adds "ceta_pre_" as a prefix to the conflicting snakeCase naming. It establishes a mapping between field types and database types, generates a mapping between value columns and pointer label columns for reference-type fields, and generates a mapping between value columns and date label columns for date-type fields. It executes the Data Definition Language (DDL) through the database change management engine, updating the three-level status flags of the form entity corresponding to the target table name. When the three-level status flags indicate that independent table is enabled, narrow table mode is enabled, or narrow table has been created, the storage mode is adjusted to narrow table. The execution of the Data Definition Language through the database change management engine includes generating a table creation script when the table does not exist, and generating an incremental script for adding columns from the existing column set when the table exists.

[0043] In one example, continue to refer to Figure 1 As shown, the data query adaptation system also includes a multi-tenant data source routing module 60. The function statement input module 10 is also used to input tenant information at the business application layer. The multi-tenant data source routing module 60 is located in the data storage layer and is connected to the function statement input module 10. It is used to switch to the corresponding database 70 according to the tenant project identifier in the tenant information, and to determine the accessed physical data table according to the independent identifier and independent table name of the form entity. The physical data tables in the database 70 include at least one of a wide table 71 and a narrow table 72. The wide table 71 includes a shared wide table and an independent wide table.

[0044] Specifically, the database types supported in this application include PostgreSQL, MySQL, SQL Server, and SAP HANA. All of these databases 70 support dynamic data source switching because the switching is implemented at the application layer (Java Spring framework) through AbstractRoutingDataSource, without relying on any special features of the database itself. Based on SpringAbstractRoutingDataSource, database connections are dynamically switched according to the tenant project token.

[0045] The impact of database switching on various aspects includes: First, there is no impact on user query functionality. The database query compatibility and adaptation module 20 will automatically adjust the processing path (generating CTEs or replacing table names) according to the switched storage mode, and the query token mapping module 30 will automatically map to the correct physical column names according to the switched storage mode. Second, data is affected. Data in the original database is migrated to the new database. During the migration process, the system uses the mapping relationship between the storageField (wide table column name) and normalizedStorageField (narrow table column name) defined in the field definition to write data from general columns (such as value1) to semantic columns (such as employee_name), thus migrating the data of the corresponding form in the wide table to the newly created narrow table. Third, performance may be affected. If switching from a wide table to a narrow table, query performance is improved because the narrow table skips the intermediate steps of system CTE virtual view generation and type conversion during runtime.

[0046] In the tenant-specific database schema, wide tables and narrow tables are created. Wide tables support table partitioning and include two types: the shared wide table `form_entity_data` and independent wide tables. The shared wide table `form_entity_data` is a base table automatically created by the database migration script during system initialization. All form data that is not enabled for independent tables is stored in this shared wide table by default, and each tenant's dedicated database contains one copy.

[0047] Independent wide tables are used as table partitions. When the `standalone` flag of a form's independent component is set to 1, but the `standaloneTableNormalized` flag of the independent table structure is 0, the system will create an independent wide table for that form. This creation method clones the structure of the shared wide table, generating a table with the exact same structure but physically independent. For example, cloning the shared wide table structure can be done using "SELECT * INTO {standaloneTableName} FROM form_entity_data WHERE id= -10000" to copy the table structure, resulting in a table like `hr_employee_data` with the exact same structure but physically independent. The goal is to separate frequently accessed form data from the shared wide table, reducing the data volume of the shared wide table and improving query performance.

[0048] Therefore, a tenant's database 70 may simultaneously contain a shared wide table (storing form data that is not partitioned), several independent wide tables (partitioned, with the same structure as the shared wide table), and several narrow tables (structured using semantic column names). The system dynamically determines which physical table to access based on the form entity's standalone flag and standaloneTableName property using the getTableName method.

[0049] The multi-tenant data source routing module 60 is based on Spring AbstractRoutingDataSource and dynamically switches database connections based on the tenant project identifier (projectToken). The data source uses local caching management (maximum 1000 entries, expiring in 100 seconds), supporting connection parameter change detection and automatic connection pool updates. Connection parameters refer to the configuration information required for database connections, including the database address (JDBC URL), username, and password. These parameters determine which database the application connects to. Connection parameter changes are detected by comparing the cached connection parameters with the newly configured parameters. Connection parameter changes occur when a platform administrator modifies the data source configuration of a tenant project in the management interface, such as changing the database server address, changing the database password, or switching to a different server. A connection pool is a database connection management mechanism, such as the HikariCP connection pool. Since creating a database connection is a time-consuming operation, the connection pool pre-creates and maintains a set of database connections. When an application needs to access the database, it borrows a connection from the pool and returns it to the pool for reuse after use, avoiding the performance overhead of frequently creating and destroying connections. Each tenant project corresponds to an independent connection pool instance. In one example, the database address is "jdbc:postgresql: / / host:5432 / dbname?currentSchema=tenant_a", and the password is stored using AES encryption.

[0050] In one example, the function statement input module 10 inputs tenant information B, a custom query statement, a platform query statement, an add statement, and a delete statement. Then, the multi-tenant data source routing module 60 selects database 70 based on tenant information B. The database query compatibility and adaptation module 20 queries the target form entities in database 70 selected by the multi-tenant data source routing module 60 based on the custom query statement. The query token mapping module 30 queries the target form entities in database 70 selected by the multi-tenant data source routing module 60 based on the platform query statement. The dual-mode CRUD engine module 40 adds or deletes the target form entities in database 70 selected by the multi-tenant data source routing module 60 based on the add and delete statements.

[0051] The function statement input module 10 inputs custom query statements, platform query statements, add statements, and delete statements. Then, the database query compatibility and adaptation module 20 searches for the target form entities in all databases 70 based on the custom query statements and the multi-tenant data source routing module 60. The query token mapping module 30 searches for the target form entities in all databases 70 based on the platform query statements. The dual-mode CRUD engine module 40 adds or deletes the target form entities in all databases 70 based on the add and delete statements.

[0052] When no tenant information is configured, the database query compatibility and adaptation module 20, the query token mapping module 30, and the dual-mode CRUD engine module 40 operate on all databases 70 in the data storage layer, the wide table 71 outside database 70, and the narrow table 72 outside database 72. The data storage layer also includes an auxiliary table 73, which stores long text content from custom query statements, platform query statements, insert statements, and delete statements. The auxiliary table 73 can exist independently or be set within database 70.

[0053] In one example, before traversing the custom query statement through the parser, the database query compatibility module 20 finds named parameters containing colons in the custom query statement, such as ":employeeId". It then replaces the colon prefix of the named parameters in the custom query statement with a placeholder that does not contain special characters, for example, replacing ":employeeId" with "query_parameter_employeeId". Correspondingly, before the database query compatibility module 20 executes the updated custom query statement, the temporarily replaced placeholder is restored to the colon prefix of the named parameters.

[0054] This example prevents the colon prefix of named parameters from being misidentified as a syntax element by the SQL parser, ensuring the correctness of subsequent SQL parsing.

[0055] In one example, the database query compatibility adaptation module 20 extracts the table reference list, column reference set, table alias mapping set, and user-shared table expression name set by traversing the custom query statement through the parser. The specific process includes: The Apache Calcite SQL parser is used to parse custom query statements into an Abstract Syntax Tree (AST). The AST accessor, inheriting from the SqlShuttle class, traverses the AST to extract the following four types of information: (a) Table Reference List: Extracts all table references from the FROM and JOIN clauses of the custom query statement, in the format "schema.table" (e.g., "hr.employee"). The AST accessor distinguishes between identifiers (i.e., table names) in the FROM clause and identifiers (i.e., column names) in the SELECT / WHERE clause by maintaining the inFromClause status flag, thus avoiding misidentifying column names as table names.

[0056] (b) Column Reference Set: Extracts the referenced column names from the SELECT clause of the custom query statement. Column names include simple column names, qualified column names, and wildcards. Simple column names are, for example, "salary"; qualified column names also record the mapping relationship between qualifiers and column names, such as "e.salary"; wildcards are, for example, " "and"e. ".

[0057] (c) Table Alias ​​Mapping: Extracts the mapping relationship between table aliases and canonical table names from the AS clause of the custom query statement. Table alias mapping is used to subsequently associate qualified column names with the correct tables, such as mapping "e" to "hr.employee".

[0058] (d) Set of User-Shared Table Expression Names: Extract the names of user-defined user-shared table expressions, i.e., user CTE virtual view names, from the WITH clause of the custom query statement.

[0059] In addition, the AST accessor performs security checks: it rejects DDL statements (CREATE, ALTER, DROP, etc.) and data modification statements (INSERT, UPDATE, DELETE, etc.), and only allows SELECT queries.

[0060] In one example, the database query compatibility adaptation module 20 finds the corresponding form entity based on the business component identifier and form identifier of each table reference in the table reference list. Specifically, it extracts each table reference from the table reference list, parses the business component identifier schema and form identifier table of the table reference to find the corresponding form entity.

[0061] If the function statement input module 10 also inputs tenant information, the multi-tenant data source routing module 60 selects the database corresponding to the tenant information, and the database query compatibility and adaptation module 20 finds the corresponding form entity from the selected database 70 according to the business component identifier and form identifier of each table referenced in the table reference list.

[0062] In one example, the database query compatibility adaptation module 20 executes the corresponding wide and narrow table branching processing based on the storage mode defined for each form entity. (See reference) Figure 2As shown, step 21 is executed when the storage mode of the form entity definition is a narrow table, and step 22 is executed when the storage mode of the form entity definition is a wide table.

[0063] When a table reference list in the same custom query contains multiple table references, different table references may be processed through different wide and narrow table branches. For example, "hr"."employee" is processed through the narrow table's wide-narrow table branch A, while "hr"."ticket" is processed through the wide table's wide-narrow table branch B. This allows the same custom query to simultaneously include physical table name replacements for narrow tables and system-shared table expressions for wide tables, enabling the system to process both modes within the same query.

[0064] refer to Figure 2 As shown, the processing steps for the wide-narrow table branch A corresponding to the narrow table storage mode are as follows: Step 21: Map the table reference directly to the physical table name of the narrow table and execute the custom query statement.

[0065] The table reference here refers to the virtual table name used in the user's query statement, in the format "business component identifier."form identifier" (such as "hr"."employee"). It is not the actual physical table name that exists in the database, but a logical naming convention defined by the low-code platform.

[0066] For example, "hr."employee" is mapped to "hr_employee". This branch does not generate a system-shared table expression (i.e., a system CTE virtual view); the query will be executed directly on the physical table. Here, "hr" is the identifier of the Platform Business Component (PBC), and "employee" is the identifier of a form within that business component. Users write user queries using these logical table names in the SQL SDK of the business application layer. The database query compatibility module 20 is responsible for resolving the table reference names and converting them into actual physical storage accesses—converting them to a system CTE virtual view in wide table mode (filtering and mapping column names from the shared wide table form_entity_data), and to a physical table name (such as hr_employee) in narrow table mode. Table references allow users to ignore the underlying physical storage structure.

[0067] Step 22: Replace the table reference with the physical table name to obtain the final custom query statement. Perform syntax and security checks on the final custom query statement. After the syntax and security checks pass, execute the final custom query statement and return the execution result.

[0068] refer to Figure 3As shown, the processing steps for the wide-narrow table branch B corresponding to the wide table storage mode are as follows: Step 23: Generate a system-shared table expression (i.e., a system CTE virtual view) based on the field definitions of the form entities. The system-shared table expression is used to map the common columns of the wide table to business semantic column names.

[0069] Step 24: Inject the system-shared table expression definition into the custom query statement.

[0070] Step 25: After the syntax and security checks pass, execute the custom query statement and return the execution result.

[0071] refer to Figure 4 As shown, the specific process of generating system-wide common table expressions based on the field definitions of form entities includes: Step 231: Select the basic system fields from the wide table corresponding to the form entity, using table aliases for qualification. The basic system fields include the wide table's id, deleted, form_entity_id, created_time, updated_time, and other system fields. Table aliases can be qualified as, for example, "fed.id".

[0072] Step 232: Iterate through the field definitions of the form entities and generate corresponding column mapping expressions based on the field types. The specific rules are as follows: (2.1) When the field definition of a form entity indicates that the field type is a plain text field, a simple column alias mapping is generated for the TEXT_BOX type field. For example, if the field Token is "employeeName" and the wide table storage field is "value1", then "fed.value1 as \"employeeName\"" is generated.

[0073] (2.2) When the field definition of a form entity indicates that the field type is a numeric field, a CAST expression containing null values ​​and empty strings will be generated for the NUMBER / NUMERIC type field.

[0074] (2.3) When the field definition of a form entity indicates that the field type is a reference field, the mapping between the value column and the label column of the SELECT or ACL type field is generated. If the reference field points to the id field of another form entity, an expression to convert CAST to BIGINT must also be added to the value column.

[0075] (2.4) When the field definition of a form entity indicates that the field type is a long text field, a left join relationship with the auxiliary text storage table is generated based on the long text field. When the storage mode is a wide table, the TEXTAREA type field (i.e., the long text content) is stored in a separate auxiliary table form_entity_data_text.

[0076] The auxiliary table, officially called the auxiliary text storage table, is a built-in system table automatically created by the Liquibase database migration script during system initialization in the low-code platform. It belongs to the platform's standard table structure and includes `form_entity_data_text` for storing long text and `form_entity_data_acl` for storing multiple-choice values. The table structure includes the following: `id` (primary key), `form_entity_data_id` (ID of the associated wide table record), `form_entity_id` (ID of the associated form entity), `form_entity_field_id` (ID of the associated field definition), and `long_text` (TEXT type, storing long text content). The auxiliary table is automatically created during platform deployment, requiring no manual user intervention. Whenever a user saves form data containing a long text field, the system automatically writes the long text content to this auxiliary table and establishes a connection with the main record of the wide table using `form_entity_data_id` and `form_entity_field_id`. For example, store the main form data fed in the text detail table fedt1 with a left join, and retrieve the text value corresponding to the specified field id: LEFT JOIN form_entity_data_text fedt1 ON fedt1.form_entity_data_id = fed.id AND fedt1.form_entity_field_id = {fieldId}, and map the long_text column in the auxiliary table to the field Token.

[0077] (2.5) When the field definition of a form entity indicates that the field type is a date field, the mapping between the value column and the date label column of the DATE type field is generated.

[0078] Step 233: Add a filter condition to ensure that the system shared table expression contains only the current form data. Add the filter condition "fed.form_entity_id={entityId}" to ensure that the system shared table expression (i.e., the system CTE virtual view) contains only the current form data. Optionally, add a deletion flag filter ("fed.deleted=0").

[0079] Step 234: Name the system-shared table expressions using the format "Business Component Identifier_Form Identifier". Name the system CTE virtual views using the format "Business Component Identifier_Form Identifier" (e.g., "hr_employee") to generate WHERE conditions. Ensure that multiple system CTE virtual views corresponding to the same custom query statement do not conflict due to naming issues.

[0080] Step 235: Filter column references from the column reference table to generate mapping expressions for executing custom queries. From at least one column reference in the column reference table, filter column references used in executing custom queries and generate mapping expressions based on these references, reducing unnecessary column calculations and JOIN operations.

[0081] refer to Figure 4 As shown, in step 24, the system-shared table expression definition is injected into the custom query statement to obtain the final custom query statement. The specific steps include: Step 241: Replace all table references in the custom query statement with the system-shared table expression name. The storage mode for the form entity corresponding to the table reference is wide table. The logical table name includes the system-shared table expression name in wide table storage mode and the physical table name in narrow table storage mode. The logical table name uses a case-insensitive string.

[0082] Step 242: Determine whether the custom query statement contains a WITH clause. If yes, proceed to step 243; otherwise, proceed to step 244.

[0083] Step 243: Insert the system common table expression definition (i.e., the system CTE virtual view definition) before the user common table expression definition (i.e., the user CTE virtual view definition) in the WITH clause and separate them with commas to obtain the final custom query statement.

[0084] The specific process of generating the final custom query statement is as follows: locate the first non-whitespace character position after the "WITH" keyword, insert the system common table expression definition and comma before this position, and realize the structure "WITH-system CTE virtual view definition-user CTE virtual view definition".

[0085] Step 244: Add the "WITH" keyword and the system common table expression definition (i.e., the system CTE virtual view definition) before the custom query statement to obtain the final custom query statement.

[0086] In step 25, after the syntax and security checks pass, the custom query statement is executed, and the execution result is returned. Specifically, this includes performing syntax and security checks on the final custom query statement, executing it after the checks pass, and returning the execution result. The purpose of the security check is to reject operations pre-defined as dangerous, such as DDL and DML operations. When executing the final custom query statement, the database driver binds the named parameters to their actual parameter values.

[0087] In summary, the workflow for database querying custom query statements is as follows: Receive user-defined query statements → Perform named parameter preprocessing → Traverse the custom query statements through a parser to extract the table reference list, column reference set, table alias mapping, and user-shared table expression name set → Determine the storage mode of the corresponding form entities for each table reference → For narrow table storage, directly map table references to the physical table name of the narrow table / For wide table storage, generate a system CTE virtual view based on the field definitions of the corresponding form entities for the table references → For narrow table storage, replace table references with the physical table name of the narrow table to obtain the final custom query statement / For wide table storage, if a WITH statement exists, insert the system CTE virtual view definition before the user CTE virtual view definition to obtain the final custom query statement / For wide table storage, if a WITH statement does not exist, add WITH and the system CTE virtual view definition before the custom query statement to obtain the final custom query statement → Parameter restoration → Syntax and security checks → Execute the final custom query statement → Return results.

[0088] In one example, the platform supports using a unified query payload object to describe query conditions and obtain platform query statements. The query payload object is also called a field token. The field token includes a filter component FilterModel, a sorting component SortModel, a search component SearchModel, a column selection component SelectColId, an aggregation component AggregationModel, and a grouping component GroupByModel. Each field token uses a field Token (such as "employeeName") to identify the target column.

[0089] refer to Figure 6 As shown, the query token mapping module 30 performs the following queries based on the platform query statement: Step 31: Construct a mapping table `tokenFieldMap` from field tokens to field definitions, and simultaneously identify the storage mode of the form entities corresponding to the platform query statements. Identify all table references in the platform query statements, determine the form entities corresponding to each table reference, and obtain the storage mode of each form entity. Execute step 32 for each table reference.

[0090] Step 32: Map the field tokens in the platform query statement to the physical column names corresponding to the storage modes based on the field type and the storage mode of the form entity.

[0091] The replacement of field tokens to physical column names is performed in parallel for six types of query components (including filtering, sorting, searching, column selection, aggregation, and grouping components). The replacement rules are differentiated based on field type and storage mode. Field type identification is achieved through the Boolean status flag `inFromClause` maintained by the AST accessor.

[0092] When the AST accessor enters a FROM clause node in a platform query statement, it sets `inFromClause` to `true`; when leaving the FROM clause and entering SELECT, WHERE, GROUP BY, HAVING, etc. clauses, it sets `inFromClause` to `false`. When the AST accessor encounters an identifier node, it checks the value of `inFromClause`: if it is `true`, the identifier is a table name and is added to the table reference list; if it is `false`, the identifier is a column name and is added to the column reference set. For the JOIN clause, the left and right child nodes of its table references are accessed in the context of `inFromClause=true`, while identifiers in the ON condition are accessed in the context of `inFromClause=false`, ensuring that column names in the JOIN condition are not misinterpreted as table names.

[0093] refer to Figure 7 As shown, the specific content of the differentiated processing includes: Step 321: For ordinary fields in the platform query statement, replace them with normalized storage fields or storage fields. If the storage mode is a narrow table, replace the ordinary field with a normalized storage field, such as "employee_name"; if the storage mode is a wide table, replace the ordinary field with a storage field, such as "value1".

[0094] Step 322: For the cascading selection field and tree selection field in the platform query statement, use the label column for searching and filtering. If the storage mode is a narrow table, replace the cascading selection field and tree selection field with "normalizedStorageField + _label suffix" (such as "region_label"); if the storage mode is a wide table, replace the cascading selection field and tree selection field with the corresponding labelN column.

[0095] Step 323: For single-select reference fields (SELECT / ACL and multiple=false) in the platform query statement, use the label column for searching and filtering. If the storage mode is a narrow table, replace the single-select reference field with "normalizedStorageField + _label suffix" (such as "region_label"); if the storage mode is a wide table, replace the cascading selection field and tree selection field with the corresponding labelN column.

[0096] Step 324: For multi-select reference fields (SELECT / ACL with multiple=true) in the platform query statement, point the multi-select reference fields to the label fields in the auxiliary table form_entity_data_acl, and achieve cross-table joins by setting special table aliases and table names. This applies to both wide and narrow table storage modes.

[0097] Auxiliary tables, used in the wide table storage model, are supplementary tables for storing special type field data. These include the long text auxiliary table `form_entity_data_text` and the multi-select reference auxiliary table `form_entity_data_acl`. The long text auxiliary table `form_entity_data_text` stores the content of `TEXTAREA` and `RICH_TEXT` type fields. Long text content is stored separately in this auxiliary table and is associated with records in the wide table through `form_entity_data_id` (the ID of the associated main table record) and `form_entity_field_id` (the ID of the associated field definition). Queries retrieve this information using a `LEFT JOIN` join. The multi-select reference auxiliary table `form_entity_data_acl` stores field data of type `SELECT / ACL` with `multiple=true` (multiple selection). Since a multi-select field may select multiple values, it cannot be stored using a single general column in the wide table; therefore, each selected value is stored as a row in the auxiliary table.

[0098] Step 325: For long text fields (TEXTAREA / RICH_TEXT) in the platform query statement, when the storage mode is a wide table, point the long text fields in the platform query statement to the long text fields in the auxiliary table form_entity_data_text; when the storage mode is a narrow table, directly represent the long text fields in the platform query statement using the TEXT type column in the narrow table. This is because long text in the narrow table is directly stored as column values, eliminating the need for cross-table joins.

[0099] Step 326: For the system-reserved fields (11 in total, including id, createdTime, userId, etc.) in the platform query statement, directly replace the system-reserved fields with fixed column names (such as "created_time") through a hard-coded mapping table.

[0100] Step 33: Set the sorting type for the sorting component.

[0101] Set the NUMBER type field in the platform query statement to numerical sorting and the DATE type field to date sorting.

[0102] The sorting component sorts the data rows in the query result set. When a user clicks on the header of a column (such as the "salary" column) on the platform interface, the platform query statement generated by the front end includes: a SortModel object, which contains colId (field token, such as "salary") and sort (sorting direction, such as "asc" ascending or "desc" descending).

[0103] The query token mapping module 30 replaces the colId in the platform query statement from the Token field to the physical column name (replaced with "value3" in wide table storage mode and "salary" in narrow table storage mode), and converts the replaced platform query statement into the final platform query statement with the ORDER BY clause (such as "ORDER BY value3 ASC" or "ORDER BY salary ASC"). Additionally, for NUMBER type fields, the sorting component sets the sort type to numeric sort (instead of the default string sort) to ensure that values ​​are sorted by size rather than lexicographical order; for DATE type fields, it sets the sort type to date sort.

[0104] Step 34: Pass the replaced platform query statement to the SQL generator to obtain the final platform query statement, and execute the final platform query statement.

[0105] It's worth noting that before proceeding to step 31, the platform query replaces named parameters with colons with placeholders, such as replacing ":employeeId" with "query_parameter_employeeId". This is a plain text-level string replacement operation designed to prevent the parser from misinterpreting colons as SQL syntax elements. Before executing the final platform query, the placeholders are restored to their original named parameter format, and then the named parameters are bound to their actual parameter values.

[0106] In one example, the dual-mode CRUD engine module 40 retrieves CRUD statements from the application business layer. These statements include insert statements, delete statements, and field type modification statements. An insert statement triggers the addition of a field, automatically assigning the next applicable general column number. It extracts the storage mode, third-level status flag, and physical table name of the form entity corresponding to the insert statement. When the storage mode is a narrow table, the normalizedStorageField is used as the parameter key, and the metadata fields from the insert statement are written to the column corresponding to the next general column number of the narrow table with the physical table name. Alternatively, when the storage mode is a wide table, the storageField is used as the parameter key, and the metadata from the insert statement is written to the wide table with the physical table name. It's worth noting that metadata fields in different storage modes have the same column name. If the third-level status flag indicates that only an independent table is started and the current environment allows it, the narrow table is returned as the physical table name; otherwise, the shared wide table name "form_entity_data" is returned.

[0107] A delete statement triggers the deletion of a field, releases the corresponding generic column number, and extracts the storage mode, third-level status flags, and physical table name of the form entity corresponding to the delete statement. When the storage mode is a narrow table, the metadata field corresponding to the generic column number in the narrow table corresponding to the physical table name is deleted; when the storage mode is a wide table, the metadata field corresponding to the generic column number in the wide table corresponding to the physical table name is deleted. If a user inputs a content modification statement into the application business layer, the modification statement is broken down into at least one of an add statement and a delete statement. For example, the modification statement "Change the target content 'a' of form entity A to 'b'" is broken down by the application business layer into a delete statement "Delete the target content 'a' of form entity A" and an add statement "Add 'b' to the target content area of ​​form entity A". During execution, the delete statement is executed first, followed by the add statement. This ensures that users can modify published form entities at any time in the form designer.

[0108] A field type modification statement triggers a field type modification, extracts the form entity corresponding to the table reference in the field type modification statement, and updates the field type attribute of the metadata field definition table within that form entity. These modifications take effect immediately at runtime, without requiring system downtime or service restart. For wide table storage mode, since the physical structure of the common columns (value1~valueN) remains unchanged, adding, deleting, and modifying fields only involves changes at the metadata level; for narrow table storage mode, incremental DDL automatically adds the corresponding physical columns to the narrow table. Here, DDL refers to database table structure definition statements.

[0109] In one example, table structure evolution module 50, also known as the dynamic schema evolution module, is responsible for dynamically creating or updating the physical structure of the narrow table at runtime based on the form field definitions. For the working process of table structure evolution module 50, please refer to [reference needed]. Figure 8 and Figure 9 As shown, it includes: Step 51: Obtain the narrow table conversion statement. The narrow table conversion statement can be input by the business application layer through the function statement input module 10, or it can be generated by the multi-tenant data source routing module 60 during the data migration process of switching databases, when migrating data from a wide table in the original database to a newly created narrow table in the new database.

[0110] Step 52: Perform an environment check on the target physical structure for narrow table conversion. Read the configuration switches; if the current environment of the target physical structure does not allow the creation of narrow tables, the process terminates; otherwise, the environment check passes.

[0111] Step 53: Verify the target table name against the reserved table names. Compare the target table name indicated by the target physical structure with more than 100 reserved table names maintained by the system. If there is a conflict between the table names, the process terminates; otherwise, the table name verification passes.

[0112] Step 54: Convert the field token to a column name.

[0113] Convert camelCase to snakeCase, for example, convert applicantName to applicant_name. Check the database column names generated after the name conversion (i.e., snakeCase names), and compare the column names with the pre-maintained set of database reserved keywords. If there is no conflict, proceed to the next step. If there is a conflict, add the prefix "ceta_pre_" to the conflicting snakeCase names.

[0114] A user created a field with the token "date" in the form designer. After camelCase to snakeCase naming, the column name remained "date". The system compared this column name with a pre-maintained set of database reserved keywords. This set of database reserved keywords includes SQL syntax keywords, data type keywords, and special value keywords. SQL syntax keywords include SELECT, FROM, and WHERE; data type keywords include DATE, TEXT, and JSON; and special value keywords include NULL, TRUE, FALSE, etc., totaling approximately 50 keywords. Because "date" is a database reserved keyword, the system automatically added the prefix "ceta_pre_", resulting in the column name "ceta_pre_date". This avoids syntax errors due to keyword conflicts when executing DDL or SQL queries.

[0115] Step 55: Establish the mapping between field types and database types. TEXT_BOX is mapped to NVARCHAR(200), NUMBER is mapped to NVARCHAR(100) (for string storage compatible with wide tables), TEXTAREA is mapped to TEXT, and so on.

[0116] Step 56: Generate a mapping between the value column and the execution label column for reference-type fields, and a mapping between the value column and the date label column for date-type fields.

[0117] Step 57: Execute the Data Definition Language (DDL) through the Database Change Management Engine. If the target table does not exist, generate a complete table creation script to create a new target table to store the new metadata; if the target table already exists, obtain the existing column set through the database metadata JDBCDatabaseMetaData, and generate only an incremental script for the new column to create a new column in the target table to store the new metadata.

[0118] Step 58: Update the three-level status flags of the form entity corresponding to the target table name. The three-level status flags include standalone enabled for independent tables, standaloneTableNormalized enabled for narrow table mode, and standaloneTableCreated created for narrow table. When all three flags are met (i.e., standalone enabled, narrow table mode enabled, and narrow table created are all satisfied), the storage mode is adjusted to narrow table, and the table structure evolution module 50 completes the storage of the form fields into the narrow table of the target table name.

[0119] The second embodiment of the present invention provides a data query adaptation method compatible with dual-mode storage of wide and narrow tables, applicable to a data query adaptation system compatible with dual-mode storage of wide and narrow tables in the first embodiment. The data query adaptation method includes: obtaining a custom query statement from the business application layer; parsing the custom query statement into an abstract syntax tree using a parser, traversing the abstract syntax tree to extract a table reference list, a column reference set, a table alias mapping set, and a set of user-shared table expression names; for each table reference in the table reference list, querying the form entity based on the business component identifier and form identifier in each table reference, executing the corresponding wide and narrow table branch processing according to the storage mode defined by the form entity, mapping the table reference to the physical table name of the narrow table when the storage mode is narrow table, and dynamically generating a system-shared table expression based on the field definition of the form entity when the storage mode is wide table, the system-shared table expression indicating the mapping of the wide table's general columns to business semantic column names; injecting the system-shared table expression definition into the custom query statement; executing the injected custom query statement and returning the result. This method is actually the working process of the database query compatibility adaptation module 20 in the first embodiment, and will not be described in detail here.

[0120] In the first case, database query adaptation was performed on form entities in a wide table storage mode using a custom query statement, referencing... Figure 9 As shown.

[0121] A company created an "Employee Information" form (pbcToken="hr", formToken="employee") in a SaaS environment, which includes the following fields: Name (TEXT_BOX, storageField=value1), Department (SELECT single selection, storageField=value2 / label2), Salary (NUMBER, storageField=value3), and Personal Profile (TEXTAREA, stored in the auxiliary table form_entity_data_text).

[0122] User-defined query statements: SELECT e."employeeName",e."department",e."salary",e."bio" FROM "hr"."employee" e WHERE e."salary">5000 ORDER BY e."employeeName" Database query compatibility adaptation module 20 performs the following processing: S1: Preprocess the named parameters (skip this example if there are no named parameters).

[0123] S2: The SQL parser extracts the table reference ["hr.employee"], column reference ["employeeName","department","salary","bio"], and alias mapping {e→hr.employee}.

[0124] S3: The query table references the form entity corresponding to "employee" and its storage mode is a wide table. isNormalizedTableCreated() returns false, indicating that a narrow table has not been created. Proceed to the wide / narrow table processing branch B.

[0125] B1: Generate a virtual CTE view for the system. employeeName(TEXT_BOX)→"fed.value1 as \"employeeName\"" department(SELECT)→"fed.value2 as \"department\"" + "fed.label2 as\"department_label\"" salary(NUMBER)→"CAST(CASE WHEN fed.value3='' OR fed.value3 IS NULLTHEN NULL ELSE fed.value3 END AS DECIMAL) as \"salary\"" bio(TEXTAREA)→LEFT JOIN form_entity_data_text,"fedt1.long_text as \"bio\"" B2: Insert the system CTE virtual view definition before the user CTE virtual view definition in the WITH clause, separated by commas, to obtain the final custom query statement: WITH "hr_employee" AS NOT MATERIALIZED( SELECTfed.id,fed.deleted,fed.form_entity_id,fed.created_time,fed.updated_time, fed.value1 as"employeeName", fed.value2 as"department", fed.label2 as"department_label", CAST(CASE WHEN fed.value3='' ORfed.value3ISNULL THEN NULL ELSE fed.value3 END ASDECIMAL)as"salary", fedt1.long_text as "bio" FROM form_entity_datafed LEFT JOIN form_entity_data_textfedt1 ON fedt1.form_entity_data_id=fed.id AND fedt1.form_entity_id=fed.form_entity_id AND fedt1.form_entity_field_id=42 WHERE fed.form_entity_id=100 ) SELECT e."employeeName",e."department",e."salary",e."bio" FROM "hr_employee" e WHERE e."salary">5000 ORDER BY e."employeeName" S4: Parameter Restoration (In this example, there are no named parameters, no preprocessing is performed, and parameter restoration is unnecessary; skip this step). This step performs syntax and security checks on the final custom query statement and then executes it. Syntax checking verifies the correctness of the SQL syntax in the custom query statement using the SQL parser. Security checking detects whether the SQL contains DDL statements such as CREATE, ALTER, and DROP, or DML statements such as INSERT, UPDATE, and DELETE, as well as dangerous keywords such as EXEC, EXECUTE, UNION, and SCRIPT. If detected, execution is refused and an exception is thrown. Security checks are placed after parameter restoration because it's necessary to inspect the final complete SQL text to ensure that the restored SQL does not contain any dangerous operations. Users do not need to know that a wide table storage is used underneath.

[0126] In the second case, database query adaptation is performed on form entities in the narrow table storage mode using a custom query statement. The same form has been switched to a narrow table (hr_employee) in the private environment, and isNormalizedTableCreated() returns true. The processing procedure of the database query compatibility adaptation module 20 for the same custom query statement differs from that in the first case in the following ways: S3: Enter the wide and narrow table processing branch A, and directly map "hr"."employee" to the physical table name "hr_employee".

[0127] A: No system CTE virtual view is generated; only table name replacement is performed to obtain the final custom query statement: SELECT e."employeeName",e."department",e."salary",e."bio" FROM hr_employeee WHERE e."salary">5000 ORDER BY e."employeeName" Compared to the first case above, the narrow table mode skips all intermediate processing such as CTE generation, type conversion, and long text JOIN, and the query is executed directly on the physical table, resulting in a significant performance improvement.

[0128] In the third case, the table references in the custom query statement correspond to both wide and narrow table storage modes. A mixed wide and narrow table query is performed using the custom query statement. (See reference...) Figure 9 As shown.

[0129] The "Employee Information" form has been switched to a narrow table, while the "Work Order" form still uses a wide table. Users can write cross-table JOIN queries: SELECT e."employeeName",t."title",t."priority" FROM "hr"."employee" e JOIN "hr"."ticket"t ONCAST(t."assignee" ASBIGINT)=e.id WHERE e."salary">5000 The difference between the database query compatibility adaptation module 20 and the first case lies in the following processing steps: S2: The parser traverses the custom query statement to extract two table references ["hr.employee","hr.ticket"].

[0130] S3: The form entity corresponding to the table reference "employee" is a narrow table storage. `isNormalizedTableCreated()` returns true, indicating the narrow table has been created. Proceed to the wide / narrow table processing branch A, mapping the table reference "employee" to the physical name of the narrow table "hr_employee", resulting in the final custom query statement A. The form entity corresponding to the table reference "ticket" is a wide table storage. `isNormalizedTableCreated()` returns false, indicating the narrow table has not been created. Proceed to the wide / narrow table processing branch B, generating a system CTE virtual view. Inserting the system CTE virtual view definition before the user CTE virtual view definition in the `WITH` clause results in the final custom query statement B.

[0131] WITH "hr_ticket" AS NOT MATERIALIZED( SELECT fed.id,fed.value1 as "title", fed.value2 as"priority", fed.value3 as"assignee" FROM form_entity_datafed WHERE fed.form_entity_id=200 ) SELECT e."employeeName",t."title",t."priority" FROM hr_employeee JOIN "hr_ticket"t ONCAST(t."assignee" ASBIGINT)=e.id WHERE e."salary">5000 S4, restore parameters (in this example there are no named parameters, no preprocessing is performed, no parameter restoration is needed, skip this step), perform syntax checks and security checks on the final custom query statement A and the final custom query statement B, and execute the final custom query statement A and the final custom query statement B.

[0132] This case demonstrates the core capability of the present invention: during the query process of the same custom query statement, the storage status of the corresponding form entities referenced by different tables is processed independently, and the CTEs of narrow and wide tables can coexist seamlessly in the custom query statement.

[0133] In the fourth case, referring to steps 27, 28 and 29 above, the processing procedure for a custom query statement containing a WITH clause is defined.

[0134] Users write custom query statements that include user CTE virtual views: WITH monthly_stats AS( SELECT e."department",COUNT(*)ascnt FROM "hr"."employee" e GROUP BY e.department ) SELECT *FROM monthly_stats WHEREcnt>5 The difference between the database query compatibility adaptation module 20 and the first case lies in the following processing steps: S2: Identify the user-defined shared table expression name (i.e., the user CTE virtual view name) "monthly_stats" within the WITH clause of the custom query statement and add it to the user shared expression name set; extract the table reference ["hr.employee"] from the custom query statement. Note: monthly_stats will not be misidentified as a table reference because it is already in the user CTE virtual view name set.

[0135] S3: The query table references the form entity corresponding to "employee" and the storage mode is a wide table. Enter the wide and narrow table processing branch B to generate the system CTE virtual view.

[0136] S5: If a custom query statement is detected to begin with "WITH", insert the system CTE virtual view definition before the user CTE virtual view definition. WITH "hr_employee" AS NOT MATERIALIZED( SELECT fed.id, fed.value1as"employeeName", fed.value2 as "department",fed.label2 as "department_label" FROM form_entity_datafed WHERE fed.form_entity_id=100 ), monthly_stats AS( SELECT e."department",COUNT(*)ascnt FROM "hr_employee" e GROUP BY e.department ) SELECT*FROM monthly_stats WHEREcnt>5 This fourth case demonstrates that a system CTE virtual view (generated by the system) and a user CTE virtual view (defined by the user in the WITH clause) can coexist without conflict.

[0137] In the fifth case, the platform query statement was generated using the platform's built-in field token for adaptive querying.

[0138] Taking the "employee information" form with 20 fields (including 3 NUMBER fields, 2 TEXTAREA fields and 5 SELECT fields) as an example, where the data volume is 100,000 records, a list query including filtering, sorting and paging is performed. At this time, when the user performs filtering + sorting + searching operations on the "employee information" form on the platform interface, the generated platform query statement is: { "filterModel":[{"colId":"salary","filter":"5000","type":"greaterThan"}], "sortModel":[{"colId":"employeeName", "sort":"asc"}],"searchText":"Zhang", "searchModels":[{"colId":"employeeName"},{"colId": "department"}] } During the processing of the query token mapping module 30, if the storage mode of the form entity corresponding to the table reference in the platform query information is a wide table, the following operations are performed: salary → value3 (for common fields, use storageField) employeeName → value1 department → label2 (for single selection of SELECT type, the label column is used for searching) If the storage mode of the form entity corresponding to the table reference in the platform query information is a narrow table, the following operations are performed: salary → salary (for common fields, use normalizedStorageField) employeeName → employee_name department → department_label (for single selection of SELECT type, use label column + _label suffix for searching) The SQL WHERE clauses generated in the two modes are different, but the query results are the same, and are completely transparent to users.

[0139] In summary, under the wide table storage model, the database query compatibility adaptation module 20 needs to generate a system CTE virtual view, containing column name mappings for 20 fields, CAST type conversion expressions for 3 NUMBER fields, and LEFT JOIN joins for 2 TEXTAREA fields. The final platform query statement contains a system CTE virtual view definition (approximately 30 rows) and 2 LEFT JOINs. When executing the final platform query statement, the CTE virtual view needs to be materialized first (scanning all records in the wide table where form_entity_id=100 and performing column mappings and type conversions), and then filtering and sorting are performed on the CTE results.

[0140] In narrow table storage mode: the database query compatibility adapter module 20 directly replaces the logical table name with the physical table name, without generating a CTE, performing type conversion, or requiring LEFT JOIN (long text is directly stored in the narrow table column). The generated final platform query statement is a simple SELECT...FROM...WHERE...ORDERBY statement. The database executes the query directly on the narrow table and can utilize the indexes on the narrow table.

[0141] This case study supports both wide and narrow table dual-mode storage, allowing mixed queries of custom query statements and platform query statements, as well as adding, deleting, and defining modifications to form entities in multiple databases with different storage modes. In the aforementioned test case, the average response time for wide table storage was approximately 120ms, while the average response time for narrow table storage was approximately 65ms. Therefore, compared to using wide table storage exclusively for queries, this case study supports mixed wide and narrow table storage and queries, resulting in a significant performance improvement. The main sources of this performance improvement are the elimination of CTE materialization overhead, the elimination of CAST type conversion overhead, the elimination of LEFT JOIN join overhead, and the support for targeted indexes in narrow tables.

[0142] Other implementation details and working methods of the data query adaptation method for compatible wide and narrow table dual-mode storage disclosed in this application are the same as or similar to the data query adaptation system for compatible wide and narrow table dual-mode storage described above, and will not be repeated here.

[0143] A third embodiment of this application provides an electronic device, including a processor and a memory, wherein the memory stores one or more computer programs, the one or more computer programs including instructions, which, when executed by the electronic device, cause the electronic device to perform the above-described data query adaptation method for compatible wide and narrow table dual-mode storage.

[0144] The above are all preferred embodiments of this application, and are not intended to limit the scope of protection of this application. Therefore, all equivalent changes made in accordance with the structure, shape and principle of this application should be covered within the scope of protection of this application.

Claims

1. A data query adaptation system compatible with wide and narrow table dual mode storage, characterized in that, The system includes: The database query compatibility and adaptation module is used to obtain custom query statements from the business application layer. It extracts the table reference list, column reference set, table alias mapping set, and user-shared table expression name set by traversing the custom query statements through the parser. It finds the corresponding form entity based on the business component identifier and form identifier of each table referenced in the table reference list. It then performs the corresponding wide and narrow table branch processing according to the storage mode defined by the form entity. Specifically, when the storage mode is a narrow table, the wide-narrow table branching process includes mapping the table reference to the physical table name of the narrow table; when the storage mode is a wide table, the wide-narrow table branching process includes dynamically constructing a system common table expression that maps the general columns of the wide table to the business semantic column names according to the field definition of the form entity, and injecting the system common table expression definition into the custom query statement. Injecting the system shared table expression definition into the custom query statement includes: replacing all table references in the custom query statement with the name of the system shared table expression; if the custom query statement does not contain a WITH clause, adding the "WITH" keyword and the system shared table expression definition before the custom query statement; if the custom query statement contains a WITH clause, inserting the system shared table expression definition before the user shared table expression definition of the WITH clause.

2. The system of claim 1, wherein, Before the parser traverses the custom query statement, the colon prefix of the named parameter of the custom query statement is replaced with a placeholder without special characters. Before the custom query statement is executed, the placeholder is restored to the colon prefix of the named parameter. The table reference list includes all table references extracted from the FROM and JOIN clauses, the column reference set includes the referenced column names extracted from the SELECT clause, the table alias mapping set includes the mapping relationship between table aliases and canonical table names extracted from the AS clause, and the user-shared table expression name set includes the defined user-shared table expression names extracted from the WITH clause.

3. The system of claim 1, wherein, Based on the field definitions of the form entities, dynamically construct system-shared table expressions that map wide table generic columns to business semantic column names, including: Use table aliases to qualify the selected basic system fields; Generate column alias mappings for plain text fields, generate type conversion expressions that include null values ​​and empty strings for numeric fields, generate mappings between value columns and pointer label columns for reference fields, generate left joins with auxiliary text storage tables for long text fields, and generate mappings between value columns and date label columns for date fields. Add filtering conditions to ensure that the system's shared table expressions only contain data from the current form; Use the format "Business Component Identifier_Form Identifier" to name the system's common table expressions.

4. The system of claim 1, wherein, The system also includes: The query token mapping module is used to obtain platform query statements from the business application layer and map the field tokens in the platform query statements to the physical column names corresponding to the storage mode according to the field type. The field tokens include filtering components, sorting components, search components, column selection components, aggregation components, and grouping components. The field types include ordinary fields, cascading selection fields, tree selection fields, single-selection reference fields, multi-selection reference fields, long text fields, and system reserved fields.

5. The system of claim 1, wherein, The system also includes: The table structure evolution module, when triggering narrow table conversion, converts camelCase naming to snakeCase naming after passing environment checks and table name validation. It establishes a mapping between field types and database types, generates mappings between value columns and reference label columns for reference-type fields, and between value columns and date label columns for date-type fields. It executes the data definition language through the database change management engine, updates the three-level status flags of form entities, and adjusts the storage mode to narrow table when independent tables are enabled, narrow table mode is enabled, or a narrow table has been created. The execution of the data definition language through the database change management engine includes generating a table creation script when the table does not exist, generating an incremental script for adding columns from the existing column set when the table exists, and adding a prefix to the snakeCase naming when it conflicts with a reserved keyword in the database.

6. The system according to claim 1, characterized in that, The system also includes: The dual-mode CRUD engine module identifies the storage mode, three-level status flag, and physical table name of the form entity corresponding to an INSERT or DELETE statement, dynamically switching between wide and narrow table storage paths. If the three-level status flag indicates that an independent table is started, the narrow table name is returned as the physical table name; otherwise, the shared wide table name is returned. In narrow table storage mode, standardized storage fields are used as parameter keys to write data to metadata fields; in wide table storage mode, storage fields are used as parameter keys to write data to metadata fields. The metadata fields have the same column names in different storage modes; and / or... The multi-tenant data source routing module is used to switch to the database corresponding to the tenant based on the tenant project identifier, and to determine the physical data table to be accessed based on the independent identifier and independent table name of the form entity. The physical data tables in the database include shared wide tables, independent wide tables and narrow tables.

7. A data query adaptation method compatible with both wide and narrow table dual-mode storage, characterized in that, The method includes the following steps: Retrieve custom query statements from the business application layer; The parser parses the custom query statement into an abstract syntax tree, and traverses the abstract syntax tree to extract the table reference list, column reference set, table alias mapping set, and user-shared table expression name set. For each table reference in the table reference list, query the form entity based on the business component identifier and form identifier in each table reference. Execute the corresponding wide or narrow table branch processing according to the storage mode defined in the form entity. When the storage mode is narrow table, map the table reference to the physical table name of the narrow table. When the storage mode is wide table, dynamically generate a system common table expression based on the field definition of the form entity. The system common table expression indicates that the wide table general column is mapped to the business semantic column name. Inject the system-shared table expression definition into the custom query statement; Execute the injected custom query and return the result; The process of injecting the system shared table expression definition into the custom query statement includes: replacing all table references in the custom query statement with the name of the system shared table expression; if the custom query statement does not contain a WITH clause, adding the "WITH" keyword and the system shared table expression definition before the custom query statement; and if the custom query statement contains a WITH clause, inserting the system shared table expression definition before the user shared table expression definition of the WITH clause.

8. The method according to claim 7, characterized in that, Traversing the abstract syntax tree includes: distinguishing between identifiers and column name identifiers by maintaining the context state flags of the FROM clause; recursively accessing the query body corresponding to the WITH clause for the user-shared table expression in the WITH clause, and adding the user-shared table expression to the user-shared table expression name set; identifying the identifiers in the ON condition of the JOIN clause as column names; Before the parser parses the custom query statement into an abstract syntax tree, the method further includes replacing the colon prefix of the named parameters of the custom query statement with a placeholder without special characters; before executing the injected custom query statement and returning the result, the method further includes restoring the placeholder to a named parameter containing a colon. The method further includes: obtaining a platform query statement from the business application layer, and mapping the field tokens in the platform query statement to the physical column names corresponding to the storage mode according to the field type. The field tokens include filtering components, sorting components, search components, column selection components, aggregation components, and grouping components. The field types include ordinary fields, cascading selection fields, tree selection fields, single-selection reference fields, multi-selection reference fields, long text fields, and system reserved fields.

9. An electronic device, characterized in that, The device includes a processor and a memory, wherein the memory stores one or more computer programs, the one or more computer programs including instructions that, when executed by the electronic device, cause the electronic device to perform the data query adaptation method for compatible wide and narrow table dual-mode storage as described in any one of claims 7-8.

Images