Database Changes Done Right

I love reading The Daily WTF dealing with programmers’ stories, experiences, and the occasional code nightmare. While it’s fun to read, it also makes you aware of how easily you can mistakes yourself, especially in an unfamiliar programming language, even after years of programming experience.

A recent article was titled Database Changes Done Right, and I was surprised that it was not fun or horror story, but rather a serious article on database change management, along with a couple of rules to watch when implementing database schema changes (yes, those ugly dev – test – production stages).

I also felt connected with the topic of the article, because for a couple of years now, I develop software that should help other developers ease this burden of managing database changes:

dbscript (blog, website, download) is a web application that stores database versions in repository (i.e. MSSQL database), calculates schema change scripts, stores definitions of table value initialization scripts and C# constant declarations, and keeps track of product version at customer installations. dbscript focuses on MS SQL Server databases in these areas, but also supports Oracle and PostgreSql databases for documentation purposes: document your database schema in a wiki or in a Word HTML file, or generate a data diagram with just a couple of mouse clicks.

SMOscript (blog, website, download) is a command-line tool that generates SQL scripts for all objects in a MSSQL database (tables, views, stored procedures, etc) to a single file, or to one file per object. These generates scripts can be kept in directories covered by source control systems (SVN, TFS, etc), and thus SMOscript simplifies storing the scripts for each database version in a source code repository.

oraddlscript (blog, website, download) is a command-line tool similar to SMOscript, but covering Oracle databases.

checktsql (blog, website, download) is a Windows tool that verifies all objects in a database (views, functions, stored procedures) by executing them or selecting from them (each in a transaction that will be rolled back), and reporting any errors that occurred during their invocation.

This product summary does not cover the complete functionality of each program. As programs evolve, they gain a lot of functionality, as users (including me!) add requirements and contribute ideas. Feel free to add yours!

DevExpress Linq to XPO and NULL values

DevExpress’ Linq To XPO has some known difficulties handling NULL values in .Where() conditions.

I came across them using code like this:

string Name = NullableString( [some input value] );
string Street = NullableString( [some input value] );

with NullableString() defined as

string NullableString(string s)
{
  if (string.IsNullOrEmpty(s))
    return null;
  return s.Trim();
}

and the query

var q = new XPQuery<Addresses>(session)
  .Where(a => a.Name == Name && a.Street == Street)
  .FirstOrDefault();

The resulting SQL statement looks like this:

Executing sql 'select * from (
  select N0."ID" as F0,N0."NAME" as F1,N0."STREET" as F2 
  from "ADDRESSES" N0 
  where ((N0."NAME" = :p0) and (N0."STREET" = null)) 
  where RowNum <= 1' with parameters {....}

This result surprised me (comparing null values using ‘=’ instead of IS NULL), especially since the DevExpress support center (Q100139, B94052)  indicates that NULL value handling has been fixed years ago.

Fortunately, another article in the support center provides an explanation and a workaround:

When the LINQ expression is processed by XPO, the value of the variable isn’t yet recognized and we can’t determine whether the variable has a value.

The workaround consists of checking each of the parameters for NULL, and comparing to the literal null value:

var q = new XPQuery<Addresses>(session)
  .Where(a => some not-null conditions);

if (Name == null)
  q = q.Where(a => a.Name == null);
else
  q = q.Where(a => a.Name == Name);

if (Street == null)
  q = q.Where(a => a.Street == null);
else
  q = q.Where(a => a.Street == Street);

Executing this Linq query generates the following statement:

Executing sql 'select * from (
  select N0."ID" as F0,N0."NAME" as F1,N0."STREET" as F2 
  from "ADDRESSES" N0 
  where ((N0."NAME" = :p0) and N0."STREET" is null) 
  where RowNum <= 1' with parameters {...}

just as you’d expect. Note that the combination of .Where() conditions is correctly translated into AND operations.

Another entry in the support center suggests that there won’t be a native solution:

We don’t see another solution, that can be implemented at the XPO level, without risk of introducing another bug. We have discussed this issue again, and come to the decision to not change anything.

 

oraddlscript 0.17

oraddlscript is a command-line utilty which generates CREATE scripts of database objects in an Oracle database using  the DBMS_METADATA.GET_DDL function.

The most recent update includes a separate executable using the DataDirect ADO.Net Data Provider to connect to an Oracle database (named oraddlscriptdd).

Command-line parameters and functionality are the same as in the original version.

The latest version of oraddlscript is available for download.

UPDATE and DELETE with JOINed Tables in Oracle

MS SQL Server allows UPDATE and DELETE statements to include a FROM … INNER JOIN … clause in the form

UPDATE Foo
SET blah = 1
FROM Foo INNER JOIN Bar ON Foo.ID = Bar.FooID
...

This syntax is not supported in Oracle databases. I found this blog which offers a different approach by updating a JOINed subselect:

UPDATE (SELECT blah FROM Foo INNER JOIN Bar ON Foo.ID = Bar.FooID
SET blah = 1

I found, however, that you need to have UPDATE grants on all joined tables or views. In my case, I needed to copy data from the meta-model views, such as USER_TAB_COLUMNS, which understandably do not allow updates.

The other solution I found was to retrieve the updating values via a subselect, such as:

UPDATE Foo
SET blah = (SELECT Bar.blah FROM Bar WHERE Foo.ID = Bar.FooID)

requiring a single-record result set for each of the updated records, and a subselect for each column.

The case of DELETing records based on a JOINed expression can be dealt with subselects, or the WHERE (…) IN (SELECT …) clause:

DELETE Foo
WHERE Foo.ID = (SELECT FooID FROM Bar ...)

or

DELETE Foo
WHERE ID IN (SELECT FooID FROM Bar...)

or

DELETE Foo
WHERE (ID, ID2) IN (SELECT FooID, FooID2 FROM Bar...)

Integrated Database Versioning and Documentation with dbscript 1.02

dbscript 1.02 adds support for edmx-based projects. If have previously written about generating documentation out of edmx files, and these documentation generators are now part of dbscript.

This is the updated table of Documentation Generators in dbscript 1.02:

Generated Documentation

	MS SQL Server	Oracle	PostgreSql	edmx
dbscript HTML	view	view	view
dbscript Single HTML	view	view	view	view view
HTML (single file)	view	view	view	view view
MediaWiki	view	view	view	view view
ScrewTurn Wiki (V2)	view	view	view
ScrewTurn Wiki (V3)	view	view	view
Word HTML	view	view	view	view view

The XSLT files on which the Documentation Generators depend for their functionality are now computed by a separate tool I wrote for this purpose, making the generated documentation more consistent throughout the different formats and databases.

Developers are free to adjust the shipped XSLT style sheets according to their layout and content needs.

Other features

• Data diagrams can now be restricted to a certain Object Selection resulting in a diagram showing only a part of the full model.
• Update notifications are implemented via JSONP
• Some fixes

dbscript is available for download here.

Selecting all Session Parameters from Oracle SYS_CONTEXT

Oracle databases provide the function SYS_CONTEXT which allow you to retrieve a set of session parameters via the namespace parameter ‘USERENV’. I created a SELECT statement to retrieve all the parameter values, text description is taken from this page at TECH on the Net:

SELECT 'AUDITED_CURSORID' AS Parameter, SYS_CONTEXT('USERENV','AUDITED_CURSORID') AS Value, 'Returns the cursor ID of the SQL that triggered the audit' AS Description FROM Dual
UNION ALL 
SELECT 'AUTHENTICATION_DATA' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') AS Value, 'Authentication data' AS Description FROM Dual
UNION ALL 
SELECT 'AUTHENTICATION_TYPE' AS Parameter, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') AS Value, 'Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy' AS Description FROM Dual
UNION ALL 
SELECT 'BG_JOB_ID' AS Parameter, SYS_CONTEXT('USERENV','BG_JOB_ID') AS Value, 'If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL.' AS Description FROM Dual
UNION ALL 
SELECT 'CLIENT_IDENTIFIER' AS Parameter, SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') AS Value, 'Returns the client identifier (global context)' AS Description FROM Dual
UNION ALL 
SELECT 'CLIENT_INFO' AS Parameter, SYS_CONTEXT('USERENV','CLIENT_INFO') AS Value, 'User session information' AS Description FROM Dual
UNION ALL 
SELECT 'CURRENT_SCHEMA' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS Value, 'Returns the default schema used in the current schema' AS Description FROM Dual
UNION ALL 
SELECT 'CURRENT_SCHEMAID' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SCHEMAID') AS Value, 'Returns the identifier of the default schema used in the current schema' AS Description FROM Dual
UNION ALL 
SELECT 'CURRENT_SQL' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_SQL') AS Value, 'Returns the SQL that triggered the audit event' AS Description FROM Dual
UNION ALL 
SELECT 'CURRENT_USER' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_USER') AS Value, 'Name of the current user' AS Description FROM Dual
UNION ALL 
SELECT 'CURRENT_USERID' AS Parameter, SYS_CONTEXT('USERENV','CURRENT_USERID') AS Value, 'Userid of the current user' AS Description FROM Dual
UNION ALL 
SELECT 'DB_DOMAIN' AS Parameter, SYS_CONTEXT('USERENV','DB_DOMAIN') AS Value, 'Domain of the database from the DB_DOMAIN initialization parameter' AS Description FROM Dual
UNION ALL 
SELECT 'DB_NAME' AS Parameter, SYS_CONTEXT('USERENV','DB_NAME') AS Value, 'Name of the database from the DB_NAME initialization parameter' AS Description FROM Dual
UNION ALL 
SELECT 'ENTRYID' AS Parameter, SYS_CONTEXT('USERENV','ENTRYID') AS Value, 'Available auditing entry identifier' AS Description FROM Dual
UNION ALL 
SELECT 'EXTERNAL_NAME' AS Parameter, SYS_CONTEXT('USERENV','EXTERNAL_NAME') AS Value, 'External of the database user' AS Description FROM Dual
UNION ALL 
SELECT 'FG_JOB_ID' AS Parameter, SYS_CONTEXT('USERENV','FG_JOB_ID') AS Value, 'If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.' AS Description FROM Dual
UNION ALL 
SELECT 'GLOBAL_CONTEXT_MEMORY' AS Parameter, SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') AS Value, 'The number used in the System Global Area by the globally accessed context' AS Description FROM Dual
UNION ALL 
SELECT 'HOST' AS Parameter, SYS_CONTEXT('USERENV','HOST') AS Value, 'Name of the host machine from which the client has connected' AS Description FROM Dual
UNION ALL 
SELECT 'INSTANCE' AS Parameter, SYS_CONTEXT('USERENV','INSTANCE') AS Value, 'The identifier number of the current instance' AS Description FROM Dual
UNION ALL 
SELECT 'IP_ADDRESS' AS Parameter, SYS_CONTEXT('USERENV','IP_ADDRESS') AS Value, 'IP address of the machine from which the client has connected' AS Description FROM Dual
UNION ALL 
SELECT 'ISDBA' AS Parameter, SYS_CONTEXT('USERENV','ISDBA') AS Value, 'Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.' AS Description FROM Dual
UNION ALL 
SELECT 'LANG' AS Parameter, SYS_CONTEXT('USERENV','LANG') AS Value, 'The ISO abbreviate for the language' AS Description FROM Dual
UNION ALL 
SELECT 'LANGUAGE' AS Parameter, SYS_CONTEXT('USERENV','LANGUAGE') AS Value, 'The language, territory, and character of the session. In the following format:language_territory.characterset' AS Description FROM Dual
UNION ALL 
SELECT 'NETWORK_PROTOCOL' AS Parameter, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') AS Value, 'Network protocol used' AS Description FROM Dual
UNION ALL 
SELECT 'NLS_CALENDAR' AS Parameter, SYS_CONTEXT('USERENV','NLS_CALENDAR') AS Value, 'The calendar of the current session' AS Description FROM Dual
UNION ALL 
SELECT 'NLS_CURRENCY' AS Parameter, SYS_CONTEXT('USERENV','NLS_CURRENCY') AS Value, 'The currency of the current session' AS Description FROM Dual
UNION ALL 
SELECT 'NLS_DATE_FORMAT' AS Parameter, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') AS Value, 'The date format for the current session' AS Description FROM Dual
UNION ALL 
SELECT 'NLS_DATE_LANGUAGE' AS Parameter, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') AS Value, 'The language used for dates' AS Description FROM Dual
UNION ALL 
SELECT 'NLS_SORT' AS Parameter, SYS_CONTEXT('USERENV','NLS_SORT') AS Value, 'BINARY or the linguistic sort basis' AS Description FROM Dual
UNION ALL 
SELECT 'NLS_TERRITORY' AS Parameter, SYS_CONTEXT('USERENV','NLS_TERRITORY') AS Value, 'The territory of the current session' AS Description FROM Dual
UNION ALL 
SELECT 'OS_USER' AS Parameter, SYS_CONTEXT('USERENV','OS_USER') AS Value, 'The OS username for the user logged in' AS Description FROM Dual
UNION ALL 
SELECT 'PROXY_USER' AS Parameter, SYS_CONTEXT('USERENV','PROXY_USER') AS Value, 'The name of the user who opened the current session on behalf of SESSION_USER' AS Description FROM Dual
UNION ALL 
SELECT 'PROXY_USERID' AS Parameter, SYS_CONTEXT('USERENV','PROXY_USERID') AS Value, 'The identifier of the user who opened the current session on behalf of SESSION_USER' AS Description FROM Dual
UNION ALL 
SELECT 'SESSION_USER' AS Parameter, SYS_CONTEXT('USERENV','SESSION_USER') AS Value, 'The database user name of the user logged in' AS Description FROM Dual
UNION ALL 
SELECT 'SESSION_USERID' AS Parameter, SYS_CONTEXT('USERENV','SESSION_USERID') AS Value, 'The database identifier of the user logged in' AS Description FROM Dual
UNION ALL 
SELECT 'SESSIONID' AS Parameter, SYS_CONTEXT('USERENV','SESSIONID') AS Value, 'The identifier of the auditing session' AS Description FROM Dual
UNION ALL 
SELECT 'TERMINAL' AS Parameter, SYS_CONTEXT('USERENV','TERMINAL') AS Value, 'The OS identifier of the current session' AS Description FROM Dual

Copy and paste into SQL Developer and execute 😉

Executing Oracle Stored Procedures using Entity Framework

In a previous post I discussed an XSLT file which generates code to execute Stored Procedures on an SQL Server database using Entity Framework.

DataDirect produces an Entity Framework provider for Oracle databases, and I found that the same XSLT file also works for edmx files generated by DataDirect. The only difference I found was that output parameters are declared as

@Mode=”InOut”

in SQL Server, but as

@Mode=”Out”

in Oracle. Therefore, some adjustments to the file are necessary.

In the first for-each statement on edm:Parameter

 <xsl:if test="position() != 1">, </xsl:if>
 <xsl:if test="@Mode = 'InOut' or @Mode = 'Out'">out </xsl:if>

in the second statement

 par.DbType = System.Data.DbType.<xsl:value-of select="@Type" />;<xsl:if test="@Mode = 'InOut'">
 par.Direction = System.Data.ParameterDirection.InputOutput;
 </xsl:if><xsl:if test="@Mode = 'Out'">
 par.Direction = System.Data.ParameterDirection.Output;</xsl:if>
 <xsl:if test="@Mode != 'InOut' and @Mode != 'Out'">

and

 <xsl:if test="@Mode = 'InOut' or @Mode = 'Out'">
 par.Value = System.DBNull.Value;
 </xsl:if>

in the third statement

<xsl:if test="@Mode = 'InOut' or @Mode = 'Out'">

Modify the original XSLT in Visual Studio, select your edmx file as Input File, and execute.

Generating a Stored Procedure Wrapper for NHibernate in PL/SQL

I discussed the execution of Oracle stored procedures via NHibernate in a previous post, and also the generation of the NHibernate .hbm.xml mapping files for Stored Procedures.

We use the same cursor declaration as in my post on generating the .hbm.xml file, and also the same parameter type conversion logic:

  csdatatype varchar2(50);
  args varchar2(500);

begin
  dbms_output.enable(1000000);
  dbms_output.put_line('using System.Collections;');
  dbms_output.put_line('using NHibernate;');
  dbms_output.put_line('');
  dbms_output.put_line('namespace MyDAL');
  dbms_output.put_line('{');
  dbms_output.put_line('    public static class Procedures');
  dbms_output.put_line('    {');

  for obj in cObjects loop
    args := '';

    for col in cColumns(obj.object_name) loop
      csdatatype := col.data_type;

      case col.data_type
        when 'NVARCHAR2' then
          csdatatype := 'string';
        when 'VARCHAR2' then
          csdatatype := 'string';
        when 'CHAR' then
          if col.char_length=1 then
            csdatatype := 'bool';
          else
            csdatatype := 'string';
          end if;
        when 'NUMBER' then
          if col.data_precision is null then
            csdatatype := 'long';
          elsif col.data_precision < 10 then
            csdatatype := 'int';
          else
            csdatatype := 'long';
          end if;
        else
          csdatatype := col.data_type;
      end case;

      args := args || ', ' || csdatatype || ' ' || col.argument_name;
    end loop;

After concatenating the parameter list for the C# method, we create an IQuery object, add parameters using the SetParameter<>() method, and return the result as an IList:

    dbms_output.put_line('        public static IList ' ||
      replace(initcap(obj.object_name), '_', '')
        || '(ISession session' || args || ')');
    dbms_output.put_line('        {');
    dbms_output.put_line('            IQuery sp = session.GetNamedQuery("'
        || replace(initcap(obj.object_name), '_', '') || '");');

    for col in cColumns(obj.object_name) loop
      csdatatype := col.data_type;

      case col.data_type
        when 'NVARCHAR2' then
          csdatatype := 'string';
        when 'VARCHAR2' then
          csdatatype := 'string';
        when 'CHAR' then
          if col.char_length=1 then
            csdatatype := 'bool';
          else
            csdatatype := 'string';
          end if;
        when 'NUMBER' then
          if col.data_precision is null then
            csdatatype := 'long';
          elsif col.data_precision < 10 then
            csdatatype := 'int';
          else
            csdatatype := 'long';
          end if;
        else
          csdatatype := col.data_type;
      end case;

      dbms_output.put_line('            sp.SetParameter<'
          || csdatatype || '>("' ||
        replace(initcap(col.argument_name), '_', '') || '", ' ||
        replace(initcap(col.argument_name), '_', '') || ');');
    end loop;

    dbms_output.put_line('            return sp.List();');
    dbms_output.put_line('        }');
  end loop;

  dbms_output.put_line('    }');
  dbms_output.put_line('}');

The IQuery object references the generated HBM declaration, and executes the PL/SQL code behind the sql-query statement

BEGIN MyStoredProc(params); END;

Generating a Stored Procedure Declarations for NHibernate in PL/SQL

I discussed the execution of Oracle stored procedures via NHibernate in a previous post, and the promise to be continued will now be fulfilled with the generation of the NHibernate mapping file for Stored Procedures.

We need two cursors, one iterating through all procedures, the second one to iterate through the parameter list of a given procedure:

cursor cObjects is
select object_type, object_name
from all_objects
where object_type in ('PROCEDURE')
and [your criteria here]
order by 1, 2;

cursor cColumns(oname in varchar2) is
select p.position, p.argument_name, p.data_type, p.default_length,
p.in_out, p.data_length, p.data_precision, p.data_scale, p.char_length
from all_arguments p
where p.object_name = oname
and [your criteria here]
order by p.position;

We need an XML header, and an sql-query element for each stored procedure:

  csdatatype varchar2(50);
  args varchar2(500);

begin
  dbms_output.enable(1000000);
  dbms_output.put_line('<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="protodal" namespace="protodal">');

  for obj in cObjects loop
    dbms_output.put_line('  <sql-query name="' ||
      replace(initcap(obj.object_name), '_', '') || '" callable="true">');

    args := '';

The most “difficult” part of the generator is to translate the PL/SQL data types into C#/.Net data types. This can be achieved in a CASE statement:

    for col in cColumns(obj.object_name) loop
      if (args is null) then
        args := '(:' || col.argument_name;
      else
        args := args || ', :' || col.argument_name;
      end if;

      csdatatype := col.data_type;
      case col.data_type
        when 'NVARCHAR2' then
          csdatatype := 'string';
        when 'VARCHAR2' then
          csdatatype := 'string';
        when 'CHAR' then
          if col.char_length=1 then
            csdatatype := 'bool';
          else
            csdatatype := 'string';
          end if;
        when 'NUMBER' then
          if col.data_precision is null then
            csdatatype := 'long';
          elsif col.data_precision < 10 then
            csdatatype := 'int';
          else
            csdatatype := 'long';
          end if;
        else
          csdatatype := col.data_type;
      end case;

      dbms_output.put_line('    <query-param name="' || col.argument_name ||
        '" type="' || csdatatype || '" />');
    end loop;

Args contains the list of parameters for each stored procedure, invoking it inside a BEGIN … END; clause:

    if (args is not null) then
      args := args || ')';
    end if;

    dbms_output.put_line('begin ' || obj.object_name || args || '; end;');
    dbms_output.put_line('  </sql-query>');
  end loop;

  dbms_output.put_line('</hibernate-mapping>');
end;

xxx

Code Generation using Oracle SQL*Plus

I mentioned the requirements for code generation by calling stored procedures from sqlplus in a previous post, but plus needs more fine-tuning to create nice source code files:

set serveroutput on format wrapped;

“format wrapped” maintains spaces in the output file.

set feedback off;

suppresses the message “PL/SQL procedure successfully completed” (found here)

set trimspool on;

trims trailing spaces in the spool file

set linesize 200;

sets maximum line length before wrapping to new line (max. 32767)

spool C:\path\to\my\output.txt

sets output file name (= spool file)

exec my_proc();

generate code using dbms_output.put_line() statements

spool off

end of code generation