Re: [sqlite] Find first non-NULL values of several columns

[Simon Slavin] > On 20 Oct 2012, at 3:57am, Keith Medcalf wrote: > >> While this is presently how SQLite works, it may not always be so. It is >> possible that a future version may change the order of traversal. > > Or indeed > >

Re: [sqlite] Find first non-NULL values of several columns

On Fri, Oct 19, 2012 at 10:57 PM, Keith Medcalf wrote: > > As far as I can tell, the SQLite engine does an inorder traversal of the > tree when doing a table scan (ie, from lowest to highest) returning rows in > rowid order. > While this is presently how SQLite works, it may not always be so. >

Re: [sqlite] Find first non-NULL values of several columns

On 20 Oct 2012, at 3:57am, Keith Medcalf wrote: > While this is presently how SQLite works, it may not always be so. It is > possible that a future version may change the order of traversal. Or indeed Simon. __

Re: [sqlite] Find first non-NULL values of several columns

Table rows in SQLite are stored in a BTree structure where the "key" is the rowid. As far as I can tell, the SQLite engine does an inorder traversal of the tree when doing a table scan (ie, from lowest to highest) returning rows in rowid order. The rowid is always the primary key of a table.

Re: [sqlite] Why can't SQLite support ALTER TABLE commands that rewrite the table?

On Fri, Oct 19, 2012 at 4:14 PM, Yves Goergen wrote: > Since my last question was not answered but instead another statement > was made which I don't understand, I feel the need to reformulate my > question to this: Why is it that SQLite can only support ALTER TABLE > statements "that can be accom

Re: [sqlite] Find first non-NULL values of several columns

"Keith Medcalf" writes: > Alternatively, to implement your original question, write an aggregate > function which returns the first non-null value it comes across, and use the > negated unix timestamp as an explicit rowid, depending on the fact that a > table-scan does an in-order traversal of

Re: [sqlite] Find first non-NULL values of several columns

Ryan Johnson writes: > On 19/10/2012 3:09 PM, Steinar Midtskogen wrote: >> I have tables with a timestamp (unix time) and columns containing >> sensor readings which are inserted continuously. I frequently need to >> access the most recent values (or NULL if there is no value within the >> lates

Re: [sqlite] Find first non-NULL values of several columns

On 19 Oct 2012, at 8:09pm, Steinar Midtskogen wrote: > I have tables with a timestamp (unix time) and columns containing > sensor readings which are inserted continuously. I frequently need to > access the most recent values (or NULL if there is no value within the > latest, say, hour). I woul

[sqlite] Why can't SQLite support ALTER TABLE commands that rewrite the table?

Since my last question was not answered but instead another statement was made which I don't understand, I feel the need to reformulate my question to this: Why is it that SQLite can only support ALTER TABLE statements "that can be accomplished without having to rewrite the entire table"? [1] This

Re: [sqlite] Find first non-NULL values of several columns

Ryan Johnson wrote: > Is each column associated with a different sensor? If so, do sensors > routinely generate data at the same time and combine their entries? Do > sensors emit data frequently? > > If any of the above is false, I'd definitely store each column in its > own table Or else, every

Re: [sqlite] several links of www.sqlite.org point to draft pages

On Fri, Oct 19, 2012 at 3:01 PM, Carlos Milon Silva wrote: > several links of www.sqlite.org are loading draft pages > Fixed now. Sorry. This morning I meant to run the command that makes a backup of the server (using rsync) but instead I (by mistake) ran the command that pushes our staging are

Re: [sqlite] Find first non-NULL values of several columns

On 19/10/2012 3:09 PM, Steinar Midtskogen wrote: Thank you for all suggestions. I will need to do such queries often, so it's just a matter of saving the typing. Unfortunately, views aren't going to be very practical either, because there are a lot of tables and columns (100+), and new ones wil

Re: [sqlite] A question about prepared statements

>> Var Alice := Db.SelectRecord('SELECT * FROM client WHERE client_name = ?', [ >> 'alice' ]); >> >> 1. If I drop record for "alice" from db and then access column data in >> prepared stmt will it work OK? > > If the select statement wasn't reset or finalized, you won't be able to > delete a re

Re: [sqlite] Find first non-NULL values of several columns

create table taglist (tagid integer primary key autoincrement, tagname text collate nocase unique); create table tagdata (tagid integer references taglist(tagid), timestamp integer not null, value not null, unique(tagid, timestamp)); select tagname, coalesce(timestamp, tsstart), value from ta

Re: [sqlite] A question about prepared statements

Григорий Григоренко wrote: > I am using prepared stmts to cache selected record for later use. I have a > wrapper class, that has methods like AsString(name), > AsFloat(name) etc to extract data for a column name. > > I prepare, bind and then do Sqlite3_step() to get record. Later I use > sqli

[sqlite] A question about prepared statements

Hello, I am using prepared stmts to cache selected record for later use. I have a wrapper class, that has methods like AsString(name), AsFloat(name) etc to extract data for a column name. I prepare, bind and then do Sqlite3_step() to get record. Later I use sqlite3_column_XXX() to access data

Re: [sqlite] Find first non-NULL values of several columns

Thank you for all suggestions. I will need to do such queries often, so it's just a matter of saving the typing. Unfortunately, views aren't going to be very practical either, because there are a lot of tables and columns (100+), and new ones will be added. The actual use case is as follows: I

[sqlite] several links of www.sqlite.org point to draft pages

several links of www.sqlite.org are loading draft pages ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Resolving Database locking issues in a multi-user environment

Ben Morris wrote: > Potentially we could have around fifty client applications all attempting > simultaneous writes > to a SQLite database stored on a file server. In that case, I would say quoting directly from the SqLite web page we've mentioned already should be sufficient to scare management

Re: [sqlite] Resolving Database locking issues in a multi-user environment

Thank you for the responses all, and please accept my apologies; I have obviously not achieved my intended level of clarity in the scenario description I provided. Firstly, by multi-user I think the most precise definition would be: 'multiple, concurrent and distributed users'. The key point that

Re: [sqlite] Resolving Database locking issues in a multi-user environment

Richard Hipp wrote: > Ben Morris wrote: >> If anyone could share their honest opinions of both my suggested >> approach, and my colleague's, I would be very grateful. > > [snip] > Using a separate SQLite database on each client to serve as a local cache of > the master database and then > period

Re: [sqlite] Find first non-NULL values of several columns

On 19/10/2012 9:17 AM, Igor Tandetnik wrote: Ryan Johnson wrote: I'd go for a user-defined aggregate taking two args: the key (to identify "first") and the value to coalesce. Sure, it would never stop the scan early, but the benefit of doing one scan instead of five probability outweighs that (

Re: [sqlite] Find first non-NULL values of several columns

Ryan Johnson wrote: > I'd go for a user-defined aggregate taking two args: the key (to > identify "first") and the value to coalesce. Sure, it would never stop > the scan early, but the benefit of doing one scan instead of five > probability outweighs that (unless Steinar has an appropriate index

Re: [sqlite] Find first non-NULL values of several columns

On 19/10/2012 8:55 AM, Igor Tandetnik wrote: Steinar Midtskogen wrote: Ok, so let's say the table v (with "a" as the primary key) is: a|b|c|d|e|f 0| | |2| |9 1|1| |3| |8 2|1| |4|4|7 3| |5|5|4|6 4|1|6|6| |5 The the question becomes, is there a more convenient way to do: SELECT * FROM (SELECT

Re: [sqlite] Find first non-NULL values of several columns

Igor Tandetnik wrote: > If the values of b, c and so on have a known upper bound, then you can write > something like > > select min(a*1000 + b), min(a*1000 + c), ..., min(a*1000 + f) from v; I mean, select min(a*1000 + b) % 1000, ... or the same with shifts and masks: select min(a<<32 + b)

Re: [sqlite] Resolving Database locking issues in a multi-user environment

On 19 Oct 2012, at 8:16am, Ben Morris wrote: > During Upload, the local database is scanned for rows where the Sync flag > is true. Each row like this is either updated or inserted into the master > database (depending on whether a row can be found with the same PK). > > During Download, every

Re: [sqlite] Find first non-NULL values of several columns

Steinar Midtskogen wrote: > Ok, so let's say the table v (with "a" as the primary key) is: > > a|b|c|d|e|f > 0| | |2| |9 > 1|1| |3| |8 > 2|1| |4|4|7 > 3| |5|5|4|6 > 4|1|6|6| |5 > > The the question becomes, is there a more convenient way to do: > > SELECT * FROM (SELECT b FROM v WHERE b IS NOT

Re: [sqlite] Find first non-NULL values of several columns

Does a view help you out? Are you just trying to make it easier for somebody to create a query for that answer without typing so much? create table v(a integer primary key,b,c,d,e,f); insert into v values(0,NULL,NULL,2,null,9); insert into v values(1,1,null,3,null,8); insert into v values(2,1,nu

Re: [sqlite] Resolving Database locking issues in a multi-user environment

On Fri, Oct 19, 2012 at 3:16 AM, Ben Morris wrote: > Hello all, > > I've recently joined an organization that is attempting to use SQLite in a > multi-user environment (C#, using System.Data.SQLite, layered under the > DevExpress XPO ORM). Due to the high-latency nature of the networks on > which

[sqlite] Resolving Database locking issues in a multi-user environment

Hello all, I've recently joined an organization that is attempting to use SQLite in a multi-user environment (C#, using System.Data.SQLite, layered under the DevExpress XPO ORM). Due to the high-latency nature of the networks on which our application is deployed, we're seeing a very high number of

Re: [sqlite] Find first non-NULL values of several columns

Simon Slavin writes: > Rows do not have an order. Without an ORDER BY clause SELECT can return rows > in a random order if it wants. If you would like to define 'order' for me I > can give you a SELECT which will find the first non-NULL value in a column, > probably something like > > SELECT

Re: [sqlite] Find first non-NULL values of several columns

On 19 Oct 2012, at 12:06pm, Steinar Midtskogen wrote: > Suppose I have this table v: > > a|b|c|d|e > | |2| |9 > 1| |3| |8 > 1| |4|4|7 > |5|5|4|6 > 1|6|6| |5 > > And I would like to return the first non-NULL value of each column. Rows do not have an order. Without an ORDER BY clause SELECT ca

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

On 10/18/2012 09:05 PM, Pavel Ivanov wrote: On Thu, Oct 18, 2012 at 6:32 AM, Daniel Polski wrote: The SELECT statement, including the _prepare() stage and all the _step()s until you've reached the last row, and then the _finalize(), is all one process. They're all part of the statement and you

[sqlite] Find first non-NULL values of several columns

Suppose I have this table v: a|b|c|d|e | |2| |9 1| |3| |8 1| |4|4|7 |5|5|4|6 1|6|6| |5 And I would like to return the first non-NULL value of each column. I can do somthing like: SELECT * FROM (SELECT a FROM v WHERE a IS NOT NULL LIMIT 1), (SELECT b FROM v WHERE b IS NOT NULL LI

Re: [sqlite] xColumn called twice for a value

On Fri, Oct 19, 2012 at 5:08 AM, Steinar Midtskogen wrote: > Hi > > Suppose I have a virtual table v with a column c. Then, if I do: > > SELECT c, c+1, c-1 FROM v; > > xColumn() will then only be called once per row. Which is all good. > > But if I add any constraints on c, e.g.: > > SELECT

[sqlite] xColumn called twice for a value

Hi Suppose I have a virtual table v with a column c. Then, if I do: SELECT c, c+1, c-1 FROM v; xColumn() will then only be called once per row. Which is all good. But if I add any constraints on c, e.g.: SELECT c, c+1, c-1 FROM v WHERE c IS NOT NULL; Then xColumn() will get called a sec