0.2.0 Demo Dataset Testing

[zackkrida]

Discussed in #4163

^{Originally posted by zackkrida January 23, 2025}
In our upcoming 0.2.0 release we've introduced several new demo datasets. For each of these datasets we need to test:

• inserting into each table
• Viewing and editing a single record page in each table
• Deleting a record from each table
• Viewing/editing foreign key records

[zackkrida]

Unable to delete rows from

Bike Shop -> Equipment:

Unable to delete row. ForeignKeyViolation: update or delete on table "Equipment" violates foreign key constraint "Service Requests_equipment_id_fkey" on table "Service Requests" DETAIL: Key (id)=(3) is still referenced from table "Service Requests". CONTEXT: SQL statement " WITH delete_cte AS (DELETE FROM "Bike Shop"."Equipment" WHERE (msar.format_data(id))::text IN (SELECT jsonb_array_elements_text('["3"]')) RETURNING *) SELECT count(1) FROM delete_cte " PL/pgSQL function msar.delete_records_from_table(oid,jsonb) line 13 at EXECUTE

Bike Shop -> Service

Unable to delete row. ForeignKeyViolation: update or delete on table "Service Requests" violates foreign key constraint "Service Milestones_service_request_id_fkey" on table "Service Milestones" DETAIL: Key (id)=(8) is still referenced from table "Service Milestones". CONTEXT: SQL statement " WITH delete_cte AS (DELETE FROM "Bike Shop"."Service Requests" WHERE (msar.format_data(id))::text IN (SELECT jsonb_array_elements_text('["8"]')) RETURNING *) SELECT count(1) FROM delete_cte " PL/pgSQL function msar.delete_records_from_table(oid,jsonb) line 13 at EXECUTE

[pavish]

Bikeshop

• Customers
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • first_name and last_name columns have NOT NULL set on them. User clicking on new row will see an error until they fill in these columns.
  • Update ✅
  • Delete [ ⚠️ ATTENTION REQUIRED ]
    • Schema has several tables linked with each other, Customers <- Service Requests <- Service Milestones.
    • Deleting a Customer is a pain since I first have to delete all linked records, and I assume it'll be a common requirement.
    • We might want to provide the user an additional option to delete with cascade. (New feature after Beta).
    • We could improve how we display the error message to ensure the user understands it clearly.
    • This is a common comment for all Delete operations in all tables.
  • Viewing record page ✅
  • Editing record page ✅
  • Edit related records in record page ✅
• Equipment
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • serial_number has NOT NULL set on it. User clicking on new row will see an error until they fill in the column.
  • Update ✅
  • Delete ✅
  • Viewing record page ✅
  • Editing record page ✅
  • Edit related records in record page ✅
• Equipment Types
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name has NOT NULL set on it. User clicking on new row will see an error until they fill in the column.
  • Update ✅
  • Delete ✅
  • Viewing record page ✅
  • Editing record page ✅
  • Edit related records in record page ✅
• Mechanics
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • first_name and last_name columns have NOT NULL set on them. User clicking on new row will see an error until they fill in these columns.
  • Update ✅
  • Delete ✅
  • Viewing record page ✅
  • Editing record page ✅
  • Edit related records in record page ✅
• Service Milestones
  • Insert ✅
  • Update ✅
  • Delete ✅
  • Edit FK columns ✅
  • Viewing record page ✅
  • Editing record page ✅
• Service Requests
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • request_description has NOT NULL set on it. User clicking on new row will see an error until they fill in the column.
  • Update ✅
  • Delete ✅
  • Edit FK columns ✅
  • Viewing record page ✅
  • Editing record page ✅
  • Edit related records in record page ✅
• Service Statuses
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name has NOT NULL set on it. User clicking on new row will see an error until they fill in the column.
  • Update ✅
  • Delete ✅
  • Viewing record page ✅
  • Editing record page ✅
  • Edit related records in record page ✅

[pavish]

Hardware Store

• Assets
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name, serial_number, and store_id columns have NOT NULL set on them. User clicking on new row will see an error until they fill in these columns.
  • Update ✅
  • Delete ✅
  • Edit FK columns ✅
  • Viewing record page ✅
  • Editing record page ✅
  • Edit related records in record page ✅
• Customers
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • first_name and last_name columns have NOT NULL set on them. User clicking on new row will see an error until they fill in these columns.
  • Update ✅
  • Delete ✅
  • Viewing record page ✅
  • Editing record page ✅
  • Edit related records in record page ✅
• Rentals
  • Insert ✅
  • Update ✅
  • Delete ✅
  • Edit FK columns ✅
  • Viewing record page ✅
  • Editing record page ✅
• Store Locations
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name has NOT NULL set on them. User clicking on new row will see an error until they fill in the column.
  • Update ✅
  • Delete ✅
  • Viewing record page ✅
  • Editing record page ✅
  • Edit related records in record page ✅
• Transactions
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • asset_id, customer_id, and transaction_date have NOT NULL set on them.
  • Update [ ⚠️ ATTENTION REQUIRED ]
    • check constraint on transction_type. Might be better as a FK column?
  • Delete ✅
  • Edit FK columns ✅
  • Viewing record page ✅
  • Editing record page ✅
  • Edit related records in record page ✅

[pavish]

Ice Cream Employee Management

• Employees
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name, role, and email have NOT NULL set on them
  • Update ✅
  • Delete ✅
  • Editing record page ✅
• Schedules
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • employee_id, start_time, and end_time have NOT NULL set on them
  • Update ✅
  • Delete ✅
  • Editing record page ✅
• Timesheets
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • employee_id, clock_in, and schedule_date have NOT NULL set on them
  • Update ✅
  • Delete ✅
  • Editing record page ✅

[pavish]

Library Makerspace

• Equipment
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name has NOT NULL set on it.
  • PK configured correctly ✅
• Equipment Statuses
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name has NOT NULL set on it.
  • PK configured correctly ✅
• Equipment Training
  • Insert ✅
  • PK configured correctly ✅
• Equipment Types
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name has NOT NULL set on it.
  • PK configured correctly ✅
• Job Statuses
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name has NOT NULL set on it.
  • PK configured correctly ✅
• Jobs
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • queue_order has NOT NULL set on it.
    • "Library Makerspace".check_training() condition present.
  • Update [ ⚠️ ATTENTION REQUIRED ]
    • "Library Makerspace".check_training() condition present.
  • PK configured correctly
• Patrons
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name and email have NOT NULL set on them.
  • PK configured correctly ✅

[pavish]

Library Management

• Authors
  • Insert ✅
  • PK configured correctly ✅
• Books
  • Insert ✅
  • PK configured correctly ✅
• Checkouts
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • Checkout Time and Due Date have NOT NULL set on them
  • PK configured correctly ✅
• Items
  • Insert ✅
  • PK configured correctly ✅
• Media
  • Insert ✅
  • PK configured correctly ✅
• Patrons
  • Insert ✅
  • PK configured correctly ✅
• Publishers
  • Insert ✅
  • PK configured correctly ✅

[pavish]

Museum Exhibits

• Acquisition Types
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • type_name has NOT NULL set on it
  • PK configured correctly ✅
• Collections
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name has NOT NULL set on it
  • PK configured correctly ✅
• Exhibits
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name, start_date, and location_id have NOT NULL set on them
  • PK configured correctly ✅
• Item_Collections
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • item_id, and collection_id have NOT NULL set on them
  • PK configured correctly ✅
• Items
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name, serial_number, acquisition_date, and acquisition_type_id have NOT NULL set on them
  • PK configured correctly ✅
• Locations
  • Insert
    • name has NOT NULL set on it
  • PK configured correctly ✅

[pavish]

Nonprofit Grant Tracking

• Grant Allocations
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • grant_id, and allocated_amount have NOT NULL set on them
  • PK configured correctly ✅
• Grant Lifecycle
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • grant_id, stage_id, and staff_id have NOT NULL set on them
  • PK configured correctly ✅
• Grants
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name, amount, start_date, and end_date have NOT NULL set on them
  • PK configured correctly ✅
• Lifecycle Stages
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name has NOT NULL set on it
  • PK configured correctly ✅
• Staff
  • Insert [ ⚠️ ATTENTION REQUIRED ]
    • name has NOT NULL set on it
  • PK configured correctly ✅

[pavish]

@kgodey @zackkrida @mathemancer

Result: ✅

• All tables are working as expected.
• Sequences are fine.
• PK configuration is fine.

Recommendations:

• Reduce the number of NOT NULL and check constraints to reduce friction for non-technical users.
  • Or ensure that they are explicitly mentioned and users are walked through them in the guides.
• We need better error messages when Delete operation fails due to linked records.
• A feature to cascade delete operations (based on user preference) would be useful.

[mathemancer]

Unable to delete rows from

Bike Shop -> Equipment:

Unable to delete row. ForeignKeyViolation: update or delete on table "Equipment" violates foreign key constraint "Service Requests_equipment_id_fkey" on table "Service Requests" DETAIL: Key (id)=(3) is still referenced from table "Service Requests". CONTEXT: SQL statement " WITH delete_cte AS (DELETE FROM "Bike Shop"."Equipment" WHERE (msar.format_data(id))::text IN (SELECT jsonb_array_elements_text('["3"]')) RETURNING *) SELECT count(1) FROM delete_cte " PL/pgSQL function msar.delete_records_from_table(oid,jsonb) line 13 at EXECUTE

Bike Shop -> Service

Unable to delete row. ForeignKeyViolation: update or delete on table "Service Requests" violates foreign key constraint "Service Milestones_service_request_id_fkey" on table "Service Milestones" DETAIL: Key (id)=(8) is still referenced from table "Service Milestones". CONTEXT: SQL statement " WITH delete_cte AS (DELETE FROM "Bike Shop"."Service Requests" WHERE (msar.format_data(id))::text IN (SELECT jsonb_array_elements_text('["8"]')) RETURNING *) SELECT count(1) FROM delete_cte " PL/pgSQL function msar.delete_records_from_table(oid,jsonb) line 13 at EXECUTE

What action do you think we should take here?

Those errors are precisely what one would expect with the data models designed. Also, the error messages are even informational enough for a user to solve the problem if they really want to.

[mathemancer]

@kgodey @zackkrida @mathemancer

Result: ✅

• All tables are working as expected.
• Sequences are fine.
• PK configuration is fine.

Recommendations:

• Reduce the number of NOT NULL and check constraints to reduce friction for non-technical users.

  • Or ensure that they are explicitly mentioned and users are walked through them in the guides.

For the various constraints, I'd prefer mentioning and walking users through them. This set of data sets is supposed to demonstrate features, and show possible data models (I guess). We should be focusing on that, not making them easy to click around in.

• We need better error messages when Delete operation fails due to linked records.
• A feature to cascade delete operations (based on user preference) would be useful.

We should offer that when users are setting up foreign key constraints. Allowing it for each delete operation is a foot bazooka.