An abstract look at drawing with T-SQL
Editor's Note: Send your experts-only T-SQL tips to Itzik Ben-Gan at blackbelt@sqlmag.com. If we use your tip in the magazine, you'll receive $100 and an exclusive T-SQL Black Belt shirt.
In "Wake Up to T-SQL," April 2002, InstantDoc ID 24139, I showed how you can use T-SQL for the unusual purpose of drawing a smiley face. Last month's drawing was more or less realistic; this time, I look at how to use T-SQL for abstract drawing. Mike Labosh, a fellow Microsoft Certified Trainer (MCT), posted a very cool puzzle in a private SQL Server MCT newsgroup. I had a lot of fun working on it and found the output interesting. With Labosh's permission, I'm going to share his puzzle and my solution with you.
As always, I urge you to try to come up with your own solution. A tip: Use stored procedures and user-defined functions (UDFs) where appropriate to make the solution modular and easy to follow and maintain.
The September 1986 issue of Byte magazine featured a cool article titled "Abstract Mathematical Art," which described a cellular automation algorithm related to John H. Conway's "Game of Life." (The "game" Conway invented is actually an algorithm with a set of rules governing the birth, death, and survival of organisms through generations. This algorithm is commonly used in software-development demos.) Those were the days of line-numbered BASIC, and the algorithm immediately caught Labosh's attention. He now uses the algorithm as a way to test his skills whenever he wants to try a new language or push the limits on one that he knows.
The automation string is a series of 80 single-digit numeric values (so they fit nicely in a line of screen output). Each digit can be either 0, 1, 2, or 3. The automation string can be stored as an array of integers or as a string of single digits—varchar(80). The algorithm generates multiple automation strings, where the first string's state changes according to the algorithm's rules. Each digit of the first automation state must be randomly generated. For more aesthetically pleasing output, you can make sure that the first and last values in the string (or array) are always 0. Doing this produces "borders," keeps the math from changing all the string's digits on every generation, and keeps the output from looking like a mess.
Part of the algorithm is a replication rule that defines how each successive automation state will be generated. The replication rule contains 10 single-digit values, in which each digit is also either 0, 1, 2, or 3. The rule can also be stored as an array of integers or as a string of 10 digits such as char(10). You can design the rule arbitrarily, or you can use replication rules that are known to produce interesting graphical formations. For example, the replication rule "0102030201" generates particularly interesting output that's more visually appealing than some arbitrary replication rules you might choose.
To calculate the next automation state, the algorithm loops across the values in the 80-digit state, starting at the second digit and ending at the 79th digit. (Make sure the first and last values are set to 0.) The algorithm calculates the next state for any given value, say the nth digit position, by following these steps:
- It calculates the sum of n-1's value, n's value, and n+1's value, then adds 1.
- The algorithm uses that sum—I'll call it m—to point at the mth digit of the replication rule.
- Whatever value appears at that position of the rule is the next state value for the nth digit of the automation string.
Prev. page
[1]
2
3
4
next page 
