1 """a L{Collection} based on L{sqlite3}"""
2 import os.path
3 from itertools import izip, imap
4 from operator import itemgetter
5 from functools import wraps
6 import sqlite3
7
8 import TableMaker
9
10 from grassyknoll.collection import Collection
11 from grassyknoll.collection import DelegateCollection
12 import grassyknoll.lib.meta
13
14
16 @wraps(f)
17 def wrapper(self, *args, **kwargs):
18 try:
19 return f(self, *args, **kwargs)
20 finally:
21 self.connection.rollback()
22 return wrapper
23
25 @wraps(f)
26 def wrapper(self, *args, **kwargs):
27 try:
28 ret=f(self, *args, **kwargs)
29 except:
30 self.connection.rollback()
31 raise
32 else:
33 self.connection.commit()
34 return ret
35 return wrapper
36
38 """return a dict from a row and a cursor description"""
39 assert len(row)==len(description)
40 return dict(izip((d[0] for d in description), row))
41
43 """given a dict, return a list of names and values"""
44
45 return zip(*sorted(d.iteritems()))
46
47
48
50 """code common to both readers & writers"""
51
52 logger=grassyknoll.lib.meta.AutoLogger()
53
55 """
56 @arg create: create the sqlite file if it doesn't exist
57 @type create: bool
58 """
59 self.filename=filename
60 assert isinstance(table, TableMaker.Table)
61 self.table=table
62
64 sql = 'SELECT count(*) FROM %s' % self.table.name
65 row, = self.connection.execute(sql).fetchall()
66 return row[0]
67
69
70
71
72
73
74
75 self.connection=sqlite3.connect(filename,
76 detect_types=sqlite3.PARSE_DECLTYPES,
77 cached_statements=cached_statements,
78 check_same_thread=False)
79
81 if synchronous is not None:
82 self.connection.execute("PRAGMA synchronous = %s"%synchronous)
83 if cache_size is not None:
84 self.connection.execute("PRAGMA cache_size = %d"%cache_size)
85
87 self.connection.close()
88 self.logger.info("Closed")
89
91
92 - def __init__(self, filename, table, cache_size=None,
93 synchronous=None, cached_statements=100):
94 """
95 @arg create: create the sqlite file if it doesn't exist
96 @type create: bool
97 """
98 super(SqliteCollectionReader, self).__init__(filename, table)
99
100 file_exists=os.path.exists(filename)
101 assert file_exists
102 self._make_connection(filename, cached_statements)
103 self.logger.info("Opened %s", filename)
104 self._config_connection(synchronous, cache_size)
105 self.connection.commit()
106
107 @Collection.addMetaData
108 @rollback
110 sql='SELECT __id__ FROM %s'%self.table.name
111 ids=[r[0] for r in self.connection.execute(sql).fetchall()]
112 return Collection.CollectionIds(ids)
113
114 @Collection.addMetaData
115 @rollback
131
132 @Collection.addMetaData
133 @rollback
135 """find results where all fields match.
136
137 C{kwargs} is interepreted as field name => value to search for.
138
139 XXX this docstring sucks. I'm tired.
140
141 XXX injection attack!?! Maybe not, as the only unsafe SQL construction
142 is from keyword arg names. Convienently, Python should disallow any
143 possible injections.
144
145 @returns: matching results
146 @rtype: L{CollectionResultSet}
147
148 """
149
150
151 l=kwargs.items()
152
153 where=" AND ".join("%s = ?"%k for k in imap(itemgetter(0), l))
154
155 sql="SELECT * FROM %s WHERE %s"%(self.table.name, where)
156
157 cursor=self.connection.execute(sql, map(itemgetter(1), l))
158 rows=cursor.fetchall()
159
160 results=[Collection.CollectionResult(dictify(r, cursor.description))
161 for r in rows]
162 return Collection.CollectionResultSet(results, {'count':len(results)})
163
164
166
167 - def __init__(self, filename, table, cache_size=None,
168 synchronous=None, cached_statements=100,
169 create=False, page_size=4096):
170 """
171 @arg create: create the sqlite file if it doesn't exist
172 @type create: bool
173 """
174 super(SqliteCollectionWriter, self).__init__(filename, table)
175
176 file_exists=os.path.exists(filename)
177 if create:
178 assert not file_exists, "sqlite file exists: %s"%filename
179 dirname = os.path.dirname(filename)
180 if dirname and not os.path.exists(dirname):
181 os.makedirs(dirname)
182 self._make_connection(filename, cached_statements)
183
184 if page_size is not None:
185 self.connection.execute("PRAGMA page_size=%d"%page_size)
186 self.logger.warn("Created %s", filename)
187 else:
188 assert file_exists
189 self._make_connection(filename, cached_statements)
190 self.logger.info("Opened %s", filename)
191
192 self._config_connection(synchronous, cache_size)
193
194 if create:
195 self.table.create(self.connection)
196 self.logger.info("Create table %s", table.name)
197 self.connection.commit()
198
199
200 @Collection.addMetaData
201 @tryCommit
204
206 assert ids
207 qmarks=", ".join("?" for _ in xrange(len(ids)))
208 sql="DELETE FROM %s WHERE __id__ IN (%s)"%(self.table.name, qmarks)
209 self.connection.execute(sql, ids)
210 return Collection.CollectionIds(ids)
211
212 @Collection.addMetaData
213 @tryCommit
215 assert docs
216
217 self._delete([doc.id for doc in docs])
218
219 cols=undictify(docs[0])[0]
220 colnames=", ".join(cols)
221 qmarks=", ".join("?" for _ in xrange(len(cols)))
222
223 sql=u"INSERT INTO %s (%s) VALUES (%s)"%(self.table.name, colnames, qmarks)
224 self.connection.executemany(sql, [undictify(d)[1] for d in docs])
225 return Collection.CollectionIds([d.id for d in docs])
226
228
229 - def __init__(self, filename, table, create=False):
234
237
239 "Remove database file."
240 os.remove(self.filename)
241