Pulling in skeleton changes
[quicknote] / src / speichern.py
1 #!/usr/bin/env python
2 # -*- coding: utf-8 -*-
3
4 """
5  Copyright (C) 2007 Christoph W├╝rstle
6
7 This program is free software; you can redistribute it and/or modify
8 it under the terms of the GNU General Public License version 2 as
9 published by the Free Software Foundation.
10 """
11
12
13 import sys
14 import os
15 import time
16 import sqlite3
17 import shelve
18 import logging
19
20
21 try:
22         _
23 except NameError:
24         _ = lambda x: x
25
26
27 _moduleLogger = logging.getLogger(__name__)
28
29
30 class Speichern(object):
31
32         def __init__(self):
33                 home_dir = os.path.expanduser('~')
34                 filename = os.path.join(home_dir, ".quicknote.dat")
35                 self.d = shelve.open(filename)
36                 self.openDB()
37
38         def speichereDirekt(self, schluessel, daten):
39                 self.d[schluessel] = daten
40                 _moduleLogger.info("speichereDirekt "+str(schluessel)+" "+str(daten)+" lesen: "+str(self.d[schluessel]))
41
42         def ladeDirekt(self, schluessel, default = ""):
43                 if (self.d.has_key(schluessel) == True):
44                         data = self.d[schluessel]
45                         return data
46                 else:
47                         return default
48
49         def speichereSQL(self, sql, tupel = None, commit = True, host = "self", log = True, pcdatum = None, rowid = ""):
50                 try:
51                         programSQLError = True
52                         if tupel is None:
53                                 self.cur.execute(sql)
54                         else:
55                                 self.cur.execute(sql, tupel)
56                         programSQLError = False
57
58                         if (log == True):
59                                 strtupel = []
60                                 if tupel is not None:
61                                         for t in tupel:
62                                                 strtupel.append(str(t))
63
64                                 if pcdatum is None:
65                                         pcdatum = int(time.time())
66                                 self.cur.execute("INSERT INTO logtable ( pcdatum, sql, param, host, rowid ) VALUES (?, ?, ?, ?, ?)", (pcdatum, sql, " <<Tren-ner>> ".join(strtupel), host, str(rowid) ))
67                         if commit:
68                                 self.conn.commit()
69
70                         return True
71                 except StandardError:
72                         s = str(sys.exc_info())
73                         if s.find(" already exists") == -1:
74                                 if (programSQLError == True):
75                                         _moduleLogger.error("speichereSQL-Exception "+str(sys.exc_info())+" "+str(sql)+" "+str(tupel))
76                                 else:
77                                         _moduleLogger.error("speichereSQL-Exception in Logging!!!! :"+str(sys.exc_info())+" "+str(sql)+" "+str(tupel))
78                         return False
79
80         def commitSQL(self):
81                 self.conn.commit()
82
83         def ladeSQL(self, sql, tupel = None):
84                 #print sql, tupel
85                 try:
86                         if tupel is None:
87                                 self.cur.execute(sql)
88                         else:
89                                 self.cur.execute(sql, tupel)
90                         return self.cur.fetchall()
91                 except StandardError:
92                         _moduleLogger.error("ladeSQL-Exception "+str(sys.exc_info())+" "+str(sql)+" "+str(tupel))
93                         return ()
94
95         def ladeHistory(self, sql_condition, param_condition):
96                 sql = "SELECT * FROM logtable WHERE sql LIKE '%"+str(sql_condition)+"%' AND param LIKE '%"+str(param_condition)+"%'"
97                 rows = self.ladeSQL(sql)
98                 #print rows 
99                 erg = []
100                 for row in rows:
101                         datum = time.strftime("%d.%m.%y %H:%M:%S", (time.localtime(row[1])))
102                         erg.append([row[1], datum, row[2], row[3], row[3].split(" <<Tren-ner>> ")])
103
104                 return erg
105
106         def openDB(self):
107                 try:
108                         self.cur.close()
109                 except StandardError:
110                         pass
111                 try:
112                         self.conn.close()
113                 except StandardError:
114                         pass
115
116                 db = self.ladeDirekt("datenbank")
117                 if db == "":
118                         home_dir = os.path.expanduser('~')
119
120                         #on hildon user not home-dir but /home/user/MyDocs
121                         if home_dir == "/home/user":
122                                 if os.path.exists(home_dir+os.sep+"MyDocs/"):
123                                         home_dir = home_dir+os.sep+"MyDocs/"
124                         db = os.path.join(home_dir, "quicknote.s3db")
125
126                 self.conn = sqlite3.connect(db)
127                 self.cur = self.conn.cursor()
128                 try:
129                         sql = "CREATE TABLE logtable (id INTEGER PRIMARY KEY AUTOINCREMENT, pcdatum INTEGER , sql TEXT, param TEXT, host TEXT, rowid TEXT)"
130                         self.cur.execute(sql)
131                         self.conn.commit()
132                 except StandardError:
133                         pass
134
135                 #Add rowid line (not in old versions included)
136                 try:
137                         sql = "ALTER TABLE logtable ADD rowid TEXT"
138                         self.cur.execute(sql)
139                         self.conn.commit()
140                 except StandardError:
141                         pass
142
143                 #Create notes table
144                 try:
145                         sql = "CREATE TABLE notes (noteid TEXT, pcdatum INTEGER , category TEXT, note TEXT)"
146                         self.cur.execute(sql)
147                         self.conn.commit()
148                 except StandardError:
149                         pass
150
151         def saveNote(self, noteid, note, category, pcdatum = None):
152                 if category == "%":
153                         category = ""
154                 sql = "SELECT noteid, pcdatum, category, note FROM notes WHERE noteid = ?"
155                 rows = self.ladeSQL(sql, (noteid, ))
156
157                 if rows is None or len(rows) == 0:
158                         sql = "INSERT INTO notes (noteid, pcdatum, category, note) VALUES (?, ?, ?, ?)"
159                         if pcdatum is None:
160                                 pcdatum = int(time.time())
161                         self.speichereSQL(sql, (noteid, pcdatum, category, note), rowid = noteid)
162                 else:
163                         sql = "UPDATE notes SET category = ?, note = ?, pcdatum = ? WHERE noteid = ?"
164                         self.speichereSQL(sql, (category, note, str(int(time.time())), noteid), rowid = noteid)
165
166         def loadNote(self, noteid):
167                 if noteid is None or str(noteid) == "":
168                         return (None, None, None, None)
169                 sql = "SELECT noteid, pcdatum, category, note FROM notes WHERE noteid = ?"
170                 rows = self.ladeSQL(sql, (noteid, ))
171                 if rows is None or len(rows) == 0:
172                         return None
173                 else:
174                         noteid, pcdatum, category, note = rows[0]
175                         return (noteid, pcdatum, category, note)
176
177         def delNote(self, noteid):
178                 sql = "DELETE FROM notes WHERE noteid = ?"
179                 self.speichereSQL(sql, (noteid, ), rowid = noteid)
180
181         def searchNotes(self, searchstring, category):
182                 sql = "SELECT noteid, category, note FROM notes WHERE note like ? AND category like ? ORDER BY note"
183                 rows = self.ladeSQL(sql, ("%"+searchstring+"%", category))
184                 if rows is None or len(rows) == 0:
185                         return None
186                 else:
187                         return rows
188
189         def getNoteHistory(self, noteid):
190                 return self.ladeHistory("UPDATE notes ", noteid)
191
192         def close(self):
193                 try:
194                         self.d.close()
195                 except StandardError:
196                         pass
197                 try:
198                         self.cur.close()
199                 except StandardError:
200                         pass
201                 try:
202                         self.conn.close()
203                 except StandardError:
204                         pass
205                 _moduleLogger.info("Alle Data saved")
206
207         def __del__(self):
208                 self.close()