[Boost-commit] svn:boost r78505 - sandbox/icl/libs/xplore/br1/sqlbrowser

Subject: [Boost-commit] svn:boost r78505 - sandbox/icl/libs/xplore/br1/sqlbrowser
From: afojgo_at_[hidden]
Date: 2012-05-18 11:50:13

Author: jofaber
Date: 2012-05-18 11:50:12 EDT (Fri, 18 May 2012)
New Revision: 78505
URL: http://svn.boost.org/trac/boost/changeset/78505

Log:
Modifications on the prototype db.
Binary files modified: 
   sandbox/icl/libs/xplore/br1/sqlbrowser/objects1.db
Text files modified: 
   sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag.sql |    59 +++++++++++++++++++++++++++++++++++++-- 
   1 files changed, 56 insertions(+), 3 deletions(-)

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-18 11:50:12 EDT (Fri, 18 May 2012)
@@ -141,12 +141,17 @@
   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 1:1 Attributes joined horizontally.
-select Object.key as Obj, VarCharObject.refAttribute as Attr, VarCharObject.value as Value
+-- 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 VarCharObject on VarCharObject.refObject = Object.key
+  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  
 
   
 -- -----------------------------------------------------------------------------
@@ -242,7 +247,55 @@
 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

Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/objects1.db
==============================================================================
Binary files. No diff available.