# HG changeset patch # User Bernhard Reiter # Date 1531132108 -7200 # Node ID adca5b3780d2640fc5d554b2df876e3f2eebef48 # Parent 5c24c116ba1f19af3803150f5a08af5bee522e6a Add collecting no-prio issues. Restructure. * Move usage examples from collect_issues.py to examples/. Also add a section about this to the readme. This should make it easier to understand how it all works together. As we have two examples now, hardcode the database filenames. * Add a function to create the necessary database command from a list of columns. This makes it easier to work with tracker that have customized their priorities, * Add an example how to collect no-prio issues in a database. Note that the display part is not included. * Cleanup roundup_content_data by removing db file name suggestions and unused "reference db" command. This could be re-added for specific installations if they need it, however it probably should be separated as configuration. diff -r 5c24c116ba1f -r adca5b3780d2 README.creole --- a/README.creole Mon Jul 09 11:44:52 2018 +0200 +++ b/README.creole Mon Jul 09 12:28:28 2018 +0200 @@ -4,7 +4,26 @@ It is Free Software, check out the file headers. +=== Example +Run ./demo.py from a roundup-tracker to have a running tracker. + +{{{ +cp examples/collect_demo1.py c1.py +./c1.py +# create or change some issues +./c1.py + +# to inspect the database contents +sqlite3 demo1.db 'select * from issues;' + +./display_issues_demo.py >demo1.html +chromium demo1.html +}}} + + === Notes +examples/collect_demo2.py builds database column names dynamically +and tracks issues with priorities. The display part is still missing. When migrating to 3:99c68ebfb3b9, Nov 30 17:46:22 2015 you need to add the print statements for the content-type header diff -r 5c24c116ba1f -r adca5b3780d2 collect_issues.py --- a/collect_issues.py Mon Jul 09 11:44:52 2018 +0200 +++ b/collect_issues.py Mon Jul 09 12:28:28 2018 +0200 @@ -11,19 +11,7 @@ GNU GENERAL PUBLIC LICENSE Version 3 or later. See http://www.gnu.org/licenses/gpl-3.0.txt for details - -##USAGE EXAMPLE: ## - -BASE_URL_DEMO = "http://localhost:8917/demo/" -SEARCH_URL_DEMO = "issue?@action=export_csv&@columns=title,priority&@filter=status&@pagesize=50&@startwith=0&status=-1,1,2,3,4,5,6,7" - -LOGIN_PARAMETERS_DEMO = ( - ("__login_name", "demo"), - ("__login_password", "demo"), - ("@action", "Login"), - ) - -save_stats_in_db(LOGIN_PARAMETERS_DEMO, BASE_URL_DEMO, rcd.DATABASE_DEMO, rcd.COLUMNS, rcd.CREATE_DB, rcd.INSERT_NEW, SEARCH_URL_DEMO) +For usage see examples/. """ import http.cookiejar diff -r 5c24c116ba1f -r adca5b3780d2 display_issues.py --- a/display_issues.py Mon Jul 09 11:44:52 2018 +0200 +++ b/display_issues.py Mon Jul 09 12:28:28 2018 +0200 @@ -96,4 +96,4 @@ #print("Content-Type: text/html") #print() - render_db_stats_as_html(rcd.DATABASE_DEMO, rcd.SELECT_ALL) + render_db_stats_as_html("./demo1.db", rcd.SELECT_ALL) diff -r 5c24c116ba1f -r adca5b3780d2 display_issues_demo.py --- a/display_issues_demo.py Mon Jul 09 11:44:52 2018 +0200 +++ b/display_issues_demo.py Mon Jul 09 12:28:28 2018 +0200 @@ -6,7 +6,7 @@ author: Bernhard Reiter author: Sean Engelhardt -(c) 2010,2015 by Intevation GmbH +(c) 2010, 2015, 2018 by Intevation GmbH This is Free Software unter the terms of the GNU GENERAL PUBLIC LICENSE Version 3 or later. @@ -16,6 +16,6 @@ from display_issues import * cgitb.enable() # (optional) HTML traceback to browser -#render_db_stats_as_html(rcd.DATABASE_DEMO, rcd.SELECT_ALL) -render_db_stats_as_html(rcd.DATABASE_DEMO, +#render_db_stats_as_html("./demo1.db", rcd.SELECT_ALL) +render_db_stats_as_html("./demo1.db", rcd.SELECT_WHERE.format("timestamp > date('now', '-2 month')")) diff -r 5c24c116ba1f -r adca5b3780d2 examples/collect_demo1.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/examples/collect_demo1.py Mon Jul 09 12:28:28 2018 +0200 @@ -0,0 +1,20 @@ +#!/usr/bin/env python3 +"""Connect to roundup-tracker and save status to db for example demo1. + +Run periodically as often as you want data points to be saved. +demo1 only tracks issues with a priority. +""" +from collect_issues import save_stats_in_db +import roundup_content_data as rcd + +BASE_URL_DEMO = "http://localhost:8917/demo/" +SEARCH_URL_DEMO = "issue?@action=export_csv&@columns=title,priority&@filter=status&@pagesize=50&@startwith=0&status=-1,1,2,3,4,5,6,7" + +LOGIN_PARAMETERS_DEMO = ( + ("__login_name", "demo"), + ("__login_password", "demo"), + ("@action", "Login"), + ) + +save_stats_in_db(LOGIN_PARAMETERS_DEMO, BASE_URL_DEMO, "./demo1.py", + rcd.COLUMNS, rcd.CREATE_DB, rcd.INSERT_NEW, SEARCH_URL_DEMO) diff -r 5c24c116ba1f -r adca5b3780d2 examples/collect_demo2.py --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/examples/collect_demo2.py Mon Jul 09 12:28:28 2018 +0200 @@ -0,0 +1,29 @@ +#!/usr/bin/env python3 +"""Connect to roundup-tracker and save status to db for example demo2. + +Run periodically as often as you want data points to be saved. +demo2 tracks issue without priority in column `None`. +""" +from collect_issues import save_stats_in_db +import roundup_content_data as rcd + +BASE_URL_DEMO = "http://localhost:8917/demo/" +SEARCH_URL_DEMO = "issue?@action=export_csv&@columns=title,priority&@filter=status&@pagesize=50&@startwith=0&status=-1,1,2,3,4,5,6,7" + +LOGIN_PARAMETERS_DEMO = ( + ("__login_name", "demo"), + ("__login_password", "demo"), + ("@action", "Login"), + ) + +list_of_columns = ['critical', 'major', 'normal', 'minor', 'wishlist'] +data_dict = { key: [] for key in list_of_columns } + +# To track issues without prio we need to add an extra column in the db cmds. +select_all, select_where, create_db, insert_new = \ + rcd.build_sql_commands(list_of_columns + ['None']) + +# We enable the extra colum with `include_no_prio=True` +save_stats_in_db(LOGIN_PARAMETERS_DEMO, BASE_URL_DEMO, "./demo2.db", + list_of_columns, create_db, insert_new, + SEARCH_URL_DEMO, include_no_prio=True) diff -r 5c24c116ba1f -r adca5b3780d2 roundup_content_data/__init__.py --- a/roundup_content_data/__init__.py Mon Jul 09 11:44:52 2018 +0200 +++ b/roundup_content_data/__init__.py Mon Jul 09 12:28:28 2018 +0200 @@ -7,7 +7,7 @@ author: Bernhard Reiter author: Sean Engelhardt -(c) 2010,2015 by Intevation GmbH +(c) 2010, 2015, 2018 by Intevation GmbH This is Free Software unter the terms of the GNU GENERAL PUBLIC LICENSE Version 3 or later. @@ -15,13 +15,7 @@ """ import os - -#Add desired sqlite databases here -DATABASE_REFERENCCE = os.path.dirname(os.path.realpath(__file__)) + "/test_reference.db" -DATABASE_DEMO = os.path.dirname(os.path.realpath(__file__)) + "/demo.db" -DATABASE_ERRORDB = os.path.dirname(os.path.realpath(__file__)) + "/errordatabase.db" -DATABASE_TECH_INTERN = os.path.dirname(os.path.realpath(__file__)) + "/tech_intern.db" -DATABASE_INT_TEST = os.path.dirname(os.path.realpath(__file__)) + "/int_test.db" +from typing import List COLUMNS= [ "critical", "urgent", "bug", "feature", "wish", @@ -62,7 +56,6 @@ ORDER BY timestamp """ - CREATE_DB = """ CREATE TABLE issues ( timestamp TIMESTAMP NOT NULL UNIQUE DEFAULT current_timestamp, @@ -80,15 +73,24 @@ VALUES (?, ?, ?, ?, ?) """ -#Referecen DB: -SELECT_ALL_REFERENCE = """ -SELECT strftime("%Y-%m-%dT%H:%M:%S", sample_time), - critical, - major, - crash, - normal, - minor, - wishlist -FROM issues -ORDER BY sample_time -""" + +def build_sql_commands(list_of_columns: list): + """Build sql commands for use with collect_issues. + """ + create_db = ("""CREATE TABLE issues ( + timestamp TIMESTAMP NOT NULL UNIQUE DEFAULT current_timestamp, + """ + + ", ".join([c + " INTEGER NOT NULL DEFAULT 0" + for c in list_of_columns]) + ")") + + insert_new = ("INSERT INTO issues (" + ", ".join(list_of_columns) + ") " + + "VALUES (" + ("?, "*len(list_of_columns))[:-2]+ ")") + + select_tmpl = ("""SELECT strftime("%Y-%m-%dT%H:%M:%S", timestamp),""" + + ", ".join(list_of_columns)) + + select_all = select_tmpl + "FROM issues ORDER BY timestamp" + select_where = select_tmpl + "FROM issues WHERE {} ORDER BY timestamp" + + # print(select_all, select_where, create_db, insert_new) + return select_all, select_where, create_db, insert_new