etoy-xgame/etoy/Pileline/Cmd/CmdExportSqliteDatabase.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace etoy
{
    internal class CmdExportSqliteDatabase : Command
    {
        protected virtual TableTag AllowedTableTag => TableTag.Client;
        protected virtual KeyValueTag AllowedKeyValueTag => KeyValueTag.Client;
        protected virtual string DatabaseOutput => Context.Option.ClientDatabaseOutput;
        public override string Description => "生成Sqlite DB文件";

        private readonly List<Exception> _errors = new List<Exception>();

        protected override void OnProcess()
        {
            if (File.Exists(DatabaseOutput))
                File.Delete(DatabaseOutput);

            SetProgress(0.1f);

            string file = DatabaseOutput.ToPath();
            if (File.Exists(file))
                File.Delete(file);

            var database = new Database(file);

            SetProgress(0.5f);

            ExportDatabase(database);

            database.Dispose();

            if (_errors.Count > 0)
            {
                SetException(new Exception(string.Join('\n', (from e in _errors select $"## ERROR: {e.Message}").ToArray())));
            }
            else
            {
                Completed();
            }
        }

        private void ExportDatabase(Database database)
        {
            database.Exec("VACUUM");
            database.Exec("PRAGMA synchronous = OFF");


            List<string> sqls = new List<string>();

            // Normal Table Create Sql
            GetCreateSqls(sqls);
            // Normal Table Insert Sql
            GetInsertSqls(sqls);
            // KeyValue Table Sql
            GetKeyValueSqls(sqls);
            // Version Table Sql
            GetVersionSqls(sqls);

            database.Exec("BEGIN TRANSACTION");

            foreach (var sql in sqls)
            {
                try
                {
                    database.Exec(sql);
                }
                catch (Exception e)
                {
                    _errors.Add(e);
                }
            }

            database.Exec("END TRANSACTION");
        }

        private void GetCreateSqls(List<string> cmds)
        {
            foreach (var table in Context.Blackboard.Tables)
            {
                if (HasTableFlag(table.TableTag))
                {
                    var sb = new StringBuilder();
                    for (int i = 0, length = table.FieldInfos.Length; i < length; i++)
                    {
                        var fieldInfo = table.FieldInfos[i];
                        if (HasTableFlag(fieldInfo.OutputTag))
                        {
                            if (i > 0)
                                sb.Append(",");
                            string fieldType = GetSqliteType(fieldInfo.FieldType, fieldInfo.IsRepeated);
                            string primaryKey = fieldInfo.IsPrimaryKey ? "PRIMARY KEY" : string.Empty;
                            sb.Append($"\'{fieldInfo.FieldName}\' {fieldType} {primaryKey}".Trim());
                        }
                    }

                    string createSql = $"CREATE TABLE IF NOT EXISTS {table.Name}({sb});";
                    cmds.Add(createSql);
                }
            }
        }

        private void GetInsertSqls(List<string> cmds)
        {
            var sb = new StringBuilder();
            foreach (var table in Context.Blackboard.Tables)
            {
                if (HasTableFlag(table.TableTag))
                {
                    for (int i = 0, rowCount = table.Rows.Count; i < rowCount; i++)
                    {
                        var row = table.Rows[i];

                        sb.Clear();
                        sb.Append($"INSERT INTO {table.Name} VALUES");
                        sb.Append("(");
                        for (int j = 0, colCount = row.Cells.Count; j < colCount; j++)
                        {
                            var cell = row.Cells[j];
                            if (HasTableFlag(cell.FieldInfo.OutputTag))
                            {
                                if (j > 0)
                                    sb.Append(",");

                                // Call ToSqlString in need
                                string sqlString = cell.Value.ToSqlString();

                                if (Context.Blackboard.ContainsMetadataType(cell.FieldInfo.FieldType))
                                {
                                    sb.Append($"'{sqlString}'");
                                }
                                else
                                {
                                    if (cell.FieldInfo.IsRepeated || cell.FieldInfo.FieldType == FieldTypeDefine.String)
                                        sb.Append($"'{sqlString.Replace("\\n", "\n")}'");
                                    else
                                        sb.Append($"{sqlString}");
                                }
                            }
                        }

                        sb.Append(");\n");
                        cmds.Add(sb.ToString());
                    }
                }
            }
        }

        private void GetKeyValueSqls(List<string> cmds)
        {
            foreach (var table in Context.Blackboard.KeyValueTables)
            {
                string createSql = $"CREATE TABLE IF NOT EXISTS {table.Name}(key varchar PRIMARY KEY, value varchar);";
                cmds.Add(createSql);

                foreach (var row in table.Rows)
                {
                    if (!HasKeyValueFlag(row.OutputTag))
                        continue;

                    string insertSql = $"INSERT INTO {table.Name} VALUES('{row.Key.ToSqlString()}', '{row.Value.ToSqlString()}');";
                    cmds.Add(insertSql);
                }
            }
        }

        private void GetVersionSqls(List<string> cmds)
        {
            string versionTableName = "version";
            string createSql = $"CREATE TABLE IF NOT EXISTS {versionTableName}(v integer PRIMARY KEY);";
            string insertSql = $"INSERT INTO {versionTableName} VALUES('{Context.Blackboard.Version.ToSqlString()}');";

            cmds.Add(createSql);
            cmds.Add(insertSql);
        }

        private string GetSqliteType(string fieldType, bool repeated)
        {
            if (repeated)
            {
                return SqliteTypeDefine.Varchar;
            }

            if (fieldType == FieldTypeDefine.Int ||
                fieldType == FieldTypeDefine.Boolean)
            {
                return SqliteTypeDefine.Integer;
            }

            if (fieldType == FieldTypeDefine.Long)
            {
                return SqliteTypeDefine.Integer64;
            }

            if (fieldType == FieldTypeDefine.String)
            {
                return SqliteTypeDefine.Varchar;
            }

            if (fieldType == FieldTypeDefine.Float || fieldType == FieldTypeDefine.Double)
            {
                return SqliteTypeDefine.Float;
            }

            return SqliteTypeDefine.Varchar;
        }

        private bool HasTableFlag(TableTag tag)
        {
            return AllowedTableTag == TableTag.All || tag.HasFlag(AllowedTableTag);
        }
        private bool HasKeyValueFlag(KeyValueTag tag)
        {
            return AllowedKeyValueTag == KeyValueTag.All || tag.HasFlag(AllowedKeyValueTag);
        }
    }
}