Alexey Pechnikov wrote:
>
> I have same database on host A and host B. On _both_ hosts I can
> insert/update/delete/select rows and periodically synchronize databases.
>
Oh. That's a different issue that you didn't mention before. It
definitely makes things more complicated.
> CREATE TABLE photos
> (
> name TEXT NOT NULL,
> filename TEXT NOT NULL,
> hostname TEXT NOT NULL DEFAULT 'A',
> save_date REAL COLLATE BINARY,
> update_date REAL COLLATE BINARY,
> delete_date REAL COLLATE BINARY
> );
>
> CREATE TRIGGER photos_delete before delete on photos begin
> update photos set delete_date = julianday('now') where rowid=old.rowid and
> delete_date IS NULL;
> select RAISE (IGNORE);
> end;
> CREATE TRIGGER photos_insert after insert on photos begin
> update photos set save_date = coalesce(save_date, julianday('now')) where
> rowid=new.rowid;
> end;
> CREATE TRIGGER photos_update after update on photos begin
> update photos set update_date = julianday('now') where rowid=old.rowid;
> end;
>
> For replication I can select all rows updated by selected period and send it
> to other hosts.
>
> ROWID can duplicate on host A and host B for different rows. But the chances
> of timestamp unique are good. So, I select item by save_date form host A or
> host B equally.
>
Yes, the internal rowid's from one database are meaningless in another
(much like pointers on one machine being meaningless on another). You
can use an explicit integer primary key id column, but then you would
need to use different id ranges on the different hosts in order to avoid
collisions when the records are copied.
It seems to me that you are still quite likely to get multiple records
with the same timestamp though. If you have lots of activity, eventually
both hosts will have and event occur at the same time.
One thing you may want to try is using the 64 bit random numbers
produced by random() as ids. The chances of any two out of even millions
of random 64 bit numbers colliding is very very small. Just ensure your
random number generators are seeded with different values (perhaps use
the host's IP address) so they generate different sequences.
I'm not sure if you even need unique id numbers for these records, or
why you are concerned that there might be multiple records with the same
save_date for that matter? Is this table linked to any others?
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
