private static void RequestPlusExample()
{
Console.WriteLine("========= RequestPlus");
// get the current count
var result1 = _bucket.Query <dynamic>("SELECT COUNT(1) as airportCount FROM `travel-sample` WHERE type='airport'")
.Rows.First();
Console.WriteLine($"Initial count: {result1.airportCount}");
// insert a new airport
var doc = new Document <dynamic> {
Id = "ScanConsistency::airport::" + _random.Next(10000),
Content = new { type = "airport" }
};
_bucket.Insert(doc);
// get the count again
var request = QueryRequest.Create("SELECT COUNT(1) as airportCount FROM `travel-sample` WHERE type='airport'");
request.ScanConsistency(ScanConsistency.RequestPlus);
var result2 = _bucket.Query <dynamic>(request).Rows.First();
Console.WriteLine($"Count after insert with RequestPlus: {result2.airportCount}");
}
public async Task <List <UserEvent> > FindLatestUserEventsAsync(string userId, EventType eventType, int limit, int offset)
{
var userEvents = new List <UserEvent>();
var n1ql = $@"SELECT META(e).id, e.userId, e.createdDate, e.eventType
FROM `{_bucket.Name}` e
WHERE e.type = 'userEvent'
AND e.eventType = $eventType
AND e.userId = $userId
LIMIT $limit
OFFSET $offset";
var query = QueryRequest.Create(n1ql);
query.AddNamedParameter("eventType", eventType);
query.AddNamedParameter("userId", userId);
query.AddNamedParameter("limit", limit);
query.AddNamedParameter("offset", offset);
var result = await _bucket.QueryAsync <UserEvent>(query);
if (result != null && result.Success)
{
userEvents = result.Rows;
}
return(userEvents);
}
public int GetCountOfAllSessions()
{
var n1ql = @"SELECT VALUE COUNT(*) FROM `" + _bucket.Name + "`";
var query = QueryRequest.Create(n1ql);
return(_bucket.Query <int>(query).Rows.First());
}
public async Task <IActionResult> DoctorsPatients(string doctorid, bool serviced = false)
{
string statement;
if (serviced)
{
statement = $@"SELECT patients.information, patients.timestamp, patients.type, META(patients).id
FROM `{_bucket.Name}` AS patients
WHERE patients.type = 'patient'
AND ANY note IN patients.notes SATISFIES note.doctor = $id END";
}
else
{
statement = $@"SELECT patients.information, patients.timestamp, patients.type, META(patients).id
FROM `{_bucket.Name}` AS doctors
JOIN `{_bucket.Name}` AS patients ON KEYS doctors.patients
WHERE doctors.type = 'doctor'
AND META(doctors).id = $id";
}
var query = QueryRequest.Create(statement);
query.AddNamedParameter("id", doctorid);
var result = await _bucket.QueryAsync <Patient>(query);
if (result.Success)
{
return(Ok(result.Rows));
}
return(CouchbaseError(result));
}
public void When_AdHoc_Is_True_Client_Doesnt_Cache()
{
var client = GetQueryClient();
var request = QueryRequest.Create("SELECT * from `beer-sample` LIMIT 100").
BaseUri(new Uri(string.Format("http://{0}:8093/query", _server))).
Pretty(false);
var stopWatch = new Stopwatch();
stopWatch.Start();
var result = client.Query <dynamic>(request);
stopWatch.Stop();
Console.WriteLine("Elasped time 1:{0}", stopWatch.ElapsedMilliseconds);
stopWatch = new Stopwatch();
stopWatch.Restart();
result = client.Query <dynamic>(request);
stopWatch.Stop();
Console.WriteLine("Elasped time 2:{0}", stopWatch.ElapsedMilliseconds);
stopWatch = new Stopwatch();
stopWatch.Restart();
result = client.Query <dynamic>(request);
stopWatch.Stop();
Console.WriteLine("Elasped time 3:{0}", stopWatch.ElapsedMilliseconds);
Assert.AreEqual(QueryStatus.Success, result.Status);
}
public async Task <List <User> > ListTenantUsers(int tenantId, int offset, int limit)
{
var users = new List <User>();
var n1ql = $@"Select meta().id as id, username, tenantId, firstName, lastname
from `{_bucket.Name}`
where type = 'user'
and tenantId = $tenantId
order by firstName asc
limit $limit
offset $offset";
var query = QueryRequest.Create(n1ql);
query.AddNamedParameter("tenantId", tenantId);
query.AddNamedParameter("limit", limit);
query.AddNamedParameter("offset", offset);
var results = await _bucket.QueryAsync <User>(query);
if (results != null && results.Success && results.Rows.Count > 0)
{
users = results.Rows;
}
return(users);
}
public void When_Prepared_Is_True_Client_Caches_And_Uses_Prepared_Statement2()
{
var config = new ClientConfiguration();
var client = new QueryClient(new HttpClient(), new JsonDataMapper(config), config);
var request = QueryRequest.Create("SELECT * from `beer-sample` LIMIT 100", false).
BaseUri(new Uri(string.Format("http://{0}:8093/query", server))).
Pretty(false);
var stopWatch = new Stopwatch();
stopWatch.Start();
var result = client.Query <dynamic>(request);
stopWatch.Stop();
Console.WriteLine("Elasped time 1:{0}", stopWatch.ElapsedMilliseconds);
stopWatch = new Stopwatch();
stopWatch.Restart();
result = client.Query <dynamic>(request);
stopWatch.Stop();
Console.WriteLine("Elasped time 2:{0}", stopWatch.ElapsedMilliseconds);
stopWatch = new Stopwatch();
stopWatch.Restart();
result = client.Query <dynamic>(request);
stopWatch.Stop();
Console.WriteLine("Elasped time 3:{0}", stopWatch.ElapsedMilliseconds);
Assert.AreEqual(QueryStatus.Success, result.Status);
}
public async Task Should_Reprepare_Query_If_Not_Adhoc_And_Receive_IndexNotFound()
{
var request = QueryRequest.Create("SELECT META().id FROM `default` WHERE name = $1 OFFSET 0 LIMIT 20;")
.AddPositionalParameter(new object[] { "Bob" })
.AdHoc(false);
const string indexName = "test-index";
var manager = _bucket.CreateManager(TestConfiguration.Settings.AdminUsername, TestConfiguration.Settings.AdminPassword);
await manager.CreateN1qlIndexAsync(indexName, false, new [] { "name" });
await manager.WatchN1qlIndexesAsync(new List <string> {
indexName
}, TimeSpan.FromSeconds(10));
var result1 = await _bucket.QueryAsync <dynamic>(request);
Assert.IsTrue(result1.Success);
await manager.DropN1qlIndexAsync(indexName);
var result2 = await _bucket.QueryAsync <dynamic>(request);
Assert.IsTrue(result2.Success);
}
public List <dynamic> GetTaskById(string taskId)
{
var queryStr = @"SELECT
p._id AS projectId,
(SELECT _id,
(SELECT a.* FROM `" + _bucket.Name + @"` a USE KEYS c.assignedTo)[0] AS assignedTo,
createdON,
description,
(select
t.log,
t.createdAt,
(SELECT u.* FROM `" + _bucket.Name + @"` u USE KEYS t.`user`)[0] AS `user` FROM `" + _bucket.Name + @"` r UNNEST r.history t WHERE r._id = $1) AS history,
name,
(SELECT o.*FROM `" + _bucket.Name + @"` o USE KEYS c.owner)[0] as owner,
(SELECT u.* FROM `" + _bucket.Name + @"` u USE KEYS c.users) AS users, permalink
FROM `" + _bucket.Name + @"` c
WHERE c._id= $1)[0] AS task
FROM `" + _bucket.Name + @"` p
WHERE ANY x IN tasks SATISFIES x = $1 END ";
var query = QueryRequest.Create(queryStr);
query.ScanConsistency(ScanConsistency.RequestPlus);
query.AddPositionalParameter(taskId);
var queryResult = _bucket.Query <dynamic>(query);
return(ExtractResultOrThrow(queryResult));
}
public IActionResult Register(User user)
{
var n1ql = "SELECT t.* FROM todoBucket t WHERE t.email = $email";
var query = QueryRequest.Create(n1ql);
query.AddNamedParameter("$email", user.Email);
var result = _bucket.Query <User>(query).FirstOrDefault();
if (result != null)
{
return(BadRequest("This email already exists"));
}
var key = Guid.NewGuid().ToString();
user.UserId = key;
List <Todo> todoList = new List <Todo>();
todoList.Add(new Todo {
TodoId = Guid.NewGuid().ToString(), Content = "First todo", TodoDate = DateTime.Now.ToLocalTime(), status = true
});
List <Category> categoryList = new List <Category>();
categoryList.Add(new Category {
CategoryId = Guid.NewGuid().ToString(), Name = "General", Description = "General Category", Todos = todoList
});
user.Categories = categoryList;
_bucket.Insert(key, user);
return(Ok("User Successfully created"));
}
public List <Company> GetCompanies()
{
var queryStr = "SELECT c.* FROM `" + _bucket.Name + "` c WHERE _type = 'Company'";
var query = QueryRequest.Create(queryStr);
query.ScanConsistency(ScanConsistency.RequestPlus);
return(ExtractResultOrThrow(_bucket.Query <Company>(query)));
}
public List <Project> GetProjects()
{
var queryStr = "SELECT projects.* FROM `" + _bucket.Name + "` AS projects WHERE _type = 'Project'";
var query = QueryRequest.Create(queryStr);
query.ScanConsistency(ScanConsistency.RequestPlus);
return(ExtractResultOrThrow(_bucket.Query <Project>(query)));
}
// For testing purposes only!!!
public void ClearBucket()
{
var n1ql = $"delete from `{_bucket.Name}`";
var query = QueryRequest.Create(n1ql);
_bucket.Query <dynamic>(query);
}
// tag::GetAllGifts[]
public static List <Gift> GetAllGifts()
{
var bucket = ClusterHelper.GetBucket("default");
var query = QueryRequest.Create("SELECT g.* FROM `default` g");
query.ScanConsistency(ScanConsistency.RequestPlus);
return(bucket.Query <Gift>(query).Rows);
}
//---------------------------------------------------------------------
// 根据玩家Guid获取指定玩家信息
async Task <PlayerInfoOther> ICellPlayerService.getPlayerInfoOther(string et_player_guid)
{
string query = string.Format(@"SELECT {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12} FROM Fishing as PlayerInfoOther
WHERE entity_type='EtPlayer' and {13}=$1 LIMIT 1;"
, "entity_guid"
, "list_component[0].def_propset.ActorId"
, "list_component[0].def_propset.NickName"
, "list_component[0].def_propset.Icon"
, "list_component[0].def_propset.Level"
, "list_component[0].def_propset.Experience"
, "list_component[0].def_propset.Chip"
, "list_component[0].def_propset.Gold"
, "list_component[0].def_propset.IndividualSignature"
, "list_component[0].def_propset.ProfileSkinTableId"
, "list_component[0].def_propset.IpAddress"
, "list_component[0].def_propset.IsVIP"
, "list_component[0].def_propset.VIPPoint"
, "entity_guid");
var query_request = QueryRequest.Create(query)
.ScanConsistency(ScanConsistency.RequestPlus)
.AddPositionalParameter(et_player_guid);
var result = await DbClientCouchbase.Instance.Bucket.QueryAsync <PlayerInfoOther>(query_request);
if (result.Success && result.Rows.Count > 0)
{
PlayerInfoOther q = result.Rows[0];
if (q != null && !string.IsNullOrEmpty(q.player_etguid))
{
if (string.IsNullOrEmpty(q.nick_name))
{
q.nick_name = EbTool.jsonDeserialize <string>(q.nick_name);
}
if (string.IsNullOrEmpty(q.icon))
{
q.icon = EbTool.jsonDeserialize <string>(q.icon);
}
if (string.IsNullOrEmpty(q.ip_address))
{
q.ip_address = EbTool.jsonDeserialize <string>(q.ip_address);
}
if (string.IsNullOrEmpty(q.individual_signature))
{
q.individual_signature = EbTool.jsonDeserialize <string>(q.individual_signature);
}
return(q);
}
}
PlayerInfoOther player_info = new PlayerInfoOther();
return(player_info);
}
// end::SaveShoppingCart[]
public ShoppingCart GetCartByUserName(string userName)
{
var n1ql = $"SELECT c.* FROM `{_bucket.Name}` c WHERE c.userName = $UserName LIMIT 1";
var query = QueryRequest.Create(n1ql);
query.ScanConsistency(ScanConsistency.RequestPlus);
query.AddNamedParameter("UserName", userName);
return(_bucket.Query <ShoppingCart>(query).Rows.FirstOrDefault());
}
//Upon user selected options, display appropriate data.
public IActionResult GetState(MerchandiseViewModel model)
{
model.countries = _countries;
model.cards = _cards;
model.networks = _networks;
model.SelectedCountry = (from c in model.countries
where c.Value == model.SelectedCountryId.ToString()
select new Country
{
CountryId = int.Parse(c.Value),
CountryName = c.Text
}).FirstOrDefault();
model.SelectedCard = (from d in model.cards
where d.Value == model.SelectedCardId.ToString()
select new Card
{
CardId = int.Parse(d.Value),
CardLetter = d.Text
}).FirstOrDefault();
model.SelectedNetwork = (from n in model.networks
where n.Value == model.SelectedNetworkId.ToString()
select new Network
{
NetworkId = int.Parse(n.Value),
NetworkName = n.Text
}).FirstOrDefault();
NetworkId = model.SelectedNetworkId;
CompanyId = model.SelectedCountryId;
CardId = model.SelectedCardId;
string countryName = FindCountryName(model.SelectedCountry.CountryId);
string cardLetter = GetCard(model.SelectedCard.CardId);
int countryCode = FindCountryCode(model.SelectedCountry.CountryId);
var bucket = _bucketProvider.GetBucket(countryName);
var n1ql = @"SELECT g.*, META(g).id
FROM `" + countryName + "` g " +
"WHERE g.companyId = " + countryCode + " " +
"AND g.showCd = '" + cardLetter + "' " +
"AND g.networkId = " + model.SelectedNetwork.NetworkId + " " +
"LIMIT 20;";
var query = QueryRequest.Create(n1ql);
var results = bucket.Query <Merchandise>(query);
try
{
model.merchandise = results.Rows;
}
catch (global::System.Exception)
{
throw;
}
return(View(model));
}
public ActionResult N1qlExample()
{
var query = QueryRequest.Create("SELECT b.foo, b.bar FROM `" + _bucket.Name + "` b");
query.ScanConsistency(ScanConsistency.RequestPlus);
var results = _bucket.Query <FooBar>(query).Rows;
return(View(results));
}
public List <dynamic> CreateTask(string projectId, Task task)
{
// create task
var taskId = Guid.NewGuid().ToString();
task.Users = new List <string> {
task.Owner
};
task._id = taskId;
task._type = "Task";
task.CreatedON = DateTime.Now.ToShortDateString();
var taskDocument = new Document <dynamic>
{
Id = taskId,
Content = task
};
_bucket.Upsert(taskDocument);
// add task to project
var projectDocument = _bucket.Get <Project>(projectId);
if (!projectDocument.Success)
{
throw new ArgumentException("The project id does not exist");
}
var project = projectDocument.Value;
project.Tasks.Add(taskId);
var updatedProjectDocument = new Document <Project>
{
Id = projectDocument.Id,
Content = project
};
_bucket.Upsert(updatedProjectDocument);
// return task with full owner/users information
var queryStr = @"SELECT c._id,
c.createdON,
c.name,
c.description,
(SELECT o.* FROM `" + _bucket.Name + @"` o USE KEYS c.owner)[0] AS owner,
c.status,
(SELECT u.* FROM `" + _bucket.Name + @"` u USE KEYS c.users) AS users,
c.permalink
FROM `" + _bucket.Name + @"` c
WHERE c._id = $1";
var query = QueryRequest.Create(queryStr);
query.ScanConsistency(ScanConsistency.RequestPlus);
query.AddPositionalParameter(taskId);
var queryResult = _bucket.Query <dynamic>(query);
return(ExtractResultOrThrow(queryResult));
}
public int GetNumDocuments()
{
var n1ql = $"SELECT COUNT(1) AS numDocuments FROM `{_bucket.Name}` WHERE type = 'Magic';";
var query = QueryRequest.Create(n1ql);
query.Timeout(new TimeSpan(0, 0, 5));
var queryResult = _bucket.Query <dynamic>(query);
return(queryResult.Rows[0].numDocuments);
}
public List <User> GetUsers()
{
var queryStr = "SELECT u.* FROM `" + _bucket.Name + @"` u WHERE _type = 'User'";
var query = QueryRequest.Create(queryStr);
query.ScanConsistency(ScanConsistency.RequestPlus);
var queryResult = _bucket.Query <User>(query);
return(ExtractResultOrThrow(queryResult));
}
public List <User> GetUserByEmail(string email)
{
var n1ql = "SELECT d.* FROM default d WHERE d.email = $email";
var query = QueryRequest.Create(n1ql);
query.AddNamedParameter("$email", email);
var result = _bucket.Query <User>(query);
return(result.Rows);
}
public IActionResult GetJPN()
{
var bucket = _bucketProvider.GetBucket("MerchJPN");
var n1ql = @"SELECT g.*, META(g).id
FROM `MerchJPN` g
WHERE g.companyId = 2;";
var query = QueryRequest.Create(n1ql);
var results = bucket.Query <Merchandise>(query);
return(Ok(results.Rows));
}
public List <Project> GetOtherProjectsByUserId(string userId)
{
var queryStr = "SELECT p.* FROM `" + _bucket.Name + "` p WHERE _type = 'Project' AND ANY x IN users SATISFIES x = $1 END";
var query = QueryRequest.Create(queryStr);
query.ScanConsistency(ScanConsistency.RequestPlus);
query.AddPositionalParameter(userId);
var queryResult = _bucket.Query <Project>(query);
return(ExtractResultOrThrow(queryResult));
}
public List <Project> GetProjectsByOwnerId(string ownerId)
{
var queryStr = "SELECT p.* FROM `" + _bucket.Name + "` p WHERE _type = 'Project' and owner = $1";
var query = QueryRequest.Create(queryStr);
query.ScanConsistency(ScanConsistency.RequestPlus);
query.AddPositionalParameter(ownerId);
var queryResult = _bucket.Query <Project>(query);
return(ExtractResultOrThrow(queryResult));
}
public IEnumerable <Type> ExecQueryReturnList(string queryRequest)
{
var query = QueryRequest.Create(queryRequest);
var result = _bucket.Query <Type>(query);
if (result.Errors.Count > 0)
{
throw new Exception(result.Errors[0].Message);
}
return(result.Rows);
}
public IHttpActionResult GetAll()
{
var n1ql = @"select g.* , meta(g).id from UBook g where g.type='ResturantType';";
var query = QueryRequest.Create(n1ql);
query.ScanConsistency(ScanConsistency.RequestPlus);
var result = _bucket.Query <ResturantType>(query);
return(Ok(result.Rows));
}
public ActionResult GetByBrowser(string browser)
{
var n1ql = $"SELECT *, META().id FROM {ConfigurationDefaults.COUCHBASE_COLLECTIONNAME} WHERE Browser = $browser";
var query = QueryRequest.Create(n1ql);
query.AddNamedParameter("$browser", browser);
query.ScanConsistency(ScanConsistency.RequestPlus);
var result = _bucket.Query <object>(query);
return(new JsonResult(result));
}
public IActionResult GetAll()
{
var getAllN1QL = @"select report.*, META(report).id from emonthly report where report.type = 'report'";
var query = QueryRequest.Create(getAllN1QL);
query.ScanConsistency(ScanConsistency.RequestPlus);
var result = _bucket.Query <Report>(query);
return(Ok(result.Rows));
}
public async Task <IActionResult> Patients()
{
var statement = $"SELECT META().id, `{_bucket.Name}`.* FROM `{_bucket.Name}` WHERE type='patient'";
var query = QueryRequest.Create(statement);
var result = await _bucket.QueryAsync <Patient>(query);
if (result.Success)
{
return(Ok(result.Rows));
}
return(CouchbaseError(result));
}