Technology Tales

Basic string searching in MySQL table columns

Last weekend, I ended up doing a spot of file structure reorganisation on the web server for my Assorted Explorations website and needed to correct some file pointers in entries on my outdoors blog. Rather than grabbing a plugin from somewhere, I decided to edit the posts table directly. First, I needed to select the affected observations and this is where I had to pick out the affected rows and edit them in MySQL Query Browser. To accomplish that, I needed basic string searching, so I opened up my MySQL e-book from Apress and constructed something like the following:

select * from posts_table where post_text like '%some_text%';

The % wildcard characters are required to pick out a search string in any part of a piece of text. There may be a more sophisticated method, but this did what I needed in a quick and dirty manner without further ado. Well, it was what I needed.

Shell swapping in Windows: PowerShell and the legacy command prompt

Until the advent of PowerShell, Windows had been the poor relation when it came to working from the command line when compared with UNIX, Linux and so on. A recent bit of fiddling had me trying to run FTP from the legacy command prompt when I ran into problems with UNC address resolution (it's unsupported by the old technology) and mapping of network drives. It turned out that my error 85 was being caused by an unavailable drive letter that the net use command didn't reveal as being in use. Reassuringly, this wasn't a Vista issue that I couldn't circumvent.

During this spot of debugging, I tried running batch files in PowerShell and discovered that you cannot run them there like you would from the old command prompt. In fact, you need a line like the following:

cmd /c script.bat

In other words, you have to call cmd.exe like perl.exe, wscript.exe and cscript.exe for batch files to execute. If I had time, I might have got to exploring the use ps1 files for setting up PowerShell commandlets, but that is something that needs to wait until another time. What I discovered though is that UNC addressing can be used with PowerShell without the need for drive letter mappings, not a bad development at all. While on the subject of discoveries, I discovered that the following command opens up a command prompt shell from PowerShell without any need to resort to the Start Menu:

cmd /k

Entering the exit command returns you to the PowerShell command line again, and entering cmd /? reveals the available options for the command, so you need never be constrained by your own knowledge or its limitations.

Removing a column from a MySQL data table

My trying out WordPress 3.0 in advance of its final release has brought me errors on the management page for website links. After a spot of poking around the TRAC, I found that the bug already has been reported and that the cause is an extraneous column in the *_links table called link_category. The change in taxonomy handling over the years seems to have made it redundant, so I removed the said column from the database using a command like the following from both the MySQL command line and MySQL Query Browser:

alter table database.wp_links drop link_category;

That seems to have made those errors go away, and I hope that their upgrade code takes care of this before WordPress 3.0 is let loose of the general blogging public. Taking out the coding brittleness would do too.

Relocating the Apache web server document root directory in Fedora 12

So as not to deface anything that is available online on the web, I have a tendency to set up an offline Apache server on a home PC to do any tinkering away from the eyes of the unsuspecting public. Though Ubuntu is my mainstay for home computing, I do have a PC with Fedora installed, and I have been trying to get an Apache instance to start automatically on there unsuccessfully for a few months. While I can start it by running the following command as root, I'd rather not have more manual steps than is necessary.

httpd -k start

The command used by the system when it starts is different and, even when manually run as root, it failed with messages saying that it couldn't find the directory while the web server files are stored. Here it is:

service httpd start

The default document root location on any Linux distribution that I have seen is /var/www and all is very well with this, but it isn't a safe place to leave things if ever a re-installation is needed. Having needed to wipe /var after having it on a separate disk or partition for the sake of one installation, it doesn't look so persistent to me. In contrast, you can safeguard /home by having it on another disk or in a dedicated partition, which means that it can be retained even when you change the distro that you're using. Thus, I have got into the habit of having the root of the web server document root folder in my home area, and that is where I have been seeing the problem.

Because of the access message, I tried using chmod and chgrp, but to no avail. The remedy has to do with reassigning the security contexts used by SELinux. In Fedora, Apache will not work with the context user_home_t that is usually associated with home directories, but needs httpd_sys_content_t instead. To find out what contexts are associated with particular folders, issue the following command:

ls -Z

The final solution was to create a user account whose home directory hosts the root of the web server file system, called www in my case. Then, I executed the following command as root to get things going:

chcon -R -h -t httpd_sys_content_t /home/www

It appears that even the root of the home directory has to have an appropriate security context (/home has home_root_t so that might do the needful too). Without that, nothing will work, even if all is well at the next level down. The switches for chcon command translate as follows:

-R : recursive; applies changes to all files and folders within a directory.

-h : changes apply only to symbolic links and not to where they refer in the file system.

-t : alters context type.

It took a while for all of this stuff about SELinux security contexts to percolate through to the point where I was able to solve the problem. A spot of further inspiration was needed too and even guided my search for the information that I needed. It's well worth trying Linux Home Networking if you need further details. Though there are references to an earlier release of Fedora, the content still applies to later versions of Fedora to the current release, if my experience is typical.

Moving application title bar buttons on GNOME desktops

A recent look at how Ubuntu 10.04 development is getting on confronted me with an interface situation to which I am not accustomed: title bar buttons at the left. The usual combination of buttons for maximisation, minimisation and closure were there in their usual order but at the left of the window. While this is the where you find them on OS X, I prefer the Windows convention and placed them to the right again.

To achieve that end, I ran gconf-editor from the command line using my usual user account (not sudo; that doesn't seem to work) and made my way to apps -> metacity -> general. Once there, I sought out the button_layout property and moved the colon in the value from the left to the right. In other words, I started with this:

maximize,minimize,close:

and changed it to this (note the position of the colon in the actual string):

:maximize,minimize,close

If you ever find yourself wanting to change things from the Windows convention to the Apple one, just reverse what I did. As an aside, you also can swap the button order too if you like. After all, it's just a text field that you can edit, and the screen immediately refreshes when you hit the Return key after completing the edit.

As a more general observation, if Ubuntu 10.04 does come out using the OS X convention for title bar button placement, I could see others like wanting it changed back and that's why I am sharing it here. Surprising users in this way, especially after the 9.10 release's attracting some adverse comments, would not be all that advisable. The issue may be easy to address, but that's small comfort when you release how easily users are discouraged.

Nevertheless, 10.04 is an LTS release and what I have seen so far looks polished; there may be no splash screen at boot and shutdown time for what I am running (I am sticking with acquiring upgrades every so often instead of periodic re-installation from a new disk image) but that's a minor matter.

For the sake of not turning over the apple cart, I may have left off VirtualBox Additions, and things look steady enough so far. In fact, I am writing these words using Firefox 3.6 on there. Accompanying that is OpenOffice 3.2, but things do not look so different apart from these, a reassuring observation. While there may be an emphasis on purple in the colour scheme at the time of writing, that could change yet. 9.10's course had plenty of that, so I am willing to be patient. After all, there's more than a month to go yet, before the final cut is available for general use.

Reading data into SAS using the EXCEL and PCFILES library engines

Recently, I had the opportunity to have a look at the Excel library engine again because I need to read Excel data into SAS. You need SAS Access for PC Files licensed for it to work, but it does simplify the process of getting data from spreadsheets into SAS. It all revolves around setting up a library pointing at the Excel file using the Excel engine. The result is that every worksheet in the file is treated like a SAS dataset, even if their names contain characters that SAS considers invalid for dataset names. The way around that is to enclose the worksheet name in single quotes with the letter n straight after the closing quote, much in the same way as you'd read in text strings as SAS date values ('04MAR2010'd, for example). To make all of this clearer, I have added some example code below.

libname testxl excel 'c:\test.xls';

data test;
    set testxl.'sheet1$'n;
run;

All of the above does apply to SAS on Windows (I have used it successfully in 9.1.3 and 9.2) but there appears to be a way of using the same type of thing on UNIX too. Again, SAS Access for PC Files is needed as well as a SAS PC Files server on an available Windows machine, and it is the PCFILES engine that is specified. While I cannot say that I have had the chance to see it working in practice but seeing it described in SAS Online Documentation corrected my previous misimpressions about the UNIX variant of SAS and its ability to read in Excel or Access data. Well, you learn something new every day.

A bigger screen?

A recent bit of thinking has caused me to cast my mind back over all the screens that have sat in front of me while working with computers over the years. Well, things have come a long way from the spare television that I used with a Commodore 64 that I occasionally got to explore the thing. Needless to say, a variety of dedicated CRT screens ensued as I started to make use of Apple and IBM compatible PC's provided in computing labs and other such places before I bought an example of the latter as my first ever PC of my own. That sported a 15" display that stood out a little in times when 14" ones were mainstream, but a 17" Iiyama followed it when its operational quality deteriorated. That Iiyama came south with me from Edinburgh as I moved to where the work was and offered sterling service before it too started to succumb to ageing.

During the time that the Iiyama CRT screen was my mainstay at home, there were changes afoot in the world of computer displays. A weighty 21" Philips screen was what greeted me on my first day at work, only for 21" Eizo LCD monitors were set to replace those behemoths and remain in use as if to prove the longevity of LCD panels and the validity of using what had been sufficient for laptops for a decade or so. In fact, the same remark regarding reliability applies to the screen that now is what I use at home, a 17" Iiyama LCD panel (yes, I stuck with the same brand when I changed technologies longer ago than I like to remember).

However, that hasn't stopped me wondering about my display needs, and it's screen size that is making me think rather than the reliability of the current panel. While that is a reflection on how my home computing needs have changed over time, they also show how my non-computing interests have evolved too. Photography is but one of these and the move to digital capture has brought with a greater deal of image processing, so much that I wonder if I need to make less photos rather than bringing home so many that it can be challenging to pick out the ones that are deserving of a wider viewing. Though that is but one area where a bigger screen would help, there is another that arises from my interest in exploring some countryside on foot or on my bike: digital mapping. When planning outings, it would be nice to have a wider field of view to be able to see more at a larger scale.

None of the above is a showstopper that would be the case if the screen itself was unreliable, so I am going to take my time on this one. The prospect of sharing desktops across two screens is another idea, one that needs some thought about where it all would fit; the room that I have set aside for working at my computer isn't the largest. After the space side of things, then there's the matter of setting up the hardware. Quite how a dual display is going to work with a KVM setup is something to explore, as is the adding of extra video cards to existing machines. After the hardware fiddling, the software side of things is not a concern that I have because of when I used a laptop as my main machine for a while last year. That confirmed that Windows (Vista, but it has been possible since 2000 anyway...) and Ubuntu (other modern Linux distributions should work too...) can cope with desktop sharing out of the box.

Apart from the nice thoughts of having more desktop space, the other tempting side to all of this is what you can get for not much outlay. It isn't impossible to get a 22" display for less than £200 and the prices for 24" ones are tempting too. That's a far cry from paying next to £300 (if my memory serves me correctly) for that 17" Iiyama, and I'd hope that the quality is as good as ever.

It's all very well talking about pricing, but you need to sit down and choose a make and model when you get to deciding on a purchase. There is plenty of choice so that would take a while with magazine reviews coming in handy here. Saying that, last year's computing misadventures have me questioning the sense of going for what a magazine places on its A-list. They also have me thinking of going to a nearby computer shop to make a purchase rather than choosing a supplier on the web; it is easier to take back a faulty unit if you don't have far to go. Speaking of faulty units, last year has left me contemplating waiting until the year is older before making any acquisitions of computer kit. All of that has put the idea of buying a new screen on the low priority list, nice to have but not essential. For now, that is where it stays, but you never know what the attractions of a shiny new thing can do...

Easier to print?

One matter that really came to light was how well or not the pages on here and on my hill walking and photography website came out on the printed page. After spotting a WordPress Codex article and with an eye on improving things, I have made a distinction between screen and print stylesheets. The code in the XHTML looks like this:

<link rel="stylesheet" href="/style.css" type="text/css" media="screen" />
<link rel="stylesheet" href="/style_print.css" type="text/css" media="print" />

The media attribute seems to be respected by the browsers that I have been using for testing (latest versions of Firefox, MSIE and Opera) so it then was a matter of using CSS to control what was shown and how it was displayed. Extraneous items like sidebars were excluded from the printed page in favour of the real content that visitors would be wanting anyway, and everything else was made as monochrome as possible, with images being the only things to escape. After all, people don't want to be wasting paper and ink in these cash strained times, and there's no need to have any more colour than necessary either. Then, there's the distraction caused by non-functioning hyperlinks that has inspired the sharing of some wisdom on A List Apart. Returning to my implementation, please let me know in the comments what you think of what I have done on here and if there remains any room for improvement.

Best left until later in the year?

In the middle of last year, my home computing experience was one of feeling displaced. A combination of a stupid accident and a power outage had rendered my main PC unusable. What followed was an enforced upgrade that used a combination that was familiar to me: Gigabyte motherboard, AMD CPU and Crucial memory. However, assembling that lot and attaching components from the old system from the old system resulted in the sound of whirring fans but nothing appearing on-screen. Not having useful beeps to guide me meant that it was a case of undertaking educated guesswork until the motherboard was found to be at fault.

In a situation like this, a better developed knowledge of electronics would have been handy and might have saved me money too. As for the motherboard, it is hard to say whether it was a faulty set from the outset or whether there was a mishap along the way, either due to ineptitude with static or incompatibility with a power supply. What really tells the tale on the mainboard was the fact that all the other components are working well in other circumstances, even that old power supply.

A few years back, I had another experience with a problematic motherboard, an Asus this time, that ate CPU's and damaged a hard drive before I stabilised things. That was another upgrade attempted in the first half of the year. My first round of PC building was in the third quarter of 1998 and that went smoothly once I realised that a new case was needed. Similarly, another PC rebuild around the same time of year in 2005 was equally painless. Based on these experiences, I should not be blamed for waiting until later in the year before doing another rebuild, preferably a planned one rather than an emergency.

Of course, there may be another factor involved too. The hint was a non-working Sony DVD writer that was acquired early last year when it really was obvious that we were in the middle of a downturn. Could older unsold inventory be a contributor? Well, it fits in with seeing poor results twice, In addition, it would certainly tally with a problematical PC rebuild in 2002 following the end of the Dot-com bubble and after the deadly Al-Qaeda attack on New York's World Trade Centre. An IBM hard drive that was acquired may not have been the best example of the bunch, and the same comment could apply to the Asus motherboard. Though the resulting construction may have been limping, it was working tolerably.

In contrast, last year's episode had me launched into using a Toshiba laptop and a spare older PC for my needs, with an external hard drive enclosure used to extract my data onto other external hard drives to keep me going. While it felt like a precarious arrangement, it was a useful experience in ways too.

There was cause for making acquaintance with nearby PC component stores that I hadn't visited before, and I got to learn about things that otherwise wouldn't have come my way. Using an external hard drive enclosure for accessing data on hard drives from a non-functioning PC is one of these. Discovering that it is possible to boot from external optical and hard disk drives came as a surprise too and will work so long as there is motherboard support for it.

Another experience came from a crisis of confidence that had me acquiring a bare-bones system from Novatech and populating it with optical and hard disk drives. Then, I discovered that I have no need for power supplies rated more than 300 watts (around 200 W suffices). Turning my PC off more often became a habit, friendly both to the planet and to household running costs too.

Then, there's the beneficial practice of shopping locally, which can suffice. You may not get what PC magazines stick on their hot lists, but shopping online for those pieces doesn't guarantee success either. All of these were useful lessons and, while I'd rather not throw away good money after bad, it goes to show that even unsuccessful acquisitions had something to offer in the form of learning opportunities. Whether you consider that is worthwhile is up to you.

Sometimes it's a small change that matters...

Firefox 3.6 is now available and others are going on about more striking features, but it's a small change that I have noticed, which happens to be a good one too. Middle-clicking on a link in a tab used to open a new one on the right hand of the tab bar. Now, the new tab opens next to the one where the click was clicked, which is a good thing if you are previewing blog posts. It was something that Internet Explorer already did, so it's good to see cross-fertilisation of useful features; yes, Microsoft can come up with good ones too from time to time. Though not likely to make major headlines, this is the type of thing that makes for a better user experience, and a few of them together can be more beneficial than some big shiny new feature. In life, it's often the little things that make all the difference.