1
00:00:00,000 --> 00:00:00,770


2
00:00:00,770 --> 00:00:03,480
VIDEO: And then we print
out the success page.

3
00:00:03,480 --> 00:00:05,220
But this isn't all that useful.

4
00:00:05,220 --> 00:00:07,640
DAVID MALAN: So we've used SQL
in the class for some time.

5
00:00:07,640 --> 00:00:12,330
In fact, ever since 2007, we
introduced my SQL as a database engine.

6
00:00:12,330 --> 00:00:15,430
And we gave students user names and
passwords and they actually used that.

7
00:00:15,430 --> 00:00:17,180
And we made a pretty
big change this year.

8
00:00:17,180 --> 00:00:18,221
We're still teaching SQL.

9
00:00:18,221 --> 00:00:21,135
But we transitioned to
SQLite, as you may recall.

10
00:00:21,135 --> 00:00:23,260
Which was actually pretty
game changing, I thought.

11
00:00:23,260 --> 00:00:26,279
And I regret not having
thought about it earlier.

12
00:00:26,279 --> 00:00:28,070
This is partly because,
I think in my mind,

13
00:00:28,070 --> 00:00:31,530
SQL light had generally been used as a
little toy applications and so forth.

14
00:00:31,530 --> 00:00:34,571
But the reality is, it's being used
more and more for mobile programming.

15
00:00:34,571 --> 00:00:37,800
When you want to have a local
database that's still SQL queryable.

16
00:00:37,800 --> 00:00:40,592
And it just made it's so much easier
to actually set everything up.

17
00:00:40,592 --> 00:00:41,300
DOUG LLOYD: Yeah.

18
00:00:41,300 --> 00:00:43,650
It really lowers the barrier
to entry for students

19
00:00:43,650 --> 00:00:45,420
to get acclimated to a database.

20
00:00:45,420 --> 00:00:47,460
DAVID MALAN: You don't have to
install the software configure.

21
00:00:47,460 --> 00:00:49,290
And in the case of
CS50 IDE, we don't have

22
00:00:49,290 --> 00:00:51,990
to run multiple services
on multiple TCP ports.

23
00:00:51,990 --> 00:00:53,370
Which was really the motivation.

24
00:00:53,370 --> 00:00:56,270
And that then to provide students
with a web based interface to it.

25
00:00:56,270 --> 00:00:58,410
We were going to have to spawn
a second web server in addition

26
00:00:58,410 --> 00:01:01,020
to the Flask instance that they're
running as their app server.

27
00:01:01,020 --> 00:01:03,436
So there were just some annoying
real world complications.

28
00:01:03,436 --> 00:01:05,960
That we don't need something
like PostgreS or MySQL.

29
00:01:05,960 --> 00:01:08,176
We do want them to learn SQL.

30
00:01:08,176 --> 00:01:10,800
DOUG LLOYD: And they can learn
the fundamentals of the language

31
00:01:10,800 --> 00:01:13,624
without needing the
more complex database.

32
00:01:13,624 --> 00:01:16,290
DAVID MALAN: All of the CRUD
operations, select, insert, delete,

33
00:01:16,290 --> 00:01:17,790
update, we introduce them to.

34
00:01:17,790 --> 00:01:20,460
We have them create their
own tables now, actually.

35
00:01:20,460 --> 00:01:21,900
Which is nice.

36
00:01:21,900 --> 00:01:26,160
We even introduce them to indexes,
like primary keys, unique indexes,

37
00:01:26,160 --> 00:01:27,832
and so forth.

38
00:01:27,832 --> 00:01:29,790
And then we did end up
using the web front end.

39
00:01:29,790 --> 00:01:31,519
So we used PHP light admin.

40
00:01:31,519 --> 00:01:34,560
Which is very similar in spirit and
actually much better than phpMyAdmin.

41
00:01:34,560 --> 00:01:38,704
Which is Ajax based UI has just been
getting buggier and buggier over time.

42
00:01:38,704 --> 00:01:40,620
So PHP light admin
actually worked quite well.

43
00:01:40,620 --> 00:01:42,328
So I've been thrilled
with all this work.

44
00:01:42,328 --> 00:01:44,730
Oh and from a teacher's
perspective, the database then

45
00:01:44,730 --> 00:01:47,220
gets stored in a local binary file.

46
00:01:47,220 --> 00:01:48,960
Which just means when
students submit it,

47
00:01:48,960 --> 00:01:50,110
it's included with their submissions.

48
00:01:50,110 --> 00:01:50,490
DOUG LLOYD: Yeah.

49
00:01:50,490 --> 00:01:50,990
Oh my gosh.

50
00:01:50,990 --> 00:01:53,074
In the past we had to have
students do a SQL dump.

51
00:01:53,074 --> 00:01:54,073
DAVID MALAN: Oh my gosh.

52
00:01:54,073 --> 00:01:55,440
And then so many people forget.

53
00:01:55,440 --> 00:01:57,630
DOUG LLOYD: And then you
can't even get the program

54
00:01:57,630 --> 00:02:00,685
to work because you don't have access
to the schema that's underneath.

55
00:02:00,685 --> 00:02:01,860
DAVID MALAN: The TFs have to import it.

56
00:02:01,860 --> 00:02:04,260
It was just so much more
work for really no upside.

57
00:02:04,260 --> 00:02:08,780
So I don't know why it took
me 10 years to think of this.

58
00:02:08,780 --> 00:02:10,289
DOUG LLOYD: Iterations.

59
00:02:10,289 --> 00:02:11,820
But this is interesting.

60
00:02:11,820 --> 00:02:14,679
It's not the last piece
of the puzzle, for sure.

61
00:02:14,679 --> 00:02:15,720
There's still JavaScript.

62
00:02:15,720 --> 00:02:17,803
And there's lots of other
things that students can

63
00:02:17,803 --> 00:02:20,370
use to make their sites more robust.

64
00:02:20,370 --> 00:02:25,590
But it is a key piece of the puzzle to
creating a full fledged application.

65
00:02:25,590 --> 00:02:30,420
And once we've introduced SQL, this
is where many students' final projects

66
00:02:30,420 --> 00:02:31,860
will originate.

67
00:02:31,860 --> 00:02:36,090
Is having a front end, like we do
with CS50 finance piece at seven,

68
00:02:36,090 --> 00:02:39,710
with a SQL or SQLite back end.

69
00:02:39,710 --> 00:02:48,030
So finally allowing students to
control and search through data,

70
00:02:48,030 --> 00:02:49,530
really gives them that final piece.

71
00:02:49,530 --> 00:02:51,821
Because they have by this
point they've learned Python.

72
00:02:51,821 --> 00:02:55,320
And in the future, it's just
making the user experience

73
00:02:55,320 --> 00:02:57,275
that much better with
JavaScript than Ajax.

74
00:02:57,275 --> 00:02:57,640
DAVID MALAN: Yeah.

75
00:02:57,640 --> 00:03:00,306
And it's a conscious pedagogical
decision too that we give them.

76
00:03:00,306 --> 00:03:03,090
Now in Python, formerly in PHP.

77
00:03:03,090 --> 00:03:05,524
Now an execute function,
so a querying function.

78
00:03:05,524 --> 00:03:07,440
Written in Python that
actually abstracts away

79
00:03:07,440 --> 00:03:11,760
some of the implementation details
of using a library for SQL.

80
00:03:11,760 --> 00:03:15,480
I want them to learn a bit of
SQL Fundamentals from this.

81
00:03:15,480 --> 00:03:19,199
I don't want them to have to worry
as much about the specific API

82
00:03:19,199 --> 00:03:19,990
that they're using.

83
00:03:19,990 --> 00:03:24,840
Whether it's PTO in PHP or something
like SQL alchemy in Python.

84
00:03:24,840 --> 00:03:27,540
So we instead give them
just an execute method.

85
00:03:27,540 --> 00:03:32,790
That if a student has executed select,
returns you a list of dict objects.

86
00:03:32,790 --> 00:03:37,950
If it's an insert or update, we tell you
how many rows were updated or deleted.

87
00:03:37,950 --> 00:03:40,091
Or an insert, how many
rows were inserted.

88
00:03:40,091 --> 00:03:42,090
And so that simplification,
I think, works well.

89
00:03:42,090 --> 00:03:43,890
But what we did
consciously this year too,

90
00:03:43,890 --> 00:03:47,820
is you can relatively easily
remove that from your code.

91
00:03:47,820 --> 00:03:50,670
The CS50 library and
just use SQL alchemy,

92
00:03:50,670 --> 00:03:54,920
which is a popular SQL
library in Python on its own.

93
00:03:54,920 --> 00:03:58,871
DOUG LLOYD: And our XU query
also does some sanitization.

94
00:03:58,871 --> 00:03:59,370
Right?

95
00:03:59,370 --> 00:04:00,078
DAVID MALAN: Yes.

96
00:04:00,078 --> 00:04:02,020
And that's actually the
most important thing.

97
00:04:02,020 --> 00:04:05,044
In most any library instance, you
should absolutely be doing that.

98
00:04:05,044 --> 00:04:07,710
And we didn't want them to be
piecing together their own queries

99
00:04:07,710 --> 00:04:10,739
and repeating mistakes that we
ourselves highlighted in class.

100
00:04:10,739 --> 00:04:12,780
And for the first time
this year too, we actually

101
00:04:12,780 --> 00:04:16,950
introduced an ORM, so an
abstraction layer on top of SQL.

102
00:04:16,950 --> 00:04:19,110
Which we didn't encourage
students to use but

103
00:04:19,110 --> 00:04:22,050
I wanted them to see it even though
we didn't spend much time on it.

104
00:04:22,050 --> 00:04:24,210
And this of course is a
way of abstracting away

105
00:04:24,210 --> 00:04:26,700
the SQL syntax altogether
so that you just

106
00:04:26,700 --> 00:04:28,440
think of things in terms of objects.

107
00:04:28,440 --> 00:04:31,530
And the hope was that maybe some
of our most comfortable students

108
00:04:31,530 --> 00:04:33,960
would actually leverage that
functionality if they already

109
00:04:33,960 --> 00:04:35,490
came in knowing a bit of a SQL.

110
00:04:35,490 --> 00:04:38,560
But we didn't want to hide
the implementation details.

111
00:04:38,560 --> 00:04:40,380
So almost every student
in the end probably

112
00:04:40,380 --> 00:04:42,230
did use raw SQL queries as expected.

113
00:04:42,230 --> 00:04:44,730
DOUG LLOYD: There's definitely
an advantage to using an ORM.

114
00:04:44,730 --> 00:04:51,590
And not having even
more concepts to learn.

115
00:04:51,590 --> 00:04:55,057
Like having to learn the raw SQL.

116
00:04:55,057 --> 00:04:55,890
DAVID MALAN: Agreed.

117
00:04:55,890 --> 00:04:56,820
But especially these days.

118
00:04:56,820 --> 00:04:58,570
Especially with so
many students going off

119
00:04:58,570 --> 00:05:02,714
into finance, the natural social
sciences, or natural sciences

120
00:05:02,714 --> 00:05:04,380
where there's just data to be processed.

121
00:05:04,380 --> 00:05:08,129
And with data science so very much
in vogue, just having some SQL chops.

122
00:05:08,129 --> 00:05:10,920
And having that savvy so that you
can actually execute raw queries,

123
00:05:10,920 --> 00:05:13,610
I think is also just a
practical skill these days.

124
00:05:13,610 --> 00:05:16,260
And I think better that students
understand database design

125
00:05:16,260 --> 00:05:16,950
at this stage.

126
00:05:16,950 --> 00:05:20,459
And this is why we introduce
indexes, primary keys, foreign keys,

127
00:05:20,459 --> 00:05:21,000
and so forth.

128
00:05:21,000 --> 00:05:24,600
So that they actually think about the
underlying data that they're storing.

129
00:05:24,600 --> 00:05:27,810
And not just treat it as an
abstract key value store.

130
00:05:27,810 --> 00:05:30,060
So much like we start with
C and transition to Python.

131
00:05:30,060 --> 00:05:31,340
So do we start with SQL.

132
00:05:31,340 --> 00:05:35,100
And if they want for their final
projects, or beyond CS50 to use an ORM,

133
00:05:35,100 --> 00:05:35,790
so be it.

134
00:05:35,790 --> 00:05:36,956
DOUG LLOYD: No, that's fair.

135
00:05:36,956 --> 00:05:41,460
Because now we're again encouraging us
to be mindful of the design decisions

136
00:05:41,460 --> 00:05:44,036
that they're making when putting
together their own schema.

137
00:05:44,036 --> 00:05:44,536