The most stable and maintainable code base I ever worked on had every single query in XML files. When you delegate the majority of the task to an abstraction layer that magically modifies your queries on the fly, bad things happen. When you're forced to write the query yourself, you have time to think about what you are doing. I don't remember soft-deletes being an issue at all - literally non-existent - 10 years ago, when all SQL queries were typed out by hand. The fact is they don't, because abstractions inherently make developers not inspect the behavior of their code as deeply as they should. Again, should every developer on a codebase understand in which situations the abstraction kicks in? Yes.
DELETE WICKR PRO ACCOUNT UPDATE
Back in the MyISAM days (before foreign keys), I found an "account deletion" mechanism that executed a "DELETE FROM messages WHERE userid = :userid AND deleted = false" - soft-deleted rows were not deleted when required, because an abstraction layer excluded soft-deleted rows in a DELETE query and the original developer never noticed.Ĭ) What happens with UPDATE and DELETE queries? I've seen abstractions that only append the soft-delete mechanism to SELECTs, and others that also affect UPDATEs and DELETEs. When the abstraction layer enforces a "deleted = false" on every query, it can be difficult or impossible to force backtracking to include soft-deleted rows. We're talking about what happens in reality, not what should happen in an ideal fantasy world.ī) Developers missing the case where they should be including soft-deleted rows. Is it "wrong" to bypass the model? Most of the time, yes! But it happens every day. Most complex queries wind up being written as raw SQL or a parsed variant, that never executes the model behavior to append the "AND deleted = false" clause. I've also seen abstractions where that clause is added to all SELECT, UPDATE, and DELETE queries.Ī) Developers bypassing the model, executing a complex JOIN that includes the table in question, and forgetting they need the "deleted = false". I've seen other abstractions where "AND deleted = false" can be automatically added to every SELECT query. I've seen abstraction layers where it's impossible to add a default clause to every SELECT query for a model.
Yes, it's technically the developers' fault for not understanding how and when the abstraction kicks in, but that doesn't mean it's "simple", or that these cases are avoided. Developers assume their configuration, such as appending a "deleted = false" clause, applies to every query without manually verifying each one. The majority of incidents I encounter with this bug occur specifically because of the complexity introduced with abstracted query building via ORMs and DBALs.
Happens so often it's impossible to keep count. I've seen this bug over and over again at every company I've worked for. Finally, ORM abstractions often automate soft-delete in such a way that makes it exhausting for developers to validate every query. Discipline aside, it's difficult for every developer on a team to remember which tables use soft-delete, and when checking that flag is or is not necessary. Managing soft-deletes on a database table requires an attention to detail, with every single query ever touching that table, that many developers lack the discipline to handle. The query used to load one item is "WHERE id = :id" instead of the correct "WHERE id = :id AND deleted = false". When viewing a single item (ex: message) the URL contains a unique identifier, whether an auto-increment integer, UID, etc. When you view the list of items (ex: inbox), the database query includes a "WHERE deleted = false" to exclude rows which have been soft-deleted.
That bug is extremely common, and the source is always the use of soft-deletes in the database.
0 kommentar(er)
