424 lines
22 KiB
C#
424 lines
22 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Diagnostics;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Text.RegularExpressions;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace DbTools {
|
|
public class SqlBuilder {
|
|
|
|
/// <summary>
|
|
/// Retrieves the names of all user-defined tables in the connected SQLite database.
|
|
/// </summary>
|
|
/// <remarks>System tables (e.g., those starting with "sqlite_") are excluded from the results.
|
|
/// The method ensures the connection is open before executing the query.</remarks>
|
|
/// <param name="cn">An open <see cref="IDbConnection"/> to the SQLite database. The connection must not be null.</param>
|
|
/// <returns>An array of strings containing the names of all user-defined tables in the database. The array will be empty
|
|
/// if no tables are found.</returns>
|
|
/// <exception cref="ArgumentNullException">Thrown if <paramref name="cn"/> is <see langword="null"/>.</exception>
|
|
public string[] GetTableNames(IDbConnection cn) {
|
|
if (cn == null) {
|
|
throw new ArgumentNullException("cn");
|
|
}
|
|
List<string> tables = new List<string>();
|
|
if (cn.State != ConnectionState.Open) {
|
|
cn.Open();
|
|
}
|
|
using (IDbCommand cmd = cn.CreateCommand()) {
|
|
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name;";
|
|
using (IDataReader reader = cmd.ExecuteReader()) {
|
|
while (reader.Read()) {
|
|
tables.Add(reader.GetString(0));
|
|
}
|
|
}
|
|
}
|
|
return tables.ToArray();
|
|
}
|
|
|
|
/// <summary>
|
|
/// Compares the tables in two database connections and returns the names of tables that exist in the second
|
|
/// database but are missing from the first.
|
|
/// </summary>
|
|
/// <remarks>This method ensures that both database connections are open before performing the
|
|
/// comparison. It retrieves the table names from each database and identifies the tables that are missing
|
|
/// from the first database.</remarks>
|
|
/// <param name="db1">The database connection representing the first database. The connection must be valid and open, or it will
|
|
/// be opened automatically.</param>
|
|
/// <param name="db2">The database connection representing the second database. The connection must be valid and open, or it will
|
|
/// be opened automatically.</param>
|
|
/// <returns>An array of strings containing the names of tables that are present in the second database but not in the
|
|
/// first. Returns an empty array if no such tables are found.</returns>
|
|
/// <exception cref="ArgumentNullException">Thrown if <paramref name="db1"/> or <paramref name="db2"/> is <see langword="null"/>.</exception>
|
|
public string[] GetMissingTables(IDbConnection db1, IDbConnection db2) {
|
|
if (db1 == null) {
|
|
throw new ArgumentNullException("Must provide a valid DBConnection for db1");
|
|
}
|
|
if (db2 == null) {
|
|
throw new ArgumentNullException("Must provide a valid DBConnection for db2");
|
|
}
|
|
if (db1.State != ConnectionState.Open) {
|
|
db1.Open();
|
|
}
|
|
if (db2.State != ConnectionState.Open) {
|
|
db2.Open();
|
|
}
|
|
|
|
string[] db1Tables = GetTableNames(db1);
|
|
string[] db2Tables = GetTableNames(db2);
|
|
|
|
return db2Tables.Where(f => !db1Tables.Contains(f)).ToArray();
|
|
}
|
|
|
|
/// <summary>
|
|
/// Retrieves the names of tables that exist in the first database but not in the second database.
|
|
/// </summary>
|
|
/// <remarks>This method compares the table names in the two databases and identifies tables that
|
|
/// are unique to the first database. The connections provided will be opened if they are not already
|
|
/// open.</remarks>
|
|
/// <param name="db1">The connection to the first database. The connection must be valid and open, or it will be opened
|
|
/// automatically.</param>
|
|
/// <param name="db2">The connection to the second database. The connection must be valid and open, or it will be opened
|
|
/// automatically.</param>
|
|
/// <returns>An array of strings containing the names of tables that are present in the first database but not in the
|
|
/// second database. Returns an empty array if no such tables exist.</returns>
|
|
/// <exception cref="ArgumentNullException">Thrown if <paramref name="db1"/> or <paramref name="db2"/> is <see langword="null"/>.</exception>
|
|
public string[] GetUnusedTables(IDbConnection db1, IDbConnection db2) {
|
|
if (db1 == null) {
|
|
throw new ArgumentNullException("Must provide a valid DBConnection for db1");
|
|
}
|
|
if (db2 == null) {
|
|
throw new ArgumentNullException("Must provide a valid DBConnection for db2");
|
|
}
|
|
if (db1.State != ConnectionState.Open) {
|
|
db1.Open();
|
|
}
|
|
if (db2.State != ConnectionState.Open) {
|
|
db2.Open();
|
|
}
|
|
|
|
string[] db1Tables = GetTableNames(db1);
|
|
string[] db2Tables = GetTableNames(db2);
|
|
|
|
return db1Tables.Where(f => !db2Tables.Contains(f)).ToArray();
|
|
}
|
|
|
|
/// <summary>
|
|
/// Generates the SQL script to recreate the specified table, including its schema, indexes, triggers, and
|
|
/// optionally its data.
|
|
/// </summary>
|
|
/// <remarks>The generated script includes the table's schema, indexes, and triggers. If a
|
|
/// <paramref name="selectStatement"/> is provided, the script will also include the data returned by the SELECT
|
|
/// statement. The method ensures the database connection is open before executing any commands.</remarks>
|
|
/// <param name="cn">The database connection to use. The connection must be to a SQLite database and can be in any state; the
|
|
/// method will ensure it is open.</param>
|
|
/// <param name="tableName">The name of the table for which to generate the SQL script. Cannot be <see langword="null"/> or empty.</param>
|
|
/// <param name="selectStatement">An optional SQL SELECT statement to retrieve data from the table. If provided, the script will include the
|
|
/// data as part of the output.</param>
|
|
/// <returns>A string containing the SQL script to recreate the table, including its schema, indexes, triggers, and
|
|
/// optionally its data. Returns an empty string if the table's schema cannot be determined.</returns>
|
|
/// <exception cref="ArgumentNullException">Thrown if <paramref name="cn"/> is <see langword="null"/> or if <paramref name="tableName"/> is <see
|
|
/// langword="null"/> or empty.</exception>
|
|
public async Task<string> GetTableCreateSqlAsync(IDbConnection cn, string tableName, string selectStatement = null) {
|
|
if (cn == null) {
|
|
throw new ArgumentNullException("cn");
|
|
}
|
|
if (string.IsNullOrEmpty(tableName)) {
|
|
throw new ArgumentNullException("tableName");
|
|
}
|
|
|
|
if (cn.State != ConnectionState.Open) {
|
|
cn.Open();
|
|
}
|
|
using (IDbCommand cmd = cn.CreateCommand()) {
|
|
cmd.CommandText = $"select sql from sqlite_master where tbl_name='{tableName}' and type='table'";
|
|
using (IDataReader reader = cmd.ExecuteReader()) {
|
|
StringBuilder sb = new StringBuilder();
|
|
reader.Read();
|
|
string sql = reader.GetString(0);
|
|
|
|
Match m = Regex.Match(sql, "CREATE TABLE \\S+ \\((.*)\\)", RegexOptions.Singleline);
|
|
if (!m.Success) {
|
|
Trace.TraceWarning("Unable to match regex on table " + tableName);
|
|
return string.Empty;
|
|
}
|
|
|
|
sb.AppendLine("-- TABLE " + tableName + " --");
|
|
int startIndex = m.Groups[1].Index;
|
|
int length = m.Groups[1].Length;
|
|
string columns = Regex.Replace(m.Groups[1].Value, "\\s{2,}", " ");
|
|
columns = Regex.Replace(columns.Replace(", ", ",").Replace(",\n", ","), ",(?!\\d+\\))", ",\r\n\t");
|
|
|
|
sb.AppendLine(sql.Substring(0, startIndex));
|
|
sb.AppendLine("\t" + columns.Trim());
|
|
sb.AppendLine(sql.Substring(startIndex + length) + ";");
|
|
|
|
string indexes = GetIndexCreateSql(cn, tableName);
|
|
if (!string.IsNullOrEmpty(indexes)) {
|
|
sb.AppendLine();
|
|
sb.AppendLine("-- INDEXES --");
|
|
sb.AppendLine(indexes);
|
|
}
|
|
|
|
string triggers = GetTriggerCreateSql(cn, tableName);
|
|
if (!string.IsNullOrEmpty(triggers)) {
|
|
sb.AppendLine();
|
|
sb.AppendLine("-- TRIGGERS --");
|
|
sb.AppendLine(triggers);
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(selectStatement)) {
|
|
string data = await GetTableDataSqlAsync(cn, tableName, selectStatement);
|
|
if (!string.IsNullOrEmpty(data)) {
|
|
sb.AppendLine();
|
|
sb.AppendLine("-- DATA --");
|
|
sb.Append(data);
|
|
}
|
|
}
|
|
sb.AppendLine("-- END TABLE " + tableName + " --");
|
|
return sb.ToString();
|
|
}
|
|
}
|
|
}
|
|
|
|
public string GetTableCreateSql(IDbConnection cn, string tableName) {
|
|
if (cn == null) {
|
|
throw new ArgumentNullException("cn");
|
|
}
|
|
if (string.IsNullOrEmpty(tableName)) {
|
|
throw new ArgumentNullException("tableName");
|
|
}
|
|
|
|
if (cn.State != ConnectionState.Open) {
|
|
cn.Open();
|
|
}
|
|
using (IDbCommand cmd = cn.CreateCommand()) {
|
|
cmd.CommandText = $"select sql from sqlite_master where tbl_name='{tableName}' and type='table'";
|
|
using (IDataReader reader = cmd.ExecuteReader()) {
|
|
StringBuilder sb = new StringBuilder();
|
|
reader.Read();
|
|
string sql = reader.GetString(0);
|
|
|
|
Match m = Regex.Match(sql, "CREATE TABLE \\S+ \\((.*)\\)", RegexOptions.Singleline);
|
|
if (!m.Success) {
|
|
Trace.TraceWarning("Unable to match regex on table " + tableName);
|
|
return string.Empty;
|
|
}
|
|
|
|
sb.AppendLine("-- TABLE " + tableName + " --");
|
|
int startIndex = m.Groups[1].Index;
|
|
int length = m.Groups[1].Length;
|
|
string columns = Regex.Replace(m.Groups[1].Value, "\\s{2,}", " ");
|
|
columns = Regex.Replace(columns.Replace(", ", ",").Replace(",\n", ","), ",(?!\\d+\\))", ",\r\n\t");
|
|
|
|
sb.AppendLine(sql.Substring(0, startIndex));
|
|
sb.AppendLine("\t" + columns.Trim());
|
|
sb.AppendLine(sql.Substring(startIndex + length) + ";");
|
|
|
|
string indexes = GetIndexCreateSql(cn, tableName);
|
|
if (!string.IsNullOrEmpty(indexes)) {
|
|
sb.AppendLine();
|
|
sb.AppendLine("-- INDEXES --");
|
|
sb.AppendLine(indexes);
|
|
}
|
|
|
|
string triggers = GetTriggerCreateSql(cn, tableName);
|
|
if (!string.IsNullOrEmpty(triggers)) {
|
|
sb.AppendLine();
|
|
sb.AppendLine("-- TRIGGERS --");
|
|
sb.AppendLine(triggers);
|
|
}
|
|
|
|
sb.AppendLine("-- END TABLE " + tableName + " --");
|
|
return sb.ToString();
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
public string GetTableDropSql(IDbConnection cn, string tableName) {
|
|
if (cn == null) {
|
|
throw new ArgumentNullException("cn");
|
|
}
|
|
if (string.IsNullOrEmpty(tableName)) {
|
|
throw new ArgumentNullException("tableName");
|
|
}
|
|
|
|
StringBuilder sb = new StringBuilder();
|
|
string[] indexes = GetIndexCreateSql(cn, tableName).Split(new string[] { "\r\n", "\n" }, StringSplitOptions.RemoveEmptyEntries);
|
|
foreach (string index in indexes) {
|
|
Match m = Regex.Match(index, "CREATE INDEX (\\S+) ON .*", RegexOptions.Singleline);
|
|
if (m.Success) {
|
|
sb.AppendLine($"DROP INDEX IF EXISTS {m.Groups[1].Value};");
|
|
}
|
|
}
|
|
|
|
string[] triggers = GetTriggerCreateSql(cn, tableName).Split(new string[] { "\r\n", "\n" }, StringSplitOptions.RemoveEmptyEntries);
|
|
foreach (string trigger in triggers) {
|
|
Match m = Regex.Match(trigger, "CREATE TRIGGER (\\S+) .*", RegexOptions.Singleline);
|
|
if (m.Success) {
|
|
sb.AppendLine($"DROP TRIGGER IF EXISTS {m.Groups[1].Value};");
|
|
}
|
|
}
|
|
|
|
sb.Append($"DROP TABLE IF EXISTS {tableName};");
|
|
return sb.ToString();
|
|
}
|
|
|
|
/// <summary>
|
|
/// Retrieves the SQL statements used to create all indexes for the specified table in a SQLite database.
|
|
/// </summary>
|
|
/// <remarks>This method queries the SQLite system table <c>sqlite_master</c> to retrieve the SQL
|
|
/// definitions of indexes associated with the specified table. The connection must be open before executing the
|
|
/// query; if it is not, the method will attempt to open it.</remarks>
|
|
/// <param name="cn">The open database connection to use for the query. The connection must be to a SQLite database.</param>
|
|
/// <param name="tableName">The name of the table whose index creation SQL statements are to be retrieved. Cannot be null or empty.</param>
|
|
/// <returns>A string containing the SQL statements for creating all indexes on the specified table, separated by
|
|
/// newlines. Returns an empty string if no indexes are found.</returns>
|
|
/// <exception cref="ArgumentNullException">Thrown if <paramref name="cn"/> is null or if <paramref name="tableName"/> is null or empty.</exception>
|
|
public string GetIndexCreateSql(IDbConnection cn, string tableName) {
|
|
if (cn == null) {
|
|
throw new ArgumentNullException("cn");
|
|
}
|
|
if (string.IsNullOrEmpty(tableName)) {
|
|
throw new ArgumentNullException("tableName");
|
|
}
|
|
if (cn.State != ConnectionState.Open) {
|
|
cn.Open();
|
|
}
|
|
StringBuilder sb = new StringBuilder();
|
|
|
|
using (IDbCommand cmd = cn.CreateCommand()) {
|
|
cmd.CommandText = $"SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name='{tableName}' AND sql NOT NULL;";
|
|
using (IDataReader reader = cmd.ExecuteReader()) {
|
|
while (reader.Read()) {
|
|
sb.AppendLine(reader.GetString(0) + ";");
|
|
}
|
|
}
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
|
|
/// <summary>
|
|
/// Retrieves the SQL definition for all triggers associated with the specified table.
|
|
/// </summary>
|
|
/// <remarks>This method queries the SQLite system table `sqlite_master` to retrieve the SQL
|
|
/// definitions of triggers. Ensure that the provided connection is valid and points to a SQLite
|
|
/// database.</remarks>
|
|
/// <param name="cn">An open database connection used to query the trigger definitions. The connection must be to a SQLite
|
|
/// database.</param>
|
|
/// <param name="tableName">The name of the table whose triggers are to be retrieved. Cannot be null or empty.</param>
|
|
/// <returns>A string containing the SQL definitions of all triggers for the specified table, separated by semicolons.
|
|
/// Returns an empty string if no triggers are found.</returns>
|
|
/// <exception cref="ArgumentNullException">Thrown if <paramref name="cn"/> is null or if <paramref name="tableName"/> is null or empty.</exception>
|
|
public string GetTriggerCreateSql(IDbConnection cn, string tableName) {
|
|
if (cn == null) {
|
|
throw new ArgumentNullException("cn");
|
|
}
|
|
if (string.IsNullOrEmpty(tableName)) {
|
|
throw new ArgumentNullException("tableName");
|
|
}
|
|
if (cn.State != ConnectionState.Open) {
|
|
cn.Open();
|
|
}
|
|
StringBuilder sb = new StringBuilder();
|
|
using (IDbCommand cmd = cn.CreateCommand()) {
|
|
cmd.CommandText = $"SELECT sql FROM sqlite_master WHERE type='trigger' AND tbl_name='{tableName}' AND sql NOT NULL;";
|
|
using (IDataReader reader = cmd.ExecuteReader()) {
|
|
while (reader.Read()) {
|
|
sb.AppendLine(reader.GetString(0) + ";");
|
|
}
|
|
}
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
|
|
/// <summary>
|
|
/// Generates an SQL script to insert data from a specified table into another database.
|
|
/// </summary>
|
|
/// <remarks>This method generates an SQL script that can be used to insert data into a table. The
|
|
/// script is constructed based on the data retrieved by the provided SELECT statement. The method ensures that
|
|
/// string and date values are properly escaped, and null values are represented as <c>NULL</c> in the
|
|
/// script.</remarks>
|
|
/// <param name="cn">An open <see cref="IDbConnection"/> used to execute the query. The connection must be open before calling
|
|
/// this method.</param>
|
|
/// <param name="tableName">The name of the table whose data will be used in the generated SQL script. Cannot be null or empty.</param>
|
|
/// <param name="selectStatement">A SQL SELECT statement that retrieves the data to be included in the script. The placeholder <c>$table</c>
|
|
/// in the statement will be replaced with the value of <paramref name="tableName"/>.</param>
|
|
/// <returns>A string containing the generated SQL script. The script includes INSERT statements for the data retrieved
|
|
/// by the <paramref name="selectStatement"/>.</returns>
|
|
/// <exception cref="ArgumentNullException">Thrown if <paramref name="cn"/> is null or <paramref name="tableName"/> is null or empty.</exception>
|
|
public async Task<string> GetTableDataSqlAsync(IDbConnection cn, string tableName, string selectStatement) {
|
|
if (cn == null) {
|
|
throw new ArgumentNullException("cn");
|
|
}
|
|
|
|
if (string.IsNullOrEmpty(tableName)) {
|
|
throw new ArgumentNullException("tableName");
|
|
}
|
|
if (cn.State != ConnectionState.Open) {
|
|
cn.Open();
|
|
}
|
|
|
|
StringBuilder sb = new StringBuilder();
|
|
selectStatement = selectStatement.Replace("$table", tableName);
|
|
await Task.Run(() => {
|
|
using (IDbCommand cmd = cn.CreateCommand()) {
|
|
cmd.CommandText = selectStatement;
|
|
using (IDataReader reader = cmd.ExecuteReader()) {
|
|
int rowCount = 0;
|
|
while (reader.Read()) {
|
|
List<string> values = new List<string>();
|
|
for (int i = 0; i < reader.FieldCount; i++) {
|
|
object value = DBNull.Value;
|
|
try {
|
|
value = reader.GetValue(i);
|
|
} catch { }
|
|
if (value == DBNull.Value) {
|
|
values.Add("NULL");
|
|
} else if (value is string || value is DateTime) {
|
|
values.Add("'" + value.ToString().Replace("'", "''") + "'");
|
|
} else if (value is bool) {
|
|
values.Add((bool)value ? "1" : "0");
|
|
} else {
|
|
values.Add(value.ToString());
|
|
}
|
|
}
|
|
|
|
if (rowCount % 100 == 0) {
|
|
if (rowCount != 0)
|
|
sb.AppendLine(";");
|
|
sb.Append($"INSERT INTO {tableName} ({string.Join(", ", GetColumnNames(reader))}) VALUES");
|
|
sb.AppendLine();
|
|
sb.Append($" ({string.Join(", ", values)})");
|
|
} else {
|
|
sb.AppendLine(",");
|
|
sb.Append($" ({string.Join(", ", values)})");
|
|
}
|
|
rowCount++;
|
|
}
|
|
if (rowCount > 0)
|
|
sb.AppendLine(";");
|
|
|
|
}
|
|
}
|
|
});
|
|
return sb.ToString();
|
|
}
|
|
|
|
/// <summary>
|
|
/// Retrieves the names of all columns from the specified <see cref="IDataReader"/>.
|
|
/// </summary>
|
|
/// <param name="reader">The <see cref="IDataReader"/> instance from which to retrieve column names. Must not be <see
|
|
/// langword="null"/>.</param>
|
|
/// <returns>An enumerable collection of strings representing the names of all columns in the data reader.</returns>
|
|
private IEnumerable<string> GetColumnNames(IDataReader reader) {
|
|
for (int i = 0; i < reader.FieldCount; i++) {
|
|
yield return reader.GetName(i);
|
|
}
|
|
}
|
|
}
|
|
}
|