search

kimptoc / dbdeploy

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

[PATCH] delimiter and delimiterType have no effect in script mode #45

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
What steps will reproduce the problem?
1. specify a delimiter or delimiter type in script mode
2. note that the insert and commit lines produced by dbdeploy always end in ;

What is the expected output? What do you see instead?
Setting the delimiter to / and delimitertype to row should result in insert and 
commit ending in a newline followed by /

What version of the product are you using? On what operating system?
3.0M2, Windows

Original issue reported on code.google.com by longs...@gmail.com on 26 Jul 2010 at 12:56

Attachments:

GoogleCodeExporter commented 9 years ago 
Missed a file in the previous patch.

Original comment by longs...@gmail.com on 26 Jul 2010 at 1:23

Attachments:

GoogleCodeExporter commented 9 years ago

Original comment by gtack...@googlemail.com on 16 Mar 2011 at 11:10

GoogleCodeExporter commented 9 years ago 
Thanks for the patch. What was the specific problem you were seeing? Is it that 
one of the database script formatters is generating the wrong script? Which 
database are you using?

The main purpose for moving to using freemarker was to avoid scripts being 
built by string concatenation, which it transpired made it much harder to 
understand what was actually being generated, and generated bugs and 
interesting cases. This patch I'm slightly nervous about as it feels like it's 
moving us back to this world.

I'm guessing you're using oracle and sqlplus. We found that it was dangerous to 
automatically include a "/" in the script generated by dbdeploy, since this can 
cause statements to be executed more than once if the creator of the script 
already included a / at the end or didn't include a PL/SQL statement. So the 
approach we take is to include a "/" on the end of any scripts that include 
PL/SQL or SP declarations, inside the script.

In fact, we got so fed up with the dreadful error handling of sqlplus (and its 
reluctance to ever return a non-zero error code) that we've switched to using 
dbdeploy's "direct to db" mode instead of generating a script.

Have you tried using the approach detailed in GeneratingAndCustomisingScripts 
to build your own templates? I'd be very happy to include a "ora-sqlplus" 
template and/or update the ora_apply.sql with the changes you need to make your 
scripts run with sqlplus.

Original comment by gtack...@googlemail.com on 16 Mar 2011 at 11:31

GoogleCodeExporter commented 9 years ago 
This is an issue with Oracle and PL/SQL scripts as you surmised. When you 
create package bodies in PL/SQL they look like this...

CREATE OR REPLACE PACKAGE my_package AS

  FUNCTION my_function RETURN PLS_INTEGER;

END my_package;

If use the default delimiter of ; with the default delimiterType of normal then 
if you configure the sql-maven-plugin to execute this file it will take the ; 
characters as delimiters and fail to parse the script. The ; at the end of the 
my_function line can't be replaced by any other character so we need to have a 
different delimiter. 

The default for dbdeploy is to have a delimiter of ; and a delimiterType of 
normal IIRC so unless you override these then nothing changes in the output 
with my patch. If you do want a different delimiter in script creation mode 
then it doesn't work as you still get a commit; in the delta file and that 
won't work when the delimiter is /.

If you want to have new ora-sqlplus_apply.ftl and ora-sqlplus_undo.ftl scripts 
which would be the same as ora_apply.ftl and ora_undo.ftl but with the changes 
from my patch (and not apply the changes to any of the existing .ftl files) 
that would work for me. The other code changes would need to be applied so that 
the delimiter and delimiterType values can affect those sqlplus ftl files. I 
can create a patch to do this if you wish.

Original comment by longs...@gmail.com on 16 Mar 2011 at 12:31

GoogleCodeExporter commented 9 years ago 
Yeah. On second thoughts, I agree with your patch - as a user of dbdeploy it 
makes a lot of sense for the delimiter settings to apply to all the generated 
scripts too. And as you say this is a non-breaking change since the defaults 
mean the scripts are just as they are.

I'll apply before a 3.0 release.

Original comment by gtack...@googlemail.com on 16 Mar 2011 at 12:54

GoogleCodeExporter commented 9 years ago

Original comment by gtack...@googlemail.com on 29 Mar 2011 at 8:51

GoogleCodeExporter commented 9 years ago 
Commited patch in 
https://github.com/tackley/dbdeploy/commit/23ab556af50b02ae46c76eac41a77813c9262
0b2

Original comment by gtack...@googlemail.com on 4 Apr 2011 at 8:37

GoogleCodeExporter commented 9 years ago 
And in svn r171

Original comment by gtack...@googlemail.com on 4 Apr 2011 at 9:15

GoogleCodeExporter commented 9 years ago 
Hi 

Has this fix been released? I'm using version 3.0M3 and don't think the fix in 
this version as the delimiter is alawys a ';'.

Original comment by amin.udd...@gmail.com on 24 Apr 2014 at 10:00