借助网络配置,控制Modbus工具的日志显示,避免卡死界面
智能大石头 authored at 2021-12-11 22:09:19
4.97 KiB
XCoder
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));
    }
}