Application Design

The next phase in development of this project is to divide the application into discrete components and plan their functionality.

User Logins

The first major design consideration stems from the requirement that the application knows who the user is, whether they have access to the server, and what database they are working with at all times. One method to accomplish this is to store the user's login criteria and database selection at the server. This can be accomplished by creating a session in PHP that stores the user's data in a temporary file, which is accessible through session variables during the execution of each PHP script. Session variables are chosen, to provide a better level of security to the application, as opposed to putting all of the login information into a cookie and returning it to the browser. By not placing the user's password in the cookie, it is more difficult for an unauthorized user to obtain a user's credentials. Also, using session data means that the information only exists while the user is accessing the application, and it is destroyed automatically at the end of a user's session. PHP's session-handling functions provide an easy-to-implement, secure mechanism for managing a user's access to the server.

Since the first step in using the application is to validate the user against the MySQL server, a screen needs to be developed for the user to input their login credentials. This screen prompts the user for their login name, password, and hostname they are expected to access the server from. Note that normally in MySQL terms, this is the actual machine the user is working on; but for this application, this will be whatever the user enters, to allow for remote administration from any machine. This method is inherently not as secure as the normal MySQL server login verification process. The login screen can also have a dual role in that it can be used not only to validate a new application user, but also to change which username the application user is currently accessing the server as. A corresponding requirement to the login screen is one that provides an exit message when the user leaves the application and offers a link for the user to re-login to the application.

Once the user has logged in to the application, they are presented with a high-level menu of available database administration tasks. As defined in the requirements, the menu of available options includes table administration, database administration, database status monitoring, user administration, and SQL query execution.

Table Administration

Activating the table administration menu selection brings up a submenu of available options. These options include listing all tables in the selected database, describing a table, displaying the contents of the table, altering a table's structure, creating a new table, and dropping an existing table. When a user selects the List-Tables option, an HTML table is generated with the heading Tables, and then a vertical listing of all of the tables within the selected database. In the case of describing a table, the user first needs to select a particular table, and then the columns and their characteristics will be returned as an HTML table. Displaying the contents of a table requires that the user first select a table and then a select * query is run against the table with all of the rows being returned within an HTML table. The option to drop a table prompts the user for the table name, and then reports the success or failure of the operation. All of these table operations can be incorporated into a single class, referred to as the tableAdmin class. Any operation that operates on a table is implemented as a method of this class.

Database Administration

Selection of the Database Administration option from the main menu results in the display of a submenu of available options that include listing all the databases available to the user, selecting a particular database for subsequent actions, dropping an existing database, and creating a new one. If the user selects listing the databases, an HTML table is generated providing a vertical list of the databases. If the user selects a particular database, the user's session variable for the database is altered to reflect that selection, along with providing the user with a success or failure indication for the selection, If the user chooses to drop a database, they will be prompted for which database they would like to drop, and after the drop is executed, the user is informed about the success or failure of the drop. Likewise, for database creation, the user will be prompted for the name of the new database and then informed if the database creation action was successful. All of these database administration tasks can be incorporated into a PHP class referred to as the dbAdmin class. Any operation that involves an entire database (not server) is implemented as a method of this class.

Database Monitoring

The selection of database status monitoring prompts the user with a submenu of available options to perform the following tasks: show database status, show the version of MySQL that is running, display a list of current processes, and show the current server variable settings. All of these menu selections result in an HTML table being generated with the corresponding information displayed, such as a single column table with the heading Version and a row containing the MySQL server version number. All of these operations are implemented as methods of a dbMonitor class.

User Administration

Selecting User Administration from the application's main menu prompts the user with the following options: Add User, Remove User, Update User, Change User, and List Users. The Add User option queries the user for the new user's login name, password, hostname, and database name they will be allowed to access. The statement is executed at the server, and the user is given an indication of success or failure. In the case of Remove User, the application prompts for the same fields as mentioned in the Add User screen. If the application user selects Update User, they are prompted for the new password they wish to set for themselves. If they select Change User from the submenu, they are effectively taken to the application's main login screen, logged out, and asked to log back in to the application. Finally, the List Users option displays in an HTML table all of the users along with the host associated with each user. All of these operations are implemented as methods of the userAdmin class.

SQL Query Execution

Finally, selecting the SQL option from the main menu brings up a large text area for the user to input a raw SQL query that will be executed at the server. If the query is a select statement, the number of rows returned is indicated and the actual rows in the result set are displayed. If the query is not a select or show statement, the number of rows impacted by the operation is indicated. In either case, if an error resulted from the query, the user is informed of that as well. The methods that allow a user to execute a query against a database and display the results are implemented as part of the SQL class in PHP.

Instant PHP 4
Instant PHP 4
Implementation