Revision Log

storeado.py

Revision 230 (checked in by fumanchu, 7 years ago)	Introspection fixes for ADO and SQLite (which now declares datatypes in CREATE TABLE).
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 warnings
17
18	import dejavu
19	from dejavu import storage, logic
20	from dejavu.storage import db
21
22	adOpenForwardOnly = 0
23	adOpenKeyset = 1
24	adOpenDynamic = 2
25	adOpenStatic = 3
26
27	adLockReadOnly = 1
28	adLockPessimistic = 2
29	adLockOptimistic = 3
30	adLockBatchOptimistic = 4
31
32	adSchemaColumns = 4
33	adSchemaIndexes = 12
34	adSchemaTables = 20
35
36	adUseClient = 3
37
38	# 12/30/1899, the zero-Date for ADO = 693594
39	zeroHour = datetime.date(1899, 12, 30).toordinal()
40
41	dbtypes = {
42	0: 'EMPTY', 2: 'SMALLINT',
43	3: 'INTEGER', 4: 'SINGLE',
44	5: 'DOUBLE', 6: 'CURRENCY',
45	7: 'DATE', 8: 'BSTR',
46	9: 'IDISPATCH', 10: 'ERROR',
47	11: 'BOOLEAN', 12: 'VARIANT',
48	13: 'IUNKNOWN', 14: 'DECIMAL',
49	16: 'TINYINT', 17: 'UNSIGNEDTINYINT',
50	18: 'UNSIGNEDSMALLINT', 19: 'UNSIGNEDINT',
51	20: 'BIGINT', 21: 'UNSIGNEDBIGINT',
52	72: 'GUID', 128: 'BINARY',
53	129: 'CHAR', 130: 'WCHAR',
54	131: 'NUMERIC', 132: 'USERDEFINED',
55	133: 'DBDATE', 134: 'DBTIME',
56	135: 'DBTIMESTAMP', 200: 'VARCHAR',
57	201: 'LONGVARCHAR', 202: 'VARWCHAR',
58	203: 'LONGVARWCHAR', 204: 'VARBINARY',
59	205: 'LONGVARBINARY'
60	}
61
62	def time_from_com(com_date):
63	"""Return a valid datetime.time from a COM date or time object."""
64	hour, minute = divmod(86400 * (float(com_date) % 1), 3600)
65	minute, second = divmod(minute, 60)
66	# Must do both int() and round() or we'll be up to 1 second off.
67	hour = int(round(hour))
68	minute = int(round(minute))
69	second = int(round(second))
70
71	while second > 59:
72	second -= 60
73	minute += 1
74	while second < 0:
75	second += 60
76	minute -= 1
77	while minute > 59:
78	minute -= 60
79	hour += 1
80	while minute < 0:
81	minute += 60
82	hour -= 1
83	while hour > 23:
84	hour -= 24
85	day += 1
86	while hour < 0:
87	hour += 24
88
89	return datetime.time(hour, minute, second)
90
91	class AdapterFromADO(db.AdapterFromDB):
92	"""Coerce incoming values from ADO to Dejavu datatypes."""
93
94	encoding = 'ISO-8859-1'
95
96	def coerce_datetime_datetime(self, value, coltype):
97	# Illegal Date/Time values will crash the
98	# app when using value.Format(). Therefore,
99	# grab the value and figure the date ourselves.
100	# Use 1-second resolution only.
101	if isinstance(value, basestring):
102	if value:
103	try:
104	return datetime.datetime(int(value[0:4]), int(value[4:6]),
105	int(value[6:8]))
106	except Exception:
107	raise ValueError("'%s' %s" % (value, type(value)))
108	else:
109	return None
110	else:
111	# For some reason, we need both float and int.
112	aDate = datetime.date.fromordinal(int(float(value)) + zeroHour)
113	return datetime.datetime.combine(aDate, time_from_com(value))
114
115	def coerce_datetime_date(self, value, coltype):
116	# See coerce_datetime
117	if isinstance(value, basestring):
118	if value:
119	try:
120	return datetime.date(int(value[0:4]), int(value[4:6]),
121	int(value[6:8]))
122	except Exception:
123	raise ValueError("'%s' %s" % (value, type(value)))
124	else:
125	return None
126	else:
127	return datetime.date.fromordinal(int(float(value)) + zeroHour)
128
129	def coerce_datetime_time(self, value, coltype):
130	# See coerce_datetime
131	return time_from_com(value)
132
133	def coerce_decimal_Decimal(self, value, coltype):
134	# pywin32 build 205 began support for returning
135	# COM Currency objects as decimal objects.
136	# See http://pywin32.cvs.sourceforge.net/pywin32/pywin32/CHANGES.txt?view=markup
137	if not isinstance(value, db.decimal.Decimal):
138	value = db.decimal.Decimal(str(self.coerce_float(value, coltype)))
139	return value
140
141	def coerce_fixedpoint_FixedPoint(self, value, coltype):
142	return db.fixedpoint.FixedPoint(self.coerce_float(value, coltype))
143
144	def coerce_float(self, value, coltype):
145	if coltype == 0x06 and isinstance(value, tuple):
146	# See http://groups.google.com/group/comp.lang.python/
147	# browse_frm/thread/fed03c64735c9e9c
148	value = map(long, value)
149	return ((value[1] & 0xFFFFFFFFL) \| (value[0] << 32)) / 1e4
150	return float(value)
151
152	def coerce_int(self, value, coltype):
153	if coltype == 0x0b:
154	# Boolean
155	return value != 0
156	return int(value)
157
158	coerce_bool = coerce_int
159
160	def coerce_unicode(self, value, coltype):
161	if isinstance(value, unicode):
162	# For some reason, inValue is already a unicode object.
163	return value
164	if isinstance(value, (basestring, buffer)):
165	try:
166	return unicode(value, self.encoding)
167	except UnicodeError:
168	raise StandardError(type(value))
169	return unicode(value)
170
171
172
173	class AdapterToADOFields(storage.Adapter):
174	"""Coerce outgoing values from Dejavu datatypes to ADO.Field types."""
175
176	def noop(self, value):
177	return value
178
179	def coerce_bool(self, value):
180	if value:
181	return True
182	return False
183
184	def coerce_datetime_datetime(self, value):
185	if value is None:
186	return None
187	return self.coerce_datetime_date(value) + self.coerce_datetime_time(value)
188
189	def coerce_datetime_date(self, value):
190	if value is None:
191	return None
192	return value.toordinal() - zeroHour
193
194	def coerce_datetime_time(self, value):
195	if value is None:
196	return None
197	return ((value.second + (value.minute * 60) + (value.hour * 3600))
198	/ 86400.0)
199
200	def do_pickle(self, value):
201	# We must not use a pickle format other than 0, because binary
202	# strings are not safe for all DB string fields.
203	return pickle.dumps(value)
204
205	coerce_dict = do_pickle
206
207	def coerce_fixedpoint_FixedPoint(self, value):
208	if value is None:
209	return None
210	return float(value)
211
212	coerce_float = noop
213	coerce_int = noop
214
215	coerce_list = do_pickle
216
217	coerce_long = noop
218	coerce_str = noop
219
220	coerce_tuple = do_pickle
221
222	coerce_unicode = noop
223
224
225	class ADOSQLDecompiler(db.SQLDecompiler):
226
227	def visit_COMPARE_OP(self, lo, hi):
228	op2, op1 = self.stack.pop(), self.stack.pop()
229	if op1 is db.cannot_represent or op2 is db.cannot_represent:
230	self.stack.append(db.cannot_represent)
231	return
232
233	op = lo + (hi << 8)
234	if op in (6, 7): # in, not in
235	# Looking for text in a field. Use Like (reverse terms).
236	# LIKE is case-insensitive in MS SQL Server (and there
237	# doesn't seem to be a way around it). Use icontainedby
238	# and just mark imperfect.
239	value = self.dejavu_icontainedby(op1, op2)
240	if op == 7:
241	value = "NOT " + value
242	self.stack.append(value)
243	self.imperfect = True
244	elif op1 == 'NULL':
245	if op in (2, 8): # '==', is
246	self.stack.append(op2 + " IS NULL")
247	elif op in (3, 9): # '!=', 'is not'
248	self.stack.append(op2 + " IS NOT NULL")
249	else:
250	raise ValueError("Non-equality Null comparisons not allowed.")
251	elif op2 == 'NULL':
252	if op in (2, 8): # '==', 'is'
253	self.stack.append(op1 + " IS NULL")
254	elif op in (3, 9): # '!=', 'is not'
255	self.stack.append(op1 + " IS NOT NULL")
256	else:
257	raise ValueError("Non-equality Null comparisons not allowed.")
258	else:
259	if (isinstance(op2, db.ConstWrapper)
260	and isinstance(op2.basevalue, basestring)):
261	# ADO comparison operators for strings are case-insensitive
262	# by default. Rather than determine which columns in the DB
263	# might be case-sensitive, just flag them all as imperfect.
264	# TODO: might be possible to cast both to varbinary, but
265	# that may cause problems with unicode columns.
266	self.imperfect = True
267	self.stack.append(op1 + " " + self.sql_cmp_op[op] + " " + op2)
268
269	# --------------------------- Dispatchees --------------------------- #
270
271	def attr_startswith(self, tos, arg):
272	self.imperfect = True
273	return tos + " LIKE '" + self.adapter.escape_like(arg) + "%'"
274
275	def attr_endswith(self, tos, arg):
276	self.imperfect = True
277	return tos + " LIKE '%" + self.adapter.escape_like(arg) + "'"
278
279	def containedby(self, op1, op2):
280	self.imperfect = True
281	if isinstance(op1, ConstWrapper):
282	# Looking for text in a field. Use Like (reverse terms).
283	return op2 + " LIKE '%" + self.adapter.escape_like(op1) + "%'"
284	else:
285	# Looking for field in (a, b, c)
286	atoms = [self.adapter.coerce(x) for x in op2.basevalue]
287	return op1 + " IN (" + ", ".join(atoms) + ")"
288
289	def dejavu_icontainedby(self, op1, op2):
290	if isinstance(op1, db.ConstWrapper):
291	# Looking for text in a field. Use Like (reverse terms).
292	# LIKE is already case-insensitive in MS SQL Server;
293	# so don't use LOWER().
294	value = op2 + " LIKE '%" + self.adapter.escape_like(op1) + "%'"
295	else:
296	# Looking for field in (a, b, c)
297	atoms = [self.adapter.coerce(x) for x in op2.basevalue]
298	value = op1 + " IN (" + ", ".join(atoms) + ")"
299	return value
300
301	def dejavu_istartswith(self, x, y):
302	# Like is already case-insensitive in ADO; so don't use LOWER().
303	return x + " LIKE '" + self.adapter.escape_like(y) + "%'"
304
305	def dejavu_iendswith(self, x, y):
306	# Like is already case-insensitive in ADO; so don't use LOWER().
307	return x + " LIKE '%" + self.adapter.escape_like(y) + "'"
308
309	def dejavu_ieq(self, x, y):
310	# = is already case-insensitive in ADO.
311	return x + " = " + y
312
313	def dejavu_now(self):
314	return "getdate()"
315
316	def dejavu_today(self):
317	return "DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)"
318
319	def func__builtin___len(self, x):
320	return "Len(" + x + ")"
321
322
323	class ADOColumnSet(db.ColumnSet):
324
325	def _rename(self, oldcol, newcol):
326	conn = self.table.db.connection()
327	try:
328	cat = win32com.client.Dispatch(r'ADOX.Catalog')
329	cat.ActiveConnection = conn
330	cat.Tables(self.table.name).Columns(oldcol.name).Name = newcol.name
331	finally:
332	conn = None
333	cat = None
334
335
336	def connatoms(connstring):
337	atoms = {}
338	for pair in connstring.split(";"):
339	if pair:
340	k, v = pair.split("=", 1)
341	atoms[k.upper().strip()] = v.strip()
342	return atoms
343
344
345	class ADODatabase(db.Database):
346
347	decompiler = ADOSQLDecompiler
348	adapterfromdb = AdapterFromADO()
349	columnsetclass = ADOColumnSet
350
351	def _get_tables(self, conn=None):
352	# cols will be
353	# [(u'TABLE_CATALOG', 202), (u'TABLE_SCHEMA', 202), (u'TABLE_NAME', 202),
354	# (u'TABLE_TYPE', 202), (u'TABLE_GUID', 72), (u'DESCRIPTION', 203),
355	# (u'TABLE_PROPID', 19), (u'DATE_CREATED', 7), (u'DATE_MODIFIED', 7)]
356	data, cols = self.fetch(adSchemaTables, conn=conn, schema=True)
357	return [db.Table(self, row[2], self.quote(row[2])) for row in data]
358
359	def _get_columns(self, tablename, conn=None):
360	# columns will be
361	# [(u'TABLE_CATALOG', 202), (u'TABLE_SCHEMA', 202), (u'TABLE_NAME', 202),
362	# (u'COLUMN_NAME', 202), (u'COLUMN_GUID', 72), (u'COLUMN_PROPID', 19),
363	# (u'ORDINAL_POSITION', 19), (u'COLUMN_HASDEFAULT', 11),
364	# (u'COLUMN_DEFAULT', 203), (u'COLUMN_FLAGS', 19), (u'IS_NULLABLE', 11),
365	# (u'DATA_TYPE', 18), (u'TYPE_GUID', 72), (u'CHARACTER_MAXIMUM_LENGTH', 19),
366	# (u'CHARACTER_OCTET_LENGTH', 19), (u'NUMERIC_PRECISION', 18),
367	# (u'NUMERIC_SCALE', 2), (u'DATETIME_PRECISION', 19),
368	# (u'CHARACTER_SET_CATALOG', 202), (u'CHARACTER_SET_SCHEMA', 202),
369	# (u'CHARACTER_SET_NAME', 202), (u'COLLATION_CATALOG', 202),
370	# (u'COLLATION_SCHEMA', 202), (u'COLLATION_NAME', 202),
371	# (u'DOMAIN_CATALOG', 202), (u'DOMAIN_SCHEMA', 202),
372	# (u'DOMAIN_NAME', 202), (u'DESCRIPTION', 203)]
373	data, coldefs = self.fetch(adSchemaColumns, conn=conn, schema=True)
374
375	cols = []
376	for row in data:
377	# I tried passing criteria to OpenSchema, but passing None is
378	# not the same as passing pythoncom.Empty (which errors).
379	if tablename and row[2] != tablename:
380	continue
381
382	dbtype = dbtypes[row[11]]
383	default = row[8]
384	if default is not None:
385	default = self.python_type(dbtype)(default)
386	c = db.Column(row[3], self.quote(row[3]), dbtype, default)
387
388	if dbtype in ("SMALLINT", "INTEGER", "TINYINT",
389	"UNSIGNEDTINYINT", "UNSIGNEDSMALLINT",
390	"UNSIGNEDINT", "BIGINT", "UNSIGNEDBIGINT"):
391	c.hints['bytes'] = row[15]
392	elif dbtype in ("SINGLE", "DOUBLE", "CURRENCY"):
393	c.hints['precision'] = row[15]
394	c.hints['scale'] = row[16]
395	elif dbtype in ("DECIMAL", "NUMERIC"):
396	c.hints['precision'] = row[15]
397	c.hints['scale'] = row[16]
398	c.dbtype = "%s(%s, %s)" % (dbtype, row[15], row[16])
399	elif dbtype in ("BSTR", "VARIANT", "BINARY", "CHAR",
400	"VARCHAR", "VARBINARY", "WCHAR", "VARWCHAR"):
401	if row[13]:
402	# row[13] will be a float
403	c.hints['bytes'] = b = int(row[13])
404	else:
405	# I'm kinda guessing on this. If we use "MEMO" in an
406	# MSAccess CREATE statement, it comes back as "WCHAR",
407	# and seems to support over 65536 bytes.
408	c.hints['bytes'] = b = (2 ** 31) - 1
409	c.dbtype = "%s(%s)" % (c.dbtype, b)
410	elif dbtype in ("LONGVARCHAR", "LONGVARBINARY", "LONGVARWCHAR"):
411	if row[13]:
412	# row[13] will be a float
413	c.hints['bytes'] = b = int(row[13])
414	c.dbtype = "%s(%s)" % (c.dbtype, b)
415	else:
416	c.hints['bytes'] = 65535
417
418	cols.append(c)
419	return cols
420
421	def _get_indices(self, tablename=None, conn=None):
422	# cols will be
423	# [(u'TABLE_CATALOG', 202), (u'TABLE_SCHEMA', 202), (u'TABLE_NAME', 202),
424	# (u'INDEX_CATALOG', 202), (u'INDEX_SCHEMA', 202), (u'INDEX_NAME', 202),
425	# (u'PRIMARY_KEY', 11), (u'UNIQUE', 11), (u'CLUSTERED', 11), (u'TYPE', 18),
426	# (u'FILL_FACTOR', 3), (u'INITIAL_SIZE', 3), (u'NULLS', 3),
427	# (u'SORT_BOOKMARKS', 11), (u'AUTO_UPDATE', 11), (u'NULL_COLLATION', 3),
428	# (u'ORDINAL_POSITION', 19), (u'COLUMN_NAME', 202), (u'COLUMN_GUID', 72),
429	# (u'COLUMN_PROPID', 19), (u'COLLATION', 2), (u'CARDINALITY', 21),
430	# (u'PAGES', 3), (u'FILTER_CONDITION', 202), (u'INTEGRATED', 11)]
431	data, _ = self.fetch(adSchemaIndexes, conn=conn, schema=True)
432	indices = []
433	for row in data:
434	# I tried passing criteria to OpenSchema, but passing None is
435	# not the same as passing pythoncom.Empty (which errors).
436	if tablename and row[2] != tablename:
437	continue
438	i = db.Index(row[5], self.quote(row[5]),
439	row[2], row[17], row[6], row[7])
440	indices.append(i)
441	return indices
442
443	def python_type(self, dbtype):
444	"""Return a Python type which can store values of the given dbtype."""
445	if dbtype.startswith("AUTOINCREMENT") or "IDENTITY" in dbtype:
446	return int
447	elif dbtype in ("DATE", "DBDATE"):
448	return datetime.date
449	elif dbtype == "DBTIME":
450	return datetime.time
451	elif dbtype in ("DATETIME", "DBTIMESTAMP"):
452	return datetime.datetime
453	elif dbtype in ("SMALLINT", "INTEGER", "TINYINT",
454	"UNSIGNEDTINYINT", "UNSIGNEDSMALLINT",
455	"UNSIGNEDINT"):
456	return int
457	elif dbtype == "BOOLEAN":
458	return bool
459	elif dbtype in ("BIGINT", "UNSIGNEDBIGINT", "LONG"):
460	return long
461	elif dbtype in ("SINGLE", "DOUBLE", "DOUBLE PRECISION", "REAL"):
462	return float
463
464	for t in ("DECIMAL", "NUMERIC", "CURRENCY"):
465	if dbtype.startswith(t):
466	if db.decimal:
467	return db.decimal.Decimal
468	elif db.fixedpoint:
469	return db.fixedpoint.FixedPoint
470
471	for t in ("BSTR", "VARIANT", "BINARY", "CHAR", "MEMO",
472	"VARCHAR", "LONGVARCHAR", "VARBINARY", "LONGVARBINARY"):
473	if dbtype.startswith(t):
474	return str
475
476	for t in ("WCHAR", "VARWCHAR", "LONGVARWCHAR"):
477	if dbtype.startswith(t):
478	return unicode
479
480	raise TypeError("Database type %s could not be converted "
481	"to a Python type." % repr(dbtype))
482
483	def _rename(self, oldtable, newtable):
484	conn = self.connection()
485	try:
486	cat = win32com.client.Dispatch(r'ADOX.Catalog')
487	cat.ActiveConnection = conn
488	cat.Tables(oldtable.name).Name = newtable.name
489	finally:
490	conn = None
491	cat = None
492
493	def quote(self, name):
494	"""Return name, quoted for use in an SQL statement."""
495	return '[' + name + ']'
496
497	def _get_conn(self):
498	conn = win32com.client.Dispatch(r'ADODB.Connection')
499	conn.Open(self.Connect)
500	return conn
501
502	def _del_conn(self, conn):
503	conn.Close()
504
505	def execute(self, query, conn=None):
506	if conn is None:
507	conn = self.connection()
508	if isinstance(query, unicode):
509	query = query.encode(self.adaptertosql.encoding)
510	self.log(query, dejavu.LOGSQL)
511	try:
512	conn.Execute(query)
513	except pywintypes.com_error, x:
514	x.args += (query, )
515	conn = None
516	raise
517
518	def fetch(self, query, conn=None, schema=False):
519	"""fetch(query, conn=None) -> rowdata, columns."""
520	if conn is None:
521	conn = self.connection()
522
523	try:
524	if schema:
525	res = conn.OpenSchema(query)
526	else:
527	self.log(query, dejavu.LOGSQL)
528	res = win32com.client.Dispatch(r'ADODB.Recordset')
529	if hasattr(conn, "conn"):
530	# 'conn' is a ConnectionWrapper object, which .Open
531	# won't accept. Pass the unwrapped connection instead.
532	res.Open(query, conn.conn, adOpenForwardOnly, adLockReadOnly)
533	else:
534	res.Open(query, conn, adOpenForwardOnly, adLockReadOnly)
535	except pywintypes.com_error, x:
536	try:
537	res.Close()
538	except:
539	pass
540	x.args += (query, )
541	conn = None
542	# "raise x" here or we could get the traceback of the inner try.
543	raise x
544
545	columns = [(x.Name, x.Type) for x in res.Fields]
546
547	data = []
548	if not(res.BOF and res.EOF):
549	# We tried .MoveNext() and lots of Fields.Item() calls.
550	# Using GetRows() beats that time by about 2/3.
551	data = res.GetRows()
552	# Convert cols x rows -> rows x cols
553	data = zip(*data)
554	try:
555	res.Close()
556	except:
557	pass
558	conn = None
559
560	return data, columns
561
562
563	class StorageManagerADO(db.StorageManagerDB):
564	"""StoreManager to save and retrieve Units via ADO 2.7.
565
566	You must run makepy on ADO 2.7 before installing.
567	"""
568
569	databaseclass = ADODatabase
570
571	def version(self):
572	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
573	return "ADO Version: %s" % adoconn.Version
574
575
576
577	###########################################################################
578	## ##
579	## SQL Server ##
580	## ##
581	###########################################################################
582
583
584	class AdapterToADOSQL_SQLServer(db.AdapterToSQL):
585
586	encoding = 'ISO-8859-1'
587
588	escapes = [("'", "''")]
589	like_escapes = [("%", "[%]"), ("_", "[_]")]
590
591	# These are not the same as coerce_bool (which is used on one side of
592	# a comparison). Instead, these are used when the whole (sub)expression
593	# is True or False, e.g. "WHERE TRUE", or "WHERE TRUE and 'a'.'b' = 3".
594	bool_true = "(1=1)"
595	bool_false = "(1=0)"
596
597	def coerce_bool(self, value):
598	if value:
599	return '1'
600	return '0'
601
602
603	class FieldTypeAdapter_SQLServer(db.FieldTypeAdapter):
604
605	# Hm. Docs say 38, but I can't seem to get more than 12 working.
606	numeric_max_precision = 12
607
608	def coerce_bool(self, cls, key):
609	return "BIT"
610
611	def coerce_datetime_datetime(self, cls, key):
612	return "DATETIME"
613
614	def coerce_datetime_date(self, cls, key):
615	return "DATETIME"
616
617	def coerce_datetime_time(self, cls, key):
618	return "DATETIME"
619
620	def coerce_int(self, cls, key):
621	result = db.FieldTypeAdapter.coerce_int(self, cls, key)
622	if isinstance(cls.sequencer, dejavu.UnitSequencerInteger):
623	if key in cls.identifiers:
624	if result not in ("BOOLEAN", "SMALLINT", "INTEGER", "BIGINT"):
625	raise dejavu.DejavuError("SQL Server does not allow "
626	"IDENTITY columns of type %s"
627	% (repr(result)))
628	result = ("%s IDENTITY(%s, 1) NOT NULL"
629	% (result, cls.sequencer.initial))
630	return result
631
632	def coerce_long(self, cls, key):
633	result = db.FieldTypeAdapter.coerce_long(self, cls, key)
634	if isinstance(cls.sequencer, dejavu.UnitSequencerInteger):
635	if key in cls.identifiers:
636	if result not in ("BOOLEAN", "SMALLINT", "INTEGER", "BIGINT"):
637	raise dejavu.DejavuError("SQL Server does not allow "
638	"IDENTITY columns of type %s"
639	% (repr(result)))
640	result = ("%s IDENTITY(%s, 1) NOT NULL"
641	% (result, cls.sequencer.initial))
642	return result
643
644	def coerce_str(self, cls, key):
645	# The bytes hint does not reflect the usual 4-byte base for varchar.
646	prop = getattr(cls, key)
647	bytes = int(prop.hints.get('bytes', '0'))
648	if bytes == 0:
649	# Okay, what the @#$%& is wrong with Redmond??!?! We can't even
650	# compare TEXT or NTEXT fields??!? Fine. We'll deny such, and
651	# warn the deployer with less swearing and exclamation points.
652	warnings.warn("You have defined a string property without "
653	"limiting its length. Microsoft SQL Server does "
654	"not allow comparisons on string fields larger "
655	"than 8000 characters. Some of your data may be "
656	"truncated.", dejavu.StorageWarning)
657	bytes = 8000
658	# 8000 bytes is the absolute upper limit, based on T_SQL docs for
659	# varchar/varbinary. If there are further fields defined for the
660	# class, or the codepage uses a double-byte character set, we still
661	# might exceed the max size (8060) for a record. We could calc the
662	# total requested record size, and adjust accordingly. Meh.
663	return "VARCHAR(%s)" % bytes
664
665
666	class SQLServerColumnSet(ADOColumnSet):
667
668	def __setitem__(self, key, column):
669	t = self.table
670	# SQL Server doesn't use the "COLUMN" keyword with "ADD"
671	t.db.execute("ALTER TABLE %s ADD %s %s;" %
672	(t.qname, column.qname, column.dbtype))
673	dict.__setitem__(self, key, column)
674
675	def _rename(self, oldcol, newcol):
676	t = self.table
677	t.db.execute("EXEC sp_rename '%s.%s', '%s', 'COLUMN'" %
678	(t.name, oldcol.name, newcol.name))
679
680
681	class SQLServerDatabase(ADODatabase):
682
683	columnsetclass = SQLServerColumnSet
684	adaptertosql = AdapterToADOSQL_SQLServer()
685
686	def create_database(self):
687	# This method hasn't been tested yet for SQL server (only MSDE).
688	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
689	atoms = connatoms(self.Connect)
690	atoms['INITIAL CATALOG'] = "tempdb"
691	adoconn.Open("; ".join(["%s=%s" % (k, v) for k, v in atoms.iteritems()]))
692	adoconn.Execute("CREATE DATABASE %s" % self.qname)
693	adoconn.Close()
694
695	def drop_database(self):
696	adoconn = win32com.client.Dispatch(r'ADODB.Connection')
697	atoms = connatoms(self.Connect)
698	atoms['INITIAL CATALOG'] = "tempdb"
699	adoconn.Open("; ".join(["%s=%s" % (k, v) for k, v in atoms.iteritems()]))
700	adoconn.Execute("DROP DATABASE %s;" % self.qname)
701	adoconn.Close()
702
703
704	class StorageManagerADO_SQLServer(StorageManagerADO):
705
706	typeAdapter = FieldTypeAdapter_SQLServer()
707	databaseclass = SQLServerDatabase
708
709	def __init__(self, name, arena, allOptions={}):
710	atoms = connatoms(allOptions['Connect'])
711	allOptions['name'] = atoms['INITIAL CATALOG']
712	db.StorageManagerDB.__init__(self, name, arena, allOptions)
713
714	def _seq_UnitSequencerInteger(self, unit):
715	"""Reserve a unit using the table's AUTOINCREMENT field."""
716	cls = unit.__class__
717	t = self.db[cls.__name__]
718
719	fields = []
720	values = []
721	for key in cls.properties:
722	dbtype = self.typeAdapter.coerce(cls, key)
723	if "IDENTITY" in dbtype:
724	# Skip this field, since we're using IDENTITY
725	continue
726	val = self.db.adaptertosql.coerce(getattr(unit, key))
727	fields.append(t.columns[key].qname)
728	values.append(val)
729
730	fields = ", ".join(fields)
731	values = ", ".join(values)
732	self.db.execute('INSERT INTO %s (%s) VALUES (%s);' %
733	(t.qname, fields, values))
734
735	# Grab the new ID. This is threadsafe because db.reserve has a mutex.
736	# For some reason, using SCOPE_IDENTITY or IDENTITY failed (returned
737	# None) when retrieving ID's just after a 99-thread-test ran. Moving
738	# the multithreading test fixed it. IDENT_CURRENT worked regardless.
739	data, col_defs = self.db.fetch("SELECT IDENT_CURRENT('%s');" % t.qname)
740	setattr(unit, cls.identifiers[0], data[0][0])
741
742
743
744	###########################################################################
745	## ##
746	## MS Access ##
747	## ##
748	###########################################################################
749
750
751	class ADOSQLDecompiler_MSAccess(ADOSQLDecompiler):
752	sql_cmp_op = ('<', '<=', '=', '<>', '>', '>=', 'in', 'not in')
753
754	def dejavu_now(self):
755	return "Now()"
756
757	def dejavu_today(self):
758	return "DateValue(Now())"
759
760	def dejavu_year(self, x):
761	return "Year(" + x + ")"
762
763
764	class FieldTypeAdapter_MSAccess(db.FieldTypeAdapter):
765
766	# Hm. Docs say 28/38, but I can't seem to get more than 12 working.
767	numeric_max_precision = 12
768
769	def coerce_bool(self, cls, key): return "BIT"
770
771	def coerce_datetime_datetime(self, cls, key): return "DATETIME"
772	def coerce_datetime_date(self, cls, key): return "DATETIME"
773	def coerce_datetime_time(self, cls, key): return "DATETIME"
774
775	def int_type(self, bytes):
776	if bytes == 1:
777	return "BIT"
778	elif bytes == 2:
779	return "INTEGER"
780	elif bytes <= 4:
781	return "LONG"
782	else:
783	# Anything larger than 4 bytes, use decimal/numeric.
784	return "DECIMAL"
785
786	def coerce_int(self, cls, key):
787	if isinstance(cls.sequencer, dejavu.UnitSequencerInteger):
788	if key in cls.identifiers:
789	return "AUTOINCREMENT(%s, 1)" % cls.sequencer.initial
790	return db.FieldTypeAdapter.coerce_int(self, cls, key)
791
792	def coerce_long(self, cls, key):
793	if isinstance(cls.sequencer, dejavu.UnitSequencerInteger):
794	if key in cls.identifiers:
795	return "AUTOINCREMENT(%s, 1)" % cls.sequencer.initial
796	return db.FieldTypeAdapter.coerce_long(self, cls, key)
797
798	def coerce_str(self, cls, key):
799	# The bytes hint shall not reflect the usual 4-byte base for varchar.
800	prop = getattr(cls, key)
801	bytes = int(prop.hints.get('bytes', 0))
802	if bytes and bytes <= 255:
803	# 255 chars is the upper limit for TEXT / VARCHAR in MS Access.
804	return "VARCHAR(%s)" % bytes
805	else:
806	# MEMO is 1 GB max when set programatically (only 64K when set
807	# in Access UI). But then, 1 GB is the limit for the whole DB.
808	# Note that OpenSchema will return a DATA_TYPE of "WCHAR".
809	for assoc in cls._associations.itervalues():
810	if assoc.nearKey == key:
811	warnings.warn("Memo fields cannot be used as join keys. "
812	"You should set %s.%s(hints={'bytes': 255})"
813	% (cls.__name__, key),
814	dejavu.StorageWarning)
815	return "MEMO"
816
817
818	class AdapterToADOSQL_MSAccess(db.AdapterToSQL):
819	"""Coerce Expression constants to ADO SQL."""
820
821	encoding = 'ISO-8859-1'
822
823	escapes = [("'", "''")]
824	like_escapes = [("%", "[%]"), ("_", "[_]")]
825
826	def coerce_datetime_datetime(self, value):
827	return ('#%s/%s/%s %02d:%02d:%02d#' %
828	(value.month, value.day, value.year,
829	value.hour, value.minute, value.second))
830
831	def coerce_datetime_date(self, value):
832	return '#%s/%s/%s#' % (value.month, value.day, value.year)
833
834	def coerce_datetime_time(self, value):
835	return '#%02d:%02d:%02d#' % (value.hour, value.minute, value.second)
836
837
838	class MSAccessDatabase(ADODatabase):
839
840	decompiler = ADOSQLDecompiler_MSAccess
841	adaptertosql = AdapterToADOSQL_MSAccess()
842
843	poolsize = 0
844
845	def connect(self):
846	# MS Access can't use a pool, because there doesn't seem
847	# to be a commit timeout.
848	self.connection = db.SingleConnection(self._get_conn, self._del_conn)
849
850	def create_database(self):
851	# By not providing an Engine Type, it defaults to 5 = Access 2000.
852	cat = win32com.client.Dispatch(r'ADOX.Catalog')
853	cat.Create(self.Connect)
854	cat.ActiveConnection.Close()
855
856	def drop_database(self):
857	import os
858	# This should accept relative or absolute paths
859	if os.path.exists(self.name):
860	os.remove(self.name)
861
862
863	class StorageManagerADO_MSAccess(StorageManagerADO):
864	# Jet Connections and Recordsets are always free-threaded.
865
866	use_asterisk_to_get_all = True
867
868	typeAdapter = FieldTypeAdapter_MSAccess()
869	databaseclass = MSAccessDatabase
870
871	def __init__(self, name, arena, allOptions={}):
872	atoms = connatoms(allOptions['Connect'])
873	allOptions['name'] = (atoms.get('DATA SOURCE') or
874	atoms.get('DATA SOURCE NAME') or
875	atoms.get('DBQ'))
876	db.StorageManagerDB.__init__(self, name, arena, allOptions)
877
878	def _seq_UnitSequencerInteger(self, unit):
879	"""Reserve a unit using the table's AUTOINCREMENT field."""
880	cls = unit.__class__
881	t = self.db[cls.__name__]
882
883	fields = []
884	values = []
885	for key in cls.properties:
886	typename = self.typeAdapter.coerce(cls, key)
887	if typename.startswith("AUTOINCREMENT"):
888	# Skip this field, since we're using AUTOINCREMENT
889	continue
890	val = self.db.adaptertosql.coerce(getattr(unit, key))
891	fields.append(t.columns[key].qname)
892	values.append(val)
893
894	fields = ", ".join(fields)
895	values = ", ".join(values)
896	self.db.execute('INSERT INTO %s (%s) VALUES (%s);' %
897	(t.qname, fields, values))
898
899	# Grab the new ID. This is threadsafe because db.reserve has a mutex.
900	data, col_defs = self.db.fetch("SELECT @@IDENTITY;")
901	setattr(unit, cls.identifiers[0], data[0][0])
902
903
904
905	def gen_py():
906	# Auto generate .py support for ADO 2.7+
907	print 'Please wait while support for ADO 2.7+ is verified...'
908	CLSID = '{EF53050B-882E-4776-B643-EDA472E8E3F2}'
909	return win32com.client.gencache.EnsureModule(CLSID, 0, 2, 7)
910
911
912	if __name__ == '__main__':
913	gen_py()

Note: See TracBrowser for help on using the browser.

root/trunk/storage/storeado.py

Download in other formats: