Make user reports show latest name for trippers

[ghost]

As can be seen on https://archive.installgentoo.net/g/reports/post-count - I have some mojibake next to my trip there. Now, the characters getting messed up probably isn't fuuka's fault, however I haven't had that smiley thing in my name since 2009 I think.

So, the issue here is that post-count used to show the last name used with a trip, but now shows the first name since the archiving began which can't be intentional. Aaron tells me it's a fuuka issue and wanted me to report it so yeah.

[eksopl]

First name seen for that trip getting recorded is intentional. Do you think it should use the last one instead?

The mojibake on early /g/ posts is due to green-oval using an old version of the MySQL Perl DBI drivers early on that messed things up.

[eksopl]

Though, for users with trips, the link should point to a trip-only search, not a username + trip search. Otherwise it'll miss posts for users that changed names.

[ghost]

Yes, it definitely should show last name used, many people have changed name but kept the trip, and I feel like the reports aren't up to date now.

[ghost]

Oh yeah, that too.

[eksopl]

It updates the post count, it just displays the first name that user used. That feels accurate to me, to be honest, especially for new users report. I don't want to keep two nicks for trippers in the database (first and latest).

But yeah, the search link is a bug.

[ghost]

I think last name used would be preferable in new-users as well, the way I see it it is also "out of date". Especially when a some old users will now be stuck with the mojibake..

[eksopl]

bluh bluh primary key for the _users table is (name, trip).

I'll roll the fix for this out with the next batch of DB changes.

@woxxy, did you guys perform any modifications to _users so far? We're going to need an auto increment user_id field to become the primary key for this. And keep a unique index on (name, trip).

Unless we add a last_name field. Either way, it's a DB schema change on _users. What do you guys prefer? Personally, I'm not against having IDs for everything on each table.

(I commented on the wrong issue, herp)

[woxxy]

We didn't touch _users, and I like the idea of having IDs for everything. We didn't touch _daily either. Add IDs on those.

We removed p_doc_id from the _threads table. I also added extra indexes there and made num PRIMARY. Will then make num UNIQUE and thread_id PRIMARY. You can leave this to me so I can send it in the pull request with the migration.

_images got media_id now, that's in the migration as well.

[eksopl]

For the _threads table, you mean parent, right?

From a database design point of view, it makes sense for the primary key on _threads to be a foreign key that references the main table, since _threads is just a subset of the main table, but with extended fields. From a pure point of view, the key for _threads should be doc_id_p like it currently is, a foreign key referencing the primary key of the main table. But having parent instead seems reasonable in this situation, too. It doesn't make sense to have unique IDs for that table, though. They'd either be left unused or you'd need extra, redundant mappings in the main table, like you did with media_id.

For _daily, unique IDs also seems unnecessary, considering the first row already represents a day. It's a number, it's unique, it doesn't need to be updated and it increments in a predictable fashion. Looks like an ID to me.

I'd say, leave it as: board: doc_id board_daily: day board_images: media_id board_threads: parent board_users: user_id

[woxxy]

Yes, I meant parent.

Had removed doc_id_p because we went with BEFORE INSERT which didn't allow us to get that number, but now we have AFTER INSERT, so it can be reimplemented without effort, though not needed.

Let's add user_id. media_id and parent are already done.

Inviato da iPad

[eksopl]

Sounds good to me.

Trigger galore is making me increasingly uncomfortable, though. Especially on MySQL. I was thinking, and inserting / updating the _images table should probably be done manually by the inside a transaction and not left to the trigger.