Projet

Général

Profil

0001-keep-case-in-tables-and-fields-names-34559.patch

Benjamin Dauvergne, 06 juillet 2019 14:58

Télécharger (9,83 ko)

Voir les différences:

Subject: [PATCH] keep case in tables' and fields' names (#34559)

 tests/conftest.py  |  3 ++-
 tests/olap.model   | 33 ++++++++++++++++++++-------------
 tests/test_wcs.py  | 13 +++++++------
 wcs_olap/feeder.py | 26 +++++++++++++-------------
 4 files changed, 42 insertions(+), 33 deletions(-)
tests/conftest.py
93 93
    fields.ItemField(id='2', label='2nd field', type='item',
94 94
                     items=['foo', 'bar', 'baz'], varname='item'),
95 95
    fields.BoolField(id='3', label='3rd field', type='bool', varname='bool'),
96
    fields.ItemField(id='4', label='4rth field', type='item', varname='item_open'),
96
    fields.ItemField(id='4', label='4rth field', type='item', varname='itemOpen'),
97
    fields.StringField(id='5', label='5th field', type='string', anonymise=False, varname='stringCaseSensitive'),
97 98
]
98 99
formdef.store()
99 100

  
tests/olap.model
37 37
               "join" : [
38 38
                  "formdef"
39 39
               ],
40
               "label" : "formulaire",
41
               "name" : "formdef",
42
               "order_by" : "formdef.label",
43
               "type" : "integer",
44
               "value" : "formdef.id",
45
               "value_label" : "formdef.label"
40
               "label": "formulaire",
41
               "name": "formdef",
42
               "order_by": "formdef.label",
43
               "type": "integer",
44
               "value": "formdef.id",
45
               "value_label": "formdef.label"
46 46
            },
47 47
            {
48 48
               "join" : [
......
268 268
            {
269 269
               "filter" : true,
270 270
               "join" : [
271
                  "item_open"
271
                  "itemOpen"
272 272
               ],
273 273
               "label" : "4rth field",
274
               "name" : "item_open",
274
               "name" : "itemOpen",
275 275
               "type" : "integer",
276
               "value" : "\"item_open\".id",
277
               "value_label" : "\"item_open\".label"
276
               "value" : "\"itemOpen\".id",
277
               "value_label" : "\"itemOpen\".label"
278
            },
279
            {
280
               "filter": true,
281
               "label": "5th field",
282
               "name": "stringCaseSensitive",
283
               "type": "string",
284
               "value": "\"field_stringCaseSensitive\""
278 285
            }
279 286
         ],
280 287
         "fact_table" : "formdata_demande",
......
343 350
            },
344 351
            {
345 352
               "detail" : "id",
346
               "master" : "field_item_open",
347
               "name" : "item_open",
348
               "table" : "formdata_demande_field_item_open"
353
               "master" : "field_itemOpen",
354
               "name" : "itemOpen",
355
               "table" : "formdata_demande_field_itemOpen"
349 356
            }
350 357
         ],
351 358
         "key" : "id",
tests/test_wcs.py
57 57
        ('formdata_demande', 'field_string'),
58 58
        ('formdata_demande', 'field_item'),
59 59
        ('formdata_demande', 'field_bool'),
60
        ('formdata_demande', 'field_item_open'),
60
        ('formdata_demande', 'field_itemOpen'),
61
        ('formdata_demande', 'field_stringCaseSensitive'),
61 62
        ('formdata_demande', 'function__receiver'),
62 63
        ('formdata_demande_field_item', 'id'),
63 64
        ('formdata_demande_field_item', 'label'),
64
        ('formdata_demande_field_item_open', 'id'),
65
        ('formdata_demande_field_item_open', 'label'),
65
        ('formdata_demande_field_itemOpen', 'id'),
66
        ('formdata_demande_field_itemOpen', 'label'),
66 67
        ('formdef', 'id'),
67 68
        ('formdef', 'category_id'),
68 69
        ('formdef', 'label'),
......
130 131
            c.execute('SELECT * FROM formdata_demande_field_item ORDER BY id')
131 132
            refs = c.fetchall()
132 133
            assert len(refs) == 3
133
            c.execute('SELECT * FROM formdata_demande_field_item_open ORDER BY id')
134
            c.execute('SELECT * FROM "formdata_demande_field_itemOpen" ORDER BY id')
134 135
            open_refs = c.fetchall()
135 136
            assert len(open_refs) == 3
136 137

  
......
181 182
            assert new_refs[-1][1] == 'bazouka'
182 183
            bazouka_id = new_refs[-1][0]
183 184

  
184
            c.execute('SELECT * FROM formdata_demande_field_item_open ORDER BY id')
185
            c.execute('SELECT * FROM "formdata_demande_field_itemOpen" ORDER BY id')
185 186
            new_open_refs = c.fetchall()
186 187
            assert len(new_open_refs) == 4
187 188
            for ref in open_refs:
......
189 190
            assert new_open_refs[-1][1] == 'open_new_value'
190 191
            open_new_id = new_open_refs[-1][0]
191 192

  
192
            c.execute('''SELECT field_item, field_item_open
193
            c.execute('''SELECT field_item, "field_itemOpen"
193 194
            FROM formdata_demande ORDER BY id''')
194 195
            formdata = c.fetchone()
195 196
            assert formdata[0] == bazouka_id
wcs_olap/feeder.py
345 345
    AS the_date(the_date));''')
346 346

  
347 347
    def create_table(self, name, columns, inherits=None, comment=None):
348
        sql = 'CREATE TABLE %s' % name
349
        sql += '(' + ', '.join('%s %s' % (n, t) for n, t in columns) + ')'
348
        sql = 'CREATE TABLE "%s"' % name
349
        sql += '(' + ', '.join('"%s" %s' % (n, t) for n, t in columns) + ')'
350 350
        if inherits:
351
            sql += ' INHERITS (%s)' % inherits
351
            sql += ' INHERITS ("%s")' % inherits
352 352
        self.ex(sql)
353 353
        if comment:
354
            self.ex('COMMENT ON TABLE %s IS %%s' % name, vars=(comment,))
354
            self.ex('COMMENT ON TABLE "%s" IS %%s' % name, vars=(comment,))
355 355

  
356 356
    def prev_table_exists(self, name):
357 357
        query = """SELECT EXISTS (SELECT 1 FROM information_schema.tables
......
366 366
        if self.prev_table_exists(name):
367 367
            # Insert data from previous table
368 368
            self.ex(
369
                'INSERT INTO {schema_temp}.{name} SELECT * FROM {schema}.{name}',
369
                'INSERT INTO {schema_temp}."{name}" SELECT * FROM {schema}."{name}"',
370 370
                ctx={'name': name}
371 371
            )
372 372
            # Update sequence
373
            self.ex("""SELECT setval(pg_get_serial_sequence('{name}', 'id'),
374
            (SELECT GREATEST(1, MAX(id)) FROM {name}))""", ctx={'name': name})
373
            self.ex("""SELECT setval(pg_get_serial_sequence('"{name}"', 'id'),
374
            (SELECT GREATEST(1, MAX(id)) FROM "{name}"))""", ctx={'name': name})
375 375

  
376 376
    def create_labeled_table(self, name, labels, comment=None):
377 377
        self.create_table(
......
458 458

  
459 459
        self.create_labeled_table('status', self.status,
460 460
                                  comment=u'statuts simplifiés')
461
        self.ex('CREATE TABLE {form_table} (id serial PRIMARY KEY,'
461
        self.ex('CREATE TABLE "{form_table}" (id serial PRIMARY KEY,'
462 462
                ' category_id integer REFERENCES {category_table} (id),'
463 463
                ' label varchar)')
464 464
        self.ex('COMMENT ON TABLE {form_table} IS %s', vars=(u'types de formulaire',))
......
630 630
                else:
631 631
                    # open item field, from data sources...
632 632
                    self.create_labeled_table_serial(table_name, comment=comment)
633
                field_def = 'smallint REFERENCES %s (id)' % table_name
633
                field_def = 'smallint REFERENCES "%s" (id)' % table_name
634 634
            elif field.type == 'bool':
635 635
                field_def = 'boolean'
636 636
            elif field.type == 'string':
......
661 661
        self.create_table('{formdata_table}', columns, inherits='{generic_formdata_table}',
662 662
                          comment=u'formulaire %s' % self.formdef.schema.name)
663 663
        for at, comment in comments.iteritems():
664
            self.ex('COMMENT ON COLUMN {formdata_table}.%s IS %%s' % at, vars=(comment,))
664
            self.ex('COMMENT ON COLUMN {formdata_table}."%s" IS %%s' % at, vars=(comment,))
665 665

  
666 666
        # Creat index for JSON fields
667 667
        if self.has_jsonb:
......
692 692

  
693 693
    def insert_item_value(self, field, value):
694 694
        table_name = self.hash_table_name('{formdata_table}_field_%s' % field.varname)
695
        self.ex("SELECT id FROM {item_table} WHERE label = %s",
695
        self.ex('SELECT id FROM "{item_table}" WHERE label = %s',
696 696
                ctx={'item_table': table_name}, vars=(value,))
697 697
        res = self.cur.fetchone()
698 698
        if res:
699 699
            return res[0]
700
        self.ex('INSERT INTO {item_table} (label) VALUES (%s) RETURNING (id)', vars=(value,),
700
        self.ex('INSERT INTO "{item_table}" (label) VALUES (%s) RETURNING (id)', vars=(value,),
701 701
                ctx={'item_table': table_name})
702 702
        return self.cur.fetchone()[0]
703 703

  
......
837 837
            self.logger.warning('no data')
838 838
            return
839 839
        self.ex('INSERT INTO {formdata_table} ({columns}) VALUES {values} RETURNING id',
840
                ctx=dict(columns=', '.join(self.columns[1:]), values=', '.join(values)))
840
                ctx=dict(columns=', '.join(['"%s"' % column for column in self.columns[1:]]), values=', '.join(values)))
841 841

  
842 842
        # insert generic evolutions
843 843
        generic_evolutions = []
844
-