forked from dataplat/dbatools
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathCopy-DbaDbViewData.Tests.ps1
148 lines (134 loc) · 8.71 KB
/
Copy-DbaDbViewData.Tests.ps1
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
$CommandName = $MyInvocation.MyCommand.Name.Replace(".Tests.ps1", "")
Write-Host -Object "Running $PSCommandPath" -ForegroundColor Cyan
. "$PSScriptRoot\constants.ps1"
Describe "$CommandName Unit Tests" -Tag 'UnitTests' {
Context "Validate parameters" {
It "Should only contain our specific parameters" {
[object[]]$params = (Get-Command $CommandName).Parameters.Keys | Where-Object {$_ -notin ('whatif', 'confirm') }
[object[]]$knownParameters = 'AutoCreateTable', 'BatchSize', 'bulkCopyTimeOut', 'CheckConstraints', 'Database', 'Destination', 'DestinationDatabase', 'DestinationSqlCredential', 'DestinationTable', 'EnableException', 'FireTriggers', 'InputObject', 'KeepIdentity', 'KeepNulls', 'NoTableLock', 'NotifyAfter', 'Query', 'SqlCredential', 'SqlInstance', 'Truncate', 'View'
$knownParameters += [System.Management.Automation.PSCmdlet]::CommonParameters
(@(Compare-Object -ReferenceObject ($knownParameters | Where-Object {$_}) -DifferenceObject $params).Count ) | Should -Be 0
}
}
}
Describe "$commandname Integration Tests" -Tags "IntegrationTests" {
BeforeAll {
function Remove-TempObjects {
param ($dbs)
function Remove-TempObject {
param ($db, $object)
$db.Query("DECLARE @obj int = OBJECT_ID('$object'); IF @obj IS NOT NULL
BEGIN
IF (SELECT type_desc FROM sys.objects WHERE object_id = @obj) = 'VIEW' DROP VIEW $object
ELSE DROP TABLE $object
END")
}
foreach ($d in $dbs) {
Remove-TempObject $d dbo.dbatoolsci_example
Remove-TempObject $d dbo.dbatoolsci_example2
Remove-TempObject $d dbo.dbatoolsci_example3
Remove-TempObject $d dbo.dbatoolsci_example4
Remove-TempObject $d dbo.dbatoolsci_view_example
Remove-TempObject $d dbo.dbatoolsci_view_example2
Remove-TempObject $d dbo.dbatoolsci_view_example3
Remove-TempObject $d dbo.dbatoolsci_view_example4
Remove-TempObject $d dbo.dbatoolsci_view_will_exist
Remove-TempObject $d dbo.dbatoolsci_view_example_table
Remove-TempObject $d dbo.dbatoolsci_view_example2_table
Remove-TempObject $d dbo.dbatoolsci_view_example3_table
Remove-TempObject $d dbo.dbatoolsci_view_example4_table
}
}
$db = Get-DbaDatabase -SqlInstance $script:instance1 -Database tempdb
$db2 = Get-DbaDatabase -SqlInstance $script:instance2 -Database tempdb
Remove-TempObjects $db, $db2
$null = $db.Query("CREATE TABLE dbo.dbatoolsci_example (id int);
INSERT dbo.dbatoolsci_example
SELECT top 10 1
FROM sys.objects")
$null = $db.Query("CREATE TABLE dbo.dbatoolsci_example2 (id int)")
$null = $db.Query("CREATE TABLE dbo.dbatoolsci_example3 (id int)")
$null = $db.Query("CREATE TABLE dbo.dbatoolsci_example4 (id int);
INSERT dbo.dbatoolsci_example4
SELECT top 13 1
FROM sys.objects")
$null = $db.Query("CREATE VIEW dbo.dbatoolsci_view_example AS SELECT * FROM dbo.dbatoolsci_example")
$null = $db.Query("CREATE VIEW dbo.dbatoolsci_view_example2 AS SELECT * FROM dbo.dbatoolsci_example2")
$null = $db.Query("CREATE VIEW dbo.dbatoolsci_view_example3 AS SELECT * FROM dbo.dbatoolsci_example3")
$null = $db.Query("CREATE VIEW dbo.dbatoolsci_view_example4 AS SELECT * FROM dbo.dbatoolsci_example4")
$null = $db2.Query("CREATE TABLE dbo.dbatoolsci_view_example (id int)")
$null = $db2.Query("CREATE TABLE dbo.dbatoolsci_view_example3 (id int)")
$null = $db2.Query("CREATE TABLE dbo.dbatoolsci_view_example4 (id int);
INSERT dbo.dbatoolsci_view_example4
SELECT top 13 2
FROM sys.objects")
}
AfterAll {
Remove-TempObjects $db, $db2
}
It "copies the view data" {
$null = Copy-DbaDbViewData -SqlInstance $script:instance1 -Database tempdb -View dbatoolsci_view_example -DestinationTable dbatoolsci_example2
$table1count = $db.Query("select id from dbo.dbatoolsci_view_example")
$table2count = $db.Query("select id from dbo.dbatoolsci_example2")
$table1count.Count | Should -Be $table2count.Count
}
It "copies the view data to another instance" {
$null = Copy-DbaDbViewData -SqlInstance $script:instance1 -Destination $script:instance2 -Database tempdb -View dbatoolsci_view_example -DestinationTable dbatoolsci_view_example3
$table1count = $db.Query("select id from dbo.dbatoolsci_view_example")
$table2count = $db2.Query("select id from dbo.dbatoolsci_view_example3")
$table1count.Count | Should -Be $table2count.Count
}
It "supports piping" {
$null = Get-DbaDbView -SqlInstance $script:instance1 -Database tempdb -View dbatoolsci_view_example | Copy-DbaDbViewData -DestinationTable dbatoolsci_example2 -Truncate
$table1count = $db.Query("select id from dbo.dbatoolsci_view_example")
$table2count = $db.Query("select id from dbo.dbatoolsci_example2")
$table1count.Count | Should -Be $table2count.Count
}
It "supports piping more than one view" {
$results = Get-DbaDbView -SqlInstance $script:instance1 -Database tempdb -View dbatoolsci_view_example2, dbatoolsci_view_example | Copy-DbaDbViewData -DestinationTable dbatoolsci_example3
$results.Count | Should -Be 2
$results.RowsCopied | Measure-Object -Sum | Select -Expand Sum | Should -Be 20
}
It "opens and closes connections properly" {
#regression test, see #3468
$results = Get-DbaDbView -SqlInstance $script:instance1 -Database tempdb -View 'dbo.dbatoolsci_view_example', 'dbo.dbatoolsci_view_example4' | Copy-DbaDbViewData -Destination $script:instance2 -DestinationDatabase tempdb -KeepIdentity -KeepNulls -BatchSize 5000 -Truncate
$results.Count | Should -Be 2
$table1dbcount = $db.Query("select id from dbo.dbatoolsci_view_example")
$table4dbcount = $db2.Query("select id from dbo.dbatoolsci_view_example4")
$table1db2count = $db.Query("select id from dbo.dbatoolsci_view_example")
$table4db2count = $db2.Query("select id from dbo.dbatoolsci_view_example4")
$table1dbcount.Count | Should -Be $table1db2count.Count
$table4dbcount.Count | Should -Be $table4db2count.Count
$results[0].RowsCopied | Should -Be 10
$results[1].RowsCopied | Should -Be 13
$table4db2check = $db2.Query("select id from dbo.dbatoolsci_view_example4 where id = 1")
$table4db2check.Count | Should -Be 13
}
It "Should warn and return nothing if Source and Destination are same" {
$result = Copy-DbaDbViewData -SqlInstance $script:instance1 -Database tempdb -View dbatoolsci_view_example -Truncate -WarningVariable tablewarning
$result | Should -Be $null
$tablewarning | Should -match "Cannot copy dbatoolsci_view_example into itself"
}
It "Should warn if the destination table doesn't exist" {
$result = Copy-DbaDbViewData -SqlInstance $script:instance1 -Database tempdb -View tempdb.dbo.dbatoolsci_view_example -DestinationTable dbatoolsci_view_does_not_exist -WarningVariable tablewarning
$result | Should -Be $null
$tablewarning | Should -match Auto
}
It "automatically creates the table" {
$result = Copy-DbaDbViewData -SqlInstance $script:instance1 -Database tempdb -View dbatoolsci_view_example -DestinationTable dbatoolsci_view_will_exist -AutoCreateTable
$result.DestinationTable | Should -Be 'dbatoolsci_view_will_exist'
}
It "Should warn if the source database doesn't exist" {
$result = Copy-DbaDbViewData -SqlInstance $script:instance2 -Database tempdb_invalid -View dbatoolsci_view_example -DestinationTable dbatoolsci_doesntexist -WarningVariable tablewarning
$result | Should -Be $null
$tablewarning | Should -match "Failure"
}
It "Copy data using a query that relies on the default source database" {
$result = Copy-DbaDbViewData -SqlInstance $script:instance1 -Database tempdb -View dbatoolsci_view_example -Query "SELECT TOP (1) Id FROM dbo.dbatoolsci_view_example4 ORDER BY Id DESC" -DestinationTable dbatoolsci_example3 -Truncate
$result.RowsCopied | Should -Be 1
}
It "Copy data using a query that uses a 3 part query" {
$result = Copy-DbaDbViewData -SqlInstance $script:instance1 -Database tempdb -View dbatoolsci_view_example -Query "SELECT TOP (1) Id FROM tempdb.dbo.dbatoolsci_view_example4 ORDER BY Id DESC" -DestinationTable dbatoolsci_example3 -Truncate
$result.RowsCopied | Should -Be 1
}
}