-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathSqlScriptSplitterTests.cs
More file actions
128 lines (105 loc) · 4.65 KB
/
SqlScriptSplitterTests.cs
File metadata and controls
128 lines (105 loc) · 4.65 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
using CSharpDB.Sql;
namespace CSharpDB.Tests;
public sealed class SqlScriptSplitterTests
{
[Fact]
public void TrySplitCompleteStatements_KeepsTriggerBodyAsSingleStatement()
{
string sql = """
CREATE TABLE t (id INTEGER PRIMARY KEY, n INTEGER);
CREATE TRIGGER tr AFTER INSERT ON t BEGIN
INSERT INTO t VALUES (NEW.id + 100, NEW.n);
END;
INSERT INTO t VALUES (1, 10);
""";
bool ok = SqlScriptSplitter.TrySplitCompleteStatements(sql, out var statements, out var remainder, out var error);
Assert.True(ok);
Assert.Null(error);
Assert.Equal(string.Empty, remainder);
Assert.Equal(3, statements.Count);
Assert.Contains("CREATE TRIGGER tr AFTER INSERT ON t BEGIN", statements[1], StringComparison.OrdinalIgnoreCase);
Assert.Contains("END;", statements[1], StringComparison.OrdinalIgnoreCase);
}
[Fact]
public void TrySplitCompleteStatements_IgnoresEmptyStatements()
{
string sql = ";;CREATE TABLE t (id INTEGER PRIMARY KEY);;;;INSERT INTO t VALUES (1);;";
bool ok = SqlScriptSplitter.TrySplitCompleteStatements(sql, out var statements, out var remainder, out var error);
Assert.True(ok);
Assert.Null(error);
Assert.Equal(string.Empty, remainder);
Assert.Equal(2, statements.Count);
}
[Fact]
public void TrySplitCompleteStatements_ReturnsIncompleteTrailingRemainder()
{
string sql = """
CREATE TABLE t (id INTEGER PRIMARY KEY);
INSERT INTO t VALUES (1
""";
bool ok = SqlScriptSplitter.TrySplitCompleteStatements(sql, out var statements, out var remainder, out var error);
Assert.True(ok);
Assert.Null(error);
Assert.Single(statements);
Assert.Contains("INSERT INTO t VALUES (1", remainder, StringComparison.Ordinal);
}
[Fact]
public void TrySplitCompleteStatements_ReturnsParserErrorForInvalidCompleteStatement()
{
string sql = "SELECT FROM t;";
bool ok = SqlScriptSplitter.TrySplitCompleteStatements(sql, out var statements, out var remainder, out var error);
Assert.False(ok);
Assert.Empty(statements);
Assert.Equal(sql, remainder);
Assert.NotNull(error);
Assert.Contains("Syntax error", error, StringComparison.Ordinal);
}
[Fact]
public void SplitExecutableStatements_AllowsFinalStatementWithoutSemicolon()
{
string sql = "CREATE TABLE t (id INTEGER PRIMARY KEY); INSERT INTO t VALUES (1)";
var statements = SqlScriptSplitter.SplitExecutableStatements(sql);
Assert.Equal(2, statements.Count);
Assert.Equal("INSERT INTO t VALUES (1)", statements[1]);
}
[Fact]
public void SplitExecutableStatements_IgnoresTrailingCommentOnlyRemainder()
{
string sql = """
SELECT * FROM t;
-- Admin-only examples can live here without becoming executable statements
-- EXEC ExampleProc arg=1;
""";
var statements = SqlScriptSplitter.SplitExecutableStatements(sql);
Assert.Single(statements);
Assert.Equal("SELECT * FROM t;", statements[0]);
}
[Theory]
[InlineData("SELECT * FROM t;", true)]
[InlineData("EXPLAIN ESTIMATE FOR SELECT * FROM t;", true)]
[InlineData("INSERT INTO t VALUES (1);", false)]
[InlineData("UPDATE t SET n = 1;", false)]
[InlineData("DELETE FROM t WHERE id = 1;", false)]
[InlineData("CREATE TABLE t (id INTEGER PRIMARY KEY);", false)]
[InlineData("CREATE TEMP TABLE t (id INTEGER PRIMARY KEY);", false)]
[InlineData("DROP TEMP TABLE t;", false)]
[InlineData("PERSIST TEMP TABLE t AS durable_t;", false)]
[InlineData("CREATE TRIGGER trg AFTER INSERT ON t BEGIN INSERT INTO log VALUES (1); END;", false)]
public void Classify_ReturnsExpectedReadOnlyState(string sql, bool expectedReadOnly)
{
var classification = SqlStatementClassifier.Classify(sql);
Assert.Equal(expectedReadOnly, classification.IsReadOnly);
Assert.Equal(!expectedReadOnly, classification.IsMutating);
Assert.Equal(expectedReadOnly, classification.IsQuery);
}
[Theory]
[InlineData("CREATE TEMP TABLE t (id INTEGER PRIMARY KEY);")]
[InlineData("CREATE TEMPORARY TABLE t (id INTEGER PRIMARY KEY);")]
[InlineData("DROP TEMP TABLE t;")]
[InlineData("PERSIST TEMP TABLE t AS durable_t;")]
public void Classify_DetectsTemporaryTableStatements(string sql)
{
var classification = SqlStatementClassifier.Classify(sql);
Assert.True(SqlStatementClassifier.IsTemporaryTableStatement(classification.Statement));
}
}