[sqlite] System.Data.SQLite, Virtual Tables, and ThreadAbortException Issues

2015-05-13 Thread Mike Nicolino
Yes, this branch does resolve the issue, thanks much!

This branch looks to be from the current mainline; I'll apply the fix locally 
to the 1.0.96.0 released version until the next release of System.Data.SQLite.

Thanks,
MikeN


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joe 
Mistachkin
Sent: Tuesday, May 12, 2015 8:43 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] System.Data.SQLite, Virtual Tables, and 
ThreadAbortException Issues


Mike Nicolino wrote:
>
> The issue is isolated to ThreadAbortException since you can't catch it 
> and prevent it from being re-thrown.  I did find a 'fix' for a similar 
> issue in SystemData.SQLite back on 10/11/2012:
>

Thanks for the excellent analysis of the issue.  I believe you are completely 
correct.

The previous "fix" was for sqlite3_prepare(), to prevent any leakage of native 
handles prior to their ownership being "transferred" into an official critical 
handle object.

I've now implemented the necessary changes on a branch, pending more testing,
here:

https://system.data.sqlite.org/index.html/timeline?r=stepNoThreadAbort

Could you test these changes in your environment and let us know if they clear 
the issue?

--
Joe Mistachkin

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


[sqlite] xBestIndex() implementation question

2015-05-13 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/13/2015 08:06 PM, Jilong Kuang wrote:
> Sure, that is one option. But I'm just wondering if inside the
> xBestIndex() we can get the value field, we can do a much better
> job to give the cardinality info.

Note that the value could be something other than a constant (eg an
expression like price > delivery_charge*0.9)

You are just helping SQLite pick the best index out of all the
possibilities, so as long as they compare sensibly it is fine.
SQLite's analyze command builds statistical information that is useful
(eg how many different values there are, how (un)evenly they are
distributed).  You could do something similar.

Or just follow Eric's suggestion :-)

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlVUGasACgkQmOOfHg372QSPcACgpmvvhwEEmj9+ccVgI+qTaw0B
y/YAn2WtrppfLn0PvDpa+n1loqFYgzQW
=vskS
-END PGP SIGNATURE-


[sqlite] sqlite3: sqlite3_step() return SQLITE_FULL error.

2015-05-13 Thread Deepak Hegde
Hi sir,

Thanks a lot. It is working fine with the same path and with multiprocess.

Thanks and regards
Deepak

On Wednesday 13 May 2015 06:44 PM, Richard Hipp wrote:
> On 5/13/15, Deepak Hegde  wrote:
>> I have gone through the document and it says, it is not thread safe.
>> What if the same database is used in the multiple processes?
>> Can we have actual database path and this temporary path same?
>>
> process!=thread.  It is perfectly safe to run PRAGMA
> temp_store_directory and point it to the same directory on multiple
> processes.  It is perfectly safe to have temp_store_directory and the
> main database directory be the same.


-- 
Thanks and Regards
Deepak



[sqlite] Tests regarding custom build of SQLite

2015-05-13 Thread Sairam Gaddam
In order to run .test files(sqlite test codes) in SQLite, I used

 ./testfixture NAMEOFFILE.test

Can anyone kindly tell which build of sqlite will TESTFIXTURE access?

Because even when I make modifications to sqlite3.c source code, the output
of the test file under test has no effect.


And one more question is that, In order to test my custom build of
sqlite3.c,
I installed both TCL and SQLite, and gave the command ./configure
After that the source file sqlite3.c file is formed and I replaced the
original sqlite3.c file with my customized sqlite3.c with the same name.
But when I ran make test command, the program again replaces my customized
sqlite3.c with the original sqlite3.c source file and tests it. So I am not
able to run tests on my modified file. So can anyone tell why it replaces
with original file and what to do in order to test my custom build.


[sqlite] sqlite3: sqlite3_step() return SQLITE_FULL error.

2015-05-13 Thread Deepak Hegde
Hi,

Thanks for the response.
I didn't use index since it was a one time execution.
After making the change to a temporary path with 64MB it is working fine.

I have some queries:
I have gone through the document and it says, it is not thread safe.
What if the same database is used in the multiple processes?
Can we have actual database path and this temporary path same?

Thanks a lot.

Thanks and Regards
Deepak

On Wednesday 13 May 2015 10:45 AM, Richard Hipp wrote:
> On 5/12/15, Deepak Hegde  wrote:
>> Hi everyone,
>>
>> I am facing a issue and details are as below:
>>
>> 1) Database table have around 15 table. One of the table have around
>> 66000 entries and this table have about 25 columns.
>> 2) This table with 66000 entry have field called TITLE with TEXT type
>> with table name AUDIO.
>> 3) When I run a query like: SELECT TITLE FROM AUDIO WHERE VALID!=0 ORDER
>> BY TITLE ASC; (here VALID is also a column),
>>   sqlite3_step() is returning error SQLITE_FULL.
> I assume you have no index on AUDIO.TITLE, correct?
>
> What is the result of:
>
>  SELECT sum(length(title)) FROM audio WHERE valid!=0;
>
> I'm guessing that result will be more than 2MB.  Without an index on
> audio.title, SQLite will need to create some temporary files used for
> sorting.  Probably those temporary files are still in your 2MB tempfs
> and are filling it up.
>
> You could create an index on audio.title to avoid the sort.
>
> You might also want to use "PRAGMA temp_store_directory"
> (https://www.sqlite.org/pragma.html#pragma_temp_store_directory) to
> force SQLite to use your larger temporary storage volume.
>
>> 4) Database is created in a tempfs path with 64MB space (This is tmpfs
>> is not a default one it is created only for the database creation).
>>   And default tempfs have 2MB of space.
>> 5) Created database size is  at around 7.5MB.
>>
>> please can anyone help me in understanding how this query works? Is this
>> error is due to no enough cache space for database to prepare the query?
>>
>> Please help me in resolving this problem. Thanks.
>>
>> Thanks and Regards
>> Deepak
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>


-- 
Thanks and Regards
Deepak



[sqlite] Query on Sample SQLite Application

2015-05-13 Thread r...@onsitesoftware.com


Hello,  

We are using Sample Sql Lite Project provided by Telerik App Builder:  

http://docs.telerik.com/platform/appbuilder/sample-apps/sample-sqlite  

We are using sql lite plugin 1.0.1 suggested by the Telerik as it works all 
fine on the ANDROID AND Apple. Following scenario compiles our problem. We 
have basic crud operation expect that we do not update.  

Our each records amounts about approx 1.10kb 1 rec = 1.10Kb.  

We Inserted 10 rec which worked all fine on Android and Apple , we were 
able to read and delete  

We Inserted 1000 rec, where we were able to insert all records, but not 
able to fetch it. To fetch the record,we had to exit out the application 
and restart it. This was same for apple and android  

After that we tried 4000 rec where Android application crashed while apple 
application we had to exit out and relaunch app to read and delete  

On 5000 rec it crashed for both android and Apple.  

I have attached the complete project with this Query. We are using Cordova 
3.7 and SQL Lite 1.0.1 .  

Appreciate your feedback on this.  





Thanks,  

Raj 





[sqlite] Multiple connections and page cache(s) reusability

2015-05-13 Thread Milan Kříž
Thanks for the reply. Therefore my expectation that the approach 3 should be 
the best is correct, isn't it?
I mean, using a dedicated connection for each table should cause better 
reusablility of cached pages?
Or what approach would you propose to get the best performance?

Does SQLite have any official (or internal) performance tests related to 
multiple connections or SQLite cache 
mechanism?
Is it possible that managing of multiple connections can cause slowdown in 
SQLite itself?

Regards,
Milan



[sqlite] Multiple connections and page cache(s) reusability

2015-05-13 Thread Clemens Ladisch
Milan K??? wrote:
> My understanding was that each connection has its own page cache.

This is correct.

> [...] Does SQLite have yet some other global cache?

No.  That other global cache is part of the OS.

It might be possible that opening a file two times changes the OS's
caching algorithm.


Regards,
Clemens


[sqlite] sqlite3: sqlite3_step() return SQLITE_FULL error.

2015-05-13 Thread Deepak Hegde
Hi everyone,

I am facing a issue and details are as below:

1) Database table have around 15 table. One of the table have around 
66000 entries and this table have about 25 columns.
2) This table with 66000 entry have field called TITLE with TEXT type 
with table name AUDIO.
3) When I run a query like: SELECT TITLE FROM AUDIO WHERE VALID!=0 ORDER 
BY TITLE ASC; (here VALID is also a column),
 sqlite3_step() is returning error SQLITE_FULL.
4) Database is created in a tempfs path with 64MB space (This is tmpfs 
is not a default one it is created only for the database creation).
 And default tempfs have 2MB of space.
5) Created database size is  at around 7.5MB.

please can anyone help me in understanding how this query works? Is this 
error is due to no enough cache space for database to prepare the query?

Please help me in resolving this problem. Thanks.

Thanks and Regards
Deepak


[sqlite] Tests regarding custom build of SQLite

2015-05-13 Thread Richard Hipp
On 5/13/15, Sairam Gaddam  wrote:
> In order to run .test files(sqlite test codes) in SQLite, I used
>
>  ./testfixture NAMEOFFILE.test
>
> Can anyone kindly tell which build of sqlite will TESTFIXTURE access?
>
> Because even when I make modifications to sqlite3.c source code, the output
> of the test file under test has no effect.

testfixture.exe builds from individual source files in the src/
directory, not from the amalgamation file sqlite3.c.  So you'll need
to make your changes to individual source files then incorporate those
changes into sqlite3.c by running "make sqlite3.c" and into
testfixture.exe by running "make test".

>
>
> And one more question is that, In order to test my custom build of
> sqlite3.c,
> I installed both TCL and SQLite, and gave the command ./configure
> After that the source file sqlite3.c file is formed and I replaced the
> original sqlite3.c file with my customized sqlite3.c with the same name.
> But when I ran make test command, the program again replaces my customized
> sqlite3.c with the original sqlite3.c source file and tests it. So I am not
> able to run tests on my modified file. So can anyone tell why it replaces
> with original file and what to do in order to test my custom build.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org


[sqlite] sqlite3: sqlite3_step() return SQLITE_FULL error.

2015-05-13 Thread Richard Hipp
On 5/13/15, Deepak Hegde  wrote:
> I have gone through the document and it says, it is not thread safe.
> What if the same database is used in the multiple processes?
> Can we have actual database path and this temporary path same?
>

process!=thread.  It is perfectly safe to run PRAGMA
temp_store_directory and point it to the same directory on multiple
processes.  It is perfectly safe to have temp_store_directory and the
main database directory be the same.
-- 
D. Richard Hipp
drh at sqlite.org


[sqlite] emptying tables

2015-05-13 Thread Zaumseil René
>You turn off auto_vacuum but do a manual VACUUM once you've deleted all the 
>data from /all/ the tables.  It's faster >before VACUUM takes time roughly 
>proportional to the amount of data left in the database.
>
>Simon.


Thank you for the hint.
Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder 
gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist 
(sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese 
Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese 
unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu 
benachrichtigen. Besten Dank.


[sqlite] emptying tables

2015-05-13 Thread Zaumseil René
>Are you running all your delete statements within a single transaction, or a 
>separate transaction for each?

A single transaction as written before in this thread.


Rene

Kernkraftwerk Goesgen-Daeniken AG
CH-4658 Daeniken, Switzerland

Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder 
gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist 
(sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese 
Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese 
unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu 
benachrichtigen. Besten Dank.


[sqlite] System.Data.SQLite, Virtual Tables, and ThreadAbortException Issues

2015-05-13 Thread Mike Nicolino
Hey Everyone,

For reference I'm using version 1.0.95.0 of System.Data.SQLite.

I've got System.Data.SQLite embedded in our cloud web service using virtual 
table modules to access our various data sources.  Our IIS is configured to 
abort requests that exceed a maximum time threshold (required for among other 
things to prevent a malicious user from sucking up web resources by spawning 
repeated long running requests).  The effect is IIS will abort threads that 
exceed the time threshold.  I'm seeing the GC finalizer get stuck trying to 
finalize an object in SQLite.Interop, blocking on a critical section (which 
eventually leads to memory exhaustion).  After a lot of digging, I believe the 
situation is as follows:


-  Request for a long running query via SQLite starts

-  SQLite 'step' is called, drops into unmanaged code, enters a 
critical section, then calls back to managed code for virtual table processing

-  IIS aborts the thread causing a ThreadAbortException in managed code 
(Virtual table processing)

-  Stack starts unrolling due to the ThreadAbortException, which 
prevents the unmanaged code from releasing the critical section

-  Finalizer gets stuck trying to acquire the critical section when the 
underlying System.Data.SQLite object(s) are getting finalized.

The issue is isolated to ThreadAbortException since you can't catch it and 
prevent it from being re-thrown.  I did find a 'fix' for a similar issue in 
SystemData.SQLite back on 10/11/2012:


https://system.data.sqlite.org/index.html/fdiff?v1=3994ed2958c14a11&v2=d05529e749a4f10b&sbs=1

I don't think the 'step' code is protected in the manner of the above fix 
(likely unneeded at the time, since virtual tables were not supported and there 
wasn't a case of 'step' dropping into unmanaged code and then back to managed 
code).

Questions:

-  Have I evaluated this issue correctly or is there potentially 
something wrong with my integration?

-  Presuming this is a bug, does anyone have a workaround suggestion?  
I'm somewhat hesitant to change the 'step' source in System.Data.SQLite to use 
the same approach as the 10/11/2012 fix, worrying about other potential side 
effects.

Thanks,
MikeN