Changeset 4183

Timestamp:

12/12/13 15:11:24 (10 years ago)

Author:

olhsha

Message:

fixing HSQL/Postgres discrepance problem when setting an current_time date in UTC as default (translation added for Unit test DB and actual Postgres DB is updated so the defaults is set implicitely (no need to send it via the sql request).

Location:

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src

Files:

• main/java/eu/dasish/annotation/backend/dao/impl/JdbcAnnotationDao.java (modified) (4 diffs)
• main/java/eu/dasish/annotation/backend/dao/impl/JdbcTargetDao.java (modified) (1 diff)
• main/sql/DashishAnnotatorCreate.sql (modified) (3 diffs)
• test/java/eu/dasish/annotation/backend/dao/impl/JdbcResourceDaoTest.java (modified) (1 diff)
• test/java/eu/dasish/annotation/backend/rest/AnnotationsTest.java (modified) (1 diff)

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/impl/JdbcAnnotationDao.java

@@ -316 +316 @@
         StringBuilder sql = new StringBuilder("UPDATE ");
         sql.append(annotationTableName).append(" SET ").
-                append(last_modified).append("=  current_timestamp AT TIME ZONE INTERVAL '00:00' HOUR TO MINUTE,").
+                append(last_modified).append("=  default,").
                 append(body_text).append("= '").append(text).
                 append("' WHERE ").append(annotation_id).append("= ?");
@@ -334 +334 @@
         StringBuilder sql = new StringBuilder("UPDATE ");
         sql.append(annotationTableName).append(" SET ").
-                append(last_modified).append("=  current_timestamp AT TIME ZONE 'UTC' HOUR TO MINUTE,").
+                append(last_modified).append("=  default,").
                 append(body_text).append("= :bodyText, ").
                 append(body_mimetype).append("= :bodyMimeType, ").
@@ -362 +362 @@
                 append(body_mimetype).append("= :bodyMimeType ,").
                 append(headline).append("=  :headline ,").
-                append(last_modified).append("=  current_timestamp AT TIME ZONE 'UTC' HOUR TO MINUTE,").
+                append(last_modified).append("=  default,").
                 append(is_xml).append("= :isXml").
                 append(" WHERE ").append(external_id).append("= :externalID");
@@ -403 +403 @@
         StringBuilder sql = new StringBuilder("INSERT INTO ");
         sql.append(annotationTableName).append("(").append(external_id).append(",").append(owner_id);
-        sql.append(",").append(headline).append(",").append(body_text).append(",").append(body_mimetype).append(",").append(is_xml).append(",").append(last_modified).append(" ) VALUES (:externalId, :ownerId, :headline, :bodyText, :bodyMimeType, :isXml, current_timestamp AT TIME ZONE INTERVAL '00:00' HOUR TO MINUTE)");
+        sql.append(",").append(headline).append(",").append(body_text).append(",").append(body_mimetype).append(",").append(is_xml).
+                append(" ) VALUES (:externalId, :ownerId, :headline, :bodyText, :bodyMimeType, :isXml)");
         int affectedRows = getSimpleJdbcTemplate().update(sql.toString(), params);
         return ((affectedRows > 0) ? getInternalID(externalID) : null);

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/java/eu/dasish/annotation/backend/dao/impl/JdbcTargetDao.java

@@ -173 +173 @@
         params.put("fragmentDescriptor", linkParts[1]);
         StringBuilder sql = new StringBuilder("INSERT INTO ");
-        sql.append(targetTableName).append("(").append(external_id).append(",").append(link_uri).append(",").append(version).append(",").append(last_modified).append(",").append(fragment_descriptor).append(" ) VALUES (:externalId, :linkUri,  :version, current_timestamp AT TIME ZONE INTERVAL '00:00' HOUR TO MINUTE, :fragmentDescriptor)");
+        sql.append(targetTableName).append("(").append(external_id).append(",").append(link_uri).append(",").append(version).append(",").append(fragment_descriptor).append(" ) VALUES (:externalId, :linkUri,  :version, :fragmentDescriptor)");
         final int affectedRows = getSimpleJdbcTemplate().update(sql.toString(), params);
         return (affectedRows > 0 ? getInternalID(UUID.fromString(externalID.toString())) : null);

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/main/sql/DashishAnnotatorCreate.sql

@@ -50 +50 @@
     notebook_id SERIAL UNIQUE NOT NULL,
     external_id text UNIQUE NOT NULL,
-    last_modified timestamp default now(),
+    last_modified timestamp default current_timestamp AT TIME ZONE 'UTC',
     title text,
     owner_id integer NOT NULL
@@ -59 +59 @@
     annotation_id SERIAL UNIQUE NOT NULL, 
     external_id text UNIQUE NOT NULL,
-    last_modified timestamp default now(),
+    last_modified timestamp default current_timestamp AT TIME ZONE 'UTC',
     owner_id integer REFERENCES principal(principal_id), 
     headline text,
@@ -72 +72 @@
     target_id SERIAL UNIQUE NOT NULL,
     external_id text UNIQUE NOT NULL,
-    last_modified timestamp default now(),
+    last_modified timestamp default current_timestamp AT TIME ZONE 'UTC',
     link_uri text, 
     version text,

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/test/java/eu/dasish/annotation/backend/dao/impl/JdbcResourceDaoTest.java

@@ -68 +68 @@
         //sqlString = sqlString.replaceAll("UUID", "text");
         sqlString = sqlString.replaceAll("SERIAL NOT NULL", "INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY");
+        sqlString = sqlString.replaceAll("AT TIME ZONE 'UTC'", "AT TIME ZONE INTERVAL '00:00' HOUR TO MINUTE");
         return sqlString;
     }

DASISH/t5.6/backend/annotator-backend/trunk/annotator-backend/src/test/java/eu/dasish/annotation/backend/rest/AnnotationsTest.java

@@ -112 +112 @@
         sqlString = sqlString.replaceAll("CACHE 1;", "; -- CACHE 1;");
         //sqlString = sqlString.replaceAll("UUID", "text");
-        sqlString = sqlString.replaceAll("SERIAL NOT NULL", "INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY");
+        sqlString = sqlString.replaceAll("SERIAL NOT NULL", "INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY");        
+        sqlString = sqlString.replaceAll("AT TIME ZONE 'UTC'", "AT TIME ZONE INTERVAL '00:00' HOUR TO MINUTE");
         return sqlString;
     }