def test_wrong_number_of_columns(self):
"""
Test that a COPY statement will fail when trying to import from a CSV
file with the wrong number of columns by:
- creating a table with a single column,
- writing a CSV file with two columns,
- attempting to COPY the CSV file into the table, and
- asserting that the COPY operation failed.
"""
self.prepare()
self.session.execute("""
CREATE TABLE testcolumns (
a int PRIMARY KEY,
b int
)""")
data = [[1, 2, 3]]
self.tempfile = NamedTemporaryFile(delete=False)
write_rows_to_csv(self.tempfile.name, data)
debug('Importing from csv file: {name}'.format(name=self.tempfile.name))
out, err = self.node1.run_cqlsh("COPY ks.testcolumns FROM '{name}'".format(name=self.tempfile.name),
return_output=True)
self.assertFalse(self.session.execute("SELECT * FROM testcolumns"))
self.assertIn('Aborting import', err)
def test_wrong_number_of_columns(self):
"""
Test that a COPY statement will fail when trying to import from a CSV
file with the wrong number of columns by:
- creating a table with a single column,
- writing a CSV file with two columns,
- attempting to COPY the CSV file into the table, and
- asserting that the COPY operation failed.
"""
self.prepare()
self.session.execute("""
CREATE TABLE testcolumns (
a int PRIMARY KEY,
b int
)""")
data = [[1, 2, 3]]
self.tempfile = NamedTemporaryFile(delete=False)
write_rows_to_csv(self.tempfile.name, data)
debug(
'Importing from csv file: {name}'.format(name=self.tempfile.name))
out, err = self.node1.run_cqlsh(
"COPY ks.testcolumns FROM '{name}'".format(
name=self.tempfile.name),
return_output=True)
self.assertFalse(self.session.execute("SELECT * FROM testcolumns"))
self.assertIn('Aborting import', err)
def quoted_column_names_reading_template(self, specify_column_names):
"""
@param specify_column_names if truthy, specify column names in COPY statement
A parameterized test. Tests that COPY can read from a CSV file into a
table with quoted column names by:
- creating a table with quoted column names,
- writing test data to a CSV file,
- COPYing that CSV file into the table, explicitly naming columns, and
- asserting that the CSV file and the table contain the same data.
If the specify_column_names parameter is truthy, the COPY statement
explicitly names the columns.
"""
self.prepare()
self.session.execute("""
CREATE TABLE testquoted (
"IdNumber" int PRIMARY KEY,
"select" text
)""")
data = [[1, 'no'], [2, 'Yes'],
[3, 'True'], [4, 'false']]
self.tempfile = NamedTemporaryFile(delete=False)
write_rows_to_csv(self.tempfile.name, data)
stmt = ("""COPY ks.testquoted ("IdNumber", "select") FROM '{name}'"""
if specify_column_names else
"""COPY ks.testquoted FROM '{name}'""").format(name=self.tempfile.name)
self.node1.run_cqlsh(stmt)
results = list(self.session.execute("SELECT * FROM testquoted"))
self.assertCsvResultEqual(self.tempfile.name, results)
def quoted_column_names_reading_template(self, specify_column_names):
"""
@param specify_column_names if truthy, specify column names in COPY statement
A parameterized test. Tests that COPY can read from a CSV file into a
table with quoted column names by:
- creating a table with quoted column names,
- writing test data to a CSV file,
- COPYing that CSV file into the table, explicitly naming columns, and
- asserting that the CSV file and the table contain the same data.
If the specify_column_names parameter is truthy, the COPY statement
explicitly names the columns.
"""
self.prepare()
self.session.execute("""
CREATE TABLE testquoted (
"IdNumber" int PRIMARY KEY,
"select" text
)""")
data = [[1, 'no'], [2, 'Yes'], [3, 'True'], [4, 'false']]
self.tempfile = NamedTemporaryFile(delete=False)
write_rows_to_csv(self.tempfile.name, data)
stmt = ("""COPY ks.testquoted ("IdNumber", "select") FROM '{name}'"""
if specify_column_names else
"""COPY ks.testquoted FROM '{name}'""").format(
name=self.tempfile.name)
self.node1.run_cqlsh(stmt)
results = list(self.session.execute("SELECT * FROM testquoted"))
self.assertCsvResultEqual(self.tempfile.name, results)
def data_validation_on_read_template(self, load_as_int, expect_invalid):
"""
@param load_as_int the value that will be loaded into a table as an int value
@param expect_invalid whether or not to expect the COPY statement to fail
Test that reading from CSV files fails when there is a type mismatch
between the value being loaded and the type of the column by:
- creating a table,
- writing a CSV file containing the value passed in as load_as_int, then
- COPYing that csv file into the table, loading load_as_int as an int.
If expect_invalid, this test will succeed when the COPY command fails
with a "Bad request" error message. If not expect_invalid, this test
will succeed when the COPY command prints no errors and the table
matches the loaded CSV file.
"""
self.prepare()
self.session.execute("""
CREATE TABLE testvalidate (
a int PRIMARY KEY,
b int
)""")
data = [[1, load_as_int]]
self.tempfile = NamedTemporaryFile(delete=False)
write_rows_to_csv(self.tempfile.name, data)
cmd = """COPY ks.testvalidate (a, b) FROM '{name}'""".format(
name=self.tempfile.name)
out, err = self.node1.run_cqlsh(cmd, return_output=True)
results = list(self.session.execute("SELECT * FROM testvalidate"))
if expect_invalid:
self.assertRegexpMatches('Bad [Rr]equest', err)
self.assertFalse(results)
else:
self.assertFalse(err)
self.assertCsvResultEqual(self.tempfile.name, results)
def data_validation_on_read_template(self, load_as_int, expect_invalid):
"""
@param load_as_int the value that will be loaded into a table as an int value
@param expect_invalid whether or not to expect the COPY statement to fail
Test that reading from CSV files fails when there is a type mismatch
between the value being loaded and the type of the column by:
- creating a table,
- writing a CSV file containing the value passed in as load_as_int, then
- COPYing that csv file into the table, loading load_as_int as an int.
If expect_invalid, this test will succeed when the COPY command fails
with a "Bad request" error message. If not expect_invalid, this test
will succeed when the COPY command prints no errors and the table
matches the loaded CSV file.
"""
self.prepare()
self.session.execute("""
CREATE TABLE testvalidate (
a int PRIMARY KEY,
b int
)""")
data = [[1, load_as_int]]
self.tempfile = NamedTemporaryFile(delete=False)
write_rows_to_csv(self.tempfile.name, data)
cmd = """COPY ks.testvalidate (a, b) FROM '{name}'""".format(name=self.tempfile.name)
out, err = self.node1.run_cqlsh(cmd, return_output=True)
results = list(self.session.execute("SELECT * FROM testvalidate"))
if expect_invalid:
self.assertRegexpMatches('Bad [Rr]equest', err)
self.assertFalse(results)
else:
self.assertFalse(err)
self.assertCsvResultEqual(self.tempfile.name, results)
def test_explicit_column_order_reading(self):
"""
Test that COPY can write to a CSV file when the order of columns is
explicitly specified by:
- creating a table,
- writing a CSV file containing columns with the same types as the
table, but in a different order,
- COPYing the contents of that CSV into the table by specifying the
order of the columns,
- asserting that the values in the CSV file match those in the table.
"""
self.prepare()
self.session.execute("""
CREATE TABLE testorder (
a int primary key,
b text,
c int
)""")
data = [[1, 20, 'ham'], [2, 40, 'eggs'], [3, 60, 'beans'],
[4, 80, 'toast']]
self.tempfile = NamedTemporaryFile(delete=False)
write_rows_to_csv(self.tempfile.name, data)
self.node1.run_cqlsh(
"COPY ks.testorder (a, c, b) FROM '{name}'".format(
name=self.tempfile.name))
results = list(self.session.execute("SELECT * FROM testorder"))
reference_file = NamedTemporaryFile(delete=False)
with open(reference_file.name, 'wb') as csvfile:
writer = csv.writer(csvfile)
for a, b, c in data:
writer.writerow([a, c, b])
csvfile.close
self.assertCsvResultEqual(reference_file.name, results)
def test_explicit_column_order_reading(self):
"""
Test that COPY can write to a CSV file when the order of columns is
explicitly specified by:
- creating a table,
- writing a CSV file containing columns with the same types as the
table, but in a different order,
- COPYing the contents of that CSV into the table by specifying the
order of the columns,
- asserting that the values in the CSV file match those in the table.
"""
self.prepare()
self.session.execute("""
CREATE TABLE testorder (
a int primary key,
b text,
c int
)""")
data = [[1, 20, 'ham'], [2, 40, 'eggs'],
[3, 60, 'beans'], [4, 80, 'toast']]
self.tempfile = NamedTemporaryFile(delete=False)
write_rows_to_csv(self.tempfile.name, data)
self.node1.run_cqlsh(
"COPY ks.testorder (a, c, b) FROM '{name}'".format(name=self.tempfile.name))
results = list(self.session.execute("SELECT * FROM testorder"))
reference_file = NamedTemporaryFile(delete=False)
with open(reference_file.name, 'wb') as csvfile:
writer = csv.writer(csvfile)
for a, b, c in data:
writer.writerow([a, c, b])
csvfile.close
self.assertCsvResultEqual(reference_file.name, results)
def quoted_column_names_writing_template(self, specify_column_names):
"""
@param specify_column_names if truthy, specify column names in COPY statement
A parameterized test. Test that COPY can write to a table with quoted
column names by:
- creating a table with quoted column names,
- inserting test data into that table,
- COPYing that table into a CSV file into the table, explicitly naming columns,
- writing that test data to a CSV file,
- asserting that the two CSV files contain the same rows.
If the specify_column_names parameter is truthy, the COPY statement
explicitly names the columns.
"""
self.prepare()
self.session.execute("""
CREATE TABLE testquoted (
"IdNumber" int PRIMARY KEY,
"select" text
)""")
data = [[1, 'no'], [2, 'Yes'], [3, 'True'], [4, 'false']]
insert_statement = self.session.prepare(
"""INSERT INTO testquoted ("IdNumber", "select") VALUES (?, ?)""")
execute_concurrent_with_args(self.session, insert_statement, data)
self.tempfile = NamedTemporaryFile(delete=False)
stmt = ("""COPY ks.testquoted ("IdNumber", "select") TO '{name}'"""
if specify_column_names else
"""COPY ks.testquoted TO '{name}'""").format(
name=self.tempfile.name)
self.node1.run_cqlsh(stmt)
reference_file = NamedTemporaryFile(delete=False)
write_rows_to_csv(reference_file.name, data)
assert_csvs_items_equal(self.tempfile.name, reference_file.name)
def quoted_column_names_writing_template(self, specify_column_names):
"""
@param specify_column_names if truthy, specify column names in COPY statement
A parameterized test. Test that COPY can write to a table with quoted
column names by:
- creating a table with quoted column names,
- inserting test data into that table,
- COPYing that table into a CSV file into the table, explicitly naming columns,
- writing that test data to a CSV file,
- asserting that the two CSV files contain the same rows.
If the specify_column_names parameter is truthy, the COPY statement
explicitly names the columns.
"""
self.prepare()
self.session.execute("""
CREATE TABLE testquoted (
"IdNumber" int PRIMARY KEY,
"select" text
)""")
data = [[1, 'no'], [2, 'Yes'],
[3, 'True'], [4, 'false']]
insert_statement = self.session.prepare("""INSERT INTO testquoted ("IdNumber", "select") VALUES (?, ?)""")
execute_concurrent_with_args(self.session, insert_statement, data)
self.tempfile = NamedTemporaryFile(delete=False)
stmt = ("""COPY ks.testquoted ("IdNumber", "select") TO '{name}'"""
if specify_column_names else
"""COPY ks.testquoted TO '{name}'""").format(name=self.tempfile.name)
self.node1.run_cqlsh(stmt)
reference_file = NamedTemporaryFile(delete=False)
write_rows_to_csv(reference_file.name, data)
assert_csvs_items_equal(self.tempfile.name, reference_file.name)