Row to Column in MySQL

Say you have two tables like these:

CREATE TABLE user (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
) ENGINE = 'InnoDB';
CREATE TABLE user_attributes(
user_id INT(11) UNSIGNED NOT NULL,
attribute_name VARCHAR(255) NOT NULL,
attribute_value VARCHAR(255) NOT NULL,
UNIQUE KEY (user_id, attribute_name)
) ENGINE = 'InnoDB';

And you have these values:

idname
1Kesavan
2Madhavan
user_idattribute_nameattribute_value
1GENDERM
1PREMIUM_USERN
1SUBSCRIBED_TO_NEWSLETTERY
2GENDERM
2PREMIUM_USERY

To get a result like this:

idnameGENDERPREMIUM_USERSUBSCRIBED_TO_NEWSLETTER
1KesavanMNY
2MadhavanMYNULL

Use this query

SELECT 
u.id,
ANY_VALUE(u.name) as name,
GROUP_CONCAT(IF (ua.attribute_name = 'GENDER', ua.attribute_value, NULL)) AS 'GENDER',
GROUP_CONCAT(IF(ua.attribute_name = 'PREMIUM_USER', ua.attribute_value, NULL)) AS 'PREMIUM_USER',
GROUP_CONCAT(IF(ua.attribute_name = 'SUBSCRIBED_TO_NEWSLETTER', ua.attribute_value, NULL)) AS 'SUBSCRIBED_TO_NEWSLETTER'
FROM user u
JOIN user_attributes ua ON (u.id = ua.user_id)
GROUP BY u.id;

Dbfiddle link: https://www.db-fiddle.com/f/adNt6wy9fMoVudrbeoPtrU/3

PHP openssl_encrypt tip

Recently I had to encrypt some data in PHP and send it to a Java App, the Java app was unable to decrypt the message.

I experimented with (data) padding, changing ciphers and changing the options for openssl_encrypt, but, none of those worked.

It was a requirement at the Java end for the Key to be hashed (sha256) and then used for encryption. The thing that worked for me was converting the hexed key to binary and using that.

<?php
$iv = 'aBCaDU9phtMwtNeV';
$key = 'B47C5126B42C9E192FAEAA5AA1892136';
$string ='testtesttesttest';

openssl_encrypt($string, "AES-256-CBC", hex2bin(hash('sha256', $key)), 0, $iv);
=> "E5vjBpIdWXo2NNuXkPzsEDVX6YVR3oFvHDwX+LohRsg="

# Bash
# >>> bin2hex('aBCaDU9phtMwtNeV')
# => "614243614455397068744d77744e6556"
# hash('sha256', 'B47C5126B42C9E192FAEAA5AA1892136')
# => "58754fcb239dfd17dfba62da3a57556980c69158d23dae6a1c24a174afeb676c"
printf %s "testtesttesttest" | openssl enc -e -aes-256-cbc -base64 -K 58754fcb239dfd17dfba62da3a57556980c69158d23dae6a1c24a174afeb676c -iv 614243614455397068744d77744e6556
E5vjBpIdWXo2NNuXkPzsEDVX6YVR3oFvHDwX+LohRsg=

Using Generators to flatten a JSON doc in PHP

To flatten a JSON like this:

{
   "addresses" : [
     {
       "line1" : "123 Livingstone Rd",
       "id" : 23444555,
       "addressSummaryType": "Building",
       "subAddresses": {
         "Level1": [
           {
             "line1" : "Level 2",
             "id" : 266887373,
             "Level2": [
               {
                 "line1": "Suite 201",
                 "id" : 276888890
               }
             ]
           }
         ]
       }
     },
     {
       "addressSummaryType": "Building",
       "line1" : "124 Livingstone Rd",
       "id" : 2562672
     }
   ]
 }

to:

array(2) {
  ["id"]=>
  int(23444555)
  ["address"]=>
  string(18) "123 Livingstone Rd"
}
array(2) {
  ["id"]=>
  int(266887373)
  ["address"]=>
  string(26) "Level 2 123 Livingstone Rd"
}
array(2) {
  ["id"]=>
  int(276888890)
  ["address"]=>
  string(36) "Suite 201 Level 2 123 Livingstone Rd"
}
array(2) {
  ["id"]=>
  int(2562672)
  ["address"]=>
  string(18) "124 Livingstone Rd"
}

I used the following code:

foreach(flattenAddress($addressObj->addresses) as $flattenedAddress) {
      var_dump($flattenedAddress);
 }

 function flattenAddress($addresses, $baseAddress = '', $level = 0)
 {
     foreach ($addresses as $address) {
         $nextLevel = "Level" . ($level + 1);
         if (property_exists($address, "addressSummaryType")) { //root
             $baseAddress = $address->line1;
             yield ["id" => $address->id, "address" => $baseAddress];
             if (property_exists($address, "subAddresses")) {
                 yield from flattenAddress($address->subAddresses->{$nextLevel}, $baseAddress, ($level + 1));
                 continue; // recurse don't reset level
             }
         } else { //subaddresses
             $subAddress =  $address->line1 . " " . $baseAddress;
             yield ["id" => $address->id, "address" => $subAddress];
             if (property_exists($address, $nextLevel)) {
                 yield from flattenAddress($address->{$nextLevel}, $subAddress, ($level + 1));
                 continue; // recurse don't reset level
             }
         }
         $level = 0; //reset for next root
     }
 }


Jenkins parallel pipeline

I was very excited to know about the ‘parallel’ feature in Jenkins Pipeline, but, there are many gotchas while makingĀ  use of the pipeline feature (many of which are documented here: Jenkins Pipeline Example). After trying and reading a few different solutions, following worked for me (notice in screenshot that the browser jobs run in parallel !)

pipeline {
  agent any
  stages {
    stage('Build') {
      steps {
        echo 'hello'
      }
    }
    stage('Test') {
      steps {
        script {
            def jobs = [:]
            def browsers = ["Chrome", "Firefox"]
            for (int i = 0; i < browsers.size(); i++) {
                def browser = browsers.get(i)
                def jobName = "printing $browser"
                jobs[jobName] = doJob(browser)
            }
            parallel jobs
        }
      }
    }
    stage('Deploy') {
      steps {
        echo 'deploying'
      }
    }
  }
}

def doJob(browser) {
    return {
        node {
            echo "testing in $browser"
        }
    }
}

casperjs output to html

Documenting what I had to do.

Used XSLT from here:
nosetest xslt

Problems and Fixes:

  1. Firefox was inserting “transformiix” as the root element, this caused the DOCTYPE to be spit out. I fixed by adding:
    doctype-public="-//W3C//DTD HTML 4.0//EN"/
    to 
    <xsl:output

    And removed the doctype declartion in the above xslt

  2. My version of casperjs was setting xml namespace to

    xmlns="http://www.w3.org/1999/xhtml

    After unsuccessfully trying to match the namespace in the xslt, I gave up and removed the namespace from the xunit xml, by

    sed -i 's@ xmlns="http://www.w3.org/1999/xhtml" @ @' "output.xml"
  3. Inserted the style into xml so can be rendered in the browser

    sed -i 's@ encoding="UTF-8"?>@ encoding="UTF-8"?><?xml-stylesheet type="text/xsl" href="nosetests.xslt" ?>@' "output.xml"
  4. Removed timestamp info from the xslt as it was in UTC timezone and could be very confusing when looking at the results (attempts to convert to my TZ in client were unsuccessful)

Checking for the existence of domains from commandline in Bash

while read line; do wget --quiet --spider --timeout=10 $line; if [ $? -ne 0 ]; then echo $line; fi; done < "/tmp/domains.txt" 
 
/tmp/domains.txt would hold the list of domain names separated by newline.
Note this is just a quick hack and not entirely reliable.

PHP as a replacement for sed/awk

I had gotten sick of not being able to do what I wanted to do with sed/awk – partly because I am not too familiar with those – and, was investigating replacements for those. Many people seem to be using perl and since I wasn’t too familiar with Perl as well, I started thinking about using PHP – which I do know. And, that served my needs brilliantly.

This is a simplistic version of my problem & a solution:
I can do the following to get the values for specific params (cid and oid in this case) for requests made, from my access logs:

k7@local:echo "http://example.com?cid=123&oid=435" | php -R 'preg_match("#.*cid=([^&]+)&oid=([^&]+)#",$argn, $matches);if(!empty($matches)) {echo $matches[1], " ", $matches[2], "n";}'
123 435

Regular Expression to check for comma separated list of Integers

//get rid of all kinds of spaces
$IDs = preg_replace('/s+/', '', $inputIDs);
if(!preg_match(''/^\d+$|^(\d+,)+\d+$/'', $IDs)) {
echo "Invalid Input";
}

You could use the validated IDs above in a SQL like the this:

SELECT * FROM t1 WHERE id IN($IDs)

Disclaimer: I am not going to be responsible for any kind of SQL injection resulting from using the snippet above.
updated 20-Mar-2013