Re: [sqlite] INSERT OR UPDATE?

[BareFeetWare]

On 02/07/2011, at 4:54 AM, KeithB wrote:

> Is there any way to perform the equivalent of an INSERT OR UPDATE statement?
> 
> I have something like this:
> 
> CREATE TABLE t1 (id INTEGER PRIMARY KEY, value INTEGER);
> CREATE TABLE t2 (parent INTEGER REFERENCES t1 ON DELETE CASCADE, child
> INTEGER REFERENCES t1);
> INSERT INTO t1 VALUES(1,100);
> INSERT INTO t1 VALUES(2,200);
> INSERT INTO t2 VALUES(1,2);
> 
> Later on, I want to change the value of t1(1,100) to (1,101) or insert
> a new row, I don't know which.

Do this:

begin immediate;
update t1 set value = 101 where id = 1;
insert or ignore into t1 (id, value) values (1, 101);
commit;

The update will only happen if the id already exists. The insert will only 
happen if the id doesn't already exist.

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

[sqlite] current version support wal mode?

[airwolf2026]

Hello,

can current version support wal mode? I try this by add "Pragram journal mode = 
wal;" in connection string;but it doesn't take effect?
how can i do that ?


Best regards,





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

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Darren Duncan]

e-mail mgbg25171 wrote:
> I know that ORDER BY sorts result but I want to sort a table BEFORE it gets
> queried and am not sure of the syntax.
> Here's my test program but...I'm not sure how to PRE-SORT tables t_x and t_y
> (by column pos)  BEFORE I do the SELECT BETWEEN on THEM
> i.e. I purposefully inserted t_d row 1,1,1 to see if it would come out first
> ie as 1. It doesn't any help much appreciated.
> Apologies if my question isn't clear.

Try using a subquery and put your order-by in there.

Rather than this:

   select ...
 from foo
 ...

You can say:

   select ...
 from (select ... from foo ... order by ...)
 ...

Then the order-by is done prior to what the outer query does.

One practical use for doing this is when you are using paged results, such as 
LIMIT/OFFSET gives you, and you just do that on a main recordset in the inner 
query, and then you do much more complicated joins or whatever in the outer 
query, and it is only going to the bother of all those joins/etc against the 
subset of main records you actually want.

If you aren't doing paging but rather some order-sensitive operation, then make 
your inner select return some extra column that contains an order number, such 
as using the RANK() SQL window function would give you.

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

Re: [sqlite] INSERT OR UPDATE?

[Jim Morris]

Or do an update and if no records are modified then do an insert.

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

Re: [sqlite] INSERT OR UPDATE?

[Simon Slavin]

On 1 Jul 2011, at 7:54pm, KeithB wrote:

> Is there any way to perform the equivalent of an INSERT OR UPDATE statement?
> 
> I have something like this:
> 
> CREATE TABLE t1 (id INTEGER PRIMARY KEY, value INTEGER);
> CREATE TABLE t2 (parent INTEGER REFERENCES t1 ON DELETE CASCADE, child
> INTEGER REFERENCES t1);
> INSERT INTO t1 VALUES(1,100);
> INSERT INTO t1 VALUES(2,200);
> INSERT INTO t2 VALUES(1,2);
> 
> Later on, I want to change the value of t1(1,100) to (1,101) or insert
> a new row, I don't know which.
> 
> I realize I can do INSERT OR REPLACE, but if I have a foreign key
> referencing that table with CASCADE DELETE, the delete propagates and
> t2(1,2) is removed.

You have correctly defined primary keys which makes it easy.

First do an INSERT OR IGNORE … which will IGNORE if the row already exists.
Follow that with an UPDATE.

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

[sqlite] INSERT OR UPDATE?

[KeithB]

Is there any way to perform the equivalent of an INSERT OR UPDATE statement?

I have something like this:

CREATE TABLE t1 (id INTEGER PRIMARY KEY, value INTEGER);
CREATE TABLE t2 (parent INTEGER REFERENCES t1 ON DELETE CASCADE, child
INTEGER REFERENCES t1);
INSERT INTO t1 VALUES(1,100);
INSERT INTO t1 VALUES(2,200);
INSERT INTO t2 VALUES(1,2);

Later on, I want to change the value of t1(1,100) to (1,101) or insert
a new row, I don't know which.

I realize I can do INSERT OR REPLACE, but if I have a foreign key
referencing that table with CASCADE DELETE, the delete propagates and
t2(1,2) is removed. Of course I can first test for the existence of
the row with a separate SQL statement, but I want to make sure I'm not
missing something, being an SQL neophyte.

I thought that perhaps I could make my foreign key reference
DEFERRABLE INITIALLY DEFFERED, but that doesn't have any effect on
CASCADE DELETE.

I looked at the documentation for ON CONFLICT clause, but didn't see
anything relevant there [N.B. in the documentation for ON CONFLICT
ABORT, it says "This is the default behavior and the behavior
proscribed the SQL standard". I'm a rookie, but I'm pretty sure that
must mean "the behavior prescribed by the SQL standard" - one letter
difference but caused me some angst.]

Any help is greatly appreciated.

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

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Jim Morris]

The between operator is order dependent.  This variation might work:

SELECT d from T_d
inner join (select min(pos) as xMin, max(pos) as yMax FROM T_x WHERE txt 
= '1990' OR txt='1991') as xcriteria on xPos between xMin and xMax
inner join (select min(pos) as yMin, max(pos) as yMax FROM T_y WHERE txt 
= 'cogs' OR txt='sg expenses') as ycriteria on yPos between yMin and yMax

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

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[David Bicking]

Part of the problem here is that you are misusing the term BETWEEN. In SQL txt 
BETWEEN 1990 AND 1991 means those rows where the value of txt is >= 1990 AND <= 
1991. It has nothing to do with the order of the rows in the table, it is 
purely a comparison of the value of txt in a particular row and the minimum and 
maximum value given in the BETWEEN statement.

Without testing it, I guess your query needs to be:

SELECT d from T_d WHERE 
xpos BETWEEN (SELECT pos FROM T_x WHERE txt = '1990')
 AND (SELECT pos FROM T_x WHERE txt = '1991')
AND
ypos BETWEEN (SELECT pos FROM T_y WHERE txt = 'cogs')
 AND (SELECT pos FROM T_y WHERE txt = 'sg expenses');

David

--- On Fri, 7/1/11, e-mail mgbg25171  wrote:

> From: e-mail mgbg25171 
> Subject: Re: [sqlite] Ensure that query acts on PRE-SORTED tables
> To: "General Discussion of SQLite Database" 
> Date: Friday, July 1, 2011, 1:38 PM
> Just to clarify further "pos BETWEEN
> txt = 1990 and 1991" as its stands
> looks (to my naive eye) like its going to return 1 3 2 and
> if you ORDER BY
> pos then it's going to return 1 2 3
> Neither of which is what I want.
> By contrast if you were to "sort" the table FIRST then "pos
> BETWEEN txt =
> 1990 and 1991" would return 1 2 and this is what I want.
> 
> 
> 
> On 1 July 2011 18:24, e-mail mgbg25171 
> wrote:
> 
> > Here's an example of what I'm trying to do with my
> query
> > t_x
> > rowid=1,pos=1, txt=1990
> > rowid=2,pos=3, txt=1992
> > rowid=3,pos=2, txt=1991
> >
> > t_y
> > rowid=1,pos=3,txt="sg expenses"
> > rowid=2,pos=2,txt="cogs"
> > rowid=3,pos=1,txt='revenue'
> >
> > t_d
> > rowid=1,xpos=1,ypos=1,d=$1
> > rowid=2,xpos=1,ypos=2,d=$2
> > rowid=3,xpos=1,ypos=3,d=$3
> > rowid=4,xpos=3,ypos=1,d=$7
> > rowid=5,xpos=3,ypos=2,d=$8
> > rowid=6,xpos=3,ypos=3,d=$9
> > rowid=7,xpos=2,ypos=1,d=$4
> > rowid=8,xpos=2,ypos=2,d=$5
> > rowid=9,xpos=2,ypos=3,d=$6
> >
> >
> > So in the GUI you'd see t_x as 1990...1991,,,1992 cos
> that's pos order
> >
> > you'd see t_y as
> > revenue
> > cogs
> > sg expenses
> > cos that's pos order
> >
> > and you'd see t_d as
> >         
>    1990  1991   1992
> > revenue    1      2 
>       3
> > cogs        4     
> 5        6
> > sg        7 
>    8        9
> >
> > ie the order in which rows are added i.e. rowid order
> is not the order
> > of the row's position in the GUI (pos order is the GUI
> order)
> >
> > The query is to return those data cells encapsulated
> by the margin text
> > values so...
> > Select all cells BETWEEN 1990 and 1991 and cogs and
> sg
> > The answer would be 4 5 7 8.
> >
> > Does this help to visualise what I'm trying to do?
> >
> >
> >
> >
> > On 1 July 2011 18:14, Pavel Ivanov 
> wrote:
> >
> >> > Its not a very good example because the two
> are adjacent and 'x1' and
> >> 'x2'
> >> > sound like they're adjacent too.
> >>
> >> They are not adjacent - 'x1123456' and a lot of
> other strings starting
> >> with 'x1' are between them.
> >>
> >> > I'm only interested in the results of BETWEEN
> when you're looking at x1
> >> and
> >> > x2 from the pos order perspective
> >>
> >> Then David's query is a way to go.
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171
> >> 
> wrote:
> >> > Pavel, David
> >> > Thanks for bearing with me...
> >> >> "txt BETWEEN 'x1' AND 'x2'" you mean
> those rows between the row where
> >> txt
> >> > = 'x1' and the row where txt = 'x2'
> >> > Yes that's right.
> >> > Its not a very good example because the two
> are adjacent and 'x1' and
> >> 'x2'
> >> > sound like they're adjacent too.
> >> > I'm only interested in the results of BETWEEN
> when you're looking at x1
> >> and
> >> > x2 from the pos order perspective
> >> > ie doing the BETWEEN query on 'x1' and 'x2'
> would be useless from the
> >> rowid
> >> > perspective/order.
> >> > I'll write a better description of what I'm
> trying to do and come back.
> >> >
> >> > On 1 July 2011 17:48, Pavel Ivanov 
> wrote:
> >> >
> >> >> > I'll certainly try
> >> >> >>SELECT pos FROM t_x WHERE txt
> BETWEEN 'x1' AND 'x2' ORDER BY pos;
> >> >> > but I need x1 and x2 to be ordered
> before BETWEEN sees them rather
> >> than
> >> >> the
> >> >> > results just sorted by pos.
> >> >>
> >> >> Maybe I've missed something in this
> conversation? Please clarify how
> >> >> "results sorted by pos" will be different
> from "x1 and x2 to be
> >> >> ordered before BETWEEN sees". And tell us
> more clearly what results
> >> >> you want to see from your query. We
> certainly see that you want to get
> >> >> value of pos from all rows where value of
> txt lies between 'x1' and
> >> >> 'x2'. Now do you want those results to be
> order by value of pos (add
> >> >> ORDER BY pos), or by value of txt (add
> ORDER BY txt), or you want them
> >> >> in a random order (do not add ORDER BY at
> all)? 

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Pavel and David...
I just moved some of the table insertions around to change their rowid
values and the results are STILL coming out in pos order so...
which wasn't what I was getting with my attempts so...
Thank you very much indeed for your advice and solution.
It is appreciated!

On 1 July 2011 18:41, e-mail mgbg25171  wrote:

> > If you believe that
> result of a query differs depending on what order SQLite processes
> rows in then you are wrong.
>
> I am wrong!
>
>
> On 1 July 2011 18:38, e-mail mgbg25171  wrote:
>
>> Just to clarify further "pos BETWEEN txt = 1990 and 1991" as its stands
>> looks (to my naive eye) like its going to return 1 3 2 and if you ORDER BY
>> pos then it's going to return 1 2 3
>> Neither of which is what I want.
>> By contrast if you were to "sort" the table FIRST then "pos BETWEEN txt =
>> 1990 and 1991" would return 1 2 and this is what I want.
>>
>>
>>
>>
>> On 1 July 2011 18:24, e-mail mgbg25171 wrote:
>>
>>> Here's an example of what I'm trying to do with my query
>>> t_x
>>> rowid=1,pos=1, txt=1990
>>> rowid=2,pos=3, txt=1992
>>> rowid=3,pos=2, txt=1991
>>>
>>> t_y
>>> rowid=1,pos=3,txt="sg expenses"
>>> rowid=2,pos=2,txt="cogs"
>>> rowid=3,pos=1,txt='revenue'
>>>
>>> t_d
>>> rowid=1,xpos=1,ypos=1,d=$1
>>> rowid=2,xpos=1,ypos=2,d=$2
>>> rowid=3,xpos=1,ypos=3,d=$3
>>> rowid=4,xpos=3,ypos=1,d=$7
>>> rowid=5,xpos=3,ypos=2,d=$8
>>> rowid=6,xpos=3,ypos=3,d=$9
>>> rowid=7,xpos=2,ypos=1,d=$4
>>> rowid=8,xpos=2,ypos=2,d=$5
>>> rowid=9,xpos=2,ypos=3,d=$6
>>>
>>>
>>> So in the GUI you'd see t_x as 1990...1991,,,1992 cos that's pos order
>>>
>>> you'd see t_y as
>>> revenue
>>> cogs
>>> sg expenses
>>> cos that's pos order
>>>
>>> and you'd see t_d as
>>> 1990  1991   1992
>>> revenue1  23
>>> cogs4  56
>>> sg7 89
>>>
>>> ie the order in which rows are added i.e. rowid order is not the order
>>> of the row's position in the GUI (pos order is the GUI order)
>>>
>>> The query is to return those data cells encapsulated by the margin text
>>> values so...
>>> Select all cells BETWEEN 1990 and 1991 and cogs and sg
>>> The answer would be 4 5 7 8.
>>>
>>> Does this help to visualise what I'm trying to do?
>>>
>>>
>>>
>>>
>>> On 1 July 2011 18:14, Pavel Ivanov  wrote:
>>>
 > Its not a very good example because the two are adjacent and 'x1' and
 'x2'
 > sound like they're adjacent too.

 They are not adjacent - 'x1123456' and a lot of other strings starting
 with 'x1' are between them.

 > I'm only interested in the results of BETWEEN when you're looking at
 x1 and
 > x2 from the pos order perspective

 Then David's query is a way to go.


 Pavel


 On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171
  wrote:
 > Pavel, David
 > Thanks for bearing with me...
 >> "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where
 txt
 > = 'x1' and the row where txt = 'x2'
 > Yes that's right.
 > Its not a very good example because the two are adjacent and 'x1' and
 'x2'
 > sound like they're adjacent too.
 > I'm only interested in the results of BETWEEN when you're looking at
 x1 and
 > x2 from the pos order perspective
 > ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the
 rowid
 > perspective/order.
 > I'll write a better description of what I'm trying to do and come
 back.
 >
 > On 1 July 2011 17:48, Pavel Ivanov  wrote:
 >
 >> > I'll certainly try
 >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
 >> > but I need x1 and x2 to be ordered before BETWEEN sees them rather
 than
 >> the
 >> > results just sorted by pos.
 >>
 >> Maybe I've missed something in this conversation? Please clarify how
 >> "results sorted by pos" will be different from "x1 and x2 to be
 >> ordered before BETWEEN sees". And tell us more clearly what results
 >> you want to see from your query. We certainly see that you want to
 get
 >> value of pos from all rows where value of txt lies between 'x1' and
 >> 'x2'. Now do you want those results to be order by value of pos (add
 >> ORDER BY pos), or by value of txt (add ORDER BY txt), or you want
 them
 >> in a random order (do not add ORDER BY at all)? If you believe that
 >> result of a query differs depending on what order SQLite processes
 >> rows in then you are wrong.
 >>
 >>
 >> Pavel
 >>
 >>
 >> On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171
 >>  wrote:
 >> > I'll certainly try
 >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
 >> > but I need x1 and x2 to be ordered before BETWEEN 

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

> If you believe that
result of a query differs depending on what order SQLite processes
rows in then you are wrong.

I am wrong!

On 1 July 2011 18:38, e-mail mgbg25171  wrote:

> Just to clarify further "pos BETWEEN txt = 1990 and 1991" as its stands
> looks (to my naive eye) like its going to return 1 3 2 and if you ORDER BY
> pos then it's going to return 1 2 3
> Neither of which is what I want.
> By contrast if you were to "sort" the table FIRST then "pos BETWEEN txt =
> 1990 and 1991" would return 1 2 and this is what I want.
>
>
>
>
> On 1 July 2011 18:24, e-mail mgbg25171  wrote:
>
>> Here's an example of what I'm trying to do with my query
>> t_x
>> rowid=1,pos=1, txt=1990
>> rowid=2,pos=3, txt=1992
>> rowid=3,pos=2, txt=1991
>>
>> t_y
>> rowid=1,pos=3,txt="sg expenses"
>> rowid=2,pos=2,txt="cogs"
>> rowid=3,pos=1,txt='revenue'
>>
>> t_d
>> rowid=1,xpos=1,ypos=1,d=$1
>> rowid=2,xpos=1,ypos=2,d=$2
>> rowid=3,xpos=1,ypos=3,d=$3
>> rowid=4,xpos=3,ypos=1,d=$7
>> rowid=5,xpos=3,ypos=2,d=$8
>> rowid=6,xpos=3,ypos=3,d=$9
>> rowid=7,xpos=2,ypos=1,d=$4
>> rowid=8,xpos=2,ypos=2,d=$5
>> rowid=9,xpos=2,ypos=3,d=$6
>>
>>
>> So in the GUI you'd see t_x as 1990...1991,,,1992 cos that's pos order
>>
>> you'd see t_y as
>> revenue
>> cogs
>> sg expenses
>> cos that's pos order
>>
>> and you'd see t_d as
>> 1990  1991   1992
>> revenue1  23
>> cogs4  56
>> sg7 89
>>
>> ie the order in which rows are added i.e. rowid order is not the order
>> of the row's position in the GUI (pos order is the GUI order)
>>
>> The query is to return those data cells encapsulated by the margin text
>> values so...
>> Select all cells BETWEEN 1990 and 1991 and cogs and sg
>> The answer would be 4 5 7 8.
>>
>> Does this help to visualise what I'm trying to do?
>>
>>
>>
>>
>> On 1 July 2011 18:14, Pavel Ivanov  wrote:
>>
>>> > Its not a very good example because the two are adjacent and 'x1' and
>>> 'x2'
>>> > sound like they're adjacent too.
>>>
>>> They are not adjacent - 'x1123456' and a lot of other strings starting
>>> with 'x1' are between them.
>>>
>>> > I'm only interested in the results of BETWEEN when you're looking at x1
>>> and
>>> > x2 from the pos order perspective
>>>
>>> Then David's query is a way to go.
>>>
>>>
>>> Pavel
>>>
>>>
>>> On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171
>>>  wrote:
>>> > Pavel, David
>>> > Thanks for bearing with me...
>>> >> "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where
>>> txt
>>> > = 'x1' and the row where txt = 'x2'
>>> > Yes that's right.
>>> > Its not a very good example because the two are adjacent and 'x1' and
>>> 'x2'
>>> > sound like they're adjacent too.
>>> > I'm only interested in the results of BETWEEN when you're looking at x1
>>> and
>>> > x2 from the pos order perspective
>>> > ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the
>>> rowid
>>> > perspective/order.
>>> > I'll write a better description of what I'm trying to do and come back.
>>> >
>>> > On 1 July 2011 17:48, Pavel Ivanov  wrote:
>>> >
>>> >> > I'll certainly try
>>> >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>>> >> > but I need x1 and x2 to be ordered before BETWEEN sees them rather
>>> than
>>> >> the
>>> >> > results just sorted by pos.
>>> >>
>>> >> Maybe I've missed something in this conversation? Please clarify how
>>> >> "results sorted by pos" will be different from "x1 and x2 to be
>>> >> ordered before BETWEEN sees". And tell us more clearly what results
>>> >> you want to see from your query. We certainly see that you want to get
>>> >> value of pos from all rows where value of txt lies between 'x1' and
>>> >> 'x2'. Now do you want those results to be order by value of pos (add
>>> >> ORDER BY pos), or by value of txt (add ORDER BY txt), or you want them
>>> >> in a random order (do not add ORDER BY at all)? If you believe that
>>> >> result of a query differs depending on what order SQLite processes
>>> >> rows in then you are wrong.
>>> >>
>>> >>
>>> >> Pavel
>>> >>
>>> >>
>>> >> On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171
>>> >>  wrote:
>>> >> > I'll certainly try
>>> >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>>> >> > but I need x1 and x2 to be ordered before BETWEEN sees them rather
>>> than
>>> >> the
>>> >> > results just sorted by pos.
>>> >> >
>>> >> > I've just done this.
>>> >> >const char* sqlSelect ="SELECT d FROM t_d "
>>> >> >"WHERE xpos in "
>>> >> >"(SELECT pos FROM (SELECT * from t_x
>>> ORDER BY
>>> >> > pos) WHERE txt BETWEEN 'x1' AND 'x2')";
>>> >> >//bit for t_y omitted.
>>> >> > in the hope that (SELECT * from t_x ORDER BY pos) presents it's
>>> results
>>> >> to
>>> >> 

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Just to clarify further "pos BETWEEN txt = 1990 and 1991" as its stands
looks (to my naive eye) like its going to return 1 3 2 and if you ORDER BY
pos then it's going to return 1 2 3
Neither of which is what I want.
By contrast if you were to "sort" the table FIRST then "pos BETWEEN txt =
1990 and 1991" would return 1 2 and this is what I want.



On 1 July 2011 18:24, e-mail mgbg25171  wrote:

> Here's an example of what I'm trying to do with my query
> t_x
> rowid=1,pos=1, txt=1990
> rowid=2,pos=3, txt=1992
> rowid=3,pos=2, txt=1991
>
> t_y
> rowid=1,pos=3,txt="sg expenses"
> rowid=2,pos=2,txt="cogs"
> rowid=3,pos=1,txt='revenue'
>
> t_d
> rowid=1,xpos=1,ypos=1,d=$1
> rowid=2,xpos=1,ypos=2,d=$2
> rowid=3,xpos=1,ypos=3,d=$3
> rowid=4,xpos=3,ypos=1,d=$7
> rowid=5,xpos=3,ypos=2,d=$8
> rowid=6,xpos=3,ypos=3,d=$9
> rowid=7,xpos=2,ypos=1,d=$4
> rowid=8,xpos=2,ypos=2,d=$5
> rowid=9,xpos=2,ypos=3,d=$6
>
>
> So in the GUI you'd see t_x as 1990...1991,,,1992 cos that's pos order
>
> you'd see t_y as
> revenue
> cogs
> sg expenses
> cos that's pos order
>
> and you'd see t_d as
> 1990  1991   1992
> revenue1  23
> cogs4  56
> sg7 89
>
> ie the order in which rows are added i.e. rowid order is not the order
> of the row's position in the GUI (pos order is the GUI order)
>
> The query is to return those data cells encapsulated by the margin text
> values so...
> Select all cells BETWEEN 1990 and 1991 and cogs and sg
> The answer would be 4 5 7 8.
>
> Does this help to visualise what I'm trying to do?
>
>
>
>
> On 1 July 2011 18:14, Pavel Ivanov  wrote:
>
>> > Its not a very good example because the two are adjacent and 'x1' and
>> 'x2'
>> > sound like they're adjacent too.
>>
>> They are not adjacent - 'x1123456' and a lot of other strings starting
>> with 'x1' are between them.
>>
>> > I'm only interested in the results of BETWEEN when you're looking at x1
>> and
>> > x2 from the pos order perspective
>>
>> Then David's query is a way to go.
>>
>>
>> Pavel
>>
>>
>> On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171
>>  wrote:
>> > Pavel, David
>> > Thanks for bearing with me...
>> >> "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where
>> txt
>> > = 'x1' and the row where txt = 'x2'
>> > Yes that's right.
>> > Its not a very good example because the two are adjacent and 'x1' and
>> 'x2'
>> > sound like they're adjacent too.
>> > I'm only interested in the results of BETWEEN when you're looking at x1
>> and
>> > x2 from the pos order perspective
>> > ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the
>> rowid
>> > perspective/order.
>> > I'll write a better description of what I'm trying to do and come back.
>> >
>> > On 1 July 2011 17:48, Pavel Ivanov  wrote:
>> >
>> >> > I'll certainly try
>> >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>> >> > but I need x1 and x2 to be ordered before BETWEEN sees them rather
>> than
>> >> the
>> >> > results just sorted by pos.
>> >>
>> >> Maybe I've missed something in this conversation? Please clarify how
>> >> "results sorted by pos" will be different from "x1 and x2 to be
>> >> ordered before BETWEEN sees". And tell us more clearly what results
>> >> you want to see from your query. We certainly see that you want to get
>> >> value of pos from all rows where value of txt lies between 'x1' and
>> >> 'x2'. Now do you want those results to be order by value of pos (add
>> >> ORDER BY pos), or by value of txt (add ORDER BY txt), or you want them
>> >> in a random order (do not add ORDER BY at all)? If you believe that
>> >> result of a query differs depending on what order SQLite processes
>> >> rows in then you are wrong.
>> >>
>> >>
>> >> Pavel
>> >>
>> >>
>> >> On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171
>> >>  wrote:
>> >> > I'll certainly try
>> >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>> >> > but I need x1 and x2 to be ordered before BETWEEN sees them rather
>> than
>> >> the
>> >> > results just sorted by pos.
>> >> >
>> >> > I've just done this.
>> >> >const char* sqlSelect ="SELECT d FROM t_d "
>> >> >"WHERE xpos in "
>> >> >"(SELECT pos FROM (SELECT * from t_x ORDER
>> BY
>> >> > pos) WHERE txt BETWEEN 'x1' AND 'x2')";
>> >> >//bit for t_y omitted.
>> >> > in the hope that (SELECT * from t_x ORDER BY pos) presents it's
>> results
>> >> to
>> >> > SELECT BETWEEN in pos order.
>> >> > I am concerned about having to specify both xpos and pos and am not
>> sure
>> >> how
>> >> > these two get reconciled.
>> >> > I am getting results but want to add more data to the tables to see
>> whats
>> >> > going on.
>> >> >
>> >> > Thank you for your assistance though.
>> >> >
>> >> > On 1 July 2011 17:07, 

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Here's an example of what I'm trying to do with my query
t_x
rowid=1,pos=1, txt=1990
rowid=2,pos=3, txt=1992
rowid=3,pos=2, txt=1991

t_y
rowid=1,pos=3,txt="sg expenses"
rowid=2,pos=2,txt="cogs"
rowid=3,pos=1,txt='revenue'

t_d
rowid=1,xpos=1,ypos=1,d=$1
rowid=2,xpos=1,ypos=2,d=$2
rowid=3,xpos=1,ypos=3,d=$3
rowid=4,xpos=3,ypos=1,d=$7
rowid=5,xpos=3,ypos=2,d=$8
rowid=6,xpos=3,ypos=3,d=$9
rowid=7,xpos=2,ypos=1,d=$4
rowid=8,xpos=2,ypos=2,d=$5
rowid=9,xpos=2,ypos=3,d=$6


So in the GUI you'd see t_x as 1990...1991,,,1992 cos that's pos order

you'd see t_y as
revenue
cogs
sg expenses
cos that's pos order

and you'd see t_d as
1990  1991   1992
revenue1  23
cogs4  56
sg7 89

ie the order in which rows are added i.e. rowid order is not the order
of the row's position in the GUI (pos order is the GUI order)

The query is to return those data cells encapsulated by the margin text
values so...
Select all cells BETWEEN 1990 and 1991 and cogs and sg
The answer would be 4 5 7 8.

Does this help to visualise what I'm trying to do?



On 1 July 2011 18:14, Pavel Ivanov  wrote:

> > Its not a very good example because the two are adjacent and 'x1' and
> 'x2'
> > sound like they're adjacent too.
>
> They are not adjacent - 'x1123456' and a lot of other strings starting
> with 'x1' are between them.
>
> > I'm only interested in the results of BETWEEN when you're looking at x1
> and
> > x2 from the pos order perspective
>
> Then David's query is a way to go.
>
>
> Pavel
>
>
> On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171
>  wrote:
> > Pavel, David
> > Thanks for bearing with me...
> >> "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where
> txt
> > = 'x1' and the row where txt = 'x2'
> > Yes that's right.
> > Its not a very good example because the two are adjacent and 'x1' and
> 'x2'
> > sound like they're adjacent too.
> > I'm only interested in the results of BETWEEN when you're looking at x1
> and
> > x2 from the pos order perspective
> > ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the
> rowid
> > perspective/order.
> > I'll write a better description of what I'm trying to do and come back.
> >
> > On 1 July 2011 17:48, Pavel Ivanov  wrote:
> >
> >> > I'll certainly try
> >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> >> > but I need x1 and x2 to be ordered before BETWEEN sees them rather
> than
> >> the
> >> > results just sorted by pos.
> >>
> >> Maybe I've missed something in this conversation? Please clarify how
> >> "results sorted by pos" will be different from "x1 and x2 to be
> >> ordered before BETWEEN sees". And tell us more clearly what results
> >> you want to see from your query. We certainly see that you want to get
> >> value of pos from all rows where value of txt lies between 'x1' and
> >> 'x2'. Now do you want those results to be order by value of pos (add
> >> ORDER BY pos), or by value of txt (add ORDER BY txt), or you want them
> >> in a random order (do not add ORDER BY at all)? If you believe that
> >> result of a query differs depending on what order SQLite processes
> >> rows in then you are wrong.
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171
> >>  wrote:
> >> > I'll certainly try
> >> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> >> > but I need x1 and x2 to be ordered before BETWEEN sees them rather
> than
> >> the
> >> > results just sorted by pos.
> >> >
> >> > I've just done this.
> >> >const char* sqlSelect ="SELECT d FROM t_d "
> >> >"WHERE xpos in "
> >> >"(SELECT pos FROM (SELECT * from t_x ORDER
> BY
> >> > pos) WHERE txt BETWEEN 'x1' AND 'x2')";
> >> >//bit for t_y omitted.
> >> > in the hope that (SELECT * from t_x ORDER BY pos) presents it's
> results
> >> to
> >> > SELECT BETWEEN in pos order.
> >> > I am concerned about having to specify both xpos and pos and am not
> sure
> >> how
> >> > these two get reconciled.
> >> > I am getting results but want to add more data to the tables to see
> whats
> >> > going on.
> >> >
> >> > Thank you for your assistance though.
> >> >
> >> > On 1 July 2011 17:07, Pavel Ivanov  wrote:
> >> >
> >> >> > It strikes me that
> >> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> >> >> > needs to be operating on the results returned by
> >> >> > SELECT * FROM t_x BY ORDER BY pos
> >> >> > ie another level of query is required but I'm not sure of how you
> >> insert
> >> >> it.
> >> >>
> >> >> I don't understand what you are talking about here. You should write
> >> >> it like this:
> >> >>
> >> >> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> >> >>
> >> >>
> >> >> Pavel
> >> >>
> >> >>
> >> >> On Fri, Jul 1, 2011 at 

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Pavel Ivanov]

> Its not a very good example because the two are adjacent and 'x1' and 'x2'
> sound like they're adjacent too.

They are not adjacent - 'x1123456' and a lot of other strings starting
with 'x1' are between them.

> I'm only interested in the results of BETWEEN when you're looking at x1 and
> x2 from the pos order perspective

Then David's query is a way to go.


Pavel


On Fri, Jul 1, 2011 at 1:04 PM, e-mail mgbg25171
 wrote:
> Pavel, David
> Thanks for bearing with me...
>> "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where txt
> = 'x1' and the row where txt = 'x2'
> Yes that's right.
> Its not a very good example because the two are adjacent and 'x1' and 'x2'
> sound like they're adjacent too.
> I'm only interested in the results of BETWEEN when you're looking at x1 and
> x2 from the pos order perspective
> ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the rowid
> perspective/order.
> I'll write a better description of what I'm trying to do and come back.
>
> On 1 July 2011 17:48, Pavel Ivanov  wrote:
>
>> > I'll certainly try
>> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>> > but I need x1 and x2 to be ordered before BETWEEN sees them rather than
>> the
>> > results just sorted by pos.
>>
>> Maybe I've missed something in this conversation? Please clarify how
>> "results sorted by pos" will be different from "x1 and x2 to be
>> ordered before BETWEEN sees". And tell us more clearly what results
>> you want to see from your query. We certainly see that you want to get
>> value of pos from all rows where value of txt lies between 'x1' and
>> 'x2'. Now do you want those results to be order by value of pos (add
>> ORDER BY pos), or by value of txt (add ORDER BY txt), or you want them
>> in a random order (do not add ORDER BY at all)? If you believe that
>> result of a query differs depending on what order SQLite processes
>> rows in then you are wrong.
>>
>>
>> Pavel
>>
>>
>> On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171
>>  wrote:
>> > I'll certainly try
>> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>> > but I need x1 and x2 to be ordered before BETWEEN sees them rather than
>> the
>> > results just sorted by pos.
>> >
>> > I've just done this.
>> >    const char* sqlSelect =    "SELECT d FROM t_d "
>> >                            "WHERE xpos in "
>> >                            "(SELECT pos FROM (SELECT * from t_x ORDER BY
>> > pos) WHERE txt BETWEEN 'x1' AND 'x2')";
>> >                            //bit for t_y omitted.
>> > in the hope that (SELECT * from t_x ORDER BY pos) presents it's results
>> to
>> > SELECT BETWEEN in pos order.
>> > I am concerned about having to specify both xpos and pos and am not sure
>> how
>> > these two get reconciled.
>> > I am getting results but want to add more data to the tables to see whats
>> > going on.
>> >
>> > Thank you for your assistance though.
>> >
>> > On 1 July 2011 17:07, Pavel Ivanov  wrote:
>> >
>> >> > It strikes me that
>> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> >> > needs to be operating on the results returned by
>> >> > SELECT * FROM t_x BY ORDER BY pos
>> >> > ie another level of query is required but I'm not sure of how you
>> insert
>> >> it.
>> >>
>> >> I don't understand what you are talking about here. You should write
>> >> it like this:
>> >>
>> >> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>> >>
>> >>
>> >> Pavel
>> >>
>> >>
>> >> On Fri, Jul 1, 2011 at 12:02 PM, e-mail mgbg25171
>> >>  wrote:
>> >> > Thx for your suggestion...
>> >> > Yes "BY ORDER BY pos" has to be in there somewhere.
>> >> > It strikes me that
>> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> >> > needs to be operating on the results returned by
>> >> > SELECT * FROM t_x BY ORDER BY pos
>> >> > ie another level of query is required but I'm not sure of how you
>> insert
>> >> it.
>> >> > I'll have a play.
>> >> >
>> >> >
>> >> >
>> >> > On 1 July 2011 16:12, Pavel Ivanov  wrote:
>> >> >
>> >> >> > What I want to do is...make sure that when I say BETWEEN I really
>> mean
>> >> eg
>> >> >> > BETWEEN x1 and x2 when you look at the table as if it's ordered by
>> pos
>> >> >> and
>> >> >> > not rowid.
>> >> >>
>> >> >> So, can you add "ORDER BY pos" to your queries?
>> >> >>
>> >> >>
>> >> >> Pavel
>> >> >>
>> >> >>
>> >> >> On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
>> >> >>  wrote:
>> >> >> > Thank you all for your responses.
>> >> >> > I had to go out after posting and have just come back.
>> >> >> > My concern is with...
>> >> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> >> >> > and
>> >> >> > SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.
>> >> >> >
>> >> >> > t_x and t_y are dimension tables.
>> >> >> > that hold the x and y 

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Pavel, David
Thanks for bearing with me...
> "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where txt
= 'x1' and the row where txt = 'x2'
Yes that's right.
Its not a very good example because the two are adjacent and 'x1' and 'x2'
sound like they're adjacent too.
I'm only interested in the results of BETWEEN when you're looking at x1 and
x2 from the pos order perspective
ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the rowid
perspective/order.
I'll write a better description of what I'm trying to do and come back.

On 1 July 2011 17:48, Pavel Ivanov  wrote:

> > I'll certainly try
> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> > but I need x1 and x2 to be ordered before BETWEEN sees them rather than
> the
> > results just sorted by pos.
>
> Maybe I've missed something in this conversation? Please clarify how
> "results sorted by pos" will be different from "x1 and x2 to be
> ordered before BETWEEN sees". And tell us more clearly what results
> you want to see from your query. We certainly see that you want to get
> value of pos from all rows where value of txt lies between 'x1' and
> 'x2'. Now do you want those results to be order by value of pos (add
> ORDER BY pos), or by value of txt (add ORDER BY txt), or you want them
> in a random order (do not add ORDER BY at all)? If you believe that
> result of a query differs depending on what order SQLite processes
> rows in then you are wrong.
>
>
> Pavel
>
>
> On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171
>  wrote:
> > I'll certainly try
> >>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> > but I need x1 and x2 to be ordered before BETWEEN sees them rather than
> the
> > results just sorted by pos.
> >
> > I've just done this.
> >const char* sqlSelect ="SELECT d FROM t_d "
> >"WHERE xpos in "
> >"(SELECT pos FROM (SELECT * from t_x ORDER BY
> > pos) WHERE txt BETWEEN 'x1' AND 'x2')";
> >//bit for t_y omitted.
> > in the hope that (SELECT * from t_x ORDER BY pos) presents it's results
> to
> > SELECT BETWEEN in pos order.
> > I am concerned about having to specify both xpos and pos and am not sure
> how
> > these two get reconciled.
> > I am getting results but want to add more data to the tables to see whats
> > going on.
> >
> > Thank you for your assistance though.
> >
> > On 1 July 2011 17:07, Pavel Ivanov  wrote:
> >
> >> > It strikes me that
> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> >> > needs to be operating on the results returned by
> >> > SELECT * FROM t_x BY ORDER BY pos
> >> > ie another level of query is required but I'm not sure of how you
> insert
> >> it.
> >>
> >> I don't understand what you are talking about here. You should write
> >> it like this:
> >>
> >> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Fri, Jul 1, 2011 at 12:02 PM, e-mail mgbg25171
> >>  wrote:
> >> > Thx for your suggestion...
> >> > Yes "BY ORDER BY pos" has to be in there somewhere.
> >> > It strikes me that
> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> >> > needs to be operating on the results returned by
> >> > SELECT * FROM t_x BY ORDER BY pos
> >> > ie another level of query is required but I'm not sure of how you
> insert
> >> it.
> >> > I'll have a play.
> >> >
> >> >
> >> >
> >> > On 1 July 2011 16:12, Pavel Ivanov  wrote:
> >> >
> >> >> > What I want to do is...make sure that when I say BETWEEN I really
> mean
> >> eg
> >> >> > BETWEEN x1 and x2 when you look at the table as if it's ordered by
> pos
> >> >> and
> >> >> > not rowid.
> >> >>
> >> >> So, can you add "ORDER BY pos" to your queries?
> >> >>
> >> >>
> >> >> Pavel
> >> >>
> >> >>
> >> >> On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
> >> >>  wrote:
> >> >> > Thank you all for your responses.
> >> >> > I had to go out after posting and have just come back.
> >> >> > My concern is with...
> >> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> >> >> > and
> >> >> > SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.
> >> >> >
> >> >> > t_x and t_y are dimension tables.
> >> >> > that hold the x and y margins of a spreadsheet.
> >> >> > The margins will have an implied order shown by pos
> >> >> > which will differ from the order in which rows are added
> (represented
> >> by
> >> >> > rowid).
> >> >> >
> >> >> > What I want to do is...make sure that when I say BETWEEN I really
> mean
> >> eg
> >> >> > BETWEEN x1 and x2 when you look at the table as if it's ordered by
> pos
> >> >> and
> >> >> > not rowid. I hope that helps explain why pos exists and is not
> rowid
> >> i.e.
> >> >> I
> >> >> > want to be able to "insert" and "delete" records "!in between" the
> >> >> existing
> >> >> 

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Pavel Ivanov]

> I'll certainly try
>>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> but I need x1 and x2 to be ordered before BETWEEN sees them rather than the
> results just sorted by pos.

Maybe I've missed something in this conversation? Please clarify how
"results sorted by pos" will be different from "x1 and x2 to be
ordered before BETWEEN sees". And tell us more clearly what results
you want to see from your query. We certainly see that you want to get
value of pos from all rows where value of txt lies between 'x1' and
'x2'. Now do you want those results to be order by value of pos (add
ORDER BY pos), or by value of txt (add ORDER BY txt), or you want them
in a random order (do not add ORDER BY at all)? If you believe that
result of a query differs depending on what order SQLite processes
rows in then you are wrong.


Pavel


On Fri, Jul 1, 2011 at 12:34 PM, e-mail mgbg25171
 wrote:
> I'll certainly try
>>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
> but I need x1 and x2 to be ordered before BETWEEN sees them rather than the
> results just sorted by pos.
>
> I've just done this.
>    const char* sqlSelect =    "SELECT d FROM t_d "
>                            "WHERE xpos in "
>                            "(SELECT pos FROM (SELECT * from t_x ORDER BY
> pos) WHERE txt BETWEEN 'x1' AND 'x2')";
>                            //bit for t_y omitted.
> in the hope that (SELECT * from t_x ORDER BY pos) presents it's results to
> SELECT BETWEEN in pos order.
> I am concerned about having to specify both xpos and pos and am not sure how
> these two get reconciled.
> I am getting results but want to add more data to the tables to see whats
> going on.
>
> Thank you for your assistance though.
>
> On 1 July 2011 17:07, Pavel Ivanov  wrote:
>
>> > It strikes me that
>> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> > needs to be operating on the results returned by
>> > SELECT * FROM t_x BY ORDER BY pos
>> > ie another level of query is required but I'm not sure of how you insert
>> it.
>>
>> I don't understand what you are talking about here. You should write
>> it like this:
>>
>> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>>
>>
>> Pavel
>>
>>
>> On Fri, Jul 1, 2011 at 12:02 PM, e-mail mgbg25171
>>  wrote:
>> > Thx for your suggestion...
>> > Yes "BY ORDER BY pos" has to be in there somewhere.
>> > It strikes me that
>> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> > needs to be operating on the results returned by
>> > SELECT * FROM t_x BY ORDER BY pos
>> > ie another level of query is required but I'm not sure of how you insert
>> it.
>> > I'll have a play.
>> >
>> >
>> >
>> > On 1 July 2011 16:12, Pavel Ivanov  wrote:
>> >
>> >> > What I want to do is...make sure that when I say BETWEEN I really mean
>> eg
>> >> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
>> >> and
>> >> > not rowid.
>> >>
>> >> So, can you add "ORDER BY pos" to your queries?
>> >>
>> >>
>> >> Pavel
>> >>
>> >>
>> >> On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
>> >>  wrote:
>> >> > Thank you all for your responses.
>> >> > I had to go out after posting and have just come back.
>> >> > My concern is with...
>> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> >> > and
>> >> > SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.
>> >> >
>> >> > t_x and t_y are dimension tables.
>> >> > that hold the x and y margins of a spreadsheet.
>> >> > The margins will have an implied order shown by pos
>> >> > which will differ from the order in which rows are added (represented
>> by
>> >> > rowid).
>> >> >
>> >> > What I want to do is...make sure that when I say BETWEEN I really mean
>> eg
>> >> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
>> >> and
>> >> > not rowid. I hope that helps explain why pos exists and is not rowid
>> i.e.
>> >> I
>> >> > want to be able to "insert" and "delete" records "!in between" the
>> >> existing
>> >> > ones or at least make it look like that even if the records are
>> >> physically
>> >> > appended to the tables.
>> >> > Hope this clarifies things and look forward to your thoughts.
>> >> >
>> >> >
>> >> > On 1 July 2011 15:30, Pavel Ivanov  wrote:
>> >> >
>> >> >> >> Putting the 'ORDER BY' clause in view won't work?
>> >> >> >
>> >> >> > It will work just fine, in that the results you see will appear in
>> the
>> >> >> ORDER you asked for.
>> >> >>
>> >> >> I believe that's not always true and is not required by SQL standard.
>> >> >> Most probably 'select * from view_name' will return rows in the order
>> >> >> written in the view. But 'select * from view_name where some_column =
>> >> >> some_value' can already return rows in completely different order.
>> And
>> >> >> 'select * from table_name, view_name where some_condition' will

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[David Bicking]

I'm not entirely sure what your data looks like, but I am thinking that when 
you say "txt BETWEEN 'x1' AND 'x2'" you mean those rows between the row where 
txt = 'x1' and the row where txt = 'x2'

If that is the case, maybe this will give you what you want:

SELECT POS FROM T_x WHERE POS BETWEEN 
  (SELECT POS FROM T_x WHERE txt = 'x1')
AND
  (SELECT POS FROM T_x WHERE txt = 'x2');

Hopefully I have guessed your need somewhat correctly,
David


--- On Fri, 7/1/11, e-mail mgbg25171  wrote:

> From: e-mail mgbg25171 
> Subject: Re: [sqlite] Ensure that query acts on PRE-SORTED tables
> To: "General Discussion of SQLite Database" 
> Date: Friday, July 1, 2011, 12:02 PM
> Thx for your suggestion...
> Yes "BY ORDER BY pos" has to be in there somewhere.
> It strikes me that
> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> needs to be operating on the results returned by
> SELECT * FROM t_x BY ORDER BY pos
> ie another level of query is required but I'm not sure of
> how you insert it.
> I'll have a play.
> 
> 
> 
> On 1 July 2011 16:12, Pavel Ivanov 
> wrote:
> 
> > > What I want to do is...make sure that when I say
> BETWEEN I really mean eg
> > > BETWEEN x1 and x2 when you look at the table as
> if it's ordered by pos
> > and
> > > not rowid.
> >
> > So, can you add "ORDER BY pos" to your queries?
> >
> >
> > Pavel
> >
> >
> > On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
> > 
> wrote:
> > > Thank you all for your responses.
> > > I had to go out after posting and have just come
> back.
> > > My concern is with...
> > > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND
> 'x2'
> > > and
> > > SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND
> 'y2'.
> > >
> > > t_x and t_y are dimension tables.
> > > that hold the x and y margins of a spreadsheet.
> > > The margins will have an implied order shown by
> pos
> > > which will differ from the order in which rows
> are added (represented by
> > > rowid).
> > >
> > > What I want to do is...make sure that when I say
> BETWEEN I really mean eg
> > > BETWEEN x1 and x2 when you look at the table as
> if it's ordered by pos
> > and
> > > not rowid. I hope that helps explain why pos
> exists and is not rowid i.e.
> > I
> > > want to be able to "insert" and "delete" records
> "!in between" the
> > existing
> > > ones or at least make it look like that even if
> the records are
> > physically
> > > appended to the tables.
> > > Hope this clarifies things and look forward to
> your thoughts.
> > >
> > >
> > > On 1 July 2011 15:30, Pavel Ivanov 
> wrote:
> > >
> > >> >> Putting the 'ORDER BY' clause in
> view won't work?
> > >> >
> > >> > It will work just fine, in that the
> results you see will appear in the
> > >> ORDER you asked for.
> > >>
> > >> I believe that's not always true and is not
> required by SQL standard.
> > >> Most probably 'select * from view_name' will
> return rows in the order
> > >> written in the view. But 'select * from
> view_name where some_column =
> > >> some_value' can already return rows in
> completely different order. And
> > >> 'select * from table_name, view_name where
> some_condition' will almost
> > >> certainly ignore any ORDER BY in the view.
> > >>
> > >> So ORDER BY in the view doesn't guarantee you
> anything.
> > >>
> > >>
> > >> Pavel
> > >>
> > >>
> > >> On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin
> 
> > >> wrote:
> > >> >
> > >> > On 1 Jul 2011, at 3:07pm, Alessandro
> Marzocchi wrote:
> > >> >
> > >> >> 2011/7/1 Simon Slavin 
> > >> >>
> > >> >>> On 1 Jul 2011, at 11:20am,
> Alessandro Marzocchi wrote:
> > >> >>>
> > >>  Isn't it possible to use a
> view for that?
> > >> >>>
> > >> >>> You can use a VIEW if you want,
> but VIEWs don't sort the table
> > either.
> > >>  A
> > >> >>> VIEW is just a way of saving a
> SELECT query.  When you consult the
> > VIEW
> > >> >>> SQLite executes the SELECT.
> > >> >>
> > >> >> Putting the 'ORDER BY' clause in
> view won't work?
> > >> >
> > >> > It will work just fine, in that the
> results you see will appear in the
> > >> ORDER you asked for.
> > >> >
> > >> > However, it has no influence on how data
> is stored.  In fact no table
> > >> data is stored for a VIEW at all.  The
> thing stored is the parameters
> > given
> > >> when you created the VIEW.  Every time
> you refer to a VIEW in a SQL
> > >> statement SQL goes back and looks at the VIEW
> specification again.
> > >> >
> > >> > 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] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

I'll certainly try
>SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
but I need x1 and x2 to be ordered before BETWEEN sees them rather than the
results just sorted by pos.

I've just done this.
const char* sqlSelect ="SELECT d FROM t_d "
"WHERE xpos in "
"(SELECT pos FROM (SELECT * from t_x ORDER BY
pos) WHERE txt BETWEEN 'x1' AND 'x2')";
//bit for t_y omitted.
in the hope that (SELECT * from t_x ORDER BY pos) presents it's results to
SELECT BETWEEN in pos order.
I am concerned about having to specify both xpos and pos and am not sure how
these two get reconciled.
I am getting results but want to add more data to the tables to see whats
going on.

Thank you for your assistance though.

On 1 July 2011 17:07, Pavel Ivanov  wrote:

> > It strikes me that
> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> > needs to be operating on the results returned by
> > SELECT * FROM t_x BY ORDER BY pos
> > ie another level of query is required but I'm not sure of how you insert
> it.
>
> I don't understand what you are talking about here. You should write
> it like this:
>
> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;
>
>
> Pavel
>
>
> On Fri, Jul 1, 2011 at 12:02 PM, e-mail mgbg25171
>  wrote:
> > Thx for your suggestion...
> > Yes "BY ORDER BY pos" has to be in there somewhere.
> > It strikes me that
> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> > needs to be operating on the results returned by
> > SELECT * FROM t_x BY ORDER BY pos
> > ie another level of query is required but I'm not sure of how you insert
> it.
> > I'll have a play.
> >
> >
> >
> > On 1 July 2011 16:12, Pavel Ivanov  wrote:
> >
> >> > What I want to do is...make sure that when I say BETWEEN I really mean
> eg
> >> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
> >> and
> >> > not rowid.
> >>
> >> So, can you add "ORDER BY pos" to your queries?
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
> >>  wrote:
> >> > Thank you all for your responses.
> >> > I had to go out after posting and have just come back.
> >> > My concern is with...
> >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> >> > and
> >> > SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.
> >> >
> >> > t_x and t_y are dimension tables.
> >> > that hold the x and y margins of a spreadsheet.
> >> > The margins will have an implied order shown by pos
> >> > which will differ from the order in which rows are added (represented
> by
> >> > rowid).
> >> >
> >> > What I want to do is...make sure that when I say BETWEEN I really mean
> eg
> >> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
> >> and
> >> > not rowid. I hope that helps explain why pos exists and is not rowid
> i.e.
> >> I
> >> > want to be able to "insert" and "delete" records "!in between" the
> >> existing
> >> > ones or at least make it look like that even if the records are
> >> physically
> >> > appended to the tables.
> >> > Hope this clarifies things and look forward to your thoughts.
> >> >
> >> >
> >> > On 1 July 2011 15:30, Pavel Ivanov  wrote:
> >> >
> >> >> >> Putting the 'ORDER BY' clause in view won't work?
> >> >> >
> >> >> > It will work just fine, in that the results you see will appear in
> the
> >> >> ORDER you asked for.
> >> >>
> >> >> I believe that's not always true and is not required by SQL standard.
> >> >> Most probably 'select * from view_name' will return rows in the order
> >> >> written in the view. But 'select * from view_name where some_column =
> >> >> some_value' can already return rows in completely different order.
> And
> >> >> 'select * from table_name, view_name where some_condition' will
> almost
> >> >> certainly ignore any ORDER BY in the view.
> >> >>
> >> >> So ORDER BY in the view doesn't guarantee you anything.
> >> >>
> >> >>
> >> >> Pavel
> >> >>
> >> >>
> >> >> On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin 
> >> >> wrote:
> >> >> >
> >> >> > On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:
> >> >> >
> >> >> >> 2011/7/1 Simon Slavin 
> >> >> >>
> >> >> >>> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
> >> >> >>>
> >> >>  Isn't it possible to use a view for that?
> >> >> >>>
> >> >> >>> You can use a VIEW if you want, but VIEWs don't sort the table
> >> either.
> >> >>  A
> >> >> >>> VIEW is just a way of saving a SELECT query.  When you consult
> the
> >> VIEW
> >> >> >>> SQLite executes the SELECT.
> >> >> >>
> >> >> >> Putting the 'ORDER BY' clause in view won't work?
> >> >> >
> >> >> > It will work just fine, in that the results you see will appear in
> the
> >> >> ORDER you asked for.
> >> >> >
> >> >> > However, it has no influence on how data is 

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Pavel Ivanov]

> It strikes me that
> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> needs to be operating on the results returned by
> SELECT * FROM t_x BY ORDER BY pos
> ie another level of query is required but I'm not sure of how you insert it.

I don't understand what you are talking about here. You should write
it like this:

SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' ORDER BY pos;


Pavel


On Fri, Jul 1, 2011 at 12:02 PM, e-mail mgbg25171
 wrote:
> Thx for your suggestion...
> Yes "BY ORDER BY pos" has to be in there somewhere.
> It strikes me that
> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> needs to be operating on the results returned by
> SELECT * FROM t_x BY ORDER BY pos
> ie another level of query is required but I'm not sure of how you insert it.
> I'll have a play.
>
>
>
> On 1 July 2011 16:12, Pavel Ivanov  wrote:
>
>> > What I want to do is...make sure that when I say BETWEEN I really mean eg
>> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
>> and
>> > not rowid.
>>
>> So, can you add "ORDER BY pos" to your queries?
>>
>>
>> Pavel
>>
>>
>> On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
>>  wrote:
>> > Thank you all for your responses.
>> > I had to go out after posting and have just come back.
>> > My concern is with...
>> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
>> > and
>> > SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.
>> >
>> > t_x and t_y are dimension tables.
>> > that hold the x and y margins of a spreadsheet.
>> > The margins will have an implied order shown by pos
>> > which will differ from the order in which rows are added (represented by
>> > rowid).
>> >
>> > What I want to do is...make sure that when I say BETWEEN I really mean eg
>> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
>> and
>> > not rowid. I hope that helps explain why pos exists and is not rowid i.e.
>> I
>> > want to be able to "insert" and "delete" records "!in between" the
>> existing
>> > ones or at least make it look like that even if the records are
>> physically
>> > appended to the tables.
>> > Hope this clarifies things and look forward to your thoughts.
>> >
>> >
>> > On 1 July 2011 15:30, Pavel Ivanov  wrote:
>> >
>> >> >> Putting the 'ORDER BY' clause in view won't work?
>> >> >
>> >> > It will work just fine, in that the results you see will appear in the
>> >> ORDER you asked for.
>> >>
>> >> I believe that's not always true and is not required by SQL standard.
>> >> Most probably 'select * from view_name' will return rows in the order
>> >> written in the view. But 'select * from view_name where some_column =
>> >> some_value' can already return rows in completely different order. And
>> >> 'select * from table_name, view_name where some_condition' will almost
>> >> certainly ignore any ORDER BY in the view.
>> >>
>> >> So ORDER BY in the view doesn't guarantee you anything.
>> >>
>> >>
>> >> Pavel
>> >>
>> >>
>> >> On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin 
>> >> wrote:
>> >> >
>> >> > On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:
>> >> >
>> >> >> 2011/7/1 Simon Slavin 
>> >> >>
>> >> >>> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
>> >> >>>
>> >>  Isn't it possible to use a view for that?
>> >> >>>
>> >> >>> You can use a VIEW if you want, but VIEWs don't sort the table
>> either.
>> >>  A
>> >> >>> VIEW is just a way of saving a SELECT query.  When you consult the
>> VIEW
>> >> >>> SQLite executes the SELECT.
>> >> >>
>> >> >> Putting the 'ORDER BY' clause in view won't work?
>> >> >
>> >> > It will work just fine, in that the results you see will appear in the
>> >> ORDER you asked for.
>> >> >
>> >> > However, it has no influence on how data is stored.  In fact no table
>> >> data is stored for a VIEW at all.  The thing stored is the parameters
>> given
>> >> when you created the VIEW.  Every time you refer to a VIEW in a SQL
>> >> statement SQL goes back and looks at the VIEW specification again.
>> >> >
>> >> > 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
>> >>
>> > ___
>> > 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
> 

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Thx for your suggestion...
Yes "BY ORDER BY pos" has to be in there somewhere.
It strikes me that
SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
needs to be operating on the results returned by
SELECT * FROM t_x BY ORDER BY pos
ie another level of query is required but I'm not sure of how you insert it.
I'll have a play.



On 1 July 2011 16:12, Pavel Ivanov  wrote:

> > What I want to do is...make sure that when I say BETWEEN I really mean eg
> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
> and
> > not rowid.
>
> So, can you add "ORDER BY pos" to your queries?
>
>
> Pavel
>
>
> On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
>  wrote:
> > Thank you all for your responses.
> > I had to go out after posting and have just come back.
> > My concern is with...
> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> > and
> > SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.
> >
> > t_x and t_y are dimension tables.
> > that hold the x and y margins of a spreadsheet.
> > The margins will have an implied order shown by pos
> > which will differ from the order in which rows are added (represented by
> > rowid).
> >
> > What I want to do is...make sure that when I say BETWEEN I really mean eg
> > BETWEEN x1 and x2 when you look at the table as if it's ordered by pos
> and
> > not rowid. I hope that helps explain why pos exists and is not rowid i.e.
> I
> > want to be able to "insert" and "delete" records "!in between" the
> existing
> > ones or at least make it look like that even if the records are
> physically
> > appended to the tables.
> > Hope this clarifies things and look forward to your thoughts.
> >
> >
> > On 1 July 2011 15:30, Pavel Ivanov  wrote:
> >
> >> >> Putting the 'ORDER BY' clause in view won't work?
> >> >
> >> > It will work just fine, in that the results you see will appear in the
> >> ORDER you asked for.
> >>
> >> I believe that's not always true and is not required by SQL standard.
> >> Most probably 'select * from view_name' will return rows in the order
> >> written in the view. But 'select * from view_name where some_column =
> >> some_value' can already return rows in completely different order. And
> >> 'select * from table_name, view_name where some_condition' will almost
> >> certainly ignore any ORDER BY in the view.
> >>
> >> So ORDER BY in the view doesn't guarantee you anything.
> >>
> >>
> >> Pavel
> >>
> >>
> >> On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin 
> >> wrote:
> >> >
> >> > On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:
> >> >
> >> >> 2011/7/1 Simon Slavin 
> >> >>
> >> >>> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
> >> >>>
> >>  Isn't it possible to use a view for that?
> >> >>>
> >> >>> You can use a VIEW if you want, but VIEWs don't sort the table
> either.
> >>  A
> >> >>> VIEW is just a way of saving a SELECT query.  When you consult the
> VIEW
> >> >>> SQLite executes the SELECT.
> >> >>
> >> >> Putting the 'ORDER BY' clause in view won't work?
> >> >
> >> > It will work just fine, in that the results you see will appear in the
> >> ORDER you asked for.
> >> >
> >> > However, it has no influence on how data is stored.  In fact no table
> >> data is stored for a VIEW at all.  The thing stored is the parameters
> given
> >> when you created the VIEW.  Every time you refer to a VIEW in a SQL
> >> statement SQL goes back and looks at the VIEW specification again.
> >> >
> >> > 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
> >>
> > ___
> > 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] sqlite3_create_function and threads

[Pavel Ivanov]

>  > Yes. What's wrong with that?
>
> Nothing at all - I just needed to know whether that was the case so I
> could design certain sections of my code accordingly.

Three question marks of yours suggested me that you think it's awfully wrong.

Note that although your function and application pointer will be
shared by all threads SQLite (or in some cases you) will guarantee
that this function won't be called simultaneously from different
threads for this connection. So you need to introduce some additional
thread-safety only if you use the same function and same application
pointer for different simultaneous connections.


Pavel


On Fri, Jul 1, 2011 at 11:11 AM, Technology Lighthouse
 wrote:
>  > Yes. What's wrong with that?
>
> Nothing at all - I just needed to know whether that was the case so I
> could design certain sections of my code accordingly.
>
> Thanks for the help!
> --
> Paul Roberts
> ___
> 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] sqlite3_create_function and threads

[Richard Hipp]

On Fri, Jul 1, 2011 at 11:02 AM, Pavel Ivanov  wrote:

> > To put it another way, if I call sqlite3_create_function to install a
> > custom function, is that function now available to all threads using
> > SQLite or is it available only to the thread that made the
> > sqlite3_create_function call?
>
> Yes, it's available to all threads using the same connection.
>

Pavel is exactly right.  But it is important to read and understand the
qualifier that Pavel attaches to his answer.

One suspects that the OP is working under the assumption that
thread==connection, in which case the answer would be "No".  Only if all
threads share the same database connection is the answer "Yes".


>
> > And does the same rule apply to the application-defined pointer that can
> > optionally be supplied in the sqlite3_create_function call via parameter
> > 5???
>
> Yes. What's wrong with that?
>
>
> Pavel
>
>
> On Fri, Jul 1, 2011 at 10:53 AM, Technology Lighthouse
>  wrote:
> >  From the docs it's unclear to me whether the use of
> > sqlite3_create_function is thread-specific or not.
> >
> > To put it another way, if I call sqlite3_create_function to install a
> > custom function, is that function now available to all threads using
> > SQLite or is it available only to the thread that made the
> > sqlite3_create_function call?
> >
> > And does the same rule apply to the application-defined pointer that can
> > optionally be supplied in the sqlite3_create_function call via parameter
> > 5???
> >
> > --
> > Paul Roberts
> > ___
> > 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
>



-- 
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] Ensure that query acts on PRE-SORTED tables

[Pavel Ivanov]

> What I want to do is...make sure that when I say BETWEEN I really mean eg
> BETWEEN x1 and x2 when you look at the table as if it's ordered by pos and
> not rowid.

So, can you add "ORDER BY pos" to your queries?


Pavel


On Fri, Jul 1, 2011 at 11:04 AM, e-mail mgbg25171
 wrote:
> Thank you all for your responses.
> I had to go out after posting and have just come back.
> My concern is with...
> SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
> and
> SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.
>
> t_x and t_y are dimension tables.
> that hold the x and y margins of a spreadsheet.
> The margins will have an implied order shown by pos
> which will differ from the order in which rows are added (represented by
> rowid).
>
> What I want to do is...make sure that when I say BETWEEN I really mean eg
> BETWEEN x1 and x2 when you look at the table as if it's ordered by pos and
> not rowid. I hope that helps explain why pos exists and is not rowid i.e. I
> want to be able to "insert" and "delete" records "!in between" the existing
> ones or at least make it look like that even if the records are physically
> appended to the tables.
> Hope this clarifies things and look forward to your thoughts.
>
>
> On 1 July 2011 15:30, Pavel Ivanov  wrote:
>
>> >> Putting the 'ORDER BY' clause in view won't work?
>> >
>> > It will work just fine, in that the results you see will appear in the
>> ORDER you asked for.
>>
>> I believe that's not always true and is not required by SQL standard.
>> Most probably 'select * from view_name' will return rows in the order
>> written in the view. But 'select * from view_name where some_column =
>> some_value' can already return rows in completely different order. And
>> 'select * from table_name, view_name where some_condition' will almost
>> certainly ignore any ORDER BY in the view.
>>
>> So ORDER BY in the view doesn't guarantee you anything.
>>
>>
>> Pavel
>>
>>
>> On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin 
>> wrote:
>> >
>> > On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:
>> >
>> >> 2011/7/1 Simon Slavin 
>> >>
>> >>> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
>> >>>
>>  Isn't it possible to use a view for that?
>> >>>
>> >>> You can use a VIEW if you want, but VIEWs don't sort the table either.
>>  A
>> >>> VIEW is just a way of saving a SELECT query.  When you consult the VIEW
>> >>> SQLite executes the SELECT.
>> >>
>> >> Putting the 'ORDER BY' clause in view won't work?
>> >
>> > It will work just fine, in that the results you see will appear in the
>> ORDER you asked for.
>> >
>> > However, it has no influence on how data is stored.  In fact no table
>> data is stored for a VIEW at all.  The thing stored is the parameters given
>> when you created the VIEW.  Every time you refer to a VIEW in a SQL
>> statement SQL goes back and looks at the VIEW specification again.
>> >
>> > 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
>>
> ___
> 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] sqlite3_create_function and threads

[Technology Lighthouse]

 > Yes. What's wrong with that?

Nothing at all - I just needed to know whether that was the case so I 
could design certain sections of my code accordingly.

Thanks for the help!
-- 
Paul Roberts
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

Thank you all for your responses.
I had to go out after posting and have just come back.
My concern is with...
SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2'
and
SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND 'y2'.

t_x and t_y are dimension tables.
that hold the x and y margins of a spreadsheet.
The margins will have an implied order shown by pos
which will differ from the order in which rows are added (represented by
rowid).

What I want to do is...make sure that when I say BETWEEN I really mean eg
BETWEEN x1 and x2 when you look at the table as if it's ordered by pos and
not rowid. I hope that helps explain why pos exists and is not rowid i.e. I
want to be able to "insert" and "delete" records "!in between" the existing
ones or at least make it look like that even if the records are physically
appended to the tables.
Hope this clarifies things and look forward to your thoughts.


On 1 July 2011 15:30, Pavel Ivanov  wrote:

> >> Putting the 'ORDER BY' clause in view won't work?
> >
> > It will work just fine, in that the results you see will appear in the
> ORDER you asked for.
>
> I believe that's not always true and is not required by SQL standard.
> Most probably 'select * from view_name' will return rows in the order
> written in the view. But 'select * from view_name where some_column =
> some_value' can already return rows in completely different order. And
> 'select * from table_name, view_name where some_condition' will almost
> certainly ignore any ORDER BY in the view.
>
> So ORDER BY in the view doesn't guarantee you anything.
>
>
> Pavel
>
>
> On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin 
> wrote:
> >
> > On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:
> >
> >> 2011/7/1 Simon Slavin 
> >>
> >>> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
> >>>
>  Isn't it possible to use a view for that?
> >>>
> >>> You can use a VIEW if you want, but VIEWs don't sort the table either.
>  A
> >>> VIEW is just a way of saving a SELECT query.  When you consult the VIEW
> >>> SQLite executes the SELECT.
> >>
> >> Putting the 'ORDER BY' clause in view won't work?
> >
> > It will work just fine, in that the results you see will appear in the
> ORDER you asked for.
> >
> > However, it has no influence on how data is stored.  In fact no table
> data is stored for a VIEW at all.  The thing stored is the parameters given
> when you created the VIEW.  Every time you refer to a VIEW in a SQL
> statement SQL goes back and looks at the VIEW specification again.
> >
> > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_create_function and threads

[Pavel Ivanov]

> To put it another way, if I call sqlite3_create_function to install a
> custom function, is that function now available to all threads using
> SQLite or is it available only to the thread that made the
> sqlite3_create_function call?

Yes, it's available to all threads using the same connection.

> And does the same rule apply to the application-defined pointer that can
> optionally be supplied in the sqlite3_create_function call via parameter
> 5???

Yes. What's wrong with that?


Pavel


On Fri, Jul 1, 2011 at 10:53 AM, Technology Lighthouse
 wrote:
>  From the docs it's unclear to me whether the use of
> sqlite3_create_function is thread-specific or not.
>
> To put it another way, if I call sqlite3_create_function to install a
> custom function, is that function now available to all threads using
> SQLite or is it available only to the thread that made the
> sqlite3_create_function call?
>
> And does the same rule apply to the application-defined pointer that can
> optionally be supplied in the sqlite3_create_function call via parameter
> 5???
>
> --
> Paul Roberts
> ___
> 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] sqlite3_create_function and threads

[Technology Lighthouse]

 From the docs it's unclear to me whether the use of 
sqlite3_create_function is thread-specific or not.

To put it another way, if I call sqlite3_create_function to install a 
custom function, is that function now available to all threads using 
SQLite or is it available only to the thread that made the 
sqlite3_create_function call?

And does the same rule apply to the application-defined pointer that can 
optionally be supplied in the sqlite3_create_function call via parameter 
5???

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

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Pavel Ivanov]

>> Putting the 'ORDER BY' clause in view won't work?
>
> It will work just fine, in that the results you see will appear in the ORDER 
> you asked for.

I believe that's not always true and is not required by SQL standard.
Most probably 'select * from view_name' will return rows in the order
written in the view. But 'select * from view_name where some_column =
some_value' can already return rows in completely different order. And
'select * from table_name, view_name where some_condition' will almost
certainly ignore any ORDER BY in the view.

So ORDER BY in the view doesn't guarantee you anything.


Pavel


On Fri, Jul 1, 2011 at 10:19 AM, Simon Slavin  wrote:
>
> On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:
>
>> 2011/7/1 Simon Slavin 
>>
>>> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
>>>
 Isn't it possible to use a view for that?
>>>
>>> You can use a VIEW if you want, but VIEWs don't sort the table either.  A
>>> VIEW is just a way of saving a SELECT query.  When you consult the VIEW
>>> SQLite executes the SELECT.
>>
>> Putting the 'ORDER BY' clause in view won't work?
>
> It will work just fine, in that the results you see will appear in the ORDER 
> you asked for.
>
> However, it has no influence on how data is stored.  In fact no table data is 
> stored for a VIEW at all.  The thing stored is the parameters given when you 
> created the VIEW.  Every time you refer to a VIEW in a SQL statement SQL goes 
> back and looks at the VIEW specification again.
>
> 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] Continuous exclusive lock

[Cecil Westerhof]

2011/7/1 Simon Slavin 

> >> If you _need_ exclusiveaccess all along, then start app, "begin
> >> exclusive", do your stuf, "commit" and exit.
> >
> > The 'problem' is that the application can run for the whole day.
>
> There's no problem with this.  You can maintain an EXCLUSIVE lock on the
> database for the whole day.  You can even maintain it while you're waiting
> an unknown amount of time for user input.  SQLite will happily keep the
> database locked the whole time.
>

That is exactly what I mend to say. Thanks for the clarification.



> Some may argue that this would be bad use of resources but that's a
> different matter.
>

Depends on the situation, but in this case I think permissible. There is
only one user. Saves me a lot of headache and the user also. Started editing
something.  Is interrupted. Forgot that he was working on it and starts the
program again. In this case the program stops with the message that the
table is locked and he can continue where he left of. ;-}

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

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Simon Slavin]

On 1 Jul 2011, at 3:07pm, Alessandro Marzocchi wrote:

> 2011/7/1 Simon Slavin 
> 
>> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
>> 
>>> Isn't it possible to use a view for that?
>> 
>> You can use a VIEW if you want, but VIEWs don't sort the table either.  A
>> VIEW is just a way of saving a SELECT query.  When you consult the VIEW
>> SQLite executes the SELECT.
> 
> Putting the 'ORDER BY' clause in view won't work?

It will work just fine, in that the results you see will appear in the ORDER 
you asked for.

However, it has no influence on how data is stored.  In fact no table data is 
stored for a VIEW at all.  The thing stored is the parameters given when you 
created the VIEW.  Every time you refer to a VIEW in a SQL statement SQL goes 
back and looks at the VIEW specification again.

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

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Alessandro Marzocchi]

2011/7/1 Simon Slavin 

>
> On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:
>
> > Isn't it possible to use a view for that?
>
> You can use a VIEW if you want, but VIEWs don't sort the table either.  A
> VIEW is just a way of saving a SELECT query.  When you consult the VIEW
> SQLite executes the SELECT.
>
> Simon.
>
>

Putting the 'ORDER BY' clause in view won't work?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Simon Slavin]

On 1 Jul 2011, at 11:20am, Alessandro Marzocchi wrote:

> Isn't it possible to use a view for that?

You can use a VIEW if you want, but VIEWs don't sort the table either.  A VIEW 
is just a way of saving a SELECT query.  When you consult the VIEW SQLite 
executes the SELECT.

Simon.

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

Re: [sqlite] FTS4 code from the website

[Dan Kennedy]

On 06/30/2011 08:55 PM, Ryan Henrie wrote:
> Even if I use the stock example from the web page, with only 2 columns,
> and the exact schema from the example, I get the same result.
>
> I'm wondering if it is a bug in the example code on the website (ie the
> source code has moved on, invalidating an example based on old code), or
> it's just something in turning their c code example into a full
> extension that I didn't do right.
>
>   From the extension's source code:
>
> nCol = aMatchinfo[1];
> if( nVal!=(*1+nCol*) ) goto wrong_number_args;
>
> So, it should scale with the number of columns.  (I would hope it's not
> hardcoded to a set number of columns!)

The page is a bit deceptive. The key phrase relating to the C code
example is:

   "Instead of a single weight, it allows a weight to be externally
assigned to each column of each document."

Making the C code function incompatible with the SQL example above
it. The C code function would work with the example in its header
comment:

 CREATE VIRTUAL TABLE documents USING fts3(title, content);

 SELECT docid FROM documents
 WHERE documents MATCH 
 ORDER BY rank(matchinfo(documents), 1.0, 0.5) DESC;

It wouldn't be real hard to adapt the C code so that it accepted
a single weight argument like the hypothetical function in the
SQL example above it.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Continuous exclusive lock

[Simon Slavin]

On 1 Jul 2011, at 3:51am, Cecil Westerhof wrote:

> 2011/6/30 Jean-Christophe Deschamps 
> 
>> If you _need_ exclusiveaccess all along, then start app, "begin
>> exclusive", do your stuf, "commit" and exit.
> 
> The 'problem' is that the application can run for the whole day.

There's no problem with this.  You can maintain an EXCLUSIVE lock on the 
database for the whole day.  You can even maintain it while you're waiting an 
unknown amount of time for user input.  SQLite will happily keep the database 
locked the whole time.

Some may argue that this would be bad use of resources but that's a different 
matter.

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

[sqlite] Bug: Database with non-loadable schema can be created

[Filip Navara]

Hello,

I have hit a bug that allows creation of a database that couldn't be
loaded. Step to reproduce are listed below.

Best regards,
Filip Navara

>sqlite3.exe
SQLite version 3.7.6.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> ATTACH DATABASE 'attached.dat' AS 'attached';
sqlite> CREATE TABLE "attached"."a" ("b");
sqlite> CREATE TRIGGER "attached"."ta" AFTER INSERT ON "attached"."a"
BEGIN SELECT 0; END;

>sqlite3.exe attached.dat
SQLite version 3.7.6.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema
Error: malformed database schema (ta) - trigger "ta" cannot reference objects in
 database attached
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4 code from the website

[Ryan Henrie]

Even if I use the stock example from the web page, with only 2 columns, 
and the exact schema from the example, I get the same result.

I'm wondering if it is a bug in the example code on the website (ie the 
source code has moved on, invalidating an example based on old code), or 
it's just something in turning their c code example into a full 
extension that I didn't do right.

 From the extension's source code:

   nCol = aMatchinfo[1];
   if( nVal!=(*1+nCol*) ) goto wrong_number_args;

So, it should scale with the number of columns.  (I would hope it's not 
hardcoded to a set number of columns!)


 Original Message 
Subject: Re: [sqlite] FTS4 code from the website
From: Dan Kennedy 
Date: 6/29/2011 11:25 PM
> On 06/30/2011 10:31 AM, Ryan Henrie wrote:
>> Reference Page: http://www.sqlite.org/fts3.html#appendix_a
>>
>> At the bottom of the page, there is a sample c file to calculate the
>> rank, and a FTS query to use it.  I can't get it to work.
>>
>> You can see my files here:
>>
>> http://coldmist.homeip.net/quotes_sql_test.txt
>> http://coldmist.homeip.net/rank.c.txt
>>
>>>   gcc -shared -fPIC -I/opt/include -o rank.so rank.c
>>>   rm test.sql; sqlite3 test.sql>
>> The C file compiles without errors or warnings on my x86 Linux machine
>> (and I verified one plugin I found compiled and worked fine, just to
>> remove build issues as a cause), but when I execute the import, it
>> complains with this:
>>
>> Error: near line 16: wrong number of arguments to function myrank()
> Looks like myrank() is supposed to be passed 5 arguments in this
> case. The return value of matchinfo() and a weight for each column.
> Your table has 4 columns, hence 5 arguments.
> ___
> 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] Ensure that query acts on PRE-SORTED tables

[Alessandro Marzocchi]

2011/7/1 Mohit Sindhwani 

> On 1/7/2011 5:37 PM, Martin.Engelschalk wrote:
> > Hi,
> >
> > i apologize beforehand if my post does not answer your question
> > directly. It seems to me that you may be missing a basic concept.
> >
> > Data in an SQL table is never sorted in itself. So, you can not sort a
> > table before you query it.
>
> SQLite does give you the ability to sort by _rowid_ which returns the
> data in the manner that it was stored into the table.  The idea of
> sorting the data before doing the query doesn't map to SQL very well...
> and is probably not required.
>
> Isn't it possible to use a view for that?
   Alessandro
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Mohit Sindhwani]

On 1/7/2011 5:37 PM, Martin.Engelschalk wrote:
> Hi,
>
> i apologize beforehand if my post does not answer your question
> directly. It seems to me that you may be missing a basic concept.
>
> Data in an SQL table is never sorted in itself. So, you can not sort a
> table before you query it.

SQLite does give you the ability to sort by _rowid_ which returns the 
data in the manner that it was stored into the table.  The idea of 
sorting the data before doing the query doesn't map to SQL very well... 
and is probably not required.

Best Regards,
Mohit.
1/7/2011 | 6:14 PM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

[Martin.Engelschalk]

Hi,

i apologize beforehand if my post does not answer your question 
directly. It seems to me that you may be missing a basic concept.

Data in an SQL table is never sorted in itself. So, you can not sort a 
table before you query it.

If you select data without an "order by" - clause, the order in which 
you get the data is arbirtary and may also change from time to time. So, 
if you want to retrieve rows in a certain order, you have to add an 
"order by" clause to your select statement.

If you want to speed up such a query, you can create an index.
In your case pos seems to be a candidate for a primary key, because it 
is unique. So, create your table like this:

CREATE TABLE IF NOT EXISTS t_x(
   "pos integer primary key, "
 "txt text NOT NULL"

);

Then, data is indexed automatically by pos, which comes near to your 
intention. However, you must always add an "order by pos" clause to your 
select statement(s).

Perhaps you might want to read up on primary keys and indexes. This is 
not sqlite specific but a feature of SQL.

hth
Martin

Am 01.07.2011 11:16, schrieb e-mail mgbg25171:
> I know that ORDER BY sorts result but I want to sort a table BEFORE it gets
> queried and am not sure of the syntax.
> Here's my test program but...I'm not sure how to PRE-SORT tables t_x and t_y
> (by column pos)  BEFORE I do the SELECT BETWEEN on THEM
> i.e. I purposefully inserted t_d row 1,1,1 to see if it would come out first
> ie as 1. It doesn't any help much appreciated.
> Apologies if my question isn't clear.
>
> #include "stdafx.h"
> #include "stdio.h"
> #include "sqlite3.h"
> #include  //malloc
> #include  //strlen
> #include  //assert
>
> /*i've made these global so...both Create_database AND Query_database can
> SEE them ie it shows what vars need to be common*/
> int res, ind = 0;
> char** sql;
> sqlite3_stmt *stmt;
> sqlite3* db=NULL;
> char* err=0;
>
> static int Open_db(char* flnm){
>  if (!strlen(flnm)){ res=sqlite3_open(":memory:",); }
>  else{ res=sqlite3_open(flnm,); }
>  if (!db){ printf("Open_db() failed\n"); }
>  return res;
> }
>
>
> void Close_db(){
>  res = sqlite3_close(db);
> }
>
>
> int Exec(char * s){
>  res = sqlite3_exec(db,s,0,0,0);
>  if ( res ){
>  printf( "Exec error re %s %s\n", s, sqlite3_errmsg(db)  );
>  }
>  assert(res==0); //so you can concentrate on 1st error
>  return res;
> }
>
>
> int _tmain(int argc, _TCHAR* argv[]){ //default project main
>  Open_db("");
>  //===
>  Exec("CREATE TABLE IF NOT EXISTS t_x("
>  "pos integer UNIQUE NOT NULL,"
>  "txt text NOT NULL"
>  ")"
>  );
>  Exec( "INSERT INTO t_x VALUES(1,'x1')" );
>  Exec( "INSERT INTO t_x VALUES(2,'x2')" );
>  //===
>  Exec("CREATE TABLE IF NOT EXISTS t_y("
>  "pos integer UNIQUE NOT NULL,"
>  "txt text NOT NULL"
>  ")"
>  );
>  Exec( "INSERT INTO t_y VALUES(1,'y1')" );
>  Exec( "INSERT INTO t_y VALUES(2,'y2')" );
>  //===
>  Exec("CREATE TABLE IF NOT EXISTS t_d("
>  "xpos integer NOT NULL,"
>  "ypos integer NOT NULL,"
>  "d float "
>  ")"
>  );
>  /*table layout
>  see onenote thoughts diary me at 30/06/2011 08:42
>  yx->
>  |1,2
>  V3,4  xy data
>VV V  */
>
>  Exec( "INSERT INTO t_d VALUES(1,2,3)" );
>  Exec( "INSERT INTO t_d VALUES(2,1,2)" );
>  Exec( "INSERT INTO t_d VALUES(2,2,4)" );
>  Exec( "INSERT INTO t_d VALUES(1,1,1)" );
>  //===
>
>  //
> http://dcravey.wordpress.com/2011/03/21/using-sqlite-in-a-visual-c-application/
>  //= this block from url albeit modified by me
> ==
>  const char* sqlSelect ="SELECT d FROM t_d "
>  "where xpos in "
>  "(SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND
> 'x2') ";
>  "AND ypos in "
>  "(SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND
> 'y2')";
>
>  char** results = NULL;
>  int rows, columns;
>  res = sqlite3_get_table(db, sqlSelect);
>  if (res){
>  //printf( "error in sqlite3_get_table %s\n", s, sqlite3_errmsg(db)
> );
>  sqlite3_free(err);
>  }
>  else{
>  // Display Table
>  for (int rowCtr = 0; rowCtr<= rows; ++rowCtr){
>  for (int colCtr = 0; colCtr<  columns; ++colCtr){
>  int cellPosition = (rowCtr * columns) + colCtr;
>  printf( "%s\t", results[cellPosition] );
>  }
>  printf( "\n");
>
>  }
>  }
>  sqlite3_free_table(results);
>
> 

[sqlite] Ensure that query acts on PRE-SORTED tables

[e-mail mgbg25171]

I know that ORDER BY sorts result but I want to sort a table BEFORE it gets
queried and am not sure of the syntax.
Here's my test program but...I'm not sure how to PRE-SORT tables t_x and t_y
(by column pos)  BEFORE I do the SELECT BETWEEN on THEM
i.e. I purposefully inserted t_d row 1,1,1 to see if it would come out first
ie as 1. It doesn't any help much appreciated.
Apologies if my question isn't clear.

#include "stdafx.h"
#include "stdio.h"
#include "sqlite3.h"
#include //malloc
#include //strlen
#include //assert

/*i've made these global so...both Create_database AND Query_database can
SEE them ie it shows what vars need to be common*/
int res, ind = 0;
char** sql;
sqlite3_stmt *stmt;
sqlite3* db=NULL;
char* err=0;

static int Open_db(char* flnm){
if (!strlen(flnm)){ res=sqlite3_open(":memory:",); }
else{ res=sqlite3_open(flnm,); }
if (!db){ printf("Open_db() failed\n"); }
return res;
}


void Close_db(){
res = sqlite3_close(db);
}


int Exec(char * s){
res = sqlite3_exec(db,s,0,0,0);
if ( res ){
printf( "Exec error re %s %s\n", s, sqlite3_errmsg(db)  );
}
assert(res==0); //so you can concentrate on 1st error
return res;
}


int _tmain(int argc, _TCHAR* argv[]){ //default project main
Open_db("");
//===
Exec("CREATE TABLE IF NOT EXISTS t_x("
"pos integer UNIQUE NOT NULL,"
"txt text NOT NULL"
")"
);
Exec( "INSERT INTO t_x VALUES(1,'x1')" );
Exec( "INSERT INTO t_x VALUES(2,'x2')" );
//===
Exec("CREATE TABLE IF NOT EXISTS t_y("
"pos integer UNIQUE NOT NULL,"
"txt text NOT NULL"
")"
);
Exec( "INSERT INTO t_y VALUES(1,'y1')" );
Exec( "INSERT INTO t_y VALUES(2,'y2')" );
//===
Exec("CREATE TABLE IF NOT EXISTS t_d("
"xpos integer NOT NULL,"
"ypos integer NOT NULL,"
"d float "
")"
);
/*table layout
see onenote thoughts diary me at 30/06/2011 08:42
yx->
|1,2
V3,4  xy data
  VV V  */

Exec( "INSERT INTO t_d VALUES(1,2,3)" );
Exec( "INSERT INTO t_d VALUES(2,1,2)" );
Exec( "INSERT INTO t_d VALUES(2,2,4)" );
Exec( "INSERT INTO t_d VALUES(1,1,1)" );
//===

//
http://dcravey.wordpress.com/2011/03/21/using-sqlite-in-a-visual-c-application/
//= this block from url albeit modified by me
==
const char* sqlSelect ="SELECT d FROM t_d "
"where xpos in "
"(SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND
'x2') ";
"AND ypos in "
"(SELECT pos FROM t_y WHERE txt BETWEEN 'y1' AND
'y2')";

char** results = NULL;
int rows, columns;
res = sqlite3_get_table(db, sqlSelect, , , , );
if (res){
//printf( "error in sqlite3_get_table %s\n", s, sqlite3_errmsg(db)
);
sqlite3_free(err);
}
else{
// Display Table
for (int rowCtr = 0; rowCtr <= rows; ++rowCtr){
for (int colCtr = 0; colCtr < columns; ++colCtr){
int cellPosition = (rowCtr * columns) + colCtr;
printf( "%s\t", results[cellPosition] );
}
printf( "\n");

}
}
sqlite3_free_table(results);

//==


Close_db();
sqlite3_free(err);
getchar();
return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Continuous exclusive lock

[Cecil Westerhof]

2011/7/1 Jean-Christophe Deschamps 

>
> > > If you _need_ exclusiveaccess all along, then start app, "begin
> > > exclusive", do your stuf, "commit" and exit.
> > >
> >
> >The 'problem' is that the application can run for the whole day.
>
> Granted. And the 'problem' is ???
>

That there is not a clear R-M-W cycle. The data is read when starting up on
eight o'clock, but maybe only at three in the afternoon something is
changed. But the current solution is good enough. When starting the program
for a second time (because you forgot it was already open), the second one
is terminated with a message that the database is locked.

When I make sure I do a COMMIT after a change and immediately a BEGIN
EXCLUSIVE, I do not have to worry about anything.

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