Table Wizard

The Table Wizard facilitates dealing with database tables:

• It allows surfacing any table in the Drupal default database through Views 2.
• Relationships between the tables it manages can be defined, so views combining data in the tables can be constructed.
• It performs analysis of the tables it manages, reporting on empty fields, data ranges, ranges of string lengths, etc.
• It provides an API for other modules to views-enable their tables.
• It provides an API for importing data into tables in the Drupal default database (automatically doing the views integration above).
• It is bundled with an implementation of this API, for importing comma- and tab-delimited files.

Applications of Table Wizard include:

• Module developers can use Table Wizard to views-enable their data tables without writing their own Views hook.
• Developers and administrators can use Table Wizard to create views of all tables in the database, whether or not their creators provided Views integration.
• Users could upload spreadsheets and apply Views to them.

Installation

Table Wizard requires the Schema and Views modules. In addition, you may need to install the following patches to support external tables (check to see if these patches are in the Schema and Views versions you have installed):

Schema 1.3 or earlier: http://drupal.org/node/411538
Views 2.3 or earlier: http://drupal.org/node/380560

For versions of Views up through at least 2.5, you need the patch at this location to be able to create custom views for tables with more than 32 characters in their name:

http://drupal.org/node/437070

Basic Usage

To add existing tables in your Drupal default database to the Table Wizard, go to Content management => Table Wizard, expand Add existing tables, and choose one or more tables to add. Once added, a table can be used as the base for a view, and a default view of the table is provided (click on the table name in the list). The Analyze page for a table gives you information on the contents of the table (identifying empty fields,
data ranges, etc.) and allows you to identify fields which can be used to join to other tables. You can Reanalyze tables if their contents have changed.

Go to Content management => Table Wizard => Relationships to create relationships among the tables you've added to Table Wizard. When you create a relationships between, say, ExampleTbl1.key and ExampleTbl2.key, then in a view containing ExampleTbl1 you can add a relationship to ExampleTbl2, and bring fields from that table into the view.

External comma-separated and tab-delimited files can be imported into the default Drupal database using the provided tw_import_delimited module. After enabling this module,
the Table Wizard page has an additional fieldset, Upload delimited files. When you import a file, it is automatically added to the Table Wizard and analyzed.

External Tables

Table Wizard is also capable of managing tables which are in a different database from your default Drupal installation. The external database needs to be defined in settings.php:

$db_url['default'] = 'mysqli://root:pass1@localhost/drupaldb';
$db_url['example'] =   'mysqli://root:pass1@localhost/otherdb';

$db_url['default'] defines your Drupal database (i.e., this is what you are most likely already assigning to $db_url). 'example' represents another connection, and the text you use in place of 'example' will appear in the Table Wizard pages to qualify tables as coming from the external database.

Once you do this, the Available tables list on the Table Wizard page will also show tables in the external database, in a separate select list. At this time, although Table Wizard does not prevent you from creating relationships across databases, the Views module does not support this.

Table prefixing

If you use table prefixing (http://drupal.org/node/2622), to make tables that are in the same physical database as your Drupal installation but are not part of it (i.e., are not prefixed) available to Table Wizard, you need to add the database as if it were external:

$db_url['default'] = 'mysqli://root:pass1@localhost/drupaldb';
$db_url['defaultnotdrupal'] =   'mysqli://root:pass1@localhost/drupaldb';

When using external tables in a table prefix environment, Drupal will prefix the external tables by default, which in most cases is not what you want. To get around this, you can set empty prefixes in settings.php for each external table (e.g. externaltbl1, externaltbl2) you are using:

$db_prefix = array(
  'default' => 'pf1_',
  'externaltbl1' => '',
  'externaltbl2' => '',
);