Inserting ID with case

I use sqlite3 for a lot of small projects. It has many features that are very impressive for such a small DB engine.

One thing I like to do is set the value of an index field using MAX(id)+1 from the table itself but that does not work on a blank table. To make this work with a blank table a CASE statement can be used.

CREATE TABLE microServices (
   id INT NOT NULL, 
   packageName CHAR(45) NULL, 
   serviceName CHAR(45) NULL, 
   deployTargets CHAR(45) NULL, 
PRIMARY KEY (id));

INSERT INTO microServices (
   id, 
   packageName, 
   serviceName) 
   SELECT CASE 
      WHEN MAX(id) > 0 
         THEN MAX(id)+1 
      ELSE 1 
   END, 
   'trucks', 
   'trucksd' 
FROM microServices;

Are you mocking me up?

There are a lot of mockup / script test sites, one that I like and try to make use of is JSFiddle. It allows you to test snippets of code. You cat enter HTML, CSS & JavaScript. It gives you the option to include and dozens of JavaScript libraries including AngularJS, jQuery and many others.

See the screenshot below the code snippets.


Paset this into the HTML box

<canvas id="myCanvas" width="578" height="200"></canvas>

Paste this into the JavaScript box, select jQuery 3.3.1 then click run

$(function() {
  var c = document.getElementById("myCanvas");
  var ctx = c.getContext("2d");

  function DrawBox(x, y, w, h) {
    ctx.beginPath();
    ctx.lineWidth = "2";
    ctx.strokeStyle = "black";
    ctx.moveTo(x, y);
    ctx.lineTo(x + w, y);
    ctx.lineTo(x + w, y + h);
    ctx.lineTo(x, y + h);
    ctx.lineTo(x, y);
    ctx.stroke();
  }

  function DrawLine(x1, y1, x2, y2) {
    ctx.beginPath();
    ctx.lineWidth = "2";
    ctx.strokeStyle = "red";
    ctx.moveTo(x1, y1);
    ctx.lineTo(x2, y2);
    ctx.stroke();
  }

  DrawBox(10, 10, 40, 20);
  DrawBox(70, 30, 40, 20);
  DrawLine(50, 20, 70, 40);
  DrawBox(70, 70, 40, 20);
  DrawLine(30, 30, 90, 70);

});

3.141592653589793238462643383279

Happy Pi day everyone. March 14th is Pi day because 3.14.

I created a BASH script to test the calculation in this video. It calculates Pi without pre-known values but by adding and subtracting fractions of one over odd numbers. 1/1 - 1/3 + 1/5 - 1/7 + 1/9 - 1/11 + 1/13 ... It gets more accurate the longer you let it run but only for a short time as fractional math in BASH is limited.

#!/bin/bash

D=1
P=0
i=1
while true; do
 O="-"
 (( i++ % 2 )) && O="+"
 P=$(echo "scale=66; ${P} ${O} 1/${D}" | bc)
 printf "%0.20f               \r" $(echo "scale=21; ${P} * 4" | bc)
 ((D+=2))
done

Tunneling UDP

We use Graylog at work, a very nice tool that collects logs from all our systems and helps us analyze them. We ran into a problem with a special cluster of servers that are isolated and could not get to our Graylog server. We only needed to connect them to Graylog for a short time so this is not a long term solution.

These systems have never been able to connect to Graylog so they were configured to send all logs to 127.0.0.1, and that is great because we can setup a listening tunnel on localhost to forward the connection and we can do all this without making changes to the target systems.

The Graylog server has SSH access to the target systems so we use that to create a reverse tunnel. SSH works over TCP but Graylog uses UDP so we use NC (netcat) to convert the UDP to TCP for tunneling and then convert it back again on the server side.

The first command forks a local listener to send TDP packets to our Graylog on UDP.
The second command opens a reverse tunnel and starts listening on UDP to send packets back via the tunnel over TCP.

nc -l 127.0.0.1 12203 | nc -u 127.0.0.1 12201 &
ssh target2 -R 12201:localhost:12203 "nc -kluw 0 127.0.0.1 12201|nc 127.0.0.1 12201"

Some important points.

• Each connection needs its own port, Graylog is using 12201, so target1 would listen on 12202 and target2 is using 12203
• Netcat wants to disconnect when the logger on target finishes a message to that nc uses 'k' to keep the connection alive and is uses 'w 0' to have no wait time out.

Update

After a day or so running netcat likes to die so this little change will automatically relaunch nc instantly by running this on the target side.

while nc -kluw 0 127.0.0.1 12201; do true; done | while cat - | nc 127.0.0.1 12201; do true; done

Recursion

My son is learning C++ in school and was given an assignment to create a program that would convert Roman Numerals to decimal integers. Most people new to C++ would do this using a while loop. Up on completion of his version I will show him an alternate method that does not use a while loop but rather is a good example of recursion.

This example makes it much harder to debug as the calculation happens in the recursion call, and I have placed that in the return of the same function, but this is just for fun.

#include <iostream>
#include <string>

using namespace std;

int Decode(const char* CodeString, int Position, int PreviousValue) {
    // When we have reached the end of the string
    if(CodeString[Position] == '\0') return(0);

    int NextValue = 0;
    switch (CodeString[Position]) {
        case 'M': NextValue = 1000;  break;
        case 'D': NextValue = 500;  break;
        case 'C': NextValue = 100;  break;
        case 'L': NextValue = 50;   break;
        case 'X': NextValue = 10;   break;
        case 'V': NextValue = 5;    break;
        case 'I': NextValue = 1;    break;
    }
    if(PreviousValue < NextValue) NextValue -= PreviousValue * 2;
    return(NextValue + Decode(CodeString, Position+1, NextValue));
}

int main()
{
    cout << "Please enter a value as valid formated Roman numerals ";
    string UserInput = "";
    cin >> UserInput;
    int Answer = Decode(UserInput.c_str(), 0, 0);
    cout << "The answer is " << Answer << endl;
    return 0;
}

Parsing a moving target.

A problem was presented to me where the raw data was expected to have columns added and moved around at some point but a report was needed that could deal with this. I chose to use Perl Named Capture Containers to solve this problem.

#!/usr/bin/perl -w

# This is an example of using named capture containers to parse data from lines 
# when the columns of data could move around, new columns are added and even if 
# the column is removed.

# Matching a pattern with multiple parts cannot deal with columns that move or are missing.
# Each part must be matched on it's own line.
# Every time a match is made it is put into the hash for use later.

# sample3.data as input
#######################
# 01:00:00 httpd=on sshd=on crond=on ntpd=on winbind=off cups=off
# 01:01:00 [567] httpd=on sshd=on crond=on ntpd=on winbind=off cups=off
# 01:02:00 [567] crond=on ntpd=on winbind=off httpd=off cups=off sshd=on
# 01:03:00 [PID:567] ntpd=on winbind=off cups=off sshd=on named=on httpd=on crond=on 
# 01:04:00 [PID:567] named=on httpd=on crond=on

# results from output
#######################
# Time  httpd ntpd sshd 
# 01:00:00  on  on  on 
# 01:01:00  on  on  on 
# 01:02:00  off  on 
# 01:03:00  on  on  on 
# 01:04:00  on 


my %DataSet; 
# The data set is dynamically gathered so to change
# the report just add or remove column names here.
my @ReportFields = ( "httpd", "ntpd", "sshd" );

sub Pack {
 my $Time = shift;
 my $FieldName = shift;
 my $DataValue = shift;
 $DataSet{$Time}->{$FieldName} = $DataValue;
}

while (<>) {
 $Line = $_;
 $Line =~ s/\n//;
 $Line =~ m/^(?<time>\d\d:\d\d:\d\d).*/;
 my $NewTime = $+{time};

 foreach my $R ( sort @ReportFields) {
  if ($Line =~ m/.* \Q$R\E=(?<value>\w*) .*/) { Pack($NewTime, $R, $+{value}) }; 
 }
}

printf "Time\t\t";
foreach my $F ( sort @ReportFields ) {
 printf $F."\t";
}
printf "\n";

foreach my $Time (sort keys %DataSet) {
 printf $Time."\t";
 foreach my $F ( sort @ReportFields ) {
  printf " ".$DataSet{$Time}->{$F}."\t" if (defined $DataSet{$Time}->{$F});
 }
 printf "\n";
}

Stop eating my pipe!

We love to use while loops in our scripts and they are a great way to read a file one line at a time to get a job done.

For example:

cat myFile.txt | while read LINE; do
   echo $LINE
   sleep 1
done

Now comes along SSH and lets say that your file contains a list hostnames you want to get uptime from:

cat myHostsFile.txt | while read LINE; do
   echo -n "Host = $LINE "
   ssh $LINE "updtime"
done

How disappointed you are when your loops stops after the first host. This is because every child process inherits it's first three file descriptors from it's parent, so SSH takes everything from STDIN for itself.

Sometimes you may want that but this time you don't. What can you do?

The simple solution here is to disassociate SSH from STDIN, and you do this using a simple re-director.

cat myHostsFile.txt | while read LINE; do
   echo -n "Host = $LINE "
   ssh 0>/dev/zero $LINE "updtime"
done

Something to keep in mind is that STDIN supplies a data stream, it does not take it. So for this reason we attache to /dev/zero not /dev/null.