/// <summary> /// Gets sorted list of all Business Locations in the dataset by filter /// </summary> /// <param name="SearchQuery">Query to filter on</param> /// <param name="PageNumber">Current page number</param> /// <param name="PageSize">Number of items per page</param> /// <param name="OrderBy">Column name to sequence the list by</param> /// <param name="OrderByAscDesc">Sort direction</param> /// <returns>object PagedList</returns> public static PagedList<BusinessLocation> GetBusinessLocations(string SearchQuery, int PageNumber, int PageSize, string OrderBy, bool OrderByAscDesc) { //Create client to talk to OpenDat API Endpoint var client = new SodaClient(_APIEndPointHost, _AppToken); //get a reference to the resource itself the result (a Resouce object) is a generic type //the type parameter represents the underlying rows of the resource var dataset = client.GetResource <PagedList<BusinessLocation>>(_APIEndPoint4x4); //Build the select list of columns for the SoQL call string[] columns = new[] { "legal_name", "doing_business_as_name", "date_issued", "city", "state", "zip_code", "latitude", "longitude" }; //Column alias must not collide with input column name, i.e. don't alias 'city' as 'city' string[] aliases = new[] { "LegalName", "DBA", "IssuedOn" }; //using SoQL and a fluent query building syntax var soql = new SoqlQuery().Select(columns) .As(aliases) .Order((OrderByAscDesc) ? SoqlOrderDirection.ASC: SoqlOrderDirection.DESC, new[] { OrderBy }); if(!string.IsNullOrEmpty(SearchQuery)) { soql = new SoqlQuery().FullTextSearch(SearchQuery); } var results = dataset.Query<BusinessLocation>(soql); //page'em cause there might be quite a few PagedList<BusinessLocation> pagedResults = new PagedList<BusinessLocation>(results.ToList(), PageNumber, PageSize); return pagedResults; }
public void Default_Ctor_Orders_By_DefaultOrder_In_DefaultOrderDirection() { string defaultOrderClause = String.Format("{0}={1} {2}", SoqlQuery.OrderKey, String.Join(SoqlQuery.Delimiter, SoqlQuery.DefaultOrder), SoqlQuery.DefaultOrderDirection); string soql = new SoqlQuery().ToString(); StringAssert.Contains(defaultOrderClause, soql); }
public void Select_Clause_Only_Gets_Valid_Columns(params string[] columns) { string expected = String.Format("{0}={1}", SoqlQuery.SelectKey, String.Join(SoqlQuery.Delimiter, columns.Where(c => !String.IsNullOrEmpty(c)))); string soql = new SoqlQuery().Select(columns).ToString(); StringAssert.Contains(expected, soql); }
public void Default_Ctor_Selects_Default() { string defaultSelectClause = String.Format("{0}={1}", SoqlQuery.SelectKey, String.Join(SoqlQuery.Delimiter, SoqlQuery.DefaultSelect)); string soql = new SoqlQuery().ToString(); StringAssert.Contains(defaultSelectClause, soql); }
public void Empty_FullTextSearch_Ignores_Search_Clause() { string startOfSearchClause = String.Format("{0}=", SoqlQuery.SearchKey); string emptySearch = new SoqlQuery().FullTextSearch("").ToString(); string nullSearch = new SoqlQuery().FullTextSearch(null).ToString(); StringAssert.DoesNotContain(startOfSearchClause, emptySearch); StringAssert.DoesNotContain(startOfSearchClause, nullSearch); }
public void Query_Ctor_Sets_RawQuery() { var query = "SELECT something WHERE this > that ORDER BY another"; var expected = String.Format("{0}={1}", SoqlQuery.QueryKey, query); var soql = new SoqlQuery(query).ToString(); Assert.AreEqual(expected, soql); }
public void Having_Clause_Gets_Formatted_Input() { string format = "something > {0}"; string expected = String.Format("{0}={1}", SoqlQuery.HavingKey, String.Format(format, "nothing")); string soql = new SoqlQuery().Having(format, "nothing").ToString(); StringAssert.Contains(expected, soql); }
public void Having_Clause_Gets_Valid_Predicate() { string predicate = "something > nothing"; string expected = String.Format("{0}={1}", SoqlQuery.HavingKey, predicate); string soql = new SoqlQuery().Having(predicate).ToString(); StringAssert.Contains(expected, soql); }
public void Empty_Where_Ignores_Where_Clause() { string startOfWhereClause = String.Format("{0}=", SoqlQuery.WhereKey); string emptyWhere = new SoqlQuery().Where("").ToString(); string nullWhere = new SoqlQuery().Where(null).ToString(); StringAssert.DoesNotContain(startOfWhereClause, emptyWhere); StringAssert.DoesNotContain(startOfWhereClause, nullWhere); }
public void Empty_Having_Ignores_Having_Clause() { string startOfHavingClause = String.Format("{0}=", SoqlQuery.HavingKey); string emptyHaving = new SoqlQuery().Having("").ToString(); string nullHaving = new SoqlQuery().Having(null).ToString(); StringAssert.DoesNotContain(startOfHavingClause, emptyHaving); StringAssert.DoesNotContain(startOfHavingClause, nullHaving); }
public void Select_Clause_Gets_Aliases_As_Lowercase() { string[] columns = new[] { "column1", "column2", "column3" }; string[] aliases = new[] { "Column_A", "COLUMN_B", "CoLuMn_C" }; string soql = new SoqlQuery().Select(columns).As(aliases).ToString(); for (int i = 0; i < columns.Length; i++) { StringAssert.Contains(String.Format("{0} AS {1}", columns[i], aliases[i].ToLower()), soql); } }
public void Select_Clause_Gets_Valid_Aliases_When_Some_Columns_Are_Aliased() { string[] columns = new[] { "column1", "column2", "column3" }; string[] aliases = new[] { "column_a", "column_b", }; string soql = new SoqlQuery().Select(columns).As(aliases).ToString(); for (int i = 0; i < aliases.Length; i++) { StringAssert.Contains(String.Format("{0} AS {1}", columns[i], aliases[i]), soql); } }
public void Empty_Order_Orders_By_DefaultOrder_In_DefaultOrderDirection() { string defaultOrderClause = String.Format("{0}={1} {2}", SoqlQuery.OrderKey, String.Join(SoqlQuery.Delimiter, SoqlQuery.DefaultOrder), SoqlQuery.DefaultOrderDirection); string emptyGroup = new SoqlQuery().Order("").ToString(); string manyEmptyGroup = new SoqlQuery().Order("", "", "").ToString(); string nullGroup = new SoqlQuery().Order(null).ToString(); StringAssert.Contains(defaultOrderClause, emptyGroup); StringAssert.Contains(defaultOrderClause, manyEmptyGroup); StringAssert.Contains(defaultOrderClause, nullGroup); }
public void Empty_Select_Selects_Default() { string defaultSelectClause = String.Format("{0}={1}", SoqlQuery.SelectKey, String.Join(SoqlQuery.Delimiter, SoqlQuery.DefaultSelect)); string emptySelect = new SoqlQuery().Select("").ToString(); string manyEmptySelect = new SoqlQuery().Select("", "", "").ToString(); string nullSelect = new SoqlQuery().Select(null).ToString(); StringAssert.Contains(defaultSelectClause, emptySelect); StringAssert.Contains(defaultSelectClause, manyEmptySelect); StringAssert.Contains(defaultSelectClause, nullSelect); }
public void Empty_Select_Selects_Default() { string defaultSelectClause = String.Format("{0}={1}", SoqlQuery.SelectKey, String.Join(SoqlQuery.Delimiter, SoqlQuery.DefaultSelect)); string emptySelect = new SoqlQuery().Select("").ToString(); string manyEmptySelect = new SoqlQuery().Select("", "", "").ToString(); string nullSelect = new SoqlQuery().Select(null).ToString(); StringAssert.Contains(defaultSelectClause, emptySelect); StringAssert.Contains(defaultSelectClause, manyEmptySelect); StringAssert.Contains(defaultSelectClause, nullSelect); }
public void Empty_Group_Ignores_Group_Clause() { string startOfGroupClause = String.Format("{0}=", SoqlQuery.GroupKey); string emptyGroup = new SoqlQuery().Group("").ToString(); string manyEmptyGroup = new SoqlQuery().Group("", "", "").ToString(); string nullGroup = new SoqlQuery().Group(null).ToString(); StringAssert.DoesNotContain(startOfGroupClause, emptyGroup); StringAssert.DoesNotContain(startOfGroupClause, manyEmptyGroup); StringAssert.DoesNotContain(startOfGroupClause, nullGroup); }
public void get(Location locate, int radius, int limit) { //initialize a new client //make sure you register for your own app token (http://dev.socrata.com/register) var client = new SodaClient("data.cityoftacoma.org", "faxxyxOUEBkwIxlgvMgFaEViQ"); //read metadata of a dataset using the resource identifier (Socrata 4x4) //var metadata = client.GetMetadata("iww5-t4fx"); //Console.WriteLine("{0} has {1} views.", metadata.Name, metadata.ViewsCount); //get a reference to the resource itself //the result (a Resouce object) is a generic type //the type parameter represents the underlying rows of the resource var dataset = client.GetResource <Dictionary <string, object> >("iww5-t4fx"); //collections of an arbitrary type can be returned //using SoQL and a fluent query building syntax string sql = "within_circle(location," + locate.latitude.ToString() + "," + locate.longitude.ToString() + "," + radius.ToString() + ")"; var soql = new SoqlQuery().Where(sql).Limit(limit); var results = dataset.Query(soql); foreach (var row in results) { var tmp = new Fire(); if (0 != row.Count) { //foreach (var vv in row) //Console.WriteLine(vv); try { tmp.intersection_address = row["location_address"].ToString(); tmp.fire_generalcause = row["fire_generalcause"].ToString(); tmp.firetype = row["firetype"].ToString(); tmp.time1 = row["firstunitturnout"].ToString(); tmp.time2 = row["incidentclosed"].ToString(); tmp.date = row["incidentdate"].ToString().Substring(0, 10); tmp.estimatedtotalfireloss = row["estimatedtotalfireloss"].ToString(); tmp.loc.city = row["city"].ToString(); tmp.loc.state = row["state"].ToString(); tmp.loc.zipcode = row["zipcode"].ToString(); tmp.loc.latitude = float.Parse(row["latitude"].ToString()); tmp.loc.longitude = float.Parse(row["longitude"].ToString()); }catch (Exception e) { Console.WriteLine("Error occurred in FireIncident entrance"); } } list.Add(tmp); } }
public void get(Location locate, int radius, int limit) { //https://data.cityoftacoma.org/resource/kjk6-j7c9.json var client = new SodaClient("data.cityoftacoma.org", "faxxyxOUEBkwIxlgvMgFaEViQ"); var dataset = client.GetResource <Dictionary <string, object> >("kjk6-j7c9"); string sql = "within_circle(collision_location," + locate.latitude.ToString() + "," + locate.longitude.ToString() + "," + radius.ToString() + ")"; var soql = new SoqlQuery().Where(sql).Limit(limit); var results = dataset.Query(soql); foreach (var row in results) { if (0 != row.Count) { try { Collision collision = new Collision { }; try { collision.most_severe_injury_type = row["most_severe_injury_type"].ToString(); } catch (Exception e) { } try { collision.lighting_conditions = row["lighting_conditions"].ToString(); } catch (Exception e) { } try { collision.jurisdiction = row["jurisdiction"].ToString(); } catch (Exception e) { } try { collision.weather = row["weather"].ToString(); } catch (Exception e) { } try { collision.date = row["date"].ToString(); }catch (Exception e) {} string collision_location = row["collision_location"].ToString(); string coordinates = collision_location.Substring(collision_location.IndexOf("coordinates") + 13).Trim('[', ']', '}', '{', '\n', '\r', ' ', '\t'); //Console.WriteLine(float.Parse(coordinates.Split(',')[0])); collision.coordinate.latitude = float.Parse(coordinates.Split(',')[1]); collision.coordinate.longitude = float.Parse(coordinates.Split(',')[0]); collision.place = collision.coordinate.reverseGeocoding(); list.Add(collision); } catch (Exception e) { //Console.WriteLine("Error traffic data entrance"); //https://data.cityofchicago.org/resource/6zsd-86xi.json? //$where=date between '2018-02-27T12:00:00' and '2018-03-04T14:00:00' } } } }
public void Select_Clause_With_Aliases_Generates_Valid_SoQL() { string[] columns = new[] { "column1", "column2" }; string[] aliases = new[] { "column_a", "column_b" }; string expected = String.Format(@"{0} AS {1},{2} AS {3}", columns[0], aliases[0], columns[1], aliases[1]); string soql = new SoqlQuery().Select(columns).As(aliases).ToString(); StringAssert.IsMatch(expected, soql); }
public void Last_Select_Overwrites_All_Previous() { string[] first = { "first", "second", "last" }; string[] second = { "first", "second" }; string[] last = { "last" }; string format = String.Format("{0}={{0}}", SoqlQuery.SelectKey); string soql = new SoqlQuery().Select(first) .Select(second) .Select(last) .ToString(); StringAssert.DoesNotContain(String.Format(format, String.Join(SoqlQuery.Delimiter, first)), soql); StringAssert.DoesNotContain(String.Format(format, String.Join(SoqlQuery.Delimiter, second)), soql); StringAssert.Contains(String.Format(format, String.Join(SoqlQuery.Delimiter, last)), soql); }
public Map(SoqlQuery <K> soqlQuery) { // make sure that Map<T, K> is Map<ID, SObject> if (!typeof(SObject).IsAssignableFrom(typeof(K)) || !typeof(ID).IsAssignableFrom(typeof(T))) { throw new NotSupportedException("Only Map<ID, SObject> can be initialized via SOQL query data."); } foreach (object row in soqlQuery.QueryResult.Value) { var sobj = (SObject)row; object key = sobj.Id; this[(T)key] = (K)row; } }
public void Last_Select_Overwrites_All_Previous() { string[] first = { "first", "second", "last" }; string[] second = { "first", "second" }; string[] last = { "last" }; string format = String.Format("{0}={{0}}", SoqlQuery.SelectKey); string soql = new SoqlQuery().Select(first) .Select(second) .Select(last) .ToString(); StringAssert.DoesNotContain(String.Format(format, String.Join(SoqlQuery.Delimiter, first)), soql); StringAssert.DoesNotContain(String.Format(format, String.Join(SoqlQuery.Delimiter, second)), soql); StringAssert.Contains(String.Format(format, String.Join(SoqlQuery.Delimiter, last)), soql); }
public void Last_FullTextSearch_Overwrites_All_Previous() { string first = "first text"; string second = "second text"; string last = "last text"; string format = String.Format("{0}={{0}}", SoqlQuery.SearchKey); string soql = new SoqlQuery().FullTextSearch(first) .FullTextSearch(second) .FullTextSearch(last) .ToString(); StringAssert.DoesNotContain(String.Format(format, first), soql); StringAssert.DoesNotContain(String.Format(format, second), soql); StringAssert.Contains(String.Format(format, last), soql); }
public void Last_Offset_Overwrites_All_Previous() { int first = 1; int second = 2; int last = 3; string format = String.Format("{0}={{0}}", SoqlQuery.OffsetKey); string soql = new SoqlQuery().Offset(first) .Offset(second) .Offset(last) .ToString(); StringAssert.DoesNotContain(String.Format(format, first), soql); StringAssert.DoesNotContain(String.Format(format, second), soql); StringAssert.Contains(String.Format(format, last), soql); }
/// <summary> /// Assisted by code from https://dev.socrata.com/foundry/data.cityofnewyork.us/fhrw-4uyv /// queried DB - https://data.cityofnewyork.us /// API KEY - PVGjhHLj8Svy7Ryz0uJgW9IBh /// loadDB connects to the database, sends the query and then returns the data /// </summary> /// <returns>Returns the dataset of the query to main</returns> public IEnumerable <Dictionary <string, object> > LoadDB(DateTime date) { SODA.SodaClient client = new SodaClient("https://data.cityofnewyork.us", "PVGjhHLj8Svy7Ryz0uJgW9IBh"); /// <remarks> /// The documentation on the web is outdated. /// The .NET library has been updated to no longer allow generic typing. /// You must use either Dictionary(String,Object) - use <> but not allowed in XML comments /// OR a user-defined json serializable class - their documentation does not explain how to do this /// well enough, however so we are sticking with the Generic Collection specified /// </remarks>> SODA.Resource <Dictionary <string, object> > dataset = client.GetResource <Dictionary <string, object> >("fhrw-4uyv"); SoqlQuery soql = this.GetQueryDate(date); IEnumerable <Dictionary <string, object> > results = dataset.Query <Dictionary <string, object> >(soql); return(results); }
public void MapCanBeInitializedWithSoqlQueryResults() { Assert.DoesNotThrow(() => { var lazyData = new LazyOfListOfSample(GetSampleObjects); var query = new SoqlQuery <SampleSObject>(lazyData, string.Empty); var map = new Map <ID, SampleSObject>(query); var item123 = map.get("123"); Assert.NotNull(item123); Assert.AreEqual("OneTwoThree", item123.Name); var item321 = map.get("321"); Assert.NotNull(item321); Assert.AreEqual("ThreeTwoOne", item321.Name); }); }
public void KeySetMethodIsImplemented() { Assert.DoesNotThrow(() => { var lazyData = new LazyOfListOfSample(GetSampleObjects); var query = new SoqlQuery <SampleSObject>(lazyData, string.Empty); var map = new Map <ID, SampleSObject>(query); var keySet = map.keySet(); Assert.NotNull(keySet); Assert.AreEqual(2, keySet.size()); Assert.IsTrue(keySet.contains("123")); Assert.IsTrue(keySet.contains("321")); Assert.IsFalse(keySet.contains("111")); }); }
public void Query_With_UndefinedLimit_UsesMaximum() { var query = new SoqlQuery(); var initialValue = query.LimitValue; try { mockClient.Query <object>(query, StringMocks.ResourceId); } catch (InvalidOperationException) { //pass } Assert.Greater(query.LimitValue, initialValue); Assert.AreEqual(SoqlQuery.MaximumLimit, query.LimitValue); }
public void Empty_Aliases_Are_Ignored() { string[] columns = new[] { "column1", "column2" }; string[] startOfAliases = columns.Select(c => String.Format("{0} AS ", c)).ToArray(); string[] nullAliases = new[] { (string)null, (string)null }; string[] emptyAliases = new[] { "", "" }; string nullSoql = new SoqlQuery().Select(columns).As(nullAliases).ToString(); string emptySoql = new SoqlQuery().Select(columns).As(emptyAliases).ToString(); foreach (string startOfAlias in startOfAliases) { StringAssert.DoesNotContain(startOfAlias, nullSoql); StringAssert.DoesNotContain(startOfAlias, emptySoql); } }
public void Query_Ctor_Takes_Precidence() { var query = "SELECT something WHERE this > that ORDER BY another"; var expected = String.Format("{0}={1}", SoqlQuery.QueryKey, query); var soql = new SoqlQuery(query); var select = soql.Select("column1", "column2").ToString(); var where = soql.Where("that > this").ToString(); var order = soql.Order("yetanother").ToString(); Assert.AreEqual(expected, select); Assert.AreEqual(expected, where); Assert.AreEqual(expected, order); }
public void Last_Having_Overwrites_All_Previous() { string first = "first > 0"; string second = "second > first"; string last = "last > anything"; string format = String.Format("{0}={{0}}", SoqlQuery.HavingKey); string expected = String.Format(format, last); string soql = new SoqlQuery().Having(first) .Having(second) .Having(last) .ToString(); StringAssert.DoesNotContain(String.Format(format, first), soql); StringAssert.DoesNotContain(String.Format(format, second), soql); StringAssert.Contains(String.Format(format, last), soql); }
static void Main(string[] args) { // Get set up data and get data. var client = new SodaClient("https://data.sfgov.org", "tOvbugsbjxkiUpNOfwfNmz4Sk"); string[] columns = { "dayoftheweekstr", "starttime", "endtime", "location", "locationdesc", "applicant", "longitude", "latitude" }; var soql = new SoqlQuery().Select(columns); var dataset = client.GetResource <FoodTruck>("jjew-r69b"); var results = dataset.GetRows(limit: 5000); List <FoodTruck> openTrucks = (from FoodTruck item in results where item.IsOpen(DateTime.Now) select item).ToList(); openTrucks.Sort(); // Queue is for paging results. Queue <FoodTruck> queueTrucks = new Queue <FoodTruck>(openTrucks); Console.WriteLine(openTrucks.Count + " trucks open at " + DateTime.Now.ToString()); Console.WriteLine("NAME - LOCATION"); while (queueTrucks.Count > 0) { for (int i = 0; i < 10; i++) { if (queueTrucks.Count == 0) { break; } queueTrucks.Dequeue().Display(); } Console.ReadKey(); } Console.WriteLine("End of list."); Console.ReadKey(); }
public void Select_Clause_Gets_Valid_Aliases_And_Ignores_Extra_Aliases() { string[] columns = new[] { "column1", "column2" }; string[] aliases = new[] { "column_a", "column_b", "column_c", "column_d" }; string soql = new SoqlQuery().Select(columns).As(aliases).ToString(); for (int i = 0; i < columns.Length; i++) { StringAssert.Contains(String.Format("{0} AS {1}", columns[i], aliases[i]), soql); } string[] extraAliases = aliases.Skip(columns.Length).ToArray(); for (int j = 0; j < extraAliases.Length; j++) { StringAssert.DoesNotContain(String.Format("AS {0}", extraAliases[j]), soql); } }
public void get(Location locate, int radius, int limit) { var client = new SodaClient("data.cityoftacoma.org", "faxxyxOUEBkwIxlgvMgFaEViQ"); var dataset = client.GetResource <Dictionary <string, object> >("vzsr-722t"); string sql = "within_circle(intersection," + locate.latitude.ToString() + "," + locate.longitude.ToString() + "," + radius.ToString() + ")"; var soql = new SoqlQuery().Where(sql).Limit(limit); var results = dataset.Query(soql); foreach (var row in results) { var tmp = new Crime(); if (0 != row.Count) { //foreach (var vv in row) //Console.WriteLine(vv); try { tmp.intersection_address = row["intersection_address"].ToString(); tmp.date = row["occurred_on"].ToString().Substring(0, 10); tmp.time = row["approximate_time"].ToString(); tmp.crime = row["crime"].ToString(); tmp.loc.city = row["intersection_city"].ToString(); tmp.loc.state = row["intersection_state"].ToString(); tmp.loc.zipcode = locate.zipcode; //longthy string intersection = row["intersection"].ToString(); String coordinates = intersection.Substring(intersection.IndexOf("coordinates") + 13).Trim('[', ']', '}', '{', '\n', '\r', ' ', '\t'); //Console.WriteLine(coordinates.Split(',')[1]); tmp.loc.latitude = float.Parse(coordinates.Split(',')[1]); tmp.loc.longitude = float.Parse(coordinates.Split(',')[0]); }catch (Exception e) { Console.WriteLine("Error occurred in Crime entrance"); } } list.Add(tmp); } }
public void All_Query_Methods_Return_The_Original_Instance() { var original = new SoqlQuery(); var select = original.Select("something"); var where = original.Where("something"); var order = original.Order(SoqlOrderDirection.DESC, "something"); var group = original.Group("something"); var limit = original.Limit(10); var offset = original.Offset(10); var search = original.FullTextSearch("something"); Assert.AreSame(original, select); Assert.AreSame(original, where); Assert.AreSame(original, order); Assert.AreSame(original, group); Assert.AreSame(original, limit); Assert.AreSame(original, offset); Assert.AreSame(original, search); }
public static PagedList <BusinessLocation> GetBusinessLocations(string SearchQuery, int PageNumber, int PageSize, string OrderBy, bool OrderByAscDesc) { var client = new SodaClient(_APIEndPointHost, _AppToken); var dataset = client.GetResource <PagedList <BusinessLocation> >(_APIEndPoint4x4); var columns = new[] { "company_name", "sub_subindustry", "phone", " location_1" }; var aliases = new[] { "CompanyName", "SubIndustry", "fone", "Location" }; var soql = new SoqlQuery().Select(columns) .As(aliases) .Order((OrderByAscDesc) ? SoqlOrderDirection.ASC : SoqlOrderDirection.DESC, new[] { OrderBy }); if (!string.IsNullOrWhiteSpace(SearchQuery)) { soql = new SoqlQuery().FullTextSearch(SearchQuery); } var results = dataset.Query <BusinessLocation>(soql); PagedList <BusinessLocation> pagedResults = new PagedList <BusinessLocation>(results.ToList(), PageNumber, PageSize); return(pagedResults); }
public void Search_Clause_Gets_FormattedInput() { string format = "search term is {0}"; string expected = String.Format("{0}={1}", SoqlQuery.SearchKey, String.Format(format, "test")); string soql = new SoqlQuery().FullTextSearch(format, "test").ToString(); StringAssert.Contains(expected, soql); }
public void Search_Clause_Gets_FullTextSearch(string searchText) { string expected = String.Format("{0}={1}", SoqlQuery.SearchKey, searchText); string soql = new SoqlQuery().FullTextSearch(searchText).ToString(); StringAssert.Contains(expected, soql); }
public void Empty_FullTextSearch_Ignores_Search_Clause() { string startOfSearchClause = String.Format("{0}=", SoqlQuery.SearchKey); string emptySearch = new SoqlQuery().FullTextSearch("").ToString(); string nullSearch = new SoqlQuery().FullTextSearch(null).ToString(); StringAssert.DoesNotContain(startOfSearchClause, emptySearch); StringAssert.DoesNotContain(startOfSearchClause, nullSearch); }
public void Where_Clause_Gets_Valid_Predicate() { string predicate = "something > nothing"; string expected = String.Format("{0}={1}", SoqlQuery.WhereKey, predicate); string soql = new SoqlQuery().Where(predicate).ToString(); StringAssert.Contains(expected, soql); }
public void Empty_Aliases_Are_Ignored() { string[] columns = new[] { "column1", "column2" }; string[] startOfAliases = columns.Select(c => String.Format("{0} AS ", c)).ToArray(); string[] nullAliases = new[] { (string)null, (string)null }; string[] emptyAliases = new[] { "", "" }; string nullSoql = new SoqlQuery().Select(columns).As(nullAliases).ToString(); string emptySoql = new SoqlQuery().Select(columns).As(emptyAliases).ToString(); foreach (string startOfAlias in startOfAliases) { StringAssert.DoesNotContain(startOfAlias, nullSoql); StringAssert.DoesNotContain(startOfAlias, emptySoql); } }
public void ForQuery_With_Valid_Arguments_Creates_Query_Uri() { SoqlQuery soqlQuery = new SoqlQuery(); var uri = SodaUri.ForQuery(StringMocks.Host, StringMocks.ResourceId, soqlQuery); StringAssert.AreEqualIgnoringCase(String.Format("/resource/{0}", StringMocks.ResourceId), uri.LocalPath); StringAssert.AreEqualIgnoringCase(String.Format("?{0}", Uri.EscapeUriString(soqlQuery.ToString())), uri.Query); }
public void Empty_Group_Ignores_Group_Clause() { string startOfGroupClause = String.Format("{0}=", SoqlQuery.GroupKey); string emptyGroup = new SoqlQuery().Group("").ToString(); string manyEmptyGroup = new SoqlQuery().Group("", "", "").ToString(); string nullGroup = new SoqlQuery().Group(null).ToString(); StringAssert.DoesNotContain(startOfGroupClause, emptyGroup); StringAssert.DoesNotContain(startOfGroupClause, manyEmptyGroup); StringAssert.DoesNotContain(startOfGroupClause, nullGroup); }
public void Empty_Order_Orders_By_DefaultOrder_In_DefaultOrderDirection() { string defaultOrderClause = String.Format("{0}={1} {2}", SoqlQuery.OrderKey, String.Join(SoqlQuery.Delimiter, SoqlQuery.DefaultOrder), SoqlQuery.DefaultOrderDirection); string emptyGroup = new SoqlQuery().Order("").ToString(); string manyEmptyGroup = new SoqlQuery().Order("", "", "").ToString(); string nullGroup = new SoqlQuery().Order(null).ToString(); StringAssert.Contains(defaultOrderClause, emptyGroup); StringAssert.Contains(defaultOrderClause, manyEmptyGroup); StringAssert.Contains(defaultOrderClause, nullGroup); }
public void Order_Clause_Gets_Order_Direction_And_Valid_Columns(SoqlOrderDirection direction, params string[] columns) { string expected = String.Format("{0}={1} {2}", SoqlQuery.OrderKey, String.Join(SoqlQuery.Delimiter, columns.Where(c => !String.IsNullOrEmpty(c))), direction); string soql = new SoqlQuery().Order(direction, columns).ToString(); StringAssert.Contains(expected, soql); }
public void Last_Where_Overwrites_All_Previous() { string first = "first > 0"; string second = "second > first"; string last = "last > anything"; string format = String.Format("{0}={{0}}", SoqlQuery.WhereKey); string expected = String.Format(format, last); string soql = new SoqlQuery().Where(first) .Where(second) .Where(last) .ToString(); StringAssert.DoesNotContain(String.Format(format, first), soql); StringAssert.DoesNotContain(String.Format(format, second), soql); StringAssert.Contains(String.Format(format, last), soql); }
public void Where_Clause_Gets_Formatted_Input() { string format = "something > {0}"; string expected = String.Format("{0}={1}", SoqlQuery.WhereKey, String.Format(format, "nothing")); string soql = new SoqlQuery().Where(format, "nothing").ToString(); StringAssert.Contains(expected, soql); }
public void Select_Clause_Gets_Valid_Aliases_And_Ignores_Extra_Aliases() { string[] columns = new[] { "column1", "column2" }; string[] aliases = new[] { "column_a", "column_b", "column_c", "column_d" }; string soql = new SoqlQuery().Select(columns).As(aliases).ToString(); for (int i = 0; i < columns.Length; i++) { StringAssert.Contains(String.Format("{0} AS {1}", columns[i], aliases[i]), soql); } string[] extraAliases = aliases.Skip(columns.Length).ToArray(); for (int j = 0; j < extraAliases.Length; j++) { StringAssert.DoesNotContain(String.Format("AS {0}", extraAliases[j]), soql); } }
public void Last_FullTextSearch_Overwrites_All_Previous() { string first = "first text"; string second = "second text"; string last = "last text"; string format = String.Format("{0}={{0}}", SoqlQuery.SearchKey); string soql = new SoqlQuery().FullTextSearch(first) .FullTextSearch(second) .FullTextSearch(last) .ToString(); StringAssert.DoesNotContain(String.Format(format, first), soql); StringAssert.DoesNotContain(String.Format(format, second), soql); StringAssert.Contains(String.Format(format, last), soql); }
public void Limit_Less_Than_One_Throws_ArgumentOutOfRangeException(int limit) { var soql = new SoqlQuery().Limit(limit); }
public void All_Query_Methods_Return_The_Original_Instance() { var original = new SoqlQuery(); var select = original.Select("something"); var where = original.Where("something"); var order = original.Order(SoqlOrderDirection.DESC, "something"); var group = original.Group("something"); var limit = original.Limit(10); var offset = original.Offset(10); var search = original.FullTextSearch("something"); Assert.AreSame(original, select); Assert.AreSame(original, where); Assert.AreSame(original, order); Assert.AreSame(original, group); Assert.AreSame(original, limit); Assert.AreSame(original, offset); Assert.AreSame(original, search); }
public void Limit_Clause_Has_A_Ceiling_At_MaximumLimit(int limit) { string expected = String.Format("{0}={1}", SoqlQuery.LimitKey, Math.Min(limit, SoqlQuery.MaximumLimit)); string soql = new SoqlQuery().Limit(limit).ToString(); StringAssert.Contains(expected, soql); }
/// <summary> /// Create a Uri for querying the specified resource on the specified Socrata host, using the specified SoqlQuery object. /// </summary> /// <param name="socrataHost">The Socrata host to target.</param> /// <param name="resourceId">The identifier (4x4) for a resource on the Socrata host to target.</param> /// <param name="soqlQuery">A SoqlQuery object to use for querying.</param> /// <returns>A query Uri for the specified resource on the specified Socrata host.</returns> public static Uri ForQuery(string socrataHost, string resourceId, SoqlQuery soqlQuery) { if (String.IsNullOrEmpty(socrataHost)) throw new ArgumentException("socrataHost", "Must provide a Socrata host to target."); if (FourByFour.IsNotValid(resourceId)) throw new ArgumentOutOfRangeException("resourceId", "The provided resourceId is not a valid Socrata (4x4) resource identifier."); if (soqlQuery == null) throw new ArgumentNullException("soqlQuery", "Must provide a valid SoqlQuery object"); string url = metadataUrl(socrataHost, resourceId).Replace("views", "resource"); string queryUrl = Uri.EscapeUriString(String.Format("{0}?{1}", url, soqlQuery.ToString())); return new Uri(queryUrl); }
public void Offset_Less_Than_Zero_Throws_ArgumentOutOfRangeException(int offset) { var soql = new SoqlQuery().Offset(offset); }
public void Select_Clause_Gets_Valid_Aliases_When_All_Columns_Are_Aliased() { string[] columns = new[] { "column1", "column2", "column3" }; string[] aliases = new[] { "column_a", "column_b", "column_c" }; string soql = new SoqlQuery().Select(columns).As(aliases).ToString(); for (int i = 0; i < columns.Length; i++) { StringAssert.Contains(String.Format("{0} AS {1}", columns[i], aliases[i]), soql); } }
public void Last_Offset_Overwrites_All_Previous() { int first = 1; int second = 2; int last = 3; string format = String.Format("{0}={{0}}", SoqlQuery.OffsetKey); string soql = new SoqlQuery().Offset(first) .Offset(second) .Offset(last) .ToString(); StringAssert.DoesNotContain(String.Format(format, first), soql); StringAssert.DoesNotContain(String.Format(format, second), soql); StringAssert.Contains(String.Format(format, last), soql); }
public void Select_Clause_With_Aliases_Generates_Valid_SoQL() { string[] columns = new[] { "column1", "column2" }; string[] aliases = new[] { "column_a", "column_b" }; string expected = String.Format(@"{0} AS {1},\s?{2} AS {3}[^,]", columns[0], aliases[0], columns[1], aliases[1]); string soql = new SoqlQuery().Select(columns).As(aliases).ToString(); StringAssert.IsMatch(expected, soql); }
public void Select_Clause_Gets_Aliases_As_Lowercase() { string[] columns = new[] { "column1", "column2", "column3" }; string[] aliases = new[] { "Column_A", "COLUMN_B", "CoLuMn_C" }; string soql = new SoqlQuery().Select(columns).As(aliases).ToString(); for (int i = 0; i < columns.Length; i++) { StringAssert.Contains(String.Format("{0} AS {1}", columns[i], aliases[i].ToLower()), soql); } }
public void Select_Clause_Selects_Unaliased_Columns_When_Some_Columns_Are_Aliased() { string[] columns = new[] { "column1", "column2", "column3", "column4", "column5" }; string[] aliases = new[] { "column_a", "column_b" }; string soql = new SoqlQuery().Select(columns).As(aliases).ToString(); StringAssert.Contains(String.Join(SoqlQuery.Delimiter, columns.Skip(aliases.Length)), soql); }
public void Empty_Where_Ignores_Where_Clause() { string startOfWhereClause = String.Format("{0}=", SoqlQuery.WhereKey); string emptyWhere = new SoqlQuery().Where("").ToString(); string nullWhere = new SoqlQuery().Where(null).ToString(); StringAssert.DoesNotContain(startOfWhereClause, emptyWhere); StringAssert.DoesNotContain(startOfWhereClause, nullWhere); }