[jira] Commented: (DERBY-1651) MySQL to Derby Migration Tool
[ http://issues.apache.org/jira/browse/DERBY-1651?page=comments#action_12425938 ] Satheesh Bandaram commented on DERBY-1651: -- Ramin, I am going to update the description and summary. If you don't like it, please change it as you see fit. It is important to have the description cover the whole feature but say you are working on implementing parts of the functionality and submit each part as being developed. > MySQL to Derby Migration Tool > - > > Key: DERBY-1651 > URL: http://issues.apache.org/jira/browse/DERBY-1651 > Project: Derby > Issue Type: New Feature > Components: Tools >Reporter: Ramin Moazeni > Assigned To: Ramin Moazeni > Attachments: MigrationTool-MySQLtoDerby.zip > > > This is the first drop of MySQL to Derby Migration tool. There is currently > support for tables and views. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-1651) MySQL to Derby Migration Tool
MySQL to Derby Migration Tool - Key: DERBY-1651 URL: http://issues.apache.org/jira/browse/DERBY-1651 Project: Derby Issue Type: New Feature Components: Tools Reporter: Ramin Moazeni Assigned To: Ramin Moazeni Attachments: MigrationTool-MySQLtoDerby.zip This is the first drop of MySQL to Derby Migration tool. There is currently support for tables and views. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: MySQL to Derby Migration Tool
Thanks, Ramin, no further comments or issues, I look forward to your next revision. David Ramin Moazeni wrote: Hi David, Thanks for your comments. My comments inline Thanks, Ramin On 6/27/06, David Van Couvering <[EMAIL PROTECTED]> wrote: Hi, Ramin. Great document! Very nicely done. Here are my comments: - You're a UML use case guy. Great! Wish there was more of that in software design, especially for tools. - What's the mapping from MySql YEAR to Derby? Derby does not seem to have a YEAR data type. We can probably use INTEGER insteadAny thoughts? - Can you explain a little more how DdlUtils takes care of data type mapping? ddlutils tool uses JDBC datatypes rather than SQL datatypes which are database dependent. Looking at http://db.apache.org/ddlutils/database-support.html, explains mapping between JDBC types and SQL types. For example, a TIINYINT datatype from MySQL, will be mapped to SMALLINT in Derby. I will explain this in detail in the document. - Can you explain a little more what you mean by "A possible solution would be to execute database-specific commands to capture required information." when you say DdlUtils doesn't support constraints, triggers, stored procedures, etc. In the case of MySQL, we can use the SHOW command to capture the exact string to re-create the original object. For example SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION can be used to capture the original statement used to create function/procedure. I will add these information to the document. - I guess I don't follow the application flow very well when using DdlUtils. What exactly are the steps taken by the user and/or the tool using DdlUtils? I probably need another usecase diagram for ddlutils. I will add that to the document. - What is *your* recommended approach (DatabaseMetadata vs. DdlUtils) and why? I would think a combination of ddlutils and DatabaseMetadata approach is needed to handle cases not supported by ddlutils like stroed procedures. I will add a section to the document on this. - Can you talk more to what happens if there is an error during migration at various phases. What steps does the tool take to set things right? Can you end up with a half-created database? What do you do to fix the errors and start over? I suspect migration is often a repetitive effort as you catch migration/mapping errors, fix things, and try again... This should be as painless as possible. Sure, I will add this information as well. - Can you please show the proposed syntax and other details (input files, config files, env variables, exit status) for the CLI? Ok. - Does the CLI have a "headless" mode (e.g. no interactive prompts) so you can run it in a script? Good suggestion.I was not thinking about CLI with headless mode. I will add information on this. - What is the syntax for starting the tool? Will you make use of derbyrun.jar? I will need to investigate this. - Can you cover quickly how you'll address internationalizing the GUI? Sure - Can you explain briefly how, using this architecture, you can plug in other data sources besides MySQL? Ok. - You might want to touch on the impact to existing JDBC applications that are running against MySQL, which you want to migrate to Derby. How are *those* migrated, and how much or how little does this tool help with that? If anything, just make it clear that application migration is out of the scope of this tool. Ok - What mechanism are you going to use to do the data transfer? Simple JDBC inserts? Bulk insert? For large databases the mechanism you use can have a large impact on performance, and may actually prevent some databases from migrating. It would be good to have a section on performance and what approaches you will take to improve performance and test performance. I will add a section on performance. - What about migration of users, groups, permissions, etc. -- is that out of scope? Good suggestionI did not think about this. I will add it to the docuemtn - Perhaps it would be good to be *very* explicit about what you plan to migrate and what you *don't* plan to migrate in this tool, and what you recommend for users for the bits that aren't being migrated (applications, authorizations, triggers, whatever is on that list). Ok - This may not be your itch to scratch, but can you address what you're planning to do to (or not do) to address governmental accessibility requirements (called Section 508)? See http://java.sun.com/products/jfc/accessibility/index.jsp. At a minimum it would be great if you don't do anything that makes it difficult for someone else to make the tool accessible. Ok. I will look into this. Thanks! David Ramin Moazeni wrote: > Hello, > > I am a Google Summer of code participant working on the Derby > Migration tool project. The High level design document is posted at > http://wiki.apache.org/db-derby/MysqlDerbyMigration/DesignDocument > > There are 2
Re: MySQL to Derby Migration Tool
Hi Satheesh Thanks for your commentsI will post the document ddlutils-dev alias as well. Thanks, Ramin On 6/27/06, Satheesh Bandaram <[EMAIL PROTECTED]> wrote: Great document and good comments from David. I think it would be good to follow ddlUtils approach (approach 2) if feasible. Ramin, should this approach be also discussed on ddlUtils development alias? I think you would need to handle some database objects like CHECK constraints and others in a database specific way in either approach. DatabaseMetadata doesn't provide a way and probably why ddlUtil doesn't support that either. These objects will have to be migrated by reading source database specific system tables... so your generic migration tool would probably need a source database specific plug-in... I would also like to encourage others to review the document as Ramin seems to be making good progress towards implementation. Satheesh On 6/27/06, David Van Couvering <[EMAIL PROTECTED]> wrote: > Hi, Ramin. Great document! Very nicely done. > > Here are my comments: > > - You're a UML use case guy. Great! Wish there was more of that in > software design, especially for tools. > > - What's the mapping from MySql YEAR to Derby? > > - Can you explain a little more how DdlUtils takes care of data type > mapping? > > - Can you explain a little more what you mean by "A possible solution > would be to execute database-specific commands to capture required > information." when you say DdlUtils doesn't support constraints, > triggers, stored procedures, etc. > > - I guess I don't follow the application flow very well when using > DdlUtils. What exactly are the steps taken by the user and/or the tool > using DdlUtils? > > - What is *your* recommended approach (DatabaseMetadata vs. DdlUtils) > and why? > > - Can you talk more to what happens if there is an error during > migration at various phases. What steps does the tool take to set things > right? Can you end up with a half-created database? What do you do to > fix the errors and start over? I suspect migration is often a > repetitive effort as you catch migration/mapping errors, fix things, and > try again... This should be as painless as possible. > > - Can you please show the proposed syntax and other details (input > files, config files, env variables, exit status) for the CLI? > > - Does the CLI have a "headless" mode ( e.g. no interactive prompts) so > you can run it in a script? > > - What is the syntax for starting the tool? Will you make use of > derbyrun.jar? > > - Can you cover quickly how you'll address internationalizing the GUI? > > - Can you explain briefly how, using this architecture, you can plug in > other data sources besides MySQL? > > - You might want to touch on the impact to existing JDBC applications > that are running against MySQL, which you want to migrate to Derby. How > are *those* migrated, and how much or how little does this tool help > with that? If anything, just make it clear that application migration > is out of the scope of this tool. > > - What mechanism are you going to use to do the data transfer? Simple > JDBC inserts? Bulk insert? For large databases the mechanism you use > can have a large impact on performance, and may actually prevent some > databases from migrating. It would be good to have a section on > performance and what approaches you will take to improve performance and > test performance. > > - What about migration of users, groups, permissions, etc. -- is that > out of scope? > > - Perhaps it would be good to be *very* explicit about what you plan to > migrate and what you *don't* plan to migrate in this tool, and what you > recommend for users for the bits that aren't being migrated > (applications, authorizations, triggers, whatever is on that list). > > - This may not be your itch to scratch, but can you address what you're > planning to do to (or not do) to address governmental accessibility > requirements (called Section 508)? See > http://java.sun.com/products/jfc/accessibility/index.jsp. At a minimum > it would be great if you don't do anything that makes it difficult for > someone else to make the tool accessible. > > Thanks! > > David > > Ramin Moazeni wrote: > > Hello, > > > > I am a Google Summer of code participant working on the Derby > > Migration tool project. The High level design document is posted at > > http://wiki.apache.org/db-derby/MysqlDerbyMigration/DesignDocument > > > > There are 2 approaches described in the document -- one based on the use > > DatabaseMetaData class and the other is based on the use ddlUtils tool > > (http://db.apache.org/ddlutils) which is also an apache project. > > > > I would appreciate your feedback and comments > > > > Thanks much > > Ramin Moazeni >
Re: MySQL to Derby Migration Tool
Hi David, Thanks for your comments. My comments inline Thanks, Ramin On 6/27/06, David Van Couvering <[EMAIL PROTECTED]> wrote: Hi, Ramin. Great document! Very nicely done. Here are my comments: - You're a UML use case guy. Great! Wish there was more of that in software design, especially for tools. - What's the mapping from MySql YEAR to Derby? Derby does not seem to have a YEAR data type. We can probably use INTEGER insteadAny thoughts? - Can you explain a little more how DdlUtils takes care of data type mapping? ddlutils tool uses JDBC datatypes rather than SQL datatypes which are database dependent. Looking at http://db.apache.org/ddlutils/database-support.html, explains mapping between JDBC types and SQL types. For example, a TIINYINT datatype from MySQL, will be mapped to SMALLINT in Derby. I will explain this in detail in the document. - Can you explain a little more what you mean by "A possible solution would be to execute database-specific commands to capture required information." when you say DdlUtils doesn't support constraints, triggers, stored procedures, etc. In the case of MySQL, we can use the SHOW command to capture the exact string to re-create the original object. For example SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION can be used to capture the original statement used to create function/procedure. I will add these information to the document. - I guess I don't follow the application flow very well when using DdlUtils. What exactly are the steps taken by the user and/or the tool using DdlUtils? I probably need another usecase diagram for ddlutils. I will add that to the document. - What is *your* recommended approach (DatabaseMetadata vs. DdlUtils) and why? I would think a combination of ddlutils and DatabaseMetadata approach is needed to handle cases not supported by ddlutils like stroed procedures. I will add a section to the document on this. - Can you talk more to what happens if there is an error during migration at various phases. What steps does the tool take to set things right? Can you end up with a half-created database? What do you do to fix the errors and start over? I suspect migration is often a repetitive effort as you catch migration/mapping errors, fix things, and try again... This should be as painless as possible. Sure, I will add this information as well. - Can you please show the proposed syntax and other details (input files, config files, env variables, exit status) for the CLI? Ok. - Does the CLI have a "headless" mode (e.g. no interactive prompts) so you can run it in a script? Good suggestion.I was not thinking about CLI with headless mode. I will add information on this. - What is the syntax for starting the tool? Will you make use of derbyrun.jar? I will need to investigate this. - Can you cover quickly how you'll address internationalizing the GUI? Sure - Can you explain briefly how, using this architecture, you can plug in other data sources besides MySQL? Ok. - You might want to touch on the impact to existing JDBC applications that are running against MySQL, which you want to migrate to Derby. How are *those* migrated, and how much or how little does this tool help with that? If anything, just make it clear that application migration is out of the scope of this tool. Ok - What mechanism are you going to use to do the data transfer? Simple JDBC inserts? Bulk insert? For large databases the mechanism you use can have a large impact on performance, and may actually prevent some databases from migrating. It would be good to have a section on performance and what approaches you will take to improve performance and test performance. I will add a section on performance. - What about migration of users, groups, permissions, etc. -- is that out of scope? Good suggestionI did not think about this. I will add it to the docuemtn - Perhaps it would be good to be *very* explicit about what you plan to migrate and what you *don't* plan to migrate in this tool, and what you recommend for users for the bits that aren't being migrated (applications, authorizations, triggers, whatever is on that list). Ok - This may not be your itch to scratch, but can you address what you're planning to do to (or not do) to address governmental accessibility requirements (called Section 508)? See http://java.sun.com/products/jfc/accessibility/index.jsp. At a minimum it would be great if you don't do anything that makes it difficult for someone else to make the tool accessible. Ok. I will look into this. Thanks! David Ramin Moazeni wrote: > Hello, > > I am a Google Summer of code participant working on the Derby > Migration tool project. The High level design document is posted at > http://wiki.apache.org/db-derby/MysqlDerbyMigration/DesignDocument > > There are 2 approaches described in the document -- one based on the use > DatabaseMetaData class and the other is based on the
Re: MySQL to Derby Migration Tool
Great document and good comments from David. I think it would be good to follow ddlUtils approach (approach 2) if feasible. Ramin, should this approach be also discussed on ddlUtils development alias?I think you would need to handle some database objects like CHECK constraints and others in a database specific way in either approach. DatabaseMetadata doesn't provide a way and probably why ddlUtil doesn't support that either. These objects will have to be migrated by reading source database specific system tables... so your generic migration tool would probably need a source database specific plug-in... I would also like to encourage others to review the document as Ramin seems to be making good progress towards implementation.SatheeshOn 6/27/06, David Van Couvering <[EMAIL PROTECTED]> wrote: Hi, Ramin. Great document! Very nicely done.Here are my comments:- You're a UML use case guy. Great! Wish there was more of that insoftware design, especially for tools.- What's the mapping from MySql YEAR to Derby? - Can you explain a little more how DdlUtils takes care of data typemapping?- Can you explain a little more what you mean by "A possible solutionwould be to execute database-specific commands to capture required information." when you say DdlUtils doesn't support constraints,triggers, stored procedures, etc.- I guess I don't follow the application flow very well when usingDdlUtils. What exactly are the steps taken by the user and/or the tool using DdlUtils?- What is *your* recommended approach (DatabaseMetadata vs. DdlUtils)and why?- Can you talk more to what happens if there is an error duringmigration at various phases. What steps does the tool take to set things right? Can you end up with a half-created database? What do you do tofix the errors and start over? I suspect migration is often arepetitive effort as you catch migration/mapping errors, fix things, and try again... This should be as painless as possible.- Can you please show the proposed syntax and other details (inputfiles, config files, env variables, exit status) for the CLI?- Does the CLI have a "headless" mode ( e.g. no interactive prompts) soyou can run it in a script?- What is the syntax for starting the tool? Will you make use ofderbyrun.jar?- Can you cover quickly how you'll address internationalizing the GUI? - Can you explain briefly how, using this architecture, you can plug inother data sources besides MySQL?- You might want to touch on the impact to existing JDBC applicationsthat are running against MySQL, which you want to migrate to Derby. How are *those* migrated, and how much or how little does this tool helpwith that? If anything, just make it clear that application migrationis out of the scope of this tool.- What mechanism are you going to use to do the data transfer? Simple JDBC inserts? Bulk insert? For large databases the mechanism you usecan have a large impact on performance, and may actually prevent somedatabases from migrating. It would be good to have a section onperformance and what approaches you will take to improve performance and test performance.- What about migration of users, groups, permissions, etc. -- is thatout of scope?- Perhaps it would be good to be *very* explicit about what you plan tomigrate and what you *don't* plan to migrate in this tool, and what you recommend for users for the bits that aren't being migrated(applications, authorizations, triggers, whatever is on that list).- This may not be your itch to scratch, but can you address what you'replanning to do to (or not do) to address governmental accessibility requirements (called Section 508)? Seehttp://java.sun.com/products/jfc/accessibility/index.jsp. At a minimumit would be great if you don't do anything that makes it difficult for someone else to make the tool accessible.Thanks!DavidRamin Moazeni wrote:> Hello,>> I am a Google Summer of code participant working on the Derby> Migration tool project. The High level design document is posted at > http://wiki.apache.org/db-derby/MysqlDerbyMigration/DesignDocument>> There are 2 approaches described in the document -- one based on the use > DatabaseMetaData class and the other is based on the use ddlUtils tool> (http://db.apache.org/ddlutils) which is also an apache project.>> I would appreciate your feedback and comments >> Thanks much> Ramin Moazeni
Re: MySQL to Derby Migration Tool
Hi, Ramin. Great document! Very nicely done. Here are my comments: - You're a UML use case guy. Great! Wish there was more of that in software design, especially for tools. - What's the mapping from MySql YEAR to Derby? - Can you explain a little more how DdlUtils takes care of data type mapping? - Can you explain a little more what you mean by "A possible solution would be to execute database-specific commands to capture required information." when you say DdlUtils doesn't support constraints, triggers, stored procedures, etc. - I guess I don't follow the application flow very well when using DdlUtils. What exactly are the steps taken by the user and/or the tool using DdlUtils? - What is *your* recommended approach (DatabaseMetadata vs. DdlUtils) and why? - Can you talk more to what happens if there is an error during migration at various phases. What steps does the tool take to set things right? Can you end up with a half-created database? What do you do to fix the errors and start over? I suspect migration is often a repetitive effort as you catch migration/mapping errors, fix things, and try again... This should be as painless as possible. - Can you please show the proposed syntax and other details (input files, config files, env variables, exit status) for the CLI? - Does the CLI have a "headless" mode (e.g. no interactive prompts) so you can run it in a script? - What is the syntax for starting the tool? Will you make use of derbyrun.jar? - Can you cover quickly how you'll address internationalizing the GUI? - Can you explain briefly how, using this architecture, you can plug in other data sources besides MySQL? - You might want to touch on the impact to existing JDBC applications that are running against MySQL, which you want to migrate to Derby. How are *those* migrated, and how much or how little does this tool help with that? If anything, just make it clear that application migration is out of the scope of this tool. - What mechanism are you going to use to do the data transfer? Simple JDBC inserts? Bulk insert? For large databases the mechanism you use can have a large impact on performance, and may actually prevent some databases from migrating. It would be good to have a section on performance and what approaches you will take to improve performance and test performance. - What about migration of users, groups, permissions, etc. -- is that out of scope? - Perhaps it would be good to be *very* explicit about what you plan to migrate and what you *don't* plan to migrate in this tool, and what you recommend for users for the bits that aren't being migrated (applications, authorizations, triggers, whatever is on that list). - This may not be your itch to scratch, but can you address what you're planning to do to (or not do) to address governmental accessibility requirements (called Section 508)? See http://java.sun.com/products/jfc/accessibility/index.jsp. At a minimum it would be great if you don't do anything that makes it difficult for someone else to make the tool accessible. Thanks! David Ramin Moazeni wrote: Hello, I am a Google Summer of code participant working on the Derby Migration tool project. The High level design document is posted at http://wiki.apache.org/db-derby/MysqlDerbyMigration/DesignDocument There are 2 approaches described in the document -- one based on the use DatabaseMetaData class and the other is based on the use ddlUtils tool (http://db.apache.org/ddlutils) which is also an apache project. I would appreciate your feedback and comments Thanks much Ramin Moazeni
Re: MySQL to Derby Migration Tool
Thanks for your comments, Jean. I would like to add that using DatabaseMetaData, I am able to find primary keys and foreign keys using the methods getPrimaryKeys and getImportedKeys, but have not been able to find a way to find out other details of a table specified in the original create statement such as CHECK constraint. Comments are appreciated... Thanks much, Ramin Moazeni On 6/22/06, Jean T. Anderson <[EMAIL PROTECTED]> wrote: Ramin Moazeni wrote: > Hello, > > I am a Google Summer of code participant working on the Derby > Migration tool project. The High level design document is posted at > http://wiki.apache.org/db-derby/MysqlDerbyMigration/DesignDocument It's great to see you doing this! > There are 2 approaches described in the document -- one based on the use > DatabaseMetaData class and the other is based on the use ddlUtils tool > (http://db.apache.org/ddlutils) which is also an apache project. In the "Approach 2: Using DdlUtils" section of http://wiki.apache.org/db-derby/MysqlDerbyMigration/DesignDocument , there is this description: In order to use DdlUtils component to migrate the source database to the target database, following steps are suggested: * Capture DDL file from DdlUtils. * Modify the source DDL to the target DDL using Serialization and Deserialization of XML. * Fed in the new DDL file to the DdlUtils. If the intent is to use the XML DDL file generated by DdlUtils, technically there should be no need to modify it for the target database -- except that there are a few issues that would need to be watched for, such as Derby doesn't support a boolean type. At any rate, the Java app can read the database model from the XML file, or even from the "live database" (see http://db.apache.org/ddlutils/api-usage.html#Reading+the+model+from+a+live+database ), then write that database model to the target. The DdlUtils code takes care of producing the SQL required to create the tables in the new database. > Please note that the DdlUtils doesn't support non-table entities, constraints, triggers, stored procedures, etc. DdlUtils supports primary and foreign key constraints, but not check constraints. Given its limitations, your DatabaseMetaData approach might be more complete. But if you can use any pieces from DdlUtils, you should feel free to do so (with all the correct attributions, of course) since it is under the ASL 2.0. -jean
Re: MySQL to Derby Migration Tool
Ramin Moazeni wrote: > Hello, > > I am a Google Summer of code participant working on the Derby > Migration tool project. The High level design document is posted at > http://wiki.apache.org/db-derby/MysqlDerbyMigration/DesignDocument It's great to see you doing this! > There are 2 approaches described in the document -- one based on the use > DatabaseMetaData class and the other is based on the use ddlUtils tool > (http://db.apache.org/ddlutils) which is also an apache project. In the "Approach 2: Using DdlUtils" section of http://wiki.apache.org/db-derby/MysqlDerbyMigration/DesignDocument , there is this description: In order to use DdlUtils component to migrate the source database to the target database, following steps are suggested: * Capture DDL file from DdlUtils. * Modify the source DDL to the target DDL using Serialization and Deserialization of XML. * Fed in the new DDL file to the DdlUtils. If the intent is to use the XML DDL file generated by DdlUtils, technically there should be no need to modify it for the target database -- except that there are a few issues that would need to be watched for, such as Derby doesn't support a boolean type. At any rate, the Java app can read the database model from the XML file, or even from the "live database" (see http://db.apache.org/ddlutils/api-usage.html#Reading+the+model+from+a+live+database ), then write that database model to the target. The DdlUtils code takes care of producing the SQL required to create the tables in the new database. > Please note that the DdlUtils doesn’t support non-table entities, > constraints, triggers, stored procedures, etc. DdlUtils supports primary and foreign key constraints, but not check constraints. Given its limitations, your DatabaseMetaData approach might be more complete. But if you can use any pieces from DdlUtils, you should feel free to do so (with all the correct attributions, of course) since it is under the ASL 2.0. -jean
MySQL to Derby Migration Tool
Hello, I am a Google Summer of code participant working on the Derby Migration tool project. The High level design document is posted at http://wiki.apache.org/db-derby/MysqlDerbyMigration/DesignDocument There are 2 approaches described in the document -- one based on the use DatabaseMetaData class and the other is based on the use ddlUtils tool (http://db.apache.org/ddlutils) which is also an apache project. I would appreciate your feedback and comments Thanks much Ramin Moazeni
