Yet Another Web Development Blog

Google Password Generator Gadget

Today I needed a short break and decided to port over a Javascript password generator that I had previouly written to the XML format used by Google for their homepage “gadgets” (aka “widget”). I use it primarily for creating database passwords and figured at least a few of the people out there could find their own use for it.

(If you use Google’s personalized homepage, click on the blue plus sign to add it to your page.)

8 comments January 30th, 2007

Who Killed the Webmaster?

Back in the frontier days of the web–when flaming skulls, scrolling marquees, and rainbow divider lines dominated the landscape–”Webmaster” was a vaunted, almost mythical, title. The Webmaster was a techno-shaman versed the black arts needed to make words and images appear on this new-fangled Information Superhighway. With the rise of the Webmaster coinciding with the explosive growth of the web, everyone predicted the birth of a new, well paying, and in-demand profession. Yet in 2007, this person has somehow vanished; even the term is scarcely mentioned. What happened? A decade later I’m left wondering “Who killed the Webmaster?”

Suspect #1: The march of technology

By 2000, I think every person in the developed world had a brother-in-law who created websites on the side. Armed with Frontpage and a pirated copy of Photoshop, he’d charge a reasonable fee per page (though posting more than three images cost extra.)

Eventually the web hit equilibrium and just having a website didn’t make a company hip and cutting-edge. Now management demanded that their website look better than the site immediately ranked above in search results. And as expensive as the sites were, ought they not “do something” too? Companies increasingly wanted an exceptional website requiring a sophisticated combination of talent to pull off. HTML and FTP skills, as useful as they had been, were no longer a sharp enough tool in the Webmaster’s toolbox. Technologies such as CSS and multi-tier web application development rapidly made WYSIWYG editors useless for all but ordinary websites. And with the explosion of competition and possibilities on the Internet few businesses were willing to pay for “ordinary”.

In 1995, the “professional web design firm” was single, talented person working from home. Today it’s a diverse team of back-end developers, front-end developers, graphic artists, UI designers, database and systems administrators, search engine marketing experts, analytics specialists, copywriters, editors, and project managers. The industry has simply grown so specialized, so quickly, for one person to hardly be a master of anything more than a single strand in the web.

Suspect #2: Is it the economy, stupid?

Then again, perhaps the disappearance of the Webmaster can better be explained by an underwhelming economy rather than overwhelming technology. Riding high on the bull market of the late 90’s, companies were increasingly willing to assume more risk to reach potential customers. This was especially true of small businesses, which traditionally have miniscule advertising and marketing budgets. Everyone wanted a piece of the Internet pie and each turned to the Webmaster to deliver. More than just a few Webmasters made a respectable living by cranking out a couple $500 websites every week.

Once the bubble burst in early 2000, the dot-com hangover left many small businesses clutching their heads and checking their wallets. As companies braced to solely maintain what they already had, the first cut inevitably was to marketing and advertising. In-house Webmasters were summarily let go, their duties hastily transferred to an already overworked office manager. Freelance Webmasters were hit even harder as business owners struggled to first take care of their own. The gold rush had crumbled to fools’ gold even faster than it had started.

While a few Webmaster were able to weather the storm—mostly those with either extraordinary skills or a gainfully employed spouse—the majority were forced to abandon their budding profession and return to the world of the mundane.

Suspect #3: The rise of Web 2.0

Another strong possibility is that the Internet has simply evolved beyond the Webmaster. “Web 2.0″ is the naked emperor of technological neologisms; we all nod our head at the term but then stammer when pressed for a definition. As far as I can tell, Web 2.0 is mostly about rounded corners, low-contrast pastel colors, and domain names with missing vowels. But it also seems to be about an emphasis on social collaboration. This may seem like a no-brainer given the connectedness of the Internet itself; however, thinking back to Web 1.0 there was a distinct lack of this philosophy. Web 1.0 was more an arms race to build “mindshare” and “eyeballs” in order to make it to the top of the hill with the most venture capital. Even the Web 1.0 term of “portal” conjures up an image of Lewis Carroll’s Alice tumbling down a hole and into an experience wholly managed by the resident experts–the Webmasters. Despite the power and promises to be so much more, the web wasn’t much different than network television or print. Even the most interesting and successful business models of the Web 1.0 era could have been accomplished years prior with an automated telephone system.

It wasn’t until after the failure of the initial experiment did people begin to rethink the entire concept of the Internet. Was the Webmaster as gatekeeper really necessary? If we all have a story to share, why can’t everyone contribute to the collective experience? Perhaps it was the overabundance of Herman Miller chairs, but Web 1.0 was inarguably about style over substance. Yet, as anyone who’s ever visited MySpace can attest, today content is king. With all of us simultaneously contributing and consuming on blogs, MySpace, YouTube, Flickr, Digg, and SecondLife, who needs a Webmaster anymore?

120 comments January 28th, 2007

A Basic Introduction to Postgres Stored Procedures

This article introduces the very basics of writing stored procedures with PostgreSQL (aka Postgres), an open-source database system. It is intended for both Postgres newbies and MS SQL Server/MySQL stored procedure veterans who are interested learning more about Postgres.

Introduction

I originally come from the world of MS SQL Server where stored procedures (”sprocs”) were a fact of life. I routinely used/created them for use in classic ASP, and later ASP.Net, mainly just to move messy SQL statements out of the application and into the database. But besides making for cleaner code, sprocs are also handy because they:

• Reduce roundtrips across the network - Stored procedures take zero or more input parameters, do something, and return a result, much like a function in any language. By “black boxing” that functionality on the database server, we avoid the need to move large datasets across the wire to the application server for processing. This can decrease network utilization and application latency.
• Can make security easier to manage - While views can help simplify the permissions needed for complex queries, stored procedures make it even easier. Instead of giving a user/application the correct rights to specific tables (or columns), a person only needs execution rights to the stored procedure.
• Are precompiled - Stored procedures are stored in a precomplied state on the server, meaning that the query optimizer doesn’t have to recalculate the most efficient execution path each time the query is run. This reduces server overhead and can speed things up a bit.

Like pretty much everything in this industry though, there are almost as many opinions about “best practices” as there are developers. The most common objection to using stored procedures is that they off-load too much of the application’s job to the database. Some architects feel that a RDBMS ought to stick to just being a bit bucket and the logic that “does stuff” should be the realm of the application. I normally hear this line of reasoning from three-tier purists. They may have a valid point when planning enterprise-level apps that will have whole teams responsible for supporting different pieces of the puzzle. But for projects that aren’t as complex or ambitious, I feel that the benefits of stored procedures outweigh the semantics. Of course, common sense dictates that you ought not stick so much logic into sprocs that server performance is affected.

The usual suspects

Before I plow ahead directly into Postgres, I want to rewind a bit for the benefit of those who are already familiar with stored procedures in Microsoft SQL Server (versions 7.0 and 2000–I’m guessing 2005 isn’t much different, but I haven’t used it all that much) and/or MySQL (version 5.0). If this doesn’t interest you, feel free to skip ahead to the next section.

Postgres has a deserved reputation for being more complex than MySQL and is considerably less documented than SQL Server. On the other hand, Postgres is arguably more advanced than MySQL and a whole lot cheaper than SQL Server. Hence, a good number of people in both camps are beginning to take a harder look at it. So, to ease the ascent up the learning curve: a bit of review…

Microsoft SQL Server and MySQL (the later which, oddly, didn’t have sprocs until its most recent release little more than a year ago) both share a similar stored procedure syntax. First, SQL Server:

CREATE PROCEDURE [getQtyOrders]
@customerID int,
@qtyOrders int OUT
AS
SET NOCOUNT ON
	SELECT
		@qtyOrders = COUNT(*)
		FROM Orders
			WHERE accnum = @customerID

Executing a MS SQL stored procedure is largely dependant on your programming environment, but directly using T-SQL it looks a bit like this:

DECLARE @qty int
EXEC getQtyOrders @customerID=12677, @qtyOrders=@qty OUT
SELECT @qty

(1 row(s) affected)
42

With MySQL, the same stored procedure is defined as:

DELIMITER $$
CREATE PROCEDURE getQtyOrders (
customerID INT,
OUT qtyOrders INT)
BEGIN
	SELECT COUNT(*) INTO qtyOrders
		FROM Orders
		WHERE accnum=customerID;
END;
$$
DELIMITER ;

The “DELIMITER” directive is a bit of a kludge that wasn’t needed with SQL Server. We use it to temporarily change the command delimiter so that the semicolons inside the definition don’t prematurely end the CREATE PROCEDURE command. Other than that, the BEGIN/END block, and a slightly different way of assigning the result to the variable, things look quite similar to SQL Server. Executing it directly isn’t much different either:

CALL getQtyOrders(12677, @qty);
SELECT @qty;

(1 row(s) returned)
(0 ms taken)
42

Enter the dark horse

Note: I’m using PostgreSQL 8.0. If you’re not, this example won’t work. Ya might as well upgrade the development server now anyhow…

With Postgres things get a tad more complex; though not overly so. Flexibility is a feature, not a bug.

Both SQL Server and MySQL use a proprietary extension of SQL to provide stored procedure functionality (amoungst other features). Microsoft calls it Transact SQL, or “T-SQL”, and as far as I know, MySQL just calls it “MySQL”. Both variants are different enough to occasionally trip up a developer but are written around the common SQL-92/2003 standards. The net result is that the syntax used inside stored procedures resembles SQL code used without sprocs; the same “SELECTs”, “INSERTs”, etc. but with a few extra commands added for control flow and the like. Postgres stored procedures can also be written with a SQL-like language called “PL/pgSQL”. But, as an added twist, a developer could instead chose to write it in Perl, Python, or Tcl using PL/Perl, PL/Python, or PL/Tcl. Perl/Python/Tcl hackers may rejoice at this thought and it does open up a great many possibilities, but for now we’ll just stick with using the basic PL/pgSQL language.

Out-of-the-box, a Postgres database doesn’t have any procedural languages loaded. Assuming that your friendly, neighborhood DBA hasn’t already loaded in PL/pgSQL, we can easily do this with the following command:

/usr/local/pgsql/bin/createlang -U postgres plpgsql <database name>

The “-U” argument specifies which user account is used to execute the command. Since normal users can’t load languages, a privileged account will have to be used. Here I specified the account that the Postgres server utilizes, postgres. Now, onwards to setting up our getQtyOrders sproc:

CREATE FUNCTION getQtyOrders(customerID int) RETURNS int AS $$
DECLARE
	qty int;
BEGIN
	SELECT COUNT(*) INTO qty
		FROM Orders
			WHERE accnum = customerID;
	RETURN qty;
END;
$$ LANGUAGE plpgsql;

Yup–pretty much the same as MS’s T-SQL and MySQL. The mentionable differences (besides the self-explanatory LANGUAGE command):

Microsoft SQL Server has distinct roles for both user-defined functions (UDFs) and stored procedures. A UDF is typically used within the context of a query, while a sproc takes input and/or output from/to an application. MySQL also has both procedures and functions, but they are defined almost identically and work almost identically. Postgres dispenses with the difference and everything is simply defined as a “function.”

As with MySQL, we also see the double dollar-sign (”$$”). However, here we’re using it for a different kind of work-around. With Postgres, the function definition is just one long string and if single quotes are contained somewhere within it, they’d have to be escaped using a pair of single quotes: ”. If there were already escaped quotes within the function, they’d then need to be escaped again, giving us a total of four quotes. To make life easier (and our code more legible), we can surround the definition with double dollar signs which negates the need for escaping.

Calling the spro… er, function is also a bit different, but not horribly so:

SELECT getQtyOrders(12677) AS qty;

42

And that’s about the extent of the basics… you’re now on your way to fruitful career programming PostgreSQL.

Summary

While there are differences to be expected between the control-of-flow structures, the basic framework of a stored procedure in Postgres is very similar those of the more familiar MS SQL Server and MySQL. I held off on exploring Postgres because I was so heavily invested in other databases systems; both experience-wise and with many thousands of lines of stored procedures. However, I’m finding that I appreciate (and actually use) the added features of Postgres: sequences and very flexible authentication options are my current favorites. It also has “free-er” licensing than MySQL (BSD vs. GPL), making it an easier sell to overly-concerned management-types. Admittedly the docs could be better, but there are a number of good books available. I recommend “Beginning Databases with PostgreSQL” as a good starting point.

6 comments January 25th, 2007