CN116028466A - Database structure migration method, device, equipment and storage medium - Google Patents
Database structure migration method, device, equipment and storage medium Download PDFInfo
- Publication number
- CN116028466A CN116028466A CN202211347144.5A CN202211347144A CN116028466A CN 116028466 A CN116028466 A CN 116028466A CN 202211347144 A CN202211347144 A CN 202211347144A CN 116028466 A CN116028466 A CN 116028466A
- Authority
- CN
- China
- Prior art keywords
- target
- migration
- database
- source
- statement
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The embodiment of the application provides a database structure migration method, a device, equipment and a storage medium, wherein the method comprises the following steps: acquiring a target migration rule according to the identification information of the source database and the identification information of the target database; determining a target object type to be migrated from a data structure of a source database according to a target migration rule, and acquiring a source object belonging to the target object type; acquiring a source DDL definition statement of a source object from a source database, and converting the source DDL definition statement into a target SQL statement conforming to the grammar of a target database; and creating a migration task according to the target SQL statement, and controlling the target database to execute the migration task to create a target object in the target data structure. According to the technical scheme of the embodiment, the rapid automatic migration of the database structure can be realized, and the migration efficiency and the user experience are effectively improved.
Description
Technical Field
The present disclosure relates to the field of database technologies, and in particular, to a method, an apparatus, a device, and a storage medium for migrating a database structure.
Background
At present, database products are more and more abundant, but different databases have different characteristics, and in the database switching process, the influence on service functions needs to be avoided, so that the complete migration of the service object structure of the source database to the target database is an important ring.
In the related technology, a migration tool equipped with a target database is generally used for migration, a migration user is selected firstly, then an object owned by the user is selected and a migration task is executed, after the migration is completed, whether a migration result is complete and accurate is checked manually, if incompatibility exists in program contents such as a storage process, a function and the like, manual processing is needed, manual intervention is more, and migration efficiency and migration quality cannot be guaranteed.
Disclosure of Invention
The main purpose of the embodiment of the application is to provide a database structure migration method, device, equipment and storage medium, which aim to realize rapid automatic migration of the structure of a database and improve migration efficiency and user experience.
To achieve the above object, a first aspect of an embodiment of the present application provides a database structure migration method, where the method includes:
acquiring a preset target migration rule according to the identification information of the source database and the identification information of the target database;
determining a target object type to be migrated from a data structure of the source database according to the target migration rule, and acquiring a source object belonging to the target object type;
acquiring a source DDL definition statement of the source object from the source database, and converting the source DDL definition statement into a target SQL statement conforming to the grammar of the target database;
And creating a migration task according to the target SQL statement, and controlling the target database to execute the migration task so as to create the target object in a target data structure of the target database.
In some embodiments, the target migration rule includes a pre-check rule, and before the determining the target object type to be migrated from the data structure of the source database according to the target migration rule, the method further includes:
determining a pre-inspection item according to the pre-inspection rule, wherein the pre-inspection item characterizes migration resources between the source database and the target database;
determining a migration resource demand, and checking the pre-check item according to the migration resource demand to obtain a pre-check result, wherein the pre-check result is used for indicating whether the migration resource between the source database and the target database meets the migration resource demand.
In some embodiments, the target migration rule further includes migration content information and migration order information, the migration content information is used to indicate a plurality of the target object types to be migrated, the migration order information is used to indicate migration orders of a plurality of the target object types, the determining a target object type to be migrated from a data structure of the source database according to the target migration rule, and obtaining a source object belonging to the target object type, including:
Determining a plurality of target object types from a data structure of the source database according to the migration content information;
determining a list processor corresponding to each target object type, wherein the list processor is used for acquiring an object list comprising a plurality of objects, and the objects in the object list belong to the target object types corresponding to the object list;
and based on the migration sequence information, operating each list processor to acquire a plurality of object lists from the source database, and determining the objects in the object lists as the source objects.
In some embodiments, the converting the source DDL definition statement to a target SQL statement conforming to the syntax of the target database comprises:
acquiring target conversion processors corresponding to the target object types from a preset conversion processor pool, wherein the target conversion processors are used for indicating the grammar mapping relation between the source DDL definition statement and the target SQL statement;
and respectively converting the source DDL definition statement of each source object into the corresponding target SQL statement by using the target conversion processor corresponding to each target object type.
In some embodiments, the migration rules further include a field type mapping rule for indicating a mapping relationship of a field type between the source database and the target database, and a rights mapping rule for indicating a mapping relationship of an operation right between the source database and the target database, the converting the source DDL definition statement of each of the source objects into the corresponding target SQL statement, respectively, including:
acquiring a source field type and a source operation authority in the source DDL definition statement;
determining a target field type corresponding to the source field type according to the field type mapping rule;
determining a target operation authority corresponding to the source operation authority according to the authority mapping rule;
for each of the source objects, converting the source DDL definition statements into the target SQL statements with the target conversion processor, wherein the target SQL statements include the target field type and the target operation rights.
In some embodiments, the creating a migration task from the target SQL statement includes:
creating the migration task according to all SQL sentences based on the migration sequence information;
Acquiring user authority configuration and role authority configuration of the source database;
generating a permission configuration statement according to the user permission configuration and the role permission configuration;
and adding the permission configuration statement to the migration task.
In some embodiments, after the creating a migration task from the target SQL statement, the method further comprises:
obtaining a migration operation of the target database in response to the target SQL statement;
summarizing all the migration operations and generating a migration report.
To achieve the above object, a second aspect of the embodiments of the present application proposes a database structure migration apparatus, including:
the rule acquisition module is used for acquiring a preset target migration rule according to the identification information of the source database and the identification information of the target database;
the object acquisition module is used for determining the type of the target object to be migrated from the data structure of the source database according to the target migration rule and acquiring the source object belonging to the type of the target object;
the statement conversion module is used for acquiring a source DDL definition statement of the source object from the source database and converting the source DDL definition statement into a target SQL statement conforming to the grammar of the target database;
And the migration module is used for creating a migration task according to the target SQL statement, and controlling the target database to execute the migration task so as to create the target object in a target data structure of the target database.
To achieve the above object, a third aspect of the embodiments of the present application proposes an electronic device, which includes a memory and a processor, the memory storing a computer program, the processor implementing the method according to the first aspect when executing the computer program.
To achieve the above object, a fourth aspect of the embodiments of the present application proposes a storage medium, which is a computer-readable storage medium, storing a computer program, which when executed by a processor implements the method described in the first aspect.
The method, the device, the equipment and the storage medium for migrating the database structure, which are provided by the application, comprise the following steps: acquiring a preset target migration rule according to the identification information of the source database and the identification information of the target database; determining a target object type to be migrated from a data structure of the source database according to the target migration rule, and acquiring a source object belonging to the target object type; acquiring a source DDL definition statement of the source object from the source database, and converting the source DDL definition statement into a target SQL statement conforming to the grammar of the target database; and creating a migration task according to the target SQL statement, and controlling the target database to execute the migration task so as to create the target object in a target data structure of the target database. According to the technical scheme of the embodiment, the source DDL definition statement of each source object in the data structure of the source database can be automatically acquired, the target object is automatically created in the target database after being automatically converted into the target SQL statement, rapid automatic migration of the database structure is realized, and migration efficiency and user experience are effectively improved.
Drawings
FIG. 1 is a flow chart of a database structure migration method provided in one embodiment of the present application;
FIG. 2 is a schematic illustration of an implementation environment provided by another embodiment of the present application;
fig. 3 is a flowchart of step S102 in fig. 1;
fig. 4 is a flowchart of step S102 in fig. 1;
fig. 5 is a flowchart of step S102 in fig. 1;
fig. 6 is a flowchart of step S102 in fig. 5;
fig. 7 is a flowchart of step S104 in fig. 1;
fig. 8 is a flowchart of step S104 in fig. 1;
fig. 9 is a schematic structural diagram of a database structure migration apparatus according to an embodiment of the present application;
fig. 10 is a schematic diagram of a hardware structure of an electronic device according to an embodiment of the present application.
Detailed Description
In order to make the objects, technical solutions and advantages of the present application more apparent, the present application will be further described in detail with reference to the accompanying drawings and examples. It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the present application.
It should be noted that although functional block division is performed in a device diagram and a logic sequence is shown in a flowchart, in some cases, the steps shown or described may be performed in a different order than the block division in the device, or in the flowchart. The terms first, second and the like in the description and in the claims and in the above-described figures, are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order.
Unless defined otherwise, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs. The terminology used herein is for the purpose of describing embodiments of the present application only and is not intended to be limiting of the present application.
In the embodiments of the present application, when related processing is required according to user information, user behavior data, user history data, user location information, and other data related to user identity or characteristics, permission or consent of the user is obtained first, and the collection, use, processing, and the like of the data comply with related laws and regulations and standards of related countries and regions. In addition, when the embodiment of the application needs to acquire the sensitive personal information of the user, the independent permission or independent consent of the user is acquired through a popup window or a jump to a confirmation page or the like, and after the independent permission or independent consent of the user is explicitly acquired, necessary user related data for enabling the embodiment of the application to normally operate is acquired.
First, several nouns referred to in this application are parsed:
Database operation statement: is a programming language used to operate on data in a database. For example, the database operation statement may be, but is not limited to, a programming language used to add, delete, modify, query, etc. data in the database, and the database operation statement may also be, but is not limited to, an SQL statement in the mysql database, an SQL statement in the oracle database, etc. An online data processing request is a request for a user to request a corresponding service in a certain business scenario, wherein the online data processing request can determine a request type of a database operation statement according to the content of the online data processing request, and the like, wherein the request type of the database operation statement includes, but is not limited to, adding, deleting, modifying, querying, and the like. The method for determining the database operation statement according to the online data processing request may be to determine the database operation statement according to the content of the online data processing request. For example, an online data processing request is a data processing request for requesting an addition to data in a database, or a data processing request for requesting a query to data in a database, or a data processing request for requesting deletion or modification of data in a database.
The library data schema definition language (Data definition language, DDL) is a statement for modifying the definition of objects (e.g., tables, indexes, columns, triggers, etc.) in a database.
The structured query language (Structured Query Language, SQL) is a database query and programming language for accessing data and querying, updating and managing relational database systems.
Dream database: the dream database management system is a high-performance database management system introduced by dream companies, and is abbreviated as DM. The latest version of the dream database management system is version 8.0, abbreviated as DM8.
It should be noted that, the data in the embodiments of the present invention may be stored in a server, and the server may be an independent server, or may be a cloud server that provides cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, content delivery networks (Content Delivery Network, CDN), and basic cloud computing services such as big data and artificial intelligence platforms.
Based on the above, the embodiment of the application provides a database structure migration method, a device, equipment and a storage medium, which aim to realize rapid automatic migration of a structure of a database and improve migration efficiency and user experience.
The method, device, equipment and storage medium for migrating a database structure provided in the embodiments of the present application are specifically described through the following embodiments, and the method for migrating a database structure in the embodiments of the present application is first described.
The embodiment of the application provides a database structure migration method, and relates to the technical field of databases. The database structure migration method provided by the embodiment of the application can be applied to a terminal, a server side and software running in the terminal or the server side. In some embodiments, the terminal may be a smart phone, tablet, notebook, desktop, etc.; the server side can be configured as an independent physical server, a server cluster or a distributed system formed by a plurality of physical servers, and a cloud server for providing cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, CDNs, basic cloud computing services such as big data and artificial intelligent platforms and the like; the software may be an application or the like that implements the database structure migration method, but is not limited to the above form.
Fig. 1 is an optional flow chart of a database structure migration method provided in an embodiment of the present application, where the method in fig. 1 may include, but is not limited to, steps S101 to S104.
Step S101, a preset target migration rule is obtained according to the identification information of a source database and the identification information of a target database;
step S102, determining a target object type to be migrated from a data structure of a source database according to a target migration rule, and acquiring a source object belonging to the target object type;
step S103, obtaining a source DDL definition statement of a source object from a source database, and converting the source DDL definition statement into a target SQL statement conforming to the grammar of a target database;
step S104, a migration task is created according to the target SQL statement, and the target database is controlled to execute the migration task so as to create a target object in a target data structure of the target database.
It should be noted that, the implementation environment of the embodiment of the present invention may be shown in fig. 2, and the implementation environment includes a control device 201, a source database 202 and a target database 204 that are communicatively connected to each other through a communication network 203, where the control device 201 may be a device capable of implementing a control function, such as a terminal or a server, and the control device 201 may perform visual control on the source database 202 and the target database 204, and send a control instruction to the source database 202 and the target database 204 through the communication network 203, and the embodiment is not limited to a specific form of the control device 201. The source database 202 and the target database 204 may be different databases, and for convenience of description, the source database 202 is exemplified by an Oracle database, and the target database 204 is exemplified by a dream database, which is not a limitation of the technical solution of the present embodiment.
It should be noted that, the identification information of the source database and the target database may be respective version numbers, for example, the source database is an Oracle database, the target database is a dream database, the identification information of the source database is a version number corresponding to the Oracle database, the identification information of the target database is a version number corresponding to the dream database, and migration rules between different source databases and the target database are difficult to be compatible. For example, migration rules 1 are configured for the Oracle database version 1 and the dream database version 7, migration rules 2 are configured for the Oracle database version 1 and the dream database version 8, when the control device connects the source database and the target database, the version number of the source database is determined to be the Oracle database version 1, and the version number of the target database is the dream database version 8, the migration rules 2 are obtained as target migration rules, so that the control device can be quickly matched with the accurate target migration rules to execute the data structure migration operation, the accuracy of the migration process can be effectively improved, manual intervention is reduced, and the migration efficiency is improved. Of course, in the present embodiment, the types of the source database and the target database are not limited.
It should be noted that, the target object type to be migrated may be all object types of the data structure of the source database, or may be a plurality of object types therein; the source object may be all objects in the target object type or may be a plurality of objects therein, and the number of the target object type and the source object is not limited in this embodiment. It will be appreciated that the object types may be database table space, database roles, etc., which is not limited in this embodiment.
It can be understood that, the technical solution of this embodiment only relates to migration of the data structure, but not migration of data corresponding to the source object, which is not repeated in the following.
It should be noted that, the DDL definition statement may be obtained through a pre-configured functional module, for example, a dbms_metadata. Get_ddl module in the Oracle database, after determining the source object, the module may obtain a corresponding DDL definition statement from the Oracle database, and determine the data structure definition of the source object through the DDL definition statement, which may improve the accuracy of the converted target SQL statement and ensure that the correct target object is created in the target database.
It should be noted that, the migration of the data structure is essentially that an object type and a corresponding object which are the same as those of the source database are created in the target database, so after the target SQL statement corresponding to the source object is obtained, the target SQL statement is added to the migration task, so that the target database executes the target SQL statement when responding to the migration task, thereby creating the source object of the target object type in the target database. It can be understood that if all source objects of all target object types of the source database need to be migrated to the target database, a statement sequence composed of target SQL statements of each source object of each target object type can be obtained sequentially according to the above manner, and a migration task is created according to the statement sequence, so that each target SQL statement is executed by the target database in turn, and each target object of each target object type is created in the target database, so as to obtain a target data structure corresponding to the data structure of the source database.
According to steps S101 to S104 of the present embodiment, a preset target migration rule is obtained according to the identification information of the source database and the identification information of the target database; determining a target object type to be migrated from a data structure of a source database according to a target migration rule, and acquiring a source object belonging to the target object type; acquiring a source DDL definition statement of a source object from a source database, and converting the source DDL definition statement into a target SQL statement conforming to the grammar of a target database; and creating a migration task according to the target SQL statement, and controlling the target database to execute the migration task so as to create a target object in a target data structure of the target database. According to the technical scheme of the embodiment, the source DDL definition statement of each source object in the data structure of the source database can be automatically acquired, the target object is automatically created in the target database after being automatically converted into the target SQL statement, rapid automatic migration of the database structure is realized, and migration efficiency and user experience are effectively improved.
In addition, in an embodiment, the target migration rule further includes a pre-check rule, referring to fig. 3, before performing step S102 shown in fig. 1, further including, but not limited to, the following steps:
Step S301, determining a pre-examination item according to a pre-examination rule, wherein the pre-examination item characterizes migration resources between a source database and a target database;
step S302, determining the requirement of the migration resource, and checking a pre-check item according to the requirement of the migration resource to obtain a pre-check result, wherein the pre-check result is used for indicating whether the migration resource between the source database and the target database meets the requirement of the migration resource.
It should be noted that, the pre-inspection rule may be configured with pre-inspection items to be inspected, each pre-inspection item may correspond to a class of migration resources, and for example, the pre-inspection items may be character set inspection, authority inspection, table space inspection, DBLINK inspection, JAVA storage process inspection, and source database rename object inspection.
It should be noted that, when the pre-inspection result indicates that the migration resource is ready, the subsequent operation is continuously performed, and when the pre-inspection result indicates that the migration resource is not ready, the error of the migration resource can be subjected to positioning analysis, so that the efficiency of error checking is improved.
In an embodiment, the target migration rule further includes migration content information and migration order information, the migration content information is used to indicate a plurality of target object types to be migrated, the migration order information is used to indicate a migration order of the plurality of target object types, and referring to fig. 4, step S102 of the embodiment shown in fig. 1 further includes, but is not limited to, the following steps:
step S401, determining a plurality of target object types from a data structure of a source database according to migration content information;
step S402, determining a list processor corresponding to each target object type, wherein the list processor is used for acquiring an object list comprising a plurality of objects, and the objects in the object list belong to the target object types corresponding to the object list;
in step S403, based on the migration order information, each list processor is run to acquire a plurality of object lists from the source database, and determine the objects in the object list as source objects.
It should be noted that, according to the description of the foregoing embodiment, the target object types may be a table space, a role, or the like, so that migration of the data structure may involve multiple target object types, in order to improve accuracy of migration of the data structure, to avoid execution of migration of incompatible object types, migration content information may be preset in a target migration rule, and the target object types migrated from the source database to the target database may be indicated by the migration content information, or, according to actual needs, an object that needs to be migrated in each target object type may be indicated in the migration content information, that is, a selection rule indicating the source object.
It should be noted that after determining the target object type, migration sequence information may be preset in the target migration rule, so that the target database may create each target object of each target object type according to the set sequence, so as to ensure that the data structure of the target database corresponds to the data structure of the source database. The migration order information may include an order of the target object types, and may also include an acquisition order of the source objects of the target object types.
It should be noted that, because the logic of different target object types is different, the acquisition mode of the list corresponding to each target object type may also be different, a default processor may be set as a default list processor, and the object list corresponding to the target object type is acquired from the dba_obj_cts table by modifying the policy mode of the default processor to the acquisition logic.
The source database is an Oracle database, the target database is a dream 8 database, after the target migration rule is determined, the type of the target object is determined to be a table space, a role and a materialized view according to migration content information in the target migration rule, and migration sequences recorded by migration sequence information are sequentially the table space, the role and the materialized view, wherein the table space and the role can acquire a list through a default processor, and the materialized view has specific logic, so that a materialized view processor is preset. When the data structure is migrated, firstly, acquiring an object list of a table space from a DBA_OBJECTS table through a default processor, wherein all OBJECTS of the object type of the table space in an Oracle database are recorded in the object list of the table space; then, obtaining an object list of the role from the DBA_OBJECTS table through a default processor, and similarly, recording all OBJECTS of the object type of the role in the Oracle database in the object list of the role; finally, an object list of the materialized view is obtained from the Oracle database through the materialized view processor, and all objects of the object type of the materialized view in the Oracle database are recorded in the object list.
It may be understood that after a plurality of object lists are sequentially obtained according to migration sequence information, source DDL definition statements of each source object may be sequentially converted into target SQL statements according to migration sequence information, and the obtained plurality of target SQL statements may be sequentially arranged to obtain migration tasks, so that a target database may execute the target SQL statements according to the arrangement sequence, and a data structure created in the target database corresponds to a data structure of the source database in terms of structure level, number of objects, and object attribute.
In an embodiment, referring to fig. 5, step S102 of the embodiment shown in fig. 1 further includes, but is not limited to, the following steps:
step S501, obtaining target conversion processors corresponding to each target object type from a preset conversion processor pool, wherein the target conversion processors are used for indicating a grammar mapping relation between a source DDL definition statement and a target SQL statement;
step S502, converting the source DDL definition statement of each source object into a corresponding target SQL statement by using a target conversion processor corresponding to each target object type.
It should be noted that after the source DDL definition statement is obtained, a target conversion processor corresponding to the target object type may be determined, for example, the target object type is a tablespace, the target conversion processor may be a tablespace processor, for example, the target object type is a table, and the target conversion processor may be a table processor.
It should be noted that, through the above embodiment, the object list can be obtained from each target object type according to the preset migration content and migration sequence, so as to determine the source object, in addition, since the content and the attribute of different target object types are different, a conversion processor may be further configured for each target object type, and the operation of the grammar conversion logic may be performed on each source object of the target object class through the corresponding conversion processor. For example, a grammar conversion operation is preset for each attribute object, taking a target object type as an example, a plurality of corresponding grammar conversion operations are acquired for a plurality of attributes of the target object type, and the plurality of grammar conversion operations are pre-packaged into a conversion processor.
Illustratively, the target object type is a table, and for each object in the table, the corresponding attribute includes a number of rows, a number of columns, and a value, and then the syntax conversion operation of the number of rows, the syntax conversion operation of the number of columns, and the conversion operation of the value may be packaged into a table conversion processor, and the source DDL definition statement of the source object of the table is converted into the target SQL statement by the table conversion processor.
Illustratively, the target object type is a function, a storage process or a package, and the attribute of each source object not only includes the definition of a variable, but also includes the content recorded with the running logic, and the syntax conversion operation of the variable and the syntax analysis operation of the running logic can be packaged into a conversion processor, and the variable and the running logic involved in the source DDL definition statement of the function, the storage process or the package are converted into a target SQL statement by the conversion processor.
It should be noted that, the source operation logic information of the source object is determined through analysis of the logic processor, so as to complete the corresponding logic operation, for example, in the table processor, through processing logic mapped to the table field types, content analysis can be completed for programs such as functions, storage processes, packages and the like, and incompatible statements in the DDL definition statements are converted, so that the obtained target SQL statement can carry operation logic in the active data structure, thereby ensuring that the created target data structure is kept logically consistent.
In an embodiment, the migration rule further includes a field type mapping rule and a rights mapping rule, the field type mapping rule is used to indicate a mapping relationship of a field type between the source database and the target database, the rights mapping rule is used to indicate a mapping relationship of an operation right between the source database and the target database, referring to fig. 6, step S502 of the embodiment shown in fig. 5 further includes, but is not limited to, the following steps:
step S601, obtaining a source field type and a source operation authority in a source DDL definition statement;
step S602, determining a target field type corresponding to the source field type according to a field type mapping rule;
Step S603, determining a target operation authority corresponding to the source operation authority according to the authority mapping rule;
in step S604, for each source object, the source DDL definition statement is converted into a target SQL statement using a target conversion processor, where the target SQL statement includes a target field type and a target operation authority.
It should be noted that, the field type mapping rule may be a mapping relationship between the source database and the target database of the same field type, for example, the RAW type of the Oracle database corresponds to the varbinary type of the dan meng 8 database, and because the field type mapping rule is preset, the source field type can be accurately converted into the target field type, so as to ensure the accuracy of the conversion. Similarly, the rights mapping rule may be a translation relationship of the same rights between the source database and the target database, e.g., CREATE PUBLIC DATABASE LINK rights of the Oracle database correspond to CREATE LINK rights of the Daphne 8 database.
It should be noted that after determining the target field type and the target authority corresponding to the target database, the source field type and the source authority in the source DDL definition statement may be replaced with the target field type and the target authority in the target SQL statement, so that the target conversion processor may convert the source DDL definition statement into the target SQL statement conforming to the syntax of the target database from three dimensions of syntax, field type and authority, thereby ensuring that the source data structure may be accurately migrated to the target database.
In an embodiment, referring to fig. 7, step S104 of the embodiment shown in fig. 1 further includes, but is not limited to, the following steps:
step S701, based on migration sequence information, creating migration tasks according to all SQL sentences;
step S702, obtaining user authority configuration and role authority configuration of a source database;
step S703, generating a permission configuration statement according to the user permission configuration and the role permission configuration;
step S704, adding the permission configuration statement to the migration task.
It should be noted that, since the data structure of the source database generally has a plurality of source objects, after obtaining a plurality of target SQL statements, a series of object migration tasks may be generated, and the migration of the data structure is completed by executing the series of object migration tasks through the target database.
After determining the object migration task, the related authority statement such as the user and the role related to the target SQL statement may be used to create the SQL task, so as to ensure that the created target data structure has the corresponding user and role.
It should be noted that the object migration task may be generated according to an object acquisition order when the source object list is acquired, so as to ensure that the target database creates a corresponding target data structure according to a predetermined order, and ensure that an object hierarchy of the target data structure is the same as the source database.
In one embodiment, referring to fig. 8, after step S104 of the embodiment shown in fig. 1 is performed, the following steps are included, but not limited to:
step S801, obtaining a migration operation of a target database in response to a target SQL statement;
step S802, summarizing all migration operations and generating a migration report.
In the migration process, the migration operations of the target database in response to the target SQL statement may be summarized and a migration report may be generated, and operations such as pre-examination, grammar conversion and the like executed in the source database may be added to the migration report, for example, migration object statistics, more named object list, invalid object list, time-consuming ranking of migration tasks and the like are recorded in the migration report, so that the convenience of monitoring the migration process is improved.
Referring to fig. 9, the embodiment of the present application further provides a database structure migration apparatus, which may implement the above database structure migration method, where the database structure migration apparatus 900 includes:
the object generating module 901, a rule obtaining module, configured to obtain a preset target migration rule according to the identification information of the source database and the identification information of the target database;
the object obtaining module 902 is configured to determine a target object type to be migrated from a data structure of the source database according to a target migration rule, and obtain a source object belonging to the target object type;
The statement conversion module 903 is configured to obtain a source DDL definition statement of a source object from a source database, and convert the source DDL definition statement into a target SQL statement that conforms to a syntax of a target database;
the migration module 904 is configured to create a migration task according to the target SQL statement, and control the target database to execute the migration task, so as to create a target object in a target data structure of the target database.
The specific implementation of the database structure migration device is basically the same as the specific embodiment of the database structure migration method described above, and will not be described herein again.
The embodiment of the application also provides electronic equipment, which comprises a memory and a processor, wherein the memory stores a computer program, and the processor realizes the database structure migration method when executing the computer program. The electronic equipment can be any intelligent terminal including a tablet personal computer, a vehicle-mounted computer and the like.
Referring to fig. 10, fig. 10 illustrates a hardware structure of an electronic device according to another embodiment, the electronic device includes:
the processor 1001 may be implemented by a general purpose central processing unit (Central Processing Unit, CPU), a microprocessor, an application specific integrated circuit (Application Specific Integrated Circuit, ASIC), or one or more integrated circuits, etc. for executing related programs to implement the technical solutions provided by the embodiments of the present application;
The Memory 1002 may be implemented in the form of a Read Only Memory (ROM), a static storage device, a dynamic storage device, or a random access Memory (Random Access Memory, RAM). The memory 1002 may store an operating system and other application programs, and when the technical solutions provided in the embodiments of the present application are implemented by software or firmware, relevant program codes are stored in the memory 1002, and the processor 1001 invokes a database structure migration method to execute the embodiments of the present application;
an input/output interface 1003 for implementing information input and output;
the communication interface 1004 is configured to implement communication interaction between the present device and other devices, and may implement communication in a wired manner (e.g. USB, network cable, etc.), or may implement communication in a wireless manner (e.g. mobile network, WIFI, bluetooth, etc.);
a bus 1005 for transferring information between the various components of the device (e.g., the processor 1001, memory 1002, input/output interface 1003, and communication interface 1004);
wherein the processor 1001, the memory 1002, the input/output interface 1003, and the communication interface 1004 realize communication connection between each other inside the device through the bus 1005.
The embodiment of the application also provides a storage medium, which is a computer readable storage medium, and the storage medium stores a computer program, and the computer program realizes the database structure migration method when being executed by a processor.
The memory, as a non-transitory computer readable storage medium, may be used to store non-transitory software programs as well as non-transitory computer executable programs. In addition, the memory may include high-speed random access memory, and may also include non-transitory memory, such as at least one magnetic disk storage device, flash memory device, or other non-transitory solid state storage device. In some embodiments, the memory optionally includes memory remotely located relative to the processor, the remote memory being connectable to the processor through a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.
The database structure migration method, device, equipment and storage medium provided by the embodiment of the application acquire a preset target migration rule according to the identification information of the source database and the identification information of the target database; determining a target object type to be migrated from a data structure of a source database according to a target migration rule, and acquiring a source object belonging to the target object type; acquiring a source DDL definition statement of a source object from a source database, and converting the source DDL definition statement into a target SQL statement conforming to the grammar of a target database; and creating a migration task according to the target SQL statement, and controlling the target database to execute the migration task so as to create a target object in a target data structure of the target database. According to the technical scheme of the embodiment, the source DDL definition statement of each source object in the data structure of the source database can be automatically acquired, the target object is automatically created in the target database after being automatically converted into the target SQL statement, rapid automatic migration of the database structure is realized, and migration efficiency and user experience are effectively improved.
The embodiments described in the embodiments of the present application are for more clearly describing the technical solutions of the embodiments of the present application, and do not constitute a limitation on the technical solutions provided by the embodiments of the present application, and as those skilled in the art can know that, with the evolution of technology and the appearance of new application scenarios, the technical solutions provided by the embodiments of the present application are equally applicable to similar technical problems.
It will be appreciated by those skilled in the art that the technical solutions shown in the figures do not constitute limitations of the embodiments of the present application, and may include more or fewer steps than shown, or may combine certain steps, or different steps.
The above described apparatus embodiments are merely illustrative, wherein the units illustrated as separate components may or may not be physically separate, i.e. may be located in one place, or may be distributed over a plurality of network elements. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
Those of ordinary skill in the art will appreciate that all or some of the steps of the methods disclosed above, functional modules/units in the system electronics may be implemented as software, firmware, hardware, and suitable combinations thereof.
The terms "first," "second," "third," "fourth," and the like in the description of the present application and in the above-described figures, if any, are used for distinguishing between similar objects and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used may be interchanged where appropriate such that embodiments of the present application described herein may be implemented in sequences other than those illustrated or otherwise described herein. Furthermore, the terms "comprises," "comprising," and "having," and any variations thereof, are intended to cover a non-exclusive inclusion, such that a process, method, system, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.
It should be understood that in this application, "at least one" means one or more, and "a plurality" means two or more. "and/or" for describing the association relationship of the association object, the representation may have three relationships, for example, "a and/or B" may represent: only a, only B and both a and B are present, wherein a, B may be singular or plural. The character "/" generally indicates that the context-dependent object is an "or" relationship. "at least one of" or the like means any combination of these items, including any combination of single item(s) or plural items(s). For example, at least one (one) of a, b or c may represent: a, b, c, "a and b", "a and c", "b and c", or "a and b and c", wherein a, b, c may be single or plural.
In the several embodiments provided in this application, it should be understood that the disclosed apparatus and method may be implemented in other ways. For example, the above-described apparatus embodiments are merely illustrative, and for example, the above-described division of units is merely a logical function division, and there may be another division manner in actual implementation, for example, a plurality of units or components may be combined or may be integrated into another system, or some features may be omitted, or not performed. Alternatively, the coupling or direct coupling or communication connection shown or discussed with each other may be an indirect coupling or communication connection via some interfaces, devices or units, which may be in electrical, mechanical or other form.
The units described above as separate components may or may not be physically separate, and components shown as units may or may not be physical units, may be located in one place, or may be distributed over a plurality of network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
The integrated units, if implemented in the form of software functional units and sold or used as stand-alone products, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present application may be embodied essentially or in part or all of the technical solution or in part in the form of a software product stored in a storage medium, including multiple instructions to cause a computer device (which may be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the methods of the various embodiments of the present application. And the aforementioned storage medium includes: various media capable of storing programs, such as a U disk, a removable hard disk, a ROM, a RAM, a magnetic disk, or an optical disk.
The units involved in the embodiments of the present application may be implemented by means of software, or may be implemented by means of hardware, and the described units may also be provided in a processor. Wherein the names of the units do not constitute a limitation of the units themselves in some cases.
From the above description of embodiments, those skilled in the art will readily appreciate that the example embodiments described herein may be implemented in software, or may be implemented in software in combination with the necessary hardware. Thus, the technical solution according to the embodiments of the present application may be embodied in the form of a software product, which may be stored in a non-volatile storage medium (may be a CD-ROM, a usb disk, a mobile hard disk, etc.) or on a network, and includes several instructions to cause a computing electronic device (may be a personal computer, a server, a touch terminal, or a network electronic device, etc.) to perform the method according to the embodiments of the present application.
Other embodiments of the present application will be apparent to those skilled in the art from consideration of the specification and practice of the embodiments disclosed herein. This application is intended to cover any variations, uses, or adaptations of the application following, in general, the principles of the application and including such departures from the present disclosure as come within known or customary practice within the art to which the application pertains.
It is to be understood that the present application is not limited to the precise arrangements and instrumentalities shown in the drawings, which have been described above, and that various modifications and changes may be effected without departing from the scope thereof. The scope of the application is limited only by the appended claims.
While the preferred embodiment of the present invention has been described in detail, the present invention is not limited to the above embodiment, and various equivalent modifications and substitutions can be made by those skilled in the art without departing from the spirit of the present invention, and these equivalent modifications and substitutions are intended to be included in the scope of the present invention as defined in the appended claims.
Claims (10)
1. A method for migrating a database structure, the method comprising:
acquiring a preset target migration rule according to the identification information of the source database and the identification information of the target database;
determining a target object type to be migrated from a data structure of the source database according to the target migration rule, and acquiring a source object belonging to the target object type;
acquiring a source DDL definition statement of the source object from the source database, and converting the source DDL definition statement into a target SQL statement conforming to the grammar of the target database;
And creating a migration task according to the target SQL statement, and controlling the target database to execute the migration task so as to create the target object in a target data structure of the target database.
2. The database structure migration method of claim 1, wherein the target migration rule comprises a pre-check rule, the method further comprising, prior to the determining the target object type to be migrated from the data structure of the source database according to the target migration rule:
determining a pre-inspection item according to the pre-inspection rule, wherein the pre-inspection item characterizes migration resources between the source database and the target database;
determining a migration resource demand, and checking the pre-check item according to the migration resource demand to obtain a pre-check result, wherein the pre-check result is used for indicating whether the migration resource between the source database and the target database meets the migration resource demand.
3. The database structure migration method according to claim 1, wherein the target migration rule further includes migration content information and migration order information, the migration content information is used to indicate a plurality of the target object types to be migrated, the migration order information is used to indicate a migration order of a plurality of the target object types, the determining the target object types to be migrated from the data structure of the source database according to the target migration rule, and acquiring the source objects attributed to the target object types, including:
Determining a plurality of target object types from a data structure of the source database according to the migration content information;
determining a list processor corresponding to each target object type, wherein the list processor is used for acquiring an object list comprising a plurality of objects, and the objects in the object list belong to the target object types corresponding to the object list;
and based on the migration sequence information, operating each list processor to acquire a plurality of object lists from the source database, and determining the objects in the object lists as the source objects.
4. A database structure migration method according to claim 3, wherein said converting said source DDL definition statements into target SQL statements conforming to the syntax of said target database comprises:
acquiring target conversion processors corresponding to the target object types from a preset conversion processor pool, wherein the target conversion processors are used for indicating the grammar mapping relation between the source DDL definition statement and the target SQL statement;
and respectively converting the source DDL definition statement of each source object into the corresponding target SQL statement by using the target conversion processor corresponding to each target object type.
5. The database structure migration method of claim 4, wherein the migration rules further include a field type mapping rule for indicating a mapping relationship of a field type between the source database and the target database and a rights mapping rule for indicating a mapping relationship of an operation right between the source database and the target database, the converting the source DDL definition statement of each of the source objects into the corresponding target SQL statement, respectively, comprising:
acquiring a source field type and a source operation authority in the source DDL definition statement;
determining a target field type corresponding to the source field type according to the field type mapping rule;
determining a target operation authority corresponding to the source operation authority according to the authority mapping rule;
for each of the source objects, converting the source DDL definition statements into the target SQL statements with the target conversion processor, wherein the target SQL statements include the target field type and the target operation rights.
6. A database structure migration method according to claim 3, wherein said creating a migration task from said target SQL statement comprises:
Creating the migration task according to all SQL sentences based on the migration sequence information;
acquiring user authority configuration and role authority configuration of the source database;
generating a permission configuration statement according to the user permission configuration and the role permission configuration;
and adding the permission configuration statement to the migration task.
7. The database structure migration method of claim 1, wherein after said creating a migration task from said target SQL statement, the method further comprises:
obtaining a migration operation of the target database in response to the target SQL statement;
summarizing all the migration operations and generating a migration report.
8. A database structure migration apparatus, the apparatus comprising:
the rule acquisition module is used for acquiring a preset target migration rule according to the identification information of the source database and the identification information of the target database;
the object acquisition module is used for determining the type of the target object to be migrated from the data structure of the source database according to the target migration rule and acquiring the source object belonging to the type of the target object;
the statement conversion module is used for acquiring a source DDL definition statement of the source object from the source database and converting the source DDL definition statement into a target SQL statement conforming to the grammar of the target database;
And the migration module is used for creating a migration task according to the target SQL statement, and controlling the target database to execute the migration task so as to create the target object in a target data structure of the target database.
9. An electronic device comprising a memory storing a computer program and a processor implementing the database structure migration method of any one of claims 1 to 7 when the computer program is executed by the processor.
10. A computer readable storage medium storing a computer program, characterized in that the computer program, when executed by a processor, implements the database structure migration method of any one of claims 1 to 7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211347144.5A CN116028466A (en) | 2022-10-31 | 2022-10-31 | Database structure migration method, device, equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202211347144.5A CN116028466A (en) | 2022-10-31 | 2022-10-31 | Database structure migration method, device, equipment and storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN116028466A true CN116028466A (en) | 2023-04-28 |
Family
ID=86076002
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202211347144.5A Pending CN116028466A (en) | 2022-10-31 | 2022-10-31 | Database structure migration method, device, equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN116028466A (en) |
-
2022
- 2022-10-31 CN CN202211347144.5A patent/CN116028466A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10853338B2 (en) | Universal data pipeline | |
| US8977600B2 (en) | System and method for continuous analytics run against a combination of static and real-time data | |
| US20180322168A1 (en) | Technologies for asynchronous querying | |
| US9280568B2 (en) | Zero downtime schema evolution | |
| US10180984B2 (en) | Pivot facets for text mining and search | |
| US10585909B2 (en) | Task-execution in a DBMS using stored procedures | |
| US20170161291A1 (en) | Database table conversion | |
| JP2015072688A (en) | Background format optimization for enhanced sql-like queries in hadoop | |
| CN111324610A (en) | Data synchronization method and device | |
| US20170161511A1 (en) | Database public interface | |
| US10740286B1 (en) | Migration task validation before data migration | |
| EP2869220B1 (en) | Networked database system | |
| CN113051268A (en) | Data query method, data query device, electronic equipment and storage medium | |
| CN111966692A (en) | Data processing method, medium, device and computing equipment for data warehouse | |
| US11567969B2 (en) | Unbalanced partitioning of database for application data | |
| US11693859B2 (en) | Systems and methods for data retrieval from a database indexed by an external search engine | |
| Bienko et al. | IBM cloudant: database as a service advanced topics | |
| US11567957B2 (en) | Incremental addition of data to partitions in database tables | |
| US8768947B2 (en) | System and method for implementing unique primary keys across enterprise databases | |
| CN116028466A (en) | Database structure migration method, device, equipment and storage medium | |
| Chullipparambil | Big data analytics using Hadoop tools | |
| US11693834B2 (en) | Model generation service for data retrieval | |
| US11500874B2 (en) | Systems and methods for linking metric data to resources | |
| US20190370242A1 (en) | Electronic database and method for forming same | |
| CN112732704B (en) | Data processing method, device and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |