Revision Log

storeado.py

Revision 22 (checked in by fumanchu, 9 years ago)	1. Moved xray, recur into dejavu. 2. Doc updates.

Line
1	import sys
2	# Put COM in free-threaded mode
3	sys.coinit_flags = 0
4
5	import win32com.client
6	import pywintypes
7	import pythoncom
8	import threading
9	import datetime
10	try:
11	import cPickle as pickle
12	except ImportError:
13	import pickle
14	from types import FunctionType
15
16	try:
17	import fixedpoint
18	except ImportError:
19	pass
20
21	import dejavu
22	from dejavu import storage, codewalk, logic, recur
23
24	adOpenForwardOnly = 0
25	adOpenKeyset = 1
26	adOpenDynamic = 2
27	adOpenStatic = 3
28
29	adLockReadOnly = 1
30	adLockPessimistic = 2
31	adLockOptimistic = 3
32	adLockBatchOptimistic = 4
33
34	adModeShareExclusive = 12
35
36	adStateClosed = 0
37	adStateOpen = 1
38	adStateConnecting = 2
39	adStateExecuting = 4
40	adStateFetching = 8
41
42	# 12/30/1899, the zero-Date for ADO = 693594
43	zeroHour = datetime.date(1899, 12, 30).toordinal()
44
45
46	def time_from_com(com_date):
47	"""Return a valid (day, datetime.time) from a COM date or time object."""
48	hour, mins = divmod(86400 * (float(com_date) % 1), 3600)
49	mins, sec = divmod(mins, 60)
50	# Must do both int() and round() or we'll be up to 1 second off.
51	hour = int(round(hour))
52	mins = int(round(mins))
53	sec = int(round(sec))
54	return recur.sane_time(0, hour, mins, sec)
55
56
57	class AdapterFromADO(storage.Adapter):
58	"""Coerce incoming values from ADO to Dejavu datatypes."""
59	def __init__(self, unit=None):
60	self.unit = unit
61
62	def consume(self, key, value):
63	expectedType = self.unit.__class__.property_type(key)
64	value = self.coerce(value, expectedType)
65	# Set the attribute directly to avoid __set__ overhead.
66	self.unit._properties[key] = value
67
68	def pickle(self, value):
69	aType, value = value
70	if value is None:
71	return None
72	else:
73	# Coerce to str for pickle.loads restriction.
74	value = str(value)
75	return pickle.loads(value)
76
77	def coerce_datetime_datetime(self, value):
78	# Illegal Date/Time values will crash the
79	# app when using value.Format(). Therefore,
80	# grab the value and figure the date ourselves.
81	# Use 1-second resolution only.
82	aType, value = value
83	if value is None:
84	return None
85	elif isinstance(value, basestring):
86	return datetime.datetime(int(value[0:4]), int(value[4:6]),
87	int(value[6:8]))
88	else:
89	# For some reason, we need both float and int.
90	aDate = datetime.date.fromordinal(int(float(value)) + zeroHour)
91	day, aTime = time_from_com(value)
92	return datetime.datetime.combine(aDate, aTime)
93
94	def coerce_datetime_date(self, value):
95	# See coerce_datetime
96	aType, value = value
97	if value is None:
98	return None
99	elif isinstance(value, basestring):
100	return datetime.date(int(value[0:4]), int(value[4:6]),
101	int(value[6:8]))
102	else:
103	return datetime.date.fromordinal(int(float(value)) + zeroHour)
104
105	def coerce_datetime_time(self, value):
106	# See coerce_datetime
107	aType, value = value
108	if value is None:
109	return None
110	else:
111	day, aTime = time_from_com(value)
112	return aTime
113
114	coerce_dict = pickle
115
116	def coerce_fixedpoint_FixedPoint(self, value):
117	aType, value = value
118	if value is None:
119	return None
120	if aType == 0x06:
121	# Currency
122	value = value[1] / 10000.0
123	return fixedpoint.FixedPoint(value)
124
125	def coerce_float(self, value):
126	aType, value = value
127	if value is None:
128	return None
129	if aType == 0x06:
130	# Currency
131	value = value[1] / 10000.0
132	return float(value)
133
134	def coerce_int(self, value):
135	aType, value = value
136	if value is None:
137	return None
138	if aType == 0x0b:
139	# Boolean
140	return value != 0
141	return int(value)
142
143	coerce_bool = coerce_int
144	coerce_list = pickle
145
146	def coerce_long(self, value):
147	aType, value = value
148	if value is None:
149	return None
150	return long(value)
151
152	def coerce_str(self, value):
153	aType, value = value
154	if value is None:
155	return None
156	return str(value)
157
158	coerce_tuple = pickle
159
160	def coerce_unicode(self, value):
161	aType, value = value
162	if value is None:
163	return None
164	if isinstance(value, unicode):
165	# For some reason, inValue is already a unicode object.
166	return value
167	if isinstance(value, str):
168	try:
169	return unicode(value, "ISO-8859-1")
170	except UnicodeError:
171	raise StandardError(type(value))
172	return unicode(value)
173
174
175
176	class AdapterToADOFields(storage.Adapter):
177	"""Coerce outgoing values from Dejavu datatypes to ADO.Field types."""
178
179	def noop(self, value):
180	return value
181
182	def coerce_bool(self, value):
183	if value:
184	return True
185	return False
186
187	def coerce_datetime_datetime(self, value):
188	if value is None:
189	return None
190	return self.coerce_datetime_date(value) + self.coerce_datetime_time(value)
191
192	def coerce_datetime_date(self, value):
193	if value is None:
194	return None
195	return value.toordinal() - zeroHour
196
197	def coerce_datetime_time(self, value):
198	if value is None:
199	return None
200	return ((value.second + (value.minute * 60) + (value.hour * 3600))
201	/ 86400.0)
202
203	def pickle(self, value):
204	# We must not use a pickle format other than 0, because binary
205	# strings are not safe for all DB string fields.
206	return pickle.dumps(value)
207
208	coerce_dict = pickle
209
210	def coerce_fixedpoint_FixedPoint(self, value):
211	if value is None:
212	return None
213	return float(value)
214
215	coerce_float = noop
216	coerce_int = noop
217
218	coerce_list = pickle
219
220	coerce_long = noop
221	coerce_str = noop
222
223	coerce_tuple = pickle
224
225	coerce_unicode = noop
226
227
228	class AdapterToADOSQL(storage.Adapter):
229	"""Coerce Expression constants to ADO SQL."""
230
231	def tostr(self, value):
232	return str(value)
233
234	def coerce_NoneType(self, value):
235	return "Null"
236
237	def coerce_bool(self, value):
238	if value:
239	return 'True'
240	return 'False'
241
242	def coerce_datetime_datetime(self, value):
243	return (u'#%s/%s/%s %02d:%02d:%02d#' %
244	(value.month, value.day, value.year,
245	value.hour, value.minute, value.second))
246
247	def coerce_datetime_date(self, value):
248	return u'#%s/%s/%s#' % (value.month, value.day, value.year)
249
250	def coerce_datetime_time(self, value):
251	return u'#%02d:%02d:%02d#' % (value.hour, value.minute, value.second)
252
253	def coerce_datetime_timedelta(self, value):
254	float_val = value.days + (value.seconds / 86400.0)
255	return repr(float_val)
256
257	coerce_fixedpoint_FixedPoint = tostr
258	coerce_float = tostr
259	coerce_int = tostr
260
261	def coerce_list(self, value):
262	return "(" + ", ".join([self.coerce(x) for x in value]) + ")"
263
264	coerce_long = tostr
265
266	def coerce_str(self, value):
267	value = value.replace(u"'", u"''")
268	value = value.replace("%", "[%]")
269	value = value.replace("_", "[_]")
270	return "'" + value + "'"
271
272	coerce_tuple = coerce_list
273
274	coerce_unicode = coerce_str
275
276
277	def icontainedby(op1, op2, notin=False):
278	# This test doesn't work right, now that we use lists as
279	# well as tuples with IN. Need a way to mark field refs.
280	if op2.startswith("[") and op2.endswith("]"):
281	# Looking for text in a field. Use Like (reverse terms).
282	value = op2 + " Like '%" + op1[1:-1] + "%'"
283	else:
284	# Looking for field in (a, b, c)
285	value = op1 + " in " + op2
286	if notin:
287	value = "not " + value
288	return value
289
290
291	class ADOSQLDecompiler(codewalk.LambdaDecompiler):
292	"""ADOSQLDecompiler(store, unitClass, expr, adapter=AdapterToADOSQL()).
293
294	Produce SQL from a supplied Expression object, with a lambda of the form:
295	lambda x, **kw: ...
296
297	Attributes of x (or whatever the name of the first argument is) will be
298	mapped to table columns. Keyword arguments should be bound to the
299	Expression before calling this decompiler.
300	"""
301
302	sql_cmp_op = ('<', '<=', '=', '!=', '>', '>=', 'in', 'not in')
303	functions = {dejavu.icontains: lambda x, y: x + " Like '%" + y[1:-1] + "%'",
304	dejavu.icontainedby: icontainedby,
305	dejavu.istartswith: lambda x, y: x + " Like '" + y[1:-1] + "%'",
306	dejavu.iendswith: lambda x, y: x + " Like '%" + y[1:-1] + "'",
307	dejavu.ieq: lambda x, y: x + " = " + y,
308	dejavu.now: lambda: "getdate()",
309	dejavu.today: lambda: "DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)",
310	dejavu.year: lambda x: "YEAR(" + x + ")",
311	}
312
313	def __init__(self, store, unitClass, expr, adapter=AdapterToADOSQL()):
314	self.store = store
315	self.unitClass = unitClass
316	self.expr = expr
317	self.adapter = adapter
318	obj = expr.func
319	codewalk.LambdaDecompiler.__init__(self, obj)
320
321	def code(self):
322	self.imperfect = False
323	self.walk()
324	result = self.stack[0]
325	if result is None:
326	result = 'True'
327	return result, self.imperfect
328
329	def visit_target(self, terms):
330	"""A target is an AND or OR test."""
331	comp = self.stack.pop()
332	while terms:
333	term, operation = terms.pop()
334	# All this checking of None is done so that a function
335	# (like dejavu.iscurrentweek) can be labeled imperfect--
336	# all Units (which match the rest of the Expression)
337	# will be recalled. They can then be compared in
338	# expr.evaluate(unit).
339	if comp is None:
340	if term is not None:
341	comp = term
342	else:
343	if term is not None:
344	comp = "(%s) %s (%s)" % (term, operation, comp)
345	self.stack.append(comp)
346
347	def visit_LOAD_DEREF(self, lo, hi):
348	raise ValueError("Illegal reference found in %s." % self.expr)
349
350	def visit_LOAD_GLOBAL(self, lo, hi):
351	raise ValueError("Illegal global found in %s." % self.expr)
352
353	def visit_LOAD_FAST(self, lo, hi):
354	pass
355
356	def visit_LOAD_ATTR(self, lo, hi):
357	name = self.co_names[lo + (hi << 8)]
358	self.stack.append("[%s%s].[%s]" %
359	(self.store.prefix, self.unitClass.__name__, name))
360
361	def visit_LOAD_CONST(self, lo, hi):
362	val = self.co_consts[lo + (hi << 8)]
363	# Some constants are function or class objects,
364	# which should not be coerced.
365	no_coerce = (FunctionType, type)
366	if isinstance(val, no_coerce):
367	pass
368	elif isinstance(val, type(len)):
369	val = str(val)
370	else:
371	val = self.adapter.coerce(val)
372	self.stack.append(val)
373
374	def visit_BUILD_TUPLE(self, lo, hi):
375	terms = ", ".join([self.stack.pop() for i in range(lo + hi << 8)])
376	self.stack.append("(" + terms + ")")
377
378	def visit_BUILD_LIST(self, lo, hi):
379	self.visit_BUILD_TUPLE(lo, hi)
380
381	def visit_CALL_FUNCTION(self, lo, hi):
382	kwargs = {}
383	for i in range(hi):
384	val = self.stack.pop()
385	key = self.stack.pop()
386	kwargs[key] = val
387	kwargs = [k + "=" + v for k, v in kwargs.iteritems()]
388
389	args = []
390	for i in range(lo):
391	arg = self.stack.pop()
392	args.append(arg)
393	args.reverse()
394
395	if kwargs:
396	args += kwargs
397
398	func = self.stack.pop()
399
400	# Handle function objects.
401	if func in self.functions:
402	self.stack.append(self.functions[func](*args))
403	else:
404	if isinstance(func, basestring):
405	if func.endswith("[startswith]"):
406	self.stack[-1] = self.stack[-1] + " Like '" + args[0][1:-1] + "%'"
407	self.imperfect = True
408	return
409	elif func.endswith("[endswith]"):
410	self.stack[-1] = self.stack[-1] + " Like '%" + args[0][1:-1] + "'"
411	self.imperfect = True
412	return
413	elif func == '<built-in function len>':
414	self.stack.append("Len(" + args[0] + ")")
415	return
416	else:
417	if self.stack:
418	self.stack[-1] = None
419	else:
420	self.stack = [None]
421	self.imperfect = True
422
423	def visit_COMPARE_OP(self, lo, hi):
424	op2, op1 = self.stack.pop(), self.stack.pop()
425	op = self.sql_cmp_op[lo + (hi << 8)]
426	if op == 'in':
427	self.stack.append(icontainedby(op1, op2))
428	self.imperfect = True
429	elif op == 'not in':
430	self.stack.append(icontainedby(op1, op2, True))
431	self.imperfect = True
432	elif op == '=' and op2 == 'Null':
433	self.stack.append(op1 + " Is Null")
434	elif op == '=' and op1 == 'Null':
435	self.stack.append(op2 + " Is Null")
436	else:
437	if op2.startswith("'") and op2.endswith("'"):
438	# All ADO comparison operators for strings are case-insensitive
439	# by default. Rather than determine column-by-column which
440	# might be case-sensitive, just flag them all as imperfect.
441	self.imperfect = True
442	self.stack.append(op1 + " " + op + " " + op2)
443
444	def binary_op(self, op):
445	op2, op1 = self.stack.pop(), self.stack.pop()
446	self.stack.append(op1 + " " + op + " " + op2)
447
448	def visit_BINARY_SUBSCR(self):
449	"""The only BINARY_SUBSCR used in Expressions should be kwargs[key]."""
450	name = self.stack.pop()
451	# name, since formed in LOAD_CONST, has extraneous single-quotes.
452	value = self.expr.kwargs[name[1:-1]]
453	value = self.adapter.coerce(value)
454	self.stack.append(value)
455
456	def visit_UNARY_NOT(self):
457	op = self.stack.pop()
458	if op is None:
459	# Usually as a result of has(farClassName).
460	self.stack.append(None)
461	else:
462	self.stack.append("not (" + op + ")")
463
464
465	def safe_name(content):
466	return unicode(content).replace(u"_", u"")
467
468
469	class StoreIteratorADO(object):
470	"""Iterator for populating Units from storage."""
471
472	def __init__(self, store, unitClass, expr):
473	self.store = store
474	self.unitClass = unitClass
475	self.expr = expr
476	self.colIndices = {}
477	self.fieldTypes = []
478
479	self.sql, self.imperfect = store.select(unitClass, expr)
480
481	def field(self, name, row):
482	try:
483	col = self.colIndices[name]
484	except KeyError, x:
485	x.args += (name, self.unitClass.__name__)
486	raise x
487	else:
488	return (self.fieldTypes[col], self.data[col][row])
489
490	def load_data(self):
491	try:
492	anRS = self.store.recordset(self.sql, adOpenForwardOnly,
493	adLockReadOnly)
494	except pywintypes.com_error, x:
495	x.args += (self.sql, )
496	raise x
497
498	for col, x in enumerate(anRS.Fields):
499	self.colIndices[x.Name] = col
500	self.fieldTypes.append(x.Type)
501
502	self.data = []
503	if not(anRS.BOF and anRS.EOF):
504	## anRS.MoveFirst()
505	## if not(anRS.BOF or anRS.EOF):
506	# We tried .MoveNext() and lots of Fields.Item() calls.
507	# Using GetRows() beats that time by about 2/3.
508	self.data = anRS.GetRows()
509	anRS.Close()
510
511	def units(self):
512	self.load_data()
513	if len(self.data) > 0:
514	for row in range(len(self.data[0])):
515	unit = self.unitClass()
516	coercer = AdapterFromADO(unit)
517	for key in unit.__class__.properties():
518	value = self.field(key, row)
519	coercer.consume(key, value)
520	# If our SQL is imperfect, don't yield it to the
521	# caller unless it passes evaluate().
522	if (not self.imperfect) or self.expr.evaluate(unit):
523	yield unit
524
525
526	class CollectionLoaderADO(StoreIteratorADO):
527	"""Iterable Factory for populating UnitCollections from storage."""
528
529	def units(self):
530	self.load_data()
531	if len(self.data) > 0:
532	coll_coercer = AdapterFromADO().coerce
533	for row in range(len(self.data[0])):
534	unit = self.unitClass()
535	coercer = AdapterFromADO(unit)
536	for key in unit.__class__.properties():
537	value = self.field(key, row)
538	coercer.consume(key, value)
539	# If our SQL is imperfect, don't yield it to the
540	# caller unless it passes evaluate().
541	if (not self.imperfect) or self.expr.evaluate(unit):
542	# Load the collection.
543	# Grab the data dictionary (list of Unit ID's)
544	rsource = (u"SELECT ID FROM [%s__%s]" %
545	(self.store.prefix, safe_name(unit.ID)))
546	try:
547	dataRS = self.store.recordset(rsource)
548	except pywintypes.com_error, x:
549	# This usually occurs because the UnitCollection was
550	# reserved but no table yet made for IDs. This is OK.
551	pass
552	else:
553	idtype = self.store.arena.class_by_name(unit.Type).ID.type
554	while not dataRS.EOF:
555	ID = coll_coercer((0, dataRS.Fields.Item(u'ID')),
556	idtype)
557	unit.add(ID)
558	dataRS.MoveNext()
559	dataRS.Close()
560	yield unit
561
562
563	class StoreMultiIteratorADO(StoreIteratorADO):
564	"""Iterator for populating Units (from multiple classes) from storage."""
565
566	def __init__(self, store, unitClass, expr, pairs):
567	self.store = store
568	self.unitClass = unitClass
569	self.expr = expr
570	self.pairs = pairs
571	self.fieldTypes = []
572
573	sel = store.multiselect(unitClass, expr, pairs)
574	self.sql, self.imperfect, self.columns = sel
575
576	def populate_unit(self, unit, row):
577	"""Populate a Unit from a database row."""
578	coercer = AdapterFromADO(unit)
579	cls = unit.__class__
580	for key in cls.properties():
581	try:
582	col = self.columns.index((cls, key))
583	except ValueError, x:
584	x.args += (cls, key)
585	raise x
586	else:
587	coercer.consume(key, (self.fieldTypes[col],
588	self.data[col][row]))
589
590	def load_data(self):
591	try:
592	anRS = self.store.recordset(self.sql, adOpenForwardOnly,
593	adLockReadOnly)
594	except pywintypes.com_error, x:
595	x.args += (self.sql, )
596	raise x
597
598	for col, x in enumerate(anRS.Fields):
599	self.fieldTypes.append(x.Type)
600
601	self.data = []
602	if not(anRS.BOF and anRS.EOF):
603	## anRS.MoveFirst()
604	## if not(anRS.BOF or anRS.EOF):
605	# We tried .MoveNext() and lots of Fields.Item() calls.
606	# Using GetRows() beats that time by about 2/3.
607	self.data = anRS.GetRows()
608	anRS.Close()
609
610	def units(self):
611	self.load_data()
612	if len(self.data) > 0:
613	for row in range(len(self.data[0])):
614	unit = self.unitClass()
615	self.populate_unit(unit, row)
616	# If our SQL is imperfect, don't yield it to the
617	# caller unless it passes evaluate().
618	if (not self.imperfect) or self.expr.evaluate(unit):
619	cls, expr = self.pairs[0]
620	farUnit = cls()
621	self.populate_unit(farUnit, row)
622	if farUnit.ID is None:
623	yield unit, None
624	elif ((not self.imperfect) or expr is None
625	or expr.evaluate(farUnit)):
626	yield unit, farUnit
627
628
629	class StorageManagerADO(storage.StorageManager):
630	"""StoreManager to save and retrieve Units via ADO 2.7.
631
632	You must run makepy on ADO 2.7 before installing.
633	"""
634
635	decompiler = ADOSQLDecompiler
636	threaded = False
637
638	def __init__(self, name, arena, allOptions={}):
639	pythoncom.CoInitialize()
640
641	storage.StorageManager.__init__(self, name, arena, allOptions)
642
643	self.connstring = allOptions[u'Connect']
644	if allOptions.get(u'Threaded', ''):
645	self.threaded = True
646	self._connection = None
647	else:
648	self._connection = win32com.client.Dispatch(r'ADODB.Connection')
649	self._connection.Open(self.connstring)
650
651	self.prefix = allOptions.get(u'Prefix', u"djv")
652	self.cursorType = int(allOptions.get(u'CursorType', adOpenDynamic))
653	self.lockType = int(allOptions.get(u'LockType', adLockOptimistic))
654
655	self.reserve_lock = threading.Lock()
656
657	def shutdown(self):
658	if self._connection is not None:
659	self._connection.Close()
660
661	def connection(self):
662	if self.threaded:
663	t = threading.currentThread()
664	if not hasattr(t, 'SMADOconn'):
665	t.SMADOconn = win32com.client.Dispatch(r'ADODB.Connection')
666	if t.SMADOconn.State == adStateClosed:
667	t.SMADOconn.Open(self.connstring)
668	return t.SMADOconn
669	else:
670	return self._connection
671
672	def recordset(self, aQuery, cursorType=None, lockType=None):
673	anRS = win32com.client.Dispatch(r'ADODB.Recordset')
674	## anRS.Cursorlocation = 3 # adUseClient; Use to obtain .Recordcount
675	if cursorType is None:
676	cursorType = self.cursorType
677	if lockType is None:
678	lockType = self.lockType
679
680	try:
681	anRS.Open(aQuery, self.connection(), cursorType, lockType)
682	except pywintypes.com_error, x:
683	x.args += (aQuery, )
684	raise x
685	return anRS
686
687	def _join(self, path=[]):
688	if not path: return u''
689	firstcls = path.pop(0)
690	if not path: return firstcls.__name__
691
692	spath = self.arena.associations.shortest_path(firstcls, path[0])
693	spath.pop(0)
694	cls = spath[0]
695	leftkey, rightkey = firstcls._associations[cls]
696	params = {u'prefix': u'djv',
697	u'left': firstcls.__name__,
698	u'right': cls.__name__,
699	u'leftkey': leftkey,
700	u'rightkey': rightkey,
701	}
702	if len(spath) == 1:
703	params[u'child'] = u"[%(prefix)s%(right)s]" % params
704	else:
705	params[u'child'] = u"(%s)" % self._join(spath)
706
707	return (u"[%(prefix)s%(left)s] LEFT JOIN %(child)s"
708	u" ON [%(prefix)s%(left)s].[%(leftkey)s] = "
709	u"[%(prefix)s%(right)s].[%(rightkey)s]" % params)
710
711	def multiselect(self, firstcls, firstexpr, pairs):
712	firstwhere, imp = self.where(firstcls, firstexpr)
713	cols = [(firstcls, k) for k in firstcls.properties()]
714
715	# TODO: concat multiple pairs.
716	assert len(pairs) == 1
717	for cls, expr in pairs:
718	if expr is None:
719	expr = logic.Expression(lambda x: True)
720	j = self._join([firstcls, cls])
721
722	w, new_imp = self.where(cls, expr)
723	imp \|= new_imp
724	if w and w != "True":
725	w = " WHERE %s AND %s" % (w, firstwhere)
726	else:
727	w = " WHERE %s" % firstwhere
728
729	cols += [(cls, k) for k in cls.properties()]
730	colnames = ["[%s%s].[%s]" % (self.prefix, colcls.__name__, k)
731	for colcls, k in cols]
732
733	statement = "SELECT %s FROM %s%s" % (u', '.join(colnames), j, w)
734
735	return statement, imp, cols
736
737	def select(self, unitClass, expr, distinct_fields=None):
738	tablename = self.prefix + safe_name(unitClass.__name__)
739	if distinct_fields:
740	distinct_fields = [u'[%s]' % x for x in distinct_fields]
741	sql = (u"SELECT DISTINCT %s FROM [%s]" %
742	(u', '.join(distinct_fields), tablename))
743	else:
744	sql = u"SELECT * FROM [%s]" % tablename
745	w, i = self.where(unitClass, expr)
746	if len(w) > 0:
747	w = u" WHERE " + w
748	else:
749	w = u""
750	sql += w
751	return sql, i
752
753	def where(self, cls, expr):
754	return self.decompiler(self, cls, expr).code()
755
756	def execute(self, aQuery):
757	self.connection().Execute(aQuery)
758
759	def recall(self, cls, expr=None, pairs=None):
760	if expr is None:
761	expr = logic.Expression(lambda x: True)
762	if pairs is not None:
763	return StoreMultiIteratorADO(self, cls, expr, pairs).units()
764	else:
765	if cls.__name__ == u'UnitCollection':
766	aLoader = CollectionLoaderADO
767	else:
768	aLoader = StoreIteratorADO
769	return aLoader(self, cls, expr).units()
770
771	def reserve(self, unit):
772	"""reserve(unit). -> Reserve a persistent slot for unit."""
773	self.reserve_lock.acquire()
774	try:
775	if unit.ID is None:
776	data = []
777	clsname = unit.__class__.__name__
778	anRS = self.recordset(u"SELECT ID FROM [%s%s];" %
779	(self.prefix, safe_name(clsname)))
780	if not (anRS.BOF and anRS.EOF):
781	## anRS.MoveFirst()
782	## if not (anRS.BOF or anRS.EOF):
783	data = anRS.GetRows()[0]
784	unit.ID = unit.sequencer.next(data)
785
786	anRS.AddNew()
787	anRS.Fields(u'ID').Value = unit.ID
788	anRS.Update()
789	anRS.Close()
790	finally:
791	self.reserve_lock.release()
792
793	def save(self, unit, forceSave=False):
794	"""save(unit, forceSave=False). -> Update storage from unit's data.
795
796	Notice in particular that we do not use the auto-number or
797	sequence generation capabilities within some databases, etc.
798	The ID should be supplied by UnitSequencers via reserve().
799	"""
800	if unit.dirty or forceSave:
801	cls = unit.__class__
802	# Use a cursor always--makes mixed-quotes, newline, etc easier.
803	anRS = self.recordset("SELECT * FROM [%s%s] WHERE ID = %s" %
804	(self.prefix, safe_name(cls.__name__),
805	AdapterToADOSQL().coerce(unit.ID)))
806	if anRS.EOF and anRS.BOF:
807	anRS.AddNew()
808	anRS.Fields(u'ID').Value = unit.ID
809	fmt = AdapterToADOFields()
810	for key in cls.properties():
811	eachType = cls.property_type(key)
812	newValue = fmt.coerce(getattr(unit, key), eachType)
813	try:
814	anRS.Fields(key).Value = newValue
815	except pywintypes.com_error, x:
816	try:
817	anRS.Close()
818	except:
819	pass
820	x.args += (cls.__name__, key, eachType, newValue)
821	raise x
822	anRS.Update()
823	# Need to explicitly close here, or save_collection
824	# will fail on BeginTrans.
825	anRS.Close()
826	if cls.__name__ == u'UnitCollection':
827	self.save_collection(unit)
828	unit.dirty = False
829
830	def save_collection(self, unitColl):
831	"""Update the database from the UnitCollection's data."""
832	conn = self.connection()
833	## # Dropped the begintrans; we were running into limits in MS Access.
834	## conn.BeginTrans()
835	deleteStatement = (u"DROP TABLE [%s__%s];" %
836	(self.prefix, safe_name(unitColl.ID)))
837	conn.Execute(deleteStatement)
838
839	cls = unitColl.unit_class()
840	fieldtype = self.createCoercions[cls.ID.type](cls, 'ID')
841	createStatement = (u"CREATE TABLE [%s__%s] (ID %s);"
842	% (self.prefix, safe_name(unitColl.ID),
843	fieldtype))
844	conn.Execute(createStatement)
845
846	ins = u"INSERT INTO [%s__%s] (ID) VALUES (%s);"
847	coercer = AdapterToADOSQL().coerce
848	for eachID in unitColl.ids():
849	# Create a row for the unit.
850	# Use an INSERT command (not a cursor) for better performance.
851	# TODO: cluster inserts.
852	insStatement = ins % (self.prefix, safe_name(unitColl.ID),
853	coercer(eachID))
854	try:
855	conn.Execute(insStatement)
856	except pywintypes.com_error, x:
857	x.args += (insStatement, createStatement)
858	raise x
859	## conn.CommitTrans()
860
861	def destroy(self, unit):
862	"""Delete the unit."""
863	# Use a DELETE command instead of a cursor for better performance.
864	deleteStatement = (u"DELETE * FROM [%s%s] WHERE ID = %s" %
865	(self.prefix, safe_name(unit.__class__.__name__),
866	AdapterToADOSQL().coerce(unit.ID)))
867	try:
868	self.execute(deleteStatement)
869	except pywintypes.com_error, x:
870	x.args += (deleteStatement, )
871	raise x
872
873	def _create_str_storage(unitClass, key):
874	"""This basic string handler does not know anything about the size
875	limitations of the particular database. You should use one of the
876	subclasses for your particular database if you need storage for
877	strings over 255 characters."""
878	# 'self' is missing from the func sig ON PURPOSE.
879	try:
880	prop = getattr(unitClass, key)
881	size = prop.hints[u'Size']
882	return u"VARCHAR(%s)" % size
883	except KeyError:
884	return u"VARCHAR(255)"
885
886	createCoercions = {datetime.datetime: lambda x, y: u"TIMESTAMP",
887	datetime.date: lambda x, y: u"DATE",
888	datetime.time: lambda x, y: u"TIME",
889	str: _create_str_storage,
890	unicode: _create_str_storage,
891	dict: _create_str_storage,
892	list: _create_str_storage,
893	fixedpoint.FixedPoint: lambda x, y: u"FLOAT",
894	int: lambda x, y: u"INTEGER",
895	bool: lambda x, y: u"BIT",
896	float: lambda x, y: u"FLOAT",
897	}
898
899	def create_storage(self, unitClass):
900	fields = []
901	for key in unitClass.properties():
902	eachType = unitClass.property_type(key)
903	aType = self.createCoercions[eachType](unitClass, key)
904	fields.append(u"[%s] %s" % (key, aType))
905	indices = [x + " ASC" for x in unitClass.indices()]
906
907	createStatement = (u"CREATE TABLE [%s%s] (%s)" %
908	(self.prefix,
909	safe_name(unitClass.__name__),
910	", ".join(fields)))
911	try:
912	self.execute(createStatement)
913	except Exception, x:
914	x.args += (createStatement, )
915	raise x
916
917	for index in indices:
918	indexStatement = (u"CREATE INDEX [%si%s%s] ON [%s%s] (%s)"
919	% (self.prefix, safe_name(unitClass.__name__),
920	safe_name(index),
921	self.prefix, safe_name(unitClass.__name__),
922	index))
923	try:
924	self.execute(indexStatement)
925	except Exception, x:
926	x.args += (indexStatement, )
927	raise x
928
929	return True
930
931	def distinct(self, cls, fields, expr=None):
932	"""Return distinct values for specified fields."""
933	if expr is None:
934	expr = logic.Expression(lambda x: True)
935
936	# ^%$#@! There's no way to handle imperfect queries without
937	# creating all involved Units, which defeats the purpose of
938	# distinct, which was a speed issue more than anything. Grr.
939	sql, imperfect = self.select(cls, expr, fields)
940	# Ignore for now.
941	## if imperfect:
942	## raise ValueError(u"The following query cannot be reliably "
943	## u"returned from an ADO data source.",
944	## u"distinct()", cls, fields, expr)
945
946	try:
947	anRS = self.recordset(sql, adOpenForwardOnly, adLockReadOnly)
948	except pywintypes.com_error, x:
949	x.args += (self.sql, )
950	raise x
951
952	fieldTypes = [x.Type for x in anRS.Fields]
953	data = []
954	if not (anRS.BOF and anRS.EOF):
955	# We tried .MoveNext() and lots of Fields.Item() calls.
956	# Using GetRows() beats that time by about 2/3.
957	data = anRS.GetRows()
958	anRS.Close()
959
960	if data:
961	coerced_data = []
962	coerce = AdapterFromADO().coerce
963	for col, field in enumerate(fields):
964	expectedType = cls.property_type(field)
965	actualType = fieldTypes[col]
966	coerced_row = [coerce((actualType, val), expectedType)
967	for val in data[col]]
968	coerced_data.append(coerced_row)
969	data = zip(*coerced_data)
970	return data
971
972
973	class StorageManagerADO_SQLServer(StorageManagerADO):
974
975	def _create_str_storage(unitClass, key):
976	try:
977	prop = getattr(unitClass, key)
978	size = prop.hints[u'Size']
979	except KeyError:
980	return u"VARCHAR(255)"
981	else:
982	if size == 0 or size > 8060:
983	# 8060 is the absolute upper limit, based on the page size
984	# of SQL server. If there are further fields defined for
985	# the unitClass, we could exceed the max size for a record.
986	# Perhaps someday we can calc the total requested record
987	# size, and adjust accordingly. For now, we just trust that
988	# units generally use a size of 0 to bump up to NTEXT.
989	return u"NTEXT"
990	return u"VARCHAR(%s)" % size
991
992	createCoercions = {datetime.datetime: lambda x, y: u"TIMESTAMP",
993	datetime.date: lambda x, y: u"DATE",
994	datetime.time: lambda x, y: u"TIME",
995	str: _create_str_storage,
996	unicode: _create_str_storage,
997	dict: lambda x, y: u"NTEXT",
998	list: lambda x, y: u"NTEXT",
999	fixedpoint.FixedPoint: lambda x, y: u"FLOAT",
1000	float: lambda x, y: u"FLOAT",
1001	int: lambda x, y: u"INTEGER",
1002	bool: lambda x, y: u"BIT",
1003	}
1004
1005
1006	###########################################################################
1007	## ##
1008	## MS Access ##
1009	## ##
1010	###########################################################################
1011
1012
1013	class ADOSQLDecompiler_MSAccess(ADOSQLDecompiler):
1014	sql_cmp_op = ('<', '<=', '=', '<>', '>', '>=', 'in', 'not in')
1015	functions = {dejavu.icontains: lambda x, y: x + " Like '%" + y[1:-1] + "%'",
1016	dejavu.icontainedby: icontainedby,
1017	dejavu.istartswith: lambda x, y: x + " Like '" + y[1:-1] + "%'",
1018	dejavu.iendswith: lambda x, y: x + " Like '%" + y[1:-1] + "'",
1019	dejavu.ieq: lambda x, y: x + " = " + y,
1020	dejavu.now: lambda: "Now()",
1021	dejavu.today: lambda: "DateValue(Now())",
1022	dejavu.year: lambda x: "Year(" + x + ")",
1023	}
1024
1025
1026	class StorageManagerADO_MSAccess(StorageManagerADO):
1027
1028	decompiler = ADOSQLDecompiler_MSAccess
1029
1030	def _create_str_storage(unitClass, key):
1031	try:
1032	prop = getattr(unitClass, key)
1033	size = prop.hints[u'Size']
1034	except KeyError:
1035	return u"VARCHAR(255)"
1036	else:
1037	if size == 0 or size > 255:
1038	# 255 is the upper limit, based on the max size of 'Text'
1039	# fields within MS Access. If there are further fields
1040	# defined for the unitClass, we could exceed the max size
1041	# for a record. Perhaps someday we can calc the total
1042	# requested record size, and adjust accordingly. For now,
1043	# we just trust that units generally use aSize of 0 to
1044	# bump up to MEMO.
1045	return u"MEMO"
1046	return u"VARCHAR(%s)" % size
1047
1048	createCoercions = {datetime.datetime: lambda x, y: u"TIMESTAMP",
1049	datetime.date: lambda x, y: u"DATE",
1050	datetime.time: lambda x, y: u"TIME",
1051	str: _create_str_storage,
1052	unicode: _create_str_storage,
1053	dict: lambda x, y: u"MEMO",
1054	list: lambda x, y: u"MEMO",
1055	fixedpoint.FixedPoint: lambda x, y: u"FLOAT",
1056	float: lambda x, y: u"FLOAT",
1057	int: lambda x, y: u"INTEGER",
1058	bool: lambda x, y: u"BIT",
1059	}
1060
1061	if __name__ == '__main__':
1062	# Auto generate .py support for ADO 2.7
1063	print 'Please wait while support for ADO 2.7 is verified...'
1064	CLSID = '{EF53050B-882E-4776-B643-EDA472E8E3F2}'
1065	win32com.client.gencache.EnsureModule(CLSID, 0, 2, 7)
1066

Note: See TracBrowser for help on using the browser.

root/trunk/storage/storeado.py

Download in other formats: