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