0002-sql-add-blockdef-fields-to-fulltext-indexation-53284.patch
tests/test_sql.py | ||
---|---|---|
4 | 4 |
import time |
5 | 5 | |
6 | 6 |
import psycopg2 |
7 | 7 |
import pytest |
8 | 8 |
from quixote import cleanup |
9 | 9 | |
10 | 10 |
import wcs.qommon.storage as st |
11 | 11 |
from wcs import fields, sql |
12 |
from wcs.blocks import BlockDef |
|
12 | 13 |
from wcs.formdata import Evolution |
13 | 14 |
from wcs.formdef import FormDef |
14 | 15 |
from wcs.qommon import force_str |
15 | 16 |
from wcs.wf.jump import JumpWorkflowStatusItem |
16 | 17 |
from wcs.wf.register_comment import RegisterCommenterWorkflowStatusItem |
17 | 18 |
from wcs.workflows import CommentableWorkflowStatusItem, Workflow, WorkflowCriticalityLevel |
18 | 19 | |
19 | 20 |
from .utilities import clean_temporary_pub, create_temporary_pub |
... | ... | |
21 | 22 | |
22 | 23 |
def setup_module(module): |
23 | 24 |
global pub, formdef |
24 | 25 | |
25 | 26 |
cleanup() |
26 | 27 | |
27 | 28 |
pub = create_temporary_pub(sql_mode=True) |
28 | 29 | |
30 |
block = BlockDef() |
|
31 |
block.name = 'fooblock' |
|
32 |
block.fields = [ |
|
33 |
fields.StringField(id='1', label='string', type='string'), |
|
34 |
fields.ItemField(id='2', label='item', items=('boat', 'plane', 'kick scooter')), |
|
35 |
] |
|
36 |
block.store() |
|
37 | ||
29 | 38 |
formdef = FormDef() |
30 | 39 |
formdef.name = 'tests' |
31 | 40 |
formdef.fields = [ |
32 | 41 |
fields.StringField(id='0', label='string'), |
33 | 42 |
fields.EmailField(id='1', label='email'), |
34 | 43 |
fields.TextField(id='2', label='text'), |
35 | 44 |
fields.BoolField(id='3', label='bool'), |
36 | 45 |
fields.ItemField(id='4', label='item', items=('apple', 'pear', 'peach', 'apricot')), |
37 | 46 |
fields.DateField(id='5', label='date'), |
38 | 47 |
fields.ItemsField(id='6', label='items', items=('apple', 'pear', 'peach', 'apricot')), |
48 |
fields.BlockField(id='7', label='block', type='block:fooblock'), |
|
39 | 49 |
] |
40 | 50 |
formdef.store() |
41 | 51 | |
42 | 52 | |
43 | 53 |
def teardown_module(module): |
44 | 54 |
clean_temporary_pub() |
45 | 55 | |
46 | 56 | |
... | ... | |
163 | 173 | |
164 | 174 | |
165 | 175 |
def test_sql_field_items(): |
166 | 176 |
check_sql_field('6', ['apricot']) |
167 | 177 |
check_sql_field('6', ['apricot', 'pear']) |
168 | 178 |
check_sql_field('6', ['apple', 'pear', 'peach']) |
169 | 179 | |
170 | 180 | |
181 |
def test_sql_block_field_text(): |
|
182 |
check_sql_field('7', {'data': [{'1': 'foo'}, {'1': 'bar'}]}) |
|
183 | ||
184 | ||
185 |
def test_sql_block_field_item(): |
|
186 |
check_sql_field( |
|
187 |
'7', |
|
188 |
{ |
|
189 |
'data': [ |
|
190 |
{'2': 'boat', '2_display': 'Yacht'}, |
|
191 |
{'2': 'plane', '2_display': 'Cessna'}, |
|
192 |
] |
|
193 |
}, |
|
194 |
) |
|
195 | ||
196 | ||
171 | 197 |
def test_sql_geoloc(): |
172 | 198 |
test_formdef = FormDef() |
173 | 199 |
test_formdef.name = 'geoloc' |
174 | 200 |
test_formdef.fields = [] |
175 | 201 |
test_formdef.geolocations = {'base': 'Plop'} |
176 | 202 |
test_formdef.store() |
177 | 203 |
data_class = test_formdef.data_class(mode='sql') |
178 | 204 |
formdata = data_class() |
... | ... | |
370 | 396 |
formdata.store() |
371 | 397 |
id2 = formdata.id |
372 | 398 | |
373 | 399 |
formdata = data_class() |
374 | 400 |
formdata.data = {'2': 'you would think other ideas of text would emerge'} |
375 | 401 |
formdata.store() |
376 | 402 |
id3 = formdata.id |
377 | 403 | |
404 |
formdata = data_class() |
|
405 |
formdata.data = { |
|
406 |
'7': { |
|
407 |
'data': [ |
|
408 |
{'1': 'some other example having foo', '2': 'boat', '2_display': 'Yatch'}, |
|
409 |
{'1': 'bar', '2': 'plane', '2_display': 'Cessna'}, |
|
410 |
] |
|
411 |
} |
|
412 |
} |
|
413 |
formdata.store() |
|
414 |
id4 = formdata.id |
|
415 | ||
378 | 416 |
ids = data_class.get_ids_from_query('text') |
379 | 417 |
assert set(ids) == set([id1, id3]) |
380 | 418 | |
381 | 419 |
ids = data_class.get_ids_from_query('classical') |
382 | 420 |
assert set(ids) == set([id2]) |
383 | 421 | |
422 |
ids = data_class.get_ids_from_query('FOO') |
|
423 |
assert set(ids) == set([id4]) |
|
424 |
ids = data_class.get_ids_from_query('cessna') |
|
425 |
assert set(ids) == set([id4]) |
|
426 | ||
384 | 427 | |
385 | 428 |
def test_sql_rollback_on_error(): |
386 | 429 |
data_class = formdef.data_class(mode='sql') |
387 | 430 |
data_class.wipe() |
388 | 431 |
with pytest.raises(psycopg2.Error): |
389 | 432 |
# this will raise a psycopg2.ProgrammingError as there's no FOOBAR |
390 | 433 |
# column in the table. |
391 | 434 |
data_class.get_ids_with_indexed_value('FOOBAR', '2') |
wcs/sql.py | ||
---|---|---|
2091 | 2091 |
sql_dict['parts'] = None |
2092 | 2092 |
cur.execute(sql_statement, sql_dict) |
2093 | 2093 |
evo._sql_id = cur.fetchone()[0] |
2094 | 2094 | |
2095 | 2095 |
fts_strings = [str(self.id), self.get_display_id()] |
2096 | 2096 |
fts_strings.append(self._formdef.name) |
2097 | 2097 |
if self.tracking_code: |
2098 | 2098 |
fts_strings.append(self.tracking_code) |
2099 |
for field in self._formdef.get_all_fields(): |
|
2100 |
if not self.data.get(field.id): |
|
2099 | ||
2100 |
def get_all_fields(): |
|
2101 |
for field in self._formdef.get_all_fields(): |
|
2102 |
if field.key == 'block' and self.data.get(field.id): |
|
2103 |
for data in self.data[field.id].get('data'): |
|
2104 |
for subfield in field.block.fields: |
|
2105 |
yield subfield, data |
|
2106 |
else: |
|
2107 |
data = self.data |
|
2108 |
yield field, self.data |
|
2109 | ||
2110 |
for field, data in get_all_fields(): |
|
2111 |
if not data.get(field.id): |
|
2101 | 2112 |
continue |
2102 | 2113 |
value = None |
2103 | 2114 |
if field.key in ('string', 'text', 'email'): |
2104 |
value = self.data.get(field.id)
|
|
2115 |
value = data.get(field.id) |
|
2105 | 2116 |
elif field.key in ('item', 'items'): |
2106 |
value = self.data.get('%s_display' % field.id)
|
|
2117 |
value = data.get('%s_display' % field.id) |
|
2107 | 2118 |
if value: |
2108 | 2119 |
if isinstance(value, str): |
2109 | 2120 |
fts_strings.append(value) |
2110 | 2121 |
elif type(value) in (tuple, list): |
2111 | 2122 |
fts_strings.extend(value) |
2112 | 2123 |
if self._evolution: |
2113 | 2124 |
for evo in self._evolution: |
2114 | 2125 |
if evo.comment: |
... | ... | |
3342 | 3353 |
cur.close() |
3343 | 3354 | |
3344 | 3355 |
return result |
3345 | 3356 | |
3346 | 3357 | |
3347 | 3358 |
# latest migration, number + description (description is not used |
3348 | 3359 |
# programmaticaly but will make sure git conflicts if two migrations are |
3349 | 3360 |
# separately added with the same number) |
3350 |
SQL_LEVEL = (50, 'switch role uuid column to varchar')
|
|
3361 |
SQL_LEVEL = (51, 'add index on formdata blockdef fields')
|
|
3351 | 3362 | |
3352 | 3363 | |
3353 | 3364 |
def migrate_global_views(conn, cur): |
3354 | 3365 |
cur.execute( |
3355 | 3366 |
'''SELECT COUNT(*) FROM information_schema.tables |
3356 | 3367 |
WHERE table_schema = 'public' |
3357 | 3368 |
AND table_name = %s''', |
3358 | 3369 |
('wcs_all_forms',), |
... | ... | |
3467 | 3478 |
migrate_views(conn, cur) |
3468 | 3479 |
for formdef in FormDef.select(): |
3469 | 3480 |
formdef.data_class().rebuild_security() |
3470 | 3481 |
if sql_level < 23: |
3471 | 3482 |
# 12: (second part), store fts in existing rows |
3472 | 3483 |
# 21: (second part), store ascii_name of users |
3473 | 3484 |
# 23: (first part), use misc.simplify() over full text queries |
3474 | 3485 |
set_reindex('user', 'needed', conn=conn, cur=cur) |
3475 |
if sql_level < 41:
|
|
3486 |
if sql_level < 51:
|
|
3476 | 3487 |
# 17: store last_update_time in tables |
3477 | 3488 |
# 18: add user name to full-text search index |
3478 | 3489 |
# 21: (third part), add user ascii_names to full-text index |
3479 | 3490 |
# 23: (second part) use misc.simplify() over full text queries |
3480 | 3491 |
# 28: add display id and formdef name to full-text index |
3481 | 3492 |
# 29: add evolution parts to full-text index |
3482 | 3493 |
# 31: add user_label to formdata |
3483 | 3494 |
# 38: extract submission_agent_id to its own column |
3484 | 3495 |
# 41: update full text normalization |
3496 |
# 51: add index on formdata blockdef fields |
|
3485 | 3497 |
set_reindex('formdata', 'needed', conn=conn, cur=cur) |
3486 | 3498 |
if sql_level < 46: |
3487 | 3499 |
from wcs.carddef import CardDef |
3488 | 3500 |
from wcs.formdef import FormDef |
3489 | 3501 | |
3490 | 3502 |
# 24: add index on evolution(formdata_id) |
3491 | 3503 |
# 35: add indexes on formdata(receipt_time) and formdata(anonymised) |
3492 | 3504 |
# 36: add index on formdata(user_id) |
3493 |
- |