ingo_weinzierl@23: # -*- coding: utf-8 -*- ingo_weinzierl@23: # ingo_weinzierl@23: # (c) 2008, 2009, 2010 by ingo_weinzierl@23: # Sascha L. Teichmann ingo_weinzierl@23: # Ingo Weinzierl bjoern@97: # (c) 2011 Björn Ricks mschieder@502: # (c) 2018 Intevation GmbH mschieder@502: # Authors: mschieder@502: # * Magnus Schieder ingo_weinzierl@23: # ingo_weinzierl@23: # This is Free Software licensed unter the terms of GPLv3 or later. ingo_weinzierl@23: # For details see LICENSE coming with the source of 'getan'. ingo_weinzierl@23: # ingo_weinzierl@23: ingo_weinzierl@23: import logging teichmann@40: import os teichmann@40: ingo@33: try: ingo@33: import sqlite3 as db ingo@33: except ImportError: ingo@33: from pysqlite2 import dbapi2 as db ingo_weinzierl@23: ingo_weinzierl@23: from getan.project import Project, Entry ingo_weinzierl@23: ingo_weinzierl@23: DEFAULT_DATABASE = "time.db" ingo_weinzierl@23: teichmann@40: CREATE_TABLES = [ bjoern@348: """ teichmann@40: CREATE TABLE projects ( teichmann@40: id INTEGER PRIMARY KEY AUTOINCREMENT, bjoern@284: key VARCHAR(16) NOT NULL CONSTRAINT unique_key UNIQUE, teichmann@40: description VARCHAR(256), teichmann@40: active BOOLEAN DEFAULT 1 teichmann@40: ) teichmann@40: """, bjoern@348: """ teichmann@40: CREATE TABLE entries ( teichmann@40: id INTEGER PRIMARY KEY AUTOINCREMENT, teichmann@40: project_id INTEGER REFERENCES projects(id), teichmann@40: start_time TIMESTAMP NOT NULL, teichmann@40: stop_time TIMESTAMP NOT NULL, teichmann@40: description VARCHAR(256), teichmann@40: teichmann@40: CHECK (strftime('%s', start_time) <= strftime('%s', stop_time)) teichmann@40: ) mschieder@499: """, mschieder@499: """ mschieder@499: CREATE TABLE recover( mschieder@499: id INTEGER PRIMARY KEY, mschieder@499: project_id INTEGER REFERENCES projects(id), mschieder@499: start_time TIMESTAMP NOT NULL, mschieder@499: stop_time TIMESTAMP NOT NULL, mschieder@499: description VARCHAR(256), mschieder@499: mschieder@499: CHECK (strftime('%s', start_time) <= strftime('%s', stop_time)) mschieder@499: ) teichmann@40: """ teichmann@40: ] teichmann@40: mschieder@501: CREATE_RECOVER = """ mschieder@501: CREATE TABLE IF NOT EXISTS recover( mschieder@501: id INTEGER PRIMARY KEY, mschieder@501: project_id INTEGER REFERENCES projects(id), mschieder@501: start_time TIMESTAMP NOT NULL, mschieder@501: stop_time TIMESTAMP NOT NULL, mschieder@501: description VARCHAR(256), mschieder@501: mschieder@501: CHECK (strftime('%s', start_time) <= strftime('%s', stop_time)) mschieder@501: ) mschieder@501: """ mschieder@501: ingo_weinzierl@23: LOAD_ACTIVE_PROJECTS = ''' ingo_weinzierl@23: SELECT id, key, description, total ingo_weinzierl@23: FROM projects LEFT JOIN bjoern@144: (SELECT bjoern@144: project_id, ingo_weinzierl@23: sum(strftime('%s', stop_time) - strftime('%s', start_time)) AS total bjoern@144: FROM entries ingo_weinzierl@23: GROUP BY project_id) ON project_id = id ingo_weinzierl@23: WHERE active ingo_weinzierl@23: ''' ingo_weinzierl@23: mschieder@550: LOAD_ACTIVE_PROJECTS_LIKE = ''' mschieder@550: SELECT id, key, description, total mschieder@550: FROM projects LEFT JOIN mschieder@550: (SELECT mschieder@550: project_id, mschieder@550: sum(strftime('%s', stop_time) - strftime('%s', start_time)) AS total mschieder@550: FROM entries mschieder@550: GROUP BY project_id) ON project_id = id mschieder@550: WHERE active and key LIKE :project_id mschieder@550: ''' mschieder@550: bjoern@351: LOAD_ACTIVE_PROJECT = ''' bjoern@351: SELECT id, key, description, total bjoern@351: FROM projects LEFT JOIN bjoern@351: (SELECT bjoern@351: project_id, bjoern@351: sum(strftime('%s', stop_time) - strftime('%s', start_time)) AS total bjoern@351: FROM entries bjoern@351: GROUP BY project_id) ON project_id = id bjoern@351: WHERE active and key = :project_id bjoern@351: ''' bjoern@351: ingo_weinzierl@23: LOAD_PROJECT_ENTRIES = ''' ingo_weinzierl@23: SELECT ingo_weinzierl@23: id, ingo_weinzierl@23: project_id, ingo_weinzierl@23: start_time as "[timestamp]", ingo_weinzierl@23: stop_time as "[timestamp]", ingo_weinzierl@23: description ingo_weinzierl@23: FROM ingo_weinzierl@23: entries ingo_weinzierl@23: WHERE bjoern@350: project_id = :project_id ingo_weinzierl@23: ORDER BY ingo_weinzierl@23: id ingo_weinzierl@23: DESC ingo_weinzierl@23: ''' ingo_weinzierl@23: bjoern@351: LOAD_PROJECT_ENTRIES_YEAR = ''' bjoern@351: SELECT bjoern@351: id, bjoern@351: project_id, bjoern@351: start_time as "[timestamp]", bjoern@351: stop_time as "[timestamp]", bjoern@351: 'no description' AS description bjoern@351: FROM entries bjoern@351: WHERE bjoern@351: project_id = :project_id AND bjoern@351: (description IS NULL or length(description) = 0) bjoern@351: GROUP BY round(julianday(start_time)) bjoern@351: UNION bjoern@351: SELECT bjoern@351: id, bjoern@351: project_id, bjoern@351: start_time as "[timestamp]", bjoern@351: stop_time as "[timestamp]", bjoern@351: description bjoern@351: FROM entries bjoern@351: WHERE bjoern@351: project_id = :project_id AND bjoern@351: (strftime('%Y', start_time) ) = :year AND bjoern@351: description IS NOT NULL AND length(description) > 0 bjoern@351: ''' bjoern@351: bjoern@351: LOAD_PROJECT_ENTRIES_WEEK = ''' bjoern@351: SELECT bjoern@351: id, bjoern@351: project_id, bjoern@351: start_time as "[timestamp]", bjoern@351: stop_time as "[timestamp]", bjoern@351: 'no description' AS description bjoern@351: FROM entries bjoern@351: WHERE bjoern@351: project_id = :project_id AND bjoern@351: (strftime('%Y', start_time) ) = :year AND bjoern@351: (description IS NULL or length(description) = 0) bjoern@351: AND (strftime('%W', start_time) = :week bjoern@351: OR strftime('%W', stop_time) = :week) bjoern@351: GROUP BY round(julianday(start_time)) bjoern@351: UNION bjoern@351: SELECT bjoern@351: id, bjoern@351: project_id, bjoern@351: start_time as "[timestamp]", bjoern@351: stop_time as "[timestamp]", bjoern@351: description bjoern@351: FROM entries bjoern@351: WHERE bjoern@351: project_id = :project_id AND bjoern@351: (strftime('%Y', start_time) ) = :year AND bjoern@351: description IS NOT NULL AND length(description) > 0 bjoern@351: AND (strftime('%W', start_time) = :week bjoern@351: OR strftime('%W', stop_time) = :week) bjoern@351: ORDER BY start_time bjoern@351: ''' bjoern@351: ingo_weinzierl@23: INSERT_PROJECT_ENTRY = ''' ingo_weinzierl@23: INSERT INTO entries (project_id, start_time, stop_time, description) ingo_weinzierl@23: VALUES(?,?,?,?) ingo_weinzierl@23: ''' ingo_weinzierl@23: mschieder@499: INSERT_RECOVER= ''' mschieder@499: INSERT OR REPLACE INTO recover(id, project_id, start_time, stop_time, description) mschieder@499: VALUES(1,?,?,?,?) mschieder@499: ''' mschieder@499: mschieder@499: LOAD_RECOVER= ''' mschieder@499: SELECT mschieder@499: id, mschieder@499: project_id, mschieder@499: start_time as "[timestamp]", mschieder@499: stop_time as "[timestamp]", mschieder@499: description mschieder@499: FROM mschieder@499: recover mschieder@499: WHERE mschieder@499: id = 1 mschieder@499: ''' mschieder@499: mschieder@499: DELETE_RECOVER= "DELETE FROM recover" mschieder@499: bjoern@49: INSERT_PROJECT = ''' bjoern@52: INSERT INTO projects (key, description, active) VALUES (?,?,1) bjoern@49: ''' bjoern@49: ingo_weinzierl@23: DELETE_PROJECT_ENTRY = 'DELETE FROM entries WHERE id = %i' ingo_weinzierl@23: ingo_weinzierl@23: MOVE_ENTRY = 'UPDATE entries SET project_id = ? WHERE id = ?' ingo_weinzierl@23: bernhard@401: UPDATE_ENTRY = ''' bernhard@401: UPDATE entries bernhard@401: SET description = ?, start_time = ?, stop_time = ? bernhard@401: WHERE id = ? bernhard@401: ''' bjoern@68: bernhard@401: UPDATE_PROJECT = 'UPDATE projects SET key = ?, description = ? WHERE id = ?' bjoern@174: ingo_weinzierl@23: logger = logging.getLogger() ingo_weinzierl@23: bjoern@348: bjoern@165: class InvalidProjectKeyError(Exception): bjoern@165: pass bjoern@165: bjoern@348: bjoern@143: class Backend(object): ingo_weinzierl@23: bjoern@348: def __init__(self, database=DEFAULT_DATABASE): ingo_weinzierl@23: self.database = database teichmann@40: self.ensure_exists() bjoern@348: self.con = db.connect(database, bjoern@348: detect_types=db.PARSE_DECLTYPES | bjoern@348: db.PARSE_COLNAMES) mschieder@467: self.con.text_factory = lambda x: str(x, "utf-8", "ignore") ingo_weinzierl@23: teichmann@40: def ensure_exists(self): teichmann@40: """ Creates the database file if it does not exist. """ bjoern@348: if os.path.isfile(self.database): bjoern@348: return teichmann@40: teichmann@40: con, cur = None, None teichmann@40: try: bjoern@348: con = db.connect(self.database) teichmann@40: cur = con.cursor() teichmann@40: try: teichmann@40: for sql in CREATE_TABLES: teichmann@40: cur.execute(sql) teichmann@40: con.commit() teichmann@40: except: teichmann@40: con.rollback() teichmann@40: raise teichmann@40: finally: bjoern@348: if cur: bjoern@348: cur.close() bjoern@348: if con: bjoern@348: con.close() teichmann@40: ingo_weinzierl@23: def load_projects(self): ingo_weinzierl@23: """ Loads active projects from database and returns them as array """ ingo_weinzierl@23: logger.debug("load active projects from database.") ingo_weinzierl@23: cur = None bjoern@348: try: ingo_weinzierl@23: cur = self.con.cursor() ingo_weinzierl@23: cur.execute(LOAD_ACTIVE_PROJECTS) ingo_weinzierl@23: ingo_weinzierl@23: projects = [] ingo_weinzierl@23: while True: ingo_weinzierl@23: row = cur.fetchone() ingo_weinzierl@23: bjoern@135: if not row: bjoern@135: break bjoern@165: # check key bjoern@165: if not row[1]: bjoern@348: raise InvalidProjectKeyError("Project with id %s needs " bjoern@348: "a key" % row[0]) bjoern@353: proj = Project(self, *row) ingo_weinzierl@23: projects.append(proj) ingo_weinzierl@23: ingo_weinzierl@23: logger.info("found %i active projects." % len(projects)) ingo_weinzierl@23: return projects ingo_weinzierl@23: ingo_weinzierl@23: finally: ingo_weinzierl@23: close(cur) ingo_weinzierl@23: mschieder@550: def load_projects_like(self, key): mschieder@550: """ Loads active projects matching the SQL LIKE pattern from the mschieder@550: database and returns them as array. """ mschieder@550: cur = None mschieder@550: try: mschieder@550: cur = self.con.cursor() mschieder@559: cur.execute("PRAGMA case_sensitive_like = true;") mschieder@550: cur.execute(LOAD_ACTIVE_PROJECTS_LIKE, {"project_id": key}) mschieder@550: mschieder@550: projects = [] mschieder@550: while True: mschieder@550: row = cur.fetchone() mschieder@550: mschieder@550: if not row: mschieder@550: break mschieder@550: # check key mschieder@550: if not row[1]: mschieder@550: raise InvalidProjectKeyError("Project with id %s needs " mschieder@550: "a key" % row[0]) mschieder@550: proj = Project(self, *row) mschieder@550: projects.append(proj) mschieder@550: mschieder@550: logger.info("found %i active projects." % len(projects)) mschieder@550: return projects mschieder@550: mschieder@550: finally: mschieder@550: close(cur) mschieder@550: mschieder@499: def load_recover(self): mschieder@501: """If there is an entry in the recovery table, the entry is moved to mschieder@501: its project.""" mschieder@499: try: mschieder@499: cur = self.con.cursor() mschieder@501: # Creates the recover table if it does not exist to make old mschieder@501: # databases compatible. mschieder@501: cur.execute(CREATE_RECOVER) mschieder@499: cur.execute(LOAD_RECOVER) mschieder@499: recover = cur.fetchone() mschieder@499: if not recover: mschieder@499: return mschieder@499: mschieder@499: _, project_id, start_time, stop_time, desc = recover mschieder@499: mschieder@499: cur.execute(INSERT_PROJECT_ENTRY, ( mschieder@499: project_id, start_time, stop_time, desc)) mschieder@499: cur.execute(DELETE_RECOVER) mschieder@499: self.con.commit() mschieder@499: finally: mschieder@499: close(cur) mschieder@499: bjoern@351: def load_project(self, key): bjoern@351: logger.debug("load active projects from database.") bjoern@351: cur = None bjoern@351: try: bjoern@351: cur = self.con.cursor() bjoern@351: cur.execute(LOAD_ACTIVE_PROJECT, {"project_id": key}) bjoern@351: row = cur.fetchone() bjoern@351: if not row: bjoern@351: raise InvalidProjectKeyError("Project with key %s not " bjoern@351: "found." % key) bjoern@351: # check key bjoern@351: if not row[1]: bjoern@351: raise InvalidProjectKeyError("Project with id %s needs " bjoern@351: "a key" % row[0]) bjoern@351: return Project(self, *row) bjoern@351: bjoern@351: finally: bjoern@351: close(cur) bjoern@351: bjoern@351: def load_entries(self, project_id, year=None, week=None): ingo_weinzierl@23: """ Loads all entries that belong to a specific project """ ingo_weinzierl@23: logger.debug("load entries that belong to project %s" % project_id) ingo_weinzierl@23: cur = None bjoern@351: bjoern@351: if week and isinstance(week, int): bjoern@351: week = "%02d" % (week) bjoern@351: ingo_weinzierl@23: try: ingo_weinzierl@23: cur = self.con.cursor() bjoern@351: bjoern@351: if year is None and week is None: bjoern@351: cur.execute(LOAD_PROJECT_ENTRIES, bjoern@351: {"project_id": project_id}) bjoern@351: elif week is None: bjoern@351: cur.execute(LOAD_PROJECT_ENTRIES_YEAR, bjoern@351: {'project_id': project_id, 'year': str(year)}) bjoern@351: else: bjoern@351: cur.execute(LOAD_PROJECT_ENTRIES_WEEK, bjoern@351: {'project_id': project_id, bjoern@351: 'week': week, bjoern@351: 'year': str(year)}) ingo_weinzierl@23: ingo_weinzierl@23: entries = [] ingo_weinzierl@23: while True: bjoern@146: row = cur.fetchone() ingo_weinzierl@23: bjoern@146: if not row: bjoern@146: break bjoern@146: entries.append(Entry(*row)) ingo_weinzierl@23: ingo_weinzierl@23: logger.debug("Found %i entries that belong to project '%i'" ingo_weinzierl@23: % (len(entries), project_id)) ingo_weinzierl@23: return entries ingo_weinzierl@23: finally: ingo_weinzierl@23: close(cur) ingo_weinzierl@23: ingo_weinzierl@23: def insert_project_entry(self, project, stop_time, desc): bjoern@135: if project is None: bjoern@135: return ingo_weinzierl@23: cur = None ingo_weinzierl@23: try: ingo_weinzierl@23: cur = self.con.cursor() ingo_weinzierl@23: cur.execute(INSERT_PROJECT_ENTRY, ( ingo_weinzierl@23: project.id, project.start, stop_time, desc)) mschieder@499: cur.execute(DELETE_RECOVER) ingo_weinzierl@23: self.con.commit() ingo_weinzierl@23: logger.debug("Added new entry '%s' of project '%s' into db" ingo_weinzierl@23: % (desc, project.desc)) ingo_weinzierl@23: bjoern@362: project.load_entries() ingo_weinzierl@23: finally: ingo_weinzierl@23: close(cur) ingo_weinzierl@23: mschieder@499: def insert_recover(self, project, stop_time, desc): mschieder@499: if project is None: mschieder@499: return mschieder@499: cur = None mschieder@499: try: mschieder@499: cur = self.con.cursor() mschieder@499: cur.execute(INSERT_RECOVER, ( mschieder@499: project.id, project.start, stop_time, desc)) mschieder@499: self.con.commit() mschieder@499: finally: mschieder@499: close(cur) mschieder@499: bjoern@52: def insert_project(self, key, description): bjoern@52: if key is None or description is None: bjoern@49: return bjoern@49: cur = None bjoern@50: try: bjoern@49: cur = self.con.cursor() bjoern@52: cur.execute(INSERT_PROJECT, (key, description)) bjoern@49: self.con.commit() bjoern@52: logger.debug("Added a new project '%s' into db" % description) bjoern@50: finally: bjoern@50: close(cur) ingo_weinzierl@23: ingo_weinzierl@23: def delete_entries(self, entries): bjoern@135: if entries is None: bjoern@135: return ingo_weinzierl@23: ingo_weinzierl@23: cur = None ingo_weinzierl@23: try: ingo_weinzierl@23: cur = self.con.cursor() ingo_weinzierl@23: for entry in entries: ingo_weinzierl@23: cur.execute(DELETE_PROJECT_ENTRY % entry.id) ingo_weinzierl@23: logger.debug("Deleted entry: %s (%d)" % (entry.desc, entry.id)) ingo_weinzierl@23: self.con.commit() ingo_weinzierl@23: finally: ingo_weinzierl@23: close(cur) ingo_weinzierl@23: ingo_weinzierl@23: def move_entries(self, entries, new_project_id): bjoern@135: if entries is None or new_project_id is None: bjoern@135: return ingo_weinzierl@23: ingo_weinzierl@23: cur = None ingo_weinzierl@23: try: ingo_weinzierl@23: cur = self.con.cursor() ingo_weinzierl@23: for entry in entries: ingo_weinzierl@23: cur.execute(MOVE_ENTRY, (new_project_id, entry.id)) ingo_weinzierl@23: logger.debug("Moved entry '%s' (id=%d) to project with id %d." ingo_weinzierl@23: % (entry.desc, entry.id, new_project_id)) ingo_weinzierl@23: self.con.commit() ingo_weinzierl@23: finally: ingo_weinzierl@23: close(cur) ingo_weinzierl@23: bjoern@68: def update_entry(self, entry): bjoern@68: if not entry: bjoern@68: return bjoern@68: bjoern@68: cur = None bjoern@68: try: bjoern@68: cur = self.con.cursor() bernhard@401: cur.execute(UPDATE_ENTRY, (entry.desc, entry.start, bernhard@401: entry.end, entry.id)) bjoern@68: self.con.commit() bernhard@401: logger.debug("Updated entry to: '%s'" % (entry)) bjoern@68: finally: bjoern@68: close(cur) bjoern@68: bjoern@174: def update_project(self, project): bjoern@174: if not project: bjoern@174: return bjoern@174: bjoern@174: cur = None bjoern@174: try: bjoern@174: cur = self.con.cursor() bjoern@348: cur.execute( bjoern@348: UPDATE_PROJECT, (project.key, project.desc, project.id)) bjoern@174: self.con.commit() bjoern@174: logger.debug("Updated project: (%d) %s %s" % (project.id, bjoern@349: project.key, bjoern@349: project.desc)) bjoern@174: finally: bjoern@174: close(cur) bjoern@174: bjoern@348: ingo_weinzierl@23: def close(cur): ingo_weinzierl@23: """ This function closes a database cursor if it is existing """ ingo_weinzierl@23: if cur: bjoern@135: try: bjoern@135: cur.close() ingo_weinzierl@23: except: ingo_weinzierl@23: logger.warn("could not close database cursor.") ingo_weinzierl@23: ingo_weinzierl@23: # vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8: