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

[Quan Yong Zhai]

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

sqlite> .open e:\\files\\t1.db
sqlite> pragma journal_mode;
delete
sqlite> pragma journal_mode=wal;
wal
sqlite> pragma synchronous;
2
sqlite>


Sent from Mail for Windows 10

[sqlite] Storing Images in Blobs - Best Practices

[Simon Slavin]

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 use the same UPDATE call, as above in 4. I don't delete the previous Blob, 
> just assuming that SQLite will take care of those details.

It should do.

> The process works well except that occasionally two images end up getting 
> corrupted. Not just one, it always happens in pairs. I get the upper part of 
> both images, with just the lower part showing garbage.

Run PRAGMA integrity_check() on the database and tell us if it finds errors.

If possible copy the database to a computer and download and use the analysis 
utility from the SQLite download page.

> The questions:
> 
>  *   Is this general approach wise?
>  *   Should I clear the old Blob before rewriting the new image, or should I 
> simply delete the old rec and write a new one?
>  *   Should I store the mime string coming back in the JSON return rather 
> than converting the string to an image, and then converting it every time we 
> need to display the image.
>  *   Should I simply store a string link to the image and load that whenever 
> it is needed.

Yes.
No, you should be able to just rewrite it.
You wrote you were storing the NSData.  That's not just the bytes of the image 
itself.  It doesn't matter which you do but be clear which you're doing.  
Storing MIME is probably a waste of space.
I think you should store the content, not a link.

Questions from me:

What language are you writing in ?
What API are you using to access SQLite ?
Check the first few bytes of a BLOB and be certain that they are the first few 
bytes of what would be in an image file stored on disk.
How are you writing your BLOB ?  Are you writing the whole field at once or are 
you using the specialist BLOB calls ?

Simon.

[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.

[Dan Kennedy]

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 KEY(id)
> );
>
> CREATE TRIGGER foo_inserted
>AFTER INSERT ON foo
>   BEGIN
> INSERT OR IGNORE INTO properties(name, value) VALUES('foo_inserts', 0);
> UPDATE properties SET value = value + 1 WHERE name = 'foo_inserts';
>   END;
>
> With a clean database I perform set of queries:
>
> INSERT OR REPLACE INTO foo(id) VALUES(1);
> INSERT OR REPLACE INTO foo(id) VALUES(2);
> INSERT OR REPLACE INTO foo(id) VALUES(3);
>
...
> I've made different test cases and came to a conclusion that 'OR IGNORE' 
> clause inside
> a query within a body of trigger suddenly works as if it was 'OR REPLACE'.

The ON CONFLICT clause of the outer statement overrides the ON CONFLICT 
clause of the statement within the trigger:

https://www.sqlite.org/mark/lang_createtrigger.html?However+if+an*used+instead

(scroll the page down a bit to see the highlighted statement)

Dan.

[sqlite] Storing Images in Blobs - Best Practices

[Rooney, Joe]

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 images as normal 
files, I avoided the process. Now, I'm doing it in Blobs.

However, there have been some hiccups - occasional corrupted data - so I'd like 
to get this group's collective thought on best practices.

I will outline the process without code, which I'll post later if there is a 
request to do so.


  1.  The database table has four fields: Employee GUID (varchar), EmployeePic 
(Blob), isPicAvail (Bool) and picDate (int).
  2.  When a employee is selected from a search list, a new record is added to 
the table with only the GUID and a NO for the isPicAvail.
  3.  If there is connectivity to our intranet via VPN, we send out a web 
service call to get the employee's photo from SharePoint, async. If no 
connectivity, we will batch it when connected.
  4.  When a image comes back as a mime string, it is converted to NSData and 
stored using UPDATE into the Blob field, along with the unix time in picDate, 
and we change the isPicAvail to YES.
  5.  Whenever the app is restarted with connectivity, I do a refresh all 
photos, which walks the table and fetches images for every record, regardless 
of whether there is one already stored. I use the same UPDATE call, as above in 
4. I don't delete the previous Blob, just assuming that SQLite will take care 
of those details. NOTE: this process will be optimized later, using modified 
dates to fetch photos only when needed.

The process works well except that occasionally two images end up getting 
corrupted. Not just one, it always happens in pairs. I get the upper part of 
both images, with just the lower part showing garbage.

The questions:

  *   Is this general approach wise?
  *   Should I clear the old Blob before rewriting the new image, or should I 
simply delete the old rec and write a new one?
  *   Should I store the mime string coming back in the JSON return rather than 
converting the string to an image, and then converting it every time we need to 
display the image.
  *   Should I simply store a string link to the image and load that whenever 
it is needed.

Any thoughts are gratefully accepted.


Joseph Rooney
Team Lead - Mobile Development

CommScope, Inc.
Hickory, NC
828-315-2920 Ext: 52920

[sqlite] MIN/MAX query

[no...@null.net]

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 perhaps the text "NULL" to represent empty values?

Mark
-- 
Mark Lawrence

[sqlite] MIN/MAX query

[David Bicking]

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.  Can you resend with each row on a separate line
> and perhaps the text "NULL" to represent empty values?
>
> Mark

Each row was on a separate line. Why it got squished on to one line is 
beyond me.
Nor do I know how to make it not be squished.

But thanks for looking anyway.

David

[sqlite] A question

[Quan Yong Zhai]

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] 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 , attrDisplayBitMask INTEGER , attributeBlob BLOB ,
 primary key (poiId, catId)
);

table index:
CREATE INDEX idx_poiTable_0 ON poiTable(catId);

CREATE TABLE poiVirtualTileTable (tileId INTEGER NOT NULL, minId INTEGER NOT 
NULL, maxId INTEGER NOT NULL, versionId INTEGER NOT NULL, isDirty INTEGER NOT 
NULL,
 primary key (tileId, minId)
);

The total number of records of poiTable is 383826;
The total number of records of poiVirtualTileTable is 9791;

sql1:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM poiTable a WHERE a.catId IN(8449,8450,8452)
Execute sql1 consumes 500-600ms, returned 20232 records

sql2:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM poiTable a,poiVirtualTileTable b WHERE a.poiId >= b.minId AND a.poiId <= 
b.maxId AND b.tileId = 557467343
Execute sql2 , returned 157 records

sql3:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM poiTable a,poiVirtualTileTable b WHERE a.poiId >= b.minId AND a.poiId <= 
b.maxId AND b.tileId = 67430686
Execute sql3 , returned 92231 records

sql4:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM nds111004.poiTable a,nds111004.poiVirtualTileTable b WHERE a.poiId >= 
b.minId AND a.poiId <= b.maxId AND b.tileId=557467343 AND a.catId 
IN(8449,8450,8452)
Execute sql4 consumes 500-600ms, returned 5847 records

sql5:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM nds111004.poiTable a,nds111004.poiVirtualTileTable b WHERE a.poiId >= 
b.minId AND a.poiId <= b.maxId AND b.tileId=67430683 AND a.catId 
IN(8449,8450,8452)
Execute sql5 consumes 500-600ms, returned 14 records

question:
the records returned of sql5 is less than sql4, but the time is almost the same 
?
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] MIN/MAX query

[David Bicking]

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?? C
I know I can't just do 
?? SELECT I, MIN(L) as MinL, MAX(L) as MaxL, V? FROM T
? GROUP BY I, Vsince that would return min/max of 1 and 7 for A, instead of the 
2 lines I need.
So should I word the SELECT statement?
Thanks,David

[sqlite] SQLite tuning advice for caching scenario

[Simon Slavin]

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 the middle of one giving you an inconsistent set of rows.

> Another clarification: is it the case that writes within a single transaction 
> will remain in the in-memory page cache until COMMIT is issued?

No, but you can get close to this by holding your journal in memory.

> I see various pragmas like cache_spill that seem to control the page cache 
> but I'm confused as to what they do.

Ignore them all.  They don't understand SQLite.

> Finally, is there a way to tell SQLite I would like to buffer, say, 16MB of 
> write/delete transactions in memory before the transactions are written to 
> disk?

No.  But you can use



and turn "PRAGMA synchronous = OFF".

Simon.

[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.

[Paul]

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
> > (
> >   name  TEXT NOT NULL,
> >   value TEXT,
> >   PRIMARY KEY(name)
> > ) WITHOUT ROWID;
> >
> > CREATE TABLE foo
> > (
> >   idTEXT NOT NULL,
> >   PRIMARY KEY(id)
> > );
> >
> > CREATE TRIGGER foo_inserted
> >AFTER INSERT ON foo
> >   BEGIN
> > INSERT OR IGNORE INTO properties(name, value) VALUES('foo_inserts', 0);
> > UPDATE properties SET value = value + 1 WHERE name = 'foo_inserts';
> >   END;
> >
> > With a clean database I perform set of queries:
> >
> > INSERT OR REPLACE INTO foo(id) VALUES(1);
> > INSERT OR REPLACE INTO foo(id) VALUES(2);
> > INSERT OR REPLACE INTO foo(id) VALUES(3);
> >
> ...
> > I've made different test cases and came to a conclusion that 'OR IGNORE' 
> > clause inside
> > a query within a body of trigger suddenly works as if it was 'OR REPLACE'.
> 
> The ON CONFLICT clause of the outer statement overrides the ON CONFLICT 
> clause of the statement within the trigger:
> 
> https://www.sqlite.org/mark/lang_createtrigger.html?However+if+an*used+instead
> 
> (scroll the page down a bit to see the highlighted statement)
> 
> Dan.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] NuGet problem

[Joe Mistachkin]

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. For
> more information, contact the package author." 
> 

That package still needs to be updated to deal with the NuGet 3.0 changes
for
packages containing native binaries.  In the meantime, is it possible for
you
to use the System.Data.SQLite.Core package to obtain the
"SQLite.Interop.dll"
files and P/Invoke against those?

--
Joe Mistachkin

[sqlite] test

[Stayros Mpampis]

eyax

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

[sql...@zzo38computer.org]

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.

I also have some feature request having to do with virtual tables.

One is allowing a virtual table implementation to consume LIMIT/OFFSET clauses. 
This may be useful when retrieving data from internet.

Other is to allow batch UPDATE/DELETE operations on virtual tables. I have some 
ideas about how to implement this.
One is to add to the index info structure, the operation code and the update 
mask; this optimization is only performed if the implementation is capable of 
consuming the entire WHERE clause.
Therefore, only if all of the records being updated/deleted are in the cursor 
being accessed.
Another is having extra methods (optional) of virtual table module, which can 
be used to implement these operations.
After xBestIndex then, after opening the cursor it might call xBatchUpdate or 
something like that.

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

[Ian Chen]

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, I got a couldn't find fts5 module exception. Do I need to do
anything to enable fts5 and how? Or I have to follow the instruction issued
by Sqlite.org with regard to build a fts5 loadable extension dll by myself.

Thanks to anyone replying to me in advance.

-- 
Ian Chen

[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 , attrDisplayBitMask INTEGER , attributeBlob BLOB , 
 primary key (poiId, catId)
);

table index:
CREATE INDEX idx_poiTable_0 ON poiTable(catId);

CREATE TABLE poiVirtualTileTable (tileId INTEGER NOT NULL, minId INTEGER NOT 
NULL, maxId INTEGER NOT NULL, versionId INTEGER NOT NULL, isDirty INTEGER NOT 
NULL, 
 primary key (tileId, minId)
);

The total number of records of poiTable is 383826;
The total number of records of poiVirtualTileTable is 9791;

sql1:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM poiTable a WHERE a.catId IN(8449,8450,8452)
Execute sql1 consumes 500-600ms, returned 20232 records

sql2:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM poiTable a,poiVirtualTileTable b WHERE a.poiId >= b.minId AND a.poiId <= 
b.maxId AND b.tileId = 557467343
Execute sql2 , returned 157 records

sql3:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM poiTable a,poiVirtualTileTable b WHERE a.poiId >= b.minId AND a.poiId <= 
b.maxId AND b.tileId = 67430686
Execute sql3 , returned 92231 records

sql4:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM nds111004.poiTable a,nds111004.poiVirtualTileTable b WHERE a.poiId >= 
b.minId AND a.poiId <= b.maxId AND b.tileId=557467343 AND a.catId 
IN(8449,8450,8452)
Execute sql4 consumes 500-600ms, returned 5847 records

sql5:SELECT 
a.poiId,a.catId,a.mortonCode,a.iconSetId,a.catIconSetId,a.brandIconSetId,a.attrBitMask,a.attrDisplayBitMask,a.attributeBlob
 FROM nds111004.poiTable a,nds111004.poiVirtualTileTable b WHERE a.poiId >= 
b.minId AND a.poiId <= b.maxId AND b.tileId=67430683 AND a.catId 
IN(8449,8450,8452)
Execute sql5 consumes 500-600ms, returned 14 records

question:
the records returned of sql5 is less than sql4, but the time is almost the same 
?

[sqlite] SQLite tuning advice for caching scenario

[Dave Baggett]

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 file on SELECTs.

(BTW, I'm using WAL mode and have found it performs better than standard 
journal mode on pretty much every target device.)

Dave

Sent with inky

"Dave Baggett"  wrote:



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 until COMMIT is issued? I see various 
pragmas like cache_spill that seem to control the page cache but I'm confused 
as to what they do.



Finally, is there a way to tell SQLite I would like to buffer, say, 16MB of 
write/delete transactions in memory before the transactions are written to 
disk? Here I am talking about the meta level above the transaction level -- I 
have atomic transactions and I want to defer physically writing them until I 
have enough of them (say, 16MB worth of altered pages).



Dave

[sqlite] SQLite tuning advice for caching scenario

[Dave Baggett]

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 until COMMIT is issued? I see various 
pragmas like cache_spill that seem to control the page cache but I'm confused 
as to what they do.

Finally, is there a way to tell SQLite I would like to buffer, say, 16MB of 
write/delete transactions in memory before the transactions are written to 
disk? Here I am talking about the meta level above the transaction level -- I 
have atomic transactions and I want to defer physically writing them until I 
have enough of them (say, 16MB worth of altered pages).

Dave

Sent with inky

"Simon Slavin"  wrote:



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 check out that strategy.



I'm sorry, that's poorly phrased and has a word missing.  Here's a better 
version:



A high proportion of the disk activity involved in making changes to the 
database are done to support the transaction structure, rather than the 
individual operation (INSERT/UPDATE/DELETE) you asked for.  Grouping lots of 
operations together into one transaction will reduce the overhead needed for 
locking and ACID.  In addition, in some journal modes operations relating to 
the transaction as a whole are done with the database file whereas much of the 
work relating to the operations is done with the journal file.  This should 
increase the advantage in your situation gained by using large transactions.



Simon.

___

sqlite-users mailing list

sqlite-users at mailinglists.sqlite.org

http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] tesing mail

[王庆刚]

testing mail

[sqlite] SQLite tuning advice for caching scenario

[Dave Baggett]

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 using SQLite via apsw -- thanks 
for that too!)

Dave

Sent with inky

"Roger Binns"  wrote:

-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 database to the persistent

(disk) one.  This ensures the writes to the disk database are in big

chunks.



Roger

-BEGIN PGP SIGNATURE-

Version: GnuPG v2



iEYEARECAAYFAlbEm9AACgkQmOOfHg372QR9rwCgu+MPM+kJEGYlBTzkKRYhHsOu

U98AoOZ4kBue7MV6Q8P9+vkljyJGobVu

=61/4

-END PGP SIGNATURE-

___

sqlite-users mailing list

sqlite-users at mailinglists.sqlite.org

http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite tuning advice for caching scenario

[Simon Slavin]

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 check out that strategy.

I'm sorry, that's poorly phrased and has a word missing.  Here's a better 
version:

A high proportion of the disk activity involved in making changes to the 
database are done to support the transaction structure, rather than the 
individual operation (INSERT/UPDATE/DELETE) you asked for.  Grouping lots of 
operations together into one transaction will reduce the overhead needed for 
locking and ACID.  In addition, in some journal modes operations relating to 
the transaction as a whole are done with the database file whereas much of the 
work relating to the operations is done with the journal file.  This should 
increase the advantage in your situation gained by using large transactions.

Simon.

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

[Lionel Tranchand / FH SARL]

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 DateTime.Parse() method.

The problem is with user langage switch :

If the user choose the indonesian langage as the system langage and stores a 
date time with the local format, the time will be stored as a string as 12.00 
for example, not 12:00.
If the user then read this data, still with indonesian language as the system 
language, no problem : the DateTime.Parse() method will works.
But if the user change the system language to english for example, then the 
DateTime.Parse() method will failed and and makes the app crash as for it, the 
time format is HH:MM, not HH.MM.

In my case, I just abandoned the DateTime format and now stores date with 
SQLite long fields using DateTime.ToBinary() and DateTime.FromBinary() methods?

Hope this should helps?

Best Regards,

Lionel Tranchand  - Mobile applications developer
FH SARL - 87, rue de S?ze - 69006 Lyon - France
www.fhsarl.com

[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.

[Paul]

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 INSERT ON foo 
?BEGIN 
?? INSERT OR IGNORE INTO properties(name, value) VALUES('foo_inserts', 0); 
?? UPDATE properties SET value = value + 1 WHERE name = 'foo_inserts'; 
?END; 

With a clean database I perform set of queries: 

INSERT OR REPLACE INTO foo(id) VALUES(1); 
INSERT OR REPLACE INTO foo(id) VALUES(2); 
INSERT OR REPLACE INTO foo(id) VALUES(3); 

SELECT * FROM foo; 
id??? 
-- 
1 
2 
3 

SELECT * FROM properties; 
name value 
---? -- 
foo_inserts? 1? 

The results are not very much expected. If I try different example, again with 
a clean database I get: 

INSERT INTO foo(id) VALUES(1); 
INSERT INTO foo(id) VALUES(2); 
INSERT INTO foo(id) VALUES(3); 

SELECT * FROM foo; 
id??? 
-- 
1 
2 
3? 

SELECT * FROM properties; 
name value 
---? -- 
foo_inserts? 3 

Everything works perfectly. This is also true for INSERT OR IGNORE INTO 
foo. 

I've made different test cases and came to a conclusion that 'OR IGNORE' clause 
inside 
a query within a body of trigger suddenly works as if it was 'OR REPLACE'. 

[sqlite] SQLite tuning advice for caching scenario

[Simon Slavin]

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 strategy for me to pursue.

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 check out that strategy.

I've forgotten whether you already said which journal mode you are using or 
not, but there is a big difference in journal usage between WAL mode and the 
original mode.  Benchmarking between these two modes would be another good test 
for you.  Unfortunately I have no idea which option would be better for you but 
there are people here with more experience of these differences than I have and 
I hope some might help.

> If there is a document (or even section of the SQLite source) that I could 
> read to fully understand where the transition from memory (page cache, etc.) 
> to disk occurs, that would probably get me above n00b level of understanding, 
> which would help.

I don't think it's gathered together at one point.  For traditional mode you 
might find section 4 of



useful.  Sorry but it mixes information at the level you want with information 
far more details than you need.  The WAL information is gathered and



explains a lot about how WAL mode uses the journal file.  

Simon.

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

[Stephan Beal]

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 TEXT. You need to confirm that you haven't stored the field as a
blob.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

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

[Glyn Jones]

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 to be UTF-8 encoded.
Is this likely to be the sqlite version? 

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: 17 February 2016 14:42
To: SQLite mailing list
Subject: Re: [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);}
set id "???"
set name whatever
db eval {insert into users (id, name) values (:id, :name)} puts [db eval 
{SELECT hex(id) FROM users}]

The output I see is "C2ACC2ACC2AC", not "C382C2ACC382C2ACC382C2AC".

--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite tuning advice for caching scenario

[Dave Baggett]

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, 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 strategy for me to pursue.

If there is a document (or even section of the SQLite source) that I could read 
to fully understand where the transition from memory (page cache, etc.) to disk 
occurs, that would probably get me above n00b level of understanding, which 
would help.

Dave

Sent with inky

"Simon Slavin"  wrote:



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 sequential chunk. In particular, the write syscall is very expensive, 
> because frequent writes cause the host to spend huge amounts of time in lock 
> contention, because the locking strategy is very coarse.



The vast majority of time spent writing is not in the change you asked to make 
(INSERT, UPDATE, etc.) but in the measures made to handle the transaction: 
locking, ensuring ACID, etc..



Are you doing multiple write commands in one chunk of time ?  If so, are you 
enclosing them in a single transaction ?



Simon.

___

sqlite-users mailing list

sqlite-users at mailinglists.sqlite.org

http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite tuning advice for caching scenario

[Simon Slavin]

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 sequential chunk. In particular, the write syscall is very expensive, 
> because frequent writes cause the host to spend huge amounts of time in lock 
> contention, because the locking strategy is very coarse.

The vast majority of time spent writing is not in the change you asked to make 
(INSERT, UPDATE, etc.) but in the measures made to handle the transaction: 
locking, ensuring ACID, etc..

Are you doing multiple write commands in one chunk of time ?  If so, are you 
enclosing them in a single transaction ?

Simon.

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

[Stephan Beal]

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(name)) > 0)
> );
>

FWIW, the _declared_ type means very little in sqlite - it can store _any_
type in that field, regardless of the declaration. You might want to add a
type check to your CHECK() constraint. i don't currently remember how to do
that, but someone on this list does and will likely answer very soon. (Yes,
i'm looking at you, Simon!)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] SQLite tuning advice for caching scenario

[Dave Baggett]

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 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 sequential chunk. In particular, the write syscall is very expensive, 
because frequent writes cause the host to spend huge amounts of time in lock 
contention, because the locking strategy is very coarse.

Given that the data I am storing in SQLite is expendable -- I can always fetch 
it again from the server -- I don't really care if I lose, say, the last 30 
seconds of data written if the machine loses power, the app is killed by the 
host operating system, etc. However, I want to avoid the database going 
corrupt, since that requires fetching everything again.

It seems like an optimal strategy for this would be to keep a WAL-like journal 
in memory. I have plenty of RAM so 64MB for an in-memory WAL "file" would work. 
However, I don't see any way to tell SQLite to use WAL mode but keep the WAL 
file in memory. I also believe from reading the docs that if the memory-based 
WAL file is lost (e.g., loss of power) then the database will be corrupt.

I've tried journal_mode=MEMORY but that didn't seem to help any. It seems that 
no matter what pragmas I use, I can't convince SQLite to keep transactions in 
memory very long.

What I need, I guess, is some tuning parameter -- or I can write my own VFS -- 
that buffers entire transactions, then periodically flushes large groups of 
transactions at once, minimizing the number of write calls.

I'd welcome any suggestions from SQLite experts on this.

Dave

Sent with inky

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

[Stephan Beal]

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 hex value is "C2AC"
> - the "NOT SIGN (U+00AC)".
> Hopefully the character will appear between the quotes "?".
>
> When I insert this character into the table, it will only allow ten
> characters to be input.
>
> If I do "select length(name) from myTable;" the result shows that each of
> the "C2AC" characters  is counted as two characters.
>
> I noticed that "C2" is a valid ANSI character, as is "AC", so wonder if
> the length() function is not dealing correctly with the range from
> 0x80-0xff.
>

Is your field TEXT or BLOB? The docs say they behave differently:

https://www.sqlite.org/lang_corefunc.html

For a string value X, the length(X) function returns the number of
characters (not bytes) in X prior to the first NUL character. Since SQLite
strings do not normally contain NUL characters, the length(X) function will
usually return the total number of characters in the string X. For a blob
value X, length(X) returns the number of bytes in the blob.



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

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

[Meinlschmidt Stefan]

> 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 would be:
> 
>  "EC8AACEC8AACEC8AAC"

I suspect that someone confused Code Points (=characters, letters) with
encoded bytes in memory, leading to double encode. The canonical
recommended read for that topic would be
http://www.joelonsoftware.com/articles/Unicode.html

S.M.
-- 
Dipl.-Phys. (Univ) Stefan Meinlschmidt, Senior Software Engineer
Am Wolfsmantel 46, 91058 Tennenlohe, Germany
Tel: +49-8458-3332-531  stefan.meinlschmidt at esolutions.de
Fax: +49-8458-3332-20-531

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

[Glyn Jones]

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 nothing about how to force it to 
insert as text instead of a blob.
(https://www.sqlite.org/tclsqlite.html#eval)

Is there a way to enforce this?


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephan Beal
Sent: 17 February 2016 14:09
To: SQLite mailing list
Subject: Re: [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 TEXT. You need to confirm that you haven't stored the field as a blob.

--
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those 
who insist on a perfect world, freedom will have to do." -- Bigby Wolf 
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Glyn Jones]

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 Behalf Of Meinlschmidt 
Stefan
Sent: 17 February 2016 13:57
To: sqlite-users at mailinglists.sqlite.org
Subject: Re: [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  stefan.meinlschmidt at esolutions.de
Fax: +49-8458-3332-20-531
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Meinlschmidt Stefan]

> 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  stefan.meinlschmidt at esolutions.de
Fax: +49-8458-3332-20-531

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

[Glyn Jones]

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 mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: 17 February 2016 13:39
To: SQLite mailing list
Subject: Re: [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" - the "NOT SIGN (U+00AC)".
> Hopefully the character will appear between the quotes "?".
>
> When I insert this character into the table, it will only allow ten 
> characters to be input.
>
> If I do "select length(name) from myTable;" the result shows that each 
> of the "C2AC" characters  is counted as two characters.
>

What does "select hex(name) FROM myTable" show?



--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Glyn Jones]

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

[Christian Schmitz]

>> 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 existing rows. Than I do the UPDATE Statement myself.

> It is actually possible to do this, if you force all rows to be
> rewritten first, and then change the table definition:
> http://stackoverflow.com/questions/25911191/altering-a-sqlite-table-to-add-a-timestamp-column-with-default-value

You really want us to modify the Table Definitions?


Sincerely
Christian

-- 
Read our blog about news on our plugins:

http://www.mbsplugins.de/

[sqlite] applyng schema changes to several databases

[Luca Ferrari]

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;
> CREATE TABLE ;
> CREATE INDEX ;
> pragma user_version=1;
> COMMIT;
> }


Thank you guys for opening my mind on the usage of pragmas.
The next question is: do I have a way to conditionally place the
pragma test into an sql file to make sqlite apply it? The above syntax
does not look familiar to me, and in the meantime I've wrapped the
user_version pragma get/set into a shell script.

Thanks,
Luca

[sqlite] A question

[Clemens Ladisch]

??? 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 the time is almost the 
> same ?

The time depends not only on the number of records returned, but also
on the number of records that need to be read from disk, and searched
through.  Searching through lots of records that end up not matching
does not reduce the time.


Regards,
Clemens

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

[Clemens Ladisch]

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 disk.
Changing the default value could change those rows.

It is actually possible to do this, if you force all rows to be
rewritten first, and then change the table definition:
http://stackoverflow.com/questions/25911191/altering-a-sqlite-table-to-add-a-timestamp-column-with-default-value

> ALTER TABLE testTable SET UNIQUE FOR testField TO 1

You can already do the same with an index.
(Renaming that index to "sqlite_autoindex_testTable_n" and adjusting the
table definition is left as an exercise.)


Regards,
Clemens

[sqlite] applyng schema changes to several databases

[Simon Slavin]

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 directly.  You have to have your 
software do it.

Simon.

[sqlite] SQLITE_DISABLE_INTRINSIC missing from sqlite3Put4byte

[JD]

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

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

[Glyn Jones]

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 character will appear between the quotes "?".

When I insert this character into the table, it will only allow ten characters 
to be input.

If I do "select length(name) from myTable;" the result shows that each of the 
"C2AC" characters  is counted as two characters.

I noticed that "C2" is a valid ANSI character, as is "AC", so wonder if the 
length() function is not dealing correctly with the range from 0x80-0xff.

sqlite> .version
SQLite 3.8.11.1 2015-07-29 20:00:57 cf538e2783e468bbc25e7cb2a9ee64d3e0e80b2f

Has anyone else seen this problem, or found a solution?

Thanks,

Glyn.

[sqlite] Small Doc Issue

[Dominique Devienne]

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

[Roger Binns]

-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 statements in SQL (beyond CASE), so a pure SQL
solution isn't reasonable.

While you can (heroically) do everything in shell script, I'd
recommend you use a higher level language for easier testing, clearer
semantics etc.  For example any of TCL, Python, Perl, Ruby, PHP will
meet your needs.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAlbEuLcACgkQmOOfHg372QTd1gCgh0B5UO/Rb6zEnK3US0+V4oWz
GYIAoJX31S2pg73eTtTsKiBT9whwofF/
=Ximu
-END PGP SIGNATURE-

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

[Richard Hipp]

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}
> C382C2ACC382C2ACC382C2AC
> %
>

You must be running on windows.  This is a case of TCL converting the
content of the script file as it reads it in to execute it.

I suggest you work around the problem by using only ASCII characters
in your TCL script, and using \u escapes for any unicode
characters you need.

To verify, try adding "puts [string length $id]" to the script, and
see that it does indeed show $id set to a 6-character string.

package require sqlite3
sqlite3 db :memory:
db eval {CREATE TABLE users(id,name);}
set id "???"
puts [string length $id]
set name whatever
db eval {insert into users (id, name) values (:id, :name)}
puts [db eval {SELECT hex(id) FROM users}]


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] whish list for 2016

[Christian Schmitz]

> 
> 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 tricky.  The problem is more acute for dropping a column - what
> do you do then, change each reference to NULL?

it may be okay to say:

If it is referenced, we may live with an error.

"Can't drop column xxx as it is used in trigger yyy."


Most columns for us are not referenced in any way. Just data columns.

Sincerely
Christian

-- 
Read our blog about news on our plugins:

http://www.mbsplugins.de/

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

[Christian Schmitz]

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 testTable SET DEFAULT FOR testField TO 0

or

ALTER TABLE testTable SET UNIQUE FOR testField TO 1


Sincerely
Christian

-- 
Read our blog about news on our plugins:

http://www.mbsplugins.de/

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

[Richard Hipp]

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);}
set id "???"
set name whatever
db eval {insert into users (id, name) values (:id, :name)}
puts [db eval {SELECT hex(id) FROM users}]

The output I see is "C2ACC2ACC2AC", not "C382C2ACC382C2ACC382C2AC".

-- 
D. Richard Hipp
drh at sqlite.org

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

[Richard Hipp]

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 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"

Upon re-reading, I see that you really want this string:

   "\u00ac\u00ac\u00ac"

The UTF-8 encoding there is:

  "C2ACC2ACC2AC"

There way, this is not what you have stored in your table.

>
> The hex encoding of the string you want would be:
>
>  "EC8AACEC8AACEC8AAC"
>
> --
> D. Richard Hipp
> drh at sqlite.org
>


-- 
D. Richard Hipp
drh at sqlite.org

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

[Richard Hipp]

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 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 would be:

 "EC8AACEC8AACEC8AAC"

-- 
D. Richard Hipp
drh at sqlite.org

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

[Richard Hipp]

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" -
> the "NOT SIGN (U+00AC)".
> Hopefully the character will appear between the quotes "?".
>
> When I insert this character into the table, it will only allow ten
> characters to be input.
>
> If I do "select length(name) from myTable;" the result shows that each of
> the "C2AC" characters  is counted as two characters.
>

What does "select hex(name) FROM myTable" show?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] SQLite tuning advice for caching scenario

[Roger Binns]

-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 contain the most recently cached "fresh"
information, and probably what is of most interest to the user.  So
the FTS searches for that will be very fast which is great, while
older information is searched at "normal" speeds.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAlbEoW4ACgkQmOOfHg372QST2wCdGgvbncjSo4B2FDxAZYQye3E0
TlIAoIKR+X4rhdwSUeOD5TVTchA83GT7
=vMsO
-END PGP SIGNATURE-

[sqlite] SQLite tuning advice for caching scenario

[Scott Hess]

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
big transaction can work pretty well.

Note that big transactions can have their own issues, for instance if you
end up having to spill the page cache.

[Obviously, you'll want to test this for your case.]

-scott


On Wed, Feb 17, 2016 at 8:20 AM, Dave Baggett  wrote:

> 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 using SQLite via apsw --
> thanks for that too!)
>
> Dave
>
> Sent with inky
>
> "Roger Binns"  wrote:
>
> -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 database to the persistent
>
> (disk) one.  This ensures the writes to the disk database are in big
>
> chunks.
>
>
>
> Roger
>
> -BEGIN PGP SIGNATURE-
>
> Version: GnuPG v2
>
>
>
> iEYEARECAAYFAlbEm9AACgkQmOOfHg372QR9rwCgu+MPM+kJEGYlBTzkKRYhHsOu
>
> U98AoOZ4kBue7MV6Q8P9+vkljyJGobVu
>
> =61/4
>
> -END PGP SIGNATURE-
>
> ___
>
> sqlite-users mailing list
>
> sqlite-users at mailinglists.sqlite.org
>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQLite tuning advice for caching scenario

[Roger Binns]

-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 database to the persistent
(disk) one.  This ensures the writes to the disk database are in big
chunks.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v2

iEYEARECAAYFAlbEm9AACgkQmOOfHg372QR9rwCgu+MPM+kJEGYlBTzkKRYhHsOu
U98AoOZ4kBue7MV6Q8P9+vkljyJGobVu
=61/4
-END PGP SIGNATURE-

[sqlite] Behavior When Comparing NULL Values

[R Smith]

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 the words are mnemonics for 
operators/tokens rather than meaningful in dictionary terms.

Here's an example that is even less intuitive than yours to prove the point:

SELECT  CASE  NULL  WHEN  NULL THEN 'This value is NULL'   ELSE 'This 
value is definitely not Null'  END;

Answer:
This value is definitely not Null

My choice of words only serve to confuse - it's best to understand by 
rewriting the statement like this:

SELECT  CASE  WHEN  NULL IS NULL  THEN 'This value is NULL' ELSE  'This 
value is definitely not Null'  END;

Which of course correctly returns:
This value is Null
(because "Null is Null" returns a boolean value and not a Null) and will 
return correct results when used in a normal statement like:

SELECT  CASE  WHEN  X IS NULL  THEN 'X is NULL'   ELSE  'X is not Null'  
END;

While this statement:

SELECT  CASE  X  WHEN  NULL THEN 'X is NULL'   ELSE  'X is definitely 
not Null'  END;

Will short-circuit to the ELSE as soon as X is actually NULL, and so 
will return "X is definitely not Null" in every conceivable scenario - 
as it should in mathematical/logical terms.


Cheers,
Ryan


On 2016/02/17 5:43 AM, Richard Hipp wrote:
> On 2/16/16, Denis Burke  wrote:
>> select case when null not in ('a') then 'not in list' else 'is in list'
>> end;
>>
> The expression "null not in ('a')" evaluates to null.  So your query
> is really:  "select case when null then 'not-in-list' else
> 'is-in-list' end;"  Since the condition is not true (because null is
> not true) then the else clause is taken.  This is correct behavior.
>
> PostgreSQL gets the same answer.

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

[Simon Slavin]

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 write a readonly database" error.

Expected behaviour.  Sharing the cache shares the connection.

Simon.

[sqlite] Documentation Typo

[Stephen Chrzanowski]

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 retrieve data and it should? the word
> should be ?supposed"
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Behavior When Comparing NULL Values

[Simon Slavin]

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 behavior, or a bug?

Expected behaviour.

The pseudo-value null is not a value like zero, it's a placeholder which means 
"I don't know the value of this thing.".  Thus a field with null in might be 
'a'.  So

> null not in ('a')

is not definitely true.

Simon.