Issue Details (XML | Word | Printable)

Type: Bug Bug
Status: Closed Closed
Resolution: Cannot Reproduce
Priority: No Testcase No Testcase
Assignee: Unassigned
Reporter: Ryan Asleson
Votes: 0
Watchers: 0

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

SchemaTool: Does Not Honor "LowerCase"

Created: 09/Feb/12 05:56 AM   Updated: 21/Mar/12 10:19 AM   Resolved: 13/Feb/12 03:00 PM
Component/s: SchemaTool
Affects Version/s: 3.0.6
Fix Version/s: None

File Attachments: 1. Zip Archive (20 kB)
2. File schematool-1.1.0m4.sql (3 kB)
3. File schematool-3.0.6.sql (3 kB)
4. XML File schematool-ant.xml (2 kB)

Environment: PostgreSQL 8.4, Windows XP, DataNucleus 3.0.6

Datastore: PostgreSQL
Severity: Development

 Description  « Hide

I'm upgrading from DataNucleus 1.1.0m4 (yes, I know it is old) to the current 3.0.6 release.

I replaced the jar files and tweaked a few package names. Everything compiled and the bytecode enhancement didn't fail, so I thought it was going to be an easy upgrade.

However, when I tried to use SchemaTool to recreate the unit testing database, the insertion of sample data wasn't working. After much confusion, I noticed that two versions of each table existed: the previous ones created by 1.1.0m4 SchemaTool, which had lower case names (the PostgreSQL standard) and ones where the names were capitalized, which were created by 3.0.6 SchemaTool. When using SchemaTool, I set the "" property to LowerCase.

I tweaked SchemaTool to output the DDL. I noticed that the DDL produced by 1.1.0m4 put the table names in lower case, and without double quotes. This is the behavior I expected, considering I set the property. The DDL produced by 3.0.6 had the table names capitalized AND inside of double quotes, which explains why it created tables with capitalized names.

Attached to this issue is the DDL produced by 1.1.0m4, 3.0.6, and the XML fragment within my Ant file that uses SchemaTool.

It seems to me that 3.0.6 is not honoring the property the way it should. I tried using UpperCase and PreserveCase, and they both produced the same DDL with capitalized table names within quotes. I also tried using the datanucleus1 identifier factory, and it too produced DDL that was capitalized and within quotes.

I tried to debug the issue but didn't get very far. In the file around line 533 there is an array of properties specified via system properties, and I noticed that was not there, so I was hoping it was an oversight. However, I looked at the 1.1.0 version and it's not there either. I also looked through classes like MappedStoreManager, IdentifierFactory, and DN2IdentifierFactory. It looks like they are all trying to handle the case requested by the user.

My best very uneducated guess is that the "plumbing" is all there, but somewhere along the way the value of the property as set by the user is being dropped or lost, so the identifier factories use the default strategy.

Is this truly a bug or did I miss a change in the configuration? I can't see any changes in the SchemaTool documentation that's on the web.

Thank you!!!!!


Ryan Asleson added a comment - 09/Feb/12 06:01 AM
schematool-ant.xml: Shows the SchemaTool Ant task usage and how the property is set.

schematool-1.1.0m4.sql: Shows the DDL produced by SchemaTool 1.1.0m4, which as expected has database identifiers in lower case and without quotes.

schematool-3.0.6.sql: Shows the DDL produced by SchemaTool 3.0.6, where the database identifiers are uppercase and in double quotes, which seems contrary to the property when set to LowerCase.

Ryan Asleson made changes - 09/Feb/12 06:01 AM
Field Original Value New Value
Attachment schematool-ant.xml [ 11582 ]
Attachment schematool-1.1.0m4.sql [ 11583 ]
Attachment schematool-3.0.6.sql [ 11584 ]
Andy Jefferson added a comment - 09/Feb/12 09:17 AM
No testcase? obviously works for me, using Postgresql 8.x. Using lowercase it puts things in lowercase. Using PreserveCase it preserves case. Using UPPERCASE it does just that. And defaults to UPPERCASE.

Andy Jefferson made changes - 09/Feb/12 09:17 AM
Priority Major [ 3 ] Incomplete [ 6 ]
Ryan Asleson added a comment - 10/Feb/12 05:42 AM
This test case, when run on my development workstation, shows how the output DDL uses uppercase identifiers in quotes even though the property is set to LowerCase.

Unzip the file and you'll find a build.xml file. In the "schematool" task you'll have to update the database connection properties with a valid PostgreSQL database, otherwise the validation apparently fails. After that, you should be able to run the "schematool" task (it's the default) and when it's done, take a look in the ${basedir}/build/db/schema-create.sql file. In my environment, the identifiers are all uppercase and contained in quotes.

The testcase uses the same domain objects and JDO files as the previous attachments, so the outputs should look identical.

Ryan Asleson made changes - 10/Feb/12 05:42 AM
Attachment [ 11585 ]
Andy Jefferson added a comment - 10/Feb/12 08:41 AM
Please revisit the testcase format (linked from front page of JIRA, and docs)

i.e a testcase ought to be more like 4kb and not 4Mb.

Ryan Asleson made changes - 10/Feb/12 03:54 PM
Attachment [ 11585 ]
Ryan Asleson added a comment - 10/Feb/12 07:38 PM
Please find new test case attached. A few notes:

1. As currently configured, the test expects these classes to be in the /lib directory: asm-3.3.jar, datanucleus-api-jdo-3.0.5.jar, datanucleus-core-3.0.6.jar, datanucleus-enhancer-3.0.1.jar, datanucleus-rdbms-3.0.6.jar, jdo-api-3.1-SNAPSHOT-20110926.jar, and postgresql-8.3-603.jdbc4.jar.

2. As long as the jar files are in the /lib directory, running the "schematool" task should output the DDL to /build/db/schema-create.sql. When run in my environment using the jars mentioned in #1, the DDL has uppercase identifiers contained in quotes, despite the property set to LowerCase.

3. Apparently a valid connection to a PostgreSQL database is required, so the database connection info in the build file will likely need to be tweaked.

Thank you for your help!!!!

Ryan Asleson made changes - 10/Feb/12 07:38 PM
Attachment [ 11586 ]
Andy Jefferson added a comment - 10/Feb/12 08:02 PM
Works as expected. I use Maven2 to invoke SchemaTool, but apart from that your files. Using Postgresql 8.3 .9 using JDBC 8.3.603 on Linux.

-- DataNucleus SchemaTool (ran at 10/02/2012 18:58:21)
-- Schema diff for jdbc:postgresql:nucleus and the following classes:-
-- org.datanucleus.test.A
-- org.datanucleus.test.B
-- org.datanucleus.test.D
-- Table d for classes [org.datanucleus.test.D, org.datanucleus.test.A]
    id int8 NOT NULL,
    authority varchar(255) NULL,
    application_user_id int8 NOT NULL,
    user_name varchar(255) NULL,
    create_timestamp timestamptz NULL,
    create_user_id int8 NULL,
    last_commit_timestamp timestamptz NULL,
    last_commit_user_id int8 NULL,
    "version" int8 NOT NULL,

-- Table b for classes [org.datanucleus.test.B, org.datanucleus.test.A]
    id int8 NOT NULL,
    password varchar(50) NOT NULL,
    user_name varchar(50) NOT NULL,
    create_timestamp timestamptz NULL,
    create_user_id int8 NULL,
    last_commit_timestamp timestamptz NULL,
    last_commit_user_id int8 NULL,
    "version" int8 NOT NULL,

-- Constraints for table d for class(es) [org.datanucleus.test.D, org.datanucleus.test.A]

CREATE INDEX d_n49 ON d (application_user_id);

-- Constraints for table b for class(es) [org.datanucleus.test.B, org.datanucleus.test.A]

Ryan Asleson added a comment - 10/Feb/12 08:36 PM
Any thoughts on what I could be doing wrong? The submitted test case fails both on my XP laptop and on my OS X workstation. I've tried restarting NetBeans but that shouldn't matter. Any thoughts or hints? I swear I'm not making this up and trying to waste your time!

Andy Jefferson added a comment - 11/Feb/12 08:32 AM
All I can guess at is that it relates to you passing parameters in as system args to SchemaTool, something that was never part of any guaranteed behaviour (and is unmaintainable). Do what any sane being would do and put them in a properties file (or persistence.xml file) and cease embedding crucial persistence information in build files ;-) This gives you the added advantage that you can use such persistence properties for SchemaTool, and for runtime PMF creation

Andy Jefferson added a comment - 13/Feb/12 03:00 PM
Closing since can't see it - see previous comment for why I think you get it and the way to avoid the problem. Look in the log for a line like
19:06:38,527 (main) DEBUG [DataNucleus.Datastore] - Datastore Identifiers : factory="datanucleus2" case=lowercase schema=public

The "case=" is what it is using, and if is not lowercase then hasn't received your property value, hence it is down to using System args.

Andy Jefferson made changes - 13/Feb/12 03:00 PM
Status Open [ 1 ] Resolved [ 5 ]
Resolution Cannot Reproduce [ 5 ]
Ryan Asleson added a comment - 14/Feb/12 02:42 PM
I placed the parameters in a properties file and now all is well. I've joined the world of the sane and can go on my merry way :-)

Thank you for your help!!!

Andy Jefferson made changes - 21/Mar/12 10:19 AM
Status Resolved [ 5 ] Closed [ 6 ]