Archive for January, 2007AiS 11: Data TransformationTuesday, January 30th, 2007The interview with DBA Chris Simonton continues. DTS E. F. Codd Composability in Operating SystemsFriday, January 26th, 2007I 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 AdministrationTuesday, January 23rd, 2007An interview with Chris Simonton, Database Administrator at Handmark. Strategies for database normalization, separation, and replication. Anonymous user in MySQLFriday, January 19th, 2007We 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 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 TechniquesMonday, January 15th, 2007Object Oriented Software Construction Continuous Integration Hanselminutes Depedency Injection Flow Analysis NMock2 EasyMock Team System ISerializable - MbUnit vs. NUnit vs. Team System AiS 8: Unit TestingWednesday, January 10th, 2007.NET Rocks! DNR TV JUnit NUnit Paging in a MySQL Stored ProcedureFriday, January 5th, 2007At 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 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 ImprovementWednesday, January 3rd, 2007Listen Now |