Using Mobility with JSON backend and MySQL 5.7.22 in production

[enthusiasmus]

Although the documentation doesn't mention one can use the json backend with mysql, we are using it already in production. It works as if we were using postgres. Maybe you can add MySQL with JSON backend to your test suite and update the documentation?

Mobility version: 0.6.0
Mobility backend: :json

Thanks,
best regards
Lukas

[shioyama]

Wow, that's really interesting! Don't often get issues for features that you didn't know are already done. Out of curiosity, do you use querying on the json columns as well?

[shioyama]

Wow, so it seems that indeed, the syntax for querying mysql columns is almost identical to pg (or at least, MySQL has aliases that make it behave the same way). As of 5.7.13, you can use column ->> path, which is what Mobility uses for the Json backend. So I could imagine everything working out-of-the-box, which is really nice.

Ideally I'd like to add tests for MySQL before adding it to the docs, not sure Travis will work and locally I use MariaDB which I believe is not compatible with MySQL JSON, or at least works differently... in any case, could you confirm whether querying does indeed work for you? That would make a difference in whether to list it on the wiki/readme or not.

[enthusiasmus]

Yep, searching works like expected. Here is a query example:

mysql> SELECT label, label->"$.de" FROM spree_site_links;
+------------------------------------+---------------+
| label                              | label->"$.de" |
+------------------------------------+---------------+
| {"de": "Kontakt", "fr": "contact"} | "Kontakt"     |
| {"de": "Presse"}                   | "Presse"      |
| {"de": "Impressum"}                | "Impressum"   |
| {"de": "AGB"}                      | "AGB"         |
| {"de": "Anbieter"}                 | "Anbieter"    |
| {"de": "Events"}                   | "Events"      |
+------------------------------------+---------------+
6 rows in set (0.00 sec)

mysql> SELECT label, label->"$.de" FROM spree_site_links WHERE JSON_EXTRACT(label, "$.de") = "Kontakt";
+------------------------------------+---------------+
| label                              | label->"$.de" |
+------------------------------------+---------------+
| {"de": "Kontakt", "fr": "contact"} | "Kontakt"     |
+------------------------------------+---------------+
1 row in set (0.00 sec)```

[shioyama]

Ok, added a note to the readme and one to the wiki as well. If we can get this combination backend/db tested, I can make support for MySQL JSON official.

[luismiv85]

Hi everyone!
I just tried to use json backend, but I have some problem and maybe any of you can help me :)

Mysql: 5.7.15
Rails: 5.2.0
Ruby: 2.5.0

I just configured the backend as :json and my models as documentations describe it.
Example:

class Page < ApplicationRecord
  extend Mobility
  translates :title,            type: :string
  translates :slug,             type: :string
  translates :meta_title,       type: :string
  translates :meta_description, type: :string
  translates :meta_key,         type: :string
  translates :navigation_title, type: :string
end

When I am going to create a new Page on this way:

Page.create(title: 'Página de inicio', active: true, draft: false)

Something is wrong with create. I am trying on console rails:

 => #<Page id: nil, title: {"es"=>"Página de inicio"}, slug: {}, meta_title: {}, meta_description: {}, meta_key: {}, view_template: "home", layout_template: "stone_theme", active: true, draft: false, ancestry: nil, string: nil, navigation_title: {}, show_in_menu: false, position: nil, created_at: nil, updated_at: nil>
2.5.0 :004 > page.save!
   (0.3ms)  BEGIN
   (0.2ms)  ROLLBACK
Traceback (most recent call last):
        1: from (irb):4
**TypeError (Cannot visit Mobility::Arel::Nodes::Json)**

Some help please.
PD: Sorry for my english, I am not enought good already :)

[shioyama]

Sorry MySQL is not supported yet for the json backend, so I can't help you with this. I think that everything other than querying should work, but there are no tests for this case so I really can't know.

[luismiv85]

Hi @shioyama, thanks so much!

If I have time I can investigate to add support to mysql. Any recomendation for starting to do it?
Thanks!

[shioyama]

Actually the easiest starting point would be figuring out how to set up tests with MySQL json on Travis. There are shared examples that run against any backend. Right now they are only run against postgres, which you can see here at the top of the spec. If you remove that constraint (i.e. remove the db: :postgres, specs will all fail on mysql (because currently mysql has no json support), but if you can setup travis with support for json then some specs may start to pass... at least that would be the starting point.