Re: [sqlite] Why can't sqlite disregard unused outer joins?

[David Bicking]

If Uniform has a given EmployeeName twice, you will get the Employee.Name twice 
in this query. Thus it would be a different result than if you did not join 
with Uniform.

David




 From: Charles Samuels 
To: General Discussion of SQLite Database  
Sent: Tuesday, May 29, 2012 8:21 PM
Subject: [sqlite] Why can't sqlite disregard unused outer joins?
 

Suppose you have a query like this:

select Employee.name from Employees left join Uniform on 
    (EmployeeSize.name=Uniform.employeeName)

This query's result should be identical weather or not we have that join; it's 
an outer join, not an inner join, afterall. However, explain query plan (and 
my time measurements) seem to indicate that the the query with the join is far 
slower/more complex.

Is it hypothetically possible that the optimizer could avoid the join? Is 
there a way to get sqlite to do so? Is this a planned feature?

Why do I ask? I have a big view that joins a whole bunch of a tables 
(including with a virtual table), but most queries use only a subset of those 
queries actual data and I'd rather not get hit by that performance penalty.

Since my queries come from the user, I don't want to have them do the joins on 
their own, it'd be tedious.

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

Re: [sqlite] Why can't sqlite disregard unused outer joins?

[Igor Tandetnik]

On 5/29/2012 8:21 PM, Charles Samuels wrote:

Suppose you have a query like this:

select Employee.name from Employees left join Uniform on
(EmployeeSize.name=Uniform.employeeName)


Doesn't look like a valid query to me. What's Employee and EmployeeSize? 
I assume you meant Employees in both places.



This query's result should be identical weather or not we have that join; it's
an outer join, not an inner join, afterall. However, explain query plan (and
my time measurements) seem to indicate that the the query with the join is far
slower/more complex.

Is it hypothetically possible that the optimizer could avoid the join?


In principle, yes.


Is there a way to get sqlite to do so?


Sure. Remove the join from the query.


Is this a planned feature?


Well, it's not a feature, it's a lack thereof. The query planner is not 
specifically looking for this particular optimization opportunity - 
probably because such situations are uncommon and are not worth the time 
looking for. Your question seems to suggest that optimizations magically 
happen by themselves unless someone specifically writes code to block 
them; in reality, it's the other way round.

--
Igor Tandetnik

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

Re: [sqlite] How to write and read the same sqlite3 DB in memory concurrently in two thread?

[Keith Medcalf]

> And that explanation along with why one would want to use each mode would be
> quite helpful to the community I would think.

>From examining the source I see that it the serialization is via the mutex 
>attached to the database connection.  Serialization of statements is achieved 
>by reference to the connection from which is was generated, so the statement 
>"SQLite can be used in a multithreaded program so long as no two threads 
>attempt to use the same database connection *or any statement prepared on that 
>connection*, at the same time" should have the part between the asterisks 
>added to make it perfectly clear which data structure limits multiple entrants.

As to why one would want to use each mode is pretty simple:

THREADSAFE=0 or single-threaded.  If you only have one thread on which all 
database operations will be performed (though you may have additional threads 
doing "other things", just not calling into SQLite).

THREADSAFE=2 or Serialized.  If you have multiple threads calling into SQLite 
*and* you do not want to manage synchronization, then this is the mode you 
should use.  It is also the default.  

THREADSAFE=1 or Free Threading.  You have designed your software so that it is 
impossible for calls into the engine to be made from more than 1 thread per 
database connection.  For example, you store your pointers to the database 
connection and any prepared statements in thread-local storage.

The only advantage of THREADSAFE=1 over THREADSAFE=2 is that you do not incur 
the cost of testing/acquiring/releasing the connection mutex around SQLite 
calls.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

[sqlite] Why can't sqlite disregard unused outer joins?

[Charles Samuels]

Suppose you have a query like this:

select Employee.name from Employees left join Uniform on 
(EmployeeSize.name=Uniform.employeeName)

This query's result should be identical weather or not we have that join; it's 
an outer join, not an inner join, afterall. However, explain query plan (and 
my time measurements) seem to indicate that the the query with the join is far 
slower/more complex.

Is it hypothetically possible that the optimizer could avoid the join? Is 
there a way to get sqlite to do so? Is this a planned feature?

Why do I ask? I have a big view that joins a whole bunch of a tables 
(including with a virtual table), but most queries use only a subset of those 
queries actual data and I'd rather not get hit by that performance penalty.

Since my queries come from the user, I don't want to have them do the joins on 
their own, it'd be tedious.

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

Re: [sqlite] Improving performance of GROUP BY

[Simon Slavin]

On 29 May 2012, at 11:04pm, Nico Williams  wrote:

> Can you post EXPLAIN QUERY PLAN output for your statements?  And/or your 
> schema.

Also perform the SQL command 'ANALYZE' on that database, then try your timings 
again.  See if the timings improve (or perhaps even get worse).

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

Re: [sqlite] Improving performance of GROUP BY

[Nico Williams]

On Fri, May 25, 2012 at 1:38 PM, Udi Karni  wrote:
> I am running the following query -
>
> CREATE TABLE XYZ AS
> SELECT ID, MIN (DATE)
> FROM SOURCE-TABLE
> WHERE CRITERIA
> GROUP BY ID ;
>
> SOURCE-TABLE has 600 million rows, 2 million meet the WHERE criteria, and
> get grouped to 100,000 distinct IDs. There are no indexes as there is too
> much variety in the WHERE clauses and index creation on this table takes
> many hours. Temp_store=memory. When executed - the source table is read
> very slowly, and runtime is 1/2 hour.
>
> I split this SQL into 2 steps - the first just extracting - the second just
> grouping. The extract read the table extremely fast and finished in 3
> minutes. The GROUP BY ran in a few seconds. So a 10X improvement - which is
> pretty incredible speed.

Meaning?  Something like this:

CREATE TABLE FOO AS SELECT ID, DATE FROM SOURCE_TABLE;
CREATE TABLE BAR AS SELECT ID, MIN(DATE) FROM FOO GROUP BY ID;

?

Are you sure that there are no indexes?  Maybe there are indexes
implied by UNIQUE constraints and maybe SQLite3 is incorrectly
deciding to scan the index instead of the table?

My guess is that you have a UNIQUE constraint on ID in SOURCE_TABLE
and SQLite3 is scanning the index and then fetching the DATE from the
table.  But this makes no sense because then it makes no sense to
select the min() of DATE as there would be a single row per-ID.  My
guess has to be wrong.  Maybe there's a UNIQUE constraint on several
columns including ID?  That would not be incompatible with the
semantics of your query.

Can you post EXPLAIN QUERY PLAN output for your statements?  And/or your schema.

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

Re: [sqlite] rearranging 2 fields into 1 based on an order indicator in 2 other fields

[Igor Tandetnik]

On 5/29/2012 4:44 PM, Gert Van Assche wrote:

I have a strange table like this

rowid; Field1x; Field2y; FieldAx; FieldBy
1; A;   a;   0;   1
2; B;   b;   4;   2

and I would need this:

rowid; Field3; FieldC
1; A;  0
2; a;  1
3; b;  2
4; B;  4


Something like this perhaps:

select
  (case when FieldAx < FieldBy then Field1x else Field2y end) as Field3,
  (case when FieldAx < FieldBy then FieldAx else FieldBy end) as FieldC
from MyTable
union all
select
  (case when FieldAx >= FieldBy then Field1x else Field2y end) as Field3,
  (case when FieldAx >= FieldBy then FieldAx else FieldBy end) as FieldC
from MyTable;

--
Igor Tandetnik

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

Re: [sqlite] rearranging 2 fields into 1 based on an order indicator in 2 other fields

[David Bicking]

Create Table table2 (Field3, FieldC);
insert into table2 (Field3, FieldC) Select Field1x, FieldAx from Table1;
insert into table2 (Field3, FieldC) Select Field2y, FieldBy from Table1;

this will put your data in to the new table. I suspect the rowids won't match 
what you want, but you can always order by FieldC to get the order you want.

If the rowids are critical, then do the inserts in to a temp table, then insert 
in to the final table using an order by FieldC.


David



 From: Gert Van Assche 
To: sqlite-users@sqlite.org 
Sent: Tuesday, May 29, 2012 4:44 PM
Subject: [sqlite] rearranging 2 fields into 1 based on an order indicator in 2 
other fields
 
All,

I have a strange table like this

rowid; Field1x; Field2y; FieldAx; FieldBy
1;     A;       a;       0;       1
2;     B;       b;       4;       2

and I would need this:

rowid; Field3; FieldC
1;     A;      0
2;     a;      1
3;     b;      2
4;     B;      4

So:
- the contents of Field1x and Field2y should go into Field3
- the order in the new table/view depends on values FieldAx and FieldBy
that should go into FieldC
- the order indicator in the first table of Field1x is in FieldAx;  the
order indicator of  Field2y is in FieldBy

I have absolutely no clue how to do this. I hope this is possible.

Thanks for your brains on this.


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

[sqlite] rearranging 2 fields into 1 based on an order indicator in 2 other fields

[Gert Van Assche]

All,

I have a strange table like this

rowid; Field1x; Field2y; FieldAx; FieldBy
1; A;   a;   0;   1
2; B;   b;   4;   2

and I would need this:

rowid; Field3; FieldC
1; A;  0
2; a;  1
3; b;  2
4; B;  4

So:
- the contents of Field1x and Field2y should go into Field3
- the order in the new table/view depends on values FieldAx and FieldBy
that should go into FieldC
- the order indicator in the first table of Field1x is in FieldAx;  the
order indicator of  Field2y is in FieldBy

I have absolutely no clue how to do this. I hope this is possible.

Thanks for your brains on this.


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

Re: [sqlite] how to merge data from fields... if this is possible.

[Petite Abeille]

On May 29, 2012, at 8:12 PM, Pavel Ivanov wrote:

> It's obeyed after aggregation. So the same query with GROUP BY won't
> work. Although one might write something like this:

According to the fine manual [1]:

"The order of the concatenated elements is arbitrary."

[1] http://www.sqlite.org/lang_aggfunc.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Cascade deletes in .Net FW 4 + EF

[Kevin Benson]

On Tue, May 29, 2012 at 9:26 AM, Peter Litsegård wrote:

> Hi!
>
> I have a very simple database hsoted by SQLite v1.0.80.0. I'm struggling
> to get cascade deletes to work using EF. I have set the OnDelete to
> 'Cascade' on the "one end" but it fails to execute. Is this a known issue?
> Are there any workarounds?
>
> Thanks!
> ___
>
 A quick search seems to indicate you need to either:

explicitly ensure execution of the PRAGMA foreign_keys = true command
...before attempting such a transaction
-OR-
append the connection string attribute as implemented here, maybe?
http://system.data.sqlite.org/index.html/tktview?name=464e6cee2f

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to merge data from fields... if this is possible.

[Pavel Ivanov]

> SELECT group_concat(fieldB,' ') FROM myTable WHERE fieldA = 'val1' ORDER BY 
> rowid
>
> Might get one of the values you wanted.  One concern is that I don't remember 
> whether the ORDER BY clause is obeyed before or after aggregation.

It's obeyed after aggregation. So the same query with GROUP BY won't
work. Although one might write something like this:

SELECT a.fieldA,
(SELECT group_concat(b.fieldB, ' ') FROM myTable b
  WHERE a.fieldA = b.fieldA
  ORDER BY b.rowid)
FROM myTable a
GROUP BY a.fieldA


Pavel


On Tue, May 29, 2012 at 1:33 PM, Simon Slavin  wrote:
>
> On 29 May 2012, at 6:24pm, Gert Van Assche  wrote:
>
>> I have a dataset that looks like this:
>>
>> rowid ; fieldA ; fieldB
>> 1     ; val1   ; This is a
>> 2     ; val1   ; small
>> 3     ; val1   ; test.
>> 4     ; val2   ; The proof is in
>> 5     ; val2   ; the pudding.
>>
>>
>> And I would like to merge all values in fieldB when the value in fieldA is
>> the same.
>>
>> rowid ; fieldA ; fieldB
>> 1     ; val1   ; This is a small test.
>> 2     ; val2   ; The proof is in the pudding.
>>
>>
>> Question: can you do this with an SQL query?
>
> I haven't tried this but I'd try using the group_concat() function.  For 
> instance
>
> SELECT group_concat(fieldB,' ') FROM myTable WHERE fieldA = 'val1' ORDER BY 
> rowid
>
> Might get one of the values you wanted.  One concern is that I don't remember 
> whether the ORDER BY clause is obeyed before or after aggregation.
>
> But if the above works the way you want, then try and get all the values you 
> want in one SELECT using GROUP BY.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to merge data from fields... if this is possible.

[Black, Michael (IS)]

Does this do what you want?



create table t1(rowid,fieldA,fieldB);
insert into t1 values(1,'val1','This is a');
insert into t1 values(2,'val1','small');
insert into t1 values(3,'val1','test.');
insert into t1 values(4,'val2','The proof is in');
insert into t1 values(5,'val2','the pudding.');
create table t2(rowid,fieldA,fieldB);
insert into t2 values(1,'val1','This is a small test.');
insert into t2 values(2,'val2','The proof is in the pudding.');



sqlite> select t2.rowid,t2.fieldB from t1,t2 where t1.fieldA=t2.fieldA;
1|val1|This is a|1|val1|This is a small test.
2|val1|small|1|val1|This is a small test.
3|val1|test.|1|val1|This is a small test.
4|val2|The proof is in|2|val2|The proof is in the pudding.
5|val2|the pudding.|2|val2|The proof is in the pudding.



sqlite> select t2.rowid,t2.fieldA,t2.fieldB from t1,t2 where 
t1.fieldA=t2.fieldA group by t2.rowid;
1|val1|This is a small test.
2|val2|The proof is in the pudding.

You may want an "order by t2.rowid" or such.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Tuesday, May 29, 2012 12:33 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] how to merge data from fields... if this is possible.


On 29 May 2012, at 6:24pm, Gert Van Assche  wrote:

> I have a dataset that looks like this:
>
> rowid ; fieldA ; fieldB
> 1 ; val1   ; This is a
> 2 ; val1   ; small
> 3 ; val1   ; test.
> 4 ; val2   ; The proof is in
> 5 ; val2   ; the pudding.
>
>
> And I would like to merge all values in fieldB when the value in fieldA is
> the same.
>
> rowid ; fieldA ; fieldB
> 1 ; val1   ; This is a small test.
> 2 ; val2   ; The proof is in the pudding.
>
>
> Question: can you do this with an SQL query?

I haven't tried this but I'd try using the group_concat() function.  For 
instance

SELECT group_concat(fieldB,' ') FROM myTable WHERE fieldA = 'val1' ORDER BY 
rowid

Might get one of the values you wanted.  One concern is that I don't remember 
whether the ORDER BY clause is obeyed before or after aggregation.

But if the above works the way you want, then try and get all the values you 
want in one SELECT using GROUP BY.

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

Re: [sqlite] how to merge data from fields... if this is possible.

[Simon Slavin]

On 29 May 2012, at 6:24pm, Gert Van Assche  wrote:

> I have a dataset that looks like this:
> 
> rowid ; fieldA ; fieldB
> 1 ; val1   ; This is a
> 2 ; val1   ; small
> 3 ; val1   ; test.
> 4 ; val2   ; The proof is in
> 5 ; val2   ; the pudding.
> 
> 
> And I would like to merge all values in fieldB when the value in fieldA is
> the same.
> 
> rowid ; fieldA ; fieldB
> 1 ; val1   ; This is a small test.
> 2 ; val2   ; The proof is in the pudding.
> 
> 
> Question: can you do this with an SQL query?

I haven't tried this but I'd try using the group_concat() function.  For 
instance

SELECT group_concat(fieldB,' ') FROM myTable WHERE fieldA = 'val1' ORDER BY 
rowid

Might get one of the values you wanted.  One concern is that I don't remember 
whether the ORDER BY clause is obeyed before or after aggregation.

But if the above works the way you want, then try and get all the values you 
want in one SELECT using GROUP BY.

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

[sqlite] how to merge data from fields... if this is possible.

[Gert Van Assche]

All,

I have a dataset that looks like this:

rowid ; fieldA ; fieldB
1 ; val1   ; This is a
2 ; val1   ; small
3 ; val1   ; test.
4 ; val2   ; The proof is in
5 ; val2   ; the pudding.


And I would like to merge all values in fieldB when the value in fieldA is
the same.

rowid ; fieldA ; fieldB
1 ; val1   ; This is a small test.
2 ; val2   ; The proof is in the pudding.


Question: can you do this with an SQL query?

thanks for your help.


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

[sqlite] Documentation glitch: Repeated "or" ...

[Ralf Junker]

... at both end and beginning of these lines:

http://www.sqlite.org/src/artifact/45a846045ddb8c4318f2919f3a70f011df5ca783?ln=2584-2585

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

Re: [sqlite] Question about multi-threading with a read-onlydatabase

[Mohit Sindhwani]

Hi Simon,

On 29/5/2012 7:36 PM, Simon Slavin wrote:


It might be worth pointing out that the creator of SQLite feels that threads 
are evil.  In fact it's in the FAQ, together with a pointer to the standard 
work on the subject (which I have never managed to understand all the way 
through):



If you haven't already gone to effort to write your program to use threads, 
then you might consider not doing it.  Perhaps you could use processes instead. 
 Multiprocessing is fine.



We're on a Windows CE embedded device and are already using processes 
for a couple of things, but still think we'll  need threads to decouple 
some drawing that uses the database from the rest of the system that 
also needs to access the database for its work.


Best Regards,
Mohit.
30/5/2012 | 12:17 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Use with asp.net

[Mike]

I'd like to use a SQLite db as a back end for asp.net web pages. Is 
there information somewhere on how to deploy it and set it up?


I gather I need to use system.data.sqlite.dll. That's the part I'm most 
interested in.


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

Re: [sqlite] Cascade deletes in .Net FW 4 + EF

[Simon Slavin]

On 29 May 2012, at 2:26pm, Peter Litsegård  wrote:

> I have a very simple database hsoted by SQLite v1.0.80.0. I'm struggling to 
> get cascade deletes to work using EF. I have set the OnDelete to 'Cascade' on 
> the "one end" but it fails to execute. Is this a known issue? Are there any 
> workarounds?

There is no such thing as version 1.0.80.0 of SQLite.  Can you figure out which 
version of SQLite you're actually using ?  You can use

SELECT sqlite_version()

Do you have foreign keys enabled ?  See section 2 of



What results are you getting when you try to execute that ?  Does it return any 
result code at all, or does it just crash ?

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

Re: [sqlite] How to write and read the same sqlite3 DB in memory concurrently in two thread?

[Black, Michael (IS)]

And that explanation along with why one would want to use each mode would be 
quite helpful to the community I would think.





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Pavel Ivanov [paiva...@gmail.com]
Sent: Tuesday, May 29, 2012 9:25 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] How to write and read the same sqlite3 DB in memory 
concurrently in two thread?

> Multi-thread -- one sqlite3 object per thread is required -- no sharing of 
> prepared statements or anything else.

This is not true. Sharing of prepared statements and sqlite3 object is
allowed in this mode as long as any access to them is protected by
mutex or any other synchronization mechanism so that no two threads
are accessing objects simultaneously.
So basically the only difference between multi-thread mode and
serialized mode is responsibility of access synchronization: in
serialized mode it's SQLite's responsibility, in multi-thread mode
it's developer responsibility.


Pavel


On Tue, May 29, 2012 at 9:57 AM, Black, Michael (IS)
 wrote:
> This seems to fly in thte face of what I remember reading on here before.  
> Can we get a clear explanation of what to do with the different THREADSAFE 
> settings?  I think it's implied...
>
>
>
> i.e. (assumign this is correct)
>
> Single-thread -- no threading allowed at all
>
> Multi-thread -- one sqlite3 object per thread is required -- no sharing of 
> prepared statements or anything else.
>
> Serialized -- sqlite3 object can be shared among threads along with prepared 
> statements and all other functions.
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Pavel Ivanov [paiva...@gmail.com]
> Sent: Tuesday, May 29, 2012 8:08 AM
> To: 吴 毅; General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] How to write and read the same sqlite3 DB in 
> memory concurrently in two thread?
>
>> How to write and read the same sqlite3 DB in memory concurrently in two 
>> thread?
>
> You need to use one connection (sqlite3 object) and not to use
> SQLITE_OPEN_NOMUTEX flag (assuming you used default SQLite compilation
> options) or protect access to sqlite3 object with your own mutex.
>
>
> Pavel
>
>
> On Fri, May 25, 2012 at 4:51 AM, 吴 毅  wrote:
>> Now I am writing datas to sqlite3 DB in memory(":memory:") in a thread .
>> And concurrently I want to read the datas of the same DB in memory too.
>> but While i used the same object of sqlite3 to write and read, i had got the 
>> error:SQLITE_MISUSE.
>> If i use the different objects of sqlite3 to write and read 
>> concurrently,these objects get the different DB in memory.This is not I want 
>> to do.I want to get datas of the same database.
>> Moreover, I don't plan to write and read the database in disks too.Becase I 
>> thick it's more faster to operate in memory.
>> I open the database in the way of "m_sqliteDB.open(":memory:");" now.
>>
>> How to write and read the same sqlite3 DB in memory concurrently in two 
>> thread?
>>
>> That might be?
>>
>> thank you.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to write and read the same sqlite3 DB in memory concurrently in two thread?

[Pavel Ivanov]

> Multi-thread -- one sqlite3 object per thread is required -- no sharing of 
> prepared statements or anything else.

This is not true. Sharing of prepared statements and sqlite3 object is
allowed in this mode as long as any access to them is protected by
mutex or any other synchronization mechanism so that no two threads
are accessing objects simultaneously.
So basically the only difference between multi-thread mode and
serialized mode is responsibility of access synchronization: in
serialized mode it's SQLite's responsibility, in multi-thread mode
it's developer responsibility.


Pavel


On Tue, May 29, 2012 at 9:57 AM, Black, Michael (IS)
 wrote:
> This seems to fly in thte face of what I remember reading on here before.  
> Can we get a clear explanation of what to do with the different THREADSAFE 
> settings?  I think it's implied...
>
>
>
> i.e. (assumign this is correct)
>
> Single-thread -- no threading allowed at all
>
> Multi-thread -- one sqlite3 object per thread is required -- no sharing of 
> prepared statements or anything else.
>
> Serialized -- sqlite3 object can be shared among threads along with prepared 
> statements and all other functions.
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Pavel Ivanov [paiva...@gmail.com]
> Sent: Tuesday, May 29, 2012 8:08 AM
> To: 吴 毅; General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] How to write and read the same sqlite3 DB in 
> memory concurrently in two thread?
>
>> How to write and read the same sqlite3 DB in memory concurrently in two 
>> thread?
>
> You need to use one connection (sqlite3 object) and not to use
> SQLITE_OPEN_NOMUTEX flag (assuming you used default SQLite compilation
> options) or protect access to sqlite3 object with your own mutex.
>
>
> Pavel
>
>
> On Fri, May 25, 2012 at 4:51 AM, 吴 毅  wrote:
>> Now I am writing datas to sqlite3 DB in memory(":memory:") in a thread .
>> And concurrently I want to read the datas of the same DB in memory too.
>> but While i used the same object of sqlite3 to write and read, i had got the 
>> error:SQLITE_MISUSE.
>> If i use the different objects of sqlite3 to write and read 
>> concurrently,these objects get the different DB in memory.This is not I want 
>> to do.I want to get datas of the same database.
>> Moreover, I don't plan to write and read the database in disks too.Becase I 
>> thick it's more faster to operate in memory.
>> I open the database in the way of "m_sqliteDB.open(":memory:");" now.
>>
>> How to write and read the same sqlite3 DB in memory concurrently in two 
>> thread?
>>
>> That might be?
>>
>> thank you.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to write and read the same sqlite3 DB in memory concurrently in two thread?

[Black, Michael (IS)]

This seems to fly in thte face of what I remember reading on here before.  Can 
we get a clear explanation of what to do with the different THREADSAFE 
settings?  I think it's implied...



i.e. (assumign this is correct)

Single-thread -- no threading allowed at all

Multi-thread -- one sqlite3 object per thread is required -- no sharing of 
prepared statements or anything else.

Serialized -- sqlite3 object can be shared among threads along with prepared 
statements and all other functions.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Pavel Ivanov [paiva...@gmail.com]
Sent: Tuesday, May 29, 2012 8:08 AM
To: 吴 毅; General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] How to write and read the same sqlite3 DB in memory 
concurrently in two thread?

> How to write and read the same sqlite3 DB in memory concurrently in two 
> thread?

You need to use one connection (sqlite3 object) and not to use
SQLITE_OPEN_NOMUTEX flag (assuming you used default SQLite compilation
options) or protect access to sqlite3 object with your own mutex.


Pavel


On Fri, May 25, 2012 at 4:51 AM, 吴 毅  wrote:
> Now I am writing datas to sqlite3 DB in memory(":memory:") in a thread .
> And concurrently I want to read the datas of the same DB in memory too.
> but While i used the same object of sqlite3 to write and read, i had got the 
> error:SQLITE_MISUSE.
> If i use the different objects of sqlite3 to write and read 
> concurrently,these objects get the different DB in memory.This is not I want 
> to do.I want to get datas of the same database.
> Moreover, I don't plan to write and read the database in disks too.Becase I 
> thick it's more faster to operate in memory.
> I open the database in the way of "m_sqliteDB.open(":memory:");" now.
>
> How to write and read the same sqlite3 DB in memory concurrently in two 
> thread?
>
> That might be?
>
> thank you.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Cascade deletes in .Net FW 4 + EF

[Peter Litsegård]

Hi!

I have a very simple database hsoted by SQLite v1.0.80.0. I'm struggling to get 
cascade deletes to work using EF. I have set the OnDelete to 'Cascade' on the 
"one end" but it fails to execute. Is this a known issue? Are there any 
workarounds?

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

Re: [sqlite] How to write and read the same sqlite3 DB in memory concurrently in two thread?

[Pavel Ivanov]

> How to write and read the same sqlite3 DB in memory concurrently in two 
> thread?

You need to use one connection (sqlite3 object) and not to use
SQLITE_OPEN_NOMUTEX flag (assuming you used default SQLite compilation
options) or protect access to sqlite3 object with your own mutex.


Pavel


On Fri, May 25, 2012 at 4:51 AM, 吴 毅  wrote:
> Now I am writing datas to sqlite3 DB in memory(":memory:") in a thread .
> And concurrently I want to read the datas of the same DB in memory too.
> but While i used the same object of sqlite3 to write and read, i had got the 
> error:SQLITE_MISUSE.
> If i use the different objects of sqlite3 to write and read 
> concurrently,these objects get the different DB in memory.This is not I want 
> to do.I want to get datas of the same database.
> Moreover, I don't plan to write and read the database in disks too.Becase I 
> thick it's more faster to operate in memory.
> I open the database in the way of "m_sqliteDB.open(":memory:");" now.
>
> How to write and read the same sqlite3 DB in memory concurrently in two 
> thread?
>
> That might be?
>
> thank you.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 'Illegal mnemonic' error during compiling sqlite on the opensolaris

[Anatolii.Apanasiuk]

Hi, 

I have this error:

o===|-=-==|=o
Starting build sqlite-amalgamation: [/var/tmp/build_svn-16470]
Assembler: sqlite3.c
"/var/tmp/build_svn-16470/ccrUHYTt.s", line 1543 : Illegal
mnemonic
Near line: "filds   (%esp)"
"/var/tmp/build_svn-16470/ccrUHYTt.s", line 1543 : Syntax error
Near line: "filds   (%esp)"
o===|-=-==|=o

Name: SQLite
Description: SQL database engine
Version: 3.7.12.1

Try to compile on the:
--
Oracle Solaris 10 8/11 s10x_u10wos_17b X86
SunOS:5.10:Generic_147441-10:i86pc
--

ENV:

APPSERVER_DATA_MODEL: [32]
  GNUMAKE_HOME: /sbcimp/run/pd/gnumake/v3.81   [
exists ]
  GCC_HOME:  /sbcimp/run/pd/gcc/32-bit/4.3.4   [
exists ]
COREUTILS_HOME: /sbcimp/run/pd/coreutils/5.0   [
exists ]
 BINUTILS_HOME:  /sbcimp/run/pd/binutils/32-bit/2.18   [
exists ]
 JAVA_HOME: /sbcimp/run/tp/sun/jdk/v1.6.0_31   [
exists ]
 SWIG_HOME: is not defined []
   PYTHON_HOME:   /sbcimp/run/pd/csm/32-bit/python/2.7.2   [
exists ]
 RUBY_HOME: is not defined []
 PERL_HOME:/sbcimp/run/pd/csm/32-bit/perl/5.14.2   [
exists ]
OPENSSLDIR:/sbcimp/run/pd/openssl/0.9.8i   [
exists ]


My cmd line: 
gcc shell.c sqlite3.c -lpthread -ldl


Can anybody help me resolve this issue?

Cordially,
Anatolii.
Visit our website at http://www.ubs.com

This message contains confidential information and is intended only 
for the individual named.  If you are not the named addressee you 
should not disseminate, distribute or copy this e-mail.  Please 
notify the sender immediately by e-mail if you have received this 
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or 
error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses.  The sender 
therefore does not accept liability for any errors or omissions in the 
contents of this message which arise as a result of e-mail transmission.  
If verification is required please request a hard-copy version.  This 
message is provided for informational purposes and should not be 
construed as a solicitation or offer to buy or sell any securities 
or related financial instruments.

 
UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update Query

[Pavel Ivanov]

On Tue, May 29, 2012 at 6:05 AM, IQuant  wrote:
>>CREATE VIEW TICKMAX
>>AS
>> SELECT ASK, BID, TRADEPRICE, TIMESTAMP, SYMBOL
>>    FROM TICKDATA
>>   WHERE TIMESTAMP = MAX(TIMESTAMP)
>>GROUP BY SYMBOL;
>
> Trying to work through your suggestions:
> I'm getting "Misuse of aggregate max()"

With the latest version of SQLite I guess this can be rewritten as

CREATE VIEW TICKMAX
AS
 SELECT ASK, BID, TRADEPRICE, MAX(TIMESTAMP), SYMBOL
   FROM TICKDATA
 GROUP BY SYMBOL;

But note only in SQLite 3.7.11 and later fields ASK, BID and
TRADEPRICE will be selected from the row with maximum TIMESTAMP,
previous SQLite versions will return values from random row.


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

Re: [sqlite] FW: Problem with SQLite when deployed.

[Peter Walburn]

Richard,

Thanks very much.  That now works with the latest version of SQLite.  I had to 
include the Microsoft Visual C++ 2010 Redistributable package.
Brilliant!



Peter Walburn
Software Engineer
E-mail: peter.walb...@omega-data.com 
Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, 
Dyce, Aberdeen AB21 0GP
Tel: +44 (0)1224 772763
Fax: +44 (0)1224 772783
www.omega-data.com



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard
Sent: 28 May 2012 19:54
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] FW: Problem with SQLite when deployed.

Peter,

I'm not sure if this will help but I had exactly the same error but only on 
some Vista and XP installations.  After much research I found that VC 2010 
runtime support was missing from these PCs.  If this is the same as your 
problem then you can either try one of the SQLite statically linked packages 
(for example: sqlite-netFx40-static-binary-bundle-Win32-2010-1.0.81.0.zip)
or you can install the VC runtime on each target PC
(http://www.microsoft.com/en-us/download/details.aspx?id=)

I must admit, I thought that SQLite was self-contained and had no dependencies, 
but that's another story.

Best wishes
Richard



-Original Message-
From: Peter Walburn [mailto:peter.walb...@omega-data.com]
Sent: 28 May 2012 11:13 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] FW: Problem with SQLite when deployed.

I thought that things were going to work ok on Friday, but now that my Install 
routine copies the 32-bit DLL files onto the 32-bit operating system, I am 
receiving the same errors.  Installing the application (using the same install 
routine) on a 64-bit operating system does work fine.  This makes me think that 
there is something I am doing wrong and maybe the 32 and 64-bit DLLs are 
getting mixed up.



Peter Walburn
Software Engineer
E-mail: peter.walb...@omega-data.com
Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen
AB21 0GP
Tel: +44 (0)1224 772763
Fax: +44 (0)1224 772783
www.omega-data.com



-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Walburn
Sent: 25 May 2012 16:48
To: General Discussion of SQLite Database
Subject: Re: [sqlite] FW: Problem with SQLite when deployed.

I've just been trying some other things and it seems that the 
SQLite.Interop.dll file that I have copied to the 32-bit operating system is 
the 64-bit version.  I think it will work if I make sure that the 32-bit 
versions of the DLLs are included in my install program.  Will the 32-bit 
versions work on 64-bit operating systems too?



Peter Walburn
Software Engineer
E-mail: peter.walb...@omega-data.com
Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, Aberdeen
AB21 0GP
Tel: +44 (0)1224 772763
Fax: +44 (0)1224 772783
www.omega-data.com




-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita
Sent: 25 May 2012 16:37
To: General Discussion of SQLite Database
Subject: Re: [sqlite] FW: Problem with SQLite when deployed.

Simple thing first:

Did you ensure that the SQLite.Interop.dll is in the path that the PC is 
searching?

regards,
Adam


On Fri, May 25, 2012 at 10:57 AM, Peter Walburn < peter.walb...@omega-data.com> 
wrote:

> Hi,
>
> I have an application written in C# .Net 4.0 Framework.  I use SQLite 
> within the application.  I have recently updated from an older version 
> of SQLite to the latest version as I have moved to .Net Framework 4.0 
> and I received error messages about Mixed Mode Frameworks.
>
> Anyway, I do the development on an 64-Bit Windows 7 operating system.  
> I use Installshield 2010 Express to create an installation for the 
> application.  The application works ok on the Windows 7 PC, but when 
> installed on a different PC (or on a Virtual Client PC using VMWare), 
> I always receive a message such as:
>
> Unhandled exception has occurred in your application.  If you click 
> Continue, the application will ignore this error and attempt to 
> continue. If you click Quit, the application will close immediately.
>
> Unable to load DLL 'SQLite.Interop.dll': The specified module could 
> not be found. (Exception from HRESULT: 0x8007007e).
>
> I have tried to post this email about 5 times and it is always 
> returned
> saying: "The message's content type was not explicitly allowed."
>
>
>
> Peter Walburn
> Software Engineer
> E-mail: peter.walb...@omega-data.com
> Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate, Dyce, 
> Aberdeen
> AB21 0GP
> Tel: +44 (0)1224 772763
> Fax: +44 (0)1224 772783
> www.omega-data.com
>
>
>
>
>
> This is an email from Omega Data Services Ltd, a company registered in 
> Edinburgh, Scotland, with company number SC192323. Registered Office:
> Maclay Murray & Spens, 66 Queens Road, Aberdeen, AB15 4YE. Tel:
> 

Re: [sqlite] SQLite Database Password Recovery

[Simon Slavin]

On 29 May 2012, at 12:16pm, malaris  wrote:

> Hi, i am using sqlite database. 
> 
> a week ago i have encrypted using password. 
> 
> Now i forgot the password. how can i break the password ?
> 
> is there any tool available to recover the database password ?

Contact the organisation which supplied the password-protection software.

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

Re: [sqlite] Question about multi-threading with a read-onlydatabase

[Simon Slavin]

On 29 May 2012, at 8:00am, Mohit Sindhwani  wrote:

> Hi Igor,
> 
> [snip]
> 
> Thank you very much for the clarification.  I guess we'll move to the 
> multi-threaded mode in a later release.

It might be worth pointing out that the creator of SQLite feels that threads 
are evil.  In fact it's in the FAQ, together with a pointer to the standard 
work on the subject (which I have never managed to understand all the way 
through):



If you haven't already gone to effort to write your program to use threads, 
then you might consider not doing it.  Perhaps you could use processes instead. 
 Multiprocessing is fine.

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

[sqlite] SQLite Database Password Recovery

[malaris]

Hi, i am using sqlite database. 

a week ago i have encrypted using password. 

Now i forgot the password. how can i break the password ?

is there any tool available to recover the database password ?
-- 
View this message in context: 
http://old.nabble.com/SQLite-Database-Password-Recovery-tp33924426p33924426.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] Update Query

[IQuant]

Keith,

Trying to work through your suggestions:
I'm getting "Misuse of aggregate max()"


>CREATE VIEW TICKMAX
>AS
> SELECT ASK, BID, TRADEPRICE, TIMESTAMP, SYMBOL
>FROM TICKDATA
>   WHERE TIMESTAMP = MAX(TIMESTAMP)
>GROUP BY SYMBOL;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users