Underhill Labs

Notes on Web and Mobile Development

Election Badge Experiment

I’ve been experimenting with codepen a lot lately. There is something so cool about experimenting with some tricky javascript and sending a link to a code sandbox somewhere to someone and they fork it and say, “here’s the fix”. Or they take the idea a step further and say, “and what about this?”

Well, below is a ballot feature that I’d like to replicate with Drupal’s webform module. (well except for the tricksy voting feature.)

See the Pen Election Badge Experiment by Bart Lantz (@majorursa) on CodePen.

Adding a Group by Clause to a Drupal View

These are some quick notes on how I added a group by clause to a query for a view in Drupal 7.

I have a custom database query in a module that I wrote and one of the fields in it: video_id corresponds to a node nid. So I wanted to pull this database into a view. However there can be many rows in my database for each node and all I wanted for the view was to print one message Update answer key if there was one or more entries in the database and a different message Create answer key if there were no entries.

tl;dr this is what worked

Tag the query in hook_views_query_alter()

1
2
3
4
5
6
7
8
9
10
11

/**
 * Implements hook_views_query_alter().
 */
function my_mod_views_query_alter(&$view, &$query) {
  if($view->name == 'video_landing_page') {
    // add_groupby doesn't work
    //$query->add_groupby('node.nid');
    $query->add_tag('my_mod');
  }
}

Next implement hook_query_TAG_alter

1
2
3
4
5
6
/**
 * Implements hook_query_TAG_alter().
 */
function dance_code_query_my_mod_alter(QueryAlterableInterface $query){
  $query->groupBy('node.nid');
}

Full Story

This was simple to do with a raw sql statement by adding “group by node.nid”. So I first tried adding this group by with hook_views_query_alter().

1
2
3
4
5
function my_mod_views_query_alter(&$view, &$query) {
  if($view->name == 'video_landing_page') {
    $query->add_groupby('node.nid');
  }
}

This did not appear to work. I searched google and D.O. and none of the solutions worked. There was also a consensus that there was a bug in hook_views_query_alter() and that I should use hook_query_altar() instead.

So I worked on implementing hook_query_alter, unfortunately the first problem was knowing how to identify that I was altering the correct view.

Notes on Drupal Rules

These are notes for some of the trickier situations for writing Rules.

Trigger Rule when a Node field has changed

In order to get the node-unchanged selector, the only event must be “Before Saving Content”.

Event

  • Before Saving content

Conditions

  1. Set condition field exists
  2. Entities -> Entity has field (eg node:field-supervisor)
  3. Data Comparison
  4. equals (negated)
    • node-unchanged:field-supervisor
    • node.field-supervisor

Vim Plugins: Vim-surround

Contunuing my series of posts on awesome, indispensible Vim plugins, today let’s meet vim-surround! Vim-surround allows you to manipulate text/tags/quotes that surround a chuck of code.

Delete Surroundings with ds*

Delete the <h3> tag:

before:

1
<h3>Welcome to Snarnia!</h3>

command to run with cursor anywhere on the line (even in tag.)

Delete Surrounding Tag
1
dst

after:

1
Welcome to Snarnia!

Change Surroundings with cs*

Change "Hello, Bossy" to <q>Hello, Bossy</q> with:

Change Surrounding Quote
1
cs"<q>

Add Surrounding text with ys*

Adding text makes use of vim’s yanking word movements. Suppose you want to add, quotes around a sentence:

before:

1
2
3
I'm a monster, as well as a dwarf. You should charge me double.

--Tyrion Lannister

Command, on same line as quotation:

1
yss"

after:

1
2
3
"I'm a monster, as well as a dwarf." You should charge me double."

--Tyrion Lannister

I have found vim-surround especially helpful when editing html.

Manipulate outer tags

One more tip for manipulating outer tags: add a numeric argument to work on outer tags.

“example html”
1
2
3
4
5
6
7
<div>
<ul>
  <li>one</li>
  <li>more</li>
  <li>thing</li>
<ul>
</div>

In order to delete the <div> tag while in a list item in the code up above, type: ds3t and that will delete the tag 3 layers up.

Afterwards:

“example html”
1
2
3
4
5
<ul>
<li>one</li>
<li>more</li>
<li>thing</li>
<ul>

Vim and NERDTree Awesome-ness

I thought I’d write up a couple quick posts on vim commands that I have been using lately. This first post is on NERDTree.

NERDTree

Wow, this plugin is so amazing. It is a sideloaded file browser for vim. screenshot of NERDTree

Installing and setting up NERDTree

If you have pathogen installed, then installing it is as simple as:

1
2
cd ~/.vim/bundles/
git clone git://github.com/scrooloose/nerdtree.git

Next, I added the following to my ~/.vimrc

1
map <leader>nt :execute 'NERDTreeToggle ' . getcwd()<CR>

My leader key is comma, so once the previous code is added to my .vimrc, I can type ,nt in normal mode, and NERDTree’s file browser pops open on the side and displays the current working directory. Type C-w h (Control-w h) to send focus to the file browser and hit <enter> over a directory to expand it, or press <enter> over a file name to open it in the main window.

Next to close the file browser type ,nt again and it disappears.

Use NERDTree to rename and create new files.

I was satisfied with the above commands for awhile, it was very convenient, but then I watched screencasts of people creating files eaily in Sublime and Textmate and lo I was unsatisfied. But then one day as I was in the file browser I happened upon the “m” or menu command and it was a godsend! Typing m while on a file will bring up a menu of options:

1
2
3
4
5
6
NERDTree Menu. Use j/k/enter and the shortcuts indicated
==========================================================
> (a)dd a childnode
  (m)ove the current node
  (d)elete the current node
  (c)opy the current node

Move (or rename) a file

  1. move cursor to a filename in the file browser
  2. type m to bring up the menu
  3. type m again to rename the file
  4. type in it’s new name, press <enter> and the file is renamed.

Create a new file

  1. move cursor to a filename in the file browser
  2. press m while on a directory.
  3. type a for add a child node
  4. type in the new file’s name and press <enter>
  5. arrow down to the new file name and select it to start editing it.

Delete a file

  1. move cursor to a filename in the file browser
  2. press m to bring up the menu.
  3. press d to delete the file.

Opening file

By default NERDTree opens a file you select in the current window (the last window you hopped to the file browser from.) If you’d like to open it in a new horizontally split window, press i when cursor is on the file. To open it in a new vertical spit, type s.

NERDTree help

As mentioned earlier, you can type ? while in the file browser to bring up a help window. Type ? again to toggle the help window’s display.

XQuery Notes

This post continues my notes from Introduction to Databases, a MOOC taught by Stanford’s Jennifer Widom. The topic of this post is XQuery.

XQuery Defition

XQuery is a query and functional programming language that is designed to query collections of XML data.

from wikipedia,

XQuery Syntax: FLWOR

FLWOR Syntax
1
2
3
4
5
for $var in expr
let $var := expr
where expr
order by expr
return expr
  • All the statements are optional, but the return statement
  • The for and let statements may be used more than once to build up complex queries

XML for the Examples

countries.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<countries>
  <country name="Afghanistan" population="22664136" area="647500">
    <language percentage="11">Turkic</language>
    <language percentage="35">Pashtu</language>
    <language percentage="50">Afghan Persian</language>
  </country>
  <country name="Albania" population="3249136" area="28750"/>
  <country name="Algeria" population="29183032" area="2381740">
    <city>
      <name>Algiers</name>
      <population>1507241</population>
    </city>
  </country>
</countries>

List all Countries with population over 1,000,000,000

1
2
3
4
5
6
7
for $c in doc('countries.xml')/countries/country
where $c/@population > 1000000000
order by $c/@population
return
<big-countries>
 { $c/data(@name) }
</big-countries>

XPath Notes

This spring I took Introduction to Databases a MOOC (Massive Open Online Class) from Stanford’s Jennifer Widom. It was very interesting, I learned a great deal from it. And one of the sections of the class that I really enjoyed, even though it sounded like it was going to be pretty dry, was the section on XML. I took quite a few pages of notes on the different XMl tools, so I figured I might as well post them to my blog.

XPath Definition

XPath is a query language for selecting nodes from an XML document.

from Wikipedia

XML for the Examples

countries.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<countries>
  <country name="Afghanistan" population="22664136" area="647500">
    <language percentage="11">Turkic</language>
    <language percentage="35">Pashtu</language>
    <language percentage="50">Afghan Persian</language>
  </country>
  <country name="Albania" population="3249136" area="28750"/>
  <country name="Algeria" population="29183032" area="2381740">
    <city>
      <name>Algiers</name>
      <population>1507241</population>
    </city>
  </country>
</countries>

Basic Path

Selects all of countries from the country root node.

1
doc('countries.xml')/countries/country

Path with Alternation

This will select countries with populations that speak Pashtu or Swedish.

1
doc('countries.xml')/countries/country[language(Pashtu|Swedish)]

Attributes are prepended with “@”

This will select the names of all countries

1
doc('countries.xml')/countries/country/@name

Path with Conditional

Select the names of all countries that have a population of less than 1,000,000.

1
doc('countries.xml')/countries/country[@population < 1000000]/@name

Complex Conditional

Select all countries that have a population of less than 1,000,000.

1
doc('countries.xml')/countries/country[@population < 1000000 and city/population > 50000]

data() prints out just the text value.

1
doc('countries.xml')/countries/country[@population < 1000000]/data(@name)

Fun With Grep and Bash Aliases

Just a quick post to write up an bash alias I just created to list the sites enabled on a server and each sites location.

I often need to look up the list of sites enabled on a web server and also the document roots of each of these sites. In the past this meant loading the Apache configuration file into emacs or vim and searching for the site I was looking for. Today I decided to create an alias for this task. Here is the original combination of grep commands.

Grep Command

1
2
grep ''^ServerName" -A 3 /etc/apache2/sites-available/default | \
   grep "^ServerName\|^DocumentRoot\|^--"
  1. ^ServerName:
    • Searches the file for all lines that begin with ServerName at the beginning of the line.
  2. -A 3
    • Prints the 3 lines after the match.
  3. 3. grep “^ServerName\|^DocumentRoot\|^–”’
    • Prints only the lines that we are interested (ie lines that begin with ServerName or DocumentRoot or –)
    • The “\|” in the second grep command separates the regex with an “or” statement.

Alias Command

  1. Choose a name for the command, in my case, show-sites
  2. Add the alias to the .bashrc or .bash_alias file in your home directory
1
2
alias show-sites='grep "^ServerName" -A 3 /etc/apache2/sites-available/default | \
 grep "^ServerName\|^DocumentRoot\|^--"'

Reload Your Bash Configuration

1
. ~/.bashrc

Show All Current Aliases

Type alias at the command line to view all of the currently defined aliases.

1
$ alias

Bash prints out all of your aliases, like so:

1
2
3
4
5
6
7
8
9
10
11
12
13
alias ducks='du -cks * |sort -rn |head -11'
alias gb='git branch'
alias gba='git branch -a'
alias gc='git commit -v'
alias gca='git commit -v -a'
alias gd='git diff | vim -'
alias gl='git log --stat'
alias gp='git push'
alias graceful='sudo apache2ctl graceful'
alias gst='git status'
alias ls='ls --color=auto'
alias show-sites='grep '\''^ServerName'\'' -A 3 /etc/apache2/sites-available/default \
  | grep '\''^ServerName\|^DocumentRoot\|^--'\'''

Using jQuery in Drupal

Drupal 7 includes jQuery 1.4.4 by default. So getting started using jQuery in your module or theme is really simple. The easiest way to include a javascript file is to include it in a .info file with a line like “scripts[]= myfile.js”.

How to include your js file in a module or theme

Add this to the .info file

1
scripts[] = js/myjavascript.js

this will include the javascript file on every page that includes your module or theme.

Include it in your Theme’s template.php

Themes have more granular control of where javascript file is included in template.php

1
drupal_add_js(drupal_get_path('theme','kepler6_omega') . 'js/myjavascript.js');

Use drupal_add_js in your module

Include js in yarr.module

1
2
3
4
function yarr_form_alter(&amp;$form, &amp;$form_state, $form_id) {
  drupal_add_js(drupal_get_path('module,'yarr') . 'js/myjavascript.js');
  ...
}

Use drupal_add_library to add libraries included in Drupal

example add jQueryUI Accordion library

1
drupal_add_library('system','ui.accordion');

While jQuery core is included by default, jQuery UI is not, so you need to include that with drupal_add_library if you need it, as above.)

Drupal Behaviors

Drupal behaviors are used instead of $(document).ready();

$(document).ready() is used to wait until the entire DOM is loaded before adding your javascript functionality to the DOM.

Drupal adds its own way to add your javascript to the DOM

Drupal attach behaviors

attach your functionality to the Drupal objects behavior array

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
(function($) {

Drupal.behaviors.yarr = {
  attach: function(context, settings) {
    $('h1 &gt; a').each(function(i) {
      var h1text = $(this).text();
      $(this).text("Yarrr, matey! " + h1text);
    });
    $('h1:not(:has(a))').each(function(i) {
      var h1text = $(this).text();
      $(this).text("Yarr, " + h1text);
    });
  }
};

})(jQuery)

Notes on the code example

  • The outside wrapper is required if you wish to use the short jquery “$” syntax.
  • $(‘h1 > a’) selects all of the H1 headers (and the inner anchor) that have immediate anchor children
  • $(‘h1:not(:has(a))’) selects all H1 headers that do not have anchors embedded in them.
  • .each, iterates over each element in the selector.

Inside the function the “this” object refers to the current DOM element. If you want the jQuery object use the $(this) syntax.

Further Reading

Drupal Documentation on Javascript and jQuery

jQuery Documentation

jQuery API Documentation

Using Drush and SQL to Delete Spam Comments

spam filters fall apart; the centre cannot hold.

Spam Gets By …

From time to time, despite precautions, spammers get by the Drupal filters and captchas. Or perhaps, the problem gets put in your lap and you have the pleasure of deleting thousands of spam comments.

The Drupal Administrative interface doesn’t provide many resources unless you enjoy clicking “select all” and “delete selected comments”, page after page, 50 comments at a time.

Drush to the Rescue

But, if you are comfortable with sql, one easy solution is to use drush sql-query to delete the comments.

Before you start Deleting anything…

If you are unfamiliar at all with sql, first back up your drupal database.

1
drush sql-dump --result-file=/path/to/dump-file.sql

You could also dump just the comment table. (The ‘~’ says to save it in your home directory.)

1
drush sql-dump --result-file=~/comment-table.sql --tables-list=comment

Now let’s get busy!

First take a look at the comment table:

1
drush sqlq "desc comment"

( drush sqlq is the short form of drush sql-query)

You could also use drush sql-cli to view the comment table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$ drush sql-cli
....
mysql> desc comment;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| cid      | int(11)             | NO   | PRI | NULL    | auto_increment |
| pid      | int(11)             | NO   | MUL | 0       |                |
| nid      | int(11)             | NO   | MUL | 0       |                |
| uid      | int(11)             | NO   | MUL | 0       |                |
| subject  | varchar(64)         | NO   |     |         |                |
| hostname | varchar(128)        | NO   |     |         |                |
| created  | int(11)             | NO   | MUL | 0       |                |
| changed  | int(11)             | NO   |     | 0       |                |
| status   | tinyint(3) unsigned | NO   |     | 1       |                |
| thread   | varchar(255)        | NO   |     | NULL    |                |
| name     | varchar(60)         | YES  |     | NULL    |                |
| mail     | varchar(64)         | YES  |     | NULL    |                |
| homepage | varchar(255)        | YES  |     | NULL    |                |
| language | varchar(12)         | NO   |     |         |                |
+----------+---------------------+------+-----+---------+----------------+
14 rows in set (0.00 sec)

Next take a look at the comments:

1
drush sqlq "select subject,cid from comment "

You can show comments from the last 30 days with the following.

The created field is stored as a unix timestamp (seconds since the Unix Epoch), so to get last month timestamp:

1
Seconds to subtract = (seconds in an hour)*(24 hours in a day)*(30 days) = 3600*24*30
1
drush sqlq "select subject,cid from comment where created > unix_timestamp(now())-3600*24*30"

Delete comments within a range of comment ids.

If you know the range of spam comment ids, you can give sql a range of comment ids:

Show the range of comments:

1
drush sqlq "select subject,cid from comment where cid > 3013 and cid < 3134 "

Delete the range of comments:

1
drush sqlq "delete from comment where cid > 3013 and cid < 3134 "

Show comments, group by name of commenter

1
drush sqlq "select subject,name,cid from comment where cid > 300 group by name"

But, honestly, there’s probably a drush delete-comments module that needs to be written!

I’ll keep you posted of my progress on that. In the meantime, you can try using the previous commands. Just remember to backup your database first, its only a single drush command after all!

1
drush dump --result-file=~/drupal-backup.sql