[sqlite] Announcing an extension for fast, constant-memory cardinality approximation

Hi, as I side-project I put together an implementation of Hyperminhash as an extension for SQLite3. It provides fast, constant-memory cardinality approximation, including the union- and intersection-set. For example, querying an in-memory table of two million (INT, INT)-rows, having no indexes,

Re: [sqlite] Row value in predicate doesn't work

Following up, this also doesn't seem to be implemented: SELECT 1 WHERE (1, 1) IS NULL; I think this should be added for completeness' sake of the row value predicate functionality On Wed, Oct 10, 2018 at 3:42 PM Lukas Eder wrote: > According to my understanding of the SQL

[sqlite] Row value in predicate doesn't work

karound is this: SELECT 1 WHERE (1, 1) IN (SELECT 1, 1 UNION ALL SELECT 1, 2); Thanks, Lukas ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Drop Table Behavior

b" causing the exception "Error: no such table: main.a" and what is the meaning of this message? Table b don't references a directly. Best regards, Lukas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Slow debugging

ugger with DLL 3.8.0.2, it need cca 3s inside sqlite3_step. Have anyone some ideas? Thank you! -- Lukas Gebauer. http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib. http://geoget.ararat.cz/ - Geocaching solution ___ sqlite-users mailing list sq

[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 differe

[sqlite] update record in contentless FTS4

Hello all! I have contentless FTS4 index for searching some my external data paired by docid. What I can do, when my existing previously indexed document was changed? I need to update existing FTS4 index too. But documentation says: "UPDATE and DELETE is not supported". Can I do new INSERT w

Re: [sqlite] Transaction issues with WinRT build

tements are fine. It was an issue with WinRT version using the wrong temp directoy by default. A workaround is to change temp dir using a pragma. Hopeflly this will get fixed in the next version. Kind Regards, Lukas ___ sqlite-users mailing list sqlite-

Re: [sqlite] Transaction issues with WinRT build

temp pragma says "Do not use this pragma" :) Kind Regards Lukas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction issues with WinRT build

, already tried disbling that. Kind Regards Lukas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Transaction issues with WinRT build

abase with something about "closed data set". Has anyone tried to use the WinRT version on Windows 8 RC? Kind Regards Lukas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Feature Request: ARM version for WinRT

build file modifications, to get it done completely automatically. If we have the ARM version, then we can deliver apps for WinRT that run on every supported Windows 8 platform, including the low-end ARM tablets. That would be awesome!! What do you think? Lukas

[sqlite] Bug - Aborted statement by a nested savepoint

opened statement - I got SQLITE_ABORT error, even I not see any Rollback. When I omit savepoint x1, then statement working well. If I omit nested x2 savepoint, statement working fine too. Is it bug or I missed something? Thank you very much! -- Lukas Gebauer. http://synapse.ararat.cz

Re: [sqlite] WAL file size

on database disconnect only. On big writes WAL log grows and grows... even I commiting datas, even I have no long transactions, even I have autocheckpointing , even I do checkpointing manually. Just close and reopen database clears WAL log file. Is this correct behavior? Thank you! -- Lukas

[sqlite] reset WAL log

, until I close connection to database. Even I calling "PRAGMA wal_checkpoint(RESTART)" frequently and no concurrent access to database. Can I flush WAL log and reduce log size without close database connection? Thank you! -- Lukas Gebauer. http://synapse.ararat.cz/ - Ararat Synap

[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 wit

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 '2

[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 m

Re: [sqlite] Sizeof tables

Am 18.08.2010 16:09, schrieb Max Vlasov: > On Tue, Aug 17, 2010 at 4:28 PM, Lukas Haase wrote: > >> 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 &g

[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 sqli

Re: [sqlite] List of active savepoints names

But they cannot ask for transaction name? First is useful and many users needs it, and second not? I am surprised. But never mind, I register your response "no"! Forget it, sorry for my bothering. -- Lukas Gebauer. E-mail: gebau...@mlp.cz http://s

Re: [sqlite] List of active savepoints names

ite. It is simple, trivial, useful... and working right at any case. This is why I am asking for. Thanks! -- Lukas Gebauer. E-mail: gebau...@mlp.cz http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib. ___ sqlite-users mailing list sqlite-users

Re: [sqlite] List of active savepoints names

> > I am searching some API function for get list of active savepoint > > names. Is this possible? Thank you! > > No, this is not possible. Why do you need it in the first place? It will be great for debugging, for example. -- Lukas Gebauer. E-mail: gebau...@mlp.cz http:/

Re: [sqlite] List of active savepoints names

duplicate names. Never mind. It not break anything. When I got list of savepoints names sorted in same order as is inside SQlite, then I got exactly what I need. -- Lukas Gebauer. E-mail: gebau...@mlp.cz http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib. __

[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] List of active savepoints names

Hello all! I am searching some API function for get list of active savepoint names. Is this possible? Thank you! -- Lukas Gebauer. E-mail: gebau...@mlp.cz http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib. ___ sqlite-users mailing list

[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 int

Re: [sqlite] FTS and postfix search

two, and > three-character word endings, 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 Me

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

27;*otor' (and others) with almost zero 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 >

Re: [sqlite] FTS and postfix search

eason why I want to use FTS instead of LIKE... Regards, Luke > > Wes > > On Wed, Aug 5, 2009 at 7:47 PM, Lukas Haase 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 g

[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" 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 table >> containing all

Re: [sqlite] queries for a fulltext-engine

Hi, Igor Tandetnik schrieb: > "Lukas Haase" 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 table >> containing all

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 wo

Re: [sqlite] SQLite version 3.6.14

durability and additional memory usage. See > http://www.sqlite.org/asyncvfs.html for additional information. Is this extension compiled in your Win32 DLL binary? Thanks! -- Lukas Gebauer. E-mail: gebau...@mlp.cz http://synapse.ararat.cz/ - Ararat Sy

[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

Re: [sqlite] Is opened transaction?

> http://www.sqlite.org/c3ref/get_autocommit.html Thanks, I missed this function for their little tricky name. Would be gread if exist one new function for getting names from savepoint transaction stack too. It would be great for nested transaction debugging. (Just idea.) -- Lukas Geba

[sqlite] Is opened transaction?

Hello! How can I detect opened transaction on my database connection from the code? When I start transaction/checkpoint, then I am missing some API or SQL command like 'IsTransactionOpen'. Thank you for your help! -- Lukas Gebauer. E-mail: gebau...@mlp.cz http://synapse

[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. Very

[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 data)

Re: [sqlite] Stringcompare operator with COLLATE NOCASE

Igor Tandetnik schrieb: > "Lukas Haase" 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 the same. So I tried:

[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" 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 elem

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

Igor Tandetnik schrieb: > "Lukas Haase" 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 >> control tells

[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. No

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

Kees Nuyt schrieb: > On Fri, 09 Jan 2009 21:16:03 +0100, Lukas Haase > wrote in General Discussion of SQLite > Database : > >> Hello Richard! >> >> Thank you very much!! It works! :-) >> >> >> Indeed. 0-10 milliseconds instead of 500-800 :-) >&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 n

Re: [sqlite] ESCAPE and version dependent documentation

ion 2? Since when is ESCAPE supported? I presume its another version 3 only feature? Correct. ESCAPE was added in version 3.1.0. And there is no alternative way to be able to use literal % and _ in version 2? regards, Lukas

[sqlite] ESCAPE and version dependent documentation

SCAPE supported? I presume its another version 3 only feature? It would be awesome if the documentation would make it clear since what version a given feature is supported. regards, Lukas