I’ve been working on photogrammetry recently and needed to get some photos of things that are hard to see from ground level, so bought a really cheap zoom lens for my camera.
Turns out it wasn’t great for photogrammetry, but it got some really good photos of the moon!
But, that scope is designed to attach to a phone so it is horizontal, not vertical as would be needed if looking up at the sky.
It occured to me that if I design an eyepiece mount for the scope to attach a small computer such as the ESP32-Cam, then I could point it anywhere I want without risking the camera falling off.
So I did:
I then decided I’d build a full-on mount for the whole thing.
I needed to find the center of balance of the scope and build a strap there that could be used to hold the telescope when turning it up or down:
Notice in the above picture that I have bolts coming out of the sides and a rectangular block there. The bolts are so I can connect the scope to a scaffold, and the rectangular block lets me slot a geared arm on so when the arm is moved, the scope is moved as well. This controls the “altitude” of the telescope:
The next step was to build a very large geared base that the telescope would rotate on. Notice that I have gears on the inside and a groove going all around the top of the circle:
I then created a platform that would sit on that base, and “rollers” to act as wheels (with 10mm pieces of 4mm steel for axles). and attached stepper motors in place to control the azimuth (rotation of the base) and altitude (rotation of the swing assembly):
The final physical work was to create mounts for the electronics needed to control power, then install those electronics and cable it up. I got tired of designing and ended up gluing a few last things in place. My design, my rules. Cat is optional:
A few important things to note about the design are that 1. it’s battery-powered, so there’s no awkward power issues 2. the ESP32-Cam has Wifi on board, so no awkward cabling needed 3. I added a battery charger to the setup so there’s no need to replace the battery (the 18650 battery holders don’t like to let go of the batteries…) 4. the ESP32-Cam has 10 GPIO ports. 2 of them don’t work as far as I can tell, and 1 of the others is linked to the onboard camera flash (very bright!). I need 8 for the project. So, the camera flash now lights up every time the telescope rotates. annoying but I didn’t want to do any circuit surgery.
There were a number of ways I could approach programming. I chose to use a simple idea: 1. the ESP32-Cam uploads a photo to a website every 10 seconds, and downloads and executes move instructions. simple. this way we offload all programming to the website itself. 2. the website displays the most recently uploaded photo to the user, and shows the current azimuth and altitude of the telescope. 3. the user can request the telescope to move to a new azimuth/altitude.
Ireland being Ireland, I then needed to wait for the clouds to go away so I could get a photo of the moon.
update (2021-01-03): The first proper photo was taken last night with the camera in SVGA mode. I had to adjust the settings to reduce the exposure quite a lot:
Not terrible, but the camera also has a UXGA mode. Here’s a clipped version:
I’m not sure why that turned out green, but then the camera is probably not designed for looking at the sky at night with low exposure settings.
It would be possible to improve the telescope without replacing too much by changing the OV2640 camera for an OV5640 instead. that’s a 5MP camera (vs the 2MP of the OV2640).
Overall, I’m happy with the project. The end result can probably be cleaned up a bit, but I’ll leave that for another project when I’ll probably rebuild the whole thing and add some new features such as tracking, GPS and compass modules, and automated star-finding. And a better camera.
I’ve been asked to write down a kind of history of events leading to today with the face shields project I’ve been trying to stay on top of.
On March 24th, I called my local hospital (Monaghan Hospital) because I wanted to help out. I was given an email address to send to. I just found the email I sent:
email offering help
In it, I said I probably had enough filament to offer 25 shields, and had purchased more so might be able to provide 100 total. (haha – I laugh.)
I had been discussing this idea with some people in the “3D Printing Ireland” group in Facebook, where some other people were also contacting their local hospitals to offer help.
We half expected to be ignored. Who are we? Just hobbiests working in sheds and attics. Who are we to try offer our help to the real experts?
My email and details were passed around to a few people before I was finally in contact with Lorraine Brady, who liased with Cavan and Monaghan hospitals for me. We arranged to meet, and I gave her three separate designs (I think) to bring to her infection control team to examine.
There are a number of popular frame designs that people could choose among. Each of them is a simple design where a transparent A4-sized plastic sheet is “hung” onto nubs in the frame which are positioned so that a common paper punch can place the holes correctly.
The first I became aware of is the “Prusa” model, named after the 3D Printer company called Prusa. I wasn’t over-fond of their design because it was very bulky, but it was tested in conjunction with the Czech health authorities, so I offered that as a design.
Secondly, there is a company called 3D Verkstan, a Swedish company specialising in 3D printing and design. They designed a frame that was about 1/3 the height of the Prusa model. I liked this one, as it was compact and relatively quick to print. Again, theirs was designed in conjuction with local health authorities.
The final design I tried out was one that was a bit like the second but more curved. I’m not sure why, but the original designer of this model pulled the design from websites shortly afterwards.
Lorraine came back to me on the 26th of March and said that the infection control team were happy with the Verkstan model. The only change they wanted was that the visor itself should be cut an inch shorter and the corners rounded on it so it doesn’t catch on clothes.
The Verkstan model is very quick to print. With barely any tuning on my printer at all, I could make one every 50 minutes. The Prusa model, though, took almost 3 hours to print one.
I started recording deliveries on the 29th of March, when I gave Lorraine 42 face shields. I would then give her an average of about 25 shields per day for the next few weeks.
In the beginning, I’m told that the nurses were using the shields once and then discarding them, as is normal procedure in hospitals. But these are not normal times. They were asked not to do this, after the infection control team investigated various ways to clean them so they could be re-used.
Because I (and most people) print using PLA, normal autoclaving is out of the question – the temperature causes the plastic to warp. Thankfully, warm water and soap is all that’s needed to destroy a virus, so the hospital was able to start re-using the frames.
This was important, because it allowed me to then start offering face shields to other people.
It occured to me that I could ask the local council for help, and with a record of delivered face shields, that would give me a stronger case, so I contacted a friend, Paul Bond, who I thought would be a good go-between to start from. Looking back through my records, it looks like I contacted Paul on the 26th of March (earlier than I thought). He immediately said to just go ahead with what I was doing. He knows me through other volunteer work (particularly Monaghan Coder Dojo), so I guess he knew I wasn’t joking around.
Monaghan Town Team is a local events-organising group tied to the council. Paul explained that there was money left over from the St Patrick’s day parade which had been cancelled because of the virus, and whatever I did would be covered from that.
On the 30th, I gave 10 face shields to Dungannon Nursing Home. I actually felt a bit guilty about this, because the hospitals were where all the action was, right? Wrong. Turns out the nursing homes were equally in danger, but they were worse off with regard to PPE (personal protective equipment), as the HSE were not supplying nursing homes.
As more requests came in from other groups, Paul took up the task of delivering the shields. I don’t drive, and he had permission to drive around as he was volunteering his services to deliver pharmaceutical supplies to people.
The requests started to grow, and I needed more printers and filament.
I paid for a printer (an Anet A8 Plus) out of the Monaghan Town Team funds along with a few spools of PLA. In normal times, I get through 1kg of PLA in about a month. I was not sure how that would change. It turns out now that I’m getting through about 3kg every single day. 90 times as much. But I was conservative with my orders in the beginning because I was fully aware that I was using other peoples’ money, and I’ve never been comfortable with that.
I asked a few friends to lend me their printers, and they did! On the 28th, I was given two Anet A8s and an Anet A8 Plus (printer models). Unfortunately, they were each broken or not fully assembled, so work was required. Over the next two days, I got two of them up and running, but one of the Anet A8s was broken beyond repair (the motherboard was burned out).
a delivery of loaned printers
Una-Minh Kavanagh of WeAreIrish.com wrote an article about what we (the 3D printing community) were working on. Various other news papers also wrote articles. Northern Sound, Anglo Celt. I remember speaking to someone from AP, and one of the guys who loaned me his printer “volunteered” me to speak to Joe Duffy (radio broadcaster). I’m told that was well-received.
The local Monaghan Town Runners group offered a large donation through Paul (a member of the group). I did not ask for the money, but gave a list of materials that I needed for the project – four new printers and a load of PLA – and they went ahead and ordered it!
A friend had asked me why I didn’t start a fund-raising campaign. I’m not comfortable touching other peoples’ money, so I much prefer to spend my own money, and let other people donate material instead (like how I handled the Town Runners offer).
But, it also occurred to me that there were a lot of frustrated people out there that wanted to help but didn’t know how, so creating a fund-rasing campaign so they could donate and I could use the money raised to buy materials and tools for the project actually made sense.
I created a GoFundMe campaign on the 12th of April, asking for €500, as I thought that would be all that I need to cover whatever requests come up.
As of today, the figure raised on that is €4580 (of which €4418 is available after fees), and I have spent about €4000 of that, on about 100kg of filament, thousands of binder covers, one more printer (still waiting on that…), various replacement parts for broken bits on the other printers, and some tools to help speed up cutting of visors.
The orders were starting to grow, so I needed to find ways to produce the shields faster and with less effort. Here’s an example of about 6 days of orders (Apr 8 to Apr 13):
sample of order table
The Verkstan model takes about 50 minutes to print on a standard printer with the default 0.4mm nozzle. This meant that I could produce at most 16 frames per day, and needed to be next to the printer at all times so I could reload every hour.
Luckily my day-job is in computers, so it wasn’t a big deal to work from the shed and just take a few minutes every hour to reload the printers.
But those 8 hours of sleep bothered me – there must be a way to “stack” the frames such that I could get some sleep and wake up to a load of frames ready to separate.
“Stacking” printed objects is not straight-forward. If the space between objects is too small, they will bond together and break if you try to separate them. If the space is too large, then the printer will start printing “spaghetti” and you will just waste filament.
a stack of spaghetti
Even if you get the space just right, there are additional complex problems associated with curves, that are not straightforward to solve.
It took me about a week of testing and fine-tuning to solve the problems. In the end, I was able to create a stack of 20 face shields, separated by 0.6mm, with a tiny bit of carefully placed “scaffolding” in between the shields to help keep the shape. Combined with a larger nozzle size (0.6mm), I managed to reduce the print time to about 30 minutes per frame.
successfully stacked frames
The result was not as smooth as when the frames are printed out one at a time, with stray plastic “hairs” making the frames scratchy, but the fact that I could now print out a lot of frames every day made this a price I was willing to pay. And even as I felt guilty for giving out scratchy frames to nurses and carers, I also felt it was justified because I could give out more of them. Also, I wasn’t charging. What are they going to do – demand their money back?
I managed to increase the output from 16 per day per printer with hourly reloads, to more than 40 per day per printer with just 2 reloads. I have 6 printers running right this minute, creating at least 240 frames per day.
On April 24th, I wrote a post explaining the various tricks I used to speed up production. Partly so other people can use my experience, and partly so I can remind myself if this happens again.
The donated printers were all Anet printers, just like my own. Unfortunately, those printers are very cheap but not very good. I like them myself, but that’s because I like to tinker. So when I bought one more printer, I got yet another Anet A8 because it’s what I’m familiar with and comfortable fixing.
Resources were very low a few times over the next few weeks. I had underestimated both how quick I was burning through PLA and how slow the post had become because of the virus. At two points, I had to turn off some printers because I did not have any PLA for them to print with.
I started overcompensating for that, ordering larger amounts. I now have enough filament on stand-by that I can keep printing for about 3 weeks before running out. That’s better than needing it and not having it.
left to right, Anet A8 Plus, and 3 Creality Ender Pros
Over time, the orders started getting fewer but larger. I started delivering to some larger hubs such as the Armagh COVID-19 Response Committee, and the Bravo Charlie Tango – Bikers Coming Through groups.
In total, I have given away about 7000 face shields so far. I am not sure yet of all the accounts, but the cost per face shield has averaged at between 50c and €1.
I’m still printing at full pelt, and expect to continue for the next week at least. I hope there isn’t a second wave, but if there is, I am prepared to continue.
I fully expect that all material I have paid for (and will continue to buy until the fund run out) will be used. People are going to be asking for shields for weeks after the “peak” has been reached. So far, every single one that I have produced has been donated within a day or two, and I don’t expect that to stop for a few weeks.
Financially, I’ve received help from Monaghan Town Team, Monaghan Town Runners, a GoFundMe campaign, a donation of €500 from Bank Of Ireland (through NewsTalk), and a grant from Monaghan Council.
More photos documenting this project: mine, Bronwyn’s.
I’m sure I’ve missed a lot in this – if there are any questions, please ask!
Over the last few weeks I’ve given away more than 3000 face shields to hospitals, nursing homes, home-care companies, vets, and even a few fast-food companies.
In order to ramp up production, I’ve had to figure out ways of producing more shields, with less effort, while keeping a good standard of production.
The tricks here will help you speed up. The results will not be perfect, but they will be fast and scalable. This article assumes you are already producing face shields and juts want some ideas to speed up. I’m not going to walk through the basics.
Visors
The visor is the simplest piece to produce. Take an A4 clear PVC binder cover, cut an inch off the side, round the edges on that side, and punch holes in the other side in precise places based on what frame you are producing.
Speedup level 1
To cut many sheets to size at the same time, take a prepared visor sheet and place it on the near right corner of a work surface you don’t mind cutting into (if you’re left-handed, reverse these directions).
mark a line along the top edge of the visor in heavy marker. stretch the line out a few inches to the left, and all the way to the right edge of the table.
take a pile of maybe 20 sheets and square them to the table corner. place a metal ruler on top so it is in line with the marked line on the table. clamp the ruler down (you can get reasonable clamps for about €3 in any discount store).
you can now run a craft knife along the ruler edge to cut a few sheets at once. Angle the blade gently in towards the ruler, or the cut may veer away.
To quickly and accurately punch up to 3 sheets at a time: Buy two punches that are the same make and model. get a small wooden plank. screw one of the punches to the plank. carefully place the second punch on the plank so that if you put a sheet in them both and press down on both, you get the correct holes. Screw that punch down as well. Now you don’t need to measure. just push the sheet in, punch punch done.
to cut the curves, just use a scissors right after doing the punching.
Speedup level 2
Here’s where I am at the moment.
Buy a pillar drill and a band saw. They’re about €149 each in Lidl. Well worth it if you’re at the stage where you’re producing so many shields that visor cutting is eating up hours of the day.
Create two “templates” by getting two plywood or MDF sheets and drawing a prepared visor onto them, then very carefully drill 5mm holes into the correct place through both the sheets. Get this right. Then you can cut out the rest of the shape of the visors. This part is not important to get right.
Take a full block of sheets (including its wrapper) and place between the templates, then clamp this in place.
Put the 5mm drill bit in the pillar drill. With the drill off, carefully pull down the drill until it is in place in the template and touching the visor. This is to make sure you’re aligned. Lift up again and tun on the drill. Push the drill bit down through the vinyl in a few passes, each pass going a little further. This gives the drill time to evacuate the plastic from the previous pass before going further.
Then just repeat for the other holes.
Next, on the bandsaw, prepare the “fence” so it is 1 inch from the blade.
You can take the block of sheets from the template now. keeping the wrapper on the sheets means they’re all still held together so you should now be able to push the block through the bandsaw to cut an inch off. To do this, use the pushstick thing to push the block in. Don’t press the block in with your hands as you may influence the blade to veer off. Or you may slip and have to go to hospital, which is not a good idea right now.
Once that’s done, you can round the edges easily using the bandsaw.
Easy! I took my time with this earlier and was able to prepare 100 visors in only 3 minutes 40 seconds.
Frames
This one is tricky. We’re all using different frame types, so use whatever instructions make sense to you. I’m not going to walk through everything button by button (that’s why god invented search engines) but will explain the steps in general.
I’m using the “Europe” version of the Verkstan model, as that’s what I started with, and my “clients” are happy with them.
There are two versions of each Verkstan model – a “solid front” and the original “plate” version. We’re going to use the plate version.
Using the solid front makes sense if you’re using a dual-extruder, but most people are not.
First, create a “cookie cutter” outline of the frame. To do this, I open the STL in FreeCad and export as PDF. This creates a top-down image of the frame. I then open that in Gimp, delete the background and the grayed-out “plate” part of the front so I’m left with the frame outline.
Select the outline and “shrink” the outline by 1px and save that.
Now load that outline in Inkscape and convert it to an SVG.
Then convert the SVG into an STL using this tool. I think the size I used was about 180mm, and 0.6mm height (twice the height of your nozzle’s line-height). tick “reverse winding-order”, untick “base plate”, and then re-render before downloading the STL.
That STL has a lot of nodes in it, so I then use Meshlab to reduce it in size. I think I reduced mine down to about 2000 nodes from …who knows.
Next, open FreeCad and import the Verkstan model.
I turn the model over so the plate is at the top and there is a space under it. This way the printed plate will always have a nice finish on the “bottom”, and there won’t be a problem of the second frame in a stack’s plate drooping down and sticking to the first frame.
now import the cookie cutter outline and manouvre it into place on top of the frame. It will actually be better (for reasons you’ll find out) to just manouver the frame into place under the outline.
In my case, this just involves moving 60mm in Y.
Using the Parts menu, convert both STLs to solid. you can then delete the imported STLs.
Now go into the Draft menu, and create separate matrices from the frame and the outline (so you have 2 matrices).
Change X and Y multiple to 1 in both. Change Z multiples to 5. If you want, you can use a Spreadsheet object to make this dynamic. The Z distance should be 5.6mm (5mm for the height of the frame, 0.6mm for the height of the outline)
Okay – select both the frame and outline matrices, and export as STL. You can now print that.
After your first print you’ll notice some really bad “stringing” issues. I don’t have any left so can’t photograph them, but they look a little like this:
The reason this happens is that when the printer is laying down lines for the outer wall of the second frame, it’s pretty much drawing on empty space. So the line pulls away from the print and follows the path of the nozzle until the nozzle touches against the frame enough for the printed line to stick.
So now we get to the fine-tuning.
Print out a stack of 2 frames. Take note of where the stringing takes place.
Create a new FreeCad file and import the cookie cutter outline. Convert that to solid.
Now, wherever you noticed stringing take place, put a little line right across the outline from the inside to the outside, using a cube block that’s 0.6mm high, whatever length is required, and 0.2mm in width. I use 0.2mm because it’s a little less than half the width of my nozzle. we want this to be an easy-to-remove line, so don’t make it heavy.
Now you can save that all as an STL and import into the main model and repeat the creation of the Outline matrix (delete the old one).
Load up in Cura or whatever. If you look at the print path at those new lines, you’ll see that the printer skips from inner to outer, etc, which forces the line to reconnect with the main model, making it stronger.
The next print of the 2-stack should be better than the first.
Repeat until happy.
Then create a 20-stack and print.
Oh – almost forgot. There’s an art to separating the stack as well. Place the stack down on a cutting surface on its side so there are 20 end hooks touching the surface. Press a blade (I use a butchers knife) down between each end hook one at a time. Turn the stack over so the other 20 end hooks are touching the surface. Repeat. Now place the stack on its “back”, hooks in the air. You should be able to place the blade between the end hooks of two frames at a time, and just push down to separate them.
To simulate air molecules, I’ve added particles to the simulation. These are tiny light molecules that will bounce off the clavichord (once that’s simulated) so I can measure the vibration of the air molecules and extract a sound simulation from that.
simulation of 2000 particles
To make sure that the particles actually bounce and don’t just pass right through each other, it was necessary to add another force to the simulation, the electrostatic force. With this, I can give all particles a “charge” which will either attract or repel other particles.
To start with, I’ve set all particles to have a positive charge, which causes them all to try to stay away from each other.
To keep the particles within a defined area (so they dfon’t just fall down off-screen forever) I’ve also added a “boundary”, which simulates an invisible box which keeps everything inside it. The particles bounce off the walls, losing a little bit of velocity as they do so.
Doing this in JavaScript is tricky – with only 2,000 particles, the simulation slows down to a crawl. I may need to look into how WebASM works, or whether it would be quicker to use WebWorkers to run the physics in the background.
A hotbed I ordered before Christmas finally arrived, so I was able to start building a new 3D printer today.
The hotbed is 300mm squared, and the lead screws I ordered are 500mm long, so this printer is going to be quite large in comparison to the old one!
new printer case on left, old printer on right (at bottom)
In the image, you can see the difference in dimensions. The old printer case was built around an Anet A8 printer which I had been changing and hacking at over the years. Most of the old printer case is unusable space. You can see for example that I have a roll of filament inside the case, and all of the electronics of the printer are inside it as well.
The new printer, though, has only 5cm margin around the hot-bed, and I will be able to use almost all space within the case.
The electronics for the new printer will not be inside the case, but will be mounted to its back on the outside. This is because electronics dont’ like getting very hot, and the point of an enclosure in a 3D printer is to keep the heat in.
It will be a few weeks before I can complete this project – there are still some parts to come. I am missing a PSU, for example, stepper drivers, and rails for the CoreXY part of the project.
So as part of my clavichord build, I’m trying to simulate the vibrations that happen within the instrument when a string is struck.
To do that, I’m building up a physical simulation from scratch. Over the last two days, I’ve gone from a 1-dimensional mass-spring simulation to a 2D one.
Yesterday’s work was to extend the math of the 1D version to handle 2D. Today’s work was to make sure that multiple nodes could be handled in a realistic fashion.
The hardest part for me so far was to figure out how to apply forces in defined directions in a realistically proportionate way. The example I read first (here) did not bother doing this, so the physics was incorrect.
Luckily, I remembered some trigonometry and was able to use Pythagoras’s theorem and some SOHCAHTOA usage to figure it out.
Springs in real life don’t just bounce for ever, so I applied some damping to the springs based on some example math I found based on Hooke’s spring law:
Relative velocity (va-vb in the above) was found by measuring the distance between the end points, measuring the distance between the end points after velocity in all dimensions are added, then simply subtracting one from the other
The end result is pretty.
Interesting aside – Pythagoras’s theorem works in not just 2 dimensions!
Today, I had hoped to have a 2D mass-spring system worked out, but it turns out the tutorial I was working from was flawed and overly simplistic, so I had to work out some math from first principles.
A 1-dimensional spring is simple – there are 2 points, the distance is simple to figure out, and when you figure out the forces (spring force, damping, gravity), it’s very obvious how to apply them in the single dimension available to you.
So, a 1-D mass-spring system is easy. Example here.
In 2 dimensions, things get a lot more tricky. Firstly, the distance between two points is no longer a simple subtraction ( 1d: z2-z1, 2d: sqrt((x2-x1)^2+(z2-z1)^2) ), but even worse, the forces being applied are proportional to the angles between the points.
So, today, I spent the most of the last few hours just making yesterday’s work easier to manage, and creating some “helper functions” to apply forces on nodes.
Tomorrow I’ll try extend the system a bit more so you can have a few nodes all linked together – like a chain or net. At the moment, the “spring” physics in the system only applies the spring force to one side of the link, so I’ll need to adjust that so the spring is applied to both. It will be interesting to see how two nodes hanging side-by side on a line behave.
So today I’ve started on the clavichord project. But I haven’t touched a piece of wood yet.
The last time I built a clavichord, I got to the point where I was trying to build the sound-box and the bridge. I could not figure out the right shape for the bridge, and could not find any documentation online that explained why any particular shape was chosen.
The nearest I could come to it was descriptions of ribbing in guitars and how they affect tone.
But, because I like to know what I’m doing before I do it, I’ve decided to build a simulator that can simulate a clavichord’s sound-generation abilities without me needing to actually build it.
And if I build it right, I’ll be able to adjust parameters in the simulation (bridge positions, soundbox sizes, etc) dynamically to figure out the best possible parameters for what I want.
After thinking about it for a day or so, I think the right way to do this is with a mass-spring system, where the clavichord is represented as a massively dense system of nodes and springs, which allow me to apply virtual forces to things like strings and keys and see what happens.
What I hope is that the simulation will result in vibrations that I can translate into actual sound, so I can hear the simulated clavichord.
I’ve never done this before (has anyone?) so I’m starting from first principles.
The first principles in this case is a simple 1-dimensional mass-spring system, which I’ve built here. It simply simulates a weight hanging from an anchor, bobbing up and down on a spring until the bouncing is damped and the weight hangs still.
Tomorrow, I’ll work on building that into a 2D system. There is a very simply tutorial of 1D mass-spring systems here. Near the end, the tutor expands it to be 2D, but I don’t think it’s done correctly – doesn’t take into account diagonal stresses that trigonometry is required to solve.
I could not find a simple 2D tutorial, so will need to build that from first-principals tomorrow.
I was thinking recently that I don’t really do as much as other people. I’m always admiring what other people get up to, and wish I could be as productive as them.
But then I realised that the reason I feel like I don’t get much done is that I’m not comparing myself to one person. I’m comparing myself to many.
So, I’m going to try keep a semi-daily record of things that I actually get done (not counting my day job, of course!)
Starting with – I’m currently finishing off some pigeon-hole shelves for Bronwyn to use for her knitting. They’re way too large, but they’re also my first attempt. Maybe I’ll remake them in a year or so.
I’m also strongly considering building a new clavichord from plywood or OSB. The last one, I got to the stage that it could make a sound, but I didn’t complete it. This time, I have the tools and the work-space, so I should be able to get a bit further.
Last week, I took on a project for my friend Phil Sneyd, who said he and Dave Kelly were on the verge of completely scrapping their long-term project MP3Unsigned, a website for unsigned musicians to upload their music, allowing the music to be charted monthly, commented on, etc.
I told him I’d have a look, and asked for some access details. I won’t get into the numbers, but when I saw the hosting costs, I did a quick check and found that moving everything to Digital Ocean with Volumes to handle the storage would cost 6 times less. A while later, I realised if I use Spaces instead of Volumes, it would be 22 times cheaper.
It was impossible to simply copy the code over, though. The website was written in an old form of ASP, the source-code of which was long gone, and the database was Microsoft SQL Server, which doesn’t play nice with anything other than Microsoft products.
So, I needed to extract the current data, and rebuild the website. This is a project that has data right back to 2003, so this is not going to be a single article rebuild 😉
This first article is about converting the database from MSSQL to MySQL.
Exporting the Database
The hardest part in this, in my opinion, was in extracting the database data. I could not find an easy way to dump the data in a format that could be converted to MySQL (the DBMS I would use on the new one) and imported.
The “CSV” export actually exports into a semi-colon delimited format (not commas), and it doesn’t quote strings, so when data itself contains semi-colons, you can’t trust the export.
The “XLS” export is not XLS. It exports an XML file which I assume can, after some manipulation, be converted into an XLSX file, but it’s definitely not XLS. I couldn’t use it.
The “XML” export at least did what it said it would! I finally was able to export the data. But I had to do it one table at a time, and the old system had 94 tables… It would be handy if myLittleAdmin had a single “export all as XML” function, but I can only work with what I have, so 94 exports it is…
There were a lot of tables that I considered irrelevant – tables created for specific competitions in 2006, or tables created for sponsors and then never used. I ignored those.
Converting to MySQL
The XML format that was created looked a little like this:
In that, we have the table name as the root, a “row” object containing row data, and then each field’s data is contained in an item named after it. Makes sense.
To convert that to MySQL, I used PHP’s built-in SimpleXML support to convert the XML file to a navigable structure, then started iterating.
It wasn’t quite as straightforward as that, but that was the gist.
To begin with, I assumed that all fields are text strings, so I set longtext as the field type. Except the first item in each row, which I assumed was an auto_increment integer (the unique primary ID of each piece of data), unless the item was obviously text, as was the case in some tables… And unless the table was the Chart data, because …who knows. When creating a table, a rule of thumb is that you should always enter a primary key. Always.
I found that when I ran the script, there were some crashes where the original export used some characters that SimpleXML complained about. For example, “”, “ ” (yes, there’s a character there…), “”, “”. After checking the context of these characters, I simply removed them. I had tried various methods including UTF8 encoding, MB transliteration. In the end, deleting them was easiest.
Once the table structure was recreated in a vaguely correct MySQL form, I could then import the XML files directly using MySQL’s “load into” method.
Here’s the PHP code I used to automate this (the dbQuery function just runs a query)
$files=new DirectoryIterator('.'); foreach ($files as $f) { if ($f->isDot() || $f->getFilename()=='table_import.php') { continue; } $fname=$f->getFilename(); $tname=strtolower(preg_replace('/mla_export_(TBL)?(.?)../', '\2', $fname)); echo $tname."\n"; dbQuery('drop table if exists '.$tname); $xmlstr=file_get_contents($fname); $xmlstr=str_replace([' ', '', ' ', '', '', '', '', '', '', '', ''], ' ', $xmlstr); $xml=simplexml_load_string($xmlstr, 'SimpleXMLElement', LIBXML_COMPACT | LIBXML_PARSEHUGE); if (!$xml || !isset($xml->row) || !count($xml->row)) { continue; } $i=0; $fields=[]; foreach ($xml->row[0] as $k=>$v) { if ($i==0 && preg_match('/^[0-9]+$/', $v) && $fname!='mla_export_TBLOfficialChart.xml') { $fields[]=$k.' int auto_increment not null primary key'; } else { $fields[]=$k.' longtext'; } $i++; } $sql='create table '.$tname.' ('.join(', ', $fields).') default charset=utf8 engine=innodb'; echo $sql."\n"; dbQuery($sql); echo "importing data\n"; dbQuery('LOAD XML LOCAL INFILE "'.$f->getPathname().'" INTO TABLE '.$tname); }
The import failed on a few of the XML files without error – simply saying “process killed”. I fixed this by increasing the RAM of the machine temporarily. There’s no need for a running production server that’s only serving simply things to have a load of RAM in it, but for the initial setup, it needs enough RAM to completely hold all of the imported data in memory, with plenty to spare while it manipulates it into a shape that can be recorded in MySQL.
The actual import took hours for the server to do with this project. At least 3 hours of non-stop crunching.
Next Steps
After the import, I have a load of data in very simple and inefficient format – inconsistent table names, inconsistent field names, and just primary keys and long-text formats.
I’m going to leave the names alone until after we’ve completed the initial rebuild, so that when I “refresh” the data just before we go live, I don’t have to do a lot of awkward remapping.
The long-texts, I’ll adjust as I rewrite the parts that read/update them.
Tomorrow, the plan is to create a test server that simply outputs the existing data in read-only format. This will act as a low-level site that will be “progressively enhanced” to what we want it to be.
