代码之家 › 专栏 › 技术社区 › jcuenod

从字符串中提取数据的最有效方法

string php

jcuenod · 技术社区 · 15 年前

我需要找到一个sqlite数据库的主键。

给出如下字符串:

CREATE TABLE cia (name PRIMARY KEY, population INTEGER)

或:

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

获取主键数组的最有效方法是使用PHP?

2 回复 | 直到 15 年前

VolkerK 15 年前

“我需要找出sqlite数据库的主键。”—如果可以使用该数据库,则可能会避免解析SQL字符串:
结果 PRAGMA TABLE_INFO() 有一个领域 pk 如果字段是表的主键的一部分,则设置为1。

$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']);
    }
  }
}

印刷品

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

AlexanderPico 15 年前

像这样的事情应该可以做到:

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

更新:如果不支持显示键,那么您可以通过选择语句和一些工作来获取信息。下面是一个php函数,它定义为提供您可能需要的所有表索引信息:

/**
 * 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