PostgreSQL 101

PostgreSQL is the finest open source example of the relational database management system (RDBMS) tradition. This means that PostGreSQL is a design-first data store. First you design the schema, and then you enter data that conforms to the definition of that schema.

History of PostgreSQL

PostgreSQL has existed in the current project incarnation since 1995, but its roots
are considerably older. The original project was written at Berkeley in the early 1970s
and called the Interactive Graphics and Retrieval System, or “Ingres” for short. In the
1980s, an improved version was launched post-Ingres—shortened to Postgres. The
project ended at Berkeley proper in 1993 but was picked up again by the open source
community as Postgres95. It was later renamed to PostgreSQL in 1996 to denote its
rather new SQL support and has remained so ever since.

Installation

You can download the latest PostgreSQL installer from http://www.postgresql.org/download (In this tutorial I’m using the Windows 64bit installer). Setup is straight-forward. Below are screen-shots of installation steps:

remember the superuser password, you will need it later

when the PostgreSQL installation finish, you could choose to run Stack Builder to install additional tools. In my case I chose to run Stack builder

choose your recently installed instance

and I choose to install Npgsql database driver (for future .Net development) and phpPgAdmin for administration tasks.

Stack Builder will lunch installation wizards for your chosen tools in sequence

 

Starting with PostgreSQL

To get started, open pgAdmin III from your start menu. On the left hand side right-click on your server instance and click Connect

 

enter the password you used in the setup

Once you connected to your server, you can right click on the databases node and click New Database

we going to create a simple database called book with the default settings

to start writing queries, you could open Tools menu, then click Query Tool (or press CTRL + E).

Working with Tables

Creating a table consists of giving it a name and a list of columns with types
and (optional) constraint information. Each table should also nominate a
unique identifier column to pinpoint specific rows. That identifier is called a
PRIMARY KEY. The SQL to create a countries table looks like this:

CREATE TABLE countries (country_code char(2) PRIMARY KEY,country_name text UNIQUE);

copy and paste this code in the Query Tool and click on the toolbar to run it.

To insert values in our countries table:

INSERT INTO countries (country_code, country_name)
VALUES ('us','United States'), ('mx','Mexico'), ('au','Australia'),
('uk','United Kingdom'), ('de','Germany'), ('ll','Loompaland');

To select from our table:

SELECT * FROM countries;

To delete :

DELETE FROM countries
WHERE country_code = 'll';

Now, let’s add a cities table. To ensure any inserted country_code also exists in our countries table, we add the REFERENCES keyword. Since the country_code column references another table’s key, it’s known as the foreign key constraint.

CREATE TABLE cities (
name text NOT NULL,
postal_code varchar(9) CHECK (postal_code <> ''),
country_code char(2) REFERENCES countries,
PRIMARY KEY (country_code, postal_code)
);

we constrained the name in cities by disallowing NULL values. We constrained postal_code by checking that no values are empty strings (<> means not equal). Furthermore, since a PRIMARY KEY uniquely identifies a row, we created a compound key: country_code + postal_code. Together, they uniquely define a row. now if you try to run this code you will get an error because we violating referential integrity:

INSERT INTO cities
VALUES ('Toronto','M4C1B5','ca');

To update records:

INSERT INTO cities
VALUES ('Portland','87200','us');
UPDATE cities
SET postal_code = '97205'
WHERE name = 'Portland';

Inner Join

Being a relational database gives PostgreSQL the ability to join tables together when reading them. Joining, in essence, is an operation taking two separate tables and combining them in some way to return a single table. It’s somewhat like shuffling up Scrabble pieces from existing words to make new words. The basic form of a join is the inner join. In the simplest form, you specify two columns (one from each table) to match by, using the ON keyword.

SELECT cities.*, country_name
FROM cities INNER JOIN countries
ON cities.country_code = countries.country_code;

The join returns a single table, sharing all columns’ values of the cities table plus the matching country_name value from the countries table. We can also join a table like cities that has a compound primary key. To test a compound join, let’s create a new table that stores a list of venues. A venue exists in both a postal code and a specific country. The foreign key must be two columns that reference both cities primary key columns. (MATCH FULL is a constraint that ensures either both values exist or both are NULL.)

CREATE TABLE venues (
venue_id SERIAL PRIMARY KEY,
name varchar(255),
street_address text,
type char(7) CHECK ( type in ('public','private') ) DEFAULT 'public',
postal_code varchar(9),
country_code char(2),
FOREIGN KEY (country_code, postal_code)
REFERENCES cities (country_code, postal_code) MATCH FULL
);

This venue_id column is a common primary key setup: automatically incremented integers (1, 2, 3, 4, and so on…). Creating new row will populate this column automatically. We make this identifier using the SERIAL keyword. CHECK keyword check that a column supplied value against a set of predefined values. DEFAULT provide a default value to be inserted in case user supplied nothing.

INSERT INTO venues (name, postal_code, country_code)
VALUES ('Crystal Ballroom', '97205', 'us');

Joining the venues table with the cities table requires both foreign key columns.

SELECT venues.venue_id, venues.name, cities.name
FROM venues INNER JOIN cities 
ON venues.postal_code = cities.postal_code AND venues.country_code = cities.country_code;

Outer Join

Outer joins are a way of merging two tables when the results of one table must always be returned, whether or not any matching column values exist on the other table. Let's create a table and populate it for testing this:

CREATE TABLE events (
event_id SERIAL PRIMARY KEY,
title varchar(255),
starts timestamp,
ends timestamp,
venue_id integer,
FOREIGN KEY (venue_id)
REFERENCES venues (venue_id) MATCH FULL
);

INSERT INTO events (title, starts, ends, venue_id)
VALUES ('LARP Club', '2012-02-15 17:30:00', '2012-02-15 19:30:00', 1);
INSERT INTO events (title, starts, ends)
VALUES ('April Fools Day', '2012-04-01 00:00:00', '22012-04-01 23:59:00');
INSERT INTO events (title, starts, ends)
VALUES ('Christmas Day', '2012-12-25 00:00:00', '2012-04-01 23:59:00');

Now the results of the following two queries showing you the difference between INNER JOIN and OUTER JOIN.

SELECT events.title, venues.name
FROM events JOIN venues
ON events.venue_id = venues.venue_id;

SELECT events.title, venues.name
FROM events LEFT JOIN venues
ON events.venue_id = venues.venue_id;

Finally, there’s the FULL JOIN, which is the union of LEFT and RIGHT; you’re guaranteed all values from each table, joined wherever columns match.

Indexes

RDBMSs uses indexes to reducing disk reads and query optimization (among other things). If we select the title of Christmas Day from the events table, the algorithm must scan every row for a match to return. Without an index, each row must be read from disk to know whether a query should return it. An index is a special data structure built to avoid a full table scan when performing a query. PostgreSQL (like any other RDBMS) automatically creates an index on the primary key, where the key is the primary key value and where the value points to a row on disk. Using the UNIQUE keyword is another way to force an index on a table column. PostgreSQL also creates indexes for columns targeted by FOREIGN KEY constraint.

You can explicitly add a hash index using the CREATE INDEX command, where each value must be unique. btree is the suitable data structure for ranges. For more information, refer to online documentation.

CREATE INDEX events_starts
ON events USING btree (starts);

Aggregate Functions

An aggregate query groups results from several rows by some common criteria. It can be as simple as counting the number of rows in a table or calculating the average of some numerical column. Examples:

SELECT count(title)
FROM events

SELECT min(starts), max(ends)
FROM events

Aggregate functions are useful but limited on their own. If we wanted to count all events at each venue, we could write the following for each venue ID (which is tedious):

SELECT count(*) FROM events WHERE venue_id = 1;
SELECT count(*) FROM events WHERE venue_id = 2;
SELECT count(*) FROM events WHERE venue_id = 3;
SELECT count(*) FROM events WHERE venue_id IS NULL;

Grouping

With GROUP BY, you tell Postgres to place the rows into groups and then perform some aggregate function (such as count()) on those groups.

SELECT venue_id, count(*)
FROM events
GROUP BY venue_id;

The GROUP BY condition has its own filter keyword: HAVING. HAVING is like the WHERE clause, except it can filter by aggregate functions (whereas WHERE cannot).

SELECT venue_id
FROM events
GROUP BY venue_id
HAVING count(*) = 1;

You can use GROUP BY without any aggregate functions. If you SELECT one column, you get all unique values.
SELECT venue_id FROM events GROUP BY venue_id;
This kind of grouping is so common that SQL has a shortcut in the DISTINCT keyword.
SELECT DISTINCT venue_id FROM events;
The results of both queries will be identical.

Window Functions

Window functions are similar to GROUP BY queries in that they allow you to run aggregate functions across multiple rows. The difference is that they allow you to use built-in aggregate functions without requiring every single field to be grouped to a single row. If we attempt to select the title column without grouping by it, we can expect an error.

We are counting up the rows by venue_id, and in the case of LARP Club and Wedding, we have two titles for a single venue_id. Postgres doesn’t know which title to display. Whereas a GROUP BY clause will return one record per matching group value, a window function can return a separate record for each row. Window functions return all matches and replicate the results of any aggregate function. It returns the results of an aggregate function OVER a PARTI TI ON of the result set.

Transactions

Transactions ensure that every command of a set is executed. If anything fails along the way, all of the commands are rolled back like they never happened. It’s the all or nothing motto that gives relational databases its consistency capability.

PostgreSQL transactions follow ACID compliance, which stands for Atomic (all ops succeed or none do), Consistent (the data will always be in a good state—no inconsistent states), Isolated (transactions don’t interfere), and Durable (a committed transaction is safe, even after a server crash). Transactions are useful when you’re modifying two tables that you don’t want out of sync.

We can wrap any transaction within a BEGIN TRANSACTION block. To verify atomicity, we’ll kill the transaction with the ROLLBACK command.

Stored Procedures

Every command we’ve seen until now has been declarative (executed on the client side), but you can execute code on the database side also. Example of a stored procedure and how to run it:

CREATE OR REPLACE FUNCTION add_event( title text, starts timestamp,
ends timestamp, venue text, postal varchar(9), country char(2) )
RETURNS boolean AS $$
DECLARE
did_insert boolean := false;
found_count integer;
the_venue_id integer;
BEGIN
SELECT venue_id INTO the_venue_id
FROM venues v
WHERE v.postal_code=postal AND v.country_code=country AND v.name ILIKE venue
LIMIT 1;
IF the_venue_id IS NULL THEN
INSERT INTO venues (name, postal_code, country_code)
VALUES (venue, postal, country)
RETURNING venue_id INTO the_venue_id;
did_insert := true;
END IF;
-- Note: not an “error”, as in some programming languages
RAISE NOTICE 'Venue found %', the_venue_id;
INSERT INTO events (title, starts, ends, venue_id)
VALUES (title, starts, ends, the_venue_id);
RETURN did_insert;
END;
$$ LANGUAGE plpgsql;

SELECT add_event('House Party', '2012-05-03 23:00',
'2012-05-04 02:00', 'Run''s House', '97205', 'us');

Triggers

Triggers automatically fire stored procedures when some event happens, like an insert or update. They allow the database to enforce some required behavior in response to changing data.

Let's create a function that logs any event changes into logs table. First we create the table

CREATE TABLE logs (
event_id integer,
old_title varchar(255),
old_starts timestamp,
old_ends timestamp,
logged_at timestamp DEFAULT current_timestamp
);

Next, we build a function to insert old data into the log. The OLD variable represents the row about to be changed (NEW represents an incoming row, which we’ll see in action soon enough). Output a notice to the console with the event_id before returning. Then we create our trigger to log changes after any row is updated through this function.

CREATE OR REPLACE FUNCTION log_event() RETURNS trigger AS $$
DECLARE
BEGIN
INSERT INTO logs (event_id, old_title, old_starts, old_ends)
VALUES (OLD.event_id, OLD.title, OLD.starts, OLD.ends);
RAISE NOTICE 'Someone just changed event #%', OLD.event_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_events
AFTER UPDATE ON events
FOR EACH ROW EXECUTE PROCEDURE log_event();

To test our trigger let's update an event and see the trigger output:

and the old event was logged

Views

Views are aliased queries that can be queried by its alias like any other table. Creating a view is as simple as writing a query and prefixing it with CREATE VIEW some_view_name AS. Views are good for opening up complex queried data in a simple way. If you want to add a new column to the view, it will have to come from the underlying table.

CREATE VIEW holidays AS
SELECT event_id AS holiday_id, title AS name, starts AS date
FROM events
WHERE title LIKE '%Day%' AND venue_id IS NULL;

SELECT name, to_char(date, 'Month DD, YYYY') AS date
FROM holidays
WHERE date <= '2012-04-01';

The RULE system

The rule system (more precisely speaking, the query rewrite rule system) is totally different from stored procedures and triggers. It modifies queries to take rules into consideration, and then passes the modified query to the query planner for planning and execution. More information can be found here. Example of changing query to allow updating our holidays view:

CREATE RULE update_holidays AS ON UPDATE TO holidays DO INSTEAD
UPDATE events
SET title = NEW.name,
starts = NEW.date
WHERE title = OLD.name;

UPDATE holidays SET date = '6/25/2013' where name = 'Christmas Day';

Crosstab

crosstab(text source_sql, text category_sql) takes two queries to pivot results on one of them on the results of the other. 

• source_sql is a SQL statement that produces the source set of data. This statement must return one row_name column, one category column, and one value column. It may also have one or more "extra" columns. The row_name column must be first. The category and value columns must be the last two columns, in that order.
  
• category_sql is a SQL statement that produces the set of categories. This statement must return only one column. It must produce at least one row, or an error will be generated. Also, it must not produce duplicate values, or an error will be generated.

If this is the first time you use something from the tablefunc module, you may need to install it to your database by running CREATE EXTENSION tablefunc;

create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);

select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);

Full-Text Search

Full-text search enables the user to search text columns with parts of the column value, not the exact value like in regular queries.

• LIKE and ILIKE : LIKE and ILIKE (case-insensitive LIKE) are the simplest forms of text search. LIKE compares column values against a given pattern string. The % and _ characters are wildcards. % matches any number of any characters, and _ matches exactly one character.
  
  
  
  • SELECT title FROM movies WHERE title ILIKE 'stardust%';
    
    
    
    • will return movies with titles like : “Stardust” or “Stardust Memories”
  
  
• Regex : You could write the WHERE part of your queries against text columns in regular expressions. A regular expression match is led by the ~ operator, with the
  optional ! (meaning, not matching) and * (meaning case insensitive). So, to count all movies that do not begin with the, the following case-insensitive query will work. The characters inside the string are the regular expression.
  
  
  
  • SELECT COUNT(*) FROM movies WHERE title !~* '^the.*';
  
  
• Bride of Levenshtein : Levenshtein is a string comparison algorithm that compares how similar two strings are by how many steps are required to change one string into another. Each replaced, missing, or added character counts as a step (Changes in case cost a point too). The distance is the total number of steps away. levenshtein() function is provided by the fuzzystrmatch contrib package.The following query return 3 because we need to replace 2 characters and and add 1 character to make bat into fads.
  
  
  
  • SELECT levenshtein('bat', 'fads');
  
  
• Trigram :  A trigram is a group of three consecutive characters taken from a string. The pg_trgm contrib module breaks a string into as many trigrams as it can. Finding a matching string is as simple as counting the number of matching trigrams. The strings with the most matches are the most similar. It’s useful for doing a search where you’re OK with either slight misspellings or even minor words missing. The longer the string, the more trigrams and the more likely a match.
  
  
  
  • SELECT show_trgm('Avatar');  -- returns {" a"," av","ar ",ata,ava,tar,vat}
  
  
• PostgreSQL have more natural language processing capabilities (TSVector and TSQuery) and even can search by word phonetics (metaphone)
  
• You could combine text search function in many interesting ways like the following query that “Get me names that sound the most like Robi n Williams, in order.”
  
  
  
  • SELECT * FROM actors
    WHERE metaphone(name,8) % metaphone('Robin Williams',8)
    ORDER BY levenshtein(lower('Robin Williams'), lower(name));

 

In this post we introduced PostgreSQL, a popular open source RDBMS. In future posts we will introduce more DBMSs.

Parameterized Test Patterns using Microsoft Pex

We talked before about the difference between unit tests and parameterized unit tests. In this post we will talk about common patterns for writing good parameterized unit tests. Keep in mind that we will use these tests with Microsoft Pex (as an automatic test input generation tool) to get test inputs that trigger all the possible scenarios of the code-under-test.

Before anything, let’s clarify what are the questions we want to answer using the parameterized unit tests. There are  two core questions:

• What are good scenarios (sequences of method calls) to exercise the code-under-test? (Coverage)
• What are good assertions that can be stated easily without re-implementing the algorithm? (Verification)

A parameterized unit test is only useful if it provides answers for both questions:

• Without sufficient coverage, i.e. if the scenario is too narrow to reach all the code-under-test, the extent of the test is limited.
• Without sufficient verification of the computed results, i.e. if the test does not contain enough assertions, the test does not check that the code is doing the right thing. All it would check for is that the code-under-test does not crash.

Test Patterns

1- Arrange, Act, Assert

The ’AAA’ (Triple-A) is a well-known pattern for writing unit tests. It applies to parameterized unit tests as well. A parameterized unit test using this patter is organized in three sections:

• Arrange: set up the unit under test
• Act: exercise the unit under test, capturing any resulting state
• Assert: verify the behavior through assertions

An example of this pattern in a traditional unit test:

[Test]
pubic void AddItem()
{
      // arrange
      var list = new ArrayList();
      var item = new object();
      // act
      list.Add(item);
      // assert
      Assert.IsTrue(list.Count == 1);
}

An example of this pattern in a parameterized unit test:

[PexMethod]
pubic void AddItem(object item)
{
      // arrange
      var list = new ArrayList();
      // act
      list.Add(item);
      // assert
      Assert.IsTrue(list.Count == 1);
}

2- Assume, Arrange, Act, Assert

This pattern is an extension of the first pattern where an Assumption section is added at the beginning. An assumption restricts possible test inputs, acting as a filter. A parameterized unit test using this pattern is organized in four sections:

• Assume: assume preconditions over the test inputs


• Arrange: set up the unit under test


• Act: exercise the unit under test, capturing any resulting state


• Assert: verify the behavior through assertions

The following example tests that adding an element to any list increments the Count property. We use an assumption to filter out the case where list is a null reference.

[PexMethod]
void AssumeActAssert(ArrayList list, object item)
{
     // assume
     PexAssume.IsNotNull(list);
     // arrange
     var count = list.Count;
     // act
     list.Add(item);
     // assert
     Assert.IsTrue(list.Count == count + 1);
}

3- Parameterized Stubs

If the code-under-test already contains many assertion statements that verify its behavior, an effective parameterized unit test might be quite simple in itself, because it can leverage the assertions in the code.

[PexMethod] 
public void Add( [PexAssumeUnderTest]ArrayList list, object item) 
{ 
 list.Add(item); 

The attribute PexAssumeUnderTest is a short-hand notation to make sure that the parameter is not null, and has exactly the type indicated by its declaration (not a subtype).

4- Observed Values

At any point in the parameterized unit test, if you want to log the value of any variable or parameter, just use

PexObserve.ValueForViewing("Variable Name", var_name);

5- Allowed Exceptions

Traditional unit test frameworks support the concept of expected exception, where a test case or API call is expected to throw an exception. If the test does not throw the exception or throws an exception that does not match the criteria, the execution fails. The same concept applied to parameterized unit tests.

[PexMethod][PexAllowedException(typeof(ArgumentNullException))] 
void Constructor(string value) 
{ 
 // throws ArgumentNullException if value is null 
 var myClass = new MyClass(value); 
}

6- State Relation

This pattern applies when an API call causes an internal state change that can be (partially) observed through other API calls. A classic example of such a pattern is the combination of Insert and Contains operation on any collection type:

[PexMethod] 
void InsertContains(stringvalue) 
{ 
 var list = new List(); 
 list.Add(value); 
 Assert.IsTrue(list.Contains(value)); 
}

7- Roundtrip

This pattern applies to an API that transforms its inputs in a reversible way: When the API has a function f and an inverse function f_1, then it should hold that f_1(f(x))=x for all x. A classic example of such pattern is property setters and getters, where the test fails when the setter rejects a particular argument value.

[PexMethod] 
void PropertyRoundtrip(string value) 
{ 
 // arrange 
 var target = new MyClass(); 
 
 // two-way roundtrip 
 target.Name = value;   // calls setter 
 var roundtripped = target.Name; // calls getter 

 // assert 
 Assert.AreEqual(value, roundtripped); 
}

Another example is serialization and deserialization of values.

[PexMethod] 
void ToStringParseRoundtrip(int value) 
{ 
 // two-way roundtrip 
 string s = value.ToString(); 
 int parsed = int.Parse(s); 

 // assert Assert.AreEqual(value, parsed); 
}

8- Normalized Roundtrip

The Pattern Roundtrip showed how to test a method for which in inverse operation exists. For example, int.Parse is the inverse of int.ToString. This is not the case in the other direction: int.ToString is not exactly the inverse of int.Parse, because the parsing ignores whitespace (some kind of data normalization):

int.Parse(" 5").ToString() == "5";

This pattern is applied when the API has a function f and an inverse function f_1, then it should hold that f_1(f(f_1(x)))=f_1(x) for all x where f_1(x) is defined.

[PexMethod] 
void ThreeWayRoundtrip(string value) 
{ 
 // ’hello%20world’ <= ’hello world’ 
 var normalized = Uri.EscapeDataString(value); 
 
 // ’helloworld’ <= ’hello%20world’ 
 var intermediate = Uri.UnescapeDataString(normalized); 

 // ’hello%20world’ <= ’hello world’ 
 var roundtripped = Uri.EscapeDataString(intermediate); 

 // assert 
 Assert.AreEqual(normalized, roundtripped); 
}

9- Reachability

If your test assumptions are so complicated and it is not clear whether there is any test input that fulfills them. You could use PexAssert.ReachEventually("GoalName") wherever in your test to make sure that there is at least one input that reach this point in your test. The goal name have to be passed to the PexAssertReachEventuallyAttribute constructor. A parameterized unit test fails when Pex does not find a way to reach a goal, indicated by calling the PexAssert.Reached method in a parameterized unit test annotated with the PexAssertReachEventuallyAttribute.

[PexMethod] 
[PexAssertReachEventually("passed", StopWhenAllReached = true)] 
public void ParsingSuccesful(string input) 
{ 
 // complicated parsing code 
 DateTime date; 
 if (DateTime.TryParse(input, out date)) 
 { 
  // and we want to see at least one case where parsing is successful. 
  PexAssert.ReachEventually("passed"); 
 } 
}

Multiple goals can be combined in a single parameterized unit test for more advanced scenarios by passing a list of goal identifiers in the constructor of the PexAssertReachEventuallyAttribute. Each goal identifier must be reached and notified in order for the parameterized unit test to succeed.

[PexMethod] 
[PexAssertReachEventually("parsed", "y2k", StopWhenAllReached = true)] 
public void ParsingSuccesfulWithMoreGoals(string input) 
{ 
 // complicated parsing code 
 DateTime date;
 if (DateTime.TryParse(input, out date)) 
 { 
  // and we want to see at least one case where parsing is successful. 
  PexAssert.ReachEventually("parsed"); 
 } 
 if (date.Year == 2000) 
 {
  // we want to see at least one test with the year 2000 
  PexAssert.ReachEventually("y2k"); 
 }
}

10- Reachable Implication

Implications assert a property when a predicate (condition) is true. Use PexAssert.ImpliesEventually to make an implication that should be true and that should be executed at least once. The test have to be annotated with the PexAssertReachEventuallyAttribute. A parameterized unit test fails when Pex does not find a way to make the predicate of PexAssert.ImpliesEventually evaluates true at least once.

[PexMethod] 
[PexAssertReachEventually] 
public void ParseImpliesParse(string input) 
{ 
 bool value; 
 // if TryParse succeeds, Parse should succeed too. Also make sure TryParse succeeds at least once 
 PexAssert.ImpliesEventually( bool.TryParse(input, out value), () => bool.Parse(input) );
}

11- Seed Values to Help Pex

A parameterized unit test needs concrete input data to be executed. Pex's role is to automatically generate relevant input data via code analysis. Sometimes it might be desirable or even necessary to provide manually chosen seed values to Pex to guide the automated code exploration. In effect, Pex will fuzz the provided values in ways that cause alternative execution paths to be taken. The PexArgumentsAttribute can be used to provide primitive data. Each instance of this attribute gives a list of values that must match the parameter types of the parameterized unit test (and in the same parameters order).

[PexMethod]
[PexArguments("var i = 0;", 0)]
[PexArguments("class Foo {}", 12)]
public void ParseTest(string text, int line)
{
 var parser = new Parser();
 parser.SetLine(line);
 var node = parser.Parse(text);

}

Before analyzing the branch conditions in the code, Pex will first execute the parameterized unit test with the provided values. This way, Pex acquires knowledge about the code reachable from a test, and during the subsequence code exploration Pex will try to further increase code coverage by slightly modifying the values to trigger different execution paths. In effect, Pex will fuzz the provided values.

12- Regression Tests

In these tests some we could persist a computed value in the generated test so when the generated test is executed in the future, it verifies that the (possible changed) code-under-test still computes the same value. There are several ways how outputs can be logged. For a single output value, one can use the return value of the parameterized unit test:

[PexMethod]
public int Add(int a, int b)
{
 return a + b;
}

Pex will recursively traverse the observable properties and fields of the value and add assertions in the generated test for each one of them:

[Test]
[PexGeneratedBy(typeof(Program))]
public void Add866()
{
    int i;
    i = this.Add(0, 0);
    PexAssert.AreEqual(0, i);
}

For multiple values, use out parameters in the parameterized unit tests

[PexMethod]
public void Add(int a, int b, out int result)
{
 result = a + b;
}

Which Pex will use to generate the following

[Test]
[PexGeneratedBy(typeof(Program))]
public void Add13()
{
    int i = 0;
    this.Add(0, 0, out i);
    PexAssert.AreEqual(0, i);
}

If the number of values might be dynamic, you could log these values using PexObserve.Value and observe it later using the PexObserve.ValueAtEndOfTest method:

[PexMethod]
void Add(int a, int b)
{
 int result = a * b;
 PexObserve.Value("result", result);
}

Microsoft Pex: Understanding Assumptions, Assertions, and Test-Case Failures

In a previous post we started using Microsoft Pex and showed how it helped exploring all possible code paths, and how that helped discovering a defect in our program logic. In that example, even our program logic is defective, all test cases succeeded. A test case fails if there is an un-caught exception or a failed assertion.

To see an example of a failed test case and how Pex could help in fixing it, let’s add the following basic function to our code:

public void Add(ArrayList a, object o)
        {
            a.Add(o);
        }

when you run Pex for that simple method, you get the following results:

As you might expected, the ArrayList object might be NULL. In situations similar to this one, when a higher-level component (the code that called Add() method) passes malformed data to a lower-level component(that Add() method), which the lower-level component rejects, then the higher-level component should be prevented from doing so in the first place.

One way to achieve this is to promote the failure condition of the lower-level component to the abstraction level of the higher-level component. This is what the Add Precondition feature of Pex does: It expresses a low-level failure condition at the abstraction level of the code under test. When added to the code-under-test as argument validation code—also called a precondition—then this effectively prevents the code under test from causing a failure somewhere in its execution. In other words, the Add Precondition feature doesn’t completely fix an error, but it promotes the failure condition to a higher, more appropriate abstraction level.

To add a precondition to your code, click the failed test case row, then on the details section (right) click Add Precondition, as in picture below

Pex will open Preview and Apply updates dialog box to show you the proposed code modifications that Pex will do.

Review and Click Apply. The modified method will look like:

        
public void Add(ArrayList a, object o)
        {
            // 
            Debug.Assert(a != (ArrayList)null, "a");
            // 
            a.Add(o);
        }

When you run Pex for our method now, we will not find a failed test case.

The concept of assertions is well known in unit test frameworks. Pex understands the built-in Assert classes provided by each supported test framework. However, most frameworks do not provide a corresponding Assume class as Pex did. PexAssume is a class to express assumptions, i.e. a precondition. The methods of this class can be used to filter out undesirable test inputs. If you do not want to use an existing test framework, Pex also has the PexAssert class. Some functionalities can be achieved using attributes instead of PexAssume methods, like PexAssumeNotNullAttribute and PexAssumeUnderTestAttribute.

 
[PexMethod]
public void Test1(object o)  //precondition: o should not be null
{
 PexAssume.IsNotNull(o);
 ...
}

[PexMethod]
public void Test2([PexAssumeNotNull]object o) //precondition: o should not be null
{ 
 ...
}

When you write an assertion, Pex will not only passively detect assertion violations, but Pex will in fact actively try to compute test inputs that will cause the assertion to fail (just as an assertion might throw a PexAssertFailedException). Pes uses these exceptions internally to stop a test case when an assumption fails.

Expected Exceptions

You can annotate the test—or the test class, or the test assembly—with one of the following attributes to indicate which exception types can or must be thrown by the test in order to be considered successful:

• PexAllowedExceptionAttribute indicates that a test method, or any other method that it calls directly or indirectly, can throw a particular type of exception for some test inputs.

  using System;
  using Microsoft.Pex.Framework;
  using Microsoft.Pex.Framework.Validation;
  
  namespace ConsoleApplication3
  {
      class Stack
      {
          int[] _elements;
          int _count;
          public Stack(int capacity)
          {
              if (capacity < 0) throw new ArgumentOutOfRangeException();
              _elements = new int[capacity];
              _count = 0;
          }
      }
  
      [PexClass]
      public partial class StackTest
      {
          [PexMethod]
          [PexAllowedException(typeof(ArgumentOutOfRangeException))]
          public void CtorTest(int capacity) // will not fail
          {
              Stack s = new Stack(capacity); // may throw ArgumentOutOfRangeException
          }
      }
  }
  

  
  
• PexAllowedExceptionFromTypeAttribute indicates that any method of a specified type can throw a particular type of exception for some test inputs.

  using System;
  using System.Collections;
  using Microsoft.Pex.Framework;
  using Microsoft.Pex.Framework.Validation;
  
  namespace ConsoleApplication3
  {
      [PexClass]
      public partial class ArrayListTest
      {
          [PexMethod]
          [PexAllowedExceptionFromType(typeof(NullReferenceException), typeof(ArrayListTest))]
          public void Add1(ArrayList a, object o) //will not fail
          {
              a.Add(o);
          }
      }
  }
  

  
  
• PexAllowedExceptionFromTypeUnderTestAttribute indicates that any method of the designated type under test can throw a particular type of exception for some test inputs.
  
• PexAllowedExceptionFromAssemblyAttribute indicates that any method located in a specified assembly can throw a particular type of exception for some test inputs.

When Does Pex Emit a Test Case?

Pex supports different filters that decide when generated test inputs will be emitted as a test case. You can configure these filters with the TestEmissionFilter property that you can set for example in the PexMethod attribute. Possible values are the following:

• All Emit every generated test input as a test case, including those that cause assumption violations.
  
• FailuresAndIncreasedBranchHits (default) Emit tests for all unique failures, and whenever a test case increases coverage, as controlled by the TestEmissionBranchHits property (take values 1 or 2).
  
• FailuresAndUniquePaths Emit tests for all failures Pex finds, and also for each test input that causes a unique execution path.
  
• Failures Emit tests for failures only.

Regarding increased branch coverage, the TestEmissionBranchHits property controls how a branch is covered. For example:

• TestEmissionBranchHits=1: Whether Pex should just consider whether a branch was covered at all. This gives a very small test suite that covers all branches Pex could reach. In particular, this test suite also covers all reached basic blocks and statements.
  
• TestEmissionBranchHits=2: Whether a test covered it either once or twice.

The default is TestEmissionBranchHits=2, which generates a more expressive test suite that is also better suited to detect future regression errors.

using System;
using Microsoft.Pex.Framework;
using Microsoft.Pex.Framework.Settings;

namespace ConsoleApplication3
{
    [PexClass]
    partial class TestEmission
    {
        int max(int x, int y)
        {
            if (x > y)
                return x;
            else
                return y;
        }

        [PexMethod(TestEmissionFilter=Microsoft.Pex.Framework.Settings.PexTestEmissionFilter.All)]
        public void MaxTest1(int a, int b, int c, int d)    // 1 test case generated
        {
            int e = max(a, b);
            PexObserve.ValueForViewing("max", e);
        }

        [PexMethod(TestEmissionFilter = Microsoft.Pex.Framework.Settings.PexTestEmissionFilter.Failures)]
        public void MaxTest2(int a, int b, int c, int d)    // No test cases generated 
        {
            int e = max(a, b);
            PexObserve.ValueForViewing("max", e);
        }
    }
}

When Does Pex Stop ?

If the code under test does not contain loops or unbounded recursion, Pex will typically stop quickly because there is only a (small) finite number of execution paths to analyze. However, most interesting programs contain loops and/or unbounded recursion. In such cases the number of execution paths is (practically) infinite, and it is in general undecidable whether a statement is reachable. In other words, Pex would take forever to analyze all execution paths of the program.

In order to make sure that Pex terminates after a reasonable amount of time, there are several exploration bounds. All bounds have predefined default values, which you can override to let Pex analyze more and longer execution paths. The bounds are parameters of the PexMethod, PexClass, and PexAssemblySettings attributes. There are different kinds of bounds:

• Constraint Solving Bounds: apply to each attempt of Pex to determine whether an execution path is feasible or not. Pex might need several constraint solving attempts to compute the next test inputs.
  
  
  
  • ConstraintSolverTimeOut Seconds the constraint solver has to figure out inputs that will cause a different execution path to be taken.
    
  • ConstraintSolverMemoryLimit Megabytes the constraint solver can use to figure out inputs.
  
  
• Exploration Path Bounds: apply to each execution path that Pex executes and monitors. These bounds make sure that the program does not get stuck in an infinite loop, or recursive method.
  
  
  
  • MaxBranches Maximum number of branches that can be taken along a single execution path.
    
  • MaxCalls Maximum number of calls that can be taken during a single execution path.
    
  • MaxStack Maximum size of the stack at any time during a single execution path, measured in number of active call frames.
    
  • MaxConditions Maximum number of conditions over the inputs that can be checked during a single execution path.
  
  
• Exploration Bounds apply to the exploration of each parameterized unit test.
  
  
  
  • MaxRuns Maximum number of runs that will be tried during an exploration (each run uses different test inputs; not every run will result in the emission of a new test case).
    
  • MaxRunsWithoutNewTests Maximum number of consecutive runs without a new test being emitted.
    
  • MaxRunsWithUniquePaths Maximum number of runs with unique execution paths that will be tried during an exploration.
    
  • MaxExceptions Maximum number of exceptions that can be found over all discovered execution paths combined.
    
  • MaxExecutionTreeNodes Maximum number of conditions over the inputs that can be checked during all discovered execution paths combined.
    
  • MaxWorkingSet Maximum size of working set in megabytes.
    
  • TimeOut Seconds after which exploration stops.

The following example shows a parameterized test that involves a loop. The loop bound depends on the test inputs, and the exception can only be triggered if the loop is executed a certain number of times. Here, we used an explicit bound of 10 runs MaxRuns=10 to let Pex finish quickly. However, with this bound, Pex will most likely not be able to trigger the exception, as Pex will not unroll the loop sufficiently many times:

 [PexMethod(MaxRuns = 10)]
        public void TestWithLoop(int n)
        {
            var sum = 0;
            for (int i = 0; i < n; i++)
                sum++;
            if (sum > 20) throw new Exception();
        } 
 

In the Pex Exploration Results you may see the Exception statement reached, but you will see on the Pex tool bar that there is 1 boundry reached. If you click on it, you will see more details:

on the right, you could click Set MaxRuns=20 to increase the MaxRuns boundry. You could do the same from the Pex tool bar and also setting boundary to infinity. Both actions will open a dialog to review and approve the code changes.

The following example shows another parameterized test that involves a loop, but this loop does not depend on the test inputs. Here, we used an explicit bound of 10 branches MaxBranches=10 to let Pex finish quickly. However, with this bound, Pex cannot even once execute the code from beginning to end, as executing the embedded loop will cause more than 10 branches to be executed.

 [PexMethod(MaxBranches = 10)]
        public void TestWithFixedLoop(int j)
        {
            var sum = 0;
            for (int i = 0; i < 15; i++)
                sum++;
            if (j == 10) throw new Exception();
        }
 

In those cases, where a particular run exceeded some path-specific bounds, we get a special row in the results. The Set MaxBranches= button on the results tool can be used to increase the bounds. If you increased the boundary to 20, you will get all you branched executed and the exception statement reached.

In this post we talked about how and when a test case fail, how to use precondition and assumptions to differentiate between failures from your unit code and failures from malformed input, how to allow specific exception to be raised from your tests without causing it to fail, how to control which test inputs should Pex use as a test case, and finally how to control the Pex stop criteria whether through  bounding the constraint solver, or bounding the exploration paths, or bounding the exploration runs.

Getting started with Microsoft Code Digger

Microsoft Code Digger is Visual Studio 2012 extension that have been released few days ago by RiSE team at Microsoft Research (the same team who developed Pex). You can download the it from the Visual Studio Gallery here.

Microsoft Code Digger uses the same engine that Pex uses, and the same techniques under the hood (dynamic symbolic execution and constraint solvers). The only constrain that Code Digger have is that it only works on public .NET code in Portable Class Libraries.

Let’s try it

After installing the Code Digger extension for Visual Studio 2012, create a Portable Class Library. Let’s use the Triang() method we used in previous posts as an example here. Your code should look like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace PortableClassLibrary1
{
    public class Class1
    {
        public static int Triang(int Side1, int Side2, int Side3)
        {
            int triOut;

            // triOut is output from the routine:
            //   Triang = 1 if triangle is scalene
            //   Triang = 2 if triangle is isosceles
            //   Triang = 3 if triangle is equilateral
            //   Triang = 4 if not a triangle

            // A quick confirmation that it's a valid triangle
            if (Side1 <= 0 || Side2 <= 0 || Side3 <= 0)
            {
                triOut = 4;
                return (triOut);
            }

            // Detect any sides of equal sides
            triOut = 0;
            if (Side1 == Side2)
                triOut = triOut + 1;
            if (Side1 == Side3)
                triOut = triOut + 2;
            if (Side2 == Side3)
                triOut = triOut + 3;
            if (triOut == 0)
            {
                if ((Side1 + Side2 <= Side3) || (Side2 + Side3 <= Side1) || (Side1 + Side3 <= Side2)) // confirm that it is a valid triangle 
                    triOut = 4;
                else
                    triOut = 1;
                return (triOut);
            }

            if (triOut > 3)
                triOut = 3;
            else if ((triOut == 1) && (Side1 + Side2 > Side3))
                triOut = 2;
            else if ((triOut == 2) && (Side1 + Side3 > Side2))
                triOut = 2;
            else if ((triOut == 3) && (Side2 + Side3 > Side1))
                triOut = 2;
            else
                triOut = 4;
            return (triOut);
        }
    }
}

Right click inside Triang() method and wait for few seconds. You will see the below Inputs / Outputs pane.

 

It’s the same set we got before from Pex and for the same reasons. No surprises, both tools use the same engine under hood.

Why Pex Choose These Inputs

In the example we gave in the previous post, it may seem that Pex chose random numbers as inputs for the Triang() method but it is not. But also its not all possible values for the inputs.

Actually, Pex generates test inputs by analyzing your program code, so it is called whitebox test generation (as opposed to blackbox test generation). For every statement in the code, Pex will eventually try to create a test input that will reach that statement. Pex will do a case analysis for every conditional branch in the code—for example, if statements, assertions, and all operations that can throw exceptions.

In other words, the number of test inputs that Pex generates depends on the number and possible combinations of conditional branches in the code (if interested to know more about that, search for symbolic execution). Pex operates in a feedback loop: it executes the code multiple times and learns about the program behavior by monitoring the control and data flow.

After each run, Pex does the following:

• Chooses a branch that was not covered previously.
• Builds a constraint system that describes how to reach that branch.
• Uses a constraint solver to determine new test inputs that fulfill the constraints, if any exist.

The test is executed again with the new inputs, and the process repeats. On each run, Pex might discover new code and dig deeper into the implementation. In this way, Pex explores the behavior of the code. 

Because our code doesn’t have any conditions that test zero length sides, Pex generated zero an input and also shows that our program is defective (because it considers a triangle with (0,0,0) as equilateral and (1,0,1) as isosceles). If we added the following lines of code after declaring triOut and before doing anything.

            
// A quick confirmation that it's a valid triangle            
if (Side1 <= 0 || Side2 <= 0 || Side3 <= 0)      
{
 triOut = 4;
 return (triOut);
}

You will get a different set of test inputs from Pex that reveal more code path combinations.

We mentioned before that pex generates test input by performing a symoblic analysis of the code under test. You can use the method GetPathConditionString of the PexSymbolicValue class to obtain a textual representation of the current path condition, a predicate (condition) that characterizes an execution path. The ToString method of PexSymbolicValue class gives you a textual representation of how a value was derived from the test input provided. To do so, add reference to Pex Framework dll (located at "C:\Program Files\Microsoft Moles\PublicAssemblies\Microsoft.Pex.Framework.dll"). Add using Microsoft.Pex.Framework; to your code then add the following code anywhere in your code to get details about the code path at that point.

 PexObserve.ValueForViewing("Condition", PexSymbolicValue.GetPathConditionString());
 PexObserve.ValueForViewing("Return Value", PexSymbolicValue.ToString(Triang(1, 1, 1)) );

Here I added it into the Triang() method we used before. Run Pex and you will see the new columns added to the results populated with conditions that led to .

ToRawString method and GetRawPathConditionString method return expressions representing symbolic values and the path condition, formatted as S-expressions.

Method PexObserve.ValueForViewing can also be used to display the value picked by Pex for any variable at any point in your code.

The same engine Pex uses is now available as part of Code Digger, a Visual Studio 2012 extension. We will talk about it in a future post.

Getting started with Microsoft Pex

Microsoft Pex is a white box test generation for .NET that came out of Microsoft Research and have been successfully integrated into Visual Studio 2010. It have been a result of collaborative work between Microsoft Research and the Automated Software Engineering Research Group at North Carolina State University led by  Dr. Tao Xie.

You can download and install Microsoft Pex for Visual Studio 2010 from here. We have talked in a previous post about parameterized unit tests and the possibilities it brings. In this post and the following we will explore Microsoft Pex and how it can help you in understanding the input/output behavior of your code, finding inputs that cause the code-under-test to crash, and exploring parameterized unit tests to check whether your code implements the desired functionality for all inputs.

Running Pex for the First Time

In Visual Studio click File > New > Project. In the left pane of the New Project dialog box, click Visual C#. In the center pane, click Console Application, pick a Name for it and click OK. Replace your class Program with the following class. It just the classic triangle classification program :

class Program
    {
        public static String[] triTypes = { "", // Ignore 0.
                                               "scalene", "isoscelese", "equilateral", "not a valid triangle"};
        public static String instructions = "This is the ancient TriType program.\nEnter three integers that represent the lengths of the sides of a triangle.\nThe triangle will be categorized as either scalene, isosceles, equilateral\n or invalid.\n";

        public static void Main()
        {
            int A, B, C;
            int T;

            Console.WriteLine(instructions);
            Console.WriteLine("Enter side 1: ");
            A = getN();
            Console.WriteLine("Enter side 2: ");
            B = getN();
            Console.WriteLine("Enter side 3: ");
            C = getN();
            T = Triang(A, B, C);

            Console.WriteLine("Result is: " + triTypes[T]);
            Console.ReadLine();
        }

        public static int Triang(int Side1, int Side2, int Side3)
        {
            int triOut;

            // triOut is output from the routine:
            //   Triang = 1 if triangle is scalene
            //   Triang = 2 if triangle is isosceles
            //   Triang = 3 if triangle is equilateral
            //   Triang = 4 if not a triangle

            // A quick confirmation that it's a valid triangle
            if (Side1 <= 0 || Side2 <= 0 || Side3 <= 0)
            {
                triOut = 4;
                return (triOut);
            }

            // Detect any sides of equal sides
            triOut = 0;
            if (Side1 == Side2)
                triOut = triOut + 1;
            if (Side1 == Side3)
                triOut = triOut + 2;
            if (Side2 == Side3)
                triOut = triOut + 3;
            if (triOut == 0)
            {
                if ((Side1 + Side2 <= Side3) || (Side2 + Side3 <= Side1) || (Side1 + Side3 <= Side2)) // confirm that it is a valid triangle 
                    triOut = 4;
                else
                    triOut = 1;
                return (triOut);
            }

            if (triOut > 3)
                triOut = 3;
            else if ((triOut == 1) && (Side1 + Side2 > Side3))
                triOut = 2;
            else if ((triOut == 2) && (Side1 + Side3 > Side2))
                triOut = 2;
            else if ((triOut == 3) && (Side2 + Side3 > Side1))
                triOut = 2;
            else
                triOut = 4;
            return (triOut);
        }

In the Build menu, click Build Solution.

To run Pex on your code, right-click in the body of the Triang method, and click Run Pex. If this is your first time running Pex, the Pex: Select a Test Framework dialog box appears. You could select your preferred test frame (Visual Studio Unit test, or NUnit)  and provide the installation path for its, then click OK. This dialog box will not appear again after you select the test framework. After a brief pause, Pex shows the results of its analysis in the Pex Exploration Results window. When you run Microsoft Pex on your .NET code, Pex generates test cases by analyzing the code-under-test. For every statement in the code, Pex will eventually try to create a test input that will reach that statement. Pex will do a case analysis for every conditional branch in the code—for example, if statements, assertions, and all operations that can throw exceptions. Each row in the table contains input/output values for the method under consideration(Traing). In the Pex Exploration Results window, click one row in the table on the left to see details in the right pane. You could select these rows and save them as unit tests. These details also displayed on the right in the Pex Explorer pane as test cases.

On the next post we will explain why Pex chose these values, and other Pex stuff :)