def find_old_ids() -> None: recips = ', '.join(str(id) for id in recipient_ids) is_topic_muted = build_topic_mute_checker(cursor, user_profile) query = ''' SELECT zerver_usermessage.id, zerver_message.recipient_id, zerver_message.subject FROM zerver_usermessage INNER JOIN zerver_message ON ( zerver_message.id = zerver_usermessage.message_id ) WHERE ( zerver_usermessage.user_profile_id = %s AND zerver_usermessage.message_id <= %s AND (zerver_usermessage.flags & 1) = 0 AND zerver_message.recipient_id in (%s) ) ''' % (user_profile.id, pointer, recips) logger.info(''' EXPLAIN analyze''' + query.rstrip() + ';') cursor.execute(query) rows = cursor.fetchall() for (um_id, recipient_id, topic) in rows: if not is_topic_muted(recipient_id, topic): user_message_ids.append(um_id) logger.info('rows found: %d' % (len(user_message_ids),))
def find() -> None: recips = ', '.join(str(id) for id in recipient_ids) query = ''' SELECT zerver_usermessage.id FROM zerver_usermessage INNER JOIN zerver_message ON ( zerver_message.id = zerver_usermessage.message_id ) WHERE ( zerver_usermessage.user_profile_id = %s AND (zerver_usermessage.flags & 1) = 0 AND zerver_message.recipient_id in (%s) ) ''' % (user_profile.id, recips) logger.info(''' EXPLAIN analyze''' + query.rstrip() + ';') cursor.execute(query) rows = cursor.fetchall() for row in rows: user_message_ids.append(row[0]) logger.info('rows found: %d' % (len(user_message_ids),))
def build_topic_mute_checker(cursor: CursorObj, user_profile: UserProfile) -> Callable[[int, str], bool]: ''' This function is similar to the function of the same name in zerver/lib/topic_mutes.py, but it works without the ORM, so that we can use it in migrations. ''' query = ''' SELECT recipient_id, topic_name FROM zerver_mutedtopic WHERE user_profile_id = %s ''' cursor.execute(query, [user_profile.id]) rows = cursor.fetchall() tups = { (recipient_id, topic_name.lower()) for (recipient_id, topic_name) in rows } def is_muted(recipient_id: int, topic: str) -> bool: return (recipient_id, topic.lower()) in tups return is_muted
def get_version(self, connection): if self.name not in self._version_cache: cursor = connection.cursor() cursor.execute('SELECT version()') version, = cursor.fetchone() self._version_cache[self.name] = tuple(map(int, RE_VERSION.search(version).groups())) return self._version_cache[self.name]
def get_table_schema(self, connection, table_name): cursor = connection.cursor() for schema in self.search_path: cursor.execute('SELECT 1 ' 'FROM information_schema.tables ' 'WHERE table_name = %s AND table_schema = %s', (table_name, schema)) if cursor.rowcount: return schema
def update_unread_flags(cursor: CursorObj, user_message_ids: List[int]) -> None: um_id_list = ', '.join(str(id) for id in user_message_ids) query = ''' UPDATE zerver_usermessage SET flags = flags | 1 WHERE id IN (%s) ''' % (um_id_list,) cursor.execute(query)
def _test(cls, connection): cursor = connection.cursor() cursor.execute('SELECT 1 FROM information_schema.tables ' 'WHERE table_name IN %s', (('ir_model', 'ir_model_field', 'ir_ui_view', 'ir_ui_menu', 'res_user', 'res_group', 'ir_module', 'ir_module_dependency', 'ir_translation', 'ir_lang'),)) return len(cursor.fetchall()) != 0
def current_user(self): if self._current_user is None: connection = self.get_connection() try: cursor = connection.cursor() cursor.execute('SELECT current_user') self._current_user = cursor.fetchone()[0] finally: self.put_connection(connection) return self._current_user
def get_connection(self, autocommit=False, readonly=False): if self._connpool is None: self.connect() conn = self._connpool.getconn() if autocommit: conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) else: conn.set_isolation_level(ISOLATION_LEVEL_REPEATABLE_READ) if readonly: cursor = conn.cursor() cursor.execute('SET TRANSACTION READ ONLY') conn.cursor_factory = PerfCursor return conn
def init(self): from trytond.modules import get_module_info connection = self.get_connection() cursor = connection.cursor() sql_file = os.path.join(os.path.dirname(__file__), 'init.sql') with open(sql_file) as fp: for line in fp.read().split(';'): if (len(line) > 0) and (not line.isspace()): cursor.execute(line) for module in ('ir', 'res'): state = 'uninstalled' if module in ('ir', 'res'): state = 'to install' info = get_module_info(module) cursor.execute('SELECT NEXTVAL(\'ir_module_id_seq\')') module_id = cursor.fetchone()[0] cursor.execute('INSERT INTO ir_module ' '(id, create_uid, create_date, name, state) ' 'VALUES (%s, %s, now(), %s, %s)', (module_id, 0, module, state)) for dependency in info.get('depends', []): cursor.execute('INSERT INTO ir_module_dependency ' '(create_uid, create_date, module, name) ' 'VALUES (%s, now(), %s, %s)', (0, module_id, dependency)) connection.commit() self.put_connection(connection)
def search_path(self): if self._search_path is None: connection = self.get_connection() try: cursor = connection.cursor() cursor.execute('SHOW search_path') path, = cursor.fetchone() special_values = { 'user': self.current_user, } self._search_path = [ unescape_quote(replace_special_values( p.strip(), **special_values)) for p in path.split(',')] finally: self.put_connection(connection) return self._search_path
def find_recipients() -> None: query = ''' SELECT zerver_subscription.recipient_id FROM zerver_subscription INNER JOIN zerver_recipient ON ( zerver_recipient.id = zerver_subscription.recipient_id ) WHERE ( zerver_subscription.user_profile_id = '%s' AND zerver_recipient.type = 2 AND (NOT zerver_subscription.active) ) ''' cursor.execute(query, [user_profile.id]) rows = cursor.fetchall() for row in rows: recipient_ids.append(row[0]) logger.info(str(recipient_ids))
def list(self): now = time.time() timeout = config.getint('session', 'timeout') res = Database._list_cache if res and abs(Database._list_cache_timestamp - now) < timeout: return res connection = self.get_connection() cursor = connection.cursor() cursor.execute('SELECT datname FROM pg_database ' 'WHERE datistemplate = false ORDER BY datname') res = [] for db_name, in cursor: try: with connect(self.dsn(db_name)) as conn: if self._test(conn): res.append(db_name) except Exception: continue self.put_connection(connection) Database._list_cache = res Database._list_cache_timestamp = now return res
def do_batch_update(cursor: CursorObj, table: str, cols: List[str], vals: List[str], batch_size: int=10000, sleep: float=0.1, escape: bool=True) -> None: # nocoverage stmt = ''' UPDATE %s SET (%s) = (%s) WHERE id >= %%s AND id < %%s ''' % (table, ', '.join(cols), ', '.join(['%s'] * len(cols))) cursor.execute("SELECT MIN(id), MAX(id) FROM %s" % (table,)) (min_id, max_id) = cursor.fetchall()[0] if min_id is None: return print("\n Range of rows to update: [%s, %s]" % (min_id, max_id)) while min_id <= max_id: lower = min_id upper = min_id + batch_size print(' Updating range [%s,%s)' % (lower, upper)) params = list(vals) + [lower, upper] if escape: cursor.execute(stmt, params=params) else: cursor.execute(stmt % tuple(params)) min_id = upper time.sleep(sleep) # Once we've finished, check if any new rows were inserted to the table if min_id > max_id: cursor.execute("SELECT MAX(id) FROM %s" % (table,)) max_id = cursor.fetchall()[0][0] print(" Finishing...", end='')
def nextid(self, connection, table): cursor = connection.cursor() cursor.execute("SELECT NEXTVAL('" + table + "_id_seq')") return cursor.fetchone()[0]
def sequence_rename(self, connection, old_name, new_name): cursor = connection.cursor() if (self.sequence_exist(connection, old_name) and not self.sequence_exist(connection, new_name)): cursor.execute('ALTER TABLE "%s" RENAME TO "%s"' % (old_name, new_name))
def currid(self, connection, table): cursor = connection.cursor() cursor.execute('SELECT last_value FROM "' + table + '_id_seq"') return cursor.fetchone()[0]
def execute(self, query, vars=None): self.Record = None return _cursor.execute(self, query, vars)
def create(cls, connection, database_name, template='template0'): cursor = connection.cursor() cursor.execute('CREATE DATABASE "' + database_name + '" ' 'TEMPLATE "' + template + '" ENCODING \'unicode\'') connection.commit() cls._list_cache.clear()
def lock(self, connection, table): cursor = connection.cursor() cursor.execute( SQL('LOCK {} IN EXCLUSIVE MODE NOWAIT').format(Identifier(table)))
def sequence_delete(self, connection, name): cursor = connection.cursor() cursor.execute(SQL("DROP SEQUENCE {}").format(Identifier(name)))
def setnextid(self, connection, table, value): cursor = connection.cursor() cursor.execute("SELECT SETVAL(%s, %s)", (table + '_id_seq', value))
def currid(self, connection, table): cursor = connection.cursor() cursor.execute( SQL("SELECT last_value FROM {}").format( Identifier(table + '_id_seq'))) return cursor.fetchone()[0]
def nextid(self, connection, table): cursor = connection.cursor() cursor.execute("SELECT NEXTVAL(%s)", (table + '_id_seq', )) return cursor.fetchone()[0]
def drop(self, connection, database_name): cursor = connection.cursor() cursor.execute( SQL("DROP DATABASE {}").format(Identifier(database_name))) self.__class__._list_cache.clear()
def drop(self, connection, database_name): cursor = connection.cursor() cursor.execute('DROP DATABASE "' + database_name + '"') Database._list_cache = None
def execute(self, query, params=None): """ Execute function """ self._odt_desc = None return _cursor.execute(self, query, params)
def query_all(query: str, query_args: Dict) -> List[Dict]: cursor = get_cursor() cursor.execute(query, query_args) result = cursor.fetchall() return result
def create(cls, connection, database_name): cursor = connection.cursor() cursor.execute('CREATE DATABASE "' + database_name + '" ' 'TEMPLATE template0 ENCODING \'unicode\'') connection.commit() cls._list_cache = None
def execute(self, query, vars=None): try: return _cursor.execute(self, query, vars) finally: self.connection.log(self.query, self)
def execute(self, query, vars=None): self.column_mapping = [] self._query_executed = 1 return _cursor.execute(self, query, vars)
def drop(self, connection, database_name): cursor = connection.cursor() cursor.execute('DROP DATABASE "' + database_name + '"') self.__class__._list_cache.clear()
def setnextid(self, connection, table, value): cursor = connection.cursor() cursor.execute("SELECT SETVAL('" + table + "_id_seq', %d)" % value)
def lock(self, connection, table): cursor = connection.cursor() cursor.execute('LOCK "%s" IN EXCLUSIVE MODE NOWAIT' % table)
def sequence_delete(self, connection, name): cursor = connection.cursor() cursor.execute('DROP SEQUENCE "%s"' % name)
def execute(self, sql, args=None): if logger.isEnabledFor(logging.DEBUG): logger.debug(self.mogrify(sql, args)) cursor.execute(self, sql, args)
def execute(self, query, vars=None): self.index = {} self._query_executed = 1 return _cursor.execute(self, query, vars)
def query_one(query: str, query_args: Dict) -> Optional[Dict]: cursor = get_cursor() cursor.execute(query, query_args) result = cursor.fetchone() return result