Re: [sqlite] Problem with System.Data.SQLite version 1.0.84 for CompactFramework

[Joe Mistachkin]

Christian Morlotte wrote:
> 
> Would it be possible those Compact Framework binaries have some kind of
> error? Maybe they were developed for 64 bit systems? Is there another
> version I could use?
> 

The files in the "sqlite-netFx35-binary-PocketPC-2008-1.0.84.0.zip" download
package are intended to be used with the .NET Compact Framework 3.5 running
on an ARM processor.

--
Joe Mistachkin

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

Re: [sqlite] Slow sqlite3_open() - possible culprits?

[Mohit Sindhwani]

Hi Richard,

On 14/3/2013 8:17 PM, Richard Hipp wrote:

On Thu, Mar 14, 2013 at 12:34 AM, Mohit Sindhwani  wrote:


Hi, we are using SQLite3 + CEROD for a number of databases in an embedded
systems application running on Windows CE.  We're finding unexpectedly long
time to open the database (0.5s ~ 2.8sec).  Maybe, these times are
reasonable, but they seem long to us.



Did you read this thread:

 http://www.mail-archive.com/sqlite-users%40sqlite.org/msg75761.html


I did read this thread and I thought that it may not be related since we 
are on Windows CE with FAT, we're using CEROD (so the database is 
implicitly meant for Read Only operations) and we're opening the 
database with SQLITE_READ_ONLY specified in the _open() API.  I'll try 
to check the permissions and attributes again to be sure.


Best Regards,
Mohit.


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

Re: [sqlite] Query optimizer suboptimal planning with virtual tables

[Clemens Ladisch]

Eleytherios Stamatogiannakis wrote:
> right now our VTs do not implement xBestIndex at all

The documentation says:
| The xBestIndex method is required for every virtual table implementation.

If you had no xBestIndex implementation, SQLite would crash.

> so i guess SQLite uses a default value of 0.

I'd guess your xBestIndex function pretends to succeed, but
leaves all the output fields with zero values.

This makes the query planner think that all your indexes have
a cost of zero, which makes it choose some random one.

You *must* return correct information from xBestIndex.


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

Re: [sqlite] Copying an SQLite database

[kyan]

On Thu, Mar 14, 2013 at 4:28 PM, J Trahair
 wrote:
> There will be times when the program running the SQLite database will copy 
> the database file MySQLiteDatabase.db to another folder, where the copy will 
> be zipped (compressed). What happens if another user (User B) is writing a 
> record to the db at the same moment that User A's program is copying it to 
> the other folder. Glitches, corruption...or perfect integrity?

If the program cannot ensure that there are no open connections to the
database file for the duration of the file copy then you should use
the SQLite backup api to copy the db file instead of a plain file
copy. The result is guaranteed to be consistent. For instance if an
update is made to the db during the backup process then the process
will be restarted transparently.

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

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

[sqlite] Lock mechanisme will not work

[peter Ketel]

Dear all,

It's impossible to control the way SQLite locks the database. Read
uncommitted will result in a crash when I try to open the database with a
C# program. I use Mono version 3, Ubuntu version 12.04 LTS, Ubuntu 12.10,
Windows 7 and Windows XP. On all systems read uncommitted WILL FAIL I've
Included the Class I use to connect.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with System.Data.SQLite version 1.0.84 for Compact Framework

[Christian Morlotte]

I'm testing a connection in a project targeting the Compact Framework and I
get exceptions that seem to point to a 32 bit vs 64 bit problem. I
downloaded these binaries:

sqlite-netFx35-binary-PocketPC-2008-1.0.84.0.zip

and I am using .NET 3.5 and a 32 bit Vista system. After failing for days I
used the "regular" Windows version. This:

sqlite-netFx35-binary-Win32-2008-1.0.84.0.zip

and my project ran on the first try, without changing anything. Well,
except the dlls of course.

I'm using C# by the way, whose vague exceptions have been really helpful in
figuring out what's wrong. (Sorry for the rant, C# aficionados)

Would it be possible those Compact Framework binaries have some kind of
error? Maybe they were developed for 64 bit systems? Is there another
version I could use?

The exceptions I get are:

System.BadImageFormatException: An attempt was made to load a program with
an incorrect format. (I looked around for this and read it had to do with
the 32 bit vs 64 bit issue)

And sometimes:

Can't load file SQLite3.cs under c:\dev\sqlite\dotnet\System.Data.SQLite.
(this path is not in my system, and I don't know what or where SQLite3.cs
is. )

I've looked around on google and forums without any luck, and I'm still
waiting for the email that lets me activate my account in the SQLite forums
(I registered 2 days ago, with two separate mail accounts), so please, if
anyone has any information at all that can help with this issue, please let
me known. Maybe another version? Another distribution? Even a different
database manager.

  Thanks a lot for your help,


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

[sqlite] Copying an SQLite database

[J Trahair]

Hi everyone

There will be times when the program running the SQLite database will copy the 
database file MySQLiteDatabase.db to another folder, where the copy will be 
zipped (compressed). What happens if another user (User B) is writing a record 
to the db at the same moment that User A's program is copying it to the other 
folder. Glitches, corruption...or perfect integrity?

Thanks

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

Re: [sqlite] Memory DB - Query does not return all records after Update

[mike.akers]

Thank You for the help everyone.

The problem was in my query. I have a trigger that updates the a
"last_modified" field anytime there is an update on a given record. My query
has a where clause that says ... last_modified < current_timestamp. So if
there were any updates that occur during the same 1s interval as the query,
they were dropped. The issue was  that I did not understand/relalize that
the precision on the timestamps were 1s.

Once again, thank you for all the help,
Mike



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Memory-DB-Query-does-not-return-all-records-after-Update-tp67267p67717.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer suboptimal planning with virtual tables

[Hick Gunter]

SQLite uses a cost-based algorithm to formulate a query plan. Therefore you 
must return a proper cost estimate in your virtual tables' xBestIndex function. 
This is usually interpreted as similar to "how many (disk) accesses are 
required" or "how many records will be returned". For your virtual table these 
numbers would be 0 (but you should probably return 1) and 100 respectively.

The available query plans are

a) FTS real table, FTS virtual table

b) FTS virtual table, FTS real table

c) Build index on real table, FTS virtual table, access covering index

If you return 0, your estimates will be

a) 100 *  0  = 0
b)   0 * 10  = 0
c) 100 * log(100) +   0 * 10 =   600

So SQLite will choose either (a) or (b).

If you return 1, your estimates will be

a) 100 *   1 =   100
b)   1 * 100 =   100
c) 100 * log(100) +   1 * 10 =   610

If you return 100, your estimates will be

a) 100 * 100 = 1
b) 100 * 100 = 1
c) 100 * log(100) + 100 * 10 =   6001000

So to make this work you need to return the number of rows...

OTOH you can always force SQLite to use the query plan suggested by the order 
of joins using CROSS JOIN

Gunter

-Ursprüngliche Nachricht-
Von: Eleytherios Stamatogiannakis [mailto:est...@gmail.com]
Gesendet: Donnerstag, 14. März 2013 15:38
An: sqlite-users@sqlite.org
Betreff: [sqlite] Query optimizer suboptimal planning with virtual tables

Hi,

I have came across a glitch with how SQLite's query optimizer plans virtual 
tables. Example follows:

I have a virtual table function named "range" that produces all numbers from 1 
to range's arg. This virtual table does not have any index functionality.

With this i'll create the virtual table instance "t1":

 > create virtual table t1 using range('100');  > select * from t1;
1
2
...
99
100
--Column names--
C1

Let's create a real table now:

 > create table t2 as select * from t1;

The plan that the optimizer will produce when i join these two tables is
this:

 > explain query plan select * from t1, t2 where t1.c1=t2.c1; 0 |0 |1 | SCAN 
 > TABLE t2 (~100 rows) 0 |1 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 
 > rows)

Even thought, i have put VT t1 first in the join list, SQLite will do a nested 
loop join (putting it on the right).

Wouldn't it had made more sense for SQLite to create an automatic index on the 
real table t2 and do the join as such?

0 |0 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows) 0 |1 |1 | SEARCH TABLE 
t2 USING AUTOMATIC COVERING INDEX idx (C1=?) (~10
rows)

Putting the VT on the right by default, doesn't make much sense to me, since it 
cannot create an automatic index on it.

In general it seems to me to be a better default to always have the non 
automatic indexable SQLite entities (views, virtual tables) on the left of the 
joins and what can be automatically indexed on the right of the joins.

Also, i think, that it would be even better if SQLite had the ability to scan 
the virtual table and build a temporary automatic covering index on it to do 
the join (why isn't this case allowed?).

Thank you,

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


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

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users