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 rh...@apache.org on 2006/11/02 15:26:12 UTC
svn commit: r470372 [1/2] - in /db/derby/code/trunk/java/demo/scores: ./
java/ java/client/ java/client/org/ java/client/org/apache/
java/client/org/apache/derbyDemo/ java/client/org/apache/derbyDemo/scores/
java/client/org/apache/derbyDemo/scores/app/...
Author: rhillegas
Date: Thu Nov 2 06:26:09 2006
New Revision: 470372
URL: http://svn.apache.org/viewvc?view=rev&rev=470372
Log:
DERBY-1993: Checkin derby-1993-v03.diff, which puts the demo classes in the org.apache.derbyDemo namespace.
Added:
db/derby/code/trunk/java/demo/scores/
db/derby/code/trunk/java/demo/scores/README (with props)
db/derby/code/trunk/java/demo/scores/build.xml (with props)
db/derby/code/trunk/java/demo/scores/customizeMe.properties (with props)
db/derby/code/trunk/java/demo/scores/java/
db/derby/code/trunk/java/demo/scores/java/client/
db/derby/code/trunk/java/demo/scores/java/client/org/
db/derby/code/trunk/java/demo/scores/java/client/org/apache/
db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/
db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/
db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/app/
db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/app/Scores.java (with props)
db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/
db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Data.java (with props)
db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Database.java (with props)
db/derby/code/trunk/java/demo/scores/java/common/
db/derby/code/trunk/java/demo/scores/java/common/org/
db/derby/code/trunk/java/demo/scores/java/common/org/apache/
db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/
db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/
db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/
db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Logger.java (with props)
db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Utils.java (with props)
db/derby/code/trunk/java/demo/scores/java/server/
db/derby/code/trunk/java/demo/scores/java/server/org/
db/derby/code/trunk/java/demo/scores/java/server/org/apache/
db/derby/code/trunk/java/demo/scores/java/server/org/apache/derbyDemo/
db/derby/code/trunk/java/demo/scores/java/server/org/apache/derbyDemo/scores/
db/derby/code/trunk/java/demo/scores/java/server/org/apache/derbyDemo/scores/proc/
db/derby/code/trunk/java/demo/scores/java/server/org/apache/derbyDemo/scores/proc/Functions.java (with props)
db/derby/code/trunk/java/demo/scores/java/server/org/apache/derbyDemo/scores/proc/Procedures.java (with props)
Added: db/derby/code/trunk/java/demo/scores/README
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/scores/README?view=auto&rev=470372
==============================================================================
--- db/derby/code/trunk/java/demo/scores/README (added)
+++ db/derby/code/trunk/java/demo/scores/README Thu Nov 2 06:26:09 2006
@@ -0,0 +1,36 @@
+This directory tree contains a demo application which shows how you
+can run user-written Java code inside the Derby database.
+
+For instructions on how to build and run the demo application, cd to this
+directory and type the following command:
+
+ ant -projecthelp
+
+The demo shows you how to do the following:
+
+o Declare Java functions and procedures and run them inside the Derby
+ engine.
+
+o Load the Derby engine with jar files of user-written code.
+
+o Enforce data consistency by wiring functions into CREATE TABLE
+ statements.
+
+o Run a Java procedure from a TRIGGER.
+
+o Improve query performance by wiring filtering functions into the
+ WHERE clause.
+
+o Format query output by wiring functions into the SELECT list.
+
+o Re-use off-the-shelf packages to write your own custom data aggregator.
+
+For more detail, please consult the following sections of the Derby
+Developer's Guide:
+
+o "Loading classes from a database"
+
+o "Derby server-side programming"
+
+
+
Propchange: db/derby/code/trunk/java/demo/scores/README
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/demo/scores/README
------------------------------------------------------------------------------
svn:executable = *
Added: db/derby/code/trunk/java/demo/scores/build.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/scores/build.xml?view=auto&rev=470372
==============================================================================
--- db/derby/code/trunk/java/demo/scores/build.xml (added)
+++ db/derby/code/trunk/java/demo/scores/build.xml Thu Nov 2 06:26:09 2006
@@ -0,0 +1,208 @@
+<?xml version="1.0"?>
+<!--
+ 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.
+-->
+
+<project default="compile" basedir=".">
+ <description>
+ Driver for building and running the Scores demo. To invoke these
+ ant targets, you should customize the properties defined in
+ customizeMe.properties.
+
+ </description>
+
+<!--
+===================================================================
+==
+== Declare variables.
+==
+===================================================================
+-->
+
+ <property file="customizeMe.properties"/>
+
+ <property name="scores.client.source" value="${basedir}/java/client"/>
+ <property name="scores.common.source" value="${basedir}/java/common"/>
+ <property name="scores.server.source" value="${basedir}/java/server"/>
+
+ <property name="scores.base.classes" value="${basedir}/classes"/>
+ <property name="scores.client.classes" value="${scores.base.classes}/client"/>
+ <property name="scores.server.classes" value="${scores.base.classes}/server"/>
+
+ <property name="scores.jars" value="${basedir}/jars"/>
+ <property name="scores.client.jar" value="${scores.jars}/scores-client.jar"/>
+ <property name="scores.server.jar" value="${scores.jars}/scores-server.jar"/>
+
+ <property name="scores.database" value="${basedir}/database"/>
+
+ <path id="scores.client.compile.classpath">
+ <pathelement location="${scores.client.classes}"/>
+ <pathelement path="${derby.lib}/derbytools.jar"/>
+ </path>
+
+ <path id="scores.server.compile.classpath">
+ <pathelement location="${scores.server.classes}"/>
+ <pathelement path="${math.library}"/>
+ </path>
+
+ <path id="scores.runtime.classpath">
+ <pathelement path="${scores.client.jar}"/>
+ <pathelement path="${derby.lib}/derby.jar"/>
+ <pathelement path="${derby.lib}/derbytools.jar"/>
+ </path>
+
+<!--
+===================================================================
+==
+== Setup and teardown targets.
+==
+===================================================================
+-->
+
+ <target
+ name="clean"
+ description="Remove all build artifacts. Does not remove the database."
+ >
+
+ <delete dir="${scores.base.classes}"/>
+ <delete dir="${scores.jars}"/>
+
+ </target>
+
+ <target
+ name="clobber"
+ depends="clean"
+ description="Remove the database and all build artifacts."
+ >
+
+ <delete dir="${scores.database}"/>
+
+ </target>
+
+ <!-- Basic setup. -->
+ <target
+ name="-init"
+ >
+
+ <mkdir dir="${scores.base.classes}"/>
+ <mkdir dir="${scores.client.classes}"/>
+ <mkdir dir="${scores.server.classes}"/>
+ <mkdir dir="${scores.jars}"/>
+ <mkdir dir="${scores.database}"/>
+
+ </target>
+
+<!--
+===================================================================
+==
+== Build targets.
+==
+===================================================================
+-->
+
+ <target
+ name="compile"
+ depends="-compile-client, -compile-server"
+ description="Compile all classes."
+ />
+
+ <!-- Compile client classes. -->
+ <target
+ name="-compile-client"
+ depends="-init"
+ >
+
+ <javac
+ srcdir="${scores.common.source}"
+ destdir="${scores.client.classes}"
+ >
+ <classpath refid="scores.client.compile.classpath"/>
+ </javac>
+
+ <javac
+ srcdir="${scores.client.source}"
+ destdir="${scores.client.classes}"
+ >
+ <classpath refid="scores.client.compile.classpath"/>
+ </javac>
+
+ </target>
+
+ <!-- Compile server classes. -->
+ <target
+ name="-compile-server"
+ depends="-init"
+ >
+
+ <javac
+ srcdir="${scores.common.source}"
+ destdir="${scores.server.classes}"
+ >
+ <classpath refid="scores.server.compile.classpath"/>
+ </javac>
+
+ <javac
+ srcdir="${scores.server.source}"
+ destdir="${scores.server.classes}"
+ >
+ <classpath refid="scores.server.compile.classpath"/>
+ </javac>
+
+ </target>
+
+ <target
+ name="buildjars"
+ depends="compile"
+ description="Build jar files."
+ >
+
+ <jar destfile="${scores.client.jar}"
+ basedir="${scores.client.classes}"
+ />
+
+ <jar destfile="${scores.server.jar}"
+ basedir="${scores.server.classes}"
+ />
+
+ </target>
+
+<!--
+===================================================================
+==
+== Execution targets.
+==
+===================================================================
+-->
+
+ <target
+ name="run"
+ depends="buildjars"
+ description="Run the Scores application."
+ >
+
+ <java
+ classname="org.apache.derbyDemo.scores.app.Scores"
+ >
+ <classpath refid="scores.runtime.classpath"/>
+ <sysproperty key="derby.system.home" value="${scores.database}"/>
+
+ <arg value="${scores.server.jar}"/>
+ <arg value="${math.library}"/>
+ </java>
+
+ </target>
+
+</project>
Propchange: db/derby/code/trunk/java/demo/scores/build.xml
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/demo/scores/build.xml
------------------------------------------------------------------------------
svn:executable = *
Added: db/derby/code/trunk/java/demo/scores/customizeMe.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/scores/customizeMe.properties?view=auto&rev=470372
==============================================================================
--- db/derby/code/trunk/java/demo/scores/customizeMe.properties (added)
+++ db/derby/code/trunk/java/demo/scores/customizeMe.properties Thu Nov 2 06:26:09 2006
@@ -0,0 +1,30 @@
+#
+# 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.
+#
+
+#
+# Location of the derby jar files. You need version 10.2 or
+# higher of Derby.
+#
+derby.lib=../../../jars/insane
+
+#
+# Location of the Jakarta commons math library
+#
+math.library=../../../../../../sw/commonsMath/commons-math-1.1/commons-math-1.1.jar
+
+
+
Propchange: db/derby/code/trunk/java/demo/scores/customizeMe.properties
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/demo/scores/customizeMe.properties
------------------------------------------------------------------------------
svn:executable = *
Added: db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/app/Scores.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/app/Scores.java?view=auto&rev=470372
==============================================================================
--- db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/app/Scores.java (added)
+++ db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/app/Scores.java Thu Nov 2 06:26:09 2006
@@ -0,0 +1,177 @@
+/*
+
+ Derby - Class org.apache.derbyDemo.scores.app.Scores
+
+ 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.
+
+ */
+
+package org.apache.derbyDemo.scores.app;
+
+import org.apache.derbyDemo.scores.data.*;
+import org.apache.derbyDemo.scores.util.*;
+
+import java.sql.*;
+
+/**
+ * <p>
+ * Application for showcasing Derby features using
+ * an educational testing schema.
+ * </p>
+ *
+ * @author Rick Hillegas
+ */
+public class Scores
+{
+ ////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ////////////////////////////////////////////////////////
+
+ ////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ////////////////////////////////////////////////////////
+
+ private Logger _logger;
+ private String _serverJar;
+ private String _mathJar;
+
+ ////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTOR
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Construct out of thin air.
+ * </p>
+ */
+ public Scores()
+ {
+ _logger = new Logger();
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // ENTRY POINT
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Run the Scores application, showcasing Derby features.
+ * </p>
+ *
+ * <ul>
+ * <li>args[ 0 ] = name of server jar</li>
+ * <li>args[ 1 ] = name of math library</li>
+ * </ul>
+ */
+ public static void main( String[] args )
+ {
+ Scores application = new Scores();
+ int argIdx = 0;
+
+ try {
+ application._serverJar = args[ argIdx++ ];
+ application._mathJar = args[ argIdx++ ];
+
+ application.execute();
+ }
+ catch (Throwable t)
+ {
+ Logger.getLogger().log( t );
+ }
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // APPLICATION BEHAVIOR
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Run the Scores application, showcasing Derby features.
+ * </p>
+ */
+ private void execute()
+ throws Exception
+ {
+ Logger log = Logger.getLogger();
+
+ log.logBanner( "Starting Scores Application..." );
+
+ Database db = Database.getDatabase( _serverJar, _mathJar );
+
+ Connection conn = db.getConnection();
+ Data data = db.getData();
+
+ log.logBanner
+ (
+ "Now the students take their tests." +
+ " Watch for the trigger firing..."
+ );
+ data.takeTests( db );
+ Utils.commit( conn );
+
+ log.logBanner( "Show scores for latest takings..." );
+ Database.prettyPrint
+ (
+ db.getConnection(),
+ "select s.lastName, s.firstName, t.testName,\n" +
+ "tk.takingID, formatPercent( tk.score ) score\n" +
+ "from Student s, Test t, TestTaking tk, LastTaking lt\n" +
+ "where t.testID = tk.testID\n" +
+ "and s.studentID = tk.studentID\n" +
+ "and tk.takingID = lt.takingID\n" +
+ "order by s.lastName, s.firstName, t.testName, tk.takingID\n"
+ );
+
+ log.logBanner
+ ( "Median Score Per Test. Note how we fake " +
+ "a user-defined aggregate..." );
+ db.prettyPrint
+ (
+ conn,
+ "select testName, " +
+ "formatPercent( getMedianTestScore( testID ) ) " +
+ "as \"median score\"\n" +
+ "from Test\n"
+ );
+
+ log.logBanner
+ (
+ "Who Needs Improvement? Note the filtering done " +
+ "at the end of the WHERE clause..."
+ );
+ data.reportWhoNeedsImprovement
+ ( db, Data.LincolnGrammar, "GSM_2_0" );
+
+ log.logBanner( "Hoopla! Everything works!" );
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // MINIONS
+ //
+ ////////////////////////////////////////////////////////
+
+}
Propchange: db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/app/Scores.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/app/Scores.java
------------------------------------------------------------------------------
svn:executable = *
Added: db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Data.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Data.java?view=auto&rev=470372
==============================================================================
--- db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Data.java (added)
+++ db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Data.java Thu Nov 2 06:26:09 2006
@@ -0,0 +1,964 @@
+/*
+
+ Derby - Class org.apache.derbyDemo.scores.data.Data
+
+ 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.
+
+ */
+
+package org.apache.derbyDemo.scores.data;
+
+import java.sql.*;
+
+import org.apache.derbyDemo.scores.util.*;
+
+/**
+ * <p>
+ * Data used by Scores application
+ * </p>
+ *
+ * @author Rick Hillegas
+ */
+public class Data
+{
+ ////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ////////////////////////////////////////////////////////
+
+ private static final int QUESTIONS_PER_TEMPLATE = 10;
+ private static final double IMPROVEMENT_PER_TAKING = 2.0;
+ private static final double GRAMMAR_SCHOOL_PENALTY = 20.0;
+ private static final double PER_STUDENT_FLUCTUATION = 3.0;
+ private static final double FLUCTATION_MULTIPLIER = 2.0;
+
+ public static final School LincolnGrammar =
+ new School( "Lincoln Grammar", true );
+ public static final School WashingtonHS =
+ new School( "Washington HS", false );
+
+ public static final Student AliceToklas = new Student
+ ( "Alice", "Toklas", "1998-03-21", LincolnGrammar );
+ public static final Student GertrudeStein = new Student
+ ( "Gertrude", "Stein", "1999-07-15", LincolnGrammar );
+ public static final Student HenryJames = new Student
+ ( "Henry", "James", "2000-09-03", LincolnGrammar );
+ public static final Student GoreVidal = new Student
+ ( "Gore", "Vidal", "2008-12-25", LincolnGrammar );
+
+ public static final Student TrumanCapote = new Student
+ ( "Truman", "Capote", "1992-08-05", WashingtonHS );
+ public static final Student WaltWhitman = new Student
+ ( "Walt", "Whitman", "1991-12-28", WashingtonHS );
+ public static final Student DorothyParker = new Student
+ ( "Dorothy", "Parker", "1990-01-09", WashingtonHS );
+ public static final Student AndrewSullivan = new Student
+ ( "Andrew", "Sullivan", "1989-02-20", WashingtonHS );
+
+ public static final Test GradeSchoolMath =
+ new Test( "Grade School Math", true, 90.0 );
+ public static final Test GradeSchoolEnglish =
+ new Test( "Grade School English", true, 85.0 );
+ public static final Test HighSchoolMath =
+ new Test( "High School Math", false, 80.0 );
+ public static final Test HighSchoolEnglish =
+ new Test( "High School English", false, 75.0 );
+
+ public static final Question GSM_1 =
+ new Question( "GSM_1", 1, 5, 2, GradeSchoolMath );
+ public static final Question GSM_2 =
+ new Question( "GSM_2", 2, 3, 1, GradeSchoolMath );
+ public static final Question GSM_3 =
+ new Question( "GSM_3", 3, 4, 3, GradeSchoolMath );
+
+ public static final Question GSE_1 =
+ new Question( "GSE_1", 3, 5, 2, GradeSchoolEnglish );
+ public static final Question GSE_2 =
+ new Question( "GSE_2", 2, 3, 1, GradeSchoolEnglish );
+ public static final Question GSE_3 =
+ new Question( "GSE_3", 1, 4, 3, GradeSchoolEnglish );
+
+ public static final Question HSM_1 =
+ new Question( "HSM_1", 1, 5, 2, HighSchoolMath );
+ public static final Question HSM_2 =
+ new Question( "HSM_2", 2, 3, 1, HighSchoolMath );
+ public static final Question HSM_3 =
+ new Question( "HSM_3", 3, 4, 3, HighSchoolMath );
+
+ public static final Question HSE_1 =
+ new Question( "HSE_1", 3, 5, 2, HighSchoolEnglish );
+ public static final Question HSE_2 =
+ new Question( "HSE_2", 2, 3, 1, HighSchoolEnglish );
+ public static final Question HSE_3 =
+ new Question( "HSE_3", 1, 4, 3, HighSchoolEnglish );
+
+ public static final Question[] QUESTIONS =
+ {
+ GSM_1,
+ GSM_2,
+ GSM_3,
+
+ GSE_1,
+ GSE_2,
+ GSE_3,
+
+ HSM_1,
+ HSM_2,
+ HSM_3,
+
+ HSE_1,
+ HSE_2,
+ HSE_3,
+ };
+
+ public static final School[] SCHOOLS =
+ {
+ LincolnGrammar,
+ WashingtonHS,
+ };
+
+ public static final Student[] STUDENTS =
+ {
+ AliceToklas,
+ GertrudeStein,
+ TrumanCapote,
+ WaltWhitman,
+ HenryJames,
+ GoreVidal,
+ DorothyParker,
+ AndrewSullivan,
+ };
+
+ public static final Test[] TESTS =
+ {
+ GradeSchoolMath,
+ GradeSchoolEnglish,
+ HighSchoolMath,
+ HighSchoolEnglish,
+ };
+
+ ////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ////////////////////////////////////////////////////////
+
+ ////////////////////////////////////////////////////////
+ //
+ // INNER CLASSES
+ //
+ ////////////////////////////////////////////////////////
+
+ public abstract static class KeyedObject
+ {
+ private Integer _key;
+
+ public void create( Database database )
+ throws SQLException
+ {
+ Logger log = Logger.getLogger();
+ boolean loggingEnabled = log.isLoggingEnabled();
+
+ // don't print out the chatter from creating these rows.
+ try {
+ log.enableLogging( false );
+
+ createMinion( database );
+ }
+ finally
+ {
+ log.enableLogging( loggingEnabled );
+ }
+ }
+
+ protected abstract void createMinion( Database database )
+ throws SQLException;
+
+ protected abstract PreparedStatement getKeyFinder
+ ( Database database )
+ throws SQLException;
+
+ public int getPrimaryKey( Database database )
+ throws SQLException
+ {
+ if ( _key == null )
+ {
+ PreparedStatement ps = getKeyFinder( database );
+
+ setPrimaryKey( Utils.getScalarValue( ps ) );
+ }
+
+ return _key.intValue();
+ }
+
+ private void setPrimaryKey( int key )
+ {
+ _key = new Integer( key );
+ }
+
+ public SQLException notImplemented()
+ { return new SQLException( "Not implemented." ); }
+ }
+
+
+ public static class School extends KeyedObject
+ {
+ private String _schoolName;
+ private boolean _isGrammarSchool;
+
+ public School( String schoolName, boolean isGrammarSchool )
+ {
+ _schoolName = schoolName;
+ _isGrammarSchool = isGrammarSchool;
+ }
+
+ public String getSchoolName() { return _schoolName; }
+ public boolean isGrammarSchool() { return _isGrammarSchool; }
+
+ protected void createMinion( Database database )
+ throws SQLException
+ {
+ Connection conn = database.getConnection();
+
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "insert into School( schoolName ) values ( ? )"
+ );
+
+ ps.setString( 1, _schoolName );
+
+ ps.execute();
+ Utils.close( ps );
+ }
+
+ protected PreparedStatement getKeyFinder( Database database )
+ throws SQLException
+ {
+ Connection conn = database.getConnection();
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "select schoolID from School where schoolName = ?"
+ );
+
+ ps.setString( 1, _schoolName );
+
+ return ps;
+ }
+
+ }
+
+ public static class Student extends KeyedObject
+ {
+ private String _firstName;
+ private String _lastName;
+ private String _birthday;
+ private School _school;
+
+ public Student
+ (
+ String firstName,
+ String lastName,
+ String birthday,
+ School school
+ )
+ {
+ _firstName = firstName;
+ _lastName = lastName;
+ _birthday = birthday;
+ _school = school;
+ }
+
+ public String getFirstName() { return _firstName; }
+ public String getLastName() { return _lastName; }
+ public String getBirthday() { return _birthday; }
+ public School getSchool() { return _school; }
+
+ protected void createMinion( Database database )
+ throws SQLException
+ {
+ int param = 1;
+ Connection conn = database.getConnection();
+ java.sql.Date sqlDate =
+ java.sql.Date.valueOf( _birthday );
+
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "insert into Student\n" +
+ "( schoolID, lastName, firstName, birthday )\n" +
+ "values( ?, ?, ?, ? )"
+ );
+
+ ps.setInt( param++, _school.getPrimaryKey( database ) );
+ ps.setString( param++, _lastName );
+ ps.setString( param++, _firstName );
+ ps.setDate( param++, sqlDate );
+
+ ps.execute();
+ Utils.close( ps );
+ }
+
+ protected PreparedStatement getKeyFinder( Database database )
+ throws SQLException
+ {
+ Connection conn = database.getConnection();
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "select studentID from Student\n" +
+ "where lastName = ? and firstName = ?"
+ );
+
+ ps.setString( 1, _lastName );
+ ps.setString( 2, _firstName );
+
+ return ps;
+ }
+
+ }
+
+ public static class Test extends KeyedObject
+ {
+ private String _testName;
+ private boolean _grammarSchoolTest;
+ private double _highSchoolScore;
+
+ public Test
+ (
+ String testName,
+ boolean grammarSchoolTest,
+ double highSchoolScore
+ )
+ {
+ _testName = testName;
+ _grammarSchoolTest = grammarSchoolTest;
+ _highSchoolScore = highSchoolScore;
+ }
+
+ public String getTestName() { return _testName; }
+
+ public boolean isGrammarSchoolTest()
+ { return _grammarSchoolTest; }
+
+ public double getHighSchoolScore()
+ { return _highSchoolScore; }
+
+ protected void createMinion( Database database )
+ throws SQLException
+ {
+ Connection conn = database.getConnection();
+
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "insert into Test( testName ) values ( ? )"
+ );
+
+ ps.setString( 1, _testName );
+
+ ps.execute();
+ Utils.close( ps );
+ }
+
+ protected PreparedStatement getKeyFinder( Database database )
+ throws SQLException
+ {
+ Connection conn = database.getConnection();
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "select testID from Test where testName = ?"
+ );
+
+ ps.setString( 1, _testName );
+
+ return ps;
+ }
+ }
+
+ public static class TestTaking extends KeyedObject
+ {
+ private Student _student;
+ private Test _test;
+
+ public TestTaking( Student student, Test test )
+ {
+ _student = student;
+ _test = test;
+ }
+
+ public Student getStudent() { return _student; }
+ public Test getTest() { return _test; }
+
+ protected void createMinion( Database database )
+ throws SQLException
+ {
+ Connection conn = database.getConnection();
+ int param = 1;
+
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "insert into TestTaking\n" +
+ "( studentID, testID, score ) values ( ?, ?, ? )"
+ );
+
+ ps.setInt( param++, _student.getPrimaryKey( database ) );
+ ps.setInt( param++, _test.getPrimaryKey( database ) );
+ ps.setInt( param++, -1 );
+
+ ps.execute();
+ Utils.close( ps );
+ }
+
+ protected PreparedStatement getKeyFinder( Database database )
+ throws SQLException
+ {
+ Connection conn = database.getConnection();
+ int param = 1;
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "select max( takingID ) from TestTaking\n" +
+ "where studentID = ? and testID = ?"
+ );
+
+ ps.setInt( param++, _student.getPrimaryKey( database ) );
+ ps.setInt( param++, _test.getPrimaryKey( database ) );
+
+ return ps;
+ }
+ }
+
+ public static class Question extends KeyedObject
+ {
+ private String _questionName;
+ private int _difficulty;
+ private int _numberOfChoices;
+ private int _correctChoice;
+ private Test _test;
+
+ public Question
+ (
+ String questionName,
+ int difficulty,
+ int numberOfChoices,
+ int correctChoice,
+ Test test
+ )
+ {
+ _questionName = questionName;
+ _difficulty = difficulty;
+ _numberOfChoices = numberOfChoices;
+ _correctChoice = correctChoice;
+ _test = test;
+ }
+
+ public String getQuestionName() { return _questionName; }
+
+ public int getDifficulty() { return _difficulty; }
+
+ public int getNumberOfChoices()
+ { return _numberOfChoices; }
+
+ public int getCorrectChoice() { return _correctChoice; }
+ public Test getTest() { return _test; }
+
+ protected void createMinion( Database database )
+ throws SQLException
+ {
+ Connection conn = database.getConnection();
+ int param = 1;
+
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "insert into Question\n" +
+ "( testID, questionName, difficulty, " +
+ "numberOfChoices, correctChoice )\n" +
+ "values ( ?, ?, ?, ?, ? )"
+ );
+
+ ps.setInt( param++, _test.getPrimaryKey( database ) );
+ ps.setString( param++, _questionName );
+ ps.setInt( param++, _difficulty );
+ ps.setInt( param++, _numberOfChoices );
+ ps.setInt( param++, _correctChoice );
+
+ ps.execute();
+ Utils.close( ps );
+ }
+
+ protected PreparedStatement getKeyFinder( Database database )
+ throws SQLException
+ {
+ Connection conn = database.getConnection();
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "select questionID from Question where questionName = ?"
+ );
+
+ ps.setString( 1, _questionName );
+
+ return ps;
+ }
+
+ }
+
+ public static class QuestionTaking extends KeyedObject
+ {
+ private TestTaking _testTaking;
+ private Question _question;
+ private int _actualChoice;
+
+ public QuestionTaking
+ (
+ TestTaking testTaking,
+ Question question,
+ int actualChoice
+ )
+ {
+ _testTaking = testTaking;
+ _question = question;
+ _actualChoice = actualChoice;
+ }
+
+ public TestTaking getTestTaking() { return _testTaking; }
+ public Question getQuestion() { return _question; }
+ public int getActualChoice() { return _actualChoice; }
+
+ protected void createMinion( Database database )
+ throws SQLException
+ {
+ Connection conn = database.getConnection();
+ int param = 1;
+
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "insert into QuestionTaking\n" +
+ "( questionID, takingID, actualChoice ) values ( ?, ?, ? )"
+ );
+
+ ps.setInt( param++, _question.getPrimaryKey( database ) );
+ ps.setInt( param++, _testTaking.getPrimaryKey( database ) );
+ ps.setInt( param++, _actualChoice );
+
+ ps.execute();
+ Utils.close( ps );
+ }
+
+ protected PreparedStatement getKeyFinder( Database database )
+ throws SQLException
+ {
+ // no primary key for this table!
+ throw notImplemented();
+ }
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTOR
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Create an object to populate the tables for the Scores application.
+ * </p>
+ */
+ public Data()
+ {
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // PUBLIC BEHAVIOR
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Initialize the Scores application with data.
+ * </p>
+ */
+ public void initialize( Database database )
+ throws SQLException
+ {
+ initialize( database, SCHOOLS );
+ initialize( database, STUDENTS );
+ initialize( database, TESTS );
+ initializeQuestions( database );
+ }
+
+ /**
+ * <p>
+ * Make the students take their tests.
+ * </p>
+ */
+ public void takeTests( Database database )
+ throws SQLException
+ {
+ int testCount = TESTS.length;
+ int takings = 0;
+
+ for ( int i = 0; i < testCount; i++ )
+ {
+ takings = takeTest( database, TESTS[ i ], takings );
+ }
+ }
+
+ /**
+ * <p>
+ * Make all eligible students take the test.
+ * Grammar school tests are taken
+ * by all students. High school tests are only taken by high school
+ * students. Returns the number of test takings so far (this is used
+ * to throttle diagnostic noise.
+ * </p>
+ */
+ public int takeTest( Database database, Test test, int takings )
+ throws SQLException
+ {
+ Logger log = Logger.getLogger();
+ boolean loggingEnabled = log.isLoggingEnabled();
+ int studentCount = STUDENTS.length;
+ double highSchoolScore = test.getHighSchoolScore();
+ double grammarSchoolScore =
+ highSchoolScore - GRAMMAR_SCHOOL_PENALTY;
+ double fluctuation;
+ double targetScore;
+
+ for ( int i = 0; i < studentCount; i++ )
+ {
+ Student student = STUDENTS[ i ];
+ boolean grammarSchoolStudent =
+ student.getSchool().isGrammarSchool();
+
+ if ( grammarSchoolStudent && (!test.isGrammarSchoolTest()) )
+ { continue; }
+
+ if ( grammarSchoolStudent )
+ { targetScore = grammarSchoolScore; }
+ else
+ { targetScore = highSchoolScore; }
+
+ fluctuation = (((double) i) * FLUCTATION_MULTIPLIER) * (-1.0);
+ targetScore += fluctuation;
+
+ try {
+ //
+ // Only print out the first test taking.
+ // They all look alike.
+ //
+ if ( takings > 0 ) { log.enableLogging( false ); }
+
+ takeTest( database, student, test, targetScore );
+ takings++;
+ }
+ finally
+ {
+ log.enableLogging( loggingEnabled );
+ }
+ }
+
+ return takings;
+ }
+
+ /**
+ * <p>
+ * Take a test.
+ * </p>
+ */
+ public void takeTest
+ (
+ Database database,
+ Student student,
+ Test test,
+ double initialTargetScore
+ )
+ throws SQLException
+ {
+ Logger log = Logger.getLogger();
+ boolean loggingEnabled = log.isLoggingEnabled();
+ TestTaking testTaking;
+
+ // don't print out the chatter from creating these rows.
+ try {
+ log.enableLogging( false );
+
+ testTaking = takeTestingMinion
+ ( database, student, test, initialTargetScore );
+ }
+ finally
+ {
+ log.enableLogging( loggingEnabled );
+ }
+
+ // I want to see this, though
+ finishTest( database, testTaking );
+ }
+
+ /**
+ * <p>
+ * The guts of test taking, removed here
+ * to a separate method so that we can turn off logging.
+ * </p>
+ */
+ private TestTaking takeTestingMinion
+ (
+ Database database,
+ Student student,
+ Test test,
+ double initialTargetScore
+ )
+ throws SQLException
+ {
+ TestTaking testTaking = new TestTaking( student, test );
+ Connection conn = database.getConnection();
+ double targetScore = makeTargetScore
+ ( database, student, test, initialTargetScore );
+
+ //
+ // Initiate the test.
+ //
+ testTaking.create( database );
+
+ //
+ // Now get the questions which have to be answered.
+ //
+
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "select questionName, difficulty, numberOfChoices, " +
+ "correctChoice from Question\n" +
+ "where testID = ?"
+ );
+ ps.setInt( 1, test.getPrimaryKey( database ) );
+
+ ResultSet rs = ps.executeQuery();
+
+ double weightSoFar = 0.0;
+ double answersSoFar = 0.0;
+ double scoreSoFar = 0.0;
+
+ while( rs.next() )
+ {
+ int column = 1;
+ Question question = new Question
+ (
+ rs.getString( column++ ),
+ rs.getInt( column++ ),
+ rs.getInt( column++ ),
+ rs.getInt( column++ ),
+ test
+ );
+ int correctChoice = question.getCorrectChoice();
+ int difficulty = question.getDifficulty();
+ int actualChoice;
+
+ //
+ // Answer the question correctly if we haven't achieved our
+ // target score.
+ //
+ if ( scoreSoFar <= targetScore )
+ { actualChoice = correctChoice; }
+ else
+ { actualChoice = correctChoice - 1; }
+
+ weightSoFar += Utils.weighQuestion( difficulty );
+ answersSoFar += Utils.scoreAnswer
+ (
+ difficulty,
+ question.getNumberOfChoices(),
+ correctChoice,
+ actualChoice
+ );
+ scoreSoFar = Utils.finishScore( weightSoFar, answersSoFar );
+
+ QuestionTaking questionTaking =
+ new QuestionTaking( testTaking, question, actualChoice );
+
+ questionTaking.create( database );
+ }
+
+ return testTaking;
+ }
+
+ /**
+ * <p>
+ * The student's grade should improve with each taking.
+ * </p>
+ */
+ private double makeTargetScore
+ (
+ Database database,
+ Student student,
+ Test test,
+ double initialTargetScore
+ )
+ throws SQLException
+ {
+ int param = 1;
+ PreparedStatement ps = Utils.prepare
+ (
+ database.getConnection(),
+ "select count(*) from TestTaking\n" +
+ "where studentID = ? and testID = ?"
+ );
+
+ ps.setInt( param++, student.getPrimaryKey( database ) );
+ ps.setInt( param++, test.getPrimaryKey( database ) );
+
+ int takingCount = Utils.getScalarValue( ps );
+
+ return
+ (((double) takingCount) * IMPROVEMENT_PER_TAKING) +
+ initialTargetScore;
+ }
+
+ /**
+ * <p>
+ * Finish taking a Test. This involves updating the datestamp and
+ * triggering Test scoring.
+ * </p>
+ */
+ private void finishTest( Database database, TestTaking testTaking )
+ throws SQLException
+ {
+ Connection conn = database.getConnection();
+ int param = 1;
+ java.sql.Date date =
+ new java.sql.Date( System.currentTimeMillis() );
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "update TestTaking set takingDate = ? where takingID = ?\n"
+ );
+
+ ps.setDate( param++, date );
+ ps.setInt( param++, testTaking.getPrimaryKey( database ) );
+
+ ps.execute();
+
+ Utils.close( ps );
+ }
+
+ /**
+ * <p>
+ * Report which students in the given school incorrectly answered the
+ * given question.
+ * </p>
+ */
+ public void reportWhoNeedsImprovement
+ ( Database database, School school, String questionName )
+ throws SQLException
+ {
+ Connection conn = database.getConnection();
+ int param = 1;
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "select st.lastName, st.firstName\n" +
+ "from School sc, Student st,\n" +
+ " LastTaking lt, QuestionTaking qt,\n" +
+ " Question q\n" +
+ "where\n" +
+ "q.questionName = ?\n" +
+ "and sc.schoolID = ?\n" +
+ "\n" +
+ "and q.questionID = qt.questionID\n" +
+ "and sc.schoolID = st.schoolID\n" +
+ "\n" +
+ "and lt.testID = q.testID\n" +
+ "and lt.studentID = st.studentID\n" +
+ "and lt.takingID = qt.takingID\n" +
+ "\n" +
+ "and scoreAnswer"+
+ "( q.difficulty, q.numberOfChoices, " +
+ "q.correctChoice, qt.actualChoice )\n" +
+ " < weighQuestion( q.difficulty )\n" +
+ "\n" +
+ "order by st.lastName, st.firstName\n"
+ );
+
+ ps.setString( param++, questionName );
+ ps.setInt( param++, school.getPrimaryKey( database ) );
+
+ ResultSet rs = ps.executeQuery();
+
+ Database.prettyPrint( conn, rs );
+
+ Utils.close( rs );
+ Utils.close( ps );
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // MINIONS
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Create a bunch of questions from the template questions.
+ * </p>
+ */
+ private void initializeQuestions( Database database )
+ throws SQLException
+ {
+ int count = QUESTIONS.length;
+
+ for ( int i = 0; i < count; i++ )
+ {
+ Question template = QUESTIONS[ i ];
+
+ for ( int j = 0; j < QUESTIONS_PER_TEMPLATE; j++ )
+ {
+ Question clone = new Question
+ (
+ template.getQuestionName() + '_' + j,
+ template.getDifficulty(),
+ template.getNumberOfChoices(),
+ template.getCorrectChoice(),
+ template.getTest()
+ );
+
+ clone.create( database );
+ }
+ }
+ }
+
+ /**
+ * <p>
+ * Populate a table with its objects.
+ * </p>
+ */
+ private void initialize( Database database, KeyedObject[] objects )
+ throws SQLException
+ {
+ int count = objects.length;
+
+ for ( int i = 0; i < count; i++ )
+ { objects[ i ].create( database ); }
+ }
+
+}
Propchange: db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Data.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Data.java
------------------------------------------------------------------------------
svn:executable = *
Added: db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Database.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Database.java?view=auto&rev=470372
==============================================================================
--- db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Database.java (added)
+++ db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Database.java Thu Nov 2 06:26:09 2006
@@ -0,0 +1,572 @@
+/*
+
+ Derby - Class org.apache.derbyDemo.scores.data.Database
+
+ 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.
+
+ */
+
+package org.apache.derbyDemo.scores.data;
+
+import java.sql.*;
+
+import org.apache.derby.tools.JDBCDisplayUtil;
+
+import org.apache.derbyDemo.scores.util.*;
+
+/**
+ * <p>
+ * Top level object for accessing a SQL database. Implemented against
+ * embedded Derby. Could be extended for use against another server.
+ * </p>
+ *
+ * @author Rick Hillegas
+ */
+public class Database
+{
+ ////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ////////////////////////////////////////////////////////
+
+ public static final String DATABASE_NAME = "ScoresDB";
+
+ private static final String DERBY_EMBEDDED_DRIVER =
+ "org.apache.derby.jdbc.EmbeddedDriver";
+ private static final String DERBY_PROTOCOL = "jdbc:derby:";
+ private static final String CREATE_ME = ";create=true";
+
+ ////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ////////////////////////////////////////////////////////
+
+ private Data _data;
+ private Connection _conn;
+
+ ////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTOR
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Construct out of thin air.
+ * </p>
+ */
+ private Database()
+ {
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // STATIC BEHAVIOR
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Recode this method to return some subclass of Database if you
+ * want to use a different data store than Derby. This method
+ * creates the database if it does not already exist.
+ * </p>
+ */
+ public static Database getDatabase
+ ( String serverJar, String mathJar )
+ throws SQLException
+ {
+ Database db = new Database();
+ Logger log = Logger.getLogger();
+
+ db._data = new Data();
+
+ //
+ // This creates an empty database if it doesn't already exist.
+ //
+ Connection conn = db.getConnection();
+
+ //
+ // If the database is empty, populate it.
+ //
+ if ( !db.schemaExists( conn ) )
+ {
+ db.createSchema( conn, serverJar, mathJar );
+
+ // populate tables with initial slug of data
+ db._data.initialize( db );
+
+ log.logBanner( "Show schools, students, and tests..." );
+
+ db.prettyPrintSchool( conn );
+ db.prettyPrintStudent( conn );
+ db.prettyPrintTest( conn );
+ //db.prettyPrintQuestion( conn );
+ }
+
+ Utils.commit( conn );
+
+ return db;
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // OVERRIDABLE PUBLIC BEHAVIOR. OVERRIDE THESE METHODS
+ // IF YOU WANT TO USE A DIFFERENT DATA STORE. THIS IMPLEMENTATION
+ // WORKS AGAINST DERBY.
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Get a connection to the database.
+ * </p>
+ */
+ public Connection getConnection()
+ throws SQLException
+ {
+ if ( _conn != null ) { return _conn; }
+
+ try {
+ Class.forName( DERBY_EMBEDDED_DRIVER );
+ }
+ catch (ClassNotFoundException e)
+ {
+ throw new SQLException
+ ( "Could not locate " + DERBY_EMBEDDED_DRIVER );
+ }
+
+ String connectionURL =
+ DERBY_PROTOCOL + DATABASE_NAME + CREATE_ME;
+
+ _conn = DriverManager.getConnection( connectionURL );
+
+ _conn.setAutoCommit( false );
+
+ return _conn;
+ }
+
+ /////////
+ //
+ // SCHEMA
+ //
+ /////////
+
+ /**
+ * <p>
+ * Return true if the schema exists.
+ * </p>
+ */
+ protected boolean schemaExists( Connection conn )
+ throws SQLException
+ {
+ String heartbeat =
+ "select count(*) from sys.systables where tablename = 'SCHOOL'";
+
+ PreparedStatement ps = Utils.prepare( conn, heartbeat );
+ ResultSet rs = ps.executeQuery();
+
+ rs.next();
+
+ int count = rs.getInt( 1 );
+
+ Utils.close( rs );
+ Utils.close( ps );
+
+ return ( count > 0 );
+ }
+
+ /**
+ * <p>
+ * Create all schema objects.
+ * </p>
+ */
+ protected void createSchema
+ ( Connection conn, String serverJar, String mathJar )
+ throws SQLException
+ {
+ Logger log = Logger.getLogger();
+
+ log.logBanner
+ (
+ "Loading jar files into database and " +
+ "wiring-up the database classpath..." );
+
+ Utils.executeCall
+ (
+ conn,
+ "call sqlj.install_jar\n" +
+ "(\n" +
+ " '" + serverJar + "',\n" +
+ " 'APP.SCORES_SERVER',\n" +
+ " 0\n" +
+ ")\n"
+ );
+ Utils.executeCall
+ (
+ conn,
+ "call sqlj.install_jar\n" +
+ "(\n" +
+ " '" + mathJar +"',\n" +
+ " 'APP.APACHE_COMMONS_MATH',\n" +
+ " 0\n" +
+ ")\n"
+ );
+ Utils.executeCall
+ (
+ conn,
+ "call syscs_util.syscs_set_database_property\n" +
+ "(\n" +
+ " 'derby.database.classpath',\n" +
+ " 'APP.SCORES_SERVER:APP.APACHE_COMMONS_MATH'\n" +
+ ")\n"
+ );
+
+ log.logBanner
+ ( "Creating functions..." );
+
+ Utils.executeDDL
+ (
+ conn,
+ "create function formatPercent\n" +
+ "(\n" +
+ " score double\n" +
+ ")\n" +
+ "returns varchar( 7 )\n" +
+ "language java\n" +
+ "parameter style java\n" +
+ "no sql\n" +
+ "external name 'org.apache.derbyDemo.scores.proc.Functions.formatPercent'\n"
+ );
+ Utils.executeDDL
+ (
+ conn,
+ "create function weighQuestion\n" +
+ "(\n" +
+ " difficulty int\n" +
+ ")\n" +
+ "returns double\n" +
+ "language java\n" +
+ "parameter style java\n" +
+ "no sql\n" +
+ "external name 'org.apache.derbyDemo.scores.proc.Functions.weighQuestion'\n"
+ );
+ Utils.executeDDL
+ (
+ conn,
+ "create function scoreAnswer\n" +
+ "(\n" +
+ " difficulty int,\n" +
+ " numberOfChoices int,\n" +
+ " correctChoice int,\n" +
+ " actualChoice int\n" +
+ ")\n" +
+ "returns double\n" +
+ "language java\n" +
+ "parameter style java\n" +
+ "no sql\n" +
+ "external name 'org.apache.derbyDemo.scores.proc.Functions.scoreAnswer'\n"
+ );
+ Utils.executeDDL
+ (
+ conn,
+ "create function computeAge\n" +
+ "(\n" +
+ " birthday date\n" +
+ ")\n" +
+ "returns int\n" +
+ "language java\n" +
+ "parameter style java\n" +
+ "no sql\n" +
+ "external name 'org.apache.derbyDemo.scores.proc.Functions.computeAge'\n"
+ );
+ Utils.executeDDL
+ (
+ conn,
+ "create function getMedianTestScore\n" +
+ "(\n" +
+ " testID int\n" +
+ ")\n" +
+ "returns double\n" +
+ "language java\n" +
+ "parameter style java\n" +
+ "reads sql data\n" +
+ "external name " +
+ "'org.apache.derbyDemo.scores.proc.Functions.getMedianTestScore'\n"
+ );
+ Utils.executeDDL
+ (
+ conn,
+ "create function vetChoice\n" +
+ "(\n" +
+ " actualChoice int,\n" +
+ " questionID int\n" +
+ ")\n" +
+ "returns int\n" +
+ "language java\n" +
+ "parameter style java\n" +
+ "reads sql data\n" +
+ "external name 'org.apache.derbyDemo.scores.proc.Functions.vetChoice'\n"
+ );
+
+ log.logBanner
+ ( "Creating procedures..." );
+
+ Utils.executeDDL
+ (
+ conn,
+ "create procedure ScoreTestTaking\n" +
+ "( in takingID int )\n" +
+ "language java\n" +
+ "parameter style java\n" +
+ "modifies sql data\n" +
+ "external name 'org.apache.derbyDemo.scores.proc.Procedures.ScoreTestTaking'\n"
+ );
+
+ log.logBanner
+ (
+ "Creating tables. Note the function " +
+ "in the check constraint on QuestionTaking..." );
+
+ Utils.executeDDL
+ (
+ conn,
+ "create table School\n" +
+ "(\n" +
+ " schoolID int primary key generated always as identity,\n" +
+ " schoolName varchar( 20 ) not null,\n" +
+ "\n" +
+ " unique( schoolName )\n" +
+ ")\n"
+ );
+ Utils.executeDDL
+ (
+ conn,
+ "create table Student\n" +
+ "(\n" +
+ " studentID int primary key generated always as identity,\n" +
+ " schoolID int not null references School( schoolID ),\n" +
+ " lastName varchar( 10 ) not null,\n" +
+ " firstName varchar( 10 ) not null,\n" +
+ " birthday date not null,\n" +
+ "\n" +
+ " unique( lastName, firstName )\n" +
+ ")\n"
+ );
+ Utils.executeDDL
+ (
+ conn,
+ "create table Test\n" +
+ "(\n" +
+ " testID int primary key generated always as identity,\n" +
+ " testName varchar( 20 ) not null,\n" +
+ "\n" +
+ " unique( testName )\n" +
+ ")\n"
+ );
+ Utils.executeDDL
+ (
+ conn,
+ "create table TestTaking\n" +
+ "(\n" +
+ " takingID int primary key generated always as identity,\n" +
+ " studentID int not null references Student( studentID ),\n" +
+ " testID int not null references Test( testID ),\n" +
+ " takingDate date,\n" +
+ " score double not null\n" +
+ ")\n"
+ );
+ Utils.executeDDL
+ (
+ conn,
+ "create table Question\n" +
+ "(\n" +
+ " questionID int primary key " +
+ " generated always as identity,\n" +
+ " testID int not null references Test( testID ),\n" +
+ " questionName varchar( 10 ) not null unique,\n" +
+ " difficulty int not null,\n" +
+ " numberOfChoices int not null,\n" +
+ " correctChoice int not null,\n" +
+ "\n" +
+ " check ( ( correctChoice > -1 ) " +
+ " and ( correctChoice < numberOfChoices ) )\n" +
+ ")\n"
+ );
+ Utils.executeDDL
+ (
+ conn,
+ "create table QuestionTaking\n" +
+ "(\n" +
+ " questionID int not null" +
+ " references Question( questionID ),\n" +
+ " takingID int not null" +
+ " references TestTaking( takingID ),\n" +
+ " actualChoice int not null,\n" +
+ "\n" +
+ " unique( questionID, takingID ),\n" +
+ " check ( vetChoice( actualChoice, questionID ) > 0 )\n" +
+ ")\n"
+ );
+
+ log.logBanner( "Creating views..." );
+
+ Utils.executeDDL
+ (
+ conn,
+ "create view LastTaking\n" +
+ "(\n" +
+ " takingID,\n" +
+ " studentID,\n" +
+ " testID\n" +
+ ")\n" +
+ "as select max( takingID ), studentID, testID\n" +
+ "from TestTaking\n" +
+ "group by studentID, testID\n"
+ );
+
+ log.logBanner
+ (
+ "Creating triggers. Note that the trigger " +
+ "invokes a procedure..."
+ );
+
+ Utils.executeDDL
+ (
+ conn,
+ "create trigger ScoreTestWhenDone\n" +
+ "after update of takingDate\n" +
+ "on TestTaking\n" +
+ "referencing new as testTakingRow\n" +
+ "for each row mode db2sql\n" +
+ "call ScoreTestTaking( testTakingRow.takingID )\n"
+ );
+ }
+
+
+ ////////////////////////////////////////////////////////
+ //
+ // PRETTY PRINITING
+ //
+ ////////////////////////////////////////////////////////
+
+ /** <p>Pretty print the School table.</p> */
+ public void prettyPrintSchool( Connection conn )
+ throws SQLException
+ {
+ prettyPrint( conn, "select * from School order by schoolName\n" );
+ }
+
+ /** <p>Pretty print the Student table.</p> */
+ public void prettyPrintStudent( Connection conn )
+ throws SQLException
+ {
+ prettyPrint
+ (
+ conn,
+ "select st.studentID, sc.schoolName," +
+ " st.lastName, st.firstName, st.birthday\n" +
+ "from Student st, School sc\n" +
+ "where st.schoolID = sc.schoolID\n" +
+ "order by st.lastName, st.firstName\n"
+ );
+ }
+
+ /** <p>Pretty print the Test table.</p> */
+ public void prettyPrintTest( Connection conn )
+ throws SQLException
+ {
+ prettyPrint( conn, "select * from Test order by testName\n" );
+ }
+
+ /** <p>Pretty print the Question table.</p> */
+ public void prettyPrintQuestion( Connection conn )
+ throws SQLException
+ {
+ prettyPrint
+ (
+ conn,
+ "select * from Question order by testID, questionID\n"
+ );
+ }
+
+ /** <p>Pretty print the QuestionTaking table.</p> */
+ public void prettyPrintQuestionTaking( Connection conn )
+ throws SQLException
+ {
+ prettyPrint
+ (
+ conn,
+ "select * from QuestionTaking order by takingID, questionID\n"
+ );
+ }
+
+ /** <p>Pretty print the TestTaking table.</p> */
+ public void prettyPrintTestTaking( Connection conn )
+ throws SQLException
+ {
+ prettyPrint( conn, "select * from TestTaking order by takingID\n" );
+ }
+
+ /**
+ * <p>
+ * Pretty print the results of a query, given its text.
+ * </p>
+ */
+ public static void prettyPrint( Connection conn, String text )
+ throws SQLException
+ {
+ PreparedStatement ps = Utils.prepare( conn, text );
+ ResultSet rs = ps.executeQuery();
+
+ prettyPrint( conn, rs );
+
+ Utils.close( rs );
+ Utils.close( ps );
+ }
+
+ /**
+ * <p>
+ * Print a ResultSet, using Derby's pretty-printing tool.
+ * </p>
+ */
+ public static void prettyPrint( Connection conn, ResultSet rs )
+ throws SQLException
+ {
+ Logger log = Logger.getLogger();
+
+ JDBCDisplayUtil.DisplayResults
+ ( log.getPrintStream(), rs, conn );
+
+ log.log( "\n" );
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // OTHER MINIONS
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Get the Data populating tool.
+ * </p>
+ */
+ public Data getData() { return _data; }
+
+}
Propchange: db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Database.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/demo/scores/java/client/org/apache/derbyDemo/scores/data/Database.java
------------------------------------------------------------------------------
svn:executable = *
Added: db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Logger.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Logger.java?view=auto&rev=470372
==============================================================================
--- db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Logger.java (added)
+++ db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Logger.java Thu Nov 2 06:26:09 2006
@@ -0,0 +1,152 @@
+/*
+
+ Derby - Class org.apache.derbyDemo.scores.util.Logger
+
+ 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.
+
+ */
+
+package org.apache.derbyDemo.scores.util;
+
+import java.io.*;
+
+/**
+ * <p>
+ * Utility class for logging messages.
+ * </p>
+ *
+ * @author Rick Hillegas
+ */
+public class Logger
+{
+ ////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ////////////////////////////////////////////////////////
+
+ private static final String BANNER_BRACKET =
+ "-----------------------------------------------------";
+ private static final String BANNER_INDENTATION =
+ "-- ";
+
+ ////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ////////////////////////////////////////////////////////
+
+ private static Logger _logger;
+ private PrintStream _printStream;
+ private boolean _loggingEnabled = true;
+
+ ////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTOR
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Construct out of thin air.
+ * </p>
+ */
+ public Logger()
+ {
+ _printStream = System.out;
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // PUBLIC BEHAVIOR
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Get the logging tool.
+ * </p>
+ */
+ public static Logger getLogger()
+ {
+ if ( _logger == null ) { _logger = new Logger(); }
+
+ return _logger;
+ }
+
+ /**
+ * <p>
+ * Report whether logging is enabled.
+ * </p>
+ */
+ public boolean isLoggingEnabled() { return _loggingEnabled; }
+
+ /**
+ * <p>
+ * Enable or disable printing.
+ * </p>
+ */
+ public void enableLogging( boolean enableLogging )
+ {
+ _loggingEnabled = enableLogging;
+ }
+
+ /**
+ * <p>
+ * Log a text message.
+ * </p>
+ */
+ public void log( String text )
+ {
+ if ( _loggingEnabled) { _printStream.println( text ); }
+ }
+
+ /**
+ * <p>
+ * Log an exception.
+ * </p>
+ */
+ public void log( Throwable t )
+ {
+ log( t.getMessage() );
+
+ t.printStackTrace( _printStream );
+ }
+
+ /**
+ * <p>
+ * Get the PrintStream used by this Logger.
+ * </p>
+ */
+ public PrintStream getPrintStream() { return _printStream; }
+
+ /**
+ * <p>
+ * Print a message inside a banner comment.
+ * </p>
+ */
+ public void logBanner( String text )
+ {
+ log( "\n" );
+ log( BANNER_BRACKET );
+ log( BANNER_INDENTATION );
+ log( BANNER_INDENTATION + text );
+ log( BANNER_INDENTATION );
+ log( BANNER_BRACKET );
+ log( "\n" );
+ }
+
+}
Propchange: db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Logger.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Logger.java
------------------------------------------------------------------------------
svn:executable = *
Added: db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Utils.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Utils.java?view=auto&rev=470372
==============================================================================
--- db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Utils.java (added)
+++ db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Utils.java Thu Nov 2 06:26:09 2006
@@ -0,0 +1,267 @@
+/*
+
+ Derby - Class org.apache.derbyDemo.scores.util.Utils
+
+ 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.
+
+ */
+
+package org.apache.derbyDemo.scores.util;
+
+import java.io.*;
+import java.sql.*;
+
+/**
+ * <p>
+ * Utility methods for the Scores application
+ * </p>
+ *
+ * @author Rick Hillegas
+ */
+public class Utils
+{
+ ////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ////////////////////////////////////////////////////////
+
+ /* private static final String PREPARE_PREAMBLE = "Preparing..."; */
+ private static final String PREPARE_PREAMBLE = "";
+
+ ////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ////////////////////////////////////////////////////////
+
+ ////////////////////////////////////////////////////////
+ //
+ // CONSTRUCTOR
+ //
+ ////////////////////////////////////////////////////////
+
+ ////////////////////////////////////////////////////////
+ //
+ // PUBLIC BEHAVIOR
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Get the logging tool.
+ * </p>
+ */
+ public static Logger getLogger()
+ {
+ return Logger.getLogger();
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // METHODS CALLED BY CALLED BY CLIENT CODE AND SERVER-SIDE FUNCTIONS
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Compute the relative weight of a Question given its difficulty.
+ * </p>
+ */
+ public static double weighQuestion(int difficulty)
+ {
+ return (double) difficulty;
+ }
+
+ /**
+ * <p>
+ * Compute the score for a question. A penalty is assessed
+ * for guessing the wrong answer. If actualChoice is -1,
+ * then the Student didn't guess and we don't assess a penalty.
+ * </p>
+ */
+ public static double scoreAnswer
+ (
+ int difficulty,
+ int numberOfChoices,
+ int correctChoice,
+ int actualChoice
+ )
+ {
+ if( actualChoice < 0 ) { return 0.0; }
+
+ double weight = weighQuestion(difficulty);
+
+ if ( correctChoice == actualChoice ) { return weight; }
+ else
+ {
+ double penaltyRatio = 1.0 / ((double) numberOfChoices);
+ return -(weight * penaltyRatio);
+ }
+ }
+
+ /**
+ * <p>
+ * Calculate the final score as a percentage, given the highest possible
+ * score and the actual score achieved.
+ * </p>
+ */
+ public static double finishScore(double allCorrect, double actual)
+ {
+ if ( actual < 0.0D ) { return 0.0D; }
+ else { return (100D * actual) / allCorrect; }
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // SQL MINIONS
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Commit a transaction
+ * </p>
+ */
+ public static void commit( Connection conn )
+ throws SQLException
+ {
+ getLogger().log( "Committing..." );
+
+ conn.commit();
+ }
+
+ /**
+ * <p>
+ * Execute a DDL statement for creating a schema object.
+ * </p>
+ */
+ public static void executeDDL( Connection conn, String text )
+ throws SQLException
+ {
+ PreparedStatement ps = prepare( conn, text );
+
+ ps.execute();
+
+ close( ps );
+ }
+
+ /**
+ * <p>
+ * Execute a call statement.
+ * </p>
+ */
+ public static void executeCall( Connection conn, String text )
+ throws SQLException
+ {
+ CallableStatement cs = prepareCall( conn, text );
+
+ cs.execute();
+
+ close( cs );
+ }
+
+ /**
+ * <p>
+ * Prepare a SQL statement.
+ * </p>
+ */
+ public static PreparedStatement prepare
+ ( Connection conn, String text )
+ throws SQLException
+ {
+ getLogger().log( PREPARE_PREAMBLE + text );
+
+ return conn.prepareStatement( text );
+ }
+
+ /**
+ * <p>
+ * Prepare a SQL CALL statement.
+ * </p>
+ */
+ public static CallableStatement prepareCall
+ ( Connection conn, String text )
+ throws SQLException
+ {
+ getLogger().log( PREPARE_PREAMBLE + text );
+
+ return conn.prepareCall( text );
+ }
+
+ /**
+ * <p>
+ * Close a PreparedStatement, releasing its resources.
+ * </p>
+ */
+ public static void close( PreparedStatement ps )
+ throws SQLException
+ {
+ if ( ps != null ) { ps.close(); }
+ }
+
+ /**
+ * <p>
+ * Close a ResultSet, releasing its resources.
+ * </p>
+ */
+ public static void close( ResultSet rs )
+ throws SQLException
+ {
+ if ( rs != null ) { rs.close(); }
+ }
+
+ /**
+ * <p>
+ * Close a Connection, releasing its resources.
+ * </p>
+ */
+ public static void close( Connection conn )
+ throws SQLException
+ {
+ if ( conn != null ) { conn.close(); }
+ }
+
+ /**
+ * <p>
+ * Get a scalar value from a PreparedStatement.
+ * </p>
+ */
+ public static int getScalarValue( PreparedStatement ps )
+ throws SQLException
+ {
+ ResultSet rs = ps.executeQuery();
+
+ rs.next();
+
+ try {
+ return rs.getInt( 1 );
+ }
+ finally
+ {
+ close( rs );
+ close( ps );
+ }
+ }
+
+ ////////////////////////////////////////////////////////
+ //
+ // MINIONS
+ //
+ ////////////////////////////////////////////////////////
+
+
+}
Propchange: db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Utils.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/demo/scores/java/common/org/apache/derbyDemo/scores/util/Utils.java
------------------------------------------------------------------------------
svn:executable = *
Added: db/derby/code/trunk/java/demo/scores/java/server/org/apache/derbyDemo/scores/proc/Functions.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/demo/scores/java/server/org/apache/derbyDemo/scores/proc/Functions.java?view=auto&rev=470372
==============================================================================
--- db/derby/code/trunk/java/demo/scores/java/server/org/apache/derbyDemo/scores/proc/Functions.java (added)
+++ db/derby/code/trunk/java/demo/scores/java/server/org/apache/derbyDemo/scores/proc/Functions.java Thu Nov 2 06:26:09 2006
@@ -0,0 +1,223 @@
+/*
+
+ Derby - Class org.apache.derbyDemo.scores.proc.Functions
+
+ 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.
+
+ */
+
+package org.apache.derbyDemo.scores.proc;
+
+import java.sql.*;
+import java.text.*;
+import java.util.*;
+
+import org.apache.commons.math.stat.descriptive.rank.Median;
+
+import org.apache.derbyDemo.scores.util.*;
+
+/**
+ * <p>
+ * Functions used by the Scores application.
+ * </p>
+ *
+ * @author Rick Hillegas
+ */
+public class Functions
+{
+ ////////////////////////////////////////////////////////
+ //
+ // CONSTANTS
+ //
+ ////////////////////////////////////////////////////////
+
+ public static final long MILLISECONDS_IN_YEAR =
+ 1000L * 60L * 60L * 24L * 365L;
+
+ ////////////////////////////////////////////////////////
+ //
+ // STATE
+ //
+ ////////////////////////////////////////////////////////
+
+ ////////////////////////////////////////////////////////
+ //
+ // STATIC BEHAVIOR
+ //
+ ////////////////////////////////////////////////////////
+
+ /**
+ * <p>
+ * Check that a legal answer was given to a question.
+ * Throws an exception if it is not.
+ * </p>
+ */
+ public static int vetChoice( int actualChoice, int questionID )
+ throws SQLException
+ {
+ Connection conn = getDefaultConnection();
+ int column = 1;
+
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "select numberOfChoices, questionName\n" +
+ "from Question\n" +
+ "where questionID = ?\n"
+ );
+
+ ps.setInt( 1, questionID );
+
+ ResultSet rs = ps.executeQuery();
+
+ rs.next();
+
+ int numberOfChoices = rs.getInt( column++ );
+ String questionName = rs.getString( column++ );
+
+ Utils.close(rs);
+ Utils.close(ps);
+
+ if ( ( actualChoice >= -1 ) && ( actualChoice < numberOfChoices ) )
+ { return 1; }
+ else
+ {
+ throw new SQLException
+ (
+ "Illegal answer " + actualChoice +
+ " given to question " + questionName
+ );
+ }
+ }
+
+ /**
+ * <p>
+ * Compute a Student's age given their birthday.
+ * </p>
+ */
+ public static int computeAge( java.sql.Date date)
+ {
+ long interval = System.currentTimeMillis() - date.getTime();
+ return (int)(interval / MILLISECONDS_IN_YEAR);
+ }
+
+ /**
+ * <p>
+ * Compute the relative weight of a Question given its difficulty.
+ * </p>
+ */
+ public static double weighQuestion(int difficulty)
+ {
+ return Utils.weighQuestion( difficulty );
+ }
+
+ /**
+ * <p>
+ * Compute the score for a question. A penalty is assessed
+ * for guessing the wrong answer. If actualChoice is -1,
+ * then the Student didn't guess and we don't assess a penalty.
+ * </p>
+ */
+ public static double scoreAnswer
+ (
+ int difficulty,
+ int numberOfChoices,
+ int correctChoice,
+ int actualChoice
+ )
+ {
+ return Utils.scoreAnswer
+ ( difficulty, numberOfChoices, correctChoice, actualChoice );
+ }
+
+ /**
+ * <p>
+ * Calculate the median score achieved on a Test.
+ * </p>
+ */
+ public static double getMedianTestScore(int testID)
+ throws SQLException
+ {
+ Logger log = Logger.getLogger();
+ boolean loggingEnabled = log.isLoggingEnabled();
+ Median median = new Median();
+ ArrayList arraylist = new ArrayList();
+ Connection conn = getDefaultConnection();
+
+ try {
+ log.enableLogging( false );
+
+ PreparedStatement ps = Utils.prepare
+ (
+ conn,
+ "select tk.score\n" +
+ "from TestTaking tk, LastTaking lt\n" +
+ "where tk.takingID = lt.takingID\n" +
+ "and tk.testID = ?\n"
+ );
+
+ ps.setInt( 1, testID );
+
+ ResultSet rs = ps.executeQuery();
+
+ while( rs.next() )
+ {
+ arraylist.add(new Double(rs.getDouble(1)));
+ }
+
+ Utils.close(rs);
+ Utils.close(ps);
+ }
+ finally
+ {
+ log.enableLogging( loggingEnabled );
+ }
+
+ int count = arraylist.size();
+ double values[] = new double[ count ];
+
+ for ( int i = 0; i < count; i++)
+ { values[ i ] = ((Double)arraylist.get(i)).doubleValue(); }
+
+ return median.evaluate( values );
+ }
+
+ /**
+ * <p>
+ * Format a double as a percentage, suitable for printing.
+ * </p>
+ */
+ public static String formatPercent(double score)
+ throws SQLException
+ {
+ int rounded = (int) score;
+ NumberFormat nf = NumberFormat.getNumberInstance();
+
+ return nf.format( rounded );
+ }
+
+ /**
+ * <p>
+ * Get the default connection, called from inside the database engine.
+ * </p>
+ */
+ static Connection getDefaultConnection()
+ throws SQLException
+ {
+ return DriverManager.getConnection("jdbc:default:connection");
+ }
+
+}
Propchange: db/derby/code/trunk/java/demo/scores/java/server/org/apache/derbyDemo/scores/proc/Functions.java
------------------------------------------------------------------------------
svn:eol-style = native
Propchange: db/derby/code/trunk/java/demo/scores/java/server/org/apache/derbyDemo/scores/proc/Functions.java
------------------------------------------------------------------------------
svn:executable = *