GS_05_Create_multiple_results - XLRIT/gears GitHub Wiki

From this moment on you will transform you first working app to something that is more like a profesional application that you could actually find in real life. For instance, in real life most webshops enable customers to:

  • decide how many products they would like to purchase and also
  • buy different products in one go
  • choose them from a product catelogue (instead of typing the name)

Let's make these changes now:

1. Table of content

2. Use proper names and add key to identify processes

Of course it is good practice to give good names. And that includes the names of the process. So if the functionality of the process is going to change (going from ordering just one product to multiple products at once), you should also change the title of the process:

  1. Rename the file Order product.sn to Order products.sn
  2. In this file replace process 'Order product' to process 'Order products'

While we ar at it, we should give unique identifiers to each process called a key. This key can be used to refer to a process from another location. We'll use that unique key in the next part of this getting started. But for now, let's simply add this key:

  1. In file Order products.sn to the right of text process 'Order products' add the following text: with key sales.products.order.

It is common practice to add keys to each process. A typical key starts with a category (sales) then the most relevant entity/collection (products), then the most relevant action this process is about (order).

3. Quantity is input from

This is going to be easy.

  1. In file Order products.sn simply change the following line
      quantity     = 1

to

      quantity     = input from CUSTOME

Did you notice that I made a copy paste error? This is on purpose. Let's simply continue with this error.

  1. Do a GEARS:Generate followed by a GEARS:Show Diagrams. The diagram of the process should look a bit like this:

As you can see the generated process now has 2 tasks. One for the CUSTOMER and the other for a new role called CUSTOME. Of course this was not our real intention but it does show that GEARS is able to create business processes not just for one role, but for as many roles as is needed in that business process. You also see that it takes only little effort to do that. As a matter of fact this new system could now be build and executed and would work just fine.

As a matter of fact it cannot even be considered an error. At least not by GEARS. The requirements clearly stated that a person with the new role CUSTOME should play a part in this process and GEARS has created a business process that matches with those requirements.

At best this can be called a mistake in the requirements made by either the requirements analist (you) or by the customer that mised this part in his/her requirements. Or it could not even be a mistake but a deliberate an correct part of the requirements.

Of course if you think about it logically, it was simply a copy paste error and we should fix that:

  1. Put the R after CUSTOME and do a GEARS:Generate followed by a GEARS:Show Diagrams. The result should look now look like this:

As you can see the process now starts with just one role that simply has to enter both a product name as well as a quantity.

Can you guess what this first form will look like? You can try it out if you like by doing a GEARS:Run Application, a GEARS:Load data and then try out the application as you did in GS_03_Create_first_working_app.

Note that if you already have an active GEARS:Run Application task, you need to first kill it with the little trash icon. I will not explicitly repeat that you need to do that, so please remember to first kill the GEARS:Run Application task before you restart it!!

4. Create multiple

Of course most web shops enable a user to choose more then one product and their quantity at once. Let's change the required end result to match that requirement.

  1. In file Order product.sn simply change the following line:
    one ORDER in ORDERS is created with:

to

    multiple ORDER in ORDERS are created with:
  1. Do a GEARS:Generate followed by a GEARS:Show Diagrams. The result should look now look like this:

In the diagram there are only small differences versus the previous diagram. Notice the extra ORDER*:? This indicates multiple ORDER(s) will be created instead of just one. The effect on the form is more profound:

  1. Do a GEARS:Run Application, a GEARS:Load data and then try out the application as you did in GS_03_Create_first_working_app. Try to order 3 different types of products with different quantities. The form should then look a bit like this:

GEARS has added all sorts of standard functionality for you such as adding a line (red arrow), copy a line including the values you already entered in it (blue arrow) and deleting a line (green arrow). With the check boxes you can select multiple lines. Copy and Delete will appear so you can copy/delete all selected lines at once. Besides that standard validations are added to see if required fields are entered and if they are entered with the right values.

  1. Click Submit and check the content of the database as you did in GS_03_Create_first_working_app. The result should look a bit like this:
ID_ CREATED_AT_ MODIFIED_AT_ PRODUCT_NAME_ QUANTITY_ CREATOR_ID_ MODIFIER_ID_
01G8...2FA 2022-07-24 14:20:15.689229 2022-07-24 14:20:15.710726 Tesla Model 3 2 demo demo
01G8...ZPD 2022-07-24 14:20:15.690722 2022-07-24 14:20:15.711232 Gaming PC 5 demo demo
01G8...S74 2022-07-24 14:20:15.691222 2022-07-24 14:20:15.711724 A really big house 1 demo demo

5. Add relations between entities

Now this work all fine, but in real life there is only one order in which you can buy different products with varying quantities at once. And if you think about that you quickly understand why: for instance how about adding the delivery address. Would you want to repeat that for each ordered product? No of course not, you would want to enter that only once. Of course you can do that and you do that by first changing the LDM to accomodate the relation between the entity that holds the delivery address (the ORDER) and the entity that holds the ordered products (which we will call, euuuuuhhhh, let's call that entity ORDER_LINES).

Before we start I must say that this is an important moment in your skills as a logical data modeller, so please make sure you understand what will happen next.

  1. In file LDM.sn replace the content with this:
reusable definitions

ORDER in ORDERS =
    delivery_address : text
    LINES            : multiple ORDER_LINE

ORDER_LINE in ORDER_LINES =
    product_name : text
    quantity     : number

Here is the explanation of the things that have changed:

Line Explanation
delivery_address : text This is easy, you simply added a new attribute of type text to hold the delivery address
LINES : multiple ORDER_LINE This is where you added your first relational attribute. You give it a name, in this case LINES. It is a name in plural which already suggests that one ORDER will have multiple LINE(s). Using plural for attributes that refer to multiple something is good practise. Then after the colon (:) you start by saying exactly that: that one ORDER has multiple LINE(s). Followed by the entity type. In this case that is always the single version of the entity definition this attribute is referring to.
ORDER_LINE in ORDER_LINES = This is defining a new entity as you did before with ORDERS. As you guessed it the single name is called the Entity Type and is used when you refer to it from relational attributes. The plural name is called the Entity Collection and is used when you create new elements in that collection with one ELEMENT is created in COLLECTION or with multiple ELEMENT are created in COLLECTION (as you will see later in this getting started).
  1. In file Order product.sn replace the content with this:
process 'Order product'

results in:
    "customer has ordered products"

"customer has ordered products" =
    one ORDER in ORDERS is created with:
        delivery_address = input from CUSTOMER
        LINES            = "multiple order lines"

"multiple order lines" =
    multiple LINE in ORDER_LINES are created with:
        product_name = input from CUSTOMER
        quantity     = input from CUSTOMER

Here is the explanation of the things that have changed:

Line Explanation
"customer has ordered products" You simply renamed the sentence to make it more correct. Such a minor change may seem silly but having good titles and names is more important than you may realize now, so let's make sure we use proper titles that actually match the detailed specification.
"customer has ordered products" = Same change but then for the sentence definition
one ORDER in ORDERS is created with: You simply changed it back from creating multiple ORDER(s) to creating just one.
delivery_address = input from CUSTOMER You added attribute delivery_address and who will fill this in.
LINES = "multiple order lines" You added attribute LINES and what it will be filled with. This is first example of using a sentence directly to define what an attribute is filled with.
"multiple order lines" = The start of the definition of that new sentence.
multiple LINE in ORDER_LINES are created with: You did this before with ORDERS so perhaps you guess what this line specifies: With this line you specify the creation of multiple elements called LINE in the collection of ORDER_LINES.
  1. Do a GEARS:Generate, GEARS:Run Application, GEARS:Load data and try out your new application by ordering basically the same as you did before. This is how a filled in form could look like:

Pretty straight forward right. Now let's take a look at the database, because there things have happened that you may not have expected:

  1. Click Submit and check the content of the database as you did in GS_03_Create_first_working_app.

  2. In the database in the left pane click on the little plus sign of each table that starts with GS_. There should be 3 and each should look like this:

Why 3 tables you wonder? Well this is because you only defined a relational attribute from ORDERS to ORDER_LINES but not the other way around. GEARS therefore does not know if one ORDER_LINE always has just one ORDER or multiple ORDERS. To be safe, GEARS assumes it could also be multiple and therefore creates an extra table called GS_ORDER_LINE that enables what we call a many to many relationship. Let's take a look at the content of all tables but especially that extra table.

  1. Do a SELECT * FROM GS_ORDER_LINE to show the content of that extra table. It should look a bit like this:
ORDER_ID_ LINES_ID_
01G8R64W80D38PQ8EAA46140VQ 01G8R64W8GTREP8SDSAHBQN2VV
01G8R64W80D38PQ8EAA46140VQ 01G8R64W8HNHFCXR5QBV089WDE
01G8R64W80D38PQ8EAA46140VQ 01G8R64W8HYGXWH6EAFXHTTB8P

Both colums of this table contain what we call technical identifiers (ID's) to a ORDER(s) and (ORDER)LINES. All the ID's in the ORDER_ID_ column are the same. That is because you only created one order. There are however 3 distinctly different ID's in column LINES_ID_ because you created 3 order lines. Basically this table simply links both of them together.

However, if you think about it, one order line can of course only be part of one order, and not multiple orders. Let's resolve that:

  1. In LDM.sn add an extra relational attribute to ORDER_LINES that looks like this:
    ORDER        : ORDER optional

You just added a relational attribute from ORDER_LINES to ORDER. Let's see what happens when you generate this. 8. Do a GEARS:Generate and a GEARS:Show Diagrams and take a look at the Project Diagrams. Do you remember from GS_03_Create_first_working_app that the entities you specifiy are blue? They should now look like this:

Why are there 2 relationships? One is many-to-many (indicated by a crows foot at both ends of the line) and the other one is a one-to-many (indicated by a crows foot only at the "many" ending of the line). The reason is that although GEARS knows that there are 2 relational attributes, it does not know that these relational attributes are each others opposites.

You may say: "Why not simply assume that they are each others opposite?". That is because in real life there could be relations between entities and there is no way to safely assume those relationships are each others opposite. Say for instance relationships between PERSONS. One could be CHILDREN, another could be NEPHEWS_OR_NIECES. Those are definitly not each others opposite because that would be PARENTS and UNCLES_OR_AUNTS. That is why it is basically always a good idea to define which relational attributes are ech others opposites. Let's doe that now.

  1. In LDM.sn to the right of LINES : multiple ORDER_LINE add the text opposite of ORDER
  2. And replace the text optional (which we don't need anyway) with opposite of LINES
  3. Do a GEARS:Generate and a GEARS:Show Diagrams and take a look at the Project Diagrams. You entities should look like this:

  1. Kill the application that is still running (quick reminder: click the trash for the Run application - Task)
  2. Do a GEARS:Run Application a GEARS:Load data and submit the same order as before. Check the result in the database and notice the differences in the tables that are created.

As any good database designer knows, for a one-to-many relationship you now only need 2 tables (and not 3 as were created earlier). GEARS is a pretty good database designer has therefore created only 2 GS_ tables with just one reference from ORDER_LINE to ORDER called ORDER_ID_. You can see that reference in when reading the GS_ORDER_LINE with a SELECT statement. It should a bit like this:

ID_ CREATED_AT_ MODIFIED_AT_ PRODUCT_NAME_ QUANTITY_ CREATOR_ID_ MODIFIER_ID_ ORDER_ID_
01G...9NS 2022-07-24 16:32:37.508201 2022-07-24 16:32:37.531694 Tesla Model 3 2 demo demo 01G...102
01G...Z6H 2022-07-24 16:32:37.509196 2022-07-24 16:32:37.532195 Gaming PC 5 demo demo 01G...102
01G...ZBG 2022-07-24 16:32:37.5097 2022-07-24 16:32:37.532195 A really big house 1 demo demo 01G...102

6. Add relational attribute to PRODUCTS

This is one of the last changes in the requirements that will make this application more real life like and that is that in normal webshops you would never let the customer type in the name of the product, but rather let the customer search for a product in a list of products. Besides that, you would definitly want have a price for each product and not want the customer to decide what that price would be. Let's add those last requirements to your app.

  1. In LDM.sn change the following:
    product_name : text

to

    PRODUCT      : PRODUCT opposite of ORDER_LINE
  1. At the end of LDM.sn add the following new entity:
PRODUCT in PRODUCTS = 
    name       : text
    price      : number(10,2)
    ORDER_LINE : multiple ORDER_LINE opposite of PRODUCT
  1. In Order product.sn simply change product_name to PRODUCT.
  2. Do a GEARS:Generat, a GEARS:Show Diagrams and take a look at the Project Diagrams. Your entities should look like this:

Basically you have replaced the normal attribute product_name with a relational attribute that is filled with a new entity called PRODUCTS. In the diagram you can also see that this has resulted in a many-to-one relationship from ORDER_LINE to PRODUCT. Besides that, each PRODUCT has a name and a price.

This is all stuff you have done before. Except for the attribute price. Its specification ends with (10,2). This defines the total length and precision of the price. In this example the total length is 10 digits and 2 of them are used for the decimal part of this value. This means the maximum value of this price will be 99,999,999.99. Let's hope that is enough.

Why not use a variable number of decimals such is often used in spreadsheet applications such as Excel? Well, one of the reasons is performance. Excel can only handle a maximum of 1 million rows per table and if you do that Excel becomes really slow. Professional applications need to be able to handle millions and millions of rows and do high performance calculations on them. One of the ways to make that possible is to use fixed length decimal values and not variable length decimal values. Besides that, in financial systems it is even required to use fixed length decimal values in financial calculations to produce correct results.

7. Fill database with test data (e.g. PRODUCTS data)

One big problem is that we do not yet have a list of products to choose from. There are several ways to solve that but let's start by simply adding them to the database.

  1. Do a GEARS:Run Application, followed by a GEARS:Load data and take a look at the resulting database.
  2. Execute the SQL statement SELECT * FROM GS_PRODUCT. The result should look like this:

This means our list of products to choose from is empty. Notice the Edit button.

  1. Click on the Edit button and click on the little green ➕ icon to add rows (a.k.a. "records"). Then fill each row in as in the screen below and click ✔️ to save each row:

You have probably noticed the CREATED_AT_, MODIFIED_AT_, CREATOR_ID_ and MODIFIER_ID_ columns before. These are added by GEARS by default and hold information on who created/modifies records and when that happens. These are automatically filled by GEARS so we do not need to worry about them. You can simply fill them with null which represents these optional fields are not filled in (yet).

  1. Let's go back to the application and try to fill in the form for the process Order product. You will notice that things look a bit different now:

You can still enter the name of a product to search for it, but you can only choose from the list of products that are in the database. Each product also shows the price.

  1. Order at least 2 products and submit the form.
  2. Investigate the database to see the result. The most profound change should be in the table GS_ORDER_LINE. It should look a bit like this:
ID_ CREATED_AT_ MODIFIED_AT_ QUANTITY_ CREATOR_ID_ MODIFIER_ID_ ORDER_ID_ PRODUCT_ID_
01G...1TW 2022-07-24 18:39:27.163448 2022-07-24 18:39:27.194447 2 demo demo 01G...06W product1
01G...N2R 2022-07-24 18:39:27.164444 2022-07-24 18:39:27.194942 1 demo demo 01G...06W product3

All references are technically resolve by your GEARS create app using ID's. In this example the right column called PRODUCT_ID_ is filled with the ID of the chosen products and not with their names.

Now you've got one problem you did not think about yet. The database is an "in memory database". This is great for developing and testing, but it also means the data inside is not stored permenantly. The moment you kill the Run Application - Task the database is gone, and so it the data inside. To resolve this we need to create a(t least one) file to contain the data we need. Let's create such a file:

  1. In folder data create a new file called 02_test_data.sql and fill it with this content:
INSERT INTO GS_PRODUCT
(ID_                  ,NAME_               ,PRICE_) VALUES
('product1'           ,'Tesla Model 3'     ,54000.00  )   ,
('product2'           ,'Gaming PC'         ,650.45    )   ,
('product3'           ,'A really big house',2500450.00);
  1. Kill the Run Application - Task.
  2. Do a GEARS:Run Application and GEARS:Load data.

In the terminal pane you should get a message like this:

Loading data files '**' from C:\git\demo_cases\getting-started\data...
- Loading 01_users_and_roles.sql
- Loading 02_test_data.sql

Executed 2 scenarios with 0 failures in 2,0 seconds

You just succesfully added those products in the product list. Killing the application is not a problem, you can simply restart it, load the data again and order all sorts of things (as long as they are in the product list of course 😉).

8. Tidy up and commit/sync your work

Same as you did at the end of GS_03_Create_first_working_app but in short:

  1. Kill all tasks
  2. Give your work a good commit message
  3. Click the blue Commit button
  4. Click the blue Sync Changes button.

9. Do it yourself

One of the special things this school does is to provide the options for a student to enlist in special courses at school. These are extra courses a student can do to prevent the student from becoming bored. Here is the situation and you assignment:

9.1. Situation: Enlist in special courses

Some students need extra work to prevent them from getting bored. It was therefore decided that the system should enable parents to enlist their son or daugther (the student) into one or more of these extra courses. Here is a list of those special courses:

  • Astronomy
  • Chinese
  • Manga Script Writing
  • Photography
  • Robotic warfare
  • Hack your parents WiFi

9.2. Assignment: new process for parent to enlist student into special courses

This is not like the order products case in where you improved an existing process. In this assignment you are going to develop a new process that enable a parent to enlist a student into special courses. That suggest you have to create a new .sn file with a new result. But is does have similarities. Instead of an order, your create one ENLISTMENT in where the PARENT will have to choose the student for which this ENLISTMENT is. Next to that the PARENT will have to choose multiple COURSES from a list of COURSES. For each COURSE the PARENT should also choose at what date the COURSE can start.

Go ahead an create this new process.