db/init/00_create_database.sql
```\n
+-------------------------------------------------+
| SQLCHECK |
+-------------------------------------------------+
> RISK LEVEL :: ONLY MEDIUM AND HIGH RISK ANTI-PATTERNS
> SQL FILE NAME :: db/init/00_create_database.sql
> COLOR MODE :: DISABLED
> VERBOSE MODE :: ENABLED
> DELIMITER :: ;
-------------------------------------------------
==================== Results ===================
-------------------------------------------------
SQL Statement at line 2: -- connect to created database \c quizdb create table users ( id serial,
nickname text, telegram_id text, role text );
[db/init/00_create_database.sql]: (HIGH RISK) (LOGICAL_DATABASE_DESIGN ANTI-PATTERN) Multi-Valued Attribute
● Store each value in its own column and row:
Storing a list of IDs as a VARCHAR/TEXT column can cause performance and data
integrity problems. Querying against such a column would require using
pattern-matching expressions. It is awkward and costly to join a comma-separated
list to matching rows. This will make it harder to validate IDs. Think about
what is the greatest number of entries this list must support? Instead of using
a multi-valued attribute, consider storing it in a separate table, so that each
individual value of that attribute occupies a separate row. Such an intersection
table implements a many-to-many relationship between the two referenced tables.
This will greatly simplify querying and validating the IDs.
[Matching Expression: id text at line 9]
[db/init/00_create_database.sql]: (HIGH RISK) (LOGICAL_DATABASE_DESIGN ANTI-PATTERN) Generic Primary Key
● Skip using a generic primary key (id):
Adding an id column to every table causes several effects that make its use seem
arbitrary. You might end up creating a redundant key or allow duplicate rows if
you add this column in a compound key. The name id is so generic that it holds
no meaning. This is especially important when you join two tables and they have
the same primary key column name.
[Matching Expression: id at line 6]
-------------------------------------------------
SQL Statement at line 12: create table quizzes ( id serial, topic text, creator_id integer, created_at
timestamp );
[db/init/00_create_database.sql]: (HIGH RISK) (LOGICAL_DATABASE_DESIGN ANTI-PATTERN) Generic Primary Key
● Skip using a generic primary key (id):
Adding an id column to every table causes several effects that make its use seem
arbitrary. You might end up creating a redundant key or allow duplicate rows if
you add this column in a compound key. The name id is so generic that it holds
no meaning. This is especially important when you join two tables and they have
the same primary key column name.
[Matching Expression: id at line 13]
-------------------------------------------------
SQL Statement at line 19: create table questions ( id serial, quiz_id integer, "text" text );
[db/init/00_create_database.sql]: (HIGH RISK) (LOGICAL_DATABASE_DESIGN ANTI-PATTERN) Generic Primary Key
● Skip using a generic primary key (id):
Adding an id column to every table causes several effects that make its use seem
arbitrary. You might end up creating a redundant key or allow duplicate rows if
you add this column in a compound key. The name id is so generic that it holds
no meaning. This is especially important when you join two tables and they have
the same primary key column name.
[Matching Expression: id at line 20]
-------------------------------------------------
SQL Statement at line 25: create table answers ( id serial, question_id integer, "text" text, is_correct
boolean );
[db/init/00_create_database.sql]: (HIGH RISK) (LOGICAL_DATABASE_DESIGN ANTI-PATTERN) Generic Primary Key
● Skip using a generic primary key (id):
Adding an id column to every table causes several effects that make its use seem
arbitrary. You might end up creating a redundant key or allow duplicate rows if
you add this column in a compound key. The name id is so generic that it holds
no meaning. This is especially important when you join two tables and they have
the same primary key column name.
[Matching Expression: id at line 26]
-------------------------------------------------
SQL Statement at line 32: create table statistics ( user_id integer, quizzes_completed integer,
mean_quiz_complete_time real, mean_question_reply_time real, correct_replies
integer, correct_replies_percent integer );
[db/init/00_create_database.sql]: (MEDIUM RISK) (PHYSICAL_DATABASE_DESIGN ANTI-PATTERN) Imprecise Data Type
● Use precise data types:
Virtually any use of FLOAT, REAL, or DOUBLE PRECISION data types is suspect.
Most applications that use floating-point numbers don't require the range of
values supported by IEEE 754 formats. The cumulative impact of inexact
floating-point numbers is severe when calculating aggregates. Instead of FLOAT
or its siblings, use the NUMERIC or DECIMAL SQL data types for fixed-precision
fractional numbers. These data types store numeric values exactly, up to the
precision you specify in the column definition. Do not use FLOAT if you can
avoid it.
[Matching Expression: real at lines 36, 37]
==================== Summary ===================
All Anti-Patterns and Hints :: 6
> High Risk :: 5
> Medium Risk :: 1
> Low Risk :: 0
> Hints :: 0
```
db/init/10_create_constrants.sql
```\n
+-------------------------------------------------+
| SQLCHECK |
+-------------------------------------------------+
> RISK LEVEL :: ONLY MEDIUM AND HIGH RISK ANTI-PATTERNS
> SQL FILE NAME :: db/init/10_create_constrants.sql
> COLOR MODE :: DISABLED
> VERBOSE MODE :: ENABLED
> DELIMITER :: ;
-------------------------------------------------
==================== Results ===================
-------------------------------------------------
SQL Statement at line 1: \c quizdb alter table users add constraint c_users_pk primary key (id), add
constraint c_users_role check (("role") in ('user', 'admin'));
[db/init/10_create_constrants.sql]: (MEDIUM RISK) (PHYSICAL_DATABASE_DESIGN ANTI-PATTERN) Values In Definition
● Don't specify values in column definition:
With enum, you declare the values as strings, but internally the column is
stored as the ordinal number of the string in the enumerated list. The storage
is therefore compact, but when you sort a query by this column, the result is
ordered by the ordinal value, not alphabetically by the string value. You may
not expect this behavior. There's no syntax to add or remove a value from an
ENUM or check constraint; you can only redefine the column with a new set of
values. Moreover, if you make a value obsolete, you could upset historical data.
As a matter of policy, changing metadata — that is, changing the definition of
tables and columns—should be infrequent and with attention to testing and
quality assurance. There's a better solution to restrict values in a column:
create a lookup table with one row for each value you allow. Then declare a
foreign key constraint on the old table referencing the new table. Use metadata
when validating against a fixed set of values. Use data when validating against
a fluid set of values.
[Matching Expression: in ( at line 7]
==================== Summary ===================
All Anti-Patterns and Hints :: 1
> High Risk :: 0
> Medium Risk :: 1
> Low Risk :: 0
> Hints :: 0
```
RullDeef
commented
1 year ago
Добавлены структуры для работы с gorm, а также переписана небольшая часть кода репозиториев.