garbett.org

I've been a grep power user for years, I've been slowly writing a modified script called grok for some time. Then I found ack which just blows it away.

Software viruses first appeared in 1970 propagating through the TENEX system. As the years went by more and more varieties of these nasties appear, trojans and worms also came on the scene. A worm is different from a virus in that it doesn't need a host program to infect, it can run stand alone. It's a parasite like bacteria is a parasite. Most biologists don't classify a bacteria as a parasite, but this is a matter of some debate outside the scope of this post.

What this post is about is the appearance of a true fully formed parasite in a software system known as Vista. The parasite is called DRM, Digital Rights Management. A parasite is defined in my Apple dictionary as "an organism that lives in or on another organism (its host) and benefits by deriving nutrients at the host's expense." DRM has no benefit to me the consumer, in fact it harms my ability for fair rights use. Yet DRM is at the core of Vista's design[0]. It consumes CPU making my computer run slower, makes for all kinds of incompatibilities and can even distort images that I own outright, if Vista feels these images should be copyright controlled. My computer, the host if I install the Vista OS, will feed DRM nutrients (CPU time and preventing fair use) at my expense of buying the system and the hardware. The only stretch here is calling DRM an organism, but this is the same stretch applied when coining the term software virus. DRM has organizations behind it as well, the RIAA and MPAA.

Recently there was a video on You Tube about a zombie caterpillar. Once infected by a wasp the poor caterpillar after giving birth to the wasp larvae, sticks around and defends the larvae. Somewhere along the way, Microsoft got stung by the RIAA and MPAA, is now infected deeply with this new form of malware known as DRM. It's now at the point that it defends the choice of DRM, like the zombie caterpillar.

I want DRM installed in my OS about like I want to eat tapeworm eggs for lunch. I think the market is agreeing with my sentiment in this regard. Apple has made huge gains in market share in recent years, and businesses are balking at upgrading XP systems.

Let's call DRM what it is, a parasite. And let's call Vista what it is, a parasitic ridden operating system looking for a host. While you're at it, go ahead and start this meme into the information system culture at large. It's an easily identified word association which abstractly reflects the reality of the situation. Besides, payback is fair play, remember when Microsoft describe the GPL as viral?

I've been fiddling with the document database CouchDB, which is an awesome idea still in it's infancy. It short circuits so many strange things by making some non-intuitive assumptions, i.e. direct access to a database with no schema storing only JSON. The protocol is simply enough, the HTTP Restful stack. That's equivalent to cutting out all the middle men when purchasing something and getting straight from the factory door.

I've seen blog posts about where folks immediately say, none of the big service providers would ever consider doing something crazy like that. I got news for you, Google, Amazon and Yahoo do something very similar but with a proprietary code base. The whole database is built around the map/reduce concept which is highly distributable, and replicable. The core language is Erlang, which most would consider even crazier, but since it's built on pi-calculus everything is a process and the idea of failure & recovery, replication, distributing load is all built in as primitives in the language.

So now that you're past the shock value, is it real? Is this another silver bullet of hype that the IT industry delivers with machine gun like bursts? The answer is, yes it is real, but it's still very alpha-- so don't consider using it for production. And no, it isn't a silver bullet, you still have to work, stop asking already. It still has issues to solve (hint: authentication). But it works, and it works amazingly well.

In my first attempt at learning it, I wanted to build an extjs data store that automatically mapped into the database. Usually extjs leaves it up to the developer to define the database interface, but in the case of CouchDB, it's got a fixed simple API, remember it's RESTful. So the API to the database is the same everytime and can be coded as such.

Here's my simple example target usage:

    var store = new Ext.ux.data.CouchStore({
      db:    'databasenamehere',
      view:  'locations/all',
      fields: [
        {name: '_id'        },  // I'd love to get rid of this as well
        {name: '_rev'       },  // ditto
        {name: 'location'   },
        {name: 'address',   },
        {name: 'latitude',  type: 'float'},
        {name: 'longitude', type: 'float'}
      ]
    });
    Ext.ux.store.load({});

Things like adding become:

    x = new store.record({
      location : 'work',
      address  : '123 over the rainbox'
    });

    store.add(x);

Or updating some records:

    var r = store.getAt(0);
    r.set('location', 'Yahoo!');
    store.commitChanges();

Or deleting something:

    var r = store.getAt(0);
    store.remove(r);

So I've attached my first pass at satisfying this API, link.

Requirements are simple, users type in a field any combination of first name, last name, birth date, social or id and get a search result back to the screen of anyone who matches this criteria. The design constraint is that it must work in PL/SQL working as a web server. Let's see what's required to make that happen.

The response is in javascript, so using htp_join the function is simple.

procedure client_data_store(query  in varchar2 := '',
                            fields in varchar2 := 'first_name last_name id') is
  result_cur      sys_refcursor;
begin
  htp.p('[');

  if length(ltrim(query)) > 0 then
    open result_cur for client_search(query,fields);
    htp_join(result_cur);
    close result_cur;
  end if;

  htp.p(']');
end client_data_store;

Wow! That was easy, but wait it depends on building the client_search cursor dynamically. This code gets fun fast. Let's look at that.

The client select statement has to be built dynamically, so a bit of magic of functional code later and one has the following:

function client_search(query  varchar2,
                       fields varchar2)
                       return varchar2 is
  search varchar2(4000) := '
    select ''[''''''||id||'''''', ''''''||first_name||'''''', ''''''||last_name||'''''', ''''''||
           to_char(birth_date, ''MM/DD/YYYY'')||'''''', ''''''||
           search.format_ssn(social_security_number)||'''''', ''''''||
           street1||'', ''||city||'' ''||state||'' ''||zip_code||'''''']''
      from clients, addresses
     where clients.id = addresses.client_id (+)';

  search2 varchar2(4000) := '              
       and rownum < 1000
     order by last_name, first_name'; 

  search_term varchar2(2000);

  cursor tokens is select * from table(regexp_split(query));
begin
  for token in tokens loop
    search_term := client_search_expr(token.column_value, fields);
    if search_term is not null then 
      search := search||' and ('||search_term||')';
    end if;
  end loop;
  return search||search2;
end client_search;

What's really beautiful about this is the levels of escaping required to get the single quotes right. I tried to create and escaping function to do that and it was uglier and more cryptic, so that was abandoned. Then I tried using some common strings and including those, but once again the ugly factor shot up fast. Like a Russian doll another layer of functional abstraction awaits, the clientsearchexpr, which parses the input to build the and clause.

function search_expr(token   varchar2,
                     fields  varchar2)
                     return  varchar2 is
begin
  return
    case
      when regexp_like(token, '\A\d{1,2}\-\d{1,2}\-\d{4}\z') then
      ( case 
        when instr(fields, 'birth_date') <> 0 then
          'birth_date = '''||to_date(token, 'MM-DD-YYYY')||''''
        else
          NULL
        end
      )
      when regexp_like(token, '\A\d{1,2}\/\d{1,2}\/\d{4}\z') then
      ( case
        when instr(fields, 'birth_date') <> 0 then
         'birth_date = '''||to_date(token, 'MM/DD/YYYY')||''''
        else
          NULL
        end
      )
      when regexp_like(token, '\A\d{9}\z') then
      ( case
        when instr(fields, 'id') <> 0 and instr(fields, 'social_security_number') <> 0 then
          'id = '||token||' or social_security_number = '||replace(token,'-','')
        when instr(fields, 'id') <> 0 then
          'id = '||token
        when instr(fields, 'social_security_number') <> 0 then
          'social_security_number = '||replace(token,'-','')
        else
          NULL
        end
      ) 
      when regexp_like(token, '\A\d+\z') then
      ( case 
        when instr(fields, 'id') <> 0 then
         'id  = '||token
        else
          NULL
        end
      )
      when regexp_like(token, '\A\d{3}-\d{2}-\d{4}\z') then
      ( case
        when instr(fields, 'social_security_number') <> 0 then
          'social_security_number = '||replace(token,'-','')
        else
          NULL
        end
      ) 
    else
      ( case
        when instr(fields, 'first_name') <> 0 and instr(fields, 'last_name') <> 0 then
          'lower(first_name) like ''%'||lower(token)||'%'' or lower(last_name) like ''%'||lower(token)||'%'''
        when instr(fields, 'first_name') <> 0 then
          'lower(first_name) like ''%'||lower(token)||'%'''
        when instr(fields, 'last_name') <> 0 then
          'lower(last_name) like ''%'||lower(token)||'%'''
        else
          NULL
        end
      )
    end;  
end client_search_expr;

Holy crap batman! There's got to be an easier way. (hint, hint, please show me the code.) Unfortunately for me, this is the best I've come up with to date. In Rails, Perl, PHP, C#, Java, I doubt it would be this difficult. The search expression loop would be the hardest.

Now a user can type 'Joe Blow' and get back a search list containing any client with Joe as a name or Blow as a name. Or they can type the social, or the birth date. It all just works.

I found a wonderful article that demonstrated a join in PL/SQL and began playing with it. The article states (correctly) that the limit in PL/SQL for strings is 32767 and uses that throughout as a value. Well the problem is that one runs a query to drive it, and SQL in Oracle has a 4000 character limit. I fiddled and fiddled and convinced myself that this was a fundamental problem with the language.

Since the company I work for uses an Oracle database as a webserver, the real problem I was trying to solve was a join to spit out correctly formatted javascript arrays. So instead of trying to concate the string until completion, I just stream it to the target output stream. Here's the modified function:

create or replace procedure htp_join( p_cursor sys_refcursor,
                                      p_del    varchar2     := ',' ) is
  l_value   varchar2(32767);
  l_last    varchar2(32767);
  l_found   boolean := false;
begin
  -- Adapted from http://articles.techrepublic.com.com/5100-10878_11-5259821.html
  loop
    fetch p_cursor into l_value;
    exit when p_cursor%notfound;
    if l_last is not null then
      htp.p(l_last || p_del);
    end if;
    l_last := l_value;
  end loop;
  if l_last is not null then
    htp.p(l_last);
  end if;
end htp_join;

Voila, a wonderful join that's bound to it's output stream. Well so much for functional programming in PL/SQL. I'll be posting an example usage in a later post.

"I got chunks of this javascript framework in my stool." -- Brad Winfrey

I am currently porting some code from Ruby to PL/SQL, and the original language supports wonderful constructs like split and block calls. So one can do things like the following:

>> 'a b c'.split.each do |ltr|
?>   puts ltr;
?> end
a
b
c

This is an immediate wall to progress on the port, and so I googled split and found some really sub optimal solutions till I found an article on Tech Republic that had some sample code.

Turns out that PL/SQL supports this idea of pipelining results from a function. Reminds me of my first exposure to a UNIX shell. Hmmm, writing bash equivalents in PL/SQL. Now that would be a hack of note.

The first thing I realized that the code was not really doing a split as Ruby did it. Because as the rdoc for the ruby function states, “If pattern is a single space, str is split on whitespace, with leading whitespace and runs of contiguous whitespace characters ignored.” I thought about it for a minute and that was exactly what I needed.

So I did a test to see what would happen with split with extra spaces or delimiters:

SQLPLUS> select nvl(column_value, 'NULL') from table(split(' a  b c d'));

NVL(COLUMN_VALUE,'NULL')                                                                                                                                
------------------------                                                                                                                                
NULL                                                                                                                                
a                                                                                                                                
NULL                                                                                                                                
b                                                                                                                                
c                                                                                                                                
d                                                                                                                                

6 rows selected

At first I thought to modify this to discard the nulls, but this is exactly what one would expect from a fixed length delimiter split. So the next step is to write a regex version of split.

create or replace function regexp_split (p_list   varchar2,
                                         p_regex  varchar2 := '\s+')
                           return split_tbl pipelined
is
    l_list   varchar2(32767) := p_list;
    l_idx    pls_integer;
    l_end    pls_integer;
begin
    loop
        l_idx := regexp_instr(l_list, p_regex);
        l_end := regexp_instr(l_list, p_regex, 1, 1, 1);
        if l_idx > 0 then
            pipe row(substr(l_list, 1, l_idx-1));
            l_list := substr(l_list, l_end);
        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end regexp_split;

A test of that function resulted in the following:

SQLPLUS> select nvl(column_value, 'NULL') from table(regexp_split(' a  b c   d '));

NVL(COLUMN_VALUE,'NULL')                                                                                                                                
------------------------                                                                                                                                
NULL                                                                                                                                
a                                                                                                                                
b                                                                                                                                
c                                                                                                                                
d                                                                                                                                
NULL

Good but not good enough. Technically, the leading and trailing space are delimiters and this is the correct functional transform of the input, but it violates the principle of least surprise, in the that for decades other languages under such a request will strip the leading and trailing space. But only in the case of space. Think about comma separated values in a string, e.g. ”,,1,2,3,”, most certainly one would expect a translation to result in NULL, NULL, 1, 2, 3, NULL. So the only exception for null values is when splitting on space, via taking the default value.

So one must strip the leading and trailing spaces if the default is used. Curiously enough PL/SQL doesn’t have a decent trim function, so one must call ltrim and rtrim in succession for the same result. So I added this after the begin.

    if p_regex = '\s+' then
      -- space, tab, form feed, return
      l_list := rtrim(p_list, ' '|| chr (9) || chr (10) || chr (13));
      -- do it again because trim doesn't support multiple characters
      l_list := ltrim(l_list, ' '|| chr (9) || chr (10) || chr (13));
    end if;

And a test of this function results in the following output:

SQLPLUS> select nvl(column_value, 'NULL') from table(regexp_split(' a  bb ccc  dddd '));

NVL(COLUMN_VALUE,'NULL')                                                                                                                                
------------------------                                                                                                                                
a                                                                                                                                
bb                                                                                                                                
ccc                                                                                                                                
dddd                                                                                                                                

4 rows selected

Now that I’m past line one of the port, I have only the other 30 lines of the function to translate. Should be a piece of cake.

The new instant client for Oracle is out. If you've been trying to develop a Rails application on a Mac book Pro, you'll know what this means. Oracle has basically ignored these users and it's been difficult at best to make these play happy. Fortunately Oracle has now decided to play ball with Mac Rails developers.

So to install, I downloaded 3 packages: instantclient-basic-macosx-10.2.0.4.0.zip, instantclient-sqlplus-macosx-10.2.0.4.0.zip, and instantclient-sdk-macosx-10.2.0.4.0.zip. Since I was using Safari on Leopard, they were automatically extracted and expanded into my Download directory. So I did the following:

sudo mkdir /usr/local/lib/instantclient
sudo cp ~/Downloads/instantclient_10_2/* /usr/local/lib/instantclient
sudo cp ~/Downloads/instantclient_10_2-1/* /usr/local/lib/instantclient
sudo cp ~/Downloads/instantclient_10_2-2/* /usr/local/lib/instantclient
sudo ln -s /usr/local/lib/instantclient/sqlplus /usr/local/bin/instantclient
cd /usr/local/lib/instantclient
sudo ln -s libclntsh.dylib.10.1 libclntsh.dylib

Then I added the following to my ~/.profile:

export ARCHFLAGS=' -arch i386 ' 
export DYLD_LIBRARY_PATH=/usr/local/lib/instantclient
export PATH=/usr/local/bin:$PATH

Followed by a:

source ~/.profile

And copied an appropriate tnsnames.ora to /etc. Note: Make sure that it's a unix text file, not a DOS with cr/lf.

Then it's ready to test with sqlplus to a database you have access too.

Now, for the ruby drivers. I grabbed the latest from rubyforge.org: ruby-oci8-1.0.0.tar.gz. Once again Safari extracted it for me, almost.

cd ~/Downloads
tar xvf ruby-oci8-1.0.0.tar
cd ruby-oci8-1.0.0
make
sudo make install
ruby -r oci8 -e 'OCI8.new("username", "password", "database").exec("select * from dual") do |r| puts r.join(","); end'

One should see an "X" marks the spot. And thar' she blows! Oracle native access through ruby on an intel mac.

Time breaks on the cliff.
My soul aches under the weight,
dreams are eroding.

Life blossoms anew.
I pour my hopes from the tree;
lost in the branches.

Our brotherhood grows,
possibilities explored.
Fools and Genius do the dance.

I'm posting my resume for the web search engines to pick up and make current. Link