[Proposal]: Dbal Deleting Loader

[norberttech]

Describe the Proposal

The idea is to create a new loader that will delete all rows that matches
the row from dataframe.

So for the following dataset:

id
1
2
3

This loader should perform a bulk delete operation.

DELETE FROM `table` WHERE `id` IN (1, 2, 3)

It should work the same way when we have multiple rows in the dataframe.

For example, if we have the following dataframe:

id	group
1	A
2	B
3	A

In that case the loader should perform a bulk delete operation like this:

DELETE FROM `table` WHERE (`id`, `group`) IN ((1, 'A'), (2, 'B'), (3, 'A'))

API Adjustments

Dbal Bulk Library

• Flow\Doctrine\Bulk\Dialect::prepareDelete(TableDefinition $table, BulkData $bulkData) : string;
• Flow\Doctrine\Bulk\QueryFactory::delete(AbstractPlatform $platform, TableDefinition $table, BulkData $bulkData) : string;
• Flow\Doctrine\Bulk::delete(Connection $connection, string $table, BulkData $bulkData) : void

Dbal Adapter

• dbal_dbal_table_delete(array|Connection $connection, string $table) : DbalLoader
• DbalLoader - add new type of operation delete next to existing insert and update

Are you intenting to also work on proposed change?

It's perfectly fine to just propose changes and wait for others to pick them up.

Yes

Are you interested in sponsoring this change? If you can't find time to work on given proposal but it's

time sensitive, it might be a good idea to consider sponsoring this change. Please find more details at https://flow-php.com/sponsor/

None

Integration & Dependencies

No new dependencies are needed

[norberttech]

Definition of Done

1. Extend Dialect interface with prepareDelete method
2. Implement prepareDelete in each dialect implementation
3. Add delete method to QueryFactory interface and implement in DbalQueryFactory
4. Add delete method to Bulk class
5. Update DbalLoader class to support delete operations
6. Add helper function for to_dbal_table_delete
7. Add unit and integration tests
8. Update documentation
9. Prepare example in /examples/topics/data_writing/database_delete (look at database_upsert for inspiration)

Testing Strategy

1. Unit Tests:

   • Test each dialect's prepareDelete method with different column combinations
   • Test the delete method in the Bulk class
   • Test the DbalLoader with the 'delete' operation
   • Test bulk delete with different data types

2. Integration Tests:

   • Test the complete delete flow against real databases (PostgreSQL, MySQL, SQLite)
   • Test with single column and composite key scenarios
   • Test with large datasets to verify performance