Patent ID: 12204528

Throughout the drawings, identical reference numbers designate similar, but not necessarily identical, elements. The figures are not necessarily to scale, and the size of some parts may be exaggerated to more clearly illustrate the example shown. Moreover, the drawings provide examples and/or implementations consistent with the description; however, the description is not limited to the examples and/or implementations provided in the drawings.

DETAILED DESCRIPTION

In the present disclosure, use of the term “a,” “an”, or “the” is intended to include the plural forms as well, unless the context clearly indicates otherwise. Also, the term “includes,” “including,” “comprises,” “comprising,” “have,” or “having” when used in this disclosure specifies the presence of the stated elements, but do not preclude the presence or addition of other elements.

A database management server includes database management instructions that manage the storage and access of data in a relational database. The database management server receives database language statements to access data and manage data in the relational database. The access of data can include retrieving data, modifying (updating or deleting data), and adding data. The managing of data can include creating, modifying, or deleting relational tables, views, or other data structures. Examples of database language statements include Structured Query Language (SQL) statements.

Examples of database management servers include those provided by various different vendors, including Oracle, Microsoft, and so forth. Database management servers can also include open source database management servers. The source code of an open source database management servers is readily available (e.g., free to download and use) for modification and even redistribution for creation of a specific database management system. Examples of open source database servers include a MySQL database server, a PostgreSQL database server, and so forth.

An enterprise that operates a system that employs an existing database management server may desire to change to use of a different database management server. An enterprise can refer to a business, an educational organization, a government agency, an individual, or any other type of entity.

An enterprise may decide to change database management servers for any of various reasons. For example, the enterprise may wish to convert from a proprietary database management server to an open source database management server for cost savings reasons. To use a proprietary database management server, the enterprise may have to pay a licensing fee to the vendor of the proprietary database management server. However, an open source database management server may not be subject to a license fee. An enterprise may also wish to change database management server for other reasons, such as for improved performance, easier maintenance, and so forth. Although some examples refer to switching from a proprietary database management server to an open source database management server, other changes may also be performed, including changing between different proprietary database management servers, or changing from an open source database management server to a proprietary database management server.

A system that includes a database management server can include an application (or multiple applications) that can issue queries to the database management server. An “application” refers to any program (formed with machine-readable instructions such as software and/or firmware) that is executable to perform respective tasks.

The application can include source code that has database language statements that can be issued to access data in a relational database of the database management server. Additionally, the application can dynamically generate database language statements at runtime of the application.

To switch the application from use of a first database management server to a second database management server, it can be difficult to convert database language statements in a first dialect of the first database management server to a second dialect of the second database management server. Different database management servers can use different dialects of database language statements. A “dialect” of database language statements includes a syntax and capability of the database language statements. In specific examples, although different dialects of database language statements can be according to SQL, the syntaxes and capabilities of the different dialects of SQL statements can be different.

For example, different syntaxes can refer to different manners of expressing variables, different ways of handling functions or stored procedures, and so forth. A function refers to code that receives a value of an input variable (or values of multiple input variables) and produces an output value (or output values) based on performing a specified task (or tasks). A stored procedure refers to code that can be defined to accept zero or more input variables and to output zero or more output variables based on performing a specified task (or tasks).

As a further example, different capabilities of different dialects of SQL statements can refer to features available to one dialect that is not available to another dialect. For example, one dialect can use packages, while another dialect is unable to use packages. A “package” refers to a group of functions or stored procedures stored together for use as a unit. As another example, a function or stored procedure that can be defined in one dialect may not be available in another dialect.

In accordance with some implementations of the present disclosure, a database application adapter is able to convert database language statements according to a source dialect for a source database server to database language statements according to a target dialect for a target database server, as part of a process to migrate from the source database server to the target database server.

FIG.1shows an example arrangement that includes a source system102and a target system122. The source system102includes a source database server103, while the target system122includes a target database server123different from the source database server103. For example, the source database server103can be a proprietary database server, such as provided by Oracle or Microsoft. The target database server123can be an open source database server, such as a MySQL database server or Postgres SQL database server. Although the foregoing refers to specific examples of the source and target database servers103and123, other types of source and target database servers103and123can be used in other examples.

Each of the source and target systems102and122can be implemented using a computing platform that has a computer or an arrangement of computers. Note that the source and target systems102and122can both be implemented on the same computing platform. In such an example, the source database server103on the computing platform is migrated to the target database sever123on the computing platform. In alternative examples, the source and target systems102and122can be implemented using different computing platforms.

The migration between the source database server103and the target database sever123is performed by a migration engine140. As used here, the term “engine” can refer to a hardware processing circuit, such as any or some combination of the following: a microprocessor, a core of the multi-core microprocessor, a microcontroller, a programmable integrated circuit device, a programmable gate array, and so forth. In other examples, an “engine” can refer to a combination of a hardware processing circuit and machine-readable instructions executable on the hardware processing circuit.

The source system102includes an application104that is able to issue source SQL statements146to access or manage data of the source database server103that stores data in a source database106. The source database106includes various data structures, including tables108, views110(a view is a result set of a stored SQL statement on data), triggers112(a trigger is a stored procedure that is automatically executed in response to an event on a particular table or view in a database), functions114(a function is designed to perform a specified task or tasks), packages116(a package is a group of related stored procedures or functions), and so forth.

The source database server103of the source system102is to be migrated to a target database server123of the target system122. In some examples, the target system122runs a converted application104A, which is a converted version of the application104that is run in the source system102. The core functionality of the application104is not changed in the converted application104A. The converted application104A differs from the application104in the dialect of SQL statements used by the respective converted application104A and application104. While the source SQL statements146issued by the application104are according to a source dialect for accessing or managing data of the source database server103, target SQL statements of the converted application104A are according to a target dialect (different from the source dialect) for accessing or managing data of the target database server123.

In alternative examples, the application in the target system122can be identical to the application104in the source system102. In such alternative examples, runtime conversion between source and target SQL statements can be performed without modifying the application104.

The target database server123includes various data structures, including tables126, views130, triggers132, and functions134. In the example ofFIG.1, while the source database server103supports the use of packages116, the target database server123does not support the use of packages. This depicts an example difference in capabilities between the first and second dialects of SQL statements used by the source database server103and the target database server123, respectively.

The migration engine140converts between the source system102and the target system122. The migration engine140can be part of the source database102or target system122, or can be separate from the source system102and/or target system122.

The migration engine140includes a database schema converter142to convert between a source database schema of the source database106(where the source database schema defines the tables108, views110, triggers112, functions114, and packages116) and a target database schema of the target database124(where the target database schema defines the tables126, views130, triggers132, and functions134). A database schema refers to information that defines the organization of data as stored in a database (and more specifically, of the organizational data as stored in relational tables of the database). The database schema can define how the database is constructed, e.g., how the database is divided into relational tables, and the database schema can also define the structure of the relational tables (including the attributes of the tables, indexes used by the tables, and so forth). The database schema can also define procedures, functions, views, and other aspects of a relational database. The database schema converter142can use any of various migration tools that migrate between different database schemas used in different databases.

In addition, the migration engine140includes an application adapter144according to some implementations of the present disclosure to adapt the application104for use with the target database server123, instead of the source database server103. The source code of the application104can include SQL statements according to the source dialect. Additionally, the application104can dynamically generate SQL statements according to the source dialect. For example, a stored procedure in the application104when called can dynamically generate SQL statements issued to the source database server103.

The application adapter144converts between source SQL statements146according to the source dialect and target SQL statements148according to the target dialect. In some examples, the conversion performed by the application adapter144can be performed during runtime of the source system102. During the runtime of the source system102, the application104is executing and issuing the source SQL statements146to the source database server103. The issued source SQL statements146are intercepted by the application adapter144and converted to the target SQL statements148.

The application adapter144can modify the source code of the application104to produce the source code of the converted application104A. The modification includes changing the dialect of the SQL statements from the source dialect in the source code of the application104to the target dialect in the source code of the converted application104A. The application adapter144can also modify other code structures (e.g., stored procedures) that can dynamically generate SQL statements when called.

For increased efficiency, the migration engine140includes a memory150that stores a pattern cache152to store patterns of source SQL statements that have been encountered before. As used here, a “pattern” can refer to an abstract representation of a SQL statement. The pattern represents elements of the SQL statement in abstract form. In some examples, the pattern can be represented in the form of an abstract syntax tree (AST) or other tree structure. A tree structure includes nodes that represent the corresponding elements of the SQL statement, where the elements can include any or some combination of the following: variables (which can represent attributes of tables and generated output values), table names, view names, Boolean operators (e.g., AND, OR, etc.), comparison operators (e.g., =, <, >, etc.), predicates (e.g., WHERE x=1), literals (e.g., constant values), functional operators (e.g., SELECT, JOIN, etc.), and so forth.

An abstract representation of a SQL statement refers to a representation in which certain elements of the SQL statement are normalized, such as by parameterizing the SQL statement by abstracting specific constant values to a wildcard indication (e.g., x=1 is changed to x=?, where “?” is a wildcard indication), abstracting a list of values of a function or other operator to a wildcard indication (e.g., IN(1, 2, 3) changed to IN(??), where “??” is a wildcard indication), changing an order of an expression, such as by sorting the items in the expression (e.g., SELECT y, m, b is changed to SELECT b, m, y by sorting the order of “y, m, b”).

The abstraction of a pattern allows for increasing the chances of matching patterns in the pattern cache152. For example, a SQL statement that includes a specific predicate (e.g., x=1), a specific function (e.g., IN(1, 2, 3)), and a specific expression (e.g., SELECT y, m, b) can be matched only to another SQL statement that includes the identical specific predicate, specific function, and specific expression. By using the abstract representation of the pattern, however, a larger number of SQL statements can be matched to the pattern. More specifically, SQL statements that include a predicate in the form of x=?, a function in the form of IN(??), and a SELECT expression that includes b, m, y in any order can potentially be matched to the pattern.

The pattern cache152includes multiple translation entries, where each translation entry stores a respective source pattern (that represents a SQL statement according to the source dialect) and a corresponding target pattern (that represents a SQL statement according to the target dialect) translated from the respective source pattern. Every time a translation is performed between a unique combination of a source pattern and a target pattern, the corresponding source pattern and target pattern are added to as a translation entry to the pattern cache152. In this way, when translating a subsequent SQL statement, a previous translation can be used if the subsequent SQL statement has a pattern that matches a source pattern in any of the entries of the pattern cache152. In this way, the application adapter144does not have to generate a new translation for each received source SQL statement if a translation for a pattern of the received source SQL statement was already translated previously. For the received source SQL statement that has a pattern that matches any of the patterns stored in the pattern cache152, the application adapter144can use the previously performed translation between the source pattern and the corresponding target pattern.

For further enhanced efficiency in performing translations, the pattern cache152can be populated with translation entries including translations between source and target patterns produced during development and/or testing of the application104. As part of the development and/or testing of the application104, developers and/or testers of the application104may invoke the application adapter144to translate certain source SQL statements according to the source dialect to target SQL statements according to the target dialect.

Such translations can be useful when converting source SQL statements to target SQL statements during the runtime of the application104in the source system104. By pre-populating the pattern cache152with translation entries based on translations generated prior to the runtime of the application104, the application adapter144can leverage such prior translations in converting source SQL statements to target SQL statements during the runtime of the application104.

FIG.2is a block diagram of an example arrangement to perform runtime conversion between the source SQL statements146and the target SQL statements148. In some examples, the application adapter144ofFIG.1can be implemented using a wrapper Java Database Connectivity (JDBC) driver202, as shown inFIG.2.

JDBC is an application programming interface (API) for the JAVA programming language. JDBC defines how a client can access a database. A JDBC driver is a program enabling a Java application (e.g., the application104) to interact with a database. A wrapper JDBC driver is a program that includes specified functionalities (e.g., the functionalities of the wrapper JDBC driver202), and which is able to call another program, such as a target JDBC driver218that is used to access the target database server123.

Although reference is made to use of JDBC inFIG.2, it is noted that in other examples, other techniques for implementing the conversion engine140can be used.

The wrapper JDBC driver202intercepts SQL statements146generated by the application104. The wrapper JDBC driver202includes a parser206, a normalizer208, a translator210, and a rebuilder212. The output of the wrapper JDBC driver202includes translated target SQL statements148, which are provided to the target JDBC driver218. The target JDBC driver218in turn issues the target SQL statements148to the target database server123to access the target database124.

The parser206parses a source SQL statement146into an AST or other abstract representation of the source SQL statement146. For example, the following source SQL statement146according to the source dialect can be parsed by the parser206into an AST tree302as shown inFIG.3:SELECT NAME, PERMISSION_NAME, DECODE(PERMISSION, 1, ‘READ’, 2 ‘WRITE’, 3 ‘DELETE’, ″) FROM TB_USERS, TB_USER_PERMISSIONS WHERE USER_ID IN (?, ?) AND TB_USERS.USER_ID=TB_USER_PERMISSIONS.USER_ID(+).

The AST tree302represents the elements of the source SQL statement146as nodes of a tree.

The normalizer208normalizes the AST302. The normalizing can include various actions to abstract the elements of the source SQL statement146. For example, the order of the elements (304,306,308inFIG.3) of the SELECT clause is sorted (in alphabetical order) to produce the corresponding elements308,304, and306in the normalized AST402shown inFIG.4.

Also, in the example ofFIGS.3and4, the IN(?, ?) clause as represented by elements310,312, and314inFIG.3is normalized to IN(??) as represented by elements310and404in the normalized AST402ofFIG.4.

Moreover, although not shown, the normalizing can also include parameterizing the source SQL statement. For example, if the SQL statement includes a predicate “USER_ID=1,” then this predicate can be normalized to “USER_ID=?.” The parameterization converts specific values in predicate clauses to wildcard indications.

Other abstraction actions can also be performed by the normalizer208.

The normalized AST402is then translated by the translator210from the source SQL dialect to the target SQL dialect. As part of the translation performed by the translator210, the translator210searches the pattern cache152in the memory150to determine if the pattern cache152contains a translation entry containing a pattern (or more specifically, an AST) that matches the normalized AST402. If so, the translation entry contains an existing translation between the normalized AST402according to the source dialect and a corresponding normalized AST according to the target SQL dialect. If a match occurs, this existing translation can be used by the translator210to perform the translation between the normalized AST402according to the source dialect and a normalized AST502(FIG.5) according to a target dialect.

In the translation, the DECODE function represented by elements308,308-1,308-2,308-3,308-4,308-5,308-6, and308-7of the normalized AST402ofFIG.4is translated to elements in block504of the translated normalized AST502ofFIG.5. In this example, the DECODE function that exists in the source dialect does not exist in the target dialect, in which case the DECODE function is replaced with the CASE (represented by element503inFIG.5) and WHEN-THEN clauses (generally in the form CASE (WHEN . . . THEN . . . END)) represented in block504ofFIG.5.

In addition, a left join expression represented by elements406,406-1, and406-2in the normalized AST402according to the source dialect ofFIG.4is translated to a different left join expression represented by elements506,506-1,506-2,506-3,506-31, and506-32of the translated normalized AST502according to the target dialect ofFIG.5.

The following sets forth an example of converting a left join expression in the source dialect to a left join expression in the target dialect:SELECT*FROM A, B WHERE A.A_ID=B.B_ID(+)converted toSELECT*FROM A LEFT OUTER JOIN B ON A.A_ID=B.B_ID.

As another example, although not shown inFIGS.4and5, an expression to obtain a next value in a sequence can also be converted, such as follows:KNTA_USERS_S.NEXTVALconverted toNEXTVAL(‘KNTA_USERS_S’).

Other translations can be performed by the translator210in other examples.

If the pattern cache152does not contain the normalized AST402, then the translator210can perform the translation on the fly, during runtime. In such a scenario, a new translation entry that includes the normalized AST402and the translated normalized AST502is added to the pattern cache152, for use in a future translation if the normalized AST402is encountered again.

In some examples, the translator210can derived from an ANother Tool for Language Recognition (ANTLR) library, which can be used to generate tree parsers for parsing ASTs.

The rebuilder212de-normalizes the translated normalized AST502to produce a de-normalized translated AST602ofFIG.6. For example, the order of the original SELECT clause (as represented by the AST302) can be restored. For example, the order of elements503,304, and306in the translated normalized AST502ofFIG.5is changed to the order of elements304,306, and503in the de-normalized AST602ofFIG.6. Moreover, the IN (??) function is de-normalized to IN (?, ?) (i.e., the wildcard indication “??” is de-abstracted to a list of values “(?,?).” This is depicted as changing the elements310and404in the translated normalized AST502ofFIG.5to the corresponding elements310,602, and604in the de-normalized translated AST602ofFIG.6. In addition, in a predicate, a wildcard indication is de-abstracted to a constant value.

The rebuilder214can then uses the de-normalized translated AST602to rebuild a target SQL statement148according to the target SQL dialect, as set forth below:SELECT NAME, PERMISSION_NAME, CASE PERMISSION WHEN 1 THEN ‘READ’ WHEN 2 THEN ‘WRITE’ WHEN 3 THEN ‘DELETE’ ELSE ″ END FROM TB_USERS LEFT JOIN TB_USER_PERMISSIONS ON TB_USERS.USER_ID=TB_USER_PERMISSIONS.USER_ID WHERE USER_ID IN (?, ?).

By using techniques or mechanisms according to some implementations of the present disclosure, the migration of a system from using a source database server to a target database server can be accomplished without modifying the core functionality of the application104. In some cases, if the application104is modified, just the database language statements of the application104are modified. Since the core functionality of the application104, multiple copies of source code of the application104do not have to be maintained.

Also, the migration can be performed at runtime of the application104, in which source database language statements according to the source dialect are converted to target database language statements according to the target dialect while the application104is running. In addition, the source database language statements according to the source dialect may have been customized by enterprises—the migration engine according to some implementations is able to translate such customized source database language statements.

FIG.7is a block diagram of a non-transitory machine-readable or computer-readable storage medium700storing program code (including machine-readable instructions) that upon execution causes a system to perform respective tasks. The program code includes source database language statement receiving instructions702to receive a source database language statement according to a first dialect; pattern determining instructions704to determine a pattern of the source database language statement, the pattern comprising an abstract representation of the source database language statement; cache checking instructions706to check whether the determined pattern is present in a cache of translations between patterns according to the first dialect and corresponding patterns according to a second dialect different from the first dialect; and converting instructions708to, in response to the determined pattern being present in the cache of translations, convert, using a corresponding translation in the cache of translations, the source database language statement according to the first dialect to a respective target database language statement according to the second dialect.

FIG.8is a block diagram of a system800that includes a memory802to store a cache804of translations, an application806, and a hardware processor808to perform respective tasks. A hardware processor can include a microprocessor, a core of a multi-core microprocessor, a microcontroller, a programmable integrated circuit, a programmable gate array, or another hardware processing circuit. A hardware processor performing a task can refer to a single hardware processor performing the task or multiple hardware processors performing the task.

The tasks performed by the hardware processor808can include a source database language statement receiving task810to receive a source database language statement according to a first dialect issued by the application, an abstract representation determining task812to determine an abstract representation of the source database language statement, a cache checking task814to check whether the determined abstract representation is present in the cache of translations between abstract representations according to the first dialect and corresponding abstract representations according to a second dialect different from the first dialect, and a converting task816to, in response to the determined abstract representation being present in the cache of translations, convert, using a corresponding translation in the cache of translations, the source database language statement according to the first dialect to a respective target database language statement according to the second dialect.

FIG.9is a flow diagram of a process according to some implementations, which can be performed by a system that includes a migration engine (e.g.,140inFIG.1), according to some examples. The process includes receiving (at902) a source database language statement according to a first dialect during runtime of an application that issued the source database language statement.

The process further includes parsing (at904) the source database language statement into a pattern of the source database language statement, the pattern comprising an abstract representation of the source database language statement.

The process further includes checking (at906) whether the pattern is present in a cache of translations between patterns according to the first dialect and corresponding patterns according to a second dialect different from the first dialect.

In response to the pattern being present in the cache of translations, the process converts (at908), using a corresponding translation in the cache of translations, the source database language statement according to the first dialect to a respective target database language statement according to the second dialect.

The storage medium700ofFIG.7can include any or some combination of the following: a semiconductor memory device such as a dynamic or static random access memory (a DRAM or SRAM), an erasable and programmable read-only memory (EPROM), an electrically erasable and programmable read-only memory (EEPROM) and flash memory; a magnetic disk such as a fixed, floppy and removable disk; another magnetic medium including tape; an optical medium such as a compact disk (CD) or a digital video disk (DVD); or another type of storage device. Note that the instructions discussed above can be provided on one computer-readable or machine-readable storage medium, or alternatively, can be provided on multiple computer-readable or machine-readable storage media distributed in a large system having possibly plural nodes. Such computer-readable or machine-readable storage medium or media is (are) considered to be part of an article (or article of manufacture). An article or article of manufacture can refer to any manufactured single component or multiple components. The storage medium or media can be located either in the machine running the machine-readable instructions, or located at a remote site (e.g., a cloud) from which machine-readable instructions can be downloaded over a network for execution.

In the foregoing description, numerous details are set forth to provide an understanding of the subject disclosed herein. However, implementations may be practiced without some of these details. Other implementations may include modifications and variations from the details discussed above. It is intended that the appended claims cover such modifications and variations.