[Sqlalchemy-commits] [1048] sqlalchemy/branches/indexes/test: Add support for inline index syntax ou
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] [1048] sqlalchemy/branches/indexes/test: Add support for inline index syntax outlined in #6
|
From: <co...@sq...> - 2006-02-26 18:34:31
|
<!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>[1048] sqlalchemy/branches/indexes/test: Add support for inline index syntax outlined in #6</title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>1048</dd> <dt>Author</dt> <dd>jpellerin</dd> <dt>Date</dt> <dd>2006-02-26 12:34:20 -0600 (Sun, 26 Feb 2006)</dd> </dl> <h3>Log Message</h3> <pre>Add support for inline index syntax outlined in #6</pre> <h3>Modified Paths</h3> <ul> <li><a href="#sqlalchemybranchesindexeslibsqlalchemyansisqlpy">sqlalchemy/branches/indexes/lib/sqlalchemy/ansisql.py</a></li> <li><a href="#sqlalchemybranchesindexeslibsqlalchemydatabasessqlitepy">sqlalchemy/branches/indexes/lib/sqlalchemy/databases/sqlite.py</a></li> <li><a href="#sqlalchemybranchesindexeslibsqlalchemyschemapy">sqlalchemy/branches/indexes/lib/sqlalchemy/schema.py</a></li> <li><a href="#sqlalchemybranchesindexeslibsqlalchemysqlpy">sqlalchemy/branches/indexes/lib/sqlalchemy/sql.py</a></li> <li><a href="#sqlalchemybranchesindexestestindexespy">sqlalchemy/branches/indexes/test/indexes.py</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="sqlalchemybranchesindexeslibsqlalchemyansisqlpy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/indexes/lib/sqlalchemy/ansisql.py (1047 => 1048)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/indexes/lib/sqlalchemy/ansisql.py 2006-02-26 18:04:14 UTC (rev 1047) +++ sqlalchemy/branches/indexes/lib/sqlalchemy/ansisql.py 2006-02-26 18:34:20 UTC (rev 1048) </span><span class="lines">@@ -517,8 +517,11 @@ </span><span class="cx"> self.append("\tPRIMARY KEY (%s)" % string.join([c.name for c in pks],', ')) </span><span class="cx"> </span><span class="cx"> self.append("\n)%s\n\n" % self.post_create_table(table)) </span><del>- self.execute() - </del><ins>+ self.execute() + if hasattr(table, 'indexes'): + for index in table.indexes: + self.visit_index(index) + </ins><span class="cx"> def post_create_table(self, table): </span><span class="cx"> return '' </span><span class="cx"> </span><span class="lines">@@ -550,6 +553,8 @@ </span><span class="cx"> self.execute() </span><span class="cx"> </span><span class="cx"> def visit_table(self, table): </span><ins>+ # NOTE: indexes on the table will be automatically dropped, so + # no need to drop them individually </ins><span class="cx"> self.append("\nDROP TABLE " + table.fullname) </span><span class="cx"> self.execute() </span><span class="cx"> </span></span></pre></div> <a id="sqlalchemybranchesindexeslibsqlalchemydatabasessqlitepy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/indexes/lib/sqlalchemy/databases/sqlite.py (1047 => 1048)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/indexes/lib/sqlalchemy/databases/sqlite.py 2006-02-26 18:04:14 UTC (rev 1047) +++ sqlalchemy/branches/indexes/lib/sqlalchemy/databases/sqlite.py 2006-02-26 18:34:20 UTC (rev 1048) </span><span class="lines">@@ -260,6 +260,9 @@ </span><span class="cx"> self.append("\tUNIQUE (%s)" % string.join([c.name for c in table.primary_key],', ')) </span><span class="cx"> </span><span class="cx"> self.append("\n)\n\n") </span><del>- self.execute() </del><ins>+ self.execute() + if hasattr(table, 'indexes'): + for index in table.indexes: + self.visit_index(index) </ins><span class="cx"> </span><span class="cx"> </span></span></pre></div> <a id="sqlalchemybranchesindexeslibsqlalchemyschemapy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/indexes/lib/sqlalchemy/schema.py (1047 => 1048)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/indexes/lib/sqlalchemy/schema.py 2006-02-26 18:04:14 UTC (rev 1047) +++ sqlalchemy/branches/indexes/lib/sqlalchemy/schema.py 2006-02-26 18:34:20 UTC (rev 1048) </span><span class="lines">@@ -160,7 +160,10 @@ </span><span class="cx"> self.primary_key.append(column) </span><span class="cx"> column.table = self </span><span class="cx"> column.type = self.engine.type_descriptor(column.type) </span><del>- </del><ins>+ + def append_index(self, index): + self.indexes[index.name] = index + </ins><span class="cx"> def _set_parent(self, schema): </span><span class="cx"> schema.tables[self.name] = self </span><span class="cx"> self.schema = schema </span><span class="lines">@@ -170,6 +173,32 @@ </span><span class="cx"> for c in self.columns: </span><span class="cx"> c.accept_schema_visitor(visitor) </span><span class="cx"> return visitor.visit_table(self) </span><ins>+ + def append_index_column(self, column, index=None, unique=None): + """Add an index or a column to an existing index of the same name. + """ + if index is not None and unique is not None: + raise ValueError("index and unique may not both be specified") + if index: + if index is True: + name = 'ix_%s' % column.name + else: + name = index + elif unique: + if unique is True: + name = 'ux_%s' % column.name + else: + name = unique + # find this index in self.indexes + # add this column to it if found + # otherwise create new + try: + index = self.indexes[name] + index.append_column(column) + except KeyError: + index = Index(name, column, unique=unique) + return index + </ins><span class="cx"> def deregister(self): </span><span class="cx"> """removes this table from it's engines table registry. this does not </span><span class="cx"> issue a SQL DROP statement.""" </span><span class="lines">@@ -224,9 +253,22 @@ </span><span class="cx"> which will be invoked upon insert if this column is not present in the insert list or is given a value </span><span class="cx"> of None. </span><span class="cx"> </span><del>- hidden=False : indicates this column should not be listed in the table's list of columns. Used for the "oid" - column, which generally isnt in column lists. - """ </del><ins>+ hidden=False : indicates this column should not be listed in the + table's list of columns. Used for the "oid" column, which generally + isnt in column lists. + + index=None : True or index name. Indicates that this column is + indexed. Pass true to autogenerate the index name. Pass a string to + specify the index name. Multiple columns that specify the same index + name will all be included in the index, in the order of their + creation. + + unique=None : True or undex name. Indicates that this column is + indexed in a unique index . Pass true to autogenerate the index + name. Pass a string to specify the index name. Multiple columns that + specify the same index name will all be included in the index, in the + order of their creation. """ + </ins><span class="cx"> name = str(name) # in case of incoming unicode </span><span class="cx"> super(Column, self).__init__(name, None, type) </span><span class="cx"> self.args = args </span><span class="lines">@@ -235,6 +277,10 @@ </span><span class="cx"> self.nullable = kwargs.pop('nullable', not self.primary_key) </span><span class="cx"> self.hidden = kwargs.pop('hidden', False) </span><span class="cx"> self.default = kwargs.pop('default', None) </span><ins>+ self.index = kwargs.pop('index', None) + self.unique = kwargs.pop('unique', None) + if self.index is not None and self.unique is not None: + raise ArgumentError("Column may not define both index and unique") </ins><span class="cx"> self._foreign_key = None </span><span class="cx"> self._orig = None </span><span class="cx"> self._parent = None </span><span class="lines">@@ -269,6 +315,10 @@ </span><span class="cx"> if getattr(self, 'table', None) is not None: </span><span class="cx"> raise ArgumentError("this Column already has a table!") </span><span class="cx"> table.append_column(self) </span><ins>+ if self.index or self.unique: + table.append_index_column(self, index=self.index, + unique=self.unique) + </ins><span class="cx"> if self.default is not None: </span><span class="cx"> self.default = ColumnDefault(self.default) </span><span class="cx"> self._init_items(self.default) </span><span class="lines">@@ -429,7 +479,6 @@ </span><span class="cx"> class Index(SchemaItem): </span><span class="cx"> """Represents an index of columns from a database table </span><span class="cx"> """ </span><del>- </del><span class="cx"> def __init__(self, name, *columns, **kw): </span><span class="cx"> """Constructs an index object. Arguments are: </span><span class="cx"> </span><span class="lines">@@ -443,24 +492,34 @@ </span><span class="cx"> unique=True : create a unique index </span><span class="cx"> """ </span><span class="cx"> self.name = name </span><del>- self.columns = columns </del><ins>+ self.columns = [] + self.table = None </ins><span class="cx"> self.unique = kw.pop('unique', False) </span><del>- self._init_items() </del><ins>+ self._init_items(*columns) </ins><span class="cx"> </span><span class="cx"> engine = property(lambda s:s.table.engine) </span><del>- def _init_items(self): </del><ins>+ def _init_items(self, *args): + for column in args: + self.append_column(column) + + def append_column(self, column): </ins><span class="cx"> # make sure all columns are from the same table </span><del>- # FIXME: and no column is repeated - self.table = None - for column in self.columns: - if self.table is None: - self.table = column.table - elif column.table != self.table: - # all columns muse be from same table - raise ArgumentError("All index columns must be from same table. " - "%s is from %s not %s" % (column, - column.table, - self.table)) </del><ins>+ # and no column is repeated + if self.table is None: + self.table = column.table + self.table.append_index(self) + elif column.table != self.table: + # all columns muse be from same table + raise ArgumentError("All index columns must be from same table. " + "%s is from %s not %s" % (column, + column.table, + self.table)) + elif column.name in [ c.name for c in self.columns ]: + raise ArgumentError("A column may not appear twice in the " + "same index (%s already has column %s)" + % (self.name, column)) + self.columns.append(column) + </ins><span class="cx"> def create(self): </span><span class="cx"> self.engine.create(self) </span><span class="cx"> return self </span><span class="lines">@@ -481,11 +540,6 @@ </span><span class="cx"> class SchemaEngine(object): </span><span class="cx"> """a factory object used to create implementations for schema objects. This object </span><span class="cx"> is the ultimate base class for the engine.SQLEngine class.""" </span><del>- def indeximpl(self, index): - """returns a new implementation object for an Index (usually - sql.IndexImpl) - """ - raise NotImplementedError() </del><span class="cx"> def reflecttable(self, table): </span><span class="cx"> """given a table, will query the database and populate its Column and ForeignKey </span><span class="cx"> objects.""" </span><span class="lines">@@ -506,7 +560,7 @@ </span><span class="cx"> """visit a ForeignKey.""" </span><span class="cx"> pass </span><span class="cx"> def visit_index(self, index): </span><del>- """visit an Index (not implemented yet).""" </del><ins>+ """visit an Index.""" </ins><span class="cx"> pass </span><span class="cx"> def visit_passive_default(self, default): </span><span class="cx"> """visit a passive default""" </span></span></pre></div> <a id="sqlalchemybranchesindexeslibsqlalchemysqlpy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/indexes/lib/sqlalchemy/sql.py (1047 => 1048)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/indexes/lib/sqlalchemy/sql.py 2006-02-26 18:04:14 UTC (rev 1047) +++ sqlalchemy/branches/indexes/lib/sqlalchemy/sql.py 2006-02-26 18:34:20 UTC (rev 1048) </span><span class="lines">@@ -982,11 +982,14 @@ </span><span class="cx"> super(TableClause, self).__init__(name) </span><span class="cx"> self.name = self.id = self.fullname = name </span><span class="cx"> self._columns = util.OrderedProperties() </span><ins>+ self._indexes = util.OrderedProperties() </ins><span class="cx"> self._foreign_keys = [] </span><span class="cx"> self._primary_key = [] </span><span class="cx"> for c in columns: </span><span class="cx"> self.append_column(c) </span><span class="cx"> </span><ins>+ indexes = property(lambda s:s._indexes) + </ins><span class="cx"> def append_column(self, c): </span><span class="cx"> self._columns[c.text] = c </span><span class="cx"> c.table = self </span></span></pre></div> <a id="sqlalchemybranchesindexestestindexespy"></a> <div class="modfile"><h4>Modified: sqlalchemy/branches/indexes/test/indexes.py (1047 => 1048)</h4> <pre class="diff"><span> <span class="info">--- sqlalchemy/branches/indexes/test/indexes.py 2006-02-26 18:04:14 UTC (rev 1047) +++ sqlalchemy/branches/indexes/test/indexes.py 2006-02-26 18:34:20 UTC (rev 1048) </span><span class="lines">@@ -6,8 +6,12 @@ </span><span class="cx"> </span><span class="cx"> def setUp(self): </span><span class="cx"> self.created = [] </span><del>- </del><ins>+ self.echo = testbase.db.echo + self.logger = testbase.db.logger + </ins><span class="cx"> def tearDown(self): </span><ins>+ testbase.db.echo = self.echo + testbase.db.logger = testbase.db.engine.logger = self.logger </ins><span class="cx"> if self.created: </span><span class="cx"> self.created.reverse() </span><span class="cx"> for entity in self.created: </span><span class="lines">@@ -26,11 +30,87 @@ </span><span class="cx"> employees.c.last_name, employees.c.first_name) </span><span class="cx"> i.create() </span><span class="cx"> self.created.append(i) </span><ins>+ assert employees.indexes['employee_name_index'] is i </ins><span class="cx"> </span><del>- i = Index('employee_email_index', - employees.c.email_address, unique=True) </del><ins>+ i2 = Index('employee_email_index', + employees.c.email_address, unique=True) + i2.create() + self.created.append(i2) + assert employees.indexes['employee_email_index'] is i2 + + def test_index_create_camelcase(self): + """test that mixed-case index identifiers are legal""" + employees = Table('companyEmployees', testbase.db, + Column('id', Integer, primary_key=True), + Column('firstName', String), + Column('lastName', String), + Column('emailAddress', String)) + employees.create() + self.created.append(employees) + + i = Index('employeeNameIndex', + employees.c.lastName, employees.c.firstName) </ins><span class="cx"> i.create() </span><span class="cx"> self.created.append(i) </span><span class="cx"> </span><ins>+ i = Index('employeeEmailIndex', + employees.c.emailAddress, unique=True) + i.create() + self.created.append(i) + + # Check that the table is useable. This is mostly for pg, + # which can be somewhat sticky with mixed-case identifiers + employees.insert().execute(firstName='Joe', lastName='Smith') + ss = employees.select().execute().fetchall() + assert ss[0].firstName == 'Joe' + assert ss[0].lastName == 'Smith' + + def test_index_create_inline(self): + """Test indexes defined with tables""" + + testbase.db.echo = True + capt = [] + class dummy: + pass + stream = dummy() + stream.write = capt.append + testbase.db.logger = testbase.db.engine.logger = stream + + events = Table('events', testbase.db, + Column('id', Integer, primary_key=True), + Column('name', String(30), unique=True), + Column('location', String(30), index=True), + Column('sport', String(30), + unique='sport_announcer'), + Column('announcer', String(30), + unique='sport_announcer'), + Column('winner', String(30), index='idx_winners')) + + index_names = [ ix.name for ix in events.indexes ] + assert 'ux_name' in index_names + assert 'ix_location' in index_names + assert 'sport_announcer' in index_names + assert 'idx_winners' in index_names + assert len(index_names) == 4 + + events.create() + self.created.append(events) + + # verify that the table is functional + events.insert().execute(id=1, name='hockey finals', location='rink', + sport='hockey', announcer='some canadian', + winner='sweden') + ss = events.select().execute().fetchall() + + assert capt[0].strip().startswith('CREATE TABLE events') + assert capt[2].strip() == \ + 'CREATE UNIQUE INDEX ux_name ON events (name)' + assert capt[4].strip() == \ + 'CREATE INDEX ix_location ON events (location)' + assert capt[6].strip() == \ + 'CREATE UNIQUE INDEX sport_announcer ON events (sport, announcer)' + assert capt[8].strip() == \ + 'CREATE INDEX idx_winners ON events (winner)' + </ins><span class="cx"> if __name__ == "__main__": </span><span class="cx"> testbase.main() </span></span></pre> </div> </div> </body> </html> |
