SG Buddy. Инструмент для формирования запросов для SQLC и proto-файлов через Web User Interface

CREATE TABLE dc.table_meta (  
    id bigint NOT NULL,  
    name character varying(128) NOT NULL,  
    description character varying(2000) NOT NULL,  
    schema_id bigint NOT NULL,  
    table_type_id bigint NOT NULL,  
    domain_id bigint NOT NULL,  
    is_deleted boolean DEFAULT false NOT NULL,  
    created_at timestamp without time zone DEFAULT now() NOT NULL,  
    updated_at timestamp without time zone DEFAULT now() NOT NULL,  
    is_get_dict bool DEFAULT false NOT NULL,  
    user_id bigint NOT NULL  
);  
CREATE TABLE dc.table_type (  
    id bigint NOT NULL,  
    name character varying(128) NOT NULL,  
    description character varying(1000) NOT NULL,  
    is_deleted boolean DEFAULT false NOT NULL,  
    created_at timestamp without time zone DEFAULT now() NOT NULL,  
    updated_at timestamp without time zone DEFAULT now() NOT NULL,  
    user_id bigint NOT NULL  
);

-- name: CreateTableMeta :one  
INSERT INTO dc.table_meta (  
    name,  
    description,  
    schema_id,  
    table_type_id,  
    domain_id,  
    is_deleted,  
    created_at,  
    updated_at,  
    is_get_dict,  
    user_id  
) VALUES (  
    @name,  
    @description,  
    @schema_id,  
    @table_type_id,  
    @domain_id,  
    false,  
    now(),  
    now(),  
    false,  
    @user_id  
)  
RETURNING *;

service TableMetaService {  
  rpc CreateTableMeta(CreateTableMetaRequest) returns (CreateTableMetaResponse);  
}  

// Строка таблицы dc.table_meta: все колонки, как их вернёт SELECT *.  
message TableMeta {  
  int64 id = 1;  
  string name = 2;  
  string description = 3;  
  int64 schema_id = 4;  
  int64 table_type_id = 5;  
  int64 domain_id = 6;  
  bool is_deleted = 7;  
  google.protobuf.Timestamp created_at = 8;  
  google.protobuf.Timestamp updated_at = 9;  
  bool is_get_dict = 10;  
  int64 user_id = 11;  
}  

// Вставка CreateTableMeta: параметры вызова.  
message CreateTableMetaRequest {  
  string name = 1;  
  string description = 2;  
  int64 schema_id = 3;  
  int64 table_type_id = 4;  
  int64 domain_id = 5;  
  int64 user_id = 6;  
}  

// Вставка CreateTableMeta: одна строка.  
message CreateTableMetaResponse {  
  TableMeta table_meta = 1;  
}

-- name: GetTableMetaById :one  
SELECT  
    table_meta.id,  
    table_meta.name,  
    table_meta.description,  
    table_meta.schema_id,  
    table_meta.table_type_id,  
    table_meta.domain_id,  
    table_meta.is_deleted,  
    table_meta.created_at,  
    table_meta.updated_at,  
    table_meta.is_get_dict,  
    table_meta.user_id  
FROM dc.table_meta  
WHERE table_meta.id = @id  
LIMIT 1;

rpc GetTableMetaById(GetTableMetaByIdRequest) returns (GetTableMetaByIdResponse);

// Выборка GetTableMetaById: параметры вызова.  
message GetTableMetaByIdRequest {  
  int64 id = 1;  
}

// Выборка GetTableMetaById: одна строка.  
message GetTableMetaByIdResponse {  
  TableMeta table_meta = 1;  
}

// Строка таблицы dc.table_meta: все колонки, как их вернёт SELECT *.  
message TableMeta {  
  int64 id = 1;  
  string name = 2;  
  string description = 3;  
  int64 schema_id = 4;  
  int64 table_type_id = 5;  
  int64 domain_id = 6;  
  bool is_deleted = 7;  
  google.protobuf.Timestamp created_at = 8;  
  google.protobuf.Timestamp updated_at = 9;  
  bool is_get_dict = 10;  
  int64 user_id = 11;  
}

-- name: GetTableMetasSearchName :many  
SELECT  
    table_meta.id,  
    table_meta.name,  
    table_meta.description,  
    table_meta.schema_id,  
    table_meta.table_type_id,  
    table_meta.domain_id,  
    table_meta.is_deleted,  
    table_meta.created_at,  
    table_meta.updated_at,  
    table_meta.is_get_dict,  
    table_meta.user_id  
FROM dc.table_meta  
WHERE table_meta.is_deleted = false  
  AND (lower(name) LIKE '%' || lower(sqlc.arg(search_name)) || '%')  
ORDER BY CASE WHEN sqlc.arg('order')::text <> 'DESC' THEN table_meta.id END ASC,  
    CASE WHEN sqlc.arg('order')::text = 'DESC' THEN table_meta.id END DESC;

rpc GetTableMetasSearchName(GetTableMetasSearchNameRequest) returns (GetTableMetasSearchNameResponse);

// Выборка GetTableMetasSearchName: параметры вызова. Порядок по умолчанию — id.  
message GetTableMetasSearchNameRequest {  
  string search_name = 1;  
  // допустимые значения: ASC, DESC  
  string order = 2;  
}

// Выборка GetTableMetasSearchName: все найденные строки.  
message GetTableMetasSearchNameResponse {  
  repeated TableMeta rows = 1;  
}

-- name: GetTableMetasWithPagination :many  
SELECT  
    table_meta.id,  
    table_meta.name,  
    table_meta.description,  
    table_meta.schema_id,  
    table_meta.table_type_id,  
    table_meta.domain_id,  
    table_meta.is_deleted,  
    table_meta.created_at,  
    table_meta.updated_at,  
    table_meta.is_get_dict,  
    table_meta.user_id  
FROM dc.table_meta  
WHERE table_meta.is_deleted = false  
ORDER BY CASE WHEN sqlc.arg('order')::text <> 'DESC' THEN table_meta.id END ASC,  
    CASE WHEN sqlc.arg('order')::text = 'DESC' THEN table_meta.id END DESC  
LIMIT @page_limit::int OFFSET (sqlc.arg('page')::int-1)*sqlc.arg('page_limit')::int;  

-- name: CountGetTableMetasWithPagination :one  
SELECT  
    count(*)                                                        AS total_items,  
    ceil(count(*)::numeric / GREATEST(@page_limit::int, 1))::bigint AS total_pages  
FROM dc.table_meta  
WHERE table_meta.is_deleted = false;

rpc GetTableMetasWithPagination(GetTableMetasWithPaginationRequest) returns (GetTableMetasWithPaginationResponse);

// Выборка GetTableMetasWithPagination: параметры вызова. Порядок по умолчанию — id.  
message GetTableMetasWithPaginationRequest {  
  // допустимые значения: ASC, DESC  
  string order = 1;  
  int32 page_limit = 2;  
  int32 page = 3;  
}

// Выборка GetTableMetasWithPagination: страница данных и пагинация.  
message GetTableMetasWithPaginationResponse {  
  repeated TableMeta data = 1;  
  Pagination pagination = 2;  
}

-- name: UpdateTableMetaById :exec  
UPDATE dc.table_meta  
SET name = @name,  
    description = @description,  
    schema_id = @schema_id,  
    table_type_id = @table_type_id,  
    domain_id = @domain_id,  
    updated_at = now(),  
    is_get_dict = @is_get_dict  
WHERE table_meta.id = @id;

rpc UpdateTableMetaById(UpdateTableMetaByIdRequest) returns (UpdateTableMetaByIdResponse);

// Изменение UpdateTableMetaById: параметры вызова.  
message UpdateTableMetaByIdRequest {  
  string name = 1;  
  string description = 2;  
  int64 schema_id = 3;  
  int64 table_type_id = 4;  
  int64 domain_id = 5;  
  bool is_get_dict = 6;  
  int64 id = 7;  
}  

// Изменение UpdateTableMetaById: ответ пустой — запрос ничего не возвращает.  
message UpdateTableMetaByIdResponse {}

-- name: DeleteTableMetaById :exec  
DELETE FROM dc.table_meta  
WHERE table_meta.id = @id;

rpc DeleteTableMetaById(DeleteTableMetaByIdRequest) returns (DeleteTableMetaByIdResponse);

// Удаление DeleteTableMetaById: параметры вызова.  
message DeleteTableMetaByIdRequest {  
  int64 id = 1;  
}

// Удаление DeleteTableMetaById: ответ пустой — запрос ничего не возвращает.  
message DeleteTableMetaByIdResponse {}

-- name: SoftDeleteTableMeta :exec  
UPDATE dc.table_meta  
SET is_deleted = true  
WHERE table_meta.id = @id;

// Мягкое удаление SoftDeleteTableMeta: параметры вызова.  
message SoftDeleteTableMetaRequest {  
  int64 id = 1;  
}  

// Мягкое удаление SoftDeleteTableMeta: ответ пустой — запрос ничего не возвращает.  
message SoftDeleteTableMetaResponse {}

-- name: UndeleteTableMeta :exec  
UPDATE dc.table_meta  
SET is_deleted = false  
WHERE table_meta.id = @id;

rpc UndeleteTableMeta(UndeleteTableMetaRequest) returns (UndeleteTableMetaResponse);

// Обратное удаление UndeleteTableMeta: параметры вызова.  
message UndeleteTableMetaRequest {  
  int64 id = 1;  
}  

// Обратное удаление UndeleteTableMeta: ответ пустой — запрос ничего не возвращает.  
message UndeleteTableMetaResponse {}

-- name: GetTableMetaByIdWithTypeName :one  
SELECT  
    table_meta.id,  
    table_meta.name,  
    table_meta.description,  
    table_meta.schema_id,  
    table_meta.table_type_id,  
    table_meta.domain_id,  
    table_meta.is_deleted,  
    table_meta.created_at,  
    table_meta.updated_at,  
    table_meta.is_get_dict,  
    table_meta.user_id,  
    table_type.name AS table_type_name  
FROM dc.table_meta  
INNER JOIN dc.table_type table_type ON table_meta.table_type_id = table_type.id  
WHERE table_meta.id = @id  
LIMIT 1;

rpc GetTableMetaByIdWithTypeName(GetTableMetaByIdWithTypeNameRequest) returns (GetTableMetaByIdWithTypeNameResponse);

// Выборка GetTableMetaByIdWithTypeName: параметры вызова.  
message GetTableMetaByIdWithTypeNameRequest {  
  int64 id = 1;  
}  

// Выборка GetTableMetaByIdWithTypeName: строка ответа — только отмеченные колонки.  
message GetTableMetaByIdWithTypeNameRow {  
  int64 id = 1;  
  string name = 2;  
  string description = 3;  
  int64 schema_id = 4;  
  int64 table_type_id = 5;  
  int64 domain_id = 6;  
  bool is_deleted = 7;  
  google.protobuf.Timestamp created_at = 8;  
  google.protobuf.Timestamp updated_at = 9;  
  bool is_get_dict = 10;  
  int64 user_id = 11;  
  string table_type_name = 12;  
}