Jumping to a particular record in MS Access

I have been working with Microsoft Access for a while but I had run up against a problem that took me a while to solve. I have a form, with two sub-forms side by side, both of which contain similar subforms displayed in continuous form mode. Each subform is a query based around activities on a daily basis, with each record representing a particular date. The reason the subforms are side by side, is to allow the user to compare dates with different filters (the filters are controlled by combo boxes at the head of each subform column.

What I had been trying to do was when a user clicks in one subform, the current record pointer in the other subform moves in tandem. I had some visual basic, that attempted to move to the first record of the subforms recordset and then search forward looking for the particular date in question. I was finding the record, but couldn’t find a way to move the current record two it.

I eventually found the solution, which was to do the searching on the recordSetClone of the form, and then set the forms bookmark to that of the recordSet. The snippet of code below shows how. “subFromDateField” is the field name of the field in the Subforms Data source (a query) which contains the date we want to jump to, and “ctlCalendar” is a calendar control set up by the users click on the other form to the date we are working with. “mySubForm” is the name of the control in the main form which holds the subform.

Dim rs AS DAO.recordset
Dim f AS Form
Dim q AS String

q = "subFormDateField <= #" & Format(ctlCalendar.value,"yyyy-mm-dd") & "#"
Set f = Me.mySubForm.Form
Set rs = f.RecordsetClone
If rs.RecordCount <> 0 Then
    rs.MoveFirst
    rs.FindFirst(q)
    f.Bookmark = rs.Bookmark
End If

Remarkably simple when you know how.

UPDATE: It turns out that you have to be rather careful about date format. I am working in UK date format, but queries like the above assume American! It seems the easiest to convert to ISO date format as there is less ambiguity that way

Managing SMF software in GIT

Yesterday, I upgraded three sites running SMF (Simple Machines Forum) software to v2.0RC5, upgrading them from v2.0RC4.  Some of these sites are more than just a simple implementation of the base software.  All of them have some modifications added, one (Melinda’s Backups forum) has a considerable degree of modification, and unlike WordPress, where plugins are pretty much stand alone, relying on well defined hooks in the software to get into the path, modifications in SMF tend to be intrusive.  Despite all the modifications, I upgraded all the sites over just a few hours.

I believe that is because of the approach I have taken to managing the configurations in Git.  In this post I want to explain how I do it.

Continue reading “Managing SMF software in GIT”

Displaying my RSS feed on Hartley Consultants’ front page

I had always planned to replace the feed on the front page of Hartley Consultants web site with a summary of recent posts from this blog.  I had previously sucked in news articles from the local SMF Forum that is on site, but I intend to remove the forum shortly because I am setting up the equivalent on this site, where it is more appropriate.

It turned out to be remarkably easy to achieve.  A call to “file_get_contents” with the url of my feed returned the raw xml into a variable, whilst a call to “preg_match_all” enabled me to parse the feed content and find the relevant information to display.  The formatting of the articles was already achieved by matching the same format as I had used for the forum feed.

Here is the code I used

	$feed = file_get_contents('http://www.chandlerfamily.org.uk/feed/');
	//having got the xml feed from chandler's zen we parse the xml looking for all the entries
	$pat = '#<item>.*?<title>(.*?)</title>.*?<link>(.*?)</link>.*?<pubDate>[^,]+,\s+(\d+)\s+([[:alpha:]]+).*?</pubDate>'; 
	$pat .= '.*?<description><!\[CDATA\[(.*?)\[\.\.\.\]\]\]></description>.*?</item>#sm';
	if (preg_match_all($pat,$feed, $topics,PREG_SET_ORDER)){
		$item = 0;
		foreach($topics as $newsitem) {
			$item++;
?>    <div class="newsitem">
        <div class="date"><h4><?php echo $newsitem[4]; ?></h4><h5><?php echo $newsitem[3]; ?></h5></div><h3><?php echo $newsitem[1]; ?></h3>
        <p><?php echo $newsitem[5];?></p>
        <p><a href="<?php echo $newsitem[2]?>" title="view full post at www.chandlerfamily.org.uk (in a new window)" target="_blank">More ...</a></p> 
    </div>
<?php
			if($item > 2) break;
		}
	} else {
		echo '<div class="newsitem">Sorry No Data</div>';
	}
	unset ($feed);
	unset ($topics);

The most important part is the pattern to match. But even that turned out to be quite straight forward, even in it did take a bit of trial and error to get it exactly right. I hope someone trying to do the same thing can use this as a starting point.

WordPress – Creating my own menu

Introduction

As part of the introduction of WordPress as the primary engine for my site, I have had to set up a template.  However, WordPress is not the only element of my site, and I expect to also have to include (at minimum) the following:-

  • An instance of GitWeb – to display my software repositories
  • Demonstrations of my applications
  • An issue tracking system (possibly combined with a discussion forum)

Each of these elements will want to take charge of the Content of the Web Pages they are controlling, but I expect all of them to exhibit the same look and feel for the basic web page.  As with WordPress, the way I achieve this is via a site template.

However, this template system is not quite the same the approach in WordPress.  It uses the following approach.

Continue reading “WordPress – Creating my own menu”

Webkit Developers know best?

In a web application that I am building I need to use a multiple selection <select> list.  Because screen space is limited I decided I would set the size attribute to 2.

In the Chrome browser, the display produces 4 lines of text – not what I wanted at all.

After a bit of searching, it seems this the webkit developers have decided not to fix this "bug" because of usability issues.

In a web application that I am building I need to use a multiple selection <select> list.  Because screen space is limited I decided I would set the size attribute to 2.

In the Chrome browser, the display produces 4 lines of text – not what I wanted at all.

After a bit of searching, it seems this the webkit developers have decided not to fix this “bug” because of usability issues.

This seems to me the height of arrogance.  Who are they to decide that I don’t want only two entries in this case – this list itself is likely to be relatively small

The making of MBChat v3 – part 2 coordinating multiple activities

In the previous blog article I discuss a process which would take several seconds to run. Obviously it is started as soon in the load process for the initial web page as possible.

Several other activities have to happen, such as loading the sound libraries, the dom has to be ready, the user has to have entered his username and password and it has to have been validated before the user can enter chat.

In the previous blog article I discuss a process which would take several seconds to run. Obviously it is started as soon in the load process for the initial web page as possible.

Several other activities have to happen, such as loading the sound libraries, the DOM has to be ready, the user has to have entered his username and password and it has to have been validated before the user can enter chat.

So several of the start up steps in getting the user properly logged on to chat and with the sound system fully working have to be co-ordinated. In order to avoid having to poll some flag to see if an item had completed, I decided to write a mootools class to handle this situation.

In order to create a coordination point for several activities, the programmer declares a new instance of the coordinator class, thus:-

var coordinator = new Coordinator(['rsa','login','dom','verify'],function(activity){
    loginRequestOptions.e = activity.get('rsa').e.toString();
    loginRequestOptions.n = activity.get('rsa').n.toString(10);
    loginRequestOptions.msg = 'MBChat version:'+MBChatVersion+'   using:'+Browser.Engine.name+Browser.Engine.version;
    loginRequestOptions.msg += ' on:'+Browser.Platform.name;
MBchat.init(loginRequestOptions,activity.get('rsa'));
    window.addEvent('beforeunload', function() {
         MBchat.logout(); //Will send you back from whence you came (if you are not already on the way)
    });
    soundcoord.done('chat',{});
});

In this instance, I am creating a coordinator which will coordinate the activities with the names of the first array parameter. When all the activities are complete the second parameter is a function which gets called with the activity object. This consists of a set of objects keyed by the activity name which are parameters which can be used. In this instance we are getting the public key components of the ‘rsa’ activity to pass to loginRequestOptions.

Note also at the end of the function a call to soundcoord.done. This is another coordinator I set up to coordinate the initialisation of the sound system. We can’t do anything with the sound system until both it is ready, and the user has logged into the chat (through this current activity). The done method is called with the name of the activity and an object (in this case empty) to pass into the final function in the activity parameter.

Just for completeness, this is the call to coordinate.done for the rsa activity so you can see how parameters (in this case the RSA key pair) are passed

var rsa = new RSA();
function genResult (key,rsa) {
    coordinator.done('rsa',key);
};

/*
We are kicking off a process to generate a rsa public/private key pair.  Typically this
takes about 1.2 seconds or so to run to completion with this key length, so should be done
before the user has completed his input - which is when we will need the result.  The genResult
function will be called when complete.
*/

rsa.generateAsync(64,65537,genResult);

This class itself is very very simple. You can download the latest release from my git repository as shown here. Although you might like to just copy and paste it from here

/*
 	Copyright (c) 2011 Alan Chandler
    This file is part of Coordinator.

    Coordinator is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    Coordinator is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with Coordinator(file COPYING.txt).  If not, 
    see <http://www.gnu.org/licenses/>.

*/

var Coordinator = new Class({
    initialize: function(activities,callback) { //an array of activity names
        this.activities = new Hash();
        activities.each(function(activity) {
            this.activities.set(activity,false);
        }.bind(this));
        this.callback = callback;
    },
    done: function(activity,parameters) {
        this.activities.set(activity,parameters);
        if (this.activities.every(function(activity) {
            return activity;
            })) this.callback(this.activities);
    }
});

The Squeeze between a Rock and a Hard Place (installing a Debian Squeeze system)

I have just re-installed my Debian MythTV server now that I have bought it bigger disks. In fact, with two new 1TB (ie approx 1000 Gigs) disks, I have put them together as a RAID1 pair and use this box to act as my new Home Internet Gateway.

I wanted to use Debian Squeeze because it will soon (sic) become Debian Stable and it is the first distribution that supports the Ext-4 filesystem that I feel is necessary for this important set of files.

However, I do not have a CD-ROM drive on this machine, so this is the story of installing Debian Squeeze using just just usb memory. With the easy and availability of USB to SD card converters, it does not matter whether you use a memory stick or an SD card – the process can be the same.

I have also decided to use extlinux as the boot loader. I have simple boot requirements, and feel nervous that using Grub (the Debian standard) means the use of a program to create the configuration file. What happens when I need to do something manual.

I have just re-installed my Debian MythTV server now that I have bought it bigger disks. In fact, with two new 1TB (ie approx 1000 Gigs) disks, I have put them together as a RAID1 pair and use this box to act as my new Home Internet Gateway.

I wanted to use Debian Squeeze because it will soon (sic) become Debian Stable and it is the first distribution that supports the Ext-4 filesystem that I feel is necessary for this important set of files.

However, I do not have a CD-ROM drive on this machine, so this is the story of installing Debian Squeeze using just just usb memory. With the easy and availability of USB to SD card converters, it does not matter whether you use a memory stick or an SD card – the process can be the same.

I have also decided to use extlinux as the boot loader. I have simple boot requirements, and feel nervous that using Grub (the Debian standard) means the use of a program to create the configuration file. What happens when I need to do something manual.

This blog entry does two things.

  1. It describes how to turn a bootable usb memory stick into an installation disk
  2. How to setup extlinux as the bootloader

Creating a USB installation memory stick

The first job is to prepare the USB memory stick (or SD card). The majority will already be formatted so that they have a boot sector that is bootable, with a single partition, formatted as FAT. If they are not, its straight forward under Linux to format them yourselves. I tend to use cfdisk as my disk partitioning program because I find it easier to use, but it doesn’t matter which. Just mark the first partition bootable, and then format it using the command

mkfs -t vfat /dev/sdX1

where /dev/sdX is the device name of your usb stick when plugged in. You can also use

mkdosfs /dev/sdX1

directly if your prefer (I never remember the second, so always use the first). mkdosfs is found in the dosfstools Debian package.

The second step is to locate and download the latest installer images. The Debian installer consist of a kernel image and and initrd image. The kernel is obvious, what is less clear is that the initrd image is the software the controls the install process. Start by navigating to http://www.debian.org/devel/debian-installer/ and looking for the different architecture images listed under the heading “other images (netboot, USB stick, etc)” (at the time of writing the squeeze alpha images are available as are the daily build images – I have used the daily builds without problems)

Regardless of which one of these two you choose you will then need to go down one further level into directory “hd-media”. In there you will find a vmlinuz image (the linux kernel) and an initrd.gz file listed. Download both of them.

You will also need an .iso image as when the installer starts it looks at all the possible partitions for a .iso image in the root. Obviously having it in the same root as the installer is the ideal here. If you have sufficient space to store a full CD image (approx 700Mb) this would be the ideal image to download, if not you will have to choose one of the smaller images (netinst or business card). I found I needed the full CD image to support my ethernet hardware so you may be forced to go for the large image anyway.

You can get the CD images from the same installer page – but then select the weekly snapshot CD image for your architecture as the one to download. Download CD1 – you don’t need any of the others.

Copy all three files (vmlinuz, initrd.gz and the .iso to the usb drive once it has been mounted.

You are also going to put syslinux on the usb, however for now just create a file syslinux.cfg in the root of the partition with the following contents

default vmlinuz
append initrd=initrd.gz

which will tell syslinux to boot the vmlinuz image with initrd=initrd.gz appended to the command line.

Finally unmount the usb stick and then type

syslinux /dev/sdX1

to put syslinux image on the partition’s boot sector.

You should now have an installation usb 🙂

Installing extlinux as the boot loader

The making of MBChat v3 – part 1 introducing Nested Closure Programming

MB chat is an Ajax chat application that I built for Melinda’s Backups. It was originally so that the leadership team could hold meetings without resorting to e-mail, but it became more of a general social thing for the members of the web site. In order for the software to work out if someone had said anything new, it polled a database every two seconds. Not something that would scale particularly but OK for v1. That was until Melinda herself came to chat one time, and a large number of people tried to come in at the same time.

MB chat is an Ajax chat application that I built for Melinda’s Backups. It was originally so that the leadership team could hold meetings without resorting to e-mail, but it became more of a general social thing for the members of the web site. In order for the software to work out if someone had said anything new, it polled a database every two seconds. Not something that would scale particularly but OK for v1. That was until Melinda herself came to chat one time, and a large number of people tried to come in at the same time. We found out that the scale limit was about 20 people. Beyond that and chat fell over.

In v2 I explored techniques for avoiding the need to poll a database and developed a solution based around the same technique as I mention in providing a high speed link between web clients using named pipes. Unfortunately the slight variation on this technique where essentially a message generated by one user has to be passed to all other users rather than just the one other, meant that you couldn’t wait for the two sides to synchronise (as is important in that solution). This means in some circumstances a message could get missed – so even with this technique a level of database interaction was necessary. I therefore also explored switching to the SQLite database as it seemed to offer a potential speed advantage. However I soon discovered that whilst it was almost faster the lock out mechanisms when multiple users attempted to access the database at the same time were crude and actually slowed things down. I got to 50 people, but that was the best I could achieve.

One thing I did discover in this test was that although SQLite was fairly fast with a single transaction per user interaction, it was lighteningly fast if multiple accesses could be done within the same transaction. I started thinking about a solution that could make that happen.

During this time, my daughter told me how they were considering a secure version of a chat program at her work. This got me thinking about how I could make MBChat secure. I set myself the following challenge:-

  • The chat server and the clients had to communicate with each other over the open internet
  • Only authorised users should be able to access the functionality or see any of the conversations
  • Clients can be sure they are talking to the server they think they are (to prevent an imposter capturing the messages)

A moments thought will immediately dictate that we should be encrypting at least the conversational messages with a key, but that since the software is in essence being downloaded from the server, it cannot contain that key within it as anyone watching the traffic would immediately be able to sniff out the key and use it to decrypt the conversation that was supposed to be secure .

In fact I decided that the only way to do this properly is for the client to generate a public/private key pair dynamically once it has started and transmit the public key of that pair to the server. The server will have to encrypt the key used to encrypt messages with that public key and pass it back to the client.  The client (and only that client) who will be able to decrypt it with his private key and start using it in the subsequent client server communication.

Generating a public/private key pair turns out to be a non trivial task.  If we are performing the task at the client end, inside the browser, then Javascript will most likely have to be the programming language which will be used to run the software which generates the public/private key pair.  Even with a reasonably fast processor, it takes several seconds to calculate an appropriate 64bit key (which we use).  If this was a normal script the browser would lock up solid, and would soon report a script failure.

I found Atsushi Oka had already solved this problem and provided a set of javascript routines that could generate the RSA public/private key pair. His solution he called “Nested Closure Oriented Progarmming”. If you take a look at the Nested Closure Oriented Programing module he has produced, you will see that part of his approach is to modify the base Object prototype to add the additional routines in. It was only after I built my first attempt to use his software to produce the key did I discover that there was major conflict with the mootools framework library that was an integral base for the rest of MBchat.

However after a detailed analysis of how the RSA key generation routines where built, I believed it was possible to make a mootools class that did almost the same job, but avoiding the need to alter the object prototype. I developed my own Nested Closure class which turned out to be considerably simpler that the library it replaced.

The key approach of nested closure programming is to have STEPS which are encapsulated within a function. A function can have part of its process broken into sub-steps, the key being that at the lowest level of steps the code can complete within the time allowed by the browser. Then all that is needed is an engine that runs one step, waits for a tick (a millisecond) and then executes the next step. Finally we need a mechanism to provide loops, for steps to indicate when they complete whether to exit this loop or to repeat the step or to continue to the next step (or loop if it was the last step of the loop), and a mechanism for passing results between steps.

Lets look at some small fragments of the RSA generation which show how it is used:-

Firstly – declaring the generator class, dealing with the result and starting the processes

RSA.prototype.generateAsync = function(keylen,exp,result) {
 var self=this;
 var generator = new NCOP( stepping_generate,true);
 var _result = function(keyPair) {
  result( self );
 };
 generator.EXEC([keylen,exp],_result);
...

We declare an instance of class NCOP (generator in this case) with the name of the highest level function used in the process, together with a flag which indicates that this instance of the NS class is unique. This allows the class to bind its key functions to the window object – making them easier to call (so you call can DO() rather than this.DO() or generator.DO()).

We start the process going and pass the parameters (as an array – which is an easy text transformation from the original code). We also pass the function that is called when the process terminates (_result).

Looking at the head of the stepping_generate function we can see how the parameters are picked up, and with a simple text transformation returned to two variables that were originally the functional parameters of that function

// Generate a new random private key B bits long, using public expt E
function stepping_generate (myArgs) {
 var B,E;
 B = myArgs[0];
 E = myArgs[1];
...

Once this high level routine has done some simple initialisation of variables, it starts the stepping activity (the main process) by returning but calling the DO method (it could have alternatively called the STEP method) of the main NCOP class. We see here how the first few steps are declared by declaring an array (indicated by the “[” at the start of the function parameters) of functions. Notice also a nested arrays starting at step 1.3. Arrays represent loops with a list of steps that are repeated until an action is taken to exit the array. Finally notice in step 1.3.1 that in certain instances the code executes return AGAIN(). This causes that whole loop to be started from the beginning rather than the (default) action of proceeding to the next step.

return DO([
 // Step1.ver2
 function () {
  RSA.log("RSAEngine:1.1");
  self.p = new BigInteger();
  rng = new SecureRandom();
 },
 function () {
  RSA.log("RSAEngine:1.2");
  // return self.p.stepping_fromNumber1( B-qs, 1, rng ).BREAK();
  return self.p.stepping_fromNumber1( qs[0], 1, rng );
 },
 [
  // Step1.3 ver3
  function () {
   RSA.log("RSAEngine:1.3.1");
   if ( self.p.subtract(BigInteger.ONE).gcd(ee).compareTo(BigInteger.ONE) != 0 ) return AGAIN();
  },
  function () {
   RSA.log("RSAEngine:1.3.2 : calling stepping_isProbablePrime");
   return self.p.stepping_isProbablePrime(10);
  },
...

It is also possible to pass the result from one function into the next step. This is shown in step 2.3.2 where a function “stepping_isProbablePrime” is called (it itself is using the NS methods to split its action into steps). The result of this step is then passed into the next step as the (only) parameter to the function which uses it to determine the flow of the following step (either exit the loop by calling DONE() or repeating the loop by calling AGAIN())

function() {
 RSA.log("RSAEngine:2.3.2");
 return self.q.stepping_isProbablePrime(10);
},
function(result) {
 RSA.log( "RSAEngine:2.3.3:result="+result );
 if ( result ) {
  RSA.log("RSAEngine:2.3.3=>EXIT");
  return DONE();
 } else {
  RSA.log("RSAEngine:2.3.3=>AGAIN");
  return AGAIN();
 }
},
...

This works very well. Dependant on the speed of the computer, it can generate a 64bit RSA public/private key pair in 1 to 2 seconds. This happens in the background – in some configurations of chat whilst the user is entering his username and password. In this instance that time delay is not even noticed. More importantly the browser never pops up a message to say that the script is taking too long to run.

Obviously when several things are happening at once (generating the key and awaiting a user to login) there is need to coordinate the completion. A simple mootools class to do that will be the subject of my next blog

Providing a high speed connection between two web clients

I have been considering the architecture for a web based real time game between two players.  It doesn’t matter what the game is, any game in which there are two players.  I’m thinking of an air hockey simulation, but it could be anything (tennis maybe).  The basic idea is that each web browser will run a javascript/ajax application to control the player on their side, and will want to pass messages very rapidly to the other player.

I have been considering the architecture for a web based real time game between two players.  It doesn’t matter what the game is, any game in which there are two players.  I’m thinking of an air hockey simulation, but it could be anything (tennis maybe).  The basic idea is that each web browser will run a javascript/ajax application to control the player on their side, and will want to pass messages very rapidly to the other player.

My last application, a chat program, passed messages via a database, with the client polling the server every two seconds to see if anything new had arrived.  For a game this would not be fast enough. So I needed an alternative.  The obvious choice for passing data between two programs in unix is a pipe, and that is what I decided I would try and use.  But there are limitations.  I can pretty well only use php as a module under Apache web server for this activity.  That means that each pipe has to be opened, read or written to, and then closed in the space of one request.  Granted I can use ajax for that request to happen in parallel (ie not block the game), but how do you sync with the other client potentially on the other side of the world, who could go and walk away from the game at any time.

Continue reading “Providing a high speed connection between two web clients”

Next Steps with UserMgr

As the last article hinted, I had hoped to move forward with the web site. The next stage is an update to my UserMgr application.

As the last article hinted, I had hoped to move forward with the web site. The next stage is an update to my UserMgr application.

As you can see if you attempt to access certain areas of the web site you are required to provide a user name and password. Currently those usernames and passwords are stored in a database, and behind the (username and password protected) Site Administration tab there is an application that allows me to edit this database. Also, the Family Area is also protected and there is part of the application on this page that allows members of the family to change their own user information. The code from all this can be found in the Software Projects area.

Continue reading “Next Steps with UserMgr”