Re: [sqlite] EF6 + System.Data.Sqlite SaveChanges() not working?

[Joe Mistachkin]

Nick Zhu wrote:
>
> So I just started using System.Data.Sqlite with entity framework 6
> (downloaded the latest System.Data.Sqlite from Nuget, version 1.0.91.0)
> After some configuration and code, I found out that I can read from the
> database but somehow write is not working.
>

This might be a problem with the configuration file.  There was an issue
with the NuGet configuration file transforms that is now fixed in package
version 1.0.91.1.  Could you try the new version and see if that clears
the issue you are seeing?

--
Joe Mistachkin

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

Re: [sqlite] Loadable extensions

[Peter Haworth]

Thanks SImon.  I found a few others by searching the web.  I see reference
to extensions within the SQLite source tree too but unfortunately I know
nothing about how to find them there.

On Tue, Feb 18, 2014 at 9:00 AM,  wrote:

> Message: 12
> Date: Mon, 17 Feb 2014 21:05:54 +
> From: Simon Slavin 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] Loadable extensions
> Message-ID: 
> Content-Type: text/plain; charset=us-ascii
>
>
> On 17 Feb 2014, at 6:33pm, Peter Haworth  wrote:
>
> > Is there a list of available loadable extensions for functions, virtual
> > tables, etc?
>
> I've seen some extensions lists on the web but none of them struck me as
> worth bookmarking.
>
> The SQLite site includes a page of contributions, which contains some
> extensions:
>
> 
>
> Look especially at the last entry.  Some entries on that page are
> obsolete, attempts at making up for things which have since been built into
> SQLite or things that don't work with up-to-date compilers.
>
> Simon.
>



Pete
lcSQL Software 
Home of lcStackBrowser  and
SQLiteAdmin 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXPLAIN output; profiling index usage; LMDB backend

[Clemens Ladisch]

Dave Baggett wrote:
> EXPLAIN output is rather hard to understand. Is there a way to produce 
> annotated output that's more human-readable?

Recompile it with SQLITE_ENABLE_EXPLAIN_COMMENTS.


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

Re: [sqlite] Problem related to ticket 4c86b126f2 ?

[Richard Hipp]

On Mon, Feb 17, 2014 at 10:09 PM, Jean-Marie CUAZ  wrote:

> Hello,
>
> Win XP + Tcl 8.6.1 + SQLite 3.8.3 (loaded from Active State)
>
> Possible usefull information on bug similar as 4c86b126f2 but in a simpler
> context (corrected by version SQLite 3.8.3.1)
>
> Description :
>
> For a table Tbl1 having historical data with 12 occurences (one for each
> month) of the value 'b' in the field2,  the following UPDATE modified _only
> 1 record_ : the last occurence (december) of 'b' in field2.
>
> "UPDATE Tbl1
> SET field1 = 'a'
> WHERE field2 = 'b'"
>
> Field2 has string values in every rows and no NULL in it, and is the third
> member of the PK. Tb1 has 39 fields, a PK on the first three fields . Apart
> the PK, no constraint are set on the other fields.
>


Please show the complete schema, including all indices, for table tbl1.  We
are unable to recreate your problem without additional information.



>
> Re-ran the same same query on the same table with older versions of SQLite
> (from AS) :
>
> version 3.8.0.1 -> OK
> version 3.8.1 -> OK
> version 3.8.3 -> NOT OK
> (unfortunately 3.8.3.1 is not available for now from AS)
>
> With version 3.8.3, we ran scripts creating test tables and reproducing
> the same logic in the same db but nothing went wrong.
>
> Our App has a kind of "Sql console" : exercising the original populated
> table with manual UPDATES we observed that the miss-match with the RHS
> criteria in the WHERE clause arose only on one particular field ("field2")
> and not the others. With this field the sole match was allways on the last
> occurence in the table.
>
> Reading ticket 4c86b126f2, changed the WHERE clause -> "WHERE + field2 =
> 'b'" -> works : criteria recognised on every rows -> every rows were updated
>
> Then ran ANALYZE command, wich was not run for more than 6 months on this
> db and re-ran exactly the same UPDATE queries on the console -> the pb
> disapeared (!)
>
> Jean-Marie
> ___
> 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

Re: [sqlite] EXPLAIN output; profiling index usage; LMDB backend

[Hick Gunter]

I use EXPLAIN and EXPLAIN QUERY PLAN as comments for each other. Selects 
requiring coroutines do tend to make it hard to read (but VDBE code is 
optimized to be fast to execute)

-Ursprüngliche Nachricht-
Von: Dave Baggett [mailto:d...@baggett.org]
Gesendet: Montag, 17. Februar 2014 18:00
An: sqlite-users@sqlite.org
Betreff: [sqlite] EXPLAIN output; profiling index usage; LMDB backend

I've been using SQLite very heavily for about a year now via Python and Roger 
Binns' APSW and have a few questions/suggestions about this incredibly awesome 
piece of code. Please take these in the positive spirit intended.

1) EXPLAIN output is rather hard to understand. Is there a way to produce 
annotated output that's more human-readable? I see that each opcode is 
extensively documented in the source; perhaps if the EXPLAIN output could be 
enhanced to include some of the commentary, or a higher-level syntax rather 
than VBDE opcodes?

2) Likewise, EXPLAIN QUERY PLAN could be annotated further. For example, 
"1|0|0|USE TEMP B-TREE FOR ORDER BY" could tell me what's being ordered; hints 
about what the relevant tables/columns are.

3) The profiling capabilities offered via xProfile are incredibly helpful for 
finding hot spots. One thing I've done that makes this even more useful is to 
"genericize" the SQL query text by replacing numbers, dates, etc. with 
placeholder symbols (e.g,. #) and then aggregating the results to produce a top 
100 list by time used. Genericizing is simple pattern matching, and helps 
coalesce queries that would otherwise appear to be unrelated by simple identity.

4) Re: profiling, it would be great to know which indices are actually being 
used so that one could prune useless ones. Furthermore, it would be extremely 
valuable to know which indices are most expensive to maintain — i.e., which 
cause writing to become disproportionately expensive. Likewise, expensive 
TRIGGERS would be nice to know about, independently of the SQL queries that 
triggered them.

5) I've seeen that Howard Chu ported SQLite 3.7 to his LMDB B-Tree 
implementation and saw performance improvements. Is there a reason this isn't 
being mainlined? I assume there are trade-offs involved? For purely read-only 
databases, it would be nice to be able to select the LMDB back-end. (I realize 
making this switchable is a big undertaking; it's just a suggestion.)

6) Anecdotally, I've found it difficult to make sense of all the tuning 
parameters and achieve good performance. The settings required seem rather 
different for Windows than OS X, and for a while I was using fullfsync under OS 
X — thinking it was the recommended safe option — only to find a comment from 
DRH that it's not recommended, and not even used by Apple (who asked for it). 
And, indeed, without it writes are 10x faster! I guess my suggestion would be 
to update the various tuning documents to reflect the current state of things — 
even it's something along the lines of "top 10 tuning suggestions for {Windows, 
OSX, Android, iOS, Ubuntu}" or something like that.


7) I find it very useful to keep SQLite's heap separated from the Python heap 
so I can see who's using memory. I've patched my SQLite source to allow forcing 
this behavior even when there are multiple cores, and it seems to work fine. I 
suggest making this a mainlined compile-time option; it's a trivial patch. I 
only do this under OS X, because that's my primary development platform; I 
don't know if other platforms let you tag heaps the way OS X does.

8) The virtual table mechanism is incredibly powerful; I've used it to speed up 
performance-critical operations immensely. (This is a shout-out rather than a 
suggestion.)

Thanks for an amazing piece of software.

Dave

___
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

Re: [sqlite] Explain Query Plan with virtual tables involved

[Hick Gunter]

The idxStr parameter is really just a (void *) that SQLite retrieves from the 
answer of your xBestIndex function and gets passed on to your xFilter function. 
SQLite does not care what you return there, as long as you correctly set the 
"need to free" flag to reflect if memory was allocated to build the "string" 
and SQLite needs to call sqlite3_free() on the pointer to avoid a memory leak.

Similarly the idxNum parameter is just a (int). With my CTree based virtual 
tables, I use idxNum == 0 to indicate "physical order" (which is very much 
faster for full table scans than any index order on some machines).

Unfortunately there is no way for the virtual table to determine in advance 
which (if any; maybe it is just a count() query) fields will be retrieved via 
the xColumn function (although a field being mentioned in the call to 
xBestIndex may be a strong hint - unless you promise to ensure the value is 
within the range requested via the "omit" flag), nor is there any method of 
specifying to SQLite which fields are "covered" by any index.

All you can do is reply truthfully to SQLite about the cost (=cardinality) of 
retrieving the (full) rows given the useable constraints.

The result of "explain query plan" is not part of the SQLite specification, 
subject to change without notice and should not be relied upon in applications 
(all stated on the SQLite website).


-Ursprüngliche Nachricht-
Von: Dominique Devienne [mailto:ddevie...@gmail.com]
Gesendet: Montag, 17. Februar 2014 11:41
An: General Discussion of SQLite Database
Betreff: [sqlite] Explain Query Plan with virtual tables involved

Would it be possible to have plans for virtual tables more in line with plans 
for regular tables?

With virtual tables, the plan is always a SCAN TABLE, while for regular tables 
it's either a SCAN TABLE for full scans, and a SEARCH TABLE for an indexed plan.

Because I don't yet use idxStr but only idxNum in sqlite3_index_info, my plans 
look like:

SCAN TABLE folder_member VIRTUAL TABLE INDEX 16:
SCAN TABLE folder_member VIRTUAL TABLE INDEX -1:

(16 is the by-folder non unique index, -1 is the full-scan index).

Even if I used idxStr, it will still be a SCAN TABLE, there's no way to tell 
SQLite that a specific index is the "full scan" index of the vtable, to have 
the plan use SCAN TABLE only for this one, and SEARCH TABLE for the other 
cursors/indexes, and this despite the fact that I use an astronomical 
estimatedCost (std::numeric_limits::max()).

Could we add either a convention on idxNum to indicate this is a full scan, or 
a new field in sqlite3_index_info to explicitly indicate it?

Also, is SQLite itself building idxStr such that it looks like "nkey (guid=? 
AND folder=?)", i.e. I need to build such a string myself, to have my 
vtable-using plans resemble the "native" SQLite table plans?

Finally, all my indexes are basically COVERING indexes, i.e. there's no 
separate lookup of the row, given the rowid, so how could I similarly 
communicate this fact to SQLite, such that it prints it like for its native 
plans?

My hope is that in the future, all plans look the same, except for an 
additional VIRTUAL keyword between USING and INDEX, and possibly the idxNum 
perhaps.

Any chance this might happen?

If not, and it's not a question of dev time, why would the above goal not be 
considered?

Thanks, --DD

C:\Users\DDevienne>sqlite3
SQLite version 3.8.3.1 2014-02-11 14:52:19 Enter ".help" for instructions Enter 
SQL statements terminated with a ";"
sqlite> create table folder_member (guid blob, folder blob, folder_type
sqlite> text); create unique index nkey on folder_member(guid, folder);
sqlite> create index by_guid on folder_member(guid); create index
sqlite> by_folder_type on folder_member(folder_type); create index
sqlite> by_folder on folder_member(folder); explain query plan select *
sqlite> from folder_member where guid='ab'
and folder='bc';
0|0|0|SEARCH TABLE folder_member USING INDEX nkey (guid=? AND folder=?)
sqlite> explain query plan select * from folder_member where
sqlite> folder_type='foo';
0|0|0|SEARCH TABLE folder_member USING INDEX by_folder_type
0|0|0|(folder_type=?)
sqlite> explain query plan select * from folder_member;
0|0|0|SCAN TABLE folder_member
sqlite>
___
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 

[sqlite] EF6 + System.Data.Sqlite SaveChanges() not working?

[Nick Zhu]

Hi guys,
So I just started using System.Data.Sqlite with entity framework 6
(downloaded the latest System.Data.Sqlite from Nuget, version 1.0.91.0)
After some configuration and code, I found out that I can read from the
database but somehow write is not working.

Here's my code:
using (var context = new InternalDbContext())
{
var demo = context.DemoEntities.Where(d => d.ID ==
1).FirstOrDefault();
demo.Name = "TestTest";
context.DemoEntities.Add(new Demo { Name = "Test" });
context.SaveChanges();
}

Basically after SaveChanges, nothing was updated in the DB. However I can
read fro the DB with the data I manually populated via SQlite admin tool.

Here's my DB schema:
Table name :Demo
Field: ID - Integer Primary Key AutoIncrement
Field: Name - VARCHAR(256)

Here's my classes

public class InternalDbContext : DbContext
{
public DbSet DemoEntities { get; set; }

public InternalDbContext()
{
// Turn off the Migrations, (NOT a code first Db)
Database.SetInitializer(null);
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// Database does not pluralize table names

modelBuilder.Conventions.Remove();
}
}

[Table("Demo")]
public class Demo
{
public long ID { get; set; }
public string Name { get; set; }
}

App.config


  

  



  
  

  


  

  
  

  


  
  


  

  
  

  

  


  
  

  
  

  
  

  
  

  


If anyone could point me to the right direction, that'd be fantastic,
thanks so much

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

[sqlite] Problem related to ticket 4c86b126f2 ?

[Jean-Marie CUAZ]

Hello,

Win XP + Tcl 8.6.1 + SQLite 3.8.3 (loaded from Active State)

Possible usefull information on bug similar as 4c86b126f2 but in a 
simpler context (corrected by version SQLite 3.8.3.1)


Description :

For a table Tbl1 having historical data with 12 occurences (one for each 
month) of the value 'b' in the field2,  the following UPDATE modified 
_only 1 record_ : the last occurence (december) of 'b' in field2.


"UPDATE Tbl1
SET field1 = 'a'
WHERE field2 = 'b'"

Field2 has string values in every rows and no NULL in it, and is the 
third member of the PK. Tb1 has 39 fields, a PK on the first three 
fields . Apart the PK, no constraint are set on the other fields.


Re-ran the same same query on the same table with older versions of 
SQLite (from AS) :


version 3.8.0.1 -> OK
version 3.8.1 -> OK
version 3.8.3 -> NOT OK
(unfortunately 3.8.3.1 is not available for now from AS)

With version 3.8.3, we ran scripts creating test tables and reproducing 
the same logic in the same db but nothing went wrong.


Our App has a kind of "Sql console" : exercising the original populated 
table with manual UPDATES we observed that the miss-match with the RHS 
criteria in the WHERE clause arose only on one particular field 
("field2") and not the others. With this field the sole match was 
allways on the last occurence in the table.


Reading ticket 4c86b126f2, changed the WHERE clause -> "WHERE + field2 = 
'b'" -> works : criteria recognised on every rows -> every rows were updated


Then ran ANALYZE command, wich was not run for more than 6 months on 
this db and re-ran exactly the same UPDATE queries on the console -> the 
pb disapeared (!)


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

[sqlite] EXPLAIN output; profiling index usage; LMDB backend

[Dave Baggett]

I've been using SQLite very heavily for about a year now via Python and Roger 
Binns' APSW and have a few questions/suggestions about this incredibly awesome 
piece of code. Please take these in the positive spirit intended.


1) EXPLAIN output is rather hard to understand. Is there a way to produce 
annotated output that's more human-readable? I see that each opcode is 
extensively documented in the source; perhaps if the EXPLAIN output could be 
enhanced to include some of the commentary, or a higher-level syntax rather 
than VBDE opcodes?


2) Likewise, EXPLAIN QUERY PLAN could be annotated further. For example, 
"1|0|0|USE TEMP B-TREE FOR ORDER BY" could tell me what's being ordered; hints 
about what the relevant tables/columns are.


3) The profiling capabilities offered via xProfile are incredibly helpful for 
finding hot spots. One thing I've done that makes this even more useful is to 
"genericize" the SQL query text by replacing numbers, dates, etc. with 
placeholder symbols (e.g,. #) and then aggregating the results to produce a 
top 100 list by time used. Genericizing is simple pattern matching, and helps 
coalesce queries that would otherwise appear to be unrelated by simple 
identity.


4) Re: profiling, it would be great to know which indices are actually being 
used so that one could prune useless ones. Furthermore, it would be extremely 
valuable to know which indices are most expensive to maintain — i.e., which 
cause writing to become disproportionately expensive. Likewise, expensive 
TRIGGERS would be nice to know about, independently of the SQL queries that 
triggered them.


5) I've seeen that Howard Chu ported SQLite 3.7 to his LMDB B-Tree 
implementation and saw performance improvements. Is there a reason this isn't 
being mainlined? I assume there are trade-offs involved? For purely read-only 
databases, it would be nice to be able to select the LMDB back-end. (I realize 
making this switchable is a big undertaking; it's just a suggestion.)


6) Anecdotally, I've found it difficult to make sense of all the tuning 
parameters and achieve good performance. The settings required seem rather 
different for Windows than OS X, and for a while I was using fullfsync under 
OS X — thinking it was the recommended safe option — only to find a comment 
from DRH that it's not recommended, and not even used by Apple (who asked for 
it). And, indeed, without it writes are 10x faster! I guess my suggestion 
would be to update the various tuning documents to reflect the current state 
of things — even it's something along the lines of "top 10 tuning suggestions 
for {Windows, OSX, Android, iOS, Ubuntu}" or something like that.



7) I find it very useful to keep SQLite's heap separated from the Python heap 
so I can see who's using memory. I've patched my SQLite source to allow 
forcing this behavior even when there are multiple cores, and it seems to work 
fine. I suggest making this a mainlined compile-time option; it's a trivial 
patch. I only do this under OS X, because that's my primary development 
platform; I don't know if other platforms let you tag heaps the way OS X does.


8) The virtual table mechanism is incredibly powerful; I've used it to speed 
up performance-critical operations immensely. (This is a shout-out rather than 
a suggestion.)


Thanks for an amazing piece of software.

Dave

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

Re: [sqlite] Question about how sqlite recovers after a power loss

[Simon Slavin]

On 18 Feb 2014, at 8:13am, Fabrice Triboix  wrote:

> Actually, the journal file does not get deleted when I do a SELECT just after 
> opening the database. 

Depending on your journal mode (I think), the journal file should be deleted, 
but then it will immediately be recreated.  Are you sure that this is not 
what's happening ?

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

Re: [sqlite] Question about how sqlite recovers after a power loss

[Fabrice Triboix]

Hi Richard,

Actually, the journal file does not get deleted when I do a SELECT just after 
opening the database. 

Why is that? 

NB: I do not set/modify any PRAGMA at all. 

Many thanks for your help!

Best regards,

  Fabrice

-Original Message-
From: Richard Hipp 
Sender: drhsql...@gmail.com
Date: Sat, 15 Feb 2014 09:57:03 
To: ; General Discussion of SQLite 
Database
Subject: Re: [sqlite] Question about how sqlite recovers after a power loss

On Sat, Feb 15, 2014 at 9:55 AM, Fabrice Triboix wrote:

> Hi Richard,
>
> All right, many thanks for that. So if I do, say, a SELECT just after
> opening the database, that's when the recovery will actually take place and
> the journal file will be replayed and deleted.
> Is my understanding correct?
>

yes.  "SELECT count(*) FROM sqlite_master" is a good query to run for this.

-- 
D. Richard Hipp
d...@sqlite.org

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