You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@empire-db.apache.org by do...@apache.org on 2008/08/06 11:07:17 UTC
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/
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 ));
+ }
+
+}