Will Glozer avatar Will Glozer committed bfad017

implement binary format of date and time types

Comments (0)

Files changed (5)

   the unnamed prepared statement and portal. PostgreSQL's binary format
   is used to return integers as Erlang integers, floats as floats,
   bytea/text/varchar columns as binaries, bools as true/false, etc.
-  For details see pgsql_binary.erl.
+
+  For details see pgsql_binary.erl and the Data Representation section
+  below.
 
 * Parse/Bind/Execute
 
   ok = pgsql:close(C, Statement).
   ok = pgsql:close(C, statement | portal, Name).
   ok = pgsql:sync(C).
+
+* Data Representation
+
+  null        = null
+  bool        = true | false
+  char        = $A
+  intX        = 1
+  floatX      = 1.0
+  date        = {Year, Month, Day}
+  time        = {Hour, Minute, Second.Microsecond}
+  timetz      = {time, Timezone}
+  timestamp   = {date, time}
+  timestamptz = {date, time}
+  interval    = {time, Days, Months}
+  text        = <<"a">>
+  varchar     = <<"a">>
+  bytea       = <<1, 2>>
+
+  record      = {int2, time, text, ...} (decode only)

src/pgsql_binary.erl

 encode(int8, N)     -> <<8:?int32, N:1/big-signed-unit:64>>;
 encode(float4, N)   -> <<4:?int32, N:1/big-float-unit:32>>;
 encode(float8, N)   -> <<8:?int32, N:1/big-float-unit:64>>;
+encode(Type, B) when Type == time; Type == timetz          -> pgsql_datetime:encode(Type, B);
+encode(Type, B) when Type == date; Type == timestamp       -> pgsql_datetime:encode(Type, B);
+encode(Type, B) when Type == timestamptz; Type == interval -> pgsql_datetime:encode(Type, B);
 encode(bytea, B) when is_binary(B)   -> <<(byte_size(B)):?int32, B/binary>>;
 encode(text, B) when is_binary(B)    -> <<(byte_size(B)):?int32, B/binary>>;
 encode(varchar, B) when is_binary(B) -> <<(byte_size(B)):?int32, B/binary>>;
 decode(float4, <<N:1/big-float-unit:32>>)   -> N;
 decode(float8, <<N:1/big-float-unit:64>>)   -> N;
 decode(record, <<_:?int32, Rest/binary>>)   -> list_to_tuple(decode_record(Rest, []));
-decode(_Other, Bin)                         -> Bin.
+decode(Type, B) when Type == time; Type == timetz          -> pgsql_datetime:decode(Type, B);
+decode(Type, B) when Type == date; Type == timestamp       -> pgsql_datetime:decode(Type, B);
+decode(Type, B) when Type == timestamptz; Type == interval -> pgsql_datetime:decode(Type, B);
+decode(_Other, Bin) -> Bin.
 
 decode_record(<<>>, Acc) ->
     lists:reverse(Acc);
 supports(text)    -> true;
 supports(varchar) -> true;
 supports(record)  -> true;
-supports(_Type)   -> false.
+supports(date)    -> true;
+supports(time)    -> true;
+supports(timetz)  -> true;
+supports(timestamp)   -> true;
+supports(timestamptz) -> true;
+supports(interval)    -> true;
+supports(_Type)       -> false.

src/pgsql_datetime.erl

+%%% Copyright (C) 2008 - Will Glozer.  All rights reserved.
+
+-module(pgsql_datetime).
+
+-export([decode/2, encode/2]).
+
+-define(int16, 1/big-signed-unit:16).
+-define(int32, 1/big-signed-unit:32).
+
+-define(postgres_epoc_jdate, 2451545).
+
+-define(mins_per_hour, 60).
+-define(secs_per_day, 86400.0).
+-define(secs_per_hour, 3600.0).
+-define(secs_per_minute, 60.0).
+
+decode(date, <<J:1/big-signed-unit:32>>)             -> j2date(?postgres_epoc_jdate + J);
+decode(time, <<N:1/big-float-unit:64>>)              -> f2time(N);
+decode(timetz, <<N:1/big-float-unit:64, TZ:?int32>>) -> {f2time(N), TZ};
+decode(timestamp, <<N:1/big-float-unit:64>>)         -> f2timestamp(N);
+decode(timestamptz, <<N:1/big-float-unit:64>>)       -> f2timestamp(N);
+decode(interval, <<N:1/big-float-unit:64, D:?int32, M:?int32>>) -> {f2time(N), D, M}.
+
+encode(date, D)         -> <<4:?int32, (date2j(D) - ?postgres_epoc_jdate):1/big-signed-unit:32>>;
+encode(time, T)         -> <<8:?int32, (time2f(T)):1/big-float-unit:64>>;
+encode(timetz, {T, TZ}) -> <<12:?int32, (time2f(T)):1/big-float-unit:64, TZ:?int32>>;
+encode(timestamp, TS)   -> <<8:?int32, (timestamp2f(TS)):1/big-float-unit:64>>;
+encode(timestamptz, TS) -> <<8:?int32, (timestamp2f(TS)):1/big-float-unit:64>>;
+encode(interval, {T, D, M}) -> <<16:?int32, (time2f(T)):1/big-float-unit:64, D:?int32, M:?int32>>.
+
+j2date(N) ->
+    J = N + 32044,
+    Q1 = J div 146097,
+    Extra = (J - Q1 * 146097) * 4 + 3,
+    J2 = J + 60 + Q1 * 3 + Extra div 146097,
+    Q2 = J2 div 1461,
+    J3 = J2 - Q2 * 1461,
+    Y = J3 * 4 div 1461,
+    case Y of
+        0 -> J4 = ((J3 + 306) rem 366) + 123;
+        _ -> J4 = ((J3 + 305) rem 365) + 123
+    end,
+    Year = (Y + Q2 * 4) - 4800,
+    Q3 = J4 * 2141 div 65536,
+    Day = J4 - 7834 * Q3 div 256,
+    Month = (Q3 + 10) rem 12 + 1,
+    {Year, Month, Day}.
+
+date2j({Y, M, D}) ->
+    case M > 2 of
+        true ->
+            M2 = M + 1,
+            Y2 = Y + 4800;
+        false ->
+            M2 = M + 13,
+            Y2 = Y + 4799
+    end,
+    C = Y2 div 100,
+    J1 = Y2 * 365 - 32167,
+    J2 = J1 + (Y2 div 4 - C + C div 4),
+    J2 + 7834 * M2 div 256 + D.
+
+f2time(N) ->
+    {R1, Hour} = tmodulo(N, ?secs_per_hour),
+    {R2, Min}  = tmodulo(R1, ?secs_per_minute),
+    {R3, Sec}  = tmodulo(R2, 1.0),
+    case timeround(R3) of
+        US when US >= 1.0 -> f2time(ceiling(N));
+        US                -> {Hour, Min, Sec + US}
+    end.
+
+time2f({H, M, S}) ->
+    ((H * ?mins_per_hour + M) * ?secs_per_minute) + S.
+
+f2timestamp(N) ->
+    case tmodulo(N, ?secs_per_day) of
+        {T, D} when T < 0 -> f2timestamp2(D - 1 + ?postgres_epoc_jdate, T + ?secs_per_day);
+        {T, D}            -> f2timestamp2(D + ?postgres_epoc_jdate, T)
+    end.
+
+f2timestamp2(D, T) ->
+    {_H, _M, S} = Time = f2time(T),
+    Date = j2date(D),
+    case tsround(S - trunc(S)) of
+        N when N >= 1.0 ->
+            case ceiling(T) of
+                T2 when T2 > ?secs_per_day -> f2timestamp2(D + 1, 0.0);
+                T2                         -> f2timestamp2(T2, D)
+            end;
+        _ -> ok
+    end,
+    {Date, Time}.
+
+timestamp2f({Date, Time}) ->
+    D = date2j(Date) - ?postgres_epoc_jdate,
+    D * ?secs_per_day + time2f(Time).
+
+tmodulo(T, U) ->
+    case T < 0 of
+        true  -> Q = ceiling(T / U);
+        false -> Q = floor(T / U)
+    end,
+    case Q of
+        0 -> {T, Q};
+        _ -> {T - rint(Q * U), Q}
+    end.
+
+rint(N)      -> round(N) * 1.0.
+timeround(J) -> rint(J * 10000000000.0) / 10000000000.0.
+tsround(J)   -> rint(J * 1000000.0) / 1000000.0.
+
+floor(X) ->
+    T = erlang:trunc(X),
+    case (X - T) of
+        N when N < 0 -> T - 1;
+        N when N > 0 -> T;
+        _            -> T
+    end.
+
+ceiling(X) ->
+    T = erlang:trunc(X),
+    case (X - T) of
+        N when N < 0 -> T;
+        N when N > 0 -> T + 1;
+        _            -> T
+    end.

test_src/pgsql_tests.erl

               {ok, _Cols, [{<<"02.01.2000">>}]} = pgsql:squery(C, "select '2000-01-02'::date")
       end).
 
-decode_binary_format_test() ->
-    with_connection(
-      fun(C) ->
-              {ok, [#column{type = unknown}], [{null}]} = pgsql:equery(C, "select null"),
-              {ok, [#column{type = bool}], [{true}]} = pgsql:equery(C, "select true"),
-              {ok, [#column{type = bool}], [{false}]} = pgsql:equery(C, "select false"),
-              {ok, [#column{type = bpchar}], [{$A}]} = pgsql:equery(C, "select 'A'::char"),
-              {ok, [#column{type = int2}], [{1}]} = pgsql:equery(C, "select 1::int2"),
-              {ok, [#column{type = int2}], [{-1}]} = pgsql:equery(C, "select -1::int2"),
-              {ok, [#column{type = int4}], [{1}]} = pgsql:equery(C, "select 1::int4"),
-              {ok, [#column{type = int4}], [{-1}]} = pgsql:equery(C, "select -1::int4"),
-              {ok, [#column{type = int8}], [{1}]} = pgsql:equery(C, "select 1::int8"),
-              {ok, [#column{type = int8}], [{-1}]} = pgsql:equery(C, "select -1::int8"),
-              {ok, [#column{type = float4}], [{1.0}]} = pgsql:equery(C, "select 1.0::float4"),
-              {ok, [#column{type = float4}], [{-1.0}]} = pgsql:equery(C, "select -1.0::float4"),
-              {ok, [#column{type = float8}], [{1.0}]} = pgsql:equery(C, "select 1.0::float8"),
-              {ok, [#column{type = float8}], [{-1.0}]} = pgsql:equery(C, "select -1.0::float8"),
-              {ok, [#column{type = bytea}], [{<<1, 2>>}]} = pgsql:equery(C, "select E'\001\002'::bytea"),
-              {ok, [#column{type = text}], [{<<"hi">>}]} = pgsql:equery(C, "select 'hi'::text"),
-              {ok, [#column{type = varchar}], [{<<"hi">>}]} = pgsql:equery(C, "select 'hi'::varchar"),
-              {ok, [#column{type = record}], [{{1, null, <<"hi">>}}]} = pgsql:equery(C, "select (1, null, 'hi')")
-      end).
-
-encode_binary_format_test() ->
-    with_connection(
-      fun(C) ->
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_bool) values ($1)", [null]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_bool) values ($1)", [true]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_bool) values ($1)", [false]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_char) values ($1)", [$A]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_int2) values ($1)", [1]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_int2) values ($1)", [-1]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_int4) values ($1)", [1]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_int4) values ($1)", [-1]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_int8) values ($1)", [1]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_int8) values ($1)", [-1]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_float4) values ($1)", [1.0]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_float4) values ($1)", [-1.0]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_float8) values ($1)", [1.0]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_float8) values ($1)", [-1.0]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_bytea) values ($1)", [<<1, 2>>]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_bytea) values ($1)", [[1, 2]]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_text) values ($1)", [<<"hi">>]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_text) values ($1)", ["hi"]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_varchar) values ($1)", [<<"hi">>]),
-              {ok, 1} = pgsql:equery(C, "insert into test_table2 (c_varchar) values ($1)", ["hi"])
-      end).
+type_test() ->
+    check_type(bool, "true", true, [true, false]),
+    check_type(bpchar, "'A'", $A, [1, $1, 255], "c_char"),
+    check_type(int2, "1", 1, [0, 256, -32768, +32767]),
+    check_type(int4, "1", 1, [0, 512, -2147483648, +2147483647]),
+    check_type(int8, "1", 1, [0, 1024, -9223372036854775808, +9223372036854775807]),
+    check_type(float4, "1.0", 1.0, [0.0, 1.23456, -1.23456]),
+    check_type(float8, "1.0", 1.0, [0.0, 1.23456789012345, -1.23456789012345]),
+    check_type(bytea, "E'\001\002'", <<1,2>>, [<<>>, <<0,128,255>>]),
+    check_type(text, "'hi'", <<"hi">>, [<<"">>, <<"hi">>]),
+    check_type(varchar, "'hi'", <<"hi">>, [<<"">>, <<"hi">>]),
+    check_type(date, "'2008-01-02'", {2008,1,2}, [{-4712,1,1}, {5874897,1,1}]),
+    check_type(time, "'00:01:02'", {0,1,2.0}, [{0,0,0.0}, {24,0,0.0}]),
+    check_type(timetz, "'00:01:02-01'", {{0,1,2.0},1*60*60}, [{{0,0,0.0},0}, {{24,0,0.0},-13*60*60}]),
+    check_type(timestamp, "'2008-01-02 03:04:05'", {{2008,1,2},{3,4,5.0}},
+               [{{-4712,1,1},{0,0,0.0}}, {{5874897,12,31}, {23,59,59.0}}]),
+    check_type(interval, "'1 hour 2 minutes 3.1 seconds'", {{1,2,3.1},0,0},
+               [{{0,0,0.0},0,-178000000 * 12}, {{0,0,0.0},0,178000000 * 12}]).
 
 text_format_test() ->
     with_connection(
                                {ok, _Cols, [{V2}]} = pgsql:equery(C, Query, [V]),
                                {ok, _Cols, [{V2}]} = pgsql:equery(C, Query, [V2])
                        end,
-              Select("timestamp", "2000-01-02 03:04:05"),
-              Select("date", "2000-01-02"),
-              Select("time", "03:04:05"),
+              Select("inet", "127.0.0.1"),
               Select("numeric", "123456")
       end).
 
                   end
       end).
 
+check_type(Type, In, Out, Values) ->
+    Column = "c_" ++ atom_to_list(Type),
+    check_type(Type, In, Out, Values, Column).
+
+check_type(Type, In, Out, Values, Column) ->
+    with_connection(
+      fun(C) ->
+              Select = io_lib:format("select ~s::~w", [In, Type]),
+              {ok, [#column{type = Type}], [{Out}]} = pgsql:equery(C, Select),
+              Sql = io_lib:format("insert into test_table2 (~s) values ($1) returning ~s", [Column, Column]),
+              {ok, #statement{columns = [#column{type = Type}]} = S} = pgsql:parse(C, Sql),
+              Insert = fun(V) ->
+                               pgsql:bind(C, S, [V]),
+                               {ok, 1, [{V2}]} = pgsql:execute(C, S),
+                               case compare(Type, V, V2) of
+                                   true  -> ok;
+                                   false -> ?debugFmt("~p =/= ~p~n", [V, V2]), ?assert(false)
+                               end,
+                               ok = pgsql:sync(C)
+                       end,
+              lists:foreach(Insert, [null | Values])
+      end).
+
+compare(_Type, null, null) -> true;
+compare(float4, V1, V2)    -> abs(V2 - V1) < 0.000001;
+compare(float8, V1, V2)    -> abs(V2 - V1) < 0.000000000000001;
+compare(_Type, V1, V2)     -> V1 =:= V2.
+
 %% flush mailbox
 flush() ->
     ?assertEqual([], flush([])).

test_src/test_schema.sql

   c_float8 float8,
   c_bytea bytea,
   c_text text,
-  c_varchar varchar(64));
+  c_varchar varchar(64),
+  c_date date,
+  c_time time,
+  c_timetz timetz,
+  c_timestamp timestamp,
+  c_timestamptz timestamptz,
+  c_interval interval);
 
 CREATE LANGUAGE plpgsql;
 
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.