Metadata for columns in SQLite v2.8 (PHP5)

Question

How can I get metadata / constraints (primary key and "null allowed" in particular) for each column in a SQLite v2.8 table using PHP5 (like mysql_fetch_field for MySql)?

sqlite_fetch_column_types (OO: $db->fetchColumnTypes) only gets column name and datatype:
http://dk.php.net/manual/en/function.sqlite-fetch-column-types.php

SQLITE_MASTER has the info - but not as a variable. Example:
SELECT name FROM SQLITE_MASTER;

... SQLITE_MASTER only outputs an array with this structure (v2.8):

[type] => table
[name] => foo
[tbl_name] => foo
[rootpage] => 3
[sql] => CREATE TABLE foo ( id INTEGER PRIMARY KEY, name CHAR(255) )

(And what is "rootpage"?)

Answer 1

I had to do this to implement a describeTable() method for SQLite when I worked on Zend Framework. SQLite does not have any way to get very detailed information about metadata.

What I did was to run the following SQLite query:

PRAGMA tableinfo( <tablename> );

See http://www.sqlite.org/pragma.html, under the heading "Pragmas to query the database schema."

The result set returned from this query has columns for:

• column position (integer)
• column name (string)
• data type (string)
• nullable (0) vs. not null (1)
• default value (string)
• primary key (1)

You can view the PHP code by downloading Zend Framework and looking in class Zend_Db_Adapter_Pdo_Sqlite, method describeTable(). You can also view the source online via the code repository browser at framework.zend.com (although it is frequently not working).

FWIW, this is not like mysql_fetch_field(). That method returns metadata about a result set, which may not be the same thing as metadata about a table.

Answer 2

Example code snippet that worked for me ... to illustrate Bill Karwin's solution (it's not an attempt to answer my own question!)

$db = new SQLiteDatabase("db.sqlite2");  
$rs = $db->arrayQuery("PRAGMA table_info( 'my_table' );");
print_r($rs);