𝖄𝕺🌎𝕿𝕽𝕺¥

Introduction#

Due to the limited bandwidth of the website, Alibaba Cloud's OSS Accelerate was used for storage. However, due to certificate issues, the resources were bound to Alibaba Cloud's default domain name oss-accelerate.aliyuncs.com. Later, it became easier to apply for an SSL certificate, so I wanted to update the website's file storage URL, for example, to file.yotroy.cool. However, it is known that the attachment addresses in the halo backend cannot be modified. Therefore, I tried to solve this problem by combining it with some basic knowledge of Java that I have.

Using the H2 Console#

Refer to the official Halo documentation Detailed Introduction and Reference to Halo Configuration File
Open the Halo configuration file application.yaml

  h2:
    console:
      settings:
        web-allow-others: true
      path: /h2-console
      enabled: true

Set web-allow-others and enabled to true, and restart the service.
Add /h2-console to the end of the URL to enter the console, and enter the username and password from application.yaml.

Enter SELECT * FROM ATTACHMENTS in the SQL statement and run it.

You will get all the attachment database information of the website.

Approach#

Use the split method in Java to split the string and then print its pairs.
Here, we need the THUMB_PATH, PATH, and ID columns.
We directly copy the table that needs to be changed to Excel and then extract these three items to Word, pasting only the text to get the text with automatic line breaks. Be careful not to confuse the order, each line should correspond correctly. Then use the replace function to replace line breaks with other characters, such as ;.
Tips: You can use the special character ^p to represent line breaks. This ensures that each data is separated by ;.
Then run the following Java code to update all attachments. Remember to backup first to prevent data corruption.

Main Java Program#

Database.java#

public class Database {
	public static void main(String[] args) {
		// newTPStr: Replaced THUMB_PATH data
		// newPStr: Replaced PATH data
		String newTPStr = "TPurl1;TPurl2";
		String newPStr = "Purl1;Purl2";
		String idStr = "1;2";
		String newTPArr[] = newTPStr.split(";");
		String newPArr[] = newPStr.split(";");
		String idArr[] = idStr.split(";");
		if (newPArr.length == idArr.length && newTPArr.length == idArr.length) {
			// e.g. UPDATE TEST SET NAME='Hi' WHERE ID=1;
			for (int i = 0; i < idArr.length; i++) {
				System.out.println("UPDATE ATTACHMENTS SET THUMB_PATH ='" + newTPArr[i] +"' WHERE ID=" +idArr[i] + ";");
			}
			for (int i = 0; i < idArr.length; i++) {
				System.out.println("UPDATE ATTACHMENTS SET PATH ='" + newPArr[i] +"' WHERE ID=" +idArr[i] + ";");
			}
		}
		else {
			System.out.println("length error");
		}
	}
}

Output#

Review#

There is still room for improvement in handling strings. If it is possible to skip the process of copying from Excel to Word and then replacing characters, it would be more efficient. I once tried to directly call the Excel data, but it resulted in errors due to Excel versions and other issues, so further efforts are still needed.

Applying the New URL#

Since this operation only updates the data in the attachment database and does not update its application (such as the reference to this attachment in the article is still the original URL), you can directly modify it through the halo backend panel. You can also modify it through halo data export, replace the modified data, and then import the data (which is too cumbersome). Alternatively, you can directly modify it in the H2 Console.

H2 Syntax Reference#