/
TutorialManipulation.lhs
174 lines (144 loc) · 5.06 KB
/
TutorialManipulation.lhs
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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
> module TutorialManipulation where
>
> import Prelude hiding (sum)
>
> import Opaleye (Field, Table, table,
> tableField, (.==), (.<),
> Insert(..),
> Update(..),
> Delete(..),
> rCount,
> rReturning,
> updateEasy,
> SqlInt4, SqlFloat8, SqlText,
> sqlString
> )
>
> import Data.Profunctor.Product (p4)
> import Opaleye.ToFields (toFields)
>
> import GHC.Int (Int64)
Manipulation
============
Manipulation means changing the data in the database. This means SQL
DELETE, INSERT and UPDATE.
To demonstrate manipulation in Opaleye we will need a table to perform
our manipulation on. It will have four fields: an int4-valued "id"
field (assumed to be an auto-incrementing field) and three
float8-valued required fields. The `Table` type constructor has two
type arguments. The first one is the type of writes to the table, and
the second is the type of reads from the table. The "id" field is
defined as optional (for writes) because its write type is `Maybe
(Field SqlInt4)`. That means we don't necessarily need to specify it
when writing to the table. The database will automatically fill in a
value for us.
> myTable :: Table
> (Maybe (Field SqlInt4), Field SqlFloat8, Field SqlFloat8, Field SqlText)
> (Field SqlInt4, Field SqlFloat8, Field SqlFloat8, Field SqlText)
> myTable = table "tablename" (p4 ( tableField "id"
> , tableField "x"
> , tableField "y"
> , tableField "s" ))
To perform a delete we provide an expression from our read type to
`Field SqlBool`. All rows for which the expression is true are deleted.
> delete :: Delete Int64
> delete = Delete
> { dTable = myTable
> , dWhere = \(_, x, y, _) -> x .< y
> , dReturning = rCount
> }
DELETE FROM tablename
WHERE ((x) < (y))
To insert we provide rows with the write type. Optional fields can
be omitted by providing `Nothing` instead. Numeric SQL types have a
Haskell `Num` instance so we can write them using numeric literals.
Values of other types should be created using the functions in the
`Opaleye.SqlTypes` module, for example `sqlString` to create a `SqlText`
from a `String`.
> insertNothing :: Insert Int64
> insertNothing = Insert
> { iTable = myTable
> , iRows = [(Nothing, 2, 3, sqlString "Hello")]
> , iReturning = rCount
> , iOnConflict = Nothing
> }
INSERT INTO "tablename" ("id",
"x",
"y",
"s")
VALUES (DEFAULT,
2.0,
3.0,
E'Hello')
If we'd like to pass a variable into the insertion function, we can't
rely on the `Num` instance and must use `toFields`:
> insertNonLiteral :: Double -> Insert Int64
> insertNonLiteral i = Insert
> { iTable = myTable
> , iRows = [(Nothing, 2, toFields i, sqlString "Hello")]
> , iReturning = rCount
> , iOnConflict = Nothing
> }
INSERT INTO "tablename" ("id",
"x",
"y",
"s")
VALUES (DEFAULT,
2.0,
12.0,
E'Hello')
If we really want to specify an optional field we can use `Just`.
> insertJust :: Insert Int64
> insertJust = Insert
> { iTable = myTable
> , iRows = [(Just 1, 2, 3, sqlString "Hello")]
> , iReturning = rCount
> , iOnConflict = Nothing
> }
INSERT INTO "tablename" ("id",
"x",
"y",
"s")
VALUES (1,
2.0,
3.0,
E'Hello')
An update takes an update function from the read type to the write
type, and a condition given by a function from the read type to
`Field SqlBool`. All rows that satisfy the condition are updated
according to the update function.
> update :: Update Int64
> update = Update
> { uTable = myTable
> , uUpdateWith = updateEasy (\(id_, x, y, s) -> (id_, x + y, x - y, s))
> , uWhere = \(id_, _, _, _) -> id_ .== 5
> , uReturning = rCount
> }
SET "id" = "id",
"x" = ("x") + ("y"),
"y" = ("x") - ("y"),
"s" = "s"
WHERE (("id") = 5)
Sometimes when we insert a row with an automatically generated field
we want the database to return the new field value to us so we can use
it in future queries. SQL supports that via `INSERT RETURNING` and
Opaleye supports it also.
> insertReturning :: Insert [Int]
> insertReturning = Insert
> { iTable = myTable
> , iRows = [(Nothing, 4, 5, sqlString "Bye")]
> , iReturning = rReturning (\(id_, _, _, _) -> id_)
> , iOnConflict = Nothing
> }
INSERT INTO "tablename" ("id",
"x",
"y",
"s")
VALUES (DEFAULT,
4.0,
5.0,
E'Bye')
RETURNING "id"
Comments
========
Opaleye does not currently support SELECT-valued INSERT or UPDATE.