The Data Studio

Code Samples

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 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.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 one of the IBM DB2 options. SQL Server does not have the translate function, so that will be an interesting problem to solve.

standard.sql