It is unbelievable to me that Microsoft SQL Server does not support regular expressions. In the absences of regex, replacing multiple occurrences of the same string/char becomes super tedious. You can nest multiple Replace() statements which gets ugly and impossible to read and you have to know exactly how many multiple occurrences there are. But for a current project I have to replace all line breaks in a column.
Here’s how I did it:
WHILE EXISTS(SELECT * FROM #yourtable WHERE (Comments like '%'+char(10)+'%')) --note I knew that char(10) and char(13) always occurred next to each other and in an effort to speed up this loop got rid of the char(13) filter
SET Comments=REPLACE(Comments,char(10),' ')
WHERE (Comments like '%'+char(10)+' %')
SET Comments=REPLACE(Comments,char(13),' ')
WHERE (Comments like '%'+char(13)+' %')
-- again you can probably do this in one statment but I was hoping to speed it up by simplifying the where statements.
There are no doubt a dozen ways to optimize this but it seems to have worked and didn’t take forever. Good luck out there, folks, working with a “modern” RDMS that doesn’t support regex.
Some folks over on the Day One Community FB group were interested in this shortcut I wrote. It computes your 7-day average for active energy and steps, asks you a few questions and creates a Day One journal entry. You will need to modify this shortcut. It’s a little tricky as it pulls health kit data which is really fiddly. You can drop me a note if you get in too deep but I can’t promise I’ll be able to make it work for you. Download the shortcut here.
I find the seven-day average data more interesting and useful. It was this podcast with Jim Collins that got me thinking about using multi-day averages to track certain metrics (e.g. as long as I’m average about 40 minutes per-day singing and playing guitar, I’m making progress, if my seven day average slips below that I am just treading water). Collins uses a 3month, 6month and 365-day average as opposed to 7, but the gist is the same. Very interesting approach to self-quantification. Go to the podcast and fast-forward to the 45-minute mark, great information.
Inspired by listening to @ttscoff, @macsparky and @rosemaryorchard on yesterday’s Automators podcast, I made some slow progress but progress nonetheless! Trying to automate as much as possible my monthly entry in Day One of all of my purchases from that month from Amazon. Currently I’ve just got a repeating todo in Things that reminds me to launch this nifty Keyboard Maestro shortcut.
Still requires that I download the .csv file, gussy it up in Numbers and paste it into Day One. Those steps are still a big reach for my automation skills but gives me something to work on.
I like to keep notes about the gigs I play with my various bands. Sometimes I log very detailed entries about changes we need to make to our gear or sound settings for the next gig, other times it’s just a few quick words so I can remember who came out to see us or what riff I need to work on in a given song for the next gig.
Naturally I use Day One to record this information. Last year I started using an iOS Shortcut that I wrote that prompts me for the type of information I want to record about each gig. The shortcut presented me with a list of questions and then combined all of my responses to those questions into a nicely-formatted Day One journal entry.
The problem is that I am not a great Shortcuts writer. I’m lazy so I didn’t add any flow control statements to try to save my responses to the prompt questions as I went along. Meaning, after answering 3 or 4 questions and typing them on my iPhone (which is needless to say tedious) I would occasionally forget about my lame programming skills and try to pause the Shortcut while I go over to facebook or somewhere and download a photo from the gig to add to the entry. Nine times out of 10 I would hit “Done” in the Shortcuts app to do this and in the process I would lose all of the responses I had already typed. Frustrating.
This morning I did just that. Again. I hit Done in Shortcuts while answering the gig prompts in order to go get a photo from facebook and lost all of the details I’d already written last night’s gig. Let me be clear this isn’t Shortcuts fault or Day One’s.
Then I realized I’m totally overthinking this whole need to be prompted bit by Shortcuts and instead trashed my old shortcut and just wrote up this little gem which works just fine and doesn’t have the risk of me screwing it up and losing text. Moral of the story: don’t overthink it! Maybe instead of using Shortcuts to prompt you for a long list of questions, just create a template entry in Shortcuts instead.
In trying to harden my WordPress install that’s hosted on Linode I decided to enable https for encrypting the login page. At first I just went with the old self-signed certificate route and it was fine but boy did I have to jump through some hoops to get iOS/my iPhone to play nicely.
Then I noticed that Google’s Chrome browser was giving me a “not secure” message on my site (and on the other sites I host on Linode) and realized that Google no-likey the self-signed certificates and further research showed that Google may actually penalize your site in search results if you don’t have a CA-signed SSL cert.
I didn’t want to pay for an SSL cert just to encrypt my WordPress login and googling didn’t return much until I came across Linode’s great documentation for Securing HTTP Traffic with Certbot.
Here I learned about Let’s Encrypt and the way they handle requesting certificates. Super, super cool. And Free. And with the instructions from Linode it was so easy to do.
And now none of my hosted sites are getting that pesky Not-Secure error from Google Chrome!
TL;DR, if your paradigm PDR-10 isn’t powering on automatically, check the fuse. It can be easily accessed by removing the back panel of the subwoofer.
Sunday morning and I had the house to myself for a few hours so decided to listen to my favorite recording of Beethoven’s 5th Symphony. Firing up the 3rd and 4th movements really loud always serves to restore my faith in humanity.
Anyway, a few bars into the 3rd movement and I knew something was up with my hifi. My Vandersteen’s sounded a bit thin and I noticed that my subwoofer power light wasn’t on. The paradigm pdr-10 that I have is not a great subwoofer but the vandersteens towers that I have are such that they only need a tiny bit of reinforcement in the very bottom end so it works for me. It is supposed to power on automatically once it detects a signal from the receiver but for some reason it wasn’t powering on and I couldn’t get the light on the front to come on.
I hit pause on the symphony and brought the subwoofer over to my kitchen table where a few screws later I determined that a blown fuse was likely my problem. Unfortunately this was not an easy fuse to source. It is a 1 1/2 amp time delay fuse. My local hardware store, which has EVERYTHING didn’t have it but they did test the fuse I brought in and confirmed it was blown so I knew I was on the right track.
Ultimately I purchased the replacement part from Amazon and finally got around to hearing the 4th movement in all of its bottom-heavy beauty.
As I started looking at the Dasher code I realized that it also had a hook for ExecFile and that could be used to call a script/program etc instead of just calling a URL. Super!
Using an Amazon Dash Button to Play a Spotify Playlist
Most of the dash button hacks that people have documented rely on IFTTT. While IFTTT is no doubt super useful, I needed a bit more granularity in what I wanted my buttons to do and wanted to be able to call something more robust like a python or AppleScript file when the button was pressed. As a starter project, I wrote an AppleScript that checks the current time of day and then launches a playlist (and sets the volume) for that time of day. Unfortunately I could not get Dasher to work with the AppleScript.
Saving the script as a compiled application and calling it in the command section, this just errored out and didn’t give me any clues
Then I wrapped the AppleScript in a shell script calling it with osascript, that errored out with a (-600) “application isn’t running” error which told me that at least the script was getting launched but wouldn’t run right
Then I realized that the process calling the shell script was running as root and as such the AppleScript was being called from root.
At first I tried all sorts of setuid BS on the shell script but that didn’t work
Then in an aha! moment I changed the shell script from ‘osascript /Users/jimwillis/bin/scripts/spotify_launch.scpt” to:
[Note/Update: now that Apple has finally linked all the iMessage backend in iCloud you don’t need to jump through these hoops anymore. Just do this instead.]
Finally figured out how to make iMessage on my MacBook cooperate with a group text thread that includes non-iphone or Android users. This assumes that you have an iCloud account and you have an iPhone and want to send text messages to people who are not apple people. This solution allows you to send iMessages from your mac desktop to individual Android users or hybrid groups that include non-Apple users.
Make sure you are logged into the same iCloud account on your Mac (go to System Preferences->iCloud) and on your iPhone (Settings, click your username at the top of the Settings list and go to iCloud). You likely are logged in as the same user but just sanity check this.
on your iPhone go to Settings->Messages and select “Text Message Forwarding”
Select your mac desktop from the list. Once you do, your iMessage application on your Mac desktop will display a string of numbers that you need to type into your iPhone to link up your iPhone and your iMessage app on your desktop.
Once I completed these steps I was able to send txt and iMessage messages from my desktop.
[Note: combine these instructions below for how to lock down AirPlay on an iPad with an app called Volume Sanity and peace will descend upon your house!]
We use Airplay for music throughout the many rooms and audio systems in our house. Our youngest son likes to watch YouTube videos of buses and trucks all day long. In doing so he fiddles around with the settings a lot on his iPad and this inevitably leads to him broadcasting the trucks/busses audio to one of our home’s HiFi’s. This sucks. Especially early in the morning when you awake to the sound of heavy equipment roaring through a not insubstantial sub-woofer in the living room.
Besides the transmission of Airplay audio from his iPad to our home audio, no matter what I do to lock down his iPad under “Restrictions” he’s always finding ways to add events to our Family Calendar and albums full of no pictures to our family shared photo albums.
I wanted to lock down his iPad and disable AirPlay entirely. This turned out to be WAAYYYY more difficult that I thought. After a bit of a rabbit hole I ended up discovering an enterprise deployment tool called Apple Configurator 2. This tool is typically used by large businesses to roll out iPhone or iPads to their employees.
But it also does a really good job at locking down the iPad for our son. Note that following these steps requires wiping out the iPad entirely and starting from scratch so that it can be prepared as a “Supervised” drive. The process is tedious though. If you’ve used Active Directory or any other enterprise profile-type tool you can figure out. Here are some notes though.
create a new organization (i just use our family last name)
IMPORTANT: Skip enrolling in the Device Enrollment Program
ERASE THE iPAD: Plug in the iPad and click “Prepare”
select “Manual Configuration”
check Supervise Devices (you can only apply restrictive Profiles to supervised devices)
I checked “Allow devices to pair with other computers.” your needs may vary.
on the next screen select “Do Not Enroll in MDM”
The rest of the screens are pretty self explanatory
Once the device is prepared and appears as a “Supervised” device
click the App button to install the apps you want on the iPad
you will need to jump through some hoops to install apps once you apply the restrictive profile so pay attention and install all the apps you want the first time to save yourself some grief.
Go to File, New Profile
Fill out the General section
Fill out the Restrictions section (i mostly unchecked EVERYTHING on this tab
I also went to the AirPlay section and added a fake MAC address to the whitelist section, ostensibly only allowing my son’s iPad to connect to a device that doesn’t exist. I used for the MAC 00:00:00:00:00:00
save the profile with a name like “restricted profile”
click the green “add +” button in the toolbar and add that profile to the iPad.
You should be all set at this point with an iPad that has working apps and has whatever restrictions you set in the profile
if you want to install apps after you do this but have disabled the installation of apps in the restricted profile, simply create a new profile that has no restrictions, save it as “unrestricted profile.” Plug in the iPad, delete the restricted profile from it and apply the unrestricted one. Install the apps and then put the restricted profile back on the devices.
I tweaked the settings in the profile multiple times and just get removing and re-applying it until i got it right.
(*Actually only $70 if you already have an amp/receiver to plug into).
We’ve been in our house for almost 10 years and I still haven’t found a good way to take advantage conveniently of the speakers that were built into our Master Bedroom ceiling. The speakers feed into a selector box in the basement which is powered by an audio system in our living room.
The problem with this setup is if I lay in bed an put on some music via the central receiver, the music could also end up playing in the kitchen or living room or outside. At which point I need to head into the basement to fix the speaker selection.
Also, the speakers have an in-wall volume control. And let’s face it, if you have speakers in your bedroom you mostly want a way to play music while laying in bed and not have to get up to dial in the volume from the wall.
So I’ve been thinking of ways to untie the bedroom speakers from the whole house audio system’s selector box in the basement. And I wanted to do so in a way that would not require that I dump a whole lot of money into new equipment.
Other requirements were:
Control music selection and volume from iPhone
Have locally stored music available via connection to networked drive
Play Spotify playlists
Play from my iPhone via Airplay/Spotify connect (so that if I’m listening to something on my phone when I get back from a bike ride I can just send that audio stream to the bedroom speakers when I step into the shower).
Raspberry Pi 3
For Christmas I bought one of my sons a Raspberry Pi and also picked up one for myself. It seemed like it could play a pretty key role in my bedroom audio solution. This kit seemed like the best deal:
I installed Volumio on the RPi and am really impressed by the feature set (Spotify, airplay, networked drives, etc.). However the on-board audio quality of the Raspberry Pi is really crappy.
After a bit of experimenting I noted that the Pi sounded great when I hooked up my $200 USB DAC to the Pi but that was only a temporary solution as I need that DAC for my living room stereo. Thanks to China and eBay, I picked up this DAC that was built for Raspberry Pi for $18. It took about 10 days to arrive from China and it sounds great (I would be hard pressed to hear the difference between it and my more expensive USB DAC).
The DAC component looks complicated but it plugs right into the Raspberry Pi and has two RCA jacks for plugging into an amp.
It took a few minutes of tinkering to get the DAC working with Volumio but once I got that sorted I just needed an amplifier.
Again, I didn’t want to break the bank and as I was only powering two small in-ceiling speakers I didn’t need a whole lot of power, just something with enough juice to sound nice. I found this highly-recommended little guy on Amazon:
This little amp sounds GREAT! Now I can lay in bed at night and queue up some music, adjust the volume, set a sleep timer — all without worrying that my Mozart or Miles is blaring through the backyard speakers.
For under $100 I am very pleased with this solution:
It’s compact – fits in a small shoebox.
extensible – new features are added to Volumio regularly
On Saturday the Tour de France will kick off and I’d like to try to suggest/convince you that it’s worth watching. I’ve been watching it on and off for several years now and watching it more and more closely the past two or three years. There are a huge number of reasons why it’s a difficult event to watch and understand but I’m going to explain here how if you’ve got just a couple of hours to kill you can learn about a truly fascinating event that requires a physical and mental toughness from its competitors that rivals any other sporting event past or present.
First though, let me say that i’m not a huge fan of road racing, especially team-supported road racing (like the Tour de France) where the competitors have a crew that follows them along with spare parts/bikes, etc. To me, self-supported overland racing like the Tour Divide is a much better measure of a cyclist’s moxie. But anyway, that doesn’t diminish the fact that the TdF is still awesome to watch.
What makes the Tour de France (and other pro road cycling races) so difficult to comprehend is that it is an individual sport played by teams. You think you understand this.
For years I thought I understood what that statement meant. Then I watched Slaying the Badger. If you do one thing before watching any of the Tour de France stages, watch this movie. It’s about Greg LeMond’s nasty rivalry with his teammate, Bernard Hinault (aka the Badger).
LeMond is the only American to have won the Tour. His victory predates (just barely) the point at which cycling became a sport of drug-enhanced bullshit artists like Lance Armstrong. The movie is awesome, it’s on Netflix and in under two hours you will come to understand the meaning of the phrase “an individual sport played by teams” in all of its complexity. I implore you to watch this movie. Even if you’re not that into cycling. I bet you will enjoy it. You can’t make up a character as complex and slippery as Bernard Hinault. He’s fascinating to watch.
Which brings us to the whole performance enhancing drug thing which really destroyed road cycling’s image.
Where Slaying the Badger is a vivid picture of total dysfunction at the team level, a new movie that follows around a bunch of younger, (purportedly) drug-free cyclists called Clean Spirit is really a great look at how members of a cycling team can support one another while still pursuing individual victory.
The movie follows around the Argos-Shimano team and its star, Marcel Kittle in last year’s Tour de France. If you watched last year’s tour at all you may recall the moment when British cyclist Mark Cavendish gave Argos-Shimano rider Tom Veelers a shoulder bump and knocked him off his bike. The filmmakers do a really excellent job of capturing how the team responds to Cavendish. Marcel Kittle makes for a great hero in this story that tries to recast cyclists as athletes instead of drug addicts.
Both of these movies are on Netflix. Watch them. Even if you’re not into cycling they give you an awesome background into team and individual dynamics and you also come away with an understanding of just how grueling of an event – physically and mentally – the Tour is for these athletes.
It’s not just the cycling though. The scenery is awesome and NBC does a good job with zooming out to place you at each stage. Kelly and I really enjoyed the footage last year from Arles and Aix where we road bicycles several years ago.
We watch it with our kids and our oldest always has questions that lead to us looking up some town or city or region on Wikipedia.
And then of course, there’s the food. Each stage presents a new opportunity to try a regional wine/cheese/something delicious so that if you can’t ride like the cyclists in the tour, at least you can eat like them.
Oh. Also. To watch the tour, NBC has a mobile that has been getting better and better every year. In past years we’ve just used Airplay to send the live stream (or archived stage coverage) to our Apple TV. Hoping we will be able to do the same this year.