DDL and generated SQL: DELETED vs ACTIVE vs TIMESTAMP vs @SQLDelete

######## H2 ########
database: H2Dialect / H2 2.4.240 (2025-09-22)

== DDL Hibernate generates
create table article_active (active boolean not null, id bigint generated by default as identity, title varchar(255), primary key (id));
create table article_deleted (deleted boolean not null, id bigint generated by default as identity, title varchar(255), primary key (id));
create table article_manual (deleted boolean not null, id bigint generated by default as identity, title varchar(255), primary key (id));
create table article_stamped (deleted_at timestamp(6) with time zone, id bigint generated by default as identity, title varchar(255), primary key (id));

== SoftDeleteType.DELETED (default)
-- insert
   sql> insert into article_deleted (title,deleted,id) values (?,false,default)
-- remove(first)
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
   sql> update article_deleted set deleted=true where id=? and deleted=false
raw rows : [1 | first | true, 2 | second | false]
-- find(first) after delete -> null
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
-- HQL: select count(a) from ArticleDeleted a
   sql> select count(ad1_0.id) from article_deleted ad1_0 where ad1_0.deleted=false

== SoftDeleteType.ACTIVE
-- remove(x)
   sql> select aa1_0.id,aa1_0.title from article_active aa1_0 where aa1_0.active=true and aa1_0.id=?
   sql> update article_active set active=false where id=? and active=true
raw rows : [1 | x | false]

== SoftDeleteType.TIMESTAMP
before delete: [1 | y | false]
-- insert
   sql> insert into article_stamped (title,deleted_at,id) values (?,null,default)
-- remove(y)
   sql> select as1_0.id,as1_0.title from article_stamped as1_0 where as1_0.deleted_at is null and as1_0.id=?
   sql> update article_stamped set deleted_at=localtimestamp where id=? and deleted_at is null
after delete : [1 | y | true, 2 | z | false]
-- find(y) after delete -> null
   sql> select as1_0.id,as1_0.title from article_stamped as1_0 where as1_0.deleted_at is null and as1_0.id=?

== @SQLDelete + @SQLRestriction
-- insert
   sql> insert into article_manual (deleted,title,id) values (?,?,default)
-- remove(m)
   sql> select am1_0.id,am1_0.deleted,am1_0.title from article_manual am1_0 where am1_0.id=? and (am1_0.deleted = false)
   sql> update article_manual set deleted = true where id = ?
raw rows : [1 | m | true, 2 | n | false]
-- find(m) after delete -> null
   sql> select am1_0.id,am1_0.deleted,am1_0.title from article_manual am1_0 where am1_0.id=? and (am1_0.deleted = false)

== Deleting a row somebody else already soft-deleted
-- s1.flush()
   sql> update article_deleted set deleted=true where id=? and deleted=false
flush failed: OptimisticLockException -> StaleStateException: Unexpected row count (expected row count 1 but was 0) [update article_deleted set deleted=true where id=? and deleted=false]

######## POSTGRES ########
database: PostgreSQLDialect / PostgreSQL 17.11

== DDL Hibernate generates
create table article_active (active boolean not null, id bigint generated by default as identity, title varchar(255), primary key (id));
create table article_deleted (deleted boolean not null, id bigint generated by default as identity, title varchar(255), primary key (id));
create table article_manual (deleted boolean not null, id bigint generated by default as identity, title varchar(255), primary key (id));
create table article_stamped (deleted_at timestamp(6) with time zone, id bigint generated by default as identity, title varchar(255), primary key (id));

== SoftDeleteType.DELETED (default)
-- insert
   sql> insert into article_deleted (title,deleted) values (?,false)
-- remove(first)
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
   sql> update article_deleted set deleted=true where id=? and deleted=false
raw rows : [1 | first | true, 2 | second | false]
-- find(first) after delete -> null
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
-- HQL: select count(a) from ArticleDeleted a
   sql> select count(ad1_0.id) from article_deleted ad1_0 where ad1_0.deleted=false

== SoftDeleteType.ACTIVE
-- remove(x)
   sql> select aa1_0.id,aa1_0.title from article_active aa1_0 where aa1_0.active=true and aa1_0.id=?
   sql> update article_active set active=false where id=? and active=true
raw rows : [1 | x | false]

== SoftDeleteType.TIMESTAMP
before delete: [1 | y | false]
-- insert
   sql> insert into article_stamped (title,deleted_at) values (?,null)
-- remove(y)
   sql> select as1_0.id,as1_0.title from article_stamped as1_0 where as1_0.deleted_at is null and as1_0.id=?
   sql> update article_stamped set deleted_at=localtimestamp where id=? and deleted_at is null
after delete : [1 | y | true, 2 | z | false]
-- find(y) after delete -> null
   sql> select as1_0.id,as1_0.title from article_stamped as1_0 where as1_0.deleted_at is null and as1_0.id=?

== @SQLDelete + @SQLRestriction
-- insert
   sql> insert into article_manual (deleted,title) values (?,?)
-- remove(m)
   sql> select am1_0.id,am1_0.deleted,am1_0.title from article_manual am1_0 where am1_0.id=? and (am1_0.deleted = false)
   sql> update article_manual set deleted = true where id = ?
raw rows : [1 | m | true, 2 | n | false]
-- find(m) after delete -> null
   sql> select am1_0.id,am1_0.deleted,am1_0.title from article_manual am1_0 where am1_0.id=? and (am1_0.deleted = false)

== Deleting a row somebody else already soft-deleted
-- s1.flush()
   sql> update article_deleted set deleted=true where id=? and deleted=false
flush failed: OptimisticLockException -> StaleStateException: Unexpected row count (expected row count 1 but was 0) [update article_deleted set deleted=true where id=? and deleted=false]