lib/pghero/methods/suggested_indexes.rb in pghero-1.4.2 vs lib/pghero/methods/suggested_indexes.rb in pghero-1.5.0
- old
+ new
@@ -186,38 +186,44 @@
def best_index_structure(statement)
return {error: "Too large"} if statement.to_s.length > 10000
begin
- tree = PgQuery.parse(statement).parsetree
+ parsed_statement = PgQuery.parse(statement)
+ v2 = parsed_statement.respond_to?(:tree)
+ tree = v2 ? parsed_statement.tree : parsed_statement.parsetree
rescue PgQuery::ParseError
return {error: "Parse error"}
end
return {error: "Unknown structure"} unless tree.size == 1
tree = tree.first
table = parse_table(tree) rescue nil
unless table
error =
case tree.keys.first
- when "INSERT INTO"
+ when "InsertStmt", "INSERT INTO"
"INSERT statement"
- when "SET"
+ when "VariableSetStmt", "SET"
"SET statement"
+ when "SelectStmt"
+ if (tree["SelectStmt"]["fromClause"].first["JoinExpr"] rescue false)
+ "JOIN not supported yet"
+ end
when "SELECT"
if (tree["SELECT"]["fromClause"].first["JOINEXPR"] rescue false)
"JOIN not supported yet"
end
end
return {error: error || "Unknown structure"}
end
- select = tree["SELECT"] || tree["DELETE FROM"] || tree["UPDATE"]
- where = (select["whereClause"] ? parse_where(select["whereClause"]) : []) rescue nil
+ select = tree.values.first
+ where = (select["whereClause"] ? parse_where(select["whereClause"], v2) : []) rescue nil
return {error: "Unknown structure"} unless where
- sort = (select["sortClause"] ? parse_sort(select["sortClause"]) : []) rescue []
+ sort = (select["sortClause"] ? parse_sort(select["sortClause"], v2) : []) rescue []
{table: table, where: where, sort: sort}
end
def index_covers?(indexed_columns, columns)
@@ -258,54 +264,88 @@
end
end
def parse_table(tree)
case tree.keys.first
+ when "SelectStmt"
+ tree["SelectStmt"]["fromClause"].first["RangeVar"]["relname"]
+ when "DeleteStmt"
+ tree["DeleteStmt"]["relation"]["RangeVar"]["relname"]
+ when "UpdateStmt"
+ tree["UpdateStmt"]["relation"]["RangeVar"]["relname"]
when "SELECT"
tree["SELECT"]["fromClause"].first["RANGEVAR"]["relname"]
when "DELETE FROM"
tree["DELETE FROM"]["relation"]["RANGEVAR"]["relname"]
when "UPDATE"
tree["UPDATE"]["relation"]["RANGEVAR"]["relname"]
end
end
# TODO capture values
- def parse_where(tree)
- aexpr = tree["AEXPR"] || tree[nil]
+ def parse_where(tree, v2 = false)
+ if v2
+ aexpr = tree["A_Expr"]
- if tree["BOOLEXPR"]
- if tree["BOOLEXPR"]["boolop"] == 0
- tree["BOOLEXPR"]["args"].flat_map { |v| parse_where(v) }
+ if tree["BoolExpr"]
+ if tree["BoolExpr"]["boolop"] == 0
+ tree["BoolExpr"]["args"].flat_map { |v| parse_where(v, v2) }
+ else
+ raise "Not Implemented"
+ end
+ elsif aexpr && ["=", "<>", ">", ">=", "<", "<=", "~~", "~~*", "BETWEEN"].include?(aexpr["name"].first["String"]["str"])
+ [{column: aexpr["lexpr"]["ColumnRef"]["fields"].last["String"]["str"], op: aexpr["name"].first["String"]["str"]}]
+ elsif tree["NullTest"]
+ op = tree["NullTest"]["nulltesttype"] == 1 ? "not_null" : "null"
+ [{column: tree["NullTest"]["arg"]["ColumnRef"]["fields"].last["String"]["str"], op: op}]
else
raise "Not Implemented"
end
- elsif tree["AEXPR AND"]
- left = parse_where(tree["AEXPR AND"]["lexpr"])
- right = parse_where(tree["AEXPR AND"]["rexpr"])
- if left && right
- left + right
+ else
+ aexpr = tree["AEXPR"] || tree[nil]
+
+ if tree["BOOLEXPR"]
+ if tree["BOOLEXPR"]["boolop"] == 0
+ tree["BOOLEXPR"]["args"].flat_map { |v| parse_where(v) }
+ else
+ raise "Not Implemented"
+ end
+ elsif tree["AEXPR AND"]
+ left = parse_where(tree["AEXPR AND"]["lexpr"])
+ right = parse_where(tree["AEXPR AND"]["rexpr"])
+ if left && right
+ left + right
+ else
+ raise "Not Implemented"
+ end
+ elsif aexpr && ["=", "<>", ">", ">=", "<", "<=", "~~", "~~*", "BETWEEN"].include?(aexpr["name"].first)
+ [{column: aexpr["lexpr"]["COLUMNREF"]["fields"].last, op: aexpr["name"].first}]
+ elsif tree["AEXPR IN"] && ["=", "<>"].include?(tree["AEXPR IN"]["name"].first)
+ [{column: tree["AEXPR IN"]["lexpr"]["COLUMNREF"]["fields"].last, op: tree["AEXPR IN"]["name"].first}]
+ elsif tree["NULLTEST"]
+ op = tree["NULLTEST"]["nulltesttype"] == 1 ? "not_null" : "null"
+ [{column: tree["NULLTEST"]["arg"]["COLUMNREF"]["fields"].last, op: op}]
else
raise "Not Implemented"
end
- elsif aexpr && ["=", "<>", ">", ">=", "<", "<=", "~~", "~~*", "BETWEEN"].include?(aexpr["name"].first)
- [{column: aexpr["lexpr"]["COLUMNREF"]["fields"].last, op: aexpr["name"].first}]
- elsif tree["AEXPR IN"] && ["=", "<>"].include?(tree["AEXPR IN"]["name"].first)
- [{column: tree["AEXPR IN"]["lexpr"]["COLUMNREF"]["fields"].last, op: tree["AEXPR IN"]["name"].first}]
- elsif tree["NULLTEST"]
- op = tree["NULLTEST"]["nulltesttype"] == 1 ? "not_null" : "null"
- [{column: tree["NULLTEST"]["arg"]["COLUMNREF"]["fields"].last, op: op}]
- else
- raise "Not Implemented"
end
end
- def parse_sort(sort_clause)
- sort_clause.map do |v|
- {
- column: v["SORTBY"]["node"]["COLUMNREF"]["fields"].last,
- direction: v["SORTBY"]["sortby_dir"] == 2 ? "desc" : "asc"
- }
+ def parse_sort(sort_clause, v2)
+ if v2
+ sort_clause.map do |v|
+ {
+ column: v["SortBy"]["node"]["ColumnRef"]["fields"].last["String"]["str"],
+ direction: v["SortBy"]["sortby_dir"] == 2 ? "desc" : "asc"
+ }
+ end
+ else
+ sort_clause.map do |v|
+ {
+ column: v["SORTBY"]["node"]["COLUMNREF"]["fields"].last,
+ direction: v["SORTBY"]["sortby_dir"] == 2 ? "desc" : "asc"
+ }
+ end
end
end
def column_stats(options = {})
schema = options[:schema]