No where can the essence of a business problem or model be better
shown than thru the means of constraints. Constraints encapsulate
the logic of a model and govern the ebb and flow of an application.
Constraints in Dataphor, based on the relational model, offer these
benefits:
* Emphasize data independence
* The ability to reference global objects
* The use of the declarative method in application development
The relational model allows and fosters the clear separation of
how a problem is modeled versus physical implementation details.
This is the relational concept of data independence. The solution
of a business problem should not be encumbered by anything that
detracts from the ability to clearly and precisely model it. This
is the essence of the concept of the logical model in relational
theory. Yet data independence is sorely lacking in sql where the
line of distinction between the logical model and performance
considerations are too often blurred. The most blatant example
is the conceptual entanglement of keys and indexes. A key, which
is a constraint, is a logical consideration. An index is a physical
consideration. By allowing indexes based on performance considerations
to directly enter the logic of the model the clarity of expression
that is needed becomes undermined or even lost. The price for a gain
in performance is paid for by the ambiguity introduced into the
model. Constraints in dataphor can be clearly expressed independent
of performance based concepts, ie. indexes.
In order to define constraints that need to reference global objects
like tables, sql needs to rely on mechanisms like triggers. All
to often this leads to problems with declared referential integrity
constraints and adds an additional layer of complexity to the
problem. Unlike simple column constraints or indexes, sql does
not allow a table constraint to be simply and clearly stated in a
'create table' or 'alter table' statement. Dataphor allows this
simple but powerfully expressive capability.
In sql the idea of metadata is independent of the logical constructs
used to derive data. In other words, in sql you can declare an
index but you can't declare any additional information about the
index. Dataphor allows metadata, information about the object, to
be declared along with the object. This additional information is
used in a declarative way to make application development easier.
Dataphors derivation engine thus absolves the developer of an
entire level of work. For example, by declaring metadata about
a constaint the meaning of the constraint can be clarified
while at the same time saving the developer the work to do this.
An interesting example of the confluence of constraints
(the logical model) and performance can be found in the article
'Indexes on Computed Columns: Speed Up Queries, Add Business Rules'
which can be viewed here. Very cleaver solutions to constraints
are proposed in the form of indexes on computed columns. Compare
this approach to simply, clearly and explicitly defining a
constraint with metadata in dataphor leaving performance optimization
as the last step of the application.
For example, one problem the article discusses is insuring that VPN
tokens are unique in the table while allowing any number of null VPN
token numbers.
I'll be using dataphor with MS sql server 2005 sp1 as the data
repository (backend).
Here is the table definition defined in dataphor:
(All dataphor keywords are in bold)
create table TESTC
{
EMPLOYEEID : Integer,
LOCATIONID : String,
DEPTID : String { default 'ONE' },
VPNTOKEN : Integer { nil } ,
key {EMPLOYEEID}
}
The primary key is EMPLOYEEID. The 'Integer { nil }' is the dataphor
way of saying that VPNTOKEN can take a null (no value) as the data
is stored in sql server.
A table constrain can now be declared that captures the logic of
uniqueness for non null VPNTOKENs and allows unlimited nulls for it.
The constraint can be declared using the dataphor alter table.
alter table TESTC
{
create constraint DISTINCTVPNWITHNULLS
exists(
TESTC
group add {Count(VPNTOKEN) CNT,Count(distinct VPNTOKEN) DISTVPNCNT}
where CNT=DISTVPNCNT
)
tags
{
DAE.Message =
"'VPNTOKEN ' + ToString(VPNTOKEN) + ' is not unique'"
}
}
The 'Count(VPNTOKEN)' and 'Count(distinct VPNTOKEN)' work the same as
they do in sql. Both count constructs eliminate null values. So the
where predicate is comparing the count of non nulls to the distinct
count of non nulls. The group statement has no by statement so
the comparison is over the whole table. The logic of the constraint
says if where is true then all the non nulls must be unique. If where
is false an attempt was made to enter an existing non null VPNTOKEN
and thus violiates the uniqueness of VPTOKEN in the table and the
constraint. The attempt to enter such a VPNTOKEN will be rejected.
Since where is independent of nulls, all null values for VPNTOKEN
will satsify the constraint (it evaluates to true) and will be
accepted. The only way a null VPNTOKEN will be rejected is if the
row being inserted violates the uniqueness of the key of the table
which is EMPLOYEEDID. This is the meaning of specifying a key. Only
unique values of EMPLOYEEID are acceptable.
The tag keyword indicates metadata is supplied with the constraint
definition. Dataphor will use the message when the constraint
is violated as opposed to a cryptic message returned by sql server
when a key/index is violated.
Lets insert some data with unique VPNTOKENs
insert
table
{
row { 1 EMPLOYEEID,'A' LOCATIONID,'ONE' DEPTID, 1 VPNTOKEN },
row { 2,'A','ONE',2},
row { 3,'B','TWO',3}
}
into TESTC;
Insert some rows with null VPNTOKEN.
insert
table
{
row { 4 EMPLOYEEID,'C' LOCATIONID,'ONE' DEPTID},
row { 5 EMPLOYEEID,'C' LOCATIONID,'THREE' DEPTID}
}
into TESTC
This is the data in the table.
select TESTC
EMPLOYEEID LOCATIONID DEPTID VPNTOKEN
---------- ---------- ------ ----------
1 A ONE 1
2 A ONE 2
3 B TWO 3
4 C ONE <No Value>
5 C THREE <No Value>
Now in the derived form that dataphor has available for every table
and view we can try to enter a row such as:
row { 7 EMPLOYEEID,'C' LOCATIONID,'THREE' DEPTID,2 VPNTOKEN}
Dataphor uses the tag metadata expression to clearly show the user
the problem with the specific data that violated the constraint:
I submit this is a significant improvement over the type of message
returned by sql server which tells you what object (ie. key)
caused the violation but gives no information about the 'data'
which caused it. The developer is left with this task on the frontend.
Any table constraint should be a straightforward short process to
represent. As another example, this constraint allows unique VPNTOKENs
but at most 1 null value.
alter table TESTC
{
create constraint DISTINCTVPNWITHONENULL
exists(
TESTC
group add {
Count() TOTALCNT,
Count(VPNTOKEN) CNT,
Count(distinct VPNTOKEN) DISTVPNCNT
}
where
(TOTALCNT-CNT < 2) and (CNT=DISTVPNCNT)
)
tags
{
DAE.Message =
"'VPNTOKEN ' + IfNil(ToString(VPNTOKEN), 'NULL') + ' is not unique'"
}
}
The where statement uses '(TOTALCNT-CNT < 2)' to check that there is
at most 1 null value (there can be 0 or 1) and uses '(CNT=DISTVPNCNT)'
to insure that the non null values are unique. With a violation, the
message will display a VPNTOKEN value that already exists in the table
including 'NULL'.
Dataphor by default will display an informative message for any key
that is violated. It is not necessary to explicitly state any
message. On sql server a primary key violation displays only the
object of the violation. For example:
'Cannot insert duplicate key row in object 'dbo.testc' with unique
index 'UIDX_TESTC_EMPLOYEEID'.
The statement has been terminated.'
While dataphor will display the specific data that caused the
violation:
In the future I'll talk about other types of constraints in
dataphor such as column and transition row constraints.
Bye for now.
Showing posts with label dataphor. Show all posts
Showing posts with label dataphor. Show all posts
Sunday, September 10, 2006
Dataphor - Are You Constrained?
Friday, September 01, 2006
Dataphor - Get LiT!
LiT: = lists into tables:) I'm going to talk about a bunch of kewl stuff as it relates to LiT. So get relaxed and come take a ride on the D(4) train:) As I showed here the D4 language makes it very easy to split strings. Now I want to show you some more kewl stuff about working with strings, lists and tables using the Split operator. Lets start with this string delimited by the pipe ('') character: 'FFBB HH HH D AA' Notice that blanks can appear at the beginning, end and within a string part. The Spit operator takes a string and splits it into a 'list' of strings based on one or more delimiters. Conceptually this means that the above string is transformed into: 'FF',' BB',' HH HH ',' D ',' AA' in other words a comma delimited list of strings. And with a list you can access and operate on the individual strings parts. The concept of a list is conceptually close to what concept in a database? Yep, you guessed it - a table! Each string in the list fits nicely with the idea of a row in a table. Make sense? And once the list is in a table, well we can do just about anything with it as you'll see:) So lets create a table from our example string using Split. The basic construction of Split using the method style is: 'somestring'.Split({delimiters}) With our example string, which uses the pipe as a delimiter, we have: 'FFBB HH HH D AA'.Split({''}) The Split operator uses a thingie called an 'indexer' to access the individual parts of the list, like this: 'some string'.Split({delimiters})[index] where index is a digit for the desired element of the list. So if we want the 1st string/element in the list we use: 'FFBB HH HH D AA'.Split({''})[0] We're using 0 because the indexer is 0 based, ie. 1st element 0, 2nd element 1 etc. Real easy. Ok we've got enough info now to select the 1st string: select 'FFBB HH HH D AA'.Split({''})[0] FF Ok for the moment lets leave the idea of getting individual parts of the list with the indexer and think of getting 'the whole list' with one swipe of the magical D4 wand. And that wand is the D4 ToTable. Since Split has done the heavy lifting of creating the list, all we have to do to get LiT is this: select ToTable( 'FFBB HH HH D AA'.Split({''})) Value ------- D HH HH AA BB FF and we are Lit (list in table) :) When you transform a list to a table the column by default is 'Value'. You can name the column anything you want by including a column name in Split. I'll call the column 'STRING': select ToTable( 'FF GG HH HH D AA'.Split({''}),'STRING') STRING ------- D HH HH AA BB FF Ok we got the list in a table but you see that the strings have the same blanks as they had originally. Since we are LiT we can trim the leading and trailing blanks of each row (string) using : {Trim(STRING) STRING} This means trim each row and retain the same column name. select ToTable ( 'FFBB HH HH D AA'.Split({''}),'STRING' ) {Trim(STRING) STRING} STRING ------ AA BB D FF HH HH Ok at this point your head is probably ready to explode since you can see that while I've made all this fuss about LiT, the rows of the table does not match what the order of strings should be! In other words, if Split creates a list shouldn't the table reflect the strings like: STRING ------ FF BB HH HH D AA If this has occurred you I'm doing my job:) As I said above we can use what's called an indexer to address the elements of the list in order. And we'll do that soon. But, it's meaningful to get LiT even though the elements are not in the proper sequence. Just understand that without supplying specific information to Split about the individual elements (the indexer thingie []) we can only get a table whose rows reflect the ordering of the string elements themselves. Here we add a system generated auto-incrementing column INDEX using the GetNextGenerator operator that reflects the ascending order of STRING. (The INDEX column is similar to the identity function used in MS Sql Server). You can see that INDEX reflects the original (untrimmed) strings. select (ToTable ( 'FFBB HH HH D AA'.Split({''}),'STRING' )) add {GetNextGenerator('INDEX') INDEX,Trim(STRING) STRING1} STRING INDEX STRING1 ------- ----- ------- D 1 D HH HH 2 HH HH AA 3 AA BB 4 BB FF 5 FF Here we get the trimmed strings and then add the auto-incrementing column. select ( (ToTable ( 'FFBB HH HH D AA'.Split({''}),'STRING' )) {Trim(STRING) STRING} ) add { GetNextGenerator('INDEX') INDEX } STRING INDEX ------ ----- AA 1 BB 2 D 3 FF 4 HH HH 5 The INDEX now reflects the ascending order of the trimmed strings. No matter how you cut it, splitting a string in toto into a table is not going to give you the order as they appear in the original string. As I said even though this is true it still has value and significance. Now I'm going to show you why. Here are 2 tables created on MS Sql Server 2005. We're going to access them with Dataphor using D4. create table str1 (ID1 int primary key,STRING1 varchar(50)) insert str1 values(1,'AABB CC') insert str1 values(2,'FFGGHH MM J KK') insert str1 values(3,'SSTTUUVV') insert str1 values(4,'AAYYYYPQQRDD') insert str1 values(5,'D10B20A30') create table str2 (ID2 int primary key,STRING2 varchar(50)) insert str2 values(1,'AA BB CC') insert str2 values(2,'FF GG HH MM J KK') insert str2 values(3,'SSTTUUXX') insert str2 values(4,'AAPPPPWRQRDD') insert str2 values(5,'D10B20') Using D4: select str1 ID1 STRING1 --- ------------------ 1 AABB CC 2 FFGGHH MM J KK 3 SSTTUUVV 4 AAYYYYPQQRDD 5 D10B20A30 select str2 ID2 STRING2 --- ---------------------- 1 AA BB CC 2 FF GG HH MM J KK 3 SSTTUUXX 4 AAPPPPWRQRDD 5 D10B20 Lets suppose we want a query that compares by ID STRING1 and STRING2 as lists. We want the ID's where the lists are the same. In other words, we want the ID's where all elements in STRING1 and STRING2 are the same (equal). So we're looking for ID's 1 and 2 where the trimmed elements are the same. You'll recall that we LiT a string using: select ToTable ( 'FFBB HH HH D AA'.Split({''}),'STRING' ) {Trim(STRING) STRING} All we have to do to LiT our strings is use STRING1 and STRING2 in Split. And once we have LiT both strings we can wave a magic wand. We can directly test to see if the 2 tables are equal! We simply join by ID's and then test the tables for equality in where. select str1 join str2 by ID1=ID2 where ((ToTable(STRING1.Split({''}),'STRING')) {Trim(STRING) STRING}) = ((ToTable(STRING2.Split({''}),'STRING')) {Trim(STRING) STRING}) ID1 STRING1 ID2 STRING2 --- ------------------ --- ---------------------- 1 AABB CC 1 AA BB CC 2 FFGGHH MM J KK 2 FF GG HH MM J KK Comparing tables is just a logical extension of comparing integers or strings or just about anything else. Tables are equal if they have the same column(s) (STRING) and the elements (rows) are equal. Is that a magic wand or what:) Now it's fundamentally important to understand how this works. The key of each table is of course STRING. Each key also implies an order. So each row, ordered by the key (STRING), in each table is compared to the corresponding row in the other table just like you'd compare integers. If every row is equal in the 2 tables the comparison is true, otherwise it's false. D4 is thus able to logically address the rows in a table via the key. This is fundamentally different than sql which can't do this. The ability to use keys for logical addressing has big, big implications. It's one of the fundamental things that differentiates a relational language from sql! I'll get into it in future posts. Now back to our example. Note that for ID's 5 that have 3 and 2 rows respectively, the table comparison was no problem. I figure that if your still with me your interested and pretty sharp. So I'll give you the benefit of the doubt and assume a pretty important question has occured to you. What if the original string has duplicate elements? For example: 'AAATTCCAAA' Lets see what happens when we try to LiT this: select ToTable ( 'AAATTCCAAA'.Split({''}),'STRING' ) WoW! We get this from the Dataphor compiler: 'Internal Index Error: "Duplicate key violation." ' Yeah we're SOL but does it makes sense? Well all tables in D4 have to have a key. No if, ands or buts. The key is of course STRING and when the string is split into a table there are duplicates of 'AAA' and there can't be any duplicate rows. So there goes our table:( I'm sure you can guess the remedy:) But remember that D4 is a strongly typed language. A list is not the same as a table. We've been trying to convert a list to a table. Too different types, that is the point of having to convert. And sometimes it just won't work the way we want it to. Before we remedy this, things could get more dicey if we have this: select ToTable ( 'AAA TTCC AAA '.Split({''}),'STRING' ) STRING ------ AAA AAA CC TT And then trimmed the strings: select ToTable ( 'AAA TTCC AAA '.Split({''}),'STRING') {Trim(STRING) STRING} STRING ------ AAA CC TT An element 'AAA' was eliminated (no duplicates allowed) so we end up with 3 elements instead of 4. And if we compared this with 'AAATTCC' select TableDee add {'False Positive' Compare} where (ToTable ( 'AAA TTCC AAA '.Split({''}),'STRING') {Trim(STRING) STRING} ) = (ToTable ( 'AAATTCC'.Split({''}),'STRING') {Trim(STRING) STRING} ) Compare -------------- False Positive we'd get a false positive. The tables are equal but the lists are not! Phew:) (Don't worry about the 'TableDee' thingie, I'll get to that in due time:) But please come to the idea that table comparisons are a magic wand and once you start using them you'll wonder how you ever got along without them. But alas, you can loose some of that magic when working with lists so be careful:) (More on table comparisons here). So finally lets get to splitting the list by the individual elements, ie. using an indexer ([]). On the MS Server newsgroups it's common to see the phrase 'use a set oriented approach'. This means don't use procedural/imperative code. So no cursors, or loops:) Now I've already gone over splitting strings here. But I want to show you in a little more depth what is going on. Given a string: 'AAA TTCC AAA ' A basic query to get the elements of the list is: select ToTable( {0, 1, 2, 3, 4 , 5, 6, 7, 8 },'INDEX') where 'AAA TTCC AAA '.Split({''}).Count()>INDEX {INDEX+1 INDEX,'AAA TTCC AAA '.Split({''})[INDEX].Trim() STRING} INDEX STRING ----- ------ 1 AAA 2 TT 3 CC 4 AAA Note that with the inclusion of INDEX we are guaranteed not to eliminate duplicate STRINGs. The INDEX column is always unique and takes part in the key of the table. Now the statement: ToTable( {0, 1, 2, 3, 4 , 5, 6, 7, 8 },'INDEX') creates a table with column INDEX for the list of integers. 'AAA TTCC AAA '.Split({''}).Count()>INDEX filters on INDEX where the count of the elements in the list is greater than the INDEX value. There are 4 elements in the list so INDEXs 0, 1, 2, 3 satisfy this criteria. Remember lists are 0 based. So we end up with a table with 4 rows, ie: INDEX ----- 0 1 2 3 Then the statement: {INDEX+1 INDEX,'AAA TTCC AAA '.Split({''})[INDEX].Trim() STRING} is applied. This statement modifies the above table by redefing INDEX and adding the column STRING. The STRING column is each list element, in order, identified by the indexer ([INDEX]). Each element of the string is also trimmed. We added 1 to INDEX because.. well I wanted too because I like to start from 1 not 0:) So there you have it. Well...not quite. There's stuff going on here under the covers that you should know about and understand. How is it that Dataphor interprets: {0, 1, 2, 3, 4, 5, 6, 7, 8 } as a list of integers and not something else? To answer this let me show you formally how this table of integers is created in a block of D4 code. begin var Indexes : list(Integer); Indexes := { 0, 1, 2, 3, 4 , 5, 6, 7, 8 }; create session table Indexes from ToTable(Indexes,'INDEX'); end; A variable, Indexes, is assigned the type list(Integer), ie. an infinite list where the elements are of type integer. Once variable Indexes is defined as a type of list, the actual elements of the list (the integers) are assigned to it. Now the list can be converted into a table. The create session table Indexes from creates what is called a session table. It's like a permanent table but is only available to the Dataphor session that creates it. It's like an MS Sql Server ## temp table. select Indexes INDEX ----- 0 1 2 . 8 The answer to the question of how Dataphor knows that: ToTable( {0, 1, 2, 3, 4 , 5, 6, 7, 8 },'INDEX') is a list of integers is because the system has inferred it! That's why it isn't necessary to formally declare any variable with a type of list(Integer). In other words, a comma separated bunch of integers is logically a list of integers. The D4 compiler as an inference engine is a super big deal!:) The concept of logically inferring metadata (ie.keys, constraints) does not exist in an sql database. I'll get into this big deal in future posts. Ok, back to our example:) So now you know some more about the integer list in our query: select ToTable( {0, 1, 2, 3, 4 , 5, 6, 7, 8 },'INDEX') where 'AAA TTCC AAA '.Split({''}).Count()>INDEX {INDEX+1 INDEX,'AAA TTCC AAA '.Split({''})[INDEX].Trim() STRING} And what about the argument to Split? Well here is a formal code block for splitting a string (using the above Indexes table). begin var LList : list(String); var ExString : String; ExString := 'AAA TTCC AAA '; LList := ExString.Split( {''} ); create session table ExList from Indexes where LList.Count()>INDEX {INDEX+1 INDEX, LList[INDEX].Trim() STRING} ; end; select ExList INDEX STRING ----- ------ 1 AAA 2 TT 3 CC 4 AAA We don't have to explicitly declare that Split should result in a list of String elements. All this information is inferred by the compiler (thank you very much D4:) I'm rambled enough:) Now we're going to finally, finally split strings and generate a little report. For good measure we're going to insert another row in our string tables, str1 and str2, via D4: insert table { row { 6 ID1, 'AAA TTCC AAA ' STRING1 } } into str1 insert table { row { 6 ID2, 'TTCCAAA' STRING2 } } into str2 So now have: select str1 ID1 STRING1 --- ------------------ 1 AABB CC 2 FFGGHH MM J KK 3 SSTTUUVV 4 AAYYYYPQQRDD 5 D10B20A30 6 AAA TTCC AAA select str2 ID2 STRING2 --- ---------------------- 1 AA BB CC 2 FF GG HH MM J KK 3 SSTTUUXX 4 AAPPPPWRQRDD 5 D10B20 6 TTCCAAA We want to compare strings by ID and only where the lists have the same number/count of elements. And we only want the strings where there are differences for the same element (INDEX). So each row will include the ID, the corresponding different STRINGs, the INDEX for each different element and the individual different string parts. select ( ( ( (str1 join str2 by ID1=ID2) where STRING1.Split({''}).Count()=STRING2.Split({''}).Count() ) times // times is like the sql cross join. Indexes where STRING1.Split({''}).Count()>INDEX ) where // <> is the notation for not equal. STRING1.Split({''})[INDEX].Trim()<>STRING2.Split({''})[INDEX].Trim() ) { ID1 ID,STRING1,STRING2,INDEX+1 INDEX, STRING1.Split({''})[INDEX].Trim() DIF1, STRING2.Split({''})[INDEX].Trim() DIF2 } ID STRING1 STRING2 INDEX DIF1 DIF2 -- ---------------- ---------------- ----- ---- ---- 3 SSTTUUVV SSTTUUXX 4 VV XX 4 AAYYYYPQQRDD AAPPPPWRQRDD 2 YYYY PPPP 4 AAYYYYPQQRDD AAPPPPWRQRDD 3 PQ WR Coming from sql you should think of each set of matching parenthesis (indented) as a derived table. Unlike sql, a derived table in D4 does not start with a 'select' statement. In D4 it's just the opposite of sql. A table is derived and then you specify the columns you want. Which way makes more sense? In sql you pay for the meal before you even order it:) Also note in D4 you don't have to give the derived able a name (alias). In D4 things are much cleaner and more logical. I hope you found this interesting. And there's plenty more to come:)
Subscribe to:
Posts (Atom)
