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()