Re: [sqlite] csv extension/feature requests: new options for field separator different from default ', ' and skip=N to skip first N lines
On 3 Dec 2019, at 16:10, Jannick wrote: > would it be possible to add to the csv extension the following parameter > options (with syntax along the lines of): > > - sep=';': field separator character (different from default ',') > - skip=N: skip the first N lines > > Both 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 mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite with branching
On 4 Nov 2019, at 22:25, Jens Alfke wrote: >> On Nov 4, 2019, at 4:57 AM, Simon Slavin wrote: >> >> That's one of the reasons that the source code for SQLite is public: so that >> people can add the features they want. > > Totally agree. However, when you go off the mainline of SQLite you lose some > things, like easy updating to new SQLite releases — you now have to deal with > merging the new official SQLite into the forked SQLite, or waiting for the > fork maintainer to do it. For such fundamental and big changes, that’s exactly the problem. For the mainline SQLite we know that the code-base is regularly maintained and updated and that the official extensions work. Hence, I can base a product on it. For such a big new feature/method, which would be at the core of my product, I either have to maintain it myself (heavy) or hope that 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
@SQLite Guys: Do you have something like branching on your roadmap? I really like this feature and see a lot of use-cases beside the blockchain topic. And, of course if this works with your encryption extension that would be awesome. * simple versioning of a database: Useful when you want to keep different app states (like complex calculations) and be able to go back and forth in time to see what data was used, what changed, what is the impact of that change. * simple implementation of alternatives: create a branch and let the user do whatever the want. Later they can keep it or go 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 have no connection with the following project. > > <https://github.com/aergoio/litetree> > > Described poorly on the web site so here's my own description: > > This is an extension of SQLite which allows branched versions, each new > branch creating one dataset which existed before the new branch and a new > dataset, initially a copy of the old dataset at some historical point, which > can be further modified. Both the old and new branches can be further > branched. > > LiteTree is implemented storing the SQLite db pages on LMDB making it more > than twice as fast as normal SQLite on Linux and MacOSX, and also runs on > Windows. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 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] 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 itself only uses BTree-indexes AFAIK. I think what would be very nice to have in SQLite is a bitmap (not image) datatype where I can store & query single bits and get back a rowid cursor. So, that I can iterate over the hits. That would unify the API and access pattern to be SQLite compliant. No need to fiddle around with other data-structure or concepts. -- Robert M. Münch, CEO Saphirion AG smarter | better | faster http://www.saphirion.com http://www.nlpp.ch Check my calendar at https://doodle.com/saphirion to find a free slot. 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
[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 http://www.nlpp.ch Check my calendar at https://doodle.com/saphirion to find a free slot. 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] 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, #defining the variable SQLITE_ENABLE_SESSION, (regardless of > its value), enables the session feature. > > In sqlite3.c, #defining the variables SQLITE_ENABLE_SESSION=1 and > SQLITE_ENABLE_PREUPDATE_HOOK=1 is necessary for the shell link to succeed. > > I suggest this could be simplified, or at least made more coherent. If the > update-related session behavior is optional in sqlite3, it probably should > be in the shell also. > > -- > Larry Brasfield > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 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
[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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/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 mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 survive any app crashes. > > Use a SAVEPOINT instead of COMMIT: > <https://www.sqlite.org/lang_savepoint.html> That won’t help. SAVEPOINTS are for undoing things. I want to write one thing definitely to disk to have it available in case of a crash. If the app crashes, all SAVEPOINTS are gone. > Your use of an open transaction here is sort-of abuse of how SQLite works. > You have no real control over what is flashed to disk. I do, that’s why I use an open transaction. In this case, everything is flushed into the .journal file, which is automatically rolled back if it exists on the next app start. > Rather than relying on side-effects of transactions it might be better to > make your own data structure which reflects which tranche of data a > transaction is in. Beside the brute force approach of copying the unmodified file before opening and changing it (without a global open transaction than) the session extension seems to be the way to go. So creating changesets which are removed on user save. If the app crashes, the user can choose to start from the last manual save point or from the latests point minimizing data loss. -- 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
[sqlite] How to do autosaving (commit data to disk) within an transaction?
When the user opens a file I start an outer-most transaction to be able to revert the file. So far so good. 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 survive any app crashes. But how can I 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 digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Network share & disk I/O error
On 12 Jul 2018, at 19:11, Richard Hipp wrote: > When network filesystems do not follow the usual semantics of a > filesystem, it can cause problems. > > That said, Firefox and Chrome have for many years stored lots of stuff > in SQLite database files in the users home directory, which is often a > network share, and they have not reported any problems. Hi, ok, that’s good news. So it’s definitely possible to get working. > On the other hand, they (or at least Firefox, I not sure about Chrome) run > with > PRAGMA locking_mode=EXCLUSIVE [1] which tends to bypass 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. -- 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Network share & disk I/O error
Hi, Context: Users of our app can define a working directory where sqlite files are stored. This can be a network share. The files are only used by one user at the time. We now saw two crash reports with „Database disk I/O error“ when a transaction was closed with „END“. Is this a known problem when having sqlite files on a network share? I read about the locking quirks on network share but related this to cases where a file could be accessed by several users at the same time and an application trusts the operating system to handle the locking correct. I don’t understand 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 database and it works great! > In general, your object tree already is the equivalent of an R-tree, > i.e., just doing hit-testing in the objects themselves should be just > as efficient. Being able to formulate much more flexible queries on the object tree and getting back a flat result list is very valuable. Reaching the same level as the SQLite implementation would still need quite some time. -- 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size of the SQLite library
On 31 May 2018, at 19:15, Richard Hipp wrote: > But more recently, mobile phone designers are telling me things like > "try to keep the size under 5 megabytes, if you can, please." > > Based on those more recent conversations, I'm thinking that we have > more headroom that we have had historically, and so I have recently > been allowing new features to start creeping into the core. Size matters IMO, it’s a sign of good design and less is more WRT errors etc. > Size is still important. But having useful features is important too. True, and we all know that most features are not used. Do you have an idea what features are used by ratio? Maybe adding a „report back feature collector“ might be an idea, for those wanting to support the feature selection process. > I'm continuing to work to find the right balance between these > competing 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] R*Tree / In Memory Database / GUI Object Hit Testing
Hi, I’m wondering if the R*Tree index of Sqlite could be used to implement GUI object hit testing? Scenario: We have a 2D scene-graph for our GUI, which does an automatic layout of objects following the CSS grid idea. So, we build up the graph, run layout and get back the coordinates where to render things. We could populate a r*tree table with (runtime-object-memory-pointer, x0, y0, x1, y0) pretty easy. Now the user clicks the mouse somewhere, and we would like to get back all rectangles that were hit. Since we don’t have any overlapping we could even sort the rectangles by size and get the whole tree-path to the leaf rectangle back. Since we would implement a R*Tree anyway for hit testing and use Sqlite in our app, I can imagine that using it might really be nice here. IMO if everything is running in memory, this should be very efficient. We are talking maybe about 5000 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Change name part of a JSON object
Hi, we use one JSON column to store column data that was added after tables
were created. If such a column is now renamed, we need to rename the name part
as well.
Example: {"Untitled":"bla"} How to change “Untitled“ ?
A hack would be to search & replace “Untitled“: 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT
On 16 Mar 2018, at 11:41, Rowan Worth wrote: > Doing it with an if means you always run two queries - the first to > determine whether a row exists: > > SELECT EXISTS (SELECT ID from Book where Name = "SearchName") Hi, yes that’s true. Not critical in my case but if you have a slow FFI in between this is not ideal. > There's two approaches which reduce the best case to a single query: > > 1. Immediately try to INSERT using the ON ABORT conflict strategy (ie.the > default). If the query fails with SQLITE_CONSTRAINT you know the row is > already present, so run the UPDATE. Yes. In my case we crash hard on any SQLite problems as such a constraint violation shouldn’t happen at all and if indicates an invalid application state. > 2. Immediately try to UPDATE the existing row. Then call sqlite3_changes to > determine how many rows were updated - 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: +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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPSERT
On 16 Mar 2018, at 11:35, Simon Slavin wrote: > You can use INSERT OR IGNORE for this. First, do an INSERT OR IGNORE command > which will add a dummy entry, with the right key, if one doesn't already > exist. > … Hi, yes, I thought about this too but really don’t like it and 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 digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UPSERT
Hi, is there an elegant way to simulate a dynamic UPSERT in SQLite since it
doesn’t has an UPSERT?
So, if I have a table with 30 columns and my code updates sub-sets out of these
columns, I don’t want to write queries that manually retrieve the old values
one by one.
insert or replace into Book (ID, Name, TypeID, Level, Seen) values (
(select ID from Book where Name = "SearchName"),
"SearchName",
5,
6,
(select Seen from Book where Name = "SearchName"));
So, for every column I don’t want to change I need to add a sub-select
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
smarter | better | faster
http://www.saphirion.com
http://www.nlpp.ch
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
[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 | faster http://www.saphirion.com http://www.nlpp.ch 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] How to do rollback & rollforward?
On 2 Nov 2017, at 16:56, Richard Hipp wrote: > Did you see this document: https://www.sqlite.org/undoredo.html No, I didn't, thanks a lot. > Do the ideas talked about there help in any way? Yes, it's what we are seeking. The SESSIONs (sorry thought it's named VERSION) extension looks 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. 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to do rollback & rollforward?
Hi, following scenario: Our app has a very exhaustive undo system. There you can, as long as you don't change anything, go back in time, take a look at your data and than go forward again and get back all changes you have done. I think the VERSION extension might help us here. On the other hand, SAVEPOINTs look very good too as they are named. But I don't see a way how I can go back to a SAVEPOINT and then forward again. Would this be possible somehow? The other idea is to take the named SAVEPOINTs idea and implement something like this manually with change-sets etc. that can 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 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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, 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT ... VALUES / want to "skip" default values
On 29 Jun 2017, at 19:06, Jens Alfke wrote: >> On Jun 29, 2017, at 12:13 AM, Hick Gunter <h...@scigames.at> wrote: >> >> Double quotes is specifically for building identifiers that "look strange" >> (i.e. embedded spaces, keywords, ...) which IMHO should be avoided because >> it tends to clutter up the statement. > > I agree that if you’re generating the schema by hand you should avoid > creating names that require quoting. > > However, if tables/columns/indexes are being generated dynamically, it can be > very convenient to name them based on the external item that uses them, and > that name might involve “strange” characters. Yes, I wrote a big function that strips all non-conforming chars, words etc. from column names (since we use '...' and I got some errors) because we import CSV user data into a table where the columns are the CSV columns. I might x-check to see if I can switch to "..." and avoid all the hassle at all. > In my case, I create indexes on the fly based on JSON paths like > “address[0].zipcode”, so I use the path as part of the index name. This is > simpler than 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 around in the sqlite3 tool. True too. -- 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT ... VALUES / want to "skip" default values
On 28 Jun 2017, at 14:51, Simon Slavin wrote: > Really ? In that case I withdraw my previous answer. I thought that NULLs > were converted to the default value for a column (which is usually NULL but > can be overridden with a DEFAULT clause). I had exactly the same understanding. 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT ... VALUES / want to "skip" default values
On 28 Jun 2017, at 9:49, Maks Verver wrote: > I'm surprised nobody mentioned that you can specify the columns to be > inserted in the query: > > INSERT INTO test(a, c, d) VALUES (1, 2 3); > > (Note that `b` is missing it `a, c, d`. It will take the default value, > which will be NULL, unless a different default was specified explicitly in > the CREATE TABLE statement.) Hi, sorry, should have mentioned that this doesn't work in my case, because we are building the column placeholders dynamically. So, we would have to handle putting the necessary column names in there 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 mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT ... VALUES / want to "skip" default values
On 27 Jun 2017, at 22:24, David Raymond wrote: > If you have to provide 4 values then the way you can use null to do that is > to add in a trigger to set the default, since NULL _is_ a value and _is_ > legal for that field. Ha, that's a very good idea. I didn't have triggers in 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT ... VALUES / want to "skip" default values
On 27 Jun 2017, at 22:11, David Raymond wrote:
> Single quotes should be used for strings, so DEFAULT '-'
I thought it doesn't matter if I use " or ' for strings. What's the difference?
> So there is no method to do something like...
>
> INSERT INTO test VALUES ('field a', 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
smarter | better | faster
http://www.saphirion.com
http://www.nlpp.ch
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
[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 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JSON1 extension / json_patch / no such function
On 25 Jun 2017, at 10:36, David Empson wrote: > json_patch() was added to the json1 extension in SQLite 3.18.0, but the other > functions were there in earlier versions as far back as SQLite 3.9.0. Hi, I see... > Looking at https://sqlitestudio.pl/index.rvt?act=changelog it appears the > latest version of SQLiteStudio (3.1.1) is using SQLite 3.15.0, so that is > consistent with it not supporting json_patch(). Ok, thanks. > As for your own program, are you compiling the SQLite 3.18.0 (or later) > amalgamation and linking it directly into your program, Yes, 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 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] JSON1 extension / json_patch / no such function
Hi, I'm trying to use the json_patch function in a query and tried in
SQLiteStudio and my own program and both times I get a "Error: Database no such
function: json_patch"
The query I want to do is:
UPDATE json_products SET json_value = json_patch(json_value,'{"col-1":1000}')
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.com
http://www.nlpp.ch
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] JSON1 extension & speed-up by using indices etc.?
On 20 Jun 2017, at 2:34, Jens Alfke wrote: > My understanding from reading the docs is that SQLite view’s aren’t “built” > at all: their contents have no physical existence in the database, the views > are simply macros that transform the statements that use them. (Correct me if > I’m wrong; I haven’t actually used views.) Hi, yes, that's right. That's why it makes sense to speed up VIEW execution as it's run dynamically when views are used. > You can create indexes to support JSON1 queries by using the same json_xx > function calls in a CREATE INDEX statement. 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? -- 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JSON1 extension & speed-up by using indices etc.?
On 19 Jun 2017, at 17:09, Simon Slavin wrote: >> CREATE VIEW json AS SELECT rec_id, json_extract(json_value,'$.col-1') as >> col1, json_extract(json_value,'$.col-2') as col2, ... , >> json_extract(json_value,'$.col-50') as col50 FROM a > Please supply a same SELECT command that you would use this VIEW for. I’m > especially interested in clauses such as GROUP BY, ORDER BY and WHERE. The SELECTs we use don't have a GROUP BY, the ORDER BY will only be on one column and we mostly return all columns, sometimes a subset. The WHERE parts look like this: ... WHERE 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 | better | faster http://www.saphirion.com http://www.nlpp.ch 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
[sqlite] JSON1 extension & speed-up by using indices etc.?
Hi, I have a table A(rec_id, JSON-of-record) and I create a view like this: CREATE VIEW json AS SELECT rec_id, json_extract(json_value,'$.col-1') as col1, json_extract(json_value,'$.col-2') as col2, ... , json_extract(json_value,'$.col-50') as col50 FROM a All SELECT requests will then run 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://www.saphirion.com http://www.nlpp.ch 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] 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, 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?
On 17 Jun 2017, at 19:29, Igor Tandetnik wrote: > For the first three (or any fixed N) columns, yes. But I thought you wanted a > view that somehow automatically becomes wider or narrower as rows are > inserted or deleted in the underlying table. I don't believe such a thing is > possible. That's right, the query has to be build fitting the structure. > So you already know the answer to your question, then? What else would you > like assistance with? Well, the question for me is, which approach will be faster? A table with (entity, value) and a query like I posted: select rec_id , max(case when col_id = "col-1" then value end) col1 , max(case when col_id = "col-2" then value end) col2 , max(case when col_id = "col-3" then value end) col3 from products where col1 = "ABC" group by rec_id or using the JSON1 extension and building a query like: select rec_id , json_extract(json_value,"$.col1") as col1 from json_products , json_extract(json_value,"$.col2") as col2 from json_products from products, json_each(col1) where json_each.value = "ABC" The JSON data might be 20.000 records each 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?
On 17 Jun 2017, at 14:25, Igor Tandetnik wrote: > By the same token, I don't believe such a query exists. Hi, I think such a query exists, here we go: Columns are: col_id, value, rec_id col-1 1 1 col-1 2 2 col-1 3 3 col-1 4 4 col-1 5 5 col-1 6 6 col-1 7 7 col-1 8 8 col-1 9 9 col-2 1 1 col-2 2 2 col-2 3 3 col-2 4 4 col-2 5 5 col-3 1 1 col-4 1 1 col-5 1 1 col-6 1 1 col-7 1 1 col-8 1 1 col-9 1 1 Which is a classical entity-attribute-value (EAV) model, taking normalization to the extreme and no longer uses columns in the traditional way. Instead, every single value is stored in its own row. Besides the value, the row also has a column to specify which attribute the value represents and a third column to identify what entity the values belongs to. Ultimately, a three column table can hold any data without ever needing to change the table definition. To get a traditional VIEW on this, we want to transpose the data, or create a pivot of it. Here is a pretty simple structure of such a VIEW for the first three columns: select rec_id , max(case when col_id = "col-1" then value end) col1 , max(case when col_id = "col-2" then value end) col2 , max(case when col_id = "col-3" then value end) col3 from products group by rec_id With columns: rec_id, col1, col2, col3 1 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 7 7 8 8 9 9 That's what I need to avoid schema changes on tables with dynamic attributes 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Session Extension & ALTER TABLE handling
On 17 Jun 2017, at 13:36, Dan Kennedy wrote: > I think that changed for ALTER TABLE ADD COLUMN in 3.17.0. As of 3.17, if the > changeset contains fewer columns than the database tables it is being applied > to, trailing columns are populated with their default values. Hi, I tried this 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: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?
On 17 Jun 2017, at 14:10, Igor Tandetnik wrote: > I don't think so. The number and names of columns in the view are determined > at the time CREATE VIEW statement is executed. That won't be a problem as we can update the VIEWs. The question is, how does a query look like that transposes 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 mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using key/value idea to store data & creating normal table VIEW?
On 17 Jun 2017, at 9:57, Wout Mertens wrote: > And another option of course is to store all those extra columns as JSON, > which you can query with the JSON1 extension. You can even index on the > extracted values. That's a very interesting idea. I'm going to check it out. So, the idea 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 signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using key/value idea to store data & creating normal table VIEW?
Hi, suppose I store my data like this: Table A: table, column, data Now I want to create a VIEW named A.table, with columns A.column and rows A.data Is that possible? Background: We have one table where columns can be added/removed. We want to use the session extension to keep track 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Session Extension & ALTER TABLE handling
Hi, if the DB scheme was changed with ATLER TABLE (like adding a column to a table) it's no longer possible to apply a changeset from before this ALTER TABLE change. If I'm reverting this ALTER TABLE change manually somehow, would it be possible to apply the changeset then? Or does SQLite 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How-to and best practice for session extension
Ok, after using the session extension and doing some tests, we face one problem I would like to get some feedback from the experts, how to best solve this. Context: User's can import CSV data into our application. We use a bunch of fixed tables and one for the user data. Of course the layout (number of columns) of this table depends on the user data structure. Users are allowed to add & remove columns. Removing is implemented by just flagging a column as "not there", so no schema change. But adding a column of course needs an ALTER TABLE operation. Hence, the user table only gets bigger. Problem: As soon as the schema changes, we can't apply recorded changesets anymore. An option would be to keep the user data in a column/value format and somehow create a view from it that looks like a normal DB table. Not sure if this is possible, how fast this is and if it makes sense at all. Next option, somehow keep track of schema changes and revert them manually before a changeset is applied. Has anyone done this? The next option would be to take a look at the session extension and see if a more relaxed use of changesets is possible. Here I think as long 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.saphirion.com http://www.nlpp.ch 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] How-to and best practice for session extension
On 12 Jun 2017, at 17:33, Simon Slavin wrote: >> 1. Is it correct that schema changes are not tracked and can't be part of a >> changeset? So any ALTER TABLE command needs to be taken care about >> separately? > > Correct. Changing the schema means that changesets are no longer valid. You > will either need to make a system which can handle both, or regard schema > changes as points you cannot UNDO past. As long as the schema changes in a way that the new schema is "bigger" than the old one, old changesets should still be applicable. I haven't tried it out though. >> 2. Changes to the VIEW query are not tracked too? So, when a view query >> changes, this is not part of the changeset. > > VIEWs, like INDEXes, are part of the schema. Changing a VIEW is changing the > schema. Ok, confirms my view. BTW: Is there a versioning/session extension that can track schema changes too? >> 3. We are thinking about supporting UNDO not only for the current session >> but for the live-time of a document. In this case we need to store the >> changeset blob into the database as well. > > But storing something in the database is changing the database! You would get > problems with recursion. Only if the table storing the changesets gets tracked too. Which can be avoided. So not tracking this particular changeset table should do the job. > Instead, you would have to store the blobs in another database. That would seem to work too, but we wouldn't have a "single file contains it all" setup anylonger. I will try to omit the changeset table and see if that works. >> 4. We haven't thought through all combinations but is the oder of UNDOs in a >> changeset relevant? So if I have a changeset do I have to apply it strictly >> in revers order in some cases to avoid constraint violations etc? > > Absolutely. Consider this sequence: > > INSERT a row > DELETE that row > > Now try to UNDO that sequence in the wrong order. You end up with a row > which wasn’t there. We use SQLite's INVERT function on changesets, which does the trick. I haven't looked at the code but assume that it applies the inverted actions in reverse order. > I cannot comment on using sessions to do this but I can suggest an > alternative 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: +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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How-to and best practice for session extension
Hi, we want to use the Session extension to implement an UNDO system for our application. We did some first steps using it and now a couple of questions came up: 1. Is it correct that schema changes are not tracked and can't be part of a changeset? So any ALTER TABLE command needs to be taken care about separately? 2. Changes to the VIEW query are not tracked too? So, when a view query changes, this is not part of the changeset. 3. We are thinking about supporting UNDO not only for the current session but for the live-time of a document. In this case we need to store the changeset blob into the database as well. We want to track changes of all tables but of course not the table getting the changesets. There is a call for which tables to track changes, but not one for which tables not-to-track. Is there a simpler way than listing all tables for which tracking should happen explicitly? 4. We haven't thought through all combinations but is the oder of UNDOs in a changeset relevant? So if I have a changeset do I have to apply it strictly in revers order in some cases to avoid constraint violations etc? Or are constraints put on hold until all changes were applied? 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@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Copy records from one DB to another
On Tue, 24 Apr 2007 19:57:31 +0200, Yuriy Martsynovskyy <[EMAIL PROTECTED]> wrote: What is the best way to copy records between tables located in different DB files? Hi, I want to extend this question: How to best copy records from one database (linked via different tables) to an 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.robertmuench.de - To unsubscribe, send email to [EMAIL PROTECTED] -
