Database tables, views and indexes can be managed from the Data Explorer main page. This page is only accessible to admin users and allows to:
- View table, view and index structures (Columns, Indexes, Foreign Keys and SQL tabs)
- Create, drop, alter, rename, copy and optimize tables and views (Actions tab)
- Manage local and remote databases (Remote database connections)
- Export and import tables
- Execute SQL script files
- Add plugin settings – hyperlinks and WordPress media library integration (Settings tab)
To get access to the administration features of a table or view, click the Manage link for the required table or view. The number of tabs shown when pressing the Manage link, depends on the selected database object type. Views for example have no Indexes tab and system views do not have the Settings tab.
The image below (click to enlarge) shows an example of the Actions tab. I presume the actions are self-explanatory. Please use the forum or leave a reply if you have any questions.
The image below (click to enlarge) shows an example of the Settings tab. This is probably one of the most powerful and underestimated features of the plugin. It allows you to add a layer around a table or view. The following settings are available:
- Add dynamic hyperlinks (add column values to your hyperlink)
- Define column labels
- Define column types (with WordPress media library integration)
- Add a table or view to your own WordPress dashboard menu
Settings defined for a table or view are available to all other plugin tools. For example, if you change the column labels of a specific table, these labels are (mostly automatically) used in projects (Data Projects) and publications (Data Publisher) as well. This allows you to manage table and views settings at one central location.
Columns, Indexes, Foreign Keys and SQL tabs
The image below (click to enlarge) shows an example of the Columns tab. The Columns, Indexes, Foreign Keys and SQL tabs are all meant to view specific characteristics of a database object and are (hopefully) self-explanatory. Please use the forum or leave a reply if you have any questions.
Remote database connections
To add a remote database connections, click the plus icon behind the database listbox at the top of the Data Explorer main page. Add the remote database connection details and test the connection (see image below – clieck to enlarge). Press save when the remote connection can be established.
Once a remote connection is established, all plugin features available for local databases are available for the remote database as well (presuming the remote account has the necessary privileges). The remote database is available through the listbox at the top of the Data Explorer main page.
Export and import tables
Execute SQL script files
Button Import data/Execute script(s) can be used to execute SQL script files. It allows DCL (grant or revoke access), DDL (e.g. create table, drop table) and DML (select, insert, update, delete) statements, presuming the connected user has the proper priveleges (works for remote databases as well).
SQL script files may contain multiple SQL statements. There is one important rule:
every SQL statement must end with a ; and a new line
If you receive a message that your SQL file was succesfully processed and you are not seeing any results, this is mostly because one or more SQL statements do not end with a ; and a new line.
If your SQL file is too long, you might not be able to upload it. Instead, you can add your SQL file to a ZIP file and upload the ZIP file.
You can also bundle multiple SQL files in a ZIP. This can be very useful, for example, if you want to copy tables, projects and/or publications from your development machine to a production environment. Be aware, that you have no influence on the order in which SQL files are processed. Make sure there are no dependies between SQL file within one ZIP file.
Be CAREFUL! Know what you do! Do what you know! If you have never written any SQL before, don’t start experimenting here. If you corrupt a WordPress table, you might need to restore your backup… (Do you have one? Right now?)
- WordPress tables are protected by default. It’s not possible to drop, truncate or rename WordPress tables.
- Not all actions on the Actions tab are available for all database objects (you cannot truncate a view for example).
- You must have ZipArchive installed to import ZIP files.