CM-5bvv/UpdateDB.bat at master · ylhyh/CM-5bvv · GitHub

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
@echo off

set CMDBServer=192.168.45.19
set CMDBUser=cm
set CMDBPass=cm@10160915
set CMDBName=CM

svn info
if %errorlevel% NEQ 0 goto Error

for /f "tokens=4 delims= " %%i in ('svn info ^| find "Last Changed Rev: "') do set LastRev=%%i
@echo Last Changed Revision: %LastRev%

if %errorlevel% NEQ 0 goto Error

sqlcmd -b -S %CMDBServer% -U %CMDBUser% -P %CMDBPass% -d %CMDBName% -Q "SELECT 'Max Revision Number:' + CONVERT(VARCHAR,ISNULL(Max(RevisionNumber),0)) MaxRev FROM DBUpdateHistory WHERE DBServer='%DBServer%' AND DBName='%DBName%' AND Success=1" > %CurDir%\temp_maxrev_query.tmp

if %errorlevel% NEQ 0 goto Error

for /f "tokens=2 delims=:" %%i in ('findstr /C:"Max Revision Number:" "%CurDir%\temp_maxrev_query.tmp"') do set PrevRev=%%i

if %errorlevel% NEQ 0 goto Error

del %CurDir%\temp_maxrev_query.tmp

@echo Previous Updated Revision: %PrevRev%

svn list --username huyaohui --password 123457 --trust-server-cert-failures=unknown-ca -v -R -r %LastRev% > "%CurDir%\newlist.tmp"
if %errorlevel% NEQ 0 goto Error

sort "%CurDir%\newlist.tmp" > "%CurDir%\sortedlist.tmp"
del "%CurDir%\newlist.tmp"
if %errorlevel% NEQ 0 goto Error

set sqlExecuteFailed=0
set successFiles=
set failedFiles=
set /a sqlFileCount=0
setlocal enabledelayedexpansion
for /f "usebackq tokens=1,7 delims= " %%i in ("%CurDir%\sortedlist.tmp") do (
    if %%i GTR %PrevRev% (
        if "%%j" NEQ "" (
           if "%Env%" EQU "Prod" (
                for /f %%i in ('%~dp0AddFrontZero.bat !sqlFileCount!') do set SQLFileNum=%%i
                copy "%CurDir%\%%j" "%PublishFolder%\DBScripts\%ActiveLanguage%\SQL_!SQLFileNum!_%%i_%%j"
                echo %PublishFolder%\DBScripts\%ActiveLanguage%\SQL_!SQLFileNum!_%%i_%%j
                echo sqlcmd -b -S %CMDBServer% -U %CMDBUser% -P %CMDBPass% -d %CMDBName% -Q "INSERT INTO DBUpdateHistory(RevisionNumber,DBServer,DBName,UpdatedFileName,Success,UpdateTime) VALUES(%%i,'%DBServer%','%DBName%','%%j',1,getdate())" >> %CMStatusUpdateBatch%
                echo if %%errorlevel%% NEQ 0 exit /b %%errorlevel%% >> %CMStatusUpdateBatch%
                set /a sqlFileCount+=1
            ) else (
                echo Starting update SQL file: %%i %%j
                sqlcmd -b -S %DBServer% -U %DBUser% -P "%DBPass%" -d %DBName% -i "%CurDir%\%%j"

                if !errorlevel! EQU 0 (
                    echo SQL file execute successfully: %%i %%j
                    echo Starting record execution status to database
                    sqlcmd -b -S %CMDBServer% -U %CMDBUser% -P %CMDBPass% -d %CMDBName% -Q "INSERT INTO DBUpdateHistory(RevisionNumber,DBServer,DBName,UpdatedFileName,Success,UpdateTime) VALUES(%%i,'%DBServer%','%DBName%','%%j',1,getdate())"
                    if "!successFiles!" NEQ "" (
                        set successFiles=!successFiles!,[%%i::%%j]
                    ) else (
                        set successFiles=[%%i::%%j]
                    )
                ) else (
                    set sqlExecuteFailed=1
                    echo SQL file execute failed: %%i %%j
                    sqlcmd -b -S %CMDBServer% -U %CMDBUser% -P %CMDBPass% -d %CMDBName% -Q "INSERT INTO DBUpdateHistory(RevisionNumber,DBServer,DBName,UpdatedFileName,Success,UpdateTime) VALUES(%%i,'%DBServer%','%DBName%','%%j',0,getdate())"
                    if "!failedFiles!" NEQ "" (
                        set failedFiles=!failedFiles!,[%%i::%%j]
                    ) else (
                        set failedFiles=[%%i::%%j]
                    )
                )
            )
        )
    )
)

del "%CurDir%\sortedlist.tmp"

echo ------------------------------------------------------------------------------

if "%Env%" EQU "Prod" (
    if %sqlFileCount% NEQ 0 (
        echo 共复制了[%sqlFileCount%]个SQL文件到发布目录: %PublishFolder% ，下次打包将不会包括这些SQL文件。
        echo DB Scripts Revision #:%LastRev% [%ActiveLanguage%] >> %PublishFolder%\Readme.txt
    ) else (
        echo 没有数据库更新。
        echo No DB scripts need to be executed. [%ActiveLanguage%] >> %PublishFolder%\Readme.txt
    )
) else (
    if "%successFiles%" EQU "" (
        if "%failedFiles%" EQU "" (
            echo 没有文件需要更新,没有,没有,没有,没有
        ) else (
            echo 数据库更新失败: %failedFiles%
            goto Error
        )
    ) else (
        if "%failedFiles%" EQU "" (
            echo 数据库更新成功,成功,成功,成功,成功
        ) else (
            echo 部分文件更新成功: %successFiles%
            echo 部分文件更新失败: %failedFiles%
            goto Error
        )
    )
)

echo ------------------------------------------------------------------------------

if %sqlExecuteFailed% NEQ 0 goto Error

:End
exit /b 0

:Error
echo An error has occurred
if %errorlevel% EQU 0 (
    exit /b 1
) else (
    exit /b %errorlevel%
)