You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ba...@apache.org on 2005/10/28 14:52:21 UTC
svn commit: r329187 [33/66] - in /db/derby/code/trunk: ./
frameworks/NetworkServer/ frameworks/NetworkServer/bin/
frameworks/embedded/bin/ java/build/ java/build/org/apache/derbyBuild/
java/build/org/apache/derbyBuild/eclipse/ java/build/org/apache/der...
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dcl.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dcl.out?rev=329187&r1=329186&r2=329187&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dcl.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dcl.out Fri Oct 28 04:51:50 2005
@@ -1,397 +1,397 @@
-ij> -- test database class loading.
-maximumdisplaywidth 300;
-ij> create schema emc;
-0 rows inserted/updated/deleted
-ij> set schema emc;
-0 rows inserted/updated/deleted
-ij> create table contacts (id int primary key, e_mail varchar(30));
-0 rows inserted/updated/deleted
-ij> create procedure EMC.ADDCONTACT(id INT, e_mail VARCHAR(30))
-MODIFIES SQL DATA
-external name 'org.apache.derbyTesting.databaseclassloader.emc.addContact'
-language java parameter style java;
-0 rows inserted/updated/deleted
-ij> create function EMC.GETARTICLE(path VARCHAR(40)) RETURNS VARCHAR(256)
-NO SQL
-external name 'org.apache.derbyTesting.databaseclassloader.emc.getArticle'
-language java parameter style java;
-0 rows inserted/updated/deleted
-ij> -- fails because no class in classpath,
-CALL EMC.ADDCONTACT(1, 'bill@somecompany.com');
-ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public.
-ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc: java.lang.ClassNotFoundException'.
-ij> -- install the jar, copied there by the magic of supportfiles
--- in the test harness (dcl_app.properties). The source for
--- the class is contained within the jar for reference.
-CALL SQLJ.INSTALL_JAR('file:extin/dcl_emc1.jar', 'EMC.MAIL_APP', 0);
-0 rows inserted/updated/deleted
-ij> -- fails because no class not in classpath, jar file not in database classpath.
-CALL EMC.ADDCONTACT(1, 'bill@somecompany.com');
-ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public.
-ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc: java.lang.ClassNotFoundException'.
-ij> -- now add this into the database class path
-call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'EMC.MAIL_APP');
-0 rows inserted/updated/deleted
-ij> -- all should work now
-CALL EMC.ADDCONTACT(1, 'bill@ruletheworld.com');
-0 rows inserted/updated/deleted
-ij> CALL EMC.ADDCONTACT(2, 'penguin@antartic.com');
-0 rows inserted/updated/deleted
-ij> SELECT id, e_mail from EMC.CONTACTS;
-ID |E_MAIL
-------------------------------------------
-1 |bill@ruletheworld.com
-2 |penguin@antartic.com
-ij> -- Test resource loading from the jar file
--- Simple path should be prepended with the package name
--- of the class executing the code to find
--- /org/apache/derbyTesting/databaseclassloader/graduation.txt
-VALUES EMC.GETARTICLE('graduate.txt');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-The Apache Foundation has released the first version of the open-source Derby database, which also gained support from Sun Microsystems.
-ij> -- now an absolute path
-VALUES EMC.GETARTICLE('/article/release.txt');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-The Apache Derby development community is pleased to announce its first release after graduating from the Apache Incubator, Apache Derby 10.1.1.0.
-ij> -- no such resources
-VALUES EMC.GETARTICLE('/article/fred.txt');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-NULL
-ij> VALUES EMC.GETARTICLE('barney.txt');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-NULL
-ij> -- try to read the class file should be disallowed
--- by returning null
-VALUES EMC.GETARTICLE('emc.class');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-NULL
-ij> VALUES EMC.GETARTICLE('/org/apache/derbyTesting/databaseclassloader/emc.class');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-NULL
-ij> -- now the application needs to track if e-mails are valid
-ALTER TABLE EMC.CONTACTS ADD COLUMN OK SMALLINT;
-0 rows inserted/updated/deleted
-ij> SELECT id, e_mail, ok from EMC.CONTACTS;
-ID |E_MAIL |OK
--------------------------------------------------
-1 |bill@ruletheworld.com |NULL
-2 |penguin@antartic.com |NULL
-ij> -- well written application, INSERT used explicit column names
--- ok defaults to NULL
-CALL EMC.ADDCONTACT(3, 'big@blue.com');
-0 rows inserted/updated/deleted
-ij> SELECT id, e_mail, ok from EMC.CONTACTS;
-ID |E_MAIL |OK
--------------------------------------------------
-1 |bill@ruletheworld.com |NULL
-2 |penguin@antartic.com |NULL
-3 |big@blue.com |NULL
-ij> -- check the roll back of class loading.
--- install a new jar in a transaction, see
--- that the new class is used and then rollback
--- the old class should be used after the rollback.
-AUTOCOMMIT OFF;
-ij> CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2.jar', 'EMC.MAIL_APP');
-0 rows inserted/updated/deleted
-ij> CALL EMC.ADDCONTACT(99, 'wormspam@soil.com');
-0 rows inserted/updated/deleted
-ij> SELECT id, e_mail, ok from EMC.CONTACTS;
-ID |E_MAIL |OK
--------------------------------------------------
-1 |bill@ruletheworld.com |NULL
-2 |penguin@antartic.com |NULL
-3 |big@blue.com |NULL
-99 |wormspam@soil.com |0
-ij> rollback;
-ij> AUTOCOMMIT ON;
-ij> SELECT id, e_mail, ok from EMC.CONTACTS;
-ID |E_MAIL |OK
--------------------------------------------------
-1 |bill@ruletheworld.com |NULL
-2 |penguin@antartic.com |NULL
-3 |big@blue.com |NULL
-ij> CALL EMC.ADDCONTACT(99, 'wormspam2@soil.com');
-0 rows inserted/updated/deleted
-ij> SELECT id, e_mail, ok from EMC.CONTACTS;
-ID |E_MAIL |OK
--------------------------------------------------
-1 |bill@ruletheworld.com |NULL
-2 |penguin@antartic.com |NULL
-3 |big@blue.com |NULL
-99 |wormspam2@soil.com |NULL
-ij> DELETE FROM EMC.CONTACTS WHERE ID = 99;
-1 row inserted/updated/deleted
-ij> -- now change the application to run checks on the e-mail
--- address to ensure it is valid (in this case by seeing if
--- simply includes 'spam' in the title.
-CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2.jar', 'EMC.MAIL_APP');
-0 rows inserted/updated/deleted
-ij> CALL EMC.ADDCONTACT(4, 'spammer@ripoff.com');
-0 rows inserted/updated/deleted
-ij> CALL EMC.ADDCONTACT(5, 'open@source.org');
-0 rows inserted/updated/deleted
-ij> SELECT id, e_mail, ok from EMC.CONTACTS;
-ID |E_MAIL |OK
--------------------------------------------------
-1 |bill@ruletheworld.com |NULL
-2 |penguin@antartic.com |NULL
-3 |big@blue.com |NULL
-4 |spammer@ripoff.com |0
-5 |open@source.org |1
-ij> -- now add another jar in to test two jars and
--- a quoted identifer for the jar names.
-create schema "emcAddOn";
-0 rows inserted/updated/deleted
-ij> set schema emcAddOn;
-ERROR 42Y07: Schema 'EMCADDON' does not exist
-ij> set schema "emcAddOn";
-0 rows inserted/updated/deleted
-ij> create function "emcAddOn".VALIDCONTACT(e_mail VARCHAR(30))
-RETURNS SMALLINT
-READS SQL DATA
-external name 'org.apache.derbyTesting.databaseclassloader.addon.vendor.util.valid'
-language java parameter style java;
-0 rows inserted/updated/deleted
-ij> CALL SQLJ.INSTALL_JAR('file:extin/dcl_emcaddon.jar', '"emcAddOn"."MailAddOn"', 0);
-0 rows inserted/updated/deleted
-ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'EMC.MAIL_APP:"emcAddOn"."MailAddOn"');
-0 rows inserted/updated/deleted
-ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS;
-E_MAIL |2
--------------------------------------
-bill@ruletheworld.com |0
-penguin@antartic.com |0
-big@blue.com |0
-spammer@ripoff.com |0
-open@source.org |1
-ij> -- function that gets the signers of the class (loaded from the jar)
-create function EMC.GETSIGNERS(CLASS_NAME VARCHAR(256))
-RETURNS VARCHAR(60)
-NO SQL
-external name 'org.apache.derbyTesting.databaseclassloader.emc.getSigners'
-language java parameter style java;
-0 rows inserted/updated/deleted
-ij> -- at this point the jar is not signed, NULL expected
-VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.emc');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-NULL
-ij> -- Replace with a signed jar
--- (self signed certificate)
---
--- Commands used to sign jar
--- keytool -genkey -dname "cn=EMC CTO, ou=EMC APP, o=Easy Mail Company, c=US" -alias emccto -keypass kpi135 -keystore emcks -storepass ab987c
--- keytool -selfcert -alias emccto -keypass kpi135 -keystore emcks -storepass ab987c
--- jarsigner -keystore emcks -storepass ab987c -keypass kpi135 -signedjar dcl_emc2s.jar dcl_emc2.jar emccto
---
---
-CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2s.jar', 'EMC.MAIL_APP');
-0 rows inserted/updated/deleted
-ij> VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.emc');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-CN=EMC CTO, OU=EMC APP, O=Easy Mail Company, C=US
-ij> -- other jar should not be signed
-VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.addon.vendor.util');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-NULL
-ij> -- Jar up this database (wombat) for use in database in a jar testing
--- at the end of this script.
-disconnect;
-ij> connect 'jdbc:derby:wombat;shutdown=true';
-ERROR 08006: Database 'wombat' shutdown.
-ij> -- jar up the database
-connect 'jdbc:derby:db1;create=true';
-ij> create procedure CREATEARCHIVE(jarName VARCHAR(20), path VARCHAR(20), dbName VARCHAR(20))
-LANGUAGE JAVA PARAMETER STYLE JAVA
-NO SQL
-EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.dbjarUtil.createArchive';
-0 rows inserted/updated/deleted
-ij> call CREATEARCHIVE('ina.jar', 'wombat', 'db7');
-0 rows inserted/updated/deleted
-ij> disconnect;
-ij> connect 'jdbc:derby:wombat';
-ij> -- replace with a hacked jar file, emc.class modified to diable
--- valid e-mail address check but using same signatures.
--- ie direct replacement of the .class file.
-CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2sm.jar', 'EMC.MAIL_APP');
-0 rows inserted/updated/deleted
-ij> CALL EMC.ADDCONTACT(99, 'spamking@cracker.org');
-ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public.
-ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc : Security exception thrown accessing class org.apache.derbyTesting.databaseclassloader.emc in jar "EMC"."MAIL_APP" : SHA1 digest error for org/apache/derbyTesting/databaseclassloader/emc.class: java.lang.ClassNotFoundException'.
-ij> -- replace with a hacked jar file, emc.class modified to
--- be an invalid jar file (no signing on this jar).
-CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2l.jar', 'EMC.MAIL_APP');
-0 rows inserted/updated/deleted
-ij> CALL EMC.ADDCONTACT(999, 'spamking2@cracker.org');
-ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public.
-ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc : org/apache/derbyTesting/databaseclassloader/emc (Unsupported major.minor version 32558.32639): java.lang.ClassNotFoundException'.
-ij> -- cleanup
-CALL SQLJ.REMOVE_JAR('EMC.MAIL_APP', 0);
-ERROR X0X07: Cannot drop jar file '"EMC"."MAIL_APP"' because its on your db2j.database.classpath '"EMC"."MAIL_APP"'.
-ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', '"emcAddOn"."MailAddOn"');
-0 rows inserted/updated/deleted
-ij> CALL EMC.ADDCONTACT(99, 'cash@venture.com');
-ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public.
-ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc: java.lang.ClassNotFoundException'.
-ij> CALL SQLJ.REMOVE_JAR('EMC.MAIL_APP', 0);
-0 rows inserted/updated/deleted
-ij> DROP PROCEDURE EMC.ADDCONTACT;
-0 rows inserted/updated/deleted
-ij> DROP FUNCTION EMC.GETSIGNERS;
-0 rows inserted/updated/deleted
-ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS;
-E_MAIL |2
--------------------------------------
-bill@ruletheworld.com |0
-penguin@antartic.com |0
-big@blue.com |0
-spammer@ripoff.com |0
-open@source.org |1
-ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', '');
-0 rows inserted/updated/deleted
-ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS;
-ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.addon.vendor.util' does not exist or is inaccessible. This can happen if the class is not public.
-ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.addon.vendor.util: java.lang.ClassNotFoundException'.
-ij> CALL SQLJ.REMOVE_JAR('"emcAddOn"."MailAddOn"', 0);
-0 rows inserted/updated/deleted
-ij> DROP FUNCTION "emcAddOn".VALIDCONTACT;
-0 rows inserted/updated/deleted
-ij> DROP TABLE EMC.CONTACTS;
-0 rows inserted/updated/deleted
-ij> disconnect;
-ij> -- test reading a database from a jar file and loading
--- classes etc. from the jars within the database.
--- first using the jar protocol and then the classpath option.
-connect 'jdbc:derby:jar:(ina.jar)db7' AS DB7;
-ij> run resource '/org/apache/derbyTesting/functionTests/tests/lang/dcl_readOnly.sql';
-ij> -- common tests for read-only jarred database
-select * from EMC.CONTACTS;
-ID |E_MAIL |OK
--------------------------------------------------
-1 |bill@ruletheworld.com |NULL
-2 |penguin@antartic.com |NULL
-3 |big@blue.com |NULL
-4 |spammer@ripoff.com |0
-5 |open@source.org |1
-ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS;
-E_MAIL |2
--------------------------------------
-bill@ruletheworld.com |0
-penguin@antartic.com |0
-big@blue.com |0
-spammer@ripoff.com |0
-open@source.org |1
-ij> insert into EMC.CONTACTS values(3, 'no@is_read_only.gov', NULL);
-ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.
-ij> CALL EMC.ADDCONTACT(3, 'really@is_read_only.gov');
-ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.
-ij> -- same set as dcl.sql for reading resources
--- VALUES EMC.GETARTICLE('graduate.txt');
--- VALUES EMC.GETARTICLE('/article/release.txt');
--- VALUES EMC.GETARTICLE('/article/fred.txt');
--- VALUES EMC.GETARTICLE('barney.txt');
--- VALUES EMC.GETARTICLE('emc.class');
--- VALUES EMC.GETARTICLE('/org/apache/derbyTesting/databaseclassloader/emc.class');
--- signed
-VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.emc');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-CN=EMC CTO, OU=EMC APP, O=Easy Mail Company, C=US
-ij> -- not signed
-VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.addon.vendor.util');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-NULL
-ij> -- ensure that a read-only database automatically gets table locking
-autocommit off;
-ij> select * from EMC.CONTACTS WITH RR;
-ID |E_MAIL |OK
--------------------------------------------------
-1 |bill@ruletheworld.com |NULL
-2 |penguin@antartic.com |NULL
-3 |big@blue.com |NULL
-4 |spammer@ripoff.com |0
-5 |open@source.org |1
-ij> select TYPE, MODE, TABLENAME from syscs_diag.lock_table ORDER BY 1,2,3;
-TYPE |MODE|TABLENAME
--------------------------------------------------------------------------------------------------------------------------------------------
-TABLE|S |CONTACTS
-ij> disconnect;
-ij> -- connect to database in jar file via classpath
--- should fail as it is not on the classpath yet.
-connect 'jdbc:derby:classpath:db7' AS DB7CLF;
-ERROR XJ004: Database 'classpath:db7' not found.
-ij> -- create a class loader for this current thread
-connect 'jdbc:derby:db1';
-ij> create procedure setDBContextClassLoader(JARNAME VARCHAR(20))
-LANGUAGE JAVA PARAMETER STYLE JAVA
-NO SQL
-EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.dbjarUtil.setDBContextClassLoader';
-0 rows inserted/updated/deleted
-ij> call setDBContextClassLoader('ina.jar');
-0 rows inserted/updated/deleted
-ij> disconnect;
-ij> connect 'jdbc:derby:classpath:db7' AS DB7CL;
-ij> run resource '/org/apache/derbyTesting/functionTests/tests/lang/dcl_readOnly.sql';
-ij> -- common tests for read-only jarred database
-select * from EMC.CONTACTS;
-ID |E_MAIL |OK
--------------------------------------------------
-1 |bill@ruletheworld.com |NULL
-2 |penguin@antartic.com |NULL
-3 |big@blue.com |NULL
-4 |spammer@ripoff.com |0
-5 |open@source.org |1
-ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS;
-E_MAIL |2
--------------------------------------
-bill@ruletheworld.com |0
-penguin@antartic.com |0
-big@blue.com |0
-spammer@ripoff.com |0
-open@source.org |1
-ij> insert into EMC.CONTACTS values(3, 'no@is_read_only.gov', NULL);
-ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.
-ij> CALL EMC.ADDCONTACT(3, 'really@is_read_only.gov');
-ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.
-ij> -- same set as dcl.sql for reading resources
--- VALUES EMC.GETARTICLE('graduate.txt');
--- VALUES EMC.GETARTICLE('/article/release.txt');
--- VALUES EMC.GETARTICLE('/article/fred.txt');
--- VALUES EMC.GETARTICLE('barney.txt');
--- VALUES EMC.GETARTICLE('emc.class');
--- VALUES EMC.GETARTICLE('/org/apache/derbyTesting/databaseclassloader/emc.class');
--- signed
-VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.emc');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-CN=EMC CTO, OU=EMC APP, O=Easy Mail Company, C=US
-ij> -- not signed
-VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.addon.vendor.util');
-1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-NULL
-ij> -- ensure that a read-only database automatically gets table locking
-autocommit off;
-ij> select * from EMC.CONTACTS WITH RR;
-ID |E_MAIL |OK
--------------------------------------------------
-1 |bill@ruletheworld.com |NULL
-2 |penguin@antartic.com |NULL
-3 |big@blue.com |NULL
-4 |spammer@ripoff.com |0
-5 |open@source.org |1
-ij> select TYPE, MODE, TABLENAME from syscs_diag.lock_table ORDER BY 1,2,3;
-TYPE |MODE|TABLENAME
--------------------------------------------------------------------------------------------------------------------------------------------
-TABLE|S |CONTACTS
-ij> disconnect;
-ij>
+ij> -- test database class loading.
+maximumdisplaywidth 300;
+ij> create schema emc;
+0 rows inserted/updated/deleted
+ij> set schema emc;
+0 rows inserted/updated/deleted
+ij> create table contacts (id int primary key, e_mail varchar(30));
+0 rows inserted/updated/deleted
+ij> create procedure EMC.ADDCONTACT(id INT, e_mail VARCHAR(30))
+MODIFIES SQL DATA
+external name 'org.apache.derbyTesting.databaseclassloader.emc.addContact'
+language java parameter style java;
+0 rows inserted/updated/deleted
+ij> create function EMC.GETARTICLE(path VARCHAR(40)) RETURNS VARCHAR(256)
+NO SQL
+external name 'org.apache.derbyTesting.databaseclassloader.emc.getArticle'
+language java parameter style java;
+0 rows inserted/updated/deleted
+ij> -- fails because no class in classpath,
+CALL EMC.ADDCONTACT(1, 'bill@somecompany.com');
+ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public.
+ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc: java.lang.ClassNotFoundException'.
+ij> -- install the jar, copied there by the magic of supportfiles
+-- in the test harness (dcl_app.properties). The source for
+-- the class is contained within the jar for reference.
+CALL SQLJ.INSTALL_JAR('file:extin/dcl_emc1.jar', 'EMC.MAIL_APP', 0);
+0 rows inserted/updated/deleted
+ij> -- fails because no class not in classpath, jar file not in database classpath.
+CALL EMC.ADDCONTACT(1, 'bill@somecompany.com');
+ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public.
+ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc: java.lang.ClassNotFoundException'.
+ij> -- now add this into the database class path
+call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'EMC.MAIL_APP');
+0 rows inserted/updated/deleted
+ij> -- all should work now
+CALL EMC.ADDCONTACT(1, 'bill@ruletheworld.com');
+0 rows inserted/updated/deleted
+ij> CALL EMC.ADDCONTACT(2, 'penguin@antartic.com');
+0 rows inserted/updated/deleted
+ij> SELECT id, e_mail from EMC.CONTACTS;
+ID |E_MAIL
+------------------------------------------
+1 |bill@ruletheworld.com
+2 |penguin@antartic.com
+ij> -- Test resource loading from the jar file
+-- Simple path should be prepended with the package name
+-- of the class executing the code to find
+-- /org/apache/derbyTesting/databaseclassloader/graduation.txt
+VALUES EMC.GETARTICLE('graduate.txt');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+The Apache Foundation has released the first version of the open-source Derby database, which also gained support from Sun Microsystems.
+ij> -- now an absolute path
+VALUES EMC.GETARTICLE('/article/release.txt');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+The Apache Derby development community is pleased to announce its first release after graduating from the Apache Incubator, Apache Derby 10.1.1.0.
+ij> -- no such resources
+VALUES EMC.GETARTICLE('/article/fred.txt');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+NULL
+ij> VALUES EMC.GETARTICLE('barney.txt');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+NULL
+ij> -- try to read the class file should be disallowed
+-- by returning null
+VALUES EMC.GETARTICLE('emc.class');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+NULL
+ij> VALUES EMC.GETARTICLE('/org/apache/derbyTesting/databaseclassloader/emc.class');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+NULL
+ij> -- now the application needs to track if e-mails are valid
+ALTER TABLE EMC.CONTACTS ADD COLUMN OK SMALLINT;
+0 rows inserted/updated/deleted
+ij> SELECT id, e_mail, ok from EMC.CONTACTS;
+ID |E_MAIL |OK
+-------------------------------------------------
+1 |bill@ruletheworld.com |NULL
+2 |penguin@antartic.com |NULL
+ij> -- well written application, INSERT used explicit column names
+-- ok defaults to NULL
+CALL EMC.ADDCONTACT(3, 'big@blue.com');
+0 rows inserted/updated/deleted
+ij> SELECT id, e_mail, ok from EMC.CONTACTS;
+ID |E_MAIL |OK
+-------------------------------------------------
+1 |bill@ruletheworld.com |NULL
+2 |penguin@antartic.com |NULL
+3 |big@blue.com |NULL
+ij> -- check the roll back of class loading.
+-- install a new jar in a transaction, see
+-- that the new class is used and then rollback
+-- the old class should be used after the rollback.
+AUTOCOMMIT OFF;
+ij> CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2.jar', 'EMC.MAIL_APP');
+0 rows inserted/updated/deleted
+ij> CALL EMC.ADDCONTACT(99, 'wormspam@soil.com');
+0 rows inserted/updated/deleted
+ij> SELECT id, e_mail, ok from EMC.CONTACTS;
+ID |E_MAIL |OK
+-------------------------------------------------
+1 |bill@ruletheworld.com |NULL
+2 |penguin@antartic.com |NULL
+3 |big@blue.com |NULL
+99 |wormspam@soil.com |0
+ij> rollback;
+ij> AUTOCOMMIT ON;
+ij> SELECT id, e_mail, ok from EMC.CONTACTS;
+ID |E_MAIL |OK
+-------------------------------------------------
+1 |bill@ruletheworld.com |NULL
+2 |penguin@antartic.com |NULL
+3 |big@blue.com |NULL
+ij> CALL EMC.ADDCONTACT(99, 'wormspam2@soil.com');
+0 rows inserted/updated/deleted
+ij> SELECT id, e_mail, ok from EMC.CONTACTS;
+ID |E_MAIL |OK
+-------------------------------------------------
+1 |bill@ruletheworld.com |NULL
+2 |penguin@antartic.com |NULL
+3 |big@blue.com |NULL
+99 |wormspam2@soil.com |NULL
+ij> DELETE FROM EMC.CONTACTS WHERE ID = 99;
+1 row inserted/updated/deleted
+ij> -- now change the application to run checks on the e-mail
+-- address to ensure it is valid (in this case by seeing if
+-- simply includes 'spam' in the title.
+CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2.jar', 'EMC.MAIL_APP');
+0 rows inserted/updated/deleted
+ij> CALL EMC.ADDCONTACT(4, 'spammer@ripoff.com');
+0 rows inserted/updated/deleted
+ij> CALL EMC.ADDCONTACT(5, 'open@source.org');
+0 rows inserted/updated/deleted
+ij> SELECT id, e_mail, ok from EMC.CONTACTS;
+ID |E_MAIL |OK
+-------------------------------------------------
+1 |bill@ruletheworld.com |NULL
+2 |penguin@antartic.com |NULL
+3 |big@blue.com |NULL
+4 |spammer@ripoff.com |0
+5 |open@source.org |1
+ij> -- now add another jar in to test two jars and
+-- a quoted identifer for the jar names.
+create schema "emcAddOn";
+0 rows inserted/updated/deleted
+ij> set schema emcAddOn;
+ERROR 42Y07: Schema 'EMCADDON' does not exist
+ij> set schema "emcAddOn";
+0 rows inserted/updated/deleted
+ij> create function "emcAddOn".VALIDCONTACT(e_mail VARCHAR(30))
+RETURNS SMALLINT
+READS SQL DATA
+external name 'org.apache.derbyTesting.databaseclassloader.addon.vendor.util.valid'
+language java parameter style java;
+0 rows inserted/updated/deleted
+ij> CALL SQLJ.INSTALL_JAR('file:extin/dcl_emcaddon.jar', '"emcAddOn"."MailAddOn"', 0);
+0 rows inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'EMC.MAIL_APP:"emcAddOn"."MailAddOn"');
+0 rows inserted/updated/deleted
+ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS;
+E_MAIL |2
+-------------------------------------
+bill@ruletheworld.com |0
+penguin@antartic.com |0
+big@blue.com |0
+spammer@ripoff.com |0
+open@source.org |1
+ij> -- function that gets the signers of the class (loaded from the jar)
+create function EMC.GETSIGNERS(CLASS_NAME VARCHAR(256))
+RETURNS VARCHAR(60)
+NO SQL
+external name 'org.apache.derbyTesting.databaseclassloader.emc.getSigners'
+language java parameter style java;
+0 rows inserted/updated/deleted
+ij> -- at this point the jar is not signed, NULL expected
+VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.emc');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+NULL
+ij> -- Replace with a signed jar
+-- (self signed certificate)
+--
+-- Commands used to sign jar
+-- keytool -genkey -dname "cn=EMC CTO, ou=EMC APP, o=Easy Mail Company, c=US" -alias emccto -keypass kpi135 -keystore emcks -storepass ab987c
+-- keytool -selfcert -alias emccto -keypass kpi135 -keystore emcks -storepass ab987c
+-- jarsigner -keystore emcks -storepass ab987c -keypass kpi135 -signedjar dcl_emc2s.jar dcl_emc2.jar emccto
+--
+--
+CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2s.jar', 'EMC.MAIL_APP');
+0 rows inserted/updated/deleted
+ij> VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.emc');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+CN=EMC CTO, OU=EMC APP, O=Easy Mail Company, C=US
+ij> -- other jar should not be signed
+VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.addon.vendor.util');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+NULL
+ij> -- Jar up this database (wombat) for use in database in a jar testing
+-- at the end of this script.
+disconnect;
+ij> connect 'jdbc:derby:wombat;shutdown=true';
+ERROR 08006: Database 'wombat' shutdown.
+ij> -- jar up the database
+connect 'jdbc:derby:db1;create=true';
+ij> create procedure CREATEARCHIVE(jarName VARCHAR(20), path VARCHAR(20), dbName VARCHAR(20))
+LANGUAGE JAVA PARAMETER STYLE JAVA
+NO SQL
+EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.dbjarUtil.createArchive';
+0 rows inserted/updated/deleted
+ij> call CREATEARCHIVE('ina.jar', 'wombat', 'db7');
+0 rows inserted/updated/deleted
+ij> disconnect;
+ij> connect 'jdbc:derby:wombat';
+ij> -- replace with a hacked jar file, emc.class modified to diable
+-- valid e-mail address check but using same signatures.
+-- ie direct replacement of the .class file.
+CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2sm.jar', 'EMC.MAIL_APP');
+0 rows inserted/updated/deleted
+ij> CALL EMC.ADDCONTACT(99, 'spamking@cracker.org');
+ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public.
+ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc : Security exception thrown accessing class org.apache.derbyTesting.databaseclassloader.emc in jar "EMC"."MAIL_APP" : SHA1 digest error for org/apache/derbyTesting/databaseclassloader/emc.class: java.lang.ClassNotFoundException'.
+ij> -- replace with a hacked jar file, emc.class modified to
+-- be an invalid jar file (no signing on this jar).
+CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2l.jar', 'EMC.MAIL_APP');
+0 rows inserted/updated/deleted
+ij> CALL EMC.ADDCONTACT(999, 'spamking2@cracker.org');
+ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public.
+ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc : org/apache/derbyTesting/databaseclassloader/emc (Unsupported major.minor version 32558.32639): java.lang.ClassNotFoundException'.
+ij> -- cleanup
+CALL SQLJ.REMOVE_JAR('EMC.MAIL_APP', 0);
+ERROR X0X07: Cannot drop jar file '"EMC"."MAIL_APP"' because its on your db2j.database.classpath '"EMC"."MAIL_APP"'.
+ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', '"emcAddOn"."MailAddOn"');
+0 rows inserted/updated/deleted
+ij> CALL EMC.ADDCONTACT(99, 'cash@venture.com');
+ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public.
+ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc: java.lang.ClassNotFoundException'.
+ij> CALL SQLJ.REMOVE_JAR('EMC.MAIL_APP', 0);
+0 rows inserted/updated/deleted
+ij> DROP PROCEDURE EMC.ADDCONTACT;
+0 rows inserted/updated/deleted
+ij> DROP FUNCTION EMC.GETSIGNERS;
+0 rows inserted/updated/deleted
+ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS;
+E_MAIL |2
+-------------------------------------
+bill@ruletheworld.com |0
+penguin@antartic.com |0
+big@blue.com |0
+spammer@ripoff.com |0
+open@source.org |1
+ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', '');
+0 rows inserted/updated/deleted
+ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS;
+ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.addon.vendor.util' does not exist or is inaccessible. This can happen if the class is not public.
+ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.addon.vendor.util: java.lang.ClassNotFoundException'.
+ij> CALL SQLJ.REMOVE_JAR('"emcAddOn"."MailAddOn"', 0);
+0 rows inserted/updated/deleted
+ij> DROP FUNCTION "emcAddOn".VALIDCONTACT;
+0 rows inserted/updated/deleted
+ij> DROP TABLE EMC.CONTACTS;
+0 rows inserted/updated/deleted
+ij> disconnect;
+ij> -- test reading a database from a jar file and loading
+-- classes etc. from the jars within the database.
+-- first using the jar protocol and then the classpath option.
+connect 'jdbc:derby:jar:(ina.jar)db7' AS DB7;
+ij> run resource '/org/apache/derbyTesting/functionTests/tests/lang/dcl_readOnly.sql';
+ij> -- common tests for read-only jarred database
+select * from EMC.CONTACTS;
+ID |E_MAIL |OK
+-------------------------------------------------
+1 |bill@ruletheworld.com |NULL
+2 |penguin@antartic.com |NULL
+3 |big@blue.com |NULL
+4 |spammer@ripoff.com |0
+5 |open@source.org |1
+ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS;
+E_MAIL |2
+-------------------------------------
+bill@ruletheworld.com |0
+penguin@antartic.com |0
+big@blue.com |0
+spammer@ripoff.com |0
+open@source.org |1
+ij> insert into EMC.CONTACTS values(3, 'no@is_read_only.gov', NULL);
+ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.
+ij> CALL EMC.ADDCONTACT(3, 'really@is_read_only.gov');
+ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.
+ij> -- same set as dcl.sql for reading resources
+-- VALUES EMC.GETARTICLE('graduate.txt');
+-- VALUES EMC.GETARTICLE('/article/release.txt');
+-- VALUES EMC.GETARTICLE('/article/fred.txt');
+-- VALUES EMC.GETARTICLE('barney.txt');
+-- VALUES EMC.GETARTICLE('emc.class');
+-- VALUES EMC.GETARTICLE('/org/apache/derbyTesting/databaseclassloader/emc.class');
+-- signed
+VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.emc');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+CN=EMC CTO, OU=EMC APP, O=Easy Mail Company, C=US
+ij> -- not signed
+VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.addon.vendor.util');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+NULL
+ij> -- ensure that a read-only database automatically gets table locking
+autocommit off;
+ij> select * from EMC.CONTACTS WITH RR;
+ID |E_MAIL |OK
+-------------------------------------------------
+1 |bill@ruletheworld.com |NULL
+2 |penguin@antartic.com |NULL
+3 |big@blue.com |NULL
+4 |spammer@ripoff.com |0
+5 |open@source.org |1
+ij> select TYPE, MODE, TABLENAME from syscs_diag.lock_table ORDER BY 1,2,3;
+TYPE |MODE|TABLENAME
+-------------------------------------------------------------------------------------------------------------------------------------------
+TABLE|S |CONTACTS
+ij> disconnect;
+ij> -- connect to database in jar file via classpath
+-- should fail as it is not on the classpath yet.
+connect 'jdbc:derby:classpath:db7' AS DB7CLF;
+ERROR XJ004: Database 'classpath:db7' not found.
+ij> -- create a class loader for this current thread
+connect 'jdbc:derby:db1';
+ij> create procedure setDBContextClassLoader(JARNAME VARCHAR(20))
+LANGUAGE JAVA PARAMETER STYLE JAVA
+NO SQL
+EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.dbjarUtil.setDBContextClassLoader';
+0 rows inserted/updated/deleted
+ij> call setDBContextClassLoader('ina.jar');
+0 rows inserted/updated/deleted
+ij> disconnect;
+ij> connect 'jdbc:derby:classpath:db7' AS DB7CL;
+ij> run resource '/org/apache/derbyTesting/functionTests/tests/lang/dcl_readOnly.sql';
+ij> -- common tests for read-only jarred database
+select * from EMC.CONTACTS;
+ID |E_MAIL |OK
+-------------------------------------------------
+1 |bill@ruletheworld.com |NULL
+2 |penguin@antartic.com |NULL
+3 |big@blue.com |NULL
+4 |spammer@ripoff.com |0
+5 |open@source.org |1
+ij> select e_mail, "emcAddOn".VALIDCONTACT(e_mail) from EMC.CONTACTS;
+E_MAIL |2
+-------------------------------------
+bill@ruletheworld.com |0
+penguin@antartic.com |0
+big@blue.com |0
+spammer@ripoff.com |0
+open@source.org |1
+ij> insert into EMC.CONTACTS values(3, 'no@is_read_only.gov', NULL);
+ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.
+ij> CALL EMC.ADDCONTACT(3, 'really@is_read_only.gov');
+ERROR 25502: An SQL data change is not permitted for a read-only connection, user or database.
+ij> -- same set as dcl.sql for reading resources
+-- VALUES EMC.GETARTICLE('graduate.txt');
+-- VALUES EMC.GETARTICLE('/article/release.txt');
+-- VALUES EMC.GETARTICLE('/article/fred.txt');
+-- VALUES EMC.GETARTICLE('barney.txt');
+-- VALUES EMC.GETARTICLE('emc.class');
+-- VALUES EMC.GETARTICLE('/org/apache/derbyTesting/databaseclassloader/emc.class');
+-- signed
+VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.emc');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+CN=EMC CTO, OU=EMC APP, O=Easy Mail Company, C=US
+ij> -- not signed
+VALUES EMC.GETSIGNERS('org.apache.derbyTesting.databaseclassloader.addon.vendor.util');
+1
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+NULL
+ij> -- ensure that a read-only database automatically gets table locking
+autocommit off;
+ij> select * from EMC.CONTACTS WITH RR;
+ID |E_MAIL |OK
+-------------------------------------------------
+1 |bill@ruletheworld.com |NULL
+2 |penguin@antartic.com |NULL
+3 |big@blue.com |NULL
+4 |spammer@ripoff.com |0
+5 |open@source.org |1
+ij> select TYPE, MODE, TABLENAME from syscs_diag.lock_table ORDER BY 1,2,3;
+TYPE |MODE|TABLENAME
+-------------------------------------------------------------------------------------------------------------------------------------------
+TABLE|S |CONTACTS
+ij> disconnect;
+ij>
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dcl.out
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/derby94.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/derby94.out?rev=329187&r1=329186&r2=329187&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/derby94.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/derby94.out Fri Oct 28 04:51:50 2005
@@ -1,83 +1,83 @@
-ij> create view lock_table as select
-cast(l.type as char(8)) as type,cast(lockcount as char(3)) as
-cnt,mode,cast(tablename as char(12)) as tabname,cast(lockname as char(10))
-as lockname,state from syscs_diag.lock_table l ;
-0 rows inserted/updated/deleted
-ij> autocommit off;
-ij> create table derby94_t1(c1 int, c2 int not null primary key);
-0 rows inserted/updated/deleted
-ij> create table derby94_t2(c1 int);
-0 rows inserted/updated/deleted
-ij> insert into derby94_t1 values (0, 200), (1, 201), (2, 202), (3, 203), (4, 204), (5, 205), (6, 206), (7, 207), (8, 208), (9, 209);
-10 rows inserted/updated/deleted
-ij> insert into derby94_t1 select c1+10 , c2 +10 from derby94_t1;
-10 rows inserted/updated/deleted
-ij> insert into derby94_t1 select c1+20 , c2 +20 from derby94_t1;
-20 rows inserted/updated/deleted
-ij> insert into derby94_t1 select c1+40 , c2 +40 from derby94_t1;
-40 rows inserted/updated/deleted
-ij> insert into derby94_t1 select c1+80 , c2 +80 from derby94_t1;
-80 rows inserted/updated/deleted
-ij> insert into derby94_t2 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
-10 rows inserted/updated/deleted
-ij> commit;
-ij> get cursor c1 as 'select * from derby94_t1 FOR UPDATE of c1';
-ij> next c1;
-C1 |C2
------------------------
-0 |200
-ij> update derby94_t1 set c1=c1+999 WHERE CURRENT OF c1;
-1 row inserted/updated/deleted
-ij> next c1;
-C1 |C2
------------------------
-1 |201
-ij> get cursor c2 as 'select * from derby94_t2 FOR UPDATE of c1';
-ij> next c2 ;
-C1
------------
-0
-ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname ;
-TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE
-------------------------------------------------
-TABLE |2 |IX |DERBY94_T1 |Tablelock |GRANT
-ROW |1 |U |DERBY94_T1 |(1,8) |GRANT
-ROW |1 |X |DERBY94_T1 |(1,7) |GRANT
-TABLE |1 |IX |DERBY94_T2 |Tablelock |GRANT
-ROW |1 |U |DERBY94_T2 |(1,7) |GRANT
-ij> --following insert should get X lock on derby94_t2 because of escalation , but should leave U lock on derby94_t1 as it is
-insert into derby94_t2 select c1 from derby94_t1 ;
-160 rows inserted/updated/deleted
-ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname ;
-TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE
-------------------------------------------------
-TABLE |3 |IX |DERBY94_T1 |Tablelock |GRANT
-ROW |1 |U |DERBY94_T1 |(1,8) |GRANT
-ROW |1 |X |DERBY94_T1 |(1,7) |GRANT
-TABLE |4 |IX |DERBY94_T2 |Tablelock |GRANT
-TABLE |1 |X |DERBY94_T2 |Tablelock |GRANT
-ij> --following update statement should escalate the locks on derby94_t1 to table level X lock
-update derby94_t1 set c1=c1+999 ;
-160 rows inserted/updated/deleted
-ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname ;
-TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE
-------------------------------------------------
-TABLE |8 |IX |DERBY94_T1 |Tablelock |GRANT
-TABLE |1 |X |DERBY94_T1 |Tablelock |GRANT
-TABLE |4 |IX |DERBY94_T2 |Tablelock |GRANT
-TABLE |1 |X |DERBY94_T2 |Tablelock |GRANT
-ij> close c1 ;
-ij> close c2 ;
-ij> commit ;
-ij> --following lock table dump should not show any locks, above commit should have release them
-select * from lock_table order by tabname, type desc, mode, cnt, lockname;
-TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE
-------------------------------------------------
-ij> drop table derby94_t1;
-0 rows inserted/updated/deleted
-ij> drop table derby94_t2;
-0 rows inserted/updated/deleted
-ij> commit;
-ij> --end derby-94 case
-;
-ij>
+ij> create view lock_table as select
+cast(l.type as char(8)) as type,cast(lockcount as char(3)) as
+cnt,mode,cast(tablename as char(12)) as tabname,cast(lockname as char(10))
+as lockname,state from syscs_diag.lock_table l ;
+0 rows inserted/updated/deleted
+ij> autocommit off;
+ij> create table derby94_t1(c1 int, c2 int not null primary key);
+0 rows inserted/updated/deleted
+ij> create table derby94_t2(c1 int);
+0 rows inserted/updated/deleted
+ij> insert into derby94_t1 values (0, 200), (1, 201), (2, 202), (3, 203), (4, 204), (5, 205), (6, 206), (7, 207), (8, 208), (9, 209);
+10 rows inserted/updated/deleted
+ij> insert into derby94_t1 select c1+10 , c2 +10 from derby94_t1;
+10 rows inserted/updated/deleted
+ij> insert into derby94_t1 select c1+20 , c2 +20 from derby94_t1;
+20 rows inserted/updated/deleted
+ij> insert into derby94_t1 select c1+40 , c2 +40 from derby94_t1;
+40 rows inserted/updated/deleted
+ij> insert into derby94_t1 select c1+80 , c2 +80 from derby94_t1;
+80 rows inserted/updated/deleted
+ij> insert into derby94_t2 values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
+10 rows inserted/updated/deleted
+ij> commit;
+ij> get cursor c1 as 'select * from derby94_t1 FOR UPDATE of c1';
+ij> next c1;
+C1 |C2
+-----------------------
+0 |200
+ij> update derby94_t1 set c1=c1+999 WHERE CURRENT OF c1;
+1 row inserted/updated/deleted
+ij> next c1;
+C1 |C2
+-----------------------
+1 |201
+ij> get cursor c2 as 'select * from derby94_t2 FOR UPDATE of c1';
+ij> next c2 ;
+C1
+-----------
+0
+ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname ;
+TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE
+------------------------------------------------
+TABLE |2 |IX |DERBY94_T1 |Tablelock |GRANT
+ROW |1 |U |DERBY94_T1 |(1,8) |GRANT
+ROW |1 |X |DERBY94_T1 |(1,7) |GRANT
+TABLE |1 |IX |DERBY94_T2 |Tablelock |GRANT
+ROW |1 |U |DERBY94_T2 |(1,7) |GRANT
+ij> --following insert should get X lock on derby94_t2 because of escalation , but should leave U lock on derby94_t1 as it is
+insert into derby94_t2 select c1 from derby94_t1 ;
+160 rows inserted/updated/deleted
+ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname ;
+TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE
+------------------------------------------------
+TABLE |3 |IX |DERBY94_T1 |Tablelock |GRANT
+ROW |1 |U |DERBY94_T1 |(1,8) |GRANT
+ROW |1 |X |DERBY94_T1 |(1,7) |GRANT
+TABLE |4 |IX |DERBY94_T2 |Tablelock |GRANT
+TABLE |1 |X |DERBY94_T2 |Tablelock |GRANT
+ij> --following update statement should escalate the locks on derby94_t1 to table level X lock
+update derby94_t1 set c1=c1+999 ;
+160 rows inserted/updated/deleted
+ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname ;
+TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE
+------------------------------------------------
+TABLE |8 |IX |DERBY94_T1 |Tablelock |GRANT
+TABLE |1 |X |DERBY94_T1 |Tablelock |GRANT
+TABLE |4 |IX |DERBY94_T2 |Tablelock |GRANT
+TABLE |1 |X |DERBY94_T2 |Tablelock |GRANT
+ij> close c1 ;
+ij> close c2 ;
+ij> commit ;
+ij> --following lock table dump should not show any locks, above commit should have release them
+select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE
+------------------------------------------------
+ij> drop table derby94_t1;
+0 rows inserted/updated/deleted
+ij> drop table derby94_t2;
+0 rows inserted/updated/deleted
+ij> commit;
+ij> --end derby-94 case
+;
+ij>
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/derby94.out
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/emptyStatistics.out
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/encryptDatabase.out
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/encryptParams.out
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/fillLog.out
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/fullRecovery.out
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/fullRecoveryFail.out
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/intersect.out
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/caseI_tr_TR.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/caseI_tr_TR.out?rev=329187&r1=329186&r2=329187&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/caseI_tr_TR.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/caseI_tr_TR.out Fri Oct 28 04:51:50 2005
@@ -1,28 +1,28 @@
-CONNECTION0* - EnC:>9< jdbc:derby:trdb
-* = current connection
-ij> create function FMTUNICODE(P1 VARCHAR(100)) RETURNS VARCHAR(300)
-EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.format'
-NO SQL
-LANGUAGE JAVA PARAMETER STYLE JAVA;
-0 rows inserted/updated/deleted
-ij> values FMTUNICODE(UCASE('i'));
-1
---------------------------------------------------------------------------------------------------------------------------------
-I
-1 row selected
-ij> values FMTUNICODE(UCASE('I'));
-1
---------------------------------------------------------------------------------------------------------------------------------
-I
-1 row selected
-ij> values FMTUNICODE(LCASE('i'));
-1
---------------------------------------------------------------------------------------------------------------------------------
-i
-1 row selected
-ij> values FMTUNICODE(LCASE('I'));
-1
---------------------------------------------------------------------------------------------------------------------------------
-i
-1 row selected
-ij>
+CONNECTION0* - EnC:>9< jdbc:derby:trdb
+* = current connection
+ij> create function FMTUNICODE(P1 VARCHAR(100)) RETURNS VARCHAR(300)
+EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.format'
+NO SQL
+LANGUAGE JAVA PARAMETER STYLE JAVA;
+0 rows inserted/updated/deleted
+ij> values FMTUNICODE(UCASE('i'));
+1
+--------------------------------------------------------------------------------------------------------------------------------
+I
+1 row selected
+ij> values FMTUNICODE(UCASE('I'));
+1
+--------------------------------------------------------------------------------------------------------------------------------
+I
+1 row selected
+ij> values FMTUNICODE(LCASE('i'));
+1
+--------------------------------------------------------------------------------------------------------------------------------
+i
+1 row selected
+ij> values FMTUNICODE(LCASE('I'));
+1
+--------------------------------------------------------------------------------------------------------------------------------
+i
+1 row selected
+ij>
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_13/caseI_tr_TR.out
------------------------------------------------------------------------------
svn:eol-style = native