This page offers downloads of code samples from the book Relational Databases For Agile Developers, and source code for some free tools useful in Data Warehousing projects.

If you want to use any of the tools, see how to install and configure them.

Code Samples and Tools

You can download code samples from this page and use them as you choose. All the samples have been tested, but you use them entirely at your own risk. We take no reponsibility for what you do with them.

The files are all plain text source code: SQL scripts, Bash shell scripts, user-defined functions, Java source code or data. You need to understand them before you use them, especially the bash shell scripts. They are written with normal precautions against doing anything bad, but if you download code from here and run it, you must take responsibility for what happens.

Title

Description

Download link

Code samples for the book Relational Databases For Agile Developers

You can download a tar or zip archive of all the samples referenced in the book, or access the individual files.

compressed tar file
zip file
Individual files

Sample data and load scripts for the book Relational Databases For Agile Developers

These scripts depend on the database you are using, so click on the appropriate link. If you need a database that isn't shown here, send me an email (ron@thedatastudio.net) telling me the database you are using and I will aim to provide details for that database next.

PostgreSQL
Microsoft SQL Server

Java source for all tools from The Data Studio

All the source code in one archive

compressed tar file
zip file

SQL Scripts to create the tables in the Data Quality database

The data quality tools use a database to collect useful information. We can then access that information in various ways, although the most common way is to run the programs that generate static web-sites, which will be useful throughout the life of your data warehouse.

The database software we have come to prefer for the data quality database is PostgreSQL. We have used Oracle and Microsoft SQL Server for this database, but why pay for a more expensive, inferior database when you can get the best one free?

compressed tar file
zip file
Individual files

The Data Quality configuration file.

The data quality tools need to connect to a variety of databases. They use JDBC to do this. There are JDBC drivers for all the SQL databases and many systems, such as Hive and Dataverse, that provide some subset of SQL database functionality. You use the configuration file stores the connection parameters for all the databases you need to access. Examples are provided for the most common ones.

any_database.conf

Java classes used in the other tools

See Data Profiling and Data Quality.

These files form a small library called DataWarehousingTools.

compressed tar file
zip file
Individual files

Java programs to profile data and display the results

See Data Profiling. Data profiling looks at characteristics of data for the purpose finding potential data quality issues. It can also give useful insights into the ways the data is used.

Download the two jar files: ProfileEngine.jar and DataProfileWebsite.jar, and the sample configuration file any_database.conf to describe the connections to your databases

ProfileEngine.jar
ProfileEngine source files
DataProfileWebsite.jar
Data Profile Website source files

Java programs to monitor data quality and display the results

See Data Quality Monitoring. Data Quality monitoring lets you see anomalies in your data (such as bad values and duplicates). It also lets you look at trends, and highlights deviations from normal patterns of business.

Download the two jar files: DQMonitor.jar and DataQualityWebsite.jar, and the sample configuration file any_database.conf to describe the connections to your databases.

DQMonitor.jar
DQMonitor source files
DataQualityWebsite.jar
DataQualityWebsite source file

Java programs to generate scripts from source databases

You can download a tar or zip archive of java source files for TableDefinition, or access the individual files.

compressed tar file
zip file
Individual files

All you need to create a calendar dimension in your data warehouse.

These scripts are described in Data Warehouse Calendar Dimension. They work with PostgreSQL; you would need to tweak some of them a bit to make them work with other databases.

DashboardFile.java
GenerateCalendar.jar
GenerateCalendar.java
build_calendar.sql
calendar.csv
calendar.sql
load_calendar.sql
read_me.txt
set_public_holidays_2013.sql
set_public_holidays_2014.sql
set_public_holidays_2015.sql
set_public_holidays_2016.sql
set_public_holidays_2017.sql
set_public_holidays_2018.sql
set_public_holidays_2019.sql
set_public_holidays_2020.sql

All you need to create a time-of-day dimension in your data warehouse.

These scripts are described in Data Warehouse Time Dimension. They work with PostgreSQL; you would need to tweak some of them a bit to make them work with other databases.

DashboardFile.java
GenerateTimeDimension.jar
GenerateTimeDimension.java
build_time_of_day.sql
time_of_day.csv
time_of_day.sql
load_time_of_day.sql

Java runtime and source to analyse the character encoding of a file.

This program reads a file and writes the analysis to stdout.

The first parameter is the name of the file (the file path).

The second parameter can be set to "true" (or "yes") if you want line by line detail. The default is "false" so that you just get the summary.

How To Find Out What Character Encoding Is Used In a Particular File explains how to use this program, and gives several examples using real data.

EncodingProfile.jar
EncodingProfile.java

Bash script to clean control characters from a file.

The script reads the file that may contain control characters and outputs a new file with every control character (except carriage return and newline) replaced by a single space. You could easily tweak the script to remove carriage returns as well, or to preserve certain control characters.

clean_control_characters.bash

PostgreSQL user-defined function to verify the check-digit in a credit card number (or the number of any payment card). The check-digit is calculated using the Luhn algorithm. It can be useful as part of a check to ensure that credit card numbers have not been stored unencrypted by accident.

This function can be used in a SQL statement such as:

    select luhn_check_passed('7354896219449612');

In this example it returns false, but if you run it on a real credit card number it will return true.

Since the Luhn check-digit is a single digit with values 0-9, if you have a set of random numbers, you can expect 10% of them to pass the check. All payment card numbers do pass the check so you never get false negatives, but you can get false positives.

You could convert this function to Oracle PL/SQL or to T-SQL for Microsoft SQL Server or SAP Sybase SQL Server or to one of the IBM DB2 options.

luhn_check_passed.sql

PostgreSQL user-defined function to "standardise" a string as described in Standardise Text For Searching.

This function: removes spaces, punctuation and accents, replaces double characters with a single character and converts the resulting string to uppercase. See Standardise Text For Searching for effective ways to use this function.

You could convert this function to Oracle PL/SQL or to T-SQL for Microsoft SQL Server or SAP Sybase SQL Server or to any of the IBM DB2 options. Microsoft provides the translate function only in SQL Server 2017 and later. If you are using an earlier version you can implement the same functionality using nested replace() functions, but they are hard to write, much slower to run, and you have to be careful to do the translations in the right order. (For example, if you replace 'A' with 'B' and 'B' with 'C' you may end up translating 'A' to 'C' if you put the replace() functions at the wrong nesting levels.)

standard.sql