|
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. |