private static void OrderBy()
{
Console.WriteLine("OrderBy");
var db = _Database;
// tag::query-orderby[]
using (var query = QueryBuilder.Select(
SelectResult.Expression(Meta.ID),
SelectResult.Property("title"))
.From(DataSource.Database(db))
.Where(Expression.Property("type").EqualTo(Expression.String("hotel")))
.OrderBy(Ordering.Property("title").Ascending())
.Limit(Expression.Int(10))) {
foreach (var result in query.Execute())
{
Console.WriteLine($"Title :: {result.GetString("title")}");
}
}
// end::query-orderby[]
}
private static void SelectRegex()
{
Console.WriteLine("Regex");
var db = _Database;
// tag::query-regex-operator[]
using (var query = QueryBuilder.Select(
SelectResult.Expression(Meta.ID),
SelectResult.Property("name"))
.From(DataSource.Database(db))
.Where(Expression.Property("type").EqualTo(Expression.String("landmark"))
.And(Expression.Property("name").Regex(Expression.String("\\bEng.*e\\b"))))
.Limit(Expression.Int(10))) {
foreach (var result in query.Execute())
{
Console.WriteLine($"Name Property :: {result.GetString("name")}");
}
}
// end::query-regex-operator[]
}
private static void SelectWildcardCharacterLike()
{
Console.WriteLine("Wildchard Characters");
var db = _Database;
// tag::query-like-operator-wildcard-character-match[]
using (var query = QueryBuilder.Select(
SelectResult.Expression(Meta.ID),
SelectResult.Property("name"))
.From(DataSource.Database(db))
.Where(Expression.Property("type").EqualTo(Expression.String("landmark"))
.And(Expression.Property("name").Like(Expression.String("Royal Eng____rs Museum"))))
.Limit(Expression.Int(10))) {
foreach (var result in query.Execute())
{
Console.WriteLine($"Name Property :: {result.GetString("name")}");
}
}
// end::query-like-operator-wildcard-character-match[]
}
private static void GroupBy()
{
Console.WriteLine("GroupBy");
var db = _Database;
// # tag::query-groupby[]
using (var query = QueryBuilder.Select(
SelectResult.Expression(Function.Count(Expression.All())),
SelectResult.Property("country"),
SelectResult.Property("tz"))
.From(DataSource.Database(db))
.Where(Expression.Property("type").EqualTo(Expression.String("airport"))
.And(Expression.Property("geo.alt").GreaterThanOrEqualTo(Expression.Int(300))))
.GroupBy(Expression.Property("country"), Expression.Property("tz"))) {
foreach (var result in query.Execute())
{
Console.WriteLine(
$"There are {result.GetInt("$1")} airports in the {result.GetString("tz")} timezone located in {result.GetString("country")} and above 300 ft");
}
}
// # end::query-groupby[]
}
private static void UseCollectionOperators()
{
Console.WriteLine("Collection Operators");
var db = _Database;
// # tag::query-collection-operator[]
using (var query = QueryBuilder.Select(
SelectResult.Expression(Meta.ID),
SelectResult.Property("name"),
SelectResult.Property("public_likes"))
.From(DataSource.Database(db))
.Where(Expression.Property("type").EqualTo(Expression.String("hotel"))
.And(ArrayFunction.Contains(Expression.Property("public_likes"),
Expression.String("Armani Langworth"))))) {
foreach (var result in query.Execute())
{
var publicLikes = result.GetArray("public_likes");
var jsonString = JsonConvert.SerializeObject(publicLikes);
Console.WriteLine($"Public Likes :: {jsonString}");
}
}
// # end::query-collection-operator[]
}
private void TestDistanceFunction(IExpression distance, string testData)
{
var tests = JsonConvert.DeserializeObject <IList <IList <object> > >(testData);
foreach (var t in tests)
{
using (var doc = new MutableDocument()) {
doc.SetValue("v1", t[0]);
doc.SetValue("v2", t[1]);
doc.SetValue("distance", t[2]);
SaveDocument(doc);
}
}
using (var q = QueryBuilder.Select(SelectResult.Expression(distance), SelectResult.Property("distance"))
.From(DataSource.Database(Db))) {
var numRows = VerifyQuery(q, (n, r) =>
{
r.GetValue(0).Should().Be(r.GetValue(1));
});
numRows.Should().Be(tests.Count);
}
}
public void TestQueryWithBlobProperty()
{
var texts = new[]
{
"Knox on fox in socks in box. Socks on Knox and Knox in box.",
"Clocks on fix tick. Clocks on Knox tock. Six sick bricks tick. Six sick chicks tock."
};
foreach (var text in texts)
{
using (var doc = new MutableDocument()) {
doc.SetBlob("text", new Blob("text/plain", Encoding.UTF8.GetBytes(text)));
SaveDocument(doc);
}
}
var textModel = new TextModel();
textModel.RegisterModel();
var input = TextModel.CreateInput("text");
var prediction = Function.Prediction(textModel.Name, input).Property("wc");
using (var q = QueryBuilder
.Select(SelectResult.Property("text"), SelectResult.Expression(prediction).As("wc"))
.From(DataSource.Database(Db))
.Where(prediction.GreaterThan(Expression.Int(15)))) {
foreach (var row in q.Execute())
{
WriteLine(row.GetInt("wc").ToString());
}
}
textModel.ContentType.Should().Be("text/plain");
Database.Prediction.UnregisterModel(textModel.Name);
}
public void TestDeletePredictiveIndexesSharedCache()
{
CreateDocument(1, 2, 3, 4, 5);
CreateDocument(6, 7, 8, 9, 10);
var aggregateModel = new AggregateModel();
aggregateModel.RegisterModel();
var model = nameof(AggregateModel);
var input = AggregateModel.CreateInput("numbers");
var prediction = Function.Prediction(model, input);
var aggIndex = IndexBuilder.PredictiveIndex(model, input, null);
Db.CreateIndex("AggIndex", aggIndex);
var sumIndex = IndexBuilder.PredictiveIndex(model, input, "sum");
Db.CreateIndex("SumIndex", sumIndex);
var avgIndex = IndexBuilder.PredictiveIndex(model, input, "avg");
Db.CreateIndex("AvgIndex", avgIndex);
using (var q = QueryBuilder.Select(SelectResult.Property("numbers"))
.From(DataSource.Database(Db))
.Where(prediction.Property("sum").LessThanOrEqualTo(Expression.Int(15)).Or(
prediction.Property("avg").EqualTo(Expression.Int(8))))) {
var explain = q.Explain();
explain.Contains("USING INDEX SumIndex").Should().BeTrue();
explain.Contains("USING INDEX AvgIndex").Should().BeTrue();
var rows = VerifyQuery(q, (n, result) => { result.GetArray(0)?.Count.Should().BeGreaterThan(0); });
aggregateModel.Error.Should().BeNull();
rows.Should().Be(2);
aggregateModel.NumberOfCalls.Should().Be(2);
}
Db.DeleteIndex("SumIndex");
// Note: With only one index, the SQLite optimizer does not utilize the index
// when using an OR expression. So test each query individually.
aggregateModel.Reset();
aggregateModel.AllowCalls = false;
using (var q = QueryBuilder.Select(SelectResult.Property("numbers"))
.From(DataSource.Database(Db))
.Where(prediction.Property("sum").EqualTo(Expression.Int(15)))) {
var explain = q.Explain();
explain.Contains("USING INDEX SumIndex").Should().BeFalse();
var rows = VerifyQuery(q, (n, result) => { result.GetArray(0)?.Count.Should().BeGreaterThan(0); });
aggregateModel.Error.Should().BeNull();
rows.Should().Be(1);
aggregateModel.NumberOfCalls.Should().Be(0);
}
aggregateModel.Reset();
aggregateModel.AllowCalls = false;
using (var q = QueryBuilder.Select(SelectResult.Property("numbers"))
.From(DataSource.Database(Db))
.Where(prediction.Property("avg").EqualTo(Expression.Int(8)))) {
var explain = q.Explain();
explain.Contains("USING INDEX AvgIndex").Should().BeTrue();
var rows = VerifyQuery(q, (n, result) => { result.GetArray(0)?.Count.Should().BeGreaterThan(0); });
aggregateModel.Error.Should().BeNull();
rows.Should().Be(1);
aggregateModel.NumberOfCalls.Should().Be(0);
}
Db.DeleteIndex("AvgIndex");
for (int i = 0; i < 2; i++)
{
aggregateModel.Reset();
aggregateModel.AllowCalls = i == 1;
using (var q = QueryBuilder.Select(SelectResult.Property("numbers"))
.From(DataSource.Database(Db))
.Where(prediction.Property("avg").EqualTo(Expression.Int(8)))) {
var explain = q.Explain();
explain.Contains("USING INDEX SumIndex").Should().BeFalse();
explain.Contains("USING INDEX AvgIndex").Should().BeFalse();
var rows = VerifyQuery(q, (n, result) => { result.GetArray(0)?.Count.Should().BeGreaterThan(0); });
aggregateModel.Error.Should().BeNull();
rows.Should().Be(1);
if (i == 0)
{
aggregateModel.NumberOfCalls.Should().Be(0);
}
else
{
aggregateModel.NumberOfCalls.Should().BeGreaterThan(0);
}
}
Db.DeleteIndex("AggIndex");
}
}
static void Main(string[] args)
{
// Get the database (and create it if it doesn't exist)
var database = new Database("mydb");
// Create a new document (i.e. a record) in the database
string id = null;
using (var mutableDoc = new MutableDocument())
{
mutableDoc.SetFloat("version", 2.0f)
.SetString("type", "SDK");
// Save it to the database
database.Save(mutableDoc);
id = mutableDoc.Id;
}
// Update a document
using (var doc = database.GetDocument(id))
using (var mutableDoc = doc.ToMutable())
{
mutableDoc.SetString("language", "python");
database.Save(mutableDoc);
using (var docAgain = database.GetDocument(id))
{
Console.WriteLine($"Document ID :: {docAgain.Id}");
Console.WriteLine($"Learning {docAgain.GetString("language")}");
}
}
// Create a query to fetch documents of type SDK
// i.e. SELECT * FROM database WHERE type = "SDK"
using (var query = QueryBuilder.Select(SelectResult.All())
.From(DataSource.Database(database))
.Where(Expression.Property("type").EqualTo(Expression.String("SDK"))))
{
// Run the query
var result = query.Execute();
Console.WriteLine($"Number of rows :: {result.Count()}");
}
using (var query = QueryBuilder.Select(
SelectResult.Expression(Meta.ID),
SelectResult.Property("language"))
.From(DataSource.Database(database)))
{
foreach (var result in query.Execute())
{
Console.WriteLine($"Document Name :: {result.GetString("language")}");
}
}
// Create replicator to push and pull changes to and from the cloud
var targetEndpoint = new URLEndpoint(new Uri("ws://localhost:4984/getting-started-db"));
var replConfig = new ReplicatorConfiguration(database, targetEndpoint);
// Add authentication
replConfig.Authenticator = new BasicAuthenticator("john", "pass");
// Create replicator (make sure to add an instance or static variable
// named _Replicator)
var _Replicator = new Replicator(replConfig);
_Replicator.AddChangeListener((sender, args) =>
{
if (args.Status.Error != null)
{
Console.WriteLine($"Error :: {args.Status.Error}");
}
});
//Path.Combine(AppContext.BaseDirectory, "CouchbaseLite");
_Replicator.Start();
}