Architecture and Data Blog

Thoughts about intersection of data, devops, design and software architecture

Behavior Driven Database Development

When Behavior Driven Development BDD was introduced, some of the key principles were

Requirements are behavior,
Provides “ubiquitous language” for analysis,
Acceptance criteria should be executable.
Design constraints should be made into executable tests.

Posted by Pramod Sadalage on Sunday, August 16, 2015

Usage of mixed case database object names is dangerous

Some versions back, Oracle would not allow to create database object names with mixed cases, even if we tried to create them, we could not. In newer versions of Oracle we can create tables, columns, indexes etc using mixed case or lower case, when the names are put inside double quotes. For example CREATE TABLE "Customer" ( "CustomerID" number(10) ... ); CREATE INDEX "IDX_Customer_CustomerID" on "Customer"("CustomerID"); We created table named “Customer” with a column “CustomerID” and the index is named “IDX_Customer_CustomerID”.

Posted by Pramod Sadalage on Tuesday, August 6, 2013

Explicitly rollback when you encounter a deadlock.

Dead lock is caused in the database when you have resources (connections) waiting for other connections to release locks on the rows that are needed by the session, resulting in all session being blocked. Oracle automatically detects deadlocks are resolves the deadlock by rolling back the statement in the transaction that detected the deadlock. Thing to remember is that last statement is rolled back and not the whole transaction, which means that if you had other modifications, those rows are still locked and the application should make sure that it does a explicit rollback on the connection.

Posted by Pramod Sadalage on Tuesday, May 26, 2009

In Oracle 11g password is case sensitive

In Oracle 10g and before we all know that passwords are not case sensitive, so PASSWORD, Password, password would let you in and everything would be okay. If you upgrade to Oracle 11g (I know lot of you are waiting for 11gR2), you will find that passwords are case sensitive. Here is an example of case sensitive passwords. c:\Software>sqlplus bddd/bddd@dosa SQL*Plus: Release 11.1.0.6.0 - Production on Wed May 6 15:17:43 2009 Copyright (c) 1982, 2007, Oracle.

Posted by Pramod Sadalage on Wednesday, May 6, 2009

Oracle Metadata can be mis-leading

Oracle has metadata about all its objects in various tables/views. One such view is the USER_OBJECTS or ALL_OBJECTS, this view has a column named as STATUS which shows you if the given object is VALID or INVALID. The status applies to DB Code (Stored Procedures, Functions, Triggers etc). To find all the INVALID objects in the schema, issue SELECT * FROM USER_OBJECTS WHERE STATUS='INVALID'; One problem with the way oracle maintains this metadata is, changing the underlying table on which the DB Code depends, oracle marks the objects are INVALID even though the underlying table may have changed in such a way, that it does not affect the DB Code at all (like adding a new column, or making a colum nullable).

Posted by Pramod Sadalage on Tuesday, March 31, 2009

Presentation on Database Refactoring

My Presentation on Database Refactoring at QCon was recorded and is live now on infoQ here

Posted by Pramod Sadalage on Thursday, March 26, 2009

Considerations when using stored procedures for doing CRUD.

Some environments like to have access to the database tables routed via stored procedures. Instead of using Create/Read/Update/Delete (CRUD) with DML, stored procedures are invoked with the parameters to perform the required operation. I’m not arguing about the benefits/pitfalls of this approach, if you have to do stored procedures, here are some things to look at. Make the stored procedure handle one object/table only and not multiple objects or tables. Do not commit open transactions inside the stored procedures.

Posted by Pramod Sadalage on Tuesday, February 3, 2009

Using Explicit Order By in your SQL Statements

Recently when our test databases where upgraded new version of Oracle, we started noticing that the order in which some drop down lists were being displayed was not correct. It turns out that the SELECT statement we had, did not have a ORDER BY clause and the data was being returned in the ORDER of the creation of rows (in the order of ROWID) when the database was upgraded these ROWID’s got changed and hence the ORDER of the data being shown in the drop down lists.

Posted by Pramod Sadalage on Tuesday, September 30, 2008

Version Control your work..

So we version control/source control everything on our project.. code/data/artifacts/diagrams etc. yesterday I said why not extend it to my writings to everything I have. So I started this long journey of refactoring my folder layout and making a nice folder structure to hold all the things I have written about have other artifacts in the process of writing and moved them all to subversion, now all my example code and writings are all under version control that gets backed up everyday….

Posted by Pramod Sadalage on Friday, June 6, 2008

Enforcing your assumptions about database functionality

When you are writing xUnit tests you are in certain ways trying to make sure that the test breaks when the code that is being tested changes the assumptions you made when writing the Test and Production code. Similarly if you are relying on the database to throw a error when you put invalid data, then you should write a test around this assumption, so that when someone changes the database to not behave the way you assumed it to behave, the test you wrote will break and it will force the team to think about the change to the database that is being undertaken.

Posted by Pramod Sadalage on Friday, June 8, 2007