| 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 |
fields = [['id', 'serial primary key'], ['ref', 'varchar']] + fields
|
|
390 |
self.create_table(name, fields, comment=comment)
|
|
391 |
|
|
392 |
if self.prev_table_exists(name):
|
|
393 |
# Insert data from previous table
|
|
394 |
self.ex(
|
|
395 |
'INSERT INTO {schema_temp}.{name} SELECT * FROM {schema}.{name}',
|
|
396 |
ctx={'name': quote(name)}
|
|
397 |
)
|
|
398 |
self.update_table_sequence_number(name)
|
|
399 |
|
|
400 |
def do_referenced_data(self, name, data, result_key):
|
|
401 |
to_insert = []
|
|
402 |
|
|
403 |
for item in data:
|
|
404 |
ref = item[-1]
|
|
405 |
self.ex(
|
|
406 |
'SELECT ref FROM {name} WHERE ref = %s', ctx={'name': quote(name)}, vars=(ref,))
|
|
407 |
if self.cur.fetchone() is None:
|
|
408 |
to_insert.append(item)
|
|
409 |
|
|
410 |
data = []
|
|
411 |
if to_insert:
|
|
412 |
self.ex('SELECT MAX(id) FROM {name}', ctx={'name': quote(name)})
|
|
413 |
next_id = (self.cur.fetchone()[0] or 0) + 1
|
|
414 |
for idx, item in enumerate(to_insert, next_id):
|
|
415 |
data.append((idx, ) + item)
|
|
416 |
|
|
417 |
if data:
|
|
418 |
columns_values = ', '.join(['%s' for x in xrange(len(item) + 1)])
|
|
419 |
tmpl = ', '.join(['(%s)' % columns_values] * len(data))
|
|
420 |
query = 'INSERT INTO {name} VALUES %s' % (tmpl,)
|
|
421 |
self.ex(query, ctx={'name': quote(name)}, vars=list(itertools.chain(*data)))
|
|
422 |
|
|
423 |
result = {}
|
|
424 |
self.ex('SELECT id, {column} FROM {name}' , ctx={'name': quote(name), 'column': result_key})
|
|
425 |
for _id, column in self.cur.fetchall():
|
|
426 |
result[column] = _id
|
|
427 |
return result
|
|
428 |
|
| 384 |
429 |
|
| 385 |
430 |
def create_labeled_table(self, name, labels, comment=None):
|
| 386 |
431 |
self.create_table(
|
| ... | ... | |
| 458 |
503 |
(role.id, tmp_role_map[role.name]) for role in self.roles)
|
| 459 |
504 |
|
| 460 |
505 |
# 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)
|
|
506 |
fields = [['label', 'varchar']]
|
|
507 |
self.create_referenced_table('category', fields, 'catégorie')
|
|
508 |
categories_data = [(c.id, c.name) for c in self.categories]
|
|
509 |
tmp_cat_map = self.do_referenced_data('category', categories_data, 'label')
|
|
510 |
print tmp_cat_map
|
|
511 |
# remap categories ids to ids in the table
|
|
512 |
categories_mapping = dict((c.id, tmp_cat_map[c.name]) for c in self.categories)
|
| 464 |
513 |
|
| 465 |
514 |
self.create_labeled_table('hour', zip(range(0, 24), map(str, range(0, 24))),
|
| 466 |
515 |
comment='heures')
|
| 467 |
516 |
|
| 468 |
517 |
self.create_labeled_table('status', self.status,
|
| 469 |
518 |
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',))
|
|
519 |
|
|
520 |
# forms
|
|
521 |
formdef_fields = [['category_id', 'integer REFERENCES {category_table} (id)'],
|
|
522 |
['label', 'varchar']
|
|
523 |
]
|
|
524 |
self.create_referenced_table(self.ctx['form_table'], formdef_fields, 'types de formulaire')
|
|
525 |
|
|
526 |
formdefs_data = [(form.slug, categories_mapping.get(form.schema.category_id),
|
|
527 |
form.schema.name) for form in self.formdefs if form.count]
|
|
528 |
self.formdefs_mapping = self.do_referenced_data(self.ctx['form_table'], formdefs_data, 'ref')
|
|
529 |
|
| 474 |
530 |
# agents
|
| 475 |
531 |
self.create_labeled_table_serial('agent', comment='agents')
|
| 476 |
532 |
|
| ... | ... | |
| 606 |
662 |
self.status_mapping = dict((s.id, tmp_status_map[s.name]) for s in statuses)
|
| 607 |
663 |
|
| 608 |
664 |
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 |
665 |
columns = OrderedDict()
|
| 615 |
666 |
columns['status_id'] = {'sql_col_name': 'status_id', 'sql_col_def': 'smallint REFERENCES {status_table} (id)'}
|
| 616 |
667 |
|
| ... | ... | |
| 766 |
817 |
if channel == 'web' and data.submission.backoffice:
|
| 767 |
818 |
channel = 'backoffice'
|
| 768 |
819 |
row = {
|
| 769 |
|
'formdef_id': self.formdef_sql_id,
|
|
820 |
'formdef_id': self.formdefs_mapping[self.formdef.slug],
|
| 770 |
821 |
'receipt_time': data.receipt_time,
|
| 771 |
822 |
'hour_id': data.receipt_time.hour,
|
| 772 |
823 |
'channel_id': self.channel_to_id[channel],
|
| 773 |
|
-
|