My GSoC proposal

Proposal Title:Add compatibility tests to explore behavior differences between Derby and other DBMS implementations



Student Name: Eranda Sooriyabandara



Student E-mail:070468D@gmail.com



Organization/Project: Apache Software Foundation Derby DB project



Assigned Mentor: Bryan Pendleton



Proposal Abstract:



As the name of the project spells this project is to create a compatibility test for Apache Derby for explore the behavior differences between Derby and the other DBMSs such as Oracle, SQL server, MySQL, PosgreSQL etc...



In this project I've seen there are three parts which I am mentioned here and describe in the Detailed description,



Identified the differences of the functionalities and options between the DBMSs. Also in this part I can document the differences of Apache Derby and other DBMSs. It mainly 3 parts as Bryan mentioned,
I. Check which SQL fetchers implemented in the DBMSs according to the SQL 99/2003/2008 standards.
II. Check which JDBC methods supported by DBMSs.
III. Check the Platform support and the behavior of DBMSs in each Platform.
Analyse the data collected in the first step and decide which tests must be include in the test bundle. After finalizing the tests I can start implementing the tests.
Third and the final step is to test each DBMSs using the test create a comparison between Derby and other DBMSs to see what improvements do we need to do to Derby to be more likely to satisfy the users needs.


Detailed Description:

Project:

This project is to create a compatibility test which can identified the main differences between Derby and other major DBMSs. After creating this compatibility test we can identify what main functionalities lack in Derby which different kinds of people expecting in a DBMS. As an example some people needs DBMS which can withstand overloads. When we knows about the lacking main functions in Derby we can improve them so that this can be much popular than now. For creating this compatibility test I follow up the following procedures which I summarized on the abstract.

When adding a compatibility test case we need to consider what tests do we need to add in. To do this we have to compare with the other DBMSs such as Oracle, MySQL,SQL server,Sybase etc... So my first thing on this project is to compare DBMSs. According to this I did a sample general comparison of Derby and MySQL which you can find on http://emsooriyabandara.blogspot.com/2010/04/derby-vs-mysql_02.html. Also I gather facts on Derby and MySQL SQL and I will able to create SQL comparison of Derby and MySQL soon.

Then I should analyse the data I gathered to find which tests do I need to add to the test case. When selecting tests I thought I should give priorities to functionalities which DBMS users expect from a DBMS. Also I thought to analyse other DBMSs for their specialties and to find how they achieve them. For example oracle is popular for its security which has three way to achieve them 1.Transparent data encryption 2.Network encryption 2.Strong authentication which I am willing to discuss when I am comparing DBMSs' "Access control and security".

Implementing the compatibility test need time other than method because when we come to this step we are know what do we want to test.

After implementing the test case we have to test against each DBMS. So we can collect statistics. This statistics we can use to improve Derby. This is the most important part of the project because we can directly identify the functionalities Derby does not support, create them as major issues and implement them.

This project I choose for the intern because I like testing and analysis. Also when I am converting Derby tests into Junit I familiar with the Derby SQL and Derby JDBC methods. Also I referred Database System Concepts book for one of my course module so that I have better knowledge in database concepts and can be able to use to check compatibility. The other reason is I choose this is up to today I used many database management systems and I am familiar with using them.

Time Line:

May 24-June 7- Comparing SQL vs DERBY fetchers.Also make a comparison on SQL functional availability in different DBMSs.

June 7-June 21-Implementing test cases for check availability of identified important SQL functions.

June 21-July 5- Comparing JDBC functions available in different DBMSs.

July 5-July 19-Implementation test case for check JDBC functions.

July 12-Submit midterm evaluation

July 19-August August 10-Check previously made test cases in different platforms using each DBMSs.

August 10-August 16-Publish results and conclusion.



About Me:

I am Eranda Sooriyabandara (Weerasekara Mudiyanselage Eranda Mahesh Sooriyabandara). I am currently a student of Department of Computer Science and Engineering at University of Moratuwa Sri Lanka. Currently I am under my intern period at WSO2 Lanka pvt Limited which is an Open Source company in Sri Lanka.



Additional Information:

This is my second time of applying on Google Summer of Code and in the first time I did Apache Derby test and fix convert test into JUnit project and successfully completed that. Under that I completed several test conversions and several bug fixing as follows,



as tests



DERBY-4318 convert inbetween.sql to JUnit
DERBY-4317 convert columnDefaults.sql to JUnit
DERBY-4248 convert checkConstraints.sql to JUnit
DERBY-4187 Convert altertable.sql to JUnit


as bugs



DERBY-4282 strange behavior with the "update... where current c1" in the CheckConstraintTest
DERBY-4256 allow alter table to increase the maximum size of a blob and a clob
DERBY-4244 ALTER TABLE Sanity ASSERT in add column with autocommit off


Also for my school project I did complete two other test conversions



DERBY-4424 Convert outerjoin.sql into JUnit
DERBY-4423 Convert dropTable.sql into JUnit


Also currently I am working on the bug DERBY-2785 ij "describe" built in command cannot describe a table named "run" and it's work is almost done.

Derby vs MySQL

Derby vs MySQL

This document describe about the differences about the DBMSs Apache Derby and MySQL.

1. Operating system support

OS\DBMS	Derby	MySQL
Windows	Yes	Yes
Mac OS	Yes	Yes
Linux	Yes	Yes
Unix	Yes	Yes
Amigo OS	No	Yes
Symbian	No	Yes

MySQL supports all most of the OS than the Apache Derby. The reason is Apache Derby need java to run it.

2. Fundamental fetchers

Fetcher\ DBMS	Derby	MySQL
ACID	yes	yes
Referential Integrity	yes	yes
Transactions	yes	yes
Unicode	yes	partial
Interface	SQL	SQL
DBMS\feacher	Derby	MySQL
ACID	yes	yes

ACID- Atomicity/ Consistency/ Integrity/ Durability

MYSQL partially support the Unicode and Apache Derby fully supports the Unicode. MySQL supports- UCS-2 encoding /UTF-8 encoding.

3. Limits

fetcher \DBMS	Derby	MySQL
Max DB size	?	unlimited
Max table size	?	MyISAM 256TB
Max row size	?	64KB
Max column per row	1012(5000 in view)	4096
Max blob/clob size	2,147,483,647 chars	4GB ( long text/long blob )
Max char size	254 varchar	32672
Max number size	?	64bits
Min DATE	0001-01-01	1000
Max DATE	31/12/9999	9999
Max column name size	128	64
Max number size	?	64bits

We can see here there are number of limitations of Apache Derby than MySQL. The main reason for this is the limitations of java. For example,

Because of java array size limit is 231

I. Number of chars in names are limited

II. Number of row s per table is limited

III. Number of column per table is limited

IV. Characters in data types like CHAR,VARCHAR is limited.

4. Tables and views

DBMS	Derby	MySQL
Temp table	Yes	Yes
Materialized view	No	No

Both DBMSs are not capable of creating materialized views. But materialized view is very important when using DBMS in data warehousing.

5. Indexes

DBMS	Derby	MySQL
R-/R+ tree	No	MyISAM only
Hash	No	Memory, Cluster(NDB), InnoDB tables only
Expressions	No	No
Partial	No	No
Reverse	No	No
Bitmap	No	No
GIST	No	No
GIN	No	No

6. Database capabilities

DBMS	Derby	MySQL
Union	Yes	Yes
Intersect	Yes	No
Except	Yes	No
Inner join	Yes	Yes
Outer join	Yes	Yes
Inner selects	?	Yes
Merge join	?	Yes
Blobs and Clobs	Yes	Yes
Common table expressions	No	No
Windowing function	No	No

7. Other objects

DBMS	Derby	MySQL
Data domain	No	No
Cursor	Yes	Yes
Trigger	Yes	Yes
Function	Yes	Yes
Procedure	Yes	Yes
External routing	Yes	Yes

8. Partitioning

DBMS	Derby	MySQL
Range	No	Yes
Hash	No	Yes
Composite(Range+Hash)	No	Yes
List	No	Yes
Shadow	?	?
Native Replication API	?	?

9. Data types

DBMS	Derby	MySQL
Type system	Static	Static
Integer	SMALLINT(16 bits) INT(32 bits) BIGINT(64 bits)	TINYINT(8 bits) SMALLINT(16 bit) MEDIUMINT(24 bit) INT(32 bit) BIGINT(64 int)
Floating point	FLOAT(32 bits) DOUBLE(64 bits) REAL(32 bits)	FLOAT(32 bit) DOUBLE(64 bit)
Decimal	DECIMAL NUMERIC	DECIMAL
String	CHAR VARCHAR CHARFORBITDATA LONGVAR- CHARFORBITDATA CLOB	CHAR VARCHAR TEXT
Binary	BLOB	BLOB
Date/Time	DATE TIME TIMESTAMP	DATETIME DATE TIMESTAMP YEAR
Boolean	BOOLEAN	BOOLEAN
Other	SET	ENUM SET

10. Access control

DBMS	Derby	MySQL
Native network encryption	Yes (can use with or without)	Yes (SSL with 4.0)
Brute-force protection	Yes	No
Enter price directory compatibility	Yes	No
Password complexity rules	?	No
Patch access	?	Partial No security page
Run unprivileged	Yes	Yes
Audit	?	?
Resource limit	?	?
Separation of duties between admin/ operator backup like RBAC	?	?
Security certification	No	No