I just released version 0.3.0 of Relational Mapper. Customization of keys and foreign keys is done now, as well as possibility to specify relation with a different name than the corresponding table (https://github.com/netizer/relational_mapper#different-name-of-an-association-than-a-table-name).
@Oliver George: your example with SupervisorId, AnalystId would work now, but have in mind that postgreSQL by default lowercases column names, so I'd still recommend supervisor_id and analyst_id. Cheers, Krzysiek On Tuesday, March 1, 2016 at 11:35:46 PM UTC+1, Oliver George wrote: > > > Both those ideas seem sensible to me. Look foward to hearing more. > > On Tuesday, 1 March 2016 23:38:43 UTC+11, Krzysiek Herod wrote: >> >> 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 clo...@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+u...@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+u...@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.
