The Blog of Maxim Porges

Posts Tagged ‘SQL’

  • Performing SQL Validation (a.k.a. “Data Dips”) with RIATest

    We started using RIATest at Highwinds to automate our Flex functional testing. For those of you who are unfamiliar, RIATest is a great Flex-only alternative to products like HP’s QuickTest Pro, allowing you to fully automate your Flex functional testing either from the RIATest IDE itself, or via the command line. While QuickTest Pro can be used to automate a wide variety of GUI-based apps, it costs about five times as much as RIATest, so if you are only testing Flex apps RIATest is a great low-cost alternative.

    Something else that QuickTest Pro lets you do that RIATest does not is perform “data dips” or, to put it in layman’s terms, query a database during functional testing. This can be useful for validating non-visual system state.

    For example, let’s say that you have an application that allows you to change user information, and that the requirements dictate that whenever the user information is changed, a last modified date on the user’s record should be updated. If the last modified date is not visible as a label within the Flex app, RIATest can’t validate that the last modified date has changed, since it can only validate the state of Flex components. In this scenario, you’d typically want to

    (a) execute a functional test to change a user’s info,
    (b) validate the changes to the UI after the user’s information is saved and loaded again for viewing, and
    (c) check the state of the database to validate that the last modified date has indeed been updated.

    However, since RIATest doesn’t offer data dip functionality, step (c) would be impossible without manual intervention from a QA tester. This is where riatest-integrator comes in.

    Thankfully, the RIATest developers put a special object in RIATest called Process. Process allows you to execute an external process, and interact with its input, output, and error streams. Once I discovered this, I decided to write a little Java library that would make it easy for me to fire up a Java process from the command line, hook RIATest to its input and output streams, and write input from RIATest to it. The process would then pass the input to some kind of integration inside of the Java process, and return output from the integration for inspection by RIATest. This turned out to be pretty easy as expected, and I published the library to Google Code under the name riatest-integrator.

    Currently, there is a download available on the Google Code site that lets you query MySQL databases right out of the box. There is a sample RIATest script on the home page of the project showing how you can call it from RIATest and validate output coming back from the database. It’s a really simple implementation, but if people are interested in it, I’ll add more features and functions. The API is also really straightforward, so any Java developer with even the most modest skills should be able to customize it to their needs.

    The API in the library is not limited to SQL; anything that Java can talk to can be hooked in to. We have some proprietary back-end servers at Highwinds that speak NNTP, so we could easily write an NNTP integration that allows us to have RIATest query the state of our NNTP message groups after actions are taken in our Flex apps.

    If you are a RIATest user, please take a look at the project, and share your feedback either here on my blog, or as feature/bug requests in the project’s issue tracker.

    2010.01.19 / 3 responses / Category: Uncategorized

  • Calculating the Differences Between Consecutive Rows with SQL

    Just a quickie. I’m sure this is obvious to the seasoned SQL freaks out there, but it was a new solution for me.

    We were recently trying to figure out a simple query to generate a set of differences in values between sets of consecutive rows in a database table. Basically, the table is full of timestamp snapshots with associated values, and we need to calculate the differences between the values in each snapshot for a time period.

    The simple solution I came up with is to normalize the dates, and then use them as a join criteria with a virtual table and some simple date math. You treat the original unmodified table as one side of the join, and a virtual select of the same table with some date math applied to the normalized dates as the other side of the join. So, for tables with timestamps five seconds apart, you simply pull the date back by five seconds on the virtual table and join the dates together. You end up with a single row joining each row with its previous peer, and you can then do direct math within the row to calculate the differences in values.

    Here’s a simple MySQL script that demonstrates the technique, which will probably make more sense than my explanation.

    CREATE TABLE consecutiveDates (
  id INT NOT NULL AUTO_INCREMENT,
  valueDate TIMESTAMP NOT NULL,
  value NUMERIC(10) NOT NULL,
  PRIMARY KEY (id)
)
 
INSERT INTO consecutiveDates VALUES (NULL, TIMESTAMP('2009-08-01 10:00:00'), 10);
INSERT INTO consecutiveDates VALUES (NULL, TIMESTAMP('2009-08-01 10:00:05'), 25);
INSERT INTO consecutiveDates VALUES (NULL, TIMESTAMP('2009-08-01 10:00:10'), 50);
 
SELECT    actual.*,
          virtual.*,
          IFNULL((virtual.virtualValue - actual.value), 0) AS 'difference'
FROM      consecutiveDates actual
          LEFT OUTER JOIN (
             SELECT   id AS 'virtualId',
                      valueDate AS 'actualVirtualDate',
                      value AS 'virtualValue',
                      TIMESTAMPADD(SECOND, -5, valueDate) AS 'adjustedVirtualDate'
             FROM     consecutiveDates
          ) virtual ON virtual.adjustedVirtualDate = actual.valueDate

    If you have dates that don’t line up perfectly like my example, you can use basic date math/conversion to normalize the dates in each row in to a rounded representation (i.e. nearest five seconds, nearest minute, etc.). With basic SQL grouping, you can then easily smash together rows that are close enough to each other to be combined in to a single row and achieve the same effect as shown in the script.

    This technique is obviously not limited to dates; you can use a similar approach with virtual row IDs in MySQL. By creating an integer session variable representing a row ID, and then selecting/incrementing it with each row in the query, you can apply basic math to the virtual table’s select statement to offset the session variable by -1 and use that as the join criteria.

    2009.10.01 / 1 response / Category: Uncategorized