routes.py #3

"""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
from config import Config
from forms import UploadForm, AnalyzeLog
import logging
import pandas as pd
import sqlite3
import traceback
from datetime import datetime, timedelta
import glob

import json
from altair import Chart, X, Y, Axis, Data, DataFormat

from app import app
from log2sql import Log2sql
import queries


# Chart dimensions
WIDTH = 600
HEIGHT = 300

UPLOAD_FOLDER = '/logs'
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():
    def __init__(self, dbname, logfile, sql=False, verbosity=logging.INFO, outlog=None):
        self.dbname = dbname
        self.logfile = logfile
        self.sql = 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=[], LogFile='', 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')

    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'])),
            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, LogFile='t1.log', form=form)