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

友情链接