Re: Database Builders, Code Generators, On-Topic?

Quoting unnamed sources, Bob Badour claimed:

> "Kenneth Downs" <MyUseNetHandle_at_linuxmail.org> wrote in message
> news:e3arab.kkk.ln_at_mercury.downsfam.net...

>> Hi folks.  I've been lurking here for a few weeks now and have made a
>> post or two, but up until now have not started a thread.
>>
>> There is a topic close to my heart which I do not see discussed here
>> much,
>> and I wonder if it is considered topical.  Briefly, I spend my days
>> designing and implementing data-driven systems, wherein we use a

>> of tables that describe tables, columns, keys, references and so forth.

This may be only semantics, but we reserve the term "system catalog" for the database server's tables or views of the schema. Our own tables are outside of the system catalog. This has many advantages, not the least of which is that run-time code only needs to know our own schema, instead of the various different schemas offered by the platforms we execute against.

Used in this sense, the purpose of the system catalog is to reveal a schema through DML, while the purpose of our catalog is to store our specification and generate both DDL and DML.

>
>

>> We
>> use this to generate both DDL at install time and DML at run-time, and

>> client code (pure under-the-hood, nothing cosmetic).

Upgrades are actually the first reason I developed early versions of this system, when I was working independently 7-8 years ago. The flow is:

1. Our catalog equals "what I want"
2. The system catalog reveals "what is"
3. Generate a bunch of DDL to turn "what is" into "What I want"
4. Optionally Execute. Or stop and report the intended plan for review, approval, etc.

This has the nifty added advantage that a first time installation is the same as an upgrade. If "what is" is empty, it builds the entire database.

This should answer your second question also, which is that it can turn any schema into any other schema.

The third question could mean, "does it drop retired columns and tables?" Mine does not, we just stop using them.

The third question might also mean, "Can you upgrade a production system without destroying data?" Since I'm new here I'll figure there is no way you would really ask that question and that I do not know what you mean.

<snip>

>
>

>> In an effort to give some rigor to our design strategies, I have coined

>> the term "Scalar Complete" to refer to the insistence that all meta-data

>> composed entirely of scalars, meaning that meta-data must never be an
>> expression that is evaluated according to the syntax rules of a language
>> (the expression "price * qty" may look like passable scalar data, but it

>> not), and must never require parsing.

The restriction is not arbitrary. If you set out to design a meta-data system, then you must make it Scalar Complete. If you break this rule and include expressions or something that must be parsed, it breaks down quickly. When it is not Scalar Complete, it is just another system for storing code.

Perhaps an example will suffice. Let's say you are building a list of derived column definitions, something like this:

colname expression Comment

=======   ===============    =================
dsc_amt   price * dsc_pct    Discount Amount

In this case, notice that you cannot validate the expression with a reference to a table of columns, because you have to parse the expression. You also cannot tell until run-time if the programmer slipped in a syntax error. A key point here is that we want to use the relational model to validate everything, we do not want to reproduce a SQL parser to check the validity of expressions.

As for making my life easy, um, guilty. It also makes the system rather robust, self-documenting, flexible, cross-platform, and has the potential (we have not gone this far yet) to be language-independent. Is this bad?

>
>

>> We have had great success with this approach for C/S apps, and I would

>> to discuss such projects with like-minded inviduals, but as I say I do
>> not see a lot of discussion of these types of things, either here or
>> anywhere else that I can find.

I have followed a thread or two on transactions that Paul gets involved in, but I have not seen any comments on a standard relational system catalog.

Also, this is more than a system catalog. We believe that the distinction between a schema description and the listing of business rules is itself a false distinction, based on this principle:

"Because all business rules must materialize as operations against a database, then all business rules can be reduced to schema statements."

Taken with the statement given below, "All operations that can be performed against a database can themselves be depicted within a database as scalar values", it is not so much a system catalog that we have as a business catalog.

>
>

>> So again, I am wondering if such discussions would
>> be considered topical here, or if there are other forums known for this.

I don't have yet the command of terms to express it rigorously, but in the vernacular it would be "Never code SQL." Or, "Always generate SQL." It is based on the simple observation that all operations permitted upon a database can themselves be expressed within a database. This is a very good thing because you can in principle reduce the entire specification of a business application into tables. This in a nutshell is the theory, that if you can specify it as something that must happen in a database, then the specification itself can be expressed in a database.

-- 
Kenneth Downs