learnbythedrop's blog

If you're like most people who build using Drupal you want to build your site around nodes, that have titles, descriptions, tags, comments, etc. It's no surprise since those types of content oriented sites are the ones that make the world of the web go round. But what about another type of site that focuses not on nodes but on data contained in the Drupal database? Can you do it? Yes. Should you do it? Only if that sort of thing excites you or perhaps if a client requests it.

I'll give you a little background on where I'm coming from and what made me decide to play around with Drupal as a way to expose data sets. About a decade ago I was part of a group of people who built a first of its kind application to send and receive data from wholesale power markets in North America. The application was built on three tiers: a client, an application server, and a database server. The client and application server were written in Java and the database relied on Oracle technology. It was about as far away from open source as you could get. The product was successful though and we did hundreds of installs all across North America. What the product basically did for its owners was suck in data from a central server then expose that data to the user via tables and charts visible from the user interface. At some point last year I started wondering if I could do something similar with Drupal. So I started investigating, tried out different modules and realized that I could do even more with a Drupal install than we did with that product.

There are four tasks that need to be accomplished if you want to use Drupal as an effective data platform.

1. Capture data to the Drupal database tables
2. Setting the data types (text, numeric, etc.)
3. Expose the data to the user using tables and charts
4. Offer simplified download format options

 Capturing Data

I have been capturing data using the Table Wizard module. Table Wizard allows you to create a database table by uploading a delimited (like tab or comma separated) file. This works pretty well. I recently uploaded a file with over 300,000 records and it was in the database within a few minutes. After you upload your file you click on the table name to "analyze" the table. Table Wizard will let you know what columns you have in the table, the data types and identify the primary key. When you first upload your data you will want to go switch over to your database to change data types of the appropriate ones and identify a primary key. Once you have done that you can come back and re-analyze the table. 

Setting the Data Types

This is probably the step that will be the least welcome to those who are not familiar with databases. If you want to do extensive work with data in Drupal then you will want to become familiar enough with phpMyAdmin and MySQL data types to be able to navigate to your database and apply the appropriate settings for each type of data. You should also indicate which field contains the primary key for the table. The primary key is a unique value that identifies each record in a table. If you have a dataset that does not have a column with unique values then consider adding a column unique numeric code in each row. You can name the column something like record_id and then add values like 100001, 100002, 100003 and so on.

Expose the Data Using Tables and Charts

If you have been a good boy or girl and added a single field primary key then the Table Wizard module will let you check a box that is labeled, "Provide default view." This option automatically creates a view that you can expand upon as you see fit. Auto created table wizard views will have the tag "tw" added to them. You can also create a view by navigating to /build/views/add and looking for the radio button in the 'View Type' section that starts with Database table. At this point you need to a thing or two about the Views module to go further. There's not enough space to cover that here so I'm going to skip over the details and point you to the key modules and settings.

The standard views module will let you create a table from your data. You just have to choose a Page display and choose the Table style. You can then choose your fields which will appear as columns within the table. Give the page a Path and perhaps add an Exposed Filter or two to allow users to narrow down the data they are looking at. If you want to get a little fancy you can choose use Views Calc to create tables that include calculations like min, max or average on a set of numeric data.

Charts are a little more tricky but it can be done with the assistance of Charts and Graphs and Views Charts. Charts and Graphs allows you to integrate a number of different free charting solutions (including Google Charts) into your site. View Charts makes those features available as a Style (called "Charts") within the Views interface. The chart gets exposed on a page based on the Path that you define. You can also create a chart within the Block display type. There are other charting modules that exist for Drupal. All of them have some drawbacks but so far the Charts and Graphs/Views Charts combo has worked the best for me.

Offering A Download Option

The ability to download data probably won't be a must have feature for every site. After all someone could probably just copy the table and paste it into their favorite spreadsheet program. It's a nice feature to offer though and you can do it pretty easily with the help of the Views Bonus Pack module. With Views Bonus Pack installed you get the option to add a Feed display within the Views interface. With the Feed display selected you then get the option of setting a Style that includes the option for CSV and other popular formats like TXT, DOC, XLS and XML. You can then attach that display to the Table display and there will be an image that appears at the bottom of the table page that will generate a file download when clicked. Be sure to add the same filters to the CSV display that you have added to the Table display so the downloaded file reflects what the user is seeing with any filters selected. 

The usual caveats apply to the ideas and tips that I've offered here. At any time a better module could come along or an existing module could change and alter the process a bit. So tread lightly and do some exploration before you commit to building a data driven site using Drupal. In fact, even though there was a very recent release of Table Wizard the development is being deprecated in favor of the Data module. I'll continue to upload using Table Wizard for now since I think it works well but also plan on giving the Data module a shot in the near future. 

As always I'd love to hear your thoughts and tips on modules and workflows in the comments. If you have built or know of a good data access website built using Drupal feel free to share those links as well.

For the past couple of months I've had my hands on an Apple iPad and one of the first things I was interested in trying out was how effectively I could manage my Drupal sites with the device. Since I didn't want to jump to hasty conclusions about how good or bad the experience is I've waited all this time to write about it. In general I'll say that it's not a bad experience. The larger screen makes managing your site much more realistic than on the iPhone which I think is nearly an impossible task because of the small screen size. With that said you won't want to sound much time engaging in hard core site building on the iPad either.

Here's what you can do. You can access any menu item even with the Administration Menu module that places a bar at the top of the screen. The drop down menus work fine so you can navigate around very quickly. You can enter text anywhere there is a plain text box. HTML formatting must be accomplished by hand. I haven't tried it yet but one of the modules that adds the correct code for you at the press of a button might make things easier. It's on my to do list to try that. You can move around the administration section and change settings pretty easily. Comment moderation and responding is also very doable especially if you don't rely on a rich text editor. You can create basic blocks and nodes (I'm creating this post on my iPad) as well but the limitations I'll mention in a moment make it a frustrating task for anything beyond basic text entry.

Here's what you can't do. You can't use a WYSIWYG editor in a text area. You can work around that by making sure the "switch to plain text editor" link is available. That allows you to enter plain text but also wipes out the line breaks once you preview the node. So your nodes or blocks will have limited formatting. You also can't access your local file system which means that you can't upload attachments or images. So the expectation should be that you can do basic editing and content creation but nothing fancy. I use the Popups API to make things easier when managing blocks and comments. I've found that the resulting popups don't scroll so that module is not very helpful if you're on the iPad. You also can't scroll within or resize the text input area which makes creating long nodes very difficult. In fact nothing that usually involves clicking and dragging, or hovering the mouse works as far as I can see. In general the experience of managing Drupal via the iPad is a mixed bag. You can get to your site settings, manage comments and create basic nodes and blocks. Since I do my most serious managing of the site at home this level of access works fine for me. I'd like to upgrade the experience though. I suppose it's on Apple to offer access to the file system and perhaps upgrade mobile Safari to work with WYSIWYG editors. Even Google Docs doesn't work yet so we're not alone there.

The bottom line is that there are limitations that prevent anyone from doing serious development on a Drupal site via the iPad. So if you have your heart set on building and creating content on a light weight device then a netbook is a much better choice. So that's my take. If you have suggestions for work arounds, modules or helpful apps I'd love to hear about them in the comments.

Here are some screenshots that I added to this post after creating it on the iPad. From left to right they show what it looks like when editing a block, activating a popup dialog and editing a comment entry, all in landscape mode. I like the landscape mode better for management because it allows me to see the appropriate areas of the screen close up and the keyboard size is much more usable.

  

This short video shows you how to create a cron job using the Dreamhost web panel that will allow you to execute cron on a Drupal site. As I have noted previously execution of the cron file is an important task for any Drupal site. Many modules rely on cron for periodic processing of data including the core search and aggregator modules. Running cron also prompts the system to check for module updates. 

Here's what I entered in the video. Be sure to replace the url with url of your site. wget -qO /dev/null http://yoursite.com/cron.php

This video is one in a series of videos I created called Getting The Most Out Of Dreamhost. If you're a current Dreamhost customer you probably already know how to do things like set up a domain or create a MySQL database so it may not be relevant to you. If you're considering Dreamhost I think the series offers a very good preview of how the web panel functions make it easy to launch new Drupal sites. There's also a discount code (LEARNDRUPAL2010) on that page that will save you $50 on a new Dreamhost web hosting account.

Note: Click the 'full screen' icon (to the left of the volume control) in order to watch online at full 1280x720 resolution.

The first quarterly (hopefully ) Drupal meetup for the Albany, NY group took place last Saturday. Seven people from the upstate New York area attended and there was a lot of really good discussion about Drupal. Here's a summary of some of the things that we discussed.

Drupal for Educators

One member of the group was interested in using Drupal in an education setting. There was a discussion of whether or not Joomla or Drupal would best meet this person's needs. It's not surprising that we all felt Drupal was a great choice. That said I think it's important to be able to clearly and concisely make the value proposition for Drupal vs. other platforms such as Joomla and WordPress. Like anything else if we want Drupal to grow in a competitive environment then we need to know how to sell Drupal.

I recommended joining the Drupal in Education group for active discussion with people who are using Drupal for education on a daily basis. I also recommended checking out Moodle Integration which can add learning management features to a Drupal site. After the meeting I also discovered another Drupal 4 Educators site. There are also plenty of sites to be found via a Google search on "Drupal, Education."

Authentication and Identity

This was a topic that I initiated and lead the discussion on and it was inspired by my recent post about my Adventures in Authentication. This is a topic that everyone is interested in but for different reasons. From an educational point of view it seems that Facebook authentication is most important. There's also the issue of schools only wanting verified students and teachers to be able to sign up. Both security and process are very important. My needs are more general. I want to widen access to site features, build community and also cut down on spam and trolls. 

One very good point was made about consistency of the various APIs made available by the social services. It's hard to have consistent authentication and sharing functionality across services (Twitter, Facebook, Google, LinkedIn, etc.) because of differing API functionality. So while you may need different code in order to connect with each service you will also have different feature sets made available. 

Multi Site Support

I have received a number of queries on this site about how to run multiple domains off of a single Drupal install. This is something you might do if you have yoursite.com and want to also add community.yoursite.com, and allow visitors to have the same account across both sites. The Domain Access module was mentioned as a very good module to use for this purpose. A recent Lullabot blog post shows you how to do it without using a contributed module.

Code Management

Code management is very important for those who are managing very complex sites and maintaining multiple environments. For example, a site administrator might have environments for development, test and production. It's a challenge to keep track of code in those situations. The challenges become even greater when you have multiple sites to manage with multiple environments for each site. Drush was mentioned as a tool to help streamline the module update process. I'm drawing a blank on some of the other tools discussed (Note to self: Take notes next time!) but I found a discussion on Drupal groups called Code Deployment and Build Management that includes a few links to useful contributed modules.

Other Topics

The subject of hosting was discussed and most agreed that shared hosting is for the birds. People differed on their preference for hosting providers. Nothing new there. Virtual Private (or Virtual Dedicated) Servers are coming down in cost (Media Temple, $50/mo. | Dreamhost, $25/mo.) and the performance improvements are worth it. When talking hosting I mentioned Acquia's Drupal Gardens which is now in private beta as an option for those who want to get started quickly with minimum technical knowledge. I also introduced the group to the newly released Fusion themes for Drupal. I am using Fusion/Skinr/Acquia Prosper on Learn By The Drop and the combination has really been awesome in helping me organize and redesign the site.

Final Thoughts

Communicating with the world online is great but these offline (aka face-to-face) interactions are so crucial for networking, community building, and knowledge sharing. So I want to thank Julie Johnson from the Kinderhook Memorial Library for organizing the gathering. It's really worth the time that people put into planning and attending these types of Drupal events so consider organizing or attending one in your local area if you have the time.

I have a lot of ideas about what people want (or need) to learn when it comes to Drupal. My ideas are a function of what I want to do with Drupal and this strongly influences the things that I research, learn, then teach others. My research reveals that there are a lot more things that people want to learn about Drupal. Certainly more than I can offer personally on this site. There are many good Drupal sites and blogs out there but I also know that many people who are working with Drupal and have ideas about the platform are not blogging about Drupal for a variety of reasons. It would be nice to hear from people in that situation because I think they could bring a lot of good knowledge to the table. There are also situations where people are writing about Drupal but their site might not have the reach they would like since they spend most of their time doing other things. Either way, I'd like to be able to help these folks get their knowledge to the wider community.

What I would like to do is create a platform for people who want to write about Drupal so they can reach the wider community and do so without either having to set up their own separate website or spend an inordinate amount of time promoting their own site. At the present time (according to Google Analytics) this site receives about 75,000 page views per month from 23,000 unique visitors. We also have over 900 subscribers to the main FeedBurner Feed and syndication via Drupal Planet. So what gets written here tends to get seen by a decent audience. I'd like to give you the opportunity to get your Drupal ideas, opinions, tutorials, etc. in front of that audience. If you write something good and I decide to publish it here I will pay you for it.