forked from Unity-Technologies/UnityDataTools
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathSQLTestHelper.cs
More file actions
135 lines (124 loc) · 5.4 KB
/
Copy pathSQLTestHelper.cs
File metadata and controls
135 lines (124 loc) · 5.4 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
129
130
131
132
133
134
135
using System.IO;
using Microsoft.Data.Sqlite;
using NUnit.Framework;
namespace UnityDataTools.UnityDataTool.Tests;
#pragma warning disable NUnit2005, NUnit2006
/// <summary>
/// Helper methods for executing SQL queries against a DB created by "Analyze"
/// and validating results in tests.
/// </summary>
public static class SQLTestHelper
{
/// <summary>
/// Default database filename used in tests.
/// </summary>
public const string DefaultDatabaseName = "database.db";
/// <summary>
/// Creates and opens a SQLite database connection with standard test settings.
/// </summary>
/// <param name="databasePath">The path to the database file.</param>
/// <returns>An opened SqliteConnection. Caller is responsible for disposing.</returns>
public static SqliteConnection OpenDatabase(string databasePath)
{
var db = new SqliteConnection(new SqliteConnectionStringBuilder
{
DataSource = databasePath,
Mode = SqliteOpenMode.ReadWriteCreate,
Pooling = false,
ForeignKeys = false,
}.ConnectionString);
db.Open();
return db;
}
/// <summary>
/// Gets the standard database path for tests (testOutputFolder/database.db).
/// </summary>
/// <param name="testOutputFolder">The test output folder path.</param>
/// <returns>The full path to the database file.</returns>
public static string GetDatabasePath(string testOutputFolder)
{
return Path.Combine(testOutputFolder, DefaultDatabaseName);
}
/// <summary>
/// Executes a SQL query and returns the integer result.
/// </summary>
/// <param name="db">The database connection to use.</param>
/// <param name="sql">The SQL query to execute (should return a single integer value).</param>
/// <returns>The integer result of the query.</returns>
public static int QueryInt(SqliteConnection db, string sql)
{
using var cmd = db.CreateCommand();
cmd.CommandText = sql;
using var reader = cmd.ExecuteReader();
reader.Read();
return reader.GetInt32(0);
}
/// <summary>
/// Executes a SQL query and returns the string result.
/// </summary>
/// <param name="db">The database connection to use.</param>
/// <param name="sql">The SQL query to execute (should return a single string value).</param>
/// <returns>The string result of the query.</returns>
public static string QueryString(SqliteConnection db, string sql)
{
using var cmd = db.CreateCommand();
cmd.CommandText = sql;
using var reader = cmd.ExecuteReader();
reader.Read();
return reader.GetString(0);
}
/// <summary>
/// Executes a SQL query and asserts the result equals the expected integer value.
/// </summary>
/// <param name="db">The database connection to use.</param>
/// <param name="sql">The SQL query to execute (should return a single integer value).</param>
/// <param name="expectedValue">The expected integer result.</param>
/// <param name="description">Description of what is being tested (used in assertion message).</param>
public static void AssertQueryInt(SqliteConnection db, string sql, int expectedValue, string description)
{
using var cmd = db.CreateCommand();
cmd.CommandText = sql;
using var reader = cmd.ExecuteReader();
reader.Read();
Assert.AreEqual(expectedValue, reader.GetInt32(0), description);
}
/// <summary>
/// Executes a SQL query and asserts the result equals the expected string value.
/// </summary>
/// <param name="db">The database connection to use.</param>
/// <param name="sql">The SQL query to execute (should return a single string value).</param>
/// <param name="expectedValue">The expected string result.</param>
/// <param name="description">Description of what is being tested (used in assertion message).</param>
public static void AssertQueryString(SqliteConnection db, string sql, string expectedValue, string description)
{
using var cmd = db.CreateCommand();
cmd.CommandText = sql;
using var reader = cmd.ExecuteReader();
reader.Read();
Assert.AreEqual(expectedValue, reader.GetString(0), description);
}
/// <summary>
/// Asserts that a table exists in the database.
/// </summary>
/// <param name="db">The database connection to use.</param>
/// <param name="tableName">The name of the table to check for.</param>
public static void AssertTableExists(SqliteConnection db, string tableName)
{
using var cmd = db.CreateCommand();
cmd.CommandText = $"SELECT name FROM sqlite_master WHERE type='table' AND name='{tableName}'";
using var reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read(), $"{tableName} table should exist");
}
/// <summary>
/// Asserts that a view exists in the database.
/// </summary>
/// <param name="db">The database connection to use.</param>
/// <param name="viewName">The name of the view to check for.</param>
public static void AssertViewExists(SqliteConnection db, string viewName)
{
using var cmd = db.CreateCommand();
cmd.CommandText = $"SELECT name FROM sqlite_master WHERE type='view' AND name='{viewName}'";
using var reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read(), $"{viewName} view should exist");
}
}