Quantcast

table-naming-expression impact on sqlite3_prepare

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

table-naming-expression impact on sqlite3_prepare

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: table-naming-expression impact on sqlite3_prepare

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: table-naming-expression impact on sqlite3_prepare

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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: table-naming-expression impact on sqlite3_prepare

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: table-naming-expression impact on sqlite3_prepare

Kees Nuyt
In reply to this post by petern
On Sun, 26 Mar 2017 15:34:22 -0700, petern <[hidden email]>
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: table-naming-expression impact on sqlite3_prepare

Simon Slavin-3

On 27 Mar 2017, at 1:52am, Kees Nuyt <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...