GeneXus for SAP Systems Data Model changes

Official Content
This documentation is valid for:

Let’s create a transaction for countries (called Country): 

image_20221122103854_1_png

Create a country identifier attribute. As you can see, GeneXus assigned it the Id domain.

Create an attribute to store the name of the country, CountryName, which takes the Name domain. 

image_20221122103953_1_png

Save.

Note that just as it did with Customer, GeneXus has automatically created the form to add, edit and delete countries.

image_20221122104032_1_png
 
Let’s go back to the Attraction transaction, so as to assign a country to each attraction. Note that typing the letter C displays the list of attributes existing in the Knowledge Base that begin with this letter.

image_2022112210424_1_png

Select CountryId, and its entire definition is displayed. 

Also, include the CountryName attribute in this transaction. It's useful to see the country´s name when this transaction is executed and a country identifier is selected

Let’s focus on these two attributes included in more than one transaction, in order to find out what role they play in Attraction.

image_20221122104318_1_png


Remember that CountryId is the identifier or key in the Country transaction:

image_20221122104416_1_png

To be more precise,CountryId is the primary key of the Country transaction and when a primary key is included in another transaction, it has the role of a foreign key.

image_2022112210469_1_png

Including an attribute that is a transaction's primary key in another transaction relates them both. This means that when executing the Attraction transaction, for this attribute you will have to enter a value that has been previously recorded through the Country transaction.

image_20221122104916_1_png

Let’s see it at runtime.

Remember to open the tunnel to connect the schema to the HANA database.  

Press the Build All option. 

GeneXus analyzes the impact caused by the new definitions made on the Knowledge Base

image_20221122105430_1_png

and informs you that a new table called Country will be created in the database with the CountryId and CountryName fields. 

In addition, a new table called Attraction will also be created with the fields AttractionId, AttractionName and CountryId.

image_20221122105530_1_png

Note that in the Attraction physical table that GeneXus will create, the CountryName attribute is not included, even though you had included it in the Attraction transaction structure. This happens because the TRANSACTION concept is not the same as the physical TABLE concept. Remember that TRANSACTION is the GX object created in the Knowledge Base to represent an object or actor of reality. By examining it, GeneXus creates a PHYSICAL TABLE in the database, to store the data that will be entered when executing the transaction.

GeneXusForSAPSystems_DataModelChanges_Image14

Not all the attributes included in a transaction structure will later be stored in the physical table created based on this transaction.

GeneXusForSAPSystems_DataModelChanges_Image15

Storing the country´s name in several physical tables would mean storing duplicate data.

GeneXusForSAPSystems_DataModelChanges_Image16

Instead, the country name can be retrieved from a single location where it is stored, in this case, from the country table.

GeneXusForSAPSystems_DataModelChanges_Image17

Go back to the development environment and click on Reorganize. The term Reorganize means to reorganize the database. It refers to the task of making changes to it.

GeneXus creates the programs to change the database and executes them, making any necessary changes. Next, it generates all the programs corresponding to the application itself. For example, for each new transaction that has been defined, programs in the selected programming language to enter, change and delete countries and tourist attractions were generated.

Go to View \ Other Tool Windows \ Launchpad 

image_2022112211057_1_png

Now, there are links to work not only with clients but also with attractions and countries.

Let's add data about some countries. Click on Country and this will open the browser.

image_2022112215253_1_png
Since the CountryId attribute was previously defined as belonging to the Id domain and this domain has the Autonumber property set to True, there's no need to enter a value for the identifier because it will be numbered automatically. Add Brazil, France and China.

And execute the Attraction transaction.

Add the tourist attraction “Louvre Museum”. You don’t have to enter a value for the identifier, so just type the name “Louvre Museum”. Indicate that the Louvre Museum is in France.

If you remember the identifier number of France, enter it.

image_2022112215431_1_png

 

Note that the country´s name can´t be changed from here, because it is displayed only for reading purposes.

Remember that a selection arrow was automatically displayed next to CountryId, providing a list of available countries

The arrow was displayed next to this attribute in particular because, as previously stated, CountryId is a foreign key here.

So, here the user will have to enter a value that has been previously registered as primary key value through the Country transaction... Therefore, GeneXus collaborates by generating and offering a list of countries available.

Now, you will see at runtime how the Country and Attraction transactions check that the values entered for the CountryId attribute are consistent. 

Enter a new attraction, such as the “Pyramids of Egypt”. In the country field, enter the value "4", but an error message is displayed because country number 4 doesn’t exist! 

image_2022112211225_1_png

Check the registered countries and you'll see that only countries 1, 2 and 3 have been entered, but not 4. 

Likewise, if you want to change an attraction that has already been entered and try to replace its country with another one that doesn't exist —4 again— you'll get the same error message:

image_2022112211238_1_png

That is to say, when you enter or change data through transactions, the associated data is automatically checked for consistency. Also, when trying to delete data through transactions, the necessary controls are made to maintain the consistency of the stored data. 

Now, for instance, when trying to delete the country France:

image_2022112215649_1_png

A message will be displayed, informing that the deletion cannot be performed because related data exists in Attraction (remember the Louvre Museum, which belongs to France).

Something very important to take into consideration is that attributes must be named using exactly the same name when they are related to the same concept.

For example, suppose that you type "CountryIdentifier" instead of "CountryId" in the Attraction transaction. They will be different attributes for GeneXus.

GeneXusForSAPSystems_DataModelChanges_Image32 

Therefore, GeneXus won't check whether the value entered in the CountryIdentifier attribute of the Attraction transaction exists in the table Country.

Hence, it won't offer the country selection list in the Attraction transaction.  

Also, it won't be possible to retrieve the name of the corresponding country. Because CountryName is referenced in the Attraction transaction due to the fact that CountryId plays the role of foreign key, retrieving its corresponding CountryName. However, CountryIdentifier is not a foreign key, as it isn’t the primary key of any transaction, so it isn’t possible to retrieve data associated with this attribute.

Let’s represent more features of the travel agency's reality. Another requirement is that every attraction has an associated category to indicate if it is a monument, museum, park, and so on.

GeneXusForSAPSystems_DataModelChanges_Image36
 
Here, the same situation that we saw with countries arises. Create a "Categories" transaction and assign the attractions’ categories.
Let’s do it now. Create the Category transaction with CategoryId and CategoryName.

GeneXusForSAPSystems_DataModelChanges_Image36-5

Now, add the CategoryId and the CategoryName attributes to the Attraction transaction.

GeneXusForSAPSystems_DataModelChanges_Image37

Before trying this at runtime, allow the category not to be set, as you don't know its value at the time of entering the attraction. 
This is done by changing the value of the Nullable property of the CategoryId attribute. Set it to Yes:

GeneXusForSAPSystems_DataModelChanges_Image38

This only makes sense for foreign keys that reference values from another table.

Let’s implement another request of the travel agency: For each attraction, they want to enter its photo. So, in the Attraction transaction, enter an attribute called AttractionPhoto.
It will be of Image type because this type makes it possible to store images. 

GeneXusForSAPSystems_DataModelChanges_Image39

Now, press F5 to apply the changes to the database and programs and run the application.
Note that a new table will be created in the database to store the categories.

image_20221122131841_1_png

Click on Attraction:

image_20221122132049_1_png

The Attraction table has to be converted, which means that the CategoryId and AttractionPhoto attributes will be added.

This element is added to store the file and to give the option to only reference a URL to it.

GeneXusForSAPSystems_DataModelChanges_Image42

Press the REORGANIZE button. 

Run the Category transaction from the Launchpad

image_20221122132253_1_png

Add the categories Museum and Monument.

Execute the Attraction transaction, note that it allows you to enter a category and a photo.

image_20221122132738_1_png

Search for the Louvre Museum, assign the Museum category and a photo to it.

image_2022112213304_1_png

Confirm.

A special feature of SAP HANA makes it possible to use table row storage instead of column storage.

To do so, pay attention to when View/Tables is opened: 

image_20221122133112_1_png

Here, GeneXus shows all the tables of the corresponding database. 

image_20221122133142_1_png

By clicking on one of the tables, you will see that among its properties there's one that is related to the type of storage in a SAP Hana database.  

GeneXusForSAPSystems_DataModelChanges_Image50

If you're trying to create a table and the percentage of transactional operations (that is to say, INSERT, UPDATE and DELETE operations) is very high, using row storage is recommended, because it is the default value. Also, it is how traditional relational DBMSs work.

For SAP Hana, there is an option to use column storage, which is useful when the table being designed has a very high percentage of reading operations (SELECT). 
If this is the case of ATTRACTION, change that value.