Skip to content

SQL UPDATE for strings in MongoDB

MongoDB currently does not offer the equivalent of a SQL Update statement when you want to change the value of a string to something that is based on the current value. In my case I needed to find spaces and change them to underscores -- something done easily in php with str_replace(), and in MySQL with REPLACE.

If you had a table named foo with a column named "mystr" you could do this sort of update like this:


UPDATE foo SET mystr = REPLACE(mystr, ' ', '_');
 


MongoDB does have collection.update() that works with a series of "modifier" operations, but none of them are useful for doing modifications to the current value of a document field when that field is a string.

MongoDB does however support javascript, and the javascript replace() function implements a regular expression search and replace. I was able to accomplish the update using the forEach() method to process the resulting documents one at a time. While not as efficient as having mongo do this internally with a specially built update modifier, at least this method only needs to operate on documents that are known to require modifications, and could be run in the mongo interactive client easily.

Again, assuming the collection is named "foo" and the document field is named "mystr"


db.foo.find({ mystr: /[ ]+/ }).forEach( function(u) { u.mystr = u.mystr.replace(/[ ]/g, "_"); db.foo.save(u); } );
 


An existing patch promises to add a new update() modifier but until such a time as it exists, this approach can be used when you need to update strings in a collection, referencing the existing values in the transformation.
Defined tags for this entry: , , , , , ,

Load the Url symfony helper in a model or form class

I'm doing some symfony work and needed to use the Url helper in a form class to return an error message that included a link. I would consider this one of those exceptions where you really need to be able to have markup in a class rather than in a template.

I should probably mention that this is the way that works in version 1.4.3, and that the method that works in actions will not work in a form class. That method is:


sfContext::getInstance()->getConfiguration()->loadHelpers(...);
 


That method was deprecated and there is also this article to consider.

In this case, the solution was to access the sfApplicationConfiguration object. In order to use the Url helper so you can call link_to() you need to also include the 'Tag' helper.


sfApplicationConfiguration::getActive()->loadHelpers(array('Url', 'Tag'));

// now you can use link_to(...)
 


Obviously this would allow you to load other helpers in the same way ... i18n for example.


How to configure AVReloaded to work with Longtail's JW Player Google Analytics Pro plugin

Allvideos Reloaded (AVReloaded) is a really nice piece of software engineering from Fritz Elfert. Rather than hardcode for all the individual players out there, Fritz created a macro system that allows you describe the parameters for any player, so it can be configured and updated.

Click for screenshot of Google Analytics Event categories, generated by the Longtail GA Pro plugin
Like the Allvideos plugin, AVReloaded comes bundled with the ubiquitous JW Player for Flash. If you are streaming flash movies from your website, one worthwhile plugin is longtail's Google Analytics Pro. The current license price for the plugin is $19 from longtail's site. If you're already using Google Analytics, the plugin feeds "Content" events from the player to Google Analytics which will show up under the "Event Tracking" menu. Ever have questions like: "I wonder what videos people are actually watching on my site" or "for this new video, how many seconds do they watch before they get bored and close it?" If so, then this plugin is for you. The player will generate events that GA groups into 3 categories: "Video Plays", "Percentage Played" and "Seconds Played". Drilling down you get a nice list of the Videos on your site. For example, finding out that people only watch on average 10% of that promotional video for your product is probably a sign that you may need some better material. Continue reading "How to configure AVReloaded to work with Longtail's JW Player Google Analytics Pro plugin"

Joomla K2 Component ERD Schema and Tree traversal optimization with Catalog Mode

Joomla K2 component Schema in png format
In the recent profile of GB4K.com, I talked about the use of the K2 component from the Folks at JoomlaWorks. While doing some customization, I decided to reverse engineer the K2 tables that are created and used by the component. I provide the diagrams for interested parties and developers looking to understand how K2 is designed. Keep in mind that these are logical diagrams, since Joomla and K2 have no intrinsic support for MySQL other than using the MyISAM engine, which doesn't support key constraints. Click on the image to get the .png version, or this link for a .pdf.

One thing I noticed about K2, is that its tree traversal code can require a lot of queries to be run, especially if you have a lot of categories. Over on the K2 forums, one K2 user with a large number of subcategories was complaining about the substantial overhead involved in displaying the top level Category for their site. With over 1000 subcategories, the site was being hammered with an equal number of queries.

This is because by default K2 will do a query for every category to find its child categories and items. Since this is a recursive problem there's no inherent MySQL solution that would allow the developers to solve this problem in SQL (like the Oracle Start With ... Connect By syntax) so it's somewhat understandable that the JoomlaWorks developers provided a short circuit mechanism into the category configuration they call "Catalog Mode". The description of this configuration flag, which defaults to "No" is:

If you select 'yes' then only the items directly belonging to this category will be retrieved. If you select 'no' then items from all sub-categories will be retrieved. Setting this option to 'no' is ideal for news/portal/magazine/blog layouts.


This setting simply shuts off the recursion behavior for the category.
Defined tags for this entry: , , , , ,

Get files from subversion without creating a sandbox using svn export

One of the first things people learn about using subversion is how to do a "checkout" using svn co. The svn checkout command pulls files from the subversion repository into your "sandbox" and in so doing creates what subversion calls a "working copy". A working copy includes a .svn directory in every subdirectory of the working copy, which is chock full of directories and files that svn uses to determine what you're doing in your sandbox.

A "working copy" is designed to be just that -- a copy of the source tree built with the assumption that you will be making changes and committing them back to subversion. But what do you do if you want the files, but you don't need or want a sandbox?

Continue reading "Get files from subversion without creating a sandbox using svn export"

Finding "Next Monday" using MySQL Dates

Several people who read my article on Exploring Mysql CURDATE and NOW. The same but different posed questions regarding how to return a valid MySQL date equivalent to "Next Monday" given any particular day, as determined by MySQL's CURDATE(). This is a little bit tricky, but can be done entirely in MySQL syntax, making it usable with Calendar applications built on top of MySQL queries, without the need to use serverside date functions. This builds upon concepts discussed in my prior article, so if you have trouble understanding the implications of using CURDATE or DATE_ADD, you should probably take a minute and read that article. Continue reading "Finding "Next Monday" using MySQL Dates"