public async Task ShouldSeedExistingIds()
{
using (_tempFolder = new TemporaryFolder())
{
var connectionString = @"Data Source=" + _tempFolder.Folder + "yessql.db;Cache=Shared";
var store1 = await StoreFactory.CreateAsync(new Configuration().UseSqLite(connectionString).SetTablePrefix(TablePrefix).UseDefaultIdGenerator());
using (var session1 = store1.CreateSession())
{
var p1 = new Person {
Firstname = "Bill"
};
session1.Save(p1);
Assert.Equal(1, p1.Id);
}
var store2 = await StoreFactory.CreateAsync(new Configuration().UseSqLite(connectionString).SetTablePrefix(TablePrefix).UseDefaultIdGenerator());
using (var session2 = store2.CreateSession())
{
var p2 = new Person {
Firstname = "Bill"
};
session2.Save(p2);
Assert.Equal(2, p2.Id);
}
}
}
public async Task ForeignKeyOfIndexesMustBe_DeleteCascated()
{
var configuration = CreateConfiguration();
_store = await StoreFactory.CreateAsync(configuration);
// First store register the index
_store.RegisterIndexes <PersonIndexProvider>();
var bill = new Person
{
Firstname = "Bill",
Lastname = "Gates"
};
using (var session = _store.CreateSession())
{
session.Save(bill);
}
// second store, don't register the index
_store = await StoreFactory.CreateAsync(configuration);
using (var session = _store.CreateSession())
{
var person = await session.Query().For <Person>().FirstOrDefaultAsync();
Assert.NotNull(person);
session.Delete(person);
}
}
public ContentItemsFieldTypeTests()
{
_tempFilename = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
var connectionString = @"Data Source=" + _tempFilename + ";Cache=Shared";
_store = StoreFactory.CreateAsync(new Configuration().UseSqLite(connectionString)).GetAwaiter().GetResult();
CreateTables();
}
public async Task ShouldGenerateIdsWithConcurrentStores(string collection)
{
var configuration = new Configuration().UseSqlServer(ConnectionString).SetTablePrefix("Store1").UseBlockIdGenerator();
using (var connection = configuration.ConnectionFactory.CreateConnection())
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction(configuration.IsolationLevel))
{
var builder = new SchemaBuilder(configuration, transaction, throwOnError: false);
builder.DropTable(configuration.TableNameConvention.GetDocumentTable(""));
builder.DropTable("Identifiers");
transaction.Commit();
}
}
var cts = new CancellationTokenSource(TimeSpan.FromMinutes(1));
var man = new ManualResetEventSlim();
var concurrency = 8;
var MaxTransactions = 5000;
long lastId = 0;
var results = new bool[2 * MaxTransactions];
var tasks = Enumerable.Range(1, concurrency).Select(i => Task.Run(async() =>
{
var store1 = await StoreFactory.CreateAsync(configuration);
await store1.InitializeCollectionAsync(collection);
long taskId;
man.Wait();
while (!cts.IsCancellationRequested)
{
lastId = taskId = store1.Configuration.IdGenerator.GetNextId(collection);
if (taskId > MaxTransactions)
{
break;
}
Assert.False(results[taskId], $"Found duplicate identifier: '{taskId}'");
results[taskId] = true;
}
})).ToList();
await Task.Delay(1000);
man.Set();
await Task.WhenAll(tasks);
Assert.True(lastId >= MaxTransactions, $"lastId: {lastId}");
}
static async Task MainAsync(string[] args)
{
var configuration = new Configuration()
.UseSqlServer(@"Data Source =.; Initial Catalog = yessql; Integrated Security = True")
.SetTablePrefix("Bench");
using (var connection = configuration.ConnectionFactory.CreateConnection())
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction(configuration.IsolationLevel))
{
var builder = new SchemaBuilder(configuration, transaction);
builder.CreateMapIndexTable <UserByName>(c => c
.Column <string>("Name")
.Column <bool>("Adult")
.Column <int>("Age")
);
transaction.Commit();
}
}
var store = await StoreFactory.CreateAsync(configuration);
store.RegisterIndexes <UserIndexProvider>();
using (var session = store.CreateSession())
{
var user = await session.Query <User>().FirstOrDefaultAsync();
var bill = new User
{
Name = "Bill",
Adult = true,
Age = 1
};
session.Save(bill);
}
using (var session = store.CreateSession())
{
var user = await session.Query <User, UserByName>().Where(x => x.Adult == true).FirstOrDefaultAsync();
user = await session.Query <User, UserByName>().Where(x => x.Age == 1).FirstOrDefaultAsync();
user = await session.Query <User, UserByName>().Where(x => x.Age == 1 && x.Adult).FirstOrDefaultAsync();
user = await session.Query <User, UserByName>().Where(x => x.Name.StartsWith("B")).FirstOrDefaultAsync();
}
}
public ContentItemsFieldTypeTests()
{
var connectionStringTemplate = @"Data Source={0};Cache=Shared";
_tempFilename = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
_store = StoreFactory.CreateAsync(new Configuration().UseSqLite(String.Format(connectionStringTemplate, _tempFilename))).GetAwaiter().GetResult();
_prefix = "tp";
_prefixedStore = StoreFactory.CreateAsync(new Configuration().UseSqLite(String.Format(connectionStringTemplate, _tempFilename + _prefix)).SetTablePrefix(_prefix + "_")).GetAwaiter().GetResult();
CreateTables(_store);
CreateTables(_prefixedStore);
}
private async Task InitializeAsync()
{
var connectionStringTemplate = @"Data Source={0};Cache=Shared";
_tempFilename = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
_store = await StoreFactory.CreateAsync(new Configuration().UseSqLite(String.Format(connectionStringTemplate, _tempFilename)));
_prefix = "tp";
_prefixedStore = await StoreFactory.CreateAsync(new Configuration().UseSqLite(String.Format(connectionStringTemplate, _tempFilename + _prefix)).SetTablePrefix(_prefix + "_"));
await CreateTablesAsync(_store);
await CreateTablesAsync(_prefixedStore);
}
private static IStore CreateStore()
{
IConfiguration storeConfiguration = new YesSql.Configuration();
var databaseFile = Path.Combine("yessql.db");
storeConfiguration
.UseSqLite($"Data Source={databaseFile};Cache=Shared", IsolationLevel.ReadUncommitted)
.UseDefaultIdGenerator();
var store = StoreFactory.CreateAsync(storeConfiguration).GetAwaiter().GetResult();
store.RegisterIndexes(new UserIndexProvider());
return(store);
}
public async Task ShouldSeedExistingIds()
{
var configuration = new Configuration().UseSqlServer(ConnectionString).SetTablePrefix("Store1").UseBlockIdGenerator();
using (var connection = configuration.ConnectionFactory.CreateConnection())
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
var builder = new SchemaBuilder(configuration, transaction, throwOnError: false);
builder.DropTable(configuration.TableNameConvention.GetDocumentTable(""));
builder.DropTable("Identifiers");
transaction.Commit();
}
}
var store1 = await StoreFactory.CreateAsync(configuration);
using (var session1 = store1.CreateSession())
{
var p1 = new Person {
Firstname = "Bill"
};
session1.Save(p1);
Assert.Equal(1, p1.Id);
}
var store2 = await StoreFactory.CreateAsync(new Configuration().UseSqlServer(ConnectionString).SetTablePrefix("Store1").UseBlockIdGenerator());
using (var session2 = store2.CreateSession())
{
var p2 = new Person {
Firstname = "Bill"
};
session2.Save(p2);
Assert.Equal(21, p2.Id);
}
}
public static IServiceCollection AddDbProvider(
this IServiceCollection services,
Action <IConfiguration> setupAction)
{
if (services == null)
{
throw new ArgumentNullException(nameof(services));
}
if (setupAction == null)
{
throw new ArgumentNullException(nameof(setupAction));
}
var config = new Configuration();
setupAction.Invoke(config);
services.AddSingleton <IStore>(StoreFactory.CreateAsync(config).GetAwaiter().GetResult());
return(services);
}
static async Task MainAsync(string[] args)
{
var store = await StoreFactory.CreateAsync(
new Configuration()
.UseSqlServer(@"Data Source =.; Initial Catalog = yessql; Integrated Security = True")
.SetTablePrefix("Hi")
);
using (var connection = store.Configuration.ConnectionFactory.CreateConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction(store.Configuration.IsolationLevel))
{
new SchemaBuilder(store.Configuration, transaction)
.CreateMapIndexTable <BlogPostByAuthor>(table => table
.Column <string>("Author")
)
.CreateReduceIndexTable <BlogPostByDay>(table => table
.Column <int>("Count")
.Column <int>("Day")
);
transaction.Commit();
}
};
// register available indexes
store.RegisterIndexes <BlogPostIndexProvider>();
// 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);
}
// loading a single blog post
using (var session = store.CreateSession())
{
var p = await session.Query().For <BlogPost>().FirstOrDefaultAsync();
Console.WriteLine(p.Title); // > Hello YesSql
}
// loading blog posts by author
using (var session = store.CreateSession())
{
var ps = await session.Query <BlogPost, BlogPostByAuthor>().Where(x => x.Author.StartsWith("B")).ListAsync();
foreach (var p in ps)
{
Console.WriteLine(p.Author); // > Bill
}
}
// loading blog posts by day of publication
using (var session = store.CreateSession())
{
var ps = await session.Query <BlogPost, BlogPostByDay>(x => x.Day == DateTime.UtcNow.ToString("yyyyMMdd")).ListAsync();
foreach (var p in ps)
{
Console.WriteLine(p.PublishedUtc); // > [Now]
}
}
// counting blog posts by day
using (var session = store.CreateSession())
{
var days = await session.QueryIndex <BlogPostByDay>().ListAsync();
foreach (var day in days)
{
Console.WriteLine(day.Day + ": " + day.Count); // > [Today]: 1
}
}
}
/// <summary>
/// Adds tenant level data access services.
/// </summary>
public static OrchardCoreBuilder AddDataAccess(this OrchardCoreBuilder builder)
{
builder.ConfigureServices(services =>
{
services.AddScoped <IDataMigrationManager, DataMigrationManager>();
services.AddScoped <IModularTenantEvents, AutomaticDataMigrations>();
// Adding supported databases
services.TryAddDataProvider(name: "Sql Server", value: "SqlConnection", hasConnectionString: true, sampleConnectionString: "Server=localhost;Database=Orchard;User Id=username;Password=password", hasTablePrefix: true, isDefault: false);
services.TryAddDataProvider(name: "Sqlite", value: "Sqlite", hasConnectionString: false, hasTablePrefix: false, isDefault: true);
services.TryAddDataProvider(name: "MySql", value: "MySql", hasConnectionString: true, sampleConnectionString: "Server=localhost;Database=Orchard;Uid=username;Pwd=password", hasTablePrefix: true, isDefault: false);
services.TryAddDataProvider(name: "Postgres", value: "Postgres", hasConnectionString: true, sampleConnectionString: "Server=localhost;Port=5432;Database=Orchard;User Id=username;Password=password", hasTablePrefix: true, isDefault: false);
// Configuring data access
services.AddSingleton <IStore>(sp =>
{
var shellSettings = sp.GetService <ShellSettings>();
// Before the setup a 'DatabaseProvider' may be configured without a required 'ConnectionString'.
if (shellSettings.State == TenantState.Uninitialized || shellSettings["DatabaseProvider"] == null)
{
return(null);
}
IConfiguration storeConfiguration = new YesSql.Configuration();
switch (shellSettings["DatabaseProvider"])
{
case "SqlConnection":
storeConfiguration
.UseSqlServer(shellSettings["ConnectionString"], IsolationLevel.ReadUncommitted)
.UseBlockIdGenerator();
break;
case "Sqlite":
var shellOptions = sp.GetService <IOptions <ShellOptions> >();
var option = shellOptions.Value;
var databaseFolder = Path.Combine(option.ShellsApplicationDataPath, option.ShellsContainerName, shellSettings.Name);
var databaseFile = Path.Combine(databaseFolder, "yessql.db");
Directory.CreateDirectory(databaseFolder);
storeConfiguration
.UseSqLite($"Data Source={databaseFile};Cache=Shared", IsolationLevel.ReadUncommitted)
.UseDefaultIdGenerator();
break;
case "MySql":
storeConfiguration
.UseMySql(shellSettings["ConnectionString"], IsolationLevel.ReadUncommitted)
.UseBlockIdGenerator();
break;
case "Postgres":
storeConfiguration
.UsePostgreSql(shellSettings["ConnectionString"], IsolationLevel.ReadUncommitted)
.UseBlockIdGenerator();
break;
default:
throw new ArgumentException("Unknown database provider: " + shellSettings["DatabaseProvider"]);
}
if (!string.IsNullOrWhiteSpace(shellSettings["TablePrefix"]))
{
storeConfiguration = storeConfiguration.SetTablePrefix(shellSettings["TablePrefix"] + "_");
}
var store = StoreFactory.CreateAsync(storeConfiguration).GetAwaiter().GetResult();
var indexes = sp.GetServices <IIndexProvider>();
store.RegisterIndexes(indexes);
return(store);
});
services.AddScoped(sp =>
{
var store = sp.GetService <IStore>();
if (store == null)
{
return(null);
}
var session = store.CreateSession();
var scopedServices = sp.GetServices <IScopedIndexProvider>();
session.RegisterIndexes(scopedServices.ToArray());
ShellScope.RegisterBeforeDispose(scope =>
{
return(session.CommitAsync());
});
return(session);
});
services.AddTransient <IDbConnectionAccessor, DbConnectionAccessor>();
});
return(builder);
}
static void Main(string[] args)
{
// Uncomment to use SQL Server
var configuration = new Configuration()
.UseSqlServer(@"Data Source =.; Initial Catalog = yessql; Integrated Security = True")
.SetTablePrefix("Gating");
// Uncomment to use Sqlite
//var store = new Store(
// new Configuration()
// .UseSqLite("Data Source=yessql.db;Cache=Shared")
// );
// Uncomment to use PostgreSql
//var store = new Store(
// new Configuration()
// .UsePostgreSql(@"Server=localhost;Port=5432;Database=yessql;User Id=root;Password=Password12!;Maximum Pool Size=1024;NoResetOnClose=true;Enlist=false;Max Auto Prepare=200")
// .SetTablePrefix("Gating")
// );
// Uncomment to disable gating
// store.Configuration.DisableQueryGating();
try
{
using (var connection = configuration.ConnectionFactory.CreateConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
new SchemaBuilder(configuration, transaction)
.DropMapIndexTable(nameof(PersonByName))
.DropTable("Identifiers")
.DropTable("Document");
transaction.Commit();
}
}
}
catch { }
using (var connection = configuration.ConnectionFactory.CreateConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var builder = new SchemaBuilder(configuration, transaction)
.CreateMapIndexTable(nameof(PersonByName), column => column
.Column <string>(nameof(PersonByName.SomeName))
);
transaction.Commit();
}
}
var store = StoreFactory.CreateAsync(configuration).GetAwaiter().GetResult();
store.RegisterIndexes <PersonIndexProvider>();
Console.WriteLine("Creating content...");
using (var session = store.CreateSession())
{
for (var i = 0; i < 10000; i++)
{
session.Save(new Person()
{
Firstname = "Steve" + i
});
}
}
// Warmup
Console.WriteLine("Warming up...");
using (var session = store.CreateSession())
{
Task.Run(async() =>
{
for (var i = 0; i < 500; i++)
{
await session.Query().For <Person>().With <PersonByName>(x => x.SomeName.StartsWith("Steve100")).ListAsync();
await session.Query().For <Person>().With <PersonByName>(x => x.SomeName == "Steve200").ListAsync();
}
}).GetAwaiter().GetResult();
}
var concurrency = 20;
var counter = 0;
var MaxTransactions = 50000;
var stopping = false;
var tasks = Enumerable.Range(1, concurrency).Select(i => Task.Run(async() =>
{
Console.WriteLine($"Starting thread {i}");
await Task.Delay(100);
while (!stopping && Interlocked.Add(ref counter, 1) < MaxTransactions)
{
using (var session = store.CreateSession())
{
await session.Query().For <Person>().With <PersonByName>(x => x.SomeName.StartsWith("Steve100")).ListAsync();
await session.Query().For <Person>().With <PersonByName>(x => x.SomeName == "Steve").ListAsync();
await session.Query().For <Person>().With <PersonByName>().Where(x => x.SomeName == "Steve").ListAsync();
}
}
})).ToList();
tasks.Add(Task.Delay(TimeSpan.FromSeconds(3)));
Task.WhenAny(tasks).GetAwaiter().GetResult();
// Flushing tasks
stopping = true;
Task.WhenAll(tasks).GetAwaiter().GetResult();
stopping = false;
Console.WriteLine(counter);
}
static async Task MainAsync(string[] args)
{
var store = await StoreFactory.CreateAsync(
new Configuration()
.UseSqlServer(@"Data Source =.; Initial Catalog = yessql; Integrated Security = True")
.SetTablePrefix("FullText")
);
using (var connection = store.Configuration.ConnectionFactory.CreateConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction(store.Configuration.IsolationLevel))
{
new SchemaBuilder(store.Configuration, transaction)
.CreateReduceIndexTable(nameof(ArticleByWord), table => table
.Column <int>("Count")
.Column <string>("Word")
);
transaction.Commit();
}
}
// register available indexes
store.RegisterIndexes <ArticleIndexProvider>();
// creating articles
using (var session = store.CreateSession())
{
session.Save(new Article {
Content = "This is a white fox"
});
session.Save(new Article {
Content = "This is a brown cat"
});
session.Save(new Article {
Content = "This is a pink elephant"
});
session.Save(new Article {
Content = "This is a white tiger"
});
}
using (var session = store.CreateSession())
{
Console.WriteLine("Simple term: 'white'");
var simple = await session.Query <Article, ArticleByWord>().Where(a => a.Word == "white").ListAsync();
foreach (var article in simple)
{
Console.WriteLine(article.Content);
}
Console.WriteLine("Boolean query: 'white or fox or pink'");
var boolQuery = await session.Query <Article, ArticleByWord>().Where(a => a.Word.IsIn(new[] { "white", "fox", "pink" })).ListAsync();
foreach (var article in boolQuery)
{
Console.WriteLine(article.Content);
}
}
}
