SQL Server · CrudLabDB

New article

Feature 1 Insert: Save draft stores it as DRAFT (feature 11), Save & publish makes it live straight away (feature 12).

INSERT INTO article
Stored in an NVARCHAR(MAX) column: long text and Thai (ภาษาไทย) are fine.
Unique business key (unique index UX_article_code). Empty = next number from the SEQUENCE.
Separate with commas. Duplicates are removed.
Cancel * required
SQL behind this page

Captured by a Hibernate StatementInspector and by the JdbcTemplate code. ? marks a parameter: values are sent separately, which keeps SQL injection out.

Loading this page
1
SELECT
  COUNT(*)                                                                AS total,
  SUM(CASE WHEN deleted = 0 AND archived = 0 THEN 1 ELSE 0 END)           AS active,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND status = 'DRAFT' THEN 1 ELSE 0 END)       AS drafts,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND status = 'PUBLISHED' THEN 1 ELSE 0 END)   AS published,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND status = 'UNPUBLISHED' THEN 1 ELSE 0 END) AS unpublished,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND pinned = 1 THEN 1 ELSE 0 END)    AS pinned,
  SUM(CASE WHEN deleted = 0 AND favourite = 1 THEN 1 ELSE 0 END)                  AS favourites,
  SUM(CASE WHEN deleted = 0 AND archived = 0 AND is_read = 0 THEN 1 ELSE 0 END)   AS unread,
  SUM(CASE WHEN deleted = 0 AND locked = 1 THEN 1 ELSE 0 END)                     AS locked,
  SUM(CASE WHEN deleted = 0 AND archived = 1 THEN 1 ELSE 0 END)                   AS archived,
  SUM(CASE WHEN deleted = 1 THEN 1 ELSE 0 END)                                    AS trash
FROM article
2
select distinct a1_0.category from article a1_0 where a1_0.deleted=0 order by a1_0.category