MySQL confirguration script

DELIMITER //

CREATE TABLE Feeds (
ID int (auto incrementing),
FeedName VARCHAR(256) NOT NULL,
FeedURI VARCHAR(256) NOT NULL,
IsActive BIT NOT NULL,
PRIMARY KEY (ID)
)//


CREATE PROCEDURE UpdateFeed (
IN prmID INT NULL,
IN prmFeedName VARCHAR(256) NULL,
IN prmFeedURI VARCHAR(256) NULL,
IN prmIsActive BIT NULL,
OUT prmErrorID int
)

-- This sproc manages entries in the Feeds table
-- a valid non-zero value for prmID will trigger an update
-- this sproc is intended to enable granular changes to Feed records

BEGIN

SET prmErrorID = 0

-- check to see if the specified id points to a valid record in the Feeds table
-- if not then assume it's a mistake and set it to zero

IF prmID != 0 AND NOT EXISTS ( SELECT ID FROM FEEDS WHERE ID = prmID ) THEN
SET prmID = 0
END IF

-- if the id is zero then create a new record,
-- otherwise we update the existing record

IF prmID=0 THEN

-- all fields are optional, but that case only applies when updating
-- existing records, prmFieldName and prmFeedURI are required for new entries

IF IS NOT NULL (prmFeedName) and IS NOT NULL (prmFeedURI) THEN

INSERT INTO Feeds(FeedName, FeedURI, IsActive)
VALUES (prmFeedName, prmFeedURI, 1)

ELSE

-- 4001 can be the error code for an invalid null
-- there must be an error api, but this will do for now
SET prmErrorID = 4001

END IF

ELSE

-- use the current value when the parameter has a null value

UPDATE Feeds
SET
FeedName = CASE
WHEN IS NOT NULL(prmFeedName) THEN prmFeedName ELSE FeedName
END,
FeedURI = CASE
WHEN IS NOT NULL(prmFeedURI) THEN prmFeedURI ELSE FeedURI
END,
IsActive = CASE
WHEN IS NOT NULL(prmIsActive) THEN prmIsActive ELSE IsActive
END
WHERE ID = prmID

END IF

END
//


DELIMITER ;

Working with RSS

RSS has been kicking around for a while now, but everybody still seems to have their own take on it - as with most web standards most of the fields are optional, with that in mind a minimal RSS viewer would need to support the following mandatory fields:

title- name of the article
link - URI pointer to the article
description - a short summary or description of the article

Everything else is optional; concepts such as ttl, rating and category are rarely used, whereas pubDate and image are quite common.

A full spec for RSS 2.0 is hosted by Harvard University : [view]

Considering that both Firefox, IE and Outlook can render RSS it should be sufficient, in the minimal case, to present the user with a list of links to RSS Feeds and let the browser manage the presentation.

Modelling the database

A minimal implementation would consist of the following:

Tables:

Feeds
ID : int (auto incrementing)
FeedName : varchar(256)
FeedURI : varchar(256)
IsActive : boolean

Stored Procedures:

UpdateFeed - manages feed records in the database
ID : int { 0 if this is a new feed, otherwise the number of the feed to update }
FeedName : varchar(256) { name to use to label the feed },
FeedURI : varchar(256) { pointer to the RSS feed },
IsActive : boolean { false will hide the feed from searches }

PseudoSQL :

IF NOT EXISTS ( SELECT ID FROM FEEDS WHERE ID = @ID )
SET @ID = 0

IF @ID=0

INSERT INTO Feeds
(FeedName, FeedURI, IsActive)
VALUES (@FeedName, @FeedURI, True)

ELSE

UPDATE Feeds SET
FeedName = CASE WHEN ISNOTNULL(@FeedName) THEN @FeedName ELSE FeedName END,
FeedURI = CASE WHEN ISNOTNULL(@FeedURI) THEN @FeedURI ELSE FeedURI END,
IsActive = CASE WHEN ISNOTNULL(@IsActive) THEN @IsActive ELSE IsActive END


FindFeed - retrieves a set of feed records from the database
Filter : varchar(50) nullable { if not null then used as a search pattern }

PseudoSQL:

SELECT FeedName, FeedURI
FROM Feeds
WHERE
(IsActive = True) AND
((ISNOTNULL(@Filter)) AND (FeedName LIKE '%' & @Filter & '%'))

Technical test

Basically the idea is to knock up a small database of RSS feeds:

1. allow a user to enter a new RSS Feed link and add this to the database
2. list all current RSS feds held within the databas
3. allow each feed to be updated or deleted
4. view the contents of a single RSS feed