new COMMENT-ON SQL statement: review change - TEXT table modification 1. mv storage of COMMENTs in TEXT table
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/a448e8f8 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/a448e8f8 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/a448e8f8 Branch: refs/heads/master Commit: a448e8f831f2dd84daf1758c332d074e71075486 Parents: 0b7e3a3 Author: eedy <[email protected]> Authored: Thu Nov 9 12:00:29 2017 +0800 Committer: eedy <[email protected]> Committed: Thu Nov 9 12:00:29 2017 +0800 ---------------------------------------------------------------------- core/sql/common/ComSmallDefs.h | 6 +- core/sql/optimizer/RelExeUtil.cpp | 2 +- core/sql/parser/BindStmtDDL.cpp | 4 +- core/sql/parser/StmtDDLCommentOn.h | 2 + core/sql/parser/StmtDDLCreate.cpp | 6 +- core/sql/sqlcomp/CmpSeabaseDDLcommentOn.cpp | 90 ++++++++++++++++-------- core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp | 13 ++++ core/sql/sqlcomp/CmpSeabaseDDLmd.h | 16 +++-- core/sql/sqlcomp/CmpSeabaseDDLschema.cpp | 13 +++- core/sql/sqlcomp/CmpSeabaseDDLtable.cpp | 18 ++++- core/sql/sqlcomp/CmpSeabaseDDLview.cpp | 8 +++ 11 files changed, 132 insertions(+), 46 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a448e8f8/core/sql/common/ComSmallDefs.h ---------------------------------------------------------------------- diff --git a/core/sql/common/ComSmallDefs.h b/core/sql/common/ComSmallDefs.h index 77914e9..1d5a15f 100644 --- a/core/sql/common/ComSmallDefs.h +++ b/core/sql/common/ComSmallDefs.h @@ -591,6 +591,8 @@ enum ComClusteringScheme { COM_UNKNOWN_CLUSTERING #define COM_MAXIMUM_NUMBER_OF_COLUMNS 20000 #define COM_DIV_EXPR_BASE_TEXT_SUBID 20000 +#define COM_MAXIMUM_LENGTH_OF_COMMENT 1000 + enum ComColumnClass { COM_UNKNOWN_CLASS , COM_SYSTEM_COLUMN , COM_USER_COLUMN @@ -660,7 +662,9 @@ enum ComTextType {COM_VIEW_TEXT = 0, COM_HBASE_COL_FAMILY_TEXT = 5, COM_HBASE_SPLIT_TEXT = 6, COM_STORED_DESC_TEXT = 7, - COM_VIEW_REF_COLS_TEXT = 8 + COM_VIEW_REF_COLS_TEXT = 8, + COM_OBJET_COMMENT_TEXT = COM_TABLE_COMMENT_TEXT, + COM_COLUMN_COMMENT_TEXT = 9 }; enum ComColumnDirection { COM_UNKNOWN_DIRECTION http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a448e8f8/core/sql/optimizer/RelExeUtil.cpp ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/RelExeUtil.cpp b/core/sql/optimizer/RelExeUtil.cpp index 71c0421..5d143a2 100644 --- a/core/sql/optimizer/RelExeUtil.cpp +++ b/core/sql/optimizer/RelExeUtil.cpp @@ -4599,7 +4599,7 @@ RelExpr * DDLExpr::bindNode(BindWA *bindWA) return NULL; } - if (isRegister) + if (isRegister || isCommentOn) { isHbase_ = TRUE; } http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a448e8f8/core/sql/parser/BindStmtDDL.cpp ---------------------------------------------------------------------- diff --git a/core/sql/parser/BindStmtDDL.cpp b/core/sql/parser/BindStmtDDL.cpp index 2294b45..a58dcd7 100644 --- a/core/sql/parser/BindStmtDDL.cpp +++ b/core/sql/parser/BindStmtDDL.cpp @@ -2611,8 +2611,6 @@ StmtDDLCommentOn::bindNode(BindWA * pBindWA) return this; } - isViewCol_ = (naTable->getViewText() ? TRUE : FALSE); - const NAColumnArray &nacolArr = naTable->getNAColumnArray(); const NAColumn * nacol = nacolArr.getColumn(getColName()); if (! nacol) @@ -2624,6 +2622,8 @@ StmtDDLCommentOn::bindNode(BindWA * pBindWA) return this; } + isViewCol_ = (naTable->getViewText() ? TRUE : FALSE); + colNum_ = nacol->getPosition(); } } http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a448e8f8/core/sql/parser/StmtDDLCommentOn.h ---------------------------------------------------------------------- diff --git a/core/sql/parser/StmtDDLCommentOn.h b/core/sql/parser/StmtDDLCommentOn.h index 57b3542..9b31b26 100644 --- a/core/sql/parser/StmtDDLCommentOn.h +++ b/core/sql/parser/StmtDDLCommentOn.h @@ -102,6 +102,7 @@ public: inline const NAString &getComment() const { return comment_; } inline const NAString &getColName() const { return colRef_->getColRefNameObj().getColName(); } inline NABoolean getIsViewCol() { return isViewCol_; } + inline Lng32 getColNum() { return colNum_; } inline Int32 getVersion() {return 1;} @@ -120,6 +121,7 @@ private: QualifiedName objectName_; ColReference * colRef_; NABoolean isViewCol_; + Lng32 colNum_; const NAString & comment_; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a448e8f8/core/sql/parser/StmtDDLCreate.cpp ---------------------------------------------------------------------- diff --git a/core/sql/parser/StmtDDLCreate.cpp b/core/sql/parser/StmtDDLCreate.cpp index ff39634..72d014e 100644 --- a/core/sql/parser/StmtDDLCreate.cpp +++ b/core/sql/parser/StmtDDLCreate.cpp @@ -7180,7 +7180,8 @@ StmtDDLCommentOn::StmtDDLCommentOn(COMMENT_ON_TYPES objType, const QualifiedName objectName_(objName, heap), comment_(commentStr), colRef_(NULL), - isViewCol_(FALSE) + isViewCol_(FALSE), + colNum_(0) { } @@ -7192,7 +7193,8 @@ StmtDDLCommentOn::StmtDDLCommentOn(COMMENT_ON_TYPES objType, const QualifiedName objectName_(objName, heap), colRef_(colRef), comment_(commentStr), - isViewCol_(FALSE) + isViewCol_(FALSE), + colNum_(0) { } http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a448e8f8/core/sql/sqlcomp/CmpSeabaseDDLcommentOn.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLcommentOn.cpp b/core/sql/sqlcomp/CmpSeabaseDDLcommentOn.cpp index 1e5e294..f9197e3 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLcommentOn.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLcommentOn.cpp @@ -87,9 +87,9 @@ short CmpSeabaseDDL::getSeabaseObjectComment(Int64 object_uid, 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)); + sprintf(query, "select TEXT from %s.\"%s\".%s where TEXT_UID = %ld and TEXT_TYPE = %d and SUB_ID = %d ; ", + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_TEXT, + object_uid, COM_OBJET_COMMENT_TEXT, 0); Queue * objQueue = NULL; cliRC = cliInterface.fetchAllRows(objQueue, query, 0, FALSE, FALSE, TRUE); @@ -111,12 +111,15 @@ short CmpSeabaseDDL::getSeabaseObjectComment(Int64 object_uid, //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); + sprintf(query, "select O.CATALOG_NAME||'.'||O.SCHEMA_NAME||'.'||O.OBJECT_NAME as INDEX_QUAL, T.TEXT " + "from %s.\"%s\".%s as O, %s.\"%s\".%s as T, %s.\"%s\".%s as I " + "where I.BASE_TABLE_UID = %ld and O.OBJECT_UID = I.INDEX_UID and T.TEXT_UID = O.OBJECT_UID " + " and T.TEXT_TYPE = %d and SUB_ID = %d " + "order by INDEX_QUAL ; ", + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_OBJECTS, + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_TEXT, + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_INDEXES, + object_uid, COM_OBJET_COMMENT_TEXT, 0); Queue * indexQueue = NULL; cliRC = cliInterface.fetchAllRows(indexQueue, query, 0, FALSE, FALSE, TRUE); @@ -148,8 +151,12 @@ short CmpSeabaseDDL::getSeabaseObjectComment(Int64 object_uid, //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); + sprintf(query, "select C.COLUMN_NAME, T.TEXT from %s.\"%s\".%s as C, %s.\"%s\".%s as T " + "where C.OBJECT_UID = %ld and T.TEXT_UID = C.OBJECT_UID and T.TEXT_TYPE = %d and C.COLUMN_NUMBER = T.SUB_ID " + "order by C.COLUMN_NUMBER ; ", + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_COLUMNS, + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_TEXT, + object_uid, COM_COLUMN_COMMENT_TEXT); Queue * colQueue = NULL; cliRC = cliInterface.fetchAllRows(colQueue, query, 0, FALSE, FALSE, TRUE); @@ -281,33 +288,32 @@ void CmpSeabaseDDL::doSeabaseCommentOn(StmtDDLCommentOn *commentOnNode, return; } - //check for overflow, but how i can get type size of COMMENT column? + + //check for overflow + NAString & comment = (NAString &) commentOnNode->getComment(); + if (comment.length() > COM_MAXIMUM_LENGTH_OF_COMMENT) + { + *CmpCommon::diags() << DgSqlCode(-8402); + processReturn (); + return; + } // add, remove, change comment of object/column - const NAString & comment = commentOnNode->getComment(); - char * query = new(STMTHEAP) char[comment.length()+1024]; + enum ComTextType textType = COM_OBJET_COMMENT_TEXT; + Lng32 subID = 0; if (StmtDDLCommentOn::COMMENT_ON_TYPE_COLUMN == commentObjectType) { - 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, - commentOnNode->getColName().data() - ); - cliRC = cliInterface.executeImmediate(query); - } - else - { - 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(), - comObjectTypeLit(enMDObjType)); - cliRC = cliInterface.executeImmediate(query); + textType = COM_COLUMN_COMMENT_TEXT; + subID = commentOnNode->getColNum(); } - NADELETEBASIC(query, STMTHEAP); + /* Not using function updateTextTable(), because can not insert Chinese properly by function updateTextTable(). + * For storing COMMENT in TEXT table is a temp solution, so updating TEXT table directly here. + * Will change this implementation until next upgrade of MD. + */ + //like function updateTextTable(), delete entry first + cliRC = deleteFromTextTable(&cliInterface, objUID, textType, subID); if (cliRC < 0) { cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); @@ -315,6 +321,28 @@ void CmpSeabaseDDL::doSeabaseCommentOn(StmtDDLCommentOn *commentOnNode, return; } + if (comment.length() > 0) + { + //add or modify comment + char query[2048]; + + str_sprintf(query, "insert into %s.\"%s\".%s values (%ld, %d, %d, %d, 0, '%s') ; ", + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_TEXT, + objUID, + textType, + subID, + 0, + comment.data()); + cliRC = cliInterface.executeImmediate(query); + + if (cliRC < 0) + { + cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); + processReturn(); + return; + } + } + processReturn(); return; } http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a448e8f8/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp b/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp index c23a180..299b6c2 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLcommon.cpp @@ -5277,6 +5277,19 @@ short CmpSeabaseDDL::deleteFromSeabaseMDTable( cliInterface->retrieveSQLDiagnostics(CmpCommon::diags()); return -1; } + + // delete comment from TEXT table for library + str_sprintf(buf, "delete from %s.\"%s\".%s where text_uid = %ld", + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_TEXT, + objUID); + cliRC = cliInterface->executeImmediate(buf); + if (cliRC < 0) + { + cliInterface->retrieveSQLDiagnostics(CmpCommon::diags()); + + return -1; + } + return 0; // nothing else to do for libraries } http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a448e8f8/core/sql/sqlcomp/CmpSeabaseDDLmd.h ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLmd.h b/core/sql/sqlcomp/CmpSeabaseDDLmd.h index fdaa99b..49a5246 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLmd.h +++ b/core/sql/sqlcomp/CmpSeabaseDDLmd.h @@ -1698,24 +1698,28 @@ static const QString createTrafViewsViewQuery[] = {" ; "} }; + static const QString createTrafObjectCommentViewQuery[] = { {" create view %s.\"%s\"."TRAF_OBJECT_COMMENT_VIEW" as "}, - {" select catalog_name, schema_name, object_name, comment "}, - {" from %s.\"%s\".\"%s\" "}, - {" where COMMENT <> '' "}, + {" select O.catalog_name, O.schema_name, O.object_name, T.text as comment "}, + {" from %s.\"%s\".\"%s\" as O, %s.\"%s\".\"%s\" as T "}, + {" where O.object_uid = T.text_uid and T.text_type = %s "}, + {" order by O.OBJECT_UID "}, {" ; "} }; static const QString createTrafColumnCommentViewQuery[] = { {" create view %s.\"%s\"."TRAF_COLUMN_COMMENT_VIEW" as "}, - {" select O.CATALOG_NAME, O.SCHEMA_NAME, O.OBJECT_NAME, C.COLUMN_NAME, C.COMMENT "}, - {" from %s.\"%s\".\"%s\" as O, %s.\"%s\".\"%s\" as C "}, - {" where O.OBJECT_UID = C.OBJECT_UID and C.COMMENT <> '' "}, + {" select O.CATALOG_NAME, O.SCHEMA_NAME, O.OBJECT_NAME, C.COLUMN_NAME, T.TEXT as COMMENT "}, + {" from %s.\"%s\".\"%s\" as O, %s.\"%s\".\"%s\" as C, %s.\"%s\".\"%s\" as T "}, + {" where O.OBJECT_UID = C.OBJECT_UID and T.TEXT_UID = O.OBJECT_UID and T.TEXT_TYPE = %s "}, + {" order by O.OBJECT_UID, C.COLUMN_NUMBER "}, {" ; "} }; + struct MDViewInfo { const char * viewName; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a448e8f8/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp b/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp index 45736b0..27d2840 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLschema.cpp @@ -1015,7 +1015,18 @@ void CmpSeabaseDDL::dropSeabaseSchema(StmtDDLDropSchema * dropSchemaNode) << DgString0(reason); goto label_error; } - + + //Drop comment in TEXT table for schema + str_sprintf(buf, "delete from %s.\"%s\".%s where text_uid = %ld", + getSystemCatalog(),SEABASE_MD_SCHEMA,SEABASE_TEXT, + schemaUID); + cliRC = cliInterface.executeImmediate(buf); + if (cliRC < 0) + { + cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); + goto label_error; + } + // Everything succeeded, return return; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a448e8f8/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp b/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp index 83abc38..ee20ae2 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLtable.cpp @@ -5840,11 +5840,25 @@ short CmpSeabaseDDL::updateMDforDropCol(ExeCliInterface &cliInterface, cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); return -1; } - - str_sprintf(buf, "update %s.\"%s\".%s set sub_id = sub_id - 1 where text_uid = %ld and text_type = %d and sub_id > %d", + + //delete comment in TEXT table for column + str_sprintf(buf, "delete from %s.\"%s\".%s where text_uid = %ld and text_type = %d and sub_id = %d ", + getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_TEXT, + objUID, + COM_COLUMN_COMMENT_TEXT, + dropColNum); + cliRC = cliInterface.executeImmediate(buf); + if (cliRC < 0) + { + cliInterface.retrieveSQLDiagnostics(CmpCommon::diags()); + return -1; + } + + str_sprintf(buf, "update %s.\"%s\".%s set sub_id = sub_id - 1 where text_uid = %ld and ( text_type = %d or text_type = %d ) and sub_id > %d", getSystemCatalog(), SEABASE_MD_SCHEMA, SEABASE_TEXT, objUID, COM_COMPUTED_COL_TEXT, + COM_COLUMN_COMMENT_TEXT, dropColNum); cliRC = cliInterface.executeImmediate(buf); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a448e8f8/core/sql/sqlcomp/CmpSeabaseDDLview.cpp ---------------------------------------------------------------------- diff --git a/core/sql/sqlcomp/CmpSeabaseDDLview.cpp b/core/sql/sqlcomp/CmpSeabaseDDLview.cpp index 587c666..a9f353c 100644 --- a/core/sql/sqlcomp/CmpSeabaseDDLview.cpp +++ b/core/sql/sqlcomp/CmpSeabaseDDLview.cpp @@ -1499,6 +1499,10 @@ short CmpSeabaseDDL::createMetadataViews(ExeCliInterface * cliInterface) param_[2] = getSystemCatalog(); param_[3] = SEABASE_MD_SCHEMA; param_[4] = SEABASE_OBJECTS; + param_[5] = getSystemCatalog(); + param_[6] = SEABASE_MD_SCHEMA; + param_[7] = SEABASE_TEXT; + param_[8] = "3";//COM_OBJET_COMMENT_TEXT } else if (strcmp(mdi.viewName, TRAF_COLUMN_COMMENT_VIEW) == 0) { @@ -1510,6 +1514,10 @@ short CmpSeabaseDDL::createMetadataViews(ExeCliInterface * cliInterface) param_[5] = getSystemCatalog(); param_[6] = SEABASE_MD_SCHEMA; param_[7] = SEABASE_COLUMNS; + param_[8] = getSystemCatalog(); + param_[9] = SEABASE_MD_SCHEMA; + param_[10] = SEABASE_TEXT; + param_[11] = "9";//COM_COLUMN_COMMENT_TEXT } else {
