Upgrade script collating system and method

Abstract

The application discloses an upgrading script arrangement system and method, wherein the corresponding system comprises a connection module, a reading module, a grouping module and a first generation module.The connection module is used for connecting a database of function development; the reading module is used for reading all line data of a form of an Excel file created by an upgrading script; the grouping module is used for grouping line data with the same function module name and script type; and the first generation module is used for determining and extracting content required for generating a corresponding upgrading script according to the corresponding script type, operation type, operation object name and the database for each group of line data, and splicing sql according to the extracted content and a predetermined first database type format corresponding to the corresponding script type and the database to form a corresponding first database type upgrading script.The application can automatically and batch generate upgrading scripts in conformity with specifications, and improve software product development efficiency.

Description

Technical Field

[0001] This application relates to the field of electronic digital data processing, and in particular to an upgrade script processing system and method. Background Technology

[0002] Software product development is closely linked to databases. Detailed design involves designing the database table structure; feature development involves creating tables, views, and data within the database; and product upgrades and iterations require compiling database upgrade scripts for use in product version upgrades.

[0003] However, database operations in the software development process have some shortcomings:

[0004] - When designing software products in detail, Excel spreadsheets are often used to design the table structure to accommodate changes and improvements in requirements, making it easier to modify and view. Therefore, during feature development, it's necessary to create or import each table individually based on the Excel structure, which becomes cumbersome when there are many tables.

[0005] -After development is completed, organizing the upgrade scripts is also troublesome. They must be written according to certain script specifications, and often each SQL script needs to be checked to see if it has been executed before, in order to avoid errors due to repeated execution.

[0006] - Manually writing upgrade scripts is particularly prone to errors due to even the slightest oversight, and troubleshooting is also very time-consuming;

[0007] - The work of creating a large number of data tables or writing upgrade scripts is tedious and affects the work enthusiasm of software developers.

[0008] Therefore, a technical solution is needed to simplify the work of software developers in creating tables and organizing upgrade scripts. Summary of the Invention

[0009] This invention provides an upgrade script organization system and method, which can automatically generate upgrade scripts that conform to specifications in batches, thereby improving the accuracy and efficiency of upgrade script organization.

[0010] In a first aspect of the present invention, an upgrade script management system is provided, the system comprising:

[0011] The connection module is used to connect to the database for feature development.

[0012] The reading module is used to read all rows of data from the form in the Excel file created by the upgrade script. Each row of data includes at least: function module name, script type, operation type, and operation object name. The script type is one of table, field, view, index, data, trigger, and function. The operation type is add or delete. The operation object name is the table name, field name, view name, index name, table name, trigger name, or function name corresponding to the corresponding script type.

[0013] The grouping module is used to group rows of data that have the same functional module name and script type.

[0014] The first generation module is used to determine and extract the content required to generate the corresponding upgrade script for each group of row data according to the corresponding script type, operation type, operation object name and the database, and to concatenate the extracted content and the SQL according to the predetermined first database type format corresponding to the corresponding script type and database to form the corresponding first database type upgrade script.

[0015] In a second aspect of the present invention, an upgrade script organization method is provided, the method comprising:

[0016] Database for connection functionality development;

[0017] Read all rows of data from the form that creates the Excel file from the upgrade script. Each row of data includes at least: function module name, script type, operation type, and operation object name. The script type is one of table, field, view, index, data, trigger, and function. The operation type is add or delete. The operation object name is the table name, field name, view name, index name, table name, trigger name, or function name corresponding to the script type.

[0018] Group rows of data that have the same functional module name and script type;

[0019] For each set of row data, the content required to generate the corresponding upgrade script is determined and extracted according to the corresponding script type, operation type, operation object name and the database. The extracted content and the SQL are concatenated with the predetermined first database type format corresponding to the corresponding script type and database to form the corresponding first database type upgrade script.

[0020] In a third aspect of the invention, a computer device is provided, including a processor, a memory, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the functions of a system according to a first aspect of the invention or the steps of a method according to a second aspect of the invention.

[0021] According to a fourth aspect of the present invention, a computer-readable storage medium is provided having a computer program stored thereon, which, when executed by a processor, implements the functions of a system according to a first aspect of the present invention or the steps of a method according to a second aspect of the present invention.

[0022] According to this invention, by reading all row data of the form in the Excel file created by the upgrade script, rows with the same functional module name and script type are grouped together. For each group of rows, the content required to generate the corresponding upgrade script is determined and extracted based on the corresponding script type, operation type, operation object name, and the database. Based on the extracted content and the predetermined first database type format corresponding to the corresponding script type and database, SQL is concatenated to form the corresponding first database type upgrade script. Users only need to download the Excel file template, fill in the template format, configure the database information, connect to the database, and submit the Excel file to automatically and batch generate upgrade scripts that meet the specifications. This improves the work efficiency of developers and greatly reduces the error rate of upgrade script preparation, effectively optimizing the software development process.

[0023] Other features and advantages of the present invention will become clearer after reading the detailed description of the embodiments of the present invention in conjunction with the accompanying drawings. Attached Figure Description

[0024] Figure 1 This is a block diagram of an embodiment of the system according to the present invention;

[0025] Figure 2 This is a flowchart of an embodiment of the method according to the present invention.

[0026] For clarity, these figures are schematic and simplified, showing only the details necessary for understanding the invention, while omitting other details. Detailed Implementation

[0027] The embodiments and examples of the present invention will now be described in detail with reference to the accompanying drawings.

[0028] The scope of the invention will become apparent from the detailed description given below. However, it should be understood that while the detailed description and specific examples illustrate preferred embodiments of the invention, they are given for illustrative purposes only.

[0029] Figure 1 A block diagram of a preferred embodiment of the upgrade script management system according to the present invention is shown.

[0030] This invention system can be implemented as a tool to assist in product software development. Users can download an Excel template for batch creation of upgrade scripts from this tool, and then fill in the Excel template according to the format to create an Excel file containing the upgrade script. The row data of the Excel template includes at least the functional module name, script type, operation type, and operation object name. The functional module name indicates the functional module involved in the upgrade, such as department cost, project cost, disease cost, etc. The script type can be one of (data) table, field, view, index, data, trigger, or function. The operation type is add or delete. The operation object name is the table name, field name, view name, index name, table name, trigger name, or function name corresponding to the script type. The row data of the Excel template may also include field attributes (field name, field type, length, precision, whether not null, etc.) and data filtering conditions. Field attributes are only required when the script type is field, and data filtering conditions are only required when the script type is data. The functional module name, script type, operation type, and field type can be designed as drop-down boxes, allowing only preset values ​​to be selected to avoid users entering incorrect data.

[0031] Configuration module 102 is used to configure the database type (e.g., SQL Server, Oracle), URL, driver, username and password for feature development, as well as to upload an Excel file created by the user according to the template-filled upgrade script.

[0032] Connection module 104 is used to connect to the corresponding database according to the configured database information.

[0033] The reading module 106 is used to read all rows of data from the form in the Excel file created by the upgrade script, with each row of data treated as a record. A corresponding script will be generated for each row of data.

[0034] Grouping module 108 is used to group row data with the same functional module name and script type into a group. Each group performs corresponding processing based on the script type, operation type and the status of the corresponding database.

[0035] The first generation module 110 is used to determine and extract the content required to generate the corresponding upgrade script for each set of row data, based on the corresponding script type, operation type, operation object name, and database status. It then concatenates the extracted content with a predefined first database type format corresponding to the script type and database to form the corresponding first database type upgrade script. Different scripts are distinguished by their script type and database type. Different script types have different formats. Similarly, different database types have different formats. The following explanation uses SQL Server as an example.

[0036] When the script type is table and the operation type is add, the table's field attributes (name, comment, type, length, default value, nullability), table description information, and field description information are retrieved based on the table name specified by the operation object name. Then, the retrieved attributes are concatenated into an SQL script according to a predefined SQL Server format (i.e., template). The template for concatenating a table upgrade script is shown below:

[0037]

[0038] The template above is just a process structure template. When the upgrade script is finally assembled, the relevant Chinese descriptions will be replaced with the corresponding database information. For example, "table name" will be replaced with "dept_cost_finance_dept" in the operation object name column, and "field name" will be replaced with "English field name in the database" retrieved based on the operation object name.

[0039] That is, when concatenating the script, first concatenate the following at the beginning: `if not exists(select 1 from sysobjects where id = object_id('table_name') and type = 'U')`, then concatenate the `create table` field name, then loop through and concatenate the obtained multiple fields (field names, field types, whether they are null, default values), concatenate the `GO` keyword, and finally concatenate the table description SQL.

[0040]

[0041]

[0042] Finally, the GO keyword is concatenated to generate the SQL Server table upgrade script.

[0043] When the script type is field and the operation type is add, the table field attributes (name, comment, type, length, default value, nullability) and field description information are retrieved based on the table name specified by the operation object name and the field name specified by the field attributes in the row data. Then, the retrieved attributes are concatenated into SQL according to a predefined SQL Server format (template). The field upgrade script concatenation template is shown below:

[0044]

[0045] That is, when constructing the upgrade script, first construct the script beginning with `if not exists(select * from syscolumns where id = object_id('table_name') and name = 'field_name')`, then construct the `begin` keyword, then construct the SQL statement `alter table_table_name add_field_name_check_if_type_is_empty`, then construct the SQL statement `execute sp_addextendedproperty 'MS_Description', 'field_comment', 'user', 'dbo', 'table', 'table_name', 'column', 'field_name';`, then construct the `end` keyword, then construct the `GO` keyword, finally generating the SQL Server field upgrade script.

[0046] When the script type is view and the operation type is add, the corresponding SQL for creating the view is obtained based on the view name specified by the operation object name; then, the script is assembled according to the predefined SQL Server format (template). The view upgrade script assembly template is shown below:

[0047]

[0048] That is, when assembling the upgrade script, first IF EXISTS(SELECT 1 FROM sys.views WHERE name = 'view name') is assembled at the beginning of the script, then the drop view view name is assembled, then the GO keyword is assembled, then the view creation script is assembled, and finally the GO keyword is assembled to generate the SQL Server view upgrade script.

[0049] When the script type is index and the operation type is insert, the corresponding SQL for creating the index is retrieved based on the index name specified by the operation object name; then, the script is assembled according to the predefined SQL Server format (template). The view upgrade script assembly template is shown below:

[0050]

[0051] That is, when constructing the upgrade script, first construct IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID('table_name',N'U')and name='index_name'), construct the begin keyword, construct the index script retrieved based on the index name, construct the end keyword, and finally construct the GO keyword to generate the SQL Server index upgrade script.

[0052] When the script type is data and the operation type is add, the specific field names and values ​​are retrieved based on the table name specified by the operation object name and the data filtering conditions in the corresponding row data; then, the script is assembled according to the prescribed SQL Server format (template). The data upgrade script assembly template is shown below:

[0053] IF NOT EXISTS(SELECT 1 FROM table_name filter condition)

[0054] INSERT INTO table_name(field1, field2) values(value1, value2);

[0055] GO

[0056] That is, when constructing the upgrade script, first construct the script beginning with IF NOT EXISTS(SELECT 1FROM table_name filter condition), then construct the INSERT INTO keyword, then construct the table name (field1, field2), then construct the values ​​(value1, value2), and finally construct the GO keyword to generate the SQL Server data upgrade script.

[0057] When the script type is data and the operation type is delete, the SQL is directly concatenated based on the operation object name and data filter conditions. Specifically, it concatenates "delete from operation object name where data filter conditions", then adds the GO keyword to generate the corresponding upgrade script.

[0058] When the script type is function and the operation type is "add", the corresponding creation function SQL is retrieved based on the function name specified by the operation object name; then, the upgrade script is assembled according to the predefined SQL Server format (template). The function upgrade script assembly template is shown below:

[0059]

[0060] That is, when assembling the upgrade script, first construct the script by concatenating the IF EXISTS(SELECT 1 FROM sysobjects where xtype='fn' and name='function name'), then construct the drop function name, then construct the GO keyword, then construct the retrieved creation function script, and finally construct the GO keyword to generate the SQL Server function upgrade script.

[0061] When the script type is trigger and the operation type is add, the corresponding create trigger SQL is retrieved based on the trigger name specified by the operation object name; then, the upgrade script is assembled according to the predefined SQL Server format (template). The trigger upgrade script assembly template is shown below:

[0062]

[0063] That is, when assembling the upgrade script, first construct the script by concatenating the following: IF EXISTS(SELECT 1 FROM sysobjects where id=object_id(N'[dbo].[trigger name]') AND OBJECTPROPERTY(id,N'isTrigger')=1), then construct the drop trigger name, then construct the GO keyword, then construct the retrieved trigger creation script, and finally construct the GO keyword to generate the SQL Server trigger upgrade script.

[0064] The naming module 112 is used to determine the upgrade script file name based on the functional module name, script type, database type, and / or date, and to name the upgrade script. For example, the upgrade script file name prefix can first be generated by combining the functional module name, script type, database type, and date, and then it can be determined whether the prefix has been used before. If it has not been used before, a sequence number such as 01 is used; otherwise, if it has been used before, the largest sequence number already used is added to 1. Then, the prefix plus the sequence number is concatenated to form the file name, which is used to name the generated upgrade script.

[0065] The system described above can automatically generate standardized, re-executable upgrade scripts in batches, thereby effectively shortening the time cost of software product development and improving development efficiency.

[0066] In this embodiment, the system further includes a second generation module, used to convert the field types of the current database into field types suitable for the second type of database according to a field type mapping table between different types of databases, and to concatenate SQL according to the previously extracted content and a predetermined second database type format corresponding to the corresponding script type to form a corresponding second database type upgrade script. Here, Oracle is used as an example of the second database type for illustration.

[0067] The mapping between SQL Server field types and Oracle field types is shown in the table below:

[0068]

[0069] The field mapping between SQL Server and Oracle is a many-to-one relationship. Bidirectional mapping is a marker that maps Oracle fields to SQL Server fields when the primary database is Oracle. For example, when an Oracle field is of type DATE, it is converted to a SQL Server field of type datetime instead of date.

[0070] After generating the corresponding SQL Server upgrade script, the SQL Server field types can be converted to Oracle-compatible field types according to the field type mapping relationship shown in the table above. For example, if the database to be connected is SQL Server and a certain field type is datetime, its type will be converted to DATE. Then, the SQL is concatenated according to the specified Oracle format and finally ends with a slash to generate the corresponding Oracle upgrade script, thereby enabling upgrades to different types of databases such as Oracle.

[0071] For example, the Oracle table upgrade script template is shown below:

[0072]

[0073] For example, the Oracle view upgrade script assembly template is shown below:

[0074]

[0075] Figure 2 A flowchart of a preferred embodiment of the upgrade script organization method according to the present invention is shown.

[0076] In step S202, connect to the database for function development;

[0077] In step S204, all row data of the form in the Excel file created by the upgrade script are read;

[0078] In step S206, rows of data with the same functional module name and script type are grouped together;

[0079] In step S208, for each group of row data, the content required to generate the corresponding upgrade script is determined and extracted according to the corresponding script type, operation type, operation object name and the database. The extracted content and the SQL are concatenated with the predetermined first database type format corresponding to the corresponding script type and database to form the corresponding first database type upgrade script.

[0080] When appropriately replaced by the corresponding process, combined Figure 1 Various implementations of the described system can be applied to this. Figure 2 The method shown is implemented. The implementation of this method has the same advantages as the corresponding system.

[0081] In another embodiment, the present invention provides a computer-readable storage medium having a computer program stored thereon, the computer program being executed by a processor to achieve [the desired result]. Figure 1 The system embodiments shown or other corresponding system embodiments combine the functions or implementations of these embodiments. Figure 2 The steps of the method embodiments or other corresponding method embodiments shown are not repeated here.

[0082] In another embodiment, the present invention provides a computer device including a processor, a memory, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to achieve [a specific action / combination]. Figure 1 The system embodiments shown or other corresponding system embodiments combine the functions or implementations of these embodiments. Figure 2 The steps of the method embodiments or other corresponding method embodiments shown are not repeated here.

[0083] The various embodiments described herein, or their specific features, structures, or characteristics, may be suitably combined in one or more embodiments of the invention. Furthermore, in some cases, the order of steps described in the flowcharts and / or pipeline processes may be modified where appropriate, and they need not be performed in the exact order described. Additionally, various aspects of the invention may be implemented using software, hardware, firmware, or combinations thereof, and / or other computer-implemented modules or devices that perform the described functions. Software implementations of the invention may include executable code stored in a computer-readable medium and executed by one or more processors. Computer-readable media may include computer hard disk drives, ROM, RAM, flash memory, portable computer storage media such as CD-ROM, DVD-ROM, flash drives, and / or other devices having a Universal Serial Bus (USB) interface, and / or any other suitable tangible or non-transitory computer-readable medium or computer memory on which executable code can be stored and executed by a processor. The invention may be used in conjunction with any suitable operating system.

[0084] Unless explicitly stated otherwise, the singular forms “a” and “the” used herein include the plural meaning (i.e., meaning “at least one”). It should be further understood that the terms “having,” “comprising,” and / or “including” as used in the specification indicate the presence of the described features, steps, operations, elements, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, elements, components, and / or combinations thereof. The term “and / or” as used herein includes any and all combinations of one or more of the listed related items.

[0085] The foregoing has described some preferred embodiments of the present invention. However, it should be emphasized that the present invention is not limited to these embodiments, but can be implemented in other ways within the scope of the present invention. Those skilled in the art can make various modifications and variations to the present invention based on the inventive concept and without departing from the scope of the present invention, and such modifications or variations still fall within the protection scope of the present invention.

Claims

1. An upgrade script organization system, characterized in that, The system includes: The connection module is used to connect to the database for feature development. The reading module is used to read all rows of data from the form in the Excel file created by the upgrade script. Each row of data includes at least: function module name, script type, operation type, and operation object name. The script type is one of table, field, view, index, data, trigger, and function. The operation type is add or delete. The operation object name is the table name, field name, view name, index name, table name, trigger name, or function name corresponding to the corresponding script type. The grouping module is used to group rows of data that have the same functional module name and script type. The first generation module is used to determine and extract the content required to generate the corresponding upgrade script for each group of row data according to the corresponding script type, operation type, operation object name and the database, and to concatenate the extracted content and the SQL according to the predetermined first database type format corresponding to the corresponding script type and database to form the corresponding first database type upgrade script.

2. The system according to claim 1, characterized in that, The system also includes: The configuration module is used to configure the database type, URL, driver, username, and password, as well as to create an Excel file for uploading the upgrade script.

3. The system according to claim 1, characterized in that, The system also includes: The second generation module is used to convert the field types of the database into field types suitable for the second type of database according to the field type mapping table between different types of databases, and to concatenate SQL according to the extracted content and the predetermined second database type format corresponding to the corresponding script type to form a corresponding second database type upgrade script.

4. The system according to claim 1, characterized in that, The first generation module is used for: In response to the script type being table and the operation type being add, the table field attributes, table description information, and field description information are obtained based on the table name specified by the operation object name; The system determines whether the database does not contain a table corresponding to the given table name. It then creates the corresponding table based on the table name and the obtained table field attributes, displays all obtained table fields, and concatenates the GO keyword, table description SQL, field description SQL, and finally the GO keyword to generate a table upgrade script corresponding to the database type.

5. The system according to claim 1, characterized in that, The corresponding row data also includes field attributes, and the first generation module is used for: In response to the script type being field and the operation type being add, the table field attributes and field description information are obtained based on the table name specified by the operation object name and the field name specified by the field attribute. The system concatenates SQL statements to determine if the corresponding table in the database does not contain the field with the specified field name, then concatenates SQL statements to add the specified field, adds the GO keyword, concatenates SQL statements to describe the field, and finally concatenates the GO keyword again to generate a field upgrade script corresponding to the database type.

6. The system according to claim 1, characterized in that, The first generation module is used for: In response to the script type being a view, function, or trigger and the operation type being "add", the corresponding SQL for creating the view, function, or trigger is obtained based on the view name, function name, or trigger name specified by the operation object name; The system constructs an SQL statement to determine if the database contains a view, function, or trigger corresponding to the specified view name, function name, or trigger name. It then constructs an SQL statement to delete the view, function, or trigger, constructs an SQL statement to create the view, function, or trigger, and finally constructs a GO keyword to generate an upgrade script for the view, function, or trigger corresponding to the database type.

7. The system according to claim 1, characterized in that, The first generation module is used for: In response to the script type being index and the operation type being add, the corresponding create index SQL is obtained based on the index name specified by the operation object name; The system concatenates the SQL statements to determine if the database does not have an index corresponding to the index name, concatenates the obtained index creation SQL, and adds the GO keyword to generate an index upgrade script corresponding to the database type.

8. The system according to claim 1, characterized in that, The corresponding row data also includes data filtering conditions, and the first generation module is used for: In response to the script type being data and the operation type being add, the corresponding field name and field value are retrieved based on the table name specified by the operation object name and the data filtering conditions; The system constructs an SQL query to retrieve the field name and field value, determines whether the database does not contain the table name and the data under the specified data filtering conditions, and then constructs the GO keyword to generate a data upgrade script corresponding to the database type.

9. The system according to claim 1 or 3, characterized in that, The system also includes: The naming module is used to determine the upgrade script file name based on the function module name, script type, database type and / or date, and to name the upgrade script.

10. A method for organizing upgrade scripts, characterized in that, The method includes: Database for connection functionality development; Read all rows of data from the form that creates the Excel file from the upgrade script. Each row of data includes at least: function module name, script type, operation type, and operation object name. The script type is one of table, field, view, index, data, trigger, and function. The operation type is add or delete. The operation object name is the table name, field name, view name, index name, table name, trigger name, or function name corresponding to the script type. Group rows of data that have the same functional module name and script type; For each set of row data, the content required to generate the corresponding upgrade script is determined and extracted according to the corresponding script type, operation type, operation object name and the database. The extracted content and the SQL are concatenated with the predetermined first database type format corresponding to the corresponding script type and database to form the corresponding first database type upgrade script.

Images