Posted by Tony Rogerson on 10/30/06 22:26

I thought you'd try and wriggle your way out of that one.

> I would Google it and find this website, which is full of handy regular
> expressions for my constraints. This page gives ten answers.
>
> http://regexlib.com/Search.aspx?k=URL

And just how the hell are you going to put that in a CONSTRAINT using
standard SQL ?? - short answer - you can't which is my whole point.

> You need the SIMILAR TO operator in SQL-92 or a vendor
> grep() functions such as Oracle and DB2 have.

Not available, so you advocate a vendor grep function?

Judging by your response here and other stuff I've seen you write about CLR
you've not a clue, you are giving comment on something you know nothing
about - very dangerous game to play and cowboyish at best.

> In SQL Server, you will
> still have an ugly translation today. The x|y operator becomes <exp>
> LIKE x OR <exp> :LIKE y and so forth. There was an old public domain
> parser for grep() to SQL Server dialect, but I cannot find it on my
> disk. It probably should be re-done with CASE expressions.

My definition of a cowboy is somebody who frigs a job, somebody who says it
will take 2 days when in reality it should take an hour, somebody who over
complicates things so they get asked back because they are the only person
who understand whats been developed, so the real cowboy is not me here
celko.

The use of CLR in this context will allow you to code a function that uses
the regex library, that same function can be used in SQL Server and in
applications - its a common re-useable component across the entire
application; no more frigging string manipulation in SQL.

> But you are confusing two different concepts; validation and
> verification. A constraint can only validate a data element (This is a
> syntactically correct URL) and verification (This URL actually belongs
> to someone). Verification has to be external to the schema by
> definition.

I am confusing nothing; I'm talking about validating the correct syntax of
the URL not that it exists, you can never know that because the existance of
the URL is outside the scope of the transaction, you have no consistency
control over the third party DNS server nor domain registration entity.

> The way people verify a URL is to send an email to it. It is like
> having a GPS tool or sextant to verify a (long, lat) location.

Wrong again, a URL may not have an MX record so emailing means nothing - the
URL may still be valid in terms of its existance even if you can't send
email to it.

I ask you again - how would YOU write this check constraint to validate the
URL is syntactically correct in STANDARD SQL? You'd kludge it even though
other features are available to give code reuse, maintainability etc...

And as for portability, thats just mythical for constraints, ORacle and SQL
Server both have different default isolations so a number of check
constraints just won't work properly.

--
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:1162241405.793659.216090@k70g2000cwa.googlegroups.com...
>>> I would really like to know how you would validate this as a URL (or
>>> email address) in a constraint. <<
>
> I would Google it and find this website, which is full of handy regular
> expressions for my constraints. This page gives ten answers.
>
> http://regexlib.com/Search.aspx?k=URL
>
> I guess cowboys post first and Google later :)
>
>>> 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. <<
>
> No CLR, Tony. You need the SIMILAR TO operator in SQL-92 or a vendor
> grep() functions such as Oracle and DB2 have. In SQL Server, you will
> still have an ugly translation today. The x|y operator becomes <exp>
> LIKE x OR <exp> :LIKE y and so forth. There was an old public domain
> parser for grep() to SQL Server dialect, but I cannot find it on my
> disk. It probably should be re-done with CASE expressions.
>
>>> How would you write this constraint to truly validate the URL is valid
>>> and also protect yourself from a DBA making an accidental update (or
>>> malicious....)? <<
>
> Nothing can protect you from a malicious DBA; with full DBA powers he
> can destroy the world.
>
> "On two occasions I have been asked, 'Pray, Mr. Babbage, if you put
> into the machine wrong figures, will the right answers come out?' I am
> not able rightly to apprehend the kind of confusion of ideas that could
> provoke such a question." -- Charles Babbage
>
> But you are confusing two different concepts; validation and
> verification. A constraint can only validate a data element (This is a
> syntactically correct URL) and verification (This URL actually belongs
> to someone). Verification has to be external to the schema by
> definition.
>
> The way people verify a URL is to send an email to it. It is like
> having a GPS tool or sextant to verify a (long, lat) location.
>

[Back to original message]