ramsh 1.0.2
Relational Algebra Modeling Shell
Requires Python: >=3.6
Keywords: Relational Algebra,Modeling,Shell,CLI

# ramsh (**R**elational **A**lgebra **M**odeling **S**hell)

ramsh is a **R**elational **A**lgebra **M**odeling **S**hell. It provides a command-line interface and a DSL to define, populate and query a relational model. This tool is intended for educational use only, to illustrate in a more interactive way the underlying concepts of relational databases

## Quick Screencast
[![asciicast](https://asciinema.org/a/Cfsl6wVD4WoCqTYlo1VP527Kq.svg)](https://asciinema.org/a/Cfsl6wVD4WoCqTYlo1VP527Kq)
## Installation

ramsh can be installed via via pip:

```
$ pip install ramsh
```

## Getting started
## Defining a relational model
ramsh can be executed by running the ```ramsh``` command from a terminal :
```
$ ramsh
```
If the command runs without error, you will be taken to a command line interface that allows you to define a relational model. **By default multiline mode is disabled**. It is sometimes necessary to toggle multiline mode in order to be able to define several relationships at the same time. This is done by pressing the [F3] key. To execute an instruction in the multiline mode, you must use the [Alt-Enter] combination. You can then define your relational model as follows:

```
>>> Movie(_Code, Title, Genre, Year, #DirectorID) 
      DirectorID references Director.ID
    Director(_ID, Name, Nationality)
>>> 
```
The syntax for defining a relatoinal model is, as you can see, self-describing :
- A relation is defined by specifying its name followed by a parenthesis and then the list of attributes separated by a comma.
- The attributes that form the primary key must be preceded by the symbol _
- Foreign key attributes must be preceded by the # symbol and the attributes they reference must be specified just after the definition of the Relation.

## Populating the relational model
Once you have defined your relational model you can populate it using the ```add``` statement as follows:

**Note:** *It may be better to disable the multiline mode ([F3] key) from here because each of the instructions that follow are single-line instructions.*
```
>>> Director.add(1, 'Robert Zemeckis', 'US')
>>> Director.add(2, 'Stanley Kubrick', 'US')
>>> Director.add(3, 'David Lynch', 'US')
>>> Director.add(4, 'Luc besson', 'FR')
>>> Movie.add(0120663, 'Eyes wide shut' , 'Drame' , 1999, 2)
>>> Movie.add(0116922, 'Lost Highway' , 'Thriller', 1997, 3)
>>> Movie.add(0110413, 'Leon' , 'Crime' , 1994, 4)
>>> Movie.add(2872732, 'Lucy' , 'Action' , 2014, 4)
>>> Movie.add(0119116, 'The Fifth Element', 'Action' , 1997, 4)
>>> Movie.add(0166924, 'Mullholland Drive' , 'Thriller', 2001, 3)
>>> Movie.add(0062622, "2001: A Space Odyssey", 'Adventure', 1968, 2) 
>>> Movie.add(0081505, 'The shining' , 'Horror' , 1980, 2)
>>> Movie.add(0109830, 'Forrest Gump' , 'Drame' , 1994, 1)
>>> Movie.add(0118884, 'Contact' , 'Drame' , 1997, 1)
>>> Movie.add(0066921, 'A Clockwork Orange' , 'Crime' , 1971, 2)
>>> Movie.add(0093058, 'Full Metal Jacket' , 'War' , 1987, 2)
>>> Movie.add(0090756, 'Blue Velvet' , 'Mystery' , 1986, 3)
>>> 
```
**Note:** *At this stage of development, racli only supports three data types: Integer, String and Float. It is unlikely that other data types will be supported in the future as this tool is purely educational.*

## Check if everything is OK !
You can verify that the relations you have populated actually contain the tuples you have added by entering the name of the relation followed by the [Enter] key (or [Alt-Enter] if you have not disabled multiline mode).
```
>>> Director
+----+-----------------+-------------+
| ID |       Name      | Nationality |
+----+-----------------+-------------+
| 1  | Robert Zemeckis |      US     |
| 2  | Stanley Kubrick |      US     |
| 4  |    Luc besson   |      FR     |
| 3  |   David Lynch   |      US     |
+----+-----------------+-------------+
>>> Movie
+---------+-----------------------+-----------+------+------------+
|   Code  |         Title         |   Genre   | Year | DirectorID |
+---------+-----------------------+-----------+------+------------+
|  116922 |      Lost Highway     |  Thriller | 1997 |     3      |
| 2872732 |          Lucy         |   Action  | 2014 |     4      |
|  93058  |   Full Metal Jacket   |    War    | 1987 |     2      |
|  90756  |      Blue Velvet      |  Mystery  | 1986 |     3      |
|  110413 |          Leon         |   Crime   | 1994 |     4      |
|  166924 |   Mullholland Drive   |  Thriller | 2001 |     3      |
|  119116 |   The Fifth Element   |   Action  | 1997 |     4      |
|  62622  | 2001: A Space Odyssey | Adventure | 1968 |     2      |
|  118884 |        Contact        |   Drame   | 1997 |     1      |
|  120663 |     Eyes wide shut    |   Drame   | 1999 |     2      |
|  109830 |      Forrest Gump     |   Drame   | 1994 |     1      |
|  81505  |      The shining      |   Horror  | 1980 |     2      |
|  66921  |   A Clockwork Orange  |   Crime   | 1971 |     2      |
+---------+-----------------------+-----------+------+------------+
```
You can also display the specification of your relational model to see what relationships exist by running the special command ```.model```

```
>>> .model
• Movie(Code, Title, Genre, Year, #DirectorID)
	DirectorID references Director.ID
• Director(ID, Name, Nationality)
```

## Querying the relational model
At this stage, it is now possible to query your relational model using standard relational algebra operations (projection, restriction, join, union, intersection, difference and Cartesian product). The two tables below show the symbol, meaning and syntax of each of the algebraic operations supported by racli

### Relational operations
| Operation  | Meaning | Syntax/Example |
| :-----------: | ------------- | -------- |
| ``` ⋈ ```  | Join  | ``` Movie ⋈(DirectorID = ID) Director ```
| ``` σ ```  | Restriction  | ``` σ(Year > 2015) Movie ```
| ``` π ``` | Projection  | ``` π(Name, Nationality) Director ```
### Set operations
| Operation  | Meaning | Syntax/Example |
| :-----------: | ------------- | -------- |
| ``` U ```  | Union  | ``` HorrorMovies U ComedyMovies ```
| ``` ∩ ```  | Intersection  | ``` HorrorMovies ∩ ComedyMovies ```
| ``` - ``` | Difference  | ``` LynchMovies - ComedyMovies ```
| ``` X ``` | Cartesian Product  | ``` Movie X Director ```

**Note:** *It may be difficult to write some symbols such as ⋈, σ, π, and ∩ using a combination of keys. To remedy this, racli allows you to quickly insert an operation by pressing the [tab] key.*

The result of an operation is displayed after the execution of the operation as below:
```
>>> π(Title, Year) Movie
+-----------------------+------+
|         Title         | Year |
+-----------------------+------+
|      Blue Velvet      | 1986 |
|   The Fifth Element   | 1997 |
|        Contact        | 1997 |
|   A Clockwork Orange  | 1971 |
|          Lucy         | 2014 |
|      The shining      | 1980 |
|      Lost Highway     | 1997 |
|   Mullholland Drive   | 2001 |
|      Forrest Gump     | 1994 |
|     Eyes wide shut    | 1999 |
|   Full Metal Jacket   | 1987 |
|          Leon         | 1994 |
| 2001: A Space Odyssey | 1968 |
+-----------------------+------+
```
The result can also be saved in a temporary relation as below:
```
>>> R1 = π(Title, Year) Movie
```
This will create a new temporary relation called R1 which can in turn be queried using standard relational algebra operations.

## Load a relational model from a file
It is also possible to open a pre-populated relational model by specifying at the beginning of the file the relational model followed by zero or more add operations. The following is the content of a file called ```movies.ramsh``` :

```
Movie(_Code, Title, Genre, Year, #DirectorID)
  DirectorID references Director.Id
Director(_Id, Name, Nationality)

.fk_off

Movie.add(0120663, 'Eyes wide shut' , 'Drame' , 1999, 2)
Movie.add(0116922, 'Lost Highway' , 'Thriller', 1997, 3)
Movie.add(0110413, 'Leon' , 'Crime' , 1994, 4)
Movie.add(2872732, 'Lucy' , 'Action' , 2014, 4)
Movie.add(0119116, 'The Fifth Element', 'Action' , 1997, 4)
Movie.add(0166924, 'Mullholland Drive' , 'Thriller', 2001, 3)
Movie.add(0062622, "2001: A Space Odyssey", 'Adventure', 1968, 2) 
Movie.add(0081505, 'The shining' , 'Horror' , 1980, 2)
Movie.add(0109830, 'Forrest Gump' , 'Drame' , 1994, 1)
Movie.add(0118884, 'Contact' , 'Drame' , 1997, 1)
Movie.add(0066921, 'A Clockwork Orange' , 'Crime' , 1971, 2)
Movie.add(0093058, 'Full Metal Jacket' , 'War' , 1987, 2)
Movie.add(0090756, 'Blue Velvet' , 'Mystery' , 1986, 3)

Director.add(1, 'Robert Zemeckis', 'US')
Director.add(2, 'Stanley Kubrick', 'US')
Director.add(3, 'David Lynch', 'US')
Director.add(4, 'Luc besson', 'FR')

.fk_on
```
It is possible to disable the foreign key check before populating the relational model and to re-enable it afterwards using the ```.fk_off``` and ```.fk_on``` commands respectively. This will allow us, in this example, to add movies in the Movie relation without adding their directors first.

To open a file you just have to specify its name when running ramsh as follows:
```
$ ramsh movies.ramsh

>>> .model
• Movie(Code, Title, Genre, Year, #DirectorID)
	DirectorID references Director.Id
• Director(Id, Name, Nationality)
>>> Director
+----+-----------------+-------------+
| Id |       Name      | Nationality |
+----+-----------------+-------------+
| 4  |    Luc besson   |      FR     |
| 3  |   David Lynch   |      US     |
| 1  | Robert Zemeckis |      US     |
| 2  | Stanley Kubrick |      US     |
+----+-----------------+-------------+
>>> 
```


## Special Commands/Keyboard Shortcuts
### Commands
- ``` .model ``` Show the relational model
- ``` .exit ```  Exit ramsh
- ``` .fk_off ``` Disable foreign keys
- ``` .fk_on ``` Enable foreign keys
### Keyboard shortcuts
- ```[F3]``` Toggle multiline
- ```[Tab]``` Insert operation
- ```[Alt-Enter]``` Run command when multiline mode is enabled
- ```[Ctrl-l]``` Clear screen

## Acknowledgments
We would like to thank the contributors of the following packages without whom ramsh would not have been possible:
- **Python Prompt Toolkit** : https://github.com/prompt-toolkit/python-prompt-toolkit
- **Click** : https://click.palletsprojects.com/en/8.0.x/
- **Pygments** : https://pygments.org/
- **textX** : https://github.com/textX/textX
- **PrettyTable** : https://github.com/jazzband/prettytable