using System;
using System.ComponentModel;
using System.Data;
using System.IO;
using NewLife.XCoder.Tools.Sql;
namespace XCoder.App.Tests;
/// <summary>SQL 工作台测试(SQLite 临时库)</summary>
public class SqlWorkbenchServiceTests
{
static SqlWorkbenchServiceTests() => System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
[Fact]
[DisplayName("建表、插入、查询可串联执行")]
public void Execute_CreateInsertSelect()
{
var path = Path.Combine(Path.GetTempPath(), "xcoder-sql-" + Guid.NewGuid().ToString("N") + ".db");
var conn = "Data Source=" + path;
try
{
var create = SqlWorkbenchService.Execute(conn, "Create Table Members(ID Integer Primary Key AutoIncrement, Name Text)");
Assert.False(create.IsQuery);
var insert = SqlWorkbenchService.Execute(conn, "Insert Into Members(Name) Values('张三')");
Assert.Equal(1, insert.RowsAffected);
var select = SqlWorkbenchService.Execute(conn, "Select * From Members");
Assert.True(select.IsQuery);
Assert.Equal(1, select.Table!.Rows.Count);
Assert.Equal("张三", select.Table.Rows[0]["Name"]?.ToString());
}
finally
{
if (File.Exists(path)) File.Delete(path);
}
}
[Fact]
[DisplayName("纯文件路径自动补全为连接串")]
public void Normalize_Path()
{
Assert.Equal("Data Source=a.db", SqlWorkbenchService.Normalize("a.db"));
Assert.Equal("Data Source=a.db", SqlWorkbenchService.Normalize("Data Source=a.db"));
}
[Theory]
[DisplayName("查询语句识别")]
[InlineData("select 1", true)]
[InlineData(" WITH t AS (Select 1) Select * From t", true)]
[InlineData("pragma table_info(x)", true)]
[InlineData("insert into t values(1)", false)]
[InlineData("update t set a=1", false)]
[InlineData("-- 统计\nselect count(*) from t", true)]
[InlineData("/* 说明 */ delete from t", false)]
[InlineData("-- 只有注释", false)]
[InlineData("/* 未闭合", false)]
public void IsQuery_Works(String sql, Boolean expected)
=> Assert.Equal(expected, SqlWorkbenchService.IsQuery(sql));
[Theory]
[DisplayName("去注释保留真正的语句")]
[InlineData(" -- a\n-- b\nselect 1", "select 1")]
[InlineData("/* a */ /* b */ insert into t values(1)", "insert into t values(1)")]
[InlineData("", "")]
public void StripLeadingComments_Works(String sql, String expected)
=> Assert.Equal(expected, SqlWorkbenchService.StripLeadingComments(sql));
[Fact]
[DisplayName("空连接串抛 ArgumentException")]
public void Empty_Connection_Throws()
=> Assert.Throws<ArgumentException>(() => SqlWorkbenchService.Execute(" ", "select 1"));
[Fact]
[DisplayName("空 SQL 抛 ArgumentException")]
public void Empty_Sql_Throws()
=> Assert.Throws<ArgumentException>(() => SqlWorkbenchService.Execute("Data Source=:memory:", " "));
[Fact]
[DisplayName("建表后可列出架构对象(表与视图)")]
public void ListObjects_SQLite()
{
var path = Path.Combine(Path.GetTempPath(), "xcoder-sql-" + Guid.NewGuid().ToString("N") + ".db");
var conn = "Data Source=" + path;
try
{
SqlWorkbenchService.Execute(conn, "Create Table Members(ID Integer Primary Key, Name Text)");
SqlWorkbenchService.Execute(conn, "Create View VMembers As Select * From Members");
var list = SqlWorkbenchService.ListObjects(conn);
Assert.Contains("Members", list);
Assert.Contains("VMembers", list);
Assert.DoesNotContain(list, e => e.StartsWith("sqlite_"));
}
finally
{
if (File.Exists(path)) File.Delete(path);
}
}
[Fact]
[DisplayName("架构查询空连接串抛异常")]
public void ListObjects_Empty_Throws()
=> Assert.Throws<ArgumentException>(() => SqlWorkbenchService.ListObjects(" "));
[Fact]
[DisplayName("结果集导出 CSV 带表头并转义特殊字符")]
public void ToCsv_Works()
{
using var table = new DataTable();
table.Columns.Add("ID", typeof(Int32));
table.Columns.Add("Name", typeof(String));
table.Rows.Add(1, "张三");
table.Rows.Add(2, "李,四");
table.Rows.Add(3, DBNull.Value);
var csv = SqlWorkbenchService.ToCsv(table);
var lines = csv.Split(Environment.NewLine, StringSplitOptions.RemoveEmptyEntries);
Assert.Equal("ID,Name", lines[0]);
Assert.Equal("1,张三", lines[1]);
Assert.Equal("2,\"李,四\"", lines[2]);
Assert.Equal("3,", lines[3]);
}
[Fact]
[DisplayName("空结果集导出为空文本")]
public void ToCsv_Empty()
{
Assert.Equal("", SqlWorkbenchService.ToCsv(null));
using var table = new DataTable();
Assert.Equal("", SqlWorkbenchService.ToCsv(table));
}
}
|