""" 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; """}, {'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; """}] queries = [{'title': 'Cmd Totals', 'graph_type': 'line', 'explanation': 'How many commands of each type', 'sql': """ select cmd, count(cmd) from process group by cmd; """}, {'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; """}, {'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; """} # {'title': '', # 'explanation': '', # 'sql': """ # """}, ]