Affinity
Relational Algebra Library written in JavaScript
Inspired from
Axiom https://github.com/dkubb/axiom
DEE http://www.quicksort.co.uk
To-do list for 0.1.0##
- Implement Extend operation
- Implement views && updatable views
- Implement Summarize operation
- Implement comparison operators (lt, gt, eq, ...)
- Implement date, string and numeric functions (cos, sin, ...)
- More tests
- Review documentation
Installation##
npm install('affinity')
var affinity = require('affinity');
Documentation##
For Documentation, install the library with npm install affinity
and access <install_dir>/docs/index.html
in your browser.
Alternatively, the source is extensively documented.
Relational Algebra##
Relational Algebra and Set Theory are the foundation of SQL implementations. The well known SELECT * WHERE ... are relational algebra operations. Though, Relational Algebra (being a superset of Set Theory) is more strict than many SQL implementations. For example, it does not allow NULL values nor duplicates.
Simply put, a Relation may be viewed as a database table. A table has columns and rows. A Relation has a header and tuples. It is basically the same thing.
Relation###
A Relation is composed of a Header and a Body.
This is a representation of a relation :
+---------------+---------------------+--------------------+------------------+----------------+
| id : TInteger | firstName : TString | lastName : TString | alive : TBoolean | age : TInteger |
+===============+=====================+====================+==================+================+
| 0 | John | Doe | true | 34 |
+---------------+---------------------+--------------------+------------------+----------------+
| 1 | Mary | Poppins | false | 95 |
+---------------+---------------------+--------------------+------------------+----------------+
| 2 | Mark | Clinton | true | 2 |
+---------------+---------------------+--------------------+------------------+----------------+
| 3 | Hopty | Duddy | false | 10 |
+---------------+---------------------+--------------------+------------------+----------------+
Set###
A Set is a collection of distinct objects (no duplicates)
[1, 2, 3, 4, ...]
['a', 'b', 'c', ...]
[{Object1},{Object2},{Object3},...]
Header###
A Header is a Set of Attributes:
+---------------+---------------------+--------------------+------------------+----------------+
| id : TInteger | firstName : TString | lastName : TString | alive : TBoolean | age : TInteger |
+===============+=====================+====================+==================+================+
Attribute###
An Attribute is a pair (name, type):
+---------------+
| id : TInteger |
+===============+
A Type can be anything (String, Boolean, Object, Vector, Array, even Tuple, Function, Relation, Set, Header...)
Body###
A Body is a Set of Tuples having the same Attributes as the Relation:
+===============+=====================+====================+==================+================+
| 0 | John | Doe | true | 34 |
+---------------+---------------------+--------------------+------------------+----------------+
| 1 | Mary | Poppins | false | 95 |
+---------------+---------------------+--------------------+------------------+----------------+
| 2 | Mark | Clinton | true | 2 |
+---------------+---------------------+--------------------+------------------+----------------+
| 3 | Hopty | Duddy | false | 10 |
+---------------+---------------------+--------------------+------------------+----------------+
Tuple###
A Tuple is a single entry in a Relation. It is an ordered set (attribute1 : value, attribute2 : value...):
+---------------+---------------------+--------------------+------------------+----------------+
| 1 | Mary | Poppins | false | 95 |
+---------------+---------------------+--------------------+------------------+----------------+
Overview
// Declare new relations
var relation = new affinity.Relation([
{ id: { type: affinity.Integer}},
{ name: { type: affinity.String }},
{exists: { type: affinity.Boolean}}
],[
[1, 'Nicolas', true ],
[2, 'Lancelot', false],
[3, 'Marie', true ],
...
]);
relation.print();
// +--------------+---------------+------------------+
// | id : Integer | name : String | exists : Boolean |
// +==============+===============+==================+
// | 1 | Nicolas | true |
// +--------------+---------------+------------------+
// | 2 | Lancelot | false |
// +--------------+---------------+------------------+
// | 3 | Marie | true |
// +--------------+---------------+------------------+
// Composition
// Like a Join, but removes common attributes
var composed = relation.compose(relation2);
// Difference
// Get tuples in A that are not in B
var difference = relation.difference(relation2);
// Extension
// Adds calculated columns to a relation
var extended = relation.extend([{ newCol : id.plus(name.length())}]);
extended.print();
// +--------------+---------------+------------------+------------------+
// | id : Integer | name : String | exists : Boolean | newCol : Integer |
// +==============+===============+==================+==================+
// | 1 | Nicolas | true | 8 |
// +--------------+---------------+------------------+------------------+
// | 2 | Lancelot | false | 10 |
// +--------------+---------------+------------------+------------------+
// | 3 | Marie | true | 8 |
// +--------------+---------------+------------------+------------------+
// Group
// Groups columns into a RVA (Relation-Valued-Attribute)
var grouped = relation.group('persons', ['id', 'exists']);
grouped.print();
// +---------------+------------------------------------+
// | name : String | persons : Relation |
// +===============+====================================+
// | Nicolas | +--------------+------------------+|
// | | | id : Integer | exists : Boolean ||
// | | +==============+==================+|
// | | | 1 | true ||
// | | +--------------+------------------+|
// +---------------+------------------------------------+
// | Lancelot | +--------------+------------------+|
// | | | id : Integer | exists : Boolean ||
// | | +==============+==================+|
// | | | 2 | false ||
// | | +--------------+------------------+|
// +---------------+------------------------------------+
// | Marie | +--------------+------------------+|
// | | | id : Integer | exists : Boolean ||
// | | +==============+==================+|
// | | | 3 | true ||
// | | +--------------+------------------+|
// +---------------+------------------------------------+
// Ungroup
// Ungroups RVAs
var ungrouped = grouped.ungroup(['persons']);
// Intersection
// Tuples in B that are also in A
var intersected = relation.intersect(otherRelation);
// Joined
// Combinations of tuples in A and B that have the same values for their common attributes
var joined = relation.join(otherRelation);
// Product
// All possible combinations of tuples in A and in B
var product = relation.product(otherRelation);
// Projection
// Selects columns from a relation
var projected = relation.project(['name', 'exists']);
// Rename
// Renames attributes of a relation
var renamed = relation.rename({ name : 'newName', exists : 'newExists' });
// Restriction
// Selects tuple that match a given predicate
var restricted = relation.restrict(
name.substr(0,1).lowercase().eq('l').or(name.substr(0,1).lowercase().eq('m'))
);
// +--------------+---------------+------------------+
// | id : Integer | name : String | exists : Boolean |
// +==============+===============+==================+
// | 2 | Lancelot | false |
// +--------------+---------------+------------------+
// | 3 | Marie | true |
// +--------------+---------------+------------------+
// SemiDifference
// Inverse of SemiJoin : Finds tuples in A that do not have a counterpart in B
var semidifference = relation.semiDifference(otherRelation);
// SemiJoin
// Finds tuples in A that have a counterpart in B. Like a Join, but only returns attributes from A
var semijoined = relation.semiJoin(otherRelation);
// Wrap
// Wraps given attributes into a TVA (tuple-valued-attribute);
var wrapped = relation.wrap('person', ['id', 'exists']);
wrapped.print();
// +--------------+--------------------------------------------+
// | id : Integer | person : Tuple |
// +==============+============================================+
// | 1 | Tuple(name : 'Nicolas', exists : 'true') |
// +--------------+--------------------------------------------+
// | 2 | Tuple(name : 'Lancelot', exists : 'false') |
// +--------------+--------------------------------------------+
// | 3 | Tuple(name : 'Marie', exists : 'true') |
// +--------------+--------------------------------------------+
// Unwrap
// Inverse of Wrap
var unwrapped = relation.unwrap(['person']);
Predicates
For the Restriction and Extension operations, you must pass a predicate that can be composed with the following :
e.g.:
relation.restrict(age.gt(10)); // age > 10
relation.restrict(age.gt(10).or(age.st(50))); // age > 10 || age < 50
relation.extend([
{ lived : died.minus(born) }
])
Function | Shortcut | Returns |
---|---|---|
Connectives | ||
And | and |
Boolean |
Or | or |
Boolean |
Not | not |
Boolean |
Tuple | ||
TupleValue | value |
* |
Comparison | ||
Equal | eq |
Boolean |
Greater Than | gt |
Boolean |
Greater Than or Equal | gte |
Boolean |
Smaller Than | st |
Boolean |
Smaller Than or Equal | ste |
Boolean |
Numeric Functions | ||
Absolute | abs |
Numeric |
Ceil | ceil |
Numeric |
Cosine | cos |
Numeric |
Division | div |
Numeric |
Exponential | exp |
Numeric |
Minus | minus |
Numeric |
Modulo | mod |
Numeric |
Multiplication | times |
Numeric |
Plus | plus |
Numeric |
Power | pow |
Numeric |
Roof | roof |
Numeric |
Round | round |
Numeric |
Sine | sin |
Numeric |
Square Root | sqrt |
Numeric |
String Functions | ||
Length | length |
Integer |
Lowercase | lowercase |
String |
Matching | matching |
Boolean |
Substring | substr |
String |
Uppercase | uppercase |
String |
Date Functions | ||
Day of Month | dayOfMonth |
Integer |
Day of Week | dayOfWeek |
Integer |
Day of Year | dayOfYear |
Integer |
Week of Year | weekOfYear |
Integer |
Year | year |
Integer |
Month | month |
Integer |
Hours | hours |
Integer |
Minutes | minutes |
Integer |
Seconds | seconds |
Integer |
Milliseconds | milliseconds |
Integer |
Timestamp | ts |
Integer |