ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- 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) -- -- keytool -delete -alias emccto -keystore emcks -storepass ab987c -- 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 -validity 36500 -keystore emcks -storepass ab987c -- keytool -keystore emcks -storepass ab987c -list -v -- jarsigner -keystore emcks -storepass ab987c -keypass kpi135 -signedjar dcl_emc2s.jar dcl_emc2.jar emccto -- keytool -delete -alias emccto -keystore emcks -storepass ab987c -- 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 remove jar file '"EMC"."MAIL_APP"' because it is on your derby.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> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- 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> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- 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>