new COMMENT-ON SQL statement: review change 1. handle return value of function getSeabaseObjectComment 2. add a new error code : 1033 - CAT_UNABLE_TO_RETRIEVE_COMMENTS 3. add showddl support for sequence comment 4. moved function getSeabaseObjectComment to file CmpSeabaseDDLcommentOn.cpp 5. changed str_sprintf to sprintf
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/0b7e3a35 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/0b7e3a35 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/0b7e3a35 Branch: refs/heads/master Commit: 0b7e3a35bf61c1e28192272c1f7a961f95766a92 Parents: 4bdd4b8 Author: eedy <[email protected]> Authored: Mon Nov 6 18:08:33 2017 +0800 Committer: eedy <[email protected]> Committed: Mon Nov 6 18:08:33 2017 +0800 ---------------------------------------------------------------------- core/sql/bin/SqlciErrors.txt | 2 +- core/sql/sqlcomp/CmpDDLCatErrorCodes.h | 2 +- core/sql/sqlcomp/CmpDescribe.cpp | 80 +++++++++++---- core/sql/sqlcomp/CmpSeabaseDDLcommentOn.cpp | 120 ++++++++++++++++++++++- core/sql/sqlcomp/CmpSeabaseDDLschema.cpp | 6 +- core/sql/sqlcomp/CmpSeabaseDDLtable.cpp | 116 ---------------------- 6 files changed, 185 insertions(+), 141 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/0b7e3a35/core/sql/bin/SqlciErrors.txt ---------------------------------------------------------------------- diff --git a/core/sql/bin/SqlciErrors.txt b/core/sql/bin/SqlciErrors.txt index 8fdd3dc..85bc129 100644 --- a/core/sql/bin/SqlciErrors.txt +++ b/core/sql/bin/SqlciErrors.txt @@ -32,7 +32,7 @@ 1030 ZZZZZ 99999 BEGINNER MINOR DBADMIN The HBase name has a length of $0~Int0 which is too long. Maximum length supported is $1~Int1. 1031 ZZZZZ 99999 BEGINNER MINOR DBADMIN Object $0~TableName could not be dropped. 1032 ZZZZZ 99999 BEGINNER MINOR DBADMIN The DISPLAY command completes and the query is not executed. -1033 ZZZZZ 99999 BEGINNER MINOR DBADMIN --- unused --- +1033 ZZZZZ 99999 BEGINNER MINOR DBADMIN Unable to obtain comments. 1034 ZZZZZ 99999 BEGINNER MAJOR DIALOUT Unable to obtain privileges. 1035 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Catalog $0~CatalogName already exists. 1036 ZZZZZ 99999 BEGINNER MAJOR DBADMIN Authorization ID $0~String0 cannot grant to authorization ID $1~String1 because it could create a circular dependency. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/0b7e3a35/core/sql/sqlcomp/CmpDDLCatErrorCodes.h ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpDDLCatErrorCodes.h b/core/sql/sqlcomp/CmpDDLCatErrorCodes.h index 2a3dab0..52eca31 100644 --- a/core/sql/sqlcomp/CmpDDLCatErrorCodes.h +++ b/core/sql/sqlcomp/CmpDDLCatErrorCodes.h @@ -64,7 +64,7 @@ enum CatErrorCode { CAT_FIRST_ERROR = 1000 , CAT_UNABLE_TO_CREATE_OBJECT = 1029 , CAT_HBASE_NAME_TOO_LONG = 1030 , CAT_UNABLE_TO_DROP_OBJECT = 1031 - // unused = 1033 + , CAT_UNABLE_TO_RETRIEVE_COMMENTS = 1033 , CAT_UNABLE_TO_RETRIEVE_PRIVS = 1034 , CAT_CATALOG_ALREADY_EXISTS = 1035 , CAT_CIRCULAR_PRIVS = 1036 http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/0b7e3a35/core/sql/sqlcomp/CmpDescribe.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpDescribe.cpp b/core/sql/sqlcomp/CmpDescribe.cpp index 2d9c9da..b4b1d4e 100644 --- a/core/sql/sqlcomp/CmpDescribe.cpp +++ b/core/sql/sqlcomp/CmpDescribe.cpp @@ -3066,12 +3066,16 @@ short CmpDescribeSeabaseTable ( { if (cmpSBD.switchCompiler()) { - *CmpCommon::diags() << DgSqlCode(-CAT_UNABLE_TO_RETRIEVE_PRIVS); + *CmpCommon::diags() << DgSqlCode(-CAT_UNABLE_TO_RETRIEVE_COMMENTS); return -1; } ComTdbVirtObjCommentInfo objCommentInfo; - cmpSBD.getSeabaseObjectComment(objectUID, COM_VIEW_OBJECT, objCommentInfo, heap); + if (cmpSBD.getSeabaseObjectComment(objectUID, COM_VIEW_OBJECT, objCommentInfo, heap)) + { + *CmpCommon::diags() << DgSqlCode(-CAT_UNABLE_TO_RETRIEVE_COMMENTS); + return -1; + } //display VIEW COMMENT statements if (objCommentInfo.objectComment != NULL) @@ -3100,10 +3104,7 @@ short CmpDescribeSeabaseTable ( } //do a comment info memory clean - if (objCommentInfo.columnCommentArray != NULL) - { - NADELETEBASIC(objCommentInfo.columnCommentArray, heap); - } + NADELETEARRAY(objCommentInfo.columnCommentArray, objCommentInfo.numColumnComment, ComTdbVirtColumnCommentInfo, heap); cmpSBD.switchBackCompiler(); } @@ -3837,12 +3838,16 @@ short CmpDescribeSeabaseTable ( if (cmpSBD.switchCompiler()) { - *CmpCommon::diags() << DgSqlCode(-CAT_UNABLE_TO_RETRIEVE_PRIVS); + *CmpCommon::diags() << DgSqlCode(-CAT_UNABLE_TO_RETRIEVE_COMMENTS); return -1; } ComTdbVirtObjCommentInfo objCommentInfo; - cmpSBD.getSeabaseObjectComment(objectUID, objType, objCommentInfo, heap); + if (cmpSBD.getSeabaseObjectComment(objectUID, objType, objCommentInfo, heap)) + { + *CmpCommon::diags() << DgSqlCode(-CAT_UNABLE_TO_RETRIEVE_COMMENTS); + return -1; + } //display Table COMMENT statements if (objCommentInfo.objectComment != NULL) @@ -3885,14 +3890,8 @@ short CmpDescribeSeabaseTable ( } //do a comment info memory clean - if (objCommentInfo.columnCommentArray != NULL) - { - NADELETEBASIC(objCommentInfo.columnCommentArray, heap); - } - if (objCommentInfo.indexCommentArray != NULL) - { - NADELETEBASIC(objCommentInfo.indexCommentArray, heap); - } + NADELETEARRAY(objCommentInfo.columnCommentArray, objCommentInfo.numColumnComment, ComTdbVirtColumnCommentInfo, heap); + NADELETEARRAY(objCommentInfo.indexCommentArray, objCommentInfo.numIndexComment, ComTdbVirtIndexCommentInfo, heap); cmpSBD.switchBackCompiler(); } @@ -4014,11 +4013,44 @@ short CmpDescribeSequence( (CmpCommon::getDefault(SHOWDDL_DISPLAY_PRIVILEGE_GRANTS) == DF_OFF)) displayPrivilegeGrants = FALSE; + int64_t objectUID = (int64_t)naTable->objectUid().get_value(); + CmpSeabaseDDL cmpSBD((NAHeap*)heap); + + //display comment + if (objectUID > 0) + { + if (cmpSBD.switchCompiler()) + { + *CmpCommon::diags() << DgSqlCode(-CAT_UNABLE_TO_RETRIEVE_COMMENTS); + return -1; + } + + ComTdbVirtObjCommentInfo objCommentInfo; + if (cmpSBD.getSeabaseObjectComment(objectUID, COM_SEQUENCE_GENERATOR_OBJECT, objCommentInfo, heap)) + { + *CmpCommon::diags() << DgSqlCode(-CAT_UNABLE_TO_RETRIEVE_COMMENTS); + return -1; + } + + if (objCommentInfo.objectComment != NULL) + { + //new line + outputLine(*space, "", 0); + + sprintf(buf, "COMMENT ON SEQUENCE %s IS '%s' ;", + cn.getQualifiedNameObj().getQualifiedNameAsAnsiString(TRUE).data(), + objCommentInfo.objectComment); + outputLine(*space, buf, 0); + } + + cmpSBD.switchBackCompiler(); + } + + // If authorization enabled, display grant statements if (CmpCommon::context()->isAuthorizationEnabled() && displayPrivilegeGrants) { // now get the grant stmts - int64_t objectUID = (int64_t)naTable->objectUid().get_value(); NAString privMDLoc; CONCAT_CATSCH(privMDLoc, CmpSeabaseDDL::getSystemCatalogStatic(), SEABASE_MD_SCHEMA); NAString privMgrMDLoc; @@ -4029,7 +4061,6 @@ short CmpDescribeSequence( std::string privilegeText; PrivMgrObjectInfo objectInfo(naTable); - CmpSeabaseDDL cmpSBD((NAHeap*)heap); if (cmpSBD.switchCompiler()) { *CmpCommon::diags() << DgSqlCode(-CAT_UNABLE_TO_RETRIEVE_PRIVS); @@ -4259,7 +4290,11 @@ char buf[1000]; } ComTdbVirtObjCommentInfo objCommentInfo; - cmpSBD.getSeabaseObjectComment(libraryUID, COM_LIBRARY_OBJECT, objCommentInfo, heap); + if (cmpSBD.getSeabaseObjectComment(libraryUID, COM_LIBRARY_OBJECT, objCommentInfo, heap)) + { + *CmpCommon::diags() << DgSqlCode(-CAT_UNABLE_TO_RETRIEVE_COMMENTS); + return -1; + } if (objCommentInfo.objectComment != NULL) { @@ -4800,7 +4835,12 @@ short CmpDescribeRoutine (const CorrName & cn, } ComTdbVirtObjCommentInfo objCommentInfo; - cmpSBD.getSeabaseObjectComment(routineUID, COM_USER_DEFINED_ROUTINE_OBJECT, objCommentInfo, heap); + if (cmpSBD.getSeabaseObjectComment(routineUID, COM_USER_DEFINED_ROUTINE_OBJECT, objCommentInfo, heap)) + { + *CmpCommon::diags() << DgSqlCode(-CAT_UNABLE_TO_RETRIEVE_COMMENTS); + return -1; + } + if (objCommentInfo.objectComment != NULL) { http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/0b7e3a35/core/sql/sqlcomp/CmpSeabaseDDLcommentOn.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLcommentOn.cpp b/core/sql/sqlcomp/CmpSeabaseDDLcommentOn.cpp index 95b589f..1e5e294 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLcommentOn.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLcommentOn.cpp @@ -66,6 +66,122 @@ #include "ComUser.h" +short CmpSeabaseDDL::getSeabaseObjectComment(Int64 object_uid, + enum ComObjectType object_type, + ComTdbVirtObjCommentInfo & comment_info, + CollHeap * heap) +{ + Lng32 retcode = 0; + Lng32 cliRC = 0; + + char query[4000]; + + comment_info.objectUid = object_uid; + comment_info.objectComment = NULL; + comment_info.numColumnComment = 0; + comment_info.columnCommentArray = NULL; + comment_info.numIndexComment = 0; + comment_info.indexCommentArray = NULL; + + ExeCliInterface cliInterface(STMTHEAP, NULL, NULL, + CmpCommon::context()->sqlSession()->getParentQid()); + + //get object comment + sprintf(query, "select comment from %s.\"%s\".%s where object_uid = %ld and object_type = '%s' and comment <> '' ;", + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_OBJECTS, + object_uid, comObjectTypeLit(object_type)); + + Queue * objQueue = NULL; + cliRC = cliInterface.fetchAllRows(objQueue, query, 0, FALSE, FALSE, TRUE); + if (cliRC < 0) + { + cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); + processReturn(); + return -1; + } + + //We should have only 1 comment for object + if (objQueue->numEntries() > 0) + { + objQueue->position(); + OutputInfo * vi = (OutputInfo*)objQueue->getNext(); + comment_info.objectComment = (char*)vi->get(0); + } + + //get index comments of table + if (COM_BASE_TABLE_OBJECT == object_type) + { + sprintf(query, "select CATALOG_NAME||'.'||SCHEMA_NAME||'.'||OBJECT_NAME, COMMENT " + "from %s.\"%s\".%s as O, %s.\"%s\".%s as I " + "where I.BASE_TABLE_UID = %ld and O.OBJECT_UID = I.INDEX_UID and O.comment <> '' ;", + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_OBJECTS, + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_INDEXES, + object_uid); + + Queue * indexQueue = NULL; + cliRC = cliInterface.fetchAllRows(indexQueue, query, 0, FALSE, FALSE, TRUE); + if (cliRC < 0) + { + cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); + processReturn(); + return -1; + } + + if (indexQueue->numEntries() > 0) + { + comment_info.numIndexComment = indexQueue->numEntries(); + comment_info.indexCommentArray = new(heap) ComTdbVirtIndexCommentInfo[comment_info.numIndexComment]; + + indexQueue->position(); + for (Lng32 idx = 0; idx < comment_info.numIndexComment; idx++) + { + OutputInfo * oi = (OutputInfo*)indexQueue->getNext(); + ComTdbVirtIndexCommentInfo &indexComment = comment_info.indexCommentArray[idx]; + + // get the index full name + indexComment.indexFullName = (char*) oi->get(0); + indexComment.indexComment = (char*) oi->get(1); + } + } + } + + //get column comments of table and view + if (COM_BASE_TABLE_OBJECT == object_type || COM_VIEW_OBJECT == object_type) + { + sprintf(query, "select COLUMN_NAME, COMMENT from %s.\"%s\".%s where OBJECT_UID = %ld and comment <> '' order by COLUMN_NUMBER ;", + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_COLUMNS, object_uid); + + Queue * colQueue = NULL; + cliRC = cliInterface.fetchAllRows(colQueue, query, 0, FALSE, FALSE, TRUE); + if (cliRC < 0) + { + cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); + processReturn(); + return -1; + } + + if (colQueue->numEntries() > 0) + { + comment_info.numColumnComment = colQueue->numEntries(); + comment_info.columnCommentArray = new(heap) ComTdbVirtColumnCommentInfo[comment_info.numColumnComment]; + + colQueue->position(); + for (Lng32 idx = 0; idx < comment_info.numColumnComment; idx++) + { + OutputInfo * oi = (OutputInfo*)colQueue->getNext(); + ComTdbVirtColumnCommentInfo &colComment = comment_info.columnCommentArray[idx]; + + // get the column name + colComment.columnName = (char*) oi->get(0); + colComment.columnComment = (char*) oi->get(1); + } + } + } + + return 0; +} + + void CmpSeabaseDDL::doSeabaseCommentOn(StmtDDLCommentOn *commentOnNode, NAString &currCatName, NAString &currSchName) @@ -173,7 +289,7 @@ void CmpSeabaseDDL::doSeabaseCommentOn(StmtDDLCommentOn *commentOnNode, if (StmtDDLCommentOn::COMMENT_ON_TYPE_COLUMN == commentObjectType) { - str_sprintf(query, "update %s.\"%s\".%s set comment = '%s' where object_uid = %ld and column_name = '%s' ", + sprintf(query, "update %s.\"%s\".%s set comment = '%s' where object_uid = %ld and column_name = '%s' ", getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_COLUMNS, comment.data(), objUID, @@ -183,7 +299,7 @@ void CmpSeabaseDDL::doSeabaseCommentOn(StmtDDLCommentOn *commentOnNode, } else { - str_sprintf(query, "update %s.\"%s\".%s set comment = '%s' where catalog_name = '%s' and schema_name = '%s' and object_name = '%s' and object_type = '%s' ", + sprintf(query, "update %s.\"%s\".%s set comment = '%s' where catalog_name = '%s' and schema_name = '%s' and object_name = '%s' and object_type = '%s' ", getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_OBJECTS, comment.data(), catalogNamePart.data(), schemaNamePart.data(), objNamePart.data(), http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/0b7e3a35/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp b/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp index e44faeb..45736b0 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp @@ -456,7 +456,11 @@ Int16 status = ComUser::getAuthNameFromAuthID(objectOwner,username, // Display Comment of schema { ComTdbVirtObjCommentInfo objCommentInfo; - cmpSBD.getSeabaseObjectComment(schemaUID, objectType, objCommentInfo, STMTHEAP); + if (cmpSBD.getSeabaseObjectComment(schemaUID, objectType, objCommentInfo, STMTHEAP)) + { + *CmpCommon::diags() << DgSqlCode(-CAT_UNABLE_TO_RETRIEVE_COMMENTS); + return -1; + } if (objCommentInfo.objectComment != NULL) { http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/0b7e3a35/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp b/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp index 886c42d..83abc38 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp @@ -12940,122 +12940,6 @@ TrafDesc *CmpSeabaseDDL::getSeabaseRoutineDescInternal(const NAString &catName, } -short CmpSeabaseDDL::getSeabaseObjectComment(Int64 object_uid, - enum ComObjectType object_type, - ComTdbVirtObjCommentInfo & comment_info, - CollHeap * heap) -{ - Lng32 retcode = 0; - Lng32 cliRC = 0; - - char query[4000]; - - comment_info.objectUid = object_uid; - comment_info.objectComment = NULL; - comment_info.numColumnComment = 0; - comment_info.columnCommentArray = NULL; - comment_info.numIndexComment = 0; - comment_info.indexCommentArray = NULL; - - ExeCliInterface cliInterface(STMTHEAP, NULL, NULL, - CmpCommon::context()->sqlSession()->getParentQid()); - - //get object comment - str_sprintf(query, "select comment from %s.\"%s\".%s where object_uid = %ld and object_type = '%s' and comment <> '' ;", - getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_OBJECTS, - object_uid, comObjectTypeLit(object_type)); - - Queue * objQueue = NULL; - cliRC = cliInterface.fetchAllRows(objQueue, query, 0, FALSE, FALSE, TRUE); - if (cliRC < 0) - { - cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); - processReturn(); - return -1; - } - - //We should have only 1 comment for object - if (objQueue->numEntries() > 0) - { - objQueue->position(); - OutputInfo * vi = (OutputInfo*)objQueue->getNext(); - comment_info.objectComment = (char*)vi->get(0); - } - - //get index comments of table - if (COM_BASE_TABLE_OBJECT == object_type) - { - str_sprintf(query, "select CATALOG_NAME||'.'||SCHEMA_NAME||'.'||OBJECT_NAME, COMMENT " - "from %s.\"%s\".%s as O, %s.\"%s\".%s as I " - "where I.BASE_TABLE_UID = %ld and O.OBJECT_UID = I.INDEX_UID and O.comment <> '' ;", - getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_OBJECTS, - getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_INDEXES, - object_uid); - - Queue * indexQueue = NULL; - cliRC = cliInterface.fetchAllRows(indexQueue, query, 0, FALSE, FALSE, TRUE); - if (cliRC < 0) - { - cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); - processReturn(); - return -1; - } - - if (indexQueue->numEntries() > 0) - { - comment_info.numIndexComment = indexQueue->numEntries(); - comment_info.indexCommentArray = new(heap) ComTdbVirtIndexCommentInfo[comment_info.numIndexComment]; - - indexQueue->position(); - for (Lng32 idx = 0; idx < comment_info.numIndexComment; idx++) - { - OutputInfo * oi = (OutputInfo*)indexQueue->getNext(); - ComTdbVirtIndexCommentInfo &indexComment = comment_info.indexCommentArray[idx]; - - // get the index full name - indexComment.indexFullName = (char*) oi->get(0); - indexComment.indexComment = (char*) oi->get(1); - } - } - } - - //get column comments of table and view - if (COM_BASE_TABLE_OBJECT == object_type || COM_VIEW_OBJECT == object_type) - { - str_sprintf(query, "select COLUMN_NAME, COMMENT from %s.\"%s\".%s where OBJECT_UID = %ld and comment <> '' order by COLUMN_NUMBER ;", - getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_COLUMNS, object_uid); - - Queue * colQueue = NULL; - cliRC = cliInterface.fetchAllRows(colQueue, query, 0, FALSE, FALSE, TRUE); - if (cliRC < 0) - { - cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); - processReturn(); - return -1; - } - - if (colQueue->numEntries() > 0) - { - comment_info.numColumnComment = colQueue->numEntries(); - comment_info.columnCommentArray = new(heap) ComTdbVirtColumnCommentInfo[comment_info.numColumnComment]; - - colQueue->position(); - for (Lng32 idx = 0; idx < comment_info.numColumnComment; idx++) - { - OutputInfo * oi = (OutputInfo*)colQueue->getNext(); - ComTdbVirtColumnCommentInfo &colComment = comment_info.columnCommentArray[idx]; - - // get the column name - colComment.columnName = (char*) oi->get(0); - colComment.columnComment = (char*) oi->get(1); - } - } - } - - return 0; -} - - // ***************************************************************************** // * * // * Function: checkSpecifiedPrivs *
