Revision Log

storeado.py

Revision 168 (checked in by fumanchu, 6 years ago)	Fixed some errors when fetch returns empty col_defs.
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	adSchemaColumns = 3
48	adSchemaTables = 20
49
50	adUseClient = 3
51
52	# 12/30/1899, the zero-Date for ADO = 693594
53	zeroHour = datetime.date(1899, 12, 30).toordinal()
54
55
56	def time_from_com(com_date):
57	"""Return a valid datetime.time from a COM date or time object."""
58	hour, minute = divmod(86400 * (float(com_date) % 1), 3600)
59	minute, second = divmod(minute, 60)
60	# Must do both int() and round() or we'll be up to 1 second off.
61	hour = int(round(hour))
62	minute = int(round(minute))
63	second = int(round(second))
64
65	while second > 59:
66	second -= 60
67	minute += 1
68	while second < 0:
69	second += 60
70	minute -= 1
71	while minute > 59:
72	minute -= 60
73	hour += 1
74	while minute < 0:
75	minute += 60
76	hour -= 1
77	while hour > 23:
78	hour -= 24
79	day += 1
80	while hour < 0:
81	hour += 24
82
83	return datetime.time(hour, minute, second)
84
85
86	class AdapterFromADO(db.AdapterFromDB):
87	"""Coerce incoming values from ADO to Dejavu datatypes."""
88
89	def coerce_datetime_datetime(self, value, coltype):
90	# Illegal Date/Time values will crash the
91	# app when using value.Format(). Therefore,
92	# grab the value and figure the date ourselves.
93	# Use 1-second resolution only.
94	if isinstance(value, basestring):
95	if value:
96	try:
97	return datetime.datetime(int(value[0:4]), int(value[4:6]),
98	int(value[6:8]))
99	except Exception, x:
100	raise ValueError("'%s' %s" % (value, type(value)))
101	else:
102	return None
103	else:
104	# For some reason, we need both float and int.
105	aDate = datetime.date.fromordinal(int(float(value)) + zeroHour)
106	return datetime.datetime.combine(aDate, time_from_com(value))
107
108	def coerce_datetime_date(self, value, coltype):
109	# See coerce_datetime
110	if isinstance(value, basestring):
111	if value:
112	try:
113	return datetime.date(int(value[0:4]), int(value[4:6]),
114	int(value[6:8]))
115	except Exception, x:
116	raise ValueError("'%s' %s" % (value, type(value)))
117	else:
118	return None
119	else:
120	return datetime.date.fromordinal(int(float(value)) + zeroHour)
121
122	def coerce_datetime_time(self, value, coltype):
123	# See coerce_datetime
124	return time_from_com(value)
125
126	def coerce_fixedpoint_FixedPoint(self, value, coltype):
127	if coltype == 0x06:
128	# Currency
129	value = value[1] / 10000.0
130	return fixedpoint.FixedPoint(value)
131
132	def coerce_float(self, value, coltype):
133	if coltype == 0x06:
134	# Currency
135	value = value[1] / 10000.0
136	return float(value)
137
138	def coerce_int(self, value, coltype):
139	if coltype == 0x0b:
140	# Boolean
141	return value != 0
142	return int(value)
143
144	coerce_bool = coerce_int
145
146	def coerce_unicode(self, value, coltype):
147	if isinstance(value, unicode):
148	# For some reason, inValue is already a unicode object.
149	return value
150	if isinstance(value, (basestring, buffer)):
151	try:
152	return unicode(value, "ISO-8859-1")
153	except UnicodeError:
154	raise StandardError(type(value))
155	return unicode(value)
156
157
158
159	class AdapterToADOFields(storage.Adapter):
160	"""Coerce outgoing values from Dejavu datatypes to ADO.Field types."""
161
162	def noop(self, value):
163	return value
164
165	def coerce_bool(self, value):
166	if value:
167	return True
168	return False
169
170	def coerce_datetime_datetime(self, value):
171	if value is None:
172	return None
173	return self.coerce_datetime_date(value) + self.coerce_datetime_time(value)
174
175	def coerce_datetime_date(self, value):
176	if value is None:
177	return None
178	return value.toordinal() - zeroHour
179
180	def coerce_datetime_time(self, value):
181	if value is None:
182	return None
183	return ((value.second + (value.minute * 60) + (value.hour * 3600))
184	/ 86400.0)
185
186	def do_pickle(self, value):
187	# We must not use a pickle format other than 0, because binary
188	# strings are not safe for all DB string fields.
189	return pickle.dumps(value)
190
191	coerce_dict = do_pickle
192
193	def coerce_fixedpoint_FixedPoint(self, value):
194	if value is None:
195	return None
196	return float(value)
197
198	coerce_float = noop
199	coerce_int = noop
200
201	coerce_list = do_pickle
202
203	coerce_long = noop
204	coerce_str = noop
205
206	coerce_tuple = do_pickle
207
208	coerce_unicode = noop
209
210
211	class ADOSQLDecompiler(db.SQLDecompiler):
212
213	def visit_COMPARE_OP(self, lo, hi):
214	op2, op1 = self.stack.pop(), self.stack.pop()
215	if op1 is db.cannot_represent or op2 is db.cannot_represent:
216	self.stack.append(db.cannot_represent)
217	return
218
219	op = lo + (hi << 8)
220	if op in (6, 7): # in, not in
221	# Looking for text in a field. Use Like (reverse terms).
222	# LIKE is case-insensitive in MS SQL Server (and there
223	# doesn't seem to be a way around it). Use icontainedby
224	# and just mark imperfect.
225	value = self.dejavu_icontainedby(op1, op2)
226	if op == 7:
227	value = "NOT " + value
228	self.stack.append(value)
229	self.imperfect = True
230	elif op1 == 'NULL':
231	if op == 2:
232	self.stack.append(op2 + " IS NULL")
233	elif op == 3:
234	self.stack.append(op2 + " IS NOT NULL")
235	else:
236	raise ValueError("Non-equality Null comparisons not allowed.")
237	elif op2 == 'NULL':
238	if op == 2:
239	self.stack.append(op1 + " IS NULL")
240	elif op == 3:
241	self.stack.append(op1 + " IS NOT NULL")
242	else:
243	raise ValueError("Non-equality Null comparisons not allowed.")
244	else:
245	if (isinstance(op2, db.ConstWrapper)
246	and isinstance(op2.basevalue, basestring)):
247	# ADO comparison operators for strings are case-insensitive
248	# by default. Rather than determine which columns in the DB
249	# might be case-sensitive, just flag them all as imperfect.
250	# TODO: might be possible to cast both to varbinary, but
251	# that may cause problems with unicode columns.
252	self.imperfect = True
253	self.stack.append(op1 + " " + self.sql_cmp_op[op] + " " + op2)
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 sql_name(self, name, quoted=True):
328	if quoted:
329	name = '[' + name + ']'
330	return name
331
332	def _get_conn(self):
333	conn = win32com.client.Dispatch(r'ADODB.Connection')
334	conn.Open(self.connstring)
335	return conn
336
337	def execute(self, query, conn=None):
338	if conn is None:
339	conn = self.connection()
340	self.arena.log(query, dejavu.LOGSQL)
341	try:
342	conn.Execute(query)
343	except pywintypes.com_error, x:
344	x.args += (query, )
345	conn = None
346	raise
347
348	def fetch(self, query, conn=None, schema=False):
349	"""fetch(query, conn=None) -> rowdata, columns."""
350	if conn is None:
351	conn = self.connection()
352
353	try:
354	if schema:
355	res = conn.OpenSchema(query)
356	else:
357	self.arena.log(query, dejavu.LOGSQL)
358	res = win32com.client.Dispatch(r'ADODB.Recordset')
359	if self.threaded:
360	# 'conn' will be a ConnectionWrapper object, which .Open
361	# won't accept. Pass the unwrapped connection instead.
362	res.Open(query, conn.conn, adOpenForwardOnly, adLockReadOnly)
363	else:
364	res.Open(query, conn, adOpenForwardOnly, adLockReadOnly)
365	except pywintypes.com_error, x:
366	try:
367	res.Close()
368	except:
369	pass
370	x.args += (query, )
371	conn = None
372	# "raise x" here or we could get the traceback of the inner try.
373	raise x
374
375	columns = [(x.Name, x.Type) for x in res.Fields]
376
377	data = []
378	if not(res.BOF and res.EOF):
379	# We tried .MoveNext() and lots of Fields.Item() calls.
380	# Using GetRows() beats that time by about 2/3.
381	data = res.GetRows()
382	# Convert cols x rows -> rows x cols
383	data = zip(*data)
384	try:
385	res.Close()
386	except:
387	pass
388	conn = None
389
390	return data, columns
391
392	def version(self):
393	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
394	return "ADO Version: %s" % adoconn.Version
395
396	def get_tables(self, conn=None):
397	return self.fetch(adSchemaTables, conn=conn, schema=True)
398
399	def has_storage(self, cls):
400	data, col_defs = self.get_tables()
401	names = [x[2] for x in data]
402	return self.table_name(cls.__name__, quoted=False) in names
403
404	def get_columns(self, conn=None):
405	return self.fetch(adSchemaColumns, conn=conn, schema=True)
406
407	def rename_property(self, cls, oldname, newname):
408	clsname = cls.__name__
409	tblname = self.table_name(clsname, quoted=False)
410	oldname = self.column_name(clsname, oldname, quoted=False)
411	newname = self.column_name(clsname, newname, quoted=False)
412
413	conn = self.connection()
414	try:
415	cat = win32com.client.Dispatch(r'ADOX.Catalog')
416	cat.ActiveConnection = conn
417	cat.Tables(tblname).Columns(oldname).Name = newname
418	except pywintypes.com_error, x:
419	conn = None
420	cat = None
421	raise
422
423
424	###########################################################################
425	## ##
426	## SQL Server ##
427	## ##
428	###########################################################################
429
430
431	class AdapterToADOSQL_SQLServer(db.AdapterToSQL):
432
433	escapes = [("'", "''")]
434	like_escapes = [("%", "[%]"), ("_", "[_]")]
435
436	# These are not the same as coerce_bool (which is used on one side of
437	# a comparison). Instead, these are used when the whole (sub)expression
438	# is True or False, e.g. "WHERE TRUE", or "WHERE TRUE and 'a'.'b' = 3".
439	bool_true = "(1=1)"
440	bool_false = "(1=0)"
441
442	def coerce_bool(self, value):
443	if value:
444	return '1'
445	return '0'
446
447
448	class FieldTypeAdapter_SQLServer(db.FieldTypeAdapter):
449
450	numeric_max_precision = 38
451
452	def coerce_bool(self, cls, key): return u"BIT"
453
454	def coerce_datetime_datetime(self, cls, key):
455	return u"DATETIME"
456
457	def coerce_datetime_date(self, cls, key):
458	return u"DATETIME"
459
460	def coerce_datetime_time(self, cls, key):
461	return u"DATETIME"
462
463	def coerce_str(self, cls, key):
464	# The bytes hint does not reflect the usual 4-byte base for varchar.
465	prop = getattr(cls, key)
466	bytes = int(prop.hints.get(u'bytes', '0'))
467	if bytes == 0:
468	# Okay, what the @#$%& is wrong with Redmond??!?! We can't even
469	# compare TEXT or NTEXT fields??!? Fine. We'll deny such, and
470	# warn the deployer with less swearing and exclamation points.
471	warnings.warn("You have defined a string property without "
472	"limiting its length. Microsoft SQL Server does "
473	"not allow comparisons on string fields larger "
474	"than 8000 characters. Some of your data may be "
475	"truncated.", dejavu.StorageWarning)
476	bytes = 8000
477	# 8000 bytes is the absolute upper limit, based on T_SQL docs for
478	# varchar/varbinary. If there are further fields defined for the
479	# class, or the codepage uses a double-byte character set, we still
480	# might exceed the max size (8060) for a record. We could calc the
481	# total requested record size, and adjust accordingly. Meh.
482	return u"VARCHAR(%s)" % bytes
483
484
485	class StorageManagerADO_SQLServer(StorageManagerADO):
486
487	typeAdapter = FieldTypeAdapter_SQLServer()
488	toAdapter = AdapterToADOSQL_SQLServer()
489
490	def __init__(self, name, arena, allOptions={}):
491	db.StorageManagerDB.__init__(self, name, arena, allOptions)
492
493	self.connstring = allOptions[u'Connect']
494	atoms = self.connatoms()
495	self.dbname = atoms[u'INITIAL CATALOG']
496
497	def create_database(self):
498	# This method hasn't been tested yet for SQL server.
499	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
500	atoms = self.connatoms()
501	atoms['INITIAL CATALOG'] = "tempdb"
502	adoconn.Open("; ".join(["%s=%s" % (k, v) for k, v in atoms.iteritems()]))
503	adoconn.Execute("CREATE DATABASE %s" % self.sql_name(self.dbname))
504	adoconn.Close()
505
506	def drop_database(self):
507	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
508	atoms = self.connatoms()
509	atoms['INITIAL CATALOG'] = "tempdb"
510	adoconn.Open("; ".join(["%s=%s" % (k, v) for k, v in atoms.iteritems()]))
511	adoconn.Execute("DROP DATABASE %s;" % self.sql_name(self.dbname))
512	adoconn.Close()
513
514	def add_property(self, cls, name):
515	clsname = cls.__name__
516	# SQL Server doesn't use the "COLUMN" keyword with "ADD"
517	self.execute("ALTER TABLE %s ADD %s %s;" %
518	(self.table_name(clsname),
519	self.column_name(clsname, name),
520	self.typeAdapter.coerce(cls, name),
521	))
522
523	def rename_property(self, cls, oldname, newname):
524	clsname = cls.__name__
525	oldname = self.column_name(clsname, oldname, quoted=False)
526	newname = self.column_name(clsname, newname, quoted=False)
527	if oldname != newname:
528	self.execute("EXEC sp_rename '%s.%s', '%s', 'COLUMN'" %
529	(self.table_name(clsname), oldname, newname))
530
531
532	###########################################################################
533	## ##
534	## MS Access ##
535	## ##
536	###########################################################################
537
538
539	class ADOSQLDecompiler_MSAccess(ADOSQLDecompiler):
540	sql_cmp_op = ('<', '<=', '=', '<>', '>', '>=', 'in', 'not in')
541
542	def dejavu_now(self):
543	return "Now()"
544
545	def dejavu_today(self):
546	return "DateValue(Now())"
547
548	def dejavu_year(self, x):
549	return "Year(" + x + ")"
550
551
552	class FieldTypeAdapter_MSAccess(db.FieldTypeAdapter):
553
554	numeric_max_precision = 15
555
556	def coerce_bool(self, cls, key): return u"BIT"
557
558	def coerce_datetime_datetime(self, cls, key): return u"DATETIME"
559	def coerce_datetime_date(self, cls, key): return u"DATETIME"
560	def coerce_datetime_time(self, cls, key): return u"DATETIME"
561
562	def numeric_type(self, cls, key, precision, scale):
563	if precision > self.numeric_max_precision:
564	warnings.warn("Decimal precision %s > maximum %s for %s.%s, "
565	"using %s. Values may be stored incorrectly."
566	% (precision, self.numeric_max_precision,
567	cls.__name__, key, self.__class__.__name__),
568	dejavu.StorageWarning)
569	precision = self.numeric_max_precision
570	if scale > 4:
571	warnings.warn("Decimal scale %s > maximum 4 for %s.%s, "
572	"using %s. Values may be stored incorrectly."
573	% (scale, cls.__name__, key,
574	self.__class__.__name__),
575	dejavu.StorageWarning)
576
577	# MS Access doesn't let us control precision and scale directly.
578	# From http://support.microsoft.com/?kbid=104977
579	# ORACLE number Microsoft Access data type
580	# ---------------------------------------------------
581	# Scale = 0 and
582	# precision <= 4 Integer
583	# precision <= 9 Long Integer
584	# precision <= 15 Double
585	# Scale > 0 and <= 4
586	# precision <= 15 Double
587	# Scale > 4 and/or
588	# precision > 15 Text
589	if scale == 0:
590	if precision <= 4:
591	return "INTEGER"
592	elif precision <= 9:
593	return "LONG"
594	return "DOUBLE"
595
596	def coerce_decimal_Decimal(self, cls, key):
597	prop = getattr(cls, key)
598	precision = int(prop.hints.get('precision', '0'))
599	if precision == 0:
600	precision = decimal.getcontext().prec
601	# Assume most people use decimal for money; default scale = 2.
602	scale = int(prop.hints.get(u'scale', 2))
603	return self.numeric_type(cls, key, precision, scale)
604
605	def coerce_fixedpoint_FixedPoint(self, cls, key):
606	prop = getattr(cls, key)
607	precision = int(prop.hints.get('precision', '0'))
608	if precision == 0:
609	precision = self.numeric_max_precision
610	# Assume most people use decimal for money; default scale = 2.
611	scale = int(prop.hints.get(u'scale', 2))
612	return self.numeric_type(cls, key, precision, scale)
613
614	def coerce_int(self, cls, key):
615	prop = getattr(cls, key)
616	bytes = int(prop.hints.get(u'bytes', '4'))
617	if bytes == 1:
618	return "BIT"
619	else:
620	return u"INTEGER"
621
622	def coerce_long(self, cls, key):
623	prop = getattr(cls, key)
624	bytes = int(prop.hints.get(u'bytes', 0))
625	return self.numeric_type(cls, key, precision, 0)
626
627	def coerce_str(self, cls, key):
628	# The bytes hint shall not reflect the usual 4-byte base for varchar.
629	prop = getattr(cls, key)
630	bytes = int(prop.hints.get(u'bytes', '0'))
631	if bytes and bytes <= 255:
632	# 255 chars is the upper limit for TEXT / VARCHAR in MS Access.
633	return u"VARCHAR(%s)" % bytes
634	else:
635	# MEMO is 1 GB max when set programatically (only 64K when set
636	# in Access UI). But then, 1 GB is the limit for the whole DB.
637	for assoc in cls._associations.itervalues():
638	if assoc.nearKey == key:
639	warnings.warn("Memo fields cannot be used as join keys. "
640	"You should set %s.%s(hints={'bytes': 255})"
641	% (cls.__name__, key),
642	dejavu.StorageWarning)
643	return u"MEMO"
644
645
646	class AdapterToADOSQL_MSAccess(db.AdapterToSQL):
647	"""Coerce Expression constants to ADO SQL."""
648
649	escapes = [("'", "''")]
650	like_escapes = [("%", "[%]"), ("_", "[_]")]
651
652	def coerce_datetime_datetime(self, value):
653	return (u'#%s/%s/%s %02d:%02d:%02d#' %
654	(value.month, value.day, value.year,
655	value.hour, value.minute, value.second))
656
657	def coerce_datetime_date(self, value):
658	return u'#%s/%s/%s#' % (value.month, value.day, value.year)
659
660	def coerce_datetime_time(self, value):
661	return u'#%02d:%02d:%02d#' % (value.hour, value.minute, value.second)
662
663
664	class StorageManagerADO_MSAccess(StorageManagerADO):
665	# Jet Connections and Recordsets are always free-threaded.
666
667	use_asterisk_to_get_all = True
668
669	decompiler = ADOSQLDecompiler_MSAccess
670	typeAdapter = FieldTypeAdapter_MSAccess()
671	toAdapter = AdapterToADOSQL_MSAccess()
672
673	def __init__(self, name, arena, allOptions={}):
674	db.StorageManagerDB.__init__(self, name, arena, allOptions)
675
676	self.connstring = allOptions[u'Connect']
677	atoms = self.connatoms()
678	self.dbname = (atoms.get(u'DATA SOURCE') or
679	atoms.get(u'DATA SOURCE NAME') or
680	atoms.get(u'DBQ'))
681	# MS Access can't use a pool, because there doesn't seem
682	# to be a commit timeout.
683	self.pool = None
684	self.threaded = False
685	self.debug_connections = True
686
687	def create_database(self):
688	# By not providing an Engine Type, it defaults to 5 = Access 2000.
689	cat = win32com.client.Dispatch(r'ADOX.Catalog')
690	cat.Create(self.connstring)
691	cat.ActiveConnection.Close()
692
693	def drop_database(self):
694	import os
695	# This should accept relative or absolute paths
696	if os.path.exists(self.dbname):
697	os.remove(self.dbname)
698
699
700	def gen_py():
701	# Auto generate .py support for ADO 2.7+
702	print 'Please wait while support for ADO 2.7+ is verified...'
703	CLSID = '{EF53050B-882E-4776-B643-EDA472E8E3F2}'
704	return win32com.client.gencache.EnsureModule(CLSID, 0, 2, 7)
705
706
707	if __name__ == '__main__':
708	gen_py()

Note: See TracBrowser for help on using the browser.

root/tags/1.4.0/storage/storeado.py

Download in other formats: