public void TranslateAnyToJoin_WhenQueriedBasedOnMultipleChildProperty()
{
HttpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=products/any(p: p/name eq 'test') and locations/any(l: l/name eq 'test2')");
var oDataQueryOptions = new ODataQueryOptions(ODataQueryContext, HttpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT VALUE c FROM c JOIN p IN c.products JOIN l IN c.locations WHERE p.name = 'test' AND l.name = 'test2' ", sqlQuery);
}
public void TranslateGeoIntersectsSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=geo.intersects(area, geography'POLYGON((31.8 -5, 32 -5, 32 -4.7, 31.8 -4.7, 31.8 -5))')");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT * FROM c WHERE ST_INTERSECTS(c.area,{\"type\":\"Polygon\",\"coordinates\":[[[31.8,-5.0],[32.0,-5.0],[32.0,-4.7],[31.8,-4.7],[31.8,-5.0]]]})", sqlQuery);
}
public void TranslateSelectWithEnumSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$select=enumNumber, id");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.SELECT_CLAUSE);
Assert.AreEqual("SELECT c.enumNumber, c.id FROM c ", sqlQuery);
}
public void TranslateConcatSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=concat(englishName, ' Canada') eq 'Microsoft Canada'");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT * FROM c WHERE CONCAT(c.englishName,' Canada') = 'Microsoft Canada'", sqlQuery);
}
public void TranslateCountSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$count=true&$filter=englishName eq 'Microsoft'");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.SELECT_CLAUSE | TranslateOptions.WHERE_CLAUSE);
Assert.AreEqual("SELECT VALUE COUNT(1) FROM c WHERE c.englishName = 'Microsoft'", sqlQuery);
}
public void TranslateUpperAndLowerSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=toupper(englishName) eq 'MICROSOFT' or tolower(englishName) eq 'microsoft'");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT * FROM c WHERE UPPER(c.englishName) = 'MICROSOFT' OR LOWER(c.englishName) = 'microsoft'", sqlQuery);
}
public void TranslateIndexOfSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=indexof(englishName,'soft') eq 4");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT * FROM c WHERE INDEX_OF(c.englishName,'soft') = 4", sqlQuery);
}
public void TranslateAnyToJoin_QueryBasedOnCorrectOrder_WhenConditionIsBasedOnId()
{
HttpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=competitor/competitorTwo/locations/any(j:j/id eq 'test')");
var oDataQueryOptions = new ODataQueryOptions(ODataQueryContext, HttpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT VALUE c FROM c JOIN j IN c.competitor.competitorTwo.locations WHERE j.id = 'test' ", sqlQuery);
}
public void TranslateAnyToJoin_WhenThereIsOneNestedjoin()
{
HttpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=competitor/locations/any(j:j/locations/any(l:l/id eq 'test'))");
var oDataQueryOptions = new ODataQueryOptions(ODataQueryContext, HttpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT VALUE c FROM c JOIN j IN c.competitor.locations JOIN l IN j.locations WHERE l.id = 'test' ", sqlQuery);
}
public void TranslateAnyToJoin_WhenQueriedBasedOnChildPropertyDifferentLetter()
{
HttpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=products/any(j:j/name eq 'test')");
var oDataQueryOptions = new ODataQueryOptions(ODataQueryContext, HttpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT VALUE c FROM c JOIN j IN c.products WHERE j.name = 'test' ", sqlQuery);
}
public void TranslateAnyToJoin_Works_WhenCollectionIsInThirdChildElement()
{
HttpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=competitor/competitor/competitor/locations/any(j:j/name eq 'test')");
var oDataQueryOptions = new ODataQueryOptions(ODataQueryContext, HttpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT VALUE c FROM c JOIN j IN c.competitor.competitor.competitor.locations WHERE j.name = 'test' ", sqlQuery);
}
public void TranslateAnyToJoin_MixedLevelProperties()
{
HttpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=competitors/any(p: p/name eq 'test') and englishName eq 'test1'");
var oDataQueryOptions = new ODataQueryOptions(ODataQueryContext, HttpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT VALUE c FROM c JOIN p IN c.competitors WHERE p.name = 'test' AND c.englishName = 'test1' ", sqlQuery);
}
public void TranslateAnyToJoin_WhenChildHasFieldIdBasedOnAnotherField()
{
HttpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=competitors/any(p: p/name eq 'test')");
var oDataQueryOptions = new ODataQueryOptions(ODataQueryContext, HttpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT VALUE c FROM c JOIN p IN c.competitors WHERE p.name = 'test' ", sqlQuery);
}
public void TranslateAnyToJoin_WhenChildHasFieldId()
{
HttpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=competitors/any(p: p/id eq '6a7ad0aa-678e-40f9-8cdf-03e3ab4a4106')");
var oDataQueryOptions = new ODataQueryOptions(ODataQueryContext, HttpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT VALUE c FROM c JOIN p IN c.competitors WHERE p.id = '6a7ad0aa-678e-40f9-8cdf-03e3ab4a4106' ", sqlQuery);
}
public void TranslateSelectOrderBySample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=property ne 'str1'&$orderby=companyId desc,id asc");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT * FROM c WHERE c.property != 'str1' ORDER BY c.companyId DESC, c.id ASC ", sqlQuery);
}
public void TranslateAnyToJoin_WhenThereIsOneNestedjoinWithComplexPath()
{
HttpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=payload/bet/legs/any(l: l/outcomes/any(o: o/id eq 'test'))");
var oDataQueryOptions = new ODataQueryOptions(ODataQueryContext, HttpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT VALUE c FROM c JOIN l IN c.payload.bet.legs JOIN o IN l.outcomes WHERE o.id = 'test' ", sqlQuery);
}
public void TranslateEndswithSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=endswith(englishName, 'Microsoft')");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT * FROM c WHERE ENDSWITH(c.englishName,'Microsoft')", sqlQuery);
}
public void TranslateSelectTopSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$select=p1, p2, p3&$filter=property ne 'str1'&$orderby=companyId DESC,id ASC&$top=15");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.SELECT_CLAUSE | TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT TOP 15 c.p1, c.p2, c.p3 FROM c ", sqlQuery);
}
public void TranslateLengthSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=length(englishName) ge 10 and length(englishName) lt 15");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT * FROM c WHERE LENGTH(c.englishName) >= 10 AND LENGTH(c.englishName) < 15", sqlQuery);
}
public void TranslateWhereSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost?$filter=englishName eq 'Microsoft' and intField le 5");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.WHERE_CLAUSE);
Assert.AreEqual("WHERE c.englishName = 'Microsoft' AND c.intField <= 5", sqlQuery);
}
public void TranslateSubstringSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=substring(englishName, 1, length(englishName)) eq 'icrosoft'");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT * FROM c WHERE SUBSTRING(c.englishName,1,LENGTH(c.englishName)) = 'icrosoft'", sqlQuery);
}
public void TranslateWhereSampleWithGUID()
{
httpRequestMessage.RequestUri = new Uri("http://localhost?$filter=id eq 2ED27DF5-F505-4A06-B168-7321C6B4AD0C");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.WHERE_CLAUSE);
Assert.AreEqual("WHERE c.id = '2ed27df5-f505-4a06-b168-7321c6b4ad0c'", sqlQuery);
}
public void TranslateMasterSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/Post?$select=id, englishName&$filter=title eq 'title1' and property/field ne 'val' or viewedCount ge 5 and (likedCount ne 3 or enumNumber eq azure_documentdb_odata_sql_tests.MockEnum'TWO')&$orderby=_lastClientEditedDateTime asc, createdDateTime desc&$top=30");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL, "c._t = 'dataType'");
Assert.AreEqual("SELECT TOP 30 c.id, c.englishName FROM c WHERE c._t = 'dataType' AND c.title = 'title1' AND c.property.field != 'val' OR c.viewedCount >= 5 AND (c.likedCount != 3 OR c.enumNumber = 'TWO') ORDER BY c._lastClientEditedDateTime ASC, c.createdDateTime DESC ", sqlQuery);
}
public void TranslateWhereWithEnumSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost?$filter=enumNumber eq azure_documentdb_odata_sql_tests.MockEnum'ONE' and intField le 5");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.WHERE_CLAUSE);
Assert.AreEqual("WHERE c.enumNumber = 'ONE' AND c.intField <= 5", sqlQuery);
}
public void TranslateGeoDistanceSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=geo.distance(location, geography'POINT(31.9 -4.8)') lt 100");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT * FROM c WHERE ST_DISTANCE(c.location,{\"type\":\"Point\",\"coordinates\":[31.9,-4.8]}) < 100", sqlQuery);
}
public void TranslateWhereWithNextedFieldsSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost?$filter=parent/child eq 'childValue' and intField le 5");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.WHERE_CLAUSE);
Assert.AreEqual("WHERE c.parent.child = 'childValue' AND c.intField <= 5", sqlQuery);
}
public void TranslateSelectAllSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.SELECT_CLAUSE);
Assert.AreEqual("SELECT * FROM c ", sqlQuery);
}
public void TranslateSelectWhereSample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost?$filter=englishName eq 'Microsoft'");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.SELECT_CLAUSE | TranslateOptions.WHERE_CLAUSE, "c.dataType = 'MockOpenType'");
Assert.AreEqual("SELECT * FROM c WHERE c.dataType = 'MockOpenType' AND c.englishName = 'Microsoft'", sqlQuery);
}
public void TranslateAnySample()
{
httpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=companies/any(p: p/id eq 'abc' or p/name eq 'blaat')");
var oDataQueryOptions = new ODataQueryOptions(oDataQueryContext, httpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.SELECT_CLAUSE | TranslateOptions.WHERE_CLAUSE);
Assert.AreEqual("SELECT VALUE c FROM c JOIN a IN c.companies WHERE a.id = 'abc' OR a.name = 'blaat'", sqlQuery);
}
public void TranslateAnyToJoin_WhenQueriedBasedOnChildPropertyBoolean()
{
HttpRequestMessage.RequestUri = new Uri("http://localhost/User?$filter=products/any(p: p/shipped eq true)");
var oDataQueryOptions = new ODataQueryOptions(ODataQueryContext, HttpRequestMessage);
var oDataToSqlTranslator = new ODataToSqlTranslator(new SQLQueryFormatter());
var sqlQuery = oDataToSqlTranslator.Translate(oDataQueryOptions, TranslateOptions.ALL & ~TranslateOptions.TOP_CLAUSE);
Assert.AreEqual("SELECT VALUE c FROM c JOIN p IN c.products WHERE p.shipped = true ", sqlQuery);
}
