Add support for the WITH clause

[marmphco]

Hello! I have been using SQLite.swift in one of my projects and so far it's been working great. Just one thing is missing that my project needs: the WITH clause. The WITH clause provides the ability to do hierarchical or recursive queries of tree and graph-like data. See https://www.sqlite.org/lang_with.html.

Thanks in advance for reviewing 🙂

API changes

• Add an all parameter to QueryType.union to allow UNION ALL to be used in a query. UNION ALL allows duplicate rows in the result, which may help improve performance for large recursive queries. I opted to add the parameter to the start of the list so that it does not dangle at the end when the union's query is long:

  users.union(all: true, posts.join(users, on: users[id] == posts[userId]))
  // I think it's a little easier to read than:
  users.union(posts.join(users, on: users[id] == posts[userId]), all: true)

• Add with function to QueryType. This function adds a WITH clause to a query. The function may be called multiple times to add multiple clauses to a query. If multiple clauses are added to the query with conflicting recursive parameters, the whole WITH clause will be considered recursive.

  Like the union function, I put the query parameter at the end so that the recursive and materializationHint options don't dangle at the end of a long query.

  let users = Table("users")
  let users = Table("posts")
  let first = Table("first")
  let second = Table("second")
  first.with(first, recursive: true as: users).with(second, recursive: false, as: posts)
  // WITH RECURSIVE "first" AS (SELECT * from users), "second" AS (SELECT * from posts) SELECT * from "first"

Other

I left one linter warning unfixed: there's a file length violation in Query.swift. The file is 519 lines long, 19 over the limit. I'm not sure how to bring the line length back down below 500 without harming readability or moving a lot of stuff around (maybe you do?). Any suggestions on what to do here?

[jberkel]

@marmphco thanks for the PR. the simplest solution for the linter problem would be to start splitting Query into functional extensions, for example Query+with.swift.

[marmphco]

@jberkel Good point, I'll try that out.