[sqlite] Full text serch - Matching all except chosen

Forgive me if this is the wrong way to do this but I'm a newbie. I am using version 3.24.0 with FTS5 Is there a way to perform a full text search that returns every row except records matching a specified query string? The following does not work because of a syntax error as the syntax requires a

Re: [sqlite] Foreign Key error

test.db https://drive.google.com/open?id=1gX4QDLy3rA1YVFXZnhj_vlAClVmrU4Cz SQLite version 3.9.2 2015-11-02 18:31:45 Enter ".help" for usage hints. sqlite> pragma foreign_keys=on; sqlite> Insert into option4_map(`option_id`,`parent_option_id`,`name_id`) values

Re: [sqlite] Foreign Key error

On Mon, Jul 30, 2018 at 2:11 PM Keith Medcalf wrote: > > >"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0", > >"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1", > >"SQLITE_DEFAULT_FOREIGN_KEYS=1" > > >Is there something about the combination of options I've used? > > Do you get

Re: [sqlite] (no subject)

On Mon, Jul 30, 2018 at 7:09 PM J Decker wrote: > > > On Mon, Jul 30, 2018 at 2:11 PM Keith Medcalf wrote: > >> >> >"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0", >> >"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1", >> >"SQLITE_DEFAULT_FOREIGN_KEYS=1" >> >> >Is there something

Re: [sqlite] (no subject)

On Mon, Jul 30, 2018 at 2:11 PM Keith Medcalf wrote: > > >"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0", > >"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1", > >"SQLITE_DEFAULT_FOREIGN_KEYS=1" > > >Is there something about the combination of options I've used? > > Do you get

[sqlite] Error: foreign key mismatch - "loan" referencing "user"

Hi, I am a beginner in database and I'm developing a Database (with Tcl/Tk) to control the loans of books in a reading room. I created a table to register the users: ... db1 eval {CREATE TABLE user (                        id_user integer PRIMARY KEY,                        name

Re: [sqlite] In tea/configure.ac why isn't --with-system-sqlite default on linux?

On Jul 30, 2018, at 2:26 PM, Peter Da Silva wrote: > > It worked fine on FreeBSD but on Linux (Ubuntu 4.13.0-27 generic) it crashed. > We traced that down to it having two copies of libsqlite linked in There can be a bunch of reasons for that crash, basically due to differing ways they’re

Re: [sqlite] (no subject)

>"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0", >"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1", >"SQLITE_DEFAULT_FOREIGN_KEYS=1" >Is there something about the combination of options I've used? Do you get different results when using different options? (of course, if you turn

[sqlite] (no subject)

I have these options ... version 3.23.0. "SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0","SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1", "SQLITE_DEFAULT_FOREIGN_KEYS=1" This is the end of operations... It's starting to get an option...

Re: [sqlite] SQLite 3.24.0 Solaris 9 build failure

On 07/30/2018 03:59 AM, Gary R. Schmidt wrote: On 2018-07-28 08:33, Andy Goth wrote: SQLite 3.24.0 fails to build on Solaris 9 (a.k.a. Solaris 2.9) It may be work while to spin up a Solaris 9 zone on a Solaris 10 or Solaris 11 server for this purpose. Not sure how you are getting a cross

Re: [sqlite] Sqlite Sharding HOWTO

On 2018/07/30 9:45 PM, Gerlando Falauto wrote: On Mon, Jul 30, 2018 at 9:42 PM, David Raymond wrote: Doesn't sound quite right to me. No matter the index you have to search through it to find the spot to do the insert. Both are going to do that search only once. An insert on a unique index

Re: [sqlite] Sqlite Sharding HOWTO

On 30 Jul 2018, at 8:38pm, Gerlando Falauto wrote: > Does that apply to the primary key as well? Primary key indexes are unique indexes, since SQLite has to enforce the primary key being unique. Howwever, I do not think there can be such a strong penalty for indexes being UNIQUE. I side

[sqlite] In tea/configure.ac why isn't --with-system-sqlite default on linux?

So I was doing some work with another Tcl extension (Pgtcl) that was linking to libsqlite3. It worked fine on FreeBSD but on Linux (Ubuntu 4.13.0-27 generic) it crashed. We traced that down to it having two copies of libsqlite linked in, the one in the sqlite3 extension (libsqlite3.24.0.so) and

Re: [sqlite] Sqlite Sharding HOWTO

I don't believe it is any worse. Question for devs: Would it be considered an optimization opportunity to push UNIQUE index inserts to the front, so that if something's going to fail then it fails sooner rather than later? In this oversimplified example the explain output shows it does the

Re: [sqlite] Sqlite Sharding HOWTO

On Mon, Jul 30, 2018 at 9:42 PM, David Raymond wrote: > Doesn't sound quite right to me. > > No matter the index you have to search through it to find the spot to do > the insert. Both are going to do that search only once. An insert on a > unique index isn't going to search through it for

Re: [sqlite] Sqlite Sharding HOWTO

Doesn't sound quite right to me. No matter the index you have to search through it to find the spot to do the insert. Both are going to do that search only once. An insert on a unique index isn't going to search through it for existence, then promptly forget what it just did and do it all over

Re: [sqlite] Sqlite Sharding HOWTO

On Mon, Jul 30, 2018 at 9:19 PM, Keith Medcalf wrote: > > >> A query doing a single insert of a few bytes with no Indexes, no > >> triggers, no functions will be stupendously fast, whereas any > >> increase in one or more of the above will slow things down. > >> How much exactly is something you

Re: [sqlite] Sqlite Sharding HOWTO

>> A query doing a single insert of a few bytes with no Indexes, no >> triggers, no functions will be stupendously fast, whereas any >> increase in one or more of the above will slow things down. >> How much exactly is something you need to test, any guesswork >> will not be useful. What I can

Re: [sqlite] Sqlite Sharding HOWTO

On Mon, Jul 30, 2018 at 1:58 AM, R Smith wrote: > On 2018/07/30 12:39 AM, Gerlando Falauto wrote: > >> >> The question that needs to be answered specifically is: How many data >>> input sources are there? as in how many Processes will attempt to write >>> to the database at the same time? Two

Re: [sqlite] Issue using SEE

On 7/30/18, Kumar_xam wrote: > > I have successfully created a .dylib file for macOS and now trying to > activate the library using sqlite3_activate_see("7bb07b8d471d642e"). My > question is do I need to use same activation code "7bb07b8d471d642e" or can > I use any 16 digit code. Please see

Re: [sqlite] Ssubstitution in Tcl goes sometimes wrong Posted

2018-07-30 20:39 GMT+02:00 Cecil Westerhof : > I described the problem here: > http://paste.tclers.tk/4800 > The solution is a CAST: AND Temperature > CAST(:tempAbove AS real) -- Cecil Westerhof ___ sqlite-users mailing list

[sqlite] Ssubstitution in Tcl goes sometimes wrong Posted

I described the problem here: http://paste.tclers.tk/4800 -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue using SEE

Hi Team, I have successfully created a .dylib file for macOS and now trying to activate the library using sqlite3_activate_see("7bb07b8d471d642e"). My question is do I need to use same activation code "7bb07b8d471d642e" or can I use any 16 digit code.

Re: [sqlite] Reducing index size

There's a good chance this comment won't be useful to you, Eric. Nevertheless, Any chance of relaxing your space requirement? I.e., what bad things happen if the space is not reduced? Maybe you're writing for a fixed-space embedded device, which nonetheless has space for the gigabytes

Re: [sqlite] Bug: Problem with sqlite3_prepare_v2

On 7/29/18, Jürgen Palm wrote: > As a quick, ugly workaround for my situation I could add something like >sqlite3_exec(conn1, "SELECT 1",NULL,NULL,NULL); > before the sqlite3_prepare_v2 after the schema change. > > Is there any better way? Or would it be possible to add a SQLITE_PREPARE >

Re: [sqlite] Bug: Problem with sqlite3_prepare_v2

Richard Hipp wrote: On 7/29/18, Jürgen Palm wrote: Hi, in an application I encountered a problem, where the changing of the structure of a table by dropping and recreating it via one connection to a db was not directly seen by another connection to the same db. Detecting a schema change

Re: [sqlite] Very, very slow commits

Droedel, Fortunately we have no performance issues in production with the 60GB database. These issues came out in testing (which is what testing is for). We're investigating the newly generated ID's as we speak or we will be once we get our replica production system setup. sqlite_analyser

Re: [sqlite] Very, very slow commits

Hi Rob, Answers are in the text below On Mon, Jul 30, 2018, at 13:45, Rob Willett wrote: > Droedel, > > We don't think there are significant read access. The database is a > single database on a single thread on a single process. The only access > to it is a Perl script that logs the incoming

Re: [sqlite] Very, very slow commits

Warren, The hardware is different, at the time we didn't want to spin up a complete production replica as thats quite expensive. We used a smaller machine, both have the same type of back end spining raid array, but we would think that writing 4-5MB of changed data back shouldn't take 7

Re: [sqlite] Very, very slow commits

On Jul 30, 2018, at 5:53 AM, Rob Willett wrote: > > I would wonder why writing the data to a 60GB database and doing a commit is > fast and writing exactly the same data to the 600MB database is different. > The programs for doing it are the same, the database schema is identical. I assume

Re: [sqlite] Very, very slow commits

Warren, On 30 Jul 2018, at 12:28, Warren Young wrote: On Jul 30, 2018, at 4:51 AM, Rob Willett wrote: The system has been running for 2-3 years Has performance changed over that span? Which direction? Performance hasn't changed on the large 60GB data database. its pretty consistent.

Re: [sqlite] Very, very slow commits

Droedel, We don't think there are significant read access. The database is a single database on a single thread on a single process. The only access to it is a Perl script that logs the incoming information. We never have two accesses at the same time. We have a nagging feeling (and thats

Re: [sqlite] Very, very slow commits

Hi Rob, Is there significant read access (iostat: r/s) during these slow writes ? If yes, it might be due to a small cache, requiring the database to read (index) pages before updating them. And is the data you're adding in both databases (large/small) added at the end of the table using the

Re: [sqlite] Very, very slow commits

On Jul 30, 2018, at 4:51 AM, Rob Willett wrote: > > The system has been running for 2-3 years Has performance changed over that span? Which direction? > we have taken the opportunity to try and prune the database from 60GB down to > 600MB. SQLite’s speed is only weakly affected by database

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

On Mon, Jul 30, 2018 at 11:42 AM Eric Grange wrote: > PtrMap pages may be too much overhead in my case, I have occasionally run > vacuum on same databases to see the effect, and it was not very > significant. > > This is likely because the databases are heavily skewed towards inserting > (and

Re: [sqlite] Very, very slow commits

Droedel, Thanks for the comprehensive reply. We have actually done all of this. The system has been running for 2-3 years and we have taken the opportunity to try and prune the database from 60GB down to 600MB. Currently the live system is working OK with a 60GB database, but our test system

Re: [sqlite] Reducing index size

On 30 July 2018 at 18:10, Eric Grange wrote: > @Rowan Worth > > Doesn't that problem already exist with the current index? Except worse > > because it's storing the cryptographic hash *and* the rowid. > > No, because SQLite is using a B-Tree (and with cryptographic hashes, it > should even take

Re: [sqlite] Very, very slow commits

Hi, When having bad performance, I usually first try to find out if the slowness is due to disk througput (sequential), slow random access or something else. In Linux, try "iostat -xtc 5". Do this with and without your application writing to disk. If you see high CPU %iowait and high %util on

Re: [sqlite] Reducing index size

@Rowan Worth > Doesn't that problem already exist with the current index? Except worse > because it's storing the cryptographic hash *and* the rowid. No, because SQLite is using a B-Tree (and with cryptographic hashes, it should even take less effort to balance) On Mon, Jul 30, 2018 at 12:05

Re: [sqlite] Reducing index size

On 30 July 2018 at 17:53, Eric Grange wrote: > @Rowan Worth > > What if you could create a "lite" index, which stores just the rowids in > a particular order and > > refers back to the table for the rest of the column data? > > As I have millions of rows, and data could get inserted anywhere in

Re: [sqlite] Reducing index size

@Dominique Devienne > SQLite supports function-based indexes, but unfortunately if does not support "function-based columns". Far fetched maybe, but could a virtual table or table-valued functions be used to provide that? ie. use the virtual table to pass data directly to an index, and then

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

PtrMap pages may be too much overhead in my case, I have occasionally run vacuum on same databases to see the effect, and it was not very significant. This is likely because the databases are heavily skewed towards inserting (and indexing) data than about update/delete, and while the tables are

Re: [sqlite] Reducing index size

On 30 Jul 2018, at 10:25am, Dominique Devienne wrote: > The former allows you to get what you want, but as you wrote, you must > rewrite your queries. The latter, > if supported, would allow to move the "function definition" to the column, > and index the vcolumn directly. It's the usual speed

Re: [sqlite] Reducing index size

On 30 July 2018 at 17:25, Dominique Devienne wrote: > On Mon, Jul 30, 2018 at 10:42 AM Simon Slavin > wrote: > > > On 30 Jul 2018, at 9:32am, Eric Grange wrote: > > > > > As these are cryptographic GUIDs, the first few bytes of a values are > in > > > practice unique, so in theory I can index

Re: [sqlite] Reducing index size

@Simon Slavin > Don't index using substr(). That would be slow because it has to keep working out substr(). I gave it a try, but that grows the size of the tables, and would require a full update of the key/value table, so not something that can be deployed without a lot of I/O. The substr()

Re: [sqlite] Reducing index size

On Mon, Jul 30, 2018 at 10:42 AM Simon Slavin wrote: > On 30 Jul 2018, at 9:32am, Eric Grange wrote: > > > As these are cryptographic GUIDs, the first few bytes of a values are in > > practice unique, so in theory I can index just the first few bytes (using > > substr()), > > this indeed

Re: [sqlite] Reducing index size

If I understand correctly then changing from a base64 index to a blob containing the raw bytes would save 25% Paul www.sandersonforensics.com SQLite Forensics Book On 30 July 2018 at 09:32, Eric Grange wrote: > Hi, > > Is there a way to reduce the

Re: [sqlite] Reducing index size

On 30 Jul 2018, at 9:32am, Eric Grange wrote: > As these are cryptographic GUIDs, the first few bytes of a values are in > practice unique, so in theory I can index just the first few bytes (using > substr()), > this indeed reduces in a much smaller index, but this also requires > adapting all

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

Oops, sent too early... On Mon, Jul 30, 2018 at 10:29 AM Dominique Devienne wrote: > On Mon, Jul 30, 2018 at 10:11 AM Eric Grange wrote: > >> @David Yip >> > If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you >> > can query the dbstat virtual table for the number of pages

[sqlite] Reducing index size

Hi, Is there a way to reduce the size of an index on strings/blobs ? I have tables which are a key + value, the key is an "integer primary key autoincrement", which is used for references in all other tables of the schema. The values are cryptographic GUIDs (so 256 to 512 bits in size) with a

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

On Mon, Jul 30, 2018 at 10:11 AM Eric Grange wrote: > @David Yip > > If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you > > can query the dbstat virtual table for the number of pages used per > table and index, e.g. > > Thanks, I did not know about that module, however it

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

@David Yip > If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you > can query the dbstat virtual table for the number of pages used per table and index, e.g. Thanks, I did not know about that module, however it is a bit slow, on a 7 GB database I have, it takes almost 5

Re: [sqlite] SQLite 3.24.0 Solaris 9 build failure

On 2018-07-28 08:33, Andy Goth wrote: SQLite 3.24.0 fails to build on Solaris 9 (a.k.a. Solaris 2.9) due to not finding fchmod, fchown, readlink, lstat, usleep, struct timeval, and gettimeofday. To correct, do not #define _XOPEN_SOURCE. There's already a check for Mac OS X, so I would suggest

Re: [sqlite] [EXTERNAL] virtual tables, xBestIndex/xFilter question

For your JOIN case, I would expect the following "conversation" (simplified and based on the original cost based QP, the NGQP may be doing something else) xBestIndex( table A, no constraints) -> Full table scan FTS(A), cost = CARD(A), count=CARD(A) xBestIndex( table A, OID) -> primary key

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

download sqlite3_analyzer from http://www2.sqlite.org/2018/sqlite-tools-linux-x86-324.zip On Mon, Jul 30, 2018 at 4:46 PM, Eric Grange wrote: > Hi, > > Is there a quick way (as in speed) to obtain the number of database blocks > (or kilobytes) allocated for each table and each index ? > >

Re: [sqlite] [EXTERNAL] Sqlite Sharding HOWTO

We almost exclusively use virtual tables in our application, and this includes virtual table code to access Faircom CTree files and in-memory data dictionaries. The structure (fields, indexes) of these tables is fixed (and identical for corresponding CTree and DD tables), with sharding achieved

Re: [sqlite] Quick way to figure SQLite database block size per table & indexes

Hi Eric, If you have the dbstat module enabled (-DSQLITE_ENABLE_DBSTAT_VTAB), you can query the dbstat virtual table for the number of pages used per table and index, e.g. SELECT name, COUNT(path) AS pages FROM dbstat GROUP BY name ORDER BY pages DESC; This runs in ~600 ms on one of my

[sqlite] Quick way to figure SQLite database block size per table & indexes

Hi, Is there a quick way (as in speed) to obtain the number of database blocks (or kilobytes) allocated for each table and each index ? I have been using various manual approaches so far (from using length() to vacuum and drops), but none of them are really fast or can practical to automate.