run-clean-us-business-data.ps1
2.97 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
#Requires -Version 5.1
<#
.SYNOPSIS
执行美国版业务数据清理 SQL（标签 / 日志 / 门店等）
.DESCRIPTION
调用同目录 clean-us-business-data.sql。
默认不保留 user / role / menu；会清空 userlocation，登录用户需重新绑定门店。
.PARAMETER Host
MySQL 主机，默认 127.0.0.1
.PARAMETER Port
MySQL 端口，默认 3306
.PARAMETER User
MySQL 用户，默认 root
.PARAMETER Database
数据库名（必填）
.PARAMETER Password
密码；未传则提示输入
.PARAMETER DryRun
仅打印将执行的命令，不真正执行
.EXAMPLE
.\run-clean-us-business-data.ps1 -Database food_labeling_us
.EXAMPLE
.\run-clean-us-business-data.ps1 -Host 127.0.0.1 -Port 3306 -User root -Database food_labeling_us
#>
[CmdletBinding(SupportsShouldProcess = $true)]
param(
[string] $Host = '127.0.0.1',
[int] $Port = 3306,
[string] $User = 'root',
[Parameter(Mandatory = $true)]
[string] $Database,
[string] $Password,
[switch] $DryRun
)
$ErrorActionPreference = 'Stop'
$scriptDir = Split-Path -Parent $MyInvocation.MyCommand.Path
$sqlFile = Join-Path $scriptDir 'clean-us-business-data.sql'
if (-not (Test-Path $sqlFile)) {
throw "SQL file not found: $sqlFile"
}
$mysql = Get-Command mysql -ErrorAction SilentlyContinue
if (-not $mysql) {
throw "mysql CLI not found in PATH. Install MySQL client or add mysql.exe to PATH."
}
Write-Host ''
Write-Host '========================================' -ForegroundColor Yellow
Write-Host ' US business data cleanup (DESTRUCTIVE) ' -ForegroundColor Yellow
Write-Host '========================================' -ForegroundColor Yellow
Write-Host "Target : $User@${Host}:$Port / $Database"
Write-Host 'Clears : print logs, labels, templates, products, locations, partner/region, userlocation'
Write-Host 'Keeps : user, role, userrole, userpost, menu, rolemenu'
Write-Host ''
if (-not $PSCmdlet.ShouldProcess($Database, 'Run clean-us-business-data.sql')) {
return
}
$confirm = Read-Host "Type YES to continue"
if ($confirm -ne 'YES') {
Write-Host 'Cancelled.' -ForegroundColor Cyan
return
}
if (-not $Password) {
$secure = Read-Host 'MySQL password' -AsSecureString
$bstr = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($secure)
try {
$Password = [Runtime.InteropServices.Marshal]::PtrToStringAuto($bstr)
} finally {
[Runtime.InteropServices.Marshal]::ZeroFreeBSTR($bstr)
}
}
$args = @(
"-h$Host",
"-P$Port",
"-u$User",
"-p$Password",
'--default-character-set=utf8mb4',
$Database
)
$cmdDisplay = "mysql -h$Host -P$Port -u$User -p*** $Database < clean-us-business-data.sql"
Write-Host "Executing: $cmdDisplay" -ForegroundColor Gray
if ($DryRun) {
Write-Host '[DryRun] Skipped execution.' -ForegroundColor Cyan
return
}
Get-Content -LiteralPath $sqlFile -Encoding UTF8 | & $mysql.Source @args
if ($LASTEXITCODE -ne 0) {
throw "mysql exited with code $LASTEXITCODE"
}
Write-Host ''
Write-Host 'Cleanup finished. Check BEFORE/AFTER counts in output above.' -ForegroundColor Green