forked from SimonCropp/Delta
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathDeltaExtensions_Sql.cs
More file actions
155 lines (137 loc) * 4.58 KB
/
DeltaExtensions_Sql.cs
File metadata and controls
155 lines (137 loc) * 4.58 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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
// ReSharper disable UseRawString
namespace Delta;
public static class TrackingExtensions
{
public static async Task SetTrackedTables(
this SqlConnection connection,
IEnumerable<string> tablesToTrack,
uint retentionDays = 1,
Cancel cancel = default)
{
await connection.EnableTracking(retentionDays, cancel);
var trackedTables = await connection.GetTrackedTables(cancel);
tablesToTrack = tablesToTrack.ToList();
var builder = new StringBuilder();
var except = tablesToTrack.Except(trackedTables, StringComparer.OrdinalIgnoreCase);
foreach (var table in except)
{
builder.AppendLine($@"
-- begin-snippet: EnableTrackingTableSql
alter table [{table}] enable change_tracking
-- end-snippet");
}
var tablesToDisable = trackedTables.Except(tablesToTrack);
foreach (var table in tablesToDisable)
{
builder.AppendLine($@"
-- begin-snippet: DisableTrackingTableSql
alter table [{table}] disable change_tracking;
-- end-snippet");
}
if (builder.Length == 0)
{
return;
}
await using var command = connection.CreateCommand();
command.CommandText = builder.ToString();
await command.ExecuteNonQueryAsync(cancel);
}
public static async Task EnableTracking(
this SqlConnection connection,
uint retentionDays = 1,
Cancel cancel = default)
{
if (await IsTrackingEnabled(connection, cancel))
{
return;
}
await using var command = connection.CreateCommand();
var database = connection.Database;
command.CommandText = $@"
-- begin-snippet: EnableTrackingSql
alter database {database}
set change_tracking = on
(
change_retention = {retentionDays} days,
auto_cleanup = on
)
-- end-snippet";
await command.ExecuteNonQueryAsync(cancel);
}
public static async Task<IReadOnlyList<string>> GetTrackedTables(this SqlConnection connection, Cancel cancel = default)
{
await using var command = connection.CreateCommand();
command.CommandText = @"
-- begin-snippet: GetTrackedTablesSql
select t.Name
from sys.tables as t left join
sys.change_tracking_tables as c on t.[object_id] = c.[object_id]
where c.[object_id] is not null
-- end-snippet";
await using var reader = await command.ExecuteReaderAsync(cancel);
var list = new List<string>();
while (await reader.ReadAsync(cancel))
{
list.Add((string) reader[0]);
}
return list;
}
public static async Task<bool> IsTrackingEnabled(this SqlConnection connection, Cancel cancel = default)
{
await using var command = connection.CreateCommand();
var database = connection.Database;
command.CommandText = $@"
-- begin-snippet: IsTrackingEnabledSql
select count(d.name)
from sys.databases as d inner join
sys.change_tracking_databases as t on
t.database_id = d.database_id
where d.name = '{database}'
-- end-snippet";
return await command.ExecuteScalarAsync(cancel) is 1;
}
public static async Task DisableTracking(this SqlConnection connection, Cancel cancel = default)
{
if (!await IsTrackingEnabled(connection, cancel))
{
return;
}
var builder = new StringBuilder();
foreach (var table in await connection.GetTrackedTables(cancel))
{
builder.AppendLine($@"
-- begin-snippet: DisableTrackingSqlTable
alter table [{table}] disable change_tracking;
-- end-snippet
");
}
var database = connection.Database;
builder.AppendLine($@"
-- begin-snippet: DisableTrackingSqlDB
alter database [{database}] set change_tracking = off;
-- end-snippet");
await using var command = connection.CreateCommand();
command.CommandText = builder.ToString();
await command.ExecuteNonQueryAsync(cancel);
}
public static async Task<IReadOnlyList<string>> GetTrackedDatabases(
this SqlConnection connection,
Cancel cancel = default)
{
await using var command = connection.CreateCommand();
command.CommandText = @"
-- begin-snippet: GetTrackedDatabasesSql
select d.name
from sys.databases as d inner join
sys.change_tracking_databases as t on
t.database_id = d.database_id
-- end-snippet";
await using var reader = await command.ExecuteReaderAsync(cancel);
var list = new List<string>();
while (await reader.ReadAsync(cancel))
{
list.Add((string) reader[0]);
}
return list;
}
}