a result of trial and error in using haskell-relational-record

generated.sql

-- #1

SELECT
    COUNT (T1.f0) AS f0
FROM
    (
        SELECT
            T0.mac_address AS f0
        FROM
            MAIN.wifi AS T0
        WHERE
            (
                (T0.source = ?)
            AND (T0.dbm_signal > ?)
            )
        GROUP BY
            T0.mac_address
        HAVING (COUNT (T0.mac_address) > 1)
    ) AS T1


-- #2

SELECT
    T3.f0 AS f0,
    COUNT (T3.f0) AS f1
FROM
    (
        SELECT
            (substr (strftime ('%Y-%m-%d %H:%M', T0.date_time), 1, 15) || '0') AS f0,
            T2.f0 AS f1
        FROM
            MAIN.wifi AS T0
            INNER JOIN
                (
                    SELECT
                        T1.mac_address AS f0
                    FROM
                        MAIN.wifi AS T1
                    WHERE
                        (
                            (
                                (T1.source = 'x')
                            AND (T1.dbm_signal >= - 90)
                            )
                        OR  (
                                (
                                    (T1.source = 'y')
                                AND (T1.dbm_signal >= - 80)
                                )
                            OR  (
                                    (T1.source = 'z')
                                AND (T1.dbm_signal >= - 70)
                                )
                            )
                        )
                    GROUP BY
                        T1.mac_address
                    HAVING (COUNT (T1.mac_address) > 1)
                ) AS T2
            ON  (T0.mac_address = T2.f0)
        GROUP BY
            (substr (strftime ('%Y-%m-%d %H:%M', T0.date_time), 1, 15) || '0'),
            T2.f0
    ) AS T3
GROUP BY
    T3.f0
ORDER BY
    T3.f0 ASC


-- #3

SELECT
    T3.f0 AS f0,
    (COUNT (T3.f2) - COUNT (T3.f3)) AS f1,
    (COUNT (T3.f2) - COUNT (T3.f4)) AS f2,
    (COUNT (T3.f2) - COUNT (T3.f5)) AS f3
FROM
    (
        SELECT
            (substr (strftime ('%Y-%m-%d %H:%M', T0.date_time), 1, 15) || '0') AS f0,
            T0.mac_address AS f1,
            T0.source AS f2,
            nullif (T0.source, 'x') AS f3,
            nullif (T0.source, 'y') AS f4,
            nullif (T0.source, 'z') AS f5
        FROM
            MAIN.wifi AS T0
            INNER JOIN
                (
                    SELECT
                        T1.mac_address AS f0
                    FROM
                        MAIN.wifi AS T1
                    WHERE
                        (
                            (
                                (T1.source = 'x')
                            AND (T1.dbm_signal >= - 90)
                            )
                        OR  (
                                (
                                    (T1.source = 'y')
                                AND (T1.dbm_signal >= - 80)
                                )
                            OR  (
                                    (T1.source = 'z')
                                AND (T1.dbm_signal >= - 70)
                                )
                            )
                        )
                    GROUP BY
                        T1.mac_address
                    HAVING (COUNT (T1.mac_address) > 1)
                ) AS T2
            ON  (T0.mac_address = T2.f0)
        GROUP BY
            (substr (strftime ('%Y-%m-%d %H:%M', T0.date_time), 1, 15) || '0'),
            T0.mac_address,
            T0.source
    ) AS T3
GROUP BY
    T3.f0
ORDER BY
    T3.f0 ASC

Main.hs

{-# LANGUAGE FlexibleContexts #-}
{-# LANGUAGE MonadComprehensions #-}

module Main where

import qualified Language.SQL.Keyword.ConcatString as SQLs
import qualified Wifi

import Data.Int (Int32, Int64)
import Database.HDBC (IConnection, SqlValue)
import Database.HDBC.Record.Query (runQuery)
import Database.HDBC.Session (handleSqlError', withConnectionIO)
import Database.Record (FromSql, ToSql)
import Database.Relational.Query (ProjectableShowSql, SqlProjectable, Expr, Relation,
                                  aggregateRelation, aggregateRelation',
                                  and, asc, count, fromRelation, expr, fst', groupBy, having,
                                  on, or, placeholder, query, query', snd',
                                  showExpr, sqlFromRelation, unsafeBinOp, unsafeProjectSql, value, wheres,
                                  (!), (.-.), (.=.), (.>.), (><))
import Prelude hiding (and, or)
import SQLite3DataSource
import Wifi (wifi)

{-# ANN module "HLint: ignore Avoid lambda" #-}

runAndPrint :: (IConnection conn, ToSql SqlValue p, FromSql SqlValue a, Show a)
            => conn -> Relation p a -> p -> IO ()
runAndPrint conn rel param = do
    putStrLn $ "SQL: " ++ sqlFromRelation rel
    records <- runQuery conn param (fromRelation rel)
    mapM_ print records
    putStrLn ""

run :: IO ()
run = handleSqlError' $ withConnectionIO connect $ \conn -> do
    let run' :: (ToSql SqlValue p, FromSql SqlValue a, Show a) => Relation p a -> p -> IO ()
        run' = runAndPrint conn
    run' countUniqueMacRel ("x", -90)
    run' countUniqueMacRel ("y", -80)
    run' countUniqueMacRel ("z", -70)
    run' countUniqueMacPerHourRel ()
    run' countUniqueMacPer10MinsBySourceRel ()

main :: IO ()
main = run

-- Schema
-- CREATE TABLE WIFI (
--     DATE_TIME    text    not null,
--     GHZ          integer not null,
--     DBM_SIGNAL   integer not null,
--     DBM_NOISE    integer not null,
--     DB_SIGNAL    integer not null,
--     DB_NOISE     integer not null,
--     TYPE         integer not null,
--     SUBTYPE      integer not null,
--     MAC_ADDRESS  text    not null,
--     VENDOR       text    not null,
--     SOURCE       text    not null
-- );
-- CREATE INDEX WIFI_IDX1 ON WIFI (DATE_TIME,MAC_ADDRESS);
-- CREATE INDEX WIFI_IDX2 ON WIFI (MAC_ADDRESS);
-- CREATE INDEX WIFI_IDX3 ON WIFI (SOURCE,DBM_SIGNAL);

-- #1. 指定したソース上に記録された指定のシグナル強度以上のレコードのうち
--    同一ソース上に複数回以上記録されているMACアドレスを選択しユニーク数をカウントする
--     SELECT COUNT (*) FROM (SELECT mac_address FROM wifi
--           WHERE (source=?  AND dbm_signal>=?)
--                 GROUP BY mac_address having count(*) > 1);

selectValidMacRel :: Relation (String, Int64) String
selectValidMacRel = aggregateRelation'
  [ (sourceP >< dbmSignalP, g)
  | wi <- query wifi
  , (sourceP,    ()) <- placeholder (\ph -> wheres $ wi ! Wifi.source'    .=. ph)
  , (dbmSignalP, ()) <- placeholder (\ph -> wheres $ wi ! Wifi.dbmSignal' .>. ph)
  , let mac = wi ! Wifi.macAddress'
  , let c   = count mac
  , g  <- groupBy (wi ! Wifi.macAddress')
  , () <- having $ c .>. value 1
  ]

countUniqueMacRel :: Relation (String, Int64) Int32
countUniqueMacRel = aggregateRelation'
  [ (ph, c) 
  | (ph, validMac) <- query' selectValidMacRel
  , let c = count validMac
  ]

-- #2. 10分単位のユニークMACアドレス数(Total)
--    select date_hour_min, count(*) as total
--      from (
--        select substr(strftime('%Y-%m-%d %H:%M',date_time), 1, 15)||'0' as date_hour_min, mac_address
--          from wifi
--          where
--             mac_address in (
--                select mac_address
--                  from wifi
--                  where (source='x' and dbm_signal>=-90)
--                     or (source='y' and dbm_signal>=-80)
--                     or (source='z' and dbm_signal>=-70)
--                  group by mac_address
--                    having count(*) > 1
--             )
--          group by date_hour_min, mac_address
--      ) 
--      group by date_hour_min
--      order by date_hour_min asc;

sqlite3Strftime :: SqlProjectable p => Expr String -> Expr String -> p String
sqlite3Strftime f t  = unsafeProjectSql $
    "strftime(" ++ showExpr f ++ "," ++ showExpr t ++ ")"

sqlite3Substr :: SqlProjectable p => Expr String -> Expr Int64 -> Expr Int64 -> p String
sqlite3Substr s b e = unsafeProjectSql $
    "substr(" ++ showExpr s ++ "," ++ showExpr b ++ "," ++ showExpr e ++ ")" 

(.>=.) :: (SqlProjectable p, ProjectableShowSql p) => p ft -> p ft -> p (Maybe Bool)
(.>=.) = unsafeBinOp (SQLs..>=.)

infix 4 .>=.

selectAllValidMacRel :: Relation () String
selectAllValidMacRel = aggregateRelation
  [ g 
  | wi <- query wifi
  , let requisite src dbm = wi ! Wifi.source'    .=.  value src
                      `and` wi ! Wifi.dbmSignal' .>=. value dbm
  , let mac = wi ! Wifi.macAddress'
  , let c   = count mac
  , () <- wheres $ requisite "x" (-90)
              `or` requisite "y" (-80)
              `or` requisite "z" (-70)
  , g  <- groupBy (wi ! Wifi.macAddress')
  , () <- having $ c .>. value 1
  ]

selectFormattedRecordRel :: Relation () (String, String)
selectFormattedRecordRel = aggregateRelation
  [ g 
  | wi  <- query wifi
  , mac <- query selectAllValidMacRel
  , ()  <- on $ wi ! Wifi.macAddress' .=. expr mac
  , let (.||.) = unsafeBinOp (SQLs..||.)
  , let dateminx = sqlite3Strftime (value "%Y-%m-%d %H:%M") (wi ! Wifi.dateTime')
  , let datemin0 = sqlite3Substr dateminx (value 1) (value 15) .||. value "0"
  , g <- groupBy (datemin0 >< mac) 
  ]

countUniqueMacPerHourRel :: Relation () (String, Int32)
countUniqueMacPerHourRel = aggregateRelation
  [ g >< c 
  | wi <- query selectFormattedRecordRel
  , let datemin = wi ! fst'
  , g  <- groupBy datemin
  , let c = count datemin
  , () <- asc g 
  ]

-- #3. 10分単位のユニークMACアドレス数 (ソース別)
--    select date_hour_min,
--           count(*) - count(x_null) as x,
--           count(*) - count(y_null) as y,
--           count(*) - count(z_null) as z
--      from (
--        select substr(strftime('%Y-%m-%d %H:%M',date_time), 1, 15)||0 as date_hour_min,
--               source,
--               mac_address,
--               nullif(source, 'x') as x_null,
--               nullif(source, 'y') as y_null,
--               nullif(source, 'z') as z_null
--          from wifi
--          where
--             mac_address in (
--                select mac_address
--                  from wifi
--                  where (source='x' and dbm_signal>=-90)
--                     or (source='y' and dbm_signal>=-80)
--                     or (source='z' and dbm_signal>=-70)
--                  group by mac_address
--                    having count(*) > 1
--             )
--          group by date_hour_min, source, mac_address
--      )
--      group by date_hour_min
--      order by date_hour_min asc;

classifyRecordRel :: Relation () (((((String, String), String), Maybe String), Maybe String), Maybe String)
classifyRecordRel = aggregateRelation
  [ g >< x_source >< y_source >< z_source
  | wi  <- query wifi
  , mac <- query selectAllValidMacRel
  , ()  <- on $ wi ! Wifi.macAddress' .=. expr mac
  , let (.||.) = unsafeBinOp (SQLs..||.)
  , let dateminx = sqlite3Strftime (value "%Y-%m-%d %H:%M") (wi ! Wifi.dateTime')
  , let datemin0 = sqlite3Substr dateminx (value 1) (value 15) .||. value "0"
  , let nullif clm src = unsafeProjectSql $ "nullif(" ++ showExpr clm ++ "," ++ showExpr src ++ ")"
  , let x_source = nullif (wi ! Wifi.source') (value "x")
  , let y_source = nullif (wi ! Wifi.source') (value "y")
  , let z_source = nullif (wi ! Wifi.source') (value "z")
  , g <- groupBy (datemin0 >< wi ! Wifi.macAddress' >< wi ! Wifi.source') 
  ]

countUniqueMacPer10MinsBySourceRel :: Relation () (((String, Int32), Int32), Int32)
countUniqueMacPer10MinsBySourceRel = aggregateRelation
  [ g >< count src .-. count mxsrc  >< count src .-. count mysrc >< count src .-. count mzsrc
  | cr6 <- query classifyRecordRel
  , let (cr5, mzsrc) = (cr6 ! fst', cr6 ! snd')
  , let (cr4, mysrc) = (cr5 ! fst', cr5 ! snd')
  , let (cr3, mxsrc) = (cr4 ! fst', cr4 ! snd')
  , let (cr2, src)   = (cr3 ! fst', cr3 ! snd')
  , let datemin      = cr2 ! fst'
  , g  <- groupBy datemin
  , () <- asc g
  ]

SQLite3DataSource.hs

module SQLite3DataSource where

import Database.HDBC.Schema.Driver (typeMap)
import Database.HDBC.Schema.SQLite3 (driverSQLite3)
import Database.HDBC.Sqlite3 (Connection, connectSqlite3)
import Database.HDBC.TH (defineTableFromDB)
import Language.Haskell.TH (Q, Dec, TypeQ)
import Language.Haskell.TH.Name.CamelCase (ConName)

{-# ANN module "HLint: ignore Eta reduce" #-}

connect :: IO Connection
connect = connectSqlite3 "data/wifi.db"

defineTable :: [(String,TypeQ)] -> String -> String -> [ConName] -> Q [Dec]
defineTable tmap scm tbl derives =
    defineTableFromDB
      connect
      (driverSQLite3 { typeMap = tmap })
      scm tbl derives

Wifi.hs

{-# LANGUAGE TemplateHaskell #-}
{-# LANGUAGE FlexibleInstances #-}
{-# LANGUAGE MultiParamTypeClasses #-}

module Wifi where

import Database.Record.TH (derivingShow)
import SQLite3DataSource (defineTable)

$(defineTable [] "main" "wifi" [derivingShow])

欲しいもの:

• .||.
• .>=. とか .<=.
• in (select ...) ができるようなもの
• 関数名のプライム有無のルール

まだよくわかってないこと:

• pi の操作が大変なので簡単にレコードにする方法