There're RegisterSqlServer, RegisterSqLite .. etc in all the DbProviderOptionsExtensions, I don't see any use to make them public, because they are originally used internally in the Use[DbProvider]

I saw that you're used them here, but I simplify the code using the Use[DbProvider] API instead in this PR, so my humble suggestion to make this methods private

Because of dependency at System.Data.SqlClient lower than 4.4.0 Orchard ported to Core 2.0 when deployed to Azure WebApp fails to configure database.
See Azure/app-service-announcements-discussions#9

OrchardCore and yessql are great projects thnak you
After tampering with orchard core multi tenancy and openId
I stumbled across this error when i tried to access opendId application list :
SqlException: Incorrect syntax near 'OFFSET'.
Invalid usage of the option NEXT in the FETCH statement.

after taking a peak at sql profiler trace sql generated by yessql
I think it is because yessql is not providing order by when it is required

it looks like this in the trace :
exec sp_executesql N'SELECT [Document].* FROM [Document] WHERE [Document].[Type] = @type OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY',N'@type nvarchar(4000)',@type=N'OrchardCore.OpenId.YesSql.Models.OpenIdApplication, OrchardCore.OpenId'

this caused the error
Modifying it like this works :
exec sp_executesql N'SELECT [Document].* FROM [Document] WHERE [Document].[Type] = @type ORDER BY(SELECT NULL) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY',N'@type nvarchar(4000)',@type=N'OrchardCore.OpenId.YesSql.Models.OpenIdApplication, OrchardCore.OpenId'

With data migration support so that migrations are executed automatically when indexes are registered.

And also an uninstall method to delete the table.

After that latest updates, seems the InMemoryDbProvider has been deleted, it was a good choice specially for testing purpose is there a reason for the deletion?

I have a "Order" class which in turn has a collection property called "Items" of type "OrderItem".
Each OrderItem has a reference to its "Order" instance. When I save an instance of an Order with one or more OrderItems, I get a "A circular reference was detected while serializing an object of type 'OrderItem'."

Are there any documented guidelines on how to handle this?

The index commands are currently delayed to the Commit phase, which could allow for more optimization by executing compound commands and issue many inserts/updates in a single query.

This should also be applied to storage by create specific commands and executing them in the end. Or just use IdentityDocument objects in a list for that matter.

Be able to shard content items in different Document tables, a.k.a collections.

Create a DocumentTableStrategy class that can provide the table name for a class. It could a single table, one per class, one per hierarchy, or a specific classes in the same tables.
When loading documents, the type needs to be defined to be able to select which table to query.

Using OrchardCore ,when querying for a content item using the IsIn extension method it does not work when searching by documentId field (Int) . It only works when querying with ContentItemId (string)

Here is an example

string contentItemId = "4p3cygc0ebtvwx3p13nk26bjw7";

//this works
var selectedContentItems0 = _session .Query<ContentItem, ContentItemIndex>().Where(s =>s.ContentItemId.IsIn(new List(){ contentItemId })).ListAsync().Result;

//this doesnt work when using the DocumentId (122) - returns a null value exception
var selectedContentItems = _session
.Query<ContentItem, ContentItemIndex>().Where(s => s.DocumentId.IsIn(new List(){122})).ListAsync().Result;

Issue has been reported on orchardCore as well.
OrchardCMS/OrchardCore#1318

Pure relational dbs have its pros and cons, pure document dbs also have its pros and cons. To have a nosql db based on an sql db is an smart movement to have best of both worlds easily available to solve the different problems we find developing an app.

Following this philosophy it would be interesting that Yessql also support relations between documents.
Usually we see those relations in ORMs for lazy loading. Personally I'm against the use of Lazy Loading because in the long term devs tend to overuse it hurting performance of the system as have seen many times in Orchard 1 when a content type has content fields pointing to other content items.

What it is interesting about relations is that YesSql can offer a kind of Include() method for queries, helping us to avoid easily N+1 queries in a transparent way. Because we will delegate on YesSql the work of optimizing the operation for getting the related documents of resulting documents of a query using only one extra query for retrieving documents on the other side of a relation.

In Orchard 2 this will be very useful to deal with fields pointing to other content items in an optimal way. I've added a similar solution based on an extension for IContentQuery applicable for content fields and other content items like content fields, taxonomy fields, MediaPickerFields that have boosted performance in Orchard 1 sites, problem it still is not enough clean and elegant as I would like to submit a PR ... :)

To declare a relation we will need to mark "navigation fields" with metadata stablished by YesSql that of course will be independent of Orchard Concepts. Those metadata will stablish keys involved on the relation and the type of the relation: Many-To-One, One-To-One and Many-To-Many. Orchard fields depending of its type will fulfill this metadata with its serialization according to its needs.
In the orchard side Fields should be developed in a way that they cannot be accessed if its navigation property have not been loaded through include extension. But YesSql should offer a way of explicitly load one field based on a relation. IMO the important thing is to don't offer lazy loading cause it is the root cause of performance issues done by devs, but give them an explicit load alternative to help them to be conscious of what they are doing.

To keep things simple for storing documents and not be worried on tracking changes on related documents, we could only store documents explicitly asked to store. But YesSql won't be responsible of storing related documents of documents explicitly stored.

Can you abstract high level commands (from Core library) into interfaces and use them by interface?
This way it is possible to use Azure Tables or any other store not only particular RDBMSs

Create a ReduceIndex without a Delete, register it and delete a document. This should try to find a Delete method and will fail.

You have tables without primary key. https://github.com/sebastienros/yessql/blob/master/src/YesSql.Core/Services/Store.cs
It's have method of "InitializeAsync()" which for migration create table without primary key so you can get exceptions. If your Mysql db is using InnoDB.
I propose to determine column of "nextval" as type of "long" and like "primary key".

Shouldn't it be 256? :) I kid, but what if I have a Page object with a BodyText property that has a length of 300 chars? Will yessql modify the columns length for me? Or will it create a separate table to store big data? Or will it just crash and burn. I should try it.

Currently this sample uses inmemory sqlconnection for ConnectionFactory and for DocumentStorageFactory.
When a call to _connection.BeginTransaction(_isolationLevel); has a value of ReadCommitted I get System.ArgumentException: 'The IsolationLevel 'ReadCommitted' is invalid.'

When I install yessql via Nuget, Nuget will download and install NHibernate. Although this seems to work initially, a runtime error occurs as soon as I want to register an index with the store: "An invalid or incomplete configuration was used while creating a SessionFactory. Check PotentialReasons collection, and InnerException for more detail."

The InnerException shows: {"Could not load type 'FluentNHibernate.Mapping.ToManyBase3' from assembly 'FluentNHibernate, Version=1.3.0.727, Culture=neutral, PublicKeyToken=8aa435e3cb308880'.":"FluentNHibernate.Mapping.ToManyBase3"}

When I use the NHibernate version (1.3.0.717) that comes with your samples, it works fine.

There are more options to manipulate with such type of column that a simple VARCHAR

There's a bug in paging in previous versions of SQL Server, OFFSET is supported on SQL Server 2012

FYI this issue is already logged here

Within a session mutating operations can be batched between document reads and batched. They would be flushed when the transaction is committed.
Make this batch size configurable.

Hi, I like YesSql after toying around with it. Particularly, the philosophy of it as database interface

Please consider the feature could let us target native POCO persistence, it further adds value to the developer & the performance of the underlying system. There are many applications, like Image Galleries, Maps, Networks, where this is much much faster.

I am attaching some links/samples behind the idea - and the speed realized.

1. Benchmarks
2. Code on Github with samples
3. Intro
4. Supporting the inherited interface, while RDB have TPH (Table Per Hierarchy) they are not easy to handle in the DAL with EF or SP's. For e.g. to leverage and qualify an object belongs to which tenant or which group or which type based on the objects strongly typed (and not another field/attribute col in the DB) would be very valuable.
5. Filters: check out both zzzfilters and dynamic filters on Github another valuable feature

Hi Sebastien,

As we need an Oracle provider for YesSql (to be used with OrchardCore). We can write the Oracle provider so you can add it to the YesSql project, if you agree.

A query on a base type should return all sub classes of this type.

Just what the title says

We should probably accept empty lists correctly. In this case because semantically it means a property has to be one of the values and there are no value, the predicate could be converted into true == false in this case to invalidated this part of expression.

Currently the schema is generated automatically. This is nice for prototyping but unsuitable to production applications, which need to be be deterministic, and need to evolve.

Solution 1:

• Keep the automatic schema as a specific step in the configuration, for prototyping
• Add migrations for production

Solution 2:

• Use migrations only, since the beginning of development

How to find the migrations ?

• File based versions like with EF (Up/Down strategy)?
• Reflection + Version like in Orchard

Should be based on NHibernate Dialects like in Orchard

Migrations can be per index or per application, so in a modular environment like Orchard, migrations can come from different assemblies. But it's maybe the responsibility of the host application to describe where to find all available migrations.

Hi!

I recently watched the YesSql presenation from Orchard Harvest 2017 and went through the unittests.
What I could not find out is:
Is it possible to create indices with the data of multiple different documents?

For now, I only saw that it was possible to create multiple indices for the same document. (User => User by Rolename, User by Username, ....)

However, I wonder if it was possible to create an index like this (example from RavenDB) wher I index a user by his/her region, country and orders:

public CustomerIndex()
{
    AddMap(customers => from customer in customers
        let region = LoadDocument(customer.RegionId)
        let country = LoadDocument(region.CountryId)
        let orders = LoadDocument(customer.OrderIds)
        select new
        {
            Id = customer.Id,
            CustomerName = customer.Name,
            RegionName = region.Name,
            CountryName = country.Name,
            OrdersProcessed = orders.Count(o => o.IsProcessed),
            ProductIds = orders.Select(o => o.ProductId)
        });
    StoreAllFields(FieldStorage.Yes);
}

This would be crucial to use Orchard 2 as an application framework, because there are many real world scenarios that require that kind of index.

But I doubt that this is possible because - as far as I know - YesSql updates the indices within the same transaction. So those updates need to be fast

Am I right?

Related with problem described here OrchardCMS/OrchardCore#742 (comment) : when YesSql has changes pending to store in db if a query is done it stores changes before executing the query. So, changes after that quey are ignored because the entity marked as ToSave are unmarked.

In the weekly meeting @sebastienros proposed to compare objects marked as ToSave using its previous and current serialization value.

So, I understand solution will be to change YesSQ, to make that when changes are stored because of a query execution the entities stored in db still are maintained as entities to save and its serialized values will be kept in the session. Next, on every next query and on final commit the stored serialized values of entities marked to save will be compared with serialization of current entity in order to decide if they need to be stored on the db.

It seems like a major flaw in the design, but that usually means I'm just greatly misunderstanding things :)
I just stored some User objects that have a Name property. Now I decided that I want to be able to query them by Name. When I create the UserByName index and execute a query, I get no results (which makes sense, since the UserByName table is empty). When I add some more users, they will be found.
Now what would be the best way to handle this situation? Should I manually populate the index tables?

Currently a collection can be set only with an using statement. We talked about to offer a mechanism based on configuration to set which class uses which collection.

Might be worth implementing something like this https://github.com/aspnet/Diagnostics/tree/dev/src/Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore

This was we could hook in to the diagnostics in Orchard easier

Maybe on initial version YesSql was based on NHibernate, but now I haven't find any dependency on it so I imagine documentation wasn't updated reflecting that.

In Configuring the store section:

A store is just a simple relational database instance. It can be any type of SQL database, as long as NHibernate supports it

In Creating a mapped index section:

Properties are virtual because it will be mapped in NHibernate to a specific table with the same name as the class.

Doesn't have to support locking for now. Options to either update and index (update the index records, usually when the index logic or fields is changed) or to rebuild it when documents have changed.

Add a version column to document tables so that we can optionally check the version when writing documents.

Based on the discussion here with @sebastienros in the Orchard CMS repo , there must be changes in both repos to support the suggested APIs, so the developer can simply use YesSQL data provider(s) as the following:

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbProvider(options =>
        options.UseSqlServer("Server=.;Database=YesSqlDb;Integrated Security=True"));
    ...
}

So, YesSql needs to be configured with a DbFactory instance containing a connection string, which you are showing correctly in the example above.

My initial thoughts here is:

• Create the following projects named YesSql.Provider.InMemory, YesSql.Provider.Sqlite, YesSql.Provider.SqlServer, YesSql.Provider.MySql, YesSql.Provider.Postgres, to support all the data providers which supported by YesSQL, so the user can reference only the one that he interest with (Pluggability)

• Add required unit tests for the suggested APIs

• Provide a web sample as a show case YesSql.Samples.Web

@sebastienros please let me know if you have any further suggestion

Hi, this lib is C# native DB 300kb Dll with ~2600 followers, very popular and free LiteDB fully C#, supported by @mbdavid

Can you please include LiteDB as a YesSql Container as well. It would give us the document functions from YesSql on top of LiteDB, while yesSql can do complex indices, graphs etc - cross platform.

In your samples can you please share some info on joins for e.g. when an Customer PersonVM is needed from ASP Identity user table and Orders total table

How to get subscribe/change notifications from cache, and YesSql. For e.g. force user log out broadcast while user is logged in a session (if his payment expired)

thanks

Have SQL storage bake in by default. Support second level caching by adding an optional store. It can support long lived serialization (on disk) but because it introduces eventual consistency it could be flushed.

Why not 1.0.0 RTM ?

/cc @alexbocharov

Hi,

I'm having some issues using Sqlite "Doesn't support nested transactions" and "table is locked" errors. Your Tutorial mentions Sql Server CE 4, so I thought I'd give that a go, but when I try to use it:

ConnectionFactory = new DbConnectionFactory<SqlCeConnection>( $"Data Source={DatabaseFilename};Cache=Shared", reuseConnection: true), DocumentStorageFactory = new SqlDocumentStorageFactory(), IsolationLevel = IsolationLevel.Serializable

I get a "Unknown connection name:sqlceconnection" error. Looking at the code, I've found this in ISqlDialect.cs:

public static Dictionary<string, ISqlDialect> SqlDialects { get; } = new Dictionary<string, ISqlDialect> { {"sqliteconnection", new SqliteDialect()}, {"sqlconnection", new SqlServerDialect()}, {"mysqlconnection", new MySqlDialect() } };

Could you add support for Sql Server CE by adding an extra entry to this dictionary?

{"sqlceconnection", new SqlServerDialect()}

As it would have the same dialect as Sql Server for the purposes of this library wouldn't it?

Let me know what you think

Thanks,

James

I would like to use YesSQL as ORM for a sqllite db embeded in a React Native app. Do you think has sense to make the effort of providing a JS API with YesSql based on Edge.js?
I have been looking for similar ORMS for js that mix sql and documento db concepts but I haven't found sth like YesSql that can work also with embeded dbs.

• Ensure we can define an index provider on a base class or an interface
• When loading documents by type, also resolve sub-classes and implementations
  • Issue about how to define this list (assembly scanning, metadata on types, configuration, or ultimately configuration with sources from metadata and assembly scanning)

using (var session = store.CreateSession())
{
  session.Save(billg);
  session.Save(billm);
  
  var people = await session.QueryAsync<Person>().List();
}

return 4 elements instead of 2

Applies to: 2.0.0-beta-0005

Loving this project so far!

I may be using the API incorrectly, but I'm getting some unexpected behaviour.

If I create a new object and save it to my SQLite db:

using (var session = _createSessionFactory())
    session.Save(o);

I get a document added to the document/content tables.

If I then retrieve that object:

using (var session = _createSessionFactory()) 
    o = await session.QueryAsync<MyObjectType>().FirstOrDefault();

Make some changes:

o.Property1 = "NewValue"

Then save it back:

using (var session = _createSessionFactory())
    session.Save(o);

There are then 2 documents in the document table. If I get a count of MyObjectType, I get 2.

Am I doing something wrong? Happy to update the wiki with an example if you can provide one please.

Based on the documentation on the link below, Microsoft is discouraging the use of ntext as it "will be removed in the future version of SQL Server."

https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql

Why don't you use LINQ and EF providers to generate SQL but do that manually?

Scenario:

You have a document with field A and B.

I then save away a document.

Then I modify my 'model' so that it has field A and C.

I retrieve the existing document, A will be filled, C will be empty, and then save it away again as-is (as I received it in the first place)

Result: It'll store A and an empty C field, but the contents of the B field will be have been removed.

Imho, there should be a way to make it so that existing 'unmapped' data can be kept when updating an entry. Either by default or optionally.

We're actually considering using this for a 'cloud' storage solution (of 'data', not files) but that would require YesSql to be able to update a record with new data without removing the unmapped data.

For example: one of the 'users' could be running on an 'old' model, without this ability any 'old model' user would be able to 'remove' unmapped field data.

It would kinda beat the cloud concept if we'd have to update all 'clients' at the same time to prevent this kind of loss.

AimOrchard here btw ;)

For performance reason. Then remove the Type column.
Pending: how to handle indexes to different document types.