Hi

My app is using SQLAlchemy0.5.6.
I have a class defined as follows.

class EmailSetup(DeclarativeBase):
__tablename__ = 'emailsetup'
id = Column(Unicode(50), primary_key=True)
mail_server=Column(Unicode(255))
description=Column(String(200))
port = Column(Integer)
use_secure = Column(Integer) #No, TLS, SSL
site_id = Column(Unicode(50),
ForeignKey('sites.id',onupdate="CASCADE", ondelete="CASCADE"))
credential=relation(Credential, \
primaryjoin=id == Credential.entity_id,\
foreign_keys=[Credential.entity_id],\
uselist=False,cascade='all, delete, delete-
orphan')


this works fine on ubuntu installation . (MySQL 5.1.37)
it creates foreign constraint to the sites table.

CREATE TABLE `emailsetup` (
`id` varchar(50) NOT NULL,
`mail_server` varchar(255) DEFAULT NULL,
`description` varchar(200) DEFAULT NULL,
`port` int(11) DEFAULT NULL,
`use_secure` int(11) DEFAULT NULL,
`site_id` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `site_id` (`site_id`),
CONSTRAINT `emailsetup_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES
`sites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8


But in centos (MySQL 5.0.77) , the foreign key constraint is missing
when i check with a show create table.

CREATE TABLE `emailsetup` (
`id` varchar(50) NOT NULL,
`mail_server` varchar(255) default NULL,
`description` varchar(200) default NULL,
`port` int(11) default NULL,
`use_secure` int(11) default NULL,
`site_id` varchar(50) default NULL,
PRIMARY KEY (`id`),
KEY `site_id` (`site_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


In both the SA create table statement is like this.

REATE TABLE emailsetup (
id VARCHAR(50) NOT NULL,
mail_server VARCHAR(255),
description VARCHAR(200),
port INTEGER,
use_secure INTEGER,
site_id VARCHAR(50),
PRIMARY KEY (id),
FOREIGN KEY(site_id) REFERENCES sites (id) ON DELETE CASCADE ON
UPDATE CASCADE
)

any idea what is happening

I manually created a table in Centos with type=Innodb
statement,this created the constraint.
Is this an issue related to MySQL?

Thanks in advance

On 2011-01-04 09:51, dhanil anupurath wrote:
[...]
In the Centos system, is all other foreign keys created al right by
SQLAlchemy? Also, is the primary key on sites present?

/Lennart

On 1/4/2011 3:51 AM, dhanil anupurath wrote:
MySQL knows nothing about Python classes. It only knows SQL.

What are the SQL statements generated by SQLAlchemy (not the one from
SHOW CREATE TABLE)?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Jan 4, 3:13*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
All the other tables created with Foreign Keys and Primary Keys
correctly.
Only missing instant for table creation here is Constraint .

On Jan 4, 5:57*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:
both
---------------------------------------------------------------------------------
2011-01-03 09:47:43,004 INFO sqlalchemy.engine.base.Engine.0x...a22c
()
2011-01-03 09:47:43,011 INFO sqlalchemy.engine.base.Engine.0x...a22c
COMMIT
2011-01-03 09:47:43,012 INFO sqlalchemy.engine.base.Engine.0x...a22c
CREATE INDEX s_id ON sites (id)
2011-01-03 09:47:43,013 INFO sqlalchemy.engine.base.Engine.0x...a22c
()
2011-01-03 09:47:43,018 INFO sqlalchemy.engine.base.Engine.0x...a22c
COMMIT
2011-01-03 09:47:43,021 INFO sqlalchemy.engine.base.Engine.0x...a22c
CREATE TABLE emailsetup (
id VARCHAR(50) NOT NULL,
mail_server VARCHAR(255),
description VARCHAR(200),
port INTEGER,
use_secure INTEGER,
site_id VARCHAR(50),
PRIMARY KEY (id),
FOREIGN KEY(site_id) REFERENCES sites (id) ON DELETE CASCADE ON
UPDATE CASCADE
)

On 1/5/2011 1:21 AM, dhanil anupurath wrote:
OK, I misunderstood your original post. I thought the CREATE TABLE you
had was from the working system.

The only reason it wouldn't would be if the table already existed. Any
failure of the foreign key constraint (i.e. lack of appropriate index on
the parent table, mismatch of data types, etc.) would cause the CREATE
TABLE to fail. But the table exists, which means either this wasn't the
CREATE TABLE being used, or the table already existed.

I don't doubt the SQLAlchemy log, so I need to ask - are you sure the
table didn't exist beforehand, without the constraint? Or is there more
information in the SQLAlchemy log possibly showing a failure of this
CREATE TABLE (and possibly an attempt to issue another CREATE TABLE)?

To see what MySQL is actually doing, however, you should use the general
query log (warning - major security exposure and potentially large
performance hit, but great for testing). See

http://dev.mysql.com/doc/refman/5.5/en/query-log.html

It really helps when things don't make sense.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Jan 5, 7:40*am, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

It appears that the reason so many companies cannot find qualified IT
talent is that too many script-kiddies try to use these -often
inefficient and broken -- framework applications instead of actually
writing code themselves. There is a HUGE difference in using the
stuff used in academia and the real word. SQLAlchemy is one of those
tools. I know of companies looking for qualified JAVA developers and
DBAs but can't find talent that knows anything but Spring or Flex or
this tool or that tool... The real world runs on real code - not
frameworks. Those are great for quick and dirty proofs-of-concept,
but fall short when it comes to making the app actually perform.

M. (getting down off of his soapbox)

On 1/5/2011 9:08 PM, onedbguru wrote:
I partially agree with you here.

I don't think there's anything wrong with using a framework. It does
speed development. But one must know and understand what the framework
does and the code it generates, and be able to troubleshoot that code.
Too many script-kiddies can't do that.

Now I'm not saying the original op here is a script-kiddie. I've never
used SQLAlchemy, but it does look like an interesting package to help
generate databases. It would be nice to have something similar for PHP
- for instance, right now I'm working on a project with 47 tables. It's
very time consuming to generate the SQL for all those tables plus the
PHP code for the database layer. But I haven't found anything I like
yet, anyway.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Thu, 06 Jan 2011 08:39:03 -0500, Jerry Stuckle wrote:
Me either. Thus, I tend to write and maintain installer scripts right
along with the thing I'm writing, on top of keeping a dump of the
database right in the source repository.

--
A *huge* proportion of people cannot make *correct and accurate*
generalisations of principles. They have to learn everything as if
it's an unrelated piece of crap, BECAUSE THEY ARE STUPID! PEOPLE ARE
STUPID! -- Thorfinn in the Monastery