Jan 162015

My ex-colleague and friend Wijnand asked me if I could do a little project together with him. It involves working with graph databases, in particular Neo4j. Graph databases are different from “traditional” relational databases in the sense that they support semi-structured data without predefining a datamodel. In a way that’s also the case for other NoSQL databases, but when referring to NoSQL databases most people will think of the ones that are used to stored documents of all types. That’s not what graph databases are for.

A graph database is typically used when the relationships between the data are as important as the data itself. Consider persons. I know a lot of other people. I love some of them. I have worked with more. “KNOW”, “LOVE” and “WORKED WITH” are relationships between persons. In a graph database you create nodes and link them with relationships. Relationships are directional and explicit. Unlike relational databases where you have to add constraints to enforce relationship-integrity, a graph database cannot have a stale/dangling relationship.
Back to the persons I know, love, and worked with (yes, an Oxford comma). Persons are not nodes, so in the graph database you label them with “person” or “Persons” (note: define a standard before wildly labeling nodes). A node can even have more than one label.

Another thing that makes graph databases so different is that relationships can have properties. Since when do I know my best friend? In what period did I work with Wijnand? When creating/defining the relationship between two nodes, you can give that relationship properties, on which you can search or filter at a later stage.

The main advantage of a graph database over a relational database is its speed. When you have data with a lot of relationships, relational databases will easily drop performance when it needs to join multiple large (lots of rows) tables. This is not the case with a graph database. It excels in joins. Because that’s what its engine is made to do in the first place. When just iterating through data, the relational database will probably win, but with a lot of joins, a graph database can make a huge difference.

For example. Create a persons table in your relational database. Make it so that you can have friends, colleagues or loved ones that are persons in the same table. Now query for all your friends that where also a colleague within a certain period. Good luck writing your hierarchical query :-)

Mar 122013

I’m an oldskool Oracle user, so I learned and used joins in the oldfashioned way. Just name all the tables in the WHERE-clause, and specify the join conditions, and use (+) for outer joins. Times change, and the new syntax has been around for years. Never really used it, but today I did, and I found this image on the internet, I thought I’d share it, since I think it’s quite helpful.


It clearly shows how to use LEFT and RIGHT joins.

Sep 302010

Unexpected news from Oracle: SQL Developer Data Modeler is now a free product. The first editions where free with the sidenote that it was going to be a paid product. The previous release was present in Oracle Global Pricing list and was not cheap. Only the viewer was free. Now Oracle’s website has a note saying that the Datamodeler is a free product: Oracle SQL Developer Data Modeler: Pricing FAQ. Unexpected, but welcome news.

Sep 222010

Just found out the beta is no longer beta, PostgreSQL 9.0.0 has reached “release” status. Version 9 has native x64 Windows support, and per-column-triggers. Nice! Read the release notes for more information about the new version.

No info about a new version of PostGIS (yet).

Jun 152010

To reset the sys password of a database, do the following:

1) rename/delete ${ORACLE_HOME}/database/PWD{ORACLE_SID}.ora
2) enter the command: orapwd file=${ORACLE_HOME}/database/PWD{ORACLE_SID}.ora password=newpassword
3) start “sqlplus /nolog”
4) login with sys and the newpassword (SQL> connect sys/newpassword as sysdba)
5) now reset other passwords or just be done