SQLite support

[skttl]

Which version of Skybrud Redirects are you using? (Please write the exact version, example: 4.0.8)

16.0.0-beta001

Which Umbraco version are you using? (Please write the exact version, example: 10.1.0)

16.0.0

Question

Can we improve SQLite support?

In v14 a migration was added, that alters some columns. This is not supported in SQLite.

Can we change it so it actually drops the table and recreates it? I noticed you save a backup of all redirects beforehand, so they could theoretically be imported straight back in afterwards?

Happy to do a PR :)

[abjerner]

Hi @skttl

To be honest it's a bit of pain that SQLite doesn't support this. This also changes with the principles that we've used for creating and managing tables in Umbraco, with having an initial schema, and then using migrations for any changes after that. Although after supporting this package for almost a decade, the migrations have also become a bit messy and even today they have some remnants from the old Umbraco 7 package 😮

I haven't played much around with SQLite, so not sure what's the best approach here. I guess the following migration could be used (and potentially replace all the existing migrations):

using System;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Hosting;
using Skybrud.Umbraco.Redirects.Extensions;
using Skybrud.Umbraco.Redirects.Models.Dtos;
using Skybrud.Umbraco.Redirects.Models.Schemas;
using Umbraco.Cms.Infrastructure.Migrations;

#pragma warning disable 1591

namespace Skybrud.Umbraco.Redirects.Migrations;

public class SuperFancyMigration : AsyncMigrationBase {

    private readonly IWebHostEnvironment _webHostEnvironment;

    public SuperFancyMigration(IMigrationContext context, IWebHostEnvironment webHostEnvironment) : base(context) {
        _webHostEnvironment = webHostEnvironment;
    }

    protected override Task MigrateAsync() {

        string tableName = RedirectSchema.TableName;
        string backupTableName = $"{RedirectSchema.TableName}_backup{DateTime.UtcNow:yyyyMMddHHmmss}";

        // If the table doesn't yet exist, we can create it from scratch 😎
        if (!Context.TableExists<RedirectSchemaNew>()) {
            Create.Table<RedirectSchemaNew>().Do();
            return Task.CompletedTask;
        }

        // TODO: detect whether we actually need to modify the table

        // Save a backup of all redirects
        RedirectsUtils.RedirectsBackupResult backup = RedirectsUtils.SaveBackup(_webHostEnvironment, Database);

        // Rename the existing table
        Rename.Table(tableName).To(backupTableName).Do();

        // Re-create the table with the updated schema
        Create.Table<RedirectSchemaNew>().Do();

        // Insert the existing redirects in the new table
        // TODO: Can we do something like 'INSERT INTO NewTable SELECT * FROM BackupTable;' instead???
        foreach (RedirectDto redirect in backup.Dtos) {
            Context.Database.Insert(redirect);
        }

        // Drop the backup table
        // TODO: introduce setting for whether the backup table should be deleted? Or maybe deleting is totally fine since we also have the backup on disk
        if (false) Delete.Table(backupTableName).Do();

        // YAY
        return Task.CompletedTask;

    }

}

Currently I don't see a way to describe a database table using Umbraco/NPoco. I would have used something like that for determining whether the current database table differs from the newest schema.

But maybe this isn't that important, as I can set up so that the migrations the migrations plan goes from:

    plan.From(string.Empty)
        .To<CreateTableMigration>("2.0.0-alpha001")
        .To<RemoveIsRegexColumnMigration>("2.0.0-alpha002")
        .To<DummyMigration>("2.0.0-alpha008")
        .To<FixRootKeyValue>("2.0.5")
        .To<DummyMigration>("2.1.1")
        .To<AddDestinationColumnsMigration>("3.0.0-alpha008")
        .To<RemoveRootIdColumnMigration>("4.0.4")
        .To<AddDestinationCultureColumnMigration>("4.0.9")
        .To<DummyMigration>("8934735b")
        .To<AddDestinationNameColumnMigration>("c0d4ddeb")
        .To<NullableColumnsMigration>("4b5fabfc");

to:

        plan.From(string.Empty)
            .To<DummyMigration>("2.0.0-alpha001")
            .To<DummyMigration>("2.0.0-alpha002")
            .To<DummyMigration>("2.0.0-alpha008")
            .To<DummyMigration>("2.0.5")
            .To<DummyMigration>("2.1.1")
            .To<DummyMigration>("3.0.0-alpha008")
            .To<DummyMigration>("4.0.4")
            .To<DummyMigration>("4.0.9")
            .To<DummyMigration>("8934735b")
            .To<DummyMigration>("c0d4ddeb")
            .To<DummyMigration>("4b5fabfc")
            .To<SuperFancyMigration>("b456d84a");

This way, the previous migrations are kept so people can still upgrade, but only the last migration will actually do anything (but only if it hasn't already run).

As we should ideally use the current SQL syntax instance instead of writing the SQL on our own. Therefore I don't see a way to do something like 'INSERT INTO NewTable SELECT * FROM BackupTable;', so my example inserts one redirect at a time, which could be slow with many redirects.

I can see that Umbraco/NPoco has InsertBatch and InsertBulk methods as well (not sure between the difference between the two), so maybe a single call to either of those will be faster than calling Insert for each redirect 🤷‍♂

[abjerner]

A looked a bit more into this after my last comment, and hit a few issues - partially because Umbraco has different abstractions for the database (which is usually a good thing), but makes it harder to do something specific for SQLite without writing more code on my own to handle this. Here's some of the issue I've hit:

• Renaming a table with SQLite won't rename the primary key constraint
  This means I can't rename the table, create a new table named SkybrudRedirects and then copy over the data. So I'll likely have do move everything to a temp database, then delete the original SkybrudRedirects table, create a new one, and then copy over the data from the temp table. Or save the JSON backup on disk, delete the existing table, create a new one, and then import the redirects from the JSON file.

• New numeric IDs
  The primary key of the SkybrudRedirects table is currently a numeric ID. Copying the data from one table to another will cause new numeric IDs to generated. Maybe I should just skip the numeric IDs in general. I know SQL server has something like Set Identity_Insert [SkybrudRedirects] On;. I haven't looked into yet whether SQLite supports this as well - but then I also have to call the database directly, instead of relying on Umbraco's abstractions.

@skttl any thoughts on this? Have you handled something like this elsewhere?

[abjerner]

Didn't mean to close this 🤦‍♂️

[skttl]

Haven't done this before sorry.

I think a sensible choice would be to move everything to a temporary table, then delete the original and recreate. After all, you only have to do it once :)

I'm not sure if the numeric IDs are actually used for anything external to the package, or if they are purely internal? If they are internal only, then I don't see a problem in them changing.

[jesperbrasmussen]

Hi @abjerner

Thank you for your work on this package.
Do you know of any workaround for this issue, to be able to use it on v16 with SQLite?.
In our case it's a new project on v16, so we don't need to migrate any existing data. We just need the final version of the table after all the migrations has run.

Did you find a workaround @skttl ?

Thanks,
Jesper Bo Rasmussen

[abjerner]

@jesperbrasmussen I haven't had the time to look into this since my comment in July.

I think there are still some challenges that I need to find a solution for, and then also the time to implement it.

Historically since HQ introduced migrations for Umbraco 7, the recommended approach has been to create an initial schema, and then handle any subsequent changes to the table via migrations. This for one means that you can update Umbraco from v7 all the way up to v16, and Umbraco will run all migrations for Skybrud Redirects to apply table changes from the past decade (+/-).

I think that, because of SQLite, I may have to strive away from this either partially or fully. E.g. so if you install a fresh Umbraco 16 solution, Skybrud Redirects will create a new database table like it should look for Umbraco 16 - instead of creating the table as it initially looked for Umbraco7 and then running a decade's worth of migrations.

[jesperbrasmussen]

@abjerner
Yes it looks like there is no way that the migrations going from v7 and all the way to v16 will work with SQLite.
Would it be a solution for now to make it possible to choose a clean install without the migrations?