git.iko.soy/project-m36
on master help sign in
project-m36/docs/15_minute_tutorial.markdown
296 lines 15 kB history raw
1# Project:M36 in 15 Minutes
3Thank you for evaluating Project:M36, a new relational algebra engine written in Haskell. This tutorial will give you a general overview of the features of the database management system (DBMS). This tutorial assumes basic knowledge of your terminal's shell.
5## Prerequisites
7* any Linux, MacOS, or Windows computer with Docker installed
9## Download and Install
11Run a pre-built docker image:
13```docker run -it projectm36/project-m36 tutd```
15or build it yourself with GHC >= 8.6:
17```bash
18git clone https://github.com/agentm/project-m36.git
19cabal new-run tutd
20```
22## Some Basic TutorialD
24When you run the ```tutd``` interpreter, you are interacting with an in-memory database using a dialect of the TutorialD language which is described in numerous books by Chris J. Date. It is a language specifically designed for human interaction with the relational algebra.
26Note that TutorialD is strongly-typed- no type coercions take place automatically. For example, an integer will never be interpreted as a string or vice versa.
28### Load the Canonical Chris Date Example
30```
31TutorialD (master/main): :importexample cjdate
32```
34```:importexample``` (do not skip typing the preceding colon), loads a built-in example schema and executes it to change the state of the database. In this case, the relations "s" (supplier), "p" (product), and "sp" (suppliers-products) are now loaded.
36### Execute Relational Expressions
38```
39TutorialD (master/main): :showexpr s
40┌──────────┬────────┬───────────┬───────────┐
41│city::Text│s#::Text│sname::Text│status::Int│
42├──────────┼────────┼───────────┼───────────┤
43│"Paris" │"S2" │"Jones" │10 │
44│"Paris" │"S3" │"Blake" │30 │
45│"London" │"S1" │"Smith" │20 │
46│"London" │"S4" │"Clark" │20 │
47│"Athens" │"S5" │"Adams" │30 │
48└──────────┴────────┴───────────┴───────────┘
49```
51Use ```:showexpr``` (note the preceding colon again) followed by a TutorialD relational expression to execute the expression against the database's current state.
53A relational expression operates on the current database state (also known as "database context") and returns a relation containing the result. Note that relational expressions do not modify the state of the database.
55You will need to [learn TutorialD](/docs/tutd_tutorial.markdown) to create relational expressions here, but here are a few quick, simple examples:
57#### Joins
59```
60TutorialD (master/main): :showexpr s join sp
61┌──────────┬────────┬────────┬────────┬───────────┬───────────┐
62│city::Text│p#::Text│qty::Int│s#::Text│sname::Text│status::Int│
63├──────────┼────────┼────────┼────────┼───────────┼───────────┤
64│"London" │"P3" │400 │"S1" │"Smith" │20 │
65│"London" │"P4" │300 │"S4" │"Clark" │20 │
66│"Paris" │"P2" │200 │"S3" │"Blake" │30 │
67│"Paris" │"P1" │300 │"S2" │"Jones" │10 │
68│"London" │"P5" │400 │"S4" │"Clark" │20 │
69│"London" │"P6" │100 │"S1" │"Smith" │20 │
70│"London" │"P5" │100 │"S1" │"Smith" │20 │
71│"Paris" │"P2" │400 │"S2" │"Jones" │10 │
72│"London" │"P4" │200 │"S1" │"Smith" │20 │
73│"London" │"P1" │300 │"S1" │"Smith" │20 │
74│"London" │"P2" │200 │"S1" │"Smith" │20 │
75└──────────┴────────┴────────┴────────┴───────────┴───────────┘
76```
78All joins in TutorialD are similar to "NATURAL JOIN" in SQL.
80#### Restriction
82```
83TutorialD (master/main): :showexpr s where status=20
84┌──────────┬────────┬───────────┬───────────┐
85│city::Text│s#::Text│sname::Text│status::Int│
86├──────────┼────────┼───────────┼───────────┤
87│"London" │"S1" │"Smith" │20 │
88│"London" │"S4" │"Clark" │20 │
89└──────────┴────────┴───────────┴───────────┘
90```
92Restrictions are similar to the "WHERE" clause in SQL.
94#### Projection
96```
97TutorialD (master/main): :showexpr p{color,p#,weight}
98┌───────────┬────────┬───────────┐
99│color::Text│p#::Text│weight::Int│
100├───────────┼────────┼───────────┤
101│"Red" │"P6" │19 │
102│"Red" │"P1" │12 │
103│"Blue" │"P5" │12 │
104│"Green" │"P2" │17 │
105│"Red" │"P4" │14 │
106│"Blue" │"P3" │17 │
107└───────────┴────────┴───────────┘
108```
110Projection is equivalent to "SELECT <columns>" in SQL.
112#### Attribute Rename
114```
115TutorialD (master/main): :showexpr s rename {city as town}
116┌────────┬───────────┬───────────┬──────────┐
117│s#::Text│sname::Text│status::Int│town::Text│
118├────────┼───────────┼───────────┼──────────┤
119│"S2" │"Jones" │10 │"Paris" │
120│"S3" │"Blake" │30 │"Paris" │
121│"S4" │"Clark" │20 │"London" │
122│"S1" │"Smith" │20 │"London" │
123│"S5" │"Adams" │30 │"Athens" │
124└────────┴───────────┴───────────┴──────────┘
125```
127Renaming attributes is equivalent to "SELECT city AS town..." in SQL.
129#### Relational "true" and "false"
131The relational algebra includes two fundamental named relations: "true" and "false". (Chris Date calls them "TABLE_DUM" and "TABLE_DEE" which are poor names.)
133"true" is the name of the relation with no attributes and a set of one tuple of no attributes.
135```
136TutorialD (master/main): :showexpr true
137┌┐
138││
139├┤
140└┘
141```
143"false" is the name of the relation with no attributes and an empty tuples set.
145```
146TutorialD (master/main): :showexpr false
147┌┐
148││
149└┘
150```
152These relations cannot be represented in SQL databases but are fundamental to the mathematics of the relational algebra. Any relation projected against no attributes will return "true" if the relation contained any tuples or "false" otherwise.
154### Execute Database Context Expressions
156Database context expressions change the state of the database, much like "CREATE TABLE", "INSERT", "UPDATE", or "DELETE" in SQL. Database expressions in the interpreter are *not* prefixed by a colon because they are the default operation type.
158#### Create a Relation Variable
160```
161TutorialD (master/main): x:=relation{name Text, age Integer}{tuple{name "Steve", age 40},tuple{name "Mike", age 31}}
162TutorialD (master/main): :showexpr x
163┌────────┬──────────┐
164│age::Int│name::Text│
165├────────┼──────────┤
166│40 │"Steve" │
167│31 │"Mike" │
168└────────┴──────────┘
169```
171This is similar to SQL's "CREATE TABLE" operator.
173#### Insert Additional Tuples
175```
176TutorialD (master/main): x:=relation{name Text, age Integer}{tuple{name "Steve", age 40},tuple{name "Mike", age 31}}
177TutorialD (master/main): :showexpr x
178┌────────────┬──────────┐
179│age::Integer│name::Text│
180├────────────┼──────────┤
181│31 │"Mike" │
182│40 │"Steve" │
183└────────────┴──────────┘
185TutorialD (master/main): insert x relation{tuple{name "Bob", age 24}}
186TutorialD (master/main): :showexpr x
187┌────────────┬──────────┐
188│age::Integer│name::Text│
189├────────────┼──────────┤
190│31 │"Mike" │
191│40 │"Steve" │
192│24 │"Bob" │
193└────────────┴──────────┘
195```
197Note that the relational insertion is identical to "x:=x union relation{...}".
199#### Update Tuples in a Relation Variable
201```
202TutorialD (master/main): :showexpr s
203┌──────────┬────────┬───────────┬───────────────┐
204│city::Text│s#::Text│sname::Text│status::Integer│
205├──────────┼────────┼───────────┼───────────────┤
206│"London" │"S1" │"Smith" │20 │
207│"Athens" │"S5" │"Adams" │30 │
208│"Paris" │"S3" │"Blake" │30 │
209│"London" │"S4" │"Clark" │20 │
210│"Paris" │"S2" │"Jones" │10 │
211└──────────┴────────┴───────────┴───────────────┘
212TutorialD (master/main): update s where city="London" (status:=35)
213TutorialD (master/main): :showexpr s
214┌──────────┬────────┬───────────┬───────────────┐
215│city::Text│s#::Text│sname::Text│status::Integer│
216├──────────┼────────┼───────────┼───────────────┤
217│"Athens" │"S5" │"Adams" │30 │
218│"London" │"S1" │"Smith" │35 │
219│"London" │"S4" │"Clark" │35 │
220│"Paris" │"S3" │"Blake" │30 │
221│"Paris" │"S2" │"Jones" │10 │
222└──────────┴────────┴───────────┴───────────────┘
224```
225#### Deleting Tuples from a Relation Variable
227```
228TutorialD (master/main): :showexpr s
229┌──────────┬────────┬───────────┬───────────────┐
230│city::Text│s#::Text│sname::Text│status::Integer│
231├──────────┼────────┼───────────┼───────────────┤
232│"Paris" │"S2" │"Jones" │10 │
233│"Paris" │"S3" │"Blake" │30 │
234│"London" │"S1" │"Smith" │20 │
235│"Athens" │"S5" │"Adams" │30 │
236│"London" │"S4" │"Clark" │20 │
237└──────────┴────────┴───────────┴───────────────┘
238TutorialD (master/main): delete s where sname="Adams"
239TutorialD (master/main): :showexpr s
240┌──────────┬────────┬───────────┬───────────────┐
241│city::Text│s#::Text│sname::Text│status::Integer│
242├──────────┼────────┼───────────┼───────────────┤
243│"Paris" │"S2" │"Jones" │10 │
244│"London" │"S4" │"Clark" │20 │
245│"Paris" │"S3" │"Blake" │30 │
246│"London" │"S1" │"Smith" │20 │
247└──────────┴────────┴───────────┴───────────────┘
248```
250### Execute Transaction Graph Expressions
252Unlike typical SQL DBMSs, Project:M36 maintains a graph of transactions, similar to how git stores source patches. This allows the database context to be branched (with a name), merged, and enables recalling the database context from past transactions.
254#### Committing
256```
257TutorialD (master/main): :commit
258```
260#### Rollback
262```
263TutorialD (master/main): :rollback
264```
266#### Branching
268```
269TutorialD (master/main): :branch experimental
270TutorialD (experimental): employee:=relation{name Text, salary Integer}
271TutorialD (experimental): :commit
272```
274Note how the prompt changes to reflect the current branch's name.
276#### Jump to Branch
278```
279TutorialD (experimental): :jumphead master
280TutorialD (master/main): :showexpr employee
281ERR: RelVarNotDefinedError "employee"
282```
284Now we have jumped back to the master branch and we can confirm that the "employee" relation variable is not present here despite being committed. It was, in fact, committed only on the "experimental" branch.
286#### Other operations
288Of course, Project:M36 supports [aggregate operators](/docs/tutd_tutorial.markdown#aggregate-queries), [value functions](/docs/tutd_tutorial.markdown#extend), [database constraints](/docs/tutd_tutorial.markdown#constraints) including candidate and foreign keys, and other operators not mentioned in this brief tutorial.
290While this tutorial only covered an in-memory database, Project:M36 also supports ACID-compliant filesystem persistence as well as [server-based operation](/docs/server_mode.markdown).
292## Conclusion
294The TutorialD dialect used by Project:M36 is ideal for learning about the relational algebra because it is quick to write and grammatically unambiguous.
296The TutorialD language is not, however, suitable for production use because such use would be susceptible to the same string injection attacks which plague SQL. For integration with middleware, please try the [persistent library driver](/docs/persistent_library_driver.markdown) or [the native Haskell interface](/docs/projectm36_client_library.markdown).