mauricio · mauricio · Nov 2, 2013 · Oct 31, 2013 · Oct 31, 2013
diff --git a/...sql-async/src/main/scala/com/github/mauricio/async/db/postgresql/column/ColumnTypes.scala b/...sql-async/src/main/scala/com/github/mauricio/async/db/postgresql/column/ColumnTypes.scala
@@ -51,6 +51,8 @@ object ColumnTypes {
   final val TimeArray = 1183
   final val TimeWithTimezone = 1266
   final val TimeWithTimezoneArray = 1270
+  final val Interval = 1186
+  final val IntervalArray = 1187
   final val Boolean = 16
   final val BooleanArray = 1000
 
@@ -120,4 +122,4 @@ object ColumnTypes {
     public static final int XML = 142;
     public static final int XML_ARRAY = 143;
 
-*/
+*/
diff --git a/...cala/com/github/mauricio/async/db/postgresql/column/PostgreSQLColumnDecoderRegistry.scala b/...cala/com/github/mauricio/async/db/postgresql/column/PostgreSQLColumnDecoderRegistry.scala
@@ -44,6 +44,7 @@ class PostgreSQLColumnDecoderRegistry( charset : Charset = CharsetUtil.UTF_8 ) e
   private final val dateArrayDecoder =  new ArrayDecoder(DateEncoderDecoder)
   private final val timeArrayDecoder = new ArrayDecoder(TimeEncoderDecoder.Instance)
   private final val timeWithTimestampArrayDecoder = new ArrayDecoder(TimeWithTimezoneEncoderDecoder)
+  private final val intervalArrayDecoder = new ArrayDecoder(PostgreSQLIntervalEncoderDecoder)
 
   override def decode(kind: ColumnData, value: ByteBuf, charset: Charset): Any = {
     decoderFor(kind.dataType).decode(kind, value, charset)
@@ -99,6 +100,9 @@ class PostgreSQLColumnDecoderRegistry( charset : Charset = CharsetUtil.UTF_8 ) e
       case TimeWithTimezone => TimeWithTimezoneEncoderDecoder
       case TimeWithTimezoneArray => this.timeWithTimestampArrayDecoder
 
+      case Interval => PostgreSQLIntervalEncoderDecoder
+      case IntervalArray => this.intervalArrayDecoder
+
       case OIDArray => this.stringArrayDecoder
       case MoneyArray => this.stringArrayDecoder
       case NameArray => this.stringArrayDecoder

diff --git a/...cala/com/github/mauricio/async/db/postgresql/column/PostgreSQLColumnEncoderRegistry.scala b/...cala/com/github/mauricio/async/db/postgresql/column/PostgreSQLColumnEncoderRegistry.scala
@@ -55,6 +55,9 @@ class PostgreSQLColumnEncoderRegistry extends ColumnEncoderRegistry {
     classOf[ReadableDateTime] -> (TimestampWithTimezoneEncoderDecoder -> ColumnTypes.TimestampWithTimezone),
     classOf[ReadableInstant] -> (DateEncoderDecoder -> ColumnTypes.Date),
 
+    classOf[ReadablePeriod] -> (PostgreSQLIntervalEncoderDecoder -> ColumnTypes.Interval),
+    classOf[ReadableDuration] -> (PostgreSQLIntervalEncoderDecoder -> ColumnTypes.Interval),
+
     classOf[java.util.Date] -> (TimestampWithTimezoneEncoderDecoder -> ColumnTypes.TimestampWithTimezone),
     classOf[java.sql.Date] -> ( DateEncoderDecoder -> ColumnTypes.Date ),
     classOf[java.sql.Time] -> ( SQLTimeEncoder -> ColumnTypes.Time ),
@@ -173,4 +176,4 @@ class PostgreSQLColumnEncoderRegistry extends ColumnEncoderRegistry {
       }
     }
   }
-}
+}
diff --git a/...ala/com/github/mauricio/async/db/postgresql/column/PostgreSQLIntervalEncoderDecoder.scala b/...ala/com/github/mauricio/async/db/postgresql/column/PostgreSQLIntervalEncoderDecoder.scala
@@ -0,0 +1,121 @@
+/*
+ * Copyright 2013 Maurício Linhares
+ * Copyright 2013 Dylan Simon
+ *
+ * Maurício Linhares 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 com.github.mauricio.async.db.postgresql.column
+
+import com.github.mauricio.async.db.column.ColumnEncoderDecoder
+import com.github.mauricio.async.db.exceptions.DateEncoderNotAvailableException
+import com.github.mauricio.async.db.util.Log
+import org.joda.time.{Period, ReadablePeriod, ReadableDuration}
+import org.joda.time.format.{ISOPeriodFormat, PeriodFormatterBuilder}
+
+object PostgreSQLIntervalEncoderDecoder extends ColumnEncoderDecoder {
+
+  private val log = Log.getByName(this.getClass.getName)
+
+  /* Postgres accepts all ISO8601 formats. */
+  private val formatter = ISOPeriodFormat.standard
+
+  override def encode(value : Any) : String = {
+    value match {
+      case t : ReadablePeriod => formatter.print(t)
+      case t : ReadableDuration => t.toString // defaults to ISO8601
+      case _ => throw new DateEncoderNotAvailableException(value)
+    }
+  }
+
+  /* these should only be used for parsing: */
+  private def postgresYMDBuilder(builder : PeriodFormatterBuilder) = builder
+    .appendYears  .appendSuffix(" year", " years").appendSeparator(" ")
+    .appendMonths .appendSuffix(" mon",  " mons" ).appendSeparator(" ")
+    .appendDays   .appendSuffix(" day",  " days" ).appendSeparator(" ")
+
+  private val postgres_verboseParser =
+    postgresYMDBuilder(new PeriodFormatterBuilder().appendLiteral("@ "))
+    .appendHours  .appendSuffix(" hour", " hours").appendSeparator(" ")
+    .appendMinutes.appendSuffix(" min",  " mins" ).appendSeparator(" ")
+    .appendSecondsWithOptionalMillis.appendSuffix(" sec", " secs")
+    .toFormatter
+
+  private def postgresHMSBuilder(builder : PeriodFormatterBuilder) = builder
+    // .printZeroAlways // really all-or-nothing
+    .rejectSignedValues(true) // XXX: sign should apply to all
+    .appendHours  .appendSuffix(":")
+    .appendMinutes.appendSuffix(":")
+    .appendSecondsWithOptionalMillis
+
+  private val hmsParser =
+    postgresHMSBuilder(new PeriodFormatterBuilder())
+    .toFormatter
+
+  private val postgresParser = 
+    postgresHMSBuilder(postgresYMDBuilder(new PeriodFormatterBuilder()))
+    .toFormatter
+
+  /* These sql_standard parsers don't handle negative signs correctly. */
+  private def sqlDTBuilder(builder : PeriodFormatterBuilder) =
+    postgresHMSBuilder(builder
+    .appendDays.appendSeparator(" "))
+
+  private val sqlDTParser =
+    sqlDTBuilder(new PeriodFormatterBuilder())
+    .toFormatter
+
+  private val sqlParser =
+    sqlDTBuilder(new PeriodFormatterBuilder()
+    .printZeroAlways
+    .rejectSignedValues(true) // XXX: sign should apply to both
+    .appendYears.appendSeparator("-").appendMonths
+    .rejectSignedValues(false)
+    .printZeroNever
+    .appendSeparator(" "))
+    .toFormatter
+
+  /* This supports all positive intervals, and intervalstyle of postgres_verbose, and iso_8601 perfectly.
+   * If intervalstyle is set to postgres or sql_standard, some negative intervals may be rejected.
+   */
+  def decode(value : String) : Period = {
+    if (value.isEmpty) /* huh? */
+      Period.ZERO
+    else {
+      val format = (
        if (value(0).equals('P')) /* iso_8601 */
+          formatter
+        else if (value.startsWith("@ "))
+          postgres_verboseParser
+        else {
+          /* try to guess based on what comes after the first number */
+          val i = value.indexWhere(!_.isDigit, if ("-+".contains(value(0))) 1 else 0)
+          if (i < 0 || ":.".contains(value(i))) /* simple HMS (to support group negation) */
+            hmsParser
+          else if (value(i).equals('-')) /* sql_standard: Y-M */
+            sqlParser
+          else if (value(i).equals(' ') && i+1 < value.length && value(i+1).isDigit) /* sql_standard: D H:M:S */
+            sqlDTParser
+          else
+            postgresParser
+        }
+      )
+      if ((format eq hmsParser) && value(0).equals('-'))
+        format.parsePeriod(value.substring(1)).negated
+      else if (value.endsWith(" ago")) /* only really applies to postgres_verbose, but shouldn't hurt */
+        format.parsePeriod(value.stripSuffix(" ago")).negated
+      else
+        format.parsePeriod(value)
+    }
+  }
+}
diff --git a/...gresql-async/src/test/scala/com/github/mauricio/async/db/postgresql/TimeAndDateSpec.scala b/...gresql-async/src/test/scala/com/github/mauricio/async/db/postgresql/TimeAndDateSpec.scala
@@ -17,7 +17,7 @@
 package com.github.mauricio.async.db.postgresql
 
 import org.specs2.mutable.Specification
-import org.joda.time.{LocalTime, DateTime}
+import org.joda.time.{LocalTime, DateTime, Period}
 
 class TimeAndDateSpec extends Specification with DatabaseTestHelper {
 
@@ -200,7 +200,22 @@ class TimeAndDateSpec extends Specification with DatabaseTestHelper {
 
           date2 === date1
       }
+    }
+
+    "support intervals" in {
+      withHandler {
+        handler =>
 
+        executeDdl(handler, "CREATE TEMP TABLE intervals (duration interval NOT NULL)")
+
+        val p = new Period(1,2,0,4,5,6,7,8) /* postgres normalizes weeks */
+        executePreparedStatement(handler, "INSERT INTO intervals (duration) VALUES (?)", Array(p))
+        val rows = executeQuery(handler, "SELECT duration FROM intervals").rows.get
+
+        rows.length === 1
+
+        rows(0)(0) === p
+      }
     }
 
   }

diff --git a/...ql-async/src/test/scala/com/github/mauricio/async/db/postgresql/column/IntervalSpec.scala b/...ql-async/src/test/scala/com/github/mauricio/async/db/postgresql/column/IntervalSpec.scala
@@ -0,0 +1,55 @@
+/*
+ * Copyright 2013 Maurício Linhares
+ * Copyright 2013 Dylan Simon
+ *
+ * Maurício Linhares 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 com.github.mauricio.async.db.postgresql.column
+
+import org.specs2.mutable.Specification
+
+class IntervalSpec extends Specification {
+
+  "interval encoder/decoder" should {
+
+    def decode(s : String) : Any = PostgreSQLIntervalEncoderDecoder.decode(s)
+    def encode(i : Any) : String = PostgreSQLIntervalEncoderDecoder.encode(i)
+    def both(s : String) : String = encode(decode(s))
+
+    "parse and encode example intervals" in {
+      Seq("1-2", "1 year 2 mons", "@ 1 year 2 mons", "@ 1 year 2 mons", "P1Y2M") forall {
+        both(_) === "P1Y2M"
+      }
+      Seq("3 4:05:06", "3 days 04:05:06", "@ 3 days 4 hours 5 mins 6 secs", "P3DT4H5M6S") forall {
+        both(_) === "P3DT4H5M6S"
+      }
+      Seq("1-2 +3 4:05:06", "1 year 2 mons +3 days 04:05:06", "@ 1 year 2 mons 3 days 4 hours 5 mins 6 secs", "P1Y2M3DT4H5M6S") forall {
+        both(_) === "P1Y2M3DT4H5M6S"
+      }
+      Seq("@ 1 year 2 mons -3 days 4 hours 5 mins 6 secs ago", "P-1Y-2M3DT-4H-5M-6S") forall {
+        both(_) === "P-1Y-2M3DT-4H-5M-6S"
+      }
+      both("-1.234") === "PT-1.234S"
+      both("-4:05:06") === "PT-4H-5M-6S"
+    }
+
+    "parse and encode example intervals" in {
+      Seq("-1-2 +3 -4:05:06", "-1 year -2 mons +3 days -04:05:06") forall {
+        both(_) === "P-1Y-2M3DT-4H-5M-6S"
+      }
+    }.pendingUntilFixed("with mixed/grouped negations")
+
+  }
+
+}