[whatwg] SQL API + access to tables

[Ian Hickson]

On Fri, 19 Oct 2007, Brady Eidson wrote:
>
> There is no standard way in SQL that I know of to get the list of tables 
> in a database.
> 
> In SQLite you can enumerate tables out of sqlite_master, but that should 
> not be encouraged.
> 
> What are people's thoughts about adding this to the API?

I'd rather keep the API as thin as possible.


On Fri, 19 Oct 2007, Scott Hess wrote:
> 
> This seems way beyond where this spec should go.  If SQLite doesn't have 
> a way to enumerate tables which can be encouraged, a way should be added 
> to SQLite (I've considered building some introspection virtual tables 
> for this kind of thing).

If something becomes the de-facto standard, we can add it to the spec's 
SQL dialect when we come around to defining it.


On Fri, 19 Oct 2007, Brady Eidson wrote:
> 
> Like I said, you can enumerate tables out of SQLite.  I claim "it should 
> not be encouraged" because it involves very SQLite specific mechanisms 
> that are not at all portable to any SQL implementation other than 
> SQLite.  In fact, we plan to disallow access to the sqlite_master table 
> because there's no reason the client *should* be able to access such an 
> implementation specific field that has nothing to do with "client side 
> database storage"
> 
> Nowhere in the spec have we said "the user agent must use SQLite" but if 
> we intend web developers to use SQLite specific features to get at a 
> "list of tables", then we're encouraging incompatibilities with 
> non-SQLite back ends. Which is something a new spec should not do!

Well, eventually we're going to have to define the exact SQL dialect we 
require, and that's going to constrain what backends implementors can use 
anyway.


On Sat, 20 Oct 2007, Aaron Boodman wrote:
> 
> Not really my business, but this seems like a bad idea. Applications 
> built on top of WebKit with SQLite are already tightly coupled to the 
> SQLite dialect. If such applications wanted to port to another database 
> engine, accessing sqlite_master would be the least of their problems. 
> SQLite's datatypes, for example, are different than those in other 
> database implementations.
> 
> Being able to introspect the database is a valuable feature. Sure it is 
> not best-practice, but in some cases, such as tools, it can be useful.

Indeed.


On Fri, 19 Oct 2007, Timothy Hatcher wrote:
>
> It isn't that useful to get the table list, only for introspection would 
> you need it. Most users of the API will know the schema of the database, 
> and know the table names they need. It would be useful if the SQL 
> back-end did not support CREATE TABLE IF NOT EXISTS. But users can just 
> blinding try to create a table and look for any errors about it already 
> existing in that case.
> 
> Regarding introspection, it would have helped us write the Web Inspector 
> feature. We ended up making a special call to get the table names.

I don't think we should necessarily optimise the API for that case! :-)


On Sat, 20 Oct 2007, Leons Petrazickis wrote:
>
> You need the ability to list tables for integration with tools such as 
> Firebug. Apple is adding table introspection to Safari's equivalent of 
> Firebug. How should Firebug or some other third-party tool achieve it?

Those are UA-specific, and can use UA-specific extensions.


On 10/20/07, Køi¹tof ®elechovski <giecrilj at stegny.2a.pl> wrote:
>
> To achieve what?  To make spying and breaking easier?  The developer 
> should know the structure of the database in advance; making such 
> guesswork in production code on the end user's machine is at least 
> inappropriate. Also, if we agreed to that, this would be a workaround 
> for a supposed deficiency of SQL.  But the SQL committee does not 
> recognize it as a deficiency; otherwise it would have been fixed by now.

On Mon, 22 Oct 2007, Michaeljohn Clement wrote:
> 
> No, this is actually fixed for 15 years now.  The SQL spec directly 
> addresses this need through the information schema, since SQL92.
> 
> Every moderately complete SQL implementation I'm aware of also meets 
> this need, either through the means specified by the standard, or by 
> incompatible extensions, as is the general custom.

Right. Ideally, SQLite and other backends used for this feature would 
follow the standard. That would be the best solution from the HTML5 spec's 
point of view.

-- 
Ian Hickson               U+1047E                )\._.,--....,'``.    fL
http://ln.hixie.ch/       U+263A                /,   _.. \   _\  ;`._ ,.
Things that are impossible just take longer.   `._.-(,_..'--(,_..'`-.;.'