Code for a new SQLite adapter #143
Description
Hi, I have used the code below to clean up a SQLite database.
I'm posting it here, in case anyone wants to create a new adapter.
using Dapper;
using System.Data.Common;
using System.Text;
namespace Test;
public sealed class DatabaseCleaner {
readonly DatabaseConnectionFactory databaseConnectionFactory;
readonly IEnumerable<string> ignoredTables = ["sqlite_sequence"];
public DatabaseCleaner(DatabaseConnectionFactory databaseConnectionFactory, IEnumerable<string>? ignoredTables) {
this.databaseConnectionFactory = databaseConnectionFactory;
if(ignoredTables is not null)
this.ignoredTables = this.ignoredTables.Union(ignoredTables).ToArray();
using DbConnection connection = databaseConnectionFactory.Create();
Command = GetCommand(connection, this.ignoredTables);
static string BuildCommand(IList<string> orderedTables) {
StringBuilder stringBuilder = new();
foreach(string table in orderedTables)
stringBuilder.Append("DELETE FROM ").Append(table).Append(';');
return stringBuilder.ToString();
}
static string GetCommand(DbConnection connection, IEnumerable<string> ignoredTables) {
var tables = GetTables(connection, ignoredTables);
var relationships = GetRelationships(connection);
var orderedTables = OrderTables(tables, relationships);
return BuildCommand(orderedTables);
}
static IList<(string PrimaryKeyTable, string ForeignKeyTable)> GetRelationships(DbConnection connection) {
var relationships = connection.Query<(string PrimaryKeyTable, string ForeignKeyTable)>("SELECT p.'table', s.name FROM sqlite_schema s JOIN pragma_foreign_key_list(s.name) p ON s.name <> p.'table' WHERE s.type = 'table'");
return relationships.ToList();
}
static IList<string> GetTables(DbConnection connection, IEnumerable<string> ignoredTables) {
var tables = connection.Query<string>("SELECT name FROM sqlite_schema WHERE type = 'table'");
return tables.Except(ignoredTables).ToList();
}
static IList<string> OrderTables(IList<string> tables, IList<(string PrimaryKeyTable, string ForeignKeyTable)> relationships) {
List<string> orderedTables = new(tables.Count);
while(tables.Count > 0) {
string[] leafs = tables.Except(relationships.Select(r => r.PrimaryKeyTable)).ToArray();
orderedTables.AddRange(leafs);
foreach(string leaf in leafs) {
tables.Remove(leaf);
foreach(var relationship in relationships.Where(r => r.ForeignKeyTable == leaf).ToArray())
relationships.Remove(relationship);
}
}
return orderedTables;
}
}
public string Command { get; }
public void Clear() {
if(string.IsNullOrEmpty(Command))
return;
using DbConnection connection = databaseConnectionFactory.Create();
connection.Execute(Command);
}
}