On 5/17/07, Emilian Bold <[EMAIL PROTECTED]> wrote:
I'm trying to use DDLUtils to migrate a database from MS SQL Server to Derby. The import process works ok with only one issue: it doesn't migrate the schema. All my SQL Server tables are in the DBO. schema and the ones in Derby just use the default schema. I've tried these fixes: - Using schemapattern="dbo" in ddlToDatabase and databaseToDdl. This doesn't do anything different.
This is for limiting from which schemas to read data (some DBs require this and will return system tables if not specified, e.g. Oracle).
- Modified the schema XML files to set table name="dbo.$name". This seems to create the tables properly! But importing the data fails and I have to change that XML file with the data too. The downside is that this file also has some CDATA XML text inside so my scripts corrupt the CDATA.
Schema specification in the schema xml is not yet properly supported (it's a feature request for 1.1).
Is there some way to import / export with schema information ? Basically have table.name="SCHEMA$oldName" and foreign-key.foreignTable="SCHEMA.$oldname" (maybe some other tags need to be update, not sure).
With Derby what you can do is to have the plain table names without any schema info into the schema XML, and then access the Derby DB using a user whose name matches the target schema. The default schema in Derby is the set to the user's name that you logged in with (see also http://db.apache.org/derby/faq.html#schema_exist), so this should work. Tom
