I must be dreaming, somebody Pinchflat me!

This is going to be another "I did a thing with AI" post, so if that's not interesting to you, bail. Primarily, this is a "I pushed myself to work with AI to better understand its benefits" post, but I get that some people are anti-AI, and though I sympathize with them, I also understand that it's now going to be a part of my daily job. Just as much as the assembly line became a part of the auto workers job, AI is now a tool that companies will use regardless of my personal beliefs, thus I may as well familiarize myself with the tool, and be able to do my job with a high degree of expertise, instead of bailing on the career as a whole.

To this effect, I decided to take on a task that has been bothering me for some time. Specifically because I perceive the issue to be a database/infrastructure problem, and not a coding problem. Long story short, after finding and deploying Daniel Da Cunha's fork of the Pinchflat repo, I started running into "Database Locked" errors. These errors could be caused by several things that I'm doing:

1. I'm grabbing 3 large channels, and though I have them in a "rolling" state (download cutoff date, and 60 day window), the indexing* of these channels often fails because of the time it takes.
2. Because I have this deployed on a K3s cluster, the actual database file is served over NFS, which may be causing a slow down/delay on the file handler, thus the "Database Busy" error
3. According to Pinchflat, I have about 900 videos (across various channels, playlists, and services) current downloaded/enqueued. I feel like this may be enough that the engine itself maybe having issues trying to keep up with the indexing, and querying of the data if care was not taken to optimize that access or maintain the underlying file.

Now, there are several ways to attempt to fix this issue:

1. I could dig around in the code, and see if I can optimize the queries, or add indexes to optimize the database handling. The quicker the queries, the less likely for the database to be "Busy"
2. I could try to figure out if the code is trying to connect to the database multiple times, which would also cause this issue
3. I could figure out how to have the database in memory and have a copy in/out file sync when Pinchflat restarts, thus eliminating the NFS stuff.
4. I could add database support for an external database (MariaDB/PostgreSQL) and attempt to fix it that way since it would remove the NFS issue, and hopefully prevent the "Database Busy" issue, as it would allow for multiple connections, and a read operation wouldn't block a write operation. 

Since my point was to utilize AI to do the work, I decided to take on the task of adding multiple database support, it was only an added benefit that the code is written in "Elixir",  which I had never worked with and thus would be less tempted to just write it myself. I decided this for a couple of reasons other then just the AI, the primary being that allowing Pinchflat to talk to an external database would also provide me access to the database. This would mean that:

1. I can see how the data is handled, have a better understanding of what indexes are there, and what data lives in the columns. All the better to optimize with later.
2. I can alter the data without much effort, this would be handy in cases where I change the file template format, or update the path on the deployed pod. That way I can keep Pinchflat in sync of where the files are instead of having it "lose" them and attempt to re-download them.

And thus began my {very} invasive rewrite of the Pinchflat repository to allow it to work with more then just a SQLite database. At first the AI basically just wrote a wrapper around the database types and attempted to swap the "expected" one at run time. Turns out that Elixir wouldn't let that fly as it has a build time/run time type checks, so it wouldn't load the image because the runtime failed the build time checks. 

Next we broke the databases actually out into type, trying to avoid any build time requirements. That lead us to basically just runtime classes that wrapped the native Ecto.adapters. Which seemed to be working, but I soon discovered, that though the page loads and I can add sources, the sources never get indexed, nor downloaded. I suspected that the AI broke the background job handler.

Well, I was half right, turned out that several of the queries that are used, use functions/syntax specific to SQLite, and thus failed when trying to run on a MariaDB database. So, well the AI did break the indexing, its more, we hadn't finished the work of conversion.  

Thus the next steps were to 

A) have the AI double check other queries to make sure that they are MariaDB compatible, or rewrite them to make them compatible

B) have the AI double check the migration scripts, as Intent may not have converted correctly, as an INT in SQLite is 64bits, where as an INT in MariaDB is 32bits. So we need to double check that the intent of the column actually matches the type of data stored.

Once I had that figured out, I stepped away for a bit. 

This turned out to be an....unwise idea.

Mostly because I went back to the master branch and attempted to fix pain points that were currently affecting me. The biggest among them was the inability to properly index* larger channels. Ones that upload, mostly, everyday and have done so for YEARS. Meaning that even though my cutoff date was 60 or so days, Pinchflat was trying to index thousands of videos. This would lock the database during the indexing and cause everything else to fail. 

I did eventually get this issue and a couple others "resolved" in the main branch, but now I had to merge these changes back into the multi-database branch. This seems like a no-brainer right? Wrong, several of the changes are specific to SQLite as they were for better performance, so I had to navigate the AI through the INTENT of the changes, not just the syntax. That way the same optimizations could be made to the other databases, or discarded entirely if not applicable. 

This proved a little trickier then I originally thought, as MariaDB does not have a notion of "VACUUM", and the indexes that needed to be applied to SQLite to make it performant, weren't needed for MariaDB, due to functionality that MariaDB has that SQLite does not. In the end it took about 4 hours to navigate the changes I had made to my master branch, and these changes are still in testing.

At this point, I had a semi-stable implementation of Pinchflat that works with both SQLite, and MariaDB.

"Wait! You haven't dealt at all with PostgreSQL!" said the reader.

Correct. I wanted to get MariaDB to a stable state before testing with PostgreSQL, but now seemed to be the time to start working on the PostgreSQL side of things.

Luckily for me, when I was prompting the AI, I had the forethought to also ask it to set up to support PostgreSQL at the same time as MariaDB. Attempting to mirror the changes in both engines, in the same commits. So when I fired up a PostgreSQL server, and pointed Pinchflat at it, Pinchflat happily connected, created the tables, and started indexing the larger source that I had deemed the "test" source. 

Unlucky for me, PostgreSQL had a whole host of new issues, some that I think effect both PostgreSQL and MariaDB, but didn't arise initially because while testing PostgreSQL I added more test sources to ensure working queries. Others seem to be specific to PostgreSQL, and are due to the AI misinterpreting the intent of some db migrations. In any case, I had found more bugs for me to walk the AI through fixing them. Thankfully, the bugs were kept to a minimum, and I was able to clean them up within a day or so. 

Thus, I now have a Pinchflat that (hopefully) supports SQLite, MariaDB, and PostgresQL for a back end. Meaning that I can now attempt to "fix" the "Database Busy" errors that have been plaguing the logs of my pod for a couple of weeks now.

Not that anyone wants it, but here'e the code base at this current state:
Pinchflat.zip

*I'm not using fast indexing, as the fast index source(the RSS feed) is inconsistent, and often unavailable, leaving the indexing to just hang, fail, or not pick up videos at all.