537 lines
17 KiB
C#
Executable File
537 lines
17 KiB
C#
Executable File
using MarketAlly.AIPlugin;
|
|
using Microsoft.Data.Sqlite;
|
|
using Microsoft.Extensions.Logging;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.IO;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace MarketAlly.AIPlugin.Analysis.Plugins
|
|
{
|
|
/// <summary>
|
|
/// Plugin that reads and analyzes SQLite database schemas
|
|
/// </summary>
|
|
[AIPlugin("SQLiteSchemaReader", "Reads and analyzes SQLite database schemas with detailed table, index, and relationship information")]
|
|
public class SQLiteSchemaReaderPlugin : IAIPlugin
|
|
{
|
|
private readonly ILogger<SQLiteSchemaReaderPlugin>? _logger;
|
|
|
|
/// <summary>
|
|
/// Creates a new instance of SQLiteSchemaReaderPlugin
|
|
/// </summary>
|
|
/// <param name="logger">Optional logger for recording operations</param>
|
|
public SQLiteSchemaReaderPlugin(ILogger<SQLiteSchemaReaderPlugin>? logger = null)
|
|
{
|
|
_logger = logger;
|
|
}
|
|
|
|
[AIParameter("Full path to the SQLite database file", required: true)]
|
|
public string DatabasePath { get; set; } = string.Empty;
|
|
|
|
[AIParameter("Include table row counts in analysis", required: false)]
|
|
public bool IncludeRowCounts { get; set; } = true;
|
|
|
|
[AIParameter("Include detailed index information", required: false)]
|
|
public bool IncludeIndexes { get; set; } = true;
|
|
|
|
[AIParameter("Include foreign key relationships", required: false)]
|
|
public bool IncludeForeignKeys { get; set; } = true;
|
|
|
|
[AIParameter("Include database metadata and statistics", required: false)]
|
|
public bool IncludeMetadata { get; set; } = true;
|
|
|
|
[AIParameter("Output format: structured, readable, json", required: false)]
|
|
public string OutputFormat { get; set; } = "structured";
|
|
|
|
[AIParameter("Maximum number of sample rows to include per table", required: false)]
|
|
public int MaxSampleRows { get; set; } = 0;
|
|
|
|
public IReadOnlyDictionary<string, Type> SupportedParameters => new Dictionary<string, Type>
|
|
{
|
|
["databasePath"] = typeof(string),
|
|
["includeRowCounts"] = typeof(bool),
|
|
["includeIndexes"] = typeof(bool),
|
|
["includeForeignKeys"] = typeof(bool),
|
|
["includeMetadata"] = typeof(bool),
|
|
["outputFormat"] = typeof(string),
|
|
["maxSampleRows"] = typeof(int)
|
|
};
|
|
|
|
public async Task<AIPluginResult> ExecuteAsync(IReadOnlyDictionary<string, object> parameters)
|
|
{
|
|
try
|
|
{
|
|
_logger?.LogInformation("SQLiteSchemaReader plugin executing for database {DatabasePath}", parameters["databasePath"]);
|
|
|
|
// Extract parameters
|
|
string databasePath = parameters["databasePath"].ToString() ?? string.Empty;
|
|
|
|
bool includeRowCounts = parameters.TryGetValue("includeRowCounts", out var rowCountsValue)
|
|
? Convert.ToBoolean(rowCountsValue)
|
|
: true;
|
|
|
|
bool includeIndexes = parameters.TryGetValue("includeIndexes", out var indexesValue)
|
|
? Convert.ToBoolean(indexesValue)
|
|
: true;
|
|
|
|
bool includeForeignKeys = parameters.TryGetValue("includeForeignKeys", out var fkValue)
|
|
? Convert.ToBoolean(fkValue)
|
|
: true;
|
|
|
|
bool includeMetadata = parameters.TryGetValue("includeMetadata", out var metadataValue)
|
|
? Convert.ToBoolean(metadataValue)
|
|
: true;
|
|
|
|
string outputFormat = parameters.TryGetValue("outputFormat", out var formatValue)
|
|
? formatValue?.ToString()?.ToLower() ?? "structured"
|
|
: "structured";
|
|
|
|
int maxSampleRows = parameters.TryGetValue("maxSampleRows", out var sampleValue)
|
|
? Convert.ToInt32(sampleValue)
|
|
: 0;
|
|
|
|
// Validate database file exists
|
|
if (!File.Exists(databasePath))
|
|
{
|
|
return new AIPluginResult(
|
|
new FileNotFoundException($"Database file not found: {databasePath}"),
|
|
"Database file not found"
|
|
);
|
|
}
|
|
|
|
// Read the schema
|
|
var schemaData = await ReadSchemaAsync(databasePath, includeRowCounts, includeIndexes,
|
|
includeForeignKeys, includeMetadata, maxSampleRows);
|
|
|
|
// Format output based on requested format
|
|
object result = outputFormat switch
|
|
{
|
|
"json" => schemaData,
|
|
"readable" => await GenerateReadableSchemaAsync(schemaData),
|
|
_ => schemaData // structured (default)
|
|
};
|
|
|
|
_logger?.LogInformation("Successfully analyzed SQLite database schema for {DatabasePath}, found {TableCount} tables",
|
|
databasePath, schemaData.Tables?.Count ?? 0);
|
|
|
|
return new AIPluginResult(
|
|
result,
|
|
$"Successfully analyzed SQLite database schema: {Path.GetFileName(databasePath)}"
|
|
);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
_logger?.LogError(ex, "Failed to read SQLite schema from {DatabasePath}", parameters["databasePath"]);
|
|
return new AIPluginResult(ex, "Failed to read SQLite database schema");
|
|
}
|
|
}
|
|
|
|
private async Task<DatabaseSchema> ReadSchemaAsync(string databasePath, bool includeRowCounts,
|
|
bool includeIndexes, bool includeForeignKeys, bool includeMetadata, int maxSampleRows)
|
|
{
|
|
var connectionString = $"Data Source={databasePath}";
|
|
var schema = new DatabaseSchema
|
|
{
|
|
DatabasePath = databasePath,
|
|
DatabaseName = Path.GetFileNameWithoutExtension(databasePath),
|
|
Tables = new List<TableSchema>()
|
|
};
|
|
|
|
using var connection = new SqliteConnection(connectionString);
|
|
await connection.OpenAsync();
|
|
|
|
// Get all tables
|
|
var tableNames = await GetTablesAsync(connection);
|
|
schema.TableCount = tableNames.Count;
|
|
|
|
foreach (var tableName in tableNames)
|
|
{
|
|
var table = new TableSchema
|
|
{
|
|
Name = tableName,
|
|
Columns = await GetTableSchemaAsync(connection, tableName)
|
|
};
|
|
|
|
if (includeIndexes)
|
|
{
|
|
table.Indexes = await GetTableIndexesAsync(connection, tableName);
|
|
}
|
|
|
|
if (includeForeignKeys)
|
|
{
|
|
table.ForeignKeys = await GetTableForeignKeysAsync(connection, tableName);
|
|
}
|
|
|
|
if (includeRowCounts)
|
|
{
|
|
table.RowCount = await GetTableRowCountAsync(connection, tableName);
|
|
}
|
|
|
|
if (maxSampleRows > 0)
|
|
{
|
|
table.SampleData = await GetSampleDataAsync(connection, tableName, maxSampleRows);
|
|
}
|
|
|
|
schema.Tables.Add(table);
|
|
}
|
|
|
|
if (includeMetadata)
|
|
{
|
|
schema.Metadata = await GetDatabaseMetadataAsync(connection, databasePath);
|
|
}
|
|
|
|
return schema;
|
|
}
|
|
|
|
private async Task<List<string>> GetTablesAsync(SqliteConnection connection)
|
|
{
|
|
var tables = new List<string>();
|
|
|
|
using var command = connection.CreateCommand();
|
|
command.CommandText = @"
|
|
SELECT name
|
|
FROM sqlite_master
|
|
WHERE type='table'
|
|
AND name NOT LIKE 'sqlite_%'
|
|
ORDER BY name";
|
|
|
|
using var reader = await command.ExecuteReaderAsync();
|
|
while (await reader.ReadAsync())
|
|
{
|
|
tables.Add(reader.GetString(0));
|
|
}
|
|
|
|
return tables;
|
|
}
|
|
|
|
private async Task<List<ColumnSchema>> GetTableSchemaAsync(SqliteConnection connection, string tableName)
|
|
{
|
|
var columns = new List<ColumnSchema>();
|
|
|
|
using var command = connection.CreateCommand();
|
|
command.CommandText = $"PRAGMA table_info([{tableName}])";
|
|
|
|
using var reader = await command.ExecuteReaderAsync();
|
|
while (await reader.ReadAsync())
|
|
{
|
|
var column = new ColumnSchema
|
|
{
|
|
Position = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
DataType = reader.GetString(2),
|
|
NotNull = reader.GetBoolean(3),
|
|
DefaultValue = reader.IsDBNull(4) ? null : reader.GetValue(4)?.ToString(),
|
|
IsPrimaryKey = reader.GetBoolean(5)
|
|
};
|
|
|
|
columns.Add(column);
|
|
}
|
|
|
|
return columns;
|
|
}
|
|
|
|
private async Task<List<IndexSchema>> GetTableIndexesAsync(SqliteConnection connection, string tableName)
|
|
{
|
|
var indexes = new List<IndexSchema>();
|
|
|
|
using var command = connection.CreateCommand();
|
|
command.CommandText = $"PRAGMA index_list([{tableName}])";
|
|
|
|
using var reader = await command.ExecuteReaderAsync();
|
|
while (await reader.ReadAsync())
|
|
{
|
|
var index = new IndexSchema
|
|
{
|
|
Sequence = reader.GetInt32(0),
|
|
Name = reader.GetString(1),
|
|
IsUnique = reader.GetBoolean(2),
|
|
Origin = reader.GetString(3),
|
|
IsPartial = reader.GetBoolean(4),
|
|
Columns = await GetIndexColumnsAsync(connection, reader.GetString(1))
|
|
};
|
|
|
|
indexes.Add(index);
|
|
}
|
|
|
|
return indexes;
|
|
}
|
|
|
|
private async Task<List<string>> GetIndexColumnsAsync(SqliteConnection connection, string indexName)
|
|
{
|
|
var columns = new List<string>();
|
|
|
|
using var command = connection.CreateCommand();
|
|
command.CommandText = $"PRAGMA index_info([{indexName}])";
|
|
|
|
using var reader = await command.ExecuteReaderAsync();
|
|
while (await reader.ReadAsync())
|
|
{
|
|
columns.Add(reader.GetString(2));
|
|
}
|
|
|
|
return columns;
|
|
}
|
|
|
|
private async Task<List<ForeignKeySchema>> GetTableForeignKeysAsync(SqliteConnection connection, string tableName)
|
|
{
|
|
var foreignKeys = new List<ForeignKeySchema>();
|
|
|
|
using var command = connection.CreateCommand();
|
|
command.CommandText = $"PRAGMA foreign_key_list([{tableName}])";
|
|
|
|
using var reader = await command.ExecuteReaderAsync();
|
|
while (await reader.ReadAsync())
|
|
{
|
|
var fk = new ForeignKeySchema
|
|
{
|
|
Id = reader.GetInt32(0),
|
|
Sequence = reader.GetInt32(1),
|
|
ReferencedTable = reader.GetString(2),
|
|
FromColumn = reader.GetString(3),
|
|
ToColumn = reader.GetString(4),
|
|
OnUpdate = reader.GetString(5),
|
|
OnDelete = reader.GetString(6),
|
|
Match = reader.GetString(7)
|
|
};
|
|
|
|
foreignKeys.Add(fk);
|
|
}
|
|
|
|
return foreignKeys;
|
|
}
|
|
|
|
private async Task<long> GetTableRowCountAsync(SqliteConnection connection, string tableName)
|
|
{
|
|
using var command = connection.CreateCommand();
|
|
command.CommandText = $"SELECT COUNT(*) FROM [{tableName}]";
|
|
|
|
var result = await command.ExecuteScalarAsync();
|
|
return Convert.ToInt64(result);
|
|
}
|
|
|
|
private async Task<List<Dictionary<string, object>>> GetSampleDataAsync(SqliteConnection connection, string tableName, int maxRows)
|
|
{
|
|
var sampleData = new List<Dictionary<string, object>>();
|
|
|
|
using var command = connection.CreateCommand();
|
|
command.CommandText = $"SELECT * FROM [{tableName}] LIMIT {maxRows}";
|
|
|
|
using var reader = await command.ExecuteReaderAsync();
|
|
while (await reader.ReadAsync())
|
|
{
|
|
var row = new Dictionary<string, object>();
|
|
for (int i = 0; i < reader.FieldCount; i++)
|
|
{
|
|
row[reader.GetName(i)] = reader.IsDBNull(i) ? DBNull.Value : reader.GetValue(i);
|
|
}
|
|
sampleData.Add(row);
|
|
}
|
|
|
|
return sampleData;
|
|
}
|
|
|
|
private async Task<DatabaseMetadata> GetDatabaseMetadataAsync(SqliteConnection connection, string databasePath)
|
|
{
|
|
var metadata = new DatabaseMetadata();
|
|
|
|
// Get SQLite version
|
|
using var versionCommand = connection.CreateCommand();
|
|
versionCommand.CommandText = "SELECT sqlite_version()";
|
|
metadata.SqliteVersion = await versionCommand.ExecuteScalarAsync() as string ?? "Unknown";
|
|
|
|
// Get database size and page info
|
|
using var sizeCommand = connection.CreateCommand();
|
|
sizeCommand.CommandText = "PRAGMA page_count; PRAGMA page_size;";
|
|
|
|
using var reader = await sizeCommand.ExecuteReaderAsync();
|
|
if (await reader.ReadAsync())
|
|
{
|
|
metadata.PageCount = reader.GetInt64(0);
|
|
}
|
|
if (await reader.NextResultAsync() && await reader.ReadAsync())
|
|
{
|
|
metadata.PageSize = reader.GetInt64(0);
|
|
}
|
|
|
|
metadata.DatabaseSize = metadata.PageCount * metadata.PageSize;
|
|
metadata.FormattedSize = FormatBytes(metadata.DatabaseSize);
|
|
|
|
// Get file info
|
|
var fileInfo = new FileInfo(databasePath);
|
|
metadata.FileSize = fileInfo.Length;
|
|
metadata.CreatedDate = fileInfo.CreationTime;
|
|
metadata.ModifiedDate = fileInfo.LastWriteTime;
|
|
|
|
// Get encoding
|
|
using var encodingCommand = connection.CreateCommand();
|
|
encodingCommand.CommandText = "PRAGMA encoding";
|
|
metadata.Encoding = await encodingCommand.ExecuteScalarAsync() as string ?? "Unknown";
|
|
|
|
// Get journal mode
|
|
using var journalCommand = connection.CreateCommand();
|
|
journalCommand.CommandText = "PRAGMA journal_mode";
|
|
metadata.JournalMode = await journalCommand.ExecuteScalarAsync() as string ?? "Unknown";
|
|
|
|
return metadata;
|
|
}
|
|
|
|
private Task<string> GenerateReadableSchemaAsync(DatabaseSchema schema)
|
|
{
|
|
var output = new StringBuilder();
|
|
|
|
output.AppendLine("=== SQLite Database Schema ===");
|
|
output.AppendLine($"Database: {schema.DatabaseName}");
|
|
output.AppendLine($"Path: {schema.DatabasePath}");
|
|
output.AppendLine($"Tables: {schema.TableCount}");
|
|
output.AppendLine();
|
|
|
|
foreach (var table in schema.Tables)
|
|
{
|
|
output.AppendLine($"TABLE: {table.Name}");
|
|
output.AppendLine(new string('-', 50));
|
|
|
|
foreach (var column in table.Columns)
|
|
{
|
|
var columnInfo = new StringBuilder();
|
|
columnInfo.Append($" {column.Name,-25} {column.DataType,-15}");
|
|
|
|
if (column.IsPrimaryKey) columnInfo.Append(" PRIMARY KEY");
|
|
if (column.NotNull && !column.IsPrimaryKey) columnInfo.Append(" NOT NULL");
|
|
if (!string.IsNullOrEmpty(column.DefaultValue)) columnInfo.Append($" DEFAULT {column.DefaultValue}");
|
|
|
|
output.AppendLine(columnInfo.ToString());
|
|
}
|
|
|
|
if (table.Indexes?.Count > 0)
|
|
{
|
|
output.AppendLine();
|
|
output.AppendLine(" INDEXES:");
|
|
foreach (var index in table.Indexes)
|
|
{
|
|
var indexInfo = new StringBuilder();
|
|
indexInfo.Append($" {index.Name}");
|
|
if (index.IsUnique) indexInfo.Append(" (UNIQUE)");
|
|
indexInfo.Append($" ON ({string.Join(", ", index.Columns)})");
|
|
output.AppendLine(indexInfo.ToString());
|
|
}
|
|
}
|
|
|
|
if (table.ForeignKeys?.Count > 0)
|
|
{
|
|
output.AppendLine();
|
|
output.AppendLine(" FOREIGN KEYS:");
|
|
foreach (var fk in table.ForeignKeys)
|
|
{
|
|
var fkInfo = $" {fk.FromColumn} -> {fk.ReferencedTable}.{fk.ToColumn}";
|
|
if (fk.OnUpdate != "NO ACTION") fkInfo += $" ON UPDATE {fk.OnUpdate}";
|
|
if (fk.OnDelete != "NO ACTION") fkInfo += $" ON DELETE {fk.OnDelete}";
|
|
output.AppendLine(fkInfo);
|
|
}
|
|
}
|
|
|
|
if (table.RowCount.HasValue)
|
|
{
|
|
output.AppendLine();
|
|
output.AppendLine($" ROW COUNT: {table.RowCount.Value:N0}");
|
|
}
|
|
|
|
output.AppendLine();
|
|
}
|
|
|
|
if (schema.Metadata != null)
|
|
{
|
|
output.AppendLine("=== Database Information ===");
|
|
output.AppendLine($"SQLite Version: {schema.Metadata.SqliteVersion}");
|
|
output.AppendLine($"Database Size: {schema.Metadata.FormattedSize}");
|
|
output.AppendLine($"Encoding: {schema.Metadata.Encoding}");
|
|
output.AppendLine($"Journal Mode: {schema.Metadata.JournalMode}");
|
|
output.AppendLine($"Created: {schema.Metadata.CreatedDate}");
|
|
output.AppendLine($"Modified: {schema.Metadata.ModifiedDate}");
|
|
}
|
|
|
|
return Task.FromResult(output.ToString());
|
|
}
|
|
|
|
private static string FormatBytes(long bytes)
|
|
{
|
|
string[] sizes = { "B", "KB", "MB", "GB", "TB" };
|
|
double len = bytes;
|
|
int order = 0;
|
|
|
|
while (len >= 1024 && order < sizes.Length - 1)
|
|
{
|
|
order++;
|
|
len = len / 1024;
|
|
}
|
|
|
|
return $"{len:0.##} {sizes[order]}";
|
|
}
|
|
}
|
|
|
|
// Supporting data structures
|
|
public class DatabaseSchema
|
|
{
|
|
public string DatabasePath { get; set; } = string.Empty;
|
|
public string DatabaseName { get; set; } = string.Empty;
|
|
public int TableCount { get; set; }
|
|
public List<TableSchema> Tables { get; set; } = new();
|
|
public DatabaseMetadata Metadata { get; set; } = new();
|
|
}
|
|
|
|
public class TableSchema
|
|
{
|
|
public string Name { get; set; } = string.Empty;
|
|
public List<ColumnSchema> Columns { get; set; } = new();
|
|
public List<IndexSchema> Indexes { get; set; } = new();
|
|
public List<ForeignKeySchema> ForeignKeys { get; set; } = new();
|
|
public long? RowCount { get; set; }
|
|
public List<Dictionary<string, object>> SampleData { get; set; } = new();
|
|
}
|
|
|
|
public class ColumnSchema
|
|
{
|
|
public int Position { get; set; }
|
|
public string Name { get; set; } = string.Empty;
|
|
public string DataType { get; set; } = string.Empty;
|
|
public bool NotNull { get; set; }
|
|
public string? DefaultValue { get; set; }
|
|
public bool IsPrimaryKey { get; set; }
|
|
}
|
|
|
|
public class IndexSchema
|
|
{
|
|
public int Sequence { get; set; }
|
|
public string Name { get; set; } = string.Empty;
|
|
public bool IsUnique { get; set; }
|
|
public string Origin { get; set; } = string.Empty;
|
|
public bool IsPartial { get; set; }
|
|
public List<string> Columns { get; set; } = new();
|
|
}
|
|
|
|
public class ForeignKeySchema
|
|
{
|
|
public int Id { get; set; }
|
|
public int Sequence { get; set; }
|
|
public string ReferencedTable { get; set; } = string.Empty;
|
|
public string FromColumn { get; set; } = string.Empty;
|
|
public string ToColumn { get; set; } = string.Empty;
|
|
public string OnUpdate { get; set; } = string.Empty;
|
|
public string OnDelete { get; set; } = string.Empty;
|
|
public string Match { get; set; } = string.Empty;
|
|
}
|
|
|
|
public class DatabaseMetadata
|
|
{
|
|
public string SqliteVersion { get; set; } = string.Empty;
|
|
public long PageCount { get; set; }
|
|
public long PageSize { get; set; }
|
|
public long DatabaseSize { get; set; }
|
|
public string FormattedSize { get; set; } = string.Empty;
|
|
public long FileSize { get; set; }
|
|
public DateTime CreatedDate { get; set; }
|
|
public DateTime ModifiedDate { get; set; }
|
|
public string Encoding { get; set; } = string.Empty;
|
|
public string JournalMode { get; set; } = string.Empty;
|
|
}
|
|
} |