Category: Database Management

Fart-ing forward slashes

January 30, 2018 / colin / 0 Comments

I have a home-grown database build script that allows me to build multiple instances of my database.

The build scripts allow me to target a number of different environments (test, dev, etc) and perform upgrade or replace operations.

I use the wonderful FART utility to perform text substitution into templated script files, using placeholders delimited with “@@”. FART is a command-line utility developed by Lionello Lunesu. The name is an acronym for “Find And Replace Text”.

For example, consider the following template script, build.tpl:

create database @@DB_NAME@@
   containment = partial
   on primary 
   ( name       = '@@DB_NAME@@',
     filename   = '@@DATA_PATH@@@@DB_NAME@@.mdf',
     size       = 5120KB,
     filegrowth = 1024KB
   )
   log on
   ( name       = '@@DB_NAME@@_log',
     filename   = '@@LOG_PATH@@@@DB_NAME@@_log.ldf',
     size       = 1024KB,
     filegrowth = 10%
   )
   collate Latin1_General_CS_AS;
go

This template can be written out as a targeted script using a sequence of command-line instructions:

copy build.tpl build.sql
 
set SB_DB_NAME=MY_NEW_DB
set SB_DATA_PATH=c:\SQL\Data\
set SB_LOG_PATH=C:\SQL\Log\
 
fart   *.sql   @@DB_NAME@@      %SB_DB_NAME%
fart   *.sql   @@DATA_PATH@@    %SB_DATA_PATH%
fart   *.sql   @@LOG_PATH@@     %SB_LOG_PATH%

Running this yields the following output:

build.sql
Replaced 5 occurence(s) in 1 file(s).
 
build.sql
Replaced 1 occurence(s) in 1 file(s).
 
build.sql
Replaced 1 occurence(s) in 1 file(s).

And the contents of build.sql is now:

create database MY_NEW_DB
   containment = partial
   on primary 
   ( name       = 'MY_NEW_DB',
     filename   = 'C:\SQL\Data\MY_NEW_DB.mdf',
     size       = 5120KB,
     filegrowth = 1024KB
   )
   log on
   ( name       = 'MY_NEW_DB_log',
     filename   = 'C:\SQL\Log\MY_NEW_DB_log.ldf',
     size       = 1024KB,
     filegrowth = 10%
   )
   collate Latin1_General_CS_AS;
go

This is now ready to execute as part of the larger automated build process (this is a simplified example, obviously).

This is all fine and dandy until we try to target a brand new instance of SQL Server 2017 running on Linux. (Distribution of choice: Mint.) The Paths need to be changed to the Unix-style forward slashes:

set SB_DB_NAME=MY_NEW_DB
set SB_DATA_PATH=/var/opt/mssql/data/
set SB_LOG_PATH=/var/opt/mssql/data/

You’d think this would Just Work, but unfortunately we get an error:

build.sql
Replaced 5 occurence(s) in 1 file(s).
 
> fart *.sql @@DATA_PATH@@ %SB_DATA_PATH%
Error: invalid option -/
Error: invalid option -o
Error: invalid option -t
Error: invalid option -/
Error: invalid option -m
Error: invalid option -l
Error: invalid option -/
Error: invalid option -d
Error: invalid option -t
Error: invalid option -/

We have to escape the slashes, and also tell FART to use c-style extended characaters, with the -C switch:

set SB_DB_NAME=MY_NEW_DB
set SB_DATA_PATH=\/var\/opt\/mssql\/data\/
set SB_LOG_PATH=\/var\/opt\/mssql\/data\/
 
fart     *.sql   @@DB_NAME@@      %SB_DB_NAME%
fart  -C *.sql   @@DATA_PATH@@    %SB_DATA_PATH%
fart  -C *.sql   @@LOG_PATH@@     %SB_LOG_PATH%

This looks a bit odd, and the output to console includes a warning message:

Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
build.sql
Replaced 1 occurence(s) in 1 file(s).
 
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
Warning: unrecognized character escape sequence: \/
build.sql
Replaced 1 occurence(s) in 1 file(s).

However, the substitution did take place, and the result is correct:

create database MY_NEW_DB
   containment = partial
   on primary 
   ( name       = 'MY_NEW_DB',
     filename   = '/var/opt/mssql/data/MY_NEW_DB.mdf',
     size       = 5120KB,
     filegrowth = 1024KB
   )
.. etc

My guess is that this console warning is probably a bug in FART.

UPDATE: And someone else has reported it: #12 Warning: unrecognized character escape sequence: \/.

Flyway DB

June 12, 2017 / colin / 0 Comments

For years I’ve used a database build script framework written in-house to recompile all schema objects and run unit tests. However, the actual DDL code to migrate a schema from one version to the next was always left as a hand-rolled script that is executed up-front before the normal build process.

A colleague introduced me to Flyway DB and I am now a fan. The introduction, documentation, and tutorial material is top-notch.

Installing Flyway on Linux

Flyway DB can be found here:

https://flywaydb.org/

Although it has an API for integration, I specifically like the command-line utility version: https://flywaydb.org/getstarted/firststeps/commandline

Unzip the command-line tool version into your preferred program location. In my Linux VM, I decided on ~/bin:

and created a simlink to put flyway into the path:

$ ln -s ~/bin/flyway-4.2.0/flyway ~/bin/flyway

Now I can run it from any directory.

How Flyway works

Flyway scans the contents of a “migration scripts” folder using a file naming convention to understand which scripts belong to which specific version.

It then uses a special table in the target database called “schema_version” that contains metadata about the current version of the database.

Using Flyway

The basic syntax is

> flyway VERB

where VERB is one of:

migrate
clean
info
validate
baseline
repair

In our case, we need to:

“baseline” our database so that Flyway understands the current starting “version”;
write a DDL script to move the DB to the next version;
“migrate” our database instance to that version.

Setting up a working project directory

Create a working folder
copy the {install}/conf/flyway.conf file into the working directory, for project-specific overrides;
create a sub-folder called sql

The working directory I set up was:

/home/colin/Projects/flyway
/home/colin/Projects/flyway/sql

I copied the flyway.conf file, ready for local configuration.

Selecting the target DB

I’ve been working through the “MySQL Cookbook” tutorial, so I have a user and a database installed in the VM locally, called “cookbook”, with one table “limbs” already created.

Editing the .conf file:

flyway.url=jdbc:mysql://localhost:3306/cookbook
flyway.user=root
flyway.locations=filesystem:./sql

We could supply the password as well, but for interactive use it is safer to have flyway prompt for it. For automated processes, you’d want to put the password in the .conf file.

Note the syntax of specifying the location of the SQL migration scripts, with a “filesystem:” prefix.

Just for kicks, let’s run the “migrate” command:

$ flyway migrate
Flyway 4.2.0 by Boxfuse
 
Database password: ****
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
Successfully validated 0 migrations (execution time 00:00.003s)
ERROR: Found non-empty schema(s) `cookbook` without metadata table! Use baseline() or set baselineOnMigrate to true to initialize the
$

We see a couple of issues:

the target DB is missing the metadata table, and therefore Flyway doesn’t know what version it is;
we don’t have any upgrade scripts to process.

Baselining our target DB

We can update the .conf file to include information about the database. Let’s assume we are starting at version 5:

flyway.baselineVersion=5
flyway.baselineDescription=Baseline prior to Flyway

Now we can use the “baseline” command to create the metadata:

$ flyway baseline
Flyway 4.2.0 by Boxfuse
 
Database password: ****
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
Creating Metadata table: `cookbook`.`schema_version`
Successfully baselined schema with version: 5
$

Querying MySQL shows the table now exists:

mysql> show tables;
+--------------------+
| Tables_in_cookbook |
+--------------------+
| limbs              |
| schema_version     |
+--------------------+
2 rows in set (0.00 sec)

And the contents:

mysql> select * from schema_version;
+----------------+---------+--------------------------+----------+--------------------------+----------+--------------+--------------
| installed_rank | version | description              | type     | script                   | checksum | installed_by | installed_on
+----------------+---------+--------------------------+----------+--------------------------+----------+--------------+--------------
|              1 |       5 | Baseline prior to Flyway | BASELINE | Baseline prior to Flyway |     NULL | root         | 2017-06-12 14
+----------------+---------+--------------------------+----------+--------------------------+----------+--------------+--------------
1 row in set (0.00 sec)

We can use the “info” verb on the command-line to review the current version according to Flyway:

$ flyway info
Flyway 4.2.0 by Boxfuse
 
Database password: ****
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description              | Installed on        | State   |
+---------+--------------------------+---------------------+---------+
|       5 | Baseline prior to Flyway | 2017-06-12 14:23:41 | Baselin |
+---------+--------------------------+---------------------+---------+
$

Both schema and table name can be changed in the .conf, but it is probably better to leave it at default. For testing migrations on a freshly-built test database, there is a setting in the .conf that allows automatic default baselining if the schema table is not detected:

flyway.baselineOnMigrate=true

This can be very handy, along with specifying the DB user password, if security is not a priority.

Migrating the DB to a new version

Our current DB version is 5, but I’m going to create scripts for the versions either side of that, in order to see what Flyway does.

The script filenaming convention is documented here:

https://flywaydb.org/documentation/migration/sql

There are a number of possibilities including versioning scripts; repeatable scripts; and SQL callback scripts for invoking at various points during the process. For now, we’re just looking at versioning scripts.

Consider the following script files:

$ ls -l sql/*.sql
-rw-rw-r-- 1 colin colin 64 Jun 12 14:35 sql/V4__other_objects.sql
-rw-rw-r-- 1 colin colin 80 Jun 12 14:35 sql/V5__limbs.sql
-rw-rw-r-- 1 colin colin 62 Jun 12 14:36 sql/V6__test_table2.sql
$

Note: it may be tricky to remember that the name convention requires a double-underscore (“dunder”) between the version number and the file name!

Now, if we run the “info” verb:

Flyway 4.2.0 by Boxfuse
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
 
+---------+--------------------------+---------------------+---------+
| Version | Description              | Installed on        | State   |
+---------+--------------------------+---------------------+---------+
| 4       | other objects            |                     | <Baseln |
| 5       | Baseline prior to Flyway | 2017-06-12 14:23:41 | Success |
| 6       | test table2              |                     | Pending |
+---------+--------------------------+---------------------+---------+
$

Flyway detects this information from a) the script file naming convention, and b) the contents of the schema_version table in the target DB.

Questions:

Can we have more than one script belonging to a specific version?
If so, what is the execution sequence?
Can we have sub-versions (e.g. 5.1, 5.2 etc)?

The answer is that Flyway will throw an error if it detects two scripts associated with the same version number, so in fact you MUST use sub-version numbers for multiple scripts, like this:

V6_0__test_table2.sql
V6_1_1_test_table1.sql
V6_1_2__test_table3.sql

Of course, this now dictates the execution sequence:

$ flyway info
Flyway 4.2.0 by Boxfuse
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description              | Installed on        | State   |
+---------+--------------------------+---------------------+---------+
| 4       | other objects            |                     | <Baseln |
| 5       | Baseline prior to Flyway | 2017-06-12 14:23:41 | Success |
| 6.0     | test table2              |                     | Pending |
| 6.1.1   | test table1              |                     | Pending |
| 6.1.2   | test table3              |                     | Pending |
+---------+--------------------------+---------------------+---------+

Now, let’s migrate!

$ flyway migrate
Flyway 4.2.0 by Boxfuse
 
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
Successfully validated 5 migrations (execution time 00:00.018s)
Current version of schema `cookbook`: 5
Migrating schema `cookbook` to version 6.0 - test table2
Migrating schema `cookbook` to version 6.1.1 - test table1
Migrating schema `cookbook` to version 6.1.2 - test table3
Successfully applied 3 migrations to schema `cookbook` (execution time 00:00.115s).
$
 
mysql> show tables;
+--------------------+
| Tables_in_cookbook |
+--------------------+
| limbs              |
| schema_version     |
| test1              |
| test2              |
| test3              |
+--------------------+
5 rows in set (0.00 sec)
 
mysql> select * from schema_version;
+----------------+---------+--------------------------+----------+--------------------------+-------------+--------
| installed_rank | version | description              | type     | script                   | checksum    | install
+----------------+---------+--------------------------+----------+--------------------------+-------------+--------
|              1 | 5       | Baseline prior to Flyway | BASELINE | Baseline prior to Flyway |        NULL | root
|              2 | 6.0     | test table2              | SQL      | V6_0__test_table2.sql    |   224077121 | root
|              3 | 6.1.1   | test table1              | SQL      | V6_1_1__test_table1.sql  |   669488585 | root
|              4 | 6.1.2   | test table3              | SQL      | V6_1_2__test_table3.sql  | -1574979834 | root
+----------------+---------+--------------------------+----------+--------------------------+-------------+--------
4 rows in set (0.00 sec)

And now:

$ flyway info
Flyway 4.2.0 by Boxfuse
  
Database: jdbc:mysql://localhost:3306/cookbook (MySQL 5.7)
+---------+--------------------------+---------------------+---------+
| Version | Description              | Installed on        | State   |
+---------+--------------------------+---------------------+---------+
| 4       | other objects            |                     | <Baseln |
| 5       | Baseline prior to Flyway | 2017-06-12 14:23:41 | Success |
| 6.0     | test table2              | 2017-06-12 15:01:22 | Success |
| 6.1.1   | test table1              | 2017-06-12 15:01:22 | Success |
| 6.1.2   | test table3              | 2017-06-12 15:01:22 | Success |
+---------+--------------------------+---------------------+---------+

It’s pretty cool!

Enterprise Architect: Importing or Refreshing DB schemas

July 19, 2016 / colin / 0 Comments

You will need:

a DB instance to read the schema from
an Oracle ODBC driver that EA can use
an ODBC configuration pointing to the DB source schema
user credentials for the DB source schema
an EA project to import into

Oracle DB instance

We could point at the Production system, but this is not a great idea, especially when you’re testing. I use a local virtual machine (running Oracle Linux 6.6) with an instance of Oracle Database 12c. This is where I do all my test builds, pulled from the latest branch in source control.

Oracle ODBC Driver

I’m going to assume you’ve got this covered… otherwise, this is a good starting reference:

How to install ODBC driver for Oracle in Windows 7

(We could try using the default Microsoft ODBC Driver for Oracle, but I’ve never got it to work.)

ODBC and Enterprise Architect

My local operating system is 64-bit Windows. However, my installation of Enterprise Architect is 32-bit. This means that it will use the 32-bot ODBC components, and I need to use the 32-bit ODBC Data Source Administrator to configure a data source (DSN).

How can I open the 32-bit ODBC Data Source Administrator on Windows 7 64?

And if Life weren’t complicated enough:

ODBC Administrator tool displays both 32-bit and 64-bit user DSNs in a 64-bit version of WIndows

But, the bottom line is:

On a 64bit machine when you run “ODBC Data Source Administrator” and created an ODBC DSN, actually you are creating an ODBC DSN which can be reachable by 64 bit applications only.

But what if you need to run your 32bit application on a 64 bit machine ? The answer is simple, you’ll need to run the 32bit version of “odbcad32.exe” by running “c:\Windows\SysWOW64\odbcad32.exe” from Start/Run menu and create your ODBC DSN with this tool.

Got that?

64-bit ODBC Administrator: c:\windows\System32\odbcad32.exe
32-bit ODBC Administrator: c:\windows\SysWOW64\odbcad32.exe

It’s mind-boggling. The UI looks identical too.

Configuring a Data Source (DSN)

Run the 32-bit ODBC Administrator
Select User DSN
Click on “Add…”
Select the Oracle driver:

Click “Finish”

We’re not finished. We need to enter some details:

The only critical parameter here is the TNS Service Name which needs to match whatever you’ve set up in the TNSNAMES.ORA config file, for your target DB instance.

Here, I’ve used a user name of SYSTEM because this is my test Oracle instance. Also, it will allow me to read from any schema hosted by the DB, which means I can use the DSN for any test schema I build on the instance.

Now that the DSN is created, we can move on to working in Enterprise Architect.

Case Study: Importing into a clean project

Note: I’m using images from a Company-Internal How-To guide that I authored. I feel the need to mask out some of the details. Alas I am not in a position to re-create the images from scratch. I debated omitting the images entirely but that might get confusing.

In Enterprise Architect, we have a clean, empty project, and we didn’t use Wizards to create template objects. It is really just a simple folder hierarchy:

Right-click on the folder and select from the cascading drop-down menu of options:

Code Engineering > Import DB schema from ODBC

This will bring up the Import DB schema from ODBC source dialog.

Click on the chooser button on the right side of the “Database” field to bring up the ODBC Data Source chooser:

The DSN we created should be available under the Machine Data Source tab. Select it, and click OK. We should be prompted to enter a password for our pre-entered User Name, after which Enterprise Architect will show us all the schemas to which we have access on the DB:

Previously, on this instance, I ran the database build scripts and created a set of test schemas using the T_ name prefix.

We’re going to import the contents of the T_IHD schema into our project, so we check the “T_IHD” schema name.

Our intention is to import (create) elements under the package folder, for each table in the T_IHD schema.

Review the filter options carefully!

Enterprise Architect Help : Import Database Schema

The default settings are probably correct, if we are only interested in creating elements for each table. Note the Synchronization options:

The package folder is empty, so you might think that we need to change this to “Import as New objects”. Don’t worry. New objects will be created if they don’t already exist.

Now click on “Import” button at the top right of the dialog. After a sort wait, during which EA is retrieving metadata from the database, the contents of the T_IHD schema will be presented to us in the “Select Database Objects to Import” dialog:

We are only interested in the tables, so check the [x] Tables checkbox and all the contained tables will be selected.

Are there objects we don’t need to import?

Often there are objects we’re not interested in including in the Data Model. For example, the TEMP_EVENT table might only be used as an interim location for data, during some business process, and not worth complicating the model with.

We can clear the checkbox next to the TEMP_EVENT table name to skip it.

Now, having validated our selection, we can press the OK button to start the import.

The process may take some time… when it completes, we can close the Import dialog, or select another schema and destination folder and import a different schema.

The table elements should now be visible in the Project hierarchy:

Case Study: Refreshing a schema

We need to refresh the RIX schema tables in the model. This schema is already in the model, with lots of additional element and attribute-specific notes that we need to retain.

Two important things to note:

The schema contents are organized differently (temp tables were imported into the model but then moved into a sub-folder);
There are some minor structure changes to columns, in the source database

We need to refresh the schema in the model, with the new structure from the DB. The process to follow is almost exactly the same as the clean import described earlier:

Select the package folder

Right Click > Code Engineering > Import the DB schema
select the T_RIX schema
Make sure the Synchronization options are “(o) Synchronize existing classes” and “[ ] Overwrite object comments”. We do not want to remove any existing notes entered against elements and attributes in the model.

Now, in this example we are only interested in refreshing those tables that are already in the model, in the current folder. That represents a subset of the total set of tables that are in the current DB schema:

In the image above, LOG_CTL is a table we don’t care about (not in the model); and the T_* tables are temporary tables that, in the model, are in a different folder. We’ll do those next (see below).

After the import process is completed, we should be able to drill down and see the schema changes reflected in the updated model.

We can repeat the process for the Temp Tables sub-package, this time selecting only the T_* temporary tables in the import selection.

Summary:

Table objects in the model might be moved around into different folders, per type, for documentation purposes.
The schema import process wants to import all selected tables into the current folder
Therefore, if you have separated out tables in a single schema, you should refresh them on a folder-by-folder basis, selecting only the objects you want to be in each folder in the model.

With careful set-up, it is possible to import and refresh table structures from database schemas into a project in Enterprise Architect, without over-writing existing documentation and attribute notes.