def count_query(cls, request, *args, **kwargs):
# Query that returns the number of records in the report.
# It implements filtering, but no paging or sorting.
conn = None
if not hasattr(request, "report"):
request.report = (SQLReport.objects.all().using(
request.database).get(pk=args[0]))
if request.report and request.report.sql:
try:
conn = create_connection(request.database)
with conn.cursor() as cursor:
sqlrole = settings.DATABASES[request.database].get(
"SQL_ROLE", "report_role")
if sqlrole:
cursor.execute("set role %s" % (sqlrole, ))
if not hasattr(request, "filter"):
request.filter = cls.getFilter(request, *args,
**kwargs)
cursor.execute(
"select count(*) from (%s) t_subquery %s" % (
request.report.sql.replace("%", "%%"),
"where %s" %
request.filter[0] if request.filter[0] else "",
),
request.filter[1],
)
return cursor.fetchone()[0]
finally:
if conn:
conn.close()
else:
return 0
def executeSQLfile(self, ifile):
"""
Execute statements from a text with SQL statements.
"""
if ifile.lower().endswith(".gz"):
file_open = gzip.open
else:
file_open = open
conn = None
try:
conn = create_connection(self.database)
with conn.cursor() as cursor:
if self.SQLrole:
cursor.execute("set role %s", (self.SQLrole,))
cursor.execute(file_open(ifile, "rt").read())
return 0
except Exception as e:
logger.error(
"%s Error executing SQL: %s"
% (datetime.now().replace(microsecond=0), e)
)
return 1
finally:
if conn:
conn.close()
def runQuery():
conn = None
sqlrole = settings.DATABASES[request.database].get(
"SQL_ROLE", "report_role")
try:
conn = create_connection(request.database)
with conn.cursor() as cursor:
with transaction.atomic():
if sqlrole:
cursor.execute("set role %s", (sqlrole, ))
sql = request.read().decode(
request.encoding or settings.DEFAULT_CHARSET)
cursor.execute(sql)
if cursor.description:
columns = [desc[0] for desc in cursor.description]
yield """{
"rowcount": %s,
"status": "ok",
"columns": %s,
"data": [
""" % (
cursor.rowcount,
json.dumps(columns),
)
first = True
for result in cursor.fetchall():
if first:
yield json.dumps(
dict(
zip(columns,
[str(i) for i in result])))
first = False
else:
yield ",%s" % json.dumps(
dict(
zip(columns,
[str(i) for i in result])))
yield "]}"
elif cursor.rowcount:
yield '{"rowcount": %s, "status": "Updated %s rows"}' % (
cursor.rowcount,
cursor.rowcount,
)
else:
yield '{"rowcount": %s, "status": "Done"}' % cursor.rowcount
except GeneratorExit:
pass
except Exception as e:
yield json.dumps({"status": str(e)})
finally:
if conn:
conn.close()
def refreshColumns(self):
from freppledb.common.middleware import _thread_locals
req = getattr(_thread_locals, "request", None)
if req:
db = getattr(req, "database", DEFAULT_DB_ALIAS)
else:
db = getattr(_thread_locals, "database", DEFAULT_DB_ALIAS)
SQLColumn.objects.filter(report=self).using(db).delete()
if self.sql:
conn = None
try:
conn = create_connection(db)
with conn.cursor() as cursor:
sqlrole = settings.DATABASES[db].get(
"SQL_ROLE", "report_role")
if sqlrole:
cursor.execute("set role %s" % (sqlrole, ))
# The query is wrapped in a dummy filter, to avoid executing the
# inner real query. It still generates the list of all columns.
cursor.execute("select * from (%s) as Q where false" %
self.sql)
if self.id:
cols = []
seq = 1
for f in cursor.description:
if f[0] in cols:
raise Exception("Duplicate column name '%s'" %
f[0])
cols.append(f[0])
if f[1] == 1700:
fmt = "number"
elif f[1] == 1184:
fmt = "datetime"
elif f[1] == 23:
fmt = "integer"
elif f[1] == 1186:
fmt = "duration"
elif f[1] == 1043:
fmt = "text"
else:
fmt = "character"
SQLColumn(report=self,
sequence=seq,
name=f[0],
format=fmt).save(using=db)
seq += 1
finally:
if conn:
conn.close()
def _generate_spreadsheet_data(cls, request, out, report, *args, **kwargs):
# Create a workbook
wb = Workbook(write_only=True)
ws = wb.create_sheet(title=report.name)
# Create a named style for the header row
readlonlyheaderstyle = NamedStyle(name="readlonlyheaderstyle")
readlonlyheaderstyle.fill = PatternFill(fill_type="solid",
fgColor="d0ebfb")
wb.add_named_style(readlonlyheaderstyle)
# Run the query
conn = None
try:
conn = create_connection(request.database)
comment = CellComment(force_text(_("Read only")),
"Author",
height=20,
width=80)
with conn.cursor() as cursor:
sqlrole = settings.DATABASES[request.database].get(
"SQL_ROLE", "report_role")
if sqlrole:
cursor.execute("set role %s" % (sqlrole, ))
cursor.execute(sql=cls.getSQL(report.sql))
if cursor.description:
# Write header row
header = []
for f in cursor.description:
cell = WriteOnlyCell(ws, value=f[0])
cell.style = "readlonlyheaderstyle"
cell.comment = comment
header.append(cell)
ws.append(header)
# Add an auto-filter to the table
ws.auto_filter.ref = "A1:%s1048576" % get_column_letter(
len(header))
# Write all output rows
for result in cursor.fetchall():
ws.append(
[_getCellValue(i, request=request) for i in result])
# Write the spreadsheet
wb.save(out)
finally:
if conn:
conn.close()
def _generate_csv_data(cls, request, report, *args, **kwargs):
sf = StringIO()
decimal_separator = get_format("DECIMAL_SEPARATOR",
request.LANGUAGE_CODE, True)
if decimal_separator == ",":
writer = csv.writer(sf,
quoting=csv.QUOTE_NONNUMERIC,
delimiter=";")
else:
writer = csv.writer(sf,
quoting=csv.QUOTE_NONNUMERIC,
delimiter=",")
# Write a Unicode Byte Order Mark header, aka BOM (Excel needs it to open UTF-8 file properly)
yield cls.getBOM(settings.CSV_CHARSET)
# Run the query
conn = None
try:
conn = create_connection(request.database)
with conn.cursor() as cursor:
sqlrole = settings.DATABASES[request.database].get(
"SQL_ROLE", "report_role")
if sqlrole:
cursor.execute("set role %s" % (sqlrole, ))
cursor.execute(sql=cls.getSQL(report.sql))
if cursor.description:
# Write header row
writer.writerow([f[0] for f in cursor.description])
yield sf.getvalue()
# Write all output rows
for result in cursor.fetchall():
# Clear the return string buffer
sf.seek(0)
sf.truncate(0)
writer.writerow([
cls._getCSVValue(i,
request=request,
decimal_separator=decimal_separator)
for i in result
])
yield sf.getvalue()
except GeneratorExit:
pass
finally:
if conn:
conn.close()
def data_query(cls, request, *args, page=None, **kwargs):
# Main query that will return all data records.
# It implements filtering, paging and sorting.
conn = None
if not hasattr(request, "report"):
request.report = (
SQLReport.objects.all().using(request.database).get(pk=args[0])
)
if request.report and request.report.sql:
try:
conn = create_connection(request.database)
with conn.cursor() as cursor:
sqlrole = settings.DATABASES[request.database].get(
"SQL_ROLE", "report_role"
)
if sqlrole:
cursor.execute("set role %s" % (sqlrole,))
if not hasattr(request, "filter"):
request.filter = cls.getFilter(request, *args, **kwargs)
cursor.execute(
"select * from (%s) t_subquery %s order by %s %s %s"
% (
request.report.sql.replace("%", "%%"),
"where %s" % request.filter[0] if request.filter[0] else "",
cls._apply_sort_index(request),
("offset %s" % ((page - 1) * request.pagesize + 1))
if page and page > 1
else "",
"limit %s" % request.pagesize if page else "",
),
request.filter[1],
)
for rec in cursor.fetchall():
result = {}
idx = 0
for f in request.rows:
result[f.name] = rec[idx]
idx += 1
yield result
finally:
if conn:
conn.close()
def _generate_json_data(cls, database, sql):
conn = None
try:
conn = create_connection(database)
with conn.cursor() as cursor:
sqlrole = settings.DATABASES[database].get("SQL_ROLE", "report_role")
if sqlrole:
cursor.execute("set role %s" % (sqlrole,))
cursor.execute(sql=cls.getSQL(sql))
if cursor.description:
counter = 0
columns = []
colmodel = []
for f in cursor.description:
columns.append(f[0])
colmodel.append(cls.getColModel(f[0], f[1], counter))
counter += 1
yield """{
"rowcount": %s,
"status": "ok",
"columns": %s,
"colmodel": %s,
"data": [
""" % (
cursor.rowcount,
json.dumps(columns),
json.dumps(colmodel),
)
first = True
for result in cursor.fetchall():
if first:
yield json.dumps(
dict(
zip(
columns,
[
i
if i is None
else i.total_seconds()
if isinstance(i, timedelta)
else str(i)
for i in result
],
)
)
)
first = False
else:
yield ",%s" % json.dumps(
dict(
zip(
columns,
[
i
if i is None
else i.total_seconds()
if isinstance(i, timedelta)
else str(i)
for i in result
],
)
)
)
yield "]}"
elif cursor.rowcount:
yield '{"rowcount": %s, "status": "Updated %s rows"}' % (
cursor.rowcount,
cursor.rowcount,
)
else:
yield '{"rowcount": %s, "status": "Done"}' % cursor.rowcount
except GeneratorExit:
pass
except Exception as e:
yield json.dumps({"status": str(e)})
finally:
if conn:
conn.close()
