Repository
Show triggers, syntax highlighted functions
Parent commits : cffb01e9f1e8c46e3e2336662e387bee9f712899,Children commits : d34041e7737f5371d1169a6057dafbf8dbf87dce,
By Laurent Defert on 2015-01-25 16:50:35
Show triggers, syntax highlighted functions
-
# TODO: there may be a naming conflict with table names here:
-
# TODO: Factorize the get_functions/get_triggers below
Difference with parent commit cffb01e9f1e8c46e3e2336662e387bee9f712899
Browse contentFiles modified:
README
---
+++
@@ -1,7 +1,7 @@
-db_annotate is a database analysis tools. See a sample output `here </db_annotate/index.html>`_
+db_annotate is a database analysis tools. See a `sample output </db_annotate/index.html>`_
Debian packages requirements:
-aptitude install graphviz python3-{psycopg2,matplotlib,sqlalchemy}
+aptitude install graphviz python3-{psycopg2,pygments,sqlalchemy}
Download Pygal (https://github.com/Kozea/pygal.git)
dbannotate/db.py
---
+++
@@ -230,3 +230,43 @@
table not in [i[1] for i in inherited]:
errors += ['has no foreign or primary key']
return errors
+
+ # TODO: Factorize the get_functions/get_triggers below
+ def get_functions(self):
+ # http://stackoverflow.com/questions/16632117/get-all-procedural-user-defined-functions
+ try:
+ r = self.engine.execute('''SELECT
+ pp.proname,
+ pl.lanname,
+ pg_get_functiondef(pp.oid)
+ FROM pg_proc pp
+ INNER JOIN pg_namespace pn ON (pp.pronamespace = pn.oid)
+ INNER JOIN pg_language pl ON (pp.prolang = pl.oid)
+ WHERE pl.lanname NOT IN ('c','internal')
+ AND pn.nspname NOT LIKE 'pg_%%'
+ AND pn.nspname <> 'information_schema';''')
+ res = r.fetchall()
+ return res
+ except Exception:
+ from traceback import print_exc
+ print_exc()
+ # if not supported by db
+ return []
+
+ def get_triggers(self, table):
+ # http://serverfault.com/questions/331024/how-can-i-show-the-content-of-a-trigger-with-psql
+ try:
+ r = self.engine.execute('''SELECT trigger_name,
+ event_manipulation,
+ action_statement,
+ action_timing
+ FROM information_schema.triggers
+ WHERE event_object_table = '%s'
+ ORDER BY event_object_table,event_manipulation''' % table)
+ res = r.fetchall()
+ return res
+ except Exception:
+ from traceback import print_exc
+ print_exc()
+ # if not supported by db
+ return []
dbannotate/html.py
---
+++
@@ -16,6 +16,11 @@
from datetime import datetime
import os
+import re
+
+from pygments import highlight
+from pygments.formatters import HtmlFormatter
+from pygments.lexers.sql import MySqlLexer, PlPgsqlLexer, PostgresConsoleLexer, PostgresLexer, SqlLexer, SqliteConsoleLexer
from .db_size import humanize
from .output_file import OutputFile, OUTPUT_DIR
@@ -26,6 +31,7 @@
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
+%s
<title>%s</title>
</head>
<body>
@@ -37,8 +43,15 @@
class HTMLFile(OutputFile):
+ CSS = []
+ BACK_BUTTON = True
+
def render(self, *args, **kw):
- self.write(HTML_BODY % self.filename)
+ css = ['<link rel="stylesheet" type="text/css" href="%s">' % css for css in self.CSS]
+ css = '\n'.join(css)
+ if self.BACK_BUTTON:
+ self.write('<a href="index.html">Back...</a><br/>')
+ self.write(HTML_BODY % (css, self.filename))
self._render(*args, **kw)
self.write(HTML_FOOTER)
@@ -66,21 +79,35 @@
html += '</map>'
return html
- def _render(self, table, errors, sizes, keys, indexes, columns):
- self.write('''<a href="index.html">Back...</a><br/>
- <h1>Table %s</h1>
+ def _render(self, table, errors, sizes, keys, indexes, columns, triggers):
+ self.write('''<h1>Table %s</h1>
<ul>
<li>Size: %s</li>
<li>Size without indexes: %s</li>
<li>Rows count: %i</li>
<li>Keys: %s</li>
- <li>Columns count: %i</li>
- </ul>''' % (table, sizes[1], sizes[0], sizes[4], ', '.join(keys),
- len(columns)))
+ <li>Columns count: %i</li>''' % (table, sizes[1], sizes[0],
+ sizes[4], ', '.join(keys), len(columns)))
+
+ _triggers = []
+ for trigger in triggers:
+ tg_name, tg_event, tg_action, tg_when = trigger
+ tg_code = tg_func = ''
+ m = re.match('^EXECUTE PROCEDURE ([^ ]*)\(\)$', tg_action)
+ if m is not None:
+ tg_code = 'EXECUTE PROCEDURE'
+ tg_func = m.group(1)
+ _triggers.append('<li>%s: %s %s %s <a href="fn_%s.html">%s()</a></li>' %
+ (tg_name, tg_when.title(), tg_event.title(),
+ tg_code, tg_func, tg_func))
+ self.write('<li>Triggers:<ul>%s</ul></li>' % '\n'.join(_triggers))
+ self.write('</ul>')
+
self.write(TableFile.get_table_html(self.filename.replace('.html', '.png')))
class IndexFile(HTMLFile):
- def _render(self, objects, db_size, db):
+ BACK_BUTTON = False
+ def _render(self, objects, db_size, db, functions):
for obj in objects:
for _obj in obj:
_obj['url'] = os.path.basename(_obj['filename'])
@@ -128,3 +155,36 @@
self.write('</tr>')
self.write('</table>')
self.write(TableFile.get_table_html(os.path.join(OUTPUT_DIR, 'map.png')))
+ if len(functions):
+ self.write('<h2>Functions</h2>')
+ self.write('<table><tr><th>Name</th><th>Lines count</th></tr>')
+ for function in functions:
+ self.write('<tr><td><a href="fn_%s.html">%s</a></td>' % (function[0], function[0]))
+ self.write('<td>%i</td></tr>' % function[1])
+ self.write('</table>')
+
+
+FORMATTER = HtmlFormatter()
+class HilightCSSFile(OutputFile):
+ def __init__(self):
+ super(HilightCSSFile, self).__init__('highlight.css')
+ def render(self):
+ self.write(FORMATTER.get_style_defs('.highlight'))
+
+class FunctionFile(HTMLFile):
+ CSS = ['highlight.css']
+ LEXERS = {
+ 'plpgsql': PlPgsqlLexer(),
+ }
+
+ def _get_lexer(self, language):
+ if language not in self.LEXERS:
+ raise NotImplemented('Unknown function language "%s"' % language)
+ return self.LEXERS[language]
+
+ def _render(self, function, language, code):
+ lexer = self._get_lexer(language)
+ highlighted = highlight(code, lexer, FORMATTER)
+ self.write('<h1>Function %s</h1>' % function)
+ self.write('%s language' % language)
+ self.write(highlighted)
dbannotate/main.py
---
+++
@@ -22,7 +22,7 @@
from .db_size import DBSize
from .dot import DotFile
from .gv import GV
-from .html import IndexFile, TableFile
+from .html import IndexFile, TableFile, FunctionFile, HilightCSSFile
from .output_file import OutputFile
@@ -70,7 +70,8 @@
errors = db.get_table_errors(table)
gv_map.add_table(table, errors, sizes, keys, indexes, columns)
gv_tables[table].add_table(table, errors, sizes, keys, indexes, columns, True)
- tables[table] = (table, errors, sizes, keys, indexes, columns)
+ triggers = db.get_triggers(table)
+ tables[table] = (table, errors, sizes, keys, indexes, columns, triggers)
print('Adding constraints')
# Constraints
@@ -135,6 +136,24 @@
table_html = TableFile(gv.basename.replace('.gv', '.html'))
table_html.render(*tables[table])
+ # Functions
+ functions = []
+ _functions = db.get_functions()
+ if len(_functions):
+ print('Buildings functions')
+ css = HilightCSSFile()
+ css.render()
+ for function in _functions:
+ # TODO: there may be a naming conflict with table names here:
+ fn_name, fn_lang, fn_code = function
+ print(fn_name)
+ html = FunctionFile('fn_%s.html' % fn_name)
+ functions.append((fn_name, len(fn_code.splitlines())))
+ html.render(*function)
+
+ # Sort functions by higher lines count
+ functions = sorted(functions, key=lambda x:x[1])
+
# Generate HTML files
html_file = IndexFile('index.html')
- html_file.render(imgs, db_size, db)
+ html_file.render(imgs, db_size, db, functions)