[Dbix-class] sqlite vs mysql

[shawn wilson]

btw, i figured it out (with help from folks on irc). it seems that the
only thing that auto increments is an 'integer' - not an unsigned int
or int, but only an integer. fix that and everything works as it
should.

On Fri, Jul 27, 2012 at 1:39 AM, shawn wilson <ag4ve.us at gmail.com> wrote:
> 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 )
> )



-- 
Shawn Wilson
703-517-1201