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

Subject: [Boost-commit] svn:boost r78749 - in sandbox/icl/libs/xplore/br1/sqlbrowser: . gen
From: afojgo_at_[hidden]
Date: 2012-05-29 12:25:23

Author: jofaber
Date: 2012-05-29 12:25:14 EDT (Tue, 29 May 2012)
New Revision: 78749
URL: http://svn.boost.org/trac/boost/changeset/78749

Log:
Optimizing Db via indixes.
Text files modified: 
   sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag.sql |    28 +++++++++++++++++--------               
   sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.cpp   |     9 ++++++++                                
   sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.h     |    43 ++++++++++++++++++++------------------- 
   3 files changed, 50 insertions(+), 30 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-29 12:25:14 EDT (Tue, 29 May 2012)
@@ -495,34 +495,44 @@
 -- -----------------------------------------------------------------------------
 -- 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
+select 
+  ConstTrackType.Name as TrackType, Vertex.key as TrackId, TrackName.value as Name
+, Duration.value as Dur, Genre.value as Genre, BPM.value as BPM, Label.value as Label, Comment.value as Comment  
+, ConstAlbumType.Name as AlbType, MotherAlbum.refSourceVertex as AlbId, AlbumName.value as Album, AlbumYear.value as AlbYr
+, ConstTitleType.Name as TitType, MotherTitle.refSourceVertex as TitId, TitleName.value as Title, TitleYear.value as TitYr
+, ConstArtistType.Name as ArtstType, ComposerOfTitle.refSourceVertex as CompId, ComposerName.value as Composer, ComposerYoBirth.value as Birth
 from Vertex
-  -- Track
+  -- 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 VarCharObject as Label     on Label.refObject    = Vertex.key and Label.refAttribute    =  4
+  left outer join VarCharObject as Comment   on Comment.refObject  = Vertex.key and Comment.refAttribute  =  5
   left outer join IntObject     as BPM       on BPM.refObject      = Vertex.key and BPM.refAttribute      = 33
-  -- Album
+  -- ObjectType(Track)
+  left outer join ObjectType as ConstTrackType on 23 = ConstTrackType.key
+  -- 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
+  -- ObjectType(Album)
+  left outer join ObjectType as ConstAlbumType on 30 = ConstAlbumType.key
+  -- 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
+  -- ObjectType(Title)
+  left outer join ObjectType as ConstTitleType on 22 = ConstTitleType.key
   -- 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 
+  -- ObjectType(Artist)
+  left outer join ObjectType as ConstArtistType on 21 = ConstArtistType.key
   

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-29 12:25:14 EDT (Tue, 29 May 2012)
@@ -126,6 +126,15 @@
     exec("create table IntObject (refObject integer, refAttribute integer, value integer, primary key (refObject, refAttribute))");
 }
 
+void DbGenerator::generateIndexes()
+{
+    // Sqlite generates indexes on primary key automatically.
+    // In addition we add
+    exec("create index IdxSourceVertex on Edge (refSourceVertex)");
+    exec("create index IdxTargetVertex on Edge (refTargetVertex)");
+    //MEMO create index IdxEdgeType on Edge (refEdgeType) makes things MORE SLOWLY!
+}
+
 void DbGenerator::generateTypeTraits()
 {
     exec("insert into TypeTraits values (0, 'atom obj')");

Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.h
==============================================================================
--- sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.h	(original)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.h	2012-05-29 12:25:14 EDT (Tue, 29 May 2012)
@@ -34,20 +34,20 @@
     typedef tInterKeySet::interval_type    tInterval;
 
     enum {
-        a_text       =  1
-      , a_integer    =  2
-      , a_real       =  3
-      , a_artist     = 21
-      , a_title      = 22
-      , a_recording  = 23
-      , c_artists    = 24
-      , c_genre      = 25
-      , r_composed   = 26
-      , r_performed  = 27
-      , r_recorded   = 28
-      , r_located_at = 29
-      , c_album      = 30
-      , r_contains   = 31
+          a_text       =  1
+        , a_integer    =  2
+        , a_real       =  3
+        , a_artist     = 21
+        , a_title      = 22
+        , a_recording  = 23
+        , c_artists    = 24
+        , c_genre      = 25
+        , r_composed   = 26
+        , r_performed  = 27
+        , r_recorded   = 28
+        , r_located_at = 29
+        , c_album      = 30
+        , r_contains   = 31
     };
 
     enum {
@@ -70,16 +70,16 @@
     };
 
     enum {
-        R_artist_composed_title    = 1
-      , R_artist_performed_record  = 2
-      , R_title_recorded_as_record = 3
-      , R_record_located_at_url    = 4
-      , R_album_contains_record    = 5
+          R_artist_composed_title    = 1
+        , R_artist_performed_record  = 2
+        , R_title_recorded_as_record = 3
+        , R_record_located_at_url    = 4
+        , R_album_contains_record    = 5
     };
 
     enum {
-        minSyllables = 2
-      , maxSyllables = 5
+          minSyllables = 2
+        , maxSyllables = 5
     };
 
     DbGenerator(const QSqlDatabase& db):
@@ -93,6 +93,7 @@
     void clearDb();
     bool generate();
     void generateTables();
+    void generateIndexes();
     void generateTypeData();
     void generateTypeViews();