Google Summer of Code 2009 - Project Proposal
Implementing SQL Authorization Support for dblook
hiranya911 (gTalk, Yahoo, Skype)
Apache Derby is an open source relational database management system implemented in Java. It integrates well with any Java application and it is based on well known standards such as Java, JDBC and SQL. Starting from version 10.2, SQL authorization support was introduced to Derby. But some of the Derby utility tools were not properly updated to cope up with the new feature addition. The objective of this project is to add SQL authorization support to one such tool, namely dblook.
Apache Derby is an open source relational database management system implemented in Java and released under the Apache Software License 2.0. It is an Apache DB subproject with a very active community in charge of its development. Derby is known as a system which is easy to install, deploy and maintain in production environments. In addition, it is built on well known standards such as Java, JDBC and SQL. For these reasons, Derby has become one of the most popular database solutions among the developers and system engineers worldwide. Apache Derby also supports the client/server mode via its network server and network client JDBC driver which makes it suitable for distributed applications. Derby’s embedded JDBC driver enables developers to embed Apache Derby in virtually any Java application.
In addition to the Derby base engine and the JDBC drivers, Apache Derby ships with a number of tools that allow developers and system administrators to monitor, manipulate and manage a Derby instance. Among these interactive tools are;
- ij – JDBC scripting tool
- The import, export utilities – enables importing data from files to tables and exporting data from tables to files
- Database class loading utilities – allows storing application logic in a database
- sysinfo – provides information regarding a derby instance and its environment
- dblook – DDL generation utility
The dblook utility is supposed to generate a script which can be used to reproduce the schema of an existing database. Scripts generated by the current dblook implementation assign all the permissions at the end of the script, in one go. However in order to faithfully and consistently recreate some database objects such as views, dblook should be capable of generating scripts with interleaving permission grant statements and object declaration statements. This becomes a necessity because certain database objects require their creator to be a specific user with a specified set of privileges. Hence dblook should be capable of understanding these user dependencies and sorting the DDL statements it emits accordingly, so that necessary permission grant statements are generated before generating the object declaration statements. This would ensure that the execution of the DDL scripts generated by dblook will consistently recreate all the database objects and register the correct dependencies in the new copy of the database schema.
Implementing this feature requires understanding the concepts of SQL authorization (permissions and roles), Derby’s authorization aspects and system tables. Therefore I intend to spend sometime analyzing the existing dblook implementation and Derby’s authorization implementation. Once I’m equipped with the right knowledge and a good design I will start modifying the dblook implementation to support SQL authorization. I believe that implementing the logic related to understanding object-user dependencies and sorting the DDL statements is the core of this project and I hope to spend a majority of my project development time on that. During the implementation phase of the project, the necessary test cases will be developed first. I realize that currently there are no sufficient test cases to test the dblook implementation with SQL authorization enabled. I will work on filling that gap and from there onwards I will devote my time on actually implementing the required features.
Things Done So Far
- Checked out Derby trunk from the SVN
- Setup my development and testing environment
- Built Derby from source and tried out Derby regression test suites
- Read the Derby tools guide
- Currently looking at some of the existing issues in Derby (will provide patches soon)
- Test cases required to verify the dblook functionality with SQL authorization support
- dblook source code modified to have SQL authorization support (all patches checked and verified against the test cases mentioned above)
- Related documentation updates
- 15th May 2009 – Complete reading the documentation and analyzing the existing Derby code base
- 25th May 2009 – Complete developing the test cases required to test the dblook implementation with SQL authorization enabled
- 1st July 2009 – Complete implementing the object-user dependency identification logic and topological sorting of dependencies
- 6th July 2009 – Submit workings for mid-term evaluations
- 1st August 2009 – Complete integrating the SQL authorization support with the dblook implementation
- 10th August 2009 – Complete testing the modified dblook implementation and finalize documentation updates
- 17th August 2009 – Submit workings for final evaluations
I’m Hiranya Jayathilaka, a final year student from University of Moratuwa, Sri Lanka doing a major in Computer Science and Engineering. I'm very competent in technologies like Java, PHP, XML and Web services. I’m a committer for Apache Synapse and I have also made some contributions to the Apache Web Services project. I took part in Google Summer of Code 2008 where I successfully contributed to Apache Xerces2/J and I’m proud to mention that I still actively contribute to the project. Last year, I and a group of my colleagues started our own open source project MOINC. I have taken two courses on database management systems during my stay so far at the University of Moratuwa and hence I’m very familiar with the underlying concepts of database systems.
I believe that this project will be a golden opportunity for me to contribute to a world renowned open source project, make some new friends in the Apache Derby community and also sharpen my skills as a prospective software engineer.
This project will be conducted within the Apache Derby community and feedback from the community will be always valued and appreciated. I have already subscribed to the Derby mailing list and started a discussion thread. I would always try to get my code reviewed by the expert developers in the mailing list and get them involved in the design decisions.
Project idea originally described at: http://wiki.apache.org/general/SummerOfCode2009#derby-project
More information: http://issues.apache.org/jira/browse/DERBY-3877