tshockquerycreator.cs

namespace MyPlugin.Db
{
    public class MyPluginDbManager
    {
        private string _tablePrefix = "plugin";
        private string _tableName = "Test";
        private readonly IDbConnection _conn;

        internal string TableName
        {
            get 
            {
                return String.Concat(_tablePrefix, _tableName);
            } 
        }

        private IQueryBuilder DbProvider
        {
            get
            {
                return _conn.GetSqlType() == SqlType.Mysql 
                    ? (IQueryBuilder)new MysqlQueryCreator()
                    : (IQueryBuilder)new SqliteQueryCreator();
            }
        }

        public MyPluginDbManager(IDbConnection conn)
        {
            _conn = conn;

            /**
             * Style 1
             *
             * Creates a table that has:
             *      A composite primary key with `id` and `someName`
             *      A foreign key constraint on `id` that references Users.ID
             *      Two indexes:
             *          `someNumber` -- a basic index
             *          `someName` -- a more specific index with an index length and a unique constraint 
             */
            var table1 = new SqlTable(TableName,
                new SqlColumn("id", MySqlDbType.Int32) { Primary = true },
                new SqlColumn("someNumber", MySqlDbType.Int32),
                new SqlColumn("anotherNumber", MySqlDbType.Int64),
                new SqlColumn("someName", MySqlDbType.VarChar) { Primary = true, Length = 16 })
                .AddForeignKey(new SqlForeignKey("id", "Users", "ID", SqlForeignKey.Action.Cascade, SqlForeignKey.Action.Cascade))
                .AddIndex(new SqlIndex("idx_someNumber", TableName, new SqlIndexColumn("someNumber")))
                .AddIndex(new SqlIndex("idx_someName", TableName, new SqlIndexColumn("someName", 16)) { Unique = true });

            // Alternative style to create a table
            var table2 = new SqlTable(TableName,
                new SqlColumn[]
                {
                    new SqlColumn("id", MySqlDbType.Int32) { Primary = true },
                    new SqlColumn("someNumber", MySqlDbType.Int32),
                    new SqlColumn("anotherNumber", MySqlDbType.Int64),
                    new SqlColumn("someName", MySqlDbType.VarChar) { Primary = true, Length = 16 }
                },
                new SqlForeignKey[]
                {
                    new SqlForeignKey("id", "Users", "ID", SqlForeignKey.Action.Cascade, SqlForeignKey.Action.Cascade)
                },
                new SqlIndex[]
                {
                    new SqlIndex("idx_someNumber", TableName, new SqlIndexColumn("someNumber")),
                    new SqlIndex("idx_someName", TableName, new SqlIndexColumn("someName", 16))
                });

            var creator = new SqlTableCreator(conn, DbProvider);
            creator.EnsureTableStructure(table1);
        }
    }
}