Revision Log

storeado.py

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

Note: See TracBrowser for help on using the browser.

root/trunk/storage/storeado.py

Download in other formats: