base_sparse_field evolution

[rvalyi]

@florian-dacosta @renatonlima @sebastienbeau
So it turns out that a way to fit our numerous kind of Brazilian fiscal documents into Odoo account invoice (lines), we better use the sparse field concept much like when working with Magento EAV model. So I did a quick investigation what it might take to upgrade our spare_field module and use JSONB Postgres data type:

from https://www.citusdata.com/blog/2016/07/14/choosing-nosql-hstore-json-jsonb/
and https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/
we see that we need the JSONB format (not hstore or JSON).

The module evolution should happen mostly in these lines:
https://github.com/akretion/ak-odoo-incubator/blob/8.0/base_sparse_field/models/fields.py#L81
with the json load/dump stuff.

These basic python/psql experiments I did may help us shape the right code:
see also see basic pyscopg2 usage http://initd.org/psycopg/docs/usage.html
and json type casting http://initd.org/psycopg/docs/extras.html#adapt-json

psql db

CREATE TABLE cards (
  data jsonb
);

python

import psycopg2
conn = psycopg2.connect("dbname=db user=odoo")
cur = conn.cursor()

import json
j=json.loads('["foo", {"bar":["baz", null, 1.0, 2]}]')

from psycopg2.extras import Json
j2=Json(j)

cur.execute("insert into cards (data) values (%s)", (j2,))
conn.commit()

psql db

db=# select * from cards
;
                  data                   
-----------------------------------------
 ["foo", {"bar": ["baz", null, 1.0, 2]}]
(1 row)

[florian-dacosta]

@rvalyi
The advantage of the jsonb field instead of hstore is not clear for me.
The few tests I made (not much really) show that the queries on hstore column seems a bit faster than jsonb. Same if we add index on some keys of the columns.
The thing is, for sparse field, we don't really something as complete ans powerful than jsonb, just a key=>value system like hstore is all we need.

Anyway, I am not against using jsonb either, if there are helpers with pyscopg2.
Actually, I guess we should choose the column type that will be the easiest to use with Odoo.

That said, I don't really see how we can implement this in a separate module.
For instance, if we have a field "data" on sale_order, with key 'a' and 'b' (which would be sparse field).
We'd like Odoo to read field a, it should make a query like "SELECT data -> 'a' FROM sale_order". Instead of "SELECT a FROM sale_order".
From what I saw, it happens here https://github.com/OCA/OCB/blob/11.0/odoo/models.py#L2613
and I don't see how we could override this.
It is just an example, but the where clause should also be complicated to override for instance.
My worry is that Odoo won't ever accept these changes in the orm, it will only accept it if it is in a separate module, as they did for version 11.