kaino / dbdeploy Goto Github PK

Package names of net.sf.dbdeploy of confusing as dbdeploy is no longer
hosted on sourceforge.

Update to com.dbdeploy to "point" to the dbdeploy website.

What steps will reproduce the problem?
1. Create any delta script.
2. Generate the DbDeploy output sql
3. Run the output sql via the Ant sql target.

What is the expected output? What do you see instead?
The script should run without error.
I'm getting an error due to incorrect syntax
"com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: Incorrect syntax
near 'GO'."

My sql script starts with the lines:

BEGIN TRANSACTION
GO

This works fine when not run via JDBC (in SQL Server Management Studio
Express).

What version of the product are you using? On what operating system?
3.0M1
SQL Server JDBC4 driver. (Not sure exactly which version of the driver).

All output from dbdeploy ant task is just dumped on stdout.  The ant task
should use the ant logger to display feedback.

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

I have a data file that is about 150MB, has 15 tables and the largest 
table has about 6 million rows. I get heap space error when I try to run 
it against mysql.

BUILD FAILED
java.lang.OutOfMemoryError: Java heap space
        at java.util.Arrays.copyOf(Arrays.java:2882)
        at java.lang.AbstractStringBuilder.expandCapacity
(AbstractStringBuilder.java:100)
        at java.lang.AbstractStringBuilder.append
(AbstractStringBuilder.java:390)
        at java.lang.StringBuilder.append(StringBuilder.java:119)
        at com.dbdeploy.scripts.ChangeScript.getFileContents
(ChangeScript.java:82)
        at com.dbdeploy.scripts.ChangeScript.getContent
(ChangeScript.java:56)
        at com.dbdeploy.appliers.DirectToDbApplier.apply
(DirectToDbApplier.java:29)
        at com.dbdeploy.Controller.processChangeScripts(Controller.java:40)
        at com.dbdeploy.DbDeploy.go(DbDeploy.java:111)
        at com.dbdeploy.AntTarget.execute(AntTarget.java:35)
        at org.apache.tools.ant.UnknownElement.execute
(UnknownElement.java:288)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke
(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.tools.ant.dispatch.DispatchUtils.execute
(DispatchUtils.java:106)
        at org.apache.tools.ant.Task.perform(Task.java:348)
        at org.apache.tools.ant.Target.execute(Target.java:357)
        at org.apache.tools.ant.Target.performTasks(Target.java:385)
        at org.apache.tools.ant.Project.executeSortedTargets
(Project.java:1337)
        at org.apache.tools.ant.Project.executeTarget(Project.java:1306)
        at org.apache.tools.ant.helper.DefaultExecutor.executeTargets
(DefaultExecutor.java:41)
        at org.apache.tools.ant.Project.executeTargets(Project.java:1189)
        at org.apache.tools.ant.Main.runBuild(Main.java:758)
        at org.apache.tools.ant.Main.startAnt(Main.java:217)
        at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257)
        at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104)

Would like to see this fixed. Or, is there a way to run load file?

I need to create a trigger on a table.  I have the following script.

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
  END;
|

delimiter ;
--//@UNDO

drop trigger testref;


When I run this, I get the following errors:


Applying #226701: 226701_user_notify_kitchen_journal_trigger.sql...
Failed to apply changes: java.lang.RuntimeException: 
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in 
your SQL syntax; check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INS' at line 1
java.lang.RuntimeException: 
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in 
your SQL syntax; check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INS' at line 1
    at com.dbdeploy.appliers.DirectToDbApplier.applyChangeScriptContent(DirectToDbApplier.java:50)
    at com.dbdeploy.appliers.DirectToDbApplier.apply(DirectToDbApplier.java:29)
    at com.dbdeploy.Controller.processChangeScripts(Controller.java:40)
    at com.dbdeploy.DbDeploy.go(DbDeploy.java:111)
    at com.dbdeploy.CommandLineTarget.main(CommandLineTarget.java:13)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have 
an error in your SQL syntax; check the manual that corresponds to your MySQL 
server version for the right syntax to use near 'delimiter |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INS' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.Util.getInstance(Util.java:384)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2512)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:781)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:624)
    at com.dbdeploy.database.changelog.QueryExecuter.execute(QueryExecuter.java:22)
    at com.dbdeploy.appliers.DirectToDbApplier.applyChangeScriptContent(DirectToDbApplier.java:47)
    ... 4 more

[deleted issue]

Some databases allow the developer to declare variables in their sql
scripts. For example, in PostgreSQL a variable name is preceded by a colon.

grant select on app_user to :readonly_role;

The value of the variable can be provided in different ways depending on
how the script is run (command line parameters, prompt dialogs etc).

It would be great if DbDeploy were able to substitute these variables when
it generates its delta script. Ideally, this would work without requiring
any DbDeploy-specific syntax in the sql scripts. i.e. The scripts should
still work with other tools.

The version number displayed should be taken from the POM.

The DbmsSyntax class supports multiple databases by allowing 5 or 6
different SQL strings to change.  (e.g. delimiter, commit, get current user
name etc).

Dbdeploy should allow users to provide their own file with these snippets
in it.  It can the be customised for specific scenarios and support for new
databases added without it having to be added to the core codebase.

What steps will reproduce the problem?
1. Create DDL or DML deltas
2. Generate output SQL for Oracle (dbms=ora)
3. See "BEGIN TRANSACTION;" in output.sql



What is the expected output? What do you see instead?
Oracle automaticly starts transactions thus expected no statements for
begining transactions.

What version of the product are you using? On what operating system?
Oracle 9, Oracle 10.

Please provide any additional information below.

"BEGIN TRANSACTION;" statement is invalid for Oracle.

Fix:
In com.dbdeploy.database.syntax.OracleDbmsSyntax.java add 
public String generateBeginTransaction() {
                return "";
        }

Same for OracleSqlPlusDbmsSyntax.java

What steps will reproduce the problem?
1. Use CLI interface with -d mysql option to generate a sql script

What is the expected output? What do you see instead?
Header and footer should be using "-- " as the beginning; however, the header 
contains "-----" 
instead (which does not work with MySQL).

What version of the product are you using? On what operating system?
3.0m1

Please provide any additional information below.

What steps will reproduce the problem?
1. Try to build dbdeploy on Windows
2. Unit tests fail for two reasons, this is one of them.

What is the expected output? What do you see instead?
Unit tests pass on Windows.

What version of the product are you using? On what operating system?
svn trunk on Windows.

What steps will reproduce the problem?
1. Run ant build with dbdeploy taskdef on Java 5 JVM

What is the expected output? What do you see instead?
See java.lang.UnsupportedClassVersionError

What version of the product are you using? On what operating system?
DbDeploy-3.0M1

Please provide any additional information below.
Everything works on JVM 6 but you'd better compile it with at least Java 5
compatibility.

What steps will reproduce the problem?
1. add a sql file to a migration set that contains comments which start 
with --
2. run the dbdeploy process
3. build process fails with java.lang.RuntimeException: 
java.sql.SQLException: Invalid SQL type
4. If the comments are deleted and the process is re-run, then no 
exceptions are thrown.

What version of the product are you using? db-deploy 3.0M2
On what operating system? windows
Database: Oracle

Please provide any additional information below.


BUILD FAILED
C:\Documents and Settings\barretni\My Documents\development\db-
deploy\build.xml:110: The following error occurred while executing this 
line:
C:\Documents and Settings\barretni\My Documents\development\db-
deploy\build-macros.xml:16: java.lang.RuntimeException: 
java.sql.SQLException: Invalid SQL type
        at com.dbdeploy.appliers.DirectToDbApplier.applyChangeScriptContent
(DirectToDbApplier.java:50)
        at com.dbdeploy.appliers.DirectToDbApplier.apply
(DirectToDbApplier.java:29)
        at com.dbdeploy.Controller.processChangeScripts(Controller.java:40)
        at com.dbdeploy.DbDeploy.go(DbDeploy.java:111)
        at com.dbdeploy.AntTarget.execute(AntTarget.java:35)
        at org.apache.tools.ant.UnknownElement.execute
(UnknownElement.java:291)
        at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:618)
        at org.apache.tools.ant.dispatch.DispatchUtils.execute
(DispatchUtils.java:106)
        at org.apache.tools.ant.Task.perform(Task.java:348)
        at org.apache.tools.ant.taskdefs.Sequential.execute
(Sequential.java:68)
        at org.apache.tools.ant.UnknownElement.execute
(UnknownElement.java:291)
        at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:618)
        at org.apache.tools.ant.dispatch.DispatchUtils.execute
(DispatchUtils.java:106)
        at org.apache.tools.ant.Task.perform(Task.java:348)
        at org.apache.tools.ant.taskdefs.MacroInstance.execute
(MacroInstance.java:398)
        at org.apache.tools.ant.UnknownElement.execute
(UnknownElement.java:291)
        at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:618)
        at org.apache.tools.ant.dispatch.DispatchUtils.execute
(DispatchUtils.java:106)
        at org.apache.tools.ant.Task.perform(Task.java:348)
        at org.apache.tools.ant.Target.execute(Target.java:390)
        at org.apache.tools.ant.Target.performTasks(Target.java:411)
        at org.apache.tools.ant.Project.executeSortedTargets
(Project.java:1360)
        at org.apache.tools.ant.Project.executeTarget(Project.java:1329)
        at org.apache.tools.ant.helper.DefaultExecutor.executeTargets
(DefaultExecutor.java:41)
        at org.apache.tools.ant.Project.executeTargets(Project.java:1212)
        at org.apache.tools.ant.Main.runBuild(Main.java:801)
        at org.apache.tools.ant.Main.startAnt(Main.java:218)
        at org.apache.tools.ant.launch.Launcher.run(Launcher.java:280)
        at org.apache.tools.ant.launch.Launcher.main(Launcher.java:109)
Caused by: java.sql.SQLException: Invalid SQL type
        at oracle.jdbc.driver.DatabaseError.throwSqlException
(DatabaseError.java:125)
        at oracle.jdbc.driver.DatabaseError.throwSqlException
(DatabaseError.java:162)
        at oracle.jdbc.driver.DatabaseError.throwSqlException
(DatabaseError.java:227)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:64)
        at oracle.jdbc.driver.T4CStatement.execute_for_rows
(T4CStatement.java:475)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout
(OracleStatement.java:1027)
        at oracle.jdbc.driver.OracleStatement.execute
(OracleStatement.java:1515)
        at com.dbdeploy.database.changelog.QueryExecuter.execute
(QueryExecuter.java:22)
        at com.dbdeploy.appliers.DirectToDbApplier.applyChangeScriptContent
(DirectToDbApplier.java:47)
        ... 34 more

Total time: 1 second

This was discussed http://groups.google.com/group/db-deploy-users/
browse_thread/thread/dd958110e5e5c3f8 - but I still see no pgsql in 
documentation nor source code.

I've attached a diff which adds the patch, as well as a tiny addition to 
the tests of the dbms factory.

What steps will reproduce the problem?
1. Create a sub-directory in the directory containing the sql delta scripts
2. Run dbdeploy


What is the expected output? What do you see instead?
DbDebploy fails with a FileNotFoundException (Access is denied) error
because it is trying to open the sub-directory. I think it should quietly
ignore any directories it finds.

What version of the product are you using? On what operating system?
3.0M1
Windows XP

What steps will reproduce the problem?
1. execute a change script that creates an oracle function or procedure
2. note that the function is created
3. however executing the function produces an error message that the procedure 
is in an invalid 
state (we then have to open it and recompile it)

This is executing dbdeploy on Windows.

See 
http://groups.google.com/group/db-deploy-users/browse_thread/thread/d342fea8a23d
ae28 
for more information.

As a DBA, 
I want to comment some words in dbdeploy script file to describe table or
field, and dbdeploy can treat those comments as a part of database
dictionary and store in them in a separate table, 
so that I can get the exact meanings easily by searching the field in the table

here is my proposal:

if this comment is provided in db script file("001 insert field in table.sql")
--//@meta: table.field="field in table"
then we can insert into the database dictionary table like this:
"insert into db_dictionay(tablename, objectname, description,
createversion) values ('table','field', 'field in table' , 1)"

BTW, looking forward to get your response, otherwise we'll add this feature
by ourselves:)

Often a database migration involves adding reference data to the database.
That reference data might not always be stored in SQL.

For example, GIS data can be stored in shapefiles.  Translating shapefiles
into SQL insert statements massively increases the size of the data, also
the shapefiles can be easily edited and viewed as geometry, while the SQL
cannot.  So, I want to store the shapefiles, not the SQL, in version control.

DBDeply should allow update files to be in other formats and translate
those formats to SQL statements during the migration procedure.

That translation process should be extensible, because data formats will be
application specific.

> What steps will reproduce the problem?
1. Create an Oracle package definition containing several procedures in
000_Create_XXX_Package.sql
2. Run DbDeploy
3. Always fails at second procedure of package body

> What is the expected output? What do you see instead?

It should create the package. Code is fine: runs perfectly against the
database. I see:

[sql] Executing resource:
C:\Projects\Helm\distribution\database\ora\Helm-schemaUpgrade.sql
[sql] Failed to execute:    procedure check_val( id in integer, l_cname in
varchar2, l_old in varchar2, l_new in varchar2)

BUILD FAILED
C:\Projects\Helm\build.xml:254: java.sql.SQLException: ORA-00900: invalid
SQL statement

> What version of the product are you using? On what operating system?
v3.0, Windows XP

> Please provide any additional information below.
Always fails on the 2nd procedure in the package definition, not the body.
See attached files. 0046_Create_Audit_Package.sql is where it fails.

In the current published version of dbdeploy if a script fails to apply you
have to manually update the changelog table to run again.

This was a regression between 1.0 and 2.0 caused by the attempt to log
start and end times of script runs. 

Insert to changelog should only be done at the end of the script fragment
within the same transaction.  Therefore, once the issue with a script is
fixed dbdeploy can be successfully rerun without manual fixups.

How it worked in 1.0:

BEGIN TRANSACTION

-- content of change script

INSERT INTO CHANGELOG
COMMIT


How it works in 2.0:

BEGIN TRANSACTION
INSERT INTO CHANGELOG
COMMIT

BEGIN TRANSACTION
-- content of change script
UPDATE CHANGELOG
COMMIT

Behaviour should revert to how it worked in 1.0.

Historically dbdeploy just published a jar file in sourceforce.

Should publish a ZIP containing:
1. the jars & dependencies
2. license
3. readme
4. source?

Currently, I think "lastChangeToApply" and undo script generation are not
supported from the command line interface.

They should be.

dbdeploy currently opens the script files without specifying a character
encoding, meaning it uses the platform default encoding.

Good practice suggests that it should be possible to explicitly specify what
character encoding is used to read change script files.

What steps will reproduce the problem?
1. Call dbdeploy from application code.

QueryExecutor fails to close the connection used to query the change log.  
It similarly fails to close the Statement and ResultSet.

What version of the product are you using? On what operating system?
2.11

We're facing a problem with the existing functionality of DbDeploy, which
is today's need for creating the CHANGELOG table manually

This could be solved by DbDeploy creating the CHANGELOG table if none exists.

In order for this to work, we chose to wrap the DbDeploy code (3.0M2)
rather than patching it, but you get the idea ;-)

Would be great if this functionality would be part of your baseline in the
future!

(This looks like the same issues as r22, but for another database dialect.)

What steps will reproduce the problem?

1. Create DDL delta
2. Generate output SQL for SyBase (dbms=syb-ase)
3. See "BEGIN TRANSACTION" and "GO" in output.sql

What is the expected output? What do you see instead?

Sybase DDL statements should not run within explicit transactions.

What version of the product are you using? On what operating system?

DbDeploy 3.0 on Ubuntu Linux.

Please provide any additional information below.

DbDeploy 2.x didn't include the "BEGIN TRANSACTION" and "GO" in output.sql,
and it worked fine.

Currently dbdeploy has to connect to the target database in order to work
out which scripts to apply.

For simple cases, this requirement could be avoided by simply specifying
the start and end script numbers to dbdeploy.  It currently supports the
"end script" value but not a start script value.

Hence, users can make upgrade and downgrade scripts for production without
having to hit the production DB to generate them.

(Suggested by Andy Slocum)

What steps will reproduce the problem?
1. Create a script that has any arbitrary SQL statement
2. Create a second script that has a CREATE PROCEDURE statement
3. Run DbDeploy

What is the expected output? What do you see instead?
The build fails with the error message:
CREATE/ALTER PROCEDURE must be the first statement in a query batch.

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



Please provide any additional information below.

As an application author, I want to apply any necessary change scripts to 
the database by invoking DBDeploy from code, so that I can easily reset my 
database between tests.

Acceptance criteria:
* After invoking dbdeploy, any necessary scripts must have been actually 
applied to the database.
* I should not have to duplicate any dbdeploy code in order to invoke it 
from my application.

This may really be 2 stories?

Proposal:

Overload ToPrintSteamDeployer.doDeploy(), to allow injection of a 
ChangeScriptWriter, for which the application author can substitute an 
implementation that writes changes direct to the database.  DBDeploy should 
be the facade through which the application author injects the substitute 
ChangeScriptWriter.

What steps will reproduce the problem?
1. Generate a delta sql.
2.
3.

What is the expected output? What do you see instead?
The value of the change number in the generated SQL should be the sequence
number from the start of the sql file name. Instead, it is the name of the
maven artifact.

INSERT INTO changelog (change_number, complete_dt, applied_by, description)
 VALUES (com.dbdeploy:dbdeploy-core:jar:3.0-SNAPSHOT, getdate(),
user_name(), '0006_create_table_margin_call.sql')

What version of the product are you using? On what operating system?
Trunk as of revision 126.

Please provide any additional information below.
Unit tests in trunk are failing. ScriptGenerationTest.

When using the -o flag from the CLI, the consolidated output file is formatting 
the change_number with a comma thousand separator, which then breaks the 
insert, as the column counts aren't correct. As you can see below the 
change_number should be 219001  not 219,001.

INSERT INTO changelog (change_number, complete_dt, applied_by, description)
 VALUES (219,001, CURRENT_TIMESTAMP, USER(), '219001_add_notify_to_user.sql');

What steps will reproduce the problem?
1. Run ant to produce database scripts
2. Try to post process scripts using other ant tasks e.g. fixcrlf
3. Script files are locked

What is the expected output? What do you see instead?
Should be able to delete or change script files

What version of the product are you using? On what operating system?
trunk on Windows

Please provide any additional information below.
Sometimes it works, presumably if the finalizer for the class has run in time 
thereby closing the stream

garyhodgson was kind enough to upload an example gant file for dbdeploy - see 
http://groups.google.com/group/db-deploy-users/browse_thread/thread/52a02913d5d2
461f

Incorporate this into the documentation / samples.

It would be nice if the dbdeploy Ant target set a property if it determines
that there are no scripts to be applied. 

An example is the way the JUnit task sets a property if any tests failed.

This would enable us to avoid attempting to apply the delta script.

Some scripts are intended only for use in development or test environments,
e.g. population of test data. 

It would be nice if there was a way to mark scripts to be excluded from
certain environments. This could be implemented in much the same way as the
Ant 'if' and 'unless' attributes can be used to filter out targets to be
run in a build.

http://ant.apache.org/manual/properties.html#if+unless

When using the command line interface, the output file should default to
stdout.

Currently dbdeploy only supports "undo" when generating scripts (using the
undoOutputfile parameter).  

It should be possible to use the undo functionality when applying direct to
db too.

The current ant task does not allow for a classpath argument like the <sql> 
task.  This makes it 
very difficult to integrate with a project using ivy or using a library dir and 
not keeping everything 
in the ant/lib or the home/.ant/lib dirs.

What steps will reproduce the problem?
1. Create a deploy script that has a number greater then > 999  (say
01000_create_whatever_table.sql)
2. Run dbdeploy ant task making sure to output to a file (apply as a
separate step)  Note, the error does not occur if you apply with dbdeploy
with the same ant task.
3. Apply using <sql> ant task the outputted script

What is the expected output?  Expected should apply.


What do you see instead?   The change number has a "comma" in it when
inserting into the ChangeLog table causing this statement to fail

      [sql] Failed to execute:    INSERT INTO changelog (change_number,
complete_dt, applied_by, description) VALUES (1,000, CURRENT_TIMESTAMP,
USER(), '1000.sql')


What version of the product are you using? On what operating system?  3.0M2
on Windows XP 32-bit/Java 6


Please provide any additional information below.


Reading change scripts from directory
D:\Camel\di-0.0.1-SNAPSHOT\di-domain\src\test\resources\testdb\deltas...
Changes currently applied to database:
  (none)
Scripts available:
  1..3, 1000
To be applied:
  1..3, 1000
Generating undo scripts...
      [sql] Executing file:
D:\Camel\di-0.0.1-SNAPSHOT\di-domain\target\testdb-apply.sql
      [sql] Failed to execute:    INSERT INTO changelog (change_number,
complete_dt, applied_by, description) VALUES (1,000, CURRENT_TIMESTAMP,
USER(), '1000.sql')
[INFO] ------------------------------------------------------------------------
[ERROR] BUILD ERROR
[INFO] ------------------------------------------------------------------------
[INFO] Error executing ant tasks

Embedded error: The following error occurred while executing this line:
D:\Camel\di-0.0.1-SNAPSHOT\di-domain\src\test\resources\testdb\build.xml:67: 
java.sql.SQLException:
Column count does no
t match in statement [   INSERT INTO changelog (change_number, complete_dt,
applied_by, description) VALUES (1,000, CURR
ENT_TIMESTAMP, USER(),]




    <target name="update-database-and-apply-as-separate-step"
description="generate a sql upgrade script">

        <!-- use dbdeploy to generate the change script -->
        <dbdeploy driver="${db.driver}" url="${db.url}"
                  userid="${db.user}"
                  password="${db.password}"
                  dir="src/test/resources/testdb/deltas"
                  outputfile="target/testdb-apply.sql"
                  undoOutputfile="target/testdb-undo.sql"
                  dbms="${db.type}"
                />

        <!-- now apply the changescript to the database -->
        <sql driver="${db.driver}" url="${db.url}"
             userid="sa" password="" classpathref="libs.classpath">
            <fileset file="target/testdb-apply.sql"/>
        </sql>

    </target>


-- START CHANGE SCRIPT #1000: 1000.sql

-- Numbering convention:  The first three represent the schema_version that
-- corresponds to the tagged production schema_version.  The next 3
-- digits are there to order the scripts for the dbdeploy testdb creation.
create table ipdsis.schema_version (
    version_id          numeric(5)       not null,
    subversion_id       varchar(10),
    apply_date          timestamp        not null,
    rollback_date       timestamp,
    status              varchar(20)     not null,
    create_date         timestamp        not null,
    modified_date       timestamp        not null
);


INSERT INTO changelog (change_number, complete_dt, applied_by, description)
 VALUES (1,000, CURRENT_TIMESTAMP, USER(), '1000.sql');

COMMIT;

-- END CHANGE SCRIPT #1000: 1000.sql

As a software developer, I want to be able to specify high level 
refactorings (e.g. rename a column, rename a table, move a column from one 
table to another, change the type of a column, etc.) to perform on my 
database, which dbdeploy would then apply, so that I don't have to write 
the scripts myself.

* dbdeploy should write both the alter table statement AND the data 
migration script required to perform the specified refactoring.

I suggest that this could be implemented as a DSL, which would output a 
change script.

Possible issues:

* Not all refactorings will be easy to support - but some will be.
* Integrating the execution of DSL scripts with the execution of standard 
change scripts, in a easy-to-use manner could be interesting.

It is possible that this could be viewed as a separate project to dbdeploy, 
but it does seem to fit well with the overall purpose of dbdeploy.  If it 
were viewed separately, then dbdeploy would be left as just a sequential 
script executor, with the proposed DSL being a script generator.  There 
does seem to be an area of overlap, though.

As a database designer, I want to specify tablespaces and storage for my 
tables and indices, etc, so that my database performs optimally.

* The storage associated with a given object should be able to be varied 
depending on the environment it is being deployed to.  e.g. my development 
server might not have 10Gb of disk space free to allocate to my segment, 
but my prod server absolutely must have this.

Proposal -

There seems to be 2 possible approaches to this:  

1) We could parse the scripts to find create statements and add storage to 
created objects according to some local (environment-specific) 
configuration file.

2) We could use a java DSL to write our create scripts, which would save us 
from having to parse the scripts and would give us much more versatile 
programmatic control.

Either way, an abstract syntax tree would have to be developed to represent 
the DDL, so option 2 might be a useful first step toward option 1.  i.e. 
the DSL would simply be a programmatic way of building our AST by hand, 
with the parser being a way of generating that AST from an existing script.

As with the other DSL enhancement that I have proposed, this could also be 
considered out of scope for core dbdeploy.  Once again, however, there is a 
fair bit of overlap between the two in the script parsing area.

It might be useful to build this as a second module within the dbdeploy 
project?

What steps will reproduce the problem?

1. Create DDL delta
2. Generate output SQL for Oracle (dbms=ora)
3. See "BEGIN TRANSACTION;" in output.sql

What is the expected output? What do you see instead?

Oracle DDL statements should not run within explicit transactions.

What version of the product are you using? On what operating system?

DbDeploy 3.0 on Win XP Pro.

Please provide any additional information below.

I removed the word "BEGIN TRANSACTION" in output.sql,
and it worked fine.

We use a continuous integration server (Hudson) to do our builds/ 
deployment. It would be nice if dbdeploy supports the concept of a version 
# -i.e. the ChangeLog table has a version number column and have that 
value set by passing parameters from the build server to the dbdeploy 
scripts.

DBDeploy should output a script that records whether the database schema is
currently in a consistent state. At the start of the update it should set
the flag to false (and commit the txn). At the end of the update it should
set the flag to true.

If the update fails, the failure can be automatically detected.

E.g. an application that uses the database can check whether it has been
started against a consistent schema, and fail immediately if it has not.

(We currently check for acceptable schema version, but cannot tell
automatically if an upgrade has failed).

The deltaset concept is sensible to use in a few edge cases only, notably
when using dbdeploy to deploy to replicated databases.

However, deltaset "Main" infects the code all over the place.  And the
changelog table is more complex than it needs be because e.g. the PK has to
cover both the change number and the deltaset name.  Therefore any custom
code looking at the changelog table needs to hardcode "Main" as well.

A simpler implementation would be to allow user specification of the name
of the changelog table instead.

Hi guys ... 

me and friend just found out that there is a bug in your freemaker templates. 
When you scripts that have a index number higher than 999 (ie 1000 and up) the 
changelog insert will fail. This is because freemaker formats numbers when 
printing them out.

So for example when i create a script 

1500-my_test_script.sql

following output is produced:

INSERT INTO changelog (change_number, complete_dt, applied_by, description)
 VALUES (1,500, CURRENT_TIMESTAMP, USER(), '1500-my_test_script.sql');

and this of course fails.

The solution is to change all the freemaker templates. For example 
mysql_apply.ftl

needs to change to this (added a ?string.number after script.id) 

INSERT INTO ${changeLogTableName} (change_number, complete_dt, applied_by, 
description)
 VALUES (${script.id?string.number}, CURRENT_TIMESTAMP, USER(), '${script.description}');

here is the section in the freemaker manual that describes exactly this problem 
:) 
http://freemarker.sourceforge.net/docs/ref_builtins_number.html

i hope you guys can fix that very quick since I already showed you how to fix 
it... :) 

Thanks
Chris

dbdeploy should support db2.

Thanks to David Carlson for the attached patch.

There should be a dbdeploy maven plugin in addition to the ant plugin and
the command line support.