A method, apparatus, device, and medium for creating scripts based on database tables.

Abstract

This invention relates to a method, apparatus, device, and medium for creating scripts based on database tables. The method includes: obtaining raw table data provided by the user through a basic information template table; determining the configuration rules corresponding to the target database type based on the target database type in the raw table data; adapting the basic table creation statements and related configuration parameters corresponding to the basic information template table according to the configuration rules, so that the processed basic table creation statements and related configuration parameters are adapted to the characteristics of the target database; and generating a table creation script file adapted to the target database based on the processed basic table creation statements and related configuration parameters. This invention obtains user configurations through a basic information template table and adapts them to the characteristics of the target database, automatically generating table creation scripts, significantly reducing manual writing workload, lowering the error rate, and improving development efficiency and script quality.

Description

Technical Field

[0001] This invention relates to the field of database adaptation and migration technology. More specifically, this invention relates to a method, apparatus, device, and medium for creating scripts based on database tables. Background Technology

[0002] For a long time, CRM core systems have heavily relied on foreign databases such as Oracle and IBM DB2, posing three major risks: First, the technical "black box" vulnerability, where user data storage and transaction processing lack complete controllability, leading to data leaks and backdoor attacks; second, insufficient supply chain stability, where geopolitical conflicts could result in technology blockades and service interruptions, directly impacting the continuity of communication services; and third, weak customization capabilities, as foreign products are difficult to adapt to the localized business processes and policy requirements of domestic operators (such as tariff adjustments and data compliance regulations). Especially with the large-scale deployment of 5G and the construction of computing networks, the security vulnerabilities in scenarios involving massive data processing and high concurrency access are further amplified, pushing operators to accelerate their efforts to reduce their dependence on foreign products.

[0003] In existing technologies, when business systems adapt to multiple domestic databases, the writing and maintenance of table creation scripts suffer from many unresolved defects, as follows: (i) Development efficiency plummeted and project cycles were significantly extended. Different domestic databases have significantly different syntax specifications (such as DM tablespace management, Kingbase partitioning functions, and Gaussian distributed table definitions). When a business system is adapted to 3-5 domestic databases, a separate table creation script needs to be written for each database. Take, for example, a provincial telecom operator's CRM system migration project: The system contains 5,200 core business tables (covering modules such as customer management, order management, and billing and settlement), and needs to be compatible with three domestic databases: DM, Kingbase, and Gauss. Some tables need to support 20 partitions and partitioning by time / region. Ultimately, 18 sets of differentiated table creation scripts need to be generated. Traditionally, manual writing of these scripts would require 6 senior database engineers working continuously for 3 months. Compared to a single database scenario, the development cycle is extended by 2 times, directly causing the project to be delayed.

[0004] (ii) Maintenance costs have skyrocketed and version iteration risks have become more frequent. Throughout the business system's lifecycle, the table structure needs to be continuously iterated along with business changes (adding fields, adjusting types, modifying partitioning rules), making the maintenance of multiple version scripts a heavy burden. The iteration efficiency is low. When the field type of a certain table changes, it is necessary to manually modify the corresponding statements in 18 sets of scripts and check the syntax compatibility one by one. A single iteration takes 1-2 days. Version consistency is difficult to guarantee, and manual modifications are prone to omissions or errors, leading to inconsistencies in table structures across development, testing, and production environments, which can cause functional test failures and data migration anomalies. Knowledge transfer is costly, requiring dedicated personnel to record the differences in syntax and adaptation rules between different databases, and new employees need 1-2 months of training to get started, increasing labor costs.

[0005] (iii) Manual coding has a high error rate, which affects business stability. Writing table creation scripts involves complex steps such as database syntax, field type matching, and table partitioning rules, which are prone to errors when done manually. Syntax errors, differences in keywords, function names, and statement structures between different databases (e.g., Oracle's NUMBER corresponds to DECIMAL in DM and supports NUMERIC in Gauss), and manual conversion can easily result in spelling or type mismatches. Incorrect configuration, such as incorrect number of tables, partition fields, or tablespace rules, can lead to uneven data sharding, a sharp drop in query performance, or even data storage overflow. A logical error and an improper selection of the sharding key in the distributed table led to a surge in cross-node queries, increasing the system response time from milliseconds to seconds.

[0006] Statistics show that the error rate of manually writing multiple versions of table creation scripts is 15%-20%, of which 30% of the errors will cause online failures, requiring a lot of resources to be invested in emergency repairs.

[0007] (iv) Poor adaptability and inability to meet personalized needs Different projects have significantly different business scenarios, data volumes, and deployment architectures, resulting in varying requirements for table creation scripts. Small projects (such as local systems of city-level operators) do not require table or partitioning, while large projects (such as nationwide CRM systems) need to support thousands of tables and two-level partitioning by region and time. Some projects require tablespaces to be used cyclically to save storage, while others need to allocate tablespaces sequentially according to business modules for easier management.

[0008] Traditional manual scripting requires custom scripts for each project, which cannot quickly adapt to individual needs, and the modification process is prone to introducing new errors, reducing the flexibility and efficiency of project implementation. Summary of the Invention

[0009] The technical problem to be solved by the present invention is to provide a method, apparatus, device and medium for creating scripts based on database tables, in order to solve at least one of the above-mentioned technical problems.

[0010] In a first aspect, the technical solution of the present invention to solve the above-mentioned technical problems is as follows: a method for creating a script based on a database table, the method comprising: S10: Obtain the original table data provided by the user through the basic information template table; S20, Based on the target database type in the original table data, determine the configuration rules corresponding to the target database type; S30, according to the configuration rules, adapt the basic table creation statements and related configuration parameters corresponding to the basic information template table so that the processed basic table creation statements and related configuration parameters are compatible with the characteristics of the target database. S40 generates a table creation script file adapted to the target database based on the processed basic table creation statements and related configuration parameters.

[0011] The beneficial effects of this invention are as follows: It obtains the original table data provided by the user through a basic information template table, determines the corresponding configuration rules based on the target database type in the original table data, and adapts the basic table creation statements and related configuration parameters according to the configuration rules. Finally, it generates a table creation script file adapted to the target database, achieving efficient and accurate database table creation script generation. This method avoids the tedious process of manually writing and modifying table creation scripts one by one, significantly reducing workload and development cycle, lowering the error rate caused by manual operation, and improving the quality and reliability of table creation scripts. This enhances the efficiency and stability of database development and migration, better meeting the adaptation needs of multiple domestic databases and the personalized needs of different projects.

[0012] Based on the above technical solution, the present invention can be further improved as follows.

[0013] Furthermore, the aforementioned configuration parameters include the target database type, table type, and character set encoding; According to the configuration rules, the basic table creation statements and related configuration parameters corresponding to the basic information template table are adapted to ensure that the processed basic table creation statements and related configuration parameters are compatible with the characteristics of the target database, including: The general SQL syntax in basic table creation statements is converted to adapt the converted syntax to the specific syntax of the target database. Based on the data type mapping rules of the target database, convert the field types corresponding to the source database types to the field types corresponding to the target database types; Convert the character set encoding configured in the template to an encoding format supported by the target database, or use the default recommended encoding of the target database; Based on the requirements of the target database, automatically add necessary special configuration statements.

[0014] Furthermore, the aforementioned configuration parameters also include table partitioning configuration parameters, partition configuration parameters, and tablespace configuration parameters. The method further includes: Based on the user's configuration requirements and table type, the table partitioning configuration parameters, partition configuration parameters, and tablespace configuration parameters are adapted and processed to generate the corresponding SQL statements.

[0015] Furthermore, based on the processed basic table creation statements and related configuration parameters, a table creation script file adapted to the target database is generated, including: Based on the processed basic table creation statements and related configuration parameters, an initial script is generated, and the initial script is validated. If the initial script passes the script validation, a table creation script file adapted to the target database will be generated based on the processed basic table creation statements and related configuration parameters. If the initial script fails the script validation, the original table data in the basic information template table will be modified to generate a table creation script file adapted to the target database based on the modified original table data.

[0016] Furthermore, the above-mentioned script validation of the initial script includes: Perform syntax and rule checks on the initial script.

[0017] Furthermore, the aforementioned table creation script file includes: Script header comments, tablespace creation statements, table creation statements, index creation statements, and script tail comments.

[0018] Furthermore, the above methods also include: Output the generated table creation script file to the specified path.

[0019] Secondly, in order to solve the above-mentioned technical problems, the present invention also provides an apparatus for creating a database table-based script, the apparatus comprising: The acquisition module retrieves the original table data provided by the user through the basic information template table. The configuration rule determination module is used to determine the configuration rules corresponding to the target database type based on the target database type in the original table data. The adaptation module is used to adapt the basic table creation statements and related configuration parameters corresponding to the basic information template table according to the configuration rules, so that the processed basic table creation statements and related configuration parameters are compatible with the characteristics of the target database. The script generation module is used to generate table creation script files adapted to the target database based on the processed basic table creation statements and related configuration parameters.

[0020] Thirdly, in order to solve the above-mentioned technical problems, the present invention also provides an electronic device, which includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the method of creating a database table-based script of the present application.

[0021] Fourthly, in order to solve the above-mentioned technical problems, the present invention also provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the method of creating a database table-based script of the present application.

[0022] Additional aspects and advantages of this application will be set forth in part in the description which follows, and will become apparent from the description or may be learned by practice of this application. Attached Figure Description

[0023] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments of the present invention will be briefly introduced below.

[0024] Figure 1 A flowchart illustrating a method for creating a script based on a database table, provided as an embodiment of the present invention; Figure 2 A flowchart illustrating another method for creating a script based on a database table, provided in one embodiment of the present invention; Figure 3 A schematic diagram of a device for creating a database table based on a script, provided in one embodiment of the present invention; Figure 4 This is a schematic diagram of the structure of an electronic device provided in one embodiment of the present invention. Detailed Implementation

[0025] The principles and features of the present invention are described below. The examples given are only for explaining the present invention and are not intended to limit the scope of the present invention.

[0026] The technical solution of the present invention and how the technical solution of the present invention solves the above-mentioned technical problems are described in detail below with specific embodiments. These specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments. The embodiments of the present invention will now be described with reference to the accompanying drawings.

[0027] The solution provided in this invention can be applied to any application scenario that requires generating table creation script files. The solution provided in this invention can be executed by any electronic device, such as a user's terminal device, including at least one of the following: smartphone, tablet computer, laptop computer, desktop computer, smart speaker, smartwatch, smart TV, or smart in-vehicle device.

[0028] This invention provides a possible implementation, such as... Figure 1The diagram shows a flowchart of a method for creating a script based on a database table. This method can be executed by any electronic device, such as a terminal device, or by both a terminal device and a server. For ease of description, the method provided in this embodiment will be described below using a terminal device as the execution subject. Figure 1 The flowchart shown indicates that the method may include the following steps: S10: Obtain the original table data provided by the user through the basic information template table; S20, Based on the target database type in the original table data, determine the configuration rules corresponding to the target database type; S30, according to the configuration rules, adapt the basic table creation statements and related configuration parameters corresponding to the basic information template table so that the processed basic table creation statements and related configuration parameters are compatible with the characteristics of the target database. S40 generates a table creation script file adapted to the target database based on the processed basic table creation statements and related configuration parameters.

[0029] The method of this invention obtains the original table data provided by the user through a basic information template table, then determines the corresponding configuration rules based on the target database type in the original table data, and adapts the basic table creation statements and related configuration parameters according to the configuration rules. Finally, it generates a table creation script file adapted to the target database, achieving efficient and accurate database table creation script generation. This method avoids the tedious process of manually writing and modifying table creation scripts one by one, significantly reducing workload and development cycle, lowering the error rate caused by manual operation, and improving the quality and reliability of table creation scripts. This enhances the efficiency and stability of database development and migration, better meeting the adaptation needs of multiple domestic databases and the personalized needs of different projects.

[0030] The following specific embodiments further illustrate the solution of the present invention. In these embodiments, addressing the shortcomings of the prior art, the core objective of the present invention is to provide an efficient, reliable, and flexible method for generating domestic database table creation scripts. The specific objectives are as follows: (a) Significantly reduce the workload of writing table creation scripts To address the needs of adapting to multiple domestic databases and the personalized requirements of various projects (table partitioning, table space configuration), a template-based configuration and automated generation mechanism reduces manual coding workload by more than 80%. Users only need to provide basic table structure information (field names, types, constraints) and personalized configuration parameters (database type, number of tables, partitioning rules) to automatically generate a complete table creation script adapted to the target database. This eliminates the need for manual coding or modification, significantly shortening the project cycle.

[0031] (ii) Reduce the error rate of manual coding By employing standardized template design and automated syntax adaptation logic, the system avoids syntax errors, type mismatches, and configuration parameter errors inherent in manual scripting. It incorporates built-in syntax rule libraries for different domestic databases, data type mapping tables, and table partitioning validation logic. Syntax and parameter validation are automatically performed during script generation, reducing the error rate to below 0.5% and ensuring the accuracy of database table structures and the stability of the business system.

[0032] (III) Improve script consistency and standardization Establish a unified standard for generating table creation scripts to ensure consistent script style and parameter configuration across different developers, projects, and environments (development, testing, and production). This avoids issues such as formatting chaos, inconsistent field naming, and missing constraints caused by manual scripting, reducing the difficulty of subsequent system maintenance and version iteration, and improving team collaboration efficiency.

[0033] (iv) Supports rapid adaptation to multiple types of domestic databases Built-in feature adaptation logic for mainstream domestic databases such as DM8, KingbaseES, GaussDB, SequoiaDB, and TDSQL. Users do not need to worry about syntax differences and feature configurations. They only need to select the type of target database, and the system will automatically complete the syntax conversion and feature adaptation (tablespace configuration, partition syntax, distributed sharding rules), realizing "one configuration for multiple databases" and reducing the technical threshold for system migration.

[0034] (v) Meeting the personalized needs of different projects It offers flexible configuration options, supporting customization of table partitioning (number of tables, suffix rules, routing algorithms), partitioning (fields, types, rules), tablespaces (usage strategies, capacity configuration), table types (HASH, DUPLICATE, SINGLETABLE), and other personalized needs. Users can flexibly configure parameters according to project size, business scenarios, and deployment architecture. The system automatically generates table creation scripts that meet the requirements, adapting to various scenarios from small local systems to large distributed systems.

[0035] Based on this, combined Figure 2 This embodiment provides a method for creating scripts based on database tables, which may include the following steps: S10: Obtain the original table data provided by the user through the basic information template table; The basic information template table refers to the pre-designed standardized Excel template "CreateTableTemplate.xlsx". As an example, the basic information template table can be found in Table 1: Table 1 Users can fill in or select information based on the above basic information template to obtain the original table data. After the user completes the form, the completed template is placed in the src / main / resources / template / directory corresponding to the utility class for subsequent retrieval.

[0036] The method also includes: Parse the original table data to obtain the basic table creation statements and related configuration parameters corresponding to the basic information template table.

[0037] Specifically, the POI utility class can be used to read Excel template configuration data (including basic table creation statements and related configuration parameters) and perform data cleaning and format validation: Basic table creation statement validation: Checks whether the SQL statement contains valid field definitions and constraints, and eliminates syntax errors; Validation of relevant configuration parameters: Verify the validity of parameters such as the number of table partitions (positive integer), partition field (must exist in the basic table creation statement), and tablespace name; Data format standardization: Convert configuration parameters into a system-recognizable format (e.g., standardize table suffix rules to the “_XXX” format, and convert partition rules into database-recognizable syntax expressions).

[0038] After parsing, the basic table creation statements and related configuration parameters are stored in the memory data model to support subsequent processing.

[0039] S20, Based on the target database type in the original table data, determine the configuration rules corresponding to the target database type; The target database type refers to the type of the target database. In this solution, the target database type can be obtained by parsing the original table data. The target database type can also be DM, Kingbase, Huawei Gauss, etc.

[0040] Configuration rules refer to a series of rules and mapping relationships established based on the characteristics of the target database. These rules guide the system on how to adapt the user-provided basic table creation statements and related configuration parameters to the syntax and feature requirements of the target database. These rules cover multiple aspects, including syntax conversion, data type mapping, tablespace configuration, partitioning configuration, sharding configuration, and special configurations. They are the core basis for achieving automated database script generation and multi-database adaptation.

[0041] As an example, the configuration rules for different database types can be found in Table 2.

[0042] Table 2 S30, according to the configuration rules, adapt the basic table creation statements and related configuration parameters corresponding to the basic information template table so that the processed basic table creation statements and related configuration parameters are compatible with the characteristics of the target database. The aforementioned configuration parameters include the target database type, table type, and character set encoding; One possible implementation of S30 above is as follows: S301 converts the general SQL syntax in basic table creation statements to adapt the converted syntax to the target database's specific syntax; (e.g., converting MySQL's AUTO_INCREMENT to DM's IDENTITY(1,1) and Gauss's SERIAL). S302, according to the data type mapping rules of the target database, convert the field type corresponding to the source database type to the field type corresponding to the target database type; where the source database type refers to a type that is different from the target database type. For example, (e.g., convert VARCHAR(50) to VARCHAR2(50) of Renmin University of China and CHAR(50) of Sequoia Capital).

[0043] S303 converts the character set encoding configured in the template to an encoding format supported by the target database, or uses the default recommended encoding of the target database; S304 automatically adds necessary special configuration statements based on the requirements of the target database. For example, it includes the tablespace extension configuration `STORAGE(ON TS_CRM, AUTOEXTEND ON NEXT 100M MAXSIZEUNLIMITED)` for DM and the distributed sharding configuration `DISTRIBUTE BY HASH(user_id)` for Gaussian. Based on this, if the relevant configuration parameters also include table partitioning configuration parameters, partition configuration parameters, and tablespace configuration parameters, the method may further include: Based on the user's configuration requirements and table type, the table partitioning configuration parameters, partition configuration parameters, and tablespace configuration parameters are adapted and processed to generate the corresponding SQL statements.

[0044] In this solution, a corresponding number of table partitioning scripts can be generated based on the table partitioning configuration parameters. The table partitioning suffix is ​​generated according to the configuration rules (e.g., _001 to _010). If the table type is HASH, the table partitioning routing algorithm configuration (e.g., hash partitioning rules based on primary keys) is automatically added. If it is DUPLICATE type, a full copy script of the configuration table is generated. Based on the partition configuration parameters, generate partition statements (such as range partition PARTITION P202501 VALUES LESSTHAN ('2025-02-01') and list partition PARTITION P_GD VALUES IN ('Guangdong')); support multi-level partitioning (such as partitioning by region first and then by time), and automatically generate the corresponding nested partitioning syntax; Based on the tablespace configuration parameters, generate the tablespace specification statement (e.g., TABLESPACE TS_CRM); if it is a cyclical usage strategy, add a tablespace capacity monitoring statement (e.g., ALTER TABLESPACE TS_CRM ADD DATAFILE 'TS_CRM_02.DBF' SIZE 500M); if it is a sequential usage strategy, allocate tablespaces according to the order of business modules (e.g., TS_CRM_CUSTOMER, TS_CRM_ORDER).

[0045] Specifically, the above adaptation processing of table partitioning configuration parameters may include: Generate SQL statements for table partitioning based on table type (such as HASH, DUPLICATE, SINGLETABLE) and partitioning configuration parameters (whether to partition, number of partitions, partitioning suffix rules).

[0046] Specifically, if the table type is HASH, a sharding script is generated based on the number of shards and the sharding suffix rule, and a hash sharding rule based on the sharding key is added. If the table type is DUPLICATE, a script for full table replication is generated, ensuring that each shard has the same data structure. If the table type is SINGLETABLE, a single-table SQL statement is generated, and no sharding operation is performed.

[0047] Adapting partition configuration parameters can include: Generate SQL statements for partitioning based on partition configuration parameters (whether to partition, partition fields, partition type, partition rules).

[0048] This solution supports multiple partition types, including range partitioning, list partitioning, and hash partitioning.

[0049] For example, if the partition field is create_time, the partition type is range partitioning, and the partitioning rule is year-month partitioning (YYYYMM), then the corresponding partitioning statement will be generated.

[0050] Adapting tablespace configuration parameters can include: Generate SQL statements for the tablespace based on the tablespace configuration parameters (tablespace name, usage strategy, capacity threshold).

[0051] Specifically, if the tablespace usage strategy is circular, add configuration statements for tablespace capacity monitoring and automatic expansion. If the tablespace usage strategy is sequential, allocate tablespaces according to the order of business modules.

[0052] Through the above adaptation process, the generated SQL statements can meet the table partitioning, table space requirements of different table types, ensuring that the generated table creation scripts can be correctly executed in the target database and meet business requirements.

[0053] S40 generates a table creation script file adapted to the target database based on the processed basic table creation statements and related configuration parameters.

[0054] Alternatively, one implementation of S40 above is as follows: S401, based on the processed basic table creation statements and related configuration parameters, generates an initial script and performs script verification on the initial script; S402, If the initial script passes the script validation, then a table creation script file adapted to the target database is generated based on the processed basic table creation statements and related configuration parameters. S403 If the initial script fails the script validation, the original table data in the basic information template table is modified to generate a table creation script file adapted to the target database based on the modified original table data.

[0055] Optionally, the above-mentioned script validation of the initial script includes: performing syntax validation and rule validation on the initial script.

[0056] Specifically, the generated script can be double-validated using both an SQL syntax validation engine and a business rule validation engine. Syntax validation: Checks whether the initial script conforms to the syntax specifications of the target database, eliminating problems such as keyword errors, statement structure errors, and mismatched brackets; Rule validation: Verify the rationality of the table partitioning rules in the initial script (such as whether the partition field is an index field, whether the number of tables matches the data volume), and the validity of the tablespace configuration (such as whether the tablespace name exists, and whether the capacity threshold is reasonable). Furthermore, when the verification fails, detailed error messages can be output (such as "Tablespace TS_CRM does not exist, please check the configuration" or "Partition field create_time is not defined in the table structure"), and the user can regenerate the table after modifying the original table data.

[0057] Optionally, the above table creation script file includes: Script header comments, tablespace creation statements, table creation statements, index creation statements, and script tail comments.

[0058] After successful verification, the system will organize the processed SQL statements in the format of "table name sorting + comment description" to generate a complete table creation script file (e.g., create_table_dm8_20251203.sql), which contains the following content: The script header comments contain information such as generation time, target database type, and a summary of configuration parameters. Tablespace creation statement (if a tablespace is configured but does not exist); The table creation statements are arranged in order of table name and include complete field definitions, constraints, table / partition configurations, tablespace specifications, etc. The index creation statement automatically extracts the index definition from the basic table creation statement and generates the corresponding index creation script; The script includes a postscript containing the generation status (success / failure) and a summary of the validation results.

[0059] Optionally, the method further includes: Output the generated table creation script file to the specified path.

[0060] The generated table creation script file can be output to the target / sql / directory, and users can specify the output path through configuration.

[0061] Based on the above, the core components involved in this solution can be designed as follows: Template parsing component: Responsible for reading Excel templates, parsing configuration parameters, and validating data validity. It implements reading and writing of Excel files based on POI and supports .xls and .xlsx formats. Database Feature Library Component: Stores the syntax rules, data type mapping tables, and special configuration requirements of mainstream domestic databases, and supports dynamic expansion (when adding a new database type, only the configuration file needs to be added, without modifying the core code); Syntax conversion component: Based on the database feature library, it realizes the conversion of general SQL syntax to the target database's proprietary syntax, and supports automatic adaptation of field types, constraints, and table partitioning syntax; Personalized processing component: encapsulates the processing logic of table partitioning, partitioning, and tablespaces, and dynamically generates corresponding SQL statements based on configuration parameters; Validation components: include a syntax validation engine and a rule validation engine to ensure the accuracy of the generated scripts; Script output component: Responsible for organizing the generated SQL statements into a standard format and outputting them as .sql files. It supports custom output paths and filename formats.

[0062] The key technical points of this invention are as follows: (I) Adaptive adaptation mechanism for multiple database features A standardized database feature description model is established, abstracting the syntax rules, data types, and configuration requirements of different domestic databases into a unified configuration template. Feature adaptation is achieved through "configuration-driven" processing. When adding a new database type, there is no need to modify the core code; simply add the corresponding configuration file (such as syntax rules.xml, type mapping.csv) to the feature library to quickly support script generation for that database, demonstrating strong scalability.

[0063] (ii) Template-based configuration lowers the barrier to entry for users. The design incorporates a "zero-code" Excel template configuration method, eliminating the need for users to master complex database syntax or programming knowledge. Users simply fill in the basic table structure and configuration parameters according to the template to generate the script. The template includes built-in instructions and examples, reducing the learning curve. Ordinary business personnel can operate independently after just one hour of training, significantly improving the tool's usability.

[0064] (III) Flexible customization and automated generation of table partitions It supports various table partitioning types such as HASH, range, and list, and provides flexible configuration of parameters such as the number of tables, suffix rules, partitioning fields, and partitioning granularity. The system automatically generates table partitioning scripts and partitioning statements based on the configuration, eliminating the need for manual writing of complex sharding logic. It also has built-in table partitioning rationality verification rules (such as matching the number of tables with the data volume and ensuring that partitioning fields are frequently queried fields) to ensure the scientific nature of table partitioning design.

[0065] (iv) Non-intrusive design of static utility classes This invention uses a static utility class to implement core functionality, eliminating the need for complex frameworks such as Spring Boot and MyBatis, and allowing it to run independently. Users simply need to place the template file in a specified directory and call the static method of the utility class (such as TableScriptGenerator.generate()) to generate the script. It supports various use cases, including local execution, server deployment, and CI / CD pipeline integration, and can be seamlessly integrated into existing project development workflows.

[0066] (v) Full-process automation and closed-loop verification It automates the entire process from template reading, data parsing, feature adaptation, and personalization to script output, eliminating the need for manual intervention. Simultaneously, it constructs a closed-loop mechanism of "parsing-processing-validation-generation," with each step accompanied by corresponding validation logic to ensure that the generated script is syntactically correct, properly configured, and meets business requirements, thus eliminating human error at its source.

[0067] This invention, through its automated, standardized, and flexible design, solves the core pain points of writing table creation scripts for many domestic databases, achieving significant technical effects and business value: (a) Development efficiency has been greatly improved Workload reduced by over 80%: Users only need to fill in the template form and call the utility class method to automatically generate multi-version table creation scripts, without the need for manual writing or modification. Taking the adaptation of 5200 tables to 3 domestic databases and 10 partitioned tables as an example, the traditional manual method requires 6 people / month, while this invention only requires 1 person / 0.5 months to complete, reducing workload by 91.7%. Project cycle shortened by 60%: The table creation script writing cycle for the database migration project has been shortened from 3 months to less than 1 month, leaving sufficient time for subsequent application adaptation, data migration, testing and deployment, and accelerating project implementation; Improved iteration efficiency: When business changes lead to adjustments in the table structure, only the basic table creation statements or configuration parameters in the template table need to be modified, and the utility class method can be re-executed to generate a new script. The time taken for a single iteration is reduced from 1-2 days to less than 10 minutes.

[0068] (ii) The error rate has been significantly reduced. The script error rate was reduced from 15%-20% in traditional manual scripting to below 0.5%, and the remaining errors were mostly template input errors (such as misspelled field names), which could be quickly corrected through template validation. No online failures occurred due to syntax errors, type matching errors, or table partitioning configuration errors, significantly improving the stability of the business system and reducing emergency repair costs.

[0069] (iii) Maintenance costs have been significantly reduced. Version maintenance costs reduced by 70%: Synchronous maintenance of multiple version scripts does not require manual modification of each one. Only one template table needs to be maintained to generate scripts for all versions, avoiding version inconsistency issues; Reduced knowledge transfer costs: New employees do not need to learn the syntax differences and script adaptation rules of different databases. They only need to master the template filling method and can independently complete script generation in 1 hour, which reduces team training costs. Improved problem localization efficiency: Standardized script formats and configuration rules have reduced the time for locating table structure-related problems from several hours to several minutes, thus improving system maintenance efficiency.

[0070] As a complete embodiment, the process of this solution specifically includes: 1. Fill in the form. The basic information template form can be "CreateTableTemplate.xlsx"; For basic table creation statements: if it involves table partitioning, the partitioning suffix needs to be set to the variable {save_code}. No other changes are required; you can directly use the table creation statement exported by MySQL. For table types: HASH is generally used for instance data sharding; DUPLICATE is used for configuration tables; SINGLETABLE is used for single-point tables with sharding, such as guardian tables starting with ti.

[0071] 2. Place the table in the resource directory corresponding to the utility class; 3. Execute static utility class methods; 4. Once the execution is complete, an SQL file will be output, which contains all the creation scripts required for the tables.

[0072] Based on and Figure 1 Using the same principle as the method shown, this embodiment of the invention also provides a device 20 for creating scripts based on database tables, such as... Figure 3 As shown, the device 20 for creating scripts based on database tables may include an acquisition module 210, a configuration rule determination module 220, an adaptation module 230, and a script generation module 240, wherein: Module 210 retrieves the original table data provided by the user through the basic information template table; The configuration rule determination module 220 is used to determine the configuration rules corresponding to the target database type based on the target database type in the original table data; The adaptation module 230 is used to adapt the basic table creation statements and related configuration parameters corresponding to the basic information template table according to the configuration rules, so that the processed basic table creation statements and related configuration parameters are adapted to the characteristics of the target database. The script generation module 240 is used to generate a table creation script file adapted to the target database based on the processed basic table creation statements and related configuration parameters.

[0073] Optionally, the above-mentioned configuration parameters include the target database type, table type, and character set encoding; The aforementioned adaptation module 230, when adapting the basic table creation statements and related configuration parameters corresponding to the basic information template table according to the configuration rules, so that the processed basic table creation statements and related configuration parameters are compatible with the characteristics of the target database, is specifically used for: The general SQL syntax in basic table creation statements is converted to adapt the converted syntax to the specific syntax of the target database. Based on the data type mapping rules of the target database, convert the field types corresponding to the source database types to the field types corresponding to the target database types; Convert the character set encoding configured in the template to an encoding format supported by the target database, or use the default recommended encoding of the target database; Based on the requirements of the target database, automatically add necessary special configuration statements.

[0074] Optionally, the aforementioned configuration parameters also include table partitioning configuration parameters, partition configuration parameters, and tablespace configuration parameters. The device further includes: The personalized configuration module is used to adapt the table partitioning configuration parameters, partition configuration parameters, and tablespace configuration parameters according to the user's configuration requirements and table type, and generate corresponding SQL statements.

[0075] Optionally, when the script generation module 240 generates a table creation script file adapted to the target database based on the processed basic table creation statements and related configuration parameters, it is specifically used for: Based on the processed basic table creation statements and related configuration parameters, an initial script is generated, and the initial script is validated. If the initial script passes the script validation, a table creation script file adapted to the target database will be generated based on the processed basic table creation statements and related configuration parameters. If the initial script fails the script validation, the original table data in the basic information template table will be modified to generate a table creation script file adapted to the target database based on the modified original table data.

[0076] Optionally, when performing script verification on the initial script, the script generation module 240 is specifically used for: Perform syntax and rule checks on the initial script.

[0077] Optionally, the above table creation script file includes: Script header comments, tablespace creation statements, table creation statements, index creation statements, and script tail comments.

[0078] Optionally, the above-mentioned device further includes: The output module is used to output the generated table creation script file to a specified path.

[0079] The apparatus based on database table creation script in this embodiment of the invention can execute the method based on database table creation script provided in this embodiment of the invention. The implementation principle is similar. The actions performed by each module and unit in the apparatus based on database table creation script in each embodiment of the invention correspond to the steps in the method based on database table creation script in each embodiment of the invention. For detailed functional descriptions of each module of the apparatus based on database table creation script, please refer to the descriptions in the corresponding methods based on database table creation script shown above, which will not be repeated here.

[0080] The aforementioned device for creating a script based on a database table can be a computer program (including program code) running on a computer device, such as an application software; the device can be used to execute the corresponding steps in the method provided in the embodiments of the present invention.

[0081] In some embodiments, the apparatus for creating a database table based on a script provided in this invention can be implemented using a combination of hardware and software. As an example, the apparatus for creating a database table based on a script provided in this invention can be a processor in the form of a hardware decoding processor, which is programmed to execute the method for creating a database table based on a script provided in this invention. For example, the processor in the form of a hardware decoding processor can be one or more application-specific integrated circuits (ASICs), DSPs, programmable logic devices (PLDs), complex programmable logic devices (CPLDs), field-programmable gate arrays (FPGAs), or other electronic components.

[0082] In other embodiments, the apparatus for creating database tables based on scripts provided in this invention can be implemented in software. Figure 3 An apparatus for creating a database table-based script stored in memory is shown. The apparatus may be software in the form of a program or plug-in, and includes a series of modules, including an acquisition module 210, a configuration rule determination module 220, an adaptation module 230, and a script generation module 240, for implementing the method for creating a database table-based script provided in the embodiments of the present invention.

[0083] The modules described in the embodiments of the present invention can be implemented in software or hardware. The names of the modules are not, in some cases, limiting the scope of the module itself.

[0084] Based on the same principles as the methods shown in the embodiments of the present invention, the embodiments of the present invention also provide an electronic device, which may include, but is not limited to: a processor and a memory; the memory for storing computer programs; and the processor for executing the methods shown in any embodiment of the present invention by invoking the computer programs.

[0085] In one alternative embodiment, an electronic device is provided, such as Figure 4 As shown, Figure 4The illustrated electronic device 4000 includes a processor 4001 and a memory 4003. The processor 4001 and the memory 4003 are connected, for example, via a bus 4002. Optionally, the electronic device 4000 may further include a transceiver 4004, which can be used for data interaction between the electronic device and other electronic devices, such as sending and / or receiving data. It should be noted that in practical applications, the transceiver 4004 is not limited to one type, and the structure of the electronic device 4000 does not constitute a limitation on the embodiments of the present invention.

[0086] Processor 4001 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 can implement or execute the various exemplary logic blocks, modules, and circuits described in conjunction with the disclosure of this invention. Processor 4001 may also be a combination that implements computational functions, such as including one or more microprocessor combinations, a combination of a DSP and a microprocessor, etc.

[0087] Bus 4002 may include a pathway for transmitting information between the aforementioned components. Bus 4002 may be a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus, etc. Bus 4002 can be divided into address bus, data bus, control bus, etc. For ease of representation, Figure 4 The bus is represented by a single thick line, but this does not mean that there is only one bus or one type of bus.

[0088] The memory 4003 may be ROM (Read Only Memory) or other types of static storage devices capable of storing static information and instructions, RAM (Random Access Memory) or other types of dynamic storage devices capable of storing information and instructions, or EEPROM (Electrically Erasable Programmable Read Only Memory), CD-ROM (Compact Disc Read Only Memory) or other optical disc storage, optical disc storage (including compressed optical discs, laser discs, optical discs, digital universal optical discs, Blu-ray discs, etc.), magnetic disk storage media or other magnetic storage devices, or any other medium capable of carrying or storing desired program code in the form of instructions or data structures and accessible by a computer, but not limited thereto.

[0089] The memory 4003 stores application code (computer program) for executing the present invention, and its execution is controlled by the processor 4001. The processor 4001 executes the application code stored in the memory 4003 to implement the content shown in the foregoing method embodiments.

[0090] Among these, electronic devices can also be terminal devices. Figure 4 The electronic device shown is merely an example and should not be construed as limiting the functionality and scope of use of the embodiments of the present invention.

[0091] This invention provides a computer-readable storage medium storing a computer program that, when run on a computer, enables the computer to execute the corresponding content in the aforementioned method embodiments.

[0092] According to another aspect of the present invention, a computer program product or computer program is also provided, comprising computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the computer device to perform the methods provided in the various embodiments described above.

[0093] Computer program code for performing the operations of this invention can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, and C++, and conventional procedural programming languages ​​such as C or similar languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a local area network (LAN) or a wide area network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0094] It should be understood that the flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of methods and computer program products according to various embodiments of the present invention. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing the specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, may be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0095] The computer-readable storage medium provided in this invention can be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of a computer-readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination thereof. In this invention, a computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device.

[0096] The aforementioned computer-readable storage medium carries one or more programs, which, when executed by the electronic device, cause the electronic device to perform the method shown in the above embodiments.

[0097] The above description is merely a preferred embodiment of the present invention and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of disclosure in this invention is not limited to technical solutions formed by specific combinations of the above-described technical features, but should also cover other technical solutions formed by arbitrary combinations of the above-described technical features or their equivalents without departing from the above-disclosed concept. For example, technical solutions formed by substituting the above features with (but not limited to) technical features with similar functions disclosed in this invention.

Claims

1. A method for creating scripts based on database tables, characterized in that, include: Obtain the original table data provided by the user through the basic information template table; Based on the target database type in the original table data, determine the configuration rules corresponding to the target database type; According to the configuration rules, the basic table creation statements and related configuration parameters corresponding to the basic information template table are adapted to make the processed basic table creation statements and related configuration parameters compatible with the characteristics of the target database. Based on the processed basic table creation statements and related configuration parameters, a table creation script file adapted to the target database is generated.

2. The method according to claim 1, characterized in that, The relevant configuration parameters include the target database type, table type, and character set encoding; The step of adapting the basic table creation statements and related configuration parameters corresponding to the basic information template table according to the configuration rules, so that the processed basic table creation statements and related configuration parameters are adapted to the characteristics of the target database, includes: The general SQL syntax in the basic table creation statements is converted to adapt the converted syntax to the specific syntax of the target database. Based on the data type mapping rules of the target database, convert the field types corresponding to the source database types to the field types corresponding to the target database types; Convert the character set encoding configured in the template to an encoding format supported by the target database, or use the default recommended encoding of the target database; Based on the requirements of the target database, automatically add the necessary special configuration statements.

3. The method according to claim 1, characterized in that, The relevant configuration parameters also include table partitioning configuration parameters, partition configuration parameters, and tablespace configuration parameters; the method further includes: Based on the user's configuration requirements and the table type, the table partitioning configuration parameters, partition configuration parameters, and tablespace configuration parameters are adapted to generate corresponding SQL statements.

4. The method according to any one of claims 1 to 3, characterized in that, The step of generating a table creation script file adapted to the target database based on the processed basic table creation statements and related configuration parameters includes: Based on the processed basic table creation statements and related configuration parameters, an initial script is generated, and the initial script is validated. If the initial script passes the script verification, a table creation script file adapted to the target database is generated based on the processed basic table creation statement and related configuration parameters. If the initial script fails the script validation, the original table data in the basic information template table is modified to generate a table creation script file adapted to the target database based on the modified original table data.

5. The method according to claim 4, characterized in that, The script validation of the initial script includes: The initial script is then subjected to syntax and rule validation.

6. The method according to any one of claims 1 to 3, characterized in that, The table creation script file includes: Script header comments, tablespace creation statements, table creation statements, index creation statements, and script tail comments.

7. The method according to any one of claims 1 to 3, characterized in that, The method further includes: Output the generated table creation script file to the specified path.

8. An apparatus for creating a database table based on a script, characterized in that, include: The acquisition module retrieves the original table data provided by the user through the basic information template table. The configuration rule determination module is used to determine the configuration rule corresponding to the target database type based on the target database type in the original table data; The adaptation module is used to adapt the basic table creation statements and related configuration parameters corresponding to the basic information template table according to the configuration rules, so that the processed basic table creation statements and related configuration parameters are adapted to the characteristics of the target database. The script generation module is used to generate a table creation script file adapted to the target database based on the processed basic table creation statements and related configuration parameters.

9. An electronic device, characterized in that, It includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the computer program, implements the method of any one of claims 1-7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the method of any one of claims 1-7.

Images