entrouvert
/
wcs-olap
16
0
Fork 0
Code Issues Pull Requests Releases Activity

keep case in tables' and fields' names (#34559)

This commit is contained in:
Benjamin Dauvergne 2019-07-06 14:26:30 +02:00
parent da5435384d
commit 1d3705a7b1
4 changed files with 56 additions and 47 deletions
Show Stats Download Patch File Download Diff File Expand all files Collapse all files

3
tests/conftest.py
View File

@ -93,7 +93,8 @@ formdef.fields = [
fields.ItemField(id='2', label='2nd field', type='item',
items=['foo', 'bar', 'baz'], varname='item'),
fields.BoolField(id='3', label='3rd field', type='bool', varname='bool'),
fields.ItemField(id='4', label='4rth field', type='item', varname='item_open'),
fields.ItemField(id='4', label='4rth field', type='item', varname='itemOpen'),
fields.StringField(id='5', label='5th field', type='string', anonymise=False, varname='stringCaseSensitive'),
]
formdef.store()

33
tests/olap.model
View File

@ -37,12 +37,12 @@
"join" : [
"formdef"
],
"label" : "formulaire",
"name" : "formdef",
"order_by" : "formdef.label",
"type" : "integer",
"value" : "formdef.id",
"value_label" : "formdef.label"
"label": "formulaire",
"name": "formdef",
"order_by": "formdef.label",
"type": "integer",
"value": "formdef.id",
"value_label": "formdef.label"
},
{
"join" : [
@ -268,13 +268,20 @@
{
"filter" : true,
"join" : [
"item_open"
"itemOpen"
],
"label" : "4rth field",
"name" : "item_open",
"name" : "itemOpen",
"type" : "integer",
"value" : "\"item_open\".id",
"value_label" : "\"item_open\".label"
"value" : "\"itemOpen\".id",
"value_label" : "\"itemOpen\".label"
},
{
"filter": true,
"label": "5th field",
"name": "stringCaseSensitive",
"type": "string",
"value": "\"field_stringCaseSensitive\""
}
],
"fact_table" : "formdata_demande",
@ -343,9 +350,9 @@
},
{
"detail" : "id",
"master" : "field_item_open",
"name" : "item_open",
"table" : "formdata_demande_field_item_open"
"master" : "field_itemOpen",
"name" : "itemOpen",
"table" : "formdata_demande_field_itemOpen"
}
],
"key" : "id",

13
tests/test_wcs.py
View File

@ -57,12 +57,13 @@ def test_wcs_fixture(wcs, postgres_db, tmpdir, olap_cmd, caplog):
('formdata_demande', 'field_string'),
('formdata_demande', 'field_item'),
('formdata_demande', 'field_bool'),
('formdata_demande', 'field_item_open'),
('formdata_demande', 'field_itemOpen'),
('formdata_demande', 'field_stringCaseSensitive'),
('formdata_demande', 'function__receiver'),
('formdata_demande_field_item', 'id'),
('formdata_demande_field_item', 'label'),
('formdata_demande_field_item_open', 'id'),
('formdata_demande_field_item_open', 'label'),
('formdata_demande_field_itemOpen', 'id'),
('formdata_demande_field_itemOpen', 'label'),
('formdef', 'id'),
('formdef', 'category_id'),
('formdef', 'label'),
@ -130,7 +131,7 @@ def test_dimension_stability(wcs, wcs_dir, postgres_db, tmpdir, olap_cmd, caplog
c.execute('SELECT * FROM formdata_demande_field_item ORDER BY id')
refs = c.fetchall()
assert len(refs) == 3
c.execute('SELECT * FROM formdata_demande_field_item_open ORDER BY id')
c.execute('SELECT * FROM "formdata_demande_field_itemOpen" ORDER BY id')
open_refs = c.fetchall()
assert len(open_refs) == 3
@ -181,7 +182,7 @@ formdata.store()
assert new_refs[-1][1] == 'bazouka'
bazouka_id = new_refs[-1][0]
c.execute('SELECT * FROM formdata_demande_field_item_open ORDER BY id')
c.execute('SELECT * FROM "formdata_demande_field_itemOpen" ORDER BY id')
new_open_refs = c.fetchall()
assert len(new_open_refs) == 4
for ref in open_refs:
@ -189,7 +190,7 @@ formdata.store()
assert new_open_refs[-1][1] == 'open_new_value'
open_new_id = new_open_refs[-1][0]
c.execute('''SELECT field_item, field_item_open
c.execute('''SELECT field_item, "field_itemOpen"
FROM formdata_demande ORDER BY id''')
formdata = c.fetchone()
assert formdata[0] == bazouka_id

54
wcs_olap/feeder.py
View File

@ -345,13 +345,13 @@ CREATE TABLE public.dates AS (SELECT
AS the_date(the_date));''')
def create_table(self, name, columns, inherits=None, comment=None):
sql = 'CREATE TABLE %s' % name
sql += '(' + ', '.join('%s %s' % (n, t) for n, t in columns) + ')'
sql = 'CREATE TABLE "%s"' % name
sql += '(' + ', '.join('"%s" %s' % (n, t) for n, t in columns) + ')'
if inherits:
sql += ' INHERITS (%s)' % inherits
sql += ' INHERITS ("%s")' % inherits
self.ex(sql)
if comment:
self.ex('COMMENT ON TABLE %s IS %%s' % name, vars=(comment,))
self.ex('COMMENT ON TABLE "%s" IS %%s' % name, vars=(comment,))
def prev_table_exists(self, name):
query = """SELECT EXISTS (SELECT 1 FROM information_schema.tables
@ -366,12 +366,12 @@ CREATE TABLE public.dates AS (SELECT
if self.prev_table_exists(name):
# Insert data from previous table
self.ex(
'INSERT INTO {schema_temp}.{name} SELECT * FROM {schema}.{name}',
'INSERT INTO {schema_temp}."{name}" SELECT * FROM {schema}."{name}"',
ctx={'name': name}
)
# Update sequence
self.ex("""SELECT setval(pg_get_serial_sequence('{name}', 'id'),
(SELECT GREATEST(1, MAX(id)) FROM {name}))""", ctx={'name': name})
self.ex("""SELECT setval(pg_get_serial_sequence('"{name}"', 'id'),
(SELECT GREATEST(1, MAX(id)) FROM "{name}"))""", ctx={'name': name})
def create_labeled_table(self, name, labels, comment=None):
self.create_table(
@ -384,20 +384,20 @@ CREATE TABLE public.dates AS (SELECT
if self.prev_table_exists(name):
# Insert data from previous table
self.ex(
'INSERT INTO {schema_temp}.{name} select * FROM {schema}.{name}',
'INSERT INTO {schema_temp}."{name}" select * FROM {schema}."{name}"',
ctx={'name': name}
)
# Find what is missing
to_insert = []
for _id, _label in labels:
self.ex(
'SELECT * FROM {name} WHERE label = %s', ctx={'name': name}, vars=(_label,))
'SELECT * FROM "{name}" WHERE label = %s', ctx={'name': name}, vars=(_label,))
if self.cur.fetchone() is None:
to_insert.append(_label)
labels = None
if to_insert:
self.ex('SELECT MAX(id) FROM {name}', ctx={'name': name})
self.ex('SELECT MAX(id) FROM "{name}"', ctx={'name': name})
next_id = (self.cur.fetchone()[0] or 0) + 1
ids = range(next_id, next_id + len(to_insert))
labels = zip(ids, to_insert)
@ -408,11 +408,11 @@ CREATE TABLE public.dates AS (SELECT
if labels:
tmpl = ', '.join(['(%s, %s)'] * len(labels))
query_str = 'INSERT INTO {name} (id, label) VALUES %s' % tmpl
query_str = 'INSERT INTO "{name}" (id, label) VALUES %s' % tmpl
self.ex(query_str, ctx={'name': name}, vars=list(itertools.chain(*labels)))
res = {}
self.ex("SELECT id, label FROM %s" % str(name))
self.ex('SELECT id, label FROM "{name}"', ctx={'name': name})
for id_, label in self.cur.fetchall():
res[label] = id_
return res
@ -458,10 +458,10 @@ CREATE TABLE public.dates AS (SELECT
self.create_labeled_table('status', self.status,
comment=u'statuts simplifiés')
self.ex('CREATE TABLE {form_table} (id serial PRIMARY KEY,'
self.ex('CREATE TABLE "{form_table}" (id serial PRIMARY KEY,'
' category_id integer REFERENCES {category_table} (id),'
' label varchar)')
self.ex('COMMENT ON TABLE {form_table} IS %s', vars=(u'types de formulaire',))
self.ex('COMMENT ON TABLE "{form_table}" IS %s', vars=(u'types de formulaire',))
# agents
self.create_labeled_table_serial('agent', comment=u'agents')
@ -630,7 +630,7 @@ class WcsFormdefFeeder(object):
else:
# open item field, from data sources...
self.create_labeled_table_serial(table_name, comment=comment)
field_def = 'smallint REFERENCES %s (id)' % table_name
field_def = 'smallint REFERENCES "%s" (id)' % table_name
elif field.type == 'bool':
field_def = 'boolean'
elif field.type == 'string':
@ -661,7 +661,7 @@ class WcsFormdefFeeder(object):
self.create_table('{formdata_table}', columns, inherits='{generic_formdata_table}',
comment=u'formulaire %s' % self.formdef.schema.name)
for at, comment in comments.iteritems():
self.ex('COMMENT ON COLUMN {formdata_table}.%s IS %%s' % at, vars=(comment,))
self.ex('COMMENT ON COLUMN "{formdata_table}"."%s" IS %%s' % at, vars=(comment,))
# Creat index for JSON fields
if self.has_jsonb:
@ -676,28 +676,28 @@ class WcsFormdefFeeder(object):
continue
i = _type.index('REFERENCES')
constraint = '%s_fk_constraint FOREIGN KEY (%s) %s' % (name, name, _type[i:])
self.ex('ALTER TABLE {formdata_table} ADD CONSTRAINT %s' % constraint)
self.ex('ALTER TABLE {formdata_table} ADD PRIMARY KEY (id)')
self.ex('ALTER TABLE "{formdata_table}" ADD CONSTRAINT %s' % constraint)
self.ex('ALTER TABLE "{formdata_table}" ADD PRIMARY KEY (id)')
# table des evolutions
self.create_table('{evolution_table}', [
['id', 'serial primary key'],
['status_id', 'smallint REFERENCES {status_table} (id)'],
['formdata_id', 'integer REFERENCES {formdata_table} (id)'],
['status_id', 'smallint REFERENCES "{status_table}" (id)'],
['formdata_id', 'integer REFERENCES "{formdata_table}" (id)'],
['time', 'timestamp'],
['date', 'date'],
['hour_id', 'smallint REFERENCES {hour_table} (id)'],
])
self.ex('COMMENT ON TABLE {evolution_table} IS %s',
self.ex('COMMENT ON TABLE "{evolution_table}" IS %s',
vars=(u'evolution des demandes %s' % self.formdef.schema.name,))
def insert_item_value(self, field, value):
table_name = self.hash_table_name('{formdata_table}_field_%s' % field.varname)
self.ex("SELECT id FROM {item_table} WHERE label = %s",
self.ex('SELECT id FROM "{item_table}" WHERE label = %s',
ctx={'item_table': table_name}, vars=(value,))
res = self.cur.fetchone()
if res:
return res[0]
self.ex('INSERT INTO {item_table} (label) VALUES (%s) RETURNING (id)', vars=(value,),
self.ex('INSERT INTO "{item_table}" (label) VALUES (%s) RETURNING (id)', vars=(value,),
ctx={'item_table': table_name})
return self.cur.fetchone()[0]
@ -836,8 +836,8 @@ class WcsFormdefFeeder(object):
if not values:
self.logger.warning('no data')
return
self.ex('INSERT INTO {formdata_table} ({columns}) VALUES {values} RETURNING id',
ctx=dict(columns=', '.join(self.columns[1:]), values=', '.join(values)))
self.ex('INSERT INTO "{formdata_table}" ({columns}) VALUES {values} RETURNING id',
ctx=dict(columns=', '.join(['"%s"' % column for column in self.columns[1:]]), values=', '.join(values)))
# insert generic evolutions
generic_evolutions = []
@ -863,12 +863,12 @@ class WcsFormdefFeeder(object):
row[0] = formdata_id
evolutions.append(tuple(row))
if len(evolutions) == 500:
self.ex('INSERT INTO {evolution_table} (%s) VALUES %s' % (
self.ex('INSERT INTO "{evolution_table}" (%s) VALUES %s' % (
', '.join(['formdata_id', 'status_id', 'time', 'date', 'hour_id']),
', '.join(['%s'] * len(evolutions))), vars=evolutions)
evolutions = []
if evolutions:
self.ex('INSERT INTO {evolution_table} (%s) VALUES %s' % (
self.ex('INSERT INTO "{evolution_table}" (%s) VALUES %s' % (
', '.join(['formdata_id', 'status_id', 'time', 'date', 'hour_id']),
', '.join(['%s'] * len(evolutions))), vars=evolutions)