| 1 | package DBIO::DuckDB::Arrow; | ||
| 2 | # ABSTRACT: Read a DuckDB result through libduckdb's Arrow interface | ||
| 3 | |||
| 4 | 1 | ▶ | use strict; |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 5 | 1 | ▶ | use warnings; |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 6 | |||
| 7 | our $VERSION = '0.900007'; | ||
| 8 | |||
| 9 | 1 | ▶ | use Config; |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 10 | 1 | ▶ | use Try::Tiny; |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 11 | 1 | ▶ | use Math::BigInt (); |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 12 | 1 | ▶ | use FFI::Platypus 2.00 (); |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 13 | 1 | ▶ | use FFI::Platypus::Memory (); |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 14 | 1 | ▶ | use FFI::Platypus::Buffer (); |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 15 | 1 | ▶ | use FFI::CheckLib (); |
|
Subroutine
BEGIN: called CC=1 SCAR=0.0
| |||
| 16 | 0 | ▶ | use DBD::DuckDB::FFI (); |
|
Subroutine
BEGIN: called CC=1 SCAR=2.6
| |||
| 17 | use DBIO::DuckDB::Util (); | ||
| 18 | |||
| 19 | # Arrow C data interface, 64 bit: struct ArrowSchema is 9 words, struct | ||
| 20 | # ArrowArray 10. The 'Q' templates and the 72 and 80 byte sizes below assume | ||
| 21 | # 8 byte pointers, hence the check. | ||
| 22 | die "DBIO::DuckDB::Arrow needs a perl with 64 bit pointers\n" if $Config{ptrsize} != 8; | ||
| 23 | my $SCHEMA_BYTES = 72; | ||
| 24 | my $ARRAY_BYTES = 80; | ||
| 25 | |||
| 26 | # DuckDB's type ids (duckdb.h, duckdb_type) the decoder tells apart or refuses. | ||
| 27 | my %TYPE_NAME = ( | ||
| 28 | 0 => 'INVALID', 28 => 'UNION', 29 => 'BIT', 30 => 'TIME WITH TIME ZONE', | ||
| 29 | 34 => 'ANY', 35 => 'BIGNUM', 40 => 'GEOMETRY', 41 => 'VARIANT', | ||
| 30 | ); | ||
| 31 | my $TYPE_UHUGEINT = 32; | ||
| 32 | my $TYPE_LIST = 24; | ||
| 33 | my $TYPE_STRUCT = 25; | ||
| 34 | my $TYPE_MAP = 26; | ||
| 35 | my $TYPE_ARRAY = 33; | ||
| 36 | my %NESTED = map { $_ => 1 } $TYPE_LIST, $TYPE_STRUCT, $TYPE_MAP, $TYPE_ARRAY; | ||
| 37 | |||
| 38 | # Arrow format => [ bytes, pack template ] of the fixed width numbers. | ||
| 39 | my %NUMBER = ( | ||
| 40 | c => [ 1, 'c' ], s => [ 2, 's<' ], i => [ 4, 'l<' ], l => [ 8, 'q<' ], | ||
| 41 | C => [ 1, 'C' ], S => [ 2, 'S<' ], I => [ 4, 'L<' ], L => [ 8, 'Q<' ], | ||
| 42 | f => [ 4, 'f<' ], g => [ 8, 'd<' ], | ||
| 43 | ); | ||
| 44 | |||
| 45 | # timestamp unit => [ units per second, digits of the fraction ] | ||
| 46 | my %UNIT = ( | ||
| 47 | s => [ 1, 0 ], m => [ 1_000, 3 ], u => [ 1_000_000, 6 ], n => [ 1_000_000_000, 9 ], | ||
| 48 | ); | ||
| 49 | |||
| 50 | my $TWO64 = Math::BigInt->new(2)->bpow(64); | ||
| 51 | |||
| 52 | my $FFI = FFI::Platypus->new( | ||
| 53 | api => 2, | ||
| 54 | lib => FFI::CheckLib::find_lib_or_die( | ||
| 55 | lib => 'duckdb', | ||
| 56 | alien => ($ENV{DUCKDB_NO_ALIEN} ? undef : 'Alien::DuckDB'), | ||
| 57 | ), | ||
| 58 | ); | ||
| 59 | # DBD::DuckDB::FFI binds the arrays of this call as single pointers. | ||
| 60 | $FFI->attach( | ||
| 61 | [ duckdb_to_arrow_schema => '_to_arrow_schema' ] => | ||
| 62 | [ 'opaque', 'opaque[]', 'string[]', 'uint64', 'opaque' ] => 'opaque', | ||
| 63 | ); | ||
| 64 | |||
| 65 | my %RELEASE; | ||
| 66 | sub _release { | ||
| 67 | my ($struct, $release_at) = @_; | ||
| 68 | my $fn = unpack 'Q', FFI::Platypus::Buffer::buffer_to_scalar($struct + $release_at, 8); | ||
| 69 | return unless $fn; | ||
| 70 | ($RELEASE{$fn} ||= $FFI->function($fn => ['opaque'] => 'void'))->call($struct); | ||
| 71 | return; | ||
| 72 | } | ||
| 73 | |||
| 74 | sub _read { FFI::Platypus::Buffer::buffer_to_scalar($_[0], $_[1]) } | ||
| 75 | |||
| 76 | sub _string { | ||
| 77 | my $ptr = shift; | ||
| 78 | return $ptr ? $FFI->cast('opaque', 'string', $ptr) : undef; | ||
| 79 | } | ||
| 80 | |||
| 81 | sub _error { | ||
| 82 | my ($err) = @_; | ||
| 83 | return unless $err; | ||
| 84 | my $message; | ||
| 85 | if (DBD::DuckDB::FFI::duckdb_error_data_has_error($err)) { | ||
| 86 | $message = DBD::DuckDB::FFI::duckdb_error_data_message($err); | ||
| 87 | } | ||
| 88 | DBD::DuckDB::FFI::duckdb_destroy_error_data(\$err); | ||
| 89 | return $message; | ||
| 90 | } | ||
| 91 | |||
| 92 | # ---- DuckDB's logical types, as a tree of ids --------------------------- | ||
| 93 | |||
| 94 | sub _type_tree { | ||
| 95 | my ($lt) = @_; | ||
| 96 | my $id = DBD::DuckDB::FFI::duckdb_get_type_id($lt); | ||
| 97 | my @kids; | ||
| 98 | my $child = sub { | ||
| 99 | my $t = shift; | ||
| 100 | push @kids, _type_tree($t); | ||
| 101 | DBD::DuckDB::FFI::duckdb_destroy_logical_type(\$t); | ||
| 102 | }; | ||
| 103 | if ($id == $TYPE_LIST) { | ||
| 104 | $child->(DBD::DuckDB::FFI::duckdb_list_type_child_type($lt)); | ||
| 105 | } | ||
| 106 | elsif ($id == $TYPE_ARRAY) { | ||
| 107 | $child->(DBD::DuckDB::FFI::duckdb_array_type_child_type($lt)); | ||
| 108 | } | ||
| 109 | elsif ($id == $TYPE_MAP) { | ||
| 110 | $child->(DBD::DuckDB::FFI::duckdb_map_type_key_type($lt)); | ||
| 111 | $child->(DBD::DuckDB::FFI::duckdb_map_type_value_type($lt)); | ||
| 112 | } | ||
| 113 | elsif ($id == $TYPE_STRUCT) { | ||
| 114 | $child->(DBD::DuckDB::FFI::duckdb_struct_type_child_type($lt, $_)) | ||
| 115 | for 0 .. DBD::DuckDB::FFI::duckdb_struct_type_child_count($lt) - 1; | ||
| 116 | } | ||
| 117 | return { id => $id, kids => \@kids }; | ||
| 118 | } | ||
| 119 | |||
| 120 | # ---- the plan: one decoder per column, made once from schema and types ---- | ||
| 121 | |||
| 122 | sub _refuse { | ||
| 123 | my ($column, $format, $type) = @_; | ||
| 124 | die sprintf "column '%s' has Arrow format '%s'%s, which cannot be decoded\n", | ||
| 125 | $column, $format, defined $type ? " (DuckDB type $type)" : ''; | ||
| 126 | } | ||
| 127 | |||
| 128 | sub _plan { | ||
| 129 | my ($schema, $node, $column) = @_; | ||
| 130 | |||
| 131 | my ($format_at, $name_at, undef, undef, $n_children, $children, $dictionary) | ||
| 132 | = unpack 'Q Q Q q q Q Q', _read($schema, 56); | ||
| 133 | my $format = _string($format_at); | ||
| 134 | my $id = $node ? $node->{id} : -1; | ||
| 135 | |||
| 136 | _refuse($column, $format, $TYPE_NAME{$id}) if $TYPE_NAME{$id}; | ||
| 137 | |||
| 138 | my @kid_schemas = $n_children ? unpack("Q$n_children", _read($children, 8 * $n_children)) : (); | ||
| 139 | my @kid_nodes = $node ? @{ $node->{kids} } : (); | ||
| 140 | my $plan = { format => $format }; | ||
| 141 | |||
| 142 | if ($dictionary) { | ||
| 143 | # DuckDB's ENUM: small integers indexing a dictionary of text. | ||
| 144 | _refuse($column, $format) unless $NUMBER{$format} and $format =~ /\A[cCsSiI]\z/; | ||
| 145 | my $index = _number_decoder($format); | ||
| 146 | my $dict = _plan($dictionary, undef, $column); | ||
| 147 | _refuse($column, $dict->{format}) unless $dict->{format} eq 'u'; | ||
| 148 | $plan->{decode} = sub { | ||
| 149 | my ($array) = @_; | ||
| 150 | my (undef, undef, undef, undef, undef, undef, undef, $dict_array) = unpack 'q q q q q Q Q Q', _read($array, 64); | ||
| 151 | my $values = $dict->{decode}->($dict_array); | ||
| 152 | return [ map { defined $_ ? $values->[$_] : undef } @{ $index->($array) } ]; | ||
| 153 | }; | ||
| 154 | } | ||
| 155 | elsif ($NUMBER{$format}) { | ||
| 156 | $plan->{decode} = _number_decoder($format); | ||
| 157 | } | ||
| 158 | elsif ($format eq 'b') { | ||
| 159 | $plan->{decode} = sub { | ||
| 160 | my ($len, $off, $buffers) = _array_head($_[0]); | ||
| 161 | return [] unless $len; | ||
| 162 | my $bits = unpack 'b*', _read($buffers->[1] + ($off >> 3), (($off & 7) + $len + 7) >> 3); | ||
| 163 | return [ map { $_ ? !!1 : !!0 } split //, substr($bits, $off & 7, $len) ]; | ||
| 164 | }; | ||
| 165 | } | ||
| 166 | elsif ($format eq 'u' or $format eq 'z') { | ||
| 167 | my $text = $format eq 'u'; | ||
| 168 | $plan->{decode} = sub { | ||
| 169 | my ($len, $off, $buffers) = _array_head($_[0]); | ||
| 170 | return [] unless $len; | ||
| 171 | my @o = unpack 'l<' . ($len + 1), _read($buffers->[1] + 4 * $off, 4 * ($len + 1)); | ||
| 172 | my $data = $o[-1] > $o[0] ? _read($buffers->[2] + $o[0], $o[-1] - $o[0]) : ''; | ||
| 173 | my $base = $o[0]; | ||
| 174 | my @v = map { substr $data, $o[$_] - $base, $o[$_ + 1] - $o[$_] } 0 .. $len - 1; | ||
| 175 | if ($text) { utf8::decode($_) for @v } | ||
| 176 | return \@v; | ||
| 177 | }; | ||
| 178 | } | ||
| 179 | elsif ($format =~ /\Ad:(\d+),(\d+)(?:,(\d+))?\z/) { | ||
| 180 | my ($scale, $bits) = ($2, $3 // 128); | ||
| 181 | _refuse($column, $format) unless $bits == 128; | ||
| 182 | my $unsigned = $id == $TYPE_UHUGEINT; | ||
| 183 | $plan->{decode} = sub { | ||
| 184 | my ($len, $off, $buffers) = _array_head($_[0]); | ||
| 185 | return [] unless $len; | ||
| 186 | my @w = unpack 'q<*', _read($buffers->[1] + 16 * $off, 16 * $len); | ||
| 187 | return [ map { _decimal($w[2 * $_], $w[2 * $_ + 1], $scale, $unsigned) } 0 .. $len - 1 ]; | ||
| 188 | }; | ||
| 189 | } | ||
| 190 | elsif ($format eq 'tdD') { | ||
| 191 | $plan->{decode} = sub { | ||
| 192 | my ($len, $off, $buffers) = _array_head($_[0]); | ||
| 193 | return [] unless $len; | ||
| 194 | return [ map { _infinite($_, 2147483647) // _date($_) } unpack 'l<' . $len, _read($buffers->[1] + 4 * $off, 4 * $len) ]; | ||
| 195 | }; | ||
| 196 | } | ||
| 197 | elsif ($format eq 'ttu') { | ||
| 198 | $plan->{decode} = sub { | ||
| 199 | my ($len, $off, $buffers) = _array_head($_[0]); | ||
| 200 | return [] unless $len; | ||
| 201 | return [ map { _time($_) } unpack 'q<' . $len, _read($buffers->[1] + 8 * $off, 8 * $len) ]; | ||
| 202 | }; | ||
| 203 | } | ||
| 204 | elsif ($format =~ /\Ats([smun]):(.*)\z/s) { | ||
| 205 | my ($per_second, $digits) = @{ $UNIT{$1} }; | ||
| 206 | my $zoned = length $2; | ||
| 207 | $plan->{decode} = sub { | ||
| 208 | my ($len, $off, $buffers) = _array_head($_[0]); | ||
| 209 | return [] unless $len; | ||
| 210 | return [ map { _infinite($_, 9223372036854775807) // _timestamp($_, $per_second, $digits, $zoned) } | ||
| 211 | unpack 'q<' . $len, _read($buffers->[1] + 8 * $off, 8 * $len) ]; | ||
| 212 | }; | ||
| 213 | } | ||
| 214 | elsif ($format eq 'tin') { | ||
| 215 | $plan->{decode} = sub { | ||
| 216 | my ($len, $off, $buffers) = _array_head($_[0]); | ||
| 217 | return [] unless $len; | ||
| 218 | my @w = unpack 'l< l< q<' x $len, _read($buffers->[1] + 16 * $off, 16 * $len); | ||
| 219 | return [ map { _interval(@w[3 * $_ .. 3 * $_ + 2]) } 0 .. $len - 1 ]; | ||
| 220 | }; | ||
| 221 | } | ||
| 222 | elsif ($format eq '+l' or $format =~ /\A\+w:\d+\z/) { | ||
| 223 | my ($fixed) = $format =~ /\A\+w:(\d+)\z/; | ||
| 224 | my $kid = _plan($kid_schemas[0], $kid_nodes[0], $column); | ||
| 225 | $plan->{decode} = sub { | ||
| 226 | my ($len, $off, $buffers, $kids) = _array_head($_[0]); | ||
| 227 | return [] unless $len; | ||
| 228 | my $values = $kid->{decode}->($kids->[0]); | ||
| 229 | if (defined $fixed) { | ||
| 230 | return [ map { my $at = ($off + $_) * $fixed; [ @$values[$at .. $at + $fixed - 1] ] } 0 .. $len - 1 ]; | ||
| 231 | } | ||
| 232 | my @o = unpack 'l<' . ($len + 1), _read($buffers->[1] + 4 * $off, 4 * ($len + 1)); | ||
| 233 | return [ map { [ @$values[$o[$_] .. $o[$_ + 1] - 1] ] } 0 .. $len - 1 ]; | ||
| 234 | }; | ||
| 235 | } | ||
| 236 | elsif ($format eq '+s') { | ||
| 237 | my @kids = map { _plan($kid_schemas[$_], $kid_nodes[$_], $column) } 0 .. $#kid_schemas; | ||
| 238 | my @names = map { _string((unpack 'Q Q', _read($_, 16))[1]) } @kid_schemas; | ||
| 239 | $plan->{decode} = sub { | ||
| 240 | my ($len, $off, undef, $kids) = _array_head($_[0]); | ||
| 241 | return [] unless $len; | ||
| 242 | my @col = map { $kids[$_]{decode}->($kids->[$_]) } 0 .. $#kids; | ||
| 243 | return [ map { my $i = $off + $_; +{ map { $names[$_] => $col[$_][$i] } 0 .. $#kids } } 0 .. $len - 1 ]; | ||
| 244 | }; | ||
| 245 | } | ||
| 246 | elsif ($format eq '+m') { | ||
| 247 | my (undef, undef, undef, undef, $n_entry, $entry_children) | ||
| 248 | = unpack 'Q Q Q q q Q', _read($kid_schemas[0], 48); | ||
| 249 | my @entry = unpack "Q$n_entry", _read($entry_children, 8 * $n_entry); | ||
| 250 | my $key = _plan($entry[0], $kid_nodes[0], $column); | ||
| 251 | my $value = _plan($entry[1], $kid_nodes[1], $column); | ||
| 252 | # a hash key is text; a nested key has none | ||
| 253 | die sprintf "column '%s' has Arrow format '%s' with a key of Arrow format '%s', which cannot be decoded\n", | ||
| 254 | $column, $format, $key->{format} | ||
| 255 | if $kid_nodes[0] and $NESTED{ $kid_nodes[0]{id} }; | ||
| 256 | $plan->{decode} = sub { | ||
| 257 | my ($len, $off, $buffers, $kids) = _array_head($_[0]); | ||
| 258 | return [] unless $len; | ||
| 259 | # the entries are a struct array of their own: its offset and validity count | ||
| 260 | my (undef, $entry_off, undef, $entry_kids) = _array_head($kids->[0]); | ||
| 261 | my @pair = @$entry_kids; | ||
| 262 | my $keys = $key->{decode}->($pair[0]); | ||
| 263 | my $values = $value->{decode}->($pair[1]); | ||
| 264 | my $valid = _validity($kids->[0]); | ||
| 265 | my @o = unpack 'l<' . ($len + 1), _read($buffers->[1] + 4 * $off, 4 * ($len + 1)); | ||
| 266 | return [ map { | ||
| 267 | +{ map { $keys->[$entry_off + $_] => $values->[$entry_off + $_] } | ||
| 268 | grep { !$valid or $valid->[$_] } $o[$_] .. $o[$_ + 1] - 1 } | ||
| 269 | } 0 .. $len - 1 ]; | ||
| 270 | }; | ||
| 271 | } | ||
| 272 | else { | ||
| 273 | _refuse($column, $format, $TYPE_NAME{$id}); | ||
| 274 | } | ||
| 275 | |||
| 276 | # Arrow's validity bitmap, read only when the block has NULLs. | ||
| 277 | my $values = $plan->{decode}; | ||
| 278 | $plan->{decode} = sub { | ||
| 279 | my $out = $values->($_[0]); | ||
| 280 | my $valid = _validity($_[0]); | ||
| 281 | if ($valid) { | ||
| 282 | $out->[$_] = undef for grep { !$valid->[$_] } 0 .. $#$valid; | ||
| 283 | } | ||
| 284 | return $out; | ||
| 285 | }; | ||
| 286 | |||
| 287 | return $plan; | ||
| 288 | } | ||
| 289 | |||
| 290 | # One flag per value, from the validity bitmap; undef when the block has no | ||
| 291 | # NULLs (the bitmap is not read then). | ||
| 292 | sub _validity { | ||
| 293 | my ($len, $off, $buffers, undef, $nulls) = _array_head($_[0]); | ||
| 294 | return unless $nulls and $buffers->[0] and $len; | ||
| 295 | my $bits = unpack 'b*', _read($buffers->[0] + ($off >> 3), (($off & 7) + $len + 7) >> 3); | ||
| 296 | return [ split //, substr($bits, $off & 7, $len) ]; | ||
| 297 | } | ||
| 298 | |||
| 299 | # ($length, $offset, \@buffers, \@children, $null_count, ...) of an ArrowArray. | ||
| 300 | sub _array_head_raw { | ||
| 301 | return unpack 'q q q q q Q Q', _read($_[0], 56); | ||
| 302 | } | ||
| 303 | |||
| 304 | sub _array_head { | ||
| 305 | my ($len, $nulls, $off, $n_buffers, $n_children, $buffers, $children) = _array_head_raw($_[0]); | ||
| 306 | my @b = $n_buffers ? unpack("Q$n_buffers", _read($buffers, 8 * $n_buffers)) : (); | ||
| 307 | my @c = $n_children ? unpack("Q$n_children", _read($children, 8 * $n_children)) : (); | ||
| 308 | return ($len, $off, \@b, \@c, $nulls); | ||
| 309 | } | ||
| 310 | |||
| 311 | sub _number_decoder { | ||
| 312 | my ($format) = @_; | ||
| 313 | my ($size, $template) = @{ $NUMBER{$format} }; | ||
| 314 | return sub { | ||
| 315 | my ($len, $off, $buffers) = _array_head($_[0]); | ||
| 316 | return [] unless $len; | ||
| 317 | return [ unpack $template . $len, _read($buffers->[1] + $size * $off, $size * $len) ]; | ||
| 318 | }; | ||
| 319 | } | ||
| 320 | |||
| 321 | # ---- values ------------------------------------------------------------- | ||
| 322 | |||
| 323 | # 128 bit two's complement, little-endian (as two signed words), to a decimal | ||
| 324 | # string with $scale digits behind the point. | ||
| 325 | sub _decimal { | ||
| 326 | my ($lo, $hi, $scale, $unsigned) = @_; | ||
| 327 | my $digits; | ||
| 328 | my $negative = 0; | ||
| 329 | if (!$unsigned and $hi == 0 and $lo >= 0) { | ||
| 330 | $digits = "$lo"; | ||
| 331 | } | ||
| 332 | elsif (!$unsigned and $hi == -1 and $lo < 0 and $lo > -9223372036854775807 - 1) { | ||
| 333 | $digits = -$lo; | ||
| 334 | $negative = 1; | ||
| 335 | } | ||
| 336 | else { | ||
| 337 | my $lo_u = Math::BigInt->new($lo); | ||
| 338 | $lo_u->badd($TWO64) if $lo < 0; | ||
| 339 | my $v = Math::BigInt->new($hi); | ||
| 340 | $v->badd($TWO64) if $unsigned and $hi < 0; | ||
| 341 | $v->bmul($TWO64)->badd($lo_u); | ||
| 342 | $negative = $v->is_neg; | ||
| 343 | $digits = $v->babs->bstr; | ||
| 344 | } | ||
| 345 | if ($scale) { | ||
| 346 | $digits = ('0' x ($scale - length($digits) + 1)) . $digits if length($digits) <= $scale; | ||
| 347 | substr($digits, -$scale, 0) = '.'; | ||
| 348 | } | ||
| 349 | return $negative ? "-$digits" : $digits; | ||
| 350 | } | ||
| 351 | |||
| 352 | # Days since 1970-01-01 to YYYY-MM-DD (proleptic Gregorian). | ||
| 353 | sub _civil { | ||
| 354 | my ($days) = @_; | ||
| 355 | use integer; | ||
| 356 | my $z = $days + 719468; | ||
| 357 | my $era = ($z >= 0 ? $z : $z - 146096) / 146097; | ||
| 358 | my $doe = $z - $era * 146097; | ||
| 359 | my $yoe = ($doe - $doe / 1460 + $doe / 36524 - $doe / 146096) / 365; | ||
| 360 | my $doy = $doe - (365 * $yoe + $yoe / 4 - $yoe / 100); | ||
| 361 | my $mp = (5 * $doy + 2) / 153; | ||
| 362 | my $d = $doy - (153 * $mp + 2) / 5 + 1; | ||
| 363 | my $m = $mp < 10 ? $mp + 3 : $mp - 9; | ||
| 364 | my $y = $yoe + $era * 400 + ($m <= 2 ? 1 : 0); | ||
| 365 | return ($y, $m, $d); | ||
| 366 | } | ||
| 367 | |||
| 368 | # DuckDB writes +infinity as the largest value of the storage type and | ||
| 369 | # -infinity as the smallest plus one; its text for them is the word. | ||
| 370 | sub _infinite { | ||
| 371 | my ($v, $max) = @_; | ||
| 372 | return $v == $max ? 'infinity' : $v == -$max ? '-infinity' : undef; | ||
| 373 | } | ||
| 374 | |||
| 375 | # DuckDB's text: years up to 0 are written as 1 - year with " (BC)". | ||
| 376 | sub _date { | ||
| 377 | my ($y, $m, $d) = _civil($_[0]); | ||
| 378 | return $y > 0 ? sprintf('%04d-%02d-%02d', $y, $m, $d) : sprintf('%04d-%02d-%02d (BC)', 1 - $y, $m, $d); | ||
| 379 | } | ||
| 380 | |||
| 381 | # floor division and remainder, for epochs before 1970 | ||
| 382 | sub _divmod { | ||
| 383 | my ($n, $d) = @_; | ||
| 384 | use integer; | ||
| 385 | my $q = $n / $d; | ||
| 386 | my $r = $n - $q * $d; | ||
| 387 | if ($r < 0) { $q--; $r += $d } | ||
| 388 | return ($q, $r); | ||
| 389 | } | ||
| 390 | |||
| 391 | sub _clock { | ||
| 392 | my ($seconds) = @_; | ||
| 393 | return sprintf '%02d:%02d:%02d', int($seconds / 3600), int(($seconds % 3600) / 60), $seconds % 60; | ||
| 394 | } | ||
| 395 | |||
| 396 | # TIME: microseconds since midnight; DuckDB's text has the fraction without | ||
| 397 | # trailing zeros, and none when it is zero. | ||
| 398 | sub _time { | ||
| 399 | my ($micros) = @_; | ||
| 400 | my ($s, $f) = _divmod($micros, 1_000_000); | ||
| 401 | my $fraction = $f ? sprintf('.%06d', $f) : ''; | ||
| 402 | $fraction =~ s/0+\z//; | ||
| 403 | return _clock($s) . $fraction; | ||
| 404 | } | ||
| 405 | |||
| 406 | sub _timestamp { | ||
| 407 | my ($v, $per_second, $digits, $zoned) = @_; | ||
| 408 | my ($s, $f) = _divmod($v, $per_second); | ||
| 409 | my ($days, $t) = _divmod($s, 86400); | ||
| 410 | my $fraction = $f ? sprintf(".%0${digits}d", $f) : ''; | ||
| 411 | # DuckDB's own text: the fraction has no trailing zeros | ||
| 412 | $fraction =~ s/0+\z//; | ||
| 413 | return _date($days) . ' ' . _clock($t) . $fraction . ($zoned ? '+00' : ''); | ||
| 414 | } | ||
| 415 | |||
| 416 | # DuckDB's own text form of an INTERVAL: months and days, then the time. | ||
| 417 | sub _interval { | ||
| 418 | my ($months, $days, $nanos) = @_; | ||
| 419 | my @part; | ||
| 420 | my ($years, $mon) = (int($months / 12), $months % 12); | ||
| 421 | $mon -= 12 if $months < 0 and $mon > 0; | ||
| 422 | my $unit = sub { push @part, "$_[0] $_[1]" . (abs($_[0]) == 1 ? '' : 's') if $_[0] }; | ||
| 423 | $unit->($years, 'year'); | ||
| 424 | $unit->($mon, 'month'); | ||
| 425 | $unit->($days, 'day'); | ||
| 426 | if ($nanos) { | ||
| 427 | my $micros = int($nanos / 1000); | ||
| 428 | my $sign = $micros < 0 ? '-' : ''; | ||
| 429 | $micros = abs $micros; | ||
| 430 | my ($s, $f) = (int($micros / 1_000_000), $micros % 1_000_000); | ||
| 431 | my $fraction = $f ? sprintf('.%06d', $f) : ''; | ||
| 432 | $fraction =~ s/0+\z//; | ||
| 433 | push @part, $sign . sprintf('%02d:%02d:%02d', int($s / 3600), int(($s % 3600) / 60), $s % 60) . $fraction; | ||
| 434 | } | ||
| 435 | return @part ? join(' ', @part) : '00:00:00'; | ||
| 436 | } | ||
| 437 | |||
| 438 | # ---- the fetch ---------------------------------------------------------- | ||
| 439 | |||
| 440 | |||
| 441 | sub fetch { | ||
| 442 | my ($class, $dbh, $sql, $binds, $mask) = @_; | ||
| 443 | $mask //= sub { $_[0] }; | ||
| 444 | |||
| 445 | my $sth; | ||
| 446 | my @cleanup; | ||
| 447 | my $result = try { | ||
| 448 | $sth = $dbh->prepare($sql); | ||
| 449 | $sth->execute(@{ $binds // [] }); | ||
| 450 | |||
| 451 | my $res = $sth->{duckdb_res}; | ||
| 452 | my @types = @{ $sth->{duckdb_col_logical_types} // [] }; | ||
| 453 | my @names = @{ $sth->{NAME} }; | ||
| 454 | my $n = @names; | ||
| 455 | |||
| 456 | my @trees = map { _type_tree($_) } @types; | ||
| 457 | |||
| 458 | my $options = DBD::DuckDB::FFI::duckdb_result_get_arrow_options($res); | ||
| 459 | push @cleanup, sub { DBD::DuckDB::FFI::duckdb_destroy_arrow_options(\$options) }; | ||
| 460 | |||
| 461 | my $schema = FFI::Platypus::Memory::calloc(1, $SCHEMA_BYTES); | ||
| 462 | push @cleanup, sub { FFI::Platypus::Memory::free($schema) }; | ||
| 463 | my $failure = _error(_to_arrow_schema($options, \@types, \@names, $n, $schema)); | ||
| 464 | die $mask->($failure) . "\n" if defined $failure; | ||
| 465 | push @cleanup, sub { _release($schema, 56) }; | ||
| 466 | |||
| 467 | my (undef, undef, undef, undef, undef, $children) = unpack 'Q Q Q q q Q', _read($schema, 48); | ||
| 468 | my @column_schemas = $n ? unpack("Q$n", _read($children, 8 * $n)) : (); | ||
| 469 | |||
| 470 | my @plans = map { _plan($column_schemas[$_], $trees[$_], $names[$_]) } 0 .. $n - 1; | ||
| 471 | |||
| 472 | my @columns = map { [] } 1 .. $n; | ||
| 473 | my $rows = 0; | ||
| 474 | while (my $chunk = DBD::DuckDB::FFI::duckdb_fetch_chunk($res)) { | ||
| 475 | my $array = FFI::Platypus::Memory::calloc(1, $ARRAY_BYTES); | ||
| 476 | try { | ||
| 477 | my $failure = _error(DBD::DuckDB::FFI::duckdb_data_chunk_to_arrow($options, $chunk, $array)); | ||
| 478 | die $mask->($failure) . "\n" if defined $failure; | ||
| 479 | my ($len, $off, undef, $kids) = _array_head($array); | ||
| 480 | for my $c (0 .. $n - 1) { | ||
| 481 | push @{ $columns[$c] }, @{ $plans[$c]{decode}->($kids->[$c]) }; | ||
| 482 | } | ||
| 483 | $rows += $len; | ||
| 484 | } | ||
| 485 | catch { die $_ } | ||
| 486 | finally { | ||
| 487 | _release($array, 64); | ||
| 488 | FFI::Platypus::Memory::free($array); | ||
| 489 | my $c = $chunk; | ||
| 490 | DBD::DuckDB::FFI::duckdb_destroy_data_chunk(\$c); | ||
| 491 | }; | ||
| 492 | } | ||
| 493 | |||
| 494 | +{ | ||
| 495 | names => \@names, | ||
| 496 | formats => [ map { $_->{format} } @plans ], | ||
| 497 | columns => \@columns, | ||
| 498 | rows => $rows, | ||
| 499 | }; | ||
| 500 | } | ||
| 501 | catch { die $_ } | ||
| 502 | finally { | ||
| 503 | for my $step (reverse @cleanup) { eval { $step->(); 1 } } | ||
| 504 | eval { $sth->finish } if $sth; | ||
| 505 | }; | ||
| 506 | |||
| 507 | return $result; | ||
| 508 | } | ||
| 509 | |||
| 510 | 1; | ||
| 511 | |||
| 512 | __END__ | ||