OpenJPA
  1. OpenJPA
  2. OPENJPA-723

Feature request for PostgreSQL XML Column Mapping

    Details

    • Type: New Feature New Feature
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.3.0, 2.0.0-M2
    • Component/s: sql
    • Labels:
      None

      Description

      PostgreSQL 8.3 has an xml data type it would be great if this was supported in the same manner as DB2, Oracle and SQLServer for XML Column Mapping.

      1. OPENJPA-723.patch
        6 kB
        Milosz Tylenda

        Issue Links

          Activity

          Hide
          Milosz Tylenda added a comment -

          The patch applied to trunk and 1.3.x branch. Made also an update for the manual.

          Show
          Milosz Tylenda added a comment - The patch applied to trunk and 1.3.x branch. Made also an update for the manual.
          Hide
          Milosz Tylenda added a comment -

          1. The attached patch provides XML column support for PostgreSQL. All changes
          are in the PostgresDictionary class.

          2. Required PostgreSQL version: 8.3 or later, with XML support compiled-in.

          3. Required JDBC driver version: 8.3-603 (2008-01-31) or later.

          4. xpath PostgreSQL function and cast are used to do XML comparisons.
          Sample queries generated by the patched code:

          SELECT t0.oid, t1.oid FROM TORDER t0 CROSS JOIN TORDER t1 WHERE (CAST((xpath('//City/text()',t0.shipAddress))[1] AS VARCHAR) = CAST((xpath('//City/text()',t1.shipAddress))[1] AS VARCHAR)) ORDER BY t0.oid ASC
          SELECT t0.oid, t0.version, t0.amount, t0.CUSTOMER_COUNTRYCODE, t0.CUSTOMER_ID, t0.delivered, t0.shipAddress FROM TORDER t0 CROSS JOIN TCUSTOMER t1 WHERE (CAST((xpath('/*/City/text()',t0.shipAddress))[1] AS VARCHAR) = t1.city)

          5. connectedConfiguration method is used to determine whether XML column is
          supported in the database.

          6. setClobString and setNull methods have been overridden/modified as PostgreSQL
          requires special care here when dealing with XML column.

          7. I removed the line

          varcharTypeName = "VARCHAR

          {0}";

          Now varcharTypeName defaults to "VARCHAR". The reason is it was causing
          incorrect SQL when using appendCast method. I could not figure out why
          was varcharTypeName set to "VARCHAR{0}

          " instead of just "VARCHAR". Does
          anybody have a clue? The change did not affect the persistence-jdbc test suite
          when run on PostgreSQL. Let me know if this change is not desired.

          8. I will provide an improved test case for XML column mapping shortly.

          9. If the patch is going to be accepted, I will provide an update for the
          manual too.

          10. I welcome comments and suggestions.

          Show
          Milosz Tylenda added a comment - 1. The attached patch provides XML column support for PostgreSQL. All changes are in the PostgresDictionary class. 2. Required PostgreSQL version: 8.3 or later, with XML support compiled-in. 3. Required JDBC driver version: 8.3-603 (2008-01-31) or later. 4. xpath PostgreSQL function and cast are used to do XML comparisons. Sample queries generated by the patched code: SELECT t0.oid, t1.oid FROM TORDER t0 CROSS JOIN TORDER t1 WHERE (CAST((xpath('/ /City/text()',t0.shipAddress)) [1] AS VARCHAR) = CAST((xpath('/ /City/text()',t1.shipAddress)) [1] AS VARCHAR)) ORDER BY t0.oid ASC SELECT t0.oid, t0.version, t0.amount, t0.CUSTOMER_COUNTRYCODE, t0.CUSTOMER_ID, t0.delivered, t0.shipAddress FROM TORDER t0 CROSS JOIN TCUSTOMER t1 WHERE (CAST((xpath('/*/City/text()',t0.shipAddress)) [1] AS VARCHAR) = t1.city) 5. connectedConfiguration method is used to determine whether XML column is supported in the database. 6. setClobString and setNull methods have been overridden/modified as PostgreSQL requires special care here when dealing with XML column. 7. I removed the line varcharTypeName = "VARCHAR {0}"; Now varcharTypeName defaults to "VARCHAR". The reason is it was causing incorrect SQL when using appendCast method. I could not figure out why was varcharTypeName set to "VARCHAR{0} " instead of just "VARCHAR". Does anybody have a clue? The change did not affect the persistence-jdbc test suite when run on PostgreSQL. Let me know if this change is not desired. 8. I will provide an improved test case for XML column mapping shortly. 9. If the patch is going to be accepted, I will provide an update for the manual too. 10. I welcome comments and suggestions.

            People

            • Assignee:
              Milosz Tylenda
              Reporter:
              Gianni Bruno
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development