[Dbix-class] can't get last insert id on create() with mysql

[Rob Kinyon]

On Mon, Nov 30, 2009 at 16:46, Robert Stockdale
<robert.stockdale at gmail.com> wrote:
> Here is the create table statement:
>
> CREATE TABLE IF NOT EXISTS `STORY` (
>   `ID` int(15) NOT NULL auto_increment,
>   `CREATE_DATE` timestamp NOT NULL default CURRENT_TIMESTAMP,
>   `NAME` varchar(140) NOT NULL,
>   `DESCRIPTION` longtext,
>   `CREATOR_ID` varchar(15) NOT NULL,
>   PRIMARY KEY  (`ID`,`CREATE_DATE`),
> );
>
> It is keyed on ID and CREATE_DATE to allow for multiple revisions of the
> same story.
> I don't have a test case yet and I am not that familiar with DBI, but I'll
> work on a pure DBI test case and see if I can get it working and then I'll
> post back.

Yeah, that's your problem. The "Right Way"™ to do this would be
something as follows:

CREATE TABLE stories (
    id int auto_increment primary key
   ,creator_id int not null REFERENCES users (id)
   `NAME` varchar(140) NOT NULL,
);

CREATE TABLE story_revisions (
    id int auto_increment primary key
   ,story_id int not null REFERENCES stories (id)
   `CREATE_DATE` timestamp NOT NULL default CURRENT_TIMESTAMP,
   `DESCRIPTION` longtext
);

You need the concept of a story (with an owner, name, and other
metadata), then the concept of a revision (with a data and some
content).

I suspect that other hacks in your system will fall away once you use
the two-table method.

Rob