On Fri, 17 Sep 2010, O'Neill, Owen wrote: > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > From: "O'Neill, Owen" <oone...@averyberkel.com> > 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users