Changeset: 19eadb234db5 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/19eadb234db5
Modified Files:
sql/test/bincopy/Tests/bincopy_support.py
Branch: copyintobinary
Log Message:
Extend existing COPY BINARY INTO tests to also test COPY INTO BINARY
diffs (truncated from 317 to 300 lines):
diff --git a/sql/test/bincopy/Tests/bincopy_support.py
b/sql/test/bincopy/Tests/bincopy_support.py
--- a/sql/test/bincopy/Tests/bincopy_support.py
+++ b/sql/test/bincopy/Tests/bincopy_support.py
@@ -3,6 +3,7 @@ import os
import re
import subprocess
import sys
+import tempfile
from MonetDBtesting.sqltest import SQLTestCase
NRECS = 1_000_000
@@ -15,17 +16,38 @@ class DataMaker:
def __init__(self):
self.fixed_substitutions = dict()
self.work_list = set()
+ self.outfile_to_expected = dict()
def additionally(self, key, value):
self.fixed_substitutions[key] = value
def substitute_match(self, match):
+ var = match.group(1)
+
+ if var in self.fixed_substitutions:
+ return self.fixed_substitutions[var]
+
+ if var.startswith('>'):
+ var = var[1:]
+ outfile = tempfile.mktemp(dir=BINCOPY_FILES, prefix='out_' + var +
'_', suffix='.bin')
+ else:
+ outfile = None
+
+ datafile = self.create_datafile(var)
+
+ if outfile:
+ self.outfile_to_expected[outfile] = datafile
+ substitution = outfile
+ else:
+ substitution = datafile
+
+ quoted = substitution.replace("'", "''")
+ return f"R'{quoted}'"
+
+ def create_datafile(self, var):
flags = []
ext = ''
- var = match.group(1)
- if var in self.fixed_substitutions:
- return self.fixed_substitutions[var]
- elif var.startswith('le_'):
+ if var.startswith('le_'):
var = var[3:]
ext = '.le'
flags.append('--little-endian')
@@ -43,7 +65,7 @@ class DataMaker:
if not os.path.isfile(dst_filename):
cmd = ("bincopydata", *flags, var, str(NRECS), tmp_filename)
self.work_list.add( (cmd, tmp_filename, dst_filename))
- return f"R'{dst_filename}'"
+ return dst_filename
def generate_files(self):
processes = []
@@ -59,19 +81,23 @@ class DataMaker:
os.rename(tmp, dst)
+ def outfiles(self):
+ return self.outfile_to_expected.items()
+
def run_test(side, testcase):
code, expected_result = testcase
+ assert len(re.findall('@ON@', code)) == len(re.findall('COPY', code))
assert '@ON@' in code
# generate the query
data_maker = DataMaker()
data_maker.additionally('ON', 'ON ' + side.upper())
data_maker.additionally('NRECS', NRECS)
data_maker.additionally('NRECS_DIV_4', NRECS / 4)
- massage = lambda s: re.sub(r'@(\w+)@', data_maker.substitute_match, s)
+ massage = lambda s: re.sub(r'@(>?\w+)@', data_maker.substitute_match, s)
code = massage(code)
- code = f"START TRANSACTION;\n{code}\nROLLBACK;"
+ code = f"START TRANSACTION;\n{code}\nROLLBACK;\n"
open(os.path.join(BINCOPY_FILES, 'test.sql'), "w").write(code)
# generate the required data files
@@ -89,29 +115,44 @@ def run_test(side, testcase):
if err_msg:
err_msg = massage(err_msg)
tr.assertFailed(err_code, err_msg)
+ for outfile, expected in data_maker.outfiles():
+ safe_maximum = 1024 * 1024 * 1024
+ if not os.path.exists(outfile):
+ tr.fail(f'Output file {outfile} was not created')
+ expected_content = open(expected, 'rb').read(safe_maximum)
+ content = open(outfile, 'rb').read(safe_maximum)
+ if len(content) != len(expected_content):
+ tr.fail(f'Outfile {outfile} has wrong length: {len(content)},
expected {len(expected_content)}')
+ elif content != expected_content:
+ tr.fail(f'Content of outfile {outfile} differs from
{expected}')
+
INTS = ("""
CREATE TABLE foo(id INT NOT NULL);
COPY BINARY INTO foo(id) FROM @ints@ @ON@;
+COPY SELECT id FROM foo INTO BINARY @>ints@ @ON@;
SELECT COUNT(DISTINCT id) FROM foo;
""", [f"{NRECS}"])
MORE_INTS = ("""
CREATE TABLE foo(id INT NOT NULL, i INT);
COPY BINARY INTO foo(id, i) FROM @ints@, @more_ints@ @ON@;
+COPY SELECT i, id FROM foo INTO BINARY @>more_ints@, @>ints@ @ON@;
SELECT COUNT(id) FROM foo WHERE i = id + 1;
""", [f"{NRECS}"])
STRINGS = ("""
CREATE TABLE foo(id INT NOT NULL, s VARCHAR(20));
COPY BINARY INTO foo(id, s) FROM @ints@, @strings@ @ON@;
+COPY SELECT id, s FROM foo INTO BINARY @>ints@, @>strings@ @ON@;
SELECT COUNT(id) FROM foo WHERE s = ('int' || id);
""", [f"{NRECS}"])
NULL_INTS = ("""
CREATE TABLE foo(id INT NOT NULL, i INT);
COPY BINARY INTO foo(id, i) FROM @ints@, @null_ints@ @ON@;
+COPY SELECT i, id FROM foo INTO BINARY @>null_ints@, @>ints@ @ON@;
SELECT COUNT(id) FROM foo
WHERE (id % 2 = 0 AND i IS NULL)
OR (id % 2 = 1 AND i = id);
@@ -120,6 +161,7 @@ OR (id % 2 = 1 AND i = id);
LARGE_STRINGS = ("""
CREATE TABLE foo(id INT NOT NULL, s TEXT);
COPY BINARY INTO foo(id, s) FROM @ints@, @large_strings@ @ON@;
+COPY SELECT id, s FROM foo INTO BINARY @>ints@, @>large_strings@ @ON@;
SELECT COUNT(id) FROM foo
WHERE (id % 10000 <> 0 AND LENGTH(s) = 9)
OR (id % 10000 = 0 AND LENGTH(s) = 280000 + 9);
@@ -141,6 +183,7 @@ SELECT COUNT(id) FROM foo WHERE s = (E'R
NULL_STRINGS = ("""
CREATE TABLE foo(id INT NOT NULL, s TEXT);
COPY BINARY INTO foo(id, s) FROM @ints@, @null_strings@ @ON@;
+COPY SELECT id, s FROM foo INTO BINARY @>ints@, @>null_strings@ @ON@;
SELECT COUNT(id) FROM foo
WHERE (id % 2 = 0 AND s IS NULL)
OR (id % 2 = 1 AND s = 'banana');
@@ -174,6 +217,22 @@ FROM @ints@,
@timestamp_ms@
@ON@;
+COPY SELECT id, ts, dt, tm, "year", "month", "day", "hour", "minute",
"second", ms FROM foo
+INTO BINARY
+ @>ints@,
+ @>timestamps@,
+ @>timestamp_dates@,
+ @>timestamp_times@,
+ @>timestamp_years@,
+ @>timestamp_months@,
+ @>timestamp_days@,
+ @>timestamp_hours@,
+ @>timestamp_minutes@,
+ @>timestamp_seconds@,
+ @>timestamp_ms@
+ @ON@;
+
+
SELECT * FROM foo
WHERE EXTRACT(YEAR FROM ts) <> "year"
LIMIT 4;
@@ -224,6 +283,7 @@ SELECT COUNT(id) FROM foo WHERE i = id +
BOOLS = ("""
CREATE TABLE foo(id INT NOT NULL, b BOOL);
COPY BINARY INTO foo(id, b) FROM @ints@, @bools@ @ON@;
+COPY SELECT id, b FROM foo INTO BINARY @>ints@, @>bools@ @ON@;
SELECT COUNT(id) FROM foo WHERE b = (id % 2 <> 0);
""", [f"{NRECS}"])
@@ -237,12 +297,14 @@ SELECT COUNT(id) FROM foo WHERE i = id +
FLOATS = ("""
CREATE TABLE foo(id INT NOT NULL, r REAL);
COPY BINARY INTO foo(id, r) FROM @ints@, @floats@ @ON@;
+COPY SELECT id, r FROM foo INTO BINARY @>ints@, @>floats@ @ON@;
SELECT COUNT(id) FROM foo WHERE CAST(id AS REAL) + 0.5 = r;
""", [f"{NRECS}"])
DOUBLES = ("""
CREATE TABLE foo(id INT NOT NULL, d DOUBLE);
COPY BINARY INTO foo(id, d) FROM @ints@, @doubles@ @ON@;
+COPY SELECT id, d FROM foo INTO BINARY @>ints@, @>doubles@ @ON@;
SELECT COUNT(id) FROM foo WHERE CAST(id AS REAL) + 0.5 = d;
""", [f"{NRECS}"])
@@ -250,6 +312,9 @@ INTEGER_TYPES = ("""
CREATE TABLE foo(t TINYINT, s SMALLINT, i INT, b BIGINT);
COPY BINARY INTO foo FROM @tinyints@, @smallints@, @ints@, @bigints@ @ON@;
+COPY SELECT t, s, i, b FROM foo
+INTO BINARY @>tinyints@, @>smallints@, @>ints@, @>bigints@ @ON@;
+
WITH
enlarged AS ( -- first go to the largest type
SELECT
@@ -288,6 +353,8 @@ HUGE_INTS = ("""
CREATE TABLE foo(b BIGINT, h HUGEINT);
COPY BINARY INTO foo FROM @bigints@, @hugeints@ @ON@;
+COPY SELECT b, h FROM foo INTO BINARY @>bigints@, @>hugeints@ @ON@;
+
WITH
enlarged AS (
SELECT
@@ -336,6 +403,7 @@ CREATE TABLE foo(
d10_2 DECIMAL(10, 2),
d18_2 DECIMAL(18, 2)
);
+
COPY BINARY INTO foo FROM
-- bte: i1, d1_1, d2_1
@tinyints@, @tinyints@, @tinyints@,
@@ -346,6 +414,21 @@ COPY BINARY INTO foo FROM
-- lng: i8, d10_2, d18_2
@bigints@, @bigints@, @bigints@
@ON@;
+
+COPY
+SELECT i1, d1_1, d2_1, i2, d3_2, d4_2, i4, d5_2, d9_2, i8, d10_2, d18_2
+FROM foo
+INTO BINARY
+ -- bte: i1, d1_1, d2_1
+ @>tinyints@, @>tinyints@, @>tinyints@,
+ -- sht: i2, d3_2, d4_2
+ @>smallints@, @>smallints@, @>smallints@,
+ -- int: i4, d5_2, d9_2
+ @>ints@, @>ints@, @>ints@,
+ -- lng: i8, d10_2, d18_2
+ @>bigints@, @>bigints@, @>bigints@
+ @ON@;
+
WITH verified AS (
SELECT
(d1_1 IS NULL OR 10 * d1_1 = i1) AS d1_1_ok,
@@ -376,9 +459,16 @@ CREATE TABLE foo(
d19_2 DECIMAL(19, 2),
d38_2 DECIMAL(38, 2)
);
+
COPY BINARY INTO foo FROM
@hugeints@, @hugeints@, @hugeints@
@ON@;
+
+COPY SELECT i, d19_2, d38_2 FROM foo
+INTO BINARY
+ @>hugeints@, @>hugeints@, @>hugeints@
+ @ON@;
+
SELECT
(100 * d19_2 = i) AS d19_ok,
(100 * d38_2 = i) AS d38_ok,
@@ -399,6 +489,7 @@ SELECT COUNT(*) FROM foo;
JSON_OBJECTS = ("""
CREATE TABLE foo(i INT, j JSON);
COPY BINARY INTO foo FROM @ints@, @json_objects@ @ON@;
+COPY SELECT i, j FROM foo INTO BINARY @>ints@, @>json_objects@ @ON@;
SELECT COUNT(*) FROM foo
WHERE (i % 100 = 99 AND j IS NULL)
OR (i % 100 <> 99 AND j IS NOT NULL)
@@ -408,6 +499,7 @@ OR (i % 100 <> 99 AND j IS NOT NULL)
UUIDS = ("""
CREATE TABLE foo(t CHAR(16), u UUID);
COPY BINARY INTO foo FROM @text_uuids@, @binary_uuids@ @ON@;
+COPY SELECT t, u FROM foo INTO BINARY @>text_uuids@, @>binary_uuids@ @ON@;
SELECT COUNT(*) FROM foo
WHERE t = CAST(u AS TEXT)
OR u IS NULL
@@ -416,8 +508,12 @@ OR u IS NULL
LITTLE_ENDIANS = ("""
CREATE TABLE foo(t TINYINT, s SMALLINT, i INT, b BIGINT, f FLOAT(4), d DOUBLE);
+
COPY LITTLE ENDIAN BINARY INTO foo FROM @le_tinyints@, @le_smallints@,
@le_ints@, @le_bigints@, @le_floats@, @le_doubles@ @ON@;
+COPY SELECT t, s, i, b, f, d FROM foo
+INTO LITTLE ENDIAN BINARY @>le_tinyints@, @>le_smallints@, @>le_ints@,
@>le_bigints@, @>le_floats@, @>le_doubles@ @ON@;
+
WITH
enlarged AS ( -- first go to the largest type
SELECT
@@ -459,8 +555,12 @@ ORDER BY t_s, s_i, i_b, f_d
BIG_ENDIANS = ("""
CREATE TABLE foo(t TINYINT, s SMALLINT, i INT, b BIGINT, f FLOAT(4), d DOUBLE);
+
COPY BIG ENDIAN BINARY INTO foo FROM @be_tinyints@, @be_smallints@, @be_ints@,
@be_bigints@, @be_floats@, @be_doubles@ @ON@;
+COPY SELECT t, s, i, b, f, d FROM foo
+INTO BIG ENDIAN BINARY @>be_tinyints@, @>be_smallints@, @>be_ints@,
@>be_bigints@, @>be_floats@, @>be_doubles@ @ON@;
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
