So, you accidentally dropped a Git stash.

Published / by Andrew

(Note: The information from this post comes from this SO question. I’m just collecting what I found to be the best parts of it.)

(Note 2: I’m using Linux with Bash, and the commands here probably won’t work in Windows. They may work in MacOS, though.)

They say that coding is 99% boredom and 1% pure terror.

I experienced that terror today when I had meant to type git stash pop, but accidentally typed git stash drop.

It turns out that the solution is much easier than you might expect, though finding it was a minor challenge. Upon dropping a stash, Git does not delete the information in the stash– It looks like it just deletes the reference. If you can find the hash, you’ll be able to still restore the stash.

If you only just now dropped the stash and still have the hash listed on your screen, skip ahead to the section involving actually applying the stash (just one command). Otherwise,

Finding the hash that matches the stash.

If, like me, you already tried some commands and no longer have the hash on the screen, the first thing you’ll need to do is figure out which hash is the one from the dropped stash.

The first thing you’ll want to do is echo out a whole bunch of information from git diff. You can echo it into a file (which is what the link above does) or you can pipe it into less, which is what I’ll do here–

git show $( git fsck --no-reflog | awk '/dangling commit/ {print $3}' ) | less

At this point, you’ll need to know something unique (or nearly unique) about your particular stash that you can search, and you’ll need to be able to recognize the changes well enough to identity which stash you need. Something unique like this could be the name of the stash, if you remember it and it’s unique, or the date (in format Fri Jun 28 15:09:54 2019 -0400), or maybe a function name that you wrote. Just anything that you can use to search via less to find the stash you need.

To search with less open, press the / key, and remember that search is case-sensitive. In my case, I had written a function that I distinctly remember writing, radval. (Also note that to move to the next match in a search, hit n, to go to the previous search, hit Shift+n. Scroll with arrow keys, but look up more details for less if it’s a program you plan to use a lot, as I do.)

Once you find some code that you know is in the stash you need (or just found the title or something), scroll up, if needed, to the heading of the stash. This will be four lines starting with “commit”, “Merge”, “Author”, and “Date”. The long alphanumeric string following “commit” is the hash, and it’s what you’ll need.

The hard part is done.

Applying the orphan stash.

This part is easy. If you have the stash hash, and for this example I’ll use 73d46febad13327963c7e0bf95ce2829fe35042d, simply put this in the command prompt–

git stash apply 73d46febad13327963c7e0bf95ce2829fe35042d

It should be done now, so you can breath out.

Some helpful VirtualBox command-line reference.

Published / by Andrew

A little bit of background for this post.

I love VirtualBox. Though, to be more specific, I love having a virtual machine to use for testing. Discovering bridged ip addresses was a revelation for me.

But I also love ssh. I regularly ssh from my laptop in my living room to my desktop in my office because I’m too lazy to get up. So, I want to be able to set up virtual machines via ssh.

Docker is pretty neat, but it’s not really designed for what I want it to do. I tried it, and decided it wasn’t really worth fidgeting with it to set up a bridged IP address, which I’m not 100% sure is possible in a Docker container anyway. I used Vagrant from Hashicorp for awhile, which is pretty good, but I’ve encountered a versioning problem– My version of VirtualBox was too new for my version of Vagrant, so it wouldn’t start. So, why not cut out the middleman and just manage VirtualBox via CLI? The only feature from Vagrant I really care about is starting a VM, anyway.

I thought it would be difficult, but it’s actually surpisingly easy. To do this, you’ll need install VirtualBox extensions from their website. Make sure to get the extentions for the right version of Virtualbox– If you’re installing VBox from a repo, it’s probably in the “VirtualBox older builds” page. Also, I’m not bothering with actually creating virtual machines via CLI from scratch, but instead making a base VM to copy when needed. Creating from scratch, I don’t think, can be done via CLI, though I could be wrong about that. So, I created a base VM while physically at the host machine, and can now clone that machine, and can now create a new test server via CLI.

And these are the commands that I use to manage them, in no particular order. “Ubuntu Server” is just an example name, matching whatever the name is that you’ll see when you open the VirtualBox Manager.

IP addresses:
It doesn’t seem to be possible to get a VM’s ip address using VBoxManage if they’re a bridged adapter. (The listed solutions online don’t work.) The best I can find is “nmap -sP 192.168.1.*”, which will list all IP addresses on the network. If it’s run both before and after the VM is started, the IP should be found.

List all VMs:
VBoxManage list vms

List all running VMs:
VBoxManage list runningvms

Start VM:
VBoxManage startvm "Ubuntu Server" --type headless

Pause VM:
VBoxManage controlvm "Ubuntu Server" pause --type headless

Restart paused VM:
VBoxManage controlvm "Ubuntu Server" resume --type headless

Shut down VM:
VBoxManage controlvm "Ubuntu Server" poweroff --type headless

Change network adaptor to bridged:
VBoxManage modifyvm "Ubuntu Server" --nic1 bridged

Clone vm:
VBoxManage clonevm "Ubuntu Server" --name "New Ubuntu Server" --register

  • If forget the “register” option, the “registervm” command is supposed to fix that, but I can’t get it to work. In that case, best to start over and delete the newly-created folder in ~/VirtualBox\ VMs
  • If forget the “name” option, it will just have the old name plus “clone”.

Delete vm:
VBoxManage unregistervm "Ubuntu Server" --delete

The Lost Art of MIDI Music

Published / by Andrew

This one’s a little different, but code-related, so I thought I’d post about it here.

Without going too deeply into the details of how I became interested in this, not long ago I created a couple of Python 3 classes that act as wrappers for MIDIUtil, which builds MIDI files. Yesterday I went ahead and added those to my GitHub page– Lost Art.

It’s not exactly a professional tool, but it’s been a lot of fun to play with.

(Side note: I’ve also started work on my blogging CMS, now dubbed “Nomad“, but it’s in such early stages that there’s not a lot to talk about.)

New project announcement: Yet Another Blog CMS.

Published / by Andrew

When I made this blog, I remember thinking, “Hey, it’ll just be easiest to make a WordPress droplet.” Technically, I wasn’t wrong, but WordPress seems to get more and more counter-intuitive as time goes by, not to mention bloated.

After some frustrating experiences with WordPress (they seem to think I want to write blog posts in a textbox the size of a postage stamp?), I’ve decided on a new project: A basic CMS. (It’s hard to say exactly when I’ll be working on this, but I’m hoping to start this coming weekend, or maybe in small steps before then.)

This CMS will be using some of my unpopular opinions:

  • Simpler is better– Have few features so they don’t get in the way.
  • A blog CMS should be lightweight and quick.
  • Writing in a markup language is better than “rich text” editors.
  • JavaScript should be kept to a minimum.
  • RSS is better than social media.

I know that very few people will use this, but I will, and I suppose that’s all that matters.

That said, I also do plan on adding some interactivity features so that one blog may interact (minimally) with another.

It’s also worth noting that I also plan on bringing Threadstr back from the dead, this time in PHP. I had originally killed it because I was concerned that SESTA/FOSTA would make it impossible unless I have a large staff, but I have ideas on how to make that easier to manage.

Right now, though, I’m more interested in this blogging platform to replace WordPress.

Reducing size of video files via CLI.

Published / by Andrew

(TL;DR: Conversion script is at the end of this post.)

When it comes to video entertainment, I much prefer local media to streaming services, because I like being able to manage as much as I can using Kodi. (I used to use Kodi with an Android TV box, but now I have a laptop hooked up to my TV, but that’s a story for another day.)

But, using local media presents a problem– Space management. Unless I want to keep getting more and bigger hard drives (and I don’t), I have to figure out how to shrink down the video files without losing so much quality that I render them unwatchable. I also want to make these conversions via CLI so that I can do it over SSH.

I developed a small script that converts all files in a directory (not recursively, just because I didn’t want to do it that way) into smaller files. It uses BASH (with which I have a love/hate relationship) and FFMPEG (with which I have a hate/hate relationship). This changes the constant rate factor, changes the video and audio codecs, and reduces the resolution to 540×360 for widescreen and 540xWhatever for 4:3 (too lazy to math right now). I also toyed with changing the framerate, but reducing that made a pretty small change in filesize with a big change in video quality, though it may be worth it if you have a 60fps video.

As it currently stands, this reduces filesize by half for my current files, but that will depend on the source files.

This uses the version of FFMPEG that comes with Ubuntu 16.04 (I haven’t upgraded to 18.04 yet).

Some useful commands when checking the results are:

du -sh filepath     # Shows amount of space a directory or file
mediainfo filepath  # Shows information about a media file, but requires
                    # installing mediainfo from repos.

I’d kind of like to source where I found all of this information, but the script below is a Frankenstein’s monster using organs scattered all across the web.

You’ll want to update the inputDir and newDir variables whenever you run this, or you can modify to use ${1} and ${2}, which I may do for myself in the near future. You do not need to escape spaces, though. (And be aware that this will take awhile if you’re running it on a large directory.) You’ll also want to put this script one directory above the directory that you want to convert, and make an empty directory for the target directory.

inputDir="Jontron"
newDir="Jontron Reduced" # Make sure this exists in the same directory as inputDir.

cd "${inputDir}"

shopt -s globstar # I have no idea what this does, but the script doesn't work if it's not run.

for file in **/*; do
    ffmpeg -i "$file" -c:v libx264 -crf 24 -b:v 1M -c:a aac -filter:v scale=540:-1 ../"${newDir}"/"${file}"
done;

Handling the equivalent of FIRST() and LAST() in MySQL

Published / by Andrew

Something that’s been an enormous pain for years is that MySQL does not have aggregate functions for first() and last(), like every other SQL-based language. Why they don’t have it, I have no idea. It’s been requested and ignored for over 15 years, and doing a search for this online reveals many people frustrated with the lack of this feature. You’ll find hundreds of “solutions”, most of which either don’t work at all or are so convoluted that they’d be nearly impossible to implement.

To make this as abstract and possible, let’s say we have a table that looks like so:

MariaDB [test_db]> select * from test_table;
+----+----------+---------+
| id | ordering | groupid |
+----+----------+---------+
|  1 |        4 |       1 |
|  2 |        1 |       1 |
|  3 |        2 |       1 |
|  4 |        4 |       2 |
|  5 |        6 |       2 |
|  6 |        1 |       2 |
|  7 |        3 |       2 |
|  8 |        8 |       3 |
|  9 |        1 |       3 |
| 10 |        5 |       3 |
+----+----------+---------+

And we need to find the id of the greatest ordering for each groupid.

If MySQL were sane, we’d be able to do this with a relatively simple query similar to this:

    -- Reminder: This does not work in MySQL!  The correct solution is later in this post.
    SELECT
        last(id)
    FROM
        test_table
    ORDER BY
        ordering
    GROUP BY
        groupid

But, no, that’s not possible.

It’s easy to figure out what the solution should be with this query:

MariaDB [test_db]> SELECT * from test_table order by groupid,ordering;
+----+----------+---------+
| id | ordering | groupid |
+----+----------+---------+
|  2 |        1 |       1 |
|  3 |        2 |       1 |
|  1 |        4 |       1 |
|  6 |        1 |       2 |
|  7 |        3 |       2 |
|  4 |        4 |       2 |
|  5 |        6 |       2 |
|  9 |        1 |       3 |
| 10 |        5 |       3 |
|  8 |        8 |       3 |
+----+----------+---------+
10 rows in set (0.00 sec)

We can look at these results and say, “Hey, it’s obvious that the ids I need are 1, 5, and 8.” But that’s not going to do us much good if we’re doing a more complex query.

I’m pretty sure that at one point I had a moderately elegant solution to this using subqueries and LIMIT 1, but I haven’t been able to figure out what that is.

But, considering how aggravating this has been, I thought I’d post my most recent solution so I’d have a reference and will, hopefully, never have to figure it out all over again.

My solution is a join with a subquery (which I’m not crazy about, but it’s better than most of the other really convoluted solutions that I’ve found online).

    SELECT
        tbl1.id,
        tbl1.groupid
    FROM
        test_table as tbl1 INNER JOIN
        (SELECT groupid,max(ordering) as maxorder FROM test_table GROUP BY groupid) as tbl2 ON
            tbl1.groupid=tbl2.groupid AND 
            tbl1.ordering=tbl2.maxorder
    ;

Which outputs:

+----+---------+
| id | groupid |
+----+---------+
|  1 |       1 |
|  5 |       2 |
|  8 |       3 |
+----+---------+

One thing that really stinks about this is that it’s probably not going to be equally useful in all situations, but this will hopefully be adaptable to different needs. Say you wanted to modify all records except the most recent ones, you could use the above as a subquery in a WHERE clause.

I’m not the only person that uses this solution to this problem, but I sure wish there were a way to do this without a subqueries.

Update: It’s never quite as easy as you’d expect.

It turns out that to actually use the above query, you need to do yet another subquery. I actually don’t really understand why it’s necessary, but I was able (with some searching) to figure out how to do it.

Let’s say we want to actually do something with this query. Let’s add another column with tinyint(1) called “myvalue” and set them all to false.

+----+----------+---------+---------+
| id | ordering | groupid | myvalue |
+----+----------+---------+---------+
|  1 |        4 |       1 |       0 |
|  2 |        1 |       1 |       0 |
|  3 |        2 |       1 |       0 |
|  4 |        4 |       2 |       0 |
|  5 |        6 |       2 |       0 |
|  6 |        1 |       2 |       0 |
|  7 |        3 |       2 |       0 |
|  8 |        8 |       3 |       0 |
|  9 |        1 |       3 |       0 |
| 10 |        5 |       3 |       0 |
+----+----------+---------+---------+

Suppose we want to set all but the most recent value to true for each group id.

So, this does not work:

-- Reminder: This does not work in MySQL!  The correct solution is later in this post.
UPDATE
    test_table
SET
    myvalue = 1
WHERE
    id NOT IN (
        SELECT
            id
        FROM
            test_table as tbl1 INNER JOIN
            (SELECT groupid,max(ordering) as maxorder FROM test_table GROUP BY groupid) as tbl2 ON
                tbl1.groupid=tbl2.groupid AND
                tbl1.ordering=tbl2.maxorder
    )
;

This results in the following error in MariaDB:

Table 'test_table' is specified twice, both as a target for 'UPDATE' and as a
separate source for data

I think I got a different error in the AWS database based off MySQL, but the result is the same– This does not work.

Instead, we have to use yet another subquery. So, this does work:

UPDATE
    test_table
SET
    myvalue = 1
WHERE
    id NOT IN ( -- Starting the added subquery here!
        SELECT
            id
        FROM (
            SELECT
                id
            FROM
                test_table AS tbl1 INNER JOIN
                (SELECT groupid,max(ordering) AS maxorder FROM test_table GROUP BY groupid) AS tbl2 ON
                    tbl1.groupid=tbl2.groupid AND
                    tbl1.ordering=tbl2.maxorder
        ) as selecttbl
    )
;

When we run SELECT * FROM test_table ORDER BY groupid,ordering;, we can see that it was successful.

+----+----------+---------+---------+
| id | ordering | groupid | myvalue |
+----+----------+---------+---------+
|  2 |        1 |       1 |       1 |
|  3 |        2 |       1 |       1 |
|  1 |        4 |       1 |       0 |
|  6 |        1 |       2 |       1 |
|  7 |        3 |       2 |       1 |
|  4 |        4 |       2 |       1 |
|  5 |        6 |       2 |       0 |
|  9 |        1 |       3 |       1 |
| 10 |        5 |       3 |       1 |
|  8 |        8 |       3 |       0 |
+----+----------+---------+---------+

Yikes.

Andrew’s Urgency Manager

Published / by Andrew

I created a project to help me manage the “urgency” of issues.  (Probably not the best term in retrospect.) I often have issues brought up that are ignored for too long because they’re “low priority,” so other issues take precedent, and newer issues keep getting added, pushing these old issues down in the priority list.

Andrew’s Urgency Manager

AUM is a command-line tool written in Python that helps manage that problem by increasing the priority of an issue over time, so even if an issue is originally assigned as a low priority, its priority level increases over time.

More details can be read on the front page of the project (the link above). My general workflow uses AUM to manage the priority of different projects and my todo syntax highlighting to manage details of a project. (The latter being what Mondonotes is based on, which I use a lot for non-work stuff.)

MondoNotes

Published / by Andrew

I’ve recently launched my latest project, MondoNotes.com.

MondoNotes is a task manager (as in, to-do list) and note-taker, but works differently from most task managers. At its core, MondoNotes is nothing more than a syntax-highlighter, changing colors based on whether you’re marking something as “todo” or “done,” or “information.” This is simple, but allows much more detailed projects and is far more intuitive than any task manager that I’ve used before. The syntax highlighting really helps to scan through the existing notes.

I’ve used this method for several years and slowly developed different features, but, until recently, I had only used a vim syntax file. MondoNotes is more accessible to non-Vim users, and has the advantage of being “in the cloud”, so I don’t have to worry about syncing files or downloading anything.

A video introducing Mondonotes can also be found here.

If you’re reading this, I hope you’re interested enough to give it a try. To say that this methodology has saved my skin and made projects much easier to manage would be an understatement. Thanks for reading!