Sometimes you know that you have to exclude database tables which follow a specific naming pattern, but there are so many that it's completely impractical going to the normal exclusion filters page and clicking on them one by one. Or you want to exclude everything which doesn't match a specific pattern (e.g. it's not part of the site's main database), but the matching set dynamically and constantly changes over time, making it impossible to create an accurate filter without lots of maintenance. Enter regular expressions. What are those regular expressions? Let's see what Wikipedia has to say on the subject:
In computing, regular expressions, also referred to as regex or regexp, provide a concise and flexible means for matching strings of text, such as particular characters, words, or patterns of characters. A regular expression is written in a formal language that can be interpreted by a regular expression processor, a program that either serves as a parser generator or examines text and identifies parts that match the provided specification. | ||
| --"Regular expression" article from Wikipedia | ||
In a nutshell, regular expressions allow you to quickly define filters which match table names based on a number of criteria. If you want a quick cheatsheet you can use, I suggest the Regular Expressions Cheat Sheet (V2) from AddedBytes.com. Some practical examples will be presented at the end of this section.
There are some special considerations experienced regular expressions users must have in mind:
-
You are supposed to specify a full regular expression, including its opening and ending separators. So
^foois invalid, but/^foo/and#^foo#are valid. -
The application supports an extension to the PCRE syntax. If you prefix the regex with an exclamation mark you negate its meaning. So
/^foo/will match all entities starting with foo, whereas!/^foo/will match all entities NOT starting with foo. -
The application stores and parses your data as raw Unicode (UTF-8), provided that your database meets the minimum requirement of site database server version. This eliminates the need to use the u suffix of regular expressions in order to reference Unicode characters.
When it comes to database table filters in particular, you have to bear in mind:
-
All tables using the configured table name prefix have their prefix stripped and replaced by the
#__placeholder. So, if your database prefix iswp_,wp_usersis internally referenced as#__users. You must take this into account when writing regex filters, as this is the name you will have to match! -
The prefix replacement is not made in Database Only backup modes (either main site database, or all databases). As a result, you have to reference the tables by their full, normal name, e.g.
wp_users. -
The examples at the end of this section apply to a full site backup scenario, where the replacement does take place.
RegEx Database Tables Exclusion
This page primarily consists of a grid view. Above the grid, you can find the Root Directory drop-down menu. The application can define filters for the site's main database or for each of the extra databases you may have defined. The default selection, Site's main database, contains all filters pertaining to the main site's database, of course. If you have defined extra databases, you can select the appropriate database from the drop-down list in order to define filters for that database.
The grid contains three columns:
- Icons column
-
You can perform the basic operations by clicking on this column's icons:
-
Trashcan. When you click it, the filter row will be removed.
-
Pencil. When you click it, the row switches to edit mode
-
Add (only on the last row). Clicking this icon adds a new row at the end of the list and switches it to edit mode. You can select the type of the newly added filter.
-
- Type
-
The filter type defines what will happen when a directory or file matches the regex filter and can be one of:
-
Exclude a table. Completely skips backing up tables whose names match the regular expression.
-
Do not backup a table's contents. Only backs up the structure of tables whose names match the regular expression, but not their contents.
-
- Filter Item
-
This is the actual regular expression you have to write.
RegEx Database Tables Exclusion - Edit Mode
When you click on the pencil or add icons, the respective row enters the edit mode. In this mode, the filter type becomes a drop-down list where you can select the type of this filter row. The filter item column also turns into an edit box so that you can enter your filter definition. The icon column now contains two different icons:
-
Floppy disk. When you click it, the changes will be saved.
-
Cancel. When you click it, any changes will be cancelled and the row will resume its previous state.
In order to make sure that your filters match the directories and/or files you meant it to, you can do so very easily. Just go back to the Control Panel and click on the Database Tables Exclusion button. The items filtered out by the regular expressions filters will be automatically highlighted in red. You can browse through the database structure to make sure that only the items you really meant are being excluded.