PostgreSQL/JPA-在import.sql文件中导入功能? [英] PostgreSQL/JPA - Import functions in import.sql file?

问题描述

我正在尝试在我的JPA import.sql文件中定义一些PostgreSQL函数和触发器.我正在使用Hibernate 5.x作为基础JPA提供程序.由于我的import.sql文件具有多行命令，因此我已经在persistence.xml文件中设置了此属性:

I'm trying to define some PostgreSQL functions and triggers in my JPA import.sql file. I'm using Hibernate 5.x as my underlying JPA provider. Since my import.sql file has commands that are multiple lines, I've got this property set in my persistence.xml file:

<property name="hibernate.hbm2ddl.import_files_sql_extractor" value="org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor" />

从那里，我试图在import.sql中定义我的函数.他们看起来像这样:

From there, I'm trying to define my functions in import.sql. They look something like this:

DROP FUNCTION IF EXISTS update_total_feedback_count() CASCADE;
CREATE FUNCTION update_total_feedback_count() RETURNS TRIGGER AS
$$
DECLARE
  application_version_id BIGINT := 0;
  app_version_metadata_id BIGINT:= 0;
  application_id BIGINT := 0;
  app_metadata_id BIGINT := 0;
BEGIN
    IF (TG_OP = 'INSERT') THEN
        SELECT INTO application_version_id tbl_application_version.id
            FROM tbl_application_version
            INNER JOIN tbl_feedback ON tbl_feedback.application_version_id = tbl_application_version.id
            WHERE tbl_feedback.id = NEW.id;

        SELECT INTO app_version_metadata_id tbl_application_version.application_version_metadata_id
            FROM tbl_application_version
            WHERE id = application_version_id;

        SELECT INTO app_metadata_id registered_application_metadata_id
            FROM tbl_registered_application
            INNER JOIN tbl_application_version ON tbl_application_version.registered_application_id = tbl_registered_application.id
            WHERE tbl_application_version.id = application_version_id;

        UPDATE tbl_registered_application_metadata SET feedbackcount = (feedbackcount + 1), lastfeedbackdate = NEW.createddate WHERE id = app_metadata_id;
        UPDATE tbl_application_version_metadata SET feedbackcount = (feedbackcount + 1), lastfeedbackdate = NEW.createddate WHERE id = app_version_metadata_id;

        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        -- IMPLEMENT THIS TRIGGER

        RETURN NULL;
    END IF;
END;
$$
LANGUAGE 'plpgsql';
ALTER FUNCTION update_total_feedback_count() OWNER TO feedback_tracker;

但是，当我部署WAR文件时，出现类似以下内容的错误消息:

However, when I deploy my WAR file, I get an error saying something like this:

无终止的美元报价从SQL CREATE中的位置65开始 功能my_function()以$ $$的形式返回触发器

Unterminated dollar quote started at position 65 in SQL CREATE FUNCTION my_function() RETURNS TRIGGER AS $$

因此，很明显，它依赖于我的函数声明中的$$.有没有解决的办法?我应该以不同的方式声明我的功能/触发吗?我可以在persistence.xml文件中设置一个可以解决此问题的属性吗?

So, clearly it's dying on the $$ in my function declaration. Is there a way around this? Should I be declaring my function/trigger differently? Is there a property I can set in my persistence.xml file that will get around this?

推荐答案

hibernate的默认SqlStatementParser实现的问题，该实现用于多行sql命令提取器中.

the problem with hibernate's default SqlStatementParser implementation, which is used in multiline sql command extractor.

如果查看语法定义hibernate-core/src/main/antlr/sql-stmt.g，则有语句结尾的定义:

if you look at grammar definition hibernate-core/src/main/antlr/sql-stmt.g there is definition of Statement End:

STMT_END
    : ';' ( '\t' | ' ' | '\r' | '\n' )*
    ;

NOT_STMT_END
    : ~( ';' )
    ;

这表明语句结尾是分号符号，后跟空格"，制表符"，回车符"或换行符".

This tells that statement end is semicolon symbol followed by "Space" "tab" "carret return" or "new line" symbols.

因此:Hibernate中的默认实施不支持美元报价.

THUS: DEFAULT IMPLEMENTATION IN HIBERNATE DOESN'T SUPPORT DOLLAR QUOTING.

如果您不想实现自定义的休眠解析器，则可以使用简单的'引用来重写所有函数而无需使用美元引号.但是您将需要小心地转义'字符.

If you don't want to implement custom hibernate's parser you can rewrite all functions without dollar quoting, using simple ' quoting. But you will need to carefully escape ' chars.

更新:您可以创建自定义的ImportSqlCommandExtractor.例如:用--******分隔命令(注释中用6个星号表示，以使文件成为正确的SQL文件，但注释中使用自定义命令分隔，或选择所需的任何疯狂组合)，然后将其拆分为简单的实施

UPDATE: you can create your custom ImportSqlCommandExtractor. For example: separate your commands with --****** (6 star symbols in comment, just to make your file proper SQL file, but with custom command separation in comments, or choose any insane combination, which you like) and then split those in simple implementation

public class ImportSqlCE implements ImportSqlCommandExtractor {

    private static final Logger log = LoggerFactory.getLogger(ImportSqlCE.class);

    @Override
    public String[] extractCommands(Reader reader) {
        try {
            String allCommands = IOUtils.toString(reader);

            return allCommands.split("--******");
        } catch (IOException e) {
            log.error("error reading import commands", e);
            log.info("sengind default empty command set");
            return new String[0];
        }
    }
}

，然后将休眠配置为使用它<property name="hibernate.hbm2ddl.import_files_sql_extractor" value="example.ImportSqlCE" />

and then configure hibernate to use it <property name="hibernate.hbm2ddl.import_files_sql_extractor" value="example.ImportSqlCE" />

与此相关，您的import.sql将支持美元报价(即它将仅忽略任何sql知道发生了什么事情.)

with this your import.sql will support dollar quoting (i.e. it will simply ignore any sql awareness of what is happening.)

这篇关于PostgreSQL/JPA-在import.sql文件中导入功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！