Monthly Archives: February 2019

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.