Revision Log

storesqlite.py

Revision 161 (checked in by fumanchu, 3 years ago)	Minor SQLite fixes.
Property svn:eol-style set to `native`

Line
1	import os
2
3	import dejavu
4	from dejavu import storage, logic
5	from dejavu.storage import db
6
7	# Use _sqlite directly to avoid all of the DB-API overhead.
8	# This will import the "old API for SQLite 3.x", using e.g. pysqlite 1.1.7
9	import _sqlite
10	_version = storage.Version(_sqlite.sqlite_version())
11
12	# ESCAPE keyword was added Nov 2004, 1 month after 3.0.8 release.
13	_escape_support = (_version > storage.Version([3, 0, 8]))
14	if not _escape_support:
15	_escape_warning = ("Version %s of sqlite does not support "
16	"wildcard literals." % _version)
17	import warnings
18	warnings.warn(_escape_warning, dejavu.StorageWarning)
19
20	_add_column_support = (_version >= storage.Version([3, 2, 0]))
21	_rename_table_support = (_version >= storage.Version([3, 1, 0]))
22
23
24	class AdapterToSQLite(db.AdapterToSQL):
25
26	like_escapes = [("%", "\%"), ("_", "\_")]
27
28	bool_true = "1"
29	bool_false = "0"
30
31	def coerce_bool(self, value):
32	if value:
33	return '1'
34	return '0'
35
36
37	class AdapterFromSQLite(db.AdapterFromDB):
38
39	def coerce_bool(self, value, coltype):
40	# sqlite 2 will return a string, either '0' or '1'.
41	if isinstance(value, basestring):
42	return (value == '1')
43	# sqlite 3 will return an int.
44	return bool(value)
45
46
47	class SQLiteDecompiler(db.SQLDecompiler):
48
49	def attr_startswith(self, tos, arg):
50	if _escape_support:
51	return tos + " LIKE '" + self.adapter.escape_like(arg) + r"%' ESCAPE '\'"
52	else:
53	if "%" in arg or "_" in arg:
54	raise ValueError(_escape_warning)
55	else:
56	return tos + " LIKE '" + arg.strip(r"'\"") + "%'"
57
58	def attr_endswith(self, tos, arg):
59	if _escape_support:
60	return tos + " LIKE '%" + self.adapter.escape_like(arg) + r"' ESCAPE '\'"
61	else:
62	if "%" in arg or "_" in arg:
63	raise ValueError(_escape_warning)
64	else:
65	return tos + " LIKE '%" + arg.strip(r"'\"") + "'"
66
67	def containedby(self, op1, op2):
68	if isinstance(op1, db.ConstWrapper):
69	# Looking for text in a field. Use Like (reverse terms).
70	if _escape_support:
71	return op2 + " LIKE '%" + self.adapter.escape_like(op1) + r"%' ESCAPE '\'"
72	else:
73	if "%" in op1 or "_" in op1:
74	raise ValueError(_escape_warning)
75	else:
76	return op2 + " LIKE '%" + op1.strip(r"'\"") + r"%'"
77	else:
78	# Looking for field in (a, b, c)
79	atoms = [self.adapter.coerce(x) for x in op2.basevalue]
80	return op1 + " IN (" + ", ".join(atoms) + ")"
81
82	def dejavu_icontainedby(self, op1, op2):
83	if isinstance(op1, db.ConstWrapper):
84	# Looking for text in a field. Use Like (reverse terms).
85	if _escape_support:
86	return ("LOWER(" + op2 + ") LIKE '%" +
87	self.adapter.escape_like(op1).lower() + r"%' ESCAPE '\'")
88	else:
89	if "%" in op1 or "_" in op1:
90	raise ValueError(_escape_warning)
91	else:
92	return ("LOWER(" + op2 + ") LIKE '%" +
93	op1.strip("'\"").lower() + r"%'")
94	else:
95	# Looking for field in (a, b, c).
96	# Force all args to lowercase for case-insensitive comparison.
97	atoms = [self.adapter.coerce(x).lower() for x in op2.basevalue]
98	return "LOWER(%s) IN (%s)" % (op1, ", ".join(atoms))
99
100	def dejavu_icontains(self, x, y):
101	return self.dejavu_icontainedby(y, x)
102
103	def dejavu_istartswith(self, x, y):
104	if _escape_support:
105	return ("LOWER(" + x + ") LIKE '" + self.adapter.escape_like(y)
106	+ r"%' ESCAPE '\'")
107	else:
108	if "%" in y or "_" in y:
109	raise ValueError(_escape_warning)
110	else:
111	return "LOWER(" + x + ") LIKE '" + y.strip("'\"") + r"%'"
112
113	def dejavu_iendswith(self, x, y):
114	if _escape_support:
115	return ("LOWER(" + x + ") LIKE '%" + self.adapter.escape_like(y)
116	+ r"%' ESCAPE '\'")
117	else:
118	if "%" in y or "_" in y:
119	raise ValueError(_escape_warning)
120	else:
121	return "LOWER(" + x + ") LIKE '%" + y.strip("'\"") + r"%'"
122
123	def dejavu_now(self):
124	self.imperfect = True
125	return db.cannot_represent
126
127	dejavu_today = dejavu_now
128
129	def dejavu_year(self, x):
130	self.imperfect = True
131	return db.cannot_represent
132
133
134	class StorageManagerSQLite(db.StorageManagerDB):
135	"""StoreManager to save and retrieve Units via _sqlite."""
136
137	sql_name_max_length = 0
138	decompiler = SQLiteDecompiler
139	toAdapter = AdapterToSQLite()
140	fromAdapter = AdapterFromSQLite()
141
142	def __init__(self, name, arena, allOptions={}):
143	db.StorageManagerDB.__init__(self, name, arena, allOptions)
144
145	dbfile = allOptions.get(u'Database', '')
146	if not os.path.isabs(dbfile):
147	dbfile = os.path.join(os.getcwd(), dbfile)
148	self.database = dbfile
149
150	self.mode = int(allOptions.get(u'Mode', '0755'), 8)
151
152	def sql_name(self, name, quoted=True):
153	"""sql_name(name, quoted=True) -> return name as a legal SQL identifier.
154
155	From the SQLite docs:
156	Keywords can be used as identifiers in three ways:
157	'keyword' Interpreted as a literal string if it occurs in a
158	legal string context, otherwise as an identifier.
159	"keyword" Interpreted as an identifier if it matches a known
160	identifier and occurs in a legal identifier context,
161	otherwise as a string.
162	[keyword] Always interpreted as an identifier.
163
164	...we'll use the third option (square brackets).
165	"""
166	if quoted:
167	name = "[" + name + "]"
168	return name
169
170	def _get_conn(self):
171	# SQLite should create the DB if missing.
172	return _sqlite.connect(self.database, self.mode)
173
174	def version(self):
175	return "SQLite Version: %s" % _version
176
177	def execute(self, query, conn=None):
178	try:
179	if conn is None:
180	conn = self.connection()
181	self.arena.log(query, dejavu.LOGSQL)
182	return conn.execute(query.encode('utf8'))
183	# ^^^^^^^
184	except Exception, x:
185	x.args += (query,)
186	# Dereference the connection so that release() is called back.
187	conn = None
188	raise
189
190	def _join(self, unitjoin):
191	on_clauses = []
192
193	cls1, cls2 = unitjoin.class1, unitjoin.class2
194	if isinstance(cls1, dejavu.UnitJoin):
195	name1, oc = self._join(cls1)
196	on_clauses.extend(oc)
197	classlist1 = iter(cls1)
198	else:
199	# cls1 is a Unit class wrapper.
200	name1 = cls1.joinname
201	classlist1 = [cls1]
202
203	if isinstance(cls2, dejavu.UnitJoin):
204	name2, oc = self._join(cls2)
205	on_clauses.extend(oc)
206	classlist2 = iter(cls2)
207	else:
208	# cls2 is a Unit class wrapper.
209	name2 = cls2.joinname
210	classlist2 = [cls2]
211
212	if unitjoin.leftbiased is None:
213	j = "%s INNER JOIN %s" % (name1, name2)
214	elif unitjoin.leftbiased is True:
215	j = "%s LEFT JOIN %s" % (name1, name2)
216	else:
217	# My version (3.0.8) of SQLite says:
218	# "RIGHT and FULL OUTER JOINs are not currently supported".
219	j = "%s LEFT JOIN %s" % (name2, name1)
220
221	# Find an association between the two halves.
222	ua = None
223	for clsA in classlist1:
224	ua = clsA.association(classlist2)
225	if ua:
226	ua, nearClass, farClass = ua
227	break
228	if ua is None:
229	msg = ("No association found between %s and %s." % (name1, name2))
230	raise dejavu.AssociationError(msg)
231	near = '%s.%s' % (nearClass, self.column_name(nearClass, ua.nearKey))
232	far = '%s.%s' % (farClass, self.column_name(farClass, ua.farKey))
233
234	on_clauses.append("%s = %s" % (near, far))
235	return j, on_clauses
236
237	def join(self, unitjoin):
238	# SQLite doesn't do nested JOINs, but instead applies them
239	# in order. Therefore, we need a single ON-clause at the
240	# end of the list of tables. For example:
241	# "From a LEFT JOIN b LEFT JOIN c ON a.ID = b.ID AND b.Name = c.Name
242	joins, on_clauses = self._join(unitjoin)
243	return joins + " ON " + " AND ".join(on_clauses)
244
245	# Schemas #
246
247	create_database = _get_conn
248
249	def drop_database(self):
250	self.shutdown()
251	# This should accept relative or absolute paths
252	os.remove(self.database)
253
254	def create_storage(self, cls):
255	clsname = cls.__name__
256	tablename = self.table_name(clsname)
257
258	# SQLite is typeless.
259	fields = [self.column_name(clsname, key) for key in cls.properties()]
260
261	self.execute(u'CREATE TABLE %s (%s);' % (tablename, ", ".join(fields)))
262	for index in cls.indices():
263	i = self.table_name("i" + clsname + index)
264	self.execute(u'CREATE INDEX %s ON %s (%s);' %
265	(i, tablename, self.column_name(clsname, index)))
266
267	def add_property(self, cls, name):
268	clsname = cls.__name__
269
270	if _add_column_support:
271	self.execute("ALTER TABLE %s ADD COLUMN %s;" %
272	(self.table_name(clsname),
273	self.column_name(clsname, name)))
274	else:
275	tablename = self.table_name(clsname, quoted=False)
276
277	# Create a temporary table with the new schema (no indices).
278	# The schema should already be changed in the model layer.
279	props = list(cls.properties())
280	fields = ", ".join([self.column_name(clsname, key) for key in props])
281	self.execute("CREATE TABLE [temp_%s] (%s);" % (tablename, fields))
282	oldfields = []
283	for key in props:
284	if key == name:
285	oldfields.append(self.toAdapter.coerce(None))
286	else:
287	oldfields.append(self.column_name(clsname, key))
288	self.execute("INSERT INTO [temp_%s] SELECT %s FROM [%s];" %
289	(tablename, ", ".join(oldfields), tablename))
290
291	# Drop and re-create the old table.
292	self.execute("DROP TABLE [%s];" % tablename)
293	self.create_storage(cls)
294	self.execute("INSERT INTO [%s] SELECT * FROM [temp_%s];" %
295	(tablename, tablename))
296	self.execute("DROP TABLE [temp_%s];" % tablename)
297
298	def drop_property(self, cls, name):
299	clsname = cls.__name__
300	tablename = self.table_name(clsname, quoted=False)
301
302	# Create a temporary table with the new schema (no indices).
303	# The schema should already be changed in the model layer.
304	fields = ", ".join([self.column_name(clsname, key)
305	for key in cls.properties()])
306	self.execute("CREATE TABLE [temp_%s] (%s);" % (tablename, fields))
307	self.execute("INSERT INTO [temp_%s] SELECT %s FROM [%s];" %
308	(tablename, fields, tablename))
309
310	# Drop and re-create the old table.
311	self.execute("DROP TABLE [%s];" % tablename)
312	self.create_storage(cls)
313	self.execute("INSERT INTO [%s] SELECT * FROM [temp_%s];" %
314	(tablename, tablename))
315	self.execute("DROP TABLE [temp_%s];" % tablename)
316
317	def rename_property(self, cls, oldname, newname):
318	clsname = cls.__name__
319	tablename = self.table_name(clsname, quoted=False)
320
321	# Create a temporary table with the new schema (no indices).
322	# The schema should already be changed in the model layer.
323	props = list(cls.properties())
324	fields = ", ".join([self.column_name(clsname, key) for key in props])
325	self.execute("CREATE TABLE [temp_%s] (%s);" % (tablename, fields))
326	oldfields = []
327	for key in props:
328	if key == newname:
329	oldfields.append("%s AS %s" % (self.column_name(clsname, oldname),
330	self.column_name(clsname, newname)))
331	else:
332	oldfields.append(self.column_name(clsname, key))
333	self.execute("INSERT INTO [temp_%s] SELECT %s FROM [%s];" %
334	(tablename, ", ".join(oldfields), tablename))
335
336	# Drop and re-create the old table.
337	self.execute("DROP TABLE [%s];" % tablename)
338	self.create_storage(cls)
339	self.execute("INSERT INTO [%s] SELECT * FROM [temp_%s];" %
340	(tablename, tablename))
341	self.execute("DROP TABLE [temp_%s];" % tablename)

Note: See TracBrowser for help on using the browser.

root/tags/1.4.0/storage/storesqlite.py

Download in other formats: