Summer Of Code with phpMyAdmin

I am excited to say that I will be working with phpMyAdmin this summer on the project ‘Enhancements collection for phpMyAdmin’. Here is the detailed project proposal including timeline.

Synopsis

The project aims at enhancing phpMyAdmin by implementing several features. The following are github links to respective issues:

Benefits to users

  1. New UI for Multi-table query generator: Currently, we have two ways of building multi-table queries in order to reduce user effort while querying multiple tables – one is designer (db_designer.php) and the other is a form-based query builder (db_qbe.php) which is redundant. The new UI replaces these two interfaces. This UI helps in reducing the learning curve for users who are new to managing databases and also to make multi-table querying faster and easier.
  2. Default options for transformations: While browsing a table, the way in which a user interacts with output is formatted by PMA to visualize output in an intuitive way (for eg. showing formatted JSON). These transformations can be set and edited in tbl_structure.php page. These transformations accept parameters (for eg. what is the length of substring to be shown if col value is a large string). The defaults for these parameters are hard-coded in PMA. It will be better if we enable user to configure these defaults, preferably in page related settings modal (also in configuration directives if necessary).
  3. Allow designer to show other Databases: Adding foreign key constraint from ‘Relation view’ has option of choosing a different database. Right now, users cannot create foreign key constraints with other databases using designer. This task involves showing tables from other databases and allowing to add foreign key constraints.
  4. Google Authenticator: Two factor authentication provides an additional layer of security and makes it harder for attackers to gain access to user accounts. We have also had some users complain that the only weak spot in their environments is access to phpMyAdmin which does not support two-factor authentication.
  5. Consolidate tablesorter libraries: Remove redundant libraries being used for sorting tables. From this task, there is no benefit directly reflecting to users, except loading one less library. This will help us (developers) to maintain sanity of the code.
  6. Facilitate drag and drop of columns between tables: Users will be able to copy columns across tables which they cannot do now. Example case: Engines like InnoDB allow only 64 columns to be indexed in a table due to which users are forced to split tables. Such users will find it very helpful to be able move columns across tables.
  7. Setup improvements: Many potentially very useful features like auto-update are not implemented right now as it would require filesystem access. With this solution in place, we can add features like Editing configuration file, Auto-update PMA installation, Theme downloader etc.

Project Details

The following is a more detailed description of the features which will be implemented:

  1. New UI for Multi-table query generator:
    • Right now, after result of submitted query is fetched, a lot of space is occupied on top with various options pushing the actual builder down (image). This is unfriendly as it is common to want to modify the query soon. The new interface will ensure that builder itself will take much less amount of space on top leaving room for result to be shown below builder.
    • Inherit most of the functionality provided by both of the existing interfaces.
    • I have made a mockup of what I planned to implement. Criteria will be entered as free text (we can provide interface for basic criteria like ‘AND’, ‘OR’ etc.)
    • I also have a plan to add additional filters after results are displayed, as reflected in the mockup referred above.
  2. Default options for transformations: We currently have pagewise settings and a setup script where configuration options are available. A form for ‘Default options for transformations’ will be added in one (or both) of these places. All the transformation plugins are in $PMA_HOME/libraries/plugins/transformations/ directory. Results are created by $PMA_HOME/libraries/DisplayResults.php where applyTransformation function is called. This function uses hard-coded defaults (eg: substring-transformation ). We have to:
    • Replace these hard-coded parameters with parameters obtained from $cfg. These parameters can either be set in page related settings or in configuration options in standalone settings page.
    • The existing hard-coded defaults will be added to config.defaults.php.
  3. Allow designer to show other Databases: As discussed on the issue on Github, adding all databases by default to the designer doesn’t make sense. A better suggested UI to reduce cluttering would be:
    • To include all the tables where foreign key constraints of current database are pointing.
    • Provide interface to add more tables.
  4. Google Authenticator:
    • Users will have an interface (a separate page) which they can access through settings page to set up 2FA.
    • We will be generating a QR code which a user can scan through Google Authenticator app (or any 2FA app) and enter the code (TOTP). If this is successful, we store the seed for the user in a database. We will be using this seed to verify code (TOTP) entered by user at every login attempt. A good option here is to use an existing library like TwoFactorAuth (MIT license).
    • We have to decide what happens if a user loses access to his/her Google Authenticator app. One option here is to follow what github does. Github provides recovery codes when you set up 2FA and these can be used instead if you lost your device or app. You can read about it here.
  5. Consolidate tablesorter libraries:
    • Identify the places where the two libraries (jquery.sortableTable.js and jquery.tablesorter.js) are used. Doing a simple grep, I was able to see that tablesorter is used much more than sortableTable (44 times vs 14 times).
    • Refactor the code base in those areas so that finally only one library is used. I am assuming I have to replace those 14 instances of sortableTable are to be replaced (other way is also fine if we decide that tablesorter is better). They also have decent documentation available.
  6. Facilitate drag and drop of columns between tables: In the tbl_structure.php page, an option will be added to append the columns from other table clicking on which displays list of tables and columns in selected table. User will be able to drag a particular column from this and add it to current table. This has issues like mismatch of no of rows in the column being added and current table. This needs to be further discussed.
  7. Setup improvements:
    • We will be adding a special option where users are requested for credentials for setting up these additional features. In future, these features can only be accessed by using these credentials.
    • The features which will be added include editing configuration file (config.inc.php), an option to update PMA to latest stable release (or re-install current version), PMA configuration storage setup (files in $PMA_HOME/sql/ directory).
    • Since these features affect only server side filesystem, having write access on $PMA_HOME/ directory to server should suffice.

The exact flow for any of the above features is subject to change on discussion with mentor and community during implementation. All the implemented features will be clearly documented. As mentioned in the timeline below, if time permits I will be taking up more feature requests and bugs.

Deliverables

  • New UI for Multi-table query generator: A new interface for multi-table queries which replaces the two existing interfaces.
  • Default options for transformations: A configuration option (a form) where default options for transformation can be configured.
  • Allow designer to show other Databases: An interface to enable users to add other tables (tables from other databases) to designer.
  • Google Authenticator: An optional two-factor-authentication while logging in.
  • Consolidate tablesorter libraries: Removed redundant libraries used for sorting tables.
  • Facilitate drag and drop of columns between tables: Added functionality to drag and drop columns between tables
  • Setup improvements: An interface to add features which require filesystem access such as PMA installation updater, PMA configuration storage setup, editing configuration file etc. I will try to implement as many of these features as possible.

Project Schedule

Span

Task

May 4th – May 29th

  • Bonding with community.
  • Understand the code base clearly.
  • Work on some feature requests and bugs.
  • Discuss and clear the ambiguities and implementation details.

May 30th – Jun 5th

  • New UI for Multi-table query generator.
    • Formalize the exact UI design.
    • Create and finalize UI.

Jun 6th – Jun 15th

  • New UI for Multi-table query generator.
    • Complete UI with backend.
    • Get the code reviewed.

Jun 16th – Jun 20th

  • Implement Default options for transformations.
    • Formalize the interface for configuration.
    • Implement the interface and code-review.

Jun 21st – Jun 24th

  • Implement showing tables from other databases in designer.
    • Formulate and design the UI which allows this functionality.

Jun 25th – Jun 29th

  • Submitting work for phase-1 evaluations.
  • Make up week.
  • Fix any bugs/issues in the work till now.

Jun 30th

  • Phase-1 evaluations.

Jul 1st – Jul 7th

  • Implement showing tables from other databases in designer.
    • Complete the backend part.
    • Get the code reviewed.

Jul 8th – Jul 13th

  • Add support for 2FA.

Jul 14th – Jul 20th

  • Remove redundant libraries used for sorting tables.
    • Identify where both libraries are used.
    • Refactor the code to ensure only one library is used.
    • Get the code reviewed.

Jul 21st – Jul 27th

  • Make-up week.
  • Fix any bugs/issues in the work till now.
  • Submit work for phase-2 evaluations

Jul 28th

  • Phase-2 evaluations

Jul 29th – Aug 4th

  • Setup improvements
    • Credentials setting up
    • Editing configuration file

Aug 5th – Aug 13th

  • Setup improvements
    • Other features such as PMA updater, Theme downloader, Configuration storage editor

Aug 14th – Aug 22nd

  • Facilitate drag and drop of columns across tables
    • Discuss and finalize the UI design.
    • Implement the finalized design.
    • Code-review.

Aug 23rd – Aug 28th

  • Make up week.
  • Wrap up the work.
  • Fix any bugs and do required documentation.
  • Work on more features if time permits.

Aug 29th

  • Final evaluation.

Time

I will be able to spend up to 30-40 hours a week on average.

Bio

I have been programming since my 11th standard (2011). I open-source most of my assignments and course projects which can be found on my github profile. The following are some of the relevant courses I have completed:

  • Computer Programming
  • Data Structures
  • Algorithms
  • Database Systems
  • Structured Systems Analysis and Design
  • IT workshop

I was Teaching Assistant for ITWS course at my university for two semesters which involves teaching HTML, CSS, JS, Introduction to PHP, encryption, git etc.

I am familiar with the technologies used by phpMyAdmin project, namely PHP, MySQL, JS, jQuery, HTML and CSS.

My other interests include NLP, Machine Learning and Research.

Blog

https://raghuramvadapalli.wordpress.com/ I will be continuing in this blog.

Open-source contributions

Other than phpMyAdmin, I contributed to an organization called Catrobat. My other open-source work can be found on my github.

phpMyAdmin contributions

I’ve contributed to phpMyAdmin for some months in the past. I’ve fixed few bugs and also added some features. These are my contributions.

Favorite phpMyAdmin feature

I find Navigation tree and autocomplete most useful. The one I liked the most is normalizing databases.

phpMyAdmin improvement

I feel the layout of phpMyAdmin can be further improved. For example, It can be centrally aligned (vaguely speaking) so that interface experience remains consistent with varying monitor resolutions. I found that this issue addresses whatever I am saying. I would really like to see this being taken up.

Advertisements

3 thoughts on “Summer Of Code with phpMyAdmin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s