def test(db, logging=False):
cursor = db.cursor()
if logging:
cursor.log = self.env.log
cursor.execute('SELECT 1 AS ' + db.quote(name))
self.assertEqual(name, get_column_names(cursor)[0])
cursor.execute('SELECT %s AS ' + db.quote(name), (42,))
self.assertEqual(name, get_column_names(cursor)[0])
cursor.executemany("UPDATE system SET value=%s WHERE "
"1=(SELECT 0 AS " + db.quote(name) + ")",
[])
cursor.executemany("UPDATE system SET value=%s WHERE "
"1=(SELECT 0 AS " + db.quote(name) + ")",
[('42',), ('43',)])
def test(logging=False):
with self.env.db_query as db:
cursor = db.cursor()
if logging:
cursor.log = self.env.log
cursor.execute('SELECT 1 AS ' + db.quote(name))
self.assertEqual(name, get_column_names(cursor)[0])
cursor.execute('SELECT %s AS ' + db.quote(name), (42, ))
self.assertEqual(name, get_column_names(cursor)[0])
stmt = """
UPDATE {0} SET value=%s WHERE 1=(SELECT 0 AS {1})
""".format(db.quote('system'), db.quote(name))
cursor.executemany(stmt, [])
cursor.executemany(stmt, [('42', ), ('43', )])
def test_quote(self):
with self.env.db_query as db:
cursor = db.cursor()
cursor.execute('SELECT 1 AS %s' % \
db.quote(r'alpha\`\"\'\\beta``gamma""delta'))
self.assertEqual(r'alpha\`\"\'\\beta``gamma""delta',
get_column_names(cursor)[0])
def copy_table(db, cursor, table):
src_cursor.execute('SELECT * FROM ' + src_db.quote(table))
columns = get_column_names(src_cursor)
n_rows = 100
if multirows_insert and max_parameters:
n_rows = min(n_rows, int(max_parameters // len(columns)))
quoted_table = db.quote(table)
holders = '(%s)' % ','.join(['%s'] * len(columns))
count = 0
cursor.execute('DELETE FROM ' + quoted_table)
while True:
rows = src_cursor.fetchmany(n_rows)
if not rows:
break
count += len(rows)
if progress:
printfout("%d records\r %s table... ",
count,
table,
newline=False)
if replace_cast is not None and table == 'report':
rows = replace_report_query(rows, columns, replace_cast)
query = 'INSERT INTO %s (%s) VALUES ' % \
(quoted_table, ','.join(map(db.quote, columns)))
if multirows_insert:
cursor.execute(query + ','.join([holders] * len(rows)),
sum(rows, ()))
else:
cursor.executemany(query + holders, rows)
return count
def _verify_table_status(self, db):
from trac.db_default import schema
tables = [t.name for t in schema]
cursor = db.cursor()
cursor.execute(
"SHOW TABLE STATUS WHERE name IN (%s)" % ','.join(
('%s', ) * len(tables)), tables)
cols = get_column_names(cursor)
rows = [dict(zip(cols, row)) for row in cursor]
engines = [
row['Name'] for row in rows
if row['Engine'] in self.UNSUPPORTED_ENGINES
]
if engines:
raise TracError(
_(
"All tables must be created as InnoDB or NDB storage engine "
"to support transactions. The following tables have been "
"created as storage engine which doesn't support "
"transactions: %(tables)s",
tables=', '.join(engines)))
non_utf8bin = [
row['Name'] for row in rows
if row['Collation'] not in ('utf8_bin', 'utf8mb4_bin', None)
]
if non_utf8bin:
raise TracError(
_(
"All tables must be created with utf8_bin or "
"utf8mb4_bin as collation. The following tables "
"don't have the collations: %(tables)s",
tables=', '.join(non_utf8bin)))
def _get_pie_graph_stats(env, factor, query=None):
"""
Return a dict in which the keys are the factors and the values are the
number of tickets of each factor.
Example:
>>> _get_pie_graph_stats(env, 'milestone')
{'milestone1' : 20,
'milestone2' : 12,
}
"""
sql, args = _get_query_sql(env, query, required_columns=[
factor,
])
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute(sql, args)
query_columns = get_column_names(cursor)
factor_index = query_columns.index(factor)
ticket_stats = defaultdict(lambda: 0)
for row in cursor:
factor_value = row[factor_index]
ticket_stats[factor_value] += 1
return ticket_stats
def _get_stacked_bar_chart_stats(env, key, x_axis, query):
required_columns = [x_axis]
if key is not None:
required_columns.append(key)
sql, args = _get_query_sql(env, query, required_columns)
db = env.get_db_cnx()
cursor = db.cursor()
cursor.execute(sql, args)
query_columns = get_column_names(cursor)
# Returning all the keys is kind of an optimization (so that we won't look
# for all keys later on)
keys = []
x_axis_field_index = query_columns.index(x_axis)
# Handle cases in which key is missing. In such cases, we'll form bar charts.
if key:
key_field_index = query_columns.index(key)
# Stacked bar charts are represented by a dict of dicts:
# {'x_axis_value' : {'key_value' : number_of_tickets}}
# Regular bar charts will be here the same, but 'key_value' will be
# replaced with None.
ticket_stats = defaultdict(lambda: defaultdict(lambda: 0), {})
for row in cursor:
if key:
key_field_value = row[key_field_index]
else:
key_field_value = None
x_axis_field_value = row[x_axis_field_index]
ticket_stats[x_axis_field_value][key_field_value] += 1
keys.append(key_field_value)
return ticket_stats, _unique_list(keys)
def _show_index(self, table):
with self.env.db_query as db:
cursor = db.cursor()
cursor.execute("SHOW INDEX FROM " + db.quote(table))
columns = get_column_names(cursor)
rows = [dict(zip(columns, row)) for row in cursor]
results = {}
for index, group in itertools.groupby(rows, lambda v: v['Key_name']):
group = list(group)
results[index] = {
'unique': not group[0]['Non_unique'],
'columns': [row['Column_name'] for row in group],
}
return results
def copy(db):
cursor = db.cursor()
printout(' %s table... ' % table, newline=False)
src_cursor.execute('SELECT * FROM ' + src_db.quote(table))
columns = get_column_names(src_cursor)
query = 'INSERT INTO ' + db.quote(table) + \
' (' + ','.join(db.quote(c) for c in columns) + ')' + \
' VALUES (' + ','.join(['%s'] * len(columns)) + ')'
cursor.execute('DELETE FROM ' + db.quote(table))
count = 0
while True:
rows = src_cursor.fetchmany(100)
if not rows:
break
cursor.executemany(query, rows)
count += len(rows)
printout('%d records.' % count)
def _verify_curr_schema(self):
self.assertFalse(self.votes.environment_needs_upgrade(self.db))
cursor = self.db.cursor()
cursor.execute('SELECT * FROM votes')
cols = get_column_names(cursor)
self.assertTrue('resource' not in cols)
self.assertEquals([
'realm', 'resource_id', 'version', 'username', 'vote', 'time',
'changetime'
], cols)
cursor.execute("""
SELECT value
FROM system
WHERE name='vote_version'
""")
schema_ver = int(cursor.fetchone()[0])
self.assertEquals(self.votes.schema_version, schema_ver)
def drop_column(self, table, column):
cursor = pymysql.cursors.Cursor(self.cnx)
if column in self.get_column_names(table):
quoted_table = self.quote(table)
cursor.execute("SHOW INDEX FROM %s" % quoted_table)
columns = get_column_names(cursor)
keys = {}
for row in cursor.fetchall():
row = dict(zip(columns, row))
keys.setdefault(row['Key_name'], []).append(row['Column_name'])
# drop all composite indices which in the given column is involved
for key, columns in keys.iteritems():
if len(columns) > 1 and column in columns:
if key == 'PRIMARY':
cursor.execute("ALTER TABLE %s DROP PRIMARY KEY" %
quoted_table)
else:
cursor.execute("ALTER TABLE %s DROP KEY %s" %
(quoted_table, self.quote(key)))
cursor.execute("ALTER TABLE %s DROP COLUMN %s " %
(quoted_table, self.quote(column)))
def _get_all_records(self, env):
def primary(row, columns):
if len(columns) == 1:
return row[columns[0]]
else:
return tuple(row[col] for col in columns)
records = {}
with env.db_query as db:
cursor = db.cursor()
for table in db_default.schema:
primary_cols = ','.join(db.quote(col) for col in table.key)
query = "SELECT * FROM %s ORDER BY %s" \
% (db.quote(table.name), primary_cols)
cursor.execute(query)
columns = get_column_names(cursor)
rows = {}
for row in cursor:
row = dict(zip(columns, row))
rows[primary(row, table.key)] = row
records[table.name] = rows
return records
def execute_paginated_report(self, req, id, sql, args, limit=0, offset=0):
"""
:param req: `Request` object.
:param id: Integer id of the report.
:param sql: SQL query that generates the report.
:param args: SQL query arguments.
:param limit: Maximum number of results to return (optional).
:param offset: Offset to start of results (optional).
"""
sql, args, missing_args = self.sql_sub_vars(sql, args)
if not sql:
raise TracError(_("Report {%(num)s} has no SQL query.", num=id))
self.log.debug('Report {%d} with SQL "%s"', id, sql)
self.log.debug('Request args: %r', req.args)
rows = None
num_items = 0
order_by = []
limit_offset = None
base_sql = sql.replace(SORT_COLUMN, '1').replace(LIMIT_OFFSET, '')
with self.env.db_query as db:
cursor = db.cursor()
if id == self.REPORT_LIST_ID or limit == 0:
sql = base_sql
else:
# The number of tickets is obtained
count_sql = 'SELECT COUNT(*) FROM (\n%s\n) AS tab' % base_sql
self.log.debug("Report {%d} SQL (count): %s", id, count_sql)
try:
cursor.execute(count_sql, args)
except Exception as e:
self.log.warning('Exception caught while executing '
'Report {%d}: %r, args %r%s',
id, count_sql, args,
exception_to_unicode(e, traceback=True))
return e, count_sql
num_items = cursor.fetchone()[0]
# The column names are obtained
colnames_sql = 'SELECT * FROM (\n%s\n) AS tab LIMIT 1' \
% base_sql
self.log.debug("Report {%d} SQL (col names): %s",
id, colnames_sql)
try:
cursor.execute(colnames_sql, args)
except Exception as e:
self.log.warning('Exception caught while executing '
'Report {%d}: args %r%s',
id, colnames_sql, args,
exception_to_unicode(e, traceback=True))
return e, colnames_sql
cols = get_column_names(cursor)
# The ORDER BY columns are inserted
sort_col = req.args.get('sort', '')
asc = req.args.getint('asc', 0, min=0, max=1)
self.log.debug("%r %s (%s)", cols, sort_col,
'^' if asc else 'v')
order_cols = []
if sort_col and sort_col not in cols:
raise TracError(_('Query parameter "sort=%(sort_col)s" '
' is invalid', sort_col=sort_col))
skel = None
if '__group__' in cols:
order_cols.append('__group__')
if sort_col:
sort_col = '%s %s' % (db.quote(sort_col),
'ASC' if asc else 'DESC')
if SORT_COLUMN in sql:
# Method 1: insert sort_col at specified position
sql = sql.replace(SORT_COLUMN, sort_col or '1')
elif sort_col:
# Method 2: automagically insert sort_col (and __group__
# before it, if __group__ was specified) as first criteria
if '__group__' in cols:
order_by.append('__group__ ASC')
order_by.append(sort_col)
# is there already an ORDER BY in the original sql?
skel = sql_skeleton(sql)
before, after = split_sql(sql, _order_by_re, skel)
if after: # there were some other criteria, keep them
order_by.append(after)
sql = ' '.join([before, 'ORDER BY', ', '.join(order_by)])
# Add LIMIT/OFFSET if pagination needed
limit_offset = ''
if num_items > limit:
limit_offset = ' '.join(['LIMIT', str(limit),
'OFFSET', str(offset)])
if LIMIT_OFFSET in sql:
# Method 1: insert LIMIT/OFFSET at specified position
sql = sql.replace(LIMIT_OFFSET, limit_offset)
else:
# Method 2: limit/offset is added unless already present
skel = skel or sql_skeleton(sql)
if 'LIMIT' not in skel.upper():
sql = ' '.join([sql, limit_offset])
self.log.debug("Report {%d} SQL (order + limit): %s", id, sql)
try:
cursor.execute(sql, args)
except Exception as e:
self.log.warning('Exception caught while executing Report '
'{%d}: %r, args %r%s', id, sql, args,
exception_to_unicode(e, traceback=True))
if order_by or limit_offset:
add_notice(req, _("Hint: if the report failed due to"
" automatic modification of the ORDER"
" BY clause or the addition of"
" LIMIT/OFFSET, please look up"
" %(sort_column)s and %(limit_offset)s"
" in TracReports to see how to gain"
" complete control over report"
" rewriting.",
sort_column=SORT_COLUMN,
limit_offset=LIMIT_OFFSET))
return e, sql
rows = cursor.fetchall() or []
cols = get_column_names(cursor)
return cols, rows, num_items, missing_args, limit_offset
