<disclaimer>
Just a stab in the dark suggestion. Not fully tested (tried simple noddy
test just now.
</disclaimer>
How about a pre-insert trigger, which will delete entries in Playlist with
the same key and which have timestamps less than the new row. Than, you
can have a constraint that if the primary key is duplicated, simply drop
the new row.
Cases:
1. No existing row.
- Trigger will delete nothing.
- New row will be inserted.
2. Existing older row.
- Trigger will delete existing row (timestamp predates our new row.)
- New row will be inserted.
3. Existing newer row.
- Trigger will delete nothing.
- New row will be discarded as it clashes with existing primary key.
So:
-- Create table with ignore constraint on primary key conflict
CREATE TABLE Playlist
(
PlaylistID integer PRIMARY KEY ON CONFLICT IGNORE,
ComputerID integer,
NetworkID integer,
PlaylistNum integer,
PlaylistItems text,
LastUpdated text
);
-- Create new trigger to delete older row
CREATE TRIGGER Playlist_InsertCheck
BEFORE INSERT ON Playlist
FOR EACH ROW
BEGIN
DELETE FROM Playlist
WHERE PlaylistID = NEW.PlaylistID
AND LastUpdated < NEW.LastUpdated;
END;
-- Index (PlaylistID,LastUpdated) to speed to DELETE in trigger
-- CREATE INDEX Playlist_IDTimeIdx ON Playlist(PlaylistID,LastUpdated);
Quick test:
sqlite> insert into playlist(PlaylistID,LastUpdated,PlaylistItems) values (0,0,0);
sqlite> select * from playlist;
0||||0|0
sqlite> insert into playlist(PlaylistID,LastUpdated,PlaylistItems) values (0,0,1);
sqlite> select * from playlist;
0||||0|0
sqlite> insert into playlist(PlaylistID,LastUpdated,PlaylistItems) values (0,1,1);
sqlite> select * from playlist;
0||||1|1
sqlite> insert into playlist(PlaylistID,LastUpdated,PlaylistItems) values (0,0,1);
sqlite> select * from playlist;
0||||1|1
sqlite>
Caveats:
Assume LastUpdated comparison will be accurate. Not sure how dates are
compared on SQLite (check) and make sure the clocks are roughly in sync.
On Mon, 14 Jun 2004, Richard Boehme wrote:
>In my database, I am going to be inserting many rows at a time. There
>might be duplicate rows in the database which I would not know the
>primary key for. There are several databases on several computers
>merging into one database which the first ones only occasionally
>contact. The schema so far is this:
>
>CREATE TABLE Playlist
>(
> PlaylistID integer PRIMARY KEY,
> ComputerID integer,
> NetworkID integer,
> PlaylistNum integer,
> PlaylistItems text,
> LastUpdated text
>);
>
>It would need to replace any playlist with the same PlaylistItems and
>NetworkID, for instance. In otherwords, my criterion for if it gets
>updated rather than inserted is if two or three keys match exactly. Is
>there a good way to do this with a lot of transactions? I'm not sure how
>flexible the REPLACE command or triggers would be for this. And as I
>mentioned, it might be on a couple thousnad rows at a time, so I would
>like to have it in a transaction, if possible.
>
>Thanks for the help.
>
>Richard Boehme
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
