Reverse analysis method and device of SQL statement, equipment and storage medium
By using an automated SQL statement reverse parsing method to remove comment text, integrate logical statements, and recursively penetrate intermediate temporary tables, the problem of inconsistencies between the logic of business personnel and technology personnel in financial regulatory reporting was solved. This enabled efficient and standardized SQL tracing, reducing error rates and manpower consumption.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-12-02
- Publication Date
- 2026-04-10
AI Technical Summary
In the regulatory reporting platform of financial institutions, the consistency between the reporting standards of business personnel and the processing logic of technical personnel is the key to accurately completing the reporting task. Existing technology relies on manual traceability, which leads to a high error rate and long time consumption. How to automate SQL parsing and traceability has become an urgent problem to be solved.
This method provides a reverse parsing approach for SQL statements. By preprocessing to remove comment text, it analyzes and obtains multiple variables, integrates logical statements, recursively penetrates intermediate temporary tables, and outputs a unified logical statement to a specified file. It supports the parsing of complex SQL statements that include subqueries and temporary tables, and automates SQL tracing.
It reduced the error rate, improved the efficiency of traceability, shortened the traceability cycle, avoided repeated traceability caused by frequent changes in standards, reduced manpower consumption, and improved the standardization and reusability of standard documents.
Smart Images
Figure CN121833731A_ABST
Abstract
Description
Technical Field
[0001] This application relates to the fields of database security and data processing technology, and in particular to a method, apparatus, device and storage medium for reverse parsing SQL statements. Background Technology
[0002] With the rapid development of the financial industry, regulatory agencies are continuously increasing their oversight of financial institutions, requiring them to submit data tables according to different requirements and methods. These data tables are then analyzed to formulate different regulatory strategies. Currently, in the regulatory reporting platforms of various financial institutions, the consistency between the reporting standards of business personnel and the processing logic of technical personnel is crucial for accurately completing the reporting task. Traditionally, to accurately understand the current reporting standards, technical personnel manually trace the code and then provide the actual standards back to business personnel. However, due to the large number of reported data tables and the increasingly frequent changes in standards, each traceability requires significant manual intervention, leading to higher error rates and consuming considerable time. Therefore, automating SQL parsing and traceability has become an urgent problem to solve, reducing manual intervention. Summary of the Invention
[0003] Therefore, it is necessary to provide a method, apparatus, computer device, computer-readable storage medium, and computer program product for reverse parsing of SQL statements to address the above-mentioned technical problems, which can automatically complete SQL parsing and tracing and improve tracing efficiency.
[0004] Firstly, this application provides a method for reverse parsing SQL statements, including:
[0005] Preprocess the SQL statement by removing comment text from it;
[0006] By analyzing the preprocessed SQL statement, several variables are obtained, including table processing logic variables, table alias variables, and subquery identifier variables. The table processing logic variables are used to record the field processing logic of each table in the SQL statement. The table alias variables are used to establish the correspondence between table aliases in the SQL statement and source table names. The subquery identifier variables are used to mark whether each table in the SQL statement is a temporary table generated by a subquery.
[0007] By integrating multiple variables, the logical scope of the insertion table is obtained. The logical scope is a structured information set with the insertion table as the core, including the table alias of the insertion table, the source table name, the processing logic of each field, and the source table name that the field depends on. If there are multiple versions of the logical scope in the same insertion table, the multiple versions of the logical scope are merged to obtain the unified logical scope of the corresponding insertion table.
[0008] When a temporary table exists in the preprocessed SQL statement represented by the subquery identifier variable, based on the unified logical caliber of the result table, the intermediate temporary table in the preprocessed SQL statement is penetrated layer by layer through recursion. The field processing logic of the intermediate temporary table in the table processing logic variable and the source table name corresponding to the intermediate temporary table in the table alias variable are retrieved and merged into the unified logical caliber of the result table.
[0009] The unified logical standard of each result table will be output to the specified file according to the preset format.
[0010] In one embodiment, multiple variables are obtained by analyzing the preprocessed SQL statement, including:
[0011] Lexical analysis is used to locate the syntax markers in the preprocessed SQL statement, and the source table name, table alias, and field names are obtained based on the syntax markers. Syntax analysis is used to identify the temporary table name in the preprocessed SQL statement, and based on the bracket hierarchy in the preprocessed SQL statement, the field processing logic and the correspondence between the table alias and the source table name are identified to obtain multiple variables.
[0012] In one embodiment, the logical definitions of multiple versions are merged to obtain a unified logical definition for the corresponding insertion table, including:
[0013] For the table processing logic variables in different versions of the logical caliber, iterate through all field processing logic of the same inserted table in different table processing logic variables, and delete duplicate field processing logic;
[0014] Compare the source table names of the corresponding inserted tables in different table alias variables, and based on the comparison results, supplement the corresponding source table names for the processing logic of fields associated with missing source table names. After completion, update the corresponding table processing logic variables.
[0015] In one embodiment, based on the unified logical scope of the result table, the intermediate temporary tables in the preprocessed SQL statement are recursively accessed layer by layer. The field processing logic of the intermediate temporary tables in the table processing logic variables and the source table names corresponding to the intermediate temporary tables in the table alias variables are retrieved and merged into the unified logical scope of the result table, including:
[0016] From the subquery identifier variables in the unified logical framework, filter out the names of intermediate temporary tables that are related to the result table;
[0017] From the table processing logic variables in the unified logic scope, retrieve the field processing logic of the intermediate temporary table corresponding to the intermediate temporary table name, and retrieve the source table name corresponding to the intermediate temporary table from the table alias variables in the unified logic scope; based on the source table name, modify the field processing logic to change the data source of the field processing logic from the intermediate temporary table to the source table.
[0018] If, based on the subquery identifier variable, it is determined that there is no nested temporary table associated with the intermediate temporary table, the unified logical standard of the result table is obtained through the above process; if, based on the subquery identifier variable, it is determined that there is a nested temporary table associated with the intermediate temporary table, the above process is repeated until it is determined that there is no nested temporary table associated with the intermediate temporary table based on the subquery identifier variable.
[0019] In one embodiment, the preset format includes the source task number of the SQL statement associated with the file name, the file path is set hierarchically according to the batch of regulatory reports submitted by financial institutions, and the source table name, field name and field processing logic are displayed in column alignment within the file.
[0020] In one embodiment, the field processing logic part within the file retains the content recorded in the table processing logic variables, including the original field operators, multi-table join conditions, and data filtering conditions in the SQL statement; the hierarchical setting of the file path is specifically as follows: the first-level directory is named the batch number of the financial institution's regulatory report, the second-level directory is named the SQL statement tracing task type, and the third-level directory is named the tracing execution date.
[0021] Secondly, this application also provides a reverse parsing apparatus for SQL statements, comprising:
[0022] The preprocessing module is used to preprocess SQL statements and remove comment text from them.
[0023] The analysis module is used to analyze the preprocessed SQL statement to obtain multiple variables, including table processing logic variables, table alias variables, and subquery identifier variables. The table processing logic variables are used to record the field processing logic of each table in the SQL statement; the table alias variables are used to establish the correspondence between table aliases in the SQL statement and source table names; and the subquery identifier variables are used to mark whether each table in the SQL statement is a temporary table generated by a subquery.
[0024] The integration module is used to integrate multiple variables to obtain the logical scope of the insertion table. The logical scope is a structured information set with the insertion table as the core, including the table alias of the insertion table, the source table name, the processing logic of each field, and the source table name that the field depends on. When there are multiple versions of the logical scope in the same insertion table, the multiple versions of the logical scope are merged to obtain the unified logical scope of the corresponding insertion table.
[0025] The processing module is used to, when there is a temporary table in the preprocessed SQL statement represented by the subquery identifier variable, recursively penetrate the intermediate temporary table in the preprocessed SQL statement layer by layer based on the unified logical caliber of the result table, retrieve the field processing logic of the intermediate temporary table in the table processing logic variable and the source table name corresponding to the intermediate temporary table in the table alias variable, and merge them into the unified logical caliber of the result table.
[0026] The output module is used to output the unified logical standard of each result table to a specified file according to a preset format.
[0027] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:
[0028] Preprocess the SQL statement by removing comment text from it;
[0029] By analyzing the preprocessed SQL statement, several variables are obtained, including table processing logic variables, table alias variables, and subquery identifier variables. The table processing logic variables are used to record the field processing logic of each table in the SQL statement. The table alias variables are used to establish the correspondence between table aliases in the SQL statement and source table names. The subquery identifier variables are used to mark whether each table in the SQL statement is a temporary table generated by a subquery.
[0030] By integrating multiple variables, the logical scope of the insertion table is obtained. The logical scope is a structured information set with the insertion table as the core, including the table alias of the insertion table, the source table name, the processing logic of each field, and the source table name that the field depends on. If there are multiple versions of the logical scope in the same insertion table, the multiple versions of the logical scope are merged to obtain the unified logical scope of the corresponding insertion table.
[0031] When a temporary table exists in the preprocessed SQL statement represented by the subquery identifier variable, based on the unified logical caliber of the result table, the intermediate temporary table in the preprocessed SQL statement is penetrated layer by layer through recursion. The field processing logic of the intermediate temporary table in the table processing logic variable and the source table name corresponding to the intermediate temporary table in the table alias variable are retrieved and merged into the unified logical caliber of the result table.
[0032] The unified logical standard of each result table will be output to the specified file according to the preset format.
[0033] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, performs the following steps:
[0034] Preprocess the SQL statement by removing comment text from it;
[0035] By analyzing the preprocessed SQL statement, several variables are obtained, including table processing logic variables, table alias variables, and subquery identifier variables. The table processing logic variables are used to record the field processing logic of each table in the SQL statement. The table alias variables are used to establish the correspondence between table aliases in the SQL statement and source table names. The subquery identifier variables are used to mark whether each table in the SQL statement is a temporary table generated by a subquery.
[0036] By integrating multiple variables, the logical scope of the insertion table is obtained. The logical scope is a structured information set with the insertion table as the core, including the table alias of the insertion table, the source table name, the processing logic of each field, and the source table name that the field depends on. If there are multiple versions of the logical scope in the same insertion table, the multiple versions of the logical scope are merged to obtain the unified logical scope of the corresponding insertion table.
[0037] When a temporary table exists in the preprocessed SQL statement represented by the subquery identifier variable, based on the unified logical caliber of the result table, the intermediate temporary table in the preprocessed SQL statement is penetrated layer by layer through recursion. The field processing logic of the intermediate temporary table in the table processing logic variable and the source table name corresponding to the intermediate temporary table in the table alias variable are retrieved and merged into the unified logical caliber of the result table.
[0038] The unified logical standard of each result table will be output to the specified file according to the preset format.
[0039] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, performs the following steps:
[0040] Preprocess the SQL statement by removing comment text from it;
[0041] By analyzing the preprocessed SQL statement, several variables are obtained, including table processing logic variables, table alias variables, and subquery identifier variables. The table processing logic variables are used to record the field processing logic of each table in the SQL statement. The table alias variables are used to establish the correspondence between table aliases in the SQL statement and source table names. The subquery identifier variables are used to mark whether each table in the SQL statement is a temporary table generated by a subquery.
[0042] By integrating multiple variables, the logical scope of the insertion table is obtained. The logical scope is a structured information set with the insertion table as the core, including the table alias of the insertion table, the source table name, the processing logic of each field, and the source table name that the field depends on. If there are multiple versions of the logical scope in the same insertion table, the multiple versions of the logical scope are merged to obtain the unified logical scope of the corresponding insertion table.
[0043] When a temporary table exists in the preprocessed SQL statement represented by the subquery identifier variable, based on the unified logical caliber of the result table, the intermediate temporary table in the preprocessed SQL statement is penetrated layer by layer through recursion. The field processing logic of the intermediate temporary table in the table processing logic variable and the source table name corresponding to the intermediate temporary table in the table alias variable are retrieved and merged into the unified logical caliber of the result table.
[0044] The unified logical standard of each result table will be output to the specified file according to the preset format.
[0045] The aforementioned reverse parsing methods, devices, computer equipment, computer-readable storage media, and computer program products for SQL statements automatically remove comments, extract logic, and integrate definitions, eliminating reliance on manual tracing, reducing error rates, and improving tracing efficiency. They support complex SQL parsing including subqueries and temporary tables, recursively penetrating intermediate temporary tables layer by layer, automatically integrating multi-layered dependent source tables and processing logic, shortening the tracing cycle. Multiple versions of logical definitions for the same inserted table are automatically merged, avoiding repeated tracing due to frequent definition changes and reducing manpower consumption caused by high-frequency regulatory definition adjustments. The unified logical definitions of each result table are output to a specified file according to a preset format, improving the standardization and reusability of definition files. Attached Figure Description
[0046] To more clearly illustrate the technical solutions in the embodiments of this application or related technologies, the drawings used in the description of the embodiments of this application or related technologies will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.
[0047] Figure 1 This is a diagram illustrating the application environment of a reverse parsing method for SQL statements in one embodiment.
[0048] Figure 2 This is a flowchart illustrating the reverse parsing method for SQL statements in one embodiment;
[0049] Figure 3 This is a structural block diagram of a reverse parsing device for SQL statements in one embodiment;
[0050] Figure 4 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation
[0051] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.
[0052] It should be noted that the terms "first," "second," etc., used in this application can be used to describe various elements, but these elements are not limited by these terms. These terms are only used to distinguish the first element from the second element. The terms "comprising" and "having," and any variations thereof, used in this application, are intended to cover non-exclusive inclusion. The term "multiple" used in this application refers to two or more. The term "and / or" used in this application refers to one of the embodiments, or any combination of multiple embodiments.
[0053] The reverse parsing method for SQL statements provided in this application can be applied to, for example... Figure 1In the application environment shown, terminal 102 communicates with server 104 via a network. A data storage system can store the data that server 104 needs to process. The data storage system can be integrated on server 104 or placed on a cloud or other network server. This embodiment uses the method applied to a terminal as an example; it is understood that this method can also be applied to a server, and can also be applied to a system including both a terminal and a server, and implemented through the interaction between the terminal and the server. Terminal 102 preprocesses the SQL statement, removing comment text from the SQL statement; by analyzing the preprocessed SQL statement, multiple variables are obtained, including table processing logic variables, table alias variables, and subquery identifier variables; the table processing logic variables are used to record the field processing logic of each table in the SQL statement; the table alias variables are used to establish the correspondence between table aliases and source table names in the SQL statement; the subquery identifier variables are used to mark whether each table in the SQL statement is a temporary table generated by a subquery; the multiple variables are integrated to obtain the logical scope of the inserted table, which is a structured information set centered on the inserted table, including the table alias of the inserted table and the source table name. The system includes: processing logic for each field and the source table names to which the fields depend; merging multiple versions of the logical approach in the same inserted table to obtain a unified logical approach for the corresponding inserted table; when a temporary table exists in the preprocessed SQL statement represented by the subquery identifier variable, recursively penetrating the intermediate temporary tables in the preprocessed SQL statement based on the unified logical approach of the result table, retrieving the field processing logic of the intermediate temporary table in the table processing logic variable and the source table name corresponding to the intermediate temporary table in the table alias variable, and merging them into the unified logical approach of the result table; and outputting the unified logical approach of each result table to a specified file according to a preset format. Terminal 102 can be, but is not limited to, various personal computers, laptops, smartphones, tablets, drones, low-altitude aircraft, IoT devices, and portable wearable devices. IoT devices can include smart speakers, smart TVs, smart air conditioners, smart in-vehicle devices, projection devices, etc. Portable wearable devices can include smartwatches, smart bracelets, head-mounted devices, etc. Headset devices can be virtual reality (VR) devices, augmented reality (AR) devices, smart glasses, etc. Server 104 can be a standalone physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing cloud computing services.
[0054] In one exemplary embodiment, such as Figure 2 As shown, a method for reverse parsing SQL statements is provided, which can be applied to... Figure 1Taking terminal 102 as an example, the explanation includes the following steps 201 to 205. Wherein:
[0055] Step 201: Preprocess the SQL statement by removing comment text from it.
[0056] SQL statements are execution instructions using the Structured Query Language (SCL) for database operations such as querying, inserting, updating, and deleting. In financial regulatory reporting scenarios, SQL statements are SQL code used for data processing and statistical analysis, such as query statements containing field calculations, table joins, and grouping aggregations, and are the core object of source tracing analysis.
[0057] Preprocessing refers to the pre-processing of data cleaning for SQL tracing, aiming to eliminate interfering factors and standardize data format. Comment text refers to descriptive text within SQL statements, used to explain code functionality, logical background, etc., and does not participate in database execution. The terminal identifies and removes comment text from SQL statements, replacing it with newline characters. Preprocessing improves the efficiency of SQL statement tracing.
[0058] Step 202: By analyzing the preprocessed SQL statement, multiple variables are obtained, including table processing logic variables, table alias variables, and subquery identifier variables. The table processing logic variables are used to record the field processing logic of each table in the SQL statement. The table alias variables are used to establish the correspondence between table aliases and source table names in the SQL statement. The subquery identifier variables are used to mark whether each table in the SQL statement is a temporary table generated by a subquery.
[0059] The analysis of the preprocessed SQL statements mainly involves extracting core semantic information from the code based on SQL syntax rules (such as keyword recognition and syntax tree parsing) to obtain multiple variables.
[0060] Each variable corresponds to a type of core semantic information. Among them, table processing logic variables are data structures that record the table processing logic in SQL, including data structures for processing details such as field sources, calculation rules, and filtering conditions. For example, the table processing logic is: CASE WHEN (T1.pd_flg='1' OR T1.lgd_flg='1' OR T1.ccf_flg='1') THEN 'AAA' WHENT3.ote_flg='1' THEN 'BBB' ELSE 'CCC' END AS risk_type.
[0061] Table alias variables are variables that store the aliases for tables in SQL queries. There is a correspondence between table aliases and source table names. For example, table alias T1 corresponds to the source table name src_table1; table alias T3 corresponds to the source table name src_table3.
[0062] A subquery is a secondary query nested within a main query, usually enclosed in parentheses. Its execution result serves as the data source for the main query (e.g., a query in the FROM clause). Subquery identifier variables are state variables that indicate whether each table in the SQL statement is a temporary table generated by the subquery. A temporary table is a virtual data table generated after the subquery is executed; it exists only during the current SQL execution (not stored in the database) and is used to hold intermediate calculation results.
[0063] Step 203: Integrate multiple variables to obtain the logical scope of the insertion table. The logical scope is a structured information set with the insertion table as the core, including the table alias of the insertion table, the source table name, the processing logic of each field, and the source table name that the field depends on. If there are multiple versions of the logical scope in the same insertion table, merge the multiple versions of the logical scope to obtain the unified logical scope of the corresponding insertion table.
[0064] The terminal cross-references, completes, and restructures information from multiple variables to eliminate information fragmentation and form an insertion table.
[0065] The source table name that a field depends on refers to the name of the original data table referenced by the field's processing logic. This may be a source table that is directly or indirectly related. The logical scope refers to the integrated structured information set stored in the inserted table, which fully records the table alias of the inserted table, the source table name, the processing logic of each field, and the source table name that the field depends on.
[0066] Due to regulatory policy adjustments, changes in business requirements, and other reasons, the same insert table may have multiple versions with different logical interpretations. The core differences may lie in field processing rules and changes in dependent source tables. To address this, the multiple versions of the same insert table's logical interpretations are uniformly processed according to preset rules, eliminating version conflicts and forming a unique standardized interpretation. For example, this might involve retaining the latest rules, integrating all dependent source tables, and annotating version differences.
[0067] Step 204: If there is a temporary table in the preprocessed SQL statement represented by the subquery identifier variable, based on the unified logical caliber of the result table, the intermediate temporary table in the preprocessed SQL statement is penetrated layer by layer through recursion. The field processing logic of the intermediate temporary table in the table processing logic variable and the source table name corresponding to the intermediate temporary table in the table alias variable are retrieved and merged into the unified logical caliber of the result table.
[0068] The result table is the target carrier of the final output data after the SQL statement is executed. It is the endpoint of data traceability and the direct object used for regulatory reporting and business analysis.
[0069] When the subquery identifier variable represents the existence of a temporary table, the intermediate temporary tables are traversed layer by layer through recursion. That is, according to the nesting level of the temporary tables (from the outside to the inside or from the inside to the outside), the field processing logic and dependent source tables of each intermediate temporary table are parsed in turn, without missing any level of nesting.
[0070] The field processing logic of the temporary table obtained through penetration, the temporary table's dependency on the source table, and the nested links of the temporary table are added to the basic unified logic of the result table to obtain the final result table.
[0071] Step 205: Output the unified logical caliber of each result table to the specified file according to the preset format.
[0072] The system organizes and outputs the results tables to a designated file according to a pre-defined format, ensuring a consistent logical framework. This process specifies the file path, file name, and file format, facilitating subsequent review, archiving, and submission to regulatory agencies.
[0073] The above-mentioned reverse parsing method for SQL statements automatically removes comments, extracts logic, and integrates definitions, eliminating the reliance on manual tracing, reducing error rates, and improving tracing efficiency. It supports complex SQL parsing including subqueries and temporary tables, recursively penetrating intermediate temporary tables layer by layer to automatically integrate multi-layered dependent source tables and processing logic, shortening the tracing cycle. Multiple versions of logical definitions for the same inserted table are automatically merged, avoiding repeated tracing due to frequent definition changes and reducing manpower consumption caused by frequent regulatory definition adjustments. The unified logical definitions of each result table are output to a specified file according to a preset format, improving the standardization and reusability of the definition files. It can accurately locate the source tables and fields dependent on the SQL statement, quickly performing reverse tracing of the SQL statement, thereby improving development and debugging efficiency. It has strong scalability and can be easily modified and extended according to specific needs.
[0074] In an exemplary embodiment, multiple variables are obtained by analyzing the preprocessed SQL statement, including: locating the syntax identifiers in the preprocessed SQL statement through lexical analysis, and obtaining the source table name, table alias, and field names based on the syntax identifiers; identifying the temporary table name in the preprocessed SQL statement through syntax analysis, and identifying the field processing logic and the correspondence between the table alias and the source table name based on the bracket hierarchy structure in the preprocessed SQL statement, so as to obtain multiple variables.
[0075] Lexical analysis refers to breaking down the preprocessed SQL statement into the smallest semantic units according to lexical rules, identifying the type of each unit, obtaining table aliases and field names, and obtaining the source table name based on the correspondence between the table aliases and the source table name.
[0076] Syntax analysis refers to the process of analyzing the syntactic structure and semantic relationships of SQL based on SQL syntax rules (such as statement structure, bracket levels, and keyword collocation) to identify complex logic. Syntax analysis can be used to identify temporary table names.
[0077] Parentheses hierarchy refers to the nested relationship of parentheses (such as inner parentheses and outer parentheses) used to enclose subqueries, function parameters, etc. in SQL statements. Parentheses hierarchy allows you to identify field processing logic and the correspondence between table aliases and source table names.
[0078] In this embodiment, lexical analysis and syntax analysis work together to accurately locate basic elements such as source table name, table alias, and field name. At the same time, based on the bracket hierarchy structure, it identifies temporary table name, field processing logic, and the mapping relationship between table alias and source table name, avoiding logical omissions or identification errors during manual parsing and ensuring the completeness and accuracy of extracted variable information. For complex SQL statements in financial regulatory reports containing multi-level subqueries, nested condition judgments, and multi-table joins, it improves the adaptability of complex SQL statement parsing.
[0079] In an exemplary embodiment, multiple versions of logical calibers are merged to obtain a unified logical caliber for the corresponding insertion table. This includes: for the table processing logical variables in different versions of logical calibers, traversing all field processing logics of the same insertion table in different table processing logical variables, deleting duplicate field processing logics; comparing the source table names of the corresponding insertion tables in different table alias variables, supplementing the corresponding source table names for the field processing logics associated with missing source table names based on the comparison results, and updating the corresponding table processing logical variables after completion.
[0080] For different versions of the same inserted table, duplicate field processing logic should be deleted, and only one unique field processing logic for the same field should be retained to eliminate redundancy.
[0081] Table aliases are often used in field processing logic. By querying the correspondence between table aliases and source table names, the corresponding source table name can be determined. When the source table name is missing, the queried source table name can be added to the unified logical scope to obtain the unified logical scope for the corresponding inserted table.
[0082] In this embodiment, by deleting duplicate processing logic for the same field, misunderstandings among business personnel or confusion in regulatory verification caused by multiple duplicate interpretations of the same field are avoided, ensuring the uniqueness of the interpretation. At the same time, the redundant costs of subsequent interpretation storage and maintenance are reduced. Missing source table names are filled in to ensure that the processing logic of each field and the source table are completely consistent, meeting the requirements of full-chain traceability of regulatory data.
[0083] In an exemplary embodiment, based on the unified logical scope of the result table, intermediate temporary tables in the preprocessed SQL statement are recursively accessed layer by layer. The field processing logic of the intermediate temporary tables in the table processing logic variables and the source table names corresponding to the intermediate temporary tables in the table alias variables are retrieved and merged into the unified logical scope of the result table. This includes: filtering out intermediate temporary table names associated with the result table from the subquery identifier variables in the unified logical scope; retrieving the field processing logic of the intermediate temporary tables corresponding to the intermediate temporary table names from the table processing logic variables in the unified logical scope, and retrieving the source table names corresponding to the intermediate temporary tables from the table alias variables in the unified logical scope; modifying the field processing logic based on the source table names to change the data source of the field processing logic from the intermediate temporary tables to the source tables; obtaining the unified logical scope of the result table through the above process if it is determined based on the subquery identifier variables that no nested temporary tables associated with the intermediate temporary tables exist; repeating the above process until it is determined based on the subquery identifier variables that no nested temporary tables associated with the intermediate temporary tables exist.
[0084] The process involves several steps. First, from the subquery identifier variables in the unified logical caliber, the names of intermediate temporary tables associated with the result table are selected. Then, the field processing logic of the intermediate temporary table is retrieved from the table processing logic variables, and the corresponding source table name is retrieved from the table alias variables. This changes the data source of the field processing logic to the source table name. If no nested temporary tables exist within the intermediate temporary tables, the unified logical caliber of the result table is directly obtained. If nested temporary tables exist, the process continues recursively, penetrating layer by layer. Specifically, from the subquery identifier variables in the unified logical caliber, the names of intermediate temporary tables associated with the result table are selected. From the table processing logic variables in the unified logical caliber, the field processing logic of the intermediate temporary table corresponding to the intermediate temporary table name is retrieved. From the table alias variables in the unified logical caliber, the corresponding source table name is retrieved. Based on the source table name, the field processing logic is modified to change the data source of the field processing logic from the intermediate temporary table to the source table. This process continues until it is determined, based on the subquery identifier variables, that no nested temporary tables associated with the intermediate temporary tables exist, thus obtaining the unified logical caliber of the result table.
[0085] In this embodiment, intermediate temporary tables and nested temporary tables are decomposed layer by layer in a recursive manner, and the indirect dependencies of the result table fields on the temporary tables are transformed into direct dependencies on the original source tables. This solves the pain point that traditional manual parsing is difficult to penetrate multiple nested layers, resulting in unclear data sources, and meets the core compliance requirements of regulatory agencies for traceable data sources and traceable processing.
[0086] In one exemplary embodiment, the preset format includes the source task number of the SQL statement associated with the file name, the file path is set hierarchically according to the batch of regulatory reports submitted by financial institutions, and the source table name, field name and field processing logic are displayed in column alignment within the file.
[0087] When outputting the unified logical standards of each result table according to the preset format, the output format specifications are defined in advance and standardized, covering three dimensions: file naming, file path, and data display within the file, to ensure that the output files are unified, standardized, traceable, and easy to view.
[0088] Specifically, file naming should be associated with the source task number of the SQL statement, file paths should be hierarchically set according to the batches of regulatory reports submitted by financial institutions, and data display within the file should show the source table name, field name, and field processing logic in column-aligned format. The resulting specified file structure is well-organized and the data is aligned, improving the efficiency of file management and cross-functional collaboration. Through standardized and traceable output, it meets regulatory compliance requirements and ultimately provides efficient and standardized support for the archiving, verification, and reuse of regulatory reporting standards by financial institutions.
[0089] In an exemplary embodiment, the field processing logic part within the file retains the content recorded in the table processing logic variables, including the original field operators, multi-table join conditions, and data filtering conditions in the SQL statement; the hierarchical setting of the file path is specifically as follows: the first-level directory is named the batch number of the financial institution's regulatory report, the second-level directory is named the SQL statement tracing task type, and the third-level directory is named the tracing execution date.
[0090] Among them, the module in the designated file that specifically displays the field processing logic shows the contents recorded in the table processing logic variables, including the original field operators, multi-table join conditions and data filtering conditions in the SQL statement, without omitting or modifying the core syntax elements in the SQL, ensuring consistency with the original SQL logic.
[0091] The file paths of specified files are standardized using a multi-level directory structure, with each level corresponding to a core classification dimension to ensure orderly storage. The first-level directory is named after the batch number of the financial institution's regulatory report, used to distinguish documents from different reporting periods. The second-level directory is named after the SQL statement tracing task type, used to distinguish documents from different business scenarios within the same batch. The third-level directory is named after the tracing execution date, used to distinguish tracing results executed at different times within the same business type. This three-level directory structure provides clear hierarchy, aligns with business scenarios, and avoids file loss or difficulty in retrieval due to chaotic path naming, making it particularly suitable for managing large batches and multiple types of tracing tasks.
[0092] It should be understood that although the steps in the flowcharts of the embodiments described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the embodiments described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages in other steps. It is understood that the steps in different embodiments can be freely combined as needed, and all non-contradictory solutions formed by such combinations are within the scope of protection of this application.
[0093] Based on the same inventive concept, this application also provides an SQL statement reverse parsing apparatus for implementing the SQL statement reverse parsing method described above. The solution provided by this apparatus is similar to the implementation described in the above method; therefore, the specific limitations of one or more SQL statement reverse parsing apparatus embodiments provided below can be found in the limitations of the SQL statement reverse parsing method described above, and will not be repeated here.
[0094] In one exemplary embodiment, such as Figure 3 As shown, a reverse parsing device 300 for SQL statements is provided, including: a preprocessing module 310, an analysis module 320, an integration module 330, a processing module 340, and an output module 350, wherein:
[0095] The preprocessing module 310 is used to preprocess the SQL statement and remove comment text from the SQL statement;
[0096] The analysis module 320 is used to analyze the preprocessed SQL statement to obtain multiple variables, including table processing logic variables, table alias variables, and subquery identifier variables. The table processing logic variables are used to record the field processing logic of each table in the SQL statement. The table alias variables are used to establish the correspondence between table aliases in the SQL statement and source table names. The subquery identifier variables are used to mark whether each table in the SQL statement is a temporary table generated by a subquery.
[0097] Integration module 330 is used to integrate the multiple variables to obtain the logical scope of the insertion table. The logical scope is a structured information set with the insertion table as the core, including the table alias of the insertion table, the source table name, the processing logic of each field, and the source table name on which the field depends. When there are multiple versions of the logical scope in the same insertion table, the multiple versions of the logical scope are merged to obtain the unified logical scope of the corresponding insertion table.
[0098] The processing module 340 is used to, when there is a temporary table in the preprocessed SQL statement represented by the subquery identifier variable, recursively penetrate the intermediate temporary table in the preprocessed SQL statement layer by layer based on the unified logical caliber of the result table, retrieve the field processing logic of the intermediate temporary table in the table processing logic variable and the source table name corresponding to the intermediate temporary table in the table alias variable, and merge them into the unified logical caliber of the result table.
[0099] The output module 350 is used to output the unified logical standard of each result table to a specified file according to a preset format.
[0100] The reverse parsing device for the aforementioned SQL statements automatically removes comments, extracts logic, and integrates definitions, eliminating reliance on manual tracing, reducing error rates, and improving tracing efficiency. It supports the parsing of complex SQL statements containing subqueries and temporary tables, recursively penetrating intermediate temporary tables layer by layer, automatically integrating multi-layered dependent source tables and processing logic, and shortening the tracing cycle. Multiple versions of logical definitions for the same inserted table are automatically merged, avoiding repeated tracing due to frequent definition changes and reducing manpower consumption caused by high-frequency regulatory definition adjustments. The unified logical definitions of each result table are output to a specified file according to a preset format, improving the standardization and reusability of definition files.
[0101] In one embodiment, by analyzing the preprocessed SQL statement, multiple variables are obtained. The analysis module 320 is also used to: locate the syntax identifiers in the preprocessed SQL statement through lexical analysis, and obtain the source table name, table alias, and field name based on the syntax identifiers; identify the temporary table name in the preprocessed SQL statement through syntax analysis, and identify the field processing logic and the correspondence between the table alias and the source table name based on the bracket hierarchy structure in the preprocessed SQL statement, so as to obtain multiple variables.
[0102] In one embodiment, multiple versions of logical definitions are merged to obtain a unified logical definition for the corresponding insertion table. The integration module 330 is also used to: for the table processing logical variables in different versions of logical definitions, traverse all field processing logics of the same insertion table in different table processing logical variables, delete duplicate field processing logics; compare the source table name of the corresponding insertion table in different table alias variables, supplement the corresponding source table name for the field processing logic associated with the missing source table name based on the comparison result, and update the corresponding table processing logical variables after completion.
[0103] In one embodiment, based on the unified logical scope of the result table, the intermediate temporary tables in the preprocessed SQL statement are recursively accessed layer by layer. The field processing logic of the intermediate temporary tables in the table processing logic variables and the source table names corresponding to the intermediate temporary tables in the table alias variables are retrieved and merged into the unified logical scope of the result table. The processing module 340 is further configured to: filter out the names of intermediate temporary tables associated with the result table from the subquery identifier variables in the unified logical scope; and retrieve the field processing logic of the intermediate temporary tables corresponding to the intermediate temporary table names from the table processing logic variables in the unified logical scope. The logic involves retrieving the source table name corresponding to the intermediate temporary table from the table alias variable in the unified logical scope; based on the source table name, modifying the field processing logic to change the data source of the field processing logic from the intermediate temporary table to the source table; if it is determined based on the subquery identifier variable that there is no nested temporary table associated with the intermediate temporary table, the unified logical scope of the result table is obtained through the above process; if it is determined based on the subquery identifier variable that there is a nested temporary table associated with the intermediate temporary table, the above process is repeated until it is determined based on the subquery identifier variable that there is no nested temporary table associated with the intermediate temporary table.
[0104] In one embodiment, the preset format includes the source task number of the SQL statement associated with the file name, the file path is set hierarchically according to the batch of regulatory reports submitted by financial institutions, and the source table name, field name and field processing logic are displayed in column alignment within the file.
[0105] In one embodiment, the field processing logic part within the file retains the content recorded in the table processing logic variables, including the original field operators, multi-table join conditions, and data filtering conditions in the SQL statement; the hierarchical setting of the file path is specifically as follows: the first-level directory is named the batch number of the financial institution's regulatory report, the second-level directory is named the SQL statement tracing task type, and the third-level directory is named the tracing execution date.
[0106] The modules in the SQL statement reverse parsing device described above can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in the processor of a computer device in hardware form or independent of it, or stored in the memory of the computer device in software form, so that the processor can call and execute the operations corresponding to each module.
[0107] In one exemplary embodiment, a computer device is provided, which may be a terminal, and its internal structure diagram may be as follows: Figure 4 As shown, the computer device includes a processor, memory, input / output interfaces, a communication interface, a display unit, and an input device. The processor, memory, and input / output interfaces are connected via a system bus, and the communication interface, display unit, and input device are also connected to the system bus via the input / output interfaces. The processor provides computing and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The input / output interfaces are used for exchanging information between the processor and external devices. The communication interface is used for wired or wireless communication with external terminals; wireless communication can be achieved through Wi-Fi, mobile cellular networks, Near Field Communication (NFC), or other technologies. When executed by the processor, the computer program implements a method for reverse parsing SQL statements. The display unit is used to form a visually visible image and can be a display screen, a projection device, or a virtual reality imaging device. The display screen can be an LCD screen or an e-ink screen. The input device of the computer device can be a touch layer covering the display screen, or buttons, trackballs, or touchpads set on the casing of the computer device, or external keyboards, touchpads, or mice, etc.
[0108] Those skilled in the art will understand that Figure 4 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.
[0109] In one exemplary embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the steps in the above-described method embodiments.
[0110] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon that, when executed by a processor, implements the steps in the above method embodiments.
[0111] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the steps in the above method embodiments.
[0112] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of the relevant data must comply with relevant regulations.
[0113] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited to these.
[0114] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.
[0115] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of this patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.
Claims
1. A method for reverse parsing SQL statements, characterized in that, The method includes: Preprocess the SQL statement by removing comment text from it; By analyzing the preprocessed SQL statement, several variables are obtained, including table processing logic variables, table alias variables, and subquery identifier variables. The table processing logic variables are used to record the field processing logic of each table in the SQL statement. The table alias variables are used to establish the correspondence between table aliases in the SQL statement and source table names. The subquery identifier variables are used to mark whether each table in the SQL statement is a temporary table generated by a subquery. The multiple variables are integrated to obtain the logical scope of the insertion table. The logical scope is a structured information set with the insertion table as the core, including the table alias of the insertion table, the source table name, the processing logic of each field, and the source table name that the field depends on. When there are multiple versions of the logical scope in the same insertion table, the multiple versions of the logical scope are merged to obtain the unified logical scope of the corresponding insertion table. When the subquery identifier variable represents a temporary table in the preprocessed SQL statement, based on the unified logical caliber of the result table, the intermediate temporary table in the preprocessed SQL statement is penetrated layer by layer through recursion. The field processing logic of the intermediate temporary table in the table processing logic variable and the source table name corresponding to the intermediate temporary table in the table alias variable are retrieved and merged into the unified logical caliber of the result table. The unified logical standard of each result table will be output to the specified file according to the preset format.
2. The method according to claim 1, characterized in that, The analysis of the preprocessed SQL statement yields several variables, including: Lexical analysis is used to locate the syntax identifiers in the preprocessed SQL statement, and the source table name, table alias, and field names are obtained based on the syntax identifiers. Syntax analysis is used to identify the temporary table name in the preprocessed SQL statement, and based on the bracket hierarchy in the preprocessed SQL statement, the field processing logic and the correspondence between the table alias and the source table name are identified to obtain multiple variables.
3. The method according to claim 1, characterized in that, The process of merging multiple versions of the logical scope to obtain a unified logical scope for the corresponding insertion table includes: For the table processing logic variables in different versions of the logical caliber, iterate through all field processing logic of the same inserted table in different table processing logic variables, and delete duplicate field processing logic; Compare the source table names of the corresponding inserted tables in different table alias variables, and based on the comparison results, supplement the corresponding source table names for the processing logic of fields associated with missing source table names. After completion, update the corresponding table processing logic variables.
4. The method according to claim 1, characterized in that, The unified logical approach based on the result table recursively penetrates the intermediate temporary tables in the preprocessed SQL statement, retrieves the field processing logic of the intermediate temporary tables in the table processing logic variables and the source table names corresponding to the intermediate temporary tables in the table alias variables, and merges them into the unified logical approach of the result table, including: From the subquery identifier variables in the unified logical framework, filter out the names of intermediate temporary tables that are related to the result table; From the table processing logic variables in the unified logic scope, retrieve the field processing logic of the intermediate temporary table corresponding to the intermediate temporary table name, and retrieve the source table name corresponding to the intermediate temporary table from the table alias variables in the unified logic scope; based on the source table name, modify the field processing logic to change the data source of the field processing logic from the intermediate temporary table to the source table. If, based on the subquery identifier variable, it is determined that there is no nested temporary table associated with the intermediate temporary table, the unified logical standard of the result table is obtained through the above process; if, based on the subquery identifier variable, it is determined that there is a nested temporary table associated with the intermediate temporary table, the above process is repeated until, based on the subquery identifier variable, it is determined that there is no nested temporary table associated with the intermediate temporary table.
5. The method according to claim 1, characterized in that, The preset format includes file naming associated with the source task number of the SQL statement, file paths set hierarchically according to the batches of regulatory reports submitted by financial institutions, and the source table name, field name and field processing logic displayed in column alignment within the file.
6. The method according to claim 5, characterized in that, The field processing logic section within the file retains the content recorded in the table processing logic variables, including the original field operators, multi-table join conditions, and data filtering conditions in the SQL statement; the hierarchical setting of the file path is as follows: the first-level directory is named the batch number of the financial institution's regulatory report, the second-level directory is named the type of the SQL statement's source tracing task, and the third-level directory is named the source tracing execution date.
7. A reverse parsing device for SQL statements, characterized in that, The device includes: The preprocessing module is used to preprocess SQL statements and remove comment text from the SQL statements; The analysis module is used to analyze the preprocessed SQL statement to obtain multiple variables, including table processing logic variables, table alias variables, and subquery identifier variables. The table processing logic variables are used to record the field processing logic of each table in the SQL statement. The table alias variables are used to establish the correspondence between table aliases in the SQL statement and source table names. The subquery identifier variables are used to mark whether each table in the SQL statement is a temporary table generated by a subquery. The integration module is used to integrate the multiple variables to obtain the logical scope of the insertion table. The logical scope is a structured information set with the insertion table as the core, including the table alias of the insertion table, the source table name, the processing logic of each field, and the source table name that the field depends on. When there are multiple versions of the logical scope in the same insertion table, the multiple versions of the logical scope are merged to obtain the unified logical scope of the corresponding insertion table. The processing module is used to, when there is a temporary table in the preprocessed SQL statement represented by the subquery identifier variable, recursively penetrate the intermediate temporary table in the preprocessed SQL statement layer by layer based on the unified logical caliber of the result table, retrieve the field processing logic of the intermediate temporary table in the table processing logic variable and the source table name corresponding to the intermediate temporary table in the table alias variable, and merge them into the unified logical caliber of the result table. The output module is used to output the unified logical standard of each result table to a specified file according to a preset format.
8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method according to any one of claims 1 to 6.
9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.
10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.