search

sharadaccenture / roundhouse

Automatically exported from code.google.com/p/roundhouse
0 stars 0 forks source link

Split scripts on the GO #17

Closed GoogleCodeExporter closed 8 years ago

GoogleCodeExporter commented 8 years ago 
I have made an enhancement on how scripts are executed. There is a problem
with Sprocs where a CREATE must be the first word on the script file.
RoundhousE or SQL Server has problems with scripts like this:

IF OBJECT_ID('ResetValues') IS NOT NULL
    DROP PROCEDURE ResetValues
GO

CREATE PROCEDURE ResetValues
AS
BEGIN
    UPDATE ValueStore SET
    value = 0    

    PRINT 'All values where set to 0'
END

The solution I found in the RH examples is good but it requires the
creators of the scripts to know that there is a problem with the script
they would normally write.

To solve this I adjusted the code so it will split scripts when they
contain GO. Everything else stays the same it it still on big script file
but is executed per GO.

See the atteched patch for the code.

Original issue reported on code.google.com by jochen.j...@gmail.com on 16 Feb 2010 at 3:16

Attachments:

GoogleCodeExporter commented 8 years ago 
The "\sGO\s" regex isn't a bulletproof solution. 
It would be better if it is changed to "[\f\n\r]GO[\f\n\r]".

Original comment by jochen.j...@gmail.com on 16 Feb 2010 at 3:44

GoogleCodeExporter commented 8 years ago 
I actually do this with the OleDB because different databases may not allow 
more 
than one statement to execute at a time.  I will port that over. 

It uses both ; and GO as valid statements to split.

Original comment by trueblu...@gmail.com on 16 Feb 2010 at 5:57

GoogleCodeExporter commented 8 years ago

Original comment by trueblu...@gmail.com on 16 Feb 2010 at 5:59

GoogleCodeExporter commented 8 years ago 
It does this:
string[] separation_characters = new string[] { ";", "GO" };

return sql_to_run.Split(separation_characters, 
StringSplitOptions.RemoveEmptyEntries);

Original comment by trueblu...@gmail.com on 16 Feb 2010 at 6:08

GoogleCodeExporter commented 8 years ago 
It may not be as fast as the RegEx, but it will catch them.

Of course, I am looking at the way I do it currently and seeing a possible bug. 
Hmmmmm.....

Original comment by trueblu...@gmail.com on 16 Feb 2010 at 6:10

GoogleCodeExporter commented 8 years ago 
This looks like it will address both.
Has to break a line afterwards

[(GO)(\;)]+[\f\n\r]

Original comment by trueblu...@gmail.com on 16 Feb 2010 at 6:30

GoogleCodeExporter commented 8 years ago 
Fixed in revision 110. Ended up with this: [GO|;]+[\f\n\r]

Original comment by trueblu...@gmail.com on 16 Feb 2010 at 11:56

GoogleCodeExporter commented 8 years ago 
After testing the new version it didn't work. While I was working on the path
yesterday somebody else dropped all our scripts in the RH test project and when 
I
tried to use the new version of RH it didn't work anymore.

It was a good test and I had to change the code again. The problem is that RH 
now
splits the scipts on GO and ;, but for T-SQL this isn't correct. I found an 
article
that explains this,
http://www.sqlservercentral.com/articles/SQL+Puzzles/thegocommandandthesemicolon
terminator/2200/.
(Use BugMeNot to login).

In short the GO is a batch separator and the ; isn't. So for T-SQL, RH shouldn't
split on ;. I think for Oracle and other database systems a split will not be 
necessary.

I made a patch with the changes. The version of RH with this path works for all 
the
scripts we have so far.

Original comment by jochen.j...@gmail.com on 17 Feb 2010 at 9:40

Attachments:

GoogleCodeExporter commented 8 years ago 
Btw changed the regex also, because it was splitting on G and O also.

Original comment by jochen.j...@gmail.com on 17 Feb 2010 at 9:43

GoogleCodeExporter commented 8 years ago 
I can't get to the site - email?

Original comment by trueblu...@gmail.com on 17 Feb 2010 at 3:39

GoogleCodeExporter commented 8 years ago 
re opening the issue...

Original comment by trueblu...@gmail.com on 17 Feb 2010 at 5:11

GoogleCodeExporter commented 8 years ago 
Patch applied in revision 113.

Original comment by trueblu...@gmail.com on 17 Feb 2010 at 10:46

GoogleCodeExporter commented 8 years ago

Original comment by trueblu...@gmail.com on 17 Feb 2010 at 10:47

GoogleCodeExporter commented 8 years ago 
In the next revision 114, I moved those separators to the SQLScripts section 
since 
it was more a concern for TSQL and PLSQL than for the database type. It also 
helps 
for OleDB to not have to know about both/multiple types.

Original comment by trueblu...@gmail.com on 17 Feb 2010 at 11:04