json2-hdbc-0.5: examples/ExampleSqlite.hs

module Main where
import Control.Monad (unless)
import Database.HDBC
import Database.HDBC.Sqlite3
import Data.JSON2
import Database.JSON2.HDBC

dbName = "test.db"
tableName = "test"

mkSqlTable = unlines [
  "CREATE TABLE test (",
  "corporation VARCHAR(40) NOT NULL,",
  "division    INTEGER NOT NULL,",
  "name        VARCHAR(40) NOT NULL,",
  "id          INTEGER)"
  ]
selectSql = "SELECT * from test"
insertSql = " INSERT INTO test VALUES (?, ?, ?, ?)"

dataTable :: [(String, Int, String, Maybe Int)]
dataTable =
  [("A", 1,  "Pupkin",   Just 1)
  ,("A", 1,  "Sidorov",  Just 2)
  ,("B", 22, "Petrov",   Just 3)
  ,("B", 22, "Ivanov",   Nothing)
  ,("B", 22, "Golubev",  Nothing)
  ,("B", 1,  "Petrov",   Nothing)
  ]

toSql4 (x1, x2, x3, x4) = [toSql x1, toSql x2, toSql x3, toSql x4]

sqlTable = map toSql4 dataTable

main = do
    conn <- connectSqlite3 dbName
    tables <- getTables conn
    unless (tableName `elem` tables) $ do
       run conn  mkSqlTable []
       stmt <- prepare conn insertSql
       executeMany stmt sqlTable
       commit conn
    stmtSel <- prepare conn selectSql
    execute stmtSel []

    -- Get data from db.
    q <- fetchAllRows stmtSel
    mapM print q

    -- Conversion SQL to Json
    let json = toJson (q :: [[SqlValue]])
    pprint json

    -- Conversion JSON .
    let json' = groupWithNCol 2 json
    pprint json'

    disconnect conn