Schema Analyzer and Data Pipeline Tool for Application Consolidation

ABSTRACT:

Today's enterprise applications deal with rich data formats such as relational databases,
spreadsheets and flat files. Traditionally, and naturally, people use various tools for data
querying, comparing, processing and transferring. JDBC (Java Database Connectivity) is a
standard Java API for SQL database accesses. What does JDBC scripting mean? It means using
JDBC for manipulating SQL databases in a way comparable to and beyond what native database
clients do. JDBC scripting is an attractive, practical matter, because SQL databases are and will
be the dominant data sources in any enterprise system. It is also important to note that all major
RDBMS (relational database management system) vendors provide pure-Java JDBC drivers for
their own products, so a capable JDBC scripting tool can easily take advantage of that capability.

All you need is a JDBC URL and the JDBC driver jar file in the class path. You can
connect to multiple databases simultaneously for multi source, heterogeneous data processing.
Using this project, you can import and export data between multiple heterogeneous
sources using DB-based architecture. The project helps to compare, copy data from one database
to another. The project is a graphical design environment for creating and executing complex
sets of data transformations and workflows, define a data source, source database, target
database, preparation for moving data to a data warehouse.The wizards allow the user to create
packages interactively that can be used to export and transform heterogeneous data using Java
APIs, and copy schema and data between relational databases.

Existing System :

When a database is being designed and developed, changes are always made and tested in
the development database first and then transferred to the product database when the product is
released. For the system to work correctly, you have to ensure the complete schema
synchronization for these two databases. To do it, you
must not miss a single change and have to carefully monitor and save all scripts, trace links
between objects, etc.
When you do it all manually, you have to spend a great deal of time and any errors
occurring during the transfer may lead to errors in the work of the system and a long debugging
process. The proposed solution is a program which compares and synchronizes the schemas of
different databases no matter how complex their schemas.Thus, the project will save a lot of time
during the synchronization process and allows you to focus on the mainthing - design and
development-by taking the tiring routine upon itself.

Key Disadvantages of Existing System :

· It is difficult to reach correct solution and results.

· Installing, configuring and launching of more tools.
· Long Debugging process.
· Doing manually leads to great deal of time. There is a consistent amount of delay in tracing
the links beteen objects and monitor the scripts.
· It deals with homogeneous data sources only and doesn't support multiple platforms.
· Efficiency: Inefficient when it comes to multiple heterogeneous databases.

Proposed System:

The proposed project is a powerful easy-to-use ODBC/ pure driver based tool for
comparison of heterogeneous databases, which can be used by DBAs, developers and testers.
The project is to be designed with high performance and friendly user interface, combined with
rich functionality and wide features.The project is intended for use in a development
environment, wherever there’s a need for changes made to a local
database to be pushed to a live database on a remote server. Normally, it is a very hard and long
process, which forces users to trawl through database schemas or search through data tables,
completing the process by generating the required migration scripts etc., manually. The project
will automate this process. The project offers
a full spectrum of features for schemas, tables and data comparison of two teratogenic or
homogenizes databases.

The project is a powerful database utility that allows users to compare any two databases
and report the structural and/or data differences between them. It then enables to synchronize the
differences. The comparison includes the following - New Tables Added, Table Columns Added,
and Column Type Added.

The project supports any ODBC compliant or pure java driver based drivers which
include all the mainstream databases including MS SQL Server, Oracle, point base, Access etc.
The project has a Query Analyzer which allows to select particular columns and filter specific
records based on where clause and generates sql
statements dynamically. The Pipeline option gives you the ability to quickly reproduce data
within a database, across databases, or even across DBMS.

Project Architecture:
 The Figure shows the architecture of the whole system “Schema Analyzer and Data
Pipeline Tool for Application Consolidation”. It clearly shows the various components involved
in the working of the system. The main program starts with user getting the DB Configuration
and entering or editing profiles in the DB Setup. Then the user can select Schema Comparator to
check the schemas for new tables, new columns or any change in the data types. Then
synchronization can be done with the help of Query Executor. The data can be migrated from
source to destination table by using the data pipeline tool. It can be done in any one of the three
ways. Then the user can also generate any query dynamically. He can search and display the
results by sorting them.
For convenience, the architecture of the project can be subdivided into the following:

The GUI design and configuration involves the creation of GUI needed for the project.

The Setup module is used for: Database Connection Information and Profile creation and
modification.

The Database Connection Information is used to configure the initial connections.

The Profile Creation and modification is used for the following purposes Creating new
profile, updating existing profile, checking the connection and deleting the existing
profiles.

The Schema Comparison wizard is used to compare the selected schemas.

Three options are provided – New table(s) added, New column(s) added, Column data
types altered.

The data pipeline wizard is used to pipeline data from the selected source table to the
destination table.

Here three options are provided – Refresh-Delete / Insert rows,Append –Insert rows and
Update-Update/Insert rows.

Sql statements are dynamically generated based on the user’s selection and the SQL
query is executed and the result set is populated to the user.

Advantages :

• Supports DSN based connections and pure java driver connections.

• Detailed comparison of source and target databases.
• Creates new tables if not contained in target schema.
• Data Pipeline facility incorporated to move data between different databases.
• Query Analyzer - Dynamic Query Generation facility.
• User settings stored for efficient re-use; Easy-to-use interface.
• Fast and Reliable; Supports Windows 98, NT, XP, 2000, 2003.
Project Modules:
GUI Designing using Swings, Configuration & Setup, JDBC Connection, Schema Comparison
Wizard, Data Pipeline Wizard, Dynamic SQL Query Generation, Table Relational Viewer, Auto
Execution of Database Schema.

Tools/ Technology :

Java Swings, JDBC, MySQL