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

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

Re: [sqlite] Problem with mailing list

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:

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

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

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

> 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

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

Re: [sqlite] Problem with mailing list

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

Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0

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

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

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 0

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

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

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

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

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

> 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

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

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

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

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

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

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

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

>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

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

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:

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

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

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

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

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

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

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

> 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

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

> 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

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

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

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

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

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.

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.

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

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

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

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

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. Other "am I right in thinking this" question: INTERSECT

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

> 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

[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

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

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.

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

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

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

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

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

> 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

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

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

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

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

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

>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

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

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

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

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

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

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

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

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

Re: [sqlite] GCC and DLL

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,

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

> 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

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

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

Re: [sqlite] GCC and DLL

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

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

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

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

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

[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

[sqlite] Bug Report: Crash When Loading Short Journal

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

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

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

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

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 >

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

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

[sqlite] Proof that a line has been modified

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