create/drop target not working under Oracle

[GoogleCodeExporter]

What steps will reproduce the problem?
1. add carbon five maven plugin configuration with oracle db
2. run -e db-migration:drop
3. get exception

Caused by: java.lang.NullPointerException
    at
com.carbonfive.db.jdbc.schema.AbstractSchemaCommand.execute(AbstractSchemaComman
d.java:63)
    at com.carbonfive.db.jdbc.schema.DropDatabase.execute(DropDatabase.java:5)
    at com.carbonfive.db.migration.maven.DropMojo.executeMojo(DropMojo.java:19)
    ... 19 more

What is the expected output? What do you see instead?
 should get valid connection to db/execute drop sql script

What version of the product are you using? On what operating system?
 0.9.6 on mac os x

Please provide any additional information below.
 I have debugged the code and found the problem:
  AbstractSchemaCommand(line:53) is not making valid connection to oracle,
this is because the UID is being chopped off the serverUrl, hence the
connection ends up being null, please note that migrations work because
they use jdbcTemplate to connect to Oracle instead of manually. Another
thing to think about is that Oracle does not have drop schema capability,
we will solve this by logging in as system account and droping the user, or
running plsql to drop all tables if possible.

Original issue reported on code.google.com by nino.svo...@gmail.com on 3 Feb 2009 at 5:17

[GoogleCodeExporter]

putting the SID twice is a workaround eg. jdbc:oracle:thin:@server:1521:SID:SID

Original comment by nino.svo...@gmail.com on 3 Feb 2009 at 6:26

[GoogleCodeExporter]

Thanks for the detailed issue report!  We haven't built "official" oracle 
support
because we don't have an oracle instance to test against.  How do you feel about
submitting a patch to DatabaseUtils and corresponding test to fix the URL 
parsing code?

Original comment by christia...@gmail.com on 3 Feb 2009 at 4:53

[GoogleCodeExporter]

I'll do my best, how does the following changes sound (as a start):

- fix DatabaseUtils server extract for oracle support
- add two properties to maven plugin: systemUsername, systemPassword
   if these are present create/drop connection will be made using these instead

Original comment by nino.svo...@gmail.com on 4 Feb 2009 at 4:57

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Please find attached the patch for fixes mentioned above. I have also updated 
the
abstract mojo to allow passing multiple statements to the createSql/dropSql
configuration.

We are able to get create/drop working on Oracle with the following 
configuration:

<plugin>
    <groupId>com.carbonfive</groupId>
    <artifactId>db-migration-maven-plugin</artifactId>
    <version>0.9.7-SNAPSHOT</version>
    <configuration>
        <migrationsPath>src/main/resources/db/migrations/</migrationsPath>
        <dropSql>
            <![CDATA[
                drop user ${db.username} cascade;
            ]]>
        </dropSql>
        <createSql>
            <![CDATA[
                create user ${db.username} identified by ${db.username}
default tablespace asdf_data temporary tablespace temp;
                grant connect, create table, create sequence to ${db.username};
                grant create procedure, create trigger to ${db.username};
                alter user ${db.username} quota unlimited on asdf_data;
                alter user ${db.username} quota unlimited on asdf_idx;
            ]]>
        </createSql>
        <systemUsername>${system.db.username}</systemUsername>
        <systemPassword>${system.db.password}</systemPassword>
        <driver>${db.driverClassName}</driver>
        <url>${db.url}</url>
        <username>${db.username}</username>
        <password>${db.password}</password>
    </configuration>
    <dependencies>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.1.0.2.0</version>
        </dependency>
    </dependencies>
</plugin>

Could you please add these changes to a snapshot when you can.
When are you planning release 1?

Original comment by nino.svo...@gmail.com on 4 Feb 2009 at 7:19

Attachments:

• fix_databaseutils_for_oracle_and_add_system_user_support.diff

[GoogleCodeExporter]

I just realised the reset goal does not reuse the create/drop code, I will make
another patch to fix the reset goal also.

Original comment by nino.svo...@gmail.com on 4 Feb 2009 at 11:58

[GoogleCodeExporter]

nino.svonja: I want to thank you for submitting a patch.  I looked over it, and
looked a little further into what it would take to support Oracle properly, and 
I'm
afraid it'll simply take too much special case code.  We don't use Oracle, so 
I'm
inclined to keep the code clean (for now).  If I have more time, and access to 
an
Oracle instance, I'll take another stab at making this work.

In the meantime, I encourage you to copy the source and make you're own branch 
that
meets your needs.  Hopefully one day I'll be able to offer Oracle support.  
Thanks again.

Original comment by christia...@gmail.com on 27 Feb 2009 at 5:02

• Changed state: WontFix