Revision Log

ado.py

Revision 1 (checked in by fumanchu, 6 years ago)	Initial import.
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
6	import pythoncom
7	Empty = pythoncom.Empty
8	clsctx = pythoncom.CLSCTX_SERVER
9
10	import win32com.client
11
12	# InvokeTypes args (always pass as *args)
13	BOF = (1002, 0, 2, (11, 0), ())
14	EOF = (1006, 0, 2, (11, 0), ())
15	Recordset_Fields = (0, 0, 2, (9, 0), ())
16	# This assumes no arguments passed to GetRows
17	Recordset_GetRows = (1016, 0, 1, (12, 0), ((3, 49), (12, 17), (12, 17)), -1, Empty, Empty)
18	Recordset_Close = (1014, 0, 1, (24, 0), (),)
19	Fields_Count = (1, 0, 2, (3, 0), ())
20	Field_Name = (1100, 0, 2, (8, 0), ())
21	Field_Type = (1102, 0, 2, (3, 0), ())
22	Field_Properties = (500, 0, 2, (9, 0), ())
23	Property_Value = (0, 0, 2, (12, 0), ())
24
25	import pywintypes
26	import datetime
27	import time
28
29	try:
30	import cPickle as pickle
31	except ImportError:
32	import pickle
33
34	import threading
35	import warnings
36
37
38	import geniusql
39	from geniusql import errors, typerefs
40
41	adOpenForwardOnly = 0
42	adOpenKeyset = 1
43	adOpenDynamic = 2
44	adOpenStatic = 3
45
46	adLockReadOnly = 1
47	adLockPessimistic = 2
48	adLockOptimistic = 3
49	adLockBatchOptimistic = 4
50
51	adSchemaColumns = 4
52	adSchemaIndexes = 12
53	adSchemaTables = 20
54	adSchemaPrimaryKeys = 28
55
56	adUseClient = 3
57
58	# 12/30/1899, the zero-Date for ADO = 693594
59	# "Sure, there are two 4-byte integers stored. But they are
60	# packed together into a BINARY(8). The first 4-byte being
61	# the elapsed number days since SQL Server's base date of
62	# 1900-01-01. The Second 4-bytes Store the Time of Day
63	# Represented as the Number of Milliseconds After Midnight."
64	# http://www.sql-server-performance.com/fk_datetime.asp
65	zeroHour = datetime.date(1899, 12, 30).toordinal()
66
67	# Note also that SQL Server allows DATETIME in the range:
68	# "1753-01-01 00:00:00.0" to "9999-12-31 23:59:59.997".
69
70	dbtypes = {
71	0: 'EMPTY', 2: 'SMALLINT',
72	3: 'INTEGER', 4: 'SINGLE',
73	5: 'DOUBLE', 6: 'CURRENCY',
74	7: 'DATE', 8: 'BSTR',
75	9: 'IDISPATCH', 10: 'ERROR',
76	11: 'BOOLEAN', 12: 'VARIANT',
77	13: 'IUNKNOWN', 14: 'DECIMAL',
78	16: 'TINYINT', 17: 'UNSIGNEDTINYINT',
79	18: 'UNSIGNEDSMALLINT', 19: 'UNSIGNEDINT',
80	20: 'BIGINT', 21: 'UNSIGNEDBIGINT',
81	72: 'GUID', 128: 'BINARY',
82	129: 'CHAR', 130: 'WCHAR',
83	131: 'NUMERIC', 132: 'USERDEFINED',
84	133: 'DBDATE', 134: 'DBTIME',
85	135: 'DBTIMESTAMP', 200: 'VARCHAR',
86	201: 'LONGVARCHAR', 202: 'VARWCHAR',
87	203: 'LONGVARWCHAR', 204: 'VARBINARY',
88	205: 'LONGVARBINARY'
89	}
90
91	DBCOLUMNFLAGS_WRITE = 0x4
92	DBCOLUMNFLAGS_WRITEUNKNOWN = 0x8
93	DBCOLUMNFLAGS_ISFIXEDLENGTH = 0x10
94	DBCOLUMNFLAGS_ISNULLABLE = 0x20
95	DBCOLUMNFLAGS_MAYBENULL = 0x40
96	DBCOLUMNFLAGS_ISLONG = 0x80
97	DBCOLUMNFLAGS_ISROWID = 0x100
98	DBCOLUMNFLAGS_ISROWVER = 0x200
99	DBCOLUMNFLAGS_CACHEDEFERRED = 0x1000
100
101
102	def time_from_com(com_date):
103	"""Return a valid datetime.time from a COM date or time object."""
104	hour, minute = divmod(86400 * (float(com_date) % 1), 3600)
105	minute, second = divmod(minute, 60)
106	# Must do both int() and round() or we'll be up to 1 second off.
107	hour = int(round(hour))
108	minute = int(round(minute))
109	second = int(round(second))
110
111	while second > 59:
112	second -= 60
113	minute += 1
114	while second < 0:
115	second += 60
116	minute -= 1
117	while minute > 59:
118	minute -= 60
119	hour += 1
120	while minute < 0:
121	minute += 60
122	hour -= 1
123	while hour > 23:
124	hour -= 24
125	day += 1
126	while hour < 0:
127	hour += 24
128
129	return datetime.time(hour, minute, second)
130
131
132	class AdapterFromADO(geniusql.AdapterFromDB):
133	"""Coerce incoming values from ADO to Python datatypes."""
134
135	encoding = 'ISO-8859-1'
136
137	def coerce_any_to_datetime_datetime(self, value):
138	# Illegal Date/Time values will crash the
139	# app when using value.Format(). Therefore,
140	# grab the value and figure the date ourselves.
141	# Use 1-second resolution only.
142	if isinstance(value, basestring):
143	if value:
144	try:
145	return datetime.datetime(int(value[0:4]), int(value[4:6]),
146	int(value[6:8]))
147	except Exception:
148	raise ValueError("'%s' %s" % (value, type(value)))
149	else:
150	return None
151	else:
152	# For some reason, we need both float and int.
153	aDate = datetime.date.fromordinal(int(float(value)) + zeroHour)
154	return datetime.datetime.combine(aDate, time_from_com(value))
155
156	def coerce_any_to_datetime_date(self, value):
157	# See coerce_any_to_datetime
158	if isinstance(value, basestring):
159	if value:
160	try:
161	return datetime.date(int(value[0:4]), int(value[4:6]),
162	int(value[6:8]))
163	except Exception:
164	raise ValueError("'%s' %s" % (value, type(value)))
165	else:
166	return None
167	else:
168	return datetime.date.fromordinal(int(float(value)) + zeroHour)
169
170	def coerce_any_to_datetime_time(self, value):
171	# See coerce_any_to_datetime
172	return time_from_com(value)
173
174	def coerce_any_to_decimal_Decimal(self, value):
175	# pywin32 build 205 began support for returning
176	# COM Currency objects as decimal objects.
177	# See http://pywin32.cvs.sourceforge.net/pywin32/pywin32/CHANGES.txt?view=markup
178	if not isinstance(value, typerefs.decimal.Decimal):
179	value = str(value)
180	value = typerefs.decimal.Decimal(value)
181	return value
182
183	def coerce_CURRENCY_to_float(self, value):
184	if isinstance(value, tuple):
185	# See http://groups.google.com/group/comp.lang.python/
186	# browse_frm/thread/fed03c64735c9e9c
187	value = map(long, value)
188	return ((value[1] & 0xFFFFFFFFL) \| (value[0] << 32)) / 1e4
189	return float(value)
190
191	def coerce_CURRENCY_to_decimal_Decimal(self, value):
192	# pywin32 build 205 began support for returning
193	# COM Currency objects as decimal objects.
194	# See http://pywin32.cvs.sourceforge.net/pywin32/pywin32/CHANGES.txt?view=markup
195	if not isinstance(value, typerefs.decimal.Decimal):
196	# See http://groups.google.com/group/comp.lang.python/
197	# browse_frm/thread/fed03c64735c9e9c
198	value = map(long, value)
199	value = (value[1] & 0xFFFFFFFFL) \| (value[0] << 32)
200	return typerefs.decimal.Decimal(value) / 10000
201	return value
202
203	def coerce_CURRENCY_to_fixedpoint_FixedPoint(self, value):
204	if isinstance(value, typerefs.decimal.Decimal):
205	value = str(value)
206	scale = 0
207	atoms = value.rsplit(".", 1)
208	if len(atoms) > 1:
209	scale = len(atoms[-1])
210	return typerefs.fixedpoint.FixedPoint(value, scale)
211	else:
212	# See http://groups.google.com/group/comp.lang.python/
213	# browse_frm/thread/fed03c64735c9e9c
214	value = map(long, value)
215	value = (value[1] & 0xFFFFFFFFL) \| (value[0] << 32)
216	return typerefs.fixedpoint.FixedPoint(value, 4) / 1e4
217
218	def coerce_any_to_unicode(self, value):
219	if isinstance(value, unicode):
220	# For some reason, value is already a unicode object.
221	return value
222
223	if isinstance(value, (basestring, buffer)):
224	try:
225	return unicode(value, self.encoding)
226	except UnicodeError, exc:
227	exc.args += (type(value),)
228	return unicode(value)
229
230
231
232	class ADOSQLDecompiler(geniusql.SQLDecompiler):
233
234	def visit_COMPARE_OP(self, lo, hi):
235	op2, op1 = self.stack.pop(), self.stack.pop()
236	if op1 is geniusql.cannot_represent or op2 is geniusql.cannot_represent:
237	self.stack.append(geniusql.cannot_represent)
238	return
239
240	op = lo + (hi << 8)
241	if op in (6, 7): # in, not in
242	# Looking for text in a field. Use Like (reverse terms).
243	# LIKE is case-insensitive in MS SQL Server (and there
244	# doesn't seem to be a way around it). Use icontainedby
245	# and just mark imperfect.
246	value = self.dejavu_icontainedby(op1, op2)
247	if op == 7:
248	value = "NOT " + value
249	self.stack.append(value)
250	self.imperfect = True
251	elif op1 == 'NULL':
252	if op in (2, 8): # '==', is
253	self.stack.append(op2 + " IS NULL")
254	elif op in (3, 9): # '!=', 'is not'
255	self.stack.append(op2 + " IS NOT NULL")
256	else:
257	raise ValueError("Non-equality Null comparisons not allowed.")
258	elif op2 == 'NULL':
259	if op in (2, 8): # '==', 'is'
260	self.stack.append(op1 + " IS NULL")
261	elif op in (3, 9): # '!=', 'is not'
262	self.stack.append(op1 + " IS NOT NULL")
263	else:
264	raise ValueError("Non-equality Null comparisons not allowed.")
265	else:
266	try:
267	op1, op2 = self._compare_imperfect(op1, op2)
268	except TypeError:
269	self.stack.append(geniusql.cannot_represent)
270	self.imperfect = True
271	return
272
273	if (isinstance(op2, geniusql.ConstWrapper)
274	and isinstance(op2.basevalue, basestring)):
275	atom = self._compare_strings(op1, op, op2)
276	if atom:
277	self.stack.append(atom)
278	return
279	self.stack.append(op1 + " " + self.sql_cmp_op[op] + " " + op2)
280
281	def _compare_strings(self, op1, op, op2):
282	# ADO comparison operators for strings are case-insensitive
283	# by default. Rather than determine which columns in the DB
284	# might be case-sensitive, just flag them all as imperfect.
285	# TODO: might be possible to cast both to varbinary, but
286	# that may cause problems with unicode columns.
287	self.imperfect = True
288
289
290	# --------------------------- Dispatchees --------------------------- #
291
292	def attr_startswith(self, tos, arg):
293	self.imperfect = True
294	return tos + " LIKE '" + self.adapter.escape_like(arg) + "%'"
295
296	def attr_endswith(self, tos, arg):
297	self.imperfect = True
298	return tos + " LIKE '%" + self.adapter.escape_like(arg) + "'"
299
300	def containedby(self, op1, op2):
301	self.imperfect = True
302	if isinstance(op1, ConstWrapper):
303	# Looking for text in a field. Use Like (reverse terms).
304	return op2 + " LIKE '%" + self.adapter.escape_like(op1) + "%'"
305	else:
306	# Looking for field in (a, b, c)
307	atoms = [self.adapter.coerce(x) for x in op2.basevalue]
308	if atoms:
309	return op1 + " IN (" + ", ".join(atoms) + ")"
310	else:
311	# Nothing will match the empty list, so return none.
312	return self.adapter.bool_false
313
314	def dejavu_icontainedby(self, op1, op2):
315	if isinstance(op1, geniusql.ConstWrapper):
316	# Looking for text in a field. Use Like (reverse terms).
317	# LIKE is already case-insensitive in MS SQL Server;
318	# so don't use LOWER().
319	value = op2 + " LIKE '%" + self.adapter.escape_like(op1) + "%'"
320	else:
321	# Looking for field in (a, b, c)
322	atoms = [self.adapter.coerce(x) for x in op2.basevalue]
323	if atoms:
324	return op1 + " IN (" + ", ".join(atoms) + ")"
325	else:
326	# Nothing will match the empty list, so return none.
327	return self.adapter.bool_false
328	return value
329
330	def dejavu_istartswith(self, x, y):
331	# Like is already case-insensitive in ADO; so don't use LOWER().
332	return x + " LIKE '" + self.adapter.escape_like(y) + "%'"
333
334	def dejavu_iendswith(self, x, y):
335	# Like is already case-insensitive in ADO; so don't use LOWER().
336	return x + " LIKE '%" + self.adapter.escape_like(y) + "'"
337
338	def dejavu_ieq(self, x, y):
339	# = is already case-insensitive in ADO.
340	return x + " = " + y
341
342	def dejavu_now(self):
343	return "getdate()"
344
345	def dejavu_today(self):
346	return "DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)"
347
348	def dejavu_year(self, x):
349	return "DATEPART(year, " + x + ")"
350
351	def dejavu_month(self, x):
352	return "DATEPART(month, " + x + ")"
353
354	def dejavu_day(self, x):
355	return "DATEPART(day, " + x + ")"
356
357	def func__builtin___len(self, x):
358	return "Len(" + x + ")"
359
360
361	class ADOTable(geniusql.Table):
362
363	def _add_column(self, column):
364	"""Internal function to add the column to the database."""
365	coldef = self.db.col_def(column)
366	# SQL Server doesn't use the "COLUMN" keyword with "ADD"
367	self.db.execute("ALTER TABLE %s ADD %s;" % (self.qname, coldef))
368
369	def _rename(self, oldcol, newcol):
370	conn = self.db.connection()
371	try:
372	cat = win32com.client.Dispatch(r'ADOX.Catalog')
373	cat.ActiveConnection = conn
374	cat.Tables(self.name).Columns(oldcol.name).Name = newcol.name
375	finally:
376	conn = None
377	cat = None
378
379
380	def connatoms(connstring):
381	atoms = {}
382	for pair in connstring.split(";"):
383	if pair:
384	k, v = pair.split("=", 1)
385	atoms[k.upper().strip()] = v.strip()
386	return atoms
387
388
389	class ADODatabase(geniusql.Database):
390
391	decompiler = ADOSQLDecompiler
392	adapterfromdb = AdapterFromADO()
393	tableclass = ADOTable
394
395	# the amount of time to try to close the db connection
396	# before raising an exception
397	shutdowntimeout = 1 # sec.
398
399
400	# Discovery #
401
402	def _get_tables(self, conn=None):
403	# cols will be
404	# [(u'TABLE_CATALOG', 202), (u'TABLE_SCHEMA', 202), (u'TABLE_NAME', 202),
405	# (u'TABLE_TYPE', 202), (u'TABLE_GUID', 72), (u'DESCRIPTION', 203),
406	# (u'TABLE_PROPID', 19), (u'DATE_CREATED', 7), (u'DATE_MODIFIED', 7)]
407	data, _ = self.fetch(adSchemaTables, conn=conn, schema=True)
408	return [self.tableclass(str(row[2]), self.quote(str(row[2])), self)
409	for row in data
410	# Ignore linked and system tables
411	if row[3] == "TABLE"]
412
413	def _get_table(self, tablename, conn=None):
414	# cols will be
415	# [(u'TABLE_CATALOG', 202), (u'TABLE_SCHEMA', 202), (u'TABLE_NAME', 202),
416	# (u'TABLE_TYPE', 202), (u'TABLE_GUID', 72), (u'DESCRIPTION', 203),
417	# (u'TABLE_PROPID', 19), (u'DATE_CREATED', 7), (u'DATE_MODIFIED', 7)]
418	data, _ = self.fetch(adSchemaTables, conn=conn, schema=True)
419	for row in data:
420	name = str(row[2])
421	if name == tablename:
422	return self.tableclass(name, self.quote(name), self)
423	raise errors.MappingError(tablename)
424
425	def _get_columns(self, tablename, conn=None):
426	# For some reason, adSchemaPrimaryKeys would only return a single
427	# record for a PK that had multiple columns. Use adSchemaIndexes.
428	# coldefs will be:
429	# [(u'TABLE_CATALOG', 202), (u'TABLE_SCHEMA', 202), (u'TABLE_NAME', 202),
430	# (u'INDEX_CATALOG', 202), (u'INDEX_SCHEMA', 202), (u'INDEX_NAME', 202),
431	# (u'PRIMARY_KEY', 11), (u'UNIQUE', 11), (u'CLUSTERED', 11), (u'TYPE', 18),
432	# (u'FILL_FACTOR', 3), (u'INITIAL_SIZE', 3), (u'NULLS', 3),
433	# (u'SORT_BOOKMARKS', 11), (u'AUTO_UPDATE', 11), (u'NULL_COLLATION', 3),
434	# (u'ORDINAL_POSITION', 19), (u'COLUMN_NAME', 202), (u'COLUMN_GUID', 72),
435	# (u'COLUMN_PROPID', 19), (u'COLLATION', 2), (u'CARDINALITY', 21),
436	# (u'PAGES', 3), (u'FILTER_CONDITION', 202), (u'INTEGRATED', 11)]
437	data, _ = self.fetch(adSchemaIndexes, conn=conn, schema=True)
438	pknames = [row[17] for row in data
439	if (tablename == row[2]) and row[6]]
440
441	# columns will be
442	# [(u'TABLE_CATALOG', 202), (u'TABLE_SCHEMA', 202), (u'TABLE_NAME', 202),
443	# (u'COLUMN_NAME', 202), (u'COLUMN_GUID', 72), (u'COLUMN_PROPID', 19),
444	# (u'ORDINAL_POSITION', 19), (u'COLUMN_HASDEFAULT', 11),
445	# (u'COLUMN_DEFAULT', 203), (u'COLUMN_FLAGS', 19), (u'IS_NULLABLE', 11),
446	# (u'DATA_TYPE', 18), (u'TYPE_GUID', 72), (u'CHARACTER_MAXIMUM_LENGTH', 19),
447	# (u'CHARACTER_OCTET_LENGTH', 19), (u'NUMERIC_PRECISION', 18),
448	# (u'NUMERIC_SCALE', 2), (u'DATETIME_PRECISION', 19),
449	# (u'CHARACTER_SET_CATALOG', 202), (u'CHARACTER_SET_SCHEMA', 202),
450	# (u'CHARACTER_SET_NAME', 202), (u'COLLATION_CATALOG', 202),
451	# (u'COLLATION_SCHEMA', 202), (u'COLLATION_NAME', 202),
452	# (u'DOMAIN_CATALOG', 202), (u'DOMAIN_SCHEMA', 202),
453	# (u'DOMAIN_NAME', 202), (u'DESCRIPTION', 203)]
454	data, _ = self.fetch(adSchemaColumns, conn=conn, schema=True)
455
456	cols = []
457	for row in data:
458	# I tried passing criteria to OpenSchema, but passing None is
459	# not the same as passing pythoncom.Empty (which errors).
460	if row[2] != tablename:
461	continue
462
463	dbtype = dbtypes[row[11]]
464	default = row[8]
465	if default is not None:
466	deftype = self.python_type(dbtype)
467	if issubclass(deftype, (int, long)):
468	# We may have stuck extraneous quotes in the default
469	# value when using numeric defaults with MSAccess.
470	if default.startswith("'") and default.endswith("'"):
471	default = default[1:-1]
472	default = deftype(default)
473
474	name = str(row[3])
475	c = geniusql.Column(name, self.quote(name), dbtype, default,
476	key=(name in pknames))
477
478	# This only works for SQL Server. The MSAccessDatabase will
479	# wrap this method and override autoincrement.
480	colflags = int(row[9])
481	if ((colflags & DBCOLUMNFLAGS_ISFIXEDLENGTH)
482	and not (colflags & DBCOLUMNFLAGS_WRITE)):
483	c.autoincrement = True
484
485	if dbtype in ("SMALLINT", "INTEGER", "TINYINT",
486	"UNSIGNEDTINYINT", "UNSIGNEDSMALLINT",
487	"UNSIGNEDINT", "BIGINT", "UNSIGNEDBIGINT"):
488	c.hints['bytes'] = row[15]
489	elif dbtype in ("SINGLE", "DOUBLE"):
490	c.hints['precision'] = row[15]
491	c.hints['scale'] = row[16]
492	elif dbtype == "CURRENCY":
493	# CURRENCY allows 15 places to the left of the decimal point,
494	# and 4 places to the right.
495	c.hints['precision'] = 19
496	c.hints['scale'] = 4
497	elif dbtype in ("DECIMAL", "NUMERIC"):
498	c.hints['precision'] = row[15]
499	c.hints['scale'] = row[16]
500	c.dbtype = "%s(%s, %s)" % (dbtype, row[15], row[16])
501	elif dbtype in ("BSTR", "VARIANT", "BINARY", "CHAR",
502	"VARCHAR", "VARBINARY", "WCHAR", "VARWCHAR"):
503	if row[13]:
504	# row[13] will be a float
505	c.hints['bytes'] = b = int(row[13])
506	else:
507	# I'm kinda guessing on this. If we use "MEMO" in an
508	# MSAccess CREATE statement, it comes back as "WCHAR",
509	# and seems to support over 65536 bytes.
510	c.hints['bytes'] = b = (2 ** 31) - 1
511	c.dbtype = "%s(%s)" % (c.dbtype, b)
512	elif dbtype in ("LONGVARCHAR", "LONGVARBINARY", "LONGVARWCHAR"):
513	if row[13]:
514	# row[13] will be a float
515	c.hints['bytes'] = b = int(row[13])
516	c.dbtype = "%s(%s)" % (c.dbtype, b)
517	else:
518	c.hints['bytes'] = 65535
519
520	cols.append(c)
521	return cols
522
523	def _get_indices(self, tablename=None, conn=None):
524	# cols will be
525	# [(u'TABLE_CATALOG', 202), (u'TABLE_SCHEMA', 202), (u'TABLE_NAME', 202),
526	# (u'INDEX_CATALOG', 202), (u'INDEX_SCHEMA', 202), (u'INDEX_NAME', 202),
527	# (u'PRIMARY_KEY', 11), (u'UNIQUE', 11), (u'CLUSTERED', 11), (u'TYPE', 18),
528	# (u'FILL_FACTOR', 3), (u'INITIAL_SIZE', 3), (u'NULLS', 3),
529	# (u'SORT_BOOKMARKS', 11), (u'AUTO_UPDATE', 11), (u'NULL_COLLATION', 3),
530	# (u'ORDINAL_POSITION', 19), (u'COLUMN_NAME', 202), (u'COLUMN_GUID', 72),
531	# (u'COLUMN_PROPID', 19), (u'COLLATION', 2), (u'CARDINALITY', 21),
532	# (u'PAGES', 3), (u'FILTER_CONDITION', 202), (u'INTEGRATED', 11)]
533	data, _ = self.fetch(adSchemaIndexes, conn=conn, schema=True)
534	indices = []
535	for row in data:
536	# I tried passing criteria to OpenSchema, but passing None is
537	# not the same as passing pythoncom.Empty (which errors).
538	if tablename and row[2] != tablename:
539	continue
540	i = geniusql.Index(row[5], self.quote(row[5]), row[2], row[17], row[7])
541	indices.append(i)
542	return indices
543
544	def python_type(self, dbtype):
545	"""Return a Python type which can store values of the given dbtype."""
546	if dbtype in ("DATE", "DBDATE"):
547	return datetime.date
548	elif dbtype == "DBTIME":
549	return datetime.time
550	elif dbtype in ("DATETIME", "DBTIMESTAMP"):
551	return datetime.datetime
552	elif dbtype in ("SMALLINT", "INTEGER", "TINYINT",
553	"UNSIGNEDTINYINT", "UNSIGNEDSMALLINT",
554	"UNSIGNEDINT"):
555	return int
556	elif dbtype in ("BIT", "BOOLEAN"):
557	return bool
558	elif dbtype in ("BIGINT", "UNSIGNEDBIGINT", "LONG"):
559	return long
560	elif dbtype in ("SINGLE", "DOUBLE", "DOUBLE PRECISION", "REAL"):
561	return float
562
563	for t in ("DECIMAL", "NUMERIC", "CURRENCY"):
564	if dbtype.startswith(t):
565	if typerefs.decimal:
566	return typerefs.decimal.Decimal
567	elif typerefs.fixedpoint:
568	return typerefs.fixedpoint.FixedPoint
569
570	for t in ("BSTR", "VARIANT", "BINARY", "CHAR", "MEMO", "TEXT",
571	"VARCHAR", "LONGVARCHAR", "VARBINARY", "LONGVARBINARY"):
572	if dbtype.startswith(t):
573	return str
574
575	for t in ("WCHAR", "VARWCHAR", "LONGVARWCHAR"):
576	if dbtype.startswith(t):
577	return unicode
578
579	raise TypeError("Database type %r could not be converted "
580	"to a Python type." % dbtype)
581
582
583	# Container #
584
585	def _rename(self, oldtable, newtable):
586	conn = self.connection()
587	try:
588	cat = win32com.client.Dispatch(r'ADOX.Catalog')
589	cat.ActiveConnection = conn
590	cat.Tables(oldtable.name).Name = newtable.name
591	finally:
592	conn = None
593	cat = None
594
595	# Naming #
596
597	def quote(self, name):
598	"""Return name, quoted for use in an SQL statement."""
599	return '[' + name + ']'
600
601	# Connecting #
602
603	ConnectionTimeout = None
604	CommandTimeout = None
605
606	def _get_conn(self):
607	conn = win32com.client.Dispatch(r'ADODB.Connection')
608	conn.Open(self.Connect)
609	if self.ConnectionTimeout is not None:
610	conn.ConnectionTimeout = self.ConnectionTimeout
611	if self.CommandTimeout is not None:
612	conn.CommandTimeout = self.CommandTimeout
613	return conn
614
615	def _del_conn(self, conn):
616	for trial in xrange(self.shutdowntimeout * 10):
617	try:
618	# This may raise "Operation cannot be performed
619	# while executing asynchronously"
620	# if a prior operation has not yet completed.
621	conn.Close()
622	return
623	except pywintypes.com_error, e:
624	try:
625	ecode = e.args[2][-1]
626	except IndexError:
627	ecode = None
628	if ecode == -2146824577:
629	# "Operation cannot be performed while executing asynchronously"
630	# Try again...
631	time.sleep(0.1)
632	continue
633	raise
634
635	def execute(self, query, conn=None):
636	if conn is None:
637	conn = self.connection()
638	if isinstance(query, unicode):
639	query = query.encode(self.adaptertosql.encoding)
640
641	self.log(query)
642	try:
643	if isinstance(conn, geniusql.ConnectionWrapper):
644	# 'conn' is a ConnectionWrapper object, which .Open
645	# won't accept. Pass the unwrapped connection instead.
646	conn = conn.conn
647
648	# Call Execute directly, skipping win32com overhead.
649	conn._oleobj_.InvokeTypes(6, 0, 1, (9, 0),
650	((8, 1), (16396, 18), (3, 49)),
651	query, pythoncom.Missing, -1)
652	except pywintypes.com_error, x:
653	x.args += (query, )
654	conn = None
655	raise
656
657	def fetch(self, query, conn=None, schema=False):
658	"""fetch(query, conn=None) -> rowdata, columns."""
659	if conn is None:
660	conn = self.connection()
661
662	try:
663	if schema:
664	# Call OpenSchema(query) directly, skipping win32com overhead.
665	res = conn._oleobj_.InvokeTypes(19, 0, 1, (9, 0),
666	((3, 1), (12, 17), (12, 17)),
667	query, Empty, Empty)
668	else:
669	self.log(query)
670	if isinstance(conn, geniusql.ConnectionWrapper):
671	# 'conn' is a ConnectionWrapper object, which .Open
672	# won't accept. Pass the unwrapped connection instead.
673	conn = conn.conn
674
675	# Call conn.Open(query) directly, skipping win32com overhead.
676	res, rows_affected = conn._oleobj_.InvokeTypes(6, 0, 1, (9, 0),
677	((8, 1), (16396, 18), (3, 49)),
678	# *args =
679	query, pythoncom.Missing, -1)
680	except pywintypes.com_error, x:
681	try:
682	# Close
683	res.InvokeTypes(*Recordset_Close)
684	except:
685	pass
686	res = None
687	x.args += (query, )
688	conn = None
689	# "raise x" here or we could get the traceback of the inner try.
690	raise x
691
692	# Using xrange(Count) is slightly faster than "for x in resFields".
693	resFields = res.InvokeTypes(*Recordset_Fields)
694	fieldcount = resFields.InvokeTypes(*Fields_Count)
695	columns = []
696	for i in xrange(fieldcount):
697	# Wow. Calling this directly (instead of resFields(i))
698	# results in a 29% speedup for a 1-row fetch() of 48 fields.
699	x = resFields.InvokeTypes(0, 0, 2, (9, 0), ((12, 1),), i)
700
701	# Wow. Calling these directly (instead of x.Name, x.Type)
702	# results in a 40% speedup for a 1-row fetch() of 48 fields.
703	name = x.InvokeTypes(*Field_Name)
704	typ = x.InvokeTypes(*Field_Type)
705	columns.append((name, typ))
706
707	data = []
708	if not (res.InvokeTypes(BOF) and res.InvokeTypes(EOF)):
709	# We tried .MoveNext() and lots of Fields.Item() calls.
710	# Using GetRows() beats that time by about 2/3.
711	# Inlining GetRows results in a 14% speedup for fetch().
712	data = res.InvokeTypes(*Recordset_GetRows)
713
714	# Convert cols x rows -> rows x cols
715	data = zip(*data)
716	try:
717	# Close
718	res.InvokeTypes(*Recordset_Close)
719	except:
720	pass
721	conn = None
722
723	return data, columns
724
725
726	# Transactions #
727
728	def start(self, isolation=None):
729	"""Start a transaction. Not needed if self.implicit_trans is True."""
730	conn = self.get_transaction(new=True)
731	self.execute("BEGIN TRANSACTION;", conn)
732	self.isolate(conn, isolation)
733
734
735
736	###########################################################################
737	## ##
738	## SQL Server ##
739	## ##
740	###########################################################################
741
742
743	class ADOSQLDecompiler_SQLServer(ADOSQLDecompiler):
744
745	def _compare_strings(self, op1, op, op2):
746	# ADO comparison operators for strings are case-insensitive.
747	if op < 6:
748	# ('<', '<=', '==', '!=', '>', '>=')
749	# Some operations on strings can be emulated with the
750	# Convert function.
751	return ("Convert(binary, %s) %s Convert(binary, %s)" %
752	(op1, self.sql_cmp_op[op], op2))
753	else:
754	return ADOSQLDecompiler._compare_strings(self, op1, op, op2)
755
756
757	class AdapterToADOSQL_SQLServer(geniusql.AdapterToSQL):
758
759	encoding = 'ISO-8859-1'
760
761	escapes = [("'", "''")]
762	like_escapes = [("[", "[[]"), ("%", "[%]"), ("_", "[_]"),
763	("?", "[?]"), ("#", "[#]")]
764
765	# These are not the same as coerce_bool_to_any (which is used on one side of
766	# a comparison). Instead, these are used when the whole (sub)expression
767	# is True or False, e.g. "WHERE TRUE", or "WHERE TRUE and 'a'.'b' = 3".
768	bool_true = "(1=1)"
769	bool_false = "(1=0)"
770
771	def coerce_bool_to_any(self, value):
772	if value:
773	return '1'
774	return '0'
775
776	def cast_VARCHAR_to_int(self, colref):
777	return ("(CASE WHEN ISNUMERIC(%s)=1 THEN CAST(%s AS int) END)"
778	% (colref, colref))
779
780
781	class TypeAdapter_SQLServer(geniusql.TypeAdapter):
782
783	# Hm. Docs say 38, but I can't seem to get more than 12 working.
784	# They must mean 38 binary digits; math.log(2 ** 38, 10) = 11.4+
785	numeric_max_precision = 12
786	numeric_max_bytes = 6
787
788	def coerce_bool(self, col):
789	return "BIT"
790
791	def coerce_datetime_datetime(self, col):
792	return "DATETIME"
793
794	def coerce_datetime_date(self, col):
795	return "DATETIME"
796
797	def coerce_datetime_time(self, col):
798	return "DATETIME"
799
800	def int_type(self, bytes):
801	"""Return a datatype which can handle the given number of bytes."""
802	if bytes <= 2:
803	return "SMALLINT"
804	elif bytes <= 4:
805	return "INTEGER"
806	elif bytes <= 8:
807	# BIGINT is usually 8 bytes
808	return "BIGINT"
809	else:
810	# Anything larger than 8 bytes, use decimal/numeric.
811	return "NUMERIC(%s, 0)" % (bytes * 2)
812
813	def coerce_str(self, col):
814	# The bytes hint does not reflect the usual 4-byte base for varchar.
815	bytes = int(col.hints.get('bytes', 255))
816
817	if bytes == 0 or bytes > 8000:
818	# Okay, what the @#$%& is wrong with Redmond??!?! We can't even
819	# compare TEXT or NTEXT fields??!? Fine. We'll deny such, and
820	# warn the deployer with less swearing and exclamation points.
821	errors.warn("You have defined a string property without "
822	"limiting its length. Microsoft SQL Server does "
823	"not allow comparisons on string fields larger "
824	"than 8000 characters. Some of your data may be "
825	"truncated.")
826	bytes = 8000
827
828	# 8000 bytes is the absolute upper limit, based on T_SQL docs for
829	# varchar/varbinary. If there are further fields defined for the
830	# class, or the codepage uses a double-byte character set, we still
831	# might exceed the max size (8060) for a record. We could calc the
832	# total requested record size, and adjust accordingly. Meh.
833	return "VARCHAR(%s)" % bytes
834
835
836	class SQLServerTable(ADOTable):
837
838	def _rename(self, oldcol, newcol):
839	self.db.execute("EXEC sp_rename '%s.%s', '%s', 'COLUMN'" %
840	(self.name, oldcol.name, newcol.name))
841
842
843	class SQLServerDatabase(ADODatabase):
844
845	decompiler = ADOSQLDecompiler_SQLServer
846	tableclass = SQLServerTable
847	adaptertosql = AdapterToADOSQL_SQLServer()
848	typeadapter = TypeAdapter_SQLServer()
849
850	def __init__(self, name, **kwargs):
851	ADODatabase.__init__(self, name, **kwargs)
852	if "2005" in self.version():
853	self.isolation_levels.append("SNAPSHOT")
854
855	def version(self):
856	adoconn = self._template_conn()
857	adov = adoconn.Version
858	data, coldefs = self.fetch("SELECT @@VERSION;", adoconn)
859	sqlv, = data[0]
860	adoconn.Close()
861	del adoconn
862	return "ADO Version: %s\n%s" % (adov, sqlv)
863
864	def _template_conn(self):
865	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
866	atoms = connatoms(self.Connect)
867	atoms['INITIAL CATALOG'] = "tempdb"
868	adoconn.Open("; ".join(["%s=%s" % (k, v) for k, v in atoms.iteritems()]))
869	return adoconn
870
871	def create_database(self):
872	self.lock("Creating database. Transactions not allowed.")
873	try:
874	adoconn = self._template_conn()
875	adoconn.Execute("CREATE DATABASE %s" % self.qname)
876	adoconn.Close()
877	self.clear()
878	finally:
879	self.unlock()
880
881	def drop_database(self):
882	self.lock("Dropping database. Transactions not allowed.")
883	try:
884	# Must shut down all connections to avoid
885	# "being accessed by other users" error.
886	self.connection.shutdown()
887
888	adoconn = self._template_conn()
889	adoconn.Execute("DROP DATABASE %s;" % self.qname)
890	adoconn.Close()
891	self.clear()
892	finally:
893	self.unlock()
894
895	def col_def(self, column):
896	"""Return a clause for the given column for CREATE or ALTER TABLE.
897
898	This will be of the form:
899	name type [DEFAULT x\|IDENTITY(initial, 1) NOT NULL]
900	"""
901	dbtype = column.dbtype
902
903	clause = ""
904	if column.autoincrement:
905	if dbtype not in ("BOOLEAN", "SMALLINT", "INTEGER", "BIGINT"):
906	raise ValueError("SQL Server does not allow IDENTITY "
907	"columns of type %r" % dbtype)
908	clause = " IDENTITY(%s, 1) NOT NULL" % column.initial
909	else:
910	# SQL Server does not allow a column to have
911	# both an IDENTITY clause and a DEFAULT clause.
912	default = column.default or ""
913	if default:
914	clause = self.adaptertosql.coerce(default, dbtype)
915	clause = " DEFAULT %s" % clause
916
917	return '%s %s%s' % (column.qname, dbtype, clause)
918
919	# Transactions #
920
921	default_isolation = "READ COMMITTED"
922
923	def is_lock_error(self, exc):
924	"""If the given exception instance is a lock timeout, return True.
925
926	This should return True for errors which arise from transaction
927	locking timeouts; for example, if the database prevents 'dirty
928	reads' by raising an error.
929	"""
930	# com_error: (-2147352567, 'Exception occurred.',
931	# (0, 'Microsoft OLE DB Provider for SQL Server',
932	# 'Timeout expired', None, 0, -2147217871), None,
933	# "UPDATE [testVet] SET [City] = 'Tehachapi' ... ;")
934	if not isinstance(exc, pywintypes.com_error):
935	return False
936	return exc.args[2][5] == -2147217871
937
938
939
940	###########################################################################
941	## ##
942	## MS Access ##
943	## ##
944	###########################################################################
945
946
947	class ADOSQLDecompiler_MSAccess(ADOSQLDecompiler):
948	sql_cmp_op = ('<', '<=', '=', '<>', '>', '>=', 'in', 'not in')
949
950	def _compare_strings(self, op1, op, op2):
951	# ADO comparison operators for strings are case-insensitive.
952	if op < 6:
953	# ('<', '<=', '==', '!=', '>', '>=')
954	# Some operations on strings can be emulated with the
955	# StrComp function. Oddly enough, "StrComp(x, y) op 0"
956	# is the same as "x op y" in most cases.
957	return "StrComp(%s, %s) %s 0" % (op1, op2, self.sql_cmp_op[op])
958	else:
959	return ADOSQLDecompiler._compare_strings(self, op1, op, op2)
960
961	def dejavu_now(self):
962	return "Now()"
963
964	def dejavu_today(self):
965	return "DateValue(Now())"
966
967	def dejavu_year(self, x):
968	return "Year(" + x + ")"
969
970	def dejavu_month(self, x):
971	return "Month(" + x + ")"
972
973	def dejavu_day(self, x):
974	return "Day(" + x + ")"
975
976
977	class TypeAdapter_MSAccess(geniusql.TypeAdapter):
978	# http://msdn2.microsoft.com/en-us/library/ms714540.aspx
979	# http://office.microsoft.com/en-us/access/HP010322481033.aspx
980
981	# Hm. Docs say 28/38, but I can't seem to get more than 12 working.
982	numeric_max_precision = 12
983	numeric_max_bytes = 6
984
985	def coerce_bool(self, col): return "BIT"
986
987	def coerce_datetime_datetime(self, col): return "DATETIME"
988	def coerce_datetime_date(self, col): return "DATETIME"
989	def coerce_datetime_time(self, col): return "DATETIME"
990
991	def int_type(self, bytes):
992	if bytes <= 2:
993	return "INTEGER"
994	elif bytes <= 4:
995	return "LONG"
996	else:
997	# Anything larger than 4 bytes, use decimal/numeric.
998	return "DECIMAL"
999
1000	def coerce_str(self, col):
1001	# The bytes hint shall not reflect the usual 4-byte base for varchar.
1002	bytes = int(col.hints.get('bytes', 255))
1003
1004	# 255 chars is the upper limit for TEXT / VARCHAR in MS Access.
1005	if bytes == 0 or bytes > 255:
1006	# MEMO is 1 GB max when set programatically (only 64K when set
1007	# in Access UI). But then, 1 GB is the limit for the whole DB.
1008	# Note that OpenSchema will return a DATA_TYPE of "WCHAR".
1009	return "MEMO"
1010
1011	return "VARCHAR(%s)" % bytes
1012
1013
1014
1015	class AdapterToADOSQL_MSAccess(geniusql.AdapterToSQL):
1016	"""Coerce Expression constants to ADO SQL."""
1017
1018	encoding = 'ISO-8859-1'
1019
1020	escapes = [("'", "''")]
1021	like_escapes = [("[", "[[]"), ("%", "[%]"), ("_", "[_]"),
1022	("?", "[?]"), ("#", "[#]")]
1023
1024	def coerce_datetime_datetime_to_any(self, value):
1025	return ('#%s/%s/%s %02d:%02d:%02d#' %
1026	(value.month, value.day, value.year,
1027	value.hour, value.minute, value.second))
1028
1029	def coerce_datetime_date_to_any(self, value):
1030	return '#%s/%s/%s#' % (value.month, value.day, value.year)
1031
1032	def coerce_datetime_time_to_any(self, value):
1033	return '#%02d:%02d:%02d#' % (value.hour, value.minute, value.second)
1034
1035
1036	class MSAccessDatabase(ADODatabase):
1037
1038	decompiler = ADOSQLDecompiler_MSAccess
1039	adaptertosql = AdapterToADOSQL_MSAccess()
1040	typeadapter = TypeAdapter_MSAccess()
1041
1042	def version(self):
1043	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
1044	v = adoconn.Version
1045	del adoconn
1046	return "ADO Version: %s" % v
1047
1048	def _get_columns(self, tablename, conn=None):
1049	cols = ADODatabase._get_columns(self, tablename, conn)
1050	if conn is None:
1051	conn = self.connection()
1052
1053	try:
1054	# Horrible hack to get autoincrement property
1055	query = "SELECT * FROM %s WHERE FALSE" % self.quote(tablename)
1056	if isinstance(conn, geniusql.ConnectionWrapper):
1057	# 'conn' is a ConnectionWrapper object, which .Open
1058	# won't accept. Pass the unwrapped connection instead.
1059	conn = conn.conn
1060
1061	# Call conn.Open(query) directly, skipping win32com overhead.
1062	res, rows_affected = conn._oleobj_.InvokeTypes(6, 0, 1, (9, 0),
1063	((8, 1), (16396, 18), (3, 49)),
1064	# *args =
1065	query, pythoncom.Missing, -1)
1066	except pywintypes.com_error, x:
1067	try:
1068	res.InvokeTypes(*Recordset_Close)
1069	except:
1070	pass
1071	res = None
1072	x.args += (query, )
1073	conn = None
1074
1075	try:
1076	if "no read permission" in x.args[2][2]:
1077	conn = None
1078	return []
1079	except IndexError:
1080	pass
1081
1082	# "raise x" here or we could get the traceback of the inner try.
1083	raise x
1084
1085	resFields = res.InvokeTypes(*Recordset_Fields)
1086	for c in cols:
1087	f = resFields.InvokeTypes(0, 0, 2, (9, 0), ((12, 1),), c.name)
1088	fprops = f.InvokeTypes(*Field_Properties)
1089	fprop = fprops.InvokeTypes(0, 0, 2, (9, 0), ((12, 1), ), "ISAUTOINCREMENT")
1090	c.autoincrement = fprop.InvokeTypes(*Property_Value)
1091
1092	try:
1093	res.InvokeTypes(*Recordset_Close)
1094	except:
1095	pass
1096	conn = None
1097
1098	return cols
1099
1100	def python_type(self, dbtype):
1101	if dbtype == "LONG":
1102	return int
1103	return ADODatabase.python_type(self, dbtype)
1104
1105	def col_def(self, column):
1106	"""Return a clause for the given column for CREATE or ALTER TABLE.
1107
1108	This will be of the form:
1109	name type [DEFAULT x\|AUTOINCREMENT(initial, 1)]
1110	"""
1111	dbtype = column.dbtype
1112
1113	if column.autoincrement:
1114	# MS Access does not allow a column to have
1115	# both an AUTOINCREMENT clause and a DEFAULT clause.
1116	# It also needs no type in this case.
1117	dbtype = "AUTOINCREMENT(%s, 1)" % column.initial
1118	else:
1119	default = column.default or ""
1120	if default:
1121	defspec = self.adaptertosql.coerce(default, dbtype)
1122	if isinstance(default, (int, long)):
1123	# Crazy quote hack to get a numeric default to work.
1124	defspec = "'%s'" % defspec
1125	dbtype = "%s DEFAULT %s" % (dbtype, defspec)
1126
1127	return '%s %s' % (column.qname, dbtype)
1128
1129	# Connecting #
1130
1131	poolsize = 0
1132
1133	def connect(self):
1134	# MS Access can't use a pool, because there doesn't seem
1135	# to be a commit timeout. See http://support.microsoft.com/kb/200300
1136	# for additional synchronization issues.
1137	self.connection = geniusql.SingleConnection(self._get_conn, self._del_conn)
1138
1139	def create_database(self):
1140	self.lock("Creating database. Transactions not allowed.")
1141	try:
1142	# By not providing an Engine Type, it defaults to 5 = Access 2000.
1143	cat = win32com.client.Dispatch(r'ADOX.Catalog')
1144	cat.Create(self.Connect)
1145	cat.ActiveConnection.Close()
1146	self.clear()
1147	finally:
1148	self.unlock()
1149
1150	def drop_database(self):
1151	self.lock("Dropping database. Transactions not allowed.")
1152	try:
1153	# Must shut down our only connection to avoid
1154	# "Permission denied" error on os.remove call below.
1155	self.connection.shutdown()
1156
1157	import os
1158	# This should accept relative or absolute paths
1159	if os.path.exists(self.name):
1160	os.remove(self.name)
1161	self.clear()
1162	finally:
1163	self.unlock()
1164
1165	default_isolation = "READ UNCOMMITTED"
1166	isolation_levels = ["READ UNCOMMITTED",]
1167
1168	def isolate(self, conn, isolation=None):
1169	"""Set the isolation level of the given connection.
1170
1171	If 'isolation' is None, our default_isolation will be used for new
1172	connections. Valid values for the 'isolation' argument may be native
1173	values for your particular database. However, it is recommended you
1174	pass items from the global 'levels' list instead; these will be
1175	automatically replaced with native values.
1176
1177	For many databases, this must be executed after START TRANSACTION.
1178	"""
1179	if isolation is None:
1180	isolation = self.default_isolation
1181
1182	if isinstance(isolation, geniusql.IsolationLevel):
1183	# Map the given IsolationLevel object to a native value.
1184	isolation = isolation.name
1185	if isolation not in self.isolation_levels:
1186	raise ValueError("IsolationLevel %r not allowed by %s."
1187	% (isolation, self.__class__.__name__))
1188
1189	# No action to take, since you can't actually set iso level.
1190	pass
1191
1192
1193	def gen_py():
1194	"""Auto generate .py support for ADO 2.7+"""
1195	print 'Please wait while support for ADO 2.7+ is verified...'
1196
1197	# Microsoft ActiveX Data Objects 2.8 Library
1198	result = win32com.client.gencache.EnsureModule('{2A75196C-D9EB-4129-B803-931327F72D5C}', 0, 2, 8)
1199	if result is not None:
1200	return
1201
1202	# Microsoft ActiveX Data Objects 2.7 Library
1203	result = win32com.client.gencache.EnsureModule('{EF53050B-882E-4776-B643-EDA472E8E3F2}', 0, 2, 7)
1204	if result is not None:
1205	return
1206
1207	raise ImportError("ADO 2.7 support could not be imported/cached")
1208
1209
1210	if __name__ == '__main__':
1211	gen_py()

Note: See TracBrowser for help on using the browser.

root/trunk/geniusql/providers/ado.py

Download in other formats: