Drupal queries and the IN SQL operator

One of the most used functions in Drupal's database abstraction layer is db_query, which allows passing an SQL string and corresponding arguments to send a query to the database. I'll give you a quick overview of how db_query works before showing you how to drupalize a query such as:

SELECT field1, field2 FROM table_name WHERE field1 IN (value1,value2,...)

Some lessons from building Drupal 6 themes

After a few Drupal 6 projects where I had to create themes from scratch, including my recently released Woodpig theme for Ventanazul I've learned a lot and decided to gather some tips I'm sure will help you, my fellow Drupalist, when turning your next design into a functional Drupal managed site. Sounds good? Let's dive into the powerful Drupal 6 theme API.

Welcome home Woodpig

That's a freaking lot of time, really, but as anybody who does web development for a living knows it's sometimes difficult to find the time for your own projects when you are working in clients' gigs. Besides, I wanted to theme my site according to a very specific set of rules that kept changing over time, common problem, I know.

First, I decided to move Ventanazul from a simple blog format to a more niche community site, a site for people working in professional web development, that meant I had to enable account registrations, forget about vBulletin and rethink about the quality of new content and the profile of users to approve (users have to be manually approved and all comments are moderated).

Defining the goals and information architecture of the new Ventanazul took a few months while I gathered ideas from a lot of sources, like sites I enjoyed and projects I was working on. That led me, after many hours with Gimp, to the final mockups of the new design. In the meantime Drupal 6 was out and I had to invest time on learning a few new tricks.

It was the perfect timing as I started working on projects for a couple of clients that required moving to Drupal 6 and using my upgraded theming skills. For Ventanazul's redesign I used many of the new nice features of Drupal, I found the preprocess functions very helpful for separating comments and their form from node content, they usually come as a whole in the $content variable of the page.tpl.php template.

The theme can be considered as a 0.9 version and I know there may be a few small bugs around that I'll fix on the road but I wanted to release and start getting feedback as soon as possible, well, two years is not really soon but you know what I mean. I have a small set of additional features cooking for a future release and may come with some other ideas. As should be the norm in 2009 the markup was built thinking in modern web browsers that respect web standards so I didn't waste time on Internet Explorer 6 bugs or horrible hacks.

Some hints about overriding themes in Drupal 6

There's a lot to read and absorb on the new Drupal 6 but the good news is that if you've spent a while hacking version 5 you will need just a few days to get up to date.

One of my favorite changes is on theming. Now every module can provide its own .tpl.php files. To override them you just need to copy them to your theme directory and start hacking around.

Mark Boulton and the redesign of Drupal.org

Mark Boulton is working on the redesign of Drupal.org, that's great news. I've been following Mark's work for many years and I really enjoy his writing, his series about grids and typography are fantastic pieces.

But what's even better is that Mark and his team have decided to open their design process, a very brave and risky move, and they've called it design by community.

I don't know if this will work or not but in any case I'm sure we will all learn a lot from it.

Learning German With Rosetta Stone Online

Rosetta Stone GermanA new and very interesting project with Drupal at Munich renewed my interest in learning German, something I've had pending for too long. This time I decided to try harder and finally learn Hermann Hesse's tongue.

I already knew that finding the time and right teacher was not an easy task so I started searching online. I had already tried a few options, including the quite helpful and funny free course from Deutsche Welle, but now I wanted something that could teach me German as fast as possible and I would pay for it.

That's how I found Rosetta Stone, a company offering software used by many many people all over the world, including executives and employees from some very big businesses. The system Rosetta Stone uses is called Dynamic Immersion and works quite well, connecting you with the new language, they have thirty available, from the very first moment.

Using nice pictures, different voices and a series of interactive exercises the student can easily get the basic concepts behind the language. I have almost one week with my Rosetta Stone course and I can say I'm right on track. The project with Drupal went quite well and at some point I could just program and design without even noticing all the text was in German.

I purchased the online version of Rosetta Stone, a little more than US$ 100 for a 3 month subscription, to avoid additional shipping and custom costs, and of course to save time. The only problem I've found so far, as a Linux user, is that the software requires Adobe Shockwave, available only for Windows and Mac OS, but anyway, that's an Adobe issue actually.

Obviously this is not the end of the road for my German classes. I will take other courses in the future, probably Berlitz Online, much more expensive and, obviously, will keep listening to Rammstein and Tokio Hotel as much as possible.

Auf Wiedersehen!

Open Source Applications For Database Modelling

Are there any open source applications to model my database? Many developers have asked me that question quite often lately.

When coding Drupal modules having a good picture of the database model can help a lot to write the right queries, well, actually this applies to any programming project that uses a database with more than a few tables.

Even if there are many commercial applications, many of them quite stable and complete, I think their prices are way too much for most developers, specially for open source developers like most of us Drupal dudes. Want numbers? I got numbers: CA Erwin Data Modeler costs US$ 3995. With that much dough I'd prefer to complete my list of gadgets I want for 2007 and add another laptop with Linux to my arsenal.

So, what are the options to model your database with an open source tool?

Some years ago I found DBDesigner 4 and used it in a few projects to model on MySQL, the only RDBMS it handles. I was quite happy to know that MySQL had taken over the product to relaunch it as MySQL Workbench, however, MySQL has focused on other tools and Workbench progress has been slow. I've recently read in their forum that they were almost finished with other tasks and would return to work with MySQL Workbench. Good to know.

DBDesigner 4 is still available and is a good choice if all you need is MySQL support. The installation is a little difficult in Ubuntu, although I didn't have any trouble with Fedora..

And what happens if, like me, you also need to model PostgreSQL databases? The only application I found for that some months ago was Mogwai ER-Designer, which works with MySQL, PostgreSQL, Oracle and a few other commercial RDBMS. Mogwai ER-Designer is written in Java, so it runs in most operating systems with a JVM.

Unfortunately Mogwai's interface still needs some work and creating models is not too user friendly, even when they correctly use domain dictionaries. I also think development progress is going a little slow.

What I'm using now, and recommend, is Power*Architect, which, like Mogwai, is a Java application and works with PostgreSQL, Mysql, Oracle and others.

I think PowerArchitect has a better interface than Mogwai, creating models and generating SQL is quite easy and takes just a few clicks. PowerArchitect has been declared open source just a few months ago, way to go guys!, and there are still many features to add and improve (like support for native auto increment fields in both MySQL and PostgreSQL) but it seems the development team is right on track and version 1.0 is coming.

So there you have it, there's still hope and we'll be seeing more mature database modelling tools in the open source arena soon.

Oh, and if you know of any other let us know.

Update September 19: Sean just told me that native types auto_increment in MySQL and serial in PostgreSQL are supported in the latest versions of Power*Architect.

We Need Standards For HTML Email

I've been using Campaign Monitor for a long time and I've just started working with them to improve the Drupal module I wrote a few months ago.

David Greiner and everybody at Campaign Monitor are doing a great job, specially promoting the use of web standards for email, and that's why I'm writing this article. We, professional web developers, must work to get email client vendors to fully support standard HTML and CSS in their products.

At 456 Berea St. Roger Johansson has already made the same call and I totally agree with him.

So, what you're waiting for? You can start by helping to define a baseline to support web standards in email.

Developing In The Age of Facebook and Second Life

I've been in the social networking scene for some time already, I know the power of connecting with the right people and I love to be a part of the online society, gosh, I have a good life thanks to that! but I didn't realize how big all the Facebook frenzy was and the many new opportunities there are for us, web developers, until I started playing with the Facebook Platform.

A cool project in which I'm currently working involves some Drupal modules talking to Facebook and it's a great opportunity to explore new directions in the development of online projects.

As many of you may know (let's imagine you're a regular reader, if you aren't what are you waiting to grab the feed?), I'm a big fan of Second Life, so it was big news when I found a cool group of residents in Facebook, I've just joined.

I'm sure we'll see many new projects involving metaverses like Second Life and social networking platforms like Facebook sprouting online quite soon. Yeah, social networking platforms, not simply sites anymore, Facebook have changed it all.

There're plenty of opportunities for people creating online experiences and if you thought there were already too many fields in the web development arena think again, soon we'll see Facebook apps experts and Second Life experiences builders along with Drupal hackers in web teams.

Gosh!, these are really exciting times for those of us who love this stuff.

So, where do you think you're heading as a web developer? If we can call ourselves that way any longer.

Better Control Over Drupal Modules Running Order

If you've been a Drupal developer for a while you know that modules run according to the value of the weight field in the system table. You can update this table using an install file so your module runs exactly when you need it. This is what community tags does to run after tagadelic and the method I've used to make sure some form_alter code runs after everything else when I needed to modify event forms.

But the change in the module's weight affect all the hooks in it. What happens if you want to control the runnning order in a per hook basis? I needed to do this to run some nodeapi operations in a custom module before event_nodeapi and form_alter stuff, in the same module, after event_form_alter. Believe me, you may need this some time (unless you opt for using before and after modules).

This idea inspired me to code a bit and individually override the operations in event_nodeapi with some code in my custom module. It worked nice. Now I can run operations load and view in event_nodeapi and override insert and update with code from my module.

The concept is quite simple. I added an event_nodeapi_overriders table with two fields: module and op. My module just needs to use its install file to insert values in this table. If I want mymodule to provide overriders for insert and update operations in event_nodeapi I should add the following module/op pairs to the table: mymodule/insert and mymodule/update.

Then I added a few select boxes to show the values from event_nodeapi_overriders table in the event settings page where I can choose which module should override which operation and used some conditions with variable_get() in event_nodeapi and mymodule_nodeapi to decide which code to run.

Perhaps the concept could be extended to eventually have a matrix of all hooks in all modules or a hook_weight parameter to allow developers to decide the running order. Or maybe is too much overhead?

Anyway, this was some kind of experiment and even if after talking to Gerhard we decided to update the event module to use Form API in the submission process instead, the idea of having better control over hooks running order could deserve some more thinking.