postgresql hstore is easy to compare

hstore is an option key=>value column type that’s been around in postgresql for a long time. I was looking at it for a project where I want to compare “new data” to old, so I can approve it. There is a hstore-hstore option that compares two hstore collections and shows the differences.

In reality, an hstore column looks like text. It’s just in a format that postgresql understands.

Here, we have an existing record with some network information.

hs1=# select id, data::hstore from d1 where id = 3;
 id |                          data                          
----+--------------------------------------------------------
  3 | "ip"=>"192.168.219.2", "fqdn"=>"hollaback.example.com"
(1 row)

Let’s say I submitted a form with slightly changed network information. I can do a select statement to get the differences.

hs1=# select id, hstore('"ip"=>"192.168.219.2", "fqdn"=>"hollaback01.example.com"')-data from d1 where id =3;
 id |             ?column?              
----+-----------------------------------
  3 | "fqdn"=>"hollaback01.example.com"
(1 row)

This works just as well if we’re adding a new key.

hs1=# select id, hstore('"ip"=>"192.168.219.2", "fqdn"=>"hollaback01.example.com", "netmask"=>"255.255.255.0"')-data from d1 where id =3;
 id |                           ?column?                            
----+---------------------------------------------------------------
  3 | "fqdn"=>"hollaback01.example.com", "netmask"=>"255.255.255.0"
(1 row)

This information could be displayed on a confirmation page. Ideally, a proposed dataset would be placed somewhere, and a page could be rendered on the fly showing any changes an approval would create within the database.

Then we can update with the newly submitted form.

hs1=# update d1 set data = data || hstore('"ip"=>"192.168.219.2", "fqdn"=>"hollaback01.example.com", "netmask"=>"255.255.255.0"') where id = 3;
UPDATE 3

hs1=# select id, data::hstore from d1 where id = 3; id |                                         data                                         
----+--------------------------------------------------------------------------------------
  3 | "ip"=>"192.168.219.2", "fqdn"=>"hollaback01.example.com", "netmask"=>"255.255.255.0"
(1 row)

Note that if I wanted to delete a key instead of just setting it to NULL, that would be a separate operation.

update d1 SET data = delete(data, 'ip') where id = 3;
UPDATE 1

http://stormatics.com/howto-handle-key-value-data-in-postgresql-the-hstore-contrib/

Codebox in Docker

This is a quick note log. I was able to setup a self-hosted web IDE for programming. Today I tested out codebox.io,
but I also want to check out the open source Cloud9 IDE.

  • Codebox.io an open-source Web IDE for programming (in your browser)
  • Docker an open source container
  • Docker-Codebox. All I really want is this guy’s Dockerfile.

I already have Docker installed, that’s the easy bit.

Build the box:

git clone https://github.com/forty9ten/docker-codebox.git
cd docker-codebox
docker build -t ytjohncodebox .
# much building occurs

Run codebox in docker

This will run the codebox.io environment on port 8000 and you’ll be editing files that are stored in a directory
called workspace1. ~/workspace1 on the host gets mounted into /workspace1 in the container.

cd ~
mkdir workspace1
docker run -p 8000:8000 -v ./workspace1:/workspace1 -t ytjohncodebox -p 8000 run /workspace1
# many things happen
# I end up seeing this:
# Codebox is running at http://localhost:8000

Now, I can access my server on port 8000 (ie, http://192.168.1.32:8000/). This instance is unprotected, it just asks
for an email to get started. But the part I skipped over is that I am actually using nginx to proxy and password
protect this instance.

I can hit Ctrl+C on the terminal to cancel my instance and all my edited files are safely stored in ~/workspace1.

Interesting bits:
I create a new file in the web browser and save it, I see this bit of json in the console output:

[events] watch.change.create : { change: 'create',
  path: '/irule.txt',
  stats: 
   { current: 
      { dev: 64513,
        mode: 33188,
        nlink: 1,
        uid: 0,
        gid: 0,
        rdev: 0,
        blksize: 4096,
        ino: 262847,
        size: 27,
        blocks: 8,
        atime: Wed Feb 26 2014 05:18:44 GMT+0000 (UTC),
        mtime: Wed Feb 26 2014 05:18:42 GMT+0000 (UTC),
        ctime: Wed Feb 26 2014 05:18:42 GMT+0000 (UTC) },
     old: null } }

One thing I really need is the ability to open a web terminal. When I do though, the terminal window appears for a
few seconds, and then vanishes. In the console, I see this:

[log][shells.stream] new socket connected
[log][shells.stream] open shell  { shellId: 'term2020-44',
  opts: { rows: 80, columns: 24, id: 'term2020-44' } }
[log][events] shell.spawn : { shellId: 'term2020-44' }
[log][events] shell.attach : { shellId: 'term2020-44' }
[log][events] shell.open : { shellId: 'term2020-44' }
[log][events] shell.exit : { shellId: 'term2020-44' }
[log][shells.stream] socket disconnected
[log][shells.stream] socket disconnected
[log][hooks] use hook settings

I got this same issue going through my nginx proxy and connecting directly on port 8000. A bit of text flashes
quickly on the “terminal” in the web browser, but closes too quickly for me to catch it. Perhaps it’s attempting to
run something that is not installed in the Docker instance. If I can fix that, then I just need to come up with a cool
way to launch workspaces and tie them into my nginx setup (or switch over to hipache
as my front-end webserver).

Anyways, just wanted to record these steps here and show how easy it could be to get your own self hosted IDE.

Starting with Ruby and AWS

This weekend I decided to takle both learning Ruby and working with AWS
via the Ruby API. Having only played with both of these in the past,
this presents two learning challenges at once. However, from past
projects, this is how I learn best. I am somewhat familiar with AWS
terms and once made a script in Python to fire up an instance. This was
before Amazon came out with their management console, so I imagine
things have come a long way since then (hopefully easier). I also played
with Ruby for a while, but didn’t have a decent project for it. Having a
project with goals will hopefully keep me on track and give me a way to
measure my progress.

My goals for this project are as follows:

  1. Utilize a web based interface. Using rails seems to be the popular
    way to do this, and I’d like to base my template interface off of
    boilerstrap5, a combination of twitter-bootstrap and
    html5boilerplate. This will probably have the most trial and error
    to get it right.
  2. Connect to the AWS api and pull some basic information such as my
    account name.
  3. Fetch details about an AMI image. Maybe I’ll be able to parse a list
    of public images, or maybe I can just punch in an image ID and pull
    up the details.
  4. Start an instance from an AMI image. This might require some steps
    like setting up a an S3 bucket — we’ll see.
  5. List my running instances.
  6. Control a running instance – ie, power cycle it.
  7. Destroy an instance.
  8. BONUS: Do something similar with S3 buckets – create, list, destroy.

First off, I need to setup a ruby development environment. Since I have
used PyCharm in the past, I will try JetBrain’s RubyMine for my
editor environment. After installing this, the first thing I learned is
that rails is not installed. I could install using apt-get, but
Jetbrains recommends using RVM. It looks like a nice way to manage
different versions of Ruby, rails, and gems. I know when I have
installed Ruby applications requiring gems, gem versions was always a
source of concern. It is very easy to get mismatched gem versions in the
wild.

RVM install locally to ~/.rvm on linux, which is nice – you don’t mess
up any system wide ruby installations and keep everything local to your
development environment. After installation, I had to figure out a
couple bits with rvm.

  • rvm install 1.9.2 # installs ruby 1.9.2
  • rvm list # lists versions of ruby installed
  • rvm use 1.8.7 # use ruby 1.8.7

First, your terminal has to be setup as a login shell. This tripped me
up for a while until I changed the settings in my terminal emulator.
terminator has this as checkmark option.

[email protected]:~$ rvm list

rvm rubies

   ruby-1.8.7-p371 [ x86_64 ]
   ruby-1.9.2-p320 [ x86_64 ]

# => - current
# =* - current && default
#  * - default

[email protected]:~$ rvm use 1.8.7

RVM is not a function, selecting rubies with 'rvm use ...' will not work.

You need to change your terminal emulator preferences to allow login shell.
Sometimes it is required to use `/bin/bash --login` as the command.
Please visit https://rvm.io/integration/gnome-terminal/ for a example.

After switching to login

[email protected]:~$ rvm use 1.8.7
Using /home/ytjohn/.rvm/gems/ruby-1.8.7-p371

Finally, once you get ruby and rails working, you can create your rails
project. I’m starting with a rails project because it’s “all the rage”
and gives you a decent running start. Later, I’ll work on switching the
supplied templates with boilerplate + bootstrap based ones.

This gets me started. Next up, I’ll actually create the project from
within RubyMine and just work on basic web functionality.

CheckByNet

/*
echeck.class, Copyright 2004 John Hogenmiller [email protected]
http://h.hsource.net/
This is an api to interface with ChecksByNet (http://www.checksbynet.com/), provided
by CrossCheck, Inc. This program is in no way (currently) endorsed by Crosscheck, Inc., nor is the author in any way affiliated with CrossCheck, Inc.
Distribution:
This program is released under the GNU Lesser General Public License.
(http://www.opensource.org/licenses/lgpl-license.php)
CONFIGURATION:
Go to the function post_echeck and change the $payto and $paytoid variables
to match what you have with ChecksByNet. Alternatively, you could pass these
in from the parent script. If ChecksByNet ever changes their
submission url, you’ll have to change the $url variable.
EXAMPLE:
Note that in a production environment, you wouldn’t want to pass $REQUEST in
to the post_echeck function as the end user would be able to change the amount,
payto, and paytoid at will. Ideally, you pull the cbn
variables out of
$REQUEST, do any regex checks you want to do on your side, and directly
set the amount from the server side.
post_echeck works by returning an array with the keys “approved” and “err”.
$array[‘approved’] // If set, echeck was approved.
$array[‘err’] // Human readable text of all errors.
$array[‘RSPxxxx’] // Server generated error as key, human readable error as value.
Useful for parent scripts that want to do more with the error.
$ec = new echeck;
$result = $ec->post_echeck($_REQUEST);
if ($result[approved])
{ print “Echeck approved
\n”; }
else
{ print “Declined:
\n”;
print $result[err];
}
VARIABLES:
Put all the variables into an array and pass that array to post_echeck.
Prefix all variables in the array with cbn

Example:
$checkno = 123;
$check[‘cbn_checknbr’] = $checkno;
$result = $ec->post_echeck($check);
Checksbynet requires the following variables FROM THE CUSTOMER:
Check Number Value: checknbr Needs to be greater than 99 / 6 Max
Customer’s First Name: writerfirst 15 Max (only for personal checks)
Customer’s Last Name: writerlast 29 Max
Customer’s Business Name: writername 50 Max (only for business checks)
Customer’s Address: writeraddr Street address required / 50 Max
City: writercity 30 Max
State: writerst 2 characters required
Zip: writerzip 5 digits required
Bank Name: bankname 50 Max
Bank City: bankcity 30 Max
Bank State: bankst 2 characters required
Bank Zip: bankzip 5 digits required or can be blank
MICR: micr 80 Max
Customer’s Driver’s License: idnbr Do not include dashes or spaces / 40 Max
Driver’s License State: idst 2 characters required
Customer’s Phone Number: phone 10 digits required / 14 max
Customer’s Email: email “@” and “.” required / 50 Max
They also require these variables FROM THE MERCHANT:
Check Dollar Amount Value: checkamt Needs to be greater than $1.00/ 10 Max characters
These variables are required by the MERCHANT, but can be set in the post_echeck
function, and do not need to be passed in from the parent program.:
Pay to: payto Who the check is being made out to. 50 Max
Pay to id: paytoid Merchant id number
*/
class echeck
{
function post_echeck($check)
{
$url = ‘https://cross.checksbynet.com/response.asp’;
if ($check[cbn_payto])
{ $payto = urlencode($check[cbn_payto]); }
else { $payto = urlencode(“Valued CrossCheck Merchant”); }
if ($check[cbn_paytoid])
{ $paytoid = urlencode($check[cbn_paytoid]); }
else { $paytoid = “12345”; }
// For initial testing, you may want to NULL out the paytoid
$paytoid = 0;
$params =
“payto=$payto”
. “&checknbr=”. urlencode($check[‘cbn_checknbr’])
. “&checkamt=”. urlencode($check[‘cbn_checkamt’])
. “&writeraddr=”. urlencode($check[‘cbn_writeraddr’])
. “&writercity=”. urlencode($check[‘cbn_writercity’])
. “&writerst=”. urlencode($check[‘cbn_writerst’])
. “&writerzip=”. urlencode($check[‘cbn_writerzip’])
. “&bankname=”. urlencode($check[‘cbn_bankname’])
. “&bankcity=”. urlencode($check[‘cbn_bankcity’])
. “&bankst=”. urlencode($check[‘cbn_bankst’])
. “&micr=”. urlencode($check[‘cbn_micr’])
. “&idnbr=”. urlencode($check[‘cbn_idnbr’])
. “&idst=”. urlencode($check[‘cbn_idst’])
. “&phone=”. urlencode($check[‘cbn_phone’])
. “&email=”. urlencode($check[‘cbn_email’]);
if ($check[‘cbn_bankzip’]) { $params .= “&bankzip=”. $check[‘cbn_bankzip’]; }
if ($check[‘cbn_writername’]) { $params .= “&writername=”. $check[‘cbn_writername’]; }
else { $params .= “&writerfirst=”. $check[‘cbn_writerfirst’] . “&writerlast=”. $check[‘cbn_writerlast’]; }
if ($paytoid) { $params .= “&paytoid=”. $paytoid; }
$ch = curl_init();
curl_setopt($ch, CURLOPT_POST,1);
curl_setopt($ch, CURLOPT_POSTFIELDS,$params);
curl_setopt($ch, CURLOPT_URL,$url);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 2);
// curl_setopt($ch, CURLOPT_USERAGENT, $defined_vars[‘HTTP_USER_AGENT’]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
$return=curl_exec($ch);
curl_close ($ch);
$goodresults = $this->quotesplit($return);
foreach ($goodresults as $key => $value)
{
$result[$value] = “ECHECK: Unknown error”; // All known return codes will replace this.
if ($value == ‘RSP0051’) { $result[err] .= “ECHECK: Configuration error: invalid payto/paytoid”;
$result[$value] .= “ECHECK: Configuration error: invalid payto/paytoid”; }
if ($value == ‘RSP1101’) { $result[err] .= “ECHECK: Bad/blank check number
\n”;
$result[$value] = “ECHECK: Bad/blank check number
\n”; }
if ($value == ‘RSP1102’) { $result[err] .= “ECHECK: Bad/blank check dollar amount
\n”;
$result[$value] = “ECHECK: Bad/blank check dollar amount
\n”; }
if ($value == ‘RSP1201’) { $result[err] .= “ECHECK: Bad/blank entry for customer name
\n”;
$result[$value] = “ECHECK: Bad/blank entry for customer name
\n”; }
if ($value == ‘RSP1202’) { $result[err] .= “ECHECK: Bad/blank address for customer $check[cbn_writeraddr]
\n”;
$result[$value] = “ECHECK: Bad/blank address for customer $check[cbn_writeraddr]
\n”; }
if ($value == ‘RSP1203’) { $result[err] .= “ECHECK: Bad/blank city for customer
\n”;
$result[$value] = “ECHECK: Bad/blank city for customer
\n”; }
if ($value == ‘RSP1204’) { $result[err] .= “ECHECK: Bad/blank state for customer
\n”;
$result[$value] = “ECHECK: Bad/blank state for customer
\n”; }
if ($value == ‘RSP1205’) { $result[err] .= “ECHECK: Bad/blank zip code for customer
\n”;
$result[$value] = “ECHECK: Bad/blank zip code for customer
\n”; }
if ($value == ‘RSP1301’) { $result[err] .= “ECHECK: Bad/blank bank name
\n”;
$result[$value] = “ECHECK: Bad/blank bank name
\n”; }
if ($value == ‘RSP1302’) { $result[err] .= “ECHECK: Bad/blank city for bank
\n”;
$result[$value] = “ECHECK: Bad/blank city for bank
\n”; }
if ($value == ‘RSP1303’) { $result[err] .= “ECHECK: Bad/blank state for bank
\n”;
$result[$value] = “ECHECK: Bad/blank state for bank
\n”; }
if ($value == ‘RSP1304’) { $result[err] .= “ECHECK: Bad/blank zip code for bank
\n”;
$result[$value] = “ECHECK: Bad/blank zip code for bank
\n”; }
if ($value == ‘RSP1311’) { $result[err] .= “ECHECK: Bad/blank account number
\n”;
$result[$value] = “ECHECK: Bad/blank account number
\n”; }
if ($value == ‘RSP1312’) { $result[err] .= “ECHECK: Bad/blank routing number
\n”;
$result[$value] = “ECHECK: Bad/blank routing number
\n”; }
if ($value == ‘RSP1313’) { $result[err] .= “ECHECK: Bad/blank micr number
\n”;
$result[$value] = “ECHECK: Bad/blank micr number
\n”; }
if ($value == ‘RSP1401’) { $result[err] .= “ECHECK: Bad/blank driver’s license number
\n”;
$result[$value] = “ECHECK: Bad/blank driver’s license number
\n”; }
if ($value == ‘RSP1402’) { $result[err] .= “ECHECK: Bad/blank state for driver’s license
\n”;
$result[$value] = “ECHECK: Bad/blank state for driver’s license
\n”; }
if ($value == ‘RSP1501’) { $result[err] .= “ECHECK: Bad/blank phone number for customer
\n”;
$result[$value] = “ECHECK: Bad/blank phone number for customer
\n”; }
if ($value == ‘RSP1502’) { $result[err] .= “ECHECK: Bad/blank email address for customer
\n”;
$result[$value] = “ECHECK: Bad/blank email address for customer
\n”; }
if ($value == ‘RSP0000’) { $result[approved] = 1; $result[err] = 0; }
if ($value == ‘RSP0001’) { $result[approved] = 0; $result[err] .= “ECHECK: Declined
\n”;
$result[$value] = “ECHECK: ECHECK: Declined
\n”; }
if ($value == ‘RSP0020’) { $result[err] .= “ECHECK: Check duplication error. We have approved this same check number from the same account in recent history.
\n”;
$result[$value] = “ECHECK: Check duplication error. We have approved this same check number from the same account in recent history.
\n”; }
if ($value == ‘RSP9999’) { $result[err] .= “ECHECK: Unable to process checks at this time
\n”;
$result[$value] = “ECHECK: Unable to process checks at this time
\n”; }
// Enable the below line for testing
if ($value == ‘RSP0010’) { $result[approved] = 1; $result[err] = 0;
$result[$value] = “ECHECK: Test completed.”;}
}
if (!$result[err] && !$result[approved]) { $result[err] .= “ECHECK: Undefined error, not approved”; }
return($result);
} #end function post_echeck
function quotesplit($s)
{
$r = Array();
$p = 0;
$l = strlen($s);
while ($p < $l) {
while (($p < $l) && (strpos(” \r\t\n”,$s[$p]) !== false)) $p++;
if ($s[$p] == ‘”‘) {
$p++;
$q = $p;
while (($p < $l) && ($s[$p] != ‘”‘)) {
if ($s[$p] == ‘\’) { $p+=2; continue; }
$p++;
}
$r[] = stripslashes(substr($s, $q, $p-$q));
$p++;
while (($p < $l) && (strpos(” \r\t\n”,$s[$p]) !== false)) $p++;
$p++;
} else if ($s[$p] == “‘”) {
$p++;
$q = $p;
while (($p < $l) && ($s[$p] != “‘”)) {
if ($s[$p] == ‘\’) { $p+=2; continue; }
$p++;
}
$r[] = stripslashes(substr($s, $q, $p-$q));
$p++;
while (($p < $l) && (strpos(” \r\t\n”,$s[$p]) !== false)) $p++;
$p++;
} else {
$q = $p;
while (($p < $l) && (strpos(“,;”,$s[$p]) === false)) {
$p++;
}
$r[] = stripslashes(trim(substr($s, $q, $p-$q)));
while (($p < $l) && (strpos(” \r\t\n”,$s[$p]) !== false)) $p++;
$p++;
}
}
return $r;
} # end function quotesplit
} # end class echeck
?>