"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

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

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

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 */

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 */

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

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

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.

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.

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

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

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 */

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

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
==========================================================================

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 */