Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 128132
Status: new
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: mark [...] markandruth.co.uk
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



Subject: JSON access support
Date: Sat, 29 Dec 2018 23:28:03 +0300
To: bug-DBIx-Class [...] rt.cpan.org
From: Mark Zealey <mark [...] markandruth.co.uk>
Download (untitled) / with headers
text/plain 1.8k
I have a module that I use internally for generating JSON dereferencing in postgres (col->'foo'->0->>'bar'). I would like to publish this as a standalone helper module or perhaps integrate into DBIC core depending on what you think of it. MySQL has similar syntax (col->>'$.foo[0].bar'), and something similar could probably implemented using LHS functions in sqlite and other databases (although I am only aware of Pg that can do true index functionality on these type of queries). The syntax I am currently using is: $rs->search({ 'col::foo.0.bar::type' => 123 }) As in Pg most of these require type casts to be able to do the compare. Additionally specifying a type of json(b) generates col->'foo'->'bar' which means you can do more advanced Pg queries like: $rs->search({ 'col::foo.0.bar::json' => { '@>' => ... }) The code for this is not too difficult, just some SQLMaker extensions. I have the following questions: 1) is the above syntax reasonable or would you suggest something different? I don't think this type of syntax would be a problem for most existing db schema? Given the difference between key names and array offsets I'd think that the mysql syntax of []s around array offsets may be better. ::'s are obviously inspired by postgres, but it may be worth choosing a different identifier (-> ?) so that in the future support for pg LHS casting could be added. So that would mean syntax would be something like ->search({ 'col->foo[0].bar::type' => ... }). 2) What are the chances of getting this extension into the core dbic SQLMaker code ? It's currently only about 50 lines for Pg/Mysql support. 3) The one issue I have found so far is that in order to enable joins to work correctly I need to do a s/::.*// on the key names in DBIx::Class::Relationship::HasOne::_validate_has_one_condition . Hence asking about 1/2 as I'd rather avoid having to patch that routine on the fly... Mark
Subject: Re: [rt.cpan.org #128132] AutoReply: JSON access support
Date: Sun, 30 Dec 2018 11:43:46 +0300
To: bug-DBIx-Class [...] rt.cpan.org
From: Mark Zealey <mark [...] markandruth.co.uk>
Download (untitled) / with headers
text/plain 280b
(to say, this functionality is in order to support the increasingly popular NoSQL-type usage of postgres per https://markandruth.co.uk/2015/12/08/mongo-db-pain and https://markandruth.co.uk/2016/01/08/how-we-tweaked-postgres-upsert-performance-to-be-2-3-faster-than-mongodb )


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.