4:44 p.m.
[
https://jira.jboss.org/jira/browse/DNA-37?page=com.atlassian.jira.plugin....
]
Sergiy Litsenko commented on DNA-37:
------------------------------------
From Sergiy:
The difficult part of database metadata mapping is that different vendors using different
concept of database instances - Microsoft/Sybase uses multiple databases as part of single
server instance, Oracle uses single database but multiple schemas. We may review how
database access tools like Aqua Data Studio (based on Java Swing and JDBC) intepreting
such graphs. I'm talking about the root part of such graph - from root node to
let's say table node.
For example:
<root database node>/<sub graph that difficult to map in generic
way>/tables/...
<root database node>/<sub graph that difficult to map in generic
way>/views/...
<root database node>/<sub graph that difficult to map in generic
way>/users/...
We need to think more how we will map subgraph highlighted above onto different db
vendors concepts:
MS SQL/Sybase, etc:
<root database node>/databases/<db name>/user tables/<user
name>.<table name>
<root database node>/databases/<db name>/system tables/sys.<table
name>
<root database node>/databases/<db name>/views/<view name>
Oracle and others similar:
<root database node>/schema/<user name>/tables/<table name>
<root database node>/schema/<user name>/views/<view name>
We may consider merging both styles together or having them separate (both approaches
have advantages and disadvantages). I would prefer
generic, merged concept view - for benefits of DNA-JCR and REST toghether so clients
don't have to change graph based on particular vendor.
From Randall:
Do we really think it's our job to come up
with a single model? Why can't we simply expose what the database exposes? Like you
said, it's mostly at the high-level (what I'd call the organizational level, above
the tables/views/procedures/etc.). How about taking it one step at a time, and focusing
on getting the functionality there to expose the same organizational structure that the
database uses. At some point, we'll probably want to come up with a single approach,
but even then I think we'd want to make it optional (specified in the metadata
connector).
I also think that we should use mixins for the type information (or maybe even node types
that are extensible to allow any properties), and that means we could actually not have
any nodes that exist to collect the types. This is much more along the lines of a So,
Oracle's schema might show up as content:
<db:schema jcr:name="Products">
<db:table jcr:name="PRODUCTS">
<db:column jcr:name="ID" ... />
<db:column jcr:name="NAME" ... />
...
<db:primaryKey ... />
...
</db:table>
<db:table jcr:name="TABLES" jcr:mixins="db:system" ...>
...
</db:table>
<db:view ...>
...
</db:view>
<db:procedure>
...
</db:procedure>
</db:schema>
I've shown that any system table/view/procedure could have an additional
"System" mixin. The great thing about this approach (of using mixins rather
than names in a hierarchy) is that you can more readily build queries to get the content
in any structure you want.
I've used an XML notation; for details about this notation, see the note somewhere
after paragraph 5 in
http://www.jboss.org/file-access/default/members/dna/freezone/docs/0.3/ma....
From Sergiy:
Anyway, for both styles we may add generic metadata subnode about RDBMS capabilities
(that will hold all not specific to database object propertise like
supportsMixedCaseIdentifiers(), storesUpperCaseIdentifiers(),
supportsAlterTableWithAddColumn(), etc):
<root database node>/capabilities
From Randall:
Yup.
<db:capabilities supportsMixedCaseIdentifiers="true"
db:supportsUpperCaseIdentifiers="true" ... />
From Sergiy:
Sub nodes of table, view, etc nodes are pretty easy
to model because they are similir for many databases. I was thinking about something
similar to following (I guess we may have design sessions and may refactor it later):
.../tables/<table name>/data [default primary type, so it can be omitted]
table's data records
.../tables/<table name>/data/record 1
.../tables/<table name>/data/record N
From Randall:
Let's table the data discussion for the
moment. I've got very different ideas about how that will work, since this is too
flat for tables with millions of rows. We may want to do something like this for a
sampling of the data (something that's very useful when you're dealing with
metadata, but even then there are enterprise security issues with this).
From Sergiy:
.../tables/<table name>/columns/<column
name>
.../tables/<table name>/constraints/<constraint name>
.../tables/<table name>/indexes/<index name>
From Randall:
As I mentioned above, this is more
straightforward.
Federate schema information from relational sources
---------------------------------------------------
Key: DNA-37
URL: https://jira.jboss.org/jira/browse/DNA-37
Project: DNA
Issue Type: Feature Request
Components: Connectors
Reporter: Randall Hauch
Assignee: Sergiy Litsenko
Priority: Minor
Fix For: 0.4
Create a connector for the federation engine that contributes and exposes the database
schema information for a relational database (via JDBC) into the federated repository.
This would make it possible to have a repository provide information about the databases
in an organization. One choice would be whether to import (and copy) the schema
structure, or to provide access to it in real time.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.jboss.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
6353
days inactive
6353
days old
0 comments
1 participants
participants (1)
-
Sergiy Litsenko (JIRA)