OleMussmann opened a new pull request, #1099:
URL: https://github.com/apache/arrow-adbc/pull/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)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
