Re: [sqlite] How to force to block sqlite file, while my conecction is open !

[Edwin Hernán Barrios Núñez]

Hi, all !

> What kind of error is closing your transaction?  When SQLite hits
> an error, it usually leaves the transaction open.

i think  that above it's  not  completed true, think you  have a table
with a UNIQUE constraint, when you create a transaction and send a
insert that violates this UNIQUE, all change on you transaction are
rollbacked !

My point with my last message was if my there is a more drastic
solution to block my db when my aplication is working !. i don't
really need silmuteaneous connections.

Thanks all !

Re: [sqlite] SQL statements in progress error message

[Bill KING]

Nicholas Bastin wrote:
> When trying to commit a transaction, I'm getting the following error
> message from sqlite3_exec():
>
> "cannot commit transaction - SQL statements in progress"
>
> Is there any way to find out what statements are in progress?  Also,
> what could cause this?  I can guarantee that sqlite3_step has returned
> SQLITE_DONE on all my statements before I get to this point.  Is that
> not sufficient?
>
> (Is it better to compile a statement that says COMMIT and use
> sqlite3_step, rather than sqlite3_exec ("COMMIT")?  )
>
> --
> Nick
>
>   
I found out yesterday, that if you have an open count(*) statement, and
fail to finalise it before doing an insert/update commit, then you get
this error code. This may help a little? (only seemed to be on count(*)
statements, other normal select statements, didn't seem to affect this).

-- 
Bill King, Software Engineer
Trolltech, Brisbane Technology Park
26 Brandl St, Eight Mile Plains, 
QLD, Australia, 4113
Tel + 61 7 3219 9906 (x137)
Fax + 61 7 3219 9938
mobile: 0423 532 733

[sqlite] B#

[John Stanton]

This is an interesting development for embedded system developers who 
use Sqlite.


http://www.embedded.com/showArticle.jhtml?articleID=183700818
http://www.embedded.com/showArticle.jhtml?articleID=185302940

Quote -

The week before ESC Silicon Valley, the number of you who clicked on
Part 1 in "B#: A programming language for small footprint embedded
systems applications" made it the most-read story on any of CMP
Media's on-line properties in quite a while. In response, we are now
running Part 2 on the underlying Virtual Machine in the Technical
Insights section. Check it out.
The overwhelming popularity of that first story on the features and
capabilities of B# (pronounced B-sharp) suggests that as deeply
embedded deterministic and real-time control applications become more
connected, there is a critical need for a language and a Virtual
Machine architecture that has capabilities similar to B# as well as
being object-oriented and extremely small.
Sun, IBM and many in the Java developer community have been focused on
the explosion of opportunities in mobile designs where soft real time
is the norm and where applications are neither deterministic nor small
in size. They seem to be blind to the explosion of opportunities in
the smaller footprint and hard real-time applications in many embedded
control applications, especially as the move toward ubiquitously
connected (wired and wireless) devices and sensors takes hold.
The number of devices that will need this sort of capability will
quickly dwarf the volumes we now see for mobile devices. The response
you have had to the B# series on Embedded.com tells me that if the
Java community does not quickly respond to the need, developers will
move on to somebody who will.
What do you think? (Bernard Cole, Site Editor, Embedded.com,

Re: [sqlite] MMAP

[John Stanton]

Christian Smith wrote:

At the risk of this turning into another argument between us...


On Thu, 20 Apr 2006, John Stanton wrote:



Q How do programs get to be slow and bloated?
A One small inefficiency at a time.

It is just as inefficient to send dynamically created content through a
series of buffers as it is to send a static file, and just as unecessary
when the machine and OS has a mechanism embedded to avoid it.

If you half the overhead of a program which hits its limit at 1,000
users, you get the potential to service 2,000 or the ability to add
features and still service the 1,000.





Assuming you spend x% of your time in function Y, then halving the
overhead of function Y will yield x/2% of run time. The lower x is, the
lower the savings.

Some overhead is just too small to be worth worrying about.

In your case, remember that you'll also have to create the file before use
(synchronous IO) and resize it to the correct size before sending it down
the pipe (sendfile sends the whole file) which will involve more
synchronous IO. You'll also have to re-mmap your file each time you
truncate it and re-write it, as mmap mappings are undefined for truncated
files etc. Invalidating mappings could also result in cross CPU interrupts
to flush TLB entries in SMP machines, which affects not only your process
but also processes on the CPU that got interrupted.

If the cross CPU interrupts haven't put you off yet, then the required
synchronous IO sure as hell should have.




Now, if you dynamic content is likely to be reused, then that is a
different matter. Write to a file as a cache entry, and sendfile the cache
entry. That I can see working.


Now you get it. :-)  Maintaining buffers not as areas of heap but as 
mapped VM spaces with an open file descriptor/handle permits one to 
ultimately sendfile/TransmitFile using the option of defining the number 
of bytes to send.  File creation and mapping becomes a setup function, 
performed every year or so when the machine is rebooted.


As for overhead, 1% here, 2% there etc can mean the difference between 
success and failure.

JS





JS





Christian

[sqlite] More on: Windows problem when updating after reboot

[Milton Sagen]

I've looked some more at this (unfortunately using 2.8.16 although  
the problem is also exhibited by 3.3.4) and the problem seems to be  
in sqlitepager_commit and its call to pager_get_all_dirty_pages which  
returns every page of the database as dirty and then proceeds to  
write every page to disk by calling pager_write_pagelist. Only one  
record has been changed, and of course as the database gets larger  
the more writing it does. I've been building the Windows sqlite  
source with CodeWarrior 9.6 on the Mac and targeting the Windows  
platform. I'm wondering if there is some compile define I should be  
setting but am unaware of.


Milt

On Apr 04, 2006, at 17:02, Milton Sagen wrote:

After a machine restart the first time I try to change a field in a  
record, on Windows, it takes an inordinately long time for the  
sqlite code to return, the amount of time is dependent on the size  
of the database but even for a 20 Meg one with about 4000 records,  
the time is in the order of a minute. Once the change is made I can  
quite the program and relaunch it, open the database, and make  
another change exactly like the first to another record and the  
sqlite code returns almost immediately. On the Mac OS X, I don't  
see this delay.


I see this with SQLite 2.8.16 as well as 3.3.4.

Here's the schema for the database:

CREATE TABLE MyTable (RID Integer Primary Key, Filename Text,  
PathType Integer, Path Text, Category Text null, Favorite Boolean  
null, Missing Boolean null, Thumbnail Blob null);

CREATE UNIQUE INDEX MyTable_RID on MyTable (RID);
CREATE INDEX MyTable_Filename on MyTable (Filename);

a record is added with

INSERT into MyTable (RID, Filename, PathType, Path, Category,  
Thumbnail) (1, 'afile.jpg', 1, 'path', 'data');


where
path = dos path (windows path - whatever you want to call it - it  
starts with c:\)
data = hex encoded data of a raw bit map - (this originally used  
2.8.16 and I could see any other way to get raw binary data into  
the database)


the update is simply this:

UPDATE MyTable set Favorite = 1 where RID = 1;

In fact I find that I get a delay anytime I change the database  
following a reboot of the machine, i.e. deleting a record,  
inserting a record, updating a record.


The routine that seems to get take the time is sqlite3VdbeExec.

Anybody have any idea what might be going on here or how to fix it?  
Why does it only happen after a reboot of the machine? Similarly,  
and this I see on both the Mac and Windows if on launch I do the  
following:


select rid from MyTable order by lower(filename);

on a database with approximatley 4000 records it will take about 14  
seconds, after a reboot, to complete. Quitting the program and  
relaunching on the Mac the select completes in 0.04 seconds.


Milt

Re: [sqlite] Direct use of SQLite btree functions / performance

[Matt Sergeant]

On 20-Apr-06, at 9:10 AM, Jay Sprenkle wrote:


Just out of curiosity why is this data in the database?
I've seen very few applications where the blob is indexed or  
operated upon

by the database and it's always a pain to deal with it. We always just
left binary data in the file system and stored references to it in the
database.


I just have to backup one file :-)

__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__

Re: [sqlite] Direct use of SQLite btree functions / performance

[Jay Sprenkle]

On 4/20/06, Dennis Jenkins <[EMAIL PROTECTED]> wrote:
> Jay Sprenkle wrote:
> > Just out of curiosity why is this data in the database?
> > I've seen very few applications where the blob is indexed or operated upon
> > by the database and it's always a pain to deal with it. We always just
> > left binary data in the file system and stored references to it in the
> > database.
> >
>
>
> For us, we use the encryption module, and our blobs must be encrypted.
> I did not want to mess with encrypting the blobs myself and the
> associated key management.
>
> I also don't want to have to deal with the blob and data pointing to it
> to be out of sync.  The atomicity of storing the blob and its meta data
> is very nice.

having everything in one file and encrypted might be pretty convenient!

Re: [sqlite] Could not add Sqlite.dll to my project...Plz Help.

[Robert Simpson]

- Original Message - 
From: "Dhivya" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, April 20, 2006 6:44 AM
Subject: [sqlite] Could not add Sqlite.dll to my project...Plz Help.



Hi Everyone,

 I am a new user to SQLite.

I could not add sqlite.dll to my project. When i try to add the reference, 
it raises this error:


 "A reference to "c:\Downloads\SQLite\SQLite.Net1.0.0\sqlite.cll" could 
not be added. This is not a valid assembly or COM component. Only 
assemblies with extension 'dll' and COM components can be referenced. 
Please make sure that the file is accessible, and that it is a valid 
assembly or COM component. "


 Plz help me to solve this problem.



This looks vaguely like a .NET project you're doing?  Where did you get the 
provider from?  The obvious answer here is the reference you are trying to 
add has a ".CLL" extension (which I've never seen before) instead of a 
".DLL" extension.


The error message is pretty self-explanatory.

Re: [sqlite] Using sqlite3_open or sqlite3_open16?

[Christian Smith]

On Thu, 20 Apr 2006, DBTools Software wrote:

>Hi,
>
>I have an application that needs to open a database in the users's personal
>folder. I noticed that in some circunstances the sqlite3_open fail as the
>filename is in UTF16 format. I don't know that in advance so I could open
>the db with sqlite3_open16.
>
>The question is:
>
>Is it safe to always use the UTF16 functions independent of the
>localization? What I mean is can I use the functions sqlite3_xxx16 instead
>of sqlite3_xxx for all cases?


Your data should always be in UTF-16 to use sqlite3_xxx16, else you'll
incur the cost of transforming your date to/from UTF-8.

If most of your data is UTF-8 or ASCII, use the regular UTF-8 functions.
If your filename is UTF-16, just convert it to UTF-8 (it's a relatively
simple, unambiguous transformation) and use sqlite3_open.


>
>Thanks,
>
>COS
>
>

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

[sqlite] Using sqlite3_open or sqlite3_open16?

[DBTools Software]

Hi,

I have an application that needs to open a database in the users's personal
folder. I noticed that in some circunstances the sqlite3_open fail as the
filename is in UTF16 format. I don't know that in advance so I could open
the db with sqlite3_open16.

The question is:

Is it safe to always use the UTF16 functions independent of the
localization? What I mean is can I use the functions sqlite3_xxx16 instead
of sqlite3_xxx for all cases?

Thanks,

COS

[sqlite] Could not add Sqlite.dll to my project...Plz Help.

[Dhivya]

Hi Everyone,
   
  I am a new user to SQLite.
  
I could not add sqlite.dll to my project. When i try to add the reference, it 
raises this error:
   
  “A reference to “c:\Downloads\SQLite\SQLite.Net1.0.0\sqlite.cll” could not be 
added. This is not a valid assembly or COM component. Only assemblies with 
extension ‘dll’ and COM components can be referenced. Please make sure that the 
file is accessible, and that it is a valid assembly or COM component. ”
   
  Plz help me to solve this problem.
   
  Thanks in Advance,
Dhivya. 


-
 Jiyo cricket on Yahoo! India cricket
Yahoo! Messenger Mobile Stay in touch with your buddies all the time.

Re: [sqlite] Direct use of SQLite btree functions / performance

[Dennis Jenkins]

Jay Sprenkle wrote:
> Just out of curiosity why is this data in the database?
> I've seen very few applications where the blob is indexed or operated upon
> by the database and it's always a pain to deal with it. We always just
> left binary data in the file system and stored references to it in the
> database.
>   


For us, we use the encryption module, and our blobs must be encrypted. 
I did not want to mess with encrypting the blobs myself and the
associated key management.

I also don't want to have to deal with the blob and data pointing to it
to be out of sync.  The atomicity of storing the blob and its meta data
is very nice.

Re: [sqlite] Direct use of SQLite btree functions / performance

[Jay Sprenkle]

> >
> > sqlite3BtreeData() actually reads a prefix of the data.
> > Because of the way large blobs are stored (as a linked list
> > of disk pages) you have to start reading at the beginning
> > and read everything up to the point of interest.
>
> Random access for blobs would be ideal, but even a sequential block
> fetching mechanism for blobs would be a useful SQLite API addition.

Just out of curiosity why is this data in the database?
I've seen very few applications where the blob is indexed or operated upon
by the database and it's always a pain to deal with it. We always just
left binary data in the file system and stored references to it in the
database.

Re: [sqlite] upgrading versions of database files across version of SQLite

[Christian Smith]

On Thu, 20 Apr 2006, Steve Bland wrote:

> We have been using SQLite ( v2.8.6 ) for a while now and as part of a
> new release were thinking of moving ahead to a more current version.
>
> But that is where the issues began. I did search the archives, but found
> noting of any real use on this. The same with the faq page and the
> changes page. Naturally I could have missed something but...
>
> If I try to load one of our existing files with the command line tool, I
> get the response "Error: file is encrypted or is not a database". If I
> do a read command on the same file, I get "Incomplete SQL: ** This file
> contains an SQLite 2.1 database **" and then there is incomplete and
> rather odd SQL data. The 2.1 database message actually overwrites the
> SQL on the screen.


The file format changed with SQLite 3.x. This was to allow certain
improvements (such as blobs, 64bit integers etc) that wearn't possible
with the SQLite 2.1 based format.


>
> If I load these files with v2.8.17 all is fine. Is there a compatibility
> problem? Is there an import tool?


You can export data from v2.8.17 using .dump, and read in the SQL commands
from the dump to a 3.x database.

However, the SQLite 3 API is also different, so unless you're using a
wrapper, it might just be easier to stay with 2.8 unless you want some of
the new functionality from 3.x.


>
> I know this is probably covered in either the email digests or somewhere
> on the site, but I could not find any information.


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


>
>
> Steve
>

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

[sqlite] upgrading versions of database files across version of SQLite

[Steve Bland]

> We have been using SQLite ( v2.8.6 ) for a while now and as part of a new 
> release were thinking of moving ahead to a more current version.
> 
> But that is where the issues began. I did search the archives, but found 
> noting of any real use on this. The same with the faq page and the changes 
> page. Naturally I could have missed something but...
> 
> If I try to load one of our existing files with the command line tool, I get 
> the response "Error: file is encrypted or is not a database". If I do a read 
> command on the same file, I get "Incomplete SQL: ** This file contains an 
> SQLite 2.1 database **" and then there is incomplete and rather odd SQL data. 
> The 2.1 database message actually overwrites the SQL on the screen.
> 
> If I load these files with v2.8.17 all is fine. Is there a compatibility 
> problem? Is there an import tool?
> 
> I know this is probably covered in either the email digests or somewhere on 
> the site, but I could not find any information.
> 
> 
> Steve
> 
> 

Re: [sqlite] MMAP

[Christian Smith]

At the risk of this turning into another argument between us...


On Thu, 20 Apr 2006, John Stanton wrote:

>Q How do programs get to be slow and bloated?
>A One small inefficiency at a time.
>
>It is just as inefficient to send dynamically created content through a
>series of buffers as it is to send a static file, and just as unecessary
>when the machine and OS has a mechanism embedded to avoid it.
>
>If you half the overhead of a program which hits its limit at 1,000
>users, you get the potential to service 2,000 or the ability to add
>features and still service the 1,000.



Assuming you spend x% of your time in function Y, then halving the
overhead of function Y will yield x/2% of run time. The lower x is, the
lower the savings.

Some overhead is just too small to be worth worrying about.

In your case, remember that you'll also have to create the file before use
(synchronous IO) and resize it to the correct size before sending it down
the pipe (sendfile sends the whole file) which will involve more
synchronous IO. You'll also have to re-mmap your file each time you
truncate it and re-write it, as mmap mappings are undefined for truncated
files etc. Invalidating mappings could also result in cross CPU interrupts
to flush TLB entries in SMP machines, which affects not only your process
but also processes on the CPU that got interrupted.

If the cross CPU interrupts haven't put you off yet, then the required
synchronous IO sure as hell should have.

Now, if you dynamic content is likely to be reused, then that is a
different matter. Write to a file as a cache entry, and sendfile the cache
entry. That I can see working.


>JS
>


Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] Direct use of SQLite btree functions / performance

[Christian Smith]

On Wed, 19 Apr 2006, Joe Wilson wrote:

>--- [EMAIL PROTECTED] wrote:
>> Joe Wilson <[EMAIL PROTECTED]> wrote:
>> >
>> > If you read the blob all in at once, true.
>> > But doesn't sqlite3BtreeData() allows you to read a partial chunk of
>> > data from the blob at an arbitrary offset? This could be advantageous
>> > if you are dealing with multi-megabyte blobs and do not require/want
>> > the entire blob in memory at once.
>> >
>>
>> sqlite3BtreeData() actually reads a prefix of the data.
>> Because of the way large blobs are stored (as a linked list
>> of disk pages) you have to start reading at the beginning
>> and read everything up to the point of interest.
>
>Random access for blobs would be ideal, but even a sequential block
>fetching mechanism for blobs would be a useful SQLite API addition.


You could simply partition the blob yourself, and use a compound key to
access parts of a BLOB at random:
CREATE TABLE blocks (
id integer,
offset integer,
block blob,
primary key (id,offset) );

Assuming you store, say, 16K in each block, then access to any one 16k
block then becomes a single index lookup and 17 page reads. Much more
efficient than storing the whole BLOB in one.

Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \