Import Typecho articles into xLog.

How to import#

xLog can directly import markdown files and supports front matter, so you just 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 the 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 the 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 write "mkdir").

Some issues#

There are currently the following issues that need to be resolved.

The exported MD file does not apply the "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 once I find a solution.