I am going to use Conway‘s Life to show off some of Kognitio’s features, including some which are new in version 8.1.50.

Life is a well-known toy problem so I won’t explain it in great detail beyond defining it. The rules are simple yet they lead to surprising emergent properties including periodic sequences and even a Turing machine:

  • At each of a sequence of time steps staring from zero, each cell in an infinite square lattice is either “alive” or “dead”.
  • At step zero there is a chosen starting configuration of dead or alive cells.
  • A dead cell will become alive in the next step if it has exactly 3 neighbours in the current step.
  • A living cell will remain alive in the next step if it has 2 or 3 neighbours in the current step.
  • All other cells will remain or become dead in the next step.

For example:

first step for a glider

We start by modelling the lattice. In principle a two-dimensional boolean array would work, but a table listing the cells that are alive in the current step – a sparse array – is more practical and will benefit from
Kognitio’s parallelism.

-- New syntax to drop a table but not give an error if it doesn't exist
drop table if exists LIFE cascade;

create table LIFE (X int not null, Y int not null, unique (X, Y));

The glider, pictured above, is a simple pattern with interesting behaviour so I will use that as an initial position.

-- Insert a glider
insert into LIFE values (0, 0), (0, 1), (0, 2), (1, 2), (2, 1);

We need a nice way to display the current state in LIFE, rather than trying to visualise a table of coordinates. This could be achieved by exporting the contents of LIFE and rendering it in an external program. But a simpler way is to use Kognitio’s external scripting feature, which allows the user to define functions on tables, to write a Python script to convert a CSV list of X and Y coordinates into a ascii-art graph for each row.

-- New syntax to drop a script environment but not error if it doesn't exist
drop script environment if exists PYTHON;

create script environment PYTHON command '/usr/bin/python';

-- New syntax to drop an external script but not error if it doesn't exist
drop external script if exists SHOW_LIFE;

create external script SHOW_LIFE environment PYTHON
receives(X int, Y int)
-- Need to preserve leading spaces because of legacy behaviour
sends (Y int, CELLS varchar(32000)) output 'fmt_preserve_leading_spaces 1'
-- Use limit 1 threads so that everything goes to the same node, otherwise
-- we end up with wrong output such as  Y|CELLS  instead of  Y|CELLS
--                                      2|**                 2|***
--                                      2|*                  1|*
--                                      1|*                  0| *
--                                      0| *
-- We could use a partition and then relax that, which would allow parallelism,
-- but we need to synchronise the min_x and min_y values between the scripts.
-- That might be the subject of a future blog post.
limit 1 threads
script LS'python(
    import csv, sys

    min_x = min_y = sys.maxint
    max_x = max_y = -sys.maxint - 1
    cells = {}

    csvreader = csv.reader(sys.stdin)
    for (x, y) in csvreader:
        x = int(x)
        y = int(y)
        cells[(x, y)] = ()
        if x < min_x:
            min_x = x
        if x > max_x:
            max_x = x
        if y < min_y:
            min_y = y
        if y > max_y:
            max_y = y

    # Allow a margin around the cells
    min_x -= 1
    min_y -= 1
    max_x += 1
    max_y += 1
    
    if len(cells) > 0:
        csvwriter = csv.writer(sys.stdout)
        for y in reversed(range(min_y, max_y + 1)):
            row = ""
            for x in range(min_x, max_x + 1):
                if (x, y) in cells:
                    row += '*'
                else:
                    row += ' '
            csvwriter.writerow([y, row])
        
        csvwriter.writerow(["", "Top left is at (%s, %s)"%(min_x, max_y)])
)python';

external script SHOW_LIFE from (select * from LIFE)
order by Y desc;

We can also write a script to populate the table from a .LIF file rather than having to write an INSERT statement. (This could alternatively be defined as an external table.)

drop external script if exists GET_LIFE;

create external script GET_LIFE environment PYTHON
sends (X int, Y int)
-- Use limit 1 threads to avoid duplicates
limit 1 threads
script LS'python(
        import os, csv, sys, urllib2

        # Download the file from the given URL
        life_file = os.environ["WX2_LIFE_URL"]
        req = urllib2.Request(life_file)
        response = urllib2.urlopen(req)
        result = response.readlines()

        csvwriter = csv.writer(sys.stdout)
        if result[0].strip() == "#Life 1.06":
                # Life 1.06: parse space-separated values
                for l in result[1:]:
                        (x, y) = l.split()
                        csvwriter.writerow([x, y])
        elif result[0].strip() == "#Life 1.05":
                # Life 1.05: parse text pattern
                x = 0
                y = 0
                for l in result[1:]:
                        l = l.strip()
                        if len(l) == 0:
                                continue
                        if l[0] == '#':
                                if l[1] == 'P':
                                        (p, x, y) = l.split()
                                        x = int(x)
                                        y = int(y)
                        else:
                                for c in l:
                                        if c == '.': # cell is dead
                                                pass
                                        elif c == '*': # cell is alive
                                                csvwriter.writerow([x, y])
                                        else:
                                                print "Unexpected character"
                                                exit(1)
                                        x += 1
                                y += 1
        else:
            print "Unknown LIFE file format"
            exit(1)
)python';

insert into LIFE
    external script GET_LIFE
    -- Use the script parameter LIFE_URL to give the URL: a 10-engine Codership
    -- Or use a URL like 'file://localhost/path/to/file.lif' for a local file
    parameters LIFE_URL='http://www.conwaylife.com/patterns/10enginecordership_105.lif';

Define a table NEIGHBOUR_MASK of the pairs (X, Y) that are neighbours of the origin, to provide a 3-by-3 “mask” for eight neighbouring cells.

drop table if exists NEIGHBOUR_MASK cascade;

create table NEIGHBOUR_MASK (DX int not null, DY int not null);

insert into NEIGHBOUR_MASK values (-1,  1), ( 0,  1), ( 1,  1),
                                  (-1,  0),           ( 1,  0),
                                  (-1, -1), ( 0, -1), ( 1, -1);

Define a view NEIGHBOUR_COUNT that joins the mask to LIFE, returning a count of the number of neighbours that are alive for each (X, Y) position that has any living neighbours (even if the position itself is dead).

-- New syntax to drop a view but not give an error if it doesn't exist
drop view if exists NEIGHBOUR_COUNT;

create view NEIGHBOUR_COUNT as
     select X + DX as X, Y + DY as Y, count(*) as NEIGHBOURS
     from LIFE, NEIGHBOUR_MASK
     group by 1, 2;

To calculate the position in the next step we need an INSERT to add cells to the current position in LIFE that become alive, and a DELETE to remove cells from the current position in LIFE that die. Kognitio provides MERGE allowing us to do the delete and insert atomically, which is essential because each makes changes that will affect the other. Each time the MERGE is run, LIFE will be updated to the next step. (Alternatives to MERGE would be to add a step number column to LIFE or use an additional working table.)

-- Update LIFE to the next step
-- New MERGE syntax with WHEN MATCHED DELETE
merge into LIFE LIFE
using NEIGHBOUR_COUNT N
on LIFE.X = N.X and LIFE.Y = N.Y
-- Use DELETE to remove cells that die
when matched and N.NEIGHBOURS not in (2, 3) then delete
-- Use INSERT to add cells that become alive
when not matched and N.NEIGHBOURS = 3 then insert values (N.X, N.Y);

Here is the output for the glider for the first few steps. After the fourth step the glider has been replaced by a copy of the original moved one position diagonally.

          Y|CELLS
     <null>|Top left is at (-1, 3)
          3|
          2| **
          1| * *
          0| *
         -1|

          Y|CELLS
     <null>|Top left is at (-2, 3)
          3|
          2|  **
          1| **
          0|   *
         -1|

          Y|CELLS
     <null>|Top left is at (-2, 3)
          3|
          2| ***
          1| *
          0|  *
         -1|

          Y|CELLS
     <null>|Top left is at (-2, 4)
          4|
          3|  *
          2| **
          1| * *
          0|

          Y|CELLS
     <null>|Top left is at (-2, 4)
          4|
          3| **
          2| * *
          1| *
          0|

Finally, the Kognitio extension EXEC can be used to have the database repeat the merge an arbitrary number of times rather than having to repeatedly run it manually.

-- Execute the MERGE four times
exec select
       'merge into LIFE LIFE
        using NEIGHBOUR_COUNT N
        on LIFE.X = N.X and LIFE.Y = N.Y
        when matched and N.NEIGHBOURS not in (2, 3) then delete
        when not matched and N.NEIGHBOURS = 3 then insert values (N.X, N.Y)'
     from values between 1 and 4;