Note: This is an outdated, unmaintained, archived post. Proceed with caution.

The Views integration module released with CiviCRM as of version 2.2.8 works best if your CiviCRM and Drupal databases reside on the same server and are accessible by the same username and password. However, there are cases when this will not be so:

• Your CiviCRM and Drupal databases are on different servers
• CiviCRM resides in an external database from Drupal and you prefer (or are required) to use separate usernames and passwords

In these cases and others, there are two additional steps required to get Views integration working. The first is to add a $db_url entry in Drupal’s settings.php file. The second is to reference that URL in CiviCRM’s Views integration module.

Note: Before taking these steps, make sure you have also changed the $db_prefix variable. Instructions here:

http://andrewbrookins.com/content/problems-and-solutions-problems-views-2-civi…

And here:

http://wiki.civicrm.org/confluence/display/CRMDOC/Views2 Integration Module

Step 1: Add a $db_url entry

Normally, your settings.php file will contain a line like this:

$db_url = 'mysqli://drupal_username:drupal_password@drupal_host/drupal_database';

But if your CiviCRM database is on another server or accessed with a different username and password, Views needs the username, password and, if necessary, the host, to access it. So, change the $db_url variable to the following (with your username, password and host):

$db_url['default'] = 'mysqli://drupal_username:drupal_password@drupal_host/drupal_database';

$db_url['civicrm'] = 'mysqli://civicrm_user:civicrm_pass@civicrm_host/civicrm_database';

Note:The entry for your Drupal database should always use the ‘default’ key, as shown in my example.

Next, you will modify the Views integration module’s table definitions to point to this database URL when looking up CiviCRM data.

Step 2: Point the CiviCRM Views integration module to the CiviCRM database

CiviCRM 2.2.8′s Views integration code appears to assume that the CiviCRM database is accessible by Drupal (IE, with the username and password associated with the Drupal database, and on the same host). In this step you will change that assumption.

Once you’ve added an entry in $db_url, the ‘civicrm’ database URL is available in CiviCRM’s Views integration code. Next, modify the Views integration code to ask our ‘civicrm’ host for the data. This is done on a table-by-table basis — yay!

The relevant code is in the following file:


civicrm/drupal/modules/views/civicrm.views.inc

Open that file up and find the following array definition:

$data['civicrm_contact']['table']['base'] = array(
    'field' => 'id', // Governs the whole mozilla
    'title' => t('CiviCRM Contacts'),
    'help' => t("View displays CiviCRM Contacts, of people, organizations, etc."),
);

This is one of the arrays that Views uses to access the civicrm_contact table. You can see, looking at the rest of the code, that there is more involved; but this is the array you need to change.

Now, add the following key/value pair to the array:

'database' => 'civicrm',

The final result should be:

$data['civicrm_contact']['table']['base'] = array(
    'field' => 'id', // Governs the whole mozilla
    'title' => t('CiviCRM Contacts'),
    'help' => t("View displays CiviCRM Contacts, of people, organizations, etc."),
    'database' => 'civicrm',
);

To get it working with the rest of your data, search for the other $data['table_name']['table']['base'] array definitions in civicrm.views.inc and add the same key/value pair to them.

Then create a view accessing CiviCRM data. Voila — it should work.

Note: If you already created a view (and were getting zero results back, no doubt!), then you need to clear the views cache.

References:

http://drupal.org/node/235062
http://groups.drupal.org/node/17638#comments

One-liner to remove all empty files from a directory:

% ls -s | grep -e '^ 0' | sed 's/^...//' | xargs -n1 rm -v


Or these two, suggested by Jameson Williams on the Portland Linux/Unix Group (http://www.pdxlinux.org/):

1.

find . -empty -maxdepth 1 -delete

2.

find * -prune -empty -exec rm {}\;

The following is a quick run-down of problems you might run into using Views 2 integration with CiviCRM 2.2.3 and Drupal 6 (or higher):

Update (11/20/2011): This is an outdated, unmaintained post. Proceed with caution.

Update (12/24/09): I recently added a new issue to this list, after updating a site to Drupal 6.15: CCK fields won’t save and/or you get “Table ‘batch’ already exists” errors. See below (number 3).

1. When creating a view of CiviCRM data using the Views user interface, no data is returned.

Running the query that Views generated directly against the database returns a result, but Views comes back empty.  If you haven’t used Views 2 to access a database external to your Drupal database before, then this one might be a stumper.

Never fear!  The fix is easy.  You probably need to update the $db_prefix variable in your Drupal settings.php file. As outlined in the CiviCRM docs (http://wiki.civicrm.org/confluence/display/CRMDOC/Views2 Integration Module), you need to change this line:

$db_prefix = '';

To an array that includesallof the CiviCRM tables, including your custom tables:

$db_prefix = array(
 'default' => '',
 'civicrm_acl' => 'civicrm.',
 'civicrm_acl_cache' => 'civicrm.',
 'civicrm_acl_entity_role' => 'civicrm.',
 'civicrm_activity' => 'civicrm.',
 'civicrm_activity_assignment' => 'civicrm.',
 'civicrm_activity_target' => 'civicrm.',
 'civicrm_address' => 'civicrm.',
 'civicrm_cache' => 'civicrm.',
 'civicrm_case' => 'civicrm.',
 'civicrm_case_activity' => 'civicrm.',
 'civicrm_case_contact' => 'civicrm.',
 'civicrm_component' => 'civicrm.',
 'civicrm_contact' => 'civicrm.',
 'civicrm_contact_ar_EG' => 'civicrm.',
 'civicrm_contact_en_US' => 'civicrm.',
 'civicrm_contact_ru_RU' => 'civicrm.',
 'civicrm_contribution' => 'civicrm.',
 'civicrm_contribution_page' => 'civicrm.',
 'civicrm_contribution_page_ar_EG' => 'civicrm.',
 'civicrm_contribution_page_en_US' => 'civicrm.',
 'civicrm_contribution_page_ru_RU' => 'civicrm.',
  //  more tables .... 
);

Note: I used to set the ‘default’ array element to ‘drupal.’ but this appears to cause problems. I now use an empty string for the ‘default’ element, but if an empty string isn’t working for you, try ‘drupal.’ for that element.

Also: I only listed some of the tables in the above example. Check the CiviCRM demo site for a comprehensive listing that includes a few custom table examples: http://drupal.demo.civicrm.org/civicrm/admin/setting/uf?reset=1

You can also use that URL to check the current settings on your installation (e.g., http://your.site.org/civicrm/admin/setting/uf?reset=1).

2. You get SQL errors when trying to use custom fields in a view

At this point, you have Views integration working. Then you try to sort or return data from a custom field, and Views spits out something like the following:

user warning: Table 'drupal.civicrm_value_custom_field_2' doesn't exist query:
SELECT civicrm_event.id AS id, civicrm_event.start_date AS civicrm_event_start_date,
civicrm_value_custom_table_2.custom_value_4 AS civicrm_value_custom_table_2_custom_value_4
FROM xxxxx_civicrm.civicrm_event civicrm_event
LEFT JOIN civicrm_value_custom_table_2 civicrm_value_custom_table_2
ON civicrm_event.id = civicrm_value_custom_table_2.entity_id
LIMIT 0, 10
in /usr/local/home/username/sites/your.site.org/modules/views/includes/view.inc on line 731.

This happens if you try to access a custom data group in Views whose table hasn’t been added to the $db_prefix array (http://forum.civicrm.org/index.php/topic,8075.0.html).

Any time you add a new group of custom fields to CiviCRM, you need to add the name of the table (CiviCRM stores custom data groups as tables in its database) to the $db_prefix array in Drupal’s settings.php file. Otherwise, Views won’t be able to find any of the fields in the group.

3. CCK Fields Won’t Save and/or “user warning: Table ‘batch’ already exists” Errors

Wow, this one rocked my socks. I literally spent the past few days debugging this, and I wouldn’t have even known it was related to my $db_prefix variable if David Machota hadn’t written to inform me he’d discovered a fix.

The problem: You can’t save CCK fields (I ran into this personally) or you keep getting a “user warning: Table ‘batch’ already exists” error message (David posted an entry on Drupal.org describing this one).

The solution appears to be leaving the ‘default’ $db_prefix array element blank when you declare your prefixes in settings.php. The example in this post had previously used ‘drupal.’ as the default prefix, but this appears to cause problems (after Drupal 6.14 and possible before).

So, if you run into either of these problems, use an empty string for the ‘default’ $db_prefix element. I have updated the examples in this post to do so. Also, please drop me an email if using an empty string like this fudges your system.

In my quest for the Holy Grail of daily Internet use (IE, how to get vim-style keybindings and modular editing with everything), I finally — at 6 a.m. this morning — was delivered.

Update (11/28/2011): This post is out of date and no longer maintained.

Here are the steps I suggest as a method for recovering control of your keyboard, assuming that you prefer not to use mutt to check multiple IMAP email accounts.

The following will get you vim-style key bindings in Thunderbird and allow you to use vim — or another real text editor — to write your email.  Easier than piecing together a 700 line .muttrc file, I promise!

Step 1: Get Thunderbird Beta 2 and the muttator 0.5 extension

• Download and install Thunderbird Beta 2: www.mozillamessaging.com/en-US/thunderbird/early_releases/downloads/
• Download and install the latest nightly build of Muttator 0.5, a beta add-on that lets you control Thunderbird with vim-like keybindings: http://download.vimperator.org/muttator/nightly/
• Open the muttator*.xpi file with 7-zip (on Windows) or a compression app of your choice on Linux, then edit the file: install.rdf
• Change the number between the <maxversion></maxversion> tags to: 3.0.*
• Saveinstall.rdfback intomuttator*.xpi
• Open Thunderbird, click Tools -> Add-ons
• Click Install and select muttator’s .xpi file, then restart Firefox
• You will probably want to view the toolbar (if you want to easily use T-bird’s message search function) and the menu for preferences, so issue the following commands after hitting escape or another escape-mapped key to enter comnand mode:
• :set guioptions =Tm <Return>
• :mk! <Return>

These two commands enabled the mail toolbar and top menu, then saved that setting in your muttatorc; the command help isn’t available yet, so refer to vimperator’s docs or an earlier version of muttator for help.

Step 2: Get the External Editor extension and configure it to use vim

• Download External Editor 0.8, a Thunderbird extension that lets you edit your mail in the text editor of your choice: http://globs.org/download.php?lng=en
• Open Thunderbird, go to Tools -> Add-ons, Install and choose the exteditor*.xpi file
• Restart Firefox when directed
• Go to Tools -> Add-ons -> Extensions, select External Editor, and click Options
• Enter “vim” or “gvim” if either of those commands are available in your environment, or click Browse to tell External Editor the location of the vim binary
• Click OK, then close the Options window
• Issue the m command to write a new message if using muttator, or click the Write button
• Oddly, a required step: Click View -> Toolbars -> Customize, then drag the External Editor button onto your toolbar (even if you just want to use ^E to open vim)

You will probably have to close the window and reopen a write/reply window to see the button correctly, but you should now have a Gvim / Vim button — to edit the mail with Vim, either push the button or hit ^E.

Step 3: Enjoy the Internet Again

That should do it!  You can now use vim-like keybindings to browse the web and check your mail, and full on vim to write messages. Aren’t you glad you lived to see this day?

GNU Screen is one of my favorite applications because of its simplicity and usefulness. And, as a fairly new user, I am constantly discovering features and awesome ways to interface it with my systems.

Following are some tips and links I’ve picked up that have vastly improved my screen experience, including how to save region placement, how to use zsh to save history across multiple shell sessions, and how to use ssh-agent with screen to ease remote access.

But before we get into the meat, let’s talk vocabulary (taken from the GNU docs):

A screen session is opened when you launch GNU Screen by typing screen in a (pseudo)terminal or via a script.

A window is a shell session opened inside of screen when you begin a new screen session or manually open a window with the ^a c command (that’s Control-a [pause] c).

A region is a portion of one window that displays the contents of another window. IE, a horizontal or vertical split.

Okay, with vocab out of the way, let’s get started!

Saving Region Placement

I was lurking in #screen on Freenode one day when I picked up this handy tip. To save the placement of your vertical and horizontal window splits, just use nested screen sessions.

First, log in to a shell (via ssh, a terminal window, etc.), then open a screen session using the screen command.

Inside of screen, open three or four windows (with ^a c) — one for each of the windows you want to save the placement of. I usually open windows for irssi, htop and iptraf, for example.

Now, detach the screen with ^a d. You’re back at the shell. Verify that screen is still running with the screen -ls command, and save the ID number of the session (which appears before .[terminal].[hostname].

For example:

andrew@levin:~$ screen -ls

There are screens on:

6718.pts-0.levin (02/08/2009 08:40:36 AM) (Multi, attached)

Here, the ID of the session is 6718.

To begin nesting, open a new screen session by typing screen again. Then, use the ID of the first session to reconnect to that session from within the second session (you can verify that two sessions are now running with the screen -ls command):

andrew@levin:~$ screen -ls

There are screens on:

31333.pts-0.levin (02/08/2009 08:40:36 AM) (Multi, attached)

6718.pts-0.levin (02/03/2009 09:43:53 PM) (Multi, detached)

2 Sockets in /var/run/screen/S-andrew.

andrew@levin:~$ screen -r 6718

And there you go — you have just nested one screen session within another.

Now, you can setup your vertical and horizontal window splits, AKA regions, in the first screen session and the settings will persist in the second session until the computer shuts down, the session dies, or you readjust the splits.

Caveat: Each nested session incurs an additional a in your command sequence.

So, demonstrating the previous example, to setup your persistent splits in the nested session, you would hit: ^a a | for a vertical split or ^a a s for a horizontal split.

Or, to tab between regions in the nested session, hit: ^a a tab (as opposed to switching in the first layer, which would be ^a tab).

Finally, to swtch windows in the nested layer, hit ^a a [window number] (IE, ^a a 1) if you know the identifier for the window, or ^a a " to list all opened windows.

That’s it!

Zsh Screen = Common Shell History

How many times have I searched my shell history for a recent command only to remember that I executed it in another screen session or window?

The amazing zsh, an excellent replacement for bash, eases command reuse in screen by allowing you to share command history among all shell sessions.

The .zshrc options to do this are setopt SHARE_HISTORY and setopt APPEND_HISTORY.

For more info on this and other history-related options for your .zshrc, check out this primer and some examples at dotfiles.org. Try urukrama’s .zshrc file to start things off.

Ssh-agent Screen = Easy Remote Login

The last tip is to setup screen to work with ssh-agent, which will let you more easily connect to systems onto which you’ve installed a (password encrypted) key. MBrisby will take us out on this one: GNU screen w/ ssh-agent.

More Screen Love

For an intro to screen, see Jonathon McPherson’s excellent how-to.

For more great screen tricks (including some good ones I use every day), check out Phil Sung’s 2008 blog post.

Happy screening!

GNU Screen is one of my favorite applications because of its simplicity and usefulness. And, as a fairly new user, I am constantly discovering features and awesome ways to interface it with my systems.

Following are some tips and links I’ve picked up that have vastly improved my screen experience, including how to save region placement, how to use zsh to save history across multiple shell sessions, and how to use ssh-agent with screen to ease remote access.

But before we get into the meat, let’s talk vocabulary (taken from the GNU docs):

A screen session is opened when you launch GNU Screen by typing screen in a (pseudo)terminal or via a script.

A window is a shell session opened inside of screen when you begin a new screen session or manually open a window with the ^a c command (that’s Control-a [pause] c).

A region is a portion of one window that displays the contents of another window. IE, a horizontal or vertical split.

Okay, with vocab out of the way, let’s get started!

Saving Region Placement

I was lurking in #screen on Freenode one day when I picked up this handy tip. To save the placement of your vertical and horizontal window splits, just use nested screen sessions.

First, log in to a shell (via ssh, a terminal window, etc.), then open a screen session using the screen command.

Inside of screen, open three or four windows (with ^a c) — one for each of the windows you want to save the placement of. I usually open windows for irssi, htop and iptraf, for example.

Now, detach the screen with ^a d. You’re back at the shell. Verify that screen is still running with the screen -ls command, and save the ID number of the session (which appears before .[terminal].[hostname].

For example:

andrew@levin:~$ screen -ls

There are screens on:

6718.pts-0.levin (02/08/2009 08:40:36 AM) (Multi, attached)

Here, the ID of the session is 6718.

To begin nesting, open a new screen session by typing screen again. Then, use the ID of the first session to reconnect to that session from within the second session (you can verify that two sessions are now running with the screen -ls command):

andrew@levin:~$ screen -ls

There are screens on:

31333.pts-0.levin (02/08/2009 08:40:36 AM) (Multi, attached)

6718.pts-0.levin (02/03/2009 09:43:53 PM) (Multi, detached)

2 Sockets in /var/run/screen/S-andrew.

andrew@levin:~$ screen -r 6718

And there you go — you have just nested one screen session within another.

Now, you can setup your vertical and horizontal window splits, AKA regions, in the first screen session and the settings will persist in the second session until the computer shuts down, the session dies, or you readjust the splits.

Caveat: Each nested session incurs an additional a in your command sequence.

So, demonstrating the previous example, to setup your persistent splits in the nested session, you would hit: ^a a | for a vertical split or ^a a s for a horizontal split.

Or, to tab between regions in the nested session, hit: ^a a tab (as opposed to switching in the first layer, which would be ^a tab).

Finally, to swtch windows in the nested layer, hit ^a a [window number] (IE, ^a a 1) if you know the identifier for the window, or ^a a " to list all opened windows.

That’s it!

Zsh Screen = Common Shell History

How many times have I searched my shell history for a recent command only to remember that I executed it in another screen session or window?

The amazing zsh, an excellent replacement for bash, eases command reuse in screen by allowing you to share command history among all shell sessions.

The .zshrc options to do this are setopt SHARE_HISTORY and setopt APPEND_HISTORY.

For more info on this and other history-related options for your .zshrc, check out this primer and some examples at dotfiles.org. Try urukrama’s .zshrc file to start things off.

Ssh-agent Screen = Easy Remote Login

The last tip is to setup screen to work with ssh-agent, which will let you more easily connect to systems onto which you’ve installed a (password encrypted) key. MBrisby will take us out on this one: GNU screen w/ ssh-agent.

More Screen Love

For an intro to screen, see Jonathon McPherson’s excellent how-to.

For more great screen tricks (including some good ones I use every day), check out Phil Sung’s 2008 blog post.

Happy screening!

The upgrade to CiviCRM 2.2.3 changed the sequence of data returned by the civicrm_contact_get() function in api/v2/Contact.php. If you’ve written any code against the CiviCRM API that uses this function, then 2.2.3 will probably break it.

Errors will likely show up anywhere that you depend on the array returned by civicrm_contact_get(). You will need to rewrite these to walk through an additional level of the array, per this forum post:

http://forum.civicrm.org/index.php/topic,7884.0.html

Or pass a boolean parameter to the function to get the deprecated array structure:

But obviously — using the old structure may not be a good idea.

More info here:

http://forum.civicrm.org/index.php/topic,8171.0.html

http://www.ubercart.org/issue/11131/doesnt_work_223