function execute() {
try {
$testLine = [
‘aa' => array(
'host' => '',
'port' => '1433',
'username' => '',
'password' => ',
'dbname' => '',
'charset'=>'UTF-8',
),
‘bb' => array(
'host' => '',
'port' => '1433',
'username' => '',
'password' => '',
'dbname' => '',
'charset'=>'UTF-8',
),
];
$testLocal = [
‘aa' => array(
'host' => '',
'port' => '1433',
'username' => '',
'password' => '',
'dbname' => '',
'charset'=>'UTF-8',
),
'bb' => array(
'host' => '',
'port' => '1433',
'username' => '',
'password' => '',
'dbname' => '',
'charset'=>'UTF-8'
)
];
$rowLocal = $this->dataStat($testLocal);
$rowLine = $this->dataStat($testLine);
// $rowLocal = $this->dataStat($testLine);
// $rowLine = $this->dataStat($testLocal);
foreach($rowLocal as $key=>$val){
$localData = $val;
$lineData = $rowLine[$key];
foreach($localData as $kk=>$vv){
$localField = json_decode($vv,true);
if(!empty($lineData[$kk])){
$lineField = json_decode($lineData[$kk],true);
foreach($localField as $kkk=>$vvv){
if(!empty($lineField[$kkk])){
foreach($vvv as $kkkk=>$vvvv){
if($vvvv != $lineField[$kkk][$kkkk]){
echo '数据表'.$key.'.'.$kk.'中'.$kkk.'字段不符'.json_encode(['local'=>$vvv,'line'=>$lineField[$kkk]]).PHP_EOL;
}
}
}else{
echo '数据表'.$key.'.'.$kk.'中：'.$kkk.'字段不存在'.PHP_EOL;
}
}
}else{
echo '数据表'.$key.'.'.$kk.'不存在'.PHP_EOL;
}
}
}
} catch (PDOException $e) {
$this->echoError("Failed to get DB handle: " . $e->getMessage() . "\n");
}
}
// 数据表结构获取
public function dataStat($config){
// 获取本地数据
$rows = [];
foreach($config as $key=>$val){
$serverName = $val['host']; //服务器名称，在 sql server management studio 的登录界面查看
$uid = $val['username']; //数据库用户名
$pwd = $val['password']; //数据库密码
$db = $key; // 数据库名
$dbName = $val['dbname']; // 数据表实际名称
$connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database" => $dbName);
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn == false) {
echo "连接失败！";
die(print_r(sqlsrv_errors(), true));
}
// select * from master.dbo.SysDatabases // 获取所有的账户类型
// SELECT * FROM SysObjects Where XType= 'U' order by name asc; // 获取所有的数据表
// SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME= 'Agent'; // 获取表结构
//执行有结果集的SQL语句
$query = sqlsrv_query($conn, " SELECT * FROM SysObjects Where XType= 'U' order by name asc ");
while ($row = sqlsrv_fetch_array($query,SQLSRV_FETCH_ASSOC)) {
$tableQuery = sqlsrv_query($conn, " SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME= '".$row['name']."' ");
$tableFields = [];
while ($tableRow = sqlsrv_fetch_array($tableQuery,SQLSRV_FETCH_ASSOC)) {
$tableFields[$tableRow['COLUMN_NAME']] = [
'COLUMN_NAME' => $tableRow['COLUMN_NAME'],
'DATA_TYPE' => $tableRow['DATA_TYPE'],
'CHARACTER_MAXIMUM_LENGTH' => $tableRow['CHARACTER_MAXIMUM_LENGTH'],
'CHARACTER_OCTET_LENGTH' => $tableRow['CHARACTER_OCTET_LENGTH'],
];
}
$temp = json_encode($tableFields,JSON_UNESCAPED_UNICODE);
$rows[$db][$row['name']] = $temp;
}
}
return $rows;
}