Hi, After struggling with Hibernate, SQL dsl, then bare SQL for a couple of years we took a radical path away from these approaches.
We do not in our world own the database models we are dealing with, keeping our heads above water (or mud you might say) has become a survival issue. So we took the problem the other way around. How can we make the relational models compliant with our business model ? We are in the health industry, a patient is a patient, why do we have to struggle with the fine prints of each vendor's relational model ? We implemented and EDN subset library in several SQL db dialects (Oracle, mysql, SqlServer support will come out before spring). Implementing an EDN library is between 250 to 300 lines of SQL code per database type so far. It's purely functional and returns a single column (entity) and a string EDN representation. We just edn/read-string in the code and we get our business entity. What remains are semantic issues with the data itself but this already addressed by a component above this in our product stack. To avoid writing the views by hand, we created a viewer generator that creates according to the db dialect a view returning an EDN representation of the business entity as we manipulate it in the code, not as it is defined in the database. One view == one business entity. The generator is db aware through some protocol magic and can handle different db brands. The mapping between the db model and our business model is defined as data. It contains the fields to extract, their types if not strings, the joins, the selection criterias, the db dialect, .... This defines our business model vs the relational model. The generator is less than 300 lines of code, entity definitions can be between 50 to 200 lines. Add to this the yesql library to wrap queries now in a single file. It shrank our sql queries to a ridiculous number per vendor while pushing away the complexity of the mapping of the relational model away from the code as configurable data. As for inserts, updates, ... views are of no help here but the generator can bridge the gap by creating sql statements and small field name translation wrappers from the same definition as the views. The parameter names we use remain coherent with the business model the code uses. The sql statements to insert, ... are again wrapped by yesql. Performançe so far is not an issue, we run our product on small boxes less powerful than our customer's database servers. Shifting the load on the side of the database server made sense in this regard. And away from this relational modeling crap. Give the same business domain entities to ten different DBAs and you will get ten relational models each with it's own nit picking subtleties. Luc P. > Hey everyone, > > We've been exploring ways to make working with database code more efficient > and less error prone. > For complex queries, we prefer working directly with SQL. However, like for > many others, a lot of our > queries are very simple and repetitive. For example, retrieving or updating > single rows, or a set of rows > based on a foreign key. > > As an experiment, we wrote a prototype that uses the information_schema > standard to automatically > extract the schema from a database and represent it as clojure code at > compile time. With this, we > were able to synthesize some simple SQL queries. The interesting part of > this is that the code generator > automatically picks up primary key constraints and also performs validation > on table and column names. > All of this is done at compile time. Errors are caught early and the > compiled code uses clojure.java.jdbc > prepared statements. You can find the code and demo here: > > https://github.com/diligenceengine/edl > > I'm personally not a big fan of huge ORM systems, so I don't know where to > go with this, if anywhere. > Though it seems useful for building small macros for common patterns we > have. > > Would love to hear if anyone has thoughts on the technique. > > Alex > > > > -- > -- > You received this message because you are subscribed to the Google > Groups "Clojure" group. > To post to this group, send email to clojure@googlegroups.com > Note that posts from new members are moderated - please be patient with your > first post. > To unsubscribe from this group, send email to > clojure+unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/clojure?hl=en > --- > You received this message because you are subscribed to the Google Groups > "Clojure" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to clojure+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/groups/opt_out. > -- Softaddicts<lprefonta...@softaddicts.ca> sent by ibisMail from my ipad! -- -- You received this message because you are subscribed to the Google Groups "Clojure" group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en --- You received this message because you are subscribed to the Google Groups "Clojure" group. To unsubscribe from this group and stop receiving emails from it, send an email to clojure+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
