Author Archives: Andrew

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!

Andrew’s Simple PHP Router

Published / by Andrew

A little while ago, I created a routing tool in PHP for a small website that I made. Essentially, it handles routing with as little as necessary to jumpstart MVC coding as closely to vanilla PHP as possible.

I created this because I felt like the MVC frameworks I’ve come across were far more complicated than necessary for small projects, and sometimes even for large projects.

Andrew’s Simple PHP Router

Threadstr is down

Published / by Andrew

Threadstr is currently down due to ipv6 issues. Hopefully, I’ll have time to create a new droplet tomorrow.

Update: Since Threadstr is so far from completion, I will not be creating a new server until a release candidate is available. (That could be awhile, because I have a lot of other projects that I’m working on.)