protected Dictionary <string, TableInfoModel> GetMySqlTableInfo(IDbConnection conn) { var tables = new Dictionary <string, TableInfoModel>(); var sqlCommand = $"SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM Information_schema.columns WHERE table_schema = '{conn.Database}';"; conn.Open(); var cmd = conn.CreateCommand(); cmd.Connection = conn; cmd.CommandText = sqlCommand; cmd.CommandType = System.Data.CommandType.Text; var reader = cmd.ExecuteReader(); while (reader.Read()) { var tableName = reader["TABLE_NAME"].ToString().ToLower(); var columnName = reader["COLUMN_NAME"].ToString().ToLower(); var columnTypes = reader["COLUMN_TYPE"].ToString().ToLower().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); var columnOption = ""; if (columnTypes.Count() > 1) { columnOption = columnTypes.Skip(1).Aggregate((opton, next) => $"{opton} {next}"); } var column = new ParseSqlData() { TableName = tableName, ColumnName = columnName, ColumnType = columnTypes[0], ColumnOptions = columnOption }; if (!tables.ContainsKey(column.TableName)) { tables.Add(column.TableName, new TableInfoModel()); } tables[column.TableName].Columns.Add(column.ColumnName, column); } conn.Close(); return(tables); }
private static bool ParseSubAlterTableCommand(string line, out ParseSqlData parseSqlData) { parseSqlData = new ParseSqlData(); var commandIndex = line.IndexOf(" "); //Command를 뜯어내고 var command = line.Substring(0, commandIndex++); //이후 무엇이 바뀌었는지 뜯어낸다. var body = line.Substring(commandIndex, line.Length - commandIndex); if (!Enum.TryParse(command, true, out CommandType type)) { return(false); } parseSqlData.CommandType = type; var isReserved = false; foreach (var key in Consts.MySqlReservedKeyword.Keys) { if (body.ToUpper().StartsWith(key)) { isReserved = true; break; } } var columnNameIndex = isReserved ? 1 : 0; if (isReserved == false && type == CommandType.Alter) { var columnInfo = body.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); parseSqlData.ColumnName = columnInfo[columnNameIndex].Replace("`", ""); parseSqlData.Command = body; } else if (isReserved == false || body.ToUpper().StartsWith("COLUMN")) { parseSqlData.ClassificationType = ClassificationType.Columns; var columnInfo = body.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); var typeIndex = isReserved ? 2 : 1; if (type == CommandType.Change) { parseSqlData.ChangeColumnName = columnInfo[2].Replace("`", ""); typeIndex++; } parseSqlData.ColumnName = columnInfo[columnNameIndex].Replace("`", ""); if (Consts.BaseMySqlDataType.ContainsKey(columnInfo[typeIndex].ToLower())) { parseSqlData.ColumnType = columnInfo[typeIndex] = Consts.BaseMySqlDataType[columnInfo[typeIndex].ToLower()]; } typeIndex++; parseSqlData.ColumnOptions = ""; for (int i = typeIndex; i < columnInfo.Count(); i++) { parseSqlData.ColumnOptions += columnInfo[i] + " "; } } else { //Index, 외래키 기본키 이름 뜯기 foreach (var key in Consts.MySqlReservedKeyword.Keys) { if (!body.ToUpper().StartsWith(key)) { continue; } foreach (var value in Consts.MySqlReservedKeyword[key]) { var idx = body.ToUpper().IndexOf(value); if (idx == -1) { idx = body.Length; } var nameZone = body.Substring(key.Length, idx - key.Length); nameZone = nameZone.Replace("`", " ").Trim(); var splits = nameZone.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); if (splits.Length == 0) { parseSqlData.ColumnName = ""; } else { parseSqlData.ColumnName = splits[0]; } break; } break; } parseSqlData.Command = body; } return(true); }
public static string CreateSqlCommand(ParseSqlData model) { return($"ALTER TABLE `{model.TableName}` {model.CommandType.ToString().ToUpper()} {model.Command};"); }
public static string AlterMySqlColumn(ParseSqlData model) { return($"ALTER TABLE `{model.TableName}` {model.CommandType.ToString().ToUpper()} COLUMN `{model.ColumnName}` {model.ColumnType} {(model.CommandType != CommandType.Drop ? $"{model.ColumnOptions}" : "")};"); }
public static string AlterMySqlColumnChange(ParseSqlData model) { return($"ALTER TABLE `{model.TableName}` CHANGE COLUMN `{model.ColumnName}` `{model.ChangeColumnName}` {model.ColumnType} {model.ColumnOptions};"); }
public static bool ParseCreateTableCommnad(string sql, out TableInfoModel tableInfoData) { tableInfoData = new TableInfoModel() { Columns = new Dictionary <string, ParseSqlData>(), }; BaseSqlReader reader = new MySqlReader(sql); while (reader.NextLine(out string line)) { if (line.ToUpper().StartsWith("CREATE TABLE")) { var openIndex = line.IndexOf("(", 0); var closeIndex = line.LastIndexOf(')'); if (openIndex == -1) { throw new Exception($"CreateTable Parse Error: {sql}"); } var tableNameIndex = line.ToUpper().IndexOf("CREATE TABLE") + "CREATE TABLE".Length; var tableName = line.Substring(tableNameIndex, openIndex - tableNameIndex).Replace("`", "").Trim(); var body = line.Substring(openIndex + 1, closeIndex - openIndex - 1).Trim(); tableInfoData.TableName = tableName; var findIndex = 0; while (findIndex <= body.Length) { int beforeIndex = findIndex; findIndex = body.IndexOf(",", findIndex); if (findIndex == -1) { findIndex = body.Length; } line = body.Substring(beforeIndex, findIndex - beforeIndex).Trim(); bool isReservedKeyword = false; var queue = new Queue <string>(); foreach (var key in Consts.MySqlReservedKeyword.Keys) { isReservedKeyword = line.ToUpper().StartsWith(key); if (isReservedKeyword == false) { continue; } queue.Enqueue(key); break; } if (isReservedKeyword == false)//기본 컬럼 { var datas = line.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).ToList(); var option = ""; for (int ii = 2; ii < datas.Count; ii++) { if (datas[ii].StartsWith("(") && datas[ii].EndsWith(")")) { datas[1] += datas[ii]; } else { option += $"{datas[ii]} "; } } if (Consts.BaseMySqlDataType.ContainsKey(datas[1])) { datas[1] = Consts.BaseMySqlDataType[datas[1]]; } var column = new ParseSqlData() { ColumnName = datas[0].Replace("`", ""), ColumnType = datas[1], ColumnOptions = option, TableName = tableName }; tableInfoData.Columns.Add(column.ColumnName, column); } else { int maxIndex = 0; int minIndex = beforeIndex; while (queue.Count > 0) { var item = queue.Dequeue(); var index = body.IndexOf(item, minIndex); if (index == -1) { maxIndex = body.Length; } if (maxIndex <= index) { maxIndex = index + item.Length; } findIndex = maxIndex; //InputManager.Instance.Write(body.Substring(beforeIndex, findIndex - beforeIndex).Trim()); minIndex = findIndex; if (!Consts.MySqlReservedKeyword.ContainsKey(item)) { continue; } foreach (var key in Consts.MySqlReservedKeyword[item]) { queue.Enqueue(key); } } } findIndex++; } } } return(true); }