|
|
Boost-Commit : |
Subject: [Boost-commit] svn:boost r78525 - in sandbox/icl/libs/xplore/br1/sqlbrowser: . gen
From: afojgo_at_[hidden]
Date: 2012-05-21 03:16:25
Author: jofaber
Date: 2012-05-21 03:16:23 EDT (Mon, 21 May 2012)
New Revision: 78525
URL: http://svn.boost.org/trac/boost/changeset/78525
Log:
Experiments with the DB. Creating Views to combine single value objects.
Added:
sandbox/icl/libs/xplore/br1/sqlbrowser/MakeTyExDagObjects.sql (contents, props changed)
sandbox/icl/libs/xplore/br1/sqlbrowser/gen/
sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NameGenerator.h (contents, props changed)
Binary files modified:
sandbox/icl/libs/xplore/br1/sqlbrowser/objects1.db
Text files modified:
sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag.sql | 316 ++++-----------------------------------
sandbox/icl/libs/xplore/br1/sqlbrowser/sqlbrowser4.pro | 4
2 files changed, 34 insertions(+), 286 deletions(-)
Added: sandbox/icl/libs/xplore/br1/sqlbrowser/MakeTyExDagObjects.sql
==============================================================================
--- (empty file)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/MakeTyExDagObjects.sql 2012-05-21 03:16:23 EDT (Mon, 21 May 2012)
@@ -0,0 +1,402 @@
+drop table StructType;
+drop table ObjectType;
+drop table RelationType;
+drop table EdgeType;
+drop table Vertex;
+drop table Edge;
+drop table Object;
+drop table Relation;
+
+create table TypeTraits (key integer primary key, name varchar);
+create table ObjectType (key integer primary key, traits integer, name varchar);
+create table EdgeType (key integer primary key, refSourceType integer, refRelationType integer, refTargetType integer, name varchar);
+create table Vertex (key integer primary key, refObjectType integer, refObject integer);
+create table Edge (key integer primary key, refEdgeType integer, refSourceVertex integer, refTargetVertex integer, refObject integer);
+create table Object (key integer primary key);
+create table Attribute (key integer primary key, refObjectType integer, name varchar);
+
+create table IntObject (refObject integer, refAttribute integer, value integer, primary key (refObject, refAttribute));
+create table VarCharObject (refObject integer, refAttribute integer, value varchar, primary key (refObject, refAttribute));
+
+
+
+
+
+-- -----------------------------------------------------------------------------
+insert into TypeTraits values (0, 'atom obj');
+insert into TypeTraits values (1, 'comp obj');
+insert into TypeTraits values (2, 'atom rel');
+
+insert into ObjectType values (0, 0, 'Nil');
+insert into ObjectType values (1, 0, 'a:artist');
+insert into ObjectType values (2, 0, 'a:title');
+insert into ObjectType values (3, 0, 'a:recording');
+insert into ObjectType values (4, 0, 'c:artists');
+insert into ObjectType values (5, 1, 'c:genre');
+insert into ObjectType values (6, 2, 'r:composed');
+insert into ObjectType values (7, 2, 'r:performed');
+insert into ObjectType values (8, 2, 'r:recorded as');
+insert into ObjectType values (9, 2, 'r:located at');
+insert into ObjectType values (10, 0, 'c:album');
+insert into ObjectType values (11, 2, 'r:contains');
+
+insert into EdgeType values (1, 1, 6, 2, 'artist composed title' );
+insert into EdgeType values (2, 1, 7, 3, 'artist performed record' );
+insert into EdgeType values (3, 2, 8, 3, 'title recorded as record');
+insert into EdgeType values (4, 3, 9, 4, 'record located at url' );
+insert into EdgeType values (5, 10, 11, 3, 'album contains record' );
+
+insert into Object values (0);
+
+insert into Object values (1);
+insert into Attribute values (1, "Name");
+insert into VarCharObject values (1, 1, "Charlie Haden");
+
+insert into Object values (2);
+insert into VarCharObject values (2, 1, "Kenny Barron");
+
+insert into Object values (3);
+insert into VarCharObject values (3, 1, "Jonny Green");
+
+insert into Object values (4);
+insert into VarCharObject values (4, 1, "Twighlight Song");
+
+insert into Object values (5);
+insert into VarCharObject values (5, 1, "Body & Soul");
+
+insert into Object values (6);
+insert into VarCharObject values (6, 1, "composed in 1995");
+
+insert into Object values (7);
+insert into VarCharObject values (7, 1, "composed in 1930");
+
+insert into Object values (8);
+insert into VarCharObject values (8, 1, "N&tC: Twighlight Song");
+
+insert into Object values (9);
+insert into VarCharObject values (9, 1, "N&tC: Body & Soul");
+
+insert into Object values (10);
+insert into VarCharObject values (10, 1, "Night & the City");
+
+
+insert into Vertex values (0, 0, 0);
+insert into Vertex values (1, 1, 1);
+insert into Vertex values (2, 1, 2);
+insert into Vertex values (3, 1, 3);
+insert into Vertex values (4, 2, 4);
+insert into Vertex values (5, 2, 5);
+insert into Vertex values (8, 3, 8);
+insert into Vertex values (9, 3, 9);
+insert into Vertex values (10, 3, 10);
+
+insert into Edge values (1, 1, 1, 4, 6);
+-- 2:key 1:composed 3:J.Green 5:Body&Soul 7:comp1930
+insert into Edge values (2, 1, 3, 5, 7);
+
+-- 3:key 2:performed 2:K.Barron 10:N&tC 11:rec1996
+insert into Edge values (3, 2, 2, 10, 11);
+-- 4:key 7:performed 1:C.Haden 10:N&tC
+insert into Edge values (4, 2, 1, 10, 11);
+
+insert into Object values (11);
+insert into VarCharObject values (11, 1, "recoreded 1996");
+
+
+-- -------------------------------------
+-- Dag Related Architecture Linking All
+-- D R A C U L A
+-- -------------------------------------------------------
+-- Dag Related Architecture Using Links All over the place
+-- D R A C U L A
+
+-- -----------------------------------------------------------------------------
+-- Check for EdgeTypes
+create view EdgeTypeCheck as
+select
+ (select ObjectType.name from ObjectType where ObjectType.key = EdgeType.refSourceType) as SrcT,
+ (select ObjectType.name from ObjectType where ObjectType.key = EdgeType.refRelationType) as RelT,
+ ObjectType.name as TrgT,
+ EdgeType.name
+from EdgeType
+ inner join ObjectType on EdgeType.refTargetType = ObjectType.key
+
+-- -----------------------------------------------------------------------------
+-- Named Objects
+create view NamedObjects as
+select Object.key as Obj, VarCharObject.value as Name
+from Object
+ inner join VarCharObject on VarCharObject.refObject = Object.key
+
+-- -----------------------------------------------------------------------------
+-- Named Objects 1:1 Attributes joined horizontally.
+select Object.key as Obj, Name.value as Name, YearOfCrea.value as YoC, Position.value as Pos, Duration.value as Dur
+from Object
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join IntObject as YearOfCrea on YearOfCrea.refObject = Object.key and YearOfCrea.refAttribute = 2
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+ -- and so on ...
+
+-- -----------------------------------------------------------------------------
+-- Named Objects and Types 1:1 Attributes joined horizontally.
+select Object.key as Obj, Vertex.refObjectType as Type, Name.value as Name, YearOfCrea.value as YoC, Position.value as Pos, Duration.value as Dur
+from Object
+ inner join Vertex on Vertex.refObject = Object.key and Type = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join IntObject as YearOfCrea on YearOfCrea.refObject = Object.key and YearOfCrea.refAttribute = 2
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+
+
+-- -----------------------------------------------------------------------------
+-- Vertexes
+select Vertex.key as Vtx, Object.key as Obj, ObjectType.Name as Type , Attribute.Name as Attr,
+ VarCharObject.Value as Name, IntObject.value as Year
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key
+ inner join Attribute on VarCharObject.refAttribute = Attribute.key
+ inner join VarCharObject on VarCharObject.refObject = Object.key
+ left outer join IntObject on IntObject.refObject = Object.key
+
+-- -----------------------------------------------------------------------------
+-- Edge-Objects and associated values
+select Edge.key as Edg, Object.key as Obj, EdgeType.Name as Type , Attribute.Name as Attr,
+ VarCharObject.Value as Name, IntObject.value as Year
+from Object
+ inner join Edge on Edge.refObject = Object.key
+ inner join EdgeType on Edge.refEdgeType = EdgeType.key
+ inner join Attribute on VarCharObject.refAttribute = Attribute.key
+ inner join VarCharObject on VarCharObject.refObject = Object.key
+ left outer join IntObject on IntObject.refObject = Object.key
+
+-- -----------------------------------------------------------------------------
+-- Edges
+select Edge.key as Edg, Object.key as Obj, EdgeType.Name as Type,
+ VarCharObject.value as Name, IntObject.value as Year
+from Object
+ inner join Edge on Edge.refObject = Object.key
+ inner join EdgeType on Edge.refEdgeType = EdgeType.key
+ inner join VarCharObject on VarCharObject.refObject = Object.key
+ left outer join IntObject on IntObject.refObject = Object.key
+
+-- -----------------------------------------------------------------------------
+-- EdgesMinimal
+create view EdgesMinimal as
+select SrcName.Value as Source, EdgeType.Name as Type, TrgName.Value as Target
+from Object
+ inner join Edge on Edge.refObject = Object.key
+ inner join EdgeType on Edge.refEdgeType = EdgeType.key
+ inner join VarCharObject on VarCharObject.refObject = Object.key
+ inner join VarCharObject as SrcName on Edge.refSourceVertex = SrcName.refObject
+ inner join VarCharObject as TrgName on Edge.refTargetVertex = TrgName.refObject
+
+-- -----------------------------------------------------------------------------
+-- EdgesComplete
+select Edge.key as Edg, Object.key as Obj, EdgeType.Name as Type,
+ VarCharObject.value as Name, IntObject.value as Year,
+ Edge.refSourceVertex as Src, SrcName.Value,
+ Edge.refTargetVertex as Trg, TrgName.Value
+from Object
+ inner join Edge on Edge.refObject = Object.key
+ inner join EdgeType on Edge.refEdgeType = EdgeType.key
+ inner join VarCharObject on VarCharObject.refObject = Object.key
+ inner join VarCharObject as SrcName on Edge.refSourceVertex = SrcName.refObject
+ inner join VarCharObject as TrgName on Edge.refTargetVertex = TrgName.refObject
+ left outer join IntObject on IntObject.refObject = Object.key
+
+-- -----------------------------------------------------------------------------
+-- Edges 2.
+--
+select refSourceVertex as Src, VarCharObject.value as Source,
+ (select EdgeType.name from EdgeType where EdgeType.key = Edge.refEdgeType) as Relation,
+ (select IntObject.value
+ from Edge
+ inner join Object on Object.key = Edge.refObject
+ inner join IntObject on Object.key = IntObject.refObject
+ ) as Year,
+ (select VarCharObject.value
+ from Edge
+ inner join Vertex on Vertex.key = Edge.refTargetVertex
+ inner join Object on Object.key = Vertex.refObject
+ inner join VarCharObject on Object.key = VarCharObject.refObject
+ ) as Target
+from Edge
+ inner join Vertex on Vertex.key = Edge.refSourceVertex
+ inner join Object on Object.key = Vertex.refObject
+ inner join VarCharObject on Object.key = VarCharObject.refObject
+
+
+-- -----------------------------------------------------------------------------
+-- Fields
+
+
+-- -----------------------------------------------------------------------------
+-- Artists
+select Vertex.key as Vtx, Object.key as Obj, VarCharObject.value as Name, IntObject.value as YoBirth
+from Vertex
+ inner join Object on Object.key = Vertex.refObject
+ inner join VarCharObject on Object.key = VarCharObject.refObject and VarCharObject.refAttribute = 1
+ inner join IntObject on Object.key = IntObject.refObject and IntObject.refAttribute = 2
+where
+ Vertex.refObjectType = 1
+
+
+
+-- -----------------------------------------------------------------------------
+-- Recordings alias Tracks
+select Object.key as Obj, Vertex.refObjectType as TKey, ObjectType.Name as Type,
+ Name.value as Name, YearOfCrea.value as YoC, Position.value as Pos, Duration.value as Dur
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join IntObject as YearOfCrea on YearOfCrea.refObject = Object.key and YearOfCrea.refAttribute = 2
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+
+
+-- -----------------------------------------------------------------------------
+-- Tracks, incluing album attributes via sub-selects
+select Object.key as Obj, Vertex.refObjectType as Tp, ObjectType.Name as Type,
+ Name.value as Name, YearOfCrea.value as YoC, Position.value as Pos, Duration.value as Dur,
+ (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) as Alb,
+ (select VarCharObject.value from VarCharObject where VarCharObject.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) and VarCharObject.refAttribute = 1) as Album,
+ (select IntObject.value from IntObject where IntObject.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) and IntObject.refAttribute = 2) as YoC
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join IntObject as YearOfCrea on YearOfCrea.refObject = Object.key and YearOfCrea.refAttribute = 2
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+
+-- -----------------------------------------------------------------------------
+-- Tracks, incluing album attributes via joins
+select Object.key as Obj, Vertex.refObjectType as Tp, ObjectType.Name as Type,
+ Name.value as Name, Position.value as Pos, Duration.value as Dur,
+ (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) as Alb,
+ Album_Name.value as Album, Album_Year.value as YoC
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+ left outer join VarCharObject as Album_Name
+ on Album_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Name.refAttribute = 1
+ left outer join IntObject as Album_Year
+ on Album_Year.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Year.refAttribute = 2
+
+
+-- -----------------------------------------------------------------------------
+-- Tracks, including album attributes via joins
+select Object.key as Obj, Vertex.refObjectType as Tp, ObjectType.Name as Type
+ , Name.value as Name, Position.value as Pos, Duration.value as Dur
+ , (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) as Alb
+ , Album_Name.value as Album, Album_Year.value as YoC
+ , Performer_Name.value as Performer
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+ left outer join VarCharObject as Album_Name
+ on Album_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Name.refAttribute = 1
+ left outer join IntObject as Album_Year
+ on Album_Year.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Year.refAttribute = 2
+ left outer join VarCharObject as Performer_Name
+ on Performer_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = 12 and refEdgeType = 2)
+
+
+-- -----------------------------------------------------------------------------
+-- Recordings alias Tracks, incluing album attributes via joins
+(select Edge.refTargetVertex, group_concat(VarCharObject.value) as Performers
+ from Edge
+ inner join VarCharObject on Edge.refSourceVertex = VarCharObject.refObject
+ where refTargetVertex = 12 and refEdgeType = 2)
+
+-- -----------------------------------------------------------------------------
+-- Tracks, including album attributes via joins
+select Object.key as Obj, Vertex.refObjectType as Tp, ObjectType.Name as Type
+ , Name.value as Name, Position.value as Pos, Duration.value as Dur
+ , (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) as Alb
+ , Album_Name.value as Album, Album_Year.value as YoC
+ , Performer_Name.value as Performer
+ , (select Edge.refTargetVertex, group_concat(VarCharObject.value) as Performers
+ from Edge
+ inner join VarCharObject on Edge.refSourceVertex = VarCharObject.refObject
+ where refTargetVertex = 12 and refEdgeType = 2)
+
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+ left outer join VarCharObject as Album_Name
+ on Album_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Name.refAttribute = 1
+ left outer join IntObject as Album_Year
+ on Album_Year.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Year.refAttribute = 2
+ left outer join VarCharObject as Performer_Name
+ on Performer_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = 12 and refEdgeType = 2)
+
+
+-- -----------------------------------------------------------------------------
+-- Tracks, including album attributes via joins
+select Object.key as Obj, Vertex.refObjectType as Tp, ObjectType.Name as Type
+ , Name.value as Name, Position.value as Pos, Duration.value as Dur
+ , (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) as Alb
+ , Album_Name.value as Album, Album_Year.value as YoC
+ , Performer_Name.value as Performer
+ , (select group_concat(VarCharObject.value)
+ from Edge
+ inner join VarCharObject on Edge.refSourceVertex = VarCharObject.refObject
+ where refTargetVertex = (select Edge.refSourceVertex from Edge
+ where refTargetVertex = Object.key and refEdgeType = 5)
+ and refEdgeType = 2
+ ) as AlbumArtists
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+ left outer join VarCharObject as Album_Name
+ on Album_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Name.refAttribute = 1
+ left outer join IntObject as Album_Year
+ on Album_Year.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Year.refAttribute = 2
+ left outer join VarCharObject as Performer_Name
+ on Performer_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = 12 and refEdgeType = 2)
+
+
+-- -----------------------------------------------------------------------------
+-- View Album
+create view Album as
+select Edge.refTargetVertex as Key, group_concat(Performer_name.value) as Artists, YearOfCreation.value as YoC
+ from Vertex
+ inner join VarCharObject as Performer_name on Performer_name.refObject = Vertex.key and Vertex.refObjectType = 1
+ inner join Edge on Edge.refSourceVertex = Vertex.key and Edge.refEdgeType = 2
+ left outer join IntObject as YearOfCreation on YearOfCreation.refObject = Vertex.key
+group by Edge.refTargetVertex
+
+select Edge.refSourceVertex, Edge.refTargetVertex, Album.Album_Artists
+from Edge
+inner join Album on Album.Album_key = Edge.refTargetVertex
+
+-- -----------------------------------------------------------------------------
+-- Utils
+alter table VarCharObject add seqnum integer
+
+
+
Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag.sql
==============================================================================
--- sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag.sql (original)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag.sql 2012-05-21 03:16:23 EDT (Mon, 21 May 2012)
@@ -5,300 +5,48 @@
drop table Vertex;
drop table Edge;
drop table Object;
-drop table Relation;
create table TypeTraits (key integer primary key, name varchar);
create table ObjectType (key integer primary key, traits integer, name varchar);
create table EdgeType (key integer primary key, refSourceType integer, refRelationType integer, refTargetType integer, name varchar);
create table Vertex (key integer primary key, refObjectType integer, refObject integer);
create table Edge (key integer primary key, refEdgeType integer, refSourceVertex integer, refTargetVertex integer, refObject integer);
-create table Object (key integer primary key);
-create table Attribute (key integer primary key, refObjectType integer, name varchar);
+-- Object is hardly a new entity of its own. It builds the disjoint union of
+-- Vertex and Edge Key.
+-- {Object.keys} == {V.keys} + {E,keys} +:set union
+-- {} == {V.keys} & {E,keys} &:set intersection
+create table Object (key integer primary key);
+create table Attribute (key integer primary key, refObjectType integer, name varchar);
create table IntObject (refObject integer, refAttribute integer, value integer, primary key (refObject, refAttribute));
create table VarCharObject (refObject integer, refAttribute integer, value varchar, primary key (refObject, refAttribute));
-//CL?
-create table Field (key integer primary key, refObject integer, refAttribute integer, seqnum integer);
-create table VarCharField (refField integer primary key, value varchar);
-create table IntField (refField integer primary key, value integer);
-
-
-
-
--- -----------------------------------------------------------------------------
-insert into TypeTraits values (0, 'atom obj');
-insert into TypeTraits values (1, 'comp obj');
-insert into TypeTraits values (2, 'atom rel');
-
-insert into ObjectType values (0, 0, 'Nil');
-insert into ObjectType values (1, 0, 'a:artist');
-insert into ObjectType values (2, 0, 'a:title');
-insert into ObjectType values (3, 0, 'a:recording');
-insert into ObjectType values (4, 0, 'c:artists');
-insert into ObjectType values (5, 1, 'c:genre');
-insert into ObjectType values (6, 2, 'r:composed');
-insert into ObjectType values (7, 2, 'r:performed');
-insert into ObjectType values (8, 2, 'r:recorded as');
-insert into ObjectType values (9, 2, 'r:located at');
-insert into ObjectType values (10, 0, 'c:album');
-insert into ObjectType values (11, 2, 'r:contains');
-
-insert into EdgeType values (1, 1, 6, 2, 'artist composed title' );
-insert into EdgeType values (2, 1, 7, 3, 'artist performed record' );
-insert into EdgeType values (3, 2, 8, 3, 'title recorded as record');
-insert into EdgeType values (4, 3, 9, 4, 'record located at url' );
-insert into EdgeType values (5, 10, 11, 3, 'album contains record' );
-
-insert into Object values (0);
-
-insert into Object values (1);
-insert into Attribute values (1, "Name");
-insert into VarCharObject values (1, 1, "Charlie Haden");
-
-insert into Object values (2);
-insert into VarCharObject values (2, 1, "Kenny Barron");
-
-insert into Object values (3);
-insert into VarCharObject values (3, 1, "Jonny Green");
-
-insert into Object values (4);
-insert into VarCharObject values (4, 1, "Twighlight Song");
-
-insert into Object values (5);
-insert into VarCharObject values (5, 1, "Body & Soul");
-
-insert into Object values (6);
-insert into VarCharObject values (6, 1, "composed in 1995");
-
-insert into Object values (7);
-insert into VarCharObject values (7, 1, "composed in 1930");
-
-insert into Object values (8);
-insert into VarCharObject values (8, 1, "N&tC: Twighlight Song");
-
-insert into Object values (9);
-insert into VarCharObject values (9, 1, "N&tC: Body & Soul");
-
-insert into Object values (10);
-insert into VarCharObject values (10, 1, "Night & the City");
-
-
-insert into Vertex values (0, 0, 0);
-insert into Vertex values (1, 1, 1);
-insert into Vertex values (2, 1, 2);
-insert into Vertex values (3, 1, 3);
-insert into Vertex values (4, 2, 4);
-insert into Vertex values (5, 2, 5);
-insert into Vertex values (8, 3, 8);
-insert into Vertex values (9, 3, 9);
-insert into Vertex values (10, 3, 10);
-
-insert into Edge values (1, 1, 1, 4, 6);
--- 2:key 1:composed 3:J.Green 5:Body&Soul 7:comp1930
-insert into Edge values (2, 1, 3, 5, 7);
-
--- 3:key 2:performed 2:K.Barron 10:N&tC 11:rec1996
-insert into Edge values (3, 2, 2, 10, 11);
--- 4:key 7:performed 1:C.Haden 10:N&tC
-insert into Edge values (4, 2, 1, 10, 11);
-
-insert into Object values (11);
-insert into VarCharObject values (11, 1, "recoreded 1996");
-
-
--- -------------------------------------
--- Dag Related Architecture Linking All
--- D R A C U L A
--- -------------------------------------------------------
--- Dag Related Architecture Using Links All over the place
--- D R A C U L A
-
--- -----------------------------------------------------------------------------
--- Check for EdgeTypes
-create view EdgeTypeCheck as
-select
- (select ObjectType.name from ObjectType where ObjectType.key = EdgeType.refSourceType) as SrcT,
- (select ObjectType.name from ObjectType where ObjectType.key = EdgeType.refRelationType) as RelT,
- ObjectType.name as TrgT,
- EdgeType.name
-from EdgeType
- inner join ObjectType on EdgeType.refTargetType = ObjectType.key
-
--- -----------------------------------------------------------------------------
--- Named Objects
-create view NamedObjects as
-select Object.key as Obj, VarCharObject.value as Name
-from Object
- inner join VarCharObject on VarCharObject.refObject = Object.key
-
--- -----------------------------------------------------------------------------
--- Named Objects 1:1 Attributes joined horizontally.
-select Object.key as Obj, Name.value as Name, YearOfCrea.value as YoC, Position.value as Pos, Duration.value as Dur
-from Object
- inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
- left outer join IntObject as YearOfCrea on YearOfCrea.refObject = Object.key and YearOfCrea.refAttribute = 2
- left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
- left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
- -- and so on ...
-
--- -----------------------------------------------------------------------------
--- Named Objects and Types 1:1 Attributes joined horizontally.
-select Object.key as Obj, Vertex.refObjectType as Type, Name.value as Name, YearOfCrea.value as YoC, Position.value as Pos, Duration.value as Dur
-from Object
- inner join Vertex on Vertex.refObject = Object.key and Type = 3
- inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
- left outer join IntObject as YearOfCrea on YearOfCrea.refObject = Object.key and YearOfCrea.refAttribute = 2
- left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
- left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
-
-
--- -----------------------------------------------------------------------------
--- Vertexes
-select Vertex.key as Vtx, Object.key as Obj, ObjectType.Name as Type , Attribute.Name as Attr,
- VarCharObject.Value as Name, IntObject.value as Year
-from Object
- inner join Vertex on Vertex.refObject = Object.key
- inner join ObjectType on Vertex.refObjectType = ObjectType.key
- inner join Attribute on VarCharObject.refAttribute = Attribute.key
- inner join VarCharObject on VarCharObject.refObject = Object.key
- left outer join IntObject on IntObject.refObject = Object.key
-
--- -----------------------------------------------------------------------------
--- Edge-Objects and associated values
-select Edge.key as Edg, Object.key as Obj, EdgeType.Name as Type , Attribute.Name as Attr,
- VarCharObject.Value as Name, IntObject.value as Year
-from Object
- inner join Edge on Edge.refObject = Object.key
- inner join EdgeType on Edge.refEdgeType = EdgeType.key
- inner join Attribute on VarCharObject.refAttribute = Attribute.key
- inner join VarCharObject on VarCharObject.refObject = Object.key
- left outer join IntObject on IntObject.refObject = Object.key
-
--- -----------------------------------------------------------------------------
--- Edges
-select Edge.key as Edg, Object.key as Obj, EdgeType.Name as Type,
- VarCharObject.value as Name, IntObject.value as Year
-from Object
- inner join Edge on Edge.refObject = Object.key
- inner join EdgeType on Edge.refEdgeType = EdgeType.key
- inner join VarCharObject on VarCharObject.refObject = Object.key
- left outer join IntObject on IntObject.refObject = Object.key
-
--- -----------------------------------------------------------------------------
--- EdgesMinimal
-create view EdgesMinimal as
-select SrcName.Value as Source, EdgeType.Name as Type, TrgName.Value as Target
-from Object
- inner join Edge on Edge.refObject = Object.key
- inner join EdgeType on Edge.refEdgeType = EdgeType.key
- inner join VarCharObject on VarCharObject.refObject = Object.key
- inner join VarCharObject as SrcName on Edge.refSourceVertex = SrcName.refObject
- inner join VarCharObject as TrgName on Edge.refTargetVertex = TrgName.refObject
-
--- -----------------------------------------------------------------------------
--- EdgesComplete
-select Edge.key as Edg, Object.key as Obj, EdgeType.Name as Type,
- VarCharObject.value as Name, IntObject.value as Year,
- Edge.refSourceVertex as Src, SrcName.Value,
- Edge.refTargetVertex as Trg, TrgName.Value
-from Object
- inner join Edge on Edge.refObject = Object.key
- inner join EdgeType on Edge.refEdgeType = EdgeType.key
- inner join VarCharObject on VarCharObject.refObject = Object.key
- inner join VarCharObject as SrcName on Edge.refSourceVertex = SrcName.refObject
- inner join VarCharObject as TrgName on Edge.refTargetVertex = TrgName.refObject
- left outer join IntObject on IntObject.refObject = Object.key
-
--- -----------------------------------------------------------------------------
--- Edges 2.
---
-select refSourceVertex as Src, VarCharObject.value as Source,
- (select EdgeType.name from EdgeType where EdgeType.key = Edge.refEdgeType) as Relation,
- (select IntObject.value
- from Edge
- inner join Object on Object.key = Edge.refObject
- inner join IntObject on Object.key = IntObject.refObject
- ) as Year,
- (select VarCharObject.value
- from Edge
- inner join Vertex on Vertex.key = Edge.refTargetVertex
- inner join Object on Object.key = Vertex.refObject
- inner join VarCharObject on Object.key = VarCharObject.refObject
- ) as Target
-from Edge
- inner join Vertex on Vertex.key = Edge.refSourceVertex
- inner join Object on Object.key = Vertex.refObject
- inner join VarCharObject on Object.key = VarCharObject.refObject
-
-
--- -----------------------------------------------------------------------------
--- Fields
-
-
--- -----------------------------------------------------------------------------
--- Artists
-select Vertex.key as Vtx, Object.key as Obj, VarCharObject.value as Name, IntObject.value as YoBirth
-from Vertex
- inner join Object on Object.key = Vertex.refObject
- inner join VarCharObject on Object.key = VarCharObject.refObject and VarCharObject.refAttribute = 1
- inner join IntObject on Object.key = IntObject.refObject and IntObject.refAttribute = 2
-where
- Vertex.refObjectType = 1
-
-
-
--- -----------------------------------------------------------------------------
--- Recordings alias Tracks
-select Object.key as Obj, Vertex.refObjectType as TKey, ObjectType.Name as Type,
- Name.value as Name, YearOfCrea.value as YoC, Position.value as Pos, Duration.value as Dur
-from Object
- inner join Vertex on Vertex.refObject = Object.key
- inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
- inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
- left outer join IntObject as YearOfCrea on YearOfCrea.refObject = Object.key and YearOfCrea.refAttribute = 2
- left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
- left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
-
-
--- -----------------------------------------------------------------------------
--- Recordings alias Tracks, incluing album attributes via sub-selects
-select Object.key as Obj, Vertex.refObjectType as Tp, ObjectType.Name as Type,
- Name.value as Name, YearOfCrea.value as YoC, Position.value as Pos, Duration.value as Dur,
- (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) as Alb,
- (select VarCharObject.value from VarCharObject where VarCharObject.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) and VarCharObject.refAttribute = 1) as Album,
- (select IntObject.value from IntObject where IntObject.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) and IntObject.refAttribute = 2) as YoC
-from Object
- inner join Vertex on Vertex.refObject = Object.key
- inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
- inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
- left outer join IntObject as YearOfCrea on YearOfCrea.refObject = Object.key and YearOfCrea.refAttribute = 2
- left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
- left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
-
--- -----------------------------------------------------------------------------
--- Recordings alias Tracks, incluing album attributes via joins
-select Object.key as Obj, Vertex.refObjectType as Tp, ObjectType.Name as Type,
- Name.value as Name, Position.value as Pos, Duration.value as Dur,
- Album1.value as Album, Album2.value as YoC
-from Object
- inner join Vertex on Vertex.refObject = Object.key
- inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
- inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
- left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
- left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
- left outer join VarCharObject as Album1
- on Album1.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
- and Album1.refAttribute = 1
- left outer join IntObject as Album2
- on Album2.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
- and Album2.refAttribute = 2
-
-
--- -----------------------------------------------------------------------------
--- Utils
-alter table VarCharObject add seqnum integer
-
+-- The creation sequence and systematics for generating example entities:
+
+-- To add an album with titles, recordings and locations
+-- we proceed from elemetal, independent, not refering entities
+-- to those that contain references to other entities
+
+-- Example Album: Abbey Road.
+
+-- add genre ...
+-- add Beat
+-- add Obj, Vtx, Name
+
+-- add artists
+-- add John Lennon
+-- add Obj, Vtx, Name, [...]
+-- add Paul McCartney
+-- add Geroge Harrison
+-- add Ringo Starr
+
+-- add titles
+-- add Come Together
+-- add Obj, Vtx, Name, YoC
+-- add Something
+-- ...
+-- add artist composed title relation
+--
Added: sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NameGenerator.h
==============================================================================
--- (empty file)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NameGenerator.h 2012-05-21 03:16:23 EDT (Mon, 21 May 2012)
@@ -0,0 +1,51 @@
+/****************************************************************************
+**
+**
+****************************************************************************/
+
+#pragma once
+
+
+#include <boost/random/mersenne_twister.hpp>
+#include <boost/random/uniform_int_distribution.hpp>
+
+namespace gen
+{
+
+
+static const char* sylable_begin[] =
+{
+ , "An"
+};
+
+static const char* sylable_mid[] =
+{
+ , "bel"
+ , "dre"
+ , "dri"
+ , "ja"
+ , "na"
+ , "nusch"
+};
+
+static const char* sylable_female_end[] =
+{
+ , "ka"
+ , "le"
+ , "na"
+};
+
+
+template<class Accessor>
+struct SelectorTraits
+{
+ typedef typename Accessor::size_type size_type;
+ typedef typename Accessor::tQuery tQuery;
+ typedef typename Accessor::iterator const_iterator;
+
+ static const_iterator begin(const Accessor& accessor);
+ static const_iterator end(const Accessor& accessor);
+ static size_type select(const tQuery&);
+};
+
+} // namespace data
Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/objects1.db
==============================================================================
Binary files. No diff available.
Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/sqlbrowser4.pro
==============================================================================
--- sandbox/icl/libs/xplore/br1/sqlbrowser/sqlbrowser4.pro (original)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/sqlbrowser4.pro 2012-05-21 03:16:23 EDT (Mon, 21 May 2012)
@@ -3,8 +3,8 @@
QT += sql
-#INCLUDEPATH += /cygwin/home/jofa/dev/boost/branches/release
-INCLUDEPATH += C:\NIBuild\3rdparty\boost-1.48.0
+INCLUDEPATH += /cygwin/home/jofa/dev/boost/branches/release
+#INCLUDEPATH += C:\NIBuild\3rdparty\boost-1.48.0
HEADERS = browser.h connectionwidget.h qsqlconnectiondialog.h \
exttableview.h \
Boost-Commit list run by bdawes at acm.org, david.abrahams at rcn.com, gregod at cs.rpi.edu, cpdaniel at pacbell.net, john at johnmaddock.co.uk