Re: [sqlite] [EXTERNAL] Lazy virtual table creation
Thanks for your responses. Actually, it is up to my code to find out if the table exists or not. But you also raised a good point, as it can disappear, so what I’m looking for is more something like: “this statement uses this set set of tables [x,y,z…], please prepare your environment accordingly”. This is what I’m currently doing by pre-eanalyzing the SQL statement and extract the tables matching a identifiable pattern. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Lazy virtual table creation
We are using virtual tables to provide an SQL access to our data set and this works very well. But we have potentially "a lot” of virtual tables, with some even yet unknown when we start the DB. We’d like to create them lazily, on first access. Is there a hook we can use so when an SQL statement refers to a non existing table it asks a callback for a VT definition? It is fine if these dynamic table requires a specific prefix similar to the "temp” one. Regards, Phil. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multithreaded SQLite
Yes, I?m using prepare(), step and finalize(). The 2 threads should actually have no interaction between them, but isolated. I mean the 2 threads should be able to do very different things (read data, write data?), within separated transactions.
[sqlite] Multithreaded SQLite
On the OS standpoint, we are using multiple ones: Android, iOS and desktop (Windows, OSX and Linux). So we need to figure a solution that works with all these OSes. when I used the WAL mode, I got a schema locked, while in DELETE mode i got database locked. >>>If WAL is supported, then you can have concurrent readers with one writer<<< Right, but how can we get the second writer to wait until the lock is released and continue instead of getting an error? I tries busy_timeout but that was unsuccessful. I?d like to avoid ?locked? messages.
[sqlite] Multithreaded SQLite
I?m a bit lost with the multi-threaded concurrent access errors I?m getting, and looking for an advise on the best solution. Basically, I have a desktop/mobile app (single user) that embeds a tiny local http server. The UI is done through an embedded browser, calling the server for pages and data. And this browser component can submit multiple requests, which will be processed simultaneously on the server, by different threads. Each of these requests can update the same SQLIte database, and this is were the troubles start. 1- Is it safe to share a single connection between all these threads (assuming serialized mode)? So far it seems to work, but what happens if a thread begins a transaction by calling ?BEGIN TRANSACTION"? Is this thread safe (the transaction bound to this thread), or will the statements from the other threads be mixed up in that transaction? Are there other known limitations, for example how does sqlite3_last_insert_row_id() behave? 2- If I need to create one connection per thread, what are then the best options to set on the connection? I tried many ones (journal mode=WAL, busy_timeout, ?) but I?m getting errors like database is locked or even schema is locked. 3- I tried to enable the shared cache, but I?m still getting database is locked (262). According to the doc, sqlite3_busy_handler does not help here. Is there a way to not get the error but simple have the thread wait until the lock is freed? Or is there a better way to get this implemented using SQLite, beyond synchronizing my threads and making sure that only one is executing DB code at a time? I?m currently using 3.9.1, but can move to the latest version if it helps.
[sqlite] Performance issue with CTE
Just to let you know, the solution using SELECT * FROM (query with offset/limit) works perfectly well. Thanks a lot for the suggestion!
[sqlite] Performance issue with CTE
I think the doc is right. I overcame the problem by using a construct like: SELECT field1, field2? WHERE PKEY IN (SELECT PKEY ? WHERE OFFSET n LIMIT m) That executes a sub query. But your solution looks actually better, as it is: SELECT * FROM (SELECT field1, field2? WHERE OFFSET n LIMIT m) I?ll try it. Thanks for the suggestion.
[sqlite] Performance issue with CTE
Thanks. I know about the technique your mentioned, but the point is not about the use of offset or not. The same issue will happen but using a key. See my other reply above.
[sqlite] Performance issue with CTE
I have a table with 500,000+ records. The table has a date column, that I?m using to sort my queries (the columns has an index). Simple queries on the table work very well, using ORDER BY, LIMIT & OFFSET. I?m actually extracting ?pages? of rows that I?m displaying in a web page. Great!. Now, instead of a simple select, I?d like to execute a recursive query using CTE, because the rows are organized in an hierarchy. And I?m only interested by a page, let's say the first n root records. Unfortunately, the doc says that the ?initial-select? in a recursive common table exception may not include ORDER BY, LIMIT or OFFSET. As a result SQLIte probably scans the whole table, which leads to very poor performance? With other databases (like PostgreSQL), I don?t have the problem because they accept ORDER BY, LIMIT and OFFSET on the initial-select, which limits the scan for the initial-select. What would be the proper solution with SQLite? One would involve a first query that selects the initial row ids, and then pass them as a condition to the initial-select in the recursive. But does anyone has a better proposal?
Re: [sqlite] Whish List for 2015
We’ll probably look at providing a set of functions for handling JSON in SQLite, similarly to what POSTGRESQL is doing. But, to make it efficient, we need to index the JSON content. I suggested earlier this year to get expression based indexes, so one can index the result of a function like json_path(“my column”,”a.b.c”) to speed up the search. That would be my own, single item, wish list :-) It can definitively open many doors. Happy new year, and I wish you guys a great 2015 year! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] strchrnul compilation error
Related to this issue, http://www.sqlite.org/src/info/0fac2c045f47c7735af4eb68ced81d8b43622a1f, sqlite 3.8.7 fails to compile on Android by default, plus there is a suspicious warning. The compiled code is the amalgamation: jni/sqlite/sqlite3.c: In function 'sqlite3VXPrintf': jni/sqlite/sqlite3.c:20980:11: warning: assignment makes pointer from integer without a cast [enabled by default] [armeabi-v7a] SharedLibrary : mylib-sqlite.so c:/Android/NDK/toolchains/arm-linux-androideabi-4.6/prebuilt/windows/bin/../lib/gcc/arm-linux-androideabi/4.6/../../../. ./arm-linux-androideabi/bin/ld.exe: ./obj/local/armeabi-v7a/objs/jsonstore-sqlite/sqlite/sqlite3.o: in function sqlite3V XPrintf:jni/sqlite/sqlite3.c:20980: error: undefined reference to 'strchrnul' collect2: ld returned 1 exit status Defining HAVE_STRCHRNUL 0 made it compile. The warning also disappears when the flag is set to '0'. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index on expressions
Hello, If there any plan to get this in? (see: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-September/055065.html ) Note that this seems implemented by several databases now, and this is a bit different from virtual columns, which is what the previous discussion ended with. Virtual columns force a table alteration (adding the columns) why indexes on expressions eventually speed up the search on conditions like f(x)='value', if the index exists. You add the index only if it makes sense from a performance standpoint, and it is not required. Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index on expressions
Is there a way to create indexes based on expressions instead of simple columns (see: http://www.postgresql.org/docs/8.1/static/indexes-expressional.html)? The idea is to have some custom functions that extract data from a JSON column and allow a fast query based on an index. This is the strategy used by POSTGRES and DB2, for example. I know that we can create a table and do a manual field extraction, but this is not transparent from a query standpoint, and forces a manual re computation of all rows when a new index is desired. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite & Android: unable to open database file - error 14
I posted the question on StackOverflow: http://stackoverflow.com/questions/21661814/sqlite-android-unable-to-open-database-file-error-14 Any help would be greatly appreciated! I saw that a similar issue has been reported many times. In my case, I'm directly using the "C" API invoked using my own JNI library (not the Android Java layer), with the latest amalgamation 3.8.3. So I control what I'm dealing with. Then, on certain operations (UPDATE) I'm getting an error stating that it cannot open a database file. I don't get that with INSERT statements, only UPDATE. When I activate the SQLite error callback, I'm seeing the following: cannot open file at line 29299 of [6c643e45c2] os_unix.c: 29299: (13) open(./etilqs_1zMsiYdpXhd3JqY) - statements aborts at 36: [UPDATE .] Digging this a little bit further, it appears to be related to the journal file. When I set the journal_mode to MEMORY using a pragma, then the issue disappears. But all the other modes, like WAL or TRUNCATED lead to the same error. When debugging the code from eclipse, I can see the -journal file being created when performing the INSERT. And then being removed when the transaction is complete. So it tells me that it has enough rights on the directory (/data/data//) Note that the exact same code works perfectly (C & Java) on Windows, where I'm not getting the error. There is definitively something going on with Android and file access. I have a way to get the error reproduced systematically. If someone has some idea on what I should check, then I'll be happy to run some experiment and report the result. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
