getan/getan: getan/backend.py annotate

annotate getan/backend.py @ 68:a25dab413260

Add new method to update a description of an entry in the database backend

author	Björn Ricks <bjoern.ricks@intevation.de>
date	Wed, 25 May 2011 13:26:09 +0200
parents	9cb57c37369a
children	99639833968d

rev	line source
23 9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	1 # -- coding: utf-8 --
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	2 #
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	3 # (c) 2008, 2009, 2010 by
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	4 # Sascha L. Teichmann <sascha.teichmann@intevation.de>
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	5 # Ingo Weinzierl <ingo.weinzierl@intevation.de>
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	6 #
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	7 # This is Free Software licensed unter the terms of GPLv3 or later.
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	8 # For details see LICENSE coming with the source of 'getan'.
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	9 #
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	10
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	11 import logging
40 e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	12 import os
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	13
33 f96a18c10836 Made getan python-2.4 compatible. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 23 diff changeset	14 try:
f96a18c10836 Made getan python-2.4 compatible. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 23 diff changeset	15 import sqlite3 as db
f96a18c10836 Made getan python-2.4 compatible. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 23 diff changeset	16 except ImportError:
f96a18c10836 Made getan python-2.4 compatible. Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 23 diff changeset	17 from pysqlite2 import dbapi2 as db
23 9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	18
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	19 from getan.project import Project, Entry
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	20
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	21 DEFAULT_DATABASE = "time.db"
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	22
40 e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	23 CREATE_TABLES = [
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	24 """
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	25 CREATE TABLE projects (
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	26 id INTEGER PRIMARY KEY AUTOINCREMENT,
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	27 key VARCHAR(16),
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	28 description VARCHAR(256),
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	29 active BOOLEAN DEFAULT 1
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	30 )
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	31 """,
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	32 """
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	33 CREATE TABLE entries (
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	34 id INTEGER PRIMARY KEY AUTOINCREMENT,
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	35 project_id INTEGER REFERENCES projects(id),
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	36 start_time TIMESTAMP NOT NULL,
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	37 stop_time TIMESTAMP NOT NULL,
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	38 description VARCHAR(256),
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	39
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	40 CHECK (strftime('%s', start_time) <= strftime('%s', stop_time))
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	41 )
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	42 """
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	43 ]
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	44
23 9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	45 LOAD_ACTIVE_PROJECTS = '''
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	46 SELECT id, key, description, total
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	47 FROM projects LEFT JOIN
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	48 (SELECT
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	49 project_id,
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	50 sum(strftime('%s', stop_time) - strftime('%s', start_time)) AS total
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	51 FROM entries
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	52 GROUP BY project_id) ON project_id = id
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	53 WHERE active
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	54 '''
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	55
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	56 LOAD_PROJECT_ENTRIES = '''
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	57 SELECT
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	58 id,
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	59 project_id,
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	60 start_time as "[timestamp]",
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	61 stop_time as "[timestamp]",
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	62 description
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	63 FROM
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	64 entries
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	65 WHERE
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	66 project_id = %i
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	67 ORDER BY
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	68 id
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	69 DESC
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	70 '''
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	71
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	72 INSERT_PROJECT_ENTRY = '''
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	73 INSERT INTO entries (project_id, start_time, stop_time, description)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	74 VALUES(?,?,?,?)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	75 '''
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	76
49 062ce001abd1 add backend method to create new projects Björn Ricks <bjoern.ricks@intevation.de> parents: 40 diff changeset	77 INSERT_PROJECT = '''
52 9cb57c37369a fix insert new project sql statemenent and api Björn Ricks <bjoern.ricks@intevation.de> parents: 50 diff changeset	78 INSERT INTO projects (key, description, active) VALUES (?,?,1)
49 062ce001abd1 add backend method to create new projects Björn Ricks <bjoern.ricks@intevation.de> parents: 40 diff changeset	79 '''
062ce001abd1 add backend method to create new projects Björn Ricks <bjoern.ricks@intevation.de> parents: 40 diff changeset	80
23 9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	81 DELETE_PROJECT_ENTRY = 'DELETE FROM entries WHERE id = %i'
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	82
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	83 MOVE_ENTRY = 'UPDATE entries SET project_id = ? WHERE id = ?'
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	84
68 a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	85 UPDATE_ENTRY = 'UPDATE entries SET description = ? WHERE id = ?'
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	86
23 9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	87 logger = logging.getLogger()
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	88
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	89 class Backend:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	90
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	91 def __init__(self, database = DEFAULT_DATABASE):
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	92 self.database = database
40 e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	93 self.ensure_exists()
23 9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	94 self.con = db.connect(database,
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	95 detect_types=db.PARSE_DECLTYPES \|
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	96 db.PARSE_COLNAMES)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	97 self.con.text_factory = lambda x: unicode(x, "utf-8", "ignore")
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	98
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	99
40 e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	100 def ensure_exists(self):
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	101 """ Creates the database file if it does not exist. """
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	102 if os.path.isfile(self.database): return
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	103
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	104 con, cur = None, None
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	105 try:
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	106 con = db.connect(self.database);
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	107 cur = con.cursor()
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	108 try:
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	109 for sql in CREATE_TABLES:
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	110 cur.execute(sql)
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	111 con.commit()
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	112 except:
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	113 con.rollback()
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	114 raise
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	115 finally:
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	116 if cur: cur.close()
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	117 if con: con.close()
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	118
e4759cc8f5e7 Create database file if it does not exist. Sascha L. Teichmann <teichmann@intevation.de> parents: 33 diff changeset	119
23 9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	120 def load_projects(self):
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	121 """ Loads active projects from database and returns them as array """
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	122 logger.debug("load active projects from database.")
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	123 cur = None
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	124 try :
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	125 cur = self.con.cursor()
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	126 cur.execute(LOAD_ACTIVE_PROJECTS)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	127
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	128 projects = []
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	129 while True:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	130 row = cur.fetchone()
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	131
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	132 if not row: break
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	133 proj = Project(*row)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	134 proj.entries = self.load_entries(proj.id)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	135 projects.append(proj)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	136
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	137 logger.info("found %i active projects." % len(projects))
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	138 return projects
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	139
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	140 finally:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	141 close(cur)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	142
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	143
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	144 def load_entries(self, project_id):
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	145 """ Loads all entries that belong to a specific project """
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	146 logger.debug("load entries that belong to project %s" % project_id)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	147 cur = None
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	148 try:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	149 cur = self.con.cursor()
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	150 cur.execute(LOAD_PROJECT_ENTRIES % project_id)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	151
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	152 entries = []
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	153 while True:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	154 try:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	155 row = cur.fetchone()
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	156
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	157 if not row: break
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	158 entries.append(Entry(*row))
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	159 except:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	160 logger.warn("found invalid entry.")
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	161
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	162 logger.debug("Found %i entries that belong to project '%i'"
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	163 % (len(entries), project_id))
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	164 return entries
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	165 finally:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	166 close(cur)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	167
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	168
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	169 def insert_project_entry(self, project, stop_time, desc):
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	170 if project is None: return
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	171 cur = None
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	172 try:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	173 cur = self.con.cursor()
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	174 cur.execute(INSERT_PROJECT_ENTRY, (
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	175 project.id, project.start, stop_time, desc))
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	176 self.con.commit()
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	177 logger.debug("Added new entry '%s' of project '%s' into db"
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	178 % (desc, project.desc))
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	179
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	180 project.entries = self.load_entries(project.id)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	181 finally:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	182 close(cur)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	183
52 9cb57c37369a fix insert new project sql statemenent and api Björn Ricks <bjoern.ricks@intevation.de> parents: 50 diff changeset	184 def insert_project(self, key, description):
9cb57c37369a fix insert new project sql statemenent and api Björn Ricks <bjoern.ricks@intevation.de> parents: 50 diff changeset	185 if key is None or description is None:
49 062ce001abd1 add backend method to create new projects Björn Ricks <bjoern.ricks@intevation.de> parents: 40 diff changeset	186 return
062ce001abd1 add backend method to create new projects Björn Ricks <bjoern.ricks@intevation.de> parents: 40 diff changeset	187 cur = None
50 9dbb6ee443a4 don't forget to save before commit :-/ BjÃ¶rn Ricks <bjoern.ricks@intevation.de> parents: 49 diff changeset	188 try:
49 062ce001abd1 add backend method to create new projects Björn Ricks <bjoern.ricks@intevation.de> parents: 40 diff changeset	189 cur = self.con.cursor()
52 9cb57c37369a fix insert new project sql statemenent and api Björn Ricks <bjoern.ricks@intevation.de> parents: 50 diff changeset	190 cur.execute(INSERT_PROJECT, (key, description))
49 062ce001abd1 add backend method to create new projects Björn Ricks <bjoern.ricks@intevation.de> parents: 40 diff changeset	191 self.con.commit()
52 9cb57c37369a fix insert new project sql statemenent and api Björn Ricks <bjoern.ricks@intevation.de> parents: 50 diff changeset	192 logger.debug("Added a new project '%s' into db" % description)
50 9dbb6ee443a4 don't forget to save before commit :-/ BjÃ¶rn Ricks <bjoern.ricks@intevation.de> parents: 49 diff changeset	193 finally:
9dbb6ee443a4 don't forget to save before commit :-/ BjÃ¶rn Ricks <bjoern.ricks@intevation.de> parents: 49 diff changeset	194 close(cur)
23 9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	195
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	196 def delete_entries(self, entries):
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	197 if entries is None: return
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	198
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	199 cur = None
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	200 try:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	201 cur = self.con.cursor()
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	202 for entry in entries:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	203 cur.execute(DELETE_PROJECT_ENTRY % entry.id)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	204 logger.debug("Deleted entry: %s (%d)" % (entry.desc, entry.id))
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	205 self.con.commit()
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	206 finally:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	207 close(cur)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	208
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	209
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	210 def move_entries(self, entries, new_project_id):
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	211 if entries is None or new_project_id is None: return
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	212
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	213 cur = None
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	214 try:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	215 cur = self.con.cursor()
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	216 for entry in entries:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	217 cur.execute(MOVE_ENTRY, (new_project_id, entry.id))
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	218 logger.debug("Moved entry '%s' (id=%d) to project with id %d."
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	219 % (entry.desc, entry.id, new_project_id))
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	220 self.con.commit()
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	221 finally:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	222 close(cur)
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	223
68 a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	224 def update_entry(self, entry):
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	225 if not entry:
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	226 return
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	227
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	228 cur = None
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	229 try:
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	230 cur = self.con.cursor()
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	231 cur.execute(UPDATE_ENTRY, (entry.desc, entry.id))
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	232 self.con.commit()
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	233 logger.debug("Updated entry: '%s' (%d)" % (entry.desc, entry.id))
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	234 finally:
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	235 close(cur)
a25dab413260 Add new method to update a description of an entry in the database backend Björn Ricks <bjoern.ricks@intevation.de> parents: 52 diff changeset	236
23 9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	237 def close(cur):
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	238 """ This function closes a database cursor if it is existing """
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	239 if cur:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	240 try: cur.close()
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	241 except:
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	242 logger.warn("could not close database cursor.")
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	243
9c4e8ba3c4fa Added a new implementation of 'getan' based on urwid, a python console user interface library. Ingo Weinzierl <ingo_weinzierl@web.de> parents: diff changeset	244 # vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8:

Mercurial > getan > getan

annotate getan/backend.py @ 68:a25dab413260