Skip to content

Instantly share code, notes, and snippets.

@dgerber

dgerber/README.txt

Created June 4, 2012 12:53
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dgerber/2868165 to your computer and use it in GitHub Desktop.
Save dgerber/2868165 to your computer and use it in GitHub Desktop.
Download ZIP
Generic query editor
Raw
README.txt
Préalables::
pip install --requirement=requirements.txt
Démarrage en local::
python server.py
Raw
client.js
function init(config){
d3.select('#db_selector')
.on('change', load_database)
.append('optgroup')
.attr('label', 'Exemples')
.selectAll('option')
.data(config.databases)
.enter()
.append('option')
.attr('value', function(d){return d.url;})
.text(function(d){return d.name || d.url;});
d3.select('#query_selector')
.on('change', load_query)
.append('optgroup')
.attr('label', 'Exemples');
}
function get_current_db(){
var s = d3.select('#db_selector');
return s.selectAll('option')[0][s[0][0].selectedIndex].__data__;
}
function load_database(){
var db = get_current_db();
if (db && db.url) {
d3.json('sql/describe?' + $.param({db: db.url}), function(schema){
update_schema_view(schema);
d3.select('#queries').style('visibility', 'visible');
update_query_menu(db.queries);
});
} else {
update_schema_view(null);
update_query_menu(null);
}
}
function get_current_query(){
var sel = d3.select('#query_selector')[0][0];
return sel.options[sel.selectedIndex].__data__;
}
function load_query(){
update_query_view(get_current_query());
}
function update_query_menu(queries){
var opts = d3.select('#query_selector > optgroup').selectAll('option')
.data(queries || []);
opts.exit().remove();
opts.enter()
.append('option')
.attr('title', function(d){return d.comment || d.label || d.type;})
.text(function(d){return d.label || d.comment;});
load_query();
}
function update_schema_view(schema){
var vis = d3.select('#schema_viewer > svg');
vis.selectAll('*')/*.transition().attr('opacity', 0)*/.remove();
if (!schema) {
return vis.attr('title', '(pas de base de données active)');
}
// circular layout - does not scale well for larger schemas...
var L = 980
, R = 0.3*L // radius
, hier_nodes = d3.layout.tree() // schema-tables-columns hierarchy
.size([2*Math.PI, R])
.children(function(d){
switch (d.type){
case 'MetaData': return d.tables;
case 'Table': return d.columns;
default: return null;}
}) // .separation(function(a, b){ return (a.parent == b.parent ? 1 : 2) / a.depth; })
.sort(null)
(schema)
, hier_map = d3.nest()
.key(function(n){return n.data.type;})
.key(function(n){return /*fullname*/(n.data.type == 'Column')
? n.data.table + '.' + n.data.name
: n.data.name;})
.rollup(function(a){return a[0];})
.map(hier_nodes)
, nb_cols = d3.sum(schema.tables, function(t){return t.columns.length;})
, table_count_scale = d3.scale.linear()
.domain(d3.extent(schema.tables, function(t){return t.count || 0;}))
, fk_links = [];
schema.tables
.reduce(function(links, table){
table.constraints.forEach(function(constraint){
if (constraint.type == 'ForeignKeyConstraint') {
d3.entries(constraint.columns).forEach(function(ref){
links.push({
'source': hier_map['Column'][table.name + '.' + ref.key],
'target': hier_map['Column'][ref.value/*full-name*/]
});
});
}
});
return links;
}, fk_links);
vis.attr('viewBox', [-L/2, -L/2, L, L].join(' '))
.attr('title', 'Schéma de la base de données '+schema.name);
var foreign_keys = vis
.selectAll('path.sql_foreign_key')
.data(d3.layout.bundle()(fk_links))
.enter()
.append('svg:path')
.attr('class', 'sql_foreign_key')
.attr('title', function(n){return 'Clef étrangère '+n[1].data.name+'.'+n[0].data.name + ' --> '
+ n[3].data.name+'.'+n[4].data.name;})
.attr('stroke-width', 1*R/nb_cols)
.attr('d',
d3.svg.line.radial()
.interpolate("bundle")
.tension(.9)
.angle(function(d) { return d.x; })
.radius(function(d) { return d.y; }))
;
var tables = vis
.selectAll("g.sql_table")
.data(hier_nodes.filter(function(n){ return n.depth == 1;}))
.enter()
.append("svg:g")
.attr("class", "sql_table")
.attr("title", function(n){ return 'Table "'+n.data.name+'" (contient '+n.data.count+' enregistrements)'; });
tables
.append("svg:path")
.attr("d",
d3.svg.arc()
.innerRadius(1.1 * R)
.outerRadius(function(n){return R *(1.15 + 0.1 * table_count_scale(n.data.count));})
.startAngle(function(n){ return d3.min(n.children, function(n){return n.x;}) - Math.PI/nb_cols; })
.endAngle(function(n){ return d3.max(n.children, function(n){return n.x;}) + Math.PI/nb_cols; }));
tables
.append("svg:text")
.attr("transform", function(n){
return "rotate("+(n.x *180/Math.PI - 90)
+")translate("+1.35*R
+")rotate("+((Math.PI/2<n.x && n.x<Math.PI*3/2) ? -90 : 90)+")";
})
.attr("text-anchor", "middle")
.attr("dy", "0.35em")
.attr('font-size', '1.2em')
.text(function(n){ return n.data.name + ' ('+n.data.count+')'; });
var columns = vis
.selectAll("g.sql_column")
.data(hier_nodes.filter(function(n){ return n.depth == 2; }))
.enter()
.append("svg:g").attr("class", "sql_column")
.attr("transform", function(n){ return "rotate("+(n.x*180/Math.PI - 90)+")translate("+n.y+")"; })
.attr("title", function(n){ return 'Colonne '+n.data.table+'.'+n.data.name+' (de type '+n.data.dtype+')'; });
columns
.append("svg:circle")
.attr("cx", 0).attr("cy", 0)
.attr("r", 1*R/nb_cols)
.attr('fill', function(n){ return n.data.primary_key ? 'orange' : 'steelblue'; });
columns
.append("svg:text")
.attr("dx", 0)
.attr("dy", ".35em")
.attr("text-anchor", function(n){ return n.x < Math.PI ? "end" : "start"; })
.attr("transform", function(n){
return "translate("+-1.5*R/nb_cols+')' + ( (n.x < Math.PI) ? "" : "rotate(180)" ); })
.text(function(n){ return n.data.name; });
}
// TODO:
function update_query_view(query) {
var vis = d3.select('#query_editor > svg');
vis.selectAll('*').remove();
vis.attr('title', 'Éditeur de requêtes');
execute_query(get_current_db().url, query);
}
function execute_query(db_url, query){
if (!db_url || !query) {
update_results_view();
} else {
$.ajax({
url: 'sql/execute',
type: 'POST',
contentType: 'application/json',
data: JSON.stringify({db: db_url, query: query}),
dataType: 'json',
async: true,
success: function(json){
update_results_view(json.head.vars, json.results.bindings);
}
});
}
}
function update_results_view(vars, bindings){
d3.selectAll('#query_results > table').remove();
if (!arguments.length) {return;}
var table = d3.select('#query_results').append('table');
var th = table.append('thead').append('tr').selectAll('th')
.data(vars)
.enter().append('th')
.text(function(d) { return d; });
var tr = table.append('tbody').selectAll('tr')
.data(bindings)
.enter()
.append('tr');
var td = tr.selectAll('td')
.data(function(d) { return vars.map(function(key){return d[key];}); })
.enter()
.append('td')
.text(function(d) { return d; });
}
Raw
config.json
{
"databases": [
{
"url":"sqlite:///data/RadioFasciste.sqlite",
"name":"Radio fasciste",
"queries": [
{
"label": "Conditions d'enregistrement et fonction du locuteur",
"comment": "un exemple de requête -- jointure simple",
"type": "Select",
"columns": [
"conditions_enregistrement.fond_sonore",
"conditions_enregistrement.lieu",
"conditions_enregistrement.langue",
"fonction.qualification"
],
"from": {
"type": "Join",
"left": {
"type": "Table",
"name": "conditions_enregistrement"
},
"right": {
"type": "Table",
"name": "fonction"
},
"onclause": {
"type": "eq",
"args": [
{
"type": "Column",
"name": "ID_doct",
"table": "conditions_enregistrement"
},
{
"type": "Column",
"name": "ID_doct",
"table": "fonction"
}
]
}
},
"where": {},
"distinct": false,
"group_by": [],
"order_by": [],
"limit": 7
}
]
},
{
"name": "suffrages",
"url":"sqlite:///data/sf.sqlite"
}
]
}
Raw
index.html
<!DOCTYPE html>
<html>
<head>
<title>Boussole-dev</title>
<link type="text/css" rel="stylesheet" href="style.css"></link>
<!-- lib -->
<script type="text/javascript" src="http://code.jquery.com/jquery-1.7.1.min.js"></script>
<!-- <script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/underscore.js/1.3.3/underscore-min.js"></script> -->
<!-- <script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/backbone.js/0.9.2/backbone-min.js"></script> -->
<!-- <script type="text/javascript" src="https://github.com/okfn/recline/raw/master/recline.js"></script> -->
<script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/d3/2.8.1/d3.v2.min.js"></script>
<!-- <script type="text/javascript" src="http://d3js.org/d3.v2.js"></script> -->
<!-- <script type="text/javascript" src="lib/d3.v2.js"></script> -->
<!-- <script type="text/javascript" src="lib/jquery/js/jquery-1.7.1.min.js"></script> -->
<!-- <script type="text/javascript" src="lib/backbone.js"></script> -->
<!-- <script type="text/javascript" src="lib/helpers.js"></script> -->
<!-- /lib -->
<script type="text/javascript" src="js/client.js"></script>
<script type="text/javascript">
$(function(){$.getJSON('config.json', init);});
</script>
</head>
<body>
<!-- #page -->
<div id="page">
<div>
<select id="db_selector" name="db">
<option value="">Choisir une base de données...</option>
</select>
</div>
<div id="schema_viewer" class="panel">
<svg></svg>
</div>
<div id="queries" class="panel" style="visibility:hidden">
<select id="query_selector" name="query">
<option value="">Choisir une requête...</option>
</select>
<div id="query_editor">
--- Graphical editor goes here ---
</div>
</div>
<div id="query_results"></div>
</div>
<!-- /#page -->
</body>
</html>
Raw
requirements.txt
SQLAlchemy>=0.7.3
Flask>=0.7
#optional
#requires libevent
gevent>=0.13
Raw
server.py
#! /usr/bin/env python
# -*- coding: utf-8 -*-
"""
"""
import flask
import sql
app = flask.Flask(__name__, static_folder='', static_url_path='')
@app.route('/')
def index():
return flask.send_file('index.html')
@app.route('/sql/describe')
def describe():
"""Returns a json representation of that database."""
url = flask.request.args.get('db')
desc = sql.describe(url)
return flask.jsonify(desc)
@app.route('/sql/execute', methods=['GET', 'POST'])
def execute():
"""JSON service for SQL queries.
Returns a dict in SPARQL-JSON-like format.
"""
data = flask.request.json
res = sql.execute(data['db'], data['query'])
resp = {
'head': {
'vars': res.keys(),
'types': []
},
'results': {
'bindings': map(dict, res)
}
}
return flask.jsonify(resp)
if __name__ == '__main__':
import sys, os, logging
host, port = '127.0.0.1', 5000
url = 'http://%s:%i/' % (host, port)
args = sys.argv[1:]
if not '-vv' in args:
# Hack to hide syscall interruption from user
# http://bugs.python.org/issue7978
sys.stderr = open(os.devnull, 'w')
if '-v' in args:
logging.basicConfig(stream=sys.stdout, level=logging.INFO)
if '-o' in args:
try:
import webbrowser, gevent
except ImportError:
pass
else:
gevent.spawn(webbrowser.open_new, url)
gevent.sleep()
print(' * Running on %s' % url)
print(' * Press (Ctrl+C) to interrupt')
app.run(host, port)
Raw
sql.py
# -*- coding: utf-8 -*-
"""Database reflection and SQL algebra.
"""
import re
import sqlalchemy as sa
def get_engine(url):
cache = get_engine._cache
if url not in cache:
cache[url] = sa.create_engine(url)
return cache[url]
get_engine._cache = {}
def describe(db_url):
"Returns a json-compatible view of metadata."
engine = get_engine(db_url)
meta = sa.MetaData(engine, reflect=True)
return {'type': 'MetaData',
'name': db_url,
'tables': [describe_table(t) for t in meta.sorted_tables if not system_tables_re.match(t.name)]}
system_tables_re = re.compile('^(sqlite_)', re.I)
describe_table = lambda t: {
'type': 'Table',
'name': t.name,
'columns': [describe_column(c, t.name) for c in t.columns],
'constraints': map(describe_constraint, t.constraints),
'count': t.count().scalar()
}
describe_column = lambda col, table=None: {
'type': 'Column',
'name': col.name,
'dtype': '(unrecognized type)' if isinstance(col.type, sa.types.NullType) else unicode(col.type),
'primary_key': col.primary_key,
'table': table
}
def describe_constraint(cst):
d = {'type': cst.__class__.__name__,
'name': cst.name}
if isinstance(cst, sa.schema.ForeignKeyConstraint):
d['columns'] = dict(zip(cst.columns, [e.target_fullname for e in cst.elements]))
elif isinstance(cst, sa.schema.ColumnCollectionConstraint):
d['columns'] = [c.name for c in cst.columns]
elif isinstance(cst, sa.schema.CheckConstraint):
d['sqltext'] = cst.sqltext
return d
def execute(db_url, statement):
engine = get_engine(db_url)
meta = sa.MetaData(engine, reflect=True)
sa_query = SAInflator.inflate(statement, meta)
results = engine.execute(sa_query)
return results
class SAInflator(object):
"Recovers sqlalchemy objects from JSON-compatible dicts."
def __init__(self, metadata):
self.meta = metadata
@classmethod
def inflate(cls, dic, metadata):
assert dic['type'] == 'Select'
return cls(metadata).inflate_select(dic)
def inflate_select(self, dic):
sel = self.inflate_selectable(dic['from']).select()
if dic.get('columns', '*') != '*':
sel = sel.with_only_columns(map(self.inflate_column, dic['columns']))
if dic.get('where'):
sel = sel.where(self.inflate_expression(dic['where']))
if dic.get('distinct'):
sel = sel.distinct()
if 'limit' in dic:
sel = sel.limit(dic['limit'])
return sel
def inflate_selectable(self, dic):
if isinstance(dic, basestring):
dic = {'type': 'Table', 'name': dic}
if dic['type'] == 'Table':
return self.meta.tables[dic['name']]
elif dic['type'] == 'Join':
left = self.inflate_selectable(dic['left'])
right = self.inflate_selectable(dic['right'])
on = self.inflate_expression(dic['onclause'])
return sa.sql.join(left, right, on)
elif dic['type'] == 'Select':
raise NotImplementedError
raise ValueError
def inflate_column(self, dic):
if isinstance(dic, basestring):
t, n = dic.split('.')
else:
t, n = dic['table'], dic['name']
return self.meta.tables[t].c.get(n)
def inflate_expression(self, dic):
if not dic:
return
t = dic.get('type', 'literal')
if t == 'literal':
return dic['value']
elif t == 'Column':
return self.inflate_column(dic)
elif t in ('eq', 'lt', 'gt'):
op = getattr(sa.sql.expression.operators, t)
else:
op = getattr(sa.sql.expression, t)
return op(*map(self.inflate_expression, dic['args']))
Raw
style.css
html {
height: 100%;
}
html>body {
height: 96%;
}
div#page {
width: 98%;
min-width: 540px;
max-width: 1120px;
min-height: 100%;
margin: 0 auto;
padding: 0;
border: 1px solid lightgrey;
}
div#query_results {
clear: both;
}
#db_selector {
top: 0;
left: 0;
display: block;
}
.panel {
width: 50%;
float: left;
}
.panel > svg {
width: 540px;
height: 540px;
}
.sql_table {
fill: olive;
}
.sql_column {
fill: steelblue;
}
.sql_foreign_key {
stroke: steelblue;
stroke-width: 0.6ex;
stroke-opacity: .25;
fill: none;
}
svg text {
fill: black;
}
.highlight {
border: 1px dotted red;
}
#query_results table {
width: 100%;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment