I went through the paper very briefly, so I might be wrong, but from the first look it seems like the algorithm would generate the actual SQL queries . If so, although the idea seems interesting, I wouldn't go in this direction because of the loss of flexibility for the user of the library. For example sometimes it happens, that the slowest SQL query called by the application is the one where database picked a sub-optimal index, or sometimes combining data by adding one more join has a great performance impact.
Actually I was thinking about giving the programmer more flexibility, and maybe splitting the whole code into query part and stitch part, so the developer would choose the most efficient queries, but the stitching part would put all those data together (with deep result structure). I'm curious what do you think about this direction. I'll comment on your issue ( https://github.com/netizer/relational_mapper/issues/3) with more details about the idea. Cheers, Krzysiek On Tue, Mar 1, 2016 at 6:03 AM, Oliver George <oli...@condense.com.au> wrote: > Awesome, thanks. > > I did a little research last night looking for techniques for turning > recursive queries into efficient SQL queries. I came across an interesting > paper: > > Cheney, James, Sam Lindley, and Philip Wadler. "Query shredding: Efficient > relational evaluation of queries over nested multisets (extended > version)."*arXiv > preprint arXiv:1404.7078* (2014). > > > The details are obscured behind some intimidating equations but the > concept seems pretty simple: The nested query gets normalised and then > shredded into a set of sql queries and the results of those queries are > stitched back together. > > There seem to be two version > <https://scholar.google.com.au/scholar?hl=en&q=Query+shredding%3A+Efficient+relational+evaluation+of+queries+over+nested+multisets+%28extended+version%29&btnG=&as_sdt=1%2C5&as_sdtp=> > of the paper. This one looks to be more detailed (26 pages): > > https://scholar.google.com/citations?view_op=view_citation&hl=en&user=Iz-3VFQAAAAJ&sortby=pubdate&citation_for_view=Iz-3VFQAAAAJ:9pM33mqn1YgC > > > > > On Monday, 29 February 2016 21:06:23 UTC+11, Krzysiek Herod wrote: >> >> Thanks a lot for detailed notes. >> >> The problem with customization of foreign keys is on my TODO list. I hope >> to fix that before releasing version 1.0. That would solve the problem with >> SupervisorId and AnalystId. >> >> What you said about deeper result structure (race -> meeting -> venue) is >> very inspiring. You can't do that with this library (you can fetch records >> with their - potentially indirect - relations, but those relations won't >> have own relations included), but definitely it's something worth >> considering. I added it to my TODO list in the README but I don't have a >> clear idea about how to do it well yet. >> >> Cheers, >> Krzysiek >> >> On Monday, February 29, 2016 at 12:54:31 PM UTC+8, Oliver George wrote: >>> >>> Oops, one more. >>> >>> There was also a Users table (Id, Username, ...) >>> >>> I didn't see a way to handle join from Races to Users based on >>> SupervisorId and AnalystId. >>> >>> >>> On Monday, 29 February 2016 15:52:48 UTC+11, Oliver George wrote: >>>> >>>> Thanks for the details. >>>> >>>> I did a little experimenting and it works as advertised. Notes below >>>> show what I did and found. >>>> >>>> I was interested to see if this might be suitable as a simple om.next >>>> remote for a relational database. Potentially fanciful but it's a topic of >>>> interest for me at the moment. >>>> >>>> I used an existing database so I had a semi interesting dataset to play >>>> with. >>>> >>>> Races (Id, RaceNumber, RaceTime, MeetingId, SupervisorId, AnalystId...) >>>> Meetings (Id, MeetingDate, MeetingTypeId, VenueId, JurisdictionId, ...) >>>> Venues (Id, Name) >>>> Jurisdiction (Id, Name, Code) >>>> >>>> >>>> The table and foreign key naming conventions didn't match so I created >>>> views for each table. If that was configurable then you'd open yourself to >>>> a wider audience. (e.g. MeetingId vs meetings_id) >>>> >>>> It was easy to setup some associations >>>> >>>> (def associations >>>> {:meeting {:race :has-many >>>> :jurisdiction :belongs-to >>>> :venue :belongs-to} >>>> :race {:meeting :belongs-to >>>> :jurisdiction [:through :meeting :belongs-to]} >>>> :venue {}}) >>>> >>>> My queries all worked as expected. >>>> >>>> (find-one db-state :meeting #{:race} [[:= :meeting.id 5617]]) >>>> (find-one db-state :meeting #{:venue} [[:= :meeting.id 5617]]) >>>> (find-one db-state :race #{:meeting :jurisdiction} [[:= :race.id >>>> 42792]]) >>>> >>>> I couldn't see how I might pull data which requires three levels of >>>> information (e.g. race -> meeting -> venue). I didn't dig deep enough to >>>> be sure. >>>> >>>> Incidentally, in case you haven't come across the datomic pull inspired >>>> om.next remote pull syntax this is what it might look like: >>>> >>>> [{:meeting [:race]}] >>>> (find-one db-state :meeting #{:race} []) >>>> >>>> [({:meeting [:race]} [:= :meeting.id 5617])] >>>> (find-one db-state :meeting #{:race} [[:= :meeting.id 5617]]) >>>> >>>> [{:meeting [:venue]}] >>>> (find-one db-state :meeting #{:venue} [[:= :meeting.id 5617]]) >>>> >>>> [{:race [{:meeting [{:venue :jurisdiction}]}]}] >>>> >>>> Not prettier necessarily but allows for composing multiple queries into >>>> a request and for drilling deeper into available data. >>>> >>>> cheers, Oliver >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> On Sunday, 28 February 2016 20:02:15 UTC+11, Krzysiek Herod wrote: >>>>> >>>>> Thanks Oliver for the feedback, >>>>> >>>>> actually I came up with the idea of relational_mapper while working on >>>>> a project in which I had one "data-model" that contained all the database >>>>> related information, but the database related code contained a lot of >>>>> features, and I really like working with small, focused clojure libraries, >>>>> so in the end relational_mapper is as small as I could think of it. >>>>> >>>>> Also as you can see in this commit: >>>>> https://github.com/netizer/relational_mapper/commit/6b4d79f92570bf723e4092d329978d484c01d2ab#diff-2b44df73d826687086fd1972295f8bd0L8 >>>>> I actually was storing both: relations and fields in the same structure, >>>>> but I changed that because I needed "fields" only for migrations that I >>>>> used in tests, and because the whole structure was unnecessarily complex >>>>> (it was much easier to make mistake modifying the fields/associations >>>>> structure). >>>>> >>>>> Relational Mapper is meant only for reading data because whenever I >>>>> tried to use complex structures to write data, I was unhappy with the >>>>> result (often you have to update indexes of related records after one of >>>>> them - with auto-increment field - is created, and there is a problem of >>>>> determining if the related record has to be created or updated). >>>>> >>>>> I didn't write compare/contrast points because I couldn't find similar >>>>> libraries in clojure. I mentioned ActiveRecord in README mostly because of >>>>> the wording in types of relations, but even ActiveRecord is very far from >>>>> Relational Mapper (it's much bigger, and has features that go way beyond >>>>> simple relational mapping). >>>>> >>>>> Thanks again, >>>>> Krzysiek >>>>> >>>>> On Sunday, February 28, 2016 at 10:54:57 AM UTC+8, Oliver George wrote: >>>>>> >>>>>> >>>>>> Seems pretty nice to me. Like a light weight version of the Django's >>>>>> migrate and queryset features which build on model definitions. >>>>>> >>>>>> It seems like this would allow me to define a database schema >>>>>> (tables, relations and fields) as data and use it to both create the >>>>>> database and run select/insert/update/delete queries against it. >>>>>> >>>>>> Is that your intention for the library? >>>>>> >>>>>> I've not explored the options in this space before. It might be good >>>>>> to have a section in the README pointing out to other related tools with >>>>>> some compare/contrast points. >>>>>> >>>>>> Thanks. >>>>>> >>>>>> >>>>>> On Friday, 26 February 2016 17:51:10 UTC+11, Krzysiek Herod wrote: >>>>>>> >>>>>>> I created Relational Mapper, for situations where there is a >>>>>>> relational database with certain amount of relations between tables and >>>>>>> it's just not cool to fetch data from each table separately nor to write >>>>>>> custom code for each such project so, with this library, you can just >>>>>>> call: >>>>>>> >>>>>>> (find_all db-state :posts #{:authors :attachments} [:= post.id 1]) >>>>>>> >>>>>>> and assuming you have appropriate relations between these tables, >>>>>>> you'll get: >>>>>>> >>>>>>> {:posts {:title "Christmas" >>>>>>> :body "Merry Christmas!" >>>>>>> :id 1 >>>>>>> :authors_id 10 >>>>>>> :authors {:name "Rudolf" :id 10} >>>>>>> :attachments [{:name "rudolf.png" :id 100 :posts_id 1} >>>>>>> {:name "santa.png" :id 101 :posts_id 1}] >>>>>>> >>>>>>> >>>>>>> The code is here: https://github.com/netizer/relational_mapper >>>>>>> >>>>>>> Please, guys, let me know what do you think, and if you have any >>>>>>> ideas about improvements. If somebody would be so kind to take a look at >>>>>>> the code, it would be awesome to read some feedback. >>>>>>> >>>>>>> Krzysiek Heród >>>>>>> >>>>>> -- > 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 a topic in the > Google Groups "Clojure" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/clojure/g6Yxk-o6_rQ/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > clojure+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- 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/d/optout.
