Fantastic. Keep up the good work. Schema conventions could be made flexible with a protocol.
(defprotocol IDBSchema (pk [_ table-name]) (rel [_ table-name rel-name]) (has-many? [_ table-name rel-name])) On Thursday, 31 March 2016 09:23:00 UTC+11, Krzysiek Herod wrote: > > 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.
