Filter Queries

Found 11 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

#1. Columns with JSON, JSONB, or XML type

INFORMATION_SCHEMA+system catalog base tables

Find columns with JSON, JSONB, or XML type. Each columns should have the most appropriate data type.

General License: MIT (opens in new tab)

#2. Cycle in a hierarchy

system catalog base tables only

There should not be cycles in hierarchies meaning that the parent must always be specified. In this case a parent must reference to a child or to itself, otherwise it cannot be registered.

Problem detection License: MIT (opens in new tab)

#3. Derived tables that present data in json or xml format

INFORMATION_SCHEMA+system catalog base tables

Find views and materialized views that present data in json or xml format. Instead of recording data in this format in base tables one could generate the data value on the fly based on data that has been recorded in base tables.

General License: MIT (opens in new tab)

#4. Do not always depend on one's parent

system catalog base tables only

Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table.

General License: MIT (opens in new tab)

#5. Do not always depend on one's parent - column names are ot sufficiently different

system catalog base tables only

Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. Find only cases where the candidate key and foreign key column names are very similar (Levenshtein distance shorter than four).

Problem detection License: MIT (opens in new tab)

#6. Do not always depend on one's parent (INFORMATION_SCHEMA)

INFORMATION_SCHEMA only

Find where a hierarchical structure is implemented in a base table by having a foreign key that refers to a candidate key of the same table. This design is called adjacency list.

General License: MIT (opens in new tab)

#7. JSON type instead of JSONB type

INFORMATION_SCHEMA only

"In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys." (https://www.postgresql.org/docs/current/datatype-json.html)

Problem detection License: MIT (opens in new tab)

#8. ON DELETE SET NULL is probably missing

system catalog base tables only

Find implementations of the adjacency list design pattern, where the corresponding foreign key columns are optional but the foreign key constraint does not have ON DELETE SET NULL compensating action. Implement adjacency list correctly.

Problem detection License: MIT (opens in new tab)

#9. Perhaps a relationship should be irreflexive

system catalog base tables only

Enforce all the constraints. A binary relation is called irreflexive, if it does not relate any element to itself.

Problem detection License: MIT (opens in new tab)

#10. Recursive relationships with the same source and target

system catalog base tables only

Find incorrectly implemented adjacency lists.

Problem detection License: MIT (opens in new tab)

#11. The number of derived table columns with an array, a user-defined, XML, JSON, or JSONB type

INFORMATION_SCHEMA+system catalog base tables

Find the number of derived table columns with json, xml, array, or user-defined types. Such columns may contain data that has been aggregated/composed based on values in base tables.

Sofware measure License: MIT (opens in new tab)
# Name (sorted ascending) Goal Type Data source Last update License Actions
1 Columns with JSON, JSONB, or XML type Find columns with JSON, JSONB, or XML type. Each columns should have the most appropriate data type. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View (opens in new tab)
2 Cycle in a hierarchy There should not be cycles in hierarchies meaning that the parent must always be specified. In this case a parent must reference to a child or to itself, otherwise it cannot be registered. Problem detection system catalog base tables only MIT (opens in new tab) View (opens in new tab)
3 Derived tables that present data in json or xml format Find views and materialized views that present data in json or xml format. Instead of recording data in this format in base tables one could generate the data value on the fly based on data that has been recorded in base tables. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View (opens in new tab)
4 Do not always depend on one's parent Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. General system catalog base tables only MIT (opens in new tab) View (opens in new tab)
5 Do not always depend on one's parent - column names are ot sufficiently different Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. Find only cases where the candidate key and foreign key column names are very similar (Levenshtein distance shorter than four). Problem detection system catalog base tables only MIT (opens in new tab) View (opens in new tab)
6 Do not always depend on one's parent (INFORMATION_SCHEMA) Find where a hierarchical structure is implemented in a base table by having a foreign key that refers to a candidate key of the same table. This design is called adjacency list. General INFORMATION_SCHEMA only MIT (opens in new tab) View (opens in new tab)
7 JSON type instead of JSONB type "In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys." (https://www.postgresql.org/docs/current/datatype-json.html) Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View (opens in new tab)
8 ON DELETE SET NULL is probably missing Find implementations of the adjacency list design pattern, where the corresponding foreign key columns are optional but the foreign key constraint does not have ON DELETE SET NULL compensating action. Implement adjacency list correctly. Problem detection system catalog base tables only MIT (opens in new tab) View (opens in new tab)
9 Perhaps a relationship should be irreflexive Enforce all the constraints. A binary relation is called irreflexive, if it does not relate any element to itself. Problem detection system catalog base tables only MIT (opens in new tab) View (opens in new tab)
10 Recursive relationships with the same source and target Find incorrectly implemented adjacency lists. Problem detection system catalog base tables only MIT (opens in new tab) View (opens in new tab)
11 The number of derived table columns with an array, a user-defined, XML, JSON, or JSONB type Find the number of derived table columns with json, xml, array, or user-defined types. Such columns may contain data that has been aggregated/composed based on values in base tables. Sofware measure INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View (opens in new tab)