0001-quote-all-templated-field-and-table-names-36503.patch

Benjamin Dauvergne, 28 septembre 2019 11:02

Voir les différences: en ligne côte à côte

Subject: [PATCH] quote all templated field and table names (#36503)

 tests/olap.model   |  12 ++---
 wcs_olap/feeder.py | 115 +++++++++++++++++++++++----------------------
 2 files changed, 66 insertions(+), 61 deletions(-)

                    "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"
-...
                    "value": "\"field_stringCaseSensitive\""
+                }
              ],
              "fact_table" : "formdata_demande",
              "fact_table" : "\"formdata_demande\"",
              "joins" : [
+                {
                    "detail" : "date",
-...
                 },
+                {
                    "detail" : "id",
                    "master" : "function__receiver",
                    "master" : "\"function__receiver\"",
                    "name" : "function__receiver",
                    "table" : "role"
                 },
-...
                    "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",
-...
                    "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"

     from wcs_olap.wcs_api import WcsApiError
     def quote(name):
         return '"%s"' % name
     def slugify(s):
         return s.replace('-', '_').replace(' ', '_')
-...
                         '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)',
+                    }
+                ]
+            }
-...
         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
-...
             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(
-...
             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)
-...
             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
-...
             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')
-...
                 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)
-...
                           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 = {}
-...
                 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(
-...
                 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
-...
                     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):
-...
             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
-...
                     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)
-...
         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
-...
             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']
-...
                 cube['joins'].append({
                     'name': at,
                     'table': 'role',
                     'master': at,
                     'master': quote(at),
                     'detail': 'id',
                 })
                 cube['dimensions'].append({
-...
                     '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,
                 })
-...
                 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:
-...
                         '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':
-...
                         '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,
+                    }
-...
                         'name': field.varname,
                         'label': field.label.lower(),
                         'type': 'string',
                         'value': '"field_%s"' % field.varname,
                         'value': quote('field_%s' % field.varname),
                         'filter': True,
+                    }
                 else:
+    -

Projet

Général

Profil

Produits Entr'ouvert » OLAP / Business Intelligence pour Publik

0001-quote-all-templated-field-and-table-names-36503.patch