Subject: [media-types] Fwd: New media registration request: application/sql

From: Yakov Shafranovich <[email-masked]>
Date: Thu, 15 Nov 2012 22:43:39 -0500

Hi,

I wanted to request the registration of the "application/sql" media
type to be used with SQL files. My draft is here:

http://www.ietf.org/id/draft-shafranovich-mime-sql-01.txt

The standard for SQL itself is published by ISO as ISO/IEC 9075:2011.

Thanks,
Yakov

---

From: Yakov Shafranovich <[email-masked]>
Date: Thu, 15 Nov 2012 22:46:55 -0500

Here is the actual template:

2.  Media Type Registration Application

   This section provides the media type registration application which
   will be submitted to IANA after IESG approval of this document.

   Type name: application

   Subtype name: sql

   Required parameters: N/A

   Optional parameters: N/A

   Encoding considerations:

      While SQL uses US-ASCII by default, some implementations may use
      other encodings including [Unicode].

   Security considerations:

      Being that SQL is a full fledged programming language and may
      include embedded source code for other programming languages, it
      may be used to transfer malware, viruses and other malicious
      payloads.  Implementors and users are encouraged only to exchange
      SQL files among trusted parties and utilize all available security
      measures such as restricted access, virus scanners, etc. to
      minimize risk.

   Interoperability considerations:

      While a single standard exists ([ISO.9075.2011]), vendor
      implementations of the standard vary significantly.  Implementors
      and users should make sure that the exchanged SQL files match to
      the specific database/tool and version that they are using.

   Published specification:

      The most recent specification of SQL is available in
      [ISO.9075.2011].

   Applications that use this media type:

      Databases and related tools

   Additional information:

      Magic number(s): N/A

      File extension(s): sql

      Macintosh File Type Code(s): TEXT

   Person & email address to contact for further information:

      Yakov Shafranovich <[email-masked]>

   Intended usage: COMMON

   Restrictions on usage: N/A

   Author/Change controller: IESG

---

From: "Martin J. Dürst" <[email-masked]>
Date: Fri, 16 Nov 2012 14:25:58 +0900

Hello Yakov,

>        other encodings including [Unicode].

How are these encodings indicated? If there's no way to indicate them, 
that's a bad idea (interoperability failure), and should be avoided.

Regards,   Martin.

>     Person&  email address to contact for further information:
>
>        Yakov Shafranovich<[email-masked]>
>
>     Intended usage: COMMON
>
> _______________________________________________
> media-types mailing list
> [email-masked]
> https://www.ietf.org/mailman/listinfo/media-types
>

---

From: Yakov Shafranovich <[email-masked]>
Date: Fri, 16 Nov 2012 08:04:07 -0500

> that's a bad idea (interoperability failure), and should be avoided.

Thanks for the comment!

Would adding "charset" as an optional parameter to indicate encoding
help to resolve this?

Yakov

---

From: "Eric Prud'hommeaux" <[email-masked]>
Date: Fri, 16 Nov 2012 09:51:18 -0500

* Yakov Shafranovich <[email-masked]> [2012-11-16 08:04-0500]
> Would adding "charset" as an optional parameter to indicate encoding

I believe this would be one path to ameliorating the pain of issuing
DDL and SQL requests. Creating UTF-8 tables and insert non-ascii data
is done entirely differently with e.g. Postgres, Oracle and MySQL. I
would applaud any efforts to improve this.

The downside of permitting a charset parameter with an arbitrary
charset parameter is that server interfaces must then accept an
arbitrary set of character encodings, passing them on to the server in
the same encoding, if the server supports that encoding¹, or
transcoding to a supported encoding.  To give a scale of this
implementation burden,
<http://en.wikipedia.org/wiki/Character_encoding#Common_character_encodings>
describes more than 50 as "common" character encodings.

¹ This is complicated by the fact that tables created with one
encoding may not support INSERTs, DELETEs, SELECTs in another
encoding.

The XML approach to solving this was to mandate that UTF-8 and UTF-16
MUST be supported (see <http://www.w3.org/TR/xml/#char32>). This
complicates parsers by demanding relatively complex encoding detection
heuristics and byte-order markers.

A simpler approach was used by e.g. text/turtle, which mandated one
encoding:
[[
Optional parameters:
    charset — this parameter is required when transferring non-ASCII
              data. If present, the value of charset is always UTF-8.
]] — <http://www.w3.org/TR/turtle/#sec-mediaReg>

This simplifies parsing and reduces (or frequently liminates) the
transcoding requirements.

Considering these schemes in light of an SQL agent G issuing a query
to a service S, the choices are:

  do nothing: G can only count on S to handle requests entirely in
  ASCII. Any non-ASCII treatment would be outside of the spec.

  arbitrary charset: G must find out by external means what encodings
  are supported by S. At that point, non-ASCII messages are in-spec
  and indicated by the charset parameter.

  guaranteed charsets: G can count on S accepting the guaranteed
  encodings. Other encodings would have to be negotiated as above.

  fixed charset: G must submit requests the appropriate encoding. (If
  G is accepting user input from e.g. KOI8R, the burden of transcoding
  is on G.) 

If not already apparent, I support the fixed charset approach.
I also urge you to register text/sql instead of application/sql.

-- 
-ericP

---

From: Yakov Shafranovich <[email-masked]>
Date: Fri, 16 Nov 2012 14:45:10 -0500

> If not already apparent, I support the fixed charset approach.

Thanks for that information. I think I am going to do a survey of what
the major database systems in order to see if it is feasible to settle
on a fixed character set or a subset of encodings.

> I also urge you to register text/sql instead of application/sql.

My impression has been that programming languages are usually
registered under the "application" tree as opposed to the "text" tree.
As per (http://tools.ietf.org/html/draft-ietf-appsawg-media-type-regs-14#section-4.2.5):

"The "application" top-level type is to be used for discrete data that
do not fit under any of the other type names, and particularly for
data to be processed by some type of application program."

and

"Expected uses for the "application" type name include but are not
limited to file transfer, spreadsheets, presentations, scheduling
data, and languages for "active"(computational) material."

A quick perusal of the "text" tree shows that other than CSS and HTML,
most languages are registered under "application". The closest example
to SQL is perhaps SPARQL which is also registered under "application".

Yakov

---

From: "Eric Prud'hommeaux" <[email-masked]>
Date: Fri, 16 Nov 2012 16:11:48 -0500

* Yakov Shafranovich <[email-masked]> [2012-11-16 14:45-0500]
> on a fixed character set or a subset of encodings.

I suspect your thinking is that picking a media type that most SQL
servers already support is likely to minimize the transcoding required
when SQL servers or 3rd party tools accept text/sql or app/sql queries.

> to SQL is perhaps SPARQL which is also registered under "application".

Yes, which I regret.

A common metric is "would your average geek want to edit this content
with a generic text editor?". Another is "would your average geek want
this content saved to disk vs. rendered as text in a web browser".
(Some people extend that to "would your average web user ...", though
they are unlikely to be clicking on a <see the SQL> link.) It drives
me crazy that the primary consumers of some media have to go hunt for
it in a Downloads directory rather than simply seeing it displayed.
For example, the most likely person to click on a app/sparql link is
someone who's expecting to read that SPARQL.

-- 
-ericP

---

From: Yakov Shafranovich <[email-masked]>
Date: Sat, 17 Nov 2012 21:45:33 -0500

> when SQL servers or 3rd party tools accept text/sql or app/sql queries.

We are sort of limited here with what we can do. Registration of a
media type that references an existing standard cannot mandate what
encoding should or should not be used that would conflict with that
standard.

I actually dug into the SQL standard and found a section discussing
encodings (ISO/IEC 9075-2:2011, section 4.2.4). It seems that the ISO
standard for SQL mandates the following encodings to be supported by
all implementations:

o GRAPHIC_IRV is the character encoding form in which the coded
representation of each character is specified in [ISO646].
o LATIN1 is the character encoding form specified in [ISO8859-1].
o ISO8BIT is the character encoding form specified in [ISO8859-1],
augmented by [ISO6429]. When restricted to the LATIN1 characters, it
is the same character encoding form as LATIN1.
o UTF32 is the character encoding form specified in [ISO10646], in
which each character is encoded as four octets.
o UTF16 is the character encoding form specified in [ISO10646], in
which each character is encoded as two or four octets.
o UTF8 is the character encoding form specified in [ISO10646], in
which each character is encoded as from one to four octets.

It also states that:

The precedence of character encoding forms applicable to the UCS
character repertoire and defined in this part of ISO/IEC 9075 is:

UTF8 ≺ UTF16 ≺ UTF32

There are also three implementation-specific encodings which aren't
relevant here (SQL_CHARACTER, SQL_TEXT and SQL_IDENTIFIER)

It sounds like the following ones are supported by all implementations
and should be portable:

US-ASCII
ISO8859-1
UTF8
UTF16
UTF32

It sounds like we can specify the optional "charset" parameter and
allow US-ASCII to be the default. We can also list the other ones as
the ones that would be possible choices and are portable across all
implementations. Any other charset would be supported only if the
implementation in question supports it. I would also add a section to
the draft discussing this entire thing.

>> > I also urge you to register text/sql instead of application/sql.
...
> someone who's expecting to read that SPARQL.

Unfortunately, I am constrained by the current registration procedures
as defined in RFC 4289 and draft-ietf-appsawg-media-type-regs-14. From
those it seems that the best place for this media type would be under
the "application" tree.

Yakov

---

From: Liam R E Quin <[email-masked]>
Date: Sat, 17 Nov 2012 22:30:06 -0500

> this content saved to disk vs. rendered as text in a web browser".

It used to be that text/* was subject to CR LF processing and to
translation of encodings by proxies.  This is part of why XML moved from
text/xml to application/xml - a rewriting proxy could not be expected to
change the XML encoding declaration inside the data stream.

(I say "used to be" because I'm not sure it's as common these days)

However, given that the registration already says that SQL should only
be exchanged over a trusted connection, I don't think proxies come into
consideration.

>  It drives

This is a function of browsers, not of MIME content type headers.
For example, I frequently have to empty my Download "folder" out because
every PDF file gets saved there even if I didn't want to keep it.

Liam

-- 
Liam Quin - XML Activity Lead, W3C, http://www.w3.org/People/Quin/
Pictures from old books: http://fromoldbooks.org/
Ankh: irc.sorcery.net irc.gnome.org freenode/#xml
Co-author, 5th edition of "Beginning XML", Wrox, Summer 2012

---

From: "Eric Prud'hommeaux" <[email-masked]>
Date: Sun, 18 Nov 2012 11:15:47 -0500

* Liam R E Quin <[email-masked]> [2012-11-17 22:30-0500]
> change the XML encoding declaration inside the data stream.

I don't see this as an argument agasint text/sql (or text/sparql-query)
in that SQL has no built-in media type label. A generic proxy would
could do the right thing without having to do any subtype-dependent
parsing/manipulation.

> (I say "used to be" because I'm not sure it's as common these days)

Yeah, HTML shouldn't be in text/ 'cause of this, c.f.
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

> be exchanged over a trusted connection, I don't think proxies come into

I was going to bring that up as well. The current Security
considerations includes:
[[
Implementors and users are encouraged only to exchange SQL files among
trusted parties
]]
which is pretty much the state of the art today. Whenever I want to
execute an SQL query or update, I find a db-specific driver, issue a
ODBC/JDBC/DBD or db-specific connect call, concoct some DB-specific
SQL, and parse the results in a DB-specific way. There's a thriving
industry making a living off of these incompatibilities and there's
no competing pressure towards a standard protocol with compliant SQL.

A trivial use of an SQL media type would be to "view source". In this
case, anyone would be encouraged to *exchange* SQL, but be judicious
about executing it.

A real advancement would be to define a media type which encouraged
either 3rd party tools or the RDBs themselves to consume valid SQL
passed via a small number (potentially one) of encodings. This would
encourage implementors to add alternate parsers (I added a SPARQL
parser to MySQL and the Drizzle folks want to do the same for a
compliant SQL parser) and generally reduce the burden of using SQL.

Of course, vendor-specific protocols tend to be nicely optimized, but
most of us don't need such bit squeezing, and no one needs it on day
one of a project.

> every PDF file gets saved there even if I didn't want to keep it.

I agree that your miscreant browser is doing the wrong thing, but I
think it's only applicable here as an example of one annoyance that
could emerge from putting sql under the application/ tree. (Presumably
your browser has a handler for application/pdf, saves the file, calls
the handler, and then fails to reap the file.)

-- 
-ericP

---

From: Ned Freed <[email-masked]>
Date: Sun, 18 Nov 2012 10:26:40 -0800 (PST)

> translation of encodings by proxies.

Depending on the protocol/transport, it still is. 

> This is part of why XML moved from

Another issue is that line terminators are supposed to be 0d0a and nothing else
in text types.

> be exchanged over a trusted connection, I don't think proxies come into

But the real question is why would you want for SQL to be under text to begin
with. I really don't see a reason for it. Just register it under application
and be done with it. That also lets you avoid all the issues with charset
parameters.

> every PDF file gets saved there even if I didn't want to keep it.

Exactly. If you want material of a given type to be handled in a particular
way, that's between you and your browser. Types tell you what some thing is,
they don't say how to handle it.

Unfortunately browsers seem to be getting worse in regard to flexible handling
of different types. But if there's something that can be done about it
from a type registration perspective, I for one have no idea what it is.

				Ned

---

From: Yakov Shafranovich <[email-masked]>
Date: Sun, 18 Nov 2012 13:47:29 -0500

> and be done with it. That also lets you avoid all the issues with charset

How would registration under "application" avoid issues with charset?
We would still need to document how encodings are handled.

Yakov

---

From: Ned Freed <[email-masked]>
Date: Mon, 19 Nov 2012 05:13:44 -0800 (PST)

> How would registration under "application" avoid issues with charset?

Application has none of the baggage associated with default behavior in the 
absence of a parameter. See RFC 6657.

> We would still need to document how encodings are handled.

Of course. But you can do that free of any entanglements with the top-level
type.

				Ned