Using Drupal to run an intranet

I have been looking for ways to replace a Sharepoint driven intranet with something else. The driving force behind this has mainly been one of platform compatibility. Sharepoint is great if you use Office and Internet Explorer on Windows. For all other users it is a usability nightmare.

There are lots of hosted or shrink-wrapped solutions for sale but the market of intranet solutions is now so mature that I felt there had to be open source solutions.

I found Alfresco, LifeRay and a few others and installed most of them. I was perplexed, however, by the sheer amount of features that were enabled out of the box. I really prefer something that starts off light and can then expand according to my needs. KnowledgeTree felt lighter but didn’t do much more than document management and I knew I also wanted a wiki as well as forums.

For a while I tried to integrate KnowledgeTree with MediaWiki and phpBB for a best-of-breeds solution. I couldn’t get all of the them to play nicely together and allow users to authenticate using accounts from a Windows Active Directory.

drupalorg

In the end I settled on Drupal. Why? It starts off light but has a truck-load of modules that can be added. I like the structure of the code. And it feels fresh – perhaps almost too light. I would have liked to see some professional free themes targeted for intranets, they would have helped to sell in the concept internally in competition with professional offerings.

The question of the authentication integration with Active Directory was solved very nicely by the module “Webserver authentication” and adding HTTP authentication to the web site in Apache where the web server is configured to use the bindings provided by Samba‘s WinBind. The only thing to remember is to set the Drupal administrator to the the same login name as the administrator in the Windows domain. After that you should disable the log out menu option in Drupal. The only thing remaining is to add some Javascript code to be able to provide a link to make the browser forget the cookie in order to force a relogin.

Serial temperature sensor hardware

I have been monitoring the temperature in my house using 1-wire sensors for some time. The easy way is to get a T-Sense and a LinkTH from iButtonLink. That would set you back $70 plus $15 per additional temperature sensor.

There is a cheaper option and that is to build the sensor adapter yourself and to base it on software that can send data on the 1-wire network with the require timings for the 1-wire protocol. Digitemp is a free application that can do this for Linux computers. This post is not about digitemp but rather about how to construct the hardware.

temp-schematics

temp-ds18s20

It only takes four diodes and a resistor and it is actually quite possible to make everything fit inside a D-Sub connector. The schematics is shown above. Note that only four of the nine pins on the connector are wired and to avoid any misunderstandings those four pins are 2 (RXD), 3 (TXD), 4 (DTR) and 5 (GND).

One note about how to connect the sensor. The ground and signal pins should of course be connected with the corresponding pins from the serial port according to the schematics above. In addition to that, Vdd must not be allowed to float so it should be connected to signal ground.

temp-photo

Here is a bill of materials for this construction. The article numbers are from Elfa, a Swedish distributor. The total cost for a sensor with a three meter cable runs at 137 SEK which amounts to roughly €12 (tax not included).

Item Description Article Price
D1 6.2V Zener diode 1N5234 70-054-08 1.34
D2 & D3 Schottky diode 1N5818 70-102-67 3.47
D4 3.9V Zener diode 1N5228 70-053-58 1.37
R1 Resistor 1.5kΩ 60-724-66 3.89
Sensor DS18S20 73-775-00 62.50
D-Sub 9 poles female soldered 44-055-02 9.61
D-Sub cover 44-130-19 12.90
Cable LiHCH 2×0.25mm2 55-780-00 10.40/m
Heat-shrinkable tubing 4.8mm 55-058-23 7.61/m

Saving a clip from YouTube

It’s gearing up to be a very nice spring for all die-hard fans of Depeche Mode. There are clips on YouTube of their next single – Wrong – and it totally rocks!

dm-wrong

So, how to get this onto my iPhone to be able to pass the time until April 20th when their next album “Sounds of the Universe” is to be released?

It turns out it to be quite easy. There are a number of Firefox add-ons that claim to be able to download content from YouTube. I imagine YouTube isn’t too happy about people downloading content and keep trying to shut down options to circumvent it. I found that the add-in “Fast Video Download” did wonders. The add-on adds the icon in the bottom-right corner of Firefox in the screenshot above.

isquint

The output from the add-on is the Flash video file with a .flv extension. This then needs to be converted to avi format. Now there are many applications that claim to do this and they all charge around $30-40. iSquint does a terrific job and it is for free. The applications has actually been discontinued but it is still available for download.

Garmin Edge 705

Thanks to my brother I have taken up biking and will, together with him, participate in Vätternrundan, the traditional Swedish bike race over some 300 km which takes place on the second Saturday of June every year.

Little did I realise just how much of a gadget sport this is. I have now settled for a Nishiki Competition Carbon, an indoor trainer and some racing shoes.

To be able to track my progress, I also wanted to get a biking computer. My short list included various bike computers from Polar, Suuntu and Garmin. I ditched Polar and Suuntu when I read that they have poor Mac support. The question that remained was which Garmin model and in the end I settled on the Garmin Edge 705 with the biking kit.

Edge 705

Edge 705 sensors

Garmin Edge 705 with the biking kit comes with pulse, cadence and speed sensors. It also has a built-in GPS.

After having used it for a few weeks on my trainer I think it has a lot of nice features and it actually works quite well with my Mac for day to day activities like syncing exercises with the Garmin Connect online service. A Windows computer is required to manage the maps so before the snow melts and the roads are cleared of sand I will have to dust off an old Windows computer and fix that.

The interface is just as non-intuitive as one could expect from a non-Apple device, but it is not too bad. There are some things, however, where Garmin don’t seem to have finished their testing. One such thing is the handling of metric/imperial units. One of the first things I changed was to set it to use metric units. Even so, the preset distances for exercises are in multiples of miles, not kilometers. Fix it Garmin!

Garmin Connect

The online service Garmin Connect is a nicely designed solution that can play back old exercises. It reminds me somewhat of the Nike+ site. My only worry is that it appears a tad slow and I hope Garmin has designed and dimensioned it so that they do not run into the roof when more people start uploading their exercises.

All in all, this is a nice product that does the job. As far as cycling computers go I would give this a 4/5 rating. An easier and more consistent GUI and 100% Mac compatibility are the things I would like to see improved.

Accessing an Oracle database from Perl

In the previous post I described how to install the Oracle Database 10g Express Edition on Ubuntu and to add some data. In this article I will show how to access that data from a Perl script.

I assume you already have Perl installed, together with the DBI framework for generic database support. We will need to add DBD::Oracle which is available from CPAN. To install this module, run this as root:

# perl -MCPAN -e shell
cpan> install DBD::Oracle

Two things are worth noting. First, the root user must have the same Oracle environment set as described in the previous article. Just issue ‘source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh’ to fix that. The second thing to note is that you will most likely get errors during testing of the newly built module which will prevent it from being installed. To override that you will need to add ‘force’ before the command, i.e.:

cpan> force install DBD::Oracle

Now, the groundwork is done and we just need to write the script. Save this in oracle_read.pl and make it executable.

#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect( 'dbi:Oracle:xe',
      'scott',
      'tiger',
      ) || die "Database connection not made: $DBI::errstr";

my $sql = qq{ SELECT id,name,age FROM persons };
my $sth = $dbh->prepare($sql);
$sth->execute();
my($id, $name, $age);
$sth->bind_columns(\$id, \$name, \$age);

print "List of persons:\n";
while( $sth->fetch() ) {
    print "$name [$age]\n";
}
$sth->finish();
$dbh->disconnect;

Oracle under Ubuntu

I am a rather proficient user of MySQL but I recently needed to set up an Oracle database to test against. Since I haven’t worked with Oracle databases in more than ten years it took literally hours to get something up and running. What follows is a description on how to install Oracle XE on Ubuntu 8.10, add a user, create a table and then drop the created schema. Everything will be done from the command line.

Note that this installs the Oracle Database 10g Express Edition. Although free it comes with similar constraints as the Microsoft SQL Server 2008 Express server – maximum 4 GB data, use maximum 1 GB of RAM and run on maximum 1 CPU.

First, add the Oracle repositories by appending the following two lines to /etc/apt/sources.lst

# Oracle Repository

deb http://oss.oracle.com/debian unstable main non-free

Then, we must add the Oracle key to avoid warnings:

wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | sudo apt-key add -

Now we are ready to install by using apt-get (either run the following as root or prepend every line with sudo)

# apt-get update
# apt-get install oracle-xe oracle-xe-client
# /etc/init.d/oracle-xe configure

The last command will present options to change some default settings. you can probably use most of the defaults. I just changed the HTTP port from 8080 since I already had another service running on that port. Note that the configuration script takes a very long time to finish.

When the script is finished it is possible to access the Oracle web interface at http://127.0.0.1:8080/apex (or some other port if you changed the default). However, it will only be accessible on the local host. If you are installing on a headless remote server like me you can port-forward using SSH:

$ ssh -L 8080:127.0.0.1:8080 user@host

Or, you can remove this limitation as described further below.

Next step will be to add the Oracle environment to your shell. The installation will have added scripts to set up the environment under /usr/lib/oracle/xe/app/oracle/product/10.2.0/. There are actually two scripts: server/bin/oracle_env.sh and client/bin/oracle_env.sh. I don’t think it matters which one but it makes more sense to use the one under ‘server’.

Append this to your .bash_profile file:

. /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh

Then log out and back in – or run ‘source .bash_profile’.

Now it is time to log onto the system:

$ sqlplus system@localhost 

If you would have logged on remotely you would have written ‘sqlplus system@host’ – but we haven’t enabled remote access yet.

Type the password you selected during installation. At the SQL prompt, enter:

> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
> EXIT;

Now it is time to add the first user. Save the following to a file (oracle_create.sql).

-- oracle_create.sql
CREATE USER scott IDENTIFIED BY tiger 
    DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp 
QUOTA UNLIMITED ON users;
CREATE ROLE myrole;
GRANT CREATE session, CREATE table, CREATE view, 
    CREATE procedure, CREATE synonym TO myrole;
GRANT myrole TO scott;
-- Switch user
CONNECT scott@localhost/tiger;
--
CREATE TABLE persons (
	id int,
	name varchar2(32),
	age number
);
--
INSERT INTO persons
	(id, name, age)
	VALUES (1, 'Joe', 35);
INSERT INTO persons
	(id, name, age)
	VALUES (2, 'Mary', 32);

You can then create the user and populate the table by running:

$ sqlplus system@localhost
> @oracle_create
> SELECT * FROM persons;
> EXIT

The ampersand indicates that SQL statements should be read from a file. Since the file ends with .sql the extension does not have to be stated. Please note the connect statement in the SQL file. This means that after that point we will be running as the user scott.

To drop everything we have created run the following:

$ sqlplus system@localhost
> drop user scott cascade;
> drop role myrole;

CSS-based rounded corners

Having rounded corners in web layout is usually a nice touch, whether it be menu tabs, boxes or whatever. Way back when the layout was done using HTML tables this could be achieved with images and a bunch of extra table cells. Ugliest solution there ever was but it worked. The last few years I have been using Javascript libraries such as NiftyCube to achieve more or less the same effect.

In CSS3 there are finally options for creating rounded corners. It is worth noting that CSS3 is still under development and that this technique only works under Firefox and Safari – but since they are the browsers I use anyway it is almost good enough for me already.

Users of Firefox and Safari should see this text in a blueish box with rounded corners. Users of other browsers will see a box with square corners.

The HTML code for the box above is

<div style="width:300px;
background-color: #ddddff;
-moz-border-radius: 15px;
-webkit-border-radius: 15px;
border: 4px solid #8888ff;
padding: 10px;" >

The added options are (for the two browser families):

-moz-border-radius: 5px;
-webkit-border-radius: 5px;

It is also possible to round only certain corners by using a combination of the following:

-moz-border-radius-topleft
-moz-border-radius-topright
-moz-border-radius-bottomleft
-moz-border-radius-bottomright
-webkit-border-radius-topleft
-webkit-border-radius-topright
-webkit-border-radius-bottomleft
-webkit-border-radius-bottomright

Configure postfix to use smart host

My ISP filters outgoing SMTP traffic so to be able to run a mail server running at home I need to use the ISPs SMTP server to relay outgoing email. If they would have allowed open relaying from their customers I could just have set their SMTP server as ‘relayhost’ in /etc/postfix/main.cf and be done with it.

However, they require that I authenticate to their SMTP server which complicates matter slightly. This is how I did it:

  1. Create a password file, assigning username and password to SMTP relay hosts. Create the file /etc/postfix/relay_password and edit it to have the following content (replace the hostname with whatever relay host your ISP is providing and use your password and login)

    smtp.bredband.net :
  2. Change permissions for the credentials file and create a map file

    # chown root:root /etc/postfix/relay_password
    # chmod 600 /etc/postfix/relay_password
    # postmap /etc/postfix/relay_password
  3. Update postfix configuration

    relayhost = [smtp.bredband.net]
    smtp_sasl_auth_enable = yes
    smtp_sasl_password_maps = hash:/etc/postfix/relay_password
  4. Restart postfix

    # /etc/init.d/postfix restart

Control Debian daemons

Debian and Ubuntu place startup files for all deamons in /etc/init.d. Symbolic links are then placed in /etc/rc.X to control when deamons are to be started and stopped. While it would certainly be possible to manage it manually, there are a few tools that makes life easier whenever you want to control which daemons are started by default.

Using rcconf

rcconf

Using sysv-rc-conf

sysv-rc-conf

Using update-rc.d

usage: update-rc.d [-n] [-f]  remove
       update-rc.d [-n]  defaults|multiuser [NN | sNN kNN]
       update-rc.d [-n]  start|stop NN runlvl [runlvl] [...] .
		-n: not really
		-f: force

iPhone viewport size

This information is from 2007 but every time I make a web page for the iPhone I have to go and search for it.

The iPhone Safari browser does a good job of presenting just about any web page on its relatively small display. In so doing, it assumes that whatever web page it is displaying was designed for a much bigger display. To design a web page targeted for the iPhone and not have the phone shrink the web page to oblivion one must therefore add a meta tag to the head section of the HTML file:

<meta name="viewport" content="width=320, user-scalable=yes">