25 |
25 |
|
26 |
26 |
|
27 |
27 |
def slugify(s):
|
28 |
|
return s.replace('-', '_').replace(' ', '_')
|
|
28 |
return s.replace('-', '_').replace(' ', '_').lower()
|
29 |
29 |
|
30 |
30 |
|
31 |
31 |
class Context(object):
|
... | ... | |
368 |
368 |
self.ex(query, vars=(name,))
|
369 |
369 |
return self.cur.fetchone()[0]
|
370 |
370 |
|
|
371 |
def update_table_sequence_number(self, name):
|
|
372 |
self.ex("""SELECT setval(pg_get_serial_sequence('{name}', 'id'),
|
|
373 |
(SELECT GREATEST(1, MAX(id)) FROM {name}))""", ctx={'name': quote(name)})
|
|
374 |
|
371 |
375 |
def create_labeled_table_serial(self, name, comment):
|
372 |
376 |
self.create_table(
|
373 |
377 |
name, [['id', 'serial primary key'], ['label', 'varchar']], comment=comment)
|
... | ... | |
378 |
382 |
'INSERT INTO {schema_temp}.{name} SELECT * FROM {schema}.{name}',
|
379 |
383 |
ctx={'name': quote(name)}
|
380 |
384 |
)
|
381 |
|
# Update sequence
|
382 |
|
self.ex("""SELECT setval(pg_get_serial_sequence('{name}', 'id'),
|
383 |
|
(SELECT GREATEST(1, MAX(id)) FROM {name}))""", ctx={'name': quote(name)})
|
|
385 |
self.update_table_sequence_number(name)
|
|
386 |
|
|
387 |
def create_referenced_table(self, name, fields, comment):
|
|
388 |
# add primary key and reference fields
|
|
389 |
new_fields = [['id', 'serial primary key'], ['ref', 'varchar UNIQUE']] + fields
|
|
390 |
self.create_table(name, new_fields, comment=comment)
|
|
391 |
|
|
392 |
if self.prev_table_exists(name):
|
|
393 |
# verify if ref exists in table schema
|
|
394 |
self.ex("""SELECT column_name FROM information_schema.columns
|
|
395 |
WHERE table_name=%s AND table_schema='{schema}'""", vars=(name,))
|
|
396 |
columns = [c[0] for c in self.cur.fetchall()]
|
|
397 |
if 'ref' in columns:
|
|
398 |
self.ex('INSERT INTO {schema_temp}.{name} SELECT * FROM {schema}.{name}', ctx={'name': quote(name)})
|
|
399 |
else:
|
|
400 |
fields_labels = ', '.join([f[0] for f in fields])
|
|
401 |
self.ex('SELECT {fields} FROM {schema}.{name}', ctx={'name': quote(name),
|
|
402 |
'fields': fields_labels})
|
|
403 |
new_values = []
|
|
404 |
for line in self.cur.fetchall():
|
|
405 |
result = (slugify(line[-1]),) + line
|
|
406 |
new_values.append(result)
|
|
407 |
if new_values:
|
|
408 |
columns_number = len(line) + 1
|
|
409 |
columns_values = ', '.join(['%s' for x in xrange(columns_number)])
|
|
410 |
tmpl = ', '.join(['(DEFAULT, %s)' % columns_values] * len(new_values))
|
|
411 |
query = 'INSERT INTO {schema_temp}.{name} VALUES %s' % tmpl
|
|
412 |
query = self.ex(query, ctx={'name': quote(name)},
|
|
413 |
vars=list(itertools.chain(*new_values)))
|
|
414 |
|
|
415 |
|
|
416 |
def do_referenced_data(self, name, data, result_column, update_column='label'):
|
|
417 |
to_insert = []
|
|
418 |
|
|
419 |
for item in data:
|
|
420 |
ref = item[-1]
|
|
421 |
self.ex(
|
|
422 |
'SELECT ref FROM {name} WHERE ref = %s', ctx={'name': quote(name)}, vars=(ref,))
|
|
423 |
if self.cur.fetchone() is None:
|
|
424 |
to_insert.append(item)
|
|
425 |
|
|
426 |
data = []
|
|
427 |
if to_insert:
|
|
428 |
for item in to_insert:
|
|
429 |
data.append(item)
|
|
430 |
|
|
431 |
if data:
|
|
432 |
columns_values = ', '.join(['%s' for x in xrange(len(item))])
|
|
433 |
tmpl = ', '.join(['(DEFAULT, %s)' % columns_values] * len(data))
|
|
434 |
query = 'INSERT INTO {name} VALUES %s ON CONFLICT (ref) ' \
|
|
435 |
'DO UPDATE SET {column} = EXCLUDED.{column}' % tmpl
|
|
436 |
self.ex(query, ctx={'name': quote(name), 'column': quote(update_column)},
|
|
437 |
vars=list(itertools.chain(*data)))
|
|
438 |
|
|
439 |
result = {}
|
|
440 |
self.ex('SELECT id, {column} FROM {name}', ctx={'name': quote(name),
|
|
441 |
'column': result_column})
|
|
442 |
for _id, column in self.cur.fetchall():
|
|
443 |
result[column] = _id
|
|
444 |
return result
|
|
445 |
|
384 |
446 |
|
385 |
447 |
def create_labeled_table(self, name, labels, comment=None):
|
386 |
448 |
self.create_table(
|
... | ... | |
446 |
508 |
def add_dim(self, **kwargs):
|
447 |
509 |
self.dimensions.append(self.tpl(kwargs))
|
448 |
510 |
|
|
511 |
def do_category_table(self):
|
|
512 |
fields = [['label', 'varchar']]
|
|
513 |
table_name = self.ctx['category_table']
|
|
514 |
self.create_referenced_table(table_name, fields, 'catégorie')
|
|
515 |
categories_data = [(c.id, c.name) for c in self.categories]
|
|
516 |
tmp_cat_map = self.do_referenced_data(table_name, categories_data, 'label')
|
|
517 |
self.update_table_sequence_number(table_name)
|
|
518 |
# remap categories ids to ids in the table
|
|
519 |
return dict((c.id, tmp_cat_map[c.name]) for c in self.categories)
|
|
520 |
|
|
521 |
def do_formdef_table(self):
|
|
522 |
categories_mapping = self.do_category_table()
|
|
523 |
|
|
524 |
formdef_fields = [['category_id', 'integer REFERENCES {category_table} (id)'],
|
|
525 |
['label', 'varchar']
|
|
526 |
]
|
|
527 |
table_name = self.ctx['form_table']
|
|
528 |
self.create_referenced_table(table_name, formdef_fields, 'types de formulaire')
|
|
529 |
|
|
530 |
formdefs_data = [(form.slug, categories_mapping.get(form.schema.category_id),
|
|
531 |
form.schema.name) for form in self.formdefs if form.count]
|
|
532 |
self.formdefs_mapping = self.do_referenced_data(table_name, formdefs_data, 'ref')
|
|
533 |
self.update_table_sequence_number(table_name)
|
|
534 |
|
449 |
535 |
def do_base_table(self):
|
450 |
536 |
# channels
|
451 |
537 |
self.create_labeled_table('channel', [[c[0], c[2]] for c in self.channels],
|
... | ... | |
457 |
543 |
self.role_mapping = dict(
|
458 |
544 |
(role.id, tmp_role_map[role.name]) for role in self.roles)
|
459 |
545 |
|
460 |
|
# categories
|
461 |
|
tmp_cat_map = self.create_labeled_table(
|
462 |
|
'category', enumerate(c.name for c in self.categories), comment='catégorie')
|
463 |
|
self.categories_mapping = dict((c.id, tmp_cat_map[c.name]) for c in self.categories)
|
|
546 |
# forms
|
|
547 |
self.do_formdef_table()
|
464 |
548 |
|
465 |
549 |
self.create_labeled_table('hour', zip(range(0, 24), map(str, range(0, 24))),
|
466 |
550 |
comment='heures')
|
467 |
551 |
|
468 |
552 |
self.create_labeled_table('status', self.status,
|
469 |
553 |
comment='statuts simplifiés')
|
470 |
|
self.ex('CREATE TABLE {form_table} (id serial PRIMARY KEY,'
|
471 |
|
' category_id integer REFERENCES {category_table} (id),'
|
472 |
|
' label varchar)')
|
473 |
|
self.ex('COMMENT ON TABLE {form_table} IS %s', vars=('types de formulaire',))
|
|
554 |
|
474 |
555 |
# agents
|
475 |
556 |
self.create_labeled_table_serial('agent', comment='agents')
|
476 |
557 |
|
... | ... | |
606 |
687 |
self.status_mapping = dict((s.id, tmp_status_map[s.name]) for s in statuses)
|
607 |
688 |
|
608 |
689 |
def do_data_table(self):
|
609 |
|
self.ex('INSERT INTO {form_table} (category_id, label) VALUES (%s, %s) RETURNING (id)',
|
610 |
|
vars=[self.categories_mapping.get(self.formdef.schema.category_id),
|
611 |
|
self.formdef.schema.name])
|
612 |
|
self.formdef_sql_id = self.cur.fetchone()[0]
|
613 |
|
|
614 |
690 |
columns = OrderedDict()
|
615 |
691 |
columns['status_id'] = {'sql_col_name': 'status_id', 'sql_col_def': 'smallint REFERENCES {status_table} (id)'}
|
616 |
692 |
|
... | ... | |
766 |
842 |
if channel == 'web' and data.submission.backoffice:
|
767 |
843 |
channel = 'backoffice'
|
768 |
844 |
row = {
|
769 |
|
'formdef_id': self.formdef_sql_id,
|
|
845 |
'formdef_id': self.formdefs_mapping[self.formdef.slug],
|
770 |
846 |
'receipt_time': data.receipt_time,
|
771 |
847 |
'hour_id': data.receipt_time.hour,
|
772 |
848 |
'channel_id': self.channel_to_id[channel],
|
773 |
|
-
|