Re: [sqlite] Proof that a line has been modified

[Michael Stephenson]

In the past, I've used the pager to secure data.  This involved encrypting the 
data before writing the data to disk and decrypting when loading from disk but 
also optionally hashing the page and storing the hash in extra data reserved 
for each page.  If anyone tampered with the data, the hash would indicate this 
and an error could be thrown.  

Also encrypting the page data makes it more difficult to tamper with the data.

Products like sqlcipher do things like this (encryption, hashing), and it's 
fairly easy to see how it's done by pulling the sqlite source (not the 
amalgamation) and diffing it with the sqlcipher source.

~Mike

> On Sep 7, 2017, at 6:34 PM, Jens Alfke  wrote:
> 
> 
> 
>> On Sep 7, 2017, at 2:47 PM, Keith Medcalf  wrote:
>> 
>> Again, this is a detection for changed data and does nothing to prevent 
>> changes being made.
> 
> The OP did not require that it be impossible to make changes (which is 
> clearly impossible without locking down write access to the file.) He 
> specifically said that detection of changed data was OK:
> 
>>> For security reasons, a customer wants to be sure that a database line 
>>> cannot be modified after its initial insertion (or unmodified without being 
>>> visible, with proof that the line has been modified).
> 
> The procedures I described provide detection that a row has been modified.  
> The first one doesn't make it evident that a row has been deleted, though the 
> second one does.
> 
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] REQUEST: sqlite3_file_control(...op=SQLITE_FCNTL_TRUNCATE_WAL...)

[Howard Kapustein]

I use journal_mode=WAL and have periods of checkpoint starvation (complicated 
reasons) so I'm looking to prune the -wal file but in less blunt way than 
SQLITE_CHECKPOINT_TRUNCATE.

Behaviorally I want SQLITE_CHECKPOINT_PASSIVE *and then* if required -wal 
content < journal_size_limit, to do the truncation a la 
SQLITE_CHECKPOINT_TRUNCATE

SQLITE_CHECKPOINT_PASSIVE gives me the best-effort checkpointing, but in the 
best case I don't get -wal shrinkage. SQLITE_CHECKPOINT_TRUNCATE is more of an 
aggressive do-it-now-wait-if-necessary which gets me the shrinkage behavior, 
but with *required*-effort rather than best-effort. I'd really like both -- 
best-effort checkpoint AND best-effort truncate.

sqlite3WalClose does exactly what I want (the key bits)

sqlite3OsLock(pWal->pDbFd, SQLITE_LOCK_EXCLUSIVE)
sqlite3WalCheckpoint(pWal, db, SQLITE_CHECKPOINT_PASSIVE,...)
sqlite3OsFileControlHint(pWal->pDbFd, SQLITE_FCNTL_PERSIST_WAL, )
if (bPersist) { if (pWal->mxWalSize>=0) { walLimitSize(pWal, 0) } }

But AFAICT this is only called when PRAGMA journal_mode changes to not WAL or 
the pager cache is closed when via sqlite3_close(). I'm a long running process 
with connection caching and associated prepared statements so torching the 
connection to trigger this isn't optimal.

Can I indirectly get this behavior if I open then immediately close a 
connection?

I think I can sorta approximate this it if I disable the busy-handler, do 
SQLITE_CHECKPOINT_TRUNCATE, ignore Busy/Locked errors and restore the 
busy-handler before returning, but that's merely 'sorta' -- ugly on several 
levels.

I don't see any way to directly try to best-effort truncate the -wal file e.g.

sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_PASSIVE, NULL, NULL))
sqlite3_file_control(db, NULL, SQLITE_FCNTL_TRUNCATE_WAL, NULL)

That would be ideal. Let's me handle both checkpoint'ing and truncation in 
low-impact best-effort manners. 

- Howard
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GCC and DLL

[Simon Slavin]

On 8 Sep 2017, at 12:16am, Papa  wrote:

> Thanks Simon for the info.

You’re welcome.

> I am new to NetBeans and I was having a little bit of problems setting up the 
> IDE, that's all.

If you have a problem, you can ask here, but you may find better information on 
a forum about NetBeans.  Happy hacking.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GCC and DLL

[Papa]

Thanks Simon for the info.
I am new to NetBeans and I was having a little bit of problems setting 
up the IDE, that's all.



On 2017-09-06 1:03 PM, Simon Slavin wrote:


On 6 Sep 2017, at 4:12pm, Papa  wrote:


Are the SQLite3 DLLs, in the Precompiled Binaries for Windows, compatible with 
MinGW-64?

SQLite is entirely C code and has nothing in which depends on C++ features or 
settings.  There shouldn’t be any problems.  Is there something specific which 
concerns you ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
ArbolOne.ca
Using Fire Fox and Thunderbird.
ArbolOne is composed of students and volunteers dedicated to providing free 
services to charitable organizations.
ArbolOne on Java Development in progress [ í ]

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Jens Alfke]

> On Sep 7, 2017, at 2:47 PM, Keith Medcalf  wrote:
> 
> Again, this is a detection for changed data and does nothing to prevent 
> changes being made.

The OP did not require that it be impossible to make changes (which is clearly 
impossible without locking down write access to the file.) He specifically said 
that detection of changed data was OK:

>> For security reasons, a customer wants to be sure that a database line 
>> cannot be modified after its initial insertion (or unmodified without being 
>> visible, with proof that the line has been modified).

The procedures I described provide detection that a row has been modified.  The 
first one doesn't make it evident that a row has been deleted, though the 
second one does.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Nico Williams]

On Thu, Sep 07, 2017 at 10:16:15AM +0200, Paxdo wrote:
> For security reasons, a customer wants to be sure that a database line
> cannot be modified after its initial insertion (or unmodified without
> being visible, with proof that the line has been modified). Including
> by technicians who can open the database (SQLITE of course).
> 
> Is there a solution to that?

You have these choices:

 - hash the whole SQLite3 file and record or sign such hash values for
   approved DB files (this will let you detect all changes)

 - something like what you described (hash every row of every table and
   bind them all somehow, then save or sign this; this too will let you
   detect all changes for all tables that you apply this to)

   (this is DB-agnostic)

 - switch to a DB that uses a Merkle hash tree (see below)

Generally speaking, the best approach for this sort of thing is to use
something called a Merkle Hash Tree, such that for each database/
filesystem/datastore you always have available a single, small (e.g.,
256 bits) cryptographic hash value for the entire thing.

In order to make such hash values usable for this purpose you'll need
the system to be "content-addressed" storage (CAS) if at all possible
(more on that in below).

A good example of a system that comes close to this is ZFS.  ZFS is a
filesystem that actually is a Merkle Hash Tree on-disk, but it's not
content-addressed, which means that if the locations of data on-disk
changes, then the root hash also changes, even if none of the actual
data changed.

What this actually means in practice is that any time you have a
"pointer" from a database/filesystem/datastore page to another, what
must actually be stored is not just the address of the target page, but
the hash of its contents.  If you apply this rule rigorously, and if you
have a root page (filesystems generally do, and databases can too, at
least per-table, and often for the entire DB), you necessarily end up
with a root page whose cryptographic hash *is* the cryptographic hash of
the entire DB/FS.

To get CAS you also need to not include block/page addresses in the
cryptographic hash computations (but still must include the hashes of
pointed-to pages/blocks).  Then you can say that the hash of a page's
content *is* its address (it's not really).

One reason that Merkle hash trees are best is that you don't have to
read every page of a DB/FS to verify the root hash.  You need only hash
the root page and you're done -- if, anyways, any errors verifying other
page hashes can be handled at run-time.  Another is that they let you
compute hashes for sub-trees.  Another is that they're easy to build.

SQLite3 is NOT a Merkle hash tree, however.  You *can* build a Merkle
hash tree with SQLite3 though.  Fossil is a version control system that
does exactly that, but that's not a technique you're likely to apply to
your use case (I'm guessing).

Given a Merkley hash tree, you can digitally sign (or save in a remote,
secure system) root hash values of approved DB/FS states.  This is
interesting, for example, for secure-boot/TPM applications.

Given that you can't easily use a Merkle hash tree with SQLite3 this
without building a DB on top of a DB (like Fossil basically does) or
switching to one that uses a Merkle hash tree (and exposes the root hash
value to you), you could hash every row, XOR the hash values (since
there's no defined order for the rows, or else you can hash the
concatenation of the hashes in some order you define), and sign that.
You could apply this for every table and XOR all the table hashes, or
just those tables that are of interest to you.  You'll want to do this
for all interesting rows in sqlite_master as well.

Lastly, as others have pointed out, the best you can do with a DB hash
is cryptographically prove that the FS/DB has approved content, for some
value of "approved content".

You cannot prove that the DB/FS hasn't been reset to an earlier approved
state without adding a revocation system.

Nor can you prove that the DB/FS has no malicious content in it -- only
that an approved entity signed it as "approved".

Nico
-- 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite inmemory size (windows Task Manager)

[Keith Medcalf]

What Operating System are you talking about (including specific version), and 
what do you mean by "memory"?  Are you talking about system-wide virtual arena, 
system-wide virtual commit size, process virtual size, process commit size, 
process working set, or something else?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Karthi M
>Sent: Thursday, 7 September, 2017 11:12
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] SQLite inmemory size (windows Task Manager)
>
>HI All
>I am using SQLite inmemory database. Every time when data is
>loaded the PC RAM increases gradually. At some point of time the
>memory reduces to normal. but still inmemory database works fine.
>why memory shows high in task manager and reduces after sometime?
>if the memory reduces to normal where inmemory stores the data?
>Any idea one this??
>
>Thanks & Regards,
>Karthi M
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Keith Medcalf]

ON Thursday, 7 September, 2017 10:32, Jens Alfke  wrote:

>> On Sep 7, 2017, at 1:38 AM, Paxdo  wrote:

>> But of course, someone who knows this checksum and its salt could
>make changes on the table and recalculate all checksums. :-(
>
>Use digital signatures. I can think of two approaches:
>
>(a) Sign each row. The program doing the insertion would need to know
>the private key, while validation would require only the public key.
>The drawback is that whoever does the insertion can later modify any
>row, although no one else can.

This is somewhat misleading.  Anyone can update the row.  Only someone in 
possession of the private key can generate a correct signature, however.  This 
does not prevent updating the row (or deleting it).  It only permits detection 
of updates made without the private key and without re-generating a valid 
signature.

It is somewhat better than a simple hash in that generating a validation token 
requires possession of the private key, but it does nothing to prevent changes.

>(a) Use external signatures as official validation. Periodically have
>the entity with the private key compute a digest of the entire data
>set and sign that, adding the signature to the database. Anyone can
>regenerate that digest and verify the signature. The drawback is that
>any recent additions since the last signing aren't safe. And again,
>the entity with the private key can modify anything.

Again, anyone can modify anything.  Only the possessor of the private key can 
generate a valid signature however.  Again, this is a detection for changed 
data and does nothing to prevent changes being made.

>To guard against modifications by the private-key owner, other
>parties can remember previous signatures and raise a ruckus if they
>ever change in the future.
>
>—Jens
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

[Keith Medcalf]

>Other "am I right in thinking this" question: INTERSECT is only going
>to be viable when comparing full records, correct? If you're looking
>to filter table A by whether its primary key is also a primary key
>for table B, but ignoring the other fields in both, then INTERSECT
>becomes not an option, or at least starts making the query more
>complex/ugly... correct?

No.  The data must merely be the same shape (order, number of columns):

SELECT c1, c2, c3, c8 from t1
INTERSECT
SELECT x5, g4, q7, b3 from t2;

each of t1 and t2 may have fields named a1 a2 a4 ... a26 b1 b2 b3 ... b26 ... 
z1 z2 z3 ... z26 (for a total of 676 fields per table).

The comparison is of resulting matrices, not underlying tables.  Of course, if 
you do "SELECT * from table1;" the * is merely "syntactic sugar" (a short way 
of spelling) the complete, in declaration order, list of explicit columns.  
(So, if and only if the ROWID is an "explicit column" is it used in the 
intersection, otherwise it is not -- and concomitantly if you explicitly list 
the columns to intersect, then the rowid participates if and only if you have 
included it in the list of data to intersect.)

INTERSECT / EXCEPT / UNION are matrix operations.  The RHS (select statement 
before the operator) and LHS (select statement after the operator) provide the 
two matrices on which the operation is performed.  Matrixes do not have column 
names, merely ordinal positions (column 1, column 2, column 3 and so forth).  
Similarly these operations do not care about column names, merely that the 
order (number of columns in each matrix) is the same.  Comparisons are done by 
ordinal position of the item in the row.  The output is a matrix.  It only 
appears "row at a time" because of the primitive method of operation of (no 
matter how advanced) computers which can only perform operations as a serial 
sequence of steps.  For "user convenience" the output column names are set to 
the RHS column names.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of David Raymond
>Sent: Thursday, 7 September, 2017 10:31
>To: SQLite mailing list
>Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency
>
>Although it may not translate as well to the more complex examples,
>would you also consider adding the IN operator to your tests? I found
>for example that "select v from t1 where v in t2;" did even better
>than the join or the intersect.
>
>
>
>
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of R Smith
>Sent: Thursday, September 07, 2017 8:06 AM
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency
>
>
>On 2017/09/07 3:43 AM, Keith Medcalf wrote:
>> Try the same test using 147 columns in each table.
>
>Exactly the plan for this weekend :)
>
>> 1 column is rather trivial.  Even a kindergarten kid could do it in
>no time using crayons and the wall.
>
>So? That is non-sequitur, I am sure given enough crayons, wall-space
>and
>time, a kindergarten kid can do it with 147 columns too. That says
>exactly nothing about the possible efficiencies of different methods.
>If
>however the 1-columness of the test gets somehow advantaged by being
>the
>PK (as Nico pointed out) or real world data such as TEXT entries sort
>slower than INTs, then it might affect it, so the 147 column tests
>will
>tell.
>
>
>> In other words except in very trivial cases (like having only one
>column that is not nullable) it will be very difficult to write a
>"correct" JOIN or correlated subquery that emulates an INTERSECT.
>
>Well I agree, but it is those trivial cases that are of interest
>here,
>and if there is a general JOIN optimization to be had. The INTERSECT
>test merely served as the catalyst to put us on the trail of the
>possible JOIN optimization, if there is even an optimization to be
>had
>(it might yet be a wild goose chase, which you seem to have your
>money
>on, so watch this space, I'll graciously accept your "told ya!" later
>after testing).
>
>
>Cheers,
>Ryan
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug Report: Crash When Loading Short Journal

[Richard Hipp]

On 9/6/17, Natalie Silvanovich  wrote:
> I'm experiencing a crash when loading a database with a corrupt journal
> file.

The chances of hitting the problem by accident are remote - so much so
that it is impossible in practice.  This problem can only come up if
an adversary deliberately crafts a malicious rollback journal and
tricks an application into using it.

In any event, the problem is now fixed on trunk.  Thanks for the
concise and clear bug report!

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

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

[David Raymond]

Expanding things for when you get bored, in addition to  JOIN vs 
INTERSECT vs IN I'd also be interested in  JOIN vs EXCEPT vs NOT IN, as 
I tend to do more exclusion rather than intersection.

The straight up "IN tablename" may be SQLite only, but it also supports IN 
(subquery) so "select v from t1 where v in (select v from t2)" also works, 
although the "IN tablename" is so much cleaner to read in my opinion.

create table statesToSkip (
  state text primary key collate nocase
) without rowid;

select foo from bar where state not in statesToSkip.

vs

select foo from bar where state not in (select state from statesToSkip)

vs

select bar.foo from bar left outer join statesToSkip
on (bar.state = statesToSkip.state)
where statesToSkip.state is null;


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, September 07, 2017 3:51 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/07 6:31 PM, David Raymond wrote:
> Although it may not translate as well to the more complex examples, would you 
> also consider adding the IN operator to your tests? I found for example that 
> "select v from t1 where v in t2;" did even better than the join or the 
> intersect.

Will do. The only thing I have somewhat against that specific query form 
is that it doesn't work in other engines (seems to not be standard). But 
for optimization in SQLite specifically that's perfect.

> Other "am I right in thinking this" question: INTERSECT is only going to be 
> viable when comparing full records, correct? If you're looking to filter 
> table A by whether its primary key is also a primary key for table B, but 
> ignoring the other fields in both, then INTERSECT becomes not an option, or 
> at least starts making the query more complex/ugly... correct?

INTERSECT will happily match however many columns you desire (and 
specify), there is no need to match full records or single keys 
specifically.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

[Nico Williams]

On Thu, Sep 07, 2017 at 09:51:07PM +0200, R Smith wrote:
> INTERSECT will happily match however many columns you desire (and specify),
> there is no need to match full records or single keys specifically.

But the two queries on either side of the set operator must have the
same number of columns (and in strongly-typed RDBMSes, the same types).

That's a huge constraint.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

[R Smith]

On 2017/09/07 6:31 PM, David Raymond wrote:

Although it may not translate as well to the more complex examples, would you also 
consider adding the IN operator to your tests? I found for example that "select v 
from t1 where v in t2;" did even better than the join or the intersect.


Will do. The only thing I have somewhat against that specific query form 
is that it doesn't work in other engines (seems to not be standard). But 
for optimization in SQLite specifically that's perfect.



Other "am I right in thinking this" question: INTERSECT is only going to be 
viable when comparing full records, correct? If you're looking to filter table A by 
whether its primary key is also a primary key for table B, but ignoring the other fields 
in both, then INTERSECT becomes not an option, or at least starts making the query more 
complex/ugly... correct?


INTERSECT will happily match however many columns you desire (and 
specify), there is no need to match full records or single keys 
specifically.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Jens Alfke]

> On Sep 7, 2017, at 11:55 AM, Igor Tandetnik  wrote:
> 
> It might be difficult to keep the private key secret. A technician that has 
> direct access to SQLite database file probably also has access to the binary 
> used to manipulate it; and that binary would need the private key lying 
> around someplace accessible.


Right, the private key couldn't be embedded in the code. There are a lot of 
other ways to manage access to private keys. But the details depend on the OP's 
situation, so I won't speculate further without more details.

I do think the problem is unsolvable without some kind of secret, whether it's 
known only to the party who's writing to the DB, or to another party who 
certifies the current contents of the DB.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a performance difference between COUNT(*) and COUNT(name)

[John Found]

On Tue, 5 Sep 2017 23:31:32 +0200
Cecil Westerhof  wrote:

> 2017-09-05 23:11 GMT+02:00 Simon Slavin :
> 
> >
> >
> > On 5 Sep 2017, at 9:21pm, Cecil Westerhof  wrote:
> >
> > > I want to know the number of teas I have in stock. For this I use:
> > > SELECT COUNT(Tea)
> > > FROM   teaInStock
> > >
> > > Tea cannot be NULL, so this is the same as:
> > > SELECT COUNT(*)
> > > FROM   teaInStock
> > >
> > > ​But I find the first more clear.
> > > I almost always see the second variant. Is this because it is more
> > > efficient, or are people just ‘lazy’?​
> >
> > Your guess is right !
> >
> > To do COUNT(*) SQLite has to retrieve all the rows.
> > To do COUNT(value) has to retrieve all the rows and test the value of each
> > row to make sure it is not NULL.
> >
> > Also, SQLite has a specific piece of code which makes COUNT(*) more
> > efficient than counting the values.  However, unless you have a big
> > database, the difference for your case may be small.  If you find
> > COUNT(Tea) easier to understand perhaps you should use that one.
> >
> 
> ​I will keep using COUNT(Tea) then, but keep in the back of my mind that I
> maybe should change that if a table becomes big.

In my tests even on small tables count(colName) is at least 2 times slower than
count(*), even if both queries uses covering indexes. So, using count(colName) 
has
meaning only if you really want to count only not null rows.

Making exception for columns that "never contain NULL" in the name of "source 
clearness" actually is hard for detection hidden bug that can strike after long 
time on the database schema change.


> 
> Thanks.​
> 
> -- 
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Graham Holden]

 Original message From: Jens Alfke  Date: 
07/09/2017  19:32  (GMT+00:00) To: SQLite mailing list 
 Subject: Re: [sqlite] Proof that a line 
has been modified 


> On Sep 7, 2017, at 10:24 AM, Igor Tandetnik  wrote:
> 
> "Device will refuse to install" is precisely an instance of "security built 
> in at the OS level".


Yes, but that's beside the point; it wasn't the relevant part of the example. 
Any software, privileged or not, can verify the signature and detect whether 
the binary has been modified. That's what the OP wants.

—Jens
That's fine for an unchanging binary signed in a secure environment and 
released to the world; what the OP wants is a similar level of security but for 
an ever-changing file (where the private keys must be accessible for "normal 
use").
Graham.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Igor Tandetnik]

On 9/7/2017 2:32 PM, Jens Alfke wrote:

On Sep 7, 2017, at 10:24 AM, Igor Tandetnik  wrote:

"Device will refuse to install" is precisely an instance of "security built in at 
the OS level".



Yes, but that's beside the point; it wasn't the relevant part of the example. 
Any software, privileged or not, can verify the signature and detect whether 
the binary has been modified. That's what the OP wants.


It might be difficult to keep the private key secret. A technician that has 
direct access to SQLite database file probably also has access to the binary 
used to manipulate it; and that binary would need the private key lying around 
someplace accessible.

Basically, if you can't trust your own admin personnel, you'd need a kind of 
military-grade security a la NSA post-Snowden, e.g. requiring two separate 
people to authenticate before access is granted. That gets pretty expensive 
pretty quickly.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Jens Alfke]

> On Sep 7, 2017, at 10:24 AM, Igor Tandetnik  wrote:
> 
> "Device will refuse to install" is precisely an instance of "security built 
> in at the OS level".


Yes, but that's beside the point; it wasn't the relevant part of the example. 
Any software, privileged or not, can verify the signature and detect whether 
the binary has been modified. That's what the OP wants.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Problem on Windows 10 machines

[Bart Smissaert]

Thanks, will give that a try.

RBS

On 7 Sep 2017 18:45,  wrote:

> Another suggestion: start the application from WinDbg (part of Windows
> debugger tools). When the application crashes: type "k" for displaying the
> stack trace or "!analyze" for automated exception analysis.
>
> Regards,
>
> Renaat
>
>
> Op 7/09/2017 om 12:56 schreef Bart Smissaert:
>
>> Noticed in the crash dump:
>>
>> LoadedModule[4]=C:\Users\User\AppData\Roaming\Microsoft\
>> AddIns\Bin\MSVBVM60.DLL
>>
>> This system dll shouldn't be in that folder and I deleted it, but didn't
>> solve any problem.
>>
>>
>> RBS
>>
>> On Thu, Sep 7, 2017 at 11:41 AM, Bart Smissaert > >
>> wrote:
>>
>> Process Monitor got me a bit further as it showed there was a call to
>>> WerFault.exe and this is the log of that:
>>>
>>> Version=1
>>> EventType=APPCRASH
>>> EventTime=131492511990301638
>>> ReportType=2
>>> Consent=1
>>> UploadTime=131492511998961952
>>> ReportStatus=268439552
>>> ReportIdentifier=9b4019d8-ff2c-48dc-b8e2-89b3d1065e90
>>> IntegratorReportIdentifier=08a25108-492e-4c35-9572-7c0afa0cda3a
>>> Wow64Host=34404
>>> Wow64Guest=332
>>> NsAppName=COM32on64.exe
>>> AppSessionGuid=25fc-0003-0005-9115-7a33be27d301
>>> TargetAppId=W:0006bf68cd3578efc2486facd1ee9646dcbe0904!
>>> fca1aabcca076defa0f16adc639b6998469c2bb9!COM32on64.exe
>>> TargetAppVer=2016//03//21:07:47:40!e334!COM32on64.exe
>>> BootId=4294967295
>>> ServiceSplit=3539031
>>> TargetAsId=4701
>>> Response.BucketId=91b0f4268c35a99eecbd35c544a753e4
>>> Response.BucketTable=1
>>> Response.LegacyBucketId=109122632689
>>> Response.type=4
>>> Sig[0].Name=Application Name
>>> Sig[0].Value=COM32on64.exe
>>> Sig[1].Name=Application Version
>>> Sig[1].Value=1.0.0.0
>>> Sig[2].Name=Application Timestamp
>>> Sig[2].Value=56efa71c
>>> Sig[3].Name=Fault Module Name
>>> Sig[3].Value=StackHash_b1df
>>> Sig[4].Name=Fault Module Version
>>> Sig[4].Value=0.0.0.0
>>> Sig[5].Name=Fault Module Timestamp
>>> Sig[5].Value=
>>> Sig[6].Name=Exception Code
>>> Sig[6].Value=c005
>>> Sig[7].Name=Exception Offset
>>> Sig[7].Value=PCH_DF_FROM_ntdll+0x000722CC
>>> DynamicSig[1].Name=OS Version
>>> DynamicSig[1].Value=10.0.15063.2.0.0.768.101
>>> DynamicSig[2].Name=Locale ID
>>> DynamicSig[2].Value=2057
>>> DynamicSig[22].Name=Additional Information 1
>>> DynamicSig[22].Value=b1df
>>> DynamicSig[23].Name=Additional Information 2
>>> DynamicSig[23].Value=b1df29542701a59c258e9db4cb35a612
>>> DynamicSig[24].Name=Additional Information 3
>>> DynamicSig[24].Value=ff08
>>> DynamicSig[25].Name=Additional Information 4
>>> DynamicSig[25].Value=ff08f5c1ef225ee708cc4b7d43225760
>>> UI[2]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\COM32on64.exe
>>> LoadedModule[0]=C:\Users\User\AppData\Roaming\Microsoft\
>>> AddIns\Bin\COM32on64.exe
>>> LoadedModule[1]=C:\WINDOWS\SYSTEM32\ntdll.dll
>>> LoadedModule[2]=C:\WINDOWS\System32\KERNEL32.DLL
>>> LoadedModule[3]=C:\WINDOWS\System32\KERNELBASE.dll
>>> LoadedModule[4]=C:\Users\User\AppData\Roaming\Microsoft\
>>> AddIns\Bin\MSVBVM60.DLL
>>> LoadedModule[5]=C:\WINDOWS\System32\USER32.dll
>>> LoadedModule[6]=C:\WINDOWS\System32\win32u.dll
>>> LoadedModule[7]=C:\WINDOWS\System32\GDI32.dll
>>> LoadedModule[8]=C:\WINDOWS\System32\gdi32full.dll
>>> LoadedModule[9]=C:\WINDOWS\System32\msvcp_win.dll
>>> LoadedModule[10]=C:\WINDOWS\System32\ucrtbase.dll
>>> LoadedModule[11]=C:\WINDOWS\System32\ADVAPI32.dll
>>> LoadedModule[12]=C:\WINDOWS\System32\msvcrt.dll
>>> LoadedModule[13]=C:\WINDOWS\System32\sechost.dll
>>> LoadedModule[14]=C:\WINDOWS\System32\RPCRT4.dll
>>> LoadedModule[15]=C:\WINDOWS\System32\SspiCli.dll
>>> LoadedModule[16]=C:\WINDOWS\System32\CRYPTBASE.dll
>>> LoadedModule[17]=C:\WINDOWS\System32\bcryptPrimitives.dll
>>> LoadedModule[18]=C:\WINDOWS\System32\ole32.dll
>>> LoadedModule[19]=C:\WINDOWS\System32\combase.dll
>>> LoadedModule[20]=C:\WINDOWS\System32\OLEAUT32.dll
>>> LoadedModule[21]=C:\WINDOWS\System32\IMM32.DLL
>>> LoadedModule[22]=C:\WINDOWS\System32\kernel.appcore.dll
>>> LoadedModule[23]=C:\WINDOWS\system32\uxtheme.dll
>>> LoadedModule[24]=C:\WINDOWS\SYSTEM32\SXS.DLL
>>> LoadedModule[25]=C:\WINDOWS\System32\MSCTF.dll
>>> LoadedModule[26]=C:\WINDOWS\System32\clbcatq.dll
>>> LoadedModule[27]=C:\Users\User\AppData\Roaming\
>>> Microsoft\AddIns\Bin\XL_SQL.dll
>>> LoadedModule[28]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\
>>> DirectCom.DLL
>>> LoadedModule[29]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\
>>> CustomMsgBox.dll
>>> LoadedModule[30]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\
>>> vbRichClient5.dll
>>> LoadedModule[31]=C:\WINDOWS\System32\SHELL32.DLL
>>> LoadedModule[32]=C:\WINDOWS\System32\cfgmgr32.dll
>>> LoadedModule[33]=C:\WINDOWS\System32\shcore.dll
>>> LoadedModule[34]=C:\WINDOWS\System32\windows.storage.dll
>>> LoadedModule[35]=C:\WINDOWS\System32\shlwapi.dll
>>> LoadedModule[36]=C:\WINDOWS\SYSTEM32\URLMON.DLL
>>> 

Re: [sqlite] Fwd: Problem on Windows 10 machines

[sqlite]

Another suggestion: start the application from WinDbg (part of Windows 
debugger tools). When the application crashes: type "k" for displaying 
the stack trace or "!analyze" for automated exception analysis.


Regards,

Renaat


Op 7/09/2017 om 12:56 schreef Bart Smissaert:

Noticed in the crash dump:

LoadedModule[4]=C:\Users\User\AppData\Roaming\Microsoft\
AddIns\Bin\MSVBVM60.DLL

This system dll shouldn't be in that folder and I deleted it, but didn't
solve any problem.


RBS

On Thu, Sep 7, 2017 at 11:41 AM, Bart Smissaert 
wrote:


Process Monitor got me a bit further as it showed there was a call to
WerFault.exe and this is the log of that:

Version=1
EventType=APPCRASH
EventTime=131492511990301638
ReportType=2
Consent=1
UploadTime=131492511998961952
ReportStatus=268439552
ReportIdentifier=9b4019d8-ff2c-48dc-b8e2-89b3d1065e90
IntegratorReportIdentifier=08a25108-492e-4c35-9572-7c0afa0cda3a
Wow64Host=34404
Wow64Guest=332
NsAppName=COM32on64.exe
AppSessionGuid=25fc-0003-0005-9115-7a33be27d301
TargetAppId=W:0006bf68cd3578efc2486facd1ee9646dcbe0904!
fca1aabcca076defa0f16adc639b6998469c2bb9!COM32on64.exe
TargetAppVer=2016//03//21:07:47:40!e334!COM32on64.exe
BootId=4294967295
ServiceSplit=3539031
TargetAsId=4701
Response.BucketId=91b0f4268c35a99eecbd35c544a753e4
Response.BucketTable=1
Response.LegacyBucketId=109122632689
Response.type=4
Sig[0].Name=Application Name
Sig[0].Value=COM32on64.exe
Sig[1].Name=Application Version
Sig[1].Value=1.0.0.0
Sig[2].Name=Application Timestamp
Sig[2].Value=56efa71c
Sig[3].Name=Fault Module Name
Sig[3].Value=StackHash_b1df
Sig[4].Name=Fault Module Version
Sig[4].Value=0.0.0.0
Sig[5].Name=Fault Module Timestamp
Sig[5].Value=
Sig[6].Name=Exception Code
Sig[6].Value=c005
Sig[7].Name=Exception Offset
Sig[7].Value=PCH_DF_FROM_ntdll+0x000722CC
DynamicSig[1].Name=OS Version
DynamicSig[1].Value=10.0.15063.2.0.0.768.101
DynamicSig[2].Name=Locale ID
DynamicSig[2].Value=2057
DynamicSig[22].Name=Additional Information 1
DynamicSig[22].Value=b1df
DynamicSig[23].Name=Additional Information 2
DynamicSig[23].Value=b1df29542701a59c258e9db4cb35a612
DynamicSig[24].Name=Additional Information 3
DynamicSig[24].Value=ff08
DynamicSig[25].Name=Additional Information 4
DynamicSig[25].Value=ff08f5c1ef225ee708cc4b7d43225760
UI[2]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\COM32on64.exe
LoadedModule[0]=C:\Users\User\AppData\Roaming\Microsoft\
AddIns\Bin\COM32on64.exe
LoadedModule[1]=C:\WINDOWS\SYSTEM32\ntdll.dll
LoadedModule[2]=C:\WINDOWS\System32\KERNEL32.DLL
LoadedModule[3]=C:\WINDOWS\System32\KERNELBASE.dll
LoadedModule[4]=C:\Users\User\AppData\Roaming\Microsoft\
AddIns\Bin\MSVBVM60.DLL
LoadedModule[5]=C:\WINDOWS\System32\USER32.dll
LoadedModule[6]=C:\WINDOWS\System32\win32u.dll
LoadedModule[7]=C:\WINDOWS\System32\GDI32.dll
LoadedModule[8]=C:\WINDOWS\System32\gdi32full.dll
LoadedModule[9]=C:\WINDOWS\System32\msvcp_win.dll
LoadedModule[10]=C:\WINDOWS\System32\ucrtbase.dll
LoadedModule[11]=C:\WINDOWS\System32\ADVAPI32.dll
LoadedModule[12]=C:\WINDOWS\System32\msvcrt.dll
LoadedModule[13]=C:\WINDOWS\System32\sechost.dll
LoadedModule[14]=C:\WINDOWS\System32\RPCRT4.dll
LoadedModule[15]=C:\WINDOWS\System32\SspiCli.dll
LoadedModule[16]=C:\WINDOWS\System32\CRYPTBASE.dll
LoadedModule[17]=C:\WINDOWS\System32\bcryptPrimitives.dll
LoadedModule[18]=C:\WINDOWS\System32\ole32.dll
LoadedModule[19]=C:\WINDOWS\System32\combase.dll
LoadedModule[20]=C:\WINDOWS\System32\OLEAUT32.dll
LoadedModule[21]=C:\WINDOWS\System32\IMM32.DLL
LoadedModule[22]=C:\WINDOWS\System32\kernel.appcore.dll
LoadedModule[23]=C:\WINDOWS\system32\uxtheme.dll
LoadedModule[24]=C:\WINDOWS\SYSTEM32\SXS.DLL
LoadedModule[25]=C:\WINDOWS\System32\MSCTF.dll
LoadedModule[26]=C:\WINDOWS\System32\clbcatq.dll
LoadedModule[27]=C:\Users\User\AppData\Roaming\
Microsoft\AddIns\Bin\XL_SQL.dll
LoadedModule[28]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\
DirectCom.DLL
LoadedModule[29]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\
CustomMsgBox.dll
LoadedModule[30]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\
vbRichClient5.dll
LoadedModule[31]=C:\WINDOWS\System32\SHELL32.DLL
LoadedModule[32]=C:\WINDOWS\System32\cfgmgr32.dll
LoadedModule[33]=C:\WINDOWS\System32\shcore.dll
LoadedModule[34]=C:\WINDOWS\System32\windows.storage.dll
LoadedModule[35]=C:\WINDOWS\System32\shlwapi.dll
LoadedModule[36]=C:\WINDOWS\SYSTEM32\URLMON.DLL
LoadedModule[37]=C:\WINDOWS\System32\powrprof.dll
LoadedModule[38]=C:\WINDOWS\System32\profapi.dll
LoadedModule[39]=C:\WINDOWS\SYSTEM32\iertutil.dll
LoadedModule[40]=C:\Users\User\AppData\Roaming\
Microsoft\AddIns\Bin\VB_CAIRO_SQLITE.DLL
LoadedModule[41]=C:\WINDOWS\SYSTEM32\DDRAW.DLL
LoadedModule[42]=C:\WINDOWS\SYSTEM32\OLEPRO32.DLL
LoadedModule[43]=C:\WINDOWS\SYSTEM32\DCIMAN32.dll
LoadedModule[44]=C:\WINDOWS\SYSTEM32\MSIMG32.dll
LoadedModule[45]=C:\WINDOWS\WinSxS\x86_microsoft.windows.common-controls_
6595b64144ccf1df_5.82.15063.413_none_55bc94a37c2a2854\comctl32.dll

Re: [sqlite] Proof that a line has been modified

[Igor Tandetnik]

On 9/7/2017 1:16 PM, Jens Alfke wrote:

On Sep 7, 2017, at 10:06 AM, Simon Slavin  wrote:

In that case any solution implemented entirely within SQLite is insecure 
because the admins can simply replace the entire file.  Or use a hex editor to 
replace the checksum values.  In cases like this the security the OP is asking 
for has to be built in at the OS level.


No, there are plenty of high level ways of tamper-proofing files. See my 
initial response.

(For example, Apple and Google use digital signatures to tamper-proof 
applications distributed through their app stores. Any modification to the app 
binary invalidates the signature, and the device will refuse to install or 
launch it.)


"Device will refuse to install" is precisely an instance of "security built in at 
the OS level".
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Jens Alfke]

> On Sep 7, 2017, at 10:06 AM, Simon Slavin  wrote:
> 
> In that case any solution implemented entirely within SQLite is insecure 
> because the admins can simply replace the entire file.  Or use a hex editor 
> to replace the checksum values.  In cases like this the security the OP is 
> asking for has to be built in at the OS level.

No, there are plenty of high level ways of tamper-proofing files. See my 
initial response.

(For example, Apple and Google use digital signatures to tamper-proof 
applications distributed through their app stores. Any modification to the app 
binary invalidates the signature, and the device will refuse to install or 
launch it.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite inmemory size (windows Task Manager)

[Simon Slavin]

On 7 Sep 2017, at 6:12pm, Karthi M  wrote:

>I am using SQLite inmemory database. Every time when data is
> loaded the PC RAM increases gradually. At some point of time the
> memory reduces to normal.

Please give us some idea of your 'normal" and "increased" amounts of memory.  
How much memory are you talking about ?

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite inmemory size (windows Task Manager)

[Karthi M]

HI All
I am using SQLite inmemory database. Every time when data is
loaded the PC RAM increases gradually. At some point of time the
memory reduces to normal. but still inmemory database works fine.
why memory shows high in task manager and reduces after sometime?
if the memory reduces to normal where inmemory stores the data?
Any idea one this??

Thanks & Regards,
Karthi M
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Simon Slavin]

On 7 Sep 2017, at 5:34pm, Jens Alfke  wrote:

> On Sep 7, 2017, at 9:31 AM, Simon Slavin  wrote:
> 
>> Either way, you should be able to do something like this with UPDATE and 
>> DELETE TRIGGERs which causes the new command to fail.  They could do this by 
>> violating a constraint, or by division by zero, or referring to a table 
>> which didn’t exist.  Those things should cause SQLite to crash or return a 
>> failure code rather than successfully replacing the original record.
> 
> That can be bypassed by editing the raw file data, without going through 
> SQLite, so I'm assuming it wouldn't be secure enough for the OP.

In that case any solution implemented entirely within SQLite is insecure 
because the admins can simply replace the entire file.  Or use a hex editor to 
replace the checksum values.  In cases like this the security the OP is asking 
for has to be built in at the OS level.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Jens Alfke]

> On Sep 7, 2017, at 9:31 AM, Simon Slavin  wrote:
> 
> Either way, you should be able to do something like this with UPDATE and 
> DELETE TRIGGERs which causes the new command to fail.  They could do this by 
> violating a constraint, or by division by zero, or referring to a table which 
> didn’t exist.  Those things should cause SQLite to crash or return a failure 
> code rather than successfully replacing the original record.

That can be bypassed by editing the raw file data, without going through 
SQLite, so I'm assuming it wouldn't be secure enough for the OP.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Jens Alfke]

> On Sep 7, 2017, at 1:38 AM, Paxdo  wrote:
> 
> But of course, someone who knows this checksum and its salt could make 
> changes on the table and recalculate all checksums. :-(


Use digital signatures. I can think of two approaches:

(a) Sign each row. The program doing the insertion would need to know the 
private key, while validation would require only the public key. The drawback 
is that whoever does the insertion can later modify any row, although no one 
else can.

(a) Use external signatures as official validation. Periodically have the 
entity with the private key compute a digest of the entire data set and sign 
that, adding the signature to the database. Anyone can regenerate that digest 
and verify the signature. The drawback is that any recent additions since the 
last signing aren't safe. And again, the entity with the private key can modify 
anything.

To guard against modifications by the private-key owner, other parties can 
remember previous signatures and raise a ruckus if they ever change in the 
future.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

[David Raymond]

Although it may not translate as well to the more complex examples, would you 
also consider adding the IN operator to your tests? I found for example that 
"select v from t1 where v in t2;" did even better than the join or the 
intersect.

Other "am I right in thinking this" question: INTERSECT is only going to be 
viable when comparing full records, correct? If you're looking to filter table 
A by whether its primary key is also a primary key for table B, but ignoring 
the other fields in both, then INTERSECT becomes not an option, or at least 
starts making the query more complex/ugly... correct?



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, September 07, 2017 8:06 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] JOIN vs. INTERSECT speed and efficiency


On 2017/09/07 3:43 AM, Keith Medcalf wrote:
> Try the same test using 147 columns in each table.

Exactly the plan for this weekend :)

> 1 column is rather trivial.  Even a kindergarten kid could do it in no time 
> using crayons and the wall.

So? That is non-sequitur, I am sure given enough crayons, wall-space and 
time, a kindergarten kid can do it with 147 columns too. That says 
exactly nothing about the possible efficiencies of different methods. If 
however the 1-columness of the test gets somehow advantaged by being the 
PK (as Nico pointed out) or real world data such as TEXT entries sort 
slower than INTs, then it might affect it, so the 147 column tests will 
tell.


> In other words except in very trivial cases (like having only one column that 
> is not nullable) it will be very difficult to write a "correct" JOIN or 
> correlated subquery that emulates an INTERSECT.

Well I agree, but it is those trivial cases that are of interest here, 
and if there is a general JOIN optimization to be had. The INTERSECT 
test merely served as the catalyst to put us on the trail of the 
possible JOIN optimization, if there is even an optimization to be had 
(it might yet be a wild goose chase, which you seem to have your money 
on, so watch this space, I'll graciously accept your "told ya!" later 
after testing).


Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Simon Slavin]

On 7 Sep 2017, at 9:16am, Paxdo  wrote:

> For security reasons, a customer wants to be sure that a database line cannot 
> be modified after its initial insertion (or unmodified without being visible, 
> with proof that the line has been modified). Including by technicians who can 
> open the database (SQLITE of course).

Is there a difference between "modified" and "deleted and then a new one put 
in" ?

Either way, you should be able to do something like this with UPDATE and DELETE 
TRIGGERs which causes the new command to fail.  They could do this by violating 
a constraint, or by division by zero, or referring to a table which didn’t 
exist.  Those things should cause SQLite to crash or return a failure code 
rather than successfully replacing the original record.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

[Nico Williams]

On Wed, Sep 06, 2017 at 07:43:07PM -0600, Keith Medcalf wrote:
> Try the same test using 147 columns in each table.
> 
> 1 column is rather trivial.  Even a kindergarten kid could do it in no
> time using crayons and the wall.
> 
> [...]
> 
> In other words except in very trivial cases (like having only one
> column that is not nullable) it will be very difficult to write a
> "correct" JOIN or correlated subquery that emulates an INTERSECT.

Yup.  But that doesn't mean that the engine couldn't internally build a
result-set from the query without some filtering JOIN, then implement
the same strategy as an INTERSECT.  You can't do this in SQL if the
filter table has different shape than the result set, but the engine
might be able to do it.

On the other hand, building a complete result set first is... not online
behavior.  If the result set size is enormous, then the INTERSECT
approach is going to make the user very unhappy!

I do think OP's tests point out a case where SQLite3 is pessimally
picking table scan over covering index scan...

...though scanning the index
means that there will be no rowid column in the result, which might
actually be a compatibility issue when using rowid tables, so maybe
SQLite3 is doing exactly the right thing?

I don't think that pessimization is too consequential as users can
improve the situation by adding ORDER BY clauses or using WITHOUT ROWID.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should the INTEGER not be cast to a REAL

[David Raymond]

sqlite> insert into weights values (null);
Error: CHECK constraint failed: float



How about ...check (cast(float as real) = float)... ?

sqlite> insert into weights values (1);

sqlite> insert into weights values (0);

sqlite> insert into weights values ('Hello');
Error: CHECK constraint failed: float

sqlite> insert into weights values (null);

sqlite> insert into weights values ('0');
--possible problem here since it takes it, but...

sqlite> select float, typeof(float) from weights;
float|typeof(float)
1.0|real
0.0|real
|null
0.0|real

--it stores it as a real anyway


Run Time: real 0.000 user 0.00 sys 0.00
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Wednesday, September 06, 2017 8:15 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Should the INTEGER not be cast to a REAL


On 2017/09/07 12:35 AM, Cecil Westerhof wrote:
> ​It does not, but this does:
> CREATE TABLE weights(
>  float  REAL,
>
>  CONSTRAINT float CHECK(TYPEOF(float) IN ("real","integer"))
> );
>
> Instead of "int" you need "integer".
>

yes of course... My bad, sorry, but at least you've solved it :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Problem on Windows 10 machines

[Keith Medcalf]

>I thought this was standard now on Win10. Not that I think it is
>relevant with my problem.

It is.  There is a syscall compiled version in System32 (64-bit) and a 32-bit 
version in SysWOW64.

named winsqlite3.dll

The App Store and the (modern app) Installer processes use sqlite3 databases to 
track dependencies, installations and so forth.  It is actually kina nice 
because when Windows gets itself in a tizzy you can open the databases with the 
command-line tool and "fix" (that is, remove) the broken entries, clean up the 
filesystem, and everything (modern app wise) will work again.  Especially 
helpful since the regular windows upgrade/install and the roll-back to previous 
version does not know how to "fix" updated versions of modern apps properly and 
if you can end up with a completely borked phone susbsystem on the computer 
which cannot even run the settings applet crap properly until you fix the 
database and let it re-install the apps.  Much better than the proprietary 
undocumented "no user serviceable parts inside" so typical of Microsoft.

Of course, the actual Store and the couple of services that use the database 
were written by a moron and excess data is cached that ought not be, so you 
have to cycle a bunch of services (or reboot) before it "notices" that the 
database was changed.


>On Thu, Sep 7, 2017 at 9:58 AM, Chris Locke
>
>wrote:
>
>> I'd suggest running the Microsoft Process Monitor
>> https://docs.microsoft.com/en-us/sysinternals/downloads/procmon
>>
>> When your application crashes, this will show the files it tried to
>access
>> before the crash.  It might point to a dependancy missing.
>> Have you 'installed' SQLite on your Win 10 machines?  I use
>> system.data.sqlite.dll in my applications, and that requires
>msvcr120.dll.
>> Without that, I get a weird 'SQLite.Interop.dll module could not be
>found'
>> error ... which makes sense, but its not strictly accurate ... its
>there,
>> it just can't be loaded.  SQLite requires a couple of extra files
>to run
>> properly.  They may not be installed on the Win 10 box.
>>
>> Ideally, you need a proper stack trace and error log from your
>application.
>>
>> > In Windows you get a frowny face "modern icon"
>>
>> Thats for a full-on Windows 'blue screen', not an application
>crash.  I
>> assume this isn't causing a blue-screen, but is just failing.
>>
>> > Could the problem be that SQLite is installed by MS already on
>those
>> machines?
>>
>> SQLite is a third party product, and would not be pre-installed by
>> Microsoft.
>>
>>
>> Thanks,
>> Chris
>>
>>
>> On Thu, Sep 7, 2017 at 8:45 AM, Bart Smissaert
>
>> wrote:
>>
>> > Yes, not very helpful. The message is from my VB6 wrapper as is
>like
>> this:
>> >
>> > Method ProcedureX of object _ClassX failed
>> >
>> > ClassX is the class in the wrapper ActiveX dll that also has the
>> procedure
>> > that makes the call to SQLite that causes the problem, in this
>> > case sqlite3_initialize.
>> > ProcedureX is another procedure in that same class, but that
>procedure
>> has
>> > nil to do with the problem.
>> > I can take that ProcedureX out and that I will get another
>procedure
>> > mentioned in the error message that is again completely unrelated
>to the
>> > problem.
>> >
>> > So the whole thing is just completely puzzling and I am seriously
>stuck
>> > with this.
>> >
>> >
>> > RBS
>> >
>> >
>> >
>> > On Thu, Sep 7, 2017 at 2:44 AM, Keith Medcalf
>
>> wrote:
>> >
>> > >
>> > > In Windows you get a frowny face "modern icon" (about 5 inches
>square)
>> > and
>> > > "something went wrong, sorry about your luck".
>> > >
>> > >
>> > > ---
>> > > The fact that there's a Highway to Hell but only a Stairway to
>Heaven
>> > says
>> > > a lot about anticipated traffic volume.
>> > >
>> > >
>> > > >-Original Message-
>> > > >From: sqlite-users [mailto:sqlite-users-
>> > > >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>> > > >Sent: Wednesday, 6 September, 2017 15:06
>> > > >To: SQLite mailing list
>> > > >Subject: Re: [sqlite] Fwd: Problem on Windows 10 machines
>> > > >
>> > > >
>> > > >
>> > > >On 6 Sep 2017, at 10:03pm, Bart Smissaert
>
>> > > >wrote:
>> > > >
>> > > >> When my wrapper makes the call to the Sqlite dll my app
>crashes
>> > > >
>> > > >With what error ?  Segmentation fault ?  Privilege violation ?
>I
>> > > >don’t think I’ve seen any crash which doesn’t produce an error
>report
>> > > >of some kind, even if we know that there’s no reason for that
>error
>> > > >at that point.
>> > > >
>> > > >Simon.
>> > > >___
>> > > >sqlite-users mailing list
>> > > >sqlite-users@mailinglists.sqlite.org
>> > > >http://mailinglists.sqlite.org/cgi-
>bin/mailman/listinfo/sqlite-users
>> > >
>> > >
>> > >
>> > > ___
>> > > sqlite-users mailing list
>> > > 

Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0

[Richard Damon]

On 9/6/17 9:42 PM, Jacky Lam wrote:

Hi All,
The reason I consider to use VACUUM is that:
when I insert 10k and delete 10k records for a number of times, the db file
size keeps constant in each iteration.
On the other hand, if I terminate the program manually and start the
iteration again, the db file size increase once in the first iteration.
Then it keeps file size constant afterwards.
For example,
1. when no manual termination of program, the file size keeps constant 100kB
2. when I terminate the program and start the iteration again, the file
size changes to 200kB in the first iteration.
3. After the first iteration, the file size keeps at 200kB in later
iterations.

Does anyone can advise me that which parts I am missing for the
Amalgamation compilation of USING *** OTHER VFS ***?

I'm thinking you don't understand why vacuum is not the default. Your 
examples show good reason why this is.


If an application doesn't delete a lot of records, it doesn't leave a 
lot of empty space so doesn't need vacuum.


If an application does delete a lot of records, it is very likely in the 
near future to create a similar number in the near future, so doing a 
vacuum to temporarily free the space is a lot of work for little gain.


Yes, there is a use case where you delete a lot of records and it will 
be a long time before more records are created, and perhaps freeing the 
space may be important, so in that particular case, the program should 
do the vacuum.


--
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

[R Smith]

On 2017/09/07 3:43 AM, Keith Medcalf wrote:

Try the same test using 147 columns in each table.


Exactly the plan for this weekend :)


1 column is rather trivial.  Even a kindergarten kid could do it in no time 
using crayons and the wall.


So? That is non-sequitur, I am sure given enough crayons, wall-space and 
time, a kindergarten kid can do it with 147 columns too. That says 
exactly nothing about the possible efficiencies of different methods. If 
however the 1-columness of the test gets somehow advantaged by being the 
PK (as Nico pointed out) or real world data such as TEXT entries sort 
slower than INTs, then it might affect it, so the 147 column tests will 
tell.




In other words except in very trivial cases (like having only one column that is not 
nullable) it will be very difficult to write a "correct" JOIN or correlated 
subquery that emulates an INTERSECT.


Well I agree, but it is those trivial cases that are of interest here, 
and if there is a general JOIN optimization to be had. The INTERSECT 
test merely served as the catalyst to put us on the trail of the 
possible JOIN optimization, if there is even an optimization to be had 
(it might yet be a wild goose chase, which you seem to have your money 
on, so watch this space, I'll graciously accept your "told ya!" later 
after testing).



Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Problem on Windows 10 machines

[Bart Smissaert]

> By the way, I am not sure exactly what programming platform or IDE you
are using

On the machine where I have this problem I use Win 10. On the main dev
machine I have Win 7. I am coding in the old VB6 (Classic VB).
The only other environment I could think of to test would be VBA, but that
is very similar to VB6. In fact the VB6 ActiveX dll is called from VBA
(Excel).

RBS




On Thu, Sep 7, 2017 at 12:24 PM, R Smith  wrote:

>
>
> On 2017/09/07 11:09 AM, Bart Smissaert wrote:
>
>>
>> SQLite is a third party product, and would not be pre-installed by
>>>
>> Microsoft.
>>
>> I thought this was standard now on Win10. Not that I think it is relevant
>> with my problem.
>>
>
> SQLite is actually found on Windows installations, but only in support to
> it's own uses, such as for Skype.
>
> There is no clean Windows installation I know of where you could open a
> command prompt and type "sqlite3.exe" where anything would execute, nor
> find an sqlite3.dll as a standard in the Win32 library folders (unless some
> other installed app put it there). To be fair, I only use Win10 64bit Prof
> - maybe it is included now on others?
>
> By the way, I am not sure exactly what programming platform or IDE you are
> using, but that must be the worst debugger in the World. Stacktraces and
> steppers should be able to take you to the exact errors in even the most
> simple programming environments these days, unless it itself is the thing
> that broke (which is quite possible!). Any chance you use multiple
> programming environments and can test the same function in another one, but
> on the same computer and OS?
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Problem on Windows 10 machines

[R Smith]

On 2017/09/07 11:09 AM, Bart Smissaert wrote:



SQLite is a third party product, and would not be pre-installed by

Microsoft.

I thought this was standard now on Win10. Not that I think it is relevant
with my problem.


SQLite is actually found on Windows installations, but only in support 
to it's own uses, such as for Skype.


There is no clean Windows installation I know of where you could open a 
command prompt and type "sqlite3.exe" where anything would execute, nor 
find an sqlite3.dll as a standard in the Win32 library folders (unless 
some other installed app put it there). To be fair, I only use Win10 
64bit Prof - maybe it is included now on others?


By the way, I am not sure exactly what programming platform or IDE you 
are using, but that must be the worst debugger in the World. Stacktraces 
and steppers should be able to take you to the exact errors in even the 
most simple programming environments these days, unless it itself is the 
thing that broke (which is quite possible!). Any chance you use multiple 
programming environments and can test the same function in another one, 
but on the same computer and OS?



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Problem on Windows 10 machines

[Bart Smissaert]

Noticed in the crash dump:

LoadedModule[4]=C:\Users\User\AppData\Roaming\Microsoft\
AddIns\Bin\MSVBVM60.DLL

This system dll shouldn't be in that folder and I deleted it, but didn't
solve any problem.


RBS

On Thu, Sep 7, 2017 at 11:41 AM, Bart Smissaert 
wrote:

> Process Monitor got me a bit further as it showed there was a call to
> WerFault.exe and this is the log of that:
>
> Version=1
> EventType=APPCRASH
> EventTime=131492511990301638
> ReportType=2
> Consent=1
> UploadTime=131492511998961952
> ReportStatus=268439552
> ReportIdentifier=9b4019d8-ff2c-48dc-b8e2-89b3d1065e90
> IntegratorReportIdentifier=08a25108-492e-4c35-9572-7c0afa0cda3a
> Wow64Host=34404
> Wow64Guest=332
> NsAppName=COM32on64.exe
> AppSessionGuid=25fc-0003-0005-9115-7a33be27d301
> TargetAppId=W:0006bf68cd3578efc2486facd1ee9646dcbe0904!
> fca1aabcca076defa0f16adc639b6998469c2bb9!COM32on64.exe
> TargetAppVer=2016//03//21:07:47:40!e334!COM32on64.exe
> BootId=4294967295
> ServiceSplit=3539031
> TargetAsId=4701
> Response.BucketId=91b0f4268c35a99eecbd35c544a753e4
> Response.BucketTable=1
> Response.LegacyBucketId=109122632689
> Response.type=4
> Sig[0].Name=Application Name
> Sig[0].Value=COM32on64.exe
> Sig[1].Name=Application Version
> Sig[1].Value=1.0.0.0
> Sig[2].Name=Application Timestamp
> Sig[2].Value=56efa71c
> Sig[3].Name=Fault Module Name
> Sig[3].Value=StackHash_b1df
> Sig[4].Name=Fault Module Version
> Sig[4].Value=0.0.0.0
> Sig[5].Name=Fault Module Timestamp
> Sig[5].Value=
> Sig[6].Name=Exception Code
> Sig[6].Value=c005
> Sig[7].Name=Exception Offset
> Sig[7].Value=PCH_DF_FROM_ntdll+0x000722CC
> DynamicSig[1].Name=OS Version
> DynamicSig[1].Value=10.0.15063.2.0.0.768.101
> DynamicSig[2].Name=Locale ID
> DynamicSig[2].Value=2057
> DynamicSig[22].Name=Additional Information 1
> DynamicSig[22].Value=b1df
> DynamicSig[23].Name=Additional Information 2
> DynamicSig[23].Value=b1df29542701a59c258e9db4cb35a612
> DynamicSig[24].Name=Additional Information 3
> DynamicSig[24].Value=ff08
> DynamicSig[25].Name=Additional Information 4
> DynamicSig[25].Value=ff08f5c1ef225ee708cc4b7d43225760
> UI[2]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\COM32on64.exe
> LoadedModule[0]=C:\Users\User\AppData\Roaming\Microsoft\
> AddIns\Bin\COM32on64.exe
> LoadedModule[1]=C:\WINDOWS\SYSTEM32\ntdll.dll
> LoadedModule[2]=C:\WINDOWS\System32\KERNEL32.DLL
> LoadedModule[3]=C:\WINDOWS\System32\KERNELBASE.dll
> LoadedModule[4]=C:\Users\User\AppData\Roaming\Microsoft\
> AddIns\Bin\MSVBVM60.DLL
> LoadedModule[5]=C:\WINDOWS\System32\USER32.dll
> LoadedModule[6]=C:\WINDOWS\System32\win32u.dll
> LoadedModule[7]=C:\WINDOWS\System32\GDI32.dll
> LoadedModule[8]=C:\WINDOWS\System32\gdi32full.dll
> LoadedModule[9]=C:\WINDOWS\System32\msvcp_win.dll
> LoadedModule[10]=C:\WINDOWS\System32\ucrtbase.dll
> LoadedModule[11]=C:\WINDOWS\System32\ADVAPI32.dll
> LoadedModule[12]=C:\WINDOWS\System32\msvcrt.dll
> LoadedModule[13]=C:\WINDOWS\System32\sechost.dll
> LoadedModule[14]=C:\WINDOWS\System32\RPCRT4.dll
> LoadedModule[15]=C:\WINDOWS\System32\SspiCli.dll
> LoadedModule[16]=C:\WINDOWS\System32\CRYPTBASE.dll
> LoadedModule[17]=C:\WINDOWS\System32\bcryptPrimitives.dll
> LoadedModule[18]=C:\WINDOWS\System32\ole32.dll
> LoadedModule[19]=C:\WINDOWS\System32\combase.dll
> LoadedModule[20]=C:\WINDOWS\System32\OLEAUT32.dll
> LoadedModule[21]=C:\WINDOWS\System32\IMM32.DLL
> LoadedModule[22]=C:\WINDOWS\System32\kernel.appcore.dll
> LoadedModule[23]=C:\WINDOWS\system32\uxtheme.dll
> LoadedModule[24]=C:\WINDOWS\SYSTEM32\SXS.DLL
> LoadedModule[25]=C:\WINDOWS\System32\MSCTF.dll
> LoadedModule[26]=C:\WINDOWS\System32\clbcatq.dll
> LoadedModule[27]=C:\Users\User\AppData\Roaming\
> Microsoft\AddIns\Bin\XL_SQL.dll
> LoadedModule[28]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\
> DirectCom.DLL
> LoadedModule[29]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\
> CustomMsgBox.dll
> LoadedModule[30]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\
> vbRichClient5.dll
> LoadedModule[31]=C:\WINDOWS\System32\SHELL32.DLL
> LoadedModule[32]=C:\WINDOWS\System32\cfgmgr32.dll
> LoadedModule[33]=C:\WINDOWS\System32\shcore.dll
> LoadedModule[34]=C:\WINDOWS\System32\windows.storage.dll
> LoadedModule[35]=C:\WINDOWS\System32\shlwapi.dll
> LoadedModule[36]=C:\WINDOWS\SYSTEM32\URLMON.DLL
> LoadedModule[37]=C:\WINDOWS\System32\powrprof.dll
> LoadedModule[38]=C:\WINDOWS\System32\profapi.dll
> LoadedModule[39]=C:\WINDOWS\SYSTEM32\iertutil.dll
> LoadedModule[40]=C:\Users\User\AppData\Roaming\
> Microsoft\AddIns\Bin\VB_CAIRO_SQLITE.DLL
> LoadedModule[41]=C:\WINDOWS\SYSTEM32\DDRAW.DLL
> LoadedModule[42]=C:\WINDOWS\SYSTEM32\OLEPRO32.DLL
> LoadedModule[43]=C:\WINDOWS\SYSTEM32\DCIMAN32.dll
> LoadedModule[44]=C:\WINDOWS\SYSTEM32\MSIMG32.dll
> LoadedModule[45]=C:\WINDOWS\WinSxS\x86_microsoft.windows.common-controls_
> 6595b64144ccf1df_5.82.15063.413_none_55bc94a37c2a2854\comctl32.dll
> LoadedModule[46]=C:\WINDOWS\SYSTEM32\atl.DLL
> 

Re: [sqlite] Problem with mailing list

[Bart Smissaert]

Thanks, will look into that.
Nearly always though I do see the message that I sent appearing in my
inbox, although
sometimes (often) it does take some time, some like a day or so.
Just occasionally it doesn't.

RBS



On Thu, Sep 7, 2017 at 11:47 AM, Richard Damon 
wrote:

> On 9/6/17 4:28 AM, Bart Smissaert wrote:
>
>> For some reason it seems postings I send sometimes don't get through or
>> maybe they do get through but I can't see them.
>> I posted something yesterday at 12:55 pm (Problem on Windows 10
>> machines) and I still can't see that posting on the list.
>> I mail from my normal GMail account.
>> Is there anything that I might be doing wrong?
>>
>> RBS
>>
>
> One thing to point out, is that Gmail has a 'feature' where it suppresses
> duplicate messages, and normally considers the message you get back from
> the list to be a duplicate of the one you send, so it doesn't show it to
> you. (unless you get messages from the list in something like a digest).
>
> --
> Richard Damon
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with mailing list

[Richard Damon]

On 9/6/17 4:28 AM, Bart Smissaert wrote:

For some reason it seems postings I send sometimes don't get through or
maybe they do get through but I can't see them.
I posted something yesterday at 12:55 pm (Problem on Windows 10
machines) and I still can't see that posting on the list.
I mail from my normal GMail account.
Is there anything that I might be doing wrong?

RBS


One thing to point out, is that Gmail has a 'feature' where it 
suppresses duplicate messages, and normally considers the message you 
get back from the list to be a duplicate of the one you send, so it 
doesn't show it to you. (unless you get messages from the list in 
something like a digest).


--
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Problem on Windows 10 machines

[Bart Smissaert]

Process Monitor got me a bit further as it showed there was a call to
WerFault.exe and this is the log of that:

Version=1
EventType=APPCRASH
EventTime=131492511990301638
ReportType=2
Consent=1
UploadTime=131492511998961952
ReportStatus=268439552
ReportIdentifier=9b4019d8-ff2c-48dc-b8e2-89b3d1065e90
IntegratorReportIdentifier=08a25108-492e-4c35-9572-7c0afa0cda3a
Wow64Host=34404
Wow64Guest=332
NsAppName=COM32on64.exe
AppSessionGuid=25fc-0003-0005-9115-7a33be27d301
TargetAppId=W:0006bf68cd3578efc2486facd1ee9646dcbe0904!fca1aabcca076defa0f16adc639b6998469c2bb9!COM32on64.exe
TargetAppVer=2016//03//21:07:47:40!e334!COM32on64.exe
BootId=4294967295
ServiceSplit=3539031
TargetAsId=4701
Response.BucketId=91b0f4268c35a99eecbd35c544a753e4
Response.BucketTable=1
Response.LegacyBucketId=109122632689
Response.type=4
Sig[0].Name=Application Name
Sig[0].Value=COM32on64.exe
Sig[1].Name=Application Version
Sig[1].Value=1.0.0.0
Sig[2].Name=Application Timestamp
Sig[2].Value=56efa71c
Sig[3].Name=Fault Module Name
Sig[3].Value=StackHash_b1df
Sig[4].Name=Fault Module Version
Sig[4].Value=0.0.0.0
Sig[5].Name=Fault Module Timestamp
Sig[5].Value=
Sig[6].Name=Exception Code
Sig[6].Value=c005
Sig[7].Name=Exception Offset
Sig[7].Value=PCH_DF_FROM_ntdll+0x000722CC
DynamicSig[1].Name=OS Version
DynamicSig[1].Value=10.0.15063.2.0.0.768.101
DynamicSig[2].Name=Locale ID
DynamicSig[2].Value=2057
DynamicSig[22].Name=Additional Information 1
DynamicSig[22].Value=b1df
DynamicSig[23].Name=Additional Information 2
DynamicSig[23].Value=b1df29542701a59c258e9db4cb35a612
DynamicSig[24].Name=Additional Information 3
DynamicSig[24].Value=ff08
DynamicSig[25].Name=Additional Information 4
DynamicSig[25].Value=ff08f5c1ef225ee708cc4b7d43225760
UI[2]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\COM32on64.exe
LoadedModule[0]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\COM32on64.exe
LoadedModule[1]=C:\WINDOWS\SYSTEM32\ntdll.dll
LoadedModule[2]=C:\WINDOWS\System32\KERNEL32.DLL
LoadedModule[3]=C:\WINDOWS\System32\KERNELBASE.dll
LoadedModule[4]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\MSVBVM60.DLL
LoadedModule[5]=C:\WINDOWS\System32\USER32.dll
LoadedModule[6]=C:\WINDOWS\System32\win32u.dll
LoadedModule[7]=C:\WINDOWS\System32\GDI32.dll
LoadedModule[8]=C:\WINDOWS\System32\gdi32full.dll
LoadedModule[9]=C:\WINDOWS\System32\msvcp_win.dll
LoadedModule[10]=C:\WINDOWS\System32\ucrtbase.dll
LoadedModule[11]=C:\WINDOWS\System32\ADVAPI32.dll
LoadedModule[12]=C:\WINDOWS\System32\msvcrt.dll
LoadedModule[13]=C:\WINDOWS\System32\sechost.dll
LoadedModule[14]=C:\WINDOWS\System32\RPCRT4.dll
LoadedModule[15]=C:\WINDOWS\System32\SspiCli.dll
LoadedModule[16]=C:\WINDOWS\System32\CRYPTBASE.dll
LoadedModule[17]=C:\WINDOWS\System32\bcryptPrimitives.dll
LoadedModule[18]=C:\WINDOWS\System32\ole32.dll
LoadedModule[19]=C:\WINDOWS\System32\combase.dll
LoadedModule[20]=C:\WINDOWS\System32\OLEAUT32.dll
LoadedModule[21]=C:\WINDOWS\System32\IMM32.DLL
LoadedModule[22]=C:\WINDOWS\System32\kernel.appcore.dll
LoadedModule[23]=C:\WINDOWS\system32\uxtheme.dll
LoadedModule[24]=C:\WINDOWS\SYSTEM32\SXS.DLL
LoadedModule[25]=C:\WINDOWS\System32\MSCTF.dll
LoadedModule[26]=C:\WINDOWS\System32\clbcatq.dll
LoadedModule[27]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\XL_SQL.dll
LoadedModule[28]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\DirectCom.DLL
LoadedModule[29]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\CustomMsgBox.dll
LoadedModule[30]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\vbRichClient5.dll
LoadedModule[31]=C:\WINDOWS\System32\SHELL32.DLL
LoadedModule[32]=C:\WINDOWS\System32\cfgmgr32.dll
LoadedModule[33]=C:\WINDOWS\System32\shcore.dll
LoadedModule[34]=C:\WINDOWS\System32\windows.storage.dll
LoadedModule[35]=C:\WINDOWS\System32\shlwapi.dll
LoadedModule[36]=C:\WINDOWS\SYSTEM32\URLMON.DLL
LoadedModule[37]=C:\WINDOWS\System32\powrprof.dll
LoadedModule[38]=C:\WINDOWS\System32\profapi.dll
LoadedModule[39]=C:\WINDOWS\SYSTEM32\iertutil.dll
LoadedModule[40]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\VB_CAIRO_SQLITE.DLL
LoadedModule[41]=C:\WINDOWS\SYSTEM32\DDRAW.DLL
LoadedModule[42]=C:\WINDOWS\SYSTEM32\OLEPRO32.DLL
LoadedModule[43]=C:\WINDOWS\SYSTEM32\DCIMAN32.dll
LoadedModule[44]=C:\WINDOWS\SYSTEM32\MSIMG32.dll
LoadedModule[45]=C:\WINDOWS\WinSxS\x86_microsoft.windows.common-controls_6595b64144ccf1df_5.82.15063.413_none_55bc94a37c2a2854\comctl32.dll
LoadedModule[46]=C:\WINDOWS\SYSTEM32\atl.DLL
LoadedModule[47]=C:\WINDOWS\WinSxS\x86_microsoft.windows.gdiplus_6595b64144ccf1df_1.1.15063.483_none_9e9856e456d5e776\gdiplus.DLL
LoadedModule[48]=C:\WINDOWS\SYSTEM32\winmm.dll
LoadedModule[49]=C:\WINDOWS\SYSTEM32\winmmbase.dll
LoadedModule[50]=C:\Users\User\AppData\Roaming\Microsoft\AddIns\Bin\SQLite3_StdCall.dll
LoadedModule[51]=C:\WINDOWS\SYSTEM32\MSVCR120.dll
State[0].Key=Transport.DoneStage1
State[0].Value=1
FriendlyEventName=Stopped working
ConsentKey=APPCRASH
AppName=COM32on64

Re: [sqlite] [EXTERNAL] Performance impact of UPDATEing multiple columns vs few columns

[Hick Gunter]

SQLite currently implements UPDATE by pretending it is SELECTing all the 
fields, except a SET clause causes the expression(s) to be evaluated instead of 
the current field value(s).

Are you using a single prepared statement and binding values (in which case, 
how do you know what values to bind for the "non-updated" columns?) or are you 
creating query strings?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von ghalwasi
Gesendet: Mittwoch, 06. September 2017 17:57
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Performance impact of UPDATEing multiple columns 
vs few columns

I am using SQLite C library in my application and I have a question regarding 
updating "mutiple" columns using UPDATE statement.
Lets suppose, my Database table has 10 columns (c1, c2 ... c10). My question is 
that what will be the difference (in context of CPU cycles & performance) if i 
UPDATE multiple columns or only few columns.

Lets say if the requirement is to just update c2 & c3 but if we are updating 
c2, c3,c4,c5,c6,c7

UPDATE db SET c2,c3,c4,c5,c6,c7 WHERE c1=x (c1 is a primary key) or UPDATE db 
SET c2,c3 WHERE c1=x (c1 is a primary key)




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Should the INTEGER not be cast to a REAL

[Hick Gunter]

The following code fragment from explain output illustrates the problem:

asql> explain insert into t values (0);
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
...
5 Integer0 3 000  NULL
6 Copy   3 5 000  NULL
7 Function   0 5 4 typeof(1)  01  NULL
8 String80 6 0 real   00  NULL
9 Eq 6 1146a  NULL
10Halt   192 0 constraint float failed  00  NULL
11MakeRecord 3 1 6 e  00  NULL

The typeof function is called on a copy of the value, which is an integer, and 
causes the check constraint to fail, *before* the MakeRecord opcode would cause 
the integer value to be stored as a float.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Cecil Westerhof
Gesendet: Donnerstag, 07. September 2017 00:06
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Should the INTEGER not be cast to a REAL

2017-09-06 23:58 GMT+02:00 R Smith :

> On 2017/09/06 11:37 PM, Cecil Westerhof wrote:
>
>> But should in the first case the 0 not be cast to a 0.0?
>>
>
> What makes you believe SQLite should massage the data into specific
> types for you without you requesting it explicitly?
>

​At https://www.sqlite.org/datatype3.html I read:
A column with REAL affinity behaves like a column with NUMERIC affinity except 
that it forces integer values into floating point representation.​




> In fact, that would consume valuable extra CPU cycles and would
> definitely make most of us unhappy.
>
> Your CHECK constraint should really find that the value is acceptable
> when it is either a REAL, OR an INT, because both those types of data
> satisfies your requirement.


​Because of the above I thought it not necessary.

I could change it of-course. The only thing could be if they enter am integer, 
then maybe they made a mistake.

--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Problem on Windows 10 machines

[Bart Smissaert]

> I'd suggest running the Microsoft Process Monitor

Thanks, will try that.

> Have you 'installed' SQLite on your Win 10 machines?

There is no SQLite installation as I use my own VB6 wrapper (ActiveX dll).

> SQLite is a third party product, and would not be pre-installed by
Microsoft.

I thought this was standard now on Win10. Not that I think it is relevant
with my problem.


RBS



On Thu, Sep 7, 2017 at 9:58 AM, Chris Locke 
wrote:

> I'd suggest running the Microsoft Process Monitor
> https://docs.microsoft.com/en-us/sysinternals/downloads/procmon
>
> When your application crashes, this will show the files it tried to access
> before the crash.  It might point to a dependancy missing.
> Have you 'installed' SQLite on your Win 10 machines?  I use
> system.data.sqlite.dll in my applications, and that requires msvcr120.dll.
> Without that, I get a weird 'SQLite.Interop.dll module could not be found'
> error ... which makes sense, but its not strictly accurate ... its there,
> it just can't be loaded.  SQLite requires a couple of extra files to run
> properly.  They may not be installed on the Win 10 box.
>
> Ideally, you need a proper stack trace and error log from your application.
>
> > In Windows you get a frowny face "modern icon"
>
> Thats for a full-on Windows 'blue screen', not an application crash.  I
> assume this isn't causing a blue-screen, but is just failing.
>
> > Could the problem be that SQLite is installed by MS already on those
> machines?
>
> SQLite is a third party product, and would not be pre-installed by
> Microsoft.
>
>
> Thanks,
> Chris
>
>
> On Thu, Sep 7, 2017 at 8:45 AM, Bart Smissaert 
> wrote:
>
> > Yes, not very helpful. The message is from my VB6 wrapper as is like
> this:
> >
> > Method ProcedureX of object _ClassX failed
> >
> > ClassX is the class in the wrapper ActiveX dll that also has the
> procedure
> > that makes the call to SQLite that causes the problem, in this
> > case sqlite3_initialize.
> > ProcedureX is another procedure in that same class, but that procedure
> has
> > nil to do with the problem.
> > I can take that ProcedureX out and that I will get another procedure
> > mentioned in the error message that is again completely unrelated to the
> > problem.
> >
> > So the whole thing is just completely puzzling and I am seriously stuck
> > with this.
> >
> >
> > RBS
> >
> >
> >
> > On Thu, Sep 7, 2017 at 2:44 AM, Keith Medcalf 
> wrote:
> >
> > >
> > > In Windows you get a frowny face "modern icon" (about 5 inches square)
> > and
> > > "something went wrong, sorry about your luck".
> > >
> > >
> > > ---
> > > The fact that there's a Highway to Hell but only a Stairway to Heaven
> > says
> > > a lot about anticipated traffic volume.
> > >
> > >
> > > >-Original Message-
> > > >From: sqlite-users [mailto:sqlite-users-
> > > >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
> > > >Sent: Wednesday, 6 September, 2017 15:06
> > > >To: SQLite mailing list
> > > >Subject: Re: [sqlite] Fwd: Problem on Windows 10 machines
> > > >
> > > >
> > > >
> > > >On 6 Sep 2017, at 10:03pm, Bart Smissaert 
> > > >wrote:
> > > >
> > > >> When my wrapper makes the call to the Sqlite dll my app crashes
> > > >
> > > >With what error ?  Segmentation fault ?  Privilege violation ?  I
> > > >don’t think I’ve seen any crash which doesn’t produce an error report
> > > >of some kind, even if we know that there’s no reason for that error
> > > >at that point.
> > > >
> > > >Simon.
> > > >___
> > > >sqlite-users mailing list
> > > >sqlite-users@mailinglists.sqlite.org
> > > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > >
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Problem on Windows 10 machines

[Chris Locke]

I'd suggest running the Microsoft Process Monitor
https://docs.microsoft.com/en-us/sysinternals/downloads/procmon

When your application crashes, this will show the files it tried to access
before the crash.  It might point to a dependancy missing.
Have you 'installed' SQLite on your Win 10 machines?  I use
system.data.sqlite.dll in my applications, and that requires msvcr120.dll.
Without that, I get a weird 'SQLite.Interop.dll module could not be found'
error ... which makes sense, but its not strictly accurate ... its there,
it just can't be loaded.  SQLite requires a couple of extra files to run
properly.  They may not be installed on the Win 10 box.

Ideally, you need a proper stack trace and error log from your application.

> In Windows you get a frowny face "modern icon"

Thats for a full-on Windows 'blue screen', not an application crash.  I
assume this isn't causing a blue-screen, but is just failing.

> Could the problem be that SQLite is installed by MS already on those
machines?

SQLite is a third party product, and would not be pre-installed by
Microsoft.


Thanks,
Chris


On Thu, Sep 7, 2017 at 8:45 AM, Bart Smissaert 
wrote:

> Yes, not very helpful. The message is from my VB6 wrapper as is like this:
>
> Method ProcedureX of object _ClassX failed
>
> ClassX is the class in the wrapper ActiveX dll that also has the procedure
> that makes the call to SQLite that causes the problem, in this
> case sqlite3_initialize.
> ProcedureX is another procedure in that same class, but that procedure has
> nil to do with the problem.
> I can take that ProcedureX out and that I will get another procedure
> mentioned in the error message that is again completely unrelated to the
> problem.
>
> So the whole thing is just completely puzzling and I am seriously stuck
> with this.
>
>
> RBS
>
>
>
> On Thu, Sep 7, 2017 at 2:44 AM, Keith Medcalf  wrote:
>
> >
> > In Windows you get a frowny face "modern icon" (about 5 inches square)
> and
> > "something went wrong, sorry about your luck".
> >
> >
> > ---
> > The fact that there's a Highway to Hell but only a Stairway to Heaven
> says
> > a lot about anticipated traffic volume.
> >
> >
> > >-Original Message-
> > >From: sqlite-users [mailto:sqlite-users-
> > >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
> > >Sent: Wednesday, 6 September, 2017 15:06
> > >To: SQLite mailing list
> > >Subject: Re: [sqlite] Fwd: Problem on Windows 10 machines
> > >
> > >
> > >
> > >On 6 Sep 2017, at 10:03pm, Bart Smissaert 
> > >wrote:
> > >
> > >> When my wrapper makes the call to the Sqlite dll my app crashes
> > >
> > >With what error ?  Segmentation fault ?  Privilege violation ?  I
> > >don’t think I’ve seen any crash which doesn’t produce an error report
> > >of some kind, even if we know that there’s no reason for that error
> > >at that point.
> > >
> > >Simon.
> > >___
> > >sqlite-users mailing list
> > >sqlite-users@mailinglists.sqlite.org
> > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Clemens Ladisch]

Paxdo wrote:
> For security reasons, a customer wants to be sure that a database line
> cannot be modified after its initial insertion (or unmodified without
> being visible, with proof that the line has been modified). Including
> by technicians who can open the database (SQLITE of course).

Something like .

See git or fossil, where the current state of the entire repository is
identified by a hash over all data and all previous changes; once a hash
value has been published, it is not possible to change anything without
changing the hash value.  (You need to save the hash values of the
interesting revisions somewhere else to be able to check them.)

These tools work on files, not on database objects.  But you could put
the entire database file in the repository.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proof that a line has been modified

[Paxdo]

I will be able to make a checksum calculated on all the columns of the 
row AND on the checksum of the previous row. So if you go back up in the 
table and recalculate all checksums, you could check that a line has 
been modified or deleted.


But of course, someone who knows this checksum and its salt could make 
changes on the table and recalculate all checksums. :-(



Paxdo 
7 septembre 2017 à 10:16

Hi all!

For security reasons, a customer wants to be sure that a database line 
cannot be modified after its initial insertion (or unmodified without 
being visible, with proof that the line has been modified). Including 
by technicians who can open the database (SQLITE of course).


Is there a solution to that?

I thought of a hash calculated and recorded only when the line was 
inserted (calculated on all columns of the line, and stored in a 
column of that line).


Or a trigger that would prevent any modification.

But these solutions can be easily bypassed, right?

Do you have any advice?

Thank you!

Tom
(Sorry for my bad english)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance impact of UPDATEing multiple columns vs few columns

[Clemens Ladisch]

ghalwasi wrote:
> what will be the difference (in context of CPU cycles & performance)
> if i UPDATE multiple columns or only few columns.

SQLite always rewrites the entire row, so there is no practical
difference.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Proof that a line has been modified

[Paxdo]

Hi all!

For security reasons, a customer wants to be sure that a database line 
cannot be modified after its initial insertion (or unmodified without 
being visible, with proof that the line has been modified). Including by 
technicians who can open the database (SQLITE of course).


Is there a solution to that?

I thought of a hash calculated and recorded only when the line was 
inserted (calculated on all columns of the line, and stored in a column 
of that line).


Or a trigger that would prevent any modification.

But these solutions can be easily bypassed, right?

Do you have any advice?

Thank you!

Tom
(Sorry for my bad english)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible thread-safety bug in lemon parser with ParseTrace()

[Richard Hipp]

On 9/7/17, Fletcher T. Penney  wrote:
> 1)  I hope I am reporting this to the right place.  If not, my apologies.
>
> 2) I have been using lemon parsers for a year or more, but am by no
> means an export on the lemon source itself.  I did not see this issue
> referenced elsewhere, my apologies if I missed it.
>
>
> I *think* there is a thread-safety issue in the ParseTrace() function:

Yeah, but ParseTrace() is a debugging function, not intended for use
in production systems.  It shouldn't ever be used by multiple threads.
It is not intended to be threadsafe.

If you found a thread-safety issue in the Parse() function, that would
be more interesting!



>
>   void ParseTrace(FILE *TraceFILE, char *zTracePrompt){
> yyTraceFILE = TraceFILE;
> yyTracePrompt = zTracePrompt;
> if( yyTraceFILE==0 ) yyTracePrompt = 0;
> else if( yyTracePrompt==0 ) yyTraceFILE = 0;
>   }
>
> It appears that `yyTraceFILE` and `yyTracePrompt` are global variables
> that can conceivably be written to simultaneously on two separate
> threads.  I suspect the negative effects of this would be low
> (overwriting of one prompt with another, and they would likely be
> identical strings anyway).
>
>
> It is detected by Xcode's Thread Sanitizer, and I wanted to report it in
> case there was a more untoward effect that I was missing.  If nothing
> needs to be done about it, that's fine too.
>
>
> Thanks!
>
> Fletcher
>
>
>
>
> --
> Fletcher T. Penney
> fletc...@fletcherpenney.net
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] Possible thread-safety bug in lemon parser with ParseTrace()

[Fletcher T. Penney]

1)  I hope I am reporting this to the right place.  If not, my apologies.

2) I have been using lemon parsers for a year or more, but am by no 
means an export on the lemon source itself.  I did not see this issue 
referenced elsewhere, my apologies if I missed it.



I *think* there is a thread-safety issue in the ParseTrace() function:

void ParseTrace(FILE *TraceFILE, char *zTracePrompt){
  yyTraceFILE = TraceFILE;
  yyTracePrompt = zTracePrompt;
  if( yyTraceFILE==0 ) yyTracePrompt = 0;
  else if( yyTracePrompt==0 ) yyTraceFILE = 0;
}

It appears that `yyTraceFILE` and `yyTracePrompt` are global variables 
that can conceivably be written to simultaneously on two separate 
threads.  I suspect the negative effects of this would be low 
(overwriting of one prompt with another, and they would likely be 
identical strings anyway).



It is detected by Xcode's Thread Sanitizer, and I wanted to report it in 
case there was a more untoward effect that I was missing.  If nothing 
needs to be done about it, that's fine too.



Thanks!

Fletcher




--
Fletcher T. Penney
fletc...@fletcherpenney.net
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug Report: Crash When Loading Short Journal

[Natalie Silvanovich]

Hi,

I'm experiencing a crash when loading a database with a corrupt journal
file. The error occurs in readMasterJournal in the following code:

│48741 if( SQLITE_OK!=(rc = sqlite3OsFileSize(pJrnl, ))

│48742  || szJ<16

│48743  || SQLITE_OK!=(rc = read32bits(pJrnl, szJ-16, ))

│48744  || len>=nMaster

│48745  || len==0

│48746  || SQLITE_OK!=(rc = read32bits(pJrnl, szJ-12, ))

│48747  || SQLITE_OK!=(rc = sqlite3OsRead(pJrnl, aMagic, 8, szJ-8))
│
│48748  || memcmp(aMagic, aJournalMagic, 8)

│48749  || SQLITE_OK!=(rc = sqlite3OsRead(pJrnl, zMaster, len,
szJ-16-len))

if len is longer than the file size szJ, szJ-16-len on line 48749 will be a
very large number, that will then be converted to a very negative number
when passed to unixRead. This will cause the check:

 if( offsetmmapSize ){

to succeed even though pFile->mmapSize is null, leading to a crash.

I don't believe this is a security issue, because len can only be between 0
and 512 on most systems, but it can get an app that relies on SQLite stuck
in a reset loop.

A journal that causes this issue is attached.

To reproduce, copy the attached files into the same folder, and open the
database, for example:

sqlitebrowser EmailProviderBody.db

Thanks,

Natalie
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance impact of UPDATEing multiple columns vs few columns

[ghalwasi]

I am using SQLite C library in my application and I have a question regarding
updating "mutiple" columns using UPDATE statement.
Lets suppose, my Database table has 10 columns (c1, c2 ... c10). My question
is that what will be the difference (in context of CPU cycles & performance)
if i UPDATE multiple columns or only few columns.

Lets say if the requirement is to just update c2 & c3 but if we are updating
c2, c3,c4,c5,c6,c7

UPDATE db SET c2,c3,c4,c5,c6,c7 WHERE c1=x (c1 is a primary key)
or 
UPDATE db SET c2,c3 WHERE c1=x (c1 is a primary key)




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Problem on Windows 10 machines

[Bart Smissaert]

Yes, not very helpful. The message is from my VB6 wrapper as is like this:

Method ProcedureX of object _ClassX failed

ClassX is the class in the wrapper ActiveX dll that also has the procedure
that makes the call to SQLite that causes the problem, in this
case sqlite3_initialize.
ProcedureX is another procedure in that same class, but that procedure has
nil to do with the problem.
I can take that ProcedureX out and that I will get another procedure
mentioned in the error message that is again completely unrelated to the
problem.

So the whole thing is just completely puzzling and I am seriously stuck
with this.


RBS



On Thu, Sep 7, 2017 at 2:44 AM, Keith Medcalf  wrote:

>
> In Windows you get a frowny face "modern icon" (about 5 inches square) and
> "something went wrong, sorry about your luck".
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
> >Sent: Wednesday, 6 September, 2017 15:06
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Fwd: Problem on Windows 10 machines
> >
> >
> >
> >On 6 Sep 2017, at 10:03pm, Bart Smissaert 
> >wrote:
> >
> >> When my wrapper makes the call to the Sqlite dll my app crashes
> >
> >With what error ?  Segmentation fault ?  Privilege violation ?  I
> >don’t think I’ve seen any crash which doesn’t produce an error report
> >of some kind, even if we know that there’s no reason for that error
> >at that point.
> >
> >Simon.
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users