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 nextid(self, connection, table):
cursor = connection.cursor()
cursor.execute("SELECT NEXTVAL('" + table + "_id_seq')")
return cursor.fetchone()[0]
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 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 execute(self, query, vars=None):
self.Record = None
return _cursor.execute(self, query, vars)
def setnextid(self, connection, table, value):
cursor = connection.cursor()
cursor.execute("SELECT SETVAL('" + table + "_id_seq', %d)" % value)
def execute(self, query, vars=None):
try:
return _cursor.execute(self, query, vars)
finally:
self.connection.log(self.query, self)
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 lock(self, connection, table):
cursor = connection.cursor()
cursor.execute('LOCK "%s" IN EXCLUSIVE MODE NOWAIT' % table)
def execute(self, sql, args=None):
if logger.isEnabledFor(logging.DEBUG):
logger.debug(self.mogrify(sql, args))
cursor.execute(self, sql, args)
def setnextid(self, connection, table, value):
cursor = connection.cursor()
cursor.execute("SELECT SETVAL('" + table + "_id_seq', %d)" % value)
def execute(self, query, vars=None):
self.index = {}
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 + '"')
Database._list_cache = None
def execute(self, query, vars=None):
self.column_mapping = []
self._query_executed = 1
return _cursor.execute(self, query, vars)
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
