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

[Steinar Midtskogen]

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

Thanks.  That was quite useful, actually, and caught a missing "ORDER
BY" in my code.  Of course, I could stick with a specific SQLite
version, but if SQLite at some point will support NATURAL FULL OUTER
JOIN, I would want to upgrade as NATURAL FULL OUTER JOIN would
simplify my code and perhaps SQLite can optimise that better than my
current use of UNIONs and NATURAL LEFT OUTER JOIN.

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

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

[Richard Hipp]

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

Indeed, this will change in version 3.7.15 with the addition of a new
optimization that will scan covering indices instead of the original table,
when a covering index is available.  You can try it out by downloading and
compiling the tip of trunk, if you like.


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



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

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

[Keith Medcalf]

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.  All other keys 
that are not explicitly named rowid ( INTEGER PRIMARY KEY) are merely 
additional indexes where the key is the concatenation of the key columns plus 
the rowid of the table row.  Thus a table with a non-INTEGER primary key (or a 
compound primary key declaration) is exactly the same as a unique index on the 
columns, or just an additional index.

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.  In 
particular, I believe SQLite4 will not perform tablescan operations in rowid, 
but rather in storage order.

Therefore, at present, if you use the negative timestamp as the key, the 
traversal order will be from newest record to oldest since the "lowest" rowid 
will be the most recent timestamp.  If you change the step function to update 
the final return value for each non-null value, you will return the "last" 
non-null value rather than the "first" -- where first and last means 
"numerically least" and "numerically greatest" rowid respectively.

Of course, this is programming to an internal implementation detail.  It may 
not be transportable to any other database system, and the specific 
implementation may change at any time.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Steinar Midtskogen
> Sent: Friday, 19 October, 2012 15:43
> To: General Discussion of SQLite Database
> Subject: 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 the table btree:
> 
> Except for the names, the below code is *exactly*, character by
> character, as what I first wrote, but then I realised that the order
> of the values going to the step function might not be defined.  So can
> I trust that I will get the order of my unix time primary key?  It
> would be convenient if I can omit the timestamp argument in the
> function.
> 
> >
> > typedef struct fnnCtx fnnCtx;
> > struct fnnCtx
> > {
> > double fnn;
> > int flag;
> > };
> >
> > SQLITE_PRIVATE void fnnStep(sqlite3_context *context, int argc,
> sqlite3_value **argv)
> > {
> > fnnCtx* p = sqlite3_aggregate_context(context, sizeof(fnnCtx));
> > if (p && sqlite3_value_numeric_type(argv[0]) != SQLITE_NULL && p->flag
> == 0)
> > {
> > p->fnn = sqlite3_value_double(argv[0]);
> > p->flag = 1;
> > }
> > }
> >
> > SQLITE_PRIVATE void fnnFinal(sqlite3_context *context)
> > {
> > fnnCtx* p = sqlite3_aggregate_context(context, 0);
> > if (p && p->flag == 1)
> > sqlite3_result_double(context, p->fnn);
> > }
> 
> --
> Steinar Midtskogen
> ___
> 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] Why can't SQLite support ALTER TABLE commands that rewrite the table?

[Nico Williams]

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 accomplished without having to rewrite the
> entire table"? [1] This includes statements to drop single columns out
> of a table. I understand that doing this might cause a considerable
> delay during which the database is not available, but the same applies
> to the VACUUM statement and that is very well supported. Could somebody
> maybe enlighten me?

SQLite3 could have a way of recording the existence of dropped columns
so as to allow column dropping while keeping the data as-is.  That is,
the column would not be dropped in actuality, just marked as so, with
all new rows getting NULL for it..  (Note that even that's not really
feasible if the column being dropped was part of a primary key or
index of any kind.)  But re-writing tables is not likely to be
terribly satisfactory: you might run into ENOSPC in the process, for
example, or it might just take too long.  Simply marking a column as
dropped is much simpler, I'm sure.  Also, to properly handle data
re-write for schema changes would require being able to process an
entire transaction of schema changes because a change to one table
might make no sense except in conjunction with a corresponding change
to another (think of foreign keys).  In other words: this feature is
difficult to implement and likely to leave a lot to be desired, so why
bother.

> If the developers deciding what SQLite can do and what not would state
> that they do not want, for personal reasons they don't want to disclose,
> to implement that particular feature, that's fine. But if that's the
> case, I'd be happy to hear that. Also, if there's a technical limitation
> preventing this, I'd like to understand it. Having no answer at all is a
> bit frustrating...

The answer I've seen given to this sort of question before is that
SQLite3 is supposed to be *lite*.  Since everyone knows to rewrite
their tables themselves when making schema changes that require it...
Or put it another way: the SQLite developers almost certainly have
lots of things they'd rather work on than this feature.   Frankly, I'd
rather they focus on SQLite4 than this feature.

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

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

[Steinar Midtskogen]

"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 the table btree:

Except for the names, the below code is *exactly*, character by
character, as what I first wrote, but then I realised that the order
of the values going to the step function might not be defined.  So can
I trust that I will get the order of my unix time primary key?  It
would be convenient if I can omit the timestamp argument in the
function.

>
> typedef struct fnnCtx fnnCtx;
> struct fnnCtx
> {
> double fnn;
> int flag;
> };
>
> SQLITE_PRIVATE void fnnStep(sqlite3_context *context, int argc, sqlite3_value 
> **argv)
> {
> fnnCtx* p = sqlite3_aggregate_context(context, sizeof(fnnCtx));
> if (p && sqlite3_value_numeric_type(argv[0]) != SQLITE_NULL && p->flag == 
> 0)
> {
> p->fnn = sqlite3_value_double(argv[0]);
> p->flag = 1;
> }
> }
>
> SQLITE_PRIVATE void fnnFinal(sqlite3_context *context)
> {
> fnnCtx* p = sqlite3_aggregate_context(context, 0);
> if (p && p->flag == 1)
> sqlite3_result_double(context, p->fnn);
> }

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

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

[Steinar Midtskogen]

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
>> latest, say, hour).  I would like to do something like:

> Is each column associated with a different sensor?

Yes.

> If so, do sensors routinely generate data at the same time and combine their 
> entries?

They usually generate data at the same time, but not always.

> Do sensors emit data frequently?

Usually every 5 minutes, but since the timestamp is unix time, 1
second frequency is supported.

> If any of the above is false, I'd definitely store each column in its
> own table, because the timestamp (the only possible thing to share) is
> replicated anyway.

I have many tables, but each has up to 30 or so values.  The table
division corresponds to the sensor source.  One source will typically
give a bunch of sensor values every 5 minutes (some values might be
NULL, though).  This makes INSERTs trivial.  I suppose I could put
everything in a single table by using UPDATEs, but since the different
sources have different timestamps and even intervals, I've been
thinking that it could make the database consist of mostly NULLs and
explode in size (say, if one table has readings every second, a single
table would have to have 3599 NULLs for each value in a column that
only have one value per hour).

The other extreme, one table for each sensor, has its problems as
well.  Quite often, I'd like to combine several values in one
expression (e.g. subtract one sensor value from another), and that may
be a bit tricky if they are located in different tables, especially if
they don't have the same timestamps.

Anyway, for this purpose I made a module which allows me to combine
several tables into a read-only virtual table similar to a natural
full outer join (I think).  Also, the virtual table will fill out all
missing values using interpolation.  I've also added functionality for
calculating time weighted averages.

It's pretty neat.  I wrote a perl wrapper as well which will look at
the columns that I want to access and it will create the necessary
virtual tables so I don't have to worry about which tables.

So if I want the outside temperature at 14 October 12:12:!2 even if
there is no table entry for that time, I can do:

$ select.pl 'temp_out WHERE unix_time = strftime("%s", "2012-10-14 12:12:12")'
0.93384

And in this case the result is an interpolation using the nearest
entries at 12:10:00 and 12:15:00.  If I want the 24 hour mean around
that time I do:

$ select.pl 'temp_out__avg_24h WHERE unix_time = strftime("%s", "2012-10-14 
12:12:12")'
1.70068 

where the __avg_24h bit is parsed so that an approperiate virtual
table gets created that will give me the 24h average.  And I can
combine columns from different tables, as in this case:

$ select.pl 'temp_in, temp_out, temp_in - temp_out WHERE unix_time = 
strftime("%s", "2012-10-14 12:12:12")'
21.528520.93384   20.59468 

where temp_in is in one table and temp_out is in another and neither
table has a row for 12:12:12.  And I can even use different averages
in the same expression, which will cause two virtual tables to become
created behind the scenes:

$ select.pl 'temp_out__avg_1y, temp_out__avg_24h, temp_out__avg_1y - 
temp_out__avg_24h WHERE unix_time = strftime("%s", "2010-01-01 00:00:00")'
4.40974   -7.61093   12.02068 

I'm getting off topic, but the essence is that I want to do flexible
queries with as little typing as possible.  If it can be done in plain
SQL, I'll do that.  If not, if it can be done by using user functions
or modules, I'll do that.  If I can't do that either, I'll add an
outer wrapper translating quasi SQL into SQLite commands.  For the
problem in this thread it seems that I can get away with adding new
aggregate functions.

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

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

[Simon Slavin]

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 would like to do something like:
> 
> SELECT coalesce(col_1), ..., coalesce(col_n)

Don't do this.  You are confusing a database with a spreadsheet.  Instead of a 
wide table make a thin table which has the above column number as a field.  You 
will find that this magically makes most of your programming problems go away.

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

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

[Yves Goergen]

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 includes statements to drop single columns out
of a table. I understand that doing this might cause a considerable
delay during which the database is not available, but the same applies
to the VACUUM statement and that is very well supported. Could somebody
maybe enlighten me?

If the developers deciding what SQLite can do and what not would state
that they do not want, for personal reasons they don't want to disclose,
to implement that particular feature, that's fine. But if that's the
case, I'd be happy to hear that. Also, if there's a technical limitation
preventing this, I'd like to understand it. Having no answer at all is a
bit frustrating...

[1] Richard Hipp, 2012-10-08 22:12 +0200, on this mailing list

-- 
Yves Goergen - nospam.l...@unclassified.de - http://unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Tandetnik]

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, everything in a single table, say, Readings(timestamp, sensorId, 
value).
-- 
Igor Tandetnik

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

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

[Richard Hipp]

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 area for the next release out to the server.  I did a "push"
instead of a "pull".  Should have had coffee first, I suppose.

Later tonight, I'll be updating our makefiles and procedures to make sure
this doesn't happen again.


> __**_
> 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] Find first non-NULL values of several columns

[Ryan Johnson]

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 will be added.

The actual use case is as follows:

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 would like to do something like:
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, because the timestamp (the only possible thing to share) is 
replicated anyway. That does get you back to the multiple subquery 
thing, but a few views should hide that pretty effectively. 
Unfortunately, it doesn't look like sqlite3 is smart enough to push 
projections down through views, otherwise a single 
"view-to-rule-them-all" would have zero runtime overhead and save a 
*lot* of typing.



SELECT coalesce(col_1), ..., coalesce(col_n) FROM v WHERE unix_time > 
strftime('%s', 'now', '-1 hour') ORDER BY unix_time DESC;

So I would typically want to access the last non-NULL value because of
the DESC keyword.  But if I understand things correctly, a statement
like above will never work because an aggregate function reads the
data in no particular order regardless of the ORDER BY statement.

I like Igor's suggestion.  Although not quite universal, it's clever.

Indeed it is. And very useful at times.


Ryan's suggestion should work well, except that I will need a first(a,
b) and last(a, b) function (if I want to support both ascending and
descending order) and I can leave out the ORDER BY part.  So:

SELECT last(col_1, unix_time), ..., last(col_n, unix_time) FROM v WHERE unix_time 
> strftime('%s', 'now', '-1 hour');

Or just pass -unix_time to first(), seeing as how it's a number.

Ryan

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

Re: [sqlite] A question about prepared statements

[Pavel Ivanov]

>> 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 record from the database (or, in WAL journaling mode, you would be 
> able to, but the reader would still see original data).

Well, if he uses the same connection used to prepare the statement,
then he will be able to delete record even in normal journaling mode.
What happens in this case is undefined. It might be that column data
will be still accessbile, it might be you get some garbage, it might
be an access violation.

>> 2. Can I drop client table while having such prepared stmt not closed?

I think table client cannot be dropped altogether until all statements
using it are reset/finalized.


Pavel


On Fri, Oct 19, 2012 at 12:49 PM, Igor Tandetnik  wrote:
> Григорий Григоренко  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 
>> sqlite3_column_XXX() to access data.
>>
>> My concern is - do open selective prepared stmts depend on something in db 
>> or lock something in db?
>
> Yes, until you call sqlite3_reset or sqlite3_finalize. For as long as the 
> statement is "active" (meaning, sqlite3_reset or sqlite3_finalize has not 
> been called after the most recent sqlite3_step call), it holds a read 
> transaction open.
>
>> 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 record from the database (or, in WAL journaling mode, you would be 
> able to, but the reader would still see original data).
>
>> 2. Can I drop client table while having such prepared stmt not closed?
>
> What do you mean by "closed" here?
>
>> 3. If I have a lot of such stmts (100 or more) will it somehow affect 
>> performance of SQLite or waste lots of memory?
>
> No, not really. There is a reasonably small data structure associated with a 
> prepared statement, on the order of a few hundred bytes perhaps.
>
>> Are there some kind of cursors for each selecting prepared stmt?
>
> In a sense. After a call to sqlite3_step and before a call of sqlite3_reset 
> or sqlite3_finalize, you might think of a statement handle as a form of a 
> cursor. Each subsequent sqlite3_step call advances this cursor forward by one 
> row.
>
>> 4. If there is a cursor, maybe there is a way to disconnect a cursor from 
>> stmt keeping the single record still available?
>
> No, short of making a copy of every column's value.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

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 taglist, (select strftime('%s', 'now') as tsend, strftime('%s', 'now', 
'-1 hour') as tsstart) as tsrange,
   outer join tagdata
 where tagname='flowrate'
   and tagdata.tagid = taglist.tagid
   and timestamp between tsstart and tsend;

will implement a sparse table that you query per tag.


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 the table btree:

create table data
(
ts  integer primary key,
v1  float,
v2  float,
v3  float,
v4  float
);
insert into data values (-9,1, NULL,  NULL,  NULL);
insert into data values (-8, NULL,2,  NULL,  NULL);
insert into data values (-7,5, NULL, 3,  NULL);
insert into data values (-6, NULL,6,  NULL, 4);
insert into data values (-5, NULL, NULL, 7,  NULL);
insert into data values (-4, NULL, NULL,  NULL, 8);
insert into data values (-3, NULL, NULL,  NULL,  NULL);
insert into data values (-2,9, NULL, 9,  NULL);
insert into data values (-1, NULL, NULL,  NULL,  NULL);
select * from data;
-9|1.0|||
-8||2.0||
-7|5.0||3.0|
-6||6.0||4.0
-5|||7.0|
-48.0
-3
-2|9.0||9.0|
-1
select fnn(v1), fnn(v2), fnn(v3), fnn(v4)
  from data
 where ts between -9 and 0;
1.0|2.0|3.0|4.0
select fnn(v1), fnn(v2), fnn(v3), fnn(v4)
  from data
 where ts between -8 and 0;
5.0|2.0|3.0|4.0
select fnn(v1), fnn(v2), fnn(v3), fnn(v4)
  from data
 where ts between -7 and 0;
5.0|6.0|3.0|4.0
select fnn(v1), fnn(v2), fnn(v3), fnn(v4)
  from data
 where ts between -6 and 0;
9.0|6.0|7.0|4.0
select fnn(v1), fnn(v2), fnn(v3), fnn(v4)
  from data
 where ts between -9 and -7;
1.0|2.0|3.0|

Your custom aggregate function looks like this:

typedef struct fnnCtx fnnCtx;
struct fnnCtx
{
double fnn;
int flag;
};

SQLITE_PRIVATE void fnnStep(sqlite3_context *context, int argc, sqlite3_value 
**argv)
{
fnnCtx* p = sqlite3_aggregate_context(context, sizeof(fnnCtx));
if (p && sqlite3_value_numeric_type(argv[0]) != SQLITE_NULL && p->flag == 0)
{
p->fnn = sqlite3_value_double(argv[0]);
p->flag = 1;
}
}

SQLITE_PRIVATE void fnnFinal(sqlite3_context *context)
{
fnnCtx* p = sqlite3_aggregate_context(context, 0);
if (p && p->flag == 1)
sqlite3_result_double(context, p->fnn);
}


---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org




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

Re: [sqlite] A question about prepared statements

[Igor Tandetnik]

Григорий Григоренко  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 
> sqlite3_column_XXX() to access data.
> 
> My concern is - do open selective prepared stmts depend on something in db or 
> lock something in db?

Yes, until you call sqlite3_reset or sqlite3_finalize. For as long as the 
statement is "active" (meaning, sqlite3_reset or sqlite3_finalize has not been 
called after the most recent sqlite3_step call), it holds a read transaction 
open.

> 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 record from the database (or, in WAL journaling mode, you would be able to, 
but the reader would still see original data).

> 2. Can I drop client table while having such prepared stmt not closed?

What do you mean by "closed" here?

> 3. If I have a lot of such stmts (100 or more) will it somehow affect 
> performance of SQLite or waste lots of memory?

No, not really. There is a reasonably small data structure associated with a 
prepared statement, on the order of a few hundred bytes perhaps.

> Are there some kind of cursors for each selecting prepared stmt? 

In a sense. After a call to sqlite3_step and before a call of sqlite3_reset or 
sqlite3_finalize, you might think of a statement handle as a form of a cursor. 
Each subsequent sqlite3_step call advances this cursor forward by one row.

> 4. If there is a cursor, maybe there is a way to disconnect a cursor from 
> stmt keeping the single record still available?

No, short of making a copy of every column's value.
-- 
Igor Tandetnik

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

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

My concern is - do open selective prepared stmts depend on something in db or 
lock something in db? 


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?

2. Can I drop client table while having such prepared stmt not closed? Will I 
be able to access data even if table was dropped?

3. If I have a lot of such stmts (100 or more) will it somehow affect 
performance of SQLite or waste lots of memory? Are there some kind of cursors 
for each selecting prepared stmt? 

4. If there is a cursor, maybe there is a way to disconnect a cursor from stmt 
keeping the single record still available? Its always about a single record. Is 
there anything to ease the burden of stopped selective stmt but not closing it?

5. Other disadvantages I might have missed?


A countrary to this is getting all the data from record to some user class and 
closing stmt. i dont like overhead of it. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Steinar Midtskogen]

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 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 would like to do something like:

SELECT coalesce(col_1), ..., coalesce(col_n) FROM v WHERE unix_time > 
strftime('%s', 'now', '-1 hour') ORDER BY unix_time DESC;

So I would typically want to access the last non-NULL value because of
the DESC keyword.  But if I understand things correctly, a statement
like above will never work because an aggregate function reads the
data in no particular order regardless of the ORDER BY statement.

I like Igor's suggestion.  Although not quite universal, it's clever.
Ryan's suggestion should work well, except that I will need a first(a,
b) and last(a, b) function (if I want to support both ascending and
descending order) and I can leave out the ORDER BY part.  So:

SELECT last(col_1, unix_time), ..., last(col_n, unix_time) FROM v WHERE 
unix_time > strftime('%s', 'now', '-1 hour');

Yes, it will have run through the whole set, whereas multiple SELECT
col_x FROM v WHERE unix_time > strftime('%s', 'now', '-1 hour') AND
col_x IS NOT NULL ORDER BY unix_time DESC LIMIT 1 will stop early.
But this will not be a problem for me since I want to have a modest
upper limit (1 hour) anyway.

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

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

[Carlos Milon Silva]

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

[Nick Shaw]

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 (emphasis added):

"If you have many client programs accessing a common database over a network, 
you should consider using a client/server database engine instead of SQLite. 
SQLite will work over a network filesystem, but because of the latency 
associated with most network filesystems, *performance will not be great*. 
Also, the file locking logic of many network filesystems implementation 
contains bugs (on both Unix and Windows). If file locking does not work like it 
should, *it might be possible for two or more client programs to modify the 
same part of the same database at the same time, resulting in database 
corruption*."

Seems pretty clear to me from that statement that SqLite would not suite your 
system's requirements as a central database.

Nick.

On Fri, Oct 19, 2012 at 4:21 PM, Nick Shaw  wrote:

> 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
> > periodically synchronizing with the master is a very reasonable 
> > thing to
> do in many situations.  Such an
> > approach can be very bandwidth efficient (if implemented correctly) 
> > and
> it has the huge advantage that is
> > allows for disconnected operation - it allows the clients to 
> > continue
> functioning when the network goes down.
> > [snip]
>
> I completely agree; this is what I do in our database application - 
> the clients have a local SqLite copy of a central MSSQL database, so 
> the clients can operate with the data when the network goes down 
> (which on some customers' sites is a fairly regular occurrence!).  To 
> avoid having to rectify duplicate rows / primary key violations / etc 
> when down, we just mark the local database as 'read-only' when the 
> link to MSSQL goes down so no changes can  be made to it 'offline'.  
> Whenever the link is up, we poll the MSSQL database for changes every 15 
> seconds or so, so the clients'
> SqLite copies are pretty much always in sync.  It adds a bit of 
> network traffic doing this every 15 seconds, but data doesn't change 
> very often in our application so there's rarely anything more than a 
> few COUNT queries going on.  Plus it means the client-side app can 
> usually just query the local SqLite database instead of talking over 
> the network to MSSQL all the time, which can dramatically speed things
>   up on slow networks.
>
> So I see no fundamental problem in using this kind of approach.  But 
> as Richard Hipp says, it depends on what your system needs are.  
> Operating on an SqLite database from multiple networked clients 
> (especially when on a Windows network) with data that is changing a 
> lot is not advised when performance and concurrency are important 
> factors.  (See http://www.sqlite.org/whentouse.html for specific 
> details, as Simon Slavin recommended).
>
> Nick.
> ___
> 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] Resolving Database locking issues in a multi-user environment

[Ben Morris]

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 I did not make explicit is that the central database
will also be a SQLite database, and thus a synchronisation algorithm will
not avoid the locking issues I mentioned in my original post. Potentially
we could have around fifty client applications all attempting simultaneous
writes to a SQLite database stored on a file server. Our application
generates an audit trail, so these writes will be happening very regularly
during normal operation - this isn't just a case of generating a few COUNT
queries.

I'm certainly not against an eventually-consistent implementation (in fact
I can see both benefits to existing use cases and new use cases emerging
from such an implementation). However, my colleague is attempting to
achieve the impression of immediate transactional consistency by executing
the synchronisation procedure once every second.

I have read the documentation thoroughly, and presented this to our
management team, however this seems to have been to no avail, so this post
was something of an appeal to authority.

On Fri, Oct 19, 2012 at 4:21 PM, Nick Shaw  wrote:

> 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
> > periodically synchronizing with the master is a very reasonable thing to
> do in many situations.  Such an
> > approach can be very bandwidth efficient (if implemented correctly) and
> it has the huge advantage that is
> > allows for disconnected operation - it allows the clients to continue
> functioning when the network goes down.
> > [snip]
>
> I completely agree; this is what I do in our database application - the
> clients have a local SqLite copy of a central MSSQL database, so the
> clients can operate with the data when the network goes down (which on some
> customers' sites is a fairly regular occurrence!).  To avoid having to
> rectify duplicate rows / primary key violations / etc when down, we just
> mark the local database as 'read-only' when the link to MSSQL goes down so
> no changes can  be made to it 'offline'.  Whenever the link is up, we poll
> the MSSQL database for changes every 15 seconds or so, so the clients'
> SqLite copies are pretty much always in sync.  It adds a bit of network
> traffic doing this every 15 seconds, but data doesn't change very often in
> our application so there's rarely anything more than a few COUNT queries
> going on.  Plus it means the client-side app can usually just query the
> local SqLite database instead of talking over the network to MSSQL all the
> time, which can dramatically speed things
>   up on slow networks.
>
> So I see no fundamental problem in using this kind of approach.  But as
> Richard Hipp says, it depends on what your system needs are.  Operating on
> an SqLite database from multiple networked clients (especially when on a
> Windows network) with data that is changing a lot is not advised when
> performance and concurrency are important factors.  (See
> http://www.sqlite.org/whentouse.html for specific details, as Simon
> Slavin recommended).
>
> Nick.
> ___
> 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] Resolving Database locking issues in a multi-user environment

[Nick Shaw]

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
> periodically synchronizing with the master is a very reasonable thing to do 
> in many situations.  Such an
> approach can be very bandwidth efficient (if implemented correctly) and it 
> has the huge advantage that is
> allows for disconnected operation - it allows the clients to continue 
> functioning when the network goes down.
> [snip]

I completely agree; this is what I do in our database application - the clients 
have a local SqLite copy of a central MSSQL database, so the clients can 
operate with the data when the network goes down (which on some customers' 
sites is a fairly regular occurrence!).  To avoid having to rectify duplicate 
rows / primary key violations / etc when down, we just mark the local database 
as 'read-only' when the link to MSSQL goes down so no changes can  be made to 
it 'offline'.  Whenever the link is up, we poll the MSSQL database for changes 
every 15 seconds or so, so the clients' SqLite copies are pretty much always in 
sync.  It adds a bit of network traffic doing this every 15 seconds, but data 
doesn't change very often in our application so there's rarely anything more 
than a few COUNT queries going on.  Plus it means the client-side app can 
usually just query the local SqLite database instead of talking over the 
network to MSSQL all the time, which can dramatically speed things
  up on slow networks.

So I see no fundamental problem in using this kind of approach.  But as Richard 
Hipp says, it depends on what your system needs are.  Operating on an SqLite 
database from multiple networked clients (especially when on a Windows network) 
with data that is changing a lot is not advised when performance and 
concurrency are important factors.  (See http://www.sqlite.org/whentouse.html 
for specific details, as Simon Slavin recommended).

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

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

[Ryan Johnson]

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 (unless Steinar has an appropriate index on
every single column, which I doubt).

Or unless a non-null value is typically found in the first few rows of a large 
table. In other words, if the ratio of nulls to non-nulls is small (and if it's 
large, then I'd be thinking of a different schema, one that represents a sparse 
table more efficiently).
I had assumed a sparse table because I'm having a hard time imagining 
why you'd want to coalesce dense rows in this way. But you're right: in 
that case you'd really want to store sparse columns in separate tables, 
which would make the nested query approach by far the best (especially 
if you alias the sparse table's foreign key with its rowid).


Ryan

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

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

[Igor Tandetnik]

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 on
> every single column, which I doubt).

Or unless a non-null value is typically found in the first few rows of a large 
table. In other words, if the ratio of nulls to non-nulls is small (and if it's 
large, then I'd be thinking of a different schema, one that represents a sparse 
table more efficiently).
-- 
Igor Tandetnik

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

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

[Ryan Johnson]

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 b FROM v WHERE b IS NOT NULL ORDER BY a LIMIT 1),
  (SELECT c FROM v WHERE c IS NOT NULL ORDER BY a LIMIT 1),
  (SELECT d FROM v WHERE d IS NOT NULL ORDER BY a LIMIT 1),
  (SELECT e FROM v WHERE e IS NOT NULL ORDER BY a LIMIT 1),
  (SELECT f FROM v WHERE f IS NOT NULL ORDER BY a LIMIT 1);

to get 1|5|2|4|9?

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;

Note however that your origial statement is likely more efficient, as it can 
stop scanning early, whereas my variant will look at every record.
That, and it will break if those fields aren't numbers, or if any take 
negative values.


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 on 
every single column, which I doubt).


The python version would be:

class fnn:
def __init__(self):
self.key,self.val = None,None
def step(self, key, val):
if val is not None and (self.key is None or key < self.key):
self.key,self.val = key,val
def finalize(self):
return self.val

conn.create_aggregate('fnn', 2, fnn)


And the query would become:

select fnn(a,b), fnn(a,c), fnn(a,d), fnn(a,e), fnn(a,f) from v


Regards,
Ryan

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

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

[Igor Tandetnik]

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) & 4294967296, ...

-- 
Igor Tandetnik

___
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

[Simon Slavin]

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 local table is compared, row-by-row, field-by-field,
> to the corresponding table in the master database (both tables are loaded
> into memory as array structures to speed up the comparison). If any
> differences are found, or the local row is missing, the local row is
> updated/inserted using the data from the master database

This is the only way to successfully sync multiple copies of a database.  if 
you are going to keep distributed copies, then this is how to do it.  However, 
you do not mention the possibility of two different 'child' copies both having 
the same row modified.  You will need a strategy for reconciling cases where 
this happens and the new rows don't exactly match.

> Ignoring the obvious fact that this will not resolve our locking and
> malformation problems, I'm also deeply concerned about the maintainability
> and supportability of such code. Given the fact that I cannot get any
> traction with my management team, I was hoping an appeal to authority might
> make them see sense.

Would it help if the primary author of SQLite himself says whether SQLite is or 
is not suitable ?



See the section at the bottom titled "Situations Where Another RDBMS May Work 
Better".  I don't know if this does fit your situation exactly, but it does 
look relevant.  You didn't cite this page in your post so I thought you should 
see it.

However, I see Dr Hipp just posted to this thread himself so he may have 
comments which address your situation exactly.

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

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

[Igor Tandetnik]

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 NULL ORDER BY a LIMIT 1),
>  (SELECT c FROM v WHERE c IS NOT NULL ORDER BY a LIMIT 1),
>  (SELECT d FROM v WHERE d IS NOT NULL ORDER BY a LIMIT 1),
>  (SELECT e FROM v WHERE e IS NOT NULL ORDER BY a LIMIT 1),
>  (SELECT f FROM v WHERE f IS NOT NULL ORDER BY a LIMIT 1);
> 
> to get 1|5|2|4|9?

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;

Note however that your origial statement is likely more efficient, as it can 
stop scanning early, whereas my variant will look at every record.
-- 
Igor Tandetnik

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

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

[Black, Michael (IS)]

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,null,4,4,7);
insert into v values(3,null,5,5,4,6);
insert into v values(4,1,6,6,null,5);
SELECT * FROM (SELECT b FROM v WHERE b IS NOT NULL ORDER BY a LIMIT 1),
   (SELECT c FROM v WHERE c IS NOT NULL ORDER BY a LIMIT 1),
   (SELECT d FROM v WHERE d IS NOT NULL ORDER BY a LIMIT 1),
   (SELECT e FROM v WHERE e IS NOT NULL ORDER BY a LIMIT 1),
   (SELECT f FROM v WHERE f IS NOT NULL ORDER BY a LIMIT 1);
create view vb as select b from v where b is not null order by a limit 1;
create view vc as select c from v where c is not null order by a limit 1;
create view vd as select d from v where d is not null order by a limit 1;
create view ve as select e from v where e is not null order by a limit 1;
create view vf as select f from v where f is not null order by a limit 1;
select * from vb,vc,vd,ve,vf;
.headers on
b|c|d|e|f
1|5|2|4|9


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Steinar Midtskogen [stei...@latinitas.org]
Sent: Friday, October 19, 2012 7:04 AM
To: General Discussion of SQLite Database
Subject: EXT :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 c FROM v WHERE c IS NOT NULL ORDER BY rowid LIMIT 1

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 NULL ORDER BY a LIMIT 1),
  (SELECT c FROM v WHERE c IS NOT NULL ORDER BY a LIMIT 1),
  (SELECT d FROM v WHERE d IS NOT NULL ORDER BY a LIMIT 1),
  (SELECT e FROM v WHERE e IS NOT NULL ORDER BY a LIMIT 1),
  (SELECT f FROM v WHERE f IS NOT NULL ORDER BY a LIMIT 1);

to get 1|5|2|4|9?

--
Steinar Midtskogen
___
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] Resolving Database locking issues in a multi-user environment

[Richard Hipp]

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 our application is deployed, we're seeing a very high number of
> database locked exceptions generated (as well as very poor performance).
>
> Performance was understandably worse when the default journalling mode was
> enabled, so this has been turned off, leading to almost daily occurrences
> of malformed databases.
>
> I've repeatedly argued that SQLite is clearly inappropriate for use in a
> multi-user environment, as per the documentation, and that we should make
> the switch to a client/server RDBMS as soon as possible. However, given my
> perceived lack of seniority, the management team has elected to go with a
> local-master synchronisation process as recommended by the primary
> developer.
>
> This developer is currently implementing this synchronisation algorithm to
> allow users to work on a local SQLite database and have that data
> transferred to a master SQLite database on the network.
>
> When a local row is changed, a Sync flag is set to true. The
> synchronisation algorithm is triggered by a timer running every second, and
> consists of two methods: Upload and Download. Any SQL commands mentioned
> below are being built using String.Format, by combining data and metadata
> extracted from methods available in the ORM layer.
>
> 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 local table is compared, row-by-row, field-by-field,
> to the corresponding table in the master database (both tables are loaded
> into memory as array structures to speed up the comparison). If any
> differences are found, or the local row is missing, the local row is
> updated/inserted using the data from the master database
>
> Ignoring the obvious fact that this will not resolve our locking and
> malformation problems, I'm also deeply concerned about the maintainability
> and supportability of such code. Given the fact that I cannot get any
> traction with my management team, I was hoping an appeal to authority might
> make them see sense.
>
> If anyone could share their honest opinions of both my suggested approach,
> and my colleague's, I would be very grateful.
>

A lot depends on what your application is trying to do...

If you need consistency between all clients, then using a client/server
RDBMS is clearly the best approach.  But no every application needs this.
Using a separate SQLite database on each client to serve as a local cache
of the master database and then periodically synchronizing with the master
is a very reasonable thing to do in many situations.  Such an approach can
be very bandwidth efficient (if implemented correctly) and it has the huge
advantage that is allows for disconnected operation - it allows the clients
to continue functioning when the network goes down.  The disadvantage is
that individual clients are not always completely up-to-date with the
latest changes.  After they've had a chance to synchronize, their display
will be up-to-date.  (We say that the clients are "eventually
consistent".)  But it might take a few seconds or a few minutes to achieve
consistency, depending on how the network is doing.

So there are trade-offs.  Is it important that your clients have an
absolutely consistent view of the state of the system at all times, but can
put up with outages due to network issues?  Or is it better to have
eventual consistency and be tolerant of network failures?  If you need
absolute consistency, then a client/server RDBMS is clearly the best
approach.  But if eventual consistency is sufficient and network failure
tolerance is desirable, then using a local SQLite cache of a master
database might be the superior approach.

A familiar example of an eventually-consistent system would be the Fossil
DVCS.  Fossil stores all content in a local SQLite database, then
synchronizes with peers over the network.  Fossil (unlike CVS or
Subversion) allows development to continue while off network, and then
synchronizes when connectivity is restored.  Git works the same way, though
Git uses an ad-hoc pile-of-files database for its local cache rather than
an RDBMS.

Which reminds me - another advantage of having local caches of the master
database is that you get automatic backups.  If the server caches fire or
otherwise self-destructs, all of your content can be recovered from any of
the clients.  In other words, you get automatic backups.



>
> Many thanks,
>
> Magos
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> 

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

[Ben Morris]

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
database locked exceptions generated (as well as very poor performance).

Performance was understandably worse when the default journalling mode was
enabled, so this has been turned off, leading to almost daily occurrences
of malformed databases.

I've repeatedly argued that SQLite is clearly inappropriate for use in a
multi-user environment, as per the documentation, and that we should make
the switch to a client/server RDBMS as soon as possible. However, given my
perceived lack of seniority, the management team has elected to go with a
local-master synchronisation process as recommended by the primary
developer.

This developer is currently implementing this synchronisation algorithm to
allow users to work on a local SQLite database and have that data
transferred to a master SQLite database on the network.

When a local row is changed, a Sync flag is set to true. The
synchronisation algorithm is triggered by a timer running every second, and
consists of two methods: Upload and Download. Any SQL commands mentioned
below are being built using String.Format, by combining data and metadata
extracted from methods available in the ORM layer.

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 local table is compared, row-by-row, field-by-field,
to the corresponding table in the master database (both tables are loaded
into memory as array structures to speed up the comparison). If any
differences are found, or the local row is missing, the local row is
updated/inserted using the data from the master database

Ignoring the obvious fact that this will not resolve our locking and
malformation problems, I'm also deeply concerned about the maintainability
and supportability of such code. Given the fact that I cannot get any
traction with my management team, I was hoping an appeal to authority might
make them see sense.

If anyone could share their honest opinions of both my suggested approach,
and my colleague's, I would be very grateful.

Many thanks,

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

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

[Steinar Midtskogen]

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 c FROM v WHERE c IS NOT NULL ORDER BY rowid LIMIT 1

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 NULL ORDER BY a LIMIT 1),
  (SELECT c FROM v WHERE c IS NOT NULL ORDER BY a LIMIT 1),
  (SELECT d FROM v WHERE d IS NOT NULL ORDER BY a LIMIT 1),
  (SELECT e FROM v WHERE e IS NOT NULL ORDER BY a LIMIT 1),
  (SELECT f FROM v WHERE f IS NOT NULL ORDER BY a LIMIT 1);

to get 1|5|2|4|9?

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

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

[Simon Slavin]

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 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 c FROM v WHERE c IS NOT NULL ORDER BY rowid LIMIT 1

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

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

[Dan Kennedy]

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 can assume that the
database is still locked until you do a _finalize().

If you are using the results of a SELECT to figure out a bunch of other
instructions like INSERT or DELETE you can do it two ways:

A) Quicky save the results of stepping into an array.  Once the SELECT is
finalized, look through the array and figure out what you want to do about
it.

B) Generate the INSERT/DELETE commands while stepping but keep them
somewhere, either as a data array or by accumulating the text of the SQL
commands in a string, rather than executing them immediately.  Once you're
finished stepping, execute the commands.  (You may choose to use _exec to
execute them all in one go.)




Thank you for the clarification, but I still misunderstand the documentation
some way.

In the documentation about WAL mode it says:
"Writers merely append new content to the end of the WAL file. Because
writers do nothing that would interfere with the actions of readers, writers
and readers can run at the same time. However, since there is only one WAL
file, there can only be one writer at a time."

Maybe the magic words I don't fully understand are what's written later:
"A checkpoint operation takes content from the WAL file and transfers it
back into the original database file. A checkpoint can run concurrently with
readers, however the checkpoint must stop when it reaches a page in the WAL
that is past the read mark of any current reader. The checkpoint has to stop
at that point because otherwise it might overwrite part of the database file
that the reader is actively using. The checkpoint remembers (in the
wal-index) how far it got and will resume transferring content from the WAL
to the database from where it left off on the next invocation."

I logically do understand that there can't be 2 writers updating the
database at the same time, but I don't understand why the second insert
statement in the example below won't work without finalizing the SELECT
query?


sqlite3* conn_1;
sqlite3* conn_2;
//(..opening db files and so on)

sqlite3_stmt* pVM_1;
sqlite3_stmt* pVM_2;

sqlite3_prepare(conn_1, "SELECT * FROM test_table_2;", -1,_1,);
//sets WAL end mark for pVM_1?
sqlite3_prepare(conn_2, "SELECT * FROM test_table_2;", -1,_2,);
//sets WAL end mark for pVM_2?

nRet = sqlite3_step(pVM_1); //stepping if<  WAL end mark set for pVM_1, set
new read mark?
nRet = sqlite3_step(pVM_2); //stepping if<  WAL end mark set for pVM_2, set
new read mark?

//statements below will add content to the end of the WAL file?
nRet = sqlite3_exec(conn_1, "BEGIN IMMEDIATE TRANSACTION;", 0, 0,);
nRet = sqlite3_exec(conn_1, "INSERT INTO test_table_1 VALUES(1, 1);", 0, 0,
);
nRet = sqlite3_exec(conn_1, "COMMIT;", 0, 0,);

nRet = sqlite3_step(pVM_1); //stepping if<  WAL end mark set for pVM_1, set
new read mark?
nRet = sqlite3_step(pVM_2); //stepping if<  WAL end mark set for pVM_2, set
new read mark?

//sqlite3_finalize(pVM_1);
//sqlite3_finalize(pVM_2);

//The execution below will fail with SQLITE_BUSY if the SELECT statement
pVM_2 isn't finalized
//(Why won't it append new data in the end of the WAL file just like the
successful insert above?)
nRet = sqlite3_exec(conn_2, "BEGIN IMMEDIATE TRANSACTION;", 0, 0,);
nRet = sqlite3_exec(conn_2, "INSERT INTO test_table_1 VALUES(1, 1);", 0, 0,
);
nRet = sqlite3_exec(conn_2, "COMMIT;", 0, 0,);


When INSERT is executed it doesn't add new row to the WAL file, it
have to add new row to some database page possibly changing some other
pages on the way too. These changed pages are written into WAL file.
Because of this fact when INSERT statement is executed it must be
executed on the latest version of the database pages. Otherwise it can
create an alternative database version that can't be merged with
version created by other INSERT statements. So when you execute INSERT
statement, or start IMMEDIATE transaction, or convert existing
read-only transaction into writing transaction SQLite have to make
sure that this transaction sees the latest version of the database.
But when you start read-only transaction SQLite ensures that all the
way through it sees a consistent database state, and that would be the
state of the database at the beginning of the transaction. So when you
start executing SELECT statement you lock conn_2 into the database
state that was at that moment. Then you do database changes on conn_1,
so database state is changed. Then you want to execute changes in
conn_2, but it can't do that because it's locked into database state
which is not latest one. And it can't change the visible database
state because SELECT statement is still in progress.


Exactly. To successfully upgrade a 

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

[Steinar Midtskogen]

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 LIMIT 1),
  (SELECT c FROM v WHERE c IS NOT NULL LIMIT 1),
  (SELECT d FROM v WHERE d IS NOT NULL LIMIT 1),
  (SELECT e FROM v WHERE e IS NOT NULL LIMIT 1);

to get 1|5|2|4|9.  But is there a more convenient way?  I don't think
I can define an aggregate function (similar to coalesce) since the
order will not be defined.  But perhaps I'm missing the obvious
solution.

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

Re: [sqlite] xColumn called twice for a value

[Richard Hipp]

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 c, c+1, c-1 FROM v WHERE c IS NOT NULL;
>
> Then xColumn() will get called a second time for c, which seems
> wasteful if c takes time to compute.  Or very bad if the computation
> has side effects (which it probably shouldn't have).
>
> Is there any reason for this behaviour?
>

xColumn() might be called any number of times for a single column - once,
one hundred times, not at all.  SQLite makes no guarantees about this.  The
virtual table implementation must return the same value for all calls.

The reason is that it is sometimes convenient for the SQLite core to call
xColumn multiple times and that it is not normally a problem for the
virtual table to implement this efficiently, using caching if necessary.


>
> My SQlite version is 3.7.14.
>
> --
> Steinar Midtskogen
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] xColumn called twice for a value

[Steinar Midtskogen]

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 second time for c, which seems
wasteful if c takes time to compute.  Or very bad if the computation
has side effects (which it probably shouldn't have).

Is there any reason for this behaviour?

My SQlite version is 3.7.14.

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