Postgres Materialized CTE functions

[joelmccracken]

Adds withMaterialized function to support this non-standard postgres behavior.

Before submitting your PR, check that you've:

• [x] Bumped the version number.
• [x] Documented new APIs with Haddock markup.
• [x] Added @since declarations to the Haddock.
• [x] Ran stylish-haskell and otherwise adhered to the style guide.

After submitting your PR:

• [x] Update the Changelog.md file with a link to your PR.
• [ ] Check that CI passes (or if it fails, for reasons unrelated to your change, like CI timeouts).

[joelmccracken]

fixes #345

[belevy]

Is there a way that we can avoid more postgres specific stuff in the internal module? Since NOT MATERIALIZED comes after the AS could we just prepend it to the query? Perhaps we just need a more generic type CTEModifier = IdentInfo -> TLB.Builder

[joelmccracken]

we could somehow insert it? the key part i think is:

    cteClauseToText (CommonTableExpressionClause _ cteMat cteIdent cteFn) =
        first
            (\tlb -> useIdent info cteIdent <> " AS " <> materializedText cteMat <> parens tlb)
            (cteFn info)

and the materializedText cteMat is the offending portion; the thing i'm really just unsure of is how to "insert" something there without like having an parameter to CommonTableExpressionClause which is like afterAsModifier which would let something else also insert something after the AS before the subquery. If you prefer that, i'll do it, It just also feels inelegant to me =(

[belevy]

Yah so the whole point is to keep that modifier text open for extension. Its obviously less safe from an implementation perspective but who is to say that MySQL won't come out with their own custom modifiers. You have a couple of options here that are all pretty distateful each in their own way.

1. Strict seperation of the SqlQuery type by backend. Each backend has a completely unique SqlQuery type, this is then the subject of typeclasses to support code reuse. This is trading strict correctness checking in the types for potential duplication in the implementation as much of the query language is standardized with a few changes.

2. Parameterize a SqlQuery by its constituent parts (i.e. data SqlQuery cteClause fromClause whereClause ... =). This has the nice effect of letting you ignore the differing parts when they don't matter. It however still has some major duplication issues that show up the minute you need to make a custom clause type and for total safety you will end up needing to create custom clauses for each part.

3. Create a single unified type that has all of the cases for all of the backends baked in, this is what esqueleto generally has done in the past. You have a single sum type that needs to handle all of the different possible backends. This is super simple but has a nasty downside in that it drags everything into the internal module, since the type is not open for extension from a different module we end up needing to modify the mega sum type, this also means that you don't really have safety to prevent mixing statements that are specific to different backends and also means you have to rely on all of the backends always (it is currently impossible to break esqueleto-core, esqueleto-postgres, esqueleto-mysql, and esqueleto-sqlite out into independent packages).

4. The approach that I have been tending towards for use in libraries lately which is something akin to a monomorphic tagless final. In this approach there is a single SqlQuery type that is the final representation of a query (or near enough) and has extension points where the backends differ. This way we can share almost all of the code and only have to change exactly what differs, this still requires the internal module to know about how backends might wish to customize the core type but it frees us of the compile time dependency. This approach is very much like OOP where you can envision all of the functions producing SqlQuery as classes that implement a SqlQuery interface ala the interpreter pattern. This has a nasty downside of removing compile time help from the implementer but I have felt that it ended up making far simpler code when I switched the Experimental module to this style.

So to answer your question, yes leaving the modifier as a free for all is kind of icky but the end user won't see it only the implementer.

[joelmccracken]

@belevy thank you for explaining. I think I got it, and have addressed your comments.

Let me know if there is anything else to address!

[joelmccracken]

I ran stylish-haskell on the files that were modified in this PR so I could mark off that check list item, just in case it caused anyone pause. For reference, however, it failed for me on Internal.hs.

for f in $(git diff --name-only origin/master | grep '.hs'); do stack exec stylish-haskell -- -i $f || continue ; done
src/Database/Esqueleto/Internal/Internal.hs: RealSrcSpan SrcSpanPoint "src/Database/Esqueleto/Internal/Internal.hs" 427 1: lexical error in string/character literal at character 's'