Re: [sqlite] Finding second occurrence of character in string

Hi, One possible way could be to combine this with FTS4 (with parenthesis support enabled) and a LIKE clause: SELECT substr(path, 4+instr(substr(path,4),'/‚)) as relativepath FROM table WHERE table MATCH "path:ab AND path:cd“ AND path LIKE "ab/cd%“ How it works: - The match clause efficiently

Re: [sqlite] Finding second occurrence of character in string

Sorry, this should rather be something like sqlite3_mprintf("%q", "Path-_1/path%2/path3_³); https://www.sqlite.org/c3ref/mprintf.html Am 26.10.14 14:57 schrieb "Stadin, Benjamin" unter <benjamin.sta...@heidelberg-mobil.com>: >char *zSQL = sqlite3_mpri

[sqlite] FTS5 compression

Hi, What kind of compression is used with the experimental FTS5? I¹d be interested to hear about real world comparisons in regards to compaction and performance. Ben ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?

I’m discussing with my company if I’m allowed to open source our "option D“. It was written for a certain use-case (to provide updates of large dbs on mobile phones for our own apps), therefore it is not entirely generic. It works like this: - Every table in the db needs a column „md5checksum“,

Re: [sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?

This should read: In our apps we required a full db download, in case the db schemes did not match. Am 25.05.16, 19:49 schrieb "sqlite-users-boun...@mailinglists.sqlite.org on behalf of Stadin, Benjamin" unter <sqlite-users-boun...@mailinglists.sqlite.org on behalf of benjamin.sta.

[sqlite] Problem using ICU

I¹m having problems to return results from a FTS4 table using ICU. I¹m doing the usual routine to load an ICU collation: 1) Directly after opening the DB: SELECT icu_load_collation('de_DE', 'LOCALIZED'); 2) Creating some table CREATE VIRTUAL TABLE ¹sometable' USING fts4 (tokenize='icu'

Re: [sqlite] Problem using ICU

Answering to my own question. After some lengthy debugging session I finally figured that it’s a problem with the ICU library which I build with SQLite for iOS. The problem was related to ICU’s obscure loading mechanism. Ben Am 03.04.14 19:37 schrieb "Stadin, Benjamin" unter <

[sqlite] Bug with FTS3 parenthesis and ICU

It seems like that creating a virtual FTS3 table with ICU tokenizer breaks FTS3 parenthesis. Example: SQLITE_ENABLE_FTS3_PARENTHESIS SQLite is of course built with SQLITE_ENABLE_FTS3_PARENTHESIS, ICU, etc. I compile SQLite with ICU for the iPhone with the following flags: . Though I think it

[sqlite] Bug with FTS3 parenthesis and ICU (2)

(Please disregard my first email. I hit the wrong button and sent the email to early.) It seems that creating a virtual FTS3 table with ICU tokenizer breaks FTS3 parenthesis. Example: — Create test table participant CREATE VIRTUAL TABLE 'participant' USING fts4 (tokenize=icu de_DE, firstName,

[sqlite] FTS3/4 bug with tokenize=icu and parentheses

A few days ago I suspected a bug with SQLite FTS4 parenthesis when using the ICU tokenizer. To rule out it was my slightly altered SQLite build environment to compile SQLite + ICU for the iPhone, I reproduced this issue today with the current version of SQLite on OS X. Steps to reproduce: SQLite

Re: [sqlite] Bug with FTS3 parenthesis and ICU (2)

Hi David, I overlooked your email at first. Thanks for your analysis. I thought that doing the quick fix wasn¹t enough for my purpose. So I replaced the tokenization part icuNext(). I hope backwards compatibility will not be an issue for this fix. Regards Ben static int icuNext(

Re: [sqlite] FTS3/4 merge function behaviour when deleting rows

Is the delete marker also set on old keys on UPDATE? Or just DELETE -> INSERT? I ran into the ever-growing FTS index issue last year. I’m creating DB diffs which also contain some FTS3/4 tables. The tables get constantly updated for the checksum. The DBs were always vacuum’ed, but the growing FTS

Re: [sqlite] Unicode61 Tokenizer

I use SQLite with some custom extensions + ICU (+ some small additions to enable loading minimized ICU dat files) since almost 3 years in all our companies' iOS projects. I came across a posting once on StackOverflow with somebody saying he got a rejection - but this was never the case for us and

Re: [sqlite] Unicode61 Tokenizer

You could create a fake framework, because it takes a while every time to compile. Just wondering: What's your rationale to use Unicode61 in an iOS project? Being able to sort based on the locale is a feature all our foreign customers demand (and here in German as well). Of course nobody will

Re: [sqlite] Proximity ranking with FTS

This info is not provided by the API (or I missed it). I required this for a ranking that considers both the distance of N matches to each other, and the offset of those matches to the beginning of a text. I worked around this by giving a boost for the byte distance of the words (taking into

Re: [sqlite] Proximity ranking with FTS

This info is however not provided to an extension function. It would be great if the token number ¹t¹ could be added. Ben Am 17.06.14 19:36 schrieb "Dan Kennedy" unter : >On 06/17/2014 10:48 AM, Josh Wilson wrote: >> Yeah I had thought about using the byte distance

Re: [sqlite] Importing ~1000 CSV files faster

Directly after opening your db: PRAGMA synchronous = OFF; PRAGMA journal_mode = WAL; It should fly then, at the cost of risking db corruption in case of a crash. Ben(jamin Stadin) Am 19.08.14 23:11 schrieb "joe.fis...@tanguaylab.com" unter : >I'm running the

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

I've written a Sqlite diff tool that does this fast, but using a different approach. I'm adding a column named md5checksum to each to be diffed table, and add a index to it. A small tool then generates checksums for all rows in the table. Instead of comparing all columns, only the difference

[sqlite] Diff two tables as fast as diff(1) (of sorted files)

Sqlitediff diffs a same db (one db changed over time). If you are looking for diffing data using the same db scheme that has been generated at different times (producing different instances of the db scheme, eg you import data from somewhere and crate a same db scheme from it, but different

[sqlite] FTS5 stopwords

I?ve implemented a custom ranker in SQLite that is similar to SPH_RANK_SPH04 using FTS4 (BM25 + word distance and distance to beginning of text). The only thing that wasn?t possible out of the box using FTS4 was to get the distance between found matches as distance between them (how many words are

[sqlite] Some FTS5 guidance

One such algorithm would be a (generalized) Ukkonnen suffix tree (https://en.m.wikipedia.org/wiki/Ukkonen%27s_algorithm). It allows you to search efficiently for substrings. It would be possible to do some match weigthing based on match distance within words. But a general solution for a

[sqlite] Fastest way to backup/copy database?

Hi Rob, I think Clemens suggestion may be worth investigating, in case you do not want to stop the updates (which probably means a change in your workflow and some effort at other places anyways). I think this may work: - Use WAL, and turn off automatic checkpointing

[sqlite] Matchinfo FTS4 token position

Hi all, I need to implement a ranking that considers the search result's token distance (so either the actual character offset, or token offset from the beginning of the text). Is it possible to get/query this distance from the FTS4 index (either directly or via ft4saux), or would I have to

Re: [sqlite] Matchinfo FTS4 token position

*); Regards Ben Am 14.05.13 14:06 schrieb "Richard Hipp" unter <d...@sqlite.org>: >On Mon, May 13, 2013 at 12:25 PM, Stadin, Benjamin < >benjamin.sta...@heidelberg-mobil.com> wrote: > >> Hi all, >> >> I need to implement a ranking that considers

[sqlite] Custom Ranking: How to get length of search word

Hi, I'm implementing a custom rank function which should give an exact match a higher score. So in the ranking function I get the length of the found matches via offsets(). But how can I get the length of the original match search word? match info() doesn't provide this. In addition, is it

[sqlite] How to get the longest common sequence using C

Hi, >From the documentation on fts3 it is not clear to me how to access the longest common distance structure. Could you please provide an example how to calculate the offset to the stuct inside the match info? According to the docs I thought it should be something like this, but so far no luck:

Re: [sqlite] Comparing two tables column by column

If you like ruby, I have another idea to get you going (maybe without needing to write much code): - Use a registered function to SQLite to create MD5 or SHA1 keys for rows in the table. Here is a ruby snippet that registers a SHA1 function:

Re: [sqlite] Comparing two tables column by column

le is a table and files have // the same column names in the same order). Actually, these are // records for different dates char sql[256] = "select * from tableA_new;"; container c; sqlite3_exec(db,sql,callback_1,(void*)c,null) }

[sqlite] Get notified as soon as it's save to modify a db after sqlite3_update_hook() was triggered

Hi, Is there a hook which allows to get notified as soon as it’s save to modify a db connection after (or as alternative to) sqlite3_update_hook was triggered? The background to this question is that I’m trying to prepare a proposal for Geopackage. And one of the questions is if it’s possible

Re: [sqlite] Get notified as soon as it's save to modify a db after sqlite3_update_hook() was triggered

e standard. Peter On 4/3/2017 9:26 AM, Stadin, Benjamin wrote: > Hi, > > Is there a hook which allows to get notified as soon as it’s save to modify a db connection after (or as alternative to) sqlite3_update_hook was triggered? > > The backg

[sqlite] sqlite3_column_type() returns SQLITE_NULL even though it contains text

Hi, I was missing a few records in my program. After debugging further, I found that sqlite3_column_type () returns SQLITE_NULL for some objects (like 10 in 2000), even though these actually contain text data. Is this normal? And how should I handle it properly (I mean, check if it’s proper

Re: [sqlite] sqlite3_get_auxdata() defect

Hi Clemens, Even though the documentation makes no guaranty about the lifetime, it is was also for me rather useless. I need to do some spatial calculations in my custom SQL function, and use the proj4 projection library within it. This SQL function is called very often, and having to

[sqlite] Callback for sqlite3_finalize()

Hi, I register a custom SQL function using dms_create_function_v2, and in the C callback I create a rather heavy C++ helper class which I need to prepare the result. I currently use sqlite3_get_auxdata and sqlite3_set_auxdata, but my problem is that the finalization callback of

Re: [sqlite] Callback for sqlite3_finalize()

That should read sqlite3_create_function_v2 Am 22.11.17, 00:03 schrieb "sqlite-users im Auftrag von Stadin, Benjamin" <sqlite-users-boun...@mailinglists.sqlite.org im Auftrag von benjamin.sta...@heidelberg-mobil.com>: dms_cr

Re: [sqlite] Feature request for the Shell Tool: .mode json

Hi Simon, I recently wrote a tool to convert an arbitrary SQLite result set to properly typed json key/value pairs, using the SQLite type affinity of the objects. Though the code is in C++. But it gives an idea how simple this is when with a JSON library (I'm using RapidJson). Rapidjson can