Hi clifford,
Thanks for a fascinating exchange, I will get the Halpin book. Work
might get in the way of concerted study but hopefully over time I'll
work through it.
Just a couple of points of clarification...

--- In information_modeling@yahoogroups.com, Clifford Heath
<clifford.heath@...> wrote:
>
> On 04/12/2007, at 9:54 AM, pitcat_regime wrote
> > Ok, I think this point comes up later - but I may misunderstand what
> > you mean in describing ploygot.
>
> > It is a point I to tried to make with the DRYSql author... AR and Og
> > are the same in terms of your use (persisting), but are opposites in
> > terms of where you 'start' - and that sometimes is not a choice one
> > has :)
> > No criticism of polygot or AF implied!
>
> The problem with DRYSql's method is that a relational database
> has already had most of the semantic information removed. The
> SQL meta-schema simply isn't rich enough to support it.
>
> Similarly, Og is based on defining objects, and mapping those to
> relations. Again, although the objects contain more semantic

Og currently implements an SQLStore, which is a specialization of a
generic Store, so one would expect to write a CQLStore. Og will
pretty much allow you to do whatever Ruby allows (all the
meta-programming, reflecting etc.), so I'd think the functionality and
expressiveness of a CQLStore would limited to what Ruby permits (If
Ruby's flexibility is not enough to generate CQL then the AF exercise
seems moot?).
On top of the CQLStore you might write an ActiveFactsAdapter - or
maybe the other way around. As far as the CQLStore or FactStore goes
you're starting with a blank page - what is written on that page is
whatever you need that Ruby permits. Form some later remarks I'm left
with the impression that there is something fundamentally incompatible
with Ruby and O/RM - such that information required for O/RM can't be
expressed by or interrogated from Ruby objects.

> information than the relational schema, a lot is still lost. Objects
> are not elementary, but aggregate. That is, they are a cluster of
> ideas that revolve around a single entity. The elementary facts
> that drove the choice of that particular aggregation are lost.

I'll need to read the Halprin book more to understand this, but a Ruby
class can be 'enchanted' (Og's term for the meta-programming that goes
on) with an arbitrary number of methods, attributes, variables etc.
what information it gets given and what the CQLStore should do with
this information... that seems to be specified by the AF/CQL.

> So both a relational model and an object model is a lower-level
> construct than a semantic model. That's why a new language is
> needed. I tried to create one as a Ruby DSL, but Ruby's syntax
> got in the way too much.

Ouch. But interesting.

> >> ActiveFacts doesn't yet take a model and transform it into a
> >> relational database design.
> >
> > OK its not clear if you mean a Fact based model, or a Ruby
> > AF-decorated-class model.
>
> Both are fact-based models, but I wasn't concerned with the syntax.
> It could come from a Ruby DSL, an ORM diagram, or from CQL.
> The transformation that's lacking is from the elementary form to the
> compound (relational) form. Look at the elementary and compound
> forms of the orienteering model here for an example:
> <http://dataconstellation.com/ActiveFacts/examples/>
>
> >> That's on the cards soon, and isn't terribly hard, but
> >> I'm otherwise occupied...
> >
> > There seems to be a large Ruby community interested in 'persistence',
> > there maybe enoguh people to help out on an initial effort?
>
> There are only a very few that know what a fact-based, or elementary,
> or semantic model is. There is a number of projects trying to make

I know, I'm one of them :)

> SQL less unpalatable, but they're only addressing the syntactic issues.
> What I'm building has a different and higher-level semantics.
>
> >> I'm creating a new query language, the Constellation Query Language.
> >> CQL has many advantages over SQL, the most important one being
> >> that queries written in CQL are immune to the most common kinds
> >> of schema migration that occur, including attribute migration. The
> >
> > OK that sounds very interesting. After emailing I looked at the
> > ORM-Metamodel.pdf and it looked like trying to map a Ruby class
> > to/from that schema there might be a better place to start.
>
> I've done that, twice actually. The ActiveFacts base API is an O-O
> API that captures exactly what you see in relational form in the SQL
> meta-model. I also have an ActiveRecord version of the same thing
> which is inside a Rails app I'm building to edit semantic models. It's
> not yet published, but it's very similar to the schema you've seen,
> with just a few concessions to Rails cursed "opinions" (i.e. errors).
>

OK, I've never used AR or Rails - cross fingers that won't change.
From what I could tell they hid too much of Ruby - you end up with a
large amount of non-transferable/non-extensible knowledge.

> > Obviously
> > I don't understand all the operation or relations flagged there, but
> > it seemed that the Og decorations would be much simpler...
>
> You should definitely get a copy of Terry Halpin's book. It will quite
> literally change forever the way you think about all this stuff. All the
> basic constructs are introduced in my example presentation to
> which I gave the URL above.

Yes I will, thanks.

> > ... however, I was stumped on how you'd (simply) query field(s) :)
>
> In sixth normal form, you may only have one non-key attribute per
> table. In that form, to access any attribute requires a join. This is a
> characteristic of the elementary form - every traversal of a fact type
> is a join. So when I query the fact type "Person has given Name", I'm
> doing a join. This query has two variables. When I execute it, I can
> bind either variable to a value, and executing the query yields the
> value(s) for the other variable. If I bind the Person variable, I get
> that
> person's given name(s). If I bind the name, for example to "Fred", I
> get all Persons called "Fred". I've given you no information about
> whether a Person may have only one, or more than one, given Name,
> and you didn't need to know. If more than one, there will be a join
> table between Person and Name. If only one, givenName will be an
> attribute in the Person table. But the point is that you didn't need to
> know that, and can *change* it, without the query being broken.
>
> This is a very simplistic example, obviously. If you "svn up"
> ActiveFacts
> to the most recent, and if you have NORMA (http://sf.net/projects/orm)
> installed in Visual Studio, you'll see an oil industry supply model.
> This
> has Regions, which have an estimated Demand for Products in a
> given Month, and Refineries, which have production commitments for
> products. Within certain seasons, certain products may be substituted
> for other products. Transport routes indicate which refineries can ship
> to which regions. A query in CQL can ask for the optimum orders for
> appropriate products to fulfil the demand for a given region, or
> potentially
> even to optimise all orders over all regions - a pretty complex task! So
> you see that there's potentially huge computational power expressed in
> quite simple queries. Building the engine to execute them will be
> another
> matter, of course... But so it was when SQL was first proposed.

CQL does indeed sound intriguing! The suspense is building :)

> > So it sounds like the answer would be to construct the CQL using Og
> > and hand-off the query generation to you CQL2SQL interpreter.
>
> No, because Og doesn't allow modeling at the right level. Rather you

I think Og permits modeling at the level Ruby does, so in fact this is
a more important observation: Ruby doesn't allow modeling at the
right level - correct?

> include the CQL file, which defines classes, then you re-open those
> classes to add code where needed.
>
> One of the operations on the model is to create a relational version
> of it, and using that, to create the actual database - so in that
> respect,
> it's like Og, not DRYSql.
>
> > So it sounds like Og would need to create a CQL-model
> > definition, then feed (save-require) that to polygot - correct?
>
> No. You write your CQL file, or use my Rails app "APRIMO" to do that,
> and then you can require it in a Ruby program and create & query the
> DB using the Ruby classes that were created by the CQL parser.

I'll keep watching AF with interest. However, purely from a end users
perspective: the requirement to know CQL to use AF (write the CQL
file) is no more attractive than the requirement to know SQL to use
DrySQL (create the database). The Rails app will fit an important
niche, but leaves those situations where Ruby is put to some of it's
best use - classes created and manipulated dynamically.

> > AF will mandate starting
> > with a CQL definition - which is fine, Og, etc will probably evolve to
> > translate some decorated ruby class (or more perhaps a class instance)
> > to CQL,
>
> Not sensible. That would be like writing assembly code and trying
> to work with the output of a reverse compiler. AF will have reverse
> engineering tools available, but the output will be a starting point
> to allow you to construct a semantic model; much of the interesting
> stuff simply isn't present in a relational schema, or even in a object
> DB language (like Og).

Is it (doing the interesting stuff) available/possible in an object
language like Ruby? If so then it should be available to Og.
I think you may misunderstand Og. A CQLStore would permit you to do
whatever Ruby allows. If however I cannot express things in Ruby that
the O/RM approach requires then that will be disappointing, and yes
then CQL is the primary language end users should learn, and Ruby
(AR/Rails, etc.) becomes a background helper script facility.

> > All sounds very promising, do you have any feel for when the CQL
> > definition/specification might be in public beta form
>
> I'm working at the same time on a parser and a CQL dump from a
> base ActiveFacts model. You can get a peek at the CQL by using
> ActiveFacts to convert NORMA model files to CQL, e.g.:
>
> ruby -I lib bin/read_norma.rb -C examples/norma/OilSupply.orm

I've posted to this list and on Ruby forge about the errors I saw
trying to run some of the examples, so thanks for providing the CQL
output, the variable definitions look straight forward, however some
of the entity qualification seem more elaborate, but systematic.

I'll watch with interest!

Thanks again
Mark

> which dumps (slightly incomplete CQL):
>
> // Model OilSupply
> Month = VariableLengthText(3);
> Product = VariableLengthText(80);
> Quantity = UnsignedInteger(32);
> RefineryName = VariableLengthText(80);
> Region = VariableLengthText(80);
> Season = VariableLengthText(6) restricted to {'Spring', 'Summer',
> 'Autumn', 'Winter'};
> Transportation = VariableLengthText();
> Year = UnsignedInteger(32);
> AcceptableSubstitutes = entity known by Product and Product and Season:
> Product may be substituted by alternate Product in Season,
> alternate Product is an acceptable substitute for Product in
> Season;
>
> Refinery = entity known by RefineryName:
> Refinery has exactly one RefineryName,
> RefineryName is of at most one Refinery;
>
> RegionalDemand = entity known by Product and Year and Month and Region:
> Region in Month of Year will need Product in at most one
> Quantity;
>
> Month is in at most one Season;
> TransportRoute = Transportation is available from Refinery to Region,
> Transportation is available to Region from Refinery;
> ProductionCommitment = Refinery has committed by Month to produce
> Product in Quantity,
> in Month Refinery has committed to produce Quantity of Product;
>
> This still has a couple of errors, for example there is a missing
> "alternate" in
> the "known by" list of AcceptableSubstitutes. Also some constraint
> types cannot
> yet be converted, and I may need to change existing parts of the
> schema to
> accommodate them. The value types (data types) are as received from
> NORMA,
> and not natural to CQL. Reference Modes, which are common patterns of
> identification of entities, are still not detected. They will reduce
> many of the
> entity definitions, for example, Refinery becomes: "Refinery = entity
> (Name);".
>
> This is basically a complete schema, which can be transformed into
> relational
> form. For the same model, NORMA produces this (nearly-usable) SQL for
> MySQL. I believe that the errors in NORMA's SQL generation are rapidly
> being fixed. Either way, you can guess which syntax I prefer!
>
> CREATE TABLE `Month`
> (
> monthValue VARCHAR(3) NOT NULL,
> season VARCHAR(6) ,
> CONSTRAINT MonthUniqueness PRIMARY KEY(monthValue)
> );
>
> CREATE TABLE TransportRoute
> (
> transportation VARCHAR() NOT NULL,
> refineryName VARCHAR(80) NOT NULL,
> region VARCHAR(80) NOT NULL,
> CONSTRAINT InternalUniquenessConstraint8 PRIMARY KEY(transportation,
> refineryName, region)
> );
>
> CREATE TABLE ProductionCommitment
> (
> refineryName VARCHAR(80) NOT NULL,
> quantity NOT NULL,
> product VARCHAR(80) NOT NULL,
> `month` VARCHAR(3) NOT NULL,
> CONSTRAINT InternalUniquenessConstraint10 PRIMARY KEY(refineryName,
> quantity, product, "month")
> );
>
> CREATE TABLE RegionalDemand
> (
> region VARCHAR(80) NOT NULL,
> product VARCHAR(80) NOT NULL,
> `year` NOT NULL,
> `month` VARCHAR(3) NOT NULL,
> quantity NOT NULL,
> CONSTRAINT InternalUniquenessConstraint17 PRIMARY KEY(region,
> product, "year", "month")
> );
>
> CREATE TABLE AcceptableSubstitutes
> (
> alternateProduct VARCHAR(80) NOT NULL,
> product VARCHAR(80) NOT NULL,
> season VARCHAR(6) NOT NULL,
> CONSTRAINT InternalUniquenessConstraint22 PRIMARY KEY
> (alternateProduct, product, season)
> );
>
> ALTER TABLE ProductionCommitment ADD CONSTRAINT
> ProductionCommitment_FK FOREIGN KEY ("month") REFERENCES `Month`
> (monthValue) ON DELETE RESTRICT ON UPDATE RESTRICT;
>
> ALTER TABLE RegionalDemand ADD CONSTRAINT RegionalDemand_FK FOREIGN
> KEY ("month") REFERENCES `Month` (monthValue) ON DELETE RESTRICT ON
> UPDATE RESTRICT;
>