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 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.
