User:Andrew Nikitin/JDB

Introduction

Some of J primitives are close analogs of different "clauses" of SQL SELECT statement. For example:

• /: ↔ ORDER BY
• /.(key adverb) ↔ GROUP BY
• SELECT ↔ {

This prompts for a possibility of translation of SQL SELECT statement into J verb that performs equivalent operation.

Next step is to make cover verbs that mimic J ODBC interface and use this API to develop applications. The benefit is that J database can be used for relatively small databases in neatly packaged standalone applications. Change of driver (J DB to ODBC) would allow to scale up or provide client-server capabilities.

Representation of a relational database in J system

{There is some theory behind relational databases and the word "relational" is a term with precise meaning, rather than just a marketing label. I've realized too late in my carreer that many people involved in programming RDB apps do not know this and use so called "common sense" when design their table structure and implement software. It would be nice to say some words on this topic that do not sound condescending and provide a link to a reasonably academic RDB tutorial}

It seems reasonable to store each RDB in a locale.

Traditionaly RDBMSes allow to specify certain relations between tables and attributes and enforce them -- to a certain level. There might be a need to store this information in predefined nouns. Among such pieces of metainformation are

• attribute types
• attribute uniqueness
• table primary key
• one-to-one and one-to-many relationship between attributes of different relations.

Traditional approach

Each table is represented as boxed rank 2 array with rows being records and columns being fields. Since table name may have non-identifier characters there must be a table_name↔J_noun_name conversion procedure. There is also need to store column names, there types and their corresponding indices in a row(=record).

Example: Table 'staff' with fields 'name', 'age', 'salary'

Table is defined as

  NAME=:0
  AGE=:1
  SALARY=:2
  staff=:5$,:'Jeffry';15;1280.34

Fields are accessed as

  NAME&{"1 staff
  AGE&{"1 staff
  SALARY&{"1 staff

Query returns boxed rank 2 array of result.

Pros:

• • intuitive
  • easy to maintain integrity

Cons:

• • boxes lead to storage inefficiency
  • processing verbs will have many extra boxing/unboxing steps which would

affect clarity

• • no possibility to store tables in mapped files [is it true?]

Sketch:

NB. RDB is locale
TABLES NB. describes table names structure; at minimum may look like this
┌───────┬────────┬─────────────────────┐
│TABLE 1│J_NOUN_1│┌───────┬───────┬───┐│
│       │        ││Field 1│Field 2│...││
│       │        │└───────┴───────┴───┘│
├───────┼────────┼─────────────────────┤
│TABLE 2│J_NOUN_2│┌───────┬───────┬───┐│
│       │        ││Field 1│Field 2│...││
│       │        │└───────┴───────┴───┘│
├───────┼────────┼─────────────────────┤
│...    │...     │...                  │
└───────┴────────┴─────────────────────┘
   ('TABLE 1';'TABLE 2';'...'),.(_2]\'Field 1';'J_NOUN_1';'Field 2';'J_NOUN_2';'...';'...'),.((<'Field 1';'Field 2';'...'),(<'Field 1';'Field 2';'...'),(,<'...'))

APL approach (inverted tables)

Each noun is a vector of values and represents single field of a single table. Several nouns matching in size represent a table. Entire locale may be assign to a table, or several tables may need to reside in a single locale (in which case some predfined NOUN with metainformation is needed)

Example: Table 'staff' with fields 'name', 'age', 'salary' Fields are accessed as:

  name_staff_
  age_staff_
  salary_staff_

Query creates nouns in a predefined locale, which can be accessed in a similar manner.

Pros:

• • J primitives more closely match relational operations
  • avoids boxing, which allows for better efficiency and possibility to store nouns in mapped files

Cons:

• • difficult to maintain integrity during insertion/deletion

TABLE
┌───────┬──────────────────┐
│TABLE 1│┌───────┬────────┐│
│       ││Field 1│J_NOUN_1││
│       │├───────┼────────┤│
│       ││Field 2│J_NOUN_2││
│       │├───────┼────────┤│
│       ││...    │...     ││
│       │└───────┴────────┘│
├───────┼──────────────────┤
│TABLE 2│┌───────┬────────┐│
│       ││Field 1│J_NOUN_3││
│       │├───────┼────────┤│
│       ││Field 2│J_NOUN_4││
│       │├───────┼────────┤│
│       ││...    │...     ││
│       │└───────┴────────┘│
├───────┼──────────────────┤
│...    │...               │
└───────┴──────────────────┘
   ('TABLE 1';'TABLE 2';'...'),.(<_2]\'Field 1';'J_NOUN_1';'Field 2';'J_NOUN_2';'...';'...'),(<_2]\'Field 1';'J_NOUN_3';'Field 2';'J_NOUN_4';'...';'...'),(<'...')

Implementation of relational operations

{Excerpts from some sample scripts}

SQL=:0 : 0
select a1 as name,a2 as age,a3
from zzz
where (age>0)
AND (age<5)
order by name
)

  PS=:identify clauses SQL
  PS
┌────────┬────────────────────────┐
│SELECT  │a1 as name,a2 as age,a3 │
├────────┼────────────────────────┤
│FROM    │zzz                     │
├────────┼────────────────────────┤
│WHERE   │(age>0) AND (age<5)     │
├────────┼────────────────────────┤
│ORDER BY│name                    │
└────────┴────────────────────────┘
   compile PS
((a2>0) AND (a2<5)) # ((<"_1 a1),.(<"_1 a2),.(<"_1 a3))

{verbs compile, identify, clauses etc are all defined in a script and are half baked}

Standardised/automatically generated GUI to access tables

In addition to maintaining database integrity metainformation can be used to automatically generate GUI for table access.

Traditionally 2 kinds of forms are provided for each table:

• "browse" mode shows a table in a grid one record per line (can be implemeted with a grid component)
• "record edit" for each record provides a form (dialog box) to enter fields. One-to-many relationship information can be used to allow selection of primary keys from appropriate table