[sqlite] why a VIEW doesn't contain a ROWID
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] how to iterate on SELECT query results
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] Re: select round(98926650.50001, 1) ?
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. >
Re: [sqlite] beginner
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 > > > * > > > > > > > >
Re: [sqlite] Re: select round(98926650.50001, 1) ?
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] why a VIEW doesn't contain a ROWID
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] -
[sqlite] unique id maximum value limiting
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) ?
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] > > - > >
Re: [sqlite] why a VIEW doesn't contain a ROWID
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&B <[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] beginner
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] Re: select round(98926650.50001, 1) ?
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] unique id maximum value limiting
#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] why a VIEW doesn't contain a ROWID
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] 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 > * >
RE: [sqlite] (select *) VS (select column1, column2 ...)
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] Re: select round(98926650.50001, 1) ?
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] beginner
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] beginner
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] (select *) VS (select column1, column2 ...)
--- "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&p=summer+activities+for+kids&cs=bz - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: select round(98926650.50001, 1) ?
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&v=1.174 > > thanks, Serena. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] why a VIEW doesn't contain a ROWID
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] how to iterate on SELECT query results
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] remote access to sqlite db?
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] Re: select round(98926650.50001, 1) ?
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] Re: select round(98926650.50001, 1) ?
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] why a VIEW doesn't contain a ROWID
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) ?
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] Re: select round(98926650.50001, 1) ?
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) ?
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] Regarding FTS1
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] -
[sqlite] sqlite versions, binary compatibility
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] sqlite versions, binary compatibility
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] -
Re: [sqlite] why a VIEW doesn't contain a ROWID
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] Re: select round(98926650.50001, 1) ?
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] sqlite versions, binary compatibility
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] sqlite versions, binary compatibility
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] -
[sqlite] BigNameUsers: Nokia using SQLite in PCsuite
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] why a VIEW doesn't contain a ROWID
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]
-
Re: [sqlite] Re: select round(98926650.50001, 1) ?
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] remote access to sqlite db?
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] remote access to sqlite db?
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] Regarding FTS1
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] -
[sqlite] Order by
Hai, I am having 4 distinct Albums in one table. To display all the 4 Albums in sorted order.I used the following statement "select distinct Albums from TableName order by Albums;" To display first 50 Albums in sorted order.I used the following statement "select distinct Albums from TableName order by Albums limit 50 offset 0;" To display last 50 Albums in sorted order.I used the following statement "select distinct Albums from TableName order by Albums limit 50 offset 39950;" I noticed that performance is much slower when we use ORDER BY. I got the output in 3000 msecs without ORDER BY and 7500 msecs with ORDER BY. Is there any other method where i can store the sorted results and use that whenever needed instead f doing order by each time. Best Regards, A.Sreedhar.
