[Dbix-class] sqlite vs mysql

[shawn wilson]

On Fri, Jul 27, 2012 at 12:40 AM, Len Jaffe <lenjaffe at jaffesystems.com> wrote:
>
>
> On Fri, Jul 27, 2012 at 12:10 AM, shawn wilson <ag4ve.us at gmail.com> wrote:
>>
>> I made this script to operate with mysql. however, I also need it to
>> work with sqlite. I am having issues with primary keys with sqlite.
>> what am i doing wrong? and can i make this schema work with both db
>> types?
>>
>> the error is:
>> err: DBI Exception: DBD::SQLite::st execute failed: org.org_pk may not
>> be NULL [for Statement "INSERT INTO org ( agency, symbol, web_ag)
>> VALUES ( ?, ?, ? )"] at
>> /usr/local/share/perl/5.10.1/DBIx/Class/Schema.pm line 1071.
>>
>> [SNIP!]
>
>
> You should probably open your  sqlite db in a db browser (perhaps with
> sqlite3) and look at the table definition.  Make sure you created the table
> with auto-increment PK.  IT looks like you told Moose about it, but I could
> not determine if that's what you told the DB in your CREATE TABLE statement.
>

sqlite doesn't support auto increment per-se:
https://www.sqlite.org/faq.html#q1

i was thinking that dbic would just dwim as far as keys go, create the
other tables as 0s or 1s as the pk and then create the main (text)
table with those the same fk as was used for the pk for those
tables...?

so, the create tables for sqlite was this from mysql (without the
AUTO_INCREMENT):

CREATE TABLE text (
        text_pk         INT UNSIGNED    NOT NULL        PRIMARY KEY
AUTO_INCREMENT,
        org_fk          INT UNSIGNED    NOT NULL,
        dates_fk        INT UNSIGNED    NOT NULL,
        batch_fk        INT UNSIGNED    NOT NULL,
        stamp           DATETIME,
        description     TEXT,
        summary         TEXT,
        address         TEXT,
        page            TEXT,
        text_uri        TEXT,
        pdf_uri         TEXT,
        action          TEXT
)

CREATE TABLE org (
        org_pk          INT UNSIGNED    NOT NULL        PRIMARY KEY
AUTO_INCREMENT,
        symbol          VARCHAR(8),
        web_ag          TEXT,
        agency          TEXT,
        CONSTRAINT org_text     FOREIGN KEY( org_pk )
REFERENCES text( org_fk )
)