Liquibase modify data type continues for CLOB

Support
#1

Hi all,
New problem at me from yesterday.
So now every time I run the project liquibase wish to modify data type with CLOB (because I set unlimited)

This is what wish liquibase to do:

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
                   xmlns:pro="http://www.liquibase.org/xml/ns/pro"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.6.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.6.xsd">
    <property name="offsetDateTime.type"
              value="timestamp with time zone"
              dbms="postgresql"/>
    <changeSet author="genedatabank"
               id="1">
        <modifyDataType columnName="FILE_"
                        newDataType="CLOB"
                        tableName="SYS_FILE"/>
    </changeSet>
</databaseChangeLog>

In entity SysFile I have:

...
    @NotNull
    @Lob
    @Column(name = "FILE_", nullable = false)
    private FileRef file;
...

Table in database was created with CLOB data type:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.20.xsd"
        objectQuotingStrategy="QUOTE_ONLY_RESERVED_WORDS">
    <changeSet id="1" author="genedatabank">
        <createTable tableName="SYS_FILE">
            <column name="ID" type="UUID">
                <constraints nullable="false" primaryKey="true" primaryKeyName="PK_SYS_FILE"/>
            </column>
            <column name="VERSION" type="INT">
                <constraints nullable="false"/>
            </column>
            <column name="CREATED_BY" type="VARCHAR(255)"/>
            <column name="CREATED_DATE" type="${offsetDateTime.type}"/>
            <column name="LAST_MODIFIED_BY" type="VARCHAR(255)"/>
            <column name="LAST_MODIFIED_DATE" type="${offsetDateTime.type}"/>
            <column name="DELETED_BY" type="VARCHAR(255)"/>
            <column name="DELETED_DATE" type="${offsetDateTime.type}"/>
            <column name="NAME" type="VARCHAR(500)">
                <constraints nullable="false"/>
            </column>
            <column name="EXT" type="VARCHAR(20)"/>
            <column name="FILE_SIZE" type="BIGINT"/>
            <column name="CREATE_DATE" type="DATE"/>
            <column name="FILE_" type="CLOB">
                <constraints nullable="false"/>
            </column>
        </createTable>
    </changeSet>
</databaseChangeLog>

and PostgreSQL database field file_ have text data type in table sys_file, because CLOB not exist in PostgreSQL.

Another problem is the dimension for the file to be upload not let me to upload more 1Mb.

I will try to change in SysFile entity the @Column annotation:

...
    @NotNull
    @Lob
     @Column(name = "FILE_", columnDefinition = "TEXT", nullable = false)
    private FileRef file;
...

maybe work.

I found some explications at JPA Annotation for the PostgreSQL TEXT Type | Baeldung

Using the @Lob annotation on the description field, we instruct Hibernate to manage this field using the PostgreSQL TEXT type.

Note that when we use Hibernate with PostgreSQL, the storage mechanics become unusual when handling a String attribute annotated with @Lob. We must understand how Hibernate persists @Lob String attribute values to avoid losing information.

For instance, PostgreSQL stores the contents of a column annotated with @Lob in a separate table, the column itself will only store a UID for each entry in that table. Therefore, this behaviour may lead to information loss. To solve this problem, we can either use @Column(columnDefinition=”TEXT”) along with the @Lob annotation or use only @Column(columnDefinition = “TEXT”).

You know other solution?

Thank you!

#2

columnDefinition = "TEXT is not a solution do it the same thing, another with modification:

image

#4

Problems solved :wink:
Solution, I changed in entity SysFile, without unlimited:

...
    @NotNull
    @Column(name = "FILE_", nullable = false, length = 1024)
    private FileRef file;
...

because is not unlimited for file, is only for the text in the field :slightly_smiling_face:

and for limit upload I read the documentation with attention, also the Note :slightly_smiling_face:

I added in file application.properties

...
# Maximum file size to upload is set to infinite
spring.servlet.multipart.max-file-size =-1
spring.servlet.multipart.max-request-size =-1
...

Thank you!

1 Like