martedì 19 maggio 2015

Tutorial: Canvas in HTML5




Let's play a bit with the graphic & Canvas...
You can find the code here:
http://jsbin.com/umaDEvu/5/edit?html,output

 <!DOCTYPE HTML>  
 <html>  
   <head>  
     <style>  
       body {  
         margin: 0px;  
         padding: 0px;  
       }  
       #myCanvas {  
         border: 1px solid #9C9898;  
       }  
     </style>  
     <script>     
       var clicked = 0;  
       var loopMouth = 0;  
       var lastMouseX = 300;  
       var lastMouseY = 300;  
       var colorSetIndex = 2;  
       var colorSet = [["limegreen", "forestgreen", "darkgreen", "olive"],   
               ["mediumblue", "blue", "darkblue", "indigo"],   
               ["darkorange", "peru", "sienna", "darkgoldenrod"]];  
      function setColorSetIndex(colorIndex){  
       colorSetIndex = colorIndex;  
      }  
       function getMousePos(canvas, evt) {  
         // get canvas position  
         var obj = canvas;  
         var top = 0;  
         var left = 0;  
         while (obj && obj.tagName != 'BODY') {  
           top += obj.offsetTop;  
           left += obj.offsetLeft;  
           obj = obj.offsetParent;  
         }  
         lastMouseX = evt.clientX - left + window.pageXOffset;  
         lastMouseY = evt.clientY - top + window.pageYOffset;  
       }  
      function drawFace(context) {  
         //left ear  
         context.beginPath();  
         var grd = context.createRadialGradient(125, 130, 10, 175, 130, 100);  
         grd.addColorStop(0.01, colorSet[colorSetIndex][0]);  
         grd.addColorStop(0.5, colorSet[colorSetIndex][1]);  
         grd.addColorStop(0.7, colorSet[colorSetIndex][2]);  
         grd.addColorStop(0.95, colorSet[colorSetIndex][3]);  
         context.arc(175, 150, 75, 0, 2*Math.PI, false);  
         context.fillStyle = grd;  
         context.fill();  
         // right ear  
         context.beginPath();  
         var grd = context.createRadialGradient(450, 95, 10, 425, 130, 100);  
         grd.addColorStop(0.01, colorSet[colorSetIndex][0]);  
         grd.addColorStop(0.5, colorSet[colorSetIndex][1]);  
         grd.addColorStop(0.7, colorSet[colorSetIndex][2]);  
         grd.addColorStop(0.95, colorSet[colorSetIndex][3]);  
         context.arc(425, 125, 75, 0, 2*Math.PI, false);  
         context.fillStyle = grd;  
         context.fill();  
         //face  
         context.beginPath();  
         var grd = context.createRadialGradient(300, 140, 50, 300, 170, 175);  
         grd.addColorStop(0.01, colorSet[colorSetIndex][0]);  
         grd.addColorStop(0.5, colorSet[colorSetIndex][1]);  
         grd.addColorStop(0.7, colorSet[colorSetIndex][2]);  
         grd.addColorStop(0.95, colorSet[colorSetIndex][3]);  
         context.arc(300, 200, 150, 0, 2*Math.PI, false);  
         context.fillStyle = grd;  
         context.fill();  
      }  
      function drawEyes(context, mx, my) {  
         var cx = [300, 220, 200, 250, 380, 350, 400];  
         var cy = [100, 130, 170, 160, 130, 150, 185];  
         var ray = [ 30, 20, 10, 10, 20, 10, 10];  
         for (var i = 0; i < cx.length; i++) {  
          //compute the multiplier for the mouse position  
          var mmx = ((mx-300)/300*ray[i]);  
          var mmy = ((my-200)/200*ray[i]);  
          context.beginPath();  
          var grd = context.createRadialGradient(cx[i]+mmx, cy[i]+mmy, 1, cx[i]+mmx, cy[i]+mmy, ray[i]*3);  
          grd.addColorStop(0.01, "black");  
          grd.addColorStop(0.1, "white");  
          grd.addColorStop(0.99, "gray");  
          context.arc(cx[i], cy[i], ray[i], 0, 2*Math.PI, false);  
          context.fillStyle = grd;  
          context.fill();    
         }  
      }  
      function drawMouth(context) {  
         var grd_m = context.createRadialGradient(300, 300, 10, 300, 300, 100);  
         grd_m.addColorStop(0.1, "red");  
         grd_m.addColorStop(0.6, "black");  
       //for closing the mouth  
       var diffLeft;  
       var diffRight;   
       //loopMouth goes from 0-100  
       // from 0-50 it close the mouth  
       //from 50-100 it reopen the mouth  
       if(loopMouth < 50)  
       {  
        tempLoopMouth = loopMouth;  
        diffLeft=((380-230)/50)*(tempLoopMouth+1);  
        diffRight=((330-250)/50)*(tempLoopMouth+1);  
       }  
       else   
       {  
        tempLoopMouth = (100-loopMouth);  
        diffLeft=((380-230)/50)*(tempLoopMouth+1);  
        diffRight=((330-250)/50)*(tempLoopMouth+1);  
       }   
         context.beginPath();  
         context.moveTo(220, 230);  
         context.bezierCurveTo(220, 380 - diffLeft, 380, 330 - diffRight, 380, 250);  
         context.closePath(); // complete custom shape  
         context.fillStyle = grd_m;  
         context.fill();  
      }       
      function drawBackground(context){  
         //set the background  
         var grd = context.createRadialGradient(300, 200, 50, 300, 200, 600);  
         grd.addColorStop(0.01, "white");  
         grd.addColorStop(0.5, "black");  
         context.arc(300, 200, 600, 0, 2*Math.PI, false);  
         context.fillStyle = grd;  
         context.fill();  
      }  
      function drawAll(context){  
           drawBackground(context);  
           drawFace(context);  
           drawEyes(context, lastMouseX, lastMouseY);  
           drawMouth(context);  
      }        
      // requestAnim shim layer by Paul Irish    
      window.requestAnimFrame = (function(){    
      return window.requestAnimationFrame ||    
       window.webkitRequestAnimationFrame ||    
       window.mozRequestAnimationFrame ||    
       window.oRequestAnimationFrame ||    
       window.msRequestAnimationFrame ||    
       function(/* function */ callback, /* DOMElement */ element){    
       window.setTimeout(callback, 1000 / 60);    
       };    
      })();     
      function animationLoop(){  
       var canvas = document.getElementById("myCanvas");  
       var context = canvas.getContext("2d");  
       if(clicked == 1)  
         loopMouth++;  
       if(loopMouth==100)  
       {  
         loopMouth = 0;  
         clicked = 0;  
       }  
       drawAll(context);  
       requestAnimFrame(animationLoop);        
      }  
       window.onload = function(){  
         var canvas = document.getElementById("myCanvas");  
         var context = canvas.getContext("2d");  
         canvas.addEventListener('mousemove', function (evt) {  
           var mousePos = getMousePos(canvas, evt);  
           drawAll(context);  
         }, false);  
         canvas.addEventListener('click', function (evt) {  
           var mousePos = getMousePos(canvas, evt);  
           if(clicked == 0)   
            clicked = 1;   
         }, false);  
         drawAll(context, 300, 200);  
         // Start animation  
         animationLoop();  
       };  
     </script>  
   </head>  
   <body onmousedown="return false;">  
     <canvas id="myCanvas" width="600" height="400">  
     </canvas>  
    <button onclick="setColorSetIndex(0)"> green </button>  
    <button onclick="setColorSetIndex(1)"> blue </button>  
    <button onclick="setColorSetIndex(2)"> orange </button>  
    <div id="help">  
     Animations are:   
     <ul>  
      <li> moving mouse on the canvas = move eyes; </li>  
      <li> click on the canvas = close mouth; </li>  
      <li> click on the buttons = change monster color; </li>  
     </ul>  
    </div>  
     </body>  
 </html>  


As you can see, there is basically no HTML. It is a pure drawing made in Javascript...

venerdì 7 novembre 2014

Improvement philosophy

Next time think twice about what are you doing... are you sure that you cannot stop for a second?



sabato 1 novembre 2014

Sankey Diagram with D3JS




I received a task recently which gave me a lot of fun.
I had to create a diagram like the one above with adapted numbers for my company core business.
The idea was to have such numbers in a Power point. One of the biggest challenge was to dig in the DB in order to find the correct informations (but this is beyond the scope of this post).
One of the funniest task was to build the power point presentation! How can I build a power point reporting the information such the one in the diagram above?

I googled it and I discovered that this diagram is called "Sankey" diagram.
I tried then to find any tool who was supporting the creation of those type of diagrams.
Of course, first choice was google chart:
https://developers.google.com/chart/interactive/docs/gallery/sankey

I found that this lybrary is quite static, it does not give too much freedom. Additionally the graphic is quite poor.

I searched deeper and I fell in love for the library D3js
http://d3js.org/

This library support too many of the non conventional diagrams. No words to mention, just have a look at the examples:
https://github.com/mbostock/d3/wiki/Gallery

I must confess that it is not really straightforward, not as easy as the google chart one.
As well the possibilities are enormous.

In this post I want to have my usual copy and paste snippets.
I found a very interesting tutorial here:
http://bl.ocks.org/d3noob/5028304

I have reported here the result of my task (of course number changed):
http://marcoandolfi.eu5.org/sankey_test/viewer.html

And now the copy and paste part.
You need three things:
- The javascript library
- The json file.
- The HTML file rendering it.

The javascript library Sankey.js, I stored a copy on my website here).
Here it follow an example of possible JSON file. Very easy: links and nodes.

 {  
 "links": [  
 {"source":"1A","target":"2A","value":"10"},  
 {"source":"1A","target":"2B","value":"1" },  
 {"source":"1B","target":"2B","value":"15"},  
 {"source":"1C","target":"2A","value":"20"},  
 {"source":"1C","target":"2B","value":"14"},  
 {"source":"1C","target":"2C","value":"45"},  
 {"source":"1D","target":"2A","value":"25"},  
 {"source":"1D","target":"2C","value":"25"},  
 {"source":"2A","target":"3A","value":"9" },  
 {"source":"2A","target":"3B","value":"15"},  
 {"source":"2A","target":"3C","value":"28"},  
 {"source":"2A","target":"3D","value":"17"},  
 {"source":"2B","target":"3B","value":"15"},  
 {"source":"2B","target":"3C","value":"15"},  
 {"source":"2C","target":"4B","value":"50"},  
 {"source":"3A","target":"4A","value":"10"},  
 {"source":"3B","target":"4B","value":"20"},  
 {"source":"3B","target":"4A","value":"5" },  
 {"source":"3C","target":"4B","value":"40"}  
 ] ,  
 "nodes": [  
 {"name":"1A"},  
 {"name":"1B"},  
 {"name":"1C"},  
 {"name":"1D"},  
 {"name":"2A"},  
 {"name":"2B"},  
 {"name":"2C"},  
 {"name":"3A"},  
 {"name":"3B"},  
 {"name":"3C"},  
 {"name":"3D"},  
 {"name":"4A"},  
 {"name":"4B"}  
 ] }  


Finally the HTML page (it is needed to adapt only the bold part: Json file name or ajax call and the location of your Sankey.js).
 <!DOCTYPE html>  
 <meta charset="utf-8">  
 <title>SANKEY Experiment</title>  
 <style>  
 .node rect {  
  cursor: move;  
  fill-opacity: .9;  
  shape-rendering: crispEdges;  
 }  
 .node text {  
  pointer-events: none;  
  text-shadow: 0 1px 0 #fff;  
 }  
 .link {  
  fill: none;  
  stroke: #000;  
  stroke-opacity: .2;  
 }  
 .link:hover {  
  stroke-opacity: .5;  
 }  
 </style>  
 <body>  
 <p id="chart">  
 <script src="http://d3js.org/d3.v3.js"></script>  
 <script src="sankey.js"></script>  
 <script>  
 var units = "Widgets";  
 var margin = {top: 10, right: 10, bottom: 10, left: 10},  
   width = 1200 - margin.left - margin.right,  
   height = 740 - margin.top - margin.bottom;  
 var formatNumber = d3.format(",.0f"),  // zero decimal places  
   format = function(d) { return formatNumber(d) + " " + units; },  
   color = d3.scale.category20();  
 // append the svg canvas to the page  
 var svg = d3.select("#chart").append("svg")  
   .attr("width", width + margin.left + margin.right)  
   .attr("height", height + margin.top + margin.bottom)  
  .append("g")  
   .attr("transform",   
      "translate(" + margin.left + "," + margin.top + ")");  
 // Set the sankey diagram properties  
 var sankey = d3.sankey()  
   .nodeWidth(36)  
   .nodePadding(10)  
   .size([width, height]);  
 var path = sankey.link();  
 // load the data  
 d3.json("data.json", function(error, graph) {  
   var nodeMap = {};  
   graph.nodes.forEach(function(x) { nodeMap[x.name] = x; });  
   graph.links = graph.links.map(function(x) {  
    return {  
     source: nodeMap[x.source],  
     target: nodeMap[x.target],  
     value: x.value  
    };  
   });  
  sankey  
    .nodes(graph.nodes)  
    .links(graph.links)  
    .layout(32);  
 // add in the links  
  var link = svg.append("g").selectAll(".link")  
    .data(graph.links)  
   .enter().append("path")  
    .attr("class", "link")  
    .attr("d", path)  
    .style("stroke-width", function(d) { return Math.max(1, d.dy); })  
    .sort(function(a, b) { return b.dy - a.dy; });  
 // add the link titles  
  link.append("title")  
     .text(function(d) {  
         return d.source.name + " → " +   
         d.target.name + "\n" + format(d.value); });  
 // add in the nodes  
  var node = svg.append("g").selectAll(".node")  
    .data(graph.nodes)  
   .enter().append("g")  
    .attr("class", "node")  
    .attr("transform", function(d) {   
            return "translate(" + d.x + "," + d.y + ")"; })  
   .call(d3.behavior.drag()  
    .origin(function(d) { return d; })  
    .on("dragstart", function() {   
            this.parentNode.appendChild(this); })  
    .on("drag", dragmove));  
 // add the rectangles for the nodes  
  node.append("rect")  
    .attr("height", function(d) { return d.dy; })  
    .attr("width", sankey.nodeWidth())  
    .style("fill", function(d) {   
            return d.color = color(d.name.replace(/ .*/, "")); })  
    .style("stroke", function(d) {   
            return d3.rgb(d.color).darker(2); })  
   .append("title")  
    .text(function(d) {   
            return d.name + "\n" + format(d.value); });  
 // add in the title for the nodes  
  node.append("text")  
    .attr("x", -6)  
    .attr("y", function(d) { return d.dy / 2; })  
    .attr("dy", ".35em")  
    .attr("text-anchor", "end")  
    .attr("transform", null)  
    .text(function(d) { return d.name; })  
   .filter(function(d) { return d.x < width / 2; })  
    .attr("x", 6 + sankey.nodeWidth())  
    .attr("text-anchor", "start");  
 // the function for moving the nodes  
  function dragmove(d) {  
   d3.select(this).attr("transform",   
     "translate(" + (  
            d.x = Math.max(0, Math.min(width - d.dx, d3.event.x))  
          ) + "," + (  
           d.y = Math.max(0, Math.min(height - d.dy, d3.event.y))  
       ) + ")");  
   sankey.relayout();  
   link.attr("d", path);  
  }  
 });  
 </script>  
 </body>  
 </html>  


Most probably you want to run the first test on your machine. Therefore you need to launch your browser in a "special mode" so that it will be able to read local file (for securityy reason browser deactivate this mode). I like Chrome and therefore what follows is valid only for Chrome, but extremely similar is for the other browser.

First be sure that you have killed every process of Chrome, which does not simply mean to close the browser, but to open the task manager and kill every process.
Then create a batch file like this (or invoke from command line):

 cd "C:\Program Files (x86)\Google\Chrome\Application"  
 chrome.exe --allow-file-access-from-files  



lunedì 29 settembre 2014

Extract DB as XML



I want to have a single query which is extracting the complete structure of the database into a simple XML.


For example having:
 create table my_test   
 (  
  f11 number,   
  f12 number,   
  f13 varchar2(300),   
  CONSTRAINT my_test_pk PRIMARY KEY (f11)  
 );  
 create table my_test_2   
 (  
  f21 number,   
  f22 number,   
  f23 varchar2(300),   
  CONSTRAINT my_test_pk_2 PRIMARY KEY (f21, f22),   
   CONSTRAINT fk_column_2  
   FOREIGN KEY (f22)  
   REFERENCES my_test (f11)  
 );  
 create table my_test_3  
 (  
  f31 number,   
  f32 number,   
  f33 varchar2(300),   
  CONSTRAINT my_test_pk_3 PRIMARY KEY (f31),   
   CONSTRAINT fk_column_3  
   FOREIGN KEY (f32)  
   REFERENCES my_test (f11)  
 );  
 comment on table my_test_2 is 'this is my test_2';  
 comment on table my_test is 'this is my test';  
 comment on column my_test.f11 is 'my_test.f11';  
 comment on column my_test.f12 is 'my_test.f12';  
 comment on column my_test.f13 is 'my_test.f13';  
 comment on column my_test_2.f21 is 'my_test.f21';  
 comment on column my_test_2.f22 is 'my_test.f22';  
 comment on column my_test_2.f23 is 'my_test.f23';   


I want to have as output:
 <?xml version="1.0" encoding="UTF-8"?>  
 <schema name="MARCOA">  
   <table name="MY_TEST" pk_column_name="F11" pk_name="MY_TEST_PK" pk_owner="MARCOA">  
    <comment>this is my test</comment>  
    <column name="F11" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">  
      <comment>my_test.f11</comment>  
      <referenced_by rk_ref_column_name="F22" rk_ref_table_name="MY_TEST_2" />  
      <referenced_by rk_ref_column_name="F32" rk_ref_table_name="MY_TEST_3" />  
    </column>  
    <column name="F12" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="Y">  
      <comment>my_test.f12</comment>  
    </column>  
    <column name="F13" num_nulls="" num_distinct="" type="VARCHAR2(300)" nullable="Y">  
      <comment>my_test.f13</comment>  
    </column>  
   </table>  
   <table name="MY_TEST_2">  
    <comment>this is my test_2</comment>  
    <column name="F21" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">  
      <comment>my_test.f21</comment>  
    </column>  
    <column name="F22" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">  
      <comment>my_test.f22</comment>  
      <referencing fk_ref_column_name="F11" fk_ref_table_name="MY_TEST" />  
    </column>  
    <column name="F23" num_nulls="" num_distinct="" type="VARCHAR2(300)" nullable="Y">  
      <comment>my_test.f23</comment>  
    </column>  
   </table>  
   <table name="MY_TEST_3">  
    <comment />  
    <column name="F31" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">  
      <comment />  
    </column>  
    <column name="F32" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="Y">  
      <comment />  
      <referencing fk_ref_column_name="F11" fk_ref_table_name="MY_TEST" />  
    </column>  
    <column name="F33" num_nulls="" num_distinct="" type="VARCHAR2(300)" nullable="Y">  
      <comment />  
    </column>  
   </table>  
 </schema>  




And here is the query to do it:
 with csv_constraint as (  
   SELECT distinct owner, constraint_name, table_name, SUBSTR (SYS_CONNECT_BY_PATH (column_name , ','), 2) csv  
      FROM (SELECT   
            acc.owner, acc.constraint_name, acc.table_name, acc.column_name,   
            acc.position rn,  
            COUNT (*) OVER (partition by cc.constraint_name) cnt  
         FROM   
            all_cons_columns acc left outer join all_constraints cc  
              on (acc.owner = cc.owner and acc.table_name = cc.table_name and cc.constraint_type = 'P')  
         where (cc.owner, cc.table_name) in (select t.owner, t.table_name from all_tables t)  
         /* to be changed */ and cc.owner = 'MARCOA'  
         )  
      WHERE rn = cnt  
   START WITH rn = 1  
   CONNECT BY constraint_name = prior constraint_name and rn = PRIOR rn + 1  
 ),   
 xml_builder as(  
  select 'STARTTAG' as tagtype, '_0000' as postfix, '<?>' as xml_tag from dual union all   
  select 'CLOSETAG' as tagtype, '_9999' as postfix, '</?>' as xml_tag from dual   
 ),   
 schemas as (  
  select   
   lpad(user_id, 4, '0') as hh_s_id,   
   username  
  from all_users   
  where username = user  
 ),   
 tab_cols_cons as (  
   select   
     tt.owner,   
     tt.table_name,   
     cl.owner pk_owner,   
     cl.constraint_name pk_name,   
     cl.csv pk_column_name,   
     lpad(ss.user_id, 4, '0') as hh_s_id,   
     lpad(t_id, 4, '0') as hh_t_id  
   from (  
    select ROW_NUMBER( ) OVER (PARTITION BY owner ORDER BY table_name NULLS LAST) t_id, t.*  
    from all_tables t  
   )tt join all_users ss   
              on (tt.owner = ss.username)  
             left outer join csv_constraint cl  
              on (cl.owner = tt.owner and tt.table_name = cl.table_name)  
   /* to be changed */ where tt.owner = 'MARCOA' and tt.table_name like 'MY_TEST%'  
 ),   
 tab_cols as (  
   select   
     atc.owner,   
     atc.table_name,   
     atc.column_name,   
     atc.nullable,   
     case   
      when atc.data_type = 'NUMBER' then atc.data_type || '(' || atc.data_length || ', ' || nvl(atc.data_scale, 0) || ')'  
      when atc.data_type = 'VARCHAR2' then atc.data_type || '(' || atc.data_length || ')'  
      else atc.data_type  
     end as col_type,  
     atc.num_distinct,  
     atc.num_nulls,   
     tcc.hh_s_id,   
     tcc.hh_t_id,   
     lpad(atc.column_id + 1, 4, '0') as hh_c_id  
   from tab_cols_cons tcc join all_tab_columns atc on (tcc.owner = atc.owner and atc.table_name = tcc.table_name)  
 ),   
 refs as (  
     select distinct all_constraints.owner fk_owner, -- all_constraints.constraint_name, all_constraints.constraint_type,  
         all_constraints.constraint_name fk_name,  
         all_cons_columns.table_name fk_table_name,  
         all_cons_columns.column_name fk_column_name,  
         all_constraints.r_owner fk_ref_owner,  
         -- all_constraints.r_constraint_name fk_ref_pk_name,  
         fk_ref_table_name,  
         fk_ref_column_name  
      from ALL_CONSTRAINTS  
      join ALL_CONS_COLUMNS on ALL_CONSTRAINTS.constraint_name = ALL_CONS_COLUMNS.constraint_name  
      join (  
         select all_constraints.owner fk_owner,   
             all_constraints.constraint_name fk_name,   
             -- all_constraints.constraint_type,  
             all_cons_columns.table_name fk_ref_table_name,  
             all_cons_columns.column_name fk_ref_column_name  
          from ALL_CONSTRAINTS  
          join ALL_CONS_COLUMNS on ALL_CONSTRAINTS.constraint_name = ALL_CONS_COLUMNS.constraint_name  
          where constraint_type in ('P')  
         ) on all_constraints.r_owner = fk_owner and all_constraints.r_constraint_name = fk_name  
      where ALL_CONSTRAINTS.owner = user  
       and constraint_type in ('R')  
 ),   
 referenced_list as (  
  select fk_owner, fk_table_name rk_ref_table_name, fk_column_name rk_ref_column_name,   
   hh_s_id,   
   hh_t_id,   
   hh_c_id,   
   ROW_NUMBER( ) OVER (PARTITION BY fk_owner, fk_table_name , fk_column_name ORDER BY fk_ref_owner, fk_ref_table_name, fk_ref_column_name NULLS LAST) rd_id  
  from tab_cols tc join refs r on (tc.owner = r.fk_ref_owner and tc.table_name = r.fk_ref_table_name and tc.column_name = r.fk_ref_column_name )  
 ),   
 referencing_list as (  
  select fk_ref_owner, fk_ref_table_name, fk_ref_column_name,  
   hh_s_id,   
   hh_t_id,   
   hh_c_id  
  from tab_cols tc join refs r on (tc.owner = r.fk_owner and tc.table_name = r.fk_table_name and tc.column_name = r.fk_column_name )  
 )  
 select xml   
 from (  
   select res_query, final_id, xml   
   from (  
     select   
      'q1' as res_query,   
      case   
       when tagtype = 'STARTTAG'   
         then replace(xml_builder.xml_tag, '?', 'schema name="' || username || '"')   
         else replace(xml_builder.xml_tag, '?', 'schema')   
      end as xml,   
      hh_s_id || xml_builder.postfix as final_id  
     from schemas join xml_builder on (1=1)  
     union all   
     select   
      'q2' as res_query,   
      case   
       when tagtype = 'STARTTAG'   
         then   
           case when pk_column_name is null   
            then replace(xml_builder.xml_tag, '?', 'table name="' || table_name || '"')  
            else replace(xml_builder.xml_tag, '?', 'table name="' || table_name || '" pk_owner = "' || pk_owner || '" pk_name = "' || pk_name || '" pk_column_name = "' || pk_column_name || '"')  
           end  
         else replace(xml_builder.xml_tag, '?', 'table')   
      end as xml,   
       hh_s_id || '_' || hh_t_id || xml_builder.postfix as final_id  
     from tab_cols_cons join xml_builder on (1=1)  
     union all   
     select   
       'q3' as res_query,   
       '<comment>' || comments || '</comment>' as xml,   
       hh_s_id || '_' || hh_t_id || '_0001' as final_id  
     from tab_cols_cons t join all_tab_comments atc on (t.owner = atc.owner and t.table_name = atc.table_name)  
     union all   
     select   
      'q4' as res_query,   
      case   
       when tagtype = 'STARTTAG'   
         then replace(xml_builder.xml_tag, '?', 'column name="' || column_name || '" nullable="' || nullable || '" type="' || col_type || '" num_distinct = "' || num_distinct || '" num_nulls ="' || num_nulls || '"' )   
         else replace(xml_builder.xml_tag, '?', 'column')   
      end as xml,   
      hh_s_id || '_' || hh_t_id || '_' || hh_c_id || xml_builder.postfix as final_id  
     from  tab_cols join xml_builder on (1=1)  
     union all   
     select   
       'q5' as res_query,   
       '<comment>' || comments || '</comment>' as xml,   
       hh_s_id || '_' || hh_t_id || '_' || hh_c_id || '_0001' as final_id  
     from tab_cols tc join all_col_comments acc on (tc.owner = acc.owner and tc.table_name = acc.table_name and tc.column_name = acc.column_name)  
     union all   
     select   
       'q6' as res_query,   
       '<referencing fk_ref_table_name="' || fk_ref_table_name || '" fk_ref_column_name="' || fk_ref_column_name || '"/>' as xml,   
       hh_s_id || '_' || hh_t_id || '_' || hh_c_id || '_0002' as final_id  
     from referencing_list  
     union all   
     select   
       'q7' as res_query,   
       '<referenced_by rk_ref_table_name="' || rk_ref_table_name || '" rk_ref_column_name="' || rk_ref_column_name || '"/>' as xml,   
       hh_s_id || '_' || hh_t_id || '_' || hh_c_id || '_' || lpad(rd_id+2, 4, '0') as final_id  
     from referenced_list  
   )  
   order by 2  
 );  

giovedì 25 settembre 2014

How to convert a select query into a CSV



Source:


Goal:
 create table countries ( country_name varchar2 (100));  
 insert into countries values ('Albania');  
 insert into countries values ('Andorra');  
 insert into countries values ('Antigua');  


 SELECT * from countries;  
 COUNTRY_NAME       
 ----------------------  
 Albania         
 Andorra         
 Antigua    
 Goal:   


 select * from ();   
 CSV  
 ----------------------  
 Albania, Andorra, Antigua  


Solution: 
 SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv  
    FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,  
           COUNT (*) OVER () cnt  
        FROM countries)  
    WHERE rn = cnt  
 START WITH rn = 1  
 CONNECT BY rn = PRIOR rn + 1;  
 CSV                                         
 --------------------------  
 Albania,Andorra,Antigua                               
 1 row selected.  

martedì 29 luglio 2014

Overload

My classic approach!
You get stuck in your dozens daily problems... You do not know how to get out of it...
STOP!
This is the exact meaning of "Thinking outside the box": try to see your problem from a different angle. How can you? do not hold your problems strict in your hands... try to now look at them and come back later... The angle will be for sure different!