public override string ToString()
{
if (this._operands == null)
this._sqlClause = SqlClause.None;
return base.ToString();
}
public void GetParameters_ToEmptyClause_ReturnsCorrectParametersList()
{
// arrange
var sut = new SqlClause();
var parameter = new SqlParameter {
ParameterName = "@test"
};
// act
sut.Add("a", parameter);
var result = sut.GetParameters();
// arrange
result.Should().Equal(parameter);
}
public void AddPredicate_ByDefault_AddsSqlAndParameters()
{
// arrange
var sut = new SqlClause();
var parameter = new SqlParameter {
ParameterName = "@x"
};
// act
sut.AddPredicate("Id", " ? ", parameter);
var sql = sut.GetSql();
var parameters = sut.GetParameters();
// assert
sql.Should().Be("Id ? @x");
parameters.Should().Equal(parameter);
}
public void ToString_WithPrefix_WithSuffix_WithSeparator_TwoExpressions_ReturnsCorrectSql()
{
// arrange
var sut = new SqlClause();
sut.Prefix = "prefix ";
sut.ExpressionsSeparator = " and ";
sut.Suffix = " suffix";
sut.Add("a");
sut.Add("b");
// act
var result = sut.ToString();
// arrange
result.Should().Be("prefix a and b suffix");
}
public void AddGreaterOrEquals_ByDefault_AddsCorrectPredicate()
{
// arrange
var sut = new SqlClause();
var parameter = new SqlParameter {
ParameterName = "@x"
};
// act
sut.AddGreaterOrEquals("Id", parameter);
var sql = sut.GetSql();
var parameters = sut.GetParameters();
// assert
sql.Should().Be("Id >= @x");
parameters.Should().Equal(parameter);
}
public void WithKey_HasExpressionWithTheSameKey_ByDefault_AddsNothing()
{
// arrange
var sut = new SqlClause();
sut.Add("key", "a");
var clause = new SqlClause();
clause.Add("b");
// act
sut.Add("key", clause);
var result = sut.GetSql();
// arrange
result.Should().Be("a");
}
public void WithKey_HasExpressionWithTheSameKey_WithOverwrite_OverwritesTheExpression()
{
// arrange
var sut = new SqlClause();
sut.Add("key", "a");
var clause = new SqlClause();
clause.Add("b");
// act
sut.Add("key", clause, overwrite: true);
var result = sut.GetSql();
// arrange
result.Should().Be("b");
}
public void ToString_WithParameter_ReplacesParameterWithItsValue(object value, Type type, string expected)
{
// arrange
if (value != null)
{
value = Convert.ChangeType(value, type);
}
var sut = new SqlClause();
sut.Add("@p", new SqlParameter("@p", value));
// act
var result = sut.ToString();
// arrange
result.Should().Be(expected);
}
public void ToNotEmptyClause_AddingNotEmptyClause_ReturnsCorrectSql()
{
// arrange
var sut = new SqlClause();
sut.Add("a");
var clause = new SqlClause();
clause.Add("b");
// act
sut.Add(clause);
var result = sut.GetSql();
// arrange
result.Should().Be("ab");
}
public void GetSql_WithKey_HasExpressionWithTheSameKey_WithOverwrite_OverwritesTheExpression()
{
// arrange
var sut = new SqlClause();
sut.Add("key", "a");
var parameter = new SqlParameter {
ParameterName = "@test"
};
// act
sut.Add("key", "b", parameter, overwrite: true);
var result = sut.GetSql();
// arrange
result.Should().Be("b");
}
public void GetParameters_WithKey_HasExpressionWithTheSameKey_ByDefault_AddsNothing()
{
// arrange
var sut = new SqlClause();
sut.Add("key", "a");
var parameter = new SqlParameter {
ParameterName = "@test"
};
// act
sut.Add("key", "b", parameter);
var result = sut.GetParameters();
// arrange
result.Should().BeEmpty();
}
public void GetParameters_WithKey_HasParameterWithTheSameKey_WithOverwrite_OverwritesTheParameter()
{
// arrange
var sut = new SqlClause();
sut.Add("key", "a");
var parameter = new SqlParameter {
ParameterName = "@test"
};
// act
sut.Add("key", "b", parameter, overwrite: true);
var result = sut.GetParameters();
// arrange
result.Should().Equal(parameter);
}
public void AddNotBetween_ByDefault_AddsCorrectPredicate()
{
// arrange
var sut = new SqlClause();
var parameter = new SqlParameter {
ParameterName = "@x"
};
var parameter2 = new SqlParameter {
ParameterName = "@x2"
};
// act
sut.AddNotBetween("Id", parameter, parameter2);
var sql = sut.GetSql();
var parameters = sut.GetParameters();
// assert
sql.Should().Be("Id not between @x and @x2");
parameters.Should().Equal(parameter, parameter2);
}
public void AddNotIn_TwoParameters_AddsCorrectPredicate()
{
// arrange
var sut = new SqlClause();
var parameter = new SqlParameter {
ParameterName = "@x"
};
var parameter2 = new SqlParameter {
ParameterName = "@x2"
};
// act
sut.AddNotIn("Id", parameter, parameter2);
var sql = sut.GetSql();
var parameters = sut.GetParameters();
// assert
sql.Should().Be("Id not in (@x, @x2)");
parameters.Should().Equal(parameter, parameter2);
}
public void AddNotExists_WhereClause_AddsCorrectPredicate()
{
// arrange
var sut = SqlClauseBuilder.Where();
var parameter = new SqlParameter {
ParameterName = "@x"
};
var clause = new SqlClause("select * from Table2");
clause.Add(parameter);
// act
sut.AddNotExists(clause);
var sql = sut.GetSql();
var parameters = sut.GetParameters();
// assert
sql.Should().BeEquivalentToSql("where (not exists (select * from Table2))");
parameters.Should().Equal(parameter);
}
public void ToEmptyClause_AddingNotEmptyClause_ReturnsCorrectParametersList()
{
// arrange
var sut = new SqlClause();
var parameter = new SqlParameter {
ParameterName = "@name"
};
var clause = new SqlClause();
clause.Add(parameter);
// act
sut.Add(clause);
var result = sut.GetParameters();
// arrange
result.Should().Equal(parameter);
}
public void AddNotExists_ByDefault_AddsCorrectPredicate()
{
// arrange
var sut = new SqlClause();
var parameter = new SqlParameter {
ParameterName = "@x"
};
var clause = new SqlClause("select * from Table2");
clause.Add(parameter);
// act
sut.AddNotExists(clause);
var sql = sut.GetSql();
var parameters = sut.GetParameters();
// assert
sql.Should().Be("not exists (select * from Table2)");
parameters.Should().Equal(parameter);
}
public AbstractBsDch客室利用台帳CQ(ConditionQuery childQuery, SqlClause sqlClause, String aliasName, int nestLevel)
: base(childQuery, sqlClause, aliasName, nestLevel)
{
}
// ===============================================================================
// Constructor
// ===========
public SimplePagingBean()
{
_sqlClause = new SqlClausePostgreSql("dummy");
}
public BsDmyプロシージャCQ(ConditionQuery childQuery, SqlClause sqlClause, String aliasName, int nestLevel)
: base(childQuery, sqlClause, aliasName, nestLevel)
{
}
// ===============================================================================
// Constructor
// ===========
public AbstractConditionBean()
{
DBMeta dbmeta = DBMetaInstanceHandler.FindDBMeta(TableDbName);
_sqlClause = new SqlClausePostgreSql(dbmeta.TableSqlName);
}
protected virtual Dch客室利用台帳CQ xcreateCQ(ConditionQuery childQuery, SqlClause sqlClause, String aliasName, int nestLevel) {
return new Dch客室利用台帳CQ(childQuery, sqlClause, aliasName, nestLevel);
}
public BsKbn客室利用区分CQ(ConditionQuery childQuery, SqlClause sqlClause, String aliasName, int nestLevel)
: base(childQuery, sqlClause, aliasName, nestLevel)
{
}
public static ClauseOperand Clause(SqlClause sqlClause)
{
return(new ClauseOperand(sqlClause));
}
protected virtual DmyプロシージャCQ xcreateCQ(ConditionQuery childQuery, SqlClause sqlClause, String aliasName, int nestLevel)
{
return(new DmyプロシージャCQ(childQuery, sqlClause, aliasName, nestLevel));
}
public BsDch従業員職位履歴台帳CQ(ConditionQuery childQuery, SqlClause sqlClause, String aliasName, int nestLevel)
: base(childQuery, sqlClause, aliasName, nestLevel)
{
}
public string GetQuery(SqlClause clause)
{
return QuerySql;
}
protected virtual Mst客室タイプマスタCQ xcreateCQ(ConditionQuery childQuery, SqlClause sqlClause, String aliasName, int nestLevel)
{
return(new Mst客室タイプマスタCQ(childQuery, sqlClause, aliasName, nestLevel));
}
/// <summary>
/// Return the SQL for this query
/// </summary>
/// <returns></returns>
public string GetQuery(SqlClause clause)
{
//if (String.IsNullOrEmpty(PrimaryKey) || String.IsNullOrEmpty(TableName)) {
// throw new InvalidOperationException("Missing primary key or table name.");
//}
SqlClause required;
switch (QueryType)
{
case QueryType.Select:
// special case for select: nothing is required, so we can extract parts
required = SqlClause.Select ;
RequireClauses(required);
/// <summary>
/// This may not actually create a query that is valid SQL in every (or even any) language. The SQL output from the IQuery/
/// ISqlQueryMaker object is not intended to be used directly by a SQL engine, rather, it is supposed to generate a unique
/// query for each distict conceptual query, and provide component parts that a DataStorageController can use to map to a
/// particular engine's implementation.
/// </summary>
///
return String.Format("{0}{1}{2}{3}{4}{5}{6}",
(clause.HasFlag(SqlClause.Select)) ?
"SELECT " + Select :
"",
(clause.HasFlag(SqlClause.From) && !String.IsNullOrEmpty(From)) ?
" FROM " + From : "",
(clause.HasFlag(SqlClause.Where) && !Where.IsEmpty) ?
" WHERE " + Where : "",
(clause.HasFlag(SqlClause.GroupBy) && !String.IsNullOrEmpty(GroupBy)) ?
" GROUP BY " + GroupBy : "",
(clause.HasFlag(SqlClause.Having) && !String.IsNullOrEmpty(Having)) ?
" HAVING " + Having : "",
(clause.HasFlag(SqlClause.OrderBy) && !OrderBy.IsEmpty) ?
" ORDER BY " + OrderBy : "",
(clause.HasFlag(SqlClause.Limit) && (TotalRows>0 || FirstRow>0)) ?
(" LIMIT " + FirstRow+"," + TotalRows) : ""
).Trim();
case QueryType.Delete:
required = SqlClause.Table | SqlClause.Where;
RequireClauses(required);
return String.Format("DELETE FROM {0} WHERE {1}",
TableName,
Where);
case QueryType.Update:
required = SqlClause.Table | SqlClause.Where | SqlClause.UpdateSet;
RequireClauses(required);
// TODO: From queries
return String.Format("UPDATE {0} SET {1} WHERE {2}",
TableName,
UpdateSet,
Where);
case QueryType.Insert:
RequireClauses(SqlClause.Table | SqlClause.InsertFields);
return String.Format("INSERT INTO {0} {1} VALUES {2}",
TableName,
InsertFields,
InsertValues);
default:
throw new InvalidOperationException("Unsupported query type.");
}
}
protected void RequireClauses(SqlClause clause)
{
var err = new Func<string>(() =>
{
return " is required for an " + QueryType.ToString() + " query.";
});
if (clause.HasFlag(SqlClause.Table) && String.IsNullOrEmpty(TableName))
{
throw new IQException("TableName" + err());
}
if (clause.HasFlag(SqlClause.Select) && String.IsNullOrEmpty(Select))
{
throw new IQException("A SELECT clause" + err());
}
if (clause.HasFlag(SqlClause.From) && String.IsNullOrEmpty(From))
{
throw new IQException("A FROM clause" + err());
}
if (clause.HasFlag(SqlClause.Where) && Where.IsEmpty)
{
throw new IQException("A WHERE clause" + err());
}
if (clause.HasFlag(SqlClause.UpdateSet) && String.IsNullOrEmpty(UpdateSet))
{
throw new IQException("TableName" + err());
}
if (clause.HasFlag(SqlClause.InsertFields) && String.IsNullOrEmpty(InsertFields))
{
throw new IQException("TableName" + err());
}
}
public static ClauseOperand Clause(SqlClause sqlClause)
{
return new ClauseOperand(sqlClause);
}
public AbstractBsMst客室タイプマスタCQ(ConditionQuery childQuery, SqlClause sqlClause, String aliasName, int nestLevel)
: base(childQuery, sqlClause, aliasName, nestLevel)
{
}
public ClauseOperand(SqlClause sqlClause)
{
this._sqlClause = sqlClause;
}
public ClauseOperand(SqlClause sqlClause)
{
this._sqlClause = sqlClause;
}
// ===============================================================================
// Constructor
// ===========
// You should NOT touch with this constructor.
/// <summary>
/// Constructor.
/// </summary>
/// <param name="childQuery">Child query as interface. (NullAllowed: If null, this is base instance.)</param>
/// <param name="sqlClause">SQL clause instance. (NotNull)</param>
/// <param name="aliasName">My alias name. (NotNull)</param>
/// <param name="nestLevel">Nest level.</param>
public Mst権限マスタCQ(ConditionQuery childQuery, SqlClause sqlClause, String aliasName, int nestLevel)
: base(childQuery, sqlClause, aliasName, nestLevel)
{
}
// ===============================================================================
// Constructor
// ===========
public DmyプロシージャCIQ(ConditionQuery childQuery, SqlClause sqlClause, String aliasName, int nestLevel, BsDmyプロシージャCQ myCQ)
: base(childQuery, sqlClause, aliasName, nestLevel) {
_myCQ = myCQ;
_foreignPropertyName = _myCQ.xgetForeignPropertyName();// Accept foreign property name.
_relationPath = _myCQ.xgetRelationPath();// Accept relation path.
}
