select o.id, o.[common_fields],
av1.field_value as custom_field1,
av2.field_value as custom_field2,
...
from main_object o
left join add_values av1 on av1.value_name = 'custom_field1' and av1.obj_id = o.id
left join add_values av2 on av2.value_name = 'custom_field2' and av2.obj_id = o.id
select id, task_id, flex_value, jsonb_array_length(flex_value)
from jgproj.task_flex_value
where flex_value @? format('$[*] ? (@."fieldId" == %s && @."value"[*] == %s)', 4519, 8760)::jsonpath
and flex_value @? format('$[*] ? (@."fieldId" == %s && @."value"[*] >= %s && @."value"[*] <= %s)', 4498, 0, 2)::jsonpath
(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])
// Интерфейс валидатора
@Repository
interface ValidateRuleRepository: JpaRepository<ValidateRule, Long> {
@Query(value = """
select column_name,
is_notnull,
is_required,
data_type,
character_maximum_length,
numeric_precision,
numeric_precision_radix,
udt_name,
table_description,
column_description,
rule_name,
rule_desc,
rule_type,
rule_regexp
from jgutil.f_get_validations(p_schema => :schema, p_object_name => :objectName)
""", nativeQuery = true
)
@Cacheable("validate_rules")
fun getValidateRules(schema: String, objectName: String): List<ValidateRule>
}
.....
// Все репозитории в коде реализуют интерфейс валидатора:
@Repository
interface ProjectRepository : JpaRepository<Project, Long>, ValidatableRepository, CodeSearchable {
.....
// соответствие типов JVM и БД:
companion object {
// Соответсвтие типов БД и JVM
val typesConformity: HashMap<String, List<String>> = HashMap()
}
init {
typesConformity["varchar"] = listOf("String")
typesConformity["int8"] = listOf("BigInteger", "Long", "long")
typesConformity["int4"] = listOf("Integer", "int")
typesConformity["int2"] = listOf("Short", "short")
typesConformity["timestamp"] = listOf("OffsetDateTime")
typesConformity["text"] = listOf("String")
typesConformity["bool"] = listOf("boolean", "bool", "Boolean")
typesConformity["date"] = listOf("OffsetDateTime")
typesConformity["timestamp"] = listOf("OffsetDateTime")
typesConformity["_text"] = listOf("[String]")
typesConformity["_int2"] = listOf("[Short]", "[short]")
typesConformity["_int4"] = listOf("[Integer]", "[int]")
typesConformity["_int8"] = listOf("[Long]", "[long]", "[BigInteger]")
// Продолжить
}
.....
// Валидация каждого объекта
fun validateObj(obj: Any, schema: String, objectName: String, extended: Boolean): List<ValidateResult> {
val res = ArrayList<ValidateResult>()
val rules = validateRuleRepository.getValidateRules(schema, objectName)
val сlazz = Class.forName(obj.javaClass.name)
for (field in сlazz.declaredFields) {
var fieldFound = false
for (rule in rules) {
var dbFieldName = field.getAnnotation(Column::class.java)?.name
if (dbFieldName.isNullOrEmpty()) dbFieldName = field.name.camelCaseToUnderscore()
if (rule.columnName == dbFieldName) {
val dbType = rule.udtName
val jvmType = parseArray(field.type.name) ?: field.type.name.substringAfterLast(".", field.type.name)
val confType = typesConformity[dbType]
if (confType == null) {
res.add(
ValidateResult(
field.name, dbFieldName, rule,
ValidateResult.Reason.TypeNotFound, rule.columnDescription,
"dbType: $dbType"
)
)
fieldFound = true
break
} else {
if (!confType.contains(jvmType)) {
res.add(
ValidateResult(
field.name, dbFieldName, rule,
ValidateResult.Reason.TypeMismatch, rule.columnDescription,
"dbType: $dbType != $confType"
)
)
fieldFound = true
break
} else {
println("testing rule ${rule.columnName}")
validateField(obj, field, dbFieldName, rule, res)
fieldFound = true
break
}
}
}
}
if ((!fieldFound) && (extended)) {
res.add(
ValidateResult(
field.name, null, null,
ValidateResult.Reason.RuleNotDefined, null
)
)
}
}
return res
}
.....
//Валидация поля (атрибута объекта)
fun validateField(obj: Any, field: Field, dbFieldName: String, rule: ValidateRule, res: MutableList<ValidateResult>) {
field.isAccessible = true
var ruleImplemented = false
val value = field.get(obj)
// Общие проверки
rule.isNotnull?.let {
if ((it) && (value == null)) {
res.add(
ValidateResult(
field.name, dbFieldName, rule,
ValidateResult.Reason.NotNull, rule.columnDescription, "dbType: ${rule.udtName}"
)
)
}
}
// Проверки по типам
if (rule.udtName == "varchar") {
ruleImplemented = true
val valueAsString = value as String?
rule.characterMaximumLength?.let {
if (valueAsString.NN().length > it)
res.add(
ValidateResult(
field.name, dbFieldName, rule,
ValidateResult.Reason.MaxLength, rule.columnDescription, "dbType: ${rule.udtName}"
)
)
}
rule.isRequired?.let {
if ((it) && (valueAsString.isNullOrBlank()))
res.add(
ValidateResult(
field.name, dbFieldName, rule,
ValidateResult.Reason.Required, rule.columnDescription, "dbType: ${rule.udtName}"
)
)
}
val rrexp = rule.ruleRegexp
if (!rrexp.isNullOrBlank()) {
val rx = rrexp.toRegex()
if (valueAsString!=null && !valueAsString.matches(rx))
res.add(
ValidateResult(
field.name, dbFieldName, rule,
ValidateResult.Reason.Regexp, rule.columnDescription, "dbType: ${rule.udtName}"
)
)
}
}
if (!ruleImplemented) {
//TODO not implemented yet
}
}
В логе приложения:
org.postgresql.util.PSQLException: ERROR: ~~DB-LG004. БД - Класс LG - Нарушение логической целостности данных. Запись с таким набором идентификаторов не существует или уже закрыта. Hint: Проверьте правильность набора идентификаторов модифицируемой записи . system_state: P0001 . errm: DB-LG004. -->p_id 6919. detail: .context: PL/pgSQL function jgproj_api.f_del_task_type_ref(bigint) line 16 at RAISE .Constraint:
Где: PL/pgSQL function jgutil.f_return_error_description(text,text,text,text,text) line 80 at RAISE
SQL statement "SELECT jgutil.f_return_error_description(l_con, l_state, l_errm, l_detail, l_context)"
PL/pgSQL function jgproj_api.f_del_task_type_ref(bigint) line 27 at PERFORM
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2674)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2364)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:354)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:484)
.......
При этом в ответе Spring-контроллера ошибка показывалась примерно так:
{
"timestamp": "2023-04-07T15:10:41.478+00:00",
"status": 500,
"error": "Internal Server Error",
"exception": "org.springframework.orm.jpa.JpaSystemException",
"message": "could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet",
"path": "/project/99160/taskType/6919",
"datetime_iso": "2023-04-07T18:10:41.47997+03:00",
"version": "0.0.1"
}
В логе приложения (обратите внимание на класс исключения):
com.rit.crossdev.jaga.exceptions.DBException: Запись с таким набором идентификаторов не существует или уже закрыта
at com.rit.crossdev.jaga.service.ErrorHandler.withDBErrorHandling(ErrorHandler.kt:363)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
В ответе контроллера:
{
"timestamp": "2023-04-07T15:37:35.727+00:00",
"status": 500,
"error": "Internal Server Error",
"exception": {
"code": "BK-00001",
"level": "SEVERE",
"cause": {
"code": "DB-LG004",
"level": "WARNING",
"hint": "Проверьте правильность набора идентификаторов модифицируемой записи",
"additionalInfo": "",
"text": "Запись с таким набором идентификаторов не существует или уже закрыта",
"group": "БД - Класс LG - Нарушение логической целостности данных"
},
"text": "Необработанная ошибка BACKEND",
"group": "BACKEND"
},
"message": "Необработанная ошибка BACKEND",
"path": "/project/99160/taskType/6919",
"datetime_iso": "2023-04-07T18:37:35.7275917+03:00",
"version": "0.0.1"
}
//Собственно сама обертка аннотация
@Target(AnnotationTarget.FUNCTION)
@Retention(AnnotationRetention.RUNTIME)
annotation class DBErrorProcessable {
}
......
//И ее реализация
@Around("@annotation(com.rit.crossdev.jaga.service.DBErrorProcessable)")
fun withDBErrorHandling(jointPoint: ProceedingJoinPoint): Any? {
logger.info("withDBErrorHandling ${jointPoint.signature.name}")
return try {
jointPoint.proceed()
} catch (ex: JpaSystemException) {
val code = ex.findPsqlErrorCode()
val error = findAndPrintErrorStr(code, ex)
throw DBException(
code = error.code,
level = error.level,
group = error.group,
text = error.text,
additionalInfo = error.additionalInfo,
hint = error.hint
)
} catch (ex: Exception) {
val error = findAndPrintError(DEFAULT_DB_ERROR_CODE, ex)
throw DBException(
code = error.code,
level = error.level,
group = error.group,
text = error.text,
additionalInfo = error.additionalInfo,
hint = error.hint
)
}
}
......
//Получаем error code и пытаемся найти ошибку в кэше ошибок по коду если не получилось то возвращаем необработанную ошибку БД по умолчанию
//Для бэкенда аналогичный обработчик
@Around("@annotation(com.rit.crossdev.jaga.service.ErrorProcessable)")
fun withErrorHandling(jointPoint: ProceedingJoinPoint): Any? {
logger.info("withErrorHandling ${jointPoint.signature.name}")
return try {
jointPoint.proceed()
} catch (ex: AttributeNameAlreadyExistException) {
val error = findAndPrintError(ATTRIBUTE_WHITH_SAME_NAME_ALREADY_EXIST_ERROR_CODE, ex)
throw BadRequestException(
code = error.code,
level = error.level,
group = error.group,
text = error.text,
additionalInfo = error.additionalInfo,
hint = error.hint,
cause = ex
)
}
catch (ex: DBException) {
val error = findAndPrintError(DEFAULT_BK_ERROR_CODE, ex)
throw BEException(
code = error.code,
level = error.level,
group = error.group,
text = error.text,
additionalInfo = error.additionalInfo,
hint = error.hint,
cause = ex
)
}
......
//Таким образом вызов оборачивается
@ErrorProcessable
fun foo(){
bar()
}
@DBErrorProcessable
Fun bar(){
}
SELECT
tc.table_schema,
ccu.constraint_schema,
tc.constraint_name,
obj_description(format('%s.%s', isc.table_schema, isc.table_name)::regclass::oid, 'pg_class') AS table_description,
tc.table_name,
kcu.column_name,
pg_catalog.col_description(format('%s.%s', isc.table_schema, isc.table_name)::regclass::oid, isc.ordinal_position) AS column_description,
ccu.table_schema AS foreign_table_schema,
ccu.table_name AS foreign_table_name,
obj_description(format('%s.%s', ccu.table_schema, ccu.table_name)::regclass::oid, 'pg_class') AS foreign_table_description,
ccu.column_name AS foreign_column_name,
pg_catalog.col_description(format('%s.%s', ccu.table_schema, ccu.table_name)::regclass::oid, isc.ordinal_position) AS foreign_column_description
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
JOIN information_schema.columns isc ON isc.table_schema = tc.table_schema
AND isc.table_name = tc.table_name
AND isc.column_name = kcu.column_name
WHERE
tc.constraint_type = 'FOREIGN KEY'
AND tc.table_name = 'task_assignee'
AND tc.constraint_name = 'assignee_fk'
Без Экзекутора:
@Query(
"""SELECT * FROM jgproj_api.v_task_assignee WHERE task_id = :task_id ORDER BY Id DESC LIMIT 1""",
nativeQuery = true
)
@DBErrorProcessable
fun findLastRecordByTaskId(
@Param("task_id") taskId: Long
): TaskAssignee
С экзекутором:
val user = executor.executeDbApi(
"jguser_api.f_upd_intern_user_profile_by_user",
userProfileByUser.toDbRequest(isOfficeLocationEdited),
UserProfile::class.java
).value?.convertToDto() ?: throw RuntimeException("can't update userProfile")
CREATE OR REPLACE FUNCTION jgcore_api.f_execute_table_func(p_sql text, p_is_json boolean)
RETURNS json
LANGUAGE plpgsql
AS $function$
declare
l_rec record; -- Очередная запись, полученная из прикладной функции
l_result jsonb; -- Результат выполнения прикладной функции
begin
l_result := '[]'::jsonb;
if not p_is_json then
for l_rec in execute 'select row_to_json(res)::jsonb as val from ' || p_sql || ' res' loop
l_result := l_result || l_rec.val;
end loop;
else
for l_rec in execute 'select res from ' || p_sql || ' res' loop
l_result := l_result || l_rec.res::jsonb;
end loop;
end if;
return l_result::json;
end;
$function$
;
COMMENT ON FUNCTION jgcore_api.f_execute_table_func(text, bool) IS 'Выполнение прикладной функции, возвращающей setof и оборачивание результата в JSON';
Мигратор разрабатывался БДшниками и для БДшников, но его задача - обеспечить миграции БД в рамках "общей парадигмы" процессов CI/CD. Что было самым сложным? На самом деле не было, а есть и остается. Когда мы начинаем говорить про мигратор с девопсами или теми, кто его не знает (новые члены команды), обычно разговор начинается так "а почему не взяли Liquibase? Может быть не умеете, давайте мы вам покажем и научим?". Ну и дальше идет длинная беседа, а что это вообще и зачем. Вот про это и поговорим.
Как нам нужно вести объекты в git? В виде жестко заданной структуры папок в виде "схема/тип_объекта/наименование_объекта". В целом, разумное же требование? Примерно как структура проекта на любом языке программирования. Мы хотим видеть историю изменений каждого объекта;
Как мы хотим "проливать" изменения на базу данных? По возможности автоматически, раз уж мы внесли изменения в объекты, то инструмент должен уметь эти изменения "собрать". Это кстати основной блок работ и самая существенная разница с разработкой "просто кода". Если для процедур и функций все более менее просто - нужно взять свежую версию и скомпилировать в БД, то для таблиц уже нужно генерировать alter table add column. Для более сложных манипуляций - нужно учитывать ситуации с зависимостями объектов, тем более в PostgreSQL с этим не очень просто (попробуйте, например, заменить представление, которое используется в куче мест, вставив ему в середину новое поле).
Мы не хотим писать вручную файл-патч (чейнджсет) с изменениями объектов, мы уже написали эти изменения в сами объекты, смотри пункт 2.
И мы точно не хотим описывать изменения структур в виде xml, хотя знаем, что у Liquibase есть такой режим. Я вообще не видел ни один проект, на котором его используют.