DBA | Architecture and Data Blog

Publish data models in CI Pipeline

Enable wider usage of data models by publishing the latest models on team Wikis

Many a times ER models are created by the data team and are not shared outside of the data team generally for the lack of tools licenses, since its not feasible for the entire team to purchase licenses for the ER modelling tools such as Erwin Data Modeller or Er Studio

Posted by "Pramod Sadalage" on Saturday, September 16, 2023

Static Analysis of PL/SQL code

In all new development and sometimes during legacy codebase modernization, developers tend to add code quality checks and static analysis of codebase such as style checks, bug finders, cyclomatic complexity checking etc. into the CI/CD pipeline. When we inherit a codebase that has much PL/SQL and there is a desire to put the PL/SQL code base through the same types of code analysis, what options does a developer/dba have?

There are some options we can explore such as

Posted by Pramod Sadalage on Tuesday, August 1, 2017

Using liquibase to load data and ignore some columns

Loading data into tables is needed many times on projects to load test, Liquibase provides a method to load data into tables with lots of customization. In the example shown below, I’m loading zip code data with the following column layout

"Zipcode","ZipCodeType","City","State","LocationType","Lat","Long","Location","Decommisioned","TaxReturnsFiled","EstimatedPopulation","TotalWages"

Posted by Pramod Sadalage on Monday, May 15, 2017

Automatically adding columns to Rails migrations

Many projects need addition of identical columns to all the tables created by the project. Audit columns are an example of such a requirement. The requirement is to add columns such as created_by, created_date, modified_by and modified_date to all the tables, these columns store, who created the row, when the row was created, who modified the row last and when was it modified. created_by and created_date are required to be present when the row is inserted and thus are required to be not nullable. Adding these columns to each and every table is a lot of work for developers.

Posted by Pramod Sadalage on Monday, April 25, 2016

Synonyms as abstraction layer

In many development shops, developers are not allowed to access the database schema directly, and are not allowed to create tables, indexes, views etc, instead are given access via a different schema that allows SELECT, UPDATE and DELETE access on data. The general reason is to avoid developers creating database objects without

Posted by Pramod Sadalage on Friday, April 15, 2016

Using rake and activerecord to generate boilerplate DB Code

IN many projects, there are tables which need default audit columns such as Created_By, Created_Date, Modified_By, Modified_date and other columns that need to be updated every time some actions are done against the tables and/or columns. This type of functionality can be implemented using triggers.

Posted by Pramod Sadalage on Monday, July 20, 2015

Database naming conventions in different environments

In every enterprise and every project we end up having multiple environments, especially the database side of the enterprise tends to stick around for a longer period of time and has much more dependencies or application integration as opposed to application urls etc. Given this, how to name the servers, databases and schemas becomes a very important decision, do these names provide for an easy way to use the application and not make it harder or the developers to access the database.

Posted by Pramod Sadalage on Wednesday, June 10, 2015

8 Techniques for testing migration of data from legacy systems

Many of the projects we end up working on are replacing existing systems with existing data either wholly or in part. In all of the above projects we end up writing data migration or data conversion code to move the data from legacy systems to the new systems. Many stake holders of the project such as business users, project managers, business analysts really care about the data conversion scripts and the quality of the conversion.

Posted by Pramod Sadalage on Friday, January 23, 2015

10 node mongodb ReplicaSet on a Single Machine

While doing evalauation of NoSQL databases, we had a 10 node riak cluster and wanted check how a similar setup would work with mongodb. So started to setup a 10 node mongodb cluster. Since this was for initial spikes, we decided to set this up on a single machine as with the other test setup using Riak. Before I explain how we setup 10 node mongodb ReplicaSet, let me talk about replica sets.

Posted by Pramod Sadalage on Monday, June 9, 2014

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