Re: [sqlite] Are parenthesis really needed?
--- "Bruno S. Oliveira" <[EMAIL PROTECTED]> wrote: > Is there any sort of expression inside a query that needs to be > surrounded by parenthesis? > > My program will receive queries built to run in MySQL, Oracle and > Postgrees. The problem, as posted before, is that SQLite has problems > with parenthesis around JOINs... So, what I want to do is to remove > every possible parenthesis present in the incoming query. So that my > problem is resolved fast. I assume you're referring to this: http://marc.info/?l=sqlite-users=118737502703454=2 In that specific case, the parens are not needed. But that's not always the case. In general, LEFT OUTER JOIN is not commutative, nor is it associative. You cannot blindy strip the parens or reorder the outer joins out of context. Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more. http://mobile.yahoo.com/go?refer=1GNXIC - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Looking for a cryptographic library
Search for the following on the web: BeeCrypt, CryptoPP, OpenSSL Ray Hurst [EMAIL PROTECTED] wrote: Hi all: I'm writing an application that uses SQLite to store user's data, and need a library to crypt some stuff, including passwords and data. The goal is to crypt before insert and decript after extract tha data, so this last can't be seen by others who gain access to the SQLite dataBase. The application don't need military security level :-) I have been reading about Blowfish, but it seem that it encrypts data in 8-byte blocks, and I suppose that it need pad the data to 8-byte round, who might cause some headache. The ideal is some freeware library although commercial products can also be considered. Of course the final product must be commercially distributable without patent issues. Any advice in this matter would be grateful A.J.Millan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem opening a new SQLite3 database file
On 8/23/07, Dennis Achá <[EMAIL PROTECTED]> wrote: > I cannot open a new SQLite3 database file through the command prompt. In > the > windows "run" window, I type "SQLite3 mydatabase.db3" and I get the > following error > message: <> Using Start Menu/Run is not the most flexible way of running the SQLite command line utility. The error message you are getting is being generated because the directory where you placed the SQLite files is not defined in your PATH variable so they cannot be found from the Run window. To modify the PATH variable in XP: 1. Right click on My Computer. 2. Select 'Properties' 3. Click the 'Advanced' tab. 4. Locate the 'Environment Variables' button and click it. 5. On the bottom half of the screen under 'System Variables' select 'Path' (you may have to scroll down to find the the Path variable). 6. Click the 'Edit' button. 7. Add the directory where you placed the SQLite files (make sure you include a semicolon (;) before the new entry). While not advisable, you can also copy/move the SQLite files to a directory already defined in the PATH variable and running the SQLite3.exe should work. I haven't worked with Win98 in a long time so I'm not sure where the Environment Variables settings are located for that OS.
Re: [sqlite] Are parenthesis really needed?
I don't think so: for example logical operations need to be contained like in a ON or WHERE clause example" WHERE ((a = "X" AND b = "Y") OR z = "W") AND x = "foo" if you remove the parenthesis then it will evaluate differently Bruno S. Oliveira wrote: Hi there, Is there any sort of expression inside a query that needs to be surrounded by parenthesis? My program will receive queries built to run in MySQL, Oracle and Postgrees. The problem, as posted before, is that SQLite has problems with parenthesis around JOINs... So, what I want to do is to remove every possible parenthesis present in the incoming query. So that my problem is resolved fast. Can I do that safely? Thanks in advance. Regards - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Are parenthesis really needed?
Bruno S. Oliveira <[EMAIL PROTECTED]> wrote: Is there any sort of expression inside a query that needs to be surrounded by parenthesis? My program will receive queries built to run in MySQL, Oracle and Postgrees. The problem, as posted before, is that SQLite has problems with parenthesis around JOINs... So, what I want to do is to remove every possible parenthesis present in the incoming query. So that my problem is resolved fast. Can I do that safely? Of course not. Consider select (1+2)*3; Also, nested subqueries require parentheses around them. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Are parenthesis really needed?
Won't most arithmetic expressions such as : SELECT (col1 + col2) * col3 FROM table require parenthesis ? Regards, -- Olivier LE FLOCH On 25 août 07, at 00:01, Bruno S. Oliveira wrote: Hi there, Is there any sort of expression inside a query that needs to be surrounded by parenthesis? My program will receive queries built to run in MySQL, Oracle and Postgrees. The problem, as posted before, is that SQLite has problems with parenthesis around JOINs... So, what I want to do is to remove every possible parenthesis present in the incoming query. So that my problem is resolved fast. Can I do that safely? Thanks in advance. Regards -- /** * Bruno S. Oliveira * Bacharel em Ciência da Computação - UFLA * Mestrando em Inteligência Computacional - UFPR * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */ smime.p7s Description: S/MIME cryptographic signature
[sqlite] Are parenthesis really needed?
Hi there, Is there any sort of expression inside a query that needs to be surrounded by parenthesis? My program will receive queries built to run in MySQL, Oracle and Postgrees. The problem, as posted before, is that SQLite has problems with parenthesis around JOINs... So, what I want to do is to remove every possible parenthesis present in the incoming query. So that my problem is resolved fast. Can I do that safely? Thanks in advance. Regards -- /** * Bruno S. Oliveira * Bacharel em Ciência da Computação - UFLA * Mestrando em Inteligência Computacional - UFPR * http://www.inf.ufpr.br/brunoso/ * * http://www.last.fm/user/bsoliveira/ */
[sqlite] Enumerating rows in a view
Hi all, When I create a table, SQLite enumerates the rows in the rowid column. When I create a view, is there any way to enumerate the output rows? For example, say I have a table: create table Planets( Name text collate nocase ); insert into Planets values( 'Mercury' ); insert into Planets values( 'Venus' ); insert into Planets values( 'Earth' ); insert into Planets values( 'Mars' ); insert into Planets values( 'Jupiter' ); and I have a view that sorts them alphabetically: create view as select Name from Planets order by Name; which gives: Earth Jupiter Mars Mercury Venus How could I give those rows with enumeration: 1 Earth 2 Jupiter 3 Mars 4 Mercury 5 Venus Note that the sort order may be more complex than this, or there may be a "where" filtering. But the result needs to simply number the rows from 1 to n. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS2 suggestion
Porter stemmer is already in there. The main issue with Porter is that it's English only. There is no general game-plan for fuzzy search at this time, though if someone wants to step into the breech, go for it! Even a prototype which demonstrates the concepts and problems but isn't production-ready would be worth something. My current focus for the next generation is international support (this is more of a Google Gears project, but with focus on SQLite so there is likely to be stuff checked in on the SQLite side), and more scalable/manageable indexing. Not a lot of focus on things like quality and recall, mostly because I'm not aware of any major users with enough of an installed baseline to even generate decent metrics. [Basically, solving concrete identified problems rather than looking for ill-defined potential problems.] -scott On 8/24/07, Uma Krishnan <[EMAIL PROTECTED]> wrote: > Would it not be more useful to first implement potter stemmer algorithm, and > then to implement n-gram (as I understand n-gram is for cross column fuzzy > search?). What is the general game plan for FTS3 with regard to fuzzy search? > > Thanks in advance > > "Cesar D. Rodas" <[EMAIL PROTECTED]> wrote: > On 23/08/07, Scott Hess wrote: > > On 8/20/07, Cesar D. Rodas wrote: > > > As I know ( I can be wrong ) SQLite Full Text Search is only match with > > > hole > > > words right? It could not be > > > And also no FT extension to db ( as far I know) is miss spell tolerant, > > > > Yes, fts is matching exactly. There is some primitive support for > > English stemming using the Porter stemmer, but, honestly, it's not > > well-exercised. > > > > > And > > > I've found this Paper that talks about *Using Superimposed Coding Of > > > N-Gram > > > Lists For Efficient Inexact Matching* > > > > http://citeseer.ist.psu.edu/cache/papers/cs/22812/http:zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/william92using.pdf > > > > > > I was reading and it is not so hard to implement, but it cost a extra > > > storage space, but I think the benefits are more. > > > > > > Also following this paper could be done a way to match with fragments of > > > words... what do you think of it? > > > > It's an interesting paper, and I must say that anything which involves > > Bloom Filters automatically draws my attention :-). > > Yeah. I am doing some investigations about that, I love that too. And > I was watching that with n-grams you get a filter to stop common > words, and could be used as a stemming-like algorithm but independent > from the language. > > I was thinking to implement this > http://www.mail-archive.com/sqlite-users%40sqlite.org/msg26923.html > when I finish up some things. What do you think of it? > > > While I think spelling-suggestion might be valuable for fts in the > > longer term, I'm not very enthusiastic about this particular model. > > It seems much more useful in the standard indexing model of building > > the index, manually tweaking it, and then doing a ton of queries > > against it. fts is really fairly constrained, because many use-cases > > are more along the lines of update the index quite a bit, and query it > > only a few times. > > > > Also, I think the concepts in the paper might have very significant > > problems handling Unicode, because the bit vectors will get so very > > large. I may be wrong, sometimes the overlapping-vector approach can > > have surprising relevance depending on the frequency distribution of > > the things in the vector. It would need some experimentation to > > figure that out. > > > > Certainly something to bookmark, though. > > > > Thanks, > > scott > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > > > -- > Cesar D. Rodas > http://www.cesarodas.com/ > Mobile Phone: 595 961 974165 > Phone: 595 21 645590 > [EMAIL PROTECTED] > [EMAIL PROTECTED] > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] R: [sqlite] Disabling tcl bug?
>When trying to install 3.4.2 without tcl (--disable-tcl) it all works >ok during compilation, but when trying to install, the install script >tries to run the tclsh command. > >Is this normal? >Thank you If you're building on Windows, using MSYS, I think it isn't normal, and I wrote a few days ago about that but no one replied. Just scroll back to 20 Aug in the mailing list archive to see my post. Anyway here is the solution that worked for me: 1) configure --disable-tcl 2) edit Makefile changing this two rows (adding ".$(TEXE)" after sqlite3): install: sqlite3.($TEXE) libsqlite3.la sqlite3.h ${HAVE_TCL:1=tcl_install} $(LTINSTALL) sqlite3.($TEXE) $(DESTDIR)$(exec_prefix)/bin 3) make 4) make install 5) have fun Best Paolo Bormida P.S. I think you might wish to run configure with the --prefix option as well, otherwise MSYS will install the binaries and dlls under /usr/bin nistead of the same directory where you unpacked sqlite sources. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] FTS2 suggestion
Would it not be more useful to first implement potter stemmer algorithm, and then to implement n-gram (as I understand n-gram is for cross column fuzzy search?). What is the general game plan for FTS3 with regard to fuzzy search? Thanks in advance "Cesar D. Rodas" <[EMAIL PROTECTED]> wrote: On 23/08/07, Scott Hess wrote: > On 8/20/07, Cesar D. Rodas wrote: > > As I know ( I can be wrong ) SQLite Full Text Search is only match with hole > > words right? It could not be > > And also no FT extension to db ( as far I know) is miss spell tolerant, > > Yes, fts is matching exactly. There is some primitive support for > English stemming using the Porter stemmer, but, honestly, it's not > well-exercised. > > > And > > I've found this Paper that talks about *Using Superimposed Coding Of N-Gram > > Lists For Efficient Inexact Matching* > > http://citeseer.ist.psu.edu/cache/papers/cs/22812/http:zSzzSzwww.novodynamics.comzSztrenklezSzpaperszSzatc92v.pdf/william92using.pdf > > > > I was reading and it is not so hard to implement, but it cost a extra > > storage space, but I think the benefits are more. > > > > Also following this paper could be done a way to match with fragments of > > words... what do you think of it? > > It's an interesting paper, and I must say that anything which involves > Bloom Filters automatically draws my attention :-). Yeah. I am doing some investigations about that, I love that too. And I was watching that with n-grams you get a filter to stop common words, and could be used as a stemming-like algorithm but independent from the language. I was thinking to implement this http://www.mail-archive.com/sqlite-users%40sqlite.org/msg26923.html when I finish up some things. What do you think of it? > While I think spelling-suggestion might be valuable for fts in the > longer term, I'm not very enthusiastic about this particular model. > It seems much more useful in the standard indexing model of building > the index, manually tweaking it, and then doing a ton of queries > against it. fts is really fairly constrained, because many use-cases > are more along the lines of update the index quite a bit, and query it > only a few times. > > Also, I think the concepts in the paper might have very significant > problems handling Unicode, because the bit vectors will get so very > large. I may be wrong, sometimes the overlapping-vector approach can > have surprising relevance depending on the frequency distribution of > the things in the vector. It would need some experimentation to > figure that out. > > Certainly something to bookmark, though. > > Thanks, > scott > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Cesar D. Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Disabling tcl bug?
Hi, When trying to install 3.4.2 without tcl (--disable-tcl) it all works ok during compilation, but when trying to install, the install script tries to run the tclsh command. Is this normal? Thank you -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Update Columns in One Table Using Values From Another Table
Hrmm... I wonder if this would work (complete guess, totally untested) INSERT OR REPLACE INTO core SELECT Core.A, Updates.B, Core.C, Updates.D FROM Core INNER JOIN Updates ON (Core.A = Updates.A) Idea being, I guess, to get the rows that you ultimately want from the sub-select and then use insert or replace to get them into the table. -T > -Original Message- > From: Chris Peachment [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 23, 2007 2:15 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Update Columns in One Table Using > Values From Another Table > > On Thu, 23 Aug 2007 18:58:32 +0200, Kees Nuyt wrote: > > >Hi Chris, > > >On Thu, 23 Aug 2007 12:14:51 -0400, you wrote: > > >>On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: > >> > >>>Chris Peachment wrote: > I have a database with more than 200,000 records in the > core table. An update table of similar record count contains > a proper subset of the core table columns. > > I'm looking for a fast method of merging the values in the > two tables such that : > > 1. core table columns are updated, and > 2. non-existent core records are inserted from the update table. > > >>>Will INSERT OR REPLACE do what you want? > >> > >> > >>>Gerry > >> > >> > >>Regrettably no. When an existing core record is found then it > >>is deleted before the insert. That means that all columns are > >>given new values and not just the ones to be updated. > > >That is exactly what INSERT OR REPLACE does. > > >http://www.sqlite.org/lang_insert.html > >http://www.sqlite.org/lang_conflict.html > > > Sorry for the confusion I introduced. I know the behaviour > of INSERT OR REPLACE is as-described, and that is NOT > what I want. I need to keep the non-updated columns. > > Chris > > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Client/Server approach
You do not need a server to manage 5 clients on the same machine, only if they are on a network. Sreedhar.a wrote: Hi, I am working in sqlite 3.3.6. I want 5 clients to browse at the same time with good performance. I enabled threadsafe and defined SQLITE_ENABLE_MEMORY_MANAGEMENT . I think by using test_server.c we can do 5 clients browsing with single server. Can any one please help me by providing some patch for implementing this. My doubts are: 1.Do I need to create a thread for each client.If so it has to be created before Sqlite3_server_start(). (Or) Some thing like below has to be followed. Main() { sqlite3_client_open() sqlite3_client_prepare() sqlite3_client_step() sqlite3_client_reset() sqlite3_client_finalize() sqlite3_client_close() Sqlite3_server_start(); For starting the server. sqlite3_server_stop(); For closing the server. } Kindly help me to solve this. Thanks in advance Best Regards, A.Sreedhar. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Locking queries
On 8/23/07, Pavan <[EMAIL PROTECTED]> wrote: > As per my understanding I see that SQLite supports only database locking, as > opposed to table/row locking. Does anyone know if you can read from a locked > database ? (i.e. if one application has locked the database for writing, > then can another application read/query it whilst its locked? No, however the blocking time for readers is made as short as possible. See http://sqlite.org/lockingv3.html for the details. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Locking queries
Hi, As per my understanding I see that SQLite supports only database locking, as opposed to table/row locking. Does anyone know if you can read from a locked database ? (i.e. if one application has locked the database for writing, then can another application read/query it whilst its locked? Thanks, Pavan. -- ' Always finish stronger than you start *