[teiid-issues] [JBoss JIRA] (TEIID-2578) add/remove schema elements

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13290707#comment-13290707 ] 

Steven Hawkins commented on TEIID-2578:
---------------------------------------

- It looks like we need to put some thought into general support of include/exclude tables across all translators.  Just doing after the fact may require quite additional repair such dropping foreign keys.
- Correct me if I'm wrong, but the concept of the exportable DDL form and the internal repository DDL seem to be intermingled.  For example we still need to be able to allow a vdb to "refresh" a particular set of imports, so we would need to give the option for export to contain the actual imported metadata or just the equivalent import statements.
- The storage mechanism is only currently effective for lob style storage - do you envision wanting to shred storage into metadata tables?  Also for larger vdbs it seems like we may still want the option of splitting the contents based upon schema.
- The change to the SYS xml procedure may be better as quoting schema so that if someone was using (quoted) named parameters it would still work.
- We still have to terminate the session if the vdb is reloaded - or do a lot more work to determine if the change is allowable (such as with the existing DDL support to alter view, trigger, properties, etc.).  In the simplest case we just need to have the jdbc client use the autoFailover=true connection property.  But in the worst case, such as if the security-domain of the vdb is altered, then the login may no longer be valid.
- The convert vdb utility will only work for vdbs that contain no vdb imports nor source metadata imports, I'm assuming in those cases you expect the user to use a running server.
 
As for using this DDL via JDBC/ODBC, it doesn't seem like too much of a stretch - it would however be difficult to allow general DDL in procedures or dynamic sql for example.   The simplest mechanics would be to use a connection property to determine if we are in an editing mode, then on a parsing failure we'd run the statement through the DDL logic (we'd obviously know the vdb/version context).  The issue would be that create table / procedure would need to be against the "current" schema which wouldn't be saved if we simply terminated the connection.  We could just make that part of the client state and implement the Connection.get/setSchema methods - as well as another SET statement override that does the same.  There is also the issue of permissioning however.  Our default assumption with existing vdbs, which is permissions are enforced only if data roles are defined, doesn't seem valid in this paradigm.  In general we need to check the operations against the role system, just as we do currently for things like alter view.  So we may need to have the create vdb operation convey that the vdb is in edit mode, make the user assign data roles, etc.

> add/remove schema elements
> --------------------------
>
>                 Key: TEIID-2578
>                 URL: https://issues.jboss.org/browse/TEIID-2578
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine
>            Reporter: Ramesh Reddy
>            Assignee: Ramesh Reddy
>             Fix For: 9.1
>
>
> Schemas are currently static after load.  Modifications can only happen with restarts or new versions.  We should allow add/drop at runtime.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)