Database version control method, system and related device based on table classification management

Abstract

The application provides a database version control method, system, device, medium and program product based on table classification management, the method comprises the following steps: obtaining the structure information of a target database, automatically identifying and classifying the data tables of the target database, determining the update strategy of different types of data tables according to the classification result; generating a baseline version file of the database according to the classification result, and recording the version identification and release information of the baseline version file; comparing the baseline version files of different version identifications, identifying the change type of the database object and generating a difference analysis result; generating corresponding update scripts for different types of data tables according to the difference analysis result and the update strategy; verifying and executing the update scripts, and updating the version identification and release information according to the execution result. By introducing table classification management and differential update mechanism in the database version management process, the application realizes the automatic update, intelligent verification and unified version control of the database version.

Description

Technical Field

[0001] This application relates to the field of database management technology, and in particular to a database version control method, system and related equipment based on table classification management. Background Technology

[0002] As enterprise information systems expand in scale and business complexity increases, database environments are becoming increasingly diversified and heterogeneous. Different types of databases, such as MySQL and ClickHouse, are often deployed together in the same system architecture to simultaneously meet the needs of transaction processing and analytical computing.

[0003] Existing database version management methods typically employ uniform script upgrades or manual maintenance, updating the database by manually writing SQL scripts or comparing structural differences. However, these methods lack a classification management mechanism based on the business attributes of database tables, failing to distinguish between different types such as feature database tables, user data tables, private management tables, and tables without data storage. Furthermore, MySQL and databases like ClickHouse differ significantly in their structural definitions, data migration, and constraint handling, and existing solutions lack a unified adaptation mechanism. In addition, traditional methods employ a single data retention strategy, unable to flexibly handle data retention or deletion based on table attributes, and lack a robust baseline version and version history tracking system, leading to difficulties in cross-environment version synchronization, error-prone upgrade processes, and low efficiency.

[0004] Therefore, existing technologies still have many shortcomings in dealing with version control in complex heterogeneous database environments, and there is an urgent need for a new database version control solution that can realize table classification and identification, differentiated updates, and unified management of heterogeneous databases. Summary of the Invention

[0005] To address the shortcomings of existing technologies, this application provides a database version control method, system, and related equipment based on table classification management, which at least solves the problems of existing database version management lacking classification management and differentiated update mechanisms for different types of data tables and being unable to support unified version control for heterogeneous databases.

[0006] To achieve the above objectives and other advantages, some embodiments of this application provide the following aspects:

[0007] Firstly, some embodiments of this application provide a database version control method based on table classification management, including:

[0008] Obtain the structural information of the target database, automatically identify and classify the data tables in the target database, and determine the update strategy for different types of data tables based on the classification results;

[0009] A baseline version file for the database is generated based on the classification results, and the version identifier and release information of the baseline version file are recorded.

[0010] By comparing the baseline version files with different version identifiers, the change types of database objects are identified and difference analysis results are generated;

[0011] Based on the difference analysis results and the update strategy, generate corresponding update scripts for different types of data tables;

[0012] The update script is verified and executed, and the version identifier and release information are updated based on the execution result.

[0013] Secondly, some embodiments of this application provide a database version control system based on table classification management, including:

[0014] The data table classification management module is used to obtain the structural information of the target database, automatically identify and classify the data tables in the target database, and determine the update strategy for different types of data tables based on the classification results.

[0015] The baseline version generation module is used to generate a baseline version file for the database based on the classification results, and to record the version identifier and release information of the baseline version file;

[0016] The version difference analysis module is used to compare the baseline version files with different version identifiers, identify the change types of database objects, and generate difference analysis results.

[0017] The update script generation module is used to generate corresponding update scripts for different types of data tables based on the difference analysis results and the update strategy.

[0018] The script verification and execution module is used to verify and execute the update script, and update the version identifier and the release information according to the execution result.

[0019] Thirdly, some embodiments of this application also provide an electronic device, the electronic device comprising:

[0020] One or more processors; and a memory storing computer program instructions that, when executed, cause the processors to perform the database version control method based on table classification management as described above.

[0021] Fourthly, some embodiments of this application also provide a computer-readable storage medium having a computer program and / or instructions stored thereon, wherein the computer program and / or instructions, when executed by a processor, implement the database version control method based on table classification management as described above.

[0022] Fifthly, some embodiments of this application also provide a computer program product, including a computer program and / or instructions, which, when executed by a processor, implement the database version control method based on table classification management as described above.

[0023] Compared with existing technologies, the solution provided in this application introduces table classification management and differentiated update mechanisms into the database version management process. This allows for the automatic acquisition of the target database's structural information, identification and classification of data tables, and determination of corresponding update strategies based on the business attributes and data independence differences of the data tables. This enables the system to adopt matching update methods for different types of data tables, avoiding data loss and inefficient updates caused by uniform processing in traditional version control methods. This achieves unified version control in heterogeneous database environments. By performing difference analysis on baseline files of different versions, it can automatically identify the addition, deletion, and structural changes of database objects, providing accurate data input for the generation of update scripts. Syntax and logic checks and simulation verification are performed before execution to ensure the security and correctness of script execution. This achieves automated, intelligent, and traceable version management of databases in multi-type and heterogeneous environments, significantly improving the stability, security, and operational efficiency of database version control. Attached Figure Description

[0024] To more clearly illustrate the technical solutions in the embodiments of this application, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of this application. For those skilled in the art, other implementation methods can be obtained based on these drawings without creative effort.

[0025] Figure 1 This is one of the flowcharts illustrating a database version control method based on table classification management provided in this application embodiment;

[0026] Figure 2 This is a schematic diagram of the structure of a database version control system based on table classification management provided in an embodiment of this application;

[0027] Figure 3 This is a second flowchart illustrating a database version control method based on table classification management provided in this application embodiment;

[0028] Figure 4 This is a schematic diagram of the structure of the electronic device provided in the embodiments of this application. Detailed Implementation

[0029] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0030] First Embodiment

[0031] The first embodiment of this application relates to a database version control method based on table classification management, referring to... Figure 1 As shown, the method may include the following steps:

[0032] Step S1: Obtain the structural information of the target database, automatically identify and classify the data tables in the target database, and determine the update strategy for different types of data tables based on the classification results.

[0033] Before step S1, the project initialization phase configuration is performed first. This phase mainly includes: creating a project management database to centrally manage the metadata and configuration information required for database version control. This project management database stores management data such as connection parameters, table classification configurations, version identifiers, release information, and script execution records for heterogeneous databases. It also involves configuring connection information for heterogeneous databases, including connection parameters and adaptation rules for different database types such as MySQL and ClickHouse, to support the access and operation of multiple databases on a unified platform. A table classification configuration system is established to define the management type attributes and data retention schemes for various data tables, providing basic configuration support for subsequent table classification and identification. Finally, the baseline version management mechanism is initialized, and a version history table is created to record historical information for each baseline generation, update, and release. Through these initialization operations, unified connection and version management preparation is achieved in a heterogeneous database environment, providing a consistent data access foundation and management framework for subsequent database structure scanning, classification and identification, baseline generation, and differentiated updates.

[0034] In this embodiment, step S1 specifically includes:

[0035] Step S101: Scan the target database to obtain the metadata information of each data table, and determine the data independence identifier corresponding to each data table based on the metadata information. The data independence identifier is used to indicate whether the data table has independent data content.

[0036] Step S102: Combine the data independence identifier and the business attributes of the data table to automatically classify the data table to distinguish data tables with different business types, and record the table classification information and set the corresponding update strategy according to the classification results.

[0037] Specifically, the target database refers to a database instance that is accessed by a version control system and subjected to structure identification, classification management, and version update operations. This database can be a business database running in different environments (such as development, testing, and production), or it can be a heterogeneous database of various types (such as MySQL, ClickHouse, PostgreSQL, etc.), and it is the direct object of this method execution.

[0038] First, a full scan of the target database is performed to obtain the metadata information of each table in the target database. The metadata information includes, but is not limited to: table name, table type; field name, field type, default value, constraint and index information; primary key and foreign key definitions, inter-table dependencies; database name, engine type (such as MySQL engine, ClickHouseMergeTree engine, etc.) and other structural information.

[0039] After obtaining the metadata information, the data independence identifier (has_own_data) for each data table is determined based on the table definition and data content attributes. This identifier is used to characterize whether the data table has independent data content that needs to be retained during version management.

[0040] Specifically, if the table type is a logical structure object such as a view, trigger, or user-defined function, since it does not store actual data content, it will automatically set has_own_data=0; if it is an entity table, its data attributes will be further analyzed.

[0041] For entity tables, data independence is determined by analyzing the amount of data within the table, field definitions, and primary key constraints. If the table stores fixed system configurations or static feature data (such as permission configurations, menu dictionaries, and feature rule tables), and the table structure and data are tightly bound, set `has_own_data=1`. If the table stores runtime dynamic business data (such as user thresholds and policy parameters), also set `has_own_data=1`, but implement a data retention strategy during subsequent updates. If the table is empty or only depends on external data sources, set `has_own_data=0`. Further judgment can be made based on auxiliary features, such as checking whether the table contains a primary key, whether it has non-nullable rows, and whether foreign key dependencies exist, to improve the accuracy of automatic identification.

[0042] By combining data independence identifiers and the business attributes of each table, all data tables in the target database are automatically classified.

[0043] Specifically, the system classifies data tables into the following four categories through methods such as table name feature matching, field semantic analysis, and configuration rule comparison:

[0044] Feature database table: Used to store static feature or rule data, such as IP address database, blacklist / whitelist, etc. The table structure is stable and the data update frequency is low.

[0045] Private management tables: used for system-level management configurations, such as user permissions, menus, dictionary items, etc. The structure and data need to be synchronized with each version update.

[0046] User data table: Used to store data generated by users during business operations, such as strategy parameters and threshold settings. The operational data must be retained when the structure is updated.

[0047] No data storage table: such as views, triggers, and user-defined functions, only the definition structure needs to be updated, without involving data migration.

[0048] After automatically classifying the data tables in the target database, the system formulates differentiated version update strategies based on the business characteristics and data independence of different categories of data tables. This ensures data security while improving the accuracy and efficiency of version updates. Specifically, the system sets different update methods for different types of data tables based on the classification results:

[0049] The feature library table is primarily used to store static features or rule-based data. Its structure is stable and it is updated infrequently. A feature data replacement update strategy is adopted, which involves performing a full export and re-import operation when generating a new version. This replacement method updates the entire feature content, ensuring data integrity and consistency, and avoiding the system burden caused by frequent structural adjustments.

[0050] For private management tables, such as permission tables, menu tables, and dictionary tables, both the structure and data may change during version updates. A full rebuild update strategy is adopted for these tables, which involves sequentially executing "DROP the old table → CREATE the new table structure → INSERT the new data" to clear the old table and rebuild the new structure, ensuring that the table definitions and data remain completely consistent with the current version.

[0051] The user data table stores dynamic data generated during business operations, such as user thresholds and policy configurations. A data retention-based update strategy is adopted, which involves executing "Rename the old table (RENAME) → Create a new table (CREATE) → Select and insert data from the old table (INSERT-SELECT)". This migrates valid data from the old table while creating a new structure, ensuring lossless data retention during version upgrades.

[0052] For tables without data storage, such as logical structure objects like views, triggers, and user-defined functions, the system adopts a structure reconstruction update strategy, which involves executing the update method of "deleting the old definition (DROP) → creating a new definition (CREATE)," updating only their logical definitions without involving data migration.

[0053] Furthermore, in heterogeneous database environments, for tables in the ClickHouse database with unique structural attributes such as partition keys, sort keys, and projection tables, a differentiated update strategy is employed during version updates to ensure data security and dependency consistency during structural adjustments. Specifically: when a table structure change involving only field-level modifications is detected, the system prioritizes generating an "ALTER TABLE" statement for dynamic structural modification, avoiding data loss caused by directly deleting the table structure; when a change in partition keys or sort keys is detected, a phased reconstruction mechanism is used: first, a temporary table conforming to the new structure definition is created; then, data is migrated using "INSERT INTO … SELECT …"; finally, the old table is replaced by a renaming operation; for the addition or deletion of projection table definitions, the system adjusts the configuration by executing "ALTER TABLE … ADD PROJECTION" or "DROP PROJECTION" statements without affecting the data storage structure of the main table. Through these differentiated update strategies, the system can achieve secure updates and automated control of structural changes within ClickHouse's columnar storage database architecture, effectively avoiding data loss, partition corruption, or dependency failures caused by operations such as DROP and CREATE during version updates.

[0054] In this embodiment, through the processing of the above steps S101 and S102, automatic identification, type differentiation and strategic management of data tables can be realized in a heterogeneous database environment, thereby providing a unified structured input for subsequent baseline version generation, difference comparison and update script generation, laying the foundation for the basic data framework of database version control.

[0055] Step S2: Generate a baseline version file for the database based on the classification results, and record the version identifier and release information of the baseline version file.

[0056] In this embodiment, step S2 specifically includes:

[0057] Step S201: Determine the export scope and export method of each type of data table based on the classification results. Export the table structure and corresponding data for data tables with independent data content, and export only the table structure for data tables without independent data content.

[0058] Step S202: Summarize the exported table structure and data to generate a baseline version file of the database, assign a unique version identifier to the baseline version file, and record the version identifier and the corresponding release information. The release information includes the generation time of the baseline version file, the database type, and the operating environment information.

[0059] Specifically, based on the classification results of various data tables, the export scope and export method for each type of data table are determined to ensure that the baseline version file can fully reflect the structural state and data content of the database under the current version. The export scope is used to limit the types and content levels of database objects that need to be exported when generating the baseline version file, including whether to export table structure definitions, data content in the tables, and related logical objects (such as views, triggers, functions, etc.). The export method is used to define the export execution strategy and command flow for the selected export objects, such as executing "DROP→CREATE→INSERT" to achieve a full export, or executing only the "CREATE" command to achieve a structure export.

[0060] For data tables with independent data content (has_own_data=1), such as feature database tables, private management tables, or user data tables, the system includes both the table structure definition and the corresponding data content during export. The export operation is completed by executing the sequential commands "DROP TABLE IF EXISTS→CREATE TABLE→INSERT INTO", thereby achieving synchronization between table structure reconstruction and data export.

[0061] For data tables that do not have independent data content (has_own_data=0), such as views, triggers, and user-defined functions, only the definition structure of the table or logical object needs to be exported, without exporting the actual data content, in order to reduce redundant storage and improve version generation efficiency.

[0062] During the export process, the system automatically distinguishes the target database type (such as MySQL, ClickHouse) and calls the corresponding export interface and data extraction module. For example, for MySQL databases, the system can directly obtain table definitions and data through mysqldump or internal script modules; for ClickHouse databases, a combination of partition export and structure definition extraction is used, and the original tables and basic business tables are exported first according to the dependency order to ensure the correct dependency relationships during subsequent version recovery and updates.

[0063] The table structure definition file and data file exported in step S201 are summarized, organized, and packaged to generate a baseline version file for the database. The baseline version file can be implemented as an SQL file, generated by exporting the full table structure definition and data content of the target database. It records the complete structure and data status of the database in the current version. This file can serve as the basis for version release and also as the basis for subsequent difference analysis and version rollback. It contains the following main contents: Structure definition file: records the SQL definition of the table structure for each data table, including field information, primary and foreign key relationships, index structure, etc.; Data file: records the exported data content, which can be SQL INSERT statements or data files (CSV, Parquet, etc. formats); Version description file: records metadata information for this baseline version, such as version identifier, generation time, exported database type, runtime environment identifier, total number of data tables, etc.

[0064] After generation, a unique version identifier is assigned to each baseline version file, for example, in the form of "V1.0.0", "V1.0.1", etc. Simultaneously, this version identifier, along with the corresponding release information, is recorded in the version history table of the project management database. Release information includes: generation time (timestamp); database type (MySQL, ClickHouse, etc.); runtime environment information (e.g., development environment, testing environment, production environment); and operator and execution node information (optional).

[0065] In this embodiment, the export scope and export method for various data tables are determined based on the table classification results. This enables the system to adopt differentiated export strategies for tables with different business attributes and data independence, ensuring core data security while avoiding redundant exports and guaranteeing the accuracy and efficiency of baseline generation. The exported table structure definitions and data files are uniformly summarized and encapsulated to generate a baseline version file with a unique version identifier, and the corresponding release information is recorded, thereby achieving accurate recording and traceable management of the database version status. Through the above mechanism, the problems of inconsistent exports, inconsistent version records, and difficulty in backtracking in traditional database version control are effectively solved. Standardized baseline management and secure version release are achieved in cross-database environments, improving the accuracy and execution efficiency of version control.

[0066] Step S3: Compare baseline version files with different version identifiers, identify the change types of database objects, and generate difference analysis results.

[0067] In this embodiment, step S3 specifically includes:

[0068] Step S301: Parse the structure definition information of the database objects from the source baseline version file and the target baseline version file respectively;

[0069] Step S302: Perform a table-level comparison of the structure definition information to identify newly added objects, deleted objects, and objects with structural changes;

[0070] Step S303: Perform field-level comparison of the field metadata of the structurally changed object to determine the addition, deletion, and attribute differences of the fields;

[0071] Step S304: Generate difference analysis results based on the results of table-level and field-level alignment.

[0072] Specifically, the system first extracts the structural definition information of database objects from both the source baseline version file (the old version) and the target baseline version file (the new version). Database objects refer to version-controlled structured entities within a database, including tables, views, triggers, functions, and indexes. By parsing the structural definition portion of the baseline file, metadata information related to each object is extracted. For example, for table objects, this includes parsing field names, field types, constraints, primary / foreign key definitions, index structure, and engine type; for view objects, it extracts the view definition statement and dependent table information; and for functions or triggers, it extracts their creation scripts and triggering conditions. During parsing, the system selects the appropriate syntax parsing rules based on the database type (e.g., MySQL or ClickHouse). For example, for MySQL, the parser extracts the standard SQL structural definition; for ClickHouse, it simultaneously extracts unique structural fields such as partition keys, sort keys, and projection definitions. All parsing results are uniformly converted into a structured data description model for subsequent table-level and field-level difference comparisons. This model can be stored in JSON or as internal data objects to ensure that structural differences are automatically recognized by the program.

[0073] Based on the structured definition information described above, a table-level comparison is performed between the source and target versions. By matching table names and unique identifiers, newly added table objects in the target version, table objects deleted in the source version, and table objects that exist in both versions but whose structure has changed can be identified. The results of the table-level comparison provide input for subsequent field-level difference identification.

[0074] For objects with structural changes identified in the table-level comparison, their field metadata is further extracted for field-level comparison. Field-level comparison is used to determine the addition, deletion, and attribute changes of each field, such as field type modification, length adjustment, default value or constraint changes. By comparing the field metadata item by item, field-level difference records can be generated, and the change type and scope of impact can be marked.

[0075] The results of both table-level and field-level comparisons are combined to generate a complete difference analysis. The difference analysis results are output in a structured format (such as JSON, XML, or database records), including the name of the changed object, the change type, difference details, the scope of the change's impact, and statistical summary information. For specific database types (such as ClickHouse), structural changes that may affect data compatibility, such as adjustments to partition keys or sort keys, can also be highlighted in the difference analysis. The generated difference analysis results can be directly used by the update script generation module to automatically generate SQL update scripts based on precise differences.

[0076] This embodiment introduces an automated difference identification mechanism based on structured parsing and hierarchical comparison. This mechanism automatically extracts the definition information of database objects by performing structured parsing on the baseline files of the source and target versions, and performs precise comparisons at both the table and field levels. This enables rapid and accurate detection and quantification of structural changes in the database between different versions, thereby achieving automatic identification and difference quantification for newly added, deleted, and modified objects. The difference analysis results are not only used to automatically generate update scripts but also provide complete and calculable data for detecting compatibility risks of structural changes and quickly rolling back the database version when needed, thus realizing intelligent and automated management of the entire database version control process.

[0077] Step S4: Based on the difference analysis results and update strategy, generate corresponding update scripts for different types of data tables.

[0078] In this embodiment, step S4 specifically includes:

[0079] Step S401: Analyze the difference analysis results and extract the change types and corresponding structural difference data for each change data table;

[0080] Step S402: Determine the update method for each changed data table based on the update strategy corresponding to different types of data tables;

[0081] Step S403: Based on the mapping relationship between change type and update method, call the preset script generation template to convert the structural difference data into SQL update instructions and generate update script.

[0082] Specifically, the difference analysis results are first parsed to extract the change types and corresponding structural difference data for each changed data table. The difference analysis results, generated in step S3, contain structural difference information between the source and target versions of each data table. By parsing the field metadata and structure definitions, the specific change type of each data table is identified, such as addition, deletion, or modification, and the corresponding structural difference data is extracted. For example, for a modification-type table, the structural difference data may include the definition of the added field, field type change information, or constraint adjustment content; for an addition-type table, the structural difference data may include the complete table structure definition and initial data content.

[0083] Based on the update strategies corresponding to different types of data tables, the update method for each changed data table is determined. During the classification phase of different data table types, each type of data table is pre-bound with a corresponding update strategy. For example, the feature database table uses a "feature data replacement update strategy," which updates the feature content entirely through replacement; the private management table uses a "full reconstruction update strategy," executing "DROP→CREATE→INSERT"; the user data table uses a "data retention update strategy," executing "RENAME→CREATE→INSERT-SELECT" to retain running data; and tables without data storage (such as views and triggers) use a "structure reconstruction update strategy," executing "DROP→CREATE." By matching the category of the changed data table with its corresponding update strategy, the appropriate update method is automatically determined, ensuring that the generated SQL commands match the table characteristics, achieving both structural updates and preventing data loss.

[0084] The script generation templates predefine various script generation modes, each corresponding to a specific update operation mode, such as table creation templates, field modification templates, and data migration templates. Based on the change type identified in the difference analysis results, the system automatically selects the corresponding template and dynamically populates the template variables with structural difference data, thereby generating SQL update statements that conform to syntax rules and can be executed directly. Template variables may include information such as table names, field names, field types, constraints, index structure, and engine type. The system automatically replaces these variables during the generation process, enabling the generated update script to run directly in the database environment and complete the structural and data update operations.

[0085] For example, the table creation template is suitable for adding new table objects or tables whose structure has completely changed, and can be directly used for new table creation and initial data writing during version updates. An example of the generated SQL statement is as follows:

[0086] "DROP TABLE IF EXISTS user_profile;

[0087] CREATE TABLE user_profile (

[0088] user_id INT PRIMARY KEY AUTO_INCREMENT,

[0089] username VARCHAR(64) NOT NULL,

[0090] email VARCHAR(128),

[0091] create_time DATETIME DEFAULT CURRENT_TIMESTAMP

[0092] ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

[0093] INSERT INTO user_profile (user_id, username, email)

[0094] VALUES (1, 'admin', 'admin@test.com');"

[0095] For cases where the table structure requires partial adjustments, an ALTER TABLE statement is generated by calling a field modification template to add fields, modify their types, or adjust constraints. For example:

[0096] ALTER TABLE order_info ADD COLUMN order_status VARCHAR(32) DEFAULT 'pending';

[0097] ALTER TABLE order_info MODIFY COLUMN amount DECIMAL(10,2) NOT NULL;”

[0098] For user data tables that need to retain historical business data, the data migration template is invoked to generate update statements for a "rename-rebuild-data migration" process, so as to achieve structural adjustments while preserving operational data. For example:

[0099] "RENAME TABLE user_threshold TO user_threshold_old;

[0100] CREATE TABLE user_threshold (

[0101] id INT PRIMARY KEY AUTO_INCREMENT,

[0102] threshold_value FLOAT NOT NULL,

[0103] update_time DATETIME DEFAULT CURRENT_TIMESTAMP

[0104] ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

[0105] INSERT INTO user_threshold (id, threshold_value, update_time)

[0106] SELECT id, threshold_value, NOW() FROM user_threshold_old;

[0107] DROP TABLE user_threshold_old;”

[0108] For tables without data storage (such as logical objects like views, triggers, and functions), the structure reconstruction template is invoked to generate only the logically defined delete and create instructions, for example:

[0109] “DROP VIEW IF EXISTS active_user_view;

[0110] CREATE VIEW active_user_view AS

[0111] SELECT user_id, username FROM user_profile WHERE status = 'active';"

[0112] In heterogeneous database scenarios, for the unique structural attributes of the ClickHouse database, update templates based on ALTERTABLE are generated first to enable dynamic modification of partition keys or projection tables while avoiding data loss. For example:

[0113] "ALTER TABLE log_event ADD COLUMN event_level String DEFAULT 'INFO';

[0114] ALTER TABLE log_event ADD PROJECTION p_level (SELECT event_level,count() GROUP BY event_level);”

[0115] When a connection configuration change is detected in a table created with the MySQL engine in the ClickHouse database, a MySQL engine connection information update script must also be generated. Based on the connection parameter changes recorded in the difference analysis results, the corresponding connection definition statements are automatically generated to update the external data source configuration of the ClickHouse table. For example, when the host address, port number, or authentication information of the MySQL database changes, the system can automatically generate the following update script:

[0116] "ALTER TABLE ck_mysql_table MODIFY ENGINE = MySQL('192.168.1.200:3306', 'analytics_db', 'user_v2', 'new_password', 'table_source');"

[0117] In this embodiment, through the above-mentioned templated script generation mechanism, SQL update statements that conform to the syntax rules and can be directly executed can be automatically generated for different types of databases, different categories of data tables and different change types, thereby realizing fully automated and traceable control of database version upgrades, ensuring the correctness, security and cross-database environment compatibility of the update process.

[0118] Step S5: Verify and execute the update script, and update the version identifier and release information based on the execution results.

[0119] In this embodiment, step S5 specifically includes:

[0120] Step S501: Perform syntax and logic consistency checks on the update script to detect potential structural conflicts or data loss risks;

[0121] Step S502: After the verification is passed, the execution engine of the target database is called to simulate the execution of the update script and generate an execution preview result;

[0122] Step S503: Confirm the preview result and execute the update script based on the confirmation result to complete the database structure and / or data update operation;

[0123] Step S504: After the update operation is completed, record the execution process and result information of the update script, and update the version information and corresponding release information.

[0124] Specifically, the generated update script first undergoes syntax and logical consistency checks. Syntax checks verify whether the script conforms to the syntax rules of the target database type, such as whether field definitions are valid, SQL statements are closed, and keywords are used correctly. Logical consistency checks analyze potential structural conflicts or data risks that may arise after script execution, such as duplicate creation of tables with the same name, missing foreign key constraints, and incompatible field types. A built-in parser performs semantic analysis on SQL commands and combines this with table structure metadata from the difference analysis results for dependency verification, identifying potential risks in advance. If inconsistencies or dangerous operations are detected, the system generates a risk warning and halts subsequent execution.

[0125] After the script passes validation, the target database's execution engine is invoked for simulated execution to generate a preview result. Simulated execution refers to parsing and executing the update script's logical operations sequentially without actually modifying the database structure or data, to verify its feasibility and the correctness of the execution order. In preview mode, detailed execution logs are generated, including the planned SQL statements, the expected number of rows affected, potential dependency changes, and estimated execution time, providing operators with a predicted version update outcome. This process ensures the script has passed logical validation before actual execution, preventing execution failures due to unknown dependencies or incorrect ordering.

[0126] After confirming that the preview results are normal, the update script is executed based on user confirmation or an automated decision-making strategy to complete the formal update of the database structure and data. During execution, the system calls the database interface in the order of the SQL statements in the script, sequentially performing operations such as table creation, field modification, data migration, and logical object reconstruction. To prevent update interruption or partial failure, the system employs a transaction mechanism or batch rollback mechanism to automatically revert to the state before the update in case of errors, ensuring data consistency. The execution results are output to the control log in real time, including information such as the number of successfully executed statements, the number of modified table structure items, and the number of rows affected.

[0127] After the update operation is completed, the entire execution process and results of the update script are recorded, including execution time, target database information, operator identification, execution status (success or failure), and exception logs. Subsequently, the version identifier and its corresponding release information in the project management database are updated. The release information records the metadata of this version update, such as update time, execution environment (test or production), execution script summary, and checksum hash value, for subsequent version tracking and backtracking.

[0128] In this embodiment, a closed-loop verification mechanism of "pre-verification - simulated execution - confirmed execution - result registration" is established before the update script is executed, realizing the secure and automated management of the database version update process. This mechanism not only ensures the controllability and recoverability of the version update operation, but also provides complete data basis for subsequent version comparison, problem backtracking and version rollback, thereby significantly improving the security and reliability of the database under multi-environment version control.

[0129] The steps of the various methods described above are only for clarity. In practice, they can be combined into one step or some steps can be split into multiple steps. As long as they include the same logical relationship, they are all within the scope of protection of this application. Adding insignificant modifications or introducing insignificant designs to the algorithm or process, but without changing the core design of the algorithm and process, are also within the scope of protection of this application.

[0130] Second Embodiment

[0131] The second embodiment of this application relates to a database version control system based on table classification management, referring to... Figure 2 As shown, the system includes:

[0132] The data table classification management module is used to obtain the structural information of the target database, automatically identify and classify the data tables in the target database, and determine the update strategy for different types of data tables based on the classification results.

[0133] This module categorizes tables by scanning database metadata (such as table names, field information, indexes, constraints, storage engine types, and data volume statistics) and combining this with identified data independence identifiers. The system automatically classifies data tables into different categories, such as feature database tables, private management tables, user data tables, and tables without data storage, and configures differentiated update strategies for each category. For example, a feature data replacement update strategy is used for feature database tables, a full reconstruction update strategy is used for private management tables, a data retention update strategy is used for user data tables, and a structure reconstruction update strategy is used for tables without data storage. This module constructs a classification system based on table attributes, providing a strategic basis for subsequent version comparisons and differentiated updates.

[0134] The baseline version generation module is used to generate baseline version files for the database based on the classification results, and to record the version identifier and release information of the baseline version files.

[0135] This module, during project initialization or phased version release, determines the export scope and method based on classification results. For tables with independent data content, it exports both the table structure and data content; for tables without data storage, it only exports the table structure information, thereby generating a standardized baseline version file. This file contains the complete structure and data status of the database at a specific point in time and is bound with a unique version identifier and release information, establishing the initial baseline for database version management. Through this module, full version persistence and traceable management of the database can be achieved.

[0136] The version difference analysis module is used to compare baseline version files with different version identifiers, identify the change types of database objects, and generate difference analysis results.

[0137] This module parses the structural definition information in the baseline version files of the source and target versions, compares the differences in database objects (such as tables, fields, indexes, views, etc.), identifies newly added, deleted, and structurally changed objects, and further performs field-level metadata comparison to determine the addition, deletion, or attribute differences of fields, thus generating detailed difference analysis results. These results provide structured input data for subsequent update script generation.

[0138] The update script generation module is used to generate corresponding update scripts for different types of data tables based on the difference analysis results and update strategies.

[0139] This module analyzes the difference analysis results to identify the type of changed data table and the type of change. Based on the update strategy for different table types, it calls the corresponding script generation template. For example, it generates a "DROP→CREATE→INSERT" script for private management tables and a "RENAME→CREATE→INSERT-SELECT" script for user data tables to preserve runtime data. For tables defined with the MySQL engine in the ClickHouse database, the system automatically generates a MySQL engine connection information update script to ensure the correctness and consistency of cross-database access. This module's templated script generation mechanism ensures that the generated SQL commands can be directly executed in the target database.

[0140] The script verification and execution module is used to verify and execute update scripts, and update the version identifier and release information based on the execution results.

[0141] This module first performs syntax and logic consistency checks on the generated SQL update script, detecting potential structural conflicts or data loss risks. If the checks pass, it calls the database execution engine to perform a simulated execution, generates an execution preview result, and then executes the formal update operation after confirmation. After the update is completed, it automatically records the execution log, version change information, and result status, updating the version history to achieve traceability and recoverability of database version changes.

[0142] Through the collaborative work of the aforementioned modules, the system enables automated management of the entire database version lifecycle, from structure identification, baseline generation, and difference analysis to script verification and execution. The system supports unified management of heterogeneous databases such as MySQL and ClickHouse, adapting to the characteristics of different databases (e.g., ClickHouse's partition keys, sort keys, and projection attributes) and automatically generating corresponding SQL update scripts during version upgrades, achieving precise synchronization of structure and data. This solution effectively improves the efficiency and security of database version control in complex environments, ensuring data consistency and system stability.

[0143] Reference Figure 3 As shown in the embodiment of this application, the operation flow of the database version control system based on table classification management is as follows:

[0144] After system startup, the system first enters the database connection configuration phase. The data table classification management module configures and establishes connection information with the target database to support access to heterogeneous databases such as MySQL and ClickHouse. After connection configuration is complete, the system enters the structure scanning and classification phase. This phase scans the target database structure information, extracts metadata for each data table (such as table name, field definitions, indexes, constraints, and data volume information), and automatically classifies the data tables based on the data independence identifier `has_own_data` and business attribute rules, generating table classification information.

[0145] Subsequently, the system performs attribute setting operations to set the management attributes and update strategies for various types of data tables. For example, private management tables adopt a full rebuild update strategy, while user data tables adopt a data retention update strategy, in order to clarify the handling methods for different types of data tables in subsequent version control.

[0146] After completing the above configuration, the system determines whether a baseline version release request exists. If the result is "yes," the system enters the baseline version generation stage. The baseline version generation module determines the export scope and method based on the table classification results. For tables with independent data content, it exports the structure and data; for tables without data storage, it only exports the structure definition, thereby generating a database baseline version file and recording the version identifier and release information. If the result is "no," the system further determines whether a version upgrade request exists. If a version upgrade request exists, the system enters the version difference analysis stage. The version difference analysis module compares the source baseline version file with the target baseline version file, identifies the addition, deletion, and structural changes of database objects, and generates difference analysis results. If no upgrade request is detected, the system enters a monitoring waiting state to listen for subsequent version operation events.

[0147] After generating the difference analysis results, the system enters the update script generation and verification phase. The update script generation module generates the corresponding SQL update script based on the difference analysis results and update strategy. For example, it generates a full rebuild script "DROP→CREATE→INSERT" for private management tables and a retainable update script "RENAME→CREATE→INSERT-SELECT" for user data tables.

[0148] The generated update script will enter the script verification and execution module for verification and execution. This module first performs syntax validation and logical consistency checks to determine if the script has any potential structural conflicts or data loss risks. If the script verification passes, the system will execute the update operation and recording phase, calling the database execution engine to run the update script to complete the database structure and data updates, and recording execution logs and result information, updating the version identifier and release information; if the script verification fails, the system will automatically revert to the version difference analysis phase, re-execute the difference detection and script generation process until verification passes.

[0149] Through the above process, the system realizes automated closed-loop control of the database from connection configuration, table structure identification, classification configuration, baseline version generation, version difference analysis, update script generation to verification execution. It can realize full lifecycle management of versions in heterogeneous database environments, ensuring data consistency, upgrade security and system traceability.

[0150] The content of the above-described database version control method embodiments based on table classification management is applicable to this system embodiment. The specific functions implemented in this system embodiment are the same as those in the above-described database version control method embodiments based on table classification management, and the beneficial effects achieved are also the same as those achieved in the above-described database version control method embodiments based on table classification management. To reduce repetition, further details are omitted here.

[0151] Furthermore, some embodiments of this application also provide an electronic device. The electronic device can be various forms of digital computer, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, etc. The electronic device can also be various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices, and other similar computing devices.

[0152] The electronic device includes: one or more processors; and a memory storing computer program instructions, which, when executed, cause the processor to perform a database version control method based on table classification management as provided in any one or more of the above embodiments. Figure 4 An exemplary structural diagram of the electronic device is disclosed. The electronic device includes one or more processors 1101, a memory 1102, and interfaces for connecting the components, including high-speed interfaces and low-speed interfaces. The components are interconnected via different buses and can be mounted on a common motherboard or otherwise installed as needed. The processors can process instructions executed within the electronic device, including instructions stored in or on memory to display graphical information of a GUI on an external input / output device (such as a display device coupled to the interface). In some other embodiments, multiple processors and / or multiple buses can be used with multiple memories and multiple memory modules, if desired. Similarly, multiple electronic devices can be connected, each providing some of the necessary operations. The components, their connections and relationships, and their functions shown herein are merely examples and are not intended to limit the implementation of the present application described and / or claimed herein.

[0153] The electronic device may further include an input device 1103 and an output device 1104. The processor 1101, memory 1102, input device 1103, and output device 1104 may be connected via a bus or other means. Figure 4 Taking the example of a connection between China and Israel via a bus.

[0154] Input device 1103 can receive input numerical or character information, and generate key signal inputs related to user settings and function control of the electronic device, such as a touch screen, keypad, mouse, trackpad, touchpad, joystick, one or more mouse buttons, trackball, joystick, etc. Output device 1104 may include a display device, auxiliary lighting device (e.g., LED), and haptic feedback device (e.g., vibration motor). The display device may include, but is not limited to, a liquid crystal display, a light-emitting diode display, and a plasma display. In some embodiments, the display device may be a touch screen.

[0155] To provide interaction with the user, the electronic device can be a computer. The computer has: a display device (e.g., a cathode ray tube or LCD monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse) through which the user provides input to the computer. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback); and input from the user can be received in any form (e.g., voice input or tactile input).

[0156] In this embodiment, a computer-readable medium stores a computer program / instruction, which, when executed by a processor, implements a database version control method based on table classification management provided in any one or more of the above embodiments. This computer-readable medium may be included in the electronic device described in the above embodiments; or it may exist independently and not assembled into that device. The aforementioned computer-readable medium carries one or more computer-readable instructions.

[0157] The memory 1102 can serve as a non-transitory computer-readable storage medium, used to store non-transitory software programs, non-transitory computer-executable programs, and modules. The processor 1101 executes various functional applications and data processing of the server by running the non-transitory software programs, instructions, and modules stored in the memory 1102, thereby implementing the program instructions / modules corresponding to the methods provided in any one or more of the embodiments described above in this application.

[0158] The memory 1102 may include a program storage area and a data storage area. The program storage area may store the operating system and applications required for at least one function; the data storage area may store data created based on the use of the electronic device. Furthermore, the memory 1102 may include high-speed random access memory and may also include non-transitory memory, such as at least one disk storage device, flash memory device, or other non-transitory solid-state storage device. In some embodiments, the memory 1102 may optionally include memory remotely located relative to the processor 1101, and these remote memories can be connected to the electronic device via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof.

[0159] It should be noted that the computer-readable medium described in this application can be a computer-readable signal medium or a computer-readable storage medium, or any combination thereof. Computer-readable media can be, for example, but not limited to, electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatuses, or devices, or any combination thereof. More specific examples of computer-readable storage media may include, but are not limited to, electrical connections having one or more wires, portable computer disks, hard disks, random access memory, read-only memory, erasable programmable read-only memory, optical fibers, portable compact disk read-only memory, optical storage devices, magnetic storage devices, or any suitable combination thereof. In this application, a computer-readable 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.

[0160] Computer-readable media include permanent and non-permanent, removable and non-removable media, which can store information by any method or technology. Information can be computer-readable instructions, data structures, program modules, or other data. Examples of computer storage media include, but are not limited to, phase-change memory, static random access memory, dynamic random access memory, other types of random access memory, read-only memory, electrically erasable programmable read-only memory, flash memory or other memory technologies, read-only optical discs, digital versatile optical discs or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transfer medium that can be used to store information accessible by a computing device.

[0161] Computer program code for performing the operations of this application 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 local area networks (LANs) or wide area networks (WANs), or it can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0162] In the above embodiments, all or part of the implementation can be achieved through software, hardware, firmware, or any combination thereof. For example, it can be implemented using an application-specific integrated circuit (ASIC), a general-purpose computer, or any other similar hardware device. In some embodiments, the software program of this application can be executed by a processor to implement the above steps or functions. Similarly, the software program of this application (including related data structures) can be stored in a computer-readable recording medium, such as RAM memory, magnetic or optical drives, floppy disks, and similar devices. In addition, some steps or functions of this application can be implemented in hardware, for example, as circuitry that cooperates with a processor to perform the various steps or functions.

[0163] The computer program product provided in this application includes one or more computer programs / instructions. When executed by a processor, these computer programs / instructions generate, in whole or in part, the processes or functions described in this application. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a computer-readable storage medium or transmitted from one computer-readable storage medium to another. For example, the computer instructions may be transmitted from one website, computer, server, or data center to another website, computer, server, or data center via wired (e.g., coaxial cable, fiber optic, digital subscriber line) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium may be any available medium that a computer can access or a data storage device such as a server or data center that integrates one or more available media. The available medium may be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium (e.g., solid-state drive), etc.

[0164] The flowcharts or block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of devices, methods, and computer program products according to various embodiments of this application. 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 a 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-specific system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0165] The scope of this application is defined by the appended claims rather than the foregoing description, and is therefore intended to encompass all variations falling within the meaning and scope of equivalents of the claims. No reference numerals in the claims should be construed as limiting the scope of the claims. Furthermore, it is clear that the word "comprising" does not exclude other units or steps, and the singular does not exclude the plural. Multiple units or devices recited in a device claim may also be implemented by a single unit or device in software or hardware. Terms such as "first," "second," etc., are used only for distinguishing descriptions and do not indicate any particular order, nor should they be construed as indicating or implying relative importance.

[0166] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any variations or substitutions that can be easily made by those skilled in the art within the scope of the technology disclosed in this application should be included within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims, and the above embodiments should be regarded as exemplary and non-limiting.

Claims

1. A database version control method based on table classification management, characterized in that, include: Obtain the structural information of the target database, automatically identify and classify the data tables in the target database, and determine the update strategy for different types of data tables based on the classification results; A baseline version file for the database is generated based on the classification results, and the version identifier and release information of the baseline version file are recorded. By comparing the baseline version files with different version identifiers, the change types of database objects are identified and difference analysis results are generated; Based on the difference analysis results and the update strategy, generate corresponding update scripts for different types of data tables; The update script is verified and executed, and the version identifier and release information are updated based on the execution result.

2. The database version control method based on table classification management according to claim 1, characterized in that, The steps of obtaining the structural information of the target database, automatically identifying and classifying the data tables in the target database, and determining the update strategy for different types of data tables based on the classification results include: The target database is scanned to obtain metadata information for each data table, and the data independence identifier corresponding to each data table is determined based on the metadata information. The data independence identifier is used to indicate whether the data table has independent data content. By combining the data independence identifier and the business attributes of the data table, the data table is automatically classified to distinguish data tables with different business types, and the classification information of the table is recorded and the corresponding update strategy is set according to the classification results.

3. The database version control method based on table classification management according to claim 1, characterized in that, The step of generating a baseline version file for the database based on the classification results, and recording the version identifier and release information of the baseline version file, includes: Based on the classification results, determine the export scope and export method for each type of data table. For data tables with independent data content, export their table structure and corresponding data; for data tables without independent data content, only export the table structure. The exported table structure and data are summarized to generate a baseline version file of the database, and a unique version identifier is assigned to the baseline version file. The version identifier and the corresponding release information are recorded. The release information includes the generation time of the baseline version file, the database type, and the operating environment information.

4. The database version control method based on table classification management according to claim 1, characterized in that, The steps of comparing baseline version files with different version identifiers, identifying change types of database objects, and generating difference analysis results include: Parse the structure definition information of database objects from the source baseline version file and the target baseline version file respectively; The structure definition information is compared at the table level to identify newly added objects, deleted objects, and objects with structural changes; Perform field-level comparisons on the field metadata of the structural change object to determine whether fields have been added, deleted, or have attribute differences. The difference analysis results are generated based on the results of the table-level comparison and the field-level comparison.

5. The database version control method based on table classification management according to claim 1, characterized in that, The step of generating corresponding update scripts for different types of data tables based on the difference analysis results and the update strategy includes: Analyze the difference analysis results and extract the change types and corresponding structural difference data for each change data table; Based on the update strategies corresponding to different types of data tables, determine the update method for each changed data table; Based on the mapping relationship between the change type and the update method, a preset script generation template is invoked to convert the structural difference data into SQL update instructions and generate an update script.

6. The database version control method based on table classification management according to claim 1, characterized in that, The steps of verifying and executing the update script, and updating the version identifier and release information based on the execution result, include: The update script is subjected to syntax and logic consistency checks to detect potential structural conflicts or data loss risks; After the verification is passed, the execution engine of the target database is invoked to simulate the execution of the update script and generate an execution preview result; Confirm the execution preview result, and execute the update script based on the confirmation result to complete the database structure and / or data update operation; After the update operation is completed, the execution process and result information of the update script are recorded, and the version information and corresponding release information are updated.

7. A database version control system based on table classification management, characterized in that, include: The data table classification management module is used to obtain the structural information of the target database, automatically identify and classify the data tables in the target database, and determine the update strategy for different types of data tables based on the classification results. The baseline version generation module is used to generate a baseline version file for the database based on the classification results, and to record the version identifier and release information of the baseline version file; The version difference analysis module is used to compare the baseline version files with different version identifiers, identify the change types of database objects, and generate difference analysis results. The update script generation module is used to generate corresponding update scripts for different types of data tables based on the difference analysis results and the update strategy. The script verification and execution module is used to verify and execute the update script, and update the version identifier and the release information according to the execution result.

8. An electronic device, characterized in that, The electronic device includes: One or more processors; and a memory storing computer program instructions, which, when executed, cause the processors to perform the database version control method based on table classification management as described in any one of claims 1-6.

9. A computer-readable storage medium having a computer program and / or instructions stored thereon, characterized in that, When the computer program and / or instructions are executed by the processor, they implement the database version control method based on table classification management as described in any one of claims 1-6.

10. A computer program product comprising a computer program and / or instructions, characterized in that, When the computer program and / or instructions are executed by the processor, they implement the database version control method based on table classification management as described in any one of claims 1-6.

Images