[Boost-commit] svn:boost r78618 - in sandbox/icl/libs/xplore/br1/sqlbrowser: . gen

Subject: [Boost-commit] svn:boost r78618 - in sandbox/icl/libs/xplore/br1/sqlbrowser: . gen
From: afojgo_at_[hidden]
Date: 2012-05-25 11:40:43

Author: jofaber
Date: 2012-05-25 11:40:42 EDT (Fri, 25 May 2012)
New Revision: 78618
URL: http://svn.boost.org/trac/boost/changeset/78618

Log:
Optimizing Db via indixes.
Text files modified: 
   sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag.sql |    39 ++++++++++++++++++++++++++++++++++++++- 
   sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.cpp   |     4 ++--                                    
   2 files changed, 40 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-25 11:40:42 EDT (Fri, 25 May 2012)
@@ -459,7 +459,7 @@
   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
+                                         and MotherTitle.refEdgeType = 3 -- 3: Title recorded as Recording
   left outer join VarCharObject as TitleName on TitleName.refObject = MotherTitle.refSourceVertex
   left outer join IntObject     as TitleYear on TitleYear.refObject = MotherTitle.refSourceVertex
   
@@ -489,3 +489,40 @@
 create index IdxTargetVertex on Edge (refTargetVertex)
 create index IdxEdgeType     on Edge (refEdgeType)
 
+
+
+-- -----------------------------------------------------------------------------
+-- -----------------------------------------------------------------------------
+-- View Track
+-- create view Track as
+select Vertex.key as TrackId
+, TrackName.value as Name
+, Duration.value as Dur, Genre.value as Genre, BPM.value as BPM 
+, MotherAlbum.refSourceVertex as AlbId, AlbumName.value as Album, AlbumYear.value as AlbYr
+, MotherTitle.refSourceVertex as TitId, TitleName.value as Title, TitleYear.value as TitYr
+, ComposerOfTitle.refSourceVertex as CompId, ComposerName.value as Composer, ComposerYoBirth.value as Birth
+from Vertex
+  -- Track
+  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
+  -- Album
+  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
+                                                and AlbumYear.refAttribute = 31
+  -- Title
+  left outer join Edge as MotherTitle on     MotherTitle.refTargetVertex = Vertex.key  
+                                         and MotherTitle.refEdgeType = 3 -- 3: Title recorded as Recording
+  left outer join VarCharObject as TitleName on TitleName.refObject = MotherTitle.refSourceVertex
+  left outer join IntObject     as TitleYear on TitleYear.refObject = MotherTitle.refSourceVertex
+  -- Composer
+  left outer join Edge as ComposerOfTitle       on     ComposerOfTitle.refTargetVertex = MotherTitle.refSourceVertex
+  left outer join VarCharObject as ComposerName on     ComposerName.refObject    = ComposerOfTitle.refSourceVertex
+  left outer join IntObject as ComposerYoBirth  on     ComposerYoBirth.refObject = ComposerOfTitle.refSourceVertex 
+                                                   and ComposerYoBirth.refAttribute = 31 
+  

Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.cpp
==============================================================================
--- sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.cpp	(original)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.cpp	2012-05-25 11:40:42 EDT (Fri, 25 May 2012)
@@ -15,17 +15,17 @@
 
 void DbGenerator::configure()
 {
+    /*
     m_iArtists     =  10;
     m_iTitles      = 100;
     m_iAlbums      =  50;
     m_iRecordings  = 200;
+    */
 
-    /*
     m_iArtists     =   5000;
     m_iTitles      =  50000;
     m_iAlbums      =  25000;
     m_iRecordings  = 100000;
-    */
 }
 
 void DbGenerator::clear()