C# DB.SqlTableCreator类代码示例

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

        public GroupManager(IDbConnection db)
        {
            database = db;

            var table = new SqlTable("GroupList",
                new SqlColumn("GroupName", MySqlDbType.VarChar, 32) { Primary = true },
                new SqlColumn("Parent", MySqlDbType.VarChar, 32),
                new SqlColumn("Commands", MySqlDbType.Text),
                new SqlColumn("ChatColor", MySqlDbType.Text)
            );
            var creator = new SqlTableCreator(db, db.GetSqlType() == SqlType.Sqlite ? (IQueryBuilder)new SqliteQueryCreator() : new MysqlQueryCreator());
            creator.EnsureExists(table);

            //Add default groups
            AddGroup("default", "canwater,canlava,warp,canbuild");
            AddGroup("newadmin", "default", "kick,editspawn,reservedslot");
            AddGroup("admin", "newadmin", "ban,unban,whitelist,causeevents,spawnboss,spawnmob,managewarp,time,tp,pvpfun,kill,logs,immunetokick,tphere");
            AddGroup("trustedadmin", "admin", "maintenance,cfg,butcher,item,heal,immunetoban,ignorecheatdetection,ignoregriefdetection,usebanneditem,manageusers");
            AddGroup("vip", "default", "canwater,canlava,warp,canbuild,reservedslot");

            String file = Path.Combine(TShock.SavePath, "groups.txt");
            if (File.Exists(file))
            {
                using (StreamReader sr = new StreamReader(file))
                {
                    String line;
                    while ((line = sr.ReadLine()) != null)
                    {
                        if (!line.Equals("") && !line.Substring(0, 1).Equals("#"))
                        {
                            String[] info = line.Split(' ');
                            String comms = "";
                            int size = info.Length;
                            for (int i = 1; i < size; i++)
                            {
                                if (!comms.Equals(""))
                                    comms = comms + ",";
                                comms = comms + info[i].Trim();
                            }

                            string query = "";
                            if (TShock.Config.StorageType.ToLower() == "sqlite")
                                query = "INSERT OR IGNORE INTO GroupList (GroupName, Commands) VALUES (@0, @1);";
                            else if (TShock.Config.StorageType.ToLower() == "mysql")
                                query = "INSERT IGNORE INTO GroupList SET [email protected], [email protected];";

                            db.Query(query, info[0].Trim(), comms);

                        }
                    }
                }
                String path = Path.Combine(TShock.SavePath, "old_configs");
                String file2 = Path.Combine(path, "groups.txt");
                if (!Directory.Exists(path))
                    Directory.CreateDirectory(path);
                if (File.Exists(file2))
                    File.Delete(file2);
                File.Move(file, file2);
            }
        }

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

 public WarpplateManager(IDbConnection db)
 {
     this.database = db;
     string arg_ED_0 = "Warpplates";
     SqlColumn[] array = new SqlColumn[11];
     array[0] = new SqlColumn("X1", MySqlDbType.Int32);
     array[1] = new SqlColumn("Y1", MySqlDbType.Int32);
     array[2] = new SqlColumn("width", MySqlDbType.Int32);
     array[3] = new SqlColumn("height", MySqlDbType.Int32);
     SqlColumn[] arg_7E_0 = array;
     int arg_7E_1 = 4;
     SqlColumn sqlColumn = new SqlColumn("WarpplateName", MySqlDbType.VarChar, new int?(50));
     sqlColumn.Primary = true;
     arg_7E_0[arg_7E_1] = sqlColumn;
     array[5] = new SqlColumn("WorldID", MySqlDbType.Text);
     array[6] = new SqlColumn("UserIds", MySqlDbType.Text);
     array[7] = new SqlColumn("Protected", MySqlDbType.Int32);
     array[8] = new SqlColumn("WarpplateDestination", MySqlDbType.VarChar, new int?(50));
     array[9] = new SqlColumn("Delay", MySqlDbType.Int32);
     array[10] = new SqlColumn("Label", MySqlDbType.Text);
     SqlTable sqlTable = new SqlTable(arg_ED_0, array);
     IQueryBuilder arg_10D_1;
     if (DbExt.GetSqlType(db) != SqlType.Sqlite)
     {
         IQueryBuilder queryBuilder = new MysqlQueryCreator();
         arg_10D_1 = queryBuilder;
     }
     else
     {
         arg_10D_1 = new SqliteQueryCreator();
     }
     SqlTableCreator sqlTableCreator = new SqlTableCreator(db, arg_10D_1);
     sqlTableCreator.EnsureExists(sqlTable);
     this.ReloadAllWarpplates();
 }

        public GroupManager(IDbConnection db)
        {
            database = db;

            var table = new SqlTable("GroupList",
                                     new SqlColumn("GroupName", MySqlDbType.VarChar, 32) {Primary = true},
                                     new SqlColumn("Parent", MySqlDbType.VarChar, 32),
                                     new SqlColumn("Commands", MySqlDbType.Text),
                                     new SqlColumn("ChatColor", MySqlDbType.Text),
                                     new SqlColumn("Prefix", MySqlDbType.Text),
                                     new SqlColumn("Suffix", MySqlDbType.Text)
                );
            var creator = new SqlTableCreator(db,
                                              db.GetSqlType() == SqlType.Sqlite
                                              	? (IQueryBuilder) new SqliteQueryCreator()
                                              	: new MysqlQueryCreator());
            creator.EnsureExists(table);

            //Add default groups
            AddGroup("guest", "canbuild,canregister,canlogin,canpartychat,cantalkinthird");
            AddGroup("default", "guest", "warp,canchangepassword");
            AddGroup("newadmin", "default", "kick,editspawn,reservedslot");
            AddGroup("admin", "newadmin",
                     "ban,unban,whitelist,causeevents,spawnboss,spawnmob,managewarp,time,tp,pvpfun,kill,logs,immunetokick,tphere");
            AddGroup("trustedadmin", "admin", "maintenance,cfg,butcher,item,heal,immunetoban,usebanneditem,manageusers");
            AddGroup("vip", "default", "reservedslot");
        }

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

 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.EnsureExists(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.EnsureExists(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.EnsureExists(table);
 }

 public static void InitTerraDB()
 {
     string sql = Path.Combine(terraDB);
     if (!File.Exists(terraDB))
     {
         SqliteConnection.CreateFile(terraDB);
     }
     DB = new SqliteConnection(string.Format("uri=file://{0},Version=3", sql));
     SQLWriter = new SqlTableCreator(DB, new SqliteQueryCreator());
     var table = new SqlTable("Clans",
     new SqlColumn("clanname", MySqlDbType.Text) { Unique = true },
     new SqlColumn("clangroup", MySqlDbType.Text),
     new SqlColumn("leaders", MySqlDbType.Text),
     new SqlColumn("members", MySqlDbType.Text),
     new SqlColumn("motd", MySqlDbType.Text),
     new SqlColumn("invites", MySqlDbType.Text)
     );
     SQLWriter.EnsureExists(table);
     table = new SqlTable("FoundQueue",
     new SqlColumn("clanname", MySqlDbType.Text) { Unique = true },
     new SqlColumn("founder", MySqlDbType.Text),
     new SqlColumn("cofounder", MySqlDbType.Text)
     );
     SQLWriter.EnsureExists(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 void InitializeTable()
        {
            var table = new SqlTable("RegionHelper",
                new SqlColumn("RegionName", MySql.Data.MySqlClient.MySqlDbType.Text),
                new SqlColumn("IsLocked", MySql.Data.MySqlClient.MySqlDbType.Text));

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

        /* 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 ChatManager(IDbConnection db)
        {
            database = db;

                var table = new SqlTable("Chat",
                    new SqlColumn("ID", MySqlDbType.Int32) { Primary = true, AutoIncrement = true },
                    new SqlColumn("Username", MySqlDbType.VarChar, 32),
                    new SqlColumn("ToUsername", MySqlDbType.VarChar, 32),
                    new SqlColumn("Message", MySqlDbType.VarChar, 32)
                    );
                var creator = new SqlTableCreator(db, db.GetSqlType() == SqlType.Sqlite ? (IQueryBuilder)new SqliteQueryCreator() : new MysqlQueryCreator());
                creator.EnsureExists(table);
        }

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