Fix GNUsocial When Instance Goes HTTP --> HTTPS

    #!/usr/bin/env tclsh
    package require Tcl 8.6
    package require tdbc::mysql

    # Set up db connectivity
    set dbname "your_database_name"
    set dbuser "your_database_user"
    set dbpwd  "your_dbuser_password"
    set dbhost "127.0.0.1"
    set remotehost "the_other_instance"

    tdbc::mysql::connection create db -user $dbuser -passwd $dbpwd -host $dbhost -database $dbname


    ## Table: profile
    #puts "\nConverting $remotehost URLs in profile table to HTTPS."
    #db foreach row "SELECT profileurl, nickname FROM profile WHERE profileurl LIKE '%$remotehost%'" {
    #   set url [dict get $row profileurl]
    #   set nick [dict get $row nickname]
    #   set temp [string index $url 4]
    #   if {$temp == ":""} {
    #      puts URL for $nick (\"$url\") is HTTP. Changing to HTTPS"
    #      # change it
    #      set newurl [string replace $url 4 4 s[string index $url 4]]
    #      set stmt [$db prepare "UPDATE `profile` set profileurl $newurl where nickname=$nick"]
    #      set res [$stmt execute]
    #      stmt close
    #   } else {
    #      puts "URL for $nick (\"$url\") is already HTTPS"
    #   }
    #}
    ## Commented because fl0wn points out that changing profile table can cause breakage.

    ## Table: ostatus_profile
    db foreach row "SELECT uri, profile_id FROM ostatus_profile WHERE uri LIKE '%$remotehost%'" {
       set url [dict get $row uri]
       set id [dict get $row profile_id]
       set temp [string index $uri 4]
       if {$temp == ":""} {
          puts "URL for $id (\"$url\") is HTTP. Changing to HTTPS"
          # change it
          set newurl [string replace $url 4 4 s[string index $url 4]]
          set stmt [$db prepare "UPDATE `ostatus_profile` set uri $newurl where profile_id=$id"]
          set res [$stmt execute]
          stmt close
       } else {
          puts "URL for $id (\"$url\") is already HTTPS"
       }
    }

    ## Table: feedsub
    db foreach row "SELECT id, uri, huburi FROM feedsub WHERE uri LIKE '%$remotehost%'" {
       set id [dict get $row id]
       set url [dict get $row uri]
       set hub [dict get $row huburi]
       set temp [string index $uri 4]
       if {$temp == ":""} {
          puts "Profile URL for $id (\"$uri\") is HTTP. Changing to HTTPS"
          # change it
          set newurl [string replace $uri 4 4 s[string index $uri 4]]
          set stmt [$db prepare "UPDATE `feedsub` set uri $newurl where id=$id"]
          set res [$stmt execute]
          stmt close
       } else {
          puts "URL for $id (\"$url\") is already HTTPS"
       }
       set temp [string index $hub 4]
       if {$temp == ":""} {
          puts "Hub URL for $id (\"$hub\") is HTTP. Changing to HTTPS"
          # change it
          set newurl [string replace $hub 4 4 s[string index $hub 4]]
          set stmt [$db prepare "UPDATE `feedsub` set huburi $newurl where id=$id"]
          set res [$stmt execute]
          stmt close
       } else {
          puts "URL for $id (\"$url\") is already HTTPS"
       }
    }

    ### Sources
    ## String insertion: http://wiki.tcl.tk/44pagetoc706ab8bb
    ## Return the nth character of a string: http://wiki.tcl.tk/10165
    ## tdbc and tdbc::mysql: http://wiki.tcl.tk/20343
    ## GNU Social tables which may need tweaking: https://git.gnu.io/gnu/gnu-social/issues/208 and https://nu.federati.net/conversation/51983 and https://sealion.club/notice/2344583 and http://whird.jpope.org/statusnet-subscription-hacking/

    ### Comment
    ## Purpose: convert the db version of a GS instance's URLs from HTTP to HTTPS