Handling database in external tests

[NoMercyIncluded]

Hello,
I want to perform some functional testing using Selenium. I have launched demo site and everything is well, but then I face one problem - I have no idea how to operate database so I can clean effects of test cases.

For example:
I have several test cases generated for register form. When I execute test case with valid data, user gets registered and therefore it might affect further cases.

Of course database it reset during every startup by defaul, but that is of no use - I want to be able to operate data freely, so I can delete exact entries rather than everything, not to mention doing full startup for every test case is silly.
My tests are in another project and I would like to just use jdbc driver there, but it is very hard to directly alter database - foreign keys, cascade dependencies and overall I feel like it is bruteforce solution.

I would be grateful if you could give me some hints about how to do it in more elegant way. Maybe creating test in external project is not good practice? If so, can you point some precise documentation that won't take ages to read and understand?
I am 100% newbie in webapp and JEE environment, so please have mercy, I won't be able to dig through complex stuff.

Thank you in advance.

[prabhat.kataria]

BLC uses hibernate and JPA to manage the database interactions. One feature of hibernate is to automagically create the whole database based on the Java classes having information about the tables.

Now if you do not want a clean database on every startup, then you can do the following:
1.go to site/src/main/resources/runtime-properties/development.properties and set value of blPU.hibernate.hbm2ddl.auto to create.
2.start the application
3.make backup script of the database
4.shutdown the server
5.again go to site/src/main/resources/runtime-properties/development.properties and set value of blPU.hibernate.hbm2ddl.auto to none.
6. start the server when you are ready to test.Now your database will be same as you leave it.

As for the second part of your question on how to clean up the test data from database, what I would suggest is to have a copy of main database for testing purpose that can keep all the test data. If this option does not suffice then the next option I would suggest is understanding the database code of BLC and create your own logic/interface to delete the data manually.

[phillipuniverse]

Good suggestions, prabhat. I have done something similar in browser tests (although we're using Geb (http://www.gebish.org) instead of raw Selenium) that I have done with Broadleaf. Here's what I did:

1. Expose some URL on the test instance like /database/snapshots. In my case, the underlying OS is Linux so I can use mysqldump to get a current dump of the database. You can invoke underlying system calls in Java by using Runtime.exec():

Code: Select all


String filePath = System.getProperty('java.io.tmpdir') + "dump.sql");
        String cmd = "mysqldump --opt --skip-lock-tables --ignore-table=" + connection.database + ".BLC_STATIC_ASSET_STRG -c -e -Q -u "
                + connection.user +
                " -p" + connection.password +
                " -h " + connection.uri.getHost() +
                " -r " + filePath +
                " " + connection.database;
               
        LOG.warn("Executing dump command: " + cmd);
        Process process = Runtime.getRuntime().exec(cmd);
        int processResult = 0;
        try {
            processResult = process.waitFor();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

public ConnectionProps getConnectionProperties() {
        SessionFactoryImpl sessionFactory = (SessionFactoryImpl) ((HibernateEntityManager) em).getSession().getSessionFactory();
        Properties properties = sessionFactory.getProperties();
        String url = (String) properties.get("database.url");
        String cleanURI = url.substring(5);
        URI uri = URI.create(cleanURI);
       
        String username = (String) properties.get("database.user");
        String password = (String) properties.get("database.password");
       
        return new ConnectionProps(username, password, uri);
    }


The URL will output the filePath where the file was saved.

2. Before your test starts, hit the /database/snapshot URL and save off the filePath
3. Create a /database/reload url mapping that takes in a filePath as a request parameter. Then you can just execute the Sql script from the file by using something like JdbcTemplate.

This is pretty slow. The database dump itself is very quick (a few seconds) but re-importing using JDBC takes ~25 seconds.

I think that this is an EXCELLENT use of Docker. If you are using Docker containers (or at least have your MySQL instance inside of a Docker container), you can do the same thing with the /database/snapshot and /database/reload URLs except that instead of communicating with the database itself, you control the system's Docker process to commit the container when you hit /database/snapshot and then once you're done with the test, restart the MySQL Docker container. All of the data that was added on will be blown away and you'll have a fresh instance of your data.

With Docker, the snapshot and reload process will take milliseconds. I haven't finished proving this out, but I think that's really the way to go if you want good test isolation.