public SqlGenerator(ISessionFactoryImplementor sfi, ITreeNodeStream input)
: this(input)
{
parseErrorHandler = new ErrorCounter();
sessionFactory = sfi;
writer = new DefaultWriter(this);
}
void ISqlElement.ToString(ISqlWriter writer)
{
writer.Write(Expression);
writer.Write(" ");
writer.Write(Ascending ? "ASC" : "DESC");
}
public void TestSetup()
{
_service = new SqlCeSchemaService {ProviderName = _providerName};
_model = _service.GetDbObjectModel(_connStr);
_sqlSW = new StringWriter();
_sqlWriter = new SqlWriter();
}
public static void Deindent(this ISqlWriter writer, int value)
{
if (writer.Indentation - value >= 0)
{
writer.Indentation = writer.Indentation - value;
}
}
public SqlGenerator(ISessionFactoryImplementor sfi, ITreeNodeStream input)
: this(input)
{
parseErrorHandler = new ErrorCounter();
sessionFactory = sfi;
writer = new DefaultWriter(this);
}
void ISqlElement.ToString(ISqlWriter writer)
{
writer.Write(Expression);
writer.Write(" ");
writer.Write(Ascending ? "ASC" : "DESC");
}
private void EndQuery()
{
SqlString sqlString = GetSqlStringWithLimitsIfNeeded((QueryWriter)writer);
writer = outputStack[0];
outputStack.RemoveAt(0);
Out(sqlString);
}
void ISqlElement.ToString(ISqlWriter writer)
{
writer.Write("SELECT ");
if (Into != null)
{
writer.Write("INTO ");
// TODO:
writer.WriteLine();
}
else
{
var colCount = Columns.Count;
int i = -1;
foreach (var column in Columns)
{
writer.Write(column);
if (++i < colCount - 1)
{
writer.Write(", ");
}
}
if (colCount > 0)
{
writer.WriteLine();
}
}
if (From != null)
{
writer.Indent(4);
writer.Write(From);
writer.WriteLine();
writer.Deindent(4);
}
if (Where != null)
{
writer.Indent(4);
writer.Write("WHERE ");
writer.Write(Where);
writer.WriteLine();
writer.Deindent(4);
}
if (Having != null)
{
writer.Indent(4);
writer.Write("HAVING ");
writer.Write(Having);
writer.WriteLine();
writer.Deindent(4);
}
}
void ISqlElement.ToString(ISqlWriter writer)
{
writer.Write(Expression);
if (Alias != null)
{
writer.Write(" AS ");
writer.Write(Alias);
}
}
private void BeginBitwiseOp(string op)
{
var function = sessionFactory.SQLFunctionRegistry.FindSQLFunction(op);
if (function == null)
{
return;
}
outputStack.Insert(0, writer);
writer = new BitwiseOpWriter();
}
/// <summary>The get summary.</summary>
/// <param name="column">The column.</param>
/// <returns>The get summary.</returns>
private string GetSummary(DbModelColumn column)
{
StringWriter stringWriter = new StringWriter();
if (_sqlWriter == null)
{
_sqlWriter = _services.Resolve <ISqlWriter>();
}
_sqlWriter.WriteSummary(stringWriter, column);
return(stringWriter.ToString());
}
public static string GetPropertyNameWithIdentifierFromExpression <TData>(this ISqlWriter <TData> serverWriter, Expression expression)
{
var exp = expression.GetMemberExpression();
if (!(exp is MemberExpression))
{
return(string.Empty);
}
var table = EntityTableCacheHelper.TryGetTable(((MemberExpression)exp).Expression.Type);
var member = ((MemberExpression)exp).Member;
return($"{table.Identifier}.{serverWriter.StartQuotationMark}{table.Columns[member.Name].ColumnName}{serverWriter.EndQuotationMark}");
}
protected override void WriteTo(ISqlWriter writer)
{
writer.Write("CASE ");
writer.Write(IfTrue);
writer.Write(" WHEN ");
writer.Write(Test);
if (IfFalse != null) {
writer.Write(" THEN ");
writer.Write(IfFalse);
}
writer.Write(" END");
}
private void EndQuery()
{
QueryWriter queryWriter = ((QueryWriter)writer);
SqlString sqlString = queryWriter.ToSqlString();
if (queryWriter.Take.HasValue || queryWriter.Skip.HasValue)
{
sqlString = sessionFactory.Dialect.GetLimitString(sqlString, queryWriter.Skip ?? 0, queryWriter.Take ?? int.MaxValue);
}
writer = outputStack[0];
outputStack.RemoveAt(0);
Out(sqlString);
}
protected override void WriteTo(ISqlWriter writer)
{
writer.Write("CASE ");
writer.Write(IfTrue);
writer.Write(" WHEN ");
writer.Write(Test);
if (IfFalse != null)
{
writer.Write(" THEN ");
writer.Write(IfFalse);
}
writer.Write(" END");
}
/// <summary>
/// Serializes to the specified writer.
/// </summary>
/// <param name="writer">The writer.</param>
public void Serialize(ISqlWriter writer)
{
if (writer == null)
{
throw new ArgumentNullException("writer");
}
writer.WriteBeginInsert();
writer.WriteTelemetryType("metric");
writer.WriteTelemetryName(this.Name);
writer.WriteTimestamp(this.Timestamp);
writer.WriteContext(this.Context);
writer.WriteProperties(this.Context.Properties);
writer.WriteValue(this.Value);
writer.WriteEndInsert();
}
private void EndBitwiseOp(string op)
{
ISQLFunction function = sessionFactory.SQLFunctionRegistry.FindSQLFunction(op);
if (function == null)
{
return;
}
var functionArguments = (BitwiseOpWriter)writer;
writer = outputStack[0];
outputStack.RemoveAt(0);
Out(function.Render(functionArguments.Args, sessionFactory));
}
/// <summary>
/// Serializes to the specified writer.
/// </summary>
/// <param name="writer">The writer.</param>
public void Serialize(ISqlWriter writer)
{
if (writer == null)
{
throw new ArgumentNullException("writer");
}
writer.WriteBeginInsert();
writer.WriteTelemetryType("exception");
writer.WriteTelemetryName(this.Exception.Source);
writer.WriteMessage(this.Exception.Message);
writer.WriteTimestamp(this.Timestamp);
writer.WriteContext(this.Context);
writer.WriteProperties(this.Context.Properties);
writer.WriteMetrics(this.Metrics);
writer.WriteEndInsert();
}
private void BeginFunctionTemplate(IASTNode m, IASTNode i)
{
var methodNode = (MethodNode)m;
ISQLFunction template = methodNode.SQLFunction;
if (template == null)
{
// if template is null we just write the function out as it appears in the hql statement
Out(i);
Out("(");
}
else
{
// this function has a template -> redirect output and catch the arguments
outputStack.Insert(0, writer);
writer = new FunctionArguments();
}
}
private void EndFunctionTemplate(IASTNode m)
{
var methodNode = (MethodNode)m;
ISQLFunction template = methodNode.SQLFunction;
if (template == null)
{
Out(")");
}
else
{
// this function has a template -> restore output, apply the template and write the result out
var functionArguments = (FunctionArguments)writer; // TODO: Downcast to avoid using an interface? Yuck.
writer = outputStack[0];
outputStack.RemoveAt(0);
Out(template.Render(functionArguments.Args, sessionFactory));
}
}
protected override void WriteTo(ISqlWriter writer)
{
writer.Write(SelectExpression);
if (OrderBy.Count > 0)
{
writer.Write("ORDER BY ");
var orderByCount = OrderBy.Count;
var i = -1;
foreach (var column in OrderBy)
{
writer.Write(column);
if (++i < orderByCount - 1)
{
writer.Write(", ");
}
}
}
}
protected virtual void WriteTo(ISqlWriter writer)
{
}
void ISqlElement.ToString(ISqlWriter writer)
{
writer.Write(Expression);
if (Alias != null) {
writer.Write(" AS ");
writer.Write(Alias);
}
}
void ISqlElement.ToString(ISqlWriter writer)
{
writer.Write(":");
writer.Write(VariableName);
}
private void EndQuery()
{
QueryWriter queryWriter = ((QueryWriter) writer);
SqlString sqlString = queryWriter.ToSqlString();
if (queryWriter.Take.HasValue || queryWriter.Skip.HasValue)
sqlString = sessionFactory.Dialect.GetLimitString(sqlString, queryWriter.Skip ?? 0, queryWriter.Take ?? int.MaxValue);
writer = outputStack[0];
outputStack.RemoveAt(0);
Out(sqlString);
}
private void StartQuery()
{
outputStack.Insert(0, writer);
writer = new QueryWriter();
}
protected override void WriteTo(ISqlWriter writer)
{
writer.Write("(");
writer.Write(Operand);
writer.Write(")");
}
private void StartQuery()
{
outputStack.Insert(0, writer);
writer = new QueryWriter();
}
public QueryHelperMsSql(AppMetadata schema, string connectionString)
: base(schema, ProviderName, connectionString)
{
TypeConverter = new SqlTypeConverterMsSql();
SqlWriter = new SqlWriterMsSql();
}
protected virtual void WriteTo(ISqlWriter writer)
{
writer.Write(Name);
}
/// <summary>The get summary.</summary>
/// <param name="column">The column.</param>
/// <returns>The get summary.</returns>
private string GetSummary(DbModelColumn column)
{
StringWriter stringWriter = new StringWriter();
if (_sqlWriter == null)
{
_sqlWriter = _services.Resolve<ISqlWriter>();
}
_sqlWriter.WriteSummary(stringWriter, column);
return stringWriter.ToString();
}
/// <summary>The settings_ database connection reset.</summary>
/// <param name="sender">The sender.</param>
/// <param name="e">The e.</param>
private void Settings_DatabaseConnectionReset(object sender, EventArgs e)
{
_metaDataService = null;
_sqlWriter = null;
ExecLoadDatabaseDetails();
}
public Converter(DiffInfo diff, ISqlWriter writer)
{
this.Diff = diff;
this.Writer = writer;
}
protected override void WriteTo(ISqlWriter writer)
{
writer.Write("(");
writer.Write(Operand);
writer.Write(")");
}
void ISqlElement.ToString(ISqlWriter writer)
{
writer.Write("SELECT ");
if (Into != null) {
writer.Write("INTO ");
// TODO:
writer.WriteLine();
} else {
var colCount = Columns.Count;
int i = -1;
foreach (var column in Columns) {
writer.Write(column);
if (++i < colCount - 1)
writer.Write(", ");
}
if (colCount > 0)
writer.WriteLine();
}
if (From != null) {
writer.Indent(4);
writer.Write(From);
writer.WriteLine();
writer.Deindent(4);
}
if (Where != null) {
writer.Indent(4);
writer.Write("WHERE ");
writer.Write(Where);
writer.WriteLine();
writer.Deindent(4);
}
if (Having != null) {
writer.Indent(4);
writer.Write("HAVING ");
writer.Write(Having);
writer.WriteLine();
writer.Deindent(4);
}
}
public LocationListDataProvider(ISqlReader reader, ISqlWriter writer)
{
Reader = reader;
Writer = writer;
}
private void EndFunctionTemplate(IASTNode m)
{
var methodNode = (MethodNode) m;
ISQLFunction template = methodNode.SQLFunction;
if (template == null)
{
Out(")");
}
else
{
// this function has a template -> restore output, apply the template and write the result out
var functionArguments = (FunctionArguments) writer; // TODO: Downcast to avoid using an interface? Yuck.
writer = outputStack[0];
outputStack.RemoveAt(0);
Out(template.Render(functionArguments.Args, sessionFactory));
}
}
protected override void WriteTo(ISqlWriter writer)
{
writer.Write(Left);
writer.Write(" {0} ", Operator.AsString());
writer.Write(Right);
}
public static void Main(string[] args)
{
#region Debug
//Console.ReadLine();
//AddArgToDebug(ref args, "-n", "teste1");
//AddArgToDebug(ref args, "-n", "teste2");
//AddArgToDebug(ref args, "-n", "teste3");
//AddArgToDebug(ref args, "-n", "");
//AddArgToDebug(ref args, "-n", null);
//AddArgToDebug(ref args, "-r", "2");
//AddArgToDebug(ref args, "-r", "3");
#endregion
#region get parameters
var showHelp = false;
var path = "";
var sqlWriterName = "sqlserver";
var tableName = "Table1";
var maxBulk = 25;
var insertStringFormat = SqlInsertStringFormat.None;
var hasHeader = true;
var delimiter = ";";
var count = -1;
var paramsParse = new OptionSet()
{
{ "p|path=", "Set the file path to convert to SQL.", paramValue => path = paramValue },
{ "delimiter=", "Set the delimiter columns, default is ';'.", paramValue => delimiter = paramValue },
{ "count=", "Set the count line to generate", (int paramValue) => count = paramValue },
{ "dbname=", "Set the database name to determine the type of output SQL, the options are: \r\n [sqlserver], \r\n [mysql].", paramValue => sqlWriterName = paramValue },
{ "tname=", "Set the table name to generate, default is '#Table1'.", paramValue => tableName = paramValue },
{ "maxbulk=", "Set the amount of 'values' that will be grouped in 'inserts' section, default is '" + maxBulk + "'.", (int paramValue) => maxBulk = paramValue },
{ "insert-format=", "Set the output format to 'insert values', default is 'None' and the options are: \r\n [none], \r\n [break-line], \r\n [break-line-and-show-columns]"
, paramValue =>
{
if (paramValue == "break-line")
{
insertStringFormat = SqlInsertStringFormat.BreakLineForEachColumn;
}
else if (paramValue == "break-line-and-show-columns")
{
insertStringFormat = SqlInsertStringFormat.BreakLineAndShowColumnNameForEachColumn;
}
else
{
insertStringFormat = SqlInsertStringFormat.None;
}
} },
{ "not-header", "Set if the CSV hasen't header", paramValue => hasHeader = false },
{ "v", "Increase debug", paramValue => { if (paramValue != null)
{
++verbosity;
}
} },
{ "h|?|help", "Show the help.", paramValue => showHelp = paramValue != null },
};
#endregion
List <string> extra;
try
{
extra = paramsParse.Parse(args);
}
catch (OptionException e)
{
Console.Write("error: ");
Console.WriteLine(e.Message);
Console.WriteLine("Try `csvtosql --help' for more information.");
return;
}
if (extra.Count > 0)
{
Console.WriteLine();
Console.WriteLine("**There are no recognized parameters:");
Console.WriteLine();
foreach (var e in extra)
{
Console.WriteLine(e);
}
Console.WriteLine();
Console.WriteLine("** All args:");
Console.WriteLine();
foreach (var e in args)
{
Console.WriteLine(e);
}
return;
}
if (showHelp)
{
ShowHelp(paramsParse);
return;
}
ISqlWriter sqlWriter = null;
if (sqlWriterName == "sqlserver")
{
sqlWriter = new SqlServerWriter();
}
else if (sqlWriterName == "mysql")
{
sqlWriter = new MySQLWriter();
}
else
{
Console.Write("The parameter 'dbname' was not found");
return;
}
try
{
TextReader textReader = null;
if (Console.IsInputRedirected)
{
textReader = Console.In;
}
else
{
textReader = GetTextReader(path);
}
var sqlTable = SqlTable.CsvToSqlTable(textReader, sqlWriter, hasHeader, delimiter, count);
var output = new StringBuilder();
if (sqlTable != null)
{
output = sqlWriter.GenerateTableWithInserts(sqlTable, tableName, maxBulk, insertStringFormat);
}
else
{
output.Append("The 'CSV' is empty");
}
Console.OutputEncoding = Encoding.UTF8;
int iChunkSize = 50000000;
for (int i = 0; i < output.Length; i += iChunkSize)
{
int A_Size = output.Length - i >= iChunkSize ? iChunkSize : Math.Abs(output.Length - i);
char[] out_i = new char[A_Size];
output.CopyTo(i, out_i, 0, A_Size);
Console.Write(out_i);
}
}
catch (Exception ex)
{
Console.Write("error: ");
Console.WriteLine(ex.Message);
Console.WriteLine("Try `csvtosql --help' for more information.");
return;
}
}
void ISqlElement.ToString(ISqlWriter writer)
{
WriteTo(writer);
}
private void BeginBitwiseOp(string op)
{
var function = sessionFactory.SQLFunctionRegistry.FindSQLFunction(op.ToLowerInvariant());
if (function == null)
return;
outputStack.Insert(0, writer);
writer = new BitwiseOpWriter();
}
private void BeginFunctionTemplate(IASTNode m, IASTNode i)
{
var methodNode = (MethodNode) m;
ISQLFunction template = methodNode.SQLFunction;
if (template == null)
{
// if template is null we just write the function out as it appears in the hql statement
Out(i);
Out("(");
}
else
{
// this function has a template -> redirect output and catch the arguments
outputStack.Insert(0, writer);
writer = new FunctionArguments();
}
}
private void EndBitwiseOp(string op)
{
ISQLFunction function = sessionFactory.SQLFunctionRegistry.FindSQLFunction(op.ToLowerInvariant());
if (function == null)
return;
var functionArguments = (BitwiseOpWriter)writer;
writer = outputStack[0];
outputStack.RemoveAt(0);
Out(function.Render(functionArguments.Args, sessionFactory));
}
private void EndQuery()
{
SqlString sqlString = GetSqlStringWithLimitsIfNeeded((QueryWriter) writer);
writer = outputStack[0];
outputStack.RemoveAt(0);
Out(sqlString);
}
protected override void WriteTo(ISqlWriter writer)
{
writer.Write(Left);
writer.Write(" {0} ", Operator.AsString());
writer.Write(Right);
}
void ISqlElement.ToString(ISqlWriter writer)
{
WriteTo(writer);
}
protected virtual void WriteTo(ISqlWriter writer)
{
var dump = new ExpressionStringWriter(writer);
dump.Dump(this);
}
protected override void WriteTo(ISqlWriter writer)
{
writer.Write(SelectExpression);
if (OrderBy.Count > 0) {
writer.Write("ORDER BY ");
var orderByCount = OrderBy.Count;
var i = -1;
foreach (var column in OrderBy) {
writer.Write(column);
if (++i < orderByCount - 1)
writer.Write(", ");
}
}
}
void ISqlElement.ToString(ISqlWriter writer)
{
writer.Write(":");
writer.Write(VariableName);
}
protected virtual void WriteTo(ISqlWriter writer)
{
}
void ISqlElement.ToString(ISqlWriter writer)
{
writer.Write(Expression);
}
public static SqlTable CsvToSqlTable(TextReader textReader, ISqlWriter sqlWriter, bool hasHeader = true, string delimiter = ",", int count = -1)
{
var config = new CsvHelper.Configuration.CsvConfiguration();
config.Delimiter = delimiter;
config.HasHeaderRecord = hasHeader;
var reader = new CsvHelper.CsvReader(textReader, config);
var rows = reader.GetRecords<object>().ToList();
textReader.Close();
SqlTable sqlTable = null;
if (rows.Count > 0)
{
sqlTable = new SqlTable();
sqlTable.Headers = new List<SqlColumnTypeDefinition>();
sqlTable.Rows = new List<SqlRow>();
var i = 0;
foreach (var row in rows)
{
if (i == count)
break;
i++;
var columns = (System.Dynamic.ExpandoObject)row;
var sqlRow = new SqlRow();
sqlTable.Rows.Add(sqlRow);
var countUnkown = 1;
foreach (var col in columns)
{
// set rows
var sqlColumn = new SqlColumn();
sqlColumn.Type = sqlWriter.SqlTypeToCSharpType(col.Value);
sqlColumn.Value = col.Value;
sqlRow.Columns.Add(sqlColumn);
// set headers
var key = col.Key;
if (string.IsNullOrWhiteSpace(key))
{
key = "Unkown" + countUnkown;
countUnkown++;
}
SqlColumnTypeDefinition sqlColumnTypeDefinition = sqlTable.Headers.FirstOrDefault(f => f.Name == key);
if (sqlColumnTypeDefinition == null)
{
sqlColumnTypeDefinition = new SqlColumnTypeDefinition();
sqlColumnTypeDefinition.Name = col.Key;
sqlColumnTypeDefinition.Type = sqlColumn.Type;
sqlTable.Headers.Add(sqlColumnTypeDefinition);
}
sqlColumn.ColumnTypeDefinition = sqlColumnTypeDefinition;
// override type of header to STRING because exists a string in the column
if (sqlColumnTypeDefinition.Type != typeof(string) && sqlColumn.Type == typeof(string))
sqlColumnTypeDefinition.Type = typeof(string);
}
}
// Fix type to STRING if all values is 'NULL'
foreach (var sqlHeader in sqlTable.Headers)
{
if (sqlHeader.Type == null)
sqlHeader.Type = typeof(string);
sqlHeader.TypeFormatted = sqlWriter.CSharpTypeToSqlType(sqlHeader.Type);
}
}
return sqlTable;
}
public ExpressionStringWriter(ISqlWriter writer)
{
this.writer = writer;
}
/// <summary>The settings_ database connection reset.</summary>
/// <param name="sender">The sender.</param>
/// <param name="e">The e.</param>
private void Settings_DatabaseConnectionReset(object sender, EventArgs e)
{
_metaDataService = null;
_sqlWriter = null;
ExecLoadDatabaseDetails();
}
protected override void WriteTo(ISqlWriter writer)
{
writer.Write(VariableName);
}
protected override void WriteTo(ISqlWriter writer)
{
writer.Write(VariableName);
}
protected override void WriteTo(ISqlWriter writer)
{
writer.Write(Value);
}
