module Main where import Control Applicative import Database SQLite Si

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
module Main where
import Control.Applicative
import Database.SQLite.Simple
import Database.SQLite.Simple.FromRow
import Data.Time
import Data.Time.Calendar
import System.IO -- for the buffering stuff
-- An aside: datetimes
now :: IO UTCTime
now = getCurrentTime -- 2019-01-01 16:54:52.03222 UTC
today :: IO Day
today = utctDay <$> now -- 2019-01-01
data Tool = Tool
{ toolId :: Int
, name :: String
, description :: String
, lastReturned :: Day
, timesBorrowed :: Int
}
data User = User
{ userId :: Int
, userName :: String
}
instance Show User where
show user = mconcat [ show $ userId user
, ".) "
, userName user]
instance Show Tool where
show tool = mconcat [ show $ toolId tool
, ".) "
, name tool
, "\n description: "
, description tool
, "\n last returned: "
, show $ lastReturned tool
, "\n times borrowed: "
, show $ timesBorrowed tool
, "\n"]
-- addUser action connects to database and inserts a user
-- The execute command allows you to insert users into your database.
addUser :: String -> IO ()
addUser userName = withConn "file:///C:/sqlite/tools.db" $
\conn -> do
execute conn "INSERT INTO users (username) VALUES (?)"
(Only userName)
print "user added"
-- withConn lets you abstract out connecting to the database
-- This function takes a String, which is the name of the database, and an action
-- that takes a connection as an argument. The end result is an action of type IO ().
withConn :: String -> (Connection -> IO ()) -> IO ()
withConn dbName action = do
conn <- open dbName
action conn
close conn
-- addUser to use withConn.
-- addUser :: String -> IO ()
-- addUser userName = withConn "tools.db" $
-- \conn -> do
-- execute conn "INSERT INTO users (username) VALUES (?)"
-- (Only userName)
-- print "user added"
checkout :: Int -> Int -> IO ()
checkout userId toolId = withConn "tools.db" $
\conn -> do
execute conn
"INSERT INTO checkedout (user_id,tool_id) VALUES (?,?)"
(userId,toolId)
-- The fromRow method ot the sqlite-simple library returns a RowParser of type a,
-- where a is the same type as whatever you’re making an instance of FromRow.
-- The field function is used internally by SQLite.Simple to consume the data
-- from a row and transform it into the values used by your type constructors.
instance FromRow User where
fromRow = User <$> field
<*> field
instance FromRow Tool where
fromRow = Tool <$> field
<*> field
<*> field
<*> field
<*> field
-- query :: (FromRow r, ToRow q) => Connection -> Query -> q -> IO [r]
-- query_ :: FromRow r => Connection -> Query -> IO [r]
-- Printing users from your database
printUsers :: IO ()
printUsers = withConn "tools.db" $
\conn -> do
-- printUsers action also takes advantage of the fact that User is an instance of Show,
resp <- query_ conn "SELECT * FROM users;" :: IO [User]
mapM_ print resp
main :: IO ()
main = print "db-lesson"