Query goal: | Find the names on base table columns that are not a part of a candidate key and a foreign key and contain a generic word (nimi, nimetus, kommentaar, kirjeldus, name, comment, description, etc.). Make sure that naming of these is consistent, i.e., such names always contain the table name or never contain the table name. |
Notes about the query: | In the column name, a part that is the same as the table name, is between (b) tags for the better readability in case the query result is displayed in a web browser. The query considers both column names in English and Estonian. A column belongs to a candidate key if it is covered by a PRIMARY KEY or UNIQUE constraint. |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Fixing suggestion: | If the naming is inconsistent, then make it consistent. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH key_columns AS (select target_schema as table_schema, target_table as table_name, a.attname as column_name from (select (select nspname from pg_namespace where oid=c.relnamespace) as target_schema, c.relname as target_table, c.oid as target_table_oid, unnest(o.conkey) AS target_col from pg_constraint o inner join pg_class c on c.oid = o.conrelid where o.contype in ('u','p')) t inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false), fk_columns AS ( select target_schema as table_schema, target_table as table_name, a.attname as column_name from (select (select nspname from pg_namespace where oid=c.relnamespace) as target_schema, c.relname as target_table, c.oid as target_table_oid, unnest(o.conkey) AS target_col from pg_constraint o inner join pg_class c on c.oid = o.conrelid where o.contype = 'f') t inner join pg_attribute a on t.target_col = a.attnum and t.target_table_oid = a.attrelid and a.attisdropped = false ) SELECT table_schema, table_name, regexp_replace(column_name, table_name, '<b>' || table_name || '</b>','g') AS column_name, substring(column_name from '(nimi|nimetus|kirjeldus|kommentaar|selgitus|lugu|pealkiri|klass|name|comment|description|explanation|story|title|class)$') as generic_column_name FROM INFORMATION_SCHEMA.columns AS c WHERE column_name~*'(nimi|nimetus|kirjeldus|kommentaar|selgitus|lugu|pealkiri|klass|name|comment|description|explanation|story|title|class)$' AND (table_schema, table_name) IN (SELECT table_schema, table_name FROM INFORMATION_SCHEMA.tables WHERE table_type='BASE TABLE') AND table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM key_columns AS pk WHERE pk.table_schema=c.table_schema AND pk.table_name=c.table_name AND pk.column_name=c.column_name) AND NOT EXISTS (SELECT 1 FROM fk_columns AS fk WHERE fk.table_schema=c.table_schema AND fk.table_name=c.table_name AND fk.column_name=c.column_name) ORDER BY generic_column_name, column_name, table_schema, table_name; |
Collection name | Collection description |
---|---|
Find problems about names | A selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview. |
Find problems by overview | Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not . |
Lexicon bad smells and linguistic antipatterns | Queries made to find the occurrences of lexicon bad smells and linguistic antipatterns |
Category name | Category description |
---|---|
Inconsistencies | Queries of this catergory provide information about inconsistencies of solving the same problem in different places. |
Naming | Queries of this category provide information about the style of naming. |
Reference |
---|
The corresponding code smell in case of cleaning code is "N4: Unambiguous Names". (Robert C. Martin, Clean Code) |
Smell "Whole-part": Abebe, S.L., Haiduc, S., Tonella, P. and Marcus, A., 2011, September. The effect of lexicon bad smells on concept location in source code. In 2011 IEEE 11th International Working Conference on Source Code Analysis and Manipulation (pp. 125-134). IEEE. |