Updating a digital log

Years ago when we first started chartering I created a Filemaker database to track our voyages and sea miles. A lot of charter companies want a “resume” and I had heard if I ever wanted to get some really advance certification that one needed minimum numbers of recorded miles as crew and captain. So I used the database to track miles etc. At the end of each trip I would diligently add in all the days, miles, notes and lat/long if I had happened to note them.

Fast forward a bunch of years and my version Filemaker now no longer runs reliably on my updated OS. And since I no longer have any need for it, buying a new license doesn’t seem like a reasonable expense. Luckily I had both a printout of the contents and was able to export the data from the files between crashes. But it was time to acquire a new database tool.

MySql/php

A bit of  sniffing around the internet and it became clear that building a MySql database was going to be the likely successor. But unlike Filemaker, it has no graphical interface—so that meant if I wanted it to be at all user friendly I would need to build a custom php website to add, view and edit the data. Since my knowledge of both MySql and php was cursory to say the least, it looked like an impossible task. But nothing ventured, nothing gained…

To cut a very long story short (or at least shorter) I managed to find a nice tutorial for building a CMS (content management system) and then began the painful task of hacking it into my new Cruising Log. Along the way I have learned tons and tons and have thoroughly enjoyed the exercise. As a work in progress it probably took me around a year to get it to the state it’s in now—although a lot of that was back-tracking to do things over so future changes would also work. I am a very dangerous coder.

Entering Data

The original design was based on the idea that I would get home after a cruise, grab info from the official ship’s log and then type it all in manually. But I use Farkwar.com to post updates on a daily basis while I am cruising which updates our position by means of an email formatted in particular way:

Enter text and/or notes here.
-----
At 04/24/2020 10:40 (pdt) our position was 49°50.1068′N,124°31.6473’W
Destination: Nanaimo

It occurred to me that my site could also parse the email and potentially update the database automatically. So I rejigged the email in a way that allows Farkwar to ignore the extra information and leaving me able to enter what I wanted to. Next I wrote a python script to parse the email, extract the relevant data and output in a format that MySql will understand. Then when I send it to my secret Farkwar email, I cc it to a personal secret email—Farkwar will post the position and update its map, and my python script will parse and post it to the database. So now the email looks like this:

Log Entry
We decided to go. It will be an arduous journey but I have faith we will survive it mostly unscathed.

Whales will be looking forward to our visit
-----
At 05/21/2020 10:40 (pdt) our position was 49°50.1068′N,124°31.6473’W
Destination: Nanaimo
From: Edmonton
To: Nanaimo
Distance: 0

…and this results in a posted entry that looks like this:

Back to the site

But before I could really get going I had to build the website, connect it to the MySql database in a more or less secure way and start formatting various pages. Another long , hijinx-filled story I won’t bore you with. So after a few farcical fits and starts, the site now consists of a front end with two pages (an overall view and a detail page for each cruise):

…and an admin backend that allows me to add/delete/edit  boat lists, crew lists, and the cruises themselves.

Of course it is never that simple, and a lot of extra things needed to be built along the way—like a login screen, a database table to hold users and some truly convoluted MYSQL statements to  be able to display and edit the information I wanted in the way I wanted it. (And, as I just discovered, a way to delete an entire voyage.) But in the end I got it done.

I also realized that since I had the latitude and longitude nicely formatted it wouldn’t be hard to build a link to google maps: https://www.google.com/maps/place/49%C2%B0%2030.8916%20N+123%C2%B0%2057.8196%20W so I could visually see the various locations each entry was made. So I did.

Printing

The last piece of the puzzle was the ability to print a nicely formatted pdf version of the log as a whole. I started off experimenting with some php to pdf utilities but in the end decided to design a nicely formatted webpage and then print direct to pdf.

Conclusion

After all that, I decided not to schedule the updating script and rather I will just run it when I get home —at least for the first few trips to ensure everything works in real world conditions. That is if we ever get to go cruising again 😉

The very last thing I did was port a copy from my personal server to the server where NeverforEver.ca is situated and try and set it up as a sample. Which, after a long battle, you can now find here: Cruising Log. If anyone is interested in the back end, let me know and I might send you a temp admin password.

It’s been a great learning experience and makes me want to do more development. Apparently Swift is pretty close to Python, and Swift is the official language of iOS development. Maybe I could make it into an iphone app next? If this COVID thing goes on long enough, well, who knows…

P.S. As a side note, according to my fancy new cruising log, not including inland water trips, I am officially at 6218 nm total cruising.

P.P.S. I will likely post at least the python code over at macblaze.ca, but probably not for a few days as it will take a bit of cleaning up.