A cross-database platform metadata query adaptive method and system

Abstract

The application discloses a cross-database platform metadata query adaptive method and system. The method comprises the following steps: identifying database features and generating feature identifiers; loading corresponding metadata query rules from a configurable metadata query rule library according to the feature identifiers; normalizing database object identifiers; dynamically generating and executing metadata query statements adapted to the SQL dialect of the target database; and parsing the query results and outputting object existence judgments. The application decouples the database-specific query logic from the core framework by constructing a "database feature identifier-metadata query rule" mapping mechanism, thereby solving the problems of metadata query incompatibility, unreliable change execution, and high adaptation cost of existing database change management tools in the domestic database environment, and significantly improving the automation level, reliability, and scalability of Schema change management across heterogeneous database platforms.

Description

Technical Field

[0001] This application relates to the fields of database management, DevOps automated deployment, and software engineering, specifically to an adaptive method, system, and electronic device for cross-database platform metadata query. Background Technology

[0002] With the diversification of enterprise IT architectures and the advancement of localization strategies, database selection is becoming increasingly complex. Enterprises typically need to manage multiple heterogeneous databases simultaneously, including mainstream international databases (such as Oracle, MySQL, and PostgreSQL) and domestic databases (such as DM, KingbaseES, and ShenTong). In DevOps practices, automated change management of database schemas is a key aspect of ensuring system stability and rapid iteration.

[0003] Liquibase, a widely used open-source database change management tool, uses ChangeLog files to manage database schema changes in a versioned manner and leverages preconditions to control the execution logic of changes. For example, it utilizes... <indexexists>Preconditional checks are used to determine if an index exists to avoid duplicate operations. However, mainstream tools like Liquibase are typically designed to support the SQL dialects and metadata views (such as INFORMATION_SCHEMA and ALL_INDEXES) of major international databases by default, leading to the following challenges when adapting to domestic databases:

[0004] 1. Metadata query incompatibility

[0005] Domestic databases typically have their own unique system views for storing database object metadata (e.g., DM Database uses SYSDBA.ALL_INDEXES, and Kingbase Database uses PG_INDEXES or compatible views), and they differ in fields such as field naming, case sensitivity, and permission models. Liquibase's built-in... <indexexists>When preconditions are executed on these databases, the default SQL query cannot correctly access or parse the corresponding metadata view, causing the index existence check to fail or misjudge.

[0006] 2. The implementation of changes is unreliable.

[0007] Inaccurate index existence checks may lead to the repeated creation of existing indexes (causing an "index already exists" error and interrupting deployment) or the incorrect skipping of indexes that should have been created (resulting in application performance degradation or functional abnormalities), severely impacting the reliability and stability of automated database deployment.

[0008] 3. High adaptation costs

[0009] To solve the above problems, developers typically need to write a large number of database-specific manual SQL scripts, or through... <sqlcheck> 、 <customprecondition>This approach hard-codes metadata query logic for various domestic databases using common extension points. This fragmentation disrupts the unified management of database changes, increases development and maintenance complexity and costs, and is prone to human error.

[0010] 4. Lack of versatility and scalability

[0011] Existing solutions are mostly "point-to-point" adaptations for specific databases, lacking a general and scalable framework to cope with the possible emergence of more domestic database types or changes in metadata structure under different versions in the future.

[0012] In summary, existing technologies lack the ability to adaptively query metadata across database platforms (especially domestic database environments). There is an urgent need for a metadata query framework that can automatically identify, dynamically adapt, and flexibly configure metadata to improve the automation level and reliability of database change management. Summary of the Invention

[0013] To overcome the aforementioned deficiencies in existing technologies, this application proposes a novel adaptive method and system for cross-database platform metadata query. More specifically, this invention provides a method and system for accurately and adaptively determining the existence of database objects (such as indexes) when managing database schema changes using version control tools in a multi-database environment, particularly for domestic databases. This aims to address the insufficient compatibility of existing database change management tools with metadata querying on domestic databases.

[0014] The technical problems to be solved by this invention include: when database schema change management tools such as Liquibase perform database change management, how to enable them to adaptively and accurately determine the existence of specified database objects (especially indexes) in domestic databases such as DM and Kingbase, overcome the judgment failure or misjudgment caused by the differences in metadata query mechanisms of different databases, and thus improve the compatibility and automation level of database changes in the domestic database environment.

[0015] To achieve the above objectives, the present invention employs the following technical strategies:

[0016] (1) Constructing an "Adaptive Framework for Database Metadata Query": Unlike simple plugin adaptation, this invention proposes and constructs a general adaptive framework based on the mapping mechanism of "database feature identifier - metadata query rules". This framework can dynamically match the metadata source, identifier normalization rules, SQL query template and result judgment rules of the target database, and realize intelligent adaptation of metadata query across database platforms.

[0017] (2) Configurable rule mapping engine and metadata query rule base: By introducing an external configurable rule base, this invention separates the database-specific query logic from the core framework, enabling the framework to flexibly adapt to new database types, different database versions or changes in metadata structure. It can be extended to support without modifying the core code, which greatly enhances the system's versatility and maintainability.

[0018] (3) Fine-grained identifier normalization mechanism: The rule base clearly defines normalization rules for database object identifiers such as schema names, table names, and index names (such as automatic conversion to uppercase / lowercase, adding / removing quotes, etc.), which accurately adapts to the dialect differences in object name processing of different domestic databases and ensures the correct execution of SQL statements on the target database.

[0019] (4) Runtime dynamic SQL generation and execution: Based on the matched rules, the framework dynamically generates query statements that are fully adapted to the target database SQL dialect and metadata view at runtime, avoiding the limitations of hard-coded SQL and improving the accuracy and robustness of the query.

[0020] (5) Deep integration of engineering practice and localization strategy: This invention directly addresses the pain points of mainstream change management tools such as Liquibase in adapting to domestic databases, and provides a solution that can be implemented in engineering and verified in practice, which strongly supports the construction of domestic information infrastructure and the improvement of DevOps system.

[0021] Specifically, this application provides the following technical solutions:

[0022] The first aspect of this application provides an adaptive method for cross-database platform metadata querying, such as... Figure 1 As shown, the method includes the following steps:

[0023] S1. Identify database characteristics: At runtime, obtain the product name and version information of the current database connection and generate a unique database characteristic identifier;

[0024] S2. Load query rules: Based on the database feature identifier, match and load the corresponding metadata query rules from the preset metadata query rule library; the metadata query rule library stores multiple mapping relationships between database feature identifiers and corresponding metadata query rules, and each metadata query rule includes at least a metadata source identifier, identifier normalization rules, SQL query template, and result extraction and judgment rules;

[0025] S3. Normalize object identifier: Receive the database object identifier to be queried, and process the database object identifier according to the identifier normalization rules in the loaded metadata query rules;

[0026] S4. Generate and execute adaptive SQL: Substitute the normalized database object identifier into the SQL query template in the loaded metadata query rules, replace the metadata source identifier placeholder in the template with the actual metadata view name, dynamically generate a metadata query SQL statement suitable for the current database, and execute the generated metadata query SQL statement through the database connection.

[0027] S5. Determine and return query results: Based on the result extraction and determination rules in the loaded metadata query rules, parse the SQL query results and output the existence determination results of the database objects.

[0028] Furthermore, in the method of this application, the generation method of the database feature identifier in step S1 includes: string matching of the database product name and / or regular expression matching of the database version number.

[0029] Furthermore, in the method of this application, the metadata query rule base mentioned in step S2 is a persistent storage configuration set, and its storage form includes XML files, JSON files or database tables.

[0030] Furthermore, in the method of this application, the identifier normalization rules in step S2 include: performing case conversion, adding or removing reference symbols on database object identifiers to adapt to the naming conventions of the target database.

[0031] Furthermore, in the method of this application, if no matching metadata query rule is found in step S2, an exception is thrown or a result that cannot adapt to the query is returned, and the upper layer application controls the process according to the preset failure handling strategy.

[0032] Furthermore, in the method of this application, the metadata source identifier mentioned in step S2 points to a system view in the target database used to store index information;

[0033] The database object identifier mentioned in step S3 includes the schema name, table name, and index name;

[0034] The metadata query SQL statement in step S4 is used to query the existence of indexes in the database.

[0035] Furthermore, the method of this application is integrated into the Liquibase database change management tool and is invoked as a custom precondition component to control the execution logic of the database changeset.

[0036] A second aspect of this application provides a cross-database platform metadata query adaptive system, wherein the system implements the steps of the aforementioned cross-database platform metadata query adaptive method during operation, such as... Figure 2 As shown, the system includes:

[0037] The database feature recognition module is used to obtain the product name and version information of the current database connection at runtime and generate a unique database feature identifier.

[0038] The metadata query rule base is used to store the mapping relationship between multiple database feature identifiers and corresponding metadata query rules. Each metadata query rule includes at least the metadata source identifier, identifier normalization rules, SQL query template, and result extraction and judgment rules.

[0039] The rule mapping engine is used to match and load the corresponding metadata query rules from the metadata query rule base based on the database feature identifier generated by the database feature recognition module.

[0040] A runtime query adapter, connected to the rule mapping engine, is used to receive the identifier of the database object to be queried and execute the query according to the loaded metadata query rules. The runtime query adapter includes:

[0041] (1) Object name normalization submodule, used to normalize the database object identifier;

[0042] (2) SQL statement generation submodule, used to dynamically generate metadata query SQL statements applicable to the current database;

[0043] (3) SQL execution submodule, used to execute the query SQL statement through the database connection;

[0044] (4) Result determination submodule, used to parse the SQL query results and output the existence determination results of the database objects.

[0045] Furthermore, the system in this application also includes a change management tool integration module, which includes a component that implements a custom precondition interface for database change management tools. This module is used to receive database object parameters from the change set, call the cross-database platform metadata query adaptive system to determine the existence of database objects, and control the execution of the change set based on the determination result.

[0046] Furthermore, in this application system, the database change management tool is Liquibase, and the custom precondition interface is liquibase.precondition.CustomPrecondition.

[0047] A third aspect of this application provides an electronic device, including: a memory and a processor;

[0048] Memory: Used to store computer programs;

[0049] Processor: Used to execute the computer program to implement the steps of the aforementioned cross-database platform metadata query adaptive method.

[0050] A fourth aspect of this application provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the aforementioned cross-database platform metadata query adaptive method.

[0051] In summary, this invention, by constructing a configurable rule mapping mechanism, achieves intelligent and adaptive determination of the existence of database objects in heterogeneous database environments, thereby improving the reliability and efficiency of automated database deployment. Compared with existing technologies, the solution of this invention has the following advantages:

[0052] (1) High-precision metadata query: By specifically adapting the metadata view and SQL dialect of different domestic databases, this invention can achieve high-precision judgment of the existence of database objects, effectively avoiding misjudgment and failure caused by insufficient query compatibility.

[0053] (2) Improve deployment reliability: Significantly reduced the deployment failure rate caused by repeated index creation and skipping necessary changes in the domestic database environment, ensured the atomicity and idempotency of database schema changes, and improved the reliability of automated deployment.

[0054] (3) Reduce development and maintenance costs: It provides a unified and configurable metadata query framework, which avoids writing a large number of customized scripts for each domestic database, greatly simplifies the database change management process, and reduces the complexity and cost of development and maintenance.

[0055] (4) Enhanced cross-platform compatibility and scalability: The design of this system has good versatility and scalability. By expanding the rule base, it can quickly adapt to the query requirements of new domestic database types, different database versions, or different types of database objects (such as tables, views, stored procedures, sequences, etc.), which promotes the application of tools such as Liquibase in heterogeneous domestic database environments.

[0056] (5) Promote the construction of the domestic ecosystem: It provides more complete DevOps toolchain support for domestic databases, which helps to enhance the competitiveness of domestic databases in enterprise applications and is in line with the national information technology application innovation strategy.

[0057] (6) Improve development efficiency: Developers can manage schema changes for all database types in a unified Liquibase ChangeLog without having to worry about differences in metadata queries of the underlying database, which improves development efficiency and team collaboration.

[0058] Other features and advantages of this application will be described in detail in the following description, or can be learned by implementing the relevant technical solutions of this application. Attached Figure Description

[0059] To more clearly illustrate the technical solution of this application, the accompanying drawings involved in the description of this invention will be briefly introduced below. It should be noted that the drawings only show some embodiments of the invention. For those skilled in the art, other related drawings can be derived from these drawings without creative effort.

[0060] Figure 1 This is a schematic diagram illustrating the operation steps of the cross-database platform metadata query adaptive method of the present invention.

[0061] Figure 2 This is a structural diagram of the cross-database platform metadata query adaptive system of the present invention.

[0062] Figure 3 This is a schematic diagram of the overall design architecture of the cross-database platform metadata query adaptive system in an embodiment of the present invention.

[0063] Figure 4 This is a schematic diagram illustrating the implementation process of the index existence determination method based on this system in an embodiment of the present invention.

[0064] Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present invention.

[0065] Caption: Processor-310, Communication Interface-320, Memory-330, Communication Bus-340. Detailed Implementation

[0066] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. It should be understood that the described embodiments are only some embodiments of this application, and not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the protection scope of this application.

[0067] In this document, the term "comprising" and any variations thereof (such as "including," "including," etc.) are open-ended expressions and should be understood as "including but not limited to," meaning that the listed content is not exhaustive and may include other content not explicitly mentioned. The term "based on" should be understood as "at least partially based on," meaning that the basis or condition referred to may not be the only factor and may involve other relevant factors. The term "one embodiment" should be understood as "at least one embodiment," meaning that the described embodiment is not the only possible implementation, and other similar embodiments may exist.

[0068] In this application, the terms "a" and "a plurality of" are used to modify related elements or features, and their expression is illustrative rather than restrictive. Unless otherwise expressly stated in the context, "a" should be understood as "at least one," and "a plurality of" should be understood as "at least two." Those skilled in the art should reasonably interpret these terms based on the semantic and logical relationships of the context to ensure that they cover the possibility of "one or more."

[0069] This invention provides the following technical solution:

[0070] This invention proposes an adaptive metadata query system across database platforms, the core of which lies in a configurable rule mapping engine and a runtime query adapter. When applied to the Liquibase tool, this system achieves intelligent determination of index existence by extending Liquibase's preconditioning mechanism.

[0071] Specific functional components include:

[0072] 1. Database type and version identification module: Automatically detects the database product name and version information of the current Liquibase connection at runtime, forming a database feature identifier.

[0073] 2. Metadata Query Rule Base: Establish a configurable rule base to store the mapping relationship between different database feature identifiers (such as "DM Database V8", "KingbaseES V8", etc.) and their corresponding metadata query rules. Each rule must contain at least:

[0074] a. Metadata source identifier: The system view or table name in the target database used to query a specified object (such as an index) (e.g., SYSDBA.ALL_INDEXES, PG_CATALOG.PG_INDEXES).

[0075] b. Identifier normalization rules: Define how to process the input database object names (schema names, table names, index names) to adapt to the naming rules of the target database (e.g., convert all to uppercase, add quotes, or handle mixed cases).

[0076] c. SQL Query Template: A parameterized SQL template for this database type used to query the existence of objects (e.g., SELECT COUNT(*) FROM {0} WHERE OWNER = ? AND TABLE_NAME = ? AND INDEX_NAME= ?).

[0077] d. Result extraction and judgment rules: Define how to extract the judgment criteria (e.g., the value of the COUNT(*) column) from the SQL query results, and how to determine whether the object exists based on the value.

[0078] e. Priority and rollback strategy: How to select the best rule or perform a rollback operation when multiple matching rules are identified or a rule fails to match.

[0079] 3. Rule Mapping Engine: Based on the feature identifiers output by the database type and version identification module, it matches and loads the most suitable metadata query rules from the metadata query rule library.

[0080] 4. Runtime Query Adapter: Receives database object parameters (table name, index name, schema name, etc.) input by the user and combines them with the query rules selected by the rule mapping engine.

[0081] a. Object name normalization: Perform identifier normalization operations defined in the rule base on the input parameters.

[0082] b. SQL statement generation: Substitute the normalized parameters into the SQL query template defined in the rule base to dynamically generate a complete metadata query SQL statement adapted to the target database.

[0083] c. SQL execution: Execute the generated SQL query statements through the JDBC abstraction layer or database connection provided by Liquibase.

[0084] d. Result determination: Based on the result extraction and determination rules defined in the rule base, parse the SQL query results and output the existence determination results of the database objects.

[0085] 5. Liquibase Extension Integration: Integrate the above system into Liquibase's CustomPrecondition component. When Liquibase parses this custom precondition, it will call the system to determine the existence of database objects and control the execution of the Liquibase changeset based on the result.

[0086] To more clearly illustrate the technical solution of this application, the following will provide further explanation through specific scenario embodiments.

[0087] Example 1: System Architecture of an Adaptive Metadata Query System Across Database Platforms

[0088] like Figure 3 As shown, this invention provides an adaptive metadata query system across database platforms. The system mainly includes the following modules:

[0089] 1. Liquibase Change Management Layer: Represents the core functionality of the Liquibase tool, responsible for parsing ChangeLog files, managing the execution order of ChangeSets, and triggering PreCondition checks before executing changes.

[0090] 2. Custom Precondition Component: As a Liquibase extension module, it implements the liquibase.precondition.CustomPrecondition interface. This component is the interface between the system and the Liquibase change management layer, responsible for receiving database object parameters (such as table name, index name, and schema name) from the ChangeLog and forwarding query requests to the system core.

[0091] 3. Database type and version identification module: Deployed inside a custom precondition component or as a standalone service, it obtains the name of the currently connected database product (such as DMDatabase, PostgreSQL) and version number (such as 8.0, 9.6) through the Liquibase Database object at runtime, and generates a unique database feature identifier (such as DM_8_0, POSTGRESQL_9_6).

[0092] 4. Metadata Query Rule Base: This is a persistently stored collection of configurations, which can be XML files, JSON files, database tables, or in-memory objects. It stores the mapping relationship between database feature identifiers and corresponding metadata query rules. This rule base is the core of the system's adaptive capabilities.

[0093] Example of a rule entry (for the DM database):

[0094] Database feature identifier: DM_8_0

[0095] Metadata source identifier: SYSDBA.ALL_INDEXES (assuming the DM index metadata view is located here)

[0096] Identifier normalization rules: {SCHEMA_NAME: UPPERCASE}, {TABLE_NAME: UPPERCASE}, {INDEX_NAME: UPPERCASE}

[0097] SQL query template: SELECT COUNT(*) FROM {METADATA_SOURCE} WHERE OWNER = ?AND TABLE_NAME = ? AND INDEX_NAME = ?

[0098] Result extraction and judgment rule: COUNT(*) > 0

[0099] Example of a rule entry (for the Renmin University of China's Kingbase database):

[0100] Database feature identifier: KINGBASEES_8_0

[0101] Metadata source identifier: PG_CATALOG.PG_INDEXES

[0102] Identifier normalization rules: {SCHEMA_NAME: LOWERCASE_IF_QUOTED, NO_QUOTES}, {TABLE_NAME: LOWERCASE_IF_QUOTED, NO_QUOTES}, {INDEX_NAME: LOWERCASE_IF_QUOTED, NO_QUOTES} (KingbaseES is usually lowercase and without quotes by default)

[0103] SQL query template: SELECT COUNT(*) FROM {METADATA_SOURCE} IDX JOIN PG_CLASST ON IDX.INDRELID = T.OID JOIN PG_NAMESPACE NS ON T.RELNAMESPACE = NS.OID WHERE NS.NSPNAME = ? AND T.RELNAME = ? AND IDX.RELNAME = ? (This needs to be determined according to the actual metadata structure of KingbaseES)

[0104] Result extraction and judgment rule: COUNT(*) > 0

[0105] 5. Rule Mapping Engine: Receives database feature identifiers (from the database type and version identification module) and searches for matching query rules in the metadata query rule base. It may include priority logic to handle multiple matching rules or version compatibility issues.

[0106] 6. Runtime Query Adapter: The core processing unit of the system. It receives database object parameters input by the user and query rules matched by the rule mapping engine.

[0107] a. Object Name Normalization Submodule: Based on the "Identifier Normalization Rules" in the matching rules, this module performs case conversion, adding / removing quotes, and other processing on the input schema names, table names, and index names to make them conform to the SQL dialect of the target database.

[0108] b. SQL statement generation submodule: Substitutes the normalized object name into the "SQL query template" in the rules, and replaces the {METADATA_SOURCE} placeholder in the template with the actual metadata view name to generate the final executable SQL query statement.

[0109] c. SQL Execution Submodule: Executes the generated SQL query statements via JDBC connection using Liquibase's ExecutorService.

[0110] d. Result Determination Submodule: Based on the "Result Extraction and Determination Rules" in the rules, parse the SQL query results (e.g., extract the value of COUNT(*) from the ResultSet) and determine whether the index exists.

[0111] 7. Liquibase Executor Service: The internal SQL execution interface of Liquibase, used to abstract JDBC operations.

[0112] 8. JDBC connection: The actual database connection used to communicate with the target database.

[0113] 9. Target Database: The actual database instance to be modified according to the schema.

[0114] Example 2: Index Existence Determination Method Based on the Above System

[0115] like Figure 4 As shown, based on the above system, this invention implements the following index existence determination method:

[0116] 1. Changeset parsing and precondition triggering: Liquibase begins executing the ChangeLog file. When a changeset containing... <customprecondition classname="com.yourcompany.DmAdaptiveIndexExistsCondition" ...>or<custom:dmAdaptiveIndexExists ...> When a change set is defined, a custom precondition is triggered.

[0117] 2. Instantiate custom precondition components: Liquibase instantiates the DmAdaptiveIndexExistsCondition class through reflection and injects the tableName, indexName, schemaName and other parameters specified in ChangeLog into the corresponding properties of the instance.

[0118] 3. Database type and version identification: The custom precondition component calls its internal check() method. This method first obtains the name and version of the currently connected database product through database.getDatabaseProductName() and database.getDatabaseProductVersion(), and generates a database feature identifier.

[0119] 4. Rule Matching and Loading: Database feature identifiers are passed to the rule mapping engine. The rule mapping engine uses these identifiers to find and load the most matching metadata query rule from the metadata query rule base.

[0120] If a matching rule is found: continue with the next steps.

[0121] If no matching rule is found: throw an exception or return a failure result, indicating that an adaptive judgment cannot be provided for the current database type. Liquibase will handle this according to the onFail attribute.

[0122] 5. Object Name Normalization: The runtime query adapter processes the passed schemaName, tableName, and indexName according to the "identifier normalization rules" in the loaded query rules. For example, for the DM database, all names are converted to uppercase.

[0123] 6. SQL statement generation: The runtime query adapter substitutes the normalized object name into the "SQL query template" in the loaded query rules, and replaces {METADATA_SOURCE} with the metadata view name defined in the rules to generate the final SQL query statement for the target database.

[0124] 7. SQL Execution: The runtime query adapter executes the generated SQL query statements through the Liquibase executor service.

[0125] 8. Result Determination: The runtime query adapter parses the SQL query results according to the "Result Extraction and Determination Rules" in the loaded query rules. For example, if the COUNT(*) value returned by the query is greater than 0, it is determined that the index exists; otherwise, it is determined that the index does not exist.

[0126] 9. Return the judgment result: Based on the judgment result of the previous step, the custom precondition component returns a success message to Liquibase regarding the change of management layer (whether the index exists or not depends on whether it is included in the XML). <not>(Label) or failure judgment result.

[0127] 10. Liquibase subsequent processing: Based on the judgment result and the change set onFail attribute (such as MARK_RAN, HALT, CONTINUE, etc.), Liquibase decides whether to continue executing the database operations in the current change set, or to abort or skip them.

[0128] Example 3: Existence determination of DM / Kingbase indexes based on Liquibase

[0129] In one specific embodiment, the system of the present invention is integrated into Liquibase to support index existence determination for DM Database and KingbaseES.

[0130] Custom precondition class: Implement the com.yourcompany.DmAdaptiveIndexExistsCondition class, which inherits from CustomPrecondition.

[0131] Metadata query rule base: Create an adaptive-metadata-rules.xml file or define rules programmatically, as shown in the example below:

[0132] <metadatarules>

[0133] <rule dbproductname="DM Database" dbproductversionregex="8.*">

[0134] <metadatasource> SYSDBA.ALL_INDEXES< / metadatasource>

[0135] <identifiernormalization>

[0136] <schema uppercase="true" / >

[0137]

[0138] <index uppercase="true" / >

[0139] < / identifiernormalization>

[0140] <sqltemplate>

[0141] SELECT COUNT(*) FROM {metadataSource} WHERE OWNER = ? AND TABLE_NAME= ? AND INDEX_NAME = ?

[0142] < / sqltemplate>

[0143] <resultpredicate> COUNT_GT_ZERO< / resultpredicate> <!-- Internal enumeration or mapping to code -->

[0144] < / rule>

[0145] <rule dbproductname="KingbaseES" dbproductversionregex="8.*">

[0146] <metadatasource> PG_CATALOG.PG_INDEXES< / metadatasource>

[0147] <identifiernormalization>

[0148] <schema lowercase="true" quoted="false" / >

[0149]

[0150] <index lowercase="true" quoted="false" / >

[0151] < / identifiernormalization>

[0152] <sqltemplate>

[0153] SELECT COUNT(DISTINCT I.INDEXNAME) FROM PG_TABLES T JOIN PG_INDEXES I ON T.TABLENAME = I.TABLENAME AND T.SCHEMANAME = I.SCHEMANAME WHERE T.SCHEMANAME = ? AND T.TABLENAME = ? AND I.INDEXNAME = ?

[0154] <!-- Note: This SQL template needs to be accurately adjusted according to the actual metadata views of KingbaseES and their join relationship-->

[0155] < / sqltemplate>

[0156] <resultpredicate> COUNT_GT_ZERO< / resultpredicate>

[0157] < / rule>

[0158] <!-- Rules for other databases or default fallback rules can be added -->

[0159] < / metadatarules>

[0160] XML ChangeLog Configuration: Developers reference this precondition using a custom namespace in the Liquibase ChangeLog XML file, for example:

[0161] <changeset id="create_index_on_user_name" author="patenter">

[0162] <preconditions onfail="MARK_RAN">

[0163] <not>

[0164] <custom:adaptiveIndexExists

[0165] tableName="APP_USER"

[0166] indexName="IDX_APP_USER_USERNAME"

[0167] schemaName="APP_SCHEMA" / >

[0168] < / not>

[0169] < / preconditions>

[0170] <createindex tablename="APP_USER" indexname="IDX_APP_USER_USERNAME" unique="true">

[0171] <column name="USERNAME" / >

[0172] < / createindex>

[0173] < / changeset>

[0174] When Liquibase connects to the DM database, the system will identify DM database characteristics, load DM rules, convert APP_SCHEMA, APP_USER, and IDX_APP_USER_USERNAME to uppercase, and generate an SQL query for SYSDBA.ALL_INDEXES. When connecting to KingbaseES, the system will identify KingbaseES characteristics, load KingbaseES rules, convert names to lowercase (default), and generate an SQL query for PG_CATALOG.PG_INDEXES.

[0175] Through the above system architecture design and implementation scheme, this invention provides a flexible, scalable and efficient solution, which effectively improves the compatibility and reliability of database change management tools in domestic database environments.

[0176] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of systems, methods, and computer program products according to various embodiments of this application, including architecture, functionality, and operation. In these figures, each block may represent a module, program segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should be noted that each block in the block diagrams and / or flowcharts, and combinations thereof, can be implemented using either a dedicated hardware-based system or a combination of dedicated hardware and computer instructions to achieve the specified function or operation.

[0177] like Figure 5 As shown, embodiments of this application also disclose an electronic device, including: a processor 310, a communication interface 320, a memory 330 for storing a processor-executable computer program, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the steps of the aforementioned cross-database platform metadata query adaptive method.

[0178] It is understood that, in addition to memory and a processor, this electronic device may also include input devices (such as a keyboard), output devices (such as a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor through I / O interfaces (i.e., input / output interfaces).

[0179] The operations described in this application can be implemented by writing computer program code using one or more programming languages ​​or a combination thereof. The programming languages ​​include, but are not limited to, the following types:

[0180] Object-oriented programming languages, such as Java, Smalltalk, C++, etc.

[0181] Conventional procedural programming languages, such as "C" or similar programming languages.

[0182] The execution methods of program code include, but are not limited to:

[0183] It runs entirely on the user's computer;

[0184] Part of it executes on the user's computer, and part of it executes on a remote computer;

[0185] Execute as a standalone software package;

[0186] It is executed entirely on a remote computer or server.

[0187] In scenarios involving remote computers, the remote computer can connect to the user's computer via any type of network, including but not limited to local area networks (LANs) or wide area networks (WANs). Furthermore, the remote computer can also connect to external computers through an internet service provider, for example, by utilizing the internet for connection.

[0188] Furthermore, this application also discloses a computer-readable storage medium, wherein when the instructions in the computer-readable storage medium are executed by a processor of an electronic device, the electronic device is able to perform the various steps of the cross-database platform metadata query adaptive method disclosed in this application.

[0189] In the context of this application, a computer-readable storage medium refers to a tangible medium capable of storing computer program code and related data. Specific examples include, but are not limited to, the following:

[0190] (1) Portable computer disk: such as floppy disks and other removable magnetic storage media.

[0191] (2) Hard disk: including mechanical hard disks and solid-state hard disks and other fixed storage devices.

[0192] (3) Random Access Memory (RAM): A volatile storage medium used for temporary storage of data and program code.

[0193] (4) Read-only memory (ROM): a non-volatile storage medium used to store fixed programs and data.

[0194] (5) Erasable programmable read-only memory (EPROM) or flash memory: non-volatile storage media that supports multiple erasures and reprogrammings.

[0195] (6) Fiber optic storage devices: storage media based on fiber optic technology.

[0196] (7) Portable compact disc read-only memory (CD-ROM): a read-only medium that stores data in the form of an optical disc.

[0197] (8) Optical storage devices: such as DVDs, Blu-ray discs and other storage media based on optical principles.

[0198] (9) Magnetic storage devices: such as magnetic tapes, disks and other storage media based on magnetic principles.

[0199] (10) Any suitable combination of the above: for example, combining multiple storage media to meet different storage needs.

[0200] These computer-readable storage media can be used to store the program code and related data described in this application to support program execution and persistent data storage.

[0201] Specifically, according to embodiments of this application, the processes described in the flowcharts can be implemented as computer software programs. For example, embodiments of this application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. This computer program includes program code for executing the cross-database platform metadata query adaptive method disclosed in this application. When this computer program is executed by a processing system, it can achieve the functions defined in the embodiments of this application.

[0202] While the foregoing discussion contains several specific implementation details, these details should not be construed as limiting the scope of this application. The above description is merely a preferred embodiment of this application and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of this application is not limited to technical solutions formed by specific combinations of the above-described technical features. Furthermore, this application should also cover other technical solutions formed by any combination of the above-described technical features or their equivalents without departing from the foregoing disclosed concept.

[0203] Those skilled in the art should also understand that modifications can be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features, without departing from the spirit and scope of the technical solutions of the embodiments of this application. These modifications or substitutions will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of this application.< / not> < / customprecondition> < / customprecondition> < / sqlcheck> < / indexexists> < / indexexists>

Claims

1. An adaptive method for cross-database platform metadata query, characterized in that, Includes the following steps: S1. Identify database characteristics: At runtime, obtain the product name and version information of the current database connection and generate a unique database characteristic identifier; S2. Load query rules: Based on the database feature identifier, match and load the corresponding metadata query rules from the preset metadata query rule library; the metadata query rule library stores multiple mapping relationships between database feature identifiers and corresponding metadata query rules, and each metadata query rule includes at least a metadata source identifier, identifier normalization rules, SQL query template, and result extraction and judgment rules; S3. Normalize object identifier: Receive the database object identifier to be queried, and process the database object identifier according to the identifier normalization rules in the loaded metadata query rules; S4. Generate and execute adaptive SQL: Substitute the normalized database object identifier into the SQL query template in the loaded metadata query rules, replace the metadata source identifier placeholder in the template with the actual metadata view name, dynamically generate a metadata query SQL statement suitable for the current database, and execute the generated metadata query SQL statement through the database connection. S5. Determine and return query results: Based on the result extraction and determination rules in the loaded metadata query rules, parse the SQL query results and output the existence determination results of the database objects.

2. The method according to claim 1, characterized in that, The method for generating the database feature identifier in step S1 includes: string matching of the database product name and / or regular expression matching of the database version number.

3. The method according to claim 1, characterized in that, The metadata query rule base mentioned in step S2 is a persistent storage configuration set, and its storage format includes XML files, JSON files, or database tables.

4. The method according to claim 1, characterized in that, The identifier normalization rules in step S2 include: performing case conversion on database object identifiers and adding or removing reference symbols to adapt to the naming conventions of the target database.

5. The method according to claim 1, characterized in that, In step S2, if no matching metadata query rule is found, an exception is thrown or a result indicating that the query cannot be adaptively performed is returned, and the upper-layer application controls the process according to the preset failure handling strategy.

6. The method according to claim 1, characterized in that, The metadata source identifier mentioned in step S2 points to the system view in the target database used to store index information; The database object identifier mentioned in step S3 includes the schema name, table name, and index name; The metadata query SQL statement in step S4 is used to query the existence of indexes in the database.

7. The method according to claim 1, characterized in that, The method is integrated into the Liquibase database change management tool and is invoked as a custom precondition component to control the execution logic of database change sets.

8. A cross-database platform metadata query adaptive system, characterized in that, The steps of implementing the cross-database platform metadata query adaptive method as described in any one of claims 1-7 during system runtime include: The database feature recognition module is used to obtain the product name and version information of the current database connection at runtime and generate a unique database feature identifier. The metadata query rule base is used to store the mapping relationship between multiple database feature identifiers and corresponding metadata query rules. Each metadata query rule includes at least the metadata source identifier, identifier normalization rules, SQL query template, and result extraction and judgment rules. The rule mapping engine is used to match and load the corresponding metadata query rules from the metadata query rule base based on the database feature identifier generated by the database feature recognition module. A runtime query adapter, connected to the rule mapping engine, is used to receive the identifier of the database object to be queried and execute the query according to the loaded metadata query rules. The runtime query adapter includes: (1) Object name normalization submodule, used to normalize the database object identifier; (2) SQL statement generation submodule, used to dynamically generate metadata query SQL statements applicable to the current database; (3) SQL execution submodule, used to execute the query SQL statement through the database connection; (4) Result determination submodule, used to parse the SQL query results and output the existence determination results of the database objects.

9. The system according to claim 8, characterized in that, The system also includes a change management tool integration module, which contains a component that implements a custom precondition interface for the database change management tool. This module receives database object parameters from the change set, calls the cross-database platform metadata query adaptive system to determine the existence of database objects, and controls the execution of the change set based on the determination result.

10. The system according to claim 9, characterized in that, The database change management tool is Liquibase, and the custom precondition interface is liquibase.precondition.CustomPrecondition.

Images