Understanding database structure for auto-migration

Question

I am trying to come up with a good way to get this handed down Drupal project to work as smooth as possible. Ideally, my goal is to do changes local, and when that looks okay, I should be able to git push this to the server. Earlier I've made myself able to push structure changes through git hooks/post-receive, CodeIgniter Migrate and some bash scripts. This was worked out really neat, and I'm interested in similiar solutions for my Drupal project.

Ideally, I would like to upon git push have my php script generate a .sql file, send it to the server and let the server execute it so that it replaces live tables as ideal. Since our main page will handle products, orders and such that can't be overwritten, I can't simply just clone the database.

The thing I'm mostly puzzled out is, how do I tell what tables do what in my Drupal database? I have around 300 tables, and am not fully sure how dependent the tables are of each other, which that handles structure changes such as enabling and disabling modules and so on. Does anyone share an insight regarding this?

Maybe there's some easy solution for what I'm after that I've missed?

Answer 1

You can skip migrating the following tables:

• accesslog
• cache_*
• sessions
• watchdog

All the above contains material that you generally don't want to migate across sites.

The contents of the tables for built-in search will be regeneated by cron if you don't migrate them, but it may take up a lot of resouces if you've a lot content. But if this is not a concern, you can also skip migrating:

• search_*

As for table names, Drupal relies heavily on namespace dicipline to avoid collisions. So a module named foobar should only create tables with a name that starts with foobar_ (or just foobar if it only creates a single table). In other words, you can tell what tables in the database that "belongs" to what module by their name. This goes for both core modules and for regular contributed modules. As for custom modules (i.e. modules that is managed outside the Drupal.org framework, you may find exceptions to this naming convention - but only if they're written by bozos :-).

If you look at your database, you may discover a lot of tables with names starting with field_. These are of course from the (core) Field module, but I mention these specifically because these are the tables used for custom content types that are created using "Fields in core" (a key Drupal feature).

As for interdependencies between tables, looking at the fields in the tables that belong to the same module is usually enough. Getting familiar with the Schema API is also helpful. But to be honest: Sometimes you need to sit down and read source code.

As for creating and dropping tables, well behaved modules create datebase tables using hook_schema (found inside the module's .install-file) upon installation, and automatically drop the same tables upon un-installation.

Note that just pushing a module with git doesn't install the module. It just makes it available for installation. To install it, you either do it manually (using the administrator's GUI), or script it using Drush.

It is also useful to know that there are clear rules in the Drupal community of practice that a module should only write to its own tables. If a client module want to write to another module's table, it must do so through the module's API, not by interacting with the database. Module interdependency is tracked in the module's .info-file. This means, for instance, that you'll not be able to disable a service module a client module relies on (provided their interdependency has been correctly declared).