Add an script to generate patchwork stats

[Mauro Carvalho Chehab]

Em Fri, 7 Jun 2019 11:35:54 -0300
mchehab at infradead.org escreveu:

> It could be useful to be able to see how many patches a patchwork
> instance has. This small script does that.
> 
> We're using it to maintain a page with includes the generated stats at:
> 
> 	https://linuxtv.org/patchwork_stats.php
> 
> It requires pandas and matplotlib to work. It assumes a database
> using MySQL, but can easily changed to Postgres or to any other
> DB.
> 
> Signed-off-by: Mauro Carvalho Chehab <mchehab+samsung at kernel.org>
> 
> -
> 
> PS.: By purpose, I didn't use Django here. It is a way easier for me to
> write SQL commands directly than try to guess how to produce the
> same results with Django. Also, I don't expect this to be applied
> upstream as-is, as, if we're doing some stats module at patchwork, it 
> would require a lot more: templates, css changes, customizations, etc.
> 
> The goal here is just to have something really simple that would
> allow to account the number of patches received/accepted/rejected/etc
> and produce graphics in a way that we could later easily add/remove
> stuff.
> 
> The script takes a 2 years window and includes the current month,
> but it should be easy to change it to any other period of time.
> 
> The drawback is that this script will require changes if the database
> model changes. It was produced against stable 2.1 data model.
> 
> Yet, as others may want to provide similar things, I'm providing
> the code we use at linuxtv.org (with the DB connection details
> sanitized).
> 
> 
> +#!/usr/bin/env python

That's what lack of caffeine brings... the patch is obviously
broken here :-)

-


[PATCH] Add an script to generate patchwork stats

It could be useful to be able to see how many patches a patchwork
instance has. This small script does that.

We're using it to maintain a page with includes the generated stats at:

	https://linuxtv.org/patchwork_stats.php

It requires pandas and matplotlib to work. It assumes a database
using MySQL, but can easily changed to Postgres or to any other
DB.

Signed-off-by: Mauro Carvalho Chehab <mchehab+samsung at kernel.org>

-

PS.: By purpose, I didn't use Django here. It is a way easier for me to
write SQL commands directly than try to guess how to produce the
same results with Django. Also, I don't expect this to be applied
upstream as-is, as, if we're doing some stats module at patchwork, it 
would require a lot more: templates, css changes, customizations, etc.

The goal here is just to have something really simple that would
allow to account the number of patches received/accepted/rejected/etc
and produce graphics in a way that we could later easily add/remove
stuff.

The script takes a 2 years window and includes the current month,
but it should be easy to change it to any other period of time.

The drawback is that this script will require changes if the database
model changes. It was produced against stable 2.1 data model.

Yet, as others may want to provide similar things, I'm providing
the code we use at linuxtv.org (with the DB connection details
sanitized).

diff --git a/stats.py b/stats.py
new file mode 100755
index 000000000000..d7878ce08c70
--- /dev/null
+++ b/stats.py
@@ -0,0 +1,49 @@
+#!/usr/bin/env python2
+
+import matplotlib
+matplotlib.use('Agg')
+
+import MySQLdb
+import pandas as pd
+import matplotlib.pyplot as plt
+
+conn = MySQLdb.connect(host="localhost", user="patchwork", passwd="yaicCoqui", db="patchwork")
+cursor = conn.cursor()
+
+# Total patches
+
+df = pd.read_sql('select DATE_FORMAT(date, "%Y-%m") AS date , count(*) AS patches from patchwork_submission WHERE date between DATE_SUB(DATE_FORMAT(CURRENT_DATE,"%Y-%m-01"),INTERVAL 2 YEAR) and NOW() group by DATE_FORMAT(date, "%Y-%m") ORDER BY YEAR(date), MONTH(DATE)', con=conn)
+df['date'] = pd.to_datetime(df.date, format='%Y-%m')
+
+fig = plt.figure(figsize=(10,6), facecolor='w', edgecolor='k')
+ax = fig.add_subplot(1,1,1)
+#ax.bar(df.date, df.patches)
+ax.plot_date(df.date, df.patches, 'ob')
+ax.patch.set_facecolor('lightgrey')
+ax.set_ylim(bottom=0)
+fig.autofmt_xdate()
+ax.set_title('Number of patches received by month')
+ax.autoscale_view()
+
+plt.grid()
+plt.savefig('/usr/local/patchwork/htdocs/static/images/patches_per_date.svg')
+
+# Patches per state
+
+df = pd.read_sql('select DATE_FORMAT(date, "%Y-%m") as date, st.name, count(*) as patches from patchwork.patchwork_patch AS p, patchwork.patchwork_submission as s, patchwork_state as st where date between DATE_SUB(DATE_FORMAT(CURRENT_DATE,"%Y-%m-01"),INTERVAL 2 YEAR) and NOW() and s.id = p.submission_ptr_id and state_id = st.id group by DATE_FORMAT(date, "%Y-%m"), state_id', con=conn)
+df['date'] = pd.to_datetime(df.date, format='%Y-%m')
+df.set_index('date', inplace=True)
+
+fig, ax = plt.subplots(figsize=(14,6))
+
+df.groupby(['name'])['patches'].plot(ax=ax, legend=True)
+ax.patch.set_facecolor('lightgrey')
+ax.set_ylim(bottom=0)
+fig.autofmt_xdate()
+ax.set_title('Number of patches per state per month')
+ax.autoscale_view()
+
+plt.grid()
+plt.legend(loc="upper left", bbox_to_anchor=(1.05,1.0))
+plt.subplots_adjust(left=0.1, bottom=0.15, right=0.8)
+plt.savefig('/usr/local/patchwork/htdocs/static/images/patches_per_state.svg')



Thanks,
Mauro