MySQL multiple table update & Regular Expression

Hello all

Though its a very simple process but we usually don't do this stuff too much unless we are bound to do that.In this article I will show how to update a table data with another table depending on another table & another will be a simple regular expression in MySQL query.

In the example I have 'new_users' table to update with the data of 'listings' table depending on the condition or 'users' table. and the table structure is as bellow.

'new_users' :
userID,phone,user_state
'listings' :
listID,userID,phone
'users':
countID,userID,category

Now the scenario is I have to update the phone of new_users from the listings tables phone of same userID but only who are only 'Admin' which we may found in users table's  category field. So the query will be as following

-- Update from listings for 'Admin'
update listings, new_users, users
set
new_users.phone = listings.phone 
where
users.category = 'Admin' &&
listings.userID = users.userID &&
users.userID = new_users.userID

Now come to the second part, we can apply regular expression in our MySQL query as follow:

-- Update all user_state to null which contains invalid character
update new_users
set
new_users.user_state = null
where
new_users.user_state REGEXP '[1234567890~!@#$%^&*()_+|}{":?><,./;]'

The query will set null to user_state field if user_state field contains none but a-z & A-Z previously.

I have also some collection or regular expression for MySQL which i have collected from different site.

A very simple example illustrating this is to select all the records from MyTable for which MyField starts with "A"

SELECT * FROM  MyTable WHERE MyField REGEXP '^a';
Please take a look to the list below in order to find more information for the available options MySQL Regular Expressions
* 
Matches zero or more instances of the string preceding it
+ 
Matches one or more instances of the string preceding it
? 
Matches zero or one instances of the string preceding it
. 
Matches any single character
[xyz] 
Matches any of x, y, or z (the characters within the brackets)
[A-Z] 
Matches any uppercase letter
[a-z] 
Matches any lowercase letter
[0-9] 
Matches any digit 
^ 
Anchors the match from the beginning 
$ 
Anchors the match to the end
| 
Separates strings in the regular expression
{n,m} 
String must occur at least n times, but no more than n  
{n} 
String must occur exactly n times
{n,| 
String must occur at least n times

[Source]

 

That's all for the day.
BYE

User ScrumPad for your Agile based projects.

Isolated Unit Test of Business Logic With NMock in TDD

Hello All

In test driven development its always needed to test a particular project tested with make it isolate ( test with removing dependency from other projects ), In this scenario we virtually create the input set which will be produced from other projects(class,methods etc) and test whether our testable methods are functioning with that input set or not.

To do that we have to make mock of the dependent objects. In the example bellow I have shown how to test a business logic layer with isolating it from data access layer.I have created three projects Web.Data , Web.Core & BL.Test .

The Web.Data project contains the Data Access Layer which will be responsible for pulling data from the data source,but as we are isolating our business logic layer from this project so in the example I will not completely  implement our desired method( as it will not be executed).

To do that we need some tools such as NMock and NUnit . We will found the NMock.dll , nunit.core & nunit.framework in both of its bin folder where we extract or install those.

our Web.Data contains only two files one is IMockDAL.cs interface and MockDAL.cs class.

IMockDAL.cs interface :

using System.Collections.Generic;

namespace Web.Data
{
   public interface IMockDAL
    {
       /* The method will return a string list of
        * active member's name from data source .
        */
        List<string> GetNameListDAL(bool active);
    }
} 

MockDAL.cs Class :

using System;
using System.Collections.Generic;

namespace Web.Data
{
    public class MockDAL:IMockDAL
    {
        #region IMockDAL Members

        /* This method will not be executed during the test,
         * as we will do mock of this method.         
         */
       public List<string> GetNameListDAL(bool active)
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}

In our Web.Core there are also two files , one is IEmployeeService.cs interface & EmployeeService.cs the class file. in this project we have to add reference of Web.Data project as it has a dependency over that.
IEmployeeService.cs  interface :

using System.Collections.Generic;
using Web.Data;

namespace Web.Core
{
   public interface IEmployeeService
    {
        IMockDAL MockDAL { set; get; }
        List<string> GetAllEmployee();
    }
}

EmployeeService.cs Class :

using System.Collections.Generic;
using Web.Data;

namespace Web.Core
{
   public class EmployeeService : IEmployeeService
    {
        /* The MockDAL property will be used to assign
         * DAL through our mock object.
         */
        public IMockDAL MockDAL { set; get; }

        public EmployeeService()
        {
            MockDAL = new MockDAL();        
        }

        #region IEmployeeService Members

        /* The "GetAllEmploye" will call the "GetNameListDAL"
         * from the mock object. For the sake of simplicity 
         * I just call the DAL,not did any other operation.
        */

        public List<string> GetAllEmployee()
        {
            return MockDAL.GetNameListDAL(true);
        }

        #endregion
    }
} 

In BL.Test project we have to add some reference as it have dependency over  couple of projects and DLLs . We have to add reference of Web.Data & Web.Core and we also have to add NMock.dll , nunit.core & nunit.framework .In this project we have a class file named MockTest.cs

using System.Collections.Generic;
using NMock2;
using NUnit.Framework;
using Web.Data;
using Web.Core;

namespace BL.Test
{
    [TestFixture]
    public class MockTest
    {
        /* Assign property of our object that will be executed.
         */
        public Mockery _mock;
        public IMockDAL _mockDAL;
        public IEmployeeService _employeeService; 

        [SetUp]
        public void init()
        {
            /* initializing our objects.
             */
            _mock = new Mockery();
            _mockDAL = _mock.NewMock<IMockDAL>();
            _employeeService = new EmployeeService();
            _employeeService.MockDAL = _mockDAL;

        }

        [Test]
        public void TestEmployeeCount()
        {
            /* In the list bellow we are creating our virtual list of
             * string, which we are expecting after calling the DAL
             * this list will be returned.
             */
            List<string> employeeList = new List<string>();
            string emp = "Maix";
            string emp2 = "Pain";
            employeeList.Add(emp);
            employeeList.Add(emp2);


            /* _mockDAL -> is our mocked DAL object.
             * GetNameListDAL -> is one of the method name of the _mockDAL object.
             * With(true) -> the single boolean parameter of the GetNameListDAL method.
             * employeeList -> The expecter return value from the GetNameListDAL method.
             */
            Expect.Once.On(_mockDAL).Method("GetNameListDAL").With(true).Will( Return.Value(employeeList));

            List<string> employeeListResult = _employeeService.GetAllEmployee();

            /* Verify the mock object is executed successfully with its expectation.
             */
            _mock.VerifyAllExpectationsHaveBeenMet();

            Assert.AreNotEqual(null, employeeListResult);
        }
    }
}

To make it run we can set the project as our startup project and configure the NUnit .

That's all for the day.

BYE

User ScrumPad for your Agile based projects. 

 

UI test with WatiN for TDD in .NET

Hello all

Today I will show how to test UI ( User Interfaces ) in .NET with WatiN . WatiN is UI test framework. Though its a very simple but i have felt the documentation is not rich enough with example. So i have taken an initiative to make it more userfriendly for the developers.

I will not emphasize on how to configure WatiN as its already well documented in the following links. What i will try to do is make familiar with HTML controls that's needed to be validated .

Follow accordingly to configure WatiN :

1. Add reference as the documentation of WatiN, you dont have to follow the example which is documented there just add the reference.
2. If you don't know how to add reference please see this and for NUnit see this. 
3. Visit this link for a good CodeProject's example.

One of the common mistake we do is, open a class file in our website project and start the writing the test code in it, but what we have to do is make a new Class Project ,add reference in that project & select as startup project for NUnit test.

using WatiN.Core;
using NUnit.Framework;
using WatiN.Core.DialogHandlers;

namespace TestStie
{
  [TestFixture]
  public class AddArticleTest
    {

      [TestFixtureSetUpAttribute]
        public void SetUp()
        { 
        }

       [Test]
        public void TestAddArticle()
        {
            using (IE ie = new IE("http://localhost:2456/testsite/login.aspx"))
            {

                /*
                 * For textbox and textarea we have to use TextField() method and to search by 
                 * id of the control we have to use Find.ById() method & to fill the textbox 
                 * we have to use TypeText() method as following.                 
                 */

                ie.TextField(Find.ById("ctl00_UserName")).TypeText("maxpain");
                ie.TextField(Find.ById("ctl00_Password")).TypeText("c0de71~123");
               
                /*
                 * For make a check box cheked we have to do as following.                 
                 */
                ie.CheckBox(Find.ById("ctl00_chkActive")).Checked = true;
                
                /*
                 * To upload a file we have to do as bellow.
                 */
                ie.FileUpload(Find.ById("ctl00_LogoFile")).Set(@"C:\calligraphy.jpg");

                /*
                 * To selct a value form a dropdown list we have to follow as bellow.
                 * In the exmaple "Software Engineer" is selected item's value.
                 */
                ie.SelectList("ctl00_BodyPlaceHolder_ddlSkillCategory").Select("Software Engineer");

                /*
                 * For make a radio button cheked we have to do as following.                 
                 */
                ie.RadioButton(Find.ByName("ctl00$BodyPlaceHolder$rbPublish")).Checked = true;

                /*
                 * To click a link we have to do as bello
                 */
                ie.Link(Find.ByUrl("http://localhost:2456/testsite/AddArticle.aspx?id=3")).Click();

                /*
                 * To click a button we have to do as bellow.
                 */
                ie.Button(Find.ById("ctl00_LoginButton")).Click();

                /*
                 * Suppose we have to make confirm a delect action through JS confirmation box when we click 
                 * a link, then we have to do as follow.
                 */
                AlertDialogHandler alertDialogHandler = new AlertDialogHandler();
                ConfirmDialogHandler confirm = new ConfirmDialogHandler();
                using (new UseDialogOnce(ie.DialogWatcher, confirm))
                {
                    Link link = ie.Link(Find.ByUrl("http://localhost:2456/testsite/AllCaseStudy.aspx?id=5"));

                    link.ClickNoWait();
                    confirm.WaitUntilExists();
                    confirm.OKButton.Click();
                }
                ie.WaitForComplete();

                /*
                 * Now to check whether our operatin is successful or not we have to do assert test 
                 * as we do in unit test. Here ContainsText("xxxx") method returns true when it found
                 * the "xxxx" string in the page appear after any event perform.(generally we show
                 * a confirmation message after any successful event performed.) 
                 */
                Assert.IsTrue(ie.ContainsText("News Deleted successfuly"), "Deleted id not found.");
            }         
        }

        [TestFixtureTearDownAttribute]
        public void TearDown()
        {
        }
    }
}

Thats all for today.

BYE

User ScrumPad for your Agile based projects.