1. Home
  2. Docs
  3. Index
  4. Data Explorer
  5. Database administration

Database administration

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:

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.

Actions 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.

Settings tab

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.

» More information about table settings...

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.

» More information about remote database connections…

Export and import tables

» More information about export and import…

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?)

NOTES

  • 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.

 

Was this article helpful to you? Yes 2 No

2 Replies to “Database administration”

  1. Installed WP Data Access to siteground site.
    Entered basic table info (event_vendors)
    Click on Create table and I get a dialog with:
    Create database table “?
    Does not create indexes!

    I have entered event_vendors for the table name and added
    and id column, company column, and name column.
    Trying to see if this plugin will allow me to create a couple of tables that will
    track vendors that are registering to have a booth a given conference.
    But, I can’t get past this error.
    I have gone as far as supplied around 20 columns, specifying the column type, key, mandatory, max length, default value for the column and it is ever does create a table…Only the 1st column is ever registered. I’ve tried this at least 15 times without success. Only once did a table get created – when I prefixed it with “wp_”…but it only had the id column.

  2. Hi Jeff,

    This is just a message to inform you that the plugin will create the table, but it won’t create any indexes. In your case this does not make any difference since you have no indexes. You only have a primary key (on your id) which will be created when you create the table. Just keep in mind that you will need to create indexes separately.

    How to continue:
    Click button CREATE TABLE and click OK. The table will be created. You can check the result in the Data Explorer. After that you can use your table in a Data Project of Data Publication.

    Hope this helps! Let me know if you need more assistance.

    Best regards,
    Peter

Leave a Reply

Your email address will not be published. Required fields are marked *

1 × three =