Increase size of location column

[Alex de Maria]

Hi,

We are facing a problem with the length of the location field. We store the physical location, and sometimes (sporadically) it exceeds the 255-character limit.

It affects the Dataset and Datafile tables:

https://repo.icatproject.org/site/icat/server/6.1.0/schema.html#Dataset

https://repo.icatproject.org/site/icat/server/6.1.0/schema.html#Datafile

We are considering extending it to 512 characters, what do you think? Are you facing similar problems?

I presume that for those storing logical locations, this is easily avoidable.

On the other hand, looking at the icat.server code, I might be wrong but I don't see how the 255-character limit is enforced. It seems it's only enforced at the database level, so there are no changes needed in icat.server only in our database. Is that correct?

https://github.com/icatproject/icat.server/blob/487e52828fa89720495795f5f34bf43e8c8e0413/src/main/java/org/icatproject/core/entity/Datafile.java#L74

Thanks for the help!

A.

[Kevin Phipps - STFC UKRI]

Hi Alex,

Yes, we have had this issue on some of the fields in our ICAT databases. I think when you define a String field in your class then the EclipseLink JPA code just uses a default for the size of that field in the database (probably 255). We have increased the size of some fields directly in the database and ICAT works happily with that.

Looking at our Datafile table I can see that the LOCATION and NAME fields are set to VARCHAR2(3000 BYTE) where other String fields are set to what I presume is the default of VARCHAR2(255 BYTE).

Cheers,

Kevin

---

From: icat...@googlegroups.com <icat...@googlegroups.com> on behalf of Alex de Maria <demari...@gmail.com>
Sent: 17 June 2025 9:17
To: icatgroup <icat...@googlegroups.com>
Subject: [icatgroup:2128] Increase size of location column

 

--
You received this message because you are subscribed to the Google Groups "icatgroup" group.
To unsubscribe from this group and stop receiving emails from it, send an email to icatgroup+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/icatgroup/CABcLKQTGSggYmjq-EvUwZECaV_gqsTNMg4VLFJmP2OPjBUWJuA%40mail.gmail.com.

[Rolf Krahl]

Dear Alex & all,

Am Dienstag, 17. Juni 2025, 10:17:28 CEST schrieb Alex de Maria:
>
> We are facing a problem with the length of the location field. We store the
> physical location, and sometimes (sporadically) it exceeds the
> 255-character limit.
> It affects the Dataset and Datafile tables:
> https://repo.icatproject.org/site/icat/server/6.1.0/schema.html#Dataset
> https://repo.icatproject.org/site/icat/server/6.1.0/schema.html#Datafile
>
> We are considering extending it to 512 characters, what do you think? Are
> you facing similar problems?

Indeed, I also noticed that limit in practice. We do not allow users
to store files as they want in the storage, but rather impose a fixed
schema for the file path. In fact, I restricted the name of datasets
and datafiles to less then 100 characters in order to guarantee that
the full path (that is stored in the location attribute) according to
this schema will end up with less than 255 characters.

I firmly believe that no one ever really needs a dataset or file name
to be longer than 100 characters and that is why I wouldn't call this
limit a problem for us.

> On the other hand, looking at the icat.server code, I might be wrong but I
> don't see how the 255-character limit is enforced.

It is in the code, but only implicitly, see [1] for Dataset and [2]
for Datafile. 255 is the default for the length a String attribute in
the Jakarta Persistence framework. If you want a different value, say
511 [3], you'd need to add a decorator to the respective lines as in:

| @Comment("The logical location of the file - which may also be the physical location")
| @Column(length = 511)
| private String location;

> It seems it's only enforced at the database level, so there are no
> changes needed in icat.server only in our database. Is that correct?

The (implict) length value in the Java source has an impact on the
autogenerated SQL code to create the database.

And so, yes, in practice, you could solve the issue by simply
modifying the existing table definition in the database. But I'd
rather prefer to keep the database and the Java source consistent.

So I'd suggest you to open an issue requesting extending the length of
the location attributes.

Best regards,
Rolf


[1]: https://github.com/icatproject/icat.server/blob/487e52828fa89720495795f5f34bf43e8c8e0413/src/main/java/org/icatproject/core/entity/Dataset.java#L74
[2]: https://github.com/icatproject/icat.server/blob/487e52828fa89720495795f5f34bf43e8c8e0413/src/main/java/org/icatproject/core/entity/Datafile.java#L74

[3]: Note: for the sake of consistency with existing fields in our
schema, I'd prefer a length of 511 rather than 512, even if the
practical impact of the difference might be negligible.

--
Rolf Krahl <rolf....@helmholtz-berlin.de>
Helmholtz-Zentrum Berlin für Materialien und Energie (HZB)
Albert-Einstein-Str. 15, 12489 Berlin
Tel.: +49 30 8062 12122

[Alex de Maria]

Thanks both for your clear answers and explanations. I agree about keeping consistency between code and DB scripts. I've created an issue here:

https://github.com/icatproject/icat.server/issues/364

Best,

A.

--
You received this message because you are subscribed to the Google Groups "icatgroup" group.
To unsubscribe from this group and stop receiving emails from it, send an email to icatgroup+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/icatgroup/2785144.mvXUDI8C0e%40nb-6t25n64.basisit.de.

[Andrew Gotz]

Hi Alex,

your proposal makes sense for our needs, I had two questions:

(1) will this impact the performance?

(2) what is the impact on the storage?

I understood that the Datafile tables are very big, will doubling the size of one of the fields have a large impact?

Cheers

Andy

--
You received this message because you are subscribed to the Google Groups "icatgroup" group.
To unsubscribe from this group and stop receiving emails from it, send an email to icatgroup+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/icatgroup/CABcLKQTGSggYmjq-EvUwZECaV_gqsTNMg4VLFJmP2OPjBUWJuA%40mail.gmail.com.

[Alex de Maria]

Hi Andy,

On Wed, 18 Jun 2025 at 09:09, Andrew Gotz <andy...@esrf.fr> wrote:

Hi Alex,

your proposal makes sense for our needs, I had two questions:

(1) will this impact the performance?

(2) what is the impact on the storage?

I understood that the Datafile tables are very big, will doubling the size of one of the fields have a large impact?

No, despite the size of the table, we are not expecting any significant impact on performance or storage. Doubling the size of the column does not affect the existing data. This patch is intended to allow long file paths to be stored in the database, but these cases are (and should remain) rather exceptional, so we do not anticipate a dramatic increase in storage space in the long run either.

However, this will need to be confirmed in practice.

Best,

A.

 

Cheers

Andy

On 17/06/2025 10:17, Alex de Maria wrote:

Hi,

We are facing a problem with the length of the location field. We store the physical location, and sometimes (sporadically) it exceeds the 255-character limit.

It affects the Dataset and Datafile tables:

https://repo.icatproject.org/site/icat/server/6.1.0/schema.html#Dataset

https://repo.icatproject.org/site/icat/server/6I can imagine .1.0/schema.I can imagine html#Datafile

We are considering extending it to 512 characters, what do you think? Are you facing similar problems?

I presume that for those storing logical locations, this is easily avoidable.

On the other hand, looking at the icat.server code, I might be wrong but I don't see how the 255-character limit is enforced. It seems it's only enforced at the database level, so there are no changes needed in icat.server only in our database. Is that correct?

https://github.com/icatproject/icat.server/blob/487e52828fa89720495795f5f34bf43e8c8e0413/src/main/java/org/icatproject/core/entity/Datafile.java#L74

Thanks for the help!

A.

--
You received this message because you are subscribed to the Google Groups "icatgroup" group.
To unsubscribe from this group and stop receiving emails from it, send an email to icatgroup+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/icatgroup/CABcLKQTGSggYmjq-EvUwZECaV_gqsTNMg4VLFJmP2OPjBUWJuA%40mail.gmail.com.

--
You received this message because you are subscribed to the Google Groups "icatgroup" group.
To unsubscribe from this group and stop receiving emails from it, send an email to icatgroup+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/icatgroup/16d799ff-cf2f-4213-90f9-3cc249d408cd%40esrf.fr.

[Rolf Krahl]

Dear Andy,

Am Mittwoch, 18. Juni 2025, 09:09:46 CEST schrieb Andrew Gotz:
>
> your proposal makes sense for our needs, I had two questions:
>
> (1) will this impact the performance?
>
> (2) what is the impact on the storage?
>
> I understood that the Datafile tables are very big, will doubling the
> size of one of the fields have a large impact?

In addition to the reply from Alex: as far as I understand, there is
no general answer to this question, it heavily depends on your
database backend and also on the storage engine in use in the backend.

In many cases, the declared width of a String column only sets an
upper limit for the width of the values stored in that column, whereas
the storage requirement in the database is independent of the declared
width and only depends on the width of the actual values. In these
cases, there should be mostly no impact at all.

That is for instance the difference between CHAR and VARCHAR columns
in MySQL / MariaDB (with InnoDB storage engine): CHAR columns are
stored with a fixed width that is declared in the column definition.
In VARCHAR columns, only the actual width of stored value matters, the
declared width of the column has no impact at all (other than allowing
for larger values). In practice, all String columns use VARCHAR.

I don't know any details for Oracle databases, but I guess, the
situation is similar.

In case of doubt: ask your local database admin / expert.

Best,
Rolf

[Rolf Krahl]

Dear all,

I will not be available for the ICAT collaboration meeting next week
Thursday, 26th June. But it would be helpful if you could discuss and
decide on Issue #364, see also the comments in this issue. I suggest
we could include that to the schema changes that we are preparing to
include in icat.server 6.2.0.

Best regards,
Rolf

Am Dienstag, 17. Juni 2025, 10:17:28 CEST schrieb Alex de Maria: