Colbert biedt unieke functionaliteit 

Data analyse

Data analyseren met SQL is soms omslachtig of onmogelijk. Dat is jammer want SQL is in de basisopzet een krachtig gereedschap.
Zo is het concept van de view een goed mechanisme om eenmalig te definiëren hoe de gewenste informatie er uit moet zien zonder hierbij data te moeten kopiëren naar aparte tabellen.

Colbert heeft SQL uitgebreid met functionaliteit die krachtige, ongeëvenaarde mogelijkheden biedt voor het doen van data-analyse. Deze extra functionaliteit is met een zeer geringe uitbreiding van SQL geïmplementeerd. De meest in het oog springende is de mogelijkheid om formules op te slaan in databasevelden. Maar Colbert kent meer bijzondere functionaliteit.

Formules

In Colbert kun je velden definiëren van het type formule. De typering van de formule ligt vast maar het aantal parameters is variabel.
Bij aanroep van een formule in een SQL statement wordt elke formule eenmalig vertaald en gebonden aan de parameters die daarbij in de dan geldende scoop worden gezocht.

De tabel Employees is gedefinieerd als:
create table Employees (Name String, Salary Double, Bonus Expr<Double> )

Employees

insert into Employees set Name = ‘Ernie’, Salary = 2900, Bonus = Revenue div 100

Meer informatie en voorbeelden van formules zijn te vinden onder Toepassingsgebieden:

Former

Former is een functie die een veld uit het vorige record benadert. Vorig wordt daarbij bepaald door een dan verplicht order by clause.

Met Former kunnen allerlei cumulatieve, ranking en andere analytische functies worden gedaan.

Sales

Toon per jaar de groei in Target en de cumulatieven van Revenue

De eerste Former heeft als parameter Target, de tweede Former heeft geen parameter en verwijst naar zichzelf in het vorige record

Colbert SQL met Former

Select *, Target – former Target as GrowTarget, Revenue + Former as TotRevenue
from (Sales Where EmpId = 16) order by Year

Slim SQL

In Colbert mag je samen met standaard SQL ook een aangepaste SQL gebruiken. Je mag dan niet relevante SQL zoals SELECT * FROM, ORDER BY, HAVING en GROUP BY weglaten. De ORDER BY mag je ook in de from clause aangeven.

Sql

Colbert SQL

Select * from Department

Department

Select * from Department where DepId > 12

Department: DepId > 12

SELECT Beer, COUNT(*) as “number” FROM Beers GROUP BY Beer ORDER BY “number” desc;

Beer, count desc as Number from Beers

CREATE FUNCTION mini(v1 number, v2 number)
RETURN number IS
BEGIN
  IF v1 < v2 THEN RETURN v1; ELSE RETURN v2; END IF;
END;

create func mini as a < b ? a : b         *

Select Name, DepId from Fam where Name = ‘Frank’
order by DepId

Name, DepId asc from Fam[Frank]

* In Colbert is mini hier ook nog eens generiek gedefinieerd !     Zie hieronder voor meer uitleg.

Tekstanalyse en tekstmutatie

Haddock is een krachtige taal voor het onderzoeken en muteren van tekst.
Haddock bestaat uit één-letter operatoren die nul of meer parameters hebben en meestal overloaded zijn.
Haddock is naadloos geïntegreerd in SQL waarbij de parameters van de Haddock operatoren ook waarden uit database velden mogen zijn.
Met Haddock is elke analyse of manipulatie op tekst mogelijk

R  Rechts
L  Links
V  Verify
E   Erase
B  Erase Back
G  Find char in range
F  Find
S  Substitute
C  Copy to Buffer

*  Herhaal zolang true
{ .. }  maak tot één operator
{ .. ; .. ; ..}  eerste match
@  buffer
#  counter
^  position
$  length
::  Haddock

Overload voorbeeld:

L4         Left 4
L$2       Lower (lengte < 2)
L’eoa’    Left if next in (e,o,a)

Haddock operator voorbeelden
Diag
Toon tweede PWS code

Haddock kan zowel tekst als boolean als int opleveren. Hier wordt een tekst opgeleverd.

Colbert SQL met Haddock in from clause

Line, Line ::f’pws’2 a3 {h’0-9’a}*q@ as pws2 from Diag

Toon alle met PWS code met numeriek deel beginnend met 34 of niet meer dan 4 karakters
Haddock levert hier een boolean op.
Colbert SQL met Haddock in where clause

* from Diag: Line :: {t’pws’ {v’34’ ; r’0-9’5 n} }~

Toon alle records met Name is palindrome.
Colbert SQL met Haddock in where clause

Name from Emp where Name :: {c r* l@e l*e}* l$2

Normalisatie
Data met herhaalde structuren kunnen met Colbert in SQL worden genormaliseerd. Colbert doet dit on the fly. Er wordt dus geen genormaliseerde kopie opgeslagen.
Sports
Toon de genormaliseerde tabel

Colbert biedt voor normalisatie een virtuele tabel Normalize die zijn waarden uit een veld van een andere tabel haalt.
Het resultaat is in SQL weer normaal te benaderen als relationele tabel.

Colbert SQL met Normalize

EmpId, ASPort from Sports join Normalize Sport as ASport

Generieke Functies

In Colbert is het heel eenvoudig om functies te definiëren. Je hoeft je hierbij niet druk te maken over typering: Als je het weglaat zoekt Colbert uit voor welke types hij een implementatie kan maken.

Emp
Definieer getypeerde functie IsPalindroom

create func (string S => bool) IsPalindrome as S:: {c r* l@e l*e}* l$2

Definieer generiek functie Next
Create func Next as X + 1
Colbert SQL met Next en IsPalindrome

Bij een functie met 0 of 1 parameter hoeven deze niet tussen haakjes te staan

Name, EmpId, BirthDate, Salary, next EmpId, next BirthDate, next Salary from Emp where IsPalindrome Name

Definieer functie GetNumber (Nr uit S)

create func GetNumber as S :: {g’0-9′ b* r’0-9’*} Nr t e* g$0

Toon eerste en tweede getal 

Colbert SQL met GetNumber in from clause
Line, GetNumber(Line, 1) , GetNumber(Line, 2) from Diag
Toon alle met een vijfde getal
Haddock is zowel een string als bool als int en Colbert kent impliciete type coercion die in de where clause de boolean oplevert.
Colbert SQL met GetNumber in where clause

Diag where GetNumber(Line, 5)