M5Stackで蓄積したデータベースをグラフ表示するWebアプリを作る(SQLite3 WASM + OPFS編)

前回でSQLite3 WASMのバージョン情報をコンソールに出した。
今回はボタンクリックでデーターベースの情報をコンソールに出してみる。

ソースコードの編集

前回のコードは雑にJavaScriptのオブジェクトをそのままPureScriptの型にしていたので、変数にPureScriptの予想外の値が入っていると例外で死ぬ。
きちんとdecodeJsonして Either JsonDecodeError aで受ける。

• main関数でコンソールにバージョン情報を出している行を消した。

src/Main.purs

module Main where

import Prelude
import App.Pages.Home (mkHome)
import Data.Maybe (Maybe(..))
import Effect (Effect)
import Effect.Exception (throw)
import React.Basic.DOM.Client (createRoot, renderRoot)
import Web.DOM.NonElementParentNode (getElementById)
import Web.HTML (window)
import Web.HTML.HTMLDocument (toNonElementParentNode)
import Web.HTML.Window (document)

main :: Effect Unit
main = do
  -- React
  maybeRoot <- getElementById "root" =<< (map toNonElementParentNode $ document =<< window)
  case maybeRoot of
    Nothing -> throw "Root element not found."
    Just r -> do
      home <- mkHome
      root <- createRoot r
      renderRoot root (home unit)

• SQLite versionボタンを追加した。

src/App/Pages/Home.purs

module App.Pages.Home where

import Prelude
import Data.Either (either)
import Effect (Effect)
import Effect.Aff (Aff)
import Effect.Aff as Aff
import Effect.Class.Console (logShow, error)
import Effect.Exception (Error)
import React.Basic.DOM as DOM
import React.Basic.DOM.Events (capture_)
import React.Basic.Hooks (Component, component, useState, (/\))
import React.Basic.Hooks as React
import Sqlite3Wasm.Sqlite3Wasm (ConfigGetResult)
import Sqlite3Wasm.Sqlite3Wasm as Sq3

type HomeProps
  = Unit

mkHome :: Component HomeProps
mkHome = do
  component "Home" \_props -> React.do
    counter /\ setCounter <- useState 0
    pure
      $ DOM.div
          { children:
              [ DOM.h1_ [ DOM.text "Home" ]
              , DOM.p_ [ DOM.text "Try clicking the button!" ]
              , DOM.button
                  { onClick:
                      capture_ do
                        setCounter (_ + 1)
                  , children:
                      [ DOM.text "Clicks: "
                      , DOM.text (show counter)
                      ]
                  }
              , DOM.button
                  { onClick: capture_ versionButtonOnClickHandler
                  , children:
                      [ DOM.text "SQLite version"
                      ]
                  }
              ]
          }

versionButtonOnClickHandler :: Effect Unit
versionButtonOnClickHandler = Aff.runAff_ (either fail success) $ configGet
  where
  configGet :: Aff ConfigGetResult
  configGet = Sq3.configGet =<< Sq3.createWorker1Promiser

  fail :: Error -> Effect Unit
  fail = error <<< Aff.message

  success :: ConfigGetResult -> Effect Unit
  success r = logShow r

https://gemmaro.github.io/purescript-book/chapter10.html#json

• JavaScriptで生成されるオブジェクトをそのままPureScriptの型にするのは良くないので
  Json型を与えてdecodeJsonで変換する。

src/Sqlite3Wasm/Sqlite3Wasm.purs

module Sqlite3Wasm.Sqlite3Wasm
  ( SqliteResponse
  , ConfigGetResult
  , SqliteVersion
  , SqliteWorker1Promiser
  , configGet
  , createWorker1Promiser
  ) where

import Prelude
import Control.Promise (Promise, toAffE)
import Data.Argonaut.Core (Json)
import Data.Argonaut.Decode (JsonDecodeError, decodeJson, printJsonDecodeError)
import Data.Either (Either, either)
import Effect (Effect)
import Effect.Aff (Aff)
import Effect.Aff as Aff
import Effect.Uncurried (EffectFn1, runEffectFn1)

-- SQLite Version numbers
type SqliteVersion
  = { libVersion :: String
    , libVersionNumber :: Number
    , sourceId :: String
    , downloadVersion :: Number
    }

{-
  https://sqlite.org/wasm/doc/trunk/api-worker1.md
  SQLite WASM Worker1 API
-}
foreign import data SqliteWorker1Promiser :: Type

foreign import createWorker1PromiserImpl :: Effect (Promise SqliteWorker1Promiser)

-- SQLiteWorker1Promiserを得る
createWorker1Promiser :: Aff SqliteWorker1Promiser
createWorker1Promiser = createWorker1PromiserImpl # toAffE

-- SQLiteメソッドの応答
type SqliteResponse
  = { type :: String -- メソッド名または"error"
    , messageId :: String -- 何らかの値が返却されてくる
    , result :: Json -- 処理結果
    }

-- config-getメソッドの結果
type ConfigGetResult
  = { version :: SqliteVersion
    , bigIntEnabled :: Boolean
    , vfsList :: Array String
    }

foreign import configGetImpl :: EffectFn1 SqliteWorker1Promiser (Promise Json)

-- config-getメソッド呼び出し
configGet :: SqliteWorker1Promiser -> Aff ConfigGetResult
configGet promiser = do
  (respondJson :: Json) <- runEffectFn1 configGetImpl promiser # toAffE
  (respond :: SqliteResponse) <- throwErrorOnFailure $ decodeJson respondJson
  -- TODO: type "error"のことは今は気にしないことにする
  throwErrorOnFailure $ decodeJson respond.result
  where
  throwErrorOnFailure :: forall a. Either JsonDecodeError a -> Aff a
  throwErrorOnFailure = either (Aff.throwError <<< Aff.error <<< printJsonDecodeError) pure

ボタンを押すとコンソールに前回と同じバージョン情報が出る。

部屋の環境測定したデーターベースファイルを取り出す

これで測定したデーターベースファイルをSDカードに取り出す。

SQLite3 Fiddleでデータを見てみる

PCでSDカードにあるデーターベースファイルをSQLite3 Fiddleで見てみる。

Load DBしたあと.tablesを入力してRunする。

pressure relative_humidity temperature
３つテーブルがある。

続けて
SELECT DISTINCT sensor_id FROM temperature;

sensor_id
6001139082296557568
4777562956331220992

sensor_idは8バイト固定長C文字列なのでJavaScriptコンソールで変換してみる。

BigInt(6001139082296557568).toString(16)
'5348543330000000'

バイトオフセット	ASCIIコード	文字
0	0x53	S
1	0x48	H
2	0x54	T
3	0x33	3
4	0x30	0
5	0x00	\0
6	0x00	\0
7	0x00	\0

BigInt(4777562956331220992).toString(16)
'424d503238300000'

バイトオフセット	ASCIIコード	文字
0	0x42	B
1	0x4d	M
2	0x50	P
3	0x32	2
4	0x38	8
5	0x30	0
6	0x00	\0
7	0x00	\0

続いて

SELECT * FROM temperature LIMIT 10;

id	sensor_id	location	at	milli_degc
1	6001139082296557568	NULL	1730158546	21670
2	4777562956331220992	NULL	1730158546	21940
3	6001139082296557568	NULL	1730158546	21670
4	4777562956331220992	NULL	1730158546	21940
5	6001139082296557568	NULL	1730158547	21656
6	4777562956331220992	NULL	1730158547	21940
7	6001139082296557568	NULL	1730158548	21656
8	4777562956331220992	NULL	1730158548	21940
9	6001139082296557568	NULL	1730158549	21656
10	4777562956331220992	NULL	1730158549	21940

クエリ発行の結果を確認した。

OPFSにデーターベースファイルを保存する

直接ローカルファイルを開けなかった(JavaScriptでローカルファイルを自由に出来るとセキュリティ問題があるんじゃないか？)ので、ローカルファイルをOrigin Private File System(OPFS)に一旦上書き保存してから、OPFS上のデーターベースファイルを開くようにした。

Worker API #1 を FFIでPureScriptの関数にする

config-getメソッドに続いて、openメソッド、closeメソッド、execメソッドをPureScriptの関数にして行く。

src/Sqlite3Wasm/Sqlite3Wasm.js

import {
    sqlite3Worker1Promiser
} from '@sqlite.org/sqlite-wasm';

/* SQLite3 WASM Worker1 Promiser API */
export const createWorker1PromiserImpl = () => { return sqlite3Worker1Promiser.v2({}); };

export const configGetImpl = (promiser) => { return promiser('config-get', {}); };

export const openImpl = (promiser, opfsFilePath) => {
    return promiser("open", { filename: "file:" + opfsFilePath + "?vfs=opfs" });
};

export const closeImpl = (promiser, dbId) => {
    return promiser("close", { dbId: dbId });
};

export const execImpl = (promiser, dbId, sql) => {
    return promiser("exec", {
        dbId: dbId,
        sql: sql,
        rowMode: "object",
        returnValue: "resultRows",
    });
};

export const overwriteOpfsFileWithSpecifiedArrayBufferImpl = (
    opfsFilePath,
    arrayBuffer
) => {
    const writable = navigator.storage.getDirectory()
        .then(opfsRoot => { return opfsRoot.getFileHandle(opfsFilePath, { create: true }) })
        .then(fileHandle => { return fileHandle.createWritable() });
    return Promise.all([writable, arrayBuffer])
        .then(([w, ab]) => { w.write(ab).then(_ => { return w.close() }) });
};

export const toStringSensorIdImpl = (input) => {
    let dv = new DataView(new ArrayBuffer(10));
    dv.setBigUint64(0, BigInt(input), false);
    let result = new Uint8Array(dv.buffer).reduce((acc, item) => { if (item == 0x00) { return acc } else { return acc.concat(String.fromCharCode(item)) } }, "")
    return result
}

src/Sqlite3Wasm/Sqlite3Wasm.purs

module Sqlite3Wasm.Sqlite3Wasm
  ( CloseResult
  , ConfigGetResult
  , DbId
  , ExecResult
  , OpenResult
  , OpfsDatabaseFilePath(..)
  , SqliteResponse
  , SqliteVersion
  , SqliteWorker1Promiser
  , close
  , configGet
  , createWorker1Promiser
  , exec
  , open
  , overwriteOpfsFileWithSpecifiedArrayBuffer
  , toStringSensorId
  ) where

import Prelude
import Control.Promise (Promise, toAffE)
import Data.Argonaut.Core (Json)
import Data.Argonaut.Decode (JsonDecodeError, decodeJson, printJsonDecodeError)
import Data.ArrayBuffer.Types (ArrayBuffer)
import Data.Either (Either, either)
import Data.Generic.Rep (class Generic)
import Data.Maybe (Maybe)
import Data.Newtype (class Newtype, unwrap)
import Data.Show.Generic (genericShow)
import Effect (Effect)
import Effect.Aff (Aff)
import Effect.Aff as Aff
import Effect.Uncurried (EffectFn1, EffectFn2, EffectFn3, runEffectFn1, runEffectFn2, runEffectFn3)
import JS.BigInt (BigInt)

-- SQLite Version numbers
type SqliteVersion
  = { libVersion :: String
    , libVersionNumber :: Number
    , sourceId :: String
    , downloadVersion :: Number
    }

{-
  https://sqlite.org/wasm/doc/trunk/api-worker1.md
  SQLite WASM Worker1 API
-}
foreign import data SqliteWorker1Promiser :: Type

type DbId
  = Json

foreign import configGetImpl :: EffectFn1 SqliteWorker1Promiser (Promise Json)

foreign import openImpl :: EffectFn2 SqliteWorker1Promiser String (Promise Json)

foreign import closeImpl :: EffectFn2 SqliteWorker1Promiser DbId (Promise Json)

foreign import execImpl :: EffectFn3 SqliteWorker1Promiser DbId String (Promise Json)

foreign import createWorker1PromiserImpl :: Effect (Promise SqliteWorker1Promiser)

foreign import overwriteOpfsFileWithSpecifiedArrayBufferImpl :: EffectFn2 String ArrayBuffer (Promise Unit)

foreign import toStringSensorIdImpl :: EffectFn1 BigInt String

-- SQLiteWorker1Promiserを得る
createWorker1Promiser :: Aff SqliteWorker1Promiser
createWorker1Promiser = createWorker1PromiserImpl # toAffE

-- SQLiteメソッドの応答
type SqliteResponse
  = { type :: String -- メソッド名または"error"
    , messageId :: String -- 何らかの値が返却されてくる
    , result :: Json -- 処理結果
    }

-- config-getメソッドの結果
type ConfigGetResult
  = { version :: SqliteVersion
    , bigIntEnabled :: Boolean
    , vfsList :: Array String
    }

-- config-getメソッド呼び出し
configGet :: SqliteWorker1Promiser -> Aff ConfigGetResult
configGet promiser = do
  (respondJson :: Json) <- runEffectFn1 configGetImpl promiser # toAffE
  (respond :: SqliteResponse) <- throwErrorOnFailure $ decodeJson respondJson
  -- TODO: type "error"のことは今は気にしないことにする
  throwErrorOnFailure $ decodeJson respond.result

-- openメソッドの結果
type OpenResult
  = { filename :: String
    , dbId :: DbId
    , persistent :: Boolean
    , vfs :: String
    }

-- openメソッド呼び出し
open :: SqliteWorker1Promiser -> OpfsDatabaseFilePath -> Aff OpenResult
open promiser opfsFilePath = do
  (respondJson :: Json) <- runEffectFn2 openImpl promiser (unwrap opfsFilePath) # toAffE
  (respond :: SqliteResponse) <- throwErrorOnFailure $ decodeJson respondJson
  -- TODO: type "error"のことは今は気にしないことにする
  throwErrorOnFailure $ decodeJson respond.result

-- closeメソッドの結果
type CloseResult
  = { filename :: Maybe String
    }

-- closeメソッド呼び出し
close :: SqliteWorker1Promiser -> DbId -> Aff CloseResult
close promiser dbId = do
  (respondJson :: Json) <- runEffectFn2 closeImpl promiser dbId # toAffE
  (respond :: SqliteResponse) <- throwErrorOnFailure $ decodeJson respondJson
  -- TODO: type "error"のことは今は気にしないことにする
  throwErrorOnFailure $ decodeJson respond.result

-- execメソッドの結果
type ExecResult
  = { resultRows :: Array Json }

-- execメソッド呼び出し
exec :: SqliteWorker1Promiser -> DbId -> String -> Aff ExecResult
exec promiser dbId sql = do
  (respondJson :: Json) <- runEffectFn3 execImpl promiser dbId sql # toAffE
  (respond :: SqliteResponse) <- throwErrorOnFailure $ decodeJson respondJson
  -- TODO: type "error"のことは今は気にしないことにする
  throwErrorOnFailure $ decodeJson respond.result

--
throwErrorOnFailure :: forall a. Either JsonDecodeError a -> Aff a
throwErrorOnFailure = either (Aff.throwError <<< Aff.error <<< printJsonDecodeError) pure

-- OPFS上のSQLite3データーベースファイルパスを指定する型
newtype OpfsDatabaseFilePath
  = OpfsDatabaseFilePath String

derive instance genericOpfsDatabaseFilePath :: Generic OpfsDatabaseFilePath _

derive instance newtypeOpfsDatabaseFilePath :: Newtype OpfsDatabaseFilePath _

instance showBaseURL :: Show OpfsDatabaseFilePath where
  show = genericShow

-- OPFS上のSQLite3データーベースファイルを指定のバッファで上書きする。
overwriteOpfsFileWithSpecifiedArrayBuffer :: OpfsDatabaseFilePath -> ArrayBuffer -> Aff Unit
overwriteOpfsFileWithSpecifiedArrayBuffer opfsDb buffer = runEffectFn2 overwriteOpfsFileWithSpecifiedArrayBufferImpl (unwrap opfsDb) buffer # toAffE

-- センサーＩＤを文字列に変換する
toStringSensorId :: BigInt -> Effect String
toStringSensorId bigint = runEffectFn1 toStringSensorIdImpl bigint

ボタンクリックでSQLを実行する。

Reactでボタンを作って、クリックでSQLを実行出来るようにする。(promiserは使いまわすか、それとも毎回作るかどっちが良いんだろう。)

src/App/Pages/Home.purs

module App.Pages.Home
  ( HomeProps
  , mkHome
  ) where

import Prelude
import Data.Argonaut.Core (Json)
import Data.Argonaut.Decode (JsonDecodeError(..), decodeJson, printJsonDecodeError)
import Data.Argonaut.Decode.Class (class DecodeJson)
import Data.Either (Either, either)
import Data.Either as Either
import Data.Generic.Rep (class Generic)
import Data.Int (toNumber)
import Data.Maybe (Maybe(..), maybe)
import Data.Newtype (class Newtype, unwrap)
import Data.Show.Generic (genericShow)
import Data.Traversable (traverse, traverse_)
import Data.Tuple (uncurry)
import Data.Tuple.Nested (type (/\), (/\), uncurry3, tuple3)
import Effect (Effect)
import Effect.Aff (Aff)
import Effect.Aff as Aff
import Effect.Class.Console (log, logShow, error, errorShow)
import Effect.Exception (Error)
import JS.BigInt (BigInt)
import JS.BigInt as BigInt
import React.Basic.DOM as DOM
import React.Basic.DOM.Events (capture_, targetFiles)
import React.Basic.Events (handler)
import React.Basic.Hooks (Component, component, useEffect, useState)
import React.Basic.Hooks as React
import Sqlite3Wasm.Sqlite3Wasm (ConfigGetResult, DbId, OpenResult, OpfsDatabaseFilePath(..), SqliteWorker1Promiser)
import Sqlite3Wasm.Sqlite3Wasm as Sq3
import Unsafe.Coerce (unsafeCoerce)
import Web.File.File (File)
import Web.File.File as File
import Web.File.FileList (FileList)
import Web.File.FileList as FileList
import Web.File.FileReader.Aff (readAsArrayBuffer)

type HomeProps
  = Unit

type State
  = { counter :: Int
    , promiser :: Maybe SqliteWorker1Promiser
    , dbId :: Maybe DbId
    }

initialState :: State
initialState =
  { counter: 0
  , promiser: Nothing
  , dbId: Nothing
  }

mkHome :: Component HomeProps
mkHome = do
  component "Home" \_props -> React.do
    -- ステートフックを使って、stateとsetStateを得る
    stateHook@(state /\ setState) <- useState initialState
    -- 副作用フック(useEffect)
    useEffect unit do
      let
        fail :: Error -> Effect Unit
        fail = log <<< Aff.message

        update :: SqliteWorker1Promiser -> Effect Unit
        update newPromiser = setState _ { promiser = Just newPromiser }
      -- SQLite3 WASM Worker1 promiser を得る
      Aff.runAff_ (either fail update) Sq3.createWorker1Promiser
      -- 副作用フックのクリーンアップ関数を返却する
      pure $ void $ closeDatabaseHandler stateHook
    --
    pure
      $ DOM.div
          { children:
              [ DOM.h1_ [ DOM.text "Home" ]
              , DOM.p_ [ DOM.text "Try clicking the button!" ]
              , DOM.button
                  { onClick:
                      capture_ do
                        setState _ { counter = state.counter + 1 }
                  , children:
                      [ DOM.text "Clicks: "
                      , DOM.text (show state.counter)
                      ]
                  }
              , DOM.br {}
              , DOM.button
                  { onClick: capture_ versionButtonOnClickHandler
                  , children:
                      [ DOM.text "SQLite version"
                      ]
                  }
              , DOM.br {}
              , DOM.button
                  { onClick:
                      capture_ $ sensorIdButtonOnClickHandler stateHook "temperature"
                  , children:
                      [ DOM.text "temperatureテーブルに入ってるsensor_id" ]
                  }
              , DOM.br {}
              , DOM.button
                  { onClick:
                      capture_ $ getTemperatureButtonOnClickHandler stateHook
                  , children:
                      [ DOM.text "temperature" ]
                  }
              , DOM.br {}
              , DOM.button
                  { onClick:
                      capture_ $ sensorIdButtonOnClickHandler stateHook "relative_humidity"
                  , children:
                      [ DOM.text "relative_humidityテーブルに入ってるsensor_id" ]
                  }
              , DOM.br {}
              , DOM.button
                  { onClick:
                      capture_ $ getRelativeHumidityButtonOnClickHandler stateHook
                  , children:
                      [ DOM.text "relative_humidity" ]
                  }
              , DOM.br {}
              , DOM.button
                  { onClick:
                      capture_ $ sensorIdButtonOnClickHandler stateHook "pressure"
                  , children:
                      [ DOM.text "pressureテーブルに入ってるsensor_id" ]
                  }
              , DOM.br {}
              , DOM.button
                  { onClick:
                      capture_ $ getPressureButtonOnClickHandler stateHook
                  , children:
                      [ DOM.text "pressure" ]
                  }
              , DOM.br {}
              , DOM.button
                  { onClick:
                      capture_ $ openFileHandler stateHook opfsDatabaseFileToUse
                  , children:
                      [ DOM.text "Open database file on OPFS"
                      ]
                  }
              , DOM.p_
                  [ DOM.text "データーベースファイルをアップロードする"
                  , DOM.input
                      { type: "file"
                      , onChange:
                          handler targetFiles loadFileHandler
                      }
                  ]
              ]
          }

-- OPFS上のデータベースファイルパス
opfsDatabaseFileToUse :: OpfsDatabaseFilePath
opfsDatabaseFileToUse = OpfsDatabaseFilePath "env_database.sqlite3"

versionButtonOnClickHandler :: Effect Unit
versionButtonOnClickHandler = Aff.runAff_ (either fail success) $ configGet
  where
  configGet :: Aff ConfigGetResult
  configGet = Sq3.configGet =<< Sq3.createWorker1Promiser

  fail :: Error -> Effect Unit
  fail = error <<< Aff.message

  success :: ConfigGetResult -> Effect Unit
  success r = logShow r

sensorIdButtonOnClickHandler :: StateHook -> String -> Effect Unit
sensorIdButtonOnClickHandler (state /\ _) table =
  Aff.runAff_ (either fail success)
    $ maybe (Aff.throwError $ Aff.error "database file not opened") (uncurry3 getSensorIdStoredInTable) do
        promiser <- state.promiser
        dbId <- state.dbId
        pure $ tuple3 promiser dbId table
  where
  fail :: Error -> Effect Unit
  fail = error <<< Aff.message

  success :: Array DbSensorId -> Effect Unit
  success = traverse_ logShow

getTemperatureButtonOnClickHandler :: StateHook -> Effect Unit
getTemperatureButtonOnClickHandler (state /\ _) =
  Aff.runAff_ (either fail success)
    $ maybe (Aff.throwError $ Aff.error "database file not opened") (uncurry3 go) do
        promiser <- state.promiser
        dbId <- state.dbId
        pure $ tuple3 promiser dbId "temperature"
  where
  fail :: Error -> Effect Unit
  fail = error <<< Aff.message

  success :: Array TemperatureChartSeries -> Effect Unit
  success = traverse_ logShow

  mkRecord :: DbSensorId -> Array DbRowTemperature -> TemperatureChartSeries
  mkRecord sensorId xs = { sensor_id: sensorId, values: (\x -> { at: x.at, degc: (toNumber x.milli_degc) / 1000.0 }) <$> xs }

  go :: SqliteWorker1Promiser -> DbId -> String -> Aff (Array TemperatureChartSeries)
  go promiser dbId table = do
    sensors <- getSensorIdStoredInTable promiser dbId table
    traverse (\s -> mkRecord s <$> getTemperature promiser dbId s) sensors

getRelativeHumidityButtonOnClickHandler :: StateHook -> Effect Unit
getRelativeHumidityButtonOnClickHandler (state /\ _) =
  Aff.runAff_ (either fail success)
    $ maybe (Aff.throwError $ Aff.error "database file not opened") (uncurry3 go) do
        promiser <- state.promiser
        dbId <- state.dbId
        pure $ tuple3 promiser dbId "relative_humidity"
  where
  fail :: Error -> Effect Unit
  fail = error <<< Aff.message

  success :: Array RelativeHumidityChartSeries -> Effect Unit
  success = traverse_ logShow

  mkRecord :: DbSensorId -> Array DbRowRelativeHumidity -> RelativeHumidityChartSeries
  mkRecord sensorId xs = { sensor_id: sensorId, values: (\x -> { at: x.at, percent: (toNumber x.ppm_rh) / 10000.0 }) <$> xs }

  go :: SqliteWorker1Promiser -> DbId -> String -> Aff (Array RelativeHumidityChartSeries)
  go promiser dbId table = do
    sensors <- getSensorIdStoredInTable promiser dbId table
    traverse (\s -> mkRecord s <$> getRelativeHumidity promiser dbId s) sensors

getPressureButtonOnClickHandler :: StateHook -> Effect Unit
getPressureButtonOnClickHandler (state /\ _) =
  Aff.runAff_ (either fail success)
    $ maybe (Aff.throwError $ Aff.error "database file not opened") (uncurry3 go) do
        promiser <- state.promiser
        dbId <- state.dbId
        pure $ tuple3 promiser dbId "pressure"
  where
  fail :: Error -> Effect Unit
  fail = error <<< Aff.message

  success :: Array PressureChartSeries -> Effect Unit
  success = traverse_ logShow

  mkRecord :: DbSensorId -> Array DbRowPressure -> PressureChartSeries
  mkRecord sensorId xs = { sensor_id: sensorId, values: (\x -> { at: x.at, hpa: (toNumber x.pascal) / 100.0 }) <$> xs }

  go :: SqliteWorker1Promiser -> DbId -> String -> Aff (Array PressureChartSeries)
  go promiser dbId table = do
    sensors <- getSensorIdStoredInTable promiser dbId table
    traverse (\s -> mkRecord s <$> getPressure promiser dbId s) sensors

type StateHook
  = (State /\ ((State -> State) -> Effect Unit))

{-}
 データーベースにクエリを発行する
 -}
newtype DbSensorId
  = DbSensorId BigInt

derive instance genericDbSensorId :: Generic DbSensorId _

derive instance newtypeDbSensorId :: Newtype DbSensorId _

derive instance eqDbSensorId :: Eq DbSensorId

derive instance ordDbSensorId :: Ord DbSensorId

instance showDbSensorId :: Show DbSensorId where
  show = genericShow

-- BigIntはDecodeJsonのインスタンスでなかったので、BigIntを文字列で解釈してからBigIntに変換した。
-- unsafeCoerceを使っているからなんか問題があるかも
instance decodeJsonDbSensorId :: DecodeJson DbSensorId where
  decodeJson json = Either.note (UnexpectedValue json) maybeDbSensorId
    where
    str :: String
    str = unsafeCoerce json

    maybeDbSensorId :: Maybe DbSensorId
    maybeDbSensorId = DbSensorId <$> BigInt.fromString str

type ChartSeries a
  = { sensor_id :: DbSensorId, values :: Array a }

type DbRowTemperature
  = { at :: Int, milli_degc :: Int }

type TemperatureChartSeries
  = ChartSeries { at :: Int, degc :: Number }

type DbRowRelativeHumidity
  = { at :: Int, ppm_rh :: Int }

type RelativeHumidityChartSeries
  = ChartSeries { at :: Int, percent :: Number }

type DbRowPressure
  = { at :: Int, pascal :: Int }

type PressureChartSeries
  = ChartSeries { at :: Int, hpa :: Number }

-- データーベースを閉じる
closeDatabaseHandler :: StateHook -> Effect Unit
closeDatabaseHandler (state /\ setState) =
  maybe mempty (uncurry close) do
    promiser <- state.promiser
    dbId <- state.dbId
    pure (promiser /\ dbId)
  where
  close :: SqliteWorker1Promiser -> DbId -> Effect Unit
  close promiser dbId = Aff.runAff_ (either fail $ const success) $ Sq3.close promiser dbId

  fail :: Error -> Effect Unit
  fail = error <<< Aff.message

  success :: Effect Unit
  success = do
    logShow "database closed"
    setState _ { dbId = Nothing }

-- OPFS上のデーターベースファイルを開く
openFileHandler :: StateHook -> OpfsDatabaseFilePath -> Effect Unit
openFileHandler (state /\ setState) filepath = maybe mempty go state.promiser
  where
  go promiser = Aff.runAff_ (either fail success) $ Sq3.open promiser filepath

  fail :: Error -> Effect Unit
  fail = error <<< Aff.message

  success :: OpenResult -> Effect Unit
  success result = do
    logShow $ "database '" <> result.filename <> "' opened"
    setState _ { dbId = Just result.dbId }

-- テーブルに格納されているセンサーＩＤを取得する
getSensorIdStoredInTable :: SqliteWorker1Promiser -> DbId -> String -> Aff (Array DbSensorId)
getSensorIdStoredInTable promiser dbId targetTable = do
  result <- Sq3.exec promiser dbId $ "SELECT DISTINCT `sensor_id` FROM `" <> targetTable <> "` ORDER BY `sensor_id`;"
  let
    decoded = map decodeJson_ result.resultRows
  traverse (either fail success) decoded
  where
  fail = Aff.throwError <<< Aff.error <<< printJsonDecodeError

  decodeJson_ :: Json -> Either JsonDecodeError { sensor_id :: DbSensorId }
  decodeJson_ = decodeJson

  success r = pure r.sensor_id

-- 温度テーブルから温度を取得する
getTemperature :: SqliteWorker1Promiser -> DbId -> DbSensorId -> Aff (Array DbRowTemperature)
getTemperature promiser dbId sensorId = do
  let
    query =
      "SELECT `at`,`milli_degc` FROM `temperature` WHERE `sensor_id`="
        <> BigInt.toString (unwrap sensorId)
        <> " ORDER BY `at` ASC LIMIT 10;"
  void $ log query
  result <- Sq3.exec promiser dbId query
  let
    decoded = map decodeJson_ result.resultRows
  traverse (either fail pure) decoded
  where
  fail = Aff.throwError <<< Aff.error <<< printJsonDecodeError

  decodeJson_ :: Json -> Either JsonDecodeError DbRowTemperature
  decodeJson_ = decodeJson

-- 湿度テーブルから温度を取得する
getRelativeHumidity :: SqliteWorker1Promiser -> DbId -> DbSensorId -> Aff (Array DbRowRelativeHumidity)
getRelativeHumidity promiser dbId sensorId = do
  let
    query =
      "SELECT `at`,`ppm_rh` FROM `relative_humidity` WHERE `sensor_id`="
        <> BigInt.toString (unwrap sensorId)
        <> " ORDER BY `at` ASC LIMIT 10;"
  void $ log query
  result <- Sq3.exec promiser dbId query
  let
    decoded = map decodeJson_ result.resultRows
  traverse (either fail pure) decoded
  where
  fail = Aff.throwError <<< Aff.error <<< printJsonDecodeError

  decodeJson_ :: Json -> Either JsonDecodeError DbRowRelativeHumidity
  decodeJson_ = decodeJson

-- 気圧テーブルから温度を取得する
getPressure :: SqliteWorker1Promiser -> DbId -> DbSensorId -> Aff (Array DbRowPressure)
getPressure promiser dbId sensorId = do
  let
    query =
      "SELECT `at`,`pascal` FROM `pressure` WHERE `sensor_id`="
        <> BigInt.toString (unwrap sensorId)
        <> " ORDER BY `at` ASC LIMIT 10;"
  void $ log query
  result <- Sq3.exec promiser dbId query
  let
    decoded = map decodeJson_ result.resultRows
  traverse (either fail pure) decoded
  where
  fail = Aff.throwError <<< Aff.error <<< printJsonDecodeError

  decodeJson_ :: Json -> Either JsonDecodeError DbRowPressure
  decodeJson_ = decodeJson

-- OPFS上のデーターベースファイルにローカルファイルを上書きする
loadFileHandler :: Maybe FileList -> Effect Unit
loadFileHandler Nothing = mempty

loadFileHandler (Just filelist) = maybe (errorShow "FileList is empty") go $ FileList.item 0 filelist
  where
  go :: File -> Effect Unit
  go file = Aff.runAff_ (either fail $ const success) $ overwriteOpfsFile file

  overwriteOpfsFile :: File -> Aff Unit
  overwriteOpfsFile file = do
    ab <- readAsArrayBuffer (File.toBlob file)
    Sq3.overwriteOpfsFileWithSpecifiedArrayBuffer opfsDatabaseFileToUse ab

  fail :: Error -> Effect Unit
  fail = error <<< Aff.message

  success :: Effect Unit
  success = do
    log "OPFS file overwrited"

ボタンクリックでデーターベース上の測定データをコンソールに出力できた。

グラフにするデーターを用意できたので、今回はここまで。