Chris Pollett >Old Classes
> |
HW2 Solutions PageFor the SSL DTD problem I was interested in a reasonable attempt to model the kind of data in a certificate. I also wanted to see you use all three of the XML components: ELEMENT, ATTLIST, and ENTITY we talked about in class to produce a valid document. Below is what I came up with: <?xml version="1.0" encoding="UTF-8"?> <!-- created by Chris Pollett (SJSU) --> <!--Root Element: SSLCertificate: I try to list out elements and attributes in breadth first order until the breadth gets too large. Then I just try the best I can.--> <!ELEMENT SSLCertificate (Subject, Issuer, PeriodOfValidity, AdministrativeInformation, ExtendedInformation)> <!--Child Elements of SSL Certificate--> <!ELEMENT Subject (DistinguishedName, PublicKey)> <!ELEMENT Issuer (DistinguishedName, PublicKey)> <!ELEMENT PeriodOfValidity (ValidFrom, ValidTo)> <!ELEMENT AdministrativeInformation EMPTY> <!ELEMENT ExtendedInformation (BasicConstraints, Flags)> <!--Elements of Subject and Issuer--> <!ELEMENT DistinguishedName (CommonName, OrganizationCompany, OrganizationalUnit, CityLocality, StateProvinve, Country)> <!ELEMENT PublicKey (#PCDATA)> <!--Elements of Period of Validity--> <!ELEMENT ValidFrom (#PCDATA)> <!ELEMENT ValidTo (#PCDATA)> <!--Attributes of AdministrativeInformation. Note Since there are only a fixed number of SSL Scheme out there now I decided to make these attributes.--> <!ATTLIST AdministrativeInformation Version (SSLv2.0 | SSLv3.0 | TLSv1.0) #REQUIRED SerialNumber CDATA "1" > <!--Elements of ExtendedInformation--> <!ELEMENT BasicConstraints EMPTY> <!ELEMENT Flags (#PCDATA)> <!--Elements of DistinguishedName--> <!ELEMENT CommonName (#PCDATA)> <!ELEMENT OrganizationCompany (#PCDATA)> <!ELEMENT CityLocality (#PCDATA)> <!ELEMENT StateProvince (#PCDATA)> <!ELEMENT Country (#PCDATA)> <!--Attributes of PublicKey--> <!ATTLIST PublicKey EncryptionAlgorithm CDATA "RSA" KeyLength CDATA "1024" > <!--Attributes of BasicConstraints--> <!ATTLIST BasicConstraints SubjectType (CA) #IMPLIED PathLength CDATA "0" > <!--Example Entity--> <!ENTITY FavouriteSerialNumber "08 7c 8e 1f ad 9b 56 a5 11 d3 56 9a 2c 4a 68 a0"> Create and Drop code REM REM MakeProject.bat REM REM DOS Batch file to make the HW2 Project REM REM Note for sqlj I added the following two paths to my classpath REM e:\oracle\designer\sqlj\lib\translator.lib REM e:\oracle\designer\sqlj\lib\runtime12ee.zip REM REM REM You should change the path to the next batch file to where it is on your machine REM This sets up the environment variables for CL.exe REM CALL "C:\Program Files\Microsoft Visual Studio .NET\Vc7\bin\vcvars32.bat" REM REM You should also modify the next files to match your system REM SET JAVAC=javac SET SQLJ="e:\oracle\designer\bin\sqlj" SET PROC="e:\oracle\designer\bin\proc" SET CC=CL SET INC="/IE:\oracle\designer\precomp\public" SET LIBRARY="E:\oracle\designer\precomp\LIB\msvc\ORASQL9.LIB" SET OUT=/o %SQLJ% Create.sqlj %SQLJ% Drop.sqlj %JAVAC% *.java %PROC% iname=create.pc %PROC% iname=drop.pc %PROC% iname=main.pc %CC% %INC% main.c create.c %LIBRARY% %OUT%create.exe %CC% %INC% main.c drop.c %LIBRARY% %OUT%drop.exe REM REM MakeClean.bat REM REM DOS BATCH file to clean project REM REM deletes all target and intermediate files for HW2 Project DEL *.class *.c *.o *.obj SQLJ*.java REM REM RunHw2.bat REM REM REM This is the DOS batch file used to run each operation of Hw2 REM REM To convert this batch file to a UNIX script do the follwoing kinds of replacements: REM REM REM --> # REM %ENVIRONMENT_VARIABLE% --> {$ENVIRONMENT_VARIABLE} REM SET JAVA=java REM SET CONNECT=jdbc:oracle:thin:@sigma.mathcs.sjsu.edu:1521:cs157b SET CONNECT=jdbc:oracle:thin:@localhost:1521:epsilon SET EMBEDCONNECT=localhost:1521:epsilon SET SERVICE_NAME=ep SET LOGIN=login SET PASSWORD=passwd REM In the following uncomment the line you want REM SQLJ commands %JAVA% Create %CONNECT% %LOGIN% %PASSWORD% REM %JAVA% Drop %CONNECT% %LOGIN% %PASSWORD% REM Proc commands REM create %SERVICE_NAME% %LOGIN% %PASSWORD% REM drop %SERVICE_NAME% %LOGIN% %PASSWORD% //Create.sqlj import java.io.*; import java.sql.*; import sqlj.runtime.*; import sqlj.runtime.ref.*; import oracle.sqlj.runtime.*; /** This class implements the Create transaction as decribed in the HW2 description using SQLJ. That is it creates a Book table and inserts the required number of rows in it. The execute() method is the method that actually does the job. */ public class Create { /** Performs Create transaction as described in HW2 @param connectString - string used to connect the oracle host:port:dbname @param login - login name on oracle @param password - password to log into oracle */ public static void execute(String connectString, String login, String password) { try { DriverManager.registerDriver( new oracle.jdbc.OracleDriver()); Oracle.connect(connectString, login, password, false); #sql { CREATE TABLE Book(bookid INTEGER, title CHAR(80), author CHAR(80), price REAL) }; for(int i=1; i <= 111; i++) { #sql{INSERT INTO Book VALUES(:i, 'AAA', 'AAA Author', 5.999)}; i++; #sql{INSERT INTO Book VALUES(:i, 'BBB', 'BBB Author', 5.999)}; i++; #sql{INSERT INTO Book VALUES(:i, 'CCC', 'CCC Author', 5.999)}; i++; #sql{INSERT INTO Book VALUES(:i, 'DDD', 'DDD Author', 5.999)}; i++; #sql{INSERT INTO Book VALUES(:i, 'EEE', 'EEE Author', 5.999)}; } #sql{ COMMIT RELEASE}; Oracle.close(); } catch(Exception e) { System.err.println("Error setting row to fixed value"); e.printStackTrace(); } } public static void main (String [] args) { if(args.length < 3) { System.out.println("This program should be run with a line like:\n"); System.out.print("java Create connect login password "); System.out.println("\n\nwhere\n"); System.out.println("connect - connect string for Oracle DB"); System.out.println("login - your login on oracle"); System.out.println("password - your password on oracle"); } else { execute(args[0],args[1],args[2]); } } String connectString; // string to connect to Oracle host:port:dbname String password; // password to connect to Oracle String login; // login to connect to Oracle } //Drop.sqlj import java.io.*; import java.sql.*; import sqlj.runtime.*; import sqlj.runtime.ref.*; import oracle.sqlj.runtime.*; /** This class implements the Drop transaction as decribed in the HW2 description using SQLJ. That is, it drops the Book table from the database being used. The execute() method is the method that actually does the job. */ public class Drop { /** Performs Drop transaction as described in HW2 @param connectString - string used to connect the oracle host:port:dbname @param login - login name on oracle @param password - password to log into oracle */ public static void execute(String connectString, String login, String password) { try { DriverManager.registerDriver( new oracle.jdbc.OracleDriver()); Oracle.connect(connectString, login, password, false); #sql{DROP TABLE Book}; #sql{ COMMIT RELEASE}; Oracle.close(); } catch(Exception e) { System.err.println("Error setting row to fixed value"); e.printStackTrace(); } } public static void main (String [] args) { if(args.length < 3) { System.out.println("This program should be run with a line like:\n"); System.out.print("java Create connect login password "); System.out.println("\n\nwhere\n"); System.out.println("connect - connect string for Oracle DB"); System.out.println("login - your login on oracle"); System.out.println("password - your password on oracle"); } else { execute(args[0],args[1],args[2]); } } String connectString; // string to connect to Oracle host:port:dbname String password; // password to connect to Oracle String login; // login to connect to Oracle } /******************************************************* * Project: CS 157b Homework #2 * File: maincreatedrop.h * Purpose: This header contains the global and global * embedded sql variables used by the * programs for proc on this homework. (create.pc, drop.pc) * It also has the function prototypes for main.pc * Start date: 10/6/03 * Programmer: Chris Pollett * *******************************************************/ EXEC SQL BEGIN DECLARE SECTION; varchar connectString[40]; /* SERVICE_NAME of Database (not host:port:dbname */ varchar login[20]; /* user's login on Oracle */ varchar password[20]; /* user's login on Oracle */ EXEC SQL END DECLARE SECTION; void initTransaction(char* connect, char* uname, char* pword); /* sets up the info for the dbconnection */ void execute(); /*function call to run either create or drop function/ /******************************************************* * Project: CS 157b Homework #2 * File: createanddrop.h * Purpose: create.pc and drop.pc include this header * Start date: 10/8/03 * Programmer: Chris Pollett * *******************************************************/ EXEC SQL BEGIN DECLARE SECTION; extern varchar connectString[40]; /* SID of Database (not host:port:dbname */ extern varchar login[20]; /* user's login on Oracle */ extern varchar password[20]; /* user's login on Oracle */ int i; EXEC SQL END DECLARE SECTION; /******************************************************* * Project: CS 157b Homework #2 * File: create.pc * Purpose: This is the source file to implement the proc version of * ersion of the create book and rows transaction as described in HW2 * Start date: 10/8/03 * Programmer: Chris Pollett * *******************************************************/ EXEC SQL INCLUDE "createanddrop.h"; #include<sqlca.h> /*-----------------------------------------------*/ void execute() /* PURPOSE: carries out the decrement transaction of HW4 RECEIVES: nothing RETURNS: nothing REMARKS: NONE */ { EXEC SQL CONNECT :login IDENTIFIED BY :password USING :connectString; EXEC SQL CREATE TABLE Book ( bookid INTEGER, title CHAR(80), author CHAR(80), price REAL ); for(i=1; i <= 111; i++) { EXEC SQL INSERT INTO Book(bookid, title, author, price) VALUES (:i, 'AAA', 'AAA Author', 5.999); i++; EXEC SQL INSERT INTO Book(bookid, title, author, price) VALUES (:i, "BBB", "BBB Author", 5.999); i++; EXEC SQL INSERT INTO Book(bookid, title, author, price) VALUES (:i, "CCC", "CCC Author", 5.999); i++; EXEC SQL INSERT INTO Book(bookid, title, author, price) VALUES (:i, "DDD", "DDD Author", 5.999); i++; EXEC SQL INSERT INTO Book(bookid, title, author, price) VALUES (:i, "EEE", "EEE Author", 5.999); } EXEC SQL COMMIT RELEASE; } /******************************************************* * Project: CS 157b Homework #2 * File: Drop.pc * Purpose: This is the source file to implement the proc version of * version of the drop book transaction as described in HW2 * Start date: 10/8/03 * Programmer: Chris Pollett * *******************************************************/ EXEC SQL INCLUDE "createanddrop.h"; #include<sqlca.h> /*-----------------------------------------------*/ void execute() /* PURPOSE: carries out the decrement transaction of HW4 RECEIVES: nothing RETURNS: nothing REMARKS: NONE */ { EXEC SQL CONNECT :login IDENTIFIED BY :password USING :connectString; EXEC SQL DROP TABLE Book; EXEC SQL COMMIT RELEASE; } ******************************************************* * Project: CS 157b Homework #2 * File: main.pc * Purpose: This is the main source file to drive the two proc files create.pc and drop.pc * Start date: 10/07/03 * Programmer: Chris Pollett * *******************************************************/ #include<stdio.h> #include<sqlca.h> EXEC SQL include "maincreatedrop.h"; /*-----------------------------------------------*/ void sql_error() /* PURPOSE: called whenever there is a Embedded SQL error to print the error message RECEIVES: nothing RETURNS: nothing REMARKS: NONE */ { char error[512]; long buflen; long msglen; EXEC SQL WHENEVER SQLERROR CONTINUE; buflen = sizeof(error); sqlglm(error,&buflen,&msglen); printf("Oracle Error:\t%25i\n", sqlca.sqlcode); printf("%.*s\n", msglen, error); EXEC SQL ROLLBACK RELEASE; exit(1); } /*-----------------------------------------------*/ void initTransaction(char* connect, char* lgn, char* pword) /* PURPOSE: sets up embedded sql global variabled used to connect to DB RECEIVES: connect -- SERVICE_NAME of oracle DB (not host:port:dbname) lgn -- login pword -- password RETURNS: nothing REMARKS: NONE */ { strcpy(connectString.arr, connect); connectString.len = strlen(connectString.arr); strcpy(login.arr, lgn); login.len = strlen(login.arr); strcpy(password.arr, pword); password.len = strlen(password.arr); } /*-----------------------------------------------*/ void main(int argc, char *argv[]) /* PURPOSE: main driver function to perform tests of a transaction RECEIVES: command line arguments saying how to connect and number of trials RETURNS: nothing REMARKS: NONE */ { if(argc < 3) { printf("This program should be run with a line like:\n\n"); printf("[TransactionTest] connect login password "); printf("trials\n\nwhere\n\n"); printf("[TransactionTest] - name of this program\n"); printf("service_name - service name for Oracle DB\n"); printf("login - your login on oracle\n"); printf("password - your password on oracle\n"); } else { initTransaction(argv[1],argv[2],argv[3]); execute(); } } <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"> <% /* This JSP solves Exercise 7.6 for HW2 of CS157b Fall 2003. It has a start up screen requesting information to connect to a DBMS. When this is entered and submitted, the page returned is a current batch of query results from a Book table (note in the R& G they use Books table). At the bottom of the page of query results. Previous and next links allow the user to navigate forwards and backwards through the query results. @author Chris Pollett @version 2003/10/8 */ %> <%@ page import="java.sql.*, java.net.*, javax.servlet.jsp.*" %> <%! String URL=""; // will contain the URL of this servlet String connect = ""; // string to connect to DBMS String login = ""; // DBMS login String password = ""; // DBMS password int numberDisplayed = 20; //number of results to display at one time String lowTitle = ""; // current start title for query results will print String lowBookID = ""; // current start id of query results will print String highTitle = ""; // last title in query results will print String highBookID = ""; // last id in query results will print int set = 0; String[] bookids; String[] titles; /** This method is called in the head of the HTML part of the JSP to initialize some global variable. In particular, it extracts the parameters from the query string. These parameters include Database connection information as well as a list of book titles and ids that correspond to the first lines output of previous pages of query results. @param request - ServletRequest fo this JSP should be passed */ void initializeConnection(HttpServletRequest request) { connect = request.getParameter("connect"); login = request.getParameter("login"); password = request.getParameter("password"); URL = request.getServerName()+":"+request.getServerPort() + request.getRequestURI(); String setString = request.getParameter("set"); if(setString == null) { set = 0; lowBookID =""; lowTitle =""; bookids = new String[1]; titles = new String[1]; } else { set = Integer.parseInt(setString); bookids = new String[set+1]; titles = new String[set+1]; for(int i=0; i <= set; i++) { bookids[i] = request.getParameter("b"+i); titles[i] = request.getParameter("t"+i); } lowBookID = bookids[set]; lowTitle = titles[set]; } } /** This function computes the next batch of query results from the database and writes them back to the client. It also computes highBookID and highTitle global variables which are needed in calculating the next link at the bottom of the page. @param out - used to write out our response to the client @throws IOException - if something bad happens while writing to client @throws SQLException - if have problems with our connection to the database */ void computeQueryResults(JspWriter out) throws IOException, SQLException { DriverManager.registerDriver( new oracle.jdbc.OracleDriver()); Connection conn = DriverManager.getConnection(connect, login, password); PreparedStatement pstmt; if( set != 0) { pstmt = conn.prepareStatement("SELECT title, bookid FROM Book WHERE"+ " (title = ? AND bookid = ?) OR (title > ?)" + "ORDER BY title, bookid"); pstmt.setString(1, lowTitle); pstmt.setString(2, lowBookID); pstmt.setString(3, lowTitle); } else { pstmt = conn.prepareStatement("SELECT title, bookid FROM Book"+ " ORDER BY title, bookid"); } ResultSet result = pstmt.executeQuery(); out.println("<p>"); int i = 0; boolean more; while( (more=result.next()) && i < numberDisplayed) { highTitle = result.getString(1); highBookID = result.getString(2); if(i == 0 && set == 0) { titles[0] = highTitle; bookids[0] = highBookID; } out.println(" Title: "+highTitle+ "    BookID: "+highBookID+"<br />"); i++; } out.println("</p>"); if(!more) { highTitle = null; highBookID = null; } pstmt.close(); conn.close(); } /** This function computes the query string needed for the next and previous links at the bottom of the web page. It also outputs these links. @param out - used to write out our response to the client @throws IOException - if something bad happens while writing to client */ void computeOutputLinks(JspWriter out) throws IOException { if(set > 0) { out.print("[<a href=\"http://"+URL+ "?" ); out.print("connect="+URLEncoder.encode(connect,"UTF-8")+"&"); out.print("login="+URLEncoder.encode(login,"UTF-8")+"&"); out.print("password="+URLEncoder.encode(password,"UTF-8")+"&"); out.print("set=" + (set-1)+"&"); for(int i = 0; i < set; i++) { out.print("t"+i+"=" + URLEncoder.encode(titles[i],"UTF-8")+"&"); out.print("b"+i+"=" + URLEncoder.encode(bookids[i],"UTF-8")+"&"); } out.println("\" >Previous</a>] "); } if(highTitle != null) { out.print("[<a href=\"http://"+URL+"?"); out.print("connect="+URLEncoder.encode(connect,"UTF-8")+"&"); out.print("login="+URLEncoder.encode(login,"UTF-8")+"&"); out.print("password="+URLEncoder.encode(password,"UTF-8")+"&"); out.print("set=" + (set+1)+"&"); for(int i = 0; i <= set; i++) { out.print("t"+i+"=" + URLEncoder.encode(titles[i],"UTF-8")+"&"); out.print("b"+i+"=" + URLEncoder.encode(bookids[i],"UTF-8")+"&"); } out.print("t"+(set+1)+"=" + URLEncoder.encode(highTitle,"UTF-8")+"&"); out.print("b"+(set+1)+"=" + URLEncoder.encode(highBookID,"UTF-8")+"&"); out.println("\" >Next</a>] "); } } %> <head> <title>Exercise7.6 <% initializeConnection(request); // set up global variables based on query if(connect == null) // display correct title depending on whether we just came to site {%> Intro Page <%} else {%> Query Results <%} %> </title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <meta name="ROBOTS" content="NOINDEX, NOFOLLOW" /> <meta name="Authors" content="Christopher Pollett" /> <style type="text/css"> <!-- .center {text-align:center} .right {text-align:right} .left {text-align:left} --> </style> </head> <body> <div class="center"> <h1>Welcome to the Hw2 Exercise 7.6 Book Query Page</h1> <% /* In the body of the JSP we display one of two pages: Either the intro screen or a query result screen. Next if statement decides which */ if(connect == null) {%> <h2>Please fill out the form below or use the default values to connect to the Book database and see the query results </h2> <form method="get" action="http://<% out.print(URL); %>" > <table summary="table used for formatting this form"> <tr> <td class="right"> <label for="connect"> Database Connect String: </label> </td> <td class="left"> <input type="text" name="connect" size="20" maxlength="40" id="connect" value="jdbc:oracle:thin:@sigma.mathcs.sjsu.edu:1521:CS157b"/> </td> </tr> <tr> <td class="right"> <label for="login"> Login: </label> </td> <td class="left"> <input type="text" name="login" size="20" maxlength="40" id="login" value="login"/> </td> </tr> <tr> <td class="right"> <label for="password"> Password: </label> </td> <td class="left"> <input type="text" name="password" size="20" maxlength="40" id="password" value="password"/> </td> </tr> </table> <p> <input type="submit" /> </p> </form> <%} else {%> <h2>Query Results:</h2> <% computeQueryResults(out); computeOutputLinks(out); } %> </div> </body> </html> Problem 8.11. We are asked for each of the following queries which index to choose to speed up the query. Then we are asked if index only plans are not considered which choice to make. Query 1: Print ename, age and sal for all employees. Answer (with index only plans considered): (b) Unclustered Hash Index on <ename, age, and sal>. This is because we can do an index only scan (unlike (d) and (e)). Since the data is unclustered file size will be smaller than (a) and (c). Answer (without index only plans): (e) No index. Since we'll have to read the whole file anyway any index can only slow us down. Query 2: Find the depts ids of departments that are on the 10th floor and have a budget of less than $15000. Answer (with index only plans considered): (c) Since its a range query we want to use a B+-tree index rather than hash index or no index eliminating (a), (b) and (e). Option (c) allows us to do an index-only evaluation. (d) does not. Answer (without index only plans): (c) This option still beats no index since can easily find start of range. |