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!
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!
domenica 27 luglio 2014
mercoledì 16 luglio 2014
Ajax call for Java Spring
I found a great tutorial on how to invoke an ajax call on java spring.
Source: http://crunchify.com/how-to-use-ajax-jquery-in-spring-web-mvc-jsp-example/
It follows the code (of the website reported).
This code is going to invoke an ajax call every x seconds.
Code for the controller:
package com.crunchify.controller;
import java.util.Date;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;
import java.util.Random;
/**
* @author Crunchify.com
*
*/
@Controller
public class CrunchifySpringAjaxJQuery {
@RequestMapping("/ajax")
public ModelAndView helloAjaxTest() {
return new ModelAndView("ajax", "message", "Crunchify Spring MVC with Ajax and JQuery Demo..");
}
@RequestMapping(value = "/ajaxtest", method = RequestMethod.GET)
public @ResponseBody
String getTime() {
Random rand = new Random();
float r = rand.nextFloat() * 100;
String result = "<br>Next Random # is <b>" + r + "</b>. Generated on <b>" + new Date().toString() + "</b>";
System.out.println("Debug Message from CrunchifySpringAjaxJQuery Controller.." + new Date().toString());
return result;
}
}
and here the code for the View:
<html>
<head>
<TITLE>Crunchify - Spring MVC Example with AJAX call</TITLE>
<style type="text/css">
body {
background-image:
url('http://cdn3.crunchify.com/wp-content/uploads/2013/03/Crunchify.bg_.300.png');
}
</style>
<script type="text/javascript"
src="http://code.jquery.com/jquery-1.10.1.min.js"></script>
<script type="text/javascript">
function crunchifyAjax() {
$.ajax({
url : 'ajaxtest.html',
success : function(data) {
$('#result').html(data);
}
});
}
</script>
<script type="text/javascript">
var intervalId = 0;
intervalId = setInterval(crunchifyAjax, 3000);
</script>
</head>
<body>
<div align="center">
<br> <br> ${message} <br> <br>
<div id="result"></div>
<br>
<p>
by <a href="http://crunchify.com">Crunchify.com</a>
</p>
</div>
</body>
</html>
I had to use a slight variation.
I wanted to have the ajax call only on click of a button.
The controller remains the same. What needs to be changed is the view:
<html>
<head>
<TITLE>Crunchify - Spring MVC Example with AJAX call</TITLE>
<style type="text/css">
body {
background-image:
url('http://cdn3.crunchify.com/wp-content/uploads/2013/03/Crunchify.bg_.300.png');
}
</style>
<script type="text/javascript"
src="http://code.jquery.com/jquery-1.10.1.min.js"></script>
<script type="text/javascript">
function crunchifyAjax() {
$.ajax({
url : 'ajaxtest.html',
success : function(data) {
$('#result').html(data);
}
});
}
$( "#button_refresh" ).click(function() {
crunchifyAjax();
});
</script>
</head>
<body>
<div align="center">
<br> <br> ${message} <br> <br>
<button id="button_refresh">Refresh</button>
<div id="result"></div>
<br>
<p>
by <a href="http://crunchify.com">Crunchify.com</a>
</p>
</div>
</body>
</html>
Iscriviti a:
Post (Atom)