Re: [sqlite] pragma table_info(database.table) not supported

2013-03-25 Thread Petite Abeille 

On Mar 21, 2013, at 6:41 PM, Peter Haworth  wrote:

> I found the code in the two attached files (are they allowed on this list?)
> on the web a while back.  The claim was that it created an information
> schema database from an sqlite db.  I know nothing about Lua but I managed
> to get as far as creating the schema. Unfortunately, the url I got it from
> no longer has the code. but I'd be interested to know if it works.

You mean these?

http://alt.textdrive.com/svn/altdev/IMDB/Info.ddl
http://alt.textdrive.com/svn/altdev/IMDB/Info.lua

Yes, they work, as far as capturing the information schema goes.

Such queryable data dictionary  is not just to be cute, but rather to perform 
real work by easily introspecting the database.

Specifically, in this case, the data dictionary is use to automatically drive 
the ETL (Extract, Transform, Load) process to load the IMDB database [1]:

http://alt.textdrive.com/svn/altdev/IMDB/ETL.ddl
http://alt.textdrive.com/svn/altdev/IMDB/ETL.lua

The entire ETL is driven by introspecting the IMDB schema, courtesy of the data 
dictionary:

http://alt.textdrive.com/svn/altdev/IMDB/IMDB.ddl
http://alt.textdrive.com/svn/altdev/IMDB/IMDB.lua

So, for example, given the biographies.list.gz IMDB file, the ETL will 
automatically populate its corresponding person_biography table by:

(1) Extracting the raw biography data into an auto-generated staging table
(2) Transform all the appropriate foreign keys by populating their reference 
tables as needed
(3) Load the final person_biography table by automatically resolving all its 
references

Here is a typical run log:

2013-02-16 16:15:59 [IMDB.Process] Inflating 
/Volumes/Queens/IMDB/imdb/biographies.list.gz
2013-02-16 16:16:14 [IMDB.Process] Converting 
/Volumes/Queens/IMDB/imdb/biographies.list
2013-02-16 16:16:35 [IMDB.Process] Processing 
/Volumes/Queens/IMDB/imdb/biographies.list.txt
2013-02-16 16:16:35 [ETL.Extract] Inserting into person_biography_extract
2013-02-16 16:21:11 [ETL.Extract] Inserted 2,585,624 rows into 
person_biography_extract
2013-02-16 16:21:11 [ETL.Transform] Inserting into biography
2013-02-16 16:21:22 [ETL.Transform] Inserted 21 rows into biography
2013-02-16 16:21:22 [ETL.Description] Updating biography
2013-02-16 16:21:22 [ETL.Description] Updated 21 rows in biography
2013-02-16 16:21:22 [ETL.Transform] Inserting into person
2013-02-16 16:21:34 [ETL.Transform] Inserted 540,346 rows into person
2013-02-16 16:21:34 [ETL.Load] Inserting into person_biography
2013-02-16 16:22:23 [ETL.Load] Inserted 2,585,624 rows into person_biography

And that's that. All automated and (meta)data driven. Courtesy of a proper data 
dictionary. 

If only SQLite could provide such information schema out-of-the-box, now that 
would be value added :))


[1] http://www.imdb.com/interfaces

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

Re: [sqlite] pragma table_info(database.table) not supported

2013-03-22 Thread Simon Slavin 

On 21 Mar 2013, at 5:41pm, Peter Haworth  wrote:

> I found the code in the two attached files (are they allowed on this list?)

You can't attach things to messages to this list.  Thanks for finding the code. 
 I think it's interesting as a talking point but we don't actually need to see 
the code.  If the developer team wants this to work they can make up their own 
code pretty easily and it won't be in Lua.

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

Re: [sqlite] pragma table_info(database.table) not supported

2013-03-22 Thread Peter Haworth 
I found the code in the two attached files (are they allowed on this list?)
on the web a while back.  The claim was that it created an information
schema database from an sqlite db.  I know nothing about Lua but I managed
to get as far as creating the schema. Unfortunately, the url I got it from
no longer has the code. but I'd be interested to know if it works.

On Thu, Mar 21, 2013 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote:

> Message: 16
> Date: Wed, 20 Mar 2013 22:15:39 +
> From: Simon Slavin <slav...@bigfraud.org>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: Re: [sqlite] pragma table_info(database.table) not supported
> Message-ID: <c9f4c996-f601-4080-a23c-7dc5c58e7...@bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
>
> On 20 Mar 2013, at 6:52pm, Petite Abeille <petite.abei...@gmail.com>
> wrote:
>
> > On Mar 20, 2013, at 6:53 PM, Jay A. Kreibich <j...@kreibi.ch> wrote:
> >
> >> If there is any change I'd like to see, it is that all the PRAGMAs
> >> that return tabular data should really be system catalog tables.
> >
> > Triple hurray for that! SQLite deserves a proper data dictionary, no
> question asked.
> >
> > What about adopting/adapting the information schema and call it a day?
> >
> > http://en.wikipedia.org/wiki/Information_schema
>
> Me three.  I feel that this would be a better way to get details about the
> currently connected database(s).  It should replace all the PRAGMAs which
> return details about tables, indexes, foreign keys, etc..  I do remember
> someone posted code here a year or two ago which executed lots of PRAGMAs
> and used the results to build such tables.  It may have been this, or
> related to it:
>
> <http://www.sqlite.org/cvstrac/wiki?p=InformationSchema>
>
> These tables could either be updated on any schema change, or could be
> left uncalculated until a query on them is executed, or could be
> created/updated on execution of a special command like ANALYSE.
>



Pete
lcSQL Software <http://www.lcsql.com>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma table_info(database.table) not supported

2013-03-20 Thread Simon Slavin 

On 20 Mar 2013, at 6:52pm, Petite Abeille  wrote:

> On Mar 20, 2013, at 6:53 PM, Jay A. Kreibich  wrote:
> 
>> If there is any change I'd like to see, it is that all the PRAGMAs
>> that return tabular data should really be system catalog tables.
> 
> Triple hurray for that! SQLite deserves a proper data dictionary, no question 
> asked.
> 
> What about adopting/adapting the information schema and call it a day?
> 
> http://en.wikipedia.org/wiki/Information_schema

Me three.  I feel that this would be a better way to get details about the 
currently connected database(s).  It should replace all the PRAGMAs which 
return details about tables, indexes, foreign keys, etc..  I do remember 
someone posted code here a year or two ago which executed lots of PRAGMAs and 
used the results to build such tables.  It may have been this, or related to it:



These tables could either be updated on any schema change, or could be left 
uncalculated until a query on them is executed, or could be created/updated on 
execution of a special command like ANALYSE.


On 20 Mar 2013, at 6:30pm, Jay A. Kreibich  wrote:

>  Somewhere I have a VT that will build a table out of any SQL
>  statement.  That sounds kind of dumb, since that's either redundant,
>  or something you could do with a VIEW, but it can also be used for
>  any PRAGMA.

You mean something that would take apart a SELECT statement and identify the 
JOINS, the WHERE clause, etc ?  That in interesting idea I've never seen 
before.  It would be a sort of precursor to EXPLAIN QUERY PLAN.

Simon.

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

Re: [sqlite] pragma table_info(database.table) not supported

2013-03-20 Thread Petite Abeille 

On Mar 20, 2013, at 6:53 PM, Jay A. Kreibich  wrote:

>  If there is any change I'd like to see, it is that all the PRAGMAs
>  that return tabular data should really be system catalog tables.

Triple hurray for that! SQLite deserves a proper data dictionary, no question 
asked.

What about adopting/adapting the information schema and call it a day?

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] pragma table_info(database.table) not supported

2013-03-20 Thread Jay A. Kreibich 
On Wed, Mar 20, 2013 at 07:00:29PM +0100, Stephan Beal scratched on the wall:
> On Wed, Mar 20, 2013 at 6:53 PM, Jay A. Kreibich  wrote:
> 
> >   That way I can use WHERE on them.  In the past I've used virtual
> >   tables to wrap the PRAGMAs into something that looked and acted more
> >   like a real table.
> 
> can you give us an example of how that is done?


  Chapter 10:  http://shop.oreilly.com/product/9780596521196.do

  Also see the "Download Example Code" link on that page.
  ".../ch10/dblist.c" shows how to wrap "PRAGMA database_list" in a VT.
  It should be pretty easy to modify this so that it would work with
  any PRAGMA.

  There is nothing fancy going on here... the virtual table still calls
  the PRAGMA and sifts through the results.  It just gets abstracted a bit.


  Somewhere I have a VT that will build a table out of any SQL
  statement.  That sounds kind of dumb, since that's either redundant,
  or something you could do with a VIEW, but it can also be used for
  any PRAGMA.  No idea where that code is off the top of my head
  however.

   -j

-- 
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] pragma table_info(database.table) not supported

2013-03-20 Thread Stephan Beal 
On Wed, Mar 20, 2013 at 6:53 PM, Jay A. Kreibich  wrote:

>   That way I can use WHERE on them.  In the past I've used virtual
>   tables to wrap the PRAGMAs into something that looked and acted more
>   like a real table.
>

Hi, Jay,

can you give us an example of how that is done?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma table_info(database.table) not supported

2013-03-20 Thread Jay A. Kreibich 
On Wed, Mar 20, 2013 at 05:57:12PM +0100, Staffan Tylen scratched on the wall:
> >PRAGMA table_info(database.tablename)
> >
> > Any chance that SQLite4 might change this, or perhaps accept both forms ?

> In addition, how about a pragma to get the tables in a database? We
> currently have pragma(database_list), pragma(index_list), and
> pragma(table_info), but no pragma(table_list) -  a SELECT against
> sqlite_master is necessary to get hold of the table names. IMHO it's not
> what one would expect.

  I would expect exactly the opposite.

  Part of the core philosophy of Relational systems is that all
  information, including self-inspection information, is available via
  tables-- specifically via the SELECT command.  These are the "system
  catalogs" found in most DBs including Oracle, MySQL, Postgres, and
  many others.

  If there is any change I'd like to see, it is that all the PRAGMAs
  that return tabular data should really be system catalog tables.
  That way I can use WHERE on them.  In the past I've used virtual
  tables to wrap the PRAGMAs into something that looked and acted more
  like a real table.
 
   -j

-- 
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] pragma table_info(database.table) not supported

2013-03-20 Thread Staffan Tylen 
>PRAGMA table_info(database.tablename)
>
> Any chance that SQLite4 might change this, or perhaps accept both forms ?
>
>
In addition, how about a pragma to get the tables in a database? We
currently have pragma(database_list), pragma(index_list), and
pragma(table_info), but no pragma(table_list) -  a SELECT against
sqlite_master is necessary to get hold of the table names. IMHO it's not
what one would expect.

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

Re: [sqlite] pragma table_info(database.table) not supported

2013-03-20 Thread Simon Slavin 

On 20 Mar 2013, at 11:03am, Richard Hipp  wrote:

> The syntax for PRAGMA is:
> 
>PRAGMA database.table_info(tablename);

Out of interest, is there some internal reason for this non-standard form ?  I 
can understand it if it makes some internal part of SQLite easier to write, but 
my instinctive form for these PRAGMAs is the same as the OPs:

   PRAGMA table_info(database.tablename)

Any chance that SQLite4 might change this, or perhaps accept both forms ?

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

Re: [sqlite] pragma table_info(database.table) not supported

2013-03-20 Thread Staffan Tylen 
Ooops! Thank :)


On Wed, Mar 20, 2013 at 12:03 PM, Richard Hipp  wrote:

> On Wed, Mar 20, 2013 at 6:54 AM, Staffan Tylen  >wrote:
>
> > I've just found out that adding a database name as a table prefix is not
> > supported by the pragma table_info function, giving a syntax error. The
> > documentation of the ATTACH DATABASE commands says:
> >
> > Tables in an attached database can be referred to using the syntax *
> > database-name.table-name*.
> >
> > This doesn't seem to be true for the table_info pragma. Can this by any
> > chance be added to the pragma, or is there an alternative way to retrieve
> > the information without parsing the information in the sqlite_master
> table?
> >
>
> The syntax for PRAGMA is:
>
> PRAGMA database.table_info(tablename);
>
>
> --
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma table_info(database.table) not supported

2013-03-20 Thread Richard Hipp 
On Wed, Mar 20, 2013 at 6:54 AM, Staffan Tylen wrote:

> I've just found out that adding a database name as a table prefix is not
> supported by the pragma table_info function, giving a syntax error. The
> documentation of the ATTACH DATABASE commands says:
>
> Tables in an attached database can be referred to using the syntax *
> database-name.table-name*.
>
> This doesn't seem to be true for the table_info pragma. Can this by any
> chance be added to the pragma, or is there an alternative way to retrieve
> the information without parsing the information in the sqlite_master table?
>

The syntax for PRAGMA is:

PRAGMA database.table_info(tablename);


-- 
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] pragma table_info(database.table) not supported

2013-03-20 Thread Clemens Ladisch 
Staffan Tylen wrote:
> I've just found out that adding a database name as a table prefix is not
> supported by the pragma table_info function, giving a syntax error.

:

PRAGMA mydatabase.table_info(mytable);


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