You are viewing a plain text version of this content. The canonical link for it is here.
Posted to scm@geronimo.apache.org by ho...@apache.org on 2006/12/04 22:32:40 UTC
svn commit: r482366 - in /geronimo/daytrader/trunk/modules:
ejb/src/main/java/org/apache/geronimo/samples/daytrader/direct/
web/src/main/java/org/apache/geronimo/samples/daytrader/web/
web/src/main/webapp/ web/src/main/webapp/dbscripts/ web/src/main/we...
Author: hogstrom
Date: Mon Dec 4 13:32:37 2006
New Revision: 482366
URL: http://svn.apache.org/viewvc?view=rev&rev=482366
Log:
DAYTRADER-14 Added support for dynamic definition of tables
Added:
geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/
geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/db2/
geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/db2/Table.ddl
geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/derby/
geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/derby/Table.ddl
geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/oracle/
geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/oracle/Table.ddl
Modified:
geronimo/daytrader/trunk/modules/ejb/src/main/java/org/apache/geronimo/samples/daytrader/direct/TradeDirect.java
geronimo/daytrader/trunk/modules/web/src/main/java/org/apache/geronimo/samples/daytrader/web/TradeBuildDB.java
geronimo/daytrader/trunk/modules/web/src/main/java/org/apache/geronimo/samples/daytrader/web/TradeConfigServlet.java
geronimo/daytrader/trunk/modules/web/src/main/webapp/configure.html
Modified: geronimo/daytrader/trunk/modules/ejb/src/main/java/org/apache/geronimo/samples/daytrader/direct/TradeDirect.java
URL: http://svn.apache.org/viewvc/geronimo/daytrader/trunk/modules/ejb/src/main/java/org/apache/geronimo/samples/daytrader/direct/TradeDirect.java?view=diff&rev=482366&r1=482365&r2=482366
==============================================================================
--- geronimo/daytrader/trunk/modules/ejb/src/main/java/org/apache/geronimo/samples/daytrader/direct/TradeDirect.java (original)
+++ geronimo/daytrader/trunk/modules/ejb/src/main/java/org/apache/geronimo/samples/daytrader/direct/TradeDirect.java Mon Dec 4 13:32:37 2006
@@ -32,11 +32,14 @@
import java.rmi.RemoteException;
import java.sql.Connection;
+import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
import java.sql.Timestamp;
-import org.apache.geronimo.samples.daytrader.*;
+import org.apache.geronimo.samples.daytrader.*;;
/**
* TradeDirect uses direct JDBC and JMS access to a <code>javax.sql.DataSource</code> to implement the business methods
@@ -1807,7 +1810,72 @@
return orderData;
}
-
+ public String checkDBProductName() throws Exception
+ {
+ Connection conn = null;
+ String dbProductName = null;
+
+ try
+ {
+ if (Log.doTrace()) Log.traceEnter("TradeDirect:checkDBProductName");
+
+ conn = getConn();
+ DatabaseMetaData dbmd = conn.getMetaData();
+ dbProductName = dbmd.getDatabaseProductName();
+ }
+ catch (SQLException e)
+ {
+ Log.error(e,"TradeDirect:checkDBProductName() -- Error checking the Daytrader Database Product Name");
+ }
+ finally
+ {
+ releaseConn(conn);
+ }
+ return dbProductName;
+ }
+
+ public boolean recreateDBTables(Object[] sqlBuffer, java.io.PrintWriter out) throws Exception
+ {
+ //Clear MDB Statistics
+ MDBStats.getInstance().reset();
+
+ Connection conn = null;
+ boolean success = false;
+ try
+ {
+ if (Log.doTrace()) Log.traceEnter("TradeDirect:recreateDBTables");
+
+ conn = getConn();
+ Statement stmt = conn.createStatement();
+ int bufferLength = sqlBuffer.length;
+ for (int i = 0; i< bufferLength; i++)
+ {
+ try
+ {
+ stmt.executeUpdate((String)sqlBuffer[i]);
+ //commit(conn);
+ }
+ catch(SQLException ex)
+ {
+ Log.error("TradeDirect:recreateDBTables SQL Exception thrown on executing the foll sql command: " + sqlBuffer[i], ex);
+ out.println("<BR>SQL Exception thrown on executing the foll sql command: <I>" + sqlBuffer[i] + "</I> . Check log for details.</BR>");
+ }
+ }
+ stmt.close();
+ commit(conn);
+ success = true;
+ }
+ catch (Exception e)
+ {
+ Log.error(e,"TradeDirect:recreateDBTables() -- Error dropping and recreating the database tables");
+ }
+ finally
+ {
+ releaseConn(conn);
+ }
+ return success;
+ }
+
public RunStatsDataBean resetTrade(boolean deleteAll)
throws Exception
{
@@ -1817,7 +1885,6 @@
RunStatsDataBean runStatsData = new RunStatsDataBean();
Connection conn=null;
- UserTransaction txn = null;
try
{
if (Log.doTrace()) Log.traceEnter("TradeDirect:resetTrade deleteAll rows=" + deleteAll);
@@ -1994,7 +2061,7 @@
stmt.close();
rs.close();
- commit(conn);
+ commit(conn);
System.out.println("TradeDirect:reset Run stats data\n\n" + runStatsData);
}
Modified: geronimo/daytrader/trunk/modules/web/src/main/java/org/apache/geronimo/samples/daytrader/web/TradeBuildDB.java
URL: http://svn.apache.org/viewvc/geronimo/daytrader/trunk/modules/web/src/main/java/org/apache/geronimo/samples/daytrader/web/TradeBuildDB.java?view=diff&rev=482366&r1=482365&r2=482366
==============================================================================
--- geronimo/daytrader/trunk/modules/web/src/main/java/org/apache/geronimo/samples/daytrader/web/TradeBuildDB.java (original)
+++ geronimo/daytrader/trunk/modules/web/src/main/java/org/apache/geronimo/samples/daytrader/web/TradeBuildDB.java Mon Dec 4 13:32:37 2006
@@ -17,15 +17,22 @@
package org.apache.geronimo.samples.daytrader.web;
+import java.io.BufferedReader;
+import java.io.File;
+import java.io.FileReader;
+import java.io.IOException;
import java.math.BigDecimal;
+import java.util.ArrayList;
+import org.apache.geronimo.samples.daytrader.direct.TradeDirect;
import org.apache.geronimo.samples.daytrader.util.*;
import org.apache.geronimo.samples.daytrader.*;
/**
- * TradeBuildDB uses operations provided by the TradeApplication to initially populate a Trade
- * database. Specifically, a new Trade User population is created using UserIDs of the form "uid:xxx"
+ * TradeBuildDB uses operations provided by the TradeApplication to (a) create the Database tables
+ * (b)populate a DayTrader database without creating the tables. Specifically, a
+ * new DayTrader User population is created using UserIDs of the form "uid:xxx"
* where xxx is a sequential number (e.g. uid:0, uid:1, etc.). New stocks are also created of the
* form "s:xxx", again where xxx represents sequential numbers (e.g. s:1, s:2, etc.)
*/
@@ -38,24 +45,122 @@
* Populate a Trade DB using standard out as a log
*/
public TradeBuildDB() throws Exception {
- this(new java.io.PrintWriter(System.out));
+ this(new java.io.PrintWriter(System.out), null);
}
/**
- * Populate a Trade DB, logging to the provided output stream
+ * Re-create the DayTrader db tables and populate them OR just populate a DayTrader DB, logging to the provided output stream
*/
- public TradeBuildDB(java.io.PrintWriter out) throws Exception {
+ public TradeBuildDB(java.io.PrintWriter out, String warPath) throws Exception {
String symbol, companyName;
int errorCount = 0; // Give up gracefully after 10 errors
TradeAction tradeAction = new TradeAction();
// TradeStatistics.statisticsEnabled=false; // disable statistics
out.println(
- "<HEAD><BR><EM> TradeBuildDB: Building Trade Database...</EM><BR> This operation will take several minutes. Please wait...</HEAD>");
-
+ "<HEAD><BR><EM> TradeBuildDB: Building DayTrader Database...</EM><BR> This operation will take several minutes. Please wait...</HEAD>");
out.println("<BODY>");
- out.println(
- "<BR>TradeBuildDB: **** Creating " + TradeConfig.getMAX_QUOTES() + " Quotes ****</BR>");
+
+ if (warPath != null)
+ {
+ //out.println("<BR>TradeBuildDB: **** warPath= "+warPath+" ****</BR></BODY>");
+
+ TradeDirect tradeDirect = new TradeDirect();
+ boolean success = false;
+ String dbProductName = null;
+ File ddlFile = null;
+ Object[] sqlBuffer = null;
+
+ //Find out the Database being used
+ try
+ {
+ dbProductName = tradeDirect.checkDBProductName();
+ }
+ catch (Exception e)
+ {
+ Log.error(e, "TradeBuildDB: Unable to check DB Product name");
+ }
+ if (dbProductName == null)
+ {
+ out.println("<BR>TradeBuildDB: **** Unable to check DB Product name, please check Database/AppServer configuration and retry ****</BR></BODY>");
+ return;
+ }
+
+ //Locate DDL file for the specified database
+ try
+ {
+ out.println("<BR>TradeBuildDB: **** Database Product detected: "+dbProductName+" ****</BR>");
+ if (dbProductName.startsWith("DB2/")) // if db is DB2
+ {
+ ddlFile = new File(warPath+File.separatorChar+"dbscripts"+File.separatorChar+"db2"+File.separatorChar+"Table.ddl");
+ }
+ else if (dbProductName.startsWith("Apache Derby")) //if db is Derby
+ {
+ ddlFile = new File(warPath+File.separatorChar+"dbscripts"+File.separatorChar+"derby"+File.separatorChar+"Table.ddl");
+ }
+ else if (dbProductName.startsWith("Oracle")) // if the Db is Oracle
+ {
+ ddlFile = new File(warPath+File.separatorChar+"dbscripts"+File.separatorChar+"oracle"+File.separatorChar+"Table.ddl");
+ }
+ else // Unsupported "Other" Database
+ {
+ ddlFile = new File(warPath+File.separatorChar+"dbscripts"+File.separatorChar+"other"+File.separatorChar+"Table.ddl");
+ out.println("<BR>TradeBuildDB: **** This Database is unsupported/untested use at your own risk ****</BR>");
+ }
+
+ if (!ddlFile.exists())
+ {
+ Log.error("TradeBuildDB: DDL file doesnt exist at path "+ ddlFile.getCanonicalPath()+" , please provide the file and retry");
+ out.println("<BR>TradeBuildDB: DDL file doesnt exist at path <I>"+ ddlFile.getCanonicalPath()+"</I> , please provide the file and retry ****</BR></BODY>");
+ return;
+ }
+ out.println("<BR>TradeBuildDB: **** The DDL file at path <I>"+ ddlFile.getCanonicalPath()+"</I> will be used ****</BR>");
+ out.flush();
+ }
+ catch (Exception e)
+ {
+ Log.error(e, "TradeBuildDB: Unable to locate DDL file for the specified database");
+ out.println("<BR>TradeBuildDB: **** Unable to locate DDL file for the specified database ****</BR></BODY>");
+ return;
+ }
+
+ //parse the DDL file and fill the SQL commands into a buffer
+ try
+ {
+ sqlBuffer = parseDDLToBuffer(ddlFile);
+ }
+ catch (Exception e)
+ {
+ Log.error(e, "TradeBuildDB: Unable to parse DDL file");
+ out.println("<BR>TradeBuildDB: **** Unable to parse DDL file for the specified database ****</BR></BODY>");
+ return;
+ }
+ if ((sqlBuffer == null)||(sqlBuffer.length == 0))
+ {
+ out.println("<BR>TradeBuildDB: **** Parsing DDL file returned empty buffer, please check that a valid DB specific DDL file is available and retry ****</BR></BODY>");
+ return;
+ }
+
+ // send the sql commands buffer to drop and recreate the Daytrader tables
+ out.println("<BR>TradeBuildDB: **** Dropping and Recreating the DayTrader tables... ****</BR>");
+ try
+ {
+ success = tradeDirect.recreateDBTables(sqlBuffer, out);
+ }
+ catch (Exception e)
+ {
+ Log.error(e, "TradeBuildDB: Unable to drop and recreate DayTrader Db Tables, please check for database consistency before continuing");
+ }
+ if (!success)
+ {
+ out.println("<BR>TradeBuildDB: **** Unable to drop and recreate DayTrader Db Tables, please check for database consistency before continuing ****</BR></BODY>");
+ return;
+ }
+ out.println("<BR>TradeBuildDB: **** DayTrader tables successfully created! ****</BR><BR><b> Please Stop and Re-start your Daytrader application (or your application server) and then use the \"Repopulate Daytrader Database\" link to populate your database.</b></BR><BR><BR></BODY>");
+ return;
+ } // end of createDBTables
+
+ out.println("<BR>TradeBuildDB: **** Creating " + TradeConfig.getMAX_QUOTES() + " Quotes ****</BR>");
//Attempt to delete all of the Trade users and Trade Quotes first
try
{
@@ -149,8 +254,58 @@
} // end-for
out.println("</BODY>");
}
+
+ public Object[] parseDDLToBuffer(File ddlFile) throws Exception
+ {
+ BufferedReader br = null;
+ ArrayList sqlBuffer = new ArrayList(30); //initial capacity 30 assuming we have 30 ddl-sql statements to read
+
+ try
+ {
+ if (Log.doTrace()) Log.traceEnter("TradeBuildDB:parseDDLToBuffer - " + ddlFile);
+
+ br = new BufferedReader(new FileReader(ddlFile));
+ String s;
+ String sql = new String();
+ while ((s = br.readLine()) != null)
+ {
+ s = s.trim();
+ if ((s.length() != 0) && (s.charAt(0) != '#')) // Empty lines or lines starting with "#" are ignored
+ {
+ sql = sql +" "+ s;
+ if (s.endsWith(";")) // reached end of sql statement
+ {
+ sql = sql.replace(';', ' '); //remove the semicolon
+ //System.out.println (sql);
+ sqlBuffer.add(sql);
+ sql = "";
+ }
+ }
+ }
+ }
+ catch (IOException ex)
+ {
+ Log.error("TradeBuildDB:parseDDLToBuffer Exeception during open/read of File: " + ddlFile, ex);
+ throw ex;
+ }
+ finally
+ {
+ if (br != null)
+ {
+ try
+ {
+ br.close();
+ } catch(IOException ex)
+ {
+ Log.error("TradeBuildDB:parseDDLToBuffer Failed to close BufferedReader", ex);
+ }
+ }
+ }
+ return sqlBuffer.toArray();
+ }
+
public static void main(String args[]) throws Exception {
new TradeBuildDB();
}
-}
\ No newline at end of file
+}
Modified: geronimo/daytrader/trunk/modules/web/src/main/java/org/apache/geronimo/samples/daytrader/web/TradeConfigServlet.java
URL: http://svn.apache.org/viewvc/geronimo/daytrader/trunk/modules/web/src/main/java/org/apache/geronimo/samples/daytrader/web/TradeConfigServlet.java?view=diff&rev=482366&r1=482365&r2=482366
==============================================================================
--- geronimo/daytrader/trunk/modules/web/src/main/java/org/apache/geronimo/samples/daytrader/web/TradeConfigServlet.java (original)
+++ geronimo/daytrader/trunk/modules/web/src/main/java/org/apache/geronimo/samples/daytrader/web/TradeConfigServlet.java Mon Dec 4 13:32:37 2006
@@ -359,9 +359,15 @@
else if (action.equals("buildDB"))
{
resp.setContentType("text/html");
- new TradeBuildDB(resp.getWriter());
+ new TradeBuildDB(resp.getWriter(), null);
result = "DayTrader Database Built - " + TradeConfig.getMAX_USERS() + "users created";
}
+ else if (action.equals("buildDBTables"))
+ {
+
+ resp.setContentType("text/html");
+ new TradeBuildDB(resp.getWriter(), getServletConfig().getServletContext().getRealPath("/"));
+ }
doConfigDisplay(req, resp, result + "Current DayTrader Configuration:");
}
catch (Exception e)
Modified: geronimo/daytrader/trunk/modules/web/src/main/webapp/configure.html
URL: http://svn.apache.org/viewvc/geronimo/daytrader/trunk/modules/web/src/main/webapp/configure.html?view=diff&rev=482366&r1=482365&r2=482366
==============================================================================
--- geronimo/daytrader/trunk/modules/web/src/main/webapp/configure.html (original)
+++ geronimo/daytrader/trunk/modules/web/src/main/webapp/configure.html Mon Dec 4 13:32:37 2006
@@ -40,6 +40,17 @@
Password for a remote or protected database when using JDBC.</TD>
</TR>
<TR>
+ <TD><A href="config?action=buildDBTables" target="_blank"><FONT
+ face="Times New Roman" size="-1">(Re)-create
+ DayTrader Database Tables and Indexes</FONT></A></TD>
+ <TD>This link is used to (a) initially create or (b) drop and re-create the
+ DayTrader tables. <b>A DayTrader database should exist before doing this action</b>,
+ the existing DayTrader tables, if any, are dropped, then new
+ tables and indexes are created. <b>Please stop and re-start the Daytrader application
+ (or your application server) after this action and then use the "Repopulate
+ DayTrader Database" link below to repopulate the new database tables.</b></TD>
+ </TR>
+ <TR>
<TD><A href="config?action=buildDB" target="_blank"><FONT
face="Times New Roman" size="-1">(Re)-populate
DayTrader Database</FONT></A></TD>
@@ -47,7 +58,8 @@
DayTrader database with fictitious users (uid:0, uid:1, ...) and
stocks (s:0, s:1, ...). First all existing users and stocks are
deleted (if any). The database is then populated with a new set of
- DayTrader users and stocks.</TD>
+ DayTrader users and stocks. This option does not drop and recreate the
+ Daytrader db tables.</TD>
</TR>
<TR>
<TD><A href="scenario" target="_blank"><FONT face="Times New Roman"
Added: geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/db2/Table.ddl
URL: http://svn.apache.org/viewvc/geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/db2/Table.ddl?view=auto&rev=482366
==============================================================================
--- geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/db2/Table.ddl (added)
+++ geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/db2/Table.ddl Mon Dec 4 13:32:37 2006
@@ -0,0 +1,94 @@
+# Each SQL statement in this file should terminate with a semicolon (;)
+# Lines starting with the pound character (#) are considered as comments
+DROP TABLE HOLDINGEJB;
+DROP TABLE ACCOUNTPROFILEEJB;
+DROP TABLE QUOTEEJB;
+DROP TABLE KEYGENEJB;
+DROP TABLE ACCOUNTEJB;
+DROP TABLE ORDEREJB;
+
+CREATE TABLE HOLDINGEJB
+ (PURCHASEPRICE DECIMAL(14, 2),
+ HOLDINGID INTEGER NOT NULL,
+ QUANTITY DOUBLE NOT NULL,
+ PURCHASEDATE TIMESTAMP,
+ ACCOUNT_ACCOUNTID INTEGER,
+ QUOTE_SYMBOL VARCHAR(250));
+
+ALTER TABLE HOLDINGEJB
+ ADD CONSTRAINT PK_HOLDINGEJB PRIMARY KEY (HOLDINGID);
+
+CREATE TABLE ACCOUNTPROFILEEJB
+ (ADDRESS VARCHAR(250),
+ PASSWD VARCHAR(250),
+ USERID VARCHAR(250) NOT NULL,
+ EMAIL VARCHAR(250),
+ CREDITCARD VARCHAR(250),
+ FULLNAME VARCHAR(250));
+
+ALTER TABLE ACCOUNTPROFILEEJB
+ ADD CONSTRAINT PK_ACCOUNTPROFILE2 PRIMARY KEY (USERID);
+
+CREATE TABLE QUOTEEJB
+ (LOW DECIMAL(14, 2),
+ OPEN1 DECIMAL(14, 2),
+ VOLUME DOUBLE NOT NULL,
+ PRICE DECIMAL(14, 2),
+ HIGH DECIMAL(14, 2),
+ COMPANYNAME VARCHAR(250),
+ SYMBOL VARCHAR(250) NOT NULL,
+ CHANGE1 DOUBLE NOT NULL);
+
+ALTER TABLE QUOTEEJB
+ ADD CONSTRAINT PK_QUOTEEJB PRIMARY KEY (SYMBOL);
+
+CREATE TABLE KEYGENEJB
+ (KEYVAL INTEGER NOT NULL,
+ KEYNAME VARCHAR(250) NOT NULL);
+
+ALTER TABLE KEYGENEJB
+ ADD CONSTRAINT PK_KEYGENEJB PRIMARY KEY (KEYNAME);
+
+CREATE TABLE ACCOUNTEJB
+ (CREATIONDATE TIMESTAMP,
+ OPENBALANCE DECIMAL(14, 2),
+ LOGOUTCOUNT INTEGER NOT NULL,
+ BALANCE DECIMAL(14, 2),
+ ACCOUNTID INTEGER NOT NULL,
+ LASTLOGIN TIMESTAMP,
+ LOGINCOUNT INTEGER NOT NULL,
+ PROFILE_USERID VARCHAR(250));
+
+ALTER TABLE ACCOUNTEJB
+ ADD CONSTRAINT PK_ACCOUNTEJB PRIMARY KEY (ACCOUNTID);
+
+CREATE TABLE ORDEREJB
+ (ORDERFEE DECIMAL(14, 2),
+ COMPLETIONDATE TIMESTAMP,
+ ORDERTYPE VARCHAR(250),
+ ORDERSTATUS VARCHAR(250),
+ PRICE DECIMAL(14, 2),
+ QUANTITY DOUBLE NOT NULL,
+ OPENDATE TIMESTAMP,
+ ORDERID INTEGER NOT NULL,
+ ACCOUNT_ACCOUNTID INTEGER,
+ QUOTE_SYMBOL VARCHAR(250),
+ HOLDING_HOLDINGID INTEGER);
+
+ALTER TABLE ORDEREJB
+ ADD CONSTRAINT PK_ORDEREJB PRIMARY KEY (ORDERID);
+
+ALTER TABLE HOLDINGEJB VOLATILE;
+ALTER TABLE ACCOUNTPROFILEEJB VOLATILE;
+ALTER TABLE QUOTEEJB VOLATILE;
+ALTER TABLE KEYGENEJB VOLATILE;
+ALTER TABLE ACCOUNTEJB VOLATILE;
+ALTER TABLE ORDEREJB VOLATILE;
+
+CREATE INDEX a.profile_userid on accountejb(profile_userid);
+CREATE INDEX h.account_accountid on holdingejb(account_accountid);
+CREATE INDEX o.account_accountid on orderejb(account_accountid);
+CREATE INDEX o.holding_holdingid on orderejb(holding_holdingid);
+CREATE INDEX o.closed_orders on orderejb(orderstatus,account_accountid);
+
+
\ No newline at end of file
Added: geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/derby/Table.ddl
URL: http://svn.apache.org/viewvc/geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/derby/Table.ddl?view=auto&rev=482366
==============================================================================
--- geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/derby/Table.ddl (added)
+++ geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/derby/Table.ddl Mon Dec 4 13:32:37 2006
@@ -0,0 +1,86 @@
+# Each SQL statement in this file should terminate with a semicolon (;)
+# Lines starting with the pound character (#) are considered as comments
+DROP TABLE HOLDINGEJB;
+DROP TABLE ACCOUNTPROFILEEJB;
+DROP TABLE QUOTEEJB;
+DROP TABLE KEYGENEJB;
+DROP TABLE ACCOUNTEJB;
+DROP TABLE ORDEREJB;
+
+create table holdingejb
+(purchaseprice decimal(10, 2),
+holdingid integer not null,
+quantity double not null,
+purchasedate timestamp,
+account_accountid integer,
+quote_symbol varchar(250));
+
+alter table holdingejb
+add constraint pk_holdingejb primary key (holdingid);
+
+create table accountprofileejb
+(address varchar(250),
+passwd varchar(250),
+userid varchar(250) not null,
+email varchar(250),
+creditcard varchar(250),
+fullname varchar(250));
+
+alter table accountprofileejb
+add constraint pk_accountprofile2 primary key (userid);
+
+create table quoteejb
+(low decimal(10, 2),
+open1 decimal(10, 2),
+volume double not null,
+price decimal(10, 2),
+high decimal(10, 2),
+companyname varchar(250),
+symbol varchar(250) not null,
+change1 double not null);
+
+alter table quoteejb
+add constraint pk_quoteejb primary key (symbol);
+
+create table keygenejb
+(keyval integer not null,
+keyname varchar(250) not null);
+
+alter table keygenejb
+add constraint pk_keygenejb primary key (keyname);
+
+create table accountejb
+(creationdate timestamp,
+openbalance decimal(10, 2),
+logoutcount integer not null,
+balance decimal(10, 2),
+accountid integer not null,
+lastlogin timestamp,
+logincount integer not null,
+PROFILE_USERID VARCHAR(250));
+
+alter table accountejb
+add constraint pk_accountejb primary key (accountid);
+
+create table orderejb
+(orderfee decimal(10, 2),
+completiondate timestamp,
+ordertype varchar(250),
+orderstatus varchar(250),
+price decimal(10, 2),
+quantity double not null,
+opendate timestamp,
+orderid integer not null,
+account_accountid integer,
+quote_symbol varchar(250),
+holding_holdingid integer);
+
+alter table orderejb
+add constraint pk_orderejb primary key (orderid);
+
+create index profile_userid on accountejb(profile_userid);
+create index account_accountid on holdingejb(account_accountid);
+create index account_accountidt on orderejb(account_accountid);
+create index holding_holdingid on orderejb(holding_holdingid);
+create index orderstatus on orderejb(orderstatus);
+create index ordertype on orderejb(ordertype);
Added: geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/oracle/Table.ddl
URL: http://svn.apache.org/viewvc/geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/oracle/Table.ddl?view=auto&rev=482366
==============================================================================
--- geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/oracle/Table.ddl (added)
+++ geronimo/daytrader/trunk/modules/web/src/main/webapp/dbscripts/oracle/Table.ddl Mon Dec 4 13:32:37 2006
@@ -0,0 +1,89 @@
+# Each SQL statement in this file should terminate with a semicolon (;)
+# Lines starting with the pound character (#) are considered as comments
+DROP TABLE HOLDINGEJB cascade constraints;
+DROP TABLE ACCOUNTPROFILEEJB cascade constraints;
+DROP TABLE QUOTEEJB cascade constraints;
+DROP TABLE KEYGENEJB cascade constraints;
+DROP TABLE ACCOUNTEJB cascade constraints;
+DROP TABLE ORDEREJB cascade constraints;
+
+
+CREATE TABLE HOLDINGEJB
+ (PURCHASEPRICE DECIMAL(14, 2) NULL,
+ HOLDINGID INTEGER NOT NULL,
+ QUANTITY NUMBER NOT NULL,
+ PURCHASEDATE DATE NULL,
+ ACCOUNT_ACCOUNTID INTEGER NULL,
+ QUOTE_SYMBOL VARCHAR2(250) NULL);
+
+ALTER TABLE HOLDINGEJB
+ ADD CONSTRAINT PK_HOLDINGEJB PRIMARY KEY (HOLDINGID);
+
+CREATE TABLE ACCOUNTPROFILEEJB
+ (ADDRESS VARCHAR2(250) NULL,
+ PASSWD VARCHAR2(250) NULL,
+ USERID VARCHAR2(250) NOT NULL,
+ EMAIL VARCHAR2(250) NULL,
+ CREDITCARD VARCHAR2(250) NULL,
+ FULLNAME VARCHAR2(250) NULL);
+
+ALTER TABLE ACCOUNTPROFILEEJB
+ ADD CONSTRAINT PK_ACCOUNTPROFILEEJB PRIMARY KEY (USERID);
+
+CREATE TABLE QUOTEEJB
+ (LOW DECIMAL(14, 2) NULL,
+ OPEN1 DECIMAL(14, 2) NULL,
+ VOLUME NUMBER NOT NULL,
+ PRICE DECIMAL(14, 2) NULL,
+ HIGH DECIMAL(14, 2) NULL,
+ COMPANYNAME VARCHAR2(250) NULL,
+ SYMBOL VARCHAR2(250) NOT NULL,
+ CHANGE1 NUMBER NOT NULL);
+
+ALTER TABLE QUOTEEJB
+ ADD CONSTRAINT PK_QUOTEEJB PRIMARY KEY (SYMBOL);
+
+CREATE TABLE KEYGENEJB
+ (KEYVAL INTEGER NOT NULL,
+ KEYNAME VARCHAR2(250) NOT NULL);
+
+ALTER TABLE KEYGENEJB
+ ADD CONSTRAINT PK_KEYGENEJB PRIMARY KEY (KEYNAME);
+
+CREATE TABLE ACCOUNTEJB
+ (CREATIONDATE DATE NULL,
+ OPENBALANCE DECIMAL(14, 2) NULL,
+ LOGOUTCOUNT INTEGER NOT NULL,
+ BALANCE DECIMAL(14, 2) NULL,
+ ACCOUNTID INTEGER NOT NULL,
+ LASTLOGIN DATE NULL,
+ LOGINCOUNT INTEGER NOT NULL,
+ PROFILE_USERID VARCHAR2(250) NULL);
+
+ALTER TABLE ACCOUNTEJB
+ ADD CONSTRAINT PK_ACCOUNTEJB PRIMARY KEY (ACCOUNTID);
+
+CREATE TABLE ORDEREJB
+ (ORDERFEE DECIMAL(14, 2) NULL,
+ COMPLETIONDATE DATE NULL,
+ ORDERTYPE VARCHAR2(250) NULL,
+ ORDERSTATUS VARCHAR2(250) NULL,
+ PRICE DECIMAL(14, 2) NULL,
+ QUANTITY NUMBER NOT NULL,
+ OPENDATE DATE NULL,
+ ORDERID INTEGER NOT NULL,
+ ACCOUNT_ACCOUNTID INTEGER NULL,
+ QUOTE_SYMBOL VARCHAR2(250) NULL,
+ HOLDING_HOLDINGID INTEGER NULL);
+
+ALTER TABLE ORDEREJB
+ ADD CONSTRAINT PK_ORDEREJB PRIMARY KEY (ORDERID);
+
+CREATE INDEX a_profile_userid on accountejb(profile_userid);
+CREATE INDEX h_account_accountid on holdingejb(account_accountid);
+CREATE INDEX o_account_accountid on orderejb(account_accountid);
+CREATE INDEX o_holding_holdingid on orderejb(holding_holdingid);
+CREATE INDEX o_closed_orders on orderejb(orderstatus,account_accountid);
+
+
+