Re: [sqlite] Regarding FTS1

2007-09-03 Thread Babu, Lokesh
Hi Scott,

Thanks for the information.

One more question, As you said, Full text builds an index of data, so
I hope you have done some memory analysis too, could you please tell
me the memory usage based on your analysis.


On 9/4/07, Scott Hess <[EMAIL PROTECTED]> wrote:
> On 9/2/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote:
> > Does anyone help me out, What is FTS1, How to use FTS1, If any sample
> > programs to use FTS1 and understand better.
>
> The "fts" modules are "fulltext search" modules for SQLite.  "Fulltext
> search" meaning that it builds an index based on terms in the data
> stored, rather than on the full column.  So you could store this
> email, and searches for the term "fulltext" will hit it.
>
> fts1 was the first iteration, and had performance problems when
> storing many documents (on the order of 10,000 documents).  fts2 is
> the next iteration, which is significantly faster than fts1 and can
> easily store hundreds of thousands of documents.
>
> fts3 is a version of fts2 with a design flaw fixed.  Both fts1 and
> fts2 will be deprecated RSN in favor of fts3, but, for now, fts2 is
> what you should be using.
>
> Usage is straight-forward, you just write SQLite code.  The most
> recent examples I've written were part of the Google Gears docs, at:
>   http://code.google.com/apis/gears/api_database.html#sqlite_fts
>
> Obviously, the way you execute SQLite statements in Google Gears
> differs from how you'd do so in C or something else, but the SQLite
> statements themselves work the same.
>
> Depending on your platform, you may have to build SQLite from scratch
> to get fts2 linked in.  There's somewhat dated information about this
> at:
>   http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex
>
> -scott
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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



Re: [sqlite] remote access to sqlite db?

2007-09-03 Thread Mina R Waheeb
On 9/4/07, Scott Derrick <[EMAIL PROTECTED]> wrote:
> You could always use rails and get to your database through an html
> connection.  You can take your choice of web servers though the built in
> mongrel-rails server will easily handle 20-30 concurrent connections.
>
> Scott
>
> Kees Nuyt wrote:
> > On Sun, 2 Sep 2007 23:23:43 -0400, you wrote:
> >
> >> Hi,
> >>
> >> Does sqlite offer the ability to connect to a sqlite db file on a
> >> remote machine? I've been using it locally for awhile and it's great.
> >> Wanted to see if it could be used remotely for some simple tasks.
> >
> > It does, but there are restrictions:
> > http://www.sqlite.org/whentouse.html
> >
> > For very low concurrency (one user at a time), it shouldn't be a
> > problem, but you will notice loss of speed.
> >
> > There are also a few client/server drivers for SQLite:
> > http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork
> > http://www.sqlite.org/contrib
> > sqlite-networked
> >
> >> Thanks,
> >> Mark
> >
> > Hope this helps.
> > Regards,
>
> --
>
> -
>  What county can preserve its liberties, if its rulers are not
> warned from time to time that its people preserve the spirit of resistance.
>
>  Thomas Jefferson
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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



Re: [sqlite] remote access to sqlite db?

2007-09-03 Thread Scott Derrick
You could always use rails and get to your database through an html 
connection.  You can take your choice of web servers though the built in 
mongrel-rails server will easily handle 20-30 concurrent connections.


Scott

Kees Nuyt wrote:

On Sun, 2 Sep 2007 23:23:43 -0400, you wrote:


Hi,

Does sqlite offer the ability to connect to a sqlite db file on a
remote machine? I've been using it locally for awhile and it's great.
Wanted to see if it could be used remotely for some simple tasks.


It does, but there are restrictions:
http://www.sqlite.org/whentouse.html

For very low concurrency (one user at a time), it shouldn't be a
problem, but you will notice loss of speed.

There are also a few client/server drivers for SQLite:
http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork
http://www.sqlite.org/contrib
sqlite-networked


Thanks,
Mark


Hope this helps.
Regards,


--

-
What county can preserve its liberties, if its rulers are not 
warned from time to time that its people preserve the spirit of resistance.


Thomas Jefferson

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



Re: [sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread John Machin

On 4/09/2007 7:13 AM, Doug Currie wrote:
On Monday, September 03, 2007 Nuno Lucas wrote: 


Maybe some OS specific error? Wasn't there some discussion earlier
about the Microsoft compiler not using the full double precision by
default?


Microsoft C compilers store long doubles in 64 bits, just like
doubles http://msdn2.microsoft.com/en-us/library/9c3yd98k(VS.80).aspx
whereas gcc stores long doubles in 96 bits and uses 64 bit mantissa
(80x87 "extended" type) versus Microsoft 53 bit mantissa.
http://msdn2.microsoft.com/en-us/library/9cx8xs15(vs.80).aspx

Sqlite3 uses long doubles in round, and other functions, and so
results between Microsoft Visual C++ compiled and gcc compiled
versions of sqlite3 are bound to produce different results.


"different" means wrong, even if only by 1 ulp.

A correct answer should be achievable with 64-bit FP. The Windows 
distribution of Python is compiled with an MS C compiler; it manages to 
get the correct answer for Selena's test cases. The source for round() 
is trivially small, platform-independent, and just uses doubles. It 
relies on the C library floor() function (ceil() if negative). Rounding 
a positive number f to 2 decimal places is effectively as simple as 
floor(f * 100.0 + 0.5) / 100.0 ... worth borrowing, perhaps.




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



Re: [sqlite] why a VIEW doesn't contain a ROWID

2007-09-03 Thread Kees Nuyt
On Mon, 03 Sep 2007 18:05:05 -0300, you wrote:

>Hi Kees,
>
>He is telling about the Rowid the unique number that represents each row
>in the table, not about a table column named "ID" or anything else, or
>the primary key of the table.
>
>[]'s,
>
>Marco Antonio Abreu
>IT Quality Systems
>[EMAIL PROTECTED]
>http://www.itquality.com.br


You are right, but if a column is defined as INTEGER PRIMARY KEY
it acts as an alias for the (physical) ROWID, so it acually
describes the same case.

Definition:
http://www.sqlite.org/lang_createtable.html says:
Specifying a PRIMARY KEY normally just creates a UNIQUE index on
the corresponding columns. However, if primary key is on a
single column that has datatype INTEGER, then that column is
used internally as the actual key of the B-Tree for the table.
This means that the column may only hold unique integer values.
[...] If a table does not have an INTEGER PRIMARY KEY column,
then the B-Tree key will be a automatically generated integer.
The B-Tree key for a row can always be accessed using one of the
special names "ROWID", "OID", or "_ROWID_". This is true
regardless of whether or not there is an INTEGER PRIMARY KEY.
[...]

Proof:
CREATE TABLE testTbl(
t_name TEXT
);
INSERT INTO testTbl VALUES('d1');
INSERT INTO testTbl VALUES('d2');
INSERT INTO testTbl VALUES('d3');
INSERT INTO testTbl VALUES('d4');
SELECT ROWID,t_name FROM testTbl;
1|d1
2|d2
3|d3
4|d4
DELETE FROM testTbl WHERE t_name='d2';
SELECT ROWID,t_name FROM testTbl;
1|d1
3|d3
4|d4

same picture..

Regards,
  
-- 
  (  Kees Nuyt
  )
c[_]

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



[sqlite] BigNameUsers: Nokia using SQLite in PCsuite

2007-09-03 Thread Kees Nuyt

Hi folks,

Nokia uses SQLite on Windows to exchange data between PC and a
cellphone using what they call Nokia PC Suite. 

Not very surprising, as Symbian is the OS on Mokia high end
phones, but SQLite is also used in exchanges with its 6233
model, which doesn't run Symbian, but Nokia OS, with Series 40
UI. 
I'm not sure if SQLite is used in the cellphone itself, I only
found a SQLite3 database in a PC disk directory used for
exchanges.

Schema:

PRAGMA page_size=1024;
PRAGMA default_cache_size=2000;
PRAGMA encoding=UTF-8;
PRAGMA legacy_file_format=1;

CREATE TABLE contact_general (
 uid PRIMARY KEY,
 name TEXT,
 first_name TEXT,
 middle_name TEXT,
 last_name TEXT,
 nickname TEXT,
 formal_name TEXT,
 versit_object BLOB,
 versit_length INTEGER
);
CREATE TABLE contact_numbers (
 gen_info_uid INTEGER,
 number_text TEXT,
 number_type TEXT
);
CREATE TABLE sms_folders (
 sms_folder_name TEXT,
 sms_view_name TEXT,
 sms_options INTEGER
);
CREATE TABLE sms_messages (
 sms_uid PRIMARY KEY,
 sms_info_field INTEGER,
 sms_data_length INTEGER,
 sms_data BLOB,
 sms_date_year INTEGER,
 sms_date_month INTEGER,
 sms_date_day INTEGER,
 sms_date_hour INTEGER,
 sms_date_minute INTEGER,
 sms_date_second INTEGER,
 sms_date_timezone INTEGER,
 sms_date_bias INTEGER,
 sms_folder TEXT,
 sms_address_1 TEXT,
 sms_address_1_type INTEGER,
 sms_address_2 TEXT,
 sms_address_2_type INTEGER
);
CREATE VIEW sms_predefinbox_view AS
 SELECT * FROM sms_messages
  WHERE sms_folder='predefinbox';

Pictures:
http://www.forum.nokia.com/devices/pics/6233_main.jpg
http://www.nokia.com/search/images/logo_nokia_115_40.gif
(I didn't find a real good one)
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] sqlite versions, binary compatibility

2007-09-03 Thread drh
Ray Kiddy <[EMAIL PROTECTED]> wrote:
> Hello -
> 
> I tried to use an older version of the sqlite3 executable on Mac OS X  
> with a data file from a newer version. No joy resulted.
> 
> Looking at http://www.sqlite.org/changes.html, I do not see any notes  
> about whether any version breaks binary compatibility with any older  
> version.
> 
> Should it just be assumed that any version change breaks all binary  
> compatibility with earlier versions? Is this done deliberately? Or is  
> there somewhere else that issues relating to binary compatibility of  
> data files between versions is documented?
> 

The database file for all versions of SQLite are
backwards compatible for all versions of SQLite going
bach through version 3.0.0.  Any newer version of
SQLite can read and write database written by older
versions of SQLite.  For the most part this works
in the opposite direction too - older versions of
SQLite can read and write databases created by
newer versions of SQLite.  The exception is if the
newer version of SQLite created database that uses
some feature that was added after the older version
of SQLite was released.



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


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



Re: [sqlite] sqlite versions, binary compatibility

2007-09-03 Thread Ray Kiddy


On Sep 3, 2007, at 1:56 PM, Marco Antonio Abreu wrote:


Hi Ray,

Look at version 3.4.0 (2007 june 18), third item.

- Added explicit upper bounds on the sizes and quantities of things
SQLite can process. *This change might cause compatibility  
problems* for

applications that use SQLite in the extreme, which is why the current
release is 3.4.0 instead of 3.3.18.

[]'s,

Marco Antonio Abreu
IT Quality Systems
[EMAIL PROTECTED]
http://www.itquality.com.br




Ah. Very clearly called out. Yes, one wonders how I could have missed  
it. :-)


thanx - ray



Ray Kiddy wrote:


Hello -

I tried to use an older version of the sqlite3 executable on Mac OS X
with a data file from a newer version. No joy resulted.

Looking at http://www.sqlite.org/changes.html, I do not see any notes
about whether any version breaks binary compatibility with any older
version.

Should it just be assumed that any version change breaks all binary
compatibility with earlier versions? Is this done deliberately? Or is
there somewhere else that issues relating to binary compatibility of
data files between versions is documented?

thanx - ray


- 



To unsubscribe, send email to [EMAIL PROTECTED]
- 








-- 
---

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






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



Re: [sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread Doug Currie
On Monday, September 03, 2007 Nuno Lucas wrote: 

> Maybe some OS specific error? Wasn't there some discussion earlier
> about the Microsoft compiler not using the full double precision by
> default?

Microsoft C compilers store long doubles in 64 bits, just like
doubles http://msdn2.microsoft.com/en-us/library/9c3yd98k(VS.80).aspx
whereas gcc stores long doubles in 96 bits and uses 64 bit mantissa
(80x87 "extended" type) versus Microsoft 53 bit mantissa.
http://msdn2.microsoft.com/en-us/library/9cx8xs15(vs.80).aspx

Sqlite3 uses long doubles in round, and other functions, and so
results between Microsoft Visual C++ compiled and gcc compiled
versions of sqlite3 are bound to produce different results.

Why my gcc 3.4.5 compiled sqlite3.exe and the one from the sqlite.org
downloads page produce different results is still a mystery to me.

e

-- 
Doug Currie
Londonderry, NH, USA


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



Re: [sqlite] why a VIEW doesn't contain a ROWID

2007-09-03 Thread Marco Antonio Abreu
Hi Kees,

He is telling about the Rowid the unique number that represents each row
in the table, not about a table column named "ID" or anything else, or
the primary key of the table.

[]'s,

Marco Antonio Abreu
IT Quality Systems
[EMAIL PROTECTED]
http://www.itquality.com.br



Kees Nuyt wrote:
> Hi Lokesh,
>
> On Mon, 3 Sep 2007 15:30:10 +0530, you wrote:
>
>   
>> This I know, but the thing is, I want the ROWID 
>> in VIEW to be sequential even after a SELECT with
>> some condition has been executed, ie., from 1 to n.
>> Just like in normal table. 
>> In your case it is not like that.
>> 
>
> If you delete rows from a table the tables' rowid isn't
> consecutive anymore:
>
> CREATE TABLE testTbl(
>   t_id INTEGER PRIMARY KEY,
>   t_name TEXT
> );
> INSERT INTO testTbl VALUES( 1, 'd1' );
> INSERT INTO testTbl VALUES( 2, 'd2' );
> INSERT INTO testTbl VALUES( 3, 'd3' );
> INSERT INTO testTbl VALUES( 4, 'd4' );
> SELECT * FROM testTbl;
> 1|d1
> 2|d2
> 3|d3
> 4|d4
> DELETE FROM testTbl WHERE t_id=2;
> SELECT * FROM testTbl;
> 1|d1
> 3|d3
> 4|d4
>
>   
>> By the way, what I mean to say is, why 
>> don't we have default ROWID in >VIEW
>> like as in normal TABLE.
>> 
>
> Because a view isn't a table.
>
> By the way, the concept of rowid is not in the SQL standard. 
> It is a physical property (the B-Tree key) which rows happen to
> have when they are stored the SQLite way. It has no other
> meaning. A member of a set doesn't have an ordinal number in
> relational theory.
>
> Richard Hipp made rowid visible because some tight embedded
> applications can be speeded up nicely by using it.
> Any code which uses the rowid is not portable, though.
>
> The number of a row in a view is its order of its occurence.
> The first row has number 1
> The second row has number 2
> etc.
>
> It is easy to materialize that number in any language you will
> use around your SQL, even in a shell:
>
> sqlite3 your.db "select * from testTbl;" | \
> awk -v OFS='|' '{print NR,$0}'
>
> 1|1|d1
> 2|3|d3
> 3|4|d4
>
> note: \ is linewrap
>
> Regards,
>   

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



Re: [sqlite] sqlite versions, binary compatibility

2007-09-03 Thread Marco Antonio Abreu
Hi Ray,

Look at version 3.4.0 (2007 june 18), third item.

- Added explicit upper bounds on the sizes and quantities of things
SQLite can process. *This change might cause compatibility problems* for
applications that use SQLite in the extreme, which is why the current
release is 3.4.0 instead of 3.3.18.

[]'s,

Marco Antonio Abreu
IT Quality Systems
[EMAIL PROTECTED]
http://www.itquality.com.br



Ray Kiddy wrote:
>
> Hello -
>
> I tried to use an older version of the sqlite3 executable on Mac OS X
> with a data file from a newer version. No joy resulted.
>
> Looking at http://www.sqlite.org/changes.html, I do not see any notes
> about whether any version breaks binary compatibility with any older
> version.
>
> Should it just be assumed that any version change breaks all binary
> compatibility with earlier versions? Is this done deliberately? Or is
> there somewhere else that issues relating to binary compatibility of
> data files between versions is documented?
>
> thanx - ray
>
>
> -
>
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
>

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



[sqlite] sqlite versions, binary compatibility

2007-09-03 Thread Ray Kiddy


Hello -

I tried to use an older version of the sqlite3 executable on Mac OS X  
with a data file from a newer version. No joy resulted.


Looking at http://www.sqlite.org/changes.html, I do not see any notes  
about whether any version breaks binary compatibility with any older  
version.


Should it just be assumed that any version change breaks all binary  
compatibility with earlier versions? Is this done deliberately? Or is  
there somewhere else that issues relating to binary compatibility of  
data files between versions is documented?


thanx - ray


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



Re: [sqlite] Regarding FTS1

2007-09-03 Thread Scott Hess
On 9/2/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote:
> Does anyone help me out, What is FTS1, How to use FTS1, If any sample
> programs to use FTS1 and understand better.

The "fts" modules are "fulltext search" modules for SQLite.  "Fulltext
search" meaning that it builds an index based on terms in the data
stored, rather than on the full column.  So you could store this
email, and searches for the term "fulltext" will hit it.

fts1 was the first iteration, and had performance problems when
storing many documents (on the order of 10,000 documents).  fts2 is
the next iteration, which is significantly faster than fts1 and can
easily store hundreds of thousands of documents.

fts3 is a version of fts2 with a design flaw fixed.  Both fts1 and
fts2 will be deprecated RSN in favor of fts3, but, for now, fts2 is
what you should be using.

Usage is straight-forward, you just write SQLite code.  The most
recent examples I've written were part of the Google Gears docs, at:
   http://code.google.com/apis/gears/api_database.html#sqlite_fts

Obviously, the way you execute SQLite statements in Google Gears
differs from how you'd do so in C or something else, but the SQLite
statements themselves work the same.

Depending on your platform, you may have to build SQLite from scratch
to get fts2 linked in.  There's somewhat dated information about this
at:
   http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex

-scott

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



Re: [sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread Kees Nuyt
On Mon, 3 Sep 2007 18:29:55 +0100, you wrote:

>This made me to remember there was a bug some time ago about the
>rounding algorithm (but can't remember at what version it was fixed),
>so I just tested it.
>
>"official" amalgamated sqlite 3.4.0 downloaded from the site some time ago:
>
>SQLite version 3.4.0
>Enter ".help" for instructions
>sqlite> select round(98926650.5, 1) ;
>98926650.5
>sqlite> select round(85227887.01, 1) ;
>85227887.0
>sqlite> select round(85227887.01, 2) ;
>85227887.01
>sqlite> select round(98926650.50001, 1) ;
>98926650.5
>
>Linux [K]Ubuntu 7.04 (feisty) sqlite3 package 3.3.13-0ubuntu1:
>
>SQLite version 3.3.13
>Enter ".help" for instructions
>sqlite> select round(98926650.5, 1);
>98926650.5
>sqlite> select round(85227887.01, 1);
>85227887.0
>sqlite> select round(85227887.01, 2);
>85227887.01
>sqlite> select round(98926650.50001, 1) ;
>98926650.5
>
>$ uname -a
>Linux ubuno 2.6.20-16-generic #2 SMP Thu Jun 7 20:19:32 UTC 2007 i686 GNU/Linux
>
>
>So it seems SQLite is already doing the right job.
>Maybe some OS specific error? Wasn't there some discussion earlier
>about the Microsoft compiler not using the full double precision by
>default?
>
>
>Regards,
>~Nuno Lucas


I found something in a post by Joe Wilson.

Message-ID:
<[EMAIL PROTECTED]>
Date: Sun, 10 Jun 2007 15:55:32 -0700 (PDT)
From: Joe Wilson <[EMAIL PROTECTED]>

He found: 

http://support.microsoft.com/kb/102555

 Microsoft Visual C++ runtime library provides default
floating-point exception handling and includes functions such as
_controlfp for determining and adjusting the floating-point
hardware's rounding, precision control, and exception handling
behavior.

More info on fp:precise and _controlfp:

 http://msdn2.microsoft.com/en-us/library/aa289157(vs.71).aspx

I'm not sure this is related.

Out of curiousness I tried it myself on Microsoft Windows
[Version 6.0.6000, aka vista]

SQLite version 3.4.2

sqlite> select round(98926650.50001, 1);
98926650.501
sqlite> select round(98926650.50001 -0.1, 1);
98926650.501
sqlite> select round(98926650.50001 -0.0001, 1);
98926650.501
sqlite> select round(98926650.50001 -0.001, 1);
98926650.501
sqlite> select round(98926650.50001 -0.01, 1);
98926650.501
sqlite> select round(98926650.50001 -0.1, 1);
98926650.4
sqlite>

Oh, well, 9 digits of accuracy is way more than most
measurements we can do in daily life.
For money, use integers and express in cents / centimes or
something.
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread Doug Currie
On Monday, September 03, 2007 Nuno Lucas wrote: 

> This made me to remember there was a bug some time ago about the
> rounding algorithm (but can't remember at what version it was fixed),
> so I just tested it.

> "official" amalgamated sqlite 3.4.0 downloaded from the site some time ago:

> SQLite version 3.4.0
> Enter ".help" for instructions
> sqlite> select round(98926650.5, 1) ;
> 98926650.5

> So it seems SQLite is already doing the right job.
> Maybe some OS specific error? Wasn't there some discussion earlier
> about the Microsoft compiler not using the full double precision by
> default?

On WinXP

SQLite version 3.4.2
Enter ".help" for instructions
sqlite> select round(98926650.5, 1);
98926650.501

My results above are using the sqlite3.exe from http://www.sqlite.org.
I belive this is compiled with gcc, but I don't know what version.

However, if I compile from sources, I get

C:\Dev\sqlite\sqlite-3.4.2\bld>.\sqlite3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> select round(98926650.5, 1);
98926650.5

I am using:
$ gcc --version
gcc.exe (GCC) 3.4.5 (mingw special)

e

-- 
Doug Currie
Londonderry, NH, USA


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



Re: [sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread Nuno Lucas
On 9/3/07, Doug Currie <[EMAIL PROTECTED]> wrote:
> On Monday, September 03, 2007 Arjen Markus wrote:
>
> > Serena Lien wrote:
>
> >>round(98926650.5, 1) -> 98926650.501
> >>round(85227887.01, 1) -> 85227887.001
>
> > They are in fact rounded, but the internal binary representation can
> > not be turned into the appropriate decimal (and human readable)
> > representation due to the finite precision.
>
> 98926650.5 is represented exactly in IEEE double; something else is
> mucking up the round or the display of the result, maybe both.
>
> 85227887.0 is represented exactly in IEEE double, though 85227887.01
> is not. Nevertheless, there are ways to print floating point numbers
> readably. http://portal.acm.org/citation.cfm?id=93559

This made me to remember there was a bug some time ago about the
rounding algorithm (but can't remember at what version it was fixed),
so I just tested it.

"official" amalgamated sqlite 3.4.0 downloaded from the site some time ago:

SQLite version 3.4.0
Enter ".help" for instructions
sqlite> select round(98926650.5, 1) ;
98926650.5
sqlite> select round(85227887.01, 1) ;
85227887.0
sqlite> select round(85227887.01, 2) ;
85227887.01
sqlite> select round(98926650.50001, 1) ;
98926650.5

Linux [K]Ubuntu 7.04 (feisty) sqlite3 package 3.3.13-0ubuntu1:

SQLite version 3.3.13
Enter ".help" for instructions
sqlite> select round(98926650.5, 1);
98926650.5
sqlite> select round(85227887.01, 1);
85227887.0
sqlite> select round(85227887.01, 2);
85227887.01
sqlite> select round(98926650.50001, 1) ;
98926650.5

$ uname -a
Linux ubuno 2.6.20-16-generic #2 SMP Thu Jun 7 20:19:32 UTC 2007 i686 GNU/Linux


So it seems SQLite is already doing the right job.
Maybe some OS specific error? Wasn't there some discussion earlier
about the Microsoft compiler not using the full double precision by
default?


Regards,
~Nuno Lucas

> e
>
> --
> Doug Currie
> Londonderry, NH, USA

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



Re: [sqlite] why a VIEW doesn't contain a ROWID

2007-09-03 Thread Kees Nuyt

Hi Lokesh,

On Mon, 3 Sep 2007 15:30:10 +0530, you wrote:

> This I know, but the thing is, I want the ROWID 
> in VIEW to be sequential even after a SELECT with
> some condition has been executed, ie., from 1 to n.
> Just like in normal table. 
> In your case it is not like that.

If you delete rows from a table the tables' rowid isn't
consecutive anymore:

CREATE TABLE testTbl(
t_id INTEGER PRIMARY KEY,
t_name TEXT
);
INSERT INTO testTbl VALUES( 1, 'd1' );
INSERT INTO testTbl VALUES( 2, 'd2' );
INSERT INTO testTbl VALUES( 3, 'd3' );
INSERT INTO testTbl VALUES( 4, 'd4' );
SELECT * FROM testTbl;
1|d1
2|d2
3|d3
4|d4
DELETE FROM testTbl WHERE t_id=2;
SELECT * FROM testTbl;
1|d1
3|d3
4|d4

> By the way, what I mean to say is, why 
> don't we have default ROWID in >VIEW
> like as in normal TABLE.

Because a view isn't a table.

By the way, the concept of rowid is not in the SQL standard. 
It is a physical property (the B-Tree key) which rows happen to
have when they are stored the SQLite way. It has no other
meaning. A member of a set doesn't have an ordinal number in
relational theory.

Richard Hipp made rowid visible because some tight embedded
applications can be speeded up nicely by using it.
Any code which uses the rowid is not portable, though.

The number of a row in a view is its order of its occurence.
The first row has number 1
The second row has number 2
etc.

It is easy to materialize that number in any language you will
use around your SQL, even in a shell:

sqlite3 your.db "select * from testTbl;" | \
awk -v OFS='|' '{print NR,$0}'

1|1|d1
2|3|d3
3|4|d4

note: \ is linewrap

Regards,
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread Doug Currie
On Monday, September 03, 2007 Arjen Markus wrote: 

> Serena Lien wrote:

>>Okay, but even if the rounded result does not have a finite binary
>>representation, shouldn't the displayed (human readable) representation be
>>at least truncated to the number of decimal places that were requested in
>>the round function? Not that I am confusing round with truncate, but surely
>>it is a more acceptable result?
>>  
>>
> Hm, that is a completely different question. It would mean that more 
> information is
> associated with the rounded result than merely the number (in internal
> representation).

No.

See the paper cited in my earlier email:
 http://portal.acm.org/citation.cfm?id=93559

Once the number is rounded (correctly), there is enough information in
the number itself. It can be displayed with exactly the smallest
number of digits necessary to reconstruct the number. This will be the
number of digits that Serena expects.

e

-- 
Doug Currie
Londonderry, NH, USA


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



Re: [sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread Doug Currie
On Monday, September 03, 2007 Arjen Markus wrote: 

> Serena Lien wrote:

>>round(98926650.5, 1) -> 98926650.501
>>round(85227887.01, 1) -> 85227887.001

> They are in fact rounded, but the internal binary representation can
> not be turned into the appropriate decimal (and human readable)
> representation due to the finite precision.

98926650.5 is represented exactly in IEEE double; something else is
mucking up the round or the display of the result, maybe both.

85227887.0 is represented exactly in IEEE double, though 85227887.01
is not. Nevertheless, there are ways to print floating point numbers
readably. http://portal.acm.org/citation.cfm?id=93559

e

-- 
Doug Currie
Londonderry, NH, USA


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



Re: [sqlite] remote access to sqlite db?

2007-09-03 Thread Kees Nuyt
On Sun, 2 Sep 2007 23:23:43 -0400, you wrote:

>Hi,
>
>Does sqlite offer the ability to connect to a sqlite db file on a
>remote machine? I've been using it locally for awhile and it's great.
>Wanted to see if it could be used remotely for some simple tasks.

It does, but there are restrictions:
http://www.sqlite.org/whentouse.html

For very low concurrency (one user at a time), it shouldn't be a
problem, but you will notice loss of speed.

There are also a few client/server drivers for SQLite:
http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork
http://www.sqlite.org/contrib
sqlite-networked

>Thanks,
>Mark

Hope this helps.
Regards,
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] how to iterate on SELECT query results

2007-09-03 Thread Trevor Talbot
On 9/3/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote:

> After doing some SELECT operation on a TABLE, say we get 100 result
> items out of 1000 records, Is there any way where I can iterate
> through this result set. i.e., Get N items out of 100, say get
> previous 10, get next 10, etc,
>
> This should be done without creating a temporary table or virtual
> tables. As there is overhead of space and time.

There is always the overhead of space or time, as it's impossible to
know what the results are unless you actually calculate them.  To get
row 100, the query must find rows 1-99 first.

The LIMIT and OFFSET clauses of the SELECT statement can be used to
get parts of a query without storing anything.  This is the worst case
of time overhead though, since the entire query must be run every
time.

An approach that works for many people is to store markers for use in
relative queries.  This requires a column with unique, ordered values,
and that you run the query using it as an ORDER BY.  An INTEGER
PRIMARY KEY AUTOINCREMENT column is useful for this; let's assume you
have one named ID.  If you retrieve rows 101-110, you can store the
IDs for row #101 (let's say this is ID 234), and row #110 (ID 280).
When you need to retrieve the previous 10 rows, you get them backwards
using a query such as:

SELECT * FROM table
WHERE id < 234
ORDER BY id DESC
LIMIT 10;

This avoids having to retrieve rows for the entire result set when
changing "pages" sequentially.

You can imagine other schemes that involve storing all the IDs for the
query result set so that you can jump to them at random, etc.

> If it is possible to use VIEWs then how can I? As I think VIEWs are
> little better than temp table or virtual table. Please correct me if
> I'm wrong.

Better for doing what?  A view isn't going to help with paging result sets.

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



Re: [sqlite] why a VIEW doesn't contain a ROWID

2007-09-03 Thread Trevor Talbot
On 9/2/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote:

> Why a VIEW doesn't contain a ROWID field. Even though if it is
> accessed, it contains (null) value. How to initialise this or how to
> make it work as in TABLE.

The rowid field exposes SQLite's internal storage mechanism for table
rows.  It has certain properties, such as being unique and relatively
stable.  Views are just stored queries which may reference many tables
or calculate columns from other data, but do not store any data
themselves.  Since a view does not store a row, a rowid simply doesn't
exist.

It's not possible to create one.  If you want table behavior, then use a table.

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



Re: [sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread Nuno Lucas
On 9/3/07, Serena Lien <[EMAIL PROTECTED]> wrote:
> Okay, but even if the rounded result does not have a finite binary
> representation, shouldn't the displayed (human readable) representation be
> at least truncated to the number of decimal places that were requested in
> the round function? Not that I am confusing round with truncate, but surely
> it is a more acceptable result?

One way you could do this would be to implement your own round()
function to return a string instead of a number. That way it would
display the right result.

Look at func.c [1] to see how round() is implemented.


Regards,
~Nuno Lucas

[1] http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/func.c=1.174

>
> thanks, Serena.

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



RE: [sqlite] (select *) VS (select column1, column2 ...)

2007-09-03 Thread Joe Wilson
--- "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
>   I wanted to know how SQLITE works internally. Assume I am doing

Try using the EXPLAIN command on your queries:

EXPLAIN SELECT foo from bar;
EXPLAIN SELECT * from bar;

http://www.sqlite.org/lang_explain.html

> But if SQLITE uses some other buffer then this might cause problem as
> copying the whole data will certainly take more time than copying the
> two columns.

Selecting unnecessary columns add extra column opcodes to the select
loop and can take up extra space in temp store for sub-queries.

Here's a concrete example of how "select *" can be slower than just
selecting the specific column(s) you need:

$ ./sqlite3 slow.db .dump
BEGIN TRANSACTION;
CREATE TABLE n(a);
INSERT INTO "n" VALUES(1);
INSERT INTO "n" VALUES(2);
INSERT INTO "n" VALUES(3);
INSERT INTO "n" VALUES(4);
INSERT INTO "n" VALUES(5);
INSERT INTO "n" VALUES(6);
INSERT INTO "n" VALUES(7);
INSERT INTO "n" VALUES(8);
INSERT INTO "n" VALUES(9);
INSERT INTO "n" VALUES(10);
CREATE VIEW v as select n1.a-n5.a k,* from n n1,n n2,n n3,n n4,n n5,n n6;
COMMIT;

$ time ./sqlite3 slow.db "select min(k) from (select * from v limit 99);"
-9
6.99user 0.23system 0:07.24elapsed 99%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (0major+4042minor)pagefaults 0swaps

$ time ./sqlite3 slow.db "select min(k) from (select k from v limit 99);"
-9
4.63user 0.13system 0:04.81elapsed 99%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (0major+4036minor)pagefaults 0swaps

You can see that using "select *" takes roughly 50% more time in this case.
The difference in time depends entirely on your specific query and the data.



   

Got a little couch potato? 
Check out fun summer activities for kids.
http://search.yahoo.com/search?fr=oni_on_mail=summer+activities+for+kids=bz
 

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



RE: [sqlite] beginner

2007-09-03 Thread kirrthana M
In the sql query use AND operator for the two columns you want to compare.
Ex:select * from table_name where col1=="value" AND col2 == "value";

-Original Message-
From: nishit sharma [mailto:[EMAIL PROTECTED]
Sent: Monday, September 03, 2007 3:41 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] beginner


thanks for this help. i have done this...
my another problem is that i have multiple columns in my database and on the
basis of
two columns( has integer values) i have to read the database.
but i m unable to make a loop in C.
can u help me.

regards
Nishit


On 9/3/07, Pavan <[EMAIL PROTECTED]> wrote:
>
> You should link the library when you compile .It should be gcc
> test.c-l
> library name should be your sqlite shared library.  Just check in /usr/lib
> directory
>
> Thanks,
> Pavan.
>
>
> On 8/31/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> >
> > hey buddy can u tell me how to compile the C source code in which i have
> > used
> > sqlite3_open() like calls.
> > i m doing gcc test.c but it is giving me error of undefined reference.
> > i think i am missing some thing in compiling.
> > waiting for reply
> >
> > regards
> > Nishit
> >
> >
> > On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> > >
> > > thanks for telling the link.
> > >
> > > regards
> > >
> > >
> > >  On 8/30/07, Pavan <[EMAIL PROTECTED]> wrote:
> > > >
> > > > Hi Nishit,
> > > >
> > > > http://www.sqlite.org/quickstart.html
> > > >
> > > > This is a good link to start with.
> > > >
> > > > Thanks,
> > > > Pavan.
> > > >
> > > >
> > > > On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> > > > >
> > > > > Hi,
> > > > > i m beginner to sqlite
> > > > > can anybody send me a link which can help me
> > > > > in building and maintining databse
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > '
> > > > Always finish stronger than you start
> > > > *
> > > >
> > >
> > >
> >
>
>
>
> --
> '
> Always finish stronger than you start
> *
>


The information contained in this electronic message and any attachments to 
this message are intended for the exclusive use of the addressee(s) and may 
contain proprietary, confidential or privileged information. If you are not the 
intended recipient, you should not disseminate, distribute or copy this e-mail. 
Please notify the sender immediately and destroy all copies of this message and 
any attachments contained in it.

Contact your Administrator for further information.

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



Re: [sqlite] beginner

2007-09-03 Thread Pavan
prepare sql query and pass it to sqlite3_exec

Thanks,
Pavan.


On 9/3/07, nishit sharma <[EMAIL PROTECTED]> wrote:
>
> thanks for this help. i have done this...
> my another problem is that i have multiple columns in my database and on
> the
> basis of
> two columns( has integer values) i have to read the database.
> but i m unable to make a loop in C.
> can u help me.
>
> regards
> Nishit
>
>
> On 9/3/07, Pavan <[EMAIL PROTECTED]> wrote:
> >
> > You should link the library when you compile .It should be gcc
> > test.c-l
> > library name should be your sqlite shared library.  Just check in
> /usr/lib
> > directory
> >
> > Thanks,
> > Pavan.
> >
> >
> > On 8/31/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> > >
> > > hey buddy can u tell me how to compile the C source code in which i
> have
> > > used
> > > sqlite3_open() like calls.
> > > i m doing gcc test.c but it is giving me error of undefined reference.
> > > i think i am missing some thing in compiling.
> > > waiting for reply
> > >
> > > regards
> > > Nishit
> > >
> > >
> > > On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> > > >
> > > > thanks for telling the link.
> > > >
> > > > regards
> > > >
> > > >
> > > >  On 8/30/07, Pavan <[EMAIL PROTECTED]> wrote:
> > > > >
> > > > > Hi Nishit,
> > > > >
> > > > > http://www.sqlite.org/quickstart.html
> > > > >
> > > > > This is a good link to start with.
> > > > >
> > > > > Thanks,
> > > > > Pavan.
> > > > >
> > > > >
> > > > > On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> > > > > >
> > > > > > Hi,
> > > > > > i m beginner to sqlite
> > > > > > can anybody send me a link which can help me
> > > > > > in building and maintining databse
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > '
> > > > > Always finish stronger than you start
> > > > > *
> > > > >
> > > >
> > > >
> > >
> >
> >
> >
> > --
> > '
> > Always finish stronger than you start
> > *
> >
>



-- 
'
Always finish stronger than you start
*


Re: [sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread Serena Lien
Thanks for pointing out the implementation issues. I was thinking only in
the case where I am asking for a string result, not all the time, ie
sqlite3_column_text, where I would want the string representation of the
rounded number in this format, and did not realize this would require
storing all the string results.. I suppose this conversion should really be
done in a wrapper function instead.

thanks for your help,
Serena.

On 9/3/07, Arjen Markus <[EMAIL PROTECTED]> wrote:
>
>
> Hm, that is a completely different question. It would mean that more
> information is
> associated with the rounded result than merely the number (in internal
> representation).
> I do not know enough about the way SQLite organises these things to
> speculate about
> that, but it seems like a computational burden to me:
>
> Suppose you have one million records to search for numbers that are then
> rounded. Not
> only would you need to compute and store the rounded numbers but also
> the string
> representation (or something to effect that you can get a proper decimal
> presentation).
>
> Regards,
>
> Arjen
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>


RE: [sqlite] (select *) VS (select column1, column2 ...)

2007-09-03 Thread B V, Phanisekhar
Thanks Tom,
I wanted to know how SQLITE works internally. Assume I am doing
"select *" and "select column1, column2" for some row. In both the cases
it will have to go through the B-Tree to reach that row. I believe this
is done when sqlite3_step is called. 
Now assume I have reached the node. Now I have to retrieve column1 and
column2 of this node. I believe this is done when I call
sqlite3_column_int and its variants. In case of "select *", I can input
the column number of column1, and column2 directly to get the results
without concerning about other columns. And if I retrieve these two
columns using "select column1, column2" then I have to input column no's
1 and 2 to get theses values. I believe the time to get any particular
column (column1 or column2 ... column40) will be same. If that's the
case then as far as sqlite3_step and Sqlite3_column_int functions are
concerned both "select *" (extracting only column1 and column2) and
"select column1, column2" will take the same time to retrieve the two
columns (column1 and column2). So I shouldn't see any difference (very
minor difference) between "select *" and "select column1, column2", when
I try to extract just the two columns. 

But if SQLITE uses some other buffer then this might cause problem as
copying the whole data will certainly take more time than copying the
two columns.

Please let me know if I am wrong and let me understand what exactly
happens. 

Regards,
Phani


-Original Message-
From: Tom Briggs [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 31, 2007 6:41 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] (select *) VS (select column1, column2 ...)


   In general, it's best to only include the columns you need in the
SELECT clause.  And not just with SQLite - that's the best approach when
dealing with any database.  SQLite is a bit more forgiving because
there's no network between the client and the database to slow things
down, but that's still a good rule to follow.

   In the particular example you cited, I think that the difference
would be so minimal as to be unnoticeable.  But there will definitely be
a difference - the sqlite3_prepare call will make it possible to
retrieve any of the 40 columns if you do "select *", while it will only
make available the three you name if you use "select col1, col2, col3".
It can't know what you're going to do after the query is executed, so it
has to prepare for any possibility.

   So, yes, there's a difference.  Yes, selecting only the columns you
need is more efficient.  No, I don't think you'll notice much of a
difference in terms of performance.

   -T

> -Original Message-
> From: B V, Phanisekhar [mailto:[EMAIL PROTECTED] 
> Sent: Friday, August 31, 2007 7:33 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] (select *) VS (select column1, column2 ...)
> 
> Assume I have a table with 40 columns.  I would like to know the
> difference between
> 
>  
> 
> Select * from table
> 
> Select column1, column2, column3 from table
> 
>  
> 
> While doing SQLITE3_PREPARE, will both take same amount of time? 
> 
> While doing SQLITE3_STEP, will both take same amount of time?
> 
> --
> --
> ---
> 
> sqlite3_prepare("Select * from table");
> 
> while(1)
> 
> {
> 
> iRet = sqlite3_step(pStmt);
> 
> if(iRet != SQLITE_ROW)
> 
> {
> 
> iRet = sqlite3_finalize(pStmt);
> 
> break;
> 
> }
> 
> Sqlite3_column_int(pStmt, column1);
> 
> Sqlite3_column_int(pStmt, column2);
> 
> Sqlite3_column_int(pStmt, column3);
> 
> }
> 
> --
> --
> ---
> 
> sqlite3_prepare("Select column1, column2, column3 from table");
> 
> while(1)
> 
> {
> 
> iRet = sqlite3_step(pStmt);
> 
> if(iRet != SQLITE_ROW)
> 
> {
> 
> iRet = sqlite3_finalize(pStmt);
> 
> break;
> 
> }
> 
> Sqlite3_column_int(pStmt, column1);
> 
> Sqlite3_column_int(pStmt, column2);
> 
> Sqlite3_column_int(pStmt, column3);
> 
> }
> 
> --
> --
> ---
> 
>  
> 
> If I want to extract just the 3 columns (column1, column2, 
> column3), and
> use select* from table as sql query, how much impact it will have?
> 
>  
> 
> Why I want to do this is because in some cases I need some particular
> combination in another any other combination of columns to be 
> extracted?
> (It's possible for me to do this using "select * from table" but it's
> not possible if I used "select column1, column2, column3 from 
> table" as
> I will have to frame another query)
> 
>  
> 
> NOTE: Please don't look at the syntax of sqlite3_prepare I just wrote
> the code to show what I want to do.
> 
>  
> 
> Regards,
> 
> Phani
> 
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] beginner

2007-09-03 Thread nishit sharma
thanks for this help. i have done this...
my another problem is that i have multiple columns in my database and on the
basis of
two columns( has integer values) i have to read the database.
but i m unable to make a loop in C.
can u help me.

regards
Nishit


On 9/3/07, Pavan <[EMAIL PROTECTED]> wrote:
>
> You should link the library when you compile .It should be gcc
> test.c-l
> library name should be your sqlite shared library.  Just check in /usr/lib
> directory
>
> Thanks,
> Pavan.
>
>
> On 8/31/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> >
> > hey buddy can u tell me how to compile the C source code in which i have
> > used
> > sqlite3_open() like calls.
> > i m doing gcc test.c but it is giving me error of undefined reference.
> > i think i am missing some thing in compiling.
> > waiting for reply
> >
> > regards
> > Nishit
> >
> >
> > On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> > >
> > > thanks for telling the link.
> > >
> > > regards
> > >
> > >
> > >  On 8/30/07, Pavan <[EMAIL PROTECTED]> wrote:
> > > >
> > > > Hi Nishit,
> > > >
> > > > http://www.sqlite.org/quickstart.html
> > > >
> > > > This is a good link to start with.
> > > >
> > > > Thanks,
> > > > Pavan.
> > > >
> > > >
> > > > On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> > > > >
> > > > > Hi,
> > > > > i m beginner to sqlite
> > > > > can anybody send me a link which can help me
> > > > > in building and maintining databse
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > '
> > > > Always finish stronger than you start
> > > > *
> > > >
> > >
> > >
> >
>
>
>
> --
> '
> Always finish stronger than you start
> *
>


Re: [sqlite] why a VIEW doesn't contain a ROWID

2007-09-03 Thread T

This I know, but the thing is, I want the ROWID in VIEW to be
sequential even after a SELECT with some condition has been executed,
ie., from 1 to n. Just like in normal table. In your case it is not
like that.


Ah, OK, I asked a similar question a week or two ago, and had to come  
up with my own solution (two actually), in the absence of others.  
Look for the thread "Enumerating rows in a view" in this mail list  
(or archive).


By the way, what I mean to say is, why don't we have default ROWID  
in VIEW like as in normal TABLE.


In a few situations (such as my previous thread) I'd like to have an  
enumerated sequence in a view, but I would not want this to replace  
the rowid that we carry over from a table. We need the rowid from a  
table so we can match rows (eg when the user changes a value in view  
data which we need to redirect back to the corresponding table data).


Tom


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



Re: [sqlite] unique id maximum value limiting

2007-09-03 Thread Babu, Lokesh
#ifdef SQLITE_INT64_TYPE
  typedef SQLITE_INT64_TYPE sqlite_int64;
  typedef unsigned SQLITE_INT64_TYPE sqlite_uint64;
#elif defined(_MSC_VER) || defined(__BORLANDC__)
  typedef __int64 sqlite_int64;
  typedef unsigned __int64 sqlite_uint64;
#else
  typedef long long sqlite_int64;
  typedef unsigned long long int sqlite_uint64;
#endif

Try changing the above statements in your sqlite3.h,
typedef int sqlite_int64;
typedef unsigned int sqlite_uint64, as appropriate for you.

Anybody please correct me if I'm wrong.


On 9/3/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I am working on a 16 bit processor.
> In windows the maximum value of unique id is 2 power 63 -1
>
> I want to restrict the maximum value of the unique id to 2 power 15 -1
>
> Can anyone help me in this?.
>
> Best Regards,
> A.Sreedhar.
>
> Jasmin Infotech Pvt. Ltd.
> Plot 119, Velachery Tambaram Road,
> (Opposite NIOT), Pallikaranai,
> Chennai 601 302
> India
> Tel: +91 44 3061 9600 ext 3057
> Fax: + 91 44 3061 9605
>
>
> ***
> Information in this email is proprietary and Confidential to
> Jasmin Infotech. Any use, copying or dissemination of the
> information in any manner is strictly prohibited. If you are
> not the intended recipient, please destroy the message and please inform us.
>
> 
>
>

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



Re: [sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread Arjen Markus

Serena Lien wrote:


Okay, but even if the rounded result does not have a finite binary
representation, shouldn't the displayed (human readable) representation be
at least truncated to the number of decimal places that were requested in
the round function? Not that I am confusing round with truncate, but surely
it is a more acceptable result?
 

Hm, that is a completely different question. It would mean that more 
information is
associated with the rounded result than merely the number (in internal 
representation).
I do not know enough about the way SQLite organises these things to 
speculate about

that, but it seems like a computational burden to me:

Suppose you have one million records to search for numbers that are then 
rounded. Not
only would you need to compute and store the rounded numbers but also 
the string
representation (or something to effect that you can get a proper decimal 
presentation).


Regards,

Arjen


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



Re: [sqlite] beginner

2007-09-03 Thread Pavan
You should link the library when you compile .It should be gcc
test.c-l
library name should be your sqlite shared library.  Just check in /usr/lib
directory

Thanks,
Pavan.


On 8/31/07, nishit sharma <[EMAIL PROTECTED]> wrote:
>
> hey buddy can u tell me how to compile the C source code in which i have
> used
> sqlite3_open() like calls.
> i m doing gcc test.c but it is giving me error of undefined reference.
> i think i am missing some thing in compiling.
> waiting for reply
>
> regards
> Nishit
>
>
> On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> >
> > thanks for telling the link.
> >
> > regards
> >
> >
> >  On 8/30/07, Pavan <[EMAIL PROTECTED]> wrote:
> > >
> > > Hi Nishit,
> > >
> > > http://www.sqlite.org/quickstart.html
> > >
> > > This is a good link to start with.
> > >
> > > Thanks,
> > > Pavan.
> > >
> > >
> > > On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> > > >
> > > > Hi,
> > > > i m beginner to sqlite
> > > > can anybody send me a link which can help me
> > > > in building and maintining databse
> > > >
> > >
> > >
> > >
> > > --
> > > '
> > > Always finish stronger than you start
> > > *
> > >
> >
> >
>



-- 
'
Always finish stronger than you start
*


Re: [sqlite] why a VIEW doesn't contain a ROWID

2007-09-03 Thread Babu, Lokesh
This I know, but the thing is, I want the ROWID in VIEW to be
sequential even after a SELECT with some condition has been executed,
ie., from 1 to n. Just like in normal table. In your case it is not
like that.

By the way, what I mean to say is, why don't we have default ROWID in
VIEW like as in normal TABLE.

On 9/3/07, T <[EMAIL PROTECTED]> wrote:
> > Why a VIEW doesn't contain a ROWID field. Even though if it is
> > accessed, it contains (null) value. How to initialise this or how
> > to make it work as in TABLE.
>
> To have your view include a rowid, you can include it in the select.
> For instance, if you have a simple table:
>
> create table People
> (
>"First Name" text,
>"Last Name" text,
>Email text,
>Age integer
> )
>
> Then you can specifically include the rowid in a select statement or
> view, such as this simple view based just on that table:
>
> create view Teenagers
> as
> select
>rowid,
>"First Name",
>"Last Name"
> from
>People
> where
>Age >= 13 and Age <= 19
> ;
>
> Or to include all fields, you could:
>
> create view Everyone
> as
> select
>rowid,
>*
> from
>People
> ;
>
> In those cases, the rowid in the view would be the same rowid as the
> matching row in the table.
>
> Tom
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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



Re: [sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread Serena Lien
Okay, but even if the rounded result does not have a finite binary
representation, shouldn't the displayed (human readable) representation be
at least truncated to the number of decimal places that were requested in
the round function? Not that I am confusing round with truncate, but surely
it is a more acceptable result?

thanks, Serena.

On 9/3/07, Arjen Markus <[EMAIL PROTECTED]> wrote:
>
> Serena Lien wrote:
>
> >I have read some of the postings/faq about the difficulties with the
> round
> >function, when certain numbers do not have a finite representation in
> >binary, which SQLite uses. eg 9.95 is actually 9.9499...etc so that
> round(
> >9.95, 1) rounds down.
> >
> >But, I have found several numbers which don't get rounded at all, and in
> >fact return more decimal places!
> >
> >round(98926650.5, 1) -> 98926650.501
> >round(85227887.01, 1) -> 85227887.001
> >
> >Even if these numbers cannot be represented properly in binary, why is it
> >they aren't rounded?
> >
> >thanks for any assistance,
> >Serena
> >
> >
> They are in fact rounded, but the internal binary representation can not
> be turned
> into the appropriate decimal (and human readable) representation due to
> the
> finite precision.
>
> A number like 1.511 can be rounded to 1.5 and that is _exactly_
> representable
> as binary number. But if, as in your examples, the number requires more
> precision
> than is available (remember: only a finite number of digits/bits in
> total, no matter
> what the position of the decimal point/comma), the old problem of
> binary-to-
> decimal conversion kicks in again.
>
> Regards,
>
> Arjen
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>


[sqlite] unique id maximum value limiting

2007-09-03 Thread Sreedhar.a
Hi,
 
I am working on a 16 bit processor.
In windows the maximum value of unique id is 2 power 63 -1 
 
I want to restrict the maximum value of the unique id to 2 power 15 -1
 
Can anyone help me in this?.
 
Best Regards,
A.Sreedhar.
 
Jasmin Infotech Pvt. Ltd.
Plot 119, Velachery Tambaram Road,
(Opposite NIOT), Pallikaranai,
Chennai 601 302
India
Tel: +91 44 3061 9600 ext 3057
Fax: + 91 44 3061 9605 
 

***
Information in this email is proprietary and Confidential to 
Jasmin Infotech. Any use, copying or dissemination of the
information in any manner is strictly prohibited. If you are 
not the intended recipient, please destroy the message and please inform us.
 

 


Re: [sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread Arjen Markus

Serena Lien wrote:


I have read some of the postings/faq about the difficulties with the round
function, when certain numbers do not have a finite representation in
binary, which SQLite uses. eg 9.95 is actually 9.9499...etc so that round(
9.95, 1) rounds down.

But, I have found several numbers which don't get rounded at all, and in
fact return more decimal places!

round(98926650.5, 1) -> 98926650.501
round(85227887.01, 1) -> 85227887.001

Even if these numbers cannot be represented properly in binary, why is it
they aren't rounded?

thanks for any assistance,
Serena
 

They are in fact rounded, but the internal binary representation can not 
be turned

into the appropriate decimal (and human readable) representation due to the
finite precision.

A number like 1.511 can be rounded to 1.5 and that is _exactly_ 
representable
as binary number. But if, as in your examples, the number requires more 
precision
than is available (remember: only a finite number of digits/bits in 
total, no matter

what the position of the decimal point/comma), the old problem of binary-to-
decimal conversion kicks in again.

Regards,

Arjen


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



Re: [sqlite] beginner

2007-09-03 Thread nishit sharma
can anybody tell me which database formats are supported by sqlite3
and the dependencies if i export my database to another system.

regards
Nishit


On 8/31/07, nishit sharma <[EMAIL PROTECTED]> wrote:
>
> hey buddy can u tell me how to compile the C source code in which i have
> used
> sqlite3_open() like calls.
> i m doing gcc test.c but it is giving me error of undefined reference.
> i think i am missing some thing in compiling.
> waiting for reply
>
> regards
> Nishit
>
>
>  On 8/30/07, nishit sharma <[EMAIL PROTECTED]> wrote:
> >
> > thanks for telling the link.
> >
> > regards
> >
> >
> >  On 8/30/07, Pavan <[EMAIL PROTECTED] > wrote:
> > >
> > > Hi Nishit,
> > >
> > > http://www.sqlite.org/quickstart.html
> > >
> > > This is a good link to start with.
> > >
> > > Thanks,
> > > Pavan.
> > >
> > >
> > > On 8/30/07, nishit sharma < [EMAIL PROTECTED]> wrote:
> > > >
> > > > Hi,
> > > > i m beginner to sqlite
> > > > can anybody send me a link which can help me
> > > > in building and maintining databse
> > > >
> > >
> > >
> > >
> > > --
> > > '
> > > Always finish stronger than you start
> > > *
> > >
> >
> >
>


[sqlite] Re: select round(98926650.50001, 1) ?

2007-09-03 Thread Serena Lien
I have read some of the postings/faq about the difficulties with the round
function, when certain numbers do not have a finite representation in
binary, which SQLite uses. eg 9.95 is actually 9.9499...etc so that round(
9.95, 1) rounds down.

But, I have found several numbers which don't get rounded at all, and in
fact return more decimal places!

round(98926650.5, 1) -> 98926650.501
round(85227887.01, 1) -> 85227887.001

Even if these numbers cannot be represented properly in binary, why is it
they aren't rounded?

thanks for any assistance,
Serena


On 8/31/07, Serena Lien <[EMAIL PROTECTED]> wrote:
>
> Hello,
>
> With SQLite 3.3.13, this returns 98926650.501
> Can you explain how I can get the expected rounding/truncation?
>
> thanks.
>


[sqlite] how to iterate on SELECT query results

2007-09-03 Thread Babu, Lokesh
Dear all,

After doing some SELECT operation on a TABLE, say we get 100 result
items out of 1000 records, Is there any way where I can iterate
through this result set. i.e., Get N items out of 100, say get
previous 10, get next 10, etc,

This should be done without creating a temporary table or virtual
tables. As there is overhead of space and time.

If it is possible to use VIEWs then how can I? As I think VIEWs are
little better than temp table or virtual table. Please correct me if
I'm wrong.

please reply, thanks in advance.

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



[sqlite] why a VIEW doesn't contain a ROWID

2007-09-03 Thread Babu, Lokesh
Dear all,

Why a VIEW doesn't contain a ROWID field. Even though if it is
accessed, it contains (null) value. How to initialise this or how to
make it work as in TABLE.

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



[sqlite] Regarding FTS1

2007-09-03 Thread Babu, Lokesh
Dear All,

Does anyone help me out, What is FTS1, How to use FTS1, If any sample
programs to use FTS1 and understand better.

Thanks in advance.

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