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

[Ramesh Reddy (JIRA)]

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

Ramesh Reddy commented on TEIID-2578:
-------------------------------------

{quote} - 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. {quote}

Yes, that would be good. I can enter a JIRA for this. What about procedures? I could not think of way to wedge those into the SQL/MED's IMPORT statement.

{quote}- 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.{quote}

My thinking is if the refresh of metadata is needed then the IMPORT command needs to be executed again, if the specific tables are defined in the IMPORT and target schema already has the tables then a update of the metadata is performed on on those tables, any new ones will be added. Some of the metadata loading based on the knowledge that persisted VDB always has all the metadata. This way the IMPORT failures or data source non-availability issues are handled when user is actually performing the task, not at other times like start of the server. I consider the usage of NativeMetadataRepository equal to some one running the DDL ad-hoc.

{quote}- 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.{quote}

Yes, that is true. Since I was starting out with file I kept this to a minimal interface. My current thinking if we go further with relational store persistence (I am also thinking Neo4J may be perfect) then this need to be expanded to finer API such that we are reacting to individual events rather then whole DB. When this is in a file, I did wanted to keep it to a single file, so that all the contents are there, that is one reason I added comments to the persisted file separating the Schema for easier finding. Also, I am not expecting anyone to directly edit these files, but if at all these make into any SCM, then that is a possible pattern users might do.

{quote}- 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.{quote}

sure, I can revert that. I even had "Type" column quoted, opted later to revert that. I have to say Type keyword gave me lot of issues with testcases.

{quote}- 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.{quote}

The way I have it right now is, user will only be able to edit VDB that is created through "CREATE DATABASE" construct, where I have the metadata of VDB available in the DatabaseStore object. Also when one issues an "Alter" I do re-validate to make sure that is allowed. As per session, I wired such that I flush out the state of the session, but keep the connection during the VDB modifications. I thought that covers issue, and gives that seamless continuity. Do you see issues with that?
 
We don't recommend anyone editing in production anyway. When VDB is dropped or re-created using "DROP DATABASE/CREATE DATABASE" then sessions behave like they do currently. IMO, AutoFailover=true is fine but, we need to make that default for editing VDBs,  that is users expect anyway when they replace the VDBs with same name and version.

{quote}- 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.{quote}

The convert utility is the current "get-schema" method on the Admin API. It is always associated with the running server. I am basing the convert on running VDB metadata, rather than the deployment time resolution of metadata of VDB. Inline with IMPORT comment above.

{quote}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.{quote}

yes, I would leave DDL out of procedure and dynamic sql for now. Enough issues to tackle. :)
 
As per the JDBC/ODBC,  hmm, I like it. Need to think about this little more. Reworking to make command based may give me a way to implement "undo" which I have been not happy with in current design.  If I go that route, I need to put more thought into command pattern design for all DDL commands. Also, since I have been validating the whole VDB each time, that is going to be an issue. If I have the dependency tree for a single table or procedure, then I can put together "selective" validation where I do not need to write the command into  store before validating, thus preserving the previous state of the VDB and undo operation. (cloning the entire Database object I am considering as expensive operation, otherwise it would have been easy to do)

{quote}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.{quote}

Yes, this is an issue. IMO we can piggy back on the ALTER grant for this, once we have the connection working over the JDBC I can take a look at this.


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