Hello Romain,
On Tue, 19 Aug 2025 at 15:15, Romain Manni-Bucau <rmannibu...@gmail.com> wrote:
>
> Hi,
>
> schemaCase is mainly for the database metadata - column names for ex, so
> has no impact on the connection url itself normally.
> The jdbc url case is normally passthrough.
>
> However you do setup buildSchema: <property
> name="openjpa.jdbc.SynchronizeMappings"
> value="buildSchema(foreignKeys=true)" />
>
> So schema factory (native) is skipped to be replaced by dynamic behavior so
> long story short the table is always assumed missing. You can tune it to be
> *refresh* instead of *buildSchema * to ensure you do use native mode.
`refresh` behave the same way `buildSchema` does
from what I can see Postgres17 always creates DB Table in lower case
and made it accessible in-ignore-case mode
same time schema tool executes following SQL to check if table already exists:
SELECT * FROM (SELECT current_database() AS current_database,
n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull OR (t.typtype =
'd' AND t.typnotnull) AS
attnotnull,a.atttypmod,a.attlen,t.typtypmod,row_number() OVER
(PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum,
nullif(a.attidentity, '') as attidentity,nullif(a.attgenerated, '') as
attgenerated,pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS
adsrc,dsc.description,t.typbasetype,t.typtype FROM
pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON
(c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON
(a.attrelid=c.oid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid)
LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND
a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON
(c.oid=dsc.objoid AND a.attnum = dsc.objsubid) LEFT JOIN
pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND
dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON
(dc.relnamespace=dn.oid AND dn.nspname='pg_catalog') WHERE c.relkind
in ('r','p','v','f','m') and a.attnum > 0 AND NOT a.attisdropped AND
c.relname LIKE 'postgre17_test') c WHERE true ORDER BY
nspname,c.relname,attnum;
it has `c.relname LIKE 'postgre17_test'` part which is case sensitive
>
> However there are a lot of toggles involved there:
>
> <property name="openjpa.jdbc.SynchronizeMappings"
> value="buildSchema(readSchema=true,foreignKeys=true)" />
> <property name="openjpa.jdbc.SchemaFactory" value="native(foreignKeys=true)"
> />
> <property name="openjpa.jdbc.DBDictionary"
> value="postgres(schemaCase=preserve,supportsSchemaForGetColumns=false)"/>
> <property name="openjpa.Sequence"
> value="table(Table=openjpa_sequence_table, Increment=1)"/>
> <property name="openjpa.jdbc.Schema" value="public"/>
>
>
> to show a few which impact this discovery
>
> But the root issue is that schema and table names are treated the same so
> until you do @Table on all entities to comply to what the dictionary
> "generates" and explicit the table (openjpa.Sequence) I fear you will need
> your own dictionary to workaround this posgres driver regression.
>
> On the long run we can want to do a few changes at openjpa level:
>
> * ensure we have a case configuration for table and for schema but not the
> same one
> * do the test in schema case insensitively for postgres since it is case
> sensitive but behaves as being insensitive
>
> If you are motiviated to do a PR we would be happy to review it or guide
> you to do it.
I'm not sure I'll have enough free time to "collect this puzzle", but
i'll plan to check the code again (at least once :))
>
> Romain Manni-Bucau
> @rmannibucau <https://x.com/rmannibucau> | .NET Blog
> <https://dotnetbirdie.github.io/> | Blog <https://rmannibucau.github.io/> |
> Old
> Blog <http://rmannibucau.wordpress.com> | Github
> <https://github.com/rmannibucau> | LinkedIn
> <https://www.linkedin.com/in/rmannibucau> | Book
> <https://www.packtpub.com/en-us/product/java-ee-8-high-performance-9781788473064>
> Javaccino founder (Java/.NET service - contact via linkedin)
>
>
> Le mar. 19 août 2025 à 07:12, Maxim Solodovnik <solomax...@gmail.com> a
> écrit :
>
> > Hello Fred,
> >
> > sorry for the late responses (my free time is limited :(( )
> > I'll try to create reproducer for your case locally and will report
> > back to this thread :)
> >
> > On Mon, 18 Aug 2025 at 16:52, MONCLAR Frederic
> > <frederic.monc...@thalesaleniaspace.com> wrote:
> > >
> > > Hi Maxim,
> > >
> > > I saw that openjpa v4.1.1 was not verified with PostgreSQL v17 and
> > PostgreSQL JDBC Driver v42.7.5 :
> > > (PostgreSQL 8.3.5, 8.4, 9, 11, 12, 13 / PostgreSQL Native
> > Driver 8.3 JDBC3 (build 603), 8.4 JDBC3 (build 701), 42.2.5, , 42.2.19)
> > > So do you think that it can explain why this property does not work in
> > my case ?
> > > Thanks
> > > Fred
> > >
> > > -----Message d'origine-----
> > > De : MONCLAR Frederic
> > > Envoyé : jeudi 14 août 2025 09:35
> > > À : 'Maxim Solodovnik' <solomax...@gmail.com>
> > > Cc : dev@openjpa.apache.org
> > > Objet : RE: how to get database name case preserved in url of
> > ConnectionURL property
> > > Critère de diffusion : Confidentiel
> > >
> > > Hi Maxim,
> > > Thank you for your reply.
> > > I attach the picture to my reply in order to make it visible. This image
> > is a capture of the code changes in PostgreSQL JDBC Driver v42.7.5 that
> > create my issue. It is annoying that the JDBC driver has evolved like that
> > on a minor version.
> > > I tried what you proposed : <property name="openjpa.jdbc.DBDictionary"
> > value="postgres(schemaCase=preserve)"/> but it does not work. I still get
> > the error where the lib try to create tables like if the table does not
> > exists :-( Fred
> > >
> > > 14-08-2025 09:32:10.317 [main] INFO
> > [d.t.e.APersistencedb][openPhaseRemote]
> > {openjpa.ConnectionURL=jdbc:postgresql://localhost:9999/DB_TEST,
> > openjpa.ConnectionUserName=postgres, openjpa.ConnectionPassword=postgres}
> > > 14-08-2025 09:32:12.031 [main] ERROR
> > [d.t.e.APersistencedb][openPhaseRemote] ERREUR: la relation «
> > openjpa_sequence_table » existe déjà {stmnt 971404566 CREATE TABLE
> > OPENJPA_SEQUENCE_TABLE (ID SMALLINT NOT NULL, SEQUENCE_VALUE BIGINT,
> > PRIMARY KEY (ID))} [code=0, state=42P07]
> > > org.apache.openjpa.persistence.PersistenceException: ERREUR: la relation
> > « openjpa_sequence_table » existe déjà {stmnt 971404566 CREATE TABLE
> > OPENJPA_SEQUENCE_TABLE (ID SMALLINT NOT NULL, SEQUENCE_VALUE BIGINT,
> > PRIMARY KEY (ID))} [code=0, state=42P07]
> > > at
> > org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:625)
> > > at
> > org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:488)
> > > at
> > org.apache.openjpa.jdbc.kernel.JDBCBrokerFactory.synchronizeMappings(JDBCBrokerFactory.java:173)
> > > at
> > org.apache.openjpa.jdbc.kernel.JDBCBrokerFactory.synchronizeMappings(JDBCBrokerFactory.java:178)
> > >
> > > -----Message d'origine-----
> > > De : Maxim Solodovnik <solomax...@gmail.com> Envoyé : jeudi 14 août
> > 2025 06:46 À : MONCLAR Frederic <frederic.monc...@thalesaleniaspace.com>
> > > Cc : dev@openjpa.apache.org
> > > Objet : Re: how to get database name case preserved in url of
> > ConnectionURL property
> > >
> > > re-sending with modified email of TS :)
> > >
> > > On Thu, 14 Aug 2025 at 11:44, Maxim Solodovnik <solomax...@gmail.com>
> > wrote:
> > > >
> > > > Hello Fred,
> > > >
> > > > welcome to the list :)
> > > >
> > > >
> > > > On Wed, 13 Aug 2025 at 13:35, MONCLAR Frederic
> > > > <frederic.monc...@thalesaleniaspace.com.invalid> wrote:
> > > > >
> > > > > Hello,
> > > > >
> > > > >
> > > > >
> > > > > I would like to know if there is an option in persistence.xml or in
> > creating entity manager factory methods where I can specify to use
> > “CASE_PRESERVE” in order to preserve the case of the name of the database
> > in ConnectionURL property?
> > > > >
> > > >
> > > > It seems to me this question better fits users@openjpa.a.o
> > > > mailing-list :))
> > > >
> > > > >
> > > > >
> > > > > I recently posted an issue on PostgreSQL JDBC Driver v42.7.5/6/7 :
> > https://github.com/pgjdbc/pgjdbc/issues/3731 because I get the following
> > exception :
> > > > >
> > > > >
> > > >
> > > > I would try to set
> > > >
> > > > openjpa.jdbc.DBDictionary: postgres(schemaCase=preserve)
> > > >
> > > > like this:
> > > >
> > > > <properties>
> > > > <property name="openjpa.jdbc.DBDictionary"
> > > > value="postgres(schemaCase=preserve)" /> </properties>
> > > >
> > > >
> > > > >
> > > > > 22-07-2025 13:05:52.082 [pool-6-thread-1] ERROR
> > > > > [c.t.s.s.p.d.i.f.APersistencedb][openPhaseRemote] ERREUR: la
> > > > > relation « element » existe déjà {stmnt 445531560 CREATE TABLE
> > > > > element ...))} [code=0, state=42P07]
> > > > > org.apache.openjpa.persistence.PersistenceException: ERREUR: la
> > > > > relation « element » existe déjà {stmnt 445531560 CREATE TABLE
> > > > > element ...)} [code=0, state=42P07] at
> > > > > org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:625
> > > > > ) at
> > > > > org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:488
> > > > > )
> > > > >
> > > > >
> > > > >
> > > > > when I try to create an EntityManager and it was working fine until
> > version 42.7.4 :
> > > > >
> > > > >
> > > > >
> > > > > String dbUrl = "jdbc:postgresql://" +
> > > > > "localhost:9999/\"DB_TEST\"";
> > > > >
> > > > > properties.put("openjpa.ConnectionURL", dbUrl);
> > > > >
> > > > > properties.put("openjpa.ConnectionUserName", "postgres");
> > > > >
> > > > > properties.put("openjpa.ConnectionPassword", "postgres");
> > > > >
> > > > >
> > > > >
> > > > > EntityManagerFactory emf =
> > > > > Persistence.createEntityManagerFactory("test_db", properties);
> > > > >
> > > > > EntityManager em = emf.createEntityManager();
> > > > >
> > > > >
> > > > >
> > > > > PostgreSQL JDBC Driver support explained that they did the following
> > change in v42.7.5 :
> > > > >
> > > > >
> > > > >
> > > > > https://github.com/apache/openjpa/blob/87e253b9e58a8f061b0431a1ab4df
> > > > > 3e5e1660519/openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/ident
> > > > > ifier/DBIdentifierUtilImpl.java#L407-L431
> > > > >
> > > > >
> > > >
> > > > Unfortunately your image was not delivered to the list Please upload
> > > > it to some image-sharing-service and send URL :))
> > > >
> > > > >
> > > > > and since, I can’t use uppercase for the name of the database- L
> > > > >
> > > > > They proposes to find a way in openjpa to preserve case for database
> > name.
> > > > >
> > > > >
> > > > >
> > > > > Thanks for any help.
> > > > >
> > > > > Fred
> > > >
> > > >
> > > >
> > > > --
> > > > Best regards,
> > > > Maxim
> > >
> > >
> > >
> > > --
> > > Best regards,
> > > Maxim
> >
> >
> >
> > --
> > Best regards,
> > Maxim
> >
--
Best regards,
Maxim
