Example #1
0
        public InventoryManager(IDbConnection db)
        {
            database = db;

            var table = new SqlTable("Inventory",
                                     new SqlColumn("Account", MySqlDbType.Int32) {Primary = true},
                                     new SqlColumn("MaxHealth", MySqlDbType.Int32),
                                     new SqlColumn("MaxMana", MySqlDbType.Int32),
                                     new SqlColumn("Inventory", MySqlDbType.Text)
                );
            var creator = new SqlTableCreator(db,
                                              db.GetSqlType() == SqlType.Sqlite
                                              	? (IQueryBuilder) new SqliteQueryCreator()
                                              	: new MysqlQueryCreator());
            creator.EnsureExists(table);
        }
Example #2
0
        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.EnsureExists(table);
        }
Example #3
0
 /// <summary>
 /// Alter a table from source to destination
 /// </summary>
 /// <param name="from">Must have name and column names. Column types are not required</param>
 /// <param name="to">Must have column names and column types.</param>
 /// <returns></returns>
 public string AlterTable(SqlTable from, SqlTable to)
 {
     /*
      * Any example outpuf from this looks like:-
         ALTER TABLE "main"."Bans" RENAME TO "oXHFcGcd04oXHFcGcd04_Bans"
         CREATE TABLE "main"."Bans" ("IP" TEXT PRIMARY KEY ,"Name" TEXT)
         INSERT INTO "main"."Bans" SELECT "IP","Name" FROM "main"."oXHFcGcd04oXHFcGcd04_Bans"
         DROP TABLE "main"."oXHFcGcd04oXHFcGcd04_Bans"
      *
      * Twitchy - Oh. I get it!
      */
     var rstr = rand.NextString(20);
     var escapedTable = EscapeTableName(from.Name);
     var tmpTable = EscapeTableName("{0}_{1}".SFormat(rstr, from.Name));
     var alter = RenameTable(escapedTable, tmpTable);
     var create = CreateTable(to);
     // combine all columns in the 'from' variable excluding ones that aren't in the 'to' variable.
     // exclude the ones that aren't in 'to' variable because if the column is deleted, why try to import the data?
     var columns = string.Join(", ", from.Columns.Where(c => to.Columns.Any(c2 => c2.Name == c.Name)).Select(c => c.Name));
     var insert = "INSERT INTO {0} ({1}) SELECT {1} FROM {2}".SFormat(escapedTable, columns, tmpTable);
     var drop = "DROP TABLE {0}".SFormat(tmpTable);
     return "{0}; {1}; {2}; {3};".SFormat(alter, create, insert, drop);
 }
Example #4
0
 public abstract string CreateTable(SqlTable table);
Example #5
0
 public override string CreateTable(SqlTable table)
 {
     var columns =
         table.Columns.Select(
             c =>
             "'{0}' {1} {2} {3} {4} {5}".SFormat(c.Name, DbTypeToString(c.Type, c.Length), c.Primary ? "PRIMARY KEY" : "",
                                             c.AutoIncrement ? "AUTOINCREMENT" : "", c.NotNull ? "NOT NULL" : "",
                                             c.Unique ? "UNIQUE" : ""));
     return "CREATE TABLE {0} ({1})".SFormat(EscapeTableName(table.Name), string.Join(", ", columns));
 }
Example #6
0
 public override string CreateTable(SqlTable table)
 {
     var columns =
         table.Columns.Select(
             c =>
             "{0} {1} {2} {3} {4}".SFormat(c.Name, DbTypeToString(c.Type, c.Length), c.Primary ? "PRIMARY KEY" : "",
                                       c.AutoIncrement ? "AUTO_INCREMENT" : "", c.NotNull ? "NOT NULL" : ""));
     var uniques = table.Columns.Where(c => c.Unique).Select(c => c.Name);
     return "CREATE TABLE {0} ({1} {2})".SFormat(EscapeTableName(table.Name), string.Join(", ", columns),
                                                 uniques.Count() > 0
                                                     ? ", UNIQUE({0})".SFormat(string.Join(", ", uniques))
                                                     : "");
 }
Example #7
0
        public UserManager(IDbConnection db, TPulse tPulse)
        {
            this.tPulse = tPulse;

            database = db;

            var table = new SqlTable("Users",
                                     new SqlColumn("ID", MySqlDbType.Int32) {Primary = true, AutoIncrement = true},
                                     new SqlColumn("Username", MySqlDbType.VarChar, 32) {Unique = true},
                                     new SqlColumn("Password", MySqlDbType.VarChar, 128),
                                     new SqlColumn("Usergroup", MySqlDbType.Text),
                                     new SqlColumn("IP", MySqlDbType.VarChar, 16)
                );
            var creator = new SqlTableCreator(db,
                                              db.GetSqlType() == SqlType.Sqlite
                                              	? (IQueryBuilder) new SqliteQueryCreator()
                                              	: new MysqlQueryCreator());
            creator.EnsureExists(table);
        }
Example #8
0
        public RegionManager(IDbConnection db, TPulse tPulse)
        {
            this.tPulse = tPulse;
            database = db;
            var table = new SqlTable("Regions",
                                     new SqlColumn("X1", MySqlDbType.Int32),
                                     new SqlColumn("Y1", MySqlDbType.Int32),
                                     new SqlColumn("width", MySqlDbType.Int32),
                                     new SqlColumn("height", MySqlDbType.Int32),
                                     new SqlColumn("RegionName", MySqlDbType.VarChar, 50) {Primary = true},
                                     new SqlColumn("WorldID", MySqlDbType.Text),
                                     new SqlColumn("UserIds", MySqlDbType.Text),
                                     new SqlColumn("Protected", MySqlDbType.Int32),
                                     new SqlColumn("Groups", MySqlDbType.Text),
                                     new SqlColumn("Owner", MySqlDbType.VarChar, 50),
                                     new SqlColumn("Z", MySqlDbType.Int32){ DefaultValue = "0" }
                );
            var creator = new SqlTableCreator(db,
                                              db.GetSqlType() == SqlType.Sqlite
                                              	? (IQueryBuilder) new SqliteQueryCreator()
                                              	: new MysqlQueryCreator());
            creator.EnsureExists(table);

            ReloadAllRegions();
        }
Example #9
0
        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("Reason", MySqlDbType.Text)
                );
            var creator = new SqlTableCreator(db,
                                              db.GetSqlType() == SqlType.Sqlite
                                              	? (IQueryBuilder) new SqliteQueryCreator()
                                              	: new MysqlQueryCreator());
            try
            {
                creator.EnsureExists(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)");
            }
        }
Example #10
0
 public void EnsureExists(SqlTable table)
 {
     var columns = GetColumns(table);
     if (columns.Count > 0)
     {
         if (!table.Columns.All(c => columns.Contains(c.Name)) || !columns.All(c => table.Columns.Any(c2 => c2.Name == c)))
         {
             var from = new SqlTable(table.Name, columns.Select(s => new SqlColumn(s, MySqlDbType.String)).ToList());
             database.Query(creator.AlterTable(from, table));
         }
     }
     else
     {
         database.Query(creator.CreateTable(table));
     }
 }
Example #11
0
        public List<string> GetColumns(SqlTable table)
        {
            var ret = new List<string>();
            var name = database.GetSqlType();
            if (name == SqlType.Sqlite)
            {
                using (var reader = database.QueryReader("PRAGMA table_info({0})".SFormat(table.Name)))
                {
                    while (reader.Read())
                        ret.Add(reader.Get<string>("name"));
                }
            }
            else if (name == SqlType.Mysql)
            {
                using (
                    var reader =
                        database.QueryReader(
                            "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME=@0 AND TABLE_SCHEMA=@1", table.Name,
                            database.Database))
                {
                    while (reader.Read())
                        ret.Add(reader.Get<string>("COLUMN_NAME"));
                }
            }
            else
            {
                throw new NotSupportedException();
            }

            return ret;
        }
Example #12
0
 public abstract string CreateTable(SqlTable table);