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.
Cheers
Owen
-----Original Message-----
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Keith Roberts
Sent: Friday, September 17, 2010 12:41 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] UNIQUE PRIMARY KEY on TEXT column?
Hi all.
I have the following table to keep a count of the number of
times files are downloaded from my website:
CREATE TABLE "file_downloads"
(
"record_id" INTEGER PRIMARY KEY,
"filename" char(60),
"dl_count" INTEGER
/* PRIMARY KEY ("record_id") */
);
The select statement to get the current download count for a
file is:
SELECT *
from file_downloads
where filename = "$dl_file";
And the update statement to update the dl_count column is:
UPDATE file_downloads set
dl_count = "$new_count"
where filename = "$dl_file";
Would this be an efficient way of doing things?
I don't expect to be keeping track of more than 200 files
for downloading.
I was wondering if it is possible to setup the filename
column as a UNIQUE PRIMARY KEY on a TEXT column. Is that
possible? I don't need FTS capabilities, just want to make
the filename column a UNIQUE column, so there is only one
row with that particular filename.
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
