Re: [sqlite] Encrypted database

On 2014-12-15 13:17, Ulrich Telle wrote: No, at least the *original* component System.Data.SQLite, available from https://system.data.sqlite.org uses its own encryption implementation based on a 128-bit RSA cipher (using the Windows Crypto API), while SQLCipher uses a 256-bit AES cipher

Re: [sqlite] recursive update

That should be: with mt(a, b) as (select i, j from ...) update tab set x = (select a from mt where b = y) where y in (select b from mt); mt is a table/view (albeit a temporary one), so the update statement is no different than if you were updating the contents of one table from the

Re: [sqlite] recursive update

with mt(a, b) as (select i, j from ...) update tab set x = (select a from mt where b = y) where y in (select y from mt); --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing

[sqlite] System.Data.Sqlite Virtual Table Example?

Does anyone by chance know of an example of implementing virtual tables with System.Data.Sqlite? The help docs are decent, but seeing an example implementation would be quite helpful to tie it all together. Thanks! ___ sqlite-users mailing list

[sqlite] recursive update

Hi, I need to implement a recursive update for some reason. So I'm trying to start to implement a stupid with-clause update and that stops me immediately. The update does not recognize the table or columns of the with clause table: with mt(a, b) as ( select i, j from ... ) update tab

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

> > > > > > The memory is being used by the statement journal, which you have in > > > memory. If the app did not set "journal_mode=memory" and > > > "temp_store=memory", SQLite would create a really large temp file > > > instead of using memory. Which would still be sub-optimal, but might >

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Hi Dan. > On 12/15/2014 11:59 PM, Dan Kennedy wrote: > > On 12/15/2014 11:11 PM, Paul wrote: > >> Hello, dear developers > >> > >> Recently I've stumbled upon a very rare and strange bug. > >> The result of this is abnormal memory usage, that does not allow us > >> to remove > >> fair number of

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Hello Richard. > On Mon, Dec 15, 2014 at 11:11 AM, Paul wrote: > > > > Hello, dear developers > > > > Recently I've stumbled upon a very rare and strange bug. > > The result of this is abnormal memory usage, that does not allow us to > > remove > > fair number of rows from a table

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

On 12/15/2014 11:59 PM, Dan Kennedy wrote: On 12/15/2014 11:11 PM, Paul wrote: Hello, dear developers Recently I've stumbled upon a very rare and strange bug. The result of this is abnormal memory usage, that does not allow us to remove fair number of rows from a table due to the limit of

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

On 12/15/2014 11:11 PM, Paul wrote: Hello, dear developers Recently I've stumbled upon a very rare and strange bug. The result of this is abnormal memory usage, that does not allow us to remove fair number of rows from a table due to the limit of memory, available for 32bit process. This is

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

On Mon, Dec 15, 2014 at 11:11 AM, Paul wrote: > > Hello, dear developers > > Recently I've stumbled upon a very rare and strange bug. > The result of this is abnormal memory usage, that does not allow us to > remove > fair number of rows from a table due to the limit of memory,

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

- Original Message - From: "Nico Williams" To: "General Discussion of SQLite Database" Sent: Monday, December 15, 2014 5:16 PM Subject: Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Sorry, I've forgot to mention my sqlite version, here it is: 3.8.7.1 2014-10-29 13:59:56 3b7b72c4685aa5cf5e675c2c47ebec10d9704221 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

On Mon, Dec 15, 2014 at 06:23:31PM +0700, Dan Kennedy wrote: > It's tricky. As you say, xBestIndex() will currently be invoked > twice - once with no constraints usable and once with both "b.id=?" > and "b.linkid=?" usable. I guess the reason it is not invoked in the > other ways you suggest is

[sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

Hello, dear developers Recently I've stumbled upon a very rare and strange bug. The result of this is abnormal memory usage, that does not allow us to remove fair number of rows from a table due to the limit of memory, available for 32bit process. This is strange, because database size is

Re: [sqlite] Encrypted database

Of the various SQLite management tools available, I have encountered only one that supports the SQLite.Net encryption: SQLite2009 Pro (http://sqlite2009pro.azurewebsites.net/). It's linked from the now obsolete CVSTrac tools page (https://www.sqlite.org/cvstrac/wiki?p=ManagementTools). It's a

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

- Original Message - From: "Hick Gunter" To: "'General Discussion of SQLite Database'" Sent: Monday, December 15, 2014 2:40 PM Subject: Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

I would concur in that SQLite is asking "which subset of the given constraints yields the most efficient access". The possible query plans are 1) A() -> B(ID) -> C(LINKID) 2) C() -> B(LINKID) -> A(ID) 3) B() -> A(ID) + C(LINKID) or B() -> C(LINKID) + A(ID) 4) A() -> C() -> B(ID,LINKID) or

Re: [sqlite] Encrypted database

Am 15.12.2014 11:37, schrieb jus...@postgresql.org: On 2014-12-13 19:29, Mujtaba Ali Panjwani wrote: I have created an encrypted database using visual studio plugin of system.data.sqlite. Now whichever other database management software than VS, I try to open that database is failing. Can any

Re: [sqlite] Encrypted database

Thanks for the reply. Yes, I have access to those systems but I am not much acquainted with those systems so I wont have any use for it. Anyways thanks. Regards, Mujtaba Panjwani -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

On 12/15/2014 13:23 PM, Dan Kennedy wrote: > On 12/12/2014 09:22 PM, Josef Kučera wrote: > > Hello, > > I am trying to use SQLite's marvellous Virtual Table mechanism as a SQL > > layer for querying an in memory storage. This works good, but I have a > > problem with more complex queries. When

Re: [sqlite] Query Planner for Virtual Tables: link table evaluation & transitive property of constraints not used

On 12/12/2014 09:22 PM, Josef Kučera wrote: Hello, I am trying to use SQLite's marvellous Virtual Table mechanism as a SQL layer for querying an in memory storage. This works good, but I have a problem with more complex queries. When querying a real SQLite database it correctly moves the

Re: [sqlite] Encrypted database

On 2014-12-13 19:29, Mujtaba Ali Panjwani wrote: I have created an encrypted database using visual studio plugin of system.data.sqlite. Now whichever other database management software than VS, I try to open that database is failing. Can any of you please help me with issue. I suspect this is

Re: [sqlite] '.timer on' in the shell tool

Units are "CPU Seconds". "user" time is spent within user code, i.e. SQLite, "sys" time is spent within system calls, i.e. reading/writing files. The balance between the times depends on various parameters, including the state of the disc cache and the complexity of your INSERT...SELECT

Re: [sqlite] '.timer on' in the shell tool

Hi, Simon. > Question 1: What are the units ? > > Question 2: I would have expected consistency in that user time was always > greater than system time. Or perhaps the other way around. Why is a different > one greater for the two examples ? > System time may be much greater if it involves a

Re: [sqlite] '.timer on' in the shell tool

On Mon, Dec 15, 2014 at 10:24 AM, Simon Slavin wrote: > > On 15 Dec 2014, at 9:20am, Donald Shepherd > wrote: > > - Units are seconds. > > - IIRC user time is time spent in SQLite code, sys time is time spent in > > system (OS) calls. Both can

Re: [sqlite] '.timer on' in the shell tool

On 15 Dec 2014, at 9:20am, Donald Shepherd wrote: > - Units are seconds. > - IIRC user time is time spent in SQLite code, sys time is time spent in > system (OS) calls. Both can vary from run to run and (at least in my > testing) sys time tends to vary based off

Re: [sqlite] '.timer on' in the shell tool

Let's see if I remember my notes from work ok at home: - Units are seconds. - IIRC user time is time spent in SQLite code, sys time is time spent in system (OS) calls. Both can vary from run to run and (at least in my testing) sys time tends to vary based off system usage. If you want the best

[sqlite] '.timer on' in the shell tool

Okay. I used '.timer on' in the shell tool. SQLite 3.7.13, if it matters. Here are two sample lines I got in response to different INSERT ... SELECT commands: CPU Time: user 880.710398 sys 353.260288 CPU Time: user 5073.001124 sys 11609.266484 The two commands were issued one after another

Re: [sqlite] Feature Request - RowCount

> > On 14 Dec 2014, at 11:08am, Jean-Christophe Deschamps > wrote: > > > Without using slow triggers or changing the v3 file format there is still > > another possibility which could be implemented relatively easily. All it > > would need is a new pragma (or internal