[Haskell-cafe] Typesafe multi-table PostgreSQL queries in Yesod with schema names?

[Boris Lykah]

Hi all,

As the author of Groundhog library I agree that it and
Persistent/Esqueleto have strengths in different areas and none of
them fully matches power of SQL. Grounhog has better support of SQL
expressions and relational schemas (composite keys, schemas, indexes,
etc.) Persistent cannot do a query with projections and nested
function calls like:

project (upper (FirstNameField `append` SecondNameField), toArith
BalanceField * 10) $ lower UserNameField `like` "%smith%"

On the other hand, while Groundhog can query only one table at a time,
Persistent combined with Esqueleto gives relational query capabilities
that match a lot of the SQL syntax (notably excluding column
aliasing).

I like Esqueleto and it seems to be easy to port to Groundhog. The
ported codebase may be even smaller because Groundhog already has
flexible functions and comparison operators which uniformly support
fields and constant values.  Alas, I don't have enough time to do
this. If someone is interested in porting I will be happy to help.

Thanks,
Boris

On Wed, Nov 20, 2013 at 12:11 AM, Manuel Gómez <targen at gmail.com> wrote:
> On Tue, Nov 19, 2013 at 11:20 PM, Erik de Castro Lopo
> <mle+hs at mega-nerd.com> wrote:
>> Manuel Gómez wrote:
>>> *   Persistent: it’s perfectly integrated into Yesod, and together
>>> with Esqueleto, it provides a nice, typesafe and fully expressive EDSL
>>> — but, as far as I can tell, there is no support for specifying schema
>>> names, which is essential for my use case.
>>
>> I'm not sure of what you mean here. DO you mean that you already have
>> a database and schema and you now want to connect Persisent/Esqueleto
>> to it without changing the existing schema?
>
> Hi Erik,
>
> Indeed, that’s more or less the situation I’m facing.  Perhaps the
> overloading of the term “schema” is a problem here: what I
> specifically meant with this point is the namespacing mechanism
> present in (some implementations of) SQL databases: database object
> names are bound within namespaces called schemas, and a name can be
> qualified with the name of the schema, separated by a dot; hence a
> table named `"with spaces"` in a schema/namespace called `"some
> tables"` would be used qualified as `"some tables"."with spaces"`.
>
> I have an existing schema (read: database) with many schemas (read:
> namespaces) and I need to do joins between tables in them.  The names
> have spaces and unusual bits of Unicode, and the dot that separates
> the schema name from the object name mustn’t be quoted, so it’s not
> simply a matter of specifying a complex name to Persistent, as
> escaping and quoting wouldn’t be done correctly.  A workaround could
> be to put everything in a single namespace and use prefixes in names,
> but that causes other issues for me (other tools would break).
>
> The other issue I have with Persistent+Esqueleto is that the database
> I have to work with has some composite or otherwise non–serial-integer
> primary keys, and I understand Persistent requires a serial primary
> key.  I could probably work around this, though.
> _______________________________________________
> Haskell-Cafe mailing list
> Haskell-Cafe at haskell.org
> http://www.haskell.org/mailman/listinfo/haskell-cafe



-- 
Regards,
Boris