Revision Log

storeado.py

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

Note: See TracBrowser for help on using the browser.

root/trunk/storage/storeado.py

Download in other formats: