Login | Register
My pages Projects Community openCollabNet

propel
Reply to message

* = Required fields
* Subject
* Body
Attachments
Send reply to
Topic
Author (directly in email)
Please type the letters in the image above.

Original message

Author hlellelid
Full name Hans Lellelid
Date 2004-04-12 12:31:33 PDT
Message Hi Denny,

No, I'm glad you sent the email! :) It just means that this needs to get
fixed asap. What are you people doing using MySQL w/ foreign keys? :)
I'll try to get that issue fixed today. I'll send a note to this list
when I've fixed it. In the short-term, you can just tweak your generated
SQL files, adding the INDEX() for each fkey column.

This isn't a hard template fix, just need to make sure it takes into
consideration possibility that a fkey column was already specified as an
index, etc.

Thanks,
Hans


> OK... obviously I'm an idiot. :)
> I just sent an email complaining about this very thing
> RIGHT AFTER I read through it.
>
> <sigh>
>
> -----Original Message-----
> From: Hans Lellelid [mailto:hans at velum dot net]
> Sent: Monday, April 12, 2004 2:54 PM
> To: users at propel dot tigris dot org
> Subject: Re: [propel] Many-to-Many Relationship Problem
>
>
> Hi Matt,
>
> Yes, I believe this is a known issue (and in the bug tracker for me to
> tackle). I believe the error you got had something to do with lack of
> indexes on the fkey constraints, correct? This seems to be a
> mysql/innodb issue -- well, not an issue, just a difference between
> mysql and other dbs that use fkeys. This should be no problem to fix,
> since we have individual templates for all the db systems. I just
> haven't done it yet
> :) I think there's even some code on the dev list.... I'll try to get
> this done ASAP.
>
> If I'm wrong about the problem, let me know.
>
> Thanks,
> Hans
>
>> Hello,
>>
>> I have never used Foreign Keys before and on Saturday I found a
>> problem. I'm not sure if there is a problem in propel or maybe I
>> missed something in my schema file. The schema is attached below.
>>
>> I created the first query after seeing a working example in the mysql
>> manual.
>>
>> The following CREATE TABLE works:
>> CREATE TABLE account_priv_ref(
>>
>> account INTEGER NOT NULL ,
>>
>> privilege INTEGER NOT NULL ,
>>
>> PRIMARY KEY(account,privilege),
>> INDEX(account),
>> FOREIGN KEY (account) REFERENCES account (id),
>> INDEX(privilege),
>> FOREIGN KEY (privilege) REFERENCES privilege (id)) Type=InnoDB;
>>
>> This query was created by the propel build file.
>> This CREATE TABLE fails: (errno: 150 [foreign-key problem]) CREATE
>> TABLE account_priv_ref(
>>
>> account INTEGER NOT NULL ,
>>
>> privilege INTEGER NOT NULL ,
>>
>> PRIMARY KEY(account,privilege),
>> FOREIGN KEY (account) REFERENCES account (id),
>> FOREIGN KEY (privilege) REFERENCES privilege (id)) Type=InnoDB;
>>
>> Here are the other two related tables:
>> CREATE TABLE account(
>>
>> id INTEGER NOT NULL ,
>>
>> firstName VARCHAR(30) NOT NULL ,
>>
>> lastName VARCHAR(30) NOT NULL ,
>>
>> email VARCHAR(100) ,
>>
>> username VARCHAR(30) NOT NULL ,
>>
>> password VARCHAR(30) NOT NULL ,
>>
>> externalId VARCHAR(30) ,
>>
>> PRIMARY KEY(id)) Type=InnoDB;
>>
>> CREATE TABLE privilege(
>>
>> id INTEGER NOT NULL ,
>>
>> name VARCHAR(50) NOT NULL ,
>>
>> namepsace VARCHAR(200) NOT NULL ,
>>
>> PRIMARY KEY(id)) Type=InnoDB;
>>
>> Mysql version is 4.1.0-max-alpha, binary distribution, on freebsd 4.9.
>>
>> Schema
>> ---
>> <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?> <!DOCTYPE
>> database SYSTEM "../dtd/database.dtd">
>>
>> <database name="athena" defaultIdMethod="native">
>> <table name="account" description="User Table">
>> <column name="id" required="true" primaryKey="true" type="INTEGER"
>> description="id" /> <column name="firstName" required="true"
>> type="VARCHAR" size="30" description="First Name of User" />
>> <column name="lastName" required="true" type="VARCHAR" size="30"
>> description="Last Name of User" />
>> <column name="email" required="false" type="VARCHAR" size="100"
>> description="Email Address of User" />
>> <column name="username" required="true" type="VARCHAR" size="30"
>> description="Account username" />
>> <column name="password" required="true" type="VARCHAR" size="30"
>> description="Account password" />
>> <column name="externalId" required="false" type="VARCHAR" size="30"
>> description="External Account Id" />
>> </table>
>>
>> <table name="privilege" description="List of all Privileges"> <column
>> name="id" required="true" primaryKey="true" type="INTEGER"
>> description="id" /> <column name="name" required="true" type="VARCHAR"
>
>> size="50" description="Name of Privilege" />
>> <column name="namepsace" required="true" type="VARCHAR" size="200"
>> description="Namespace the privilege resides in" />
>> </table>
>>
>> <table name="account_priv_ref" description="Maps Accounts to Available
>
>> Privileges" /> <column name="account" type="INTEGER" required="true"
>> primaryKey="true" />
>> <column name="privilege" type="INTEGER" required="true"
>> primaryKey="true" />
>> <foreign-key foreignTable="account">
>> <reference local="account" foreign="id"/>
>> </foreign-key>
>> <foreign-key foreignTable="privilege">
>> <reference local="privilege" foreign="id"/>
>> </foreign-key>
>> </table>
>>
>> </database>
>>
>> Thanks,
>>
>> Matt
>
>
> --------------------​--------------------​--------------------​---------
> To unsubscribe, e-mail: users-unsubscribe@pr​opel.tigris.org
> For additional commands, e-mail: users-help at propel dot tigris dot org
>
>
> --------------------​--------------------​--------------------​---------
> To unsubscribe, e-mail: users-unsubscribe@pr​opel.tigris.org
> For additional commands, e-mail: users-help at propel dot tigris dot org
>
>


--------------------​--------------------​--------------------​---------
To unsubscribe, e-mail: users-unsubscribe@pr​opel.tigris.org
For additional commands, e-mail: users-help at propel dot tigris dot org