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] In the case of ZIPVFS

[Michael Stephenson]

Having just glanced at the documentation for ZIPVFS (didn't even know it
existed)...

1) Perhaps using a real-time compression algorithm, something like lz4.
It's not clear to me which algorithm ZIPVFS uses by default.

2) Perhaps increasing the cache settings to cache more pages in memory.

3) Perhaps increasing the page size.

4) Perhaps ensure that WAL mode is used.

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of ???
Sent: Wednesday, December 23, 2015 4:05 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: [sqlite] In the case of ZIPVFS

HI,all
SQLite retrieves the compressed records is slower than the uncompressed
records about 30%.
How can improve the problem?  and anyone any suggustion?

best regards
wqg
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Destroy all evidence of a database

[Michael Stephenson]

Simon, if the data in the database is sensitive, could you encrypt the database 
(ala something like https://www.zetetic.net/sqlcipher/)?  

That way if the file is left around for some reason, it's much less of a 
concern.  Your app could generate a new (random) key each time it creates a new 
database.  When the program exits, the key is not saved anywhere so getting at 
the data means breaking the encryption if the file is left hanging around for 
some reason.

If sqlcipher itself is not that palatable or won't work on your mystery OS, 
it's pretty easy to write your own pager-based encryption based on the hooks 
that are provided when SQLITE_HAS_CODEC is defined. 

I did something like this.  I got started by diffing the sqlite source with the 
sqlcipher source which showed me what they had done.  I wasn't pleased that 
sqlcipher linked in the full OpenSSL library due mostly to the size (around 
500K packed) and complexity, neither of which I needed. 

It was pretty easy to just create my own encryption based on the same general 
approach as sqlcipher and used OpenSSL's AES implementation directly for the 
encryption and decryption.  Using the optimized ASM version of the OpenSSL AES 
code, all of this came in at around 10K packed (including some small libs for 
key derivation, HMAC, etc.) and the performance is almost identical to plain 
vanilla sqlite.  

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of John McKown
Sent: Wednesday, April 22, 2015 11:12 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Destroy all evidence of a database

On Wed, Apr 22, 2015 at 9:34 AM, Simon Slavin  wrote:

>
> On 22 Apr 2015, at 3:23pm, John McKown 
> wrote:
>
> > If it is
> > a POSIX compliant, perhaps what you could do is create a "temporary"
> > (mktemp) file of "appropriate" size.
>
> I had never considered that idea.  Thank you very much.  Unfortunately 
> it won't work in this situation because the people in control of the 
> system would either say "No virtual file systems" or leap at the idea 
> and insist that everyone uses virtual encrypted file systems for all 
> data files at all times.  I'm not sure which would be worse.
>
>
?Oh, my condolences. I've had that type of management too. If a teaspoon of 
medicine is good, then a tablespoon is better, but let's just take the entire 
bottle and be done with it.?


--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[sqlite] Did A Recent Windows & Update Affect SQLite???

[Michael Stephenson]

Some suggestions:

1) Check your temp folder and remove chaff.

2) Download CCleaner and check your registry integrity.

3) Review updates that have been applied and try removing them one at a time
or in a targeted fashion.  You should be able to see what updates were
applied around the time you think Lightroom went bad.  (At the start menu
type Update in the search box and select "View installed updates").

4) Have a look at Lightroom using Process Monitor and perhaps Performance
Monitor.  Maybe the process has a high number of threads, or files in use,
or handles or something like that.

4) Install Windows Performance Toolkit and try xperf or Windows Performance
Analyzer to profile Lightroom and try to see what is taking up time.

5) Turn off Avast shields for a while and see if that makes a difference.

6) Stop/Disable the Windows Search service temporarily and see if that makes
a difference.

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Mark
Romero
Sent: Wednesday, April 1, 2015 3:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Did A Recent Windows & Update Affect SQLite???

Hello everyone and thanks for your replies (I am new to using a mailing list
so I hope that everyone gets this response).

- Viruses and Antivirus software:

I use Avast in real time and I have scanned my system several times and it
came up clean.

I installed malwarebytes shortly after Lightroom began having problems, and
the scans came up clean, too. (I have since uninstalled malware bytes.)

Have not tried any of the native Microsoft virus tools.

- Software Updates:

I am using the latest version of Lightroom (5.7.1)

I am using Win 7 Pro 64-bit Service Pack 1 and I am pretty sure it is up to
date since I do automatic updates with it.

- CPU Usage:

In resource monitor and task manager, it is ONLY lightroom that is using up
all the CPU and is "spiking" the cpu (it repeatedly and rhythmically goes
from 50% use to 100% use over and over again. It spikes approximately every
5 seconds or so.

No other program or process is using an abnormally large percentage of CPU
(or memory)

Please also note that when Lightroom IS spiking the CPU, I am still able to
open other programs (like Photoshop, which is something of a resource hog),
and work in those programs, WHILE Lightroom is spiking the CPU. To rephrase
this, when lightroom stops responding I can go ahead and open photoshop and
work on a different photo, with only minimal lag compared to when Lightroom
is not spiking the CPU.

- Hard Drive (and other hardware):

I have used the Dell diagnostic tests and the Western Digital tests and they
have all been fine for the hard drive as well as ALL other hardware.

- Disk Performance / File corruption

Yes, I have seen NTFS errors in the event manager. But they only seem to
happen when Lightroom has stopped responding and I chose to do a
ctrl-alt-del instead of doing a ctrl-q and waiting for the program to
eventually close. (I know that ctrl-alt-delete is bad... will try to avoid
again!)

I have done another chkdsk and it came out fine, and I did a chkntfs and it
came out saying C: is not dirty.

- Tech Support from Adobe:

I have had THREE separate Adobe technicians login remotely to my computer
and make changes to different settings, and unfortunately it has not made a
difference.

Because the program stops responding sporadically, they will make a few
changes and then ask me to try it. If the problem doesn't happen within five
minutes, they end the chat and say, "It's probably ok, let  us know if you
have problems in the future."

Then five minutes after they end their support chat and log out of my
system, it will stop responding again.

The changes they have made include increasing the cache, giving adobe
programs "resource priority" . they also tried to update my drivers but I
don't know if they were able to update or not.

- Reinstallation:

I have deleted and reinstalled Lightroom 5.7.1 once already after the
problem started a few months back, and unfortunately it did not make a
difference.

- Lightroom versus Adobe Camera Raw (ACR)

Someone pointed out that Lightroom is different than ACR, and that is true.
However, I spend most of my time in the Development module of Lightroom,
which is (as far as I understand) extremely similar to ACR.

Just so you know, I can use ACR ALL DAY LONG without any problems.

My understanding is that Lightroom saves info to its SQlite database, while
ACR saves info to a separate file.

One Other Fun Fact:

A lot of people are dismissive of my problem because I have an older
computer (core 2 duo E8400 with 8 gigs DDR3 RAM).

"Just buy an i7" they like to chant.

However, Lightroom worked fine for over 8 months until just about two months
ago, when the problems started, and...

My sons computer has the same problem, which developed at the same time.
Again, worked fine for 8 months, 

Re: [sqlite] SQL Logic error or missing database

[Michael Stephenson]

As Dan said, the console app needs the read/write flag.  The other app appears 
to be using CStringW, but the api takes a const char *, not a wide char 
pointer.  I'd try CStringA and explicitly cast to LPCSTR.

Michael Stephenson

On Apr 5, 2013, at 11:01 AM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 04/05/2013 09:08 PM, Rob Collie wrote:
>> Yeap, I'm on Visual Studio 2012. I've created a console app:
>> 
>> 
>>  sqlite3 *oDatabase;
>>  int returnValue;
>>  returnValue = sqlite3_open_v2("file://C:/Newfolder/testing.db",
>> , SQLITE_OPEN_CREATE, NULL);
>>  if (returnValue != SQLITE_OK )
>>  {
>>   //sqlite3_close(oDatabase);
>>   return returnValue ;
>>  }
>>  int anyKey;
>>  return 0;
>> 
>> It returns 21. Checking the other project, the open actually does return 21
>> too.
> 
> This one is returning SQLITE_MISUSE because the SQLITE_OPEN_READWRITE
> flag is not being passed. It seems quite odd that the other code
> would do the same though.
> 
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About new ticket "Per page/region checksums"

[Michael Stephenson]

It's almost trivial to add per-page checksums at the page level.  Here are 
basic steps:

 

1)  Define SQLITE_HAS_CODEC when building.  This will cause 5 unresolved 
externals (functions declared but not defined/implemented).  You need to 
provide implementations of these 5 functions:

a.   sqlite3_activate_see:  can provide an empty implementation.  It is 
required for the SEE security extension, not needed here.

b.  sqlite3_key:  can provide an empty implementation.  This is used for 
encryption extensions to set the encryption key, not needed here

c.   sqlite3_rekey:  can provide an empty implementation.  This is used for 
changing the encryption key on a database, not needed here.

d.  sqlite3CodecAttach:  This is the only function of these 5 that needs 
implementing.  Two things are required:

   i.  Reserve 
space at the end of each database page for storing the checksum for that page.  
(via a call to sqlite3BtreeSetPageSize).  

 ii.  Wire our 
“codec” into the Pager via a call to sqlite3PagerSetCodec and passing a pointer 
to our “codec” function.

e.  sqlite3CodecGetKey:  can provide an empty implementation.  SQLite will 
call this to get the encryption key, not needed here.

2)  Implement a “codec function” that the Pager will call each time a page 
is read from disk or written to disk.  Perhaps we could call this function 
XCodec.  A pointer to the function is passed to SQLite when 
sqlite3PagerSetCodec is called.  The XCodec function would verify the checksum 
if the page was being read and write the checksum if the page is being written. 
 What to do if a problem was found should probably be implementation specific.

 

That’s about it, minus some detail.

 

Given the above, would per-page checksums be better served up as an extension, 
rather than written into the core database code?  Note that none of the above 
steps require changes to the SQLite amalgamation; rather the “codec” code can 
be compiled in as an independent unit and linked in.

 

Using the Pager hooks would mean that this would conflict with either SEE or a 
home-grown encryption extension, but my assumption would be that that extension 
would have its own functionality for per-page checksums or HMACs.

 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Nico Williams
Sent: Monday, February 06, 2012 4:49 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] About new ticket "Per page/region checksums"

 

On Mon, Feb 6, 2012 at 2:27 PM, Roger Binns <  
rog...@rogerbinns.com> wrote:

> On 06/02/12 11:35, Nico Williams wrote:

>> Indeed, but if you'd do integrity protection at the application layer 

>> then surely you'd have have a backup/restore strategy to deal with 

>> lower-layer corruption.

> 

> Only if you know about that corruption! It is perfectly possible for 

> stray zeroes to be written into the btrees in such a way that they are 

> still considered valid, but you have effectively amputated a row.

 

No, you can do something about that: you could store a hash of the XOR of the 
hashes of all the rows (per-table).  To check integrity simply get all the 
rows, compute the running XOR of hashes, then when you're done hash the result 
and then check that against the stored value for the whole DB.

 

(You could also rebuild indexes after checking DB integrity.)

 

>> However, if you're going to have SQLite3 do it

> 

> Note that SQLite already has a way of reserving space on a per page basis.

>  It is currently used by the encryption extension.  It is possible to 

> store a checksum there instead which would still be readable but not 

> writeable by older versions of SQLite.

 

Interesting.

 

> Since the btree pages would also have checksums things should work in 

> most scenarios.  Any solution would be better than the current no 

> solution, especially as checksums let you discover corruption early.

 

Yes.

 

>> Well, one can hope.  (I don't keep up with btrfs; perhaps it will 

>> mature and become common on Android devices.)

> 

> Approximately zero percent of SQLite databases will be on btrfs for 

> the foreseeable future, even if you and I start using btrfs.  Android 

> 3 and above do support encrypting the filesystem which effectively 

> gives you a lot of the checksumming, although filesystem 

> implementations tend to care far more about metadata than file contents.

 

Encryption is not enough.  You really need block pointers to carry the block 
checksum/hash/MAC/integrity tag.

 

> Windows 8 server will be coming with a filesystem that does integrity 

> checking.  However again approximately zero percent of SQLite 

> databases will be stored on that filesystem for the foreseeable future.

 

Right.

 

> SQLite 

Re: [sqlite] 3.7.9 amalgamation file in VS2005

[Michael Stephenson]

The Visual Studio debugger has a limitation of 64K lines in a source file for 
code stepping.  Well, actually the debugger is fine, it's a limitation of 
Visual Studio itself that has been reported to Microsoft (maybe a decade ago) 
but never fixed.

WinDbg is almost as good.

Another solution is to compile at least a debug version from the 
non-amalgamated, preprocessed source (assuming that is still available) by 
adding all of the files to a VS project and removing a few files from the 
project.

Another possible solution might be to break up the amalgamation into smaller 
files, renaming all but the last piece as .h, having each piece 
include the previous piece.  I haven't tried this, but it might work.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jens Frederich
Sent: Friday, December 30, 2011 1:03 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] 3.7.9 amalgamation file in VS2005

IntelliSence on/off doesn't solve the stepping problem. Maybe it's a MSVC 
problem. My pracmatic solution is take WinDbg. 
 
Jens

Am 29.12.2011 um 15:17 schrieb "Black, Michael (IS)" :

> What happens if you disable IntelliSense?
> 
> 
> 
> Tools/Options/Text Editor/C++/Advanced -- IntelliSense/Disable 
> IntelliSense=True
> 
> 
> 
> Michael D. Black
> 
> Senior Scientist
> 
> Advanced Analytics Directorate
> 
> Advanced GEOINT Solutions Operating Unit
> 
> Northrop Grumman Information Systems
> 
> 
> From: sqlite-users-boun...@sqlite.org 
> [sqlite-users-boun...@sqlite.org] on behalf of Jens Frederich 
> [jfreder...@gmail.com]
> Sent: Thursday, December 29, 2011 12:47 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] 3.7.9 amalgamation file in VS2005
> 
> Hi,
> 
> I duplicated your problem on C++ 2011 Professional. Source code 
> stepping doesn't work.
> My solution is to remove all comment lines.
> 
> Jens
> 
> 
> On Wed, Dec 28, 2011 at 2:09 PM, Teg  wrote:
> 
>> 
>> Yeah,  I  was  wrong. Color syntax highlighting does work. It's 
>> single stepping into the code that doesn't. Sorry.
>> 
>> C
>> 
>> 
>> Wednesday, December 28, 2011, 3:04:39 AM, you wrote:
>> 
>> AN> Dear Michael,
>> AN>
>> AN> thanks for trying this out. Not at the moment but I will consider
>> upgrading for the future.
>> AN>
>> AN> In fact I was surprised about this problem because amalgamation 
>> AN> file version 3.7.7.1 still *has* correct highlighting in both
>> AN> VS2005 and 2008. The 3.7.9 amalgamation fle is, of course, 
>> AN> larger, but the difference does not seem to be so big so that the 
>> AN> highlighting should fail. So maybe there might be some syntax 
>> AN> elements in 3.7.9 (large comments or whatever) causing this 
>> AN> behaviour or there is really a maximum source file size that
>> AN> VS2005 and 2008 syntax highlighter can support, which was reached
>> between 3.7.7.1 and 3.7.9.
>> AN>
>> AN> Thanks
>> AN>
>> AN> Alex
>> AN> __
 Od: "Black, Michael (IS)"
 Komu: General Discussion of SQLite Database
 Datum: 27.12.2011 23:36
 Předmět: Re: [sqlite] 3.7.9 amalgamation file in VS2005
 
>> AN> I duplicated your problem on C++ 2005 Express and C++ 2008 Express.
>> 
>> 
>> 
>> AN> C++ 2010 Express does the syntax highlighting correctly (or at 
>> AN> C++ least a
>> lot better).
>> 
>> 
>> 
>> AN> Can you upgrade?
>> 
>> 
>> 
>> AN> Michael D. Black
>> 
>> AN> Senior Scientist
>> 
>> AN> Advanced Analytics Directorate
>> 
>> AN> Advanced GEOINT Solutions Operating Unit
>> 
>> AN> Northrop Grumman Information Systems
>> 
>> AN> 
>> AN> From: sqlite-users-boun...@sqlite.org 
>> AN> [sqlite-users-boun...@sqlite.org] on behalf of Alexandr Němec [
>> a.ne...@atlas.cz]
>> AN> Sent: Tuesday, December 27, 2011 8:55 AM
>> AN> To: sqlite-users@sqlite.org
>> AN> Subject: EXT :[sqlite] 3.7.9 amalgamation file in VS2005
>> 
>> 
>> AN> Dear all,
>> 
>> AN> I have one question that is not strictly a SQLite question 
>> AN> (sorry), but maybe someone encountered this problem and found the 
>> AN> solution. I have upgraded from an older SQLite release to 3.7.9 
>> AN> and loaded the amalgamation file into a Visual Studio 2005 
>> AN> project. But the syntax code highlighter does not behave 
>> AN> correctly with the 3.7.9 amalgamation file because it greys out 
>> AN> not only the sections of code that are not compiled at all 
>> AN> (because of
>> AN> if(n)def's) but also other sections that DO compile. Well, I 
>> AN> think that this is a bug of the VS 2005 syntax highlighter for 
>> AN> such a large source file, because the file compiles ok, but 
>> AN> working with such a file in VS 2005 is frustrating.
>> 
>> AN> Did anyone see (did anyone find a solution for) this problem? I 
>> AN> have not seen this for older versions of the amalgamation file.
>> 

Re: [sqlite] library routine called out of sequence

[Michael Stephenson]

Assuming that you are calling reset after each use of a prepared statement
and/or that the error occurs on the very first bind...

This sounds like in the working case the statements are prepared, bound, and
then reset by the same thread each time these steps are done, but perhaps in
the non-working case the statements are prepared in one thread and then
bound in different threads (perhaps even on the first call to bind).  I
think that might be your issue.

Which version of SQLite are you using, and what OS?  Is thread support
compiled in to your SQLite instance?

Also, could two threads be attempting to bind the same statement at the same
time?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP
Sent: Friday, December 23, 2011 9:06 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] library routine called out of sequence

Hi,

yes, I know. I have a multithreaded app. One db conection.I will try to
reproduce the problem with a simple app.

However, the difference between the working and non working case is that in
the working case statements are prepared each time. In the non working case,
its reused.

The very first call to bind throws this error.

Sreekumar
On Dec 23, 2011 2:51 PM, "Pavel Ivanov"  wrote:
>
> Devil is in the details. What you described in general is okay. But I 
> guess the exact code you have has some problems. To find them we 
> should see the code.
>
> An just some first check: do you have a single-threaded application 
> accessing database?
>
>
> Pavel
>
>
> On Fri, Dec 23, 2011 at 3:32 AM, Sreekumar TP 
wrote:
> > Hi ,
> >
> > I have a strange problem. I do the following in sequence -
> >
> > 1. open database
> > 2. set pragmas
> > 3.create table
> > 4. prepare a paremetrised statement ( SELECT ..) 5. bind values to 
> > the statement 6. step
> >
> > At 5, I get the error  "library routine called out of sequence".
> >
> > If I replace the parameterised statement  with a 'non-parameterised'
> > statement, I have no issues.
> >
> > If I use a parameterized INSERT statement , I do not get the error.
> >
> > Any suggestions?
> >
> > -Sreekumar
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] SQLite reading old data

[Michael Stephenson]

I think the only solutions would be:  1) SQLIte changes so that it does not
use a direct file read/write for the change counter.  2) Have the page-level
encryptor specifically not encrypt certain parts of the database header,
such as the change counter, when saving page 1 to disk.

I imagine that the direct reads/writes are to help ensure consistency and
recovery in the face of an application crash. 

You might try turning on WAL and see what happens.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Fabrizio Steiner
Sent: Thursday, December 15, 2011 3:00 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite reading old data

The answers to your questions :
1)Yes I'm still using System.Data.SQLite but without any key.
2)No I don't use WAL.
3)Running only one thread didn't bring up the problem.
4)Didn't have any time yet.
5)Standard System.Data.SQLite with RC4 algorithm impleemnted there.

I've taken a look into the the caching of SQLite during the afternoon
because I suspected some issues with caching. And I found a problem with the
change counter exactly as you thought.

I took a look into the source code of the pager and the pager cache. I've
found the pager cache decides if the cache needs to be flushed whenever a
shared lock gets requested. In order to determine if a flush is needed or
not, the change counter of the db file is compared to the stored one in the
pager structure. The change counter is directly retrieved from the
underlying OS (sqlite3OsRead), so if the database is encrypted, the
encrypted bytes will be retrieved here (Sytem.Data.SQLite encrypts the
entire database, including page 1 and the SQLite header).

So dbFileVers of the pager structure is always the encrypted value. I
started checking if there was a situation where it was used as the plaintext
value.

In the function pager_write_changecounter the change counter will get
incremented, but the encrypted value pPg->pPager->dbFileVer is used. After
incrementation the value will be put back into the page buffer. During the
write of the page 1, the is again encrypted (see, pager_write_pagelist).
After the page has been written the dbFileVers gets updated
(pager_write_pagelist Line 4049) with the encrypted value.

So at least for incrementing the change counter the plaintext value should
be used.

When RC4 with the full database encryption (as implemented in
System.Data.SQLite) is used the following happens.
RC4 basically XORs a plaintext byte with a pseudo random byte. Let's assume
the following change counter values, for simplicity consider only the last 4
bits of it. As we've seen the encrypted change counter is incremented,
encrypted and stored back into the file. Let's consider the following
operations.

DB Initial State
- Encrypted DB File counter:  X   Y   Z
0(The bits X, Y, Z are unknown, but the last bit is 0.)

First update of Database:
- Update DB, Increment counter:   X   Y   Z
1(Adding one to the encrypted counter.)
- New encrypted Value in DB: (X XOR K1)  (Y XOR K2)  (Z
XOR K3) (1 XOR 1) = 0(Assuming the LSB of the pseudo byte is 1, the
probability is 1/2 for this if its purely random.)

Let's update the database again:
- Update DB, Increment counter:  (X XOR K1)  (Y XOR K2)  (Z
XOR K3) 1(Adding one to the encrypted counter.)
- New encrypted Value in DB:((X XOR K1) XOR K1) ((Y XOR K2) XOR K2) ((Z
XOR K3) XOR K3) 0(The same pseudo byte is again used for encryption.)
   =  X   Y   Z
0

As a result after the second db update the encrypted change counter is the
same as before the updates occured. dbFileVers contains 12 more bytes but
these represent the "db size in pages", the "page number of the first free
page" and the "number of free pages". But these may be unchanged if no new
pages were needed and there was no free page.

=> Therefore theres a good chance that a db change is undetected depending
on the encryption algorithm.

Is it allowed to encrypt the entire databse especially the header with the
pagesize, change counter and so on?
   - If yes, then SQLite should make sure all data get's decrypted prior
using these values.
   - If not, shouldn't SQLite make sure the crypt api never sees the header?

I've found no documentation about implementing the crypting api.

Kind Regards
Fabrizio

> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- 
> boun...@sqlite.org] Im Auftrag von Michael Stephenson
> Gesendet: Donnerstag, 15. Dezember 2011 20:40
> An: 'General Discussion of SQLite Database'
> Betreff: Re: [sqlite] SQLite reading old data
>
> A couple more questions:
>
> 1)  Does "without encryption" mean still using Sys

Re: [sqlite] SQLite reading old data

[Michael Stephenson]

A couple more questions:

1)  Does "without encryption" mean still using System.Data.SQLite, just
without using a key?
2)  Are you using WAL?
3)  Do you also see the problem with a single process with two connections?
4)  Any chance you could provide a small demo project that demonstrates the
problem?
5)  Are you using standard System.Data.SQLite, or the SqlCipher version?
(http://sqlcipher.net)

I'm pretty fuzzy on this, but here are some (possibly way off base)
thoughts...

The way I understand things, when you do an update inside of a transaction,
the new data is written to the log until it is committed, and then a
rollback involves discarding the log page with the changes.  This means you
expect to see the new data in your "session" but no one else should see the
new data until you commit.  

The question of which version of the data a session sees is, I think, tied
to some global counter-type metadata that tracks changes.  In Oracle, this
would be perhaps a system change number that would determine which undo
blocks your session would see versus other sessions.  I think SQLite has
something similar, and I think this is stored in the database header on the
first database page.  

I also recall that there are a few places in SQLite where it reads data for
page 1 using system-level I/O rather than the pager (e.g., direct reads on
the database file), and this causes issues for page-level encryptors because
they don't get an opportunity to decrypt the encrypted page before SQLite
reads that piece of data, so they typically have to hard-code some value at
that data location or hope that SQLite ignores what would be an obviously
invalid value.  I think the change counter is one of these items.

Maybe folks with much more understanding could make sense of this, but my
thought is that the equivalent of the "system change number" is being mucked
up by the page-level encryptor, causing SQLite to get the old data because
as far as it knows that's the data at the given (invalid) change count.  

Well, something like that.  Again, probably way off base, but I had a couple
of minutes and thought I'd throw it out there...

~Mike

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Fabrizio Steiner
Sent: Thursday, December 15, 2011 9:01 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite reading old data

I think the following observation, which I made in the meantime, may be very
interesting. Which also gives a hint to a problem with SQLite in combination
with the encryption of System.Data.SQLite.
 
I tried the same test with an unencrypted database, and surprisingly the 3
threads do their work without any exception. I executed several test runs
with the 3 threads and it wasn't reproducable anymore. 
Activating the encryption again it's reproducable and exceptions occur due
to an old stamp that is being retrieved. 

Answers to your questions:
- No connections are shared at all between threads. So every thread creates
2 private connections. 
- Updating always occurs in the second connection.
- The after-update checks will be perfomed in the same transaction as the
update itself. 
- No thread ever deletes a row.

Regards
Fabrizio

> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- 
> boun...@sqlite.org] Im Auftrag von Igor Tandetnik
> Gesendet: Donnerstag, 15. Dezember 2011 14:26
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] SQLite reading old data
> 
> Fabrizio Steiner  wrote:
> > I'm currently facing a problem with the System.Data.SQLite ADO.NET 
> > Provider. I'm using stamps (simple integer column) on the rows in 
> > order to detect if another user has altered the same datarow during 
> > the
> time the first user started the edit and when he invoked the save. 
> Every update increases this stamp and the update statement is 
> constrained with the old stamp. If the update has
> > no affected rows, there are different cases which are checked:
> > - If the datarow has been deleted, the user is informed about this.
> > - If the datarow still exists the current stamp in the database is
retrieved.
> >   - If the db stamp is greater than the reference stamp, the user 
> > will be informed that another user changed the data in the meantime.
> >   - If the db stamp is smaller or equal to the reference stamp, an 
> > exception
> is thrown because the stamp can never get smaller.
> >
> > I tried to simulate 3 users which are updating a datarow in the same 
> > table, every user changes it's own row.  Basically I had 3 threads 
> > each of one first retrieving a row in one connection. Updating the 
> > data and
> storing it back into the table with another connection. Every thread 
> made a loop with several edits and a sleep in between.
> 
> Do you have two connections, each shared by three threads; or six 
> separate connections? Do you ever perform any writing 

Re: [sqlite] How to : encrypt the data of database table.

[Michael Stephenson]

Have a look at:

http://sqlcipher.net

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ashokkumar Gupta
Sent: Wednesday, September 14, 2011 1:53 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to : encrypt the data of database table.

Hi All,

Does sqlite has encryption feature or not. If yes, how to do it and if No,
then is there any work around by which we can have that feature ??

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

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

Re: [sqlite] help to get the value of file change counter

[Michael Stephenson]

Just a guess, but you may have to use one of the Pager functions to examine
the buffer for database page 1, which is always pinned in memory and which
represents the first  bytes of the database file.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Akash Agrawal
Sent: Tuesday, September 13, 2011 9:08 AM
To: General Discussion of SQLite Database; Simon Slavin
Subject: Re: [sqlite] help to get the value of file change counter

hii

thank you for suggestion.
but when reading the value from sqlite header it printing same value no
change i can see i pasting the c++ code below

/CODE***
*/
#include 
#include 
using namespace std;

int main ()
{

  FILE * pFile;
  int c;

  int n = 0;
  char fcc[35] = {0};
  int i = 0;
  int j =0;
  while(j < 4)
  {
  int n = 0;
  sleep(10);  // sleep is used so that i can make any change in
database so that value of counter will change

  pFile=fopen ("demo.sqlite","r");   //demo.sqlite is my sqlite  file

  if (pFile==NULL)
  {
  perror ("Error opening file");
  exit(-1);
  }
  else
  {
  do
  {
  c = fgetc (pFile);
  n++;

  if(n >23 && n < 28)
  {
  printf("%x",c);// printing the value of header whic in
hex format
  i++;
  }
  }while (c != EOF);
  fclose (pFile);
  cout< wrote:

>
> On 12 Sep 2011, at 1:35pm, Richard Hipp wrote:
>
> > On Mon, Sep 12, 2011 at 8:02 AM, Igor Tandetnik 
> > 
> wrote:
> >
> >> Simon Slavin  wrote:
> >>> 
> >>>
> >>> int sqlite3_total_changes(sqlite3*);
> >>>
> >>> My understanding (which might be wrong) is that this count 
> >>> includes all changes made by all connections to that database: not 
> >>> only changes made using your connection but also chances made by
> another
> >>> computer, process, or thread.
> >>
> >> I'm 99% sure your understanding is wrong [snip]
> >
> > Igor is right.  The sqlite3_total_changes() function only reports 
> > the
> number
> > of rows that have been changed by the same database connection that
> issued
> > the sqlite3_total_changes() call.
>
> Okay,  I find that page in the documentation ambiguous then.  Could 
> that sentence, or something like it, be added for clarity ?
>
> > There is no API for accessing the database change counter.  But you 
> > can
> read
> > it yourself by looking at bytes 24-27 of the database file.
>
> How hard would it be to implement this safely using sqlite3's own 
> filehandle to the database file ?  I know we're meant to treat 
> sqlite3* as a black box but could it be done relatively safely, with a 
> warning that it might fail under certain weird conditions ?  Would one 
> use
> sqlite3_file_control() ?
>
> Alternatively, suppose one was doing a lot of in-memory caching for a 
> SQLite database but didn't want to block other apps from accessing it.  
> Is there a clean way to say "Has anyone been messing with this but me ?" ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

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

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

Re: [sqlite] Sqlite+ICU library and usage of LIKE

[Michael Stephenson]

"It's rather pointless to use a LIKE operator with no wildcards."

Except that it performs a case-insensitive comparison, so  a quick-and-dirty 
way to accomplish that.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Tuesday, September 06, 2011 11:52 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Sqlite+ICU library and usage of LIKE

On 9/6/2011 11:41 AM, Sreekumar TP wrote:
> If I modify the statement to return all strings which match 'м' , No 
> strings are fetched.
>
>   zSQL = sqlite3_snprintf(1024,temp2,"SELECT * FROM l1 WHERE data  LIKE 'м'
> ;");

You probably want LIKE 'м%'. It's rather pointless to use a LIKE operator with 
no wildcards.
--
Igor Tandetnik

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

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

Re: [sqlite] Totalview Debugger & MemoryScape showing leak in my SQLite code?

[Michael Stephenson]

Well, in the first code example, yes you need to free your peekText pointer.
Since you passed SQLITE_STATIC for the destructor parameter to
sqlite3_bind_text, you should free it before you exit the function and not
before.  If you use SQLITE_TRANSIENT, you can free it immediately after the
bind call because SQLite will make its own copy.  It's a leak because you
allocated memory on the heap (the memory pointed at by peekText), and never
freed the memory (the pointer itself is destroyed when the stack frame is
reclaimed).

Just glanced briefly at the second code sample.  Where is the "handle"
variable declared?


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Lynton Grice
Sent: Tuesday, September 06, 2011 1:52 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Totalview Debugger & MemoryScape showing leak in my SQLite
code?

  Hi there,

I am a huge fan of SQLIte and have recently done some code that I am
debugging with TotalView and MemoryScape (http://www.roguewave.com) - VERY
VERY nice debugger, I have used GDB as well alot but Totalview is simply
awesome

NOTE: C coding is NOT my day job ;-)

I am using MemoryScape (I suppose the same as Valgrind) to detect Memory
leaks in my code.when I look at the leak detection I can see I have some
small leaks in my SQLIte codeand would love someone to tell me how I can
fix them?

For example is moans about the following 2 lines below:

- idx = sqlite3_bind_parameter_index( stmt, ":tid" );
- rc = sqlite3_step(stmt);

Here is more of the code context

int queue_peekByTID(const char *tid, message *msg){
 char *peekText = "SELECT * FROM queue WHERE tid = :tid;";
 const char *value = NULL;
 int idx;
 int len;

 sqlite3_prepare_v2(handle,peekText,-1,,0 );

 idx = sqlite3_bind_parameter_index( stmt, ":tid" );
 sqlite3_bind_text( stmt, idx, tid, -1, SQLITE_STATIC );

rc = sqlite3_step(stmt);
 if(rc == SQLITE_ROW){
 
 
}

What is wrong with my code above? Must I FREE the char*? Why would something
say it was a "leak"?

I am also getting it complaining when I do a "sqlite3_finalize(stmt);"

I another piece of code I am using SQLite to log certain events for
meand complains about the following 3 lines below:

- rc = sqlite3_open_v2(eventLogName,, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_SHAREDCACHE | SQLITE_OPEN_CREATE, NULL);
- rc = sqlite3_exec(handle,journalMode,0,0,0);
- rc = sqlite3_exec(handle,trigger,0,0,0);

Here is the code context

int eventLogOpen(char *eventLogName, unsigned int eventLogRetentionPeriod){
 char *eventLogTable = "CREATE TABLE IF NOT EXISTS [log] ( "
  "[idx] INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT, "
  "[timestamp] CHAR(25), "
  "[timestamp_secs] CHAR(10), "
  "[event_cat] CHAR(10), "
  "[event_tid] CHAR(50), "
  "[event_bus_ref] CHAR(50), "
  "[event_msg] TEXT);";

 char trigger[2024];

 if(eventLogRetentionPeriod > 0){
 sprintf(trigger, "DROP TRIGGER IF EXISTS [log_retention]; "
  "CREATE TRIGGER [log_retention] "
   "AFTER INSERT ON log "
   "FOR EACH ROW BEGIN "
 "DELETE FROM log "
 "WHERE timestamp_secs < (strftime('%%s',
'now') - %i); "
   "END;", eventLogRetentionPeriod);
 }

 char *journalMode = "PRAGMA journal_mode=wal;";
 int successFlag = ERROR;

rc = sqlite3_open_v2(eventLogName,, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_SHAREDCACHE | SQLITE_OPEN_CREATE, NULL);
 if (rc == OK){
 successFlag = OK;
 p = sqlite3_malloc(256);
 sqlite3_busy_handler(handle, , p);
 sqlite3_free(p);
 sqlite3_exec(handle,"PRAGMA default_cache_size = 50;",0,0,0); rc =
sqlite3_exec(handle,journalMode,0,0,0);
 if(rc == OK){
   rc = sqlite3_exec(handle,eventLogTable,0,0,0);
   if(rc == OK){
rc = sqlite3_exec(handle,trigger,0,0,0);
   }
 }
 }
 return successFlag;
}

Is there anything I can do to prevent these "leaks"? Maybe I need to clean
up using some other SQLite functions I am not aware of etc?

Thanks for the help ;-)

Lynton








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

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

Re: [sqlite] Custom Collation comparing only firt character?

[Michael Stephenson]

Most likely, since you say only the first character is being compared, is
that you have an ANSI/Unicode issue.  Specifically, it sounds like a Unicode
string is being passed to your collation function, which is expecting ANSI
and then finding a 0 at the second byte is determining that that is the end
of the string, thus exiting after the first character.

I have a collation function (Windows, code page 1252) that sounds like what
you are doing.  It was actually a bit complex to get it right (I think it's
right).  If you're interested, I could post it.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roberto Colnaghi
Sent: Thursday, August 25, 2011 1:59 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Custom Collation comparing only firt character?











Hi,


I'm using iOS SQLite with a custom collation.


I've registered it:
sqlite3_create_collation(sqlDatabase, 
"anyCIAI", 
SQLITE_UTF16, 
nil,
collationAnyCIAI);


And it is used like this:
"select * from Team where Name = 'SOMETHING' COLLATE anyCIAI;"


It works though only the FIRST character seems to be compared instead of the
whole "Name".
Is there anything missing here?


The collation method should compare a á à ã... and so on as equal.

Thank you!










int collationAnyCIAI(void *arg1, int str1Length, const void *str1, int
str2Length, const void *str2) {
NSString *strA = [NSString hexStringWithData:str1 ofLength:1];
NSString *strB = [NSString hexStringWithData:str2 ofLength:1];
int striA;
sscanf([strA cString], "%x", );
int striB;
sscanf([strB cString], "%x", );


//convert to accentless
//aA with accent to capital A
if( (striA >= 192 && striA <= 197) || (striA >= 224 && striA <= 229)
){
striA = 65;
}
//çÇ to C
if( striA == 199 || striA == 231 ){
striA = 67;
}
//eE with accent to capital E
if( (striA >= 200 && striA <= 203) || (striA >= 232 && striA <= 235)
){
striA = 69;
}
//iI with accent to capital I
if( (striA >= 204 && striA <= 207) || (striA >= 236 && striA <= 239)
){
striA = 73;
}
//oO with accent to capital O
if( (striA >= 210 && striA <= 214) || (striA >= 242 && striA <= 246)
){
striA = 79;
}
//uU with accent to capital U
if( (striA >= 217 && striA <= 220) || (striA >= 249 && striA <= 252)
){
striA = 85;
}
//a-z to A-Z
if( striA >= 97 && striA <= 122 ){
striA -= 32;
}


//convert to accentless
//aA with accent to capital A
if( (striB >= 192 && striB <= 197) || (striB >= 224 && striB <= 229)
){
striB = 65;
}
//çÇ to C
if( striB == 199 || striB == 231 ){
striB = 67;
}
//eE with accent to capital E
if( (striB >= 200 && striB <= 203) || (striB >= 232 && striB <= 235)
){
striB = 69;
}
//iI with accent to capital I
if( (striB >= 204 && striB <= 207) || (striB >= 236 && striB <= 239)
){
striB = 73;
}
//oO with accent to capital O
if( (striB >= 210 && striB <= 214) || (striB >= 242 && striB <= 246)
){
striB = 79;
}
//uU with accent to capital U
if( (striB >= 217 && striB <= 220) || (striB >= 249 && striB <= 252)
){
striB = 85;
}
//a-z to A-Z
if( striB >= 97 && striB <= 122 ){
striB -= 32;
}

int result = striA - striB;


return result;
} 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Split Function for SQLite?

[Michael Stephenson]

If your programming language is C or C++, it's trivial to register a
function with SQLite at runtime that can then be used in your queries.  This
does not require hacking SQLite itself.

As noted, you can't return multiple rows per database row via a function.
That's not how SQL works; it is "set" oriented.

What you could do is write a function to extract the Nth item in a delimited
string, passing the column name and the index of the item you want to
extract, and then you could split out or coalesced string column into
multiple columns like this for example:

select c1, extract(c2,0) as c2_0, extract(c2,1) as c2_1
from mytable

I can imagine a technique that you could possibly use to generate multiple
rows that would have an extract function that keeps internal state and
increments the index of the item to be extracted in a circular way (e.g., 0,
1, 2, 0, 1, 2) and then use a self join on the table to create a cross join
where you get a new row for each combination of c1 and the extracted
component of c2.  But, this is imaginary only and quite a hack and it would
definitely be better to handle this in your program directly.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Wednesday, August 24, 2011 11:09 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Split Function for SQLite?

> Did you see the code in the link I provided?  It's a function that can be
added to SQL.

Note, it's not SQL. SQL doesn't support "adding functions". SQL is all about
SELECT/INSERT/UPDATE/DELETE + DDL commands (like CREATE TABLE).
That's it. So the link you provided is a function that can be added to T-SQL
which is an internal programming language of MS SQL Server.

SQLite doesn't have its own internal programming language, so it doesn't
support writing stored procedures and functions like the example in the
link. And although SQLite allows to add functions written in C those
functions can't return such datatype as "table". So behavior you want is
impossible to reproduce with SQLite and you have to implement it in your
programming language.
And just to prevent speculations about this: lacking of own programming
language is not an issue for SQLite. Having such language won't have any
benefit because SQLite works differently than any client-server DBMS. Doing
what you want in your programming language will always work faster and is
more convenient to implement.


Pavel


On Wed, Aug 24, 2011 at 10:47 PM, Gregory Moore 
wrote:
> I don't know know whether it's a SQLite "issue" or not.  Did you see the
code in the link I provided?  It's a function that can be added to SQL. I
just need to find out whether it can be added to SQLite and if so, then how
to add it.  I'd like to know before I try it whether I would run the risk of
messing up my SQLite "installation."   Thanks!
>
> On Aug 24, 2011, at 9:35 PM, J.M. Royalty wrote:
>
>> On 8/24/2011 9:05 PM, Gregory Moore wrote:
>>> I'm thinking it's parameters would be the name of a column and a
character to designate where the string wold be separated.  It would return
multiple rows and each row would contain a piece of the string.
>>>
>>> In other word, take a table like this:
>>>
>>> c1 c2
>>> ---
>>> 1  a; b; c
>>>
>>> run a statement like this:
>>>
>>> select c1, split(c2, ';')from t1;
>>>
>>> and get this:
>>>
>>> c1 c2
>>> ---
>>> 1   a
>>> 1   b
>>> 1   c
>>>
>>> Maybe SQLite can do it or not, i don't know.  I've searched using Google
and found code for a few different implementations but they are for other
dialects of SQL.  Here's a link to one such function i found:
>>>
>>> http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-
>>> break-comma-separated-strings-into-table.aspx
>>>
>>> Would that work with SQLite?
>>>
>>> On Aug 24, 2011, at 8:40 PM, Igor Tandetnik wrote:
>>>
 On 8/24/2011 9:36 PM, Gregory Moore wrote:
> Thanks for answering!  Can this not be added as a function?
 What parameters would such a function take, and more interestingly, 
 what would its return value be?
 --
 Igor Tandetnik


 Gregory Moore
 thewatchful...@gmail.com

>> lurker here, but felt compelled, and my apologies in advance, but 
>>
>> Isn't this more a function of whatever language you are using and not 
>> a SQLite issue?
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> Gregory Moore
> thewatchful...@gmail.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Use of VACUUM

[Michael Stephenson]

If you use INTEGER PRIMARY KEY, that column becomes your rowids; this does
not create a new, separate column in addition to the rowid column.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Abhinav Upadhyay
Sent: Thursday, August 11, 2011 2:06 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Use of VACUUM

On Thu, Aug 11, 2011 at 11:15 PM, Simon Slavin  wrote:
>
> On 11 Aug 2011, at 6:35pm, Abhinav Upadhyay wrote:
>
>> The documentation page of the VACUUM command says that "The VACUUM 
>> command may change the ROWIDs of entries in any tables that do not 
>> have an explicit INTEGER PRIMARY KEY." So what are the possible cases 
>> in which the ROWIDs might change ?
>
> Not documented.  So even if someone told you what they were in this
version of SQLite, there might be other reasons in the next version.
 Theoretically it might renumber rows to close up gaps in the AUTOINCREMENT.
That makes sense.

> As it says, to stop it all you need to do is declare one of the columns as
INTEGER PRIMARY KEY.  Once you do that it assumes that you might be
referring to those values elsewhere and won't change them.
Indeed, I am using the ROWID as a reference in another table. Actually I
have an FTS table and I don't really want to create an explicit column for
storing  the IDs, as I am afraid that matches from the ID column could
affect the quality of search results.
But if this is the only option, then I guess I need to give it a try.
I might give this column a weight of 0.0 so that it doesn't create noise in
the search results.

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

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

Re: [sqlite] Minimize sqlite3.o size

[Michael Stephenson]

Add #include  or #include "SomeHeader.h"  to the top of the
amalgamation, then define them in SomeHeader.h?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Baruch Burstein
Sent: Tuesday, July 12, 2011 10:48 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Minimize sqlite3.o size

These do not work with the amalgamation. How do I use these?

On Tue, Jul 12, 2011 at 5:44 PM, Richard Hipp  wrote:

> Here are some additional options you might try to minimize the SQLite 
> size in Fossil:
>
> SQLITE_OMIT_ANALYZE
> SQLITE_OMIT_AUTOMATIC_INDEX
> SQLITE_OMIT_AUTOVACUUM
> SQLITE_OMIT_BUILTIN_TEST
> SQLITE_OMIT_DEPRECATED
> SQLITE_OMIT_GET_TABLE
> SQLITE_OMIT_INCRBLOB
> SQLITE_OMIT_SHARED_CACHE
> SQLITE_OMIT_UTF16
> SQLITE_OMIT_VIRTUALTABLE
>
> No guarantees that Fossil will run, or even compile, with the above.  
> But if you experiment you can probably find a subset of the above that 
> will work for you.
>
>
>
> On Tue, Jul 12, 2011 at 9:36 AM, Baruch Burstein  >wrote:
>
> > Yes I know (although I only got a 200k difference), but -O3 is about
> twice
> > as fast in my tests.
> >
> >
> > On Tue, Jul 12, 2011 at 4:22 PM, Stephan Beal 
> > 
> > wrote:
> >
> > > On Tue, Jul 12, 2011 at 3:10 PM, Baruch Burstein 
> > >  > > >wrote:
> > >
> > > > I assume this is the same for sqlite3.dll or sqlite3.lib, but if 
> > > > not,
> I
> > > am
> > > > interested in sqlite3.o (mingw32).
> > > > How can I minimize the size of the library (compiling with -O3, 
> > > > since
> > > speed
> > > > is my top concern, so different optimizations is not an option)? 
> > > > If I
> > > know
> > > >
> > >
> > > Coincidentally, i just tried -O3 and the end result was almost 
> > > 0.5MB
> > larger
> > > than with -g -Os.
> > >
> > > --
> > > - stephan beal
> > > http://wanderinghorse.net/home/stephan/
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Insert not working for sqlite3

[Michael Stephenson]

What if you put a semicolon at the end of the query text?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James_21th
Sent: Thursday, July 07, 2011 3:32 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Insert not working for sqlite3

Yes, 100% sure, because I only have one copy on my PC, and the script which
can display the db's data also on the same directory. 
 
I also tried below, but no error captured, just nothing happened.
 
try {
$result=$dbh->exec("INSERT INTO tbl1(one,two) VALUES ('new1','new2')"); }
catch(PDOExecption $e) { print "Error!: " . $e->getMessage() . ""; }




- Original Message 
From: Stephan Beal 
To: General Discussion of SQLite Database 
Sent: Thu, 7 July, 2011 2:27:34 PM
Subject: Re: [sqlite] Insert not working for sqlite3

On Thu, Jul 7, 2011 at 8:24 AM, James_21th  wrote:

> The table already created with some data inside, the existing data can 
> be display use "select * from tbl1".


Are you 100% sure that the test.db being opened here is the same test.db
being used in the rest of he code (same directory)?

--
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Insert not working for sqlite3

[Michael Stephenson]

I don't see something like a "create table tbl1(one, two);" statement.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James_21th
Sent: Thursday, July 07, 2011 1:52 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Insert not working for sqlite3

Dear all,
 
I'm able to connect to sqlite3 DB and display the data inside, but when
insert data, no matter how to try, just don't work, there's no error, but no
data inserted.
 
Below is the simple PDO SQL I'm using, table name is "tbl1", the two fields
name is just "one" & "two":
 
$dbh = new PDO('sqlite:test.db');
$result=$dbh->query("INSERT INTO tbl1(one,two) VALUES ('new1','new2')");
 
 
Any feed back will be greately appreciated!
 
Regards!
James
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Minimal SQLite

[Michael Stephenson]

You might want to check out various compilation options at:

http://www.sqlite.org/compile.html

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, July 04, 2011 7:28 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Minimal SQLite


On 4 Jul 2011, at 12:07pm, trilok nuwal wrote:

> I want to use SQLite for one of the application which runs on a 
> embedded system where we have memory limitations.
> 
> In the SQLite we have around 180+ distinct APIs, but not all APIs we 
> are going to use it.
> 
> What I want  is the just core APIs. Can I build SQLite in such a way 
> that I can avoid all the unnecessary API implement ions in the final 
> library. I want to avoid all the extra APIs except the core API.

SQLite is distributed as source code.  It's available in two forms: either
lots of individual files each with their own subject area, or as one big
'amalgamation' C file.  You can download both from

http://www.sqlite.org/download.html

Pick one form.  Download it.  Compile it and check that it compiles in your
compiler and runs as supplied.

Remove everything you don't think you need.  Compile as suits you.  Fix
dependencies and try again.

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

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

Re: [sqlite] How to recover the SQLITE_CORRUPT error in C programming ?

[Michael Stephenson]

Perhaps you could look at the code in shell.c and see how it does the .dump,
then copy paste or something similar into your own app.  I'm not sure if
there might be issues with functions declared SQLITE_API vs SQLITE_INTERNAL,
which may require some additional working around, but hopefully not.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, June 27, 2011 8:06 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How to recover the SQLITE_CORRUPT error in C
programming ?


On 27 Jun 2011, at 9:15am, baiydavid wrote:

>when an SQLITE_CORRUPT error is returned, we can use ".dump" command to
exports the data into a sql script, and then exec this sql in a new
database,
>But how to dump the data into a sql script with C programming
Interface? Is there any other better method to solve this problem?

Sorry, David, but there is no built-in way to dump a SQLite database as SQL
commands.

If you need to worry about this, this suggests you are getting corrupt
databases very often.  This should not happen: SQLite should not be
corrupting its databases.  Perhaps you could investigate this problem with
your setup.

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

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

Re: [sqlite] Critical issue

[Michael Stephenson]

Hi Cyrille,

You should probably use Dependency Walker on an end-user machine and profile
your app with it to see what module(s) it cannot load.

Since your app is 32bit, you'll need to get the 32bit version of Dependency
Walker.  Then, load your exe into DW, click on the Profile menu, and start
profiling.

The log at the bottom of the DW window will show quite clearly which modules
are loaded (you may be surprised :o) and will indicate in red any issues
that are encountered.

Note that not all failures of LoadLibrary or GetProcAddress are actual
errors.  Usually the library that is executing these calls is ready to
handle the case where the DLL may be missing or may be a different version
that does not export a particular function.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Cyrille
Sent: Monday, June 20, 2011 2:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Critical issue

Dear Michael,

Thank you very much. I am currently working with my users to make some 
testing following your advice. It seems that so far, the users 
encountering the bug has IE 8 installed. However, I have on another 
computer IE9 and the desktop application is running perfectly.

They ran Depends on their end-user machine and it reveals two DLLs issue 
(cf. screenshot attached). However, these DLLs are not the SQLite.NET 
one. Thus my question: do the error mentioned in the screenshot attached 
can lead, on the end-user machine, to an error like 
"System.DllNotFoundException: Impossible to load the DLL 
'_SQLite.Interop.DLL_'"? (i.e. an error linked to a different DLL than 
the one mentioned by Depends)

Thanks again very much :)
Best regards,
Cyrille


Le 19/06/2011 20:10, Michael Stephenson a écrit :
> You might also ask them if their IE version is 32-bit or 64-bit.
>
> Yes, ideally you would try Depends on an end-user machine.  Then, you
might
> try tweaking that machine (manually adjust the path, copy ieshims.dll to
> somewhere on the path, etc.) until you come up with a workable solution.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Cyrille
> Sent: Sunday, June 19, 2011 1:56 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Critical issue
>
> Dear Michael,
>
> Thanks for your message. I am going to ask my users for their IE version
to
> see if there is a link with the issue.
>
> Regards,
> Cyrille
>
>
>
> Le 19/06/2011 05:20, Michael Stephenson a écrit :
>> If the IE folder is not on the path, yes, you have to either copy it
>> somewhere on the path, copy it to your application folder, or add the
>> IE folder to the path.  I believe that not having the IE folder on the
>> path is a mistake in the installer for the newer versions of IE.  Back
>> in the day, I think that the IE folder used to be on the path
>> automatically.  This is a good example of bad practices on Microsoft's
>> part and also how they have tied IE into the operating system so that you
> can't get rid of it.
>> I think that this is a typical issue with just about any application
>> (Depends will show a missing delay load) because shell32.dll on newer
>> versions of Windows has this delay load dependency on ieshims.dll.
>>
>> However, usually this is not a problem because usually the delayed
>> load isn't invoked because nothing in the DLL is used.
>>
>> You might want to use Dependency Walker to profile your app and get a
>> good idea of what the exact issue is.  If your app is 32-bit, you'll
>> need the 32-bit depends.exe to profile the app.
>>
>> I'm going to bet that if all of your users aren't having the problem,
>> then the ones with IE7 or earlier are good and the ones with IE8 or
>> later are the ones having the problem.
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Cyrille
>> Sent: Saturday, June 18, 2011 11:36 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Critical issue
>>
>> It seems that I have been too quickly enthusiastic. Indeed, I have
>> already received a feedback from a user who has the same issue running
>> a 32-bit Windows.
>>
>> Thus, I tried to follow your advices Michael. Concerning the first
>> link which advice putting the DLL in the environment path. I am not
>> sure to understand but:
>> - the application is correctly running from VB express
>> - all DLLs are in the application folder (once built) So, this issue
>> seems to be ok, am I right?
>>
>> In your second message, you suggest me to use D

Re: [sqlite] Critical issue

[Michael Stephenson]

You might also ask them if their IE version is 32-bit or 64-bit.

Yes, ideally you would try Depends on an end-user machine.  Then, you might
try tweaking that machine (manually adjust the path, copy ieshims.dll to
somewhere on the path, etc.) until you come up with a workable solution.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Cyrille
Sent: Sunday, June 19, 2011 1:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Critical issue

Dear Michael,

Thanks for your message. I am going to ask my users for their IE version to
see if there is a link with the issue.

Regards,
Cyrille



Le 19/06/2011 05:20, Michael Stephenson a écrit :
> If the IE folder is not on the path, yes, you have to either copy it 
> somewhere on the path, copy it to your application folder, or add the 
> IE folder to the path.  I believe that not having the IE folder on the 
> path is a mistake in the installer for the newer versions of IE.  Back 
> in the day, I think that the IE folder used to be on the path 
> automatically.  This is a good example of bad practices on Microsoft's 
> part and also how they have tied IE into the operating system so that you
can't get rid of it.
>
> I think that this is a typical issue with just about any application 
> (Depends will show a missing delay load) because shell32.dll on newer 
> versions of Windows has this delay load dependency on ieshims.dll.
>
> However, usually this is not a problem because usually the delayed 
> load isn't invoked because nothing in the DLL is used.
>
> You might want to use Dependency Walker to profile your app and get a 
> good idea of what the exact issue is.  If your app is 32-bit, you'll 
> need the 32-bit depends.exe to profile the app.
>
> I'm going to bet that if all of your users aren't having the problem, 
> then the ones with IE7 or earlier are good and the ones with IE8 or 
> later are the ones having the problem.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Cyrille
> Sent: Saturday, June 18, 2011 11:36 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Critical issue
>
> It seems that I have been too quickly enthusiastic. Indeed, I have 
> already received a feedback from a user who has the same issue running 
> a 32-bit Windows.
>
> Thus, I tried to follow your advices Michael. Concerning the first 
> link which advice putting the DLL in the environment path. I am not 
> sure to understand but:
> - the application is correctly running from VB express
> - all DLLs are in the application folder (once built) So, this issue 
> seems to be ok, am I right?
>
> In your second message, you suggest me to use Dependency Walker. I did 
> it and I obtain two errors:
> Warning: At least one delay-load dependency module was not found.
> Warning: At least one module has an unresolved import due to a missing 
> export function in a delay-load dependent module.
> The warnings seem to be linked to the error displayed in the same
> software: Error opening file for IESHIMS.DLL
>
> However, this DLL should be in the IE folder so I do not think I have 
> to copy it in my application folder. Right?
>
> Sorry if my questions are stupid but I cannot figure it out.
>
> Best regards,
> Cyrille
>
>
> Le 18/06/2011 15:50, Black, Michael (IS) a écrit :
>> Also...try dependency walker to ensure you have all the DLLs you need
> deployed with your package that aren't "standard".
>> http://www.dependencywalker.com/
>>
>>
>>
>>
>>
>> Michael D. Black
>>
>> Senior Scientist
>>
>> NG Information Systems
>>
>> Advanced Analytics Directorate
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org 
>> [sqlite-users-boun...@sqlite.org] on behalf of beell [be...@web.de]
>> Sent: Saturday, June 18, 2011 8:37 AM
>> To: sqlite-users@sqlite.org
>> Subject: EXT :Re: [sqlite] Critical issue
>>
>> Am 18.06.2011 14:47, schrieb Cyrille:
>>> Dear all,
>>>
>>> Since I am using the new version of SQLite.NET, some of my users 
>>> have the following error when launching my application:
>>>
>>> System.DllNotFoundException: Impossible de charger la DLL
>>> 'SQLite.Interop.DLL': Le module spécifié est introuvable. (Exception 
>>> de HRESULT : 0x8007007E)
>>>à
> System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[]
>>> utf8Filename, Int32 flags, IntPtr&db)
>>>à System.Data.SQLite.SQLite3.Open(String strFilename, 

Re: [sqlite] Critical issue

[Michael Stephenson]

If the IE folder is not on the path, yes, you have to either copy it
somewhere on the path, copy it to your application folder, or add the IE
folder to the path.  I believe that not having the IE folder on the path is
a mistake in the installer for the newer versions of IE.  Back in the day, I
think that the IE folder used to be on the path automatically.  This is a
good example of bad practices on Microsoft's part and also how they have
tied IE into the operating system so that you can't get rid of it.

I think that this is a typical issue with just about any application
(Depends will show a missing delay load) because shell32.dll on newer
versions of Windows has this delay load dependency on ieshims.dll.

However, usually this is not a problem because usually the delayed load
isn't invoked because nothing in the DLL is used.

You might want to use Dependency Walker to profile your app and get a good
idea of what the exact issue is.  If your app is 32-bit, you'll need the
32-bit depends.exe to profile the app.

I'm going to bet that if all of your users aren't having the problem, then
the ones with IE7 or earlier are good and the ones with IE8 or later are the
ones having the problem.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Cyrille
Sent: Saturday, June 18, 2011 11:36 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Critical issue

It seems that I have been too quickly enthusiastic. Indeed, I have already
received a feedback from a user who has the same issue running a 32-bit
Windows.

Thus, I tried to follow your advices Michael. Concerning the first link
which advice putting the DLL in the environment path. I am not sure to
understand but:
- the application is correctly running from VB express
- all DLLs are in the application folder (once built) So, this issue seems
to be ok, am I right?

In your second message, you suggest me to use Dependency Walker. I did it
and I obtain two errors:
Warning: At least one delay-load dependency module was not found.
Warning: At least one module has an unresolved import due to a missing
export function in a delay-load dependent module.
The warnings seem to be linked to the error displayed in the same
software: Error opening file for IESHIMS.DLL

However, this DLL should be in the IE folder so I do not think I have to
copy it in my application folder. Right?

Sorry if my questions are stupid but I cannot figure it out.

Best regards,
Cyrille


Le 18/06/2011 15:50, Black, Michael (IS) a écrit :
> Also...try dependency walker to ensure you have all the DLLs you need
deployed with your package that aren't "standard".
>
> http://www.dependencywalker.com/
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
> 
> From: sqlite-users-boun...@sqlite.org 
> [sqlite-users-boun...@sqlite.org] on behalf of beell [be...@web.de]
> Sent: Saturday, June 18, 2011 8:37 AM
> To: sqlite-users@sqlite.org
> Subject: EXT :Re: [sqlite] Critical issue
>
> Am 18.06.2011 14:47, schrieb Cyrille:
>> Dear all,
>>
>> Since I am using the new version of SQLite.NET, some of my users have 
>> the following error when launching my application:
>>
>> System.DllNotFoundException: Impossible de charger la DLL
>> 'SQLite.Interop.DLL': Le module spécifié est introuvable. (Exception 
>> de HRESULT : 0x8007007E)
>>   à
System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[]
>> utf8Filename, Int32 flags, IntPtr&   db)
>>   à System.Data.SQLite.SQLite3.Open(String strFilename, 
>> SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool)
>>   à System.Data.SQLite.SQLiteConnection.Open()
>>
>> However, the SQLite.Interop is in the application folder. I specify 
>> that I am using VB 2010 Express with the framework 4.0.
>>
>> Sorry but I really have troubles: if I cannot solve this critical 
>> issue, I cannot see what to do but stopping the development of my 
>> application :-( Thank you very much in advance Best regards, Cyrille
>
> Are you sure that your users have the right dlls according to their 
> system (x86 vs x64)?
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Breaking a string into two

[Michael Stephenson]

Well, it's pretty easy to add regex functionality, for example via PCRE.
You can add this to your custom build of SQLite, or you can add it to your
client application and register a regex function with SQLite after the
database is opened, then use it in your queries.  I can provide an example
if you're interested in going this route.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of BareFeetWare
Sent: Thursday, June 16, 2011 12:06 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Breaking a string into two

Hi all,

I have some source data that includes a "Silo And Region" column of two
words appended together, such as 'NorthPlains', 'SouthPlains',
'NorthSlopes', 'SouthSlopes' etc. I want to split them into two columns.

How can I do this in SQLite? A regex or offset/position and replace function
would take care of this (by looking for the second uppercase letter), but
there's no regex or offset/position function provided.

If I know all of the possible combinations beforehand, such as:

create table "Silo Group"
(   ID integer primary key not null
,   Name text collate nocase not null
)
;
insert into "Silo Group" (Name) values ('North') ; insert into "Silo Group"
(Name) values ('South') ; create table "Region"
(   ID integer primary key not null
,   Name text collate nocase not null
)
;
insert into "Region" (Name) values ('Plains') ; insert into "Region" (Name)
values ('Slopes') ;

then I can extract by looking for a match in each table, such as:

select  "Silo Group".Name as "Silo Group"
,   "Region".Name as "Region"
from "Import"
left join "Silo Group" on "Import"."Silo And Region" like "Silo
Group".Name || '%'
left join "Region" on "Import"."Silo And Region" like % ||
"Region".Name ;

But I don't know all of the parts (ie "Silo Group" and "Region") until I
bring in the Import.

Any ideas? Hopefully I'm missing something obvious in SQLite. I am basically
trying to normalise the supplied data, and don't want to have to resort to
application code, just for the sake of this one function.

Thanks,
Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

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

[sqlite] Anyway to order query results by "in" list?

[Michael Stephenson]

Wondering if anyone has a way to execute a query that selects rows based on
a list of rowids and returns the results in the order of the rowids passed
in.

Thanks,

~Mike

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

Re: [sqlite] Trigger help needed

[Michael Stephenson]

NEW.timestamp < OLD.timestamp

(less than)?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jim Mellander
Sent: Wednesday, June 01, 2011 3:31 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Trigger help needed

Hopefully someone can help me with this

I have a table with IP addresses and timestamps - I want to update the table
when the new timestamp is later than the old one


$ sqlite3 test.db
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
CREATE TABLE ip_timestamps (ip text unique, timestamp date); CREATE UNIQUE
INDEX ip_index on ip_timestamps (ip ASC); CREATE TRIGGER ts_update after
update on ip_timestamps when NEW.timestamp < OLD.timestamp BEGIN update
ip_timestamps set timestamp = OLD.timestamp; END;


I'm adding/updating records with statements like:

INSERT OR REPLACE into ip_timestamps VALUES ( "182.71.33.222" , 1306932777
);

The goal is to keep the latest timestamp in the database (the older
timestamp could occur later in the input than the newer timestamp), but the
trigger doesn't seem to be working - I assume the trigger is flawed.  Any
suggestions?


Thanks in advance.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Create DB file and then Create Table - Table FAILS.

[Michael Stephenson]

I don't see anywhere a "sqlCmd.Execute()" or whatever the method is to
execute the command.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Don Ireland
Sent: Tuesday, May 31, 2011 11:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Create DB file and then Create Table - Table FAILS.

Ooh.  I don't know what happened to that message it was sent as paragraphs.
It's kinda hard to read now.

Don Ireland

-Original Message-
From: Don Ireland 
To: SQLite 
Sent: Tue, 31 May 2011 10:18 PM
Subject: [sqlite] Create DB file and then Create Table - Table FAILS.

I'm hoping someone can help me with this. Using Visual Studio C++, the
following code DOES create the DB file. But the table doesn't get created
and I'm stumped as to why it won't create the table. SQLiteConnection conn;
conn.ConnectionString = "Data
Source=D:\Users\Don\Downloads\CashBoxPrefs.dat"; conn.Open(); SQLiteCommand
sqlCmd(%conn); sqlCmd.CommandText = "CREATE TABLE IF NOT EXISTS Prefs
(SyncEnabled bool,SyncWatchPort int,SyncLoginKey TEXT,SyncAccessKey TEXT,
SyncLogFile TEXT, SyncLogClearInterval int,GenLatestBook TEXT, GenBookMRU_0
TEXT,GenBookMRU_1 TEXT, GenBookMRU_2 TEXT, GenBookMRU_3 TEXT, GenBookMRU_4
TEXT);"; But if I run following at the SQLite3.exe from the command line, it
DOES create the file and the table. sqlite3 Cashboxprefs.dat sqlite> CREATE
TABLE IF NOT EXISTS Prefs (SyncEnabled bool,SyncWatchPort int,SyncLoginKey
TEXT,SyncAccessKey TEXT, SyncLogFile TEXT, SyncLogClearInterval
int,GenLatestBook TEXT, GenBookMRU_0 TEXT,GenBookMRU_1 TEXT, GenBookMRU_2
TEXT, GenBookMRU_3 TEXT, GenBookMRU_4 TEXT);

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

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

Re: [sqlite] Is it possible to optimize the readperformanceofa C++ app using sqlite pragma journal_mode = wal

[Michael Stephenson]

If you change the page size after the database has been created and written
to, yes, you need to a vacuum to have SQLite reorganize the database pages.
Note that even this does not work if WAL is in use (if WAL is turned on,
then you can't change the database page size after the database has been
written to, even with a vacuum).

What you want to do, however, is set the page size right after you first
open the database, before you issue your first create table statement.  When
you first open a new database, all you have a file descriptor with a 0-byte
file.  You want to issue your pragmas at this point, before you actually
write to the database.  The settings are then storage on the first database
page (page 1), so  you don't have to do a vacuum since the setting is stored
with the database forever more.

If you're building SQLite yourself, you can also specify the default page
size, default number of cache pages, etc. via defines when you're building
the library or DLL.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Sunday, May 29, 2011 9:22 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is it possible to optimize the readperformanceofa C++
app using sqlite pragma journal_mode = wal


On 30 May 2011, at 2:08am, Frank Chang wrote:

> Thank you for your help with  the PRAGMA page size question. Sometimes I
see C++ programs which first issue the PRAGMA page_size = 4096. Then these
C++ programs request a sqlite3_execute(Database,"VACUUM",callback,0,)
which takes about 4 minutes to complete.
>  We wondering if the
sqlite3_execute(Database,"VACUUM",callback,0,) is necessary to change
the PRAGMA page_size=4096.

I just looked it up:

http://www.sqlite.org/lang_vacuum.html

You're right: VACUUM allows you to change the page size of a database file.
I didn't know that.  Clever.

Yes, it won't happen without the VACUUM.  This rewrites the entire database
file.

Under normal circumstances the amount of time doesn't matter.  You don't
normally use VACUUM once you have your system up and running.

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

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

Re: [sqlite] Is it possible to optimize the readperformanceof a C++ app using sqlite pragma journal_mode = wal

[Michael Stephenson]

You're welcome.  Let's hold judgment on whether any of them turn out to be
helpful :o).

Increasing the page size might help, it also gives you four times the cache
with the same default setting of 2000 cache pages.  I would suggest again
that you consider bumping that number of cache pages up if you have memory
available.  On a modern system, it should be no big deal to allocate, say,
50MB for the page cache.  Of course, increasing the cache will only help if
your reads tend to be in the cache.

By the way, the simplest way to take journaling out of the picture if you're
done with all of your inserts and are just reading at that point is to
reopen the database in read-only mode.  This will turn off journaling and I
think will might also reduce any locking that might occur even if you are
accessing the database from multiple threads.

Also by the way, if you're doing C++, you might want to consider using a C++
wrapper for SQLite; it might simplify your code significantly.  I've used a
thin wrapper called CppSqlite for a couple of years (and have had to tweak
it only slightly during this time).  Here is SQLite's page on wrappers:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Frank Chang
Sent: Saturday, May 28, 2011 9:38 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is it possible to optimize the readperformanceof a C++
app using sqlite pragma journal_mode = wal


 Michael Stephenson, We want to thank you again for your excellent
suggestions regarding how to improve the speed of our Sqlite WAL reads and
our deduper prototype. We looked at the SQlite documentation for increasing
the Sqlite page size to 4K and an excerpt of our code is shown below. If we
can implement your suggestions corrrectly, we try to let you and the
sqlite-users group about our results if they are meaningful. Thank you for
taking the time to write all of your suggestions and ideas. 
 
strcpy(Command,"PRAGMA page_size=4096");
ReturnValue=sqlite3_prepare(Database,Command,-1,,0);
printf("1 ReturnValue %d\n",ReturnValue); status = sqlite3_step(Statement);
printf("1 status %d\n",status);
status = sqlite3_finalize(Statement);
printf("1 status %d\n",status);
 
strcpy(Command,"VACUUM");
ReturnValue=sqlite3_exec(Database,Command,callback,0,);
if (Msg!=0) {
strcpy(LastError,Msg);
sqlite3_free(Msg);
}
printf("1-1 ReturnValue %d\n",ReturnValue); 
 
 
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Is it possible to optimize the read performanceof a C++ app using sqlite pragma journal_mode = wal

[Michael Stephenson]

Not an expert, but I doubt that WAL will have much of anything to do with
your read performance after you've populated the database and committed the
last insert transaction.  WAL mostly deals with inserts
(http://www.sqlite.org/draft/wal.html).  Once the last transaction is
written and you are only reading, WAL is basically out of the picture.

For this same reason, I doubt that turning off journaling altogether will
have much effect on your read performance, because if all you're doing is
reading then there shouldn't be any journaling at all (reads are not
transactional).

I was thinking that something like what was already suggested might work, if
you could effectively hash values as you insert them using some method that
would make finding duplicates a matter of hash collisions, and especially if
you could order the rows with an index on the hash to help ensure that they
are on database pages close to each other.  Not sure if that will work,
however, with something like a Levenshtein distance if you must compare
words to words that already exist.

I'm also kind of wondering if the full-text search extension (FTS3 or FTS4)
might help you process words faster.

As for your read performance, if you are doing or can do things
single-threaded at read time, either compiling SQLite as single-threaded
(SQLITE_THREADSAFE=0) or setting it single threaded at start time
(SQLITE_CONFIG_SINGLETHREADED passed to sqlite3config()) would probably
improve things noticeably because mutexes would not be used at all.

Other than that, the more cache you can have in memory at once may make a
big difference.  The default SQLite page size is 1Kb, and the default number
of pages kept in the in-memory cache is 2000, so only 2MB of cache.  If you
have enough memory to prevent the OS from doing excessive paging of virtual
memory, you might adjust those values up significantly.  A 4Kb page size is
often a good choice because it often matches hard disk sector size.  If you
do this plus bump up the number of cache pages, you may be able to
significantly reduce disk i/o (which is the slowest operation).

You may be able to go a step further with a large page cache allocated from
a static buffer using sqlite3_config() with SQLITE_CONFIG_PAGECACHE.

If you have enough memory for your cache, you might eek out even some extra
performance if you could allocate this static buffer and lock it in memory
via API calls such as VirtualLock, SetProcessWorkingSetSize(Ex), etc., to
try to ensure that the OS does not push the pages to disk.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Frank Chang
Sent: Friday, May 27, 2011 11:06 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is it possible to optimize the read performanceof a
C++ app using sqlite pragma journal_mode = wal 


  Roger Binns, Thank you suggesting that we run a benchmark that tests our
prototype deduper with and without WAL using different page sizes and
different transactions.
 
>> You never answered the important bit - is your concern about initial 
>> population of the database or about runtime later on.
 
 I apologize for not answering your question. Our primary concern is about
the runtime later on rather than the initial population of the database. Is
it possible for you to recommend how we should use the latest sqlite
distribution(i.e. pragmas,sqlite function parameters) if we are concerned
about the run time later on. Thank you for your help.
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Incorrect left join behavior?

[Michael Stephenson]

Makes sense.  Thanks.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Wednesday, May 25, 2011 9:37 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Incorrect left join behavior?

> I can use two left joins.  While writing the first left join, I discovered
> that it is behaving like an inner join.
>
> select *
> from test a
>   left join test b on a.component = b.component
> where a.machine = 'machine1'
>   and b.machine = 'machine2';

By the WHERE condition you limit results of your left join only to
those that have b.machine equal to 'machine2' and exclude other 2 rows
where b.machine is null. To get the result you need you have to
rewrite the query in one of the following ways:

select *
from test a left join test b
on a.component = b.component
and b.machine = 'machine2'
where a.machine = 'machine1';

or

select *
from test a left join test b
on a.component = b.component
where a.machine = 'machine1'
and (b.machine is null or b.machine = 'machine2');


I think the first syntax is preferable.


Pavel


On Wed, May 25, 2011 at 9:09 AM, Michael Stephenson
<domehead...@gmail.com> wrote:
> Hi, I have a table as below.  Note that machine1 has 3 components (1-3),
> while machine2 has only 1 components (1).
>
>
>
>
> Machine
>
> Component
>
> Version
>
>
> machine1
>
> component1
>
> 1
>
>
> machine1
>
> component2
>
> 1
>
>
> machine1
>
> component3
>
> 1
>
>
> machine2
>
> component1
>
> 1
>
>
>
> create table test(Machine, Component, Version);
>
> insert into test values('machine1', 'component1', 1);
>
> insert into test values('machine1', 'component2', 1);
>
> insert into test values('machine1', 'component3', 1);
>
> insert into test values('machine2', 'component1', 1);
>
>
>
> I need to see what component versions are different on the machines.  What
I
> really need is a "self outer join", but since SQLite doesn't do outer
joins,
> I can use two left joins.  While writing the first left join, I discovered
> that it is behaving like an inner join.
>
>
>
> select *
>
> from test a
>
>   left join test b on a.component = b.component
>
> where a.machine = 'machine1'
>
>   and b.machine = 'machine2';
>
>
>
>
>
> The expected behavior would be to return 3 rows: one row showing the
> component versions for machine1 and machine2, and two rows showing the
> component version for machine1 with null for machine2.
>
>
>
> The actual behavior is that of an inner join:  I get only one row that
shows
> the component versions for both machines, and the other two components,
> which don't exist for machine2, do not appear in the results at all.
>
>
>
> I'm aware that SQLite only does left joins properly using SQL92 syntax,
> which I am using.  I'm also aware that I'm using a where clause, and the
> docs state that join constraints in a where clause cause a left join to
> behave as an inner join.  However, my where clause does not constrain the
> join expression, just the initial rows involved available to be joined.
>
>
>
> I'm able to work around this by:
>
>
>
> select *
>
> from (select * from test where machine = 'machine1') a
>
>   (select * from test where machine = 'machine2') b
>
> on a.component = b.component;
>
>
>
> But, I just wanted to point this behavior out.  Perhaps I've missed
> something.  It looks like any time there is a where clause, a left join is
> going to behave as an inner join.
>
>
>
> Thanks,
>
>
>
> ~Mike
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Incorrect left join behavior?

[Michael Stephenson]

Hi, I have a table as below.  Note that machine1 has 3 components (1-3),
while machine2 has only 1 components (1).

 


Machine

Component

Version


machine1

component1

1


machine1

component2

1


machine1

component3

1


machine2

component1

1

 

create table test(Machine, Component, Version);

insert into test values('machine1', 'component1', 1);

insert into test values('machine1', 'component2', 1);

insert into test values('machine1', 'component3', 1);

insert into test values('machine2', 'component1', 1);

 

I need to see what component versions are different on the machines.  What I
really need is a "self outer join", but since SQLite doesn't do outer joins,
I can use two left joins.  While writing the first left join, I discovered
that it is behaving like an inner join.

 

select *

from test a

   left join test b on a.component = b.component

where a.machine = 'machine1'

   and b.machine = 'machine2';

 

 

The expected behavior would be to return 3 rows: one row showing the
component versions for machine1 and machine2, and two rows showing the
component version for machine1 with null for machine2.

 

The actual behavior is that of an inner join:  I get only one row that shows
the component versions for both machines, and the other two components,
which don't exist for machine2, do not appear in the results at all.

 

I'm aware that SQLite only does left joins properly using SQL92 syntax,
which I am using.  I'm also aware that I'm using a where clause, and the
docs state that join constraints in a where clause cause a left join to
behave as an inner join.  However, my where clause does not constrain the
join expression, just the initial rows involved available to be joined.

 

I'm able to work around this by:

 

select *

from (select * from test where machine = 'machine1') a

   (select * from test where machine = 'machine2') b

on a.component = b.component;

 

But, I just wanted to point this behavior out.  Perhaps I've missed
something.  It looks like any time there is a where clause, a left join is
going to behave as an inner join.

 

Thanks,

 

~Mike

 

 

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