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); } } }