geronimo-scm mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From hogst...@apache.org
Subject 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...
Date Mon, 04 Dec 2006 21:32:40 GMT
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
+                &nbsp;DayTrader&nbsp;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
                 &nbsp;DayTrader&nbsp;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);
+
+
+  



Mime
View raw message