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}]}}})]
...)
On Monday, 4 April 2016 19:25:16 UTC+10, Krzysiek Herod wrote:
>
> 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?
>
> On Thursday, March 31, 2016 at 2:59:56 AM UTC+2, Oliver George wrote:
>>
>> 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 <[email protected]>
>>>>> 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 [email protected]
>>>>>> Note that posts from new members are moderated - please be patient
>>>>>> with your first post.
>>>>>> To unsubscribe from this group, send email to
>>>>>> [email protected]
>>>>>> 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
>>>>>> [email protected].
>>>>>> 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 [email protected]
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
[email protected]
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
