table inheritance

In this article I'm going to lay down the foundation for the Nordic Object/Relational Database Design, using cascading custom class tables.  This particle pattern uses a concrete table for each class. 

I learned of this design many years ago through <a href="http://www.sqlserverbible.com/">Paul Nielsen's</a> article <a href="http://msdn.microsoft.com/en-us/library/bb245675.aspx">The Nordic Object/Relational Database Design</a> which is an excellent read for the gritty details.

The heart of the cascading custom class pattern is a base table and a type table.  The type table will have an entry for each concrete class type (think leaves of the inheritence graph).  The base table stores the type and provides the identity ID used for the one-to-one relationship that join the records across the tables in the inheritence graph of a class's records.  The type diambiguates the class type of the root and node tables of the inheritence graph can be of different types.

The names of tables are arbitrary, in this case item and itemType.  Here's the bootstrap for these two tables:

/* caption is not required but provides a common place to store a human friendly representation */
create table dbo.item (
    id bigint identity(1,1) primary key,
    itemTypeId bigint,
    caption varchar(255) not null
);

create table dbo.itemTypeAbstract (
    id bigint primary key,
    schema_name sysname,
    table_name sysname,
    constraint [fk_itemType_item_id] foreign key (id) references dbo.item(id)
)

insert into dbo.item (caption, itemTypeId) values ('Item Type', 1);
insert into dbo.itemTypeAbstract (id, schema_name, table_name) values (1, 'dbo', 'itemType');

alter table dbo.item
add constraint [fk_item_itemType] foreign key (itemTypeId) references dbo.itemType(id);

go 

Table 'itemType' itself is a subclass of 'item'.  Note 'caption' in root table dbo.item;  All sub-class tables will inherit this field (along with id and itemTypeId).

As of now it takes two inserts to get any values into the concrete class itemType (a row in item and a row in itemType).  The proper set of 'instead of' triggers will allow the view to become fully CRUDable, but first a view is needed:

create view dbo.itemType with schemabinding as
select
    item.id, item.typeId, item.caption,
    itemType.schem_name, itemType.table_name
from
    dbo.itemTypeAbstract as itemType
    inner join dbo.item as item on item.id = itemType.id

create unique clustered index on dbo.itemType (id);

You may or may not want the clusterd index;  If operations are read heavy, the clustered index will materialize the view and make reads much faster at the cost of more expensive DML actions. 

select * from dbo.itemType;

id	typeId	caption	schema_name	table_name
1	1	Item Type	dbo	itemType

I'll cover the instead of triggers in a future article;  These triggers provide the insert, update, and delete directly against the view.

t-sql: univsersal drop procedure

A very common t-sql code pattern is the 'check if exists and drop' snippet:

IF Object_id('dbo.DropObject') IS NOT NULL
  DROP PROCEDURE dbo.dropobject;
go

 Procedure dbo.DropObject handles checking to see if the object exists, and also creates the proper statement to drop the object based on the object's type.

The procedure's signature is thus:

CREATE PROCEDURE dbo.Dropobject (@object NVARCHAR(400),
                                 @type   SYSNAME = '')
AS

The object is found using built-in Object_id.  If needed to disambiguate, the type can be specified as defined in the 'type' column of sys.objects.  If foreign key constraints are preventing a table drop, they will be listed.  This leads to helpful function #2:

CREATE PROCEDURE dbo.Dropreferingfks(@table NVARCHAR(256))

As expected, this function will remove any foreign keys referencing a table, which will allow for the table to be dropped.

IF Object_id('dbo.DropObject') IS NOT NULL
  DROP PROCEDURE dbo.dropobject;

go

CREATE PROCEDURE dbo.Dropobject (@object NVARCHAR(400),
                                 @type   SYSNAME = '')
AS
  BEGIN
      DECLARE @qschema SYSNAME,
              @qname   SYSNAME;
      DECLARE @schema SYSNAME,
              @name   SYSNAME;
      DECLARE @id INT;

      IF @type = ''
        SET @id = Object_id(@object)
      ELSE
        SET @id = Object_id(@object, @type)

      IF @id IS NOT NULL
        SELECT @qname = Quotename(sys.objects.name),
               @qschema = Quotename(sys.schemas.name),
               @name = sys.objects.name,
               @schema = sys.schemas.name,
               @type = sys.objects.type
        FROM   sys.objects
               INNER JOIN sys.schemas
                       ON sys.objects.schema_id = sys.schemas.schema_id
        WHERE  sys.objects.object_id = @id
               AND ( @type = ''
                      OR @type = sys.objects.type );

      IF @id IS NULL
        BEGIN
            SELECT @id = object_id,
                   @qname = Quotename(name),
                   @type = 'TD'
            FROM   sys.triggers
            WHERE  name = @object
                   AND parent_class = 0
                   AND @type IN ( '', 'TD' );
        END

      IF @id IS NULL
        BEGIN
            EXEC dbo.Log
              'dbo',
              'DropObject',
              'W',
              'Did not find object "',
              @object,
              '" of type "',
              @type,
              '"';

            RETURN;
        END

      IF @qname IS NULL
        BEGIN
            EXEC dbo.Log
              'dbo',
              'DropObject',
              'W',
              'Found id ',
              @id,
              ' but no object';

            RETURN;
        END

      EXEC dbo.Log
        'dbo',
        'DropObject',
        'V',
        'Found ',
        @qschema,
        '.',
        @qname,
        ' type ',
        @type;

      DECLARE @sql NVARCHAR(max);

      IF @type = 'D '
        SET @sql = '';

      IF @type = 'F '
        SELECT @sql = 'alter table '
                      + Quotename(ctu.table_schema) + '.'
                      + Quotename(ctu.table_name)
                      + ' drop constraint '
                      + Quotename(ctu.constraint_name)
        FROM   information_schema.constraint_table_usage AS ctu
        WHERE  constraint_name = @name

      IF @type = 'FN'
        SET @sql = 'drop function ' + @qschema + '.' + @qname

      IF @type = 'FS'
        SET @sql = 'drop function ' + @qschema + '.' + @qname

      IF @type = 'IF'
        SET @sql = '';

      IF @type = 'IT'
        SET @sql = '';

      IF @type = 'P '
        SET @sql = 'drop procedure ' + @qschema + '.' + @qname

      IF @type = 'PC'
        SET @sql = '';

      IF @type = 'PK'
        SET @sql = '';

      IF @type = 'S '
        SET @sql = '';

      IF @type = 'SQ'
        SET @sql = '';

      IF @type = 'TD'
        SET @sql = 'drop TRIGGER ' + @qname + ' ON DATABASE';

      IF @type = 'TF'
        SET @sql = 'drop function ' + @qschema + '.' + @qname;

      IF @type = 'TR'
        SET @sql = 'drop trigger ' + @qschema + '.' + @qname

      IF @type = 'U '
        BEGIN
            --check for foreign key relationships to table
            DECLARE @ot NVARCHAR(max);

            SELECT
            --pk.TABLE_SCHEMA as pkTableSchema, pk.TABLE_NAME as pkTableName, fk.TABLE_SCHEMA as pkTableSchema, fk.TABLE_NAME as pkTableName
            @ot = Isnull(@ot + ', ' + Quotename(fk.table_schema) + '.'
                         + Quotename(fk.table_name),
                  Quotename(fk.table_schema) + '.'
                  + Quotename(fk.table_name))
            FROM   information_schema.referential_constraints AS rc
                   INNER JOIN information_schema.constraint_table_usage AS pk
                           ON pk.constraint_schema = rc.unique_constraint_schema
                              AND pk.constraint_name = rc.unique_constraint_name
                   INNER JOIN information_schema.constraint_table_usage AS fk
                           ON fk.constraint_schema = rc.constraint_schema
                              AND fk.constraint_name = rc.constraint_name
            WHERE  pk.table_schema = @schema
                   AND pk.table_name = @name;

            IF @ot IS NOT NULL
              BEGIN
                  SET @ot = @qschema + '.' + @qname
                            + ' is referenced by table(s) ' + @ot;

                  EXEC dbo.Log
                    'dbo',
                    'DropObject',
                    'E',
                    'Drop fails; ',
                    @ot;

                  RETURN;
              END

            SET @sql = 'drop table ' + @qschema + '.' + @qname
        END

      IF @type = 'UQ'
        SET @sql = '';

      IF @type = 'V '
        SET @sql = 'drop view ' + @qschema + '.' + @qname

      IF @sql = ''
        BEGIN
            EXEC dbo.Log
              'dbo',
              'DropObject',
              'W',
              'No drop command defined for ',
              @type;

            RETURN;
        END

      EXEC dbo.Log
        'dbo',
        'DropObject',
        'V',
        'Executing "',
        @sql,
        '"';

      EXEC Sp_executesql
        @statement = @sql;

      EXEC dbo.Log
        'dbo',
        'DropObject',
        'I',
        'Dropped ',
        @qschema,
        '.',
        @qname,
        ' type ',
        @type;
  END

go

EXEC Dropobject
  'dbo.dropReferingFKs';

go

CREATE PROCEDURE dbo.Dropreferingfks(@table NVARCHAR(256))
AS
  BEGIN
      DECLARE @ot NVARCHAR(max) = ''
      DECLARE @schema SYSNAME,
              @name   SYSNAME;
      DECLARE @id  INT,
              @cnt INT;

      SET @id = Object_id(@table, 'U')

      IF @id IS NULL
        BEGIN
            EXEC dbo.Log
              'dbo',
              'dropReferingFKs',
              'W',
              'Did not find user table "',
              @table;

            RETURN;
        END

      SELECT @name = sys.objects.name,
             @schema = sys.schemas.name
      FROM   sys.objects
             INNER JOIN sys.schemas
                     ON sys.objects.schema_id = sys.schemas.schema_id
      WHERE  sys.objects.object_id = @id;

      SELECT
      --pk.TABLE_SCHEMA as pkTableSchema, pk.TABLE_NAME as pkTableName, fk.TABLE_SCHEMA as fkTableSchema, fk.TABLE_NAME as fkTableName
      @ot = @ot + 'exec dropObject '
            + Quotename(Quotename(rc.constraint_name), '''')
            + ';  '
      FROM   information_schema.referential_constraints AS rc
             INNER JOIN information_schema.constraint_table_usage AS pk
                     ON pk.constraint_schema = rc.unique_constraint_schema
                        AND pk.constraint_name = rc.unique_constraint_name
             INNER JOIN information_schema.constraint_table_usage AS fk
                     ON fk.constraint_schema = rc.constraint_schema
                        AND fk.constraint_name = rc.constraint_name
      WHERE  pk.table_schema = @schema
             AND pk.table_name = @name;

      SET @cnt = @@ROWCOUNT;

      IF @cnt = 0
        BEGIN
            EXEC dbo.Log
              'dbo',
              'dropReferingFKs',
              'V',
              'Did not find any foreign keys referencing ',
              @schema,
              '.',
              @name;

            RETURN;
        END

      EXEC dbo.Log
        'dbo',
        'dropReferingFKs',
        'I',
        'Dropping ',
        @cnt,
        ' foreign key(s) referencing ',
        @schema,
        '.',
        @name;

      EXEC dbo.Log
        'dbo',
        'dropReferingFKs',
        'V',
        'Preparing to sp_exec: ',
        @ot;

      EXEC Sp_executesql
        @ot;
  END

go 

t-sql logging

This is post one of a long series of SQL Server posts (t-sql) that will culminate in a CRUD'able schema and an automated Nordic database structure.  Eventually all of the code will be available as an open source project on github.

The Nordic database structure leads to some very interesting capabilities;  One of my favorites is universal directed graph relationships between any tables/rows, with the options to config what type of nodes an edge is allowed to connect.  This is some very powerful stuff.

The first couple of articles are going to build up the helper utilities and functions that I tend to use everywhere. I tend to write my t-sql code in such a way that any given script file can be ran at any time.  The scripts drop all the objects that will be created first and then recreates them. 

The first function is a generalized logging function with configurable output level (error, warning, info).  All logging is also written to a log table (dbo.metaLog).  The output level is persisted using SET context_info. 

We'll end up with two functions: dbo.SetLogOutputLevel and dbo.Log

IF Object_id('dbo.SetLogOutputLevel') IS NOT NULL
  DROP PROCEDURE dbo.setlogoutputlevel;

go

--set and persist debug output level use by sp dbo.log
CREATE PROCEDURE dbo.Setlogoutputlevel(@level CHAR(1))
AS
  BEGIN
      DECLARE @BinVar VARBINARY(128);

      SET @BinVar = Cast(@level AS VARBINARY(128));
      SET context_info @BinVar;
  END

go

EXEC dbo.Setlogoutputlevel
  'W';

go

--if not present create log table used by dbo.log
IF Object_id('dbo.metaLog') IS NULL
  CREATE TABLE dbo.metalog
    (
       id       BIGINT IDENTITY(1, 1) PRIMARY KEY,
       dt       DATETIME DEFAULT(Getdate()),
       who      SYSNAME DEFAULT SYSTEM_USER,
       [level]  CHAR(1) DEFAULT('I'),
       detail   NVARCHAR(max),
       [schema] SYSNAME,
       [name]   SYSNAME,
       [sql]    NVARCHAR(max) NULL
    )

go

IF Object_id('dbo.log') IS NOT NULL
  DROP PROCEDURE dbo.log;

go

--universal log function
--print based on log level found in CONTEXT_INFO()
--insert all logs to dbo.metLog table
--use like:  exec dbo.log 'dbo', 'DropObject', 'W', 'Did not find object "', @object, '" of type "', @type, '"';
CREATE PROCEDURE dbo.Log (@schema SYSNAME='',
                          @name   SYSNAME='',
                          @level  CHAR(1)='',
                          @detail NVARCHAR(max)='',
                          @d1     NVARCHAR(max)='',
                          @d2     NVARCHAR(max)='',
                          @d3     NVARCHAR(max)='',
                          @d4     NVARCHAR(max)='',
                          @d5     NVARCHAR(max)='')
AS
  BEGIN
      SET nocount ON;

      DECLARE @l CHAR(1);

      SELECT @l = Cast(Context_info() AS CHAR(1));

      -- if context_info value is non sensical use most verbose 'I'
      IF @l NOT IN ( 'E', 'W', 'I', 'V', 'S' )
        BEGIN
            --exec dbo.SetLogOutputLevel 'V';
            SELECT @l = 'I';
        END

      DECLARE @mess NVARCHAR(max);

      SET @mess = @detail + Isnull(@d1, '') + Isnull(@d2, '')
                  + Isnull(@d3, '') + Isnull(@d4, '')
                  + Isnull(@d5, '');

      DECLARE @sql NVARCHAR(max);

      IF Object_id('dbo.metaLog') IS NOT NULL
        INSERT INTO dbo.metalog
                    ([schema],
                     name,
                     [level],
                     detail)
        VALUES      (@schema,
                     @name,
                     @level,
                     @mess);
      ELSE
        PRINT '***table dbo.metaLog not present';

      IF @l = 'S'
        RETURN;

      IF @level LIKE 'E'
        PRINT '*** ERROR:  ' + Isnull(@schema + '.', '')
              + @name + ': ' + @mess;

      IF @level LIKE 'W'
         AND @l IN ( 'W', 'I', 'V' )
        PRINT ' ** WARN :  ' + Isnull(@schema + '.', '')
              + @name + ': ' + @mess;

      IF @level LIKE 'I'
         AND @l IN ( 'I', 'V' )
        PRINT '  * INFO :  ' + Isnull(@schema + '.', '')
              + @name + ': ' + @mess;

      IF @level LIKE 'V'
         AND @l = 'V'
        PRINT '    All  :  ' + Isnull(@schema + '.', '')
              + @name + ': ' + @mess;
  END

go