Unity3d 中 将远程 MySQL 数据库转换为本地 Sqlite

hao0089

　　1、创建MySQL2Sqlite脚本mysql2sqlite.sh：（代码地址：https://gist.github.com/esperlu/943776）





#!/bin/sh
# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
# CREATE block and create them in separate commands _after_ all the INSERTs.
# Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
# The mysqldump file is traversed only once.
# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite
# Thanks to and @artemyk and @gkuenning for their nice tweaks.
mysqldump  --compatible=ansi --skip-extended-insert --compact  "$@" | \
awk '

BEGIN {
FS=",$"
print "PRAGMA synchronous = OFF;"
print "PRAGMA journal_mode = MEMORY;"
print "BEGIN TRANSACTION;"
}
# CREATE TRIGGER statements have funny commenting.  Remember we are in trigger.
/^\/\*.*CREATE.*TRIGGER/ {
gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
print
inTrigger = 1
next
}
# The end of CREATE TRIGGER has a stray comment terminator
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }

# The rest of triggers just get passed through
inTrigger != 0 { print; next }
# Skip other comments
/^\/\*/ { next }
# Print all `INSERT` lines. The single quotes are protected by another single quote.
/INSERT/ {
gsub( /\\\047/, "\047\047" )
gsub(/\\n/, "\n")
gsub(/\\r/, "\r")
gsub(/\\"/, "\"")
gsub(/\\\\/, "\\")
gsub(/\\\032/, "\032")
print
next
}
# Print the `CREATE` line as is and capture the table name.
/^CREATE/ {
print
if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 )
}
# Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
/^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }

# Get rid of field lengths in KEY lines
/ KEY/ { gsub(/\([0-9]+\)/, "") }
# Print all fields definition lines except the `KEY` lines.
/^  / && !/^(  KEY|\);)/ {
gsub( /AUTO_INCREMENT|auto_increment/, "" )
gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
gsub( /(COLLATE|collate) [^ ]+ /, "" )
gsub(/(ENUM|enum)[^)]+\)/, "text ")
gsub(/(SET|set)\([^)]+\)/, "text ")
gsub(/UNSIGNED|unsigned/, "")
if (prev) print prev ","
prev = $1
}
# `KEY` lines are extracted from the `CREATE` block and stored in array for later print
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to
# avoid a sqlite error for duplicate index name.
/^(  KEY|\);)/ {
if (prev) print prev
prev=""
if ($0 == ");"){
print
} else {
if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 )
if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 )
key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
}
}
# Print all `KEY` creation lines.
END {
for (table in key) printf key[table]
print "END TRANSACTION;"
}
'
exit 0
View Code 　　2、创建convertDB.sh来调用mysql2sqlite.sh：





rm -rf sqlite_db_name
sh mysql2sqlite.sh -umysql_user_name -pmysql_user_pwd -hmysql_host mysql_db_name | sqlite3 sqlite_db_name
View Code
修改相应的参数：
　　sqlite_db_name （要转换成的sqlite数据库名称）

mysql_user_name （mysql用户名称）
mysql_user_pwd （mysql用户密码）
mysql_host （mysql服务器ip地址）
mysql_db_name（要转换的mysql数据库名称）
　　3、在Unity3d中执行shell脚本（放在Editor文件夹下）：





using UnityEngine;
using System.Collections;
using UnityEditor;
using System.IO;
using System.Diagnostics;
public class ChangeDB  {
static string sqliteDBpath = Application.streamingAssetsPath + "/dbFile/";
static string sqliteDBname = "sqlite_db_name.db";
[MenuItem("Tools/Mysql to Sqlite")]
public static void mysqlToSqlite ()
{
if(File.Exists(sqliteDBpath)){
UnityEngine.Debug.Log("delete old sqlite db file...");
File.Delete(sqliteDBpath+sqliteDBname);
}
if(File.Exists(sqliteDBpath+sqliteDBname+".meta")){
File.Delete(sqliteDBpath+sqliteDBname+".meta");
}        
//start to convert mysql to sql
UnityEngine.Debug.Log("start convert...");
ProcessStartInfo proc = new ProcessStartInfo();
proc.WorkingDirectory = sqliteDBpath;
proc.FileName = "sh";
proc.Arguments = "convertDB.sh";
proc.UseShellExecute = false;
proc.Verb = "";        
Process.Start(proc);
}
}
View Code　　4、点击Tools下的 Mysql to Sqlite 就可以转换了
　　5、注意在iOS下可以将sqlite数据库放在 Application.streamingAssetsPath 目录下直接访问
　　在Android下访问Application.streamingAssetsPath下的文件必须先调用下面的方法解压一下：





string sqlitedbPath = Application.streamingAssetsPath + "/dbFile/dbname.db";
string filepath = Application.persistentDataPath + "/dbname.db";
IEnumerator LoadSqliteDBForAndroid (string url,string newPath){
using(WWW www = new WWW(url)){
yieldreturn www;
if (www.error != null)
Debug.LogError("WWW download:" + www.error);
File.WriteAllBytes(newPath, www.bytes);

LoadAllResource();

Debug.Log("android db download finished");
}
}
View Code 　　
　　参考资料：
　　1、https://gist.github.com/esperlu/943776
　　2、http://docs.unity3d.com/Documentation/ScriptReference/Application-streamingAssetsPath.html