A partial implementation of dbmate in YSH

dbman

This is a partial implementation of dbmate [1] in YSH [0].

This is more of a example of what YSH looks like for larger and more realistic
programs. It's not very well tested, lacks many features, could use some
cleaning up, and probably has a few bugs. But I think it's a good proof of
concept and is functional enough to be worth it's salt.

> I want to run it

You will want to make sure you have sqlite3 and YSH [0] installed. Then run the
program with `ysh dbman.ysh <action>`.

The program is very similar to dbmate (apart from the DB_URL configuration,
that's hardcoded to `demo.db` right now). So you can, for example:

```
ysh dbman.ysh create
ysh dbman.ysh new my_migration
ysh dbman.ysh up
ysh dbman.ysh new another_migration
ysh dbman.ysh up
ysh dbman.ysh down
ysh dbman.ysh drop
```

See [1.1] for some more details on usage.

  [0]: https://github.com/oilshell/oil
  [1]: https://github.com/amacneil/dbmate
[1.1]: https://github.com/amacneil/dbmate#creating-migrations

dbman.ysh

func parseDbUrl(url) {
  # Should it be: const parts = url->split(":") ?
  const parts = split(url, ':')
  const db = parts[0]
  const DB_PATH = parts[1]

  if (db !== 'sqlite') {
    error ("Unsupported database: $db")
  }

  return (DB_PATH)
}

const DB_URL = 'sqlite:demo.db'
const DB_PATH = parseDbUrl(DB_URL)

proc ensure-db-dir {
  mkdir --parent db/migrations
}

proc ensure-db {
  if ! test --file $DB_PATH {
    sqlite3 $DB_PATH ''
  }
}

proc new(name) {
  ensure-db-dir

  const time = $(date +'%s')
  const migration = "db/migrations/$time-$name.sql"

  fopen >$migration {
    echo '-- migrate:up'
    echo
    echo '-- migrate:down'
  }
}

proc create {
  ensure-db $DB_URL
}

proc drop {
  if test --file $DB_PATH {
    rm $DB_PATH
  }
}

# This should be in the stdlib
func includes(haystack, needle) {
  for item in (haystack) {
    if (item === needle) {
      return (true)
    }
  }

  return (false)
}

proc ensure-migrations-table {
  ensure-db
  sqlite3 $DB_PATH 'CREATE TABLE IF NOT EXISTS migrations (version varchar(128) PRIMARY KEY)'
}

func listMigrations() {
  cd db/migrations {
    const migrations = split( $(ls *.sql | sed 's/\.sql$//'), $'\n')
    return (migrations)
  }
}

func appliedMigrations() {
  ensure-db $DB_URL
  ensure-migrations-table

  const applied = $(sqlite3 $DB_PATH 'SELECT * FROM migrations')
  return (split(applied, $'\n'))
}

proc parse-migration (file, outUp Ref, outDown Ref) {
  const contents = $(cat $file)
  const lines = split(contents, $'\n')

  var up = ''
  var down = ''

  var state = null
  for line in (lines) {
    if (line === '-- migrate:up') {
      setvar state = 'up'
      continue
    } elif (line === '-- migrate:down') {
      setvar state = 'down'
      continue
    }

    case (state) {
      up { setvar up = up ++ line ++ $'\n' }
      down { setvar down = down ++ line ++ $'\n' }
    }
  }

  setref outUp = up
  setref outDown = down
}

func missingMigrations() {
  const migrations = listMigrations()
  const applied = appliedMigrations()

  var missing = []
  for migration in (migrations) {
    for a in (applied) {
      if (a === migration) {
        echo "[x] $migration"
        continue 2
      }
    }

    echo "[ ] $migration"
    append :missing $migration
  }

  return (missing)
}

proc status {
  # Will print out migration status
  const missing = missingMigrations()

  if (len(missing) > 0) {
    echo "Need to run $[len(missing)] migration(s)"
  } else {
    echo "Up to date"
  }
}

proc up {
  const missing = missingMigrations()

  for name in (missing) {
    echo Running migration: $name
    #const path = "db/$name.sql"  -- would like to to this, but it errors
    var path = "db/$name.sql"

    var up = ''
    var down = ''
    parse-migration ./db/migrations/$name.sql :up :down

    write -- $up | sqlite3 $DB_PATH
    sqlite3 $DB_PATH "INSERT INTO migrations VALUES ('$name')"
  }
}

proc down {
  const applied = appliedMigrations()

  if (len(applied) === 0) {
    return
  }

  const toRemove = applied[-1]

  echo Dropping migration: $toRemove
  const path = "db/$toRemove.sql"
  parse-migration ./db/migrations/$toRemove.sql :up :down
  write -- $down | sqlite3 $DB_PATH
  sqlite3 $DB_PATH "DELETE FROM migrations WHERE version = '$toRemove'"
}

proc usage-error (message) {
  write -- $message >&2
  exit 1
}

if (len(ARGV) === 0) {
  usage-error 'No command given. Usage: dbman.ysh <command> [args]'
}

case (ARGV[0]) {
  new {
    if (len(ARGV) !== 2) {
      usage-error 'Expected a migration name. Usage: dbman.ysh new <migration>'
    }

    new $[ARGV[1]]
  }
  create {
    if (len(ARGV) !== 1) {
      usage-error 'Found unexpected args. Usage: dbman.ysh create'
    }

    create
  }
  drop {
    if (len(ARGV) !== 1) {
      usage-error 'Found unexpected args. Usage: dbman.ysh drop'
    }

    drop
  }
  up {
    if (len(ARGV) !== 1) {
      usage-error 'Found unexpected args. Usage: dbman.ysh up'
    }

    up
  }
  down {
    if (len(ARGV) !== 1) {
      usage-error 'Found unexpected args. Usage: dbman.ysh down'
    }

    down
  }
  status {
    if (len(ARGV) !== 1) {
      usage-error 'Found unexpected args. Usage: dbman.ysh status'
    }

    status
  }
  (else) { usage-error "Unknown command '$[ARGV[0]]'" }
}