Hey Luc, Our use case is quite a bit different. We treat our database schema as part of our applications data model. In contrast, it seems that your problem is one of data integration. That's a much more difficult problem to solve.
If I understand correctly, you have a code that generates a database specific view (e.g. oracle, sql server) that maps a vendors schema to your own schema based on a manually specified mapping? Seems like a reasonable solution. Unrelated to this demo, I've actually been involved in a research project creating a similar technique where we describe schemas and their mappings in formal logic, then have a compiler that can create high performance plans based on logic proofs. Thus you can write queries against your common logical schema and automatically generate plans over a different physical schema. Sadly, the project is not at a state that we can release quite yet. Hopefully one day though. On Wednesday, January 8, 2014 4:07:01 AM UTC-5, Luc wrote: > > 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 clo...@googlegroups.com<javascript:> > > Note that posts from new members are moderated - please be patient with > your first post. > > To unsubscribe from this group, send email to > > clojure+u...@googlegroups.com <javascript:> > > 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+u...@googlegroups.com <javascript:>. > > For more options, visit https://groups.google.com/groups/opt_out. > > > -- > Softaddicts<lprefo...@softaddicts.ca <javascript:>> 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.
