I began porting the dba module to Drupal 7 as an opportunity to learn more about the new database abstraction layer. In this upcoming Drupal release, our database layer is now built upon the PHP Data Objects (PDO) data-access abstraction layer, introducing new syntax and symantecs into writing queries with Drupal. A powerful new query builder makes it possible to easily write queries that run on any database type supported by Drupal, currently including MySQL, PostgreSQL and SQLite.
The dba module was always intended to be a useful tool for any database administrator, and toward that end I have recently merged in mysqlreport functionality which utilizes MySQL's server statistics to offer an overview of server health and performance. As an enhancement, I also merged in the knowledge shared in this earlier Tag1 Consulting article, so now problem areas detected in the mysqlreport output are automatically highlighted along with some useful help text offering tips on better tuning your database.
Screenshots
The following screenshot shows a sample of the output generated by the enhanced MySQL reporting built into the Drupal 7 version of the dba module:
When holding your mouse pointer over any of the text that's highlighted in red, a tooltip pops up offering further details and making helpful tuning suggestions.
With MySQL's reporting fully working, I was next interested in how difficult it would be to offer basic reporting for PostgreSQL. The dba module tries to be relatively database agnostic, however obviously every database type reports different statistics. I created a new pgsql.report.inc file, and in it pulled together a few PostgreSQL statistics, generating the following very limited PostgreSQL report as a proof of concept:
As noted in the above output, patches are welcome to help create a first class PostgreSQL report.
What's next
At this time, there are a limited number of thresholds that highlight lines in the reports and trigger tooltip help. It may be interesting to make these thresholds and tooltip help text configurable by a Drupal administrator for easier customization. This would allow database administrators to adapt the reporting to their specific databases.
It could also be helpful to be able to run reports automatically, emailing the output to database administrators either on a regular schedule, or when specific thresholds are crossed. The report could even be run automatically when visiting the Drupal admin pages, displaying a warning when thresholds have been crossed.
Beyond reporting, not all of the functionality found in the earlier dba module is currently available in the Drupal 7 version. It is currently possible to view the contents of one or more tables, a feature that works regardless of your database backend thanks to Drupal's newly improved database abstraction layer. You can also export the contents of one or more tables in multiple formats, including CSV, HTML, mysqldump, OpenOffice Spreadsheet, OpenOffice Document, Microsoft Excel, and Microsoft Word. Finally, it's possible to truncate one or more tables.
Not yet implemented is the ability to edit rows within database tables. I'm also intending to add filters, allowing you to display and edit only rows where an ID is greater than, equal to, or less than X, or where a variable starts or ends with a specific character or group of characters. It's also not yet possible to run arbitrary queries, which once implemented will be enhanced with the ability to bookmark favorite queries that you want to run repeatedly. Nor is it yet possible to import data from MySQL dumps, CSV files, or other formatted files.
Finally, the dba module currently only works with the default database defined in settings.php. Eventually it will be possible to work with any database defined in settings.php, running reports on slaves, etc.
If any of these features sound interesting to you, or you have other ideas you'd like to see, I encourage you to submit patches to the dba module issue queue. There is no active effort to backport these features to earlier Drupal releases, though as always patches are welcome.
The work on the dba module described in this article is sponsored by Tag1 Consulting, Inc. If improving the performance and scalability of Drupal websites is something you enjoy, take a look at our jobs page. We're hiring!