Database Changes Done Right

March 12, 2012

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!

Advertisements

DevExpress Linq to XPO and NULL values

March 2, 2011

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

August 3, 2010

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

July 19, 2010

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

July 11, 2010

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

July 9, 2010

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

July 8, 2010

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.