Program, computer, and information processing method

Abstract

To provide a program, a system and an information processing method for enabling a programmer or the like to preliminarily recognize the existence of an error or an anti-pattern to be generated in the execution of a program without executing a program written in an SQL sentence.SOLUTION: By the control unit 12, 52 executing the program, detecting a predetermined data manipulation language set in advance from the received SQL statement and decomposing a statement including the data manipulation language into constituent elements; Which table is being processed is specified from the decomposed components, a field of an innermost table is specified by analyzing a multilayered data manipulation language for the received SQL statement, and at least one of verification of a comparison operation and verification of an assignment operation is performed or an anti-pattern is detected for the specified field of the innermost table based on the acquired definition information.SELECTED DRAWING: Figure 1

Description

[Technical Field]

[0001] The present disclosure relates to a program, a computer, and an information processing method related to analyzing an SQL statement. [Background technology]

[0002] Various techniques have been proposed as methods for analyzing SQL statements and detecting errors. For example, Patent Document 1 discloses a coding check support system that executes SQL statement matching processing using SQL statement influence pattern data and influence patterns recorded in a learning data storage unit, and records error information in memory when a match is found. [Prior art documents] [Patent documents]

[0003] [Patent Document 1] Japanese Patent Application Laid-Open No. 2009-258913 Summary of the Invention [Problem to be solved by the invention]

[0004] Programs written in SQL statements are generally compiled to detect errors. However, even if no errors are found by compiling the program, it may be discovered that errors or anti-patterns occur when the program is executed depending on the table data handled or various external prerequisites.

[0005] The present disclosure has been made in consideration of these points, and aims to provide a program, system, and information processing method that allows programmers and others to recognize in advance when errors or anti-patterns occur during program execution, without having to execute a program written in SQL statements. [Means for solving the problem]

[0006] The program of the present disclosure is A program executed by a control unit of a computer, When the control unit executes the program, Accepts SQL statements, Detecting a predetermined data manipulation language from the received SQL statement, and decomposing the statement into constituent elements including the data manipulation language; Identify which table is being processed from the decomposed components, Obtain definition information of columns of a table to be processed by the data manipulation language; Identifying a field of the innermost table by analyzing the multi-layered data manipulation language of the received SQL statement; With respect to the identified field of the innermost table, based on the acquired definition information, at least one of verifying a comparison operation of a first statement including a first specified language in the received SQL statement and verifying an assignment operation of a second statement including a second specified language, or detecting an anti-pattern; If the verification of the comparison operation, the verification of the assignment process, or the detection of the anti-pattern is determined to be abnormal, a warning is issued.

[0007] In the program of the present disclosure, The predetermined data manipulation language may be at least one of SELECT, UPDATE, INSERT, and DELETE.

[0008] The first specific language may be at least one of WHERE, CASE, and JOIN.

[0009] The second specific language may be at least one of INSERT and UPDATE.

[0010] Furthermore, when the control unit executes the program, The received SQL statement and the warning information to be notified may be displayed on a display unit of a client terminal.

[0011] Furthermore, information regarding which table from the decomposed components is being processed may be associated with the data manipulation language and displayed on the display unit of the client terminal.

[0012] Furthermore, when the warning information displayed on the display unit of the client terminal is specified, the parts of the SQL statement displayed on the display unit that are determined to be abnormal in the verification of comparison operations, verification of assignment processing, or detection of anti-patterns may be shown.

[0013] Furthermore, when a phrase corresponding to a field of the table in the SQL statement displayed on the display unit of the client terminal is specified, information about the innermost table related to the phrase may be displayed on the display unit.

[0014] The computer of the present disclosure includes: A computer having a control unit, When the control unit executes a program, Accepts SQL statements, Detecting a predetermined data manipulation language from the received SQL statement, and decomposing the statement into constituent elements including the data manipulation language; Identify which table is being processed from the decomposed components, Obtain definition information of columns of a table to be processed by the data manipulation language; Identifying a field of the innermost table by analyzing the multi-layered data manipulation language of the received SQL statement; With respect to the identified field of the innermost table, based on the acquired definition information, at least one of verifying a comparison operation of a first statement including a first specified language in the received SQL statement and verifying an assignment operation of a second statement including a second specified language, or detecting an anti-pattern; If the verification of the comparison operation, the verification of the assignment process, or the detection of the anti-pattern is determined to be abnormal, a warning is issued.

[0015] The information processing method of the present disclosure includes: An information processing method executed by a computer having a control unit, a step of the control unit receiving an SQL statement; a step of detecting a predetermined data manipulation language set in advance from the SQL statement received by the control unit and decomposing the statement into constituent elements including the data manipulation language; a step of identifying which table the control unit is processing from the decomposed components; a step in which the control unit acquires definition information of columns of a table to be processed by the data manipulation language; a step of identifying a field of the innermost table by analyzing the multi-layered data manipulation language of the SQL statement received by the control unit; a step in which, with respect to the field of the innermost table identified by the control unit, the control unit performs at least one of verifying a comparison operation of a first statement including a first specified language in the received SQL statement and verifying an assignment operation of a second statement including a second specified language, or detecting an anti-pattern, based on the acquired definition information; a step of issuing a warning when the control unit determines that the comparison operation verification, the assignment processing verification, or the anti-pattern detection is abnormal; The present invention is characterized by the following features. [Effects of the Invention]

[0016] According to the program, system, and information processing method disclosed herein, programmers and others can recognize in advance if there are errors or anti-patterns that may occur during program execution, without having to execute a program written in SQL statements. [Brief explanation of the drawings]

[0017] [Figure 1] 1 is a configuration diagram schematically illustrating a configuration of a system according to an embodiment of the present disclosure. [Figure 2] 2 is a configuration diagram showing the configuration of a client terminal in the system shown in FIG. 1. [Figure 3] FIG. 2 is a configuration diagram showing the configuration of a DBMS server in the system shown in FIG. [Figure 4] FIG. 2 is a configuration diagram showing the configuration of a web server in the system shown in FIG. [Figure 5] 1 is a flowchart showing a flow of information processing by a system according to an embodiment of the present disclosure. [Figure 6] 3 is a diagram showing a screen displayed on a display unit of the client terminal shown in FIG. 2. FIG. DETAILED DESCRIPTION OF THE INVENTION

[0018] [Terminology] A DBMS (Database Management System) is software that manages data in tabular format (tables) and stores, retrieves, manipulates, and manages data. DBMSs ensure data integrity, consistency, and persistence, enabling efficient data management. One type of DBMS is the Relational Database Management System (RDBMS). DBMSs use SQL (Structured Query Language). SQL is a standard language used to manipulate and manage data in database management systems (DBMSs). SQL statements are written in SQL. SQL provides a wide range of functions for creating, operating, managing, and controlling databases, and is primarily composed of Data Definition Language (DDL) and Data Manipulation Language (DML). DDL includes commands for defining the structure and schema of a database, such as CREATE, ALTER, and DROP. DML, on the other hand, includes commands for manipulating data in a database, such as SELECT, INSERT, UPDATE, and DELETE.Typical DDL commands include "CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100), position VARCHAR(50), salary DECIMAL(10, 2));", "ALTER TABLE employees ADD COLUMN hire_date DATE;", and "DROP TABLE employees;", while typical DML commands include "INSERT INTO employees (id, name, position, salary) VALUES (1, 'John Doe', 'Manager', 75000.00);", "SELECT * FROM employees WHERE salary > 50000;", "UPDATE employees SET salary = 80000 WHERE id = 1;", and "DELETE FROM employees WHERE id = 1;". DBMS achieves efficient database management and information processing through these commands and functions.

[0019] A parser is generally a function that analyzes a programming language by breaking down written program source into its smallest units (tokens) based on the specifications of that language. Note that whether the parser itself not only breaks down but also detects grammatical errors and runtime errors depends on the functionality of the parser.

[0020] The code model is a model of the code stored in memory after the parser has finished decomposing and analyzing all of the actual physical programs. In other words, it is not the program itself, but a model that abstractly conceptualizes the reference relationships between all tokens in the program.

[0021] Tokens are individual units within SELECT products, amounts, etc.

[0022] An IDE (Integrated Developer Environment) is an integrated development environment that combines functions such as an editor, compiler, and debugger. An IDE integrates the tools necessary for efficient software development into a single interface, allowing developers to easily write, compile, run, debug, and test code. The editor assists in creating and editing source code and provides functions such as syntax highlighting and code completion. The compiler converts the written source code into an executable format, enabling the execution of programs written in SQL statements (hereinafter referred to as SQL statements). The debugger is a tool for detecting and correcting errors that occur during the execution of programs written in SQL statements, and provides functions such as setting breakpoints and monitoring variables. IDEs that integrate these functions provide a consistent user experience throughout the entire development process, helping to improve development efficiency and quickly correct errors.

[0023] In this embodiment, in the assignment process described later, the field to be assigned is called the left side, and the value to be assigned is called the right side.

[0024] [System 1 Configuration] Hereinafter, an embodiment of the present disclosure will be described with reference to the drawings. Figs. 1 to 6 are diagrams showing a system 1 according to this embodiment and the components of this system 1. Of these, Fig. 1 is a configuration diagram that schematically shows the configuration of system 1 according to this embodiment, and Figs. 2 to 4 are configuration diagrams that respectively show the configurations of a client terminal 10, a DBMS server 30, and a web server 50 in system 1 shown in Fig. 1. Also, Fig. 6 is a diagram showing a screen displayed on display unit 14 of client terminal 10 shown in Fig. 2.

[0025] 1, the system 1 of this embodiment is composed of a client terminal 10, a DBMS server 30, and a web server 50. The client terminal 10, the DBMS server 30, and the web server 50 are connected to each other so as to be able to communicate with each other via a communication network 80 such as the Internet.

[0026] [Configuration of client terminal 10] The configuration of the client terminal 10 will be described with reference to Fig. 2. The client terminal 10 includes, but is not limited to, a personal computer, a PC tablet, a smartphone, etc. As shown in Fig. 2, the client terminal 10 has a control unit 12, a display unit 14, an operation unit 15, a storage unit 16, and a communication unit 17. The control unit 12 is connected to each of the display unit 14, the operation unit 15, the storage unit 16, and the communication unit 17 via a bus 19.

[0027] The control unit 12 is composed of a circuit including a CPU and a semiconductor memory, and controls the operation of the client terminal 10 by executing various programs stored in the storage unit 16. The display unit 14 is composed of, for example, a liquid crystal display or the like, and functions as a means for displaying various information. The display unit 14 displays various information in response to instructions from the control unit 12. The operation unit 15 functions as a means for inputting various instructions by the user. For example, a keyboard, a mouse, a touch panel, etc. are used as the operation unit 15. When the operation unit 15 is a touch panel, such a touch panel is superimposed on the display unit 14, and an operation signal is input to the control unit 12 when the user touches the touch panel.

[0028] The storage unit 16 is composed of a hard disk drive (HDD), random access memory (RAM), read only memory (ROM), or solid state drive (SSD), etc. Various programs executed by the control unit 12 are stored in the storage unit 16. The communication unit 17 includes a communication interface that transmits and receives various data between the control unit 12 and external devices such as the DBMS server 30 and web server 50 via a communication network 60.

[0029] [Configuration of DBMS Server 30] The DBMS server 30 is a server system for managing and operating databases using SQL. Specifically, the DBMS server 30 performs database management, query processing, transaction management, user and security management, and backup and recovery. Database management refers to creating, reading, updating, and deleting data (CRUD operations), and also manages database schema definitions and indexes. Query processing refers to receiving, analyzing, optimizing, and executing DBMS queries, generating query execution results, and returning them to the requester (client terminal 10). Transaction management refers to managing multiple database operations as a single transaction and ensuring the ACID (Atomicity, Consistency, Isolation, Durability) characteristics of the transaction. User and security management refers to managing access rights to the database and performing user authentication and access control. Backup and recovery refers to performing periodic database backups and recovering data in the event of a failure. Such a DBMS server 30 may be operated in an on-premise (company-installed) environment, a cloud environment, or a hybrid environment that combines these.

[0030] The configuration of the DBMS server 30 will be described with reference to FIG. 3. As shown in FIG. 3, the DBMS server 30 has a control unit 32, a storage unit 36, and a communication unit 37. The control unit 32 is connected to each of the storage unit 36 ​​and the communication unit 37 via a bus 38. The control unit 32 is composed of a circuit including a CPU and a semiconductor memory, and controls the operation of the DBMS server 30 by executing various programs stored in the storage unit 36. Specifically, the control unit 32 functions as a receiving unit 33 and a transmitting unit 34 by executing various programs stored in the storage unit 36. The receiving unit 33 receives various information from the client terminal 10 via the communication unit 37. The transmitting unit 54 transmits various information and instructions to the client terminal 10 via the communication unit 37.

[0031] The storage unit 36 ​​is configured with an HDD, RAM, ROM, SSD, etc. Various programs executed by the control unit 32 are stored in the storage unit 36. The storage unit 36 ​​also stores various information related to the DBMS server 30. The communication unit 37 includes a communication interface that transmits and receives various data between the control unit 32 and the client terminal 10 via the communication network 60.

[0032] [Configuration of Web Server 50] The configuration of the web server 50 will be described with reference to FIG. 4. The web server 50 displays a website in a browser or the like displayed on the display unit 14 of the client terminal 10. As shown in FIG. 4, the web server 50 includes a control unit 52, a memory unit 56, and a communication unit 57. The control unit 52 is connected to the memory unit 56 and the communication unit 57 via a bus 58. The control unit 52 is configured with a circuit including a CPU and semiconductor memory, and controls the operation of the web server 50 by executing various programs stored in the memory unit 56. Specifically, the control unit 52 functions as a receiving unit 53 and a transmitting unit 54 by executing various programs stored in the memory unit 56. When the receiving unit 53 receives URL information from the client terminal 10, the transmitting unit 54 transmits to the client terminal 10 a display instruction for the website stored in the memory unit 56 that corresponds to the URL information received by the receiving unit 53. This causes the website to be displayed in the browser or the like displayed on the display unit 14 of the client terminal 10.

[0033] The storage unit 56 is configured with an HDD, RAM, ROM, SSD, or the like. The storage unit 56 stores various programs executed by the control unit 52. The storage unit 56 also stores various website information (specifically, HTML information that constructs websites, etc.) associated with URL information. The communication unit 57 includes a communication interface that transmits and receives various data between the control unit 52 and the client terminal 10 via the communication network 60.

[0034] [Regarding the check program of this embodiment] The check program of this embodiment can be downloaded by a user from a predetermined website to client terminal 10. The check program downloaded to client terminal 10 is stored in storage unit 16 and executed by control unit 12. When a user creates a program written in SQL statements while communicating with DBMS server 30 using client terminal 10, the program stored in storage unit 16 is executed by control unit 12, thereby checking the SQL statements created by the user. Alternatively, the check program of this embodiment is stored in storage unit 16 of web server 50, and the check program stored in storage unit 16 is executed by control unit 52. When a user creates a program written in SQL statements while communicating with DBMS server 30 using client terminal 10, the check program stored in storage unit 56 is executed by control unit 52, thereby checking the SQL statements. Such an SQL statement checking operation will be described with reference to FIGS. 5 and 6. When the check program according to this embodiment is installed in client terminal 10, the following processing is performed by control unit 12 of client terminal 10 executing a check program stored in storage unit 16 or a check program transmitted to client terminal 10 from an external device (for example, a management server, not shown). When the check program according to this embodiment is stored in storage unit 56 of web server 50, the following processing is performed by control unit 52 of web server 50 executing a check program stored in storage unit 36 ​​when a predetermined browser is displayed on display unit 14 of client terminal 10 as a result of access from client terminal 10 to web server 50.

[0035] In this embodiment, the SQL statement check process performed by the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50 consists of three stages. Hereinafter, these stages are referred to as [Stage 1], [Stage 2], and [Stage 3]. As an example of the process performed by the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50, the following mode of verifying an SQL statement program will be described.

[0036] [Program example] As an example of a program written in SQL statements, we will consider the program shown below. / * First INSERT statement. Compiles but contains errors. No subquery. * / INSERT INTO tSALES (SALES_NO, Amount, Currency, Accounting Date) VALUES('001', 'AAA', NULL, 'BBB') / * Second INSERT statement. The second half is a subquery. The innermost table of "B. Name" must be retrieved. * / INSERT INTO tSALES (SALES_NO, Amount, Currency, Accounting Date, Contact) SELECT A.AccountingDate, A.SALES_NO, 100, 'AAAAA', B.Name FROM tSALES_2 A LEFT JOIN V_USERS B ON A. TANTOU_ID=B.USERID WHERE SALES_NO='1' ) / * UPDATE statement compiles but contains errors. * / UPDATE tSALES SET Amount='AAA' WHERE SALES_NO='1' / * The DELETE statement compiles but contains an error. The types on the left and right sides of the WHERE clause do not match.* / DELETE FROM tSALES WHERE SALES_NO= 2 / * VIEW definition * / CREATE VIEW V_SALES AS SELECT A.AccountingDate, A.SALES_NO, B.Name FROM tSALES A LEFT JOIN V_USERS B ON A.TANTOU_ID=B.USERID

[0037] Furthermore, the tables of T_SALES, T_USERS, and V_DEPT are stored in the storage unit 16 of the client terminal 10 or the storage unit 56 of the web server 50.

[0038] [First Stage] In the first stage, the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50 accepts a program written in SQL statements (hereinafter also referred to as SQL statements) input by a user via the operation unit 15 of the client terminal 10. When the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50 accepts the SQL statements ("YES" in step S1 of FIG. 5), the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50 displays the accepted SQL statements on the display unit 14 of the client terminal 10, as shown in FIG. 6 (step S2 of FIG. 5). In addition, the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50 detects a predetermined data manipulation language from the SQL statements and breaks them down into statement components containing the data manipulation language (step S3 of FIG. 5). As described above, predetermined data manipulation languages ​​include, but are not limited to, SELECT, UPDATE, INSERT, and DELETE. In the example program above, three INSERT statements and one SELECT statement are detected and decomposed into the components of each INSERT statement and the components of the SELECT statement.

[0039] Next, the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50 identifies which table (list) is being processed from the decomposed components and displays it on the display unit 14 of the client terminal 10 (step S4 in FIG. 5). Specifically, T_SALES is identified as the target table for each INSERT statement, and T_SALES_2 and V_USERS are identified as target tables for each SELECT statement. Furthermore, as shown in FIG. 6, the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50 displays the identified tables together with the data manipulation language on the display unit 14 of the client terminal 10 (step S4 in FIG. 5).

[0040] Next, the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50 acquires definition information for the columns of the table to be processed using the data manipulation language (step S5 in FIG. 5). As described above, the definition information includes, but is not limited to, the type, size, and nullability. Specifically, the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50 acquires the definition information (type, size, and nullability) for the columns T_SALES_2 and V_USERS and stores it in the memory unit 16 of the client terminal 10 or the memory unit 56 of the web server 50.

[0041] [Second Stage] In the second stage, the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50 analyzes the multi-layered data manipulation language and identifies the fields of the innermost table through a recursive loop (step S6 in Figure 5).

[0042] Specifically, when identifying fields using a recursive loop, the subquery of the second INSERT statement (second INSERT statement) is first analyzed. By analyzing this subquery, the following is analyzed: ( SELECT A.AccountingDate, A.SALES_NO, 100, 'AAAAA', B.Name FROM tSALES_2 A LEFT JOIN V_USERS B ON A.TANTOU_ID=B.USERID WHERE SALES_NO='1' ). Furthermore, by analyzing the JOIN clause, the T_SALES_2 table and V_USERS view are identified.

[0043] Then, the JOIN clause in the SELECT statement is recursively analyzed to identify the fields of each table. For example, in SELECT A.AccountingDate, A.SALES_NO, B.Name FROM tSALES A LEFT JOIN V_USERS B ON A.TANTOU_ID=B.USERID, the innermost fields of A.AccountingDate and B.Name are identified.

[0044] Next, multiple references in each field are listed. SELECT A.AccountingDate, A.SALES_NO, B.Name FROM tSALES A LEFT JOIN V_USERS B ON A.TANTOU_ID=B.USERID etc. are analyzed, and the relationships between multiple SELECTs in each VIEW and the relationships between multiple SELECTs in each SELECT statement in each SQL statement are listed.

[0045] As described above, in this embodiment, for an SQL statement that includes a subquery or nested query, the outermost query is first analyzed and its fields are identified. This process is performed recursively to clarify which table all fields come from. For example, for the second INSERT statement, the outer SELECT statement is first analyzed. Then, the fields of the innermost query are recursively identified and used. This process makes it possible to accurately identify which table all fields in the SQL statement come from and to recursively analyze them.

[0046] [Third Stage] In the third stage, for the fields of the identified innermost table, at least one of the following is performed based on the acquired definition information: verification of comparison operations in the first statement containing a first specified language in the received SQL statement; verification of assignment processing in the second statement containing a second specified language; and detection of anti-patterns. Here, examples of the first specified language include, but are not limited to, WHERE, CASE, JOIN, etc.; and examples of the second specified language include, but are not limited to, INSERT, UPDATE, etc.

[0047] First, the verification of the comparison operation of the first statement containing a first specific language (e.g., WHERE, CASE, JOIN) in the SQL statement (step S7 in FIG. 5) will be described. Specifically, for statements containing WHERE, CASE, or JOIN in the SQL statement, the left and right sides of comparison operators such as "=", "<", ">", and "LIKE" are compared based on the acquired definition information. For example, for the statement "WHERE age > 30", the age field (integer type) is compared with the literal value 30 (integer type). Then, it is confirmed whether the field information on the left and right sides of the comparison operator matches. If the types are different, the sizes do not match, or the nullability is different, it is determined that an error will occur at runtime. For example, if the age field is a string type and the literal value 30 is an integer type, a type mismatch occurs and it is determined that an error will occur.

[0048] Next, we will explain the verification of the assignment process of the second statement containing a second specific language (e.g., INSERT, UPDATE, etc.) in the SQL statement (step S8 in Figure 5). Specifically, for statements containing INSERT or UPDATE in the SQL statement, the left side (field) and right side (value and other fields) of the assignment process are extracted based on the acquired definition information, and the field information for each is compared. For example, in the statement UPDATE employees SET age = 'thirty', the age field (integer type) is compared with the literal value 'thirty' (string type). Then, it is confirmed that the field information on the left side and right side matches. If the types are different, the sizes do not match, or the nullability is different, it is determined that an error will occur at runtime. For example, if the age field is an integer type and 'thirty' is a string type, a type mismatch occurs, and it is determined that an error will occur.

[0049] The above example discussed type mismatches, but other definition information is explained below. A size mismatch occurs when the data size (length) of a field handled by an SQL statement exceeds the limit. For example, this occurs when a string of greater length is assigned to a VARCHAR(10) field. Specifically, if the product name field is defined as VARCHAR(10), a size mismatch error will occur if a product name string of 11 characters or more is inserted, so it is determined that an error has occurred.

[0050] Also, differences in nullability occur when you try to assign a NULL value to a field that does not allow NULL in the SQL statement. For example, if the "Project Category" field has a NOT NULL constraint, an error will occur due to differences in nullability when a NULL value is inserted, so it is determined that an error will occur.

[0051] This embodiment also makes it possible to detect anti-patterns in SQL statements. Anti-patterns refer to non-recommended coding methods and design patterns that should generally be avoided when writing SQL statements. While these anti-patterns rarely cause runtime errors, they can lead to poor DBMS query performance, data inconsistencies, poor maintainability, and even unintended behavior. The processing of this embodiment makes it possible to detect and avoid these anti-patterns.

[0052] Anti-patterns include the following: 1. Using SELECT * (select with an asterisk) 2. Left-hand side function Example: FORMAT( SALES_DATE, 'yyyy / MM' ) = '2024 / 06' 3. Outer joins that do not use all primary key fields 4. INSERT clause between tables without EXISTS or uniqueness 5. Changing primary key values ​​with UPDATE 6. Unintended assignment success / comparison operation success (e.g., a date type is assigned to a numeric type column. Although no error occurs, it is likely that the assignment is incorrect)

[0053] In the first and second stages described above, each field that makes up the SQL statement is identified, making it possible to detect each of the anti-patterns mentioned above in the third stage.

[0054] If the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50 determines that the state is normal in the above-mentioned comparison operation verification, assignment operation verification, or anti-pattern detection ("NO" in step S9 of FIG. 5), it displays that the state is normal on the display unit 14 of the client terminal 10 (step S10 of FIG. 5). On the other hand, if the control unit 12 of the client terminal 10 or the control unit 52 of the web server 50 determines that the state is not normal ("YES" in step S9 of FIG. 5), it displays warning information on the display unit 14 of the client terminal 10 (step S11 of FIG. 5). Specifically, as shown in FIG. 6, the display unit 14 of the client terminal 10 displays a table listing multiple fields into which the INSERT statement is broken down and why the fields are not normal when the comparison operation or assignment operation is performed (specifically, what kind of errors may occur). For example, the causes of errors such as type mismatch (displayed as "string assignment to numeric type"), size mismatch (displayed as "size exceeded"), and nullability (displayed as "NULL assignment to NOT_NULL") are displayed for each field on the display unit 14 of the client terminal 10.

[0055] Furthermore, in this embodiment, when a certain piece of warning information (for example, the third warning information from the top in FIG. 6, "Size Exceeded") among the multiple pieces of warning information displayed on the display unit 14 of the client terminal 10 is designated with, for example, the cursor, a portion of the SQL statement displayed on the display unit 14 that has been determined to be abnormal in the verification of a comparison operation, the verification of an assignment process, or the detection of an anti-pattern is displayed in a manner different from the normal display manner (a different color, a different font size, bold, etc.). This allows the operator of the client terminal 10 to immediately recognize the abnormal portion in the SQL statement.

[0056] Furthermore, in this embodiment, when a phrase corresponding to a field of a table in an SQL statement displayed on the display unit 14 of the client terminal 10 is designated, for example, with the cursor, information about the innermost table related to the phrase is displayed on the display unit 14. This allows the operator to immediately recognize the information about the innermost table corresponding to the phrase corresponding to the field of the table in the SQL statement.

[0057] In this embodiment, UPDATE and INSERT statements do not simply reference tables; they may also reference views or subqueries. When referencing views or subqueries, it is difficult to identify the underlying fields of the fields on the left and right sides of assignments or comparison operations. In response to this problem, the parser described above has the ability to identify the referenced fields by recursively analyzing them, even in these complex reference structures, and to provide feedback on the comparison results. Furthermore, this ability to identify the innermost fields enables the analysis of multi-layered SELECT statements, which was difficult to achieve with conventional technology. This allows for visualization of data manipulation language (DML) and data definition language (DDL) operations and can be applied as a technology that enables the automatic generation of specifications. This function facilitates the understanding and management of multi-layered query structures in system development, contributing to improved development efficiency and reduced errors. In system development, there are often program deliverables that exist in a one-to-one relationship with specifications written by humans, and discrepancies between the two frequently occur. These discrepancies are caused by implementer errors or forgetting to update documents, and are a major issue. In contrast, the above-described technology provides an effective solution to such problems, making it possible to automatically verify and maintain the consistency between the specifications and the program.

[0058] According to the program (check program), computer (client terminal 10, web server 50), system 1, and information processing method of the present embodiment configured as described above, the program is executed by control units 12 and 52 to accept an SQL statement, detect a predetermined data manipulation language from the accepted SQL statement, decompose the statement into statement components containing the data manipulation language, identify which table is being processed from the decomposed components, and obtain definition information for the columns of the table processed by the data manipulation language. The program also identifies the fields of the innermost table by analyzing the multi-layered data manipulation language of the accepted SQL statement. Furthermore, for the identified fields of the innermost table, at least one of verification of a comparison operation in a first statement containing a first specified language and verification of an assignment operation in a second statement containing a second specified language in the accepted SQL statement is performed based on the obtained definition information, or anti-pattern detection is performed. If the comparison operation verification, assignment processing verification, or anti-pattern detection determines that the statement is abnormal, a warning is issued. According to such a program, computer, system 1, and information processing method, programmers and the like can recognize in advance if there are errors or anti-patterns that may occur during program execution, without having to execute a program written in SQL statements.

[0059] More specifically, the program, computer, system 1, and information processing method of the present embodiment have the advantage of enabling bug and anti-pattern detection, which previously required testing, to be performed without the need for a testing process. Eliminating the testing process means that associated processes such as "test data creation," "repeating test patterns until a bug is found," and "detecting anti-patterns" are also unnecessary. This means that system development companies can significantly reduce the development process, leading to shorter development times and lower development costs. Furthermore, in programming, static analysis and third-party code reviews are also performed in addition to testing to improve source code quality. However, these processes can also be eliminated or simplified or shortened using the program, computer, system 1, and information processing method of the present embodiment. Furthermore, the program, computer, system 1, and information processing method of the present embodiment are also effective in detecting potential errors and anti-patterns in source code that has actually been delivered and is currently in production. Specifically, proactive measures such as data checks to avoid bugs and anti-patterns are possible, leading to a lower error rate in running systems, improved performance, and ultimately increased end-user satisfaction. Another advantage is that it can simulate the impact of changing logic and definitions when modifying a system that is currently in production.It can instantly analyze whether the changed logic and definitions or other programs that depend on those changes will degrade, allowing the modification process to be completed with minimal risk.

[0060] Furthermore, in the program, computer, system 1, and information processing method of this embodiment, as described above, the preset predetermined data manipulation language may be at least one of SELECT, UPDATE, INSERT, and DELETE. In this case, errors and anti-patterns that may occur during program execution can be detected more reliably in advance, even without executing the program written in SQL statements.

[0061] Furthermore, the first specified language may be at least one of WHERE, CASE, and JOIN. In this case, it is possible to more accurately verify the comparison operation of the first statement that includes the first specified language in the SQL statement.

[0062] The second specific language may be at least one of INSERT and UPDATE, in which case the assignment operation of the second statement including the second specific language can be verified more accurately.

[0063] Furthermore, the control units 12 and 52 may execute a program to display the received SQL statement and the warning information to be notified on the display unit 14 of the client terminal 10. In this case, an operator such as a programmer who operates the client terminal 10 can check the warning information at a glance.

[0064] Furthermore, information regarding which table from the decomposed components is being processed may be associated with the data manipulation language and displayed on the display unit 14 of the client terminal 10. In this case, an operator such as a programmer operating the client terminal 10 can confirm at a glance the information associated with the data manipulation language regarding which table is being processed.

[0065] Furthermore, when warning information to be displayed on the display unit 14 of the client terminal 10 is specified, the part of the SQL statement that is determined to be abnormal in the comparison operation verification, assignment processing verification, or anti-pattern detection displayed on the display unit 14 may be displayed. In this case, an operator such as a programmer who operates the client terminal 10 can confirm at a glance the part that is determined to be abnormal in the comparison operation verification, assignment processing verification, or anti-pattern detection.

[0066] Furthermore, when a phrase corresponding to a field of a table in an SQL statement displayed on the display unit 14 of the client terminal 10 is specified, information about the innermost table related to the phrase may be displayed on the display unit 14. In this case, for a phrase corresponding to a field of a table in an SQL statement, an operator such as a programmer operating the client terminal 10 can check at a glance information about the innermost table related to the phrase.

[0067] [Other Aspects of the Present Embodiment] The program (check program), computer (client terminal 10, web server 50), system 1, and information processing method according to this embodiment are not limited to the above-described aspects, and various modifications can be made.

[0068] For example, the values ​​assigned during assignment processing may include the following patterns: a table field itself, a fixed value (e.g., 'Thirty'), a calculated value (e.g., FORMAT(GETDATE(), 'yyyy / MM / dd'), Amount * 1.1 AS Amount Including Tax), and a variable or function value. When these values ​​are assigned, the information that should be fed back to the user as a warning is their location, their logical type, size, and nullability information, as well as their definition location (the variable declaration section for variables, or the function definition for functions). Therefore, if the right-hand side (the assigned value) is not a field during assignment processing, this information is fed back to the user as a warning. Furthermore, this feedback information can be obtained by using specific DBMS functions, without performing the "processing to identify the innermost field of each SELECT statement" described in this embodiment. The above technical matters also apply to comparison operations.

[0069] Furthermore, with regard to coding convention and antipattern checking functions, if there are company- or team-specific antipatterns in addition to general SQL statement antipatterns, each engineer can input their antipattern definitions, and the parser can search for the corresponding antipatterns in the code model analyzed. This makes it possible to build a custom antipattern detection mechanism. In particular, since there are no strict definitions for various "bad" things other than runtime error detection, or "things that could potentially cause bugs or performance degradation," these often exist as local rules for individual companies or teams. Anyone can discover these rules, enabling early detection and eliminating the need for testing.

[0070] Furthermore, while DBMSs have error messages for "runtime errors" or "programs that cannot be compiled" when recompiling, they do not have a mechanism for detecting errors such as "the syntax is valid, but the referenced table or field does not exist." If a program contains multiple errors, the DBMS will only issue an error message for the first one. However, if there are any anomalies in the code model analyzed by this parser, it is possible to issue errors indicating the location and nature of all the anomalies. In other words, it is possible to display a warning for all anomalies at once. Furthermore, the error messages issued by DBMSs are often vague and the methods for dealing with the errors are unclear, but it is believed that using this parser will improve the error messages. [Explanation of symbols]

[0071] 1 System 10 Client Terminal 12 Control Unit 14 Display section 15 Control section 16 Memory section 17 Communications Department 19 Bus 30 DBMS Server 32 Control Unit 33 Reception methods 34 Transmission means 36 Memory section 37 Communications Department 38 Bus 50 Web Server 52 Control section 53 Reception methods 54 Transmission Method 56 Memory section 57 Communications Department 58 Bus 80 Communication Network

Claims

1. A program executed by a control unit of a computer, When the control unit executes the program, Accepts SQL statements, Detecting a predetermined data manipulation language from the received SQL statement, and decomposing the statement into constituent elements including the data manipulation language; Identify which table is being processed from the decomposed components, Obtain definition information of columns of a table to be processed by the data manipulation language; Identifying a field of the innermost table by analyzing the multi-layered data manipulation language of the received SQL statement; With respect to the field of the identified innermost table, based on the acquired definition information, at least one of verifying a comparison operation of a first statement including a first specified language in the received SQL statement and verifying an assignment operation of a second statement including a second specified language, or detecting an anti-pattern; A program that issues warning information when it is determined that a comparison operation, assignment process, or anti-pattern is not normal.

2. 2. The program according to claim 1, wherein the predetermined data manipulation language is at least one of SELECT, UPDATE, INSERT, and DELETE.

3. 2. The program according to claim 1, wherein the first specific language is at least one of WHERE, CASE, and JOIN.

4. 2. The program according to claim 1, wherein the second specific language is at least one of INSERT and UPDATE.

5. When the control unit executes the program, 2. The program according to claim 1, further comprising displaying the received SQL statement and the warning information to be notified on a display unit of a client terminal.

6. 6. The program according to claim 5, wherein information on which table from the decomposed components is being processed is displayed on the display unit of the client terminal in association with the data manipulation language.

7. The program of claim 5, wherein when the warning information displayed on the display unit of the client terminal is specified, the parts of the SQL statement displayed on the display unit that are determined to be abnormal in verification of comparison operations, verification of assignment processing, or detection of anti-patterns are displayed.

8. 6. The program according to claim 5, wherein when a phrase corresponding to a field of the table in the SQL statement displayed on the display unit of the client terminal is specified, information about the innermost table related to the phrase is displayed on the display unit.

9. A computer having a control unit, When the control unit executes a program, Accepts SQL statements, Detecting a predetermined data manipulation language from the received SQL statement, and decomposing the statement into constituent elements including the data manipulation language; Identify which table is being processed from the decomposed components, Obtain definition information of columns of a table to be processed by the data manipulation language; Identifying a field of the innermost table by analyzing the multi-layered data manipulation language of the received SQL statement; With respect to the field of the identified innermost table, based on the acquired definition information, at least one of verifying a comparison operation of a first statement including a first specified language in the received SQL statement and verifying an assignment operation of a second statement including a second specified language, or detecting an anti-pattern; The computer issues warning information when it is determined that the comparison operation verification, the assignment process verification, or the anti-pattern detection is not normal.

10. An information processing method executed by a computer having a control unit, a step of the control unit receiving an SQL statement; a step of detecting a predetermined data manipulation language set in advance from the SQL statement received by the control unit and decomposing the statement into constituent elements including the data manipulation language; a step of identifying which table the control unit is processing from the decomposed components; a step in which the control unit acquires definition information of columns of a table to be processed by the data manipulation language; a step of identifying a field of the innermost table by analyzing the multi-layered data manipulation language of the SQL statement received by the control unit; a step in which, with respect to the field of the innermost table identified by the control unit, the control unit performs at least one of verifying a comparison operation of a first statement including a first specified language in the received SQL statement and verifying an assignment operation of a second statement including a second specified language, or detecting an anti-pattern, based on the acquired definition information; a step of issuing a warning when the control unit determines that the comparison operation verification, the assignment processing verification, or the anti-pattern detection is abnormal; An information processing method comprising:

Images