Login Register Actian.com  

Actian Community Wiki

Navigation
Learn About
Developing With
Ingres Talk
Information
Toolbox

IngresMigrationToolSet

From Ingres Community Wiki

Jump to: navigation, search

Image:IngresMigrationToolSetLogo.png
The Ingres Migration Tool Set is a collection of tools and libraries, designed to simplify the task of migrating database schemas and procedures from competitor database products to Ingres. This community project is intended for use by individual community members and by Ingres Partners and all feedback and contributions will be gratefully received.

Beneath the following list of project links is detailed project information which we encourage you to read.


thumb frameless
  Project Home on Sourceforge.net
thumb frameless
  Project Home in the Ingres Community
thumb frameless
  Main thread in the Ingres discussion forum
thumb frameless
  Main Source Code repository and Source Code mirror on Sourceforge.net

or Browse the Source Code on Sourceforge.net

thumb frameless
  Facebook Group


Contents

Preamble

The 'Ingres Migration Tool Set' is a collection of tools and libraries, created to support you in migrating your Database Schemas from DBMS-X to the Ingres DBMS. DBMS-X means currently the following:

  • Oracle to Ingres
  • MySQL to Ingres
  • Other DBMS like MS SQL Server will follow

The Tool Set currently consists of the following Tools and Libraries:

  • Ingres Parser Collection: A collection of ANTLR based parsers. This includes for instance the 'Oracle to Ingres' Procedure parsers those are part of the Idiom project.
  • Ingres Swing Lib: This library was newly created for the 'Ingres Migration Tool Set'. It's a library of Java Swing components. It's purpose is to give all the Tools of this Tool Set an unified Look and Feel.
  • Ingres Migration Wizard: The Ingres Migration Wizard is an application which allows a step by step Database Schema Migration

The following Tools are planned:

  • Ingres Schema Optimizer: An ANTLR based tool in a Wizard style which allows to transform an Ingres SQL script to an optimized one.
  • Database Browser: A tool to browse an Ingres Database and even the Source Database's structure

Project

The following chapter explains the 'Ingres Migration Tool Set' project and it's sub projects.

Project design overview

The following diagram shows how the several sub projects do depend on each other.

Design Overview
Enlarge
Design Overview


Project organization

Roles

The following persons are currently involved in that project:

  • Management committee: Emma K. McGrattan
  • Project leader: David Maier
  • Contact person regarding the requirements: Keith Bolam
  • Developers:
    • David Maier
    • Dejan Lekic : Community member
    • Timo Wagner : Central configuration and Logging system
    • Jane Thalen : Documentation review, GUI work incl. IngresSwingLib to SWT port
    • Ian Utley: Idiom (Oracle to Ingres Procedure parsers)

You are welcome to take part in this Project.

Project Plan

TBD

Project RoadMap

Type Date Description
Appointment 01 '10 Creation of an initial project plan, make some design decisions, A&D for the Ingres Migration Wizard
Appointment 02 '10 Begin the Development of a GUI Library to support a unified Look and Feel of all the Migration Tools
Milestone by the end of Q1'10 A first working version of the 'Ingres Migration Wizard' for 'Oracle to Ingres' Migrations is available for testing
Appointment 04 '10 Test the 'Ingres Migration Wizard' and fix issues
Appointment 04 '10 Reactivate the 'Ingres Schema Optimization Wizard' project by adapting it to fit into the 'Ingres Migration Tools' requirements
Appointment 06 '10 Develop the 'Ingres Database Browser', a tool to access even Migration Source Databases
Milestone by the end of Q2 '10 The 'MySQL to Ingres' module for the 'Ingres Migration Wizard' is available
Milestone by the end of Q3 '10 The 'Ingres Schema Optimization Wizard' is available
Milestone by the end of Q3 '10 'MSSQL Server to Ingres' module for the 'Ingres Migration Wizard' is available
Milestone by the end of Q4'10 Version 1 of the 'Ingres Migration Tool Set' is available


Components


IngresMigrationWizard

Purpose

There are a few Migration related Tools available for Ingres. The purpose of the 'Ingres Migration Tool Set' is to:

  • Simplify the Database Schema Migration process
  • Unify by e.G. using a similar Look and Feel for all Migration related tools
  • Enhance by inserting and improving existing Migration related tools and libraries like Idiom

Target Audience

  • Ingres Service Engineers
  • Independent Software Vendors
  • End Users

Requirements

Must have
  • Functional requirements
    • The migration should be able from Oracle, MySQL , Sybase and MSSQL server
    • It should be possible to migrate the Database Schema from DBMS-X to Ingres
    • We want to be able to migrate all kinds of Database Objects
      • Tables (+)
      • Views (+)
      • Stored procedures (+)
      • User defined functions (?)
      • Triggers (+)
      • Indexes (?)
      • Columns (+)
      • User defined data types (?)
      • Foreign key constraints (+)
      • Default and Check constraints (+)
      • Variables (?)
    • The system must be able to migrate 1000 of database objects
  • Non functional requirements
    • Wizard styled application
    • Migrations should be able in minutes instead hours
Nice to have requirements
  • Incremental migrations
  • Version control for database Schemas
  • Load and save Migration projects
  • Hibernate preset: Specific Hibernate datatype mapping
Boundary conditions

Currently the system should only support Meta-data migration and no data migration.

Additional Developers are highly appreciated. You are welcome to attend in this project! .

The 'Perform Migration' use case

Perform Migration: Steps the user must do to perform the migration

  • Choose source database: Define a connection to the source database
    • Set JDBC connection: The JDBC connection parameters like the URL, the User name, Password and other JDBC options must be set (e.G. Autocommit enabled)
    • Associated with the JDBC connection is to choose a JDBC driver for that connection, which means basically to choose the driver file and class
  • Choose Objects to migrate: Add the objects those should be included to a list
    • Browse Objects to migrate: It must be possible to browse the objects (Will be added as part of the Database Browser Tool)
    • Search Objects to migrate: If there is a high number of them then it must be possible to find specific objects easily
  • Summarize the Object selection: The user should get returned a summary of the current selection
  • Name Objects those can not be migrated: Inform the user about objects those we can not migrate currently and so allow him to handle them manually
  • Preview Migration Result: After the migration will be manifested double check the result
    • Manually Edit: Allow to manually edit the SQL or DDL in the Ingres SQL script result
    • Browse migrated objects: A kind of Object outline should allow to edit the DDL per object
    • Search migrated objects: If there is a high number of database objects then allow to Search in the Outline view
    • Import to Druid: Allow to export the result as Druid project file to be able to edit it manually inside the Druid Database Manager (TBD)
  • Finish Migration: Handle the result of the migration
    • Save Migration result: Save an Ingres SQL script
  • Apply Migration result: Load the result to a destination database which is Ingres
    • Therefore an Ingres JDBC connection must be set
First use case diagram
Perform Migration Use Case Diagram
Enlarge
Perform Migration Use Case Diagram

The Use Case Diagram may contain references to the Druid Database Manager project Druid based Migration. We meanwhile decided not to use Druid as the backend for the 'Ingres Migration Wizard', but it is planned to extend the 'Ingres Migration Wizard' to allow it to produce Druid project files as an alternative output format.

First class digram
First class diagram
Enlarge
First class diagram

The Use Class Diagram may contain references to the Druid Database Manager project Druid based Migration. We meanwhile decided not to use Druid as the backend for the 'Ingres Migration Wizard', but it is planned to extend the 'Ingres Migration Wizard' to allow it to produce Druid project files as an alternative output format.

Documentation

Ingres Migration Wizard User Guide

The user documentation is available here: http://ingresmigtools.sourceforge.net/doc/index.html . It can be also called from the 'Ingres Migration Wizard' tool itself by using the menu: 'Help' -> 'Online help'.

Ingres Migration Wizard Developer Guide

How to develop a DBMS-X specific extraction module

To extend the 'Ingres Migration Wizard' to migrate from an additional DBMS-X to the Ingres DBMS the development of two additonal modules is required.

  • Extraction module
  • Transformation module

This section explains how to get started with the development of the extraction module.

There is already an Oracle module for the extraction and the class which implements the Oracle 'extraction module' can be found here: Orale module . As you can see it extends the abstract class Module: Abstract class Module .Such a module has to implement/override some methods. So e.G. the following one: 'abstract public void retViewMetaData(Schema schema) throws SQLException;' For that example it means that the 'retViewMetaData' method gets passed an initialized Schema object which then gets the 'Views' related attributes set.

Such an 'extraction module' is used by a database object itself to retrieve meta data information. So an object from here may have a 'retDBMSSpecificMetaData' method. An example is the Schema object .You can see that the most of the data is extracted via the JDBC's Meta Data API, but because not all information can be extracted by using this API, it then must be handled DBMS specific. Therefore the 'extraction modules' are used. In the best case, we can only develop the module and so we can keep the object's source code untouched.


What you also can see is that the object itself (e.G. the Schema object) is not aware of which module it is using:

public void retDBMSSpecificMetaData(Connection jdbConnection) throws SQLException {          
  
   Module module = ModuleFactory.createModule(jdbConnection);          
   
   module.retViewMetaData(this);         
   module.retProceduresMetaData(this);         
   module.retFunctionsMetaData(this);         
   module.retSequencesMetaData(this);      
 } 


So you can see here that an instance of a module is created by using a ModuleFactory:

   Module module = ModuleFactory.createModule(jdbConnection); 

There is an interface which is named ModuleSettings and which is used to configure the ModuleFactory. So to be able to use your 'extraction module' you should add the the static constant DBMS-X_MODULE to it. The factory then must be modified to create/return an instance of the DBMS-X module if the constant MODEL_PROP is set to it.

Note: Later we can extend the Migration Wizard to handle that configuration more dynamically, e.G. by determining the module type dependent on the given JDBC driver.

Links

The following links may be of interest:

Downloads


Screenshots


Screencasts

Events

Event When Where Who Description
Migration Tools Code Sprint June the 5th - June the 7th '10 Ingres Office, Slough (UK) Dejan Lekic, Keith Bolam, Rilson Oscar do Nascimento, David Maier Please see Implementing MySQL support for the Ingres Migration Wizard during the 2010 UK Code Sprint
UKIUA Summer conference June the 8th '10 Holiday Inn, Coram Street, London Users, Partners, Developers There is a session named 'Migration Tools for Ingres' where Ian Utley from our Professional Service division will present the Ingres Migration Wizard. Further information is avialable here: IUA Home

Contact

User:David.Maier@*** David Maier


... or subscribe to the Project's mailing list :

Subscription page Migration Tool Set mailing list


USA Property Investment | Australian Property Investment | Airless Spray

Personal tools
© 2011 Actian Corporation. All Rights Reserved