Generic Database Explorer

1. Generic Database Explorer Xunyang Shen Demetrios Dimatos Madhujith Hapuarachchi

2. Project Overview • Original version of BDAP • Homepage with description • Generic Database Explorer • Enhancements • Pending Queue • User Cached Results • Baby Databases • Team work • Do work individually with interdependency • SVN, Weekly meeting, testing after mergence

3. Control Structure

4. Baby Databases Basic idea: to generate a smaller database characterizing the full database so that queries can be run on it very fast. Approach: Load/configure a database Generate histogram info Generate baby database Statistic Information Latest Interface

5. Baby Database Development Process Design document Difficulties Algorithm to generate baby databases; The major logic path of query.php; The pending queue has some dependencies on baby databases; Easy to trig conflicts in SVN, since everybody needs to revise query.php & function.lib.php; Testing is slow for big databases.

6. Baby Database Code Estimate total code lines: 850 Files created or changed: configDB.php setDB.php loadDB.php viewStats.php generateBDB.php babyDB.php query.php function.lib.php

7. Pending Queue Previous State : In the previous version of software, once a query was submitted to the database, it was run to completion and no other queries can even be submitted from the page. Enhanced State: Each submitted query is stored in a data structure that acts as a FIFO queue. The users no longer have to wait for a query to end before they can submit another.

8. Pending Queue Development Process • Attempting the run the queries on the database while collecting the submitted queries tp a queue using the single control point was difficult. This was solved by extracting the query execution part in to independent PHP script that infinitely ran as a background process, initiated by the administrator. . • Estimating the time for each query that would be run on the full database was obtained by manipulating the sizes of the full database, the baby database and the time consumed for querying the baby database. • Coding while learning PHP and MySql, presented difficulties but COSI books, google and friends were always a savior.

9. Pending Queue Code • query.php • function.lib.php (All functions of BDAP) • Execute_query.php (Independent PHP query) • display_pending_queue.php (Global Pending Queue) • display_personal_queue.php (User Pending Queue)

10. Implemented Features and Restrictions • Once a query has been selected to be sent to the full database, it will arrive at the pending queue. Unless the exact query is not already present in the queue table, it will wait to be executed. • Links in the navigation bar will display the pending queue for all users as well as all the queue for the current logged in user. • The users may delete their queries from their pending queue.

11. User Cached Queries • Store all user queries • Every submitted query after running on the baby database is stored in the user cache. • Allows for users to share cached queries • Fast retrieval • Queries stored as serialized data • Recreate the table from the serialized data

12. User Cached Queries • Manageable cache size • Administrator can specify each user's maximum cache size. • Automated garbage collection occurs when a user has used more then their space allowance. Deletion of the oldest viewed query. • View and delete cached queries • Users can view only their own cached queries as well as delete them.

13. User Cache Development Process

14. Using Version Control • Managing files • Occasional conflicts • SVN merging of source code • Rollback • Subversion

15. The End.