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
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.

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.

>> 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
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).

> 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.

> ... 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.

> 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
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.

> 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).

> 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

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;