1

I have the following batch file which concatenates all of the files in a folder that have a .sql ending.

set func=%~dp0%Stored Procedures\*.sql
for %%i in (%func%) do type "%%i" >>InstallScript.sql

We use SVN as our repository, and we're using branching. Currently the script concatenates all the .sql files, even the ones that haven't changed. I'd like to change it so it only concatenates files that have been modified and/or created after the branch was created. We can do that by looking at the datetime on the .svn folder in each folder (there's a Stored Procedure, View, Function subfolders). But I don't know how to do that with batch files.

Ideally something like this(pseudo code):

set func=%~dp0%Stored Procedures\*.sql
set branchDateTime=GetDateTime(%~dp0%.svn)  <- Gets the datetime when the .svn folder was created

for %%i in (%func%)
{
  if(%%i.LastModifiedOrCreated > branchDateTime)
   do type "%%i" >> InstallScript.sql
}

Thanks to Doc Brown, I ended up getting this(for anyone that needs something similar):

@echo off
cls

echo --Install Script Generated For MMH Database %DATE% %TIME% > InstallScript.sql

set branch=%~dp0.svn
for %%i in (%branch%) do set SvnFileDate=%%~ti
set year=%SvnFileDate:~8,2%
set month=%SvnFileDate:~0,2%
set day=%SvnFileDate:~3,2%
set hours=%SvnFileDate:~11,2%
set minutes=%SvnFileDate:~14,2%
set datetime=%year%%month%%day%%hours%%minutes%

:: Folder for Functions
set func=%~dp0%Functions\*.sql

setlocal enableextensions enabledelayedexpansion

@for %%i in (%func%) do set FuncFileDate=%%~ti& ^
set year2=!FuncFileDate:~8,2!& ^
set month2=!FuncFileDate:~0,2!& ^
set day2=!FuncFileDate:~3,2!& ^
set hours2=!FuncFileDate:~11,2!& ^
set minutes2=!FuncFileDate:~14,2!& ^
set datetime2=!year2!!month2!!day2!!hours2!!minutes2!& ^

if "!datetime!" LSS "!datetime2!" type "%%i" >> InstallScript.sql

:: I would add similiar code for each folder, one for Views, Stored Procs, etc...

endlocal


pause
Chris L
  • 669
  • 2
  • 10
  • 21

1 Answers1

1

The first step, getting the date time for the.svn folder works like this:

for %%i in (.svn) do set FileDate=%%~ti

You may have to reorder the result of that line to a comparable date-time format:

for %%i in (.svn) do set FileDate=%%~ti
set year=%FileDate:~6,4%
set month=%FileDate:~3,2%
set day=%FileDate:~0,2%
set hours=%FileDate:~11,2%
set minutes=%FileDate:~14,2%
set datetime=%year%%month%%day%%hours%%minutes%
echo %datetime%

The second part is a little bit more sophisticated, you need mutiline command, delayed expansion and a comparison:

setlocal enabledelayedexpansion

@for %%i in (*.sql) do set FileDate=%%~ti& ^
set year2=!FileDate:~6,4!& ^
set month2=!FileDate:~3,2!& ^
set day2=!FileDate:~0,2!& ^
set hours2=!FileDate:~11,2!& ^
set minutes2=!FileDate:~14,2!& ^
set datetime2=!year2!!month2!!day2!!hours2!!minutes2!& ^
if "!datetime!" LSS "!datetime2!" type "%%i" >> InstallScript.sql

Make sure not to enter any additional whitespace at the multi-line part. Hope this helps.

Doc Brown
  • 19,739
  • 7
  • 52
  • 88
  • I've got most of it working except for the last line (the If clause). I changed "setlocal enabledelayedexpansion" to "setlocal enableextensions enabledelayedexpansion" and added "endlocal" after the if. Nothing is getting added to InstallScript.sql – Chris L Oct 01 '12 at 22:08
  • @ChrisL: my suggestion: add some `echo` commands for debugging, check the values (order of month/day may vary depending on your local settings), and test the "IF" clause with fixed values to see if it works as it should. – Doc Brown Oct 02 '12 at 06:42
  • I found the problem, apparently the datetime/number is too long -> YYYYMMDDHHMM in numeric is too long for LSS to test, so I shortened the year to two digit only. YYMMDDHHMM and everything seems to work. However I'm getting the hour (HH) back in 12 hour with AM/PM – Chris L Oct 02 '12 at 14:46
  • I tried this: if not %SvnFileDate:PM=%==%SvnFileDate% set hours= hours+12 (from here: http://stackoverflow.com/questions/7005951/batch-file-find-if-substring-is-in-string-not-in-a-file) If there exists a "PM" in the string add 12 to hours. – Chris L Oct 02 '12 at 22:00
  • @ChrisL: did you add the quotes " around the numbers, just as I wrote above? Without that quotes, I had the same problems like you before. AFAIK using quotes causes the command shell to use a string comparison instead of a number comparison, which is the right thing as long as the strings have equal length. – Doc Brown Oct 03 '12 at 14:43