Most efficient way of pulling data out of a string

I need to find out the primary key of a sqlite database.

Given a string like:

CREATE TABLE cia (name PRIMARY KEY, population INTEG开发者_StackOverflow社区ER)

or:

CREATE TABLE casting(movieid INTEGER, actorid INTEGER, PRIMARY KEY (movieid, actorid))

What would the most effective way of getting an array of primary keys be using php?

"I need to find out the primary key of a sqlite database." - if you can use the database you might avoid parsing the sql string:
The result of PRAGMA TABLE_INFO() has a field pk which is set to 1 if the field is part of the PRIMARY key of the table.

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$pdo->exec('CREATE TABLE cia (name PRIMARY KEY, population INTEGER)');
$pdo->exec('CREATE TABLE casting(movieid INTEGER, actorid INTEGER, PRIMARY KEY (movieid, actorid))');

showPrimary($pdo, 'cia');
showPrimary($pdo, 'casting');

function showPrimary($pdo, $tablename) {
  // only an example. I don't care about injections here
  echo "primary key for '$tablename'\n";
  foreach($pdo->query("PRAGMA TABLE_INFO($tablename)", PDO::FETCH_ASSOC) as $row) {
    if ( $row['pk'] ) {
      printf("  %s %s PRIMARY\n", $row['name'], $row['type']);
    }
  }
}

prints

primary key for 'cia'
  name  PRIMARY
primary key for 'casting'
  movieid INTEGER PRIMARY
  actorid INTEGER PRIMARY

Something like this should do it:

<?php
$db = new SQLiteDatabase('dbfilename')) 
$q = @$db->query('SHOW KEYS FROM cia WHERE Key_name = "PRIMARY"');
$result = $q->fetch();
var_dump(sqlite_fetch_array($result, SQLITE_BOTH))
?>

Update: if SHOW KEYS is not supported, then you can get the infomration via SELECT statments and a bit of work. Here is a PHP function defined to provide all the table index info you could ever want:

/**
 * return the list of field in $table
 * @param string $table name of the sql table to work on
 * @param bool $extended_info if true will return the result of a show field query in a query_to_array fashion
 *                           (indexed by fieldname instead of int if false)
 * @return array
 */
function list_table_fields($table,$extended_info=FALSE){
# Try the simple method
if( (! $extended_info) && $res = $this->query_to_array("SELECT * FROM $table LIMIT 0,1")){
  return array_keys($res[0]);
}else{ # There 's no row in this table so we try an alternate method or we want extended infos            
  if(! $fields = $this->query_to_array("SELECT sql FROM sqlite_master WHERE type='table' AND name ='$table'") )
    return FALSE;
  # get fields from the create query
  $flds_str = $fields[0]['sql'];
  $flds_str = substr($flds_str,strpos($flds_str,'('));
  $type = "((?:[a-z]+)\s*(?:\(\s*\d+\s*(?:,\s*\d+\s*)?\))?)?\s*";
  $default = '(?:DEFAULT\s+((["\']).*?(?<!\\\\)\\4|[^\s,]+))?\s*';
  if( preg_match_all('/(\w+)\s+'.$type.$default.'[^,]*(,|\))/i',$flds_str,$m,PREG_SET_ORDER) ){
    $key  = "PRIMARY|UNIQUE|CHECK";
    $null = 'NOT\s*NULL';
    $Extra = 'AUTOINCREMENT';
    $default = 'DEFAULT\s+((["\'])(.*?)(?<!\\\\)\\2|\S+)';
    foreach($m as $v){
      list($field,$name,$type,$default) = $v;
      # print_r($field);
      if(!$extended_info){
        $res[] = $name;
        continue;
      }
      $res[$name] = array('Field'=>$name,'Type'=>$type,'Null'=>'YES','Key'=>'','Default'=>$default,'Extra'=>'');
      if( preg_match("!($key)!i",$field,$n))
        $res[$name]['Key'] = $n[1];
      if( preg_match("!($Extra)!i",$field,$n))
        $res[$name]['Extra'] = $n[1];
      if( preg_match('!(NO)T\s+NULL!i',$field,$n))
        $res[$name]['Null'] = $n[1];
    }
    return $res;
  }
  return FALSE;
 }
} 

Source