views:

324

answers:

2

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"?)

A: 

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.

Bill Karwin
Thanks - great solution.
Kristoffer Bohmann
+1  A: 

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);
Kristoffer Bohmann