I see that. Well, heres the questions:
1. do we want to make InnoDB the default when MySQL creates
tables ? Do we ever want to use myISAM ?
2. if both are needed, how about a "use_innodb" or "use_myisam"
option to create_engine with mysql...which is the default? :
# if myisam is the default
x = create_engine('mysql://user=foo&etc..', use_innodb=True)
# if innodb is the default
x = create_engine('mysql://user=foo&etc..', use_myisam=True)
3. same thing, use_innodb/use_myisam keyword arguments on Table() ?
im pretty sure its a yes on this at least...
4. Well the docs are pretty clear, the only ways to get at those
foreign keys from a describe is via SHOW CREATE TABLE or a SHOW TABLE
STATUS, each of which require some regular expression wizardry and
close-watches on changes in string syntax from version to version to
get at them.
a. do we want to use the current DESCRIBE/SHOW TABLE STATUS to
reflect tables, or just SHOW CREATE TABLE (I sort of like SHOW CREATE
TABLE since its one-stop shopping) ? do we want to use one for
myISAM and the other for InnoDB ?
b. is anyone using MySQL 5 yet ? do InnoDB users genrally use MySQL
5 or is 3/4 support just as important ? should we deal at all with
mysql5's information_schema support or its better to just do
something that works for all 3 versions ?
c. does anyone want to take a crack at this ? just a single
function - i.e. I give you a SHOW CREATE TABLE, you give me a list of
columns, types, foreign keys, INNODB status, etc.
On Feb 5, 2006, at 12:59 PM, Wade Leftwich wrote:
MySQL 4.1 (and earlier, I think) supports foreign keys if your table
storage is "InnoDB", instead of the default "myISAM". This is also how
you get proper commit() and rollback() behavior in MySQL.
http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-
constraints.html
To make an InnoDB table, the CREATE statement looks like this:
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
You can also do an ALTER TABLE to convert a myISAM table to InnoDB,
but
then you would also have to go in and add the foreign key constraints
via ALTER TABLE.
-- Wade
Michael Bayer wrote:
OK, this is terrific - but here is the zillion dollar question:
how do
I get the FOREIGN KEY information back from a DESCRIBE (or
similar) ?
where are you seeing that it "stores" the foreign key in one case
and
not in the other (AFAIK mysql 4.1 doesnt actually *support* foreign
keys ?)
On Feb 5, 2006, at 8:52 AM, Alastair Houghton wrote:
Hi all,
I'm currently evaluating SQLAlchemy for a fair-sized project; I
like
the way it doesn't force data objects to inherit from something
from
its own framework, and I also like the way that all the table
definitions and database mappings can be put in one place rather
than
distributing them throughout the code.
Anyway, I noticed that MySQL (which I'm using as a back end) wasn't
storing the foreign key constraints, although there seemed to be
some
attempt in the code to mark them; it looks to me like the
version of
MySQL I'm using (4.1.13) doesn't support---or rather,
*ignores*---the
syntax that mysql.py is generating for foreign key references.
This
patch
Index: lib/sqlalchemy/databases/mysql.py
===================================================================
--- lib/sqlalchemy/databases/mysql.py (revision 905)
+++ lib/sqlalchemy/databases/mysql.py (working copy)
@@ -215,6 +215,6 @@
if first_pk and isinstance(column.type, types.Integer):
colspec += " AUTO_INCREMENT"
if column.foreign_key:
- colspec += " REFERENCES %s(%s)" %
(column.column.foreign_key.column.table.name,
column.column.foreign_key.column.name)
+ colspec += ", FOREIGN KEY (%s) REFERENCES %s(%s)" %
(column.name, column.column.foreign_key.column.table.name,
column.column.foreign_key.column.name)
return colspec
changes the code so that it adds a separate foreign key constraint
instead, which does seem to work.
Kind regards,
Alastair.
--
http://www.alastairs-place.net
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
