Tutorial

Tutorial

Presentation

The purpose of this tutorial is to understand how to save some custom objects to the documents store, create some indexes and execute some queries. This assumes you have some good knowledge of C# and are familiar with document databases concepts.

The full source of the tutorial can be found in the source code in the samples directory here: https://github.com/sebastienros/yessql/tree/master/samples/YesSql.Samples.Hi

Model

The model which will be used during this tutorial is made of a single class `BlogPost`. Here is the complete class:

    public class BlogPost
    {
        public string Title { get; set; }
        public string Author { get; set; }
        public string Content { get; set; }
        public DateTime PublishedUtc { get; set; }
        public string[] Tags { get; set; }
    }

This class contains different content types in order to demonstrate that we don't have to care about them during the modeling process. There is no constraint on them. As a document, it's a root aggregate, which means any related object will be persisted as only one document. In YesSql a document will be materialized as a single database record in the Document table.

Configuring the store

A store is just a simple relational database instance. It can be any type of SQL database, as long as (Dapper)[https://github.com/StackExchange/dapper-dot-net] supports it. The tutorial will use Microsoft Sql Ce 4.0 but you can switch it to anyone you want.

A store is represented by the Store class. An instance of Store should be unique per application, a singleton. This is a common practice in database management. If you have ever used an ORM like NHibernate or Entity Framework then it will be familiar. It depends on each application to define what strategy is used to access this singleton (DI, static accessor, ...). This tutorial is based on single method call, which means it won't deal with this particular aspect.

    var configuration = new Configuration
    {
        ConnectionFactory = new DbConnectionFactory<SqliteConnection>(@"Data Source=yessql.db;Cache=Shared", true),
        DocumentStorageFactory = new new SqlDocumentStorageFactory()
    };

    // Create a reusable store holding the configuration
    var store = new Store(configuration);
    
    // Creates the necessary tables
    store.InitializeAsync().Wait();

In this example the store is initialized using a connection string to a local Sqlite database named yessql.db.

At this stage you will obviously need to add a reference to Microsoft.Data.Sqlite if you want to run the application.

Persisting a document

The store will behave as a factory for database transactions. In YesSql they are called __sessions__.

    // creating a blog post
    var post = new BlogPost
    {
        Title = "Hello YesSql",
        Author = "Bill",
        Content = "Hello",
        PublishedUtc = DateTime.UtcNow,
        Tags = new[] {"Hello", "YesSql"}
    };

    // saving the post to the database
    using(var session = store.CreateSession())
    {
        session.Save(post);
    }

When the sessions is disposed, all the changes will be automatically flushed onto the database. Alternatively, if you want to handle when changes are flushed to the database you can use session.CommitAsync() explicitly, even though the transaction will actually be committed once the session is disposed. A call to session.Cancel() can be used to prevent the current changes from being committed.

Loading a document

Now that a document has been saved in the store, we might need to retrieve it. It can be done in various ways which will all be explored, but the simplest one is to load a document by its type only.

    // loading an single blog post
    using(var session = store.CreateSession())
    {
        var p = session.Query<BlogPost>().FirstOrDefault();
        Console.WriteLine(p.Title); // > Hello YesSql
    }

Updating a document

Updating a document and saving it back to the store should be done as part of the same session.

    // loading an existing blog post
    using(var session = store.CreateSession())
    {
        var p = session.Query<BlogPost>().FirstOrDefault();

        // this change will be saved back into the same store document
        p.Title = "A new title";
    }

Using the Query method doesn't give access to the inner properties of the documents themselves, which is a major limitation for most real scenarios. To do such queries, you will need to create some dedicated indexes.

Querying documents

In YesSql queries are executed on materialized views called indexes. There are two types of indexes:

• Mapped indexes, to do elementary queries on document properties
• Reduced indexes, for grouping and doing queries on aggregated property values

Creating a mapped index

A mapped index is represented as a class inheriting from `MapIndex`. Here is an example which will store the `Author` property of the `BlogPost` class.

    public class BlogPostByAuthor : MapIndex
    {
        public string Author { get; set; }
    }

Declaring the map logic

Indexes have to be described in `IIndexProvider` implementations. Here is one registering `BlogPostByAuthor` by inheriting from `IndexProvider`.

    public class BlogPostIndexProvider : IndexProvider<BlogPost>
    {
        public override void Describe(DescribeContext<BlogPost> context)
        {
            // for each BlogPost, create a BlogPostByAuthor index
            context.For<BlogPostByAuthor>().Map(blogPost => new BlogPostByAuthor { Author = blogPost.Author });
        }
    }

This class will tell the system how to construct the indexes from an existing BlogPost object. Right now there is only one index defined, but several could be described in the same provider.

Then the provider is registered in the system like this:

store.RegisterIndexes<BlogPostIndexProvider>();

Using the map index to query documents

Queries are executed directly on the map index. You can use either `QueryIndex()` to query the index itself, or `QueryByMappedIndex()` on an `ISession` instance to retrieve the associated documents. ```csharp // loading blog posts by author using (var session = store.CreateSession()) { var ps = await session.QueryAsync(x => x.Author.StartsWith("B")).List();

    foreach (var p in ps)
    {
        Console.WriteLine(p.Author); // > Bill
    }
}

This example demonstrates how to use the index to query `BlogPost` by their `Author` property.

<h2 name="using-map-reduce-index">Using map/reduce indexes</h2>
Map/Reduce indexes are useful to construct aggregated information from multiple objects. With a map index, one index entry is related to only one object, whereas with a map/reduced, an index entry will represent some information for a set of objects.

<h2 name="creating-map-reduce-index">Creating a map/reduce index</h2>
Let's say we want to compute some indexes representing how many '`BlogPost`' are published for a specific day, and also keep a trace of those posts. Here is a `BlogPostByDay` index class inheriting from `ReduceIndex`.

```csharp
    public class BlogPostByDay : ReduceIndex
    {
        public virtual string Day { get; set; }
        public virtual int Count { get; set; }
    }

This index then needs to be described in an index provider. The only difference is that on top of a map function, it also need to define how to group and also reduce those results. Ultimately a delete function tells YesSql what to do with the index when a related object is deleted, the opposite of reducing the index.

    // for each BlogPost, aggregate in an exiting BlogPostByDay
    context.For<BlogPostByDay, string>()
        .Map( blogPost => new BlogPostByDay {
                    Day = blogPost.PublishedUtc.ToString("yyyyMMdd"),
                    Count = 1
            })
        .Group( blogPost => blogPost.Day )
        .Reduce( group => new BlogPostByDay {
                Day = group.Key,
                Count = group.Sum(p => p.Count)
            })
        .Delete( (index, map) => {
                index.Count -= map.Sum(x => x.Count);

                // if Count == 0 then delete the index
                return index.Count > 0 ? index : null;
            });

Using the map/reduce index to query documents

Here are some examples of how to use the newly created index:

    // loading blog posts by day of publication
    using (var session = store.CreateSession())
    {
        var ps = await session.QueryAsync<BlogPost, BlogPostByDay>(x => x.Day == DateTime.UtcNow.ToString("yyyyMMdd")).List();

        foreach (var p in ps)
        {
            Console.WriteLine(p.PublishedUtc); // > [Now]
        }
    }

    // counting blog posts by day
    using (var session = store.CreateSession())
    {
        var days = await session.QueryIndexAsync<BlogPostByDay>().ToList();

        foreach (var day in days)
        {
            Console.WriteLine(day.Day + ": " + day.Count); // > [Today]: 1
        }
    }