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

Subject: [Boost-commit] svn:boost r78612 - sandbox/icl/libs/xplore/br1/sqlbrowser
From: afojgo_at_[hidden]
Date: 2012-05-25 09:45:21

Author: jofaber
Date: 2012-05-25 09:45:19 EDT (Fri, 25 May 2012)
New Revision: 78612
URL: http://svn.boost.org/trac/boost/changeset/78612

Log:
Critiacal Query.
Text files modified: 
   sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag.sql |    51 ++++++++++++++++++++++++++++++++++++++++
   1 files changed, 51 insertions(+), 0 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-25 09:45:19 EDT (Fri, 25 May 2012)
@@ -438,3 +438,54 @@
   left outer join VarCharObject as TitleName on TitleName.refObject = Edge.refTargetVertex
 group by refTargetVertex
 order by Title
+
+
+-- -----------------------------------------------------------------------------
+-- -----------------------------------------------------------------------------
+-- View Track
+-- create view Track as
+select Vertex.key as TrackId
+, TrackName.value as Name
+, MotherAlbum.refSourceVertex as AlbId, AlbumName.value as Album, AlbumYear.value as AlbYr
+, MotherTitle.refSourceVertex as TitId, TitleName.value as Title, TitleYear.value as TitYr
+, Duration.value as Dur 
+, Genre.value as Genre 
+from Vertex
+  inner join VarCharObject as TrackName on     TrackName.refObject = Vertex.key 
+                                           and TrackName.refAttribute = 1
+                                           and Vertex.refObjectType = 23 -- 23: Recording (aka. Track)
+  left outer join Edge as MotherAlbum on     MotherAlbum.refTargetVertex = Vertex.key  
+                                         and MotherAlbum.refEdgeType = 5 -- 5: Album contains Recording
+  left outer join VarCharObject as AlbumName on AlbumName.refObject = MotherAlbum.refSourceVertex
+  left outer join IntObject     as AlbumYear on AlbumYear.refObject = MotherAlbum.refSourceVertex
+  left outer join Edge as MotherTitle on     MotherTitle.refTargetVertex = Vertex.key  
+                                         and MotherTitle.refEdgeType = 3 -- 3: Title recoreded as Recording
+  left outer join VarCharObject as TitleName on TitleName.refObject = MotherTitle.refSourceVertex
+  left outer join IntObject     as TitleYear on TitleYear.refObject = MotherTitle.refSourceVertex
+  
+  left outer join VarCharObject as Duration  on Duration.refObject = Vertex.key and Duration.refAttribute = 2
+  left outer join VarCharObject as Genre     on Genre.refObject = Vertex.key and Genre.refAttribute = 3
+  
+-- -----------------------------------------------------------------------------
+select Vertex.key as TrackId
+, TrackName.value as Name
+, Duration.value as Dur 
+, Genre.value as Genre 
+, BPM.value as BPM 
+, MotherAlbum.refSourceVertex as AlbumKey
+from Vertex
+  inner join VarCharObject as TrackName on     TrackName.refObject = Vertex.key 
+                                           and TrackName.refAttribute = 1
+                                           and Vertex.refObjectType = 23 -- 23: Recording (aka. Track)
+  left outer join VarCharObject as Duration  on Duration.refObject = Vertex.key and Duration.refAttribute = 2
+  left outer join VarCharObject as Genre     on Genre.refObject = Vertex.key and Genre.refAttribute = 3
+  left outer join IntObject     as BPM       on BPM.refObject = Vertex.key and BPM.refAttribute = 33
+  left outer join Edge as MotherAlbum on     MotherAlbum.refTargetVertex = Vertex.key  
+                                         and MotherAlbum.refEdgeType = 5 -- 5: Album contains Recording
+  
+
+-- -----------------------------------------------------------------------------
+create index IdxSourceVertex on Edge (refSourceVertex)
+create index IdxTargetVertex on Edge (refTargetVertex)
+create index IdxEdgeType     on Edge (refEdgeType)
+