On Nov 12, 2010, at 9:37 AM, Gennady Kovalev wrote: > Hi! > > In production server, in our product we use postgresql as db engine, > than supports schema name as sqlalchmy's feature. But we have unit- > tests, that must passed without postgresql server. > > In code we use schema names and foreign keys. There are a lot of > topics with "sqlite does not support foreign keys to external > database". > > I read that Michael Bayer sad do not use ForeignKey() with sqlite. But > we must use it for postgresql. > > The question is: may be add some argument for sqlite dialect, that > suppress foreign key constraint?
I don't think I would have ever said that ForeignKey() should not be used with SQLite. ForeignKey() should be used liberally since SQLAlchemy assigns importance to this token as defined in table metadata. SQLite by default ignores REFERENCES clauses when CREATE TABLE is issued so there is usually no issue there. However, you're likely referring here to ticket 1851, http://www.sqlalchemy.org/trac/ticket/1851, which is specific to the REFERENCES clause when rendered with a table that has a schema name. The only issue is that we had no documentation on how to generate the REFERENCES clause to a remote table that has a schema name. I've installed sqlite3 version 3.6.20 so that I could test its foreign key support and the correct syntax is stated in that ticket, i.e. you omit the schema name in the REFERENCES clause and the remote table is assumed to be in the same schema. I implemented that fix, as well as an additional change so that the REFERENCES clause is omitted entirely only if the two "schemas" of the tables are different, that is as of 8cc53b0afb99 . > > See the patch for example: > > > diff -ru sqlalchemy.orig/dialects/sqlite/base.py sqlalchemy/dialects/ > sqlite/base.py > --- sqlalchemy.orig/dialects/sqlite/base.py 2010-11-12 > 17:13:23.000000000 +0300 > +++ sqlalchemy/dialects/sqlite/base.py 2010-11-12 17:20:02.000000000 > +0300 > @@ -272,6 +272,13 @@ > visit_primary_key_constraint(constraint) > > > + def visit_foreign_key_constraint(self, constraint): > + # Suppress foreign key constraint if configured > + if getattr(self.dialect, 'suppress_fk_constraint', False): > + return None > + return super(SQLiteDDLCompiler, self).\ > + visit_foreign_key_constraint(constraint) > + > def visit_create_index(self, create): > index = create.element > preparer = self.preparer > @@ -342,7 +349,8 @@ > supports_cast = True > supports_default_values = True > > - def __init__(self, isolation_level=None, native_datetime=False, > **kwargs): > + def __init__(self, isolation_level=None, native_datetime=False, > + > suppress_fk_constraint=False, **kwargs): > default.DefaultDialect.__init__(self, **kwargs) > if isolation_level and isolation_level not in > ('SERIALIZABLE', > 'READ UNCOMMITTED'): > @@ -350,6 +358,8 @@ > "Valid isolation levels for sqlite are 'SERIALIZABLE' > and " > "'READ UNCOMMITTED'.") > self.isolation_level = isolation_level > + > + self.suppress_fk_constraint = suppress_fk_constraint > > # this flag used by pysqlite dialect, and perhaps others in > the > # future, to indicate the driver is handling date/timestamp > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
