[PATCH] Fixes and enhancements for the sql2xml task

[Andreou Andreas <an...@di.uoa.gr>]

hi! I've only just starting following this list, and I was trying to use 
the jdbc task in order to convert some of my databases
(mainly oracle) to torque's xml. For some reason, this just does not 
work for me, so I figured I could update the sql2xml
task and have it generate the xml from my sql scripts.
So, the main changes are:

    SQLScanner.java: now reports line number correctly on MS platforms, 
and recognizes when the minus sign is not used for comments.

    SQLToAppData.java: parses ALTER TABLE arguments, CREATE INDEX 
statements and the CONSTRAINTS keyword in
        CREATE TABLE. It now works ok for all my sql scripts+for the 
scripts that get generated by torque (when given an xml)
        The only problem I had was when a column was desclared as LONG 
VARCHAR, which is easy to fix, but , since I'm new,
        I don't know if we should support this type (maybe it's only 
oracle specific).

    Index.java-Foreign.java-Table.java: enable the previous changes + 
now export xml fields that weren't exported

I hope this can help those that want to convert sql scripts to xml. 
Respect...

Info on jdbc task on Oracle

[Andreou Andreas <an...@di.uoa.gr>]

Martin Poeschl wrote:

> what was the problem with the jdbc task????

Actually the problem is not on the task, but in ORACLE's schema support...

Whenever a user is created on oracle, a schema with the same name is 
also created.
The catch is:
    1) Even though username and password is case insensitive, the schema 
is NOT.
    2) The schema is created by uppercasing the username !!!
So,for ORACLE, in the build.properties, one should define the schema in 
uppercase.

Re: [PATCH] Fixes and enhancements for the sql2xml task

[Martin Poeschl <mp...@marmot.at>]

Andreou Andreas wrote:

> Well, just some more thoughts:
>
> - Is there a reason why the sql2xml task does NOT generate a dtd 
> compliant xml? You can
> take a look at the toString() method of AppData.java and see that it 
> uses an <app-data> tag.
> Also, the toString() method of ForeignKey.java does not append 
> ondelete and onupdate
> information, and the toString() of Table.java does not append foreign 
> keys and indexes. Maybe
> this was because the sql2xml task couldn't extract such information 
> from an *.sql file, but using a
> patch I've submitted in the past all these can be extracted...
>
> - Is there any interest on a SQLToAppData test? I've already created 
> such a test which basicly
> does the following:
>    1) Copies all *.sql and *.ref.xml files from a folder in src/rttest 
> to target/test/rttest/sql
>    2) Generates a *.sql.xml file for each *.sql file in 
> target/test/rttest/sql using the sql2xml task
>    3) Compares all *.sql.xml files with the corresponding *.ref.xml. 
> The test passes if and only if
>    all data (= tags and values) found on *.ref.xml (reference xml) are 
> also found on the generated
>    *.sql.xml files
>
> - Are there any other thoughts on how to improve the sql2xml task or 
> the above-described test? 

did you file the issue and patches in scarab??
please add your tests to the issue ...

martin

>
>
> Respect,
>    Andreas Andreou
>
>
> -- 
> To unsubscribe, e-mail:   
> <ma...@jakarta.apache.org>
> For additional commands, e-mail: 
> <ma...@jakarta.apache.org>
>

Re: [PATCH] Fixes and enhancements for the sql2xml task

[Andreou Andreas <an...@di.uoa.gr>]

Well, just some more thoughts:

- Is there a reason why the sql2xml task does NOT generate a dtd 
compliant xml? You can
take a look at the toString() method of AppData.java and see that it 
uses an <app-data> tag.
Also, the toString() method of ForeignKey.java does not append ondelete 
and onupdate
information, and the toString() of Table.java does not append foreign 
keys and indexes. Maybe
this was because the sql2xml task couldn't extract such information from 
an *.sql file, but using a
patch I've submitted in the past all these can be extracted...

- Is there any interest on a SQLToAppData test? I've already created 
such a test which basicly
does the following:
    1) Copies all *.sql and *.ref.xml files from a folder in src/rttest 
to target/test/rttest/sql
    2) Generates a *.sql.xml file for each *.sql file in 
target/test/rttest/sql using the sql2xml task
    3) Compares all *.sql.xml files with the corresponding *.ref.xml. 
The test passes if and only if
    all data (= tags and values) found on *.ref.xml (reference xml) are 
also found on the generated
    *.sql.xml files

- Are there any other thoughts on how to improve the sql2xml task or the 
above-described test?

Respect,
    Andreas Andreou

[PATCH] Enhancements on the sql2xml task and the jdbc task

[Andreou Andreas <an...@di.uoa.gr>]

AppData can now output valid xml files with correct dtd (that's why 
Database needs to
return valid defaultIdMethod).

TorqueJDBCTransformTask takes into account the
DatabaseMetaData.storesUpperCaseIdentifiers() property in order to
correct(i.e. uppercase) the schema name (when needed)

Re: [PATCH] Fixes and enhancements for the sql2xml task

[Andreou Andreas <an...@di.uoa.gr>]

hmm...forgive me for posting again, but the patches don't seem to get 
included in the mail...

Re: [PATCH] Fixes and enhancements for the sql2xml task

[Andreou Andreas <an...@di.uoa.gr>]

rest of diffs

Re: [PATCH] Fixes and enhancements for the sql2xml task

[Andreou Andreas <an...@di.uoa.gr>]

Martin Poeschl wrote:

> the unit tests!!
> i don't want to check in anything which could break the current stuff 
> before the 3.0 release is done ...

ok...here are the latest u-diffs and the output of the unit tests...

Re: [PATCH] Fixes and enhancements for the sql2xml task

[Martin Poeschl <mp...@marmot.at>]

Andreou Andreas wrote:

>> if you send the tests i'll checkin your changes :-)
>>
> you are talking about my manual tests (I have'em right now) or
> the junit test (will need some time on that - as I see it, it should 
> load all
> *.sql files on a given folder, create the xmls and have them compared
> with the expected xmls - also found on the same folder) ???
> Also, the sql2xml task used to enter in infinite loops when given complex
> sql files...This has been improved but not totally fixed yet (i.e. set 
> a column
> type to long varchar and see what hapens!!!) 

the unit tests!!
i don't want to check in anything which could break the current stuff 
before the 3.0 release is done ...

martin

Re: [PATCH] Fixes and enhancements for the sql2xml task

[Andreou Andreas <an...@di.uoa.gr>]

> if you send the tests i'll checkin your changes :-)
>
you are talking about my manual tests (I have'em right now) or
the junit test (will need some time on that - as I see it, it should 
load all
*.sql files on a given folder, create the xmls and have them compared
with the expected xmls - also found on the same folder) ???
Also, the sql2xml task used to enter in infinite loops when given complex
sql files...This has been improved but not totally fixed yet (i.e. set a 
column
type to long varchar and see what hapens!!!)

Re: [PATCH] Fixes and enhancements for the sql2xml task

[Martin Poeschl <mp...@marmot.at>]

Andreou Andreas wrote:

> Martin Poeschl wrote:
>
>> what was the problem with the jdbc task???? 
>
>
> don't know yet...I'll try to look at it today...maybe I'm not setting
> parameters correctly, but I tried to have the jdbc task connect to
> Oracle and Sap (which it correctly did) and then generate the
> corresponding shema.xml (which it DOESN'T)
>
>> you only tested for oracle??
>>
> No.
> My own sql scripts are mainly for oracle (cause that's what we use at 
> the university)
> and I tested heavily on them and they all pass and create correct xmls.
> Then I had torque create sql scripts from the above xmls, from those
> included with the source (id-table-schema.xml) and also from hand made 
> xmls.
> I told it to create the scripts for both postgres and then oracle.
> Using those scripts, I finally run the sql2xml task again and they 
> pass ok.
> I also tested the postgres sql script that comes with jive.
> So, till now I've tested on postgres and oracle sripts.
> I'm planning on testing the task with many other scripts + for other 
> kinds
> of DBs (i.e. mySql) and making a junit test to automate this process... 

cool!
if you send the tests i'll checkin your changes :-)

as i want to release 3.0 soon .. i think this will be included in 3.1 
... i'll send a mail about my plans for future releases later today ..

martin

Re: [PATCH] Fixes and enhancements for the sql2xml task

[Andreou Andreas <an...@di.uoa.gr>]

Martin Poeschl wrote:

> what was the problem with the jdbc task???? 

don't know yet...I'll try to look at it today...maybe I'm not setting
parameters correctly, but I tried to have the jdbc task connect to
Oracle and Sap (which it correctly did) and then generate the
corresponding shema.xml (which it DOESN'T)

> you only tested for oracle??
>
No.
My own sql scripts are mainly for oracle (cause that's what we use at 
the university)
and I tested heavily on them and they all pass and create correct xmls.
Then I had torque create sql scripts from the above xmls, from those
included with the source (id-table-schema.xml) and also from hand made xmls.
I told it to create the scripts for both postgres and then oracle.
Using those scripts, I finally run the sql2xml task again and they pass ok.
I also tested the postgres sql script that comes with jive.
So, till now I've tested on postgres and oracle sripts.
I'm planning on testing the task with many other scripts + for other kinds
of DBs (i.e. mySql) and making a junit test to automate this process...

Re: [PATCH] Fixes and enhancements for the sql2xml task

[Martin Poeschl <mp...@marmot.at>]

Andreou Andreas wrote:

> hi! I've only just starting following this list, and I was trying to 
> use the jdbc task in order to convert some of my databases
> (mainly oracle) to torque's xml. For some reason, this just does not 
> work for me, so I figured I could update the sql2xml
> task and have it generate the xml from my sql scripts. 

what was the problem with the jdbc task????

>
> So, the main changes are:
>
>    SQLScanner.java: now reports line number correctly on MS platforms, 
> and recognizes when the minus sign is not used for comments.
>
>    SQLToAppData.java: parses ALTER TABLE arguments, CREATE INDEX 
> statements and the CONSTRAINTS keyword in
>        CREATE TABLE. It now works ok for all my sql scripts+for the 
> scripts that get generated by torque (when given an xml)
>        The only problem I had was when a column was desclared as LONG 
> VARCHAR, which is easy to fix, but , since I'm new,
>        I don't know if we should support this type (maybe it's only 
> oracle specific).
>
>    Index.java-Foreign.java-Table.java: enable the previous changes + 
> now export xml fields that weren't exported
>
> I hope this can help those that want to convert sql scripts to xml. 
> Respect...

you only tested for oracle??