Posted by Tony Rogerson on 10/29/06 22:17

> CHECK (content_url LIKE 'http://%.com'), -- or whatever grep() you

Interesting, I would really like to know how you would validate this as a
URL (or email address) in a constraint.

You need to look for a lot more that http:// and .com; there is http / https
for a start, ftp: there is .com, .co.uk, .net and about 2 dozen other
domains and there is sub-domains to handle.....

The only way you are going to write a safe constraint like this is to use
regular expressions and for that you'll need CLR.

So, come on celko - just how would you write this contraint to truely
validate the url is valid and also protect yourself from a DBA making an
accidental update (or melicious....)??????

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1162059442.535632.304470@e64g2000cwd.googlegroups.com...
>>> there can't be a UNIQUE constraint on the sortorder column.<<
>
> What is the nature of a sort on a set? It is a sequence; every element
> has a unique position in that sequence. This is by definition.
>
>>> But I really would love to hear how you would keep a list of links in an
>>> arbitrary order <<
>
> Now we have some more specs to use.
>
> CREATE TABLE UserLinks
> (user_id INTEGER NOT NULL
> REFERENCES Users(user_id)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> preference_nbr INTEGER NOT NULL
> CHECK (preference_nbr > 0),
> content_url VARCHAR (255) NOT NULL
> CHECK (content_url LIKE 'http://%.com'), -- or whatever grep() you
> need
> PRIMARY KEY (user_id, preference_nbr)); -- here is your unique
> constraint
>
> You will need a proc to move them around once they are in the table.
>
> CREATE PROCEDURE SwapUserLinks
> (@my_user_id INTEGER, @old_preference_nbr INTEGER, @new_preference_nbr
> INTEGER)
> AS
> UPDATE UserLinks
> SET preference_nbr
> = CASE preference_nbr
> WHEN @old_preference_nbr
> THEN @new_preference_nbr
> ELSE preference_nbr + SIGN(@old_preference_nbr - @new_pos)
> END
> WHERE user_id = @my_user_id
> AND (preference_nbr BETWEEN @old_preference_nbr AND
> @new_preference_nbr
> OR preference_nbr BETWEEN @new_preference_nbr AND
> @old_preference_nbr);
>
> Play with it a little bit. It takes a starting position in the list
> and moves it up or down to the new position and slides the elements in
> between either up or down.
>
> When you want to drop a few rows, remember to close the gaps with this
> code. It will start the numbering at 1 within each user.
>
> CREATE PROCEDURE CloseUserLinksGaps(@my_user_id INTEGER)
> AS
> UPDATE UserLinks
> SET preference_nbr
> = (SELECT COUNT (U1.preference_nbr)
> FROM UserLinks AS U1
> WHERE U1.preference_nbr <= UserLinks.preference_nbr
> AND user_id = @my_user_id
> AND @my_user_id = UserLinks.user_id);
>

[Back to original message]