date:20121016

Bart Smissaert  wrote:
> To do with the same, what is wrong with this update SQL?
> 
> update final2
> set group_count =
> (select count(*)
> from
> final2
> group by
> group_marker)
> 
> It makes group_count always 1

The subquery does not depend on the values in the row being updated. It always 
produces the same resultset. Now, the value of the expression of the form 
"(select ...)" is the value of the first column of the first row of the 
resultset. In your case, it just happens to be 1.

You are probably looking for something like this:

update final2 set group_count =
(select count(*) from final2 t2 where t2.group_marker = final2.group_marker);

Here, the condition of the subquery mentions a value from the outer table, so 
it's evaluated anew for every row being updated. See also:

http://en.wikipedia.org/wiki/Correlated_subquery

-- 
Igor Tandetnik

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

Re: [sqlite] new Windows versions?

2012-10-16 Thread Darren Duncan


TaxDetective wrote:
Hi Darren are you on your own yet? 


Yes, I moved into my own place 3 months ago, in downtown Victoria.

I'm currently at:

  unit 110,
  915 Cook Street,
  Victoria, BC,
  V8V3Z4

My phone number is 778-265-1827.  This is a land-line (Shaw digital, with 
unlimited NA long distance); I don't have a cellphone yet, though intend to get 
one within 6 months.


I am still providing computer support to my mother though, who doesn't really 
have an alternative as savvy, most of it remote but sometimes I go over there to 
do maintenance.


As for Windows I use what comes on the machine at the time and trade up every 3 years.  No idea.  


Okay.

Well, Windows 7 was released between July and October of 2009, so that means 
unless you got your machine almost 3 years ago you probably have that.


Presumably, unless you explicitly request otherwise for your next machine, 
you'll have Windows 8 on your next trade-up, as it comes out later this month.


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

[sqlite] Technical article on SQLite

2012-10-16 Thread Simon Slavin



"In this first article exploring SQLite's internal workings, we look at 
SQLite's Virtual Database Engine (VDBE), a virtual machine which executes code 
generated from SQL commands to store and retrieve data."

Not very advanced.  Mostly just says that there is a VDBE and how to see what 
it does.

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

Re: [sqlite] find sequential groups

To do with the same, what is wrong with this update SQL?

update final2
set group_count =
(select count(*)
from
final2
group by
group_marker)

It makes group_count always 1, but should include higher values.
The select by itself gives the right result.

RBS

On Wed, Oct 17, 2012 at 12:31 AM, Bart Smissaert
 wrote:
> Without the concatenation it runs fine,
> enormously faster than with the concatenation.
> Have checked and the result is fine as well.
> Thanks again.
>
> RBS
>
>
> On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik  wrote:
>> On 10/16/2012 6:29 PM, Bart Smissaert wrote:
>>>
>>> Actually, it really is slow, made worse by the fact that there is not
>>> one grouping
>>> field (value in my example), but three. I am running your SQL now,
>>> concatenating
>>> these 3 fields, but still running and looks will be a long time.
>>> Will have to improve it with indexes and maybe avoiding the concatenation.
>>
>>
>> This would avoid concatenation:
>>
>>
>> update MyTable set Group_Marker = (
>>   select count(*) from MyTable t1
>>   where t1.ID <= MyTable.ID and not (
>> select (t2.Value1=t1.Value1 and t2.Value2=t1.Value2 and
>> t2.Value3=t1.Value3)
>>
>> from MyTable t2 where t2.ID < t1.ID
>> order by t2.ID desc limit 1
>>   )
>> );
>>
>> The only index that would be helful is one on ID, which I suspect you might
>> already have.
>>
>> --
>> Igor Tandetnik
>>
>> ___
>> 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] find sequential groups

Without the concatenation it runs fine,
enormously faster than with the concatenation.
Have checked and the result is fine as well.
Thanks again.

RBS


On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik  wrote:
> On 10/16/2012 6:29 PM, Bart Smissaert wrote:
>>
>> Actually, it really is slow, made worse by the fact that there is not
>> one grouping
>> field (value in my example), but three. I am running your SQL now,
>> concatenating
>> these 3 fields, but still running and looks will be a long time.
>> Will have to improve it with indexes and maybe avoiding the concatenation.
>
>
> This would avoid concatenation:
>
>
> update MyTable set Group_Marker = (
>   select count(*) from MyTable t1
>   where t1.ID <= MyTable.ID and not (
> select (t2.Value1=t1.Value1 and t2.Value2=t1.Value2 and
> t2.Value3=t1.Value3)
>
> from MyTable t2 where t2.ID < t1.ID
> order by t2.ID desc limit 1
>   )
> );
>
> The only index that would be helful is one on ID, which I suspect you might
> already have.
>
> --
> Igor Tandetnik
>
> ___
> 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] find sequential groups

Thanks, will give that a try as well.

RBS



On Wed, Oct 17, 2012 at 12:00 AM, Black, Michael (IS)
 wrote:
> Ok...how about with triggers then?
> This will give a unique number to each sequence as you insert them.
>
> CREATE TABLE Test(ID,Value,Group_Marker);
> CREATE TRIGGER insert_trigger1 after insert on Test
> WHEN new.id=1
> BEGIN
>   UPDATE Test set Group_Marker=1;
> END;
> CREATE TRIGGER insert_trigger2 after insert on Test
> WHEN new.id > 1 and ((SELECT Group_Marker from Test where id=new.id-1 and 
> Value=new.Value) IS NOT NULL)
> BEGIN
>   UPDATE Test set Group_Marker=(select Group_Marker from Test where 
> id=new.id-1) where id=new.id;
> END;
> CREATE TRIGGER insert_trigger3 after insert on Test
> WHEN new.id > 1 and ((SELECT Group_Marker from Test where id=new.id-1 and 
> Value!=new.Value) IS NOT NULL)
> BEGIN
>   UPDATE Test set Group_Marker=(select Group_Marker+1 from Test where 
> id=new.id-1) where id=new.id;
> END;
> INSERT INTO "Test" VALUES(1,'D',0);
> INSERT INTO "Test" VALUES(2,'X',0);
> INSERT INTO "Test" VALUES(3,'X',0);
> INSERT INTO "Test" VALUES(4,'X',0);
> INSERT INTO "Test" VALUES(5,'A',0);
> INSERT INTO "Test" VALUES(6,'B',0);
> INSERT INTO "Test" VALUES(7,'X',0);
> SELECT * FROM Test;
>
> You'll see that # 7 gets a new Group_Marker instead of repeating group#2.
>
>
> 1|D|1
> 2|X|2
> 3|X|2
> 4|X|2
> 5|A|3
> 6|B|4
> 7|X|5
>
>
> 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 Bart Smissaert [bart.smissa...@gmail.com]
> Sent: Tuesday, October 16, 2012 5:45 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] find sequential groups
>
> Thanks, will try that.
> Yes, the ID field is an integer primary key autoincrement.
> Still running the old sql with concatenation. Looks I may need
> to kill that.
>
> RBS
>
> On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik  wrote:
>> On 10/16/2012 6:29 PM, Bart Smissaert wrote:
>>>
>>> Actually, it really is slow, made worse by the fact that there is not
>>> one grouping
>>> field (value in my example), but three. I am running your SQL now,
>>> concatenating
>>> these 3 fields, but still running and looks will be a long time.
>>> Will have to improve it with indexes and maybe avoiding the concatenation.
>>
>>
>> This would avoid concatenation:
>>
>>
>> update MyTable set Group_Marker = (
>>   select count(*) from MyTable t1
>>   where t1.ID <= MyTable.ID and not (
>> select (t2.Value1=t1.Value1 and t2.Value2=t1.Value2 and
>> t2.Value3=t1.Value3)
>>
>> from MyTable t2 where t2.ID < t1.ID
>> order by t2.ID desc limit 1
>>   )
>> );
>>
>> The only index that would be helful is one on ID, which I suspect you might
>> already have.
>>
>> --
>> Igor Tandetnik
>>
>> ___
>> 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] Support of cascading updates and deletes

On Tue, Oct 16, 2012 at 7:12 PM, Kris Peters  wrote:

> I¹m new to sqlite; I took the lazy way out and downloaded the sqlite3
> precompiled binaries for Mac OS X. Creating tables, doing queries, and
> noncascading deletes and updates work fine.  But specifying any kind of
> cascading update and deletes seems to be ignored. What do you recommend?
>

PRAGMA foreign_keys=ON;

http://www.sqlite.org/pragma.html#pragma_foreign_keys



>
> Kris Peters
>
> ___
> 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

[sqlite] Support of cascading updates and deletes

2012-10-16 Thread Kris Peters

I¹m new to sqlite; I took the lazy way out and downloaded the sqlite3
precompiled binaries for Mac OS X. Creating tables, doing queries, and
noncascading deletes and updates work fine.  But specifying any kind of
cascading update and deletes seems to be ignored. What do you recommend?

Kris Peters

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

Re: [sqlite] find sequential groups

Ok...how about with triggers then?
This will give a unique number to each sequence as you insert them.

CREATE TABLE Test(ID,Value,Group_Marker);
CREATE TRIGGER insert_trigger1 after insert on Test
WHEN new.id=1
BEGIN
  UPDATE Test set Group_Marker=1;
END;
CREATE TRIGGER insert_trigger2 after insert on Test
WHEN new.id > 1 and ((SELECT Group_Marker from Test where id=new.id-1 and 
Value=new.Value) IS NOT NULL)
BEGIN
  UPDATE Test set Group_Marker=(select Group_Marker from Test where 
id=new.id-1) where id=new.id;
END;
CREATE TRIGGER insert_trigger3 after insert on Test
WHEN new.id > 1 and ((SELECT Group_Marker from Test where id=new.id-1 and 
Value!=new.Value) IS NOT NULL)
BEGIN
  UPDATE Test set Group_Marker=(select Group_Marker+1 from Test where 
id=new.id-1) where id=new.id;
END;
INSERT INTO "Test" VALUES(1,'D',0);
INSERT INTO "Test" VALUES(2,'X',0);
INSERT INTO "Test" VALUES(3,'X',0);
INSERT INTO "Test" VALUES(4,'X',0);
INSERT INTO "Test" VALUES(5,'A',0);
INSERT INTO "Test" VALUES(6,'B',0);
INSERT INTO "Test" VALUES(7,'X',0);
SELECT * FROM Test;

You'll see that # 7 gets a new Group_Marker instead of repeating group#2.


1|D|1
2|X|2
3|X|2
4|X|2
5|A|3
6|B|4
7|X|5


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 Bart Smissaert [bart.smissa...@gmail.com]
Sent: Tuesday, October 16, 2012 5:45 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] find sequential groups

Thanks, will try that.
Yes, the ID field is an integer primary key autoincrement.
Still running the old sql with concatenation. Looks I may need
to kill that.

RBS

On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik  wrote:
> On 10/16/2012 6:29 PM, Bart Smissaert wrote:
>>
>> Actually, it really is slow, made worse by the fact that there is not
>> one grouping
>> field (value in my example), but three. I am running your SQL now,
>> concatenating
>> these 3 fields, but still running and looks will be a long time.
>> Will have to improve it with indexes and maybe avoiding the concatenation.
>
>
> This would avoid concatenation:
>
>
> update MyTable set Group_Marker = (
>   select count(*) from MyTable t1
>   where t1.ID <= MyTable.ID and not (
> select (t2.Value1=t1.Value1 and t2.Value2=t1.Value2 and
> t2.Value3=t1.Value3)
>
> from MyTable t2 where t2.ID < t1.ID
> order by t2.ID desc limit 1
>   )
> );
>
> The only index that would be helful is one on ID, which I suspect you might
> already have.
>
> --
> Igor Tandetnik
>
> ___
> 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] find sequential groups

Thanks, will try that.
Yes, the ID field is an integer primary key autoincrement.
Still running the old sql with concatenation. Looks I may need
to kill that.

RBS

On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik  wrote:
> On 10/16/2012 6:29 PM, Bart Smissaert wrote:
>>
>> Actually, it really is slow, made worse by the fact that there is not
>> one grouping
>> field (value in my example), but three. I am running your SQL now,
>> concatenating
>> these 3 fields, but still running and looks will be a long time.
>> Will have to improve it with indexes and maybe avoiding the concatenation.
>
>
> This would avoid concatenation:
>
>
> update MyTable set Group_Marker = (
>   select count(*) from MyTable t1
>   where t1.ID <= MyTable.ID and not (
> select (t2.Value1=t1.Value1 and t2.Value2=t1.Value2 and
> t2.Value3=t1.Value3)
>
> from MyTable t2 where t2.ID < t1.ID
> order by t2.ID desc limit 1
>   )
> );
>
> The only index that would be helful is one on ID, which I suspect you might
> already have.
>
> --
> Igor Tandetnik
>
> ___
> 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] find sequential groups


On 10/16/2012 6:29 PM, Bart Smissaert wrote:

Actually, it really is slow, made worse by the fact that there is not
one grouping
field (value in my example), but three. I am running your SQL now, concatenating
these 3 fields, but still running and looks will be a long time.
Will have to improve it with indexes and maybe avoiding the concatenation.


This would avoid concatenation:

update MyTable set Group_Marker = (
  select count(*) from MyTable t1
  where t1.ID <= MyTable.ID and not (
select (t2.Value1=t1.Value1 and t2.Value2=t1.Value2 and 
t2.Value3=t1.Value3)

from MyTable t2 where t2.ID < t1.ID
order by t2.ID desc limit 1
  )
);

The only index that would be helful is one on ID, which I suspect you 
might already have.

--
Igor Tandetnik

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

Re: [sqlite] EXT : find sequential groups

Yes, that should be 4 groups, marked with 1, 2, 3 and 4.

RBS


On Tue, Oct 16, 2012 at 11:15 PM, Igor Tandetnik  wrote:
> On 10/16/2012 6:08 PM, Black, Michael (IS) wrote:
>>
>> Do this work for you?
>>
>> CREATE TABLE Groups (Value);
>> insert into Groups select distinct(Value) from test;
>
>
> Since the OP mentioned "sequential groups", I assumed that a sequence like
> "A,A,B,B,A,A,B,B" should count as four groups, not two as your approach
> would end up with.
> --
> Igor Tandetnik
>
>
> ___
> 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] find sequential groups

Thanks, very nice solution that!
Yes, I realise that this is a lot faster in code, but for now that is no option.
Actually, it really is slow, made worse by the fact that there is not
one grouping
field (value in my example), but three. I am running your SQL now, concatenating
these 3 fields, but still running and looks will be a long time.
Will have to improve it with indexes and maybe avoiding the concatenation.

RBS


On Tue, Oct 16, 2012 at 10:53 PM, Igor Tandetnik  wrote:
> On 10/16/2012 4:56 PM, Bart Smissaert wrote:
>>
>> Trying to make a query that can mark records, indicating them to
>> belong to a sequential group.
>> Giving the most simple example:
>>
>> IDValue   Group_Marker
>> ---
>> 1  D1
>> 2  X 2
>> 3  X 2
>> 4  X 2
>> 5  A 3
>> 6  B 4
>>
>> Given I have a table with data in the fields ID and Value, but not in
>> Group_Marker, can I make a SQL
>> that will find the values in the field Group_Marker as above and
>> update that field to hold those
>> values. The field Value holds the data indicating a sequential group,
>> so record 2, 3 and 4 are
>> the second group, hence I need the 2 in the field Group_Marker. ID is
>> the field indicating the sequence.
>> This is easy to do in code with a simple loop, but not sure now how to
>> do it in SQL.
>
>
> Something like this - but note that it's mostly an academic exercise. A
> simple loop would work orders of magnitude faster than this statement.
>
> update MyTable set Group_Marker = (
>   select count(*) from MyTable t1
>   where t1.ID <= MyTable.ID and t1.Value not in (
> select t2.Value from MyTable t2 where t2.ID < t1.ID
> order by t2.ID desc limit 1
>   )
> );
>
> In prose, for each record count the number of records below it (inclusive)
> that are first-in-group; where first-in-group in turn is defined as "a
> record such that the next record down by ID has a different Value, or there
> is no smaller ID at all".
> --
> Igor Tandetnik
>
> ___
> 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] EXT : find sequential groups


On 10/16/2012 6:08 PM, Black, Michael (IS) wrote:

Do this work for you?

CREATE TABLE Groups (Value);
insert into Groups select distinct(Value) from test;


Since the OP mentioned "sequential groups", I assumed that a sequence 
like "A,A,B,B,A,A,B,B" should count as four groups, not two as your 
approach would end up with.

--
Igor Tandetnik

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

Re: [sqlite] EXT : find sequential groups

Do this work for you?

CREATE TABLE Test(ID,Value,Group_Marker);
INSERT INTO "Test" VALUES(1,'D',0);
INSERT INTO "Test" VALUES(2,'X',0);
INSERT INTO "Test" VALUES(3,'X',0);
INSERT INTO "Test" VALUES(4,'X',0);
INSERT INTO "Test" VALUES(5,'A',0);
INSERT INTO "Test" VALUES(6,'B',0);
SELECT * FROM Test;
CREATE TABLE Groups (Value);
insert into Groups select distinct(Value) from test;
SELECT * FROM Groups;
update test set Group_Marker=(select rowid from Groups where 
Groups.Value=test.Value);
SELECT * FROM Test;

sqlite> CREATE TABLE Test(ID,Value,Group_Marker);
sqlite> INSERT INTO "Test" VALUES(1,'D',0);
sqlite> INSERT INTO "Test" VALUES(2,'X',0);
sqlite> INSERT INTO "Test" VALUES(3,'X',0);
sqlite> INSERT INTO "Test" VALUES(4,'X',0);
sqlite> INSERT INTO "Test" VALUES(5,'A',0);
sqlite> INSERT INTO "Test" VALUES(6,'B',0);
sqlite> SELECT * FROM Test;
1|D|0
2|X|0
3|X|0
4|X|0
5|A|0
6|B|0
sqlite> CREATE TABLE Groups (Value);
sqlite> insert into Groups select distinct(Value) from test;
sqlite> SELECT * FROM Groups;
D
X
A
B
sqlite> update test set Group_Marker=(select rowid from Groups where Groups.Val
e=test.Value);
sqlite> SELECT * FROM Test;
1|D|1
2|X|2
3|X|2
4|X|2
5|A|3
6|B|4

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 Bart Smissaert [bart.smissa...@gmail.com]
Sent: Tuesday, October 16, 2012 3:56 PM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] find sequential groups

Trying to make a query that can mark records, indicating them to
belong to a sequential group.
Giving the most simple example:

IDValue   Group_Marker
---
1  D1
2  X 2
3  X 2
4  X 2
5  A 3
6  B 4

Given I have a table with data in the fields ID and Value, but not in
Group_Marker, can I make a SQL
that will find the values in the field Group_Marker as above and
update that field to hold those
values. The field Value holds the data indicating a sequential group,
so record 2, 3 and 4 are
the second group, hence I need the 2 in the field Group_Marker. ID is
the field indicating the sequence.
This is easy to do in code with a simple loop, but not sure now how to
do it in SQL.

RBS
___
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] find sequential groups


On 10/16/2012 4:56 PM, Bart Smissaert wrote:

Trying to make a query that can mark records, indicating them to
belong to a sequential group.
Giving the most simple example:

IDValue   Group_Marker
---
1  D1
2  X 2
3  X 2
4  X 2
5  A 3
6  B 4

Given I have a table with data in the fields ID and Value, but not in
Group_Marker, can I make a SQL
that will find the values in the field Group_Marker as above and
update that field to hold those
values. The field Value holds the data indicating a sequential group,
so record 2, 3 and 4 are
the second group, hence I need the 2 in the field Group_Marker. ID is
the field indicating the sequence.
This is easy to do in code with a simple loop, but not sure now how to
do it in SQL.


Something like this - but note that it's mostly an academic exercise. A 
simple loop would work orders of magnitude faster than this statement.


update MyTable set Group_Marker = (
  select count(*) from MyTable t1
  where t1.ID <= MyTable.ID and t1.Value not in (
select t2.Value from MyTable t2 where t2.ID < t1.ID
order by t2.ID desc limit 1
  )
);

In prose, for each record count the number of records below it 
(inclusive) that are first-in-group; where first-in-group in turn is 
defined as "a record such that the next record down by ID has a 
different Value, or there is no smaller ID at all".

--
Igor Tandetnik

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

[sqlite] find sequential groups

Trying to make a query that can mark records, indicating them to
belong to a sequential group.
Giving the most simple example:

IDValue   Group_Marker
---
1  D1
2  X 2
3  X 2
4  X 2
5  A 3
6  B 4

Given I have a table with data in the fields ID and Value, but not in
Group_Marker, can I make a SQL
that will find the values in the field Group_Marker as above and
update that field to hold those
values. The field Value holds the data indicating a sequential group,
so record 2, 3 and 4 are
the second group, hence I need the 2 in the field Group_Marker. ID is
the field indicating the sequence.
This is easy to do in code with a simple loop, but not sure now how to
do it in SQL.

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

Re: [sqlite] sqlite3.dll no longer operative

2012-10-16 Thread Navaneeth.K.N

On Oct 11, 2012 5:58 PM, "L. Dale Rohl"  wrote:
>
> I am using 64bit Window 7 on my stand alone Toshiba Computer. Recently I
> lost a Hard Drive and had it replaced. All is well but during activation
of
> the computer a pop-up is on the screen that says that "sqlite3.dll" has
been
> lost and I need to download and replace the file.
>
>
>
> I have looked and looked and finally found you and need your assistance
> about how to download this file and cure the problem.

AFAIK, there is no pre built 64 bit version dll available to dowload. You
might have to download amalgamation and compile the dll yourself.

>
>
>
> Thanks for your help.
>
>
>
> Regards,
>
>
>
> Dale
>
>
>
> L. Dale Rohl, President
>
> ROHL MORTGAGE CAPITAL CORPORATION
>
> 602 SE 131st Court
>
> Vancouver, WA 98683-4001
>
> Telephone: 360-944-1440
>
> Mobile: 360-921-6610
>
> FAX: 360-892-4632
>
>
>
> ___
> 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] System.Data.SQLite [nuGet] 1.0.82.0 dll issue

2012-10-16 Thread Bernhard Mogens Ege

I made it work by including "Content" files in the Application folder in the
Setup project.
The setup file installs perfectly now on a clean server. 
Everything is neat now. :)

Bernhard

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: 16. oktober 2012 16:05
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] System.Data.SQLite [nuGet] 1.0.82.0 dll issue

Bernhard Mogens Ege wrote:
> 
> When building a setup project, the interop dll files are not included 
> and hence the program will not work when installed. Only the 
> System.Data.SQLite[.Linq].dll assemblies are included in the setup
project. 
> 

My answer to this is largely "I don't know" as I have never actually used
the Visual Studio "setup" project type.

Unfortunately, NuGet does not have official support for including native
code with a package, see:

https://nuget.codeplex.com/workitem/679

The System.Data.SQLite project uses the "best practices" workaround for this
lack of native code support; however, it is not perfect.  It relies on
treating the native code libraries as "content" and having a small
PowerShell script add them to the project file as such.

The NuGet package for System.Data.SQLite is only intended to support
app-local deployment and in that case it works just fine.  Supporting
anything more would require extra tooling support from NuGet.

--
Joe Mistachkin

___
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] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF

I knew I was missing somethingthanks for the correction and pointer...learn 
something new every day.

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 Igor Tandetnik [itandet...@mvps.org]
Sent: Tuesday, October 16, 2012 8:54 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] 5. Re: Sqlite, Is it possible to calculate the 
length of the longest increasing subsequence using an UDF

Black, Michael (IS)  wrote:
> Pseudo-code:
>
> lastchar='';
> For (char c in array)
>  if (lastchar = '' || c = lastchar+1)
>curseq.push(c);
>  else
>curseq.clear();
>curseq..push(c);
>  end

The longest increasing subsequence doesn't need to be contiguous. Nor does the 
difference between two neighboring elements have to be exactly one - it just 
have to be a positive number. At least as defined at

http://en.wikipedia.org/wiki/Longest_increasing_subsequence

E.g. for a sequence of (1, 100, 2, 4) your algorithm finds (1), while the 
correct answer is (1, 2, 4).
--
Igor Tandetnik

___
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] inserting record datinto mutliple tables with a composite primary key

LMHmedchem  wrote:
> I have data that I am loading into a sqlite database from a text file. I am
> using a composite primary key for four ints for the main table.
> 
> create table Structure (
>   i1 integer not null,
>   i2 integer not null,
>   i3 integer not null,
>   i4 integer not null,
>   name string not null,
>   filePath string not null,
>   SMILES string not null,
>   formula string not null,
>   fw float not null,
>   primary key (i1, i2, i3, i4)
> )
> 
> For this table, there should be a reasonable syntax to insert and select
> using the composite primary key values to find things. My understanding is
> that the composite key values will be hashed, leading to a fast look up. I
> am not entirely sure of the syntax, but I believe that this is a sound
> structure.

No special syntax, just plain

select * from Structure where i1=:value1 and i2=:value2 and i3=:value3 and 
i4=:value4;

SQLite is smart enough to use the key for such a query.

Note that "string" doesn't have any special meaning in SQLite; you are creating 
columns with no affinity. It's better to use "text" instead. For details, see 
http://sqlite.org/datatype3.html

> There is more data for each record that will go into other tables. The
> question I have is how to best keep the data in the different tables in
> registration, meaning to make sure that I can retrieve all of the data from
> the record from all tables using the same 4 primary key values.

If you have a one-to-one relationship between two tables, is there a reason why 
you don't simply combine the two into a single, wider table?

> I could create the same primary key in every table,
> 
> create table Identifier(
>   i1 integer not null,
>   i2 integer not null,
>   i3 integer not null,
>   i4 integer not null,
>   CSpider string,
>   KEGG string,
>   CAS string,
>   primary key (i1, i2, i3, i4)
> )
> 
> but the notion of having multiple primary keys doesn't seem quite right.

Why is that? Basically, every table needs a primary key, whether composite or 
otherwise. If this tuple of integers is the natural key for your data, I don't 
see a problem.

> It also seems as if there should be a way to record the rowid of where a
> record went in the first table and I should be able to use that to insert
> data from the same record to the same rowid of other tables.

There is - see http://sqlite.org/autoinc.html . Change your table to

create table Structure (
   id integer primary key,
   i1 integer not null,
   i2 integer not null,
   i3 integer not null,
   i4 integer not null,
   ...
   unique (i1, i2, i3, i4)
);

Now, you can insert a record while leaving 'id' column out, and it will be 
automatically assigned a unique integer value, which you can retrieve with 
sqlite3_last_insert_rowid. You can then use that ID when inserting records into 
your "satellite" tables.
-- 
Igor Tandetnik

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

Re: [sqlite] System.Data.SQLite [nuGet] 1.0.82.0 dll issue

2012-10-16 Thread Joe Mistachkin

Bernhard Mogens Ege wrote:
> 
> When building a setup project, the interop dll files are not included and
> hence the program will not work when installed. Only the
> System.Data.SQLite[.Linq].dll assemblies are included in the setup
project. 
> 

My answer to this is largely "I don't know" as I have never actually used
the
Visual Studio "setup" project type.

Unfortunately, NuGet does not have official support for including native
code
with a package, see:

https://nuget.codeplex.com/workitem/679

The System.Data.SQLite project uses the "best practices" workaround for this
lack of native code support; however, it is not perfect.  It relies on
treating
the native code libraries as "content" and having a small PowerShell script
add
them to the project file as such.

The NuGet package for System.Data.SQLite is only intended to support
app-local
deployment and in that case it works just fine.  Supporting anything more
would
require extra tooling support from NuGet.

--
Joe Mistachkin

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

[sqlite] 5 readers, 1 writer, shared cache and read uncommitted - repost

2012-10-16 Thread Bob Price

I asked this last Friday evening (which probably wasn't a good time) and didn't 
get any responses, so I thought I would try one more time.  I apologize for the 
duplication.


This is a question to ask about a particular Sqlite usage configuration to see 
if it is appropriate or how to make it better.

A
 brief sketch of the processing need is that I have one process managing
 a lot of "item" data in a Sqlite db, and occasionally there is a need 
to walk through all items and read some stuff from the db, do some 
substantial processing, and write back some new results.  Each item can 
be processed independently.  There is no other competition for accessing
 the database while this is going on.

I have the following Sqlite
 3.7.14.1 configuration that seems to work well, but it is likely not 
common so I would like some validation or alternatives.

 - one process that  uses many threads to do the work
 - WAL journal mode on local in-process Sqlite db
 -
 SQLITE_THREADSAFE=2   SQLITE_TEMP_STORE=2
 - one read-write connection
 - 5 read-only connections
 - all 6 connections use one large shared cache
 - all connections are configured with read-uncommitted transactions
 -
 processing is done in N threads with each processing one item at a 
time, and when needed each thread briefly acquires (with thread 
synchronization) one of the read connections or the write connection to 
read or write from the db and then immediately releases it for another 
thread to use
 - when all work is done then one thread will commit the write connection

This is essentially having 6 connections all participate in a single very large 
Sqlite transaction.  And, it seems to work well.

But, is this a valid and safe usage of Sqlite?  Are there alternatives or other 
configuration settings that would help?

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

Re: [sqlite] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF

Black, Michael (IS)  wrote:
> Pseudo-code:
> 
> lastchar='';
> For (char c in array)
>  if (lastchar = '' || c = lastchar+1)
>curseq.push(c);
>  else
>curseq.clear();
>curseq..push(c);
>  end

The longest increasing subsequence doesn't need to be contiguous. Nor does the 
difference between two neighboring elements have to be exactly one - it just 
have to be a positive number. At least as defined at

http://en.wikipedia.org/wiki/Longest_increasing_subsequence

E.g. for a sequence of (1, 100, 2, 4) your algorithm finds (1), while the 
correct answer is (1, 2, 4).
-- 
Igor Tandetnik

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

[sqlite] System.Data.SQLite [nuGet] 1.0.82.0 dll issue

2012-10-16 Thread Bernhard Mogens Ege

Hi,

 

Now that 1.0.82.0 is available as a nuget package, I tried to use it. 

 

I added the x32/x64 package and this completed without problems.

 

It added the references and a x32 and x64 folder, each with
SQLite.Interop.dll files.

 

My problem:

 

When building a setup project, the interop dll files are not included and
hence the program will not work when installed. Only the
System.Data.SQLite[.Linq].dll assemblies are included in the setup project.

 

The Build Action for the interop dll files is "Content" and "Copy to Output
Directory" is "Copy always".

 

What should I do to have the x32 and x64 interop dlls to be included in the
setup project?

 

VS2010 does not detect that the interop dlls are necessary. :-/

 

Thanks,

 

Bernhard

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

Re: [sqlite] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF

Maybe I'm missing something (wouldn't surprise me) but I can think of O(n) 
traversal of the array for doing this.  Not in SQL of course but you should be 
able to write a user function for it.

Pseudo-code:

lastchar='';
For (char c in array)
  if (lastchar = '' || c = lastchar+1) 
curseq.push(c);
  else
curseq.clear();
curseq..push(c);
  end
  lastchar = c;
  if (curseq.size() > longest.size())
longest = curseq;
  end
end
print longest.size();

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 Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, October 15, 2012 6:05 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] 5. Re: Sqlite, Is it possible to calculate the 
length of the longest increasing subsequence using an UDF

On 10/15/2012 4:29 PM, Frank Chang wrote:
> Igor Tandetnik,
>
 So what is the purpose of this whole exercise
>
> Following the project gurus's example sequence of -- 1,2,3,4,3,5,6,7,8,10
> -- the numeric sorted ascending subsequence is found to be
> 1,2,3,4,5,6,7,8,10 using an automatic variable containing the most recent
> monotically increasing sequence member value and traversing the array
> sequentially in Big-O(linear time).

What will this algorithm do for a sequence 1, 10, 2, 3, 4, 5, 6, 7, 8, 9
? What about 1, 7, 2, 8, 3, 9, 4, 5, 6?

Generally, there is no known algorithm to find the longest subsequence
in O(n) time. You seem to be describing a greedy algorithm: it will
certainly find *some* increasing subsequence, but not necessarily the
longest one.

In any case, you still haven't explained two things that are of interest:

a) Why do you care about the longest increasing subsequence in the first
place? What do you plan to do with it, once found? This is not a purely
academical exercise, I presume.

b) Why does the solution have to be in the form of a SQLite user-defined
function?
--
Igor Tandetnik

___
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] Q: FTS 3-4 for WinRT

2012-10-16 Thread Kenneth Grant

No. I just did not see a definitive statement that it was included in the WinRT 
release. Thanks for the reply.

Sent from my iPad

On Oct 16, 2012, at 8:28 AM, Richard Hipp  wrote:

> On Mon, Oct 15, 2012 at 4:27 PM, Ken Grant  wrote:
> 
>> Q: any plans to have a FTS3/4 WinRT extension for Metro style Win8 apps
>> and if so, when?
> 
> FTS3/4 has been working on WinRT builds for the past three releases.  Are
> you having problems?
> 
> 
>> 
>> Sent from my iPhone
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Q: FTS 3-4 for WinRT

On Mon, Oct 15, 2012 at 4:27 PM, Ken Grant  wrote:

> Q: any plans to have a FTS3/4 WinRT extension for Metro style Win8 apps
> and if so, when?
>

FTS3/4 has been working on WinRT builds for the past three releases.  Are
you having problems?


>
> Sent from my iPhone
> ___
> 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] subscribe to mailing list

On Mon, Oct 15, 2012 at 8:05 PM, LMH  wrote:

> Hello,
>
> I have tried to subscribe to this list by using the "subscribe" button at,
> http://sqlite.1065341.n5.**nabble.com/mailing_list/**
> MailingListOptions.jtp?forum=2
>
> I keep getting a bounced email from mailer-dae...@sam.nabble.com
>
> I don't know if I am subscribed to this group or not. Can someone let me
> know?
>

Go to http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users to
subscribe to this mailing list.



>
> LMHmedchem
> __**_
> 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] Zeroblob initialization memory allocation issue

On Mon, Oct 15, 2012 at 4:15 PM, Petr Kovalev  wrote:

> Hello worldwide Sqlite team!
>
> We've found an issue caused by zeroblob initialization. Depending on the
> table columns creation order, zeroblob initialization causes huge memory
> allocation or not.
>
> Attached sample project is for Mono, but we've tried the same scenario
> with native methods. In first case no additional memory is allocated, but
> in ZeroblobFail method approx 100Mb of memory is allocated for a quick
> period of zeroblob creation.
>
> Could you please help to explain the behavior and provide possible
> workarounds?
>
> Here is quote:
> http://www.sqlite.org/c3ref/bind_blob.html
> «The sqlite3_bind_zeroblob() routine binds a BLOB of length N that is
> filled with zeroes. A zeroblob uses a fixed amount of memory (just an
> integer to hold its size) while it is being processed. Zeroblobs are
> intended to serve as placeholders for BLOBs whose content is later written
> using incremental BLOB I/O
> routines»
>

In order for zeroblobs to work as above (using a fixed amount of memory no
matter how big they are) all zeroblobs must be at the end of the row.  In
other words, the columns of the table that are receiving the zeroblobs must
be the last columns in the table.  If any non-zero content follows the
zeroblob, then the zeroblob is expanded into a literal sequence of zero
bytes, meaning memory must be allocated for the entire zeroblob.



>
> Any advice would be great.
>
> Thanks.
>
> Petr.
>
> ___
> 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

[sqlite] subscribe to mailing list

2012-10-16 Thread LMH


Hello,

I have tried to subscribe to this list by using the "subscribe" button at,
http://sqlite.1065341.n5.nabble.com/mailing_list/MailingListOptions.jtp?forum=2

I keep getting a bounced email from mailer-dae...@sam.nabble.com

I don't know if I am subscribed to this group or not. Can someone let me 
know?


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

[sqlite] inserting record datinto mutliple tables with a composite primary key

2012-10-16 Thread LMHmedchem

Hello,

I have data that I am loading into a sqlite database from a text file. I am
using a composite primary key for four ints for the main table.

create table Structure (
i1 integer not null,
i2 integer not null,
i3 integer not null,
i4 integer not null,
name string not null,
filePath string not null,
SMILES string not null,
formula string not null,
fw float not null,
primary key (i1, i2, i3, i4)
)

For this table, there should be a reasonable syntax to insert and select
using the composite primary key values to find things. My understanding is
that the composite key values will be hashed, leading to a fast look up. I
am not entirely sure of the syntax, but I believe that this is a sound
structure.

There is more data for each record that will go into other tables. The
question I have is how to best keep the data in the different tables in
registration, meaning to make sure that I can retrieve all of the data from
the record from all tables using the same 4 primary key values.

I could create the same primary key in every table,

create table Identifier(
i1 integer not null,
i2 integer not null,
i3 integer not null,
i4 integer not null,
CSpider string,
KEGG string,
CAS string,
primary key (i1, i2, i3, i4)
)

but the notion of having multiple primary keys doesn't seem quite right.

It also seems as if there should be a way to record the rowid of where a
record went in the first table and I should be able to use that to insert
data from the same record to the same rowid of other tables. That assumes
that there would be a way to look up the rowid associated with a set of 4
key values and use that to retrieve data from any table where that rowid was
used to insert data.

Am I going about this in the best way, or even in a reasonable way?
Suggestions and criticisms would be appreciated and a link to some examples
or a tutorial would be fantastic.

LMHmedchem

--
View this message in context:
http://sqlite.1065341.n5.nabble.com/inserting-record-datinto-mutliple-tables-with-a-composite-primary-key-tp64874.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

[sqlite] Q: FTS 3-4 for WinRT

2012-10-16 Thread Ken Grant

Q: any plans to have a FTS3/4 WinRT extension for Metro style Win8 apps and if 
so, when?

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

[sqlite] Zeroblob initialization memory allocation issue

2012-10-16 Thread Petr Kovalev

Hello worldwide Sqlite team!

We've found an issue caused by zeroblob initialization. Depending on the table 
columns creation order, zeroblob initialization causes huge memory allocation 
or not.

Attached sample project is for Mono, but we've tried the same scenario with 
native methods. In first case no additional memory is allocated, but in 
ZeroblobFail method approx 100Mb of memory is allocated for a quick period of 
zeroblob creation.

Could you please help to explain the behavior and provide possible workarounds?

Here is quote:
http://www.sqlite.org/c3ref/bind_blob.html
«The sqlite3_bind_zeroblob() routine binds a BLOB of length N that is filled 
with zeroes. A zeroblob uses a fixed amount of memory (just an integer to hold 
its size) while it is being processed. Zeroblobs are intended to serve as 
placeholders for BLOBs whose content is later written using incremental BLOB 
I/O routines»

Any advice would be great.

Thanks.

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

Re: [sqlite] System.Data.SQLite Field Name are surrounded by double quotes for Views

2012-10-16 Thread Vincent DARON


Thanks a lot !

Le 15/10/12 18:27, Joe Mistachkin a écrit :

Vincent DARON wrote:

There is now way to know if the query is on a View or a Table while
generating the SQL.


The core SQLite library has been enhanced to deal with this situation, here:

http://www.sqlite.org/src/info/5526e0aa3c

--
Joe Mistachkin

___
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] primary key with bulk insert (UNION SELECT)

2012-10-16 Thread Alan Frankel

I ended up modifying the statement to add NULL to the ROWID column for each 
row. SQLite silently replaces the NULL with an automatically generated row id. 
This works even in older versions, so this was the solution I ended up using.

Thanks,
Alan

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Davies
Sent: Friday, October 12, 2012 8:42 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] primary key with bulk insert (UNION SELECT)

On 11 October 2012 15:07, Alan Frankel  wrote:
> I have a table that uses an autogenerated id as primary key. I want to do 
> bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I 
> specify an id for each row:
>
> sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name 
> sqlite> VARCHAR(25), distance REAL); insert into CelestialObject 
> sqlite> select 'Betelguese' as name, 200 as distance UNION SELECT 
> sqlite> 'Procyon', 500;
> Error: table CelestialObject has 3 columns but 2 values were supplied
>
> If I specify AUTOINCREMENT for the id (i.e., "id INTEGER PRIMARY KEY 
> AUTOINCREMENT") when I create the table, the error is the same. Can anyone 
> tell me whether there's a way to use a bulk insert without specifying an id 
> for each row?

insert into CelestialObject( name, distance ) select 'Betelguese' as name, 200 
as distance UNION SELECT 'Procyon', 500;

>
> Thanks,
> Alan
>

Regards,
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] Begin immediate transaction -> SQLITE_BUSY (database is locked)

2012-10-16 Thread Daniel Polski


One of the other threads is writing at the same time.  SQLite only allows a
single writer at a time to a single database file.  Others have to wait in
line.

Your solution is that when you get an SQLITE_BUSY, delay for a short while
and then try again.  Keep trying until you break through.


Note what I do when the application detects that the database is locked:

SQL Error: SQLITE_BUSY[5]: database is locked
0x10d4f8waiting 1 sec <-
0x10d4f8begin immediate transaction
SQL Error: SQLITE_BUSY[5]: database is locked

This loop continue even when all other threads are done writing and 
waiting in an idle state.


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

Re: [sqlite] Begin immediate transaction -> SQLITE_BUSY (database is locked)