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 ??? >

[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

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

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.

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.

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

[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'

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.

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

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.

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

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 >>

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

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

[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

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.

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

[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

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

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 > >

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

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

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

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

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

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

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

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

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 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

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

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

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

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

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

[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

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

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] 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

[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

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