How to backup a remote MySql table to a spreadsheet (Excel, Calc, etc)

The other day, one of our customers asked for a spreadsheet export of the data he has stored in our MySQL database. One very easy way to export a remote database’s table to a spreadsheet is with using the -B commandline option:

mysql -h<remote database URL> -u<username> -p<password> <databasename> -e “SELECT * FROM whateveryourtableiscalled” -B > export.tsv

The -B option makes MySQL export to a Tabs Seperated format, which afterwards can easily be imported into Calc or Excel spreadsheets. Have fun!

Testing a web-app with Selenium (part1)

Today I want to deal with application testing.
I have split my post into several parts.
The first part deals with the definition of a simple test for an existing application already installed (e.g. in a test environment).
The second part is about how to perform integration testing during the build with Maven.

The problem: how to create automatic repetible integration tests for web apps?

The solution: write  test cases using Selenium.

Let’s start with a simple web-app. Notice that at this level the language used to write it is not relevant.
In this case I used wicket as ajax framework and maven to build it.
Our web-app is simple calculator performing a sum between two number. It’s composed by a panel with two text-box, a ajax button and a label for the result. It’s deployed on JBoss.

 

Our automatic test has to fill the two text boxes, click the “Add” button and assert the correct result.
Moreover, we can assert if all the widgets are presents on the user interface as well.
There are several ways to create an automatic test with Selenium:

  • using Selenium IDE (a powerful plug-in for Firefox) to record interactions with a browser
  • using Selenium WebDriver  to drive a browser programatically

Usually, for the beginners I suggest to start using SeleniumIDE to record a specific test case. After that  it’s always possible to convert the test case recorded in a specific language (JAVA, C#, Python) for use it in Selenium WebDriver.

Let’s start with the Selenium IDE:
To install it take a look at the IDE documentation (http://seleniumhq.org/projects/ide/).
Now, open Firefox at the application url. From the Firefox menù click on Tools -> Selenium IDE:

just click on the red button on the command bar and we are ready to record the test steps:

  1. in the first text box write the value “1”
  2. in the second write the value “5”
  3. click ADD

 

Notice in the IDE  the steps recorded. As last step we have to define the assert of correct result.
To do that, right click on the label containig the result –> click on Show All Available Commands –> wait for text present 6

Done! This is our first test case….
Save it with the name “TestSum”  and close both Firefox and Selenium.
Reopening Firefox (and Selenium), we load the test case to run it clicking on run on the command bar. Notice that Selenium open automatically the correct page, fills the fields and assert the result. If all is ok, you will see a green strip, otherwise a red one.

Now we have a test case that can be run on several environments (dev, test, pre-production…) just changing the Base URL on Selenium IDE.

With the same process we can record a test case to assert the components on our user interface. More test cases saved together form a Test Suite. In other words, I can define a test suite loading more test cases and save it together with the SeleniumIDE.

How to convert the test case in JAVA?
After we have recorded our test case, we want to convert it in java to perform more complex actions.
The first thing to do is export the test case directly from SeleniumIDE.
I export it as Java /JUnit4/Web driver.  I get in this way a simple java file that i import in an aclipse project.

This is the snippet:

public class TestSum {
 private WebDriver driver;
 private String baseUrl;
 private StringBuffer verificationErrors = new StringBuffer();
 @Before
 public void setUp() throws Exception {
  driver = new FirefoxDriver();
  baseUrl = "http://localhost:8080/";
  driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
 }
 @Test
 public void testSum() throws Exception {
  driver.get(baseUrl + "/");
  driver.findElement(By.xpath("//td/input")).clear();
  driver.findElement(By.xpath("//td/input")).sendKeys("1");
  driver.findElement(By.xpath("//td[2]/input")).clear();
  driver.findElement(By.xpath("//td[2]/input")).sendKeys("5");
  driver.findElement(By.xpath("//tr[2]/td/input")).click();
  for (int second = 0;; second++) {
   if (second >= 60) fail("timeout");
   try { if (driver.findElement(By.cssSelector("BODY")).getText().matches("^[sS]*6[sS]*$")) break; } catch (Exception e) {}
   Thread.sleep(1000);
  }
}

This is a simple JUnit class that uses WebDriver to drive the browser.
We import this in an eclipse project and we download the libs to compile it from http://seleniumhq.org/download/
Since we deal with a java class we have to download the java libs.

Let’s a look at the code: In this case the browser is Firefox, since i’m using  a FirefoxDriver, but you can choose more driver (InternetExplorerDriver, ChromeDriver and so on… ) The unique thing to check is your command path has to contain the path to the executable file of your browser.
Each element on the page is find by using XPath.

But what’s happen if we attempt to run this class?

As if by magic Selenium WEBDriver opens automatically Firefox and executes the steps of the test!!!
So we have find a way to control a browser programmatically. This opens a wide scenario.
For istance, use the power of a language as Java to define several actions and define articulated tests, schedule integration test overnight on different environments… There is no limit!!

But this step is very important for the second part of this post. Since we have a JUnit class we can think to execute it during the build phase with maven!
But this will be trated in the near future….
That’s all for now.

Carmine.

Wicket – How to replace a panel with Ajax

Today I’d like to deal with a common problem in wicket application. How to replace dinamically a panel. Let’s start…

The problem:
You have a Radio Choice with two buttons, when you click on the first button, the panel1  is diplayed, when you click on the second one, the panel2 is displayed in place of the last one and so on…

The solution: I write two different html files for the two panels: panel1.html and panel2.html.

<wicket:panel>
 this is the panel 1
 </wicket:panel>
<wicket:panel>
 this is the panel 2
 </wicket:panel>

The java class for each panel looks like this:

public class Panel1 extends Panel{
    public Panel2(String id) {
       super(id);
       //write other code here...
    }
}

Now we can start with the parent component wich will contain the panels and the radio component.
The parent component can be a Page or another panel. For this example it is a wicket page. Here is the html code:

<html>
    <head>
        <title>Replace Panel example</title>
    </head>
    <body>
        <form wicket:id="form">
          <span wicket:id="rChoice"></span>
          <span wicket:id="replacedPanel"></span>
        </form>
    </body>
</html>

Under the tag form we have two components, the radio choice and a panel. This panel will be replaced each time the user changes the radio selection.

To do that using Ajax, we have to attach a behaviour to the radio choice component.
Let’s take a look at the java code…

public class HomePage extends WebPage {
    private Panel replacedPanel;
    public HomePage(final PageParameters parameters) {
        List<String> choices = new ArrayList<String>();
        choices.add("panel1");
        choices.add("panel2");
        final RadioChoice<String> rChoice = new RadioChoice<String>("rChoice");
        rChoice.setModel(new Model<String>(selectedPanel));
        rChoice.setChoices(choices);
        rChoice.add(new AjaxFormChoiceComponentUpdatingBehavior() {
              @Override
              protected void onUpdate(AjaxRequestTarget target) {
                  Panel newPanel = null;
                  if ("panel1".equals(rChoice.getModelObject())){
                      newPanel = new Panel1("replacedPanel");
                  }else{
                      newPanel = new Panel2("replacedPanel");
                  }
                  newPanel.setOutputMarkupId(true);
                  replacedPanel.replaceWith(newPanel);
                  target.addComponent(newPanel);
                  replacedPanel = newPanel;  
              }
        });
        //this shows the panel1 when you load for the first time the page
        replacedPanel = new Panel1("replacedPanel");
        replacedPanel.setOutputMarkupId(true);
        Form<String> form = new Form<String>("form");
        form.add(rChoice);
        form.add(replacedPanel);
        this.add(form);       
    }
}

Finished!
As you can see, the most important thing is that the two panels should have the same id (in this case is replacedPanel).
This is a general example, maybe the way to create the panels in the onUpdate method is not elegant but it’s out of scope from this example.

That’s all for now.
Carmine

(Yet another) PHP Unzip code

Basically it’s just a “merge” of this and this combining the best of both (the second one rely on a system(‘zip’) call which often a standard php hosting doesn’t allow).

 

<?php
    // The unzip script
    // Created by Max at http://www.solvedo.com
    //
    // This script lists all of the .zip files in a directory
    // and allows you to select one to unzip. 
    //
    // To use this script, FTP or paste this script into a folder where you can
    // invoke it remotey (i.e. www.mysite.com/unzip.php"
 
    // TO CUSTOMIZE: Directory where to read and unzip the files
    // N.B. Unzip will be done in the '$workdir/zipfilename.zip.tmp/' folder.
    $workdir = 'tmp/';
	
    // See if there's a file parameter in the URL string
    $file = $_GET['file'];
 
    if (isset($file)) {
		echo "Unzipping " . $file . "
"; $zip = new ZipArchive; $res = $zip->open($workdir.$file); if ($res === TRUE) { if ($zip->extractTo($workdir.$file.'.tmp/')){ echo "Unzip done."; } else { echo "Unzip failed."; } $zip->close(); } else { echo "Failed to open file."; } exit; } // create a handler to read the directory contents $handler = opendir($workdir); echo "Please choose a file to unzip: " . "
"; // A blank action field posts the form to itself echo "
"; $found = FALSE; // Used to see if there were any valid files // keep going until all files in directory have been read while ($file = readdir($handler)) { if (preg_match ('/.zip$/i', $file)) { echo " " . $file . "
"; $found = true; } } closedir($handler); if ($found == FALSE) echo "No files ending in .zip found
"; else echo "
Warning: Existing files will be overwritten.

"; echo "
"; ?>

Spring-Security3 PreAuthentication + JSF2

Spring Security (formerly Acegi Security) is for sure one of the most flexible framework to address security+authentication for a real-life web application. Its pluggable mechanism offers an easy path to achieve security in a large number of contexts.

Recently I decided to use it for a webapp that will be deployed and used by an organization which use a custom SSO strategy.

From the webapp perspective this means that we don’t have to authenticate users, this was already done by the SSO framework. What the webapp need to know is basically who is the user and which role he play in the webapp domain.

The “which” part is essentially a mapping of the organization-wide user grants to the webapp specific (three) roles.

Spring Security offers a rich set of ready-made tools for the “pre-authenticated” scenario (link). Unfortunately I didn’t found any real life example showing how to use these tools. So here comes this article.

First of all the web.xml…

...
<context-param>
  <param-name>contextConfigLocation</param-name>
  <param-value>classpath*:applicationContext.xml, 
               classpath*:applicationContext-security.xml</param-value>
</context-param>

<filter>
  <filter-name>springSecurityFilterChain</filter-name>
  <filter-class>org.springframework.web.filter.DelegatingFilterProxy</filter-class>
</filter>

<filter-mapping>
  <filter-name>springSecurityFilterChain</filter-name>
  <url-pattern>/*</url-pattern>
</filter-mapping>
...

Here you have to customize two things:

  • the contextConfigLocation value to meet your configuration setup (in the example above there are two separate files, one for the generic Spring beans configuration, another for the security configuration only)
  • the url-pattern for the filter mapping that need to address your specific security requirement. (in the example all pages will be “secured”)

Now, let’s see the applicationContext-security.xml….

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:context="http://www.springframework.org/schema/context"
  xmlns:sec="http://www.springframework.org/schema/security"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
    http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-3.1.xsd">

<!-- <sec:debug/> --> <!-- Uncomment this to have verbose debug informations -->

  <sec:http realm="My Realm" auto-config='true' create-session="ifRequired" disable-url-rewriting="true">
    <sec:intercept-url pattern="/**" access="ROLE_USER"/>    
    <sec:custom-filter ref="myPreAuthFilter" position="PRE_AUTH_FILTER"/>    
    <sec:session-management session-fixation-protection="newSession"/>
  </sec:http>

  <sec:authentication-manager alias="authenticationManager">
    <sec:authentication-provider ref='preAuthenticatedAuthenticationProvider'/>
  </sec:authentication-manager>

  <bean id="myPreAuthFilter" class="mypackage.MyPreAuthenticatedProcessingFilter">
    <property name="authenticationManager" ref="authenticationManager"/>
    <property name="authenticationDetailsSource" ref="authenticationDetailsSource"/>
    <property name="continueFilterChainOnUnsuccessfulAuthentication" value="false"/>
  </bean>

  <bean id="authenticationDetailsSource" class="mypackage.MyAuthenticationDetailsSource" />

  <bean id="authenticationManager" class="org.springframework.security.authentication.ProviderManager">
    <constructor-arg>
      <list>
        <ref bean="preAuthenticatedAuthenticationProvider"/>
      </list>
    </constructor-arg>
  </bean>

  <bean id="preAuthenticatedAuthenticationProvider" class="org.springframework.security.web.authentication.preauth.PreAuthenticatedAuthenticationProvider">
    <property name="preAuthenticatedUserDetailsService" ref="preAuthenticatedUserDetailsService"/>
  </bean>
  <bean id="preAuthenticatedUserDetailsService" class="org.springframework.security.web.authentication.preauth.PreAuthenticatedGrantedAuthoritiesUserDetailsService"/>
</beans>

Let’s see the most important things …

  • the sec:http part configure the relevant things in our secure  realm, inside this we are saying that
  • we want to intercept access to every page, including sub-folders (/**), and make them accessible to users with role “ROLE_USER” (which in our scenario means to every SSO authenticated user) and
  • we want to place our pre-authenticated filter in the Spring-Security filters-chain in the appropriate position (more here)

The rest of the file says that we want to use a certain authenticationManager, a custom authenticationFilter and a custom authenticationDetailSource.

The easier way to understand what those filter and detail source do it’s to look to their source:

public class MyPreAuthenticatedProcessingFilter extends AbstractPreAuthenticatedProcessingFilter{

  @Override
  protected Object getPreAuthenticatedPrincipal(HttpServletRequest request) {
    MyUser user = null;
      try {       
        // Here you have to extract the user from the request.
        // This is SSO framework dependant.
        user = customMethodToGetTheUserFromTheRequest(request);
      } catch (MyException e) {
        throw new AuthenticationServiceException("Error....", e);
      }

    return user; //User must implement java.security.Principal
  }

  @Override
  protected Object getPreAuthenticatedCredentials(HttpServletRequest request) {
    //Normally this should return the password or any other credential
    return "N/A";
  }
}
public class MyAuthenticationDetailsSource implements AuthenticationDetailsSource {

	@Override
	public GrantedAuthoritiesContainer buildDetails(HttpServletRequest request) {
		MyUser user = customMethodToGetTheUserFromTheRequest(request);

		List gal = new ArrayList();
		try{
			GrantedAuthority ga = null;
			if (user.isMemeberOfTheOrganization()){
				ga = new SimpleGrantedAuthority(Roles.ROLE_USER);
				gal.add(ga);
			}
			if (user.isMemberOfTheATeam()){
				ga = new SimpleGrantedAuthority(Roles.ROLE_MASTER);
				gal.add(ga);
			}
			if (user.isMemberOfTheBTeam()){
				ga = new SimpleGrantedAuthority(Roles.ROLE_ADMIN);
				gal.add(ga);
			}
		} catch (MyException e) {
			throw new AuthenticationServiceException("Error..", e);
		}

		return new PreAuthenticatedGrantedAuthoritiesWebAuthenticationDetails(request, gal);
	}
}

The two classes are almost self-explicative. The first one retrieve/build the webapp user reading the SSO data from the request.
The second maps the organizational user roles to the webapp user roles.

That’s all :)