Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Sql Mythology


Greetings,

(This is also available and probably easier to read at:
 http://beyondsql.blogspot.com/2007/05/sql-mythology.html)

To paraphrase 'V' from the movie 'V for Vendetta' if
as an application developer or even as an sql programmer
you feel that everything is fine and dandy in S2005
and even .net you need read no further. But if you feel
there is something askew, something that does not make
alot of sense in sql land, then I suggest you read on.

'Think in terms of sets' in sql is empty of any real meaning
except don't use declarative statements. This phrase should
be replace with 'think in terms of application development'.
As a critic of sql I can only go so far. So from time to time
I'm going to walk the walk instead of just talk the talk.

I'm going to show you how to go about application development
in a new way. A way that makes sense. I'm going to introduce
you to Dataphor from:
www.alphora.com

In broad strokes Dataphor is an application development system
based on the MS .net technologies. It is therefore a strongly
typed system. It offers a table type of a very specific nature,
a relational table type. To support this type there are a host
of 'relational' operators. The table type and its corresponding
operators are the advancement over and beyond current sql. All
the types, be they table, strings, numbers, boolean can be
accessed and manipulated within a Pascal like declarative
language. And within the language all types can logically be
treated as 'variables'. This development environment is referred
to as 'D4'.

The availability of a 'relational' system is a fairly recent thing.
What transpired in the interim was the perpetuation of a grand myth.
Developers were led to believe that sql was relational! Led by
vendoraptors like IBM and Oracle and more recently MS and industry
punitwits like Joe Celko, the illusion of sql working as if it
were relational was and still is the industry norm. Finally with
Dataphor developers can 'see' the differences for themselves. Of
course there are many myths surrounding sql and I hope to get to
most of them in time -:)

So lets look at question raised in the post:
Tuesday, May 22, 2007 8:02 AM
microsoft.public.sqlserver.programming
'parsing string into sequence of numbers and letters?'
http://tinyurl.com/yo28he

This question elucidates many themes still prevalent today.
1. The Abbott & Costello syndrome. Where is the appropriate place
   for a data operation, the client or the server. Every
   failure for sql to support an operation has been portrayed
   as the inappropriateness for that operation to done on the
   server. A glaring example of this is list/string operations.
   A favorite tactic is too call an operation 'formatting' and
   argue that it belongs on the client. This none-sense has gone
   on forever and disappears in the strong typed environment of D4.
   The artifical dichotomy between client/server is a major
   hindrance to application development. It is now possible for
   developers to see this for themselves.
2. There is no logical manifestation of the concept of a 'key'
   in sql. Like so many concepts sql convinced users that a key
   was synonymous with an 'index'. Keys are of such fundamental
   importance that it is actually problematic to even call S2005
   (or Oracle,DB2) a database! With D4 developers can see all the
   'logical' advantages they gain with their use. All the none-sense
   about 'faking' arrays in sql goes out the window in D4. As you
   will see there is no reason to fake something and create more
   of a mess when the concept of an 'array' is seen as a component
   of a key. It is often written that sql is a 'data retrieval
   language'. It is more meaningful to say it is a 'fast' retrieval
   language. That is why there is so much emphasis on indexes.
   But performance comes at a cost such as integrity and manipulation.
   Up till now this cost has been covered up with all kinds of
   none-sense.
3. D4 offers consistency of operations across types. Such consistency
   brings clarity to application development not found in sql.

What we want to do here is take a string and break it apart
creating separate columns for letters and digits. The only
twist is that there is an explicit ordering - letters for the
1st column, digits for the 2nd and letters for the 3rd column.
If a string does not have letters-digits-letters we want to
fill the remaining column(s) with a placeholder (we're using
a blank here but a null (nil in D4) or anything else could be used).

Table VMI will reside in the pubs database in S2005.
RowID will be the clustered primary key. Additional indexes
can be created directly in S2005 or thru D4. All logical operations
concerning 'keys' will be handled by the Dataphor server.
Essentially all data operations are handled by D4. This includes
all data integrity and manipulation. And both are a qualitative
advance over sql. S2005 is simply the data respository and D4
will take advantage of its (hopefully) fast data access paths.
The D4 server 'chunks' data operations. What this means is D4
will determine when it is appropriate to hand over query requests
(as well as intermediate steps) to S2005 and when to handle them itself.
This whole process is basically transparent to the user. The physical
implementation details needn't concern the user.

create table VMI
{
RowID:Integer,
InputStr:String
static tags {Storage.Length = '12'},
key{RowID}

};

insert table
{
row{1 RowID,'AA99BB' InputStr},
row{2,'WWW8888PPPPP'},
row{3,'77DD'},
row{4,'EE66'},
row{5,'G'},
row{6,'HHHH5J'},
row{7,'44'},
row{8,'22KK'}

} into VMI;

//The data is now in S2005 and available to D4.
select VMI;

Operator Op1VMI() uses string operators to chunk together like characters
within a RowID. As an operator it is available to be used anywhere.
An operator can return any kind of supported type in Dataphor. That
means it can return: nothing, a table, a row (multiple scalar values),
a scalar, a cursor, a list. The result may be virtual or it may persist
in a table. Whatever it can return, it can also use as arguments
(parameters).
Since 'relational' is synonymous with 'type', tables/rows can be arguments
since we are dealing with structure based on type, ie for a table its type
is the structure of the table the column names and their scalar types.
This a fundamental difference from sql which equates a table with its file
name.
This is also why in Dataphor we are dealing with tables as relational
variables.
A table is just another type of variable, but a variable nonetheless just
like
an integer.
Also note that there is no arbitrary breakup of operators. There are no
'procedures'
as opposed to 'functions' as in sql server. There are no limitations as what
can be done in an operator as there are in sql server. All the sql
limitations
are based on design flaws that developers have to work around. This is
another
example of the general pattern of 'inconsistency' in sql:).
If your wondering why not define a view instead of an operator I can
understand.
Views in Dataphor are not the same relatively simpleton macros they are
in sql. They are much more highly evolved in a relational system and serve
many different functions not available in sql. There is no reason for a
'relational' view here, again all in good time -:).

create operator Op1VMI():
     table{RowID:Integer,StrChar:String,InputStr:String,Rank:Integer,
             Cnt:Integer,PadStr:String,sequence:Integer}
begin
result:=table of typeof(result){};
result:=
 ToTable
  (
   ToList
    (
     cursor
       (
         (
    //Each character in a string can be addressed by its indexed position.
           (VMI add {Length(InputStr) LenStr})
              times
               numbers
                where num<=(LenStr-1)
                 add {InputStr[num] StrChar}
                   remove {LenStr}
          )
          //Here we're concatenating repetitions of characters into a single
string.
          //We could have simply used the Concat operator but there is a
rich
          //number of string operators available in D4 (and you can add your
own).

            group by {RowID,StrChar}
               add{Max(InputStr)InputStr,Min(num)Rank,Count()Cnt}
                add {PadRight(StrChar,Cnt,StrChar) PadStr}
                 order by {RowID,Rank}

    //Using ToTable(ToList(cursor creates a table with a
    //sequence (rank) column in the order of the cursor declaration.
    //The sequence column is similar the sql-99 row_number() function.
    //In fact could have used a passthru query to sql server and used
    //row_number(). The returned table would then be available for
    //manipulation within Dataphor. All in good time -:).

        )//cursor
      )//ToList
    );//ToTable
end;

Invoking the operator makes its result (a table) available, just like
a table. For examples:
select Op1VMI() order by {sequence};

Table LVMI is similar in concept to a #tmp table in S2005.
We're going to store both the delimited string (DelStr)
and the delimited string as a 'list' (StrAsList). You need
only a dose of common sense to understand the difference
between the 'string' TYPE and the 'list' TYPE. In D4 a type
is supported by a plethora of dedicated operators. This
includes operations for strings and lists not available in sql.
These operators also serve to guarantee the integrity of the
type. The vacuum left by their absence in sql gives raise to a
special type of programming, commonly referred to as a 'kludge'-:)
(Wasn't LV1 the planet in Alien/Aliens?)

create session table LVMI
 {
  RowID:Integer,
  InputStr:String,
  DelStr:String,   //There is no real need to store the delimited
                   //string. In fact it defeats the purpose of the
                   //'list'. But we do it to illustrate the difference
                   //between a delimited string and a list.
  StrAsList:list(String),
  key{RowID}
 } ;

In sql there is always much adieu made of violating normal form
by ...

read more »

Hello, Steve

Considering the following DDL and your sample data:

CREATE TABLE VMI (
        RowID int PRIMARY KEY,
        InputStr varchar(12) NOT NULL
)

INSERT INTO VMI VALUES (1,'AA99BB')
INSERT INTO VMI VALUES (2,'WWW8888PPPPP')
INSERT INTO VMI VALUES (3,'77DD')
INSERT INTO VMI VALUES (4,'EE66')
INSERT INTO VMI VALUES (5,'G')
INSERT INTO VMI VALUES (6,'HHHH5J')
INSERT INTO VMI VALUES (7,'44')
INSERT INTO VMI VALUES (8,'22KK')

We need an auxiliary Numbers table:

SELECT IDENTITY(int) N INTO Numbers FROM syscolumns
ALTER TABLE Numbers ADD PRIMARY KEY (N)

And then we can provide the expected result in a single SQL query:

SELECT V.InputStr,
        COALESCE(SUBSTRING(V.InputStr,1,A.N),'') AS C1,
        COALESCE(SUBSTRING(V.InputStr,COALESCE(A.N,0)+1,
                COALESCE(B.N,100)-COALESCE(A.N,0)),'') AS C2,
        COALESCE(SUBSTRING(V.InputStr,B.N+1,100),'') AS C3
FROM VMI V LEFT JOIN (
        SELECT InputStr, N FROM VMI, Numbers
        WHERE SUBSTRING(InputStr,N,2) LIKE '[A-Z][0-9]'
) A ON V.InputStr=A.InputStr
LEFT JOIN (
        SELECT InputStr, N FROM VMI, Numbers
        WHERE SUBSTRING(InputStr,N,2) LIKE '[0-9][A-Z]'
) B ON V.InputStr=B.InputStr

InputStr     C1           C2           C3
------------ ------------ ------------ ------------
AA99BB       AA           99           BB
WWW8888PPPPP WWW          8888         PPPPP
77DD                      77           DD
EE66         EE           66
G                         G
HHHH5J       HHHH         5            J
44                        44
22KK                      22           KK
(8 row(s) affected)

Razvan

-----------------------------------------------Reply-----------------------------------------------

Hello,

I appreciate your effort but you apparantly do not get or understand
what I am trying to communicate :(

best,
steve

"Razvan Socol" <rso@gmail.com> wrote in message

news:1180432672.062549.109680@q75g2000hsh.googlegroups.com...

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc