Revision Log

storeado.py

Revision 108 (checked in by fumanchu, 7 years ago)	New "version" method for db SM's.
Property svn:eol-style set to `native`

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 do_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 = do_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 = do_pickle
199
200	coerce_long = noop
201	coerce_str = noop
202
203	coerce_tuple = do_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	self.arena.log(query, dejavu.LOGSQL)
356	try:
357	conn.Execute(query)
358	except pywintypes.com_error, x:
359	x.args += (query, )
360	conn = None
361	raise x
362
363	def fetch(self, query, conn=None):
364	"""fetch(query, conn=None) -> rowdata, columns."""
365	if conn is None:
366	conn = self.connection()
367
368	res = win32com.client.Dispatch(r'ADODB.Recordset')
369	# Uncomment the following to get .Recordcount
370	# res.CursorLocation = adUseClient
371	try:
372	if self.threaded:
373	# 'conn' will be a ConnectionWrapper object, which .Open
374	# won't accept. Pass the unwrapped connection instead.
375	res.Open(query, conn.conn, adOpenForwardOnly, adLockReadOnly)
376	else:
377	res.Open(query, conn, adOpenForwardOnly, adLockReadOnly)
378	except pywintypes.com_error, x:
379	try:
380	res.Close()
381	except:
382	pass
383	x.args += (query, )
384	conn = None
385	raise x
386
387	columns = [(x.Name, x.Type) for x in res.Fields]
388
389	data = []
390	if not(res.BOF and res.EOF):
391	# We tried .MoveNext() and lots of Fields.Item() calls.
392	# Using GetRows() beats that time by about 2/3.
393	data = res.GetRows()
394	# Convert cols x rows -> rows x cols
395	data = zip(*data)
396	res.Close()
397	conn = None
398
399	return data, columns
400
401	def version(self):
402	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
403	return "ADO Version: %s" % adoconn.Version
404
405
406	###########################################################################
407	## ##
408	## SQL Server ##
409	## ##
410	###########################################################################
411
412
413	class AdapterToADOSQL_SQLServer(db.AdapterToSQL):
414
415	escapes = [("'", "''")]
416	like_escapes = [("%", "[%]"), ("_", "[_]")]
417
418	# These are not the same as coerce_bool (which is used on one side of
419	# a comparison). Instead, these are used when the whole (sub)expression
420	# is True or False, e.g. "WHERE TRUE", or "WHERE TRUE and 'a'.'b' = 3".
421	bool_true = "(1=1)"
422	bool_false = "(1=0)"
423
424	def coerce_bool(self, value):
425	if value:
426	return '1'
427	return '0'
428
429
430	class FieldTypeAdapter_SQLServer(db.FieldTypeAdapter):
431
432	numeric_max_precision = 38
433
434	def coerce_bool(self, cls, key): return u"BIT"
435
436	def coerce_datetime_datetime(self, cls, key):
437	return u"DATETIME"
438
439	def coerce_datetime_date(self, cls, key):
440	return u"DATETIME"
441
442	def coerce_datetime_time(self, cls, key):
443	return u"DATETIME"
444
445	def coerce_str(self, cls, key):
446	# The bytes hint does not reflect the usual 4-byte base for varchar.
447	prop = getattr(cls, key)
448	bytes = int(prop.hints.get(u'bytes', '0'))
449	if bytes == 0:
450	# Okay, what the @#$%& is wrong with Redmond??!?! We can't even
451	# compare TEXT or NTEXT fields??!? Fine. We'll deny such, and
452	# warn the deployer with less swearing and exclamation points.
453	warnings.warn("You have defined a string property without "
454	"limiting its length. Microsoft SQL Server does "
455	"not allow comparisons on string fields larger "
456	"than 8000 characters. Some of your data may be "
457	"truncated.")
458	bytes = 8000
459	# 8000 bytes is the absolute upper limit, based on T_SQL docs for
460	# varchar/varbinary. If there are further fields defined for the
461	# class, or the codepage uses a double-byte character set, we still
462	# might exceed the max size (8060) for a record. We could calc the
463	# total requested record size, and adjust accordingly. Meh.
464	return u"VARCHAR(%s)" % bytes
465
466
467	class StorageManagerADO_SQLServer(StorageManagerADO):
468
469	typeAdapter = FieldTypeAdapter_SQLServer()
470	toAdapter = AdapterToADOSQL_SQLServer()
471
472	def __init__(self, name, arena, allOptions={}):
473	db.StorageManagerDB.__init__(self, name, arena, allOptions)
474
475	self.connstring = allOptions[u'Connect']
476	atoms = self.connatoms()
477	self.dbname = atoms[u'INITIAL CATALOG']
478
479	def create_database(self):
480	# This method hasn't been tested yet for SQL server.
481	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
482	atoms = self.connatoms()
483	atoms['INITIAL CATALOG'] = "tempdb"
484	adoconn.Open("; ".join(["%s=%s" % (k, v) for k, v in atoms.iteritems()]))
485	adoconn.Execute("CREATE DATABASE %s" % self.identifier(self.dbname))
486	adoconn.Close()
487
488	def drop_database(self):
489	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
490	atoms = self.connatoms()
491	atoms['INITIAL CATALOG'] = "tempdb"
492	adoconn.Open("; ".join(["%s=%s" % (k, v) for k, v in atoms.iteritems()]))
493	adoconn.Execute("DROP DATABASE %s;" % self.identifier(self.dbname))
494	adoconn.Close()
495
496
497	###########################################################################
498	## ##
499	## MS Access ##
500	## ##
501	###########################################################################
502
503
504	class ADOSQLDecompiler_MSAccess(ADOSQLDecompiler):
505	sql_cmp_op = ('<', '<=', '=', '<>', '>', '>=', 'in', 'not in')
506
507	def dejavu_now(self):
508	return "Now()"
509
510	def dejavu_today(self):
511	return "DateValue(Now())"
512
513	def dejavu_year(self, x):
514	return "Year(" + x + ")"
515
516
517	class FieldTypeAdapter_MSAccess(db.FieldTypeAdapter):
518
519	numeric_max_precision = 15
520
521	def coerce_bool(self, cls, key): return u"BIT"
522
523	def coerce_datetime_datetime(self, cls, key): return u"DATETIME"
524	def coerce_datetime_date(self, cls, key): return u"DATETIME"
525	def coerce_datetime_time(self, cls, key): return u"DATETIME"
526
527	def numeric_type(self, cls, key, precision, scale):
528	if precision > self.numeric_max_precision:
529	warnings.warn("Decimal precision %s > maximum %s for %s.%s, "
530	"using %s. Values may be stored incorrectly."
531	% (precision, self.numeric_max_precision,
532	cls.__name__, key, self.__class__.__name__))
533	precision = self.numeric_max_precision
534	if scale > 4:
535	warnings.warn("Decimal scale %s > maximum 4 for %s.%s, "
536	"using %s. Values may be stored incorrectly."
537	% (scale, cls.__name__, key,
538	self.__class__.__name__))
539
540	# MS Access doesn't let us control precision and scale directly.
541	# From http://support.microsoft.com/?kbid=104977
542	# ORACLE number Microsoft Access data type
543	# ---------------------------------------------------
544	# Scale = 0 and
545	# precision <= 4 Integer
546	# precision <= 9 Long Integer
547	# precision <= 15 Double
548	# Scale > 0 and <= 4
549	# precision <= 15 Double
550	# Scale > 4 and/or
551	# precision > 15 Text
552	if scale == 0:
553	if precision <= 4:
554	return "INTEGER"
555	elif precision <= 9:
556	return "LONG"
557	return "DOUBLE"
558
559	def coerce_decimal_Decimal(self, cls, key):
560	prop = getattr(cls, key)
561	precision = int(prop.hints.get('precision', '0'))
562	if precision == 0:
563	precision = decimal.getcontext().prec
564	# Assume most people use decimal for money; default scale = 2.
565	scale = int(prop.hints.get(u'scale', 2))
566	return self.numeric_type(cls, key, precision, scale)
567
568	def coerce_fixedpoint_FixedPoint(self, cls, key):
569	prop = getattr(cls, key)
570	precision = int(prop.hints.get('precision', '0'))
571	if precision == 0:
572	precision = self.numeric_max_precision
573	# Assume most people use decimal for money; default scale = 2.
574	scale = int(prop.hints.get(u'scale', 2))
575	return self.numeric_type(cls, key, precision, scale)
576
577	def coerce_int(self, cls, key):
578	prop = getattr(cls, key)
579	bytes = int(prop.hints.get(u'bytes', '4'))
580	if bytes == 1:
581	return "BIT"
582	else:
583	return u"INTEGER"
584
585	def coerce_long(self, cls, key):
586	prop = getattr(cls, key)
587	bytes = int(prop.hints.get(u'bytes', 0))
588	return self.numeric_type(cls, key, precision, 0)
589
590	def coerce_str(self, cls, key):
591	# The bytes hint shall not reflect the usual 4-byte base for varchar.
592	prop = getattr(cls, key)
593	bytes = int(prop.hints.get(u'bytes', '0'))
594	if bytes and bytes <= 255:
595	# 255 chars is the upper limit for TEXT / VARCHAR in MS Access.
596	return u"VARCHAR(%s)" % bytes
597	else:
598	# MEMO is 1 GB max when set programatically (only 64K when set
599	# in Access UI). But then, 1 GB is the limit for the whole DB.
600	return u"MEMO"
601
602
603	class AdapterToADOSQL_MSAccess(db.AdapterToSQL):
604	"""Coerce Expression constants to ADO SQL."""
605
606	escapes = [("'", "''")]
607	like_escapes = [("%", "[%]"), ("_", "[_]")]
608
609	def coerce_datetime_datetime(self, value):
610	return (u'#%s/%s/%s %02d:%02d:%02d#' %
611	(value.month, value.day, value.year,
612	value.hour, value.minute, value.second))
613
614	def coerce_datetime_date(self, value):
615	return u'#%s/%s/%s#' % (value.month, value.day, value.year)
616
617	def coerce_datetime_time(self, value):
618	return u'#%02d:%02d:%02d#' % (value.hour, value.minute, value.second)
619
620
621	class StorageManagerADO_MSAccess(StorageManagerADO):
622	# Jet Connections and Recordsets are always free-threaded.
623
624	decompiler = ADOSQLDecompiler_MSAccess
625	typeAdapter = FieldTypeAdapter_MSAccess()
626	toAdapter = AdapterToADOSQL_MSAccess()
627
628	def __init__(self, name, arena, allOptions={}):
629	db.StorageManagerDB.__init__(self, name, arena, allOptions)
630
631	self.connstring = allOptions[u'Connect']
632	atoms = self.connatoms()
633	self.dbname = (atoms.get(u'DATA SOURCE') or
634	atoms.get(u'DATA SOURCE NAME') or
635	atoms.get(u'DBQ'))
636	# MS Access can't use a pool, because there doesn't seem
637	# to be a commit timeout.
638	self.pool = None
639	self.threaded = False
640	self.debug_connections = True
641
642	def create_database(self):
643	# By not providing an Engine Type, it defaults to 5 = Access 2000.
644	cat = win32com.client.Dispatch(r'ADOX.Catalog')
645	cat.Create(self.connstring)
646	cat.ActiveConnection.Close()
647
648	def drop_database(self):
649	import os
650	# This should accept relative or absolute paths
651	if os.path.exists(self.dbname):
652	os.remove(self.dbname)
653
654
655	def gen_py():
656	# Auto generate .py support for ADO 2.7+
657	print 'Please wait while support for ADO 2.7+ is verified...'
658	CLSID = '{EF53050B-882E-4776-B643-EDA472E8E3F2}'
659	return win32com.client.gencache.EnsureModule(CLSID, 0, 2, 7)
660
661
662	if __name__ == '__main__':
663	gen_py()

Note: See TracBrowser for help on using the browser.

root/trunk/storage/storeado.py

Download in other formats: