Monthly Archives: January 2007

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.)

abilify side effects
buy liquid accutane
buy aciphex
generic acomplia
generic actonel
actos ambien
aleve effexor withdrawals
order allegra online
alli weight loss pills
altace online from rxpharmaceuticals
antibiotics online no rx
buy aricept on line
arimidex
ashwagandha dosage
cheap astelin
buy online atacand
atarax compared benedryl
side effects of augmentin
avandia purchase
buy avapro
avodart
cheap bactrim
buy online benadryl
benicar hct withdrawal
biaxin high blood pressure
buspar anxiety
online cardizem
celebrex use
buy cephalexin on line
viagra levitra cialis
buy cipro online
cla and guarana
clarinex 5mg
claritin 24
50 mg of clomid success
order clonidine
online colchicine
coreg cr cap 20mg
coumadin
generic cozaar
cheap creatine
crestor generics
cymbalta and weight loss
depakote
diclofenac na
differin cream reviews
diflucan cheap
diovan coupons
side effects of doxycycline
effexor xr withdrawal symptoms
buy cheap flagyl
price flomax
clomid glucophage
hair loss causes
hangover pill
hoodia
lamictal discount
purchase lamisil
gout and lasix and clonidine
levaquin 750
levitra low price
gain weight on lexapro
lipitor
lisinopril HCTZ
Melatonin Dosage
micardis tendon
buy mobic wihout prescription
Ingredients in Motrin
neurontin generic
nexium dreams
online nizoral
purchase nolvadex
omnicef side effects
Paxil Attorney
what’s the deal with penis extender
cheap phentermine online
plan b emergency contraception
side effects of plavix
overnight pravachol
dog prednisone
herbal alternative to premarin
prevacid
prometrium + transgender
Effect Propecia
provera maximum dose
prozac rebuilds brain cells
reglan side effects
risperdal and children
rogaine on sale in nj
seroquel as a sleep aid
DTC spend 2003 Singulair
skelaxin on line
laser stop smoking
side effect of strattera
mechanical stress relief
synthroid and high doses
topamax side effects
toprol
Side Effects of Toradol
tramadol online
lipitor and tricor
trileptal seizure
ultracet apap
Diflucan Valtrex
Best Price for Generic Viagra
voltaren product lable
vytorin report
weight loss diet plan
wellbutrin and withdrawal
yohimbe problems
Zantac 150
zetia vytorin ineffective
Zithromax and morgellons
zoloft and hair loss
buy cheaper zovirax
Zyban Nasea
Drug Zyprexa
Allegra Vs Zyrtec
cost of prescription zyvox
Share

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?

Share

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.

Share