private void MakeTableScript()
{
//테이블 변경점을 찾아보자
foreach (var table in _scriptTables)
{
if (_dbTables.ContainsKey(table.Key))
{
var connectDbTable = _dbTables[table.Key];
foreach (var column in table.Value.Columns)
{
if (connectDbTable.Columns.ContainsKey(column.Key.ToLower()))
{
//변경
var connectDbColumn = connectDbTable.Columns[column.Key.ToLower()];
if (!connectDbColumn.TypeCompare(column.Value))
{
MySqlParseHelper.AlterMySqlColumn(column.Value);
InputManager.Instance.WriteTrace($"Table[{column.Value.TableName}] ColumnName[{column.Value.ColumnName}] [{connectDbColumn.ColumnType}] 에서 [{column.Value.ColumnType}] 으로 변경됩니다.");
InputManager.Instance.WriteTrace("");
}
}
else
{
if (column.Value.CommandType == CommandType.Add && column.Value.ClassificationType == ClassificationType.Columns)
{
InputManager.Instance.WriteTrace($"Table[ {column.Value.TableName} ] ColumnName[ {column.Value.ColumnName} ] (이)가 추가됩니다.");
InputManager.Instance.WriteTrace("");
var output = MySqlParseHelper.AlterMySqlColumn(column.Value);
}
else if (column.Value.CommandType == CommandType.Change && column.Value.ClassificationType == ClassificationType.Columns)
{
InputManager.Instance.WriteTrace($"Table[ {column.Value.TableName} ] ColumnName[ {column.Value.ColumnName} ] 에서 ColumnName[ {column.Value.ChangeColumnName} ] [ {column.Value.ColumnType} ] 으로 변경됩니다.");
InputManager.Instance.WriteTrace("");
MySqlParseHelper.AlterMySqlColumnChange(column.Value);
}
else
{
InputManager.Instance.WriteError($"Table[ {column.Value.TableName} ] ColumnName[ {column.Value.ColumnName} ] [ {column.Value.ColumnType} ]");
throw new Exception("Unknown Error");
}
}
}
}
}
}
private void LoadSp()
{
_outputOtherBuffer.AppendLine();
InputManager.Instance.WriteInfo($">>>Load Stored Procedure Files : {_config.StoredProcedurePath}");
if (string.IsNullOrEmpty(_config.StoredProcedurePath))
{
return;
}
var directoryInfo = new DirectoryInfo(_config.StoredProcedurePath);
var files = GetSqlFiles(directoryInfo);
for (int i = 0; i < files.Count; i++)
{
InputManager.Instance.Write($"Read File : {files[i].Name}");
using (var sr = new StreamReader(files[i].OpenRead()))
{
var sql = sr.ReadToEnd();
sr.Close();
if (string.IsNullOrEmpty(sql))
{
throw new Exception($"{files[i].Name} : 쿼리 문이 없습니다.");
}
if (MySqlParseHelper.CheckConnectDatabase(sql, out string database))
{
if (!database.ToUpper().Equals(connectedDatabaseName))
{
InputManager.Instance.WriteWarning($"File { files[i].Name } Database [ {connectedDatabaseName } ] 과 [ { database } ](이)가 다릅니다.");
InputManager.Instance.WriteWarning("");
continue;
}
}
_outputOtherBuffer.AppendLine(sql);
_outputOtherBuffer.AppendLine();
}
}
}
private void LoadTable()
{
//var currentDBTables = GetMySqlTableInfo(new MySqlConnection(_config.SqlConnect));
InputManager.Instance.WriteInfo($">>>>Load Table Files : {_config.TablePath}");
if (string.IsNullOrEmpty(_config.TablePath))
{
return;
}
_outputTableBuffer.AppendLine($"DELIMITER $$");
_outputTableBuffer.AppendLine($"DROP PROCEDURE IF EXISTS `make_create_table`;");
_outputTableBuffer.AppendLine($"CREATE PROCEDURE `make_create_table`() BEGIN");
var directoryInfo = new DirectoryInfo(_config.TablePath);
var files = GetSqlFiles(directoryInfo);
for (int i = 0; i < files.Count; i++)
{
InputManager.Instance.Write($"Read File : {files[i].Name}");
using (var sr = new StreamReader(files[i].OpenRead()))
{
var sql = sr.ReadToEnd();
sr.Close();
if (string.IsNullOrEmpty(sql))
{
throw new Exception($"{files[i].Name} : 쿼리 문이 없습니다.");
}
if (MySqlParseHelper.CheckConnectDatabase(sql, out string database))
{
if (!database.ToUpper().Equals(connectedDatabaseName))
{
InputManager.Instance.WriteWarning($"File { files[i].Name } Database [ {connectedDatabaseName } ] 과 [ { database } ](이)가 다릅니다.");
InputManager.Instance.WriteWarning("");
continue;
}
}
if (MySqlParseHelper.ParseCreateTableCommnad(sql, out TableInfoModel parseTableData))
{
if (!_dbTables.ContainsKey(parseTableData.TableName.ToLower()))
{
_outputTableBuffer.AppendLine(sql);
InputManager.Instance.WriteTrace($"Table[ {parseTableData.TableName} ] (이)가 생성됩니다.");
}
_scriptTables.Add(parseTableData.TableName, parseTableData);
//기존 코드 => Craete 기준으로 변경 감지
// var dbColumns = currentDBTables[parseTableData.TableName.ToLower()].Columns;
// foreach (var columnModel in parseTableData.Columns)
// {
// var dbColumn = dbColumns.Where(r => r.NameCompare(columnModel)).FirstOrDefault();
// if (dbColumn == null)
// {
// _buffer.AppendLine(SqlParseHelper.AlterMySqlColumn(columnModel, AlterTableType.Add));
// InputManager.Instance.WriteTrace($"Table[{parseTableData.TableName}] ColumnName[{columnModel.ColumnName}] (이)가 추가됩니다.");
// }
// else if (!dbColumn.TypeCompare(columnModel))
// {
// _buffer.AppendLine(SqlParseHelper.AlterMySqlColumn(columnModel, AlterTableType.Modify));
// InputManager.Instance.WriteTrace($"Table[{parseTableData.TableName}] ColumnName[{columnModel.ColumnName}] [{dbColumn.ColumnType}] 에서 [{columnModel.ColumnType}] 으로 변경됩니다.");
// }
// }
//foreach (var column in dbColumns)
//{
// if (parseTableData.Columns.Any(r => r.NameCompare(column)))
// continue;
// _buffer.AppendLine(SqlParseHelper.AlterMySqlColumn(column, AlterTableType.Drop));
// InputManager.Instance.WriteTrace($"Table[{parseTableData.TableName}] ColumnName[{column.ColumnName}] (이)가 삭제됩니다.");
//}
}
}
}
_outputTableBuffer.AppendLine($"END $$;");
_outputTableBuffer.AppendLine($"DELIMITER ;");
_outputTableBuffer.AppendLine($"CALL `make_create_table`();");
_outputTableBuffer.AppendLine($"DROP PROCEDURE IF EXISTS `make_create_table`;");
_outputTableBuffer.AppendLine();
}
private void LoadAlterTable()
{
InputManager.Instance.WriteInfo($">>>>Load AlterTable Files : {_config.AlterTablePath}");
if (string.IsNullOrEmpty(_config.AlterTablePath))
{
return;
}
_outputTableBuffer.AppendLine($"DELIMITER $$");
_outputTableBuffer.AppendLine($"DROP PROCEDURE IF EXISTS `make_alter_table`;");
_outputTableBuffer.AppendLine($"CREATE PROCEDURE `make_alter_table`() BEGIN");
var directoryInfo = new DirectoryInfo(_config.AlterTablePath);
var files = GetSqlFiles(directoryInfo);
for (int i = 0; i < files.Count; i++)
{
InputManager.Instance.Write($"Read File : {files[i].Name}");
using (var sr = new StreamReader(files[i].OpenRead()))
{
var sql = sr.ReadToEnd();
sr.Close();
if (string.IsNullOrEmpty(sql))
{
throw new Exception($"{files[i].Name} : 쿼리 문이 없습니다.");
}
if (MySqlParseHelper.CheckConnectDatabase(sql, out string database))
{
if (!database.ToUpper().Equals(connectedDatabaseName))
{
continue;
}
}
if (MySqlParseHelper.ParseAlterCommnad(sql, out List <ParseSqlData> parseSqlDatas))
{
foreach (var data in parseSqlDatas)
{
if (data.ClassificationType == ClassificationType.Columns)
{
if (data.CommandType == CommandType.Alter)
{
InputManager.Instance.WriteTrace($"Table[ {data.TableName} ] [ {data.Command} ] (이)가 실행됩니다.");
InputManager.Instance.WriteTrace("");
_outputOtherBuffer.AppendLine(MySqlParseHelper.CreateSqlCommand(data));
}
else if (_scriptTables.ContainsKey(data.TableName))
{
if (_scriptTables[data.TableName].Columns.ContainsKey(data.ColumnName))
{
if (data.CommandType == CommandType.Change)
{
_scriptTables[data.TableName].Columns.Remove(data.ColumnName);
_scriptTables[data.TableName].Columns.Add(data.ChangeColumnName, data);
}
if (data.CommandType == CommandType.Drop)
{
_scriptTables[data.TableName].Columns.Remove(data.ColumnName);
}
}
else
{
_scriptTables[data.TableName].Columns.Add(data.ColumnName, data);
}
}
//Create Table에 정보가 없는 경우 현재 접속한 DB 에서 Table 정보를 가져왔음.
else if (_dbTables.ContainsKey(data.TableName) && !_scriptTables.ContainsKey(data.TableName))
{
var option = _dbTables[data.TableName].TableOption;
_scriptTables.Add(data.TableName, new TableInfoModel()
{
Columns = _dbTables[data.TableName].Columns.ToDictionary(r => r.Key, r => r.Value),
TableName = data.TableName,
TableOption = _dbTables[data.TableName].TableOption
});
}
}
else
{
if (string.IsNullOrEmpty(data.ColumnName))
{
InputManager.Instance.WriteWarning($"Table[ {data.TableName} ] [ {data.Command} ] 명시적 이름이 없습니다. 이미 변경이 이뤄졌을 수도 있습니다.");
Console.ReadKey();
InputManager.Instance.WriteTrace("");
continue;
}
var index = _dbIndexTables[data.TableName].Find(r => r.IndexName == data.ColumnName);
if (index == null && data.CommandType == CommandType.Add)
{
InputManager.Instance.WriteTrace($"Table[ {data.TableName} ] Name[ {data.ColumnName} ] [ {data.Command} ] (이)가 추가됩니다.");
InputManager.Instance.WriteTrace("");
_dbIndexTables[data.TableName].Add(new IndexModel()
{
IndexName = data.ColumnName,
TableName = data.TableName
});
_outputTableBuffer.AppendLine(MySqlParseHelper.CreateSqlCommand(data));
}
else if (index != null && data.CommandType == CommandType.Drop)
{
InputManager.Instance.WriteTrace($"Table[ {data.TableName} ] Name[ {data.Command} ] (이)가 제거됩니다.");
InputManager.Instance.WriteTrace("");
_dbIndexTables[data.TableName].Add(new IndexModel()
{
IndexName = data.ColumnName,
TableName = data.TableName
});
_outputTableBuffer.AppendLine(MySqlParseHelper.CreateSqlCommand(data));
}
else if (data.CommandType == CommandType.Alter)
{
InputManager.Instance.WriteTrace($"Table[ {data.TableName} ] [ {data.Command} ] (이)가 실행됩니다.");
InputManager.Instance.WriteTrace("");
_outputTableBuffer.AppendLine(MySqlParseHelper.CreateSqlCommand(data));
}
}
}
}
}
}
_outputTableBuffer.AppendLine($"END $$;");
_outputTableBuffer.AppendLine($"DELIMITER ;");
_outputTableBuffer.AppendLine($"CALL `make_alter_table`();");
_outputTableBuffer.AppendLine($"DROP PROCEDURE IF EXISTS `make_alter_table`;");
_outputTableBuffer.AppendLine();
}
