Build a library with local data and web search

{
  "schemaVersion": 1,
  "projectId": "2d0b0c2d-6db2-4e04-8c1c-7bbd9c5e7a01",
  "name": "LibraryDesk",
  "kind": "DesktopApp",
  "language": {
    "dialect": "McBasic1",
    "requireExplicit": true,
    "compare": "Binary",
    "defaultArrayBase": 0
  },
  "application": {
    "bundleIdentifier": "com.mcbasic.samples.librarydesk",
    "version": "0.1.0",
    "buildNumber": "1",
    "startupFormId": "form-main",
    "shutdownMode": "OnStartupFormClosed"
  },
  "forms": [
    {
      "id": "form-main",
      "typeName": "frmLibrary",
      "layout": "Forms/frmLibrary.form.json",
      "code": "Forms/frmLibrary.bas"
    }
  ],
  "modules": [],
  "classes": [],
  "tests": [],
  "references": [],
  "resources": [],
  "targets": [
    "osx-arm64"
  ],
  "capabilities": [],
  "migrations": [
    {
      "id": 1,
      "path": "Database/migrations/0001_initial.sql"
    }
  ]
}

Private Async Sub Form_Shown()
    Dim version As Long

    mBusy = True
    cmdSearch.Enabled = False
    cmdSave.Enabled = False
    cmdExport.Enabled = False
    lblStatus.Caption = "Opening the local library..."

    Try
        Set mDb = Await Sqlite.OpenAsync(AppPaths.DataFile("librarydesk.db"))
        version = Await mDb.ApplyMigrationsAsync(App.Migrations)
        Await RefreshBooks()
        cmdSearch.Enabled = True
        cmdSave.Enabled = True
        cmdExport.Enabled = True
        lblStatus.Caption = CStr(mRows.RowCount) & " saved book(s). Search Open Library to add one."
    Catch failure As McException
        lblStatus.Caption = "Database error " & CStr(failure.Number) & ": " & failure.Message
    Finally
        mBusy = False
    End Try
End Sub

Private Async Function RefreshBooks() As Boolean
    Dim command As DbCommand

    Set command = mDb.CreateCommand("SELECT id, title, author, first_publish_year, saved_at FROM books ORDER BY title, id")
    Set mRows = Await command.QueryAsync()
    Set grdBooks.DataSource = mRows.Bind()
    RefreshBooks = True
End Function

Private Async Sub cmdSearch_Click()
    Dim query As String
    Dim url As String
    Dim envelopeText As String
    Dim bodyText As String
    Dim envelope As JsonValue
    Dim body As JsonValue
    Dim documents As JsonValue
    Dim firstBook As JsonValue
    Dim authors As JsonValue
    Dim title As String
    Dim author As String
    Dim firstYear As String
    Dim count As Long

    If mBusy Then Exit Sub
    query = Trim$(txtQuery.Text)
    If Len(query) = 0 Then
        lblStatus.Caption = "Enter a title or author to search."
        txtQuery.SetFocus
        Exit Sub
    End If

    mBusy = True
    cmdSearch.Enabled = False
    lblStatus.Caption = "Searching Open Library..."
    Try
        url = "https://openlibrary.org/search.json?q=" & Replace(query, " ", "+") & "&limit=5&fields=key,title,author_name,first_publish_year"
        envelopeText = Await Http.RequestAsync("GET", url, "{""User-Agent"":""McBasic Library Desk (replace-with-your-email@example.com)""}", "", 10000)
        envelope = Json.Parse(envelopeText)
        If CLng(Json.Value(Json.Get(envelope, "status"))) < 200 Or CLng(Json.Value(Json.Get(envelope, "status"))) >= 300 Then
            Err.Raise 513, "Web request", "The service returned HTTP " & CStr(Json.Value(Json.Get(envelope, "status")))
        End If
        bodyText = CStr(Json.Value(Json.Get(envelope, "body")))
        body = Json.Parse(bodyText)
        documents = Json.Get(body, "docs")
        count = Json.Count(documents)

        If count = 0 Then
            lblStatus.Caption = "No books matched the search."
            Exit Sub
        End If

        firstBook = Json.At(documents, 0)
        title = CStr(Json.Value(Json.Get(firstBook, "title")))
        author = "Unknown author"
        If Json.Has(firstBook, "author_name") Then
            authors = Json.Get(firstBook, "author_name")
            If Json.Count(authors) > 0 Then author = CStr(Json.Value(Json.At(authors, 0)))
        End If
        firstYear = ""
        If Json.Has(firstBook, "first_publish_year") Then
            firstYear = CStr(Json.Value(Json.Get(firstBook, "first_publish_year")))
        End If

        txtTitle.Text = title
        txtAuthor.Text = author
        txtYear.Text = firstYear
        lblStatus.Caption = CStr(count) & " result(s). The first result is ready to save."
    Catch failure As McException
        lblStatus.Caption = "Search error " & CStr(failure.Number) & ": " & failure.Message
    Finally
        mBusy = False
        cmdSearch.Enabled = True
    End Try
End Sub

Private Async Sub cmdSave_Click()
    Dim command As DbCommand

    If mBusy Then Exit Sub
    If Len(Trim$(txtTitle.Text)) = 0 Then
        lblStatus.Caption = "Search for a book before saving."
        Exit Sub
    End If
    If Len(Trim$(txtAuthor.Text)) = 0 Then txtAuthor.Text = "Unknown author"

    mBusy = True
    cmdSave.Enabled = False
    Try
        Set command = mDb.CreateCommand("INSERT INTO books(title, author, first_publish_year) VALUES(@title, @author, @year)")
        command.AddParameter "@title", Trim$(txtTitle.Text)
        command.AddParameter "@author", Trim$(txtAuthor.Text)
        If Len(Trim$(txtYear.Text)) = 0 Then
            command.AddParameter "@year", Null
        Else
            command.AddParameter "@year", CLng(Val(txtYear.Text))
        End If
        Await command.ExecuteAsync()
        Await RefreshBooks()
        lblStatus.Caption = "Book saved locally."
    Catch failure As McException
        lblStatus.Caption = "Save error " & CStr(failure.Number) & ": " & failure.Message
    Finally
        mBusy = False
        cmdSave.Enabled = True
    End Try
End Sub

Private Async Sub cmdExport_Click()
    Dim command As DbCommand
    Dim rows As Recordset

    If mBusy Then Exit Sub
    mBusy = True
    cmdExport.Enabled = False
    Try
        Set command = mDb.CreateCommand("SELECT title, author, first_publish_year, saved_at FROM books ORDER BY title, id")
        Set rows = Await command.QueryAsync()
        Await rows.ExportCsvAsync(AppPaths.DataFile("librarydesk.csv"))
        lblStatus.Caption = CStr(rows.RowCount) & " book(s) exported."
    Catch failure As McException
        lblStatus.Caption = "Export error " & CStr(failure.Number) & ": " & failure.Message
    Finally
        mBusy = False
        cmdExport.Enabled = True
    End Try
End Sub

Private Sub cmdClear_Click()
    If mBusy Then Exit Sub
    txtQuery.Text = ""
    txtTitle.Text = ""
    txtAuthor.Text = ""
    txtYear.Text = ""
    lblStatus.Caption = "Ready for another search."
    txtQuery.SetFocus
End Sub

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

Option Explicit

Private mDb As Database
Private mRows As Recordset
Private mBusy As Boolean

Private Async Sub Form_Shown()
    Dim version As Long

    mBusy = True
    cmdSearch.Enabled = False
    cmdSave.Enabled = False
    cmdExport.Enabled = False
    lblStatus.Caption = "Opening the local library..."

    Try
        Set mDb = Await Sqlite.OpenAsync(AppPaths.DataFile("librarydesk.db"))
        version = Await mDb.ApplyMigrationsAsync(App.Migrations)
        Await RefreshBooks()
        cmdSearch.Enabled = True
        cmdSave.Enabled = True
        cmdExport.Enabled = True
        lblStatus.Caption = CStr(mRows.RowCount) & " saved book(s). Search Open Library to add one."
    Catch failure As McException
        lblStatus.Caption = "Database error " & CStr(failure.Number) & ": " & failure.Message
    Finally
        mBusy = False
    End Try
End Sub

Private Async Function RefreshBooks() As Boolean
    Dim command As DbCommand

    Set command = mDb.CreateCommand("SELECT id, title, author, first_publish_year, saved_at FROM books ORDER BY title, id")
    Set mRows = Await command.QueryAsync()
    Set grdBooks.DataSource = mRows.Bind()
    RefreshBooks = True
End Function

Private Async Sub cmdSearch_Click()
    Dim query As String
    Dim url As String
    Dim envelopeText As String
    Dim bodyText As String
    Dim envelope As JsonValue
    Dim body As JsonValue
    Dim documents As JsonValue
    Dim firstBook As JsonValue
    Dim authors As JsonValue
    Dim title As String
    Dim author As String
    Dim firstYear As String
    Dim count As Long

    If mBusy Then Exit Sub
    query = Trim$(txtQuery.Text)
    If Len(query) = 0 Then
        lblStatus.Caption = "Enter a title or author to search."
        txtQuery.SetFocus
        Exit Sub
    End If

    mBusy = True
    cmdSearch.Enabled = False
    lblStatus.Caption = "Searching Open Library..."
    Try
        url = "https://openlibrary.org/search.json?q=" & Replace(query, " ", "+") & "&limit=5&fields=key,title,author_name,first_publish_year"
        envelopeText = Await Http.RequestAsync("GET", url, "{""User-Agent"":""McBasic Library Desk (replace-with-your-email@example.com)""}", "", 10000)
        envelope = Json.Parse(envelopeText)
        If CLng(Json.Value(Json.Get(envelope, "status"))) < 200 Or CLng(Json.Value(Json.Get(envelope, "status"))) >= 300 Then
            Err.Raise 513, "Web request", "The service returned HTTP " & CStr(Json.Value(Json.Get(envelope, "status")))
        End If
        bodyText = CStr(Json.Value(Json.Get(envelope, "body")))
        body = Json.Parse(bodyText)
        documents = Json.Get(body, "docs")
        count = Json.Count(documents)

        If count = 0 Then
            lblStatus.Caption = "No books matched the search."
            Exit Sub
        End If

        firstBook = Json.At(documents, 0)
        title = CStr(Json.Value(Json.Get(firstBook, "title")))
        author = "Unknown author"
        If Json.Has(firstBook, "author_name") Then
            authors = Json.Get(firstBook, "author_name")
            If Json.Count(authors) > 0 Then author = CStr(Json.Value(Json.At(authors, 0)))
        End If
        firstYear = ""
        If Json.Has(firstBook, "first_publish_year") Then
            firstYear = CStr(Json.Value(Json.Get(firstBook, "first_publish_year")))
        End If

        txtTitle.Text = title
        txtAuthor.Text = author
        txtYear.Text = firstYear
        lblStatus.Caption = CStr(count) & " result(s). The first result is ready to save."
    Catch failure As McException
        lblStatus.Caption = "Search error " & CStr(failure.Number) & ": " & failure.Message
    Finally
        mBusy = False
        cmdSearch.Enabled = True
    End Try
End Sub

Private Async Sub cmdSave_Click()
    Dim command As DbCommand

    If mBusy Then Exit Sub
    If Len(Trim$(txtTitle.Text)) = 0 Then
        lblStatus.Caption = "Search for a book before saving."
        Exit Sub
    End If
    If Len(Trim$(txtAuthor.Text)) = 0 Then txtAuthor.Text = "Unknown author"

    mBusy = True
    cmdSave.Enabled = False
    Try
        Set command = mDb.CreateCommand("INSERT INTO books(title, author, first_publish_year) VALUES(@title, @author, @year)")
        command.AddParameter "@title", Trim$(txtTitle.Text)
        command.AddParameter "@author", Trim$(txtAuthor.Text)
        If Len(Trim$(txtYear.Text)) = 0 Then
            command.AddParameter "@year", Null
        Else
            command.AddParameter "@year", CLng(Val(txtYear.Text))
        End If
        Await command.ExecuteAsync()
        Await RefreshBooks()
        lblStatus.Caption = "Book saved locally."
    Catch failure As McException
        lblStatus.Caption = "Save error " & CStr(failure.Number) & ": " & failure.Message
    Finally
        mBusy = False
        cmdSave.Enabled = True
    End Try
End Sub

Private Async Sub cmdExport_Click()
    Dim command As DbCommand
    Dim rows As Recordset

    If mBusy Then Exit Sub
    mBusy = True
    cmdExport.Enabled = False
    Try
        Set command = mDb.CreateCommand("SELECT title, author, first_publish_year, saved_at FROM books ORDER BY title, id")
        Set rows = Await command.QueryAsync()
        Await rows.ExportCsvAsync(AppPaths.DataFile("librarydesk.csv"))
        lblStatus.Caption = CStr(rows.RowCount) & " book(s) exported."
    Catch failure As McException
        lblStatus.Caption = "Export error " & CStr(failure.Number) & ": " & failure.Message
    Finally
        mBusy = False
        cmdExport.Enabled = True
    End Try
End Sub

Private Sub cmdClear_Click()
    If mBusy Then Exit Sub
    txtQuery.Text = ""
    txtTitle.Text = ""
    txtAuthor.Text = ""
    txtYear.Text = ""
    lblStatus.Caption = "Ready for another search."
    txtQuery.SetFocus
End Sub

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

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    first_publish_year INTEGER NULL,
    openlibrary_key TEXT NULL,
    saved_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);