C# SqlTableCreator.EnsureTableStructure方法代碼示例

        public CharacterManager(IDbConnection db)
        {
            database = db;

            var table = new SqlTable("tsCharacter",
                                     new SqlColumn("Account", MySqlDbType.Int32) {Primary = true},
                                     new SqlColumn("Health", MySqlDbType.Int32),
                                     new SqlColumn("MaxHealth", MySqlDbType.Int32),
                                     new SqlColumn("Mana", MySqlDbType.Int32),
                                     new SqlColumn("MaxMana", MySqlDbType.Int32),
                                     new SqlColumn("Inventory", MySqlDbType.Text),
                                     new SqlColumn("extraSlot", MySqlDbType.Int32),
                                     new SqlColumn("spawnX", MySqlDbType.Int32),
                                     new SqlColumn("spawnY", MySqlDbType.Int32),
                                     new SqlColumn("skinVariant", MySqlDbType.Int32),
                                     new SqlColumn("hair", MySqlDbType.Int32),
                                     new SqlColumn("hairDye", MySqlDbType.Int32),
                                     new SqlColumn("hairColor", MySqlDbType.Int32),
                                     new SqlColumn("pantsColor", MySqlDbType.Int32),
                                     new SqlColumn("shirtColor", MySqlDbType.Int32),
                                     new SqlColumn("underShirtColor", MySqlDbType.Int32),
                                     new SqlColumn("shoeColor", MySqlDbType.Int32),
                                     new SqlColumn("hideVisuals", MySqlDbType.Int32),
                                     new SqlColumn("skinColor", MySqlDbType.Int32),
                                     new SqlColumn("eyeColor", MySqlDbType.Int32),
                                     new SqlColumn("questsCompleted", MySqlDbType.Int32)
                );
            var creator = new SqlTableCreator(db,
                                              db.GetSqlType() == SqlType.Sqlite
                                              	? (IQueryBuilder) new SqliteQueryCreator()
                                              	: new MysqlQueryCreator());
            creator.EnsureTableStructure(table);
        }

 internal static void CheckTables(IDbConnection connection)
 {
     SQLWriter = new SqlTableCreator(connection, new MysqlQueryCreator());
     var table = new SqlTable("BannedIP",
     new SqlColumn("IP", MySqlDbType.Text),
     new SqlColumn("BanDate", MySqlDbType.Int32),
     new SqlColumn("UnbanDate", MySqlDbType.Int32),
     new SqlColumn("BannedBy", MySqlDbType.Text),
     new SqlColumn("Reason", MySqlDbType.Text)
     );
     SQLWriter.EnsureTableStructure(table);
     table = new SqlTable("BannedPlayer",
     new SqlColumn("Player", MySqlDbType.Text),
     new SqlColumn("BanDate", MySqlDbType.Int32),
     new SqlColumn("UnbanDate", MySqlDbType.Int32),
     new SqlColumn("BannedBy", MySqlDbType.Text),
     new SqlColumn("Reason", MySqlDbType.Text)
     );
     SQLWriter.EnsureTableStructure(table);
     table = new SqlTable("MutedPlayer",
     new SqlColumn("Player", MySqlDbType.Text),
     new SqlColumn("MuteDate", MySqlDbType.Int32),
     new SqlColumn("UnmuteDate", MySqlDbType.Int32),
     new SqlColumn("MutedBy", MySqlDbType.Text),
     new SqlColumn("Reason", MySqlDbType.Text)
     );
     SQLWriter.EnsureTableStructure(table);
 }

        internal static void DBConnect()
        {
            switch (TShock.Config.StorageType.ToLower())
            {
                case "mysql":
                    string[] dbHost = TShock.Config.MySqlHost.Split(':');
                    db = new MySqlConnection()
                    {
                        ConnectionString = string.Format("Server={0}; Port={1}; Database={2}; Uid={3}; Pwd={4};",
                            dbHost[0],
                            dbHost.Length == 1 ? "3306" : dbHost[1],
                            TShock.Config.MySqlDbName,
                            TShock.Config.MySqlUsername,
                            TShock.Config.MySqlPassword)

                    };
                    break;

                case "sqlite":
                    string sql = Path.Combine(TShock.SavePath, "tshock.sqlite");
                    db = new SqliteConnection(string.Format("uri=file://{0},Version=3", sql));
                    break;

            }

            SqlTableCreator sqlcreator = new SqlTableCreator(db, db.GetSqlType() == SqlType.Sqlite ? (IQueryBuilder)new SqliteQueryCreator() : new MysqlQueryCreator());

            sqlcreator.EnsureTableStructure(new SqlTable("NoTpRegions",
                new SqlColumn("Name", MySqlDbType.Text)));
        }

		public HomeManager(IDbConnection db)
		{
			this.db = db;

			var sqlCreator = new SqlTableCreator(db,
				db.GetSqlType() == SqlType.Sqlite ? (IQueryBuilder) new SqliteQueryCreator() : new MysqlQueryCreator());
			sqlCreator.EnsureTableStructure(new SqlTable("Homes",
				new SqlColumn("ID", MySqlDbType.Int32) {AutoIncrement = true, Primary = true},
				new SqlColumn("UserID", MySqlDbType.Int32),
				new SqlColumn("Name", MySqlDbType.Text),
				new SqlColumn("X", MySqlDbType.Double),
				new SqlColumn("Y", MySqlDbType.Double),
				new SqlColumn("WorldID", MySqlDbType.Int32)));

			using (QueryResult result = db.QueryReader("SELECT * FROM Homes WHERE WorldID = @0", Main.worldID))
			{
				while (result.Read())
				{
					homes.Add(new Home(
						result.Get<int>("UserID"),
						result.Get<string>("Name"),
						result.Get<float>("X"),
						result.Get<float>("Y")));
				}
			}
		}

        private void OnInitialize(EventArgs args)
        {
            SqlTableCreator sqlcreator = new SqlTableCreator(TShock.DB, TShock.DB.GetSqlType() == SqlType.Sqlite ? (IQueryBuilder)new SqliteQueryCreator() : new MysqlQueryCreator());
            sqlcreator.EnsureTableStructure(new SqlTable("TradeRequestLogs",
            new SqlColumn("ID", MySqlDbType.Int32) { AutoIncrement = true, Primary = true },
            new SqlColumn("Trader", MySqlDbType.Text),
            new SqlColumn("Tradee", MySqlDbType.Text),
            new SqlColumn("TraderItems", MySqlDbType.Text),
            new SqlColumn("TradeeItems", MySqlDbType.Text),
            new SqlColumn("TradeDate", MySqlDbType.Text)
            ));

            #region Commands
            Action<Command> Add = c =>
            {
                TShockAPI.Commands.ChatCommands.RemoveAll(c2 => c2.Names.Select(s => s.ToLowerInvariant()).Intersect(c.Names.Select(s => s.ToLowerInvariant())).Any());
                TShockAPI.Commands.ChatCommands.Add(c);
            };

            Add(new Command(Permissions.trade, Commands.Trade, "trade")
            {
                AllowServer = true,
                HelpText = "Allows the user to trade with another user."
            });

            Add(new Command(Permissions.tradelogs, Commands.TradeLogs, "tradelogs")
            {
                AllowServer = true,
                HelpText = "Allows the user check trade logs."
            });
            #endregion
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="TShockAPI.DB.BanManager"/> class.
        /// </summary>
        /// <param name="db">A valid connection to the TShock database</param>
		public BanManager(IDbConnection db)
		{
			database = db;

			var table = new SqlTable("Bans",
			                         new SqlColumn("IP", MySqlDbType.String, 16) {Primary = true},
			                         new SqlColumn("Name", MySqlDbType.Text),
									 new SqlColumn("UUID", MySqlDbType.Text),
			                         new SqlColumn("Reason", MySqlDbType.Text),
                                     new SqlColumn("BanningUser", MySqlDbType.Text),
                                     new SqlColumn("Date", MySqlDbType.Text),
                                     new SqlColumn("Expiration", MySqlDbType.Text)
				);
			var creator = new SqlTableCreator(db,
			                                  db.GetSqlType() == SqlType.Sqlite
			                                  	? (IQueryBuilder) new SqliteQueryCreator()
			                                  	: new MysqlQueryCreator());
			try
			{
				creator.EnsureTableStructure(table);
			}
			catch (DllNotFoundException)
			{
				System.Console.WriteLine("Possible problem with your database - is Sqlite3.dll present?");
				throw new Exception("Could not find a database library (probably Sqlite3.dll)");
			}
		}

        /* EnsureExists
         *
         * Checks if tables exists and creates them if not
         *
         * */
        public void EnsureExists(params SqlTable[] Tables)
        {
            // We want to use TShock creator for checking
            SqlTableCreator SqlCreator = new SqlTableCreator(DB,
                (IQueryBuilder)new SqliteQueryCreator());

            foreach (var Table in Tables)
            {
                SqlCreator.EnsureTableStructure(Table);
            }
        }

        public static void Initialize()
        {
            if (!Directory.Exists(SavePath))
                Directory.CreateDirectory(SavePath);

            switch (TShock.Config.StorageType.ToLower())
            {
                case "mysql":
                    string[] host = TShock.Config.MySqlHost.Split(':');
                    Database = new MySqlConnection()
                    {
                        ConnectionString = string.Format("Server={0}; Port={1}; Database={2}; Uid={3}; Pwd={4};",
                                host[0],
                                host.Length == 1 ? "3306" : host[1],
                                TShock.Config.MySqlDbName,
                                TShock.Config.MySqlUsername,
                                TShock.Config.MySqlPassword)
                    };
                    break;
                case "sqlite":
                    string sql = Path.Combine(SavePath, "Clans.sqlite");
                    Database = new SqliteConnection(string.Format("uri=file://{0},Version=3", sql));
                    break;
            }
            SqlTableCreator SQLcreator = new SqlTableCreator(Database, Database.GetSqlType() == SqlType.Sqlite
            ? (IQueryBuilder)new SqliteQueryCreator() : new MysqlQueryCreator());

            SqlTable[] tables = new SqlTable[]
            {
                 new SqlTable("Clans",
                     new SqlColumn("Name", MySqlDbType.VarChar) { Primary=true, Unique=true },
                     new SqlColumn("Owner", MySqlDbType.VarChar),
                     new SqlColumn("InviteMode", MySqlDbType.Int32),
                     new SqlColumn("TileX", MySqlDbType.Int32),
                     new SqlColumn("TileY", MySqlDbType.Int32),
                     new SqlColumn("ChatColor", MySqlDbType.VarChar),
                     new SqlColumn("Bans", MySqlDbType.VarChar)
                     ),
                 new SqlTable("ClanMembers",
                     new SqlColumn("Username",MySqlDbType.VarChar) { Primary=true, Unique=true },
                     new SqlColumn("ClanName", MySqlDbType.VarChar)
                     ),
                 new SqlTable("ClanWarps",
                     new SqlColumn("WarpName",MySqlDbType.VarChar) { Primary=true, Unique=true }                  
                     )
            };

            for (int i = 0; i < tables.Length; i++)
                SQLcreator.EnsureTableStructure(tables[i]);

            Config = Config.Read();
            LoadClans();
        }

        public VocationManager(IDbConnection conn)
        {
            _conn = conn;

            var table = new SqlTable(TableName,
                new SqlColumn("id", MySqlDbType.Int32) { Primary = true },
                new SqlColumn("level", MySqlDbType.Int32),
                new SqlColumn("experience", MySqlDbType.Int64),
                new SqlColumn("vocation", MySqlDbType.VarChar) { Primary = true, Length = 16 });

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

		public MuteManager(IDbConnection db)
		{
			this.db = db;

			var sqlCreator = new SqlTableCreator(db, db.GetSqlType() == SqlType.Sqlite
				? (IQueryBuilder)new SqliteQueryCreator() 
				: new MysqlQueryCreator());

			sqlCreator.EnsureTableStructure(new SqlTable("Mutes",
				new SqlColumn("ID", MySqlDbType.Int32) { AutoIncrement = true, Primary = true },
				new SqlColumn("Name", MySqlDbType.Text),
				new SqlColumn("UUID", MySqlDbType.Text),
				new SqlColumn("IP", MySqlDbType.Text),
				new SqlColumn("Date", MySqlDbType.Text),
				new SqlColumn("Expiration", MySqlDbType.Text)));
		}

		public RememberedPosManager(IDbConnection db)
		{
			database = db;

			var table = new SqlTable("RememberedPos",
			                         new SqlColumn("Name", MySqlDbType.VarChar, 50) {Primary = true},
			                         new SqlColumn("IP", MySqlDbType.Text),
			                         new SqlColumn("X", MySqlDbType.Int32),
			                         new SqlColumn("Y", MySqlDbType.Int32),
			                         new SqlColumn("WorldID", MySqlDbType.Text)
				);
			var creator = new SqlTableCreator(db,
			                                  db.GetSqlType() == SqlType.Sqlite
			                                  	? (IQueryBuilder) new SqliteQueryCreator()
			                                  	: new MysqlQueryCreator());
			creator.EnsureTableStructure(table);
		}

		internal WarpManager(IDbConnection db)
		{
			database = db;

			var table = new SqlTable("Warps",
			                         new SqlColumn("Id", MySqlDbType.Int32){Primary = true, AutoIncrement = true},
									 new SqlColumn("WarpName", MySqlDbType.VarChar, 50) {Unique = true},
			                         new SqlColumn("X", MySqlDbType.Int32),
			                         new SqlColumn("Y", MySqlDbType.Int32),
									 new SqlColumn("WorldID", MySqlDbType.VarChar, 50) { Unique = true },
			                         new SqlColumn("Private", MySqlDbType.Text)
				);
			var creator = new SqlTableCreator(db,
			                                  db.GetSqlType() == SqlType.Sqlite
			                                  	? (IQueryBuilder) new SqliteQueryCreator()
			                                  	: new MysqlQueryCreator());
			creator.EnsureTableStructure(table);
		}

        public Database(IDbConnection db)
        {
            _db = db;

            var sqlCreator = new SqlTableCreator(_db,
                _db.GetSqlType() == SqlType.Sqlite
                    ? (IQueryBuilder) new SqliteQueryCreator()
                    : new MysqlQueryCreator());

            var table = new SqlTable("Reports",
                new SqlColumn("ReportID", MySqlDbType.Int32) {AutoIncrement = true, Primary = true},
                new SqlColumn("UserID", MySqlDbType.Int32),
                new SqlColumn("ReportedID", MySqlDbType.Int32),
                new SqlColumn("Message", MySqlDbType.Text),
                new SqlColumn("Position", MySqlDbType.Text),
                new SqlColumn("State", MySqlDbType.Int32));

            sqlCreator.EnsureTableStructure(table);
        }

		private Database(IDbConnection db)
		{
			_db = db;

			//Define a table creator that will be responsible for ensuring the database table exists
			_tableCreator = new SqlTableCreator(_db,
				_db.GetSqlType() == SqlType.Sqlite
					? (IQueryBuilder)new SqliteQueryCreator()
					: new MysqlQueryCreator());

			//Define the table
			var table = new SqlTable("ServerEvents",
				new SqlColumn("UserID", MySqlDbType.Int32) { AutoIncrement = true, Primary = true },
				new SqlColumn("LastLoginTime", MySqlDbType.VarChar),
				new SqlColumn("DailyLogIn", MySqlDbType.Int32) { DefaultValue = "0" },
				new SqlColumn("DailyLogInStreak", MySqlDbType.Int32));

			_tableCreator.EnsureTableStructure(table);
		}

		/// <summary>
		/// Sets the database connection and the initial log level.
		/// </summary>
		/// <param name="db">Database connection</param>
		/// <param name="textlogFilepath">File path to a backup text log in case the SQL log fails</param>
		/// <param name="clearTextLog"></param>
		public SqlLog(IDbConnection db, string textlogFilepath, bool clearTextLog)
		{
			FileName = string.Format("{0}://database", db.GetSqlType());
			_database = db;
			_backupLog = new TextLog(textlogFilepath, clearTextLog);

			var table = new SqlTable("Logs",
				new SqlColumn("ID", MySqlDbType.Int32) {AutoIncrement = true, Primary = true},
				new SqlColumn("TimeStamp", MySqlDbType.Text),
				new SqlColumn("LogLevel", MySqlDbType.Int32),
				new SqlColumn("Caller", MySqlDbType.Text),
				new SqlColumn("Message", MySqlDbType.Text)
				);

			var creator = new SqlTableCreator(db,
				db.GetSqlType() == SqlType.Sqlite
					? (IQueryBuilder) new SqliteQueryCreator()
					: new MysqlQueryCreator());
			creator.EnsureTableStructure(table);
		}