public object FindAll(string search, string token)
{
if (search.Length == 3)
{
// LAX
var query =
new QueryRequest("SELECT airportname FROM `" + CouchbaseConfigHelper.Instance.Bucket + "` WHERE faa=$1")
.AddPositionalParameter(search.ToUpper());
return CouchbaseStorageHelper.Instance.ExecuteQuery(query).Rows;
}
else if (search.Length == 4)
{
// KLAX
var query =
new QueryRequest("SELECT airportname FROM `" + CouchbaseConfigHelper.Instance.Bucket + "` WHERE icao = '$1'")
.AddPositionalParameter(search.ToUpper());
return CouchbaseStorageHelper.Instance.ExecuteQuery(query).Rows;
}
else
{
// Los Angeles
var query =
new QueryRequest("SELECT airportname FROM `" + CouchbaseConfigHelper.Instance.Bucket + "` WHERE airportname LIKE $1")
.AddPositionalParameter("%" + search + "%");
return CouchbaseStorageHelper.Instance.ExecuteQuery(query).Rows;
}
}
public override Task ReplayMessagesAsync(string persistenceId, long fromSequenceNr, long toSequenceNr, long max, Action<IPersistentRepresentation> replayCallback)
{
// Limit(0) doesn't work...
if (max == 0)
return Task.Run(() => {});
// Limit allows only integer
var maxValue = max >= int.MaxValue ? int.MaxValue : (int)max;
var sender = Context.Sender;
String N1QLQueryString = "SELECT `" + _CBBucket.Name + "`.* FROM `" + _CBBucket.Name + "` WHERE PersistenceId = $PersistenceId AND SequenceNr >= $FromSequenceNr AND SequenceNr <= $ToSequenceNr ORDER BY SequenceNr ASC LIMIT $Maximum";
IQueryRequest N1QLQueryRequest = new QueryRequest()
.Statement(N1QLQueryString)
.AddNamedParameter("PersistenceId", persistenceId)
.AddNamedParameter("FromSequenceNr", fromSequenceNr)
.AddNamedParameter("ToSequenceNr", toSequenceNr)
.AddNamedParameter("Maximum", maxValue)
.AdHoc(false);
Couchbase.N1QL.IQueryResult<JournalEntry> result = _CBBucket.Query<JournalEntry>(N1QLQueryRequest);
return result.Rows.ForEachAsync<JournalEntry>(je => replayCallback(ToPersistanceRepresentation(je, sender)));
}
protected override Task<SelectedSnapshot> LoadAsync(string persistenceId, SnapshotSelectionCriteria criteria)
{
// Create a Query with dynamic parameters
string N1QLQueryString = "select `" + _CBBucket.Name + "`.* from `" + _CBBucket.Name + "` where DocumentType = 'SnapshotEntry' AND PersistenceId = $PersistenceId ";
IQueryRequest N1QLQueryRequest = new QueryRequest()
.AddNamedParameter("PersistenceId", persistenceId);
string N1QLQueryOrderByClauseString = "ORDER BY SequenceNr DESC";
if (criteria.MaxSequenceNr > 0 && criteria.MaxSequenceNr < long.MaxValue)
{
N1QLQueryString += "AND SequenceNr <= $limit ";
N1QLQueryOrderByClauseString = "ORDER BY SequenceNr DESC,";
N1QLQueryRequest.AddNamedParameter("limit",criteria.MaxSequenceNr);
}
if (criteria.MaxTimeStamp != DateTime.MinValue && criteria.MaxTimeStamp != DateTime.MaxValue)
{
N1QLQueryString += " AND Timestamp <= $timelimit ";
N1QLQueryOrderByClauseString = "ORDER BY Timestamp DESC,";
N1QLQueryRequest.AddNamedParameter("timelimit", criteria.MaxTimeStamp.Ticks.ToString());
}
N1QLQueryString += N1QLQueryOrderByClauseString.TrimEnd(',') + " LIMIT 1";
N1QLQueryRequest.Statement(N1QLQueryString).AdHoc(false);
return taskLoadAsync(N1QLQueryRequest);
}
public void Test_Statement()
{
var query = new QueryRequest().
BaseUri(new Uri(string.Format("http://{0}:8093/query", _server))).
Statement("SELECT * FROM default");
var values = query.GetFormValues();
Assert.AreEqual("SELECT * FROM default", values["statement"]);
}
public void Test_Statement()
{
var query = new QueryRequest().
BaseUri(new Uri(string.Format("http://{0}:8093/query", _server))).
Statement("SELECT * FROM default");
var uri = query.GetRequestUri();
Assert.IsTrue(uri.ToString().Contains(":8093/query?statement=SELECT * FROM default"));
Console.WriteLine(uri);
}
public void Test_Query_HelloWorld()
{
var client = GetQueryClient();
var uri = new Uri(string.Format("http://{0}:8093/query", _server));
var query = new QueryRequest("SELECT 'Hello World' AS Greeting").BaseUri(uri);
var result = client.Query<dynamic>(query);
Assert.IsNotNull(result);
Assert.AreEqual("Hello World", result.Rows.First().Greeting.ToString());
}
public void Test_Query_HelloWorld()
{
var config = new ClientConfiguration();
var client = new QueryClient(new HttpClient(), new JsonDataMapper(config), new BucketConfig(), config);
var uri = new Uri(string.Format("http://{0}:8093/query", _server));
var query = new QueryRequest("SELECT 'Hello World' AS Greeting").BaseUri(uri);
var result = client.Query<dynamic>(query);
Assert.IsNotNull(result);
Assert.AreEqual("Hello World", result.Rows.First().Greeting.ToString());
}
public async Task <string> Couchbase_QueryRequestAsync()
{
var bucketName = CouchbaseTestObject.CouchbaseTestBucket;
var bucket = _connection.Bucket;
var queryRequest = new Couchbase.N1QL.QueryRequest("SELECT * FROM `" + bucketName + "` LIMIT 10");
var result = await bucket.QueryAsync <QueryRequest>(queryRequest);
return(result.Rows.Count().ToString());
}
public void Test_Positional_Parameters()
{
var query = new QueryRequest().
BaseUri(new Uri(string.Format("http://{0}:8093/query", _server))).
Statement("SELECT * FROM default WHERE type=$1").
AddPositionalParameter("dog");
var values = query.GetFormValues();
Assert.AreEqual("SELECT * FROM default WHERE type=$1", values["statement"]);
Assert.AreEqual(new[] {"dog"}, values["args"]);
}
public void Test_Statement_ClientContextId_Pretty()
{
var query = new QueryRequest().
BaseUri(new Uri(string.Format("http://{0}:8093/query", _server))).
Statement("SELECT * FROM default").
ClientContextId("somecontextlessthanorequalto64chars").
Pretty(true);
var values = query.GetFormValues();
Assert.AreEqual(true, values["pretty"]);
}
public void Test_Statement_ClientContextId()
{
var query = new QueryRequest().
BaseUri(new Uri(string.Format("http://{0}:8093/query", _server))).
Statement("SELECT * FROM default").
ClientContextId("somecontextlessthanorequalto64chars");
var values = query.GetFormValues();
string contextid = values["client_context_id"].ToString().Split(new String[] {"::"}, System.StringSplitOptions.None)[0];
Assert.AreEqual("somecontextlessthanorequalto64chars", contextid);
}
public void Test_Statement_ClientContextId()
{
var query = new QueryRequest().
BaseUri(new Uri(string.Format("http://{0}:8093/query", _server))).
Statement("SELECT * FROM default").
ClientContextId("somecontextlessthanorequalto64chars");
var uri = query.GetRequestUri();
Console.WriteLine(uri);
Assert.IsTrue(uri.ToString().Contains(":8093/query?statement=SELECT * FROM default&client_context_id=somecontextlessthanorequalto64chars"));
}
public void Test_N1QL_Query()
{
using (var bucket = _cluster.OpenBucket())
{
var query = new QueryRequest("SELECT * FROM `beer-sample` LIMIT 10");
var result = bucket.Query<dynamic>(query);
Assert.IsTrue(result.Success);
Assert.AreEqual(10, result.Rows.Count);
}
}
public void Test_Positional_Parameters()
{
var query = new QueryRequest().
BaseUri(new Uri(string.Format("http://{0}:8093/query", _server))).
Statement("SELECT * FROM default WHERE type=$1").
AddPositionalParameter("dog");
var uri = query.GetRequestUri();
Console.WriteLine(uri);
Assert.IsTrue(uri.ToString().Contains(":8093/query?statement=SELECT * FROM default WHERE type=$1&args=[\"dog\"]"));
}
public void Test_Statement()
{
var query = new QueryRequest().
BaseUri(new Uri(string.Format("http://{0}:8093/query", _server))).
Statement("SELECT * FROM default");
var json = query.GetFormValuesAsJson();
var expected = "{\"statement\":\"SELECT * FROM default\",\"timeout\":\"75000ms\"}";
Console.WriteLine(json);
Assert.AreEqual(expected, json);
}
public string Couchbase_QueryRequest()
{
var bucketName = CouchbaseTestObject.CouchbaseTestBucket;
var bucket = _connection.Bucket;
var queryRequest = new Couchbase.N1QL.QueryRequest("SELECT * FROM `" + bucketName + "` LIMIT 10");
var result = bucket.Query <dynamic>(queryRequest);
return(result.Rows.Count().ToString());
}
public void When_Bucket_Has_No_PrimaryIndex_Status_Is_Fatal()
{
using (var bucket = _cluster.OpenBucket())
{
var queryRequest = new QueryRequest()
.Statement("SELECT * FROM `default` LIMIT 10");
var result = bucket.Query<dynamic>(queryRequest);
Assert.IsFalse(result.Success);
Assert.AreEqual(QueryStatus.Fatal, result.Status);
}
}
public void Test_Statement_ClientContextId()
{
var query = new QueryRequest().
BaseUri(new Uri(string.Format("http://{0}:8093/query", _server))).
Statement("SELECT * FROM default").
ClientContextId("somecontextlessthanorequalto64chars");
var json = query.GetFormValuesAsJson();
var expected = "{\"statement\":\"SELECT * FROM default\",\"client_context_id\":\"somecontextlessthanorequalto64chars\"}";
Console.WriteLine(json);
Assert.AreEqual(expected, json);
}
public void Test_Positional_Parameters()
{
var query = new QueryRequest().
BaseUri(new Uri(string.Format("http://{0}:8093/query", _server))).
Statement("SELECT * FROM default WHERE type=$1").
AddPositionalParameter("dog");
var json = query.GetFormValuesAsJson();
var expected = "{\"statement\":\"SELECT * FROM default WHERE type=$1\",\"timeout\":\"75000ms\",\"args\":[\"dog\"]}";
Console.WriteLine(json);
Assert.AreEqual(expected, json);
}
public void Test_Query_Incorrect_Syntax()
{
var config = new ClientConfiguration();
var client = new QueryClient(new HttpClient(), new JsonDataMapper(config), config);
var uri = new Uri(string.Format("http://{0}:8093/query", _server));
var query = new QueryRequest("SELECT 'Hello World' ASB Greeting").BaseUri(uri);
var result = client.Query<dynamic>(query);
Assert.IsNotNull(result);
Assert.IsFalse(result.Success);
Assert.IsEmpty(result.Rows);
}
public async void When_Bucket_Requires_Authentication_And_Credentials_Provided_QueryAsync_Succeeds()
{
using (var bucket = _cluster.OpenBucket("authenticated", "secret"))
{
//NOTE: authenticated is password protected
const string query = "SELECT * FROM `authenticated` LIMIT 10";
var queryRequest = new QueryRequest(query);
var result = await bucket.QueryAsync<dynamic>(queryRequest);
Assert.IsTrue(result.Success);
}
}
public void When_Bucket_Requires_Authentication_And_Credentials_NotProvided_Query_Fails()
{
using (var bucket = _cluster.OpenBucket("default"))
{
//NOTE: authenticated is password protected
const string query = "SELECT * FROM `authenticated` LIMIT 10";
var queryRequest = new QueryRequest(query);
var result = bucket.Query<dynamic>(queryRequest);
Assert.IsFalse(result.Success);
}
}
public async void Test_QueryAsync_With_QueryRequest()
{
using (var bucket = _cluster.OpenBucket())
{
var queryRequest = new QueryRequest()
.Statement("SELECT * FROM `beer-sample` LIMIT 10");
var result = await bucket.QueryAsync<dynamic>(queryRequest);
Assert.IsTrue(result.Success);
Assert.AreEqual(10, result.Rows.Count);
}
}
public void When_EnableQueryTiming_Is_True_And_LogLevel_Is_Not_Configured_Nothing_Is_Logged()
{
var queryRequest = new QueryRequest(Statement);
var log = new FakeLog("mylogger", LogLevel.Off, true, true, true, LogDateFormat);
using (var timer = new QueryTimer(queryRequest, new CommonLogStore(log), true))
{
Thread.Sleep(TimeSpan.FromSeconds(1));
timer.ClusterElapsedTime = ServerElapsedTime;
}
var logOutput = log.LogStore.ToString();
Assert.IsEmpty(logOutput);
}
/// <summary>
/// Lists the indexes for the current <see cref="IBucket" />.
/// </summary>
/// <returns></returns>
public virtual IndexResult ListN1qlIndexes()
{
var request = new QueryRequest(string.Format(Statements.ListIndexes, BucketName));
var result = _bucket.Query<IndexInfo>(request);
return new IndexResult
{
Value = result.Rows,
Exception = result.Exception,
Message = result.Message,
Success = result.Success
};
}
public void ServerExecutionTime_Defaults_To_NotRecorded()
{
var queryRequest = new QueryRequest(Statement);
var log = new FakeLog("mylogger", LogLevel.Debug, true, true, true, LogDateFormat);
using (new QueryTimer(queryRequest, new CommonLogStore(log), true))
{
Thread.Sleep(TimeSpan.FromSeconds(1));
}
var logOutput = log.LogStore.ToString();
Assert.IsTrue(logOutput.Contains("[INFO]"));
Assert.IsTrue(logOutput.Contains("Query Timing"));
Assert.IsTrue(logOutput.Contains(QueryTimer.NotRecorded));
Assert.IsTrue(logOutput.Contains(Statement));
}
private async Task<long> taskReadHighestSequenceNrAsync(string persistenceId, long fromSequenceNr)
{
string resultField = "MaxSequenceNr";
string N1QLQueryString = "SELECT MAX(`" + _CBBucket.Name + "`.SequenceNr) AS " + resultField + " FROM `" + _CBBucket.Name + "` WHERE DocumentType='JournalEntry' AND PersistenceId =$PersistenceId";
IQueryRequest N1QLQueryRequest = new QueryRequest()
.Statement(N1QLQueryString)
.AddNamedParameter("PersistenceId", persistenceId)
.AdHoc(false);
var queryTask = _CBBucket.QueryAsync<dynamic>(N1QLQueryRequest);
long highestSeqNum = 0;
var result = await queryTask;
long.TryParse(result.Rows[0][resultField].ToString(), out highestSeqNum);
return highestSeqNum;
}
public void When_EnableQueryTiming_Is_True_And_LogLevel_Configured_QueryTiming_Is_Logged()
{
var queryRequest = new QueryRequest(Statement);
var log = new FakeLog("mylogger", LogLevel.Debug, true, true, true, LogDateFormat);
using (var timer = new QueryTimer(queryRequest, new CommonLogStore(log), true))
{
Thread.Sleep(TimeSpan.FromMilliseconds(25));
timer.ClusterElapsedTime = ServerElapsedTime;
}
var logOutput = log.LogStore.ToString();
Assert.IsTrue(logOutput.Contains("[INFO]"));
Assert.IsTrue(logOutput.Contains("Query Timing"));
Assert.IsTrue(logOutput.Contains(ServerElapsedTime));
Assert.IsTrue(logOutput.Contains(Statement));
}
public static async Task<IEnumerable<User>> SimpleN1qlQuery(IBucket bucket, string lastname)
{
var query = string.Format("Select {0}.* from `{0}` where type=\"{1}\" and lastname=\"{2}\"", bucket.Name, "user", lastname);
var queryN1ql = new QueryRequest().Statement(query);
var result = await bucket.QueryAsync<dynamic>(queryN1ql);
var users = new List<User>();
foreach (var row in result.Rows)
{
var user = JsonConvert.DeserializeObject<User>(Convert.ToString(row));
users.Add(user);
}
return users;
}
public void Test_Query_Select_Where_Type_Is_Beer_As_Poco()
{
var config = new ClientConfiguration();
var client = new QueryClient(new HttpClient(), new JsonDataMapper(config), config);
var uri = new Uri(string.Format("http://{0}:8093/query", _server));
var query = new QueryRequest(
"SELECT abv, brewery_id, category, description, ibu, name, srm, style, type, upc, updated " +
"FROM `beer-sample` as beer " +
"WHERE beer.type='beer' LIMIT 10").BaseUri(uri);
var result = client.Query<Beer>(query);
Assert.IsNotNull(result);
Assert.IsTrue(result.Success);
Assert.IsNotNull(result.Rows);
var beer = result.Rows.First();
Assert.IsNotNullOrEmpty(beer.Name);
}
public ActionResult FindAll(string from, string to)
{
/*
QueryResult result = bucket.query(Query.simple(
select("a.name", "s.flight", "s.utc", "r.equipment")
.from(i(bucket.name()).as("r"))
.unnest("r.schedule s")
.join(i(bucket.name()).as("a").toString()).onKeys("r.airlineid")
.where(x("r.sourceairport").eq(s(from)).and(x("r.destinationairport").eq(s(to))))
.orderBy(asc("s.utc"))
));
List<FlightPath> flightPaths = new ArrayList<>();
for (QueryRow rows : result) {
flightPaths.add(FlightPath.create(rows.value(), from, to));
}
return flightPaths;
*/
var query = "SELECT a.name, s.flight, s.utc, r.equipment from `" +
bucket.Name + "` AS r UNNEST r.schedule s JOIN `" +
bucket.Name + "` AS a ON KEYS r.airlineid WHERE r.sourceairport = '" +
from.ToUpper() + "' AND r.destinationairport = '" +
to.ToUpper() + "' ORDER BY s.utc ASC";
var request = new QueryRequest().Statement(query);
var queryResult = bucket.Query<dynamic>(request);
if (queryResult.Rows.Any())
{
var sb = new StringBuilder();
foreach (var row in queryResult.Rows)
{
sb.Append(row.ToString());
sb.Append("\n\r");
}
return Content(sb.ToString());
}
return Content(queryResult.Exception != null ? queryResult.Exception.Message : "No rows");
}
public ActionResult AirportNamesStartingWith(string prefix)
{
/*
QueryResult result = bucket.query(Query.simple(
select("airportname")
.from(i(bucket.name()))
.where(
x("type").eq(s("airport")).and(x("airportname").like(s(prefix.toUpperCase() + "%")))
)
.orderBy(asc("airportname"))
));
List<String> airports = new ArrayList<>();
for (QueryRow row : result)
{
airports.add(row.value().getString("airportname"));
}
return airports;
*/
string query = "SELECT airportname FROM `" + bucket.Name
+ "` WHERE type = 'airport' AND airportname LIKE '" + prefix + "%' ORDER BY airportname ASC";
var request = new QueryRequest().Statement(query);
var queryResult = bucket.Query<dynamic>(request);
if (queryResult.Rows.Any())
{
var sb = new StringBuilder();
foreach (var row in queryResult.Rows)
{
sb.Append(row.ToString());
sb.Append("\n\r");
}
return Content(sb.ToString());
}
return Content("No rows");
}
public IQueryResult <T> Query <T>(Uri server, string query)
{
var queryRequest = new QueryRequest(query).BaseUri(server);
return(Query <T>(queryRequest));
}
/// <summary>
/// Executes an ad-hoc N1QL query against a Couchbase Server.
/// </summary>
/// <typeparam name="T">The Type to cast the resulting rows to.</typeparam>
/// <param name="server">The <see cref="Uri"/> of the server.</param>
/// <param name="query">A string containing a N1QL query.</param>
/// <returns>An <see cref="IQueryResult{T}"/> implementation representing the results of the query.</returns>
public Task<IQueryResult<T>> QueryAsync<T>(Uri server, string query)
{
var queryRequest = new QueryRequest(query).BaseUri(server);
return QueryAsync<T>(queryRequest);
}
