Data type compatibility

[Ян Программист <we...@gmail.com>]

Have some vision about data centric compatibility between databases:


   1. Numeric type precision
      1. If a lot of entries with large numbers should be collected,
      precision for float, decimal are represented threw last digits
      2. As a result of (1) most parts of data is duplicated (in binary
      format duplication is lower, unlike human readable format)
      3. (2) gives a chance to make table space on disk much lower, storage
      I/O more effective after resolving that disadvantage
      4. Most parts of float & decimal based types should be rad from end,
      unlike normal cell entries. This will help in providing a way of making
      SELECT with a lower precision in entries, unlike waiting for a
full-featured
      results, if needed. Extra usable stuff for number comparison tasks; when
      rather lower <-> grater plotting is used for data representation & full
      precision is not critical. Client would later initiate individual entry
      SELECT for detailed, precision critical analyzes
      5. (1)-(4) is good for embedding JavaDB in a microprocessor runtime
      (J2ME), for sing it as an embed database for storing data in measurement
      devices (e..g. factory devices, collecting large data with high precision
      for production quality control)
   2. XML type compatibility
      1. Some kind of XML to SQL, client to server translation, when client
      is doing XML request, and server accepts SQL equivalent. Good for XML
      compatibility with XML type of Oracle, DB2, etc. I arranged that in
      following rules:
         1. Returned data is taken from indexes ONLY
         2. If nested XML tokens are related to entries under transaction at
         request moment - only parent XML structure is returned; transaction
         isolation would isolate tokens from response
         3. Used ONLY in XML based/driven architectures, populated via
         JavaDB entries in a XML ruled way

John

Re: Data type compatibility

[Bryan Pendleton <bp...@gmail.com>]

?? ??????????? wrote:
> So, you feeling OK about sending such a proposal to Google guys? They 
> will forward to Apache people, so I will post proposal to derby-dev 
> mailing list also for conversation in community

That sounds good to me. It's important that all technical discussions about
Derby's implementations happen as a conversation in the community, so
derby-dev is the right place to discuss the issues around building SQLXML support.

Your GSoC application can, and should, refererence the derby-dev mail threads
and the JIRA issues that are used for tracking work for the project. As we
start to get more specific about the particular aspects of JDBC/SQLXML support
that need to be implemented, we can open specific JIRA entries to track each one.

As you can see, there are a number of existing JIRA entries about the XML support
that has been added so far, for example:
  - https://issues.apache.org/jira/browse/DERBY-688
  - https://issues.apache.org/jira/browse/DERBY-1759
Studying these issues in detail should give you a feel for how the process
of developing additional SQLXML support can proceed: identify the areas where
Derby's support is lacking, propose an implementation to enhance Derby in those
areas, respond to community comments and suggestions, develop tests, etc.

Your ideas about the uses of XML and extensions to the XML handling are
great. The most important thing for the Derby community is to implement
a standards-compliant and highly-compatible database, so our first priority
is to ensure that we implement the JDBC and SQLXML specs, but beyond that
we can propose extensions to the XML handling to the Derby community to
see what the reaction is.

It is great that you have looked into the implementations of SQLXML in
Postgres, MySQL, and others; since the goal is compatibility, it will be
very helpful to compare our implementation against others.

Great work getting this far! I think the next step is for you to put together
a GSoC application and be as specific as you can be about the proposed work.

thanks,

bryan

Re: Data type compatibility

[Ян Программист <we...@gmail.com>]

So, you feeling OK about sending such a proposal to Google guys? They will
forward to Apache people, so I will post proposal to derby-dev mailing list
also for conversation in community

By the way - MySQL use special XML mode for client, where needed,
http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html#option_mysql_xml,
instead of normal XML formatting:
<...>
 <VariableName>...</VariableName>
<Value>...</Value>
</....>

I see that like a big disadvantage because of inability of XML validation
threw XMLSchema after retrieving on client

 http://java.sun.com/javase/6/docs/api/java/sql/SQLXML.html: there is
something about XSLT, but that should not be a database feature, nor JDBC
itself. It is pure client stuff (read: code, initiated database requests
threw JDBC). John

Re: Data type compatibility

[Bryan Pendleton <bp...@gmail.com>]

?? ??????????? wrote:
> "Because none of the JDBC-side support for SQL/XML is implemented 
> in Derby, it is not possible to bind directly into an XML value or to 
> retrieve an XML value directly from a result set using JDBC. Instead, 
> you must bind and retrieve the XML data as Java strings or character 
> streams by explicitly specifying the appropriate XML operators, XMLPARSE 
> and XMLSERIALIZE, as part of your SQL queries"

Yes, this is definitely a restriction of the current Derby implementation,
and I can certainly see that it could cause compatibility problems with
applications written against other DBMS systems.

I think this would be a great improvement, to lift these restrictions and
implement the JDBC support for SQL/XML.

If you are interested in working on this project, I think this would be
a very good project to propose for Google Summer of Code. You can find
a lot of information about the SQLXML JDBC interfaces on the web, and
implementing that support for Derby would be a nice new feature for Derby to have.

thanks,

bryan

Re: Data type compatibility

[Ян Программист <we...@gmail.com>]

>
> I think you are describing an approximate numeric data type, in which
> two distinct values would be considered to be equal if the difference
> between them was small. So sometimes when manipulating a value we would
> pay attention to the entire precision, sometimes we would not.
>
> Is that right?
>
You are right. Besides usually precision is lost on the client side, while
data is processed in formulas after retrieval. Because it is a part of
metrology theory to figure out minimal precision loss due formula
calculations. As a result it would be a good perspective to use Derby in
embed, real time Java systems. So there is a good chance to balance
client/storage performance in such a precision driven way

>
>    2. XML type compatibility
>>
>
> I didn't understand this proposal. Derby currently supports an XML
> data type; it is described here:
> http://db.apache.org/derby/docs/10.5/ref/rrefsqljtypexml.html
>
> "Because none of the JDBC-side support for SQL/XML is implemented in Derby,
it is not possible to bind directly into an XML value or to retrieve an XML
value directly from a result set using JDBC. Instead, you must bind and
retrieve the XML data as Java strings or character streams by explicitly
specifying the appropriate XML operators, XMLPARSE and XMLSERIALIZE, as part
of your SQL queries"

But if XML values would be retrievable from indexed only columns
(non-indexed data would not be critical to retrieve; rather would be
retrieved in other client and/or way)... so I was meaning that client
connector would support XML. But you would not have overloads to Derby doing
XMLPARSE/XMLSERIALIZE - Derby engine would feel like it is processing normal
SQL queries

> Do you feel that this data type is not compatible with Oracle or DB2?
>
I remember that, at least, SQL Server has direct XML support, with binding
or something like that. Tell me what support in SQL you see minimal. Besides
customizability level in those databases comes to the skies ;), unlike Derby
for existing XML support ;=(

John






>
> thanks,
>
> bryan
>
>

Re: Data type compatibility

[Bryan Pendleton <bp...@gmail.com>]

Hi John, thanks for the ideas about data type handling.

> Have some vision about data centric compatibility between databases:
> 
>    1. Numeric type precision

I think you are describing an approximate numeric data type, in which
two distinct values would be considered to be equal if the difference
between them was small. So sometimes when manipulating a value we would
pay attention to the entire precision, sometimes we would not.

Is that right?

>    2. XML type compatibility

I didn't understand this proposal. Derby currently supports an XML
data type; it is described here:
http://db.apache.org/derby/docs/10.5/ref/rrefsqljtypexml.html

Do you feel that this data type is not compatible with Oracle or DB2?

thanks,

bryan