|
#:package Microsoft.Data.Sqlite |
|
#:package Microsoft.Data.Sqlite |
|
#:package Dapper |
|
#:package TestBase |
|
|
|
using Microsoft.Data.Sqlite; |
|
using Dapper; |
|
using TestBase; |
|
using TooString; |
|
|
|
new SelfTest().SqliteDbOpensAndSharesInMemoryNamesInstances(); |
|
new SelfTest().SqliteDbOpensAndSharesInMemoryAnonymousInstance(); |
|
|
|
//```csharp |
|
// Create a shared in-memory database and bootstrap it with some sql. |
|
using var dbInstance1 = SqliteDb |
|
.InMemoryShared |
|
.Named("Db1") |
|
.InitFromString("Create Table Strings(Id string)"); |
|
|
|
var db1 = dbInstance1.Connection; |
|
db1.Execute("Insert Into Strings Values('1')"); |
|
db1.Query<string>("Select * from Strings"); |
|
|
|
// Create a shared in-memory database and bootstrap it with a sql script file. |
|
var sqlScriptPath = System.IO.Path.GetTempFileName(); |
|
System.IO.File.WriteAllText(sqlScriptPath, "Create Table Files(Id string)"); |
|
using var dbInstance2 = SqliteDb |
|
.InMemoryShared |
|
.Named("Db2") |
|
.InitFromFile(sqlScriptPath); |
|
var db2 = dbInstance2.Connection; |
|
System.IO.File.Delete(sqlScriptPath); |
|
|
|
// Initting an already initted SqliteDb instance will do nothing. |
|
dbInstance1.InitFromFile("No error here. dbInstance1 is already initted."); |
|
|
|
|
|
// Other references with the same named in-memory database will see the existing database. |
|
// result will contain the row inserted above. |
|
using var anotherRefenceToDbs1 = SqliteDb.InMemoryShared.Named("Db1"); |
|
var result= anotherRefenceToDbs1.Connection.Query<string>("Select * from Strings").ToList(); |
|
result.ForEach(Console.WriteLine); |
|
result.ShouldBeOfLength(1).Single().ShouldBe("1"); |
|
|
|
//Creating a Sqlite db on disk works as normal |
|
var file1 = System.IO.Path.GetTempFileName(); |
|
using var fileBackedDb1 = new SqliteDb(file1); |
|
Console.WriteLine(fileBackedDb1.Connection.ConnectionString); |
|
//``` |
|
|
|
//[TestClass] |
|
public class SelfTest |
|
{ |
|
//[TestMethod] |
|
public void SqliteDbOpensAndSharesInMemoryNamesInstances() |
|
{ |
|
var dbs = Enumerable |
|
.Range(0, 10) |
|
.Select(i => SqliteDb.InMemoryShared.Named(i.ToString())).ToList(); |
|
|
|
dbs.ForEach(db => |
|
{ |
|
db.InitFromString( |
|
$"Create Table A(Name string);Insert Into A Values('Hello {db.DataSource}')"); |
|
var rows = |
|
db.Connection.Query<string>("Select * from A"); |
|
rows.Single().ShouldBe($"Hello {db.DataSource}"); |
|
}); |
|
|
|
dbs.ForEach(db => |
|
{ |
|
using var db2 = new SqliteConnection(db.Connection!.ConnectionString); |
|
var rows = db2.Query<string>("Select * from A").ToList(); |
|
Console.WriteLine(rows.ToJson()); |
|
rows.Single().ShouldBe($"Hello {db.DataSource}"); |
|
}); |
|
|
|
dbs.ForEach(db => db.Dispose()); |
|
|
|
//Can't really prove that Sqlite drops the in-memory databases? |
|
} |
|
|
|
//[TestMethod] |
|
public void SqliteDbOpensAndSharesInMemoryAnonymousInstance() |
|
{ |
|
var id = Guid.NewGuid().ToString(); |
|
using var db = new SqliteDb() |
|
.InitFromString($"Create Table A(Name string);Insert Into A Values('Hello {id}')") |
|
.Connection; |
|
var rows = db.Query<string>("Select * from A").ToList(); |
|
Console.WriteLine(rows.ToJson()); |
|
rows.Single().ShouldBe($"Hello {id}"); |
|
|
|
|
|
var dbs = Enumerable |
|
.Range(0, 10) |
|
.Select(i => SqliteDb.InMemoryShared.AnonymousInstance).ToList(); |
|
|
|
dbs.ForEach(db => |
|
{ |
|
var rows = db.Connection!.Query<string>("Select * from A"); |
|
rows.Single().ShouldBe($"Hello {id}"); |
|
db.Dispose(); |
|
}); |
|
|
|
//Can't really prove that Sqlite drops the in-memory database? |
|
} |
|
} |
|
|
|
|
|
/// <summary> |
|
/// This class encapsulates the creation and one-time bootstrapping |
|
/// of a Sqlite database. |
|
/// In particular, it can create named or anonymous shared in-memory databases |
|
/// which will persist until the last connection to the shared database is |
|
/// closed. |
|
/// </summary> |
|
/// <remarks> |
|
/// <list type="bullet"> |
|
/// <item>For named, shared, in-memory instances use |
|
/// <see cref="SqliteDb.InMemoryNamedShared.Instance(string)"/>.</item> |
|
/// <item>For access to the anonymous, shared, in-memory instance |
|
/// use <see cref="SqliteDb.InMemoryAnonymousShared.Instance"/>.</item> |
|
/// <item>For disk-backed instances, or any other option, pass an appropriate |
|
/// Sqlite Data Source to the constructor, for instance new("path/to/myfile.db").</item> |
|
/// </list> |
|
/// </remarks> |
|
class SqliteDb : IDisposable, IAsyncDisposable |
|
{ |
|
|
|
public static class InMemoryShared |
|
{ |
|
/// <summary> |
|
/// Returns a <see cref="SqliteDb"/> whose <see cref="Connection"/> |
|
/// is to an in-memory database that is shared with any other |
|
/// connection using the same <paramref name="name"/>. |
|
/// </summary> |
|
/// <param name="name"></param> |
|
/// <returns></returns> |
|
public static SqliteDb Named(string name) |
|
=> new(UriFor(name)); |
|
|
|
/// <summary> |
|
/// Return a Data Source name for use in a Sqlite connection |
|
/// string to connect to a shared, named, in-memory database. |
|
/// </summary> |
|
/// <param name="name"></param> |
|
/// <returns></returns> |
|
internal static string UriFor(string name) |
|
=> $"file:{name}?mode=memory&cache=shared"; |
|
|
|
/// <summary> |
|
/// Returns a <see cref="SqliteDb"/> whose <see cref="Connection"/> |
|
/// is to an in-memory database that is shared with any other |
|
/// connection using the anonymous shared instance. |
|
/// </summary> |
|
/// <param name="name"></param> |
|
/// <returns></returns> |
|
public static SqliteDb AnonymousInstance => new(); |
|
|
|
/// <summary> |
|
/// Sqlite magic string for un-named in-memory database with shared cache. |
|
/// Every new connection using this string will get the same database. |
|
/// The database will persist until the last connection is closed. |
|
/// </summary> |
|
/// <seealso href="https://www.sqlite.org/inmemorydb.html"/> |
|
public const string AnonymousInstanceUri = "file::memory:?cache=shared"; |
|
} |
|
|
|
/// <summary> |
|
/// The data source passed to the constructor. Either a file path |
|
/// or magic string such as <see cref="InMemoryAnonymousShared.Uri"/> |
|
/// or a named in-memory string such as <see cref="InMemoryNamedShared.Uri(string)"/>. |
|
/// </summary> |
|
public string DataSource { get; } |
|
|
|
/// <summary>A single connection to the database.</summary> |
|
public SqliteConnection Connection { get; private set; } |
|
|
|
/// <summary> |
|
/// Create a new database and initialise it with the contents |
|
/// of the given SQL script file. |
|
/// If one of <see cref="InitFromFile"/> or <see cref="InitFromString"/> |
|
/// has already been called, this method does nothing. |
|
/// </summary> |
|
/// <param name="sqlScriptFile"></param> |
|
/// <returns><c>this</c></returns> |
|
public SqliteDb InitFromFile(string sqlScriptFile) |
|
{ |
|
lock (dbInittedLock) |
|
{ |
|
if (isInitted) return this; |
|
string dbDeployScript = File.ReadAllText(sqlScriptFile); |
|
InitFromString(dbDeployScript); |
|
} |
|
return this; |
|
} |
|
|
|
/// <summary> |
|
/// Create a new database and initialise it with the |
|
/// given SQL script. |
|
/// If one of <see cref="InitFromFile"/> or <see cref="InitFromString"/> |
|
/// has already been called, this method does nothing. |
|
/// </summary> |
|
/// <param name="sql"></param> |
|
/// <returns><c>this</c></returns> |
|
public SqliteDb InitFromString(string sql) |
|
{ |
|
lock (dbInittedLock) |
|
{ |
|
if (isInitted) return this; |
|
|
|
Connection = new SqliteConnection($"Data Source=" + DataSource); |
|
Connection.Open(); |
|
using var cmd = Connection.CreateCommand(); |
|
cmd.CommandText = sql; |
|
cmd.ExecuteNonQuery(); |
|
isInitted = true; |
|
} |
|
return this; |
|
} |
|
|
|
readonly Lock dbInittedLock = new(); |
|
bool isInitted; |
|
|
|
/// <summary> |
|
/// This class encapsulates the creation and one-time bootstrapping |
|
/// of a Sqlite database. |
|
/// In particular, it can create named or anonymous shared in-memory databases |
|
/// which will persist until the last connection to the shared database is |
|
/// closed. |
|
/// </summary> |
|
/// <param name="dataSource"> |
|
/// Either |
|
/// <list type="bullet"> |
|
/// <item>A file path to a sqlite database. This can be an existing file or one |
|
/// to be created.</item> |
|
/// <item>A uri for an in-memory database, for instance the uris used |
|
/// by <see cref="SqliteDb.InMemoryShared"/></item> |
|
/// </list> |
|
/// Defaults to <see cref="InMemoryShared.AnonymousInstanceUri"/>, which will |
|
/// result in Sqlite creating an anonymous, shared, in-memory database. |
|
/// The database will persist until the last connection to the |
|
/// anonymous shared instance is closed. |
|
/// </param> |
|
/// <remarks> |
|
/// To create named, shared, in-memory instances use |
|
/// <see cref="SqliteDb.InMemoryNamedShared.Instance(string)"/>. |
|
/// </remarks> |
|
public SqliteDb(string dataSource = InMemoryShared.AnonymousInstanceUri) |
|
{ |
|
DataSource = dataSource; |
|
Connection = new SqliteConnection($"Data Source=" + DataSource + ";"); |
|
} |
|
|
|
#region Dispose |
|
/// <summary> |
|
/// Disposes the <see cref="Connection"/>. |
|
/// An in-memory database will be dropped when the last connection |
|
/// to it is closed. |
|
/// </summary> |
|
public void Dispose() => Connection.Dispose(); |
|
|
|
/// <summary> |
|
/// Disposes the <see cref="Connection"/>. |
|
/// An in-memory database will be dropped when the last connection |
|
/// to it is closed. |
|
/// </summary> |
|
public ValueTask DisposeAsync() => Connection.DisposeAsync(); |
|
|
|
#endregion |
|
} |
