Reverse Engineering

From JRapid

(Redirected from ReverseEngineering)
Jump to: navigation, search

Contents

Introduction

Legacy applications that use relational databases to persist data are very common. Updating them to current state of the art technologies is a time consuming task that rarely gets done, forcing a total rebuild from scratch.

JRapid's reverse engineering feature takes an existing database schema, analyzes it and creates the necessary entities to support the existing tables. Based on primary keys, foreign keys and column data type, the process tries to define the entities with as much detail as possible.

Keep in mind that some special attributes that define how JRapid's forms and properties behave and relate cannot be inferred from a database schema definition.


How the process works

The process inspects tables and columns and creates.

  • An entity for every table in the original schema.
  • A property for every column in a table in the original schema, setting the type attribute according to the column data type.
  • Primary Key columns define primary key properties for the entity.
  • Foreign Keys create entity-type properties that specify relationship between entities.
  • Properties are given valid names and the column attribute is used to map to the original column name.

Using reverse engineering feature

To start the Reverse engineering process, click on the Create -> DB Reverse Engineering (Beta) option.

File:db_re_1.jpg

The Reverse Engineering dialog offers two ways of providing the source database schema:

  • Connect to Public SQL Server Instance
  • Paste SQL Dump File

File:db_re_2.jpg

Paste SQL Dump File takes the text dump of a database schema.

File:db_re_3.jpg

Connect to Public SQL Server Instance takes a database connection IP and credentials.

File:db_re_4.jpg

Suppose you paste a MySQL dump like shown in the following image.

File:db_re_5.jpg

Once you click Submit the process analyzes the tables and shows a form where you may correct labels and names.

File:db_re_6.jpg

At last, when you submit this last form, the AML definition for your entities is created and a code generation is performed, producing a working application based on you database schema.

Example

The existing database has two tables, artists and albums with each having a name and the albums table having a foreign reference to the artists table.

File:albums_artists.png

Below is the mysqldump of the database containing the two tables.

-- MySQL dump 10.13  Distrib 5.5.9, for Win32 (x86)
--
-- Host: db4free.net    Database: reverse
-- ------------------------------------------------------
-- Server version	5.5.11-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `albums`
--

DROP TABLE IF EXISTS `albums`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `albums` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `artist` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `addDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `artist` (`artist`),
  CONSTRAINT `albums_ibfk_1` FOREIGN KEY (`artist`) REFERENCES `artists` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `albums`
--

LOCK TABLES `albums` WRITE;
/*!40000 ALTER TABLE `albums` DISABLE KEYS */;
/*!40000 ALTER TABLE `albums` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `artists`
--

DROP TABLE IF EXISTS `artists`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `artists` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `addDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `artists`
--

LOCK TABLES `artists` WRITE;
/*!40000 ALTER TABLE `artists` DISABLE KEYS */;
/*!40000 ALTER TABLE `artists` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2011-04-19 12:27:26

You can pull from a live database, or use the dump. For this example, I'll show the screen with the values for pulling from the live database, and the screen with the dump.

File:reverse_engineering.png

Please note that screen will display your actual password, but the example screen I have as stars. Also, yourserver.com should be changed to your particular server.

For a dump there is less information to enter. Pasting the dump contents into the window should looks as follows:

File:mysqldump.png

If your file ends with a comment and not a line of code you will get a warning that it should end with a semi-colon, but you can safely ignore the warning.

The final step is taking the discovered fields and tables and adding or updating labels so they are properly displayed. Foreign key references should be picked up automatically and linked between entities.

File:names_labels.png

JRapid will create ID values for each entity, so afterwards, I made each original ID field hidden, so they would continue to exist but would not be part of displayed fields for my entities.


See also

Personal tools