Junk DNA – Junk Oracle, part 0000003 …. DROP TABLE IF EXISTS

Almost every week I write or enhance software components that are deployed into a PROD environment. Every week, the full-fat versions of the underlying application is deployed into UAT, QA and other staging or test/integration environments prior to the PROD release. Many times the software changes involve database changes; back-population of data, changes to underlying PSM’s, new columns in existing database tables, new database tables and so on. The devil is always in the detail but the whole thing, understood by DevOps and developers alike, is embodied within a familiar CI/CD (Continuous Integration/Deployment) process.

If a show-stopper issue is raised in a UAT environment, the release can be pulled, or, if feasible, the faulty component or logic removed from the scheduled release. Of course, the fault can be remedied too; it is only too late once the PROD release has happened. UAT acceptance/sign-off, that is outside all the automated and integration testing, is more often than not performed by users bashing keyboards and confirming the software change meets their expectations.

Of course, CI/CD deployment has its own checks too. Should the unit tests fail, the scheduled deployment may be halted until the test “dashboard” is green; if the code does not compile, it will not be deployed, or if the deployment scripts result in error, this is clearly a showstopper too.

With respect to database scripts, that are usually not included in the codebase proper, the CD post UAT release deployment process normally involves a scrape of the deployment logs. If the scripts are expecting to create a new database table, there should be some sort of “checkout” script to confirm a PASS/FAIL that the new table is created. If the DDL used to create the new database table fails, the post execution script scraper will also grep out the error.

So, how do you create a new table in SQL? The answer is, irrespective of the RDBMS vendor, something not dissimilar to

create table foo(bar date);

The column bar above has data type date, but it might be something else, a VARCHAR, NUMBER with different precision or range, LOB of some flavour, and so on. Part of this consistency is that the SQL language is described in various standards – ANSI and ISO typically. The consistency means that an Oracle developer can be proficient with other DBMS vendor products such as SQL Server, MySQL and more, or technologies such as ORM can point to an arbitrary RDBMS without too much concern for subtle differences in the RDBMS SQL implementation. Put another way, the lingua franca of the relational database is SQL, but there are also variations in the SQL as implemented by different RDBMS vendors.

During the course of UAT rollout, and there may be one or more of these rollouts per day in the run-up to a software release, either

• the database must be torn down and rebuilt for each UAT release (resetting the database schema into a prior known state representative of the current PROD environment) and the database scripts executed
• the database must be rolled back to a snapshot prior to when the scripts were first executed
• the database scripts are be rerunnable and re-run over some prior deployment.

I would like to focus on the last approach. Firstly, what do I mean by re-runnable? What I mean is that when the database scripts are applied second and subsequent times, they do not error. After-all, if a database table is created, you cannot create it again, but having errors in the deployment logs will be treated as a deployment failure. A scenario that is to be avoided is an incorrect interpretation on whether the log file error is expected or not, only to find out on PROD release/rollout day, that the interpretation was wrong.

Oracle

For literally decades, I have been writing this type of re-runnable crap, and worse, in Oracle. Look at it, this isn’t SQL (DDL); the key part is nothing short of a hard-coded string without syntax checking. I include two variants of the code, as both are common; remember the goal is to execute the database scripts without error – CI/CD re-runnable scripts that do not produce errors.

The first example drops a table if it exists, and then creates it (often things are a lot more complicated than this trivial example due to existing data and FK constraints, or the introduction of FK, CHECK, calculated/virtual column formula etc.).

declare
begin
  for r in (
             select u.table_name
               from user_tables u
                 where u.table_name = 'FOO'
           ) loop
               execute immediate 'drop table foo';
             end loop;
end;
/

create table foo(bar date);

The second example uses exceptions, another common variant on this theme.

declare
begin 
   execute immediate 'drop table foo';
 exception
  when others then 
    if sqlcode = -942 
         then null; 
      else 
       raise;
    end if;
end;
/ 
create table foo(bar date);

SQL Server

I am currently skilling-up in SQL Server. It is a mature feature-rich fast database and it also now can be deployed on Linux (a month back  I demoed how easy it was to deploy SQL Server in Ubuntu Linux for example) and Windows.

How do you drop a table if and only if it exists in SQL Server, and then recreate it, for the purposes CI/CD deployment without execution log errors.

The code is below

drop table if exists foo;
create table foo(bar date);

Goodness me, well that grammar isn’t defined in the ISO/ANSI standards is it? It is however very concise, clear, and also unlikely to contain an error that the eye might miss on a casual glance. It is also very minimal – two commands, one to drop the table if and only if it already exists, without error, and the second command to create the table foo. It is committed to memory relatively quickly (I am sure you have already remembered this too!) unlike the sort of code written above to demo the same functionality in Oracle.

PostgreSQL

Hmmm, how about PostgreSQL? What would be the idiosyncratic syntax in PostgreSQL?

drop table if exists foo;
create table foo(bar date);

Well, that is a surprise. It is identical to SQL Server. Come to think of it, this should not come as a surprise to me. PostgreSQL is a snappy feature rich database too, and free, and adopts Standards defined SQL uncompromisingly. It also adopts the norm in other RDBMS implementations, where the language functionality is not defined in the Standards. Put another way, it has a good rep.

Faircom DB

How about something left-field like Faircom DB? I have been using Faircom products since Micro B+ in 1982. For readers of this blog that are not familiar with Faircom, the take home message is that for decades this company has been producing slick performant products with quality technical documentation (and what more can you want?). Faircom implement this exact syntax and grammar too. Again, it does not surprise me.

drop table if exists foo;
create table foo(bar date);

Summary

To delete and recreate a database table is no big deal. All RDBMS vendors offer the functionality. It is also embodied within the ISO/IEC/ANSI Standards (I bought a copy 5 years back when I previously blogged on another DB difference surprise).

Having to write lines and lines of boilerplate code to do simple things is both tedious and error prone. With Oracle however, it seems like this needs to be done very often. It feels like Oracle hasn’t moved on, hasn’t kept up with the times, and are unconcerned about their RDBMS flagship product having a very jurassic feel. Again, this argument is not about dropping and recreating a table, it is about the whole RDBMS implementation.

I think my core issue is one of embarrassment, and not for Oracle, but for myself. I am an Oracle developer through and through. To repeat a phrase I have mentioned a few times in various blog articles, I have backed the wrong horse and this is being reinforced every time I use a competitor product. I should know better.

Addendum (and where the Junk DNA reference in the blog post title is relevant)

It is my opinion that this DROP (or REPLACE) IF EXISTS implementation in Oracle (ie. lack of implementation) is another example of a dated RDBMS implementation littered with inconsistencies. In Faircom DB for example, IF EXISTS is consistently implemented not just for database tables, but across other objects too. To repeat my point again too, this is unrelated to ISO/EIC/ANSI standards adherence, but just big-picture consistency and keeping up with the times.

For Oracle, I have to write that boilerplate code above to drop a table to test whether it already exists or not prior to recreating the database table. The inconsistency is I can create or replace a function without this concern. A minimal PSM function returning a scalar value is shown below for example.

create or replace function baz return number as
begin
return 2;
end;
/

Again, with Faircom DB, I would execute DROP PROCEDURE IF EXISTS, consistent with the remainder of the SQL language implementation. I view this inconsistency similarly to how I view Junk DNA – detritus left from decades of evolution of the database implementation. This detritus, inconsistent grammar, syntax, and even data types, gives the Oracle RDBMS implementation the feel of a legacy product.

To fix all of this, if you consider it broken of course, and I do, would involve many breaking changes to the language.  Is that a bad thing? The answer to this question is simply if you don’t fix it now you will likely have to do it in the years/decades to come, if your product survives. Make your choice. Younger developers, technical managers, …., all fuelled by the bad press that is deserved here – we are not going to tolerate this crap for much longer. After all, there is nothing special about the Oracle database. It is just one of very many proven RDBMS implementations.

— Published by Mike, 00:13:11 01 Feb 2021 (GMT)