| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| */ |
| package org.apache.empire.vue.sample.db; |
| |
| import java.sql.SQLException; |
| |
| import org.apache.empire.data.Column; |
| import org.apache.empire.data.DataType; |
| import org.apache.empire.db.DBColumn; |
| import org.apache.empire.db.DBContext; |
| import org.apache.empire.db.DBDatabase; |
| import org.apache.empire.db.DBSQLScript; |
| import org.apache.empire.db.DBTableColumn; |
| import org.apache.empire.db.validation.DBModelChecker; |
| import org.apache.empire.db.validation.DBModelErrorLogger; |
| import org.apache.empire.dbms.postgresql.DBMSHandlerPostgreSQL; |
| import org.slf4j.Logger; |
| import org.slf4j.LoggerFactory; |
| |
| /** |
| * <PRE> |
| * This file contains the definition of the data model in Java. |
| * The SampleDB data model consists of three tables and two foreign key relations. |
| * The tables are defined as nested classes here, but you may put them in separate files if you want to. |
| * |
| * The class also provided methods to create the database (if it doesn't exist) |
| * and to check the datamodel i.e. to compare this definition to the database in the DBMS |
| * |
| * PLEASE NOTE THE NAMING CONVENTION: |
| * Since all tables, views and columns are declared as "final" constants we have all put them in upper case. |
| * |
| * You may declare other database tables or views in the same way. |
| * </PRE> |
| */ |
| public class SampleDB extends DBDatabase |
| { |
| // Logger |
| private static final Logger log = LoggerFactory.getLogger(SampleDB.class); |
| |
| /** |
| * Gender enum |
| */ |
| public enum Gender |
| { |
| M("Male"), |
| F("Female"), |
| U("Unknown"); |
| |
| private final String title; |
| private Gender(String title) |
| { |
| this.title = title; |
| } |
| @Override |
| public String toString() |
| { |
| return title; |
| } |
| } |
| |
| /** |
| * This class represents the Departments table. |
| */ |
| public static class TDepartments extends SampleTable |
| { |
| public final DBTableColumn ID; |
| public final DBTableColumn NAME; |
| public final DBTableColumn HEAD; |
| public final DBTableColumn BUSINESS_UNIT; |
| public final DBTableColumn UPDATE_TIMESTAMP; |
| |
| public TDepartments(SampleDB db) |
| { |
| super("DEPARTMENTS", db); |
| // ID |
| ID = addIdentity ("ID", "DEP_ID_SEQUENCE"); // Optional Sequence for some DBMS (e.g. Oracle) |
| NAME = addColumn ("NAME", DataType.VARCHAR, 80, true); |
| HEAD = addColumn ("HEAD", DataType.VARCHAR, 80, false); |
| BUSINESS_UNIT = addColumn ("BUSINESS_UNIT", DataType.VARCHAR, 4, true, "ITTK"); |
| UPDATE_TIMESTAMP= addTimestamp("UPDATE_TIMESTAMP"); |
| |
| // Primary Key (automatically set due to addIdentity()) otherwise use |
| // setPrimaryKey(...); |
| |
| // Set other Indexes |
| addIndex("DEPARTMENT_NAME_IDX", true, new DBColumn[] { NAME }); |
| |
| // optional: set entity name for selectQualified() to singular since table is plural |
| setEntityName("DEPARTMENT"); |
| } |
| } |
| |
| /** |
| * This class represents the Employees table. |
| */ |
| public static class TEmployees extends SampleTable |
| { |
| public final DBTableColumn ID; |
| public final DBTableColumn SALUTATION; |
| public final DBTableColumn FIRST_NAME; |
| public final DBTableColumn LAST_NAME; |
| public final DBTableColumn DATE_OF_BIRTH; |
| public final DBTableColumn DEPARTMENT_ID; |
| public final DBTableColumn GENDER; |
| public final DBTableColumn PHONE_NUMBER; |
| public final DBTableColumn EMAIL; |
| public final DBTableColumn SALARY; |
| public final DBTableColumn RETIRED; |
| public final DBTableColumn UPDATE_TIMESTAMP; |
| |
| public TEmployees(SampleDB db) |
| { |
| super("EMPLOYEES", db); |
| |
| // ID |
| ID = addIdentity ("ID", "EMPLOYEE_ID_SEQUENCE"); // Optional Sequence name for some DBMS (e.g. Oracle) |
| SALUTATION = addColumn ("SALUTATION", DataType.VARCHAR, 5, false); |
| FIRST_NAME = addColumn ("FIRST_NAME", DataType.VARCHAR, 40, true); |
| LAST_NAME = addColumn ("LAST_NAME", DataType.VARCHAR, 40, true); |
| DATE_OF_BIRTH = addColumn ("DATE_OF_BIRTH", DataType.DATE, 0, false); |
| DEPARTMENT_ID = addForeignKey("DEPARTMENT_ID", db.DEPARTMENTS, true); |
| GENDER = addColumn ("GENDER", DataType.VARCHAR, 1, true, Gender.class); |
| PHONE_NUMBER = addColumn ("PHONE_NUMBER", DataType.VARCHAR, 40, false, "0815-"); |
| EMAIL = addColumn ("EMAIL", DataType.VARCHAR, 80, false); |
| SALARY = addColumn ("SALARY", DataType.DECIMAL, 10.2, false); |
| RETIRED = addColumn ("RETIRED", DataType.BOOL, 0, true, false); |
| UPDATE_TIMESTAMP= addTimestamp ("UPDATE_TIMESTAMP"); |
| |
| // Primary Key (automatically set due to addIdentity()) otherwise use |
| // setPrimaryKey(...); |
| |
| // Set other Indexes |
| addIndex("EMPLOYEE_NAME_IDX", true, new DBColumn[] { FIRST_NAME, LAST_NAME, DATE_OF_BIRTH }); |
| |
| // optional: set entity name for selectQualified() to singular since table is plural |
| setEntityName("EMPLOYEE"); |
| |
| // Set custom style for SALUTATION |
| SALUTATION.setAttribute("styleClass", "eInpShort"); |
| // Salary special |
| SALARY.setAttribute("styleClass", "eInpDecimal") |
| .setAttribute("format:unit", "USD") |
| .setAttribute(Column.COLATTR_NUMBER_TYPE, "Decimal") |
| .setAttribute(Column.COLATTR_FRACTION_DIGITS, 2) |
| .setAttribute(Column.COLATTR_NUMBER_GROUPSEP, true); |
| } |
| } |
| |
| /** |
| * This class represents the Payments table. |
| */ |
| public static class TPayments extends SampleTable |
| { |
| public final DBTableColumn EMPLOYEE_ID; |
| public final DBTableColumn YEAR; |
| public final DBTableColumn MONTH; |
| public final DBTableColumn AMOUNT; |
| |
| public TPayments(SampleDB db) |
| { |
| super("PAYMENTS", db); |
| |
| // ID |
| EMPLOYEE_ID = addForeignKey("EMPLOYEE_ID", db.EMPLOYEES, true); |
| YEAR = addColumn("YEAR", DataType.DECIMAL, 4.0, true); |
| MONTH = addColumn("MONTH", DataType.DECIMAL, 2.0, true); |
| AMOUNT = addColumn("AMOUNT", DataType.DECIMAL, 8.2, true); |
| |
| // Primary Key |
| setPrimaryKey(EMPLOYEE_ID, YEAR, MONTH); |
| |
| // optional: set entity name for selectQualified() to singular since table is plural |
| setEntityName("PAYMENT"); |
| } |
| } |
| |
| // Declare all Tables and Views here |
| public final TDepartments DEPARTMENTS = new TDepartments(this); |
| public final TEmployees EMPLOYEES = new TEmployees(this); |
| public final TPayments PAYMENTS = new TPayments(this); |
| |
| /** |
| * Constructor of the SampleDB data model |
| * |
| * Put all foreign key relations here. |
| */ |
| public SampleDB() |
| { |
| // Define additional Foreign-Key Relations here |
| // which have not already been defined by addForeignKey() |
| // addRelation( {Source Column}.referenceOn( {Target Column} )); |
| log.info("SampleDB has been created with {} Tables and {} Relations", getTables().size(), getRelations().size()); |
| } |
| |
| // Needed for the DBELResolver |
| @Override |
| protected void register(String id) |
| { |
| super.register("db"); |
| } |
| |
| @Override |
| public void open(DBContext context) |
| { |
| // Enable prepared statements |
| // setPreparedStatementsEnabled(true); |
| // Check exists |
| if (checkExists(context)) |
| { // attach to driver |
| super.open(context); |
| // yes, it exists, then check the model |
| checkDataModel(context); |
| } |
| else |
| { // PostgreSQL does not support DDL in transaction |
| if(getDbms() instanceof DBMSHandlerPostgreSQL) |
| setAutoCommit(context, true); |
| // create the database |
| createDatabase(context); |
| // PostgreSQL does not support DDL in transaction |
| if(getDbms() instanceof DBMSHandlerPostgreSQL) |
| setAutoCommit(context, false); |
| // attach to driver |
| super.open(context); |
| } |
| } |
| |
| private void createDatabase(DBContext context) |
| { |
| // create DDL for Database Definition |
| DBSQLScript script = new DBSQLScript(context); |
| getCreateDDLScript(script); |
| // Show DDL Statement |
| log.info(script.toString()); |
| // Execute Script |
| script.executeAll(false); |
| // Commit |
| context.commit(); |
| } |
| |
| private void checkDataModel(DBContext context) |
| { try { |
| DBModelChecker modelChecker = context.getDbms().createModelChecker(this); |
| // Check data model |
| log.info("Checking DataModel for {} using {}", getClass().getSimpleName(), modelChecker.getClass().getSimpleName()); |
| // dbo schema |
| DBModelErrorLogger logger = new DBModelErrorLogger(); |
| modelChecker.checkModel(this, context.getConnection(), logger); |
| // show result |
| log.info("Data model check done. Found {} errors and {} warnings.", logger.getErrorCount(), logger.getWarnCount()); |
| } catch(Exception e) { |
| log.error("FATAL error when checking data model. Probably not properly implemented by DBMSHandler!"); |
| } |
| } |
| |
| private void setAutoCommit(DBContext context, boolean enable) |
| { try { |
| context.getConnection().setAutoCommit(enable); |
| } catch (SQLException e) { |
| log.error("Unable to set AutoCommit on Connection", e); |
| } |
| } |
| } |