Re: [sqlite] How does one block a reader connection?

2017-03-26 Thread Rowan Worth
On 26 March 2017 at 14:17, Keith Medcalf  wrote:

> If you do not specify your own custom busy handler (to display flying ball
> bearings, etc, or do your own exponential sleeping, etc) then the default
> busy_handler is used.  The default busy handler does its own exponential
> backoff algorithm but does not display whirling ball bearings or anything
> else, it just sleeps silently.


Nitpick: the "default" busy_handler is in fact not used by default. That
is, if you open a connection and don't specify a busy handler or timeout
then you will get SQLITE_BUSY returned immediately if a lock cannot be
acquired. There are essentially three busy handler states:

(1) Normal settings: no busy handler is active
(2) sqlite3_busy_timeout()/PRAGMA timeout invoked: a sleep() with
exponential backoff busy handler is used
(3) sqlite3_busy_handler() invoked: the user defined busy handler is used

Sqlite implements the timeout feature by installing a busy_handler behind
the scenes (which is called the "default" busy handler in the code).
There's only one busy_handler per connection though, so whichever is called
last out of sqlite3_busy_handler() or sqlite3_busy_timeout() will take
precedence.

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


Re: [sqlite] table-naming-expression impact on sqlite3_prepare

2017-03-26 Thread Simon Slavin

On 27 Mar 2017, at 1:52am, Kees Nuyt  wrote:

> It's something that can be done by any host language. No need to implement
> that in SQL.

Also, you’re scripting a shell tool.  So write a text file with your SQL 
commands in and feed it to the shell tool whole.

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


Re: [sqlite] table-naming-expression impact on sqlite3_prepare

2017-03-26 Thread Kees Nuyt
On Sun, 26 Mar 2017 15:34:22 -0700, petern 
wrote:

> Here is your suggestion with matched brackets and quotes and assuming
> mytable has a column [tablename]:
>
> select eval(printf('create table %s (a,b,c)',tablename)) from mytables;

I think you mean:

eval(printf('create table %s (a,b,c)',(select tablename from mytables)));

It's something that can be done by any host language. No need to implement
that in SQL.

-- 
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] table-naming-expression impact on sqlite3_prepare

2017-03-26 Thread E.Pasma

27 mrt 2017, petern:
In general I've been thinking about materializing data dependent  
temporary
tables and even using them in CTE's. The tremendous expressive  
economy of
TCL and somewhat built-in support within SQLite got me thinking.   
Consider
the problem of pivot table function for creating temp.crosstab_*  
summary

tables...


I left out the example command line script from you reply. This  
clarifies your somewahat abstract requirement. Now I also see why  
earlier on you wished eval() to return query result. But, as eval()  
does ddl too I just make it define a temp view. That can be selected  
from afterwards. See below. Thanks for the TCL example that helps me  
learn. Best regards, E. Pasma, sponsor of https://facebook.com/RadioParadijs


.version
SQLite 3.18.0 2017-03-06 20:44:13  
ec529bf11b16c801ea438e57d208ff7e4cedf1f9

select load_extension('eval');

drop table if exists sales;
create table sales(year integer,fruit text,tons integer);
insert into sales
values(2017,'mangos',3),(2016,'peaches',2),(2017,'apples',7), 
(2017,'peaches',6),(2016,'mangos',12);

--select * from sales;
select ddl,  eval(ddl)
from(
select  'create temp view vttt as select year, '
||  group_concat('sum(case when fruit='''
||  fruit
||  ''' then tons end) as '
||  fruit)
||  ' from sales group by year'
as ddl
from( select fruit from sales group by fruit)
)
;
create temp view vttt as select year, sum(case when fruit='apples'  
then tons end) as apples,sum(case when fruit='mangos' then tons end)  
as mangos,sum(case when fruit='peaches' then tons end) as peaches from  
sales group by year|

.header on
select * from vttt;
year|apples|mangos|peaches
2016||12|2
2017|7|3|6


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


Re: [sqlite] Syntax. table-function-name vs table-naming-function-name

2017-03-26 Thread petern
Richard, thank you for your reply.  I really appreciate it.  The fact that
you have carefully thought about how to cross the FROM clause barrier with
expressions is itself a useful fact.  If you say the current implementation
is painted into a corner on this issue I believe you.  It would be
impossible to deduce this fact by simply studying documentation or source
code.  Thank you very much for taking my question.

At some point I may try what you suggest and see what I can learn.  In the
meantime, the CSV virtual table example appears to be closest to where I'd
like to go next.

https://sqlite.org/csv.html

That example allows the caller to specify a creation schema.  Expanding on
the schema specifier, perhaps there is a way to make xBestIndex() and
xFilter() work more generally in the problem domain of lightly encapsulated
dynamic tables.







On Sun, Mar 26, 2017 at 11:44 AM, Richard Hipp  wrote:

> On 3/25/17, petern  wrote:
> >
> > Why can't we have a parallel syntax branch for scalar valued
> > "table-naming-function-name"?  In other words, why not have support for
> > simply naming an existing table or view by return value of a scalar
> > function?
> >
>
> The easiest way for me to answer this is to ask you to provide a
> sample implementation.  After you've work on the problem for a while,
> I think you will begin to understand why it is not possible.  I can't
> seem to come up with words to help make that realization any easier.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] table-naming-expression impact on sqlite3_prepare

2017-03-26 Thread petern
That you for your kind comment about my table-naming-expression proposal.

Here is your suggestion with matched brackets and quotes and assuming
mytable has a column [tablename]:

select eval(printf('create table %s (a,b,c)',tablename)) from mytables;

Significant credit should also go to the built in printf function.

In general I've been thinking about materializing data dependent temporary
tables and even using them in CTE's. The tremendous expressive economy of
TCL and somewhat built-in support within SQLite got me thinking.  Consider
the problem of pivot table function for creating temp.crosstab_* summary
tables...


-
$
tclsh

%
load ./tcldbshell.so sqlite3

%
sqlite3 db :memory:

%
proc createpivotsum {table row column value} {
  set outputtable "temp.crosstab\_$table\_$row\_$column\_$value";
  set q [list "select $row"];
  db eval "SELECT DISTINCT ($column)col FROM $table" cols {lappend q
"sum(case when $column='$cols(col)' then $value else 0 end)$cols(col)"};
  db eval "DROP TABLE IF EXISTS $outputtable; CREATE TABLE $outputtable AS
[join $q ,] FROM $table GROUP BY $row;";
  return $outputtable;
};

%
db function pivotsum -deterministic -argcount 4 createpivotsum;

%
#Wouldn't it be great if this TCL function could be called from the regular
shell?

%
#Standard libtclsqlite can't invoke shell.c yet so I added a shell call to
libtclsqlite.

%
db shell;

sqlite>
--Let's run a fruit stand.

sqlite>
drop table if exists sales;
create table sales(year integer,fruit text,tons integer);
insert into sales
values(2017,'mangos',3),(2016,'peaches',2),(2017,'apples',7),(2017,'peaches',6),(2016,'mangos',12);

sqlite>
select * from sales;

year,fruit,tons
2017,mangos,3
2016,peaches,2
2017,apples,7
2017,peaches,6
2016,mangos,12

sqlite>
--What are current sales by year and type of fruit?
select pivotsum('sales','year','fruit','tons');
"pivotsum('sales','year','fruit','tons')"

temp.crosstab_sales_year_fruit_tons

sqlite>
select * from crosstab_sales_year_fruit_tons;

year,mangos,peaches,apples
2016,12,2,0
2017,3,6,7

sqlite>
--Try doing pivot stuff using in CTA noting that pivotsum is deterministic!
with t as (select (select pivotsum('sales','year','fruit','tons')),* from
crosstab_sales_year_fruit_tons)
   ...> select * from t;

Error: database table is locked

sqlite>
--So much for CTE's and deterministic column functions...

sqlite>
.exit

%
#now back in tclsh

%
exit

$
-

For those that are interested I used slightly modified versions of
tclsqlite.c and shell.c to build libtclsqlite. About 10 lines changed
including the #ifndef TCL_DB_SHELL statements.















On Sun, Mar 26, 2017 at 10:36 AM, E.Pasma  wrote:

> 26-03-2017 petern :
>
> > The table-naming-expression, if
> > normal expressions are allowed, would obviously require sqlite3_prepare
> to
> > consult the database in situations where the name string expression
> depended
> > on a SQL statement being evaluated. Is this the main problem with
> allowing
> > dynamically named tables SQLite?
>
> Hi, in an earliar post you mentioned the loadable extension eval.c that
> adds
> the eval() SQL function.  Just for  my understanding, may I assume that you
> currently use that as a work around. E.g.
> select eval(printf("create table %s (a,b,c)', tablename) from mytables;
>
> Anyway thanks for mentioning the existense of eval(). E. Pasma
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Syntax. table-function-name vs table-naming-function-name

2017-03-26 Thread Richard Hipp
On 3/25/17, petern  wrote:
>
> Why can't we have a parallel syntax branch for scalar valued
> "table-naming-function-name"?  In other words, why not have support for
> simply naming an existing table or view by return value of a scalar
> function?
>

The easiest way for me to answer this is to ask you to provide a
sample implementation.  After you've work on the problem for a while,
I think you will begin to understand why it is not possible.  I can't
seem to come up with words to help make that realization any easier.

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


Re: [sqlite] table-naming-expression impact on sqlite3_prepare

2017-03-26 Thread E.Pasma
26-03-2017 petern :

> The table-naming-expression, if
> normal expressions are allowed, would obviously require sqlite3_prepare to
> consult the database in situations where the name string expression depended
> on a SQL statement being evaluated. Is this the main problem with allowing
> dynamically named tables SQLite?

Hi, in an earliar post you mentioned the loadable extension eval.c that adds
the eval() SQL function.  Just for  my understanding, may I assume that you
currently use that as a work around. E.g.
select eval(printf("create table %s (a,b,c)', tablename) from mytables;

Anyway thanks for mentioning the existense of eval(). E. Pasma

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


[sqlite] table-naming-expression impact on sqlite3_prepare

2017-03-26 Thread petern
My thanks to everyone who responded to my read blocking transaction
isolation question.

Further to my other question/proposal with no responses, what would be the
impact on sqlite3_prepare to introduce a new branch called
table-naming-expression into the syntax graph at:

https://www.sqlite.org/syntax/table-or-subquery.html

?

[I hope everybody had a chance to see my original narrower proposal called
table-naming-function-name.  I didn't see my message posted to the board
that time but it was definitely sent.]

The table-naming-expression, if normal expressions are allowed, would
obviously require sqlite3_prepare to consult the database in situations
where the name string expression depended on a SQL statement being
evaluated.

Is this the main problem with allowing dynamically named tables SQLite?  Is
there a design goal which states that sqlite3_prepare shall never return a
lock related error message such as SQLITE_BUSY?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] How does one block a reader connection?

2017-03-26 Thread petern
Keith, I understand your point.  The timescale of polling is between 1 and
10 seconds by sleep loop depending on operational objectives.  This range
of sleep loop will have a corresponding latency of between 0.5 and 5
seconds for single commands with a uniform arrival time distribution.

The idea was to get near the 0.5 second latency of the 1 second sleep loop
while actually polling in a very gentle 10 second sleep loop.

Thanks for the colorful explanation of custom busy handlers.



On Sat, Mar 25, 2017 at 11:17 PM, Keith Medcalf  wrote:

> Saturday, 25 March, 2017 23:44. petern 
> wrote:
>
> > Can anybody explain the purpose of
> > http://sqlite.org/c3ref/busy_handler.html
> > ?   It seems the only practical use would be to allow the caller to give
> > the engine a suggested lock deadline before SQLITE_BUSY is returned by
> > sqlite3_exec or sqlite3_step   Then, if the calling thread would prefer
> to
> > wait longer, it can just retry with progressively smaller lock deadline
> > until it's time to do something else.
>
> The busy handler is used if you have set a timeout to obtain a lock.  If
> you try to do
>
> BEGIN IMMEDIATE
>
> on a database that is not in WAL mode, then in order to obtain a lock
> there must be no active readers or writers.  If there are, the lock cannot
> be obtained an SQLITE_BUSY is returned and you try again later.  If you set
> a timeout with PRAMGA timeout, that specifies the amount of time to wait
> for a lock before returning SQLITE_BUSY.  The busy handler is used to
> "handle" the "busy" state (that is, rather than return SQLITE_BUSY to the
> caller, your busy handler is called.  You do something (like wait a while
> using sleep, then return, perhaps displaying flying ball bearings or other
> annoyances to indicate to the user that the program is waiting).  Lather
> rinse repeat until the specified timeout expires at which time SQLITE_BUSY
> is returned to the caller.
>
> If you do not specify your own custom busy handler (to display flying ball
> bearings, etc, or do your own exponential sleeping, etc) then the default
> busy_handler is used.  The default busy handler does its own exponential
> backoff algorithm but does not display whirling ball bearings or anything
> else, it just sleeps silently.
>
> > In my application it is more ideal if commands in the command table are
> > known to the readers immediately but without the overhead of rapid
> polling
> > by the readers.  Setting a suggested wait time and then waiting again if
> > SQLITE_BUSY comes back early and there's nothing else to do would be just
> > fine.
>
> Again, you have specified "RAPID POLLING".  Can you please define what you
> mean by "RAPID POLLING"?  To me, "RAPID POLLING" means using the immensely
> stupid spinlock type implementation such as:
>
> open connection
> while (nothing to do)
>query database for something to do
> do what needs doing
>
> If you need things to go "RAPIDLY" then you need to use some IPC
> notification mechanism.
>
> Please state in explicit terms your definition of "RAPIDLY".   Using terms
> without defining them makes it difficult for anyone to understand what you
> are talking about.  One persons "RAPIDLY" may mean "after the MoC is filled
> out and endorsed, operations windows are arranged, and the board operator
> gives the final go ahead -- in other words, two weeks to six months".
> Another person's definition of "RAPIDLY" may be "within 250 milliseconds".
> Please specify what you definition of "RAPIDLY" is, giving minimum and
> maximum bounds in common time units.
>
> If RAPIDLY means "in less than one second, 33% of the time, within two
> seconds 75% of the time, and no more than three seconds ever" then you are
> talking about rather SLOW POLLING.
>
> open connection
> while (nothing to do)
>sleep 1 second
>query database for something to do
> do what needs doing
>
> This type of SLOW POLLING will use about 1% of the available CPU on a 4.77
> Mhz 8086 CPU while in the polling loop.  This means that you can have about
> 100 such processes polling at the same time on a 4.77 Mhz 8086 equipped
> computer.  On a modern CPU with a couple of cores and about 2Ghz per code,
> you should be able to run many tens of thousands of such processes
> simultaneously and STILL have resources left over.  (though the OS
> scheduler will probably consume more CPU than the polling loops do, and the
> process table and OS resources are likely to become exhausted long before
> you hit any reasonable limit on the resource limits for the simultaneously
> polling processes themselves.)
>
> > [Regarding the suggestion of synchronizing the readers with another
> > concurrency object, I could do that.  But if the database connection
> > itself
> > can coordinate the same thing, the reader program is a lot simpler and
> > more
> > portable.]
>
> > I'm not sure I understand Simon's comments how using EXCLUSIVE
> TRANSACTION
> > 

Re: [sqlite] 3.17.0 bug report: FTS5 insertion puts a wrong value in last_insert_rowid

2017-03-26 Thread Florian Weimer
* Gwendal Roué:

> I have found a regression in SQLite 3.17.0. In the following SQL statements:
>
> CREATE VIRTUAL TABLE t1 USING FTS5(content);
> INSERT INTO t1(content) VALUES ('some text');
> SELECT last_insert_rowid(); // 10 (wrong)
> SELECT rowid FROM t1;   // 1
>
> The expected value of the the SQL function last_insert_rowid()
> function is 1, not 10. Same for the C function
> sqlite3_last_insert_rowid().

I think this is a known issue.  SQLite 3.18 adds a
sqlite3_set_last_insert_rowid() function and uses it in “the new
interface in the FTS3, FTS4, and FTS5 extensions to ensure that the
sqlite3_last_insert_rowid() interface always returns reasonable
values”.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] 3.17.0 bug report: FTS5 insertion puts a wrong value in last_insert_rowid

2017-03-26 Thread Gwendal Roué
Hello,

I have found a regression in SQLite 3.17.0. In the following SQL statements:

CREATE VIRTUAL TABLE t1 USING FTS5(content);
INSERT INTO t1(content) VALUES ('some text');
SELECT last_insert_rowid(); // 10 (wrong)
SELECT rowid FROM t1;   // 1

The expected value of the the SQL function last_insert_rowid() function is 1, 
not 10. Same for the C function sqlite3_last_insert_rowid().

This bug was not present in 3.16.2.

This bug is very similar to http://www.sqlite.org/src/tktview?name=13137dccf3, 
which affected FTS3.

Cheers,
Gwendal Roué

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


Re: [sqlite] How does one block a reader connection?

2017-03-26 Thread Jens Alfke

> On Mar 25, 2017, at 3:52 PM, petern  wrote:
> 
> So finally, here is the question.  Is there a SQLite API way for reader
> connections to block and wait for a meaningful change, like a new row, in
> the 'cmd' table instead of madly polling and using up database concurrency
> resources?  [Block with timeout would be even more desirable of course.]

No; I’ve asked about this myself.

Alternatives to polling are either (a) using a platform-specific filesystem 
notification API to inform you when the database file(s) have been changed; or 
(b) using a platform-specific cross-process notification API to let the writer 
process signal that there are new changes. If you only want to know about 
specific types of changes, then option (b) sounds best.

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


Re: [sqlite] How does one block a reader connection?

2017-03-26 Thread Keith Medcalf
Saturday, 25 March, 2017 23:44. petern  wrote:

> Can anybody explain the purpose of
> http://sqlite.org/c3ref/busy_handler.html
> ?   It seems the only practical use would be to allow the caller to give
> the engine a suggested lock deadline before SQLITE_BUSY is returned by
> sqlite3_exec or sqlite3_step   Then, if the calling thread would prefer to
> wait longer, it can just retry with progressively smaller lock deadline
> until it's time to do something else.

The busy handler is used if you have set a timeout to obtain a lock.  If you 
try to do

BEGIN IMMEDIATE

on a database that is not in WAL mode, then in order to obtain a lock there 
must be no active readers or writers.  If there are, the lock cannot be 
obtained an SQLITE_BUSY is returned and you try again later.  If you set a 
timeout with PRAMGA timeout, that specifies the amount of time to wait for a 
lock before returning SQLITE_BUSY.  The busy handler is used to "handle" the 
"busy" state (that is, rather than return SQLITE_BUSY to the caller, your busy 
handler is called.  You do something (like wait a while using sleep, then 
return, perhaps displaying flying ball bearings or other annoyances to indicate 
to the user that the program is waiting).  Lather rinse repeat until the 
specified timeout expires at which time SQLITE_BUSY is returned to the caller.

If you do not specify your own custom busy handler (to display flying ball 
bearings, etc, or do your own exponential sleeping, etc) then the default 
busy_handler is used.  The default busy handler does its own exponential 
backoff algorithm but does not display whirling ball bearings or anything else, 
it just sleeps silently.
 
> In my application it is more ideal if commands in the command table are
> known to the readers immediately but without the overhead of rapid polling
> by the readers.  Setting a suggested wait time and then waiting again if
> SQLITE_BUSY comes back early and there's nothing else to do would be just
> fine.

Again, you have specified "RAPID POLLING".  Can you please define what you mean 
by "RAPID POLLING"?  To me, "RAPID POLLING" means using the immensely stupid 
spinlock type implementation such as:

open connection
while (nothing to do)
   query database for something to do
do what needs doing

If you need things to go "RAPIDLY" then you need to use some IPC notification 
mechanism.

Please state in explicit terms your definition of "RAPIDLY".   Using terms 
without defining them makes it difficult for anyone to understand what you are 
talking about.  One persons "RAPIDLY" may mean "after the MoC is filled out and 
endorsed, operations windows are arranged, and the board operator gives the 
final go ahead -- in other words, two weeks to six months".  Another person's 
definition of "RAPIDLY" may be "within 250 milliseconds".  Please specify what 
you definition of "RAPIDLY" is, giving minimum and maximum bounds in common 
time units.

If RAPIDLY means "in less than one second, 33% of the time, within two seconds 
75% of the time, and no more than three seconds ever" then you are talking 
about rather SLOW POLLING. 

open connection
while (nothing to do)
   sleep 1 second
   query database for something to do
do what needs doing

This type of SLOW POLLING will use about 1% of the available CPU on a 4.77 Mhz 
8086 CPU while in the polling loop.  This means that you can have about 100 
such processes polling at the same time on a 4.77 Mhz 8086 equipped computer.  
On a modern CPU with a couple of cores and about 2Ghz per code, you should be 
able to run many tens of thousands of such processes simultaneously and STILL 
have resources left over.  (though the OS scheduler will probably consume more 
CPU than the polling loops do, and the process table and OS resources are 
likely to become exhausted long before you hit any reasonable limit on the 
resource limits for the simultaneously polling processes themselves.)

> [Regarding the suggestion of synchronizing the readers with another
> concurrency object, I could do that.  But if the database connection
> itself
> can coordinate the same thing, the reader program is a lot simpler and
> more
> portable.]
 
> I'm not sure I understand Simon's comments how using EXCLUSIVE TRANSACTION
> writer isolation is subverting something subject to change if that
> isolation level is desired.  Reader would still be querying the command
> table but in committed read mode with EXCLUSIVE isolation.  Why would this
> work differently on different systems?  From
> http://www.sqlite.org/lockingv3.html
 
> "EXCLUSIVE An EXCLUSIVE lock is needed in order to write to the
> database file. Only one EXCLUSIVE lock is allowed on the file and no other
> locks of any kind are allowed to coexist with an EXCLUSIVE lock. In order
> to maximize concurrency, SQLite works to minimize the amount of time that
> EXCLUSIVE locks are held."
 
> "If the same database is being read and written using two different
>