Differences between revisions 8 and 9
Revision 8 as of 2006-06-01 08:24:13
Size: 3908
Editor: RaminMoazeni
Comment:
Revision 9 as of 2009-09-20 22:12:37
Size: 3908
Editor: localhost
Comment: converted to 1.6 markup
No differences found!

[5/30/2006]

I sepnt some of my time today researching the feature differences between the Apache Derby and the MySQL databases. I created a Feature Matrix posted in the "Notes" section of the MysqlDerbyMigration page. The purpose of this Feature Matrix is to provide information about the features that the Target System can offer. This will be helpful in deciding if the target system have the necessary features needed for the Source System or not.

The Feature Matrix is grouped into two categories for now: Data Integrity and Database Object. The Data Integrity group shows Support of ACID transactions, savepoints, Foreign Key, locking or Multiversion Concurrency Control (MVCC). The Database Object group shows Advanced database objects like views, schemas, temporary tables.

A detailed version of this feature matrix as well as links to additional resources I used for creating this matrix can be found at:

http://en.wikipedia.org/wiki/Comparison_of_SQL_database_management_systems

and

http://www.osdbmigration.org:8080/osdb/data_migration

I also looked into the dump utility provided by the MySQL database: "mysqldump". This dump utility is a backup program that can be used to dump a database or a collection of databases for backup or for transferring the data. The dump contains SQL statements to create the table or populate it, or both. An example of a dump output is shown below:

{{{ -- MySQL dump 10.10 -- -- Host: localhost Database: testDB --


-- Server version 5.0.21-community-nt

/*!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 example --

DROP TABLE IF EXISTS example; CREATE TABLE example (

  • id int(11) default NULL, data varchar(100) default NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- -- Dumping data for table example --

/*!40000 ALTER TABLE example DISABLE KEYS */; LOCK TABLES example WRITE; INSERT INTO example VALUES (11,'hello'); UNLOCK TABLES; /*!40000 ALTER TABLE example ENABLE KEYS */; /*!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 */; }}}

The following command is used to create the above dump:

shell> mysqldump -u username -p passwd database_name [table] > filename.sql

Detailed information about the "mysqldump" utility can be found at:

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

Now, I need to analyze the above dump and make necessary changes to the above statements so that they can be executed on Apache Derby. Once the translation is done, the database can be migrated.

[5/31/2006]

I started off today with writing a piece of code that parses the data from the mysql dump. I then started working on finding the comparison between MySQL and Derby's datatypes such numeric types, string types, and Data and Time types. The document is posted in the "Notes" section of the MysqlDerbyMigration page.

RaminMoazeni/DailyNotes (last edited 2009-09-20 22:12:37 by localhost)