[sqlite] the default synchronous setting in WAL mode is "FULL", not "NORMAL"

http://sqlite.org/pragma.html#pragma_synchronous Says:?NORMAL is the default when in WAL mode.? But, sqlite3 e:\files\t.db SQLite version 3.8.8.3 2015-02-25 13:29:11 Enter ".help" for usage hints. sqlite> pragma journal_mode; wal sqlite> pragma synchronous; -- ??? 2 sq

[sqlite] Storing Images in Blobs - Best Practices

On 17 Feb 2016, at 10:23pm, Rooney, Joe wrote: > 1. The database table has four fields: Employee GUID (varchar), EmployeePic > (Blob), isPicAvail (Bool) and picDate (int). Store the BLOB field at the end of the row. This will make accessing the two fields currently after it faster. > I us

[sqlite] INSERT OR IGNORE query inside of a body of trigger, fired by INSERT OR REPLACE query, works as if it was INSERT OR REPLACE.

On 02/17/2016 08:47 PM, Paul wrote: > Let's say I have a following database structure: > > CREATE TABLE properties > ( > name TEXT NOT NULL, > value TEXT, > PRIMARY KEY(name) > ) WITHOUT ROWID; > > CREATE TABLE foo > ( > idTEXT NOT NULL, > PRIMARY

[sqlite] Storing Images in Blobs - Best Practices

I've wanted to use SQLite Blobs for storing images for some time, and a new update to an iOS app gave me the opportunity - our Corporate Directory mobile app. I had considered it a few years before but after reading some posts that suggested a better way was to store links in SQLite and the imag

[sqlite] MIN/MAX query

On Wed Feb 17, 2016 at 06:17:40PM +, David Bicking wrote: > I have a table > I? L? V1 1?? A1 2?? A1 3?? A1 4?? B1 5 ? B1 6?? A1 7?? A2 1 ? C2 2?? C The formatting of this (and your desired results) does not make the question clear enough. Can you resend with each row on a separate line and p

[sqlite] MIN/MAX query

On 02/17/2016 03:22 PM, nomad at null.net wrote: > On Wed Feb 17, 2016 at 06:17:40PM +, David Bicking wrote: >> I have a table >> I L V1 1 A1 2 A1 3 A1 4 B1 5 B1 6 A1 7 A2 1 C2 2 C > The formatting of this (and your desired results) does not make the > question clear enough.

[sqlite] A question

Create an r-tree index on poitable(poiid), the query time of your SQL will drop to 1ms. http://www.sqlite.org/rtree.html ???: ??? : ?2016/?2/?17 16:34 ???: sqlite-users at mailinglists.sqlite.org

[sqlite] MIN/MAX query

I have a table I? L? V1 1?? A1 2?? A1 3?? A1 4?? B1 5 ? B1 6?? A1 7?? A2 1 ? C2 2?? C I want to return the minimal and maximum L for each "group" of V in a given I. The result I want: I? MinL? MaxL? V1? 1??? 3? A1? 4??? 5?? B1? 6??? 7?? A2? 1??? 2

[sqlite] SQLite tuning advice for caching scenario

On 17 Feb 2016, at 4:27pm, Dave Baggett wrote: > One clarification: is it the case that transaction bundling ONLY affects > write/delete operations -- i.e., those operations that alter the database? A transaction must be created for read operations too. Else a database might be changed in th

[sqlite] INSERT OR IGNORE query inside of a body of trigger, fired by INSERT OR REPLACE query, works as if it was INSERT OR REPLACE.

Oh, thanks for pointing me to this statement! Didn't know that 'OR IGNORE' is an alias of 'ON CONFLICT IGNORE'. 17 February 2016, 17:32:32, by "Dan Kennedy" : > On 02/17/2016 08:47 PM, Paul wrote: > > Let's say I have a following database structure: > > > > CREATE TABLE properties > > ( > >

[sqlite] NuGet problem

David M Bennett wrote: > > "Could not install package 'sqlite.redist 3.8.4.2'. You are trying to > install this package into a project that targets > '.NETFramework,Version=v4.5', but the package does not contain any assembly > references or content files that are compatible with that framework.

[sqlite] test

eyax

[sqlite] Bug with AuxData in triggers; also feature requests

Because the AuxData stores only the line number in the program, this means that if a function that uses auxdata is called in two different trigger programs, it might interfere and give the wrong result. Possibly it can be fixed by storing the pointer into the program instead of the line number.

[sqlite] using fts5 under System.Data.SQLite 1.0.99.0 (3.9.2) package

Hi I am new to System.Data.SQLite. Under 1.0.99.0 (3.9.2), I could create virtual table using fts4 and then run full-text matching search through SQLiteConnection and SQLiteCommand without doing anything to "enable fts4". But when replacing "using fts4" with "using fts5" in Create Table statement

[sqlite] A question

I have a question: there are two tables: CREATE TABLE poiTable ( poiId INTEGER NOT NULL, catId INTEGER NOT NULL, mortonCode INTEGER NOT NULL, versionId INTEGER NOT NULL, iconSetId INTEGER , catIconSetId INTEGER , brandIconSetId INTEGER , regionId INTEGER , attrBitMask INTEGER , attrDisplayBitMa

[sqlite] SQLite tuning advice for caching scenario

Clarification to my own post: When I talk about buffering, say, 16MB of write transactions in memory, I want the effects transactions to be visible to readers immediately (once they are "commited" to RAM). This seems akin to WAL mode where SQLite scans the .wal file prior to scanning the .dat f

[sqlite] SQLite tuning advice for caching scenario

OK, that helps -- thank you. One clarification: is it the case that transaction bundling ONLY affects write/delete operations -- i.e., those operations that alter the database? Another clarification: is it the case that writes within a single transaction will remain in the in-memory page cache

[sqlite] tesing mail

testing mail

[sqlite] SQLite tuning advice for caching scenario

That's a great suggestion. One issue, though is that I'd have to run two FTS searches to search -- one on the disk-based database, and one on the memory-based one. I also already have the database split into 8 .dat files for scaling purposes. :) But this may be workable -- thanks. (BTW, I am us

[sqlite] SQLite tuning advice for caching scenario

On 17 Feb 2016, at 3:34pm, Simon Slavin wrote: > A lot of operations on the database file are done at the beginning and end of > every transaction. If your journal is in memory, then you can dramatically > disk usage by using large transactions. So I think you are right and you > should che

[sqlite] Bug (?) with DateTime in Windows Phone 8.1

Hello, Just to let you know that I found what we may call a bug, but not sure it is really one : When storing DateTime Winrt variable in a DateTime SQLite field, it seems that the date is actually stored as a string with the DateTime.ToString() method. To read it, it seems the engine uses DateT

[sqlite] INSERT OR IGNORE query inside of a body of trigger, fired by INSERT OR REPLACE query, works as if it was INSERT OR REPLACE.

Let's say I have a following database structure: CREATE TABLE properties ( ?name? TEXT NOT NULL, ?value TEXT, ?PRIMARY KEY(name) ) WITHOUT ROWID; CREATE TABLE foo ( ?id??? TEXT NOT NULL, ?PRIMARY KEY(id) ); CREATE TRIGGER foo_inserted ? AFTER INSE

[sqlite] SQLite tuning advice for caching scenario

On 17 Feb 2016, at 2:48pm, Dave Baggett wrote: > Regarding transactions, I'm bundling write operations into transactions, but > not optimally. If, say, I do a huge write as a single transaction, will that > cause SQLite to keep everything in memory until I COMMIT? Perhaps that's the > right s

[sqlite] Possible error using length on UTF-8 characters

On Wed, Feb 17, 2016 at 2:59 PM, Glyn Jones wrote: > Agreed. > The problem is that "length(id)" returns double the number of UTF-8 > characters, rather than the actual number input to the field using "insert". > According to the docs, i does that only if you've stored the data as a BLOB, not TEX

[sqlite] Possible error using length on UTF-8 characters

My code behaves differently: % package require sqlite3 3.8.11.1 % sqlite3 db :memory: % db eval {CREATE TABLE users(id,name);} % set id "???" ??? % db eval {insert into users (id, name) values (:id,:id)} % db eval {select hex(id) from users} C382C2ACC382C2ACC382C2AC % The terminal I'm using claims

[sqlite] SQLite tuning advice for caching scenario

I should have clarified: the problematic locking is happening in the OS layer. I've completely disable SQLite (thread) locking by building with SQLITE_THREADSAFE=0. (And, yes, I'm only using SQLite from a single thread!) Regarding transactions, I'm bundling write operations into transactions, bu

[sqlite] SQLite tuning advice for caching scenario

On 17 Feb 2016, at 2:37pm, Dave Baggett wrote: > I'm seeking specific advice on how to tune SQLite for this application when > deployed on a target with extremely poor write performance. On this target > writing in many small chunks is much more expensive than writing in a single > big sequen

[sqlite] Possible error using length on UTF-8 characters

On Wed, Feb 17, 2016 at 2:36 PM, Glyn Jones wrote: > The field is TEXT: > > sqlite> .schema operators > CREATE TABLE operators ( > uid INTEGER PRIMARY KEY, > id TEXT UNIQUE NOT NULL CHECK(length(id) BETWEEN 1 AND 20), > name TEXT CHECK(length(trim(n

[sqlite] SQLite tuning advice for caching scenario

My application uses SQLite to cache data. Specifically, the cache isn't the storage of record for anything; it's just a local copy of data that's stored remotely on servers of record. The cache primarily facilitates rapid searching of the data locally, via FTS. I'm seeking specific advice on ho

[sqlite] Possible error using length on UTF-8 characters

On Wed, Feb 17, 2016 at 11:53 AM, Glyn Jones wrote: > My database is configured with encoding "UTF-8". > I have a table with a constraint check for 20 characters on a column. This > mostly works, but sometimes has some strange behaviour. > > One character in particular has caused a problem. The h

[sqlite] Possible error using length on UTF-8 characters

> No. I think you are confused about how UTF8 works. > > The hexadecimal shown above is a 6-character string as follows: > > "\u00c2\u00ac\u00c2\u00ac\u00c2\u00ac" > > You are wanting this three-character string: > > "\uc2ac\uc283\uc283" > > The hex encoding of the string you want w

[sqlite] Possible error using length on UTF-8 characters

I set the schema to TEXT, but that apparently makes no difference! I'm using the TCL interface: set id "???" db eval {insert into users (id, name) values (:id, :name)} The documentation says the : ensures that the insert is done in such a way as to help against SQL injection, but there is nothi

[sqlite] Possible error using length on UTF-8 characters

Agreed. The problem is that "length(id)" returns double the number of UTF-8 characters, rather than the actual number input to the field using "insert". -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behal

[sqlite] Possible error using length on UTF-8 characters

> sqlite> select hex(id) from operators; > C382C2ACC382C2ACC382C2AC This is hex utf8 for "??", i.e. 3? 0xc2 0xac, which again is utf8 for "?" (0xac) S.M. -- Dipl.-Phys. (Univ) Stefan Meinlschmidt, Senior Software Engineer Am Wolfsmantel 46, 91058 Tennenlohe, Germany Tel: +49-8458-3332-531 s

[sqlite] Possible error using length on UTF-8 characters

sqlite> select hex(id) from operators; C382C2ACC382C2ACC382C2AC This should show three characters of values "C2AC", and when I read from the DB and display on an HTML page I do see " ???" It looks like additional data has been added? -Original Message- From: sqlite-users-bounces at mai

[sqlite] Possible error using length on UTF-8 characters

The field is TEXT: sqlite> .schema operators CREATE TABLE operators ( uid INTEGER PRIMARY KEY, id TEXT UNIQUE NOT NULL CHECK(length(id) BETWEEN 1 AND 20), name TEXT CHECK(length(trim(name)) > 0) ); Glyn.

[sqlite] Wish list: better ALTER TABLE for setting default

>> ALTER TABLE testTable SET DEFAULT FOR testField TO 0 > > Not all values in this column might actually be stored on disk. > Changing the default value could change those rows. it would be perfectly okay if you document this to either * apply the new default to all NULL values * not change exis

[sqlite] applyng schema changes to several databases

On Tue, Feb 16, 2016 at 9:51 PM, Roger Binns wrote: > The way I (and many others) do it is to use the user_version. It is > an integer that starts out as zero, and can be read or written with > pragma user_version. Startup code then looks like this: > > if user_version()==0 { > BEGIN; >

[sqlite] A question

??? wrote: > sql4:SELECT ... FROM ... WHERE b.tileId=557467343 AND ... > Execute sql4 consumes 500-600ms, returned 5847 records > > sql5:SELECT ... FROM ... WHERE b.tileId=67430683 AND ... > Execute sql5 consumes 500-600ms, returned 14 records > > the records returned of sql5 is less than sql4, but

[sqlite] Wish list: better ALTER TABLE for setting default

Christian Schmitz wrote: > I would like to see more alter table things. Currently, ALTER TABLE implements only those changes that can be done without rewriting any of the table data. > ALTER TABLE testTable SET DEFAULT FOR testField TO 0 Not all values in this column might actually be stored on

[sqlite] applyng schema changes to several databases

On 17 Feb 2016, at 11:38am, Luca Ferrari wrote: > > The next question is: do I have a way to conditionally place the > pragma test into an sql file to make sqlite apply it? No. You can store SQL commands in a table if you like, but there's no way to make SQLite automatically execute them dire

[sqlite] SQLITE_DISABLE_INTRINSIC missing from sqlite3Put4byte

Shouldn't sqlite3Put4byte have the same SQLITE_DISABLE_INTRINSIC guards that sqlite3Get4byte has?

[sqlite] Possible error using length on UTF-8 characters

My database is configured with encoding "UTF-8". I have a table with a constraint check for 20 characters on a column. This mostly works, but sometimes has some strange behaviour. One character in particular has caused a problem. The hex value is "C2AC" - the "NOT SIGN (U+00AC)". Hopefully the c

[sqlite] Small Doc Issue

in https://www.sqlite.org/c3ref/wal_hook.html and will those overwrite any prior sqlite3_wal_hook() settings should be and those will overwrite any prior sqlite3_wal_hook() settings no? --DD

[sqlite] applyng schema changes to several databases

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/02/16 03:38, Luca Ferrari wrote: > The above syntax> does not look familiar to me, It is made up to be concise and get the point across. > and in the meantime I've wrapped the user_version pragma get/set > into a shell script. You can't do if

[sqlite] Possible error using length on UTF-8 characters

On 2/17/16, Glyn Jones wrote: > My code behaves differently: > % package require sqlite3 > 3.8.11.1 > % sqlite3 db :memory: > % db eval {CREATE TABLE users(id,name);} > % set id "???" > ??? > % db eval {insert into users (id, name) values (:id,:id)} > % db eval {select hex(id) from users} > C382C2

[sqlite] whish list for 2016

> > DROP COLUMN and RENAME COLUMN are relatively easy for tables that lack > indexes, triggers, views, foreign key references, CHECK constraints, > and other constructs that might reference the dropped or renamed > column. Reliably finding every use of a column name and changing it > can be tric

[sqlite] Wish list: better ALTER TABLE for setting default

Hi, Sometimes I add columns to tables. But when I forget an option, I can't do much, but going back to backup copy or creating a copy of whole database with little change. e.g. a column misses default value. So I would like to see more alter table things. Maybe like this: ALTER TABLE testTabl

[sqlite] Possible error using length on UTF-8 characters

On 2/17/16, Glyn Jones wrote: > I set the schema to TEXT, but that apparently makes no difference! > > I'm using the TCL interface: > > set id "???" > db eval {insert into users (id, name) values (:id, :name)} > When I run this script: sqlite3 db :memory: db eval {CREATE TABLE users(id,name);} s

[sqlite] Possible error using length on UTF-8 characters

On 2/17/16, Richard Hipp wrote: > On 2/17/16, Glyn Jones wrote: >> sqlite> select hex(id) from operators; >> C382C2ACC382C2ACC382C2AC >> >> This should show three characters of values "C2AC", and when I read from >> the >> DB and display on an HTML page I do see " ???" >> > > No. I think you are

[sqlite] Possible error using length on UTF-8 characters

On 2/17/16, Glyn Jones wrote: > sqlite> select hex(id) from operators; > C382C2ACC382C2ACC382C2AC > > This should show three characters of values "C2AC", and when I read from the > DB and display on an HTML page I do see " ???" > No. I think you are confused about how UTF8 works. The hexadecima

[sqlite] Possible error using length on UTF-8 characters

On 2/17/16, Glyn Jones wrote: > My database is configured with encoding "UTF-8". > I have a table with a constraint check for 20 characters on a column. This > mostly works, but sometimes has some strange behaviour. > > One character in particular has caused a problem. The hex value is "C2AC" - >

[sqlite] SQLite tuning advice for caching scenario

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/02/16 08:20, Dave Baggett wrote: > One issue, though is that I'd have to run two FTS searches to > search -- one on the disk-based database, and one on the > memory-based one You see issues, I see features :-) The memory based cache would conta

[sqlite] SQLite tuning advice for caching scenario

Just FYI, FTS writes each transaction's index data in a segment, then does segment merges over time. So there's some advantage to bulk updates versus one-at-a-time updates in terms of index fragmentation and write overhead. Having an in-memory FTS table which you spill to the on-disk table(s) as a

[sqlite] SQLite tuning advice for caching scenario

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 17/02/16 06:37, Dave Baggett wrote: > I'd welcome any suggestions How about two databases? Create an in memory database for the cache. Then whenever it hits a certain size (eg 64MB) or time passed (eg 5 minutes), copy/move data from the memory da

[sqlite] Behavior When Comparing NULL Values

Others have explained the technicality of why you get the result - I would just like to point out that you should be careful of influencing your own expectation with the words you use in these statements. SQL only pretends to understand English, it really is a mathematical/logical language and t

[sqlite] Bug: writing to a database in shared cache mode doesn't work if same database was opened in readonly mode previously

On 16 Feb 2016, at 3:04pm, Ludger Kr?mer wrote: > I connect to the same database file twice, first in readonly mode and > then in readwrite mode while having set sqlite3_enable_shared_cache(1). > > Any attempt to change any data using the second database connection results > in an "attempt to

[sqlite] Documentation Typo

There'll be fights about that. I'd suggest "a database is used to store". On Tue, Feb 16, 2016 at 11:16 AM, Chris Malumphy wrote: > On https://www.sqlite.org/datatypes.html there is a typographical error. > > In the first paragraph of 1.0 Typelessness > > "A database is suppose to store and ret

[sqlite] Behavior When Comparing NULL Values

On 17 Feb 2016, at 3:33am, Denis Burke wrote: > select case when null not in ('a') then 'not in list' else 'is in list' end; > > This gives "is in list". But since null is clearly not in the list of one > member ('a'), I would expect this to generate "not in the list". > > Is this expected be