| 384 |
384 |
if self.prev_table_exists(name):
|
| 385 |
385 |
# Insert data from previous table
|
| 386 |
386 |
self.ex(
|
| 387 |
|
'INSERT INTO {schema_temp}.{name} select * FROM {schema}.{name}',
|
|
387 |
'INSERT INTO {schema_temp}."{name}" select * FROM {schema}."{name}"',
|
| 388 |
388 |
ctx={'name': name}
|
| 389 |
389 |
)
|
| 390 |
390 |
# Find what is missing
|
| 391 |
391 |
to_insert = []
|
| 392 |
392 |
for _id, _label in labels:
|
| 393 |
393 |
self.ex(
|
| 394 |
|
'SELECT * FROM {name} WHERE label = %s', ctx={'name': name}, vars=(_label,))
|
|
394 |
'SELECT * FROM "{name}" WHERE label = %s', ctx={'name': name}, vars=(_label,))
|
| 395 |
395 |
if self.cur.fetchone() is None:
|
| 396 |
396 |
to_insert.append(_label)
|
| 397 |
397 |
|
| 398 |
398 |
labels = None
|
| 399 |
399 |
if to_insert:
|
| 400 |
|
self.ex('SELECT MAX(id) FROM {name}', ctx={'name': name})
|
|
400 |
self.ex('SELECT MAX(id) FROM "{name}"', ctx={'name': name})
|
| 401 |
401 |
next_id = (self.cur.fetchone()[0] or 0) + 1
|
| 402 |
402 |
ids = range(next_id, next_id + len(to_insert))
|
| 403 |
403 |
labels = zip(ids, to_insert)
|
| ... | ... | |
| 408 |
408 |
|
| 409 |
409 |
if labels:
|
| 410 |
410 |
tmpl = ', '.join(['(%s, %s)'] * len(labels))
|
| 411 |
|
query_str = 'INSERT INTO {name} (id, label) VALUES %s' % tmpl
|
|
411 |
query_str = 'INSERT INTO "{name}" (id, label) VALUES %s' % tmpl
|
| 412 |
412 |
self.ex(query_str, ctx={'name': name}, vars=list(itertools.chain(*labels)))
|
| 413 |
413 |
|
| 414 |
414 |
res = {}
|
| 415 |
|
self.ex("SELECT id, label FROM %s" % str(name))
|
|
415 |
self.ex('SELECT id, label FROM "{name}"', ctx={'name': name})
|
| 416 |
416 |
for id_, label in self.cur.fetchall():
|
| 417 |
417 |
res[label] = id_
|
| 418 |
418 |
return res
|
| ... | ... | |
| 461 |
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 |
|
self.ex('COMMENT ON TABLE {form_table} IS %s', vars=(u'types de formulaire',))
|
|
464 |
self.ex('COMMENT ON TABLE "{form_table}" IS %s', vars=(u'types de formulaire',))
|
| 465 |
465 |
# agents
|
| 466 |
466 |
self.create_labeled_table_serial('agent', comment=u'agents')
|
| 467 |
467 |
|
| ... | ... | |
| 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:
|
| ... | ... | |
| 676 |
676 |
continue
|
| 677 |
677 |
i = _type.index('REFERENCES')
|
| 678 |
678 |
constraint = '%s_fk_constraint FOREIGN KEY (%s) %s' % (name, name, _type[i:])
|
| 679 |
|
self.ex('ALTER TABLE {formdata_table} ADD CONSTRAINT %s' % constraint)
|
| 680 |
|
self.ex('ALTER TABLE {formdata_table} ADD PRIMARY KEY (id)')
|
|
679 |
self.ex('ALTER TABLE "{formdata_table}" ADD CONSTRAINT %s' % constraint)
|
|
680 |
self.ex('ALTER TABLE "{formdata_table}" ADD PRIMARY KEY (id)')
|
| 681 |
681 |
# table des evolutions
|
| 682 |
682 |
self.create_table('{evolution_table}', [
|
| 683 |
683 |
['id', 'serial primary key'],
|
| 684 |
|
['status_id', 'smallint REFERENCES {status_table} (id)'],
|
| 685 |
|
['formdata_id', 'integer REFERENCES {formdata_table} (id)'],
|
|
684 |
['status_id', 'smallint REFERENCES "{status_table}" (id)'],
|
|
685 |
['formdata_id', 'integer REFERENCES "{formdata_table}" (id)'],
|
| 686 |
686 |
['time', 'timestamp'],
|
| 687 |
687 |
['date', 'date'],
|
| 688 |
688 |
['hour_id', 'smallint REFERENCES {hour_table} (id)'],
|
| 689 |
689 |
])
|
| 690 |
|
self.ex('COMMENT ON TABLE {evolution_table} IS %s',
|
|
690 |
self.ex('COMMENT ON TABLE "{evolution_table}" IS %s',
|
| 691 |
691 |
vars=(u'evolution des demandes %s' % self.formdef.schema.name,))
|
| 692 |
692 |
|
| 693 |
693 |
def insert_item_value(self, field, value):
|
| ... | ... | |
| 836 |
836 |
if not values:
|
| 837 |
837 |
self.logger.warning('no data')
|
| 838 |
838 |
return
|
| 839 |
|
self.ex('INSERT INTO {formdata_table} ({columns}) VALUES {values} RETURNING id',
|
|
839 |
self.ex('INSERT INTO "{formdata_table}" ({columns}) VALUES {values} RETURNING id',
|
| 840 |
840 |
ctx=dict(columns=', '.join(['"%s"' % column for column in self.columns[1:]]), values=', '.join(values)))
|
| 841 |
841 |
|
| 842 |
842 |
# insert generic evolutions
|
| ... | ... | |
| 863 |
863 |
row[0] = formdata_id
|
| 864 |
864 |
evolutions.append(tuple(row))
|
| 865 |
865 |
if len(evolutions) == 500:
|
| 866 |
|
self.ex('INSERT INTO {evolution_table} (%s) VALUES %s' % (
|
|
866 |
self.ex('INSERT INTO "{evolution_table}" (%s) VALUES %s' % (
|
| 867 |
867 |
', '.join(['formdata_id', 'status_id', 'time', 'date', 'hour_id']),
|
| 868 |
868 |
', '.join(['%s'] * len(evolutions))), vars=evolutions)
|
| 869 |
869 |
evolutions = []
|
| 870 |
870 |
if evolutions:
|
| 871 |
|
self.ex('INSERT INTO {evolution_table} (%s) VALUES %s' % (
|
|
871 |
self.ex('INSERT INTO "{evolution_table}" (%s) VALUES %s' % (
|
| 872 |
872 |
', '.join(['formdata_id', 'status_id', 'time', 'date', 'hour_id']),
|
| 873 |
873 |
', '.join(['%s'] * len(evolutions))), vars=evolutions)
|
| 874 |
874 |
|
| 875 |
|
-
|