/// <summary>
/// 初始化所有的表并创建没有的表
/// </summary>
/// <returns></returns>
internal bool InitAllTables()
{
Open();
try
{
Type[] ts = this.GetType().Assembly.GetTypes();
List <Type> types = ts.Where(t => t.BaseType != null && t.BaseType.Namespace.Equals(assembly) && t.BaseType.Name.Equals(typeof(SqliteDataTableBase).Name) && t.IsClass)
.Select(tt => tt).ToList();
foreach (var t in types)
{
string tablename = SqliteTabelCmdStrTool.GetTableName(t);
if (!CheckTable(tablename))
{
string cmdStr = SqliteTabelCmdStrTool.GetCreateTableCmdStr(t, true, true);
OperateRecords(cmdStr);
}
}
return(true);
}
catch (SqliteException e)
{
LogOperator.AddWarnningRecord("检查所有的表并创建没有的表时异常", e.Message);
return(false);
}
}
/// <summary>
/// 数据库操作
/// </summary>
/// <param name="dbFullName"></param>
public TableOperator(string dbFullName)
{
try
{
dbFullName = dbFullName.ToLower().EndsWith(".db") ? dbFullName : string.Format("{0}.db", dbFullName);
string[] sp = dbFullName.Split(',');
this.assembly = sp.Length >= 2 ? sp[0] : this.GetType().Namespace;
dbFullName = string.Format("{0}/{1}", Application.persistentDataPath, dbFullName);
scsb = new SqliteConnectionStringBuilder();
scsb.DataSource = dbFullName;
if (!File.Exists(dbFullName))
{
CreateDB(dbFullName);
Debug.LogFormat("创建数据库：{0}", dbFullName);
}
else
{
// 初始化所有的表，并创建没有的表
InitAllTables();
}
}
catch (SqliteException e)
{
connection = null;
LogOperator.AddWarnningRecord("创建数据库时有异常", e.Message);
}
}
/// <summary>
/// 更新记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ts"></param>
/// <param name="args">存的是一个实例中的字段名与值，key为字段名字，value的List是实例的字段名字、类型和值</param>
/// <returns></returns>
internal bool Update <T>(List <T> ts, Dictionary <string, List <object> > args = null) where T : SqliteDataTableBase
{
Open();
bool hasIdPk = HasIdPK <T>();
Dictionary <string, string> argsStr = args != null?args.Select(arg => new { key = arg.Key, value = SqliteTabelCmdStrTool.GetFieldValue(arg.Value[0].ToString(), arg.Value[1] as Type, arg.Value[2]) }).ToDictionary(p => p.key, p => p.value) : null;
string cmdStr = SqliteTabelCmdStrTool.GetUpdateStr(ts, hasIdPk, argsStr);
SqliteTransaction st = connection.BeginTransaction();
try
{
int res = 0;
lock (_dbLocker)
{
res = OperateRecords(cmdStr);
if (res > 0)
{
st.Commit();
}
}
return(res > 0);
}
catch (SqliteException e)
{
st.Rollback();
LogOperator.AddWarnningRecord("更新记录时异常", e.Message);
return(false);
}
finally
{
Close();
}
}
/// <summary>
/// 删除记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ts"></param>
/// <returns></returns>
internal bool Delete <T>(List <T> ts) where T : SqliteDataTableBase
{
Open();
bool hasIdPk = HasIdPK <T>();
string cmdStr = SqliteTabelCmdStrTool.GetDeleteStr(ts, hasIdPk);
SqliteTransaction st = connection.BeginTransaction();
try
{
int res = 0;
lock (_dbLocker)
{
res = OperateRecords(cmdStr);
if (res > 0)
{
st.Commit();
}
}
return(res > 0);
}
catch (SqliteException e)
{
st.Rollback();
LogOperator.AddWarnningRecord("删除记录时异常", e.Message);
return(false);
}
finally
{
Close();
}
}
/// <summary>
/// 插入数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ts"></param>
/// <returns></returns>
internal bool Insert <T>(List <T> ts) where T : SqliteDataTableBase
{
Open();
bool hasIdIncre = HasAutoIncrementID <T>();
string cmdStr = SqliteTabelCmdStrTool.GetInsertStr(ts, hasIdIncre);
Debug.Log(cmdStr);
SqliteTransaction st = connection.BeginTransaction();
try
{
int res = 0;
lock (_dbLocker)
{
res = OperateRecords(cmdStr);
if (res > 0)
{
st.Commit();
}
}
return(res > 0);
}
catch (SqliteException e)
{
st.Rollback();
LogOperator.AddWarnningRecord("插入数据时异常", e.Message);
return(false);
}
finally
{
Close();
}
}
/// <summary>
/// 结构是否存在自增长字段id
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
internal bool HasAutoIncrementID(Type t)
{
try
{
string cmdStr = SqliteTabelCmdStrTool.GetAutoIncrement(t);
DataTable dt = OperateQuery(cmdStr);
return(dt.Rows[0][0].ToString().Contains("INTEGER PRIMARY KEY AUTOINCREMENT"));
}
catch (SqliteException e)
{
LogOperator.AddWarnningRecord("执行查询结构是否存在自增长字段id时异常", e.Message);
return(false);
}
}
/// <summary>
/// 关闭数据库链接
/// </summary>
/// <returns></returns>
internal bool Close()
{
try
{
if (connection != null && connection.State != ConnectionState.Closed)
{
connection.Close();
}
return(true);
}
catch (SqliteException e)
{
LogOperator.AddWarnningRecord("关闭数据库时有异常", e.Message);
return(false);
}
}
/// <summary>
/// 执行sql非查询语句
/// </summary>
/// <param name="cmdStr"></param>
/// <returns></returns>
internal int OperateRecords(string cmdStr)
{
try
{
int res = 0;
using (SqliteCommand cmd = new SqliteCommand(cmdStr, connection))
{
res = cmd.ExecuteNonQuery();
}
return(res);
}
catch (SqliteException e)
{
LogOperator.AddWarnningRecord("执行非查询语句时异常", e.Message);
return(0);
}
}
/// <summary>
/// 执行查询命令
/// </summary>
/// <param name="cmdStr"></param>
/// <returns></returns>
internal DataTable OperateQuery(string cmdStr)
{
try
{
DataTable data = new DataTable();
using (SqliteCommand cmd = new SqliteCommand(cmdStr, connection))
{
SqliteDataAdapter adapter = new SqliteDataAdapter(cmd);
adapter.Fill(data);
}
return(data);
}
catch (SqliteException e)
{
LogOperator.AddWarnningRecord("执行查询命令出现异常", e.Message);
return(null);
}
}
/// <summary>
/// 检查表是否存在
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tablename"></param>
/// <returns></returns>
internal bool CheckTable(string tablename)
{
try
{
int res = 0;
lock (_dbLocker)
{
string cmdStr = SqliteTabelCmdStrTool.GetCheckNameExist(tablename);
DataTable data = OperateQuery(cmdStr);
res = int.Parse(data.Rows[0][0].ToString());
}
return(res == 1);
}
catch (SqliteException e)
{
LogOperator.AddWarnningRecord("检查表存在出现异常", e.Message);
return(false);
}
}
/// <summary>
/// 是否有主键id
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
internal bool HasIdPK(Type t)
{
try
{
string cmdStr = SqliteTabelCmdStrTool.GetAutoIncrement(t);
DataTable dt = OperateQuery(cmdStr);
if (dt.Rows.Count > 0 && dt.Columns.Count > 0)
{
return(dt.Rows[0][0].ToString().ToUpper().Contains("ID INTEGER PRIMARY KEY"));
}
{
return(false);
}
}
catch (SqliteException e)
{
LogOperator.AddWarnningRecord("执行查询结构是否存在自增长字段id时异常", e.Message);
return(false);
}
}
/// <summary>
/// 链接数据库
/// </summary>
/// <returns></returns>
internal bool Open()
{
try
{
if (connection != null && connection.State != ConnectionState.Open)
{
connection.Open();
}
else
{
connection = new SqliteConnection(scsb.ToString());
connection.Open();
}
return(true);
}
catch (Exception e)
{
LogOperator.AddWarnningRecord("链接数据库时有异常", e.Message);
return(false);
}
}
/// <summary>
/// 获取UI物体并返回UI逻辑代码，一个UI物体只能打开一次，再打开时就返回已经存在的UI物体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="uIData"></param>
/// <param name="level"></param>
/// <param name="assetName"></param>
/// <param name="abName"></param>
/// <returns></returns>
private T OpenUI <T>(IUIData uIData = null, UILevel level = UILevel.Common, string assetName = null, string abName = null) where T : UIPanel
{
string prefabName = typeof(T).Name;
T t = null;
if (uiGos.ContainsKey(prefabName))
{
LogOperator.AddWarnningRecord(string.Format("UI名字为 {0} 的物体已经存在，请不要重复创建！", prefabName));
return(null);
}
GameObject prefab = null;
if (string.IsNullOrEmpty(assetName))
{
assetName = prefabName;
}
if (string.IsNullOrEmpty(abName))
{
abName = prefabName;
}
if (!uiPrefabs.ContainsKey(prefabName))
{
string uiPrefabPath = null;
string persPath = null;
#if UNITY_EDITOR
uiPrefabPath = string.Format("{0}/{1}/{2}/{3}.ab", Application.streamingAssetsPath, ConfigContent.configPath.AssetbundlePath, ConfigContent.CurrPlatform, abName);
persPath = string.Format("{0}/{1}/{2}/{3}.ab", Application.persistentDataPath, ConfigContent.configPath.AssetbundlePath, ConfigContent.CurrPlatform, abName);
// 直接加载Prefab
bool useFromPrefab = EditorPrefs.GetBool(ConfigNameForPlayerPref.EDITOR_UI_USE_FROM_PREFAB, false);
if (useFromPrefab)
{
// 直接加载Prefab
uiPrefabPath = string.Format("Assets/{0}/{1}.prefab", ConfigContent.configPath.UIPrePath, abName);
prefab = AssetDatabase.LoadAssetAtPath <GameObject>(uiPrefabPath);
}
else
{
if (File.Exists(persPath))
{
// 保证使用最新UI资源
uiPrefabPath = persPath;
}
Debug.Log(uiPrefabPath);
AssetBundle ab = AssetBundle.LoadFromFile(uiPrefabPath);
prefab = ab.LoadAsset <GameObject>(assetName);
ab.Unload(false);
}
#elif UNITY_EDITOR_WIN
uiPrefabPath = string.Format("{0}/Assetbundles/{1}/{2}.ab", Application.streamingAssetsPath, ConfigContent.CurrPlatform, abName.ToLower());
persPath = string.Format("{0}/Assetbundles/{1}/{2}.ab", Application.persistentDataPath, ConfigContent.CurrPlatform, abName.ToLower());
if (File.Exists(persPath))
{
// 保证使用最新UI资源
uiPrefabPath = persPath;
}
AssetBundle ab = AssetBundle.LoadFromFile(uiPrefabPath);
prefab = ab.LoadAsset <GameObject>(assetName);
ab.Unload(false);
#elif UNITY_ANDROID
// 先查看 Application.persistentDataPath 下面有没有ab包，有的话就先使用 Application.persistentDataPath 下面的，
// 这样就保证使用最新更改过的原UI资源和打包进ab包里面的原最新UI脚本，如果是新添加的资源就不行了，就需要更新主程序
uiPrefabPath = string.Format("{0}/Assetbundles/{1}/{2}.ab", Application.streamingAssetsPath, ConfigContent.CurrPlatform, abName.ToLower());
persPath = string.Format("{0}/Assetbundles/{1}/{2}.ab", Application.persistentDataPath, ConfigContent.CurrPlatform, abName.ToLower());
if (File.Exists(persPath))
{
// 保证使用最新UI资源
uiPrefabPath = persPath;
}
AssetBundle ab = AssetBundle.LoadFromFile(uiPrefabPath);
prefab = ab.LoadAsset <GameObject>(assetName);
ab.Unload(false);
#elif UNITY_IPHONE
uiPrefabPath = string.Format("{0}/Assetbundles/{1}/{2}.ab", Application.streamingAssetsPath, ConfigContent.CurrPlatform, abName.ToLower());
persPath = string.Format("{0}/Assetbundles/{1}/{2}.ab", Application.persistentDataPath, ConfigContent.CurrPlatform, abName);
if (File.Exists(persPath))
{
// 保证使用最新UI资源
uiPrefabPath = persPath;
}
AssetBundle ab = AssetBundle.LoadFromFile(uiPrefabPath);
prefab = ab.LoadAsset <GameObject>(assetName);
ab.Unload(false);
#endif
uiPrefabs.Add(prefabName, prefab);
Debug.Log(">>>Open UI: " + uiPrefabPath);
}
else
{
prefab = uiPrefabs[prefabName];
Debug.Log(">>>Open UI: " + prefabName);
}
GameObject go = Instantiate(prefab, GetLevelTransform(level));
uiGos.Add(prefabName, go);
go.name = abName;
t = go.GetComponent <T>();
t.OnOpen(uIData);
return(t);
}
/// <summary>
/// 条件查询，默认查询所有
/// 查询条件结构解释：key为字段名字，value的List是实例的字段名字、类型和值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="args">存的是一个实例中的字段名与值，key为字段名字，value的List是实例的字段名字、类型和值</param>
/// <returns></returns>
internal List <T> Select <T>(Dictionary <string, List <object> > args = null) where T : SqliteDataTableBase, new()
{
Open();
Dictionary <string, string> argsStr = (args == null || args.Count == 0) ?
null : args.Select(arg => new { key = arg.Key, value = SqliteTabelCmdStrTool.GetFieldValue(arg.Value[0].ToString(), arg.Value[1] as Type, arg.Value[2]) })
.ToDictionary(p => p.key, p => p.value);
string cmdStr = SqliteTabelCmdStrTool.GetSelectStr <T>(argsStr);
SqliteTransaction st = connection.BeginTransaction();
try
{
List <T> ts = new List <T>();
lock (_dbLocker)
{
DataTable dt = OperateQuery(cmdStr);
if (dt.Rows.Count > 0)
{
Dictionary <string, FieldInfo> fNameType = new Dictionary <string, FieldInfo>();
FieldInfo[] fis = typeof(T).GetFields();
foreach (var fi in fis)
{
fNameType.Add(fi.Name, fi);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
T t = new T();
for (int j = 0; j < dt.Columns.Count; j++)
{
string cName = dt.Columns[j].ColumnName;
Type type = dt.Columns[j].DataType;
if (type == typeof(Int64) || type == typeof(Int32))
{
var v = dt.Rows[i][j];
fNameType[cName].SetValue(t, Convert.ToInt32(v));
}
else if (type == typeof(String))
{
var value = dt.Rows[i][j];
fNameType[cName].SetValue(t, value == null ? null : value.ToString());
}
else if (type == typeof(Single))
{
fNameType[cName].SetValue(t, Convert.ToSingle(dt.Rows[i][j]));
}
else if (type == typeof(Boolean))
{
fNameType[cName].SetValue(t, Convert.ToBoolean(dt.Rows[i][j]));
}
else if (type == typeof(Double))
{
fNameType[cName].SetValue(t, Convert.ToDouble(dt.Rows[i][j]));
}
else
{
// 找不到的就直接赋值
fNameType[cName].SetValue(t, dt.Rows[i][j]);
}
}
ts.Add(t);
}
return(ts);
}
else
{
return(null);
}
}
}
catch (SqliteException e)
{
st.Rollback();
LogOperator.AddWarnningRecord("查询记录时异常", e.Message);
return(null);
}
finally
{
Close();
}
}
