unity -- asset配置 & SQLite数据库

将可序列化的类的数据保存为asset资源

摘自：https://jingyan.baidu.com/article/a3f121e4abbe6afc9052bb29.html

• .asset：

1）优点：
可以保存数据类型多样（int、string、Vector3、GameObject、Transform、Texture等）如关联预设，关联图片等资源数据，而XML、TXT等只能保存（int、string、Vector3等基本数据类型）。
2）缺点：
如果配置数据中保存了（GameObject、Texture）等资源数据，当关联的资源被删除时，配置数据将丢失，需要重新将新的资源再次关联到配置数据上

• ScriptableObject优点：

1）把数据真正存储在了资源文件中，可以像其他资源那样管理它，例如退出运行也一样会保持修改
2）可以在项目之间很好的复用，不用再制作Prefab那样导入导出
3）在概念上有很好的fit，强迫症患者的福音

public class Config : ScriptableObject
{
    public ConfigItem[] Configs;
}

[Serializable]
public class ConfigItem
{
    [Column(Name = "ID", DataType = "int32", DefaultValue = "0", IsPrimaryKey = true)]
    public int Id;
    [Column(Name = "Position", DataType = "float", DefaultValue = "0")]
    public Vector3 Pos;
}

//转存到Excel时使用
[AttributeUsage(AttributeTargets.Property | AttributeTargets.Field, AllowMultiple = false, Inherited = false)]
public sealed class ColumnAttribute : Attribute
{
    public string Name { get; set; }
    public string DataType {get; set; }
    public string DefaultValue { get; set; }
    public bool IsPrimaryKey { get; set; }

    public ColumnAttribute()
    {
        IsPrimaryKey = false;
    }
}

    public static Config CreateAsset()
    {
        //实例化配置类
        Config instance = ScriptableObject.CreateInstance();
		instance.Configs = new ConfigItem[0];
        //生成自定义资源到自定路径
        var savepath = string.Format("Assets/_Res/Config/{0}.asset",typeof(Config).ToString());
        AssetDatabase.CreateAsset(instance, savepath);
    }

在保存路径中打开双击配置文件

在Inspector窗口中更改配置数据

或代码更改

        var configInstance = AssetDatabase.LoadAssetAtPath(savepath);
        List configList = new List();
        var item = new ConfigItem()
        {
            Id = 1,
            Pos = new Vector3(1,1,1)
        };
        configList.Add(item);
        
        configInstance.Configs = null;
        configInstance.Configs = configList.ToArray();
        EditorUtility.SetDirty(configInstance);
        AssetDatabase.SaveAssets();

	SQLite3.Open(databasePathAsBytes, out handle, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite, IntPtr.Zero);//与SQLite数据库建立连接
	
	
	var sql = "PRAGMA temp_store = MEMORY"  //SQLite编译指令：临时表和索引存放于内存中
    
    
	

	var fields = ConfigItem.GetFields(BindingFlags.Instance | BindingFlags.Public);//获取数据的属性列表
	var colList = new List();//组装建表SQL时用
	foreach (var field in fields)
    {
        var col = field.GetCustomAttribute(false);  //每个属性的字段信息
        colList.add(col);
    }
    string sql = $"CREATE TABLE 'asset_config' (n'{colList[i].Name}' {colList[i].DataType} NOT NULL DEFAULT '0',n"....;//建表SQL
    
    

	var configData = AssetDatabase.LoadAssetAtPath(savepath);  //读取配置文件
	var datas = configData.Configs;
	List rowCellList = new List();
	foreach (var item in datas)//每条配置
    {
    	rowCellList.Clear();
    	foreach (var field in fields)//每条属性
    	{
    		var col = field.GetCustomAttribute(false);  //每个属性的字段信息
    		//将属性值转为string类型->cell
    		rowCellList.Add(cell);
    	}
    	 var rowValue = string.Join(",", valueList);
    	 var Names = string.Join(",", colList.Select(col => col.ColName));
    	 string sql = $"INSERT INTO asset_config ({Names}) VALUES({rowValue})"
    	 
    }
	

	SQLite3.Close(handle);//与数据库断开连接
	AssetDatabase.Refresh();

	
	//遍历表名
	var sql = "select name from sqlite_master where type='table'";
	IntPtr stmt = SQLite3.Prepare2(handle, sql);
    var names = new List();  //表名列表
    try
    {
        while (SQLite3.Step(stmt) == SQLite3.Result.Row)
        {
            names.Add(SQLite3.ColumnString(stmt, 0));
        }
    }
    finally
    {
        SQLite3.Finalize(stmt);
    }
	
	//根据表名删除指定表
	for (int i = 0, n = names.Count; i < n; i++)
    {
        var name = names[i];
        var sql = $"drop table if exists {name}";
        
    }

	
    IntPtr stmt = SQLite3.Prepare2(handle, sql);
    try
    {
        var step = SQLite3.Step(stmt);
        if (step != SQLite3.Result.Done)
        {
            var error = SQLite3.GetErrmsg(handle);
            throw new InvalidOperationException($"{sql} error-{error}--step:{step}");
        }
    }
    finally
    {
        SQLite3.Finalize(stmt);
    }

//ConfigItem对应的配置项数据类
public class asset_config
{
    public int id;
    public float pos_x;
    public float pos_y;
    public float pos_z;
}

//配置数据缓存
public class AssetConfig
{
	private Dictionary _configMap;
	
	public void ReadFromDb(IntPtr stmt)
    {
    	SQLite3.Open(databasePathAsBytes, out handle, (int) openFlags, IntPtr.Zero);
    	
    	var sql = $"select * from asset_config";
    	var stmt = SQLite3.Prepare2(handle, sql);
    	try
        {
            while (SQLite3.Step(stmt) == SQLite3.Result.Row)
            {
                var obj = Activator.CreateInstance(asset_config);
                obj.id = SQLite3.ColumnInt(stmt, 0);
        		obj.pos_x = (float) SQLite3.ColumnDouble(stmt, 1);
        		obj.pos_y = (float) SQLite3.ColumnDouble(stmt, 2);
        		obj.pos_z = (float) SQLite3.ColumnDouble(stmt, 3);
                _configMap.Add(obj.id, obj);
            }
        }
        finally
        {
            SQLite3.Finalize(stmt);
        }
    }

	public Vector3 GetPos(int id)
	{
		if (_configMap.TryGetValue(id, out asset_config cfg))
        {
            return new Vector3(cfg.pos_x, cfg.pos_y, cfg.pos_z);
        }
	}
}