Creating an SPA with Polymer

I maintain a medium sized Microsoft Access application that is the operational system for a small business.  This business has offices in London, Birmingham Leeds and a copy of the Access application runs on PCs within the office system of these offices, allowing the staff to run the business.  A SQL Server database runs in the London office and all the Microsoft Access clients connect to it.

For reasons of confidentiality I wont give too many more details about the business, but this article and those that follow will concentrate on the technical aspects of creating a web based replacement that can sit alongside (but maybe eventually replace) the existing Access client.  The reasons for taking this path are both business driven and technical.  The key drivers are:-

  • Access is Windows only, but there is a desire to connect from other devices (such as iPads and Macintosh laptops).
  • Difficulties in distributing each new release of the Access client to all offices.  A web application doesn’t have this problem
  • Difficulties in doing multiple changes to the Access client simultaneously through lack of version control. The web application is essentially textual source code and can be version controlled (I will use git).
  • Ability to explore different UI approaches to existing processes to improve usability.

I am undertaking this project in the background, and expect it to take quite a while to complete.  For this reason, a key underlying driver is that the new and old applications sit side by side.

I have decided to build the web application as a Single Page Application (SPA) .  I did start this project before, about two years ago, and at that time started with JQuery Mobile.  A characteristic of this was I started to see quite large and complex files that would have become increasingly difficult to maintain.  The essence of using Polymer is that it possible to build relatively small self contained custom web elements, that comprise both the html user interface, but also the control logic surrounding it.  This encapsulation is what I am looking for.

The SPA will communicate with the database with small self contained PHP scripts called via AJAX requests.  They will use PDO to access the SQL Server database.  I could have chosen from a wide range of approaches, but this one is one I know and feel confident with.

My efforts at writing a series of posts to cover a project have, in the past, led to nothing.  I write the first post like this, then get dragged into the project and by the time I have emerged from the other side I have no desire to talk about it further.  This may go the same way, or maybe not.  Polymer, and web components in general, is a new technology that has yet to be fully explored.  I have some opinions about application structure and approaches that I hope to discuss in further articles.  Whether they see the light of day is another matter.  Lets just wait and see.

 

 

 

 

 

Setting up my Raspberry Pi to be the home server

When I first had a broadband connection, the cable company provided a modem, but it was up to me to provide the router. Not long after, I was having regular failures from the small proprietary routers that you could buy which a detailed examination showed could not keep up with the massive amount of low level ethernet protocol messages coming from the cable side (looking like a large ethernet community) that seemed to be caused by a virus someone on that network had. I put a linux box in to see if I could get more information, but it was rock solid and was up for almost a year before a power failure caused a reboot. However it remaining running was crucial to my whole houses internet connectivity. Recent disk failures in this PC has highlighted its vunerability, and I decided to see if I could do something about it.
Continue reading “Setting up my Raspberry Pi to be the home server”

Adding custom buttons with your theme in an SMF forum

I recently wanted to update a theme I was developing for an SMF forum so it could add the “unread messages” and “replies to my posts” links as a menu items on the main menu.

Since the menu is placed on every page all this takes place in the main index.template.php for your theme.

With the integration hooks introduced in SMF v2.0 it becomes a very easy process. First during the theme initialisation (function template_init) when need to add a temporary (ie it only lasts for the display of the page) integration hook for the menu buttons

	add_integration_function('integrate_menu_buttons', 'mytheme_button_add',FALSE);

Then we just add a function at the end of the template page

function mytheme_button_add(&$buttons) {
	global $context,$txt,$scripturl;

	//only do this if user is logged in
	if ($context['user']['is_logged'])
	{
		//this is our new entry
		$newbuttons = array('unread' => array(
					'title' => $txt['view_unread_category'],
					'href' => $scripturl . '?action=unread',
					'show' => true,
					'sub_buttons' => array()
					),
				'replies' => array(
					'title' => $txt['replies'],
					'href' => $scripturl, '?action=unreadreplies',
					'show' => true,
					'sub_buttons' => array()
					)
				);
		//now insert just after home.
		$buttons = array_slice($buttons,0,1,true) + $newbuttons + array_slice($buttons,1,count($buttons)-1,true);
	}
	return;
}

Note how we slice the button array into two halves (in this case 1 before the break and count -1 after, but you could put them at any position), and insert the new buttons in between.

Using FreeTDS to connect to SQL Server from Debian Apache/PHP

This post describes how to set up a web site which uses PHP to connect to a backend SQL Server database using FreeTDS. I wrote it because I found the documentation currently available a bit confusing until I had completed the task, when it turned out to be really simple.

Continue reading “Using FreeTDS to connect to SQL Server from Debian Apache/PHP”

The disappointing state of Linux Video Editing software

I thought it would be a simple job. Record a video of me using my Air Hockey program to demonstrate its features. I have spent some time writing a script and recording and editing it in AudaCity. I have generated a number of ogg/vorbis/theora (not sure which is the right term) video files using recordMyDesktop showing the software running, and so all I needed to do was pick video sequencing editor to put them all together and I would be done.

Easy, or so I thought. Wrong!!!

Continue reading “The disappointing state of Linux Video Editing software”

A Conditional Formatting problem with Microsoft Access

I have been puzzling for some time why a Microsoft Access continuous form in an application I am maintaining insisted on replacing the background colour of a control by making it transparent when the record was not selected, and although the background colour returned when actually selecting the record the conditional formatting that was supposed to have been applied failed to do so  . Only today have I found the reason, and so I thought I would share it with you here.

Continue reading “A Conditional Formatting problem with Microsoft Access”

Supporting the fight against SOPA

This site, along with my sister site Hartley Consultants are supporting the fight against SOPA.  Although this is an American legislation, which I have little influence over, the bill’s implementation will have far reaching effects way beyond the intended one of stopping piracy. In fact it will probably do nothing much to stop piracy, whilst providing the US government (and my own government if this legislation spreads) the ability to restrict free speech in unacceptable ways.

Therefore on 18th January, this site will blackout for the day in support of the fight against this legislation.

Removing Security From An Access Application

I have been working with an old Access 2000 application and decided, due to the change in use to a Kiosk type application, where multiple people use the same application without shutting down, that the security mechanism based on Workgroup security will have to go. This application had previously been secured by using a bespoke workgroup file, where the original Admin user had been deleted and a new Administrator user had been added to act as the overall administrator.

I tried the mechanisms that I found by searching for it, namely to give privileges to everything to the user group but it failed at the point where you are supposed to import the application into a newly created database – telling me, not unreasonably, that it was secured by a workgroup file that I hadn’t provided.

The breakthrough came when I was reading about the security provided by Access 2007, and in particular the new accdb file format. User level security no longer applies to these file formats and Access removes any security it has applied when you convert to this database format.

So the process turns out to be simple. Just follow the following steps (using Access 2010 – I assume earlier versions will have equivalent options):-

  1. Start Access up as normal for a secured file (ie specify the workgroup file on the command line)
  2. Select the file tab and chose the “Save and Publish” option. Select the file format for the accdb format and click on the large “Save As” button
  3. When the conversion completes (mine took about 5 minutes – it is not an immediate process) and the file is saved in the new format close down Access
  4. Restart access without a work group file and create a new blank database in the the earlier Access file format (I am using Access 2000 format)
  5. Use the “External Data” tab and select an “Access” filetype to import from
  6. Import all objects from the file that you saved in the accdb format

The only issues I have had with that process so far are

  • Some of the reports were set up for a printed not on my current development machine. I will have to re do the printer setup for those
  • I was missing the references to ADO and DAO objects, so I had to manually re-add them (from Visual Basic, Tools, References menu).

How to have a SQL Server stored procedure be the source for an Access report

In converting my Access back end database I came across a little gotcha that took a while to solve. I was trying to dynamically have a stored procedure on the server act as the record source for a Report. But what ever I tried, I kept getting an Error Message “Run-time error ‘32585’ This feature is only available in an ADP”.

Google searches did not exactly throw up an answer, but it gave me several avenues to explore. Eventually and with a bit of tweaking I eventually found a solution that works. Since the report I was converting had previously used a Query (“qryPromotionLetter”) as its datasource, I knew that I could rely on that name to provide a vehicle to hold a new dynamically created query. The problem was that the connection string I needed for my ADO.Connection didn’t seem to work with the DAO.queryDef that I was creating. But as you can see from the code below, I eventually found the solution.

    Dim conn As New ADODB.Connection
    Dim SQL As String, stConnect As String
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    
    conn.Open getStrConn 'getStrConn  is function that returns the string to make an ADO connection
    Set db = CurrentDb()
 
    On Error Resume Next
    Set qd = db.QueryDefs("qryPromotionLetter")
    If Not IsNull(qd) Then
    ' So it exists, we must recreate it because the date might be different
        db.QueryDefs.Delete qd.name
    End If
        
    stConnect = "ODBC;driver=SQL Server;" & conn.Properties("Extended Properties") & ";"
    Set qd = db.CreateQueryDef("qryPromotionLetter")
    qd.Connect = stConnect
    qd.SQL = "EXEC dbo.qryPromotionLetter '" & letterDate & "';"  'letter date  can come from anywhere
    qd.ReturnsRecords = True

    qd.Close
    Set qd = Nothing
    Set conn = Nothing
    Set db = Nothing

An automated backup regime for a small SQL Server database

I am making this post to show how to make a simple automated backup regime for a relatively small SQL Server (I am using SQL Server 2008 R2 Express Edition) database. I am doing so, because when I was looking for some inspiration for doing this myself I found it quite hard to get all the information together in one place. I hope this is of use to someone.

The database itself is supporting a small business and will typically have transactions that alter it only during the day. The database itself is currently about 150MB big and a full backup takes less than a minute. It replaces an Access database which is being backed up daily. As a result of that, I decided not to bother with differential backups, and instead take a full backup daily. But I believe we can do better (than with Access) with respect to data security, so I am using the full backup mode on the database and taking transaction log backups. For convenience, I take one just before business starts for the day at 8:00am, and repeat the process every 4 hours (at Midday, 4:00pm and at 8:00pm) until business is shut. Because this is the Express Edition of SQL Server I am scheduling the backups using the standard Windows task scheduler.
Continue reading “An automated backup regime for a small SQL Server database”