[Fwd: Re: [om-list] synergy]

[Luke Call]

[sorry. correcting "to" line again.]

Perhaps it could also make sense for the graph edge traversal to be
implemented in a stored procedure, depending on the specifics.

Here's a dump of the database creation code, with comments where things
are nonobvious. I hope it's adequately readable:

create table Entity (
id bigint DEFAULT nextval('EntityKeySequence') PRIMARY KEY,
name varchar(60) NOT NULL
)
WITHOUT OIDS
st2.executeUpdate(sql)
st2a = mConn.createStatement()
create index upper_name on Entity (upper(name))
st2a.executeUpdate(sql)

st3 = mConn.createStatement()
create sequence QuantityAttributeKeySequence minvalue -9223372036854775808
st3.executeUpdate(sql)

st4 = mConn.createStatement()
// the parent_id is the key for the entity on which this quantity info
is recorded for other meanings see comments on
// Entity.addQuantityAttribute(...).
// id must be "unique not null" in ANY database used, because it is the
primary key.
create table QuantityAttribute (
parent_id bigint NOT NULL,
id bigint DEFAULT nextval('QuantityAttributeKeySequence') PRIMARY KEY,
unit_id bigint NOT NULL,
quantity_number double precision not null,
attr_type_id bigint not null,
valid_on_date bigint not null,
observation_date bigint not null,
CONSTRAINT valid_unit_id FOREIGN KEY (unit_id) REFERENCES entity (id),
CONSTRAINT valid_attr_type_id FOREIGN KEY (attr_type_id) REFERENCES
entity (id),
CONSTRAINT valid_parent_id FOREIGN KEY (parent_id) REFERENCES entity
(id) ON DELETE CASCADE
//ON UPDATE CASCADE  // here for good measure?, but no test written for
it. Hopefully we wuoldn't be changing those keys anyway.
)
WITHOUT OIDS
st4.executeUpdate(sql)

st5 = mConn.createStatement()
create sequence TextAttributeKeySequence minvalue -9223372036854775808
st5.executeUpdate(sql)

st6 = mConn.createStatement()
// the parent_id is the key for the entity on which this text info is
recorded for other meanings see comments on
// Entity.addQuantityAttribute(...).
// id must be "unique not null" in ANY database used, because it is the
primary key.
create table TextAttribute (
parent_id bigint NOT NULL,
id bigint DEFAULT nextval('TextAttributeKeySequence') PRIMARY KEY,
textValue text NOT NULL,
attr_type_id bigint not null,
valid_on_date bigint not null,
observation_date bigint not null,
CONSTRAINT valid_attr_type_id FOREIGN KEY (attr_type_id) REFERENCES
entity (id),
CONSTRAINT valid_parent_id FOREIGN KEY (parent_id) REFERENCES entity
(id) ON DELETE CASCADE
//ON UPDATE CASCADE  // here for good measure?, but no test written for
it. Hopefully we wuoldn't be changing those keys anyway.
)
WITHOUT OIDS
st6.executeUpdate(sql)

st7 = mConn.createStatement()
create sequence RelationTypeKeySequence minvalue -9223372036854775808
st7.executeUpdate(sql)

// this table "inherits" from Entity (each relation type is an Entity)
but we use homegrown "inheritance" for that to make it
// easier to port to databases that don't have postgresql-like
inheritance built in.  It inherits from Entity so that as Entity
// expands (i.e., context-based naming or whatever), we'll automatically
get the benefits, in objects based on this table (at least
// that's the idea at this moment...) --Luke Call 8/2003
st8 = mConn.createStatement()
create table RelationType ( // inherits from Entity see
RelationConnection for more info.
entity_id bigint PRIMARY KEY,
name_in_reverse_direction varchar(60),
directionality char(3) CHECK (directionality in ('BI','UNI','NON')),
//NOT NULL, //valid values are "BI ","UNI","NON"-directional for this
relationship. example: parent/child is unidirectional. sibling is
bidirectional, what is nondirectional?
CONSTRAINT valid_rel_entity_id FOREIGN KEY (entity_id) REFERENCES Entity
(id) ON DELETE CASCADE
)
WITHOUT OIDS
st8.executeUpdate(sql)
st8a = mConn.createStatement()
create index upper_name_in_reverse_direction on RelationType
(upper(name_in_reverse_direction))
st8a.executeUpdate(sql)

//Example: a relationship between a state and various counties might be
set up like this:
// The state and each county are Entities. A RelationType (which is an
Entity with some
// additional columns) is bi- directional and indicates some kind of
containment relationship, between
// state & counties. In the Relation table there would be a row whose
rel_type_id points to the described RelationType,
// whose entity_id_1 points to the state Entity, and whose entity_id_2
points to a given county Entity. There would be
// additional rows for each county, varying only in the value in
entity_id_2.
// And example of something non(?)directional would be where the
relationship is identical no matter which you go, like
// two human acquaintances). The relationship between a state and county
is not the same in reverse. Haven't got a good
// unidirectional example, so maybe it can be eliminated?
// --Luke Call 8/2003.
st9 = mConn.createStatement()
create table Relation (
rel_type_id bigint NOT NULL,  //for lookup so know which
RelationAttribute obj goes w/ what rows here
entity_id_1 bigint NOT NULL,  // what is related (see RelationConnection
for "related to what" (related_to_entity_id)
entity_id_2 bigint NOT NULL,  // entity_id in RelAttr table is related
to what other entity(ies)
valid_on_date bigint not null,
observation_date bigint not null,
PRIMARY KEY (rel_type_id, entity_id_1, entity_id_2),
CONSTRAINT valid_rel_type_id FOREIGN KEY (rel_type_id) REFERENCES
RelationType (entity_id) ON DELETE CASCADE,
CONSTRAINT valid_related_to_entity_id_1 FOREIGN KEY (entity_id_1)
REFERENCES entity (id) ON DELETE CASCADE,
CONSTRAINT valid_related_to_entity_id_2 FOREIGN KEY (entity_id_2)
REFERENCES entity (id) ON DELETE CASCADE
)
WITHOUT OIDS
st9.executeUpdate(sql)

st9a = mConn.createStatement()
create index entity_id_1 on Relation (entity_id_1)
st9a.executeUpdate(sql)

st9b = mConn.createStatement()
create index entity_id_2 on Relation (entity_id_2)


Mark Butler wrote:
> Luke:
> 
> My current genealogy project that has to integrate with a large C++ code 
> base, so I really couldn't do it in Java even if I wanted to.  However, 
> it would be nice to be moving in the direction of a common data model, 
> so if you would create a standalone commented description of your 
> database tables, I would be glad to compare to what I am doing and offer 
> suggestions for convergence.
> 
> Regrettably, most relational databases do not perform particularly well 
> for graph intensive applications like family history, at least in 
> client/server mode due to the high latency required for each edge 
> traversal.   ....