On Jul 13, 2010, at 10:31 AM, Michael Bayer wrote:
>
> On Jul 13, 2010, at 5:44 AM, Ralph Heinkel wrote:
>
>> Hi,
>>
>> this might be another small bug in SA. It shows up when an Sqlite db
>> is composed of multiple sub-DBs via the 'attach' statement. This is
>> useful for mimicking schemata and allows tables to be addressed like
>> <schema>.<table> (using common dot-syntax).
>>
>> To make the following work run this statement in your sqlite3 shell /
>> db:
>>
>>> attach "master.db" as master;
>>
>> The following table definition is used in SA:
>>
>> sa_table = Table('project', metadata,
>> Column('project_id', Integer,
>> Sequence('seq_project_id', schema='master'), primary_key=True),
>> Column('name', String(20), nullable=False),
>> Column('owner_id', Integer,
>> ForeignKey('master.owner.owner_id')),
>> owner='master', schema='master')
>>
>> which leads to a statement (through "metadata.create_all(engine)")
>>
>> CREATE TABLE master.project (
>> project_id INTEGER NOT NULL,
>> owner_id INTEGER,
>> PRIMARY KEY (project_id),
>> FOREIGN KEY(owner_id) REFERENCES master.owner (owner_id)
>> );
>>
>> However this fails. The problem is REFERENCES master.owner where
>> sqlite complains about the dot in the referenced table name.
>> The solution could be to use square brackets (i.e. REFERENCES
>> [master.owner] ) or quotes (i.e. REFERENCES "master.owner" ) for
>> sqlite.
>> Unfortunately both solutions seem to be somehow unique to sqlite, they
>> fail e.g. with postgres. I haven't tried other DBs.
>>
>> Any idea how to solve this?
>
> this is a variant of #1439. please add a ticket to trac.
I would also add that the likely solution here would be to not render the
REFERENCES clause at all. The quoted solution you have seems like a hack
which takes advantage of the fact that sqlite foreign keys are disabled by
default and are meaningless. I bet if you enabled foreign keys, it would
fail. I can find no documentation on sqlite's site indicating how to render
foreign key references to remote databases and I'd guess its not supported.
--
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.
