Re: [sqlite] Ensure that query acts on PRE-SORTED tables
Thank you Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR UPDATE?
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?
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
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?
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?
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?
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
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&a 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
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&a 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&a 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&a 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&a 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&a. > > 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 > >
Re: [sqlite] Ensure that query acts on PRE-SORTED tables
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&a 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&a expenses >>> cos that's pos order >>> >>> and you'd see t_d as >>> 1990 1991 1992 >>> revenue1 23 >>> cogs4 56 >>> sg&a7 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&a. >>> 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
Re: [sqlite] Ensure that query acts on PRE-SORTED tables
> 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&a 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&a expenses >> cos that's pos order >> >> and you'd see t_d as >> 1990 1991 1992 >> revenue1 23 >> cogs4 56 >> sg&a7 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&a. >> 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
Re: [sqlite] Ensure that query acts on PRE-SORTED tables
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&a 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&a expenses > cos that's pos order > > and you'd see t_d as > 1990 1991 1992 > revenue1 23 > cogs4 56 > sg&a7 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&a. > 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
Re: [sqlite] Ensure that query acts on PRE-SORTED tables
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&a 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&a expenses cos that's pos order and you'd see t_d as 1990 1991 1992 revenue1 23 cogs4 56 sg&a7 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&a. 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 12:02 PM, e-mail mgbg25171 > >> >> wrote: > >> >> > Thx for your suggestion... > >> >> > Yes "BY ORDER BY pos"
Re: [sqlite] Ensure that query acts on PRE-SORTED tables
> 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 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 >>
Re: [sqlite] Ensure that query acts on PRE-SORTED tables
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 > >> >> > ones or at least make it look like that even if the records are > >> >> physically > >> >> > appended to the tables. > >> >> > Hope this clarif
Re: [sqlite] Ensure that query acts on PRE-SORTED tables
> 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 >> almost >> >> >> certainly ignore any ORDER BY in the view. >> >> >> >> >> >> So ORDER BY in the view doesn't guarantee you anything. >> >> >>
Re: [sqlite] Ensure that query acts on PRE-SORTED tables
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 > > >> > > > ___ > > > 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
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 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
Re: [sqlite] Ensure that query acts on PRE-SORTED tables
> 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users m
Re: [sqlite] Ensure that query acts on PRE-SORTED tables
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
> > 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
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
> 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
> 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
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
> 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
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
>> 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
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
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
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
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
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
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
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
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
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
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
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:",&db); } > else{ res=sqlite3_open(flnm,&db); } > 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,&results,&rows,&columns,&err); > 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(re
[sqlite] Ensure that query acts on PRE-SORTED tables
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:",&db); } else{ res=sqlite3_open(flnm,&db); } 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, &results, &rows, &columns, &err); 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
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