Oct 102016

My colleague needed to use a function in the database in a query, but found that the function was not defined/reachable in his schema. So the solution is to ask for the function to be granted to you, or to create the function in your schema, or…

…use the new WITH enhancement available from Oracle 12c.

Here is a very simple example, that squares a number and returns it. The function is used in the query. How nice :-p

  function s(x in integer) return integer is
    return x*x;
  q as (select 1 n from dual union all select 2 from dual)
  select q.n,s(q.n) from q
Aug 202016

Gaming is a way to relief stress and not think about daily life. Lately, even gaming can’t keep me interested. But my mind is busy all day long with all kinds of stuff, so yesterday I gave mindmapping a try. A mindmap for what is needed to create a gameserver. A server that keeps track of the users, their progress, serves the game itself to the clients, etc. One thing a gameserver needs is a way to store all that data, so a proper database is needed. So I looked at several candidates and in the process I came across Redis. Maybe not the easiest backend database when you need graphing for social features, but absolutely the perfect toy to keep my mind occupied for some time.
Redis has enough client APIs, like most databases. Let’s see if we can use the C#/.Net API from within Unity. Touch/collect this item and increase a value in the Redis database: the XP of the user playing the game.

I will probably create nothing for real, but it’s nice to be not bored for some time.

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