Re: [sqlite] multithreaded app with in-memory database and transactions
On Thursday, 13 February, 2020 17:58, Jim Dodgen wrote: >I have often wondered what the performance difference is between /dev/shm >and :memory: databases Theoretically a :memory: database is faster than a /dev/shm stored database. A :memory: database is purely in memory and has no extra connection cache (and no transactions to speak of). A database in /dev/shm is just like an on-disk database, and the connection has its own memory cache, the difference being that there will never be a wait to flush the OS cache to disk (the file exists only in the OS file cache). Using a /dev/shm file will still require file read/write because it is a file as far as the application is concerned. A file on /dev/shm will release all its space back to the OS free pool when the file is deleted but will persist until reboot when closed by the application (and not deleted). A :memory: database will release its memory back to the OS when it is detached/closed. A database opened on a RAMDISK will behave like a database on /dev/shm except that deleting the file will not release the space allocated for the RAMDISK back to the OS since the RAMDISK usually permanently allocates virtual storage for its contents when created. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multithreaded app with in-memory database and transactions
I have often wondered what the performance difference is between /dev/shm and :memory: databases Jim "Jed" Dodgen j...@dodgen.us On Thu, Feb 13, 2020 at 4:48 PM Keith Medcalf wrote: > > On Thursday, 13 February, 2020 17:06, Jim Dodgen > wrote: > > >I have placed databases on/in /dev/shm and shared them across both > >threads and processes. > > Yeah, /dev/shm is a pre-existing tmpfs filesystem, separate from the one > mounted on /tmp. I keep forgetting about that one ... > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multithreaded app with in-memory database and transactions
On Thursday, 13 February, 2020 17:06, Jim Dodgen wrote: >I have placed databases on/in /dev/shm and shared them across both >threads and processes. Yeah, /dev/shm is a pre-existing tmpfs filesystem, separate from the one mounted on /tmp. I keep forgetting about that one ... -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multithreaded app with in-memory database and transactions
I have placed databases on/in /dev/shm and shared them across both threads and processes. Jim "Jed" Dodgen j...@dodgen.us On Thu, Feb 13, 2020 at 2:38 PM Keith Medcalf wrote: > > Correct. "memory" databases can only be shared between connections in the > same process, and then only by the sharedcache method. In effect, a > "memory" database is nothing more than a cache, and sharing it between > connections means sharing the cache. cache=private uses a separate cache > for the connection and therefore (by definition) a different "memory" > database. > > You could, of course, use an "on disk" database where the database resides > on a memory resident filesystem. On Linux you would simply mount another > filesystem using tmpfs and put your "on disk" database there. Delete the > "file" off the tmpfs filesystem when you are done with it. (Or just use > /tmp if it is mounted using tmpfs which it usually is. Just beware that > everyone has access to /tmp and if you mount your own tmpfs at a different > location you can control the access permissions). > > On Windows the closest thing I know of that can create an autosizing > ramdisk is http://winramtech.atwebpages.com/RAMDriv/ramdriv.htm though I > have not tried it. > > For other OS's, Google is your friend! > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > >-Original Message- > >From: sqlite-users On > >Behalf Of Wim Hoekman > >Sent: Thursday, 13 February, 2020 11:44 > >To: sqlite-users@mailinglists.sqlite.org > >Subject: [sqlite] multithreaded app with in-memory database and > >transactions > > > >I have an app which is multithreaded. Sometimes during lengty inserts a > >different thread (which only reads the database) sees part of the > >updated data. > > > >This would be solved by using transactions. However, during the > >transaction the "reading" thread gets a 'database table is locked' error. > > > >In my case, transactions work fine when using on-disk database file, but > >not when using an in-memory database. > > > >In my code, I open the database twice, so I have two separate database > >connections: one for updating, one for reading. > > > >I've tried several ways of opening the in-memory database: > > > >1) "file:memdb?cache=shared=memory", > >SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > > does not work, 'database table is locked' error > > > >2) "file:memdb?cache=private=memory", > >SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > > does not work, "different" databases > > data added via one db connection is not visible when querying via > >other > >db conncection > > > >3) "file:filedb?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > > does not work, 'database table is locked' error > > > >4) "file:filedb?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > > works, but is not in memory > > > >5) "file:filedb", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > >does not work, 'database table is locked' error > > > > (because I called sqlite3_enable_shared_cache( 1 )! ) > > > >6) "file::memory:?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > > same behaviour as 1) > > > >7) "file::memory:?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > > same behaviour as 2) > > > >It seems I can not get 2 database connections to the same in-memory > >database, without using a shared cache. > >And having a shared cache causes 'database table is locked' errors. > > > >Did I miss something, or is multithreaded transactions with an in-memory > >database not supported? > >Any hints or tips to get this working are appreciated. > > > >Regards, > > > >Wim. > > > >___ > >sqlite-users mailing list > >sqlite-users@mailinglists.sqlite.org > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multithreaded app with in-memory database and transactions
Correct. "memory" databases can only be shared between connections in the same process, and then only by the sharedcache method. In effect, a "memory" database is nothing more than a cache, and sharing it between connections means sharing the cache. cache=private uses a separate cache for the connection and therefore (by definition) a different "memory" database. You could, of course, use an "on disk" database where the database resides on a memory resident filesystem. On Linux you would simply mount another filesystem using tmpfs and put your "on disk" database there. Delete the "file" off the tmpfs filesystem when you are done with it. (Or just use /tmp if it is mounted using tmpfs which it usually is. Just beware that everyone has access to /tmp and if you mount your own tmpfs at a different location you can control the access permissions). On Windows the closest thing I know of that can create an autosizing ramdisk is http://winramtech.atwebpages.com/RAMDriv/ramdriv.htm though I have not tried it. For other OS's, Google is your friend! -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Wim Hoekman >Sent: Thursday, 13 February, 2020 11:44 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] multithreaded app with in-memory database and >transactions > >I have an app which is multithreaded. Sometimes during lengty inserts a >different thread (which only reads the database) sees part of the >updated data. > >This would be solved by using transactions. However, during the >transaction the "reading" thread gets a 'database table is locked' error. > >In my case, transactions work fine when using on-disk database file, but >not when using an in-memory database. > >In my code, I open the database twice, so I have two separate database >connections: one for updating, one for reading. > >I've tried several ways of opening the in-memory database: > >1) "file:memdb?cache=shared=memory", >SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > does not work, 'database table is locked' error > >2) "file:memdb?cache=private=memory", >SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > does not work, "different" databases > data added via one db connection is not visible when querying via >other >db conncection > >3) "file:filedb?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > does not work, 'database table is locked' error > >4) "file:filedb?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > works, but is not in memory > >5) "file:filedb", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI >does not work, 'database table is locked' error > > (because I called sqlite3_enable_shared_cache( 1 )! ) > >6) "file::memory:?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > same behaviour as 1) > >7) "file::memory:?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI > same behaviour as 2) > >It seems I can not get 2 database connections to the same in-memory >database, without using a shared cache. >And having a shared cache causes 'database table is locked' errors. > >Did I miss something, or is multithreaded transactions with an in-memory >database not supported? >Any hints or tips to get this working are appreciated. > >Regards, > >Wim. > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorrect join result with duplicate WHERE clause constraint (again)
On 2/13/20, Jim Bosch wrote: > https://gist.github.com/TallJimbo/d819876a77cfd79312ad48508cfdd8a2 Thanks for the very succinct bug report. The problem is now fixed on trunk. https://www.sqlite.org/src/timeline?c=c9a8defcef35a1fe -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual table function calls
Jens Alfke asks:
>Maybe pass the column name as a string, i.e. `attr('H',3)`
2 problems with that idea. First, the first argument has to refer to a value in
the virtual table in order to invoke the overridden version (overrides are per
table, so I use the ppArg to bind function invocation to associated virtual
table). Second, if I created a view that gives column H a name of Total, I’d
want to the function call to be attr(Total,3). I don’t know how to make the
function figure out that attr(‘Total’,3) really means column H in some table.
>>Dave
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual table function calls
> On Feb 13, 2020, at 12:52 PM, David Jones wrote:
>
> sqlite> select F,G,H,attr(H,3) from summary; # show formula used
> to calculate column H.
Maybe pass the column name as a string, i.e. `attr('H',3)`? It sounds like your
`attr` function needs to know the _identity_ of the column, not its contents,
and the name is basically the identity.
—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling SQLite Encryption Extension for ARM processor
> On Feb 13, 2020, at 10:51 AM, Subodh Pathak wrote: > > I am looking for help to configure machine to compile SEE for ARM. I am > using Android mobile Samsung G7. You have to use a cross-compiler, a version of GCC that runs on your platform but generates ARM-Linux code. Specifically, to build for Android you should be using the Android Native Development Kit (NDK). The page Dr. Hipp linked to will show you how to do that. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual table function calls
I’ve written a virtual table to view spreadsheet data inside Excel workbook (.xlsx) files as SQlite tables. I want to implement an SQL function, attr(), which the virtual table overrides to give access to the raw attributes of the cell being returned. I’m looking for a robust way to find the actual column refered to by an overriden function, not just the value returned by the xColumn call. Example: sqlite> .load xlsxvtab.so # creates xlsxvtab module and attr() function sqlite> create virtual table summary using xlsxvtab(‘expenses.xlsx’,’sheet1’,’F20’, ‘H32’); sqlite> select F,G,H,attr(H,3) from summary; # show formula used to calculate column H. The issue is that attr gets called with 2 values and I need to divine that the first value came from column H of the current row of the cursor opened by this select statement. The hack I’m using now is to give the values returned by the xColumn method a subtype equal to the column number. In the attr() function I retrieve the subtype and re-fetch that column from the current row of the last cursor open on that table. Various pitfalls with this technique: - Subtype numbers are limited to the range 0-255. - Assumes all functions called before xNext() method called again. - Complex queries with multiple cursors? Any suggestions? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Incorrect join result with duplicate WHERE clause constraint (again)
In 3.31.1, this self-contained script, which joins to an unnecessary table and adds what should be a redundant but harmless WHERE constraint on it: https://gist.github.com/TallJimbo/d819876a77cfd79312ad48508cfdd8a2 returns incorrect results (which clearly violate the redundant constraint). Removing either of the two redundant constraints fixes the problem. The results in 3.27.2 and 3.30.1 were correct. Tests were done with SQLite packages from the Anaconda Python distribution, against regular file-based databases. I didn't see any active tickets that appeared to be related, but I did find https://www.sqlite.org/src/tktview?name=cf5ed20fc8, which was reported against 3.25 and seems to have fixed before any of the versions I tested - I'm not sure if this is just a reappearance of that bug, but it's at least very similar. Hope this is useful! Jim Bosch ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] multithreaded app with in-memory database and transactions
I have an app which is multithreaded. Sometimes during lengty inserts a different thread (which only reads the database) sees part of the updated data. This would be solved by using transactions. However, during the transaction the "reading" thread gets a 'database table is locked' error. In my case, transactions work fine when using on-disk database file, but not when using an in-memory database. In my code, I open the database twice, so I have two separate database connections: one for updating, one for reading. I've tried several ways of opening the in-memory database: 1) "file:memdb?cache=shared=memory", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI does not work, 'database table is locked' error 2) "file:memdb?cache=private=memory", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI does not work, "different" databases data added via one db connection is not visible when querying via other db conncection 3) "file:filedb?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI does not work, 'database table is locked' error 4) "file:filedb?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI works, but is not in memory 5) "file:filedb", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI does not work, 'database table is locked' error (because I called sqlite3_enable_shared_cache( 1 )! ) 6) "file::memory:?cache=shared", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI same behaviour as 1) 7) "file::memory:?cache=private", SQLITE_OPEN_READWRITE|SQLITE_OPEN_URI same behaviour as 2) It seems I can not get 2 database connections to the same in-memory database, without using a shared cache. And having a shared cache causes 'database table is locked' errors. Did I miss something, or is multithreaded transactions with an in-memory database not supported? Any hints or tips to get this working are appreciated. Regards, Wim. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling SQLite Encryption Extension for ARM processor
On 2/13/20, Subodh Pathak wrote: > > I am trying to compile SEE for ARM processor. There is a website explain how to compile SQLite for Android here: https://www.sqlite.org/android/doc/trunk/www/index.wiki Please review the instructions on that website and write again if they do not work for you. D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compiling SQLite Encryption Extension for ARM processor
Team, I am trying to compile SEE for ARM processor. I have followed following steps. But was not successful in generating " *libsqliteX.so*" file which can be used on Android Samsung G7 mobile. 1. Installed GCC compiler from “http://www.mingw.org/” reference at “ https://gcc.gnu.org/”. 2. Installed required library specially related to lpthread. 3. Downloaded another library “libdl.a” from “ https://github.com/dlfcn-win32/dlfcn-win32” to compile using -ldl command. MinGW-W32 and MinGW-W64 only have dlfcn to provide libdl, but that's not a regularly installed package. After extracting package. Copied “libdl.a” to “C:\MinGW\lib” * gcc -c -fPIC sqlite3.c see-aes256-ofb.c* * gcc -shared -o libsqliteX.so -fPIC sqlite3.o see-aes256-ofb.c -ldl –lpthread* 4. Above mentioned command does generate "libsqliteX.so" file but not compatible to use on Android Samsugn G7. References: https://gitlab.kitware.com/cmake/cmake/merge_requests/1642 https://gcc.gnu.org/install/binaries.html https://gcc.gnu.org/ https://sqlite.org/android/doc/trunk/www/index.wiki https://www.sqlite.org/see/doc/release/www/index.wiki https://www.hwaci.com/sw/sqlite/see.html https://sqlite.org/src/doc/trunk/README.md I have also tried to do below configuration before compile but no lock. ./configure --build=x86_64-unknown-linux-gnu --host=arm-linux --target=arm-linux I am looking for help to configure machine to compile SEE for ARM. I am using Android mobile Samsung G7. Any help is much appreciated. Regards, Subodh ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .timer explanation anywhere
On Windows the GetProcessTimes Win32 API is used to get the user and kernel (sys) times for the current process since getrusage only exists on unix-like platforms. In all cases the precision and accuracy are limited by the underlying OS timer accuracy. The vfs call to get the current time is limited to milliseconds notwithstanding that the underlying precision and accuracy of the underlying OS call may be different than 1 millisecond. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .timer explanation anywhere
According to the code in shell.c the .timer on/off sets a flag that tells whether you want timer data printed or not, and then for each statement: if .timer is turned on save the current wall clock and getrusage times (usr and sys times) execute the statement. if .timer is turned on get the new wall clock and getrsuage times display the difference between the new and old values sort of like you would know how long X() took (in wall clock elapsed seconds) if you did: begin = time(); X(); finish = time(); elapsed = finish - begin; Just that .timer saves and reports three values provided by the underlying Operating System, not just one. "real" is the current time (in seconds) reported by the underlying OS, and user/sys are the underlying times in seconds reported by the Operating System getrusage call for user/sys CPU usage times for the current process. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Jose Isaias Cabrera >Sent: Thursday, 13 February, 2020 06:48 >To: SQLite mailing list >Subject: [sqlite] .timer explanation anywhere > > >Greetings. > >I was searching on sqlite.org for [sqlite command line tool .timer >explanation] and found nothing. I also searched on the internet and found >an old thread[1] of when .timer had just two entries: > >CPU Time: user 880.710398 sys 353.260288 > >And, although, there is some good information there, I would like for us >to have a solid answer somewhere. :-) Maybe even explain it on the site >[2], or better yet, have an option on .timer (on|off|?) to explain each >piece of the output. One-liners will suffice. > >I also found this other interesting post [3], which I think is not >totally correct, but I will let you guys explain why it is true. Or, at >least, if it has some truth in it. > >Thanks for your support. > >josé > >[1] http://sqlite.1065341.n5.nabble.com/timer-on-in-the-shell-tool- >td79626.html >[2] >https://www.sqlite.org/cli.html#special_commands_to_sqlite3_dot_commands_ >[3] https://stackoverflow.com/questions/40329106/how-to-measure-the- >execution-time-of-each-sql-statement-query-in-sqlite >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Prevent receiving "ok" when using the .dump and .output commands
Hi, How can I prevent receiving the "ok" added here: https://www.sqlite.org/src/info/b3692c406f7ba625 when I'm using `sqlite -cmd "PRAGMA key " database.sqlite .output`? iulianOnofrei ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?
Tom and Slavin: The dump of information sounds like a good idea. To Slavin's question, the user need to be able to repeated search, but as a developer, I would want and idea I can eventually implement repeatedly. I've done this successfully in the past, but it required 4-5 methods handling a combination of SQL statements and looping arrays. Thanks for your help guys! Scott ValleryEcclesiastes 4:9-10 On Thursday, February 13, 2020, 09:35:54 AM EST, Simon Slavin wrote: On 13 Feb 2020, at 2:01pm, Scott wrote: > Can I search all tables and columns of SQLite database for a specific text > string? No. There's no way to do this, and there's no way to say "all tables" in SQL. In other words it's not easy to write such a thing. I like Thomas Kurz's solution, to dump the database as SQL commands. Alternatively you could write a text exporter for all the columns you think might hold the string, then search that text. If you want better solutions, it might help to tell us whether this is a one-time problem, something you (the developer) might want to do repeatedly, or something you want the user to be able to do repeatedly. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?
Scott, on Thursday, February 13, 2020 09:01 AM, wrote...
>
> Can I search all tables and columns of SQLite database for a specific
> text string? I'm sure this question has been asked many times, but I'm
> having trouble finding a solid answer.
> My problem: My clients SQLite database has 11 tables and multiple columns
> (some designated and others not) and they want to be able to search the
> entire database for a specific text or phrase.
> What I have done: I've been searching a couple days and found the Full
> Text search on SQLite home based upon using a virtual table, but I don't
> think that will work. It appears that I may be able to search the
> sqlite_master but it seems it may only contain table and column information
> only minus the data.
> What I'm working in: This is an Android app written in Java using the
> SQLite
> What I hope to do: Find a simple query statement or combination of
> statements that may help to efficiently query for the string across tables
> and columns before I resort to multiple queries and methods for all 11
> tables.
> I'm looking for any experienced thoughts or suggestions anyone may have
> encountered resolving this kind of issue if available. I'm not expecting
> anyone to solve it for me -- just some guidance would be helpful.
This is a very wide open question. It is a lot of work to create the query.
I actually have to do this for some tables and some fields, but I know these
tables and these fields. Here are some questions:
1. What are you going to do when you find a string match in a table field?
2. Do you need to know that table? Do you need to know the field?
3. Do you need the whole content of that field if matched?
There are just too many questions to help, but it is possible if you know
what do you want to do. Here are some ideas:
a. The command prompt has a .table option that will provide all the tables
available on a DB
b. The .schema [tablename] will give you the table's fields
Imagine these three tables:
create table table0 (id INTEGER PRIMARY KEY, t0a, t0b, t0c, t0d);
insert into table0 (t0a, t0b, t0c, t0d) values ('text in here', 'Shelby 2002',
'2 plus 2 equals 4', 'I am going home soon');
create table table1 (id INTEGER PRIMARY KEY, t10,t11,t12);
insert into table1 (t10,t11,t12) values ('p001', 'Shelby 2002', '1 plus 1
equals 2');
create table table2 (id INTEGER PRIMARY KEY, t2a, t2b, t2c, t2d);
insert into table2 (t2a, t2b, t2c, t2d) values ('in here', 'going home',
'2020-02-11','Once upon a time...');
SELECT
'field t0a on table0 has the string [plus]: ', t0a from table0
WHERE t0a LIKE '%plus%'
UNION
SELECT
'field t0b on table0 has the string [plus]: ', t0b from table0
WHERE t0b LIKE '%plus%'
UNION
SELECT
'field t0c on table0 has the string [plus]: ', t0c from table0
WHERE t0c LIKE '%plus%'
UNION
SELECT
'field t0d on table0 has the string [plus]: ', t0d from table0
WHERE t0d LIKE '%plus%'
UNION
SELECT
'field t10 on table1 has the string [plus]: ', t10 from table1
WHERE t10 LIKE '%plus%'
UNION
SELECT
'field t11 on table1 has the string [plus]: ', t11 from table1
WHERE t11 LIKE '%plus%'
UNION
SELECT
'field t12 on table1 has the string [plus]: ', t12 from table1
WHERE t12 LIKE '%plus%'
UNION
SELECT
'field t2a on table2 has the string [plus]: ', t2a from table2
WHERE t2a LIKE '%plus%'
UNION
SELECT
'field t2b on table2 has the string [plus]: ', t2a from table2
WHERE t2a LIKE '%plus%'
UNION
SELECT
'field t2a on table2 has the string [plus]: ', t2a from table2
WHERE t2a LIKE '%plus%'
;
For just to search on the string 'plus' you will have to do the
above query.
sqlite> SELECT
...> 'field t0a on table0 has the string [plus]: ', t0a from table0
...> WHERE t0a LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t0b on table0 has the string [plus]: ', t0b from table0
...> WHERE t0b LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t0c on table0 has the string [plus]: ', t0c from table0
...> WHERE t0c LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t0d on table0 has the string [plus]: ', t0d from table0
...> WHERE t0d LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t10 on table1 has the string [plus]: ', t10 from table1
...> WHERE t10 LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t11 on table1 has the string [plus]: ', t11 from table1
...> WHERE t11 LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t12 on table1 has the string [plus]: ', t12 from table1
...> WHERE t12 LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t2a on table2 has the string [plus]: ', t2a from table2
...> WHERE t2a LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t2b on table2 has the string [plus]: ', t2a from table2
...> WHERE t2a LIKE '%plus%'
...> UNION
...> SELECT
...> 'field t2a on table2 has the string [plus]: ', t2a from table2
...> WHERE t2a LIKE '%plus%'
...> ;
field t0c on table0 has the string [plus]: |2 plus 2 equals 4
field t12 on table1 has the
Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?
On 13 Feb 2020, at 2:01pm, Scott wrote: > Can I search all tables and columns of SQLite database for a specific text > string? No. There's no way to do this, and there's no way to say "all tables" in SQL. In other words it's not easy to write such a thing. I like Thomas Kurz's solution, to dump the database as SQL commands. Alternatively you could write a text exporter for all the columns you think might hold the string, then search that text. If you want better solutions, it might help to tell us whether this is a one-time problem, something you (the developer) might want to do repeatedly, or something you want the user to be able to do repeatedly. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can I search all tables and columns of SQLite database for a specific text string?
Can I search all tables and columns of SQLite database for a specific text string? I'm sure this question has been asked many times, but I'm having trouble finding a solid answer. My problem: My clients SQLite database has 11 tables and multiple columns (some designated and others not) and they want to be able to search the entire database for a specific text or phrase. What I have done: I've been searching a couple days and found the Full Text search on SQLite home based upon using a virtual table, but I don't think that will work. It appears that I may be able to search the sqlite_master but it seems it may only contain table and column information only minus the data. What I'm working in: This is an Android app written in Java using the SQLite What I hope to do: Find a simple query statement or combination of statements that may help to efficiently query for the string across tables and columns before I resort to multiple queries and methods for all 11 tables. I'm looking for any experienced thoughts or suggestions anyone may have encountered resolving this kind of issue if available. I'm not expecting anyone to solve it for me -- just some guidance would be helpful. Thanks, Scott ValleryEcclesiastes 4:9-10 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?
I would create an SQL dump ("sqlite3 file.db .dump") and search therein.
- Original Message -
From: Scott
To: SQLite Mailing List
Sent: Thursday, February 13, 2020, 15:01:06
Subject: [sqlite] Can I search all tables and columns of SQLite database for a
specific text string?
Can I search all tables and columns of SQLite database for a specific text
string? I'm sure this question has been asked many times, but I'm having
trouble finding a solid answer.
My problem: My clients SQLite database has 11 tables and multiple columns (some
designated and others not) and they want to be able to search the entire
database for a specific text or phrase.
What I have done: I've been searching a couple days and found the Full Text
search on SQLite home based upon using a virtual table, but I don't think that
will work. It appears that I may be able to search the sqlite_master but it
seems it may only contain table and column information only minus the data.
What I'm working in: This is an Android app written in Java using the SQLite
What I hope to do: Find a simple query statement or combination of statements
that may help to efficiently query for the string across tables and columns
before I resort to multiple queries and methods for all 11 tables.
I'm looking for any experienced thoughts or suggestions anyone may have
encountered resolving this kind of issue if available. I'm not expecting anyone
to solve it for me -- just some guidance would be helpful.
Thanks,
Scott ValleryEcclesiastes 4:9-10
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] O_NOFOLLOW issue with /dev/null on Solaris
On 2/13/20, jakub.ku...@oracle.com wrote: > > Recently, O_NOFOLLOW was added to several calls into robust_open(). In > that function, if the fd returned by open() is too low (in the stdio > range 0-2), then it closes it, and opens /dev/null to pad out the fd's > until we reach at least fd#3. Background information: That mechanism was added as a defense again application bugs causing database corruption. See paragraph 1.1 of the "How To Corrupt an SQLite Database" document: https://www.sqlite.org/howtocorrupt.html The fact that you are hitting this problem suggests that there is something wrong with your application. Thanks for the suggested improvements to SQLite. A patch for this will appear in the next release. Or you can use the latest trunk check-in. https://www.sqlite.org/src/timeline?c=0c683c43a62fe25c -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .timer explanation anywhere
Greetings. I was searching on sqlite.org for [sqlite command line tool .timer explanation] and found nothing. I also searched on the internet and found an old thread[1] of when .timer had just two entries: CPU Time: user 880.710398 sys 353.260288 And, although, there is some good information there, I would like for us to have a solid answer somewhere. :-) Maybe even explain it on the site [2], or better yet, have an option on .timer (on|off|?) to explain each piece of the output. One-liners will suffice. I also found this other interesting post [3], which I think is not totally correct, but I will let you guys explain why it is true. Or, at least, if it has some truth in it. Thanks for your support. josé [1] http://sqlite.1065341.n5.nabble.com/timer-on-in-the-shell-tool-td79626.html [2] https://www.sqlite.org/cli.html#special_commands_to_sqlite3_dot_commands_ [3] https://stackoverflow.com/questions/40329106/how-to-measure-the-execution-time-of-each-sql-statement-query-in-sqlite ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] O_NOFOLLOW issue with /dev/null on Solaris
After sqlite 3.29 -> 3.31 upgrade, we started seeing issues related to
differences in /dev/null in Solaris.
Recently, O_NOFOLLOW was added to several calls into robust_open(). In
that function, if the fd returned by open() is too low (in the stdio
range 0-2), then it closes it, and opens /dev/null to pad out the fd's
until we reach at least fd#3.
However, it uses the same flags to open /dev/null as were passed in for
the database open(), resulting in O_NOFOLLOW being passed to
open("/dev/null"). The issue we are seeing is that /dev/null is a
symlink on Solaris, and hence this returns ELOOP, and robust_open()
returns an error.
$> ls -l /dev/null
lrwxrwxrwx 1 root root 27 Feb 12 18:05 /dev/null ->
../devices/pseudo/mm@0:null
I propose to patch this one of two ways:
a) replace the flags with known hard-coded values (as there is no
need to use caller-supplied flags to open /dev/null):
-if( osOpen("/dev/null", f, m)<0 ) break;
+if( osOpen("/dev/null", O_RDONLY, m)<0 ) break;
b) mask out the O_NOFOLLOW, but allow all others to come through.
-if( osOpen("/dev/null", f, m)<0 ) break;
+if( osOpen("/dev/null", f & ~O_NOFOLLOW, m)<0 ) break;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Announce: sqlite3-cli node package
Hi Everybody, I just thought I'd announce this new node library here: name: sqlite3-cli description: A shell for executing sqlite queries https://github.com/pguardiario/sqlite3-cli Comments / requests are welcome. - Thanks. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
On Wed, Feb 12, 2020 at 9:02 PM Eric Grange wrote:
> [...] This is completely safe vs SQL injection, and IME quite efficient. [...]
I disagree that this is efficient enough. I'd much rather have native support in
SQLite for array binding, in the public API, than this. That public
API could wrap
what carray does perhaps, except in a type-safe way (which carray is not IMHO).
e.g., the API could be, for SQL "select rowid from tab where owner = ?
and name_id in (?)":
sqlite3_bind_int(stmt, 1, scalar_int_val);
sqlite3_bind_array_begin(stmt, 2, vector_int_val.size()); // size
hint, to pre-size internal buffers
for (int i : vector_int_val) { // C++11 range-for loop
sqlite3_bind_int(stmt, 2, i);
}
sqlite3_bind_array_end(stmt, 2);
That syntax is completely made up, but with the equivalent of carray(), SQLite
could efficiently "do the right thing" (perhaps rewriting the SQL into
a join), knows
the cardinatity of the array, so can order the join correctly, etc...
The above approach adds only two APIs, and reuses the existing bind APIs,
to avoid duplicating them all with array-variants. FWIW :). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
