Beispiel #1
0
 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)
Beispiel #2
0
 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)
Beispiel #3
0
 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)
Beispiel #4
0
    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)
Beispiel #5
0
    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']
Beispiel #6
0
 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)
Beispiel #7
0
 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)
Beispiel #8
0
 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']