Use transactions, editable drafts and backups

{
  "schemaVersion": 1,
  "projectId": "mcbasic-web-data-workbench",
  "name": "DataWorkbench",
  "kind": "DesktopApp",
  "language": {
    "dialect": "McBasic1",
    "requireExplicit": true,
    "compare": "Binary",
    "defaultArrayBase": 0
  },
  "application": {
    "bundleIdentifier": "org.mcbasic.web.dataworkbench",
    "version": "1.0.0",
    "buildNumber": "1",
    "startupFormId": "main",
    "shutdownMode": "OnStartupFormClosed"
  },
  "forms": [
    {
      "id": "main",
      "typeName": "frmMain",
      "layout": "frmMain.form.json",
      "code": "frmMain.bas"
    }
  ],
  "modules": [],
  "classes": [],
  "tests": [],
  "references": [],
  "resources": [],
  "targets": [
    "osx-arm64"
  ],
  "capabilities": []
}

Private Sub SetBusy(ByVal value As Boolean)
    mBusy = value
    cmdTransfer.Enabled = Not value
    cmdRename.Enabled = Not value
    cmdBackup.Enabled = Not value
End Sub

Private Async Sub Form_Shown()
    Dim migrations As MigrationSet
    Dim schema As DbSchema
    Dim version As Long
    mBusy = True
    Try
        Set mDb = Await Sqlite.OpenAsync(AppPaths.DataFile("data-workbench.db"))
        Set migrations = Sqlite.CreateMigrationSet()
        migrations.Add 1, "CREATE TABLE Accounts (ID INTEGER PRIMARY KEY, Name TEXT NOT NULL, Balance INTEGER NOT NULL CHECK(Balance >= 0)); INSERT INTO Accounts VALUES(1, 'First', 100); INSERT INTO Accounts VALUES(2, 'Second', 100);"
        version = Await mDb.ApplyMigrationsAsync(migrations)
        Set schema = Await mDb.GetSchemaAsync()
        If Not schema.HasTable("Accounts") Then Err.Raise 513, "DataWorkbench", "Accounts table missing."
        Await RefreshRows()
        SetBusy False
        lblStatus.Caption = "Migration " & CStr(version) & "; " & CStr(schema.TableCount) & " table(s). Transfers preserve a total of 200 units."
    Catch failure As McException
        lblStatus.Caption = "Startup failed: " & failure.Message
        mBusy = False
    End Try
End Sub

Private Async Function RefreshRows() As Boolean
    Dim command As DbCommand
    Dim rows As Recordset
    Set command = mDb.CreateCommand("SELECT ID, Name, Balance FROM Accounts ORDER BY ID")
    Set rows = Await command.QueryAsync()
    Set mRows = rows.Bind()
    Set grdAccounts.DataSource = mRows
    RefreshRows = True
End Function

Private Async Sub cmdTransfer_Click()
    Dim transaction As DbTransaction
    Dim command As DbCommand
    Dim changed As LongLong
    If mBusy Then Exit Sub
    SetBusy True
    Try
        Set transaction = Await mDb.BeginTransactionAsync()
        Set command = transaction.CreateCommand("UPDATE Accounts SET Balance = Balance - @amount WHERE ID = 1 AND Balance >= @amount")
        command.AddParameter "@amount", 10
        changed = Await command.ExecuteAsync()
        If changed <> 1 Then Err.Raise 513, "DataWorkbench", "The first account has fewer than 10 units."
        Set command = transaction.CreateCommand("UPDATE Accounts SET Balance = Balance + @amount WHERE ID = 2")
        command.AddParameter "@amount", 10
        Await command.ExecuteAsync()
        If chkRollback.Value Then
            Await transaction.RollbackAsync()
            lblStatus.Caption = "Rolled back: neither account changed."
        Else
            Await transaction.CommitAsync()
            lblStatus.Caption = "Committed: 10 units moved between accounts."
        End If
    Catch failure As McException
        lblStatus.Caption = "Transfer failed: " & failure.Message
    Finally
        If Not transaction Is Nothing Then transaction.Dispose
        SetBusy False
    End Try
    Await RefreshRows()
End Sub

Private Async Sub cmdRename_Click()
    Dim saved As Long
    If mBusy Then Exit Sub
    SetBusy True
    Try
        mRows.BeginEdit
        mRows.Require "Name"
        mRows.SetText 0, "Name", Trim$(txtName.Text)
        If Not mRows.Validate() Then
            lblStatus.Caption = "Draft rejected: " & mRows.ValidationError
            mRows.CancelEdit
            Exit Sub
        End If
        saved = Await mRows.SaveAsync(mDb, "Accounts", "ID")
        lblStatus.Caption = "Saved " & CStr(saved) & " draft change(s)."
        Await RefreshRows()
    Catch failure As McException
        mRows.CancelEdit
        lblStatus.Caption = "Draft save failed: " & failure.Message
    Finally
        SetBusy False
    End Try
End Sub

Private Async Sub cmdBackup_Click()
    Dim destination As String
    If mBusy Then Exit Sub
    destination = AppPaths.DataFile("data-workbench-backup.db")
    If File.Exists(destination) Then
        lblStatus.Caption = "Backup already exists; preserve it or move it before making a new backup."
        Exit Sub
    End If
    SetBusy True
    Try
        Await mDb.BackupAsync(destination)
        lblStatus.Caption = "Backup created: " & destination
    Catch failure As McException
        lblStatus.Caption = "Backup failed: " & failure.Message
    Finally
        SetBusy False
    End Try
End Sub

Private Sub Form_QueryUnload(ByRef Cancel As Integer, ByVal UnloadMode As Integer)
    If mBusy Then
        Cancel = 1
        lblStatus.Caption = "Finish the current data operation before closing."
    End If
End Sub

Private Sub Form_Unload()
    If Not mDb Is Nothing Then mDb.Dispose
End Sub

Option Explicit

Private mDb As Database
Private mRows As BindingSource
Private mBusy As Boolean

Private Sub SetBusy(ByVal value As Boolean)
    mBusy = value
    cmdTransfer.Enabled = Not value
    cmdRename.Enabled = Not value
    cmdBackup.Enabled = Not value
End Sub

Private Async Sub Form_Shown()
    Dim migrations As MigrationSet
    Dim schema As DbSchema
    Dim version As Long
    mBusy = True
    Try
        Set mDb = Await Sqlite.OpenAsync(AppPaths.DataFile("data-workbench.db"))
        Set migrations = Sqlite.CreateMigrationSet()
        migrations.Add 1, "CREATE TABLE Accounts (ID INTEGER PRIMARY KEY, Name TEXT NOT NULL, Balance INTEGER NOT NULL CHECK(Balance >= 0)); INSERT INTO Accounts VALUES(1, 'First', 100); INSERT INTO Accounts VALUES(2, 'Second', 100);"
        version = Await mDb.ApplyMigrationsAsync(migrations)
        Set schema = Await mDb.GetSchemaAsync()
        If Not schema.HasTable("Accounts") Then Err.Raise 513, "DataWorkbench", "Accounts table missing."
        Await RefreshRows()
        SetBusy False
        lblStatus.Caption = "Migration " & CStr(version) & "; " & CStr(schema.TableCount) & " table(s). Transfers preserve a total of 200 units."
    Catch failure As McException
        lblStatus.Caption = "Startup failed: " & failure.Message
        mBusy = False
    End Try
End Sub

Private Async Function RefreshRows() As Boolean
    Dim command As DbCommand
    Dim rows As Recordset
    Set command = mDb.CreateCommand("SELECT ID, Name, Balance FROM Accounts ORDER BY ID")
    Set rows = Await command.QueryAsync()
    Set mRows = rows.Bind()
    Set grdAccounts.DataSource = mRows
    RefreshRows = True
End Function

Private Async Sub cmdTransfer_Click()
    Dim transaction As DbTransaction
    Dim command As DbCommand
    Dim changed As LongLong
    If mBusy Then Exit Sub
    SetBusy True
    Try
        Set transaction = Await mDb.BeginTransactionAsync()
        Set command = transaction.CreateCommand("UPDATE Accounts SET Balance = Balance - @amount WHERE ID = 1 AND Balance >= @amount")
        command.AddParameter "@amount", 10
        changed = Await command.ExecuteAsync()
        If changed <> 1 Then Err.Raise 513, "DataWorkbench", "The first account has fewer than 10 units."
        Set command = transaction.CreateCommand("UPDATE Accounts SET Balance = Balance + @amount WHERE ID = 2")
        command.AddParameter "@amount", 10
        Await command.ExecuteAsync()
        If chkRollback.Value Then
            Await transaction.RollbackAsync()
            lblStatus.Caption = "Rolled back: neither account changed."
        Else
            Await transaction.CommitAsync()
            lblStatus.Caption = "Committed: 10 units moved between accounts."
        End If
    Catch failure As McException
        lblStatus.Caption = "Transfer failed: " & failure.Message
    Finally
        If Not transaction Is Nothing Then transaction.Dispose
        SetBusy False
    End Try
    Await RefreshRows()
End Sub

Private Async Sub cmdRename_Click()
    Dim saved As Long
    If mBusy Then Exit Sub
    SetBusy True
    Try
        mRows.BeginEdit
        mRows.Require "Name"
        mRows.SetText 0, "Name", Trim$(txtName.Text)
        If Not mRows.Validate() Then
            lblStatus.Caption = "Draft rejected: " & mRows.ValidationError
            mRows.CancelEdit
            Exit Sub
        End If
        saved = Await mRows.SaveAsync(mDb, "Accounts", "ID")
        lblStatus.Caption = "Saved " & CStr(saved) & " draft change(s)."
        Await RefreshRows()
    Catch failure As McException
        mRows.CancelEdit
        lblStatus.Caption = "Draft save failed: " & failure.Message
    Finally
        SetBusy False
    End Try
End Sub

Private Async Sub cmdBackup_Click()
    Dim destination As String
    If mBusy Then Exit Sub
    destination = AppPaths.DataFile("data-workbench-backup.db")
    If File.Exists(destination) Then
        lblStatus.Caption = "Backup already exists; preserve it or move it before making a new backup."
        Exit Sub
    End If
    SetBusy True
    Try
        Await mDb.BackupAsync(destination)
        lblStatus.Caption = "Backup created: " & destination
    Catch failure As McException
        lblStatus.Caption = "Backup failed: " & failure.Message
    Finally
        SetBusy False
    End Try
End Sub

Private Sub Form_QueryUnload(ByRef Cancel As Integer, ByVal UnloadMode As Integer)
    If mBusy Then
        Cancel = 1
        lblStatus.Caption = "Finish the current data operation before closing."
    End If
End Sub

Private Sub Form_Unload()
    If Not mDb Is Nothing Then mDb.Dispose
End Sub