Mixed analysis middleware based on cross-type database and query method

Abstract

The invention discloses mixed analysis middleware based on a cross-type database and a query method. The mixed analysis middleware comprises a data source management module, a non-relational database object mapping module, a multi-tenant authorization management module, a context-independent grammar construction prediction analysis table, an expansion BNF elimination left recursion module and a distributed transaction control module. Through the middleware, the requirement that an application system needs to use data in a cross-database mode in the petroleum industry is met. Cross-database query of relational and non-relational databases is realized, database access and service modes are unified, large application systems and platforms containing various types of data storage products are supported, the development efficiency of the application systems is greatly improved, and the learning threshold of research and development personnel is reduced. Meanwhile, through the provided multi-tenant and authorization management, monitoring and review of data row-level security can be realized, sensitive data are effectively protected, and data leakage is prevented.

Description

Technical Field

[0001] The present invention belongs to the technical field of data application methods in the petroleum industry, and in particular relates to a hybrid parsing middleware and a query method based on cross-type databases. Background Art

[0002] In the oil industry, data management is a crucial link. Due to the particularity of the industry, there are many types of data generated in various links such as oil exploration, mining, processing, and transportation. Therefore, companies often use different database systems to store this data. For example, relational databases may be used to store structured data, such as oil well production data, employee information, etc.; while non-relational databases may be used to store semi-structured or unstructured data, such as geological exploration reports, equipment logs, etc.

[0003] However, in actual queries, it is often necessary to simultaneously search for relevant data from different types of databases for comprehensive analysis and decision-making. Traditional data query methods cannot query across different types of databases, and therefore cannot meet the above requirements. Summary of the invention

[0004] The problem to be solved by the present invention is to provide a hybrid parsing middleware and query method based on cross-type databases. The method solves the problem of needing to operate across different databases in the petroleum industry, provides a unified database operation standard for applications, and solves hybrid cross-database queries of relational and non-relational databases. At the same time, combined with multi-tenancy and distributed transaction control, the security of data and business logic processing is improved.

[0005] In order to solve the above technical problems, the technical solution adopted by the present invention is: a hybrid parsing middleware based on cross-type databases, comprising:

[0006] A data source management module, which performs unified registration and management of all databases involved, shielding the specific configuration and deployment location of the data source;

[0007] The non-relational database object mapping module maps the related objects in the non-relational data storage to SQL standard objects to support SQL parsing correspondence;

[0008] The multi-tenant authorization management module combines tenants with databases to perform data row-level authorization;

[0009] The context-free grammar constructs a prediction analysis table. In the top-down syntax analysis process, the overall, mapping table, and storage stack architecture are constructed. After the LL(1) grammar is formed, the prediction analysis table of the production or error is determined based on the stack and the current symbol to achieve automatic syntax analysis.

[0010] Extended BNF left recursion elimination module, for rules containing left recursion, rewrite and eliminate them through extended BNF representation;

[0011] The distributed transaction control module is based on message-driven TCC distributed transaction control. By establishing a global transaction management center, it does not rely on the transaction processing support of different underlying databases.

[0012] Furthermore, the database includes relational databases and various non-relational data storage or data exchange products, the relational databases include but are not limited to Oracle, MySQL, SqlServer, Postgresql, ClickHouse, and Kudu based on Impala, and the various non-relational data storage or data exchange products include but are not limited to MinIO, Kafka, and Redis.

[0013] Furthermore, the non-relational database object mapping module includes an object mapping submodule for MinIO document storage, an object mapping submodule for Kafka message storage, and an object mapping submodule for Redis.

[0014] Furthermore, the multi-tenant authorization management module is established by the following steps:

[0015] First, extract and define metadata of data sources to form a unified metamodel;

[0016] Secondly, determine the master data table and clarify the parent-child or association relationship between the master data;

[0017] Next, determine the organization field in the master data table and use it to bind the logged-in tenant's configuration permissions to authorize and filter data.

[0018] Finally, determine the effect object fields in the business table, and associate the effect object fields of each business table with the master data table of the corresponding type by foreign key.

[0019] Furthermore, in the extended BNF representation, curly brackets {α} are used to indicate that the symbol string α may appear 0 or more times, i.e., α*; square brackets [α] are used to indicate that the appearance of α may or may not occur, and are used to indicate optional symbol strings; and round brackets are used to extract factors in the rules.

[0020] Furthermore, the distributed transaction control module establishes a message mechanism to drive subsequent actions by receiving messages of execution results of different stages of TCC.

[0021] In the Try phase, the business operation attempts to perform certain transactional operations, records the operations as a preparatory operation, and does not directly submit the results to the database or other persistent storage system, so that the commit or rollback can be performed as needed in the Confirm or Cancel phase;

[0022] In the Confirm phase, if the operation in the Try phase is successfully executed, the operation is confirmed and submitted to the database or other persistent storage system, that is, the preparatory operation in the Try phase is submitted and turned into the final stable state;

[0023] In the Cancel phase, if the operation in the Try phase fails or needs to be rolled back for some reason, the Cancel operation is executed to roll back or cancel the preparatory operation in the Try phase, that is, to roll back the preparatory operation in the Try phase and restore it to its original state.

[0024] Furthermore, the present invention also provides a query method based on cross-type database hybrid parsing middleware, using the above-mentioned cross-type database hybrid parsing middleware, including the following steps:

[0025] S1: Build database management module;

[0026] S2: Build a global SQL interception module;

[0027] S3: Implement SQL parser inside the middleware;

[0028] S4: Dynamically generate query statements;

[0029] S5: Obtain database connection information and establish a connection;

[0030] S6: Send query statements and receive execution results;

[0031] S7: Integrate and return the execution results.

[0032] Furthermore, in S2, a global SQL interception module is constructed to intercept all database queries issued in the form of JDBC / ODBC. When the middleware is integrated into the Java project in the form of Maven dependency, all database queries issued in the form of mybatis will enter this middleware for processing, thereby achieving unified management and control of all database queries.

[0033] Further, the S3 comprises the following steps:

[0034] S31: Preliminary processing of the input original SQL query string through a lexical analyzer;

[0035] S32: The validated and annotated AST is fed into the query optimizer.

[0036] Furthermore, in S4, according to the parsing result of the SQL parser, a query statement of the corresponding database is dynamically generated. For a relational database, a standard SQL statement is generated; for a non-relational database, the standard SQL is converted into a corresponding non-relational database API call.

[0037] The advantages and positive effects of the present invention are:

[0038] The present invention realizes the need for cross-database data use in the application system in the petroleum industry through middleware. It not only realizes cross-database query of relational and non-relational databases, unifies database access and service modes, supports large-scale application systems and platforms containing various types of data storage products, greatly improves the development efficiency of application systems, and reduces the learning threshold for R&D personnel. At the same time, through the multi-tenant and authorization management provided, it is possible to monitor and review the row-level security of data, effectively protect sensitive data and prevent data leakage. In addition, the present invention also promotes the innovation of cross-database technology and data sharing, and reduces the IT maintenance costs of enterprises. The present invention has shown significant beneficial effects in many aspects such as data processing efficiency, user experience, security, and technological innovation. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] Figure 1 4 is a system architecture diagram of an embodiment of the present invention.

[0040] Figure 2 It is an overall flow chart of an embodiment of the present invention.

[0041] Figure 3 The data authorization flow chart of the embodiment of the present invention.

[0042] Figure 4 It is a technical architecture diagram of an embodiment of the present invention.

[0043] Figure 5 This is a flow chart of cross-database hybrid query according to an embodiment of the present invention.

[0044] Figure 6 It is a schematic diagram of the application effect of an embodiment of the present invention. DETAILED DESCRIPTION

[0045] The technical solution of the present invention will be described clearly and completely below in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0046] In the description of the present invention, it should be noted that the terms "center", "upper", "lower", "left", "right", "vertical", "horizontal", "inner", "outer", etc., indicating the orientation or positional relationship, are based on the orientation or positional relationship shown in the drawings, and are only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore cannot be understood as limiting the present invention. In addition, the terms "first", "second", and "third" are used for descriptive purposes only, and cannot be understood as indicating or implying relative importance.

[0047] In the description of the present invention, it should be noted that, unless otherwise clearly specified and limited, the terms "installed", "connected", and "connected" should be understood in a broad sense, for example, it can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection, or it can be indirectly connected through an intermediate medium, or it can be the internal communication of two components. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0048] The embodiments of the present invention are further described below in conjunction with the accompanying drawings:

[0049] like Figure 1 As shown, a hybrid parsing middleware based on cross-type databases includes:

[0050] Data source management module: The data source management module performs unified registration and management of all databases involved, shielding the specific configuration and deployment location of the data source. Specifically, the database includes relational databases and various non-relational data storage or data exchange products. Relational databases include but are not limited to Oracle, MySQL, SqlServer, Postgresql, ClickHouse, Kudu based on Impala, etc. Various non-relational data storage or data exchange products include but are not limited to MinIO, Kafka, Redis, etc. Adapters that implement different database connections and interface operations for each database.

[0051] The non-relational database object mapping module maps the related objects in the non-relational data storage to SQL standard objects to support SQL parsing correspondence. Specifically, the non-relational database object mapping module includes an object mapping submodule for MinIO document storage, an object mapping submodule for Kafka message storage, and an object mapping submodule for Redis.

[0052] Among them, the object mapping of MinIO document storage: the tenant and document type (such as document, picture, data body, etc.) are combined and mapped to the table object in SQL, the storage path (including multiple levels) is mapped to the column object in SQL, and the document body is mapped to the value object of SQL.

[0053] Object mapping for Kafka message storage: Tenants and topics are combined and mapped to table objects in SQL, keys are mapped to column objects in SQL, and values ​​are mapped to value objects in SQL. If partitions exist, partitions can be mapped to schema objects in SQL.

[0054] Object mapping for Redis: Since redis is a key-value pair storage mode, the table object in SQL can be mapped to a fixed value (such as redis_t), the key object can be mapped to the column object in SQL, and the value object can be mapped to the value object in SQL.

[0055] The multi-tenant authorization management module combines tenants with the database to perform row-level authorization. Specifically, the multi-tenant authorization management module is established through the following steps:

[0056] First, extract and define metadata of data sources to form a unified metamodel;

[0057] Secondly, determine the master data table and clarify the parent-child or association relationship between the master data;

[0058] Next, determine the organization field in the master data table and use it to bind the logged-in tenant's configuration permissions to authorize and filter data.

[0059] Finally, determine the effect object fields in the business table, and associate the effect object fields of each business table with the master data table of the corresponding type by foreign key.

[0060] The context-free grammar constructs a prediction analysis table. In the top-down grammar analysis process, the overall, mapping table, and storage stack architecture are constructed. After the LL(1) grammar is formed, the prediction analysis table of the production or error is determined based on the stack and the current symbol to achieve automatic grammar analysis. Specifically, the expansion rule when the non-terminal symbol encounters the input symbol is realized by establishing an expansion mapping rule table of the non-terminal symbol corresponding to the terminal symbol.

[0061] The extended BNF left recursion elimination module rewrites and eliminates rules containing left recursion through extended BNF representation. Specifically, in the extended BNF representation, curly brackets {α} are used to indicate that the symbol string α can appear 0 or more times, that is, α*; square brackets [α] are used to indicate that the appearance of α is optional, which is used to indicate optional symbol strings; parentheses can be used to extract factors in the rules.

[0062] The distributed transaction control module is based on message-driven TCC distributed transaction control. By establishing a global transaction management center, it does not rely on the transaction processing support of different underlying databases.

[0063] By customizing the transaction processing logic of the three stages of prepare, commit, and rollback. Specifically, a message mechanism is established to drive subsequent actions by receiving messages about the execution results of different stages of TCC.

[0064] In the Try phase, the business operation attempts to perform certain transactional operations, records the operations as a preparatory operation, and does not directly submit the results to the database or other persistent storage system, so that the commit or rollback can be performed as needed in the Confirm or Cancel phase.

[0065] In the Confirm phase, if the operation in the Try phase is successfully executed, the operation is confirmed and submitted to the database or other persistent storage system, that is, the preparatory operation of the Try phase is submitted and turned into the final stable state.

[0066] In the Cancel phase, if the operation in the Try phase fails or needs to be rolled back for some reason, the Cancel operation is executed to roll back or cancel the preparatory operation in the Try phase, that is, to roll back the preparatory operation in the Try phase and restore it to its original state.

[0067] like Figure 2 As shown, the present invention also provides a query method based on cross-type database hybrid parsing middleware, using the above-mentioned cross-type database hybrid parsing middleware, including the following steps:

[0068] S1: Build a database management module. Specifically, build a unified database management module, which is responsible for collecting and managing all database connection information that needs to be accessed. To ensure security, this information is encrypted with national secrets and stored in the relational database Renmin University of China Golden Warehouse, and data authorization is defined, such as Figure 3 As shown, thereby preventing illegal acquisition and use.

[0069] S2: Build a global SQL interception module to intercept all queries to the database through JDBC / ODBC. After the middleware is introduced into the Java project as a Maven dependency, all queries to the database through mybatis will be processed by the middleware, such as Figure 4 The middleware is located at an intermediate level in the entire introduced project, and mainly provides data query capabilities across database types for the project that introduces the middleware.

[0070] S3: Implement an SQL parser inside the middleware. This parser can identify and parse the query requests submitted by users, whether these requests are for relational or non-relational databases, and can analyze the database, data table, field, and query conditions in the query request. The parsing process includes lexical analysis, syntax analysis, semantic analysis, and query optimization, and finally generates an efficient logical execution plan. The specific steps are as follows:

[0071] First, the input raw SQL query string is preliminarily processed by the lexical analyzer. In this step, the query string is carefully split into a series of lexical units, or tokens, such as keywords, identifiers, constants, and operators. These tokens are sent to the syntax analyzer for deeper combination and parsing. Following the grammatical rules of SQL, the tokens are carefully organized into an abstract syntax tree. This AST not only reflects the structure of the query statement, but also reveals its internal logical relationship, providing a clear context for subsequent semantic analysis. Entering the semantic analysis stage, a series of verification and annotation operations are performed on the AST. It ensures that the tables and columns referenced in the query actually exist, the data types match correctly, and the entity executing the query has sufficient permissions. In addition, the metadata-based annotation process adds rich contextual information to the AST, such as table and column statistics, filter condition selectivity, etc., which is crucial for subsequent query optimization.

[0072] Next, the verified and annotated AST is sent to the query optimizer. Using a series of optimization rules and cost models, the AST is carefully transformed and optimized. It may rewrite the query statement, select the optimal index, determine the order and method of connection, etc., to generate an efficient logical execution plan. This plan aims to return the query results with the least resource consumption and the shortest execution time. When parsing, it will first find out which are the SQL statements of relational databases and which are the SQL statements of non-relational databases. The premise of identifying the data source and is that when writing SQL statements, they must be written in the format of 'database.table name'. In this way, the type of database can be determined in combination with the database information in the database management module, such as Figure 5 shown.

[0073] S4: Dynamically generate query statements. Specifically, dynamically generate query statements for the corresponding database based on the parsing results of the SQL parser. For relational databases, dynamically generate standard SQL statements for the relational database. For non-relational databases, replace the parsed standard SQL with the corresponding non-relational database API based on its meaning, thereby obtaining the corresponding query statement.

[0074] S5: Get database connection information and establish a connection. Specifically, the middleware can obtain the database connection information parsed in S3 from the database management module. The obtained database connection information needs to be decrypted to get the plain text. In the future, it is necessary to be able to connect to various databases. This requires implementing a database connection adapter based on JDBC / ODBC in the middleware. After implementation, the database connection information can be used to connect to the corresponding database.

[0075] S6: Send query statements and receive execution results. Specifically, after establishing a database connection, the statements generated in S4 are sent to the corresponding database for execution through the middleware, and the execution results are received. This allows users to obtain data by simply submitting SQL query requests without having to pay attention to the database type and connection method.

[0076] S7: Integrate and return execution results. Specifically, the middleware integrates the received execution results to ensure the uniformity of the data format, and returns the integrated execution results to the user after encryption through a unified data interface, so that the user can easily obtain data query results across database types.

[0077] Throughout the implementation process, we always pay attention to data security. The middleware uses advanced data encryption technology to ensure the security of data during transmission and storage.

[0078] In general, if Figure 6 As shown, the specific implementation method of the present invention is to realize the cross-database query function of relational database and non-relational database by building a powerful middleware, providing efficient and secure data access and query solutions for enterprises such as the petroleum industry that need to process multiple types of databases at the same time.

[0079] The advantages and positive effects of the present invention are:

[0080] The present invention realizes the need for cross-database data use in the application system in the petroleum industry through middleware. It not only realizes cross-database query of relational and non-relational databases, unifies database access and service modes, supports large-scale application systems and platforms containing various types of data storage products, greatly improves the development efficiency of application systems, and reduces the learning threshold for R&D personnel. At the same time, through the multi-tenant and authorization management provided, it is possible to monitor and review the row-level security of data, effectively protect sensitive data and prevent data leakage. In addition, the present invention also promotes the innovation of cross-database technology and data sharing, and reduces the IT maintenance costs of enterprises. The present invention has shown significant beneficial effects in many aspects such as data processing efficiency, user experience, security, and technological innovation.

[0081] The above is a detailed description of an embodiment of the present invention, but the content is only a preferred embodiment of the present invention and cannot be considered to limit the scope of implementation of the present invention. All equivalent changes and improvements made within the scope of the present invention should still fall within the scope of the patent coverage of the present invention.

Claims

1. A hybrid parsing middleware based on cross-type databases, characterized by: include, A data source management module, which performs unified registration and management of all databases involved, shielding the specific configuration and deployment location of the data source; The non-relational database object mapping module maps the related objects in the non-relational data storage to SQL standard objects to support SQL parsing correspondence; The multi-tenant authorization management module combines tenants with databases to perform data row-level authorization; The context-free grammar constructs a prediction analysis table. In the top-down syntax analysis process, the overall, mapping table, and storage stack architecture are constructed. After the LL(1) grammar is formed, the prediction analysis table of the production or error is determined based on the stack and the current symbol to achieve automatic syntax analysis. Extended BNF left recursion elimination module, for rules containing left recursion, rewrite and eliminate them through extended BNF representation; The distributed transaction control module is based on message-driven TCC distributed transaction control. By establishing a global transaction management center, it does not rely on the transaction processing support of different underlying databases.

2. According to claim 1, a hybrid parsing middleware based on cross-type databases is characterized in that: The database includes relational databases and various non-relational data storage or data exchange products. The relational databases include but are not limited to Oracle, MySQL, SqlServer, Postgresql, ClickHouse, and Kudu based on Impala. The various non-relational data storage or data exchange products include but are not limited to MinIO, Kafka, and Redis.

3. A cross-type database hybrid parsing middleware according to claim 1 or 2, characterized in that: The non-relational database object mapping module includes an object mapping submodule for MinIO document storage, an object mapping submodule for Kafka message storage, and an object mapping submodule for Redis.

4. The hybrid parsing middleware based on cross-type databases according to claim 1 or 2, characterized in that: The multi-tenant authorization management module is established through the following steps: First, extract and define metadata of data sources to form a unified metamodel; Secondly, determine the master data table and clarify the parent-child or association relationship between the master data; Next, determine the organization field in the master data table and use it to bind the logged-in tenant's configuration permissions to authorize and filter data; Finally, determine the effect object fields in the business table, and associate the effect object fields of each business table with the master data table of the corresponding type by foreign key.

5. The hybrid parsing middleware based on cross-type databases according to claim 1 or 2, characterized in that: In the extended BNF representation, curly brackets {α} are used to indicate that the symbol string α may appear 0 or more times, that is, α*; square brackets [α] are used to indicate that the appearance of α may or may not occur, and are used to indicate optional symbol strings; parentheses are used to extract factors in the rules.

6. The cross-type database hybrid parsing middleware according to claim 1 or 2, characterized in that: The distributed transaction control module establishes a message mechanism to drive subsequent actions by receiving messages about the execution results of different stages of TCC. In the Try phase, the business operation attempts to perform certain transactional operations, records the operations as a preparatory operation, and does not directly submit the results to the database or other persistent storage system, so that the commit or rollback can be performed as needed in the Confirm or Cancel phase; In the Confirm phase, if the operation in the Try phase is successfully executed, the operation is confirmed and submitted to the database or other persistent storage system, that is, the preparatory operation in the Try phase is submitted and turned into the final stable state; In the Cancel phase, if the operation in the Try phase fails or needs to be rolled back for some reason, the Cancel operation is executed to roll back or cancel the preparatory operation in the Try phase, that is, to roll back the preparatory operation in the Try phase and restore it to its original state.

7. A query method based on cross-type database hybrid parsing middleware, using the cross-type database hybrid parsing middleware according to any one of claims 1 to 6, characterized in that: The following steps are included: S1: Build database management module; S2: Build a global SQL interception module; S3: Implement SQL parser inside the middleware; S4: Dynamically generate query statements; S5: Obtain database connection information and establish a connection; S6: Send query statements and receive execution results; S7: Integrate and return the execution results.

8. The query method based on cross-type database hybrid parsing middleware according to claim 7 is characterized in that: In S2, a global SQL interception module is constructed to intercept all database queries issued in the form of JDBC / ODBC. When the middleware is integrated into the Java project in the form of Maven dependency, all database queries issued in the form of mybatis will enter this middleware for processing, thereby achieving unified management and control of all database queries.

9. A query method based on cross-type database hybrid parsing middleware according to claim 7 or 8, characterized in that: The S3 comprises the following steps, S31: Preliminary processing of the input original SQL query string through the lexical analyzer; S32: The validated and annotated AST is fed into the query optimizer.

10. A query method based on cross-type database hybrid parsing middleware according to claim 7 or 8, characterized in that: In S4, according to the parsing result of the SQL parser, a query statement of the corresponding database is dynamically generated. For a relational database, a standard SQL statement is generated; for a non-relational database, the standard SQL is converted into a corresponding non-relational database API call.

Images