/// <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() ); }