Saturday, September 16, 2006

Michael Arrington Badmouths Squidoo (Again)

Michael Arrington delivered a presentation to The Future of Web Apps conference held in San Francisco on September 13-14, 2006. According to Dan Farber, who covered the conference for ZD Net, Arrington included Squidoo in a "What were they thinking?" list of companies that he was "not proud that they exist."

The startups subject to Arrington's stigmata are Inform, Gather, PubSub, Browzor, Jigsaw, and Squidoo. Of that group, I'm only familiar with Squidoo and Gather; the preceding two links point to one of my Squidoo lenses and a Gather demonstration page.

Note: This post is an expansion of the "Subsequent Events [9/14-15/2006]" section of my August 6, 2006 "Hubpages' Attempt to Clone Squidoo is a Bust" post. This post was updated 9/18/2006.

Squidoo describes itself as "a new online platform and community that makes it easy for anyone to build a single page--called a lens--on a topic, idea, product or cause he is passionate about. These lenses in turn help finders get unique, human perspectives instead of computer-selected and often irrelevant search results."

Squidoo received favorable coverage in an April 10, 2006 New York Times article, "A Home Where Bloggers Can Plumb Those Obscure Passions." A Google search on "The Black Scholar" returns about 30,000 hits; my Squidoo lens on the topic is in second place—immediately after The Black Scholar's Web site.

Gather is a social sitebuilding startup that claims to be "a place to connect over common interests and passions, to engage in dialog or share different viewpoints." BusinessWeek's Liz Ryan named Gather one of the Top Ten Desktop Diversions of 2006 on March 27, 2006. I'm inclined to give a BusinessWeek columnist far more credence than a blog entrepreneur.

Arrington's Praise for Squidoo-Like Wiki Hosts

On June 18, 2006, Arrington favorably reviewed Wetpaint, an ad-supported Wiki hosting site, which received $5 million funding from Trinity Ventures and Frazier Technology Ventures. Arrington said that Wetpaint "is the best hosted wiki I’ve seen so far," comparing it to competitiors PBwiki, Wikia, and JotSpot.

Note: The "best hosted wiki I've seen so far" is StikiPad, based on my first tests of the preceding and a few other hosted wiki sites. StikiPad offers exceptional editing flexibility, doesn't clutter free accounts with AdSense adverts, and has a very intuitive and attractive UI. Here's a link to the default page for my first StikiPad wiki. Click here for a WikiMatrix comparison of JotSpot, PBWiki, Socialtext, StikiPad, and Wetpaint. StikiPad offers free as well as paid—$4.95, $9.95, and $14.95—accounts. (StikiPad is based on the Ruby-based Instiki wiki clone and the Ruby on Rails framework.)

My impression of these wiki sites is that they are simply free (ad-supported) or paid Web site hosts with multi-person editing, comments, or both enabled. For example, PBwiki offers free, $9.95, $24.95, and $34.95 per month options. JotSpot (requires free registration to read) has $9.95, $24.95, $69.95, and $199.95 per month plans. Wikia requires a wiki to "have a large potential audience and be likely to attract enough editors to maintain the wiki." The preceding links open simple test wikis; here's a link to a more complete Wetpaint wiki.

Note: No hosted wiki that I've seen so far, including Socialtext and wiki.com, has features that come close to matching the capabilities of Microsoft's Windows SharePoint Services (WSS) 2.0 or 3.0, which is a free add-in to Windows Server 2003 SP1. Stay tuned for a future post comparing WSS 3.0 and online wiki applications.

Wiki sites enable multi-person page editing, but most wiki sites offer far fewer editing widgets than Squidoo. I've seen nothing so far to convince me that any of these startups have much—if anything—more to offer than Squidoo, Google Pages, or Blogger. For example, Blogger lets you enable team blogging. If you're willing to take the risk, you can enable multi-person editing of Squidoo lenses by disclosing your username and password to trusted colleagues. Alternatively, you can set preferences to Share My Squidoo and enable anyone to edit your lens.

Rick Segal's Take on Arrington's Squidoo Castigation

Canadian venture capitalist Rick Segal takes on Arrington's castigation of Squidoo in a trilogy of posts—Mike Arrington - Sit Down, Michael Arrington Responds, and The Good of Web 2.0. Squidoo donates a portion of advertising revenue after operating expenses to charity, and Squidoo Lensmasters have the option of donating their Ad Sense earnings to charity.

One of Segal's issues is Arrington's insistence on an accounting of Squidoo's donations before considering a retraction of his miscategorization of Squidoo. According to Squidoo's Report page's Squidoo-wide Stats section, Total Money for Charity was $7,194.03 from 40,282 Total Lenses as of 9/19/2006.

Note: Here's how Squidoo distributes its income: "We divide up the money we receive in a very public way. First, we pay our bills. That's direct out of pocket expenses like rent and servers and salary and benefits expenses (our CEO doesn't take a salary, and neither does our board of directors). Then, with no other deductions, we pay 5% of our post-expense revenue directly to the charity pool, 50% directly to our lensmasters and retain the rest to pay off investors and employees." (From Everything about getting paid, "11. Does Squidoo Make a Profit?")

The fact that charities and Lensmasters are receiving payments—regardless of the amount—indicates that Squidoo is profitable and debunks Arrington's May 9, 2006 assertion that Squidoo is Seth Godin's "Purple Albatross." It's a good bet that Hubpages—a Squidoo rip-off and Arrington's favored topical Web page host—won't reach profitability before it burns through the $2 million funding it received earlier this year from Hummer-Winblad Venture Partners.

Arrington's Squidoo-related posts and comments demonstrate to me that he harbors personal enmity for Squidoo and/or Seth Godin. The question for TechCrunch readers is the extent to which this animus biases Arrington's "Web 2.0" reviews.

Technorati tags: , , , , , , , , ,

Monday, September 11, 2006

SQL Server 2005 Ordered View and Inline Function Problems

SQL Server 2000 introduced the TOP n [PERCENT] operator to enable restricting the number of rows in the resultset of a view or inline table-valued function (TVF). In SQL Server 2000, starting the SQL statement for a view or TVF with SELECT TOP n [PERCENT] ... enables adding an ORDER BY clause to order the query's internal rowset so as to return the correct resultset. As a byproduct of that operation, views and TVFs returned resultsets sorted in accordance with the ORDER BY clause. A SELECT TOP 100 PERCENT query with an ORDER BY clause in SQL Server 2000 or MSDE 2000 views and TVFs returns all rows sorted. SQL Server Management Studio [Express] and the Microsoft Access Upsizing Wizard expect—and the Wizard depends on—this behavior. SQL Server 2005 [Express] does not respect the SELECT TOP 100 PERCENT operator or it's SQL Server 2005-only functional equivalent—SELECT TOP (100) PERCENT—and ignores the ORDER BY clause in views and TVFs. Thus resultsets are not sorted. ANSI SQL specifies that ORDER BY clauses are valid only in the top-most (outer) SELECT block of a query. But no ANSI SQL version supports the TOP operator, so the ANSI rules don't apply to SELECT TOP queries. Most SQL Server DBAs don't permit use of ordered views or TVFs, preferring to apply the sort with an ORDER BY clause in the SELECT block that invokes the view or TVF. Visual Studio and Microsoft Access developers commonly use the shortcut. As mentioned earlier, SQL Server 2005's corresponding TOP (100) PERCENT function also enables adding an ORDER BY clause to a view or TVF. However, the SQL 2005 query optimizer ignores the TOP (100) PERCENT and ORDER BY clauses and does not return a result set with the specified custom sort. The query optimizer "optimizes out" the TOP (100) PERCENT function as redundant, which deactivates the ORDER BY clause without warning. Fortunately, there's a workaroud that I'll discuss shortly. SQL Server Books Online's ORDER BY Clause (Transact-SQL) topic has a note that states:

When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.
The upshot of this note is that there is no guarantee whatsoever that SELECT TOP ... ORDER BY ... query constructs will return rows in sequence specified by the ORDER BY clause. My experience—and that of most other commentators on this issue—is that SQL Server 2000 returns rows in the expected order for all valid TOP integer and TOP float PERCENT values, as does SQL Server 2005 for valid integer and all float values except 100. The da Vinci Toolset Obscures the Issue The Visual Data Tools (VDT)—more commonly called the da Vinci toolset—first appeared in the SQL Server 7.0 version of SQL Server Enterprise Manager (codenamed "Starfighter"). SQL Server 2005 Management Studio (SSMS), SQL Server Management Studio Express (SSMSX), Visual Studio 2005, and Access 2007 use the current da Vinci toolset version. The da Vinci toolset's query parser automatically adds a TOP (100) PERCENT operator when you save or execute an ALTER VIEW or ALTER FUNCTION definition that contains and ORDER BY clause in its SELECT block. Note: SQL Server Express (SSX) SP1 offers SSMSX as an individual download or bundled with SSX SP1 in SSX with Advanced Services SP1. Here's an example of a TOP (100) PERCENT query (Access's Alphabetical List of Products) in SSMSX's view design window:

Notice that the resultset in the lower Results pane is ordered alphabetically by the ProductName column. However, when you right-click the Alphabetical List of Products item in Object Explorer and choose Open View, the resultset isn't ordered by ProductName (or any other set of column values), as seen here: The sorted display of the Results pane is undoubtedly due to the SELECT query in the SQL pane being the outermost query at this point. This ambiguous presentation for views is likely to lead unsuspecting developers down the primrose path.

Note: SSMX and a Microsoft Access query from upsized Northwind.accdb is used as the example, because many more data-intensive application developers use (or will use) SQL Server (2005) Express (SSX) with SSMSX and Visual Studio Express editions or Access than SSMS and SQL Server 2005 Standard or Enterprise editions. (Visual Studio Express editions don't include the VDT.) The da Vinci Tools in the Access 2007 Project Designer Microsoft Access 2000, 2002, 2003, and 2007 use the da Vinci toolset as the Project Designer for Access Data Projects (ADPs). However, the Access 2007 Beta 2 Technical Refresh version shown here appears to use a slightly different query parser. Adding a sort to a view or inline TVF adds the SQL Server 2000-style TOP 100 PERCENT operator (without the parenthesis for backward compatibility with the Microsoft Desktop Engine (MSDE) 2000.) Note: Access 2007 Beta 2 TR won't display the contents of the Diagram or Grid panes if you or SSMS[X] add parenthesis to the TOP operator. Addition of parenthesis by SSMS[X] prevents round-tripping of view or TVF designs to Access 2007 without loss of Diagram and Grid panes. Running the preceding query displays the unordered resultset in Access's Datasheet View, as shown here: Access substitutes it's Datasheet View for SSMS[X]'s Results pane. The preceding resultset obviously isn't ordered by ProductName, so developers will know immediately that the sort wasn't applied.

Note: In Access 2007 Beta 2, clicking the Tools group's Run (!) button of the Function & View Tools, Design ribbon doesn't update the row order of an open Datasheet View. You must close and reopen the Datasheet View to see the effect of a change to the view or inline TVF row order. A Simple Workaround for the TOP (100) PERCENT Problem If you must apply a custom sort order to views and TVFs, which is generally considered to be a hack, substitute TOP (VeryLargeInteger) for TOP (100) PERCENT. The largest integer that the query parser will accept is MAX(int) or 2147483647. This limitation appears to be for backward compatibility, because SQL Server 2005 converts TOP (n) values to the bigint data type. Here's the view's new query in SSMSX: In this case, the Results pane above reflects reality, as shown below: The TOP 2147483647 operator also behaves correctly in Access views and queries. However, Access developers have the option to set the Order By property of a view or inline TVF to Column1 [DESC][, Column2 [DESC] ... ] on the Data page of the ObjectName Properties dialog. This property specifies the ORDER BY clause of the outer query. Visual Studio developers and others might need to alter their TableAdapters or SELECT queries to apply an ex post facto sort order.

Note: SQL Server 2005 TOP (n) views are updatable if the view without the TOP (n) function is updatable. Conclusion It's surprising that the SQL Server tools group didn't alter the query parser to replace TOP (100) Percent with TOP (2147483647). The group also should have fixed—or warned users about—the ambiguous presentation in the Results pane for views.

The appropriate solution for SQL Server 2005 [Express] SP2 is to require SQL Server 2005's query processor to respect SELECT TOP (100) PERCENT ... ORDER BY views and TVFs by emulating SQL Server 2000 behavior, and change the party line by guaranteeing that SELECT TOP ... ORDER BY views and TVFs return a sorted resultset.

I haven't tested the workaround exhaustively, but it's likely to work in most or all cases. However, you're far better off from a performance and reusability standpoint to follow the ANSI SQL rules and apply the ORDER BY clause within the outermost query block, not in views or inline TVFs.

Note: For more information about the use of ORDER BY in SQL Server 2005, see the Ordering guarantees in SQL Server... item by Conor Cunningham, Query Optimizer Development Lead, in the SQL Engine Tips blog. See Books Online's TOP (Transact-SQL) topic for more detailed information on the TOP function.

Updates: Minor updates with clarifications on 9/27/2006 and 9/29/2006.

Technorati tags: , , , , , , , , , , , , , , ,

Monday, September 04, 2006

Generate RSS Feeds with ASP.NET and LINQ to XML

Jim Wooley has written a clever LINQ to XML procedure that generates RSS feeds from event logs. If you've installed the Microsoft Visual Studio Code Name “Orcas” Language-Integrated Query, May 2006 Community Technology Preview, you can download Jim's source code for the _Default partial class as Default.aspx.vb, and give it a test drive, preferably with IE 7.0 RC1 or later. Just create a new Visual Basic LINQ ASP.Net Web Site from the VB templates and substitute his Default.aspx.vb for the original empty version under the Default.aspx node, as shown here:

Tip: Clear at least the Application log and reboot before running the project. Otherwise, you'll find that Iexplore.exe will consume almost all available memory while it attempts to generate an RSS file with several thousand items.

Press F5 to build and run the project. If you have IE 7.0 RC1 or a late beta, the page appears as shown here:

If you're familiar with Dave Winer's RSS 2.0 spec, you'll notice some missing items—such as the top-level <rss version="2.0"> element—and non-conforming <publisher> and <author> tags under the <channel> element. <item> elements also have non-conforming <date> and <creator> tags, and dates that don't conform to RSS's requirement for the RFC 822 date/time format.

Adapting the Page for Feed Readers

IE 7.0 ordinarily displays RSS and ATOM files with a style sheet. For example, here's the start of the IE 7.0 feed page for the OakLeaf Systems blog as of this post's date:

Note: IE 7.0's style sheet treats ATOM 0.3/1.0 feeds as RSS 2.0 feeds with elements qualified by ATOM namespaces.

I wanted to see how IE 7.0's style sheet would handle the feeds generated by Jim's project. The missing top-level <rss version="2.0"> element prevents IE 7.0 from applying its feed style sheet. I believe that other non-conforming elements might prevent some readers from displaying the data correctly. I also wanted to add hierarchical <category> tags to see if IE 7.0 would filter events by category correctly.

So I added the <?xml version='1.0' encoding='UTF-8'?> XML declaration and the <rss version="2.0"> element pair, then made other minor corrections and a few additions to bring the feed up to the RSS 2.0 standard. Here's the result before subscribing to the feed in IE 7.0:

Note: The five filter categories are EventLog, EventLog/Entries, EventLog/Entries/Application, EventLog/Entries/Information, EventLog/Entries/Application/Warning. Filtering works as expected.

And here's the transformed page after subscribing to the feed:

If you publish the Website to IIS, run Cassini on a fixed port, or just copy the current URL—http://localhost:1686/XLinqRSS/Default.aspx—for this example you can subscribe to the feed with any popular reader. For example, here's Dare Obasanjo's RSS Bandit open-source reader displaying the OAKLEAF-MS15 feed:

You can read an abbreviated version of the updated RSS 2.0 feed code here and see the modified Visual Basic 9.0 source code for the updated WriteRSS20 subprocedure here.

Syndicating event logs isn't a mainstream RSS application, but Jim Wooley's technique demonstrates the remarkable efficiency of programming with LINQ for XML and VB 9.0's XML inline literals with inserted element and attribute values.

Technorati Tags: LINQ to XML, XLinq, LINQ, Orcas, RSS 2.0, RSS, Event Logs, Jim Wooley