def serve(self):
"""Handle the request and serve the response"""
RequestBase.serve(self)
# Request values
self.args = {}
self.args['UUID'] = self.params.get_item('uuid')
self.args['FORMAT'] = self.params.get_item('format')
# Response values
self.values = {}
self.values['ENGINE'] = None
self.values['CONNECTION'] = None
self.values['CATALOG'] = None
self.values['DESCRIPTION'] = None
self.values['SQL'] = None
self.values['REPORT'] = None
self.values['ERRORS'] = []
self.values['FIELDS'] = None
self.values['DATA'] = None
self.values['REQUIRES'] = []
self.values['REQUIRES'].append('jquery')
self.values['REQUIRES'].append('tablesorter')
# Parameters
self.parameters = OrderedDict()
self.extra_parameters = {}
engine = None
engine_settings = self.open_settings_db()
# Get query information
existing_fields, existing_data = engine_settings.get_data(
'SELECT catalog, description, sql, report, parameters '
'FROM queries '
'WHERE uuid=?',
None,
(self.args['UUID'], ))
if existing_data:
self.values['CATALOG'] = existing_data[0][0]
self.values['DESCRIPTION'] = existing_data[0][1]
self.values['SQL'] = existing_data[0][2].encode('utf-8').replace(
'\r\n', ' ').replace('\n', ' ').replace('\r', ' ')
self.values['REPORT'] = existing_data[0][3]
self.values['PARAMETERS'] = existing_data[0][4].encode('utf-8')
else:
self.values['ERRORS'].append('Query not found')
if not self.values['ERRORS']:
# Get the catalog information
existing_fields, existing_data = engine_settings.get_data(
'SELECT engine, connstr, server, database, username, '
'password, encoding '
'FROM catalogs '
'WHERE name=?',
None,
(self.values['CATALOG'], ))
if existing_data:
self.values['ENGINE'] = existing_data[0][0]
self.values['SERVER'] = existing_data[0][2]
self.values['DATABASE'] = existing_data[0][3]
self.values['USERNAME'] = existing_data[0][4]
self.values['PASSWORD'] = existing_data[0][5]
self.values['ENCODING'] = existing_data[0][6]
self.values['CONNECTION'] = self.prepare_connection_string(
connection=existing_data[0][1],
engine=self.values['ENGINE'],
server=self.values['SERVER'],
database=self.values['DATABASE'],
username=self.values['USERNAME'],
password=self.values['PASSWORD'])
else:
self.values['ERRORS'].append('Catalog not found')
if not self.values['ERRORS']:
# Open catalog database
engine = self.open_db_from_engine_type(
self.values['ENGINE'],
self.values['CATALOG'],
self.values['CONNECTION'],
self.values['USERNAME'],
self.values['PASSWORD'],
self.values['DATABASE'],
self.values['SERVER'])
# Parse parameters
if self.values['PARAMETERS']:
list_parameters = self.values['PARAMETERS'].replace(
'\r\n', '\n').replace('\r', '\n').split('\n')
for parameter in list_parameters:
param_name, param_config = parameter.split('=', 1)
# Get the parameter from the parameters table if needed
if param_config.startswith('parameter:'):
existing_fields, existing_data = engine_settings.get_data(
statement='SELECT content FROM parameters WHERE name=?',
replaces=None,
parameters=(param_config[10:], ))
if existing_data:
param_config = existing_data[0][0]
list_parameters.insert(
list_parameters.index(parameter),
'%s=%s' % (param_name, param_config))
list_parameters.remove(parameter)
for parameter in list_parameters:
param_name, param_config = parameter.split('=', 1)
# Parameter configuration
if param_config.startswith('list:'):
# List of values
param_values = param_config[5:].split(',')
elif param_config.startswith('range:'):
# Range between two values
param_value1, param_value2 = param_config[6:].split('-', 1)
param_values = range(
int(param_value1), int(param_value2) + 1)
elif param_config.startswith('sql:'):
# Result of a SQL statement
existing_fields, existing_data = engine.get_data(
param_config[4:])
param_values = []
for param_value1 in existing_data:
param_values.append(param_value1[0])
elif param_config.startswith('text:'):
# Input text
param_values = ''
elif param_config.startswith('date:'):
# Date input text
param_values = datetime.date.today()
if 'jquery-ui' not in self.values['REQUIRES']:
self.values['REQUIRES'].append('jquery-ui')
elif param_config.startswith('values:'):
# List of key=description values
param_values = []
for param_value1 in param_config[7:].split(','):
param_values.append(param_value1.split('=', 1))
elif param_config.startswith('parameters:'):
param_values = []
for param_value1 in param_config[11:].split(','):
param_values.append(param_value1.split('=', 1)[0])
else:
# Not implemented parameter type
raise Exception('Not implemented parameter type: %s' %
param_config)
self.parameters[param_name] = param_values
# Check all the parameters for parameters type values
for parameter in list_parameters:
param_name, param_config = parameter.split('=', 1)
# param_name is the parameters: name
if param_config.startswith('parameters:'):
# A parameter of type parameters has the following syntax:
# NAME=parameters:PARAMVALUE1=FIELD1=VALUE1,PARAMVALUE2=FIELD1=VALUE1
self.args[param_name] = self.params.get_item(param_name, None)
# self.args[param_name] is the selected PARAMVALUE
param_values = param_config[11:].split(',')
for param_values in param_values:
param_name2, param_values = param_values.split('=', 1)
# param_name2 is each PARAMVALUE
# param_values is the FIELD=VALUE pairs list
param_values = param_values.split(';')
for parameter in param_values:
# parameter is each FIELD=value pair
param_value1, param_value2 = parameter.split('=', 1)
# Check if the parameters parameter was set
if self.args[param_name] == param_name2:
self.args[param_value1] = param_value2
else:
self.args[param_value1] = None
self.extra_parameters[param_value1] = self.args[param_value1]
# Exit after the current PARAMVALUE was found
if self.args[param_name] == param_name2:
break
break
# Check all the parameters if they were configured
for parameter in self.parameters.keys():
self.args[parameter] = self.params.get_item(parameter, None)
if self.args[parameter] is not None:
self.args[parameter] = self.args[parameter].replace(
'\'', '\'\'')
else:
self.values['ERRORS'].append(
'Parameter %s was not provided' % parameter)
# Fill parameters with extra parameters
for parameter in self.extra_parameters.keys():
if self.extra_parameters.get(parameter, None) is not None:
self.args[parameter] = self.extra_parameters[parameter].replace(
'\'', '\'\'')
if engine:
if not self.values['ERRORS']:
# Get the data from the query
self.values['FIELDS'], self.values['DATA'] = engine.get_data(
self.values['SQL'], self.args, None)
engine.close()
engine_settings.close()
engine_settings = None
configuration.set_locale(None)
if not self.values['ERRORS'] and self.args['FORMAT'] == 'csv':
# Returns results like text/csv
self.set_content_type('text/csv')
self.set_filename('%s.csv' % self.values['DESCRIPTION'])
return self.output_to_csv(self.values['DATA'])
else:
return self.get_template('reports/%s.tpl' % self.values['REPORT'],
ARGS=self.args,
VALUES=self.values,
PARAMETERS=self.parameters,
printable_text_for_encoding=self.printable_text_for_encoding,
datetime=datetime)
def serve(self):
"""Handle the request and serve the response"""
RequestBase.serve(self)
# Request values
self.args = {}
self.args['CONFIRM'] = self.params.get_item('confirm')
self.args['SQL'] = self.params.get_utf8_item('sql')
self.args['CATALOG'] = self.params.get_utf8_item('catalog')
self.args['LIST_TABLES'] = self.params.get_item('list_tables')
self.args['FORMAT'] = self.params.get_item('format')
self.args['SAVE'] = self.params.get_item('save')
# Save query by redirecting to the queries page
if self.args['SAVE']:
return 'REDIRECT:queries?%s' % urllib.urlencode(
{
'catalog': self.args['CATALOG'].encode('utf-8'),
'sql': self.args['SQL'].encode('utf-8'),
'confirm': 'ok',
})
# Response values
self.values = {}
self.values['ERRORS'] = []
self.values['TABLES'] = []
self.values['FIELDS'] = None
self.values['DATA'] = None
# Get the configured catalogs
engine = self.open_settings_db()
self.values['CATALOGS'] = engine.get_data(
'SELECT name, description, engine, connstr, server, database, '
'username, password, encoding '
'FROM catalogs ORDER BY name')[1]
for catalog in self.values['CATALOGS']:
if catalog[0] == self.args['CATALOG']:
catalog_engine = catalog[2]
catalog_server = catalog[4]
catalog_database = catalog[5]
catalog_username = catalog[6]
catalog_password = catalog[7]
catalog_connection = self.prepare_connection_string(
connection=catalog[3],
engine=catalog_engine,
server=catalog_server,
database=catalog_database,
username=catalog_username,
password=catalog_password)
catalog_encoding = catalog[8]
break
else:
catalog_engine = ''
catalog_connection = ''
catalog_server = ''
catalog_database = ''
catalog_username = ''
catalog_password = ''
catalog_encoding = ''
engine.close()
# Check the requested arguments for errors
if self.args['CONFIRM']:
# Get data
engine = self.open_db_from_engine_type(
catalog_engine,
self.args['CATALOG'],
catalog_connection,
catalog_username,
catalog_password,
catalog_database,
catalog_server)
if engine:
# List the catalog tables if available and requested
self.values['TABLES'] = [(t, t) for t in engine.list_tables()] \
if self.args['LIST_TABLES'] else ()
# Get fields and data if SQL argument was provided
self.values['FIELDS'], self.values['DATA'] = engine.get_data(
self.args['SQL'].encode('utf-8').replace(
'\r\n', ' ').replace('\n', ' ').replace('\r', ' ')) if len(
self.args['SQL']) > 0 else ([], [])
self.values['ENCODING'] = catalog_encoding
engine.close()
configuration.set_locale(None)
if self.args['FORMAT'] == 'csv':
# Returns results like text/csv
self.set_content_type('text/csv')
self.set_filename('query.csv')
return self.output_to_csv(self.values['DATA'])
else:
return self.get_template('query.tpl',
ARGS=self.args,
VALUES=self.values,
printable_text_for_encoding=self.printable_text_for_encoding)
def serve(self):
"""Handle the request and serve the response"""
RequestBase.serve(self)
# Request values
self.args = {}
self.args['CONFIRM'] = self.params.get_item('confirm')
self.args['DELETE'] = self.params.get_item('delete')
self.args['CATALOG'] = self.params.get_utf8_item('catalog')
self.args['DESCRIPTION'] = self.params.get_utf8_item('description')
self.args['ENGINE'] = self.params.get_item('engine')
self.args['CONNECTION'] = self.params.get_utf8_item('connection')
self.args['SERVER'] = self.params.get_utf8_item('server')
self.args['DATABASE'] = self.params.get_utf8_item('database')
self.args['USERNAME'] = self.params.get_utf8_item('username')
self.args['PASSWORD'] = self.params.get_utf8_item('password')
self.args['ENCODING'] = self.params.get_item('encoding')
# Avoid empty description
if not self.args['DESCRIPTION']:
self.args['DESCRIPTION'] = self.args['CATALOG']
# Response values
self.values = {}
self.values['ERRORS'] = []
# Build engines list
self.values['ENGINES'] = [('', '< select DB engine >')]
for engine_name in self.engines.values():
self.values['ENGINES'].append((engine_name.descriptor,
engine_name.description))
self.values['ENGINES'].sort()
self.values['DATA'] = None
existing_catalog = ''
existing_description = ''
existing_engine = ''
existing_connection = ''
existing_server = ''
existing_database = ''
existing_username = ''
existing_password = ''
existing_encoding = ''
engine = self.open_settings_db()
# Get existing catalog details
if self.args['CATALOG']:
existing_fields, existing_data = engine.get_data(
'SELECT name, description, engine, connstr, '
'server, database, username, password, encoding '
'FROM catalogs '
'WHERE name=?',
None,
(self.args['CATALOG'], ))
if existing_data:
if self.args['DELETE']:
# Delete existing catalog
logging.debug('Deleting catalog: %s' % self.args['CATALOG'])
engine.execute('DELETE FROM catalogs WHERE name=?', (
self.args['CATALOG'], ))
engine.save()
logging.info('Deleted catalog: %s' % self.args['CATALOG'])
# Reload empty page afer save
return 'REDIRECT:catalogs'
else:
existing_catalog, existing_description, existing_engine, \
existing_connection, existing_server, existing_database, \
existing_username, existing_password, existing_encoding = \
existing_data[0]
# Check the requested arguments for errors
if self.args['CONFIRM']:
# Check parameters for errors
if not self.args['CATALOG']:
self.values['ERRORS'].append('Missing catalog name')
if not self.args['ENGINE']:
self.values['ERRORS'].append('Missing engine name')
if not self.args['CONNECTION']:
self.values['ERRORS'].append('Missing connection string')
# Process data
if not self.values['ERRORS']:
if existing_catalog:
# Update existing catalog
logging.debug('Updating catalog: %s' % self.args['CATALOG'])
engine.execute('UPDATE catalogs '
'SET description=?, engine=?, connstr=?, '
'server=?, database=?, username=?, '
'password=?, encoding=? '
'WHERE name=?', (
self.args['DESCRIPTION'],
self.args['ENGINE'],
self.args['CONNECTION'],
self.args['SERVER'],
self.args['DATABASE'],
self.args['USERNAME'],
self.args['PASSWORD'],
self.args['ENCODING'],
self.args['CATALOG']))
logging.info('Updated catalog: %s' % self.args['CATALOG'])
else:
# Insert new catalog
logging.debug('Inserting catalog: %s' % self.args['CATALOG'])
engine.execute('INSERT INTO catalogs(name, description, '
'engine, connstr, server, database, '
'username, password, encoding) '
'VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)', (
self.args['CATALOG'],
self.args['DESCRIPTION'],
self.args['ENGINE'],
self.args['CONNECTION'],
self.args['SERVER'],
self.args['DATABASE'],
self.args['USERNAME'],
self.args['PASSWORD'],
self.args['ENCODING']))
logging.info('Inserted catalog: %s' % self.args['CATALOG'])
engine.save()
# Reload empty page afer save
return 'REDIRECT:catalogs'
else:
# Use existing details
self.args['DESCRIPTION'] = existing_description
self.args['ENGINE'] = existing_engine
self.args['CONNECTION'] = existing_connection
self.args['SERVER'] = existing_server
self.args['DATABASE'] = existing_database
self.args['USERNAME'] = existing_username
self.args['PASSWORD'] = existing_password
self.args['ENCODING'] = existing_encoding
# Get existing catalogs list
self.values['FIELDS'], self.values['DATA'] = engine.get_data(
'SELECT name, description, engine '
'FROM catalogs '
'ORDER BY name')
engine.close()
configuration.set_locale(None)
return self.get_template('catalogs.tpl',
ARGS=self.args,
VALUES=self.values,
printable_text_for_encoding=self.printable_text_for_encoding)
def serve(self):
"""Handle the request and serve the response"""
RequestBase.serve(self)
# Request values
self.args = {}
self.args['CONFIRM'] = self.params.get_item('confirm')
self.args['DELETE'] = self.params.get_item('delete')
self.args['CATALOG'] = self.params.get_utf8_item('catalog')
self.args['DESCRIPTION'] = self.params.get_utf8_item('description')
self.args['ENGINE'] = self.params.get_item('engine')
self.args['CONNECTION'] = self.params.get_utf8_item('connection')
self.args['SERVER'] = self.params.get_utf8_item('server')
self.args['DATABASE'] = self.params.get_utf8_item('database')
self.args['USERNAME'] = self.params.get_utf8_item('username')
self.args['PASSWORD'] = self.params.get_utf8_item('password')
self.args['ENCODING'] = self.params.get_item('encoding')
# Avoid empty description
if not self.args['DESCRIPTION']:
self.args['DESCRIPTION'] = self.args['CATALOG']
# Response values
self.values = {}
self.values['ERRORS'] = []
# Build engines list
self.values['ENGINES'] = [('', '< select DB engine >')]
for engine_name in self.engines.values():
self.values['ENGINES'].append(
(engine_name.descriptor, engine_name.description))
self.values['ENGINES'].sort()
self.values['DATA'] = None
existing_catalog = ''
existing_description = ''
existing_engine = ''
existing_connection = ''
existing_server = ''
existing_database = ''
existing_username = ''
existing_password = ''
existing_encoding = ''
engine = self.open_settings_db()
# Get existing catalog details
if self.args['CATALOG']:
existing_fields, existing_data = engine.get_data(
'SELECT name, description, engine, connstr, '
'server, database, username, password, encoding '
'FROM catalogs '
'WHERE name=?', None, (self.args['CATALOG'], ))
if existing_data:
if self.args['DELETE']:
# Delete existing catalog
logging.debug('Deleting catalog: %s' %
self.args['CATALOG'])
engine.execute('DELETE FROM catalogs WHERE name=?',
(self.args['CATALOG'], ))
engine.save()
logging.info('Deleted catalog: %s' % self.args['CATALOG'])
# Reload empty page afer save
return 'REDIRECT:catalogs'
else:
existing_catalog, existing_description, existing_engine, \
existing_connection, existing_server, existing_database, \
existing_username, existing_password, existing_encoding = \
existing_data[0]
# Check the requested arguments for errors
if self.args['CONFIRM']:
# Check parameters for errors
if not self.args['CATALOG']:
self.values['ERRORS'].append('Missing catalog name')
if not self.args['ENGINE']:
self.values['ERRORS'].append('Missing engine name')
if not self.args['CONNECTION']:
self.values['ERRORS'].append('Missing connection string')
# Process data
if not self.values['ERRORS']:
if existing_catalog:
# Update existing catalog
logging.debug('Updating catalog: %s' %
self.args['CATALOG'])
engine.execute(
'UPDATE catalogs '
'SET description=?, engine=?, connstr=?, '
'server=?, database=?, username=?, '
'password=?, encoding=? '
'WHERE name=?',
(self.args['DESCRIPTION'], self.args['ENGINE'],
self.args['CONNECTION'], self.args['SERVER'],
self.args['DATABASE'], self.args['USERNAME'],
self.args['PASSWORD'], self.args['ENCODING'],
self.args['CATALOG']))
logging.info('Updated catalog: %s' % self.args['CATALOG'])
else:
# Insert new catalog
logging.debug('Inserting catalog: %s' %
self.args['CATALOG'])
engine.execute(
'INSERT INTO catalogs(name, description, '
'engine, connstr, server, database, '
'username, password, encoding) '
'VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)',
(self.args['CATALOG'], self.args['DESCRIPTION'],
self.args['ENGINE'], self.args['CONNECTION'],
self.args['SERVER'], self.args['DATABASE'],
self.args['USERNAME'], self.args['PASSWORD'],
self.args['ENCODING']))
logging.info('Inserted catalog: %s' % self.args['CATALOG'])
engine.save()
# Reload empty page afer save
return 'REDIRECT:catalogs'
else:
# Use existing details
self.args['DESCRIPTION'] = existing_description
self.args['ENGINE'] = existing_engine
self.args['CONNECTION'] = existing_connection
self.args['SERVER'] = existing_server
self.args['DATABASE'] = existing_database
self.args['USERNAME'] = existing_username
self.args['PASSWORD'] = existing_password
self.args['ENCODING'] = existing_encoding
# Get existing catalogs list
self.values['FIELDS'], self.values['DATA'] = engine.get_data(
'SELECT name, description, engine '
'FROM catalogs '
'ORDER BY name')
engine.close()
configuration.set_locale(None)
return self.get_template(
'catalogs.tpl',
ARGS=self.args,
VALUES=self.values,
printable_text_for_encoding=self.printable_text_for_encoding)
def serve(self):
"""Handle the request and serve the response"""
RequestBase.serve(self)
# Request values
self.args = {}
self.args['FORMAT'] = self.params.get_item('format')
self.args['CONFIRM'] = self.params.get_item('confirm')
self.args['DELETE'] = self.params.get_item('delete')
self.args['UUID'] = self.params.get_item('uuid')
self.args['CATALOG'] = self.params.get_utf8_item('catalog')
self.args['NAME'] = self.params.get_utf8_item('name')
self.args['DESCRIPTION'] = self.params.get_utf8_item('description')
self.args['SQL'] = self.params.get_utf8_item('sql')
self.args['FOLDER'] = self.params.get_utf8_item('folder')
self.args['REPORT'] = self.params.get_utf8_item('report')
self.args['PARAMETERS'] = self.params.get_utf8_item('parameters')
# Avoid empty description
if not self.args['DESCRIPTION']:
self.args['DESCRIPTION'] = self.args['NAME']
# Response values
self.values = {}
self.values['ERRORS'] = []
self.values['DATA'] = None
self.values['REPORTS'] = (
('table', 'Vertical table'),
('card', 'Horizontal table'),
('once1',
'Vertical table which shows only once the first element'),
('csv', 'Comma separated values (CSV)'),
)
existing_id = 0
existing_catalog = ''
existing_query = ''
existing_description = ''
existing_sql = ''
existing_folder = ''
existing_report = ''
existing_parameters = ''
engine = self.open_settings_db()
# The query configuration is valid when a format is not requested
if not self.args['FORMAT']:
# Get existing catalog details
if self.args['UUID']:
existing_fields, existing_data = engine.get_data(
'SELECT uuid, catalog, name, description, sql, folder, '
'report, parameters '
'FROM queries '
'WHERE uuid=?', None, (self.args['UUID'], ))
if existing_data:
if self.args['DELETE']:
# Delete existing query
logging.debug('Deleting query: %s' % self.args['UUID'])
engine.execute('DELETE FROM queries WHERE uuid=?',
(self.args['UUID'], ))
engine.save()
logging.info('Deleted query: %s' % self.args['UUID'])
# Reload empty page afer save
return 'REDIRECT:queries'
else:
existing_id, existing_catalog, existing_query, \
existing_description, existing_sql, existing_folder, \
existing_report, existing_parameters = existing_data[0]
# Check the requested arguments for errors
if self.args['CONFIRM']:
# Check parameters for errors
if not self.args['NAME']:
self.values['ERRORS'].append('Missing query name')
if not self.args['SQL']:
self.values['ERRORS'].append('Missing SQL statement')
if not self.args['REPORT']:
self.values['ERRORS'].append('Missing report')
# Process data
if not self.values['ERRORS']:
if existing_id:
# Update existing query
logging.debug('Updating query: %s' % self.args['UUID'])
engine.execute(
'UPDATE queries '
'SET catalog=?, name=?, description=?, sql=?, '
'folder=?, report=?, parameters=? '
'WHERE uuid=?',
(self.args['CATALOG'], self.args['NAME'],
self.args['DESCRIPTION'], self.args['SQL'],
self.args['FOLDER'], self.args['REPORT'],
self.args['PARAMETERS'], self.args['UUID']))
logging.info('Updated query: %s' % self.args['UUID'])
else:
# Insert new query
logging.debug('Inserting query: %s' %
self.args['NAME'])
engine.execute(
'INSERT INTO queries(uuid, catalog, name, '
'description, sql, folder, report, '
'parameters) '
'VALUES(?, ?, ?, ?, ?, ?, ?, ?)',
(str(uuid.uuid4()), self.args['CATALOG'],
self.args['NAME'], self.args['DESCRIPTION'],
self.args['SQL'], self.args['FOLDER'],
self.args['REPORT'], self.args['PARAMETERS']))
logging.info('Inserted catalog: %s' %
self.args['NAME'])
engine.save()
# Reload empty page afer save
return 'REDIRECT:queries'
else:
# Use existing details
self.args['CATALOG'] = existing_catalog
self.args['NAME'] = existing_query
self.args['DESCRIPTION'] = existing_description
self.args['SQL'] = existing_sql
self.args['FOLDER'] = existing_folder
self.args['REPORT'] = existing_report
self.args['PARAMETERS'] = existing_parameters
# Get existing catalogs list
self.values['FIELDS'], self.values['CATALOGS'] = engine.get_data(
'SELECT name, description FROM catalogs '
'ORDER BY name')
# Get existing folders list
self.values['FIELDS'], self.values['FOLDERS'] = engine.get_data(
'SELECT name, description FROM folders '
'ORDER BY name')
# Get existing queries list
self.values['FIELDS'], self.values['DATA'] = engine.get_data(
'SELECT queries.uuid, queries.folder, queries.name, '
'queries.description, queries.catalog, queries.report, '
'folders.visible, folders.description '
'FROM queries '
'LEFT JOIN folders '
'ON folders.name=queries.folder '
'ORDER BY queries.folder, queries.catalog, queries.name')
engine.close()
if not self.args['FORMAT']:
# Serve the queries page
configuration.set_locale(None)
return self.get_template(
'queries.tpl',
ARGS=self.args,
VALUES=self.values,
printable_text_for_encoding=self.printable_text_for_encoding)
elif self.args['FORMAT'] == 'json':
# JSON format queries list
results = []
folder = {}
for row in self.values['DATA']:
# Add a new folder
if folder.get('title') != row[1].encode('utf-8'):
queries = []
folder = {}
folder['title'] = row[1].encode('utf-8')
folder['folder'] = True
folder['expanded'] = True
folder['tooltip'] = row[7].encode('utf-8')
folder['children'] = queries
results.append(folder)
# Add the query to the queries list object
queries.append({
'title':
'<a href="?uuid=%s">%s</a>' % (row[0], row[2]),
'tooltip':
row[3].encode('utf-8'),
'key':
row[0],
'catalog':
row[4],
'report':
row[5],
})
return results
else:
# Unexpected format
return 'ABORT:500:Unexpected format: %s' % self.args['FORMAT']
def serve(self):
"""Handle the request and serve the response"""
RequestBase.serve(self)
# Request values
self.args = {}
self.args['CONFIRM'] = self.params.get_item('confirm')
self.args['DELETE'] = self.params.get_item('delete')
self.args['PARAMETER'] = self.params.get_utf8_item('parameter')
self.args['DESCRIPTION'] = self.params.get_utf8_item('description')
self.args['CONTENT'] = self.params.get_utf8_item('content')
# Avoid empty description
if not self.args['DESCRIPTION']:
self.args['DESCRIPTION'] = self.args['PARAMETER']
# Response values
self.values = {}
self.values['ERRORS'] = []
self.values['DATA'] = None
existing_parameter = ''
existing_description = ''
existing_content = ''
engine = self.open_settings_db()
# Get existing catalog details
if self.args['PARAMETER']:
existing_fields, existing_data = engine.get_data(
'SELECT name, description, content '
'FROM parameters '
'WHERE name=?',
None,
(self.args['PARAMETER'], ))
if existing_data:
if self.args['DELETE']:
# Delete existing parameter
logging.debug('Deleting parameter: %s' % self.args['PARAMETER'])
engine.execute('DELETE FROM parameters WHERE name=?', (
self.args['PARAMETER'], ))
engine.save()
logging.info('Deleted parameter: %s' % self.args['PARAMETER'])
# Reload empty page afer save
return 'REDIRECT:parameters'
else:
existing_parameter, existing_description, existing_content = \
existing_data[0]
# Check the requested arguments for errors
if self.args['CONFIRM']:
# Check parameters for errors
if not self.args['PARAMETER']:
self.values['ERRORS'].append('Missing parameter name')
# Process data
if not self.values['ERRORS']:
if existing_parameter:
# Update existing parameter
logging.debug('Updating parameter: %s' % self.args['PARAMETER'])
engine.execute('UPDATE parameters '
'SET description=?, content=? '
'WHERE name=?', (
self.args['DESCRIPTION'],
self.args['CONTENT'],
self.args['PARAMETER']))
logging.info('Updated parameter: %s' % self.args['PARAMETER'])
else:
# Insert new parameter
logging.debug('Inserting parameter: %s' % self.args['PARAMETER'])
engine.execute('INSERT INTO parameters(name, description, '
'content) '
'VALUES(?, ?, ?)', (
self.args['PARAMETER'],
self.args['DESCRIPTION'],
self.args['CONTENT']))
logging.info('Inserted parameter: %s' % self.args['PARAMETER'])
engine.save()
# Reload empty page afer save
return 'REDIRECT:parameters'
else:
# Use existing details
self.args['DESCRIPTION'] = existing_description
self.args['CONTENT'] = existing_content
# Get existing parameters list
self.values['FIELDS'], self.values['DATA'] = engine.get_data(
'SELECT name, description '
'FROM parameters '
'ORDER BY name')
engine.close()
configuration.set_locale(None)
return self.get_template('parameters.tpl',
ARGS=self.args,
VALUES=self.values,
printable_text_for_encoding=self.printable_text_for_encoding)
def serve(self):
"""Handle the request and serve the response"""
RequestBase.serve(self)
# Request values
self.args = {}
self.args['UUID'] = self.params.get_item('uuid')
self.args['FORMAT'] = self.params.get_item('format')
# Response values
self.values = {}
self.values['ENGINE'] = None
self.values['CONNECTION'] = None
self.values['CATALOG'] = None
self.values['DESCRIPTION'] = None
self.values['SQL'] = None
self.values['REPORT'] = None
self.values['ERRORS'] = []
self.values['FIELDS'] = None
self.values['DATA'] = None
self.values['REQUIRES'] = []
self.values['REQUIRES'].append('jquery')
self.values['REQUIRES'].append('tablesorter')
# Parameters
self.parameters = OrderedDict()
self.extra_parameters = {}
engine = None
engine_settings = self.open_settings_db()
# Get query information
existing_fields, existing_data = engine_settings.get_data(
'SELECT catalog, description, sql, report, parameters '
'FROM queries '
'WHERE uuid=?', None, (self.args['UUID'], ))
if existing_data:
self.values['CATALOG'] = existing_data[0][0]
self.values['DESCRIPTION'] = existing_data[0][1]
self.values['SQL'] = existing_data[0][2].encode('utf-8').replace(
'\r\n', ' ').replace('\n', ' ').replace('\r', ' ')
self.values['REPORT'] = existing_data[0][3]
self.values['PARAMETERS'] = existing_data[0][4].encode('utf-8')
else:
self.values['ERRORS'].append('Query not found')
if not self.values['ERRORS']:
# Get the catalog information
existing_fields, existing_data = engine_settings.get_data(
'SELECT engine, connstr, server, database, username, '
'password, encoding '
'FROM catalogs '
'WHERE name=?', None, (self.values['CATALOG'], ))
if existing_data:
self.values['ENGINE'] = existing_data[0][0]
self.values['SERVER'] = existing_data[0][2]
self.values['DATABASE'] = existing_data[0][3]
self.values['USERNAME'] = existing_data[0][4]
self.values['PASSWORD'] = existing_data[0][5]
self.values['ENCODING'] = existing_data[0][6]
self.values['CONNECTION'] = self.prepare_connection_string(
connection=existing_data[0][1],
engine=self.values['ENGINE'],
server=self.values['SERVER'],
database=self.values['DATABASE'],
username=self.values['USERNAME'],
password=self.values['PASSWORD'])
else:
self.values['ERRORS'].append('Catalog not found')
if not self.values['ERRORS']:
# Open catalog database
engine = self.open_db_from_engine_type(
self.values['ENGINE'], self.values['CATALOG'],
self.values['CONNECTION'], self.values['USERNAME'],
self.values['PASSWORD'], self.values['DATABASE'],
self.values['SERVER'])
# Parse parameters
if self.values['PARAMETERS']:
list_parameters = self.values['PARAMETERS'].replace(
'\r\n', '\n').replace('\r', '\n').split('\n')
for parameter in list_parameters:
param_name, param_config = parameter.split('=', 1)
# Get the parameter from the parameters table if needed
if param_config.startswith('parameter:'):
existing_fields, existing_data = engine_settings.get_data(
statement=
'SELECT content FROM parameters WHERE name=?',
replaces=None,
parameters=(param_config[10:], ))
if existing_data:
param_config = existing_data[0][0]
list_parameters.insert(
list_parameters.index(parameter),
'%s=%s' % (param_name, param_config))
list_parameters.remove(parameter)
for parameter in list_parameters:
param_name, param_config = parameter.split('=', 1)
# Parameter configuration
if param_config.startswith('list:'):
# List of values
param_values = param_config[5:].split(',')
elif param_config.startswith('range:'):
# Range between two values
param_value1, param_value2 = param_config[6:].split(
'-', 1)
param_values = range(int(param_value1),
int(param_value2) + 1)
elif param_config.startswith('sql:'):
# Result of a SQL statement
existing_fields, existing_data = engine.get_data(
param_config[4:])
param_values = []
for param_value1 in existing_data:
param_values.append(param_value1[0])
elif param_config.startswith('text:'):
# Input text
param_values = ''
elif param_config.startswith('date:'):
# Date input text
param_values = datetime.date.today()
if 'jquery-ui' not in self.values['REQUIRES']:
self.values['REQUIRES'].append('jquery-ui')
elif param_config.startswith('values:'):
# List of key=description values
param_values = []
for param_value1 in param_config[7:].split(','):
param_values.append(param_value1.split('=', 1))
elif param_config.startswith('parameters:'):
param_values = []
for param_value1 in param_config[11:].split(','):
param_values.append(param_value1.split('=', 1)[0])
else:
# Not implemented parameter type
raise Exception('Not implemented parameter type: %s' %
param_config)
self.parameters[param_name] = param_values
# Check all the parameters for parameters type values
for parameter in list_parameters:
param_name, param_config = parameter.split('=', 1)
# param_name is the parameters: name
if param_config.startswith('parameters:'):
# A parameter of type parameters has the following syntax:
# NAME=parameters:PARAMVALUE1=FIELD1=VALUE1,PARAMVALUE2=FIELD1=VALUE1
self.args[param_name] = self.params.get_item(
param_name, None)
# self.args[param_name] is the selected PARAMVALUE
param_values = param_config[11:].split(',')
for param_values in param_values:
param_name2, param_values = param_values.split(
'=', 1)
# param_name2 is each PARAMVALUE
# param_values is the FIELD=VALUE pairs list
param_values = param_values.split(';')
for parameter in param_values:
# parameter is each FIELD=value pair
param_value1, param_value2 = parameter.split(
'=', 1)
# Check if the parameters parameter was set
if self.args[param_name] == param_name2:
self.args[param_value1] = param_value2
else:
self.args[param_value1] = None
self.extra_parameters[
param_value1] = self.args[param_value1]
# Exit after the current PARAMVALUE was found
if self.args[param_name] == param_name2:
break
break
# Check all the parameters if they were configured
for parameter in self.parameters.keys():
self.args[parameter] = self.params.get_item(
parameter, None)
if self.args[parameter] is not None:
self.args[parameter] = self.args[parameter].replace(
'\'', '\'\'')
else:
self.values['ERRORS'].append(
'Parameter %s was not provided' % parameter)
# Fill parameters with extra parameters
for parameter in self.extra_parameters.keys():
if self.extra_parameters.get(parameter, None) is not None:
self.args[parameter] = self.extra_parameters[
parameter].replace('\'', '\'\'')
if engine:
if not self.values['ERRORS']:
# Get the data from the query
self.values['FIELDS'], self.values['DATA'] = engine.get_data(
self.values['SQL'], self.args, None)
engine.close()
engine_settings.close()
engine_settings = None
configuration.set_locale(None)
if not self.values['ERRORS'] and self.args['FORMAT'].lower() == 'csv':
# Returns results like text/csv
self.set_content_type('text/csv')
self.set_filename('%s.csv' % self.values['DESCRIPTION'])
return self.output_to_csv(self.values['DATA'])
else:
return self.get_template(
'reports/%s.tpl' % self.values['REPORT'],
ARGS=self.args,
VALUES=self.values,
PARAMETERS=self.parameters,
printable_text_for_encoding=self.printable_text_for_encoding,
datetime=datetime)
def serve(self):
"""Handle the request and serve the response"""
RequestBase.serve(self)
# Request values
self.args = {}
self.args['FORMAT'] = self.params.get_item('format')
self.args['CONFIRM'] = self.params.get_item('confirm')
self.args['DELETE'] = self.params.get_item('delete')
self.args['UUID'] = self.params.get_item('uuid')
self.args['CATALOG'] = self.params.get_utf8_item('catalog')
self.args['NAME'] = self.params.get_utf8_item('name')
self.args['DESCRIPTION'] = self.params.get_utf8_item('description')
self.args['SQL'] = self.params.get_utf8_item('sql')
self.args['FOLDER'] = self.params.get_utf8_item('folder')
self.args['REPORT'] = self.params.get_utf8_item('report')
self.args['PARAMETERS'] = self.params.get_utf8_item('parameters')
# Avoid empty description
if not self.args['DESCRIPTION']:
self.args['DESCRIPTION'] = self.args['NAME']
# Response values
self.values = {}
self.values['ERRORS'] = []
self.values['DATA'] = None
self.values['REPORTS'] = (
('table', 'Vertical table'),
('card', 'Horizontal table'),
('once1', 'Vertical table which shows only once the first element'),
('csv', 'Comma separated values (CSV)'),
)
existing_id = 0
existing_catalog = ''
existing_query = ''
existing_description = ''
existing_sql = ''
existing_folder = ''
existing_report = ''
existing_parameters = ''
engine = self.open_settings_db()
# The query configuration is valid when a format is not requested
if not self.args['FORMAT']:
# Get existing catalog details
if self.args['UUID']:
existing_fields, existing_data = engine.get_data(
'SELECT uuid, catalog, name, description, sql, folder, '
'report, parameters '
'FROM queries '
'WHERE uuid=?',
None,
(self.args['UUID'], ))
if existing_data:
if self.args['DELETE']:
# Delete existing query
logging.debug('Deleting query: %s' % self.args['UUID'])
engine.execute('DELETE FROM queries WHERE uuid=?', (
self.args['UUID'], ))
engine.save()
logging.info('Deleted query: %s' % self.args['UUID'])
# Reload empty page afer save
return 'REDIRECT:queries'
else:
existing_id, existing_catalog, existing_query, \
existing_description, existing_sql, existing_folder, \
existing_report, existing_parameters = existing_data[0]
# Check the requested arguments for errors
if self.args['CONFIRM']:
# Check parameters for errors
if not self.args['NAME']:
self.values['ERRORS'].append('Missing query name')
if not self.args['SQL']:
self.values['ERRORS'].append('Missing SQL statement')
if not self.args['REPORT']:
self.values['ERRORS'].append('Missing report')
# Process data
if not self.values['ERRORS']:
if existing_id:
# Update existing query
logging.debug('Updating query: %s' % self.args['UUID'])
engine.execute('UPDATE queries '
'SET catalog=?, name=?, description=?, sql=?, '
'folder=?, report=?, parameters=? '
'WHERE uuid=?', (
self.args['CATALOG'],
self.args['NAME'],
self.args['DESCRIPTION'],
self.args['SQL'],
self.args['FOLDER'],
self.args['REPORT'],
self.args['PARAMETERS'],
self.args['UUID']))
logging.info('Updated query: %s' % self.args['UUID'])
else:
# Insert new query
logging.debug('Inserting query: %s' % self.args['NAME'])
engine.execute('INSERT INTO queries(uuid, catalog, name, '
'description, sql, folder, report, '
'parameters) '
'VALUES(?, ?, ?, ?, ?, ?, ?, ?)', (
str(uuid.uuid4()),
self.args['CATALOG'],
self.args['NAME'],
self.args['DESCRIPTION'],
self.args['SQL'],
self.args['FOLDER'],
self.args['REPORT'],
self.args['PARAMETERS']))
logging.info('Inserted catalog: %s' % self.args['NAME'])
engine.save()
# Reload empty page afer save
return 'REDIRECT:queries'
else:
# Use existing details
self.args['CATALOG'] = existing_catalog
self.args['NAME'] = existing_query
self.args['DESCRIPTION'] = existing_description
self.args['SQL'] = existing_sql
self.args['FOLDER'] = existing_folder
self.args['REPORT'] = existing_report
self.args['PARAMETERS'] = existing_parameters
# Get existing catalogs list
self.values['FIELDS'], self.values['CATALOGS'] = engine.get_data(
'SELECT name, description FROM catalogs '
'ORDER BY name')
# Get existing folders list
self.values['FIELDS'], self.values['FOLDERS'] = engine.get_data(
'SELECT name, description FROM folders '
'ORDER BY name')
# Get existing queries list
self.values['FIELDS'], self.values['DATA'] = engine.get_data(
'SELECT queries.uuid, queries.folder, queries.name, '
'queries.description, queries.catalog, queries.report, '
'folders.visible, folders.description '
'FROM queries '
'LEFT JOIN folders '
'ON folders.name=queries.folder '
'ORDER BY queries.folder, queries.catalog, queries.name')
engine.close()
if not self.args['FORMAT']:
# Serve the queries page
configuration.set_locale(None)
return self.get_template('queries.tpl',
ARGS=self.args,
VALUES=self.values,
printable_text_for_encoding=self.printable_text_for_encoding)
elif self.args['FORMAT'] == 'json':
# JSON format queries list
results = []
folder = {}
for row in self.values['DATA']:
# Add a new folder
if folder.get('title') != row[1].encode('utf-8'):
queries = []
folder = {}
folder['title'] = row[1].encode('utf-8')
folder['folder'] = True
folder['expanded'] = True
folder['tooltip'] = row[7].encode('utf-8')
folder['children'] = queries
results.append(folder)
# Add the query to the queries list object
queries.append({
'title': '<a href="?uuid=%s">%s</a>' % (row[0], row[2]),
'tooltip': row[3].encode('utf-8'),
'key': row[0],
'catalog': row[4],
'report': row[5],
})
return results
else:
# Unexpected format
return 'ABORT:500:Unexpected format: %s' % self.args['FORMAT']
