Add examples

[orangemug]

Our only examples currently live in ./test and README.md we should put some more complete examples into an ./examples directory

These would show our current use cases, especially with regard to

• Composing SQL via CTE
• Toggling of features via the ternary switch
• Example of a parse error

I suggest we also make a sqlite database in order to properly show these in operation via knex.

[orangemug]

@oliverbrooks this might help with use cases for #13

[oliverbrooks]

One example of a conditional join hack

  {?disablefishFilter, "LEFT JOIN", "INNER JOIN"}
    "fish" ON
      (
        {?disablefishFilter} = false
        AND
        "fish"."foodId" = "food"."id"
      )

This will inner join and filter if disableFishFilter is false.
It will left join nothing if disableFishFilter is true.

This is useful if you need to do a join to filter down results but you don't want the weight of the join all the time.

[orangemug]

@oliverbrooks so I thought I'd have a go at getting a pattern working for the above.

So I think you're trying to always join to a table and then switch to only return rows which join to that table.

I believe the below does this, also it's ANSI SQL (at least I think) and can be prepared and hence optimised ahead of time

SELECT
  *
FROM
  "users"
  LEFT OUTER JOIN "friends" ON (
    "friends"."fromId" = "users"."id"
  )
WHERE
  COALESCE("friends"."id", {?filterOnFriends})

[orangemug]

Work on branch https://github.com/orangemug/sql-stamp/tree/feature/issue-18-add-examples