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 <sascha.teichmann@intevation.de>
sean@0: author: Bernhard Reiter <bernhard@intevation.de>
sean@0: author: Sean Engelhardt <sean.engelhardt@intevation.de>
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