Re: [sqlite] starting INTEGER PRIMARY KEY at 0

[David Westbrook]

On Mon, Mar 16, 2009 at 10:08 AM, P Kishor  wrote:
> On Mon, Mar 16, 2009 at 8:59 AM, John Machin  wrote:
>> On 17/03/2009 12:33 AM, P Kishor wrote:
>>> On Mon, Mar 16, 2009 at 8:31 AM, P Kishor  wrote:
 is there a way to have a table start the INTEGER PRIMARY KEY sequence
 at 0 (or some other arbitrary number)?

Looks like you can set it to an arbitrary number (sort of) ... this
will result in id's of 10,11,12:

  CREATE TABLE test1(a INTeger primary key autoincrement, b TEXT);
  select * from SQLITE_SEQUENCE;
  insert into SQLITE_SEQUENCE (name, seq) VALUES ('test1', 9);
  select * from SQLITE_SEQUENCE;
  INSERT INTO test1(b) VALUES('hello A');
  INSERT INTO test1(b) VALUES('hello B');
  INSERT INTO test1(b) VALUES('hello C');
  select * from SQLITE_SEQUENCE;
  select * from test1;

But if you try to set the seq to 0 or -1 or -2, you always get 1,2,3
for the ids.


> dunno... only DRH can tell, but it just may be convention. My modeling
> program uses 0 as the first index, and arrays in C and Perl start at 0
> as well.

For perl (i won't speak to the c side), i'd say that the 0-based
arrays isn't a sufficient reason on it's own, for two reasons:
  1)  it's nice to be able to use perl "truth" and just say:
  die "missing row" unless $pk;
  and not have to worry about undef vs 0 (or '')
  2) You probably can (should?? there are of course exceptions) code
it w/o needing the actual array index...
my $rows = $dbh->selectall_arrayref('select * from mytable', {Slice=>{}})
 foreach my $row (@$rows){
 warn $row->{pkCol};
 warn $row->{colA};
 }
  (And there's also Class::DBI, or DBIx::Class, or any of the others
that nicely hide all the sql)

And a 3rd general reason (should apply to both C & Perl) -- you don't
want to be doing stuff like $myArr[ $pk ] = $row;   if the pk's are
big and there are gaps, e.g. over time if rows have been deleted.  Say
rows 20 -> 500 get delete'd. to store 501 you've extended the array
w/a bunch of empty/wasted elements.

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

Re: [sqlite] starting INTEGER PRIMARY KEY at 0

[Dennis Cote]

P Kishor wrote:
>
> compatibility. And, as 'they' say, 0 is a perfectly fine number. Why
> let it go waste.
>
>   
Real people always start counting from 1.

Only programmers (and the occasional hardware engineer) start counting 
from 0. We see it so often it starts to seem normal, but it really is 
strange to the vast majority of the worlds population. :-)

Dennis Cote




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

Re: [sqlite] starting INTEGER PRIMARY KEY at 0

[P Kishor]

On Mon, Mar 16, 2009 at 8:59 AM, John Machin  wrote:
> On 17/03/2009 12:33 AM, P Kishor wrote:
>> On Mon, Mar 16, 2009 at 8:31 AM, P Kishor  wrote:
>>> is there a way to have a table start the INTEGER PRIMARY KEY sequence
>>> at 0 (or some other arbitrary number)?
>>>
>>> --
>>> Puneet Kishor
>>>
>>
>> I should have added.. yes, I can do the following
>>
>> CREATE TABLE foo (a INTEGER PRIMARY KEY, b TEXT);
>> INSERT INTO foo VALUES (0, 'blah');
>>
>> but, I want to do
>>
>> INSERT INTO foo (b) VALUES ('blah');
>>
>> additionally, are their any gotchas with forcing INTEGER PRIMARY KEY
>> (hence, the ROWID) to start from 0?
>>
>>
> Hi Puneet,
>
> Have you read this: http://www.sqlite.org/autoinc.html ? The first part
> appears to cover non-autoincrement as a background to explaining the
> subtle differences with auto increment.

right... read that.

>
> It appears that you will need to write the first ROWID explicitly.
> There may be a gotcha with zero, otherwise why pick 1 for the default?

dunno... only DRH can tell, but it just may be convention. My modeling
program uses 0 as the first index, and arrays in C and Perl start at 0
as well. But SQLite implements 'stuff' starting at base 1. For
example, consider

sqlite> SELECT substr('blah', 1, 1);
b
sqlite> SELECT substr('blah', 0, 1);

sqlite>






> What are you trying to achieve? If you are going to let the software
> choose your PK for you, why do you care what the starting value is?

compatibility. And, as 'they' say, 0 is a perfectly fine number. Why
let it go waste.


>
> Cheers,
> John





-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] starting INTEGER PRIMARY KEY at 0

[John Machin]

On 17/03/2009 12:33 AM, P Kishor wrote:
> On Mon, Mar 16, 2009 at 8:31 AM, P Kishor  wrote:
>> is there a way to have a table start the INTEGER PRIMARY KEY sequence
>> at 0 (or some other arbitrary number)?
>>
>> --
>> Puneet Kishor
>>
> 
> I should have added.. yes, I can do the following
> 
> CREATE TABLE foo (a INTEGER PRIMARY KEY, b TEXT);
> INSERT INTO foo VALUES (0, 'blah');
> 
> but, I want to do
> 
> INSERT INTO foo (b) VALUES ('blah');
> 
> additionally, are their any gotchas with forcing INTEGER PRIMARY KEY
> (hence, the ROWID) to start from 0?
> 
> 
Hi Puneet,

Have you read this: http://www.sqlite.org/autoinc.html ? The first part 
appears to cover non-autoincrement as a background to explaining the 
subtle differences with auto increment.

It appears that you will need to write the first ROWID explicitly.
There may be a gotcha with zero, otherwise why pick 1 for the default?
What are you trying to achieve? If you are going to let the software 
choose your PK for you, why do you care what the starting value is?

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

Re: [sqlite] starting INTEGER PRIMARY KEY at 0

[P Kishor]

On Mon, Mar 16, 2009 at 8:31 AM, P Kishor  wrote:
> is there a way to have a table start the INTEGER PRIMARY KEY sequence
> at 0 (or some other arbitrary number)?
>
> --
> Puneet Kishor
>

I should have added.. yes, I can do the following

CREATE TABLE foo (a INTEGER PRIMARY KEY, b TEXT);
INSERT INTO foo VALUES (0, 'blah');

but, I want to do

INSERT INTO foo (b) VALUES ('blah');

additionally, are their any gotchas with forcing INTEGER PRIMARY KEY
(hence, the ROWID) to start from 0?


-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] starting INTEGER PRIMARY KEY at 0

[P Kishor]

is there a way to have a table start the INTEGER PRIMARY KEY sequence
at 0 (or some other arbitrary number)?

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