sean@0: #!/usr/bin/env python sean@0: sean@0: """ supplys the data needed to comunicate with the roundup-server, sean@0: and the sqlite database. Represents the types of errors used in roundup. sean@0: sean@0: author: Sascha L. Teichmann sean@0: author: Bernhard Reiter sean@0: author: Sean Engelhardt sean@0: bernhard@17: (c) 2010, 2015, 2018 by Intevation GmbH sean@0: sean@0: This is Free Software unter the terms of the sean@0: GNU GENERAL PUBLIC LICENSE Version 3 or later. sean@0: See http://www.gnu.org/licenses/gpl-3.0.txt for details sean@0: """ sean@0: sean@0: import os bernhard@17: from typing import List sean@0: sean@1: COLUMNS= [ sean@0: "critical", "urgent", "bug", "feature", "wish", sean@0: ] sean@0: sean@0: data_dict = { sean@0: "date": [], sean@0: "critical": [], sean@0: "urgent": [], sean@0: "bug": [], sean@0: "feature": [], mschieder@20: "wish": [], mschieder@20: "noPrio": [] sean@0: } sean@0: sean@1: #SQL sean@0: sean@1: #DEMO System sean@0: SELECT_ALL = """ sean@0: SELECT strftime("%Y-%m-%dT%H:%M:%S", timestamp), sean@0: critical, sean@0: urgent, sean@0: bug, sean@0: feature, sean@0: wish sean@0: FROM issues sean@0: ORDER BY timestamp sean@0: """ sean@0: bernhard@7: SELECT_WHERE = """ bernhard@7: SELECT strftime("%Y-%m-%dT%H:%M:%S", timestamp), bernhard@7: critical, bernhard@7: urgent, bernhard@7: bug, bernhard@7: feature, mschieder@20: wish, mschieder@20: None bernhard@7: FROM issues bernhard@7: WHERE {} bernhard@7: ORDER BY timestamp bernhard@7: """ bernhard@7: sean@0: CREATE_DB = """ sean@0: CREATE TABLE issues ( sean@0: timestamp TIMESTAMP NOT NULL UNIQUE DEFAULT current_timestamp, sean@0: critical INTEGER NOT NULL DEFAULT 0, sean@0: urgent INTEGER NOT NULL DEFAULT 0, sean@0: bug INTEGER NOT NULL DEFAULT 0, sean@0: feature INTEGER NOT NULL DEFAULT 0, sean@0: wish INTEGER NOT NULL DEFAULT 0 sean@0: ) sean@0: """ sean@0: sean@0: sean@0: INSERT_NEW = """ sean@0: INSERT INTO issues (critical, urgent, bug, feature, wish) sean@0: VALUES (?, ?, ?, ?, ?) sean@1: """ mschieder@20: def build_sql_select(columns): mschieder@20: return """ mschieder@20: SELECT strftime("%Y-%m-%dT%H:%M:%S", timestamp), mschieder@20: """+ columns + """ mschieder@20: FROM issues mschieder@20: WHERE {} mschieder@20: ORDER BY timestamp mschieder@20: """ mschieder@20: sean@1: bernhard@17: bernhard@17: def build_sql_commands(list_of_columns: list): bernhard@17: """Build sql commands for use with collect_issues. bernhard@17: """ bernhard@17: create_db = ("""CREATE TABLE issues ( bernhard@17: timestamp TIMESTAMP NOT NULL UNIQUE DEFAULT current_timestamp, bernhard@17: """ + bernhard@17: ", ".join([c + " INTEGER NOT NULL DEFAULT 0" bernhard@17: for c in list_of_columns]) + ")") bernhard@17: bernhard@17: insert_new = ("INSERT INTO issues (" + ", ".join(list_of_columns) + ") " + bernhard@17: "VALUES (" + ("?, "*len(list_of_columns))[:-2]+ ")") bernhard@17: bernhard@17: select_tmpl = ("""SELECT strftime("%Y-%m-%dT%H:%M:%S", timestamp),""" + bernhard@17: ", ".join(list_of_columns)) bernhard@17: bernhard@17: select_all = select_tmpl + "FROM issues ORDER BY timestamp" bernhard@17: select_where = select_tmpl + "FROM issues WHERE {} ORDER BY timestamp" bernhard@17: bernhard@17: # print(select_all, select_where, create_db, insert_new) bernhard@17: return select_all, select_where, create_db, insert_new