empire-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From doeb...@apache.org
Subject svn commit: r683186 - in /incubator/empire-db/trunk/core/DBSampleAdv: ./ .settings/ bin/ src/ src/org/ src/org/apache/ src/org/apache/empire/ src/org/apache/empire/samples/ src/org/apache/empire/samples/db/ src/org/apache/empire/samples/db/advanced/
Date Wed, 06 Aug 2008 09:07:17 GMT
Author: doebele
Date: Wed Aug  6 02:07:15 2008
New Revision: 683186

URL: http://svn.apache.org/viewvc?rev=683186&view=rev
Log: (empty)

Added:
    incubator/empire-db/trunk/core/DBSampleAdv/.classpath
    incubator/empire-db/trunk/core/DBSampleAdv/.cvsignore
    incubator/empire-db/trunk/core/DBSampleAdv/.project
    incubator/empire-db/trunk/core/DBSampleAdv/.settings/
    incubator/empire-db/trunk/core/DBSampleAdv/.settings/org.eclipse.jdt.ui.prefs
    incubator/empire-db/trunk/core/DBSampleAdv/.settings/org.eclipse.wst.validation.prefs
    incubator/empire-db/trunk/core/DBSampleAdv/bin/
    incubator/empire-db/trunk/core/DBSampleAdv/classpath_delivery-version
    incubator/empire-db/trunk/core/DBSampleAdv/config.xml
    incubator/empire-db/trunk/core/DBSampleAdv/readme.txt
    incubator/empire-db/trunk/core/DBSampleAdv/src/
    incubator/empire-db/trunk/core/DBSampleAdv/src/org/
    incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/
    incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/
    incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/
    incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/
    incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/
    incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvApp.java
    incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvConfig.java
    incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvDB.java
Modified:
    incubator/empire-db/trunk/core/DBSampleAdv/   (props changed)

Propchange: incubator/empire-db/trunk/core/DBSampleAdv/
------------------------------------------------------------------------------
--- svn:ignore (added)
+++ svn:ignore Wed Aug  6 02:07:15 2008
@@ -0,0 +1 @@
+hsqldb

Added: incubator/empire-db/trunk/core/DBSampleAdv/.classpath
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/DBSampleAdv/.classpath?rev=683186&view=auto
==============================================================================
--- incubator/empire-db/trunk/core/DBSampleAdv/.classpath (added)
+++ incubator/empire-db/trunk/core/DBSampleAdv/.classpath Wed Aug  6 02:07:15 2008
@@ -0,0 +1,8 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<classpath>
+	<classpathentry kind="src" path="src"/>
+	<classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER"/>
+	<classpathentry combineaccessrules="false" kind="src" path="/Empire-db"/>
+	<classpathentry combineaccessrules="false" kind="src" path="/JDBCDrivers"/>
+	<classpathentry kind="output" path="bin"/>
+</classpath>

Added: incubator/empire-db/trunk/core/DBSampleAdv/.cvsignore
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/DBSampleAdv/.cvsignore?rev=683186&view=auto
==============================================================================
--- incubator/empire-db/trunk/core/DBSampleAdv/.cvsignore (added)
+++ incubator/empire-db/trunk/core/DBSampleAdv/.cvsignore Wed Aug  6 02:07:15 2008
@@ -0,0 +1 @@
+hsqldb

Added: incubator/empire-db/trunk/core/DBSampleAdv/.project
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/DBSampleAdv/.project?rev=683186&view=auto
==============================================================================
--- incubator/empire-db/trunk/core/DBSampleAdv/.project (added)
+++ incubator/empire-db/trunk/core/DBSampleAdv/.project Wed Aug  6 02:07:15 2008
@@ -0,0 +1,17 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<projectDescription>
+	<name>DBSampleAdv</name>
+	<comment></comment>
+	<projects>
+	</projects>
+	<buildSpec>
+		<buildCommand>
+			<name>org.eclipse.jdt.core.javabuilder</name>
+			<arguments>
+			</arguments>
+		</buildCommand>
+	</buildSpec>
+	<natures>
+		<nature>org.eclipse.jdt.core.javanature</nature>
+	</natures>
+</projectDescription>

Added: incubator/empire-db/trunk/core/DBSampleAdv/.settings/org.eclipse.jdt.ui.prefs
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/DBSampleAdv/.settings/org.eclipse.jdt.ui.prefs?rev=683186&view=auto
==============================================================================
--- incubator/empire-db/trunk/core/DBSampleAdv/.settings/org.eclipse.jdt.ui.prefs (added)
+++ incubator/empire-db/trunk/core/DBSampleAdv/.settings/org.eclipse.jdt.ui.prefs Wed Aug  6 02:07:15 2008
@@ -0,0 +1,3 @@
+#Wed May 02 16:14:09 CEST 2007
+eclipse.preferences.version=1
+org.eclipse.jdt.ui.text.custom_code_templates=<?xml version\="1.0" encoding\="UTF-8"?><templates/>

Added: incubator/empire-db/trunk/core/DBSampleAdv/.settings/org.eclipse.wst.validation.prefs
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/DBSampleAdv/.settings/org.eclipse.wst.validation.prefs?rev=683186&view=auto
==============================================================================
--- incubator/empire-db/trunk/core/DBSampleAdv/.settings/org.eclipse.wst.validation.prefs (added)
+++ incubator/empire-db/trunk/core/DBSampleAdv/.settings/org.eclipse.wst.validation.prefs Wed Aug  6 02:07:15 2008
@@ -0,0 +1,6 @@
+#Tue Nov 20 11:27:45 CET 2007
+DELEGATES_PREFERENCE=delegateValidatorListorg.eclipse.wst.wsdl.validation.internal.eclipse.WSDLDelegatingValidator\=org.eclipse.wst.wsdl.validation.internal.eclipse.Validator;org.eclipse.wst.xsd.core.internal.validation.eclipse.XSDDelegatingValidator\=org.eclipse.wst.xsd.core.internal.validation.eclipse.Validator;
+USER_BUILD_PREFERENCE=enabledBuildValidatorListorg.eclipse.wst.wsi.ui.internal.WSIMessageValidator;org.eclipse.wst.html.internal.validation.HTMLValidator;org.eclipse.wst.xsd.core.internal.validation.eclipse.XSDDelegatingValidator;org.eclipse.wst.xml.core.internal.validation.eclipse.Validator;org.eclipse.wst.dtd.core.internal.validation.eclipse.Validator;
+USER_MANUAL_PREFERENCE=enabledManualValidatorListorg.eclipse.wst.wsi.ui.internal.WSIMessageValidator;org.eclipse.wst.html.internal.validation.HTMLValidator;org.eclipse.wst.xsd.core.internal.validation.eclipse.XSDDelegatingValidator;org.eclipse.wst.xml.core.internal.validation.eclipse.Validator;org.eclipse.wst.dtd.core.internal.validation.eclipse.Validator;
+USER_PREFERENCE=overrideGlobalPreferencesfalse
+eclipse.preferences.version=1

Added: incubator/empire-db/trunk/core/DBSampleAdv/classpath_delivery-version
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/DBSampleAdv/classpath_delivery-version?rev=683186&view=auto
==============================================================================
--- incubator/empire-db/trunk/core/DBSampleAdv/classpath_delivery-version (added)
+++ incubator/empire-db/trunk/core/DBSampleAdv/classpath_delivery-version Wed Aug  6 02:07:15 2008
@@ -0,0 +1,12 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<classpath>
+	<classpathentry kind="src" path="src"/>
+	<classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER"/>
+	<classpathentry kind="lib" path="lib/hsqldb.jar"/>
+	<classpathentry kind="lib" path="lib/log4j-1.2.14.jar"/>
+	<classpathentry kind="lib" path="lib/empire-db-2.0.1.jar"/>
+	<classpathentry kind="lib" path="lib/commons-beanutils-1.7.0.jar"/>
+	<classpathentry kind="lib" path="lib/commons-collections-2.1.1.jar"/>
+	<classpathentry kind="lib" path="lib/commons-logging-1.0.4.jar"/>
+	<classpathentry kind="output" path="bin"/>
+</classpath>
\ No newline at end of file

Added: incubator/empire-db/trunk/core/DBSampleAdv/config.xml
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/DBSampleAdv/config.xml?rev=683186&view=auto
==============================================================================
--- incubator/empire-db/trunk/core/DBSampleAdv/config.xml (added)
+++ incubator/empire-db/trunk/core/DBSampleAdv/config.xml Wed Aug  6 02:07:15 2008
@@ -0,0 +1,100 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<config>
+
+	<properties>
+		<!-- provider name must match the property-section containing the connection data -->
+		<databaseProvider>hsqldb</databaseProvider>
+	</properties>
+
+	<properties-hsqldb>
+		<!-- JDBC properties for a HSQLDB Database connection -->
+		<!-- Required jar file: hsqldb.jar -->
+		<jdbcClass>org.hsqldb.jdbcDriver</jdbcClass>
+		<jdbcURL>jdbc:hsqldb:file:hsqldb/sample;shutdown=true</jdbcURL>
+		<jdbcUser>sa</jdbcUser>
+		<jdbcPwd></jdbcPwd>
+		<schemaName>DBSAMPLEADV</schemaName>
+	</properties-hsqldb>
+
+	<properties-mysql>
+		<!-- JDBC properties for a MySQL Database connection -->
+		<!-- Required jar file: mysql-connector-java-5.1.6-bin.jar -->
+		<jdbcClass>com.mysql.jdbc.Driver</jdbcClass>
+		<jdbcURL>jdbc:mysql://localhost</jdbcURL>
+		<jdbcUser>root</jdbcUser>
+		<jdbcPwd>esteam</jdbcPwd>
+		<schemaName>DBSAMPLEADV</schemaName>
+	</properties-mysql>
+
+	<properties-oracle>
+		<!-- JDBC properties for an Oracle Database connection -->
+		<!-- Required jar file: ojdbc14.jar -->
+		<jdbcClass>oracle.jdbc.driver.OracleDriver</jdbcClass>
+		<jdbcURL>jdbc:oracle:thin:@192.168.0.2:1521:ora10</jdbcURL>
+		<jdbcUser>DBSAMPLEADV</jdbcUser>
+		<jdbcPwd>DBSAMPLEADV</jdbcPwd>
+		<schemaName>DBSAMPLEADV</schemaName>
+	</properties-oracle>
+
+	<properties-sqlserver>
+		<!-- JDBC properties for a Microsoft SQL-Server Database connection -->
+		<!-- Required jar file: sqljdbc.jar -->
+		<jdbcClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbcClass>
+		<jdbcURL>jdbc:sqlserver://192.168.0.2:1433</jdbcURL>
+		<jdbcUser>sa</jdbcUser>
+		<jdbcPwd>esteam</jdbcPwd>
+		<schemaName>DBSAMPLEADV</schemaName>
+	</properties-sqlserver>
+	
+	<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
+
+		<appender name="default" class="org.apache.log4j.ConsoleAppender">
+			<!-- layout class="org.apache.log4j.TTCCLayout"/ -->
+			<layout class="org.apache.log4j.PatternLayout">
+				<!-- param name="ConversionPattern" value="NSB(%c) %-5p %m	at %l%n"/ -->
+				<param name="ConversionPattern" value="%-5p [%d{yyyy/MM/dd HH:mm}]: %m		at %l %n"/>
+			</layout>
+		</appender>
+	
+		<!-- log detail configuration -->
+
+		<logger name="org.apache.empire.xml" additivity="false">
+			<level value="info"/>
+			<appender-ref ref="default"/>
+		</logger>
+		
+		<logger name="org.apache.empire.commons" additivity="false">
+			<level value="warn"/>
+			<appender-ref ref="default"/>
+		</logger>
+	
+		<logger name="org.apache.empire.db" additivity="false">
+			<level value="warn"/>
+			<appender-ref ref="default"/>
+		</logger>
+
+		<!-- Set this level to "debug" to log all SQL-Statements -->		
+		<logger name="org.apache.empire.db.DBDatabase" additivity="false">
+			<level value="info"/>
+			<appender-ref ref="default"/>
+		</logger>
+
+		<!-- Set this level to "debug" to log all DDL-Statements -->		
+		<logger name="org.apache.empire.db.DBSQLScript" additivity="false">
+			<level value="info"/>
+			<appender-ref ref="default"/>
+		</logger>
+		
+		<logger name="org.apache.empire.samples.db.advanced" additivity="false">
+			<level value="debug"/>
+			<appender-ref ref="default"/>
+		</logger>
+	
+		<root>
+			<priority value="info"/>
+			<appender-ref ref="default"/>
+		</root>
+
+	</log4j:configuration>
+	
+</config>

Added: incubator/empire-db/trunk/core/DBSampleAdv/readme.txt
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/DBSampleAdv/readme.txt?rev=683186&view=auto
==============================================================================
--- incubator/empire-db/trunk/core/DBSampleAdv/readme.txt (added)
+++ incubator/empire-db/trunk/core/DBSampleAdv/readme.txt Wed Aug  6 02:07:15 2008
@@ -0,0 +1,10 @@
+In order to run / debug the Empire-db SampleApp please do the following:
+
+1. In Eclipse IDE use File -> Import -> Existing Projects into Workspace to import the Sample Project
+
+2. Open the file org.apache.empire.samples.db.SampleApp.java in the src folder and set a breakpoint in first code line of the main() function.
+ 
+3. Then right-click on the DBSample Project Node and select Debug-As -> 3 Java Application and select the "SampleApp" class
+
+The Sample uses a HSQLDB database. If you want to use another database system you will have to add the driver jar file to the the classpath
+and change the config.xml file accordingly.

Added: incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvApp.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvApp.java?rev=683186&view=auto
==============================================================================
--- incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvApp.java (added)
+++ incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvApp.java Wed Aug  6 02:07:15 2008
@@ -0,0 +1,595 @@
+package org.apache.empire.samples.db.advanced;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.util.Date;
+import java.util.HashMap;
+import java.util.logging.Logger;
+
+import org.apache.empire.commons.DateUtils;
+import org.apache.empire.commons.ErrorObject;
+import org.apache.empire.commons.Options;
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBCmdType;
+import org.apache.empire.db.DBColumnExpr;
+import org.apache.empire.db.DBCommand;
+import org.apache.empire.db.DBDatabaseDriver;
+import org.apache.empire.db.DBQuery;
+import org.apache.empire.db.DBReader;
+import org.apache.empire.db.DBRecord;
+import org.apache.empire.db.DBSQLScript;
+import org.apache.empire.db.DBTableColumn;
+import org.apache.empire.db.hsql.DBDatabaseDriverHSql;
+import org.apache.empire.db.mysql.DBDatabaseDriverMySQL;
+import org.apache.empire.db.oracle.DBDatabaseDriverOracle;
+import org.apache.empire.db.sqlserver.DBDatabaseDriverMSSQL;
+
+
+public class SampleAdvApp 
+{
+    public static Logger logger = Logger.getLogger(SampleAdvApp.class.getName());
+
+    private static final SampleAdvDB db = new SampleAdvDB();
+
+    private static SampleAdvConfig config = new SampleAdvConfig();
+    
+    // Shortcuts
+    private static SampleAdvDB.Employees T_EMP = db.T_EMPLOYEES;
+    private static SampleAdvDB.Departments T_DEP = db.T_DEPARTMENTS;
+    private static SampleAdvDB.EmployeeDepartmentHistory T_EDH = db.T_EMP_DEP_HIST;
+
+    /**
+     * <PRE>
+     * This is the entry point of the Empire-DB Sample Application
+     * Please check the config.xml configuration file for Database and Connection settings.
+     * </PRE>
+     */
+    public static void main(String[] args)
+    {
+        try
+        {
+
+            // Init Configuration
+            config.init((args.length > 0 ? args[0] : "config.xml" ));
+
+            // Enable Exceptions
+            ErrorObject.setExceptionsEnabled(true);
+
+            System.out.println("Running DB Sample Advanced...");
+
+            // STEP 1: Get a JDBC Connection
+            System.out.println("*** Step 1: getJDBCConnection() ***");
+            Connection conn = getJDBCConnection();
+
+            // STEP 2: Choose a driver
+            System.out.println("*** Step 2: getDatabaseProvider() ***");
+            DBDatabaseDriver driver = getDatabaseDriver(config.getDatabaseProvider());
+
+            // STEP 3: Open Database (and create if not existing)
+            System.out.println("*** Step 3: openDatabase() ***");
+            try {
+                db.open(driver, conn);
+                databaseExists(conn);
+                System.out.println("*** Database already exists. Skipping Step4 ***");
+                
+            } catch(Exception e) {
+                // STEP 4: Create Database
+                System.out.println("*** Step 4: createDDL() ***");
+                createDatabase(driver, conn);
+                // Open again
+                if (db.isOpen()==false)
+                    db.open(driver, conn);
+            }
+
+            // STEP 5: Clear Database (Delete all records)
+            System.out.println("*** Step 5: clearDatabase() ***");
+            clearDatabase(conn);
+
+            // STEP 6: Insert Records
+            // Insert Departments
+            System.out.println("*** Step 6: inserting departments, employees and employee_department_history records ***");
+            int idDevDep = insertDepartment(conn, "Development", "ITTK");
+            int idProdDep = insertDepartment(conn, "Production", "ITTK");
+            int idSalDep = insertDepartment(conn, "Sales", "ITTK");
+
+            // Insert Employees
+            int idPers1 = insertEmployee(conn, "Peter", "Sharp", "M");
+            int idPers2 = insertEmployee(conn, "Fred", "Bloggs", "M");
+            int idPers3 = insertEmployee(conn, "Emma", "White", "F");
+            
+            insertEmpDepHistory(conn, idPers1,  idDevDep,  DateUtils.getDate(2005, 11,  1));            
+            insertEmpDepHistory(conn, idPers1,  idProdDep, DateUtils.getDate(2006,  8,  1));           
+            insertEmpDepHistory(conn, idPers1,  idSalDep,  DateUtils.getDate(2007,  4, 15));           
+
+            insertEmpDepHistory(conn, idPers2,  idSalDep,  DateUtils.getDate(2004,  2,  1));            
+            insertEmpDepHistory(conn, idPers2,  idDevDep,  DateUtils.getDate(2006, 10, 15));           
+
+            insertEmpDepHistory(conn, idPers3,  idDevDep,  DateUtils.getDate(2004,  8, 15));            
+            insertEmpDepHistory(conn, idPers3,  idSalDep,  DateUtils.getDate(2005,  7,  1));           
+            insertEmpDepHistory(conn, idPers3,  idProdDep, DateUtils.getDate(2006,  6, 15));           
+            
+            // commit
+            db.commit(conn);
+
+            // STEP 7: read from Employee_Info_View
+            if (true) {
+                System.out.println("*** read from EMPLOYEE_INFO_VIEW ***");
+                DBCommand cmd = db.createCommand();
+                cmd.select (db.V_EMPLOYEE_INFO.getColumns());
+                cmd.orderBy(db.V_EMPLOYEE_INFO.C_NAME_AND_DEP);
+                printQueryResults(cmd, conn);
+            }
+            // STEP 8: bulkReadRecords
+            if (true) {
+                System.out.println("*** bulkReadRecords: reads employee records into a hashmap, reads employee from hashmap and updates employee ***");
+                HashMap<Integer, DBRecord> employeeMap = bulkReadRecords(conn);
+                DBRecord rec = employeeMap.get(idPers2);
+                rec.setValue(db.T_EMPLOYEES.C_SALUTATION, "Mr.");
+                rec.update(conn);
+            }
+            // STEP 9: bulkProcessRecords
+            if (true) {
+                System.out.println("*** bulkProcessRecords: creates a checksum for every employee in the employees table ***");
+                bulkProcessRecords(conn);
+            }
+            // STEP 10: querySample
+            if (true) {
+                System.out.println("*** querySample: shows how to use DBQuery class for subqueries and multi table records ***");
+                querySample(conn, idPers2);
+            }
+            // STEP 11: ddlSample
+            if (true) {
+                System.out.println("*** ddlSample: shows how to add a column at runtime and update a record with the added column ***");
+                ddlSample(conn, idPers2);                
+            }
+
+            // Done
+            System.out.println("DB Sample finished successfully.");
+
+        } catch (Exception e)
+        {
+            // Error
+            System.out.println(e.toString());
+            e.printStackTrace();
+        }
+
+    }
+
+    /**
+     * <PRE>
+     * Opens and returns a JDBC-Connection.
+     * JDBC url, user and password for the connection are obained from the SampleConfig bean
+     * Please use the config.xml file to change connection params.
+     * </PRE>
+     */
+    private static Connection getJDBCConnection()
+    {
+        // Establish a new database connection
+        Connection conn = null;
+        logger.info("Connecting to Database'" + config.getJdbcURL() + "' / User=" + config.getJdbcUser());
+        try
+        {
+            // Connect to the databse
+            Class.forName(config.getJdbcClass()).newInstance();
+            conn = DriverManager.getConnection(config.getJdbcURL(), config.getJdbcUser(), config.getJdbcPwd());
+            logger.info("Connected successfully");
+            // set the AutoCommit to false this session. You must commit
+            // explicitly now
+            conn.setAutoCommit(false);
+            logger.info("AutoCommit is " + conn.getAutoCommit());
+
+        } catch (Exception e)
+        {
+            logger.severe("Failed to connect directly to '" + config.getJdbcURL() + "' / User=" + config.getJdbcUser());
+            logger.severe(e.toString());
+            throw new RuntimeException(e);
+        }
+        return conn;
+    }
+
+    /**
+     * <PRE>
+     * Returns the correspondig DatabaseDriver for a given database provider / vendor
+     * Valid Providers are "oracle", "sqlserver" and "hsqldb".
+     * </PRE>
+     */
+    private static DBDatabaseDriver getDatabaseDriver(String provider)
+    {
+        if (provider.equalsIgnoreCase("mysql"))
+        {
+            DBDatabaseDriverMySQL driver = new DBDatabaseDriverMySQL();
+            // Set Driver specific properties (if any)
+            driver.setDatabaseName(config.getSchemaName());
+            return driver;
+        }
+        else if (provider.equalsIgnoreCase("oracle"))
+        {
+            DBDatabaseDriverOracle driver = new DBDatabaseDriverOracle();
+            // Set Driver specific properties (if any)
+            return driver;
+        }
+        else if (provider.equalsIgnoreCase("sqlserver"))
+        {
+            DBDatabaseDriverMSSQL driver = new DBDatabaseDriverMSSQL();
+            // Set Driver specific properties (if any)
+            driver.setDatabaseName(config.getSchemaName());
+            return driver;
+        }
+        else if (provider.equalsIgnoreCase("hsqldb"))
+        {
+            DBDatabaseDriverHSql driver = new DBDatabaseDriverHSql();
+            // Set Driver specific properties (if any)
+            return driver;
+        }
+        else
+        {   // Unknown Provider
+            throw new RuntimeException("Unknown Database Provider " + provider);
+        }
+    }
+
+    /**
+     * <PRE>
+     * Checks whether the database exists or not by executing
+     *     select count(*) from DEPARTMENTS
+     * If the Departments table does not exist the querySingleInt() function return -1 for failure.
+     * Please note that in this case an error will appear in the log wich can be ingored.
+     * </PRE>
+     */
+    private static boolean databaseExists(Connection conn)
+    {
+        // Check wether DB exists
+        DBCommand cmd = db.createCommand();
+        cmd.select(T_DEP.count());
+        // Check using "select count(*) from DEPARTMENTS"
+        System.out.println("Checking whether table DEPARTMENTS exists (SQLException will be logged if not - please ignore) ...");
+        return (db.querySingleInt(cmd.getSelect(), -1, conn) >= 0);
+    }
+
+    /**
+     * <PRE>
+     * Creates a DDL Script for entire SampleDB Database and executes it line by line.
+     * Please make sure you uses the correct DatabaseDriver for your target dbms.
+     * </PRE>
+     */
+    private static void createDatabase(DBDatabaseDriver driver, Connection conn)
+    {
+        // create DLL for Database Definition
+        DBSQLScript script = new DBSQLScript();
+        db.getCreateDDLScript(driver, script);
+        // Show DLL Statement
+        System.out.println(script.toString());
+        // Execute Script
+        script.run(driver, conn, false);
+        // Commit
+        db.commit(conn);
+    }
+
+    /**
+     * <PRE>
+     * Empties all Tables.
+     * </PRE>
+     */
+    private static void clearDatabase(Connection conn)
+    {
+        DBCommand cmd = db.createCommand();
+        // Delete all Employee Department History records
+        db.executeSQL(cmd.getDelete(T_EDH), conn);
+        // Delete all Employees (no constraints)
+        db.executeSQL(cmd.getDelete(T_EMP), conn);
+        // Delete all Departments (no constraints)
+        db.executeSQL(cmd.getDelete(T_DEP), conn);
+    }
+
+    /**
+     * <PRE>
+     * Insert a Department into the Departments table.
+     * </PRE>
+     */
+    private static int insertDepartment(Connection conn, String departmentName, String businessUnit)
+    {
+        // Insert a Department
+        DBRecord rec = new DBRecord();
+        rec.create(T_DEP);
+        rec.setValue(T_DEP.C_NAME, departmentName);
+        rec.setValue(T_DEP.C_BUSINESS_UNIT, businessUnit);
+        if (!rec.update(conn))
+        {
+            logger.severe(rec.getErrorMessage());
+            return 0;
+        }
+        // Return Department ID
+        return rec.getInt(T_DEP.C_DEPARTMENT_ID);
+    }
+
+    /**
+     * <PRE>
+     * Inserts an Employee into the Employees table.
+     * </PRE>
+     */
+    private static int insertEmployee(Connection conn, String firstName, String lastName, String gender)
+    {
+        // Insert an Employee
+        DBRecord rec = new DBRecord();
+        rec.create(T_EMP);
+        rec.setValue(T_EMP.C_FIRSTNAME, firstName);
+        rec.setValue(T_EMP.C_LASTNAME, lastName);
+        rec.setValue(T_EMP.C_GENDER, gender);
+        if (!rec.update(conn))
+        {
+            logger.severe(rec.getErrorMessage());
+            return 0;
+        }
+        // Return Employee ID
+        return rec.getInt(T_EMP.C_EMPLOYEE_ID);
+    }
+
+    /**
+     * <PRE>
+     * Inserts an Employee into the Employees table.
+     * </PRE>
+     */
+    private static void insertEmpDepHistory(Connection conn, int employeeId, int departmentId, Date dateFrom)
+    {
+        // Insert an Employee
+        DBRecord rec = new DBRecord();
+        rec.create(T_EDH);
+        rec.setValue(T_EDH.C_EMPLOYEE_ID, employeeId);
+        rec.setValue(T_EDH.C_DEPARTMENT_ID, departmentId);
+        rec.setValue(T_EDH.C_DATE_FROM, dateFrom);
+        if (!rec.update(conn))
+        {
+            logger.severe(rec.getErrorMessage());
+        }
+    }
+
+    /**
+     * This function performs a query to select non-retired employees,<BR>
+     * then it calculates a checksum for every record<BR>
+     * and writes that checksum back to the database.<BR>
+     * <P>
+     * @param conn a connection to the database
+     */
+    private static void bulkProcessRecords(Connection conn)
+    {
+        // Define the query
+        DBCommand cmd = db.createCommand();
+        // Define shortcuts for tables used - not necessary but convenient
+        SampleAdvDB.Employees EMP = T_EMP;
+        // Select requried columns
+        cmd.select(T_EMP.getColumns());
+        // Set Constraints
+        cmd.where(T_EMP.C_RETIRED.is(false));
+
+        // Query Records and print output
+        DBReader reader = new DBReader();
+        try
+        {
+            // Open Reader
+            System.out.println("Running Query:");
+            System.out.println(cmd.getSelect());
+            if (reader.open(cmd, conn) == false)
+                throw new RuntimeException(reader.getErrorMessage());
+            // Print output
+            DBRecord record = new DBRecord();
+            while (reader.moveNext())
+            {
+                // Calculate sum
+                int sum = 0;
+                for (int i=0; i<reader.getFieldCount(); i++)
+                    sum += calcCharSum(reader.getString(i));
+                // Init updateable record
+                reader.initRecord(EMP, record);
+                // reader
+                record.setValue(T_EMP.C_CHECKSUM, sum);
+                record.update(conn);
+            }
+            // Done
+            db.commit(conn);
+
+        } finally
+        {
+            // always close Reader
+            reader.close();
+        }
+    }
+    
+	private static int calcCharSum(String value)
+    {
+        int sum = 0;
+        if (value!=null)
+        {	// calcCharSum
+            int len = value.length();
+            for (int i=0; i<len; i++)
+                sum += value.charAt(i);
+        }
+        return sum;    
+    }
+    
+    private static HashMap<Integer, DBRecord> bulkReadRecords(Connection conn)
+    {
+        // Define the query
+        DBCommand cmd = db.createCommand();
+        // Select requried columns
+        cmd.select(T_EMP.getColumns());
+        // Set Constraints
+        cmd.where(T_EMP.C_RETIRED.is(false));
+
+        // Query Records and print output
+        DBReader reader = new DBReader();
+        try
+        {   // Open Reader
+            System.out.println("Running Query:");
+            System.out.println(cmd.getSelect());
+            if (reader.open(cmd, conn) == false)
+                throw new RuntimeException(reader.getErrorMessage());
+            // Print output
+            HashMap<Integer, DBRecord> employeeMap = new HashMap<Integer, DBRecord>();
+            while (reader.moveNext())
+            {
+                DBRecord rec = new DBRecord();
+                reader.initRecord(T_EMP, rec);
+                employeeMap.put(reader.getInt(T_EMP.C_EMPLOYEE_ID), rec);
+            }
+            return employeeMap;
+
+        } finally
+        {   // always close Reader
+            reader.close();
+        }
+    }
+    
+    /**
+     * This method demonstrates how to add, modify and delete a database column.<BR>
+     * This function demonstrates the use of the {@link DBDatabaseDriver#getDDLScript(org.apache.empire.db.DBCmdType, org.apache.empire.db.DBObject, DBSQLScript)}<BR>
+     * 
+     */
+    private static void ddlSample(Connection conn, int idTestPerson)
+    {
+        // Enable Column default for the database 
+        // This is needed for adding required fields to non-empty tables
+        db.getDriver().setDDLColumnDefaults(true);
+
+        // First, add a new column to the Table object
+        DBTableColumn C_FOO = db.T_EMPLOYEES.addColumn("FOO", DataType.TEXT, 20, false);
+
+        // Now create the corresponding DDL statement 
+        System.out.println("Creating new column named FOO as varchar(20) for the EMPLOYEES table:");
+        DBSQLScript script = new DBSQLScript();
+        db.getDriver().getDDLScript(DBCmdType.CREATE, C_FOO, script);
+        script.run(db.getDriver(), conn, false);
+        
+        // Now load a record from that table and set the value for foo
+        System.out.println("Changing the value for the FOO field of a particular employee:");
+        DBRecord rec = new DBRecord();
+        rec.read(db.T_EMPLOYEES, idTestPerson, conn);
+        rec.setValue(C_FOO, "Hello World");
+        rec.update(conn);
+        
+        // Now extend the size of the field from 20 to 40 characters
+        System.out.println("Extending size of column FOO to 40 characters:");
+        C_FOO.setSize(40); 
+        script.clear();
+        db.getDriver().getDDLScript(DBCmdType.ALTER, C_FOO, script);
+        script.run(db.getDriver(), conn, false);
+
+        // Now set a longer value for the record
+        System.out.println("Changing the value for the FOO field for the above employee to a longer string:");
+        rec.setValue(C_FOO, "This is a very long field value!");
+        rec.update(conn);
+
+        // Finally, drop the column again
+        System.out.println("Dropping the FOO column from the employee table:");
+        script.clear();
+        db.getDriver().getDDLScript(DBCmdType.DROP, C_FOO, script);
+        script.run(db.getDriver(), conn, false);
+    }
+
+    /**
+     * This function demostrates the use of the DBQuery object.<BR>
+     * First a DBQuery is used to define a subquery that gets the latest employee department history record.<BR>
+     * This subquery is then used inside another query to list all employees with the current department.<BR>
+     * <P>
+     * In the second part, another DBQuery object is used to read a record that holds information from both 
+     * the employee and department table. When the information is modified and the record's update method is
+     * called, then both tables are updated.
+     * <P>
+     * @param conn
+     * @param employeeId
+     */
+    private static void querySample(Connection conn, int employeeId)
+    {
+        // Define the sub query
+        DBCommand subCmd = db.createCommand();
+        DBColumnExpr MAX_DATE_FROM = T_EDH.C_DATE_FROM.max().as(T_EDH.C_DATE_FROM);
+        subCmd.select(T_EDH.C_EMPLOYEE_ID, MAX_DATE_FROM);
+        subCmd.groupBy(T_EDH.C_EMPLOYEE_ID);
+        DBQuery Q_MAX_DATE = new DBQuery(subCmd);
+
+        // Define the query
+        DBCommand cmd = db.createCommand();
+        // Select requried columns
+        cmd.select(T_EMP.C_EMPLOYEE_ID, T_EMP.C_FULLNAME);
+        cmd.select(T_EMP.C_GENDER, T_EMP.C_PHONE_NUMBER);
+        cmd.select(T_DEP.C_DEPARTMENT_ID, T_DEP.C_NAME, T_DEP.C_BUSINESS_UNIT);
+        cmd.select(T_EMP.C_UPDATE_TIMESTAMP, T_DEP.C_UPDATE_TIMESTAMP);
+        // Set Joins
+        cmd.join(T_EDH.C_EMPLOYEE_ID, Q_MAX_DATE.findQueryColumn(T_EDH.C_EMPLOYEE_ID))
+          .where(T_EDH.C_DATE_FROM.is(Q_MAX_DATE.findQueryColumn(MAX_DATE_FROM)));
+        cmd.join(T_EMP.C_EMPLOYEE_ID, T_EDH.C_EMPLOYEE_ID);
+        cmd.join(T_DEP.C_DEPARTMENT_ID, T_EDH.C_DEPARTMENT_ID);
+        // Set Constraints
+        cmd.where(T_EMP.C_RETIRED.is(false));
+        // Set Order
+        cmd.orderBy(T_EMP.C_LASTNAME);
+        cmd.orderBy(T_EMP.C_FIRSTNAME);
+
+        // Query Records and print output
+        printQueryResults(cmd, conn);
+        
+        // Define an updateable query
+        DBQuery Q_EMP_DEP = new DBQuery(cmd, T_EMP.C_EMPLOYEE_ID);
+        DBRecord rec = new DBRecord();
+        rec.read(Q_EMP_DEP, employeeId, conn);
+        // Modify and Update fields from both Employee and Department
+        rec.setValue(T_EMP.C_PHONE_NUMBER, "0815-4711");
+        rec.setValue(T_DEP.C_BUSINESS_UNIT, "AUTO");
+        rec.update(conn);
+        // Sucessfully updated
+        System.out.println("The employee has been sucessfully updated");
+    }    
+
+    /**
+     * This functions prints the results of a query which is performed using the supplied command
+     * @param cmd the command to be used for performing the query
+     * @param conn the connection
+     */
+    private static void printQueryResults(DBCommand cmd, Connection conn)
+    {
+        // Query Records and print output
+        DBReader reader = new DBReader();
+        try
+        {   // Open Reader
+            System.out.println("Running Query:");
+            System.out.println(cmd.getSelect());
+            if (reader.open(cmd, conn) == false)
+                throw new RuntimeException(reader.getErrorMessage());
+            // Print column titles 
+            System.out.println("---------------------------------");
+            int count = reader.getFieldCount();
+            for (int i=0; i<count; i++)
+            {   // Print all column names
+                DBColumnExpr c = reader.getColumnExpr(i);
+                if (i>0)
+                    System.out.print("\t");
+                System.out.print(c.getName());
+            }
+            // Print output
+            System.out.println("");
+            // Text-Output by iterating through all records.
+            while (reader.moveNext())
+            {
+                for (int i=0; i<count; i++)
+                {   // Print all field values
+                    if (i>0)
+                        System.out.print("\t");
+                    // Check if conversion is necessary
+                    DBColumnExpr c = reader.getColumnExpr(i);
+                    Options opt = c.getOptions();
+                    if (opt!=null)
+                    {   // Option Lookup
+                        System.out.print(opt.get(reader.getValue(i)));
+                    }
+                    else
+                    {   // Print String
+                        System.out.print(reader.getString(i));
+                    }
+                }
+                System.out.println("");
+            }
+
+        } finally
+        {   // always close Reader
+            reader.close();
+        }
+    }
+}

Added: incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvConfig.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvConfig.java?rev=683186&view=auto
==============================================================================
--- incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvConfig.java (added)
+++ incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvConfig.java Wed Aug  6 02:07:15 2008
@@ -0,0 +1,104 @@
+package org.apache.empire.samples.db.advanced;
+
+import org.apache.empire.xml.XMLConfiguration;
+
+/**
+ * <PRE>
+ * The SampleConfig class provides access to configuration settings.
+ * The configruation will be read from a xml configuration file (usually config.xml) 
+ * Thus the default values here will be overridden. 
+ * </PRE>
+ */
+public class SampleAdvConfig extends XMLConfiguration
+{
+
+    private String databaseProvider = "hsqldb";
+
+    private String jdbcClass = "org.hsqldb.jdbcDriver";
+
+    private String jdbcURL = "jdbc:hsqldb:file:hsqldb/sample;shutdown=true";
+
+    private String jdbcUser = "jdbc:hsqldb:file:hsqldb/sample;shutdown=true";
+
+    private String jdbcPwd = "";
+
+    private String schemaName = "DBSAMPLE";
+
+    /**
+     * Initialize the configuration.
+     */
+    public boolean init(String filename)
+    {
+        // Read the properties file
+        if (super.init(filename, false, true) == false)
+            return false;
+        // Done
+        if (readProperties(this, "properties")==false)
+            return false;
+        // Reader Provider Properties
+        return readProperties(this, "properties-" + databaseProvider);
+    }
+
+    public String getDatabaseProvider()
+    {
+        return databaseProvider;
+    }
+
+    public String getJdbcClass()
+    {
+        return jdbcClass;
+    }
+
+    public void setJdbcClass(String jdbcClass)
+    {
+        this.jdbcClass = jdbcClass;
+    }
+
+    public String getJdbcPwd()
+    {
+        return jdbcPwd;
+    }
+
+    public void setJdbcPwd(String jdbcPwd)
+    {
+        this.jdbcPwd = jdbcPwd;
+    }
+
+    public String getJdbcURL()
+    {
+        return jdbcURL;
+    }
+
+    public String getSchemaName()
+    {
+        return schemaName;
+    }
+
+    // ------- Setters -------
+
+    public void setDatabaseProvider(String databaseProvider)
+    {
+        this.databaseProvider = databaseProvider;
+    }
+
+    public void setJdbcURL(String jdbcURL)
+    {
+        this.jdbcURL = jdbcURL;
+    }
+
+    public String getJdbcUser()
+    {
+        return jdbcUser;
+    }
+
+    public void setJdbcUser(String jdbcUser)
+    {
+        this.jdbcUser = jdbcUser;
+    }
+
+    public void setSchemaName(String schemaName)
+    {
+        this.schemaName = schemaName;
+    }
+
+}

Added: incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvDB.java
URL: http://svn.apache.org/viewvc/incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvDB.java?rev=683186&view=auto
==============================================================================
--- incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvDB.java (added)
+++ incubator/empire-db/trunk/core/DBSampleAdv/src/org/apache/empire/samples/db/advanced/SampleAdvDB.java Wed Aug  6 02:07:15 2008
@@ -0,0 +1,259 @@
+package org.apache.empire.samples.db.advanced;
+
+import org.apache.empire.commons.Options;
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBColumn;
+import org.apache.empire.db.DBColumnExpr;
+import org.apache.empire.db.DBCommand;
+import org.apache.empire.db.DBCommandExpr;
+import org.apache.empire.db.DBDatabase;
+import org.apache.empire.db.DBTable;
+import org.apache.empire.db.DBTableColumn;
+import org.apache.empire.db.DBView;
+
+/**
+ * <PRE>
+ * This file contains the definition of the data model in Java.
+ * The SampleDB data model consists of two tables and a foreign key relation.
+ * The tables are defined as nested classes here, but you may put the in separate files if you want.
+ *
+ * PLEASE NOTE THE NAMING CONVENTION:
+ * Since all tables, views and columns are declared as "final" constants they are all in upper case.
+ * We recommend using a prefix of T_ for tables and C_ for columns in order to keep them togehter
+ * when listed in your IDE's code completition.
+ * There is no need to stick to this convention but it makes life just another little bit easier.
+ *
+ * You may declare other database tables or views in the same way.
+ * </PRE>
+ */
+public class SampleAdvDB extends DBDatabase
+{
+    /**
+     * This class represents the definition of the Departments table.
+     */
+    public static class Departments extends DBTable
+    {
+        public final DBTableColumn C_DEPARTMENT_ID;
+        public final DBTableColumn C_NAME;
+        public final DBTableColumn C_HEAD;
+        public final DBTableColumn C_BUSINESS_UNIT;
+        public final DBTableColumn C_UPDATE_TIMESTAMP;
+
+        public Departments(DBDatabase db)
+        {
+            super("DEPARTMENTS", db);
+            // ID
+            C_DEPARTMENT_ID   = addColumn("DEPARTMENT_ID",    DataType.AUTOINC,       0, true, "DEP_ID_SEQUENCE");
+            C_NAME            = addColumn("NAME",             DataType.TEXT,         80, true);
+            C_HEAD            = addColumn("HEAD",             DataType.TEXT,         80, false);
+            C_BUSINESS_UNIT   = addColumn("BUSINESS_UNIT",    DataType.TEXT,          4, true, "ITTK");
+            C_UPDATE_TIMESTAMP= addColumn("UPDATE_TIMESTAMP", DataType.DATETIME,      0, true);
+
+            // Primary Key
+            setPrimaryKey(C_DEPARTMENT_ID);
+            // Set other Indexes
+            addIndex("DEARTMENT_NAME_IDX", true, new DBColumn[] { C_NAME });
+            // Set timestamp column for save updates
+            setTimestampColumn(C_UPDATE_TIMESTAMP);
+        }
+    }
+
+    /**
+     * This class represents the definition of the Employees table.
+     */
+    public static class Employees extends DBTable
+    {
+        public final DBTableColumn C_EMPLOYEE_ID;
+        public final DBTableColumn C_SALUTATION;
+        public final DBTableColumn C_FIRSTNAME;
+        public final DBTableColumn C_LASTNAME;
+        public final DBTableColumn C_DATE_OF_BIRTH;
+        public final DBTableColumn C_GENDER;
+        public final DBTableColumn C_PHONE_NUMBER;
+        public final DBTableColumn C_EMAIL;
+        public final DBTableColumn C_RETIRED;
+        public final DBTableColumn C_CHECKSUM;
+        public final DBTableColumn C_UPDATE_TIMESTAMP;
+
+        // Useful column expressions
+        public final DBColumnExpr  C_FULLNAME;
+        
+        public Employees(DBDatabase db)
+        {
+            super("EMPLOYEES", db);
+            // ID
+            C_EMPLOYEE_ID     = addColumn("EMPLOYEE_ID",      DataType.AUTOINC,      0, true, "EMPLOYEE_ID_SEQUENCE");
+            C_SALUTATION      = addColumn("SALUTATION",       DataType.TEXT,        20, false);
+            C_FIRSTNAME       = addColumn("FIRSTNAME",        DataType.TEXT,        40, true);
+            C_LASTNAME        = addColumn("LASTNAME",         DataType.TEXT,        40, true);
+            C_DATE_OF_BIRTH   = addColumn("DATE_OF_BIRTH",    DataType.DATE,         0, false);
+            C_GENDER          = addColumn("GENDER",           DataType.TEXT,         1, false);
+            C_PHONE_NUMBER    = addColumn("PHONE_NUMBER",     DataType.TEXT,        40, false);
+            C_EMAIL           = addColumn("EMAIL",            DataType.TEXT,        80, false);
+            C_RETIRED         = addColumn("RETIRED",          DataType.BOOL,         0, true, false);
+            C_CHECKSUM        = addColumn("CHECKSUM",         DataType.INTEGER,      0, false);
+            C_UPDATE_TIMESTAMP= addColumn("UPDATE_TIMESTAMP", DataType.DATETIME,     0, true);
+
+            // Primary Key
+            setPrimaryKey(C_EMPLOYEE_ID);
+            // Set other Indexes
+            addIndex("EMPLOYEE_NAME_IDX", true, new DBColumn[] { C_FIRSTNAME, C_LASTNAME, C_DATE_OF_BIRTH });
+            // Set timestamp column for save updates
+            setTimestampColumn(C_UPDATE_TIMESTAMP);
+
+            // Create Options for GENDER column
+            Options genders = new Options();
+            genders.set("M", "Male");
+            genders.set("F", "Female");
+            C_GENDER.setOptions(genders);
+            
+            // Define Column Expressions
+            C_FULLNAME = C_LASTNAME.append(", ").append(C_FIRSTNAME).as("FULL_NAME");
+            
+        }
+    }
+    
+    /**
+     * This class represents the definition of the Departments table.
+     */
+    public static class EmployeeDepartmentHistory extends DBTable
+    {
+        public final DBTableColumn C_EMPLOYEE_ID;
+        public final DBTableColumn C_DEPARTMENT_ID;
+        public final DBTableColumn C_DATE_FROM;
+
+        public EmployeeDepartmentHistory(DBDatabase db)
+        {
+            super("EMPLOYEE_DEPARTMENT_HIST", db);
+            // ID
+            C_EMPLOYEE_ID   = addColumn("EMPLOYEE_ID",      DataType.INTEGER,       0, true);
+            C_DEPARTMENT_ID = addColumn("DEPARTMENT_ID",    DataType.INTEGER,       0, true);
+            C_DATE_FROM     = addColumn("DATE_FROM",        DataType.DATE,          0, true);
+
+            // Primary Key
+            setPrimaryKey(C_EMPLOYEE_ID, C_DATE_FROM);
+        }
+    }
+
+    /**
+     * This class represents the definition of the EmployeeDepSinceView table.
+     */
+    public static class EmployeeDepSinceView extends DBView
+    {
+        public final DBViewColumn C_EMPLOYEE_ID;
+        public final DBViewColumn C_MAX_DATE_FROM;
+        
+        public EmployeeDepSinceView(DBDatabase db, EmployeeDepartmentHistory T_EDH)
+        {
+            super("EMPLOYEE_DEP_SINCE_VIEW", db);
+            // ID
+            C_EMPLOYEE_ID     = addColumn(T_EDH.C_EMPLOYEE_ID);
+            C_MAX_DATE_FROM   = addColumn("MAX_DATE_FROM", T_EDH.C_DATE_FROM); 
+
+            // set Key-column (if any)
+            setKeyColumn(C_EMPLOYEE_ID);
+        }
+
+        @Override
+        public DBCommandExpr createCommand()
+        {
+            /* Sample DDL for this View:
+            CREATE VIEW EMPLOYEE_DEP_SINCE_VIEW (EMPLOYEE_ID, MAX_DATE_FROM)
+            AS (SELECT t3.EMPLOYEE_ID, max(t3.DATE_FROM)
+                FROM EMPLOYEE_DEPARTMENT_HIST t3
+                GROUP BY t3.EMPLOYEE_ID);
+            */
+            
+            SampleAdvDB db = (SampleAdvDB)getDatabase();
+            SampleAdvDB.EmployeeDepartmentHistory T_EDH = db.T_EMP_DEP_HIST;
+            
+            // Define the sub query
+            DBCommand cmd = db.createCommand();
+            cmd.select (T_EDH.C_EMPLOYEE_ID, T_EDH.C_DATE_FROM.max());
+            cmd.groupBy(T_EDH.C_EMPLOYEE_ID);
+            return cmd;
+        }
+    }
+
+    /**
+     * This class represents the definition of the EmployeeInfoView table.
+     */
+    public static class EmployeeInfoView extends DBView
+    {
+        public final DBViewColumn C_EMPLOYEE_ID;
+        public final DBViewColumn C_CURRENT_DEP_ID;
+        public final DBViewColumn C_NAME_AND_DEP;
+
+        public EmployeeInfoView(DBDatabase db, Employees T_EMP, Departments T_DEP)
+        {
+            super("EMPLOYEE_INFO_VIEW", db);
+            // ID
+            C_EMPLOYEE_ID     = addColumn(T_EMP.C_EMPLOYEE_ID);
+            C_CURRENT_DEP_ID  = addColumn("CURRENT_DEP_ID", T_DEP.C_DEPARTMENT_ID); 
+            C_NAME_AND_DEP    = addColumn("NAME_AND_DEP", DataType.TEXT);
+
+            // set Key-column (if any)
+            setKeyColumn(C_EMPLOYEE_ID);
+        }
+
+        @Override
+        public DBCommandExpr createCommand()
+        {
+            /* Sample DDL for this View:
+            CREATE VIEW EMPLOYEE_INFO_VIEW (EMPLOYEE_ID, CURRENT_DEP_ID, NAME_AND_DEP)
+            AS (SELECT t2.EMPLOYEE_ID, t1.DEPARTMENT_ID, t2.LASTNAME + ', ' + coalesce(t2.FIRSTNAME, '') + ' (' + t1.NAME + ')'
+                FROM EMPLOYEE_DEPARTMENT_HIST t3 
+                INNER JOIN EMPLOYEE_DEP_SINCE_VIEW v1 ON v1.EMPLOYEE_ID = t3.EMPLOYEE_ID AND t3.DATE_FROM=v1.MAX_DATE_FROM
+                INNER JOIN EMPLOYEES t2 ON t2.EMPLOYEE_ID = t3.EMPLOYEE_ID
+                INNER JOIN DEPARTMENTS t1 ON t1.DEPARTMENT_ID = t3.DEPARTMENT_ID);
+            */
+            
+            SampleAdvDB db = (SampleAdvDB)getDatabase();
+            SampleAdvDB.Employees   T_EMP = db.T_EMPLOYEES;
+            SampleAdvDB.EmployeeDepartmentHistory T_EDH = db.T_EMP_DEP_HIST;
+            SampleAdvDB.EmployeeDepSinceView V_EDS = db.V_EMP_DEP_SINCE_VIEW;
+            SampleAdvDB.Departments T_DEP = db.T_DEPARTMENTS;
+
+            // Define the query
+            DBCommand cmd = db.createCommand();
+            // Select requried columns
+            cmd.select(T_EMP.C_EMPLOYEE_ID);
+            cmd.select(T_DEP.C_DEPARTMENT_ID);
+            cmd.select(T_EMP.C_LASTNAME.append(", ")
+                       .append(T_EMP.C_FIRSTNAME.coalesce(DBDatabase.EMPTY_STRING))
+                       .append(" (").append(T_DEP.C_NAME).append(")"));
+            // Set Joins
+            cmd.join(T_EDH.C_EMPLOYEE_ID, V_EDS.C_EMPLOYEE_ID)
+              .where(T_EDH.C_DATE_FROM.is(V_EDS.C_MAX_DATE_FROM));
+            cmd.join(T_EMP.C_EMPLOYEE_ID, T_EDH.C_EMPLOYEE_ID);
+            cmd.join(T_DEP.C_DEPARTMENT_ID, T_EDH.C_DEPARTMENT_ID);
+            // done
+            return cmd;
+        }
+    }
+    
+    // Declare all Tables
+    public final Departments  T_DEPARTMENTS = new Departments(this);
+    public final Employees    T_EMPLOYEES   = new Employees(this);
+    public final EmployeeDepartmentHistory  T_EMP_DEP_HIST = new EmployeeDepartmentHistory(this);
+    // Declare all Views
+    public final EmployeeDepSinceView V_EMP_DEP_SINCE_VIEW;
+    public final EmployeeInfoView V_EMPLOYEE_INFO; 
+    
+    /**
+     * Constructor of the SampleDB data model description
+     *
+     * Put all foreigen key realtions here.
+     */
+    public SampleAdvDB()
+    {
+        // Create views
+        V_EMP_DEP_SINCE_VIEW = new EmployeeDepSinceView(this, T_EMP_DEP_HIST);
+        V_EMPLOYEE_INFO = new EmployeeInfoView(this, T_EMPLOYEES, T_DEPARTMENTS);
+        
+        // Define Foreign-Key Relations
+        addRelation( T_EMP_DEP_HIST.C_EMPLOYEE_ID  .referenceOn( T_EMPLOYEES.C_EMPLOYEE_ID ));
+        addRelation( T_EMP_DEP_HIST.C_DEPARTMENT_ID.referenceOn( T_DEPARTMENTS.C_DEPARTMENT_ID ));
+    }
+
+}



Mime
View raw message