empire-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From inem...@apache.org
Subject empire-db git commit: EMPIREDB-233
Date Sun, 08 Nov 2015 15:22:23 GMT
Repository: empire-db
Updated Branches:
  refs/heads/master 7ac3454c4 -> e846070ef


EMPIREDB-233

- changed in MSSQL driver DATE and DATETIME templates
- removed 'set dateformat' sql from attachDatabase
- added junit test class

Project: http://git-wip-us.apache.org/repos/asf/empire-db/repo
Commit: http://git-wip-us.apache.org/repos/asf/empire-db/commit/e846070e
Tree: http://git-wip-us.apache.org/repos/asf/empire-db/tree/e846070e
Diff: http://git-wip-us.apache.org/repos/asf/empire-db/diff/e846070e

Branch: refs/heads/master
Commit: e846070effcb179dbb97f4c658fc10368dc4e68b
Parents: 7ac3454
Author: inemeth <inemeth@apache.org>
Authored: Sun Nov 8 16:22:05 2015 +0100
Committer: inemeth <inemeth@apache.org>
Committed: Sun Nov 8 16:22:05 2015 +0100

----------------------------------------------------------------------
 .../db/sqlserver/DBDatabaseDriverMSSQL.java     |   6 +-
 .../db/mssql/DBDatabaseDriverMSSqlDateTest.java | 188 +++++++++++++++++++
 2 files changed, 190 insertions(+), 4 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/empire-db/blob/e846070e/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
----------------------------------------------------------------------
diff --git a/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
b/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
index 5b8b812..66645e0 100644
--- a/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
+++ b/empire-db/src/main/java/org/apache/empire/db/sqlserver/DBDatabaseDriverMSSQL.java
@@ -204,8 +204,6 @@ public class DBDatabaseDriverMSSQL extends DBDatabaseDriver
         {   // Set Database
             if (StringUtils.isNotEmpty(databaseName))
                 executeSQL("USE " + databaseName, null, conn, null);
-            // Set Dateformat
-            executeSQL("SET DATEFORMAT ymd", null, conn, null);
             // Sequence Table
             if (useSequenceTable && db.getTable(sequenceTableName)==null)
                 new DBSeqTable(sequenceTableName, db);
@@ -282,10 +280,10 @@ public class DBDatabaseDriverMSSQL extends DBDatabaseDriver
             case SQL_BOOLEAN_FALSE:           return "0";
             case SQL_CURRENT_DATE:            return "convert(char, getdate(), 111)";
             case SQL_DATE_PATTERN:            return "yyyy-MM-dd";
-            case SQL_DATE_TEMPLATE:           return "'{0}'";
+            case SQL_DATE_TEMPLATE:           return "convert(date, '{0}', 121)";
             case SQL_CURRENT_DATETIME:        return "getdate()";
             case SQL_DATETIME_PATTERN:        return "yyyy-MM-dd HH:mm:ss.SSS";
-            case SQL_DATETIME_TEMPLATE:       return "'{0}'";
+            case SQL_DATETIME_TEMPLATE:       return "convert(datetime, '{0}', 121)";
             // functions
             case SQL_FUNC_COALESCE:           return "coalesce(?, {0})";
             case SQL_FUNC_SUBSTRING:          return "substring(?, {0}, 4000)";

http://git-wip-us.apache.org/repos/asf/empire-db/blob/e846070e/empire-db/src/test/java/org/apache/empire/db/mssql/DBDatabaseDriverMSSqlDateTest.java
----------------------------------------------------------------------
diff --git a/empire-db/src/test/java/org/apache/empire/db/mssql/DBDatabaseDriverMSSqlDateTest.java
b/empire-db/src/test/java/org/apache/empire/db/mssql/DBDatabaseDriverMSSqlDateTest.java
new file mode 100644
index 0000000..3f0c12c
--- /dev/null
+++ b/empire-db/src/test/java/org/apache/empire/db/mssql/DBDatabaseDriverMSSqlDateTest.java
@@ -0,0 +1,188 @@
+/*
+ * 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.db.mssql;
+
+import static org.junit.Assert.assertEquals;
+
+import java.sql.Connection;
+import java.sql.Timestamp;
+import java.text.DateFormat;
+import java.text.SimpleDateFormat;
+import java.util.Date;
+
+import org.apache.empire.DBResource;
+import org.apache.empire.DBResource.DB;
+import org.apache.empire.data.DataMode;
+import org.apache.empire.data.DataType;
+import org.apache.empire.db.DBCommand;
+import org.apache.empire.db.DBDatabase;
+import org.apache.empire.db.DBDatabaseDriver;
+import org.apache.empire.db.DBRecord;
+import org.apache.empire.db.DBSQLScript;
+import org.apache.empire.db.DBTable;
+import org.apache.empire.db.DBTableColumn;
+import org.junit.Ignore;
+import org.junit.Rule;
+import org.junit.Test;
+
+@Ignore
+public class DBDatabaseDriverMSSqlDateTest {
+
+	@Rule
+	public DBResource dbResource = new DBResource(DB.MSSQL_JTDS);
+
+	@Test
+	public void testDateFormatYMD() throws Exception {
+		runTestWithDateFormat("ymd");
+	}
+
+	@Test
+	public void testDateFormatDMY() throws Exception {
+		runTestWithDateFormat("dmy");
+	}
+
+	public void runTestWithDateFormat(String dbDateFormat) throws Exception {
+		Connection conn = dbResource.getConnection();
+
+		DBDatabaseDriver driver = dbResource.newDriver();
+		DateTimeTestDB db = new DateTimeTestDB();
+
+		// Encoding issue occur when prepared statement is disabled
+		// db.setPreparedStatementsEnabled(true);
+
+		db.open(driver, dbResource.getConnection());
+
+		if (!databaseExists(conn, db)) {
+			DBSQLScript script = new DBSQLScript();
+			db.getCreateDDLScript(db.getDriver(), script);
+			System.out.println(script.toString());
+			script.run(db.getDriver(), dbResource.getConnection(), false);
+		}
+
+		// STEP 5: Clear Database (Delete all records)
+		clearDatabase(conn, db);
+
+		// MSSQL datetime Accuracy => Rounded to increments of .000, .003, or
+		// .007 seconds => ignore ms for comparison
+		DateFormat truncDateFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+
+		DateFormat dateFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
+
+		Timestamp lastLoginTs = new Timestamp(System.currentTimeMillis());
+		Date regDate = dateFmt.parse("2015-08-20 00:00:00.000");
+
+		{
+
+			driver.executeSQL("SET DATEFORMAT " + dbDateFormat, null, conn,
+					null);
+			DBRecord rec = new DBRecord();
+			rec.create(db.USER_INFO);
+			rec.setValue(db.USER_INFO.USER, "john.doe");
+			rec.setValue(db.USER_INFO.REG_DATE, regDate);
+			rec.setValue(db.USER_INFO.LAST_LOGIN, lastLoginTs);
+			rec.update(conn);
+
+			int id = rec.getInt(db.USER_INFO.ID);
+
+			DBRecord recRead = new DBRecord();
+			recRead.read(db.USER_INFO, id, conn);
+
+			assertEquals(truncDateFmt.format(lastLoginTs),
+					truncDateFmt.format(recRead
+							.getDateTime(db.USER_INFO.LAST_LOGIN)));
+			assertEquals(truncDateFmt.format(regDate),
+					truncDateFmt.format(recRead
+							.getDateTime(db.USER_INFO.REG_DATE)));
+
+		}
+
+	}
+
+	/**
+	 * <PRE>
+	 * Empties all Tables.
+	 * </PRE>
+	 */
+	private static void clearDatabase(Connection conn, DateTimeTestDB db) {
+		DBCommand cmd = db.createCommand();
+		// Delete all Employees (no constraints)
+		db.executeSQL(cmd.getDelete(db.USER_INFO), conn);
+	}
+
+	/**
+	 * <PRE>
+	 * Checks whether the database exists or not by executing
+	 *     select count(*) from USER_INFO
+	 * 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 which can be ignored.
+	 * </PRE>
+	 */
+	private static boolean databaseExists(Connection conn, DateTimeTestDB db) {
+		// Check whether DB exists
+		DBCommand cmd = db.createCommand();
+		cmd.select(db.USER_INFO.count());
+		// Check using "select count(*) from DEPARTMENTS"
+
+		//System.out
+				//.println("Checking whether table USER_INFO exists (SQLException will be logged if not
- please ignore) ...");
+		try {
+			return (db.querySingleInt(cmd, -1, conn) >= 0);
+		} catch (Exception e) {
+			return false;
+		}
+
+	}
+
+	private static class DateTimeTestDB extends DBDatabase {
+
+		private static final long serialVersionUID = 1L;
+
+		public static class UserInfoTable extends DBTable {
+
+			public final DBTableColumn ID;
+			public final DBTableColumn USER;
+			public final DBTableColumn REG_DATE;
+			public final DBTableColumn LAST_LOGIN;
+
+			public UserInfoTable(DBDatabase db) {
+				super("USER_INFO", db);
+				ID = addColumn("DEPARTMENT_ID", DataType.AUTOINC, 0,
+						DataMode.AutoGenerated);
+				USER = addColumn("USER", DataType.TEXT, 80, DataMode.NotNull);
+				REG_DATE = addColumn("REG_DATE", DataType.DATE, 80,
+						DataMode.NotNull);
+				LAST_LOGIN = addColumn("LAST_LOGIN", DataType.DATETIME, 0,
+						DataMode.Nullable);
+
+				// Primary Key
+				setPrimaryKey(ID);
+
+			}
+
+		}
+
+		public final UserInfoTable USER_INFO = new UserInfoTable(this);
+
+		public DateTimeTestDB() {
+			super();
+		}
+
+	}
+
+}


Mime
View raw message