Instructions

WPDB Power Tool Instructions for the WPDB Tables tab

Purpose: Display all tables in the WordPress site database, backup tables, restore backed up tables and drop unneeded tables.

Functionality: Displays number of rows in tables and size of table. By clicking on the table name the table structure is displayed. By clicking on the rows the most recent 500 records are display on all tables using the first column as a primary key or index field.

Table View Options: You can select the tables you want to view by selecting the option above the table. All tables, only tables without data and only tables with data.

Table Name: By clicking on the table name the table structure will display in the upper right showing the Field name, type, allowed NULL, is a Key (primary or otherwise), the default value and anything extra.

Row Count: By clicking on the row count you will see a sample of the tables data restricted to 500 rows. The rows are typically ordered by the last 500 rows descending, depending on what the first field is. The purpose of this tab is to give you insight into the tables data, not to present all the data. That is what the Query Tool. is for. You will be able to slice and dice your data in any way imaginable with the Query Tool.

BACKUP: When you click on backup, a backup table with the data in the current table is created. Some important table structure is lost here (like auto increment, primary keys, special fields). This is simply a store for the data in the tables present state. Once you have made a backup of the table the option to restore the backup table displays as a restore table. Hit BACKUP again the last backup table you made is TRUNCATED and the backup table is loaded anew from the current tables state of data. So, in other words, at the moment you do the BACKUP you have a mirror of that table.

RESTORE: When you click on restore, the table you backed up is restored to the exact same state it was in when you backed it up. The process is TRUNCATE Table (this is a fast way of deleting all rows) then SELECT INTO the table. This ensures any primary keys and foreign keys are not lost and any AUTO INCREMENT is left alone in the state of the last insert. This tool is more useful for development and testing. Less so for creating a backup as a disaster recovery plan which this tool was not designed for.

DROP: The DROP process actually makes a copy of the CREATE TABLE sql as well as the data and still creates a backup. The original table is dropped from the mix. Removed, deleted, and no longer exists. The backup table has the text wpdrop in the table name (i.e. if the table were wp_posts the new table would be wp_wpdrop_posts). If you drop this table there is no recovery for that table or the data that was in it. So be certain you want to drop this if you click drop on the dropped table.

RESTORE This option restores a dropped table to the state it was in in when it was originally dropped. Restoring all primary and foreign keys as well. It is then taken out of the dropped status and the RESTORE DROP row is removed from the table list.

DROP: This is the final removal of the table. Once this is clicked on there is no easy recovery of this table. You will need to get the table back from a database backup if you have one available.

WPDB Power Tool Instructions for the Query Tool Tab

The Query Tool Tab provides the ability to virtually run an SQL statement to use for a WordPress site and more.
There are two tools. The Query Editor which is built on the WordPress code editor (in fact is the WordPress editor) using the WordPress SQL styling. The second tool is the SQL History tool. This tracks all of your SQL queries so you can reuse them with a simple click. You can also easily delete them as you see fit.

To use the query tool in the editor type your query. For example, type SELECT DATABASE(); then press the Control and Enter key at the same time and you should see two things happen here. In the display pane you should see the name of your WordPress database appear under the field name DATABASE() on the right side of your screen. Then under the Query Editor in the SQL History pane your query should be displayed. Now let’s do another query. Type SHOW TABLES; then press Control Enter. Now you should see a full list of the tables in your WordPress database. One of those tables should have the word posts in it. Double click on that table so that it is highlighted then press Control C (to copy the table name). Click in the Query Editor and type SELECT * FROM (and a space) and press Control and the V key to paste the table name and press Control Enter. Now you should see the contents of the WordPress posts table as well as the SELECT query you just ran added to your SQL History pane. These are the pages and posts on your website.

In the Query Editor and the SQL History panes you can Shrink the pane and expand the pane. You can also change the height and width of these panes and move them to wherever you like.

In the SQL History pane you can rerun your SQL by clicking on the SQL statement as well as delete the SQL by clicking on the Red SQL ID number.

WPDB Power Tool Instructions for the Stored Procedures Tab

The Stored Procedure tab is to make developing and testing Stored Procedures with as little difficulty as possible. It has three main tools to achieve this. The Stored Procedure Editor which is the same WordPress Editor as used on the Query Tool tab except sized for Stored Procedure editing. The second tool is the Stored Procedure log that displays when clicking on the View Log which tracks only successful CREATE, UPDATE and DROP queries. It tracks all the successful queries in order that in the event of a failed CREATE you can recover the previous stored procedure. You can think of it as a poor man’s Stored Procedure version control system. The third tool is the Stored Procedure Tester. In time, this feature will grow to have more advanced features however, at present it loads the stored procedure parameters for you then you can enter them to ensure it is working correctly. In order to really see how to use the Stored Procedure tool go through the Building a Stored Procedure tutorial followed by the Testing a Stored Procedure tutorial.