?????!

2019??? ?? ?????? ??? ???? ???? ??? ???? pg-index-health?? ?? ?? ??? ??? ????. ?? ?? ? ???? ? ??? ??? ??????? ?? ???? ??????.

??? ?? pg-index-health? ???? ???????. 2024??? ?? ???? ??? ?? ?? "???"? ??? ???? ????? ??? ??? ??? ??? ??????.

???????? ??? ?? ??????? ??

?? 2015??? PostgreSQL? ??? ??? ? ???? ??? ?????? ??? ? Tensor?? ???????.

2015??? ??? ??? ??????? ??? ???? ?? ??? ??????. ????? ??? ?????? ??? ????? ?? ?? ??? ??? ?? ???? ?? ???? ?? ??? ??????. ?? ???? ????? ????? ?? ????? ???? ??? ?? ??????.

????? ???? ???????? ???? ??????.
?????? ?? ?? ?????, ??? ? ?????? ?? ??? ?? ??????. ?? ? ?? ?? ??????? ??? ????? ???? ??????. ???? ?? ??? ??? ???? ?????? ?? ??? ? ????? ?? ???? ???? ???? ??????.

??? ????? ? ??

??? ????? ?? ???? ??? ?? ????. ????? ?? ???? ?? ??? ??? ?? ??? ???? ??? ????. ??? ??? Martin Fowler? ?? ??????.

???????? ?? ?? ???? ??? ??? ????? ??? ?? ?????. ??? ??? ?? ????? ??(Docker, Testcontainers)? ??? ?? ???????. ??? ??? ????? ? ?? ????? ????. ?? ??? ??? ?? ? ????. ?? ? ??? ??? ??? ??? ???? ????.

?? ??? ?? ?? ??? ??? ?? ?? ???? ??? ?? ???? ????? ???? ?? ??? ???? ?? ?? ? ?? ???? ?? ??? ???? ????.

?? ????? ?? ???? ?? ??? ??????. ? ???? ???? ??? ????? ??? ?? ?? ??? ????.

?? ?? ??? ???

Java ? Kotlin ??? ?? ?? ??? ?? ???? ?????. Kotlin ???? ?? ????? ???? ??? decekt???. Java ??????? ?? ?? ??? ??(?? Linter??? ?)? ??? ? ????. ?? ???? Checkstyle, PMD, SpotBugs, Error Prone ?? ????. ?? ?? ??? ??? ?? ???? ???? ? ????.

?? detekt? Checkstyle ?? ?? ??? ???? ????? ?? ?? ??? ?????.

?????? ??????? ?? ?? ??

?? ????????? ?????? ??? ?? ?????? ??? ???? ?????? ?? ?????? ??????? ???? ??? ????.

Java ????? ??????? ???? ?? ??? Liquibase? Flyway???. ?????? ??? ?? ?? ?? ??? ?? ?????? ? ?????? ???. ?????? ??? ???? ?? ???? ?????? ?? ?? ??? ?? ??? ????? ??? ??????? ???? ???.

?? SQL? ??????? ???? ?? Liquibase? ?? XML ?? ??? ??? ?? ?? ??? ???? ???? ??? ????? ?? ?????. ?? ???? ? ?? "?? ????? PostgreSQL ??? ?? 6?? ?"?? ??????.

SQL ?????? ?? ??

???????? SQL ??? ????? ????? SQL? ???? Checkstyle? SQLFluff? ???? ?? ????. ? ??? ?? ??????? ??(PostgreSQL ??)? ???? CI ?????? ??? ? ????. 60? ??? ??? ?? ??? ??? ???? ??? ? ? ??, SQL ?? ?/??? ??, ????, ??? ? ?? ?? ?? ??? ? ????.

??? ?? ??? ??? ?? ??? ?????.

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

? ??? SQL ??? ?? ????? ?? ????. ?? ??? ?? ?? ?? ??? ?? ??? ?? ?? ??? ? ?? ??? ??? ???? ????. SQLFluff? ??? ???? ???? ??? ?????.

SQLFluff ??

?? ???? ??? ??? ??? ????.

?? SQLFluff? select ??? ?? ? ?? ??? ??? ??? ??????. ? ??? ?? ???? ?? ?? ?? ????. ??? ?. ? ?? ?? ?? ??? ??? ? ?????. ?? ??? ?? ??? ?? ?? ??? ?????. ? ??? join ?? ?? ?? ??? ???????. ?? ??? ???? ???? ?? ?????.

?? ?? SQLFluff? ? ?? ?? ??? ? ?? ?? ????? one, two? ?????.

?????? ???? ?????? ?? ??

?????? ??? ??? ??? ? ????. ??? ?????? ??? ?? ?????. ??????? ?? ? ????. ??? ??????? ?? ??????? ??? ??? ?? ????. ????? ??? ?? ???? ??????? ?? ??

?? ??? ??

PostgreSQL(?? ?? ??? ??????? ?????)? ?? ??? ?? ?? ??? ?? ?????? ???? information_schema ???? ??? ?????. information_schema? ?? ??? ???? ??, ?? ?? ???? ??? ??? ? ????(??? ?? SchemaCrawler? ???? ?????).

PostgreSQL?? ????? information_schema ?? ?? ??????? ?? ??? ?? ?? ? ?? ??? ???? ??? ????(pg_catalog ???)? ??? ? ????.

???????

????? ??? PostgreSQL? ? ??????? ??? ?? ??(?? ??? ?????, ??? ?????, ?? ??? ??? ????? ?)? ?????. ?? ?? ???? ??? ?????. ? ??????.

??? ??? ?? ??? ??? ???? ?? ??? ????? ???? ???? ??? ??? ? ????.

• ???? ?? ???? ?????.
• ??? ???? ??? ???? ?????.

??? ???? ???? ? ????. ??? ??? ??? ??? ???? ?????. ??? ??? ? ??? ??? ????? ?? ???? ?? ?????. ?? ?? ?/??/?? 1? ???? ???? ??? ?? ?? ??? ??? ??? ???? ??? ???? ???.

?????? ????? ???? ?? ??? ? ????? ????? ???? ???? ??? ???? ????.

pg-index-health? ? ??

??? ??? ??? ?????? ?? ?? ?????? ???? ?????? ??? ????? ????? ?? pg-index-health?? ?? ??? ??????.

? ????? ?? ?? ??? ???? ????.

• ??? ???? ??? SQL ?? ??? ?? ?????(?? 25?? ??? ???? ??). ??? Java ??????? ???? ?? ????? ??? ??? ?????? ???? ? ????.
• ??? ?? — ?? ??? ??? ???? ???? ??? ?????.
• ?? ???? ??? ?????? ????? ???? ?? HighAvailabilityPgConnection ???.
• SQL ??? ???? ??? ??? ?? ??? ????? ???????.
• ??/????/?? ???? ??? ???? ??? ???? ?? Spring Boot ??????.
• ??? ??? ?? ?? SQL ??????? ??? ? ?? ?????? ???

?? ??

?? ??(?????? ?)? ? ???? ????.

• ??? ??(?? ??)
• ?? ??(??? ???? ??).

??? ??

??? ??? ????? ??? ?????? ?????? ??? ?? ??? ????. ??? ???? ??? ??? ???? ????? ?? ????? ? ???? ?????.

??, ??, ??? ???? ? ?? ???? ??? ?????? ????? ??? ?????. ?? ???? ??? ????? ?? ????? ???? ?? ??? ?? ??? ?????? ?? ?? ??? ?????. ??? ??? ?? ??? ???? ?? ??? ?? ??? ???? ??? ??? ??? ????? ?? ????? ???? ????.

?? ????? PostgreSQL??? ??? ? ????? ??? ???? ???? ???? ???? ????. ??? ?? ???? ??? ?????? ???? ??? ??? ?? ??? ? ????. ???? ???? ??? ???? ????? ????? ? ???? ?? ??? ???? ??? ???? ???.

?? ??

?? ???? ??? ??? ???? ??? ?????? ?? ? ?? ?? ????? ??? ? ????. ?? ????? ???? ?? ?? ???? ???????? ??? ?? ????. ??? ???? ??? ???? ?? ???? ???? ??? ???? ???? ? ??? ??? ???? ?? ?????.

pg-index-health? ???? ??

pg-index-health? ?? ?? ??? ??? ?????? ?????? ??? ???? ?? ???? ???? ????.

Spring Boot ??????? ?? ??? ???? ???? ???? ???.

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

?? ?? ?? ???? ?????.

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

? ?????? ?? ??? ?? ??? ???? ?????. ?? ?? ??????? ??? ????? ??? ?? ??????? ?? ?? ??? ????? ?????.

?????? ? ???? ? ??? ????? ???. ?? ??????? ??? ? ??? ??? ???? ?????. ???? ?? ??? ???? ?? ????? ??? ???? ???. ?, ???????? ??? ????? ????? ???? ????.

?? ?? ? ?? ??

?? ?? ???? ????? pg-index-health? ???? ??? ? ??? ?? ???? ?? ?????. ?? ?? ??? ????? ??? ?? ?? ????. ?? ??, ?????? ??? ????? ?? ?? ???? ?????. PostgreSQL? ???? ?? ?? ?????? ??? ??? ??? ? ????. ????????? ??? ?? ? ????.

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

? ???? ??? ?? ??? ??? ?? ????. ? ?? ?? ?? ??(TABLES_WITHOUT_DESCRIPTION, COLUMNS_WITHOUT_DESCRIPTION, FUNCTIONS_WITHOUT_DESCRIPTION)? ??? ????.

?? ??? ??? ??? ? ????.

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<DatabaseCheckOnHost<? extends DbObject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}

?? ??? ??? ?????.

create table if not exists demo.warehouse
(
    id bigint primary key generated always as identity,
    name varchar(255) not null
);

comment on table demo.warehouse is 'Information about the warehouses';
comment on column demo.warehouse.id is 'Unique identifier of the warehouse';
comment on column demo.warehouse.name is 'Human readable name of the warehouse';

pg-index-health? ??? ? ?????? ??? ?? ??? ??? ?? ???? ?? ?? ??? ?? ??? ? ????. ??? ??? ???? ??? ?????? ?? ??? ????. ??? ?? ??? ?? ??? ???? ?? ?? ????.

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .filter(c -> c.getDiagnostic() != Diagnostic.TABLES_WITHOUT_DESCRIPTION &&
            c.getDiagnostic() != Diagnostic.COLUMNS_WITHOUT_DESCRIPTION)
        .forEach(c -> assertThat(c.check())
            .as(c.getDiagnostic().name())
            .isEmpty());
}

?? ?? ?? ???? ??? ?? ? ? ??? ???????.

?? ?? ?? ???

PostgreSQL? MVCC ????? ???? ?? ?? ?? ?? ?? ???(?? ???)? ??? ??? ??? ??? ?? ??? ??? ? ????. ?? ?? ?? ?? ?????? ?? ?? ?? ?? ??? ????? ?? ?? ?? ??? ? ????.

?????? ?? ??? ??? autovacuum ????? ?? ????? ??? ??? ??? ??? ????? ????. ???? ??? ??? ????? ??? ??? ??? ?? ?? ?? ?? ??? ??? VACUUM FULL ??? ???? ????. ?? ???? ?? ? ??? ?? ? ???? ???? ?? ?????? ?? ?? ??? ??????.

???? ?? ??? ??? ??? ???? ?? pg_repack? ?? ?? ?? ????? ???? ??? ????. pg_repack? ?? ?? ?? ? ??? ?? ???? ?? ? ?? ?? ??? ?? ??? ??? ????. TABLES_WITHOUT_PRIMARY_KEY ??? ?? ?? ?? ???? ???? ?? ?? ?? ??? ???? ? ??? ???.

??? ?? ?? ?? ???? ????. ? ???? bloat? ???? pg_repack? ?? ??? ? ?? ??? ?????.

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

??? ???

?? ??????? ???? ??? ????? ???? ??? ??? ?? ?????. Database-as-a-Service ???? ??? ? ??? ? ?? ?? ?????? ??? ?? ??? ??? ?? ??? ????.

???? ? ???? ????? ??? ??????. ??? ???? ?? ??? ?? ???? ????? ??? ?? ? ???? ??? ?????. ?? ??? ???? ?? ?? ???? ???? ?? ??? ????. ??? ???? ????? ???? ???? ??? ??? ??? ??? ?? ??? ???? ??? ???? ??? ??? ? ????. ??? ???? ??? ?? ?????? ??? ??? ????? ???? ???.

?????? ???? ???? ???? ???? ??? ?? ?????. ?? ?? ?? ?? ?? ??? ???? ?????. id ?? ???? ????? ?? ????? ????? ??? ?? ?? ?? ??? ????.

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

??? ?? ????? ??? ??? ?????. unique ???? ?(?? ? ??)? ???? PostgreSQL? ???? ?? ?(?? ? ??)? ?? ?? ???? ?????. . ?? ???? ???? ??? ??? ????. ???? ???? ?????. ??? ?? ??? ??? ? ?? ???? ?? DUPLICATED_INDEXES ??? ?? ?? ??? ? ????.

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

???(????) ???

???? ???? ?? ?? ?? ?????. ?? ???? ???? ?? ?? ???? ? ??? ???? ??? ? ????. ?? ?? A, A B, A B C? ?? ?? ?? ???? ???? ????? ?????. ? ???? ?? ? ???? ? ?? ???? ?????? ?? ??? ? ????(? ???? ???? ?? ????). . ??? ?? ???? ???? ??? ??? ??? ??? ? ??? INTERSECTED_INDEXES ??? ??? ??? ????? ???????.

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

???? ?? ?? ?

PostgreSQL??? ?? ???? ???? ?? ?? ? ?? ??? ??? ? ????. ?, ?? ???? ???? ????? ???? ???? ??? ???? ???? ????. ?? ???? ??? ??? ?? ?? ?? ?? ?? ???? ?? ?? ????. ??? ??? ?? ???? ??? ??? ?? ????.

?? ?? ???????(?? PostgreSQL 16.6? ???? ????).

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

orders ???? order_item ???? ????. order_id ?? ?? ?? ?? ?????. ?? ?? ?? ?? ? ?? ?? ?? ?? ??? ???? ?? ?? ??? ?? ?????.

???? ???? ??? ??? ??? ?????. 100,000?? ??? ?????. ??? 2? ???? ???? 1????.

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<DatabaseCheckOnHost<? extends DbObject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}

ID=100? ??? ?? ??? ????? ?? 2?? ?? ????? ????? ???. ?? ???? id ?? ???? ???? ? ??? ??? ? ? ????.

create table if not exists demo.warehouse
(
    id bigint primary key generated always as identity,
    name varchar(255) not null
);

comment on table demo.warehouse is 'Information about the warehouses';
comment on column demo.warehouse.id is 'Unique identifier of the warehouse';
comment on column demo.warehouse.name is 'Human readable name of the warehouse';

??? ? ??? ???????? ?? ?? ???? ???? ????? ???? ?? ? ? ????. ?? ??? ?? ??? ?? ?? ??? ?? ???? ???(Buffers ????).

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .filter(c -> c.getDiagnostic() != Diagnostic.TABLES_WITHOUT_DESCRIPTION &&
            c.getDiagnostic() != Diagnostic.COLUMNS_WITHOUT_DESCRIPTION)
        .forEach(c -> assertThat(c.check())
            .as(c.getDiagnostic().name())
            .isEmpty());
}

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .forEach(c -> {
            final ListAssert<? extends DbObject> listAssert = assertThat(c.check())
                .as(c.getDiagnostic().name());
            switch (c.getDiagnostic()) {
                case TABLES_WITHOUT_DESCRIPTION, COLUMNS_WITHOUT_DESCRIPTION -> listAssert.hasSizeGreaterThanOrEqualTo(0); // ignored

                default -> listAssert.isEmpty();
            }
        });
}

?? ?? ?? ?? ?? ???? ???? ??? ?? ???? ?????.

@Test
void checksShouldWorkForAdditionalSchema() {
    final PgContext ctx = PgContext.of("additional_schema");
    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .forEach(c -> {
            final ListAssert<? extends DbObject> listAssert = assertThat(c.check(ctx))
                .as(c.getDiagnostic().name());

            switch (c.getDiagnostic()) {
                case TABLES_WITHOUT_DESCRIPTION, TABLES_NOT_LINKED_TO_OTHERS ->
                    listAssert.hasSize(1)
                        .asInstanceOf(list(Table.class))
                        .containsExactly(
                            Table.of(ctx, "additional_table")
                        );

                default -> listAssert.isEmpty();
            }
        });
}

?? ???? ?? ??? ???? ?? ??? ?? ?? ?????.

create table if not exists demo.payment
(
    id bigint not null, -- column is not marked as primary key
    order_id bigint references demo.orders (id),
    status int not null,
    created_at timestamp not null,
    payment_total decimal(22, 2) not null
);

FOREIGN_KEYS_WITHOUT_INDEX ??? ???? ?? ?? ??? ??? ??? ???? ?? ??? ??? ? ????.

???? Null ?

????? PostgreSQL? btree ???? null ?? ????? ????? ???? ????. ?? null ?? ???? ? ?? null? ???? ??? ??? ?? ????. ???? ?? null? ????. ?? INDEXES_WITH_NULL_VALUES? ??? ??? ???? ? ??? ???.

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<DatabaseCheckOnHost<? extends DbObject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}

-- well-formatted SQL
select
    pc.oid::regclass::text as table_name,
    pg_table_size(pc.oid) as table_size
from
    pg_catalog.pg_class pc
    inner join pg_catalog.pg_namespace nsp on nsp.oid = pc.relnamespace
where
    pc.relkind = 'r' and
    pc.oid not in (
        select c.conrelid as table_oid
        from pg_catalog.pg_constraint c
        where c.contype = 'p'
    ) and
    nsp.nspname = :schema_name_param::text
order by table_name;

-- poorly formatted SQL
SELECT pc.oid::regclass::text AS table_name, pg_table_size(pc.oid) AS table_size
FROM pg_catalog.pg_class  pc
JOIN pg_catalog.pg_namespace AS nsp
ON nsp.oid =  pc.relnamespace
WHERE pc.relkind = 'r’
and pc.oid NOT in (
  select c.conrelid as table_oid
  from pg_catalog.pg_constraint   c
  where    c.contype = 'p’
)
and nsp.nspname  = :schema_name_param::text
ORDER BY  table_name;

dependencies {
    testImplementation("io.github.mfvanek:pg-index-health-test-starter:0.14.4")
}

import io.github.mfvanek.pg.core.checks.common.DatabaseCheckOnHost;
import io.github.mfvanek.pg.core.checks.common.Diagnostic;
import io.github.mfvanek.pg.model.dbobject.DbObject;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;

@SpringBootTest
@ActiveProfiles("test")
class DatabaseStructureStaticAnalysisTest {

    @Autowired
    private List<DatabaseCheckOnHost<? extends DbObject>> checks;

    @Test
    void checksShouldWork() {
        assertThat(checks)
            .hasSameSizeAs(Diagnostic.values());

        checks.stream()
            .filter(DatabaseCheckOnHost::isStatic)
            .forEach(c -> assertThat(c.check())
                .as(c.getDiagnostic().name())
                .isEmpty());
    }
}

create table if not exists demo.warehouse
(
    id bigint primary key generated always as identity,
    name varchar(255) not null
);

comment on table demo.warehouse is 'Information about the warehouses';
comment on column demo.warehouse.id is 'Unique identifier of the warehouse';
comment on column demo.warehouse.name is 'Human readable name of the warehouse';

@Test
void checksShouldWork() {
    assertThat(checks)
        .hasSameSizeAs(Diagnostic.values());

    checks.stream()
        .filter(DatabaseCheckOnHost::isStatic)
        .filter(c -> c.getDiagnostic() != Diagnostic.TABLES_WITHOUT_DESCRIPTION &&
            c.getDiagnostic() != Diagnostic.COLUMNS_WITHOUT_DESCRIPTION)
        .forEach(c -> assertThat(c.check())
            .as(c.getDiagnostic().name())
            .isEmpty());
}