Boost logo

Boost-Commit :

Subject: [Boost-commit] svn:boost r78787 - sandbox/icl/libs/xplore/br1/sqlbrowser
From: afojgo_at_[hidden]
Date: 2012-06-01 03:41:22


Author: jofaber
Date: 2012-06-01 03:41:22 EDT (Fri, 01 Jun 2012)
New Revision: 78787
URL: http://svn.boost.org/trac/boost/changeset/78787

Log:
Optimizing Db via indixes.
Text files modified:
   sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag.sql | 37 +++++++++++++++++++++++++++++++++++++
   1 files changed, 37 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-06-01 03:41:22 EDT (Fri, 01 Jun 2012)
@@ -652,3 +652,40 @@
   -- ObjectType(Artist)
   left outer join ObjectType as ConstArtistType on 21 = ConstArtistType.key
   
+
+-- -----------------------------------------------------------------------------
+-- View Track
+-- create view Track as
+select
+ Recording.key as TrackId, Recording.Name
+, Recording.Duration, Recording.Genre, Recording.BPM, Recording.Label, Recording.Comment
+, Recording.Playcount, Recording.Rating
+, ConstAlbumType.Name as AlbType, Album.key as AlbId, Album.Name as Album, Album.Year 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 ---------------------------------------------------------------------
+ inner join Recording on Recording.key = Vertex.key
+ -- Album ---------------------------------------------------------------------
+ left outer join Edge as MotherAlbum on MotherAlbum.refTargetVertex = Vertex.key
+ and MotherAlbum.refEdgeType = 5 -- 5: Album contains Recording
+ left outer join Album on Album.key = MotherAlbum.refSourceVertex
+ left outer join IntObject as AlbumYear on AlbumYear.refObject = MotherAlbum.refSourceVertex
+ and AlbumYear.refAttribute = 31
+ -- 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
+


Boost-Commit list run by bdawes at acm.org, david.abrahams at rcn.com, gregod at cs.rpi.edu, cpdaniel at pacbell.net, john at johnmaddock.co.uk