Colbert offers unique features

Data analysis

SQL is a simple and powerful language for analysing your data. For example, the concept of a SQL view is a good mechanism to define once what the desired information should look like without having to store the actual result in a new table. But sometimes SQL in not powerful enough to solve your query.

To overcome these limitations, Colbert comes with extended functionality that offers powerful, unparalleled possibilities for data analysis. This functionality has been implemented with a very small extension of SQL. The most interesting feature is the possibility to store formulas in database fields. But Colbert has more special functionality that is seamlessly integrated into SQL.

Formulas

With Colbert you can define and execute fields that contain a formula. The result type of the formula is defined while the parameters can vary in type and number.

When a formula called in a SQL query, it is compiled once and parameters are bound to variables found in the Scope of that query.

The Employees table is gedefinieerd as:
create table Employees (Name String, Salary Double, Bonus Expr )

Employees table with bonus formulas
Employees

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

More information and examples can be found in the section  Application areas 

Former

Former is a function that refers a field in the previous record. When Former is used the query should have an order-by-clause.

With Former it is possible to define cumulative, ranking, relative and other key figures.

Sales figures
Sales

Show the grow of Target and the
cumulative sum of Revenue per Year.

The first Former has Target as a parameter. The second Former has no parameter and refers to itself.

Fetch value from former database field

Colbert SQL with Former.

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

Slim SQL

In Colbert, a modified SQL may be used in addition to the standard SQL. This Slim SQL makes it possible to write a query in a more compact and simple manner.

For example, irrelevant SQL such like SELECT * FROM, HAVING and GROUP BY might be omitted. The ORDER BY may also be specified in the from-clause.

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]

* mini is defined generic !  See the explanation below.

Text analysis en text mutation

Haddock is a powerful language for inspect and update text. Haddock consists of one-letter operators with zero or more parameters and are usually overloaded.

Haddock is seamlessly integrated into Colbert SQL and parameters of Haddock operators might be values from database fields or an expression.

With Haddock, any analysis or manipulation of text is possible. Haddock can yield results of type string, boolean and integer.

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

*  repeat while true
{ .. }  make one operator
{ .. ; .. ; ..}  first match
@  buffer
#  counter
^  position
$  length
::  Haddock

Overload example:

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

Haddock operator examples

Not normalized table
Diag

Show second PWS code

Haddock might yield string, boolean or integer. Here a string is yielded.

Extract text with Haddock

Colbert SQL with Haddock in from clause

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

Show all PWS code with numeric part starting with 34 or numeric part not exceeding 4 digits.

Haddock yields a boolean here.

Haddock in where clause

Colbert SQL with Haddock in where clause

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

Show all records with Name that is a palindrome.

Test if palindrome with Haddock

Colbert SQL with Haddock in where clause

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

Normalisation

Data with repeated structures can be normalized with Colbert in SQL. Colbert does this on the fly so no normalized copy has to be stored.

Sports table not normalized
Sports

Show the normalized table

Normalize yields a virtual table with values derived from a field of another table.

The result might be used as a subquery in SQL.

Normalized table

Colbert SQL with Normalize

EmpId, ASPort from Sports join Normalize Sport as ASport

Generic Functions

With Colbert it is very easy to define functions. You don’t have to worry about the result type and the parameter types: If you leave it out, Colbert will find out for which types a valid implementation exists.
Functions may also be overloaded in Colbert.

Employees table with bonus formulas
Employees

Define a typed functie IsPalindroom

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

Define a generic function Next

Create func Next as X + 1

Colbert SQL with Next and IsPalindrome

Parenthesis might be omitted for a function with one or zero parameters.

Generic functions in SQL

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

Definine a function GetNumber with Nr as parameter.

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

Show first and second number.

Extract number from text with Haddock

Colbert SQL with GetNumber in from clause

Line, GetNumber(Line, 1) , GetNumber(Line, 2) from Diag

Toon all records that contain 5 numbers

Haddock is a tuple with a string, Boolean and integer. As Colbert has implicit coercions, a Boolean is yielded in the where-clause.

Check number in text in SQL

Colbert SQL with GetNumber in where-clause

Diag where GetNumber(Line, 5)