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

quote all templated field and table names (#36503)

This commit is contained in:
Benjamin Dauvergne 2019-09-28 11:01:50 +02:00
parent 8e60ac10d1
commit 9698d7852a
2 changed files with 66 additions and 61 deletions
Show Stats Download Patch File Download Diff File Expand all files Collapse all files

12
tests/olap.model
View File

@ -158,7 +158,7 @@
"type" : "percent"
},
{
"expression" : "array_agg(\"{fact_table}\".geolocation_base) FILTER (WHERE \"{fact_table}\".geolocation_base IS NOT NULL)",
"expression" : "array_agg({fact_table}.geolocation_base) FILTER (WHERE {fact_table}.geolocation_base IS NOT NULL)",
"label" : "localisation géographique",
"name" : "geolocation",
"type" : "point"
@ -284,7 +284,7 @@
"value": "\"field_stringCaseSensitive\""
}
],
"fact_table" : "formdata_demande",
"fact_table" : "\"formdata_demande\"",
"joins" : [
{
"detail" : "date",
@ -338,7 +338,7 @@
},
{
"detail" : "id",
"master" : "function__receiver",
"master" : "\"function__receiver\"",
"name" : "function__receiver",
"table" : "role"
},
@ -346,13 +346,13 @@
"detail" : "id",
"master" : "\"field_item\"",
"name" : "item",
"table" : "formdata_demande_field_item"
"table" : "\"formdata_demande_field_item\""
},
{
"detail" : "id",
"master" : "\"field_itemOpen\"",
"name" : "itemOpen",
"table" : "formdata_demande_field_itemOpen"
"table" : "\"formdata_demande_field_itemOpen\""
}
],
"key" : "id",
@ -389,7 +389,7 @@
"type" : "percent"
},
{
"expression" : "array_agg(\"{fact_table}\".geolocation_base) FILTER (WHERE \"{fact_table}\".geolocation_base IS NOT NULL)",
"expression" : "array_agg({fact_table}.geolocation_base) FILTER (WHERE {fact_table}.geolocation_base IS NOT NULL)",
"label" : "localisation géographique",
"name" : "geolocation",
"type" : "point"

115
wcs_olap/feeder.py
View File

@ -14,6 +14,10 @@ from cached_property import cached_property
from wcs_olap.wcs_api import WcsApiError
def quote(name):
return '"%s"' % name
def slugify(s):
return s.replace('-', '_').replace(' ', '_')
@ -250,7 +254,8 @@ class WcsOlapFeeder(object):
'name': 'geolocation',
'label': 'localisation géographique',
'type': 'point',
'expression': 'array_agg("{fact_table}".geolocation_base) FILTER (WHERE "{fact_table}".geolocation_base IS NOT NULL)',
'expression': 'array_agg({fact_table}.geolocation_base) '
'FILTER (WHERE {fact_table}.geolocation_base IS NOT NULL)',
}
]
}
@ -344,7 +349,7 @@ CREATE TABLE public.dates AS (SELECT
AS the_date(the_date));''' % last_date.strftime('%Y-%m-%d'))
def create_table(self, name, columns, inherits=None, comment=None):
sql = 'CREATE TABLE "%s"' % name
sql = 'CREATE TABLE %s' % quote(name)
sql += '(' + ', '.join('"%s" %s' % (n, t) for n, t in columns) + ')'
if inherits:
sql += ' INHERITS ("%s")' % inherits
@ -365,12 +370,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}"',
ctx={'name': name}
'INSERT INTO {schema_temp}.{name} SELECT * FROM {schema}.{name}',
ctx={'name': quote(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': quote(name)})
def create_labeled_table(self, name, labels, comment=None):
self.create_table(
@ -383,20 +388,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}"',
ctx={'name': name}
'INSERT INTO {schema_temp}.{name} select * FROM {schema}.{name}',
ctx={'name': quote(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': quote(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': quote(name)})
next_id = (self.cur.fetchone()[0] or 0) + 1
ids = range(next_id, next_id + len(to_insert))
labels = zip(ids, to_insert)
@ -407,11 +412,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
self.ex(query_str, ctx={'name': name}, vars=list(itertools.chain(*labels)))
query_str = 'INSERT INTO {name} (id, label) VALUES %s' % tmpl
self.ex(query_str, ctx={'name': quote(name)}, vars=list(itertools.chain(*labels)))
res = {}
self.ex('SELECT id, label FROM "{name}"', ctx={'name': name})
self.ex('SELECT id, label FROM {name}', ctx={'name': quote(name)})
for id_, label in self.cur.fetchall():
res[label] = id_
return res
@ -457,10 +462,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')
@ -552,13 +557,13 @@ CREATE TABLE public.dates AS (SELECT
self.agents_mapping[user.id] = self.insert_agent(user.name)
return self.agents_mapping[user.id]
def create_formdata_json_index(self, varname):
def create_formdata_json_index(self, table_name, varname):
if varname in self.formdata_json_index:
return
index_name = self.hash_table_name('{formdata_table}_%s_json_idx' % varname,
hash_length=8, force_hash=True)
index_name = self.hash_table_name('%s_%s_json_idx' % (table_name, varname), hash_length=8,
force_hash=True)
self.ex('CREATE INDEX {index_name} ON {generic_formdata_table} (("json_data"->>%s))',
ctx={'index_name': index_name}, vars=[varname])
ctx={'index_name': quote(index_name)}, vars=[varname])
# prevent double creation
self.formdata_json_index.append(varname)
@ -601,7 +606,7 @@ class WcsFormdefFeeder(object):
self.formdef.schema.name])
self.formdef_sql_id = self.cur.fetchone()[0]
columns = [['status_id', 'smallint REFERENCES "{status_table}" (id)']]
columns = [['status_id', 'smallint REFERENCES {status_table} (id)']]
comments = {}
@ -623,7 +628,7 @@ class WcsFormdefFeeder(object):
if field.type == 'item':
comment = (u'valeurs du champ « %s » du formulaire %s'
% (field.label, self.formdef.schema.name))
table_name = self.hash_table_name('{formdata_table}_field_%s' % field.varname)
table_name = self.hash_table_name('%s_field_%s' % (self.table_name, field.varname))
# create table and mapping
if field.items:
self.items_mappings[field.varname] = self.create_labeled_table(
@ -659,16 +664,16 @@ class WcsFormdefFeeder(object):
name for name, _type in columns])
self.columns.remove('geolocation_base')
self.create_table('{formdata_table}', columns, inherits='{generic_formdata_table}',
self.create_table(self.table_name, 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' % quote(at), vars=(comment,))
# Creat index for JSON fields
if self.has_jsonb:
for field in fields:
if field.varname and '-' not in field.varname:
self.create_formdata_json_index(field.varname)
self.create_formdata_json_index(self.table_name, field.varname)
# PostgreSQL does not propagate foreign key constraints to child tables
# so we must recreate them manually
@ -677,29 +682,29 @@ 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}', [
self.create_table(self.evolution_table_name, [
['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',
ctx={'item_table': table_name}, vars=(value,))
table_name = self.hash_table_name('%s_field_%s' % (self.table_name, field.varname))
self.ex('SELECT id FROM {item_table} WHERE label = %s',
ctx={'item_table': quote(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,),
ctx={'item_table': table_name})
self.ex('INSERT INTO {item_table} (label) VALUES (%s) RETURNING (id)', vars=(value,),
ctx={'item_table': quote(table_name)})
return self.cur.fetchone()[0]
def get_item_id(self, field, value):
@ -837,7 +842,7 @@ class WcsFormdefFeeder(object):
if not values:
self.logger.warning('no data')
return
self.ex('INSERT INTO "{formdata_table}" ({columns}) VALUES {values} RETURNING id',
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
@ -864,12 +869,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)
@ -880,9 +885,9 @@ class WcsFormdefFeeder(object):
def feed(self):
self.olap_feeder.ctx.push({
'formdata_table': self.table_name,
'status_table': self.status_table_name,
'evolution_table': self.evolution_table_name
'formdata_table': quote(self.table_name),
'status_table': quote(self.status_table_name),
'evolution_table': quote(self.evolution_table_name),
})
# create cube
@ -892,7 +897,7 @@ class WcsFormdefFeeder(object):
cube.update({
'name': self.table_name,
'label': self.formdef.schema.name,
'fact_table': self.table_name,
'fact_table': quote(self.table_name),
'key': 'id',
})
cube['dimensions'] = [dimension for dimension in cube['dimensions']
@ -920,7 +925,7 @@ class WcsFormdefFeeder(object):
cube['joins'].append({
'name': at,
'table': 'role',
'master': at,
'master': quote(at),
'detail': 'id',
})
cube['dimensions'].append({
@ -928,8 +933,8 @@ class WcsFormdefFeeder(object):
'label': u'fonction %s' % name.lower(),
'join': [at],
'type': 'integer',
'value': '"%s".id' % at,
'value_label': '"%s".label' % at,
'value': '%s.id' % quote(at),
'value_label': '%s.label' % quote(at),
'filter': False,
})
@ -947,11 +952,11 @@ class WcsFormdefFeeder(object):
join = None
if field.type == 'item':
table_name = self.hash_table_name('{formdata_table}_field_%s' % field.varname)
table_name = self.hash_table_name('%s_field_%s' % (self.table_name, field.varname))
join = {
'name': field.varname,
'table': table_name,
'master': '"field_%s"' % field.varname,
'table': quote(table_name),
'master': quote('field_%s' % field.varname),
'detail': 'id',
}
if not field.required:
@ -961,8 +966,8 @@ class WcsFormdefFeeder(object):
'label': field.label.lower(),
'join': [field.varname],
'type': 'integer',
'value': '"%s".id' % field.varname,
'value_label': '"%s".label' % field.varname,
'value': '%s.id' % quote(field.varname),
'value_label': '%s.label' % quote(field.varname),
'filter': True,
}
elif field.type == 'bool':
@ -970,11 +975,11 @@ class WcsFormdefFeeder(object):
'name': field.varname,
'label': field.label.lower(),
'type': 'bool',
'value': '"field_%s"' % field.varname,
'value_label': '(CASE WHEN "field_%(varname)s" IS NULL THEN NULL'
' WHEN "field_%(varname)s" THEN \'Oui\''
'value': quote('field_%s' % field.varname),
'value_label': '(CASE WHEN %(field)s IS NULL THEN NULL'
' WHEN %(field)s THEN \'Oui\''
' ELSE \'Non\' END)' % {
'varname': field.varname,
'field': quote('field_%s' % field.varname),
},
'filter': True,
}
@ -983,7 +988,7 @@ class WcsFormdefFeeder(object):
'name': field.varname,
'label': field.label.lower(),
'type': 'string',
'value': '"field_%s"' % field.varname,
'value': quote('field_%s' % field.varname),
'filter': True,
}
else: