Part one discussed using SQL to solve directed graph problems. Another kind of directed graph is a state machine. In a state
machine, each node is called a state and represents the current
status of a system, object, or variable. There are vectors between
each state that represent a change in state caused by an external

For example, Windows event-based architecture
or JavaScript
events are designed around the idea that a GUI contains a set of
objects that respond to various user-input or system-generated

Even a programming language compiler is a state machine. A compiler
recognizes tokens by shifting between different states until a
token is “recognized.” A popular utility, lex, was often used to
take a set of tokens, or reserved words, and convert it and
optimize it into a state machine in C. You can duplicate this same
process in SQL, with the advantage that the generated code can be
in any language.

First, create a table of tokens:

create table tokens
    id$         integer
constraint tokens_pk primary key,
token       varchar2(20)

A state machine needs to store a set of states
and a set of transitions between states. For this example, a single
input character will represent each transition:

create table states
    id$         integer
constraint states_pk primary key,
    accept#     integer,

    code        varchar2(20),

    constraint  states_accept_fk foreign
key (accept#) references tokens(id$),
    constraint  states_accept_uq unique

create table trans
    state$#     integer
not null,
    input$      char
not null,
    next#       integer,

    constraint  trans_pk primary key
    constraint  trans_state_fk foreign
key (state$#) references states(id$),
    constraint  trans_next_fk foreign key
(next#) references states(id$)

The problem is to build up these tables of
states and transitions based on the set of tokens. As in the
previous directed graph problem, we’ll start with some initial node
(in this case, the initial state) and try to find all the routes
between different states. Each route is the set of characters
recognized so far. For example:

state 0: initial state, empty string
state 1: ‘A’ state
state 2: ‘AA’ state
state 3: ‘AB’ state
state 4: ‘ABC’ state

You can populate the transition table by
associating the letter (or regular expression in a more realistic
program) with the state we’re currently in and the state at which
we’ll arrive. Let’s populate and optimize the table with this

    l_state pls_integer := 0;
    l_len pls_integer := 1;
    — populate states
    insert into states (id$) values
        insert into


distinct substr(token,1,l_len) code
length(token) >= l_len

        exit when
sql%rowcount = 0;
        l_state := l_state
+ sql%rowcount;
        l_len := l_len +
    end loop;
    — populate transitions
    insert into trans
states s1,states s2
(s1.code = substr(s2.code,1,length(s2.code)-1))
($ = 0 and length(s2.code) = 1);
    — map accept states to tokens
    update states
       set accept# = (select
id$ from tokens where token = states.code);
show errors;

The first statement inserts the initial state.
Then, for cutting up the tokens into different lengths, we can
enumerate all the states. Next, we populate the transition table
with the character required to get from one state to the next.
Finally, we attach the token id to the final state to indicate that
this state represents a complete token. For example, if we add
three tokens, where 1 = AAA, 2 = ABC, 3 = ASC, then running the
above code will result in data, which you can see in Table A.

You can use this table to generate code in many
languages that process characters from strings efficiently. The
only code that gets used in a recognition program is the “accept”
column for states, and the input and next state columns from the
transition table. For example, in C:

#include <stdio.h>

typedef struct state state;
typedef struct trans trans;

struct state
accept;               /*
token value or 0 */
offset;               /*
offset into transition table */
    int count;
struct trans
input;               /*
input character */
next;              /*
next state */

state statetbl[8] =
    {0,0,1},                  /*
0: initial state */
    {0,1,3},                  /*
1: ‘A’ state */
    {0,4,1},                  /*
2: ‘AA’ state */
    {0,5,1},                  /*
3: ‘AB’ state */
    {0,6,1},                  /*
4: ‘AS’ state */
    {1,7,0},                  /*
5: ‘AAA’ state */
    {2,7,0},                  /*
6: ‘ABC’ state */
    {3,7,0},                  /*
7: ‘ASC’ state */
trans trantbl[7] =
   {‘A’,1},                   /*
0: initial -> ‘A’ */
   {‘A’,2},                   /*
1: ‘A’ -> ‘AA’ */
   {‘B’,3},                   /*
2: ‘A’ -> ‘AB’ */
   {‘S’,4},                   /*
3: ‘A’ -> ‘AS’ */
   {‘A’,5},                   /*
4: ‘AA’ -> ‘AAA’ */
   {‘C’,6},                   /*
5: ‘AB’ -> ‘ABC’ */
   {‘C’,7},                   /*
6: ‘AS’ -> ‘ASC’ */

/* given a current state and input character, return the next
state */
int move(int state,char input)
    int i;
    for (i =
(trantbl[statetbl[state].offset+i].input == input)
-1;      /* unknown transition

int main(int argc,char* argv[])
    int i,next,state;
    if (argc < 2)
%s \”tokens…\”\n”,argv[0]);
        return 1;

    /* parse the first argument */
    state = 0;
    for (i=0;argv[1][i];i++)
        next =
        if (next == -1)
state = 0;
= next;
    return 0;

Then, you can run this program and get tokens
recognized on the command line:

$ cc -o token token.c

$ token  “ABC ASC FEF ASC”
recognized token#2
recognized token#3
recognized token#3

TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!