A cross-platform universal database access middleware

Abstract

The present invention discloses a cross-platform universal database access middleware, which includes: a universal data access component, a network component, and a database business processing component. The universal data access component provides a universal database access interface for upper-layer applications and completes SQL statement preprocessing; the network component implements communication between the client and the server and access connection pool management; the database business processing component parses and executes SQL statements, completes related business operations and accesses the underlying database. The present invention provides a set of database access interface specifications for upper-layer applications, which can adapt to all databases with ODBC and JDBC access engines; and focuses on adapting domestic platforms and domestic databases, preserving their functional implementation and characteristics to the greatest extent possible. Compared with traditional universal database access interfaces, the present invention improves the database access efficiency of upper-layer applications.

Description

Technical Field

[0001] The present invention relates to the technical field of domestic database access, and in particular to a cross-platform universal database access middleware. Background Art

[0002] With the continuous advancement of database technology, database applications are becoming increasingly widespread, penetrating into various industries. In the development of practical systems, applications often need to efficiently access several different database management systems, or even different versions of the same database, across different operating systems. Overly complex database access processes and table query statements create development difficulties and software compatibility issues for application developers. There is an urgent need for a universal database access system that can mask the underlying differences among various databases, provide transparency into database business logic and resource scheduling, and provide unified development interface specifications.

[0003] Existing general-purpose database access technologies are already compatible with the basic functions of various databases, providing users with a unified secondary development interface. Mainstream database access technologies have good compatibility, but sacrifice the efficiency of basic business execution such as access and query, and are unable to leverage the advantages and characteristics of various databases. Database-specific technologies such as read-write separation, sharding, and virtual routing are difficult to utilize. At the same time, traditional database general-purpose access technologies do not support cross-platform use, especially without in-depth adaptation to mainstream domestic platforms. There is also a lack of adaptability development for mainstream domestic database access modes, business additions, deletions, modifications, and connection management functions. Summary of the Invention

[0004] In order to solve the above problems, the present invention proposes a cross-platform universal database access middleware, including a universal data access component, a network component and a database business processing component; the universal data access component provides a universal database access interface for upper-level applications and completes the preprocessing of SQL statements; the network component is responsible for realizing communication between the client and the server and access connection pool management; the database business processing component parses and executes SQL statements to complete related business operations and access to the underlying database.

[0005] Furthermore, the universal data access component provides a universal database access interface for upper-layer applications, which is specifically composed of five objects: data source, transaction, command, row set and error.

[0006] Furthermore, the network component includes two parts, the client and the server, and cross-platform network communication is achieved between the two parts through a TCP-based Socket mechanism; a connection-oriented concurrent multi-threaded service mechanism is adopted, providing a service thread for each client request, and managing database connections through a connection pool mechanism.

[0007] Furthermore, the database business processing component uses the AST syntax tree model to parse SQL statements, decomposes them into basic database business operations for a single database and a single table, and executes them using concurrent processing and sharding technologies.

[0008] Furthermore, domestic databases including DAMO, Shentong and Jincang were adapted in terms of universal access interface, SQL statement parsing and business logic processing.

[0009] Compared with the prior art, the present invention has the following beneficial effects:

[0010] (1) Provide a set of database access interface specifications for upper-layer applications, adapting to all databases with ODBC and JDBC access systems;

[0011] (2) We focused on adapting the domestic platform and domestic database, preserving their functional implementation and characteristics to the greatest extent possible, and improving the performance of business processing such as addition, deletion, modification, and query;

[0012] (3) Bring convenience to every software developer, reduce the manpower and time costs of the project, improve the efficiency of software development, and enhance the security and stability of the software system. BRIEF DESCRIPTION OF THE DRAWINGS

[0013] Figure 1 This is a business logic flow chart of the universal database access middleware of the present invention.

[0014] Figure 2 This is a schematic diagram of the objects and methods included in the universal data access component of the present invention.

[0015] Figure 3 This is a logical flow chart of the database business processing component.

[0016] Figure 4 This is a diagram of the AST syntax tree.

[0017] Figure 5 This is a diagram showing how to segment SQL statements. DETAILED DESCRIPTION

[0018] In order to solve practical application problems such as compatibility and efficiency encountered in existing general database access technologies, the present invention designs a solution that can improve the compatibility and performance of general database access technologies, achieves comprehensive adaptation on domestic platforms, and unifies access interface specifications at the application level, shielding the differences between underlying database systems for upper-level applications. It is portable, scalable, and can cross various operating system platforms, truly realizing quick, simplified, flexible and efficient use of databases.

[0019] The specific implementation of the cross-platform universal database access middleware of the present invention is described in detail below with reference to the accompanying drawings.

[0020] Example 1

[0021] like Figure 1 As shown in the figure, general database access middleware mainly consists of three parts: general data access components, network components, and database business processing components. The network components include client network components and server network components. In terms of overall system architecture, general database access middleware adopts a client / server architecture, with general data access components and client network components deployed on the client side, and server network components and database business processing components deployed on the server side.

[0022] The general data access component provides a universal database access interface for upper-layer applications, preprocessing SQL statements, including initial parameters for business execution, such as database table information, types, and locations. The network component is responsible for client-server communication and connection pool management. The database business processing component is responsible for parsing and executing SQL statements, completing related business operations and accessing the underlying database. The functions and design of each component are detailed below.

[0023] a) Common data access components

[0024] The universal data access component provides a universal database access interface for upper-level applications. These interfaces are more abstract and simpler than ODBC, OLEDB, and ADO. They are mainly composed of five objects: data source (Environment), transaction (Transaction), command (Command), row set (Rowset), and error (Error). Figure 2 Lists the main methods contained in each object.

[0025] The pseudo code of a data access process based on the general database access interface is as follows:

[0026] pEnv = new DBO-Environment; / / Establish resources required for database access

[0027] pDataSource->Connect(DataSource,Name,PWD); / / Establish a connection with the database

[0028] pDataSource->TransCreate(&pTran);

[0029] pTran->TranBegin(); / / Start transaction

[0030] pTran->CreateCommand(&pComm);

[0031] pComm->SetCommandText(szCommandText)

[0032] pComm->Execute(&pRowset) / / Execute command

[0033] pRowset->MoveFirst()

[0034] PTran->Commit(); / / Commit transaction

[0035] pTran->DisConnect(); / / disconnect

[0036] When calling CDataSource::Connect(), the upper-level application does not need to know any specific information about the database where the data it accesses is located, such as database type, version, and platform. It only needs to enter the three mapped parameters: data source name, user name, and password, which realizes database transparency and simplified programming.

[0037] b) Network components

[0038] The network component is responsible for transmitting the user's database access request to the database server safely, accurately and quickly, and transmitting the returned results to the user safely, accurately and quickly.

[0039] The network component consists of two parts: the client and the server. These two parts communicate across platforms via a TCP-based socket mechanism. To ensure the security of data transmission (both requests and access results), the network component uses an internal communication mechanism to decouple database access from specific DBMS network communication products (such as Oracle's SQL NET) and client products, enhancing system security. The network component also ensures data transmission security through data encryption and security restrictions, completely transparent to upper-level developers.

[0040] The network component uses a connection-oriented, concurrent, multi-threaded service mechanism, providing a service thread for each client request. Furthermore, the network component manages database connections through a connection pool mechanism. This mechanism saves significant time in establishing new database connections by maintaining actual database connections in the connection pool, significantly increasing client-to-database server connection speeds and client response times. Furthermore, it enables shared database connection resources by ensuring that each client shares the same actual connection in the connection pool whenever possible.

[0041] c) Database business processing components

[0042] The database business processing component is the core of the access system. The specific logic flow chart is as follows: Figure 3 When the network component passes pre-processed SQL and other database management commands to the database business processing component, they are intercepted by the SQL processing module. After syntax and lexical analysis, the SQL statement is converted into a syntax tree to facilitate further execution and operation.

[0043] 1) SQL parsing

[0044] The system needs to use an SQL parser to parse SQL and obtain an AST (Abstract Syntax Tree). Through the syntax tree, a series of business execution specific information such as the current SQL type, query field name, database table name, condition judgment, parameter information, etc. can be obtained. It is roughly divided into two parts: syntax analysis and lexical analysis. Take the following statement as an example:

[0045]

[0046] After lexical analysis, we can get the operation information such as Select, Insert, Delete, as well as specific database tables and field information such as Time and Table1, and then translate it into the following according to the AST syntax tree model: Figure 4 The syntax tree of .

[0047] The main parsing method used in this invention is predictive analysis. It only needs to traverse from the first to the last character to complete lexical parsing and grammatical analysis simultaneously, and also construct a syntax tree. The SQL syntax judgment standard adopts SQL-92 and SQL-99, and supports commonly used non-domestic and domestic databases.

[0048] 2) SQL routing and execution

[0049] SQL routing is the basis for implementing sharding, which specifies the rules by which data should be routed to different single databases and tables. The SQL routing in the universal database access middleware of the present invention is essentially the implementation of a script expression. By calculating the built-in expression, the result of system uniqueness is directly obtained. The calculation method includes hash modulo, date rotation, etc. Routing rules are mainly divided into two categories: library rules and table rules. Whether executing INSERT, UPDATE, DELETE, or SELECT statements, a globally unique ID field must be included for positioning and binding. The generation of the ID field will be explained in detail below.

[0050] After the SQL statement is analyzed through routing, it can be sliced into multiple single SQL statements bound to a single database and table. The present invention designs a special thread pool for concurrently executing the single SQL statements to improve the execution efficiency. Figure 5 shown.

[0051] If the execution of a single SQL statement fails, the execution of other statements in the thread pool will be collectively terminated, and the business will be automatically rolled back, and the exception information of the execution failure will be fed back to the upper layer.

[0052] 3) Database and table sharding design

[0053] The sharding technology used to execute the parsed results of SQL statements can horizontally expand the database's storage capacity, improve the database's write efficiency, and optimize database service performance. However, this requires more computing resources and hardware support for the server. The sharding design of the present invention adds compatibility design for various databases to traditional technical implementations, mainly adapting and optimizing basic business functions and distributed transaction processing.

[0054] The sharding of databases and tables is transparent to top-level users. Users still operate the database in the same way as a single database and single table. Therefore, when users perform batch add, delete, modify, and query operations, the access system will split each batch operation. Take the following SQL statement as an example:

[0055]

[0056] Because the system performs database and table sharding for each execution, this SQL operation will be split and its target will be determined as a single table database, as shown below:

[0057] Insert into user(id,name)values(a1,b1) Insert into user(id,name)values(a2,b2)

[0058] First, the field values to be inserted are analyzed, identifying a1 and a2. Library routing and table routing are then used to determine the library and table directories into which this record should be inserted. When a SQL statement is rewritten and split into multiple statements, they are not executed sequentially but concurrently. The result set is then aligned using standard alignment for collection and feedback.

[0059] 4) Distributed business processing

[0060] In the scenario of sharding databases and tables, the database's auto-incrementing primary key cannot be used when processing database business, and a unified distributed primary key ID generator must be used. The ID generator algorithm designed by the present invention refers to the generation schemes of mainstream ZK and MySQL, and its basic data content is 12-digit date, 10-digit IP, 6-digit random sequence and 4-digit database identification extension bit. This design is mainly to ensure the uniqueness and increasing trend of the ID. The 12-digit date generation strategy can be used until 2099, and the time part is prepended to ensure the increasing trend; the 12-digit IP is converted to decimal using the IP to Decimal algorithm to enhance the uniqueness of the ID; the 6-digit random sequence number means that high-concurrency scenarios support the simultaneous generation of up to 1 million IDs, and the leading digits are filled with 0. The 4-digit database identification bit is used for adaptation and expansion of domestic databases.

[0061] The goal of distributed database processing is to ensure data consistency across distributed databases. However, cross-database processing can encounter various uncontrollable issues, such as the permanent downtime of individual nodes. Achieving the same ACID compliance as a single server is unfeasible. This embodiment of distributed database processing employs weak consistency to achieve eventual consistency in results, rather than adopting strong consistency models and algorithms. At the protocol level, Paxos and Raft algorithms are used to prevent data inconsistencies.

[0062] d) Domestic database adaptation

[0063] Most common non-domestic databases are designed and developed with reference to the database business standards released by Oracle. However, the research and development of domestic databases started relatively late, and Oracle has developed into a very heavyweight database product. Its complex rules and design standards are too redundant for emerging domestic databases. Therefore, domestic databases generally only choose to adapt to Oracle in terms of underlying database design such as metadata, library table structure, table space, etc. to ensure that data can be migrated in parallel. Apart from this, the database system design is very different from Oracle. The domestic database adaptation work of the present invention has obtained technical support from a number of domestic database manufacturers (Shentong, Dameng, Jincang, etc.), and has adapted domestic databases in several aspects such as universal access interface, SQL statement parsing, and business logic processing, so that users can use domestic databases and other common non-domestic databases without distinction.

[0064] 1) Access interface adaptation

[0065] The universal data access interface designed based on the standard database business OCI interface specification has good adaptability to the underlying interfaces of common databases such as Oracle and MySQL, but it cannot achieve seamless docking for domestic databases. Because the implementation of basic interfaces such as connection, binding, offset, etc. at the bottom of the domestic database all has its own unique set of standards. Taking Shentong's ACI interface standard as an example, it requires the upper layer to pass in 7 binding parameters when establishing a basic database connection, but the OCI standard only needs to pass in 3, and the rest are handled by the database itself. Therefore, the universal access interface of the present invention is adapted to the special interfaces of several types of domestic databases based on the reference OCI interface specification to ensure that its functions and database characteristics are not lost.

[0066] 2) Business logic adaptation

[0067] The SQL execution systems of domestic databases have different logics in executing database services and do not use the common Oracle standards. Taking the Shentong database as an example, Shentong's ACI interface will additionally bind the query search area based on the user's parameters when performing cross-database joint queries, and then merge the result set according to the specified cross-database method, and finally return the query results. This is different from the Oracle standard, so the same SQL statement will produce different results when executed in different databases. The present invention, through adaptation to the special services of domestic databases, shields users from the differences caused by the differences in business logic processing of various databases at the upper interface, thereby ensuring the efficiency and correctness of universal access.

[0068] This invention uses C++ language and is based on the MyCat development environment (cross-platform support) to design a set of general database access middleware that can adapt to three-tier / multi-tier structures, supporting common databases such as Oracle, MySql, and domestic databases such as DAMO and Shentong.

[0069] This system has done in-depth work on domestic adaptation, supporting the instruction sets and specifications of chips such as Feiteng, Huaxin, and Hygon, and supporting various operating system platforms including Windows / Linux / Solaris / Kylin / virtualization platforms. While maintaining the performance and advantages of each database, it can shield the differences between the management systems of different databases and different versions of the same database. Compared with the traditional general database access interface, it improves the database access efficiency of the application.

[0070] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A cross-platform universal database access middleware, characterized in that: A three-tier architecture is adopted, including a general data access component, a network component, and a database business processing component; the general data access component provides a general database access interface for upper-layer applications and completes SQL statement preprocessing; the network component is responsible for realizing communication between the client and the server and accessing the connection pool management; the database business processing component parses and executes SQL statements, completes related business operations and accesses the underlying database; the general data access component provides a general database access interface for upper-layer applications, which is specifically composed of five objects: data source, transaction, command, row set, and error. The data source object includes Connect, DisConnect, TransCreate, and SetCommand methods; the transaction object includes TransactionBegin, TransactionEnd, Commit, Rollback, and SetCommand methods; the command object includes Execute, SetCommandText, and GetCommandText methods; the row set object includes GetRow, InsertRow, DeleteRow, UpdateRow, RefreshRowset, MoveFirst, MoveLast, and MovePosition methods; the error object includes IsError, GetErrorCode, and GetErrorMsg methods; Domestic software and hardware platforms and domestic databases have been adapted in terms of general access interfaces, SQL statement parsing and business logic processing. The domestic software and hardware platforms include Feiteng, Huaxin, Hygon domestic CPUs and Zhongbad Kylin domestic operating system, and domestic databases include DAMO, Shentong and Jincang.

2. The cross-platform universal database access middleware according to claim 1, characterized in that: The network component includes two parts, the client and the server, which realize cross-platform network communication between the two parts through the TCP-based Socket mechanism; a connection-oriented concurrent multi-threaded service mechanism is adopted to provide a service thread for each client request, and a connection pool mechanism is used to manage database connections.

3. The cross-platform universal database access middleware according to claim 2, characterized in that: The database business processing component uses the AST syntax tree model to parse SQL statements, decomposes them into basic database business operations for a single database and table, and uses concurrent processing and sharding technologies for execution. It also uses a distributed primary key ID generator, Paxos, and Raft algorithms to implement distributed business processing.

Images