Tags, you’re it!

Hey all, hope you’re doing alright. As promised, I’ll be talking about the tag system and how it pertains to the story list this time around.  

In order to have a dynamic number of tags for a variable amount of stories, inputting data in a relational database is an obvious starting point. That tags ought to be their own table, with a column for a unique id at another with a human-readable name (eg: “Alice Margatroid”) at the very least. I added another column if I needed to specify a tag “type” (such as character, genre, etc) and a final column to keep track of the number of times a tag is used. I may remove the last one down the line pending how other features come along.

There already was a story list table stored in THP’s database. It has a unique id for every story, title, the author’s name, tripcode, etc. A simple solution would be to have tag ids in their own column for each row. But I decided against that, not really keen on making this column have potentially overly long fields; any particular story might have at least a dozen tags and it makes for messy reading and retrieval.  

I opted for yet another table that contains two columns—storyid and tagid. Each tag for a story has its own id, which is the same as the one in the tag table. While the lookup process is a little more complex, it also offers some more flexibility in looking up data starting with either a story and its information or a tag and its information. Getting the other category of information is easy from that starting point. Coincidentally, I later took a peak at how open-source booru shimmie arranges its tags in the database and found a similar setup.  

All of that is fine and dandy but there’s also the problem of populating the tags table and the tagged stories. The former has more or less been implemented already and, if you’re on discord, may recall me sharing this page a couple of weeks back.  

Aside from listing extant tags under each category, it also allows anyone to submit a tag under a category. These submissions are then separated and sanitized before input into the database. If you’re unfamiliar with what a sanitized input is, it basically means take steps to ensure that it can’t do anything unintended. Think possibly arbitrary commands that may be executed by malicious actors or invalid data being stored. In this particular case, anything other than a number or letter is removed from a submitted tag. Some more manipulation happens before a tag is inserted, replacing underscores with spaces, and capitalizing the first letter of words.  

Additionally, since it’s unlikely that we’ll have tags that number in the thousands—there’s a limited amount of characters and genres, after all—I made it so that any submissions must also be manually approved by an admin (so, me). This isn’t really about security but more that I think that certain tags may be a repeat or otherwise overlap. There’s no need to have a confusing free-for-all mess. Any pending tags that I haven’t approved are listed on the bottom of the tag submission page.  

All this code, along with the stuff on the management page side of things, was fairly easy to do and required a regular amount of effort. I had spent more hours in doing the previous groundwork, like simplifying some of the database structure or refactoring other related code than actually implementing tags and the submission page.

Now, as for tagging stories themselves… I haven’t gotten to that yet!

The front end parts where users/moderators assign tags, set synopsis, threads and all that jazz is something that I’ll get to sometime soon. That part will require quite a bit of work as there are a lot of checks and features that need to be integrated for that to work sanely. I’ll share details in another one of these posts once I’m mostly done.  

I’m not sure if the next one of these will deal with that or with something else, but expect one sometime soon regardless. Until then, take it easy!

P.S. I didn’t mention it but each story can have a synopsis too. Synopses are like tags. Structurally even simpler: a table with the synopsis column (with a character limit) and storyid column.