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):
|
... | ... | |
358 |
358 |
self.ex(query, vars=(name,))
|
359 |
359 |
return self.cur.fetchone()[0]
|
360 |
360 |
|
|
361 |
def update_table_sequence_number(self, name):
|
|
362 |
self.ex("""SELECT setval(pg_get_serial_sequence('{name}', 'id'),
|
|
363 |
(SELECT GREATEST(1, MAX(id)) FROM {name}))""", ctx={'name': quote(name)})
|
|
364 |
|
361 |
365 |
def create_labeled_table_serial(self, name, comment):
|
362 |
366 |
self.create_table(
|
363 |
367 |
name, [['id', 'serial primary key'], ['label', 'varchar']], comment=comment)
|
... | ... | |
368 |
372 |
'INSERT INTO {schema_temp}.{name} SELECT * FROM {schema}.{name}',
|
369 |
373 |
ctx={'name': quote(name)}
|
370 |
374 |
)
|
371 |
|
# Update sequence
|
372 |
|
self.ex("""SELECT setval(pg_get_serial_sequence('{name}', 'id'),
|
373 |
|
(SELECT GREATEST(1, MAX(id)) FROM {name}))""", ctx={'name': quote(name)})
|
|
375 |
self.update_table_sequence_number(name)
|
|
376 |
|
|
377 |
def create_referenced_table(self, name, fields, comment):
|
|
378 |
# add primary key and reference fields
|
|
379 |
new_fields = [['id', 'serial primary key'], ['ref', 'varchar UNIQUE']] + fields
|
|
380 |
self.create_table(name, new_fields, comment=comment)
|
|
381 |
|
|
382 |
|
|
383 |
def do_referenced_data(self, name, data, result_column, update_column='label'):
|
|
384 |
to_insert = []
|
|
385 |
|
|
386 |
for item in data:
|
|
387 |
ref = item[0]
|
|
388 |
self.ex(
|
|
389 |
'SELECT ref, {column} FROM {name} WHERE ref = %s',
|
|
390 |
ctx={'name': quote(name), 'column': quote(update_column)},
|
|
391 |
vars=(ref,))
|
|
392 |
if self.cur.fetchall():
|
|
393 |
for item in self.cur.fetchall():
|
|
394 |
self.ex('UPDATE {name} SET {column}=%s WHERE ref=%s',
|
|
395 |
ctx={'name': quote(name), 'column': quote(update_column)},
|
|
396 |
vars=[item[1], ref])
|
|
397 |
else:
|
|
398 |
to_insert.append(item)
|
|
399 |
if to_insert:
|
|
400 |
columns_values = ', '.join(['%s' for x in range(len(item))])
|
|
401 |
tmpl = ', '.join(['(DEFAULT, %s)' % columns_values] * len(data))
|
|
402 |
|
|
403 |
query = 'INSERT INTO {name} VALUES %s' % tmpl
|
|
404 |
self.ex(query, ctx={'name': quote(name)}, # 'column': quote(update_column)},
|
|
405 |
vars=list(itertools.chain(*to_insert)))
|
|
406 |
|
|
407 |
result = {}
|
|
408 |
self.ex('SELECT id, {column} FROM {name}', ctx={'name': quote(name),
|
|
409 |
'column': result_column})
|
|
410 |
for _id, column in self.cur.fetchall():
|
|
411 |
result[column] = _id
|
|
412 |
return result
|
|
413 |
|
374 |
414 |
|
375 |
415 |
def create_labeled_table(self, name, labels, comment=None):
|
376 |
416 |
self.create_table(
|
... | ... | |
416 |
456 |
res[label] = id_
|
417 |
457 |
return res
|
418 |
458 |
|
|
459 |
def do_category_table(self):
|
|
460 |
fields = [['label', 'varchar']]
|
|
461 |
table_name = self.default_ctx['category_table']
|
|
462 |
self.create_referenced_table(table_name, fields, 'catégorie')
|
|
463 |
categories_data = [(c.slug, c.title) for c in self.categories]
|
|
464 |
tmp_cat_map = self.do_referenced_data(table_name, categories_data, 'label')
|
|
465 |
self.update_table_sequence_number(table_name)
|
|
466 |
# remap categories ids to ids in the table
|
|
467 |
return dict((c.title, tmp_cat_map[c.title]) for c in self.categories)
|
|
468 |
|
|
469 |
def do_formdef_table(self):
|
|
470 |
categories_mapping = self.do_category_table()
|
|
471 |
|
|
472 |
formdef_fields = [['category_id', 'integer REFERENCES {category_table} (id)'],
|
|
473 |
['label', 'varchar']
|
|
474 |
]
|
|
475 |
table_name = self.default_ctx['form_table']
|
|
476 |
self.create_referenced_table(table_name, formdef_fields, 'types de formulaire')
|
|
477 |
|
|
478 |
formdefs = [(form.slug, categories_mapping.get(form.schema.category),
|
|
479 |
form.schema.name) for form in self.formdefs if form.count]
|
|
480 |
self.formdefs_mapping = self.do_referenced_data(table_name, formdefs, 'ref')
|
|
481 |
self.update_table_sequence_number(table_name)
|
|
482 |
|
419 |
483 |
def do_base_table(self):
|
420 |
484 |
# channels
|
421 |
485 |
self.create_labeled_table('channel', [[c[0], c[2]] for c in self.channels],
|
... | ... | |
427 |
491 |
self.role_mapping = dict(
|
428 |
492 |
(role.id, tmp_role_map[role.name]) for role in self.roles)
|
429 |
493 |
|
430 |
|
# categories
|
431 |
|
tmp_cat_map = self.create_labeled_table(
|
432 |
|
'category', enumerate(c.title for c in self.categories), comment='catégorie')
|
433 |
|
self.categories_mapping = dict((c.slug, tmp_cat_map[c.title]) for c in self.categories)
|
|
494 |
# forms
|
|
495 |
self.do_formdef_table()
|
434 |
496 |
|
435 |
497 |
self.create_labeled_table('hour', zip(range(0, 24), map(str, range(0, 24))),
|
436 |
498 |
comment='heures')
|
437 |
499 |
|
438 |
500 |
self.create_labeled_table('status', self.status,
|
439 |
501 |
comment='statuts simplifiés')
|
440 |
|
self.ex('CREATE TABLE {form_table} (id serial PRIMARY KEY,'
|
441 |
|
' category_id integer REFERENCES {category_table} (id),'
|
442 |
|
' label varchar)')
|
443 |
|
self.ex('COMMENT ON TABLE {form_table} IS %s', vars=('types de formulaire',))
|
|
502 |
|
444 |
503 |
# agents
|
445 |
504 |
self.create_labeled_table_serial('agent', comment='agents')
|
446 |
505 |
|
... | ... | |
576 |
635 |
self.status_mapping = dict((s.id, tmp_status_map[s.name]) for s in statuses)
|
577 |
636 |
|
578 |
637 |
def do_data_table(self):
|
579 |
|
self.ex('INSERT INTO {form_table} (category_id, label) VALUES (%s, %s) RETURNING (id)',
|
580 |
|
vars=[self.categories_mapping.get(self.formdef.schema.category_id),
|
581 |
|
self.formdef.schema.name])
|
582 |
|
self.formdef_sql_id = self.cur.fetchone()[0]
|
583 |
|
|
584 |
638 |
columns = OrderedDict()
|
585 |
639 |
columns['status_id'] = {'sql_col_name': 'status_id', 'sql_col_def': 'smallint REFERENCES {status_table} (id)'}
|
586 |
640 |
|
... | ... | |
736 |
790 |
if channel == 'web' and data.submission.backoffice:
|
737 |
791 |
channel = 'backoffice'
|
738 |
792 |
row = {
|
739 |
|
'formdef_id': self.formdef_sql_id,
|
|
793 |
'formdef_id': self.formdefs_mapping[self.formdef.slug],
|
740 |
794 |
'receipt_time': data.receipt_time,
|
741 |
795 |
'hour_id': data.receipt_time.hour,
|
742 |
796 |
'channel_id': self.channel_to_id[channel],
|
743 |
|
-
|