On Fri, 17 Sep 2010, O'Neill, Owen wrote:
> To: General Discussion of SQLite Database <[email protected]>
> From: "O'Neill, Owen" <[email protected]>
> Subject: Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?
>
>
> Hi Keith,
>
> Depending on your definition of 'efficient' then this might do what you
> want. (untested - might need to add or remove bracket since sqlite does
> seem fussy about having what it considers to be extra ones)
>
> INSERT OR REPLACE INTO "file_downloads" SELECT
> COALESCE( (SELECT record_id from "file_downloads" where where
> filename = "$dl_file"),(SELECT MAX( record_id) FROM
> "file_downloads")+1),
> $dl_file,
> COALESE( (select "dl_count" from "file_downloads" where where
> filename = "$dl_file"),0)+1;
>
> In summary - we get the record ID for the file in question - if it does
> not exist then we get the max of the current ones and add 1
> Then we get either the current download count for the file - or a zero
> and add 1 to it.
> So the whole thing is atomic and you run it once for each download that
> happens so can run it across multiple instances / threads etc.
Thanks Owen.
I don't think there will be a problem with the row for the
particular filename not existing. I will run an insert
statement from the sqlite3 command line to insert/create the
neccessary row.
Using the UNIQUE constraint will ensure there is only one
row for that particular filename in the database. I only
want one counter per filename, otherwise things could get
messy!
It appears that the UNIQUE constraint also creates an INDEX
on that text column - which is just what I want.
So I'm now using:
CREATE TABLE "file_downloads"
(
"record_id" INTEGER PRIMARY KEY,
"filename" TEXT UNIQUE,
"dl_count" INTEGER
/* PRIMARY KEY ("record_id") */
);
And this is what I have on the SQLite3 command line:
sqlite> insert into file_downloads
...> (filename, dl_count)
...> VALUES ('KickstartUserGuide.pdf', '0');
sqlite> insert into file_downloads
...> (filename, dl_count)
...> VALUES ('KickstartUserGuide.pdf', '0');
Error: constraint failed
So the UNIQUE constraint is working nicely.
sqlite> insert into file_downloads
...> (filename, dl_count)
...> VALUES ('KickstartUserGuide.dvi', '0');
sqlite>
sqlite> select * from file_downloads;
record_id = 1
filename = KickstartUserGuide.pdf
dl_count = 0
record_id = 2
filename = KickstartUserGuide.dvi
dl_count = 0
sqlite>
That should do fine for my purposes. The code to increment
the dl_count column is done in PHP, when the SQL command is
prepared.
Thanks for all your help.
Kind Regards,
Keith Roberts
-----------------------------------------------------------------
Websites:
http://www.karsites.net
http://www.php-debuggers.net
http://www.raised-from-the-dead.org.uk
All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-----------------------------------------------------------------
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
