[Sqlalchemy-commits] [1900] sqlalchemy/trunk: Simplified MSSQL table reflection code
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1900] sqlalchemy/trunk: Simplified MSSQL table reflection code
|
From: <co...@sq...> - 2006-09-26 20:34:48
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head><style type="text/css"><!-- #msg dl { border: 1px #006 solid; background: #369; padding: 6px; color: #fff; } #msg dt { float: left; width: 6em; font-weight: bold; } #msg dt:after { content:':';} #msg dl, #msg dt, #msg ul, #msg li { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; } #msg dl a { font-weight: bold} #msg dl a:link { color:#fc3; } #msg dl a:active { color:#ff0; } #msg dl a:visited { color:#cc6; } h3 { font-family: verdana,arial,helvetica,sans-serif; font-size: 10pt; font-weight: bold; } #msg pre { overflow: auto; background: #ffc; border: 1px #fc0 solid; padding: 6px; } #msg ul, pre { overflow: auto; } #patch { width: 100%; } #patch h4 {font-family: verdana,arial,helvetica,sans-serif;font-size:10pt;padding:8px;background:#369;color:#fff;margin:0;} #patch .propset h4, #patch .binary h4 {margin:0;} #patch pre {padding:0;line-height:1.2em;margin:0;} #patch .diff {width:100%;background:#eee;padding: 0 0 10px 0;overflow:auto;} #patch .propset .diff, #patch .binary .diff {padding:10px 0;} #patch span {display:block;padding:0 10px;} #patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;} #patch ins {background:#dfd;text-decoration:none;display:block;padding:0 10px;} #patch del {background:#fdd;text-decoration:none;display:block;padding:0 10px;} #patch .lines, .info {color:#888;background:#fff;} --></style> <title>[1900] sqlalchemy/trunk: Simplified MSSQL table reflection code</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1900</dd> <dt>Author</dt> <dd>ram</dd> <dt>Date</dt> <dd>2006-09-26 16:33:33 -0500 (Tue, 26 Sep 2006)</dd> </dl> <h3>Log Message</h3> <pre>Simplified MSSQL table reflection code added support for multi-column foreign keys</pre> <h3>Modified Paths</h3> <ul> <li><a href="#sqlalchemytrunkCHANGES">sqlalchemy/trunk/CHANGES</a></li> <li><a href="#sqlalchemytrunklibsqlalchemydatabasesmssqlpy">sqlalchemy/trunk/lib/sqlalchemy/databases/mssql.py</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemytrunkCHANGES"></a> <div class="modfile"><h4>Modified: sqlalchemy/trunk/CHANGES (1899 => 1900)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/trunk/CHANGES 2006-09-26 14:35:49 UTC (rev 1899) +++ sqlalchemy/trunk/CHANGES 2006-09-26 21:33:33 UTC (rev 1900) </span><span class="lines">@@ -13,6 +13,8 @@ </span><span class="cx"> -- fixes bug 261 (table reflection broken for MS-SQL case-sensitive databases) </span><span class="cx"> -- can now specify port for pymssql </span><span class="cx"> -- introduces new "auto_identity_insert" option for auto-switching between "SET IDENTITY_INSERT" mode when values specified for IDENTITY columns </span><ins>+ -- now supports multi-column foreign keys + -- fix to reflecting date/datetime columns </ins><span class="cx"> - more rearrangements of unit-of-work commit scheme to better allow </span><span class="cx"> dependencies within circular flushes to work properly...updated </span><span class="cx"> task traversal/logging implementation </span></span></pre></div> <a id="sqlalchemytrunklibsqlalchemydatabasesmssqlpy"></a> <div class="modfile"><h4>Modified: sqlalchemy/trunk/lib/sqlalchemy/databases/mssql.py (1899 => 1900)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/trunk/lib/sqlalchemy/databases/mssql.py 2006-09-26 14:35:49 UTC (rev 1899) +++ sqlalchemy/trunk/lib/sqlalchemy/databases/mssql.py 2006-09-26 21:33:33 UTC (rev 1900) </span><span class="lines">@@ -105,6 +105,9 @@ </span><span class="cx"> return "SMALLINT" </span><span class="cx"> </span><span class="cx"> class MSDateTime(sqltypes.DateTime): </span><ins>+ def __init__(self, *a, **kw): + super(MSDateTime, self).__init__(False) + </ins><span class="cx"> def get_col_spec(self): </span><span class="cx"> return "DATETIME" </span><span class="cx"> </span><span class="lines">@@ -122,6 +125,9 @@ </span><span class="cx"> return value </span><span class="cx"> </span><span class="cx"> class MSDate(sqltypes.Date): </span><ins>+ def __init__(self, *a, **kw): + super(MSDate, self).__init__(False) + </ins><span class="cx"> def get_col_spec(self): </span><span class="cx"> return "SMALLDATETIME" </span><span class="cx"> </span><span class="lines">@@ -380,7 +386,9 @@ </span><span class="cx"> current_schema = self.get_default_schema_name() </span><span class="cx"> columns = self.uppercase_table(ischema.columns) </span><span class="cx"> s = sql.select([columns], </span><del>- current_schema and sql.and_(columns.c.table_name==tablename, columns.c.table_schema==current_schema) or columns.c.table_name==tablename, </del><ins>+ current_schema + and sql.and_(columns.c.table_name==tablename, columns.c.table_schema==current_schema) + or columns.c.table_name==tablename, </ins><span class="cx"> ) </span><span class="cx"> </span><span class="cx"> c = connection.execute(s) </span><span class="lines">@@ -398,7 +406,9 @@ </span><span class="cx"> </span><span class="cx"> columns = self.uppercase_table(ischema.columns) </span><span class="cx"> s = sql.select([columns], </span><del>- current_schema and sql.and_(columns.c.table_name==table.name, columns.c.table_schema==current_schema) or columns.c.table_name==table.name, </del><ins>+ current_schema + and sql.and_(columns.c.table_name==table.name, columns.c.table_schema==current_schema) + or columns.c.table_name==table.name, </ins><span class="cx"> order_by=[columns.c.ordinal_position]) </span><span class="cx"> </span><span class="cx"> c = connection.execute(s) </span><span class="lines">@@ -432,7 +442,7 @@ </span><span class="cx"> </span><span class="cx"> if not found_table: </span><span class="cx"> raise exceptions.NoSuchTableError(table.name) </span><del>- </del><ins>+ </ins><span class="cx"> # We also run an sp_columns to check for identity columns: </span><span class="cx"> # FIXME: note that this only fetches the existence of an identity column, not it's properties like (seed, increment) </span><span class="cx"> # also, add a check to make sure we specify the schema name of the table </span><span class="lines">@@ -455,40 +465,43 @@ </span><span class="cx"> C = self.uppercase_table(ischema.column_constraints).alias('C') #information_schema.constraint_column_usage: the constrained column </span><span class="cx"> R = self.uppercase_table(ischema.column_constraints).alias('R') #information_schema.constraint_column_usage: the referenced column </span><span class="cx"> </span><del>- fromjoin = TC.join(RR, RR.c.constraint_name == TC.c.constraint_name).join(C, C.c.constraint_name == RR.c.constraint_name) - fromjoin = fromjoin.join(R, R.c.constraint_name == RR.c.unique_constraint_name) - - s = sql.select([TC.c.constraint_type, C.c.table_schema, C.c.table_name, C.c.column_name, - R.c.table_schema, R.c.table_name, R.c.column_name], - sql.and_(RR.c.constraint_schema == current_schema, C.c.table_name == table.name), - from_obj = [fromjoin], use_labels=True - ) - colmap = [TC.c.constraint_type, C.c.column_name, R.c.table_schema, R.c.table_name, R.c.column_name] - </del><ins>+ # Primary key constraints + s = sql.select([C.c.column_name, TC.c.constraint_type], sql.and_(TC.c.constraint_name == C.c.constraint_name, + C.c.table_name == table.name)) </ins><span class="cx"> c = connection.execute(s) </span><ins>+ for row in c: + if 'PRIMARY' in row[TC.c.constraint_type.name]: + table.c[row[C.c.column_name.name]]._set_primary_key() </ins><span class="cx"> </span><del>- while True: - row = c.fetchone() - if row is None: - break - print "CCROW", row.keys(), row - (type, constrained_column, referred_schema, referred_table, referred_column) = ( - row[colmap[0]], - row[colmap[1]], - row[colmap[2]], - row[colmap[3]], - row[colmap[4]] - ) </del><span class="cx"> </span><del>- if type=='PRIMARY KEY': - table.c[constrained_column]._set_primary_key() - elif type=='FOREIGN KEY': - if current_schema == referred_schema: - referred_schema = table.schema - remotetable = schema.Table(referred_table, table.metadata, autoload=True, autoload_with=connection, schema=referred_schema) - table.c[constrained_column].append_item(schema.ForeignKey(remotetable.c[referred_column])) </del><ins>+ # Foreign key constraints + s = sql.select([C.c.column_name, + R.c.table_schema, R.c.table_name, R.c.column_name, + RR.c.constraint_name, RR.c.match_option, RR.c.update_rule, RR.c.delete_rule], + sql.and_(C.c.table_name == table.name, + C.c.constraint_name == RR.c.constraint_name, + R.c.constraint_name == RR.c.unique_constraint_name + ), + order_by = [RR.c.constraint_name]) + rows = connection.execute(s).fetchall() </ins><span class="cx"> </span><ins>+ # group rows by constraint ID, to handle multi-column FKs + import pdb; pdb.set_trace() + fknm, scols, rcols = (None, [], []) + for r in rows: + scol, rschema, rtbl, rcol, rfknm, fkmatch, fkuprule, fkdelrule = r + if rfknm != fknm: + if fknm: + table.append_item(schema.ForeignKeyConstraint(scols, ['%s.%s' % (t,c) for (s,t,c) in rcols], fknm)) + fknm, scols, rcols = (rfknm, [], []) + if (not scol in scols): scols.append(scol) + if (not (rschema, rtbl, rcol) in rcols): rcols.append((rschema, rtbl, rcol)) </ins><span class="cx"> </span><ins>+ if fknm and scols: + table.append_item(schema.ForeignKeyConstraint(scols, ['%s.%s' % (t,c) for (s,t,c) in rcols], fknm)) + + + </ins><span class="cx"> class MSSQLCompiler(ansisql.ANSICompiler): </span><span class="cx"> def __init__(self, dialect, statement, parameters, **kwargs): </span><span class="cx"> super(MSSQLCompiler, self).__init__(dialect, statement, parameters, **kwargs) </span></span></pre> </div> </div> </body> </html> |