Archive for January, 2007

AiS 11: Data Transformation

Tuesday, January 30th, 2007

Listen Now

The interview with DBA Chris Simonton continues.

DTS
Data Transformation Services
http://en.wikipedia.org/wiki/Data_Transformation_Services
A tool found in previous versions of Microsoft SQL Server to extract, transform, and load data. Since replaced by SQL Server Integration Services.

E. F. Codd
http://en.wikipedia.org/wiki/E.F._Codd
Mathematician who invented the theory of relational databases.

Composability in Operating Systems

Friday, January 26th, 2007

I just finished watching a fascinating video on Channel 9. Well, fascinating to a language geek like me, anyway. It's Anders Hejlsberg, Herb Sutter, Erik Meijer, Brian Beckman: Software Composability and the Future of Languages. These are the people who make Microsoft development platforms as good as they are. In fact, I am not alone in crediting Anders Hejlsberg with the success of .NET.

These industry experts discussed the concept of composability as it applies to language design. Composability is simply the ability to build useful systems out of smaller pieces. In language design, this is manifested as the ability to build expressions from smaller expressions, or to pass functions as parameters to other functions. Structured languages such as Pascal are composable because you can pass the results of one function into another function. Functional languages such as Haskell are composable because you can build functions on-the-fly by combining operators to form lambda expressions.

It occurred to me that the concept is also of vital importance to an operating system. Composability was one of the key features of Unix that made it so successful. Before Unix was born, system operators had to create custom programs each time they had a new problem to solve. But Unix provided a mechanism for sysops to combine small utility programs to create solutions on-the-fly. By giving each program an input and output stream, and by allowing the output of one to be piped to the input of another, Unix made it possible for the user to write a command to solve a problem that no one had ever though of before.

Up till now, Windows has been lagging behind Unix in the area of composability. While DOS supports streams, Microsoft has not provided the toolset necessary to compose useful solutions. Windows failed to add significantly to the DOS legacy of composability, providing us only with complex media such as DDE and COM. ActiveX was supposed to herald a new day of component-based development, but the success has mostly eluded us.

But now Microsoft is finally fielding a strong contender for the composability crown. Windows Power Shell does Unix one better by replacing streams with objects as the medium of composability. No longer do you have to grep, sed, and awk your text to extract and combine properties. Now you can work with your objects in their native type. Microsoft provides commandlets with which you can easily manage files, processes, and other OS constructs. And you can write your own commandlets in .NET to manage your own application objects.

Up till now, I've tried to create useful management consoles for any production product I've worked on. But I can never anticipate all of the functionality that an operations engineer will need. So in future projects targeting Windows, I will expose management objects through commandlets. Then any features that I haven't thought of can be easily composed from the pieces available.

AiS 10: Database Administration

Tuesday, January 23rd, 2007

Listen Now

An interview with Chris Simonton, Database Administrator at Handmark. Strategies for database normalization, separation, and replication.

Anonymous user in MySQL

Friday, January 19th, 2007

We rolled out a new release of our server yesterday, and ran into an unusual problem. We still don't know the root cause of it (probably a MySQL bug), but we do know what the symptoms and workarrounds are.

The symptom is the following error message:

execute command denied to user ''@'' for routine 'mydatabase.myproc'

The interesting part of this error message is the user with no name and no host. We are logged in as an application user. A search for this user reveals that it does indeed exist, although it was not present on the development box.

mysql> select count(1) from mysql.user where user='' and host='';
+----------+
| count(1) |
+----------+
|        1 |
+----------+
1 row in set (0.03 sec)

We use replication to separate writes from reads. This user appears on both the write and read servers, but this error only occurs on reads.

Investigating further, we tried to learn more about this mystery user. We entered the following command:

mysql> show grants for ''@'';

PLEASE DO NOT EXECUTE THIS COMMAND. This brought the server down. We didn't believe that it was the cause, so we tried again on another server. After restarting both servers, we continued our investigation.

We have no idea where this user came from or what might depend upon it. We also have no idea why this user is used to authenticate this proc instead of the app user. This being the production server, we didn't do anything as drastic as dropping the user without proper research. So we focused on this procedure in particular.

Here's my solution
The fix that we eventually found is two-fold. First, identify the user by password in the grant clause after creating the procedure. Second, explicitly drop and recreate the procedure on each of the read servers. Replication of the create script had some part in causing the problem. So our script ended up looking like this:

USE mydatabase
DROP PROCEDURE IF EXISTS myproc
DELIMITER $$
CREATE PROCEDURE myproc()
BEGIN
    SELECT 1;
END$$
DELIMITER ;
GRANT EXECUTE ON PROCEDURE myproc TO appuser IDENTIFIED BY 'apppassword';

Run this script on the write server, wait for it to replicate to the read servers, then run this script on each read server individually. I don't know why, but it works.

AiS 9: Advanced Unit Testing Techniques

Monday, January 15th, 2007

Listen Now

Object Oriented Software Construction
http://archive.eiffel.com/doc/oosc/
A defnition and study of Contract-Based Programming.

Continuous Integration
http://en.wikipedia.org/wiki/Continuous_integration
The process of using automated nightly builds to discover integration problems early.

Hanselminutes
http://hanselminutes.com/
Scott Hanselman and Carl Franklin talk about all aspects of programming.

Depedency Injection
http://en.wikipedia.org/wiki/Dependency_injection
A pattern for depending upon abstractions rather than concrete classes.

Flow Analysis
http://en.wikipedia.org/wiki/Flow_analysis
A technique used in compiled languages to analyze the flow of logic through the source code.

NMock2
http://sourceforge.net/projects/nmock/
A mock-object framework for .NET. Creates test harnesses that let you set and validate expectations.

EasyMock
http://www.easymock.org/
A mock-object framework for Java. Scott Hanselman called this a "TiVo" for unit tests. Record your expectations, then replay them within the unit test. EasyMock works with intellisense, whereas many other frameworks do not.

Team System
http://msdn.microsoft.com/vstudio/teamsystem/
Microsoft's all-encompasing development engine. It includes source control, defect tracking, project management, and testing, among other features. Its unit testing framework is relatively poor when compared to NUnit, but it integrates into the build system.

ISerializable - MbUnit vs. NUnit vs. Team System
http://weblogs.asp.net/rosherove/archive/2006/09/24/MbUnit-vs.-NUnit-Vs.-Team-System-Unit-Testing-_2D00_-Choosing-a-unit-test-framework.aspx
A comparison of unit test frameworks for .NET.

AiS 8: Unit Testing

Wednesday, January 10th, 2007

Listen Now

.NET Rocks!
http://dotnetrocks.com/
Weekly podcast with Carl Franklin and Richard Campbell.

DNR TV
http://dnrtv.com/
Video companion to .NET Rocks!

JUnit
http://www.junit.org/
The most popular unit testing framework for Java.
"Never in the field of software development was so much owed by so many to so few lines of code" Martin Fowler

NUnit
http://www.nunit.org/
Originally a port of JUnit to .NET, NUnit now takes full advantage of the platform, including attributes.

Paging in a MySQL Stored Procedure

Friday, January 5th, 2007

At Handmark, we make on-line services for both cell phones and web browsers. A common need on both platforms is to return search results to the client a page  at a time. The way to accomplish this in MySQL is to use the LIMIT ... OFFSET clause. Unfortunately, this doesn't work so well in a stored procedure.

The parameters of LIMIT ... OFFSET are the number of rows to return, and the number of rows to skip. So if you want to display 25 rows at a time you end your SELECT statement with LIMIT 25 OFFSET 0. When the user clicks the "next" button, you use LIMIT 25 OFFSET 25.

Mobile devices come in many different sizes and form factors. A Windows Mobile Pocket PC has a larger screen than a Blackberry, for example, and can therefore display more results per page without scrolling. We adjust the number of results based on the device type to optimize the user experience.

The combination of these concerns means that the parameters to the LIMIT ... OFFSET clause must be soft. We cannot hardcode them into our stored procedures, nor can we afford to maintain several different procedures with different hardcoded values. But it would seem that the designers of MySQL expect us to do just that, since you cannot use procedure parameters with this clause.

Here's my solution
There are several quirks that I discovered on the road to getting this working correctly. Some of them I am convinced are simply bugs, and will probably be fixed soon. But the pattern that I found to work is to prepare a statement, then call it using user-defined "@" variables. The actual stored procedure parameters won't work.

DELIMITER $$

CREATE PROCEDURE call_log_by_user_enum_v1(
    IN i_user_id INT,
    IN i_limit INT,
    IN i_offset INT,
    OUT o_count INT )
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET o_count = (SELECT COUNT(1) FROM call_log WHERE user_id = i_user_id);

    PREPARE ps FROM 'SELECT call_date, command
        FROM call_log
        WHERE user_id = ?
        LIMIT ? OFFSET ?';

    SET @v_user_id = i_user_id;
    SET @v_limit = i_limit;
    SET @v_offset = i_offset;
    EXECUTE ps USING @v_user_id, @v_limit, @v_offset;
    DROP PREPARE ps;
END$$

DELIMITER ;

A recent release has thankfully allowed the limit clause in a prepared statement to accept "?" placeholders. Prior to that release, you had to concatenate the SQL string in a variable (an "@" variable, not a DECLAREd variable). That's one small step toward the right solution, but there are still a couple more to take.

AiS 7: Techniques for Quality Improvement

Wednesday, January 3rd, 2007

Listen Now
User interaction design
Unit testing
Automated testing
Code generation
Contract-based design
Use-case analysis
Litterate programming