Back To School

Normally I avoid writing about closed source software in this blog simply because I do not want to advocate commercial products without getting payed. I also do not like express negative opinions on specific products, when I do it’s mostly to illustrate a more general phenomenon like e.g. NoSQL (this morning I read a great post on the word NoSQL).
I do not bear any grudge against closed source, I respect both copyright and patent, I would love to own a successful closed source product myself but that’s another story.
In this blog I focus on my own professional life and  this week I attended a four day MS SharePoint introduction course, and that is what this post is about.

SharePoint introduction

Four days for a key user introductory course may sound overkill. But on the contrary, if anything it was a day to little, MS SharePoint is loaded with functionality, it is a full fledged web development platform for a lot of types of web apps. I thought it was some kind of document system but it is a hell lot more than that. The Learning Tree course was given in a brisk pace. The teacher never gave me a chance to do what I do the most attending IT classes, sleep. I do not like IT courses, I normally find them slow, boring with dull exercises which I never complete successfully. This time however I stayed alert most of the time, however I still failed most of the exercises. I really tried the patience of the teacher, he gave me unlimited time ‘Lars stay after we finish today, we can work it through together’. With a ‘little’ bit of help, in end I got all exercises stitched together. I am by no means a key user of MS Sharepoint, no one is after a four day course, but it gives you an understanding of what the product can do, and sufficient knowledge to start work in SharePoint. You need at least an additional three months of hard work to qualify as a key user.

SharePoint reminds me a lot of mindtouch an open source product I installed at the Company during my vacation 2008. At that time those I showed it for didn’t get the idea, they could not see a need for it and ‘we will soon install Quickr’. With all respect Quickr (at least at that time) was a different piece of software. Anyway I worked with mindtouch enough to understand what this kind of collaboration software is, (and I have opinions how it should  be used). A few first impressions of SharePoint:  

The good

The integration with office and mail (an absolute must for collaboration software) is top class. As a user and developer you work seamlessly with MS Office and MS outlook.

The GUI is consistent and intuitive and lends heavily from MS Office, which makes it familiar right from start.

Lists (a cornerstone in SharePoint) are great and easy to work with.

The implementation of workflows looks decent. Workflows are very complex a decent implementation is good. (I managed the workflows exercises without help). But to judge workflows you really need to work with them and see how they interact with humans and other software in your environment.

The bad

The GUI was cluttered with a zillion of options, I had severe problems finding the options I wanted, most of my failures was due to not finding the configuring option I was looking for.

Web development looked mezzy to me. Any app beyond the very simple was very hard to realize. Expressing apps by configuring options (which you do in SharePoint) is tedious and cumbersome. This is because creating a complex app is complex. There are good reasons for programing languages, good ones can express logic and actions succinct and unambiguous with a limited set of operations.
I do not think no-programming applications development is the right way to go. I rather see a revival of my beloved Mims 4GL, where you tell the computer what you want in coherent and super condensed code, that together with a WYSIWYG screen editor.

The fear

I see an upcoming clash between titans in the collaborative software arena at the Company, SAP and Microsoft. SAP are working their butts off to launch collaborative tools. A product company having their business in the SAP ERP system is likely to favor SAP for collaboration, since most information they use is already in there. On the other hand SharePoint may fill ‘the holes’ and act like glue between ERP and other software. On the other hand again SAP is doing it’s best to fill ‘the holes’ and act like glue between themselves and other software. Anyway it turns out, these alternatives are a big vendor lock ins, these collaboration packages comes bundled or connected with all the products the Vendor can possibly throw in. Once in exit will be excruciatingly painful.

The epilogue

Will I recommend SharePoint to a friend? I do not know yet. It is a well designed quality product that’s for sure. During the course we did not encounter unlogic behavior or bugs, it’s darn good having twenty newbies hammering wildly for four days not showing any erratic behavior.

What I happily can recommend is both the introductory course and the teacher.


Dynamic PHP code 2

In the previous post I showed how ITL can be used to generate HTML tables from MySQL selects. Now I have cleaned the prototype and added the ability to customize the  html table rows. The intention with generating HTML tables in ITL is not to give the ability to customize all aspects of an HTML table, but to easily create decent tables. This is not easy to do since it is complex to format HTML.  The present ITL HTML generating capabilities are primarily for adding HTML tables to mails, still it is complex to format HTML so it looks nice. Therefore I decided to do the formatting with PHP code, this is not the simplest way to add HTML formatting, but it gives the flexibility I find necessary. I added the ability to modify the row tag <tr> by <htmlrow>php code </htmlrow>, the SQL result table is exposed to this code row by row as the cells are exposed to <htmlcell>php code</htmlcell>.  
I expose the entire SQL table to the htmlrow function and an index pointing out the current row in the table. In the htmlcell function I expose the entire table row with an index pointing at the current cell. This gives freedom to format depending on values of other rows and cells.

If we look at the new version of the job ‘noCellColors’:
You see I added an <hmlrow> tag to get a (row-) striped table, and a maroon row if the first cell’s value is ‘en’, like this:

The second job (from the post) ‘cellColor’ now looks like this:

Here I added some ‘cell logic’ formatting background colors depending on the value of the second cell in each row, giving this html table:  
Maybe not so pretty, but it illustrates the ability to format depending on values.

I use the eval function to import the PHP code from the job XML script. I think it’s a bit clumsy and suspect there is better ways to do it, but I could not find a better way. What I found was this  „If eval() is the answer, you're almost certainly asking the wrong question.“ Rasmus Lerdorf.

Maybe PHP is the wrong language, if eval() is the answer. 
I would have better use of  practical advice than a quibbling quote, but I didn’t find any so I use eval(). Anyway for the moment I’m happy with the HTML formatting so far, but I suspect this is not the final code.
I end this post with the sql converter code creating these amazing tables.
* SQL result converter - dynamically included in function {@link execSql()}
* This code converts a mysqli select result into a HTML table.
* For documentation see {@link sqlconverter_default.php}
* <sqlconverter name='sqlconverter_HTML03.php' target='table' totline='yes'>
*  <htmlrow><![CDATA[
*    var_dump($cnt);
*    if($rno % 2 == 0) $tag = '<tr class="ett">';
*    else $tag = '<tr style="background: maroon; color: white;">';
*    if ($tbl[$rno]['0'] == 'en') $tag = '<tr style="background: maroon; color: white;">';
*  ]]></htmlrow>
*  <htmlcell><![CDATA[
*    if ($cno == 1){
*      if ($row[$cno] < 2) $tag = "<td style="background: red; color: black;border:1px solid #D5D5D5; padding:15px;">";
*      else if ($row[$cno] < 10) $tag = '<td style="background: blue; color: white;">';
*      else $tag = "<td class='green'>";   
*    }
*  ]]></htmlcell>
* </sqlconverter>
* The parms:
* 1 totline=yes|no determines if a total line is inserted at the end  
* 2 emptytable=yes|no determines if the an empty table is written to disk or not
* 3 width=width of table  
* htmlrow($tbl,$rno,$meta)
* $tbl array, $rno=row number, $meta array
* return $trtag
* htmlcell($row,$cno,$meta)
* $row array, $cno=position in row (0=1st cell), $meta array
* return $tdtag
* @author Lasse Johansson <lars.a.johansson@se.atlascopco.com>
* @version  1.0.0
* @package adac
* @subpackage sqlconverter
* @param string $sqltarget filename where we should save the file.
* @param object $result mysqli SELECT result set
$mysqltarget = $sqltarget;
$sqltarget = $temparr = 'html0';
/** Include the default converter to do a basic conversion of the result table */
$sqltarget = $mysqltarget;
if(is_numeric(substr($sqltarget, -1,1))) {
$sqltarget = "$sqltarget";
} else {
for ($x=0; 1==1; $x++){
 if (!file_exists("$sqltarget$x")){
   $sqltarget = "$sqltarget$x";
$sqlarray = unserialize(file_get_contents("$temparr"));
$arrMeta = unserialize(file_get_contents("$temparr".'meta_.TXTArray'));

$filePfx = '.ARRAY';
$metafile = $sqltarget.'meta_';
if(is_numeric(substr($sqltarget, -1,1))) {
$metafile = "$metafile$filePfx";
$sqltarget = "$sqltarget";
} else {
for ($x=0; 1==1; $x++){
if (!file_exists("$metafile$x$filePfx")){
$metafile = "$metafile$x$filePfx";
$sqltarget = "$sqltarget$x";
$sqllog->logit('Enter',"sqlconverter writing output to $metafile and $sqltarget");

if (array_key_exists('table',$xmlconverter)) {
   $xmltable = $xmlconverter['table'][0];
   if (array_key_exists('width',$xmltable))
$xmltablewidth = is_string($xmltable['width']) ? $xmltable['width'] : $xmltable['width'][0]['value'];

$rf =  
'$rowfunc = function($tbl,$rno,$meta) {$tag = "<tr>";'.$xmlconverter['htmlrow'][0]['value'].'return "$tag";};';
$cf =  
'$cellfunc = function($row,$cno,$meta) {$tag = "<td>";'.$xmlconverter['htmlcell'][0]['value'].'return "$tag{$row[$cno]}</td>";};';
if (isset($xmltablewidth)) $Table.= "<table id='mysql' width=$xmltablewidth>";
else $Table.= "<table id='mysql' style='table-layout: fixed; width: 100%;'>";
//Header Row with Field Names
$NumFields = $result->field_count;
$Table.= "<tr>";
foreach($arrMeta as $field) {
   if ( $field->type == 253) $Table.= "<th align='right'>".$field->name."</th>";
   else $Table.= "<th>" . $field->name . "</th>";
$Table.= "</tr>";

foreach($sqlarray as $rno => $Row){
   $Table.= $rowfunc($sqlarray,$rno,$arrMeta);
   foreach($Row as $key => $value){
$Table.= $cellfunc($Row,$key,$arrMeta);
   $Table.= "</tr>";
if ($xmlconverter['totline'] != 'no'){
 $Table.= "<tr class='tot'><td colspan='$NumFields'>Query returned " . $result->num_rows . " rows</td></tr>";
$Table.= "</table>";
if (!($xmlconverter['zerorows'] == 'no' and $RowCt == 0)){
$sqllog->logit('Note',"Exit sqlconverter");


Dynamic PHP code by closures

The ITL language is primarily intended for ETL workflows in the Data Warehouse, but ITL is not limit to ETL. If you load data into the Data Warehouse you probably want to use that data. Normally we use standard interfaces like ODBC, JDBC from GUI applications of users choice like MS Excel or Qlikview. But we also ship data to other applications and send mails and then ITL comes handy, since it is actually more of a job control system than just a language or an ETL processor. In this post I show how we generate PHP code on the fly to format HTML tables.

In ITL workflows are organised in jobs, jobs of type ‘sql’ execute sql queries, if the query produce a result table an sql converter post process the result e.g:

This job calls the HTML03.php sqlconverter to post process the sql query, creating an html table that looks like this:

I think this is a fairly nice table, the intention is not to create a dazzling web app , just to jazz up emails a bit. But this vanilla table did not satisfy the developers for very long. ‘How can I color the cells?’ Since I do not know HTML/CSS well I didn’t have an answer. After some reading I created ‘color classes’ in a CSS, that was easy the problem was how to implement logic in the job above to allow for flexible coloring of cells. I decided to utilize plain PHP code:  


This is what I came up with, in <cellfunction> you can override the default <td> tag for any tuple/cell in the HTML table. This is just plain PHP code, and applied on the table above it looks like this:

If this is better than the first default table is a matter of taste, this is just a very simple example since you have PHP to back you up, you are free to format the cells to your taste. Inline CSS code is probably a wiser choice than my predefined classes, but I didn’t know of inline CSS when I started coloring the cells. (My knowledge of CSS/HTML is still 'vauge'.)
Now I just had to grab the code from the job and apply it to all cells, I decided to create an anonymous function like this:


As you see I complement the code snippet from the job and assign this to the $f variable at the top, then I eval the string which results in the $cellfunc variable points to a closure which is called at the bottom of this code. To my surprise I could not create the closure directly but I had to eval the expression which I feel is a clumsy way to generate the closure.
Now when we can create strawberry tables, toppings is requested, ‘I want to format rows depending on...’ I suspect when I can offer toppings someone will ask for mixins. But to achieve mixins I would probably need to hijack the PHPcompiler in some way to generate the mixin PHP code. Here you can read the next post about dynamic PHP code.


PHP 5.6 towards Data Warehouse production

Today I compiled PHP 5.6.0 for the Data Warehouse. So far all looks good. The SAP interfaces
compiled nicely although I got a deprecation warning I have not seen before.:
/home/tooljn/SAPRFC/saprfc-1.4.1_54/saprfc.c:2275:5: warning: 'zend_get_parameters_ex' is deprecated (declared at /home/tooljn/PHP5.6.0/usr/local/include/php/Zend/zend_API.h:241)

I have run PHP 5.6 since alpha1 with no problems as far as I can remember, but now it’s time to go production. First we have to test,test and test. This will take a week or two then we hopefully can push PHP 5.6 into production.


Wrong entrance

Sometimes I think with the wrong body part or put into Swedish - ' Ibland tänker jag med arschlet'.
I got this question, how do I transfer a MYSQL macro variable into an ITL @tag? Like this:


I want to use the value in @THISYEAR to create a column in a table like this:


After some (2 hours) research I realized it is not easy to grab the MySQL macro variable @THISYEAR and transfer it into an ITL @TAG. I started to look for alternative solutions. When you start to look on a tough problem from another angle, you often find a simple solution that seems so obvious when it's found, here is one simple ETL solution:



It is as my former colleague Ulf Davidsson used to say ‘It’s about the entrance, if you enter a problem from the wrong direction, you will create the wrong solution’.
While I was searching for the wrong solution, the problem was solved in a stored procedure. All solutions that work are good solutions, well almost anyway.