[PHP::PDO]SQLITE Drop Columns function

//CODE DEMO
$dbh = new PDO('sqlite:ClientFB.sqlite3');
$st = $dbh->query("PRAGMA table_info(ap_form_elements)");
var_dump($st->fetchAll(PDO::FETCH_COLUMN,1));

sqlite_drop_table_columns('ap_form_elements',array('element_position','element_default_value'),$dbh);

$st = $dbh->query("PRAGMA table_info(ap_form_elements)");
var_dump($st->fetchAll(PDO::FETCH_COLUMN,1));

/**
* 實做一個刪除資料表欄位 function
* @prama $table STRING
* @prama $columns ARRAY
*/
function sqlite_drop_table_columns($table,$columns,$dbh){
//取得所有欄位
$st = $dbh->query("PRAGMA table_info(ap_form_elements)");
if($st){
$fields = $st->fetchAll(PDO::FETCH_COLUMN,1);
//去除不要的欄位
$fls = array();
foreach($fields as $f){
if(!in_array($f,$columns)){
$fls[] = $f;
}
}
}else{
return FALSE;
}
unset($st);
//組合SQL語法
$field_list = implode(',',$fls);
$SQL = < <<QUERY
CREATE TEMPORARY TABLE {$table}_bak({$field_list});
INSERT INTO {$table}_bak SELECT {$field_list} FROM {$table};
DROP TABLE {$table};
CREATE TABLE {$table}({$field_list});
INSERT INTO {$table} SELECT {$field_list} FROM {$table}_bak;
DROP TABLE {$table}_bak;
QUERY;
return $dbh->exec($SQL));
}

This is a sample show case,It will break the table attribut (ex: AUTOINCREMENT,PRIMARY KEY….)

AND this is more powerful version

/**
* implement a drop columns sqlite function
* @param $table STRING
* @param $columns ARRAY
* @param $dbh PDO
* @retrun FALSE|INT
*/
function sqlite_drop_table_columns($table,$columns,$dbh){
	//取得所有欄位
	$st = sqlite_create_analytic($table,$dbh);
	if($st){
		$fields = array_keys($st);
		//去除不要的欄位
		$fls = array();
		foreach($st as $f => $v){
			if(!in_array($f,$columns)){
				$fls[$f] = $v;
			}
		}
	}else{
		return FALSE;
	}
	unset($st);
	//組合SQL語法
	$field_list = implode(',',array_keys($fls));
	$field_list_sql = implode(',',array_values($fls));
	$SQL = <<<QUERY
CREATE TEMPORARY TABLE {$table}_bak({$field_list_sql});
INSERT INTO {$table}_bak SELECT {$field_list} FROM {$table};
DROP TABLE {$table};
CREATE TABLE {$table}({$field_list_sql});
INSERT INTO {$table} SELECT {$field_list} FROM {$table}_bak;
DROP TABLE {$table}_bak;
QUERY;

	return $dbh->exec($SQL);
}
/*
* @param $table STRING
* @param $dbh PDO
* @return FALSE|ARRAY
*/
function sqlite_create_analytic($table,$dbh){
	$sql = "SELECT sql FROM sqlite_master WHERE type='table' and name='{$table}'";
	$st = $dbh->query($sql);
	if($st){
		$sql = $st->fetch(PDO::FETCH_COLUMN,1);
	}else{
		return FALSE;
	}

	if(preg_match('/\(([\s\S\n]+)\)/si',$sql,$mt)){
		$col_sqls = explode(',',$mt[1]);
		$columns = array();
		$st = $dbh->query("PRAGMA table_info({$table})");
		if($st){
			$cols = $st->fetchAll(PDO::FETCH_COLUMN,1);
			foreach($cols as $col){
				foreach($col_sqls as $col_sql){
					$test = str_replace(array('"',"'"),'',$col_sql);
					if(FALSE === strstr($test,"{$col} ")){
						continue;
					}else{
						$columns[$col] = trim($col_sql);
					}
				}
			}
			if(empty($columns)){
				return false;
			}else{
				return $columns;
			}
		}else{
			return false;
		}
	}else{
		return FALSE;
	}
}