There are a couple of places in the docs where the term "DML" is used,
but it is not defined anywhere. What exactly does it stand for and how
would you define it?

                    - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Fri, 11 Jun 1999 15:38:38 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
"DML"
> There are a couple of places in the docs where the term "DML" is used,
> but it is not defined anywhere. What exactly does it stand for and how
> would you define it?

Hmm. Thanks to Don and Jay for a definition. The def ("Data
Manipulation Language") was what I thought, but I find it confusing
that it is used to refer to a subset of "SQL", which also has
"Language" in the acronym.

Would it be acceptable to replace "DML statement" with something like
"data-altering statement"? The phrase shows up in only two places in
our ~700 pages of docs and out of context it doesn't seem to add
value...

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Fri, 11 Jun 1999 16:28:00 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] "DML"
>
>Hmm. Thanks to Don and Jay for a definition. The def ("Data
>Manipulation Language") was what I thought, but I find it confusing
>that it is used to refer to a subset of "SQL", which also has
>"Language" in the acronym.

NO!!!! SQL doesn't stand for *anything*! (Though common practice calls
it Structured Query Language, it was officially called SQL because the
original name of the language was copyrighted.)

SQL is composed of three languages (DML, DDL -- Data Definition Language,
and some TLA that I've forgotten).

(OK, I admit to being persnickity about this.)  :)
-- 
D. Jay Newman                   ! For the pleasure and the profit it derives
jay@sprucegrove.com              ! I arrange things, like furniture, and
http://www.sprucegrove.com/~jay/   ! daffodils, and ...lives.  -- Hello Dolly

Fri, 11 Jun 1999 12:46:24 -0400 (EDT)
"D. Jay Newman" <jay@sprucegrove.com>
Re: [HACKERS] "DML"
At 04:28 PM 6/11/99 +0000, Thomas Lockhart wrote:

>Would it be acceptable to replace "DML statement" with something like
>"data-altering statement"? The phrase shows up in only two places in
>our ~700 pages of docs and out of context it doesn't seem to add
>value...

It would probably be OK.  DML seems to be one of those terms of
art, not using it would seem odd to db geeks but they'd understand
what you're saying.  Using it is incomprehensible to the rest of
us.  So I'd vote for writing something non-experts can understand.

Experts don't need your docs anyway :)



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, and other goodies at
  http://donb.photo.net

Fri, 11 Jun 1999 09:49:44 -0700
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] "DML"
On Fri, 11 Jun 1999, Don Baccus wrote:

> At 04:28 PM 6/11/99 +0000, Thomas Lockhart wrote:
> 
> >Would it be acceptable to replace "DML statement" with something like
> >"data-altering statement"? The phrase shows up in only two places in
> >our ~700 pages of docs and out of context it doesn't seem to add
> >value...
> 
> It would probably be OK.  DML seems to be one of those terms of
> art, not using it would seem odd to db geeks but they'd understand
> what you're saying.  Using it is incomprehensible to the rest of
> us.  So I'd vote for writing something non-experts can understand.

I dunno about that one Don.  DML and its cousins (DDL and DCL) are
very much in common usage these days.  DDL (Data Definintion Language)
would be CREATE TABLE and so on whilst DML is INSERT INTO, UPDATE
and DELETE FROM...DCL (Data Control Language) usually deals with
permissions (GRANT/REVOKE) and often gets lumped in with DDL.

If you have a look at any comprehensive text that tells neophytes
(like yers truly) how to get a handle on SQL the subsets are defined
and referred to by their acronyms (e.g., Groff and Weinberg, 'LAN
Times Guide to SQL' which I keep handy...)

I'm not sure why they originally split up SQL but I know this:
proprietary databases like PROGRESS that claim to `support'
SQL generally don't support *all* the subsets.

PROGRESS, for example, does not support DCL at all.  One must use
the `data dictionary', an awkward user interface.  It claims to
support DDL but you can't access the tables you make with DDL cmds
via the dictionary.  However, DML is fairly well supported, for what
that's worth...



------- North Richmond Community Mental Health Center -------
Anyway, hope I haven't muddied the waters but I wanted to say that
even non-SQL databases use these terms...and almost all SQL textbooks
cover them in detail.

Cheers,
Tom

Thomas Good                       MIS Coordinator, Senior DBA
Vital Signs:                  tomg@ { admin | q8 } .nrnet.org
                                          Phone: 718-354-5528  
                                          Fax:   718-354-5056  
                              
/* Member: Computer Professionals For Social Responsibility */ 


Fri, 11 Jun 1999 13:12:56 -0400 (EDT)
Thomas Good <tomg@nrnet.org>
Re: [HACKERS] "DML"
On Fri, 11 Jun 1999, Don Baccus wrote:

> Hmmm...it appears "The Practical SQL Handbook" gets it wrong, 
> then, as it lumps "select" with data modification statements
> when it makes its breakdown between DML, data definition, 
> and what they call data administration (clearly they mean
> the same things as your DCL definition, i.e. grant/revoke
> type stuff).
> 
> And the AOLServer guys get it right as they talk about 
> "ns_db dml" working on insert/update/delete and "also
> data definition" statements, i.e. they recognize the
> difference in their documentation.
> 
> OK, in the DML, DCL, and DDL decomposition of things, just what
> *is* a select statement?

Hee hee...according to Guy Harrison who wrote 'Oracle SQL High 
Performance Tuning' (a great book) SELECT is NOT part of DML.

It is a QUERY.  
Harrison separates SELECT from DML because it does not alter data.

Is this getting murkier or do I really need some coffee?
 
> You've given a simple definition of the decomposition, why
> not bottle it and pour it into the docs?

I would give it a try, if Thomas wanted it...usually tho I sit back
and try to learn from following this list.  Anyway the two books that
I've come to rely on are literally on my coffee table at home, so 
maybe tonight I'll have a pint and try to write up a coherent def.

Tom

------- North Richmond Community Mental Health Center -------

Thomas Good                                   MIS Coordinator
Vital Signs:                  tomg@ { admin | q8 } .nrnet.org
                                          Phone: 718-354-5528  
                                          Fax:   718-354-5056  
                              
/* Member: Computer Professionals For Social Responsibility */ 


Fri, 11 Jun 1999 13:36:26 -0400 (EDT)
Thomas Good <tomg@nrnet.org>
Re: [HACKERS] "DML"
At 01:12 PM 6/11/99 -0400, Thomas Good wrote:

>I dunno about that one Don.  DML and its cousins (DDL and DCL) are
>very much in common usage these days.  DDL (Data Definintion Language)
>would be CREATE TABLE and so on whilst DML is INSERT INTO, UPDATE
>and DELETE FROM...DCL (Data Control Language) usually deals with
>permissions (GRANT/REVOKE) and often gets lumped in with DDL.

>If you have a look at any comprehensive text that tells neophytes
>(like yers truly) how to get a handle on SQL...

Hmmm...it appears "The Practical SQL Handbook" gets it wrong, 
then, as it lumps "select" with data modification statements
when it makes its breakdown between DML, data definition, 
and what they call data administration (clearly they mean
the same things as your DCL definition, i.e. grant/revoke
type stuff).

And the AOLServer guys get it right as they talk about 
"ns_db dml" working on insert/update/delete and "also
data definition" statements, i.e. they recognize the
difference in their documentation.

OK, in the DML, DCL, and DDL decomposition of things, just what
*is* a select statement?

You've given a simple definition of the decomposition, why
not bottle it and pour it into the docs?

...

>PROGRESS, for example, does not support DCL at all.  One must use
>the `data dictionary', an awkward user interface.

Barf

>  It claims to
>support DDL but you can't access the tables you make with DDL cmds
>via the dictionary.

And Codds hasn't struck them dead with lightning? :)



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, and other goodies at
  http://donb.photo.net

Fri, 11 Jun 1999 11:28:41 -0700
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] "DML"
At 01:36 PM 6/11/99 -0400, Thomas Good wrote:

>Hee hee...according to Guy Harrison who wrote 'Oracle SQL High 
>Performance Tuning' (a great book) SELECT is NOT part of DML.

>It is a QUERY.  
>Harrison separates SELECT from DML because it does not alter data.

>Is this getting murkier or do I really need some coffee?

Murkier :)

OK, so SQL can be decomposed into: queries, DML, DDL, DCL.

The queries are ... DQL? :)

> Anyway the two books that
>I've come to rely on are literally on my coffee table at home, so 
>maybe tonight I'll have a pint ....

Taking my "bottle and pour it" advice literally, I see!



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, and other goodies at
  http://donb.photo.net

Fri, 11 Jun 1999 12:42:04 -0700
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] "DML"
Thus spake Thomas Good
> I'm not sure why they originally split up SQL but I know this:

Actually, I think it was the other way around.  The term SQL didn't
come into use until DDL, DML and DCL had been in common use.  I'm
pretty sure Date didn't mention SQL in his original paper.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Fri, 11 Jun 1999 22:34:46 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] "DML"
Thus spake D'Arcy J.M. Cain
> Thus spake Thomas Good
> > I'm not sure why they originally split up SQL but I know this:
> 
> Actually, I think it was the other way around.  The term SQL didn't
> come into use until DDL, DML and DCL had been in common use.  I'm
> pretty sure Date didn't mention SQL in his original paper.

Oops.  That would be E.F. Codd, not Chris Date.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Fri, 11 Jun 1999 22:56:18 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] "DML"
Thomas Good wrote:
> 
> > OK, in the DML, DCL, and DDL decomposition of things, just what
> > *is* a select statement?
> 
> Hee hee...according to Guy Harrison who wrote 'Oracle SQL High
> Performance Tuning' (a great book) SELECT is NOT part of DML.
> 
> It is a QUERY.
> Harrison separates SELECT from DML because it does not alter data.

But what would you call yer query when the tables have rules/triggers
attached that do alter data ? A DML-savvy QUERY ;)

BTW, where do all the triggers and rules fall anyway, or ar they a
different subset - maybe Data Behaviour Language (DBL)
 
> Is this getting murkier or do I really need some coffee?

Maybe next we should try to divide english into sublanguages ?
 English for Order Giving - EOG
 English for Describing Things - EDT
 Englisg for Discussing SQL Sublanguages - EDSQLL

------------------
Hannu

Sat, 12 Jun 1999 13:27:11 +0300
Hannu Krosing <hannu@trust.ee>
Re: [HACKERS] "DML"
Thomas Lockhart wrote:
> 
> There are a couple of places in the docs where the term "DML" is used,
> but it is not defined anywhere. What exactly does it stand for and how
> would you define it?

I used it for
SELECT, INSERT, DELETE, UPDATE, FETCH and COPY_TO statements.
Seems that I was wrong using "DML" for all of them. 

They are statements for data management and querying.
Server computes snapshot only for them. 
Anytime when I used "DML" in docs it was related to this,
i.e. to the time of snapshot calculation.

Vadim

Sat, 12 Jun 1999 19:15:14 +0800
Vadim Mikheev <vadim@krs.ru>
Re: "DML"
On Sat, 12 Jun 1999, Hannu Krosing wrote:

> But what would you call yer query when the tables have rules/triggers
> attached that do alter data ? A DML-savvy QUERY ;)
> 
> BTW, where do all the triggers and rules fall anyway, or ar they a
> different subset - maybe Data Behaviour Language (DBL)

Right.  Well, in the interests of annoying Tom Lockhart further...
I've attempted to define SQL and its subsets.  Preliminary apologies
are apprently offered to Thomas, Hannu, Don, D'Arcy and Vadim (who
seems to be cc'd on this foolishness).

Here 'tis:

Conversational SQL - A Gratuitous Glossary
------------------------------------------

It would appear that one could blame E. F. ('Ted') Codd for
the current state of confusion regarding the various subsets
(DML-DDL-DCL-Queries, et al.) of the SQL language.  Of course,
he is probably not responsible...in any event:

In June, 1970 Codd presented a paper on 'A Relational Model of
Data For Large Shared Data Banks.'  This act set in motion
a chain reaction the result of which was the preeminence of
relational databases and their primary interface - the language
known as `SQL'.  It also set in motion the entire process of
developing acronyms to help with the obfuscation of any remaining
points of clarity surrounding the language whose very name is 
the subject of controversy.  (The fact that a language which does 
not have a procedural nature is termed `structured' is an apparently 
oxymoronic bit of wordplay along the lines of `English Grammar.')

The original query language that accompanied IBM's System R database
was known as SEQUEL  (Structured English QUEry Language).  IBM
is often blamed for later truncating the name to SQL.  In any case,
SQL (pronounced either as sequel or S-Q-L) stuck.  At some point
in the birthing process, SQL was either carved up into subsets
or perhaps borne of the concatenation of various subsets which may
or may not have existed within the vernacular of SQL's mischeivous
midwives.

The various subsets are the source of much confusion, apparently 
by design.  Although this point is also open to disputation.

The subsets would seem to be (and these are widely disputed and 
misrepresented, especially by this writer):

The QUERY:  the basic building block of SQL.  The SELECT statement
is the quintessential QUERY.  Except, as was pointed out by Hannu
Krossing, when the SELECT accesses a TABLE that has rules or triggers
attached...in this instance (and probably others as well) the SELECT
comes dangerously close to DML (a notorious subset covered below).
However, this is probably an inaccurate definition as rules, triggers
and stored procecures are not actually part of core SQL, if there
is such a thing.  What are they?  Procedural Extensions...

Moving along nicely now we come to Data Manipulation Language, another
subset of SQL which alleges to provide some functionality whilst defying
definition.  DML, as it is known, is composed of INSERT INTO, UPDATE
and DELETE FROM statements.  DML manipulates data unlike SELECT QUERIES
which manipulate data.  The difference apparently is that DML can alter
data, while SELECT cannot unless the table it is querying has procedural
extensions somehow glued to it.  Or if it is part of an DML statement as
in an INSERT INTO y SELECT * FROM x;  statement or a CREATE TABLE AS SELECT
statement.  Although there is tremendous overlap here we cling to this
arbitary cutting up in the interests of clarity.

The next subset would seem to be DDL.  Data Definition Language appears to
govern the creation and destruction of database objects.  It includes
CREATE TABLE, CREATE SEQUENCE, CREATE INDEX and similar statements.  It 
also includes the destructive counterparts:  DROP TABLE and so on.
Unfortunately DROP ACRONYM is apparently not implemented.  As DDL seems
to be clearly defined by comparison with DML and SELECT queries it is
perhaps time to introduce some ambiguity.

DCL (Data Control Language) may or may not exist.  It may or may not be
a part of DDL, depending on which erudite text one reads.  It may also be
colloquially termed `data administration language' or something similar.
Perhaps not.  In any case it seems to contain the GRANT and REVOKE 
statements which offer the illusion of control in a language which is 
obviously out of control.

EOT!
-------------------------------------
Tom Good, New York City, 12 June 1999

------- North Richmond Community Mental Health Center -------

Thomas Good                                   MIS Coordinator
Vital Signs:                  tomg@ { admin | q8 } .nrnet.org
                                          Phone: 718-354-5528  
                                          Fax:   718-354-5056  
                              
/* Member: Computer Professionals For Social Responsibility */ 


Sat, 12 Jun 1999 08:23:49 -0400 (EDT)
Thomas Good <tomg@nrnet.org>
Re: [HACKERS] "DML"...CREATE ACRONYM statement
I like it! :)

Of course, if I include it in the docs then it would be much more
effective if I also include the previous 20 mail messages on the
subject...

                   - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Sat, 12 Jun 1999 14:45:49 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] "DML"...CREATE ACRONYM statement

On 12-Jun-99 Hannu Krosing wrote:
> 
> Maybe next we should try to divide english into sublanguages ?
>  English for Order Giving - EOG
>  English for Describing Things - EDT
>  Englisg for Discussing SQL Sublanguages - EDSQLL

And don't forget my favorite:

   English for Confusing Things - ECT

not to be confused with it's counterpart:

   English That Confuses - ETC


Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================



Sat, 12 Jun 1999 10:53:23 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] "DML"
On Sat, 12 Jun 1999, Thomas Lockhart wrote:

> I like it! :)
> 
> Of course, if I include it in the docs then it would be much more
> effective if I also include the previous 20 mail messages on the
> subject...
> 
>                    - Thomas

Ok...ok!  I sheared off abit of the fluff and here it is...provided
it meets with everyone's approval you're free to do with it what you
will.

Cheers,
Tom

-------------------------------------------------------------

SQL subsets - QUERIES, DML, DDL and DCL
---------------------------------------

The original query language that accompanied IBM's System R database
was known as SEQUEL  (Structured English QUEry Language).  IBM
later truncated the name to SQL - pronounced either as sequel 
or as the phonetic eS-Que-eL.  SQL has one basic operation and
3 subsets:

The QUERY - SQL has one primordial operation known as a QUERY.  
'SELECT' is the verb in the basic SQL query.  Queries can become rather
complex and may include elements of DML (Data Manipulation Language).
Queries may also access tables that contain TRIGGERs and RULEs
thus invoking more complex database operations.  In its most elemental
form, however, a SELECT statement is a simple QUERY.

DML - the Data Manipulation Languge subset of SQL - is composed of 
INSERT INTO, UPDATE and DELETE FROM statements.  DML manipulates data
(altering it) while SELECT, at least in its purest form, does not.

DDL - the Data Definition Language subset of SQL - is a collection of
statements that create and destroy database objects.  It includes
CREATE TABLE, CREATE SEQUENCE, CREATE INDEX and similar statements.  It 
also includes the destructive counterparts:  DROP TABLE and so on.
(Unfortunately DROP ACRONYM is apparently yet to be implemented.)

DCL - the Data Control Language subset of SQL - is that part of the SQL
language that controls user access.  It contains the GRANT and REVOKE 
statements which govern permissions.

------- North Richmond Community Mental Health Center -------

Thomas Good                                   MIS Coordinator
Vital Signs:                  tomg@ { admin | q8 } .nrnet.org
                                          Phone: 718-354-5528  
                                          Fax:   718-354-5056  
                              
/* Member: Computer Professionals For Social Responsibility */ 


------- North Richmond Community Mental Health Center -------

Thomas Good                                   MIS Coordinator
Vital Signs:                  tomg@ { admin | q8 } .nrnet.org
                                          Phone: 718-354-5528  
                                          Fax:   718-354-5056  
                              
/* Member: Computer Professionals For Social Responsibility */ 


Sat, 12 Jun 1999 14:32:42 -0400 (EDT)
Thomas Good <tomg@nrnet.org>
Re: [HACKERS] "DML"...CREATE ACRONYM statement