Method and device for generating data migration sql statement based on mapping, equipment and storage medium

Abstract

The invention discloses a method and device for generating a data migration sql statement based on mapping, equipment and a storage medium. By reading a document template, public migration configuration in the document template is obtained and put into a public configuration set, and private migration configuration in the document template is obtained and put into a private configuration set; obtaining field relation mapping according to the public configuration set and the private configuration set, and generating a table relation mapping set according to the field relation mapping; and generating an xml file from the table relation mapping set, and generating a final sql statement according to the xml file. According to the method and the device for automatically converting the new and old tables and the mapping of the new and old fields in the visual page into the data migration sql statement, the high labor cost caused by manual writing of the data migration sql statement and the high error risk caused by manual writing in the prior art can be effectively solved.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to related fields requiring data migration from old to new databases when a system is reconstructed, and more particularly to a method, device and storage medium for generating data migration SQL statements based on mapping. Background Art

[0002] In the current complex scenario of system reconstruction, ensuring the smooth transition of data from the existing system to the new system is a crucial task.

[0003] As the most intuitive tool for viewing the differences between the old and new data, the visualization page is currently widely used to sort out the mapping relationships between the old and new tables and between the old and new fields. Based on these accurately sorted mapping relationships, the subsequent data migration work can be carried out in an orderly manner. However, the existing technology relies on manually written SQL statements to implement specific data migration logic, which brings a high risk of uncertainty and errors, and the labor cost of manually writing data migration SQL statements is also very high.

[0004] Therefore, providing a tool that automatically converts the mapping of new and old tables and new and old fields in a visualization page into data migration SQL statements is a technical problem that technical personnel in this field urgently need to solve. Summary of the invention

[0005] To achieve the above objectives, in a first aspect, the present invention provides a method for generating a data migration SQL statement based on mapping, comprising:

[0006] Read the document template, obtain the public migration configuration in the document template and put it into the public configuration collection, obtain the private migration configuration in the document template and put it into the private configuration collection;

[0007] Acquire a field relationship mapping according to the public configuration set and the private configuration set, and generate a table relationship mapping set according to the field relationship mapping;

[0008] Generate an XML file from the table relationship mapping set, wherein an initial migration SQL statement is generated according to the migration field information and associated table information in the table relationship mapping set, a value in the public configuration is assigned according to the migration condition field in the table relationship mapping set, and an import statement for the target table migration is generated according to the required fields of the target table;

[0009] Generate the final SQL statement according to the XML file.

[0010] Further, generating the initial migration SQL statement according to the migration field information and the associated table information in the table relationship mapping set includes:

[0011] Traverse the table relationship mapping set and the field mapping set; obtain the source table export fields according to the table dimension, and take the fields with the migration identifier of 1 for list; export the fields according to whether there are special SQL fields, and splice them after associating the exported fields with the table.

[0012] Furthermore, assigning a value to a value in a public configuration according to the migration condition field in the table relationship mapping set includes:

[0013] The table relationship mapping set is obtained, and each table migration condition field is traversed in turn, matched with the specific value of the public configuration in the table relationship mapping set, and the matching results are spliced ​​to obtain the conditions field.

[0014] Furthermore, generating the import statement for migrating the target table according to the fields required by the target table includes:

[0015] The table mapping set and the field mapping set are traversed, and the target table import fields are obtained according to the table dimension. After the field list with the identification 1 is migrated, brackets are added on both sides, and the separator is used in the middle for separation.

[0016] In a second aspect, the present invention also provides a device for generating data migration SQL statements based on mapping, comprising: an input unit, a relationship mapping unit, a format conversion unit, and an output unit;

[0017] The input unit is used to read the document template, obtain the public migration configuration in the document template and put it into the public configuration set, and obtain the private migration configuration in the document template and put it into the private configuration set;

[0018] The relationship mapping unit is used to obtain a field relationship mapping according to the public configuration set and the private configuration set, and generate a table relationship mapping set according to the field relationship mapping;

[0019] The format conversion unit is used to generate an XML file from the table relationship mapping set, wherein an initial migration SQL statement is generated according to the migration field information and the associated table information in the table relationship mapping set, a migration condition field in the table relationship mapping set is assigned a value in the public configuration, and an import statement for the target table migration is generated according to the required fields of the target table;

[0020] The output unit is used to generate the final SQL statement according to the XML file.

[0021] Further, generating the initial migration SQL statement according to the migration field information and the associated table information in the table relationship mapping set includes:

[0022] Traverse the table relationship mapping set and the field mapping set; obtain the source table export fields according to the table dimension, and take the fields with the migration identifier of 1 for list; export the fields according to whether there are special SQL fields, and splice them after associating the exported fields with the table.

[0023] Furthermore, assigning a value to a value in a public configuration according to the migration condition field in the table relationship mapping set includes:

[0024] The table relationship mapping set is obtained, and each table migration condition field is traversed in turn, matched with the specific value of the public configuration in the table relationship mapping set, and the matching results are spliced ​​to obtain the conditions field.

[0025] Furthermore, generating the import statement for migrating the target table according to the fields required by the target table includes:

[0026] The table mapping set and the field mapping set are traversed, and the target table import fields are obtained according to the table dimension. After the field list with the identification 1 is migrated, brackets are added on both sides, and the separator is used in the middle for separation.

[0027] In a third aspect, the present invention provides a computing device, comprising:

[0028] A memory for storing program instructions;

[0029] The processor is used to call the program instructions stored in the memory, and execute the above-mentioned method for generating data migration SQL statements based on mapping according to the obtained program instructions.

[0030] In a fourth aspect, the present invention provides a computer-readable storage medium, including computer-readable instructions. When a computer reads and executes the computer-readable instructions, the above-mentioned method of generating data migration SQL statements based on mapping is implemented. The readable medium may be multiple, and the multiple readable media can run independently of each other.

[0031] Compared with the prior art, this solution reads the document template, obtains the public migration configuration therein and puts it into the public configuration set, obtains the private migration configuration therein and puts it into the private configuration set; obtains the field relationship mapping according to the public configuration set and the private configuration set, generates the table relationship mapping set according to the field relationship mapping; generates an xml file from the table relationship mapping set, and then generates the final sql statement according to the xml file. The method and device provided by the present invention for automatically converting the mapping of new and old tables and new and old fields in the visualization page into data migration sql statements can effectively solve the high labor cost and high error risk caused by the manual writing of data migration sql statements in the prior art. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] In order to more clearly illustrate the embodiments of the present disclosure or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present disclosure. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0033] Figure 1 A flowchart of an embodiment of a method for generating data migration SQL statements based on mapping provided by the present invention;

[0034] Figure 2 A structural block diagram of an embodiment of a device for generating data migration SQL statements based on mapping provided by the present invention;

[0035] Figure 3 The present invention provides a schematic structural diagram of an electronic device. DETAILED DESCRIPTION

[0036] The embodiments of the present application are described in detail below, and examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present application, and cannot be interpreted as limiting the present application.

[0037] It will be understood by those skilled in the art that, unless otherwise stated, the singular forms "a", "an" and "the" used herein may also include plural forms. It should be further understood that the term "comprising" used in the specification of the present application refers to the presence of features, integers, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or groups thereof. The term "and / or" used herein includes all or any unit and all combinations of one or more associated listed items.

[0038] In order to make the objectives, technical solutions and advantages of the present application more clear, the specific implementation methods of the present application will be further described in detail below with reference to the accompanying drawings.

[0039] The core of the present invention is to provide a method for generating data migration SQL statements based on mapping. Figure 1 The figure is a flow chart of the method for generating data migration SQL statements based on mapping provided in this embodiment. In order to better understand the method, Figure 1 The technical solution of this embodiment is described in detail:

[0040] Step S1: read the document template, obtain the public migration configuration in the document template and put it into the public configuration set, obtain the private migration configuration in the document template and put it into the private configuration set;

[0041] In a specific implementation, the document template is a visual page template, and the public configuration is obtained according to the visual page template and put into a public configuration collection (publiclist).

[0042] Get the public migration configuration content as follows:

[0043] Merchant ID: This tool supports migration by merchant dimension. This field configures the merchant ID that needs to be migrated.

[0044] Start Date: This tool supports migration by time range. This field configures the start date of the migration.

[0045] End Date: This tool supports migration by time range. This field configures the end date of the migration.

[0046] Migration concurrency control: This tool supports controlling the number of multi-threaded processing during migration.

[0047] Read this part of the content in the visualization page and put it into the public configuration collection (the public configuration involved in the migration process of each table).

[0048] Get the private configuration according to the visual page template and put it into the private configuration collection (privatelist).

[0049] Get the private migration configuration content as follows:

[0050] Column A: table name. Assume that tables A, B, and C need to be migrated this time.

[0051] Column B: segment key. During migration, some tables may have a large amount of data, and the migration time of a single table is too long. To solve this problem, the concept of segment key is proposed. A single table is split into multiple parts according to the segment key for migration.

[0052] Column C: The database field type of the segment key, mainly to solve the format problem of generating SQL statements.

[0053] Column E: Segment step size, which refers to the size of a segment.

[0054] Read this part of the content in the visualization page and put it into the private configuration collection (private configuration involved in the migration process of a single table).

[0055] Step S2: acquiring a field relationship mapping according to the public configuration set and the private configuration set, and generating a table relationship mapping set according to the field relationship mapping;

[0056] In a specific implementation, the acquisition table mapping relationship is as follows:

[0057] Column A: the name of the original table, for example, table A needs to be migrated this time.

[0058] Column B: The Chinese name of the original table, mainly used to describe Table A for easy viewing.

[0059] Column C: segment key. As described in the private configuration above, the segment key of table A is field a.

[0060] Column D: Migration conditions. This field indicates whether the table needs to be migrated by merchant dimension. If so, the field name of the merchant number in the table is used; whether the table needs to be migrated by date range. If so, the date field in the table is used.

[0061] Column E: The name of the target table. For example, the name of the target table this time is A~table.

[0062] Column F: The Chinese name of the target table, mainly used to describe the A~ table for easy viewing.

[0063] Column G: custom conditions, some additional migration configuration conditions. For example, when migrating table A this time, only the data with b=1 is migrated, which is subsequently spliced ​​after where.

[0064] Column H: Associated table, which describes whether the original table needs to be associated with other tables to retrieve data when the table is migrated. For example, when migrating table A, it needs to be associated with table B for migration.

[0065] Column I: Association field: If there is an association table, you need to fill in the association field in this column. For example, if tables A and B are associated, the association field is Ac=Bc.

[0066] Column J: MD5 verification: After migration, the MD5 value of the exported file needs to be verified for data verification, so the exported data needs to be sorted, such as orderby b.

[0067] Read the visualization page content and put it into the table list collection (table relationship mapping collection).

[0068] In a specific implementation, the field relationship mapping is obtained and put into fieldlist, and the visualization page content is read and put into the field relationship mapping set as follows:

[0069] Column A: the name of the original table, for example, table A needs to be migrated this time.

[0070] Column B: Field names of the original table. For example, this time Table A involves migrating fields a, b, c, d, e, and f.

[0071] Column C: The Chinese name of the field in the original table, describing the field information for easy viewing.

[0072] Column H: Whether to migrate, describing whether the field is migrated to the new table.

[0073] Column I: The name of the target table. For example, the name of the target table is A~.

[0074] Column J: Field names of the target table. For example, the target table A~ involves migrating fields a~, b~, c~, d~, e~, and f~.

[0075] R column: special SQL statements, referring to some special processing when migrating new and old fields:

[0076] (1) When data is retrieved from an associated table, for example, the c~ field takes its value from the c field of table B.

[0077] (2) There is a change in the dictionary values ​​of the new and old fields. For example, the Y value of the e field corresponds to 1 of the e~ field; the N value of the e field corresponds to 0 of the e~ field.

[0078] (3) If the new and old fields have different types, for example, if the f field is changed from date type to datetime, you need to concatenate the fields before migration: cast(concat(f,'040000')as datetime).

[0079] Read the visualization page content and put it into the field collection (the original table target table field relationship mapping collection).

[0080] Step S3: Generate an XML file from the table relationship mapping set, wherein an initial migration SQL statement is generated according to the migration field information and associated table information in the table relationship mapping set, and a value in the public configuration is assigned according to the migration condition field in the table relationship mapping set, and an import statement for the target table migration is generated according to the required fields of the target table;

[0081] In a specific implementation, based on the result list of the read visualization page, an XML file that is more convenient for program processing is generated, and the key fields thereof are described as follows:

[0082] exportsql field: Generates the initial migration SQL statement based on the migration field information and associated table information of the original table.

[0083] The specific generation process is as follows:

[0084] 1. Traverse the table mapping collection (tablelist) and field mapping collection (fieldlist)

[0085] 2. Obtain the source table export fields according to the table dimension, and obtain the list of fields with the migration flag set to 1.

[0086] 3. Export field: exportField

[0087] Fields with special sql --> exportField += fieldlist.specialsql;

[0088] Fields without special SQL --> exportField += fieldlist. Source table field name;

[0089] 4. Associated table fields: joinField = tablelist.Associated table + tablelist.Associated field

[0090] 5. exportsql is finally completed by splicing several parts: "<![CDATA[select"+exportField+joinField+"]]>"

[0091] The final result is shown in the figure:

[0092] <! [CDATA[select a,b,Bc,d,case when e='Y'then'1'when e='N'then'0'end,

[0093] cast(concat(f,'040000')as datetime)from Ajoin B on Ac=Bc]]>

[0094] Conditions field: According to the migration condition field in the table mapping relationship, the value is assigned to the value in the public configuration. The specific generation process is as follows:

[0095] 1. Get the table mapping set (tablelist) and traverse it in sequence:

[0096] 2. Take each table migration condition field in tablelist and match it with the specific value in the public configuration of publiclist, as shown in the example:

[0097] (1) The first condition: the merchant number field. The merchant numbers configured in the public configuration are 111, 222, and 333. The final merchant condition field after splicing is:

[0098] g(merchant number)in('111','222','333')

[0099] (2) The second condition: the date field. The date range configured in the public configuration is 20230101 to 20231025. According to the left-closed and right-open time interval rule, the final spliced ​​date condition is:

[0100] 20230101 <= h(Date) <2023102>5

[0101] The custCondition field: additional migration conditions. Take the table mapping set (tablelist) and traverse it in sequence. For example, the fixed condition for migrating Table A in the above figure is b = 1.

[0102] The patitionkey field: the value range of the segmentation key. The specific generation process is as follows:

[0103] 1. Take the table mapping set (tablelist) and traverse it in sequence:

[0104] 2. Take each table name field in tablelist and match the specific value in the private configuration of privatelist.

[0105] Field a is the segmentation key, and the segmentation range is one segmentation every 30 days.

[0106] The importFields field: the fields that need to be imported into the target table, which are used to generate the import statements for migrating the target table. The specific generation process is as follows:

[0107] 1. Traverse the table mapping set (tablelist) and the field mapping set (fieldlist)

[0108] 2. Obtain the fields to be imported into the target table according to the table dimension, and take the list of fields with the migration flag as 1

[0109] 3. Concatenate parentheses on both sides and separate them with commas in the middle.

[0110] Step S4: Generate the final SQL statement according to the xml file.

[0111] Based on the xml file, generate the final version of the migration SQL statement, as follows:

[0112] 1. Query field generation:

[0113] Take the value in the tag exportsql and directly concatenate it as the query field of the select statement.

[0114] 2. Segmentation condition generation:

[0115] for (segment number = migration days / segmentation step size) {

[0116] Segment start time: migration date - segmentation step size * number of loops;

[0117] Segment end time: migration date - segmentation step size * (number of loops - 1);

[0118] patKeyCon = between + start time of segmentation + and + end time of segmentation;

[0119] }

[0120] For example, if the total number of days of data to be migrated in Table A is 90, the segmentation key is a, and the segmentation size is 30, then this table will be split into 3 migration export SQL statements, and the three segmentation conditions are as follows:

[0121] a between today - 30 and today

[0122] a between today - 60 and today - 30

[0123] a between today - 90 and today - 60

[0124] 3. Generation of migration conditions:

[0125] Date condition: mirDateCon = start date value <= date identifier < end date value;

[0126] Merchant condition: mirMrchCon = merchant identifier in merchant number value

[0127] The results are as follows:

[0128] g(merchant number) in ('111', '222', '333') and 20230101 <= h(date) <2023102>5

[0129] 4. Generation of custom conditions:

[0130] Just directly splice the value in the tag custCondition.

[0131] 5. Generation of sorting conditions:

[0132] Just directly splice the value in the tag sqlSort.

[0133] Finally, the migration SQL statement exported from the original table.

[0134] In a preferred embodiment, the generating the initial migration SQL statement according to the migration field information and the associated table information in the table relationship mapping set includes:

[0135] Traverse the table relationship mapping set and the field mapping set; obtain the source table export fields according to the table dimension, and list the fields with a migration identifier of 1 among them; export the fields according to whether there are special SQL fields, and splice them after associating with the exported fields.

[0136] In a preferred embodiment, assigning a value in a public configuration to a migration condition field in the table relationship mapping set includes:

[0137] The table relationship mapping set is obtained, and each table migration condition field is traversed in turn, matched with the specific value of the public configuration in the table relationship mapping set, and the matching results are spliced ​​to obtain the conditions field.

[0138] In a preferred embodiment, generating the import statement for migrating the target table according to the fields required by the target table includes:

[0139] The table mapping set and the field mapping set are traversed, and the target table import fields are obtained according to the table dimension. After the field list with the identification 1 is migrated, brackets are added on both sides, and the separator is used in the middle for separation.

[0140] In the solution of this embodiment, by reading the document template, obtaining the public migration configuration therein and putting it into the public configuration set, obtaining the private migration configuration therein and putting it into the private configuration set; obtaining the field relationship mapping according to the public configuration set and the private configuration set, generating the table relationship mapping set according to the field relationship mapping; generating an xml file from the table relationship mapping set, and then generating the final sql statement according to the xml file. The method and device provided by the present invention for automatically converting the mapping of the new and old tables and the new and old fields in the visualization page into the data migration sql statement can effectively solve the high labor cost and high error risk caused by the manual writing of the data migration sql statement in the prior art.

[0141] In one embodiment, the present invention provides a device for generating data migration SQL statements based on mapping, see Figure 2 The structure diagram of a device for generating data migration SQL statements based on mapping provided by this embodiment is shown, and the device includes: an input unit 10, a relationship mapping unit 20, a format conversion unit 30, and an output unit 40;

[0142] The input unit 10 is used to read the document template, obtain the public migration configuration in the document template and put it into the public configuration set, and obtain the private migration configuration in the document template and put it into the private configuration set;

[0143] The relationship mapping unit 20 is used to obtain a field relationship mapping according to the public configuration set and the private configuration set, and generate a table relationship mapping set according to the field relationship mapping;

[0144] The format conversion unit 30 is used to generate an XML file from the table relationship mapping set, wherein an initial migration SQL statement is generated according to the migration field information and the associated table information in the table relationship mapping set, a value in the public configuration is assigned according to the migration condition field in the table relationship mapping set, and an import statement for the target table migration is generated according to the required fields of the target table;

[0145] The output unit 40 is used to generate a final SQL statement according to the XML file.

[0146] In a preferred embodiment, generating the initial migration SQL statement according to the migration field information and the associated table information in the table relationship mapping set includes:

[0147] Traverse the table relationship mapping set and the field mapping set; obtain the source table export fields according to the table dimension, and take the fields with the migration identifier of 1 for list; export the fields according to whether there are special SQL fields, and splice them after associating the exported fields with the table.

[0148] In a preferred embodiment, assigning a value in a public configuration to a migration condition field in the table relationship mapping set includes:

[0149] The table relationship mapping set is obtained, and each table migration condition field is traversed in turn, matched with the specific value of the public configuration in the table relationship mapping set, and the matching results are spliced ​​to obtain the conditions field.

[0150] In a preferred embodiment, generating the import statement for migrating the target table according to the fields required by the target table includes:

[0151] The table mapping set and the field mapping set are traversed, and the target table import fields are obtained according to the table dimension. After the field list with the identification 1 is migrated, brackets are added on both sides, and the separator is used in the middle for separation.

[0152] In the solution of this embodiment, by reading the document template, obtaining the public migration configuration therein and putting it into the public configuration set, obtaining the private migration configuration therein and putting it into the private configuration set; obtaining the field relationship mapping according to the public configuration set and the private configuration set, generating the table relationship mapping set according to the field relationship mapping; generating an xml file from the table relationship mapping set, and then generating the final sql statement according to the xml file. The method and device provided by the present invention for automatically converting the mapping of the new and old tables and the new and old fields in the visualization page into the data migration sql statement can effectively solve the high labor cost and high error risk caused by the manual writing of the data migration sql statement in the prior art.

[0153] The device for generating data migration SQL statements based on mapping in this embodiment is used to implement the aforementioned method for generating data migration SQL statements based on mapping. Therefore, the specific implementation method of the device for generating data migration SQL statements based on mapping can be seen from the specific implementation method of the method for generating data migration SQL statements based on mapping in the previous text. For example, the input unit 10 is used to implement step S1 in the above-mentioned method for generating data migration SQL statements based on mapping, and the relationship mapping unit 20 is used to implement step S2 in the above-mentioned method for generating data migration SQL statements based on mapping, etc. Therefore, its specific implementation method can refer to the description of the corresponding embodiments of each part. The device provided by the present invention is attached to the method provided by the present invention, and its specific effects can refer to the above-mentioned method, which will not be repeated here.

[0154] In one embodiment, a computing device is provided, comprising:

[0155] A memory for storing program instructions;

[0156] A processor is used to implement the steps of any of the above-mentioned methods for generating data migration SQL statements based on mapping when executing the computer program.

[0157] As an example, Figure 3 A schematic diagram of the structure of an electronic device applicable to the embodiment of the present application is shown. Figure 3 As shown, the electronic device 2000 includes: a processor 2001 and a memory 2003. The processor 2001 and the memory 2003 are connected, such as through a bus 2002. Optionally, the electronic device 2000 may also include a transceiver 2004. It should be noted that in actual applications, the transceiver 2004 is not limited to one, and the structure of the electronic device 2000 does not constitute a limitation on the embodiments of the present application.

[0158] The processor 2001 is applied in the embodiment of the present application to implement the method shown in the above method embodiment. The transceiver 2004 may include a receiver and a transmitter. The transceiver 2004 is applied in the embodiment of the present application to implement the function of the electronic device of the embodiment of the present application communicating with other devices when executed.

[0159] Processor 2001 may be a CPU (Central Processing Unit), a general purpose processor, a DSP (Digital Signal Processor), an ASIC (Application Specific Integrated Circuit), an FPGA (Field Programmable Gate Array), or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. It may implement or execute various exemplary logic blocks, modules, and circuits described in conjunction with the disclosure of this application. Processor 2001 may also be a combination that implements computing functions, such as a combination of one or more microprocessors, a combination of a DSP and a microprocessor, and the like.

[0160] The bus 2002 may include a path to transmit information between the above components. The bus 2002 may be a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus. The bus 2002 may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 3 Only one thick line is used in the diagram, but this does not mean that there is only one bus or only one type of bus.

[0161] The memory 2003 can be a ROM (Read Only Memory) or other types of static storage devices that can store static information and instructions, a RAM (Random Access Memory) or other types of dynamic storage devices that can store information and instructions, or an EEPROM (Electrically Erasable Programmable Read Only Memory), a CD-ROM (Compact Disc Read Only Memory) or other optical disk storage, optical disk storage (including compressed optical disk, laser disk, optical disk, digital versatile disk, Blu-ray disk, etc.), a magnetic disk storage medium or other magnetic storage device, or any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed by a computer, but is not limited to these.

[0162] Optionally, the memory 2003 is used to store application code for executing the solution of the present application, and the execution is controlled by the processor 2001. The processor 2001 is used to execute the application code stored in the memory 2003 to implement the investment and financing project recommendation method provided in any embodiment of the present application.

[0163] The electronic device provided in the embodiment of the present application is applicable to any embodiment of the above method and will not be described in detail here.

[0164] The method for generating data migration sql statements based on mapping provided by the present invention has obvious advantages and beneficial effects compared with the prior art. By reading the document template, the public migration configuration therein is obtained and put into the public configuration set, and the private migration configuration therein is obtained and put into the private configuration set; the field relationship mapping is obtained according to the public configuration set and the private configuration set, and the table relationship mapping set is generated according to the field relationship mapping; the table relationship mapping set is generated into an xml file, and then the final sql statement is generated according to the xml file. The method and device for automatically converting the mapping of new and old tables and new and old fields in the visualization page into data migration sql statements provided by the present invention can effectively solve the high labor cost and high error risk caused by the manual writing of data migration sql statements in the prior art.

[0165] In one embodiment, a computer-readable storage medium is provided, including computer-readable instructions. When a computer reads and executes the computer-readable instructions, the steps of the method for generating data migration sql statements based on mapping described above are implemented. The method for generating data migration sql statements based on mapping provided by the present invention has obvious advantages and beneficial effects compared with the prior art. By reading a document template, the public migration configuration is obtained and put into a public configuration set, and the private migration configuration is obtained and put into a private configuration set; the field relationship mapping is obtained according to the public configuration set and the private configuration set, and a table relationship mapping set is generated according to the field relationship mapping; the table relationship mapping set is generated into an xml file, and then the final sql statement is generated according to the xml file. The method and device for automatically converting the mapping of new and old tables and new and old fields in a visual page into data migration sql statements provided by the present invention can effectively solve the high labor cost and high error risk caused by the manual writing of data migration sql statements in the prior art.

[0166] Through the description of the above implementation methods, technical personnel in the relevant field can understand that for the convenience and simplicity of description, only the division of the above-mentioned functional modules is used as an example. In actual applications, the above-mentioned functions can be assigned to different functional modules as needed, that is, the internal structure of the specific device can be divided into different functional modules to complete all or part of the functions described above.

[0167] Those of ordinary skill in the art will appreciate that the modules and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the composition and steps of each example have been generally described in terms of function in the above description. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of this application.

[0168] In several embodiments covered by the present application, it should be understood that the disclosed devices, equipment and methods can be implemented in other ways. For example, the division of modules is only a logical function division, and there may be other division methods in actual implementation, such as multiple modules or components can be combined or integrated into another device, or some features can be ignored or not executed. In addition, the mutual coupling or direct coupling or communication connection shown or discussed can be an indirect coupling or communication connection through some interfaces, devices or modules, or it can be an electrical, mechanical or other form of connection.

[0169] The modules described as separate components may or may not be physically separated, and the components displayed as modules may or may not be physical modules, that is, they may be located in one place or distributed on multiple network modules. Some or all of the modules may be selected according to actual needs to achieve the purpose of the embodiments of the present application.

[0170] In addition, each functional module in each embodiment of the present application can be implemented in the form of hardware or in the form of software functional modules. If these functional modules are implemented in the form of software functional modules and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on such an understanding, the technical solution of the present application is essentially or part of the prior art that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a computer program product, which includes one or more computer instructions. When loading and executing computer program instructions on a computer, the process or function according to the embodiment of the present application is generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices. The computer program product is stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium, for example, a computer instruction can be transmitted from a website site, a computer, a server or a data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) mode to another website site, computer, server or data center. The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server or a data center that includes one or more available media integrations. The available medium may be a magnetic medium (eg, a floppy disk, a hard disk, a magnetic tape), an optical medium (eg, a DVD), or a semiconductor medium (eg, a solid state disk (SSD)).

[0171] The above contents are merely examples and explanations of the structure of the present invention. The technicians in this technical field may make various modifications or additions to the specific embodiments described or replace them in a similar manner. As long as they do not deviate from the structure of the invention or exceed the scope defined by the claims, they should all fall within the protection scope of the present invention.

Claims

1. A method for generating data migration SQL statements based on mapping, characterized in that: include: Read the document template, obtain the public migration configuration in the document template and put it into the public configuration collection, obtain the private migration configuration in the document template and put it into the private configuration collection; Acquire a field relationship mapping according to the public configuration set and the private configuration set, and generate a table relationship mapping set according to the field relationship mapping; Generate an XML file from the table relationship mapping set, wherein an initial migration SQL statement is generated according to the migration field information and associated table information in the table relationship mapping set, a value in the public configuration is assigned according to the migration condition field in the table relationship mapping set, and an import statement for the target table migration is generated according to the required fields of the target table; Generate the final SQL statement according to the XML file.

2. The method for generating data migration SQL statements based on mapping according to claim 1, characterized in that: The generating the initial migration SQL statement according to the migration field information and the associated table information in the table relationship mapping set includes: Traverse the table relationship mapping set and the field mapping set; obtain the source table export fields according to the table dimension, and take the fields with the migration identifier of 1 for list; export the fields according to whether there are special SQL fields, and splice them after associating the exported fields with the table.

3. The method for generating data migration SQL statements based on mapping according to claim 1, characterized in that: The assigning of values ​​to the public configuration according to the migration condition field in the table relationship mapping set includes: The table relationship mapping set is obtained, and each table migration condition field is traversed in turn, matched with the specific value of the public configuration in the table relationship mapping set, and the matching results are spliced ​​to obtain the conditions field.

4. The method for generating data migration SQL statements based on mapping according to claim 1, characterized in that: The step of generating the import statement for migrating the target table according to the fields required by the target table includes: The table mapping set and the field mapping set are traversed, and the target table import fields are obtained according to the table dimension. After the field list with the identification 1 is migrated, brackets are added on both sides, and the separator is used in the middle for separation.

5. A device for generating data migration SQL statements based on mapping, characterized in that: The device comprises: an input unit, a relationship mapping unit, a format conversion unit, and an output unit; The input unit is used to read the document template, obtain the public migration configuration in the document template and put it into the public configuration set, and obtain the private migration configuration in the document template and put it into the private configuration set; The relationship mapping unit is used to obtain a field relationship mapping according to the public configuration set and the private configuration set, and generate a table relationship mapping set according to the field relationship mapping; The format conversion unit is used to generate an XML file from the table relationship mapping set, wherein an initial migration SQL statement is generated according to the migration field information and the associated table information in the table relationship mapping set, a migration condition field in the table relationship mapping set is assigned a value in the public configuration, and an import statement for the target table migration is generated according to the required fields of the target table; The output unit is used to generate the final SQL statement according to the XML file.

6. The device for generating data migration SQL statements based on mapping according to claim 5, characterized in that: The generating the initial migration SQL statement according to the migration field information and the associated table information in the table relationship mapping set includes: Traverse the table relationship mapping set and the field mapping set; obtain the source table export fields according to the table dimension, and take the fields with the migration identifier of 1 for list; export the fields according to whether there are special SQL fields, and splice them after associating the exported fields with the table.

7. The device for generating data migration SQL statements based on mapping according to claim 5, characterized in that: The assigning of values ​​to the public configuration according to the migration condition field in the table relationship mapping set includes: The table relationship mapping set is obtained, and each table migration condition field is traversed in turn, matched with the specific value of the public configuration in the table relationship mapping set, and the matching results are spliced ​​to obtain the conditions field.

8. The device for generating data migration SQL statements based on mapping according to claim 5, characterized in that: The step of generating the import statement for migrating the target table according to the fields required by the target table includes: The table mapping set and the field mapping set are traversed, and the target table import fields are obtained according to the table dimension. After the field list with the identification 1 is migrated, brackets are added on both sides, and the separator is used in the middle for separation.

9. A computing device, characterized in that include: A memory for storing program instructions; A processor is used to call the program instructions stored in the memory, and execute the method according to any one of claims 1 to 4 according to the obtained program instructions.

10. A computer-readable storage medium, characterized in that: It includes computer-readable instructions. When a computer reads and executes the computer-readable instructions, it implements the method as claimed in any one of claims 1 to 4. There can be multiple readable media, and the multiple readable media can operate independently of each other.

Images