[sqlite] Parallel accessing sqlite files in Win

Hi, I use sqlite3_open_v2 with flag SQLITE_OPEN_READONLY to open an SQLite database. When different processes access the same file (read-only) in Win, can I be sure that there won't be any problems? Furthermore, I open another database with SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE. When

[sqlite] FTS3: Combining match-column, prefix query and phrase query

Hi, Is it somehow possible to combine these three things? (1) match column: ... MATCH 'column:foo' (2) prefix query: ... MATCH 'foo*' (3) phrase query: ... MATCH '"foo bar"' I think (1) and (2) is no problem. For example if I want to search all documents containing words beginning OR ending

Re: [sqlite] FTS3 finds too much: Slash special meaning? Something else?

Am 03.09.2010 13:27, schrieb Dan Kennedy: > > On Sep 2, 2010, at 6:37 PM, Lukas Haase wrote: > >> Hi, >> >> I use FTS3 (SQLITE_ENABLE_FTS3) with enhanced query syntax >> (SQLITE_ENABLE_FTS3_PARENTHESIS). >> >> Now if I search for a string like '20

[sqlite] FTS3 finds too much: Slash special meaning? Something else?

Hi, I use FTS3 (SQLITE_ENABLE_FTS3) with enhanced query syntax (SQLITE_ENABLE_FTS3_PARENTHESIS). Now if I search for a string like '2002/91/AH' there are lots of items which do NOT contain this string. This is a query: SELECT rowid, content FROM fulltext WHERE content MATCH '2002/91/AH'; In

Re: [sqlite] Sizeof tables

Am 18.08.2010 16:09, schrieb Max Vlasov: > On Tue, Aug 17, 2010 at 4:28 PM, Lukas Haase<lukasha...@gmx.at> wrote: > >> Hi, >> >> My sqlite database is about 65 MB. The data is split into serval tables. >> >> Is there a way to enumerate the space requir

[sqlite] Sizeof tables

Hi, My sqlite database is about 65 MB. The data is split into serval tables. Is there a way to enumerate the space requirements for each table so that I can see which tables are the memory consumers? Regards, Luke ___ sqlite-users mailing list

[sqlite] Understanding ICU collations: Where needed?

Hi, I have a database which is built on a Linux System with PHP and pdo_sqlite and used (read-only) on a Windows platform. When I use the ICU module and I create a collation, where is the collation "stored"? Do I have to call icu_create_collation everytime I start up the database? Or just one

[sqlite] Loading FTS3 as module: SQL error: error during initialization:

Hi, I want to load FTS3 dynamically as a module. I compile it from the source with: gcc -c fts3.c gcc -c fts3_expr.c gcc -c fts3_hash.c gcc -c fts3_icu.c gcc -c fts3_porter.c gcc -c fts3_tokenizer1.c gcc -c fts3_tokenizer.c gcc -shared fts3_expr.o fts3_hash.o fts3_icu.o fts3.o fts3_porter.o \

Re: [sqlite] Compile sqlite with only ANSI support (no Unicode)

Singaravelu, Rajaram schrieb: > Hi, > > [...] > > In short, can anyone tell me if I can compile sqlite3 with only ANSI > support so that it works like the ANSI version of fopen(). If I understand you correctly you just care about the filename when you talk about UTF8?! In this case everything

[sqlite] Extending FTS

Hi, Thank you anybody for your replies and ideas to "FTS and postfix search". I thought a lot about it and came to the conclusion: In general it is not necessary for a fulltext system not find subwords. If it would be, then I either need no index (search through whole data) or put subwords

Re: [sqlite] FTS and postfix search

ngs, and join to it from all of your words > (stored in forward order). Then search first for the primary key of > the word ending you want to search for, then search your words for > that key. > > Index the join. > > - Original Message - > From: "Lukas Haas

Re: [sqlite] FTS and postfix search

Roger Binns schrieb: > [...] > > Lukas Haase wrote: >> additionally I would also accept the index being bigger. > > You could have a second FTS table where you store the keywords in reverse > order :-) Hi, DAMN!! Damn, damn. Thank you for the great idea. But unfortun

Re: [sqlite] FTS and postfix search

CPU and time consumption. I'd be curious how they did this. For a solution for SQLite I would accept a small performance penalty in that case (but very few secs max); additionally I would also accept the index being bigger. Regards, Luke > Wes > > On Wed, Aug 5, 2009 at 8:58 PM, Lukas Haase<

Re: [sqlite] FTS and postfix search

use FTS instead of LIKE... Regards, Luke > > Wes > > On Wed, Aug 5, 2009 at 7:47 PM, Lukas Haase<lukasha...@gmx.at> wrote: >> Hi, >> >> It's me again, sorry. The next big problem concerning FTS. I have the >> requirement to do postfix searches, like:

[sqlite] FTS and postfix search

Hi, It's me again, sorry. The next big problem concerning FTS. I have the requirement to do postfix searches, like: SELECT topic_title FROM topics WHERE topic MATCH '*otor' ORDER BY topic_title ASC; should find Motor, motor, Monotor etc. But this does not seem to work. Is there any chance to

[sqlite] virtual tables may not be indexed

Hi list, I have a huge problem: A database with 2 HTML fragements should contain a fulltext index. For that reason I put all data into a virtual table: CREATE VIRTUAL TABLE topics USING fts3( topicID INTEGER, topic_title VARCHAR(200) COLLATE NOCASE, topic TEXT,

Re: [sqlite] queries for a fulltext-engine

Igor Tandetnik schrieb: > "Lukas Haase" <lukasha...@gmx.at> wrote in > message news:gv9fcm$5r...@ger.gmane.org >> I have a database containing thousands of HTML pages ("topics"). There >> is a fulltext index for these topics. First there is a tab

Re: [sqlite] queries for a fulltext-engine

Hi, Igor Tandetnik schrieb: > "Lukas Haase" <lukasha...@gmx.at> wrote in > message news:gv9fcm$5r...@ger.gmane.org >> I have a database containing thousands of HTML pages ("topics"). There >> is a fulltext index for these topics. First there is a tab

Re: [sqlite] queries for a fulltext-engine

Hi, At first, thank you for your answer! Simon Slavin schrieb: > On 23 May 2009, at 7:30pm, Lukas Haase wrote: >> SELECT topic_fulltext.topicID >> FROM fulltext >> JOIN topic_fulltext ON topic_fulltext.fulltextID = fulltext.fulltextID >> WHERE word LIKE 'Word%'; >

[sqlite] queries for a fulltext-engine

Hi, Sorry for the subject - I just do not know for what to search of what to ask - I actually do not know where is exactly my problem :-( I have a database containing thousands of HTML pages ("topics"). There is a fulltext index for these topics. First there is a table containing all single

[sqlite] Sort order with umlauts

Hello, For a database I also need a fulltext index. This is my table containing all the words: CREATE TABLE db_fulltext.fulltext( fulltextID INTEGER PRIMARY KEY, word VARCHAR(100) COLLATE NOCASE ); Now I have the problem that I have also words with umlauts. Now they are sorted

[sqlite] Fast and simple database merge

Hi, For an application I use SQLite as datafile. I have written a "compiler" (script chain in Linux) for creating my database file. There are dependencies between tables and "compiling" my single database takes about 1-2 hours. When there is an error I have to restart the whole procedure.

[sqlite] onthefly Compression of SQLite database

Hi, I searched a little bit with google but I could not find any code or extensions to compress an SQLite database on the fly. Is this possible? I use SQLite to store a database engine with images and HTML code. I already use zlib to compress the data itself (i.e. the images and the HTML

Re: [sqlite] Stringcompare operator with COLLATE NOCASE

Igor Tandetnik schrieb: > "Lukas Haase" <lukasha...@gmx.at> wrote in > message news:gkd3f4$cl...@ger.gmane.org >> This is not what I want. So I use COLLATE NOCASE: >> >> This is the result I want. However, now I need a WHERE clause to work >> exactly t

[sqlite] Stringcompare operator with COLLATE NOCASE

Hello, I have a table containting keywords: CREATE TABLE keywords( keywordID INTEGER PRIMARY KEY, keyword VARCHAR(100) ); INSERT INTO keywords VALUES(1,'Apple'); INSERT INTO keywords VALUES(2,'apple'); INSERT INTO keywords VALUES(3,'Angle'); INSERT INTO keywords VALUES(4,'Tree');

Re: [sqlite] Getting the "position" (like LIMIT) for a query

Lukas Haase schrieb: > Igor Tandetnik schrieb: >> "Lukas Haase" <lukasha...@gmx.at> wrote in >> message news:gkat07$n2...@ger.gmane.org >>> I use an SQLite database to fill a virtual list control in Windows. In >>> this control, I just tell the con

Re: [sqlite] Getting the "position" (like LIMIT) for a query

Igor Tandetnik schrieb: > "Lukas Haase" <lukasha...@gmx.at> wrote in > message news:gkat07$n2...@ger.gmane.org >> I use an SQLite database to fill a virtual list control in Windows. In >> this control, I just tell the control the numer of my elements and the &

[sqlite] Getting the "position" (like LIMIT) for a query

Hello, I use an SQLite database to fill a virtual list control in Windows. In this control, I just tell the control the numer of my elements and the control tells me for which range data is needed. For example I set: SELECT COUNT(*) FROM keywords; to tell the control the numer of elements.

Re: [sqlite] 600ms for simple query: How to optimize it?

Kees Nuyt schrieb: > On Fri, 09 Jan 2009 21:16:03 +0100, Lukas Haase > <lukasha...@gmx.at> wrote in General Discussion of SQLite > Database <sqlite-users@sqlite.org>: > >> Hello Richard! >> >> Thank you very much!! It works! :-) >> &g

Re: [sqlite] 600ms for simple query: How to optimize it?

D. Richard Hipp schrieb: > On Jan 9, 2009, at 3:16 PM, Lukas Haase wrote: >> SELECT t.topic, t.length >> FROM printgroup AS pg1 >> LEFT JOIN printgroup AS pg2 ON pg1.printgroup = pg2.printgroup >> LEFT JOIN topics AS t ON t.topicID = pg2.topicID >> LEFT JOIN to

Re: [sqlite] 600ms for simple query: How to optimize it?

Hello Richard! Thank you very much!! It works! :-) D. Richard Hipp schrieb: > On Jan 7, 2009, at 6:11 PM, Lukas Haase wrote: > >> Hello, >> >> Can somebody tell me why this (simple) query take so much time? This >> query does nothing more than querying a table a

[sqlite] 600ms for simple query: How to optimize it?

Hello, Can somebody tell me why this (simple) query take so much time? This query does nothing more than querying a table and JOINing two other tables together. SELECT ti1.topicID AS topicID, ti2.topic_textID AS parent, n.level, n.level_order FROM navigation AS