On 13 Sep 2011 at 13:04, Simon Slavin <slav...@bigfraud.org> wrote:
> On 13 Sep 2011, at 12:13pm, Tim Streater wrote:
>
>> SQLite error: general code: HY000 error: 17, database schema has changed
>>
>> and since all the rest of the time the code works fine I'm having some
>> trouble pinning down whether it's my bug (more likely) and if so where, or an
>> SQLite bug (less likely).
>
> The schema of a database is not the data in it -- values of fields, new rows
> inserted, etc. -- but things like what columns there are in a database.
Yeah, I know.
>> What I have is two databases (each is a mailbox) and I wish, from time to
>> time, to move a row from one database to the other (the schemas are the same
>> - but see below). Each row has a unique id, (defined as: absid integer
>> PRIMARY KEY - but see below) and when the row is moved, I want to allocate a
>> new absid value to the row in its new location. I have to do this to avoid
>> clashing with absid values for already-existing rows.
>
> I don't see an easy way of solving conflicting absids.
The hard way of solving this is to select all the fields of the messages table
explicitly (except absid), so I can then insert them into a new row in the
destination table. But I'm trying to avoid this as a maintenance headache (I
may wish to change the schema for messages from time to time).
> Whatever strategy you
> use, there's still a chance you'll get a crash. And you have the problem of
> each copy of the database showing different absid numbers for the same data.
This is not a problem.
> I'd like to suggest an alternative strategy:
>
> Create a new column which indicates which database the row originated in. For
> each of those two databases, when you create a new row, set the right value
> for this 'origin' column. Make your primary key not just 'absid' but
> '(origin,absid)'.
>
> You can still define 'absid' as 'INTEGER AUTOINCREMENT', but not 'INTEGER
> PRIMARY KEY'. Something like
>
> CREATE TABLE myTable (orig TEXT, absid INTEGER AUTOINCREMENT, a, b, c, d,
> PRIMARY KEY (orig, absid))
In general the row could get moved around any number of databases and then back
to the original one.
>> I'm using the PDO interface in PHP and what I do is ($dbh is a handle to the
>> source database):
>>
>> $dbh->query ("attach database ':memory:' as mem");
>> $dbh->query ($create_messages); // Create the messages table in the
>> memory database
>> $dbh->query ("attach database '" . $mailbox . "' as dst");
>> $dbh->query ("insert into mem.messages select * from main.messages where
>> absid='$absid'"); // *** The failing statement ***
>> $dbh->query ("update mem.messages set absid=null");
>> $dbh->query ("insert into dst.messages select * from mem.messages");
>> $absid = $dbh->lastInsertId ();
>>
>> The only way I could find to do what I need regarding a new absid value is,
>> as above, to copy the row to a memory database, set its absid to null, and
>> then copy to the destination database. Even to do this I've had to define
>> absid in the memory database as "absid integer" rather than "absid integer
>> PRIMARY KEY". Is this the cause of the error message? If so, why does it work
>> 99.9% of the time?
>
> Two records with the same absid will violate your PRIMARY KEY contraint,
> because they break the UNIQUE requirement. Perhaps the time they fail is when
> by coincidence both databases generate entries with the same absid.
I don't see how this can have an impact. The row is copied to an intermediate
database (the memory one). Its absid is then set to null (in the memory
database), so that when the second insert is done, the destination database can
choose a new absid value.
Here's a simple question. If I do this:
insert into dst.messages select * from src.messages
do the databases as represented by src and dst have to have the same schema, or
merely the same number of columns?
Meanwhile I think I'll refresh my understanding of INTEGER PRIMARY KEY and
INTEGER AUTOINCREMENT.
--
Cheers -- Tim
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
