CN117667882A

CN117667882A - Method and device for checking sql code

Info

Publication number: CN117667882A
Application number: CN202311365007.9A
Authority: CN
Inventors: 方海波
Original assignee: Zhejiang Geely Holding Group Co Ltd; Zhejiang Zeekr Intelligent Technology Co Ltd
Current assignee: Zhejiang Geely Holding Group Co Ltd; Zhejiang Zeekr Intelligent Technology Co Ltd
Priority date: 2023-10-18
Filing date: 2023-10-18
Publication date: 2024-03-08

Abstract

The application provides an sql code checking method and device, wherein the method comprises the following steps: obtaining the sql code to be checked in a sounding way; the sql code at least comprises first primary key information; connecting a database corresponding to the sql code, and acquiring metadata information of the database; the metadata information at least comprises second primary key information; determining whether the first primary key information is consistent with the second primary key information; and if the first main key information is inconsistent with the second main key information, generating an error prompt and outputting the error prompt to a user.

Description

Method and device for checking sql code

Technical Field

The application relates to the technical field of computers, in particular to an sql code checking method and device.

Background

In database design and development, primary keys are very important concepts. Proper use of the primary key can ensure the integrity, accuracy and uniqueness of the data, while erroneous use of the primary key can lead to confusion, errors and loss of data. Therefore, in database design and development, it is essential to detect the use of primary keys in code.

The traditional method is to check the code and data table structure manually, but this method is inefficient, error-prone and time-consuming. Therefore, an automated method is needed to detect the correctness of the primary key usage to improve the development efficiency and code quality.

Disclosure of Invention

The application provides an sql code checking method, which comprises the following steps:

acquiring an sql code to be checked; the sql code at least comprises first primary key information;

connecting a database corresponding to the sql code, and acquiring metadata information of the database; the metadata information at least comprises second primary key information;

determining whether the first primary key information is consistent with the second primary key information; and if the first main key information is inconsistent with the second main key information, generating an error prompt and outputting the error prompt to a user.

Optionally, the sql code to be checked is obtained; the sql code at least comprises first primary key information in a database corresponding to the sql code, and the method comprises the following steps:

acquiring an sql code to be checked; the sql code at least comprises first primary key information in a database corresponding to the sql code;

traversing the sql code, analyzing at least one sql statement from the sql code, and analyzing the first primary key information from the at least one sql statement respectively.

Optionally, traversing the sql code, parsing at least one sql statement from the sql code, and parsing the first primary key information from the at least one sql statement respectively, including:

traversing the sql code, and analyzing at least one sql statement from the sql code;

and respectively determining whether the at least one sql statement contains a primary key, and if the sql statement contains the primary key, analyzing the first primary key information from the at least one sql statement.

Optionally, the determining whether the first primary key information is consistent with the second primary key information; if not, generating an error prompt and outputting to a user, including:

determining whether the at least one sql statement is a primary key definition statement;

if the sql statement is a primary key definition statement, determining whether the first primary key information is consistent with the second primary key information; and if the first main key information is inconsistent with the second main key information, generating an error prompt and outputting the error prompt to a user.

Optionally, the method further comprises:

if the sql statement is not a primary key definition statement, determining whether the sql statement is a primary key use statement;

if the sql language is a main key use statement, inquiring the second main key information, and determining whether a main key used in the main key use statement is unique;

if the primary key used in the primary key use statement is not unique, generating an error prompt and outputting the error prompt to a user; otherwise, further determining whether the primary key used in the primary key use statement is assigned a null value;

and if the primary key used in the primary key use statement is assigned to be a null value, generating an error prompt and outputting the error prompt to a user.

Optionally, the primary key information includes a primary key name, a primary key type, and a primary key length;

the determining whether the first primary key information is consistent with the second primary key information, if not, generating an error prompt and outputting to a user comprises:

determining whether the first primary key name is consistent with the second primary key name, if not, generating an error prompt and outputting to a user; and/or the number of the groups of groups,

determining whether the first main key type is consistent with the second main key type, if not, generating an error prompt and outputting to a user; and/or the number of the groups of groups,

determining whether the first main key length is larger than the second main key length, if the first main key length is larger than the second main key length, generating an error prompt and outputting to a user.

Optionally, the method further comprises:

and inquiring metadata information of the database, and determining whether repeated primary key information is contained in the metadata information.

The application provides an sql code inspection apparatus, the apparatus comprising:

a code acquisition unit for acquiring an sql code to be checked; the sql code at least comprises first primary key information;

the database connection unit is used for connecting a database corresponding to the sql code and acquiring metadata information of the database; the metadata information at least comprises second primary key information;

an information comparison unit, configured to determine whether the first primary key information is consistent with the second primary key information; and if the first main key information is inconsistent with the second main key information, generating an error prompt and outputting the error prompt to a user.

The present application also provides a computer readable storage medium storing machine readable instructions that when invoked and executed by a processor implement the above-described methods.

In the scheme described in the above embodiment, the database is connected in the code writing stage to obtain metadata information, and the primary key information in the code is compared with the primary key information in the metadata information, so that whether the primary key is used in error is detected, and when the error is found, the prompt is performed, thereby helping a developer to avoid the situation that the primary key is used in error. By automated inspection, time and effort can be saved for manual inspection by the developer, enabling the developer to develop and test code more quickly. Meanwhile, errors caused by human negligence can be avoided, so that the accuracy and quality of codes are improved.

Drawings

FIG. 1 is a flow chart of an sql code inspection method provided in an exemplary embodiment.

Fig. 2 is a hardware configuration diagram of an electronic device in which the sql code inspection apparatus is located according to an exemplary embodiment.

FIG. 3 is a block diagram of an sql code inspection apparatus provided by an exemplary embodiment.

Detailed Description

Reference will now be made in detail to exemplary embodiments, examples of which are illustrated in the accompanying drawings. When the following description refers to the accompanying drawings, the same numbers in different drawings refer to the same or similar elements, unless otherwise indicated. The implementations described in the following exemplary examples are not representative of all implementations consistent with the present application. Rather, they are merely examples of apparatus and methods consistent with some aspects of the present application as detailed in the accompanying claims.

It should be noted that: in other embodiments, the steps of the corresponding method are not necessarily performed in the order shown and described in this specification. In some other embodiments, the method may include more or fewer steps than described in this specification. Furthermore, individual steps described in this specification, in other embodiments, may be described as being split into multiple steps; while various steps described in this specification may be combined into a single step in other embodiments.

Referring to fig. 1, fig. 1 is a flowchart of an sql code inspection method according to an exemplary embodiment. The method may comprise the steps of:

step 102, acquiring an sql code to be checked; the sql code at least comprises first primary key information.

In the present specification, the user can acquire the sql code and perform code inspection by the sql code inspection method disclosed in the present specification.

The specific manner in which the sql code to be checked is obtained is not specifically limited in this specification. For example, a user can manually write sql codes, write sql statements for query, insert, update, delete, etc. according to actual requirements, and write sql codes meeting requirements according to grammar rules and data table structures of a database.

The user may also connect to the database using database management tools (e.g., mySQL Workbench, phpmyidamin, SQL Server Management Studio, etc.) that typically provide a visual interface to generate the corresponding sql code by dragging, selecting, etc. the sql code may be generated using graphical interface operations.

Some database systems record the actual executed sql code, which the user may also obtain by looking up the query log or transaction log of the database.

In this specification, primary key information refers to a field or column in a table for uniquely identifying each record. The primary key serves to uniquely identify each record in the table, ensuring that each record has a unique identifier. The primary key information is typically defined at the time of table creation or in subsequent modifications and there is only one primary key. The primary key may be a single column or a combination of columns. The value of the primary key field must be unique and cannot be null in order to effectively distinguish between and operate on each record in the table.

In the sql code, the primary key information refers to related information for defining and specifying a primary key in a table when creating or modifying a table structure.

For example, a primary key is specified when creating a table.

For another example, a primary key constraint is added when modifying the table structure.

ALTER TABLE table_name

ADD PRIMARY KEY(pk_column1,pk_column2,...)。

In one embodiment, after the sql code is obtained, the sql code may be traversed, at least one sql statement may be parsed from the sql code, and the first primary key information may be parsed from the at least one sql statement, respectively.

In this specification, sql code refers to a piece of code or script that contains sql statements. An sql statement is a single instruction used to perform a database operation. The sql code may contain one or more sql statements for implementing a particular database operation.

In the following, a specific example shows the relation between sql code and sql statements.

For example, the following is a piece of sql code.

INSERT INTO users(id,name,age)

VALUES(1,'John Doe',25)；

SELECT name,age

FROM users

WHERE age>30；

The code contains three sql statements.

The CREATE TABLE statement is used to CREATE a TABLE named users, defining three columns: id. name and age.

The INSERT INTO statement is used to INSERT a record INTO the users table, including the values of id, name, and age.

The SELECT statement is used to query the records from the users table that satisfy the condition, age >30, and return the values of the name and age fields.

Generally speaking, SQL code is a piece of code that contains SQL statements to represent the logic of database operations. Each SQL statement is a separate instruction for performing a particular database operation, such as creating a table, inserting data, or querying data.

By traversing the sql code, at least one sql statement may be parsed from the sql code, and the first primary key information may be parsed from the at least one sql statement, respectively.

For example, in the above CREATE TABLE statement, the column id is the primary key, and the information contained in the column id may be the first primary key information.

In one embodiment, it may also be determined whether a primary key is included in the at least one sql statement, and if the primary key is included in the sql statement, the first primary key information is parsed from the at least one sql statement. If the sql statement does not contain a primary key, analysis is not needed, so that calculation resources are saved, and efficiency is improved.

102, connecting a database corresponding to the sql code, and acquiring metadata information of the database; the metadata information includes at least second primary key information.

In the present specification, a database corresponding to the sql code may be connected, and metadata information of the database may be obtained.

Specifically, it is first necessary to ensure that a database driver or library corresponding to the database system used has been imported into the application. Different database systems have different drivers or libraries, and need to be selected according to actual situations.

Parameters required for database connection are set in the codes, including the address, port number, user name, password, etc. of the database server. These parameters are used to establish a connection with the database.

The connection to the database is established by means of the incoming connection parameters using an interface or method provided by the database driver. The connection process may take a certain time, and the situations such as exception handling and connection timeout may need to be considered.

It should be noted that the specific steps of connecting databases may vary depending on the programming language and database system used, and may be operated with reference to corresponding documents and courses.

In this specification, metadata information of a database refers to data describing a database structure and an object. It contains various definitions and attribute information about objects such as tables, columns, indexes, constraints, views, stored procedures, etc. in the database.

Metadata information may provide a detailed description of the structure and content of the database, which is an important basis for managing and operating the database. By querying the metadata information, detailed information such as names, types, sizes, data types, constraints, indexes, association relations and the like of objects in the database can be obtained.

In practical applications, to obtain metadata information in a database, a system table or a system view provided by a database system may be used. These tables or views store metadata information about database structures and objects (e.g., tables, columns, indexes, etc.).

For example, when the database is a Mysql database, the following system table may be used to obtain metadata information:

information_schema. Information about all tables in the database, such as table names, columns, indexes, etc., is contained.

information_schemes. Information about columns in the table is contained, such as column name, data type, data length, etc.

When the database is an SQL Server database, the following system views may be used to obtain metadata information:

sys.tables: information about all tables in the database, such as table names, columns, indexes, etc., is contained.

sys.columns: information about columns in the table is contained, such as column name, data type, whether it is a primary key, etc.

In this specification, metadata information of a database may be queried to obtain primary key information of a table.

For example, in MySQL, primary key information may be obtained by querying the information_schema.key_column_use system view. For example, the following query will return primary key column information of the specification table:

SELECT column_name

FROM information_schema.key_column_usage

WHERE table_schema＝'your_database_name'AND table_name＝'your_table_name'。

in SQL Server, primary key information can be obtained by querying the sys.key_constraints system view. For example, the following query will return primary key column information of the specification table:

SELECT COL_NAME(object_id,column_id)AS column_name

FROM sys.key_constraints

WHERE parent_object_id＝OBJECT_ID('your_table_name')AND type＝'PK'。

note that the you_database_name and you_table_name in the above examples should be replaced with actual database names and table names.

Step 106, determining whether the first primary key information is consistent with the second primary key information; and if the first main key information is inconsistent with the second main key information, generating an error prompt and outputting the error prompt to a user.

In this specification, after the sql code to be checked is obtained, the first primary key information may be parsed from the sql code, and the second primary key information may be obtained from metadata information of a database.

By comparing whether the first primary key information is identical to the second primary key information, it can be determined whether the sql code has a problem. If not, an error prompt may be generated and output to the user.

In one embodiment, it may be determined whether the sql statement is a primary key definition statement.

Wherein the primary key definition statement is an SQL statement used to define primary keys in the database table. It is typically contained in the CREATE TABLE statement to specify which columns are the primary keys of the TABLE.

In the SQL language, the primary key may be defined using PRIMARY KEY keywords, such as:

when the sql statement is a primary key definition statement, determining whether the first primary key information is consistent with the second primary key information; if not, generating an error prompt and outputting to a user.

When the sql statement is not a primary key definition statement, it may be further determined whether the sql statement is a primary key use statement.

The main key use statement refers to an sql statement for performing data operation by using a main key.

For example, when the primary key is id, the primary key may be used when inserting data:

INSERT INTO your_table_name(id,name,age)VALUES(1,'John',25)。

the primary key may be used in updating the data:

UPDATE your_table_name SET name＝'Alice'WHERE id＝1。

when the sql statement is a primary key use statement, the second primary key information may be queried, and it may be determined whether a primary key used in the primary key use statement is unique.

For example, when a primary key used in a primary key use statement is an id, it may be determined whether a plurality of ids exist in primary key information stored in database metadata, and if so, it is interpreted that the primary key is not unique.

If the primary key used in the primary key use statement is unique, it may be further determined whether the primary key used in the primary key use statement is assigned a null value.

In one embodiment, the primary key information includes a primary key name, a primary key type, and a primary key length.

The primary key name refers to a name specified for the primary key at the time of creating the table. When creating the table, the user may assign a name to the primary key to reference the primary key in subsequent operations.

The primary key type may be a variety of data types depending on the database management system used and the column definition in the table. Such as integer type, string type, etc.

The length of the primary key may refer to a length restriction of the data type used to store the primary key value. The specific primary key length depends on the database management system used and the type of data used.

For integer type primary keys, such as INT or BIGINT, they are of fixed length in most database management systems. For example, INT is typically 4 bytes (32 bits) and BIGINT is typically 8 bytes (64 bits).

For a primary key of a string type, such as VARCHAR or CHAR, its length may be defined as desired. When creating a table, it is necessary to specify the maximum length of the primary key column. For example, VARCHAR (50) represents a string in which the primary key column can store up to 50 characters.

In determining whether the first primary key information is consistent with the second primary key information, it may be determined whether the first primary key name is consistent with the second primary key name. Meanwhile, it may also be determined whether the first primary key type is consistent with the second primary key type. Meanwhile, whether the first main key length is larger than the second main key length can be determined, and if the first main key length is larger than the second main key length, an error prompt is generated and output to a user.

The user may select any detection rule to determine whether the first primary key information is consistent with the second primary key information, which is not specifically limited in this specification.

In one embodiment, metadata information of the database may be further queried to determine whether the metadata information includes duplicate primary key information.

For example, in practical applications MySQL uses an information_schema table to store metadata information. The following example queries the primary key information of all tables in the library named mydatabase and checks if there is a duplicate primary key value.

SELECT table_name,column_name,COUNT(*)as pk_count

FROM information_schema.key_column_usage

WHERE constraint_schema＝'mydatabase'AND constraint_name＝'PRIMARY'

GROUP BY table_name,column_name

HAVING COUNT(*)>1；

In SQL Server, the system view sys.key_constraints may be used to query the primary key information. The following example queries the primary key information of all tables in the library named mydatabase and checks if there is a duplicate primary key value.

SELECT OBJECT_NAME(object_id)AS table_name,name AS pk_name,

(SELECT COUNT(*)

FROM sys.index_columns

WHERE object_id＝c.object_id AND index_id＝c.unique_index_id)AS pk_count

FROM sys.key_constraints AS c

WHERE[type]＝'PK'AND[schema_id]＝SCHEMA_ID('mypackage')

HAVING COUNT(*)>1；

If duplicate primary key values exist, they may cause the data to duplicate or be inconsistent, thereby compromising the integrity of the data. Thus, querying duplicate primary key information helps to ensure data integrity.

The present specification also provides an embodiment of the sql code inspection apparatus, corresponding to the embodiment of the sql code inspection method described above.

Referring to fig. 2, fig. 2 is a hardware configuration diagram of an electronic device in which a code anomaly detection device is located in an exemplary embodiment. At the hardware level, the device includes a processor 202, an internal bus 204, a network interface 206, a memory 208, and a non-volatile storage 210, although other hardware required for the service is possible. One or more embodiments of the present description may be implemented in a software-based manner, such as by the processor 202 reading a corresponding computer program from the non-volatile storage 210 into the memory 208 and then running. Of course, in addition to software implementation, one or more embodiments of the present disclosure do not exclude other implementation manners, such as a logic device or a combination of software and hardware, etc., that is, the execution subject of the following processing flow is not limited to each logic unit, but may also be hardware or a logic device.

Referring to fig. 3, fig. 3 is a block diagram of an sql code inspection apparatus according to an exemplary embodiment. The sql code inspection apparatus may be applied to the electronic device shown in fig. 2 to implement the technical solution of the present specification. Wherein, the sql code inspection apparatus may include:

a code acquisition unit 302 for acquiring an sql code to be checked; the sql code at least comprises first primary key information;

a database connection unit 304, configured to connect to a database corresponding to the sql code, and obtain metadata information of the database; the metadata information at least comprises second primary key information;

an information comparing unit 306, configured to determine whether the first primary key information is consistent with the second primary key information; and if the first main key information is inconsistent with the second main key information, generating an error prompt and outputting the error prompt to a user.

In this embodiment, the code obtaining unit is further configured to obtain an sql code to be checked; the sql code at least comprises first primary key information in a database corresponding to the sql code;

In this embodiment, the code obtaining unit is further configured to traverse the sql code, and parse at least one sql statement from the sql code;

In this embodiment, the information comparing unit is further configured to determine whether the at least one sql statement is a primary key definition statement;

In this embodiment, the apparatus further includes:

a uniqueness determination unit configured to determine whether the sql statement is a primary key usage statement if the sql statement is not a primary key definition statement;

In this embodiment, the primary key information includes a primary key name, a primary key type, and a primary key length;

the information comparison unit is further used for determining whether the first primary key name is consistent with the second primary key name, and if not, generating an error prompt and outputting the error prompt to a user; and/or the number of the groups of groups,

In this embodiment, the apparatus further includes:

and the metadata checking unit is used for inquiring the metadata information of the database and determining whether the metadata information contains repeated primary key information.

For the device embodiments, reference is made to the description of the method embodiments for the relevant points, since they essentially correspond to the method embodiments. The apparatus embodiments described above are illustrative only, in that the elements illustrated as separate elements may or may not be physically separate, and the elements shown as elements may or may not be physical elements, may be located in one place, or may be distributed over a plurality of network elements. Some or all of the modules may be selected according to actual needs to achieve the purposes of the present description. Those of ordinary skill in the art will understand and implement the present invention without undue burden.

User information (including but not limited to user equipment information, user personal information, etc.) and data (including but not limited to data for analysis, stored data, presented data, etc.) referred to in this specification are both information and data authorized by the user or sufficiently authorized by the parties, and the collection, use and processing of relevant data requires compliance with relevant laws and regulations and standards of the relevant country and region, and is provided with corresponding operation portals for the user to choose authorization or denial.

The present specification also provides an embodiment of a computer-readable storage medium. The computer readable storage medium stores machine readable instructions that, when invoked and executed by a processor, may implement the sql code inspection method provided by any of the embodiments of the present specification.

The computer readable storage medium provided by the embodiments of the present specification may include, but is not limited to, any type of disk including floppy disks, hard disks, optical disks, CD-ROMs, and magneto-optical disks, ROMs (Read-Only memories), RAMs (Random Access Memory, random access memories), EPROMs (Erasable Programmable Read-Only memories), EEPROMs (Electrically Erasable Programmable Read-Only memories), flash memories, magnetic cards, or optical fiber cards. That is, a readable storage medium includes a readable medium that can store or transfer information.

The system, apparatus, module or unit set forth in the above embodiments may be implemented in particular by a computer chip or entity, or by a product having a certain function. A typical implementation device is a computer, which may be in the form of a personal computer, laptop computer, cellular telephone, camera phone, smart phone, personal digital assistant, media player, navigation device, email device, game console, tablet computer, wearable device, or a combination of any of these devices.

In a typical configuration, a computer includes one or more processors (CPUs), input/output interfaces, network interfaces, and memory.

The memory may include volatile memory in a computer-readable medium, random Access Memory (RAM) and/or nonvolatile memory, such as Read Only Memory (ROM) or flash memory (flash RAM). Memory is an example of computer-readable media.

Computer readable media, including both non-transitory and non-transitory, removable and non-removable media, may implement information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, or other data. Examples of storage media for a computer include, but are not limited to, phase change memory (PRAM), static Random Access Memory (SRAM), dynamic Random Access Memory (DRAM), other types of Random Access Memory (RAM), read Only Memory (ROM), electrically Erasable Programmable Read Only Memory (EEPROM), flash memory or other memory technology, read only compact disc read only memory (CD-ROM), digital Versatile Discs (DVD) or other optical storage, magnetic cassettes, magnetic disk storage, quantum memory, graphene-based storage or other magnetic storage devices, or any other non-transmission medium, which can be used to store information that can be accessed by the computing device. Computer-readable media, as defined herein, does not include transitory computer-readable media (transmission media), such as modulated data signals and carrier waves.

It should also be noted that the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one … …" does not exclude the presence of other like elements in a process, method, article or apparatus that comprises the element.

The foregoing describes specific embodiments of the present disclosure. Other embodiments are within the scope of the following claims. In some cases, the actions or steps recited in the claims can be performed in a different order than in the embodiments and still achieve desirable results. In addition, the processes depicted in the accompanying figures do not necessarily require the particular order shown, or sequential order, to achieve desirable results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

The terminology used in the one or more embodiments of the specification is for the purpose of describing particular embodiments only and is not intended to be limiting of the one or more embodiments of the specification. As used in this specification, one or more embodiments and the appended claims, the singular forms "a," "an," and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise. It should also be understood that the term "and/or" as used herein refers to and encompasses any or all possible combinations of one or more of the associated listed items.

It should be understood that although the terms first, second, third, etc. may be used in one or more embodiments of the present description to describe various information, these information should not be limited to these terms. These terms are only used to distinguish one type of information from another. For example, first information may also be referred to as second information, and similarly, second information may also be referred to as first information, without departing from the scope of one or more embodiments of the present description. The word "if" as used herein may be interpreted as "at … …" or "at … …" or "responsive to a determination", depending on the context.

The foregoing description of the preferred embodiment(s) is (are) merely intended to illustrate the embodiment(s) of the present invention, and it is not intended to limit the embodiment(s) of the present invention to the particular embodiment(s) described.

Claims

1. An sql code inspection method, the method comprising:

2. The method of claim 1, the acquiring an sql code to be checked; the sql code at least comprises first primary key information in a database corresponding to the sql code, and the method comprises the following steps:

3. The method of claim 2, the traversing the sql code, parsing at least one sql statement from the sql code, and parsing the first primary key information from the at least one sql statement, respectively, comprising:

4. A method according to claim 3, said determining whether the first primary key information is consistent with the second primary key information; if not, generating an error prompt and outputting to a user, including:

5. The method of claim 4, the method further comprising:

6. The method of claim 1, the primary key information comprising a primary key name, a primary key type, and a primary key length;

7. The method of claim 1, the method further comprising:

8. An sql code inspection apparatus, the apparatus comprising:

9. An electronic device comprises a communication interface, a processor, a memory and a bus, wherein the communication interface, the processor and the memory are connected with each other through the bus;

the memory stores machine readable instructions, and the processor performs the method of any of claims 1-7 by invoking the machine readable instructions.

10. A computer readable storage medium storing machine readable instructions which, when invoked and executed by a processor, implement the method of any one of claims 1-7.