1
00:00:02,180 --> 00:00:05,750
With the database initialized, let's make sure we can store data in there.

2
00:00:05,750 --> 00:00:13,460
So back in the db.js file in the helpers folder, we can add a new constant, insert place for example

3
00:00:13,460 --> 00:00:14,850
which I export

4
00:00:15,110 --> 00:00:21,650
and now here I want to get the title of the place, I want to get the imageUri, I want to get the address

5
00:00:21,930 --> 00:00:27,770
and also a latitude and longitude, so I want to get all the data that makes up a place and in there,

6
00:00:27,800 --> 00:00:33,950
now I want to insert the place into the database. For that, we'll use the same syntax as here so

7
00:00:33,950 --> 00:00:39,890
I can just copy this because I also want to have a promise as a wrapper here so that I can conveniently

8
00:00:39,890 --> 00:00:45,430
use then and catch or async await in the place where I call insert place

9
00:00:45,560 --> 00:00:49,010
but of course the query I execute now is different.

10
00:00:49,010 --> 00:00:52,380
Instead of creating a table, I now want to insert data into the table and

11
00:00:52,400 --> 00:00:56,620
that's done with the insert into command.

12
00:00:56,660 --> 00:01:02,450
Now you need to specify the table and that's places and then between parentheses, the fields you want to

13
00:01:02,450 --> 00:01:03,380
target in there and

14
00:01:03,410 --> 00:01:08,840
of course I want to target all fields except for the ID because as I mentioned, that will be autogenerated

15
00:01:08,840 --> 00:01:10,320
which is very convenient of course

16
00:01:10,520 --> 00:01:15,510
but I want to target my title field and now you need to make sure you match the names which you set

17
00:01:15,510 --> 00:01:17,360
up there for your columns,

18
00:01:17,450 --> 00:01:20,150
you need to match these names here now.

19
00:01:20,180 --> 00:01:24,480
The order does not have to be the same but the naming is important,

20
00:01:24,500 --> 00:01:27,400
so I want to target title, the imageUri, that's

21
00:01:27,410 --> 00:01:30,360
also a name I chose up there, right?

22
00:01:30,440 --> 00:01:32,660
So imageUri, address,

23
00:01:32,660 --> 00:01:34,610
latitude and longitude

24
00:01:34,610 --> 00:01:38,110
and now we need to specify what values should go in there.

25
00:01:38,120 --> 00:01:44,260
You do that by adding the values keyword here after this list of columns you want to target

26
00:01:44,310 --> 00:01:50,330
and now you have another pair of parentheses here where you specify the values that should go into each

27
00:01:50,330 --> 00:01:51,310
of these columns.

28
00:01:51,320 --> 00:01:56,120
Now the order of values here of course has to match the order of column identifiers you have here,

29
00:01:56,150 --> 00:02:02,820
so the first value you enter here will go into the title, imageUri is the second value and so on.

30
00:02:02,840 --> 00:02:08,570
Now what you could do here is you could use of course back ticks instead of single quotes and then for

31
00:02:08,570 --> 00:02:15,970
example inject title here and then have a comma and then the imageUri and so on.

32
00:02:15,970 --> 00:02:20,270
You could do that and it would work but this wouldn't be good,

33
00:02:20,290 --> 00:02:21,940
this is insecure,

34
00:02:21,940 --> 00:02:32,350
it opens a door for an attack pattern called SQL injection, where users could kind of maybe insert malicious

35
00:02:32,350 --> 00:02:37,240
code in there and run other queries which could break the database.

36
00:02:37,240 --> 00:02:42,210
Of course in this app where it's a local database just on this user's device, that might not be super

37
00:02:42,210 --> 00:02:47,830
bad but still we want to defend against this and therefore here, you actually use a couple of placeholders,

38
00:02:47,850 --> 00:02:52,960
you add question marks here and you need to add as many question marks as you need to enter values.

39
00:02:53,050 --> 00:02:58,450
So here I'm entering five values, hence here I have five question marks separated by commas.

40
00:02:58,540 --> 00:03:03,700
Now the concrete values for the question marks are provided in the second argument you pass to execute

41
00:03:03,700 --> 00:03:07,990
SQL, this array of arguments. Previously it was empty,

42
00:03:08,020 --> 00:03:11,860
now we fill in the arguments in the order we need them,

43
00:03:11,860 --> 00:03:19,570
so title first, then imageUri and so on and the SQL package will then swap these question marks

44
00:03:19,810 --> 00:03:27,030
for our values but not before validating them to ensure that such an injection attack can't happen.

45
00:03:27,070 --> 00:03:33,050
So we get some extra automatic validation by using this approach and then we can enter the title here,

46
00:03:33,160 --> 00:03:36,040
then the second piece of data was the imageUri,

47
00:03:36,280 --> 00:03:37,450
then we need the address,

48
00:03:37,450 --> 00:03:40,210
then we need the latitude and then we need the longitude,

49
00:03:40,240 --> 00:03:47,310
so this is how we now pass our data into this query. Now in these success functions,

50
00:03:47,320 --> 00:03:52,510
as I mentioned the first argument always is like the repetition of your query but the second argument

51
00:03:52,510 --> 00:03:56,540
is then either the error or here the result of this query

52
00:03:56,650 --> 00:04:02,910
and here I want to resolve this result so that in the place where we call insert place, we can actually

53
00:04:02,910 --> 00:04:09,490
then listen to the resolving of the promise and get our result of this query which should be some

54
00:04:09,490 --> 00:04:12,640
information about the record that was created.

55
00:04:12,650 --> 00:04:18,730
So now we can call insert place and of course the place where I want to call it again is my places actions

56
00:04:18,730 --> 00:04:19,370
file.

57
00:04:19,610 --> 00:04:21,810
There we move the image, there

58
00:04:21,830 --> 00:04:28,700
I now also want to reach out to my database and add an entry. To do that in here first of all, let's import

59
00:04:28,910 --> 00:04:40,980
this insert place method from the helpers folder and there, from the db file and with that imported,

60
00:04:41,100 --> 00:04:46,450
in add place after solving our filesystem and so on, here 

61
00:04:46,470 --> 00:04:52,290
I of course then also, still in the try catch block, want to try inserting data into the database.

62
00:04:52,380 --> 00:04:53,520
This also can fail,

63
00:04:53,520 --> 00:04:57,600
so having it in a try block isn't the worst idea.

64
00:04:57,630 --> 00:05:03,930
So here I can call insert place and that returns a promise because we have our own promise in there, so

65
00:05:03,930 --> 00:05:05,370
we can awaits this

66
00:05:05,730 --> 00:05:11,460
and I get back my DB result thereafter, so I'll store this in a constant and now insert place of course

67
00:05:11,460 --> 00:05:17,200
needs a title, an imageUri, an address, a latitude and longitude.

68
00:05:17,270 --> 00:05:19,960
Now therefore I will forward my title which I already have,

69
00:05:19,960 --> 00:05:27,240
I will forward the new path which is my imageUri and now for address, I'll use a dummy address because

70
00:05:27,240 --> 00:05:29,700
we have no real address yet. For latitude,

71
00:05:29,700 --> 00:05:35,760
I'll use a dummy number of 15.6 and for longitude, let's say 12.3.

72
00:05:35,760 --> 00:05:39,750
Now this might be in the middle of the ocean, I don't know but at least we have some coordinates here

73
00:05:39,780 --> 00:05:45,730
which we can use for now until we're able to fetch a real location.

74
00:05:45,750 --> 00:05:51,960
Now this db result of course is interesting, so let's console log it and let's also by the way make

75
00:05:51,960 --> 00:05:57,730
sure that we only dispatch this action and therefore touch our Redux store if we succeeded with both

76
00:05:57,730 --> 00:06:00,330
the filesystem and the database action,

77
00:06:00,330 --> 00:06:06,690
so inside of the try block and now, let's have a look at this. Now we should be able to store data in

78
00:06:06,690 --> 00:06:07,740
the database,

79
00:06:07,770 --> 00:06:11,790
so let's test this on Android where we can take an image,

80
00:06:11,790 --> 00:06:14,240
this is a test.

81
00:06:14,270 --> 00:06:24,980
Let's take an image here and see whether all of that works by now cropping that image and saving this place.

82
00:06:25,090 --> 00:06:26,280
This works

83
00:06:26,380 --> 00:06:31,380
and here we see some output, we see this is the result of our database action

84
00:06:31,480 --> 00:06:37,000
and we see that one row was affected because one row was inserted and we also get back the ID that

85
00:06:37,000 --> 00:06:38,460
was automatically created.

86
00:06:38,500 --> 00:06:41,710
It's a numeric ID here but that's of course no problem,

87
00:06:41,710 --> 00:06:47,920
so now we can also use this ID in our reducer instead of manually creating this state snapshot ID

88
00:06:47,920 --> 00:06:49,060
here.

89
00:06:49,060 --> 00:06:51,520
So that's a tiny improvement,

90
00:06:51,520 --> 00:06:59,600
when we dispatch our action here in the place data, I now also want to provide the ID and that

91
00:06:59,600 --> 00:07:05,780
ID can be fetched from the DB result constant and there, it's in the insert ID field here as you

92
00:07:05,780 --> 00:07:06,170
can see,

93
00:07:06,170 --> 00:07:13,000
so let's access insert ID here. Now in the reducer therefore, we no longer need to generate it like this,

94
00:07:13,000 --> 00:07:17,330
instead here we can simply refer to action.placeData.id.

95
00:07:17,330 --> 00:07:22,100
Now keep in mind there it's a number, here I actually want to have a string though you could also keep

96
00:07:22,100 --> 00:07:28,760
a number but I will convert it to a string here and with that, we're now also using that ID, we're storing

97
00:07:28,760 --> 00:07:34,850
it in the database. Of course now it would be nice to load all our data whenever our app restarts because

98
00:07:34,850 --> 00:07:37,580
right now when it restarts, the data still is lost,

99
00:07:37,700 --> 00:07:39,950
it's not really lost, it isn't a database

100
00:07:39,950 --> 00:07:45,230
but we don't see it here because we don't load this into our Redux store which then of course is the

101
00:07:45,230 --> 00:07:46,640
data we use in the running app.

102
00:07:46,940 --> 00:07:50,840
So we need to make sure we do load the data into the Redux store when this app starts.