Docs: Features: Ignore Regex Options

Several Skeema options provide a mechanism for ignoring some objects (tables, stored procedures, etc) based on the object’s type and a regular expression matching against the object’s name. This is useful when you wish to exclude some specific objects, or even entire types of objects, from schema management completely.

Using ignore regex options

The ignore options are all global options, meaning they affect all Skeema commands. Any matching objects will be completely disregarded by Skeema functionality, both on the database side as well in terms of CREATE statements in the filesystem *.sql files.

When specifying a regular expression value for these options, do not wrap the value in slash delimiters. For example, use ignore-schema = ^test.*, not ignore-schema = /^test.*/. As with all Skeema options, you may optionally wrap the value in single or double quotes inside a .skeema option file.

Skeema uses Golang’s regular expression engine. The ignore options perform case-sensitive name matching by default, regardless of your database’s lower_case_table_names setting. To make a match be case-insensitive, put (?i) at the beginning of the regex, e.g. --ignore-schema='(?i)^test.*' will match “TESTING”, “Test”, “test”, etc.

To match a full name, use the ^ start and $ end anchor symbols as needed. For example ignore-table = ^some_table_name$ will perform an exact-match on a full name. If you need to match multiple name patterns for the same object type, you must construct a single regular expression using the | operator, e.g. ignore-table = ^(apples|bananas)$. Attempting to supply the ignore-table option multiple times will not work properly, due to Skeema’s option precedence logic.

As with all Skeema options, you may supply the ignore options on the command-line as a temporary override, or supply them in an option file for a permanent solution. As a special case, when these options are supplied on the command-line to skeema init, the option value will be persisted into the directory’s auto-generated .skeema option file, so that subsequent commands on this schema will continue to ignore the corresponding objects names.

Differences between Skeema Community and Skeema Premium

Skeema Community Edition does not support management of views, triggers, or events, and always completely ignores these object types in both the database and filesystem. As such, the ignore-view, ignore-trigger, and ignore-event options do not exist in the Community Edition.

Skeema Premium Edition fully supports management of views, triggers, and events and includes corresponding ignore regex options. Additionally, the ignore-table option has implications for views and triggers:

• Since views share a namespace with tables, ignore-table also affects views. Any views with a name matching the ignore-table regex will be ignored. (To only ignore views and not tables, use ignore-view instead.)
• If an ignored table has any associated triggers, those triggers will be ignored automatically as well, regardless of their name. (Aside from this behavior, ignore-table does not directly match trigger names; see the separate ignore-trigger option to ignore match based on trigger name.)

Skeema Premium also includes support for seed data, with special treatment for views whose names begin with “_seed_”. Such views are handled separately and then automatically ignored in most commands, regardless of the value of ignore-view or ignore-table.

Ignoring shadow tables from OSC tools

External online schema change tools (such as pt-online-schema-change, spirit, and gh-ost) typically operate by building a “shadow table” with the new desired structure, and then renaming it in place of the old table. When using such tools, you should permanently configure ignore-table in a .skeema file to match the shadow tables. For example, configuring ignore-table=_new$ will match all tables with names ending in “_new”, ignoring the shadow tables used by the naming patterns of pt-online-schema-change and spirit.

With gh-ost, two tables are created: assuming an original table name of “foo”, by default gh-ost will create a shadow table “_foo_gho” and a changelog table “_foo_ghc”. You could simply ignore all tables beginning with an underscore using ignore-table=^_, or you could use a more targeted regular expression such as ignore-table=^_.*_gh[oc]$.

Some OSC tools can be configured to keep the “old” table around, instead of dropping it when the OSC operation completes. With such a configuration, you may wish to also ignore these old tables. For example, when using pt-online-schema-change --no-drop-old-table, you may wish to use ignore-table=_(old|new)$ to ignore both the _old and _new suffixes.

In addition to ignoring shadow tables, keep in mind that older OSC tools such as pt-online-schema-change rely on triggers to function. If you’re using pt-online-schema-change with Skeema Premium, you may wish to set ignore-trigger=^pt_osc_ in order to ignore the triggers which pt-online-schema-change automatically creates on the original table.

Ignoring entire schemas

The ignore-schema option is useful in scenarios where a database server contains multiple schemas which you need to manage with Skeema, as well as some “scratch” databases that you wish to ignore.

If you wish to track only a single schema from a database server, you should run skeema init with the --schema option on the command line, instead of using ignore-schema. For other commands, to interact with just a single schema at a time, simply cd to that schema’s subdirectory before invoking skeema; see the FAQ entry for more information.

Note that unlike other ignore options, ignore-schema does not affect skeema lint or skeema format, as these commands operate on the filesystem representation without needing to interact with the schema option.

Regardless of the ignore-schema option, Skeema always ignores certain schema names:

• system schemas (information_schema, performance_schema, sys, mysql)
• the test schema which is pre-created in some database server versions/distributions
• Skeema’s own workspace schema (which is called _skeema_tmp by default)

In a sharded environment, the ignore-schema option effectively acts as a filter against the schema option, permitting configuration of sharded and unsharded subdirectories to map different types of schemas on each database server.

Ignoring routines (procs/funcs)

Note that stored procedures and functions have separate namespaces in the database, meaning that a function and a stored procedure may have the same name in the same schema. For this reason, ignore-proc only applies to stored procedures, and ignore-func only applies to functions. If you wish to apply the same regex pattern to all routines, you must set both of these options accordingly.

Ignoring all objects of one type

To ignore all objects of one type, use a regular expression of ., which will match any object name. For example, to force Skeema to completely ignore all stored procedures, configure ignore-proc=., as this regex will match any procedure name.

This technique can be leveraged in interesting ways, such as setting ignore-table=. if you wish to use Skeema only for managing stored procedures and functions alongside a traditional migration-based tool for managing tables.

Splitting up a diff/push operation

Ignore options can also be useful as ad hoc command-line overrides to split up a large skeema push operation into multiple commands. For example, if your diff affects multiple tables and you wish to alter them one-at-a-time in a specific order, you could use --ignore-table on the command-line to temporarily ignore one or more of the tables.

This technique can also be useful if your diff affects multiple types of objects, and you wish to push different types in a specific order. For example, to push only the procs and funcs first, you could supply --ignore-table=. on the command-line to temporarily ignore all tables.

As always, you should run skeema diff with these command-line overrides first and confirm their correctness prior to using them with skeema push.