git.iko.soy/project-m36
on 144_proxy_tupleable help sign in
project-m36/docs/15_minute_tutorial.markdown
299 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
8* GHC 7.10 or later version (Haskell compiler)
9* cabal (package downloader) installed
11## Download and Install
13```bash
14git clone https://github.com/agentm/project-m36.git
15cabal sandbox init
16cabal install --dependencies-only
17cabal run tutd
18```
20## Some Basic TutorialD
22When 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 book by Chris J. Date. It is a language specifically designed for human interaction with the relational algebra.
24Note 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.
26### Load the Canonical Chris Date Example
28```
29TutorialD (master/main): :importexample date
30```
32or
34```
35TutorialD (master/main): :importtud "scripts/DateExamples.tutd"
36```
38```:importtutd``` (do not skip typing the preceding colon), loads a file which contains a script written in the TutorialD dialect used by Project:M36 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.
40```:importexample date``` loads the date example directly without any file I/O, so it is safe to use with the websocket server.
42### Execute Relational Expressions
44```
45TutorialD (master/main): :showexpr s
46┌──────────┬────────┬───────────┬───────────┐
47│city::Text│s#::Text│sname::Text│status::Int│
48├──────────┼────────┼───────────┼───────────┤
49│"Paris" │"S2" │"Jones" │10 │
50│"Paris" │"S3" │"Blake" │30 │
51│"London" │"S1" │"Smith" │20 │
52│"London" │"S4" │"Clark" │20 │
53│"Athens" │"S5" │"Adams" │30 │
54└──────────┴────────┴───────────┴───────────┘
55```
57Use ```:showexpr``` (note the preceding colon again) followed by a TutorialD relational expression to execute the expression against the database's current state.
59A 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.
61You will need to [learn TutorialD](/docs/tutd_tutorial.markdown) to create relational expressions here, but here are a few quick, simple examples:
63#### Joins
65```
66TutorialD (master/main): :showexpr s join sp
67┌──────────┬────────┬────────┬────────┬───────────┬───────────┐
68│city::Text│p#::Text│qty::Int│s#::Text│sname::Text│status::Int│
69├──────────┼────────┼────────┼────────┼───────────┼───────────┤
70│"London" │"P3" │400 │"S1" │"Smith" │20 │
71│"London" │"P4" │300 │"S4" │"Clark" │20 │
72│"Paris" │"P2" │200 │"S3" │"Blake" │30 │
73│"Paris" │"P1" │300 │"S2" │"Jones" │10 │
74│"London" │"P5" │400 │"S4" │"Clark" │20 │
75│"London" │"P6" │100 │"S1" │"Smith" │20 │
76│"London" │"P5" │100 │"S1" │"Smith" │20 │
77│"Paris" │"P2" │400 │"S2" │"Jones" │10 │
78│"London" │"P4" │200 │"S1" │"Smith" │20 │
79│"London" │"P1" │300 │"S1" │"Smith" │20 │
80│"London" │"P2" │200 │"S1" │"Smith" │20 │
81└──────────┴────────┴────────┴────────┴───────────┴───────────┘
82```
84All joins in TutorialD are similar to "NATURAL JOIN" in SQL.
86#### Restriction
88```
89TutorialD (master/main): :showexpr s where status=20
90┌──────────┬────────┬───────────┬───────────┐
91│city::Text│s#::Text│sname::Text│status::Int│
92├──────────┼────────┼───────────┼───────────┤
93│"London" │"S1" │"Smith" │20 │
94│"London" │"S4" │"Clark" │20 │
95└──────────┴────────┴───────────┴───────────┘
96```
98Restrictions are similar to the "WHERE" clause in SQL.
100#### Projection
102```
103TutorialD (master/main): :showexpr p{color,p#,weight}
104┌───────────┬────────┬───────────┐
105│color::Text│p#::Text│weight::Int│
106├───────────┼────────┼───────────┤
107│"Red" │"P6" │19 │
108│"Red" │"P1" │12 │
109│"Blue" │"P5" │12 │
110│"Green" │"P2" │17 │
111│"Red" │"P4" │14 │
112│"Blue" │"P3" │17 │
113└───────────┴────────┴───────────┘
114```
116Projection is equivalent to "SELECT <columns>" in SQL.
118#### Attribute Rename
120```
121TutorialD (master/main): :showexpr s rename {city as town}
122┌────────┬───────────┬───────────┬──────────┐
123│s#::Text│sname::Text│status::Int│town::Text│
124├────────┼───────────┼───────────┼──────────┤
125│"S2" │"Jones" │10 │"Paris" │
126│"S3" │"Blake" │30 │"Paris" │
127│"S4" │"Clark" │20 │"London" │
128│"S1" │"Smith" │20 │"London" │
129│"S5" │"Adams" │30 │"Athens" │
130└────────┴───────────┴───────────┴──────────┘
131```
133Renaming attributes is equivalent to "SELECT city AS town..." in SQL.
135#### Relational "true" and "false"
137The relational algebra includes two fundamental named relations: "true" and "false". (Chris Date calls them "TABLE_DUM" and "TABLE_DEE" which are poor names.)
139"true" is the name of the relation with no attributes and a set of one tuple of no attributes.
141```
142TutorialD (master/main): :showexpr true
143┌┐
144││
145├┤
146└┘
147```
149"false" is the name of the relation with no attributes and an empty tuples set.
151```
152TutorialD (master/main): :showexpr false
153┌┐
154││
155└┘
156```
158These 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.
160### Execute Database Context Expressions
162Database 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.
164#### Create a Relation Variable
166```
167TutorialD (master/main): x:=relation{name Text, age Int}{tuple{name "Steve", age 40},tuple{name "Mike", age 31}}
168TutorialD (master/main): :showexpr x
169┌────────┬──────────┐
170│age::Int│name::Text│
171├────────┼──────────┤
172│40 │"Steve" │
173│31 │"Mike" │
174└────────┴──────────┘
175```
177This is similar to SQL's "CREATE TABLE" operator.
179#### Insert Additional Tuples
181```
182TutorialD (master/main): x:=relation{name Text, age Int}{tuple{name "Steve", age 40},tuple{name "Mike", age 31}}
183TutorialD (master/main): :showexpr x
184┌────────┬──────────┐
185│age::Int│name::Text│
186├────────┼──────────┤
187│40 │"Steve" │
188│31 │"Mike" │
189└────────┴──────────┘
190TutorialD (master/main): insert x relation{tuple{name "Bob", age 24}}
191TutorialD (master/main): :showexpr x
192┌────────┬──────────┐
193│age::Int│name::Text│
194├────────┼──────────┤
195│40 │"Steve" │
196│24 │"Bob" │
197│31 │"Mike" │
198└────────┴──────────┘
199```
201Note that the relational insertion is identical to "x:=x union relation{...}".
203#### Update Tuples in a Relation Variable
205```
206TutorialD (master/main): :showexpr s
207┌──────────┬────────┬───────────┬───────────┐
208│city::Text│s#::Text│sname::Text│status::Int│
209├──────────┼────────┼───────────┼───────────┤
210│"Paris" │"S2" │"Jones" │10 │
211│"Paris" │"S3" │"Blake" │30 │
212│"London" │"S1" │"Smith" │20 │
213│"London" │"S4" │"Clark" │20 │
214│"Athens" │"S5" │"Adams" │30 │
215└──────────┴────────┴───────────┴───────────┘
216TutorialD (master/main): update s where city="London" (status:=35)
217TutorialD (master/main): :showexpr s
218┌──────────┬────────┬───────────┬───────────┐
219│city::Text│s#::Text│sname::Text│status::Int│
220├──────────┼────────┼───────────┼───────────┤
221│"London" │"S4" │"Clark" │35 │
222│"London" │"S1" │"Smith" │35 │
223│"Paris" │"S2" │"Jones" │10 │
224│"Paris" │"S3" │"Blake" │30 │
225│"Athens" │"S5" │"Adams" │30 │
226└──────────┴────────┴───────────┴───────────┘
227```
228#### Deleting Tuples from a Relation Variable
230```
231TutorialD (master/main): :showexpr s
232┌──────────┬────────┬───────────┬───────────┐
233│city::Text│s#::Text│sname::Text│status::Int│
234├──────────┼────────┼───────────┼───────────┤
235│"Paris" │"S2" │"Jones" │10 │
236│"Paris" │"S3" │"Blake" │30 │
237│"London" │"S1" │"Smith" │20 │
238│"London" │"S4" │"Clark" │20 │
239│"Athens" │"S5" │"Adams" │30 │
240└──────────┴────────┴───────────┴───────────┘
241TutorialD (master/main): delete s where sname="Adams"
242TutorialD (master/main): :showexpr s
243┌──────────┬────────┬───────────┬───────────┐
244│city::Text│s#::Text│sname::Text│status::Int│
245├──────────┼────────┼───────────┼───────────┤
246│"Paris" │"S2" │"Jones" │10 │
247│"Paris" │"S3" │"Blake" │30 │
248│"London" │"S1" │"Smith" │20 │
249│"London" │"S4" │"Clark" │20 │
250└──────────┴────────┴───────────┴───────────┘
251```
253### Execute Transaction Graph Expressions
255Unlike 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.
257#### Committing
259```
260TutorialD (master/main): :commit
261```
263#### Rollback
265```
266TutorialD (master/main): :rollback
267```
269#### Branching
271```
272TutorialD (master/main): :branch experimental
273TutorialD (experimental): employee:=relation{name Text, salary Int}
274TutorialD (experimental): :commit
275```
277Note how the prompt changes to reflect the current branch's name.
279#### Jump to Branch
281```
282TutorialD (experimental): :jumphead master
283TutorialD (master/main): :showexpr employee
284ERR: RelVarNotDefinedError "employee"
285```
287Now 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.
289#### Other operations
291Of 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.
293While 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).
295## Conclusion
297The TutorialD dialect used by Project:M36 is ideal for learning about the relational algebra because it is quick to write and grammatically unambiguous.
299The 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).