Killing Links and Users

As mentioned in my <a href=ShowJB.php?page=jb13.htm>last column</a>, one of the major problems that has plagued The Shadowrun Archive over the last year has been the large amount of dead links it contains. I had Saturday night off work, so I spent much of the day working on that problem - first using an automated program to check all the external links for validity, then working my way through all of the dead links to double check that they were indeed dead, and deleting them. The total was impressive - between 150-200 of them, and it took a good portion of my afternoon. Time well spent, IMO. It was a good start on getting the database in good condition so that converting it to the new formats when those are finalized would be less painful - change is never painless.

With that achievement down I turned my attention to the User database, which to my surprise contained about 1800 entries - more entries than we have submissions! Sure enough, some bugs in the code had resulted in many many entries being duplicated, and blank entries had also made their way into the database. Pruning the blank entries was easy enough, and then I set about writing a script to check for duplicate entries that had no Articles attached to them.

At this point I’ll digress and explain a bit about the setup of the Archive database, which is a relational database. There is a table “Users” which contains all the information about the users, and a table “Articles” which contains all the articles. One of the fields is “Articles” is “ArAuthorID”, which points to the author of the article, from the Users table. With the two tables linked in this way it’s possible to take a single user and find all the articles he’s written, or find the author of any given article, etc.

So at this time my idea was to simply loop through the database, and if a User had no articles in the database assigned to him I would delete the user. This would delete duplicate entries that hadn’t been used, and although it would delete new users that hadn’t submitted anything yet, the registration process is short so I didn’t think that would deter anyone from re-registering. Plus, it would speed up the Archive overall, and once again make it easier to convert the old data to the new formats.

Everything seemed fine. I tested the code a few times without actually committing the changes to the database, told Paolo to back the database up, and then ran the script. Which, due to a misplaced “{“, instead deleted every single entry from the database.

I laughed. I then told Paolo to send me the backups while I went for Pizza, and I would fix the problem when I got back.

I returned from the Pizza - Mexican, with no peppers - and began digging through the CSV file that Paolo had sent me to make sure it would import correctly. I checked that the basic functions of the Archive were working, and it seemed to be; although some parts were broken the major features seemed to work alright. I posted a quick note on the Archive about it and went to bed.

After work the next day I really wasn’t in the mood to work on the database, but I made a few attempts to import the data, each one eventually resulting in failure. I gave up - better to not do anything than to do something I’ll regrget - and went to bed early. Today, double checking the CSV file I found that partway through it many of the entries had incorrect ID numbers, and it would be impossible to import it correctly - the ID numbers need to be unique, and these ones weren’t. A quick discussion with Paolo was had with no conclusion reached, as he wasn’t sure if he still had the backups at home or if he had deleted them after sending them to me, and my copy of the backups had been modified. So rather than have the Archive working but partially broken (Almost all the features that related to Users, including just displaying the author of each link were broken), the code to display authors was quickly commented out. The problems are still there on the back-end, but the viewers of the Archive don’t see them - at the expense of some loss in functionality for the time being.

There’s been no decision yet, but I think the likely course of action will be to leave the Archive in this state until the new version is ready - fixing all of the broken User data will be a major job that is probably better spent on getting the new version up and working. It’s not an optimal solution but it’s the best one we have at the moment. I apologize for any inconvenience this may have caused.