 Excellent. Thank you. So I'd like to actually sort of start by saying the presentation I've given going to give is on Juniper Nates, which is the last link in the book, and I've prepared like little db fiddles of the example. So if you want to go ahead and play with those on your own, you're not going to interrupt anyone else on the moment. So hopefully that'll give you some time to actually create some really insightful questions as I go along. So what I'm giving this talk today is I've seen people use SQL but sort of aren't actually aware of some of the later features in MariaDB that can actually help with queries. Now the first is going to talk about is common table expressions, which came along in MariaDB 10.2. And what they do, if we look at example, we just got a sample table. We've got this structure here in the middle of a CTE. So we've got the form of width, then we've got the name of the common table expression, and then we'll bracket that in a little bit of SQL to what it is. And so what this does, it actually behaves like a view for the purpose of a transaction. Now for something as simple as this, you know, it's fairly basic. We're from a set of student tests. We've filtered it down to just the SQL test. Now on this own, that's probably not very useful, but in our next example, what we've done is we've created common table expression, the same one. And then we've actually selected to get all the students that actually have the same scores. So T1 equals T2, the same name on those. And because we've actually constructed our common table expression as SQL, we don't need to pin both tables to that name. Where common table expressions really come to a powerful play is with recursive common table expressions. So here we've created a table of bus routes. I've set max recursive iterations to 20, because it's quite easy, I guess, with recursion to put the wrong criteria and you end up going for a very large time. Now the form of a common table expression is a recursive one. You start with recursive. You've got your CTE name. And that's what before is what we use in the results. The first bit of the commentation, the CTE is the anchor data. So this is a select statement that provides the initial data in the table. And I'll get to what that means shortly. This statement determines how many columns the CTE will have. And it will also determine their type and the names, because if you do select Bob as name, then name becomes the column name. Next part of the CTE is a union. There can be more than one union in a row. And what we have is a recursive part of the select. Now the recursive part of a select is normally a join against the initial table name CTE. And what the recursive part of the select is, it says from your initial data, we're going to select more data, hence the join aspect, and add to the CTE. So what the SQL engine underneath does is it keeps on doing of that until it finds no more results. And then you've got a data set. Once you have a data set, like the non recursive CTE, you can do a select from CTE and to get the data that way. So in this example, we're going to say, how do I get, where can I go from New York, given our data above? So the first bit of the select, as I said before, is the anchor. And this says select from the bus routes, where the origin is New York. And if we look at our bus routes before, yeah, there we go. We can see, we can go over a number of places from New York. So what the recursive part of the CTE says, well, okay, we've got bus destination was the CTE name. So where a bus destination can match the bus routes of the origin, that's where we can go. And we add that to the results. So with that, we get all these bus routes, including, yes, buses can go to Sydney from New York, they just get wet. With CTEs, especially recursive CTEs, you need to watch about the casting. If we've got an anchor statement that sort of says select null as the anchor point, and this is some point where you might want to start on a tree to go out that way. Remember, I said the data type is actually derived from the anchor. And so what this will mean is that there's a bar char zero or similar zero length structure. And when you join it against another query, you'll get this never shows up cast down to a bar char zero, which doesn't despite really well. With recursive CTEs, you'll need a cast statement. If you know your data is going to exceed what is the default type for the anchor. In MariaDB 10.5, what was added was the cycle restrict. So if we create our table here, we've just got a from and to, we've got a bunch of values, and there they are. We've got a recursive CTE here, and I'll go through it a bit. We've done something different. We've put the field names at the top, and that's instead of putting the field names in here. It's just an alternate form. It means the same thing. So here we're starting with zero as the depth. That's the beginning. Zero is the distance. One is the form. One is the two. And that's the beginning of the data. We union that with depth plus one. So every time we do a recursive iteration through, we're going to get a higher value of depth. We're getting a distance for arbitrary reasons. We're adding the absolute distance between the numbers and the from and to, and we select this from our T1 table. We're joining it to our CTE, where the from number is in the CTE two number. So whatever's connected to one on the first iteration will become into the CTE, and then again, and again, and again. And what this gets is a fairly large table, and this is only restricted because I did a max recursive level, so it goes 20, which is why there's 20 down the bottom. It's quite easy to get carried away with the CTEs. And so what we've done in MariaDB 10.5 is we've added a keyword cycle restrict. Now cycle restrict includes a list of field names, and that will determine what will stop the CTE from recursing is when those pair of values are the same as what occurred before. So on our running list CTE, we get one, one, two, one, and all the way down. And after forward one, we're repeating what's here. But if we look at here, the fifth row here is 21. Sorry, one, two. I do have dyslexia. So that's the same as this one. So it goes, aha, we're in a cycle, we're breaking out of our CTE. And this way you can do things like I want to find all the ways to this destination. But once you've found that destination, there's no point doing an exhaustive search to come up with the same answer. Now insert returning is also a new feature in DB 10.5. And what this is an extension on is in MariaDB 10.3, there was a delete returning if you wanted to delete the data but have a copy of it. And also in MariaDB 10.5, we added a replace returning, which follows the same syntax as here. And the obvious question to ask here is why would you want to return data that you've just inserted? And the trick is what you're inserting into the rows isn't always every element that you have. I.e. there's columns like auto increment that have values. You can have a column that has a UUID generated. And what's useful to do in those cases if you insert a bunch of names is to have that return to you. And that's what I've done with this statement. We've inserted Bob, Jane, Fred and Harry into the table. And what we've got back is their auto increment values. And this in whatever language you're using, you'd actually treat the insert statement as if it was a select. So you'd have a cursor on it, you'd fetch the results back and you get those values. But what if we've got something different? We've got a unique key on the table. Now what we happen if we do the insert is we get a duplicate key because Bob's already on the table. Now something we might want to do with a statement is we go, well, okay, let's just do that and ignore any duplicate things and see what we get. And what we get back is we get a bunch of IDs. And with IDs on their own, it wouldn't be obvious as to which one got eliminated. So we include the name and here we get back the set of data actually got inserted into the database. And so there's a number of applications for this kind of consistency to make it able to do one query rather than multiple. If you didn't have this, what you'd be doing is you'd be inserting one row at a time and looking at the last ID value or probing backs. So we've made this a lot easier for the programmers here. Another feature that's been around for a while is system version tables in MariaDB 10.3. There is a SQL standard form that looks like this where we add a start and a stop timestamp. We define a period and we call it with system versioning. MariaDB decided to put a simpler form since a lot of those fields were just very verbose. And what we also wanted to do was to hide the timestamps such that it doesn't appear in something like star. So with the second form here, we insert a bunch of data, we select from it, we only see the X column in the tables. Now that doesn't mean the row start and row end aren't there, they are just hidden. If you actually explicitly select from them, you'll get the timestamps of when the data was inserted and when the data got deleted. And by default, that's the maximum value of time. So what this is useful for is if you want to maintain the full history of a table, but most of the time you're just wanting to look at the current values and you don't want to do a lot of coding around, okay, if I delete something, I've got to move it to a different table. This way the deleted starter can still be in the same table. And if we do it delete, all for X equals 5, we insert a couple of values, we do a select, and as we see the default select doesn't show X equals 5 because we deleted it strangely enough. Now that there's an extended syntax that says select for all time every element of this table. So what we see here is that the X equals 5 row appears and we can see when it was added and when it was deleted. Now a number of different variants of that can apply. For instance, if we look at one of the timestamps in there, we can say what did this table look like as of a particular timestamp? And we get those list of values. And there's a syntax flow which is show it for a range of times. So all values that exist at the table between a certain set of times. And we can drop and recreate a versioning on a table. So we drop the versioning, the syntax no longer supported and we can add it back. And this time we use the more explicit version of it there. The other version rather than just timestamps is what we've done is to be able to include NADB transaction IDs in the table. And for some situations this makes more sense than timestamps on that because you can compare to other things including logs and other audible records. The NADB transaction timestamps are global throughout the NADB engine. So if there's other tables with also system versioning you'll be able to see which ones occurred on the exact transaction rather than trying to work out whether a microsecond difference is actually applicable. As we see we can insert a bunch of values and if they're in the same transaction they'll get the same transaction start ID. And for the rest of the point the syntax is fairly similar. We can select what a table looks like as of a particular transaction ID. We can make these transaction IDs invisible by including that invisible columns directive when we're creating the table. And here when we do a select star we won't actually have the NADB transaction IDs ever like before they do exist. A common case for what you'd want to do when actually having a history is to actually petition it off so you've got a separate historical petition and a record of the current one. And this means if you're doing a lot of current queries you're not actually trawling through historical records in the background which makes it slower to retrieve the current records. So petition off gives that separation. It also enables you to delete the history and obviously there must be one current petition. If we've got a number of history petitions we can impose a limit on them. So here at your history we'll fill up petition zero and then we'll fill up petition one. It's quite silly just to stop it there when petition one gets full so that'll start to issue a warning when petition one is actually full. Now the way to petition data is to petition it by the time on an interval of months and say 12 petitions. And there's more details on the knowledge base on this and this is the kind that you'd use for rolling over data. MariaDB in 10.5 actually added the INET-D data type, INET-6 and it obviously can store INET-4 as well. I probably should have included more examples of this but you just treat that as a normal type. You insert normal strings into it and what you'll get is an optimized storage of INET-6 data. There's some constraints around it. You can't just cast it to a decimal type. It won't work. However it's got full equivalence to something like VAR Binary 16 which you might have been using already for IPv6 types. So you can select INET-2 to T2, the same data was T1. We look at it and it's the same representation of IPv6 addresses. We can do a join on it and it will treat the VAR Binary and the INET-6 data type as the same. If we do here, we're looking at what it does with values. Here we do collace. If A is null, I will actually define the data type which is INET-6 but otherwise it will return V. What it does here is it returns the IP address because the first entry clearly translates but the second one isn't actually an invalid IP address so it throws an error and shows up in the warnings. Another feature that was added in MariaDB 10.4 was application time periods. We've got a bunch of data here. It took from in two times. The period on itself becomes useful when we say we want to delete a portion of a data. What that does is it clearly cuts and slices the data of those rows. Row A wasn't actually within time period so it's entirely unchanged. Row B had a bunch of data above 2001. What it does is it's truncated the top end and it's also put a bottom end, a new row in for what was actually after 2008. Table C had a bunch of data up until 2017 so it was truncated down to its 2001 time. Table D had a start period in 2001 and they're for a cutoff as well. There's a number of ways you can actually use that. You can also, I guess, not only delete, you can do an update and that does a similar sort of thing. It replaces the data originally with a new entry. A most useful thing if you're ever doing bookings in a hotel or reservations or anything is with your time periods. By default, if you do this instant treatment as it is, there's no checks for overlaps. Here we see that the last visitor actually overlaps with the second visitor when they did the bookings. Here we add a unique constraint if I get it right, of the room number and the period without overlaps. What happens here if we try to actually insert a duplicate overlap, it will actually give us a duplicate key error. This saves us doing a lot of application code to do the same thing. That's all the questions I actually wanted to or syntax as I want to present today. There's probably a very little amount of time for questions but you're welcome to ask them. Hi, Mr. Daniel. Thank you so much for speech just now. We are a bit short on time. We have one minutes left for Q&A. Does anyone want to quickly voice out their questions or quickly type them out in the public chat? Well, I'll be on a short time afterwards for the discuss but otherwise, please welcome Jacinto on this next talk and thank you for hosting me here.