Application Development using LogicBlox

Table of Contents

1. Software prerequisites for local development

1. Lesson 1 - Hello World!

To start with your exercises, go to the directory <TrainingDir>/helloworld/lesson1:

% cd <TrainingDir>/helloworld/lesson1

You can find the base source code needed to begin your exercise in the following files:

lesson 1/ 
  helloworld.logic 
  default_greeting.logic 
  helloworld.sh

containing:

the declarations (=data model) of the predicates greetings, person_speaks, person_hasGreeting and default_greeting.
the logic to compute person_hasGreeting based on the language the person speaks and a rule that states that "English" is the language of the default greeting.
some data for the greetings and person_speaks predicates.

contains a rule that computes the greeting for a person who speaks a language for which we have no greeting defined. When no greeting is available for that language, the default greeting is used. Note that in the exercises, you will be asked to modify the way we compute this predicate. Having this rule in a separate file allows us to easily exclude it in favor of your own, by not installing it during the build.

our build script. It creates a workspace helloworld. If there is a workspace already with the same name, it will be deleted because of the --overwrite argument. It also installs helloworld.logic and default_greeting.logic, and runs some queries.

You can run helloworld.sh by running the following command:

% sh helloworld.sh

You should see the following text printed in your terminal:

created workspace 'helloworld' 
add block helloworld.logic to workspace helloworld
added block 'helloworld' 
add block default_greeting.logic to workspace helloworld
added block 'default_greeting' 
Print content of person_hasGreeting 
"Guido van Rossum" "Hello World"
"Lisa"             "Hallo Welt" 
"Paco"             "Hola Mundo" 
"Sarah"            "Hello World"
Print greeting for Sarah 
/--------------- _ ---------------\
"Hello World"
\--------------- _ ---------------/
Print greeting for Lisa 
/--------------- _ ---------------\
"Hallo Welt"
\--------------- _ ---------------/

	the result of the `lb create --overwrite` command.
	the result of the `lb addblock` command which we use to add the file `helloworld.logic` to the workspace.
	the result of the `lb addblock` command which we use to add the file `default_greeting.logic` to the workspace.
	the result of the command below, that prints the content of the predicate `person_hasGreeting`: % lb print helloworld person_hasGreeting
	the result of the query below, that queries all the `greetings` for Sarah: % lb query helloworld ' _(greeting) <- person_hasGreeting("Sarah", greeting).'
	the result of the query below, that queries all the `greetings` for Lisa: % lb query helloworld ' _(greeting) <- person_hasGreeting("Lisa", greeting).'

Now that you are familiar with the file structure for this lesson and know how to build your application, you are ready to get started with the exercises! For the remainder of this lesson we assume that you are in the lesson1 folder.

1.1. Exercise 1: Personalized Farewells

Let us get started by creating a new logic file called exercise1.logic.

Can you edit the build script helloworld.sh to add your new logic file to the helloworld workspace? You can also delete the queries related to the person_hasGreeting predicate from the script. If you performed the required modifications in helloworld.sh correctly, your helloworld.sh should look like this:

#!/bin/bash

set -e

lb create --overwrite helloworld

lb addblock helloworld -f ../../helloworld.logic
lb addblock helloworld -f ../../default_greeting.logic

#your script should use file exercise1.logic instead of exercise1_1.logic
lb addblock helloworld -f exercise1_1.logic

In the sample logic in helloworld.logic we have defined greetings and a default greeting for people. In this exercise, you will have to define farewells and a default farewell message.

Creating farewell messages: Let's start by defining the farewells predicate: In exercise1.logic, model predicate farewells as a relation between string(language) and string(farewell_message).

Also in exercise1.logic, define the following data to the farewells predicate:

language	farewell
"English"	"Goodbye World"
"German"	"Auf Wiedersehen Welt"
"Spanish"	"Adios Mundo"

Add the lb print command at the end of your build script to check that you have the expected data in farewells:

$ lb print helloworld farewells

Once you have written your logic and added the lb print command, run your build script:

$ sh helloworld.sh

If you have successfully added your logic, you should see the following message:

created workspace 'helloworld'
added block 'helloworld'
added block 'default_greeting'
added block 'exercise1_1'
Content of predicate farewell:
"English" "Goodbye World"       
"German"  "Auf Wiedersehen Welt"
"Spanish" "Adios Mundo"

Did you get the same result? If not, you might need to look at your logic again.

Personalized farewell messages: Now that we have created some farewell messages, let's define the farewell message for each person.
- In exercise1.logic, model the predicate person_hasFarewell as a relation between string(name) and string(farewell).
- To make sure that there is always a farewell message associated with a person, we should define a default farewell, similarly to our default greeting: Also in exercise1.logic, model the predicate default_farewell as string(farewell).
- Write a rule that defines the default_farewell message to be the "German" version (because it's more fun to say!), as stored in the predicate farewells.
- Let's now define the default farewell message for a person: Create a file default_farewell.logic. In this file, write a rule that defines the predicate person_hasFarewell as follows:
  - If there is a farewell message for the language spoken by a person (as in farewells), let the person_hasFarewell for that person be the farewell message for that person's spoken language.
  - If there is not a farewell message for the language spoken by a person, then use default_farewell as the farewell message for that person.
  Tip
  
  you will need to use the predicate person_speaks in this rule. The predicate person_speaks is modelled as a relation between a string(name) and a string(language). You can have a look at the declaration of this predicate in helloworld.logic.
- Modify helloworld.sh to also add your newly created logic file default_farewell.logic to the workspace helloworld.
Adding data: Let's define some additional people and their spoken language: Add yourself and your native language to the person_speaks predicate. You should make these changes in exercise1.logic
Running it:
- Add the lb print command at the end of your build script to check that you have the expected data in person_hasFarewell, showing for each person a farewell message.
- Add your logic to the helloworld workspace by running the helloworld.sh script again:
```
$ sh helloworld.sh
```
- Do you see a farewell message for Guido van Rossum? Is it "Auf Wiedersehen Welt"? If not, you need to check your logic again.
- What about for yourself? Can you use lb exec to query for the farewell message for yourself? Is it in the language you expected?

You can find the solution to the exercises above here.

1.2. Answers to Exercise 1: Personalized Farewells

Your exercise1.logic file should look now similar to the example below:

// predicate containing farewell messages
farewells(language, content) -> string(language), string(content).

// data for farewells
farewells("English", "Goodbye World").
farewells("German", "Auf Wiedersehen Welt").
farewells("Spanish", "Adios Mundo").

Your exercise1.logic file should look now similar to the example below:

// predicate containing farewell messages
farewells(language, content) -> string(language), string(content).

// personalized farewell messages (exercise 1.2)
person_hasFarewell(name, farewell) -> string(name), string(farewell).

// data for farewells
farewells("English", "Goodbye World").
farewells("German", "Auf Wiedersehen Welt").
farewells("Spanish", "Adios Mundo").

// default farewell is German (exercise 1.2)
default_farewell(farewell) -> string(farewell).
default_farewell(farewell)
  <- farewells("German", farewell).

Your exercise1_default_farewell.logic file should contain a rule that looks similar to the following:

// personalized farewell computation
person_hasFarewell(name, farewell)
  <- person_speaks(name, language),
     farewells(language, farewell).

person_hasFarewell(name, farewell)
  <- person_speaks(name, language),
     !farewells(language, _),
     default_farewell(farewell).

Your exercise1.logic file should look now similar to the example below, except that it should also include you in the data, together with Paco:

// predicate containing farewell messages
farewells(language, content) -> string(language), string(content).

// personalized farewell messages (exercise 1.2)
person_hasFarewell(name, farewell) -> string(name), string(farewell).

// data for farewells
farewells("English", "Goodbye World").
farewells("German", "Auf Wiedersehen Welt").
farewells("Spanish", "Adios Mundo").

// default farewell is German (exercise 1.2)
default_farewell(farewell) -> string(farewell).
default_farewell(farewell)
  <- farewells("German", farewell).

// Paco speaks Spanish, I speak French (exercise 1.3)
person_speaks("Paco", "Spanish").
person_speaks("I", "French").

Your build script should create the following output:

created workspace 'helloworld'
added block 'helloworld'
added block 'default_greeting'
added block 'exercise1_3'
added block 'exercise1_2_default_farewell'
Content of predicate person_hasFarewell:
"Guido van Rossum" "Auf Wiedersehen Welt"
"I"                "Auf Wiedersehen Welt"
"Lisa"             "Auf Wiedersehen Welt"
"Paco"             "Adios Mundo"         
"Sarah"            "Goodbye World"

1.3. Exercise 2: Personalizing Messages using Friends

In the following exercises, we are going to introduce the friends relationship. We will make changes to the way we derive the default greetings and farewells for a person, based on the friends a person has. As it is likely that someone speaks a certain language, if his friends speak that language, don't you think?

Let's first create a new file called exercise2.logic. Modify the build script helloworld.sh by adding the new logic file to the helloworld workspace, after importing the logic that you have written in exercise 1.

You can delete the commands that add default_farewell.logic and default_greeting.logic to the workspace, as in this exercise you will write new rules to derive the farewell and greetings for a person.

Question: Why do you think we need to leave these logic files out? Why can't we just add new rules?

After these modifications, your build script should look like this:

#!/bin/bash

set -e

lb create --overwrite helloworld

lb addblock helloworld -f ../../helloworld.logic

#your script should use file exercise1.logic instead of exercise1_3.logic
lb addblock helloworld -f ../exercise1/exercise1_3.logic

#your script should use file exercise2.logic instead of exercise2_1.logic
lb addblock helloworld -f exercise2_1.logic

Creating friends:
- First you should define the relationship between 2 people: In exercise2.logic, model predicate friends as a relation between string(name1) and string(name2), where name1 and name2 refer to the names of people who are friends.
- Add data by making yourself a friend of "Guido van Rossum".
Determine likely languages:
- Next you should try to define the languages that a person likely speaks. In exercise2.logic, model predicate person_likelySpeaks as a relation between string(name) and string(language), indicating the language a person is likely to speak.
- Write a rule that defines the language a person likely speaks as follows: a person_likelySpeaks a language if his/her friend speaks that language (as determined using person_speaks).
  
  Tip
  
  You can look up the declaration of the person_speaks predicate in helloworld.logic.
- Run the helloworld.sh script to add the changes you have just made to the workspace.
- Does person_likelySpeaks tell you what language "Guido van Rossum" likely speaks? Does it match the language you speak? If not, can you think of a reason why?
  
  Tip
  
  Have a look at the two arguments that the friends relation takes.
Further Personalizing Greetings and Farewells:
- Let's make some modifications to the way we derive the farewells and greetings for a person. If we know that a person likely speaks a certain language, then we rather want to display the farewells and greetings for him/her in that language and use the default values only in exceptional circumstances: Modify the logic defining the predicates person_hasGreeting and person_hasFarewell in exercise2.logic such that default_greeting and default_farewell are only used if:
  - a person speaks a language that we have no greetings or farewells for, and we also have no person_likelySpeaks information for that person.
  - a person likely speaks a language that we have no greetings or farewells for, and we also have no person_speaks information for that person.
- Add the program to the helloworld workspace by running helloworld.sh script.
Querying our new predicates:
- Write a query that prints the predicates person_hasGreeting and person_hasFarewell for "Guido van Rossum".
- Now that he's friends with you, do his greeting and farewell messages match yours?
- How many greetings and farewell messages do you see for "Guido van Rossum"? If you see more than one for each greetings and farewells, then you need to check your logic to see why. That's not supposed to happen :)

You can find the solution to the exercises above here.

1.4. Answers to Exercise 2: Personalizing Messages using Friends

Your exercise2.logic file should look now similar to the example below:

// friends predicate (exercise 2.1)
friends(name1, name2) -> string(name1), string(name2).

friends("Guido van Rossum", "Sarah").

Your exercise2.logic file should look now similar to the example below:

// friends predicate (exercise 2.1)
friends(name1, name2) -> string(name1), string(name2).

friends("Guido van Rossum", "Sarah").

// likely language computation (exercise 2.2)
person_likelySpeaks(name, language) -> string(name), string(language).

person_likelySpeaks(name, language)
  <- friends(name, friend),
     person_speaks(friend, language).

Your exercise2.logic file should look now similar to the example below:

// friends predicate (exercise 2.1)
friends(name1, name2) -> string(name1), string(name2).

friends("Guido van Rossum", "Sarah").

// likely language computation (exercise 2.2)
person_likelySpeaks(name, language) -> string(name), string(language).

person_likelySpeaks(name, language)
  <- friends(name, friend),
     person_speaks(friend, language).

// modified greetings using likelySpeaks information (exercise 2.3)
person_hasGreeting(name, greeting)
  <- ( person_speaks(name, language)
     ; person_likelySpeaks(name, language) ),
     greetings(language, greeting).

person_hasGreeting(name, greeting)
  <- person_speaks(name, language), 
     !person_likelySpeaks(name, _),
     !greetings(language, _),
     default_greeting(greeting).

person_hasGreeting(name, greeting)
  <- !person_speaks(name, _),
     person_likelySpeaks(name, language),
     !greetings(language, _),
     default_greeting(greeting).

// modified farewell using likelySpeaks information (exercise 2.3)
person_hasFarewell(name, farewell)
  <- ( person_speaks(name, language)
     ; person_likelySpeaks(name, language)),
     farewells(language, farewell).

person_hasFarewell(name, farewell)
  <- person_speaks(name, language), 
     !person_likelySpeaks(name, _),
     !farewells(language, _),
     default_farewell(farewell).

person_hasFarewell(name, farewell)
  <- !person_speaks(name, _), 
     person_likelySpeaks(name, language),
     !farewells(language, _),
     default_farewell(farewell).

The query that prints the predicates person_hasGreeting and person_hasFarewell for "Guido van Rossum" can be found below:
```
$ lb query helloworld '
    _(g,f) <- person_hasGreeting(n,g),
	      person_hasFarewell(n,f),
	      n = "Guido van Rossum".'
```

1.5. Extra Credit

This additional exercise shows you what a great language LogiQL is for modeling relations, by letting you play around with recursion!

Create a new file called exercise3.logic. Create a new script exercise3.sh (you can also simply modify helloworld.sh if you want. This exercise does not require the logic from the previous 2 exercises):

#!/bin/bash

set -e

lb create --overwrite helloworld

lb addblock helloworld -f ../../helloworld.logic

#your script should use file exercise3.logic instead of exercise3_1.logic
lb addblock helloworld -f exercise3_1.logic

You are my friend, and I am yours: Can you write a rule that makes friends a symmetric relation? You can define the symmetric relation using the following formula:

If ("Alice", "Bob") are friends, then ("Bob", "Alice") are friends, as well.

Test your program with by declaring the following friends relations:

friends

"Alice"	"Bob"
"Guido van Rossum"	"Charlie"
"Bob"	"Guido van Rossum"

Add the lb print command command at the end of your build script (exercise3.sh) to check that you have the expected data in friends.
Add the program to the helloworld workspace by running the exercise3.sh script:
```
$ sh exercise3.sh
```

Do you see the following? If not, you might need to look at your logic again.

created workspace 'helloworld'
added block 'helloworld'
added block 'exercise3_1'
Content of predicate friends:
"Alice"            "Bob"             
"Bob"              "Alice"           
"Bob"              "Guido van Rossum"
"Charlie"          "Guido van Rossum"
"Guido van Rossum" "Bob"             
"Guido van Rossum" "Charlie"

You are my friend's friend: Can you write a rule that makes friends a transitive relation? You can define the transitive relation using the following formula:

If ("Alice", "Bob") are friends, then "Alice" is also friends with "Bob"'s friends, and his friends' friends, etc.
- Add the program to the helloworld workspace by running the exercise3.sh script:
```
$ sh exercise3.sh
```
- Use the lb print command to check that you have the expected data in friends.
- Is "Alice" friends with "Guido van Rossum"? Is she friends with "Charlie"?

Tip

Hint: recursion is your friend.

You can find the solution to the exercises above here.

1.6. Answers to Extra Credit

Your exercise3.logic file should look now similar to the example below:

// symmetric friends relation (exercise 3.1)
friends(x,y) <- friends(y,x).

// data (exercise 3.1)
friends("Alice", "Bob").
friends("Guido van Rossum", "Charlie").
friends("Bob", "Guido van Rossum").

Your exercise3.logic file should look now similar to the example below:

// symmetric friends relation (exercise 3.1)
friends(x,y) <- friends(y,x).

// transitive friends relation (exercise 3.2)
friends(you,friend)
  <- friends(you,i), friends(i,friend).

// data (exercise 3.1)
friends("Alice", "Bob").
friends("Guido van Rossum", "Charlie").
friends("Bob", "Guido van Rossum").

2. Lesson 2- State and Events

To start with your exercises, go to the directory <TrainingDir>/helloworld/lesson2:

$ cd <TrainingDir>/helloworld/lesson2

You can find again a file called helloworld.logic in this folder. This file contains all the logic that you should have created by the end of lesson 1. We will use this file as the base for the following exercises.

2.1. Exercise 1: Modeling and Populating EDB Predicates

In this exercise, we will change some of the model for helloworld to be EDB predicates -- predicates whose contents can be changed externally.

Change the following predicates to be EDB predicates, so that their contents can be modified.
- greetings
- person_speaks
- farewells
Data and logic do not have to reside in the same file, let us therefore create a separate file to store our data, called data.logic. Add rules to data.logic to populate the predicates greetings, person_speaks, and farewells. Make sure that these predicates contain the same tuples that were defined in helloworld.logic using IDB rules. You can then remove those IDB rules from helloworld.logic.
Create a shell script exercise1.sh, so that it accomplishes the following tasks:
- Creates (and possibly overwrites) a workspace helloworld
- Add to helloworld the logic in helloworld.logic, as database lifetime predicates and logic.
- Add to helloworld the data defined in data.logic, where rules in data.logic are executed as transaction lifetime rules.
Tip

You might want to take a look at the lb addblock and lb exec commands and the bash scripts that you have used in the previous lesson.
It is also possible to add data to a workspace directly by running commands via the command-line, instead of executing a file: Use the lb exec command to make the appropriate change to the workspace such that the greeting for person "Sarah", in person_hasGreeting, is the Spanish greeting: "Hola Mundo". Similarly, the farewell for person "Sarah", as stored in person_hasFarewell, should be the Spanish farewell, "Adios Mundo"
- Execute a query (Note: do not use lb print) that prints out the personalized greeting and farewell for "Sarah". Is it what you expect to see?

You can find the solution to the exercise above here.

2.2. Answers to Exercise 1: Modeling EDB Predicates

Your helloworld.logic should look similar to the example below, with declarations and lang:derivationType statements as follows:

/************ model *****************************/
//model changes from exercise 1
greetings(language, content) -> string(language), string(content).
lang:derivationType[`greetings] = "Extensional".

person_speaks(name, language) -> string(name), string(language).
lang:derivationType[`person_speaks] = "Extensional".

person_hasGreeting(name, greeting) -> string(name), string(greeting).

farewells(language, content) -> string(language), string(content).
lang:derivationType[`farewells] = "Extensional".

//unchanged model from lesson 1
person_hasFarewell(name, farewell) -> string(name), string(farewell).

default_farewell(farewell) -> string(farewell).

default_greeting(greeting) -> string(greeting).

friends(name1, name2) -> string(name1), string(name2).

person_likelySpeaks(name, language) -> string(name), string(language).

/*********** logic ***********************************/
//unchanged logic from lesson 1
person_hasGreeting(name, greeting)
  <- person_speaks(name, language),
     greetings(language, greeting).

// default greeting
default_greeting(greeting)
  <- greetings("English", greeting).

// default farewell is German
default_farewell(farewell)
  <- farewells("German", farewell).

// likely language computation
person_likelySpeaks(name, language)
  <- friends(name, friend),
     person_speaks(friend, language).

// modified greetings using likelySpeaks information
person_hasGreeting(name, greeting)
  <- ( person_speaks(name, language)
     ; person_likelySpeaks(name, language) ),
     greetings(language, greeting).

person_hasGreeting(name, greeting)
  <- person_speaks(name, language), 
     !person_likelySpeaks(name, _),
     !greetings(language, _),
     default_greeting(greeting).

person_hasGreeting(name, greeting)
  <- !person_speaks(name, _),
     person_likelySpeaks(name, language),
     !greetings(language, _),
     default_greeting(greeting).

// modified farewell using likelySpeaks information
person_hasFarewell(name, farewell)
  <- ( person_speaks(name, language)
     ; person_likelySpeaks(name, language)),
     farewells(language, farewell).

person_hasFarewell(name, farewell)
  <- person_speaks(name, language), 
     !person_likelySpeaks(name, _),
     !farewells(language, _),
     default_farewell(farewell).

person_hasFarewell(name, farewell)
  <- !person_speaks(name, _), 
     person_likelySpeaks(name, language),
     !farewells(language, _),
     default_farewell(farewell).


/**************** data *****************************/
friends("Guido van Rossum", "Sarah").

Your data.logic should contain the following rules:

+greetings("English", "Hello World").
+greetings("German", "Hallo Welt").
+greetings("Spanish", "Hola Mundo").

+person_speaks("Sarah", "English").
+person_speaks("Lisa", "German").
+person_speaks("Guido van Rossum", "Dutch").
+person_speaks("Paco", "Spanish").

+farewells("English", "Goodbye World").
+farewells("German", "Auf Wiedersehen Welt").
+farewells("Spanish", "Adios Mundo").

Your shell script should look like the following:

#!/bin/bash

set -e

lb create --overwrite helloworld
lb addblock helloworld -f helloworld.logic
lb exec helloworld -f data.logic

You should execute the following command to achieve the desired changes in person_hasGreeting and person_hasFarewell for "Sarah":

$ lb exec helloworld '
    -person_speaks("Sarah", lang) 
      <- person_speaks@prev("Sarah", lang). 
    +person_speaks("Sarah", "Spanish").'

To query the personalized greeting and farewell for "Sarah", you should write a query similar to the one below:

$ lb query helloworld
    '_(greeting,farewell)
	<- person_hasGreeting("Sarah", greeting),
	person_hasFarewell("Sarah",farewell).'

The query should result in the following:

/--------------- _ ---------------\
"Hola Mundo" "Adios Mundo"
\--------------- _ ---------------/

2.3. Exercise 2: Database Lifetime Delta Rules

In this exercise, we put to practice the ability to define database lifetime delta rules, and use them to capture events.

Create a new file called events.logic to store all the logic related to this exercise.

We want to know when a certain language has been added to the workspace. To capture this, model an EDB predicate language_added, as a relation between string(language), and datetime(timestamp).
Note: datetime is a primitive type supported by the LogicBlox platform. We will learn the built-in functions that support its use as we go through this exercise.
Define a database lifetime EDB rule that, each time a new tuple is asserted into person_speaks a tuple gets asserted into language_added. The asserted tuple should contain the language asserted into person_speaks, and the timestamp should be computed using the formula:
```
timestamp = datetime:now[]
```
Note

datetime:now[] is a built-in function that returns a value of type datetime. By default, without further formatting, the time stored is the current UTC time. You can learn more aboute date/time operators in the Reference Manual.
A new language can not only be added via person_speaks, but also via the predicates greetings and farewells. You therefore also need to add database lifetime EDB rules that assert a tuple into language_added when new tuples are asserted into greetings or farewells.
Create a shell script exercise2.sh, so that it accomplishes the following tasks:
- Create (and possibly overwrite) a workspace helloworld
- Add to helloworld the logic in helloworld.logic from the previous exercise, as database lifetime predicates and logic.
- Add to helloworld the logic in events.logic, such that the rules are database lifetime rules, and that they will capture each time a language is added or retracted.
- Add to helloworld the data defined in data.logic from the previous exercise, where rules in data.logic are executed as transaction lifetime rules.
- Prints the content of language_added using lb print.
Does your script print out the following in your terminal?
```
"Dutch"     2015-10-10-14:46:35+00:00
"English"   2015-10-10-14:46:35+00:00
"German"    2015-10-10-14:46:35+00:00
"Spanish"   2015-10-10-14:46:35+00:00 
```
If not, you may want to check your logic.
We do not only want to know when a language has been added, but also when it got deleted. Model an EDB predicate language_retracted as a relation from string(language) to datetime(timestamp)
Define rules such that, when any tuple is retracted from person_speaks, greetings, or farewells, a tuple is asserted into language_retracted, logging the language retracted, and the timestamp retraction happened.

Rerun exercise2.sh so that your revised events.logic gets loaded into the workspace.
Execute a rule directly against the helloworld workspace that retracts all persons that speak "Dutch". Note: your rule should not refer to any person using a string constant.
Check the content of language_retracted using lb print. What do you see? Do you see one entry for "Dutch"? If not, check your logic.

You can find the solution to the exercises above here.

2.4. Answers to Exercise 2: Database Lifetime Delta Rules

Your events.logic should now look as follows, containing the rules for the declaration of language_added:

 // Capture times when a language is added (exercise 2.1)
language_added(language, timestamp) -> string(language), datetime(timestamp).
lang:derivationType[`language_added] = "Extensional".

Your events.logic should now also contain the rule to calculate language_added each time a new tuple is asserted into person_speaks:

 // Capture times when a language is added (exercise 2.1)
language_added(language, timestamp) -> string(language), datetime(timestamp).
lang:derivationType[`language_added] = "Extensional".

//each time a new tuple is asserted into person_speaks a tuple gets asserted into language_added 
//(exercise 2.2)
+language_added(language, timestamp)
  <- +person_speaks(_, language),
     timestamp = datetime:now[].

Your events.logic should now also contain rules that assert a tuple into language_added when new tuples are asserted into greetings or farewells:

 // Capture times when a language is added (exercise 2.1)
language_added(language, timestamp) -> string(language), datetime(timestamp).
lang:derivationType[`language_added] = "Extensional".

// add an entry each time a language is added via person_speaks
//(exercise 2.2)
+language_added(language, timestamp)
  <- +person_speaks(_, language),
     timestamp = datetime:now[].

// add an entry each time a language is added via greetings or farewells
//(exercise 2.3)
+language_added(language, timestamp)
  <- +greetings(language, _),
     timestamp = datetime:now[].

+language_added(language, timestamp)
  <- +farewells(language, _),
     timestamp = datetime:now[].

Your exercise2.sh should look as follows:

#!/bin/bash

set -e

lb create --overwrite helloworld
lb addblock helloworld -f ../exercise1/helloworld.logic
lb addblock helloworld -f events.logic
lb exec helloworld -f ../exercise1/data.logic
lb print helloworld language_added

Your events.logic should now also contain the following declaration for language_retracted:

 // Capture times when a language is added (exercise 2.1)
language_added(language, timestamp) -> string(language), datetime(timestamp).
lang:derivationType[`language_added] = "Extensional".

// add an entry each time a language is added via person_speaks
//(exercise 2.2)
+language_added(language, timestamp)
  <- +person_speaks(_, language),
     timestamp = datetime:now[].

// add an entry each time a language is added via greetings or farewells
//(exercise 2.3)
+language_added(language, timestamp)
  <- +greetings(language, _),
     timestamp = datetime:now[].

+language_added(language, timestamp)
  <- +farewells(language, _),
     timestamp = datetime:now[].

// Capture times when a language is retracted
//(exercise 2.3)
language_retracted(language, timestamp) -> string(language), datetime(timestamp).
lang:derivationType[`language_retracted] = "Extensional".

Your events.logic should now also contains rules that assert a tuple into language_retracted when any tuple is retracted from person_speaks, greetings, or farewells

 // Capture times when a language is added (exercise 2.1)
language_added(language, timestamp) -> string(language), datetime(timestamp).
lang:derivationType[`language_added] = "Extensional".

// add an entry each time a language is added via person_speaks
//(exercise 2.2)
+language_added(language, timestamp)
  <- +person_speaks(_, language),
     timestamp = datetime:now[].

// add an entry each time a language is added via greetings or farewells
//(exercise 2.3)
+language_added(language, timestamp)
  <- +greetings(language, _),
     timestamp = datetime:now[].

+language_added(language, timestamp)
  <- +farewells(language, _),
     timestamp = datetime:now[].

// Capture times when a language is retracted
//(exercise 2.4)
language_retracted(language, timestamp) -> string(language), datetime(timestamp).
lang:derivationType[`language_retracted] = "Extensional".

// add an entry each time a language is retracted via person_speaks, greetings or farewells
+language_retracted(language, timestamp)
  <- -person_speaks(_, language),	 
     timestamp = datetime:now[].

+language_retracted(language, timestamp)
  <- -greetings(language, _),
     timestamp = datetime:now[].

+language_retracted(language, timestamp)
  <- -farewells(language, _),
     timestamp = datetime:now[].

Your exec command should be as follows:

$ lb exec helloworld '
-person_speaks(name, "Dutch") <- person_speaks@prev(name, "Dutch").'

Checking the content of language_retracted should yield results similar to those below in your terminal:

$ lb print helloworld language_retracted
"Dutch"   2015-10-10 10:00:44+00:00

3. Lesson 3 - Entities

To start with your exercises, go to the directory <TrainingDir>/helloworld/lesson3:

$ cd <TrainingDir>/helloworld/lesson3

In this folder, you should find the following three files, containing logic developed in the last lesson:

helloworld.logic
data.logic
events.logic

We will use these files as the starting point for the following exercises.

3.1. Exercise 1: Exercising Entities

In this exercise, we will change the data model to use entities instead of primitive types.

Let's not use strings anymore for the person names and the languages, but entities instead:
- In helloworld.logic, model an entity language with a refmode language_name of the type string.
- In helloworld.logic, model an entity person with a refmode person_name of the type string.
Modify the predicates currently referring to the name of a person or a language as string's in helloworld.logic to refer to the entity types. You will have to modify the following predicates:
- greetings
- person_speaks
- person_hasGreeting
- farewells
- person_hasFarewell
- friends
- person_likelySpeaks
Modify the following rule in helloworld.logic such that friends is populated with person entities:
```
friends("Lisa", "Guido van Rossum"). 
```
Modify the declarations in events.logic such that they use language entity as the type, rather than string.
Tip

Note that you will also need to modify the rules where we are updating the language_retracted predicate, such that the language variable is bound to the language entity, see example below:
```
+language_retracted(language, timestamp)
  <- -person_speaks(_, language),	 
     timestamp = datetime:now[],
     language(language). 
```
Without binding the language variable in the body, you will recevie a GHOST_NON_CORPOREAL_HEAD_VAR error during compilation time.
Create a shell script exercise3.sh that performs the following activities:
1. Create (and possibly overwrite) workspace helloworld
2. Add logic in the revised helloworld.logic, such that the predicate and logic contained in helloworld.logic has database lifetime.
3. Add logic in the revised events.logic, such that the predicate and logic contained in events.logic has database lifetime.
Run your script to check that your modifications compile.

You can find the solution to the exercise above here.

3.2. Answers to Exercise 1: Exercising Entities

Add the following two new entities to helloworld.logic:

//Declaration of entity language with refmode language_name (exercise 1.1)
language(lang), language_name(lang : name) -> string(name).

//Declaration of entity person with refmode person_name (exercise 1.1)
person(p), person_name(p : name) -> string(name).

Your helloworld.logic should contain declarations as follows:

//Modified predicate declarations, using the entities
//language and person as type instead of string (exercise 1.2)
greetings(language, content) -> language(language), string(content).
lang:derivationType[`greetings] = "Extensional".

person_speaks(name, language) -> person(name), language(language).
lang:derivationType[`person_speaks] = "Extensional".

person_hasGreeting(name, greeting) -> person(name), string(greeting).

farewells(language, content) -> language(language), string(content).
lang:derivationType[`farewells] = "Extensional".

person_hasFarewell(name, farewell) -> person(name), string(farewell).

default_farewell(farewell) -> string(farewell).

default_greeting(greeting) -> string(greeting).

friends(name1, name2) -> person(name1), person(name2).

person_likelySpeaks(name, language) -> person(name), language(language).

You should modify the rule deriving into friends in helloworld.logic to look like the following:

friends(guido, lisa)
  <- person_name(lisa,"Lisa"), person_name(guido,"Guido van Rossum").

You need to modify the declarations for language_added and language_retracted in events.logic to look like the following:

language_added(language, timestamp) -> language(language), datetime(timestamp).
lang:derivationType[`language_added] = "Extensional".

language_retracted(language, timestamp) -> language(language), datetime(timestamp).
lang:derivationType[`language_retracted] = "Extensional".

You script should look like the following:

#!/bin/bash

set -e

lb create --overwrite helloworld
lb addblock helloworld -f helloworld.logic
lb addblock helloworld -f events.logic

3.3. Exercise 2: Creating Entities

Now that we have changed the data model to use entities instead of primitive types, we will also need to change the way data is being added: Modify data.logic to create the following person entities:
- person: Add the persons from the list below, using their name as their refmode:
  - Sarah
  - Lisa
  - Guido van Rossum
  - Paco
- language: Add the languages from the list below:
  - English
  - German
  - Spanish
- Modify the rules populating greetings to contain language entities.
- Modify the rules populating person_speaks to contain person entities and language entities.
- Modify the rules populating farewells to contain language entities.

You can find the solution to the exercise above here.

3.4. Answers to Exercise 2: Using Delta Rules

Your data.logic file should look similar to the example below:

 /**************** data *****************************/
+person(person), +person_name(person :"Sarah").
+person(person), +person_name(person: "Lisa").
+person(person), +person_name(person :"Guido van Rossum").
+person(person), +person_name(person : "Paco").

+language(language), +language_name(language : "English").
+language(language), +language_name(language : "German").
+language(language), +language_name(language : "Spanish").

+greetings(language, "Hello World") <- language_name[language]="English".
+greetings(language, "Hallo Welt") <- language_name[language]="German".
+greetings(language, "Hola Mundo") <- language_name[language]="Spanish".

+greetings(english, "Hello World")
  <- language_name(english : "English").

+greetings(german, "Hallo Welt")
  <- language_name(german : "German").

+greetings(spanish, "Hola Mundo")
  <- language_name(spanish : "Spanish").

+person_speaks(sarah, english)
  <- person_name(sarah, "Sarah"), 
     language_name(english, "English").

+person_speaks(Lisa, german)
  <- person_name(Lisa, "Lisa"), 
     language_name(german, "German").

+person_speaks(guido, dutch)
  <- person_name(guido, "Guido van Rossum"), 
     language_name(dutch, "Dutch").

+person_speaks(paco, spanish)
  <- person_name(paco, "Paco"), 
     language_name(spanish, "Spanish").

+farewells(language, "Goodbye World") <- language_name[language]="English".
+farewells(language, "Auf Wiedersehen Welt") <- language_name[language]="German".
+farewells(language, "Adios Mundo") <- language_name[language]="Spanish".

3.5. Exercise 3: Putting it all Together

Let's say good-bye to our Hello World example by running a few queries to make sure that all the changes from the previous exercises were performed correctly:

Modify your script exercise3.sh such that it also adds the data in data.logic, as transaction lifetime rules.
Additionally, it should also run a query that prints the personalized greeting for Sarah
- Is the personalized greeting for Sarah "Hello World"? If not, you might want to re-visit your logic changes.
Additionally runs a query that prints the personalized greeting for Guido van Rossum.
- Is the personalized greeting for Guido "Hallo Welt"? If not, you might want to re-visit your logic changes.

You can find the solution to the exercise above here.

3.6. Answers to Exercise 3: Putting it all Together

Your exercise3.sh script should look similar to the example below:

 #!/bin/bash

set -e

lb create --overwrite helloworld

#your script should use file helloworld.logic instead of helloworld3.logic
#(it also might be that you have it in the same directory as this script)
lb addblock helloworld -f ../exercise1/helloworld3.logic

#your script should use file events.logic instead of events4.logic
#(it also might be that you have it in the same directory as this script)
lb addblock helloworld -f ../exercise1/events4.logic

#(it might be that you have it in the same directory as this script)
lb exec helloworld -f ../exercise2/data.logic

Your exercise.sh script should now also contain the query to print the personalized greeting for Sarah:

 #!/bin/bash

set -e

lb create --overwrite helloworld

#your script should use file helloworld.logic instead of helloworld3.logic
#(it also might be that you have it in the same directory as this script)
lb addblock helloworld -f ../exercise1/helloworld3.logic

#your script should use file events.logic instead of events4.logic
#(it also might be that you have it in the same directory as this script)
lb addblock helloworld -f ../exercise1/events4.logic

#(it might be that you have it in the same directory as this script)
lb exec helloworld -f ../exercise2/data.logic

echo "Personalized greeting for Sarah"
lb query helloworld '_(greeting) <- person_hasGreeting(p, greeting), person_name(p, "Sarah").'

Your exercise.sh script should now also contain the query to print ther personalized greeting for Guido van Rossum:

 #!/bin/bash

set -e

lb create --overwrite helloworld

#your script should use file helloworld.logic instead of helloworld3.logic
#(it also might be that you have it in the same directory as this script)
lb addblock helloworld -f ../exercise1/helloworld3.logic

#your script should use file events.logic instead of events4.logic
#(it also might be that you have it in the same directory as this script)
lb addblock helloworld -f ../exercise1/events4.logic

#(it might be that you have it in the same directory as this script)
lb exec helloworld -f ../exercise2/data.logic

echo "Personalized greeting for Sarah (exercise 3.2)"
lb query helloworld '_(greeting) <- person_hasGreeting(p, greeting), person_name(p, "Sarah").'

echo "Personalized greeting for Guido (exercise 3.3)"
lb query helloworld '_(greeting) <- person_hasGreeting(p, greeting), person_name(p, "Guido van Rossum").'

4. Lesson 4 - Project Organization and Unit Testing

4.1. Introduction

Welcome to Discount Wines!

Now that we have explored the very basics of writing applications based on the LogicBlox platform, it is time to look at a more realistic application.

For the remainder of this training we will be extending a simple application for a retailer, called "Discount Wines". In this lesson, we will look in detail at all the application components involved in building the grid displayed on the simple page depicted in Figure 1, “Basic Wine Info”. The application will be further extended during the following lessons.

Figure 1. Basic Wine Info

You can find the source code for the training application that we will use for the remainder of this training in the following directory: <TrainingDir>/discountwines. We will from now on always assume that you are in that directory.

Building the application

Let's build the application and run some queries to get to know this new application better:

go to the directory <TrainingDir>/discountwines
```
$ cd <TrainingDir>/discountwines
```
set environment variables:
```
$ source env.sh 
```
start necessary services:
```
$ lb services restart 
```
fetch the source code for the first lesson of the tutorial:
```
$ ./tools/restore-base 
```
build the lesson source code and import sample data:
```
$ lb config
$ make 
```
serve the tutorial application:
```
$ make start-nginx 
```
The output of the command will contain the following entry:
```
You can access your application via localhost:8081 
```
Note that this command will not exit and will not print anything if it starts successfully! You can stop it again any time by hitting <ctrl+c>.

If you are developing locally, you will need to open your browser and navigate to http://localhost:8081/.
Tip

We have seen this script failing for certain users, with the following error:
```
[emerg] 63266#0: mkdir() "/opt/local/var/run/nginx/uwsgi_temp" failed (13: Permission denied) 
```
This is the fact that nginx is trying to create a directory with insufficient permissions. You need to use sudo privilege to create this directory. Once it is created, this error should go away, and you can run this script without sudo. To create the directory:
```
$ sudo mkdir /opt/local/var/run/nginx/uwsgi_temp
```

NOTE: Do not worry about trying to understand what each step is doing at this point. We are going to discuss these build steps in detail during the next lessons and you will also learn how to modify the build script to serve your needs.

Application Architecture

Before we get started with the exercises, let’s take a look at the project structure, in which we keep the source code for this sample application, including some tests:

discountwines/
  app/
  data/
  logiql/
    core.project 
    core/ 
    protocols/ 
    services/ 
  tests/
    lb_unit/ 
    services/

	contains the source code for a very simple JavaScript UI
	contains some sample data
	the (core) project manifest
	contains the source code for the core application logic, which includes the data model and business logic
	contains the service protocol for the services
	contains the source code for the services
	contains the tests for unit testing the logic
	contains the tests for the services

4.2. Tips on the usage of the Discount Wines application

We have setup this training application for you in a way that allows you to restore the application at any point of time to the base source code for any lesson. You can do this by simply running the following command from the <TrainingDir>/discountwines directory:

$ ./tools/restore-base <lesson_number>

Once you have restored a lesson, you will have to build the application again by running:

$ lb config
$ make

At the beginning of each lesson we will ask you to restore the application to the base version of that lesson, as often we have prepared some new files or made modifications to existing files that you need to use as input for your exercises.

Tip

If you restore a lesson or answer, all your local changes are overwritten. If you want to keep your changes, make sure that you make a local copy of them, before you run the script.

You can also restore the answer source code for any lesson by running the following command from the <TrainingDir>/discountwines directory:

$ ./tools/restore-answers <lesson_number>

Tip

Similarly to restoring the source code to the base version of a lesson, you will have to run $ lb config and $ make.

4.3. In Class Exercise: Queries

Now that we have built the application based on the base source code, let's run some queries to get to know the data model of the application better.

Run the following command to see a list of all the application specific predicates that we have defined in the base setup of this training application:

$ lb list discountwines |grep core

They are defined in the following two files:

logiql/core/attributes.logic
logiql/core/wine.logic

Let's get to know our dataset better, by writing a query using the lb query command to print all the descriptions of the wines in our application.

The query should return the following results:
```
[0] 1003   "Santa Rita Medalla Real Leyda Valley Pinot Noir 2008"
[1] 1002   "Santa Ines Limari Chardonnay 2010"                   
[2] 1001   "Santa Ana Chardonnay Viognier 2010"                  
[3] 1000   "San Martino Riserva 2007"    
```
Can you write a query using the lb query command that prints all wines of year < 2010?

Your query should return the following results:
```
[0] 1003   "Santa Rita Medalla Real Leyda Valley Pinot Noir 2008"
[3] 1000   "San Martino Riserva 2007" 
```

You can find the queries here.

4.4. Lab Exercises

Tip

Make all your changes directly in the discountwines folder and not the discountwines/lessons folder!

Exercise 1: Adding to the model

In a later lesson, we will extend our wine screen (as depicted in Figure 1, “Basic Wine Info”) with a search pane that will allow the user to search for wines based on certain parameters, such as the country of origin of the wine or the wine type. Let's not jump too far ahead and start with modeling these predicates. In this exercise we will extend our data model with two new predicates: hasOrigin and ofType:

Make the following changes to logiql/core/attributes.logic:
- Model an entity country, with a refmode country_id of the type string.
- Model an entity wineType, with a refmode wineType_id of the type string.
Let's add some data to our country and wineType entities using delta rules in data/wine.logic :
- Add the following country entities: Italy, Chile and Argentina
- Add the following wineType entities: white, red, rose and bubbly
Make the following changes to logiql/core/wine.logic:
- Model a predicate hasOrigin that is a function from wine to country.
- Model a predicate ofType that is a function from wine to wineType.

Tip

Don’t forget to recompile by running:

$ make

You can find the solution to the exercises above here.

Exercise 2: Create more data

Now that we have created some attributes for wines, let's populate them for the 4 wines that we available in our application: Extend the delta rules in data/wine.logic by populating the newly created attributes (hasOrigin and ofType) with the following data:

`core:wine:hasDescription`	`core:wine:hasOrigin`	`core:wine:ofType`
San Martino Reserva 2007	Italy	red
Santa Ana Chardonnay Viognier 2010	Argentina	white
Santa Ines Limari Chardonnay 2010	Chile	white
Santa Rita Medalla Real Leyda Valley Pinot Noir 2008	Chile	red

You can find the solution to this exercise here.

Exercise 3: Adding logic

Our wine search screen as depicted in Figure 1, “Basic Wine Info” currently displays the margin of a wine. In the next lesson, you will learn to modify the searchWine service to display the margin percentage instead of the margin. If you have a look at logiql/core/wine.logic, you'll see that the margin percentage not yet defined, though. The purpose of this exercise is therefore to extend the data model in logiql/core/wine.logic with a predicate that calculates the margin percent:
- Model the predicate margin_percent as a relation between a wine and a float.
- Define margin_percent using the formula below and round the results to 0 significant digits.
```
margin_percent = ( margin / cost ) * 100
```
  Tip
  
  you will find the built-in predicate float:round useful. Run the lb predinfo command on this built-in predicate to find out more information on it's usage!
Use the lb print command to check the content of the following two predicates:
- margin should return the following results:
```
[10000000032] 1002 3.79
[10000000033] 1001 3.39
[10000000035] 1003 5.49
[10000000038] 1000 3.39 
```
- margin_percent should return the following results:
```
[10000000032] 1002 90.0 
[10000000033] 1001 94.0 
[10000000035] 1003 122.0
[10000000038] 1000 94.0 
```

You can find the solution the exercises above here.

Exercise 4: Constraints

Let's add some constraints to our data model. Modify logiql/core/wine.logic to impose functional dependency constraints on the following predicates:

hasDescription: each wine should have at most one description.
ofYear: each wine should have at most one vintage year.
cost: each wine should have at most one cost.
retail: each wine should have at most one retail price.
margin: each wine should have at most one margin.
margin_percent: each wine should have at most one margin percent.
hasOrigin: each wine should have at most one country of origin.
ofType: each wine should have at most one type.

You can find the solution of this exercise here.

Exercise 5: Unit Testing

Now that we have created a new predicate, we also need to create a unit test for it. Go to the tests/lb_unit/suiteBasic folder and extend the test suite by creating a new file called testMarginPercent.lb for your new tests:

Write a new test to make sure that there is always a cost for a wine.
Write a new test to make that there is always a retail for a wine.
You can verify your test by running the following command:
```
$ lb unit --suite-dir tests/lb_unit/ --progress 
```
If your test passes successfully, you should see the following in the command line after running the command above:
```
tests/lb_unit/suiteBasic/testMargin.lb	Success
tests/lb_unit/suiteBasic/testMarginPercent.lb	Success

_________

OK
Test Results:
Run:2	Failures:0	Errors:0

Script Elapsed Time: 2.13385s 
```

You can find the solution to the exercises above here.

Extra credit

Add another test to tests/lb_unit/suiteBasic/testMarginPercent.lb to make sure that the margin computed from margin_percent is within 1% of the margin computed from retail - cost

You can find the solution to this exercise here.

4.5. Answers to Exercises Lesson 4

Answers to In Class Exercise: Queries

You should run the query below to print the expected results:
```
$ lb query discountwines '_(w,desc) <- core:wine:hasDescription(w,desc).' 
```

You should run the query below to print the expected results:

$ lb query discountwines \
  '_(w, desc) <-
    core:wine:ofYear(w, y),
    core:attributes:year_id(y, yid),
    yid < 2010,
    core:wine:hasDescription(w, desc).'

Answers to Exercise 1

Your logiql/core/attributes.logic file should look now similar to the example below (the modifications from this exercise are marked in bold):

block(`attributes) {

  export(`{

    year(x), year_id(x : id) -> int(id).

    country(x), country_id(x : id) -> string(id).
    wineType(x), wineType_id(x : id) -> string(id).

  })

} <-- .

Your data/wine.logic file should look now similar to the example below (the modifications from this exercise are marked in bold):

// creating the year entities
+core:attributes:year(y), +core:attributes:year_id(y,y_id) <- y_id = 2007.
+core:attributes:year(y), +core:attributes:year_id(y,y_id) <- y_id = 2008.
+core:attributes:year(y), +core:attributes:year_id(y,y_id) <- y_id = 2009.
+core:attributes:year(y), +core:attributes:year_id(y,y_id) <- y_id = 2010.
+core:attributes:year(y), +core:attributes:year_id(y,y_id) <- y_id = 2007.

// creating countries
+core:attributes:country(x), +core:attributes:country_id(x,"Italy").
+core:attributes:country(x), +core:attributes:country_id(x,"Argentina").
+core:attributes:country(x), +core:attributes:country_id(x,"Chile").

// creating types
+core:attributes:wineType(x), +core:attributes:wineType_id(x,"white").
+core:attributes:wineType(x), +core:attributes:wineType_id(x,"red").
+core:attributes:wineType(x), +core:attributes:wineType_id(x,"rose").
+core:attributes:wineType(x), +core:attributes:wineType_id(x,"bubbly").

// creating wines
+core:wine:wine(x), +core:wine:wine_id(x:1000),
+core:wine:hasDescription(x,"San Martino Riserva 2007"),
+core:wine:ofYear(x,year),
+core:wine:cost(x,3.6f),
+core:wine:retail(x,6.99f)
  <- core:attributes:year_id[year] = 2007.     

+core:wine:wine(x), +core:wine:wine_id(x:1001),
+core:wine:hasDescription(x,"Santa Ana Chardonnay Viognier 2010"),
+core:wine:ofYear(x,year),
+core:wine:cost(x,3.6f),
+core:wine:retail(x,6.99f)
  <- core:attributes:year_id[year] = 2010.

+core:wine:wine(x), +core:wine:wine_id(x:1002),
+core:wine:hasDescription(x,"Santa Ines Limari Chardonnay 2010"),
+core:wine:ofYear(x,year),
+core:wine:cost(x,4.2f),
+core:wine:retail(x,7.99f)
  <- core:attributes:year_id[year] = 2010.

+core:wine:wine(x), +core:wine:wine_id(x:1003),
+core:wine:hasDescription(x,"Santa Rita Medalla Real Leyda Valley Pinot Noir 2008"),
+core:wine:ofYear(x,year),
+core:wine:cost(x,4.5f),
+core:wine:retail(x,9.99f)
  <- core:attributes:year_id[year] = 2008.

Your logiql/core/wine.logic file should look now similar to the example below (the modifications from this exercise are marked in bold):

block (`wine) {

  alias(`attributes:year, `year),
  alias(`attributes:country, `country),
  alias(`attributes:wineType, `wineType), 

  export(`{

    wine(wine), wine_id(wine : id) -> int(id).
    hasDescription(wine, name) -> wine(wine), string(name).
    ofYear(wine, year) -> wine(wine), year(year).

    cost(wine,price) -> wine(wine), float(price).
    retail(wine,price) -> wine(wine), float(price).
    margin(wine,margin) -> wine(wine), float(margin).
    margin_percent(wine,percent) -> wine(wine), float(percent).

    hasOrigin(wine, origin) -> wine(wine), country(origin).
    ofType(wine, type) -> wine(wine), wineType(type).

  }),

  clauses(`{

    margin(wine,margin)
      <- retail(wine,retail),
        cost(wine,cost),
   margin = retail - cost.

    margin_percent(wine,percent)
      <- margin(wine, margin),
        cost(wine, cost),
   percent = float:round[( margin / cost ) * 100f].

  })

} <-- .

Answers to Exercise 2

Your data/wine.logic file should look now similar to the example below (the modifications from this exercise are marked in bold):

// creating the year entities
+core:attributes:year(y), +core:attributes:year_id(y,y_id) <- y_id = 2007.
+core:attributes:year(y), +core:attributes:year_id(y,y_id) <- y_id = 2008.
+core:attributes:year(y), +core:attributes:year_id(y,y_id) <- y_id = 2009.
+core:attributes:year(y), +core:attributes:year_id(y,y_id) <- y_id = 2010.
+core:attributes:year(y), +core:attributes:year_id(y,y_id) <- y_id = 2007.

// creating countries
+core:attributes:country(x), +core:attributes:country_id(x,"Italy").
+core:attributes:country(x), +core:attributes:country_id(x,"Argentina").
+core:attributes:country(x), +core:attributes:country_id(x,"Chile").

// creating types
+core:attributes:wineType(x), +core:attributes:wineType_id(x,"white").
+core:attributes:wineType(x), +core:attributes:wineType_id(x,"red").
+core:attributes:wineType(x), +core:attributes:wineType_id(x,"rose").
+core:attributes:wineType(x), +core:attributes:wineType_id(x,"bubbly").

// creating wines
+core:wine:wine(x), +core:wine:wine_id(x:1000),
+core:wine:hasDescription(x,"San Martino Riserva 2007"),
+core:wine:ofYear(x,year),
+core:wine:cost(x,3.6f),
+core:wine:retail(x,6.99f),
+core:wine:hasOrigin(x,italy),
+core:wine:ofType(x,type) 
  <- core:attributes:year_id[year] = 2007,
     core:attributes:country_id[italy] = "Italy",
     core:attributes:wineType_id[type] = "red".

+core:wine:wine(x), +core:wine:wine_id(x:1001),
+core:wine:hasDescription(x,"Santa Ana Chardonnay Viognier 2010"),
+core:wine:ofYear(x,year),
+core:wine:cost(x,3.6f),
+core:wine:retail(x,6.99f),
+core:wine:hasOrigin(x,country),
+core:wine:ofType(x,type) 
  <- core:attributes:year_id[year] = 2010,
     core:attributes:country_id[country] = "Argentina",
     core:attributes:wineType_id[type] = "white".

+core:wine:wine(x), +core:wine:wine_id(x:1002),
+core:wine:hasDescription(x,"Santa Ines Limari Chardonnay 2010"),
+core:wine:ofYear(x,year),
+core:wine:cost(x,4.2f),
+core:wine:retail(x,7.99f),
+core:wine:hasOrigin(x,country),
+core:wine:ofType(x,type)
  <- core:attributes:year_id[year] = 2010,
     core:attributes:country_id[country] = "Chile",
     core:attributes:wineType_id[type] = "white".

+core:wine:wine(x), +core:wine:wine_id(x:1003),
+core:wine:hasDescription(x,"Santa Rita Medalla Real Leyda Valley Pinot Noir 2008"),
+core:wine:ofYear(x,year),
+core:wine:cost(x,4.5f),
+core:wine:retail(x,9.99f),
+core:wine:hasOrigin(x,country),
+core:wine:ofType(x,type)
  <- core:attributes:year_id[year] = 2008,
     core:attributes:country_id[country] = "Chile",
     core:attributes:wineType_id[type] = "red".

Answers to Exercise 3

Your logiql/core/wine.logic file should look now similar to the example below (the modifications from this exercise are marked in bold):

block (`wine) {
     
  alias(`attributes:year, `year),
  alias(`attributes:country, `country),
  alias(`attributes:wineType, `wineType),

  export(`{

    wine(wine), wine_id(wine : id) -> int(id).
    hasDescription(wine, name) -> wine(wine), string(name).
    ofYear(wine, year) -> wine(wine), year(year).

    cost(wine,price) -> wine(wine), float(price).
    retail(wine,price) -> wine(wine), float(price).
    margin(wine,margin) -> wine(wine), float(margin).

    hasOrigin(wine, origin) -> wine(wine), country(origin).
    ofType(wine, type) -> wine(wine), wineType(type).
    
    margin_percent(wine, percent) -> wine(wine), float(percent). 

  }),

  clauses(`{

    margin(wine,margin)
      <- retail(wine,retail),
         cost(wine,cost),
   margin = retail - cost.
    
    margin_percent(wine,percent) 
      <- margin(wine, margin),
         cost(wine, cost),
   percent = float:round[( margin / cost ) * 100f].
  })

} <-- .

You should run the following commands:

$ lb print discountwines core:wine:margin

$ lb print discountwines core:wine:margin_percent

Answers to Exercise 4

Your logiql/core/wine.logic file should look now similar to the example below (the modifications from this exercise are marked in bold):

block (`wine) {
     
  alias(`attributes:year, `year),
  alias(`attributes:country, `country),
  alias(`attributes:wineType, `wineType),

  export(`{

    wine(wine), wine_id(wine : id) -> int(id).
    hasDescription[wine] = name -> wine(wine), string(name).
    ofYear[wine] = year -> wine(wine), year(year).

    cost[wine] = price -> wine(wine), float(price).
    retail[wine] = price -> wine(wine), float(price).
    margin[wine] = margin -> wine(wine), float(margin).
    margin_percent[wine] = percent -> wine(wine), float(percent).
    
    hasOrigin[wine] = origin -> wine(wine), country(origin).
    ofType[wine] = type -> wine(wine), wineType(type).

  }),

  clauses(`{

    margin(wine,margin)
      <- retail(wine,retail),
        cost(wine,cost),
  margin = retail - cost.

     margin_percent(wine,percent) 
       <- margin(wine, margin),
        cost(wine, cost),
  percent = float:round[( margin / cost ) * 100f].

  })

} <-- .

Answers to Exercise 5

Your tests/lb_unit/suiteBasic/testMarginPercent.lb file should look now similar to the example below

transaction

//test that there is always a cost
exec <doc>
core:wine:wine(wine)
  -> core:wine:cost[wine] = _.
</doc>

commit

Your tests/lb_unit/suiteBasic/testMarginPercent.lb file should look now similar to the example below (the modifications from this exercise are marked in bold):

transaction

//test that there is always a cost
exec <doc>
core:wine:wine(wine)
  -> core:wine:cost[wine] = _.
</doc>

//test that there is always a retail
exec <doc>
core:wine:wine(wine)
  -> core:wine:retail[wine] = _.
</doc> 

commit

Answers to Extra credit

Your tests/lb_unit/suiteBasic/testMarginPercent.lb file should look now similar to the example below (the modifications from this exercise are marked in bold):

transaction

//test that there is always a cost
exec <doc>
core:wine:wine(wine)
  -> core:wine:cost[wine] = _.
</doc>

//test that there is always a retail
exec <doc>
core:wine:wine(wine)
  -> core:wine:retail[wine] = _.
</doc>

// test that the margin computed from margin percent is within 1% of the
// margin computed from retail - cost
exec <doc>
core:wine:margin[wine] = margin1,
( core:wine:margin_percent[wine] / 100f ) * core:wine:cost[wine] = margin2
  -> ( margin1 - margin2 ) / margin1 <= 0.01f.
</doc>
commit

5. Lesson 5 - Building and Testing Services

5.1. Lab Exercises

In the following exercises you will extend the searchWines interface to return the results below:

Before you get started with your exercises, make sure that you restore the application to the base version of this lesson, by running the following commands (note that your answers from the previous exercises will be overwritten. If you would like to keep them for later reference, make sure that you make a local copy, before running the script):

$ ./tools/restore-base 5
$ lb config
$ make

Exercise 1

In the previous lesson, you have added three new predicates to the data model. In this exercise, you will need to extend the protocol to send these three predicates, so that they are available to be displayed in the grid. Revise the Response protocol in logiql/protocols/searchWines.proto with the following changes:

Change the field margin to margin_percent
Return an additional required field of type string, named origin
Return an additional required field of type string, named type

You can find the solution to this exercise here.

Exercise 2

Extend the logic in logiql/services/searchWines.logic such that the fields we just modified/added above get populated with the appropriate values:

Field margin_percent should be populated with the value of margin_percent defined in logiql/core/wine.logic
Field origin should be populated with the value of hasOrigin defined in logiql/core/wine.logic
Field type should be populated with the value of ofType defined in logiql/core/wine.logic

You can find the solution to this exercise here.

Exercise 3

Now that we have made modifications to the service, we also need to update our tests to reflect these changes. Modify the python tests for the service in tests/services/basics.py to expect margin_percent, origin, and type.

You can find the solution here.

Testing your changes from this lesson

You can test your changes from the previous exercises in this lesson by running the tests for the service via the following command:

$ python tests/services/basics.py

Tip

Note that before you can test your changes, you must recompile and re-deploy your application by running

$ make

The test should return the following results:

test_getWines (__main__.TestBasicService) ... {
    "wine":[
        {
            "cost":3.6,
            "description":"San Martino Riserva 2007",
            "id":1000,
            "margin_percent":94.0,
            "origin":"Italy",
            "retail":6.99,
            "type":"red",
            "year":2007
        },
        {
            "cost":3.6,
            "description":"Santa Ana Chardonnay Viognier 2010",
            "id":1001,
            "margin_percent":94.0,
            "origin":"Argentina",
            "retail":6.99,
            "type":"white",
            "year":2010
        },
        {
            "cost":4.2,
            "description":"Santa Ines Limari Chardonnay 2010",
            "id":1002,
            "margin_percent":90.0,
            "origin":"Chile",
            "retail":7.99,
            "type":"white",
            "year":2010
        },
        {
            "cost":4.5,
            "description":"Santa Rita Medalla Real Leyda Valley Pinot Noir 2008",
            "id":1003,
            "margin_percent":122.0,
            "origin":"Chile",
            "retail":9.99,
            "type":"red",
            "year":2008
        }
    ]
}
ok

----------------------------------------------------------------------
Ran 1 test in 0.217s

OK

Test your changes with the browser UI

If you wish, you can test whether your changes work with the browser UI provided with this training application. In order to do so, you need to make sure that you have the UI running:

$ make start-nginx

Now you should be able to access the application via the URL http://localhost:8081/. The link for Lesson 5 should exercise your service, expecting to receive the additional as well as modified fields.

5.2. Answers to Exercises Lesson 5

Answers to Exercise 1

Your protocol file logiql/protocols/serachWines.proto should contain a message Wine as follows, with the modified and additional fields displayed in bold:

message Wine {
  required int64 id = 1;
  required string description = 2;
  required int64 year = 3;

  required double cost = 6;
  required double retail = 7;
  required double margin_percent = 8;

  required string origin = 9;
  required string type = 10;
}

Answers to Exercise 2

The main EDB rule in logiql/services/searchWines.logic constructing the resulting message protocols:searchWines:Wine should be modified as follows:

/** create Wine message **/
+WineConstructor[id]= result,
+protocols:searchWines:Wine(result),
+protocols:searchWines:Wine_id(result,id),
+protocols:searchWines:Wine_description(result,description),
+protocols:searchWines:Wine_year(result,year_id),
+protocols:searchWines:Wine_cost(result,cost),
+protocols:searchWines:Wine_retail(result,retail),
+protocols:searchWines:Wine_margin_percent(result,margin_percent),
+protocols:searchWines:Wine_origin(result,originId),
+protocols:searchWines:Wine_type(result,typeId),
+protocols:searchWines:Response_wine(response,id,result)
  <-
  +protocols:searchWines:Response(response),
  wine:wine_id(wine : id),
  wine:hasDescription(wine,description),
  wine:ofYear(wine,year),
  attributes:year_id[year]=year_id,
  wine:cost(wine,cost),
  wine:retail(wine,retail),
  wine:margin_percent(wine,margin_percent),
  wine:hasOrigin(wine,origin), attributes:country_id(origin, originId),
  wine:ofType(wine,type), attributes:wineType_id(type,typeId). 

  })
} <-- .

Answers to Exercise 3

The required modifications in tests/services/basics.py are displayed in bold:

#! /usr/bin/env python

import sys
import os
import unittest
import time
import json
import subprocess
from datetime import datetime

sys.path.insert(0, '%s/lib/python' % os.environ.get('LOGICBLOX_HOME'))
sys.path.insert(0, '%s/lib/python' % os.environ.get('LB_WEBSERVER_HOME'))

import lb.web.testcase
import lb.web.service
import lb.web.admin

class TestBasicService(lb.web.testcase.PrototypeWorkspaceTestCase):

    prototype = "discountwines"

    def setUp(self):
        super(TestBasicService, self).setUp()
        self.client = lb.web.service.Client("localhost", 8080, "/dw/searchWines")

    def test_getWines(self):
        req = '{}'
        response = self.client.call_json(req)
        r = json.loads(response)
        print json.dumps(r, sort_keys=True, indent=4, separators=(',', ':'))

        self.assert_("wine" in r)
        
        # check that each wine has a "type", and a "origin", and a "margin_percent" field
        for wine in r["wine"] :
            self.assert_("origin" in wine)
            self.assert_("type" in wine)
            self.assert_("margin_percent" in wine) 

def suite(args):
    suite = unittest.TestSuite()
    if (len(args) == 1):
        suite.addTest(unittest.makeSuite(TestBasicService))
    else:
        suite.addTest(TestBasicService(args[1]))
    return suite

if __name__ == '__main__':
    result = unittest.TextTestRunner(verbosity=2).run(suite(sys.argv))
    sys.exit(not result.wasSuccessful())

6. Lesson 6 - Aggregations and Hierarchies

Before you get started with your exercises, make sure that you restore the application to the base version of this lesson, by running the following commands:

$ ./tools/restore-base 6
$ lb config
$ make

6.1. In Class Exercise: Queries

- add unit_sales_day by modeling the predicate as a function from a day to a int.
- define unit_sales_day as the total of unit_sales by day.
- add unit_sales_wine_month by modeling the predicate as a function from wine and month to a int.
- define unit_sales_wine_month as the total of unit_sales by wine and month.
- add unit_sales_wine_year by modeling the predicate as a function of wine and year to a int.
- define unit_sales_wine_year as the total of unit_sales_wine_month by wine and year.

6.2. Lab Exercises

In the following exercises, you will practice writing aggregations to define predicates on various levels of the calendar hierarchy (even an alternative calendar hierarchy!), such as the unit returns per day, month and year, the average monthly net sales or the unit returns, given a day of a year by month of a year.

Exercise 1

Let's start with calculating the net sales in units: Net sales can be computed as the difference between sales and returns. If you have a look at the model in logiql/core/sales.logic, you can see that the unit returns are not yet defined, though, so let's go back a step and define it by extending the model in logiql/core/sales.logic:

add the unit_returns per day and wine by modeling predicate unit_returns as a function from a wine and day to a int.
add unit_netsales per day and wine by modeling predicate unit_netsales as a function from a wine and day to a int.
define unit_netsales using the following formula:
```
(unit_sales - unit_returns) 
```

You can find the solution to the exercises above here.

Exercise 2

Once we have defined the values by day, we can create our first aggregation to calculate the monthly and yearly values.

Tip

Do you remember how to map a day to a month and a month to a year?

Extend the model in logiql/core/sales.logic with the following aggregations:

- add unit_returns_wine_month by modeling predicate unit_returns_wine_month as a function from a wine and month to a int.
- define unit_returns_wine_month as the total of unit_returns by month and wine.
- add unit_returns_wine_year by modeling predicate unit_returns_wine_year as a function from a wine and year to a int.
- define unit_returns_wine_year as the total of unit_returns_wine_month by year and wine.
- add unit_netsales_wine_month by modeling predicate unit_netsales_wine_month as a function from a wine and month to a int.
- can you find a way to define unit_netsales_wine_month without using an aggregation rule?
- add unit_netsales_wine_year by modeling predicate unit_netsales_wine_year as a function from a wine and year to a int.
- can you find a way to define unit_netsales_wine_year without using an aggregation rule?

You can find the solution to the exercises above here.

Exercise 3

We do not only want to be able to compare per wine which one is selling best, but by wine type or by the vintage year! We therefore need to extend the model in logiql/core/sales.logic again, with the following aggregations:

- add unit_sales_byDayType, the number of units sold per day and the type of wine, by modeling predicate unit_sales_byDayType as a function from a day and wineType to a int.
- define unit_sales_byDayType as the total of unit_sales by day and attributes:wineType.
- add unit_sales_byDayVintageYear, the number of units sold per day and the vintage year of a wine, by modeling predicate unit_sales_byDayVintageYear as a function from a day and attributes:year to a int.
- define unit_sales_byDayVintageYear as the total of unit_sales by day and ofYear.

You can find the solution to the exercises above here.

Exercise 4

Wouldn't it be interesting to know, how much profit we made per day, month or year? Extend the model in logiql/core/sales.logic by computing the predicates listed below, using the following formula:

profit = margin * netsales

Tip

You will find the built-in predicate int:float:convert useful. You can find more information on the usage of this built-in predicate in the Reference Manual.

- add profit_day by modeling predicate profit_day as a function from a day to a float.
- define profit_day as the total profit per day.
- add profit_month by modeling predicate profit_month as a function from a month to a float.
- define profit_month as the total profit per month.
- add profit_year by modeling predicate profit_year as a function from a year to a float.
- define profit_year as the total profit per year.
- create a new unit test called testProfit.lb in tests/lb_unit/suiteBasic that verifies for each day that:
```
profit = retail - cost 
```
- you can verify your test by running the following command:
```
$ lb unit --suite-dir tests/lb_unit/ --progress 
```

You can find the solution to the exercises above here.

Exercise 5

Now that we have calculated the profit per day, month and year, we can also calculate the profit percentage. Extend the model in logiql/core/sales.logic by computing the predicates listed below, using the following formula:

profit_percent = profit / cost

- add profit_percent_day by modeling predicate profit_percent_day as a function from a day to a float.
- define profit_percent_day as the daily profit percentage.
- add profit_percent_month by modeling predicate profit_percent_month as a function from a month to a float.
- define profit_percent_month as the monthly profit percentage.
- add profit_percent_year by modeling predicate profit_percent_year as a function from a year to a float.
- define profit_percent_year as the yearly profit percentage.

Tip

You might want to think about how to total up a percentage!

You can find the solution to the exercises above here.

Exercise 6

One thing we haven't looked at yet, are the calculations of averages. Let's calculate the monthly and yearly average of our net sales per wine. Extend the model in logiql/core/sales.logic by computing:

- add unit_netsales_monthave, the average monthly net sales over all the months that we have data for, by modeling predicate unit_netsales_monthave as a function to a int.
- define unit_netsales_monthave as the average monthly net sales over all the months that we have data for.
  
  Tip
  
  there is no average aggregation!
- add unit_netsales_yearave, the average yearly net sales over all the years that we have data for, by modeling predicate unit_netsales_yearave as a function to a int.
- define unit_netsales_yearave as the average yearly net sales over all the years that we have data for.
check the content of unit_netsales by using the lb print command
- can you think of a reason why there is no data for unit_netsales ?
- extend data/sales.logic with a delta rule that, for every assertion to core:sales:unit_sales[wine, day], asserts to core:sales:unit_returns[wine, day] the value 0.
- re-run the lb print command from before to verify your changes.

You can find the solution to the exercises above here.

Exercise 7

In this exercise, you will have to model an alternative calendar hierarchy, that will allow you to not just create aggregations on a daily, monthly and yearly basis, but also give insight on a "day of week" or "month of year" level.

Model an alternative calendar hierarchy in core/hierarchies/calendar.logic by making the following enhancements:
- model an entity dayOfWeek, with refmode dayOfWeek_id of type string.
- model an entity monthOfYear, with refmode monthOfYear_id of type string.
- define a mapping from day to dayOfWeek: model a predicate day2dayOfWeek that is a function from day to dayOfWeek.
- define a mapping from month to monthOfYear: model a predicate month2monthOfYear that is a function from month to monthOfYear.
Modify data/hierarchies.logic to also generate tuples for dayOfWeek and monthOfYear

Tip

You will need functions datetime:parse and datetime:formatTZ. You can find more information on the usage of these built-in predicates in the Reference Manual.

datetime:format formats a predicate of type datetime to a string according to a specified datetime format (see ??? below). The declaration of the datetime:format predicate is the following:

datetime:format[dt, format]= s -> datetime(dt), string(format), string(s).

datetime:formatTZ additionally also takes a timezone as input for the formatting. The declaration of the datetime:formatTZ predicate is the following:

datetime:format[dt, format,timezone]= s -> datetime(dt), string(format), string(timezone), string(s).

datetime:parse parses a string representing a datetime according to a specified datetime format (see Reference Manual). The declaration of the datetime:parse predicate is the following:

datetime:parse[s, format]= dt -> string(s), string(format), datetime(dt).

You can find the solution to the exercises above here.

Exercise 8

Finally, let us add some aggregations that make use of our alternative calendar hierarchy. Extend the model in logiql/core/sales.logic with:

- add unit_sales_dayOfWeek_monthOfYear, the unit_sales given a day of a year by month of a year, by modeling predicate unit_sales_dayOfWeek_monthOfYear as a function from a dayOfWeek and monthOfYear to a int.
- define unit_sales_dayOfWeek_monthOfYear as the total of unit_sales given a (dayOfWeek, monthOfYear) pair.
- add unit_returns_dayOfWeek_monthOfYear, the unit_returns given a day of a year by month of a year, by by modeling predicate unit_returns_dayOfWeek_monthOfYear as a function from a dayOfWeek and monthOfYear to a int.
- define unit_returns_dayOfWeek_monthOfYear as the total of unit_returns given a (dayOfWeek, monthOfYear) pair.

You can find the solution to the exercises above here.

6.3. Answers to Exercises Lesson 6

Answers to Exercise 1

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
      
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.

  })
} <-- .

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
      
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.

  })
} <-- .

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
      
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
    
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].

  })
} <-- .

Answers to Exercise 2

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
            
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
    
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.
    
  })
} <-- .

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
            
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
    
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.
       
    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year. 

  })
} <-- .

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
            
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
    
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.
       
    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    
  })
} <-- .

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
            
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
    
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = unit_sales_wine_year[wine, year] - unit_returns_wine_year[wine, year].
  })
} <-- .

Answers to Exercise 3

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
      
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = units 
      -> day(day), attributes:wineType(wineType), int(units).
  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
    
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = tunits     
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofType[wine] = wineType.
  })
} <-- .

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
      
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = units 
      -> day(day), attributes:wineType(wineType), int(units).
    unit_sales_byDayVintageYear[day, year] = units 
      -> day(day), attributes:year(year), int(units).
  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
    
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = tunits     
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofType[wine] = wineType.
    
    unit_sales_byDayVintageYear[day, year] = tunits 
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofYear[wine] = year.
  })
} <-- .

Answers to Exercise 4

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
      
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = units 
      -> day(day), attributes:wineType(wineType), int(units).
    unit_sales_byDayVintageYear[day, year] = units 
      -> day(day), attributes:year(year), int(units).
    
    profit_day[day] = tprofit -> day(day), float(tprofit).
  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
    
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = tunits     
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofType[wine] = wineType.
    
    unit_sales_byDayVintageYear[day, year] = tunits 
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofYear[wine] = year.
    
    profit_day[day] = tprofit
      <- agg<< tprofit = total(profitPerWine) >>
   profitPerWine = wine:margin[wine] * int:float:convert[unit_netsales[wine, day]].
    
  })
} <-- .

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
      
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = units 
      -> day(day), attributes:wineType(wineType), int(units).
    unit_sales_byDayVintageYear[day, year] = units 
      -> day(day), attributes:year(year), int(units).
    
    profit_day[day] = tprofit -> day(day), float(tprofit).
    profit_month[month] = tprofit -> month(month), float(tprofit).
  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.

    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = tunits     
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofType[wine] = wineType.
    
    unit_sales_byDayVintageYear[day, year] = tunits 
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofYear[wine] = year.
    
  profit_day[day] = tprofit
      <- agg<< tprofit = total(profitPerWine) >>
   profitPerWine = wine:margin[wine] * int:float:convert[unit_netsales[wine, day]].
    
    profit_month[month] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_day[day] = profit,
   day2month[day] = month.
  })
} <-- .

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
      
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = units 
      -> day(day), attributes:wineType(wineType), int(units).
    unit_sales_byDayVintageYear[day, year] = units 
      -> day(day), attributes:year(year), int(units).
    
    profit_day[day] = tprofit -> day(day), float(tprofit).
    profit_month[month] = tprofit -> month(month), float(tprofit).
    profit_year[year] = tprofit -> year(year), float(tprofit).
  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
    
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = tunits     
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofType[wine] = wineType.
    
    unit_sales_byDayVintageYear[day, year] = tunits 
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofYear[wine] = year.
    
   profit_day[day] = tprofit
      <- agg<< tprofit = total(profitPerWine) >>
   profitPerWine = wine:margin[wine] * int:float:convert[unit_netsales[wine, day]].
    
    profit_month[month] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_day[day] = profit,
   day2month[day] = month.
    
     profit_year[year] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_month[month] = profit,
   month2year[month] = year.
  })
} <-- .

Your tests/lb_unit/suiteBasic/testProfit.lb file should look now similar to the example below:

transaction
exec <doc>
_retail[day] = tretail -> core:hierarchies:calendar:day(day), float(tretail).
_cost[day] = tcost -> core:hierarchies:calendar:day(day), float(tcost).

_retail[day] = tretail
  <- agg<< tretail = total(retail) >>
     retail = core:wine:retail[wine] * int:float:convert[core:sales:unit_netsales[wine, day]].

_cost[day] = tcost
  <- agg<< tcost = total(cost) >>
     cost = core:wine:cost[wine] *  int:float:convert[core:sales:unit_netsales[wine, day]].
     
_retail[day] - _cost[day] = profit1,
core:sales:profit_day[day] = profit2
  -> profit1 = profit2.

</doc>
commit

Answers to Exercise 5

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
            
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = units 
      -> day(day), attributes:wineType(wineType), int(units).
    unit_sales_byDayVintageYear[day, year] = units 
      -> day(day), attributes:year(year), int(units).
    
    profit_day[day] = tprofit -> day(day), float(tprofit).
    profit_month[month] = tprofit -> month(month), float(tprofit).
    profit_year[year] = tprofit -> year(year), float(tprofit).
    
    cost_day[day] = cost -> day(day), float(cost).
    profit_percent_day[day] = ppercent -> day(day), float(ppercent).
  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
    
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = tunits     
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofType[wine] = wineType.
    
    unit_sales_byDayVintageYear[day, year] = tunits 
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofYear[wine] = year.
    
   profit_day[day] = tprofit
      <- agg<< tprofit = total(profitPerWine) >>
   profitPerWine = wine:margin[wine] * int:float:convert[unit_netsales[wine, day]].
    
    profit_month[month] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_day[day] = profit,
   day2month[day] = month.
    
     profit_year[year] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_month[month] = profit,
   month2year[month] = year.
     
    profit_percent_day[day] = profit_day[day] / cost_day[day].

    cost_day[day] = tcost
      <- agg<< tcost = total(cost) >>
         cost = int:float:convert[unit_netsales[wine, day]] * wine:cost[wine].
    
  })
} <-- .

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).
    
    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).
    
    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
      
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units).
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).
    
    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = units 
      -> day(day), attributes:wineType(wineType), int(units).
    unit_sales_byDayVintageYear[day, year] = units 
      -> day(day), attributes:year(year), int(units).
    
    profit_day[day] = tprofit -> day(day), float(tprofit).
    profit_month[month] = tprofit -> month(month), float(tprofit).
    profit_year[year] = tprofit -> year(year), float(tprofit).
    cost_day[day] = cost -> day(day), float(cost).
    profit_percent_day[day] = ppercent -> day(day), float(ppercent).
    profit_percent_month[month] = ppercent -> month(month), float(ppercent).
  }),
  
  clauses(`{
    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
         
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.
         
    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.
   
    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
        unit_sales_wine_month[wine, month] = units,
        month2year[month] = year.

    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
        unit_returns[wine, day] = units,
        day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
        unit_returns_wine_month[wine, month] = units,
        month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = tunits     
      <- agg<< tunits = total(units) >>
        unit_sales[wine, day] = units,
        wine:ofType[wine] = wineType.
    
    unit_sales_byDayVintageYear[day, year] = tunits 
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
         wine:ofYear[wine] = year.
         
    profit_day[day] = tprofit
        <- agg<< tprofit = total(profitPerWine) >>
           profitPerWine = wine:margin[wine] * int:float:convert[unit_netsales[wine, day]].
           
    profit_month[month] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_day[day] = profit,
        day2month[day] = month.
        
    profit_year[year] = tprofit
     <- agg<< tprofit = total(profit) >> 
        profit_month[month] = profit,
        month2year[month] = year.
    
    profit_percent_day[day] = profit_day[day] / cost_day[day].
  
    cost_day[day] = tcost
      <- agg<< tcost = total(cost) >>
         cost = int:float:convert[unit_netsales[wine, day]] * wine:cost[wine].
    
    profit_percent_month[month] = profit_month[month] / cost_month[month].

    cost_month[month] = tcost
      <- agg<< tcost = total(cost) >>
        cost_day[day] = cost,
        day2month[day] = month.
  })
} <-- .

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
            
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = units 
      -> day(day), attributes:wineType(wineType), int(units).
    unit_sales_byDayVintageYear[day, year] = units 
      -> day(day), attributes:year(year), int(units).
    
    profit_day[day] = tprofit -> day(day), float(tprofit).
    profit_month[month] = tprofit -> month(month), float(tprofit).
    profit_year[year] = tprofit -> year(year), float(tprofit).
    
    cost_day[day] = cost -> day(day), float(cost).
    profit_percent_day[day] = ppercent -> day(day), float(ppercent).
    profit_percent_month[month] = ppercent -> month(month), float(ppercent).
    profit_percent_year[year] = ppercent -> year(year), float(ppercent).
  }),

  clauses(`{

    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
    
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = tunits     
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofType[wine] = wineType.
    
    unit_sales_byDayVintageYear[day, year] = tunits 
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofYear[wine] = year.
    
  profit_day[day] = tprofit
      <- agg<< tprofit = total(profitPerWine) >>
   profitPerWine = wine:margin[wine] * int:float:convert[unit_netsales[wine, day]].
    
    profit_month[month] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_day[day] = profit,
   day2month[day] = month.
    
     profit_year[year] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_month[month] = profit,
   month2year[month] = year.
     
    profit_percent_day[day] = profit_day[day] / cost_day[day].

    cost_day[day] = tcost
      <- agg<< tcost = total(cost) >>
         cost = int:float:convert[unit_netsales[wine, day]] * wine:cost[wine].

    profit_percent_month[month] = profit_month[month] / cost_month[month].

    cost_month[month] = tcost
      <- agg<< tcost = total(cost) >>
         cost_day[day] = cost,
   day2month[day] = month.
    
    profit_percent_year[year] = profit_year[year] / cost_year[year].

    cost_year[year] = tcost
      <- agg<< tcost = total(cost) >> 
         cost_month[month] = cost,
   month2year[month] = year.
  })
} <-- .

Answers to Exercise 6

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
            
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = units 
      -> day(day), attributes:wineType(wineType), int(units).
    unit_sales_byDayVintageYear[day, year] = units 
      -> day(day), attributes:year(year), int(units).
    
    profit_day[day] = tprofit -> day(day), float(tprofit).
    profit_month[month] = tprofit -> month(month), float(tprofit).
    profit_year[year] = tprofit -> year(year), float(tprofit).
    
    cost_day[day] = cost -> day(day), float(cost).
    profit_percent_day[day] = ppercent -> day(day), float(ppercent).
    profit_percent_month[month] = ppercent -> month(month), float(ppercent).
    profit_percent_year[year] = ppercent -> year(year), float(ppercent).
    
    unit_netsales_monthave[] = units -> int(units).
  }),

  clauses(`{
    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
        
    numMonth[] = n
      <- agg<<n = count() >> month(_).
    
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
    
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
 
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = tunits     
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofType[wine] = wineType.
    
    unit_sales_byDayVintageYear[day, year] = tunits 
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofYear[wine] = year.
    
   profit_day[day] = tprofit
      <- agg<< tprofit = total(profitPerWine) >>
   profitPerWine = wine:margin[wine] * int:float:convert[unit_netsales[wine, day]].
    
    profit_month[month] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_day[day] = profit,
   day2month[day] = month.
    
     profit_year[year] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_month[month] = profit,
   month2year[month] = year.
     
    profit_percent_day[day] = profit_day[day] / cost_day[day].

    cost_day[day] = tcost
      <- agg<< tcost = total(cost) >>
         cost = int:float:convert[unit_netsales[wine, day]] * wine:cost[wine].

    profit_percent_month[month] = profit_month[month] / cost_month[month].

    cost_month[month] = tcost
      <- agg<< tcost = total(cost) >>
         cost_day[day] = cost,
   day2month[day] = month.
         
    profit_percent_year[year] = profit_year[year] / cost_year[year].

    cost_year[year] = tcost
      <- agg<< tcost = total(cost) >> 
         cost_month[month] = cost,
   month2year[month] = year.
    
    unit_netsales_monthave[] = unit_netsales_all[] / numMonth[].
    
    unit_netsales_all[] = tunits
      <- agg<< tunits = total(units) >>
           unit_netsales[_,_] = units.    
  })
} <-- .

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
            
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = units 
      -> day(day), attributes:wineType(wineType), int(units).
    unit_sales_byDayVintageYear[day, year] = units 
      -> day(day), attributes:year(year), int(units).
    
    profit_day[day] = tprofit -> day(day), float(tprofit).
    profit_month[month] = tprofit -> month(month), float(tprofit).
    profit_year[year] = tprofit -> year(year), float(tprofit).
    
    cost_day[day] = cost -> day(day), float(cost).
    profit_percent_day[day] = ppercent -> day(day), float(ppercent).
    profit_percent_month[month] = ppercent -> month(month), float(ppercent).
    profit_percent_year[year] = ppercent -> year(year), float(ppercent).
    
    unit_netsales_monthave[] = units -> int(units).
    unit_netsales_yearave[] = units -> int(units).
  }),

  clauses(`{
    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
     
    numMonth[] = n
      <- agg<<n = count() >> month(_).
    
    numYear[] = n
      <- agg<<n = count() >> year(_).
       
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
        
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = tunits     
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofType[wine] = wineType.
    
    unit_sales_byDayVintageYear[day, year] = tunits 
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofYear[wine] = year.
    
  profit_day[day] = tprofit
      <- agg<< tprofit = total(profitPerWine) >>
   profitPerWine = wine:margin[wine] * int:float:convert[unit_netsales[wine, day]].
    
    profit_month[month] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_day[day] = profit,
   day2month[day] = month.
    
     profit_year[year] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_month[month] = profit,
   month2year[month] = year.
     
    profit_percent_day[day] = profit_day[day] / cost_day[day].

    cost_day[day] = tcost
      <- agg<< tcost = total(cost) >>
         cost = int:float:convert[unit_netsales[wine, day]] * wine:cost[wine].
 
     profit_percent_month[month] = profit_month[month] / cost_month[month].

    cost_month[month] = tcost
      <- agg<< tcost = total(cost) >>
         cost_day[day] = cost,
   day2month[day] = month.
   
    profit_percent_year[year] = profit_year[year] / cost_year[year].

    cost_year[year] = tcost
      <- agg<< tcost = total(cost) >> 
         cost_month[month] = cost,
   month2year[month] = year.
    
    unit_netsales_monthave[] = unit_netsales_all[] / numMonth[].
    unit_netsales_yearave[] = unit_netsales_all[] / numYear[].
    
    unit_netsales_all[] = tunits
      <- agg<< tunits = total(units) >>
           unit_netsales[_,_] = units.    
  })
} <-- .

The required modifications in data/sales.logic are displayed in bold:

_file(offset; ID,DAY,SALE) -> int(offset), string(ID), string(DAY), string(SALE).
 
lang:physical:fileMode[`_file] = "import".
lang:physical:filePath[`_file] = "data/sales.dlm".
lang:physical:delimiter[`_file] = "|".
lang:physical:hasColumnNames[`_file] = true.
lang:physical:columnNames[`_file] = "ID,DAY,SALE".

+core:sales:unit_sales[wine,day] = string:int:convert[SALE]
  <- _file(_;ID,DAY,SALE),
     core:wine:wine_id(wine, string:int:convert[ID]),
     core:hierarchies:calendar:day_id(day, DAY).

+core:sales:unit_returns[wine,day]=0
  <- +core:sales:unit_sales[wine,day]=_.

Answers to Exercise 7

The required modifications in core/hierarchies/calendar.logic are displayed in bold:

block(`calendar) {
  
  export(`{

    day(x), day_id(x : id) -> string(id).
    month(x), month_id(x : id) -> string(id).
    year(x), year_id(x : id) -> string(id).

    day2month[x] = y -> day(x), month(y).
    month2year[x] = y -> month(x), year(y).
    
    dayOfWeek(x), dayOfWeek_id(x : id) -> string(id).
    day2dayOfWeek[x] = y -> day(x), dayOfWeek(y).

    monthOfYear(x), monthOfYear_id(x : id) -> string(id).
    month2monthOfYear[x] = y -> month(x), monthOfYear(y). 

  })
} <-- .

The required modifications in data/hierarchies.logic are displayed in bold:

+_done(),
+core:hierarchies:calendar:day(x),
+core:hierarchies:calendar:day_id[x] = label
   <-
   y = 2011,
   datetime:parse[int:string:convert[y] + "-1-1 12:0:0 UTC", "%Y/%m/%d %H:%M:%S %Q"] = begin,
   int:range(0, 100, 1, i),
   datetime:add[begin, i, "days"] = dt,
   string:int:convert[datetime:formatTZ[dt, "%Y", "UTC"]] = y,
   datetime:formatTZ[dt, "%Y-%m-%d", "UTC"] = label.

+core:hierarchies:calendar:day(x),
+core:hierarchies:calendar:day_id[x] = label
   <-
   y = 2012,
   datetime:parse[int:string:convert[y] + "-1-1 12:0:0 UTC", "%Y/%m/%d %H:%M:%S %Q"] = begin,
   int:range(0, 100, 1, i),
   datetime:add[begin, i, "days"] = dt,
   string:int:convert[datetime:formatTZ[dt, "%Y", "UTC"]] = y,
   datetime:formatTZ[dt, "%Y-%m-%d", "UTC"] = label, 
   +_done().

+core:hierarchies:calendar:month(m),
+core:hierarchies:calendar:month_id(m:s),
+core:hierarchies:calendar:day2month[x] = m
   <-
   datetime:parse[core:hierarchies:calendar:day_id[x] + " 12:0:0 UTC", "%Y-%m-%d %H:%M:%S UTC"] = dt,
   datetime:formatTZ[dt, "%b %Y", "UTC"] = s.

+core:hierarchies:calendar:year(y),
+core:hierarchies:calendar:year_id(y:s),
+core:hierarchies:calendar:month2year[m] = y
   <-
   datetime:parse[core:hierarchies:calendar:day_id[x] + " 12:0:0 UTC", "%Y-%m-%d %H:%M:%S UTC"] = dt,
   datetime:formatTZ[dt, "%Y", "UTC"] = s,
   core:hierarchies:calendar:day2month[x] = m.


+core:hierarchies:calendar:dayOfWeek(d),
+core:hierarchies:calendar:dayOfWeek_id(d:s),
+core:hierarchies:calendar:day2dayOfWeek[x] = d
   <-
   datetime:parse[core:hierarchies:calendar:day_id[x] + " 12:0:0 UTC", "%Y-%m-%d %H:%M:%S UTC"] = dt,
   datetime:formatTZ[dt, "%A", "UTC"] = s.

+core:hierarchies:calendar:monthOfYear(my),
+core:hierarchies:calendar:monthOfYear_id(my:s),
+core:hierarchies:calendar:month2monthOfYear[m] = my
   <-
   datetime:parse[core:hierarchies:calendar:day_id[x] + " 12:0:0 UTC", "%Y-%m-%d %H:%M:%S UTC"] = dt,
   datetime:formatTZ[dt, "%B", "UTC"] = s,
   core:hierarchies:calendar:day2month[x] = m.

Answers to Exercise 8

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
            
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = units 
      -> day(day), attributes:wineType(wineType), int(units).
    unit_sales_byDayVintageYear[day, year] = units 
      -> day(day), attributes:year(year), int(units).
    
    profit_day[day] = tprofit -> day(day), float(tprofit).
    profit_month[month] = tprofit -> month(month), float(tprofit).
    profit_year[year] = tprofit -> year(year), float(tprofit).
    
    cost_day[day] = cost -> day(day), float(cost).
    profit_percent_day[day] = ppercent -> day(day), float(ppercent).
    profit_percent_month[month] = ppercent -> month(month), float(ppercent).
    profit_percent_year[year] = ppercent -> year(year), float(ppercent).
    
    unit_netsales_monthave[] = units -> int(units).
    unit_netsales_yearave[] = units -> int(units).
    
    unit_sales_dayOfWeek_monthOfYear[dow, moy] = units -> 
      dayOfWeek(dow), monthOfYear(moy), int(units).
  }),

  clauses(`{
    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
     
    numMonth[] = n
      <- agg<<n = count() >> month(_).
    
    numYear[] = n
      <- agg<<n = count() >> year(_).
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
        
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = tunits     
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofType[wine] = wineType.
    
    unit_sales_byDayVintageYear[day, year] = tunits 
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofYear[wine] = year.
    
  profit_day[day] = tprofit
      <- agg<< tprofit = total(profitPerWine) >>
   profitPerWine = wine:margin[wine] * int:float:convert[unit_netsales[wine, day]].
    
    profit_month[month] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_day[day] = profit,
   day2month[day] = month.
    
     profit_year[year] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_month[month] = profit,
   month2year[month] = year.
     
    profit_percent_day[day] = profit_day[day] / cost_day[day].

    cost_day[day] = tcost
      <- agg<< tcost = total(cost) >>
         cost = int:float:convert[unit_netsales[wine, day]] * wine:cost[wine].

    profit_percent_month[month] = profit_month[month] / cost_month[month].

    cost_month[month] = tcost
      <- agg<< tcost = total(cost) >>
         cost_day[day] = cost,
   day2month[day] = month.
    
    profit_percent_year[year] = profit_year[year] / cost_year[year].

    cost_year[year] = tcost
      <- agg<< tcost = total(cost) >> 
         cost_month[month] = cost,
   month2year[month] = year.
    
    unit_netsales_monthave[] = unit_netsales_all[] / numMonth[].
    unit_netsales_yearave[] = unit_netsales_all[] / numYear[].
    
    unit_netsales_all[] = tunits
      <- agg<< tunits = total(units) >>
           unit_netsales[_,_] = units.
     
    unit_sales_dayOfWeek_monthOfYear[dow, moy] = tunits 
      <- agg<< tunits = total(units) >>
           unit_sales[_, day] = units,
           day2dayOfWeek[day] = dow,
     day2month[day] = month,
     month2monthOfYear[month] = moy.
  })
} <-- .

The required modifications in logiql/core/sales.logic are displayed in bold:

block(`sales) {

  alias_all(`hierarchies:calendar),

  export(`{

    unit_sales[wine, day] = units -> wine:wine(wine), day(day), int(units).  
    unit_sales_total[] = units -> int(units).

    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = units
      -> day(day), int(units).

    unit_sales_wine_month[wine, month] = units 
      -> wine:wine(wine), month(month), int(units).

    unit_sales_wine_year[wine, year] = units 
      -> wine:wine(wine), year(year), int(units).
            
    unit_returns[wine, day] = units -> wine:wine(wine), day(day), int(units). 
    unit_netsales[wine, day] = units -> wine:wine(wine), day(day), int(units).

    unit_returns_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_returns_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_netsales_wine_month[wine, month] = units -> wine:wine(wine), month(month), int(units).
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = units 
      -> day(day), attributes:wineType(wineType), int(units).
    unit_sales_byDayVintageYear[day, year] = units 
      -> day(day), attributes:year(year), int(units).
    
    profit_day[day] = tprofit -> day(day), float(tprofit).
    profit_month[month] = tprofit -> month(month), float(tprofit).
    profit_year[year] = tprofit -> year(year), float(tprofit).
    
    cost_day[day] = cost -> day(day), float(cost).
    profit_percent_day[day] = ppercent -> day(day), float(ppercent).
    profit_percent_month[month] = ppercent -> month(month), float(ppercent).
    profit_percent_year[year] = ppercent -> year(year), float(ppercent).
    
    unit_netsales_monthave[] = units -> int(units).
    unit_netsales_yearave[] = units -> int(units).
    
    unit_sales_dayOfWeek_monthOfYear[dow, moy] = units -> 
      dayOfWeek(dow), monthOfYear(moy), int(units).
    
    unit_returns_dayOfWeek_monthOfYear[dow, moy] = units -> 
      dayOfWeek(dow), monthOfYear(moy), int(units).
  }),

  clauses(`{
  
    unit_sales_total[] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, _] = units.
     
    numMonth[] = n
      <- agg<<n = count() >> month(_).
    
    numYear[] = n
      <- agg<<n = count() >> year(_).   
    /*************** Lesson 6, In Class Exercise *************/
    unit_sales_day[day] = tunits
      <- agg<< tunits = total(units) >>
         unit_sales[_, day] = units.

    unit_sales_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_sales[wine, day] = units,
   day2month[day] = month.

    unit_sales_wine_year[wine, year] = munits
      <- agg<< munits = total(units) >>
         unit_sales_wine_month[wine, month] = units,
   month2year[month] = year.
            
    unit_netsales[wine, day] = unit_sales[wine, day] - unit_returns[wine, day].
    // aggregations for unit_returns
    unit_returns_wine_month[wine, month] = munits
      <- agg<< munits = total(units) >>
         unit_returns[wine, day] = units,
   day2month[day] = month.

    unit_returns_wine_year[wine, year] = yunits
      <- agg<< yunits = total(units) >>
         unit_returns_wine_month[wine, month] = units,
   month2year[month] = year.

    // aggregations for unit_netsales
    unit_netsales_wine_month[wine, month] = unit_sales_wine_month[wine, month] - unit_returns_wine_month[wine, month].
    unit_netsales_wine_year[wine, year] = units -> wine:wine(wine), year(year), int(units).
    
    unit_sales_byDayType[day, wineType] = tunits     
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofType[wine] = wineType.
    
    unit_sales_byDayVintageYear[day, year] = tunits 
      <- agg<< tunits = total(units) >>
         unit_sales[wine, day] = units,
   wine:ofYear[wine] = year.
    
   profit_day[day] = tprofit
      <- agg<< tprofit = total(profitPerWine) >>
   profitPerWine = wine:margin[wine] * int:float:convert[unit_netsales[wine, day]].
    
    profit_month[month] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_day[day] = profit,
   day2month[day] = month.
    
     profit_year[year] = tprofit
      <- agg<< tprofit = total(profit) >> 
         profit_month[month] = profit,
   month2year[month] = year.
     
    profit_percent_day[day] = profit_day[day] / cost_day[day].

    cost_day[day] = tcost
      <- agg<< tcost = total(cost) >>
         cost = int:float:convert[unit_netsales[wine, day]] * wine:cost[wine].

    profit_percent_month[month] = profit_month[month] / cost_month[month].

    cost_month[month] = tcost
      <- agg<< tcost = total(cost) >>
         cost_day[day] = cost,
   day2month[day] = month.
    
    profit_percent_year[year] = profit_year[year] / cost_year[year].

    cost_year[year] = tcost
      <- agg<< tcost = total(cost) >> 
         cost_month[month] = cost,
   month2year[month] = year.
    
    unit_netsales_monthave[] = unit_netsales_all[] / numMonth[].
    unit_netsales_yearave[] = unit_netsales_all[] / numYear[].
    
    unit_netsales_all[] = tunits
      <- agg<< tunits = total(units) >>
           unit_netsales[_,_] = units.
     
    unit_sales_dayOfWeek_monthOfYear[dow, moy] = tunits 
      <- agg<< tunits = total(units) >>
           unit_sales[_, day] = units,
           day2dayOfWeek[day] = dow,
     day2month[day] = month,
     month2monthOfYear[month] = moy.
    
    unit_returns_dayOfWeek_monthOfYear[dow, moy] = tunits 
      <- agg<< tunits = total(units) >>
           unit_returns[_, day] = units,
           day2dayOfWeek[day] = dow,
     day2month[day] = month,
     month2monthOfYear[month] = moy.
  })
} <-- .

7. Lesson 7 - Scalable Data Import/Export

In this lesson you will have to write services to import delimited files. You do not have to create the delimited files (files containing the actual data to be imported) yourself, as we have prepared them for you already.

Before you get started with your exercises, make sure that you restore the application to the base version of this lesson, by running the following commands:

$ ./tools/restore-base 7
$ lb config
$ make

The delimited files can be found in the data folder, the service definitions for the delimited services (we have 2 examples, one without optional columns, and another one with) can be found under logiql/services/tdx. If you take a look at the configuration file (config.py), you can see that we now use the delim-wines-opt service to import the wine data, instead of executing some logic. A test that exercises both services can be found under tests/services/delimited.py.

7.1. Lab Exercises

Exercise 1

In this exercise you will have to create a delimited file service to import the unit_sales and unit_returns that are provided in data/sales.dlm.

Create a new file logiql/services/tdx/sales.logic and add the file definition and file (predicate) binding for a delimited file service that would take data/sales.dlm as input. Name your file definition sales, and your file binding sales_bindings.
Your service should be offered at URI /dw/delim-sales, and be bound to the file binding you just created, sales_bindings.
Try importing data/sales.dlm via curl, by POSTing the data/sales.dlm at URL http://localhost:8080/dw/delim-sales

If you need help on using the curl command, take a look at config.py, where curl is used to post wine data.
1. Write a query that prints out the total number of entries in core:sales:unit_sales (hint: use count aggregation). Do you see 808 as the result? If not, you should check your import service.
2. Write a query that prints out the total number of entries in core:sales:unit_returns. Do you see 808 as the result? If not, you should check your import service.

You can find the solution to the exercises above here.

Exercise 2

In this exercise you will implement a delimited file service that can import/export the calendar hierarchy (which we have generated until now). The service should be available at URI /dw/delim-calendar; it should take the file data/calendar.dlm as input.

Add the new file logiql/services/tdx/calendar.logic, with the file definition and file binding for the /dw/delim-calendar service.
- Name your file definition calendar; and make sure that it can handle the format of file data/calendar.dlm
- Name your file binding calendar_bindings; it should specify bindings for the following predicates:
  - core:hierarchies:calendar:day2month
  - core:hierarchies:calendar:month2year
  - core:hierarchies:calendar:day2dayOfWeek
  - core:hierarchies:calendar:month2monthOfYear
Add the service configuration for your new service in logiql/services/tdx/calendar.logic. The service should be available at URI /dw/delim-calendar, and it should have the file binding calendar_bindings.
Try importing data/calendar.dlm via curl, by POSTing the data/calendar.dlm at URL http://localhost:8080/dw/delim-calendar If you need help on using the curl command, take a look at config.py, where curl is used to post wine data.

Create a new unit test called testHierarchy.lb in tests/lb_unit/suiteBasics that verifies that the data that you have imported is correct:
1. all the days are associated with a dayOfWeek.
2. all the days are associated with a month.
3. all the months are associated with a monthOfYear.
4. all the months are associated with a year

You can find the solution to the exercises above here.

Exercise 3

Modify config.py to load calendar as well as sales data using the services you just built.

You can find the solution to the exercises above here.

Exercise 4

Create a file called tests/services/delim-cal.py. In this file, define a class TestDelimCalendar, which extends lb.web.testcase.PrototypeWorkspaceTestCase, and uses discountwines_test as the prototype workspace. This workspace will contain no data, which will allow you to unit test the calendar data loading of your service. Define a test that exercises your service /dw/delim-cal with some sample data. You can use some small subset of data/calendar.dlm, for instance.

Update config.py and uncomment the row in which this file is executed. To run your test (together with all of the other tests), run the following commands:
```
lb config  //to re-generate the Makefile, as you made changes to config.py
make check //run tests on test workspace
```
Tip

Take a look at the tests for importing wines in tests/services/delimited.py for some example tests!
Create a file called tests/services/delim-sales.py. In this file, define a class TestDelimSales, which extends lb.web.testcase.PrototypeWorkspaceTestCase, and uses discountwines_test_sales as the prototype workspace. This workspace will contain no sales data (only wine and calendar related hierarchy data), which will allow you to unit test the sales data loading of your service. Define a test that exercises your service /dw/delim-sales with some sample data. You can use some small subset of data/sales.dlm, for instance.

Update config.py and uncomment the rows below so that your test runs against the discountwines_test_sales workspace when running make check (we use this separate workspace, as we need to populate the workspace with some sample wines and the calendar hierarchy):
```
######################### test sales service
check_lb_workspace(
  name = test_ws_sales,
  libraries=[core_library])

check_program('tests/services/delim-sales.py', [test_ws_sales],input=['load-test-data'])
```
To run your tests, run:
```
lb config
make check
```

You can find the solution to the exercises above here.

7.2. Answers to Exercises Lesson 7

Answers to Exercise 1

The content of logiql/services/tdx/sales.logic should look as follows:

block(`sales) {
  alias_all(`lb:web:delim:schema),
  alias_all(`lb:web:delim:schema_abbr),
  alias_all(`lb:web:delim:binding),
  alias_all(`lb:web:delim:binding_abbr),

  alias_all(`lb:web:config:service),
  alias_all(`lb:web:config:service_abbr),
  alias_all(`lb:web:config:delim),


  clauses(`{

  file_definition_by_name["sales"] = fd,
    file_definition(fd) {
      file_delimiter[] = "|",
      column_headers[] = "ID,DAY,SALE,RETURN",
      column_formats[] = "integer,string,0+,0+"
    }.

  file_binding_by_name["sales_bindings"] = fb,
    file_binding(fb) {
      file_binding_definition_name[] = "sales",
      
    predicate_binding_by_name["core:sales:unit_sales"] =
        predicate_binding(_) {
        predicate_binding_columns[] = "ID,DAY,SALE"
      },
    
    predicate_binding_by_name["core:sales:unit_returns"] =
        predicate_binding(_) {
          predicate_binding_columns[] = "ID,DAY,RETURN"
        }
    }.

  })
} <-- .

Add the following to logiql/services/tdx/sales.logic:

  delim_service(x) {
    service_by_prefix("/dw/delim-sales"),
    delim_file_binding("sales_bindings")
  }.

Run the following command via the command line to import data/sales.dlm:

$ curl -X POST -H "Content-Type: text/csv" --data-binary data/wines/sales.dlm http://localhost:8080/dw/delim-sales

To query the total number of entries in core:sales:unit_sales, use the following command:
```
lb query discountwines '_[]=c <- agg<<c=count()>> core:sales:unit_sales(_,_,_).' 
```
To query the total number of entries in core:sales:unit_returns, use the following command:
```
lb query discountwines '_[]=c <- agg<<c=count()>> core:sales:unit_returns(_,_,_).' 
```

Answers to Exercise 2

The content of logiql/services/tdx/calendar.logic should look as follows:

block(`calendar) {
  alias_all(`lb:web:delim:schema),
  alias_all(`lb:web:delim:schema_abbr),
  alias_all(`lb:web:delim:binding),
  alias_all(`lb:web:delim:binding_abbr),

  alias_all(`lb:web:config:service),
  alias_all(`lb:web:config:service_abbr),
  alias_all(`lb:web:config:delim),


  clauses(`{


    file_definition_by_name["calendar"] = fd,
    file_definition(fd) {
      file_delimiter[] = "|",
      column_headers[] = "DAY,MONTH,YEAR,DAYOFWEEK,MONTHOFYEAR",
      column_formats[] = "string,string,string,string,string"
    }.

    file_binding_by_name["calendar_bindings"] = fb,
    file_binding(fb) {
      file_binding_definition_name[] = "calendar",
      file_binding_entity_creation[] = "accumulate",

      predicate_binding_by_name["core:hierarchies:calendar:day2month"] =
        predicate_binding(_) {
          predicate_binding_columns[] = "DAY,MONTH"
      },

      predicate_binding_by_name["core:hierarchies:calendar:month2year"]  =
        predicate_binding(_) {
        predicate_binding_columns[] = "MONTH,YEAR"
      },

      predicate_binding_by_name["core:hierarchies:calendar:day2dayOfWeek"] = 
        predicate_binding(_) {
        predicate_binding_columns[] = "DAY,DAYOFWEEK"
      },

      predicate_binding_by_name["core:hierarchies:calendar:month2monthOfYear"] = 
        predicate_binding(_) {
        predicate_binding_columns[] = "MONTH,MONTHOFYEAR"
      }
    }.
  })
} <-- .

Add the following to logiql/services/tdx/calendar.logic:

    delim_service(x) {
      service_by_prefix("/dw/delim-calendar"),
      delim_file_binding("calendar_bindings")
     }.

Your tests/lb_unit/suiteBasic/testHierarchy.lb file should look now similar to the example below:

transaction

/**
 * all days are associated with a day_of_week
 */
exec <doc>
core:hierarchies:calendar:day(day) 
  -> core:hierarchies:calendar:day2dayOfWeek(day, _).
</doc>

commit

The required modifications in tests/lb_unit/suiteBasic/testHierarchy.lb are displayed in bold:

transaction

/**
 * all days are associated with a day_of_week
 */
exec <doc>
core:hierarchies:calendar:day(day) 
  -> core:hierarchies:calendar:day2dayOfWeek(day, _).
</doc>


/**
 * all days are associated with a month
 */
exec <doc>
core:hierarchies:calendar:day(day) 
  -> core:hierarchies:calendar:day2month(day, _).
</doc>

commit

The required modifications in tests/lb_unit/suiteBasic/testHierarchy.lb are displayed in bold:

transaction

/**
 * all days are associated with a day_of_week
 */
exec <doc>
core:hierarchies:calendar:day(day) 
  -> core:hierarchies:calendar:day2dayOfWeek(day, _).
</doc>

/**
 * all days are associated with a month
 */
exec <doc>
core:hierarchies:calendar:day(day) 
  -> core:hierarchies:calendar:day2month(day, _).
</doc>

/**
 * all months are associated with a monthOfYear
 */
exec <doc>
core:hierarchies:calendar:month(month)
  -> core:hierarchies:calendar:month2monthOfYear(month,_).
</doc>

commit

The required modifications in tests/lb_unit/suiteBasic/testHierarchy.lb are displayed in bold:

transaction

/**
 * all days are associated with a day_of_week
 */
exec <doc>
core:hierarchies:calendar:day(day) 
  -> core:hierarchies:calendar:day2dayOfWeek(day, _).
</doc>

/**
 * all days are associated with a month
 */
exec <doc>
core:hierarchies:calendar:day(day) 
  -> core:hierarchies:calendar:day2month(day, _).
</doc>

/**
 * all months are associated with a monthOfYear
 */
exec <doc>
core:hierarchies:calendar:month(month)
  -> core:hierarchies:calendar:month2monthOfYear(month,_).
</doc>

/**
 * all months are associated with a year
 */
exec <doc>
core:hierarchies:calendar:month(month)
  -> core:hierarchies:calendar:month2year(month,_).
</doc>

commit

Answers to Exercise 3

You should remove the following lines in config.py

'echo "Adding hierarchy data"',
'lb exec ' + ws + ' --file data/hierarchies.logic',
'echo "Adding sales data"'
'lb exec ' + ws + ' --file data/sales.logic'

The new commands that load calendar and sales data are as follows:

'echo "Adding calendar data"',
'curl -X POST -H "Content-Type: text/csv" --data-binary @data/calendar.dlm http://localhost:8080/dw/delim-calendar',
'echo "Adding sales data"',
'curl -X POST -H "Content-Type: text/csv" --data-binary @data/sales.dlm http://localhost:8080/dw/delim-sales'

Answers to Exercise 4

Your test/services/delim-cal.py should look similar to the class below:

#! /usr/bin/env python

import sys
import os
import unittest
import time
import json
import subprocess
from datetime import datetime

sys.path.insert(0, '%s/lib/python' % os.environ.get('LOGICBLOX_HOME'))
sys.path.insert(0, '%s/lib/python' % os.environ.get('LB_WEBSERVER_HOME'))

import lb.web.testcase
import lb.web.service
import lb.web.admin

def get_client(path):
    return lb.web.service.DelimClient("localhost", 8080, "/dw/delim-" + path)


class TestDelimService(lb.web.testcase.PrototypeWorkspaceTestCase):

    prototype = "discountwines_test"

    def setUp(self):
        super(TestDelimService, self).setUp()
        self.maxDiff = None
        self.client = get_client("calendar")

    def post_calendar(self,client):
        client.post("""
            DAY|MONTH|YEAR|DAYOFWEEK|MONTHOFYEAR
            2013-01-01|Jan 2012|2012|Tuesday|January
            2013-01-02|Jan 2012|2012|Wednesday|January
            2013-01-03|Jan 2012|2012|Thursday|January
            """)
        self.assertDelimEqual(client.get(), """
            DAY|MONTH|YEAR|DAYOFWEEK|MONTHOFYEAR
            2013-01-01|Jan 2012|2012|Tuesday|January
            2013-01-02|Jan 2012|2012|Wednesday|January
            2013-01-03|Jan 2012|2012|Thursday|January
            """)
    
    def test_post_calendar(self):
        client = get_client("calendar")
        self.post_calendar(client)


def suite(args):
    suite = unittest.TestSuite()
    if (len(args) == 1):
        suite.addTest(unittest.makeSuite(TestDelimService))
    else:
        suite.addTest(TestDelimService(args[1]))
    return suite

if __name__ == '__main__':
    result = unittest.TextTestRunner(verbosity=2).run(suite(sys.argv))
    sys.exit(not result.wasSuccessful())

Your test/services/delim-sales.py should look similar to the class below:

#! /usr/bin/env python

import sys
import os
import unittest
import time
import json
import subprocess
from datetime import datetime

sys.path.insert(0, '%s/lib/python' % os.environ.get('LOGICBLOX_HOME'))
sys.path.insert(0, '%s/lib/python' % os.environ.get('LB_WEBSERVER_HOME'))

import lb.web.testcase
import lb.web.service
import lb.web.admin

def get_client(path):
    return lb.web.service.DelimClient("localhost", 8080, "/dw/delim-" + path)


class TestDelimService(lb.web.testcase.PrototypeWorkspaceTestCase):

    prototype = "discountwines_test_sales"

    def setUp(self):
        super(TestDelimService, self).setUp()
        self.maxDiff = None
        self.client = get_client("sales")

    def post_sales(self,client):
        client.post("""
            ID|DAY|SALE|RETURN
            1000|2011-01-01|879|940
            1001|2011-01-01|434|593
            1002|2011-01-01|960|1022
            """)
        self.assertDelimEqual(client.get(), """
            ID|DAY|SALE|RETURN
            1000|2011-01-01|879|940
            1001|2011-01-01|434|593
            1002|2011-01-01|960|1022
            """)
    
    def test_post_sales(self):
        client = get_client("sales")
        self.post_sales(client)


def suite(args):
    suite = unittest.TestSuite()
    if (len(args) == 1):
        suite.addTest(unittest.makeSuite(TestDelimService))
    else:
        suite.addTest(TestDelimService(args[1]))
    return suite

if __name__ == '__main__':
    result = unittest.TextTestRunner(verbosity=2).run(suite(sys.argv))
    sys.exit(not result.wasSuccessful())

		Next
		Getting Started