[sqlite] Aggregate and query limit
Hi, I have some questions about the behavior of aggregate functions and the result LIMIT. After register TEST function and executing query (SELECT TEST() FROM objects group by id LIMIT 30,3) I was expect calling back TEST() only 3 times but what happened is the TEST() is called 33 time, and the result set is correct 3 rows. My questions: - Why TEST() is called for non-result rows? Is this designed feature? - When the evaluation of the result-column functions happen? - How SQLite preform the LIMIT? Thanks, Mina. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: like operator
RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: I could not under the "/" what is the purpose of it? Like I said - '/' just happens to immediately follow '.' in ASCII table. Thus, 'xxx/' is the smallest string greater than any string of the form 'xxx.yyy', in lexicographical order. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: like operator
Hi,
Thanks for the reply.
I could not under the "/" what is the purpose of it?
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Igor Tandetnik <[EMAIL PROTECTED]>
Date: Friday, August 17, 2007 9:55 am
Subject: [sqlite] Re: like operator
> RaghavendraK 70574
> <[EMAIL PROTECTED]> wrote:
> > we have given a web interface which receive delete request.
> > Now in the req we get "%" and in the delete impl we do this
> > delete from table where itemName like xxx.%;
> >
> > since the key is % the above statement becomes,
> > "delete from table where itemName like %.%";And result in fatal
> > problem of erasing all records.
>
> Try
>
> delete from table
> where itemName >= 'xxx.' and itemName < 'xxx/';
>
> (a slash '/' character happens to come after period '.' in ASCII). Or
>
> delete from table
> where substr(itemName, 1, length('xxx.')) = 'xxx.';
>
> The first query would run much faster than the second if you have
> an
> index on itemName.
>
> Igor Tandetnik
>
>
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Re: like operator
RaghavendraK 70574
<[EMAIL PROTECTED]> wrote:
we have given a web interface which receive delete request.
Now in the req we get "%" and in the delete impl we do this
delete from table where itemName like xxx.%;
since the key is % the above statement becomes,
"delete from table where itemName like %.%";And result in fatal
problem of erasing all records.
Try
delete from table
where itemName >= 'xxx.' and itemName < 'xxx/';
(a slash '/' character happens to come after period '.' in ASCII). Or
delete from table
where substr(itemName, 1, length('xxx.')) = 'xxx.';
The first query would run much faster than the second if you have an
index on itemName.
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] like operator
You have to test your incoming values, and reject requests that have "%" (and other illegal) chars. I never allow real deletes from a web form, and especially not from trusted users. Consider adding a "deleted" column, and update the affected rows to indicate they've been deleted. It's a little more work, but it sounds like it'll save you some pain in the long run. -Clark - Original Message From: RaghavendraK 70574 <[EMAIL PROTECTED]> To: SQLite Sent: Thursday, August 16, 2007 6:02:32 PM Subject: [sqlite] like operator Hi, we have given a web interface which receive delete request. Now in the req we get "%" and in the delete impl we do this delete from table where itemName like xxx.%; since the key is % the above statement becomes, "delete from table where itemName like %.%";And result in fatal problem of erasing all records. Is there any api to deal with like operator for these conditions, pls help. Hopefully fix will not degrade performance. regrds ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] like operator
Hi, we have given a web interface which receive delete request. Now in the req we get "%" and in the delete impl we do this delete from table where itemName like xxx.%; since the key is % the above statement becomes, "delete from table where itemName like %.%";And result in fatal problem of erasing all records. Is there any api to deal with like operator for these conditions, pls help. Hopefully fix will not degrade performance. regrds ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] building 3.4.2 on solaris
I tried to build sqlite 3.4.2 on Solaris 8, 64 bits. My existing gcc setup failed to do it, but I could do it with the Sun compiler 5.3. I configured with: ../sqlite-3.4.2/configure --enable-debug --enable-threadsafe --with-tcl=/usr/local/lib/sparcv9 CFLAGS='-DSQLITE_MEMDEBUG=2 -DSQLITE_TEST=1 -DSQLITE_DEBUG=1 -DSUN_CC=1' CC='cc -xarch=v9' Building encountered 3 problems: 1. tool/lemon.c line 111 redefines B_FALSE and B_TRUE; this conflicts with a similar definition in (one of the) types.h; you may try to define POSIX_C_SOURCE or else XOPEN_SOURCE, but that breaks struct timeval later in the build; it seems the configure should test for the existence of boolean_t or bool_t and if it exists should typedef Boolean to boolean_t; else it should keep the current enum line In the above configure line SUN_CC is my simple way to do that 2. testfixture needs -lrt (for sched_yield) - had to edit the Makefile 3. the linker /usr/ccs/bin/ld does neither understand nor need -64 - had to edit the LD definition in the libtool Then I ran the fulltest and one test failed as follows: printf-8.2... Expected: [2147483647 2147483648 4294967295] . (which is 7FFF 8000 ) Got: [2147483647 18446744071562067968 18446744073709551615] . (which is 7FFF 8000 ) The tcl is 8.4.15 built with the same compiler Does sqlite assume that a long is the same size as an int? Reading the test seems to point that way, but I think I better ask. Do I have to find the compiler flag for that and build that way? please advise, and thank you very much, yours truly, Victor Secarin --- This e-mail, including any attached files, may contain confidential and privileged information for the sole use of the intended recipient. Any review, use, distribution, or disclosure by others is strictly prohibited. If you are not the intended recipient (or authorized to receive information for the intended recipient), please contact the sender by reply e-mail and delete all copies of this message. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite 3.4.0 - problem with prompt
Do you have readline? Rachmel, Nir (Nir) wrote: Hi, I recently upgraded from sqlite 3.2.8 to sqlite 3.4.0. I have only made minor changes in my system to get the new version up and running, however I encountered a problem: when I run sqlite with a database and try to use the 'history' feature of the command-line (pressing the 'up' arrow key to browse through the latest commands executed) the result is some special characters printed to the screen instead of the expected behaviour. I am cross compiling sqlite from an i686 machine to ppc running linux. here are my configure options: ./configure '--build=i686-linux' \ --host=$(CONFIGURE_HOST) \ --prefix=$(PREFIX) \ '--disable-tcl' \ '--disable-debug' \ '--with-gnu-ld' \ '--enable-threadsafe' \ '--enable-releasemode' \ '--enable-static' and I also set this environment variable which I suspect is related: export READLINE_FLAGS='-DHAVE_READLINE=0'; export LIBREADLINE='' (however this worked just find with 3.2.8!!) I would appreciate your help, Thanks, Nir. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLITE_BUSY error in multi-threaded environment
I came across some OS's over the years which implemented file locks as a single global lock. Yours may do that. Mark Brown wrote: Hi John- There is a .lock file for each database. From my understanding, that should prohibit 2 connections from using the same database at the same time. However, that is not the situation I am wondering about. I am specifically wondering if database activity on a connection to DB 1 would have any effect on database activity on a different connection to DB2. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Problem loading extension in Delphi (FTS2)
DISQLite3 does not use SQLite.NET. As I read Sam, he did not say so. He just compared the two to support his argument that "loadable extensions are not required to create custom functions and having access to source is not required for custom functions either". Ralf >I was not aware that DISQLite3 uses SQLite.NET. > >--- "Samuel R. Neff" <[EMAIL PROTECTED]> wrote: >> >> loadable extensions are not required to create custom functions, and having >> access to source is not required for custom functions either. SQLite.NET >> provides very clean support for custom functions written in any .NET >> language and they are loaded automatically by the wrapper from any DLL >> present in the application--they don't have to be added to the SQLite.NET >> codebase. >> >> Sam - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem loading extension in Delphi (FTS2)
Hello Zlatko Matic, >How about Lazarus version of DISQLite3? :) DISQLite3 is Delphi only at the moment, maybe later! ;-) Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
"As recommended, BEGIN IMMEDIATE should prevent thread2 from even starting a transaction if thread1 did so first, however I think this will only work correctly if the same connection handle is used in both, else they still may not know about eachother." Simply not true... If you have different connection handles to the same db.. Then it would be wise to use BEGIN EXCLUSIVE. The reason is that sqlite will acquire an EXCLUSIVE lock, in the file when you use begin EXLCUSIVE. Or it will return a sqlite error sqlite_busy, simply retry... BEGIN IMMEDIATE will acquire a reserved lock. Other uses may still be reading and this lock type must escalate to a PENDING and then to an EXCLUSIVE. While the reserved lock is enabled other users (threads) may perform reads. But they may not perform begin immediate/exlusive etc... Once an exclusive lock is acquired no other user (thread) may access the DB.. Example: THREAD1 THREAD2 sqlite3_prepare sqlite3_step (Step through query) BEGIN EXCLUSIVE -- Loop here on sqlite BUSY. INSERTS -- You should not get any qlite busy here! COMMIT-- Nor should you get sqlite busy here!!! The problem you were facing is that whith a begin immediate sqlite acquires a "RESERVED" lock. This is an intent to write lock. You could still actually get sqlite busy errors during your inserts or commit operations. But the thread wrting thread should eventually be able to acquire the lock and continue as long as the reading threads close off their locks before the writer times out. HTH. Ken
Re: [sqlite] Problem loading extension in Delphi (FTS2)
>I assumed that people would want to load their custom functions from sqlite >extension shared libraries. But if your customers get source code, I suppose >they can work around this. A key feature of DISQLite3 is that it can be fully embedded into applications with minimum footprint only. You can of course also compile DISQLite3 as an external library, be it a regular DLL or a Delphi BPL runtime package. >I prefer to have a separate sqlite3 shared library so it can be customized >and upgraded independently of the host application. Certainly. With DISQLite3 you are free to create your separate libraries and runtime packages just as you need them: With or without wrapper classes, data cache, regular expression support, etc. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DISQLite FTS
>Does DISQLite have its own implementation of FTS, so not using FTS2 at >all? DISQLite3 uses the original full text search modules, adapted to Delphi. Both FTS1 and FTS2 are already compiled in, and can both be used by the same application (like SQLiteSpy does). >Does it use the same mechanism as FTS2 with virtual tables? Yes. >And have you compared speed and functionality to FTS2, Speed is likely to be a little faster than external FTS2, resulting from register calling conventions. DISQLite3's embedded FTS features are identical to external FTS. Just today I uploaded a new version which adds a Unicode Pascal tokenizer written in Delphi which you can use out of the box or as a basis for your own customized tokenizer (see demo). >I guess what it comes down to is to know options available, however I >think the FTS2 project is great and hopes that it continues to grow, as >it can be used on all platforms. FTS in DISQLite3 is cross-platform database file compatabile, just as the entire library. However, if you use custom tokenizers, user functions or collation sequences with your Delphi application you need to replicate them on other platforms. >I guess what might be a problem is that I would not be able to use >DISQLite's FTS implementation in Python or .net for example, or would I? No, this is not a problem. Database files created by DISQLite3 can be read and modified by Python, .net, or any other SQLite3 compatible applications. If in doubt, run some test with SQLiteSpy: It uses DISQLite3 as its build-in SQLite3 implementation. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Sqlite 3.4.0 - problem with prompt
http://www.google.com/ --- "Rachmel, Nir (Nir)" <[EMAIL PROTECTED]> wrote: > Tried without this line - it still doesn't work. > Any ideas? > > Thanks, Nir. > > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 16, 2007 4:16 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Sqlite 3.4.0 - problem with prompt > > You want to enable readline to allow command history editting, not > disable it. > > --- "Rachmel, Nir (Nir)" <[EMAIL PROTECTED]> wrote: > > I recently upgraded from sqlite 3.2.8 to sqlite 3.4.0. > > I have only made minor changes in my system to get the new version up > > and running, however I encountered a problem: > > when I run sqlite with a database and try to use the 'history' feature > > > of the command-line (pressing the 'up' arrow key to browse through the > > > latest commands executed) the result is some special characters > > printed to the screen instead of the expected behaviour. > > > > I am cross compiling sqlite from an i686 machine to ppc running linux. > > > > here are my configure options: > > > > ./configure '--build=i686-linux' \ > > --host=$(CONFIGURE_HOST) \ > > --prefix=$(PREFIX) \ > > '--disable-tcl' \ > > '--disable-debug' \ > > '--with-gnu-ld' \ > > '--enable-threadsafe' \ > > '--enable-releasemode' \ > > '--enable-static' > > > > and I also set this environment variable which I suspect is related: > > export READLINE_FLAGS='-DHAVE_READLINE=0'; export LIBREADLINE='' > > > > (however this worked just find with 3.2.8!!) 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] Problem loading extension in Delphi (FTS2)
I was not aware that DISQLite3 uses SQLite.NET. thanks. --- "Samuel R. Neff" <[EMAIL PROTECTED]> wrote: > > loadable extensions are not required to create custom functions, and having > access to source is not required for custom functions either. SQLite.NET > provides very clean support for custom functions written in any .NET > language and they are loaded automatically by the wrapper from any DLL > present in the application--they don't have to be added to the SQLite.NET > codebase. > > Sam > > > --- > We're Hiring! Seeking a passionate developer to join our team building > products. Position is in the Washington D.C. metro area. If interested > contact [EMAIL PROTECTED] > > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 16, 2007 9:34 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Problem loading extension in Delphi (FTS2) > > --- Ralf Junker <[EMAIL PROTECTED]> wrote: > > >Your product is not useful to a few users like me who require custom > > >sqlite functions for their databases. > > > > I am not sure I understand currectly. Only loadable extensions are > currently omited from > > DISQLite3. > > > > sqlite3_create_function() is very well available in DISQLite3 Pro to > create custom SQL > > functions. > > I assumed that people would want to load their custom functions from sqlite > extension shared libraries. But if your customers get source code, I suppose > they can work around this. > > I prefer to have a separate sqlite3 shared library so it can be customized > and upgraded independently of the host application. Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dump with where clause
Andre du Plessis wrote: HI, how can I use .dump or something similar but specify a where clause, I cant see that the .dump command allows this, Without any arguments it seems to dump the whole db, the only argument supported is the table name, I would like to be able to do something like: .dump table1 where ID > 1000 I don't have a problem with the INSERT into statements, in fact I think I prefer it because the main idea is to extract parts of the db (revisions), And then to be able to rebuild the db in case of corruption... I know there is also the COPY command in SQL I have not really tried it by the documentation it seems to be able to dump the table in comma or tab delimited, but Preferably I don't want to write too much code to do this. Andre, You can use the insert mode in the shell to do what you want. It will format the select output as insert statements. .mode insert select * from table1 where ID > 1000; This doesn't generate the transaction wrapper, or the table's create statement, but you can add those yourself if needed. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem loading extension in Delphi (FTS2)
Ralf, How about Lazarus version of DISQLite3?:) Regards, Zlatko - Original Message - From: "Ralf Junker" <[EMAIL PROTECTED]> To: Sent: Thursday, August 16, 2007 11:54 AM Subject: Re: [sqlite] Problem loading extension in Delphi (FTS2) Hello Joe Wilson, Your product is not useful to a few users like me who require custom sqlite functions for their databases. I am not sure I understand currectly. Only loadable extensions are currently omited from DISQLite3. sqlite3_create_function() is very well available in DISQLite3 Pro to create custom SQL functions. DISQLite3 also includes units with ready-made function extensions: * REGEXP regular expression support provided by DIRegEx in DISQLite3RegExp.pas. * Mathematical utility functions [(acos(), asin(), atan(), atan(), atan2(), ceil(), ceiling(), cos(), cot(), degrees(), exp(), floor(), ln(), log(), log(), log2(), log10(), mod(), pi(), pow(), radians(), sign(), sin(), sqrt(), tan(), truncate()] in DISQLite3Functions.pas. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Problem loading extension in Delphi (FTS2)
loadable extensions are not required to create custom functions, and having access to source is not required for custom functions either. SQLite.NET provides very clean support for custom functions written in any .NET language and they are loaded automatically by the wrapper from any DLL present in the application--they don't have to be added to the SQLite.NET codebase. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Thursday, August 16, 2007 9:34 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Problem loading extension in Delphi (FTS2) --- Ralf Junker <[EMAIL PROTECTED]> wrote: > >Your product is not useful to a few users like me who require custom > >sqlite functions for their databases. > > I am not sure I understand currectly. Only loadable extensions are currently omited from > DISQLite3. > > sqlite3_create_function() is very well available in DISQLite3 Pro to create custom SQL > functions. I assumed that people would want to load their custom functions from sqlite extension shared libraries. But if your customers get source code, I suppose they can work around this. I prefer to have a separate sqlite3 shared library so it can be customized and upgraded independently of the host application. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Sqlite 3.4.0 - problem with prompt
Tried without this line - it still doesn't work. Any ideas? Thanks, Nir. -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Thursday, August 16, 2007 4:16 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Sqlite 3.4.0 - problem with prompt You want to enable readline to allow command history editting, not disable it. --- "Rachmel, Nir (Nir)" <[EMAIL PROTECTED]> wrote: > I recently upgraded from sqlite 3.2.8 to sqlite 3.4.0. > I have only made minor changes in my system to get the new version up > and running, however I encountered a problem: > when I run sqlite with a database and try to use the 'history' feature > of the command-line (pressing the 'up' arrow key to browse through the > latest commands executed) the result is some special characters > printed to the screen instead of the expected behaviour. > > I am cross compiling sqlite from an i686 machine to ppc running linux. > > here are my configure options: > > ./configure '--build=i686-linux' \ > --host=$(CONFIGURE_HOST) \ > --prefix=$(PREFIX) \ > '--disable-tcl' \ > '--disable-debug' \ > '--with-gnu-ld' \ > '--enable-threadsafe' \ > '--enable-releasemode' \ > '--enable-static' > > and I also set this environment variable which I suspect is related: > export READLINE_FLAGS='-DHAVE_READLINE=0'; export LIBREADLINE='' > > (however this worked just find with 3.2.8!!) Luggage? GPS? Comic books? Check out fitting gifts for grads at Yahoo! Search http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unique ids for each record
I assume you'd rather want three separate tables (artist, album, track) with an autoincrementing ID field per table. Your approach would not allow users to own more than 100 albums. regards, Markus Quoting "Sreedhar.a" <[EMAIL PROTECTED]>: Hi, I have a table with 3 columns. Artist Album and tracks. Can i fix a range of ids for each column like 1-100 for Artist 101-200 for Album and 201-300 for tracks So that I can have a unique number(id) for each record. Will there be a problem while deleting and inserting the records? Thanks and best regards, A.Sreedhar. -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with "mhoenicka") http://www.mhoenicka.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem loading extension in Delphi (FTS2)
--- Ralf Junker <[EMAIL PROTECTED]> wrote: > >Your product is not useful to a few users like me who require custom > >sqlite functions for their databases. > > I am not sure I understand currectly. Only loadable extensions are currently > omited from > DISQLite3. > > sqlite3_create_function() is very well available in DISQLite3 Pro to create > custom SQL > functions. I assumed that people would want to load their custom functions from sqlite extension shared libraries. But if your customers get source code, I suppose they can work around this. I prefer to have a separate sqlite3 shared library so it can be customized and upgraded independently of the host application. Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Dump with where clause
That's a very neat little trick, Once again why didn't I think of that :) Thank you very much. -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 16 August 2007 01:52 PM To: SQLite Subject: [sqlite] Re: Dump with where clause Andre du Plessis <[EMAIL PROTECTED]> wrote: > HI, how can I use .dump or something similar but specify a where > clause, I cant see that the .dump command allows this, > > I would like to be able to do something like: > > .dump table1 where ID > 1000 create temp table tmp as select * from table1 where ID > 1000; .dump tmp drop table tmp; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite 3.4.0 - problem with prompt
You want to enable readline to allow command history editting, not disable it. --- "Rachmel, Nir (Nir)" <[EMAIL PROTECTED]> wrote: > I recently upgraded from sqlite 3.2.8 to sqlite 3.4.0. > I have only made minor changes in my system to get the new version up > and running, however I encountered a problem: > when I run sqlite with a database and try to use the 'history' feature > of the command-line (pressing the 'up' arrow key to browse through the > latest commands executed) the result is some special characters printed > to the screen instead of the expected behaviour. > > I am cross compiling sqlite from an i686 machine to ppc running linux. > > here are my configure options: > > ./configure '--build=i686-linux' \ > --host=$(CONFIGURE_HOST) \ > --prefix=$(PREFIX) \ > '--disable-tcl' \ > '--disable-debug' \ > '--with-gnu-ld' \ > '--enable-threadsafe' \ > '--enable-releasemode' \ > '--enable-static' > > and I also set this environment variable which I suspect is related: > export READLINE_FLAGS='-DHAVE_READLINE=0'; export LIBREADLINE='' > > (however this worked just find with 3.2.8!!) Luggage? GPS? Comic books? Check out fitting gifts for grads at Yahoo! Search http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Dump with where clause
Andre du Plessis <[EMAIL PROTECTED]> wrote: HI, how can I use .dump or something similar but specify a where clause, I cant see that the .dump command allows this, I would like to be able to do something like: .dump table1 where ID > 1000 create temp table tmp as select * from table1 where ID > 1000; .dump tmp drop table tmp; Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] DISQLite FTS
>Does DISQLite have its own implementation of FTS, so not using FTS2 at all? FTS1/2 are compiled in DISQLite. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Sqlite 3.4.0 - problem with prompt
Hi, I recently upgraded from sqlite 3.2.8 to sqlite 3.4.0. I have only made minor changes in my system to get the new version up and running, however I encountered a problem: when I run sqlite with a database and try to use the 'history' feature of the command-line (pressing the 'up' arrow key to browse through the latest commands executed) the result is some special characters printed to the screen instead of the expected behaviour. I am cross compiling sqlite from an i686 machine to ppc running linux. here are my configure options: ./configure '--build=i686-linux' \ --host=$(CONFIGURE_HOST) \ --prefix=$(PREFIX) \ '--disable-tcl' \ '--disable-debug' \ '--with-gnu-ld' \ '--enable-threadsafe' \ '--enable-releasemode' \ '--enable-static' and I also set this environment variable which I suspect is related: export READLINE_FLAGS='-DHAVE_READLINE=0'; export LIBREADLINE='' (however this worked just find with 3.2.8!!) I would appreciate your help, Thanks, Nir.
[sqlite] DISQLite FTS
I did not want to highjack the exsiting FTS thread but Ralf, Does DISQLite have its own implementation of FTS, so not using FTS2 at all? Does it use the same mechanism as FTS2 with virtual tables? And have you compared speed and functionality to FTS2, Maybe im actually just looking for some more information on how DISQLite does things. I guess what it comes down to is to know options available, however I think the FTS2 project is great and hopes that it continues to grow, as it can be used on all platforms. I guess what might be a problem is that I would not be able to use DISQLite's FTS implementation in Python or .net for example, or would I? Thanks.
Re: [sqlite] Problem loading extension in Delphi (FTS2)
Hello Joe Wilson, >Your product is not useful to a few users like me who require custom >sqlite functions for their databases. I am not sure I understand currectly. Only loadable extensions are currently omited from DISQLite3. sqlite3_create_function() is very well available in DISQLite3 Pro to create custom SQL functions. DISQLite3 also includes units with ready-made function extensions: * REGEXP regular expression support provided by DIRegEx in DISQLite3RegExp.pas. * Mathematical utility functions [(acos(), asin(), atan(), atan(), atan2(), ceil(), ceiling(), cos(), cot(), degrees(), exp(), floor(), ln(), log(), log(), log2(), log10(), mod(), pi(), pow(), radians(), sign(), sin(), sqrt(), tan(), truncate()] in DISQLite3Functions.pas. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
If they are different files then you should not have any of these problems. -Original Message- From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] Sent: 16 August 2007 11:21 AM To: sqlite-users@sqlite.org Subject: Re: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment hi, Am not clear. Suppose i have 2 databases and a process spwans 2 threads and each thread opne the db will it result in any problem? They are independent files. thx ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Andre du Plessis <[EMAIL PROTECTED]> Date: Thursday, August 16, 2007 4:36 pm Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > Ok well I guess I forgot to mention this is what has made me want to > pull my hair out a few times :) the fact that you have to worry about > both scenarios for two different reasons, if multiple threads are > working with the same connection handle, then SQL will have a better > understanding of the state of your connection and inform you of busy > errors better. If you are using different DB handles what will > happen is > that SQLite may not care that some other thread is busy with another > handle and all will work fine until one point, the connection handle > needs to commit data and enter exclusive mode, it has to get an > exclusive lock on the DB File and no matter that other > connections have > their own handles if they have any locks on the db, sqlite will go > intobusy handler mode and eventually timeout, > depending on how long you wait. If a query keeps a read cursor > open for > some reason inevitably this will result in a database is locked error. > The problem to watch out for is a deadlock, example > > THREAD1 THREAD2 > BEGINBEGIN > INSERT SOME INSERT SOME > COMMIT (busy handler)COMMIT (busy handler) > As you can see thread1 waits for thread2, they will deadlock, and > unlessyou have a limit in your busy handler you will wait forever. > > As recommended, BEGIN IMMEDIATE should prevent thread2 from even > starting a transaction if thread1 did so first, however I think this > will only work correctly if the same connection handle is used in > both,else they still may not know about eachother. > > So yes there is two ways to do this, one is that make sure your busy > handler works properly and then let your applications just try and > thenfail on busy throw the exception and let the application try > again until > all locks are gone, > Or two use a global mutex (IF your application runs in more than one > process space) > Or 3 (use a global critical section - this will be faster) if your > application is just in one process space. > Make sure that inserts/queries finish their business including > begin and > commit transaction in the critical > > If your application ONLY does queries for example you should have NO > problem, > > Additionally if you are using the same DB handle across threads EVERY > CALL to the library no matter what should be (serialized) locked > in a > critical section. > > Ive used these principles that is running fine now, so I will > stick to > this design > > Hope this helps > > -Original Message- > From: Mark Brown [mailto:[EMAIL PROTECTED] > Sent: 15 August 2007 04:34 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > > Hi Andre- > > After rereading your post, I wanted to confirm something. In your > example > below, are thread1 and thread2 connected to the same database, or > different > databases? In my scenario, the threads are connected to different > databases, so I'm not sure if it is the same situation. > > Thanks, > Mark > > > > -Original Message- > > From: Andre du Plessis [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, August 15, 2007 5:05 AM > > To: sqlite-users@sqlite.org > > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded > environment> > > > > Being a newbie to SQLite I've had the same problems working > > with SQLite > > so maybe I can help, > > It does not matter how well your database is synchronized, a common > > pitfall I had was that I would have a query object with an open > cursor> which prevents any other statement from committing to the > database.> > > So for example: > > THREAD1
Re: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
hi, Am not clear. Suppose i have 2 databases and a process spwans 2 threads and each thread opne the db will it result in any problem? They are independent files. thx ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: Andre du Plessis <[EMAIL PROTECTED]> Date: Thursday, August 16, 2007 4:36 pm Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > Ok well I guess I forgot to mention this is what has made me want to > pull my hair out a few times :) the fact that you have to worry about > both scenarios for two different reasons, if multiple threads are > working with the same connection handle, then SQL will have a better > understanding of the state of your connection and inform you of busy > errors better. If you are using different DB handles what will > happen is > that SQLite may not care that some other thread is busy with another > handle and all will work fine until one point, the connection handle > needs to commit data and enter exclusive mode, it has to get an > exclusive lock on the DB File and no matter that other > connections have > their own handles if they have any locks on the db, sqlite will go > intobusy handler mode and eventually timeout, > depending on how long you wait. If a query keeps a read cursor > open for > some reason inevitably this will result in a database is locked error. > The problem to watch out for is a deadlock, example > > THREAD1 THREAD2 > BEGINBEGIN > INSERT SOME INSERT SOME > COMMIT (busy handler)COMMIT (busy handler) > As you can see thread1 waits for thread2, they will deadlock, and > unlessyou have a limit in your busy handler you will wait forever. > > As recommended, BEGIN IMMEDIATE should prevent thread2 from even > starting a transaction if thread1 did so first, however I think this > will only work correctly if the same connection handle is used in > both,else they still may not know about eachother. > > So yes there is two ways to do this, one is that make sure your busy > handler works properly and then let your applications just try and > thenfail on busy throw the exception and let the application try > again until > all locks are gone, > Or two use a global mutex (IF your application runs in more than one > process space) > Or 3 (use a global critical section - this will be faster) if your > application is just in one process space. > Make sure that inserts/queries finish their business including > begin and > commit transaction in the critical > > If your application ONLY does queries for example you should have NO > problem, > > Additionally if you are using the same DB handle across threads EVERY > CALL to the library no matter what should be (serialized) locked > in a > critical section. > > Ive used these principles that is running fine now, so I will > stick to > this design > > Hope this helps > > -Original Message- > From: Mark Brown [mailto:[EMAIL PROTECTED] > Sent: 15 August 2007 04:34 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > > Hi Andre- > > After rereading your post, I wanted to confirm something. In your > example > below, are thread1 and thread2 connected to the same database, or > different > databases? In my scenario, the threads are connected to different > databases, so I'm not sure if it is the same situation. > > Thanks, > Mark > > > > -Original Message- > > From: Andre du Plessis [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, August 15, 2007 5:05 AM > > To: sqlite-users@sqlite.org > > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded > environment> > > > > Being a newbie to SQLite I've had the same problems working > > with SQLite > > so maybe I can help, > > It does not matter how well your database is synchronized, a common > > pitfall I had was that I would have a query object with an open > cursor> which prevents any other statement from committing to the > database.> > > So for example: > > THREAD1 THREAD2 > > LOCK > > QUERY > > UNLOCK LOCK > > (Step through query)BEGIN TRANSACTION > > INSERTS > > COMMIT <- SQLite busy error here > > UNL
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
Ok well I guess I forgot to mention this is what has made me want to pull my hair out a few times :) the fact that you have to worry about both scenarios for two different reasons, if multiple threads are working with the same connection handle, then SQL will have a better understanding of the state of your connection and inform you of busy errors better. If you are using different DB handles what will happen is that SQLite may not care that some other thread is busy with another handle and all will work fine until one point, the connection handle needs to commit data and enter exclusive mode, it has to get an exclusive lock on the DB File and no matter that other connections have their own handles if they have any locks on the db, sqlite will go into busy handler mode and eventually timeout, depending on how long you wait. If a query keeps a read cursor open for some reason inevitably this will result in a database is locked error. The problem to watch out for is a deadlock, example THREAD1 THREAD2 BEGINBEGIN INSERT SOME INSERT SOME COMMIT (busy handler)COMMIT (busy handler) As you can see thread1 waits for thread2, they will deadlock, and unless you have a limit in your busy handler you will wait forever. As recommended, BEGIN IMMEDIATE should prevent thread2 from even starting a transaction if thread1 did so first, however I think this will only work correctly if the same connection handle is used in both, else they still may not know about eachother. So yes there is two ways to do this, one is that make sure your busy handler works properly and then let your applications just try and then fail on busy throw the exception and let the application try again until all locks are gone, Or two use a global mutex (IF your application runs in more than one process space) Or 3 (use a global critical section - this will be faster) if your application is just in one process space. Make sure that inserts/queries finish their business including begin and commit transaction in the critical If your application ONLY does queries for example you should have NO problem, Additionally if you are using the same DB handle across threads EVERY CALL to the library no matter what should be (serialized) locked in a critical section. Ive used these principles that is running fine now, so I will stick to this design Hope this helps -Original Message- From: Mark Brown [mailto:[EMAIL PROTECTED] Sent: 15 August 2007 04:34 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment Hi Andre- After rereading your post, I wanted to confirm something. In your example below, are thread1 and thread2 connected to the same database, or different databases? In my scenario, the threads are connected to different databases, so I'm not sure if it is the same situation. Thanks, Mark > -Original Message- > From: Andre du Plessis [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 15, 2007 5:05 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > > > Being a newbie to SQLite I've had the same problems working > with SQLite > so maybe I can help, > It does not matter how well your database is synchronized, a common > pitfall I had was that I would have a query object with an open cursor > which prevents any other statement from committing to the database. > > So for example: > THREAD1 THREAD2 > LOCK > QUERY > UNLOCK LOCK > (Step through query)BEGIN TRANSACTION > INSERTS > COMMIT <- SQLite busy error here > UNLOCK > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Dump with where clause
HI, how can I use .dump or something similar but specify a where clause, I cant see that the .dump command allows this, Without any arguments it seems to dump the whole db, the only argument supported is the table name, I would like to be able to do something like: .dump table1 where ID > 1000 I don't have a problem with the INSERT into statements, in fact I think I prefer it because the main idea is to extract parts of the db (revisions), And then to be able to rebuild the db in case of corruption... I know there is also the COPY command in SQL I have not really tried it by the documentation it seems to be able to dump the table in comma or tab delimited, but Preferably I don't want to write too much code to do this. Thanks.
