Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Nicolas Williams]

On Fri, Sep 17, 2010 at 02:02:44PM -0400, Igor Tandetnik wrote:
> Keith Roberts  wrote:
> > I think what I really mean is I want a text column with only
> > one unique value, that is also indexed.
> 
> UNIQUE constraint will do just that.
> 
> > Also ,I guess the INTEGER PRIMARY KEY column is worth
> > keeping in the table, as a matter of course?
> 
> Usually, yes.

SQLite3 always has an integer primary key, either explicit or implied.
So if you have a primary key column that is not an integer primary key,
then effectively what you get is the same as having one column as
integer primary key and another column with a unique constraint.

It doesn't have to be so though.  A SQL RDMBS could easily have tables
which are hash-based instead of b-tree-based, in which case a table with
a primary key column that is not an integer primary key could well not
have an implied integer primary key.  But that's not what SQLite3 does
at this time.

In any case, it's almost always useful to have an integer primary key.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Keith Roberts]

On Fri, 17 Sep 2010, Roger Andersson wrote:

> To: 'General Discussion of SQLite Database' <sqlite-users@sqlite.org>
> From: Roger Andersson <r...@telia.com>
> Subject: Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?
> 
>> I'm getting there now ;)
>>
>> Keith
>
> I don't know if this will be of any help but you can do something like
> UPDATE file_downloads set
> dl_count = dl_count + 1
> where filename = "$dl_file";

Hi Roger.

Yes, that's viable.

I've been writing a PHP script and did the increment in 
that. Either way it's gonna work.

I know you are supposed to put as much of the logic into 
pure SQL code, but as it's only a simple SQL command, so it 
probably doesn't matter in this scenario.

Keith

-
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

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Roger Andersson]

> I'm getting there now ;)
> 
> Keith

I don't know if this will be of any help but you can do something like
UPDATE file_downloads set
dl_count = dl_count + 1
where filename = "$dl_file";

/Roger

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Keith Roberts]

On Fri, 17 Sep 2010, Igor Tandetnik wrote:

> To: sqlite-users@sqlite.org
> From: Igor Tandetnik <itandet...@mvps.org>
> Subject: Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?
> 
> Keith Roberts <ke...@karsites.net> wrote:
>> I think what I really mean is I want a text column with only
>> one unique value, that is also indexed.
>
> UNIQUE constraint will do just that.
>
>> Also ,I guess the INTEGER PRIMARY KEY column is worth
>> keeping in the table, as a matter of course?
>
> Usually, yes.

Thanks Igor.

I'm getting there now ;)

Keith

-
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

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Igor Tandetnik]

Keith Roberts  wrote:
> I think what I really mean is I want a text column with only
> one unique value, that is also indexed.

UNIQUE constraint will do just that.

> Also ,I guess the INTEGER PRIMARY KEY column is worth
> keeping in the table, as a matter of course?

Usually, yes.
-- 
Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Keith Roberts]

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

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Keith Roberts]

On Fri, 17 Sep 2010, Igor Tandetnik wrote:

> To: sqlite-users@sqlite.org
> From: Igor Tandetnik <itandet...@mvps.org>
> Subject: Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?
> 
> Keith Roberts <ke...@karsites.net> wrote:
>> 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.
>
> Not really efficient, but with 200 records, you won't notice.
>
>> I was wondering if it is possible to setup the filename 
>> column as a UNIQUE PRIMARY KEY on a TEXT column.
>
> It's UNIQUE or PRIMARY KEY: they don't go together. Yes, 
> you could do either. There could be only one PRIMARY KEY 
> in a table, and you already have one, so you'll have to 
> choose which one you want. On the other hand, you could 
> have an unlimited number of UNIQUE constraints. -- Igor 
> Tandetnik

Thanks Igor.

I think what I really mean is I want a text column with only 
one unique value, that is also indexed.

Also ,I guess the INTEGER PRIMARY KEY column is worth 
keeping in the table, as a matter of course?

Keith

-
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

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[O'Neill, Owen]

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

Re: [sqlite] UNIQUE PRIMARY KEY on TEXT column?

[Igor Tandetnik]

Keith Roberts  wrote:
> 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.

Not really efficient, but with 200 records, you won't notice.

> I was wondering if it is possible to setup the filename
> column as a UNIQUE PRIMARY KEY on a TEXT column.

It's UNIQUE or PRIMARY KEY: they don't go together. Yes, you could do either. 
There could be only one PRIMARY KEY in a table, and you already have one, so 
you'll have to choose which one you want. On the other hand, you could have an 
unlimited number of UNIQUE constraints.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users