Perspective on Computing

So I have to get back to the old vs. new. or perhaps the two worlds of programming.

Here is my frustration with UNIX/Linux or more accurately, vi:

The world of text editors is divided to three groups:

Those who believe that vi is God's gift to humanity

Those who believe that vi is a bug, not a feature

And those who use IDPF

But much more frustrating is the oo vs. procedural:

When I write in Perl and even COBOL, the language is a tool to express my ideas.

When I write the same thing in Java, the idea becomes a tool to  express the language

/****** Object: StoredProcedure [dbo].[za_transform] Script Date: 03/03/2011 23:20:26 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:
-- Create date: <3/1/2011>
-- Description:
--
-- Version 1.0
-- Published (temporarily) under LGPL 3.0
-- Next versions may switch to another open source license
--
-- Simpliistic implemetation
-- Usage:
-- EXEC @return_value = [dbo].[za_transform]
-- @Aggregate_Function = aggregate function (e.g. N'sum'), [default sum]
-- @Aggregate_Column = column to aggregate using the funcion,
-- @TableOrView_Name = table name or select query. Must contain the pivot column,
-- @Select_Column = row header columns, [PIVOT allows more then one]
-- @Pivot_Column = the field to pivot on,
-- @IN nvarchar = default NULL - Fixed values used to create column headings
-- THIS OPTION IS NOT YET IMPLEMENTED,
-- @DEBUG = 0/1 (currently default 1) print the select/pivot statement
--
-- This implemetation use a temporary table #_ZAT_pivotNames that is dropped in the
-- end of the run.
-- This version implements partial and very simplistic error checking.
-- Note that the user interface for this sproc was made to be compatible
-- with the popular sp_transform. New parameters are added in the end of
-- the parameter list, except of flags such as DEBUG.
-- This sproc was designed to use new (2005, 2008, etc.) features in SQL
-- server such as FOR XML PATH and PIVOT, but the autor is still full of
-- owe for the achivement of sp_transform and what could be done with
-- the primitive tools available in 2000.
--
-- =============================================
ALTER PROCEDURE [dbo].[za_transform]
@Aggregate_Function nvarchar (2000),
@Aggregate_Column nvarchar (2000),
@TableOrView_Name nvarchar (2000),
@Select_Column nvarchar (2000) = null,
@Pivot_Column nvarchar (2000),
@IN nvarchar(2000) = null,
@DEBUG bit = 1
AS
BEGIN
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#_ZAT_pivotNames','u') IS NOT NULL
drop table #_ZAT_pivotNames;

declare @tableName nvarchar(2000);
declare @PivotColumnName nvarchar(2000);
declare @aggregate nvarchar(2000);
declare @rowHeader nvarchar(2000);
declare @inList nvarchar (2000); --< develop this idea
declare @return int = 0; --< develop this idea
-- Develop total option.
declare @SQLSTR nvarchar (max);

declare @i int;

IF @Aggregate_Function NOT IN
('SUM', 'COUNT', 'MAX', 'MIN', 'AVG', 'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN
RAISERROR ('Invalid aggregate function: %s', 0, 1, @Aggregate_Function)
set @return = -1;
goto droptab
END

create table #_ZAT_pivotNames (pivotName sql_variant);

set @tableName = @TableOrView_Name
set @PivotColumnName = @Pivot_Column
set @aggregate = @Aggregate_Function + '(' + @Aggregate_Column + ')'
if @Select_Column is not null
set @rowHeader = @Select_Column + ',' + @PivotColumnName + ',' +
@Aggregate_Column
else
set @rowHeader = '*'
if ltrim (@tableName) like 'select %'
begin
set @tableName = '(' + @tableName + ') a'
end

set @tableName = '(select ' + @rowHeader +' from ' + @tableName + ') b'

set @tableName = '(select * from ' + @tableName + ')'

set @SQLSTR = 'insert into #_ZAT_pivotNames ' +
'select distinct ' + @PivotColumnName +
' from ' + @tableName + ' a;'

exec @i = sp_executesql @SQLSTR
if @i <> 0
begin
RAISERROR ('Invalid return code from sp_executesql: %d5', 0, 1, @i)
set @return = -1;
goto droptab
end

select @i = count (*) from #_ZAT_pivotNames
where cast(pivotName as nvarchar(2000)) like '%\[%' escape '\'
or cast(pivotName as nvarchar(2000)) like '%\]%' escape '\';

-- I may use QUOTENAME with its limitations or replace all ] by ]]
if coalesce(@i,0) <> 0
BEGIN
RAISERROR ('Invalid pivot column value: [ or ]', 0, 1)
set @return = -1;
goto droptab
END
else
begin
set @SQLSTR = (SELECT '[' + cast (pivotName as nvarchar(2000)) + '],'
FROM #_ZAT_pivotNames
FOR xml PATH(''));

set @SQLSTR = left (@SQLSTR, len(@SQLSTR) -1)

set @SQLSTR = 'SELECT * from ' + @tableName +
' a PIVOT (' + @aggregate + ' FOR ' + @PivotColumnName + ' in ('
+ @SQLSTR +
')) as p;'

if @DEBUG = 1
print @SQLSTR;

exec @i = sp_executesql @SQLSTR

if @i <> 0
begin
RAISERROR ('Invalid return code from sp_executesql: %d5', 0, 2, @i)
set @return = -1;
goto droptab
end
end

droptab:
drop table #_ZAT_pivotNames;
return @return;

END



GO

As an old timer, I learned computing before the reign of Object Oriented and it is hard for me to get there. However, as oposing to the stupid Lisp that was and still is a bad idea, I do see how OO is actually a very good idea. It is important concept and should reign supreme in the programming world. I use objects when I need them and when available. What I don't like and don't appericiate is the OO fanaticism. You look at python and Ruby and everything is an object, no primitives need apply. You look at Java and C++ and while primitives are allowed, evrything else must be encapsulated in a class.

What does one have to do when all this encapsulation layers stand between him or her and what he or she wants to do. Before I can program, I have to encapsulate it, before I code what I positicely want to do, I have to throw and catch what I don't want to do and so on. Well, this is fine for full fledged programs or packages, but they want me to do that when I code even a simple script that, let's say, read a text file, parse it to certain tokens and write a comma delimitted file. In order to prevent me from reading and writing, they invented Newspeach in which the verbs read and write do not exist anymore. Instead, you persist an object! But I don't want to persist an object, I rebel against this Newspeach and I want to write a line of text.

I would like to give a little testimonial here. I am in the software development field for over thirty years ant I atest that all the concepts that are in the computing software world were invented sometime before the end of the nineteen seventies. Don't misunderstand me, not all the technologies that we have today were available then. The concepts were developed and refined a lot since then. But the basic concepts themselves were already available.

I may give many examples and I chalenge all to show me something that was not known conceptually then. You want to take XML for example, as a way to persist structured data it is based on SGML and as a way to layout hierachical data, a quick look at the ancient IBM's IMS hierarchical database will convince anybody that the hierarchical model is very ancient indeed. The relational model and SQL; just see the date on Dr. Dodd's first articles on that subject. And the list is long.

Well, as it happens, not all concepts that could be thought about are positive. Some of them are either negative or sheer idiotic. And lo and behold most of the idiotic concepts were invented and persist with us since then as well. And here I come to my main point. Sometime in that era, somebody with crooked mind invented LISP, one of the the most unintuitive languages ever invented. There are other examples such as APL, but none of them looms big over the computing world as does LISP.

That would not be that bad if LISP would stay in the academic world and be used to develop some Artificial Inteligence systems. The people in that environment probably need to express themselves with such a monster. However, at some point these people gave us LDAP. Now LDAP is based conceptually on LISP and since LISP is basically hierarchical, LDAP is also hierarchical. There is nothing specifically wrong with the hierarchical model per se when one is dealing with primitive unidimensional and one purpose only systems, but LDAP is not supposed to be such a primitive system.

Let me explain the practical problems of the hierarchical model and the specific LISP oriented implementation of LDAP.

The first and formost problem is that the hierarchical model is unidirectional. Conceptually, you have to enter through the one root and navigate your way around. This drawback has been noticed almost from the start and many solutions have been devised. IMS does have indices that allow to bypass the root in searches. The network model that was implemented in IDMS allows multi-hierarchies and therefore several roots or entry points, but none of these eliminated the basic need to navigate over the hierarchy in order to get the information.

Since all these hierarchies could easily be translated into tables and relations, the relational model swept the world and practically burried the hierarchical model. The relational model eliminated the navigation problem and replaced the complex navigational programming with the simple, clean and elegant SQL.

It took me a while to understand the need for XML with its inherent hierarchical nature, but once I understood that XML is a way to persist data with its meta-data rather then structure it logically, I accepted the concept. XML does not replace the relational model with a back to the future thinking and does not intend to do so.

LDAP on the other hand is an hierarchical monster with all the drawbacks of that model and none of the benefits. There is nothing in LDAP that may not be implemeted in much easier and simpler manner using the relational model. Add to the basic flawed model the fact that the official query tool is the LDAP (V3 I believe) language which is a LISP derivative and therefore may not be understood by normal human beings and you may imagine how is that monster basically, access proof. There is no simple way to query the thing short of using complex navigational programming with complex API... Just a minute, isn't that exactly why we scrapped the hierarchicl model in the first place?

I am working with a software product that utilize LDAP as its means for handling user accounts. You may only imagine my frustration over the inability to search for anything short of coding a full fledge utility program. I view LDAP as an inhibitor rather then an enabler tool in my work and as with any inhibitor, the only way to deal with it is to bypass it. One practical solution may be to actually dump the information in the off hours, parse it with Perl and upload the relevant data to any relational database that might be available. The information might be stale in the end of the day, but its bulk may still be correct and usefull until a new copy may be taken.

Well, management and pundits may be buying into that stuff, but the show must go on and radical, at least partial solutions must be sought.