routes.py #8

"""PSLA - Perforce Server Log Analyzer
"""

import os
from flask import Flask, request, redirect, url_for, render_template, flash
from flask_wtf.csrf import CSRFProtect
from werkzeug import secure_filename

import logging
import pandas as pd
import sqlite3
import traceback
from datetime import datetime, timedelta
import glob

from altair import Chart, X, Y, Axis, Label

# Modules from our psla app
from config import Config
from forms import UploadForm, AnalyzeLog, QueryLog
from app import app
from log2sql import Log2sql
import queries
import os

# Default chart dimensions
WIDTH = 600
HEIGHT = 300

UPLOAD_FOLDER = os.getenv('PSLA_LOGS', '/logs')     # Default is inside docker container
ALLOWED_EXTENSIONS = set(['txt'])

app.config.from_object(Config)
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
csrf = CSRFProtect(app)


def allowed_file(filename):
    return True
    return '.' in filename and \
           filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS

class MyOptions():
    "Options class with defaults for Log2sql"
    def __init__(self, dbname, logfile, sql=False, verbosity=logging.INFO, outlog=None):
        self.dbname = dbname
        self.logfile = logfile
        self.sql = sql
        self.no_sql = not sql
        self.verbosity = verbosity
        self.interval = 10
        self.outlog = outlog
        self.output = None

@app.route('/')
def index():
    """Front page for application"""
    return render_template('index.html')

@app.route('/uploadLog', methods=['GET', 'POST'])
def uploadLog():
    "Upload log file"
    form = UploadForm()
    app.logger.debug("uploadLog: %s" % form.errors)
    app.logger.debug('------ {0}'.format(request.form))
    if form.is_submitted():
        app.logger.debug(form.errors)

    if form.validate():
        app.logger.debug(form.errors)
    app.logger.debug(form.errors)

    if form.validate_on_submit():
        db_folder = app.config['UPLOAD_FOLDER']
        filename = secure_filename(form.uploadFile.data.filename)
        file_path = os.path.join(db_folder, filename)
        form.uploadFile.data.save(file_path)

        os.chdir(db_folder)
        optionsSQL = MyOptions("db", [file_path], sql=True, outlog='log.out')
        dbname = "%s.db" % optionsSQL.dbname
        if os.path.exists(dbname):
            os.remove(dbname)
        log2sql = Log2sql(optionsSQL)
        log2sql.processLog()
        msgs = []

        return redirect(url_for('analyzeLog'))
    return render_template('uploadLog.html', title='Upload Log', form=form)


@app.route('/analyzeLog', methods=['GET', 'POST'])
def analyzeLog():
    "Access previously uploaded log"
    db_folder = app.config['UPLOAD_FOLDER']
    logFiles = glob.glob('%s/*.db' % db_folder)

    form = AnalyzeLog()
    form.logFile.choices = [(f, f) for f in logFiles]

    if not form.validate_on_submit():
        return render_template('analyzeLog.html', tables=[], charts=[], dbName=None, form=form)

    dbname = os.path.join(form.logFile.data)
    if not os.path.exists(dbname):
        flash('Database does not exist', 'error')
        return render_template('error.html', title='Database error')

    app.dbname = dbname

    try:
        conn = sqlite3.connect(dbname)
    except Exception as e:
        app.logger.error(traceback.format_exc())
        flash('Error: %s' % (str(e)), 'error')
        return render_template('error.html', title='Error in database reporting')

    tables = []
    for q in queries.queries:
        if not 'sql' in q or not q['sql']:
            continue
        app.logger.debug("running table query: %s - %s" % (q['title'], q['sql']))
        start = datetime.now()
        try:
            df = pd.read_sql_query(q['sql'], conn)
            table = df.to_html()
        except Exception as e:
            table = "<h3>ERROR: %s</h3>" % str(e)
        end = datetime.now()
        delta = end - start
        tables.append({'data': table,
                       'title': q['title'],
                       'explanation': q['explanation'],
                       'sql': q['sql'],
                       'time_taken': str(delta)})
    charts = []
    i = 0
    for q in queries.graphs:
        i += 1
        if not 'sql' in q or not q['sql']:
            continue
        app.logger.debug("running chart query: %s - %s" % (q['title'], q['sql']))
        start = datetime.now()
        try:
            df = pd.read_sql_query(q['sql'], conn)
        except Exception as e:
            app.logger.error(str(e))
            continue
        end = datetime.now()
        delta = end - start
        chart = Chart(data=df, height=HEIGHT, width=WIDTH).mark_line().encode(
            X(q['x']['field'], axis=Axis(title=q['x']['title'], labelOverlap='greedy')),
            Y(q['y']['field'], axis=Axis(title=q['y']['title']))
        )
        charts.append({'id': "chart-%d" % i,
                       'data': chart.to_json(),
                       'title': q['title'],
                       'explanation': q['explanation'],
                       'sql': q['sql'],
                       'time_taken': str(delta)})
    return render_template('analyzeLog.html', tables=tables, charts=charts, dbName=dbname, form=form)


@app.route('/queryLog', methods=['GET', 'POST'])
def queryLog():
    "Run queries on selected log"

    form = QueryLog()

    if not form.validate_on_submit():
        return render_template('queryLog.html', data={}, dbName=None, form=form)

    dbname = app.dbname
    if not os.path.exists(dbname):
        flash('Database does not exist', 'error')
        return render_template('error.html', title='Database error')

    try:
        conn = sqlite3.connect(dbname)
    except Exception as e:
        app.logger.error(traceback.format_exc())
        flash('Error: %s' % (str(e)), 'error')
        return render_template('error.html', title='Error in database reporting')

    sqlQuery = form.sqlQuery.data
    app.logger.debug("running interactive query: %s" % (sqlQuery))
    start = datetime.now()
    try:
        df = pd.read_sql_query(sqlQuery, conn)
        table = df.to_html()
    except Exception as e:
        table = "<h3>ERROR: %s</h3>" % str(e)
    end = datetime.now()
    delta = end - start
    data = ({'data': table,
             'time_taken': str(delta)})
    return render_template('queryLog.html', data=data, dbName=dbname, form=form)