JDB/Examples

There are four example databases included with the system:

• sandp is the standard Supplier and Parts database with values matching those in the SQLite Addon
• northwind is the Northwind database
• sed is a Section/Employee database
• vr is essentially equivalent to the database in the mapped database lab

sandp

This is included as part of the main JDB system, and defined in the jdb locale. There are three main functions: buildsandp, opensandp and demosandp. Each takes an argument of a directory path for the sandp data files. For example, try:

load 'data/jdb'
demosandp_jdb_ jpath '~temp'

This builds the database, then shows some table queries in the session.

northwind

This is the sample database included in MS Access and SQL Server. Load the script, then try:

load 'data/jdb'
load 'data/jdb/northwind'
db_demo ''

For example:

   Reads__D 'from OrderDetails where Customers.Country=UK and Quantity>60'
+-------+---------+---------+--------+--------+
|OrderID|ProductID|UnitPrice|Quantity|Discount|
+-------+---------+---------+--------+--------+
|10359  |31       |0        |70      |0.05    |
|10359  |60       |0        |80      |0.05    |
|10472  |24       |0        |80      |0.05    |
+-------+---------+---------+--------+--------+

sed

This is defined in script sed.ijs, and loads into the base locale. The function demosed runs a demo. Note the last examples show the automatic joins from referencing columns in another table:

load 'data/jdb'
load 'data/jdb/sed'
demosed jpath '~temp'

Table e has two columns:

   Reads__locD 'from e'
+---------+------+
|ename    |secnum|
+---------+------+
|Smith    |4     |
|Jones    |3     |
|Robinson |4     |
|Jasper   |3     |
|Steinberg|3     |
|Rafferty |1     |
+---------+------+

Columns in referenced tables can be used in queries. A name needs a table qualifier (table.column) to resolve any ambiguity:

   Reads__locD 'ename,secnum,r.name,sdiv,s.name from e where r.name=Rome'
+---------+------+------+-----+-----------+
|ename    |secnum|r.name|sdiv |s.name     |
+---------+------+------+-----+-----------+
|Jones    |3     |Rome  |Truck|Engineering|
|Jasper   |3     |Rome  |Truck|Engineering|
|Steinberg|3     |Rome  |Truck|Engineering|
+---------+------+------+-----+-----------+

A column query of ** gives all referenced columns, excluding duplicates, i.e. excluding columns that reference a single column in another table. Columns are shown in order of reference, i.e. for each column recursively get references before showing the next column:

   Reads__locD '** from e where r.name=Rome'
+---------+---+-----+---+------+-----+-----------+
|ename    |sno|sdiv |rno|r.name|popul|s.name     |
+---------+---+-----+---+------+-----+-----------+
|Jones    |33 |Truck|5  |Rome  |2000 |Engineering|
|Jasper   |33 |Truck|5  |Rome  |2000 |Engineering|
|Steinberg|33 |Truck|5  |Rome  |2000 |Engineering|
+---------+---+-----+---+------+-----+-----------+

A column query of *+ is similar, except showing only referenced key columns:

   Reads__locD '*+ from e where r.name=Rome'
+---------+---+-----+---+------+
|ename    |sno|sdiv |rno|r.name|
+---------+---+-----+---+------+
|Jones    |33 |Truck|5  |Rome  |
|Jasper   |33 |Truck|5  |Rome  |
|Steinberg|33 |Truck|5  |Rome  |
+---------+---+-----+---+------+

vr

This is defined in script vr.ijs, and loads into the base locale. The build argument is a size and path. To match the vr lab, use a size of 1e6 - this will take several seconds to build, for example:

   load 'data/jdb'
   load 'data/jdb/vr'

   buildvr 1e4;jpath '~temp'       NB. create database of size 1e4
   demovr openvr jpath '~temp'     NB. run a demo

   d=: buildvr 1e6;jpath '~temp'   NB. create database of size 1e6
   Read__d 'count year from vr'
+----+-------+
|year|1000000|
+----+-------+

   Reads__d 'from vr where make=Ford and year=1997 and fine>0'
+-------+----+-----+----+-----+---------+--------+-----------+
|lic    |make|color|year|fine |firstname|lastname|comment    |
+-------+----+-----+----+-----+---------+--------+-----------+
|1034781|Ford|Pink |1997|28.64|Carlos   |Mills   |Outstanding|
|1044553|Ford|Pink |1997|15.31|Skip     |McKee   |Outstanding|
|1075903|Ford|Blue |1997|22.59|Lewis    |Power   |           |
|1051492|Ford|Mauve|1997|50.51|Harlan   |Tang    |           |
|1063528|Ford|Blue |1997|16.41|Boris    |McKee   |Outstanding|
+-------+----+-----+----+-----+---------+--------+-----------+