SimonOnSoftware

The Importance of Database Testing

Simon — Wed, 23 Nov 2011 11:39:37 +0000

Basic Mistakes in Database Testing There is a huge number of books about TDD. Those books usually concentrate on using test for units of work. Units of work are understood in many different ways, usually it means a class. So the books say: write a lot of tests, make code in such a way the [...] Related posts:

Testing Database – Small Reply On Ovid’s blog I found lately this entry. First of...
Unit Test Your Database. I observe the endless war about TDD. On one side...
Checking PostgreSQL Database Creation Date Some time ago we had a problem: on testing there...

PostgreSQL Versioning Policy

Simon — Fri, 18 Nov 2011 13:19:27 +0000

I was explaining the PostgreSQL version naming convention to my colleague a couple of days ago. The differences between e.g. 8.3 and 8.4 and what is different between 8.4 and 9.0 and what should be used during database migration to another version. I knew all that just because somebody told me that many years ago [...] Related posts:

Unit Test Your Database. I observe the endless war about TDD. On one side...
How to check PostgreSQL privileges. Someone asked me how to list all tables that a...
Problem with random() in PostgreSQL Subselect In the simple project I work sometimes on, I need...

Unit Test Your Database.

Simon — Mon, 14 Nov 2011 20:14:29 +0000

I observe the endless war about TDD. On one side there are those who claim that TDD is the best thing since sliced bread. On the other side there are those who claim that TDD is just a waste of time and is too slow. But what about databases? Nobody writes about them too seriously. [...] Related posts:

Testing Database – Small Reply On Ovid’s blog I found lately this entry. First of...
Why Use ORMs? Why Use ORMs? ORM aka Object Relational Mapping is a...
The Importance of Database Testing Basic Mistakes in Database Testing There is a huge number...

How to Ensure There is One Row… part 2

Simon — Wed, 09 Nov 2011 20:31:02 +0000

Some time ago I wrote about ensuring that there is only one row with a specific value in a column. Today a very similar problem: there is a table like this: CREATE TABLE test ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, value TEXT NOT NULL ); We need to be sure that there [...] Related posts:

How to Ensure There is One Row With a Value The Problem Let’s imagine a business requirement that there should...
SQLAlchemy Defines New SQL Standard Dreaming About SQL Well, I thought that I know the...
How to Allow For Max 4 Rows in Update (PostgreSQL) The Problem Question found on the net: how to restrict...

What Is The Real PostgreSQL Triggers Ordering

Simon — Tue, 01 Nov 2011 20:49:00 +0000

Introduction During the last database training that I led, I was talking about selecting rows from database and sorting them (including some problems with collations). Later I was talking about triggers. For this entry assume that there are only row level triggers. When you perform e.g. INSERT in PostgreSQL, there are fired triggers (if they [...] Related posts:

How to Allow For Max 4 Rows in Update (PostgreSQL) The Problem Question found on the net: how to restrict...
PostgreSQL Collation – part 1 PostgreSQL is (IMHO) much better than MySQL but unfortunately it ...
PostgreSQL FOUND Problem FOUND is a global variable that exists in the plpgsql...

How To Store UUIDs in PostgreSQL

Simon — Tue, 11 Oct 2011 20:34:01 +0000

This story is a just another example of what I call Using varchar(255) for everything won’t make you a sorcerer. Every database has many different types, they are there on purpose, not by accident. I’ve seen that too many times: a programmer (most companies don’t have DBAs for that) creates a database structure using ONE [...] Related posts:

UUID and Java :) Why not to use UUID as Primary Key and that...
Random String in PostgreSQL Last time I wrote about the common problem with the...
PostGIS and Small Number of Huge Geometries The Problem A couple of weeks ago I had a...

PostGIS and Small Number of Huge Geometries

Simon — Thu, 22 Sep 2011 20:29:21 +0000

The Problem A couple of weeks ago I had a quite strange situation: a PostgreSQL table with small number of huge geometries. A program had to query the table really often, so the speed was a crucial thing. Replicate the Situation Small Number of Big Geometries First of all let s create a couple of [...] Related posts:

PostgreSQL Strange Timings A very simple query. PostgreSQL 8.4. I have no idea...
Are SQL Database Joins So Bad? Last time on many forums there were endless discussions about...
How to Allow For Max 4 Rows in Update (PostgreSQL) The Problem Question found on the net: how to restrict...

Why Is Query Slow?

Simon — Sun, 21 Aug 2011 14:18:25 +0000

A couple of days ago a colleague of mine had a quite normal problem. The query was slow. The whole application was written in Java, using Hibernate, tables were small. There were about 30 rows in one table, and 10 in another. One small join, one call of PostGIS function. The query took several seconds [...] Related posts:

What a big database is and why there isn’t anything like that. Yea, simple question. Usually I hear something like this: “Help...
PostgreSQL Strange Timings A very simple query. PostgreSQL 8.4. I have no idea...
Unit Test Your Database. I observe the endless war about TDD. On one side...

Problem with random() in PostgreSQL Subselect

Simon — Thu, 04 Aug 2011 20:04:10 +0000

In the simple project I work sometimes on, I need to generate a lot of random data in PostgreSQL database. I’ve got a couple of functions for that, but all of them are based on the simple random() function. The random() returns value from the range [0, 1): # select random(); random ------------------- 0.583591205533594 (1 [...] Related posts:

Random String in PostgreSQL Last time I wrote about the common problem with the...
Common Problem with random(min, max) There is a common problem with many homemade random functions....
How to Ensure There is One Row With a Value The Problem Let’s imagine a business requirement that there should...

How to Ensure There is One Row With a Value

Simon — Mon, 27 Jun 2011 18:46:19 +0000

The Problem Let’s imagine a business requirement that there should be only one active row in a table. Active means that the column ‘is_active’ is set to true. There can be many rows with false or null values. The row with only one value can be only one. The simple table could look like this: [...] Related posts:

How to Ensure There is One Row… part 2 Some time ago I wrote about ensuring that there is...
How to Allow For Max 4 Rows in Update (PostgreSQL) The Problem Question found on the net: how to restrict...
SQLAlchemy Defines New SQL Standard Dreaming About SQL Well, I thought that I know the...