Help to convert or export database to import in new setup.

[rmayergfx]

Starting with shiori i used a older image from docker search radhifadlillah/shiori as this shows 21 installations and seems nice to me. Was a big mistake, after some issues i found, that this is since 3 years not updated and now i have too many bookmarks inside this old db.

Did a fresh setup with ghcr.io/go-shiori/shiori:latest. but i need to convert or export the old data to get it working with this version.

Some documentation/help would be great.
Tried to copy archive thumb and shiori.db into the new setup and testet the rights, but it always shows

Error running migration: Dirty database version 2. Fix and force version.

Problem is, inside this old Database are websites which are now no longer avail, as they are changed/shutdown/deleted.

[fmartingr]

Hey @rmayergfx.

First of all, when you upgrade I recommend going into a fixed version (right now latest is v1.5.4). That would be chcr.io/go-shiori/shiori:v1.5.4.

You should be able to upgrade from the old version to the new one, the problem you have is that in the meantime we added a new migration system and that's the problem you are experiencing.

Can you give me the output of:

sqlite3 shiori.db ".tables"
sqlite3 shiori.db ".schema bookmark_content"
sqlite3 shiori.db ".schema bookmark"
sqlite3 shiori.db "SELECT * FROM schema_migrations"

The good thing is that you have a backup so you haven't lost any data and even in the remote case we could not upgrade your database to v1.5.4, we could do it manually using a script.

The only manual interaction we will need to do once your Shiori is running is converting the content from FTS4 to FTS5, which is done by a separate utility. Instructions here.

[rmayergfx]

@fmartingr
thx to the link with the instructions. But i don´t know how to use this. I am stuck in a docker container and i am missing inside the instructions how and where the commands should be used. So for a beginner it´s almost impossible to convert this with command.

[fmartingr]

Let's start from the beginning. If you have the shiori.db file (since you copied it from one place to another I'm assuming you can copy it to your computer), get the output of this:

sqlite3 shiori.db ".tables"
sqlite3 shiori.db ".schema bookmark_content"
sqlite3 shiori.db ".schema bookmark"
sqlite3 shiori.db "SELECT * FROM schema_migrations"

So I can check in which state your database is.

sqlite3 is the database engine used underneath, installation for all systems should be in their official website. We can worry about the conversion later.

[rmayergfx]

Her we go:
`sqlite3 shiori.db ".tables"
account bookmark_content_segdir
bookmark bookmark_content_segments
bookmark_content bookmark_content_stat
bookmark_content_content bookmark_tag
bookmark_content_docsize tag

sqlite3 shiori.db ".schema bookmark_content"
CREATE VIRTUAL TABLE bookmark_content USING fts4(title, content, html)
/* bookmark_content(title,content,html) */;

sqlite3 shiori.db ".schema bookmark"
CREATE TABLE bookmark(
id INTEGER NOT NULL,
url TEXT NOT NULL,
title TEXT NOT NULL,
excerpt TEXT NOT NULL DEFAULT "",
author TEXT NOT NULL DEFAULT "",
public INTEGER NOT NULL DEFAULT 0,
modified TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT bookmark_PK PRIMARY KEY(id),
CONSTRAINT bookmark_url_UNIQUE UNIQUE(url));

sqlite3 shiori.db "SELECT * FROM schema_migrations"
Error: in prepare, no such table: schema_migrations

`

[fmartingr]

Did you know which version you were running before the upgrade? I will try to reproduce the same behavior myself.

[rmayergfx]

it was this docker:
https://hub.docker.com/r/radhifadlillah/shiori

[fmartingr]

Hey @rmayergfx, an user came to Matrix with the same symptoms and yourself and the following steps worked for its problem (which seemed the same as yours):

Changes should be non-destructive, but remember to always backup your database before doing anything.

1. Get shiori.db
2. Run the shiori-utils migration script. (you already did this?)
3. Execute the following query: UPDATE schema_migrations SET version = 1, dirty = 0.
4. Run the new server.

Migrations should apply correctly after those changes.

You can, alternatively, re-run the shiori-utils script, I introduced a change to automatically leave the database in a usable state.

[rmayergfx]

Sorry,
i don´t know how to run the shiori-utils on synology and query. I am lost without a tutorial. shiori.db is already a backup, so nothing can happen.

[fmartingr]

I don't know about the specifics of running all this under a synology, but the best that comes to mind is that you download the shiori.db to your computer and run the migration script and the SQL query there.

I need to check if it's feasible for me to include a migration in the code that perform the migration on behalf of the user. #702

[rmayergfx]

Download to PC will not be the problem, but how to convert this on Windows 10, i have no experience with sql, total novice.
To do this i need a step to step tutorial or video that shows how to do this.

[fmartingr]

I have added the help-wanted tag since I have no experience with Windows either, and the conversor would need to be compiled.

Putting the migration code directly in shiori would be too much effort so we're not going to do it, but maybe have a simple webservice so people can convert their databases online...? I don't know. Seems very risky playing with other people's databases. I need to sit and think on this.