Import Typecho articles into xLog

How to import#

xLog can directly import markdown files and supports front matter. Therefore, you only need to export the articles from the database and set them in hexo format.

Import process#

You can use PHP's Pdo to export the "typecho_contents" table from the database as a file. The code is as follows, and you can adjust it according to your actual situation.

<?php

// Connect to the database
$dsn = 'mysql:host=localhost;dbname=blog;charset=utf8';
$username = 'root';
$password = '123456';

$conn = new PDO($dsn, $username, $password);

// Set error handling
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Execute the query
$sql = 'SELECT * FROM `typecho_contents` WHERE `type` = "post"';
$stmt = $conn->prepare($sql);
$stmt->execute();

// Iterate through the result set
$results = $stmt->fetchAll();
foreach ($results as $result) {
    // Output data
    echo $result['title'] . "\n\n";

    // Get the relationship between tags and articles
    $s_sql = 'SELECT * FROM `typecho_relationships` WHERE `cid`=' . $result['cid'];
    $stmt2 = $conn->prepare($s_sql);
    $stmt2->execute();
    $relationships = [];
    foreach($stmt2->fetchAll() as $s){
        $relationships[] = $s['mid'];
    }

    // Get tag names
    $t_sql = 'SELECT `name` FROM `typecho_metas` WHERE `mid` in (\''. implode("','", $relationships) .'\') and `type`=\'\'';
    $stmt3 = $conn->prepare($t_sql);
    $stmt3->execute();
    $tags = [];
    foreach($stmt3->fetchAll() as $t){
        $tags[] = $t['name'];
    }

    // Generate Markdown file
    $str = '---
tags:
 - '. implode("\n - ", $tags) .'
type: note
title: "' . $result['title'] . '"
date: ' . date('Y-m-d H:i:s', $result['created']) . '
updated: ' . date('Y-m-d H:i:s', $result['modified']) . '
---
';
    file_put_contents(__DIR__ . '/blog/' . str_replace('/', '_', $result['title']) . '.md', str_replace('<!--markdown-->', $str, $result['text']));
}

// Close the connection
$conn = null;

The files will be stored in the "blog" folder in the current directory (if it doesn't exist, you need to create it yourself because I didn't include "mkdir").

Some issues#

Currently, there are the following issues that need to be resolved:

The exported markdown files do not have functioning tags and last modified time.
After importing xLog, it is not possible to modify tags (probably a bug in xLog).
Images cannot be automatically uploaded to IPFS.

The last point is the most frustrating for me, so I deleted the uploaded articles and will migrate them again once I find a solution.