Most efficient way of pulling data out of a string

Question

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

Given a string like:

CREATE TABLE cia (name PRIMARY KEY, population INTEGER)

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?

Answer 1

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

Answer 2

"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