Reindexing my forum

[Senkusha]

I'm not sure where to put this, so please relocate if needed.

I'm in the process of manually adding old messages from an archived forum (that I don't have access to the database, it was lost when the host crashed, and I was an idiot and didn't maintain a backup).  Upon completion of this task, I'm going to want to reindex my site.

The way I understand SMF organizes topics to be displayed is based on ID_MSG and ID_TOPIC.  The higher the ID, the more current the message.  But, because the poster_time is being manually changed to reflect the original post time, the order is inconsistent.

After thinking about this problem, I ran the following through ChatGPT to see if I'd forgotten anything, and this is what it told me.  I want to check here with people who are intimately familiar with the software to ensure that I'm not going to complete corrupt anything.  (I'll be doing this on a copy of my current live database before running it on the live database (after yet another backup!)

Code Select Expand

<?php
$db = new mysqli('localhost', 'username', 'password', 'database_name');

if ($db->connect_error) {
   die("Connection failed: " . $db->connect_error);
}

// Step 1: Add OLD_ID_MSG column
$db->query("ALTER TABLE smf_messages ADD COLUMN OLD_ID_MSG INT;");

// Step 2: Copy ID_MSG to OLD_ID_MSG
$db->query("UPDATE smf_messages SET OLD_ID_MSG = ID_MSG;");

// Step 3: Export data ordered by POSTER_TIME
$result = $db->query("SELECT * FROM smf_messages ORDER BY POSTER_TIME;");
$data = [];
while ($row = $result->fetch_assoc()) {
   $data[] = $row;
}

// Step 4: Truncate smf_messages
$db->query("TRUNCATE TABLE smf_messages;");

// Step 5: Reset AUTO_INCREMENT
$db->query("ALTER TABLE smf_messages AUTO_INCREMENT = 1;");

// Step 6: Insert exported data
foreach ($data as $row) {
   $columns = implode(", ", array_keys($row));
   $values = implode("', '", array_values($row));
   $db->query("INSERT INTO smf_messages ($columns) VALUES ('$values');");
}

// Step 7: Update smf_topics
$db->query("UPDATE smf_topics t JOIN smf_messages m ON t.ID_FIRST_MSG = m.OLD_ID_MSG SET t.ID_FIRST_MSG = m.ID_MSG;");
$db->query("UPDATE smf_topics t JOIN smf_messages m ON t.ID_LAST_MSG = m.OLD_ID_MSG SET t.ID_LAST_MSG = m.ID_MSG;");
$db->query("UPDATE smf_topics t JOIN smf_messages m ON t.ID_MSG_MODIFIED = m.OLD_ID_MSG SET t.ID_MSG_MODIFIED = m.ID_MSG;");

echo "Reindexing complete!";
?>


[sudoku]

ChatGPT?
While some AI models are getting better all the time, they do make mistakes and often. They even have a disclaimer, stating something to that effect.

Why didn't you just come to the experts here first with your plan?

[Kindred]

In short no.

The method that SMF uses to display the post order is baked into ALOT of places and changing it on a global level involves ALOT of changes. There is no simple change

Additionally, changing the database order like that **MIGHT** reorder the existing records -- but it would then start using the old method as new posts and topics are made.  Additionally additionally -- although it TRIES to, I do not think that this correctly accounts for the change the IDs of the first/last messages in a topic

You are trying to change a core component of how SMF works... (which would be erased during any future upgrade -- or completely break)

[Senkusha]

I want to ensure we're on the same page.  Message posts are stored in the smf_messages table, and topics in the smf_topics table.  I would be essentially creating a temporary mapping field with the current id_msg in the smf_messages table, and ordering the records by poster_time.  This will be exported to a file to be used later.  Then deleting all the message data in the smf_messages table and inserting the exported records into the smf_messages table after the auto_increment is reset to 1.  The original id_msg would be purposely omitted.

Now that I have the records back in the database in the proper order, it's time to remap all the other message ids by running an update query based on the temporary id_msg field and querying the newly inserted id_msg to update those fields.  Finally, I'd need to run a script to figure out the last message of each topic, grab that id_msg and update the smf_topics table.

I'm not wishing to change the internal ordering mechanism, only to reorder the records in the database according to their posted timestamp.  Once the data has been updated, there won't be any need to run this again.

p.s. also need to do something similar with the smf_topics table to get the id_topic in the correct order.

[@rjen]

I cannot comment on the exact queries to be used, but I do know that it is technically possible to do something like this; years ago we followed a similar process when we integrated all topics en messages from an old Snitz forum into an SMF forum.

We did not use an extra field but instead increased all topic ids with 10.000 and all message ids with 100.000.

Then we inserted the old topics and messages in the now empty ranges...

It is possible, just make sure to test the process and validate the results on a test copy of the forum before you do it for real...

And make many backups before and during the process...

[shawnb61]

Dangerous & ill-advised, but not impossible. 

Don't forget there are message IDs also in topics, boards, attachments, user_likes, log_actions & log_reported. 
- They're also embedded in any forum post links, those will break.
- They're also embedded in quoted text links, those will break.
- They're also embedded in message links shared with other sites, even search engines.
- I see them in folks' signatures a lot, too...
- They're also in log_boards, log_topics & log_mark_read, so everyone's picture of what they've visited will break (the various unread & new indicators throughout).

That's just messages.  Topics has a simlar, but slightly different, list... For topics, don't forget calendar entries if you use those.

It's also likely that mods are affected.  You'd need to analyze your mods.

If I were bringing over old topics & posts manually, I'd go another route.  I'd create a new category & board for them, and add them there.  Nobody will be following these new boards (unless they see it & choose to) so for the most part they won't receive notifications. 

Make it a feature - e.g., "Greatest Hits".  Post an announcement that's what's going on.  Folks would probably like that visibility, actually. 

[Senkusha]

Wow!  Okay.  There are many things that I've failed to take into consideration regarding this process.  I think I'll just leave it all be and I'm sure the internal message order won't be all that affected eventually... the issue will probably just phase out... unless somebody is looking for it (like me, who knows it's there. LOL)