Re: [sqlite] Copy records from one DB to another

other database? How to keep record references in sync? Because if a new records is in the destination table it gets a new auto-increment ID and all references need to be adjusted. Thanks. -- Robert M. Münch http://www.robertmue

Re: [sqlite] Poll: Include the recent sqlite3_column_name() fix in the upcoming 3.20.0 release?

On 31 Jul 2017, at 17:21, Richard Hipp wrote: > Ticket https://sqlite.org/src/info/de3403bf5ae5f72ed describes a > problem with column naming, and a proposed solution. Today's > question: Should the proposed solution be merged into the 3.20.0 > release? +1 yes -- Robert M. Münch

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

ding. BTW: Is this behaviour standard or a SQLite variant? -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature ___ sqlite-user

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

DEFAULT, 'field c', 'field d'); That's what I want to do. > PS: Simon: Specifying NULL will just put a NULL value in there, it won't use > the default. I tried NULL and as you said, that doesn't work because NULL is put in. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG sm

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

here all the time, which is not feasible. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature ___ sqlite-users m

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

the radar. Great, I think that's solving my problem. Thanks a lot! -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature _

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

an escaping the punctuation, using a digest of the path, or making > up an identifier that then has to be stored somewhere else. Good point. We need to do the same in the future. > It also makes the schema a lot easier to understand when looking at generated > statements or poking aroun

[sqlite] INSERT ... VALUES / want to "skip" default values

Hi, I have a table like: CREATE TABLE test(a, b DEFAULT "-", c, d) Now I would like to use INSERT VALUES(a,?,c,d) Where ? is something that the default value is used and not the provided value. Is this possible at all? Viele Grüsse. -- Robert M. Münch, CEO M: +41 79 65 11 49 6

[sqlite] How-to and best practice for session extension

? Viele Grüsse. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

Ah, cool. Since at one point I know all the columns and later on a couple columns could be added, I'm thinking about creating the table with the known columns in a classical way and have one additional JSON column for future expansion. I think that could speed things up. What do you think? --

Re: [sqlite] JSON1 extension & speed-up by using indices etc.?

col-1 <> NULL AND col-2 <> NULL ... AND col-x <> NULL ... WHERE col-x LIKE ... and so on. So mostly pretty simple queries. No JOINS etc. We have about 50.000 records and maybe at max 50 columns. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | bett

Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?

On 19 Jun 2017, at 10:47, Robert M. Münch wrote: > Well, the question for me is, which approach will be faster? > ... Hi, answering my own question as I tried it with 5 records and 50 columns. The JSON approach is way faster up to factor 1000. Viele Grüsse. -- Robert M. Münch,

[sqlite] JSON1 extension & speed-up by using indices etc.?

against the view. This view works and of course takes some time to build. Can the JSON1 extension be supported by adding indices or whatever, that speeds-up the VIEW construction/updates? Viele Grüsse. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http

[sqlite] JSON1 extension / json_patch / no such function

000}') json_extract works and json_value too. So I'm really wondering why json_patch is missing. I checked the sources, and the code is there. I really don't have an idea what's up here. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.co

Re: [sqlite] JSON1 extension / json_patch / no such function

I do this. I'm using 3.19.3 now. I found out that if you don't set SQLITE_ENABLE_JSON1 it seems some functions are available but not all... when setting SQLITE_ENABLE_JSON1 all are available. That's a bit strange IMO, but might be a problem on my end, not sure. -- Robert M. Münch, CEO M: +41 79

Re: [sqlite] How-to and best practice for session extension

rnative which is compatible with more systems. This is that for every SQL > command you execute you provide one or more commands which would reverse the > effect. That's what SQLite constructs when creating an inverted changeset. That's what we use. -- Robert M. Münch, CEO M: +4

Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?

dea would be to just store JSON data in table A that contains column/value/...? -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital

Re: [sqlite] Session Extension & ALTER TABLE handling

s but get an error "Database schema changed" when applying the inverted changeset. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPG

[sqlite] Session Extension & ALTER TABLE handling

count scheme changes incrementally, so that even then it's not possible to apply the changeset? -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature

Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?

On 17 Jun 2017, at 9:53, Wout Mertens wrote: > Could you not combine the data on the app side? We are currently holding all data on the app side and want to get it into SQLite to make use of it's querying features. So, that would be a step back. -- Robert M. Münch, CEO M: +41 79 65 11 4

Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?

sposes the data from a flat to column structure? -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature ___ sqlite-users mailin

Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?

ttributes and make it compatible with the session extension. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature ___ sqlit

[sqlite] Using key/value idea to store data & creating normal table VIEW?

of changes to our database. But the session extension can't handle schema changes. Thanks a lot. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature

Re: [sqlite] How-to and best practice for session extension

as all columns are there a change can be applied. For additional columns the values would just be deleted. Or even better add reversing of ALTER TABLE commands to the changeset handler. Viele Grüsse. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www

Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?

ach JSON object/array containing 5 - 50 fields. Are there are any benchmarks available? -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature __

[sqlite] How to do rollback & rollforward?

be played back and forth. I'm not totally sure how to best tackle this. If using VERSION extension and storing the changes inside the DB (while excluding the tracking for the change-set-table) this should even give us a persistent undo system, right? Viele Grüsse. -- Robert M. Münch, CEO M: +41 79

Re: [sqlite] How to do rollback & rollforward?

s like something that will handle the tracking tables for us and create those change-sets as in the example. Storing change-sets inside the DB should work too, I think. Or would it be better to use the approach from the example and omit the SESSIONs extension at all? Viele Grüsse. -- Robert M.

[sqlite] R*Tree / In Memory Database / GUI Object Hit Testing

entries. What do you think? Or maybe any experiences with such a setup? -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature

Re: [sqlite] Size of the SQLite library

g goals. Keeping things configurable as it is, is a very good approach, please keep it. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature ___

Re: [sqlite] R*Tree / In Memory Database / GUI Object Hit Testing

On 20 May 2018, at 15:56, Clemens Ladisch wrote: > Robert M. Münch wrote: >> I’m wondering if the R*Tree index of Sqlite could be used to implement >> GUI object hit testing? > > Yes, that would be possible. Using this approach now for some time with a memory databa

[sqlite] Session Extension / Memory Consumption

Hi, does anyone know the memory consumption when I track changes to a database in relation to the change-size? For example: If my DB file is 10MB and I change 30% of the database will the memory consumption be something around 3MB? -- Robert M. Münch, CEO Saphirion AG smarter | better

Re: [sqlite] How to do autosaving (commit data to disk) within an transaction?

On 29 Jul 2018, at 14:07, Simon Slavin wrote: > On 29 Jul 2018, at 12:56pm, Robert M. Münch > wrote: > >> I want to add an autosave feature that stores some data at critical >> points/specific intervals. This data of course should be commited to disk, >> to survi

[sqlite] How to do autosaving (commit data to disk) within an transaction?

commit stuff to disk, while an outer-most transaction is open? Is there something like an „UPDATE IMMEDIATELY…“ or so to bypass an open transaction? How would one do this? I would like to avoid using a 2nd database-file if possible. -- Robert M. Münch signature.asc Description: OpenPGP

Re: [sqlite] Network share & disk I/O error

ypass most of the > network filesystem bugs, at the expense of restricting database access > to a single process at a time. Ok, we can try that. > Is your system able to live with that restriction and use PRAGMA > locking_mode=EXCLUSIVE? Yes, that shouldn’t be a problem. Thanks. -- Ro

[sqlite] Network share & disk I/O error

it that way that using sqlite files from a network share is per se impossible. Any insights on this? Viele Grüsse. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature

[sqlite] UPSERT

statement. If I need to build this statement dynamically, IMO it would be better to handle this code directly in code: if(record-exists?){ UPDATE … } else { INSERT … } Any suggestions / feedback? Viele Grüsse. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG s

[sqlite] Change name part of a JSON object

tled“: with the new name… but might be dangerous… any better idea? -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digital signature ___ s

Re: [sqlite] UPSERT

this assumes that you don’t use AUTOINCREMENT on a table otherwise this strategy will fail. Viele Grüsse. -- Robert M. Münch, CEO M: +41 79 65 11 49 6 Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch signature.asc Description: OpenPGP digit

Re: [sqlite] UPSERT

ated - if zero then you know the row didn't > exist, so run the INSERT. That’s a nice one. I’m going to try this. At least you don’t run two queries but you need two calls, replacing the query for the #of-rows-changed query, which should be quicker. Viele Grüsse. -- Robert M. Münch, CEO M: +4

[sqlite] SIMD based JSON parsing for speeding up JSON extension

Hi, see: https://github.com/lemire/simdjson Can parse GB/s of JSON input. This might be a good candidate to use in the extension. -- Robert M. Münch signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users

[sqlite] Speed: geopoly_overlap() or geopoly_within() VS. using R*Tree directly?

Hi, when doing 2D hit-testing with only rectangular areas, is it faster to use the geopoly extension and functions or is the bare R*Tree extension faster? -- Robert M. Münch signature.asc Description: OpenPGP digital signature ___ sqlite-users

Re: [sqlite] shell session feature, #define awkwardness

The docs at https://www.sqlite.org/compile.html state: „SQLITE_ENABLE_SESSION This option enables the session extension.“ which is not enough. You need to define SQLITE_ENABLE_PREUPDATE_HOOK too. Robert M. Münch On 8 Aug 2018, at 17:55, Larry Brasfield wrote: > In shell.c, #defin

[sqlite] http://roaringbitmap.org/

Hi, I think that SQLite use some bitmap indexes and this here might be of interest if not already used/known: http://roaringbitmap.org/ I think it’s from the same guy how did SIMDJSON. Viele Grüsse. -- Robert M. Münch, CEO Saphirion AG smarter | better | faster http://www.saphirion.com

Re: [sqlite] http://roaringbitmap.org/

On 2 Sep 2019, at 10:59, Dominique Devienne wrote: > On Mon, Sep 2, 2019 at 8:06 AM Robert M. Münch > wrote: > >> Hi, I think that SQLite use some bitmap indexes > > > Not that I know of, but I don't know the full source code. Maybe FTS[345] > do/es, but SQLite its

Re: [sqlite] SQLite with branching

it’s not a dead-end… the risk profile is just not good. Viele Grüsse. -- Robert M. Münch signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with branching

back. I think using changesets can be a bit more challenging for such a use-case. * I see such a feature as a natural companion to the session extension. Where versioning/branching works on the whole database. Viele Grüsse. Robert M. Münch On 29 Aug 2018, at 14:28, Simon Slavin wrote: > I h

Re: [sqlite] csv extension/feature requests: new options for field separator different from default ', ' and skip=N to skip first N lines

options would be very helpful for information of fixed format > downloaded from information providers. Hi, those would be very useful enhancements. -- Robert M. Münch signature.asc Description: OpenPGP digital signature ___ sqlite-users m