Заметки по Rel

Заметки по Rel

9.1.2017 20:39

Заметки по библиотеке Rel

Использование NULL

Глобальные последовательности

Автоматические поля/колонки

Alias (псевдонимы)

Типы данных в Rel

Схемы баз данных в Rel

Методы организации запросов и изменений в базе

Методы вставки записей (строк) в таблицы

Оператор join

Индексирование

Метод проверки существования exists/1

Первичные и внешние ключи

Оператор group и агрегаторы значений

Обработка результата запросов

Корректировка существующей базы

Все примеры ниже – рабочие. Для запуска надо их скопировать в Libretto IDE.

Использование NULL

Добавил NULL. Для этого используется метод rel/null. Например, 

use com/teacode/rel
use libretto/h2

def main = {

  fix tbl = 
    rel/table("TABLE") {
      stringColumn("NAME")
      intColumn("AGE")
    }

  fix base = rel/db(h2/temp)

  base.w: {
    tbl.create
    
    (tbl.row(('John, 19)),
      tbl.row(('Ann, null)),
      tbl.row((null, 100))). insert

    rel/from(tbl).select(tbl). out. showtable
  }
}

Результат (включая сгенерированный SQL-код): 

SELECT TABLE.NAME, TABLE.AGE
FROM TABLE
┌────────────┬───────────┐
│ TABLE.NAME │ TABLE.AGE │
├────────────┼───────────┤
│ John       │ 19        │
├────────────┼───────────┤
│ Ann        │ NULL      │
├────────────┼───────────┤
│ NULL       │ 100       │
└────────────┴───────────┘

Опция notNull запрещает использовать NULL в колонке: 

use com/teacode/rel
use libretto/h2

def main = {

  fix tbl = 
    rel/table("TABLE") {
      stringColumn("NAME"). notNull
      intColumn("AGE")
    }

  fix base = rel/db(h2/temp)

  base.w: {
    tbl.create
    (tbl.row(('John, 19)),
      tbl.row(('Ann, null)),
      tbl.row((null, 100))). insert

    rel/from(tbl).select(tbl).result.showtable
  } 
}

Результат: 

org.h2.jdbc.JdbcSQLException: NULL not allowed for column "NAME"; SQL statement:
INSERT INTO TABLE(NAME, AGE) VALUES(?, ?) [23502-169]

Глобальные последовательности

Реализовал Sequence – глобальный генератор чисел, поскольку часто использую глобальные последовательности в своих проектах. Sequence была недавно включена в стандарт SQL. Реализована в основных СУБД (Oracle, MS SQL, PostgreSQL, DB2, H2).

Теперь Sequence является компонентой абстрактной модели (схемы) Rel. Она порождается одноместным методом rel/sequence(sname:String!). Пока работает только на Bigint. Методы-опции start/1 и step/1 позволяют задать начальное значение и шаг генератора. По умолчанию стартовое значение и шаг равны 1.

Управляют Sequence три метода:

  • create: создание новой последовательности в базе на основе абстрактного объекта Sequence.
  • next: получение следующего значения последовательности.
  • curr: получение текущего значения последовательности без сдвига.

Методы действуют внутри транзакций, однако никогда не откатывают значения.

Пример: 

use com/teacode/rel
use libretto/h2

def main = {
  fix base = rel/db(h2/temp)

  fix seq = rel/sequence("SEQ").start(1000).step(50)

  base.w: {
    seq.create

    println( seq.next )
    println( seq.curr )
    println( seq.next )
  }
}
Печатает: 
1000
1000
1050

Для создания последовательности из примера в H2 Rel автоматически генерирует следующий код: 

CREATE SEQUENCE SEQ START WITH 1000 INCREMENT BY 50

Автоматические поля/колонки

Среди сонма инструментов для порождения полей с автоматически генерируемыми значениями, пока выбрал для поддержки в Rel три варианта:

Identity. Этот тип поля соответствует IDENTITY в SQL. По умолчанию является первичным ключом. Для его порождения в Rel реализован метод 

  def Table identityColumn(name: String!)

Методы-опции start/1 и step/1 позволяют задать начальное значение и шаг генератора. По умолчанию стартовое значение и шаг равны 1.

AutoIncrement. Похоже на Identity, но по умолчанию не является первичным ключом. Действуют те же методы-опции, что и в Identity. Порождается методом 

  def Table autoIncrementColumn(name: String!)

Sequence. Автоинкремент, базирующийся на глобальной последовательности. Позволяет организовать сквозной "identity" для нескольких таблиц (нескольких типов объектов). Данный автоинкремент – вроде бы изобретение Rel, не соответствует стандартным инструментам SQL. По умолчанию первичным ключом не является. Генерируется с помощью метода 

  def Table sequenceColumn(name: String!, seq: rel/Sequence!)

Пример с использованием всех трех вариантов: 

use com/teacode/rel
use libretto/h2

def main = {
  fix base = rel/db(h2/temp)

  fix seq = rel/sequence("SEQ"). start(3). step(3)

  fix table = 
    rel/table("ITABLE") {
        identityColumn("IDENTITY"). start(100). step(7)
        autoIncrementColumn("AUTO"). start(210). step(4)
        sequenceColumn("SEQUENCE", seq)
    }

  base.w: {
    seq.create

    table.create

    1..5. (table. row(()). insert)

    rel/from(table). select(table). out. showtable
  }
}

Вставляем пять строк, в которых все поля автоматические. Результат: 

┌─────────────────┬─────────────┬─────────────────┐
│ ITABLE.IDENTITY │ ITABLE.AUTO │ ITABLE.SEQUENCE │
├─────────────────┼─────────────┼─────────────────┤
│ 100             │ 210         │ 3               │
├─────────────────┼─────────────┼─────────────────┤
│ 107             │ 214         │ 6               │
├─────────────────┼─────────────┼─────────────────┤
│ 114             │ 218         │ 9               │
├─────────────────┼─────────────┼─────────────────┤
│ 121             │ 222         │ 12              │
├─────────────────┼─────────────┼─────────────────┤
│ 128             │ 226         │ 15              │
└─────────────────┴─────────────┴─────────────────┘

Вот SQL-ная часть примера, автоматически генерируемая Rel'ом: 

CREATE SEQUENCE SEQ START WITH 3 INCREMENT BY 3

CREATE TABLE ITABLE(
  IDENTITY BIGINT IDENTITY(100, 7), 
  AUTO BIGINT AUTO_INCREMENT(210, 4), 
  SEQUENCE BIGINT)

INSERT INTO ITABLE(SEQUENCE) VALUES(SEQ.NEXTVAL)
INSERT INTO ITABLE(SEQUENCE) VALUES(SEQ.NEXTVAL)
INSERT INTO ITABLE(SEQUENCE) VALUES(SEQ.NEXTVAL)
INSERT INTO ITABLE(SEQUENCE) VALUES(SEQ.NEXTVAL)
INSERT INTO ITABLE(SEQUENCE) VALUES(SEQ.NEXTVAL)

SELECT ITABLE.IDENTITY, ITABLE.AUTO, ITABLE.SEQUENCE
FROM ITABLE

Alias (псевдонимы)

Реализовал методы переименования полей (определения алиасов). Оказалось, что в DSL-стиле это определить не так-то просто.

В СУБД часто используются переименования (алиасы) таблиц и полей. Они в основном требуются для наведения красоты (например, определения читабельных названий строк). Нас это мало интересует, поскольку мы можем это делать на уровне Libretto.

Однако в одном случае переименования необходимы и нам – когда в запросе используются несколько экземпляров одной и той же таблицы. Это необходимо в случае т.н. self-join операций. Например, пусть у нас есть таблица персон 

┌────────────┬──────────────┬────────────────┐
│ PERSONS.ID │ PERSONS.NAME │ PERSONS.SPOUSE │
├────────────┼──────────────┼────────────────┤
│ 1          │ John         │ 2              │
├────────────┼──────────────┼────────────────┤
│ 2          │ Ann          │ 1              │
└────────────┴──────────────┴────────────────┘
и мы хотим сформировать табличку, каждая строка которой состоит из пары имен супругов. В этом случае первичная таблица будет использоваться в запросе дважды – для получения имени супруга и имени супруги.

В SQL такой запрос выглядит так: 

SELECT P1.NAME, P2.NAME
FROM PERSONS AS P1, PERSONS AS P2
WHERE P1.ID = P2.SPOUSE

В библиотеке Rel методы определения алиасов помещаются не до, а после именуемого объекта: 

    def Table a(alias: (String | Int)!)
    def Column a(alias: (String | Int)!)

В качестве алиасов разрешаем как строки, так и целые. Например, persons.a("p1") задает для таблицы persons алиас p1. Для вариантов a(0)..a(9) определены методы-сокращения a0..a9.

Теперь мы готовы решить задачку с именами супругов: 

use com/teacode/rel
use libretto/h2

def main = {
  fix persons = rel/table("PERSONS") 
  
  fix pid = persons.identityColumn('ID)
  fix name = persons.stringColumn('NAME)
  fix spouse = persons.refColumn('SPOUSE, pid)

  // запрос показать имена супружеских пар с использованием алиасов

  fix getspouses = rel/from(persons.a1, persons.a2).
                   where(pid.a1 `==` spouse.a2). 
                   select(name.a1, name.a2)

  // работа с базой
  fix base = rel/db(h2/temp)
  
  base.w: {
    persons.create // создаем таблицу

    // создаем персон, столбик супругов пока зануляем
    fix john = persons.row(("John", null)).key.insert.*dyn(#Int!)
    fix ann =  persons.row(("Ann", null)).key.insert.*dyn(#Int!)

    // женим
    rel/update(persons). where(pid `==` john). set( spouse `=` ann ). run
    rel/update(persons). where(pid `==` ann). set( spouse `=` john ). run

    // табличка имен супружеских пар
    getspouses.out.showtable
  }
}
Получается что-то вроде математических индексов, которые ставятся не до, а после выражения. Результат выполнения программы: 

┌──────────────┬──────────────┐
│ ALIAS_1.NAME │ ALIAS_2.NAME │
├──────────────┼──────────────┤
│ Ann          │ John         │
├──────────────┼──────────────┤
│ John         │ Ann          │
└──────────────┴──────────────┘

Типы данных в Rel

На мой вкус система типов данных в БД оставляет ощущение какой-то кучи. Например, только по целым числам имеем TINYINT, SMALLINT, INT, BIGINT. По строкам имеем VARCHAR, VARCHAR(N), CHAR(N), CLOB, CLOB(N), VARCHAR_IGNORECASE, VARCHAR_IGNORECASE(N). В типах смешиваются глобальные понятия (например, строки) и технические вопросы реализации (например, размер, место хранения).

В Rel я использую только глобальные типы, а технические детали уточняю через методы-опции. Это не только чистит код, но и дает гибкие возможности для развитии библиотеки. Задаем ядро, а потом развиваем с помощью методов-опций.

Сейчас у меня получаются следующие основные типы полей (перечисляю генерирующие методы и методы-опции):

Поля с ручным заданием значений: 

IntColumn // тип поля целых чисел
  def Table intColumn(name: String!) // генератор поля, по умолчанию INT
    def IntColumn bigInt             // опция переключения на тип BIGINT
    def IntColumn tinyInt  // опция переключения на TINYINT, пока не работает
    def IntColumn smallInt // опция переключения на SMALLINT, пока не работает

StringColumn
  def Table stringColumn(name:String!) // по умолчанию VARCHAR
    def StringColumn char(length:Int!) // тип CHAR
    def StringColumn max(length:Int!)  // максимальная длина – для всех типов
    def StringColumn clob              // тип CLOB
    def StringColumn ignoreCase        // тип VARCHAR_IGNORECASE

RealColumn
  def Table realColumn(name:String!)                    // по умолчанию DOUBLE
    def RealColumn decimal(precision:Int!, scale:Int?)  // DECIMAL(precision, scale)
    def RealColumn decimal(precision:Int!)              // DECIMAL(precision)

RefColumn // внешний ключ
  def Table refColumn( name:String!, column: Column!) // у значений тот же тип, что и у column
Поля с автоматическим заданием значений: 

IdentityColumn 
  def Table identityColumn(name:String!) // реализация IDENTITY, первичный ключ
    def _ start(ss:Int!)                 // первое значение, по умолчанию = 1
    def _ step(st:Int!)                  // шаг, по умолчанию = 1

AutoIncrementColumn
  def Table autoIncrementColumn(name:String!) // реализация AUTO_INCREMENT
    def _ start(ss:Int!)                // первое значение, по умолчанию = 1
    def _ step(st:Int!)                 // шаг, по умолчанию = 1

SequenceColumn
  def Table sequenceColumn(name:String!, seq:Sequence!) // по глобальной посл.

Развивается также группировка общих опций (применимых к любым полям). Пока имеем здесь: 

def _ default(val: Const!)
def _ notNull
def _ unique
def _ comment(text:String!)

Пример использования опций: 

use com/teacode/rel
use libretto/h2

def main = {
  // схема (абстрактное описание)
  fix coffees = rel/table('COFFEES)
  
  fix id = coffees.identityColumn('ID).start(5).step(10)
  fix name = 
        coffees.stringColumn('COF_NAME).char(15).unique.default("Default Coffee")
  fix price = coffees.realColumn('PRICE).decimal(4,2).notNull
  fix amount = coffees.intColumn('AMOUNT).bigInt

  // запрос (тоже абстрактный)

  fix query = 
      rel/from(coffees). 
      where(price `>` 7.0). 
      select(coffees). 
      `^`(name)

  // работа с базой

  fix coffeeshop = rel/db(h2/temp)
  
  coffeeshop.w: {
    coffees.create
    
    coffees.(
      row(('Java, 11.0, 100)),
      row(('Pele, 3.0, 1200)),
      row(('Jacobs, 8.0, 700)),
      row((price, amount), (15.0, 2))). insert // default name
  } 
  coffeeshop.w: query.result.showtable
}
Результат вычисления программы: 
┌────────────┬──────────────────┬───────────────┬────────────────┐
│ COFFEES.ID │ COFFEES.COF_NAME │ COFFEES.PRICE │ COFFEES.AMOUNT │
├────────────┼──────────────────┼───────────────┼────────────────┤
│         35 │ Default Coffee   │         15.00 │              2 │
├────────────┼──────────────────┼───────────────┼────────────────┤
│         25 │ Jacobs           │          8.00 │            700 │
├────────────┼──────────────────┼───────────────┼────────────────┤
│          5 │ Java             │         11.00 │            100 │
└────────────┴──────────────────┴───────────────┴────────────────┘

Схемы баз данных в Rel

Добавил в модель Rel понятие схемы. Схема – замкнутая коллекция объектов базы данных, формирующая отдельную реляционную модель. Для Rel схема важна как инструмент группировки разрозненных объектов в общую модель.

Основные методы работы со схемами следующие.

Методы абстрактной модели: 

def schema(name:String!) // создание объекта схемы

def defaultSchema // объект дефолтной схемы (схемы, заданной по умолчанию)

При формировании объектов (таблиц, индексов, последовательностей) можно явно указывать, в какой схеме определяется объект: 

// объект таблицы задается в схеме по умолчанию
fix ytable = table('TABLE_IN_DEFAULT_SCHEMA) 

fix myschema = rel/schema('MY_SCHEMA)

// объект таблицы описывается в рамках схемы myschema
fix mytable = myschema.table('MY_TABLE)
Аналогично для индексов и последовательностей.

Методы управления схемами в базе данных: 

def Schema create // создание новой схемы в базе

def Schema setDefault // установка схемы как схемы по умолчанию

В следующем примере видно, что схема может выступать и в роли пространства имен. Объект таблицы table0 определен в схеме по умолчанию, поэтому будет работать в пространстве той схемы, которая на данный момент является дефолтной. У таблиц table1 и table2 схемы явно указаны. Все три объекта задают одну и ту же сигнатуру таблицы. 

use com/teacode/rel
use libretto/h2

def main = {
  fix mschema = rel/schema('MY_SCHEMA)
  fix pschema = rel/schema('PUBLIC) // объект стандартной дефолтной схемы в H2

  fix table0 = rel/table('TABLE) { stringColumn('NAME) }

  fix table1 = mschema.rel/table('TABLE) { stringColumn('NAME) }

  fix table2 = pschema.rel/table('TABLE) { stringColumn('NAME) }
  
  rel/db(h2/temp).w: {
    mschema.create
//    pschema.create // будет ошибка, так как схема PUBLIC уже есть в H2
    table0.create // создаем таблицу "по умолчанию" в схеме по умолчанию PUBLIC

    table0. row('Ann). insert
    table2. row('Tom). insert   // сейчас table0 эквивалентна table2

    mschema.setDefault // устанавливаем схему по умолчанию MY_SCHEMA
    
    table0.create     // создаем таблицу "по умолчанию" в схеме MY_SCHEMA

    table0. row('Marie). insert
    table1. row('John). insert      // сейчас table0 эквивалентна table1
    
    table1.show
    table2.show
  }
} 

def rel/Table show = rel/from(this).select(this).out.showtable.*println

В результате получим две таблицы – для схем MY_SCHEMA и PUBLIC, соответственно: 

┌────────────┐
│ TABLE.NAME │
├────────────┤
│ Marie      │
├────────────┤
│ John       │
└────────────┘

┌────────────┐
│ TABLE.NAME │
├────────────┤
│ Ann        │
├────────────┤
│ Tom        │
└────────────┘

Методы организации запросов и изменений в базе

Определился с методами, запускающими операции и запросы в базе данных. Теперь это run и out.

Для операций, изменяющих состояние базы (и не требующих ответа) используется метод run. Он имеется в двух эквивалентных вариантах: 

def Query run

def run(q:Query!)
Например, 

rel/update(persons). where(pid `==` johnID). set( spouse `=` annID ). run
Для запросов к базе данных используем метод out. Он также имеется в двух вариантах: 
def Query out

def out(q:Query!)
Например, 
rel/from(coffees).select(coffees).out

rel/out: rel/from(coffees).select(coffees)
Оба метода работают только внутри траназкций баз данных.

Методы вставки записей (строк) в таблицы

Переделал insert. Теперь это метод, описывающий "индивидуумов" в абстрактной модели. Поэтому вместо глагола insert использую существительное row. Например, 

coffees. row: ('Java, 11.0, 100)
описывает конкретную марку кофе. Здесь coffees – объект таблицы.

Метод row определен в двух вариантах: 

def Table row(columns: ManualColumn*, values: Const*)

def Table row(values: Const*)
В первом варианте все поля, для которых задаются значения, перечисляются в аргументе columns (не указанные поля заполняются дефолтными значениями или NULL). Второй вариант используется, когда задаются значения всех полей строки. В этом случае число значений в values должно совпадать с числом полей, заполняемых вручную. Автоматические поля (типа identityColumn) не учитываются.

Для вставки строк в таблицу в рамках транзакции надо явно применить к "индивидууму" метод insert. Например, 

rel/insert: coffees.row: ('Java, 11.0, 100)
или 
coffees.row(('Java, 11.0, 100)). insert
Если в результате вставки необходимо получить первичный ключ вставляемой строки, то применяется опция key.

В следующем примере строим таблицу персон, содержащую ключ, имя и ключ супруга. 

use com/teacode/rel
use libretto/h2
use libretto/util

def main = {
  // схема базы
  fix persons = rel/table("PERSONS") 

  fix pid = persons.identityColumn('ID)
  fix name = persons.stringColumn('NAME)
  fix spouse = persons.refColumn('SPOUSE, pid)

  fix getpersons = rel/from(persons).select(persons)

  // создаем "индивидуумов" абстрактной модели
  fix ann = persons. row: ("Ann", null)
  fix john = persons. row: ("John", null)
  
  // включаем базу
  fix base = rel/db(h2/temp)
  
  base.w: {
    persons.create // создаем таблицу

    // добавляем объекты ann и john в базу, получаем их ID с помощью key
    fix annID =  ann. key. insert. *dyn(#Int!)
    fix johnID = john. key. insert. *dyn(#Int!)
    
    // женим
    rel/update(persons). where(pid `==` johnID). set( spouse `=` annID ). run
    rel/update(persons). where(pid `==` annID). set( spouse `=` johnID ). run

    // рисуем таблицу
    getpersons. out. showtable
  }
}

С помощью опции key получаем значения ключей только что вставленных строк. Результат: 

┌────────────┬──────────────┬────────────────┐
│ PERSONS.ID │ PERSONS.NAME │ PERSONS.SPOUSE │
├────────────┼──────────────┼────────────────┤
│          1 │ Ann          │              2 │
├────────────┼──────────────┼────────────────┤
│          2 │ John         │              1 │
└────────────┴──────────────┴────────────────┘

Оператор join

Методы 

def Query join(table: Table!, condition: Condition!)
def Query innerJoin(table: Table!, condition: Condition!)
def Query leftJoin(table: Table!, condition: Condition!)
def Query rightJoin(table: Table!, condition: Condition!)

обеспечивают классические операции объединения записей из двух и более таблиц. join и innerJoin эквивалентны. Параметры: 

  table – присоединяемая таблица
  condition – условие "склейки" записей

Пример: 

use com/teacode/rel
use libretto/h2

def main = {  
  fix authors = rel/table('AUTHOR)
    fix id = authors.identityColumn('ID)
    fix fname = authors.stringColumn('FIRST_NAME)
    fix lname = authors.stringColumn('LAST_NAME)

  fix books = rel/table('BOOK)
    fix title = books.stringColumn('TITLE)
    fix aid = books.refColumn('AUTHOR_ID, id)
    fix published = books.intColumn('PUBLISHED_IN)

  fix jdb = rel/db(h2/temp)  
  jdb.w: {
    authors.create
    authors.( 
      row(('Александр, 'Пушкин)),
      row(('Claude, 'Shannon)),
      row(('Андрей, 'Платонов))). insert

    books.create
    books.(
      row(("Руслан и Людмила", 1, 1820)),
      row(("Чевенгур", 3, 1988)),
      row(("A Mathematical Theory of Communication", 2, 1948))). insert

    rel/from(books).
      join(authors, id `==` aid).
      where(published `>` 1900).
      select(fname, lname, title).
        out.showtable
  }
}

Здесь join используется для объединения данных из таблицы авторов и таблицы книг. Склейка проводится по ID автора. Результат: 

┌───────────────────┬──────────────────┬────────────────────────────────────────┐
│ AUTHOR.FIRST_NAME │ AUTHOR.LAST_NAME │ BOOK.TITLE                             │
├───────────────────┼──────────────────┼────────────────────────────────────────┤
│ Андрей            │ Платонов         │ Чевенгур                               │
├───────────────────┼──────────────────┼────────────────────────────────────────┤
│ Claude            │ Shannon          │ A Mathematical Theory of Communication │
└───────────────────┴──────────────────┴────────────────────────────────────────┘

Последовательным применением join-ов можно присоединить три и более таблиц.

Индексирование

Индексы (наряду со схемами, таблицами, последовательностями и строками) являются базовыми объектами модели Rel. Объект индекса создается с помощью метода 

def Table indexx(name:String!, columns:Column+)

Здесь name – имя индекса, columns – индексируемые столбцы. Если столбцов несколько, то все они должны принадлежать одной таблице. Для индекса доступны следующие конфигурационные опции: 

def Indexx hash // хэш-индекс (для таблиц в памяти, для остальных игнорируется)

def Indexx unique // запрещаются повторения значений

Пример создания объекта индекса: 

fix idx1 = table1.indexx('IDX_1, (col1, col2)). unique

На уровне базы данных с индексами работают следующие методы: 

def Indexx create // создание индекса в базе

def Indexx drop // удаление индекса из базы

Например, 

idx1.create

создает в базе индекс по объекту idx1. Эта операция должна проводиться внутри транзакции.

В следующем примере у нас

Дано: две таблицы – таблица персон с именами и возрастом, и таблица друзей, содержащая пары ID друзей.

Задача: построить таблицу, показывающую имя персоны и возраст ее друга.

Задача решается через двойное использование join: начинаем с таблицы персон, к которой присоединяем таблицу друзей (чтоб найти друга), к которой присоединяем вторую копию таблицы персон (чтобы найти возраст друга).

Проблема в том, что при джойнах должен осуществляться быстрый поиск как по первому, так и по второму столбцу таблицы друзей. Поэтому индексируем их: 

use com/teacode/rel
use libretto/h2

def main = {  
  fix persons = rel/table('PERSONS)
    fix id = persons.identityColumn('ID)
    fix name = persons.stringColumn('NAME)
    fix age = persons.intColumn('AGE)

  fix friends = rel/table('FRIENDS)
    fix fr1 = friends.intColumn('FRIEND_1)
    fix fr2 = friends.intColumn('FRIEND_2)

  fix idx1 = friends.indexx('IDX_1, fr1)
  fix idx2 = friends.indexx('IDX_2, fr2)

  fix ann = persons.row(('Ann, 16))
  fix john = persons.row(('John, 18))
  fix marie = persons.row(('Marie, 17))
  fix tom = persons.row(('Tom, 19))

  fix base = rel/db(h2/temp)
  base.w: {
    persons.create
    friends.create

    fix annID = ann. key. insert. *dyn(#Int!)
    fix johnID = john. key. insert. *dyn(#Int!)
    fix marieID = marie. key. insert. *dyn(#Int!)
    fix tomID = tom. key. insert. *dyn(#Int!)

    friends. (
      row((annID, johnID)),
      row((marieID, tomID))). insert

    idx1.create
    idx2.create

    rel/from(persons).
      join(friends, id `==` fr1).
      join(persons.a1, id.a1 `==` fr2).
      select(name, age.a1).
        out. showtable     
  }
}

Чтобы работать с двумя копиями таблицы персон, используем алиасы. Результат: 

┌──────────────┬─────────────┐
│ PERSONS.NAME │ ALIAS_1.AGE │
├──────────────┼─────────────┤
│ Ann          │          18 │
├──────────────┼─────────────┤
│ Marie        │          19 │
└──────────────┴─────────────┘

Метод проверки существования exists/1

Реализовал метод, проверяющий существование в базе конкретных объектов. Проверяются объекты четырех основных типов – схем, таблиц, индексов и последовательностей: 

struct Object = Schema | Table | Sequence | Indexx

def exists(obj: Object!)

Этот метод позволяет избавиться от управляющих конструкций SQL (типа корявых IF EXISTS и IF NOT EXISTS), вынося проверку на уровень Libretto.

Проверка проводится непосредственно в базе (реализация exists основана на использовании системных таблиц СУБД). Это означает, что проверяемый объект должен быть не только включен в модель, но и реально создан в базе. Метод возвращает полное имя объекта, если объект в базе существует, и () в обратном случае.

Пример: 

use com/teacode/rel
use libretto/h2

def main = {
  fix schema1 = rel/schema('SCHEMA_1)
  fix schema2 = rel/schema('SCHEMA_2)

  fix table1 = rel/table('TABLE_1)

  fix ncol = table1.stringColumn('NAME)

  fix idx = table1.indexx('IDX_1, ncol)

  fix table2 = rel/table('TABLE_2)
  fix table3 = schema1.rel/table('TABLE_3)

  fix seq = schema1.sequence('SEQ_1)


  fix base = rel/db(h2/temp)

  base.w: {
    schema1.create
    
    table1.create
    table3.create

    idx.create
    seq.create
    
    rel/exists(schema1) .*println
    rel/exists(rel/schema('SCHEMA_1)) .*println
    rel/exists(schema2) .*println
    rel/exists(rel/defaultSchema) .*println

    rel/exists(table1) .*println
    rel/exists(table2) .*println
    rel/exists(table3) .*println

    rel/exists(idx) .*println

    rel/exists(seq) .*println  
  }
}
Выдает в качестве результата: 

SCHEMA_1
SCHEMA_1
()
PUBLIC
PUBLIC.TABLE_1
()
SCHEMA_1.TABLE_3
PUBLIC.TABLE_1.IDX_1
SCHEMA_1.SEQ_1

Первичные и внешние ключи

В SQL первичные и внешние ключи можно задавать как на уровне определения полей, так и на уровне определения таблицы в целом (в виде отдельных ограничений). Первый способ более компактный, однако без второго не обойтись, если ключ формируется из нескольких полей.

В Rel первичные и внешние ключи создаются с помощью методов setPrimary и setReference, соответственно.

Первичный ключ определяется с помощью 

def Table setPrimary(cols: Column*)
Здесь cols – поля таблицы, в совокупности формирующие первичный ключ. Данный метод используется для определения в качестве первичных ключей всех полей, кроме identityColumn, поскольку identityColumn автоматически задается как первичный ключ при создании. А, например, для автоматического поля sequenceColumn это нужно делать явно. Другое автоматическое поле – autoIncrementColumn – декларировать в качестве первичного ключа не имеет смысла, поскольку эта декларация превращает его в identityColumn.

Метод 

def Table getPrimary
используется для получения полей, формирующих первичный ключ.

Метод для формирования внешнего ключа: 

def Table setReference(cols: Column*, refcols:Column*)
Здесь cols – поля, формирующие внешний ключ, refcols – поля, на которые ссылаются поля внешнего ключа. Поля cols должны принадлежать таблице из контекста метода.

В отличие от setPrimary, метод setReference может быть задействован в таблице несколько раз. Это делается в случае, когда таблица содержит более одного внешнего ключа.

Если внешний ключ содержит только одно поле, то вместо общего констрейнта setReference можно использовать специальное поле refColumn. Оно был описано раньше. В Rel удобно сочетать поля identityColumn и refColumn.

Внешние ключи могут ссылаться на любые поля, не обязательно первичные ключи.

Приведем пример использования setPrimary и setReference. Имеются две таблицы. Одна описывает персон с их паспортными данными, вторая привязывает штрафы к паспортным данным. Задача – выдать "ведомость" с суммой штрафов по каждой персоне. 

use com/teacode/rel
use libretto/h2

def main = {
  fix persons = rel/table('PERSONS)
    fix name = persons.stringColumn('NAME)
    fix surname = persons.stringColumn('SURNAME)
    fix ps = persons.intColumn('SERIES)
    fix pn = persons.intColumn('NUMBER)

  persons.setPrimary((ps, pn))

  fix fines = rel/table('FINES)
    fix psref = fines.intColumn('SREF)
    fix pnref = fines.intColumn('NREF)
    fix fine  = fines.realColumn('FINE).decimal(6,2)

  fines.setReference((psref, pnref), (ps, pn))

  rel/db(h2/temp).w: {
    persons.create
    fines.create

    persons. (
      row(('Иван, 'Петров, 2510, 642199)),
      row(('Петр, 'Иванов, 2508, 223712))).insert

    fines. (
      row((2510, 642199, 1200.00)),
      row((2508, 223712, 882.20)),
      row((2508, 223712, 1190.33)),
      row((2510, 642199, 2400.10))).insert

    rel/from(persons).
      join(fines, ps `==` psref `and` (pn `==` pnref)).
      group((ps, pn)).
      select(name, surname, ps, pn, sum(fine)).
        out.showtable
  }
}

Здесь и первичный, и внешний ключи состоят из двух полей. Теперь если в таблице fines появится номер паспорта, отсутствующий в первой таблице, возникнет ошибка связности базы. Результат вычислений: 

┌──────────────┬─────────────────┬────────────────┬────────────────┬───────────┐
│ PERSONS.NAME │ PERSONS.SURNAME │ PERSONS.SERIES │ PERSONS.NUMBER │ SUM(FINE) │
├──────────────┼─────────────────┼────────────────┼────────────────┼───────────┤
│ Иван         │ Петров          │           2510 │         642199 │   3600.10 │
├──────────────┼─────────────────┼────────────────┼────────────────┼───────────┤
│ Петр         │ Иванов          │           2508 │         223712 │   2072.53 │
└──────────────┴─────────────────┴────────────────┴────────────────┴───────────┘

Оператор group и агрегаторы значений

Рассмотрим, как работает метод group и агрегаторы типа min, max, avg, count.

Метод 

def Query group(gr: Column+)

группирует строки по значениям колонок col (может быть одна или более колонок). Агрегаторы выполняют групповые операции взятия минимального и максимального значений, вычисления среднего, и общего количества строк в группе.

В качестве примера возьмем базу средних температурных значений по месяцам в Иркутске: 

use com/teacode/rel
use libretto/h2

def main = {
  fix climate = rel/table('КЛИМАТ)
    fix city = climate.stringColumn('ГОРОД)
    fix month = climate.stringColumn('МЕСЯЦ)
    fix quarter = climate.intColumn('КВАРТАЛ)
    fix temp = climate.realColumn('ТЕМПЕРАТУРА).decimal(4, 1)

  rel/db(h2/temp).w: {
    climate.create

    climate. (
      row(("Иркутск", "Январь", 1, -17.8)),
      row(("Иркутск", "Февраль", 1, -14.4)),
      row(("Иркутск", "Март", 1, -6.4)),
      row(("Иркутск", "Апрель", 2, 2.5)),
      row(("Иркутск", "Май", 2, 10.2)),
      row(("Иркутск", "Июнь", 2, 15.4)),
      row(("Иркутск", "Июль", 3, 18.3)),
      row(("Иркутск", "Август", 3, 15.9)),
      row(("Иркутск", "Сентябрь", 3, 9.2)),
      row(("Иркутск", "Октябрь", 4, 1.8)),
      row(("Иркутск", "Ноябрь", 4, -7.6)),
      row(("Иркутск", "Декабрь", 4, -15.3))).insert
 
      rel/from(climate).
        group(quarter).
        select(city, quarter, count(quarter), min(temp), max(temp)).
          out.showtable
  }
}

В запросе данные группируются по кварталам: вычисляются поквартальные минимальные и максимальные значения температур, а также уточняется число месяцев в квартале.

Результат: 

┌──────────────┬────────────────┬────────────────┬──────────────────┬──────────────────┐
│ КЛИМАТ.ГОРОД │ КЛИМАТ.КВАРТАЛ │ COUNT(КВАРТАЛ) │ MIN(ТЕМПЕРАТУРА) │ MAX(ТЕМПЕРАТУРА) │
├──────────────┼────────────────┼────────────────┼──────────────────┼──────────────────┤
│ Иркутск      │              1 │              3 │            -17.8 │             -6.4 │
├──────────────┼────────────────┼────────────────┼──────────────────┼──────────────────┤
│ Иркутск      │              2 │              3 │              2.5 │             15.4 │
├──────────────┼────────────────┼────────────────┼──────────────────┼──────────────────┤
│ Иркутск      │              3 │              3 │              9.2 │             18.3 │
├──────────────┼────────────────┼────────────────┼──────────────────┼──────────────────┤
│ Иркутск      │              4 │              3 │            -15.3 │              1.8 │
└──────────────┴────────────────┴────────────────┴──────────────────┴──────────────────┘

Обработка результата запросов

Результат выполнения запроса (метода out) представляется в виде структур Result и Row: 

struct Const = Real | String | Int | Null | Boolean

struct Result(columns: (Column|Arith)*, rows: Row*)
struct Row(vals: Const*)

Поле columns содержит объекты полей или арифметических операций, соответствующих столбцам. Поле rows – последовательность объектов Row, каждый из которых представляет строку ответа – последовательность объектов структуры Const.

К структурам Result и Row прилагается набор геттеров: 

// взятие значения у поля номер col строки номер row
def Result get(row:Int!, col:Int!) 

// взятие в поле номер col у первой строки
def Result get(col:Int!) 

// взятие целочисленного значения или пустоты (для NULL) 
// в поле номер col строки номер row
def Result intNull(row:Int!, col:Int!) 

// взятие целочисленного значения или пустоты (для NULL) 
// в поле номер col первой строки                                
def Result intNull(col:Int!) 

// взятие целочисленного значения или пустоты (для null) 
// в поле номер col данной строки
def Row intNull(col:Int!) 

// получение целых чисел без null:
def Result int(row:Int!, col:Int!):Int!
def Result int(col:Int!):Int!
def Row int(col:Int!):Int!

Аналогично для строк и вещественных чисел: 

def Result stringNull(row:Int!, col:Int!):String?
def Result stringNull(col:Int!):String?
def Row stringNull(col:Int!):String?

def Result string(row:Int!, col:Int!):String!
def Result string(col:Int!):String!
def Row string(col:Int!):String!

def Result realNull(row:Int!, col:Int!):Real?
def Result realNull(col:Int!):Real?
def Row realNull(col:Int!):Real?

def Result real(row:Int!, col:Int!):Real!
def Result real(col:Int!):Real!
def Row real(col:Int!):Real!

Взятие всех значений столбца (соответствующего поля из каждой строки) 

def Result getCol(col:Int!):Const*

и всех полей строки: 

def Result getRow(row:Int!):Const*

Проверка результата на пустоту: 

def Result empty
def Result nonEmpty

UPD: Поле columns в структуре Result теперь содержит не имена колонок, а объекты полей или арифметических операций. Для получения имен нужно применить к объектам columns метод string.

Корректировка существующей базы

Добавлен метод alter, который позволяет корректировать существующую базу. Пока самые минимальные возможности, включая добавление столбцов. Пример: 

use com/teacode/rel
use libretto/h2

def main = {

  fix tbl = 
    rel/table("TABLE")
    
  fix name = tbl.stringColumn("NAME")
  fix age = tbl.intColumn("AGE")

  fix base = rel/db(h2/temp)

  base.w: {
    tbl.create
    
    (tbl.row(('John, 19)),
      tbl.row(('Ann, 17))). insert

    println(rel/from(tbl).select(tbl). out. showtable)

    fix surname = tbl.stringColumn("SURNAME") // расширяем модель базы
    
    rel/alter(tbl).add(stringColumn("SURNAME")).run // расширяем базу физически

    // работаем с обновленной таблицей:

    tbl.row(('Paul, 22, 'Smith)).insert 
    rel/update(tbl). where(name `==` 'John). set(surname `=` 'Johnson). run

    println(rel/from(tbl).select(tbl). out. showtable)
  }
}

Результат: 

┌────────────┬───────────┐
│ TABLE.NAME │ TABLE.AGE │
├────────────┼───────────┤
│ John       │        19 │
├────────────┼───────────┤
│ Ann        │        17 │
└────────────┴───────────┘

┌────────────┬───────────┬───────────────┐
│ TABLE.NAME │ TABLE.AGE │ TABLE.SURNAME │
├────────────┼───────────┼───────────────┤
│ Ann        │        17 │ NULL          │
├────────────┼───────────┼───────────────┤
│ Paul       │        22 │ Smith         │
├────────────┼───────────┼───────────────┤
│ John       │        19 │ Johnson       │
└────────────┴───────────┴───────────────┘
Result (48 ms): unit