I wanted to try to display a github-like timeline of editor contributions. For this purpose I'm going to use the calendar-heatmap javascript library, and of course I need to load all my edit history. Another candidate I tested is the cal-heatmap library which gives similar results.
%run startup.ipy
Let's choose an editor randomly:
editor_name = 'loujin'
We can extract the number of edits made every day with:
edits_count = sql("""
SELECT date_trunc('day', edit.open_time) AS date,
COUNT(*) AS cnt
FROM edit
JOIN editor ON editor.id = edit.editor
WHERE editor.name = %(editor_name)s
GROUP BY date
ORDER BY cnt DESC
;
""", editor_name=editor_name)
We need to convert the date to seconds for the javascript library:
edits_count.index = edits_count.date.astype('int')*1e-6
edits_count.head()
Now we can transmit the data to javascript by replacing the data in an HTML template:
# Transform into JSON
data = edits_count.cnt.to_json()
from jinja2 import Template
template = Template("""
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>MusicBrainz Editor edits timeline</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.12.0/moment.min.js" charset="utf-8"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.17/d3.min.js" charset="utf-8"></script>
<script src="https://cdn.rawgit.com/DKirwan/calendar-heatmap/337b4314/src/calendar-heatmap.js"></script>
<link rel="stylesheet" href="https://cdn.rawgit.com/DKirwan/calendar-heatmap/337b4314/src/calendar-heatmap.css" />
<style>
svg.calendar-heatmap {
box-sizing: initial;
overflow: visible;
}
</style>
</head>
<body>
<h1>{{ editor_name }}'s MusicBrainz edits timeline
<div id="heatmap"></div>
<script type="text/javascript">
const data = Object.entries({{ data }}).map(
function (t,cnt) {return {date: new Date(parseInt(t)),
count: parseInt(cnt)}}
);
const range = (start, end) => [...Array(end - start + 1)].map((_, i) => start + i);
for (let year of range(2012, 2018)) {
document.getElementById('heatmap').insertAdjacentHTML(
'beforeend', `
<h3>${year}</h3>
<div id="y${year}"></div>
`);
calendarHeatmap().data(data)
.startDate(new Date(year,1,1))
.selector(`#y${year}`)
.tooltipEnabled(true)
.colorRange(['#ffffff', '#ff0000'])
.onClick(function () {
console.log(data);
})();
}
</script>
</body>
</html>
""")
with open('docs/editor_edits_timeline.html', 'w') as f:
f.write(template.render(**globals()))
The result can be seen on this page
Number of artists created:
# lib/MusicBrainz/Server/Constants.pm
types = dict(
EDIT_ARTIST_CREATE = 1,
EDIT_LABEL_CREATE = 10,
EDIT_RELEASE_CREATE = 31,
EDIT_WORK_CREATE = 41,
EDIT_PLACE_CREATE = 61,
EDIT_SERIES_CREATE = 140,
EDIT_EVENT_CREATE = 150,
)
case = ' '.join(["WHEN edit.type={1} THEN '{0}'".format(*it) for it in types.items()])
edits_count = sql("""
SELECT CASE {} END, COUNT(*)
FROM edit
JOIN editor ON editor.id = edit.editor
WHERE editor.name = %(editor_name)s
AND edit.type IN ({})
GROUP BY edit.type
ORDER BY edit.type
;
""".format(case, ', '.join(str(t) for t in types.values())), case=case, editor_name=editor_name)
edits_count