Ferdium Item Order Manipulation - https://github.com/ferdium/ferdium-app/issues/146

1_ferdium-ws-order-sqlite.nu

#!/usr/bin/env nu
############################################################################
# Copyright © 2025  Akito <[email protected]>                                  #
#                                                                          #
# This program is free software: you can redistribute it and/or modify     #
# it under the terms of the GNU Affero General Public License as           #
# published by the Free Software Foundation, either version 3 of the       #
# License, or (at your option) any later version.                          #
#                                                                          #
# This program is distributed in the hope that it will be useful,          #
# but WITHOUT ANY WARRANTY; without even the implied warranty of           #
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the            #
# GNU Affero General Public License for more details.                      #
#                                                                          #
# You should have received a copy of the GNU Affero General Public License #
# along with this program.  If not, see <https://www.gnu.org/licenses/>.   #
############################################################################

## Ferdium Workspace & Service Order Manipulation
## https://gist.github.com/theAkito/daf71b45e02ecde502d55af4df0a6721

# Hard-Coded UUID
# https://github.com/ferdium/ferdium-app/blob/edfa442c9df5f98df182f6e3f957f7662bff9b6f/src/config.ts#L37
const uuid_placeholder = "0a0aa000-0a0a-49a0-a000-a0a0a0a0a0a0"

def set_ws_order [
  path_ferdium: path
  execute: bool # Actually apply the new order to the local Ferdium SQLite database, instead of only dry running to display changes.
] {
  # Set SQLite database file paths
  let path_sqlite = ($path_ferdium | path join server.sqlite)
  let path_sqlite_bak_dir = ($path_ferdium | path join "backup_sqlite")
  let path_sqlite_bak = ($path_sqlite_bak_dir | path join $"server.sqlite_(date now | format date '%Y%m%d_%H%M%S')")

  # Get specified Workspaces to modify
  let workspaces = (open ferdium_tool_input/order.yaml | get workspaces)

  ## Sanity Checks
  # Check if Workspace Order is unique
  if ($workspaces | get order | uniq --repeated | is-not-empty) {
    error make --unspanned { msg: $"Provided YAML configuration file contains duplicate order ordinals for its defined workspaces. For example, your first workspace is of order 1 and the last one is of order 1, as well. Make sure, that each workspace gets a unique ordinal assigned." }
  }

  # Check if Service Order is unique
  if ($workspaces | get services | filter { is-not-empty } | each { |r| $r | get order | uniq --repeated | is-not-empty } | any { $in == true }) {
    error make --unspanned { msg: $"Provided YAML configuration file contains duplicate order ordinals for its defined services. For example, your first service of one workspace is of order 1 and the last one is of order 1, as well. Make sure, that each workspace's service gets a unique \(unique within that workspace, not across all workspaces\) ordinal assigned." }
  }

  # Clear Nushell's in-memory SQLite database
  stor reset

  # Import Ferdium SQLite database
  stor import --file-name $path_sqlite

  # Open SQLite database
  let sqlite_live = (stor open)

  # Replace services list with list featuring desired order
  let workspaces_live = ($sqlite_live | query db $"SELECT * FROM workspaces")
  let service_id_to_name = ($sqlite_live | query db $"SELECT * FROM services" | select serviceId name)
  let name_to_workspace_live = ($workspaces_live | each { |workspace|
    {
      name: $workspace.name
      workspace: ($workspace | select id workspaceId order services)
    }
  })

  stor delete --table-name workspaces --where-clause "id != 0"

  let result = (
    $workspaces_live
    | each { |workspace_live|
      {
        id: (
          if ($workspaces | any { |workspace| $workspace.name == $workspace_live.name }) {
            (($workspaces | where name == $workspace_live.name | first | get order) + 1)
          } else {
            error make --unspanned { msg: $"1. Provided YAML configuration file does not contain workspace named '($workspace_live.name)'. Add the configuration for this workspace, before re-running this script." }
          }
        )
        workspaceId: $workspace_live.workspaceId
        `'name'`: $workspace_live.name
        `'order'`: $workspace_live.order
        created_at: $workspace_live.created_at
        updated_at: $workspace_live.updated_at
        services: (do {
          let workspace = (if ($workspaces | any { |workspace| $workspace.name == $workspace_live.name }) {
              ($workspaces | where name == $workspace_live.name | first)
            } else {
              error make --unspanned { msg: $"2. Provided YAML configuration file does not contain workspace named '($workspace_live.name)'. Add the configuration for this workspace, before re-running this script." }
            })
          if (($workspace.services | is-empty) or ($workspace_live.services | is-empty)) {
            $workspace_live.services | from json
          } else {
            let svc_conf = $workspace.services
            let svc_live = ($workspace_live.services | from json)
            let svc_live_pos = ($svc_live | enumerate)
            let svc_live_pos_clean = ($svc_live_pos | where $it.item != $uuid_placeholder)
            let svc_1 = ($svc_live_pos_clean | first)
            let svc_1_idx = ($svc_1 | get index)
            $svc_live_pos_clean
            | reduce --fold $svc_live { |svc, acc|
              let svc_name = ($service_id_to_name | where serviceId =~ $svc.item | get name | first)
              let svc_conf_spec = ($svc_conf | where name == $svc_name)
              # Sanity check
              if ($svc_conf_spec | is-empty) {
                # Workaround for `error make` not being fatal, but being passed on inside this `reduce`.
                # https://github.com/nushell/nushell/discussions/15866
                print $"Provided YAML configuration file is missing the service with the id '($svc.item)' and the name '($svc_name)' inside workspace '($workspace_live.name)'. Make sure, to add the service to the list of services for that specific workspace. If you do not wish to handle the order for this workspace at all, assign an empty object to the `services` key of that workspace. Example: `services: {}`."
                exit 1
              }
              $acc
              | update $svc.index $uuid_placeholder # Erase the previous spot to mitigate duplicate service entries inside a workspace.
              | update ($svc_1_idx + (($svc_conf_spec | get order | first) - 1)) $svc.item
            }
          }
        } | to json --raw)
      }
    }
  ) | stor insert --table-name workspaces

  if ($execute) {
    # Prepare Backup of Ferdium SQLite database
    try {
      mkdir $path_sqlite_bak_dir
    } catch {
      error make --unspanned { msg: $"Unable to create SQLite backup directory ($path_sqlite_bak_dir). Abort." }
    }

    # Back up Ferdium SQLite database
    try {
      mv $path_sqlite $path_sqlite_bak
    } catch {
      error make --unspanned { msg: "Unable to back up local Ferdium SQLite database. Abort.\nThis most likely happens, due to the Ferdium program still running.\nClose the app and make sure no Ferdium process is running.\nAfter that, re-run this script." }
    }

    # Write modified state to Ferdium SQLite database
    try {
      stor export --file-name $path_sqlite
    } catch {
      error make --unspanned { msg: "Unable to export modified in-memory SQLite database onto local Ferdium SQLite database. Abort.\nThis most likely happens, due to the Ferdium program still running.\nClose the app and make sure no Ferdium process is running.\nAfter that, re-run this script." }
    }
  }

  $result
}

# Switch Ferdium Workspace & Service Order
# Make sure no Ferdium process is running, before running this script!
# Reads `./ferdium_tool_input/order.yaml`. which content looks like this.
#
# workspaces:
#   - name: Personal
#     order: 1
#     services:
#       - name: Github
#         order: 1
#       - name: Gitlab
#         order: 2
#       - name: Gitea
#         order: 3
#       - name: Mattermost
#         order: 4
#       - name: ChatGPT
#         order: 5
#   - name: Project
#     order: 2
#     services:
#       - name: Affine
#         order: 1
#       - name: Anytype
#         order: 2
#       - name: YouTrack
#         order: 3
#   - name: Work
#     order: 3
#     services:
#       - name: Gitlab
#         order: 1
#       - name: Microshit Teams
#         order: 2
#       - name: Slack
#         order: 3
#       - name: Jira
#         order: 4
#       - name: Microshit Copilot
#         order: 5
#   - name: Janitorial
#     order: 4
#     services: {} # Order is not handled for this workspace.
#
# Use the above sample and fill it with your workspaces and their services.
# If some ordinals are set the wrong way, the script will abort execution early.
# Run the script to see the modified result.
# Once confident, that everything is as it should be, re-execute the script with the `--execute` flag, to actually modify the Ferdium database.
# The script backs up the database before each modification, when running with the `--execute` flag.
def main [
  path?: path # Provide custom path to Ferdium configuration directory. Linux Default: ~/.config/ferdium/ Windows Default: C:\Users\<username>\AppData\Roaming\Ferdium ($env.APPDATA\Ferdium)
  --execute   # Actually apply the new order to the local Ferdium SQLite database, instead of only dry running to display changes.
] {
  let path_ferdium_default = if (((sys host).name == "Windows") and ($path | is-empty)) {
    $env.APPDATA | path join Ferdium
  } else if (((sys host).name | str ends-with "Linux") and ($path | is-empty)) {
    $env | get --ignore-errors XDG_CONFIG_HOME | default "~/.config/" | path join ferdium
  } else {
    $path
  }
  let path_ferdium = ($path | default $path_ferdium_default)
  set_ws_order $path_ferdium $execute
  | get workspaces
  | select id workspaceId name services
  | update cells --columns [ services ] { from json }
}

2_ferdium-workspace-order-sqlite.nu

#!/usr/bin/env nu
############################################################################
# Copyright © 2025  Akito <[email protected]>                                  #
#                                                                          #
# This program is free software: you can redistribute it and/or modify     #
# it under the terms of the GNU Affero General Public License as           #
# published by the Free Software Foundation, either version 3 of the       #
# License, or (at your option) any later version.                          #
#                                                                          #
# This program is distributed in the hope that it will be useful,          #
# but WITHOUT ANY WARRANTY; without even the implied warranty of           #
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the            #
# GNU Affero General Public License for more details.                      #
#                                                                          #
# You should have received a copy of the GNU Affero General Public License #
# along with this program.  If not, see <https://www.gnu.org/licenses/>.   #
############################################################################

## Ferdium Workspace Order Manipulation
## https://github.com/ferdium/ferdium-app/issues/146

def set_workspace_order [
  path_ferdium: path
] {
  # Set SQLite database file paths
  let path_sqlite = ($path_ferdium | path join server.sqlite)
  let path_sqlite_bak = ($path_ferdium | path join $"server.sqlite_(date now | format date '%Y%m%d_%H%M%S')")

  # Get specified Workspace to modify
  let workspaces = (open input/workspace.yaml | get workspaces)

  # Clear Nushell's in-memory SQLite database
  stor reset

  # Import Ferdium SQLite database
  stor import --file-name $path_sqlite

  # Open SQLite database
  let sqlite_live = (stor open)

  # Replace workspaces list with list featuring desired order
  let workspaces_live = ($sqlite_live | query db $"SELECT * FROM workspaces")
  stor delete --table-name workspaces --where-clause "id != 0"
  (
    $workspaces_live
    | each { |workspace_live|
      {
        id: (($workspaces | get $workspace_live.name) + 1)
        workspaceId: $workspace_live.workspaceId
        `'name'`: $workspace_live.name
        `'order'`: $workspace_live.order
        created_at: $workspace_live.created_at
        updated_at: $workspace_live.updated_at
        services: $workspace_live.services
      }
    }
  ) | stor insert --table-name workspaces

  # Back up Ferdium SQLite database
  mv $path_sqlite $path_sqlite_bak

  # Write modified state to Ferdium SQLite database
  stor export --file-name $path_sqlite
}

# Switch Ferdium Workspace Order
# Make sure no Ferdium process is running, before running this script!
# Reads `./input/workspace.yaml`. which content looks like this.
#
# workspaces:
#   Personal: 1
#   Work: 2
#   Project: 3
#   Janitorial: 4
#
# Where the map's key is the exact workspace's display name, as shown in the Ferdium client.
# Where the map's value is the desired list position.
def main [
  path?: path
] {
  let parh_ferdium_default = if (((sys host).name == "Windows") and ($path | is-empty)) {
    $env.APPDATA | path join Ferdium
  } else if (((sys host).name | str ends-with "Linux") and ($path | is-empty)) {
    $env | get --ignore-errors XDG_CONFIG_HOME | default "~/.config/" | path join ferdium
  } else {
    $path
  }
  let path_ferdium = ($path | default $parh_ferdium_default)
  (
    set_workspace_order $path_ferdium
    | get workspaces
  )
}

3_ferdium-service-order-sqlite.nu

#!/usr/bin/env nu
############################################################################
# Copyright © 2025  Akito <[email protected]>                                  #
#                                                                          #
# This program is free software: you can redistribute it and/or modify     #
# it under the terms of the GNU Affero General Public License as           #
# published by the Free Software Foundation, either version 3 of the       #
# License, or (at your option) any later version.                          #
#                                                                          #
# This program is distributed in the hope that it will be useful,          #
# but WITHOUT ANY WARRANTY; without even the implied warranty of           #
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the            #
# GNU Affero General Public License for more details.                      #
#                                                                          #
# You should have received a copy of the GNU Affero General Public License #
# along with this program.  If not, see <https://www.gnu.org/licenses/>.   #
############################################################################

## Proof Of Concept for Manual Service Order Manipulation
## https://github.com/ferdium/ferdium-app/issues/146

# Switch first service found with second service found
def demo_switch_service [
  path_ferdium: path
  workspace_name: string
] {
  let path_sqlite = ($path_ferdium | path join server.sqlite)
  let path_sqlite_bak = ($path_ferdium | path join $"server.sqlite_(date now | format date '%Y%m%d_%H%M%S')")

  # Clear Nushell's in-memory SQLite database
  stor reset

  # Import Ferdium SQLite database
  stor import --file-name $path_sqlite

  # Get specified Workspace to modify
  let workspace = (stor open | query db $"SELECT * FROM workspaces WHERE name = '($workspace_name)'" | first)

  # Get the first two service indices
  let svc_o = ($workspace.services | from json);
  let svc_e = ($svc_o | enumerate);
  # Hard-Coded UUID
  # https://github.com/ferdium/ferdium-app/blob/edfa442c9df5f98df182f6e3f957f7662bff9b6f/src/config.ts#L37
  let svc_u = ($svc_e | where $it.item != "0a0aa000-0a0a-49a0-a000-a0a0a0a0a0a0");
  let svc_1 = ($svc_u | first);
  let svc_2 = ($svc_u | first 2 | last);
  let svc_1_idx = ($svc_1 | get index);
  let svc_2_idx = ($svc_2 | get index);

  # Update Nushell's in-memory SQLite database with new Service Order
  stor update --table-name workspaces --where-clause $"name = '($workspace_name)'" --update-record {
    id: $workspace.id
    workspaceId: $workspace.workspaceId
    `'name'`: $workspace.name
    `'order'`: $workspace.order
    created_at: $workspace.created_at
    updated_at: $workspace.updated_at
    services: ( # Switch svc_1 with svc_2 in the original services list
      $svc_o
      | update $svc_1_idx $svc_2.item
      | update $svc_2_idx $svc_1.item
      | to json --raw
    )
  }

  # Back up Ferdium SQLite database
  mv $path_sqlite $path_sqlite_bak

  # Write modified state to Ferdium SQLite database
  stor export --file-name $path_sqlite
}

# Proof of Concept for switching Service Order in Ferdium Workspace
# Make sure no Ferdium process is running, before running this script!
def main [
  name: string
  path?: path
] {
  let parh_ferdium_default = if (((sys host).name == "Windows") and ($path | is-empty)) {
    $env.APPDATA | path join Ferdium
  } else if (((sys host).name | str ends-with "Linux") and ($path | is-empty)) {
    $env | get --ignore-errors XDG_CONFIG_HOME | default "~/.config/" | path join ferdium
  } else {
    $path
  }
  let path_ferdium = ($path | default $parh_ferdium_default)
  (
    demo_switch_service $path_ferdium $name
    | get workspaces
    | where name == $name
  )
}