public static void kToSqlCreateTable(string tblsFile, string sqlFile = cSqlCreateTblScriptFile)
{
string tSqlFile = string.Format("{0}k{1}.{2}", cScriptHomePath, sqlFile, cSqlFileExt);
SqlScriptLogger scriptLogger = new SqlScriptLogger(tSqlFile);
scriptLogger.kWriteLine("USE akm_m6dtx;" + Environment.NewLine);
Console.WriteLine(string.Format("Creating {0} . . .", tSqlFile));
string tCsvFile = string.Format("{0}{1}_{2}.{3}", cDataHomePath, tblsFile, cDataPostfix, cCsvFileExt);
CsvFileReader reader = new CsvFileReader(tCsvFile);
Console.WriteLine(string.Format("Opening {0} . . .", tCsvFile));
Console.WriteLine("[Press the ENTER key to begin . . .]");
Console.ReadKey();
CsvRow row = new CsvRow();
bool isHeader = true;
while (reader.ReadRow(row))
{
if (isHeader)
{
isHeader = false;
continue;
}
foreach (string tblSchema in row)
{
string script = string.Empty;
try
{
string tTblSchemaFile = string.Format("{0}_{1}", tblSchema, cSchemaPostfix);
scriptLogger.kWriteLineHeader(tblSchema);
scriptLogger.kWriteLine(2);
scriptLogger.kWriteLine(SqlScriptTrans.kToSqlCreateTable(tTblSchemaFile, string.Format("{0}{1}.{2}", cDataHomePath, tTblSchemaFile, cCsvFileExt)));
scriptLogger.kWriteLine(2);
scriptLogger.kWriteLineFooter();
}
catch (FileNotFoundException)
{
scriptLogger.kWriteLine("## [ERROR::" + tblSchema + "] The table does not exists in the database.");
continue;
}
scriptLogger.kWriteLine(4);
}
}
reader.Close();
scriptLogger.kClose();
}
public static void kToSqlInsert(string tblsFile, string sqlFile = cSqlInsertScriptFile)
{
string tSqlFile = string.Format("{0}{1}.{2}", cScriptHomePath, sqlFile, cSqlFileExt);
// Console.WriteLine(string.Format("Creating {0} . . .", tSqlFile));
string tCsvFile = string.Format("{0}{1}_{2}.{3}", cDataHomePath, tblsFile, cDataPostfix, cCsvFileExt);
CsvFileReader reader = new CsvFileReader(tCsvFile);
Console.WriteLine(string.Format("Opening {0} . . .", tCsvFile));
Console.WriteLine("[Press the ENTER key to begin . . .]");
Console.ReadKey();
CsvRow row = new CsvRow();
bool isHeader = true;
while (reader.ReadRow(row))
{
if (isHeader)
{
isHeader = false;
continue;
}
foreach (string tblName in row)
{
string tTblDataFile = string.Format("{0}_{1}.{2}", tblName, cDataPostfix, cCsvFileExt);
try
{
SqlScriptTrans.kToSqlInsertTable(tTblDataFile, string.Format("{0}{1}", cDataHomePath, tTblDataFile));
}
catch (FileNotFoundException)
{
Console.WriteLine("[ERROR::" + tblName + "]Reading a non-existent physical database table.");
continue;
}
}
}
reader.Close();
}
public static void kToSqlInsertTable(string tblFileName, string csvFilePath)
{
string cmdText = string.Empty;
using (CsvFileReader reader = new CsvFileReader(csvFilePath))
{
CsvRow row = new CsvRow();
bool isHeader = true;
string tTblName = tblFileName.Substring(0, tblFileName.LastIndexOf("_data"));
string tblFilePath = string.Format("{0}k{1}.{2}", SqlScriptGen.cScriptHomePath, tTblName, SqlScriptGen.cSqlFileExt);
SqlScriptLogger scriptLogger = new SqlScriptLogger(tblFilePath);
scriptLogger.kWriteLine("USE akm_m6dtx;" + Environment.NewLine);
scriptLogger.kWriteLineHeader(tblFileName);
scriptLogger.kWriteLine(2);
cmdText = string.Format("INSERT INTO {0} (", tTblName) + Environment.NewLine;
while (reader.ReadRow(row))
{
string insertCmd = string.Empty;
/**
* Filter the columns of the table and translate to its template header script.
*/
if (isHeader)
{
isHeader = false;
row.ForEach(col => { cmdText += string.Format("`{0}`,", col); });
cmdText = cmdText.Remove(cmdText.LastIndexOf(","), 1);
cmdText += ") VALUES (" + Environment.NewLine;
}
else
{
insertCmd = cmdText;
foreach (string col in row)
{
// TODO: Improve filtering by using Regex
if ((col.IndexOf("AM") > 0 || col.IndexOf("PM") > 0) &&
col.IndexOf(@"/") > 0 &&
col.IndexOf(@":") > 0)
{
insertCmd += string.Format(@"STR_TO_DATE('{0}', '%m/%d/%Y %l:%i:%s %p'),", col);
}
else
insertCmd += string.Format("\t'{0}',", col.Replace('\'', '^')) + Environment.NewLine;
}
insertCmd = insertCmd.Remove(insertCmd.LastIndexOf(","), 1);
insertCmd += Environment.NewLine + ");";
Console.Write("c o d i n g . . . t r a n s c o d i n g . . . t r a n s . . . . . ");
scriptLogger.kWriteLine(insertCmd);
Console.WriteLine("DONE !!!");
}
}
scriptLogger.kWriteLine(2);
scriptLogger.kWriteLineFooter();
scriptLogger.kClose();
reader.Close();
}
}
public static string kToSqlCreateTable(string tblFileName, string csvFilePath)
{
string cmdText = string.Empty;
using (CsvFileReader reader = new CsvFileReader(csvFilePath))
{
CsvRow row = new CsvRow();
bool isHeader = true;
string tTblFile = tblFileName.Substring(0, tblFileName.IndexOf("_"));
Console.WriteLine("c o d i n g . . . t r a n s c o d i n g . . . t r a n s . . . . . ");
cmdText += string.Format("DROP TABLE IF EXISTS `{0}`;", tTblFile) + Environment.NewLine;
cmdText += string.Format("CREATE TABLE IF NOT EXISTS `{0}` (", tTblFile) + Environment.NewLine;
while (reader.ReadRow(row))
{
if (isHeader)
{
isHeader = false;
continue;
}
string tRowType = kToMySQLTypeStatement(row);
cmdText += "\t" + (tRowType.Equals(",") ? "" : tRowType) + Environment.NewLine;
}
cmdText = cmdText.Remove(cmdText.LastIndexOf(","), 1);
cmdText += ") ENGINE=InnoDB DEFAULT CHARSET=utf8;" + Environment.NewLine;
Console.WriteLine(cmdText);
Console.WriteLine("DONE !!!");
reader.Close();
}
return cmdText;
}