0001-sql-add-fts-column-to-users-table-8973.patch

Frédéric Péters, 13 novembre 2015 14:33

Voir les différences: en ligne côte à côte

Subject: [PATCH] sql: add fts column to users table (#8973)

 tests/test_sql.py | 83 ++++++++++++++++++++++++++++++++++++++++++++++++++++
 wcs/sql.py        | 87 ++++++++++++++++++++++++++++++++++++++-----------------
 2 files changed, 143 insertions(+), 27 deletions(-)

         assert sql.SqlUser.get(12345, ignore_errors=True) is None
     @postgresql
     def test_user_formdef():
         sql.SqlUser.wipe()
         from wcs.admin.settings import UserFieldsFormDef
         formdef = UserFieldsFormDef(pub)
         formdef.fields = [fields.StringField(id='3', label='test', type='string')]
         formdef.store()
         user = sql.SqlUser()
         user.name = 'Pierre'
         user.form_data = {'3': 'Papier'}
         user.store()
         assert sql.SqlUser.get(user.id, ignore_errors=True).form_data['3'] == 'Papier'
         del pub.cfg['users']['formdef']
         pub.write_cfg()
     @postgresql
     def test_get_users_with_role():
         sql.SqlUser.wipe()
-...
         assert len(sql.SqlUser.get_users_with_name_identifier('foo')) == 1
         assert sql.SqlUser.get_users_with_name_identifier('foo')[0].name == 'Pierre'
     @postgresql
     def test_get_users_fts():
         sql.SqlUser.wipe()
         user = sql.SqlUser()
         user.name = 'Pierre'
         user.name_identifiers = ['foo']
         user.store()
         user_id = user.id
         user = sql.SqlUser()
         user.name = 'Papier'
         user.store()
         assert len(sql.SqlUser.get_ids_from_query('pierre')) == 1
         assert sql.SqlUser.get(sql.SqlUser.get_ids_from_query('pierre')[0]).id == user_id
     @postgresql
     def test_get_users_formdef_fts():
         sql.SqlUser.wipe()
         from wcs.admin.settings import UserFieldsFormDef
         formdef = UserFieldsFormDef(pub)
         formdef.fields = [fields.StringField(id='3', label='test', type='string')]
         formdef.store()
         user = sql.SqlUser()
         user.name = 'Pierre'
         user.form_data = {'3': 'Papier'}
         user.store()
         user_id = user.id
         assert len(sql.SqlUser.get_ids_from_query('pierre papier')) == 1
         assert sql.SqlUser.get(sql.SqlUser.get_ids_from_query('pierre papier')[0]).id == user_id
         assert len(sql.SqlUser.get_ids_from_query('papier pierre')) == 1
         assert sql.SqlUser.get(sql.SqlUser.get_ids_from_query('papier pierre')[0]).id == user_id
         del pub.cfg['users']['formdef']
         pub.write_cfg()
     @postgresql
     def test_urlname_change():
-...
         conn.commit()
         cur.close()
     @postgresql
     def test_migration_12_users_fts():
         conn, cur = sql.get_connection_and_cursor()
         cur.execute('UPDATE wcs_meta SET value = 11 WHERE key = %s', ('sql_level',))
         sql.SqlUser.wipe()
         user = sql.SqlUser()
         user.name = 'Pierre'
         user.store()
         # remove the fts column
         cur.execute('ALTER TABLE users DROP COLUMN fts')
         assert not column_exists_in_table(cur, 'users', 'fts')
         sql.migrate()
         assert column_exists_in_table(cur, 'users', 'fts')
         assert migration_level(cur) >= 12
         # make sure the fts is filled after the migration
         assert len(sql.SqlUser.get_ids_from_query('pierre')) == 1
         conn.commit()
         cur.close()
     def drop_formdef_tables():
         conn, cur = sql.get_connection_and_cursor()

         needed_fields = set(['id', 'name', 'email', 'roles', 'is_admin',
             'anonymous', 'name_identifiers',
             'lasso_dump', 'last_seen'])
             'lasso_dump', 'last_seen', 'fts'])
         from admin.settings import UserFieldsFormDef
         formdef = UserFieldsFormDef()
-...
                     cur.execute('''ALTER TABLE %s ADD COLUMN %s bytea''' % (
                                             table_name, 'f%s_structured' % field.id))
         # migrations
         if not 'fts' in existing_fields:
             # full text search
             cur.execute('''ALTER TABLE %s ADD COLUMN fts tsvector''' % table_name)
             cur.execute('''CREATE INDEX %s_fts ON %s USING gin(fts)''' % (
                                     table_name, table_name))
         # delete obsolete fields
         for field in (existing_fields - needed_fields):
             cur.execute('''ALTER TABLE %s DROP COLUMN %s''' % (table_name, field))
-...
         get_with_indexed_value = classmethod(get_with_indexed_value)
         @guard_postgres
         def get_ids_from_query(cls, query):
             conn, cur = get_connection_and_cursor()
             sql_statement = '''SELECT id FROM %s
                                 WHERE fts @@ plainto_tsquery(%%(value)s)''' % cls._table_name
             cur.execute(sql_statement, {'value': query})
             all_ids = [x[0] for x in cur.fetchall()]
             cur.close()
             return all_ids
         get_ids_from_query = classmethod(get_ids_from_query)
         @guard_postgres
         def get(cls, id, ignore_errors=False, ignore_migration=False):
             if id is None:
                 if ignore_errors:
-...
         get_ids_with_indexed_value = classmethod(get_ids_with_indexed_value)
         @guard_postgres
         def get_ids_from_query(cls, query):
             conn, cur = get_connection_and_cursor()
             sql_statement = '''SELECT id FROM %s
                                 WHERE fts @@ plainto_tsquery(%%(value)s)''' % cls._table_name
             cur.execute(sql_statement, {'value': query})
             all_ids = [x[0] for x in cur.fetchall()]
             cur.close()
             return all_ids
         get_ids_from_query = classmethod(get_ids_from_query)
         @guard_postgres
         def fix_sequences(cls):
             conn, cur = get_connection_and_cursor()
-...
             if self.last_seen:
                 sql_dict['last_seen'] = datetime.datetime.fromtimestamp(self.last_seen),
             sql_dict.update(self.get_sql_dict_from_data(self.form_data, self.get_formdef()))
             user_formdef = self.get_formdef()
             sql_dict.update(self.get_sql_dict_from_data(self.form_data, user_formdef))
             conn, cur = get_connection_and_cursor()
             if not self.id:
-...
                                            ', '.join(['%%(%s)s' % x for x in column_names]))
                     cur.execute(sql_statement, sql_dict)
             fts_strings = []
             if self.name:
                 fts_strings.append(self.name)
             if self.email:
                 fts_strings.append(self.email)
             if user_formdef and user_formdef.fields:
                 for field in user_formdef.fields:
                     if not self.form_data.get(field.id):
                         continue
                     value = None
                     if field.key in ('string', 'text', 'email'):
                         value = self.form_data.get(field.id)
                     elif field.key in ('item', 'items'):
                         value = self.form_data.get('%s_display' % field.id)
                     if value:
                         if isinstance(value, basestring):
                             fts_strings.append(value)
                         elif type(value) in (tuple, list):
                             fts_strings.extend(value)
             sql_statement = '''UPDATE %s SET fts = to_tsvector( %%(fts)s)
                                 WHERE id = %%(id)s''' % self._table_name
             cur.execute(sql_statement, {'id': self.id, 'fts': ' '.join(fts_strings)})
             conn.commit()
             cur.close()
-...
         return result
     SQL_LEVEL = 11
     SQL_LEVEL = 12
     def migrate_global_views(conn, cur):
         cur.execute('''SELECT COUNT(*) FROM information_schema.tables
-...
             raise RuntimeError()
         if sql_level < 1: # 1: introduction of tracking_code table
             do_tracking_code_table()
         if sql_level < 2: # 2: introduction of formdef_id in views
         if sql_level < 11:
             # 2: introduction of formdef_id in views
             # 5: add concerned_roles_array, is_at_endpoint and fts to views
             # 7: add backoffice_submission to tables and views
             # 8: add submission_context to tables
             # 9: add last_update_time to views
             # 10: add submission_channel to tables
             # 11: add formdef_name and user_name to views
             migrate_views(conn, cur)
         if sql_level < 4:
         if sql_level < 12:
             # 3: introduction of _structured for user fields
             # 4: removal of identification_token
             # 12: (first part) add fts to users
             do_user_table()
         if sql_level < 5:
             # 5: add concerned_roles_array, is_at_endpoint and fts to views
             migrate_views(conn, cur)
         if sql_level < 6:
             # 6: add actions_roles_array to tables and views
             from wcs.formdef import FormDef
             migrate_views(conn, cur)
             for formdef in FormDef.select():
                 formdef.data_class().rebuild_security()
         if sql_level < 11:
             # 7: add backoffice_submission to tables and views
             # 8: add submission_context to tables
             # 9: add last_update_time to views
             # 10: add submission_channel to tables
             # 11: add formdef_name and user_name to views
             migrate_views(conn, cur)
         if sql_level < 12:
             # 12: (second part), store fts in existing rows
             for user_id in SqlUser.keys():
                 SqlUser.get(user_id).store()
         cur.execute('''UPDATE wcs_meta SET value = %s WHERE key = %s''', (
             str(SQL_LEVEL), 'sql_level'))
+    -

Projet

Général

Profil

Produits Entr'ouvert » w.c.s.

0001-sql-add-fts-column-to-users-table-8973.patch