Re: [sqlite] how to cast utf16 text to int?
On 12/5/07, Maxim V. Shiyanovsky <[EMAIL PROTECTED]> wrote: > I have table that holds values of different types in utf16. > I also know value type for the current row. > How should I cast value to compare it with integer? > > This test shows 1 instead of 17 that I expected. > > sqlite> create table t (value text, field_type int); > sqlite> insert into t values (X'31003700', 1); > sqlite> select value from t; > 1 Based on your description, I think you mean you're trying to store a string of Unicode characters, and you're just working with them as UTF-16 in your application. In that case, the problem is that you're trying to use blobs. Use text instead. sqlite3_bind_text16() will let you use UTF-16 in your application, and SQLite will take care of the on-disk encoding and byte order. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
On 12/10/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote: > IBM DB2 9.5 > > select a AS "foo" from t1 union select b from t1 order by foo > SQL0206N "FOO" is not valid in the context where it is used. SQLSTATE=42703 The problem here is with the inconsistent quoting. PostgreSQL uses the opposite case folding as everyone else, hence the behavior difference. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] http://www.sqlite.org/
I am not well versed in english, but this phrase from home page: "This the homepage for SQLite - ...", I think must be rephrased: "This is the homepage for SQLite - ...". Anyway, many thanks for SQLite and its community. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
On 12/10/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > --- [EMAIL PROTECTED] wrote: > > Dennis Cote <[EMAIL PROTECTED]> wrote: > > > [EMAIL PROTECTED] wrote: > > > > > > > > Can you please tell me what > > > > other databases do with this: > > > > > > > >CREATE TABLE t1(a,b,c); > > > >INSERT INTO t1 VALUES(1,2,4); > > > >SELECT a+b AS c FROM t1 WHERE c==4; > > > > > > > > In the WHERE clause, should the "c" resolve to > > > > the column "c" or to the "a+b" expression? I'm > > > > guessing the "a+b" expression. But SQLite is > > > > currently resolving the name to the column "c" > > > > in table t1. Thus SQLite currently answers > > > > "3" to the SELECT statement, when I think it > > > > should give an empty set. Or maybe it should give > > > > an error? > > > > > > > > Opinions, anyone? > > > > > > According to the where clause definition in the SQL:1999 standard the > > > "c" in the where clause should refer to the column in table "t1" which > > > is the result of the preceding from clause. To conform to the standard > > > SQLite should return 3. > > > > I never would have guessed things worked that way. But then > > again, SQL is not noted for making a whole lot of sense. > > I figure if you get agreement between many different databases, they > probably follow the standard. Or is it vice versa? > > It doesn't appear to be possible to use column aliases in the WHERE > clause of postgres and MySQL. So they seem to have interpreted the > standard in the same way. I thought sqlite's useful WHERE clause column > alias extension was common. Perhaps not. > > postgres=> select a AS foo from t1 where foo=1; > ERROR: column "foo" does not exist > postgres=> select a AS foo from t1 where "foo"=1; > ERROR: column "foo" does not exist > postgres=> select a AS "foo" from t1 where "foo"=1; > ERROR: column "foo" does not exist > > mysql> select a AS foo from t9 where foo=1; > ERROR 1054 (42S22): Unknown column 'foo' in 'where clause' > mysql> select a AS foo from t9 where "foo"=1; > Empty set, 1 warning (0.00 sec) > > MySQL seems to interpret the WHERE clause "foo" as the string literal 'foo'. > > But ORDER BY is a different story: > > mysql> select a AS "foo" from t1 union select b from t1 order by foo; > +--+ > | foo | > +--+ > |1 | > |2 | > +--+ > > postgres=> select a AS "foo" from t1 union select b from t1 order by foo; > foo > - >1 >2 > IBM DB2 9.5 select a AS "foo" from t1 union select b from t1 order by foo SQL0206N "FOO" is not valid in the context where it is used. SQLSTATE=42703 > postgres=> select a+b AS "c" from t1 union select b from t1 order by c; > c > --- > 2 > 3 > IBM DB2 9.5 select a+b AS "c" from t1 union select b from t1 order by c SQL0206N "C" is not valid in the context where it is used. SQLSTATE=42703 > Let's add another row to table t1... > > postgres=> insert into t1 values(2, -1000, 5); > INSERT 0 1 > test=> select * from t1; > a | b | c > ---+---+--- > 1 | 2 | 4 > 2 | -1000 | 5 > > postgres=> select a, a+b AS "c" from t1 order by c; > a | c > ---+-- > 2 | -998 > 1 |3 > > > mysql> select * from t1; > +--+---+--+ > | a| b | c| > +--+---+--+ > |1 | 2 |4 | > |2 | -1000 |5 | > +--+---+--+ > > mysql> select a, a+b AS "c" from t1 order by c; > +--+--+ > | a| c| > +--+--+ > |2 | -998 | > |1 |3 | > +--+--+ > > which differs from: > > SQLite version 3.5.1 > sqlite> select * from t1; > a b c > -- -- -- > 1 2 4 > 2 -1000 5 > > sqlite> select a, a+b AS "c" from t1 order by c; > a c > -- -- > 1 3 > 2 -998 > > Which database is correct? > IBM DB2 9.5 select * from t1; a b c -- -- -- 1 2 4 2 -1000 5 select a, a+b AS "c" from t1 order by c; a c -- -- 1 3 2 -998 Robert Wishlaw - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
On 12/8/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > --- Robert Wishlaw <[EMAIL PROTECTED]> wrote: > > > >CREATE TABLE t1(a,b,c); > > > IBM DB2 9.5 > > > > INSERT INTO t1 VALUES(1,2,4); > > SELECT a+b AS c FROM t1 WHERE c=4; > > > > returns > > > > C > > > >3 > >3 > >3 > >3 > >4 record(s) selected > > How could more than one row be returned if t1 only has one row? > t1 has 4 rows. Why does t1 have 4 rows? Blame me. Robert Wishlaw - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Seg fault after calling sqlite3_exec while running under cygwin
On Dec 11, 2007, at 10:37 AM, John Williams wrote: I'm new to using sqlite, so I'm writing a rather simple piece of software for a friend that I would normally just use my own file stucture...but being that I want to learn about sqlite here I am. I should note that since the and program is destined for windows computers I'm doing my development from within cygwin to allow me to quickly and easily move to it's native environment. So (especially after looking at the backtrace below) it's hard for me to be sure if this is a problem with my code, sqlite, or cygwin itself. However I should note that if I compile with an option to use native win32 libraries instead of the cygwin ones...I still seg fault at the same point. When calling sqlite3_exec from within my add record function I am greeted with a seg fault. From viewing a backtrace in gdb I gather the following info: Does the sqlite3 shell work when compiled the same way? Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Regarding explicitly managing SQLite Databases....[Resend]
On Dec 11, 2007, at 8:39 AM, Yuvaraj Athur Raghuvir wrote: Hello, I am trying a simple experiment where I want to limit the size of the file that SQLite uses. Further, I want to manage the growth of the database(s) explicitly. One of the first aspects I want to manage is the size of the file on the disk. I want to set hard limits on the size and during query execution, on failures, explicitly manage the persistence across multiple databases (or disk files). I'm not sure I understand the second part of the question. Does "pragma max_page_count" (http://www.sqlite.org/pragma.html) help any? Dan. (a) Since I am new to SQLite, I would like to hear from the community on how this can be done using what SQLite3 provides. If there are specifics that need to be managed outside the context of SQLite, I am fine with that. However, for doing external management I believe I would need hooks into the basic management of the database. What I would like to know is (b) are such hooks already available? (c) if these need to be implemented, the list of source files I need to look into will help. Thanks for your time, Yuva p.s: I am resending this since I am not sure if it has reached the mailing list. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Seg fault after calling sqlite3_exec while running under cygwin
On Mon, 2007-12-10 at 20:37 -0700, John Williams wrote: > Since I'm not really sure where the problem is I've attached a zip of my c > files. AptAssist.c is my main file and contains the full program. > Temp.cis a simple pull out of the problem function. I didn't get an attachment. Did anyone? JS. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Seg fault after calling sqlite3_exec while running under cygwin
I'm new to using sqlite, so I'm writing a rather simple piece of software for a friend that I would normally just use my own file stucture...but being that I want to learn about sqlite here I am. I should note that since the and program is destined for windows computers I'm doing my development from within cygwin to allow me to quickly and easily move to it's native environment. So (especially after looking at the backtrace below) it's hard for me to be sure if this is a problem with my code, sqlite, or cygwin itself. However I should note that if I compile with an option to use native win32 libraries instead of the cygwin ones...I still seg fault at the same point. When calling sqlite3_exec from within my add record function I am greeted with a seg fault. From viewing a backtrace in gdb I gather the following info: #0 0x77caef22 in ntdll!RtlNumberGenericTableElementsAvl from /cygdrive/c/Windows//syswow64/ #1 0x00457e10 in cygwin_premain3 #2 0x00404724 in sqlite3_mutex_enter #3 0x0043c5ee in sqlite3_exec #4 0x00401936 in addgen addgen is obviously my function...and it is separated by multiple calls from the actual failure point. My _exec call is pretty straightforward: errc = sqlite3_exec(db, sql, NULL, 0, &errmsg). Since I'm not really sure where the problem is I've attached a zip of my c files. AptAssist.c is my main file and contains the full program. Temp.cis a simple pull out of the problem function.
[sqlite] Regarding explicitly managing SQLite Databases....[Resend]
Hello, I am trying a simple experiment where I want to limit the size of the file that SQLite uses. Further, I want to manage the growth of the database(s) explicitly. One of the first aspects I want to manage is the size of the file on the disk. I want to set hard limits on the size and during query execution, on failures, explicitly manage the persistence across multiple databases (or disk files). (a) Since I am new to SQLite, I would like to hear from the community on how this can be done using what SQLite3 provides. If there are specifics that need to be managed outside the context of SQLite, I am fine with that. However, for doing external management I believe I would need hooks into the basic management of the database. What I would like to know is (b) are such hooks already available? (c) if these need to be implemented, the list of source files I need to look into will help. Thanks for your time, Yuva p.s: I am resending this since I am not sure if it has reached the mailing list.
Re: [sqlite] Simple question about optimization
Thanks Dan, your answer it's exactly what i want to know. Thanks again!
On 10/12/2007, Kees Nuyt <[EMAIL PROTECTED]> wrote:
On Mon, 10 Dec 2007 12:22:53 +0100, Clodo <[EMAIL PROTECTED]>
wrote:
In this case:
-
CREATE TABLE test (
Field01 text PRIMARY KEY NOT NULL,
Field02 text
);
insert into test values ('alpha','beta');
update test set Field01='alpha', Field02='gamma';
Under the hood, the UPDATE statement above updates both the
table and index. SQLite does not realize that the index already
contains the correct data.
This:
UPDATE test set Field02='gamma';
does not touch the index.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Can someone explain this error....
On 12/10/07, Tom Shaw <[EMAIL PROTECTED]> wrote: > > I periodically get the following error: > > Error!: SQLSTATE[HY000]: General error: 17 database schema has changed > > However all I am doing is selecting, inserting and updating. How can > those functions change the schema? > Just a guess but are you using the vacuum feature? Found this while searching the archives: http://www.mail-archive.com/sqlite-users@sqlite.org/msg08357.html
[sqlite] Can someone explain this error....
I periodically get the following error: Error!: SQLSTATE[HY000]: General error: 17 database schema has changed However all I am doing is selecting, inserting and updating. How can those functions change the schema? TIA Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: SQLite News
On 12/10/07, Jonathan O <[EMAIL PROTECTED]> wrote: > > http://sqlite.org/news.html has a bad link to http://sqlite.org/download, > instead it should be http://sqlite.org/download.html . The link is under > 3.5.3 news second paragraph first sentence called download.html. > I had trouble trying to find the repo that holds this file. Seems it is now at http://sqlite.org/docsrc/index, but I couldn't figure out how to see the code to edit/diff against to send a patch. Am I doing something wrong?
[sqlite] SQLite News
http://sqlite.org/news.html has a bad link to http://sqlite.org/download, instead it should be http://sqlite.org/download.html. The link is under 3.5.3 news second paragraph first sentence called download.html. Jonathan
Re: [sqlite] Simple question about optimization
On 10/12/2007, Kees Nuyt <[EMAIL PROTECTED]> wrote:
On Mon, 10 Dec 2007 12:22:53 +0100, Clodo <[EMAIL PROTECTED]>
wrote:
In this case:
-
CREATE TABLE test (
Field01 text PRIMARY KEY NOT NULL,
Field02 text
);
insert into test values ('alpha','beta');
update test set Field01='alpha', Field02='gamma';
Under the hood, the UPDATE statement above updates both the
table and index. SQLite does not realize that the index already
contains the correct data.
This:
UPDATE test set Field02='gamma';
does not touch the index.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] search time in FTS3 tables sometimes very long
BTW, http://www.sqlite.org/cvstrac/chngview?cn=4599 is the final fix to this. It's different from the patch I posted. The patch did the job, but felt wrong to me. This has the same performance characteristics, but feels ... well, less wrong. This change should apply cleanly to fts2.c, if anyone is still using fts2. -scott On Dec 5, 2007 2:18 AM, Ingo Godau-Gellert <[EMAIL PROTECTED]> wrote: > Hi Scott! > > You're great! I checked the attached modification and found no search > taking longer than 20s now! It's a great improvement. I didn't find any > other problems, so I will leave the modification in my FTS3 compilation. > > Many thanks! > > Ingo > > > Scott Hess schrieb: > > > 2007/12/4 Scott Hess <[EMAIL PROTECTED]>: > > > >> This seems a little excessive, though. I do see that there's an > >> O(N^2) path in the prefix-searching (loadSegmentLeavesInt()'s call to > >> docListUnion()). I can reasonably make that O(logN), which might help > >> a great deal, if you're hitting it. Not really sure how to tell if > >> you're hitting it, but I'll experiment at my end and see whether I can > >> improve things there. > >> > > > > With the attached patch, the time to match against 't*' with the rfc > > dataset goes from 1m16s to 5s. > > > > It passes the tests, but I'll not guarantee that this is what I'll > > check in. I want to think on it. But let me know if this doesn't > > help. > > > > -scott > > > > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem installing TCL bindings
[EMAIL PROTECTED] wrote: > Zbigniew Baniewski <[EMAIL PROTECTED]> wrote: > > On Sat, Dec 08, 2007 at 08:14:41PM +0530, yahalome wrote: > > > > > Just mkdir /usr/share/tcl8.4/sqlite3 [..] > > > > Perhaps it'll be not enough, when he got such error message: > > > > > symbol: sqlite3StrICmp > > > > It looks like tclsqlite-3.5.3.so is miscompiled. I'll have > to redo it. But right now I have everything taken apart > trying to work on #2822, since that seems to be the bee > in everybody's bonnet. So it will be a while before I can > do another build... > Problem fixed. Please download a fresh copy of tclsqlite3.so and try again. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a way to do comparison of text in a numerical way.
Thanks for the hint. W.Braun Dennis Cote wrote: Mag. Wilhelm Braun wrote: I thought that this might properly a bigger thing. Well, I found a solution which fits my purpose at the moment. ( SELECT txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) ) I do not use selection of max() or min() very often - it seems it is the best suiting solution (effort - result) at the moment. I think you should probably use a query like the following: select txt from test where cast(txt as real) = (select max(cast(txt as real)) from test) Which applies the same cast to each row for the comparison that it applied to each row for the max value determination. This cast may be done implicitly by SQLite, but it is probably safer to make it explicit. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Simple question about optimization
On 10/12/2007, Cesar D. Rodas <[EMAIL PROTECTED]> wrote:
>
> What Kess Nuyt is asking is if you want to replace a column with a data,
> which is the data, will SQLite update it or SQLite is smart enough for
> avoid write the same thing?, very important for keep executing time,
> because write something to HDD is very expensive.
>
> I hope I understand well your question Kees, unfortunately I couldn't
> answer your question, because I don't know, I think Dr. Hipp will be able to
> answer your question :-)
It was Clodo question, sorry :-)
On 10/12/2007, Kees Nuyt <[EMAIL PROTECTED]> wrote:
> >
> > On Mon, 10 Dec 2007 12:22:53 +0100, Clodo <[EMAIL PROTECTED]>
> > wrote:
> >
> > >In this case:
> > >
> > >-
> > >CREATE TABLE test (
> > > Field01 text PRIMARY KEY NOT NULL,
> > > Field02 text
> > >);
> > >
> > >insert into test values ('alpha','beta');
> > >
> > >update test set Field01='alpha', Field02='gamma';
> > >-
> > >
> > >In the "update" statement, i re-set the primary field "Field01" to a
> > >value that field already have.
> >
> > >Sqlite detect this situation and don't update the primary index,
> >
> > SQLite will update the primary key, verifying all constraints
> > (NOT NULL, UNIQUE). The performance impact isn't very big,
> > because the relevant pages will be loaded in the cache anyway.
> >
> > EXPLAIN UPDATE test SET Field01='alpha', Field02='gamma';
> > tells the whole story.
> >
> > >or suppose that developers optimize situation like that?
> >
> > The statement does not do what you seem to need.
> > Usually you want to do:
> > UPDATE test SET Field02='gamma' WHERE Field01='alpha';
> >
> > Without the WHERE clause ALL rows will be updated, which will
> > fail because column Field01 will not be unique anymore.
> >
> > Extend your test set with:
> > insert into test values ('delta','kappa');
> > and rerun your test to see what happens.
> >
> > >P.s. in our program, the "update" statement are generated from a
> > >database-layer, and optimize the statement generation is a big work..
> > >for that i'm trying to understand if will be a biggest optimization or
> > >not..
> > >thanks for feedback!
> >
> > HTH
> > --
> > ( Kees Nuyt
> > )
> > c[_]
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
>
>
> --
> Best Regards
>
> Cesar D. Rodas
> http://www.cesarodas.com
> http://www.thyphp.com
> http://www.phpajax.org
> Phone: +595-961-974165
--
Best Regards
Cesar D. Rodas
http://www.cesarodas.com
http://www.thyphp.com
http://www.phpajax.org
Phone: +595-961-974165
Re: [sqlite] Simple question about optimization
What Kess Nuyt is asking is if you want to replace a column with a data,
which is the data, will SQLite update it or SQLite is smart enough for
avoid write the same thing?, very important for keep executing time,
because write something to HDD is very expensive.
I hope I understand well your question Kees, unfortunately I couldn't
answer your question, because I don't know, I think Dr. Hipp will be able to
answer your question :-)
On 10/12/2007, Kees Nuyt <[EMAIL PROTECTED]> wrote:
>
> On Mon, 10 Dec 2007 12:22:53 +0100, Clodo <[EMAIL PROTECTED]>
> wrote:
>
> >In this case:
> >
> >-
> >CREATE TABLE test (
> > Field01 text PRIMARY KEY NOT NULL,
> > Field02 text
> >);
> >
> >insert into test values ('alpha','beta');
> >
> >update test set Field01='alpha', Field02='gamma';
> >-
> >
> >In the "update" statement, i re-set the primary field "Field01" to a
> >value that field already have.
>
> >Sqlite detect this situation and don't update the primary index,
>
> SQLite will update the primary key, verifying all constraints
> (NOT NULL, UNIQUE). The performance impact isn't very big,
> because the relevant pages will be loaded in the cache anyway.
>
> EXPLAIN UPDATE test SET Field01='alpha', Field02='gamma';
> tells the whole story.
>
> >or suppose that developers optimize situation like that?
>
> The statement does not do what you seem to need.
> Usually you want to do:
> UPDATE test SET Field02='gamma' WHERE Field01='alpha';
>
> Without the WHERE clause ALL rows will be updated, which will
> fail because column Field01 will not be unique anymore.
>
> Extend your test set with:
> insert into test values ('delta','kappa');
> and rerun your test to see what happens.
>
> >P.s. in our program, the "update" statement are generated from a
> >database-layer, and optimize the statement generation is a big work..
> >for that i'm trying to understand if will be a biggest optimization or
> >not..
> >thanks for feedback!
>
> HTH
> --
> ( Kees Nuyt
> )
> c[_]
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
--
Best Regards
Cesar D. Rodas
http://www.cesarodas.com
http://www.thyphp.com
http://www.phpajax.org
Phone: +595-961-974165
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
--- [EMAIL PROTECTED] wrote: > Dennis Cote <[EMAIL PROTECTED]> wrote: > > [EMAIL PROTECTED] wrote: > > > > > > Can you please tell me what > > > other databases do with this: > > > > > >CREATE TABLE t1(a,b,c); > > >INSERT INTO t1 VALUES(1,2,4); > > >SELECT a+b AS c FROM t1 WHERE c==4; > > > > > > In the WHERE clause, should the "c" resolve to > > > the column "c" or to the "a+b" expression? I'm > > > guessing the "a+b" expression. But SQLite is > > > currently resolving the name to the column "c" > > > in table t1. Thus SQLite currently answers > > > "3" to the SELECT statement, when I think it > > > should give an empty set. Or maybe it should give > > > an error? > > > > > > Opinions, anyone? > > > > According to the where clause definition in the SQL:1999 standard the > > "c" in the where clause should refer to the column in table "t1" which > > is the result of the preceding from clause. To conform to the standard > > SQLite should return 3. > > I never would have guessed things worked that way. But then > again, SQL is not noted for making a whole lot of sense. I figure if you get agreement between many different databases, they probably follow the standard. Or is it vice versa? It doesn't appear to be possible to use column aliases in the WHERE clause of postgres and MySQL. So they seem to have interpreted the standard in the same way. I thought sqlite's useful WHERE clause column alias extension was common. Perhaps not. postgres=> select a AS foo from t1 where foo=1; ERROR: column "foo" does not exist postgres=> select a AS foo from t1 where "foo"=1; ERROR: column "foo" does not exist postgres=> select a AS "foo" from t1 where "foo"=1; ERROR: column "foo" does not exist mysql> select a AS foo from t9 where foo=1; ERROR 1054 (42S22): Unknown column 'foo' in 'where clause' mysql> select a AS foo from t9 where "foo"=1; Empty set, 1 warning (0.00 sec) MySQL seems to interpret the WHERE clause "foo" as the string literal 'foo'. But ORDER BY is a different story: mysql> select a AS "foo" from t1 union select b from t1 order by foo; +--+ | foo | +--+ |1 | |2 | +--+ postgres=> select a AS "foo" from t1 union select b from t1 order by foo; foo - 1 2 postgres=> select a+b AS "c" from t1 union select b from t1 order by c; c --- 2 3 Let's add another row to table t1... postgres=> insert into t1 values(2, -1000, 5); INSERT 0 1 test=> select * from t1; a | b | c ---+---+--- 1 | 2 | 4 2 | -1000 | 5 postgres=> select a, a+b AS "c" from t1 order by c; a | c ---+-- 2 | -998 1 |3 mysql> select * from t1; +--+---+--+ | a| b | c| +--+---+--+ |1 | 2 |4 | |2 | -1000 |5 | +--+---+--+ mysql> select a, a+b AS "c" from t1 order by c; +--+--+ | a| c| +--+--+ |2 | -998 | |1 |3 | +--+--+ which differs from: SQLite version 3.5.1 sqlite> select * from t1; a b c -- -- -- 1 2 4 2 -1000 5 sqlite> select a, a+b AS "c" from t1 order by c; a c -- -- 1 3 2 -998 Which database is correct? Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance issue
Stergios Zissakis wrote: My question is: shouldn't sqlite's engine figure out what I am trying to do and sort the tables on the fly in an effort to optimize the query? When using no indexes, a .explain reveals 3 nested loops which take a long time to return results. Any help/ideas will be much appreciated. You should read the optimizer docs at http://www.sqlite.org/optoverview.html for an explanation of why this happens. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a way to do comparison of text in a numerical way.
Mag. Wilhelm Braun wrote: I thought that this might properly a bigger thing. Well, I found a solution which fits my purpose at the moment. ( SELECT txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) ) I do not use selection of max() or min() very often - it seems it is the best suiting solution (effort - result) at the moment. I think you should probably use a query like the following: select txt from test where cast(txt as real) = (select max(cast(txt as real)) from test) Which applies the same cast to each row for the comparison that it applied to each row for the max value determination. This cast may be done implicitly by SQLite, but it is probably safer to make it explicit. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
Dennis Cote <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > > > Can you please tell me what > > other databases do with this: > > > >CREATE TABLE t1(a,b,c); > >INSERT INTO t1 VALUES(1,2,4); > >SELECT a+b AS c FROM t1 WHERE c==4; > > > > In the WHERE clause, should the "c" resolve to > > the column "c" or to the "a+b" expression? I'm > > guessing the "a+b" expression. But SQLite is > > currently resolving the name to the column "c" > > in table t1. Thus SQLite currently answers > > "3" to the SELECT statement, when I think it > > should give an empty set. Or maybe it should give > > an error? > > > > Opinions, anyone? > > > > > > > > According to the where clause definition in the SQL:1999 standard the > "c" in the where clause should refer to the column in table "t1" which > is the result of the preceding from clause. To conform to the standard > SQLite should return 3. > I never would have guessed things worked that way. But then again, SQL is not noted for making a whole lot of sense. So it appears that a bug in my implementation cancelled out a bug in my understanding of SQL. How often does that happen: two bugs cancelling each other out perfectly Dennis: In the developers chatroom, Dan Kennedy is marvelling at your ability to read and decypher standards documents -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [Linux + PHP] Recommended way to access SQLite?
On Mon, 10 Dec 2007 08:36:54 -0400, Chris Peachment
<[EMAIL PROTECTED]> wrote:
> According to the php info() function, on Ubuntu, PHP Version
> 5.2.3-1ubuntu6.2 was released on 3 December 2007 and includes the
> PDO sqlite driver for sqlite version 3.5.2.
>
> It also includes the extension library sqlite driver for version
> 2.8.17 so you have your choice there.
That's the advantage of (some) Linux distributions.
I just updated my Windows Apache/2.2.4 with PHP 5.2.5 (.zip
install, build date Nov 8 2007 23:18:08). Its php_pdo_sqlite is
not very up to date, it appears to use SQLite 3.3.17.
So I activated php_pdo_sqlite_external instead and copied
sqlite3.dll v3.5.3 to %serverroot%/bin .
That works like a charm.
>Personally, I use PDO with sqlite on my local server as a direct
>substitute for PDO with mysql on the remote server. If you use only
>simple sql statements then the only change needed is something like:
>
> define("SERVER_MYSQL", 1);
> define("SERVER_SQLITE", 2);
> define("DATABASE_SERVER", SERVER_SQLITE);
>// define("DATABASE_SERVER", SERVER_MYSQL);
>
>try {
> if (DATABASE_SERVER == SERVER_MYSQL) {
>$dbh = new PDO("mysql:host=localhost;dbname=tasks", "tasks",
>"tasks");
> }
> else {
>$dbh = new PDO('sqlite:tasks.db');
> }
>}
Nice setup.
I still have MySQL v5.0.41 running next to SQLite for a few
third party 'legacy' non-PDO MySQL applications, both on my
development machine, in the production environment, and at home.
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] PATCH: compound query column naming and resolving (Ticket #2822)
[EMAIL PROTECTED] wrote: Can you please tell me what other databases do with this: CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(1,2,4); SELECT a+b AS c FROM t1 WHERE c==4; In the WHERE clause, should the "c" resolve to the column "c" or to the "a+b" expression? I'm guessing the "a+b" expression. But SQLite is currently resolving the name to the column "c" in table t1. Thus SQLite currently answers "3" to the SELECT statement, when I think it should give an empty set. Or maybe it should give an error? Opinions, anyone? Richard, According to the where clause definition in the SQL:1999 standard the "c" in the where clause should refer to the column in table "t1" which is the result of the preceding from clause. To conform to the standard SQLite should return 3. *7.8 * *Function* Specify a table derived by the application of a to the result of the preceding . *Format* ::= WHERE *Syntax Rules* 1) Let /T /be the result of the preceding . Each column reference directly contained in the shall unambiguously reference a column of /T /or be an outer reference. NOTE 98 – /Outer reference /is defined in Subclause 6.6, ‘‘reference>’’. My reading of the outer reference definition is that is applies to triggers and SQL procedures, and correlated subqueries. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Simple question about optimization
On Mon, 10 Dec 2007 12:22:53 +0100, Clodo <[EMAIL PROTECTED]>
wrote:
>In this case:
>
>-
>CREATE TABLE test (
> Field01 text PRIMARY KEY NOT NULL,
> Field02 text
>);
>
>insert into test values ('alpha','beta');
>
>update test set Field01='alpha', Field02='gamma';
>-
>
>In the "update" statement, i re-set the primary field "Field01" to a
>value that field already have.
>Sqlite detect this situation and don't update the primary index,
SQLite will update the primary key, verifying all constraints
(NOT NULL, UNIQUE). The performance impact isn't very big,
because the relevant pages will be loaded in the cache anyway.
EXPLAIN UPDATE test SET Field01='alpha', Field02='gamma';
tells the whole story.
>or suppose that developers optimize situation like that?
The statement does not do what you seem to need.
Usually you want to do:
UPDATE test SET Field02='gamma' WHERE Field01='alpha';
Without the WHERE clause ALL rows will be updated, which will
fail because column Field01 will not be unique anymore.
Extend your test set with:
insert into test values ('delta','kappa');
and rerun your test to see what happens.
>P.s. in our program, the "update" statement are generated from a
>database-layer, and optimize the statement generation is a big work..
>for that i'm trying to understand if will be a biggest optimization or
>not..
>thanks for feedback!
HTH
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: Simple question about optimization
Clodo <[EMAIL PROTECTED]> wrote:
-
CREATE TABLE test (
Field01 text PRIMARY KEY NOT NULL,
Field02 text
);
insert into test values ('alpha','beta');
update test set Field01='alpha', Field02='gamma';
-
In the "update" statement, i re-set the primary field "Field01" to a
value that field already have.
Sqlite detect this situation and don't update the primary index, or
suppose that developers optimize situation like that?
Your update statement is only valid when the table has no more than one
record. Updating or not updating the index consisting of a single entry
is unlikely to make a measurable difference. I don't think the optimizer
goes out of its way to optimize a trivial special case like this.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] [Linux + PHP] Recommended way to access SQLite?
According to the php info() function, on Ubuntu, PHP Version
5.2.3-1ubuntu6.2 was released on 3 December 2007 and includes the
PDO sqlite driver for sqlite version 3.5.2.
It also includes the extension library sqlite driver for version
2.8.17 so you have your choice there.
Personally, I use PDO with sqlite on my local server as a direct
substitute for PDO with mysql on the remote server. If you use only
simple sql statements then the only change needed is something like:
define("SERVER_MYSQL", 1);
define("SERVER_SQLITE", 2);
define("DATABASE_SERVER", SERVER_SQLITE);
// define("DATABASE_SERVER", SERVER_MYSQL);
try {
if (DATABASE_SERVER == SERVER_MYSQL) {
$dbh = new PDO("mysql:host=localhost;dbname=tasks", "tasks",
"tasks");
}
else {
$dbh = new PDO('sqlite:tasks.db');
}
}
On Mon, 2007-12-10 at 02:07 +0100, Kees Nuyt wrote:
> On Sun, 9 Dec 2007 23:34:44 +0100, DJ Anubis
> <[EMAIL PROTECTED]> wrote:
>
> >Le dimanche 9 décembre 2007, Gilles Ganault a écrit :
> >> It seems like I have two options:
> >> - calling the SQLite library
> >> - going through the PDO interface, and its SQLite module.
> >>
> >> Which of the two would you recomend? Are there other options I
> >> should know about?
> >
> >I would recommend using PDO interface, as this is the standard
> >PHP5 API.
>
> I agree. My experience with php_pdo_sqlite is positive, and I
> think it is the easiest way to use sqlite3 in PHP.
>
> I didn't try php_pdo_sqlite_external yet, it seems to call a
> self-supplied sqlite3.dll, so one would be able to use the
> latest SQLite3 version.
>
> >Don't worry about PHP4, as this old version will no more be
> >supported soon...
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Simple question about optimization
In this case:
-
CREATE TABLE test (
Field01 text PRIMARY KEY NOT NULL,
Field02 text
);
insert into test values ('alpha','beta');
update test set Field01='alpha', Field02='gamma';
-
In the "update" statement, i re-set the primary field "Field01" to a
value that field already have.
Sqlite detect this situation and don't update the primary index, or
suppose that developers optimize situation like that?
P.s. in our program, the "update" statement are generated from a
database-layer, and optimize the statement generation is a big work..
for that i'm trying to understand if will be a biggest optimization or
not..
thanks for feedback!
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Explicitly managing multiple SQLite databases.....
Hello, I am trying a simple experiment where I want to limit the size of the file that SQLite uses. Further, I want to manage the growth of the database(s) explicitly. One of the first aspects I want to manage is the size of the file on the disk. I want to set hard limits on the size and during query execution, on failures, explicitly manage the persistence across multiple databases (or disk files). (a) Since I am new to SQLite, I would like to hear from the community on how this can be done using what SQLite3 provides. If there are specifics that need to be managed outside the context of SQLite, I am fine with that. However, for doing external management I believe I would need hooks into the basic management of the database. What I would like to know is (b) are such hooks already available? (c) if these need to be implemented, the list of source files I need to look into will help. Thanks for your time, Yuva
[sqlite] Performance issue
Hello to everyone, This is my first post in the list I've got the following 3 tables: CREATE TABLE A ( int1 INTEGER, txt1 TEXT, int2 INTEGER, txt2 TEXT, PRIMARY KEY ( txt1 ) ); CREATE TABLE B ( txt1 TEXT, int1 INTEGER ); CREATE TABLE C ( txt1 TEXT, int1 INTEGER ); Each table contains 1000 rows. The following query takes about 7 minutes to return results without using any index apart from the table A's primary key: select count(*) from A INNER JOIN Bon (A.txt1 = B.txt1) INNER JOIN C on (b.txt1 = C.txt1); If I index columns A.txt1 kai B.txt1, the time gets reduced to milliseconds. My question is: shouldn't sqlite's engine figure out what I am trying to do and sort the tables on the fly in an effort to optimize the query? When using no indexes, a .explain reveals 3 nested loops which take a long time to return results. Any help/ideas will be much appreciated. Thanks for your time. Kind Regards, Stergios Zissakis (aka Sterge) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem using mem1.c/mem3.c with v3.5.3
Hello Jang, Monday, December 10, 2007, 1:39:02 AM, you wrote: JJ> Hello, JJ> JJ> Now I'm using SQLite v3.4.2 in my Mobile Device(ARM9-200Mhz, 8MB SRAM) JJ> and it works fine. JJ> JJ> But when I updated to SQLitev3.5.3 this time I am facing problem. JJ> JJ> Please let me know why it is happening. JJ> JJ> This is my test source in MS's VisualC++ 6.0 (Console application). JJ> JJ> http://cfs7.blog.daum.net/upload_control/download.blog?fhandle=MElCMXRAZ JJ> nM3LmJsb2cuZGF1bS5uZXQ6L0lNQUdFLzAvMC56aXA=&filename=0.zip&filename=SQL3 JJ> 53_VC6.zip JJ> Problem 1. JJ> when I use "mem1.c" and use "Order By", it require more memory than I JJ> expected. JJ> It seems has same problem when I use "mem3.c". I want use less than 1MB. JJ> So I set "SQLITE_DEFAULT_CACHE_SIZE=800" and JJ> "SQLITE_DEFAULT_TEMP_CACHE_SIZE=200". JJ> JJ> Problem 2. JJ> When I use "mem3.c" and set "SQLITE_MEMORY_SIZE = 1024000", "insert" was JJ> failed. JJ> JJ> Please help am I setting ANY WRONG PARAMETERS. JJ> JJ> Best Regards, JJ> Jang JJ> I've run into the same problem. Mem3.c doesn't fail very gracefully. SQL just stops working with an out of memory error when you're out and I haven't figured a good way to estimate how much memory I might need. It would be nice if you could set some high water mark but, still exceed it when needed and the pool coult free up whatever memory exceeds the water mark. On the flip side, when it's working it's exceptionally fast. -- Best regards, Tegmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Huge performance drop when using prepared statement
On 10/12/2007, Ed Pasma <[EMAIL PROTECTED]> wrote:
> Hello, think I got it, but it is disappointingly simple, see below. Ed.
>
> Markus Gritsch wrote:
>
> > Even more strange:
> >
> > c.execute("""SELECT * FROM entry, word, word_entry WHERE
> > entry.id = word_entry.entry_id AND
> > word.id = word_entry.word_id AND
> > word.word GLOB ?
> > """, ('tes*',))
> >
> > takes less than 1ms but
> >
> > c.execute("""SELECT * FROM entry, word, word_entry WHERE
> > entry.id = word_entry.entry_id AND
> > word.id = word_entry.word_id AND
> > word.word GLOB ?
> > """, ('test',))
> >
> > takes several hundred ms.
>
> The execute in Python includes prepare (or get from cache), bind and
> the first step.
> The answer must be that the wait time lies in the first step.
> The engine is doing a full scan and it all depends how far in the
> table it needs to go to find the first match.
> So the bind values with * just come across a match sooner.
Yes, I think your explanation is correct. Thank you. If I actually
fetch all results by calling c.fetchall() after issuing the queries,
both of them take several hundret ms.
Thank you again,
Markus
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
