Jun 29 2014
Jun 29

The Drupal community web site has a profile field to list "My mentors"

For example, on my profile I say I was mentored by:
  • robbiethegeek - how to appreciate Drupal awesomeness and its limitations
  • Alex UA - how to run a business providing Drupal services
  • forestmars - how to be involved in the Drupal community
  • smerrill - how to be an engineer with platform tools like Jenkins, Vagrant, Redis
  • snugug - how to make web sites responsive
  • ericduran - how to experiment with new doodads like HTML5, Android
  • zroger - how to use Drupal hooks and APIs in code

I started thinking about my dumb luck picking Drupal as a tool about 9 years ago. I was looking for a Content Management System that made sense.

I was awfully interested in a project called PAWS (PHP Automatic Web Site) -- and it's a good thing I didn't ride that horse, which was long ago put out to pasture.

A client asked me to convert his static PHP site so that he could manage the content in the include files without editing code. I built my first Drupal 4.x site, with the crazy hack of creating a node for every include, and then printing the includes/nodes inside a main node (Panels, sort of, which did not exist in Drupal then). I also customized the front end of the TinyMCE wysiwyg editor to add buttons to apply his brand's pink and blue colors. The client smoked a lot of pot, drifted away, came back a year or two later for more work -- without a database. Oh well, not the first - or last - time the db was lost by a client.

That experience convinced me that a lot could be done with Drupal that I had not been able to do without a lot of custom coding just to build the base web application. Other projects with early versions of WordPress and Mambo (predecessor to Joomla) left me unimpressed with their extensibility. I have often said since then that "WordPress is like the smaller sibling of Drupal, but Joomla is the evil cousin."

Then Earl Miles conjured up his merlinofchaos wizardry for Sony Music, creating Views and Panels and Ctools, and that was around the time that a lot of developers took notice of Drupal. I was profoundly convinced that Drupal had outgrown being a CMS enabling writers to (more or less) easily edit content without (much) coding, and had become a Content Management Framework that could perform elegant and dynamic manipulations of the content in its database.

So I had to add dumbluck to my mentors - not just for my early experiment hacking the node system, but for each solution that I was able to implement afterwards, because my choice of Drupal provided me with an extensible framework allowing complex algorithms for presentation of content, and the Drupal project improves with every contributor's enhancements.

I think I'm dumb, maybe just happy

[embedded content]

I noticed in preparing this post that some Drupal user profiles are accessible by username, eg. https://www.drupal.org/u/decibel.places and https://www.drupal.org/u/robbiethegeek, while others, like merlinofchaos and smerrill, are only accessible by their UIDs https://www.drupal.org/user/26979 and https://www.drupal.org/user/77539 respectively.

May 09 2014
May 09

We have launched FINclusion Lab Beta

a new Drupal 7 site by MIX (Microfinance Information Exchange)

FINclusion Lab provides visual analytic tools to interpret the "proliferation of data in the field of financial inclusion" by aggregating data in Tableau dashboards and Mapbox maps.

The platform "provides financial service providers, policy makers, regulators, and other development professionals the opportunity to identify problems and devise solutions for increasing financial inclusion in their countries through interactive data tools and visualizations."

The FINclusion Lab team has worked over the past two years to gather data on supply and demand for financial services at the sub-national level for a growing number of countries in Africa, Asia, and Latin America.

FINclusion Lab Beta

We are using the Drupal Tableau module, sponsored by MIX, to display Tableau data visualization dashboards via Drupal Views.

Other MIX sites with free and premium data about microfinance:

MIX Market and MIX Market Reports

coming soon: translations in French, Spanish and Russian using the Drupal i18n  module for localization.

Mar 31 2014
Mar 31

I'm working on a Drupal application that stores data in separate mysql databases, and syncs some of the data to CouchDB with node.js scripts.

The extra mysql dbs are 16+ GB and it's not practical nor necessary to keep them locally since I only want to read the latest data in local development.

Wouldn't it be cool if my local development Drupal sites can read from the remote database servers?

In some cases you can just use the connection you find in the remote site's settings.php:

'otherdb' => 'mysqli://username:[email protected]/dbname'

(note: it's a Drupal 6 site so that's why you don't see an array - I will give a Drupal 7 example below)

However, there's often a twist: I must create a SSH tunnel to connect to this particular db server.

First, you need to have configured and installed SSH keys on your local and remote machines.

Then fire up your terminal and create the SSH tunnel to forward the remote mysql port to a local port. This technique is based on information clearly presented by RevSys (quick example) and noah.org (more details).

ssh -L [local port]:[db host]:[remote port] [ssh-username]@[remote host] -N

NOTES:

  1. -N tells ssh that you don't want to run a remote command; you only want to forward ports.
  2. use a different port for your tunnel [local port] than the one you normally use for mysql; for example, if you connect to mysql locally on the default port 3306, use 3307 (or any other unused port) for your tunnel. You should use the correct [remote port] which is typically 3306, and you can see if it is different by looking at settings.php in the remote site.
  3. Keep this connection alive as long as you need to connect to the remote database.

ssh -L 3307:[db host]:3306 [ssh-username]@[remote host] -N

Then you can test your connection (in a different terminal instance):

mysql -u[dbuser] -p -P 3307 -h 127.0.0.1

Here is the connection in settings.php for Drupal 6:

What's cool is that you can mix local and remote databases. For example, I want to use a local copy of the Drupal database, which is smaller and easier to sync, and read the data from the second (and third, in my case) remote dbs.

$db_url = array(

);

You can also connect Drupal to the default remote database, but it makes sense to use a local instance for development.

And in Drupal 7:

$databases['default']['default'] = array(

  'driver' => 'mysql',

  'database' => 'local-dbname',

  'username' => 'local-dbuser',

  'password' => 'password',

  'host' => 'localhost',

  'prefix' => '',

);

$databases['otherdb']['default'] = array(

  'driver' => 'mysql',

  'database' => 'dbname',

  'username' => 'username',

  'password' => 'password',

  'host' => '127.0.0.1',

  'port' => '3307',

  'prefix' => '',

);

WARNING: 

If the db user for the remote db has all privileges, your application may alter the remote database.

Therefore, you should create a "read-only" user for the remote database which will prevent you from altering it.

THINK

Jan 29 2014
Jan 29

I had to migrate site members from a legacy site with an Oracle db to a relaunch on Drupal 7. Instead of connecting directly to Oracle, I was provided with an Excel dump which I converted to csv. Also the old site does not require members to be authenticated, so I did not have to deal with old passwords.

There are a lot of stale posts in Google results about migrating Addressfield that do not work on the latest Migrate V2 for Drupal 7. For one thing arguments in the field mapping are deprecated.

Using the Migrate module I was able to create migration classes for the users, and some other information; but I got stuck at importing the address data into the Drupal Addressfield table, because it uses subfields and requires building an array in Migrate.

I used colon notation for subfield mapping, but it was not working.

<?php
    $this
->addFieldMapping('body', 'body');
   
$this->addFieldMapping('body:summary', 'excerpt');
?>

Migrate kept throwing an error: Call to a member function import() on a non-object

But when I put drush_print_r on the line throwing the error, I could see the stdClass object arrays were there and populated with the correct data.

I decided to abandon the Addressfield migrate class and use db_insert in an existing class (class SchoolMigration) where I was already running db queries.

It worked, and although I was worried a direct db_insert would not roll back, since I registered the inserts in the process() function for class SchoolMigration, they also rolled back.

Here is my example migrate.inc

<?php

/**

 * @file migrate_users.migrate.inc

 * Our own hook implementation.

 */

/**

 * Migration classes for migrating users and user fields

 *

 * see Migrate handbook: https://drupal.org/migrate

 * and http://www.verbosity.ca/working-drupals-migrate-module

*/

/* useful functions for debugging using drush migrate commands:

drush_print_r($row);

drush_print_r($this->destinationValues);

*/    

/**

 * Implements hook_migrate_api()

 *

 * Returns 'api' => 2 for the 7.x-2.x branch of Migrate.

 * Registers the migration classes for the 7.x-2.6 branch of Migrate (including

 * 7.x-2.6+xx-dev).

 */

function migrate_users_migrate_api() {

  $api = array(

    'api' => 2,

    // Migrations can be organized into groups. The key used here will be the

    // machine name of the group, which can be used in Drush:

    //  drush migrate-import --group=MigrateMembers

    // The title is a required argument which is displayed for the group in the

    // UI. You may also have additional arguments for any other data which is

    // common to all migrations in the group.

    'groups' => array(

      'MigrateMembersGroup' => array(

        'title' => t('Import Member Users'),

      ),

    ),

    // Here we register the individual migrations. The keys (MigrateMembers_Step1,

    // etc.) are the machine names of the migrations, and the class_name

    // argument is required. The group_name is optional (defaulting to 'default')

    // but specifying it is a best practice.

    'migrations' => array(

      'MigrateMembers' => array(

        'class_name' => 'MigrateMembers',

        'group_name' => 'MigrateMembersGroup',

      ),

      'GraduationYear' => array(

        'class_name' => 'GraduationYearMigration',

        'group_name' => 'MigrateMembersGroup',

      ),

      'School' => array(

        'class_name' => 'SchoolMigration',

        'group_name' => 'MigrateMembersGroup',

      ),

      'SchoolID' => array(

        'class_name' => 'SchoolIDMigration',

        'group_name' => 'MigrateMembersGroup',

      ),

    ),

  );

  return $api;

}

/**

* Abstract class as a base for all our migration classes

*/

abstract class MigrateMembers_Basic_Migration extends Migration {

  public function __construct($arguments) {

    // Always call the parent constructor first for basic setup

    parent::__construct($arguments);

    // Avoid known line ending issue: drupal.org/node/1705850

    ini_set('auto_detect_line_endings', TRUE);

  }

}

/**

* User-only migration 

* (and creates $row (Row) with user id to key other tables in subsequent migration classes)

*

* The data file is assumed to be in

* sites/all/modules/migrate_users/data_sources/

* the columns in the csv must be in the same order left-to-right as the $columns array below

* every row in csv must have a unique id in the first column (can be ascending numbers, for example)

*/

class MigrateMembers extends MigrateMembers_Basic_Migration {

public function __construct($arguments) {

  parent::__construct($arguments);

  $this->description = t('Import an CSV-file (only "User Account"-fields)');

  $columns = array(

  // Format: ('Fieldname', t('Description'))

      0 => array('Row', t('Row number (must be unique)')),

      1 => array('Type', t('S = Student M = Member')),

      2 => array('Prefix', t('Mr. Mrs. etc')),

      3 => array('FirstName', t('First Name')),

      4 => array('MiddleName', t('MiddleName')),

      5 => array('LastName', t('Last Name')),

      6 => array('StaffNotes', t('Staff Notes')),

      7 => array('SchoolNameAffiliation', t('SchoolName Affiliation')),

      8 => array('ProfessionalAffiliation', t('Professional Affiliation ')),

      9 => array('AddressLine1', t('Address Line 1')),

      10 => array('AddressLine2', t('Address Line 2')),

      11 => array('City', t('City')),

      12 => array('State', t('State')),

      13 => array('ZipCode', t('Zip Code')),

      14 => array('Country', t('Country')),

      15 => array('TelephoneNumber', t('Telephone Number')),

      16 => array('EMAIL', t('Email')),

      17 => array('Birthday', t('Birthday')),

      18 => array('SchoolID', t('SchoolID')),

      19 => array('Descriptor', t('Descriptor')),

      20 => array('GraduationYear', t('Graduation Year')),

      21 => array('SchoolAffiliation', t('SchoolAffiliation')),

  );

  // TIP: delete ", array('header_rows' => 1)" in the next line, if the CSV-file has NO header-line

  $this->source = new MigrateSourceCSV(DRUPAL_ROOT . '/' . drupal_get_path('module', 'migrate_users') . '/data_sources/drupaluser_import.csv', $columns, array('header_rows' => 1));

  $this->destination = new MigrateDestinationUser();

  $this->map = new MigrateSQLMap($this->machineName,

  array('Row' => array( // this field is used to connect user to other tables

          'type' => 'varchar',

          'length' => 6,

          'not null' => TRUE,

          'description' => t('User\'s Member-ID') // description never used

        )

       ),

  MigrateDestinationUser::getKeySchema()

  );

  // Mapped fields

  $this->addFieldMapping('name', 'EMAIL') 

    ->defaultValue('')

    ->description(t('Name (User Account)'));

  $this->addFieldMapping('mail', 'EMAIL') 

    ->defaultValue('')

    ->description(t('Email address'));

  $this->addFieldMapping('init')

    ->defaultValue('')

    ->description(t('Email address used for initial user-account creation'));

  $this->addFieldMapping('pass', 'PASSWORD')

    ->defaultValue('asdf')

    ->description(t("User's password (plain text)"));

  $this->addFieldMapping('is_new')

    ->defaultValue(TRUE)

    ->description(t('Build the new user (0|1)'));

  $this->addFieldMapping('roles')

    ->defaultValue(6) 

    ->description(6 . t(' = "member"'));

  $this->addFieldMapping('theme')

    ->defaultValue('')

    ->description(t("User's default theme"));

  $this->addFieldMapping('signature')

    ->defaultValue('')

    ->description(t("User's signature"));

  $this->addFieldMapping('signature_format')

    ->defaultValue('filtered_html')

    ->description(t('Which filter applies to this signature'));

  $this->addFieldMapping('created')

    ->defaultValue(time())

    ->description(t('unique timestamp of user creation date'));

  $this->addFieldMapping('access')

    ->defaultValue(0)

    ->description(t('unique timestamp for previous time user accessed the site'));

  $this->addFieldMapping('login')

    ->defaultValue(0)

    ->description(t('unique timestamp for user\'s last login'));

  $this->addFieldMapping('status')

    ->defaultValue(1)

    ->description(t('Whether the user is active(1) or blocked(0)'));

  $this->addFieldMapping('timezone')

    ->defaultValue(t('Europe/London')) // 'America/Los_Angeles', 'Europe/Berlin', 'UTC', ... from drupal.org/node/714214

    ->description(t("User's time zone"));

  $this->addFieldMapping('language')

    ->defaultValue(t('en')) // e.g.: 'en', 'fr', 'de', ...

    ->description(t("User's default language"));

  $this->addFieldMapping('picture')

    ->defaultValue(0)  

    ->description(t('Avatar of the user'));

  // Other handlers

  if (module_exists('path')) {

    $this->addFieldMapping('path')

       ->defaultValue(NULL)

       ->description(t('Path alias'));

  }

  if (module_exists('pathauto')) {

    $this->addFieldMapping('pathauto')

    ->defaultValue(1) 

    ->description(t('Perform aliasing (set to 0 to prevent alias generation during migration)'));

  }

  // Unmapped destination fields

  $this->addUnmigratedDestinations(array('role_names', 'data'));

  }  

}

class GraduationYearMigration extends MigrateMembers_Basic_Migration {

  public function __construct($arguments) {

  parent::__construct($arguments);

  $this->description = 'Add data to field_data_field_graduation_year';

  $this->dependencies = array('MigrateMembers');

  $table_name = 'field_data_field_graduation_year';

  $this->source = new MigrateSourceCSV(DRUPAL_ROOT . '/' . drupal_get_path('module', 'migrate_users') . '/data_sources/drupaluser_import.csv', $columns, array('header_rows' => 1));

  $this->destination = new MigrateDestinationTable($table_name);

  $this->map = new MigrateSQLMap($this->machineName,

    array('Row' => array(

      'type' => 'int',

      'unsigned' => TRUE,

      'not null' => TRUE,

       )

     ),

    MigrateDestinationTable::getKeySchema($table_name)

    );

  // Mapped fields

  // add uid from completed MigrateMembers migration

  $this->addFieldMapping('entity_id', 'Row')->sourceMigration('MigrateMembers');

  $this->addFieldMapping('revision_id', 'Row')->sourceMigration('MigrateMembers');

  // Insert Graduation Year value

  $this->addFieldMapping('field_graduation_year_value', 'SchoolAffiliation')->sourceMigration('MigrateMembers')

    ->defaultValue(date("Y"));

  // set some defaults from the schema

  $this->addFieldMapping('delta')

    ->defaultValue("0");

  $this->addFieldMapping('entity_type')

    ->defaultValue("user");

  $this->addFieldMapping('bundle')

    ->defaultValue("user");

  $this->addFieldMapping('language')

    ->defaultValue("und");

  $this->removeFieldMapping('path');

  $this->removeFieldMapping('pathauto');

  }

}

class SchoolMigration extends MigrateMembers_Basic_Migration {

  public function __construct($arguments) {

    parent::__construct($arguments);

    $this->description = 'Add data to field_data_field_member_school';

    $this->dependencies = array('MigrateMembers');

    $table_name = 'field_data_field_member_school';

    $this->source = new MigrateSourceCSV(DRUPAL_ROOT . '/' . drupal_get_path('module', 'migrate_users') . '/data_sources/drupaluser_import.csv', $columns, array('header_rows' => 1));

    $this->destination = new MigrateDestinationTable($table_name);

    $this->map = new MigrateSQLMap($this->machineName,

      array('Row' => array(

        'type' => 'int',

        'unsigned' => TRUE,

        'not null' => TRUE,

         )

       ),

      MigrateDestinationTable::getKeySchema($table_name)

      );

    $this->addFieldMapping('entity_id', 'Row')->sourceMigration('MigrateMembers');

    $this->addFieldMapping('revision_id', 'Row')->sourceMigration('MigrateMembers');

    $this->addFieldMapping('field_member_school_tid', 'SchoolAffiliation')->sourceMigration('MigrateMembers');

    // set some defaults from the schema

    $this->addFieldMapping('delta')

      ->defaultValue("0");

    $this->addFieldMapping('entity_type')

      ->defaultValue("user");

    $this->addFieldMapping('bundle')

      ->defaultValue("user");

    $this->addFieldMapping('language')

      ->defaultValue("und");

    $this->removeFieldMapping('path');

    $this->removeFieldMapping('pathauto');

  }

  public function prepareRow($row){

    // convert 3 letter codes to ISO 3166-1 standard 2 letter codes http://en.wikipedia.org/wiki/ISO_3166-1_alpha-2#Current_codes

    // TODO:

    // 1 - check for full list of country codes in old data

    // 2 - many records with US addresses do not have a foreing country code, not the USA country code

    // 3 - What are INO SAF SWD?

    switch ($row->Country)

      {

        case 'AUS': // Austria

        $row->Country = "AT";

        break;

        case 'BEL': // Belgium

        $row->Country = "BE";

        break;

        [more mappings]

        default : 

        $row->Country = "US";

        break;      

      }

  }

  function prepare($entity, stdClass $row){

    // delete rows added by MigrateMembers

    $num_deleted1 = db_delete('field_data_field_member_school')

      ->condition('entity_id', $this->destinationValues->entity_id)

      ->execute();    

    $num_deleted2 = db_delete('field_data_field_user_address')

      ->condition('entity_id', $this->destinationValues->entity_id)

      ->execute();

      switch ($row->SchoolAffiliation)

      {

        case 'AR': // Arts

        $row->SchoolAffiliation = "91";

        break;

        [more mappings]

        default : 

        $row->SchoolAffiliation = "NULL";

        break;      

      }

    $this->destinationValues->field_member_school_tid = $row->SchoolAffiliation;

    $fields = array('entity_type' => 'user', 'bundle' => 'user', 'deleted' => 0, 'entity_id' => $this->destinationValues->entity_id, 'revision_id' => $this->destinationValues->entity_id, 'language' => 'und', 'delta' => 0, 'field_user_address_country' => $row->Country, 'field_user_address_administrative_area' => $row->State, 'field_user_address_sub_administrative_area' => '', 'field_user_address_locality' => $row->City, 'field_user_address_dependent_locality' => '', 'field_user_address_postal_code' => $row->ZipCode, 'field_user_address_thoroughfare' => $row->AddressLine1, 'field_user_address_premise' => $row->AddressLine2, 'field_user_address_sub_premise' => '', 'field_user_address_organisation_name' => '', 'field_user_address_name_line' => '', 'field_user_address_first_name' => $row->FirstName, 'field_user_address_last_name' => $row->LastName, 'field_user_address_data' => '');

    db_insert('field_data_field_user_address')->fields($fields)->execute(); 

  }

}

class SchoolIDMigration extends MigrateMembers_Basic_Migration {

  public function __construct($arguments) {

    parent::__construct($arguments);

    $this->description = 'Add data to field_data_field_SchoolID';

    $this->dependencies = array('MigrateMembers');

    $table_name = 'field_data_field_SchoolID';

    $this->source = new MigrateSourceCSV(DRUPAL_ROOT . '/' . drupal_get_path('module', 'migrate_users') . '/data_sources/drupaluser_import.csv', $columns, array('header_rows' => 1));

    $this->destination = new MigrateDestinationTable($table_name);

    $this->map = new MigrateSQLMap($this->machineName,

      array('Row' => array(

            'type' => 'int',

            'unsigned' => TRUE,

            'not null' => TRUE,

           )

         ),

        MigrateDestinationTable::getKeySchema($table_name)

      );

    // add uid from completed MigrateMembers migration

    $this->addFieldMapping('entity_id', 'Row')->sourceMigration('MigrateMembers');

    $this->addFieldMapping('revision_id', 'Row')->sourceMigration('MigrateMembers');

    //this assignment does not find the SchoolID; it is set in prepare()

    //$this->addFieldMapping('field_SchoolID_value', 'SchoolID')->sourceMigration('MigrateMembers');

    // set some defaults from the schema

    $this->addFieldMapping('delta')

      ->defaultValue("0");

    $this->addFieldMapping('entity_type')

      ->defaultValue("user");

    $this->addFieldMapping('bundle')

      ->defaultValue("user");

    $this->addFieldMapping('language')

      ->defaultValue("und");

    $this->addFieldMapping('field_SchoolID_format')

      ->defaultValue("plain_text");

    $this->removeFieldMapping('path');

    $this->removeFieldMapping('pathauto');

  }

  function prepare($entity, stdClass $row){

    // delete rows in table added by MigrateMembers

    $num_deleted3 = db_delete('field_data_field_SchoolID')

      ->condition('entity_id', $this->destinationValues->entity_id)

      ->execute();

    //

    if ($row->SchoolID) $this->destinationValues->field_SchoolID_value = $row->SchoolID;

    else $this->destinationValues->field_SchoolID_value = "NULL";

  }

}

Jan 02 2014
Jan 02

I took over a Drupal 7 project building a web application for college students to upload original videos about their school, and for schools to manage, group, and share the videos.

It's a startup privately funded by the principal, and we are literally working on a shoestring. My previous experience with media in Drupal led the principal to contact me via LinkedIn.

When it came time to build a video playlist in Drupal Views for JW Player version="6.7.4071" (formerly known as Longtail Video), I found very little useful documentation. In fact, someone suggested that those who know how are not interested in sharing their knowlege. -- but not me smiley

There are a couple of videos on YouTube by Bryan Ollendyke for Drupal 6. But a lot has changed in Drupal since then.

The Goal:

Back to the playlist: Site admins can mark a video featured by ticking a checkbox on the custom video content type. Now I want to display those featured videos as a playlist.

JW Player provides clear documentation about how to embed the player, and how to construct and load the playlist via a RSS feed.

The structure of the RSS file:

<rss version="2.0" xmlns:jwplayer="http://rss.jwpcdn.com/">
<channel>

  <item>
    <title>Sintel Trailer</title>
    <description>Sintel is a fantasy CGI from the Blender Open Movie Project.</description>
    <jwplayer:image>/assets/sintel.jpg</jwplayer:image>
    <jwplayer:source file="/assets/sintel.mp4" />
  </item>

</channel>
</rss>

There are various threads on drupal.org discussing video playlists for JW Player. None of them were particularly useful for me.

I constructed my Drupal View to to create a page display with a URL path and display fields:

jwplayer aml playlist drupal view

I tried using the Views Data Export module (a Drupal 7 successor of the Views Bonus Pack export functions) but it was difficult to create the proper syntax, particularly the xml namespace tag 

xmlns:jwplayer="http://rss.jwpcdn.com/"

I found the Views Datasource sub-module views_xml.module workable - mostly. I could add the namespace tag. The old videos by Bryan Ollendyke suggested the strategy to use the Views field labels for the xml tags like <title> and <description>.

I added the xml namespace in Format: XML data document: Settings:

jwplayer drupal view xml namespace

and set up the root and child syntax there too:

jw player xml root and child settings

But there were still two problems:

  1. The Video module does not expose the derivative transcoded video filenames to Views
  2. The JW Player syntax for the file source <jwplayer:source file="/assets/sintel.mp4" /> does not follow the convention of an opening and closing tag.

I used the Views PHP module to locate the filename from the node nid that was available in the the View. Yes, I could have written custom Views plugins and handlers, but remember: shoestring budget. It only took 3 queries:

$video_fid = db_query('SELECT field_video_fid FROM `field_revision_field_video` WHERE entity_id = ' . $data->nid)->fetchColumn();

$video_output_fid = db_query('SELECT output_fid FROM `video_output` WHERE original_fid = ' . $video_fid . ' ORDER BY output_fid DESC')->fetchColumn();

$video_uri = db_query('SELECT uri FROM `file_managed` WHERE fid = ' . $video_output_fid)->fetchColumn();

$video_source = str_ireplace("public://",$base_url,$video_uri);

return $video_source;

  1. Locate the original video_fid in the field_revision_field_video table
  2. Locate the video_output_fid (a different number generated by the Video module) in the video_output table
  3. Get the video_uri in the file_managed table

(yes we could use SQL JOIN - but we're only listing a few videos; TODO: rewrite the queries with placeholders for better security)

I considered creating php template files for these Views fields. But since I planned to embed the player in a custom block, I decided the parsing and processing could take place there.

Now we have the video file uri wrapped in tags <php></php> (from the label of the Views php field - it could be any arbitrary label, and we will be replacing it later).

The result of this view is something like:

<?xml version="1.0" encoding="utf-8"?> <rss version="2.0" xmlns:jwplayer="http://rss.jwpcdn.com/"> <channel> <item> <title>Who doesn&#039;t like free dessert?! </title> <description>Manhattan College</description> <jwplayer:image>http://[domain]/sites/default/files/[path]/thumbnail-690_0004.jpg</jwplayer:image> <php>path/690/MVI_4592_mp4_1384800425.mp4</php> </item> </channel>

Notice that it is not a valid XML or RSS document because there are no closing tags for the xml or the rss - but we will fix that later. Also I have used the value of the school field to rewrite the description field; but I might restore the description text in the playlist.

At first I thought I might use the view to write a file to the filesystem so I set the path to sites/default/files/playlists/featured_videos_playlist.rss.xml

Next, I had to get the playlist into the player.

I created a block with PHP code (or you can do it with a custom module and hook_block_info - but you already know I was working fast and cheap):

<?php

global $base_url;

$url = $base_url . "/sites/default/files/playlists/featured_videos_playlist.rss.xml";

$xml = fopen($url, "r");  

$playlist = stream_get_contents($xml);

$playlist = str_ireplace('<php>','<jwplayer:source file="/sites/default/files/',$playlist);

$playlist = str_ireplace('</php>','"/>',$playlist);

$playlist .= '</rss>';

file_put_contents('public://playlists/featured_videos_playlist.rss',$playlist);

?>

<div id="myElement">Loading the player ...</div>

<script type="text/javascript">

jwplayer.key="[put your key here if you have one]";

jwplayer("myElement").setup({

    playlist: "<?php print $base_path . '/sites/default/files/playlists/featured_videos_playlist.rss' ?>",

    listbar: {

        position: 'bottom',

        size: 80

    },

    height: 380,

    width: 420

    });

</script>

The block goes through 7 steps:

  1. Load the JW Player Javascript library
  2. Read the View output with PHP's fopen function (yes it works even though there is no actual file at the uri). I tried file_get_contents, but that failed because there was no actual file.
  3. Create the specialized jwplayer:source file tag from the file uri we placed within the <php></php> labels
  4. Close the rss tag (no need to close the xml tag)
  5. Write the playlist file to the filesystem (tip - use a different filename than the path in the View!!!)
  6. Embed the player in a div in the block
  7. Load the playlist we created

The generated playlist file (for the first item):

<?xml version="1.0" encoding="utf-8"?>

<rss version="2.0" xmlns:jwplayer="http://rss.jwpcdn.com/">

<channel>

  <item>

    <title>Who doesn&#039;t like free dessert?! </title>

    <description>Manhattan College</description>

    <jwplayer:image>http://[domain]/sites/default/files/[path]/thumbnail-690_0004.jpg</jwplayer:image>

    <jwplayer:source file="/sites/default/files/[path]/MVI_4592_mp4_1384800425.mp4"/>

  </item>

</channel>

</rss>

Finally, I used Context to place the block on the desired (home) page. Some additional CSS was required. JW Player is also fully skinnable.

The result may be viewed on the Proud Campus home pageOn that page, and the user pages and video upload page, we are using a simple responsive theme Ember selected via the Themekey module, to be more mobile-friendly. The web site is under active development and has some "rough edges."

Debriefing:

The project I inherited uses the "premium" GoVideo theme by ThemeSnap and the Video module.

If I were to start fresh, I would not have chosen to use them. GoVideo is a good example of how not to build a Drupal application all in the theme templates. I have started to move many of the functions into custom modules. One benefit of purchasing the GoVideo theme is that it includes a key for the licensed version of JW Player. Also, the Video module makes some assumptions and decisions that become tricky to customize. I will replace them with a responsive theme and component media modules, to better configure the file management, transcoding and display of the videos.

However, I think that much of my solution can be useful in general, without depending on the Video Module or the GoVideo theme. Also, we are not using the Drupal JW Player module - GoVideo includes the player's JavaScript library within its theme, and we are loading it in the block from the preferred location in Libraries.

I'm not going to into detail on how the content type is created, it uses the video field provided by the Video module, but the video could have been attached to the nodes in other ways.

I'm also not going to elaborate on the transcoding process; we are using avconv (formerly ffmpeg) on our server. I have used Encoding.com in the past at a client's request, and the Brightcove and Ooyala video management services as well. Eventually we will use Amazon AWS as a CDN for the videos - when we get more funding wink

 

<?php

global $base_url;

$url = $base_url . "/sites/default/files/playlists/featured_videos_playlist.rss.xml";

$xml = fopen($url, "r");  

$playlist = stream_get_contents($xml);

$playlist = str_ireplace('<php>','<jwplayer:source file="/sites/default/files/',$playlist);

$playlist = str_ireplace('</php>','"/>',$playlist);

$playlist .= '</rss>';

file_put_contents('public://playlists/featured_videos_playlist.rss',$playlist);

?>

<div id="myElement">Loading the player ...</div>

<script type="text/javascript">

jwplayer.key="[put your key here if you have one]";

jwplayer("myElement").setup({

    playlist: "<?php print $base_path . '/sites/default/files/playlists/featured_videos_playlist.rss' ?>",

    listbar: {

        position: 'bottom',

        size: 80

    },

    height: 380,

    width: 420

    });

</script>

The block goes through 7 steps:

  1. Load the JW Player Javascript library
  2. Read the View output with PHP's fopen function (yes it works even though there is no actual file at the uri). I tried file_get_contents, but that failed because there was no actual file.
  3. Create the specialized jwplayer:source file tag from the file uri we placed within the <php></php> labels
  4. Close the rss tag (no need to close the xml tag)
  5. Write the playlist file to the filesystem (tip - use a different filename than the path in the View!!!)
  6. Embed the player in a div in the block
  7. Load the playlist we created

The generated playlist file (for the first item):

<?xml version="1.0" encoding="utf-8"?>

<rss version="2.0" xmlns:jwplayer="http://rss.jwpcdn.com/">

<channel>

  <item>

    <title>Who doesn&#039;t like free dessert?! </title>

    <description>Manhattan College</description>

    <jwplayer:image>http://[domain]/sites/default/files/[path]/thumbnail-690_0004.jpg</jwplayer:image>

    <jwplayer:source file="/sites/default/files/[path]/690/MVI_4592_mp4_1384800425.mp4"/>

  </item>

</channel>

</rss>

(the school tag is ignored

Finally, I used Context to place the block on the desired (home) page. Some additional CSS was required. JW Player is also fully skinnable.

The result may be viewed on the Proud Campus home page. On that page, and the user pages and video upload page, we are using a simple responsive theme Ember selected via the Themekey module, to be more mobile-friendly. The web site is under active development and has some "rough edges."

Debriefing:

My project uses the "premium" GoVideo theme by ThemeSnap and the Video module. If I were to start fresh, I would not have chosen to use them. Govideo is a good example of how not to build a Drupal application all in the theme templates. I have started to move many of the functions into custom modules. One benefit of purchasing the Govideo theme is that it includes a key for the licensed version of JW Player. Also, the Video module makes some assumptions and decisions that become tricky to customize. I would have used a nice responsive theme and component media modules, to better configure the file management, transcoding and display of the videos. However, I think that much of my solution can be useful in general, without depending on the Video Module or the Govideo theme. Also, we are not using the Drupal JW Player module - GoVideo includes the player's JavaScript library within its theme, and we are loading it in the block from the preferred location in Libraries.

I'm not going to into detail on how the content type is created, it uses the video field provided by the Video module, but the video could have been attached to the nodes in other ways.

I'm also not going to elaborate on the transcoding process; we are using avconv (formerly ffmpeg) on our server. I have used Encoding.com in the past at a client's request, and the Brightcove and Ooyala video management services as well. Eventually we will use Amazon AWS as a CDN for the videos - when we get more funding wink

Nov 17 2013
Nov 17

I am working on a Drupal project for the Columbia University Office of Alumni Affairs and Development, and I was unable to connect to the Pantheon servers from the Ubuntu command line from home.

I have Ubuntu Linux installed in an Oracle VirtualBox, because the laptop provided by Columbia is pretty locked down and I don't have admin rights, but they installed VirtualBox so I could add an Ubuntu machine and configure it as needed.

It worked fine while I was on site, but now that I am working remotely I could not connect.

Pantheon support suggested that my ISP is unable to connect to their IPs in DNS, and I might be able to connect using Google Public DNS:

There are two steps in debugging the problem you are experiencing.

1. Check to see if you are getting an I.P. address returned when you run the following command, replacing “<xxx>” with your site’s UUID:

dig appserver.dev.<xxx>.drush.in
(ex.:dig appserver.dev.38fde024-2874-4cce-b02a-072686c4ded9.drush.in)

If there is no I.P. in the output then the ISP on the network you are currently on is failing to recognize the hostname of the database.

2. For some users that may fail so the next step is to test this command with name server, in this case Google’s 8.8.8.8 I.P address:

dig @8.8.8.8 appserver.dev.<xxx>.drush.in
(ex:dig @8.8.8.8 appserver.dev.38fde024-2874-4cce-b02a-072686c4ded9.drush.in)

If that returns an I.P. address, this means that using Google’s DNS you were able to resolve the hostname. To resolve the issue you can set your DNS to use Google’s service and you should be able to connect:

https://developers.google.com/speed/public-dns/

It works fine, but the directions for a VirtualBox instance are a little different than what Google has posted in the instuctions for Ubuntu:

Ubuntu in the VirtualBox is using its eth0 wired connection to the host OS to piggyback on the Windows wireless network adapter.

I had to configure the Ubuntu wired connection (there is no wireless connection defined). The rest of the guide is applicable, and I changed Method: "Automatic (DHCP)" to "Automatic (DHCP) addresses only"

configuring Google public dns for Ubuntu in VirtualBox
Jun 21 2013
Jun 21

Unless otherwise noted, all content on the netsperience 2.x site is copyright © 2008 - 2014 by Randall Goya you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version, see <http://www.gnu.org/licenses/>

Drupal is a registered trademark of Dries Buytaert

May 03 2013
May 03

Unless otherwise noted, all content on the netsperience 2.x site is copyright © 2008 - 2014 by Randall Goya you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version, see <http://www.gnu.org/licenses/>

Drupal is a registered trademark of Dries Buytaert

Jan 18 2013
Jan 18

In honor of Internet Freedom Day I'm Bumping This Post || RIP Aaron Swartz

Help spread the word by joining this Thunderclap: https://www.thunderclap.it/projects/1039-internet-freedom-day

What Is SOPA? Get me spyglass, I'll warrant ye!

(original text Pirated from Gizmodo.com)

If you hadn't heard of SOPA before, you probably have by now: Some of the internet's most influential sites—Reddit and Wikipedia among them—are going dark to protest the much-maligned anti-piracy bill. But other than being a very bad thing, what is SOPA? And what will it mean for you if it passes?
SOPA is an anti-piracy bill working its way through Congress...

House Judiciary Committee Chair and Texas Republican Lamar Smith, along with 12 co-sponsors, introduced the Stop Online Piracy Act on October 26th of last year. Debate on H.R. 3261, as it's formally known, has consisted of one hearing on November 16th and a "mark-up period" on December 15th, which was designed to make the bill more agreeable to both parties. Its counterpart in the Senate is the Protect IP Act (S. 968). Also known by its cuter-but-still-deadly name: PIPA. There will likely be a vote on PIPA next Wednesday; SOPA discussions had been placed on hold but will resume in February of this year.
...that would grant content creators extraordinary power over the internet...

The beating heart of SOPA is the ability of intellectual property owners (read: movie studios and record labels) to effectively pull the plug on foreign sites against whom they have a copyright claim. If Warner Bros., for example, says that a site in Italy is torrenting a copy of The Dark Knight, the studio could demand that Google remove that site from its search results, that PayPal no longer accept payments to or from that site, that ad services pull all ads and finances from it, and—most dangerously—that the site's ISP prevent people from even going there.
...which would go almost comedically unchecked...

Perhaps the most galling thing about SOPA in its original construction is that it let IP owners take these actions without a single court appearance or judicial sign-off. All it required was a single letter claiming a "good faith belief" that the target site has infringed on its content. Once Google or PayPal or whoever received the quarantine notice, they would have five days to either abide or to challenge the claim in court. Rights holders still have the power to request that kind of blockade, but in the most recent version of the bill the five day window has softened, and companies now would need the court's permission.

Oct 14 2012
Oct 14

When I was unable to post to Twitter from my blog tonight, I found a very new thread on drupal.org discussing the issue: the Twitter API changed again!

I am only using the Twitter module on Drupal 6 sites right n   ow, I took two of the fixes and combined them in a patch for the 6.x-3.0-beta9 version. Then MinhH submitted new code that only requires a change in one place.

here's my latest patch for the 6.x-3.0-beta9 version

since the Twitter API is constantly changing, and the module is not very stable, always read the thread carefully to make sure you are applying the latest patch! Others have cleaned up the code I added from MinhH, and created a patch for the 6.x-3.x-dev branch which makes sense (however I suspect that 6.x-3.0-beta9 may be ahead of the -dev branch, and it's not a branch provided in the project's Github). Anyway, Open Source rocks, Murray!

and because my Twitter RSS feed URL was also broken, I found this post

https://dev.twitter.com/discussions/844

which provides an update to the URL format:

https://api.twitter.com/1/statuses/user_timeline.rss?screen_name=decibelplaces

About Drupal Sun

Drupal Sun is an Evolving Web project. It allows you to:

  • Do full-text search on all the articles in Drupal Planet (thanks to Apache Solr)
  • Facet based on tags, author, or feed
  • Flip through articles quickly (with j/k or arrow keys) to find what you're interested in
  • View the entire article text inline, or in the context of the site where it was created

See the blog post at Evolving Web

Evolving Web