iBoxDB—Unity WebGL Sqlite3替代方案

2022-04-13 15:12:25

1.iBoxDB 简介
http://www.iboxdb.com/

2.选择原因
Sqlite3不支持WebGL (选择方案：WebGL平台用iboxDB，其它平台用Sqlite3)

iboxDB 支持：JAVA C# Android Unity Xamarin Mono Nashorn Linux Windows

安装简单，Unity只需要一个dll即可。

Web需要主动调用持久化(例如5分钟调用一次保存，或者当某个页面关闭时保存等等，根据开发者自定义，如果不保存，刷新浏览器 iboxdb 将没有保存数据)。

3.简单用法

注意变量必须用？替代，防止sql注入。

mysql ：where category = 1 AND type in (1,2,3,4) order by create_at desc, id desc limit 0, 10
iBoxDb : 
var * = new int[]{1,2,3,4};
 
可以写个转化函数：var Sql = from Email where category == ? & Message*Strings(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 Message*ToStrings(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,*); 数组这么传参不对，可以转化成 5个参数。
 
Select(Sql,ConvertParams(1,*)); //[1,1,2,3,4]
 
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 ();
}

group by 语法不支持

自己取出数据，然后用linq语句进行筛选。

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);
	}
}