CN114925142A

CN114925142A - Multi-type database compatibility method, device, equipment and medium of ORM framework

Info

Publication number: CN114925142A
Application number: CN202210421544.XA
Authority: CN
Inventors: 秦雨村
Original assignee: Shandong Inspur Genersoft Information Technology Co Ltd
Current assignee: Shandong Inspur Genersoft Information Technology Co Ltd
Priority date: 2022-04-21
Filing date: 2022-04-21
Publication date: 2022-08-19

Abstract

The embodiment of the specification discloses a multi-type database compatible method of an ORM framework, which comprises the following steps: in an ORM frame, SQL sentences respectively supported by multiple types of databases are obtained, and personalized SQL sentences different from other databases are respectively marked in each type of database; creating an SQL sentence conversion tool for carrying out SQL sentence conversion among the multiple types of databases according to the SQL sentences and the personalized SQL sentences respectively supported by the multiple types of databases; setting the SQL statement type output by the SQL statement conversion tool according to the type of a database built in the ORM frame; and developing a corresponding application function according to the SQL statement type, and applying the application function to the ORM framework so as to realize that the ORM framework is compatible with SQL statements of various types of databases.

Description

Multi-type database compatible method, device, equipment and medium of ORM framework

Technical Field

The present disclosure relates to the field of computer technologies, and in particular, to a method, an apparatus, a device, and a medium for multi-type database compatibility of an ORM framework.

Background

In the big data era, the types and sizes of databases are continuously expanding, which brings certain challenges to database management. In social production and life, the application range of the database is gradually enlarged, the efficiency of database development and application is improved, and the method is the key for guaranteeing the efficient operation of the social production and life in China. The ORM (object Relational mapping) framework employs metadata to describe the details of the object and relationship mappings. As long as the mapping relationship between the persistence class and the table is provided, the ORM framework can refer to the information of the mapping file at the running time to persist the object into the database.

In the prior art, the ORM framework cannot be well compatible with various types of databases, and in practical application, SQL statements which cannot be compatible with the databases need to be converted by means of an external tool, so that user experience is affected.

Disclosure of Invention

One or more embodiments of the present specification provide a method, an apparatus, a device, and a medium for multi-type database compatibility of an ORM framework, which are used to solve the following technical problems:

One or more embodiments of the present disclosure adopt the following technical solutions:

one or more embodiments of the present specification provide a multi-type database compatibility method of an ORM framework, the method including:

in an ORM framework, SQL sentences respectively supported by the multiple types of databases are obtained, and personalized SQL sentences different from other databases are respectively marked in each type of database;

creating an SQL statement conversion tool for carrying out SQL statement conversion among the multiple types of databases according to the SQL statements respectively supported by the multiple types of databases and the personalized SQL statements;

setting the SQL statement type output by the SQL statement conversion tool according to the type of a database built in the ORM frame;

and developing a corresponding application function according to the SQL statement type, and applying the application function to the ORM framework so as to realize that the ORM framework is compatible with SQL statements of various types of databases.

Further, after applying the application function to the ORM framework, the method further includes:

determining clause types in the SQL sentences, and setting optimization rules of different clause types;

after an appointed SQL statement is input into the ORM framework, converting the appointed SQL statement into a first SQL statement of the SQL statement type through the SQL statement conversion tool;

and adjusting the execution sequence of the first SQL statement according to the optimization rule.

and after the specified SQL statement is input into the ORM framework, performing serialization conversion and deserialization conversion on the specified SQL statement.

setting a normal form of a database table in the ORM framework, specifically comprising:

setting all fields in the database table to be single attributes so that the database table is a two-dimensional table;

and setting a service primary key for the database table so that the database table does not have a non-primary key column.

when the database of the ORM frame executes a plurality of index instructions, determining a data ID through the plurality of index instructions, and inquiring corresponding data in the database of the ORM frame according to the data ID.

when the memory space of the server of the ORM frame is larger than a preset threshold value and the performance requirement is high, performing read-write analysis on the database of the ORM frame;

distributing read requests to a plurality of slave servers to relieve the database of stress.

and converting the operational characters in the application function into SQL statements of the database in the ORM framework so as to achieve the operation of the operational object to the database level.

One or more embodiments of the present specification provide a multi-type database compatible apparatus of an ORM framework, the apparatus comprising:

the acquisition unit is used for acquiring SQL sentences supported by various types of databases respectively in an ORM frame and marking personalized SQL sentences different from other databases in each type of database;

the creating unit is used for creating an SQL statement conversion tool for carrying out SQL statement conversion among the multiple types of databases according to the SQL statements respectively supported by the multiple types of databases and the personalized SQL statements;

the setting unit is used for setting the SQL statement type output by the SQL statement conversion tool according to the type of a database built in the ORM frame;

and the application unit is used for developing a corresponding application function according to the SQL statement type and applying the application function to the ORM frame so as to realize that the ORM frame is compatible with SQL statements of various types of databases.

at least one processor; and (c) a second step of,

a memory communicatively coupled to the at least one processor; wherein,

the memory stores instructions executable by the at least one processor to cause the at least one processor to:

in an ORM frame, SQL sentences respectively supported by multiple types of databases are obtained, and personalized SQL sentences different from other databases are respectively marked in each type of database;

One or more embodiments of the present specification provide a non-transitory computer storage medium storing computer-executable instructions configured to:

The embodiment of the specification adopts at least one technical scheme which can achieve the following beneficial effects: in the embodiment of the present specification, by acquiring SQL statements respectively supported by multiple types of databases in an ORM framework, the multiple types of databases at this time may be updated and added, so that the ORM framework supports more types of databases. Meanwhile, the embodiment of the specification can mark personalized SQL statements different from other databases in each type of database, so that an SQL statement conversion tool for performing SQL statement conversion between the multiple types of databases is created through the SQL statements and the personalized SQL statements respectively supported by the multiple types of databases, so as to convert subsequently input SQL statements, and the ORM framework can be well compatible with the different types of databases to process the SQL statements of the different types of databases. In addition, one or more database types built in the ORM framework can convert the input SQL statement into the SQL statement supported by the database built in the ORM framework by setting the SQL statement type output by the SQL statement conversion tool. In addition, an application function is developed according to the SQL sentence type output by the SQL sentence conversion tool, and the application function is applied to the ORM framework, so that the ORM framework can be compatible with the SQL sentences of various types of databases.

Drawings

In order to more clearly illustrate the embodiments of the present specification or the technical solutions in the prior art, the drawings needed to be used in the description of the embodiments or the prior art will be briefly introduced below, it is obvious that the drawings in the following description are only some embodiments described in the present specification, and for those skilled in the art, other drawings can be obtained according to the drawings without any creative effort. In the drawings:

FIG. 1 is a flow diagram illustrating a multi-type database compatibility method of an ORM framework according to one or more embodiments of the present disclosure;

FIG. 2 is a schematic diagram of an ORM framework according to one or more embodiments of the present disclosure;

FIG. 3 is a block diagram of a multi-type database compatible device of an ORM framework according to one or more embodiments of the present disclosure;

fig. 4 is a schematic diagram illustrating a multi-type database compatible device of an ORM framework according to one or more embodiments of the present disclosure.

Detailed Description

The embodiment of the specification provides a method, a device, equipment and a medium for compatibility of a multi-type database of an ORM framework.

In order to make those skilled in the art better understand the technical solutions in the present specification, the technical solutions in the embodiments of the present specification will be clearly and completely described below with reference to the drawings in the embodiments of the present specification, and it is obvious that the described embodiments are only a part of the embodiments of the present specification, and not all of the embodiments. All other embodiments obtained by a person of ordinary skill in the art based on the embodiments of the present specification without any creative effort shall fall within the protection scope of the present specification.

FIG. 1 is a flow diagram illustrating a multi-type database compatibility method of an ORM framework according to one or more embodiments of the present disclosure, where the flow may be performed by a multi-type database compatibility system that may be used for multi-type database compatibility in the ORM framework, and where certain input parameters or intermediate results in the flow allow for manual intervention adjustments to help improve accuracy.

The method of the embodiments of the present specification comprises the following steps:

s102, in an ORM framework, SQL sentences respectively supported by the multiple types of databases are obtained, and personalized SQL sentences different from other databases are marked in each type of database.

In the embodiment of the present specification, the multiple types of databases are database types with which the ORM framework can be compatible, and subsequently, in the use process, other types of databases may be added, so that the ORM framework has a better effect in terms of database compatibility.

In the embodiment of the present specification, the personalized SQL statement is a part of each type of database that is different from other databases, and based on the personalized SQL statement, the SQL statement of the database of the specified type can be better transferred to subsequently when the SQL statement is converted.

And S104, creating an SQL statement conversion tool for SQL statement conversion among the multiple types of databases according to the SQL statements respectively supported by the multiple types of databases and the personalized SQL statements.

In this embodiment of the present specification, the SQL statement conversion tool may be an SQL statement conversion package, and during the execution process, the personalized SQL statement may be loaded into the Dialect program of the SQLAlchemy according to the beginning of the connection string, so as to implement conversion of different SQL statements, where the personalized SQL statement is a Dialect (Dialect).

And S106, setting the SQL statement type output by the SQL statement conversion tool according to the type of the database built in the ORM frame.

In the embodiment of the present specification, one or more types of databases may be built in the ORM frame, and if the input SQL statement does not belong to the type of the database built in the ORM frame, the input SQL statement needs to be converted, and at this time, the SQL statement type output by the SQL statement conversion tool may be set, and the input SQL statement may be converted into the SQL statement supported by the ORM frame.

S108, developing a corresponding application function according to the SQL statement type, and applying the application function to the ORM framework to realize that the ORM framework is compatible with SQL statements of multiple types of databases.

In the embodiments of the present specification, the application function may be developed according to different service scenarios.

After the application function is applied to the ORM frame, the ORM frame needs to be optimized, a clause type in an SQL statement may be determined, and optimization rules of different clause types are set, then after a specified SQL statement is input to the ORM frame, the specified SQL statement is converted into a first SQL statement of the SQL statement type by the SQL statement conversion tool, and finally, an execution sequence of the first SQL statement is adjusted according to the optimization rules.

After the application function is applied to the ORM framework, the embodiment of the present specification may perform serialization conversion and deserialization conversion on the specified SQL statement to optimize the ORM framework after the specified SQL statement is input into the ORM framework.

After the application function is applied to the ORM framework, the embodiment of the present specification may set a normal form of a database table in the ORM framework, which specifically includes: setting all fields in the database table to be single attribute, wherein the column of the single attribute is formed by basic data types, so that the database table is a two-dimensional table; and setting a service primary key for the database table so that the database table does not have a non-primary key column.

After the application function is applied to the ORM frame, when a database of the ORM frame executes a plurality of index instructions, determining a data ID through the plurality of index instructions, and querying corresponding data in the database of the ORM frame according to the data ID.

After the application function is applied to the ORM frame, in the embodiment of the present specification, when the server memory space of the ORM frame is greater than a preset threshold and the performance requirement is high, read-write analysis may be performed on a database of the ORM frame first; read requests are then distributed to multiple slave servers to relieve the database of stress. The high performance requirements may include high stability requirements, high availability requirements, high scalability requirements, and high manageability requirements.

After the application function is applied to the ORM framework, the application function may convert an operator in the application function into an SQL statement of a database in the ORM framework, so as to reach an operation object to a database hierarchical level.

The embodiment of the specification can be based on a python SQL Alchemy ORM framework and is used for compatibly adapting to various databases. The method solves the feasibility scheme that enterprises can simultaneously use domestic databases and other mainstream databases. SQLAlchemy is an open source software under Python programming language. An SQL toolkit and Object Relational Mapping (ORM) tool are provided, using MIT license issuance. ORM is often mentioned in the Web application framework because it is a key component in the Rapid Development (Rapid Development) stack. Web frameworks such as Django and Ruby on Rails adopt a method of designing an independent stack, and an ORM developed autonomously is tightly integrated into the framework. In the embodiment of the specification, different databases are automatically identified according to the database connection strings, Dialect (Dialect) of the corresponding database is matched, SQL statements are generated through object operation in Dialect conversion codes, and then DBAPIs of different databases are called to access the databases, so that the purpose of database interaction can be achieved.

In addition, with the gradual improvement of the domestic database ecology, enterprises have more choices and more reliable and safe database services. When the ecological market of the database tends to be perfect and the information security is stricter, the development cost is reduced in the enterprise software service for quickly converting the results of the database. Based on this, the compatibility matching between databases is more important at present.

In the specific implementation process of the embodiments of the present specification, the following four aspects may be performed:

A. the method meets the vast majority of database use scenarios (add/delete/change/search/function) according to different database characteristics and SQL syntax;

B. optimizing query strategies for the performance of different databases and design styles to achieve maximum performance;

C. different design ideas of different databases are considered, and conversion and verification of the sequence words and deserialization are carried out in a targeted manner;

D. the upper layer structure matched with the SQL Alchemy ORM framework is matched with the ORM object operation layer language correlation function and method, and the conversion capability is realized;

the content of A can be performed in the following way:

(1) for different databases, the SQL sentences which support the SQL syntax differences to achieve the same purpose are also different, and the individual SQL sentences can be used for the differences in the code execution process;

(2) according to the content described in (1), when the service requirements cannot be met, corresponding stored procedure functions and general functions need to be developed in a targeted manner to meet the use scenario of the database.

The content of B can be performed in the following way:

(1) the method has certain processing capacity aiming at the problems of database performance, database conversation duration, conversation type, overtime correspondence and the like;

(2) and reasonably optimizing the SQL sentence execution sequence of the database query.

The content of C can be performed as follows:

(1) aiming at different databases, reasonable conversion is carried out when the deserialization of the databases is ensured, so that the performance of the databases can be improved, and the exception of database layer error reporting can be prevented;

(2) aiming at different databases, when the serialization of the databases is ensured, data is reasonably converted according to the definition of an ORM layer object, and the stability of business logic is facilitated.

The content of D can be performed by:

(1) certain ORM to database conversion capability is provided for some functions and operation operations of the ORM layer and user-defined operation functions which cannot meet conventional operation;

(2) aiming at an ORM object, the operation attribute of the object has certain function self-definition capability, and the function is matched with a database function for execution.

In order to adapt to various databases in python SQL Alchemyorm framework in a compatible way, the design can be completed by the following structure:

1. multiple database connection problem

For different databases, connection strings and SQL statement adaptation packets are different, a conversion packet can be installed, dialects (dialects) are loaded into a Dialect program of the SQLAlchemy according to the beginning of the connection strings, and different dialects can be used for processing.

2. The upper layer structure matched with pythonSQL Alchemy ORM framework is matched with the function and method related to the ORM object operation layer language

After the database is connected, the operation is carried out on data, the concept of an ORM relation object is introduced for facilitating the complex logic operation of a user, namely, the operation of a database layer is achieved through an operation object, a common operator in the operation object is converted into SQL language of the database, meanwhile, special attribute definition can be carried out on a special model, the special attribute is disguised as a common attribute or a field, and a database function is automatically called when the special attribute is called.

3. Serialization and deserialization

The meaning of serialization is to convert the data taken out from the database into the attribute of the ORM object, and the data processing and the type conversion are carried out aiming at the virtual field of the model attribute possibility and also possibly a function of special processing. For deserialization. The method is characterized in that parameters are transmitted into a WEB service operation database through an ORM, the parameters can cause the operation of the database to be blocked and error-reported without deserialization, even SQL statements are injected to cause loss, and the serialization and deserialization can write verification conversion logic in a user-defined Dialect.

4. ORM operation database performance aspects

The table structure optimization is the most important in database optimization, how to design the optimization more reasonably needs to be combined with actual situations, the principle of indexing in the indexing aspect is that the data are stored after being arranged according to the specified field sequence in advance when the addition and deletion are carried out, the corresponding pointer can be found from the index when the data are searched, and for a large number of queries, all data id can be queried conditionally (in the case of having an index, the index coverage can be removed), and the efficiency of querying the data through the id is higher than that of directly querying the data through the id when the id is searched. The database reading and writing analysis can be carried out under the conditions of abundant servers and high performance requirements, generally, a written server is called a master server, is written and then is synchronized to a reading server (namely a slave server), and a reading request is distributed to a plurality of slave servers, so that the pressure of a master database is relieved.

5. Covering more comprehensive database operation requirements

The basic operations of different databases are similar, and have no corresponding functions due to complex operations or differences among databases, which require specific database functions or stored procedures.

Further, an embodiment of the present specification further provides a structural schematic diagram of an ORM framework, see fig. 2, where an Obj Relational Map (ORM) is an ORM layer, a next layer is an SQL cachey engine, a next core layer is used for communicating with a data API, different database APIs are called according to different database strings, and a last layer is used for storing different databases.

Fig. 3 is a schematic structural diagram of a multi-type database compatible device of an ORM framework according to one or more embodiments of the present specification, where the device includes: an acquisition unit 302, a creation unit 304, a setting unit 306 and an application unit 308.

The obtaining unit 302 obtains SQL statements respectively supported by multiple types of databases in an ORM framework, and marks personalized SQL statements different from other databases in each type of database;

the creating unit 304 creates an SQL statement conversion tool for performing SQL statement conversion between the multiple types of databases according to the SQL statements respectively supported by the multiple types of databases and the personalized SQL statements;

the setting unit 306 sets the SQL statement type output by the SQL statement conversion tool according to the database type built in the ORM frame;

the application unit 308 develops a corresponding application function according to the SQL statement type, and applies the application function to the ORM frame, so that the ORM frame is compatible with SQL statements of multiple types of databases.

Fig. 4 is a schematic structural diagram of a multi-type database compatible device of an ORM framework according to one or more embodiments of the present specification, where the device includes:

at least one processor; and the number of the first and second groups,

a memory communicatively coupled to the at least one processor; wherein,

the memory stores instructions executable by the at least one processor to enable the at least one processor to:

The embodiments in the present specification are described in a progressive manner, and the same and similar parts among the embodiments are referred to each other, and each embodiment focuses on the differences from the other embodiments. In particular, for the apparatus, device, and non-volatile computer storage medium embodiments, since they are substantially similar to the method embodiments, the description is relatively simple, and reference may be made to the partial description of the method embodiments for relevant points.

The foregoing description has been directed to specific embodiments of this disclosure. Other embodiments are within the scope of the following claims. In some cases, the actions or steps recited in the claims can be performed in a different order than in the embodiments and still achieve desirable results. In addition, the processes depicted in the accompanying figures do not necessarily require the particular order shown, or sequential order, to achieve desirable results. In some embodiments, multitasking and parallel processing may also be possible or may be advantageous.

The above description is merely one or more embodiments of the present disclosure and is not intended to limit the present disclosure. Various modifications and alterations to one or more embodiments of the present description will be apparent to those skilled in the art. Any modification, equivalent replacement, improvement or the like made within the spirit and principle of one or more embodiments of the present specification should be included in the scope of the claims of the present specification.

Claims

1. A method for multi-type database compatibility of an ORM framework, the method comprising:

2. The method of claim 1, wherein after applying the application function to the ORM framework, the method further comprises:

after an SQL statement is specified and input into the ORM framework, the SQL statement is converted into a first SQL statement of the SQL statement type through the SQL statement conversion tool;

3. The method of claim 1, wherein after applying the application function to the ORM framework, the method further comprises:

4. The method of claim 1, wherein after applying the application function to the ORM framework, the method further comprises:

5. The method of claim 1, wherein after applying the application function to the ORM framework, the method further comprises:

6. The method of claim 1, wherein after applying the application function to the ORM framework, the method further comprises:

7. The method of claim 1, wherein after applying the application function to the ORM framework, the method further comprises:

8. A multi-type database compatible apparatus of an ORM framework, the apparatus comprising:

9. A multi-type database compatible device of an ORM framework, the device comprising:

at least one processor; and the number of the first and second groups,

a memory communicatively coupled to the at least one processor; wherein,

creating an SQL sentence conversion tool for carrying out SQL sentence conversion among the multiple types of databases according to the SQL sentences and the personalized SQL sentences respectively supported by the multiple types of databases;

10. A non-transitory computer storage medium having stored thereon computer-executable instructions configured to:

in an ORM framework, SQL sentences respectively supported by multiple types of databases are obtained, and personalized SQL sentences different from other databases are respectively marked in each type of database;