-- allows automatic derivation from Entity type class
{-# LANGUAGE DeriveAnyClass #-}
module Main (main, main1, main2, main3) where
import Database.GP
import Database.HDBC
import Database.HDBC.Sqlite3
import GHC.Generics
-- | An Entity data type with several fields, using record syntax.
data Person = Person
{ personID :: Int,
name :: String,
age :: Int,
address :: String
}
deriving (Generic, Entity, Show) -- deriving Entity allows to handle the type with GenericPersistence
data Book = Book
{ book_id :: Int,
title :: String,
author :: String,
year :: Int
}
deriving (Generic, Show) -- no auto deriving of Entity, so we have to implement the Entity type class:
instance Entity Book where
-- this is the primary key field of the Book data type
idField = "book_id"
-- this defines the mapping between the field names of the Book data type and the column names of the database table
fieldsToColumns = [("book_id", "bookId"), ("title", "bookTitle"), ("author", "bookAuthor"), ("year", "bookYear")]
-- this is the name of the database table
tableName = "BOOK_TBL"
-- -- this is the function that converts a row from the database table into a Book data type
-- fromRow _c row = return $ Book (col 0) (col 1) (col 2) (col 3)
-- where
-- col i = fromSql (row !! i)
-- -- this is the function that converts a Book data type into a row for the database table
-- toRow _c b = return [toSql (book_id b), toSql (title b), toSql (author b), toSql (year b)]
main :: IO ()
main = do
-- connect to a database
conn <- connect SQLite <$> connectSqlite3 "sqlite.db"
-- initialize Person table
setupTableFor @Person conn
-- create a Person entity
let alice = Person {personID = 123456, name = "Alice", age = 25, address = "Elmstreet 1"}
-- insert a Person into a database
insert conn alice
-- update a Person
update conn alice {address = "Main Street 200"}
-- select a Person from a database
-- The result type must be provided by the call site,
-- as `retrieveEntityById` has a polymorphic return type `IO (Maybe a)`.
alice' <- retrieveById @Person conn "123456"
print alice'
-- select all Persons from a database
allPersons <- retrieveAll @Person conn
print allPersons
-- delete a Person from a database
delete conn alice
-- select all Persons from a database. Now it should be empty.
allPersons' <- retrieveAll conn :: IO [Person]
print allPersons'
-- close connection
disconnect conn
main1 :: IO ()
main1 = do
-- connect to a database
conn <- Conn SQLite False <$> connectSqlite3 "test.db" -- ":memory:"
-- initialize Person and Book tables
setupTableFor @Person conn
setupTableFor @Book conn
let alice = Person 123456 "Alice" 25 "123 Main St"
book = Book 1 "The Hobbit" "J.R.R. Tolkien" 1937
-- insert a Person into the database (persist will either insert or update)
persist conn alice
-- insert a second Person
persist conn alice {personID = 123457, name = "Bob"}
-- update a Person
persist conn alice {address = "Elmstreet 1"}
-- select a Person from a database
alice' <- retrieveById conn (123456 :: Int) :: IO (Maybe Person)
print alice'
-- select all Persons from the database
allPersons <- retrieveAll conn :: IO [Person]
print allPersons
-- delete a Person
delete conn alice
-- select all Persons from a database. The deleted Person is not in the result.
allPersons' <- retrieveAll conn :: IO [Person]
print allPersons'
let book2 = Book {book_id = 2, title = "The Lord of the Ring", author = "J.R.R. Tolkien", year = 1954}
-- this time we are using insert directly
insert conn book
insert conn book2
allBooks <- retrieveAll conn :: IO [Book]
print allBooks
-- explicitly updating a Book
update conn book2 {title = "The Lord of the Rings"}
delete conn book
allBooks' <- retrieveAll conn :: IO [Book]
print allBooks'
-- close connection
disconnect conn
main2 :: IO ()
main2 = do
-- connect to a database
conn <- connect SQLite <$> connectSqlite3 ":memory:"
-- initialize Person table
setupTableFor @Person conn
let alice = Person 1 "Alice" 25 "123 Main St"
bob = Person 2 "Bob" 30 "456 Elm St"
charlie = Person 3 "Charlie" 35 "789 Pine St"
dave = Person 4 "Dave" 40 "1011 Oak St"
eve = Person 5 "Eve" 45 "1213 Maple St"
frank = Person 6 "Frank" 50 "1415 Walnut St"
people = [alice, bob, charlie, dave, eve, frank]
stmt = "SELECT * FROM Person WHERE age >= ?"
-- insert all persons into the database
insertMany conn people
-- select all Person with age >= 40
resultRows <- quickQuery conn stmt [toSql (40 :: Int)]
fourtplussers <- entitiesFromRows @Person conn resultRows
print fourtplussers
main3 :: IO ()
main3 = do
-- connect to a database
conn <- connect SQLite <$> connectSqlite3 "test.db"
-- initialize Person table
setupTableFor @Person conn
let alice = Person 1 "Alice" 25 "123 Main St"
bob = Person 2 "Bob" 30 "456 Elm St"
charlie = Person 3 "Charlie" 35 "789 Pine St"
dave = Person 4 "Dave" 40 "1011 Oak St"
eve = Person 5 "Eve" 45 "1213 Maple St"
frank = Person 6 "Frank" 50 "1415 Walnut St"
people = [alice, bob, charlie, dave, eve, frank]
-- insert all persons into the database
insertMany conn people
people' <- retrieveAll @Person conn
print $ length people'