I was working on ResourceBlender last week trying to find a way to get the data from multiple rows in SQL. I thought I’d have to loop through programatically and abuse Dictionaries and HashTables until I found out about a lesser known and at first confusing feature of SQL known as cross tabulation.

The data I needed contained translations. I have a table for elements and a table for translations, and each translation can be used by multiple elements.

mysql> SELECT elementname, LANGUAGE, translation, elements.translationid 
FROM elements 
	INNER JOIN translations ON translations.translationid = elements.translationid 
WHERE elements.translationid 
	BETWEEN 24 AND 25;
+-------------+----------+-------------------+---------------+
| elementname | LANGUAGE | translation       | translationid |
+-------------+----------+-------------------+---------------+
| PrevWeek    | de-DE    | Vorherige Woche   |            24 |
| PrevWeek    | en-GB    | Previous Week     |            24 |
| PrevWeek    | es-ES    | Semana anterior   |            24 |
| PrevWeek    | nl-NL    | Vorige week       |            24 |
| PrevWeek    | ro-RO    | Saptamana trecuta |            24 |
| PrevMonth   | cs-CZ    | Předchozí rok   |            25 |
| PrevMonth   | de-DE    | Vorheriger Monat  |            25 |
| PrevMonth   | el-GR    | Προηγ. Ετος|            25 |
| PrevMonth   | en-GB    | Previous Month    |            25 |
| PrevMonth   | es-ES    | Año anterior     |            25 |
| PrevMonth   | fr-FR    | Préc. An         |            25 |
| PrevMonth   | it-IT    | Anno prec.        |            25 |
| PrevMonth   | nl-NL    | Vorig jaar        |            25 |
| PrevMonth   | pl-PL    | Poprzedni rok     |            25 |
| PrevMonth   | pt-PT    | Ano anterior      |            25 |
| PrevMonth   | ro-RO    | Luna trecuta      |            25 |
| PrevMonth   | ru-RU    | Предыдущий год|            25 |
| PrevMonth   | sl-SI    | Prejšnje leto    |            25 |
| PrevMonth   | sv-SE    | Föreg. År       |            25 |
| PrevMonth   | tr-TR    | րnceki Yýl      |            25 |
| PrevMonth   | zh-Hans  | 上一年         |            25 |
+-------------+----------+-------------------+---------------+
21 rows IN SET

Read more »

If you have a page which uses a master page, then put controls inside a ContentPlaceHolder on the sub page, accessing the values of the controls can be difficult if you’re posting the form to another page. The problem is, the ContentPlaceHolder mangles the control names so instead of radApplication, you get ctl00$cntMain$radApplication, meaning you can’t read them from Request.Form.

Hardcoding the name of the placeholder would be a bad idea incase you ever change the name of it or change the master page. There are actually two ways you can get at the controls.

Read more »

After someone suggested a way to match URLs and protocols with wildcards in LockCrypt, I started work implementing a URL which accepted wildcard (*) characters. The result is a class which takes a URL string as a constructor and breaks it apart into it’s component parts. The class is based on a JavaScript regex from Steve Levithan.

The full specification for the URL it constructs is protocol://user:password@host:port/direc/tory/file?query#ref. Any parts left blank are assumed to be wildcards. Performance averages out to about 0.05ms per check, not bad for a beefy regex.

Just the constructor is listed here, the full source is available at http://leghumped.com/WildcardURL.java.

Read more »

I’ve spent the last half an hour tweaking this so it works. This batch file will loop through all files in the source directory with the specified extension and add each one to an individual .rar file with maximum compression.

As it is, it will add all files the the extension nds in the current directory to a new subdirectory called zipped.

Read more »

If you’ve ever needed a static method to load a resource, there’s a lot of conflicting information about how to get a resource contained in your JAR. I tried SomeProg.class.getClassLoader() and a few others but ended up with either a null value or an exception. Eventually on a whim I tried the following, which worked.

Read more »

I recently switched from WordPress to Movable Type for my software blogs. This site still runs WordPress because there are too many extensions which I need. My software blogs though, were just more or less the same site with different colours and about different products. I only use them for posting new release info and it was too much of an ordeal to update five WordPress blogs everytime an update was released.

One of the more annoying things about Movable type is the URL structure it uses by default. Everything is statically published by default, which works quite well and isn’t a problem in itself, but there’s something about seeing a .html or .php extension which makes a site look amateurish.

There’s an easy way to change Movable Type to use permalinks like http://leghumped.com/post-name instead of http://leghumped.com/2008/09/post-name.html

Read more »

DreamHost have been offering Subversion repositories with their hosting packages for the last couple of months, and if you're a developer, a central repository can be a useful thing to have.

Although DreamHost have a simple form for setting up a new repository and they make regular backups incase a hard drive fails, I still have a lingering fear of losing all of my source code. Luckily, DreamHost also offer SSH access and allow you to setup cron jobs to backup your data yourself. This guide provides instructions for creating a shell script to backup, compress, email and restore your SVN repository.

The tools you'll need to export a repository are svnadmin, tar, split, mutt - an email client, and optionally crontab.

Read more »

When Microsoft were deciding what to include in the .NET Compact Framework, they decided to restrict the OpenFileDialog and SaveFileDialog to the My Documents directory. There are plenty of reasons you’d need to choose a file outside of My Documents, so I coded a new file chooser from other components.

The dialog is just a form with a ComboBox which shows the current directory and all of it’s parents, a ListView which shows the files and directories in the current directory, and a text box to show the selected file.

Instantiate it with either the default constructor or a directory and file to show on startup.

FileInfo dbFile = new FileInfo("\\Storage Card\\Somefile.txt");
Lime49.OpenFileDialog dlg = new Lime49.OpenFileDialog(dbFile.DirectoryName, dbFile.Name);
dlg.Filter = "*.txt";
dlg.ShowDirectory(dbFile.DirectoryName);
dlg.ShowDialog();
MessageBox.Show(dlg.SelectedFile);

The selected file is available through the SelectedFile property, and the filter doesn’t work the same way as standard FileDialogs, it uses a standard wildcard pattern to list files.

Download Lime49.OpenFileDialog
Also published on CodeProject

Last week I posted about drag and drop in C#. The post covered pre-built controls (TreeView and ListView). There are a few things the post didn't cover, such as user controls and anything which doesn't raise the ItemDrag event.

To be able to drag a user control, the control has to call the DoDragDrop method. The logical way to do this is by tracking the state of the left mouse button. If the left button is pressed and the mouse moves, start the drag/drop.

private bool isDragging = false;
...
private void userControl_MouseDown(object sender, MouseEventArgs e) {
    this.isDragging = true;
};
private void userControl_MouseUp(object sender, MouseEventArgs e) {
    this.isDragging = false;
};
private void userControl_MouseLeave(object sender, EventArgs e) {
    isDragging = false;
};
private void userControl_MouseMove(object sender, MouseEventArgs e) {
    if(isDragging) {
        DoDragDrop(userControl, DragDropEffects.Move);
    }
};

Read more »

Drag and drop is one of the things that seems easier in C# than Java. In OnTime, the project I'm currently working on, I've used it in several places to make the UI easier to use.

The first example I want to cover is dragging between a ListView and TreeView. This could be used to make re-arranging things easier. Eg: If you're displaying categories in a tree and items in a list.

Read more »

This took a while to get working, but I got there eventually. It's used in OnTime to calculate the number of hours and minutes a user has clicked on. I have rows of horizontal lines which show hours. It's easy to extract a time from a click position, but generally people don't need that much precision and unless the grid is quite large (bigger than 1440 pixels), you won't have 1px per minute anyway.

This snippet will find the nearsest 30 minute interval, so if a user clicks ⅓ of the way between the 4th and 5th division, this would round to 4 hours and 30 minutes.

  double clickPosition = ((double)e.Y + -this.AutoScrollPosition.Y) / heightPerHour;
  int hours = (int)clickPosition;
  int mins = (int)((clickPosition - (double)hours) * 60);
  int roundedMinutes = (int)Math.Round((double)mins / 30, 0) * 30;
  double fractionalHours = hours + (double)(roundedMinutes / 60.0);

This is designed for a control which autoscrolls, so the actual position of the click is determined first. heightPerHour is the height of each row (each hour).

I thought it would be nifty to show my last Twitter update as a profile field next to all of my posts in the Lime49 forums. It took a bit longer than expected, but eventually I got it working. These instructions are specific to Dreamhost, but you could change the slightly to work on other hosts.

1. You need to change your PHPBB template to include the status next to post matching a certain criteria. I'm the only administrator on the forum, so I set a template flag which is set for administrators, but no-one else.

2. Open viewtopic.php and add the template switch will determine whether the field will be shown. I used my User ID so it wouldn’t be shown for anyone else, but you could use another variable from PHPBB.

   Find the section:

     'S_CUSTOM_FIELDS' => (isset($cp_row['row']) && sizeof($cp_row['row'])) ? true : false,
     'S_TOPIC_POSTER'  => ($topic_data['topic_poster'] == $poster_id) ? true : false,
    
     'S_IGNORE_POST'   => ($row['hide_post']) ? true : false,
     'L_IGNORE_POST'   => ($row['hide_post']) ? sprintf($user->lang['POST_BY_FOE'], get_username_string('full', $poster_id, $row['username'], $row['user_colour'], $row['post_username']), '<a href="' . $viewtopic_url . "&amp;p={$row['post_id']}&amp;view=show#p{$row['post_id']}" . '">', '</a>') : '',

   And at the end add:

     'POSTER_ADMIN'  =>  ($poster_id == 2),

3. Now open stylename\template\viewtopic_body.html and add the following to the top:

     <!-- PHP -->
     $twitterStatus = file_get_contents('/home/hjennerway/lime49.com/forums/twitter.php');
     <!-- ENDPHP -->

4. Still in viewtopic_body.html, add this to the profile section (where the status message will be shown).:

     <!-- IF postrow.POSTER_ADMIN -->
       <br />
       <dd><strong>Currently:</strong> 
         <span class="twitter"><!-- PHP -->echo $twitterStatus;<!-- ENDPHP --></span>
       </dd>
     <!-- ENDIF -->

5. Log in to the admin panel and purge the cache.

6. By default, PHP is not enabled in templates. You can enable it in the Security Settings section on the General tab.

7. Now you need a cronjob to connect to twitter, parse your last update and write it to a file. Create a new php file and modify this script to point to your PHPBB installation. Save it as twittercron.php

8. Add a line to crontab to run the script every six hours (or more, depending on how often you update your status). You'll need to change the path to the location where PHP is installed, which you can using which php.

     * */6 * * * php5.cgi /path/to/twittercron.php 1>&2 &>/dev/null

This code saves your twitter status to a file every six hours. Them stores the contents of the file in a variable, which is shown in the template.

If you have a toolstrip for navigation, you might want to have buttons on the left and the right, and a label in the centre. There’s no way to stretch the label automatically, but this snippet will resize the label to fill the rest of the ToolStrip. The only requirement is that all of the buttons are the same width.

private void CenteredLabel_Resize() {
    int remainingWidth = 144 + btnPrevSkip.Bounds.Left + 2;
    lblCenteredLabel.Width = toolStrip1.ClientSize.Width - remainingWidth;
}

You need to replace 144 with the total width of all of the other items on the ToolStrip.

Due to a quirk in the way Windows handles events, once a mouse hover event has been triggered on a windows form control, another event cannot be triggered until the mouse leaves and re-enters the control. Sometimes you might need to process more than one MouseHover event, for example if you have a user control which has draws shapes on itself. As long as you have a record of where the shapes are (by storing them in a collection), you can use the method below as a workaround.

I used this to display a tooltip, so to prevent the MouseHover event being spammed, action is only taken when my tooltip is not visible.

private const uint TME_HOVER = 0x00000001;
private const uint TME_LEAVE = 0x00000002;
private const uint HOVER_DEFAULT = 0xFFFFFFFF;
 
[DllImport("user32.dll")]
public static extern bool TrackMouseEvent(ref TRACKMOUSEEVENT lpEventTrack);
 
public struct TRACKMOUSEEVENT {
    public uint cbSize;
    public uint dwFlags;
    public IntPtr hwndTrack;
    public uint dwHoverTime;
}
 
ToolTip toolTip = new ToolTip();
toolTip.Location = new System.Drawing.Point(290, 80);
 
Rectangle currentActiveShape = new Rectangle(); // the currently active shape (over which the mouse is hovering)
 
this.MouseMove += delegate(object sender, MouseEventArgs e) {
    foreach(Rectangle rect in myShapeCollection) {
        if(rect.Contains(e.Location)) {
            if(currentActiveDay != day.Bounds) {
                toolTip.Hide();
                Console.WriteLine("Hide tooltip");
            }
            currentActiveShape = rect;
            // Set location here
            Console.WriteLine("Entered " + rect.ToString());
        }
    }
};
this.MouseHover += delegate(object sender, EventArgs e) {
    TRACKMOUSEEVENT trackMouseEvent = new TRACKMOUSEEVENT();
    trackMouseEvent.hwndTrack = ((Control)sender).Handle;
    trackMouseEvent.dwFlags = TME_HOVER;
    trackMouseEvent.dwHoverTime = HOVER_DEFAULT;
    trackMouseEvent.cbSize = (uint)System.Runtime.InteropServices.Marshal.SizeOf(trackMouseEvent);
    TrackMouseEvent(ref trackMouseEvent);
 
    if(!toolTip.Active) {
        toolTip.Show();
        Console.WriteLine("Show tooltip");
    }
};

As the mouse moves around the control, the location of the tooltip changes, but it’s not shown until the MouseHover event is triggerd.

Due to a quirk in the way Windows handles events, once a mouse hover event has been triggered on a windows form control, another event cannot be triggered until the mouse leaves and re-enters the control. Sometimes you might need to process more than one MouseHover event, for example if you have a user control which has draws shapes on itself. As long as you have a record of where the shapes are (by storing them in a collection), you can use the method below as a workaround.

This snippet will create a JTextBox and draw an image on the right hand side. It also sets the margin accordingly to prevent the user typing into that area.

JTextField myTextField = new JTextField(20) {
    @Override
    protected void paintComponent(Graphics g) {
        super.paintComponent(g);
	try {
            URL url = this.getClass().getResource("image.png");
            final java.awt.image.BufferedImage image = javax.imageio.ImageIO.read(url);
            Border border = UIManager.getBorder("TextField.border");
            JTextField defaultField = new JTextField();
            final int x = getWidth() - border.getBorderInsets(defaultField).right - image.getWidth();
            setMargin(new Insets(2, 2, 2, getWidth() - x));
            int y = (getHeight() - image.getHeight())/2;
            g.drawImage(image, x, y, this);
        } catch(Exception ignore) {}
    }
};