On Mon, Mar 16, 2009 at 10:08 AM, P Kishor <punk.k...@gmail.com> wrote:
> On Mon, Mar 16, 2009 at 8:59 AM, John Machin <sjmac...@lexicon.net> wrote:
>> On 17/03/2009 12:33 AM, P Kishor wrote:
>>> On Mon, Mar 16, 2009 at 8:31 AM, P Kishor <punk.k...@gmail.com> 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
