Revision Log

storeado.py

Revision 183 (checked in by fumanchu, 7 years ago)	Those SM's with autoincrement support now respect UnitSequencerInteger?.initial.
Property svn:eol-style set to `native`

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

Note: See TracBrowser for help on using the browser.

root/trunk/storage/storeado.py

Download in other formats: