Re: On view updating

Paul <paul_at_test.com> wrote in message news:<41595cde$0$69737$ed2619ec_at_ptn-nntp-reader01.plus.net>...
> Interesting solution. But I think it involves pushing functionality away
> from the database engine and into the type engine.
>

I'm leaping in a little late in this discussion, but why would you consider that a problem ?  

> Taking this to its logical absurdity, you could have a table with a
> single column of type "logical proposition"! With a function "VAL" say
> that returned either true or false.
>

Mmm... no, it would be simpler than that. You'd just store "logical proposition", and add new propositions and delete them if they were found to be false. No need for VAL - if it's there, it's true !  

> It also means the DBMS has to be know this type is a regular expression,
> just as it had to know an empty string meant "missing data". Because
> suppose you enter a value of '/[Bill|John]/' for someone's name. Now if
> you do a query in SQL like:
>
> SELECT * FROM emp WHERE name = 'Bill' OR name = 'John'
>
> It's not going to pick up our row, even though logically it should.
> You'd have to just know that the column is a regular expression and use
> some function called "Like" say that takes two regexps as arguments and
> returns true if the first regexp is a subset of the second.
>
> SELECT * FROM emp WHERE Like(name, '/[Bill|John]/') = 1
>
> The "Like" function would have to realise that, for example,
> '/[Bill|John]/' and '/[John|Bill]/' meant the same thing. Also you'd
> want Like('/[Bill|John]/', '/[Bill|John|Fred]/') to be true.
>
> But the two queries above should logically return identical results. So
> the database engine would have to know about and treat regular
> expression types as "special" and be able to relate them to its own
> boolean logic.
>

Why would the database engine need to know anything about this ? It needs to know there is a function called "like", which when supplied with some parameters will return an integer from your example (why ? why not just a boolean ?) which will then have to be supplied to an operator (e.g. =) to return the desired boolean result. The "like" operator may have to know all about regexes and whatever, but the DBMS certainly doesn't. (Indeed I'd go as far as to say it *shouldn't* - the internal workings of the type & the like operator are none of the DBMS' business.)

Interesting idea though, regexes as types in their own right, rather than as expressions resulting in strings... Alternatively, if you had a DBMS that understood the Haskell type system, your entire problem becomes :

• forename type, allowing for either one and only one known forename
• or a list of possible forenames
• of course, the list of possible forenames could be empty... ;) data Forename = KnownName String | PossNames [String];
• a like operator, to compare Known forenames to Possible forenames like :: Forename -> Forename -> Bool like KnownName x KnownName y = x == y like KnownName x PossNames y = x `elem` y like PossNames x PossNames y = x == y like PossNames x KnownName y = y `elem` x like _ _ = False

The DBMS should only need to know

- there is a function called like
- it takes two Forename parameters
- it returns a boolean, and is therefore suitable for using directly

> Suppose you allow global NULLs.
>

Ugh. Anyway...

> Then if you have a row like: (123, NULL, 10, 10,000)
> to mean "Employee 123 is of unknown name, works in dept 10, and has
> salary 10,000"
>
> Does that conflict with the row-level constraint:
> empid!=123 OR (empid=123 AND (name='John' OR name = 'Bill'))?
>
> I guess that any boolean expression involving NULL would return NULL. Do
> you have 3-valued constraints in SQL? I'm not sure.
>

I haven't tried this, but it strikes me that you might have to get into contortions with ifnull(...) in there.

> If it didn't conflict with the constraint then we have effectively
> stored in our database the fact that employee 123 has an unknown name,
> but is either called John or Bill. If it did, then my point is irrelevant.
>

But what if employee 234 might be called Terry or June ? Would you expect to keep adding row constraints to deal with each possible situation like this ?

> Now to deal with it if you don't have global NULLs I'm not sure...
> Maybe you could make it a compulsory part of a type definition to define
> one value for each type that means "data unknown"?
>

I'm never keen on "thou shalt"s like this - there's always someone who "shalt not". Maybe you have types that never need to handle "data unknown" conditions ?

All good clean family fun !

• Tony