Thank you for joining me for this video working with Table Objects for Servoy University I Bob Cusick. In a previous lesson we saw how we can set up database servers and they can be of any type whether it's the bundled database post-grass or a different source like MySQL. We're going to take a look now at how you can work with Table Objects right within the Servoy environment without the need to use external editors. Again it all starts with a database connection. So here I have my test or database and as I roll over it shows me that it's a MySQL database. If I click on it it will show me a list of all of the tables and all of the views that are in that particular database. Right now this test or database has a single table called Table 1. Servoy has built-in tools that will allow me to create tables and create columns. So to view what's in this Table 1, simply right-click it or double-click it and choose Edit View Table. Now here this is showing me that I have three fields. There's a rec ID column of type integer, it's the primary key and it's going to be auto-entered by Servoy, automatic numerical sequence. There's two other fields, first and last, that are both text fields of length 25. You'll notice that both the first and the last fields are orange. This tells me that there's something strange or potentially strange with these field names. In this case first and last are reserved SQL words and that's a bad thing. Because if you were to change the backend database that this table is connected to, say Oracle for example, or SQL Server, there's a chance that the database won't work properly. Now Servoy does not let me change column names. That could be very, very dangerous because there could be other applications outside of Servoy that rely on those field names. However, I know in this case that this is the only application that uses this database. So I'm going to go into my MySQL admin and change these names just to show you how to do it. Here I'm using PHP MyAdmin, a simple and free tool to administer SQL databases with. So I'm going to edit this column called first and I'm going to change the name to first underscore name and then I'm going to click save. Likewise I'm going to edit this column called last and change its to last underscore name and click save. So I've successfully changed the names of these columns. Let's see what's happens over in Servoy. Well in Servoy they didn't change. Well why not? Well because Servoy caches all of the information about all of the databases when it first loads. So if you make a change outside of Servoy then you need to let Servoy know that those tables have changed and it needs to update its information. To do that it's really simple. Simply go to the database connection, right click and say reload tables and data structure from the database. Once you do that if you come back to your table and right click and say edit table review and you'll see indeed that Servoy is refreshed the information that it has about the database. Servoy will also let me create brand new tables. So let's give that a try. Simply come to create table. It'll ask you to give it a name. Let's call this one invoices and click OK. Now by default Servoy creates a single column for you which is an inner drew column. It's the primary key and it's of the sequence type Servoy sequence. Again this will add incremental integer numbers as you add rows. I can also add new columns here. So I'm going to add a new column. I'm going to call this one invoices date and then I can choose the type of data that gets stored. So there's text integer number date time or media. This one's going to be a date time. And when you choose a date time field there's no need to specify the length. And if you notice the allow nulls flag is automatically set to true which means that it's OK for a record to have an empty value. Let's add another column. This one's going to be comment and this time it's going to be a text field. But I want the length to be more than 50 characters. Now the word comment is a reserve sequel word. So Servoy will try to help you from making a mistake. So let's take out comment and put invoice note. That should be OK. And now we'll change the maximum length. So what's the maximum number of characters that I want to enter here. I'm going to enter 255. And you can keep on creating as many columns as you want. Now notice that there's a little asterisk here by the invoices. That means that this is not yet saved. Once you save it, you cannot rename any column and you cannot change the data type. And you can't change any of these properties for allow null, the length, the type, or the name. So before you hit save, make sure everything is the way that you want it to be. Now's the perfect time to talk a little bit more about primary keys. In SQL primary keys are very, very important and you must have a primary key for every table that you use in the Servoy environment. It's how Servoy goes and finds all of the records. Now your keys can be what's called simple keys, which is just a single column or they can be what's called composite keys made up of more than one field. Servoy will support either one of those types. Now in most cases, a simple integer field with sequential numbers is going to be fine. However, if you're going to create solutions that are going to be used in a multi-tenant environment, or you're going to create solutions that are going to require database replication. So maybe you want to duplicate all of the data in a particular database and you want to roll it up into a master database, or you're going to replicate data and synchronize it across multiple sources. And an integer primary key is really not a good idea. What you want to use instead is called a UUID or a unique identifier. It's basically a string of 35 characters, both numbers and letters separated by hyphens. And the reason for the length of the key is so that it can be absolutely unique across every record, every database, every object, every time. So to change this primary key from an integer to UUID, we need to make sure we haven't saved it yet and then come to the type, change it to a text, set the length to 40, you can set it to 50 if you want. And we're also going to change the sequence type. So the sequence type by default is set to a servoy sequence. Again, that's a numerical integer that just increments for every row. You also have the opportunity to use a DB identity. That's in the case where you have set on the back end that the database should automatically increment that key. So servoy then will go ahead and just respect that and not touch that primary key when adding a new row. And the last option is a UUID generator. And that's what we're going to use. This will automatically generate a unique ID every time a new record is created. So once we have that, we're now ready to click save. Once we click save, you'll notice that there are additional options that appear below the list of columns. And as you click on them, you can set these options for each one of these fields. So for our invoice ID because we've specified it as a UUID generator, you must check this checkbox that says UUID. UUIDs are binary objects and they can have a text representation. If you're using UUIDs as your primary key, then you must check this flag. If you're using foreign keys that are also UUIDs, then you must check this flag. So for example, on our invoice, if we had a customer ID that's stored in another table and we want to create a relationship between the invoices and the customers. And that customer ID is a UUID. Well, we need to make sure that we check the UUID flag for that customer ID in this invoices file. There's other options that we can set for each of the columns as well. First of all, the title. Now the title will be the default label when you add it to a form. So if I wanted to call this one invoice date, I can simply type it the way that I want it to appear as the default label. There's also a default format. So I can choose how I want to format this when I drag it out to a form. Since I'm in America, I'm going to use month-day year. The foreign type will help you when you're specifying the relationships which we'll get to in another video. And of course we've talked about the flags, but there's other options as well. So there's auto-enter options. And you have a variety of choices. So here you can say that it's managed by the database. You can use the creation date time of the client. You can use the creation server date time, the modification date time of the client, or the modification server date time. So we're going to say that this is going to be the creation server date time. And this will automatically stamp this data when a new row is created. You could also, for example, on your invoice note, you could use a custom default value that you can just type in. If you specified on your backend database that it should have a default value, you can use a database default. You can also look up a value. So you can look up a value based on a relationship. So auto-enter data, even more than one file away. And finally, you can auto-enter a sequence like we've seen before, either a voice sequence, a DB identity, or the UUID generator. In this case, we're going to enter nothing. There's a couple of more tabs that you should be aware of. The first tab is a validation. And there's a number of validations that you can choose that come right out of the box. One is an email validator. This will ensure that the data that's entered into this field is a valid email address. You have a size validator. If you choose that, it'll say what's the length to ensure that there's a minimum or maximum length. You can also choose a regular expression validator. So if you're good at regular expressions, you can type in your regular expression here. And the contents of the field will be validated against that regular expression before it's saved. And finally, you have a global method validator. So if you have a global method inside of your solution, you can choose to choose one of those. And then it will run every time just before the data is saved, the database, to validate against whatever custom code you have. Likewise there's a conversion tab. So here we have a global method converter and a string serializer. So for a global method converter, there are actually three things that we need to specify. What's the global method to convert the object to the database value? And to do that, you would choose again a global method or you can create a new one. Then what is the converted object type? Is it a text integer number, date time or media or as it is, which is the default? And then what's the global method that you want to convert the database value to this new object? So why would you use these conversions? Well for example, if you wanted to store encrypted credit card data within your database and you want to, your admin user to be able to see what the card number is automatically, then this will do an auto conversion. So it's stored as encrypted data inside of your backend database. But when it comes out and it's viewed on the form, it's viewed in its normal numeric state. There's some other interesting options that we have down here at the bottom, like calculations, aggregations, events and security. I'm going to go ahead and set this back to none. There's another database called example data and there's a number of tables in here. Let's take a look at this table called order details. This is a postgres database. Notice how in-servoy, it doesn't really matter with the backend sources, whether it's coming from MySQL or Postgres or Oracle or whatever your backend data is, is all presented in exactly the same way. Now of course you still have the ability to have as much control as you want using your backend tools, but here in-servoy everything's presented with one single unified interface. So let's take a look at this table. There's only a few columns in here and because these are order details, I want to talk a little bit about calculations. Calculations work for a specific solution. They're not for every single solution. So if you define a calculation in the current solution, which is the servoy sample CRM, it will only run in that context. You can then create a different calculation on the same table for a different solution and it will run a different calculation. Pretty cool. To create a calculation, it's very simple. You select the solution name and click add. You give it a name. We're going to create one that's the extended price. And it's going to return a number and then we want to edit the calculation. So we're going to double click. We've got to save first. So let's save it. Double click it. And now what happens is a couple things happened. Number one, we have a code editor has come up. It's showing us that our calculation is really just a function. That has a return value of one. When you create a calculation, you must have the keyword return because that's theta that's going to get returned from the calculation. If you don't have the word return, then you're not going to get the results that you expect. What we're going to do for our calculation is we're going to come down here and notice how this solution explorer has changed. It's changed to show what's valid within the calculation context. All the stuff that you see here and all the functions that you see here are valid within a calculation context. And not everything is available to you in a calculation context. What we want to do is we want to see what the data providers are. Well, here's all of the data providers that are in this particular table. And notice down here at the end, we have extended price. An extended price is a column, but it's green. That means it's a calculated column where the other columns are gray. And that means that their actual stored columns within the database. So we want our calculation to say if there's a quantity and there's a unit price, give us the result of the quantity times the unit price. But if the quantity is missing or the unit price is missing, then just return nothing. So we're going to say if and then we're going to double click. We're going to say quantity. We're going to put a double ampers stand, which means and unit price. This is saying if both of those are not empty, and we're going to open a curly bracket. And then we're going to say return quantity times and double click unit price. Return quantity times unit price. Else, curly bracket. Return null. Pretty simple. Now we're going to go ahead and save that. So there it is. Now we have what's called an un-stored calculation. An un-stored calculation simply means that this is a virtual column. There is no extended price column in this physical table, but this extended price will function as a virtual column. You can put it onto a form. You can use it in other calculations, but it's not stored in the database. That means you can't sort by it. That means you can't use it in a relationship. But if you wanted to store this, it's very easy to do. We simply add a column. We'll call it price extended. We'll choose the data type. We're going to make this one a number. And click save. Once we do that, if we come back to the calculations tab, you'll notice that from the dropdown, we have price extended, even though we named it extended price, we're going to link it to this column called price extended. Once we link it to a physical column, servoy is going to evaluate the calculation and then write the result of the calculation back to this physical database column called price extended. So now because it's a stored value, we can sort by it, we can use it in relationships and we can use it in aggregations. And aggregations are the next tab over. Again, aggregations run in context of solutions. For example, servoy sample CRM is my selected active solution. So that's what we're creating the aggregate for this order detail table on. We're going to say add and let's just call this one order total. Now, aggregates in servoy are run on the server and servoy is smart enough to know the context in which these aggregates are placed. So we'll always give you the correct amount. If you place this order total through a relationship, let's pretend you set one up between orders and order details so that you can show all the detail items on the order. Now, if you place this order total through the same relationship when you put it at the bottom of all of those items, what will happen is servoy will know that you want to add up only that price extended for only the items that are on this particular invoice. Likewise, you could do something where you created a relationship between customers and invoices and then you had another relationship between invoices and order details. And if you were then to place this order total aggregate onto the customer, then it would show you a total of all the order items for that particular customer. So the key when you're creating aggregates is make sure that you put the aggregate on the lowest level on the thing that you want to count. In this case, we're going to go ahead and hit save so that we can get our order total. And we're going to change it from account to a sum. We have minimum maximum average in sum. We're going to say it's going to be a sum of our physical column called price extended. Or at the next tab down at the bottom is called events. So events again for this particular table, for this particular solution, there are events, think of events like database triggers. And they will run global methods on any of these events on record insert, on record update, on record delete, after record insert, after record update, and after record delete. And you can specify any method. You just double click it and it will let you choose any global method that you have in your solution. Or you can create a new one. Keep in mind, this is in the context of a particular solution. So every solution that you make, even if multiple solutions use the same order details table, you can set up different events for every single separate solution. The final tab down here at the bottom is security. There's going to be a whole separate set of videos on security. But just know that for the groups that you have set up, you can have implicit or explicit permissions to read, insert, update, delete, or track in an audit trail, all the changes made to the data. Well, that's about it for working with table objects in Sirvoy. Thanks for watching.