Unity WebGL Sqlite3替代方案iBoxDB

1.iBoxDB 简介

http://www.iboxdb.com/

2.选择原因

sqlite3不支持WebGL (更改业务逻辑,存到后端工作量有点大,只能更改前端存储,webgl用iboxDB,其它平台用sqlite3)

iboxDB :JAVA   C#   Android   Unity   Xamarin   Mono   Nashorn   Linux   Windows
安装简单,unity只需要一个dll即可。

3.简单用法

百度/谷歌也能搜索到一些例子,大都是封装了一个Helper类,我简单补充一些用法。

1.注意变量必须用  ?替代,防止sql注入。
Select("from Email where category == ? & type == ? order by created_at desc, id desc",1,1);

2.字段数据类型,必须一致。
Class{
int a;
long b;
}
Select("from Email where a == ? & b == ?",1,(long)1); //需要传long型

3.mysql in 语句。这个语法数据库不支持,可以翻译成条件判断。
例如: 
mysql :where category = 1 AND type in (1,2,3,4) order by create_at desc, id desc limit 0, 10
iBoxDb : 
var types = new int[]{1,2,3,4};
可以写个转化函数:var Sql = from Email where category == ? & MessageTypesStrings(type.Length) order by create_at desc, id desc limit 0, 10
转化后就是: from Email where category == ? & (type == ? | type == ? | type == ? | type == ?) order by create_at desc, id desc limit 0, 10
private string MessageTypesToStrings(int length){
		var result = "";
		if (length == 0)
			return result;
		if (length > 1) {
			for (var i = 0; i < length - 1; i++) {
				result += "type==?" + "|";
			}
		}
		result += "type==?";
		return "(" + result + ")";
	}
多参数问题:
Select(Sql,1,types); 数组这么传参不对,可以转化成 5个参数。
Select(Sql,ConvertParams(1,types)); //ok
private object[] ConvertParams(params object[] parameters){
		var list = new List<object> ();
		foreach (var item in parameters) {
			if (item.GetType () == typeof(int[])) {
				var items = item as int[];
				foreach (var item1 in items) {
					list.Add ((int)item1);
				}
			} else if (item.GetType () == typeof(long[])) {
				var items = item as long[];
				foreach (var item1 in items) {
					list.Add ((long)item1);
				}
			} else {
				list.Add (item);
			}
		}
		return list.ToArray ();
	}


4.group by 语法不支持
自己取出数据,然后用linq语句进行筛选。
var sums = from n in QueryData
           group n by new{n.category,n.type} into g
	   select new {Total = g.Count ()};

from n in QueryData
	group n by new {n.category,n.type} 
	into g
	select g.OrderByDescending (t => t.id).ThenByDescending (t => t.created_at).FirstOrDefault ()
大多数运算 都可以Select DataBase 然后进行 linq逻辑操作。

5.逻辑操作符
Select("from Email where (type == ?) & (reward_time > ? | life_time-?<=?)",1,1,2,1); //支持运算

4.我的helper类

public class iBoxDBHelper  {

	private static iBoxDBHelper _ins;
	public static iBoxDBHelper ins{
		get{
			if (null == _ins) {
				_ins = new iBoxDBHelper ();
			}
			return _ins;
		}
	}

	public static string boxDBEmail = "Email";

	private string DBBoxDir{
		get{  
			return Application.persistentDataPath;
		}
	}

	private AutoBox m_autoBox = null;
	private DB m_db;
	public AutoBox Box{
		get{  
			return m_autoBox;
		}
	}
	public void InitDB(){
		CreateDirectory ();
		if (null == m_autoBox) {
			DB.Root (DBBoxDir);
			m_db = new DB (3);//3=自定义数字,在这没有具体意义。

			m_db.GetConfig ().EnsureTable<DBEmail> (boxDBEmail, "id", "category");
			m_db.GetConfig ().EnsureIndex<DBEmail> (boxDBEmail, "created_at", "read_at");

			m_autoBox = m_db.Open();
		}
	}
	public void ChangeDB(){
		m_db.Dispose ();
		m_autoBox = null;
		InitDB ();
	}

	private void CreateDirectory () {
		if(!Directory.Exists (DBBoxDir)) {
		Directory.CreateDirectory (DBBoxDir);
		}
	}

	public bool Insert(string tableName, DBEmail data){
		return _ins.m_autoBox.Insert (tableName, data);
	}
	public void InsertMulti(string tableName, List<DBEmail> data){
		using(var box = _ins.m_autoBox.Cube())
		{
			Binder binder = box.Bind(tableName);
			foreach (var item in data) {
				binder.Insert (item);
			}
			box.Commit ();
		}
	}

	public void Update(string tableName, object data){
		_ins.m_autoBox.Update (tableName, data);
	}
	public void UpdateMulti(string tableName, List<object> data){
		using(var box = _ins.m_autoBox.Cube())
		{
			Binder binder = box.Bind(tableName);
			foreach (var item in data) {
				binder.Update (item);
			}
			box.Commit();
		}
	}

	public DBEmail GetOne(string sql,params object[] param){
		var dbEmail = new DBEmail ();
		var datas = _ins.m_autoBox.Select <DBEmail> (sql, param);
		foreach (var item in datas) {
			dbEmail = item;
		}
		return dbEmail;
	}

	public void Delete(string tableName,string QL){
		_ins.m_autoBox.Delete (tableName, QL);
	}
}
相关文章
相关标签/搜索