list.cpp

// LIST SOURCE

#include <regex>

#include "include/list.h"
#include "include/checker.h"

namespace sqlcheck {

// UTILITY

std::string GetTableName(const std::string& sql_statement){
  std::string table_template = "create table";
  std::size_t found = sql_statement.find(table_template);
  if (found == std::string::npos) {
    return "";
  }

  // Locate table name
  auto rest = sql_statement.substr(found + table_template.size());
  // Strip space at beginning
  rest = std::regex_replace(rest, std::regex("^ +| +$|( ) +"), "$1");
  auto table_name = rest.substr(0, rest.find(' '));

  return table_name;
}

bool IsDDLStatement(const std::string& sql_statement){
  std::string create_table_template = "create table";
  std::size_t found = sql_statement.find(create_table_template);
  if (found != std::string::npos) {
    return true;
  }

  std::string alter_table_template = "alter table";
  found = sql_statement.find(alter_table_template);
  if (found != std::string::npos) {
    return true;
  }

  return false;
}

bool IsCreateStatement(const std::string& sql_statement){
  std::string create_table_template = "create table";
  std::size_t found = sql_statement.find(create_table_template);
  if (found != std::string::npos) {
    return true;
  }

  return false;
}

// LOGICAL DATABASE DESIGN


void CheckMultiValuedAttribute(Configuration& state,
                               const std::string& sql_statement,
                               bool& print_statement){

  std::regex pattern("(id\\s+varchar)|(id\\s+text)|(id\\s+regexp)");
  std::string title = "Multi-Valued Attribute";
  PatternType pattern_type = PatternType::PATTERN_TYPE_LOGICAL_DATABASE_DESIGN;

  auto message =
      "● Store each value in its own column and row:  "
      "Storing a list of IDs as a VARCHAR/TEXT column can cause performance and data integrity "
      "problems. Querying against such a column would require using pattern-matching "
      "expressions. It is awkward and costly to join a comma-separated list to matching rows. "
      "This will make it harder to validate IDs. Think about what is the greatest number of "
      "entries this list must support? Instead of using a multi-valued attribute, "
      "consider storing it in a separate table, so that each individual value of that attribute "
      "occupies a separate row. Such an intersection table implements a many-to-many relationship "
      "between the two referenced tables. This will greatly simplify querying and validating "
      "the IDs.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_HIGH,
               pattern_type,
               title,
               message,
               true);

}

void CheckRecursiveDependency(Configuration& state,
                              const std::string& sql_statement,
                              bool& print_statement){

  std::string table_name = GetTableName(sql_statement);
  if(table_name.empty()){
    return;
  }

  std::regex pattern("(references\\s+" + table_name+ ")");
  std::string title = "Recursive Dependency";
  PatternType pattern_type = PatternType::PATTERN_TYPE_LOGICAL_DATABASE_DESIGN;

  auto message =
      "● Avoid recursive relationships:  "
      "It’s common for data to have recursive relationships. Data may be organized in a "
      "treelike or hierarchical way. However, creating a foreign key constraint to enforce "
      "the relationship between two columns in the same table lends to awkward querying. "
      "Each level of the tree corresponds to another join. You will need to issue recursive "
      "queries to get all descendants or all ancestors of a node. "
      "A solution is to construct an additional closure table. It involves storing all paths "
      "through the tree, not just those with a direct parent-child relationship. "
      "You might want to compare different hierarchical data designs -- closure table, "
      "path enumeration, nested sets -- and pick one based on your application's needs.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_HIGH,
               pattern_type,
               title,
               message,
               true);

}

void CheckPrimaryKeyExists(Configuration& state,
                           const std::string& sql_statement,
                           bool& print_statement){

  auto create_statement = IsCreateStatement(sql_statement);
  if(create_statement == false){
    return;
  }

  std::regex pattern("(primary key)");
  std::string title = "Primary Key Does Not Exist";
  PatternType pattern_type = PatternType::PATTERN_TYPE_LOGICAL_DATABASE_DESIGN;

  auto message =
      "● Consider adding a primary key:  "
      "A primary key constraint is important when you need to do the following:  "
      "prevent a table from containing duplicate rows, "
      "reference individual rows in queries, and "
      "support foreign key references "
      "If you don’t use primary key constraints, you create a chore for yourself:  "
      "checking for duplicate rows. More often than not, you will need to define "
      "a primary key for every table. Use compound keys when they are appropriate.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_MEDIUM,
               pattern_type,
               title,
               message,
               false);

}

void CheckGenericPrimaryKey(Configuration& state,
                            const std::string& sql_statement,
                            bool& print_statement){

  auto ddl_statement = IsDDLStatement(sql_statement);
  if(ddl_statement == false){
    return;
  }

  std::regex pattern("(\\s+[\\(]?id\\s+)|(,id\\s+)|(\\s+id\\s+serial)");
  std::string title = "Generic Primary Key";
  PatternType pattern_type = PatternType::PATTERN_TYPE_LOGICAL_DATABASE_DESIGN;

  auto message =
      "● Skip using a generic primary key (id):  "
      "Adding an id column to every table causes several effects that make its "
      "use seem arbitrary. You might end up creating a redundant key or allow "
      "duplicate rows if you add this column in a compound key. "
      "The name id is so generic that it holds no meaning. This is especially "
      "important when you join two tables and they have the same primary "
      "key column name.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_HIGH,
               pattern_type,
               title,
               message,
               true);

}

void CheckForeignKeyExists(Configuration& state,
                           const std::string& sql_statement,
                           bool& print_statement){

  auto create_statement = IsCreateStatement(sql_statement);
  if(create_statement == false){
    return;
  }

  std::regex pattern("(foreign key)");
  std::string title = "Foreign Key Does Not Exist";
  PatternType pattern_type = PatternType::PATTERN_TYPE_LOGICAL_DATABASE_DESIGN;

  auto message =
      "● Consider adding a foreign key:  "
      "Are you leaving out the application constraints? Even though it seems at "
      "first that skipping foreign key constraints makes your database design "
      "simpler, more flexible, or speedier, you pay for this in other ways. "
      "It becomes your responsibility to write code to ensure referential integrity "
      "manually. Use foreign key constraints to enforce referential integrity. "
      "Foreign keys have another feature you can’t mimic using application code:  "
      "cascading updates to multiple tables. This feature allows you to "
      "update or delete the parent row and lets the database takes care of any child "
      "rows that reference it. The way you declare the ON UPDATE or ON DELETE clauses "
      "in the foreign key constraint allow you to control the result of a cascading "
      "operation. Make your database mistake-proof with constraints.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_MEDIUM,
               pattern_type,
               title,
               message,
               false);

}

void CheckVariableAttribute(Configuration& state,
                            const std::string& sql_statement,
                            bool& print_statement){

  std::string table_name = GetTableName(sql_statement);
  if(table_name.empty()){
    return;
  }

  auto found = table_name.find("attribute");
  if (found == std::string::npos) {
    return;
  }

  std::regex pattern("(attribute)");
  std::string title = "Entity-Attribute-Value Pattern";
  PatternType pattern_type = PatternType::PATTERN_TYPE_LOGICAL_DATABASE_DESIGN;

  auto message =
      "● Dynamic schema with variable attributes:  "
      "Are you trying to create a schema where you can define new attributes "
      "at runtime.? This involves storing attributes as rows in an attribute table. "
      "This is referred to as the Entity-Attribute-Value or schemaless pattern. "
      "When you use this pattern,  you sacrifice many advantages that a conventional "
      "database design would have given you. You can't make mandatory attributes. "
      "You can't enforce referential integrity. You might find that attributes are "
      "not being named consistently. A solution is to store all related types in one table, "
      "with distinct columns for every attribute that exists in any type "
      "(Single Table Inheritance). Use one attribute to define the subtype of a given row. "
      "Many attributes are subtype-specific, and these columns must "
      "be given a null value on any row storing an object for which the attribute "
      "does not apply; the columns with non-null values become sparse. "
      "Another solution is to create a separate table for each subtype "
      "(Concrete Table Inheritance). A third solution mimics inheritance, "
      "as though tables were object-oriented classes (Class Table Inheritance). "
      "Create a single table for the base type, containing attributes common to "
      "all subtypes. Then for each subtype, create another table, with a primary key "
      "that also serves as a foreign key to the base table. "
      "If you have many subtypes or if you must support new attributes frequently, "
      "you can add a BLOB column to store data in a format such as XML or JSON, "
      "which encodes both the attribute names and their values. "
      "This design is best when you can’t limit yourself to a finite set of subtypes "
      "and when you need complete flexibility to define new attributes at any time.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_MEDIUM,
               pattern_type,
               title,
               message,
               true);

}

void CheckMetadataTribbles(Configuration& state,
                           const std::string& sql_statement,
                           bool& print_statement){

  auto ddl_statement = IsDDLStatement(sql_statement);
  if(ddl_statement == false){
    return;
  }

  std::regex pattern("[A-za-z\\-_@]+[0-9]+ ");
  std::string title = "Metadata Tribbles";
  PatternType pattern_type = PatternType::PATTERN_TYPE_LOGICAL_DATABASE_DESIGN;

  std::string message1 =
      "● Breaking down a table or column by year/user/etc.:  "
      "You might be trying to split a single column into multiple columns, "
      "using column names based on distinct values in another attribute. "
      "For each year or user, you will need to add one more column or table. "
      "You are mixing metadata with data. You will now need to make sure that "
      "the primary key values are unique across all the split columns or tables. "
      "The solution is to use a feature called sharding or horizontal partitioning. "
      "(PARTITION BY HASH ( YEAR(...) ). With this feature, you can gain the "
      "benefits of splitting a large table without the drawbacks. "
      "Partitioning is not defined in the SQL standard, so each brand of database "
      "implements it in their own nonstandard way. "
      "Another remedy for metadata tribbles is to create a dependent table. "
      "Instead of one row per entity with multiple columns for each year, "
      "use multiple rows. Don't let data spawn metadata.";

  std::string message2 =
      "● Store each value with the same meaning in a single column:  "
      "Creating multiple columns in a table with the same prefix "
      "indicates that you are trying to store a multivalued attribute. "
      "This design makes it hard to add or remove values, "
      "to ensure the uniqueness of values, and handling growing sets of values. "
      "The best solution is to create a dependent table with one column for the "
      "multivalued attribute. Store the multiple values in multiple rows instead of "
      "multiple columns and define a foreign key in the dependent table to associate "
      "the values to its parent row.";

  auto message = message1 + "\n" + message2;

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_MEDIUM,
               pattern_type,
               title,
               message,
               true);

}

// PHYSICAL DATABASE DESIGN

void CheckFloat(Configuration& state,
                const std::string& sql_statement,
                bool& print_statement){

  std::regex pattern("(float)|(real)|(double precision)|(0\\.000[0-9]*)");
  std::string title = "Imprecise Data Type";
  PatternType pattern_type = PatternType::PATTERN_TYPE_PHYSICAL_DATABASE_DESIGN;

  auto message =
      "● Use precise data types:  "
      "Virtually any use of FLOAT, REAL, or DOUBLE PRECISION data types is suspect. "
      "Most applications that use floating-point numbers don't require the range of "
      "values supported by IEEE 754 formats. The cumulative impact of inexact  "
      "floating-point numbers is severe when calculating aggregates. "
      "Instead of FLOAT or its siblings, use the NUMERIC or DECIMAL SQL data types "
      "for fixed-precision fractional numbers. These data types store numeric values "
      "exactly, up to the precision you specify in the column definition. "
      "Do not use FLOAT if you can avoid it.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_MEDIUM,
               pattern_type,
               title,
               message,
               true);

}

void CheckValuesInDefinition(Configuration& state,
                             const std::string& sql_statement,
                             bool& print_statement){

  auto ddl_statement = IsDDLStatement(sql_statement);
  if(ddl_statement == false){
    return;
  }

  std::regex pattern("(enum)|(in \\()");
  std::string title = "Values In Definition";
  PatternType pattern_type = PatternType::PATTERN_TYPE_PHYSICAL_DATABASE_DESIGN;

  auto message =
      "● Don't specify values in column definition:  "
      "With enum, you declare the values as strings, "
      "but internally the column is stored as the ordinal number of the string "
      "in the enumerated list. The storage is therefore compact, but when you "
      "sort a query by this column, the result is ordered by the ordinal value, "
      "not alphabetically by the string value. You may not expect this behavior. "
      "There's no syntax to add or remove a value from an ENUM or check constraint; "
      "you can only redefine the column with a new set of values. "
      "Moreover, if you make a value obsolete, you could upset historical data. "
      "As a matter of policy, changing metadata — that is, changing the definition "
      "of tables and columns—should be infrequent and with attention to testing and "
      "quality assurance. There's a better solution to restrict values in a column:  "
      "create a lookup table with one row for each value you allow. "
      "Then declare a foreign key constraint on the old table referencing "
      "the new table. "
      "Use metadata when validating against a fixed set of values. "
      "Use data when validating against a fluid set of values.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_MEDIUM,
               pattern_type,
               title,
               message,
               true);

}

void CheckExternalFiles(Configuration& state,
                        const std::string& sql_statement,
                        bool& print_statement){

  std::regex pattern("(path varchar)|(unlink\\s?\\()");
  std::string title = "Files Are Not SQL Data Types";
  PatternType pattern_type = PatternType::PATTERN_TYPE_PHYSICAL_DATABASE_DESIGN;

  auto message =
      "● Resources outside the database are not managed by the database:  "
      "It's common for programmers to be unequivocal that we should always "
      "store files external to the database. "
      "Files don't obey DELETE, transaction isolation, rollback, or work well with "
      "database backup tools. They do not obey SQL access privileges and are not SQL "
      "data types. "
      "Resources outside the database are not managed by the database. "
      "You should consider storing blobs inside the database instead of in "
      "external files. You can save the contents of a BLOB column to a file.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_MEDIUM,
               pattern_type,
               title,
               message,
               true);

}

void CheckIndexCount(Configuration& state,
                     const std::string& sql_statement,
                     bool& print_statement){

  auto create_statement = IsCreateStatement(sql_statement);
  if(create_statement == false){
    return;
  }

  std::size_t min_count = 3;
  std::regex pattern("(index)");
  std::string title = "Too Many Indexes";
  PatternType pattern_type = PatternType::PATTERN_TYPE_PHYSICAL_DATABASE_DESIGN;

  auto message =
      "● Don't create too many indexes:  "
      "You benefit from an index only if you run queries that use that index. "
      "There's no benefit to creating indexes that you don't use. "
      "If you cover a database table with indexes, you incur a lot of overhead "
      "with no assurance of payoff. "
      "Consider dropping unnecessary indexes. "
      "If an index provides all the columns we need, then we don't need to read "
      "rows of data from the table at all. Consider using such covering indexes. "
      "Know your data, know your queries, and maintain the right set of indexes.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_MEDIUM,
               pattern_type,
               title,
               message,
               true,
               min_count);

}

void CheckIndexAttributeOrder(Configuration& state,
                              const std::string& sql_statement,
                              bool& print_statement){


  std::regex pattern("(create index)");
  std::string title = "Index Attribute Order";
  PatternType pattern_type = PatternType::PATTERN_TYPE_PHYSICAL_DATABASE_DESIGN;

  auto message =
      "● Align the index attribute order with queries:  "
      "If you create a compound index for the columns, make sure that the query "
      "attributes are in the same order as the index attributes, so that the DBMS "
      "can use the index while processing the query. "
      "If the query and index attribute orders are not aligned, then the DBMS might "
      "be unable to use the index during query processing. "
      "EX: CREATE INDEX TelephoneBook ON Accounts(last_name, first_name); "
      "SELECT * FROM Accounts ORDER BY first_name, last_name;";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true);

}


// QUERY

void CheckSelectStar(Configuration& state,
                     const std::string& sql_statement,
                     bool& print_statement){

  std::regex pattern("(select\\s+\\*)");
  std::string title = "SELECT *";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;

  std::string message1 =
      "● Inefficiency in moving data to the consumer:  "
      "When you SELECT *, you're often retrieving more columns from the database than "
      "your application really needs to function. This causes more data to move from "
      "the database server to the client, slowing access and increasing load on your "
      "machines, as well as taking more time to travel across the network. This is "
      "especially true when someone adds new columns to underlying tables that didn't "
      "exist and weren't needed when the original consumers coded their data access.";

  std::string message2 =
      "● Indexing issues:  "
      "Consider a scenario where you want to tune a query to a high level of performance. "
      "If you were to use *, and it returned more columns than you actually needed, "
      "the server would often have to perform more expensive methods to retrieve your "
      "data than it otherwise might. For example, you wouldn't be able to create an index "
      "which simply covered the columns in your SELECT list, and even if you did "
      "(including all columns [shudder]), the next guy who came around and added a column "
      "to the underlying table would cause the optimizer to ignore your optimized covering "
      "index, and you'd likely find that the performance of your query would drop "
      "substantially for no readily apparent reason.";

  std::string message3 =
      "● Binding Problems:  "
      "When you SELECT *, it's possible to retrieve two columns of the same name from two "
      "different tables. This can often crash your data consumer. Imagine a query that joins "
      "two tables, both of which contain a column called \"ID\". How would a consumer know "
      "which was which? SELECT * can also confuse views (at least in some versions SQL Server) "
      "when underlying table structures change -- the view is not rebuilt, and the data which "
      "comes back can be nonsense. And the worst part of it is that you can take care to name "
      "your columns whatever you want, but the next guy who comes along might have no way of "
      "knowing that he has to worry about adding a column which will collide with your "
      "already-developed names.";

  auto message = message1 + "\n" + message2 + "\n" + message3;

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_HIGH,
               pattern_type,
               title,
               message,
               true);

}

void CheckNullUsage(Configuration& state,
                    const std::string& sql_statement,
                    bool& print_statement) {

  std::regex pattern("(null)");
  std::string title = "NULL Usage";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;

  auto message =
      "● Use NULL as a Unique Value:  "
      "NULL is not the same as zero. A number ten greater than an unknown is still an unknown. "
      "NULL is not the same as a string of zero length. "
      "Combining any string with NULL in standard SQL returns NULL. "
      "NULL is not the same as false. Boolean expressions with AND, OR, and NOT also produce "
      "results that some people find confusing. "
      "When you declare a column as NOT NULL, it should be because it would make no sense "
      "for the row to exist without a value in that column. "
      "Use null to signify a missing value for any data type.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_NONE,
               pattern_type,
               title,
               message,
               true);

}

void CheckNotNullUsage(Configuration& state,
                       const std::string& sql_statement,
                       bool& print_statement) {

  auto create_statement = IsCreateStatement(sql_statement);
  if(create_statement == false){
    return;
  }

  std::regex pattern("(not null)");
  std::string title = "NOT NULL Usage";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;

  auto message =
      "● Use NOT NULL only if the column cannot have a missing value:  "
      "When you declare a column as NOT NULL, it should be because it would make no sense "
      "for the row to exist without a value in that column. "
      "Use null to signify a missing value for any data type.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_NONE,
               pattern_type,
               title,
               message,
               true);

}

void CheckConcatenation(Configuration& state,
                        const std::string& sql_statement,
                        bool& print_statement) {


  std::regex pattern("\\|\\|");
  std::string title = "String Concatenation";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;

  auto message =
      "● Use COALESCE for string concatenation of nullable columns:  "
      "You may need to force a column or expression to be non-null for the sake of "
      "simplifying the query logic, but you don't want that value to be stored. "
      "Use COALESCE function to construct the concatenated expression so that a "
      "null-valued column doesn't make the whole expression become null. "
      "EX: SELECT first_name || COALESCE(' ' || middle_initial || ' ', ' ') || last_name "
      "AS full_name FROM Accounts;";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true);

}

void CheckGroupByUsage(Configuration& state,
                       const std::string& sql_statement,
                       bool& print_statement){

  std::regex pattern("(group by)");
  std::string title = "GROUP BY Usage";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;

  auto message =
      "● Do not reference non-grouped columns:  "
      "Every column in the select-list of a query must have a single value row "
      "per row group. This is called the Single-Value Rule. "
      "Columns named in the GROUP BY clause are guaranteed to be exactly one value "
      "per group, no matter how many rows the group matches. "
      "Most DBMSs report an error if you try to run any query that tries to return "
      "a column other than those columns named in the GROUP BY clause or as "
      "arguments to aggregate functions. "
      "Every expression in the select list must be contained in either an "
      "aggregate function or the GROUP BY clause. "
      "Follow the single-value rule to avoid ambiguous query results.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true);


}

void CheckOrderByRand(Configuration& state,
                      const std::string& sql_statement,
                      bool& print_statement){

  std::regex pattern("(order by rand\\()");
  std::string title = "ORDER BY RAND Usage";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;

  auto message =
      "● Sorting by a nondeterministic expression (RAND()) means the sorting cannot benefit from an index:  "
      "There is no index containing the values returned by the random function. "
      "That’s the point of them being ran- dom: they are different and "
      "unpredictable each time they're selected. This is a problem for the performance "
      "of the query, because using an index is one of the best ways of speeding up "
      "sorting. The consequence of not using an index is that the query result set "
      "has to be sorted by the database using a slow table scan. "
      "One technique that avoids sorting the table is to choose a random value "
      "between 1 and the greatest primary key value. "
      "Still another technique that avoids problems found in the preceding alternatives "
      "is to count the rows in the data set and return a random number between 0 and "
      "the count. Then use this number as an offset when querying the data set. "
      "Some queries just cannot be optimized; consider taking a different approach.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_MEDIUM,
               pattern_type,
               title,
               message,
               true);

}

void CheckPatternMatching(Configuration& state,
                          const std::string& sql_statement,
                          bool& print_statement){

  std::regex pattern("(\blike\b)|(\bregexp\b)|(\bsimilar to\b)");
  std::string title = "Pattern Matching Usage";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;

  auto message =
      "● Avoid using vanilla pattern matching:  "
      "The most important disadvantage of pattern-matching operators is that "
      "they have poor performance. A second problem of simple pattern-matching using LIKE "
      "or regular expressions is that it can find unintended matches. "
      "It's best to use a specialized search engine technology like Apache Lucene, instead of SQL. "
      "Another alternative is to reduce the recurring cost of search by saving the result. "
      "Consider using vendor extensions like FULLTEXT INDEX in MySQL. "
      "More broadly, you don't have to use SQL to solve every problem.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_MEDIUM,
               pattern_type,
               title,
               message,
               true);

}

void CheckSpaghettiQuery(Configuration& state,
                         const std::string& sql_statement,
                         bool& print_statement){

  std::regex true_pattern(".+");
  std::regex false_pattern("pattern must not exist");
  std::regex pattern;

  std::string title = "Spaghetti Query Alert";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;
  std::size_t spaghetti_query_char_count = 500;

  if(sql_statement.size() >= spaghetti_query_char_count){
    pattern = true_pattern;
  }
  else {
    pattern = false_pattern;
  }

  auto message =
      "● Split up a complex spaghetti query into several simpler queries:  "
      "SQL is a very expressive language—you can accomplish a lot in a single query or statement. "
      "But that doesn't mean it's mandatory or even a good idea to approach every task with the "
      "assumption it has to be done in one line of code. "
      "One common unintended consequence of producing all your results in one query is "
      "a Cartesian product. This happens when two of the tables in the query have no condition "
      "restricting their relationship. Without such a restriction, the join of two tables pairs "
      "each row in the first table to every row in the other table. Each such pairing becomes a "
      "row of the result set, and you end up with many more rows than you expect. "
      "It's important to consider that these queries are simply hard to write, hard to modify, "
      "and hard to debug. You should expect to get regular requests for incremental enhancements "
      "to your database applications. Managers want more complex reports and more fields in a "
      "user interface. If you design intricate, monolithic SQL queries, it's more costly and "
      "time-consuming to make enhancements to them. Your time is worth something, both to you "
      "and to your project. "
      "Split up a complex spaghetti query into several simpler queries. "
      "When you split up a complex SQL query, the result may be many similar queries, "
      "perhaps varying slightly depending on data values. Writing these queries is a chore, "
      "so it's a good application of SQL code generation. "
      "Although SQL makes it seem possible to solve a complex problem in a single line of code, "
      "don't be tempted to build a house of cards.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true);

}

void CheckJoinCount(Configuration& state,
                    const std::string& sql_statement,
                    bool& print_statement){

  std::regex pattern("(\bjoin\b)");
  std::string title = "Reduce Number of JOINs";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;
  std::size_t min_count = 5;

  auto message =
      "● Reduce Number of JOINs:  "
      "Too many JOINs is a symptom of complex spaghetti queries. Consider splitting "
      "up the complex query into many simpler queries, and reduce the number of JOINs";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true,
               min_count);

}

void CheckDistinctCount(Configuration& state,
                        const std::string& sql_statement,
                        bool& print_statement){

  std::regex pattern("(\bdistinct\b)");
  std::string title = "Eliminate Unnecessary DISTINCT Conditions";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;
  std::size_t min_count = 5;

  auto message =
      "● Eliminate Unnecessary DISTINCT Conditions:  "
      "Too many DISTINCT conditions is a symptom of complex spaghetti queries. "
      "Consider splitting up the complex query into many simpler queries, "
      "and reduce the number of DISTINCT conditions "
      "It is possible that the DISTINCT condition has no effect if a primary key "
      "column is part of the result set of columns";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true,
               min_count);

}

void CheckImplicitColumns(Configuration& state,
                          const std::string& sql_statement,
                          bool& print_statement){

  std::regex pattern("(insert into \\S+ values)");
  std::string title = "Implicit Column Usage";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;

  auto message =
      "● Explicitly name columns:  "
      "Although using wildcards and unnamed columns satisfies the goal "
      "of less typing, this habit creates several hazards. "
      "This can break application refactoring and can harm performance. "
      "Always spell out all the columns you need, instead of relying on "
      "wild-cards or implicit column lists.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true);

}

void CheckHaving(Configuration& state,
                 const std::string& sql_statement,
                 bool& print_statement){

  std::regex pattern("(\bhaving\b)");
  std::string title = "HAVING Clause Usage";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;

  auto message =
      "● Consider removing the HAVING clause:  "
      "Rewriting the query's HAVING clause into a predicate will enable the "
      "use of indexes during query processing. "
      "EX: SELECT s.cust_id,count(s.cust_id) FROM SH.sales s GROUP BY s.cust_id "
      "HAVING s.cust_id != '1660' AND s.cust_id != '2'; can be rewritten as:  "
      "SELECT s.cust_id,count(cust_id) FROM SH.sales s WHERE s.cust_id != '1660' "
      "AND s.cust_id !='2' GROUP BY s.cust_id;";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true);

}

void CheckNesting(Configuration& state,
                  const std::string& sql_statement,
                  bool& print_statement){

  std::regex pattern("(\bselect\b)");
  std::string title = "Nested sub queries";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;
  std::size_t min_count = 2;

  auto message =
      "● Un-nest sub queries:  "
      " Rewriting nested queries as joins often leads to more efficient "
      "execution and more effective optimization. In general, sub-query unnesting "
      "is always done for correlated sub-queries with, at most, one table in "
      "the FROM clause, which are used in ANY, ALL, and EXISTS predicates. "
      "A uncorrelated sub-query, or a sub-query with more than one table in "
      "the FROM clause, is flattened if it can be decided, based on the query "
      "semantics, that the sub-query returns at most one row. "
      "EX: SELECT * FROM SH.products p WHERE p.prod_id = (SELECT s.prod_id FROM SH.sales "
      "s WHERE s.cust_id = 100996 AND s.quantity_sold = 1 ); can be rewritten as:  "
      "SELECT p.* FROM SH.products p, sales s WHERE p.prod_id = s.prod_id AND "
      "s.cust_id = 100996 AND s.quantity_sold = 1;";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true,
               min_count);


}

void CheckOr(Configuration& state,
                 const std::string& sql_statement,
                 bool& print_statement){

  std::regex pattern("(\bor\b)");
  std::string title = "OR Usage";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;

  auto message =
      "● Consider using an IN predicate when querying an indexed column:  "
      "The IN-list predicate can be exploited for indexed retrieval and also, "
      "the optimizer can sort the IN-list to match the sort sequence of the index, "
      "leading to more efficient retrieval. Note that the IN-list must contain only "
      "constants, or values that are constant during one execution of the query block, "
      "such as outer references. "
      "EX: SELECT s.* FROM SH.sales s WHERE s.prod_id = 14 OR s.prod_id = 17; "
      "can be rewritten as:  "
      "SELECT s.* FROM SH.sales s WHERE s.prod_id IN (14, 17);";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true);

}

void CheckUnion(Configuration& state,
                const std::string& sql_statement,
                bool& print_statement){

  std::regex pattern("(union)");
  std::string title = "UNION Usage";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;

  auto message =
      "● Consider using UNION ALL if you do not care about duplicates:  "
      "Unlike UNION which removes duplicates, UNION ALL allows duplicate tuples. "
      "If you do not care about duplicate tuples, then using UNION ALL would be "
      "a faster option.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true);

}

void CheckDistinctJoin(Configuration& state,
                       const std::string& sql_statement,
                       bool& print_statement){

  std::regex pattern("(distinct.*join)");
  std::string title = "DISTINCT & JOIN Usage";
  PatternType pattern_type = PatternType::PATTERN_TYPE_QUERY;

  auto message =
      "● Consider using a sub-query with EXISTS instead of DISTINCT:  "
      "The DISTINCT keyword removes duplicates after sorting the tuples. "
      "Instead, consider using a sub query with the EXISTS keyword, you can avoid "
      "having to return an entire table. "
      "EX: SELECT DISTINCT c.country_id, c.country_name FROM SH.countries c, "
      "SH.customers e WHERE e.country_id = c.country_id; "
      "can be rewritten to:  "
      "SELECT c.country_id, c.country_name FROM SH.countries c WHERE  EXISTS "
      "(SELECT 'X' FROM  SH.customers e WHERE e.country_id = c.country_id);";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true);

}



// APPLICATION

void CheckReadablePasswords(Configuration& state,
                            const std::string& sql_statement,
                            bool& print_statement){

  std::regex pattern("(password varchar)|(password text)|(password =)| "
      "(pwd varchar)|(pwd text)|(pwd =)");
  std::string title = "Readable Passwords";
  PatternType pattern_type = PatternType::PATTERN_TYPE_APPLICATION;

  auto message =
      "● Do not store readable passwords:  "
      "It’s not secure to store a password in clear text or even to pass it over the "
      "network in the clear. If an attacker can read the SQL statement you use to "
      "insert a password, they can see the password plainly. "
      "Additionally, interpolating the user's input string into the SQL query in plain text "
      "exposes it to discovery by an attacker. "
      "If you can read passwords, so can a hacker. "
      "The solution is to encode the password using a one-way cryptographic hash  "
      "function. This function transforms its input string into a new string, "
      "called the hash, that is unrecognizable. "
      "Use a salt to thwart dictionary attacks. Don't put the plain-text password "
      "into the SQL query. Instead, compute the hash in your application code, "
      "and use only the hash in the SQL query.";

  CheckPattern(state,
               sql_statement,
               print_statement,
               pattern,
               RISK_LEVEL_LOW,
               pattern_type,
               title,
               message,
               true);

}

}  // namespace machine