// StringBuilder s = new StringBuilder();
// stmt.accept(new MySqlOutputAstVisitor(s));
// s.toString();
/// <exception cref="System.Exception" />
public override string GetSql()
{
var sql = "insert into xoffer (member_id, gmt_create) values ('1','2001-09-13 20:20:33')";
stmt = SqlParserDelegate.Parse(sql);
return("insert into xoffer (member_id, gmt_create) values ('1','2001-09-13 20:20:33')");
}
private static void ThrowRetrieveDuplicateObjectException(ISqlStatement statement, IBusinessObject loadedBo)
{
throw new HabaneroDeveloperException
("There was an error with loading the class '" + loadedBo.ClassDef.ClassNameFull + "'",
"Loading a '" + loadedBo.ClassDef.ClassNameFull + "' with criteria '" + statement.Statement
+ "' returned more than one record when only one was expected.");
}
/// <summary>
/// Execute the given statement and returns the number of affected rows
/// </summary>
public static int Execute <TDb>(this ISqlStatement statement, NpgsqlConnection conn)
{
var sql = statement.ToSql();
var pars = NpgsqlParamLogic.GetParams(sql.Params);
return(NpgsqlMapper.Execute(conn, sql));
}
/// <summary>
/// Ejecuta el query en un contexto de EF. Devuelve la cantidad de filas afectadas
/// Abre la conexión en caso de que no este abierta y la deja abierta
/// </summary>
public static async Task <int> Execute <TDb>(this ISqlStatement statement, DbContext context)
{
var sql = statement.ToSql();
var pars = NpgsqlParamLogic.GetParams(sql.Params);
return(await DoConnection(context, async conn => await NpgsqlMapper.ExecuteAsync(conn, sql)));
}
// StringBuilder s = new StringBuilder();
// stmt.accept(new MySqlOutputAstVisitor(s));
// s.toString();
/// <exception cref="System.Exception" />
public override string GetSql()
{
var sql = "select id,member_id,gmt_create from offer where member_id in ('22')";
stmt = SqlParserDelegate.Parse(sql);
return("select id,member_id,gmt_create from offer where member_id in ('1','22','333','1124','4525')");
}
/// <summary>
/// Loads a data reader
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <returns>Returns an IDataReader object</returns>
/// <exception cref="DatabaseReadException">Thrown when an error
/// occurred while setting up the data reader. Also sends error
/// output to the log.</exception>
public virtual IDataReader LoadDataReader(ISqlStatement selectSql)
{
if (selectSql == null)
{
throw new DatabaseConnectionException
("The sql statement object " + "that has been passed to LoadDataReader() is null.");
}
IDbConnection con = null;
try
{
con = GetOpenConnectionForReading();
var cmd = CreateCommand(con);
selectSql.SetupCommand(cmd);
SetupReadTransaction(cmd);
return(cmd.ExecuteReader(CommandBehavior.CloseConnection));
}
catch (Exception ex)
{
Log.Log
("Error reading from database : " + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 10, true), LogCategory.Exception);
Log.Log("Sql: " + selectSql, LogCategory.Exception);
Console.Out.WriteLine
("Error reading from database : " + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 10, true));
Console.Out.WriteLine("Sql: " + selectSql);
throw new DatabaseReadException
("There was an error reading the database. Please contact your system administrator." + Environment.NewLine +
selectSql.ToString() + Environment.NewLine,
"The DataReader could not be filled with", ex, selectSql.ToString(), ErrorSafeConnectString());
}
}
/// <summary>
/// Execute the given statement and returns the number of affected rows
/// </summary>
public static async Task <int> ExecuteAsync <TDb>(this ISqlStatement statement, NpgsqlConnection conn)
{
var sql = statement.ToSql();
var pars = NpgsqlParamLogic.GetParams(sql.Params);
return(await NpgsqlMapper.ExecuteAsync(conn, sql));
}
private static string GenSql(ISqlStatement ast, string orginalSql)
{
var s = new StringBuilder();
ast.Accept(new MySqlOutputAstVisitor(s));
return(s.ToString());
}
public ColumnCollection GetColumns()
{
ColumnCollection columns = new ColumnCollection();
ISqlScript sqlScript = SqlParserService.Parse(_select);
if (sqlScript.Statements != null && sqlScript.Statements.Count > 0)
{
ISqlStatement statement = sqlScript.Statements[0];
if (statement is ISelectStatement)
{
ISelectStatement selectStatement = statement as ISelectStatement;
if (selectStatement.QueryExpression != null && selectStatement.QueryExpression is ISelectExpression)
{
ISelectExpression selectExpression = selectStatement.QueryExpression as ISelectExpression;
for (int i = 0; i < selectExpression.SelectList.Count; i++)
{
SelectColumn selectColumn = selectExpression.SelectList[i];
Column column = _table[selectColumn.Name];
if (column != null)
{
columns.Add(column);
}
}
}
}
}
return(columns);
}
public void ApplyParametersComplexTest()
{
try
{
NHibernateSqlOutputFormatter nhibernateFormatter = new NHibernateSqlOutputFormatter();
sqlStatement = nhibernateFormatter.TryParsSql(SecureResources.SQL14);
Assert.IsTrue(sqlStatement.Parameters.Count == 1);
string appliedParameterSql = sqlStatement.ApplyParameters();
Assert.IsFalse(appliedParameterSql.Contains("@"));
string appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 3454);
sqlStatement = nhibernateFormatter.TryParsSql(Properties.SecureResources.SQL7);
appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsFalse(appliedParameterSql.Contains("@"));
sqlStatement = nhibernateFormatter.TryParsSql(Properties.SecureResources.SQL8);
appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsFalse(appliedParameterSql.Contains("@"));
}
catch (Exception exception)
{
Assert.IsNotInstanceOfType(exception, typeof(Exception));
}
finally
{
SecureResources.ResourceManager.ReleaseAllResources();
}
}
public void Insert()
{
statement = FooSchema.Insert((sql, s) => sql.Set(s.oid, 1).Set(s.oname, "foo1"));
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(1,'foo1')", statement.ToString());
statement = FooSchema.Table.Insert().Columns(FooSchema.Instance.oid, FooSchema.Instance.oname).Values(1, "foo1");
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(1,'foo1')", statement.ToString());
statement = FooSchema.Table.Insert().Set(FooSchema.Instance.oid, 1).Set(FooSchema.Instance.oname, "foo1");
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(1,'foo1')", statement.ToString());
statement = FooSchema.Table.Insert().SetC(FooSchema.Instance.oid, "1").SetVarCustomer(FooSchema.Instance.oname, "'foo1'");
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(1,'foo1')", statement.ToString());
statement = FooSchema.Table.Insert().SetP(FooSchema.Instance.oid).SetVarParam(FooSchema.Instance.oname);
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(@oid,@oname)", statement.ToString());
statement = FooSchema.Table.Insert(FooSchema.Instance.oid, FooSchema.Instance.oname).Values(1, "foo1");
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(1,'foo1')", statement.ToString());
statement = FooSchema.Table.Insert(FooSchema.Instance.oid, FooSchema.Instance.oname).ValuesVarCustomer("1", "'foo1'");
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(1,'foo1')", statement.ToString());
statement = FooSchema.Table.Insert(FooSchema.Instance.oid, FooSchema.Instance.oname).ValuesVarParam();
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(@oid,@oname)", statement.ToString());
statement = FooSchema.Table.Insert(FooSchema.Instance.oid, FooSchema.Instance.oname).ValuesFillNull();
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(NULL,NULL)", statement.ToString());
}
public virtual void Testdesc()
{
var sql = "desc tb1";
var lexer = new MySqlLexer(sql);
var parser = new MySqlDalParser(lexer, new MySqlExprParser(lexer));
ISqlStatement desc = parser.Desc();
parser.Match(MySqlToken.Eof);
var output = Output2MySql(desc, sql);
Assert.AreEqual("DESC tb1", output);
sql = "desc db.tb1";
lexer = new MySqlLexer(sql);
parser = new MySqlDalParser(lexer, new MySqlExprParser(lexer));
desc = parser.Desc();
parser.Match(MySqlToken.Eof);
output = Output2MySql(desc, sql);
Assert.AreEqual("DESC db.tb1", output);
sql = "describe db.tb1";
lexer = new MySqlLexer(sql);
parser = new MySqlDalParser(lexer, new MySqlExprParser(lexer));
desc = parser.Desc();
parser.Match(MySqlToken.Eof);
output = Output2MySql(desc, sql);
Assert.AreEqual("DESC db.tb1", output);
}
private static bool IsSystemReadSql(ISqlStatement ast)
{
if (ast is DalShowStatement)
{
return(true);
}
DmlSelectStatement select = null;
if (ast is DmlSelectStatement)
{
select = (DmlSelectStatement)ast;
}
else if (ast is DmlSelectUnionStatement)
{
var union = (DmlSelectUnionStatement)ast;
if (union.SelectStmtList.Count == 1)
{
select = union.SelectStmtList[0];
}
else
{
return(false);
}
}
else
{
return(false);
}
return(@select.Tables == null);
}
public void Select()
{
statement = TestSchema.Select((sql, s) => sql.Get(s.All()));
Assert.AreEqual("SELECT test.oid,test.oname,test.age,test.gender,test.isdel FROM test", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.oid).Where(s.oid > 1));
Assert.AreEqual("SELECT test.oid FROM test WHERE test.oid>1", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.age).OrderBy(s.age.Desc()));
Assert.AreEqual("SELECT test.age FROM test ORDER BY test.age DESC", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.gender, s.age.Avg()).GroupBy(s.gender).Having(s.age.Avg() < 40));
Assert.AreEqual("SELECT test.gender,AVG(test.age) FROM test GROUP BY test.gender HAVING AVG(test.age)<40", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.All()).Where(s.oid > 1).OrderBy(s.age.Desc()));
Assert.AreEqual("SELECT test.oid,test.oname,test.age,test.gender,test.isdel FROM test WHERE test.oid>1 ORDER BY test.age DESC", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.age.Avg()).Where(s.oid > 1).GroupBy(s.gender).Having(s.age.Avg() > 18).OrderBy(s.age.Desc()));
Assert.AreEqual("SELECT AVG(test.age) FROM test WHERE test.oid>1 GROUP BY test.gender HAVING AVG(test.age)>18 ORDER BY test.age DESC", statement.ToString());
statement = TestSchema.Table.Select(TestSchema.Instance.oid);
Assert.AreEqual("SELECT test.oid FROM test", statement.ToString());
statement = TestSchema.Table.SelectVarCustomer("test.oid");
Assert.AreEqual("SELECT test.oid FROM test", statement.ToString());
statement = TestSchema.Table.Select().Get(TestSchema.Instance.oid);
Assert.AreEqual("SELECT test.oid FROM test", statement.ToString());
statement = TestSchema.Table.Select().GetVarCustomer("test.oid");
Assert.AreEqual("SELECT test.oid FROM test", statement.ToString());
statement = TestSchema.Table.Select().GetAs(TestSchema.Instance.oid, "a");
Assert.AreEqual("SELECT test.oid AS a FROM test", statement.ToString());
}
private static bool IsEOFedDdl(ISqlStatement stmt)
{
if (stmt is IDdlStatement && stmt is DdlCreateIndexStatement)
return false;
return true;
}
/// <summary>
/// Appends an order-by clause to the sql statement. " ORDER BY " is
/// automatically prefixed by this method.
/// </summary>
/// <param name="statement"></param>
/// <param name="orderByCriteria">The order-by clause</param>
private static void AppendOrderBy(ISqlStatement statement, string orderByCriteria)
{
if (!string.IsNullOrEmpty(orderByCriteria))
{
statement.Statement.Append(" ORDER BY " + orderByCriteria);
}
}
public GenerateSql(string path, string outputPath, string dllName, string outputName, ISqlStatement sqlStatement)
{
this.DllName = dllName;
this.DllFilePath = path;
this.SqlStatement = sqlStatement;
this.ScriptPath = outputPath + @"\" + outputName;
}
public static void RouteForTableMeta(RouteResultset rrs,
SchemaConfig schema,
ISqlStatement ast,
PartitionKeyVisitor visitor,
string stmt)
{
var sql = stmt;
if (visitor.IsSchemaTrimmed())
{
sql = GenSql(ast, stmt);
}
var tables = visitor.GetMetaReadTable();
if (tables == null)
{
throw new ArgumentException(string.Format("route err: tables[] is null for meta read table: {0}",
stmt));
}
string[] dataNodes;
if (tables.Length <= 0)
{
dataNodes = schema.MetaDataNodes;
}
else
{
if (tables.Length == 1)
{
dataNodes = new string[1];
dataNodes[0] = GetMetaReadDataNode(schema, tables[0]);
}
else
{
ICollection <string> dataNodeSet = new HashSet <string>();
foreach (var table in tables)
{
var dataNode = GetMetaReadDataNode(schema, table);
dataNodeSet.Add(dataNode);
}
dataNodes = dataNodeSet.ToArray();
//dataNodes = new string[dataNodeSet.Count];
//IEnumerator<string> iter = dataNodeSet.GetEnumerator();
//for (int i = 0; i < dataNodes.Length; ++i)
//{
// dataNodes[i] = iter.Current;
//}
}
}
var nodes = new RouteResultsetNode[dataNodes.Length];
rrs.Nodes = nodes;
for (var i1 = 0; i1 < dataNodes.Length; ++i1)
{
nodes[i1] = new RouteResultsetNode(dataNodes[i1], sql);
}
}
public bool EndStatement(ISqlStatement statement)
{
if (statement != null)
{
statement.Close(); // CHECK:
}
return(true);
}
/// <summary>
/// Executes a single sql statement object
/// </summary>
/// <param name="sql">The sql statement object</param>
/// <returns>Returns the number of rows affected</returns>
public int ExecuteSql(ISqlStatement sql)
{
if (sql == null)
{
throw new ArgumentNullException("sql");
}
return(ExecuteSql(new[] { sql }));
}
public static BatchSqlStatement Concat(this ISqlStatement sql, params ISqlStatement[] otherSqls)
{
List <ISqlStatement> sqls = new List <ISqlStatement>();
sqls.Add(sql);
sqls.AddRange(otherSqls);
return(new BatchSqlStatement(sqls.ToArray()));
}
public void SetupTest()
{
conn = Substitute.For<IDatabaseConnection>();
statement = new SqlStatement(DatabaseConnection.CurrentConnection);
statement.Statement.Append(Sql);
conn.LoadDataTable(statement, "", "").Returns(dt);
conn.GetConnection().Returns(DatabaseConnection.CurrentConnection.GetConnection());
}
/// <summary>
/// Obtiene el SQL y los parámetros de un select
/// </summary>
public static SqlResult ToSql(this ISqlStatement statement, ParamMode mode)
{
var dic = new SqlParamDic();
var sql = StatementStr.StatementToString(statement, mode, dic).Sql;
var pars = dic.Items.Select(x => new SqlParam(x.ParamName, x.GetValue(), x.GetParamType()));
return(new SqlResult(sql, pars.ToList()));
}
public void Delete()
{
statement = FooSchema.Delete((sql, s) => sql.Where(s.oid == 1));
Assert.AreEqual("DELETE FROM foo WHERE foo.oid=1", statement.ToString());
statement = FooSchema.Table.Delete().Where(FooSchema.Instance.oid == 1);
Assert.AreEqual("DELETE FROM foo WHERE foo.oid=1", statement.ToString());
}
public void SetupTest()
{
conn = Substitute.For <IDatabaseConnection>();
statement = new SqlStatement(DatabaseConnection.CurrentConnection);
statement.Statement.Append(Sql);
conn.LoadDataTable(statement, "", "").Returns(dt);
conn.GetConnection().Returns(DatabaseConnection.CurrentConnection.GetConnection());
}
private static bool IsEOFedDdl(ISqlStatement stmt)
{
if (stmt is IDdlStatement && stmt is DdlCreateIndexStatement)
{
return(false);
}
return(true);
}
/// <summary>
/// 检查sqlId是否存在，如果存在则以ISqlStatement返回其sql
/// </summary>
private bool FindStatement(string sql, out ISqlStatement statement)
{
if (!DaoFactory.GetSqlSource().IsValidKey(sql))
{
statement = null;
return(false);
}
return(null != (statement = DaoFactory.GetSqlSource().Find(sql, _provider.Name)));
}
private static DaoExecutor CreateCommand(ISqlStatement sql, object parameters)
{
Dao dao = string.IsNullOrEmpty(sql.Connection) ? Dao.Get() : Dao.Get(sql.Connection);
ISqlCommand command = sql.CreateCommand(dao.Provider, parameters);
return(new DaoExecutor {
Command = command, Dao = dao
});
}
public QueryTable(string alias, int explicitPrimaryKeys, JoinType?joinType, SqlStatementBuilder queryBuilder, IQueryTables tables, ISqlStatement parentStatement)
{
Alias = alias ?? throw new ArgumentNullException(nameof(alias));
ExplicitPrimaryKeys = explicitPrimaryKeys;
JoinType = joinType;
QueryBuilder = queryBuilder ?? throw new ArgumentNullException(nameof(queryBuilder));
Tables = tables ?? throw new ArgumentNullException(nameof(tables));
ParentStatement = parentStatement ?? throw new ArgumentNullException(nameof(parentStatement));
}
protected override void SetupCommand(ISqlStatement statement, IDbCommand command, IDbTransaction transaction)
{
base.SetupCommand(statement, command, transaction);
//TODO mark 10 Oct 2014: Test this following code somehow...
var isAutoIncrementingInsert = IsAutoIncrementingInsertStatement(statement);
if (isAutoIncrementingInsert)
{
SetupCommandForAutoIncrement(command);
}
}
/// <summary>
/// Loads a data reader and specifies an order-by clause
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <param name="strOrderByCriteria">A sql order-by clause</param>
/// <returns>Returns an IDataReader object</returns>
public IDataReader LoadDataReader(ISqlStatement selectSql, string strOrderByCriteria)
{
if (selectSql == null)
{
throw new ArgumentNullException("selectSql");
}
AppendOrderBy(selectSql, strOrderByCriteria);
return(LoadDataReader(selectSql));
}
public void SetupTest()
{
dbConnMock = new DynamicMock(typeof (IDatabaseConnection));
conn = (IDatabaseConnection) dbConnMock.MockInstance;
statement = new SqlStatement(DatabaseConnection.CurrentConnection);
statement.Statement.Append(Sql);
dbConnMock.ExpectAndReturn("LoadDataTable", dt, new object[] {statement, "", ""});
dbConnMock.ExpectAndReturn("GetConnection", DatabaseConnection.CurrentConnection.GetConnection(),
new object[] {});
}
protected override void SetupCommand(ISqlStatement statement, IDbCommand command, IDbTransaction transaction)
{
base.SetupCommand(statement, command, transaction);
var newParameter = command.CreateParameter();
newParameter.ParameterName = _newIdentityParameterName;
newParameter.DbType = DbType.Int64;
newParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(newParameter);
command.CommandText += ";" + Environment.NewLine + "SELECT @newIdentity = SCOPE_IDENTITY();";
}
/// <summary>
/// Creates the full expression in sql format.
/// See IExpression.SqlExpressionString for more detail on the
/// format of the arguments.
/// </summary>
public void SqlExpressionString(ISqlStatement statement, string tableNameFieldNameLeftSeparator,
string tableNameFieldNameRightSeparator)
{
statement.Statement.Append("(");
_leftExpression.SqlExpressionString(statement, tableNameFieldNameLeftSeparator,
tableNameFieldNameRightSeparator);
statement.Statement.Append(" " + _sqlOperator.ExpressionString().Trim() + " ");
_rightExpression.SqlExpressionString(statement, tableNameFieldNameLeftSeparator,
tableNameFieldNameRightSeparator);
statement.Statement.Append(")");
}
public ISqlStatement Find(string key, string daoProviderName)
{
ISqlStatement statement = null;
if (!string.IsNullOrEmpty(daoProviderName))
{
statement = Find(key + "$" + daoProviderName.ToLower());
}
return(statement ?? Find(key));
}
public void ParseTest()
{
NHibernateSqlOutputFormatter nhibernateFormatter = new NHibernateSqlOutputFormatter();
sqlStatement = nhibernateFormatter.TryParsSql(SecureResources.SQLInsert1);
Assert.IsTrue(sqlStatement.Parameters.Count == 4);
string appliedParameterSql = sqlStatement.ApplyParameters();
Assert.IsFalse(appliedParameterSql.Contains("@"));
string appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 161);
}
public bool TryWrite(ISqlStatement statement)
{
// Bad code ... you're assuming order!
var properties = statement.Parameters.GetType().GetProperties().ToArray();
if ((_parameters.Count + properties.Length) <= 2100) {
if (statement is SqlStoredProcedureStatement) {
var text = statement.Text;
var index = 0;
foreach (var property in properties) {
var parameterName = "@P" + _parameterIndex++;
_parameters.Add(
new SqlParameter(parameterName, property.GetValue(statement.Parameters)));
if (index > 0) {
text = text + ", " + parameterName;
}
else {
text = text + " " + parameterName;
}
index++;
}
_text = _text + "EXEC " + text + ";";
}
else if (statement is SqlTextStatement) {
var text = statement.Text;
foreach (var property in properties) {
var parameterName = "@P" + _parameterIndex++;
_parameters.Add(
new SqlParameter(parameterName, property.GetValue(statement.Parameters)));
text = text.Replace("@" + property.Name, parameterName);
}
_text = _text + text + ";";
}
return true;
}
return false;
}
static public void DebugWriteline(ISqlStatement sqlStatement, int lineNum)
{
if (sqlStatement is SqlSelectStatement)
{
System.Diagnostics.Debug.WriteLine("------------");
System.Diagnostics.Debug.WriteLine("SELECT PARTS");
System.Diagnostics.Debug.WriteLine("------------");
var selectItem = (SqlSelectStatement)sqlStatement;
var selectPart = selectItem.SelectPart.Value;
{
lineNum++;
System.Diagnostics.Debug.WriteLine(string.Format("{0}.]{1}\n", lineNum, selectPart));
}
System.Diagnostics.Debug.WriteLine("----------");
System.Diagnostics.Debug.WriteLine("FROM PARTS");
System.Diagnostics.Debug.WriteLine("----------");
var fromPart = selectItem.FromPart.Value;
{
System.Diagnostics.Debug.WriteLine(string.Format("{0}.]{1}\n", lineNum, fromPart));
}
System.Diagnostics.Debug.WriteLine("-----------");
System.Diagnostics.Debug.WriteLine("WHERE PARTS");
System.Diagnostics.Debug.WriteLine("-----------");
var wherePart = selectItem.WherePart.Value;
{
System.Diagnostics.Debug.WriteLine(string.Format("{0}.]{1}\n", lineNum, wherePart));
}
System.Diagnostics.Debug.WriteLine("-------------");
System.Diagnostics.Debug.WriteLine("ORDER BY PART");
System.Diagnostics.Debug.WriteLine("-------------");
System.Diagnostics.Debug.WriteLine(string.Format("{0}.]{1}\n", lineNum, selectItem.OrderByPart));
}
}
public void ApplyParametersSqlColesceSelectTest()
{
try
{
NHibernateSqlOutputFormatter nhibernateFormatter = new NHibernateSqlOutputFormatter();
nhibernateFormatter.SqlIdentifiers = new string[] { "NHibernate.SQL:" };
sqlStatement = nhibernateFormatter.TryParsSql(SecureResources.ColesceSelectSql);
Assert.IsTrue(sqlStatement.Parameters.Count == 3);
string appliedParameterSql = sqlStatement.ApplyParameters();
Assert.IsFalse(appliedParameterSql.Contains("@"));
string appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 371);
}
catch (Exception exception)
{
Assert.IsNotInstanceOfType(exception, typeof(Exception));
}
finally
{
SecureResources.ResourceManager.ReleaseAllResources();
}
}
/// <summary>
/// Loads a data reader
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <returns>Returns an IDataReader object</returns>
/// <exception cref="DatabaseReadException">Thrown when an error
/// occurred while setting up the data reader. Also sends error
/// output to the log.</exception>
public virtual IDataReader LoadDataReader(ISqlStatement selectSql)
{
if (selectSql == null)
{
throw new DatabaseConnectionException
("The sql statement object " + "that has been passed to LoadDataReader() is null.");
}
IDbConnection con = null;
try
{
con = GetOpenConnectionForReading();
var cmd = CreateCommand(con);
selectSql.SetupCommand(cmd);
SetupReadTransaction(cmd);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
Log.Log
("Error reading from database : " + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 10, true), LogCategory.Exception);
Log.Log("Sql: " + selectSql, LogCategory.Exception);
Console.Out.WriteLine
("Error reading from database : " + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 10, true));
Console.Out.WriteLine("Sql: " + selectSql);
throw new DatabaseReadException
("There was an error reading the database. Please contact your system administrator." + Environment.NewLine +
selectSql.ToString() + Environment.NewLine,
"The DataReader could not be filled with", ex, selectSql.ToString(), ErrorSafeConnectString());
}
}
/// <summary>
/// Appends an order-by clause to the sql statement. " ORDER BY " is
/// automatically prefixed by this method.
/// </summary>
/// <param name="statement"></param>
/// <param name="orderByCriteria">The order-by clause</param>
private static void AppendOrderBy(ISqlStatement statement, string orderByCriteria)
{
if (!string.IsNullOrEmpty(orderByCriteria))
{
statement.Statement.Append(" ORDER BY " + orderByCriteria);
}
}
/// <summary>
/// Loads a data reader and specifies an order-by clause
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <param name="strOrderByCriteria">A sql order-by clause</param>
/// <returns>Returns an IDataReader object</returns>
public IDataReader LoadDataReader(ISqlStatement selectSql, string strOrderByCriteria)
{
if (selectSql == null) throw new ArgumentNullException("selectSql");
AppendOrderBy(selectSql, strOrderByCriteria);
return LoadDataReader(selectSql);
}
// StringBuilder s = new StringBuilder();
// stmt.accept(new MySqlOutputAstVisitor(s));
// s.toString();
/// <exception cref="System.Exception" />
public override string GetSql()
{
var sql = "select id,member_id,gmt_create from offer where member_id in ('22')";
stmt = SqlParserDelegate.Parse(sql);
return "select id,member_id,gmt_create from offer where member_id in ('1','22','333','1124','4525')";
}
// StringBuilder s = new StringBuilder();
// stmt.accept(new MySqlOutputAstVisitor(s));
// s.toString();
/// <exception cref="System.Exception" />
public override string GetSql()
{
var sql = "insert into offer (member_id, gmt_create) values ('1','2001-09-13 20:20:33')";
stmt = SqlParserDelegate.Parse(sql);
return
"insert into offer (member_id, gmt_create) values ('1','2001-09-13 20:20:33'),('1','2001-09-13 20:20:34')";
}
public void ApplySuggestedFormatTest()
{
try
{
NHibernateSqlOutputFormatter nhibernateFormatter = new NHibernateSqlOutputFormatter();
nhibernateFormatter.SqlIdentifiers = new string[] { "NHibernate.SQL:" };
IList<ISqlStatement> sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL1);
var sqlStatement = sqlStatements[0];
var sqlSelectStatement = sqlStatement as SqlSelectStatement;
string appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 3454);
Assert.IsTrue(sqlStatement.ColumnNames.Count == 48);
Assert.IsTrue(sqlStatement.Parameters.Count == 1);
Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 3061);
Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 6);
Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 30);
Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 29);
sqlStatement = sqlStatements[1];
sqlSelectStatement = sqlStatement as SqlSelectStatement;
appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 454);
Assert.IsTrue(sqlStatement.ColumnNames.Count == 11);
Assert.IsTrue(sqlStatement.Parameters.Count == 1);
Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 361);
Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 23);
Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 21);
sqlStatement = sqlStatements[2];
sqlSelectStatement = sqlStatement as SqlSelectStatement;
appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 2136);
Assert.IsTrue(sqlStatement.ColumnNames.Count == 29);
Assert.IsTrue(sqlStatement.Parameters.Count == 4);
Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 1121);
Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 595);
Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 121);
sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL3);
sqlStatement = sqlStatements[3];
sqlSelectStatement = sqlStatement as SqlSelectStatement;
appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 366);
Assert.IsTrue(sqlStatement.ColumnNames.Count == 4);
Assert.IsTrue(sqlStatement.Parameters.Count == 2);
Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 93);
Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 32);
Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 48);
sqlStatement = sqlStatements[5];
sqlSelectStatement = sqlStatement as SqlSelectStatement;
appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 378);
Assert.IsTrue(sqlStatement.ColumnNames.Count == 4);
Assert.IsTrue(sqlStatement.Parameters.Count == 2);
Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 93);
Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 32);
Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 48);
sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL5);
sqlStatement = sqlStatements[7];
sqlSelectStatement = sqlStatement as SqlSelectStatement;
appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 1634);
Assert.IsTrue(sqlStatement.ColumnNames.Count == 8);
Assert.IsTrue(sqlStatement.Parameters.Count == 0);
Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 1016);
Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 5);
Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 325);
Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 65);
sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL6);
sqlStatement = sqlStatements[8];
sqlSelectStatement = sqlStatement as SqlSelectStatement;
appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 1811);
Assert.IsTrue(sqlStatement.ColumnNames.Count == 27);
Assert.IsTrue(sqlStatement.Parameters.Count == 6);
Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 931);
Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 270);
Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 187);
sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL7);
sqlStatement = sqlStatements[9];
sqlSelectStatement = sqlStatement as SqlSelectStatement;
appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 885);
Assert.IsTrue(sqlStatement.ColumnNames.Count == 2);
Assert.IsTrue(sqlStatement.Parameters.Count == 56);
Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 45);
Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 33);
Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 426);
sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL8);
sqlStatement = sqlStatements[10];
sqlSelectStatement = sqlStatement as SqlSelectStatement;
appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 282);
Assert.IsTrue(sqlStatement.ColumnNames.Count == 1);
Assert.IsTrue(sqlStatement.Parameters.Count == 1);
Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 26);
Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 33);
Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 46);
sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL9);
sqlStatement = sqlStatements[11];
sqlSelectStatement = sqlStatement as SqlSelectStatement;
appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 735);
Assert.IsTrue(sqlStatement.ColumnNames.Count == 17);
Assert.IsTrue(sqlStatement.Parameters.Count == 1);
Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 588);
Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 27);
Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 37);
sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL10);
sqlStatement = sqlStatements[12];
sqlSelectStatement = sqlStatement as SqlSelectStatement;
appliedFormatSql = sqlStatement.ApplySuggestedFormat();
Assert.IsTrue(appliedFormatSql.Length == 156);
Assert.IsTrue(sqlStatement.ColumnNames.Count == 1);
Assert.IsTrue(sqlStatement.Parameters.Count == 1);
Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 27);
Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 27);
Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0);
Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 43);
IList<ISqlStatement> sqlSelectStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL3);
foreach (var statement in sqlSelectStatements)
{
string sql = statement.ApplySuggestedFormat();
#if DEBUG
Debug.WriteLine(sql);
#endif
}
}
catch
{
throw;
}
finally
{
SecureResources.ResourceManager.ReleaseAllResources();
}
}
/// <summary>
/// Sets the sql statement to execute from the <see cref="ISqlStatement"/>
/// fluent interface. Any parameters set on the SqlStatement will
/// replace any existing set of parameters.
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public AdhocCommandBase WithSql(ISqlStatement sql)
{
mySql = sql.ToString();
Parameters = sql.Parameters;
return this;
}
public void Insert(int index, ISqlStatement value)
{
List.Insert(index, value);
}
public void Remove(ISqlStatement order)
{
this.orders.Remove(order);
}
public void Add(ISqlStatement order)
{
this.orders.Add(order);
}
/// <summary>
/// Sets up an <see cref="IDbCommand"/> object with the provided statement.
/// </summary>
/// <param name="statement">The <see cref="ISqlStatement"/> to setup the command with.</param>
/// <param name="command">The <see cref="IDbCommand"/> to set up.</param>
/// <param name="transaction">The <see cref="IDbTransaction"/> that the command runs under.</param>
protected virtual void SetupCommand(ISqlStatement statement, IDbCommand command, IDbTransaction transaction)
{
statement.SetupCommand(command);
}
public void Remove(ISqlStatement value)
{
List.Remove(value);
}
public bool Contains(ISqlStatement value)
{
return List.Contains(value);
}
/// <summary>
/// Executes a single sql statement object
/// </summary>
/// <param name="sql">The sql statement object</param>
/// <returns>Returns the number of rows affected</returns>
public int ExecuteSql(ISqlStatement sql)
{
if (sql == null) throw new ArgumentNullException("sql");
return ExecuteSql(new[] { sql });
}
public int IndexOf(ISqlStatement value)
{
return List.IndexOf(value);
}
/// <summary>
/// Loads data from the database into a DataTable object, using the
/// sql statement object provided
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <param name="strSearchCriteria">The search criteria as a string
/// to append</param>
/// <param name="strOrderByCriteria">The order by criteria as a string
/// to append</param>
/// <returns>Returns a DataTable object</returns>
/// <exception cref="DatabaseReadException">Thrown if there is an
/// error reading the database. Also outputs error messages to the log.
/// </exception>
public DataTable LoadDataTable(ISqlStatement selectSql, string strSearchCriteria, string strOrderByCriteria)
{
//It was chosen to use a datareader to fill the DataTable instead of using an
// adapter because the data adapter approach requires creating a different adapter depending on the
// database type. This is understandable but for simple loading of a datatable without all the additional
// schema data it is unneccessary.
// It could however be easily achieved since there is a physical instance of a database connection object
// per database type that inherit from this class e.g. DatabaseConnectionMySQL.
if (selectSql == null) throw new ArgumentNullException("selectSql");
IDbConnection con = null;
try
{
con = GetOpenConnectionForReading();
IDbCommand cmd = CreateCommand(con);
selectSql.SetupCommand(cmd);
DataTable dt;
using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt = GetDataTable(reader);
reader.Close();
}
return dt;
}
catch (Exception ex)
{
Log.Log
("Error in LoadDataTable:" + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 8, true), LogCategory.Exception);
Log.Log("Sql string: " + selectSql, LogCategory.Exception);
throw new DatabaseReadException
("There was an error reading the database. Please contact your system administrator.",
"The DataReader could not be filled with", ex, selectSql.ToString(), ErrorSafeConnectString());
}
}
public int Add(ISqlStatement value)
{
return List.Add(value);
}
///<summary>
/// Constructor for SQL Builder
///</summary>
///<param name="connection"></param>
///<param name="statement"></param>
public SqlStatementBuilder(IDatabaseConnection connection, string statement)
{
_connection = connection;
_statement = new SqlStatement(_connection, statement);
}
/// <summary>
/// Creates a valid sql expression, e.g. for a "where" clause.
/// See IExpression.SqlExpressionString for more detail.
/// </summary>
public void SqlExpressionString(ISqlStatement statement, string tableNameFieldNameLeftSeparator,
string tableNameFieldNameRightSeparator)
{
statement.Statement.Append(
FieldFullName(tableNameFieldNameLeftSeparator, tableNameFieldNameRightSeparator) + GetSqlOperator());
if (this.DoesntRequireParametrisedValue())
{
statement.Statement.Append(GetSqlStringWithNoParameters());
}
else
{
statement.AddParameterToStatement(GetParameterValueAsObject());
}
}
public void AddStatement(ISqlStatement statement)
{
_statements.Add(statement);
}
private static bool IsAutoIncrementingInsertStatement(ISqlStatement statement)
{
var insertSqlStatement = statement as InsertSqlStatement;
return insertSqlStatement != null &&
insertSqlStatement.SupportsAutoIncrementingField != null;
}
