Attachment Issue with Port form Vanilla 3 to Flarum

[MrCaspan]

So I am now on to testing transferring my image imbeds. The URL Imbeds that are in a post are easy to deal with. Side problem is Vanilla put absolute paths to each image IE https://domain.com/uploads/editor/es/ererwerwer.jpg instead of a relative path.

This is not the end of the world because I can just run a quick SQL query to update any of these absolute paths to relative paths

SQL For anyone that might need to do the same!

UPDATE posts
SET content = REPLACE(content, 'src="https://domain.com/uploads/', 'src="/assets/files/')
WHERE content LIKE '%https://domain.com/uploads/%';

The real problem I am having is with attachments, it seems your tool has populated the fof_upload_files table with all the files form the old forum but they are not attached to any post. It seems like there is a fof_upload_file_posts table that keeps a record of the ID of each post that a file is attached to. I tried manually adding one of my attachments here and link it to a post id. It seems like you need to reference in the post the UUID of the file, see example below of an attachment I did using the new system.

Add the file info to the fof_upload_files table (with UUID??)
Add the post ID and attachment ID to the fof_upload_file_posts
Add the code to the end of the post.

Here is a sample of what that embed code for an attachment looks like

[upl-image-preview uuid=ebd0eafb-8335-4f46-b3db-2723ccaf07d3 url=https://domain.com/assets/files/2024-12-31/1735616935-268987-img-20181127-155639-193-copy.jpg alt={TEXT?}]

Any ideas if this will work or expected to work?

[linc]

Are you sure that association in fof_upload_file_posts is necessary? I'm fairly certain I tested this as working so I'm a bit surprised if it's that far away from working.

There is a post_id on fof_upload_files that gets populated if the info is available. Unfortunately, it's not always — early file uploads in Vanilla didn't record the post_id as I recall, for example.

There's also a broader URL problem — it's not just files that will have the old URL, it's internal links to your own discussions you created. There's certainly an argument that the Postscript should be more aggressive in its cleanup — URLs, emojis, and perhaps other post formatting work.

One thing you'll probably keep running into is that Flarum is far more aggressive in its data strictness than Vanilla was. Whenever we built a data structure in Vanilla, top of mind was how we were going to import various systems into it, and so we made a lot of things optional — a "graceful degradation" if data was missing. Flarum follows a "don't trust it if anything is slightly off" philosophy, so it can be very fiddly to get each piece working 100%.

[MrCaspan]

Okay so I just did another fresh import and you are correct in the fof_upload_files table, I can see the attachment and the very last column is post_id and it does reference the proper post_id that is on Flarum that this attachment should be on! (look at first row)

But as you can see by the photo above this is the post the attachment should be on (note the post id in the code) but no attachment shows up

Any ideas?

[MrCaspan]

@linc I am also willing to donate my database to your service if that helps to figure this out

[MrCaspan]

So talking to the users at FoF post it seems like the structure has changed and you must have a UUID on each file (seems like it can just be randomly generated) then you need to add a reference to the fof_upload_file_posts table so that you know how many places its been used and links to that location. then you have to add this BBCode (Not BBCode but like it) to the post.

I am going to do some tests to see if this will work and if it will I am going to just write some php to take each Attachment and generate a UUID then append that code to the end of a post it references then add a entry to the fof_upload_file_posts table!

Unless you get to it first but this is my only show stopper for me to migrate!!

[MrCaspan]

Okay sorry for all the messages. I have figure it all out and how attachments work (I THINK)

After you bring the attachments over they MUST have a UUID, this is how fof targets the attachments. If you don't put a UUID on an attachment you WILL be able to see and choose attachments form your My Media page, but nothing will show up on the page because it does not have a UUID. Also there is a weird error that if you upload a NEW attachment, that attachment will have a UUID and none of the other attachments will and this causes the my media page to just spin forever when trying to load. You will see an error in the browser console about all or none. It was hard to figure out what it was talking about. Finally I realized either No file can have a UUID or ALL files need to have a UUID. Problem with that is as soon as you upload a new file it will be given a UUID and will break the My Media page again. Bottom line IS every file must have a UUID.

I created a PHP file (I just called test.php) . I know there is a better way to do this but it works:

<?php

	// Database connection details
	$host = 'host';
	$dbname = 'db_name';
	$username = 'db_user';
	$password = 'db_pass';


        // Generate a UUID
       // I know there is a UUIDv4 library already used by fof_upload I could use but this works
	function guidv4($data = null) {
		// Generate 16 bytes (128 bits) of random data or use the data passed into the function.
		$data = $data ?? random_bytes(16);
		assert(strlen($data) == 16);

		// Set version to 0100
		$data[6] = chr(ord($data[6]) & 0x0f | 0x40);
		// Set bits 6-7 to 10
		$data[8] = chr(ord($data[8]) & 0x3f | 0x80);

		// Output the 36 character UUID.
		return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));
	}



	try {
		// Connect to the database
		$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
		$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

		// Fetch all rows
		$query = "SELECT id FROM fof_upload_files";
		$stmt = $pdo->query($query);

		// Prepare an update statement
		$updateQuery = "UPDATE fof_upload_files SET uuid = :uuid WHERE id = :id";
		$updateStmt = $pdo->prepare($updateQuery);

		// Generate and assign UUIDs
		while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
			$uuid = guidv4(); // Call your existing function to generate a UUID
			$updateStmt->execute([':uuid' => $uuid, ':id' => $row['id']]);
		}

		echo "UUIDs successfully generated and stored.";\

	} catch (PDOException $e) {

		echo "Database error: " . $e->getMessage();
	}

?>

Upload the file to your site and hit the URL, this works really well to make sure all your attachments have a UUID.

Now for the fun part. fof Upload uses some kind of BBCode or something like that to make a attachment show up in a post ALSO there are SIX different way of presenting an attachment

Default File Download Template - Just makes a file button with the name and size of the file for downloading
Default Image Download Template - It allows you to download the image with no preview makes a file button
Complete Image Preview Template - [upl-image-preview uuid={files UUID} url=https://{Image URL} alt={TEXT?}]
Just URL - I am assuming the is a URL to an external source like imager
Markdown Image Template - ![Image description](https://domain.com/assets/files/image.jpg)
BBC Code Image Template - [URL=https://{Image file}][IMG]https://{Image file}[/IMG][/URL]
Text Preview - Seems like you use this when you want a text file to be previewed in a post? Weird but I am sure its there for a reason

The reason for these are you can specify WHEN A FILE IS UPLOADED if it's file type matches one of your specified MIME types you set in the extension settings it will store the information in the fof_upload_files table and then in the tag column it will store HOW you want to handle this file type when you choose it from your media manager and inset it into a post.

What this means is you will have to look at your fof_upload_files table to figure out what kinds of files you have. You can do this by running the query below to see how many unique file types you have. For me 95% were images and the reset were PDF and TXT

SELECT type, COUNT(*) AS occurrence_count
FROM fof_upload_files
GROUP BY type;

Here is a preview of my result

So you can see there is a bunch of images but not a lot of files. So you will have to decide what template you want to use to open up each file type. For me I wanted to use the Complete Image Preview Template for all my images so that means going through the database and updating all the rows that have images in them so their tag column has a value of image-preview for everything else you will most likely use the file tag. The nitro-porter tool does a great job of already getting 95% of these for you but any file with a MIME type of JPEG gets marked as file not a image-preview. Because for some reason its MIME type is jpeg not image\jpeg So you might have to do some updating of a few records to update any records with that incorrect tagging.

Some simple SQL to do a find and replace this one searches for the file type jpeg and the tag column to be image-preview value instead

UPDATE fof_upload_files
SET tag = 'image-preview'
WHERE type = 'jpeg';

The reason you have to do this is because the tag value will never get updated even if you change it in the backend. It is set in the database when the file is uploaded and what ever it is set to it cannot be changed (unless you do it through the database) So any time you want to add that picture to a post it will use the value that is stored in the database with that file EVEN if you have chenged it in the backend settings. So update them now while you have the chance!!. For me the jpeg was the only MIME type that got labeled as a file so an easy update.

Now that the attachment's database is functional and you can go forward knowing that all files already uploaded are going to use the proper format to display them when user add them to a post.

The real fun part... any old post that was migrated that had an attachment on it will need to be updated to show the attachment. The way it seems Vanilla did things is it just associated a attachment ID to the Post ID and when the post was rendered it would look to see if it had any attachments and would show any attachment under that post. Very simple and very clean to implement.. not for Flarum.. You have to add the code to the post for the file or picture to show up! This means reading in each row of the fof_upload_files table, gett the post_id value, use that value to look up that post_id in the Posts table and once you have found it you will have to add the proper code to the post to make it show up. I chose to just append it to the end of a post

Also just remember the code you see in the WYSIWYG interface for inserting an image is NOT what is stored in the database so insert an image into a post and have a look to see what your code looks like in the database.

IMPORTANT any post that has one of these images in it will be wrapped in an tag (R is for rich text I believe) if its just text only in a post it will be wrapped in a tag (t= text I am assuming). You must place this value inside an tag. If you place this value outside this tag you will get an error and if you please it in a tag you will also get an error

I am still working on a solution and code to update the posts tables with the image tags. Please stand by as I get this code for myself but wanted to update on my progress Linc as you will probably have to do this in your importer .. Might as well share how I did it!

Here is the tag that I am using to insert into a post

<UPL-IMAGE-PREVIEW alt="{TEXT?}" url="{url}" uuid="{uuid}">[upl-image-preview uuid={uuid} url={url} alt={TEXT?}]</UPL-IMAGE-PREVIEW>

Obviously the {url} and {uuid} would be replaced by the values from the fof_upload_files table

Ill post my code once I feel like I have it and also for reference you also need to update the fof_upload_file_posts table to show that this attachment is in this post. I have no clue why but this is what their tools does when you insert an attachment so might as well do the same. Maybe its for a future request like remove the attachment from a post if you delete the attachment or a way to know how many posts with url this attachment was used. Who know s but i want to keep it the same just in case!

[MrCaspan]

As promised here is the code to put each attachment from the fof_upload_files table into each post that it belongs to. Add your DB info to the top and towards the bottom. Save the file and throw it on your server and run it. I have it spit out a URL to each post it adds a link to so that you can check that it worked on a few

<?php

// Database configuration
	$host = '';
	$dbname = '';
	$username = '';
	$password = '';


// Function to format file size with one decimal place
function formatSizeUnits($bytes)
{
    if ($bytes >= 1073741824) {
        $bytes = number_format($bytes / 1073741824, 1) . ' GB';
    } elseif ($bytes >= 1048576) {
        $bytes = number_format($bytes / 1048576, 1) . ' MB';
    } elseif ($bytes >= 1024) {
        $bytes = number_format($bytes / 1024, 1) . ' kB';
    } else {
        $bytes = $bytes . ' B';
    }
    return $bytes;
}

// Connect to MySQL
$conn = new mysqli($host, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Fetch all rows from `fof_upload_files`
$sql = "SELECT * FROM fof_upload_files";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $post_id = $row['post_id'];
        $tag = $row['tag']; // Changed from 'tag_value' to 'tag'
        $url = $row['url'];
        $uuid = $row['uuid'];
        $base_name = $row['base_name'];
        $size = formatSizeUnits($row['size']); // Format size for display

        if ($post_id) {
            // Query the `posts` table for the corresponding `post_id`
            $post_sql = "SELECT * FROM posts WHERE ID = ?";
            $stmt = $conn->prepare($post_sql);
            $stmt->bind_param("i", $post_id);
            $stmt->execute();
            $post_result = $stmt->get_result();

            if ($post_result->num_rows > 0) {
                $post_row = $post_result->fetch_assoc();
                $content = $post_row['content'];
                $discussion_id = $post_row['discussion_id']; // Get the discussion_id

                // Check if content contains a <t> tag
                if (preg_match('/<t>(.*?)<\/t>/', $content, $matches)) {

                    // Replace <t> with <r>
                    $content = preg_replace('/<t>(.*?)<\/t>/', '<r>${1}</r>', $content);

                    // Append the appropriate tag based on `tag`
                    if ($tag === "image-preview") {
                        $append_str = "<UPL-IMAGE-PREVIEW alt=\"{TEXT?}\" url=\"{$url}\" uuid=\"{$uuid}\">" .
                                      "[upl-image-preview uuid={$uuid} url={$url} alt={TEXT?}]" .
                                      "</UPL-IMAGE-PREVIEW>";
                    } elseif ($tag === "file") {
                        $append_str = "<UPL-FILE content=\"{$base_name}\" size=\"{$size}\" uuid=\"{$uuid}\">" .
                                      "<s>[upl-file uuid={$uuid} size={$size}]</s>{$base_name}<e>[/upl-file]</e>" .
                                      "</UPL-FILE>";
                    }

                    // Append the string to the <r> tag content
                    $content = preg_replace('/<r>(.*?)<\/r>/', '<r>${1}' . $append_str . '</r>', $content);

                    $discussion_url = "/d/{$discussion_id}";

                    // Update the `posts` table with the modified content
                    $update_sql = "UPDATE posts SET content = ? WHERE ID = ?";
                    $update_stmt = $conn->prepare($update_sql);
                    $update_stmt->bind_param("si", $content, $post_id);

                    if ($update_stmt->execute()) {
                        echo "Attachment added for <a href=\"$discussion_url\" target=\"_blank\">$discussion_url</a><br>";
                    }
                }
            }
        }
    }
}

// Close the database connection
$conn->close();
?>

[chris-pdg]

I think I am just going to run the SQL statement across the entire posts database so that it catches everything. Its dirty but it will work for the most part.

@MrCaspan How are you SQL converting the attachments? I looked at this 6-8 months ago so it is not fresh on my mind but I had considered converting the attachments to embedded images. Does this make sense?

[MrCaspan]

This is only for the embedded images that i am doing a SQL to update the absolute path to the relative, it would need to be done anyways because the assets directory is different then the old Vanilla3 location.

It does make sense to convert photo attachments to embeds (IE just append the Embed code at the end of the post they are associated with) but not all attachments are images some are Zips, PDFs and hand history like TXT files (It's a Poker Forum)

[MrCaspan]

@chris-pdg Just FYI I had to convert all attachments to embedded items. This is the way FoF handles attachments now! I added the code above on how to do it!

[chris-pdg]

Thanks @MrCaspan for posting your solution for converting attachments! This has been the final sticking point for my forum conversion