Where is my database? 

I have been working on the DBTD_ASSERT_DB_EXISTS assert for SQL Server lately, to keep functionality compatible we have ventured in to creating compatible logic for Netezza and Oracle, and that is where database engine architecture pushed us in to rough territory.

Because the question is: Where is my database?

Architecture

The cave paintings are probably the earliest created by human databases. Back then data were stored data on the media called – the “Rock”. There were a lot of rocks to store a data on, the indigenous pupil would say “let’s go draw something on Our Rock” and his fearsome friend will reply “How about Cave by the river”. Even back in those times, databases had names known to everyone in a tribe. In nova days we have quite improved our data storage facilities and data delivery systems, but in general the “database” term still means a “collection of data” and as long as you are able to connect to it (and get data from there) everyone is fine. Even if you venture into ANSI SQL stands book for Structured Query Language you will not find set-in-stone definition of the “the Database” concept as container of schemas or object.

Putting people aside (because it is clear for us what database is) let’s look on how different product manufactures use this term:

Oracle

Oracle Architecture

From very simplified architectural point have Oracle following necessary components:

  • Host – a hardware or virtualized computer (server);
  • Oracle Database Instance or just Oracle database. Each instance can be split in to following two parts:
    • The Oracle Instance – is the set or application and processes to manage the database, it is identified by individual SID (or Oracle System ID)
      Multiple instances can be installed on an individual host
    • The Oracle Database - is identified by Global Database Name and represents a unique collection of files and settings that define characters set, security, connectivity, memory allocation and disk usage, and many other parameters that eventually control how different database objects (tables, views, indexes, packages etc.) are created, used and accessed.

A few extra important comments:

  • Multiple instances of oracle can be installed on one host, as long as there is enough resources on the host
  • In general there is one database per Oracle instance
  • Database has its own schemas, users, tables, and many other database objects
  • Permission managed on the database/instance level
  • Future Oracle products claim to support multi database per single host configurations, so it is coming to the market
  • Many oracle developers might never even use CREATE DATABASE statement, and there is a good set of reasons for it

 

SQL Server

SQL Server Architecture

In the SQL Server world when you have installed SQL Server – you got yourself environment to manage as many databases as you need. Here are a few SQL Server components:

  • Host - a hardware or virtualized computer (server)
  • Database Engine Instance – is the set of programs and services that manage a system databases and one or more user databases. Each instance would have following components:
    • SQL Server Engine Services – set of core programs and services;
    • Built In System Databases: Master, Model, MSDB, TempDB – used internally by SQL Server Engine Instance;
    • User Databases – user defined databases.

A few important comments:

  • One host can run multiple SQL Server Engine Instances, as long as there is enough resources on the host
  • There are many databases per SQL Server Engine Instances
  • Each Database has its own schemas, users, tables, and many other database objects
  • Permission managed on the database level

Netezza

Netezza Architecture

When you work with Netezza – you have got yourself appliance to manage databases.

Here are the few components of Netezza appliance:

  • Factory configured Netezza data warehouse appliance – the hardware and software
  • Set of built in super users to manage hardware, software and databases
  • Databases

A few important comments:

  • Like refrigerator to store groceries, Netezza would store databases. Appliance has been architected in a way to compliment hardware and database engine software to get the better of two words
  • There could be many databases created per appliance, as long as there are enough resources
  • Each Database has its own tables, views, procedures and functions, sequences, and libraries
  • Permissions are managed on the appliance level
  • There are no individual users in the database

 

What about our question now?

Assuming DBTD_ASSERT_DB_EXISTS assert stored procedure should let us know if database with the given name exists or not we are running in to bit of issue here with the question itself.

While questioning existence of the database in Netezza and SQL Server can provide us with meaningful answer. In the Oracle, however, such a question will have a little sense. Hopefully not for long, or at list until we will get to the future Oracle versions that will support so called CDB  or “Container Database” and PDB or “Pluggable Database”.

For now Oracle developers rather would want to check: DB_NAME, DB_UNIQUE NAME, HOST, INSTANCE, or INSTANCE_NAME along other specific database identification parameters.

Reflection on the History

Caveman DBA 1:    “Let’s go and draw a new record on that “Rock-by-the-river”
Caveman DBA 2:    “Hm… Sure …”
Caveman DBA  3:   "What river?” 

 

Thx
Alex Podlesny


 

Friday, June 28, 2013 11:14:00 AM

Dependency Injection pattern in database development 

To get yourself familiar with dependency injection
pattern please check Wikipedia link here:
http://en.wikipedia.org/wiki/Dependency_injection

The primary database focus is to persist data and guaranty data integrity… well, at list we can say it about many databases. As a result database development evolved in its own procedural-way, and it is hard to argue against such a way or to change it, because in most cased database designs have tendency to harden, solidify, and resist a change after they get built. Besides, who want a change when over the years masses of applications got built on that-old-table. Just try to change it and wait for something to fail…
Whatever reason you have for a change try the Dependency Injection pattern. This pattern injects the “depended-on” code to the destination code automatically by knowing the requirement of the destination code and altering an expected outcome of the destination code. Let’s take very popular BOO and FOO templates for our example:

CREATE PROCEDURE dbo.sp_BOO_manager
AS
BEGIN
   IF dbo.FOO() < dbo.FOOTheshold()
      EXEC BOO 'Have to do BOO logic';
      ELSE MOO 'Have to do MOO logic, instead';
END

Here is how to introduce dependency injection into our code:

  1. Create following two schemas: INTERFACE, PROD_IMPLEMENTATION

CREATE SCHEMA INTERFACE;
CREATE SCHEMA PROD_IMPLEMENTATION;

  1. Move actual functions and procedures with business logic to the PROD_IMPLEMENTATION schema, like this one:

CREATE PROCEDURE PROD_IMPLEMENTATION.BOO
  @Message VARCHAR(50)
AS
BEGIN
   PRINT 'Very complicated proprietary logic';
END

  1. Create synonyms in the INTERFACE schema that will point to the functions and stored procedures in the PROD_IMPLEMENTATION schema. Note that synonyms can have the same names, because they are located in the different schemas than actual business logic. For instance:

CREATE SYNONYM INTERFACE.BOO
   FOR PROD_IMPLEMENTATION.BOO;
CREATE SYNONYM INTERFACE.FOO
   FOR PROD_IMPLEMENTATION.FOO;
CREATE SYNONYM INTERFACE.FOOTheshold
   FOR PROD_IMPLEMENTATION.FOOTheshold;

  1. Then change dbo.sp_BOO_manager stored procedure to use synonyms from the INTERFACE schema instead of objects themselves. Here is what you will get:

CREATE PROCEDURE dbo.sp_BOO_manage
AS
BEGIN
   IF INTERFACE.FOO() < INTERFACE.FOOTheshold()
      EXEC INTERFACE.BOO 'Have to do BOO logic';
      ELSE INTERFACE.MOO 'Have to do MOO logic';
END

Now our sample code does not bounds to the actual business logic directly, instead it calls an abstracted logic through an interface (which we can override when needed), allowing the calling process to inject desired functionality.
Let’s do it…
Make a new object somewhere in another database:

CREATE PROCEDURE SOMEOTHERDB.MOCK_OBJECTS.BOO
   @Message VARCHAR(50)
AS
BEGIN
   DECLARE @OverrideName VARCHAR(100)
      = 'SOMEOTHERDB.MOCK_OBJECTS.BOO'
   PRINT 'Very simple log message';
   INSERT INTO TEST_MESSAGES
      (OverrideName, [Message], EventDate)
      VALUES (@OverrideName,@TmpMessage, GETDATE());
END

Then (on your development server) change BOO synonym to point to the mocked object:

DROP SYNONYM INTERFACE.BOO;
CREATE SYNONYM INTERFACE.BOO
   FOR SOMEOTHERDB.MOCK_OBJECTS.BOO;

After this change, you can call business logic and verify that expected message got logged in to a test table.

--call logic
EXEC dbo.sp_BOO_manager;
--assert result
EXEC DBTD_ASSERT_COLUMN_HAVE_VALUE
   @v_TableName = 'TEST_MESSAGES',
   @v_ColumnName = 'OverrideName',
   @v_Value = 'SOMEOTHERDB.MOCK_OBJECTS.BOO',
   @v_ValueDataType = 'VARCHAR(100)',
   @v_UserMessage = 'BOO functionality should run';

There are a few ways to introduce dependency injection pattern in databases, we have chosen SYNONYMS, because of the following benefit:

  • Synonyms are very simple database objects;
  • Synonym does not change actual business logic they represents;
  • Synonym provides less invasive way to perform injection;
  • When creating synonym developer only need the name of the synonym (already known from consumer side) and the name of the object it represents (which is also known by developer from a provider side );
  • Locating synonyms and business logic in the different schemas allows synonym to have the same name as the object it abstracts, this preserve an existing naming convention used by development team;
  • Synonym can be created on the following objects:
    CLR Stored Procedures, Table-valued Functions, Scalar Functions, and Aggregate Functions, SQL Stored Procedure, Replication-filter-procedures, Extended Stored Procedures, SQL Scalar Functions, SQL Table-valued Functions, SQL Inline-table-valued Function, Views, User-defined Tables, includes local and global temporary tables

Well, here you go, now you have one more tool in your development toolbox. Use injection wisely, make sure you have basic infrastructure tests to go with it, and don’t forget about security.

Thursday, June 6, 2013 2:55:00 PM

Dynamically create a function or a stored procedure 

 

Dynamically create a function or a stored procedure in “some-other“ database from within the stored procedure running in “another“ database.
 
Note: for the time being we will forget about possible security issues caused by dynamic SQL
 
First of all we all know that CREATE or ALTER procedure clauses do not allow specifying the database name as a prefix to the object name, for instance if you will try to run following statement you will get an error:
 

 

CREATE PROCEDURE A.dbo.sp_bar
AS
BEGIN
SELECT 'a'
END;
 
That brings us to the usual way of setting database and then executing CREATE statement in the script, like this:
 
USE A
GO
CREATE PROCEDURE dbo.sp_bar
AS
BEGIN
SELECT 'a'
END
GO
 
Don’t forget about “GO” either, because CREATE or ALTER procedure must be the first statement in a query batch. That in fact itself, brings a little challenge when trying to dynamically create a function or a stored procedure in “A”database from within the stored procedure running in “B” database, since sp_executesql does not accept “GO”… if you will try following code:
 
DECLARE @sql NVARCHAR(100) = 'USE A GO'
EXEC sp_executesql @sql
 
or even this one
 
DECLARE @sql NVARCHAR(100)
SET @sql = 'USE A
GO'
EXEC sp_executesql @sql
 
both will return “Incorrect syntax near 'GO'” error message
 
So, to get around of all those limitations, create a little stored procedure in the target database (assuming that you have control over database and that you have thought about all the security concerns, and you have limited all access to such stored procedure) that will look like this:
 
USE A
GO
CREATE PROCEDURE dbo.sp_executesql_inTHISDatabase
(
@sql NVARCHAR(MAX)
)
AS
BEGIN
SET NOCOUNT ON;
EXEC sp_executesql @sql;
END
GO
 
All we need now is to do what we originally intended
 
USE B
GO
DECLARE @sql NVARCHAR(800)
SET @sql=
'CREATE PROCEDURE dbo.sp_bar
AS
BEGIN
SELECT ''a''
END;
';
EXEC A.dbo.sp_executesql_inTHISDatabase @sql;
 
Hurray! It works.
 
But now… please do remember all those security concerns we were trying to forget for a little while.
 
Thx.
Alex Podlesny
Wednesday, May 8, 2013 3:38:00 PM
Page 2 of 2 << < 1 2