public async Task DeleteUserDataAsync(string userId)
{
using (var connection = new SQLiteConnection(ConnectionString)) {
await connection.OpenAsync();
var transaction = await connection.BeginTransactionAsync();
var param = new { Id = userId };
// Delete all notes that belong to the given user
string sql = "SELECT NoteId FROM UserNotes WHERE UserId = @Id";
IEnumerable <string> noteIds = await connection.QueryAsync <string>(sql, param);
foreach (string id in noteIds)
{
await DeleteNoteAsync(id, connection);
}
// Delete all labels that belong to the given user
sql = "SELECT LabelId FROM UserLabels WHERE UserId = @Id";
IEnumerable <string> labelIds = await connection.QueryAsync <string>(sql, param);
foreach (string id in labelIds)
{
await DeleteLabelAsync(id, connection);
}
await transaction.CommitAsync();
}
}
/// <summary>
/// 插入数据
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="database">数据库文件</param>
/// <param name="entities">实体列表</param>
public async Task <bool> InsertAsync <T>(string database, IEnumerable <T> entities) where T : class, new()
{
using (var connection = new SQLiteConnection(GetConnectionString(database)))
{
await connection.OpenAsync();
var dbTran = await connection.BeginTransactionAsync();
try
{
foreach (var entity in entities)
{
await connection.InsertAsync(entity, dbTran);
}
dbTran.Commit();
return(true);
}
catch (Exception ex)
{
dbTran.Rollback();
//Logger.Instance.Common.Error($"[ Dapper ] InsertAsync, Database = {database}, Message = {ex.Message}");
return(false);
}
}
}
public async Task InsertCacheCmd(int count)
{
const string methodName = nameof(InsertCacheCmd);
var file = $"db{count}{methodName}.db";
var connectionString = DbHelper.ConnectionString(file);
await ResetDatabase(file);
const int batchSize = 10000;
var guid = Guid.NewGuid().ToString();
var(cmd, p) = CreateCommand();
var pageCount = count / batchSize;
var sw = Stopwatch.StartNew();
for (var i = 0; i < pageCount; i++)
{
await using var sqLiteConnection = new SQLiteConnection(connectionString);
cmd.Connection = sqLiteConnection;
await sqLiteConnection.OpenAsync();
await using var beginTransactionAsync = await sqLiteConnection.BeginTransactionAsync();
for (var j = 0; j < batchSize; j++)
{
p.Value = guid;
await cmd.ExecuteNonQueryAsync();
}
await beginTransactionAsync.CommitAsync();
}
sw.Stop();
Console.WriteLine($"cost: {sw.ElapsedMilliseconds}");
}
public async Task CreateDb()
{
var fileName = "data.db";
if (File.Exists(fileName))
{
return;
}
var connectionString = GetConnectionString(fileName);
await using var sqlConnection = new SQLiteConnection(connectionString);
await sqlConnection.OpenAsync();
await using var transaction = await sqlConnection.BeginTransactionAsync();
await sqlConnection.ExecuteAsync(@"
CREATE TABLE Product(
ProductId int PRIMARY KEY,
Color TEXT
)", transaction);
var dict = CreateDict();
foreach (var(_, p) in dict)
{
await sqlConnection.ExecuteAsync(@"
INSERT INTO Product(ProductId,Color)
VALUES(@ProductId,@Color)", p, transaction);
}
await transaction.CommitAsync();
}
public static async Task <KVTransaction> BeginTransactionAsync(SQLiteConnection connection, SemaphoreSlim trxSemaphore)
{
try
{
var transaction = await connection.BeginTransactionAsync();
return(new KVTransaction(transaction, trxSemaphore));
}
catch (Exception ex)
{
Console.WriteLine($"{ex}");
trxSemaphore.Release();
throw;
}
}
public static async Task <System.Data.IDbTransaction> TryBeginTransaction(DbType type, string db)
{
DbConnection conn = null;
try
{
switch (type)
{
case DbType.MySql:
conn = new MySqlConnection(db);
break;
case DbType.SqlServer:
conn = new SqlConnection(db);
break;
case DbType.PostgreSql:
conn = new NpgsqlConnection(db);
break;
case DbType.Sqlite:
conn = new SQLiteConnection(db);
break;
default:
throw new NotImplementedException();
}
await conn.OpenAsync();
return(await conn.BeginTransactionAsync());
}
catch (Exception ex)
{
#if DEBUG
Console.WriteLine(ex.Message);
#endif
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
await conn.CloseAsync();
}
conn.Dispose();
return(null);
}
}
public async Task <string> CreateLabelAsync(string userId, Label label)
{
label.Id = Guid.NewGuid().ToString();
using (var connection = new SQLiteConnection(ConnectionString)) {
connection.Open();
var transaction = await connection.BeginTransactionAsync();
string sql = "INSERT INTO Labels VALUES (@Id, @Name)";
await connection.ExecuteAsync(sql, label, transaction);
sql = "INSERT INTO UserLabels VALUES (@UserId, @LabelId)";
var param = new { UserId = userId, LabelID = label.Id };
await connection.ExecuteAsync(sql, param, transaction);
await transaction.CommitAsync();
return(label.Id);
}
}
public async Task <string> CreateNoteAsync(string userId, Note note)
{
note.Id = Guid.NewGuid().ToString();
using (var connection = new SQLiteConnection(ConnectionString)) {
connection.Open();
var transaction = await connection.BeginTransactionAsync();
string sql = @"INSERT INTO Notes VALUES (@Id, @Title, @Content, @Pinned, @ModifiedDate)";
await connection.ExecuteAsync(sql, note, transaction);
sql = "INSERT INTO UserNotes VALUES (@UserId, @NoteId)";
var param = new { UserId = userId, NoteId = note.Id };
await connection.ExecuteAsync(sql, param, transaction);
await transaction.CommitAsync();
return(note.Id);
}
}
public async Task <int> DeleteLabelAsync(string id)
{
if (!await LabelExistsAsync(id))
{
return(0);
}
using (var connection = new SQLiteConnection(ConnectionString)) {
await connection.OpenAsync();
var transaction = await connection.BeginTransactionAsync();
int deleteCount = await DeleteLabelAsync(id, connection);
await transaction.CommitAsync();
return(deleteCount);
}
}
private static async Task PopulateDatabase(SQLiteConnection connection)
{
await using var transaction = await connection.BeginTransactionAsync();
await using var command = connection.CreateCommand();
command.CommandText = @"
CREATE TABLE characters (id INTEGER NOT NULL, name NVARCHAR(20) NOT NULL, type NVARCHAR(10) NOT NULL, homePlanet INTEGER NULL, primaryFunction NVARCHAR(20) NULL);
CREATE TABLE friends (characterId INTEGER NOT NULL, friendId INTEGER NOT NULL);
CREATE TABLE planets (id INTEGER NOT NULL, name NVARCHAR(20) NOT NULL);
INSERT INTO planets (id, name) VALUES (1, 'Tatooine');
INSERT INTO characters (id, name, type, homePlanet) VALUES (1, 'Luke', 'Human', 1);
INSERT INTO characters (id, name, type, homePlanet) VALUES (2, 'Vader', 'Human', 1);
INSERT INTO characters (id, name, type, primaryFunction) VALUES (3, 'R2-D2', 'Droid', 'Astromech');
INSERT INTO characters (id, name, type, primaryFunction) VALUES (4, 'C-3PO', 'Droid', 'Protocol');
INSERT INTO friends (characterId, friendId) VALUES (1, 3);
INSERT INTO friends (characterId, friendId) VALUES (1, 4);
INSERT INTO friends (characterId, friendId) VALUES (3, 1);
INSERT INTO friends (characterId, friendId) VALUES (3, 4);
";
await command.ExecuteNonQueryAsync();
await transaction.CommitAsync();
}
public async Task Test()
{
// arrange
var observer = new Observer();
using var subscription = DiagnosticListener.AllListeners.Subscribe(observer);
var isolationLevel = IsolationLevel.ReadCommitted;
// act
await using var connection = new SQLiteConnection(_connectionString).AddDiagnosting();
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync(isolationLevel);
await connection.QueryAsync("SELECT id, name from test", transaction : transaction);
await transaction.CommitAsync();
await connection.CloseAsync();
// assert
Assert.Equal(8, observer.Events.Count);
{
var first = observer.Events[0];
Assert.Equal(DbDiagnosticListener.EventNames.ConnectionOpening, first.Key);
var before = Assert.IsType <ConnectionPayload>(first.Value);
Assert.Equal(connection, before.Connection);
var second = observer.Events[1];
Assert.Equal(DbDiagnosticListener.EventNames.ConnectionOpened, second.Key);
var after = Assert.IsType <ConnectionPayload>(second.Value);
Assert.Equal(connection, after.Connection);
Assert.Equal(before.OperationId, after.OperationId);
Assert.True(before.Timestamp < after.Timestamp);
}
{
var third = observer.Events[2];
Assert.Equal(DbDiagnosticListener.EventNames.CommandExecuting, third.Key);
var before = Assert.IsType <CommandPayload>(third.Value);
Assert.Equal(connection.GetGuid(), before.ConnectionId);
Assert.Equal(transaction.GetId(), before.TransactionId);
var fourth = observer.Events[3];
Assert.Equal(DbDiagnosticListener.EventNames.CommandExecuted, fourth.Key);
var after = Assert.IsType <CommandPayload>(fourth.Value);
Assert.Equal(connection.GetGuid(), before.ConnectionId);
Assert.Equal(transaction.GetId(), before.TransactionId);
Assert.Equal(before.Command, after.Command);
Assert.Equal(before.OperationId, after.OperationId);
Assert.True(before.Timestamp < after.Timestamp);
}
{
var fifth = observer.Events[4];
Assert.Equal(DbDiagnosticListener.EventNames.TransactionCommitting, fifth.Key);
var before = Assert.IsType <TransactionPayload>(fifth.Value);
Assert.Equal(connection, before.Connection);
Assert.Equal(transaction.GetId(), before.TransactionId);
Assert.Equal(isolationLevel, before.IsolationLevel);
var sixth = observer.Events[5];
Assert.Equal(DbDiagnosticListener.EventNames.TransactionCommitted, sixth.Key);
var after = Assert.IsType <TransactionPayload>(sixth.Value);
Assert.Equal(connection, after.Connection);
Assert.Equal(transaction.GetId(), after.TransactionId);
Assert.Equal(isolationLevel, after.IsolationLevel);
Assert.Equal(before.OperationId, after.OperationId);
Assert.True(before.Timestamp < after.Timestamp);
}
{
var seventh = observer.Events[6];
Assert.Equal(DbDiagnosticListener.EventNames.ConnectionClosing, seventh.Key);
var before = Assert.IsType <ConnectionPayload>(seventh.Value);
Assert.Equal(connection, before.Connection);
var eighth = observer.Events[7];
Assert.Equal(DbDiagnosticListener.EventNames.ConnectionClosed, eighth.Key);
var after = Assert.IsType <ConnectionPayload>(eighth.Value);
Assert.Equal(connection, after.Connection);
Assert.Equal(before.OperationId, after.OperationId);
Assert.True(before.Timestamp < after.Timestamp);
}
}
public async Task InsertChannelClassBatchItemSetResult(int count)
{
const string methodName = nameof(InsertChannelClassBatchItemSetResult);
var file = $"db{count}{methodName}.db";
var connectionString = DbHelper.ConnectionString(file);
await ResetDatabase(file);
const int batchSize = 10000;
var guid = Guid.NewGuid().ToString();
var(cmd, p) = CreateCommand();
await using var sqLiteConnection = new SQLiteConnection(connectionString);
await sqLiteConnection.OpenAsync();
cmd.Connection = sqLiteConnection;
var pool = ObjectPool.Create <ClassBatchValueItem>();
var arrayPool = ArrayPool <ClassBatchValueItem> .Create();
var resultPool = ObjectPool.Create <ClassBatchValueResultItem>();
var taskChannel = Channel.CreateUnbounded <ClassBatchValueItem>();
var resultChannel = Channel.CreateUnbounded <ClassBatchValueResultItem>();
#pragma warning disable 4014
Task.Factory.StartNew(async() =>
{
while (await resultChannel.Reader.WaitToReadAsync())
{
while (resultChannel.Reader.TryRead(out var item))
{
try
{
if (item.IsSuccess)
{
var item1 = item;
Parallel.For(0, item.ItemCount, i =>
{
var taskCompletionSource = item1.Items[i];
taskCompletionSource.Tcs.SetResult(item1.Result);
});
}
else
{
var item1 = item;
Parallel.For(0, item.ItemCount, i =>
{
var taskCompletionSource = item1.Items[i];
taskCompletionSource.Tcs.SetException(item1.Exception);
});
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
resultPool.Return(item);
foreach (var task in item.Items)
{
pool.Return(task);
}
arrayPool.Return(item.Items);
}
}
}
}, TaskCreationOptions.LongRunning).Unwrap();
#pragma warning restore 4014
#pragma warning disable 4014
Task.Factory.StartNew(async() =>
{
var waiting = TimeSpan.FromMilliseconds(50);
while (await taskChannel.Reader.WaitToReadAsync())
{
var list = new List <ClassBatchValueItem>();
var time = DateTimeOffset.Now;
while (list.Count < batchSize &&
DateTimeOffset.Now - time < waiting &&
taskChannel.Reader.TryRead(out var item))
{
list.Add(item);
}
if (list.Any())
{
await using var transaction = await sqLiteConnection.BeginTransactionAsync();
foreach (var item in list)
{
p.Value = item.Data;
await cmd.ExecuteNonQueryAsync();
}
try
{
await transaction.CommitAsync();
var batchResultItem = resultPool.Get();
batchResultItem.IsSuccess = true;
batchResultItem.Result = 0;
var classBatchValueItems = arrayPool.Rent(list.Count);
list.CopyTo(classBatchValueItems);
batchResultItem.Items = classBatchValueItems;
batchResultItem.ItemCount = list.Count;
var valueTask = resultChannel.Writer.WriteAsync(batchResultItem);
if (!valueTask.IsCompleted)
{
await valueTask;
}
}
catch (Exception e)
{
var classBatchResultItem = resultPool.Get();
classBatchResultItem.IsSuccess = false;
classBatchResultItem.Exception = e;
var classBatchValueItems = arrayPool.Rent(list.Count);
list.CopyTo(classBatchValueItems);
classBatchResultItem.Items = classBatchValueItems;
classBatchResultItem.ItemCount = list.Count;
var valueTask = resultChannel.Writer.WriteAsync(classBatchResultItem);
if (!valueTask.IsCompleted)
{
await valueTask;
}
}
finally
{
list.Clear();
}
}
}
}).Unwrap();
#pragma warning restore 4014
var pageCount = count / batchSize;
var sw = Stopwatch.StartNew();
for (var i = 0; i < pageCount; i++)
{
Parallel.For(0, batchSize, async j =>
{
var classBatchItem = pool.Get();
if (classBatchItem.Tcs == null)
{
classBatchItem.Tcs = new ManualResetValueTaskSource <int>();
}
else
{
classBatchItem.Tcs.Reset();
}
classBatchItem.Data = guid;
var valueTask = taskChannel.Writer.WriteAsync(classBatchItem);
if (!valueTask.IsCompleted)
{
await valueTask;
}
var finalValueTask = new ValueTask(classBatchItem.Tcs, classBatchItem.Tcs.Version);
if (!finalValueTask.IsCompleted)
{
await finalValueTask;
}
});
}
taskChannel.Writer.Complete();
await taskChannel.Reader.Completion;
sw.Stop();
Console.WriteLine($"cost: {sw.ElapsedMilliseconds}");
}
public async Task InsertChannelClassBatchItemWithPool(int count)
{
const string methodName = nameof(InsertChannelClassBatchItemWithPool);
var file = $"db{count}{methodName}.db";
var connectionString = DbHelper.ConnectionString(file);
await ResetDatabase(file);
const int batchSize = 10000;
var guid = Guid.NewGuid().ToString();
var(cmd, p) = CreateCommand();
await using var sqLiteConnection = new SQLiteConnection(connectionString);
await sqLiteConnection.OpenAsync();
cmd.Connection = sqLiteConnection;
var bounded = Channel.CreateUnbounded <ClassBatchItem>();
var pool = ObjectPool.Create <ClassBatchItem>();
#pragma warning disable 4014
Task.Factory.StartNew(async() =>
{
var waiting = TimeSpan.FromMilliseconds(50);
while (await bounded.Reader.WaitToReadAsync())
{
var list = new List <ClassBatchItem>();
var time = DateTimeOffset.Now;
while (list.Count < batchSize &&
DateTimeOffset.Now - time < waiting &&
bounded.Reader.TryRead(out var item))
{
list.Add(item);
}
if (list.Any())
{
await using var transaction = await sqLiteConnection.BeginTransactionAsync();
foreach (var item in list)
{
p.Value = item.Data;
await cmd.ExecuteNonQueryAsync();
}
try
{
await transaction.CommitAsync();
foreach (var item in list)
{
item.Tcs.SetResult(0);
}
}
catch (Exception e)
{
foreach (var item in list)
{
item.Tcs.SetException(e);
}
}
finally
{
list.Clear();
foreach (var classBatchItem in list)
{
pool.Return(classBatchItem);
}
}
}
}
}).Unwrap();
#pragma warning restore 4014
var pageCount = count / batchSize;
var sw = Stopwatch.StartNew();
for (var i = 0; i < pageCount; i++)
{
Parallel.For(0, batchSize, async j =>
{
var tcs = new TaskCompletionSource <int>();
var classBatchItem = pool.Get();
classBatchItem.Tcs = tcs;
classBatchItem.Data = guid;
var valueTask = bounded.Writer.WriteAsync(classBatchItem);
if (!valueTask.IsCompleted)
{
await valueTask;
}
await tcs.Task;
});
}
bounded.Writer.Complete();
await bounded.Reader.Completion;
sw.Stop();
Console.WriteLine($"cost: {sw.ElapsedMilliseconds}");
}
public async Task InsertChannelStructBatchItemValueTask(int count)
{
const string methodName = nameof(InsertChannelStructBatchItemValueTask);
var file = $"db{count}{methodName}.db";
var connectionString = DbHelper.ConnectionString(file);
await ResetDatabase(file);
const int batchSize = 10000;
var guid = Guid.NewGuid().ToString();
var(cmd, p) = CreateCommand();
await using var sqLiteConnection = new SQLiteConnection(connectionString);
await sqLiteConnection.OpenAsync();
cmd.Connection = sqLiteConnection;
var bounded = Channel.CreateUnbounded <StructBatchItemValueTask>();
#pragma warning disable 4014
Task.Factory.StartNew(async() =>
{
var waiting = TimeSpan.FromMilliseconds(50);
while (await bounded.Reader.WaitToReadAsync())
{
var list = new List <StructBatchItemValueTask>();
var time = DateTimeOffset.Now;
while (list.Count < batchSize &&
DateTimeOffset.Now - time < waiting &&
bounded.Reader.TryRead(out var item))
{
list.Add(item);
}
if (list.Any())
{
await using var transaction = await sqLiteConnection.BeginTransactionAsync();
foreach (var item in list)
{
p.Value = item.Data;
await cmd.ExecuteNonQueryAsync();
}
try
{
await transaction.CommitAsync();
foreach (var item in list.AsParallel())
{
item.Tcs.SetResult(0);
}
}
catch (Exception e)
{
foreach (var item in list.AsParallel())
{
item.Tcs.SetException(e);
}
}
finally
{
list.Clear();
}
}
}
}).Unwrap();
#pragma warning restore 4014
var pageCount = count / batchSize;
var sw = Stopwatch.StartNew();
for (var i = 0; i < pageCount; i++)
{
await Enumerable.Range(0, batchSize)
.ToObservable()
.Select(j => Observable.FromAsync(async() =>
{
var valueTaskSource = new ManualResetValueTaskSource <int>();
var valueTask = bounded.Writer.WriteAsync(new StructBatchItemValueTask
{
Tcs = valueTaskSource,
Data = guid
});
if (!valueTask.IsCompleted)
{
await valueTask;
}
var finalValueTask = new ValueTask(valueTaskSource, valueTaskSource.Version);
if (!finalValueTask.IsCompleted)
{
await finalValueTask;
}
}))
.Merge()
.ToTask();
}
bounded.Writer.Complete();
await bounded.Reader.Completion;
sw.Stop();
Console.WriteLine($"cost: {sw.ElapsedMilliseconds} ms");
}
public static async Task <KVTransaction> BeginTransactionAsync(SQLiteConnection connection)
{
var transaction = await connection.BeginTransactionAsync();
return(new KVTransaction(transaction));
}
