Method and device for dynamically generating database script

Abstract

The invention discloses a method and device for dynamically generating database scripts, and the method comprises the steps: designing and recording a plurality of attributes of database tables, fields and indexes required by a database, and recording the attributes as a current version; calling a Velocity engine, and generating a corresponding CREATE TABLE SQL (Structured Query Language) statement so as to create a database; recording the changed database table attributes, field attributes and index attributes as newly added versions; and comparing the attribute of the newly added version with the attribute of the current version, and calling a Velocity engine to generate a corresponding DDL SQL statement so as to update the database. According to the method, the DDL SQL statement is dynamically generated and executed in combination with the Velocity template engine technology, the function of automatically creating and updating the database table is achieved, the development efficiency is improved, the error rate is reduced, import of the existing physical table is supported, flexibility and expandability are achieved, maintenance and upgrading are easy, and the automation level of database management is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of computer databases, and in particular to a method and device for dynamically generating database scripts. Background Art

[0002] In the traditional business development process, developers usually need to manually write SQL statements to create and modify database tables. This method is not only time-consuming and labor-intensive, but also prone to errors. Every time the database structure changes, developers need to manually modify the corresponding SQL statements, which undoubtedly increases the workload of development and maintenance. In order to improve development efficiency and reduce human errors, this project proposes a method and device for dynamically generating database scripts.

[0003] With the rapid development of information technology and the continuous changes in business needs, the management and optimization of database structure as the basic support of information systems have become increasingly important. However, traditional database management methods often rely on manual operations by developers, which is not only inefficient but also difficult to adapt to rapidly changing business needs. Summary of the invention

[0004] In view of the low efficiency of current database management methods, the present invention provides a method and device for dynamically generating database scripts to improve development efficiency, reduce maintenance costs, and promote the automation and intelligent development of database management.

[0005] To achieve the above object, the embodiments of the present invention adopt the following technical solutions: A method for dynamically generating a database script, the method comprising: Design and record several properties of database tables, fields and indexes required by the database, and record them as the current version; Call the Velocity engine, input the current version properties to generate the corresponding CREATE TABLE SQL statement, and execute the CREATE TABLE SQL statement to create a database; When a database change is detected, the target database attribute is obtained and recorded as a new version; Compare the attributes of the new version with the attributes of the current version and obtain the differences between the attributes of the new version and the attributes of the current version; The Velocity engine is called to generate corresponding DDL SQL statements according to the attribute differences, the DDL SQL statements are executed to modify the database and the attributes of the modified database are recorded as the current version.

[0006] Preferably, the database design is performed by designing and recording several properties of the database tables, fields and indexes required by the database through the CRUD page, and the CRUD page includes a save table design page, a save field design page and a save index design page.

[0007] Preferably, the attributes of the database tables, fields and indexes required by the database include one or more of table name, table comment, field id, field name, field comment, field default value, field type, field length, whether the field is a primary key, whether the field is auto-incrementing, whether the field is required, index ID, index name, fields included in the index, and index type.

[0008] Preferably, the current version is managed with an iteration number, and the corresponding current version is recorded as a historical version with a corresponding number before executing the DDL SQL statement.

[0009] Preferably, several properties of database tables, fields and indexes required by the database are designed and recorded and imported through existing database properties.

[0010] Preferably, importing through existing database attributes includes: Query existing database attributes; Save the queried existing database attributes to the current version of the generated database attributes.

[0011] The method for dynamically generating database scripts according to claim 6 is characterized in that when it is found that the existing database attributes are missing during the query of the existing database attributes, the preset default values ​​are filled in.

[0012] On the other hand, a device for dynamically generating a database script is characterized in that the device includes: Design module: used to design and record several properties of database tables, fields and indexes required by the database; Generation module: used to generate the corresponding CREATE TABLE SQL statement to create a database; Preprocessing module: used to record the changed database table attributes, field attributes and index attributes; Change module: used to call the Velocity engine to generate DDL SQL statements to change the database.

[0013] Preferably, the design module includes a first design unit: used to manually record several properties of database tables, fields and indexes required by the database, and also includes one or more of the following units: The second design unit is used to provide a CRUD page and record several properties of database tables, fields and indexes required by the database through the CRUD page; The third design unit is used to import existing database attributes to record several attributes of database tables, fields and indexes required by the database.

[0014] Preferably, the change database is managed by the project iteration number, and the corresponding change database is recorded as a historical database with the corresponding number before executing the DDL SQL statement.

[0015] Advantages of the implementation of the present invention: The method for dynamically generating database scripts realizes the function of automatically creating and updating database tables by automatically comparing database table designs and combining Velocity template engine technology to dynamically generate and execute DDL SQL statements, thereby improving development efficiency, reducing error rates, supporting the import of existing physical tables, having flexibility and scalability, being easy to maintain and upgrade, and improving the level of database management automation. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0017] Figure 1 The present invention is a flowchart of a method for dynamically generating database scripts. DETAILED DESCRIPTION

[0018] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0019] Example 1 A method for dynamically generating a database script, the method comprising: S1: Design and record several properties of the database tables, fields, and indexes required by the database, and record them as the current version.

[0020] Specifically, a version number is recorded after the attribute, such as v1. The attributes of the database table, field and index required by the database include table name, table comment, field id, field name, field comment, field default value, field type, field length, whether the field is the primary key, whether the field is auto-increment, whether the field is required, index ID, index name, fields included in the index, and index type.

[0021] S2: Call the Velocity engine, input the current version attributes to generate the corresponding CREATE TABLE SQL statement, and execute the CREATE TABLE SQL statement to create a database; Specifically, the Velocity template used by the CREATE TABLE SQL statement is as follows: [CREATE TABLE IF NOT EXISTS $tableName ( #set($COMMA = ',') #foreach($field in $fields) ## The last field and no index #if($foreach.last && ($indexes == $null || $indexes.isEmpty())) #set($COMMA = '') #end #parse("base_field.vm")$COMMA #end #foreach($index in $indexes) #if($foreach.last) #set($COMMA = '') #end #if($index.indexType != 'NORMAL') $index.indexType #end KEY $index.indexName ( #parse("base_index.vm") )$COMMA #end ) #if($tableDesc != $null) COMMENT '$tableDesc' #end CHARSET = 'utf8mb4' collate = 'utf8mb4_unicode_ci';] S3: When database tables, fields, and indexes change, the changed database table attributes, field attributes, and index attributes are recorded as new versions; Specifically, a version number is recorded after the attribute, such as v2, and the current version is managed by increasing the number of project iterations. For example, at 00:00:00 on 2024-10-28, the database attribute changes, and its current version is v2 created at 00:00:00 on 2024-10-27, and the new version should be recorded as v3.

[0022] S4: Compare the properties of the new version with the properties of the current version, and call the Velocity engine to generate corresponding DDL SQL statements according to the differences between the properties of the new version and the current version, execute the DDL SQL statements and record the properties of the database after execution as the current version.

[0023] Specifically, before executing the DDL SQL statement, the corresponding current version is recorded as a historical version with a corresponding number. The difference between the new version and the current version may exist in one or more of the following situations: The database table name has changed.

[0024] The database table comment changes.

[0025] One to more fields were added.

[0026] One or more fields were deleted.

[0027] The names of one or more fields were modified.

[0028] Modified the properties of one or more fields.

[0029] One to more indexes were added.

[0030] One or more indexes were deleted.

[0031] One or more index names were modified.

[0032] One to more index types were modified.

[0033] Modified one to more fields included in the index.

[0034] The corresponding DDL statements generated based on the differences between the above new version and the current version are as follows: When the database table name changes, a RENAME TABLE ... TO ...; statement is generated and executed automatically.

[0035] When the database table description changes, an ALTER TABLE ... COMMENT ...; statement is generated and executed automatically.

[0036] The newly added fields generate the ALTER TABLE ... ADD ...; SQL statement and execute it automatically.

[0037] Deleting a column generates the ALTER TABLE ... DROP COLUMN ...; SQL statement and executes it automatically.

[0038] When a field name changes, an ALTER TABLE ... CHANGE ... ...; SQL statement is generated and executed automatically.

[0039] Modifying field properties generates ALTER TABLE MODIFY ...; SQL statements and executes them automatically.

[0040] The new index generates the CREATE INDEX ... ON ...(...); SQL statement and executes it automatically.

[0041] Deleting an index generates an ALTER TABLE ... DROP INDEX ...; SQL statement and executes it automatically.

[0042] Modifying the index name, index type, and fields included in the index will generate ALTER TABLE... DROP INDEX ...; and CREATE INDEX ... ON ...(...); SQL statements and execute them automatically. That is, the index is deleted first and then re-created.

[0043] Example 2 A method for dynamically generating a database script, the method comprising: S1: Design and record several properties of database tables, fields and indexes required by the database, and record them as the current version; Specifically, the database is designed through the addition, deletion, modification and query page, and the addition, deletion, modification and query page includes a save table design page, a save field design page and a save index design page. There is no need to manually operate the database, which is simple, convenient and fast.

[0044] S2: Call the Velocity engine, input the current version attributes to generate the corresponding CREATE TABLE SQL statement, and execute the CREATE TABLE SQL statement to create a database; S3: When database tables, fields, and indexes change, the changed database table attributes, field attributes, and index attributes are recorded as new versions; S4: Compare the properties of the new version with the properties of the current version, and call the Velocity engine to generate corresponding DDL SQL statements according to the differences between the properties of the new version and the current version, execute the DDL SQL statements and record the properties of the database after execution as the current version.

[0045] Example 3 A method for dynamically generating a database script, the method comprising: S1: Design and record several properties of database tables, fields and indexes required by the database, and record them as the current version; Specifically, several properties of database tables, fields and indexes required for designing and recording the database can be imported through existing database properties.

[0046] Importing through existing database properties includes: Query existing database attributes; Save the queried existing database attributes to the current version of the generated database attributes.

[0047] When the existing database attributes are found to be missing during the query, the preset default values ​​are filled in.

[0048] With this technology, developers do not need to manually enter this information, thus avoiding errors caused by human operational errors and improving the accuracy and efficiency of database attribute management.

[0049] Specifically, the import implementation method is: (1) By querying INFORMATION_SCHEMA.TABLES, the information of the physical table can be saved in online_design_table. The specific query content is: [ SELECT TABLE_NAME AS tableName, TABLE_COMMENT AS tableDesc FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'xxx';] Save the queried tableName and tableDesc to the table_name and table_desc fields of online_design_table. For other fields of online_design_table, the default values ​​are automatically filled in.

[0050] By querying INFORMATION_SCHEMA.COLUMNS, you can save the physical table field information to online_design_table_field. The specific query content is: [ SELECT TABLE_NAME AS tableName, COLUMN_NAME as fieldName, COLUMN_COMMENT as fieldDesc, COLUMN_DEFAULT as fieldDefault, UPPER(DATA_TYPE) as fieldType, IFNULL(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION) ASfieldLength, COLUMN_KEY as fieldIsPrimaryKey, IS_NULLABLE as nullAble FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'xxx' AND TABLE_NAME = #{tableName} ORDER BY ORDINAL_POSITION] Similarly, the queried information is saved in online_design_table_field, and other fields of online_design_table_field are automatically filled with default values.

[0051] By querying INFORMATION_SCHEMA. STATISTICS, you can save the physical table index information to online_design_table_index. The specific query content is: [SELECT DISTINCT TABLE_NAME as tableName, INDEX_NAME as indexName, NON_UNIQUE as nonUnique, COLUMN_NAME as fieldName, SEQ_IN_INDEX FROM INFORMATION_SCHEMA.STATISTICS <!-- Temporarily hardcode the data source --> WHERE TABLE_SCHEMA = 'xxx' <!-- Exclude the primary key --> AND INDEX_NAME != 'PRIMARY' AND TABLE_NAME = #{tableName} ORDER BY indexName, SEQ_IN_INDEX] Similarly, save the queried information correspondingly into online_design_table_index, and for other fields of online_design_table_index, fill in the default values automatically.

[0052] S2: Call the Velocity engine, input the current version attributes to generate the corresponding CREATE TABLE SQL statement, and execute the CREATE TABLE SQL statement to create the database; S3: When the database tables, fields, and indexes change, record the changed database table attributes, field attributes, and index attributes, and record them as a new version; S4: Compare the attributes of the new version with the attributes of the current version, and call the Velocity engine to generate the corresponding DDL SQL statement according to the differences between the attributes of the new version and the current version, execute the DDL SQL statement, and record the attributes of the database after execution as the current version.

[0053] Embodiment 4 An apparatus for dynamically generating database scripts, the apparatus includes: A design module: used to design and record several attributes of database tables, fields, and indexes required for the database; A generation module: used to generate the corresponding CREATE TABLE SQL statement to create the database; A preprocessing module: used to record the changed database table attributes, field attributes, and index attributes; A change module: used to call the Velocity engine to generate DDL SQL statements to change the database.

[0054] Specifically, the design module includes: The first design unit: used to manually record several properties of database tables, fields and indexes required by the database; The second design unit is used to provide a CRUD page and record several properties of database tables, fields and indexes required by the database through the CRUD page.

[0055] Example 5

[0056] A device for dynamically generating a database script, the device comprising: Design module: used to design and record several properties of database tables, fields and indexes required by the database; Generation module: used to generate the corresponding CREATE TABLE SQL statement to create a database; Preprocessing module: used to record the changed database table attributes, field attributes and index attributes; Change module: used to call the Velocity engine to generate DDL SQL statements to change the database.

[0057] Specifically, the design modules include: The first design unit: used to manually record several properties of database tables, fields and indexes required by the database; The second design unit is used to provide a CRUD page and record several properties of database tables, fields and indexes required by the database through the CRUD page; The third design unit is used to import existing database attributes to record several attributes of database tables, fields and indexes required by the database.

[0058] Example 6 A device for dynamically generating a database script, the device comprising: Design module: used to design and record several properties of database tables, fields and indexes required by the database; Generation module: used to generate the corresponding CREATE TABLE SQL statement to create a database; Preprocessing module: used to record the changed database table attributes, field attributes and index attributes; Change module: used to call the Velocity engine to generate DDL SQL statements to change the database.

[0059] Specifically, the design modules include: The first design unit: used to manually record several properties of database tables, fields and indexes required by the database; The second design unit is used to provide a CRUD page and record several properties of database tables, fields and indexes required by the database through the CRUD page; The third design unit is used to import existing database attributes to record several attributes of database tables, fields and indexes required by the database.

[0060] Specifically, the change database is managed by the project iteration number, and the corresponding change database is recorded as a historical database with a corresponding number before executing the DDL SQL statement.

[0061] Advantages of the implementation of the present invention: The method for dynamically generating database scripts realizes the function of automatically creating and updating database tables by automatically comparing database table designs and combining Velocity template engine technology to dynamically generate and execute DDL SQL statements, thereby improving development efficiency, reducing error rates, supporting the import of existing physical tables, having flexibility and scalability, being easy to maintain and upgrade, and improving the level of database management automation.

[0062] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any changes or substitutions that can be easily thought of by any technician familiar with the art within the technical scope disclosed in the present invention should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention shall be based on the protection scope of the claims.

Claims

1. A method for dynamically generating a database script, characterized in that: The method comprises: Design and record several properties of database tables, fields and indexes required by the database, and record them as the current version; Call the Velocity engine, input the current version properties to generate the corresponding CREATE TABLE SQL statement, and execute the CREATE TABLE SQL statement to create a database; When a database change is detected, the target database attribute is obtained and recorded as a new version; Compare the attributes of the new version with the attributes of the current version and obtain the differences between the attributes of the new version and the attributes of the current version; The Velocity engine is called to generate corresponding DDL SQL statements according to the attribute differences, the DDL SQL statements are executed to modify the database and the attributes of the modified database are recorded as the current version.

2. The method for dynamically generating database scripts according to claim 1, characterized in that: The design and recording of several properties of database tables, fields and indexes required by the database is performed through the addition, deletion, modification and query page method to design the database. The addition, deletion, modification and query page includes a table design page, a field design page and an index design page.

3. The method for dynamically generating database scripts according to claim 1, characterized in that: The attributes of the database tables, fields and indexes required by the database include one or more of table name, table comment, field id, field name, field comment, field default value, field type, field length, whether the field is a primary key, whether the field is auto-incrementing, whether the field is required, index ID, index name, fields included in the index, and index type.

4. The method for dynamically generating database scripts according to claim 1, characterized in that: The current version is managed with an iteration number, and the corresponding current version is recorded as a historical version with a corresponding number before executing the DDL SQL statement.

5. The method for dynamically generating database scripts according to claim 1, characterized in that: The design and recording of several properties of database tables, fields and indexes required for the database are imported through existing database properties.

6. The method for dynamically generating database scripts according to claim 5, characterized in that: The importing through existing database attributes includes: Query existing database attributes; Save the queried existing database attributes to the current version of the generated database attributes.

7. The method for dynamically generating database scripts according to claim 6, characterized in that: When the query of the existing database attributes finds that the existing database attributes are missing, the preset default value is filled.

8. A device for dynamically generating database scripts, characterized in that: The device comprises: Design module: used to design and record several properties of database tables, fields and indexes required by the database; Generation module: used to generate the corresponding CREATE TABLE SQL statement to create a database; Preprocessing module: used to record the changed database table attributes, field attributes and index attributes; Change module: used to call the Velocity engine to generate DDL SQL statements to change the database.

9. The device for dynamically generating database scripts according to claim 8, characterized in that: The design module includes a first design unit: used to manually record several properties of database tables, fields and indexes required by the database, and also includes one or more of the following units: The second design unit is used to provide a CRUD page and record several properties of database tables, fields and indexes required by the database through the CRUD page; The third design unit is used to import existing database attributes to record several attributes of database tables, fields and indexes required by the database.

10. The device for dynamically generating database scripts according to claim 8, characterized in that: The change database is managed by the project iteration number, and the corresponding change database is recorded as a historical database with the corresponding number before executing the DDL SQL statement.

Images