Checkpoint Q&A #1
Checkpoint Q&A #1
This lesson is part of the Developer Online Class for OutSystems 9 course.

Here you can review some of the main concepts and get help on common problems and challenges faced by students when going through the previous chapters.

NOTE: You may have already watched sections of this session if you followed any of the "further information" callouts in previous lessons.

Hello and welcome to the first checkpoint of the developer foundation
course. Basically, on this checkpoint broadcast we're going to review some of
the basic concepts that you have come across in the lessons, and also talk about some of
the common pitfalls, problems, and specific challenges of the lessons
running up to each of these checkpoints. So what we have in store for this
checkpoint? Basically we're just going to talk very briefly again about aggregates
and SQL queries and what are the differences that you find between
them. Then we're going to talk a little bit about variable types and try
to demystify some ideas and concepts that people have about limitations.
Then we're going to mention bootstrapping from Excel and I'm just going to warn
you about one particular thing, that if you pay attention, it will save you a
lot of time. In specific to the assignment two, we're going to look at the
SQL query that you'll got over there, in particular the cancelled status, which
always raises a few questions, as to what you should pass to it. In the process of
doing this, I'm just going to warn you about arguments, suggestions, generally in
the Service Studio and finally just to close and make sure that everyone is on
the same page,
since the main objective of this first assignment is to assemble and created
effectively when booking, let's just go through everything that needs to be in a
booking. Now, as for the queries that you have available using the OutSystems
Platform, you probably remember creating two of those. In the preparation
you created what we call an aggregate and effectively these aggregates and not
just the aggregates but also the SQL, they've been explaining in the videos, but I
think it's just a good opportunity to talk a little bit about, what are the
differences between them, to just drive the point home. Basically if you remember
this is what the aggregate looks like, and effectively you have several panels that
allow you to get all the information and all the connections between the several
entities, several tables, that you want them to retrieve from the database.
So you have the panels to do with the SOURCES, what are the entities involved in
the queries, what are the joins that exist between them, and in particular, some times
people miss this, this is where you can actually go and see the actual
expression that binds the two entities. So we've got here the relationship between
booking and room via the foreign key and we see that this should be effectively
an inner join because it's ONLY WITH. FILTERS is where you'll find any
constraints, so this map to the WHERE clause, in normal database queries
and effectively here you can filter for parameters that might be going in to the
aggregates and for the relations that you might want.
In SORTING, you can specify one or more columns by which you want to sort, and if
you want these as ascending or descending and finally in TEST VALUES
everytime the aggregate editor determines that you've got a parameter that
somehow is going to influence the filter, something outside from the entities
involved, in this case we can see that we are using the BOOKINGID, which is a
parameter to this screen, then this BOOKINGID shows up in the test
values for you to fill in case you want to try the several outcomes
of this query. Ok? So this is basically how you do it and effective you can see
here that this panel will allow you to do the previews of the result set of
this query and currently we actually do not have any bookings in the database.
The other type of queries that we have are the SQL queries, you probably
created this one or in fact you have created this one in the assignment two, to
actually get the available room. So let me click here to get the available
rooms, and over here, we have the SQL that looks a little bit like, well any
query that you would do when using any sequel developer kind of tool, so effectively you
have a very big panel where you just write your SQL, you have parameters
that you pass into this query and you can then specify test
inputs, very similar to what we saw in the aggregates, to actually test this thing in
runtime over here, sorry, in development time, while you are in Service Studio, which is basically
what you've been doing. We're going to drill a little bit more into these
parameters, but these are effectively just to give you an idea of, to remember what we're
talking about, about these two concepts. Let me just go back to the couple of
slides where we try to summarize the differences that you find between this
two things. So, the aggregate query, the first one, the one day you saw, supports
effectively the conventional queries,
that involve one or more entities and you can, as we saw, define the joins.
You can also create and compute additional columns, so effectively you can break out of just
having tables involved and actually generates new columns, by using formulas,
and you are going to do this a lot during the whole course. Another very interesting
thing that actually is the reason why this is called the aggregate, you can
do GROUP BY functionalities, clustering your results and therefore applying
aggregate functions like AVERAGE and SUM
as you normally are used to do if you use SQL directly. Finally, you can
also ORDER BY either one or more columns, ascending descending, and you can actually
order this things dynamically as well, and you're going to do this much later
in the course. Now,
what are some of the features of this particular approach? It's a very
intuitive visual design, there's very, very little typing, mostly you can just
drag-and-drop your your entities to create relationships between them. You get
a live preview of result set, the fetched columns, and this is something that it's
probably not mentioned in the videos, you probably don't realize, is the actual
fetched columns from the database are optimized by the compiler. What do I mean
by this? Is effectively, if you're doing the query as we were doing in our
particular example, let's go to the aggregates here. This query here, is in theory,
it looks like it's actually grabbing everything from booking and room. We can't
actually pick which columns we want to be in the output, nor do we really need to, because
effectively, the compiler is going to, when he's going through your coding
generating code, is going to realize which columns you're actually using
anywhere in this particular espace, that's coming out of this query and therefore
he realizes that you just are using, say for example, from the booking you're just using
the check-in and check-out date, In this particular screen, the generated code
actually only fetches these two columns. This is very very handy and allows you to
not have to keep revisiting the query if you need more outputs. So it's actually a lot,
a lot of work that's saved for you because the compiler does this automation. As you can see the
visual design also brings quite a few things not only the maintenance of when
you change your entities, as it was seen here in the last bullet, but something that people don't
actually realize is
the platform can run against three types of database, Oracle, SQL Server and
MySQL and if you change, if you take your module into an infrastructure that
is actually running against different database, your query will require no
modification, the compiler actually generates the SQL, which is consistent
the database that you are using over there. On the other hand the SQL queries, the ones
that we saw, sometimes you might see them mentioned has advanced queries or SQL
queries, they have very big flexibility because obviously you write the SQL
yourself and therefore you can do anything that the database allows you
like sub-queries and other kinds of statements. Obviously this thing comes at a cost, which
is since you write you're own query and we don't really change anything we
just pass it to the database as it is, this means that although you can
actually get the most out of your queries by fine-tuning them, it means
that we can't go back and scale the query and get extra columns, if for
example you need those. So it it actually takes a little bit more time to backport
any changes that you might make and obviously if your query uses certain
statements that only exists in one database, for example the TOP in Microsoft SQL, if
you go into Oracle that is not going to work, because the mechanic to get the
maximum number of rows is different in Oracle than it is in SQL Server. So, it
is a balancing act, by and large, what we say is if you can do it in an aggregate,
do it in an aggregate, because it's going to take less time to scale, and it will not give you
quite as much trouble to maintain. Ok, so one thing to do since we're talking
about data and retrieving data, one very common problem that people get when
they're in these particular assignments,
is the bootstrapping of data and let me just call to your attention and ask
you to please, whenever you bootstrapping data, please always take this into
account, which is make sure that when you create your module, that
names and the types of the columns are correct with the Excel that you then going
to use to bootstrap the data. Let me just give you a very quick example here. So if
I go and bootstrap from rooms, you can see that every single column in Excel is
mapped to a room attribute, this means that we're going to produce code that
is very consistent, that every single thing that we found in the Excel file is going
to end up neatly in your entity. Now if for example, and this is quite common,
you miss typed like the name of the attribute, so instead of price let's go
and write first, ok? So we just swap this thing around and likewise let's actually
go into AdultCapacity, which is an integer and change this to date or a datetime.
Ok? Now if I bootstrap from the exact same Excel file, noticed that
these two columns in the Excel, have been matched to what's in your module,
ok?... your entity. This is going to cause the bootstrap code to be incomplete, he is
only going to be loading over the room number and the ChildrenCapacity into your
database, ok? So, again as I basically need to drive the point home is,
always pay very much attention to this particular bootstrap screen and make sure that
everything that should be lined up his lined up, because what happens is even
though it is possible to fix this
afterwards, by changing the code, it is not a fun thing to do.
Ok? By this time you might also have stuff already in your database, with
the missing columns
and you're just going to end up with the really bad data. So, let's go back to
data and talk a little bit about variables. Very, very quickly about
variables, just to demystify and it is very common that we find that people assume
when you're creating a variable, I'm just creating a variable, any old variable,
in one screen, is when creating a variable, it's easy, normally for students, to
assume: "ok, I can create a variable of some basic type, some integer, some text and
actually work on that, but for some reason people don't realize that
they can also create complex variables which are made up of records, and sometimes we
see people doing empty queries, just so they can have a variable at hand,
with some extra information, that they can build and just to demystify, yes
you can have the basic types, so this variable can be text, integer, etcetera,
furthermore, it can be of identifier types, those are the foreign keys,
in the videos we just talk about this as well, so these are types that, are not
complete entities, are just strictly speaking the identifier that you
normally use to retrieve something, but, if you want this variable in memory
to be of a certain record, say we want a booking, we can go over here and
stay booking, ok? This means, let me go into this preparation just to show you this
variable, as it appears to be used, if you look into what's here you see that
all the attributes from Booking are over here. So I just created in memory a
type variable and basically don't think that you need to use a query to
create in memory a
variable of this kind. Should you also require more than one entity, so
effectively a join, that's okay as well, just change this into say a record,
if you wanted to record list, you will do a
record list and in the record definition rather than just leaving Booking,
so effectively we were doing, very similar to the other one, just the record containing
Booking, if you want we can go into the record editor and say for example that
we want a Booking and a Room and if we look at the scope now, at what's available,
next variable, we actually see that a variable has the two things side by side, ok? So, record
type variables are perfectly normal variables for you to create manually.
Now moving swiftly onwards to a very common doubt that we have when
people are doing this assignment and focusing over here.
if I open the SQL query, you can see that I've got the information copied
over to here and has is in workbook and in the dashboard, and normally people don't have too
much trouble understanding want they should pass for number of adults, number of
check-in date and check-out date, ok? And, let's actually do the assessment letters
to be to be done, and realize that you normal in this particular context you
want to passing the stuff that you passed of the user, so, in the form you've
got to number of adults, in the form as well you got to number of children, in
the form as well you've got the check-in date, again in the forum you have the
check-out date and it's now very common that people go over here and just pass
in the status id, that happens to be in the form. This is the wrong thing to do in this
particular case, because if you remember and think about it, you never ask the
user to type in a status id, it doesn't make sense do so. So, what we have in
here is
a suggestion, which is not appropriate one for the particular scenario that we
have. Let's then have a look at what CanceledStatus really means, so you know
what to pass in. Knowing actually or having knowledge of SQL is not a
requirement for this course, but very very quickly, we're trying to find all
the rooms that can fit two people that we want to put in there, but we want to
remove from that selection any rooms that have booking with conflicting dates,
and here's the important bit and last that booking has been cancelled,
ok? So from this context we can see that the CancelStatus parameter, it
should have the absolute value that the static entity, status or
BookingStatus have when we set a certain booking to be cancelled. So therefore pay
very, very much attention here, what you want as an argument to this is not
this one because it hasn't been filled in at all but we actually want the
absolute id of the CanceledStatus, so therefore if you think about it, like
this, now it makes sense, all the rooms where you can fit the adults and children
and last this room has got a booking that conflicts the dates and last this booking has
been cancelled.
Ok? So this is what you want to pass in. So this takes the opportunity to warn
you about suggestions in dropdowns, be very, very careful about the suggestions
that you take. Normally the stuff that you want is over here. I would say eighty
percent of the time is over here, in this shortlist. However this is exactly what
the name says it's a shortlist of values by which an heuristics
that your development environment
feels, are probably values of the type that that particular parameter
requires. So this means that a lot of times you might find that the actual
value, that you want, that makes sense for this operation, is knocking the shortlist,
therefore you should obviously go into the expression editor and look more
thoroughly at the values that you have. So, again always, by all means, always pay
attention to the suggestions, they save a lot of time in development but be very
very critical and and pay attention if that value that you're passing is
really the one that makes sense for that particular context, ok? So, it's an
accelerator, is not a substitute for our attention or our thinking process. Finally
in closing, just to recap what we need when we actually going to booking a room,
let me just read here the diagram. A booking is effectively made up of
several things and the first few attributes of this booking are
the ones that we can see that we get from
the user in the inputs on that screen, guest, last name, first name, check-in date,
check-out date, so these we grab from the form, ok? And that's why if we look at the
code that is actually creating the booking in the database, actually
the baseline effectively is the booking as it is in the form, because the
majority of stuff is over there. However if you pay attention, if you remember
there's other stuff that you grab when you go into this action by getting
the available rooms, ok? When you get the available rooms, effectively you are able
to retrieve the room and the price let's not worry about this, because this
one is just for visualization purpose, the RoomId and the price associated, the miss
type is here because I played around with the name of the attribute, and the price that's
going to complement that information in our booking. So we already have
the RoomId and the price that we got from that screen action, easily get
from the user, these two we got from the advanced query of available rooms and
finally a lot of people forget, we still need to have a StatusId to close
the whole booking and effectively creating the database. So, don't forget before you
actually do this, you need to seed this final attribute and by definition when
we book a room we start that room in the status booked and that's why we
have this assign here, that's just sets the final item for us have a complete
booking to the status book and after this finally, we have all attributes
taken care off and we can finally create or update booking. Ok? This is very, very
important. If you forget some of these attributes,
if they happened to be foreign keys to references, to other entities, you're
actually going to get an error in runtime, one of those ugly red panels, coming from the
top of the screen, so you probably won't be able to miss those but as for the
other attributes, the ones we should have no constraints in the database like integers,
dates or text, you will end up just inserting in the database an empty
field, which is not what you want, ok? This are specific tips for
the booking creation exercise that we see in our assignment number two.
Ok? And this is basically it for this checkpoint. I hope this clears up a few
things and I see you guys in the next checkpoint. Keep up the good
work and I'll see you around. Take care! Bye bye!