1
00:00:06,600 --> 00:00:09,180
- In the previous demo, we
showed how to get a connection

2
00:00:09,180 --> 00:00:13,260
to a database represented
by a PooledConn object.

3
00:00:13,260 --> 00:00:15,180
Once we've got the PooledConn object,

4
00:00:15,180 --> 00:00:19,290
you can then invoke functions
upon it to execute queries.

5
00:00:19,290 --> 00:00:21,570
So you need to find out what functions

6
00:00:21,570 --> 00:00:25,950
does the PooledConn structure
have to execute those queries.

7
00:00:25,950 --> 00:00:28,050
So we need to look at the documentation.

8
00:00:28,050 --> 00:00:31,290
Okay, so here's the documentation
for the mysql crate,

9
00:00:31,290 --> 00:00:34,650
particularly looking at
the PooledConn structure.

10
00:00:34,650 --> 00:00:36,500
Let's see what functions it provides.

11
00:00:37,500 --> 00:00:39,360
Okay, so here's the PooledConn struct

12
00:00:39,360 --> 00:00:41,460
here in the mysql crate,

13
00:00:41,460 --> 00:00:44,040
and it's got a little
bit of an example here.

14
00:00:44,040 --> 00:00:46,290
If you scroll down over here,

15
00:00:46,290 --> 00:00:48,450
you can see all the different functions

16
00:00:48,450 --> 00:00:50,160
provided by PooledConn.

17
00:00:50,160 --> 00:00:52,680
So obviously when you're
learning a new API,

18
00:00:52,680 --> 00:00:55,650
part of the task is to figure
out which of these structures

19
00:00:55,650 --> 00:00:58,920
and enums and macros
and things are important

20
00:00:58,920 --> 00:01:02,040
and which ones you can
just chiefly ignore,

21
00:01:02,040 --> 00:01:04,050
and, you know, what you
don't wanna do is sit down

22
00:01:04,050 --> 00:01:05,460
and just read this from top to bottom

23
00:01:05,460 --> 00:01:08,640
because you'll read in
stuff you'll never need.

24
00:01:08,640 --> 00:01:10,830
The best approach to
learn is to have a task

25
00:01:10,830 --> 00:01:12,360
that you're trying to fulfill,

26
00:01:12,360 --> 00:01:14,340
and in order to fulfill a task,

27
00:01:14,340 --> 00:01:16,380
that will lead you to inquire about

28
00:01:16,380 --> 00:01:17,700
the functions that you need,

29
00:01:17,700 --> 00:01:20,010
and that's what I decided
to do with this demo.

30
00:01:20,010 --> 00:01:23,190
So with that in mind, we can observe

31
00:01:23,190 --> 00:01:27,810
that PooledConn implements
the Queryable trait.

32
00:01:27,810 --> 00:01:31,470
Okay, so that's worth looking
at back in the documentation.

33
00:01:31,470 --> 00:01:32,910
In the documentation,

34
00:01:32,910 --> 00:01:36,450
it shows methods available
directly in PooledConn,

35
00:01:36,450 --> 00:01:39,750
but also it shows methods
that it implements

36
00:01:39,750 --> 00:01:41,970
based on a particular trait.

37
00:01:41,970 --> 00:01:45,210
So it implements these traits here.

38
00:01:45,210 --> 00:01:48,240
It implements Debug, Deref.

39
00:01:48,240 --> 00:01:49,846
Deref means you can automatically,

40
00:01:49,846 --> 00:01:51,840
you can put a star in front of it.

41
00:01:51,840 --> 00:01:54,360
Drop, okay, so the
connection will be closed

42
00:01:54,360 --> 00:01:58,830
when you exit the function, and Queryable.

43
00:01:58,830 --> 00:02:01,620
So that's the trait that's interesting.

44
00:02:01,620 --> 00:02:04,110
The Queryable trait provides functions

45
00:02:04,110 --> 00:02:07,740
which allow us query the
database via the connection.

46
00:02:07,740 --> 00:02:11,097
Let's click on that, and what we see here

47
00:02:11,097 --> 00:02:13,620
are a bunch of different functions

48
00:02:13,620 --> 00:02:15,600
to do different types of query

49
00:02:15,600 --> 00:02:17,943
and execute different types of command.

50
00:02:19,200 --> 00:02:22,200
Okay, so just gonna give
you a quick overview

51
00:02:22,200 --> 00:02:27,200
of some of the important
functions in this Queryable trait.

52
00:02:27,600 --> 00:02:31,420
So there are a bunch of
methods called query something

53
00:02:32,550 --> 00:02:34,680
to execute simple queries,

54
00:02:34,680 --> 00:02:38,790
simple SQL statements.

55
00:02:38,790 --> 00:02:43,260
So for example, there are
various different functions

56
00:02:43,260 --> 00:02:47,280
called query, query_iter, query_first,

57
00:02:47,280 --> 00:02:51,180
query_map and query_drop
and other ones as well.

58
00:02:51,180 --> 00:02:53,520
So let me just give
you a quick run through

59
00:02:53,520 --> 00:02:54,600
of what these do.

60
00:02:54,600 --> 00:02:56,670
You can have a look in the documentation

61
00:02:56,670 --> 00:02:59,100
if you want the full technical details

62
00:02:59,100 --> 00:03:02,670
but just so you can
understand how it works.

63
00:03:02,670 --> 00:03:07,670
The idea is you'll have a
PooledConnection object here,

64
00:03:08,040 --> 00:03:10,380
and upon that PooledConnection,

65
00:03:10,380 --> 00:03:12,420
you'll call one of these functions.

66
00:03:12,420 --> 00:03:17,420
You'd say something like a
PooledConnection.query, okay?

67
00:03:17,760 --> 00:03:19,440
When you call the query function,

68
00:03:19,440 --> 00:03:22,980
what it'll do is it'll
kind of execute the SQL

69
00:03:22,980 --> 00:03:26,130
on the database, give
you back a results set,

70
00:03:26,130 --> 00:03:31,130
and it will collect that results
set into a vector of rows.

71
00:03:31,620 --> 00:03:34,710
So what you'll get back in
that case is a collection

72
00:03:34,710 --> 00:03:36,540
of all the rows in the results set

73
00:03:36,540 --> 00:03:39,270
already collected and bundled, okay,

74
00:03:39,270 --> 00:03:41,910
so you can then loop
through it in a normal loop.

75
00:03:41,910 --> 00:03:44,100
All right, so that's what the pooled,

76
00:03:44,100 --> 00:03:46,710
what the query function does.

77
00:03:46,710 --> 00:03:47,850
It gives you back a vector.

78
00:03:47,850 --> 00:03:52,850
I'm gonna draw this diagram
in a slightly different way

79
00:03:53,250 --> 00:03:55,950
because what's kind of important

80
00:03:55,950 --> 00:03:58,317
is that the PooledConnection,

81
00:03:58,317 --> 00:04:01,830
it will always come back with
a results set of some type,

82
00:04:03,420 --> 00:04:04,590
and then depending which of

83
00:04:04,590 --> 00:04:06,540
these query functions you've called,

84
00:04:06,540 --> 00:04:08,910
it'll kind of adapt that return.

85
00:04:08,910 --> 00:04:12,900
So if you called a query
function like this one here,

86
00:04:12,900 --> 00:04:15,780
it will collect all those rows together

87
00:04:15,780 --> 00:04:19,110
and give you back a vector, okay,

88
00:04:19,110 --> 00:04:21,930
which is my preferred approach.

89
00:04:21,930 --> 00:04:25,230
So that's what happens when
you call the query function.

90
00:04:25,230 --> 00:04:28,815
Now let's discuss what happens
when you call query_iter,

91
00:04:28,815 --> 00:04:30,750
query_iter, okay?

92
00:04:30,750 --> 00:04:34,440
So when you call the query_iter,
it's not quite the same.

93
00:04:34,440 --> 00:04:36,210
It'll still execute the query, you know,

94
00:04:36,210 --> 00:04:37,800
the select statement for example,

95
00:04:37,800 --> 00:04:40,260
and it'll still come
back with a results set,

96
00:04:40,260 --> 00:04:43,020
but it doesn't bundle it into a vector.

97
00:04:43,020 --> 00:04:46,170
What it gives you back
instead is an iterator,

98
00:04:46,170 --> 00:04:48,780
like a cursor which you can say

99
00:04:48,780 --> 00:04:52,050
you can then loop through the
elements one by one yourself.

100
00:04:52,050 --> 00:04:55,470
It hasn't bundled it into
a pre-aggregated vector.

101
00:04:55,470 --> 00:04:58,710
It just gives back an iterator,
hence the name query_iter,

102
00:04:58,710 --> 00:05:01,350
which you can then use
to go into the next row

103
00:05:01,350 --> 00:05:05,640
and the next row and the next
row, row by row like that.

104
00:05:05,640 --> 00:05:07,500
All right, so that kinda
makes sense, and again,

105
00:05:07,500 --> 00:05:10,200
you can have a look at the
documentation online for that,

106
00:05:10,200 --> 00:05:12,840
and it'll explain what I've just done.

107
00:05:12,840 --> 00:05:14,485
What about query_first?

108
00:05:14,485 --> 00:05:16,680
Well, I think you can probably
imagine what that one does.

109
00:05:16,680 --> 00:05:18,570
When you call query_first,

110
00:05:18,570 --> 00:05:20,400
it'll give you back the first row.

111
00:05:20,400 --> 00:05:22,573
So I'll say query_first.

112
00:05:24,510 --> 00:05:26,280
I'll call the query_first function.

113
00:05:26,280 --> 00:05:28,410
I give it an SQL as a parameter.

114
00:05:28,410 --> 00:05:31,170
It'll execute the query,
pull back the results set,

115
00:05:31,170 --> 00:05:34,920
but it'll just give me back the first row

116
00:05:34,920 --> 00:05:37,353
from the results set, okay, just that.

117
00:05:38,350 --> 00:05:41,967
Query_map is interesting.
Let's discuss query_map.

118
00:05:43,211 --> 00:05:46,080
That's quite a handy one actually.

119
00:05:46,080 --> 00:05:47,457
When you say query_map,

120
00:05:49,170 --> 00:05:52,863
what that does is it will
pull back a results set.

121
00:05:54,090 --> 00:05:55,500
When you call query_map,

122
00:05:55,500 --> 00:06:00,500
one of the parameters you pass
in is a lambda or closure,

123
00:06:00,780 --> 00:06:03,740
and it'll invoke that
closure upon each row

124
00:06:03,740 --> 00:06:07,560
in the results set to transform
it into something else,

125
00:06:07,560 --> 00:06:08,700
and that can be quite useful.

126
00:06:08,700 --> 00:06:11,130
So for example, if you'd executed a query,

127
00:06:11,130 --> 00:06:13,620
select staff from employees,

128
00:06:13,620 --> 00:06:16,830
each row in the results set
would be an employee row,

129
00:06:16,830 --> 00:06:20,070
you know, name, salary, region and ID.

130
00:06:20,070 --> 00:06:21,720
When you call a query_map,

131
00:06:21,720 --> 00:06:25,230
you could pass in a
closure here, a lambda,

132
00:06:25,230 --> 00:06:27,720
which would say for each row,

133
00:06:27,720 --> 00:06:30,090
convert it into an employee object,

134
00:06:30,090 --> 00:06:33,240
and what you'd get back
would be an employee object

135
00:06:33,240 --> 00:06:36,120
for the first row and an employee
object for the second row

136
00:06:36,120 --> 00:06:37,980
and an employee object for the third row,

137
00:06:37,980 --> 00:06:41,010
all kind of collated
together into a collection,

138
00:06:41,010 --> 00:06:42,483
pre-converted.

139
00:06:43,560 --> 00:06:44,730
So that's really quite handy.

140
00:06:44,730 --> 00:06:48,827
I quite like that function,
and then query_drop.

141
00:06:48,827 --> 00:06:53,160
With query_drop, that's useful
if you are not particularly,

142
00:06:53,160 --> 00:06:55,320
you want to perform some kind of SQL,

143
00:06:55,320 --> 00:06:56,700
but you're not particularly,

144
00:06:56,700 --> 00:06:57,960
you don't particularly care about

145
00:06:57,960 --> 00:06:59,970
the results you wanna get back, right?

146
00:06:59,970 --> 00:07:02,763
So if I just sanitize my diagram again.

147
00:07:04,440 --> 00:07:07,290
So you execute a query.
It returns a results set.

148
00:07:07,290 --> 00:07:09,120
Typically this is something like

149
00:07:09,120 --> 00:07:10,620
some kind of insert or update.

150
00:07:10,620 --> 00:07:13,350
I don't care about the
result. I just wanna do it.

151
00:07:13,350 --> 00:07:16,470
So there will be some kind of
results set that comes back,

152
00:07:16,470 --> 00:07:18,300
but it just drops that results set

153
00:07:18,300 --> 00:07:21,390
immediately having executed the statement.

154
00:07:21,390 --> 00:07:23,130
All right, so hopefully
that gives you an idea

155
00:07:23,130 --> 00:07:25,593
about the kind of functionality available.

156
00:07:26,490 --> 00:07:28,020
Have a look at the documentation,

157
00:07:28,020 --> 00:07:30,030
and it goes into more detail about

158
00:07:30,030 --> 00:07:31,860
the technicalities of those functions,

159
00:07:31,860 --> 00:07:33,000
and there are other functions as well.

160
00:07:33,000 --> 00:07:35,040
I'm not attempting to
be comprehensive here

161
00:07:35,040 --> 00:07:36,630
because there's too much detail.

162
00:07:36,630 --> 00:07:38,130
You'd never take it all in,

163
00:07:38,130 --> 00:07:39,390
but as long as you kind of get a feel

164
00:07:39,390 --> 00:07:41,760
for the kind of functionality that exists

165
00:07:41,760 --> 00:07:43,830
and know where to look
to get the full detail,

166
00:07:43,830 --> 00:07:45,150
that's the main thing.

167
00:07:45,150 --> 00:07:46,860
So let's have a look at an example.

168
00:07:46,860 --> 00:07:50,010
What we're gonna do is we're
gonna execute a SQL statement

169
00:07:50,010 --> 00:07:53,160
to create a temporary
table in the database.

170
00:07:53,160 --> 00:07:55,350
Our database already has tables,

171
00:07:55,350 --> 00:07:59,970
employee, employee two,
skills, contracts, whatever.

172
00:07:59,970 --> 00:08:02,553
I'm gonna execute a temporary,

173
00:08:03,450 --> 00:08:06,420
execute a SQL statement to
create a temporary table

174
00:08:06,420 --> 00:08:08,880
which will exist while
my application is running

175
00:08:08,880 --> 00:08:12,030
but then gets dropped as soon
as the application terminates.

176
00:08:12,030 --> 00:08:13,890
Okay, so in the main code,

177
00:08:13,890 --> 00:08:15,960
we're gonna look at the main function,

178
00:08:15,960 --> 00:08:18,060
and obviously that's a given,

179
00:08:18,060 --> 00:08:19,380
and then we're gonna
look at another function

180
00:08:19,380 --> 00:08:21,333
in my demo called create_temp_table.

181
00:08:22,560 --> 00:08:25,290
Right, so I've reset my code example.

182
00:08:25,290 --> 00:08:27,660
So in main, it's basically as it was

183
00:08:27,660 --> 00:08:30,360
in the original distribution.

184
00:08:30,360 --> 00:08:33,057
Basically all the code is uncommented,

185
00:08:33,057 --> 00:08:35,430
and I could potentially
be getting warnings

186
00:08:35,430 --> 00:08:36,840
if I start commenting bits out.

187
00:08:36,840 --> 00:08:37,830
So I'm gonna comment out.

188
00:08:37,830 --> 00:08:39,960
I'm gonna keep the first two statements

189
00:08:39,960 --> 00:08:43,890
to get the connection, and then
to create a temporary table

190
00:08:43,890 --> 00:08:46,590
using the connection, has to be mutable,

191
00:08:46,590 --> 00:08:48,690
I'm gonna comment out the rest of it.

192
00:08:48,690 --> 00:08:50,433
We're gonna look at that later on,

193
00:08:51,600 --> 00:08:55,080
and then in order to avoid
getting warnings about things,

194
00:08:55,080 --> 00:08:59,310
I'm gonna add back in
my crate level attribute

195
00:08:59,310 --> 00:09:00,720
to allow dead code.

196
00:09:00,720 --> 00:09:05,550
I actually don't need to
allow unused variables now

197
00:09:05,550 --> 00:09:10,550
because my connection
is actually being used,

198
00:09:10,770 --> 00:09:13,650
and it is being used in
a mutable kind of way.

199
00:09:13,650 --> 00:09:17,550
So I can actually get rid
of these warnings now.

200
00:09:17,550 --> 00:09:19,620
I don't need to suppress those warnings.

201
00:09:19,620 --> 00:09:22,080
I just need to avoid
warnings about functions

202
00:09:22,080 --> 00:09:23,970
that haven't been invoked.

203
00:09:23,970 --> 00:09:25,980
Okay, so that looks a bit nicer.

204
00:09:25,980 --> 00:09:28,470
Okay, so I guess we
need to look at the code

205
00:09:28,470 --> 00:09:32,460
in this function to see
how to execute a query.

206
00:09:32,460 --> 00:09:34,800
So here we are then.

207
00:09:34,800 --> 00:09:37,480
You just call the function
connection.query_drop

208
00:09:39,000 --> 00:09:42,570
and give it, oh, so here,
I've given it a raw string.

209
00:09:42,570 --> 00:09:45,390
Remember r? It means you can
have a multi-line string.

210
00:09:45,390 --> 00:09:49,440
That's very handy here,
a giant chunk of SQL.

211
00:09:49,440 --> 00:09:52,140
This, every database
has its own particular

212
00:09:52,140 --> 00:09:56,580
SQL variant or dialect.

213
00:09:56,580 --> 00:10:00,000
This is the SQL that you'd
need for MySQL database.

214
00:10:00,000 --> 00:10:03,990
It creates a temporary
table called employees_temp.

215
00:10:03,990 --> 00:10:06,630
There's already an employees
table in the database.

216
00:10:06,630 --> 00:10:09,060
This is just a temporary
one that'll disappear

217
00:10:09,060 --> 00:10:12,840
when my application finishes,
but it's got the same idea.

218
00:10:12,840 --> 00:10:15,720
There'll be an employee_id,
which is an integer,

219
00:10:15,720 --> 00:10:18,873
which, by the way, is the PRIMARY KEY,

220
00:10:19,980 --> 00:10:22,530
and then a string for the person's name

221
00:10:22,530 --> 00:10:26,370
and their salary and their region, okay?

222
00:10:26,370 --> 00:10:28,770
Like most functions, when
you're doing database handling,

223
00:10:28,770 --> 00:10:31,203
it returns a result enum,

224
00:10:32,190 --> 00:10:35,820
which you could check to see
if it's okay or an error.

225
00:10:35,820 --> 00:10:37,740
Just for the purposes of a simple demo,

226
00:10:37,740 --> 00:10:39,630
I'm just gonna unwrap the result

227
00:10:39,630 --> 00:10:41,493
and hope that it's all okay.

228
00:10:42,330 --> 00:10:43,800
So when I run the application,

229
00:10:43,800 --> 00:10:45,870
it'll create that temporary table,

230
00:10:45,870 --> 00:10:48,120
and then basically it'll stop, okay,

231
00:10:48,120 --> 00:10:50,610
because I'm not actually
doing anything with it,

232
00:10:50,610 --> 00:10:53,490
but at least, well, I'm
hoping, fingers crossed,

233
00:10:53,490 --> 00:10:55,200
I'm not gonna get any errors.

234
00:10:55,200 --> 00:10:58,110
So if I save the example and just run it,

235
00:10:58,110 --> 00:11:01,560
I'm hoping for, please, dear example,

236
00:11:01,560 --> 00:11:03,720
don't give me any errors.

237
00:11:03,720 --> 00:11:06,873
Cargo run and fingers crossed.

238
00:11:10,260 --> 00:11:11,220
Okay. Great.

239
00:11:11,220 --> 00:11:14,040
So the application ran without any errors.

240
00:11:14,040 --> 00:11:15,890
We're heading in the right direction.