[sqlite] Query optimization: Checking for existence before performing action

Hello, I am trying to optimize the SQL calls that my application makes. I have a scenario where words are inserted into a table. Now each word will have a column called confidence. There is a unique primary key on word. When inserting a word, first I check if the words exists by performing a

[sqlite] Should I do analyze?

Hello, I learned about the use of ANALYZE command recently. In my application, SQLIte file is generated once and never modified. Currently my application creates SQLIte database, creates required tables, indexes and inserts records into it. As a last step, it runs VACUUM. I am wondering should I

[sqlite] Query Help

Hi Guys, I have a table named symbols. I am writing the below query. select lower(pattern), id from symbols where value1 = ?1 or value2 = ?1 This returned the following results. chu, 20851 chchu, 20879 cchu, 20907 chu, 20935 From this, I need only distinct patterns. So I tried this query.

Re: [sqlite] Query Help

Hello, Thanks for the help. On Sun, Mar 10, 2013 at 10:36 PM, Igor Tandetnik i...@tandetnik.org wrote: On 3/10/2013 11:06 AM, Navaneeth.K.N wrote: select distinct(lower(pattern)) as pattern, id from symbols where value1 = ?1 or value2 = ?1 group by pattern This returns cchu, 20907

Re: [sqlite] sqlite3.dll no longer operative

On Oct 11, 2012 5:58 PM, L. Dale Rohl dr...@rmccmortgage.com wrote: I am using 64bit Window 7 on my stand alone Toshiba Computer. Recently I lost a Hard Drive and had it replaced. All is well but during activation of the computer a pop-up is on the screen that says that sqlite3.dll has been

[sqlite] Choosing the best query plan

Hello, I have two tables named patterns_content and words. CREATE TABLE patterns_content (pattern text, word_id integer, primary key(pattern, word_id)) CREATE TABLE words (id integer primary key, word text unique, confidence integer default 1, learned integer default 1, learned_on date) Given a

[sqlite] How is this query so fast?

Hello, I have a table which contains a set of words. These are stored in a table called 'patterns_content' with pattern as unique key. There is a FTS4 table named 'patterns' which has 'content=patterns_content' set. It will have only ASCII characters. Something like, ab abd abcd . .

Re: [sqlite] How is this query so fast?

Forgot to add. I got idea for last method from this[1] post. [1] http://sqlite.1065341.n5.nabble.com/fast-string-prefix-matching-td10777.html On Thu, Aug 2, 2012 at 11:36 PM, Navaneeth.K.N navaneet...@gmail.comwrote: Hello, I have a table which contains a set of words. These are stored

Re: [sqlite] EXT : Unknown module FTS4

Hello, On Mon, Jul 30, 2012 at 6:43 PM, Black, Michael (IS) michael.bla...@ngc.com wrote: You've got me totally confusedyou say shared library and dynamically linked but then say it's embedded in the GUI. Which is it? Are you on Unix/Linux? Can you show us your Makefile or an

Re: [sqlite] Handling concurrent read request to SQLite

On Thu, Jul 26, 2012 at 11:37 AM, Simon Slavin slav...@bigfraud.org wrote: Have you set a timeout ? If you haven't the SQLite functions never back off and retry when they find the database locked, they just immediately return an error. Thanks a lot. I didn't know about this feature. I will

[sqlite] Unknown module FTS4

Hello, I have a weird problem. I am working on a shared library, written using C and a GUI application written on C++. GUI application uses the shared library. This shared library uses SQLite amalgamation and links statically. GUI also uses SQLite for some configuration purpose. It is also

[sqlite] Handling concurrent read request to SQLite

Hello, I am writing a shared library which uses SQLite as the file format. This library has got a learning subsystem which can learn a word and all possible ways to type that word. Usually the word will be UTF-8 encoded indic text and patterns will be words with latin characters. I am using the

[sqlite] How to avoid duplicate entries in FTS table?

Hello, I have a table wth the following schema. create virtual table patterns using fts4 (pattern text, id integer) Now, repeating a pattern and id combination is an error to me. There should be always one pattern to id combination. If this was not a virtual table, I'd have solved the problem

Re: [sqlite] How to avoid duplicate entries in FTS table?

Hello, On Sun, Jul 1, 2012 at 2:27 PM, Petite Abeille petite.abei...@gmail.comwrote: On Jul 1, 2012, at 9:11 AM, Navaneeth.K.N wrote: Now, repeating a pattern and id combination is an error to me. There should be always one pattern to id combination. If this was not a virtual table, I'd

[sqlite] Cache all pages

Hello, I have a SQLite database which has got 60 pages. For performance reasons, I am thinking of making SQLite cache all of this 60 pages, so for further queries no disk read will be performed. I believe when all pages are cached, SQLIte just has to read the cached pages and would be faster. To

Re: [sqlite] Cache all pages

Hello, On Wed, May 9, 2012 at 5:15 PM, Simon Slavin slav...@bigfraud.org wrote: Sure. That would cache the data. And then the next thing that needs to be cached might overwrite it all again. You're messing with something that your OS thinks it has sole control over. Are there any API

Re: [sqlite] Problem with binding parameters to LIKE

On Sun, Oct 23, 2011 at 2:21 PM, Baruch Burstein bmburst...@gmail.com wrote: I have done something similar and it worked for me, but there is an issue with indexes you should take into account, as discussed here: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2011-July/031470.html

[sqlite] Problem with binding parameters to LIKE

Hello, I am trying to use parameters in a LIKE query. I have the following code which uses Sqlite C/C++ API. const char *sql = SELECT word FROM words WHERE word LIKE ?1 || '%' ORDER BY freq DESC LIMIT 10;; int rc = sqlite3_prepare_v2 (db, sql, -1, stmt, NULL); if ( rc != SQLITE_OK ) return

[sqlite] Will opening database in ReadOnly mode result in better query time

Hello, I have an application that uses SQLite just for querying. Application will not write anything to the database. So I am wondering will I get a better query time if the database is opened with flag SQLITE_OPEN_READONLY? I am guessing on a read only database, SQLite doesn't have to do any

Re: [sqlite] Will opening database in ReadOnly mode result in better query time

You may have opened the file as read-only, but someone else may open the same file for writing. Thus, your connection still needs to maintain a shared lock, just like any other reader. Thanks. I understand this. But my file will be on a read-only medium. So no other connection opening for

Re: [sqlite] Multiple threads sharing one DB

On Wed, Jan 26, 2011 at 10:24 PM, Ian Hardingham i...@omroth.com wrote: Many thanks Eric. Does a write on Table A block a read/write on Table B? AFAIK, it does. The lock is acquired on the whole file and not on tables. -- -n ___ sqlite-users

[sqlite] Select statement not returning any result

Hello, I have a database with a table named symbols. This has the following schema. CREATE TABLE symbols (type TEXT, pattern TEXT, value1 TEXT, value2 TEXT, children INTEGER); I am using the C API of Sqlite and my application inserts records into the above table. Everything is executed inside a

Re: [sqlite] Select statement not returning any result

Thanks for replying You've probably used the wrong form of quotes somewhere and either your database fields or your SELECT has the n with some form of quote marks around it.  Try using the command-line tool with exactly this request: I tried everything through the command line tool. select

Re: [sqlite] Select statement not returning any result

Hmmm, OK, try: .dump symbols and see what you get. I get the same queries and I am not seeing anything unusual with that. I tried creating another database through the Sqlite command line tool and executed the output got from .dump symbols. In that DB, the selects seems to be working fine.

Re: [sqlite] Select statement not returning any result

Hello, On Sun, Jan 16, 2011 at 9:41 PM, Drake Wilson dr...@begriffli.ch wrote: Quoth Navaneeth.K.N navaneet...@gmail.com, on 2011-01-16 21:31:42 +0530:     rc = sqlite3_bind_text(stmt, 2, tok-pattern, VARNAM_SYMBOL_MAX, NULL);  /* debugged and tok-pattern doesn't have any extra characters

Re: [sqlite] Select statement not returning any result

Ah, just read your new post. Seems you've found the error in code. Good :-) Thanks everyone for the help. I fixed my code and it is working fine. However, I am wondring why the function (sqlite3_bind_text) don't respect NULL character in the string and stop reading when it find one? --

[sqlite] Compilation flags required for amalgamation

Hello, I am trying to understand the compilation flags required to use for compiling the amalgamation copied into my source code directory. I can't use the make files provided with the amalgamation as I am using a different build system using CMake. Currently I am using only SQLITE_THREADSAFE=1 .