<?php
/**
 +------------------------------------------------------------------------------
 * mssql导入mysql 操作类 1.1
 +------------------------------------------------------------------------------
 * @author   ljqsc <ljqsc2010@gmail.com> msn:ljq_sc@hotmail.com
 * @version  $Id$
 +------------------------------------------------------------------------------
update log 
1.添加日志记录方法
2.优化数据库插入操作
3.设置内存释放
 */
/*
//测试用例
set_time_limit ( 0 );
ini_set ( "memory_limit", "2000M" );//设置最大使用内存
$mssqlArr = array("MSSQL_HOST"=>'',"MSSQL_PORT"=>'',"MSSQL_DBNAME"=>'',"MSSQL_USER"=>'',"MSSQL_PWD"=>'');
$mysqlArr = array("MYSQL_HOST"=>'',"MYSQL_PORT"=>'',"MYSQL_DBNAME"=>'',"MYSQL_USER"=>'',"MYSQL_PWD"=>'');
$p = new Mssql2Mysql($mssqlArr,$mysqlArr);
//$p->getMssqlStruct();//得到sqlserver 的sql 文件
//$p->getMysqlStruct();//得到mysql 的sql 文件
$p->debug = 1;//打开调试模式，有错误会终止程序运行
$p->insertDataNum = 2;//先插入2条，测试数据是否正常插入
$p->doImport(); //执行导入如果指定表名则但表插入
*/

class Mssql2Mysql{
//mysql 相关参数
protected $MYSQL_HOST = ''; //服务器地址
protected $MYSQL_PORT = '3306'; //服务器端口
protected $MYSQL_DBNAME = ''; //数据库名
protected $MYSQL_USER = ''; //用户名
protected $MYSQL_PWD = ''; //密码
//mssql 相关参数
protected $MSSQL_HOST = ''; //服务器地址
protected $MSSQL_PORT = '1433'; //服务器端口
protected $MSSQL_DBNAME = ''; //数据库名
protected $MSSQL_USER = ''; //用户名
protected $MSSQL_PWD = ''; //密码
//sql连接
protected $MYSQL_CONN = '';
protected $MSSQL_CONN = '';
//获取mssql那种类型的表数据
protected $xtype= 'U';//默认为用户表
//mysql不需要长度的字段
protected $noLengthArr = Array("datetime","date","smalldatetime");
//需要用convert转换的数据
protected $convertFieldMssqlArr = Array("nvarchar","ntext","nchar","smalldatetime");
protected $convertFieldMysqlArr = Array("varchar(255)","text","varchar(255)","datetime");
//sql server 需要转换成mysql的字段类型,两个数组必须对应更改。#为字段定界符
protected $mssqlFieldArr = Array("/#char#/i","/#p_w_picpath#/i","/#nvarchar#/i","/#bit#/i","/#ntext#/i","/#smalldatetime#/i");
protected $mysqlFieldArr = Array("text","binary","varchar","tinyint","text","datetime");
//插入数据的数量
public $insertDataNum = 0;//0代表所有
//调试模式
public $debug = 0;//1代表调试模式

   /**
     +----------------------------------------------------------
     * 架构函数 获得相关参数.链接mysql，和mssql
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
    public function __construct($mssqlArr,$mysqlArr){
$this->checkSystemSupport();
$this->MYSQL_HOST = $mysqlArr['MYSQL_HOST'];
$this->MYSQL_PORT = $mysqlArr['MYSQL_PORT'];
$this->MYSQL_DBNAME = $mysqlArr['MYSQL_DBNAME'];
$this->MYSQL_USER = $mysqlArr['MYSQL_USER'];
$this->MYSQL_PWD = $mysqlArr['MYSQL_PWD'];
//mssql 相关参数
$this->MSSQL_HOST = $mssqlArr['MSSQL_HOST'];
$this->MSSQL_PORT = $mssqlArr['MSSQL_PORT'];
$this->MSSQL_DBNAME = $mssqlArr['MSSQL_DBNAME'];
$this->MSSQL_USER = $mssqlArr['MSSQL_USER'];
$this->MSSQL_PWD = $mssqlArr['MSSQL_PWD'];

$this->__mysqlconn();
$this->__mssqlconn();
    }
/**
     +----------------------------------------------------------
     * 析构函数 销毁小管参数。
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
    public function __destruct(){
if($this->MYSQL_CONN){
mysql_close($this->MYSQL_CONN);
}
if($this->MSSQL_CONN){
mssql_close($this->MSSQL_CONN);
}
$this->MYSQL_HOST = "";
$this->MYSQL_DBNAME = "";
$this->MYSQL_USER = "";
$this->MYSQL_PWD = "";
//mssql 相关参数
$this->MSSQL_HOST = "";
$this->MSSQL_DBNAME = "";
$this->MSSQL_USER = "";
$this->MSSQL_PWD = "";
    }
/**
     +----------------------------------------------------------
     * 检测是否符合环境要求
* windows 需要覆盖ntwdblib.dll文件
* linux 需要安装freetds程序并重新编译php
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
    public function checkSystemSupport(){
$ishave = function_exists("mssql_connect");
if(!$ishave){
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
echo "检查是否安装ntwdblib.dll文件";
} else {
echo "freetds no install or \n php no include freetds";
}
die;
}
    }
  /**
     +----------------------------------------------------------
     * 连接mysql
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
protected function __mysqlconn(){
$this->MYSQL_CONN = mysql_connect($this->MYSQL_HOST.":".$this->MYSQL_PORT, $this->MYSQL_USER, $this->MYSQL_PWD) or die("Could not connect: check host,port,user,pwd" . mysql_error());
mysql_query("CREATE DATABASE IF NOT EXISTS `$this->MYSQL_DBNAME`") or die ('Can\'t create database $this->MYSQL_DBNAME  : ' . mysql_error());
mysql_select_db($this->MYSQL_DBNAME, $this->MYSQL_CONN) or die ('Can\'t use  : ' . mysql_error());
mysql_query ( "set names 'UTF8'");
$this->addlog("mysql连接成功！");
    }
   /**
     +----------------------------------------------------------
     * 连接mssql
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
    protected function __mssqlconn(){
$this->MSSQL_CONN = mssql_connect($this->MSSQL_HOST.",".$this->MSSQL_PORT, $this->MSSQL_USER, $this->MSSQL_PWD)or die("Could not connect: check host,port,user,pwd" . mssql_error());
mssql_select_db($this->MSSQL_DBNAME, $this->MSSQL_CONN) or die("数据库选择失败mssql");
$this->addlog("mssql连接成功！");
    }
  /**
     +----------------------------------------------------------
     * 执行导入(
* 首先创建相关表
* 根据操作类型，如果$tablename 为null 则自动读取全部的表数据)
     +----------------------------------------------------------
     * @access public
+----------------------------------------------------------
* @param string $tablename  需要处理的表数量
     +----------------------------------------------------------
     * @return 无  
     +----------------------------------------------------------
     */
public function doImport($tablename=NULL) {
if(is_null($tablename)){
$this->CreateAllTables();
$this->InsertAllDatas();
}else{
$this->CreateTable($tablename);//需要检查mssql是否有该表
$this->insertDataToMysql($tablename);//检查mysql是否有该表
}
}
  /**
     +----------------------------------------------------------
     * 批量创建mysql表
     +----------------------------------------------------------
     * @access public
+----------------------------------------------------------
* @param string $saveType  需要存储的类型,默认为文件
     +----------------------------------------------------------
     * @return str  
     +----------------------------------------------------------
     */
public function CreateAllTables() {
//获得所有表
$allTablesArr = $this->getMssqlAllTableName();
foreach($allTablesArr as $k=>$v){
$this->CreateTable($v);
}
}
 /**
     +----------------------------------------------------------
     * 创建mysql某一张表
     +----------------------------------------------------------
     * @access public
+----------------------------------------------------------
* @param string $tablename  表明
     +----------------------------------------------------------
     * @return str  
     +----------------------------------------------------------
     */
public function CreateTable($tablename) {
if( mysql_num_rows(mysql_query("SHOW TABLES LIKE '".$tablename."'"))==1 ){
$this->addlog("表已经存在$tablename表已经存在");
return;
}
$sql = $this->getCreateTableMysqlSql($tablename);
if($this->debug){
$result = mysql_query($sql) or die("创建表失败: 错误行:".__LINE__ . mysql_error());
}else{
$result = mysql_query($sql);
}
if($result){
$this->addlog("mysql建表-成功-#$tablename#成功");
}else{
$this->addlog("mysql建表-失败-#$tablename#失败");
}
}
/**
     +----------------------------------------------------------
     * 将所有数据插入到mysql
     +----------------------------------------------------------
     * @access public
+----------------------------------------------------------
     * @param string 
     +----------------------------------------------------------
     * @return string 
     +----------------------------------------------------------
     */
    public function insertAllDatas() {
//获得所有表
$allTablesArr = $this->getMssqlAllTableName();
foreach($allTablesArr as $k=>$v){
$this->insertData($v);
}
}
/**
     +----------------------------------------------------------
     * 执行插入,专业你对一张表进行插入
     +----------------------------------------------------------
     * @access protected
+----------------------------------------------------------
     * @param $tablename 
     +----------------------------------------------------------
     * @return 无
     +----------------------------------------------------------
     */
    protected function insertData($tablename) {
$insertNum = 0;
$getNum = 0;
$sqlfields = $this->getInsertFieldsSql($tablename);
$getDataSql = $this->getSelcectConvertFields($tablename);
$result = mssql_query($getDataSql);

while ($row = mssql_fetch_array($result,MSSQL_NUM)) {//获得某表所有数据,只取数字键
$sqlvalues = NULL;
//处理相关字段
for($i=0;$i<count($row);$i++){
$value = addslashes($row[$i]);
$value = $this->convert($value);
if( mssql_field_type($result,$i)=="datetime" or mssql_field_type($result,$i)=="smalldatetime"){
$value = $this->convertDate($value);
}
$sqlvalues .= is_null($sqlvalues) ?  "'$value'" : ",'$value'";
}
if($sqlvalues){
$insertsql = $sqlfields." VALUES (".$sqlvalues.")";
if($this->debug){
mysql_query($insertsql) or die("数据插入失败,错误行:".__LINE__ . mysql_error());
}else{
mysql_query($insertsql);
}
if(mysql_affected_rows()){
$insertNum++;
}
}
$getNum++;
}
$this->addlog("从mssql获得-$getNum-个数据,插入mysql-$insertNum-个数据");
mssql_free_result($result);
}
/**
     +----------------------------------------------------------
     * 得到mysql数据库结构 sql
     +----------------------------------------------------------
     * @access public
+----------------------------------------------------------
     * @param string $tablename 需要生生sql文件的表名 
     +----------------------------------------------------------
     * @return str  
     +----------------------------------------------------------
     */
public function getMysqlStruct() {
//获得所有表
$allTablesArr = $this->getMssqlAllTableName();
foreach($allTablesArr as $k=>$v){
//生成建表sql语句
$sql .= $this->getCreateTableMysqlSql($v)."\n\n\n";
}
header('Content-type: application/txt');
header('Content-Disposition: p_w_upload; filename="mssql2mysql_'.date("Y-m-d-H-i-s").'.sql"'); 
echo $sql;exit;
}
/**
     +----------------------------------------------------------
     * 得到mysql数据库结构 sql
     +----------------------------------------------------------
     * @access public
+----------------------------------------------------------
     * @param string $tablename 需要生生sql文件的表名 
     +----------------------------------------------------------
     * @return str  
     +----------------------------------------------------------
     */
public function getMssqlStruct() {
//获得所有表
$allTablesArr = $this->getMssqlAllTableName();
foreach($allTablesArr as $k=>$v){
$sql_qian = "CREATE TABLE IF NOT EXISTS `$v` (\n";
$sql_hou = ");";
$fieldArr = $this->getMssqlAllTableDesc($v);
$sql = null;
foreach($fieldArr as $a=>$b){
$field = $b['field'];
$type = $b['type'];
$length = $b['length'];
$sql .= is_null($sql) ? "`$field` $type($length)" : ",\n"."`$field` $type($length)";
}
$sqlAll .= $sql_qian.$sql.$sql_hou."\n\n\n";
}
header('Content-type: application/txt');
header('Content-Disposition: p_w_upload; filename="mssql2mysql_'.date("Y-m-d-H-i-s").'.sql"'); 
echo $sql;exit;
}
 /**
     +----------------------------------------------------------
     * 自动生成mysql建表的sql语句
     +----------------------------------------------------------
     * @access protected
+----------------------------------------------------------
     * @param string $tablename 需要生生sql文件的表名 
     +----------------------------------------------------------
     * @return str  
     +----------------------------------------------------------
     */
protected function getCreateTableMysqlSql($tablename) {
$fieldArr = $this->getMssqlAllTableDesc($tablename);
$sql_qian = "CREATE TABLE IF NOT EXISTS `$tablename` (\n";
$sql_hou = ") ENGINE=MyISAM DEFAULT CHARSET=utf8;";

foreach($fieldArr as $k=>$v){
$field = $v['field'];
$type = $v['type'];
$length = $v['length'];
if(in_array ($type, $this->noLengthArr)){
$sql .= isset($sql) ? ",\n"."`$field` #$type#" : "`$field` $type";
}else{
$sql .= isset($sql) ? ",\n"."`$field` #$type#($length)" : "`$field` $type($length)";
}
}
$sql = $this->fieldTypeFliter($sql);
$sql = $sql_qian.$sql.$sql_hou;
return $sql;
}
 /**
     +----------------------------------------------------------
     * 获得sql server 所有的表结构 中文表除外
     +----------------------------------------------------------
     * @access protected
+----------------------------------------------------------
     * @param string str 返回所有mssql数据库表名
     +----------------------------------------------------------
     * @return array 
     +----------------------------------------------------------
     */
protected function getMssqlAllTableName() {
$xtype = isset($this->xtype) ? $this->xtype : 'U';
$tablename_sql = "SELECT name FROM sysobjects WHERE xtype ='$xtype'";
if($this->debug){
$result = mssql_query($tablename_sql) or die("获得mssql表名失败,错误行:".__LINE__ );
}else{
$result = mssql_query($tablename_sql);
}
while ($row = mssql_fetch_array($result)) {
if (!preg_match("/^[".chr(0xa1)."-".chr(0xff)."]+$/", $row['name'])){ //过滤掉中文表
$tablenameArr[] = $row['name'];
}
}
return $tablenameArr;
}
  /**
     +----------------------------------------------------------
     * 获得sql server 所有表的字段类型
     +----------------------------------------------------------
     * @access protected
+----------------------------------------------------------
     * @param string str 获得mssql数据库表结构
     +----------------------------------------------------------
     * @return array二维  
     +----------------------------------------------------------
     */
protected function getMssqlAllTableDesc($tablename) {
$tabledesc_sql = "SELECT syscolumns.name,systypes.name, syscolumns.length  FROM syscolumns, systypes  WHERE syscolumns.
xusertype = systypes.xusertype  AND syscolumns.id = object_id('$tablename')";
if($this->debug){
$result = mssql_query($tabledesc_sql) or die("获得mssql--$tablename表属性字段失败,错误行:".__LINE__ );
}else{
$result = mssql_query($tabledesc_sql);
}
$i = 0;
while ($row = mssql_fetch_array($result)) {
$tabledescArr[$i]['field'] = $row[0];//字段名
$tabledescArr[$i]['type'] = $row[1];//类型
$tabledescArr[$i]['length'] = $row[2];//长度
$i++;
}
return $tabledescArr;
}
   /**
     +----------------------------------------------------------
     * 返回mysql合法的字段类型,需要添加相关字段类型
     +----------------------------------------------------------
     * @access public
+----------------------------------------------------------
     * @param string 
     +----------------------------------------------------------
     * @return str
     +----------------------------------------------------------
     */
protected function fieldTypeFliter($sql) {
$sql = preg_replace($this->mssqlFieldArr, $this->mysqlFieldArr, $sql);
//过滤掉定界符
$sql = str_replace("#","",$sql);
return $sql;
}
    /**
     +----------------------------------------------------------
     * 构造数据转化字段 
     +----------------------------------------------------------
     * @access public
+----------------------------------------------------------
     * @param string $tablename
     +----------------------------------------------------------
     * @return string 
     +----------------------------------------------------------
     */
protected function getSelcectConvertFields($tablename) {
if($this->insertDataNum === 0){
$sql_qian = "SELECT * ";
}else{
$sql_qian = "SELECT TOP $this->insertDataNum ";
}
$fieldsInfo = $this->getMssqlAllTableDesc($tablename);
foreach ($fieldsInfo as $k=>$v){
$field = $v['field'];
$type = $v['type'];
if(in_array($type,$this->convertFieldMssqlArr)){
$type = str_ireplace($this->convertFieldMssqlArr, $this->convertFieldMysqlArr, $type);
$field = "convert($type,$field) as $field";
}
$sql .= isset($sql) ? ","."$field" : "$field";
}
$sql_hou = " FROM $tablename";
$sql = $sql_qian.$sql.$sql_hou;
return $sql;
}
  /**
     +----------------------------------------------------------
     * 构造插入mysql insert 字段的sql语句
     +----------------------------------------------------------
     * @access public
+----------------------------------------------------------
     * @param string $tablename
     +----------------------------------------------------------
     * @return string 插入字段的sql语句
     +----------------------------------------------------------
     */
protected function getInsertFieldsSql($tablename) {
$dbname = $this->MSSQL_DBNAME;
$tableinfo = $this->getMssqlAllTableDesc($tablename);
$sql_qian = "INSERT INTO `$dbname`.$tablename (";
foreach ($tableinfo as $k=>$v){
$field = $v['field'];
$sql .= isset($sql) ? ","."`$field`" : "`$field`";
}
$sql_hou = ")";
$sql = $sql_qian.$sql.$sql_hou;
return $sql;
}
   /**
     +----------------------------------------------------------
     * 判断编码并做相应转换 
     +----------------------------------------------------------
     * @access public
+----------------------------------------------------------
     * @param string str 需要转换的字符串
     +----------------------------------------------------------
     * @return string 返回转换后的字符串
     +----------------------------------------------------------
     */
protected function convert($str) {
        $encoding = mb_detect_encoding($str, array('ASCII','GB2312','GBK','UTF-8','BIG5'));
if($encoding != 'CP936' and $encoding != "UTF-8"){
$str = mb_convert_encoding($str, "UTF-8", $encoding);
}
         return $str;
}
/**
     +----------------------------------------------------------
     * 日期处理
     +----------------------------------------------------------
     * @access public
+----------------------------------------------------------
     * @param string str 需要转换的字符串
     +----------------------------------------------------------
     * @return string 返回转换后的字符串
     +----------------------------------------------------------
     */
protected function convertDate($str) {
        $time = explode(" ",$str);
$date = explode("/",$time[0]);
$str = $date[2]."-".$date[1]."-".$date[0]." ".$time[1];
return $str;
}
/**
     +----------------------------------------------------------
     * 记录日志类
     +----------------------------------------------------------
     * @access public
+----------------------------------------------------------
     * @param string str 需要转换的字符串
     +----------------------------------------------------------
     * @return string 返回转换后的字符串
     +----------------------------------------------------------
     */
function addlog($str){
$str = date("Y-m-d H:i:s",time())."\t".$str."\n";
$handle = fopen("mssqltomysql.log", "a");
fwrite($handle, $str);
fclose($handle);
}
}
?>