PHP sql_split函数代码示例

 function test_sql_split()
 {
     $sql = "select * from foo;\nselect * from bar";
     $split = sql_split($sql);
     $this->assertEquals(2, count($split));
     $this->assertEquals("select * from foo\n", $split[0]);
     $this->assertEquals("select * from bar\n", $split[1]);
 }

 static function setUpBeforeClass()
 {
     DB::open(array('master' => true, 'driver' => 'sqlite', 'file' => ':memory:'));
     $sql = sql_split("create table qwerty ( foo char(12), bar char(12) );\n\t\tcreate table foo(id int, name char(12));\n\t\tcreate table bar(id int, name char(12), foo int);\n\t\tcreate table gallery (\n\t\t\tid integer primary key,\n\t\t\ttitle char(48)\n\t\t);\n\t\tcreate table cover (\n\t\t\tid integer primary key,\n\t\t\tgallery integer unique,\n\t\t\ttitle char(48)\n\t\t);\n\t\tcreate table item (\n\t\t\tid integer primary key,\n\t\t\tgallery_id integer,\n\t\t\ttitle char(48)\n\t\t);\n\t\tinsert into gallery (id, title) values (1, 'Gallery One');\n\t\tinsert into cover (id, gallery, title) values (1, 1, 'Cover One');\n\t\tinsert into item (id, gallery_id, title) values (1, 1, 'Item One');\n\t\tinsert into item (id, gallery_id, title) values (2, 1, 'Item Two');\n\t\tinsert into item (id, gallery_id, title) values (3, 1, 'Item Three');\n\t\tinsert into gallery (id, title) values (2, 'Gallery Two');\n\t\tinsert into cover (id, gallery, title) values (2, 2, 'Cover Two');\n\t\tinsert into item (id, gallery_id, title) values (4, 2, 'Item Four');\n\t\tinsert into item (id, gallery_id, title) values (5, 2, 'Item Five');\n\t\tinsert into item (id, gallery_id, title) values (6, 2, 'Item Six');\n\t\tcreate table author (\n\t\t\tid integer primary key,\n\t\t\tname char(32)\n\t\t);\n\t\tcreate table book (\n\t\t\tid integer primary key,\n\t\t\tname char(32)\n\t\t);\n\t\tcreate table book_author (\n\t\t\tbook int not null,\n\t\t\tauthor int not null\n\t\t);\n\t\tinsert into author (id, name) values (1, 'Johnny Fast Fingers');\n\t\tinsert into author (id, name) values (2, 'Frankie Bazzar');\n\t\tinsert into book (id, name) values (1, 'Johnny & Frankie');\n\t\tinsert into book (id, name) values (2, 'Jamaican Me Crazy');\n\t\tinsert into book_author (book, author) values (1, 1);\n\t\tinsert into book_author (book, author) values (1, 2);\n\t\tinsert into book_author (book, author) values (2, 1);\n\t\tinsert into book_author (book, author) values (2, 2);\n\t\tcreate table next_test (\n\t\t\tfieldname int not null\n\t\t);\n\t\t");
     foreach ($sql as $query) {
         DB::execute($query);
     }
     self::$q = new Qwerty();
 }

function sql_execute($sql, $replace = 0, $dosubmit1 = 0)
{
    global $db, $tablepre, $met_visit_day, $met_visit_detail, $met_admin_table;
    $split = sql_split($sql);
    $sqls = $split['sql'];
    $info = $split['info'];
    $infos = explode('#', $info);
    $localurl = "http://";
    $localurl .= $_SERVER['HTTP_HOST'] . $_SERVER["PHP_SELF"];
    $localurl_a = explode("/", $localurl);
    $localurl_count = count($localurl_a);
    $localurl_admin = $localurl_a[$localurl_count - 4];
    $localurl_admin = $localurl_admin . "/system/";
    $localurl_real = explode($localurl_admin, $localurl);
    $localurl = $localurl_real[0];
    if ($infos[3] && $tablepre != $infos[3]) {
        $sqlre1 = 1;
    }
    if ($infos[2] && $localurl != $infos[2]) {
        $sqlre2 = 1;
    }
    if (is_array($sqls)) {
        foreach ($sqls as $sql) {
            if ($replace) {
                $sql = str_replace('met_', $tablepre, $sql);
                $sql = str_replace('metconfig_', 'met_', $sql);
            }
            if ($dosubmit1 == '1') {
                $sql = preg_replace(array('/INSERT INTO ' . $met_admin_table . '/', '/DROP TABLE IF EXISTS ' . $met_admin_table . '/', '/CREATE TABLE `' . $met_admin_table . '`/'), array('INSERT INTO test_admin_table1', 'DROP TABLE IF EXISTS test_admin_table1', 'CREATE TABLE `test_admin_table1`'), $sql);
            }
            if ($sqlre1 == 1) {
                $sql = preg_replace(array('/^INSERT INTO ' . $infos[3] . '/', '/^DROP TABLE IF EXISTS ' . $infos[3] . '/', '/^CREATE TABLE `' . $infos[3] . '/'), array('INSERT INTO ' . $tablepre, 'DROP TABLE IF EXISTS ' . $tablepre, 'CREATE TABLE `' . $tablepre), $sql, 1);
            }
            if ($sqlre2 == 1) {
                if (!preg_match('/^INSERT INTO ((' . $met_visit_day . ')|(' . $met_visit_detail . '.))/', $sql)) {
                    $sql = str_replace($infos[2], $localurl, $sql);
                }
            }
            if (trim($sql) != '') {
                if (!$db->query($sql)) {
                    return false;
                }
            }
        }
    } else {
        if (!$db->query($sqls)) {
            return false;
        }
    }
    if ($dosubmit1 == '1') {
        if (!$db->query('DROP TABLE IF EXISTS test_admin_table1')) {
            return false;
        }
    }
    return true;
}

function sql_execute($sql, $tablepre)
{
    $sqls = sql_split($sql, $tablepre);
    if (is_array($sqls)) {
        foreach ($sqls as $sql) {
            if (trim($sql) != '') {
                mysql_query($sql);
            }
        }
    } else {
        mysql_query($sqls);
    }
    return true;
}

/**
 * 数据库处理
 */
function sql_execute(&$db, $sql)
{
    $sqls = sql_split($sql, $db->dbcharset, $db->tablepre);
    if (is_array($sqls)) {
        foreach ($sqls as $sql) {
            if (trim($sql) != '') {
                $db->query($sql);
            }
        }
        return TRUE;
    } else {
        return $db->query($sqls);
    }
}

function sql_execute($sql)
{
    global $db;
    $sqls = sql_split($sql);
    if (is_array($sqls)) {
        foreach ($sqls as $sql) {
            if (trim($sql) != '') {
                $db->query($sql);
            }
        }
    } else {
        $db->query($sqls);
    }
    return true;
}

function upgrade_db($out = false)
{
    loader_import('saf.File.Directory');
    $files = Dir::find('*-' . upgrade_version_num() . '.sql', 'upgrade');
    if (count($files) == 0) {
        return true;
    }
    $file = array_shift($files);
    $sql = join('', file($file));
    $sql = sql_split($sql);
    foreach ($sql as $query) {
        if ($out) {
            echo '<pre>' . $query . '</pre>';
        }
        if (!db_execute($query)) {
            return false;
        }
    }
    return true;
}

    static function setUpBeforeClass()
    {
        DB::open(array('master' => true, 'driver' => 'sqlite', 'file' => ':memory:'));
        DB::$prefix = 'elefant_';
        $sql = sql_split('
			create table #prefix#webpage (
				id char(72) not null primary key,
				title char(72) not null,
				menu_title char(72) not null,
				window_title char(72) not null,
				access char(12) not null,
				layout char(48) not null,
				description text,
				keywords text,
				body text
			);
			insert into #prefix#webpage (id, title, menu_title, window_title, access, layout, description, keywords, body) values ("index", "Welcome to Elefant", "Home", "", "public", "default", "", "", \'<table><tbody><tr><td><h3>Congratulations!</h3>You have successfully installed Elefant, the refreshingly simple new PHP web framework and CMS.</td><td><h3>Getting Started</h3>To log in as an administrator and edit pages, write a blog post, or upload files, go to <a href="/admin">/admin</a>.</td><td><h3>Developers</h3>Documentation, source code and issue tracking can be found at <a href="http://github.com/jbroadway/elefant">github.com/jbroadway/elefant</a></td></tr></tbody></table>\');
		');
        foreach ($sql as $query) {
            if (!DB::execute($query)) {
                die(DB::error());
            }
        }
    }

     printf("Testing patch: %s\n", basename($version['patch']));
     exec('patch --dry-run -p1 -f -i ' . $version['patch'], $output);
     $output = join("\n", $output);
     if (strpos($output, 'FAILED')) {
         Cli::out('Error applying patch ' . $version['patch'], 'error');
         echo "See conf/updates/error.log for details.\n";
         file_put_contents('conf/updates/error.log', $output);
         return;
     }
     // Patch is okay to apply
     echo "Patch ok, applying...\n";
     exec('patch -p1 -f -i ' . $version['patch']);
     // Apply associated database updates
     if ($version['script']) {
         printf("Applying db update: %s\n", basename($version['script']));
         $sqldata = sql_split(file_get_contents($version['script']));
         DB::beginTransaction();
         foreach ($sqldata as $sql) {
             if (!DB::execute($sql)) {
                 $error = DB::error();
                 DB::rollback();
                 Cli::out('Error applying db update: ' . $version['script'], 'error');
                 echo "See conf/updates/error.log for details.\n";
                 file_put_contents('conf/updates/error.log', $error);
                 return;
             }
         }
         DB::commit();
     }
 }
 Cli::out(sprintf("Applied %d updates.", count($versions)), 'success');

			$_POST['host'] = $_POST[$_POST['driver'] . '_host'];
			$_POST['port'] = $_POST[$_POST['driver'] . '_port'];
			$_POST['name'] = $_POST[$_POST['driver'] . '_name'];
			$_POST['user'] = $_POST[$_POST['driver'] . '_user'];
			$_POST['pass'] = $_POST[$_POST['driver'] . '_pass'];
			$_POST['prefix'] = $_POST[$_POST['driver'] . '_prefix'];

			DB::$prefix = $_POST['prefix'];

			if (! DB::open ($_POST)) {
				$data['error'] = DB::error ();
			} else {
				$data['error'] = false;

				// create the database
				$sqldata = sql_split (file_get_contents ('../conf/install_' . $_POST['driver'] . '.sql'));
				foreach ($sqldata as $sql) {
					if (! DB::execute ($sql)) {
						$data['error'] = DB::error ();
						DB::execute ('rollback');
						break;
					}
				}
				
				$wp = new Webpage ('index');
				Versions::add ($wp);
				$b = new Block ('members');
				Versions::add ($b);

				// write the settings
				if (! $data['error']) {

/**
 * 执行文件中SQL语句函数
 * @param string $file sql语句文件路径
 * @param string $tablepre  自己的前缀
 * @return multitype:string 返回最终需要的sql语句
 */
function execute_sql_from_file($file)
{
    $sql_data = file_get_contents($file);
    $sql_format = sql_split($sql_data, C('DB_PREFIX'));
    $counts = count($sql_format);
    for ($i = 0; $i < $counts; $i++) {
        $sql = trim($sql_format[$i]);
        D()->execute($sql);
    }
    return true;
}

$page->title = __ ('Upgrading User App');

// grab the database driver
$conn = conf ('Database', 'master');
$driver = $conn['driver'];

// check if upgrade script exists and if so, run it
$base_version = preg_replace ('/-.*$/', '', $version);
$file = 'apps/' . $this->app . '/conf/upgrade_' . $base_version . '_' . $driver . '.sql';
if (file_exists ($file)) {
    // begin the transaction
    DB::beginTransaction ();

    // parse the database schema into individual queries
    $sql = sql_split (file_get_contents ($file));

    // execute each query in turn
    foreach ($sql as $query) {
        if (! DB::execute ($query)) {
            // show error and rollback on failures
            printf (
                '<p class="visible-notice">%s: %s</p><p>%s</p>',
                __ ('Error'),
                DB::error (),
                __ ('Install failed.')
            );
            DB::rollback ();
            return;
        }
    }

     header(sprintf('Location: ?step=3&dbhost=%s&dbport=%s&database=%s&dbuser=%s&dbpass=%s&drop=%s&error=%s', $cgi->dbhost, $cgi->dbport, $cgi->database, $cgi->dbuser, $cgi->dbpass, $cgi->drop, 'Failed to connect to MySQL: ' . mysql_error()));
     exit;
 }
 if (!@mysql_select_db($cgi->database, $conn)) {
     header(sprintf('Location: ?step=3&dbhost=%s&dbport=%s&database=%s&dbuser=%s&dbpass=%s&drop=%s&error=%s', $cgi->dbhost, $cgi->dbport, $cgi->database, $cgi->dbuser, $cgi->dbpass, $cgi->drop, 'Can\'t use database "' . $cgi->database . '": ' . mysql_error()));
     exit;
 }
 $query = mysql_query('select version()', $conn);
 $version = mysql_result($query, 0);
 mysql_free_result($query);
 if (version_compare($version, '5.0.0', 'ge')) {
     $sql = @join('', @file('install-mysql.sql'));
 } else {
     $sql = @join('', @file('install-old.sql'));
 }
 $sql = sql_split($sql);
 // execute each sql query
 foreach ($sql as $query) {
     if ($cgi->drop == 'yes' && preg_match('/^create table ([^ ]+) /is', $query, $regs)) {
         @mysql_query('drop table ' . $regs[1]);
     }
     if (!@mysql_query($query, $conn)) {
         $data['error'] = true;
         $data['body'] = '<p class="notice">SQL Error: ' . mysql_error() . '</p>';
         break;
     }
 }
 if (!$data['error']) {
     // save info to config.ini.php
     $conf = @join('', @file('../inc/conf/config.ini.php'));
     if ($cgi->dbhost != 'localhost' || $cgi->dbport != 3306) {

 public function recover()
 {
     if ($_GET['do'] == 'delete') {
         foreach ((array) $_POST['files'] as $r) {
             unlink($r);
         }
         $this->success(L('do_ok'));
     } elseif ($_GET['do'] == 'import') {
         header('Content-Type: text/html; charset=UTF-8');
         $filename = $_GET['filename'];
         $filelist = dir_list($this->datadir);
         foreach ((array) $filelist as $r) {
             $file = explode('-', basename($r));
             if ($file[0] == $filename) {
                 $files[] = $r;
             }
         }
         foreach ((array) $files as $file) {
             //读取数据文件
             $sqldata = file_get_contents($file);
             $sqlFormat = sql_split($sqldata, C('DB_PREFIX'));
             foreach ((array) $sqlFormat as $sql) {
                 $sql = trim($sql);
                 if (strstr($sql, 'CREATE TABLE')) {
                     preg_match('/CREATE TABLE `([^ ]*)`/', $sql, $matches);
                     $ret = $this->excuteQuery($sql);
                     //if($ret){echo   L('CREATE_TABLE_OK').$matches[0].' <br />';}else{echo 'Error sql:'.$sql;}exit;
                 } else {
                     $ret = $this->excuteQuery($sql);
                 }
             }
             echo L('CREATE_TABLE_OK') . basename($file) . '<br>';
         }
     } else {
         $filelist = dir_list($this->datadir);
         foreach ((array) $filelist as $r) {
             $filename = explode('-', basename($r));
             $files[] = array('path' => $r, 'file' => basename($r), 'name' => $filename[0], 'size' => filesize($r), 'time' => filemtime($r));
         }
         $this->assign('files', $files);
         $this->display();
     }
 }

 public function mysql()
 {
     $n = intval($_GET['n']);
     $arr = array();
     $dbHost = trim($_POST['dbhost']);
     $dbPort = trim($_POST['dbport']);
     $dbName = trim($_POST['dbname']);
     $dbHost = empty($dbPort) || $dbPort == 3306 ? $dbHost : $dbHost . ':' . $dbPort;
     $dbUser = trim($_POST['dbuser']);
     $dbPwd = trim($_POST['dbpw']);
     $dbPrefix = empty($_POST['dbprefix']) ? 'think_' : trim($_POST['dbprefix']);
     $username = trim($_POST['manager']);
     $password = trim($_POST['manager_pwd']);
     //网站名称
     $site_name = addslashes(trim($_POST['sitename']));
     //网站域名
     $site_url = trim($_POST['siteurl']);
     $_site_url = parse_url($site_url);
     //附件地址
     $sitefileurl = $_site_url['path'] . "d/file/";
     //描述
     $seo_description = trim($_POST['siteinfo']);
     //关键词
     $seo_keywords = trim($_POST['sitekeywords']);
     //测试数据
     $testdata = (int) $_POST['testdata'];
     //邮箱地址
     $siteemail = trim($_POST['manager_email']);
     $conn = @mysql_connect($dbHost, $dbUser, $dbPwd);
     if (!$conn) {
         $arr['msg'] = "连接数据库失败!";
         echo json_encode($arr);
         exit;
     }
     mysql_query("SET NAMES 'utf8'");
     //,character_set_client=binary,sql_mode='';
     $version = mysql_get_server_info($conn);
     if ($version < 5.0) {
         $arr['msg'] = '数据库版本太低!';
         echo json_encode($arr);
         exit;
     }
     if (!mysql_select_db($dbName, $conn)) {
         //创建数据时同时设置编码
         if (!mysql_query("CREATE DATABASE IF NOT EXISTS `" . $dbName . "` DEFAULT CHARACTER SET utf8;", $conn)) {
             $arr['msg'] = '数据库 ' . $dbName . ' 不存在，也没权限创建新的数据库！';
             echo json_encode($arr);
             exit;
         }
         if (empty($n)) {
             $arr['n'] = 1;
             $arr['msg'] = "成功创建数据库:{$dbName}<br>";
             echo json_encode($arr);
             exit;
         }
         mysql_select_db($dbName, $conn);
     }
     //读取数据文件
     $sqldata = file_get_contents(MODULE_PATH . 'Data/shuipfblog.sql');
     //读取测试数据
     if ($testdata) {
         $sqldataDemo = file_get_contents(MODULE_PATH . 'Data/shuipfblog_demo.sql');
         $sqldata = $sqldata . "\r\n" . $sqldataDemo;
     } else {
         //不加测试数据的时候，删除d目录的文件
         try {
             $Dir = new \Dir();
             $Dir->delDir(SITE_PATH . 'd/file/contents/');
         } catch (Exception $exc) {
         }
     }
     $sqlFormat = sql_split($sqldata, $dbPrefix);
     /**
      执行SQL语句
     */
     $counts = count($sqlFormat);
     for ($i = $n; $i < $counts; $i++) {
         $sql = trim($sqlFormat[$i]);
         if (strstr($sql, 'CREATE TABLE')) {
             preg_match('/CREATE TABLE `([^ ]*)`/', $sql, $matches);
             mysql_query("DROP TABLE IF EXISTS `{$matches['1']}");
             $ret = mysql_query($sql);
             if ($ret) {
                 $message = '<li><span class="correct_span">&radic;</span>创建数据表' . $matches[1] . '，完成</li> ';
             } else {
                 $message = '<li><span class="correct_span error_span">&radic;</span>创建数据表' . $matches[1] . '，失败</li>';
             }
             $i++;
             $arr = array('n' => $i, 'msg' => $message);
             echo json_encode($arr);
             exit;
         } else {
             $ret = mysql_query($sql);
             $message = '';
             $arr = array('n' => $i, 'msg' => $message);
             //echo json_encode($arr); exit;
         }
     }
     if ($i == 999999) {
         exit;
//.........这里部分代码省略.........