[ANN] As I was missing this kind of library in clojure I created relational_mapper for dealing with querying relational databases

[Krzysiek Herod]

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

[James Reeves]

Why "find_all" instead of "find-all"?

- James

--
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 the Google Groups "Clojure" group.
To unsubscribe from this group and stop receiving emails from it, send an email to clojure+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Krzysiek Herod]

Oh, that was only a typo in the documentation and in the post. I changed the README.md, and as there were some other minor changes, I released version 0.2.0.

Thanks James for letting me know :-)

[Oliver George]

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.

[Krzysiek Herod]

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

[Oliver George]

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

[Oliver George]

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.  

[Krzysiek Herod]

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

[Oliver George]

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

[Krzysiek Herod]

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

--

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.

[Oliver George]

Both those ideas seem sensible to me.  Look foward to hearing more.

[Krzysiek Herod]

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

[Oliver George]

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]))

[Krzysiek Herod]

Thanks :-)

I was thinking about using protocols for defining interfaces of different types of databases (e.g. MySQL, PostgreSQL), but with conventions I'm not sure.
Most of the time conventions would not change, and once in a while somebody will want to change only one of them (probably foreign-key). I wouldn't want him to need to define a new record with all the conventions (foreign-key, private-key) in such case. Or did I get it wrong? 

[Oliver George]

I like the idea of DB convention interfaces but that wasn't what I was thinking about specifically.

For tables, fields and relations in an application there may be conventions established by a framework but there are also likely to be exceptions on a table-by-table basis.  An schema interface provides a mechanism to default to a convention but also make an exception when necessary.  (Another case this supports is querying the database schema tables dynamically rather than hard coding)

I have some old code lying around from when I was experimenting with this a few weeks bacl, not saying it's perfect but perhaps it'll explain better.  In this case, pk defaults to :Id but in the case of the user table it is :UserId.

(defrecord SimpleSchema [pks rels]

  IDBSchema
  (pk [_ table-name]
    (get pks table-name :Id))

  (rel [_ from-table rel-name]
    (if-let [[to-table field-mapping] (get-in rels [from-table rel-name])]
      {:from from-table
       :to   to-table
       :on   field-mapping}))

  (has-many?
    [db-schema table-name rel-name]
    (if-let [{:keys [on]} (rel db-schema table-name rel-name)]
      (not= [(pk db-schema table-name)] (vals on)))))

(let [test-schema
      (map->TestSchema
        {:pks  {:Races :Id :Meetings :Id :Users :UserId}
         :rels {:Races    {:Meeting [:Meetings {:MeetingId :Id}]}
                :Meetings {:Races [:Races {:Id :MeetingId}]}}})]

  ...)