Patent Issued for Automatic Review Of SQL Statement Complexity (USPTO 10,540,256)

2020 JAN 30 (NewsRx) -- By a News Reporter-Staff News Editor at Insurance Daily News -- State Farm Mutual Automobile Insurance Company (Bloomington, Illinois, United States) has been issued patent number 10,540,256, according to news reporting originating out of Alexandria, Virginia, by NewsRx editors.

The patent’s inventors are Snyder, Richard (Colfax, IL); Anderson, Matthew R (Bloomington, IL); Galvin, Daniel (Bloomington, IL).

This patent was filed on November 2, 2018 and was published online on February 3, 2020.

From the background information supplied by the inventors, news correspondents obtained the following quote: “Structured query language (SQL) is a standard computer-based language for accessing a database. Among other functions, SQL may be utilized to insert, query, update, and delete data in a database, create and modify database schema, and control access to the database. Large corporations, such as insurance providers, may maintain a plurality of databases, some of which may store a vast amount of data. Various departments, including accounting, sales, marketing, etc., within the corporation may desire access to the databases in order to provide or retrieve data regarding customers, products, and so forth. Coders, developers, or programmers may write code in SQL to access a database for their particular purpose. However, the coders might not be aware of the amount of resources that their code requires of the database computing system and the computing staff who support the computing system. Computing run time, storage space, and the ability to correct or modify the code in case of an error are all complexity factors that should be considered before allowing SQL code to execute on the database computing system. Furthermore, some coders who are proficient in writing SQL code might not realize that some of the functions they are trying to perform with SQL may be better performed using other programming languages, such as Java, PL1, COBOL, or the like, which do not possess the complexity of SQL code.”

Supplementing the background information on this patent, NewsRx reporters also obtained the inventors’ summary information for this patent: “Embodiments of the present technology relate to computer-implemented methods, computing devices, and computer-readable media for automatically reviewing structured query language (SQL) statements. The embodiments provide for receiving SQL that seeks to access or modify a database. The code is parsed to retrieve SQL keywords, mathematical operators, and logical operators. A complexity score calculation algorithm is applied to the parsed code. The complexity score is calculated and the SQL code is executed on a computing system which stores the database if the complexity score is less than or equal to a first threshold.

“In a first aspect, a computer-implemented method for automatically reviewing structured query language (SQL) statements may be provided. The method may include: (1) receiving SQL code from a user seeking to access a database; (2) parsing the SQL code to retrieve SQL keywords, mathematical operators, and logical operators; (3) applying a complexity score calculation algorithm to the parsed code; (4) calculating a complexity score; and/or (5) executing the SQL code on a computing system which stores the database if the complexity score is less than or equal to a first threshold. The method may include additional, fewer, or alternative actions, including those discussed elsewhere herein.

“In another aspect, a computer-readable medium for automatically reviewing structured query language (SQL) statements may be provided. The computer-readable medium may include an executable program stored thereon, wherein the program instructs a processing element of a computing device to perform the following steps: (1) receiving SQL code from a user seeking to access a database; (2) parsing the SQL code to retrieve SQL keywords, mathematical operators, and logical operators; (3) applying a complexity score calculation algorithm to the parsed code; (4) calculating a complexity score; and/or (5) executing the SQL code on a computing system which stores the database if the complexity score is less than or equal to a first threshold. The program stored on the computer-readable medium may instruct the processing element to perform additional, fewer, or alternative actions, including those discussed elsewhere herein.

“In yet another aspect, a computing device for automatically reviewing structured query language (SQL) statements may be provided. The computing device may include a communication element, a memory element, and a processing element. The communication element may receive and transmit communications to and from a communication network. The memory element may be electronically coupled to the communication element and may store executable instructions. The processing element may be electronically coupled to the communication element and the memory element. The processing element may be configured to receive SQL code from a user seeking to access a database; parse the SQL code to retrieve SQL keywords, mathematical operators, and logical operators; apply a complexity score calculation algorithm to the parsed code; calculate a complexity score; and/or execute the SQL code on a computing system which stores the database if the complexity score is less than or equal to a first threshold. The computing device may include additional, fewer, or alternate components and/or functionality, including that discussed elsewhere herein.

“Advantages of these and other embodiments will become more apparent to those skilled in the art from the following description of the exemplary embodiments which have been shown and described by way of illustration. As will be realized, the present embodiments described herein may be capable of other and different embodiments, and their details are capable of modification in various respects. Accordingly, the drawings and description are to be regarded as illustrative in nature and not as restrictive.”

The claims supplied by the inventors are:

“We claim:

“1. A computer-implemented method for automatically reviewing structured query language (SQL) statements, the computer-implemented method comprising, via one or more processors: parsing SQL code to retrieve SQL keywords, mathematical operators, and logical operators; identifying the version of SQL that was used to create the SQL code; detecting pre-SQL-92 code based on a lack of newer SQL statement syntax; setting a temporary score if pre-SQL-92 code is detected; applying a complexity score calculation algorithm to the parsed SQL code to determine a presence of keywords and operators in the parsed SQL code; calculating a complexity score that varies according to a number of occurrences of keywords or operators and a variable complexity factor; executing the SQL code on a computing system which stores the database if the complexity score is less than or equal to a first threshold; and flagging the SQL code to be reviewed by a computer administrator if the complexity score is greater than the first threshold.

“2. The computer-implemented method of claim 1, the method further comprising: returning the SQL code to an original coder to rewrite at least a portion of the SQL code in a different programming language if the complexity score is greater than a second threshold.

“3. The computer-implemented method of claim 1, wherein the complexity score calculation algorithm calculates a temporary score for the retrieved keywords, mathematical operators, and logical operators.

“4. The computer-implemented method of claim 3, wherein the complexity score is the sum of the temporary scores.

“5. The computer-implemented method of claim 3, wherein the temporary score for each keyword is equal to a product of a number of occurrences of the keyword and a variable complexity factor.

“6. The computer-implemented method of claim 5, wherein the complexity factor has a value greater than 1 for keywords that form the start of an SQL clause.

“7. The computer-implemented method of claim 3, wherein the temporary score for each mathematical operator is equal to a product of a number of occurrences of the mathematical operator and a variable complexity factor or a product of a number of occurrences of the logical operator and a variable complexity factor.

“8. A non-transitory computer-readable medium with an executable program stored thereon for automatically reviewing structured query language (SQL) statements, wherein the program instructs a processing element of a computing device to perform the following: parsing SQL code to retrieve SQL keywords, mathematical operators, and logical operators; identifying the version of SQL that was used to create the SQL code; detecting pre-SQL-92 code based on a lack of newer SQL statement syntax; setting a temporary score if pre-SQL-92 code is detected; applying a complexity score calculation algorithm to the parsed SQL code to determine a presence of keywords and operators in the parsed SQL code; calculating a complexity score that varies according to a number of occurrences of keywords or operators and a variable complexity factor; executing the SQL code on a computing system which stores the database if the complexity score is less than or equal to a first threshold; and flagging the SQL code to be reviewed by a computer administrator if the complexity score is greater than the first threshold.

“9. The non-transitory computer-readable medium of claim 8, wherein the program instructs the processing element of the computing device to further perform the following: returning the SQL code to an original coder to rewrite at least a portion of the SQL code in a different programming language if the complexity score is greater than a second threshold.

“10. The non-transitory computer-readable medium of claim 8, wherein the complexity score calculation algorithm calculates a temporary score for the retrieved keywords, mathematical operators, and logical operators.

“11. The non-transitory computer-readable medium of claim 10, wherein the complexity score is the sum of the temporary scores.

“12. The non-transitory computer-readable medium of claim 10, wherein the temporary score for each keyword is equal to a product of a number of occurrences of the keyword and a variable complexity factor which has a value greater than 1 for keywords that form the start of an SQL clause.

“13. The non-transitory computer-readable medium of claim 10, wherein the temporary score for each mathematical operator is equal to a product of a number of occurrences of the mathematical operator and a variable complexity factor or a product of a number of occurrences of the logical operator and a variable complexity factor.

“14. A computing device for automatically reviewing structured query language (SQL) statements, the device comprising: a communication element configured to receive and transmit communications to and from a communication network; a memory element electronically coupled to the communication element, the memory element configured to store executable instructions; and a processing element electronically coupled to the communication element and the memory element, the processing element configured to-- parse SQL code to retrieve SQL keywords, mathematical operators, and logical operators; identify the version of SQL that was used to create the SQL code; detect pre-SQL-92 code based on a lack of newer SQL statement syntax; set a temporary score if pre-SQL-92 code is detected; apply a complexity score calculation algorithm to the parsed SQL code to determine a presence of keywords and operators in the parsed SQL code; calculate a complexity score that varies according to a number of occurrences of keywords or operators and a variable complexity factor; execute the SQL code on a computing system which stores the database if the complexity score is less than or equal to a first threshold; and flag the SQL code to be reviewed by a computer administrator if the complexity score is greater than the first threshold.

“15. The computing device of claim 14, wherein the processing element is further configured to return the SQL code to an original coder to rewrite at least a portion of the SQL code in a different programming language if the complexity score is greater than the first threshold.

“16. The computing device of claim 14, wherein the complexity score calculation algorithm calculates a temporary score for the retrieved keywords, mathematical operators, and logical operators.

“17. The computing device of claim 16, wherein the complexity score is the sum of the temporary scores.

“18. The computing device of claim 16, wherein the temporary score for each keyword is equal to a product of a number of occurrences of the keyword and a variable complexity factor.

“19. The computing device of claim 18, wherein the complexity factor has a value greater than 1 for keywords that form the start of an SQL clause.

“20. The computing device of claim 16, wherein the temporary score for each mathematical operator is equal to a product of a number of occurrences of the mathematical operator and a variable complexity factor or a product of a number of occurrences of the logical operator and a variable complexity factor.”

For the URL and additional information on this patent, see: Snyder, Richard; Anderson, Matthew R; Galvin, Daniel. Automatic Review Of SQL Statement Complexity. U.S. Patent Number 10,540,256, filed November 2, 2018, and published online on February 3, 2020. Patent URL: http://patft.uspto.gov/netacgi/nph-Parser?Sect1=PTO1&Sect2=HITOFF&d=PALL&p=1&u=%2Fnetahtml%2FPTO%2Fsrchnum.htm&r=1&f=G&l=50&s1=10,540,256.PN.&OS=PN/10,540,256RS=PN/10,540,256

(Our reports deliver fact-based news of research and discoveries from around the world.)