/// <summary>
/// Use the class Table_Structure to create the create query on SQL
/// WARNING all fields are cast on VARCHAR to make load easier (If not wanted, please see get_vertica_ddl(Table_Structure tbl_struct))
/// WARNING my DB target is Vertica, the length are on octet and UTF-8 so I multiply by 4 to take into account all the characters
/// </summary>
/// <param name="tbl_struct">Table's structure with table's name, table's schema and all fields</param>
/// <returns>The create query on SQL for the input structure</returns>
public static string get_vertica_ddl_varchar(Table_Structure tbl_struct)
{
string ddl_tmp = "CREATE TABLE " + tbl_struct.schema + "." + tbl_struct.name + " (";
List <string> list_field_tmp = new List <string>();
foreach (Tuple <String, String, String> t in tbl_struct.structure)
{
switch (t.Item2.ToUpper())
{
case "CHAR":
case "VARCHAR":
list_field_tmp.Add(t.Item1 + " VARCHAR(" + Int32.Parse(t.Item3) * 4 + ")");
break;
case "DECIMAL":
String[] s_tmp = t.Item3.Split(new[] { ',' });
list_field_tmp.Add((Int32.Parse(s_tmp[0]) + Int32.Parse(s_tmp[1]) + 2).ToString());
break;
default:
list_field_tmp.Add(t.Item1 + " VARCHAR(" + t.Item3 + ")");
break;
}
}
ddl_tmp += "\r\n\t" + String.Join(",\r\n\t", list_field_tmp) + "\r\n);\n";
return(ddl_tmp);
}
/// <summary>
/// Use the class Table_Structure to create the create query on SQL
/// WARNING my DB target is Vertica, the length are on octet and UTF-8 so I multiply by 4 to take into account all the characters
/// </summary>
/// <param name="tbl_struct">Table's structure with table's name, table's schema and all fields</param>
/// <returns>The create query on SQL for the input structure</returns>
public static string get_vertica_ddl(Table_Structure tbl_struct)
{
List <string> list_field_tmp = new List <string>();
foreach (Tuple <string, string, string> t in tbl_struct.structure)
{
//TODO : check vertica type
switch (t.Item2)
{
case "smallint":
list_field_tmp.Add(t.Item1 + " smallint");
break;
case "integer":
list_field_tmp.Add(t.Item1 + " INT");
break;
case "date":
list_field_tmp.Add(t.Item1 + " DATE");
break;
case "time":
list_field_tmp.Add(t.Item1 + " TIME");
break;
case "timestamp":
list_field_tmp.Add(t.Item1 + " TIMESTAMP");
break;
case "char":
list_field_tmp.Add(t.Item1 + " VARCHAR(" + Int32.Parse(t.Item3) * 4 + ")");
break;
case "varchar":
list_field_tmp.Add(t.Item1 + " VARCHAR(" + Int32.Parse(t.Item3) * 4 + ")");
break;
default:
list_field_tmp.Add(t.Item1 + ' ' + t.Item2 + '(' + t.Item3 + ")");
break;
}
}
string ddl_tmp = "CREATE TABLE " + tbl_struct.schema + "." + tbl_struct.name + " (";
ddl_tmp += "\r\n\t" + String.Join(",\r\n\t", list_field_tmp);
if (tbl_struct.keys.Count > 0)
{
ddl_tmp += "\r\n\tPRIMARY KEY (" + String.Join(",\r\n\t\t", tbl_struct.keys) + ")";
}
return(ddl_tmp + "\r\n);\n");
}
/// <summary>
/// Methods to convert a xml file which describe data to sql queries
/// </summary>
/// <param name="xml_path">xml which describe the data</param>
/// <param name="sql_path">sql file that will contain create SQL queries</param>
public static void xml_to_sql(string xml_path, string sql_path)
{
//Clean the output file
System.IO.File.WriteAllText(sql_path, string.Empty);
//Open the xml with all the table
using (XmlReader xmlReader = XmlReader.Create(xml_path))
{
//Read the xml file
while (xmlReader.Read())
{
//Processing only when we find the "table" element's start
if (xmlReader.IsStartElement() && xmlReader.Name == "table")
{
try
{
Table_Structure tbl_struct_tmp = new Table_Structure(xmlReader["name"], xmlReader["schema"]);
//Add all element's field to the Table_Structure after verifying that we have name/type/length
while (xmlReader.Read() && xmlReader.Name != "table" && xmlReader.NodeType != XmlNodeType.EndElement)
{
if (xmlReader.Name == "field" &&
xmlReader["name"] != null &&
xmlReader["type"] != null &&
xmlReader["length"] != null)
{
tbl_struct_tmp.structure.Add(new Tuple <string, string, string>(
xmlReader["name"],
xmlReader["type"],
xmlReader["length"]));
}
}
var sql_create = get_vertica_ddl_varchar(tbl_struct_tmp);
//var sql_create = get_vertica_ddl_varchar(tbl_struct_tmp);
append_sql_to_file(sql_path, sql_create);
}
catch (Exception err)
{
//TODO : gérer un fichier de rejet des tables "out"
Console.WriteLine(err);
}
}
}
}
}