On Tuesday, August 14, 2012 04:36:36 AM Tero Laitinen wrote:
I'm intending to use an RDBMS ...
Which one ?
Hello,I recently started using Yesod in a commercial context. So far I've been very pleased with the framework. However, I'm intending to use an RDBMS as the database backend, so the non-relation Database.Persist layer falls a bit short. HaskellDB seems to be more suitable to use with a relational database. I read an alarming post (http://chrisdone.com/posts/2011-11-06-haskelldb-tutorial.html) commenting about its bugs, so I don't know yet which database layer to tap into.I'll describe a bit of my use case, so my approach hopefully makes more sense. I'm building data intensive applications that can have dozens or even hundreds of tables. I want to automate most database tasks like database migrations (including indexes), validation of fields' data, versioning, per-user-limited-database-views, etc. Also, I may need to access the database from a Python or C++ application, so building on top of Template Haskell might incur additional troubles. Also, I have no idea on how to write a EDSL with Haskell, so at the moment I'm considering a high-level DSL and a code generator to do the heavy lifting.I imagine that there is a number of people who might benefit from a library that integrates Yesod tighter to an RDBMS, there could be potential for fruitful collaboration.I did some preliminary hacking experimenting with a DSL aimed at defining a database in a modular way. The parser for the work-in-progress DSL is at https://github.com/tlaitinen/enterdsl .There's not much to see, so I explain the core ideas here.The database schema is composed of database definition modules. Let's call them dbdef-files from now on. There are three kind of objects in a dbdef-file: entities, relations, and interfaces. The first two are easier to understand with some background of the ER-model while the third one offers a polymorphic interface to a number of entities,For example, the top level dbdef-file can be following.import "module.dbdef"; -- entities and interfaces from "mobule.dbdef" can now be usedentity Bar {-- string field with a default value, and whenever-- the field value is changed, the function longer3 has to return Truestring name (default "foo", check longer3);-- one-to-one relation between Bar and Foo. The identifier 'bar' in parentheses-- is "attached" to the entity "Foo", e.g. Foo.bar can be used to retrieve the-- corresponding Bar.Foo foo (bar);-- defines a unique index with one fieldunique index (name);}entity Foo {-- implements-keyword is used to inherit all fields and relations-- of an interface, see belowimplements Versioned;optional string name;}interface Versioned {-- this field is embedded in every table implementing the interfaceinteger version;}-- a "relation" keyword specifies typically a many-to-many relationship-- implemented as a single table, but when used with interfaces,-- it can also be a one-to-many relationship the relation.-- If an interface is used in the relation, the relation is implemented-- using a number of tables - one for each implements-instance.relation Changes {-- in this case, there will be a table referencing Foo and ChangeRecord-- since Foo implements Versioned-interface.-- the notation [] in parentheses denotes the (many-)multiplicity of the relation.-- Foo.changes can now be used to retrieve all ChangeRecord's of a number of Foo entityiesVersioned versioned ([] changes);ChangeRecord change;}entity ChangeRecord {string field:string oldValue;string newValue;}relation FooBar {Foo foo ([] fooBars);Bar bar (bar);}Please bear with the syntax. It was just easier to write a parser with explicit blocks and statements. The idea is that the database interfaces can also be used to perform operations on objects implementing an interface, e.g.- selecting some objects implementing a specific interface- creating "business layers" on top of the database, e.g. defining a layer to update an entity implementing Versioned-interface or to display an entity implementing a Renderable-interface.I can already confess that I have not extensively searched for related work on this, so I would like to hear your comments on:- related work- how Yesod's RDBMS capabilities could be enhanced and whether there is any work-in-progress on this- which database layer should be used for this- whether template Haskell or a separate DSL is the way to go- potential pitfalls of the proposed entity/relation/interface paradigmBest Regards,Tero Laitinen
I've actually been kicking around some ideas in my head for a more powerful SQL generation library built on top of Persistent. I considered trying to get it written for 1.0, but I ran out of time and didn't yet have a completely fleshed-out design. Here's a sketch of what I've been thinking of, maybe it will be a good basis for some brainstorming.I first wanted to start off with a way to select individual fields. I was thinking of some kind of function that you could call like:rows <- runQuery $ singleTable (PersonName, PersonAge) [PersonName !=. "foo"] [Asc PersonAge]print (rows :: [(Text, Int)])Next was going to be some way to perform joins between two tables based on explicitly state IDs:let personQuery = singleTable PersonName [] []carQuery = singleTable EntireEntity [] []fullQuery = innerJoin personQuery carQuery PersonId CarOwnerrows <- runQuery fullQueryprint (rows :: [(Text, Car)])That initial design doesn't really support querying the same table multiple times, which is a limitation I'd like to overcome. I'd also like to think about a way to implement aggregates and HAVING clauses. But as a core concept, I think Persistent's existing infrastructure might be able to get us pretty far.Michael