Customer support questions come in many forms, some of which are difficult to answer:<\/p>\n
Answering these questions in a satisfactory way is challenging, and a few large tech companies have avoided this challenge by not providing customer service. SQL was built to map human questions to a data model, and while it doesn’t make the above questions easy, it’s embedded in many existing systems, and it makes sense to work with what we have.<\/p>\n
These questions tend to arise when an application applies many rules to achieve it’s results – for instance, Google claims to apply over two hundred factors in it’s ranking algorithms.<\/p>\n
If your product uses a relational database, you can get a lot of mileage from extracting SQL queries from a log, finding what where clauses are included, then documenting the result – a process that might be executed by a helpdesk with heavy technical skills.<\/p>\n
Consider, however, that the technical community has made great advances in our ability to deal with data, so we ought to be able to automate some of this away.<\/p>\n
Consider the following hypothetical query, which searches for files visible to a particular user, with names matching a typed search. While there are better ways to build a system that does this, many do work this way:<\/p>\n
\nSELECT *\nFROM files\nWHERE\n file_name like '%test%' AND\n parent_folder = 'test_folder' AND \n ( owner_user = 'gsieling' OR\n file_groups IN \n ( SELECT name \n FROM groups \n WHERE member_user = 'gsieling' ) )\n<\/pre>\nIn this example, files may be hidden because of any combination of three filters. These are straightforward, and should be pretty simple to explain to a customer (note that we’re not explaining why<\/em>). Rather than filtering rows out, it is helpful to transform this query into an equivalent form, which returns each row and whether it should have been included:<\/p>\n\nSELECT \n (case when file_name like '%test%' then 1 else 0 end) as name_condition,\n (case when parent_folder = 'test_folder' then 1 else 0 end) as folder_condition,\n (case when owner_user = 'gsieling' then 1 else 0 end) as owner_condition,\n (case when file_groups IN \n ( SELECT name \n FROM groups \n WHERE member_user = 'gsieling' ) then 1 else 0 end) as group_condition\nFROM files\n<\/pre>\nOften the first form of these queries are generated by an application layer<\/a>, but the second form is something I’ve not seen used. <\/p>\nSQL is a complex language, but fortunately there are libraries that handle parsing, which fits this problem well, especially if you’re using an ORM, since the queries will follow a pattern. <\/p>\n
A Python library called sql-parse<\/a> can tokenize and tag queries without validating them, which is a perfect fit.<\/p>\nYou can parse a query simply:<\/p>\n
\nstmt = sqlparse.parse(sql)[0]\n<\/pre>\nIf we could use this result to find each section of the WHERE clause, we could trivially turn these into CASE statements:<\/p>\n
def generate_case(token, index):\n return '(case when (' + token + ') then 1 else 0 end) as a' + str(index)<\/pre>\nSqlparse returns parts of the query as a parse tree, with each object marked with attributes identifying the role a token plays in the query. We need to split the query into the part that is in the WHERE clause, and the part that isn’t. Having done that, we’ll reformat the conditions and place them in the SELECT (those with SQL experience will likely object: we’re not handing nested queries, common table expressions, or conditions on JOINs, but consider that this is a thought experiment)<\/p>\n
\ndef remove_where(stmt):\n return [x for x in stmt.flatten() \\\n if not x.within(sql.Where)]\n\ndef get_where(stmt):\n results = [x for x in stmt.tokens \\\n if type(x) is sql.Where]\n\n if len(results) > 0:\n return results[0]\n else: \n return None\n<\/pre>\nThe tricky part is extracting all the conditions – to do this we can recurse through the entire WHERE clause, pulling out each combination of conditions.<\/p>\n
\ndef get_clauses(tokens):\n found = []\n for token in tokens: \n if (token.is_group() and type(token) is sql.Parenthesis):\n found.append(token.to_unicode())\n found.extend(get_clauses(token.tokens))\n elif (token.is_group()):\n found.append(token.to_unicode())\n\n return found\n<\/pre>\nTo put this back together, we need to find the SELECT within the query. This demonstrates the biggest risk of this type of process: there are many ways to write the same query, so the only way this works is if queries are generated consistently by our application.<\/p>\n
def replace_select(tokens, toSelect):\n foundSelect = False\n foundFrom = False\n addedSelect = False\n\n resultSql = ''\n for token in tokens:\n if token.value.lower() == 'from':\n foundFrom = True\n\n if not foundSelect or foundFrom:\n resultSql = resultSql + token.to_unicode()\n else:\n if not addedSelect:\n resultSql = resultSql + ' ' + toSelect + ' '\n addedSelect = True\n\n if token.value.lower() == 'select':\n foundSelect = True\n\n return resultSql<\/pre>\n