Re: Announce: persistent-hssqlppp 0.1
On Mon, Oct 31, 2011 at 11:34 PM, Ilya Portnov
<ilya.portnov@...> wrote:
> Hello Yesod world.
>
> I'm glad to announce «Persistent-HsSqlPpp», a small package which
> should simplify using complex SQL queries from Persistent (and Yesod).
> As it name says, it uses HsSqlPpp package for SQL parsing and
> manipulations with SQL AST.
>
> persistent-hssqlppp on hackage: http://hackage.haskell.org/package/persistent-hssqlppp
> git repo: http://gitorious.org/persistent-hssqlppp/
> hssqlppp: http://hackage.haskell.org/package/hssqlppp
>
> Main idea is that you describe your queries in usual SQL syntax and
> use persistent-hssqlppp to generate EntityDef and QueryExpr
> declarations from them. Then you pass that EntityDef to usual
> Persistent's functions (like `share') to declare PersistEntity
> instance. QueryExpr represents AST (abstract syntax tree) of your
> query and is passed to selectFromQuery function, which returns list of
> entities, selected by query.
>
> Column types declarations (passed after delimiter ------ line) are
> used to construct valid EntityDef (and then declare a valid
> PersistEntity instance). Some types are inferred automatically, for
> example, COUNT(…) is always Int. But for most columns you need to
> specify types.
>
> Note that usual Persistent selectList, insert, update etc functions
> will not work with declared-from-query entities. For example, let's
> consider you have
>
>> (query, entity) = [persistSql|
>> SELECT d.name AS dept,
>> COUNT(e.id),
>> AVG(e.salary)
>> FROM Employee e, Department d
>> WHERE e.dept = d.id
>> GROUP BY d.name
>> ORDER BY d.name
>> -----------------
>> dept String
>> salary Double
>> |]
>
> …
>
>> share [mkPersist sqlSettings] [entity {entityName = "Stats"}]
>
> Then you can do, for example,
>
>> selectFromQuery query [] :: SqlPersist IO (Either String [Stats])
>
> but you cannot do `selectList [] [] :: SqlPersist IO [Stats]', because
> there is no "Stats" table in the database.
>
> Another limitation is that `persistSql' function (quasiquoter) should
> be called in separate module, in order to pass generated EntityDef to
> `share' function. This is because of GHC stage restriction.
>
> Third limitation is SQL-specific. There is some… hm… feature in SQL:
> if you do, for example, "SELECT table.column AS alias FROM table…",
> then you cannot continue with "… WHERE alias = 25", instead you need
> to write "WHERE table.column = 25". But Persistent (and persistent-
> template) cannot work with qualified column names (you cannot get
> «table.column» and «Table.column» as Haskell identifiers). So, in
> order to work with qualified column names in SELECT queries, you need
> to use column aliases (AS clause). Then persistent-hssqlppp will use
> that aliases as column names (for example, you can get smth like
> «queryAlias» and «QueryAlias»). And then, if you'll use last
> selectFromQuery's argument to add WHERE clause, for example,
>
>> selectFromQuery query [QueryAlias ==. 25]
>
> persistent will generate «SELECT table.column AS alias FROM table
> WHERE alias = 25», which is not valid SQL query :/
>
> So, currently you cannot use aliased columns when adding conditions
> using second argument of selectFromQuery function. But you off cause
> can use any valid WHERE clauses in your SQL queries directly.
>
> WBR, Ilya Portnov.
This looks like a very cool solution for creating complex SQL queries.
Does this work for SQLite, or only PostgreSQL?
Michael
RSS Feed