Re: [sqlite] pragmas in subselects?

[Petite Abeille]

On Jan 29, 2014, at 9:58 PM, big stone  wrote:

> (killing two birds with one stone)

No. One bird only.

Enhancing ‘alter table’ is another kettle of fish altogether.

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

Re: [sqlite] pragmas in subselects?

[big stone]

Would the implementation of a subset of  "Information schema" also allow
the "Alter table" function to work in more (SQL) standard situations ?

(killing two birds with one stone)

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

Re: [sqlite] pragmas in subselects?

[Stephan Beal]

On Sun, Jan 26, 2014 at 9:45 PM, Jay Kreibich  wrote:

>
> Chapter 10 of Using SQLite covers virtual tables.  One of the examples
> given shows how to wrap a PRAGMA statement, so it can be used as system
> catalog and used in normal SELECT statements.  It would be pretty easy to
> expand the given example to cover almost any SQL statement (including any
> PRAGMA).
>
> Even if you don't have a copy of the book, you can download the example
> code off the product page:
>

i've got the book, so many thanks for that tip - i'll take a look.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragmas in subselects?

[Clemens Ladisch]

big stone wrote:
> There is a non-logicality of having "pragma table_info(my_table)" :
> - answering like a select,
> - but being not usable as a "select", nor queriable by a "select".

SQLite's PRAGMA implementations do not plug into the internal query
mechanism used by SELECT, but hardcode a VDBE program that directly
generates the output as seen by sqlite3_step/column_xxx.  There is no
built-in mechanism that would allow to use the output of a VDBE program
directly as input for other queries.  (But it is possible to write
a virtual table that does this.)


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

Re: [sqlite] pragmas in subselects?

[Simon Slavin]

On 26 Jan 2014, at 10:32pm, Petite Abeille  wrote:

> What SQLite would really benefit from is a proper, consistent, queryable data 
> dictionary such as the the standard information schema:
> 
> http://en.wikipedia.org/wiki/Information_schema

I would like that for in SQLite4.  Something close to SQL-92 without messing up 
the tenets of SQLite.  For those interested, it's section 21 page 533 of



but that's a horrible description and very hard to understand.  It if turns out 
to be useful for the SQLite community I can update an simple one with examples 
I've got lying about somewhere.

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

Re: [sqlite] pragmas in subselects?

[Petite Abeille]

On Jan 26, 2014, at 11:19 PM, big stone  wrote:

> ==> Is it the reason ?

Well, that pragmas are not directly queryable from SQL just add insult to 
injury. 

What SQLite would really benefit from is a proper, consistent, queryable data 
dictionary such as the the standard information schema:

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

Re: [sqlite] pragmas in subselects?

[big stone]

There is a non-logicality of having "pragma table_info(my_table)" :
- answering like a select,
- but being not usable as a "select", nor queriable by a "select".

Other databases seem more logical on this practical matter.

Multi-motor tools, like dbeaver, have currently much less good support of
"database diagram" when dealing with SQLite.
==> Is it the reason ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragmas in subselects?

[Jay Kreibich]

Chapter 10 of Using SQLite covers virtual tables.  One of the examples given 
shows how to wrap a PRAGMA statement, so it can be used as system catalog and 
used in normal SELECT statements.  It would be pretty easy to expand the given 
example to cover almost any SQL statement (including any PRAGMA).

Even if you don't have a copy of the book, you can download the example code 
off the product page:
http://shop.oreilly.com/product/9780596521196.do
The example code download link is on the right side of the page.

Somewhere I've got a virtual table that will wrap any SQL statement, but I'm 
not sure where it is.  Just modify the book's example code to take an SQL 
statement as a table creation parameter and you should be able to create 
something similar.

 -j


On Jan 26, 2014, at 11:01 AM, Petite Abeille  wrote:

> 
> On Jan 26, 2014, at 5:09 PM, Stephan Beal  wrote:
> 
>> Is this possible?
> 
> Sadly, no. Much of a PITA.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

--  
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing 
it to the wrong people has the tendency to make them feel uncomfortable." -- 
Angela Johnson




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

Re: [sqlite] pragmas in subselects?

[Luuk]

On 26-01-2014 17:09, Stephan Beal wrote:

Hi, all,

is there a syntactical construct which will allow me to use a pragma in a
subselect? e.g. i'm trying to do...

sqlite> pragma table_info(vfile);
cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|0||1
1|vid|INTEGER|0||0
...

sqlite> select name from (pragma table_info(vfile));
Error: near "(": syntax error
sqlite> select name from pragma table_info(vfile);
Error: near "(": syntax error
sqlite> create view v as pragma table_info(vfile);
Error: near "pragma": syntax error
sqlite> create view v as select (pragma table_info(vfile));
Error: near "table_info": syntax error
sqlite> create view v as select pragma table_info(vfile);
Error: near "(": syntax error

Is this possible?




http://stackoverflow.com/questions/685206/sqlite-how-to-get-a-list-of-column-names

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

Re: [sqlite] pragmas in subselects?

[Petite Abeille]

On Jan 26, 2014, at 5:09 PM, Stephan Beal  wrote:

> Is this possible?

Sadly, no. Much of a PITA.

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

[sqlite] pragmas in subselects?

[Stephan Beal]

Hi, all,

is there a syntactical construct which will allow me to use a pragma in a
subselect? e.g. i'm trying to do...

sqlite> pragma table_info(vfile);
cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|0||1
1|vid|INTEGER|0||0
...

sqlite> select name from (pragma table_info(vfile));
Error: near "(": syntax error
sqlite> select name from pragma table_info(vfile);
Error: near "(": syntax error
sqlite> create view v as pragma table_info(vfile);
Error: near "pragma": syntax error
sqlite> create view v as select (pragma table_info(vfile));
Error: near "table_info": syntax error
sqlite> create view v as select pragma table_info(vfile);
Error: near "(": syntax error

Is this possible?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users