def process_block(self, block_text, user, **params):
""" Yields all query results at once. Only once.
May return an exception.
"""
try:
tokens = self.parse_block(block_text)
db_name = tokens.get(self.ATTR_DB,
settings.DATABASE_DEFAULT_ALIAS).strip()
con = None
db = settings.DATABASES.get(db_name, None)
if db == None:
raise DwException(db_name, "Unknown database %s." % db_name)
true_sql = tokens.sqltext
p = re.compile(u'\$\{[a-z]+[0-9]*\}')
lst = p.findall(true_sql)
for item in lst:
stripped_item = item[2:-1]
# TODO check for default value here
value = 'None'
if stripped_item in params:
value = params[stripped_item]
value = urllib.unquote(value)
# replace it
true_sql = true_sql.replace(item, value)
# get a connection from somewhere
con = self.get_con(db)
cur = con.cursor()
cur.execute(true_sql)
row = cur.fetchone()
num_rows = 0
while row is not None:
if num_rows == db.get('LIMIT_ROWS', 1000):
# we are 1 row over limit
add_line = htmlconstants.SQL_OVER_LIMIT.format(num_rows)
row = None
break
else:
retstr = u''
for val in row:
retstr = retstr + ' ' + self.format_value(val)
retstr += "\n"
yield retstr
num_rows += 1
row = cur.fetchone()
con.close()
con = None
except DwException, e:
if con is not None:
con.close()
raise e
def process_block(self, block_text, user, block_id, **params):
def int_or_none(val):
"""Returns an integer or none. May pass anything
"""
try:
ret = int(val)
except ValueError:
ret = None
return ret
def float_or_none(val):
"""Returns a float or none. May pass anything
"""
if val is None:
return val
else:
try:
ret = float(val)
except ValueError:
ret = None
return ret
def int_or_default(val, default=0):
""" Returns int or a given default
"""
try:
ret = int(val)
except ValueError:
ret = default
return ret
try:
tokens = self.parse_block(block_text)
except Exception, e:
traceback.print_exc()
# reraise it wrapped in our class
raise DwException('', "Errpr in graph definition", e)
class SqlTableStreamer(dwstreamer.BaseDwStreamer):
"""Block streamer that creates a table out of sql query"""
TAGNAME = 'sqltable'
# re to find attributes for table cells
ATTR_RE = re.compile('\{: *([ [a-z]+=\".+\" *]*)\}')
# Attributes of {sqltable} tag
ATTR_DB = u'db'
ATTR_TOTALS = u'totals'
ATTR_ORIENTATION = u'orientation'
# available orientations
ATTR_VAL_ORIENTATION_VERTICAL = u'vertical'
ATTR_VAL_ORIENTATION_HORIZONTAL = u'horizontal'
ATTR_DEFAULT_VAL_ORIENTATION = ATTR_VAL_ORIENTATION_VERTICAL
# table style
ATTR_STYLE = u'style'
# available styles are there in css file
ATTR_DEFAULT_VAL_STYLE = u'standard-report'
# header or no header. Can be 'yes' or 'no'. Default - 'yes'
ATTR_HEADER = u'header'
ATTR_DEFAULT_VAL_HEADER = u'yes'
ATTR_HEADER_VAL_YES = u'yes'
ATTR_HEADER_VAL_NO = u'no'
# debugging attribute for delaying rows output
ATTR_DELAY = u'delay'
ATTR_DELAY_DEFAULT_VAL = u'0'
# value types that can be aggregated
FIELD_TYPES_AGGREGATION = ['Decimal', 'int', 'long', 'float']
def __init__(self):
self.con = None
self.sett = settings.CSV_DOWNLOAD_FORMAT['en']
# separator for csv
self.sep = unicode(self.sett.get('FIELD_SEPARATOR', ','))
def factory(self):
return SqlTableStreamer()
def get_tagname(self):
return self.TAGNAME
def format_value(self, val, html=True):
"""default formatting of database values.
returns <td>value</td> if html=True. Else returns just value"""
#td_start = u'<td class='
cell_style = self.table_css_class + '-cell'
td_start = u'<td class="' + cell_style + u'"'
td_align_left = ' '
td_align_right = ' align="right" '
align = td_align_left
#res = td + unicode(val) + '</td>'
tp = type(val).__name__
#md = markdown.Markdown(extensions=['markdown.extensions.attr_list'])
md = markdown.Markdown()
#attr = markdown.extensions.attr_list
#attr.extend(md, [])
#md = markdown.Markdown(safe_mode='escape')
attrs = ''
if tp == 'str':
res = val.decode('utf-8')
match = self.ATTR_RE.search(res)
if match != None:
attrs = ' ' + match.group(1) + ' '
res = re.sub(self.ATTR_RE, '', res)
# markdown it
res = md.convert(res)
# remove <p></p>
if res[:3] == '<p>':
res = res[3:-4]
elif tp == 'unicode':
res = unicode(val)
match = self.ATTR_RE.search(res)
if match != None:
attrs = ' ' + match.group(1) + ' '
res = re.sub(self.ATTR_RE, '', res)
# markdown it
res = md.convert(res)
# remove <p></p>
if res[:3] == '<p>':
res = res[3:-4]
elif tp in ['Decimal', 'float']:
res = "{0:0.2f}".format(val)
align = td_align_right
elif tp == 'date':
res = val.strftime("%d.%m.%Y")
elif tp in ['int', 'long']:
res = "{0}".format(val)
align = td_align_right
elif tp in ['NoneType']:
res = ' '
else:
# unknown datatype
res = unicode(val)
match = self.ATTR_RE.search(res)
if match != None:
attrs = ' ' + match.group(1) + ' '
res = re.sub(self.ATTR_RE, '', res)
# markdown it
res = md.convert(res)
# remove <p></p>
if res[:3] == '<p>':
res = res[3:-4]
if html:
res = td_start + align + attrs + '>' + res + '</td>'
# now we can possibly have a link in a column.
# It may contain URL parameters with special characters.
# Something like /myreport?name="Some long name in God knows what Language"
# We should quote it
return res
def format_csv_value(self, val):
"""Format a value for csv output, according to standard csv rules
(https://en.wikipedia.org/wiki/Comma-separated_values).
Also settings are used. The value is returned as unicode.
All html, attributes and markdown links are replaced by plain text"""
def deal_with_str(strval):
"""Formats string as unicode with optional double quotes according
to standard"""
need_quotes = False
result = strval
# first commas or semicolons
ind = result.find(self.sep)
if ind >= 0:
need_quotes = True
# now double quotes
# we need to add one to each occurence
ind = result.find('"')
if ind >= 0:
need_quotes = True
result = result.replace('"', '""')
# newline characters.
newline_repl = self.sett.get('NEWLINE_REPLACEMENT', None)
if newline_repl is None:
need_quotes = True
else:
# replace them
result = result.replace("\r\n", newline_repl)
result = result.replace("\n", newline_repl)
result = result.replace("\r", newline_repl)
# leading or trailing spaces require double quotes too
if (result[0] == ' ') or (result[-1] == ' '):
need_quotes = True
# tabs require quotes or replacement
ind = result.find("\t")
if ind >= 0:
tab_repl = self.sett.get('TAB_REPLACEMENT', None)
if tab_repl is None:
need_quotes = True
else:
result = result.replace("\t", tab_repl)
# finally
if need_quotes:
result = u'"' + result + u'"'
return result
sett = settings.CSV_DOWNLOAD_FORMAT['en']
tp = type(val).__name__
res = ''
if tp == 'str':
res = deal_with_str(val.decode('utf-8'))
elif tp == 'unicode':
res = deal_with_str(unicode(val))
elif tp in ['Decimal', 'float', 'int', 'long']:
# plain
res = str(val)
elif tp == 'date':
res = val.strftime(sett['DATE_FORMAT'])
elif tp in ['NoneType']:
res = ''
# attributes
match = self.ATTR_RE.search(res)
if match != None:
attrs = ' ' + match.group(1) + ' '
res = re.sub(self.ATTR_RE, '', res)
return res
# gets a database connection
# it should work with several connections
# throws a DWDatabaseError
def get_con(self):
# TODO work around assigning a connection to a member var.
# We really want to have a stateless object here
self.con = dwwiki.connectors.get_connection(self.db)
# #return con
#
# #con = MySQLdb.connect(host=db['HOST'], user=db['USER'], passwd=db['PASSWORD'], db=db['DB'],
# # charset='utf8',
# # cursorclass = MySQLdb.cursors.SSCursor)
# self.con = None
# db = self.db
# # TODO different connectors should live in different files
# if db['ENGINE'] == 'mysql':
# import mysql.connector
# timeout = db.get('QUERY_TIMEOUT', 60) * 1000 # milliseconds
# self.con = mysql.connector.connect(
# user=db['USER'],
# passwd=db['PASSWORD'],
# host=db['HOST'],
# database=db['DB'],
# connection_timeout=timeout
# )
# elif db['ENGINE'] == 'postgresql':
# import psycopg2
# # set query timeout as options to psycopg
# timeout = db.get('QUERY_TIMEOUT', 60) * 1000 # milliseconds
# self.con = psycopg2.connect(
# user=db['USER'],
# password=db['PASSWORD'],
# host=db['HOST'],
# database=db['DB'],
# port=db['PORT'],
# options="-c statement_timeout=%d" % timeout
# )
# self.con.set_client_encoding('utf-8')
# elif db['ENGINE'] == 'sqlite':
# import sqlite3
# timeout = db.get('QUERY_TIMEOUT', 60) * 1000 # milliseconds
# self.con = sqlite3.connect(
# database=db['DB'],
# timeout=timeout
# )
# returns sql block parsed into tokens
def parse_block(self, block_text):
# make a grammar
block_start = Literal("{")
sql_start = Keyword("sqltable", caseless=True)
colon = Literal(":")
sql_end = Literal("}")
separator = Literal("|")
block_end = Keyword("{sqltable}", caseless=True)
# params
field_name = Word(alphanums)
equal_sign = Suppress(Literal("="))
# whatever value
field_value = (CharsNotIn("|}"))
# param name and value
param_group = Group(field_name + equal_sign + field_value)
# list of all params
param_list = delimitedList(param_group, '|')
# helper
param_dict = Dict(param_list)
# sql text
sql_text = SkipTo(block_end)
sqldecl = Forward()
sqldecl << (block_start +
sql_start +
Optional(colon) +
Optional(param_dict) +
sql_end +
sql_text.setResultsName('sqltext') +
block_end)
block_str = "".join(block_text)
tokens = sqldecl.parseString( block_str )
return tokens
def prepare_for_query(self, block_text, user, **params):
self.tokens = self.parse_block(block_text)
# set all required parameters
self.db_name = self.tokens.get(self.ATTR_DB, settings.DATABASE_DEFAULT_ALIAS).strip()
# check orientation
self.orientation = self.tokens.get(self.ATTR_ORIENTATION, self.ATTR_DEFAULT_VAL_ORIENTATION).strip()
# string for totals
self.totals_str = self.tokens.get(self.ATTR_TOTALS, '')
# array of required totals like ,,sum,crap,avg
self.totals_array = []
# array to calculate real totals if possible
self.totals_values = []
if self.totals_str <> '':
# user requires totals
self.totals_array = self.totals_str.split(',')
# table, row, footer and cell styles
self.table_css_class = self.tokens.get(self.ATTR_STYLE, self.ATTR_DEFAULT_VAL_STYLE).lower().strip()
# header
self.header = self.tokens.get(self.ATTR_HEADER, self.ATTR_DEFAULT_VAL_HEADER).lower().strip()
# avoid gibberish
if not self.header in [self.ATTR_HEADER_VAL_YES, self.ATTR_HEADER_VAL_NO]:
self.header = self.ATTR_HEADER_VAL_YES
# delay for rows. in milliseconds. for debugging
self.delay = 0
s = self.tokens.get(self.ATTR_DELAY, self.ATTR_DELAY_DEFAULT_VAL)
try:
self.delay = int(s)
except ValueError, e:
self.delay = 0
# beware for max and min: 0 to 10,000 = 10 seconds
if (self.delay < 0):
self.delay = 0
elif self.delay > 10000:
self.delay = 10000
# database. real one
self.db = settings.DATABASES.get(self.db_name, None)
if self.db == None:
raise DwException(self.db_name, "Unknown database %s." % self.db_name)
self.true_sql = self.tokens.sqltext
# now that we have a sql, try to replace parameters in it.
# Parameter looks like ${param_name}
p = re.compile(u'\$\{[a-z]+[0-9]*\}')
# get the list of all mentioned params
lst = p.findall(self.true_sql)
# loop through all these variables and try to replace
# them with params passed to us
# test with sql with parameters
for item in lst:
stripped_item = item[2:-1]
# TODO check for default value here
value = 'None'
if stripped_item in params:
value = params[stripped_item]
value = urllib.unquote(value)
# replace it
self.true_sql = self.true_sql.replace(item, value)
# get a connection from somewhere
self.get_con()
self.cur = self.con.cursor()
self.cur.execute(self.true_sql)
# download_link + '">csv</a></td></tr>' + "\n").format(col_count)
# close table tag
closing_line += u"</table>\n" + add_line
yield closing_line
except DwException, e:
if self.con is not None:
self.con.close()
raise e
except Exception, e:
if self.con is not None:
self.con.close()
traceback.print_exc()
# reraise it wrapped in our class
raise DwException(self.db_name, "Some source exception", e)
def process_block_as_csv(self, tempfile, block_text, user, **params):
#tokens = self.parse_block(block_text)
#db_name = tokens.get(self.ATTR_DB, settings.DATABASE_DEFAULT_ALIAS).strip()
#con = None
sett = settings.CSV_DOWNLOAD_FORMAT['en']
sep = unicode(sett.get('FIELD_SEPARATOR', ','))
enc = unicode(sett.get('ENCODING', 'utf8'))
nl = sett.get('NEWLINE', "\n")
try:
self.prepare_for_query(block_text, user, **params)
# process columns first
class SqlLineChartStreamer(dwstreamer.BaseDwStreamer):
"""Creates a simple line chart
A query should return columns in a given order:
1st - An x-axis tick marks
2nd - y value
3rd - etc. other lines if there is more than one
"""
TAGNAME = 'sqllinechart'
# === Attributes that can be used with tagname sqllinechart ===
# this is as usual
ATTR_DB = 'db'
ATTR_TITLE = 'title'
ATTR_WIDTH = 'width'
ATTR_HEIGHT = 'height'
ATTR_XLABEL = 'xlabel'
ATTR_YLABEL = 'ylabel'
ATTR_FONT_SIZE = 'fontsize'
# grid lines to draw. Can be 'x', 'y', and 'both'
ATTR_GRID = 'grid'
# min and max y values. In floats or integers. Otherwise ignored.
ATTR_MINY = 'miny'
ATTR_MAXY = 'maxy'
# Here we may specify several colors delimited by commas
# Like green, red, black separated by commas
ATTR_LINE_COLOR = 'linecolor'
# line width in pixels
ATTR_LINE_WIDTH = 'linewidth'
# the line color if user hasn't specified one
DEFAULT_LINE_COLOR = 'black'
# fill color for line
ATTR_FILL_COLOR = 'fillcolor'
DEFAULT_FILL_COLOR = 'none'
ATTR_MARKER = 'marker'
ATTR_MARKER_DEFAULT = ''
# the line color if user has specified it,
# but made an error
USER_ERROR_LINE_COLOR = 'grey'
ATTR_LEGEND = 'legend'
ATTR_VALUE_YES = 'yes'
ATTR_VALUE_NO = 'no'
ATTR_LEGEND_DEFAULT_VALUE = ATTR_VALUE_YES
# maximum number of line points allowed
# if there is more, just don't draw them
MAX_X_POINTS = 20000
# maximum number of parallel lines that can be shown
# simultaneously.
MAX_LINE_SETS = 20
# default top margin over the top of the maximum y value
# measured in fractions of the difference between min and max
# values
DEFAULT_TOP_Y_AXIS_MARGIN = 0.1
# font size for text and tick marks
DEFAULT_FONT_SIZE = 10
def factory(self):
return SqlLineChartStreamer()
def make_unicode_or_none(self, val):
""" Given a value try to make a unicode string
out of it, transforming strings, numbers and dates.
Or make it None. Assume non-unicode strings
are in utf-8
"""
ret = None
if type(val) is str:
ret = val.decode('utf-8')
elif type(val) is unicode:
ret = val
else:
ret = unicode(val)
return ret
def get_tagname(self):
return self.TAGNAME
# gets a database connection
# it should work with several connections
# throws a DWDatabaseError
def get_con(self, db):
con = dwwiki.connectors.get_connection(db)
return con
# # TODO work around if connection does not exist
# #db = settings.DATABASES[dbname]
#
# #con = MySQLdb.connect(host=db['HOST'], user=db['USER'], passwd=db['PASSWORD'], db=db['DB'],
# # charset='utf8',
# # cursorclass = MySQLdb.cursors.SSCursor)
# con = None
# # TODO different connectors should live in different files
# if db['ENGINE'] == 'mysql':
# import mysql.connector
# timeout = db.get('QUERY_TIMEOUT', 60) * 1000 # milliseconds
# con = mysql.connector.connect(
# user=db['USER'],
# passwd=db['PASSWORD'],
# host=db['HOST'],
# database=db['DB'],
# connection_timeout=timeout
# )
# elif db['ENGINE'] == 'postgresql':
# import psycopg2
# # set query timeout as options to psycopg
# timeout = db.get('QUERY_TIMEOUT', 60) * 1000 # milliseconds
# con = psycopg2.connect(
# user=db['USER'],
# password=db['PASSWORD'],
# host=db['HOST'],
# database=db['DB'],
# port=db['PORT'],
# options="-c statement_timeout=%d" % timeout
# )
# con.set_client_encoding('utf-8')
# elif db['ENGINE'] == 'sqlite':
# import sqlite3
# timeout = db.get('QUERY_TIMEOUT', 60) * 1000 # milliseconds
# con = sqlite3.connect(
# database=db['DB'],
# timeout=timeout
# )
#
#
# #con.set_client_encoding('unicode')
#
# return con
def parse_block(self, block_text):
"""Parses sql block into tokens
"""
# Valid grammar looks like this:
# {sqllinechart: title='Some string' | colors=green, yellow}
# make a grammar
block_start = Literal("{")
sql_start = Keyword(self.TAGNAME, caseless=True)
colon = Literal(":")
sql_end = Literal("}")
separator = Literal("|")
block_end = Keyword("{" + self.TAGNAME + "}", caseless=True)
# params
field_name = Word(alphanums)
equal_sign = Suppress(Literal("="))
# whatever value
field_value = (CharsNotIn("|}"))
# param name and value
param_group = Group(field_name + equal_sign + field_value)
# list of all params
param_list = delimitedList(param_group, '|')
# helper
param_dict = Dict(param_list)
# sql text
sql_text = SkipTo(block_end)
sqldecl = Forward()
sqldecl << (block_start + sql_start + Optional(colon) +
Optional(param_dict) + sql_end +
sql_text.setResultsName('sqltext') + block_end)
block_str = "".join(block_text)
tokens = sqldecl.parseString(block_str)
return tokens
def process_block(self, block_text, user, block_id, **params):
def int_or_none(val):
"""Returns an integer or none. May pass anything
"""
try:
ret = int(val)
except ValueError:
ret = None
return ret
def float_or_none(val):
"""Returns a float or none. May pass anything
"""
if val is None:
return val
else:
try:
ret = float(val)
except ValueError:
ret = None
return ret
def int_or_default(val, default=0):
""" Returns int or a given default
"""
try:
ret = int(val)
except ValueError:
ret = default
return ret
try:
tokens = self.parse_block(block_text)
except Exception, e:
traceback.print_exc()
# reraise it wrapped in our class
raise DwException('', "Errpr in graph definition", e)
# get our tokens
db_name = tokens.get(self.ATTR_DB,
settings.DATABASE_DEFAULT_ALIAS).strip()
# chart title. empty by default
chart_title = tokens.get(self.ATTR_TITLE, '').strip()
# line colors
line_color_str = tokens.get(self.ATTR_LINE_COLOR,
self.DEFAULT_LINE_COLOR).strip()
line_color_str = line_color_str.encode('utf-8')
# separate into array
# this is not final. Probably there are errors here.
# We'll deal with it later, when we read the columns
line_color_list = line_color_str.split(',')
fill_color_str = tokens.get(self.ATTR_FILL_COLOR,
self.DEFAULT_FILL_COLOR).strip()
fill_color_str = fill_color_str.encode('utf-8')
fill_color_list = fill_color_str.split(',')
# line style.
marker_str = tokens.get(self.ATTR_MARKER,
self.ATTR_MARKER_DEFAULT).strip()
#line_style_list = line_style_str.split(',')
# TODO check line styles
# chart width and height in pixels. Beware, the user
# inevitably will write anything but valid numbers
chart_width = int_or_none(tokens.get(self.ATTR_WIDTH, '').strip())
chart_height = int_or_none(tokens.get(self.ATTR_HEIGHT, '').strip())
xlabel = tokens.get(self.ATTR_XLABEL, '').strip()
ylabel = tokens.get(self.ATTR_YLABEL, '').strip()
font_size = int_or_default(
tokens.get(self.ATTR_FONT_SIZE, self.DEFAULT_FONT_SIZE))
if font_size <= 0:
font_size = self.DEFAULT_FONT_SIZE
# max and min y
miny = float_or_none(tokens.get(self.ATTR_MINY, None))
maxy = float_or_none(tokens.get(self.ATTR_MAXY, None))
# legend
legend_str = tokens.get(self.ATTR_LEGEND,
self.ATTR_LEGEND_DEFAULT_VALUE).strip()
# check for errors
if legend_str not in (self.ATTR_VALUE_YES, self.ATTR_VALUE_NO):
legend_str = self.ATTR_LEGEND_DEFAULT_VALUE
# grid
grid = tokens.get(self.ATTR_GRID, None)
if grid is not None:
grid = grid.strip()
if grid not in ['x', 'y', 'both']:
grid = None
# line widths for all the lines in pixels separated by commas
line_width_str = tokens.get(self.ATTR_LINE_WIDTH, '').strip()
# also check for negative numbers or zeroes
if (chart_width is not None) and (chart_height is not None):
if (chart_width <= 0):
chart_width = None
if (chart_height <= 0):
chart_height = None
else:
# some of them are none
# set both to none
chart_width = None
chart_height = None
con = None
# if anything, we rethrow the exception
try:
db = settings.DATABASES.get(db_name, None)
# non-existent database - exception
if db == None:
raise DwException(db_name, "Unknown database %s." % db_name)
true_sql = tokens.sqltext
# now that we have a sql, try to replace parameters in it.
# Parameter looks like ${param_name}
p = re.compile(u'\$\{[a-z]+[0-9]*\}')
# get the list of all mentioned params
lst = p.findall(true_sql)
# loop through all these variables and try to replace
# them with params passed to us
for item in lst:
stripped_item = item[2:-1]
# TODO check for default value here
value = 'None'
if stripped_item in params:
value = params[stripped_item]
value = urllib.unquote(value)
# replace it
true_sql = true_sql.replace(item, value)
# get a connection from somewhere
con = self.get_con(db)
cur = con.cursor()
#print true_sql
cur.execute(true_sql)
row = cur.fetchone()
# TODO Now if row is None it means the set is empty
# in this case we have to generate a stub
# for now assume the data is present
# Get columns for labels
# We need at least two columns
# first one is x axis, column heading does not matter
# the second is y axis values, so are 3rd and subsequent ones
# their headings are the legend if we have to show it
columns = cur.description
col_count = len(columns)
if col_count < 2:
raise Exception("There should be at least two columns")
# Now we can have multiple lines here.
# col 0 is x axis marks. x axis values we count from zero to 1,2 etc.
# always. If some rows have nulls in this column, it means
# there will be no points here.
# But the x value will be counted anyway.
# The line will continue as usual.
# on the previous row value. If a non-null is encountered again
# on subsequent row, the line will start again there.
# col 2,3 etc. are other lines.
# Max allowed number for these columns is set to 20
# For each line - the color and style is set in graph attributes
# To plot a line for each column we need two arrays:
# One - all the y values. If null is encountered, the y value is
# set to the previous value. If first value is null, it is set to
# miny value. if miny is none, then to zero.
# Two - masked x points.
# Two arrays for each column, arranged as a dictionary 'yvalues', 'xmasked'
# We don't know beforehand how many columns there are,
# so we create a list to keep them
col_values_list = list()
# x coordinates are the same for all cols, so we make one array
x_values_list = list()
# collect column titles for cols 1 - ... as labels
legend_list = list()
n = 0
for col in columns:
# that's column name
s = col[0]
if type(s) is str:
s = s.decode('utf-8')
elif type(s) is unicode:
s = col[0]
if n > 0:
legend_list.append(s)
n += 1
# now deal with line color for each column
# TODO line colors should be specified exactly
# like matplotlib understands
color_converter = ColorConverter()
final_color_list = list()
final_fill_list = list()
for i in range(0, col_count - 1):
# Just in case the user specified less colors, than columns
if i < len(line_color_list):
color_str = line_color_list[i]
else:
# we have more columns, than colors specified by user
color_str = self.DEFAULT_LINE_COLOR
# The color may well be gibberish
try:
color_rgba = color_converter.to_rgba(color_str)
except Exception, e:
#sys.stderr.write("Color error: %s\n" % color_str)
color_str = self.USER_ERROR_LINE_COLOR
final_color_list.append(color_str)
# fill color
if i < len(fill_color_list):
fill_str = fill_color_list[i].strip()
else:
# more columns than colors
fill_str = 'none'
try:
color_rgba = color_converter.to_rgba(fill_str)
except Exception, e:
#sys.stderr.write("Color error: %s\n" % color_str)
fill_str = 'none'
final_fill_list.append(fill_str)
# now add a dictionary for each column
d = {'yvalues': list(), 'xmasked': list()}
col_values_list.append(d)
class SqlInlineStreamer(dwstreamer.BaseDwStreamer):
"""Returns query results just as string separated by spaces"""
ATTR_DB = 'db'
TAGNAME = 'sqlinline'
def factory(self):
return SqlInlineStreamer()
def get_tagname(self):
return self.TAGNAME
def get_con(self, db):
con = dwwiki.connectors.get_connection(db)
return con
# # TODO work around if connection does not exist
# #db = settings.DATABASES[dbname]
#
# #con = MySQLdb.connect(host=db['HOST'], user=db['USER'], passwd=db['PASSWORD'], db=db['DB'],
# # charset='utf8',
# # cursorclass = MySQLdb.cursors.SSCursor)
# con = None
# # TODO different connectors should live in different files
# if db['ENGINE'] == 'mysql':
# import mysql.connector
# timeout = db.get('QUERY_TIMEOUT', 60) * 1000 # milliseconds
# con = mysql.connector.connect(
# user=db['USER'],
# passwd=db['PASSWORD'],
# host=db['HOST'],
# database=db['DB'],
# connection_timeout=timeout
# )
# elif db['ENGINE'] == 'postgresql':
# import psycopg2
# # set query timeout as options to psycopg
# timeout = db.get('QUERY_TIMEOUT', 60) * 1000 # milliseconds
# con = psycopg2.connect(
# user=db['USER'],
# password=db['PASSWORD'],
# host=db['HOST'],
# database=db['DB'],
# port=db['PORT'],
# options="-c statement_timeout=%d" % timeout
# )
# con.set_client_encoding('utf-8')
# elif db['ENGINE'] == 'sqlite':
# import sqlite3
# timeout = db.get('QUERY_TIMEOUT', 60) * 1000 # milliseconds
# con = sqlite3.connect(
# database=db['DB'],
# timeout=timeout
# )
#
#
# #con.set_client_encoding('unicode')
#
# return con
# returns sql block parsed into tokens
def parse_block(self, block_text):
# make a grammar
block_start = Literal("{")
sql_start = Keyword("sqlinline", caseless=True)
colon = Literal(":")
sql_end = Literal("}")
separator = Literal("|")
block_end = Keyword("{sqlinline}", caseless=True)
# params
field_name = Word(alphanums)
equal_sign = Suppress(Literal("="))
# whatever value
field_value = (CharsNotIn("|}"))
# param name and value
param_group = Group(field_name + equal_sign + field_value)
# list of all params
param_list = delimitedList(param_group, '|')
# helper
param_dict = Dict(param_list)
# sql text
sql_text = SkipTo(block_end)
sqldecl = Forward()
sqldecl << (block_start + sql_start + Optional(colon) +
Optional(param_dict) + sql_end +
sql_text.setResultsName('sqltext') + block_end)
block_str = "".join(block_text)
tokens = sqldecl.parseString(block_str)
return tokens
def format_value(self, val):
tp = type(val).__name__
if tp == 'str':
res = val.decode('utf-8')
elif tp == 'unicode':
res = unicode(val)
elif tp in ['Decimal', 'float']:
res = "{0:0.2f}".format(val)
elif tp == 'date':
res = val.strftime("%d.%m.%Y")
elif tp in ['int', 'long']:
res = "{0}".format(val)
else:
# unknown datatype
res = unicode(val)
return res
def process_block(self, block_text, user, **params):
""" Yields all query results at once. Only once.
May return an exception.
"""
try:
tokens = self.parse_block(block_text)
db_name = tokens.get(self.ATTR_DB,
settings.DATABASE_DEFAULT_ALIAS).strip()
con = None
db = settings.DATABASES.get(db_name, None)
if db == None:
raise DwException(db_name, "Unknown database %s." % db_name)
true_sql = tokens.sqltext
p = re.compile(u'\$\{[a-z]+[0-9]*\}')
lst = p.findall(true_sql)
for item in lst:
stripped_item = item[2:-1]
# TODO check for default value here
value = 'None'
if stripped_item in params:
value = params[stripped_item]
value = urllib.unquote(value)
# replace it
true_sql = true_sql.replace(item, value)
# get a connection from somewhere
con = self.get_con(db)
cur = con.cursor()
cur.execute(true_sql)
row = cur.fetchone()
num_rows = 0
while row is not None:
if num_rows == db.get('LIMIT_ROWS', 1000):
# we are 1 row over limit
add_line = htmlconstants.SQL_OVER_LIMIT.format(num_rows)
row = None
break
else:
retstr = u''
for val in row:
retstr = retstr + ' ' + self.format_value(val)
retstr += "\n"
yield retstr
num_rows += 1
row = cur.fetchone()
con.close()
con = None
except DwException, e:
if con is not None:
con.close()
raise e
except Exception, e:
if con is not None:
con.close()
# reraise it wrapped in our class
raise DwException(db_name, "Some source exception", e)
def process_block(self, block_text, user, block_id, **params):
def int_or_none(val):
"""Returns an integer or none. May pass anything
"""
try:
ret = int(val)
except ValueError:
ret = None
return ret
def int_or_default(val, default=0):
""" Returns int or a given default
"""
try:
ret = int(val)
except ValueError:
ret = default
return ret
def float_or_default(val, default=0.0):
""" Returns float or a given default
"""
try:
ret = float(val)
except ValueError:
ret = default
return ret
tokens = self.parse_block(block_text)
# get our tokens
db_name = tokens.get(self.ATTR_DB,
settings.DATABASE_DEFAULT_ALIAS).strip()
# chart title. empty by default
chart_title = tokens.get(self.ATTR_TITLE, '').strip()
# chart width and height in pixels. Beware, the user
# inevitably will write anything but valid numbers
chart_width = int_or_none(tokens.get(self.ATTR_WIDTH, '').strip())
chart_height = int_or_none(tokens.get(self.ATTR_HEIGHT, '').strip())
ylabel = tokens.get(self.ATTR_YLABEL, '').strip()
xlabel = tokens.get(self.ATTR_XLABEL, '').strip()
font_size = int_or_default(
tokens.get(self.ATTR_FONT_SIZE, self.DEFAULT_FONT_SIZE))
if font_size <= 1:
font_size = self.DEFAULT_FONT_SIZE
if font_size >= 100:
font_size = self.DEFAULT_FONT_SIZE
# grid
grid = tokens.get(self.ATTR_GRID, 'none').strip().lower()
if grid not in ('x', 'y', 'both', 'none'):
grid = 'none'
# also check for negative numbers or zeroes
if (chart_width is not None) and (chart_height is not None):
if (chart_width <= 0) or (chart_height <= 0):
# both none
chart_width = None
chart_height = None
else:
# some of them are none
# set both to none
chart_width = None
chart_height = None
# xtick rotation
x_tick_rotation = int_or_default(
tokens.get(self.ATTR_XTICK_ROTATION, self.DEFAULT_XTICK_ROTATION))
if x_tick_rotation > 90:
x_tick_rotation = 90
if x_tick_rotation < -90:
x_tick_rotation = -90
# legend
legend_str = tokens.get(self.ATTR_LEGEND,
self.ATTR_VALUE_NO).strip().lower()
if legend_str not in (self.ATTR_VALUE_NO, self.ATTR_VALUE_YES):
legend_str = self.ATTR_VALUE_NO
# legend location
legend_location = tokens.get(
self.ATTR_LEGEND_LOCATION,
self.DEFAULT_LEGEND_LOCATION).strip().lower()
# this is not necessary, matplotlib does it by himself,
# yet we check it here to avoid warnings on stdout
if legend_location not in self.VALID_LEGEND_LOCATIONS:
legend_location = self.DEFAULT_LEGEND_LOCATION
# bar width
bar_width = float_or_default(
tokens.get(self.ATTR_BAR_WIDTH, self.DEFAULT_BAR_WIDTH),
self.DEFAULT_BAR_WIDTH)
if bar_width > self.MAX_BAR_WIDTH:
bar_width = self.MAX_BAR_WIDTH
if bar_width < self.MIN_BAR_WIDTH:
bar_width = self.MIN_BAR_WIDTH
# stacked
stacked = tokens.get(self.ATTR_STACKED,
self.ATTR_VALUE_NO).strip().lower()
if stacked not in (self.ATTR_VALUE_YES, self.ATTR_VALUE_NO):
stacked = self.ATTR_VALUE_NO
con = None
# if anything, we rethrow the exception
try:
db = settings.DATABASES.get(db_name, None)
# non-existent database - exception
if db == None:
raise DwException(db_name, "Unknown database %s." % db_name)
true_sql = tokens.sqltext
# now that we have a sql, try to replace parameters in it.
# Parameter looks like ${param_name}
p = re.compile(u'\$\{[a-z]+[0-9]*\}')
# get the list of all mentioned params
lst = p.findall(true_sql)
# loop through all these variables and try to replace
# them with params passed to us
for item in lst:
stripped_item = item[2:-1]
# TODO check for default value here
value = 'None'
if stripped_item in params:
value = params[stripped_item]
value = urllib.unquote(value)
# replace it
true_sql = true_sql.replace(item, value)
# get a connection from somewhere
con = self.get_con(db)
cur = con.cursor()
cur.execute(true_sql)
row = cur.fetchone()
# TODO Now if row is None it means the set is empty
# in this case we have to generate a stub
# for now assume the data is present
# Get columns for labels
# We need at least two columns
# first one is x axis, column heading does not matter
# the second is y axis values
# the third one is the color of values
# if there is no third column, use default color
# which is gray
columns = cur.description
col_count = len(columns)
if col_count < 2:
raise DwException(db_name,
"There should be at least two columns")
# second column should be a number. Don't test it here.
# we will try to test each value
# every odd column is value
# every even column is color
# Now traverse all the rows
# x_tick_marks is col 0 values
x_tick_marks = list()
# y_values is an array of bar heights
# stored in the second column, fourth etc. columns
# this is array of arrays
y_values_array = list()
y_colors_array = list()
for i in range(1, col_count):
is_value = i % 2 == 1
is_color = i % 2 == 0
if is_value is True:
y_values = list()
y_values_array.append(y_values)
# add color array in any case
y_colors = list()
y_colors_array.append(y_colors)
#if is_color is True:
#y_colors_array.append(y_colors)
#y_values = list()
# bar_colors is an array of bar colors
# stored in the third column
# if there is no third column,
# assume the color is grey
#y_colors = list()
# Now traverse. If row count is too big,
# just stop where it exceeds the maximum allowed number
# which we set to 100 for now
curr_bar = 0
color_converter = ColorConverter()
while row is not None:
# tick mark as unicode
mark = row[0]
if mark is None:
mark = ''
if type(mark) is str:
mark = mark.decode('utf-8')
elif type(mark) is unicode:
pass
else:
# TODO maybe a number
# we need to convert it to a string
# according to it's type (integer, decimal, float)
# date, time, datetime
mark = unicode(mark)
x_tick_marks.append(mark)
# collect values and colors
for i in range(1, col_count):
is_value = i % 2 == 1
is_color = i % 2 == 0
# value itself. plot does not support Decimal,
# need to convert to float
if is_value is True:
value_index = i / 2
y_values = y_values_array[value_index]
value = row[i]
tp = type(value).__name__
if tp is 'Decimal':
value = float(value)
# all nulls are replaced with zeroes
if value is None:
value = 0.0
y_values.append(value)
# now colors
color_col = i + 1
y_colors = y_colors_array[value_index]
# color himself +1 col if exists
color_str = None
if color_col < len(row):
color_str = self.make_unicode_or_none(row[i + 1])
if color_str is None:
color_str = self.DEFAULT_BAR_COLOR
# Now try to make a color. If it's unsuccessful,
# it returns an exception
try:
color_rgba = color_converter.to_rgba(color_str)
except Exception, e:
#sys.stderr.write("Color error: %s\n" % color_str)
color_rgba = color_converter.to_rgba(
self.USER_ERROR_BAR_COLOR)
# Hopefully, color_rgba is now set
# to a tuple of R, G, B and Alpha
y_colors.append(color_rgba)
# increment row counter
curr_bar += 1
if curr_bar >= self.MAX_BARS:
break
else:
row = cur.fetchone()
# Now we have collected all the values, marks and colors
con.close()
con = None
# Build a graph
# 80 dots per inch. So 7 x 4 inches will make 560 x 320 pixels
if (chart_width > 0) and (chart_height > 0):
fig = plt.figure(figsize=[chart_width / 80, chart_height / 80],
dpi=80)
else:
fig = plt.figure()
ax = fig.add_subplot(111)
# data size
#N = len(curr_bar) # number of bar groups - scale units for x axis = number of query rows
# arrange N between 0 and N-1. Returns an array
ind = np.arange(curr_bar) # distribute equally
# todo expose bar width
if stacked == self.ATTR_VALUE_NO:
width = bar_width / len(
y_values_array) # width of the bars (measures?)
else:
width = bar_width
min_value = 0
max_value = 0
for y_values in y_values_array:
# remember it may be negative
temp_min_value = min(y_values)
min_value = min(temp_min_value, min_value)
if min_value > 0:
min_value = 0
temp_max_value = max(y_values)
max_value = max(temp_max_value, max_value)
if max_value < 0:
max_value = 0
# now add additional 10% of min-max range at the top
value_range = max_value - min_value
top_y_axis_margin = value_range * self.DEFAULT_TOP_Y_AXIS_MARGIN
# no margin for bottom
bottom_y_axis_margin = 0
# if we have negative bars, set margin below too
if min_value < 0:
bottom_y_axis_margin = top_y_axis_margin
# here we set final max and min y axis values to account for our margins
max_y_axis_value = max_value + top_y_axis_margin
min_y_axis_value = min_value - bottom_y_axis_margin
# cycle through values and draw bars
for i in range(len(y_values_array)):
y_values = y_values_array[i]
y_colors = y_colors_array[i]
# bar labels
# get a description
# it's in columns 1,3,5 etc.
# i=0 => col=1
# i=1 => col=3
col_index = i * 2 + 1
col = columns[col_index]
bar_label = col[0]
if type(col[0]) is str:
bar_label = col[0].decode('utf-8')
elif type(col[0]) is unicode:
bar_label = col[0]
# The Drawing
if stacked == self.ATTR_VALUE_NO:
rects1 = ax.bar(ind + i * width,
y_values,
width,
color=y_colors,
label=bar_label)
else:
rects1 = ax.bar(ind,
y_values,
width,
color=y_colors,
label=bar_label)
ax.set_xlim(-1 + bar_width, len(ind))
ax.set_ylim(min_y_axis_value, max_y_axis_value)
# draw horizontal grid lines
if grid <> 'none':
ax.grid(which='major', axis=grid)
if ylabel <> '':
plt.ylabel(ylabel, fontsize=font_size)
if xlabel <> '':
plt.xlabel(xlabel, fontsize=font_size)
ax.set_title(chart_title, fontsize=font_size)
# font for y major ticks
for tick in ax.yaxis.get_major_ticks():
tick.label.set_fontsize(font_size)
# ticks on top and bottom
ax.set_xticks(ind + bar_width / 2)
#canvas = FigureCanvasAgg(fig)
x_tick_labels = ax.set_xticklabels(x_tick_marks)
plt.setp(x_tick_labels,
rotation=x_tick_rotation,
fontsize=font_size)
# legend
if legend_str == self.ATTR_VALUE_YES:
ax.legend(loc=legend_location, prop={'size': font_size})
plt.tight_layout()
tempfiledir = cherrypy.config['dwwiki.tempfiledir']
tempfile.tempdir = tempfiledir
try:
f = tempfile.NamedTemporaryFile(delete=False)
# print graph to a temporary file
# TODO we assume it prints png. Yet I don't know
# why. Is it the default format?
#canvas.print_figure(f)
#canvas.print_png(f)
fig.savefig(f)
f.close()
# Now all we need is to encode the file in base64,
# then serve it as an <img>
input_file = open(f.name, 'rb')
output_file = cStringIO.StringIO()
base64.encode(input_file, output_file)
# we have it as a png string
#yield '<img alt="graph" src="data:image/png;base64,'
yield '<img alt="graph" src="data:image/png;base64,'
yield output_file.getvalue()
yield "\"/>\n"
output_file.close()
input_file.close()
finally:
os.remove(f.name)