Re: Proving an Upgrade is Possible

Jan Hidders wrote:

> Kenneth Downs wrote:

>> Jan Hidders wrote:
>>>
>>>Ok, so I suspect are you asking the following:
>>>
>>>"Given the old database schema and a few updates to its constraints (but
>>>not its structure) is it possible to algorithmically decide (or
>>>mathematically prove) whether all instances of the old schema are also
>>>instances of the new schema?"
>>>
>>>I leave it to you whether it is "algorithmically decide" or
>>>"mathematically prove".
>> 
>> You are on target, except that constraints are included, and also
>> automation
>> (columns like price_extended = price * qty).  So while we are at it, this
>> means things like NULL/NOT NULL rules, DEFAULT values and so forth.

To provide an analytic and deterministic answer to the viability of any db change, you must consider constraints, null rules, and defaults.

Consider this case. Add two columns to a table, COL_A and COL_B, with the constraint that COL_A < COL_B. One has a default value, the other does not, and they may not be null. What on Earth do you do to satisfy these requirements in the rows that already exist in the table?

I am not here asking for the answer to that question, anybody in this forum can come up with case-by-case answers to puzzles. What I am probing is the ability to determine the answer by analysis in the general case. This way a db analsyst punches in what they want to happen, and the system tells them why it will not work.

>
> Or do you want to also consider updates that change the structure of the
> schema, because in that case my formulation would be completely off track.

I must not understand your formulation because I thought we were talking about the same thing: analyzing in advance a change to a database that might include anything the db server has implemented: tables, columns, constraints, null rules, and defaults.

Perhaps we should keep automation out of it because that is something I do that is generally considered heresy and it tends to divert discussions.

>

>> Another way to state the question is:  "What structure of meta-data
>> allows us to specify completely a database of arbitrary complexity and
>> also allows us to demonstrate by analysis that any given change in the
>> meta data is valid or not valid".

The whole point is to arrive at a description of the meta-data that allows the analytic prediction of the validity of a state change. The meta-data is in tables, so it is valid if it satisfies the constraints on those tables.

Arbitrary complexity means the system you are describing in meta-data has no limits on complexity, either in number of tables, columns, constraints, or their interaction. Put another way, it is not a "toy" system that breaks down when you try to make real-world mission-critical stuff.

To demonstrate by analysis is to examine the meta-data and know if the upgrade will succeed. I did not choose your term "algorithmically decide" because it is not a step-by-step code thing, the validity or invalidity of the state change should be captured in the state of the meta-data. For the same reason I did not choose your term "mathematically prove". Demonstrate by Analysis seems closer to the mark because we want to examine (or analyze) the meta-data.

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)