The NewSQL Movement - ReadWriteCloud

"This post is part of our ReadWriteCloud channel, which is dedicated to covering virtualization and cloud computing. The channel is sponsored by Intel and VMware. Read the case study about how IBM helped one client solved its server sprawl problems."

The NewSQL Movement - ReadWriteCloud

To be relational, or not to be relational? That's NOT the question!

A Sergio Bossa and Alex Snaps interesting presentation see it on slideshare

Intercept null value on a query

These last days working to create a Pentaho report with openERP data, I came across a serious problem:
if a query return a null value arithmetic operations are in error. The question was how to intercept the null value, surfing on Google I found this

SELECT IFNULL (1,0) this query return 1
SELECT IFNULL(null, 0) this query return 0

Fine my problem was solved NO!!  openERP use postgresql and postgresql doesn't support IFNULL
Again surfing on Google and I found COALESCE
The postgresql manual say:

The COALESCE function returns the first arguments of ITS THAT IS NOT NULL. Returned is null only if all arguments are null. Often it is used to substitute a default value for null values 

Finally my problem was solved my query now  is something like this
SELECT COALESCE(sum(aal.amount * -1) , 0) FROM account_analytic_line AS aal WHERE (aal.account_id=83) and aal.journal_id = 3

Rinaldo

Nested Sets

It is often required to have tree structures, even if only to manage categories. A tree structure is a tree consisting of a single or multiple root which each node can have multiple nodes son, and that with infinite depth. 
Generally the method used to do this is to have a unique identifier for each node of the tree, and insert the identifier of the father node in the registration of the node.
The disadvantage of this technique is that one is obliged to make a SQL query for each node to retrieve all the son of a node. We can improve that by downloading all the nodes in the database, and rebuilding the tree by hand using an array indexed by parent node.
This technique is not really suitable for very large tree structures of several thousands of nodes.
So why don't trying by represent the tree differently, not as a tree, but as  Nested Sets.

The Nested Set is an adjacent list realized in SQL as a tree.

For example having the following tree structure:

We can represent it as a set

The SQL for create the table is :

CREATE TABLE `tree` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 32 ) NOT NULL ,
`parentid` INT NOT NULL ,
`lft` INT NOT NULL ,
`rgt` INT NOT NULL ,
PRIMARY KEY ( `id` ))

Selection of all nodes is very simple:

SELECT
  n0.id
FROM
  tree n0,
  tree n1
WHERE
    n1.id = 0
AND n0.lft BETWEEN n1.lft AND n1.rgt;

...

ASAP I will wrote a Java classes to maintain Nested Sets

keep in touch 

Rinaldo

references: http://www.mti.epita.fr/blogs/2008/07/06/avoir-une-structure-arborescente-en-sql/