Re: [sqlite] format for column names

[Martin Jenkins]

Nikki Locke wrote:
JOOI, were you on CIX? 

Still am.


Thought the name was familiar. I don't get on much these days. :-/

Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] problem with auto increment of ROWID

[Bruce Q. Hammond]

To save someone the trouble, it's about 2924 centuries.  :-)
--BQ


[EMAIL PROTECTED] wrote:

Dixon Hutchinson <[EMAIL PROTECTED]> wrote:
  
If I actually specify AUTOINCREMENT, then an insert will fail when I 
have reached the max row value, even if there are unused rows in the 
table.  So I don't want to specify AUTOINCREMENT.





The maximum rowid is 9223372036854775807.  You're going to take
a long time to reach that value if you start with 1.

Do the math:  If you do 1 million inserts per second, 24 hours
per day, 365 days per year, how many centuries does it take you
to run out of rowids?

--
D. Richard Hipp   <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-


  



--

*Bruce Q. Hammond*

Sr. Software Engineer

Extensis

http://www.extensis.com/

phone: 503.274.2020 x228

email: [EMAIL PROTECTED]


Software management solutions for your fonts and creative assets


/To learn more about Extensis Solutions, visit:/

/http://www.extensis.com/en/solutions/index.jsp//  /


Notice:  This communication may contain privileged or other confidential 
information.  If you are not the intended recipient, or believe that you 
have received this communication in error, please do not print, copy, 
retransmit, disseminate, or otherwise use the information.  Also, please 
indicate to the sender that you have received this email in error, and 
delete the copy you received.  Thank you.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Sqlite 3.3.7 intel compiler warnings

[Miguel Angel Latorre Díaz]

I know.
I checked them all and at least they are dangerous and probably not obvious.
I think the one in os_win.c is really a bug unless the intention were to 
always return OK. 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Sqlite 3.3.7 intel compiler warnings

[Jay Sprenkle]

hidden variables might very well not be a problem, if it was done that
way on purpose.

On 8/30/06, Miguel Angel Latorre Díaz <[EMAIL PROTECTED]> wrote:

I already filtered out all the "common" warnings but these:

build.c(1969): remark #1599: declaration hides variable "v" (declared at
line 1883)
Vdbe *v = sqlite3GetVdbe(pParse);
  ^



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Sqlite 3.3.7 intel compiler warnings

[Miguel Angel Latorre Díaz]

I already filtered out all the "common" warnings but these:

build.c(1969): remark #1599: declaration hides variable "v" (declared at 
line 1883)

   Vdbe *v = sqlite3GetVdbe(pParse);
 ^

expr.c(1520): remark #1599: declaration hides variable "op" (declared at 
line 1489)

 int op = (pTab && IsVirtual(pTab)) ? OP_VColumn : OP_Column;
 ^

expr.c(1530): remark #1599: declaration hides variable "op" (declared at 
line 1489)

 int op = (pTab && IsVirtual(pTab)) ? OP_VRowid : OP_Rowid;
 ^
I think this one is a bug:
os_win.c(840): remark #1599: declaration hides variable "rc" (declared at 
line 838)

 int rc, cnt = 0;
 ^

vtab.c(326): remark #1599: declaration hides variable "zModule" (declared at 
line 316)

 const char *zModule = pTab->azModuleArg[0];
 ^

where.c(1043): remark #1599: declaration hides variable "pTerm" (declared at 
line 1032)

 WhereTerm *pTerm;
^

where.c(2040): remark #1599: declaration hides variable "j" (declared at 
line 2002)

   int j;
   ^



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] format for column names

[Nikki Locke]

Martin Jenkins wrote:
> Nikki Locke wrote: 
> > Martin Jenkins wrote: 
> > Using [] is a Microsoft thing. More portable to use double quotes... 
>  
> Thanks, I didn't know that. I avoid spaces in column names so I haven't 
> actually use it in anger. 
>  
> JOOI, were you on CIX? 

Still am.

-- 
Nikki Locke, Trumphurst Ltd.  PC & Unix consultancy & programming
http://www.trumphurst.com/



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Performance Question

[Rob Sciuk]

On Wed, 30 Aug 2006 [EMAIL PROTECTED] wrote:

> > I have to go along with Mario, here.  This is a potential show stopper,
>
> Show stopper?  Really?  The bug has been there for years, literally,
> and nobody has even noticed it until now - despite thousands of users
> and millions and millions of deployments.
>
> There is a really simple work-around: Just add NOT NULL to your
> PRIMARY KEY column declaration...

Point taken.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using Between in SQL

[Gerry Snyder]

Roger wrote:


But is this a Code design issue because Between A and D is supposed to
be inclusive.


It is. Any Surname which consists of the single character "D" will be 
included. Which is just what you told sqlite to check for.


You need to check for the first char of the name being between A and D, 
rather than the complete Surname being within those limits, or set the 
upper limit high enough. See the example below.


HTH,

Gerry
-

$ sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> create table a(a);
sqlite> insert into a values("A");
sqlite> insert into a values("D");
sqlite> insert into a values("Da");
sqlite> select * from A where a between 'A%' and 'D%';
D
sqlite> select * from A where a between 'A' and 'D%';
A
D
sqlite> select * from A where a between 'A' and 'D';
A
D
sqlite> select * from A where a between 'A' and 'Dzzz';
A
D
Da


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Performance Question

[drh]

Rob Sciuk <[EMAIL PROTECTED]> wrote:
> On Wed, 30 Aug 2006, Mario Frasca wrote:
> > On 2006-0829 13:15:02, [EMAIL PROTECTED] wrote:
> >
> > >> To my surprise (perhaps "horror") I find that SQLite has
> > >> for a very long time allowed NULL values in PRIMARY KEY
> > >> columns.  [...]
> >
> > I understand your concern about legacy programs, but most of us expect
> > PRIMARY KEY to imply NOT NULL...  don't we?  what about looking for
> > alternative good solutions?  we could put the correction code in the
> > source, conditionally compiled (not the default) and with the next major
> > release reverse the condition (new 'corrected' source becomes default
> > and old 'legacy' behaviour still available if desired) ... ?
> >
>
> I have to go along with Mario, here.  This is a potential show stopper,

Show stopper?  Really?  The bug has been there for years, literally,
and nobody has even noticed it until now - despite thousands of users 
and millions and millions of deployments.

There is a really simple work-around: Just add NOT NULL to your
PRIMARY KEY column declaration...
--
D. Richard Hipp   <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] An estimate on how many users?

[JP]

Has anybody tried to estimate how many end-users are using sqlite?  This 
would include actual firefox users and users of any other product that 
uses sqlite.


Somebody asked me, "how many people are using sqlite" (or sqlite based 
products)?


jp.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Performance Question

[Rob Sciuk]

On Wed, 30 Aug 2006, Mario Frasca wrote:
> On 2006-0829 13:15:02, [EMAIL PROTECTED] wrote:
>
> >> To my surprise (perhaps "horror") I find that SQLite has
> >> for a very long time allowed NULL values in PRIMARY KEY
> >> columns.  [...]
>
> I understand your concern about legacy programs, but most of us expect
> PRIMARY KEY to imply NOT NULL...  don't we?  what about looking for
> alternative good solutions?  we could put the correction code in the
> source, conditionally compiled (not the default) and with the next major
> release reverse the condition (new 'corrected' source becomes default
> and old 'legacy' behaviour still available if desired) ... ?
>
> maybe 'the best of both worlds', hope you agree.
>
> regards,
> Mario


I have to go along with Mario, here.  This is a potential show stopper,
and I would grab a "fixed" version of SQLite ASAP were it made available.
Alternatively, one might simply use an ifdef to restore the old (legacy)
behaviour, something like:

#ifdef WEIRD_AND_UNEXPECTED_BEHAVIOURS_DESIRED
...
#endif

I'd suggest that this is one for the regression suite as well.


Rob Sciuk

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] problem with auto increment of ROWID

[Martin Jenkins]

Dixon Hutchinson wrote:

But I need ROWID to auto increment until the largest 64-bit integer
value is used


You'll never get there - 2^64 is huge. 2^31 seconds is about 68 years so 
even if you're getting billions of inserts/sec you'll be dead before the 
rowid wraps.


Martin




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] problem with auto increment of ROWID

[davep]

> 9,223,372,036,854,775,807 / (1,000,000 * 60seconds * 60minutes * 24hours *
> 365days) = 292471 years
>
> On 8/30/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>>
>> The maximum rowid is 9223372036854775807.  You're going to take
>> a long time to reach that value if you start with 1.
>>
>> Do the math:  If you do 1 million inserts per second, 24 hours
>> per day, 365 days per year, how many centuries does it take you
>> to run out of rowids?
>>
>

We only need two bytes to store year numbers Chuck.
We only need a 32bit unsigned integer to store seconds since the Epoc.
We only need

Eventually computers will mature to a point where a data intensive
application can store at a rate much faster than a million inserts per
second using sqlite. ;-)

( I hope )

D.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] problem with auto increment of ROWID

[Clay Dowling]

It's great to design your application to be future proof and all, but I
think Dr. Hipp has a point: that failure point probably isn't in your
lifetime, or the lifetime of 32 bit computing.

Do you have a particular reason for needing this behavior, other than your
own desires?  If choosing non-sequential rowid values is fine for some
point down the road in the unforseeable future, why isn't it fine for now?

Clay Dowling

Dixon Hutchinson said:
> But "AUTOINCREMENT" has slightly different behavior that what I desire.
> The difference is what happens when the ROWID reaches the "largest
> possible integer".  If AUTOINCREMENT is specified, then the next insert
> after "largest possible integer" is reach will fail, regardless of the
> availability of empty rows that resulted from deletes.  The behavior I
> desire is that after "largest possible integer" is reach, "/the database
> engine starts picking candidate ROWIDs at random until it finds one that
> is not previously used/".
>
> Mario Frasca wrote:
>> Dixon Hutchinson wrote:
>>
>>>H:\b>sqlite3.exe t.dat
>>>SQLite version 3.3.7
>>>Enter ".help" for instructions
>>>sqlite> CREATE TABLE abc
>>>   ...> (
>>>   ...> c TEXT,
>>>   ...> p INTEGER,
>>>   ...> t TEXT,
>>>   ...> masked INTEGER PRIMARY KEY,
>>>   ...> UNIQUE(p,c)
>>>   ...> );
>>> [...]
>>>
>>> Notice I still have elements 1,2 and 3 in the end where I want to
>>> have elements 1, 2 and 4.
>>
>> which is the reason why sqlite has autoincrement...
>>
>> [EMAIL PROTECTED]:~$ /usr/bin/sqlite3
>> SQLite version 3.3.4
>> Enter ".help" for instructions
>> sqlite> CREATE TABLE abc ( c  TEXT,p INTEGER, t TEXT, masked INTEGER
>> PRIMARY KEY AUTOINCREMENT, UNIQUE(p,c));
>> sqlite> INSERT INTO abc(c,p,t) VALUES('t1', 24, 't2');
>> sqlite> INSERT INTO abc(c,p,t) VALUES('t3', 25, 't4');
>> sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't6');
>> sqlite> SELECT * FROM abc;
>> t1|24|t2|1
>> t3|25|t4|2
>> t5|26|t6|3
>> sqlite> DELETE FROM abc WHERE ROWID='3';
>> sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't8');
>> sqlite> SELECT * FROM abc;
>> t1|24|t2|1
>> t3|25|t4|2
>> t5|26|t8|4
>> sqlite>
>>
>> works also if you write "rowid" instead of "masked"
>>
>> -
>>
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -
>>
>


-- 
Simple Content Management
http://www.ceamus.com


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] problem with auto increment of ROWID

[Dixon Hutchinson]

I did the math once,  came to the same conclusion, then somewhere in the 
last moths, forgot it and slipped into 32-bit mode for some reason :-[


Specifying AUTOINCREMENT should work fine for what I need.

[EMAIL PROTECTED] wrote:

Dixon Hutchinson <[EMAIL PROTECTED]> wrote:
  
If I actually specify AUTOINCREMENT, then an insert will fail when I 
have reached the max row value, even if there are unused rows in the 
table.  So I don't want to specify AUTOINCREMENT.





The maximum rowid is 9223372036854775807.  You're going to take
a long time to reach that value if you start with 1.

Do the math:  If you do 1 million inserts per second, 24 hours
per day, 365 days per year, how many centuries does it take you
to run out of rowids?

--
D. Richard Hipp   <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

  

Re: [sqlite] problem with auto increment of ROWID

[Joel Lucsy]

9,223,372,036,854,775,807 / (1,000,000 * 60seconds * 60minutes * 24hours *
365days) = 292471 years

On 8/30/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


The maximum rowid is 9223372036854775807.  You're going to take
a long time to reach that value if you start with 1.

Do the math:  If you do 1 million inserts per second, 24 hours
per day, 365 days per year, how many centuries does it take you
to run out of rowids?



--
Joel Lucsy
"The dinosaurs became extinct because they didn't have a space program." --
Larry Niven

Re: [sqlite] problem with auto increment of ROWID

[Dixon Hutchinson]

Brandon,
I think you tickled the right neurons.  I misread the SQLite web page.  
Below is the statement from the web page.  The italics is how I 
mis-interpreted it.


   If no ROWID is specified on the insert, an appropriate ROWID is
   created automatically. The usual algorithm is to give the newly
   created row a ROWID that is one larger than the largest ROWID in the
   table prior to the insert.

   /If no ROWID is specified on the insert, an appropriate ROWID is
   created automatically. The usual algorithm is to give the newly
   created row a ROWID that is one larger than the largest ROWID *ever
   inserted in the table*./

Thanks Brandon.


Brandon, Nicholas (UK) wrote:
  

I have not reach a point of creating a "maximum possible ROWID",  I'm


only at three rows.  The behavior I was desiring is:
  

   If no ROWID is specified on the insert, an appropriate ROWID is
   created automatically. The usual algorithm is to give the newly
   created row a ROWID that is one larger than the largest ROWID in


the
  

   table prior to the insert.



Based on your previous example its doing exactly the behaviour
described.

Try

DELETE FROM abc WHERE ROWID='2';
INSERT INTO abc(c,p,t) VALUES('t5', 26, 't8');
SELECT * FROM abc;

I would expect 'masked' to be 1,3,4


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

  

Re: [sqlite] problem with auto increment of ROWID

[Mario Frasca]

Dixon Hutchinson wrote:

I have not reach a point of creating a "maximum possible ROWID",  I'm 
only at three rows.  


it's a matter of definition...  if you just reached three rows, without 
deletions, then the maximum rowid is 3.


If I actually specify AUTOINCREMENT, then an insert will fail when I 
have reached the max row value, even if there are unused rows in the 
table.  


true.  in a computer you have a limited amount of numbers.  sooner or 
later you use them up.  if you don't specify AUTOINCREMENT, then an 
insert will fail when the table is full (not a hole to be found!).  if 
you do specify AUTOINCREMENT, then an insert will fail when there isn't 
any historically unique rowid available.



So I don't want to specify AUTOINCREMENT.


which, translated into English, reads: "so I want to reuse rowids".

are you even more confused than before?  hope not.

Mario.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] problem with auto increment of ROWID

[Dixon Hutchinson]

But "AUTOINCREMENT" has slightly different behavior that what I desire.  
The difference is what happens when the ROWID reaches the "largest 
possible integer".  If AUTOINCREMENT is specified, then the next insert 
after "largest possible integer" is reach will fail, regardless of the 
availability of empty rows that resulted from deletes.  The behavior I 
desire is that after "largest possible integer" is reach, "/the database 
engine starts picking candidate ROWIDs at random until it finds one that 
is not previously used/".


Mario Frasca wrote:

Dixon Hutchinson wrote:


   H:\b>sqlite3.exe t.dat
   SQLite version 3.3.7
   Enter ".help" for instructions
   sqlite> CREATE TABLE abc
  ...> (
  ...> c TEXT,
  ...> p INTEGER,
  ...> t TEXT,
  ...> masked INTEGER PRIMARY KEY,
  ...> UNIQUE(p,c)
  ...> );
[...]

Notice I still have elements 1,2 and 3 in the end where I want to 
have elements 1, 2 and 4.


which is the reason why sqlite has autoincrement...

[EMAIL PROTECTED]:~$ /usr/bin/sqlite3
SQLite version 3.3.4
Enter ".help" for instructions
sqlite> CREATE TABLE abc ( c  TEXT,p INTEGER, t TEXT, masked INTEGER 
PRIMARY KEY AUTOINCREMENT, UNIQUE(p,c));

sqlite> INSERT INTO abc(c,p,t) VALUES('t1', 24, 't2');
sqlite> INSERT INTO abc(c,p,t) VALUES('t3', 25, 't4');
sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't6');
sqlite> SELECT * FROM abc;
t1|24|t2|1
t3|25|t4|2
t5|26|t6|3
sqlite> DELETE FROM abc WHERE ROWID='3';
sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't8');
sqlite> SELECT * FROM abc;
t1|24|t2|1
t3|25|t4|2
t5|26|t8|4
sqlite>

works also if you write "rowid" instead of "masked"

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 

Re: [sqlite] problem with auto increment of ROWID

[drh]

Dixon Hutchinson <[EMAIL PROTECTED]> wrote:
> 
> If I actually specify AUTOINCREMENT, then an insert will fail when I 
> have reached the max row value, even if there are unused rows in the 
> table.  So I don't want to specify AUTOINCREMENT.
> 

The maximum rowid is 9223372036854775807.  You're going to take
a long time to reach that value if you start with 1.

Do the math:  If you do 1 million inserts per second, 24 hours
per day, 365 days per year, how many centuries does it take you
to run out of rowids?

--
D. Richard Hipp   <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] problem with auto increment of ROWID

[Brandon, Nicholas \(UK\)]

>I have not reach a point of creating a "maximum possible ROWID",  I'm
only at three rows.  The behavior I was desiring is:
>
>If no ROWID is specified on the insert, an appropriate ROWID is
>created automatically. The usual algorithm is to give the newly
>created row a ROWID that is one larger than the largest ROWID in
the
>table prior to the insert.

Based on your previous example its doing exactly the behaviour
described.

Try

DELETE FROM abc WHERE ROWID='2';
INSERT INTO abc(c,p,t) VALUES('t5', 26, 't8');
SELECT * FROM abc;

I would expect 'masked' to be 1,3,4


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] problem with auto increment of ROWID

[Mario Frasca]

Dixon Hutchinson wrote:


   H:\b>sqlite3.exe t.dat
   SQLite version 3.3.7
   Enter ".help" for instructions
   sqlite> CREATE TABLE abc
  ...> (
  ...> c TEXT,
  ...> p INTEGER,
  ...> t TEXT,
  ...> masked INTEGER PRIMARY KEY,
  ...> UNIQUE(p,c)
  ...> );
[...]

Notice I still have elements 1,2 and 3 in the end where I want to have 
elements 1, 2 and 4.


which is the reason why sqlite has autoincrement...

[EMAIL PROTECTED]:~$ /usr/bin/sqlite3
SQLite version 3.3.4
Enter ".help" for instructions
sqlite> CREATE TABLE abc ( c  TEXT,p INTEGER, t TEXT, masked INTEGER 
PRIMARY KEY AUTOINCREMENT, UNIQUE(p,c));

sqlite> INSERT INTO abc(c,p,t) VALUES('t1', 24, 't2');
sqlite> INSERT INTO abc(c,p,t) VALUES('t3', 25, 't4');
sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't6');
sqlite> SELECT * FROM abc;
t1|24|t2|1
t3|25|t4|2
t5|26|t6|3
sqlite> DELETE FROM abc WHERE ROWID='3';
sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't8');
sqlite> SELECT * FROM abc;
t1|24|t2|1
t3|25|t4|2
t5|26|t8|4
sqlite>

works also if you write "rowid" instead of "masked"

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: format for column names

[A. Pagaltzis]

* Nikki Locke <[EMAIL PROTECTED]> [2006-08-30 14:40]:
> Using [] is a Microsoft thing. More portable to use double
> quotes...

I’d use the square brackets anyway.

sqlite> create table foo ( "bar baz" text );
sqlite> insert into foo values ( "quux" );

OK…

sqlite> select [bar baz] from foo;
quux

Obviously. Now let’s make a typo:

sqlite> select [baz baz] from foo;
SQL error: no such column: baz baz

Caught it, good.

sqlite> select "bar baz" from foo;
quux

As expected. Now let’s mistype it.

sqlite> select "baz baz" from foo;
baz baz

Oops.

In other words, if yoz like spurious bugs, then feel free to use
double quotes. If you prefer robust code, then you’ll stay away
from them.

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] problem with auto increment of ROWID

[Dixon Hutchinson]

I have not reach a point of creating a "maximum possible ROWID",  I'm 
only at three rows.  The behavior I was desiring is:


   If no ROWID is specified on the insert, an appropriate ROWID is
   created automatically. The usual algorithm is to give the newly
   created row a ROWID that is one larger than the largest ROWID in the
   table prior to the insert.

If I actually specify AUTOINCREMENT, then an insert will fail when I 
have reached the max row value, even if there are unused rows in the 
table.  So I don't want to specify AUTOINCREMENT.


Am I misunderstanding your point?


Mario Frasca wrote:

Dixon Hutchinson wrote:

[...] I was hoping to get the behavior specified at 
http://www.sqlite.org/autoinc.html. where the AUTOINCREMENT keyword 
is not used.  


but you are getting quite exactly what is stated there:
«If you ever delete rows or if you ever create a row with the maximum 
possible ROWID, then ROWIDs from previously deleted rows might be 
reused [...]»


with AUTOINCREMENT you would get:
«The ROWID chosen for the new row is one larger than the largest ROWID 
that has ever before existed in that same table»

where 'larger than' implies 'different from'...

which behaviour do you actually need?

ciao,
Mario

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] problem with auto increment of ROWID

[Dixon Hutchinson]

I thought actually specifying a ROWID would be harmless.  So I tried 
your idea, same results:


   H:\b>sqlite3.exe t.dat
   SQLite version 3.3.7
   Enter ".help" for instructions
   sqlite> CREATE TABLE abc
  ...> (
  ...> c TEXT,
  ...> p INTEGER,
  ...> t TEXT,
  ...> masked INTEGER PRIMARY KEY,
  ...> UNIQUE(p,c)
  ...> );
   sqlite> INSERT INTO abc(c,p,t) VALUES('t1', 24, 't2');
   sqlite> INSERT INTO abc(c,p,t) VALUES('t3', 25, 't4');
   sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't6');
   sqlite> SELECT * FROM abc;
   t1|24|t2|1
   t3|25|t4|2
   t5|26|t6|3
   sqlite> DELETE FROM abc WHERE ROWID='3';
   sqlite> INSERT INTO abc(c,p,t) VALUES('t5', 26, 't8');
   sqlite> SELECT * FROM abc;
   t1|24|t2|1
   t3|25|t4|2
   t5|26|t8|3
   sqlite>


Notice I still have elements 1,2 and 3 in the end where I want to have 
elements 1, 2 and 4.



Clay Dowling wrote:

ROWID is a reserved word.  Each row has one and you don't need to specify
it.  In fact you probably shouldn't, since it seems to be causing you
problems.

If you want the column to be explicitly declared in your table, call it
something else like id.  If you don't want to do that just use the keyword
ROWID in your queries, without declaring it as a column, and everything
wil be fine.

Clay


Dixon Hutchinson said:
  

I am having a problem with default behavior of ROWID not
auto-incrementing.
I use the following to create my table:

CREATE TABLE abc
{
c TEXT,
p INTEGER,
t TEXT,
ROWID INTEGER PRIMARY KEY,
UNIQUE(p,c)
);

When I insert into the table using just c, p and t, I see that ROWID is
assigned auto-incrementing values, as I would expect.  If I delete an
entry (and in my test it was the last entry made), and then reinsert the
same c and p with a different t, I notice that ROWID gets the value of
the row I just deleted.  I was hoping to get the behavior specified at
http://www.sqlite.org/autoinc.html. where the AUTOINCREMENT keyword is
not used.  I have other elements in the table, but I left them out for
this illustration.  I want to be able to do relatively quick lookups of
data by either p, c, or ROWID.  But I need ROWID to auto increment until
the largest 64-bit integer value is used and then follow the random
search algorithm described in the previously mention web page.  I
suspect my problem is that specifying "UNIQUE(p,c)" has obviated the
table's need for a unique ROWID.

I'm looking for a recommendation on how to specify the table such that
ROWID gets the desired behavior and I can still do quick lookups by
ROWID, p, or c.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-






  

Re: [sqlite] problem with auto increment of ROWID

[Mario Frasca]

Dixon Hutchinson wrote:

[...] I was hoping to get the behavior specified at 
http://www.sqlite.org/autoinc.html. where the AUTOINCREMENT keyword is 
not used.  


but you are getting quite exactly what is stated there:
«If you ever delete rows or if you ever create a row with the maximum 
possible ROWID, then ROWIDs from previously deleted rows might be reused 
[...]»


with AUTOINCREMENT you would get:
«The ROWID chosen for the new row is one larger than the largest ROWID 
that has ever before existed in that same table»

where 'larger than' implies 'different from'...

which behaviour do you actually need?

ciao,
Mario

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] problem with auto increment of ROWID

[Clay Dowling]

ROWID is a reserved word.  Each row has one and you don't need to specify
it.  In fact you probably shouldn't, since it seems to be causing you
problems.

If you want the column to be explicitly declared in your table, call it
something else like id.  If you don't want to do that just use the keyword
ROWID in your queries, without declaring it as a column, and everything
wil be fine.

Clay


Dixon Hutchinson said:
> I am having a problem with default behavior of ROWID not
> auto-incrementing.
> I use the following to create my table:
>
> CREATE TABLE abc
> {
> c TEXT,
> p INTEGER,
> t TEXT,
> ROWID INTEGER PRIMARY KEY,
> UNIQUE(p,c)
> );
>
> When I insert into the table using just c, p and t, I see that ROWID is
> assigned auto-incrementing values, as I would expect.  If I delete an
> entry (and in my test it was the last entry made), and then reinsert the
> same c and p with a different t, I notice that ROWID gets the value of
> the row I just deleted.  I was hoping to get the behavior specified at
> http://www.sqlite.org/autoinc.html. where the AUTOINCREMENT keyword is
> not used.  I have other elements in the table, but I left them out for
> this illustration.  I want to be able to do relatively quick lookups of
> data by either p, c, or ROWID.  But I need ROWID to auto increment until
> the largest 64-bit integer value is used and then follow the random
> search algorithm described in the previously mention web page.  I
> suspect my problem is that specifying "UNIQUE(p,c)" has obviated the
> table's need for a unique ROWID.
>
> I'm looking for a recommendation on how to specify the table such that
> ROWID gets the desired behavior and I can still do quick lookups by
> ROWID, p, or c.
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-- 
Simple Content Management
http://www.ceamus.com


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] problem with auto increment of ROWID

[Dixon Hutchinson]

I am having a problem with default behavior of ROWID not auto-incrementing.
I use the following to create my table:

CREATE TABLE abc
{
   c TEXT,
   p INTEGER,
   t TEXT,
   ROWID INTEGER PRIMARY KEY,
   UNIQUE(p,c)
);

When I insert into the table using just c, p and t, I see that ROWID is 
assigned auto-incrementing values, as I would expect.  If I delete an 
entry (and in my test it was the last entry made), and then reinsert the 
same c and p with a different t, I notice that ROWID gets the value of 
the row I just deleted.  I was hoping to get the behavior specified at 
http://www.sqlite.org/autoinc.html. where the AUTOINCREMENT keyword is 
not used.  I have other elements in the table, but I left them out for 
this illustration.  I want to be able to do relatively quick lookups of 
data by either p, c, or ROWID.  But I need ROWID to auto increment until 
the largest 64-bit integer value is used and then follow the random 
search algorithm described in the previously mention web page.  I 
suspect my problem is that specifying "UNIQUE(p,c)" has obviated the 
table's need for a unique ROWID.


I'm looking for a recommendation on how to specify the table such that 
ROWID gets the desired behavior and I can still do quick lookups by 
ROWID, p, or c.





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] format for column names

[Martin Jenkins]

Nikki Locke wrote:

Martin Jenkins wrote:
Using [] is a Microsoft thing. More portable to use double quotes...


Thanks, I didn't know that. I avoid spaces in column names so I haven't 
actually use it in anger.


JOOI, were you on CIX?

Martin


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] format for column names

[Nikki Locke]

Martin Jenkins wrote:
> T&B wrote: 
> > So, are spaces allowed or not? Is there a specification somewhere 
> > that shows what characters, length etc are allowed in column names 
> > (and table names)? 
>  
> It's an SQL thing, not an SQLite thing - if you must use spaces in 
> column names, put the column name in []. Same applies for column names 
> which are reserved words. 

Using [] is a Microsoft thing. More portable to use double quotes...

create table people("Name First", "Name Last");
insert into People values('firstname', 'lastname');
headers on
select * from people;
Name First|Name Last
firstname|lastname
select "Name First" from people;
Name First
firstname

SQLite accepts [], "" or `` to surround column names, presumably because 
Microsoft databases accept [], and MySQL accepts ``. As far as I know, all SQL 
databases accept "".

-- 
Nikki Locke, Trumphurst Ltd.  PC & Unix consultancy & programming
http://www.trumphurst.com/



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using Between in SQL

[Nikki Locke]

Arjen Markus wrote:
> Roger wrote: 
>  
> >I have a question. 
> > 
> >I trying to write a query as follows 
> > 
> >Select * 
> >>From People 
> >Where Surname Between 'A%' and 'E%' 
> >Order by UPPER(Surname); 
> > 
> >Now the problem i have is that i get only the Surnames from A up to D 
> >and the E's are excluded. 

All E's up to and including 'E%' will be included - e.g. 'E ', 'E!', 'E$'.
All A's before 'A%' will be EXcluded - e.g. 'A ', 'A!', 'A$'.

> I am no expert on SQL, but I think it is likely that BETWEEN does not 
> acknowledge the wildcards. 
>  
> Try: Surname between 'A' and 'E' instead (or 'F') 

Or, depending on what characters are allowed, perhaps 'E~' might be better. 
Presumably, if you allow international characters, you would use something with 
an even larger character code than '~'.

-- 
Nikki Locke, Trumphurst Ltd.  PC & Unix consultancy & programming
http://www.trumphurst.com/



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using Between in SQL

[drh]

Roger <[EMAIL PROTECTED]> wrote:
> I have a question.
> 
> I trying to write a query as follows
> 
> Select *
> >From People
> Where Surname Between 'A%' and 'E%'
> Order by UPPER(Surname);
> 
> Now the problem i have is that i get only the Surnames from A up to D
> and the E's are excluded.
> 

What is the '%' about?  BETWEEN does not understand LIKE-style
patterns.  The BETWEEN operator is exactly equivalent to two
comparisons:

x BETWEEN y AND z

is the same as

x>=y AND x<=z

Probably want you want to do is drop the BETWEEN all together
and use two comparisons instead, but make the upperbound a
strict inequality:

WHERE surname>='A' AND surname<'E'

--
D. Richard Hipp   <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using Between in SQL

[Roger]

On Wed, 2006-08-30 at 13:33 +0200, Arjen Markus wrote:
> Roger wrote:
> 
> >On Wed, 2006-08-30 at 12:03 +0100, Martin Jenkins wrote:
> >  
> >
> >>Roger wrote:
> >>
> >>
> >>>I have a question.
> >>>
> >>>I trying to write a query as follows
> >>>
> >>>Select *
> From People
> >>>Where Surname Between 'A%' and 'E%'
> >>>Order by UPPER(Surname);
> >>>
> >>>Now the problem i have is that i get only the Surnames from A up to D
> >>>and the E's are excluded.
> >>>
> >>>Can anyone help me out, i am writing a web based application.
> >>>
> >>>  
> >>>
> >>How about:
> >>
> >>   Select *
> >>   From People
> >>   Where Surname Between 'A%' and 'F%' ' <--- s/E/F/
> >>   Order by UPPER(Surname);
> >>
> >>
> >Thanks for the response guys but then my problem comes when i try and
> >query a range between T and Z i tried to make it query between T to A
> >but got no result. Anyway i will have to add a constraint in my PHP code
> >for T to Z.
> >  
> >
> Well, the character after 'Z' is 'a' in the ASCII table. You could try: 
> BETWEEN 'Z' and 'a'
Oh! Thank you. Thank you. I feel kinda silly because i thought about the
ASCII table. 

It works now thanks.

But is this a Code design issue because Between A and D is supposed to
be inclusive.

> 
> Regards,
> 
> Arjen
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using Between in SQL

[Martin Jenkins]

Arjen Markus wrote:
Well, the character after 'Z' is 'a' in the ASCII table. You could try: 
BETWEEN 'Z' and 'a'


Not quite. It's "XYZ[\]^_`abc".

Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using Between in SQL

[Martin Jenkins]

Roger wrote:

Thanks for the response guys but then my problem comes when i try and
query a range between T and Z i tried to make it query between T to A
but got no result. Anyway i will have to add a constraint in my PHP code
for T to Z.


I saw that coming, but you didn't ask about that bit. ;)

In ASCII the character after Z is '[' not 'A' so

  Select *
  From People
  Where Surname Between 'T%' and '[%'
  Order by UPPER(Surname);

might be good enough for your application, but you'd need to check that 
if you're going to be using other character sets.


Your other approach is to use substr(Surname,1,1). This might be slower 
but the docs say it handles UTF-8 so it's probably the way to go.


  Select *
  From People
  Where substr(Surname,1,1) Between 'T' and 'Z'
  Order by UPPER(Surname);

Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using Between in SQL

[Arjen Markus]

Roger wrote:


On Wed, 2006-08-30 at 12:03 +0100, Martin Jenkins wrote:
 


Roger wrote:
   


I have a question.

I trying to write a query as follows

Select *

From People

Where Surname Between 'A%' and 'E%'
Order by UPPER(Surname);

Now the problem i have is that i get only the Surnames from A up to D
and the E's are excluded.

Can anyone help me out, i am writing a web based application.

 


How about:

  Select *
  From People
  Where Surname Between 'A%' and 'F%' ' <--- s/E/F/
  Order by UPPER(Surname);
   


Thanks for the response guys but then my problem comes when i try and
query a range between T and Z i tried to make it query between T to A
but got no result. Anyway i will have to add a constraint in my PHP code
for T to Z.
 

Well, the character after 'Z' is 'a' in the ASCII table. You could try: 
BETWEEN 'Z' and 'a'


Regards,

Arjen

Re: [sqlite] Using Between in SQL

[Roger]

On Wed, 2006-08-30 at 12:03 +0100, Martin Jenkins wrote:
> Roger wrote:
> > I have a question.
> > 
> > I trying to write a query as follows
> > 
> > Select *
> >>From People
> > Where Surname Between 'A%' and 'E%'
> > Order by UPPER(Surname);
> > 
> > Now the problem i have is that i get only the Surnames from A up to D
> > and the E's are excluded.
> > 
> > Can anyone help me out, i am writing a web based application.
> > 
> 
> How about:
> 
>Select *
>From People
>Where Surname Between 'A%' and 'F%' ' <--- s/E/F/
>Order by UPPER(Surname);
Thanks for the response guys but then my problem comes when i try and
query a range between T and Z i tried to make it query between T to A
but got no result. Anyway i will have to add a constraint in my PHP code
for T to Z.

Regards

Roger
> 
> Martin
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using Between in SQL

[Arjen Markus]

Roger wrote:


I have a question.

I trying to write a query as follows

Select *

From People

Where Surname Between 'A%' and 'E%'
Order by UPPER(Surname);

Now the problem i have is that i get only the Surnames from A up to D
and the E's are excluded.

Can anyone help me out, i am writing a web based application.

 


I am no expert on SQL, but I think it is likely that BETWEEN does not
acknowledge the wildcards.

Try: Surname between 'A' and 'E' instead (or 'F')

(If it does, there may be a severe interpretation problem - wildcards
introduce some sort of a range themselves, so the bounds are ranges too!)

Regards,

Arjen

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Using Between in SQL

[Martin Jenkins]

Roger wrote:

I have a question.

I trying to write a query as follows

Select *

From People

Where Surname Between 'A%' and 'E%'
Order by UPPER(Surname);

Now the problem i have is that i get only the Surnames from A up to D
and the E's are excluded.

Can anyone help me out, i am writing a web based application.



How about:

  Select *
  From People
  Where Surname Between 'A%' and 'F%' ' <--- s/E/F/
  Order by UPPER(Surname);

Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] count(gid) takes too long

[Martin Jenkins]

Sripathi Raj wrote:


This is on a NFS on Windows XP Xeon - 2.8 Ghz, 1 Gig RAM and the database
size is 395 MB. I'm connecting to the database from Perl. There is no
discernible difference b/w Perl and sqlite shell.


NFS? Hmm... You have been following the locking threads, haven't you? ;)

How long does it take to read the entire file? Don't forget that network 
bandwidth is << than disk bandwidth. The disks in my Samba server may do 
>60MB/sec but my 100Mb network restricts this to about 8.5MB/sec. 
That's around 50 seconds for a straight read of your 400MB file before 
you even do anything with it. If you have to search indexes, go back to 
read data etc etc then it's bound to be slow. If your network is at all 
loaded the performance will be even worse.


It takes about 5 seconds to copy my 43MB test database to a Samba server
(1GHz Athlon, new SATA2 disks) over 100Mb CAT5. The SQLite shell takes a
good 9 seconds for your count(gid) query. It's 8.5 seconds from Python.

If I repeat the test the time goes down to about 0.4 seconds - XP cached 
the data.


If I (roughly) simulate lots of network traffic by listing all the files 
on the server the test takes about 28 seconds.


If I "saturate" the network by copying a ~4GB file from the server to my 
PC the test takes about 43 seconds.


That's a >100x slowdown without even trying.

Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Using Between in SQL

[Roger]

I have a question.

I trying to write a query as follows

Select *
>From People
Where Surname Between 'A%' and 'E%'
Order by UPPER(Surname);

Now the problem i have is that i get only the Surnames from A up to D
and the E's are excluded.

Can anyone help me out, i am writing a web based application.

Re: [sqlite] format for column names

[Martin Jenkins]

T&B wrote:

Hi all,

I've just joined this mail list.

I've read through the syntax page and other sources as to how to
create a table using SQLite, and it's all working fine. But I can't
find any specifications for the format of a column name. Does the
spec permit spaces in the name? It seems to work OK when creating a
table if I enclose the column name in quotes, eg:

CREATE TABLE People('Name First', 'Name Last')

And getting the list of column names works fine. But select
statements don't seem to work if the column names have spaces.

So, are spaces allowed or not? Is there a specification somewhere
that shows what characters, length etc are allowed in column names
(and table names)?


It's an SQL thing, not an SQLite thing - if you must use spaces in
column names, put the column name in []. Same applies for column names
which are reserved words.

C:\>sqlite3 tom
Loading resources from C:\Documents and Settings\Martin/.sqliterc
SQLite version 3.3.6
Enter ".help" for instructions
sqlite> CREATE TABLE People('Name First', 'Name Last');
sqlite> insert into People values("Martin","Jenkins");

sqlite> select * from People;
Name First  Name Last
--  --
Martin  Jenkins

sqlite> select 'name First' from People;
'name First'

name First

sqlite> select [name First] from People;
Name First
--
Martin

sqlite>
sqlite> select "made up name" from People;
"made up name"
--
made up name

sqlite>.q

HTH

Martin



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Performance Question

[Mario Frasca]

On 2006-0829 13:15:02, [EMAIL PROTECTED] wrote:


To my surprise (perhaps "horror") I find that SQLite has
for a very long time allowed NULL values in PRIMARY KEY
columns.  [...]
 



I understand your concern about legacy programs, but most of us expect PRIMARY 
KEY to imply NOT NULL...  don't we?  what about looking for alternative good 
solutions?  we could put the correction code in the source, conditionally 
compiled (not the default) and with the next major release reverse the 
condition (new 'corrected' source becomes default and old 'legacy' behaviour 
still available if desired) ... ?

maybe 'the best of both worlds', hope you agree.

regards,
Mario



-
To unsubscribe, send email to [EMAIL PROTECTED]
-