This is an automated email from the ASF dual-hosted git repository.
lidavidm pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/arrow-adbc.git
The following commit(s) were added to refs/heads/main by this push:
new 5137fe26 feat(c/driver/postgresql,c/driver/sqlite): Implement FOREIGN
KEY constraints (#1099)
5137fe26 is described below
commit 5137fe26c4d034b74e91cb368b1207c719c32a31
Author: OleMussmann <[email protected]>
AuthorDate: Thu Oct 5 00:22:29 2023 +0200
feat(c/driver/postgresql,c/driver/sqlite): Implement FOREIGN KEY
constraints (#1099)
# Test and Implement Foreign Keys
This feature relies on Composite Primary Keys, which are implemented,
but had no tests. The missing tests are included in this PR as a
separate commit. The pull request closes #936 .
## Discussion points
- SQLite constraints are currently nameless, whereas the PostgreSQL
constraints have generated names. Should it stay that way?
- SQLite and PostgreSQL return their constraints in different orders. Is
that by design?
- Since the order of constraints can vary between DBs, the Foreign Key
tests currently identify constraint columns by their number of column
usages. Maybe there's a better solution to it?
- `c/validation/adbc_validation.cc` lines [1252-1255] assume a certain
order of the constraint column usages. Should we find an order-agnostic
solution instead? It might be more verbose. It's currently:
```
ForeignKeyColumnUsagesTest(child_constraint, 0, "adbc_fkey_parent_2_test",
"id_primary_col1");
ForeignKeyColumnUsagesTest(child_constraint, 1, "adbc_fkey_parent_2_test",
"id_primary_col2");
```
- `c/validation/adbc_validation.cc` lines [964,1115] assume a certain
order of the constraint column names. It is currently:
```
for (column_index = 0; column_index < number_of_columns; column_index++) {
std::string_view constraint_column_name(
constraint->constraint_column_names[column_index].data,
constraint->constraint_column_names[column_index].size_bytes);
ASSERT_EQ(constraint_column_name, columns[column_index]);
}
```
An order-agnostic, but less specific, approach to test this could be
```
ASSERT_THAT(constraint_column_name,
::testing::AnyOf(::testing::Eq(parent_2_column_names[0]),
::testing::Eq(parent_2_column_names[1])));
```
## Working tests for Composite Primary Keys
The composite primary key tests fail correctly in the following ways.
### Number of table columns
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsPrimaryKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1046: Failure
Expected equality of these values:
composite_table->n_table_columns
Which is: 9
2
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsPrimaryKey (87 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsPrimaryKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1046: Failure
Expected equality of these values:
composite_table->n_table_columns
Which is: 9
2
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsPrimaryKey (0 ms)
```
### Table names
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsPrimaryKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1057: Failure
Expected: (parent_2_column) != (nullptr), actual: NULL vs (nullptr)
could not find column NOT_EXISTING on adbc_composite_pkey_test table
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsPrimaryKey (90 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsPrimaryKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1057: Failure
Expected: (parent_2_column) != (nullptr), actual: NULL vs (nullptr)
could not find column NOT_EXISTING on adbc_composite_pkey_test table
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsPrimaryKey (0 ms)
```
### Constraint column names
This test gives less helpful output, but is aligned with the existing
primary key tests.
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsPrimaryKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1064: Failure
Expected equality of these values:
constraint_column_name
Which is: "id_primary_col1"
parent_2_column_names[column_name_index]
Which is: 0x55de7b
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsPrimaryKey (79 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsPrimaryKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1064: Failure
Expected equality of these values:
constraint_column_name
Which is: "id_primary_col1"
parent_2_column_names[column_name_index]
Which is: 0x512e86
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsPrimaryKey (0 ms)
```
### Number of table constraints
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsPrimaryKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1068: Failure
Expected equality of these values:
composite_table->n_table_constraints
Which is: 9
1
expected 1 constraint on adbc_pkey_test table, found: 9
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsPrimaryKey (87 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsPrimaryKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1068: Failure
Expected equality of these values:
composite_table->n_table_constraints
Which is: 9
1
expected 1 constraint on adbc_pkey_test table, found: 9
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsPrimaryKey (0 ms)
```
### Constraint type
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsPrimaryKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1075: Failure
Expected equality of these values:
composite_constraint_type
Which is: "UNKNOWN KEY"
"PRIMARY KEY"
Which is: 0x55dee9
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsPrimaryKey (87 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsPrimaryKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1075: Failure
Expected equality of these values:
composite_constraint_type
Which is: "UNKNOWN KEY"
"PRIMARY KEY"
Which is: 0x512ef4
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsPrimaryKey (0 ms)
```
### Number of constraints
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsPrimaryKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1076: Failure
Expected equality of these values:
composite_constraint->n_column_names
Which is: 9
2
expected constraint adbc_pkey_test_pkey to be applied to 2 columns, found: 9
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsPrimaryKey (79 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsPrimaryKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1076: Failure
Expected equality of these values:
composite_constraint->n_column_names
Which is: 9
2
expected constraint adbc_pkey_test_pkey to be applied to 2 columns, found: 9
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsPrimaryKey (0 ms)
```
## Working tests for Foreign Keys
The foreign key tests fail correctly in the following ways.
### Number of columns
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1229: Failure
Expected equality of these values:
child_table->n_table_columns
Which is: 9
3
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsForeignKey (103 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1229: Failure
Expected equality of these values:
child_table->n_table_columns
Which is: 9
3
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsForeignKey (0 ms)
```
### Table names
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1237: Failure
Expected: (child_column) != (nullptr), actual: NULL vs (nullptr)
could not find column NOT_EXISTING on adbc_fkey_child_test table
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsForeignKey (120 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1237: Failure
Expected: (child_column) != (nullptr), actual: NULL vs (nullptr)
could not find column NOT_EXISTING on adbc_fkey_child_test table
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsForeignKey (0 ms)
```
### Number of table constraints
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1244: Failure
Expected equality of these values:
child_table->n_table_constraints
Which is: 9
3
expected 3 constraint on adbc_fkey_child_test table, found: 9
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsForeignKey (98 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1244: Failure
Expected equality of these values:
child_table->n_table_constraints
Which is: 9
3
expected 3 constraint on adbc_fkey_child_test table, found: 9
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsForeignKey (0 ms)
```
### Constraint Type
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1095: Failure
Expected equality of these values:
constraint_type
Which is: "UNKNOWN KEY"
key_type
Which is: "FOREIGN KEY"
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsForeignKey (130 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1095: Failure
Expected equality of these values:
constraint_type
Which is: "UNKNOWN KEY"
key_type
Which is: "FOREIGN KEY"
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsForeignKey (0 ms)
```
### Constraint Column Names (less helpful, but aligned with primary key
tests)
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1106: Failure
Expected equality of these values:
constraint_column_name
Which is: "WRONG_NAME"
columns[column_index]
Which is: "id_child_col1"
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsForeignKey (97 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1106: Failure
Expected equality of these values:
constraint_column_name
Which is: "WRONG_NAME"
columns[column_index]
Which is: "id_child_col1"
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsForeignKey (0 ms)
```
### Target table name
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1139: Failure
Expected equality of these values:
constraint_column_usage_fk_table
Which is: "WRONG_NAME"
fk_table_name
Which is: "adbc_fkey_parent_2_test"
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsForeignKey (103 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1139: Failure
Expected equality of these values:
constraint_column_usage_fk_table
Which is: "WRONG_NAME"
fk_table_name
Which is: "adbc_fkey_parent_2_test"
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsForeignKey (103 ms)
```
### Target column name
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1146: Failure
Expected equality of these values:
constraint_column_usage_fk_column_name
Which is: "WRONG_NAME"
fk_column_name
Which is: "id_primary_col1"
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsForeignKey (99 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1146: Failure
Expected equality of these values:
constraint_column_usage_fk_column_name
Which is: "WRONG_NAME"
fk_column_name
Which is: "id_primary_col1"
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsForeignKey (0 ms)
```
### Skipped a mandatory constraint test
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1293: Failure
Value of: TestedConstraints.adbc_fkey_child_test_pkey
Actual: false
Expected: true
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsForeignKey (98 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1293: Failure
Value of: TestedConstraints.adbc_fkey_child_test_pkey
Actual: false
Expected: true
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsForeignKey (0 ms)
```
### Number of Foreign Key columns
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1096: Failure
Expected equality of these values:
constraint->n_column_names
Which is: 9
2
expected constraint FOREIGN KEY of adbc_fkey_child_test to be applied to 2
column(s), found: 9
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsForeignKey (102 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1096: Failure
Expected equality of these values:
constraint->n_column_names
Which is: 9
1
expected constraint PRIMARY KEY of adbc_fkey_child_test to be applied to 1
column(s), found: 9
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsForeignKey (0 ms)
```
### Foreign Key catalog name
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1120: Failure
Value of: constraint_column_usage_fk_catalog
Expected: (is equal to "main") or (is equal to "postgres")
Actual: "WRONG_NAME"
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsForeignKey (99 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1120: Failure
Value of: constraint_column_usage_fk_catalog
Expected: (is equal to "main") or (is equal to "postgres")
Actual: "WRONG_NAME"
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsForeignKey (0 ms)
```
### Foreign Key DB schema name
```
[ RUN ] PostgresConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1131: Failure
Value of: constraint_column_usage_fk_db_schema
Expected: (is equal to "") or (is equal to "public")
Actual: "WRONG_NAME"
[ FAILED ] PostgresConnectionTest.MetadataGetObjectsForeignKey (97 ms)
```
```
[ RUN ] SqliteConnectionTest.MetadataGetObjectsForeignKey
/home/ole/code/arrow-adbc/c/validation/adbc_validation.cc:1131: Failure
Value of: constraint_column_usage_fk_db_schema
Expected: (is equal to "") or (is equal to "public")
Actual: "WRONG_NAME"
[ FAILED ] SqliteConnectionTest.MetadataGetObjectsForeignKey (0 ms)
```
---------
Co-authored-by: Ole Mussmann <[email protected]>
Co-authored-by: Suvayu Ali <[email protected]>
---
c/driver/postgresql/postgresql_test.cc | 26 +++
c/driver/sqlite/sqlite.c | 51 +++---
c/driver/sqlite/sqlite_test.cc | 26 +++
c/validation/adbc_validation.cc | 283 ++++++++++++++++++++++++++++++---
c/validation/adbc_validation.h | 28 ++++
5 files changed, 370 insertions(+), 44 deletions(-)
diff --git a/c/driver/postgresql/postgresql_test.cc
b/c/driver/postgresql/postgresql_test.cc
index 2afc4dbb..303cc059 100644
--- a/c/driver/postgresql/postgresql_test.cc
+++ b/c/driver/postgresql/postgresql_test.cc
@@ -107,6 +107,32 @@ class PostgresQuirks : public
adbc_validation::DriverQuirks {
return ddl;
}
+ std::optional<std::string> CompositePrimaryKeyTableDdl(
+ std::string_view name) const override {
+ std::string ddl = "CREATE TABLE ";
+ ddl += name;
+ ddl += " (id_primary_col1 SERIAL, id_primary_col2 SERIAL,";
+ ddl += " PRIMARY KEY (id_primary_col1, id_primary_col2));";
+ return ddl;
+ }
+
+ std::optional<std::string> ForeignKeyChildTableDdl(
+ std::string_view child_name, std::string_view parent_name_1,
+ std::string_view parent_name_2) const override {
+ std::string ddl = "CREATE TABLE ";
+ ddl += child_name;
+ ddl += " (id_child_col1 SERIAL PRIMARY KEY, ";
+ ddl += " id_child_col2 SERIAL, ";
+ ddl += " id_child_col3 SERIAL, ";
+ ddl += " FOREIGN KEY (id_child_col3) REFERENCES ";
+ ddl += parent_name_1;
+ ddl += " (id),";
+ ddl += " FOREIGN KEY (id_child_col1, id_child_col2) REFERENCES ";
+ ddl += parent_name_2;
+ ddl += " (id_primary_col1, id_primary_col2))";
+ return ddl;
+ }
+
std::string catalog() const override { return "postgres"; }
std::string db_schema() const override { return "public"; }
diff --git a/c/driver/sqlite/sqlite.c b/c/driver/sqlite/sqlite.c
index b47336fe..05243c03 100644
--- a/c/driver/sqlite/sqlite.c
+++ b/c/driver/sqlite/sqlite.c
@@ -573,12 +573,9 @@ AdbcStatusCode SqliteConnectionGetConstraintsImpl(
const char* from_col = (const char*)sqlite3_column_text(fk_stmt, 3);
const char* to_col = (const char*)sqlite3_column_text(fk_stmt, 4);
+ // New foreign key constraint or -constraint sets
if (fk_id != prev_fk_id) {
- CHECK_NA(INTERNAL, ArrowArrayAppendNull(constraint_name_col, 1), error);
- CHECK_NA(INTERNAL,
- ArrowArrayAppendString(constraint_name_col,
ArrowCharView("FOREIGN KEY")),
- error);
-
+ // Not first constraint of the table
if (prev_fk_id != -1) {
CHECK_NA(INTERNAL,
ArrowArrayFinishElement(constraint_column_names_col), error);
CHECK_NA(INTERNAL,
ArrowArrayFinishElement(constraint_column_usage_col), error);
@@ -586,28 +583,34 @@ AdbcStatusCode SqliteConnectionGetConstraintsImpl(
}
prev_fk_id = fk_id;
+ CHECK_NA(INTERNAL, ArrowArrayAppendNull(constraint_name_col, 1), error);
CHECK_NA(INTERNAL,
- ArrowArrayAppendString(
- constraint_column_names_items,
- (struct ArrowStringView){
- .data = from_col, .size_bytes =
sqlite3_column_bytes(pk_stmt, 3)}),
- error);
- CHECK_NA(INTERNAL, ArrowArrayAppendString(fk_catalog_col,
ArrowCharView("main")),
- error);
- CHECK_NA(INTERNAL, ArrowArrayAppendNull(fk_db_schema_col, 1), error);
- CHECK_NA(INTERNAL,
- ArrowArrayAppendString(
- fk_table_col,
- (struct ArrowStringView){
- .data = to_table, .size_bytes =
sqlite3_column_bytes(pk_stmt, 2)}),
- error);
- CHECK_NA(INTERNAL,
- ArrowArrayAppendString(
- fk_column_name_col,
- (struct ArrowStringView){
- .data = to_col, .size_bytes =
sqlite3_column_bytes(pk_stmt, 4)}),
+ ArrowArrayAppendString(constraint_type_col,
ArrowCharView("FOREIGN KEY")),
error);
}
+ CHECK_NA(INTERNAL,
+ ArrowArrayAppendString(
+ constraint_column_names_items,
+ (struct ArrowStringView){
+ .data = from_col, .size_bytes =
sqlite3_column_bytes(fk_stmt, 3)}),
+ error);
+ CHECK_NA(INTERNAL, ArrowArrayAppendString(fk_catalog_col,
ArrowCharView("main")),
+ error);
+ CHECK_NA(INTERNAL, ArrowArrayAppendNull(fk_db_schema_col, 1), error);
+ CHECK_NA(INTERNAL,
+ ArrowArrayAppendString(
+ fk_table_col,
+ (struct ArrowStringView){
+ .data = to_table, .size_bytes =
sqlite3_column_bytes(fk_stmt, 2)}),
+ error);
+ CHECK_NA(INTERNAL,
+ ArrowArrayAppendString(
+ fk_column_name_col,
+ (struct ArrowStringView){
+ .data = to_col, .size_bytes =
sqlite3_column_bytes(fk_stmt, 4)}),
+ error);
+
+ CHECK_NA(INTERNAL, ArrowArrayFinishElement(constraint_column_usage_items),
error);
}
RAISE(INTERNAL, rc == SQLITE_DONE, sqlite3_errmsg(conn->conn), error);
if (prev_fk_id != -1) {
diff --git a/c/driver/sqlite/sqlite_test.cc b/c/driver/sqlite/sqlite_test.cc
index 617fcb01..e5566df2 100644
--- a/c/driver/sqlite/sqlite_test.cc
+++ b/c/driver/sqlite/sqlite_test.cc
@@ -98,6 +98,32 @@ class SqliteQuirks : public adbc_validation::DriverQuirks {
return ddl;
}
+ std::optional<std::string> CompositePrimaryKeyTableDdl(
+ std::string_view name) const override {
+ std::string ddl = "CREATE TABLE ";
+ ddl += name;
+ ddl += " (id_primary_col1 INTEGER, id_primary_col2 INTEGER,";
+ ddl += " PRIMARY KEY (id_primary_col1, id_primary_col2));";
+ return ddl;
+ }
+
+ std::optional<std::string> ForeignKeyChildTableDdl(
+ std::string_view child_name, std::string_view parent_name_1,
+ std::string_view parent_name_2) const override {
+ std::string ddl = "CREATE TABLE ";
+ ddl += child_name;
+ ddl += " (id_child_col1 INTEGER PRIMARY KEY,";
+ ddl += " id_child_col2 INTEGER,";
+ ddl += " id_child_col3 INTEGER,";
+ ddl += " FOREIGN KEY (id_child_col3) REFERENCES ";
+ ddl += parent_name_1;
+ ddl += " (id),";
+ ddl += " FOREIGN KEY (id_child_col1, id_child_col2) REFERENCES ";
+ ddl += parent_name_2;
+ ddl += " (id_primary_col1, id_primary_col2));";
+ return ddl;
+ }
+
bool supports_bulk_ingest(const char* mode) const override {
return std::strcmp(mode, ADBC_INGEST_OPTION_MODE_APPEND) == 0 ||
std::strcmp(mode, ADBC_INGEST_OPTION_MODE_CREATE) == 0;
diff --git a/c/validation/adbc_validation.cc b/c/validation/adbc_validation.cc
index 0dedb8f6..ab74f57c 100644
--- a/c/validation/adbc_validation.cc
+++ b/c/validation/adbc_validation.cc
@@ -945,6 +945,58 @@ void ConnectionTest::TestMetadataGetObjectsConstraints() {
// TODO: can't be done portably (need to create tables with primary keys and
such)
}
+void ConstraintTest(const AdbcGetObjectsConstraint* constraint,
+ const std::string& key_type,
+ const std::vector<std::string>& columns) {
+ std::string_view constraint_type(constraint->constraint_type.data,
+ constraint->constraint_type.size_bytes);
+ int number_of_columns = columns.size();
+ ASSERT_EQ(constraint_type, key_type);
+ ASSERT_EQ(constraint->n_column_names, number_of_columns)
+ << "expected constraint " << key_type
+ << " of adbc_fkey_child_test to be applied to " <<
std::to_string(number_of_columns)
+ << " column(s), found: " << constraint->n_column_names;
+
+ int column_index;
+ for (column_index = 0; column_index < number_of_columns; column_index++) {
+ std::string_view constraint_column_name(
+ constraint->constraint_column_names[column_index].data,
+ constraint->constraint_column_names[column_index].size_bytes);
+ ASSERT_EQ(constraint_column_name, columns[column_index]);
+ }
+}
+
+void ForeignKeyColumnUsagesTest(const AdbcGetObjectsConstraint* constraint,
+ const std::string& catalog, const std::string&
db_schema,
+ const int column_usage_index,
+ const std::string& fk_table_name,
+ const std::string& fk_column_name) {
+ // Test fk_catalog
+ std::string_view constraint_column_usage_fk_catalog(
+
constraint->constraint_column_usages[column_usage_index]->fk_catalog.data,
+
constraint->constraint_column_usages[column_usage_index]->fk_catalog.size_bytes);
+ ASSERT_THAT(constraint_column_usage_fk_catalog, catalog);
+
+ // Test fk_db_schema
+ std::string_view constraint_column_usage_fk_db_schema(
+
constraint->constraint_column_usages[column_usage_index]->fk_db_schema.data,
+
constraint->constraint_column_usages[column_usage_index]->fk_db_schema.size_bytes);
+ ASSERT_THAT(constraint_column_usage_fk_db_schema, db_schema);
+
+ // Test fk_table_name
+ std::string_view constraint_column_usage_fk_table(
+ constraint->constraint_column_usages[column_usage_index]->fk_table.data,
+
constraint->constraint_column_usages[column_usage_index]->fk_table.size_bytes);
+ ASSERT_EQ(constraint_column_usage_fk_table, fk_table_name);
+
+ // Test fk_column_name
+ std::string_view constraint_column_usage_fk_column_name(
+
constraint->constraint_column_usages[column_usage_index]->fk_column_name.data,
+ constraint->constraint_column_usages[column_usage_index]
+ ->fk_column_name.size_bytes);
+ ASSERT_EQ(constraint_column_usage_fk_column_name, fk_column_name);
+}
+
void ConnectionTest::TestMetadataGetObjectsPrimaryKey() {
ASSERT_THAT(AdbcConnectionNew(&connection, &error), IsOkStatus(&error));
ASSERT_THAT(AdbcConnectionInit(&connection, &database, &error),
IsOkStatus(&error));
@@ -953,6 +1005,7 @@ void ConnectionTest::TestMetadataGetObjectsPrimaryKey() {
GTEST_SKIP();
}
+ // Set up primary key ddl
std::optional<std::string> maybe_ddl =
quirks()->PrimaryKeyTableDdl("adbc_pkey_test");
if (!maybe_ddl.has_value()) {
GTEST_SKIP();
@@ -962,16 +1015,37 @@ void ConnectionTest::TestMetadataGetObjectsPrimaryKey() {
ASSERT_THAT(quirks()->DropTable(&connection, "adbc_pkey_test", &error),
IsOkStatus(&error));
+ // Set up composite primary key ddl
+ std::optional<std::string> maybe_composite_ddl =
+ quirks()->CompositePrimaryKeyTableDdl("adbc_composite_pkey_test");
+ if (!maybe_composite_ddl.has_value()) {
+ GTEST_SKIP();
+ }
+ std::string composite_ddl = std::move(*maybe_composite_ddl);
+
+ // Empty database
+ ASSERT_THAT(quirks()->DropTable(&connection, "adbc_pkey_test", &error),
+ IsOkStatus(&error));
+ ASSERT_THAT(quirks()->DropTable(&connection, "adbc_composite_pkey_test",
&error),
+ IsOkStatus(&error));
+
+ // Populate database
{
- Handle<AdbcStatement> statement;
- ASSERT_THAT(AdbcStatementNew(&connection, &statement.value, &error),
- IsOkStatus(&error));
- ASSERT_THAT(AdbcStatementSetSqlQuery(&statement.value, ddl.c_str(),
&error),
- IsOkStatus(&error));
- int64_t rows_affected = 0;
- ASSERT_THAT(
- AdbcStatementExecuteQuery(&statement.value, nullptr, &rows_affected,
&error),
- IsOkStatus(&error));
+ Handle<AdbcStatement> statements[2];
+ std::string ddls[2] = {ddl, composite_ddl};
+ int64_t rows_affected;
+
+ for (int ddl_index = 0; ddl_index < 2; ddl_index++) {
+ rows_affected = 0;
+ ASSERT_THAT(AdbcStatementNew(&connection, &statements[ddl_index].value,
&error),
+ IsOkStatus(&error));
+ ASSERT_THAT(AdbcStatementSetSqlQuery(&statements[ddl_index].value,
+ ddls[ddl_index].c_str(), &error),
+ IsOkStatus(&error));
+ ASSERT_THAT(AdbcStatementExecuteQuery(&statements[ddl_index].value,
nullptr,
+ &rows_affected, &error),
+ IsOkStatus(&error));
+ }
}
adbc_validation::StreamReader reader;
@@ -988,6 +1062,7 @@ void ConnectionTest::TestMetadataGetObjectsPrimaryKey() {
ASSERT_NE(*get_objects_data, nullptr)
<< "could not initialize the AdbcGetObjectsData object";
+ // Test primary key
struct AdbcGetObjectsTable* table =
AdbcGetObjectsDataGetTableByName(*get_objects_data,
quirks()->catalog().c_str(),
quirks()->db_schema().c_str(),
"adbc_pkey_test");
@@ -1004,18 +1079,186 @@ void
ConnectionTest::TestMetadataGetObjectsPrimaryKey() {
<< table->n_table_constraints;
struct AdbcGetObjectsConstraint* constraint = table->table_constraints[0];
+ ConstraintTest(constraint, "PRIMARY KEY", {"id"});
- std::string_view constraint_type(constraint->constraint_type.data,
- constraint->constraint_type.size_bytes);
- ASSERT_EQ(constraint_type, "PRIMARY KEY");
- ASSERT_EQ(constraint->n_column_names, 1)
- << "expected constraint adbc_pkey_test_pkey to be applied to 1 column,
found: "
- << constraint->n_column_names;
-
- std::string_view constraint_column_name(
- constraint->constraint_column_names[0].data,
- constraint->constraint_column_names[0].size_bytes);
- ASSERT_EQ(constraint_column_name, "id");
+ // Test composite primary key
+ struct AdbcGetObjectsTable* composite_table =
AdbcGetObjectsDataGetTableByName(
+ *get_objects_data, quirks()->catalog().c_str(),
quirks()->db_schema().c_str(),
+ "adbc_composite_pkey_test");
+ ASSERT_NE(composite_table, nullptr) << "could not find
adbc_composite_pkey_test table";
+
+ // The composite primary key table has two columns: id_primary_col1,
id_primary_col2
+ ASSERT_EQ(composite_table->n_table_columns, 2);
+
+ struct AdbcGetObjectsConstraint* composite_constraint =
+ composite_table->table_constraints[0];
+ const char* parent_2_column_names[2] = {"id_primary_col1",
"id_primary_col2"};
+ struct AdbcGetObjectsColumn* parent_2_column;
+ for (int column_name_index = 0; column_name_index < 2; column_name_index++) {
+ parent_2_column = AdbcGetObjectsDataGetColumnByName(
+ *get_objects_data, quirks()->catalog().c_str(),
quirks()->db_schema().c_str(),
+ "adbc_composite_pkey_test", parent_2_column_names[column_name_index]);
+ ASSERT_NE(parent_2_column, nullptr)
+ << "could not find column " << parent_2_column_names[column_name_index]
+ << " on adbc_composite_pkey_test table";
+
+ std::string_view constraint_column_name(
+ composite_constraint->constraint_column_names[column_name_index].data,
+
composite_constraint->constraint_column_names[column_name_index].size_bytes);
+ ASSERT_EQ(constraint_column_name,
parent_2_column_names[column_name_index]);
+ }
+
+ ConstraintTest(composite_constraint, "PRIMARY KEY",
+ {"id_primary_col1", "id_primary_col2"});
+}
+
+void ConnectionTest::TestMetadataGetObjectsForeignKey() {
+ ASSERT_THAT(AdbcConnectionNew(&connection, &error), IsOkStatus(&error));
+ ASSERT_THAT(AdbcConnectionInit(&connection, &database, &error),
IsOkStatus(&error));
+
+ if (!quirks()->supports_get_objects()) {
+ GTEST_SKIP();
+ }
+
+ // Load DDLs
+ std::optional<std::string> maybe_parent_1_ddl =
+ quirks()->PrimaryKeyTableDdl("adbc_fkey_parent_1_test");
+ if (!maybe_parent_1_ddl.has_value()) {
+ GTEST_SKIP();
+ }
+
+ std::string parent_1_ddl = std::move(*maybe_parent_1_ddl);
+
+ std::optional<std::string> maybe_parent_2_ddl =
+ quirks()->CompositePrimaryKeyTableDdl("adbc_fkey_parent_2_test");
+ if (!maybe_parent_2_ddl.has_value()) {
+ GTEST_SKIP();
+ }
+ std::string parent_2_ddl = std::move(*maybe_parent_2_ddl);
+
+ std::optional<std::string> maybe_child_ddl =
quirks()->ForeignKeyChildTableDdl(
+ "adbc_fkey_child_test", "adbc_fkey_parent_1_test",
"adbc_fkey_parent_2_test");
+ if (!maybe_child_ddl.has_value()) {
+ GTEST_SKIP();
+ }
+ std::string child_ddl = std::move(*maybe_child_ddl);
+
+ // Empty database
+ // First drop the child table, since the parent tables depends on it
+ ASSERT_THAT(quirks()->DropTable(&connection, "adbc_fkey_child_test", &error),
+ IsOkStatus(&error));
+ ASSERT_THAT(quirks()->DropTable(&connection, "adbc_fkey_parent_1_test",
&error),
+ IsOkStatus(&error));
+ ASSERT_THAT(quirks()->DropTable(&connection, "adbc_fkey_parent_2_test",
&error),
+ IsOkStatus(&error));
+
+ // Populate database
+ {
+ Handle<AdbcStatement> statements[3];
+ std::string ddls[3] = {parent_1_ddl, parent_2_ddl, child_ddl};
+ int64_t rows_affected;
+
+ for (int ddl_index = 0; ddl_index < 3; ddl_index++) {
+ rows_affected = 0;
+ ASSERT_THAT(AdbcStatementNew(&connection, &statements[ddl_index].value,
&error),
+ IsOkStatus(&error));
+ ASSERT_THAT(AdbcStatementSetSqlQuery(&statements[ddl_index].value,
+ ddls[ddl_index].c_str(), &error),
+ IsOkStatus(&error));
+ ASSERT_THAT(AdbcStatementExecuteQuery(&statements[ddl_index].value,
nullptr,
+ &rows_affected, &error),
+ IsOkStatus(&error));
+ }
+ }
+
+ adbc_validation::StreamReader reader;
+ ASSERT_THAT(
+ AdbcConnectionGetObjects(&connection, ADBC_OBJECT_DEPTH_ALL, nullptr,
nullptr,
+ nullptr, nullptr, nullptr,
&reader.stream.value, &error),
+ IsOkStatus(&error));
+ ASSERT_NO_FATAL_FAILURE(reader.GetSchema());
+ ASSERT_NO_FATAL_FAILURE(reader.Next());
+ ASSERT_NE(nullptr, reader.array->release);
+ ASSERT_GT(reader.array->length, 0);
+
+ auto get_objects_data =
adbc_validation::GetObjectsReader{&reader.array_view.value};
+ ASSERT_NE(*get_objects_data, nullptr)
+ << "could not initialize the AdbcGetObjectsData object";
+
+ // Test child table
+ struct AdbcGetObjectsTable* child_table = AdbcGetObjectsDataGetTableByName(
+ *get_objects_data, quirks()->catalog().c_str(),
quirks()->db_schema().c_str(),
+ "adbc_fkey_child_test");
+ ASSERT_NE(child_table, nullptr) << "could not find adbc_fkey_child_test
table";
+
+ // The child table has three columns: id_child_col1, id_child_col2,
id_child_col3
+ ASSERT_EQ(child_table->n_table_columns, 3);
+
+ const char* child_column_names[3] = {"id_child_col1", "id_child_col2",
"id_child_col3"};
+ struct AdbcGetObjectsColumn* child_column;
+ for (int column_index = 0; column_index < 2; column_index++) {
+ child_column = AdbcGetObjectsDataGetColumnByName(
+ *get_objects_data, quirks()->catalog().c_str(),
quirks()->db_schema().c_str(),
+ "adbc_fkey_child_test", child_column_names[column_index]);
+ ASSERT_NE(child_column, nullptr)
+ << "could not find column " << child_column_names[column_index]
+ << " on adbc_fkey_child_test table";
+ }
+
+ // There are three constraints: PRIMARY KEY, FOREIGN KEY, FOREIGN KEY
+ // affecting one, one, and two columns, respetively
+ ASSERT_EQ(child_table->n_table_constraints, 3)
+ << "expected 3 constraint on adbc_fkey_child_test table, found: "
+ << child_table->n_table_constraints;
+
+ struct ConstraintFlags {
+ bool adbc_fkey_child_test_pkey = false;
+ bool adbc_fkey_child_test_id_child_col3_fkey = false;
+ bool adbc_fkey_child_test_id_child_col1_id_child_col2_fkey = false;
+ };
+ ConstraintFlags TestedConstraints;
+
+ for (int constraint_index = 0; constraint_index < 3; constraint_index++) {
+ struct AdbcGetObjectsConstraint* child_constraint =
+ child_table->table_constraints[constraint_index];
+ int numbern_of_column_usages = child_constraint->n_column_usages;
+
+ // The number of column usages identifies the constraint
+ switch (numbern_of_column_usages) {
+ case 0: {
+ // adbc_fkey_child_test_pkey
+ ConstraintTest(child_constraint, "PRIMARY KEY", {"id_child_col1"});
+
+ TestedConstraints.adbc_fkey_child_test_pkey = true;
+ } break;
+ case 1: {
+ // adbc_fkey_child_test_id_child_col3_fkey
+ ConstraintTest(child_constraint, "FOREIGN KEY", {"id_child_col3"});
+ ForeignKeyColumnUsagesTest(child_constraint, quirks()->catalog(),
+ quirks()->db_schema(), 0,
"adbc_fkey_parent_1_test",
+ "id");
+
+ TestedConstraints.adbc_fkey_child_test_id_child_col3_fkey = true;
+ } break;
+ case 2: {
+ // adbc_fkey_child_test_id_child_col1_id_child_col2_fkey
+ ConstraintTest(child_constraint, "FOREIGN KEY",
+ {"id_child_col1", "id_child_col2"});
+ ForeignKeyColumnUsagesTest(child_constraint, quirks()->catalog(),
+ quirks()->db_schema(), 0,
"adbc_fkey_parent_2_test",
+ "id_primary_col1");
+ ForeignKeyColumnUsagesTest(child_constraint, quirks()->catalog(),
+ quirks()->db_schema(), 1,
"adbc_fkey_parent_2_test",
+ "id_primary_col2");
+
+
TestedConstraints.adbc_fkey_child_test_id_child_col1_id_child_col2_fkey = true;
+ } break;
+ }
+ }
+
+ ASSERT_TRUE(TestedConstraints.adbc_fkey_child_test_pkey);
+ ASSERT_TRUE(TestedConstraints.adbc_fkey_child_test_id_child_col3_fkey);
+
ASSERT_TRUE(TestedConstraints.adbc_fkey_child_test_id_child_col1_id_child_col2_fkey);
}
void ConnectionTest::TestMetadataGetObjectsCancel() {
diff --git a/c/validation/adbc_validation.h b/c/validation/adbc_validation.h
index df5e17ff..e5b31e02 100644
--- a/c/validation/adbc_validation.h
+++ b/c/validation/adbc_validation.h
@@ -86,6 +86,32 @@ class DriverQuirks {
return std::nullopt;
}
+ /// \brief Get the statement to create a table with a composite primary key,
+ /// or nullopt if not supported.
+ ///
+ /// The table should have two columns:
+ /// - "id_primary_col1" of Arrow type int64 (together forming a composite
primary key)
+ /// - "id_primary_col2" of Arrow type int64 (together forming a composite
primary key)
+ virtual std::optional<std::string> CompositePrimaryKeyTableDdl(
+ std::string_view name) const {
+ return std::nullopt;
+ }
+
+ /// \brief Get the statement to create a child table with foreign keys,
+ /// or nullopt if not supported.
+ ///
+ /// The child table should have three columns:
+ /// - "id_child_col1" of Arrow type int64 (primary key, foreign key)
referencing "id" in
+ /// the parent 1 primary key table
+ /// - "id_child_col2" of Arrow type int64 (composite foreign key) together
with:
+ /// - "id_child_col3" of Arrow type int64 (composite foreign key) referencing
+ /// "(id_primary_col1, id_primary_col2)" in the parent 2 primary key table
+ virtual std::optional<std::string> ForeignKeyChildTableDdl(
+ std::string_view child_name, std::string_view parent_name_1,
+ std::string_view parent_name_2) const {
+ return std::nullopt;
+ }
+
/// \brief Return the SQL to reference the bind parameter of the given index
virtual std::string BindParameter(int index) const { return "?"; }
@@ -215,6 +241,7 @@ class ConnectionTest {
void TestMetadataGetObjectsColumns();
void TestMetadataGetObjectsConstraints();
void TestMetadataGetObjectsPrimaryKey();
+ void TestMetadataGetObjectsForeignKey();
void TestMetadataGetObjectsCancel();
void TestMetadataGetStatisticNames();
@@ -255,6 +282,7 @@ class ConnectionTest {
TestMetadataGetObjectsConstraints();
\
}
\
TEST_F(FIXTURE, MetadataGetObjectsPrimaryKey) {
TestMetadataGetObjectsPrimaryKey(); } \
+ TEST_F(FIXTURE, MetadataGetObjectsForeignKey) {
TestMetadataGetObjectsForeignKey(); } \
TEST_F(FIXTURE, MetadataGetObjectsCancel) { TestMetadataGetObjectsCancel();
} \
TEST_F(FIXTURE, MetadataGetStatisticNames) {
TestMetadataGetStatisticNames(); }
