Re: Extracting a database schema and using it to synthesize and check SQL queries at compile time
Hi Alex, On Wednesday, 8 January 2014 13:28:29 UTC+5:30, Alexander Hudek wrote: 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. I totally agree with this. I have noticed this is the most common scenario when working with SQL databases. 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. The approach to read the database to generate code is pretty interesting. There is a more portable way to extract the schema information, using DatabaseMetadata that you can extract from a Connection. http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#getMetaData() Shantanu -- -- 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.
Re: Extracting a database schema and using it to synthesize and check SQL queries at compile time
The approach to read the database to generate code is pretty interesting. There is a more portable way to extract the schema information, using DatabaseMetadata that you can extract from a Connection. http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#getMetaData() I did some related work quite a while ago: https://github.com/kumarshantanu/clj-jdbcutil/blob/master/src/clj_jdbcutil/core.clj#L535 Sharing just in case somebody finds it useful. Shantanu -- -- 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.
Re: Extracting a database schema and using it to synthesize and check SQL queries at compile time
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. -- Softaddictslprefonta...@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
Re: Extracting a database schema and using it to synthesize and check SQL queries at compile time
This looks interesting. Hopefully it's a more consistent interface than information_schema. I'll try it out, thanks! On Wednesday, January 8, 2014 3:32:49 AM UTC-5, Shantanu Kumar wrote: The approach to read the database to generate code is pretty interesting. There is a more portable way to extract the schema information, using DatabaseMetadata that you can extract from a Connection. http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#getMetaData() I did some related work quite a while ago: https://github.com/kumarshantanu/clj-jdbcutil/blob/master/src/clj_jdbcutil/core.clj#L535 Sharing just in case somebody finds it useful. Shantanu -- -- 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.
Re: Extracting a database schema and using it to synthesize and check SQL queries at compile time
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
