public void TestInitialFieldsQuery()
{
var sqlFieldsQuery = new SqlFieldsQuery("select _key, _val, val from BINARIZABLEENTRY where val < 33");
TestInitialQuery(sqlFieldsQuery, cur => cur.GetAll());
TestInitialQuery(sqlFieldsQuery, cur => cur.ToList());
}
/// <summary>
/// Populate cache with test data.
/// </summary>
/// <param name="organizationCache">Organization cache.</param>
/// <param name="employeeCache">Employee cache.</param>
private static void Insert(ICache <int, Organization> organizationCache, ICache <int, Employee> employeeCache)
{
// Insert organizations.
var qry = new SqlFieldsQuery("insert into Organization (key, name) values (?, ?)", 1, "ASF");
organizationCache.Query(qry);
qry.Arguments = new object[] { 2, "Eclipse" };
organizationCache.Query(qry);
// Insert employees.
qry = new SqlFieldsQuery("insert into Employee (key, name, organizationId, salary) values (?, ?, ?, ?)");
qry.Arguments = new object[] { 1, "mnb", 1, 4000 };
employeeCache.Query(qry);
qry.Arguments = new object[] { 2, "qwe", 1, 5000 };
employeeCache.Query(qry);
qry.Arguments = new object[] { 3, "xzdfg", 2, 2000 };
employeeCache.Query(qry);
qry.Arguments = new object[] { 4, "puort", 2, 3000 };
employeeCache.Query(qry);
}
public SqlFieldsQuery CreateTable()
{
string query = "CREATE TABLE Lecturer(Name varchar(55),Id varchar(55),Departments varchar(max),Address varchar(250),JoinDate varchar(50),PRIMARY KEY (Id)) WITH \"CACHE_NAME = college-code-123\"";
SqlFieldsQuery squery = new SqlFieldsQuery(query);
return(squery);
}
public IgniteLinqBenchmark()
{
var ignite = Ignition.TryGetIgnite()
?? Ignition.Start(new IgniteConfiguration
{
BinaryConfiguration = new BinaryConfiguration(typeof(SqlPerson)),
CacheConfiguration = new[] { new CacheConfiguration("persons", new QueryEntity(typeof(SqlPerson))) }
});
_cache = ignite.GetCache <int, SqlPerson>("persons");
_cache.PutAll(Enumerable.Range(0, PersonCount)
.ToDictionary(x => x, x => new SqlPerson {
Id = x, Age = x * 2
}));
// Prepare queries.
_sqlQuery = new SqlFieldsQuery("select Age from SqlPerson where (SqlPerson.Id < ?)", SelectCount);
var persons = _cache.AsCacheQueryable();
_linq = persons.Where(x => x.Value.Id < SelectCount).Select(x => x.Value.Age);
_compiledLinq = CompiledQuery.Compile(() => persons
.Where(x => x.Value.Id < SelectCount).Select(x => x.Value.Age));
}
/** <inheritDoc /> */
public IQueryCursor <T> QueryFields <T>(SqlFieldsQuery qry, Func <IBinaryRawReader, int, T> readerFunc)
{
IgniteArgumentCheck.NotNull(qry, "qry");
IgniteArgumentCheck.NotNull(readerFunc, "readerFunc");
if (string.IsNullOrEmpty(qry.Sql))
{
throw new ArgumentException("Sql cannot be null or empty");
}
IUnmanagedTarget cursor;
using (var stream = IgniteManager.Memory.Allocate().GetStream())
{
var writer = Marshaller.StartMarshal(stream);
writer.WriteBoolean(qry.Local);
writer.WriteString(qry.Sql);
writer.WriteInt(qry.PageSize);
WriteQueryArgs(writer, qry.Arguments);
writer.WriteBoolean(qry.EnableDistributedJoins);
writer.WriteBoolean(qry.EnforceJoinOrder);
FinishMarshal(writer);
cursor = UU.CacheOutOpQueryCursor(Target, (int)CacheOp.QrySqlFields, stream.SynchronizeOutput());
}
return(new FieldsQueryCursor <T>(cursor, Marshaller, _flagKeepBinary, readerFunc));
}
// end::queryEntities[]
public static void QueryingDemo()
{
var ignite = Ignition.Start();
// tag::querying[]
var cache = ignite.GetCache <long, Person>("Person");
var sql = new SqlFieldsQuery("select concat(FirstName, ' ', LastName) from Person");
using (var cursor = cache.Query(sql))
{
foreach (var row in cursor)
{
Console.WriteLine("personName=" + row[0]);
}
}
// end::querying[]
// tag::schema[]
var sqlFieldsQuery = new SqlFieldsQuery("select name from City")
{
Schema = "PERSON"
};
// end::schema[]
}
public void TestFieldNames()
{
var cache = Cache();
PopulateCache(cache, false, 5, x => true);
// Get before iteration.
var qry = new SqlFieldsQuery("SELECT * FROM QueryPerson");
var cur = cache.Query(qry);
var names = cur.FieldNames;
Assert.AreEqual(new[] { "AGE", "NAME" }, names);
cur.Dispose();
Assert.AreSame(names, cur.FieldNames);
Assert.Throws <NotSupportedException>(() => cur.FieldNames.Add("x"));
// Custom order, key-val, get after iteration.
qry.Sql = "SELECT NAME, _key, AGE, _val FROM QueryPerson";
cur = cache.Query(qry);
cur.GetAll();
Assert.AreEqual(new[] { "NAME", "_KEY", "AGE", "_VAL" }, cur.FieldNames);
// Get after disposal.
qry.Sql = "SELECT 1, AGE FROM QueryPerson";
cur = cache.Query(qry);
cur.Dispose();
Assert.AreEqual(new[] { "1", "AGE" }, cur.FieldNames);
}
/// <summary>
/// Populate cache with test data.
/// </summary>
/// <param name="organizationCache">Organization cache.</param>
/// <param name="employeeCache">Employee cache.</param>
private static void Insert(ICache <int, Organization> organizationCache, ICache <int, Employee> employeeCache)
{
// Insert organizations.
var qry = new SqlFieldsQuery("insert into Organization (_key, name) values (?, ?)", 1, "ASF");
organizationCache.QueryFields(qry);
qry.Arguments = new object[] { 2, "Eclipse" };
organizationCache.QueryFields(qry);
// Insert employees.
qry = new SqlFieldsQuery("insert into Employee (_key, name, organizationId, salary) values (?, ?, ?, ?)");
qry.Arguments = new object[] { 1, "John Doe", 1, 4000 };
employeeCache.QueryFields(qry);
qry.Arguments = new object[] { 2, "Jane Roe", 1, 5000 };
employeeCache.QueryFields(qry);
qry.Arguments = new object[] { 3, "Mary Major", 2, 2000 };
employeeCache.QueryFields(qry);
qry.Arguments = new object[] { 4, "Richard Miles", 2, 3000 };
employeeCache.QueryFields(qry);
}
/** <inheritDoc /> */
public IFieldsQueryCursor Query(SqlFieldsQuery qry)
{
var cursor = QueryFieldsInternal(qry);
return(new FieldsQueryCursor(cursor, _flagKeepBinary,
(reader, count) => ReadFieldsArrayList(reader, count)));
}
private IPlatformTargetInternal QueryFieldsInternal(SqlFieldsQuery qry)
{
IgniteArgumentCheck.NotNull(qry, "qry");
if (string.IsNullOrEmpty(qry.Sql))
{
throw new ArgumentException("Sql cannot be null or empty");
}
return(DoOutOpObject((int)CacheOp.QrySqlFields, writer =>
{
writer.WriteBoolean(qry.Local);
writer.WriteString(qry.Sql);
writer.WriteInt(qry.PageSize);
QueryBase.WriteQueryArgs(writer, qry.Arguments);
writer.WriteBoolean(qry.EnableDistributedJoins);
writer.WriteBoolean(qry.EnforceJoinOrder);
writer.WriteBoolean(qry.Lazy); // Lazy flag.
writer.WriteInt((int)qry.Timeout.TotalMilliseconds);
writer.WriteBoolean(qry.ReplicatedOnly);
writer.WriteBoolean(qry.Colocated);
writer.WriteString(qry.Schema); // Schema
}));
}
/// <summary>
/// Writes the SQL fields query.
/// </summary>
private static void WriteSqlFieldsQuery(IBinaryRawWriter writer, SqlFieldsQuery qry,
bool includeColumns = true)
{
Debug.Assert(qry != null);
writer.WriteString(qry.Schema);
writer.WriteInt(qry.PageSize);
writer.WriteInt(-1); // maxRows: unlimited
writer.WriteString(qry.Sql);
QueryBase.WriteQueryArgs(writer, qry.Arguments);
// .NET client does not discern between different statements for now.
// We could have ExecuteNonQuery method, which uses StatementType.Update, for example.
writer.WriteByte((byte)StatementType.Any);
writer.WriteBoolean(qry.EnableDistributedJoins);
writer.WriteBoolean(qry.Local);
#pragma warning disable 618
writer.WriteBoolean(qry.ReplicatedOnly);
#pragma warning restore 618
writer.WriteBoolean(qry.EnforceJoinOrder);
writer.WriteBoolean(qry.Colocated);
writer.WriteBoolean(qry.Lazy);
writer.WriteTimeSpanAsLong(qry.Timeout);
writer.WriteBoolean(includeColumns);
}
/** <inheritDoc /> */
public IQueryCursor <IList> QueryFields(SqlFieldsQuery qry)
{
IgniteArgumentCheck.NotNull(qry, "qry");
if (string.IsNullOrEmpty(qry.Sql))
{
throw new ArgumentException("Sql cannot be null or empty");
}
IUnmanagedTarget cursor;
using (var stream = IgniteManager.Memory.Allocate().Stream())
{
var writer = Marshaller.StartMarshal(stream);
writer.WriteBoolean(qry.Local);
writer.WriteString(qry.Sql);
writer.WriteInt(qry.PageSize);
WriteQueryArgs(writer, qry.Arguments);
FinishMarshal(writer);
cursor = UU.CacheOutOpQueryCursor(Target, (int)CacheOp.QrySqlFields, stream.SynchronizeOutput());
}
return(new FieldsQueryCursor(cursor, Marshaller, _flagKeepPortable));
}
static void Main()
{
IIgnite ignite = Ignition.Start();
ICache <int, Person> cache = ignite.GetOrCreateCache <int, Person>(
new CacheConfiguration("persons", typeof(Person)));
cache[1] = new Person {
Name = "John Doe", Age = 27
};
cache[2] = new Person {
Name = "Jane Moe", Age = 43
};
var sqlQuery = new SqlQuery(typeof(Person), "where Age > ?", 30);
IQueryCursor <ICacheEntry <int, Person> > queryCursor = cache.Query(sqlQuery);
foreach (ICacheEntry <int, Person> cacheEntry in queryCursor)
{
Console.WriteLine(cacheEntry);
}
var fieldsQuery = new SqlFieldsQuery("select name from Person where Age > ? ", 30);
IQueryCursor <IList> quertCursor = cache.QueryFields(fieldsQuery);
foreach (IList fieldsList in quertCursor)
{
Console.WriteLine(fieldsList[0]);
}
var fieldsquery = new SqlFieldsQuery("select sum(Age) from Person");
IQueryCursor <IList> queryCursor1 = cache.QueryFields(fieldsquery);
Console.WriteLine(queryCursor1.GetAll()[0][0]);
}
/** <inheritDoc /> */
public IQueryCursor <T> QueryFields <T>(SqlFieldsQuery qry, Func <IBinaryRawReader, int, T> readerFunc)
{
IgniteArgumentCheck.NotNull(qry, "qry");
IgniteArgumentCheck.NotNull(readerFunc, "readerFunc");
if (string.IsNullOrEmpty(qry.Sql))
{
throw new ArgumentException("Sql cannot be null or empty");
}
var cursor = DoOutOpObject((int)CacheOp.QrySqlFields, writer =>
{
writer.WriteBoolean(qry.Local);
writer.WriteString(qry.Sql);
writer.WriteInt(qry.PageSize);
QueryBase.WriteQueryArgs(writer, qry.Arguments);
writer.WriteBoolean(qry.EnableDistributedJoins);
writer.WriteBoolean(qry.EnforceJoinOrder);
writer.WriteBoolean(qry.Lazy); // Lazy flag.
writer.WriteInt((int)qry.Timeout.TotalMilliseconds);
writer.WriteBoolean(qry.ReplicatedOnly);
writer.WriteBoolean(qry.Colocated);
writer.WriteString(qry.Schema); // Schema
});
return(new FieldsQueryCursor <T>(cursor, _flagKeepBinary, readerFunc));
}
public void TestFieldsQuery()
{
var cache = GetClientCache <Person>();
// All items.
var qry = new SqlFieldsQuery("select Id from Person");
var cursor = cache.Query(qry);
CollectionAssert.AreEquivalent(Enumerable.Range(1, Count), cursor.Select(x => (int)x[0]));
Assert.AreEqual("ID", cursor.FieldNames.Single());
// All items local.
qry.Local = true;
Assert.Greater(Count, cache.Query(qry).Count());
// Filter.
qry = new SqlFieldsQuery("select Name from Person where Id = ?", 1)
{
Lazy = true,
PageSize = 5,
};
Assert.AreEqual("Person 1", cache.Query(qry).Single().Single());
// DateTime.
qry = new SqlFieldsQuery("select Id, DateTime from Person where DateTime > ?", DateTime.UtcNow.AddDays(9));
cursor = cache.Query(qry);
Assert.AreEqual(new[] { "ID", "DATETIME" }, cursor.FieldNames);
Assert.AreEqual(cache[Count].DateTime, cursor.Single().Last());
// Invalid args.
qry.Sql = null;
Assert.Throws <ArgumentNullException>(() => cache.Query(qry));
}
/** <inheritDoc /> */
public IFieldsQueryCursor Query(SqlFieldsQuery sqlFieldsQuery)
{
IgniteArgumentCheck.NotNull(sqlFieldsQuery, "sqlFieldsQuery");
IgniteArgumentCheck.NotNull(sqlFieldsQuery.Sql, "sqlFieldsQuery.Sql");
return(DoOutInOp(ClientOp.QuerySqlFields,
ctx => WriteSqlFieldsQuery(ctx.Writer, sqlFieldsQuery),
ctx => GetFieldsCursor(ctx)));
}
public void TestSqlFieldsQuery([Values(true, false)] bool loc, [Values(true, false)] bool distrJoin,
[Values(true, false)] bool enforceJoinOrder, [Values(true, false)] bool lazy)
{
int cnt = MaxItemCnt;
var cache = Cache();
// 1. Populate cache with data, calculating expected count in parallel.
var exp = PopulateCache(cache, loc, cnt, x => x < 50);
// 2. Validate results.
var qry = new SqlFieldsQuery("SELECT name, age FROM QueryPerson WHERE age < 50")
{
EnableDistributedJoins = distrJoin,
EnforceJoinOrder = enforceJoinOrder,
Colocated = !distrJoin,
#pragma warning disable 618
ReplicatedOnly = false,
#pragma warning restore 618
Local = loc,
Timeout = TimeSpan.FromSeconds(2),
Lazy = lazy
};
using (var cursor = cache.Query(qry))
{
HashSet <int> exp0 = new HashSet <int>(exp);
foreach (var entry in cursor.GetAll())
{
Assert.AreEqual(2, entry.Count);
Assert.AreEqual(entry[0].ToString(), entry[1].ToString());
exp0.Remove((int)entry[1]);
}
Assert.AreEqual(0, exp0.Count);
Assert.AreEqual(new[] { "NAME", "AGE" }, cursor.FieldNames);
}
// Test old API as well.
#pragma warning disable 618
using (var cursor = cache.QueryFields(qry))
{
HashSet <int> exp0 = new HashSet <int>(exp);
foreach (var entry in cursor)
{
Assert.AreEqual(entry[0].ToString(), entry[1].ToString());
exp0.Remove((int)entry[1]);
}
Assert.AreEqual(0, exp0.Count);
}
#pragma warning restore 618
}
/// <summary>
/// Conditional DELETE query: remove non-ASF employees.
/// </summary>
/// <param name="employeeCache">Employee cache.</param>
private static void Delete(ICache <int, Employee> employeeCache)
{
var qry = new SqlFieldsQuery(string.Format(
"delete from Employee where _key in (" +
"select emp._key from Employee emp, \"{0}\".Organization org " +
"where org.Name != ? and org._key = emp.organizationId)", OrganizationCacheName), "ASF");
employeeCache.QueryFields(qry);
}
public static void EnforceJoinOrder()
{
// tag::sqlJoinOrder[]
var query = new SqlFieldsQuery("SELECT * FROM TABLE_A, TABLE_B USE INDEX(HASH_JOIN_IDX) WHERE TABLE_A.column1 = TABLE_B.column2")
{
EnforceJoinOrder = true
};
// end::sqlJoinOrder[]
}
public void TestDml()
{
var cache = GetClientCache <Person>();
var qry = new SqlFieldsQuery("insert into Person (_key, id, name) values (?, ?, ?)", -10, 1, "baz");
var res = cache.Query(qry).GetAll();
Assert.AreEqual(1, res[0][0]);
Assert.AreEqual("baz", cache[-10].Name);
}
/** <inheritDoc /> */
public IFieldsQueryCursor Query(SqlFieldsQuery sqlFieldsQuery)
{
IgniteArgumentCheck.NotNull(sqlFieldsQuery, "sqlFieldsQuery");
IgniteArgumentCheck.NotNull(sqlFieldsQuery.Sql, "sqlFieldsQuery.Sql");
return(DoOutInOp(ClientOp.QuerySqlFields, w => WriteSqlFieldsQuery(w, sqlFieldsQuery),
s => new ClientFieldsQueryCursor(
_ignite, s.ReadLong(), _keepBinary, s, ClientOp.QuerySqlFieldsCursorGetPage,
ClientFieldsQueryCursor.ReadColumns(_marsh.StartUnmarshal(s)))));
}
public void TestBinaryStreamerCreatesSqlRecord()
{
var cacheCfg = new CacheConfiguration
{
Name = "TestBinaryStreamerCreatesSqlRecord",
SqlSchema = "persons",
QueryEntities = new[]
{
new QueryEntity
{
ValueTypeName = "Person",
Fields = new List <QueryField>
{
new QueryField
{
Name = "Name",
FieldType = typeof(string),
},
new QueryField
{
Name = "Age",
FieldType = typeof(int)
}
}
}
}
};
var cacheClientBinary = _grid.GetOrCreateCache <int, IBinaryObject>(cacheCfg)
.WithKeepBinary <int, IBinaryObject>();
// Prepare a binary object.
var jane = _grid.GetBinary().GetBuilder("Person")
.SetStringField("Name", "Jane")
.SetIntField("Age", 43)
.Build();
const int key = 1;
// Stream the binary object to the server.
using (var streamer = _grid.GetDataStreamer <int, IBinaryObject>(cacheCfg.Name))
{
streamer.Add(key, jane);
streamer.Flush();
}
// Check that SQL works.
var query = new SqlFieldsQuery("SELECT Name, Age FROM \"PERSONS\".PERSON");
var fullResultAfterClientStreamer = cacheClientBinary.Query(query).GetAll();
Assert.IsNotNull(fullResultAfterClientStreamer);
Assert.AreEqual(1, fullResultAfterClientStreamer.Count);
Assert.AreEqual("Jane", fullResultAfterClientStreamer[0][0]);
Assert.AreEqual(43, fullResultAfterClientStreamer[0][1]);
}
public void TestUpdateBatchSizeValidation()
{
var cache = GetClientCache <Person>();
var qry = new SqlFieldsQuery("SELECT * FROM Person")
{
UpdateBatchSize = -1
};
var ex = Assert.Throws <IgniteClientException>(() => cache.Query(qry).GetAll());
StringAssert.EndsWith("updateBatchSize cannot be lower than 1", ex.Message);
}
public async Task QueryAsync(string query, string cacheName)
{
try
{
var sqlquery = new SqlFieldsQuery(query);
var client = GetIgnite("QueryAsync");
var cache = client.GetCache <object, object>(cacheName);
var res = cache.Query(sqlquery).GetAll();
}catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
public void TestFieldsQueryTimeout()
{
var cache = GetClientCache <Person>();
cache.PutAll(Enumerable.Range(1, 1000).ToDictionary(x => x, x => new Person(x)));
var qry = new SqlFieldsQuery("select * from Person p0, Person p1, Person p2")
{
Timeout = TimeSpan.FromMilliseconds(1)
};
Assert.Throws <IgniteClientException>(() => cache.Query(qry).GetAll());
}
public void TestSqlFieldsQuery([Values(true, false)] bool loc, [Values(true, false)] bool distrJoin,
[Values(true, false)] bool enforceJoinOrder)
{
int cnt = MaxItemCnt;
var cache = Cache();
// 1. Populate cache with data, calculating expected count in parallel.
var exp = PopulateCache(cache, loc, cnt, x => x < 50);
// 2. Validate results.
var qry = new SqlFieldsQuery("SELECT name, age FROM QueryPerson WHERE age < 50")
{
EnableDistributedJoins = distrJoin,
EnforceJoinOrder = enforceJoinOrder,
Colocated = !distrJoin,
ReplicatedOnly = false,
Local = loc,
Timeout = TimeSpan.FromSeconds(2)
};
using (IQueryCursor <IList> cursor = cache.QueryFields(qry))
{
HashSet <int> exp0 = new HashSet <int>(exp);
foreach (var entry in cursor.GetAll())
{
Assert.AreEqual(2, entry.Count);
Assert.AreEqual(entry[0].ToString(), entry[1].ToString());
exp0.Remove((int)entry[1]);
}
Assert.AreEqual(0, exp0.Count);
}
using (IQueryCursor <IList> cursor = cache.QueryFields(qry))
{
HashSet <int> exp0 = new HashSet <int>(exp);
foreach (var entry in cursor)
{
Assert.AreEqual(entry[0].ToString(), entry[1].ToString());
exp0.Remove((int)entry[1]);
}
Assert.AreEqual(0, exp0.Count);
}
}
public void TestFieldsQueryDistributedJoins()
{
var cache = GetClientCache <Person>();
// Non-distributed join returns incomplete results.
var qry = new SqlFieldsQuery(string.Format(
"select p2.Name from \"{0}\".Person, \"{1}\".Person as p2 where Person.Id = 11 - p2.Id",
CacheName, CacheName2));
Assert.Greater(Count, cache.Query(qry).Count());
// Distributed join fixes the problem.
qry.EnableDistributedJoins = true;
Assert.AreEqual(Count, cache.Query(qry).Count());
}
public IEnumerable <T> ExecuteCollection <T>(QueryModel queryModel)
{
Debug.Assert(queryModel != null);
var qryData = GetQueryData(queryModel);
Debug.WriteLine("\nFields Query: {0} | {1}", qryData.QueryText,
string.Join(", ", qryData.Parameters.Select(x => x == null ? "null" : x.ToString())));
var qry = new SqlFieldsQuery(qryData.QueryText, _local, qryData.Parameters.ToArray());
var selector = GetResultSelector <T>(queryModel.SelectClause.Selector);
return(_cache.QueryFields(qry, selector));
}
public void TestPartitionsValidation()
{
var cache = GetClientCache <Person>();
var qry = new SqlFieldsQuery("SELECT * FROM Person")
{
Partitions = new int[0]
};
var ex = Assert.Throws <IgniteClientException>(() => cache.Query(qry).GetAll());
StringAssert.EndsWith("Partitions must not be empty.", ex.Message);
qry.Partitions = new[] { -1, -2 };
ex = Assert.Throws <IgniteClientException>(() => cache.Query(qry).GetAll());
StringAssert.EndsWith("Illegal partition", ex.Message);
}
public void TestSqlFieldsQueryTimeout()
{
var cache = Cache();
PopulateCache(cache, false, 20000, x => true);
var fieldsQry = new SqlFieldsQuery("SELECT * FROM QueryPerson WHERE age < 5000 AND name like '%0%'")
{
Timeout = TimeSpan.FromMilliseconds(3)
};
// ReSharper disable once ReturnValueOfPureMethodIsNotUsed
var ex = Assert.Throws <CacheException>(() => cache.Query(fieldsQry).ToArray());
Assert.IsTrue(ex.ToString().Contains("QueryCancelledException: The query was cancelled while executing."));
}
