/// <summary>
/// Verify parameter order when using unnamed parameters
/// </summary>
public void ExecuteTestUnnamedParameterOrder( string providerName )
{
GentleSettings.AnalyzerLevel = AnalyzerLevel.None;
PersistenceBroker broker = new PersistenceBroker( providerName, "..." );
GentleSqlFactory sf = broker.GetSqlFactory();
if( ! sf.HasCapability( Capability.NamedParameters ) )
{
// create parameterized query
SqlBuilder sb = new SqlBuilder( broker, StatementType.Select, typeof(MailingList) );
sb.AddConstraint( Operator.Equals, "SenderAddress", "SenderAddress" );
sb.AddConstraint( Operator.Equals, "Name", "Name" );
SqlStatement stmt = sb.GetStatement( true );
foreach( IDataParameter param in stmt.Command.Parameters )
{
Assert.IsTrue( param.ParameterName.IndexOf( (string) param.Value ) >= 0, "1: Parameter order not correctly maintained." );
}
// retry using parameters in reverse order
sb = new SqlBuilder( broker, StatementType.Select, typeof(MailingList) );
sb.AddConstraint( Operator.Equals, "Name", "Name" );
sb.AddConstraint( Operator.Equals, "SenderAddress", "SenderAddress" );
stmt = sb.GetStatement( true );
foreach( IDataParameter param in stmt.Command.Parameters )
{
Assert.IsTrue( param.ParameterName.IndexOf( (string) param.Value ) >= 0, "2: Parameter order not correctly maintained." );
}
}
}
public void Init()
{
// make sure we have only 4 members
SqlBuilder sb = new SqlBuilder( StatementType.Delete, typeof(Member) );
sb.AddConstraint( Operator.GreaterThan, "Id", 4 );
Broker.Execute( sb.GetStatement( true ) );
// make sure we have only 3 lists
sb = new SqlBuilder( StatementType.Delete, typeof(MailingList) );
sb.AddConstraint( Operator.GreaterThan, "Id", 3 );
Broker.Execute( sb.GetStatement( true ) );
GentleSettings.CacheObjects = false;
CacheManager.Clear();
}
public void TestNullValueForLong()
{
// get map to make sure object map is ok
ObjectMap om = ObjectFactory.GetMap( null, typeof(BigNull) );
FieldMap fm = om.GetFieldMap( "bigNull" );
Assert.IsTrue( fm.NullValue != null, "NullValue not set." );
Assert.IsTrue( fm.IsNullable, "Database column not marked as nullable." );
Assert.IsTrue( fm.IsValueType, "Type not a value type (can hold null)." );
Assert.IsFalse( fm.IsNullAssignable, "Type can hold null." );
long x = 0;
Assert.AreEqual( x, fm.NullValue, "Comparison 1 of object and long failed." );
Assert.IsTrue( x.Equals( fm.NullValue ), "Comparison 2 of object and long failed." );
Assert.IsTrue( fm.NullValue.Equals( x ), "Comparison 3 of object and long failed." );
// create test statement with bigint parameter
SqlBuilder sb = new SqlBuilder( StatementType.Insert, typeof(BigNull) );
SqlStatement stmt = sb.GetStatement();
// create object and set statement params
BigNull bn = new BigNull();
stmt.SetParameters( bn, true );
// verify parameter values (assume bigNull is first and only param)
IDbDataParameter param = stmt.Command.Parameters[ 0 ] as IDbDataParameter;
Assert.AreEqual( DBNull.Value, param.Value, "Parameter 0 not converted to DBNull." );
}
public void TestComplexSql()
{
if( Broker.SessionBroker.ProviderName == "SQLServer" )
{
string sql = "select * from list, listmember where list.listid = listmember.listid order by list.listid, memberid";
SqlBuilder sb = new SqlBuilder();
SqlStatement stmt = sb.GetStatement( sql, StatementType.Select, typeof(MailingList) );
SqlResult sr = Broker.Execute( stmt );
Assert.IsNotNull( sr );
Assert.IsNotNull( sr.Rows );
Assert.AreEqual( 4, sr.Rows.Count, "Complex Sql Error " );
}
}
public void TestSimpleSql()
{
GentleSqlFactory sf = Broker.GetSqlFactory();
string sql = String.Format( "select count(*) as RecordCount from {0}{1}",
sf.GetTableName( "List" ), sf.GetStatementTerminator() );
SqlBuilder sb = new SqlBuilder();
SqlStatement stmt = sb.GetStatement( sql, StatementType.Select, typeof(MailingList) );
Assert.IsNotNull( stmt, "stmt is null" );
SqlResult sr = Broker.Execute( stmt );
Assert.IsNotNull( sr );
Assert.AreEqual( 0, sr.ErrorCode );
Assert.IsNotNull( sr.Rows.Count );
Assert.AreEqual( 1, sr.Rows.Count );
object[] row = (object[]) sr.Rows[ 0 ];
int expected = Convert.ToInt32( row[ 0 ] );
// verify StatementType.Count retrieval
sr = Broker.Execute( sb.GetStatement( StatementType.Count, typeof(MailingList) ) );
Assert.AreEqual( expected, sr.Count );
}
public void TestSqlResultViewGeneration()
{
SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(MailingList) );
SqlResult sr = sb.GetStatement().Execute();
DataView dv = ObjectView.GetDataView( sr );
// TODO add stuff to validate the contents of the view
}
public void TestCustomListUsingInConstraint()
{
// subselects not supported by the inferior mysql engine - skip test.
if( ! Broker.ProviderName.Equals( "MySQL" ) )
{
GentleSqlFactory sf = Broker.GetSqlFactory();
// first, select the number of expected entries
SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(MailingList) );
string sql = String.Format( "select distinct MemberAddress from {0}",
sf.GetTableName( "ListMember" ) );
sb.AddConstraint( Operator.In, "SenderAddress", Broker.Execute( sql ), "MemberAddress" );
SqlResult sr = Broker.Execute( sb.GetStatement() );
int expected = sr.Count;
// verify list retrieval (using IList data)
IList lists = MailingList.ListByCustomListConstraint();
Assert.IsNotNull( lists );
Assert.AreEqual( expected, lists.Count );
// verify same result using alternative approach (using SqlResult data)
sb = new SqlBuilder( StatementType.Select, typeof(Member) );
SqlResult members = sb.GetStatement( true ).Execute();
sb = new SqlBuilder( StatementType.Select, typeof(MailingList) );
// use column name as indexer into SqlResult for list constraints
sb.AddConstraint( Operator.In, "SenderAddress", members, "MemberAddress" );
SqlStatement stmt = sb.GetStatement( true );
lists = ObjectFactory.GetCollection( typeof(MailingList), stmt.Execute() );
Assert.IsNotNull( lists );
Assert.AreEqual( expected, lists.Count );
}
}
/// <summary>
/// Example list retrieval of objects using a custom "in" constraint.
/// </summary>
/// <returns>A list of MailingList objects</returns>
public static IList ListByCustomListConstraint()
{
SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(MailingList) );
sb.AddConstraint( Operator.In, "SenderAddress", Member.ListAll, "Address" );
SqlStatement stmt = sb.GetStatement( true );
return ObjectFactory.GetCollection( typeof(MailingList), stmt.Execute() );
}
public virtual void TestNullNotEquals()
{
SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(Member) );
sb.AddConstraint( Operator.NotEquals, "MemberName", null );
SqlStatement stmt = sb.GetStatement( true );
}
public void TestOrderByRetrieval()
{
GentleSqlFactory sf = Broker.GetSqlFactory();
// first verify that data set is ok for test
SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(MailingList) );
// get ascending
sb.AddOrderByField( true, "SenderAddress" );
SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained == MailingList.ListAll.Count,
"Wrong number of rows were selected." );
IList lists = ObjectFactory.GetCollection( typeof(MailingList), sr );
Assert.IsNotNull( lists, "Test case invalid if row count is not 3." );
Assert.IsTrue( lists.Count == 3, "Test case invalid if row count is not 3." );
l1 = lists[ 0 ] as MailingList;
l2 = lists[ 2 ] as MailingList;
Assert.IsTrue( l1.SenderAddress.StartsWith( "ann" ), "Test case invalid if row order is wrong." );
Assert.IsTrue( l2.SenderAddress.StartsWith( "inf" ), "Test case invalid if row order is wrong." );
// now fetch the reverse ordered list
sb = new SqlBuilder( StatementType.Select, typeof(MailingList) );
sb.AddOrderByField( false, "SenderAddress" );
sr = Broker.Execute( sb.GetStatement( true ) );
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained == MailingList.ListAll.Count,
"Wrong number of rows were selected." );
IList lists2 = ObjectFactory.GetCollection( typeof(MailingList), sr );
l1 = lists2[ 0 ] as MailingList;
l2 = lists2[ 2 ] as MailingList;
Assert.IsTrue( l1.SenderAddress.StartsWith( "inf" ), "Result set was in wrong order." );
Assert.IsTrue( l2.SenderAddress.StartsWith( "ann" ), "Result set was in wrong order." );
}
public void TestPaging()
{
GentleSqlFactory sf = Broker.GetSqlFactory();
if( sf.HasCapability( Capability.Paging ) )
{
// first verify that data set is ok for test
IList lists = MailingList.ListAll;
Assert.IsNotNull( lists, "Test case invalid if row count is below 3." );
Assert.IsTrue( lists.Count >= 3, "Test case invalid if row count is below 3." );
// try with 1 row limit
SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(MailingList) );
sb.SetRowLimit( 1 );
SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
// verify result
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected." );
Assert.AreEqual( 1, sr.RowsContained, "Result set 1 was not limited." );
MailingList ml = ObjectFactory.GetInstance( typeof(MailingList), sr ) as MailingList;
Assert.AreEqual( (lists[ 0 ] as MailingList).Id, ml.Id, "Wrong row(s) retrieved." );
// get next result
sr = sr.Next();
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected." );
Assert.AreEqual( 1, sr.RowsContained, "Result set 2 was not limited." );
ml = ObjectFactory.GetInstance( typeof(MailingList), sr ) as MailingList;
Assert.AreEqual( (lists[ 1 ] as MailingList).Id, ml.Id, "Wrong row(s) retrieved." );
// get next result
sr = sr.Next();
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected." );
Assert.AreEqual( 1, sr.RowsContained, "Result set 3 was not limited." );
ml = ObjectFactory.GetInstance( typeof(MailingList), sr ) as MailingList;
Assert.AreEqual( (lists[ 2 ] as MailingList).Id, ml.Id, "Wrong row(s) retrieved." );
// try the previous again
sr = sr.Previous();
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected." );
Assert.AreEqual( 1, sr.RowsContained, "Result set 4 was not limited." );
ml = ObjectFactory.GetInstance( typeof(MailingList), sr ) as MailingList;
Assert.AreEqual( (lists[ 1 ] as MailingList).Id, ml.Id, "Wrong row(s) retrieved." );
//Try Page Numbers..
sr = sr.Page( 2 );
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected." );
Assert.AreEqual( 1, sr.RowsContained, "Result set 5 was not limited." );
ml = ObjectFactory.GetInstance( typeof(MailingList), sr ) as MailingList;
Assert.AreEqual( (lists[ 1 ] as MailingList).Id, ml.Id, "Wrong row(s) retrieved." );
sr = sr.Page( 1 );
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected." );
Assert.AreEqual( 1, sr.RowsContained, "Result set 6 was not limited." );
ml = ObjectFactory.GetInstance( typeof(MailingList), sr ) as MailingList;
Assert.AreEqual( (lists[ 0 ] as MailingList).Id, ml.Id, "Wrong row(s) retrieved." );
sr = sr.Next();
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected." );
Assert.AreEqual( 1, sr.RowsContained, "Result set 7 was not limited." );
ml = ObjectFactory.GetInstance( typeof(MailingList), sr ) as MailingList;
Assert.AreEqual( (lists[ 1 ] as MailingList).Id, ml.Id, "Wrong row(s) retrieved." );
sr = sr.Page( 3 );
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected." );
Assert.AreEqual( 1, sr.RowsContained, "Result set 8 was not limited." );
ml = ObjectFactory.GetInstance( typeof(MailingList), sr ) as MailingList;
Assert.AreEqual( (lists[ 2 ] as MailingList).Id, ml.Id, "Wrong row(s) retrieved." );
//Test for Page 0 - should return first page
sr = sr.Page( 0 );
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected." );
Assert.AreEqual( 1, sr.RowsContained, "Result set 9 was not limited." );
ml = ObjectFactory.GetInstance( typeof(MailingList), sr ) as MailingList;
Assert.AreEqual( (lists[ 0 ] as MailingList).Id, ml.Id, "Wrong row(s) retrieved." );
//Test for invalid page - should return no rows
sr = sr.Page( 1 + (lists.Count / sr.Statement.RowLimit) );
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained == 0 );
}
}
public void TestRowLimitRetrieval()
{
GentleSqlFactory sf = Broker.GetSqlFactory();
// first verify that data set is ok for test
IList lists = MailingList.ListAll;
Assert.IsNotNull( lists, "Test case invalid if row count is below 3." );
Assert.IsTrue( lists.Count >= 3, "Test case invalid if row count is below 3." );
// try with 1 row limit
SqlBuilder sb = new SqlBuilder( StatementType.Select, typeof(MailingList) );
sb.SetRowLimit( 1 );
SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected." );
Assert.AreEqual( 1, sr.RowsContained, "Result set was not limited." );
// try again this time with 2 rows
sb = new SqlBuilder( StatementType.Select, typeof(MailingList) );
sb.SetRowLimit( 2 );
sr = Broker.Execute( sb.GetStatement( true ) );
Assert.IsTrue( sr.ErrorCode == 0 && sr.RowsContained > 0, "No rows were selected." );
Assert.AreEqual( 2, sr.RowsContained, "Result set was not limited." );
}
public void TestTypeListRetrieval()
{
GentleSqlFactory sf = Broker.GetSqlFactory();
// first, select the number of expected entries
SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(MailingList) );
SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
int expected = sr.Count;
// verify list retrieval (entire table)
IList lists = MailingList.ListAll;
Assert.IsNotNull( lists );
Assert.AreEqual( expected, lists.Count );
}
public void TestCount()
{
GentleSqlFactory sf = Broker.GetSqlFactory();
// first, select the number of expected entries
SqlResult sr = Broker.Execute( String.Format( "select count(*) as RecordCount from {0}{1}",
sf.GetTableName( "List" ), sf.GetStatementTerminator() ) );
Assert.IsNotNull( sr );
Assert.AreEqual( 0, sr.ErrorCode );
Assert.IsNotNull( sr.Rows );
Assert.AreEqual( 1, sr.Rows.Count );
object[] row = (object[]) sr.Rows[ 0 ];
int expected = Convert.ToInt32( row[ 0 ] );
// verify StatementType.Count retrieval
SqlBuilder sb = new SqlBuilder();
sr = Broker.Execute( sb.GetStatement( StatementType.Count, typeof(MailingList) ) );
Assert.AreEqual( expected, sr.Count );
}
public void TestMultiBroker()
{
if( GentleSettings.DefaultProviderConnectionString.IndexOf( "127.0.0.1" ) != -1 )
{
// use modified connection string
string connStr = GentleSettings.DefaultProviderConnectionString.Replace( "127.0.0.1", "(local)" );
PersistenceBroker pb = new PersistenceBroker( GentleSettings.DefaultProviderName, connStr );
// fetch list
IList pbList = pb.RetrieveList( typeof(MailingList) );
Assert.AreEqual( Broker.RetrieveList( typeof(MailingList) ).Count, pbList.Count, "Not same result." );
// check that connstr remains same when using SqlBuilder with custom broker
SqlBuilder sb = new SqlBuilder( pb, StatementType.Select, typeof(MailingList) );
SqlStatement stmt = sb.GetStatement( true );
SqlResult sr = stmt.Execute();
Assert.AreEqual( sr.SessionBroker.Provider.ConnectionString, connStr, "ConnStr not preserved." );
}
}
public void TestOtherTypeListRetrieval()
{
GentleSqlFactory sf = Broker.GetSqlFactory();
// select all lists
IList lists = MailingList.ListAll;
foreach( MailingList list in lists )
{
// get the expected member count
SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(Member) );
sb.AddConstraint( Operator.Equals, "ListId", list.Id );
SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
int expected = sr.Count;
// verify list retrieval (entire table)
IList members = list.Members;
Assert.IsNotNull( members );
Assert.AreEqual( expected, members.Count );
}
}
public virtual void TestSqlOverride()
{
if( ! Broker.ProviderName.Equals( "PostgreSQL" ) )
{
SqlBuilder sql = new SqlBuilder( StatementType.Select, typeof(MailingList) );
SqlStatement statement = sql.GetStatement( true );
ObjectMap map = ObjectFactory.GetMap( null, typeof(MailingList) );
statement.Command.CommandText = String.Format( "select * from {0}", map.TableName );
SqlResult result = statement.Execute();
IList list = ObjectFactory.GetCollection( typeof(MailingList), result );
Assert.AreEqual( 3, list.Count );
}
}
public void TestCustomListUsingLikeConstraint()
{
GentleSqlFactory sf = Broker.GetSqlFactory();
// first, select the number of expected entries
SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(MailingList) );
sb.AddConstraint( Operator.Like, "SenderAddress", "%.com" );
SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
int expected = sr.Count;
// verify list retrieval
IList lists = MailingList.ListByDomain( ".com" );
Assert.IsNotNull( lists );
Assert.AreEqual( expected, lists.Count );
}
public void TestCRUD()
{
l1 = new MailingList( "Test 1", "*****@*****.**" );
// insert
l1.Persist();
Assert.IsTrue( l1.Id > 0, "The List object was not assigned an id by the database!" );
Assert.AreEqual( l1.Name, "Test 1", "The List object was not properly inserted!" );
Assert.AreEqual( l1.SenderAddress, "*****@*****.**", "The List object was not properly inserted!" );
// select
l2 = MailingList.Retrieve( l1.Id );
// verify select/insert
Assert.IsTrue( l2.Id != 0, "The List object could not be retrieved from the database!" );
Assert.AreEqual( l1.Id, l2.Id, "The List object could not be retrieved from the database!" );
Assert.AreEqual( "Test 1", l2.Name, "The List object was not properly retrieved on construction!" );
Assert.AreEqual( "*****@*****.**", l2.SenderAddress,
"The List object was not properly retrieved on construction!" );
// update
l2.Name = "Test 2";
l2.SenderAddress = "*****@*****.**";
l2.Persist();
// verify update
l1 = MailingList.Retrieve( l2.Id );
Assert.AreEqual( l2.Name, l1.Name, "Name not updated!" );
Assert.AreEqual( l2.SenderAddress, l1.SenderAddress, "SenderAddress not updated!" );
// delete
l2.Remove();
// verify delete by counting the number of rows
SqlBuilder sb = new SqlBuilder( StatementType.Count, typeof(MailingList) );
sb.AddConstraint( Operator.Equals, "Id", l1.Id );
SqlResult sr = Broker.Execute( sb.GetStatement( true ) );
Assert.AreEqual( 0, sr.Count, "Object not removed" );
}
/// <summary>
/// Example list retrieval of objects using a custom "like" constraint.
/// </summary>
/// <param name="domain">A partial (right-side exact) string to match for senderaddress</param>
/// <returns>A list of MailingList objects</returns>
public static IList ListByDomain( string domain )
{
SqlBuilder sb = new SqlBuilder( StatementType.Select );
sb.AddConstraint( String.Format( "SenderAddress LIKE '%{0}'", domain ) );
SqlStatement stmt = sb.GetStatement( StatementType.Select, typeof(MailingList), true );
return ObjectFactory.GetCollection( typeof(MailingList), stmt.Execute() );
}
