|
|
Boost-Commit : |
Subject: [Boost-commit] svn:boost r78541 - in sandbox/icl/libs/xplore/br1/sqlbrowser: . gen
From: afojgo_at_[hidden]
Date: 2012-05-22 12:43:40
Author: jofaber
Date: 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
New Revision: 78541
URL: http://svn.boost.org/trac/boost/changeset/78541
Log:
Worked on Db Generator.
Added:
sandbox/icl/libs/xplore/br1/sqlbrowser/Generatic1.db (contents, props changed)
sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag_cpp.sql (contents, props changed)
sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.cpp (contents, props changed)
sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.h (contents, props changed)
sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NumberGenerator.cpp (contents, props changed)
sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NumberGenerator.h (contents, props changed)
Text files modified:
sandbox/icl/libs/xplore/br1/sqlbrowser/MakeTyExDagObjects.sql | 12 ++++++++++++
sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag.sql | 9 +++++----
sandbox/icl/libs/xplore/br1/sqlbrowser/browser.cpp | 37 +++++++++++++++++++++++++++++++++----
sandbox/icl/libs/xplore/br1/sqlbrowser/browser.h | 5 ++++-
sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NameGenerator.cpp | 31 ++++++++++++++++++++++++-------
sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NameGenerator.h | 9 +++++++--
sandbox/icl/libs/xplore/br1/sqlbrowser/qsqlconnectiondialog.ui | 2 +-
sandbox/icl/libs/xplore/br1/sqlbrowser/sqlbrowser4.pro | 8 ++++++--
8 files changed, 92 insertions(+), 21 deletions(-)
Added: sandbox/icl/libs/xplore/br1/sqlbrowser/Generatic1.db
==============================================================================
Binary file. No diff available.
Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/MakeTyExDagObjects.sql
==============================================================================
--- sandbox/icl/libs/xplore/br1/sqlbrowser/MakeTyExDagObjects.sql (original)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/MakeTyExDagObjects.sql 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -22,6 +22,11 @@
create table IntObject (refObject integer, refAttribute integer, value integer, primary key (refObject, refAttribute));
create table VarCharObject (refObject integer, refAttribute integer, value varchar, primary key (refObject, refAttribute));
+-- -----------------------------------------------------------------------------
+-- Utilities
+-- sqlite_master : Contains the DB Schema Tables, Views and Indices
+select * from sqlite_master
+
-- The creation sequence and systematics for generating example entities:
@@ -105,3 +110,10 @@
-- = Edge(31,2,16,21,31)
+ _ _
+ / | | | Musikhaus Thomann Cyberteam
+ / |__ | |___ ___ ____ ___ ____ _ ___ _ ___ ________________
+/_ __|| __ \/ \| _ \/_ \|__ \ | / \| / \ Tel: 09546-9223-0
+ | | | / \/ :-) \ | \ / | | __| | | /| || /| | Fax: 09546-6774
+ | |__ | | |\ / | | | | |/ <> | | | | || | | | info_at_[hidden]
+ \___||_| |_\___/|_| |_| |_|\_____\|_| |_||_| |_| www.thomann.de
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-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -1,11 +1,12 @@
-drop table StructType;
+drop table TypeTraits;
drop table ObjectType;
-drop table RelationType;
drop table EdgeType;
drop table Vertex;
drop table Edge;
drop table Object;
-drop table Relation;
+drop table Attribute;
+drop table VarCharObject;
+drop table IntObject;
create table TypeTraits (key integer primary key, name varchar);
create table ObjectType (key integer primary key, traits integer, name varchar);
@@ -15,8 +16,8 @@
create table Object (key integer primary key);
create table Attribute (key integer primary key, refObjectType integer, name varchar);
-create table IntObject (refObject integer, refAttribute integer, value integer, primary key (refObject, refAttribute));
create table VarCharObject (refObject integer, refAttribute integer, value varchar, primary key (refObject, refAttribute));
+create table IntObject (refObject integer, refAttribute integer, value integer, primary key (refObject, refAttribute));
Added: sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag_cpp.sql
==============================================================================
--- (empty file)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/TypeExtensibleDag_cpp.sql 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -0,0 +1,419 @@
+drop table TypeTraits;
+drop table ObjectType;
+drop table EdgeType;
+drop table Vertex;
+drop table Edge;
+drop table Object;
+drop table Attribute;
+drop table VarCharObject;
+drop table IntObject;
+
+exec("create table TypeTraits (key integer primary key, name varchar)");
+exec("create table ObjectType (key integer primary key, traits integer, name varchar)");
+exec("create table EdgeType (key integer primary key, refSourceType integer, refRelationType integer, refTargetType integer, name varchar)");
+exec("create table Vertex (key integer primary key, refObjectType integer, refObject integer)");
+exec("create table Edge (key integer primary key, refEdgeType integer, refSourceVertex integer, refTargetVertex integer, refObject integer)");
+exec("create table Object (key integer primary key)");
+exec("create table Attribute (key integer primary key, refObjectType integer, name varchar)");
+exec("create table VarCharObject (refObject integer, refAttribute integer, value varchar, primary key (refObject, refAttribute))");
+exec("create table IntObject (refObject integer, refAttribute integer, value integer, primary key (refObject, refAttribute))");
+
+-- -----------------------------------------------------------------------------
+exec("insert into TypeTraits values (0, 'atom obj')");
+exec("insert into TypeTraits values (1, 'comp obj')");
+exec("insert into TypeTraits values (2, 'atom rel')");
+
+-- -----------------------------------------------------------------------------
+exec("insert into ObjectType values (1, 0, 'a:text')");
+exec("insert into ObjectType values (2, 0, 'a:integer')");
+exec("insert into ObjectType values (3, 0, 'a:real')");
+
+exec("insert into ObjectType values (21, 0, 'a:artist')");
+exec("insert into ObjectType values (22, 0, 'a:title')");
+exec("insert into ObjectType values (23, 0, 'a:recording')");
+exec("insert into ObjectType values (24, 0, 'c:artists')");
+exec("insert into ObjectType values (25, 1, 'c:genre')");
+exec("insert into ObjectType values (26, 2, 'r:composed')");
+exec("insert into ObjectType values (27, 2, 'r:performed')");
+exec("insert into ObjectType values (28, 2, 'r:recorded as')");
+exec("insert into ObjectType values (29, 2, 'r:located at')");
+exec("insert into ObjectType values (30, 0, 'c:album')");
+exec("insert into ObjectType values (31, 2, 'r:contains')");
+
+exec("insert into EdgeType values (1, 21, 26, 22, 'artist composed title' )");
+exec("insert into EdgeType values (2, 21, 27, 23, 'artist performed record' )");
+exec("insert into EdgeType values (3, 22, 28, 23, 'title recorded as record')");
+exec("insert into EdgeType values (4, 23, 29, 24, 'record located at url' )");
+exec("insert into EdgeType values (5, 30, 31, 23, 'album contains record' )");
+
+exec("insert into Attribute values (1, 1, 'Name')" );
+exec("insert into Attribute values (2, 1, 'Duration')");
+exec("insert into Attribute values (31, 2, 'Year')" );
+exec("insert into Attribute values (32, 2, 'Pos')" );
+
+
+insert into Object values (0);
+
+insert into Object values (1);
+insert into Attribute values (1, "Name");
+insert into VarCharObject values (1, 1, "Charlie Haden");
+
+insert into Object values (2);
+insert into VarCharObject values (2, 1, "Kenny Barron");
+
+insert into Object values (3);
+insert into VarCharObject values (3, 1, "Jonny Green");
+
+insert into Object values (4);
+insert into VarCharObject values (4, 1, "Twighlight Song");
+
+insert into Object values (5);
+insert into VarCharObject values (5, 1, "Body & Soul");
+
+insert into Object values (6);
+insert into VarCharObject values (6, 1, "composed in 1995");
+
+insert into Object values (7);
+insert into VarCharObject values (7, 1, "composed in 1930");
+
+insert into Object values (8);
+insert into VarCharObject values (8, 1, "N&tC: Twighlight Song");
+
+insert into Object values (9);
+insert into VarCharObject values (9, 1, "N&tC: Body & Soul");
+
+insert into Object values (10);
+insert into VarCharObject values (10, 1, "Night & the City");
+
+
+insert into Vertex values (0, 0, 0);
+insert into Vertex values (1, 1, 1);
+insert into Vertex values (2, 1, 2);
+insert into Vertex values (3, 1, 3);
+insert into Vertex values (4, 2, 4);
+insert into Vertex values (5, 2, 5);
+insert into Vertex values (8, 3, 8);
+insert into Vertex values (9, 3, 9);
+insert into Vertex values (10, 3, 10);
+
+insert into Edge values (1, 1, 1, 4, 6);
+-- 2:key 1:composed 3:J.Green 5:Body&Soul 7:comp1930
+insert into Edge values (2, 1, 3, 5, 7);
+
+-- 3:key 2:performed 2:K.Barron 10:N&tC 11:rec1996
+insert into Edge values (3, 2, 2, 10, 11);
+-- 4:key 7:performed 1:C.Haden 10:N&tC
+insert into Edge values (4, 2, 1, 10, 11);
+
+insert into Object values (11);
+insert into VarCharObject values (11, 1, "recoreded 1996");
+
+
+-- -------------------------------------
+-- Dag Related Architecture Linking All
+-- D R A C U L A
+-- -------------------------------------------------------
+-- Dag Related Architecture Using Links All over the place
+-- D R A C U L A
+
+-- -----------------------------------------------------------------------------
+-- Check for EdgeTypes
+"create view EdgeTypeCheck as "
+"select "
+" (select ObjectType.name from ObjectType where ObjectType.key = EdgeType.refSourceType) as SrcT, "
+" (select ObjectType.name from ObjectType where ObjectType.key = EdgeType.refRelationType) as RelT, "
+" ObjectType.name as TrgT, "
+" EdgeType.name "
+"from EdgeType "
+" inner join ObjectType on EdgeType.refTargetType = ObjectType.key"
+
+-- -----------------------------------------------------------------------------
+-- Named Objects
+create view NamedObjects as
+select Object.key as Obj, VarCharObject.value as Name
+from Object
+ inner join VarCharObject on VarCharObject.refObject = Object.key
+
+-- -----------------------------------------------------------------------------
+-- Named Objects 1:1 Attributes joined horizontally.
+select Object.key as Obj, Name.value as Name, YearOfCrea.value as YoC, Position.value as Pos, Duration.value as Dur
+from Object
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join IntObject as YearOfCrea on YearOfCrea.refObject = Object.key and YearOfCrea.refAttribute = 2
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+ -- and so on ...
+
+-- -----------------------------------------------------------------------------
+-- Named Objects and Types 1:1 Attributes joined horizontally.
+select Object.key as Obj, Vertex.refObjectType as Type, Name.value as Name, YearOfCrea.value as YoC, Position.value as Pos, Duration.value as Dur
+from Object
+ inner join Vertex on Vertex.refObject = Object.key and Type = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join IntObject as YearOfCrea on YearOfCrea.refObject = Object.key and YearOfCrea.refAttribute = 2
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+
+
+-- -----------------------------------------------------------------------------
+-- Vertexes
+select Vertex.key as Vtx, Object.key as Obj, ObjectType.Name as Type , Attribute.Name as Attr,
+ VarCharObject.Value as Name, IntObject.value as Year
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key
+ inner join Attribute on VarCharObject.refAttribute = Attribute.key
+ inner join VarCharObject on VarCharObject.refObject = Object.key
+ left outer join IntObject on IntObject.refObject = Object.key
+
+-- -----------------------------------------------------------------------------
+-- Edge-Objects and associated values
+select Edge.key as Edg, Object.key as Obj, EdgeType.Name as Type , Attribute.Name as Attr,
+ VarCharObject.Value as Name, IntObject.value as Year
+from Object
+ inner join Edge on Edge.refObject = Object.key
+ inner join EdgeType on Edge.refEdgeType = EdgeType.key
+ inner join Attribute on VarCharObject.refAttribute = Attribute.key
+ inner join VarCharObject on VarCharObject.refObject = Object.key
+ left outer join IntObject on IntObject.refObject = Object.key
+
+-- -----------------------------------------------------------------------------
+-- Edges
+select Edge.key as Edg, Object.key as Obj, EdgeType.Name as Type,
+ VarCharObject.value as Name, IntObject.value as Year
+from Object
+ inner join Edge on Edge.refObject = Object.key
+ inner join EdgeType on Edge.refEdgeType = EdgeType.key
+ inner join VarCharObject on VarCharObject.refObject = Object.key
+ left outer join IntObject on IntObject.refObject = Object.key
+
+-- -----------------------------------------------------------------------------
+-- EdgesMinimal
+create view EdgesMinimal as
+select SrcName.Value as Source, EdgeType.Name as Type, TrgName.Value as Target
+from Object
+ inner join Edge on Edge.refObject = Object.key
+ inner join EdgeType on Edge.refEdgeType = EdgeType.key
+ inner join VarCharObject on VarCharObject.refObject = Object.key
+ inner join VarCharObject as SrcName on Edge.refSourceVertex = SrcName.refObject
+ inner join VarCharObject as TrgName on Edge.refTargetVertex = TrgName.refObject
+
+-- -----------------------------------------------------------------------------
+-- EdgesComplete
+select Edge.key as Edg, Object.key as Obj, EdgeType.Name as Type,
+ VarCharObject.value as Name, IntObject.value as Year,
+ Edge.refSourceVertex as Src, SrcName.Value,
+ Edge.refTargetVertex as Trg, TrgName.Value
+from Object
+ inner join Edge on Edge.refObject = Object.key
+ inner join EdgeType on Edge.refEdgeType = EdgeType.key
+ inner join VarCharObject on VarCharObject.refObject = Object.key
+ inner join VarCharObject as SrcName on Edge.refSourceVertex = SrcName.refObject
+ inner join VarCharObject as TrgName on Edge.refTargetVertex = TrgName.refObject
+ left outer join IntObject on IntObject.refObject = Object.key
+
+-- -----------------------------------------------------------------------------
+-- Edges 2.
+--
+select refSourceVertex as Src, VarCharObject.value as Source,
+ (select EdgeType.name from EdgeType where EdgeType.key = Edge.refEdgeType) as Relation,
+ (select IntObject.value
+ from Edge
+ inner join Object on Object.key = Edge.refObject
+ inner join IntObject on Object.key = IntObject.refObject
+ ) as Year,
+ (select VarCharObject.value
+ from Edge
+ inner join Vertex on Vertex.key = Edge.refTargetVertex
+ inner join Object on Object.key = Vertex.refObject
+ inner join VarCharObject on Object.key = VarCharObject.refObject
+ ) as Target
+from Edge
+ inner join Vertex on Vertex.key = Edge.refSourceVertex
+ inner join Object on Object.key = Vertex.refObject
+ inner join VarCharObject on Object.key = VarCharObject.refObject
+
+
+-- -----------------------------------------------------------------------------
+-- Fields
+
+
+-- -----------------------------------------------------------------------------
+-- Artists
+select Vertex.key as Vtx, Object.key as Obj, VarCharObject.value as Name, IntObject.value as YoBirth
+from Vertex
+ inner join Object on Object.key = Vertex.refObject
+ inner join VarCharObject on Object.key = VarCharObject.refObject and VarCharObject.refAttribute = 1
+ inner join IntObject on Object.key = IntObject.refObject and IntObject.refAttribute = 2
+where
+ Vertex.refObjectType = 1
+
+
+
+-- -----------------------------------------------------------------------------
+-- Recordings alias Tracks
+select Object.key as Obj, Vertex.refObjectType as TKey, ObjectType.Name as Type,
+ Name.value as Name, YearOfCrea.value as YoC, Position.value as Pos, Duration.value as Dur
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join IntObject as YearOfCrea on YearOfCrea.refObject = Object.key and YearOfCrea.refAttribute = 2
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+
+
+-- -----------------------------------------------------------------------------
+-- Tracks, incluing album attributes via sub-selects
+select Object.key as Obj, Vertex.refObjectType as Tp, ObjectType.Name as Type,
+ Name.value as Name, YearOfCrea.value as YoC, Position.value as Pos, Duration.value as Dur,
+ (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) as Alb,
+ (select VarCharObject.value from VarCharObject where VarCharObject.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) and VarCharObject.refAttribute = 1) as Album,
+ (select IntObject.value from IntObject where IntObject.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) and IntObject.refAttribute = 2) as YoC
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join IntObject as YearOfCrea on YearOfCrea.refObject = Object.key and YearOfCrea.refAttribute = 2
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+
+-- -----------------------------------------------------------------------------
+-- Tracks, incluing album attributes via joins
+select Object.key as Obj, Vertex.refObjectType as Tp, ObjectType.Name as Type,
+ Name.value as Name, Position.value as Pos, Duration.value as Dur,
+ (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) as Alb,
+ Album_Name.value as Album, Album_Year.value as YoC
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+ left outer join VarCharObject as Album_Name
+ on Album_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Name.refAttribute = 1
+ left outer join IntObject as Album_Year
+ on Album_Year.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Year.refAttribute = 2
+
+
+-- -----------------------------------------------------------------------------
+-- Tracks, including album attributes via joins
+select Object.key as Obj, Vertex.refObjectType as Tp, ObjectType.Name as Type
+ , Name.value as Name, Position.value as Pos, Duration.value as Dur
+ , (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) as Alb
+ , Album_Name.value as Album, Album_Year.value as YoC
+ , Performer_Name.value as Performer
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+ left outer join VarCharObject as Album_Name
+ on Album_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Name.refAttribute = 1
+ left outer join IntObject as Album_Year
+ on Album_Year.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Year.refAttribute = 2
+ left outer join VarCharObject as Performer_Name
+ on Performer_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = 12 and refEdgeType = 2)
+
+
+-- -----------------------------------------------------------------------------
+-- Recordings alias Tracks, incluing album attributes via joins
+(select Edge.refTargetVertex, group_concat(VarCharObject.value) as Performers
+ from Edge
+ inner join VarCharObject on Edge.refSourceVertex = VarCharObject.refObject
+ where refTargetVertex = 12 and refEdgeType = 2)
+
+-- -----------------------------------------------------------------------------
+-- Tracks, including album attributes via joins
+select Object.key as Obj, Vertex.refObjectType as Tp, ObjectType.Name as Type
+ , Name.value as Name, Position.value as Pos, Duration.value as Dur
+ , (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) as Alb
+ , Album_Name.value as Album, Album_Year.value as YoC
+ , Performer_Name.value as Performer
+ , (select Edge.refTargetVertex, group_concat(VarCharObject.value) as Performers
+ from Edge
+ inner join VarCharObject on Edge.refSourceVertex = VarCharObject.refObject
+ where refTargetVertex = 12 and refEdgeType = 2)
+
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+ left outer join VarCharObject as Album_Name
+ on Album_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Name.refAttribute = 1
+ left outer join IntObject as Album_Year
+ on Album_Year.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Year.refAttribute = 2
+ left outer join VarCharObject as Performer_Name
+ on Performer_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = 12 and refEdgeType = 2)
+
+
+-- -----------------------------------------------------------------------------
+-- Tracks, including album attributes via joins (*)
+select Object.key as Obj, Vertex.refObjectType as Tp, ObjectType.Name as Type
+ , Name.value as Name, Position.value as Pos, Duration.value as Dur
+ , (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5) as Alb
+ , Album_Name.value as Album, Album_Year.value as YoC
+ , Performer_Name.value as Performer
+ , (select group_concat(VarCharObject.value)
+ from Edge
+ inner join VarCharObject on Edge.refSourceVertex = VarCharObject.refObject
+ where refTargetVertex = (select Edge.refSourceVertex from Edge
+ where refTargetVertex = Object.key and refEdgeType = 5)
+ and refEdgeType = 2
+ ) as AlbumArtists
+from Object
+ inner join Vertex on Vertex.refObject = Object.key
+ inner join ObjectType on Vertex.refObjectType = ObjectType.key and ObjectType.key = 3
+ inner join VarCharObject as Name on Name.refObject = Object.key and Name.refAttribute = 1
+ left outer join VarCharObject as Duration on Duration.refObject = Object.key and Duration.refAttribute = 3
+ left outer join VarCharObject as Position on Position.refObject = Object.key and Position.refAttribute = 4
+ left outer join VarCharObject as Album_Name
+ on Album_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Name.refAttribute = 1
+ left outer join IntObject as Album_Year
+ on Album_Year.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = Object.key and refEdgeType = 5)
+ and Album_Year.refAttribute = 2
+ left outer join VarCharObject as Performer_Name
+ on Performer_Name.refObject = (select Edge.refSourceVertex from Edge where refTargetVertex = 12 and refEdgeType = 2)
+
+
+-- -----------------------------------------------------------------------------
+-- Experimental views: Checking how we can structure selects based on Edges
+-- using Sql-Views to make them more managable.
+-- -----------------------------------------------------------------------------
+-- View Album
+create view Album as
+select -- Edge.refSourceVertex as ArtistKey,
+ Vertex.key as Key, AlbumTitle.value as Name, group_concat(Performer.value) as Artists, YearOfCreation.value as YoC
+from Vertex
+ inner join Edge on Edge.refTargetVertex = Vertex.key
+ and Edge.refEdgeType = 2 -- 2: Artist (performs on) Album
+ left outer join VarCharObject as AlbumTitle on AlbumTitle.refObject = vertex.key and AlbumTitle.refAttribute = 1
+ left outer join VarCharObject as Performer on Performer.refObject = Edge.refSourceVertex
+ left outer join IntObject as YearOfCreation on YearOfCreation.refObject = Vertex.key
+group by Vertex.key
+
+-- -----------------------------------------------------------------------------
+-- View Track
+-- create view Track as
+select -- Edge.refSourceVertex as ArtistKey,
+ Vertex.key as TrkKey, TrackName.value
+ , MotherAlbum.refSourceVertex as MAlbKey, Album.Key as AlbKey
+ , Album.Name as AlbumName, Album.Artists as AlbumArtists
+from Vertex
+ inner join VarCharObject as TrackName on TrackName.refObject = Vertex.key
+ and TrackName.refAttribute = 1
+ and Vertex.refObjectType = 3
+ left outer join Edge as MotherAlbum on MotherAlbum.refTargetVertex = Vertex.key
+ and MotherAlbum.refEdgeType = 5 -- 5: Album contains Recording
+ left outer join Album on MotherAlbum.refSourceVertex = Album.Key
+
Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/browser.cpp
==============================================================================
--- sandbox/icl/libs/xplore/br1/sqlbrowser/browser.cpp (original)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/browser.cpp 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -51,7 +51,9 @@
#include "data/qsql/QSqlSelector.h"
#include "data/concept/Selector.h"
+#include "gen/NumberGenerator.h"
#include "gen/NameGenerator.h"
+#include "gen/DbGenerator.h"
Browser::Browser(QWidget *parent)
: QWidget(parent)
@@ -165,9 +167,16 @@
updateActions();
}
-/*JODO REV
bool Browser::runScript()
{
+ //return execScript(); // Execute a script containing of multiple sql-statements
+ //return casualTests();
+ return generateDb();
+}
+
+
+bool Browser::execScript()
+{
QString curSql = sqlEdit->toPlainText();
QSqlQuery curQuery = QSqlQuery(connectionWidget->currentDatabase());
@@ -180,9 +189,26 @@
return success;
}
-*/
-bool Browser::runScript()
+
+//------------------------------------------------------------------------------
+//JODO CL. Make a better GUI for different funcitons.
+//Experimental space
+bool Browser::generateDb()
+{
+ using namespace gen;
+ DbGenerator aDbGentor = DbGenerator(connectionWidget->currentDatabase());
+ bool success = aDbGentor.generate();
+
+ if(success)
+ emit statusMessage(tr("Db generated successfully."));
+ else
+ emit statusMessage(tr("Error(s), in Db generation."));
+
+ return success;
+}
+
+bool Browser::casualTests()
{
QString curSql = "select * from EdgeType";
QSqlQuery curQuery = QSqlQuery(connectionWidget->currentDatabase());
@@ -191,8 +217,10 @@
gen::tString some;
gen::NameGenerator makeSome(2,5);
- for(int i=0; i<10; i++)
+ int trials = gen::IntGenerator(2,10)();
+ for(int i=0; i<trials ; i++)
{
+ double dbg_dbl = gen::DoubleGenerator(0.1, 0.9)();
some += makeSome();
some += "\n";
}
@@ -357,6 +385,7 @@
}
}
+
void Browser::openFile()
{
QString fileName
Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/browser.h
==============================================================================
--- sandbox/icl/libs/xplore/br1/sqlbrowser/browser.h (original)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/browser.h 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -127,8 +127,11 @@
private:
void deselectFirstCell(int selectedRow);
-
void insertNewVertex(DagModel* pDagModel, const QModelIndex& index);
+
+ bool execScript();
+ bool casualTests();
+ bool generateDb();
};
#endif
Added: sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.cpp
==============================================================================
--- (empty file)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.cpp 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -0,0 +1,161 @@
+/****************************************************************************
+**
+**
+****************************************************************************/
+
+#include <QtGui>
+#include <QString>
+#include <QMessageBox>
+#include "gen/DbGenerator.h"
+
+using namespace gen;
+
+
+bool DbGenerator::generate()
+{
+ m_aFailingSql = tString();
+
+ if(!m_aDb.open())
+ {
+ m_aFailingSql = tString("m_aDb.open()");
+ return false;
+ }
+
+ //------------------------------------------
+ clean();
+ generateTables();
+
+ generateTypeData();
+ //------------------------------------------
+
+ if(m_aFailingSql.isEmpty())
+ return true;
+ else
+ return false;
+}
+
+
+bool DbGenerator::exec(const char* sql)
+{
+ if(!m_aQuery.exec(sql))
+ {
+ m_aFailingSql = sql;
+ m_aLastError = m_aQuery.lastError();
+ return false;
+ }
+ return true;
+}
+
+
+void DbGenerator::generateTypeData()
+{
+ generateTypeTraits();
+ generateObjectTypes();
+ generateEdgeTypes();
+ generateAttributes();
+
+ generateTypeViews();
+}
+
+
+
+void DbGenerator::clean()
+{
+
+ exec("drop view Album" );
+ exec("drop view EdgesMinimal" );
+ exec("drop view NamedObjects" );
+ exec("drop view EdgeTypeCheck");
+
+ exec("drop table TypeTraits" );
+ exec("drop table ObjectType" );
+ exec("drop table EdgeType" );
+ exec("drop table Vertex" );
+ exec("drop table Edge" );
+ exec("drop table Object" );
+ exec("drop table Attribute" );
+ exec("drop table VarCharObject");
+ exec("drop table IntObject" );
+}
+
+void DbGenerator::generateTables()
+{
+ exec("create table TypeTraits (key integer primary key, name varchar)");
+ exec("create table ObjectType (key integer primary key, traits integer, name varchar)");
+ exec("create table EdgeType (key integer primary key, refSourceType integer, refRelationType integer, refTargetType integer, name varchar)");
+ exec("create table Vertex (key integer primary key, refObjectType integer)");
+ exec("create table Edge (key integer primary key, refEdgeType integer, refSourceVertex integer, refTargetVertex integer)");
+ exec("create table Object (key integer primary key)");
+ exec("create table Attribute (key integer primary key, refObjectType integer, name varchar)");
+ exec("create table VarCharObject (refObject integer, refAttribute integer, value varchar, primary key (refObject, refAttribute))");
+ exec("create table IntObject (refObject integer, refAttribute integer, value integer, primary key (refObject, refAttribute))");
+}
+
+void DbGenerator::generateTypeTraits()
+{
+ exec("insert into TypeTraits values (0, 'atom obj')");
+ exec("insert into TypeTraits values (1, 'comp obj')");
+ exec("insert into TypeTraits values (2, 'atom rel')");
+}
+
+void DbGenerator::generateObjectTypes()
+{
+ exec("insert into ObjectType values (1, 0, 'a:text')");
+ exec("insert into ObjectType values (2, 0, 'a:integer')");
+ exec("insert into ObjectType values (3, 0, 'a:real')");
+
+ exec("insert into ObjectType values (21, 0, 'a:artist')");
+ exec("insert into ObjectType values (22, 0, 'a:title')");
+ exec("insert into ObjectType values (23, 0, 'a:recording')");
+ exec("insert into ObjectType values (24, 0, 'c:artists')");
+ exec("insert into ObjectType values (25, 1, 'c:genre')");
+ exec("insert into ObjectType values (26, 2, 'r:composed')");
+ exec("insert into ObjectType values (27, 2, 'r:performed')");
+ exec("insert into ObjectType values (28, 2, 'r:recorded as')");
+ exec("insert into ObjectType values (29, 2, 'r:located at')");
+ exec("insert into ObjectType values (30, 0, 'c:album')");
+ exec("insert into ObjectType values (31, 2, 'r:contains')");
+}
+
+void DbGenerator::generateEdgeTypes()
+{
+ exec("insert into EdgeType values (1, 21, 26, 22, 'artist composed title' )");
+ exec("insert into EdgeType values (2, 21, 27, 23, 'artist performed record' )");
+ exec("insert into EdgeType values (3, 22, 28, 23, 'title recorded as record')");
+ exec("insert into EdgeType values (4, 23, 29, 24, 'record located at url' )");
+ exec("insert into EdgeType values (5, 30, 31, 23, 'album contains record' )");
+}
+
+void DbGenerator::generateAttributes()
+{
+ exec("insert into Attribute values (1, 1, 'Name')" );
+ exec("insert into Attribute values (2, 1, 'Duration')");
+ exec("insert into Attribute values (31, 2, 'Year')" );
+ exec("insert into Attribute values (32, 2, 'Pos')" );
+
+}
+
+void DbGenerator::generateTypeViews()
+{
+ exec
+ (
+ "create view EdgeTypeCheck as "
+ "select "
+ " (select ObjectType.name from ObjectType where ObjectType.key = EdgeType.refSourceType) as SrcT, "
+ " (select ObjectType.name from ObjectType where ObjectType.key = EdgeType.refRelationType) as RelT, "
+ " ObjectType.name as TrgT, "
+ " EdgeType.name "
+ "from EdgeType "
+ " inner join ObjectType on EdgeType.refTargetType = ObjectType.key"
+ );
+}
+
+/*
+void DbGenerator::generateArtist()
+{
+ tKey aKey = generateObject();
+ generateVertex(aKey, a_artist);
+ generateVarCharObject(aKey, )
+
+}
+*/
Added: sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.h
==============================================================================
--- (empty file)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/gen/DbGenerator.h 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -0,0 +1,76 @@
+/****************************************************************************
+**
+**
+****************************************************************************/
+
+#pragma once
+
+
+
+#include <QtSql>
+#include <QSqlDatabase.h>
+#include <QString.h>
+
+#include "Dag/DbType.h"
+#include "gen/NameGenerator.h"
+
+namespace gen
+{
+
+class DbGenerator
+{
+public:
+ typedef dag::db::tKey tKey;
+
+ 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
+ };
+
+ enum {
+ A_Name = 1
+ , A_Duration = 2
+ , A_Year = 31
+ , A_Position = 32
+ };
+
+ DbGenerator(const QSqlDatabase& db):
+ m_aDb(db), m_aQuery("", m_aDb){}
+
+ bool generate();
+ void clean();
+ void generateTables();
+
+ void generateTypeData();
+
+ void generateTypeViews();
+
+private:
+ bool exec(const char* sql);
+
+ //void generateTypeData();
+ void generateTypeTraits();
+ void generateObjectTypes();
+ void generateEdgeTypes();
+ void generateAttributes();
+
+private:
+ QSqlDatabase m_aDb;
+ QSqlQuery m_aQuery;
+ QSqlError m_aLastError;
+ tString m_aFailingSql;
+};
+
+} // namespace data
Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NameGenerator.cpp
==============================================================================
--- sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NameGenerator.cpp (original)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NameGenerator.cpp 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -3,12 +3,11 @@
**
****************************************************************************/
-#include "NameGenerator.h"
+#include "gen/NumberGenerator.h"
+#include "gen/NameGenerator.h"
using namespace gen;
-boost::random::mt19937 NameGenerator::s_aGen;
-
const char* NameGenerator::s_SyllablesBegin[] =
{
"Ana"
@@ -53,6 +52,23 @@
return sizeof(s_SyllablesEnd)/sizeof(s_SyllablesEnd[0]);
}
+int NameGenerator::syllableIndexBegin()
+{
+ return s_BeginDist(g_aRandomGenerator);
+}
+
+int NameGenerator::syllableIndexMid()
+{
+ return s_MidDist(g_aRandomGenerator);
+}
+
+int NameGenerator::syllableIndexEnd()
+{
+ return s_EndDist(g_aRandomGenerator);
+}
+
+
+
tUniform NameGenerator::s_BeginDist(0, NameGenerator::syllablesBeginCount()-1);
tUniform NameGenerator::s_MidDist (0, NameGenerator::syllablesMidCount()-1);
tUniform NameGenerator::s_EndDist (0, NameGenerator::syllablesEndCount()-1);
@@ -60,16 +76,17 @@
tString NameGenerator::operator()()const
{
tString someName = m_aPrefix;
- someName += s_SyllablesBegin[s_BeginDist(s_aGen)];
+ someName += s_SyllablesBegin[syllableIndexBegin()];
- int count = m_aDist(s_aGen) - 2;
+ int count = m_aDist(g_aRandomGenerator) - 2;
count = count < 0 ? 0 : count;
for(int idx = 0; idx < count; idx++)
- someName += s_SyllablesMid[s_MidDist(s_aGen)];
+ someName += s_SyllablesMid[syllableIndexMid()];
- someName += s_SyllablesEnd[s_EndDist(s_aGen)];
+ someName += s_SyllablesEnd[syllableIndexEnd()];
return someName;
}
+
Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NameGenerator.h
==============================================================================
--- sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NameGenerator.h (original)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NameGenerator.h 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -16,6 +16,8 @@
typedef QString tString;
typedef boost::random::uniform_int_distribution<> tUniform;
+typedef boost::random::mt19937 tRandomGenerator;
+
class NameGenerator
{
@@ -25,17 +27,20 @@
tString operator()()const;
+
static int syllablesBeginCount();
static int syllablesMidCount();
static int syllablesEndCount();
+ static int syllableIndexBegin();
+ static int syllableIndexMid();
+ static int syllableIndexEnd();
+
private:
int m_iMinSyllables;
int m_iMaxSyllables;
tString m_aPrefix;
- static boost::random::mt19937 s_aGen;
-
tUniform m_aDist;
static const char* s_SyllablesBegin[];
Added: sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NumberGenerator.cpp
==============================================================================
--- (empty file)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NumberGenerator.cpp 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -0,0 +1,11 @@
+/****************************************************************************
+**
+**
+****************************************************************************/
+
+#include "gen/NumberGenerator.h"
+
+using namespace gen;
+
+//Yes, its a global RandomGenerator
+gen::tRandomGenerator gen::g_aRandomGenerator;
Added: sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NumberGenerator.h
==============================================================================
--- (empty file)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/gen/NumberGenerator.h 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -0,0 +1,49 @@
+/****************************************************************************
+**
+**
+****************************************************************************/
+
+#pragma once
+
+
+#include <boost/random/mersenne_twister.hpp>
+#include <boost/random/uniform_int_distribution.hpp>
+#include <boost/random/uniform_real_distribution.hpp>
+
+#include <QString.h>
+
+namespace gen
+{
+
+typedef QString tString;
+typedef boost::random::uniform_int_distribution<> tUniformInt;
+typedef boost::random::uniform_real_distribution<> tUniformReal;
+typedef boost::random::mt19937 tRandomGenerator;
+
+
+extern tRandomGenerator g_aRandomGenerator;
+
+class IntGenerator
+{
+public:
+ IntGenerator(int min, int max): m_aDist(min, max){}
+
+ int operator()()const { return m_aDist(g_aRandomGenerator); }
+
+private:
+ gen::tUniformInt m_aDist;
+};
+
+class DoubleGenerator
+{
+public:
+ DoubleGenerator(double min, double max): m_aDist(min, max){}
+
+ double operator()()const { return m_aDist(g_aRandomGenerator); }
+
+private:
+ gen::tUniformReal m_aDist;
+};
+
+
+} // namespace data
Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/qsqlconnectiondialog.ui
==============================================================================
--- sandbox/icl/libs/xplore/br1/sqlbrowser/qsqlconnectiondialog.ui (original)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/qsqlconnectiondialog.ui 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -58,7 +58,7 @@
<item row="1" column="1">
<widget class="QLineEdit" name="editDatabase">
<property name="text">
- <string>../sqlbrowser/objects1.db</string>
+ <string>../sqlbrowser/Generatic1.db</string>
</property>
</widget>
</item>
Modified: sandbox/icl/libs/xplore/br1/sqlbrowser/sqlbrowser4.pro
==============================================================================
--- sandbox/icl/libs/xplore/br1/sqlbrowser/sqlbrowser4.pro (original)
+++ sandbox/icl/libs/xplore/br1/sqlbrowser/sqlbrowser4.pro 2012-05-22 12:43:38 EDT (Tue, 22 May 2012)
@@ -26,14 +26,18 @@
data/qsql/QSqlSelector.h \
data/qsql/QSqlCreator.h \
data/concept/GetCreator.h \
- gen/NameGenerator.h
+ gen/NameGenerator.h \
+ gen/NumberGenerator.h \
+ gen/DbGenerator.h
SOURCES = main.cpp browser.cpp connectionwidget.cpp qsqlconnectiondialog.cpp \
exttableview.cpp \
dagitem.cpp \
dagmodel.cpp \
Dag/TypeGraph.cpp \
Dag/TypeGraph.cpp \
- gen/NameGenerator.cpp
+ gen/NameGenerator.cpp \
+ gen/NumberGenerator.cpp \
+ gen/DbGenerator.cpp
FORMS = browserwidget.ui qsqlconnectiondialog.ui
build_all:!build_pass {
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