core/sql/regress/udr/EXPECTED102

>>cqd SHOWDDL_DISPLAY_PRIVILEGE_GRANTS 'ON';

--- SQL operation complete.
>>set schema "_LIBMGR_";

--- SQL operation complete.
>>
>>prepare get_schemas from
+>select distinct substring (schema_name,1,16) as schema_name
+>from trafodion."_MD_".objects
+>where schema_name in ('SEABASE', '_LIBMGR_', '_MD_', '_PRIVMGR_MD_', '_REPOS_')
+>order by 1;

--- SQL command prepared.
>>
>>
>>-- ****************************************************************************
>>--  Verify enabling library management followed by authorization is correct
>>-- ****************************************************************************
>>
>>-- authorization and library management should be disabled
>>execute get_schemas;

SCHEMA_NAME                                                     
----------------------------------------------------------------

SEABASE                                                         
_MD_                                                            
_REPOS_                                                         

--- 3 row(s) selected.
>>
>>-- create library management 
>>initialize trafodion, create library management;

--- SQL operation complete.
>>execute get_schemas;

SCHEMA_NAME                                                     
----------------------------------------------------------------

SEABASE                                                         
_LIBMGR_                                                        
_MD_                                                            
_REPOS_                                                         

--- 4 row(s) selected.
>>get libraries;

Libraries in Schema TRAFODION._LIBMGR_
======================================

DB__LIBMGRNAME

--- SQL operation complete.
>>get procedures;

Procedures in Schema TRAFODION._LIBMGR_
=======================================

ADDLIB
ALTERLIB
DROPLIB
GETFILE
HELP
LS
LSALL
PUT
PUTFILE
RM
RMREX

--- SQL operation complete.
>>showddl procedure rm;

CREATE PROCEDURE TRAFODION."_LIBMGR_".RM
  (
    IN FILENAME VARCHAR(256 CHARS) CHARACTER SET UTF8
  )
  EXTERNAL NAME 'org.trafodion.libmgmt.FileMgmt.rm (java.lang.String)'
  LIBRARY TRAFODION."_LIBMGR_".DB__LIBMGRNAME
  EXTERNAL SECURITY DEFINER
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  READS SQL DATA
  DYNAMIC RESULT SETS 0
  NO TRANSACTION REQUIRED
  ISOLATE
  ;

--- SQL operation complete.
>>set param ?h 'PUTFILE';
>>call help (?h);

COMMANDNAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

PUTFILE - Upload a library file. SHOWDDL PROCEDURE [SCHEMA NAME.]PUTFILE for more info.

--- SQL operation complete.
>>
>>-- enable authorization
>>initialize authorization;

--- SQL operation complete.
>>execute get_schemas;

SCHEMA_NAME                                                     
----------------------------------------------------------------

SEABASE                                                         
_LIBMGR_                                                        
_MD_                                                            
_PRIVMGR_MD_                                                    
_REPOS_                                                         

--- 5 row(s) selected.
>>get roles;

Roles
=====

DB__HBASEROLE
DB__HIVEROLE
DB__LIBMGRROLE
DB__ROOTROLE
PUBLIC

--- SQL operation complete.
>>showddl procedure rm;

CREATE PROCEDURE TRAFODION."_LIBMGR_".RM
  (
    IN FILENAME VARCHAR(256 CHARS) CHARACTER SET UTF8
  )
  EXTERNAL NAME 'org.trafodion.libmgmt.FileMgmt.rm (java.lang.String)'
  LIBRARY TRAFODION."_LIBMGR_".DB__LIBMGRNAME
  EXTERNAL SECURITY DEFINER
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  READS SQL DATA
  DYNAMIC RESULT SETS 0
  NO TRANSACTION REQUIRED
  ISOLATE
  ;

-- GRANT EXECUTE ON FUNCTION TRAFODION."_LIBMGR_".RM TO DB__ROOT WITH GRANT OPTION;
  GRANT EXECUTE ON FUNCTION TRAFODION."_LIBMGR_".RM TO DB__LIBMGRROLE WITH
  GRANT OPTION;

--- SQL operation complete.
>>
>>-- reset
>>initialize authorization, drop;

--- SQL operation complete.
>>initialize trafodion, drop library management;

--- SQL operation complete.
>>execute get_schemas;

SCHEMA_NAME                                                     
----------------------------------------------------------------

SEABASE                                                         
_MD_                                                            
_REPOS_                                                         

--- 3 row(s) selected.
>>get procedures;

--- SQL operation complete.
>>get roles;

Roles
=====

PUBLIC

--- SQL operation complete.
>>
>>-- ****************************************************************************
>>--  Verify enabling authorization followed by library management is correct
>>-- ****************************************************************************
>>
>>-- enable authorization
>>initialize authorization;

--- SQL operation complete.
>>execute get_schemas;

SCHEMA_NAME                                                     
----------------------------------------------------------------

SEABASE                                                         
_MD_                                                            
_PRIVMGR_MD_                                                    
_REPOS_                                                         

--- 4 row(s) selected.
>>get roles;

Roles
=====

DB__HBASEROLE
DB__HIVEROLE
DB__LIBMGRROLE
DB__ROOTROLE
PUBLIC

--- SQL operation complete.
>>
>>-- enable library management
>>initialize trafodion, create library management;

--- SQL operation complete.
>>execute get_schemas;

SCHEMA_NAME                                                     
----------------------------------------------------------------

SEABASE                                                         
_LIBMGR_                                                        
_MD_                                                            
_PRIVMGR_MD_                                                    
_REPOS_                                                         

--- 5 row(s) selected.
>>get libraries;

Libraries in Schema TRAFODION._LIBMGR_
======================================

DB__LIBMGRNAME

--- SQL operation complete.
>>get procedures;

Procedures in Schema TRAFODION._LIBMGR_
=======================================

ADDLIB
ALTERLIB
DROPLIB
GETFILE
HELP
LS
LSALL
PUT
PUTFILE
RM
RMREX

--- SQL operation complete.
>>showddl procedure rm;

CREATE PROCEDURE TRAFODION."_LIBMGR_".RM
  (
    IN FILENAME VARCHAR(256 CHARS) CHARACTER SET UTF8
  )
  EXTERNAL NAME 'org.trafodion.libmgmt.FileMgmt.rm (java.lang.String)'
  LIBRARY TRAFODION."_LIBMGR_".DB__LIBMGRNAME
  EXTERNAL SECURITY DEFINER
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  READS SQL DATA
  DYNAMIC RESULT SETS 0
  NO TRANSACTION REQUIRED
  ISOLATE
  ;

-- GRANT EXECUTE ON FUNCTION TRAFODION."_LIBMGR_".RM TO DB__ROOT WITH GRANT OPTION;
  GRANT EXECUTE ON FUNCTION TRAFODION."_LIBMGR_".RM TO DB__LIBMGRROLE WITH
  GRANT OPTION;

--- SQL operation complete.
>>set param ?h 'PUTFILE';
>>call help (?h);

COMMANDNAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

PUTFILE - Upload a library file. SHOWDDL PROCEDURE [SCHEMA NAME.]PUTFILE for more info.

--- SQL operation complete.
>>
>>-- ****************************************************************************
>>--  Test upgrade
>>-- ****************************************************************************
>>
>>-- upgrade, remove a couple of procedures and run upgrade
>>set parserflags 131072;

--- SQL operation complete.
>>drop procedure PUT;

--- SQL operation complete.
>>drop procedure ls;

--- SQL operation complete.
>>reset parserflags 131072;

--- SQL operation complete.
>>get procedures;

Procedures in Schema TRAFODION._LIBMGR_
=======================================

ADDLIB
ALTERLIB
DROPLIB
GETFILE
HELP
LSALL
PUTFILE
RM
RMREX

--- SQL operation complete.
>>call ls(?);

*** ERROR[1389] Object LS does not exist in Trafodion.

*** ERROR[8822] The statement was not prepared.

>>
>>initialize trafodion, upgrade library management;

--- SQL operation complete.
>>get procedures;

Procedures in Schema TRAFODION._LIBMGR_
=======================================

ADDLIB
ALTERLIB
DROPLIB
GETFILE
HELP
LS
LSALL
PUT
PUTFILE
RM
RMREX

--- SQL operation complete.
>>showddl procedure put;

CREATE PROCEDURE TRAFODION."_LIBMGR_".PUT
  (
    IN FILEDATA VARCHAR(102400) CHARACTER SET ISO88591
  , IN FILENAME VARCHAR(256 CHARS) CHARACTER SET UTF8
  , IN CREATEFLAG INTEGER
  , IN FILEOVERWRITE INTEGER
  )
  EXTERNAL NAME 'org.trafodion.libmgmt.FileMgmt.put (java.lang.String,java.lang.String,int,int)'
  LIBRARY TRAFODION."_LIBMGR_".DB__LIBMGRNAME
  EXTERNAL SECURITY DEFINER
  LANGUAGE JAVA
  PARAMETER STYLE JAVA
  READS SQL DATA
  DYNAMIC RESULT SETS 0
  NO TRANSACTION REQUIRED
  ISOLATE
  ;

-- GRANT EXECUTE ON FUNCTION TRAFODION."_LIBMGR_".PUT TO DB__ROOT WITH GRANT OPTION;
  GRANT EXECUTE ON FUNCTION TRAFODION."_LIBMGR_".PUT TO DB__LIBMGRROLE WITH
  GRANT OPTION;

--- SQL operation complete.
>>call ls ('lib_mgmt.jar',?);

FILENAMES
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

<ls></ls>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

--- SQL operation complete.
>>
>>-- should fail - library management already enabled
>>initialize trafodion, create library management;

*** ERROR[1390] Object TRAFODION."_LIBMGR_".DB__LIBMGRNAME already exists in Trafodion.

--- SQL operation failed with errors.
>>
>>-- make sure everything is enabled
>>execute get_schemas;

SCHEMA_NAME                                                     
----------------------------------------------------------------

SEABASE                                                         
_LIBMGR_                                                        
_MD_                                                            
_PRIVMGR_MD_                                                    
_REPOS_                                                         

--- 5 row(s) selected.
>>get procedures;

Procedures in Schema TRAFODION._LIBMGR_
=======================================

ADDLIB
ALTERLIB
DROPLIB
GETFILE
HELP
LS
LSALL
PUT
PUTFILE
RM
RMREX

--- SQL operation complete.
>>
>>
>>-- put a file in a single chunk
>>call "_LIBMGR_".putfile('abcd', 'udr_test102_1', 1, 1, 1);

--- SQL operation complete.
>>
>>-- put a file in three chunks
>>call "_LIBMGR_".putfile('abcd', 'udr_test102_3', 1, 0, 1);

--- SQL operation complete.
>>call "_LIBMGR_".putfile('efgh', 'udr_test102_3', 0, 0, 1);

--- SQL operation complete.
>>call "_LIBMGR_".putfile('ijkl', 'udr_test102_3', 0, 1, 1);

--- SQL operation complete.
>>
>>-- negative test, file already exists and no overwrite requested
>>call "_LIBMGR_".putfile('abcd', 'udr_test102_1', 1, 1, 0);

*** ERROR[11220] A Java method completed with an uncaught java.sql.SQLException with invalid SQLSTATE. The uncaught exception had a SQLCODE of 0 and SQLSTATE of . Details: java.sql.SQLException: File udr_test102_1 already exists!

--- SQL operation failed with errors.
>>
>>-- expect two lines with the contents of the two files below
>>log;
abcd 
abcdefghijkl 
>>
>>call "_LIBMGR_".rm('udr_test102_1');

--- SQL operation complete.
>>call "_LIBMGR_".rm('udr_test102_3');

--- SQL operation complete.
>>
>>-- we are done
>>exit;

End of MXCI Session