[sqlite] Possible bug

[Adams, Justin]

Hello,

I searched the bug tracker and the only thing I saw that looked relevant was 
ticket UUID 6c266900a22574d4d6474503da5bfe849c8b244f.
http://www.sqlite.org/src/tktview?name=6c266900a2

However, that seems to deal with special Unicode characters and I'm not dealing 
with that here.

I'm using SQLite 3.8.11.1 on Windows 7 Pro 64 built with Visual C++ 2010.

I was trying to create a table with a column for IPv4 addresses as VARCHAR. It 
seems when I set the .mode to column that some records are truncated on the 
right. Changing the .mode fixes the problem, so I would think it has to do with 
the column width calculation. Following my sig is some example code. You will 
see .mode is changed to column then "172.16.18.239" is truncated to 
"172.16.18.23". In the second example you will see "10.999.987.25" gets 
truncated to "10.999.987.".

Thanks to everyone for your help. If there is any more info you need please let 
me know. Also, if anybody can tell me what file/function in the source deals 
with this I would appreciate it.

Thank you,
Justin Adams

Example 1

C:\>C:\app\sqlite\sqlite3.exe C:\Temp\bugdemo.sqlite
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE ipAddresses(ip VARCHAR(20) PRIMARY KEY);
sqlite> INSERT INTO ipAddresses VALUES ('10.186.34.23'), ('192.168.1.4'), 
('172.16.18.239'), ('54.6.74.21'), ('8.8.8.8');
sqlite> SELECT * FROM ipAddresses;
10.186.34.23
192.168.1.4
172.16.18.239
54.6.74.21
8.8.8.8
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM ipAddresses;
ip

10.186.34.23
192.168.1.4
172.16.18.23
54.6.74.21
8.8.8.8
sqlite> .mode list
sqlite> SELECT * FROM ipAddresses;
ip
10.186.34.23
192.168.1.4
172.16.18.239
54.6.74.21
8.8.8.8
sqlite> .mode column
sqlite> SELECT * FROM ipAddresses;
ip

10.186.34.23
192.168.1.4
172.16.18.23
54.6.74.21
8.8.8.8




Example 2

C:\home\db\static>C:\app\sqlite\sqlite3.exe static.sqlite
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE tableCategories(category VARCHAR(16) PRIMARY KEY);
CREATE TABLE tableSites(siteName VARCHAR(64) PRIMARY KEY);
CREATE TABLE tableStatic
(
ip VARCHAR(16) PRIMARY KEY,
location VARCHAR(64) REFERENCES tableSites (siteName) ON UPDATE CASCADE,
category VARCHAR(16) REFERENCES tableCategories (category) ON UPDATE 
CASCADE,
description VARCHAR(64)
);
sqlite> SELECT ip, category FROM tableStatic;
10.537.79.7|PBX
10.999.987.25|PBX
sqlite> .mode column
sqlite> SELECT ip, category FROM tableStatic;
10.537.79.7  PBX
10.999.987.  PBX
sqlite> SELECT category, ip FROM tableStatic;
PBX 10.537.79.7
PBX 10.999.987.
sqlite> .mode list
sqlite> SELECT category, ip FROM tableStatic;
PBX|10.537.79.7
PBX|10.999.987.25

Confidentiality Notice: This e-mail message, including any attachments, is for 
the sole use of the intended recipient(s) and may contain confidential and 
privileged information. Any unauthorized review, use, disclosure, or 
distribution is prohibited. If you are not the intended recipient, please 
contact the sender by reply e-mail and destroy all copies of the original 
message.

[sqlite] Array or set type or some other indexable data?

[J Decker]

So I've used CTE to solve a simple problem... I'm tempted to use it to
fix more problems... but I'm wondering how to select different values
at different levels.  I know there's like 'select * from table where
column in ( set,of,things) ' but can I index into the set?  is there
some sort of way I can specify an array of values?

would like to do something like ' select value from option_map where
name is ["system","device","com port", "1" ] '  where the program
statement would look more like

GetOptionValue ( "/system/device/com port/1" ) where the name is
really sort of registry like and variable in length...

I could probably do some sort of indexing passing that exact string
through and parsing in SQL the substrings of interest based on the
level of the query... but would rather preparse the string.

[sqlite] combining overlapping tables

[Keith Medcalf]

insert into combinedTable
select * from table1
union
select * from table2
union
select * from table 3;

{set} union {set} returns the distinct result
{set} union all {set} returns a results that may have duplicate rows


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Wednesday, 11 November, 2015 07:32
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] combining overlapping tables
> 
> On 11/11/2015 3:56 AM, J.L. Blom wrote:
> > So e.g. table one ends (in column one) on 2015-07-21 11:55
> > and the next starts on  2015-07-19
> > 17:50
> > There is an overlap op 2 days approx. 380 rows (each row contains 53
> > items).
> > Is there a script - or commando to combine the tables excluding the
> > overlapping rows?
> 
> Are you looking for something like
> 
> insert into TableOne
> select * from TableTwo t2
> where t2.timestamp > (select max(timestamp) from TableOne);
> 
> --
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules

[a...@zator.com]

>  Mensaje original 
> De: Richard Hipp  
>Fecha: 4/11/2015 23:23 (GMT+01:00)
>Para: SQLite mailing list 
>Asunto: Re: [sqlite] Problem when upgrading from FTS3/4 to FTS5 modules 

>Here is a hack.
>Let the name of your FTS3 table be "xyzzy"
>(1) Invoke "PRAGMA writable_schema=ON". (Read the documentation about
>the dangers thereof. Do not complain if something goes wrong and you
>corrupt your database.)
>(2) Run "DELETE FROM sqlite_master WHERE name='xyzzy';"
>(3) Set "PRAGMA writable_schema=OFF" again.
>(4) Run the following:
>
>DROP TABLE IF EXISTS xyzzy_content;
>DROP TABLE IF EXISTS xyzzy_segments;
>DROP TABLE IF EXISTS xyzzy_segdir;
>DROP TABLE IF EXISTS xyzzy_docsize;
>DROP TABLE IF EXISTS xyzzy_stat;

Thanks a lot
That tricky worked fine.

--
Adolfo

[sqlite] setting cache_size to 0 right after opening a connection is ignored

[Richard Hipp]

On 11/11/15, Richard Hipp  wrote:
> On 11/10/15, Ousherovitch Alex  wrote:
>>
>> setting PRAGMA cache_size to zero is a no-op and the default (2000 pages)
>> will be used if it's done right after opening a database.
>
> I'm unable to reproduce this problem.  Do you have a test case?
>

I see now.  The problem only comes up when you set the cache size to
exactly 0.  If you set it to 1, it works.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

[Jay Kreibich]

On Nov 10, 2015, at 6:38 PM, Richard Hipp  wrote:

> On 11/10/15, Jay Kreibich  wrote:
>> 
>> I want to be a Certified SQLite Professional.
>> 
> 
> The inventors and developers of the SQLite database engine to all to
> whom these presents may come, Greetings:  Whereas Mr. Jay Kreibich has
> exhibited detailed knowledge of the use and inner workings of SQLite
> by publishing an outstanding textbook on that subject, we do hereby
> confirm unto him the title of
> 
>Certified SQLite Professional
> 
> with all the rights, honors, and duties thereunto appertaining.  In
> witness whereof, the electronic signature of the BDLF of SQLite is
> hereto subscribed.  Given in Charlotte on this the tenth day of
> November and in the year of our Lord two thousand and fifteen.


w00t!  Time to update my resume!

 -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

[Jay Kreibich]

On Nov 11, 2015, at 3:25 AM, Simon Slavin  wrote:

> There's a story behind this.
> 
> During the review leading to HTML5 the web consortium (the one which decides 
> which features should be introduced to HTML/CSS/JavaScript) listed a 
> 'webdatabase' specification so that browsers could maintain data in a 
> database-like structure.  The specification boiled down to "Implement a 
> simple SQL engine with an API like [this].".
> 
> Unfortunately all the leading browser publishers implemented this happily and 
> quickly.  "Unfortunately" because they all did it by including SQLite in 
> their project.  This is a problem because the only way to ensure 
> compatibility in that case is for the web consortium to include the full 
> specification of SQLite inside the specification for a compatible web 
> browser.  Otherwise people would start doing things like calling PRAGMA 
> commands from web pages, and then an unofficial standard would evolve which 
> required PRAGMA commands to be supported from the web API.
> 
> So they did.  And it failed.  And that's where we are today.

Sorta-kinda.  The standard was called WebSQL, and it was an extension to 
client-side data stores.  W3C setup a working group and sent out a request for 
implementations, but as you said, everyone in the working group implemented the 
standard by basically wrapping SQLite.  The standard was then killed due to 
lack of unique implementations.  As you said, there was concern that without 
independent implementations, aspects specific to SQLite would ?leak? into the 
standards and commonly used APIs, so the standard was killed.

The official working group notes are here:

http://www.w3.org/TR/webdatabase/

  -j

--  
Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

[a...@zator.com]

>
>  Mensaje original 
> De: Simon Slavin 
> Para:  SQLite mailing list 
> Fecha:  Wed, 11 Nov 2015 09:25:47 +
> Asunto:  Re: [sqlite] "Shipping a New Mindset" - SQLite in Windows 10
>
>There's a story behind this.
>
>During the review leading to HTML5 the web consortium (the one which decides 
>which features should be introduced to HTML/CSS/JavaScript) listed a 
>'webdatabase' specification so that browsers could maintain data in a 
>database-like structure.  The specification boiled down to "Implement a simple 
>SQL engine with an API like [this].".
>
>Unfortunately all the leading browser publishers implemented this happily and 
>quickly.  "Unfortunately" because they all did it by including SQLite in their 
>project.  This is a problem because the only way to ensure compatibility in 
>that case is for the web consortium to include the full specification of 
>SQLite inside the specification for a compatible web browser.  Otherwise 
>people would start doing things like calling PRAGMA commands from web pages, 
>and then an unofficial standard would evolve which required PRAGMA commands to 
>be supported from the web API.
>
>So they did.  And it failed.  And that's where we are today.
>

Simon:

You are right, and I realized that before. By the way the "storage" issue from 
JavaScript has been my nightmare in the recent past.

I believe that it's a nonsense that the standard suppose a limitation in the 
field. Although it seem that the Standard work like the lawyers who usually go 
behind the real life. But that reminds me the adoption of the C++ STL whos 
first incarnation was -I think remember- in a unique piece from HP and nobody 
put objections.

Any way in the real life we need it desperately IMHO.

--
Adolfo

[sqlite] setting cache_size to 0 right after opening a connection is ignored

[Richard Hipp]

On 11/10/15, Ousherovitch Alex  wrote:
>
> setting PRAGMA cache_size to zero is a no-op and the default (2000 pages)
> will be used if it's done right after opening a database.

I'm unable to reproduce this problem.  Do you have a test case?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] combining overlapping tables

[J.L. Blom]

Hi ,
I am rather unknown to sqlite and I have the following problem.
I have several tables (3) with exactly the same schema. I have to 
combine them to one table but there are overlapping data in each.
So e.g. table one ends (in column one) on 2015-07-21 11:55
and the next starts on  2015-07-19 17:50
There is an overlap op 2 days approx. 380 rows (each row contains 53 items).
Is there a script - or commando to combine the tables excluding the 
overlapping rows?
I?m thankful for any help,
Greetings
Joep

[sqlite] combining overlapping tables

[Simon Slavin]

On 11 Nov 2015, at 8:56am, J.L. Blom  wrote:

> I have several tables (3) with exactly the same schema. I have to combine 
> them to one table but there are overlapping data in each.

Are you doing this just once -- now -- to prepare some data, or do you have to 
write software which will do this ?

You might want to look at this form of the INSERT command:

INSERT OR IGNORE INTO bigTable SELECT * FROM sourceTable

This form will add rows to a bigTable, but ignore (not insert, generate no 
error message) any row which would cause any constraint violation, like a 
duplicate primary key.



Simon.

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

[a...@zator.com]

>  Mensaje original 
> De: Richard Hipp 
> Para:  General Discussion of SQLite Database mailinglists.sqlite.org>
> Fecha:  Tue, 10 Nov 2015 14:41:28 -0500
> Asunto:  [sqlite] "Shipping a New Mindset" - SQLite in Windows 10
> 
>
>http://engineering.microsoft.com/2015/10/29/sqlite-in-windows-10/
>

Congratulations to all the team. A great praise coming from M$.

A great notice to all us who write Windows. And a final prayer to MS: add an 
extension to use it from JavaScript.

--
Adolfo J. Millan

[sqlite] combining overlapping tables

[Igor Tandetnik]

On 11/11/2015 3:56 AM, J.L. Blom wrote:
> So e.g. table one ends (in column one) on 2015-07-21 11:55
> and the next starts on  2015-07-19
> 17:50
> There is an overlap op 2 days approx. 380 rows (each row contains 53
> items).
> Is there a script - or commando to combine the tables excluding the
> overlapping rows?

Are you looking for something like

insert into TableOne
select * from TableTwo t2
where t2.timestamp > (select max(timestamp) from TableOne);

-- 
Igor Tandetnik

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

[Simon Slavin]

On 11 Nov 2015, at 8:33am, ajm at zator.com wrote:

> A great notice to all us who write Windows. And a final prayer to MS: add an 
> extension to use it from JavaScript.

There's a story behind this.

During the review leading to HTML5 the web consortium (the one which decides 
which features should be introduced to HTML/CSS/JavaScript) listed a 
'webdatabase' specification so that browsers could maintain data in a 
database-like structure.  The specification boiled down to "Implement a simple 
SQL engine with an API like [this].".

Unfortunately all the leading browser publishers implemented this happily and 
quickly.  "Unfortunately" because they all did it by including SQLite in their 
project.  This is a problem because the only way to ensure compatibility in 
that case is for the web consortium to include the full specification of SQLite 
inside the specification for a compatible web browser.  Otherwise people would 
start doing things like calling PRAGMA commands from web pages, and then an 
unofficial standard would evolve which required PRAGMA commands to be supported 
from the web API.

So they did.  And it failed.  And that's where we are today.

Simon.

[sqlite] Understanding WALs

[Paul Sanderson]

Thanks Richard a nice simple answer to pass on.


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 11 November 2015 at 00:24, Richard Hipp  wrote:
> On 11/10/15, Paul Sanderson  wrote:
>>
>> As the last thing I did was a wal-checkpoint then according to the
>> documentation the page should have been written to the DB (it was see
>> pic below of page two of the DB) AND the salts incremented and
>> randomised (section 4.3 of the file format page).
>>
>
> The increment and randomization of the salts occurs at the start of
> the next write transaction.  (It is deferred until then to avoid
> having to take unnecessary write locks.)
>
> The file format documentation is not technically wrong, but it is
> misleading.  I have updated that document now (see the second
> paragraph of section 4.3 at https://www.sqlite.org/fileformat.html) to
> try to make it less ambiguous.
>
> --
> 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] Another unexpected source of SQLITE_BUSY_SNAPSHOT

[Dan Kennedy]

On 11/11/2015 01:19 AM, Florian Weimer wrote:
> * Dan Kennedy:
>
>> On 10/18/2015 10:27 PM, Florian Weimer wrote:
>>> My first surprise was SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE (see
>>> ?SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE?).  I have found another
>>> source of such snapshot failures with WAL-mode databases.
>>>
>>> I like to pre-compile my DML statements before starting transactions,
>>> mainly for the implied syntax check.  (But perhaps there are also
>>> performance benefits, too?I haven't checked.)
>>>
>>> Here is what I did (if my tracing is correct).  Unless mentioned
>>> otherwise, the operations succeed with SQLITE_OK.
>>>
>>> Open the database.
>>> Compile the UPDATE statement.
>>> Compile the ROLLBACK statement (used below).
>>> Start a BEGIN DEFERRED transaction (with a fresh statement).
>>> Reset the UPDATE statement.
>>> Step the UPDATE statement.
>>>  -> this fails with SQLITE_BUSY_SNAPSHOT
>>> Reset/step/reset the pre-compiled ROLLBACK statement.
>>> Start a BEGIN IMMEDIATE transaction (with a fresh statement).
>>>  -> this fails with SQLITE_BUSY_SNAPSHOT
>>> Start a BEGIN IMMEDIATE transaction (with a fresh statement).
>>> Reset the UPDATE statement.
>>>  -> this fails with SQLITE_BUSY_SNAPSHOT
>>>
>>> After the last failure, iterating through the list of stattements does
>>> not show that the UPDATE statement is busy.
>>
>> The SQLITE_BUSY_SNAPSHOT returned by the final call to sqlite3_reset()
>> on the UPDATE is not a new error - it has just saved the error code
>> from the error that occurred during the sqlite3_step() call in the
>> previous transaction.
> D'oh!  I mistakenly assumed using the sqlite3_prepare_v2() function
> would avoid that.  Thanks.
>
>> So technically the error did not occur within
>> the BEGIN IMMEDIATE transaction.
> Right.
>
> I have changed my wrapper for sqlite3_reset() to ignore the error
> value and removed the compensation code from my transaction monitor.
> The tests that previously failed occasionally still succeed.
>
>>> I don't know how risky pre-compiling UPDATE statement is.  For SELECT
>>> statements, it is problematic because they can lead to persistent
>>> transaction failures with SQLITE_BUSY_SNAPSHOT because the
>>> pre-compiled and partitial executed statement is busy and will block
>>> creating a new transaction.  I wonder if this can happen with UPDATE
>>> statements as well.
>> I don't quite understand this. Preparing an SQL statement may read the
>> database (in order to read the schema). But calling sqlite3_prepare()
>> doesn't leave the statement in a "partially executed" state.
> My concern is about sqlite3_step(UPDATE) without a following
> sqlite3_reset(UPDATE).  Perhaps I should change my wrapper to
> unconditionally call sqlite3_reset() after DML-related sqlite3_step(),
> whether the stepping operation succeeded or not.

For a DML statement, I usually just do:

   sqlite3_step(pStmt);
   rc = sqlite3_reset(pStmt);
   if( rc!=SQLITE_OK ){
 /* Handle error */
   }

I don't see why you would want to call step() but not reset() for a DML 
operation. And since reset() always returns either the same or a more 
specific error code, there isn't too much reason to ever catch the 
return value of step(). IMO.

Dan.

[sqlite] "Shipping a New Mindset" - SQLite in Windows 10

[Simon Slavin]

On 10 Nov 2015, at 11:53pm, Tim Uy  wrote:

> On Tue, Nov 10, 2015 at 3:31 PM, Jay Kreibich  wrote:
> 
>> I want to be a Certified SQLite Professional.
> 
> Exactly. How does this happen?

If you'd each like to send me a check for five thousand dollars and take a 
short online test I will be happy to send you your certificates.

Simon.