-
-
/**
- * 读取 sql 文件并写入数据库
- * @version 1.01 demo.php
- * @author xingshaocheng
- * @edit: bbs.it-home.org
- */
- class dbmanager
- {
- var $dbhost = '';
- var $dbuser = '';
- var $dbpassword = '';
- var $dbschema = '';
-
- function __construct($host,$user,$password,$schema)
- {
- $this->dbhost = $host;
- $this->dbuser = $user;
- $this->dbpassword = $password;
- $this->dbschema = $schema;
- }
-
- function createfromfile($sqlpath,$delimiter = '(;\n)|((;\r\n))|(;\r)',$prefix = '',$commenter = array('#','--'))
- {
- //判断文件是否存在
- if(!file_exists($sqlpath))
- return false;
-
- $handle = fopen($sqlpath,'rb');
-
- $sqlstr = fread($handle,filesize($sqlpath));
-
- //通过sql语法的语句分割符进行分割
- $segment = explode(";",trim($sqlstr));
-
- //var_dump($segment);
-
- //去掉注释和多余的空行
- foreach($segment as & $statement)
- {
- $sentence = explode("\n",$statement);
-
- $newstatement = array();
-
- foreach($sentence as $subsentence)
- {
- if('' != trim($subsentence))
- {
- //判断是会否是注释
- $iscomment = false;
- foreach($commenter as $comer)
- {
- if(eregi("^(".$comer.")",trim($subsentence)))
- {
- $iscomment = true;
- break;
- }
- }
- //如果不是注释,则认为是sql语句
- if(!$iscomment)
- $newstatement[] = $subsentence;
- }
- }
-
- $statement = $newstatement;
- }
- //对表名加前缀
- if('' != $prefix)
- {
-
-
- //只有表名在第一行出现时才有效 例如 create table talbename
-
- $regxtable = "^[\`\'\"]{0,1}[\_a-za-z]+[\_a-za-z0-9]*[\`\'\"]{0,1}$";//处理表名的正则表达式
- $regxleftwall = "^[\`\'\"]发达";
-
- $sqlflagtree = array(
- "create" => array(
- "table" => array(
- "$regxtable" => 0
- )
- ),
- "insert" => array(
- "into" => array(
- "$regxtable" => 0
- )
- )
-
- );
-
- foreach($segment as & $statement)
- {
- $tokens = split(" ",$statement[0]);
-
- $tablename = array();
- $this->findtablename($sqlflagtree,$tokens,0,$tablename);
-
- if(empty($tablename['leftwall']))
- {
- $newtablename = $prefix.$tablename['name'];
- }
- else{
- $newtablename = $tablename['leftwall'].$prefix.substr($tablename['name'],1);
- }
-
- $statement[0] = str_replace($tablename['name'],$newtablename,$statement[0]);
- }
-
- }
- //组合sql语句
- foreach($segment as & $statement)
- {
- $newstmt = '';
- foreach($statement as $sentence)
- {
- $newstmt = $newstmt.trim($sentence)."\n";
- }
-
- $statement = $newstmt;
- }
-
- //用于测试------------------------
- //var_dump($segment);
- //writearraytofile('data.txt',$segment);
- //-------------------------------
-
- self::savebyquery($segment);
-
- return true;
- }
-
- private function savebyquery($sqlarray)
- {
- $conn = mysql_connect($this->dbhost,$this->dbuser,$this->dbpassword);
-
- mysql_select_db($this->dbschema);
-
- foreach($sqlarray as $sql)
- {
- mysql_query($sql);
- }
- mysql_close($conn);
- }
-
- private function findtablename($sqlflagtree,$tokens,$tokenskey=0,& $tablename = array())
- {
- $regxleftwall = "^[\`\'\"]发达";
-
- if(count($tokens) return false;
-
- if('' == trim($tokens[$tokenskey]))
- {
- return self::findtablename($sqlflagtree,$tokens,$tokenskey+1,$tablename);
- }
- else
- {
- foreach($sqlflagtree as $flag => $v)
- {
- if(eregi($flag,$tokens[$tokenskey]))
- {
- if(0==$v)
- {
- $tablename['name'] = $tokens[$tokenskey];
-
- if(eregi($regxleftwall,$tablename['name']))
- {
- $tablename['leftwall'] = $tablename['name']{0};
- }
-
- return true;
- }
- else{
- return self::findtablename($v,$tokens,$tokenskey+1,& $tablename);
- }
- }
- }
- }
-
- return false;
- }
- }
function writeArrayToFile($fileName,$dataArray,$delimiter="\r\n")
- {
- $handle=fopen($fileName, "wb");
-
- $text = '';
-
- foreach($dataArray as $data)
- {
- $text = $text.$data.$delimiter;
- }
- fwrite($handle,$text);
- }
//测试
- $dbM = new DBManager('localhost','w01f','123456','test');
- $dbM->createFromFile('data.sql',null,'fff_');
- ?>
-
复制代码
|