Revision Log

storeado.py

Revision 62 (checked in by fumanchu, 8 years ago)	1. Tests added for CachingProxy?, BurnedProxy?. 2. Edit tests added to zoo_fixture.py 3. Bug fixed in CachingProxy?.save (should not have checked dirty). 4. CachingProxy? now holds current Timer and cancels it on shutdown.

Line
1	import sys
2	# Put COM in free-threaded mode. This first thread will have
3	# CoInitializeEx called automatically when pythoncom is imported.
4	sys.coinit_flags = 0
5	import pythoncom
6
7	import win32com.client
8	import pywintypes
9	import datetime
10
11	try:
12	import cPickle as pickle
13	except ImportError:
14	import pickle
15
16	try:
17	import fixedpoint
18	except ImportError:
19	pass
20
21	try:
22	# Builtin in Python 2.5?
23	decimal
24	except NameError:
25	try:
26	# Module in Python 2.3, 2.4
27	import decimal
28	except ImportError:
29	pass
30
31	import warnings
32
33	import dejavu
34	from dejavu import storage, logic
35	from dejavu.storage import db
36
37	adOpenForwardOnly = 0
38	adOpenKeyset = 1
39	adOpenDynamic = 2
40	adOpenStatic = 3
41
42	adLockReadOnly = 1
43	adLockPessimistic = 2
44	adLockOptimistic = 3
45	adLockBatchOptimistic = 4
46
47	adUseClient = 3
48
49	# 12/30/1899, the zero-Date for ADO = 693594
50	zeroHour = datetime.date(1899, 12, 30).toordinal()
51
52
53	def time_from_com(com_date):
54	"""Return a valid datetime.time from a COM date or time object."""
55	hour, minute = divmod(86400 * (float(com_date) % 1), 3600)
56	minute, second = divmod(minute, 60)
57	# Must do both int() and round() or we'll be up to 1 second off.
58	hour = int(round(hour))
59	minute = int(round(minute))
60	second = int(round(second))
61
62	while second > 59:
63	second -= 60
64	minute += 1
65	while second < 0:
66	second += 60
67	minute -= 1
68	while minute > 59:
69	minute -= 60
70	hour += 1
71	while minute < 0:
72	minute += 60
73	hour -= 1
74	while hour > 23:
75	hour -= 24
76	day += 1
77	while hour < 0:
78	hour += 24
79
80	return datetime.time(hour, minute, second)
81
82
83	class AdapterFromADO(db.AdapterFromDB):
84	"""Coerce incoming values from ADO to Dejavu datatypes."""
85
86	def coerce_datetime_datetime(self, value, coltype):
87	# Illegal Date/Time values will crash the
88	# app when using value.Format(). Therefore,
89	# grab the value and figure the date ourselves.
90	# Use 1-second resolution only.
91	if isinstance(value, basestring):
92	if value:
93	try:
94	return datetime.datetime(int(value[0:4]), int(value[4:6]),
95	int(value[6:8]))
96	except Exception, x:
97	raise ValueError("'%s' %s" % (value, type(value)))
98	else:
99	return None
100	else:
101	# For some reason, we need both float and int.
102	aDate = datetime.date.fromordinal(int(float(value)) + zeroHour)
103	return datetime.datetime.combine(aDate, time_from_com(value))
104
105	def coerce_datetime_date(self, value, coltype):
106	# See coerce_datetime
107	if isinstance(value, basestring):
108	if value:
109	try:
110	return datetime.date(int(value[0:4]), int(value[4:6]),
111	int(value[6:8]))
112	except Exception, x:
113	raise ValueError("'%s' %s" % (value, type(value)))
114	else:
115	return None
116	else:
117	return datetime.date.fromordinal(int(float(value)) + zeroHour)
118
119	def coerce_datetime_time(self, value, coltype):
120	# See coerce_datetime
121	return time_from_com(value)
122
123	def coerce_fixedpoint_FixedPoint(self, value, coltype):
124	if coltype == 0x06:
125	# Currency
126	value = value[1] / 10000.0
127	return fixedpoint.FixedPoint(value)
128
129	def coerce_float(self, value, coltype):
130	if coltype == 0x06:
131	# Currency
132	value = value[1] / 10000.0
133	return float(value)
134
135	def coerce_int(self, value, coltype):
136	if coltype == 0x0b:
137	# Boolean
138	return value != 0
139	return int(value)
140
141	coerce_bool = coerce_int
142
143	def coerce_unicode(self, value, coltype):
144	if isinstance(value, unicode):
145	# For some reason, inValue is already a unicode object.
146	return value
147	if isinstance(value, (basestring, buffer)):
148	try:
149	return unicode(value, "ISO-8859-1")
150	except UnicodeError:
151	raise StandardError(type(value))
152	return unicode(value)
153
154
155
156	class AdapterToADOFields(storage.Adapter):
157	"""Coerce outgoing values from Dejavu datatypes to ADO.Field types."""
158
159	def noop(self, value):
160	return value
161
162	def coerce_bool(self, value):
163	if value:
164	return True
165	return False
166
167	def coerce_datetime_datetime(self, value):
168	if value is None:
169	return None
170	return self.coerce_datetime_date(value) + self.coerce_datetime_time(value)
171
172	def coerce_datetime_date(self, value):
173	if value is None:
174	return None
175	return value.toordinal() - zeroHour
176
177	def coerce_datetime_time(self, value):
178	if value is None:
179	return None
180	return ((value.second + (value.minute * 60) + (value.hour * 3600))
181	/ 86400.0)
182
183	def pickle(self, value):
184	# We must not use a pickle format other than 0, because binary
185	# strings are not safe for all DB string fields.
186	return pickle.dumps(value)
187
188	coerce_dict = pickle
189
190	def coerce_fixedpoint_FixedPoint(self, value):
191	if value is None:
192	return None
193	return float(value)
194
195	coerce_float = noop
196	coerce_int = noop
197
198	coerce_list = pickle
199
200	coerce_long = noop
201	coerce_str = noop
202
203	coerce_tuple = pickle
204
205	coerce_unicode = noop
206
207
208	class ADOSQLDecompiler(db.SQLDecompiler):
209
210	def visit_COMPARE_OP(self, lo, hi):
211	op2, op1 = self.stack.pop(), self.stack.pop()
212	if op1 is db.cannot_represent or op2 is db.cannot_represent:
213	self.stack.append(db.cannot_represent)
214	return
215
216	op = lo + (hi << 8)
217	if op in (6, 7): # in, not in
218	# Looking for text in a field. Use Like (reverse terms).
219	# LIKE is case-insensitive in MS SQL Server (and there
220	# doesn't seem to be a way around it). Use icontainedby
221	# and just mark imperfect.
222	value = self.dejavu_icontainedby(op1, op2)
223	if op == 7:
224	value = "NOT " + value
225	self.stack.append(value)
226	self.imperfect = True
227	elif op1 == 'NULL':
228	if op == 2:
229	self.stack.append(op2 + " IS NULL")
230	elif op == 3:
231	self.stack.append(op2 + " IS NOT NULL")
232	else:
233	raise ValueError("Non-equality Null comparisons not allowed.")
234	elif op2 == 'NULL':
235	if op == 2:
236	self.stack.append(op1 + " IS NULL")
237	elif op == 3:
238	self.stack.append(op1 + " IS NOT NULL")
239	else:
240	raise ValueError("Non-equality Null comparisons not allowed.")
241	else:
242	if (isinstance(op2, db.ConstWrapper)
243	and isinstance(op2.basevalue, basestring)):
244	# ADO comparison operators for strings are case-insensitive
245	# by default. Rather than determine which columns in the DB
246	# might be case-sensitive, just flag them all as imperfect.
247	# TODO: might be possible to cast both to varbinary, but
248	# that may cause problems with unicode columns.
249	self.imperfect = True
250	self.stack.append(op1 + " " + self.sql_cmp_op[op] + " " + op2)
251
252	def column_name(self, name):
253	return '%s.[%s]' % (self.tablename, name)
254
255	# --------------------------- Dispatchees --------------------------- #
256
257	def attr_startswith(self, tos, arg):
258	self.imperfect = True
259	return tos + " LIKE '" + self.adapter.escape_like(arg) + "%'"
260
261	def attr_endswith(self, tos, arg):
262	self.imperfect = True
263	return tos + " LIKE '%" + self.adapter.escape_like(arg) + "'"
264
265	def containedby(self, op1, op2):
266	self.imperfect = True
267	if isinstance(op1, ConstWrapper):
268	# Looking for text in a field. Use Like (reverse terms).
269	return op2 + " LIKE '%" + self.adapter.escape_like(op1) + "%'"
270	else:
271	# Looking for field in (a, b, c)
272	atoms = [self.adapter.coerce(x) for x in op2.basevalue]
273	return op1 + " IN (" + ", ".join(atoms) + ")"
274
275	def dejavu_icontainedby(self, op1, op2):
276	if isinstance(op1, db.ConstWrapper):
277	# Looking for text in a field. Use Like (reverse terms).
278	# LIKE is already case-insensitive in MS SQL Server;
279	# so don't use LOWER().
280	value = op2 + " LIKE '%" + self.adapter.escape_like(op1) + "%'"
281	else:
282	# Looking for field in (a, b, c)
283	atoms = [self.adapter.coerce(x) for x in op2.basevalue]
284	value = op1 + " IN (" + ", ".join(atoms) + ")"
285	return value
286
287	def dejavu_istartswith(self, x, y):
288	# Like is already case-insensitive in ADO; so don't use LOWER().
289	return x + " LIKE '" + self.adapter.escape_like(y) + "%'"
290
291	def dejavu_iendswith(self, x, y):
292	# Like is already case-insensitive in ADO; so don't use LOWER().
293	return x + " LIKE '%" + self.adapter.escape_like(y) + "'"
294
295	def dejavu_ieq(self, x, y):
296	# = is already case-insensitive in ADO.
297	return x + " = " + y
298
299	def dejavu_now(self):
300	return "getdate()"
301
302	def dejavu_today(self):
303	return "DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)"
304
305	def func__builtin___len(self, x):
306	return "Len(" + x + ")"
307
308
309	class StorageManagerADO(db.StorageManagerDB):
310	"""StoreManager to save and retrieve Units via ADO 2.7.
311
312	You must run makepy on ADO 2.7 before installing.
313	"""
314
315	close_connection_method = 'Close'
316	decompiler = ADOSQLDecompiler
317	fromAdapter = AdapterFromADO()
318
319	def connatoms(self):
320	atoms = {}
321	for pair in self.connstring.split(";"):
322	if pair:
323	k, v = pair.split("=", 1)
324	atoms[k.upper().strip()] = v.strip()
325	return atoms
326
327	def identifier(self, *atoms):
328	ident = ''.join(map(str, atoms))
329	return '[' + ident + ']'
330
331	def _get_conn(self):
332	conn = win32com.client.Dispatch(r'ADODB.Connection')
333	try:
334	conn.Open(self.connstring)
335	return conn
336	except pywintypes.com_error, x:
337	if x.args[2][5] == -2147467259:
338	msg = x.args[2][2]
339	if (
340	# SQL Server: "Cannot open database requested in login
341	# 'dejavu_test'. Login fails."
342	msg.startswith("Cannot open database") or
343	# MSAccess: "Could not find file
344	# 'C:\Python23\Lib\site-packages\dejavu\storage\zoo.mdb'."
345	msg.startswith("Could not find file")):
346	if self.CreateIfMissing:
347	self.create_database()
348	conn.Open(self.connstring)
349	return conn
350	raise
351
352	def execute(self, query, conn=None):
353	if conn is None:
354	conn = self.connection()
355	try:
356	conn.Execute(query)
357	except pywintypes.com_error, x:
358	x.args += (query, )
359	conn = None
360	raise x
361
362	def fetch(self, query, conn=None):
363	"""fetch(query, conn=None) -> rowdata, columns."""
364	if conn is None:
365	conn = self.connection()
366
367	res = win32com.client.Dispatch(r'ADODB.Recordset')
368	# Uncomment the following to get .Recordcount
369	# res.CursorLocation = adUseClient
370	try:
371	if self.threaded:
372	# 'conn' will be a ConnectionWrapper object, which .Open
373	# won't accept. Pass the unwrapped connection instead.
374	res.Open(query, conn.conn, adOpenForwardOnly, adLockReadOnly)
375	else:
376	res.Open(query, conn, adOpenForwardOnly, adLockReadOnly)
377	except pywintypes.com_error, x:
378	try:
379	res.Close()
380	except:
381	pass
382	x.args += (query, )
383	conn = None
384	raise x
385
386	columns = [(x.Name, x.Type) for x in res.Fields]
387
388	data = []
389	if not(res.BOF and res.EOF):
390	# We tried .MoveNext() and lots of Fields.Item() calls.
391	# Using GetRows() beats that time by about 2/3.
392	data = res.GetRows()
393	# Convert cols x rows -> rows x cols
394	data = zip(*data)
395	res.Close()
396	conn = None
397
398	return data, columns
399
400
401	###########################################################################
402	## ##
403	## SQL Server ##
404	## ##
405	###########################################################################
406
407
408	class AdapterToADOSQL_SQLServer(db.AdapterToSQL):
409
410	escapes = [("'", "''")]
411	like_escapes = [("%", "[%]"), ("_", "[_]")]
412
413	# These are not the same as coerce_bool (which is used on one side of
414	# a comparison). Instead, these are used when the whole (sub)expression
415	# is True or False, e.g. "WHERE TRUE", or "WHERE TRUE and 'a'.'b' = 3".
416	bool_true = "(1=1)"
417	bool_false = "(1=0)"
418
419	def coerce_bool(self, value):
420	if value:
421	return '1'
422	return '0'
423
424
425	class FieldTypeAdapter_SQLServer(db.FieldTypeAdapter):
426
427	numeric_max_precision = 38
428
429	def coerce_bool(self, cls, key): return u"BIT"
430
431	def coerce_datetime_datetime(self, cls, key):
432	return u"DATETIME"
433
434	def coerce_datetime_date(self, cls, key):
435	return u"DATETIME"
436
437	def coerce_datetime_time(self, cls, key):
438	return u"DATETIME"
439
440	def coerce_str(self, cls, key):
441	# The bytes hint does not reflect the usual 4-byte base for varchar.
442	prop = getattr(cls, key)
443	bytes = int(prop.hints.get(u'bytes', '0'))
444	if bytes == 0:
445	# Okay, what the @#$%& is wrong with Redmond??!?! We can't even
446	# compare TEXT or NTEXT fields??!? Fine. We'll deny such, and
447	# warn the deployer with less swearing and exclamation points.
448	import warnings
449	warnings.warn("You have defined a string property without "
450	"limiting its length. Microsoft SQL Server does "
451	"not allow comparisons on string fields larger "
452	"than 8000 characters. Some of your data may be "
453	"truncated.")
454	bytes = 8000
455	# 8000 bytes is the absolute upper limit, based on T_SQL docs for
456	# varchar/varbinary. If there are further fields defined for the
457	# class, or the codepage uses a double-byte character set, we still
458	# might exceed the max size (8060) for a record. We could calc the
459	# total requested record size, and adjust accordingly. Meh.
460	return u"VARCHAR(%s)" % bytes
461
462
463	class StorageManagerADO_SQLServer(StorageManagerADO):
464
465	typeAdapter = FieldTypeAdapter_SQLServer()
466	toAdapter = AdapterToADOSQL_SQLServer()
467
468	def __init__(self, name, arena, allOptions={}):
469	db.StorageManagerDB.__init__(self, name, arena, allOptions)
470
471	self.connstring = allOptions[u'Connect']
472	atoms = self.connatoms()
473	self.dbname = atoms[u'INITIAL CATALOG']
474
475	def create_database(self):
476	# This method hasn't been tested yet for SQL server.
477	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
478	atoms = self.connatoms()
479	atoms['INITIAL CATALOG'] = "tempdb"
480	adoconn.Open("; ".join(["%s=%s" % (k, v) for k, v in atoms.iteritems()]))
481	adoconn.Execute("CREATE DATABASE %s" % self.identifier(self.dbname))
482	adoconn.Close()
483
484	def drop_database(self):
485	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
486	atoms = self.connatoms()
487	atoms['INITIAL CATALOG'] = "tempdb"
488	adoconn.Open("; ".join(["%s=%s" % (k, v) for k, v in atoms.iteritems()]))
489	adoconn.Execute("DROP DATABASE %s;" % self.identifier(self.dbname))
490	adoconn.Close()
491
492
493	###########################################################################
494	## ##
495	## MS Access ##
496	## ##
497	###########################################################################
498
499
500	class ADOSQLDecompiler_MSAccess(ADOSQLDecompiler):
501	sql_cmp_op = ('<', '<=', '=', '<>', '>', '>=', 'in', 'not in')
502
503	def dejavu_now(self):
504	return "Now()"
505
506	def dejavu_today(self):
507	return "DateValue(Now())"
508
509	def dejavu_year(self, x):
510	return "Year(" + x + ")"
511
512
513	class FieldTypeAdapter_MSAccess(db.FieldTypeAdapter):
514
515	numeric_max_precision = 15
516
517	def coerce_bool(self, cls, key): return u"BIT"
518
519	def coerce_datetime_datetime(self, cls, key): return u"DATETIME"
520	def coerce_datetime_date(self, cls, key): return u"DATETIME"
521	def coerce_datetime_time(self, cls, key): return u"DATETIME"
522
523	def numeric_type(self, cls, key, precision, scale):
524	if precision > self.numeric_max_precision:
525	warnings.warn("Decimal precision %s > maximum %s for %s.%s, "
526	"using %s. Values may be stored incorrectly."
527	% (precision, self.numeric_max_precision,
528	cls.__name__, key, self.__class__.__name__))
529	precision = self.numeric_max_precision
530	if scale > 4:
531	warnings.warn("Decimal scale %s > maximum 4 for %s.%s, "
532	"using %s. Values may be stored incorrectly."
533	% (scale, cls.__name__, key,
534	self.__class__.__name__))
535
536	# MS Access doesn't let us control precision and scale directly.
537	# From http://support.microsoft.com/?kbid=104977
538	# ORACLE number Microsoft Access data type
539	# ---------------------------------------------------
540	# Scale = 0 and
541	# precision <= 4 Integer
542	# precision <= 9 Long Integer
543	# precision <= 15 Double
544	# Scale > 0 and <= 4
545	# precision <= 15 Double
546	# Scale > 4 and/or
547	# precision > 15 Text
548	if scale == 0:
549	if precision <= 4:
550	return "INTEGER"
551	elif precision <= 9:
552	return "LONG"
553	return "DOUBLE"
554
555	def coerce_decimal_Decimal(self, cls, key):
556	prop = getattr(cls, key)
557	precision = int(prop.hints.get('precision', '0'))
558	if precision == 0:
559	precision = decimal.getcontext().prec
560	# Assume most people use decimal for money; default scale = 2.
561	scale = int(prop.hints.get(u'scale', 2))
562	return self.numeric_type(cls, key, precision, scale)
563
564	def coerce_fixedpoint_FixedPoint(self, cls, key):
565	prop = getattr(cls, key)
566	precision = int(prop.hints.get('precision', '0'))
567	if precision == 0:
568	precision = self.numeric_max_precision
569	# Assume most people use decimal for money; default scale = 2.
570	scale = int(prop.hints.get(u'scale', 2))
571	return self.numeric_type(cls, key, precision, scale)
572
573	def coerce_int(self, cls, key):
574	prop = getattr(cls, key)
575	bytes = int(prop.hints.get(u'bytes', '4'))
576	if bytes == 1:
577	return "BIT"
578	else:
579	return u"INTEGER"
580
581	def coerce_long(self, cls, key):
582	prop = getattr(cls, key)
583	bytes = int(prop.hints.get(u'bytes', 0))
584	return self.numeric_type(cls, key, precision, 0)
585
586	def coerce_str(self, cls, key):
587	# The bytes hint shall not reflect the usual 4-byte base for varchar.
588	prop = getattr(cls, key)
589	bytes = int(prop.hints.get(u'bytes', '0'))
590	if bytes and bytes <= 255:
591	# 255 chars is the upper limit for TEXT / VARCHAR in MS Access.
592	return u"VARCHAR(%s)" % bytes
593	else:
594	# MEMO is 1 GB max when set programatically (only 64K when set
595	# in Access UI). But then, 1 GB is the limit for the whole DB.
596	return u"MEMO"
597
598
599	class AdapterToADOSQL_MSAccess(db.AdapterToSQL):
600	"""Coerce Expression constants to ADO SQL."""
601
602	escapes = [("'", "''")]
603	like_escapes = [("%", "[%]"), ("_", "[_]")]
604
605	def coerce_datetime_datetime(self, value):
606	return (u'#%s/%s/%s %02d:%02d:%02d#' %
607	(value.month, value.day, value.year,
608	value.hour, value.minute, value.second))
609
610	def coerce_datetime_date(self, value):
611	return u'#%s/%s/%s#' % (value.month, value.day, value.year)
612
613	def coerce_datetime_time(self, value):
614	return u'#%02d:%02d:%02d#' % (value.hour, value.minute, value.second)
615
616
617	class StorageManagerADO_MSAccess(StorageManagerADO):
618	# Jet Connections and Recordsets are always free-threaded.
619
620	decompiler = ADOSQLDecompiler_MSAccess
621	typeAdapter = FieldTypeAdapter_MSAccess()
622	toAdapter = AdapterToADOSQL_MSAccess()
623
624	def __init__(self, name, arena, allOptions={}):
625	db.StorageManagerDB.__init__(self, name, arena, allOptions)
626
627	self.connstring = allOptions[u'Connect']
628	atoms = self.connatoms()
629	self.dbname = (atoms.get(u'DATA SOURCE') or
630	atoms.get(u'DATA SOURCE NAME') or
631	atoms.get(u'DBQ'))
632	# MS Access can't use a pool, because there doesn't seem
633	# to be a commit timeout.
634	self.pool = None
635	self.threaded = False
636	self.debug_connections = True
637
638	def create_database(self):
639	# By not providing an Engine Type, it defaults to 5 = Access 2000.
640	cat = win32com.client.Dispatch(r'ADOX.Catalog')
641	cat.Create(self.connstring)
642	cat.ActiveConnection.Close()
643
644	def drop_database(self):
645	import os
646	# This should accept relative or absolute paths
647	if os.path.exists(self.dbname):
648	os.remove(self.dbname)
649
650
651	if __name__ == '__main__':
652	# Auto generate .py support for ADO 2.7
653	print 'Please wait while support for ADO 2.7 is verified...'
654	CLSID = '{EF53050B-882E-4776-B643-EDA472E8E3F2}'
655	win32com.client.gencache.EnsureModule(CLSID, 0, 2, 7)
656

Note: See TracBrowser for help on using the browser.

root/trunk/storage/storeado.py

Download in other formats: