public void ShouldGenerate_Select_Also_First_Nth_As_Query()
{
string generatedUntypedQuery = new OSqlSelect()
.Select("foo").As("Foo")
.Also("bar").As("Bar")
.Also("baz").First().As("Baz")
.Also("baq").Nth(0).As("Baq")
.From("OGraphVertex")
.ToString();
string generatedTypedQuery = new OSqlSelect()
.Select("foo").As("Foo")
.Also("bar").As("Bar")
.Also("baz").First().As("Baz")
.Also("baq").Nth(0).As("Baq")
.From<OGraphVertex>()
.ToString();
string query =
"SELECT foo AS Foo, " +
"bar AS Bar, " +
"first(baz) AS Baz, " +
"baq[0] AS Baq " +
"FROM OGraphVertex";
Assert.AreEqual(generatedUntypedQuery, query);
Assert.AreEqual(generatedTypedQuery, query);
}
public void ShouldGenerateSelectWhereLimitQuery()
{
string generatedQuery = new OSqlSelect()
.Select()
.From("TestClass")
.Where("foo").Equals("whoa")
.Or("foo").NotEquals(123)
.And("foo").Lesser(1)
.And("foo").LesserEqual(2)
.And("foo").Greater(3)
.And("foo").GreaterEqual(4)
.And("foo").Like("%whoa%")
.And("foo").IsNull()
.And("foo").Contains("johny")
.And("foo").Contains("name", "johny")
.Limit(5)
.ToString();
string query =
"SELECT " +
"FROM TestClass " +
"WHERE foo = 'whoa' " +
"OR foo != 123 " +
"AND foo < 1 " +
"AND foo <= 2 " +
"AND foo > 3 " +
"AND foo >= 4 " +
"AND foo LIKE '%whoa%' " +
"AND foo IS NULL " +
"AND foo CONTAINS 'johny' " +
"AND foo CONTAINS (name = 'johny') " +
"LIMIT 5";
Assert.AreEqual(generatedQuery, query);
}
public void ShouldGenerateSelectWhereLimitQuery()
{
string generatedQuery = new OSqlSelect()
.Select()
.From("TestClass")
.Where("foo").Equals("whoa")
.Or("foo").NotEquals(123)
.And("foo").Lesser(1)
.And("foo").LesserEqual(2)
.And("foo").Greater(3)
.And("foo").GreaterEqual(4)
.And("foo").Like("%whoa%")
.And("foo").IsNull()
.And("foo").Contains("johny")
.And("foo").Contains("name", "johny")
.Limit(5)
.ToString();
string query =
"SELECT " +
"FROM TestClass " +
"WHERE foo = 'whoa' " +
"OR foo != 123 " +
"AND foo < 1 " +
"AND foo <= 2 " +
"AND foo > 3 " +
"AND foo >= 4 " +
"AND foo LIKE '%whoa%' " +
"AND foo IS NULL " +
"AND foo CONTAINS 'johny' " +
"AND foo CONTAINS (name = 'johny') " +
"LIMIT 5";
Assert.AreEqual(generatedQuery, query);
}
public void ShouldGenerateSelectFromOrderByDescendingQuery()
{
string generatedQuery = new OSqlSelect()
.Select()
.From("TestClass")
.OrderBy("foo")
.Descending()
.ToString();
string query =
"SELECT " +
"FROM TestClass " +
"ORDER BY foo DESC";
Assert.Equal(generatedQuery, query);
}
public void ShouldGenerateSelectFromDocumentOridQuery()
{
ODocument document = new ODocument();
document.ORID = new ORID(8, 0);
string generatedQuery = new OSqlSelect()
.Select("foo", "bar")
.From(document)
.ToString();
string query =
"SELECT foo, bar " +
"FROM #8:0";
Assert.AreEqual(generatedQuery, query);
}
public void ShouldGenerateSelectAlsoNthAsQuery()
{
string generatedQuery = new OSqlSelect()
.Select("foo").As("Foo")
.Also("bar").As("Bar")
.Also("baq").Nth(0).As("Baq")
.From("TestClass")
.ToString();
string query =
"SELECT foo AS Foo, " +
"bar AS Bar, " +
"baq[0] AS Baq " +
"FROM TestClass";
Assert.Equal(generatedQuery, query);
}
public void ShouldGenerateSelectFromDocumentOClassNameQuery()
{
ODocument document = new ODocument();
document.OClassName = "TestClass";
string generatedQuery = new OSqlSelect()
.Select("foo", "bar")
.From(document)
.ToString();
string query =
"SELECT foo, bar " +
"FROM TestClass";
Assert.Equal(generatedQuery, query);
}
public void ShouldGenerateSelectFromDocumentOridQuery()
{
ODocument document = new ODocument();
document.ORID = new ORID(8, 0);
string generatedQuery = new OSqlSelect()
.Select("foo", "bar")
.From(document)
.ToString();
string query =
"SELECT foo, bar " +
"FROM #8:0";
Assert.Equal(generatedQuery, query);
}
public void ShouldGenerateSelectAlsoNthAsQuery()
{
string generatedQuery = new OSqlSelect()
.Select("foo").As("Foo")
.Also("bar").As("Bar")
.Also("baq").Nth(0).As("Baq")
.From("TestClass")
.ToString();
string query =
"SELECT foo AS Foo, " +
"bar AS Bar, " +
"baq[0] AS Baq " +
"FROM TestClass";
Assert.AreEqual(generatedQuery, query);
}
public void ShouldGenerateSelectFromOrderBySkipLimitQuery()
{
string generatedQuery = new OSqlSelect()
.Select()
.From("TestClass")
.OrderBy("foo", "bar")
.Skip(5)
.Limit(10)
.ToString();
string query =
"SELECT " +
"FROM TestClass " +
"ORDER BY foo, bar " +
"SKIP 5 " +
"LIMIT 10";
Assert.Equal(generatedQuery, query);
}
public void ShouldGenerate_Select_Where_And_Or_Like_IsNull_Conditions_Query()
{
string generatedUntypedQuery = new OSqlSelect()
.Select()
.From("OGraphVertex")
.Where("foo").Equals("foo string")
.And("bar").NotEquals(12345)
.Or("baz").LesserEqual(10)
.Or("baq").GreaterEqual(50)
.Or("f1").Like("fulltext%")
.Or("f2").IsNull()
.ToString();
string generatedTypedQuery = new OSqlSelect()
.Select()
.From<OGraphVertex>()
.Where("foo").Equals("foo string")
.And("bar").NotEquals(12345)
.Or("baz").LesserEqual(10)
.Or("baq").GreaterEqual(50)
.Or("f1").Like("fulltext%")
.Or("f2").IsNull()
.ToString();
string query =
"SELECT " +
"FROM OGraphVertex " +
"WHERE foo = 'foo string' " +
"AND bar != 12345 " +
"OR baz <= 10 " +
"OR baq >= 50 " +
"OR f1 LIKE 'fulltext%' " +
"OR f2 IS NULL";
Assert.AreEqual(generatedUntypedQuery, query);
Assert.AreEqual(generatedTypedQuery, query);
}
public void ShouldGenerateSelectFromDocumentOClassNameQuery()
{
ODocument document = new ODocument();
document.OClassName = "TestClass";
string generatedQuery = new OSqlSelect()
.Select("foo", "bar")
.From(document)
.ToString();
string query =
"SELECT foo, bar " +
"FROM TestClass";
Assert.AreEqual(generatedQuery, query);
}
public void ShouldGenerateSelectFromOrderByDescendingQuery()
{
string generatedQuery = new OSqlSelect()
.Select()
.From("TestClass")
.OrderBy("foo")
.Descending()
.ToString();
string query =
"SELECT " +
"FROM TestClass " +
"ORDER BY foo DESC";
Assert.AreEqual(generatedQuery, query);
}
public void ShouldGenerateSelectFromOrderBySkipLimitQuery()
{
string generatedQuery = new OSqlSelect()
.Select()
.From("TestClass")
.OrderBy("foo", "bar")
.Skip(5)
.Limit(10)
.ToString();
string query =
"SELECT " +
"FROM TestClass " +
"ORDER BY foo, bar " +
"SKIP 5 " +
"LIMIT 10";
Assert.AreEqual(generatedQuery, query);
}
public void ShouldGenerate_Select_Where_Contains_Query()
{
string generatedUntypedQuery = new OSqlSelect()
.Select()
.From("OGraphVertex")
.Where("foo").Contains("english")
.And("bar").Contains("foo", 123)
.ToString();
string generatedTypedQuery = new OSqlSelect()
.Select()
.From<OGraphVertex>()
.Where("foo").Contains("english")
.And("bar").Contains("foo", 123)
.ToString();
string query =
"SELECT " +
"FROM OGraphVertex " +
"WHERE foo CONTAINS 'english' " +
"AND bar CONTAINS (foo = 123)";
Assert.AreEqual(generatedUntypedQuery, query);
Assert.AreEqual(generatedTypedQuery, query);
}
internal void From(OSqlSelect nestedSelect)
{
_compiler.Unique(Q.From, "(", nestedSelect.ToString(), ")");
}
static void Main(string[] args)
{
List <Occupations> _lisOccupations = LoadOccupations();
#region
try
{
OrientDBDriver.CreateDatabase();
OrientDBDriver.CreatePool();
ODatabase oDB = new ODatabase(OrientDBDriver.DatabaseAlias);
// we are setting Database to useLightweightEdges set to True because currently
// we don't need any properties in edges for User hasOccupationa and Movies_genres
oDB.Command("ALTER DATABASE custom useLightweightEdges=true");
#region Load Occupation
// First of all create Class of Occupation which extends from vertex class V
Console.WriteLine("Creating VERTEX Occupation ....");
oDB.Create.Class <Occupations>().Extends <OVertex>().CreateProperties <Occupations>().Run();
foreach (Occupations occ in _lisOccupations)
{
oDB.Create.Vertex <Occupations>(occ).Run();
}
Console.WriteLine("Successfully created Vertex Occupation");
#endregion
#region Load Users
List <Users> _listUsers = LoadUsers();
Console.WriteLine("Creating VERTEX Users with relation 'hasOccupation' using EDGE ....");
oDB.Create.Class <Users>().Extends <OVertex>().CreateProperties().Run();
oDB.Create.Class("hasOccupation").Extends <OEdge>().Run();
foreach (Users user in _listUsers)
{
ODocument odoc = oDB.Create.Vertex("Users")
.Set("userID", user.userID)
.Set("Gender", user.gender)
// .Set("OccupationID",user.OccupationID)
.Set("Age", user.age)
.Set("ZipCode", user.ZipCode)
.Run();
string generateSQL = new OSqlSelect().Select().From("Occupations").Where("OccupationID").Equals(user.OccupationID).Limit(1).ToString();
List <ODocument> result = oDB.Query(generateSQL);
if (result.Count > 0)
{
OEdge edge = oDB.Create.Edge("hasOccupation").From(odoc.ORID).To(result[0].ORID).Run();
}
}
Console.WriteLine("Successfully created Vertex Users");
#endregion
#region Load Genres
List <Genres> _listGenres = LoadGenres();
Console.WriteLine("Creating VERTEX Genres ....");
oDB.Create.Class <Genres>().Extends <OVertex>().CreateProperties <Genres>().Run();
foreach (Genres g in _listGenres)
{
oDB.Create.Vertex <Genres>(g).Run();
}
Console.WriteLine("Successfully created Vertex Occupation");
Console.WriteLine("Successfully created Vertex Genres");
#endregion
#region Load Movies
// ------------- Load Movies with Relation Genres-----------------
List <Movies> _listMovies = LoadMovies();
Console.WriteLine("Creating VERTEX Movies ....");
oDB.Create.Class <Movies>().Extends <OVertex>().CreateProperties().Run();
oDB.Create.Class("hasGenera").Extends <OEdge>().Run();
foreach (Movies g in _listMovies)
{
ODocument odoc = oDB.Create.Vertex("Movies")
.Set("MovieID", g.MovieID)
.Set("Title", g.Title)
.Run();
foreach (string genres in g.Genres)
{
string generateSQL = new OSqlSelect().Select().From("Genres").Where("description").Equals(genres).Limit(1).ToString();
List <ODocument> result = oDB.Query(generateSQL);
if (result.Count > 0)
{
OEdge edge = oDB.Create.Edge("hasGenera").From(odoc.ORID).To(result[0].ORID).Run();
}
}
}
Console.WriteLine("Successfully created Vertex Movies with relation has genres");
#endregion
//---------- Load One Million I takes almost ~20mins in Core i7 6th Gen, 2.5 GHz processor with 8GB RAM
// --- Waiting for response from OrientDB for Bulkinsert (massiveinsert) operaion support in .Net API
#region Load Ratings
oDB.Command("ALTER DATABASE custom useLightweightEdges=false");
oDB.Create.Class <Rated>().Extends <OEdge>().CreateProperties().Run();
oDB.Command("CREATE INDEX users.userID UNIQUE");
oDB.Command("CREATE INDEX Movies.movieID UNIQUE");
Console.WriteLine("Creating Ratings one Million....");
int i = 0;
System.Diagnostics.Stopwatch st = new System.Diagnostics.Stopwatch();
st.Start();
List <Rating> _listRating = LoadRatings(_PathRatings);
oDB.Transaction.Reset();
object locker = new object();
Parallel.ForEach(_listRating, r =>
{
lock (locker)
{
i++;
// we can also use OrientDB command to create Edges between two vertices
oDB.Command(" create edge rated from (select from Users where userID = " + r.userID + ") to (select from Movies where MovieID = " + r.movieID + " ) set rating =" + r.Ratings + " ");
Console.WriteLine("Creating Ratings...." + i.ToString());
}
});
st.Stop();
Console.WriteLine("Successfully Created Ratings Transaction committed...." + st.Elapsed.TotalMinutes + "mins");
#endregion
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
#endregion
Console.ReadLine();
}
