queries.py #4

""" These are the pre-configured SQL queries and graphs which are run
    by the code in routes.py
"""

graphs = [{'title': 'Cmds per minute',
           'graph_type': 'line',
           'explanation': 'Maximum number of concurrent commands each minute',
           'x': {'title': 'x axis',
                 'field': 'Minute'},
           'y': {'title': 'y axis',
                 'field': 'MaxCommands'},
           'sql': """
                SELECT SUBSTR(startTime, 0, 17) as 'Minute', MAX(running) as 'MaxCommands'
                FROM process
                GROUP BY Minute;
            """},
          {'title': 'Cmd Totals',
           'explanation': 'How many commands of each type',
           'x': {'title': 'x axis',
                 'field': 'Cmd'},
           'y': {'title': 'y axis',
                 'field': 'NumCmds'},
           'sql': """
                select cmd as 'Cmd', count(cmd) as 'NumCmds'
                from process
                group by cmd
                order by NumCmds desc
                limit 15;
            """},
            {'title': 'Cmds per user',
           'explanation': 'How many commands of each type per user',
           'x': {'title': 'x axis',
                 'field': 'User'},
           'y': {'title': 'y axis',
                 'field': 'NumCmds'},
           'sql': """
                select user as 'User', count(user) as 'NumCmds'
                from process
                group by user
                order by NumCmds desc
                limit 15;
            """}]

queries = [{'title': 'Cmd Totals',
           'graph_type': 'line',
            'explanation': 'How many commands of each type',

            'sql': """
                select cmd, count(cmd) as CountCmds from process
                group by cmd
                order by CountCmds desc limit 20;
            """},
           {'title': 'Average wait time',
            'explanation': 'Average wait time',
            'sql': """
                SELECT AVG(totalreadWait+totalwriteWait) as wait
                FROM tableUse;
            """},

           {'title': 'Worst lock offenders',
            'explanation': 'Users whose commands hold locks',
            'sql': """
            SELECT user, SUM(maxreadHeld+maxwriteHeld) as held
              FROM tableUse JOIN process USING (processKey)
              GROUP BY user ORDER BY held DESC LIMIT 25;
            """},

           {'title': 'Blocking Commands',
            'explanation': 'Commands that blocked others',
            'sql': """
            	SELECT startTime, endTime, running, cmd, pid,
                    tablename, maxReadHeld,
                    maxWriteHeld, totalReadWait, totalWriteWait
                FROM tableUse JOIN process USING (processKey)
                WHERE processkey = processkey AND (( totalReadHeld > 10000 or
                    totalWriteHeld > 10000 )) ORDER BY startTime, endTime
                    limit 20;
            """},
           {'title': 'Block commands - victims of the above',
            'explanation': '',
            'sql': """
                SELECT startTime, endTime, computedLapse, running, cmd, pid,
                    tablename, maxReadHeld,
                    maxWriteHeld,totalReadWait, totalWriteWait
                FROM tableUse JOIN process USING (processKey)
                WHERE processkey = processkey AND (( totalReadWait > 10000 or
                totalWriteWait > 10000 ))
                ORDER BY startTime, endTime
                limit 20;
            """}
           # {'title': '',
           #  'explanation': '',
           #  'sql': """
           #  """},
           ]