0 Replies Latest reply on Jul 12, 2006 9:19 PM by Liam McGrath

    CMP type mapping for String with Postgres

    Liam McGrath Newbie

      I switched my DefaultDS to PostgreSQL using directions similar to this: http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3951811

      I edited the datasource-mapping element of standardjbosscmp-jdbc.xml so that it looks like the one below. The mapping below seems to specify that objects of type java.lang.String should be stored in columns of type TEXT (which are variable in length with no limit), however, columns of type varchar(255) (which are variable in length with limit of 255) are getting created in my database for Strings. Do I understand this correctly? Could I be missing something?

      (Another possibly related thing I noticed since switching this file is that it's almost almost seems like the reserved-words portion of this file is being ignored. I had an entity bean named User that never caused problems when hsqldb was the defaultds, but now it errors when trying to create a table named User.)

      Thanks

      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE jbosscmp-jdbc PUBLIC
       "-//JBoss//DTD JBOSSCMP-JDBC 4.0//EN"
       "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_4_0.dtd">
      
      <!-- ===================================================================== -->
      <!-- -->
      <!-- Standard JBossCMP-JDBC Configuration -->
      <!-- -->
      <!-- ===================================================================== -->
      
      <!-- $Id: standardjbosscmp-jdbc.xml,v 1.84.2.8 2006/03/06 14:38:05 aloubyansky Exp $ -->
      
      <jbosscmp-jdbc>
      
       <defaults>
       <datasource>java:/DefaultDS</datasource>
       <datasource-mapping>PostgreSQL</datasource-mapping>
      
       <create-table>true</create-table>
       <remove-table>false</remove-table>
       <read-only>false</read-only>
       <read-time-out>300000</read-time-out>
       <row-locking>false</row-locking>
       <pk-constraint>true</pk-constraint>
       <fk-constraint>false</fk-constraint>
       <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
       <read-ahead>
       <strategy>on-load</strategy>
       <page-size>1000</page-size>
       <eager-load-group>*</eager-load-group>
       </read-ahead>
       <list-cache-max>1000</list-cache-max>
       <clean-read-ahead-on-load>false</clean-read-ahead-on-load>
      
       <unknown-pk>
       <key-generator-factory>UUIDKeyGeneratorFactory</key-generator-factory>
       <unknown-pk-class>java.lang.String</unknown-pk-class>
       <jdbc-type>VARCHAR</jdbc-type>
       <sql-type>VARCHAR(32)</sql-type>
       </unknown-pk>
      
       <entity-command name="default"/>
       <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLCompiler</ql-compiler>
       <throw-runtime-exceptions>false</throw-runtime-exceptions>
       </defaults>
      
       <type-mappings>
      
      (snip)-----------------------------------------
      
      <type-mapping>
       <name>PostgreSQL</name>
       <row-locking-template>SELECT ?1 FROM ?2 WHERE ?3 ORDER BY ?4 FOR UPDATE</row-locking-template>
       <pk-constraint-template>CONSTRAINT ?1 PRIMARY KEY (?2)</pk-constraint-template>
       <fk-constraint-template>ALTER TABLE ?1 ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)</fk-constraint-template>
       <auto-increment-template>?1</auto-increment-template>
       <alias-header-prefix>t</alias-header-prefix>
       <alias-header-suffix>_</alias-header-suffix>
       <alias-max-length>32</alias-max-length>
       <subquery-supported>true</subquery-supported>
       <true-mapping>TRUE</true-mapping>
       <false-mapping>FALSE</false-mapping>
      
       <function-mapping>
       <function-name>concat</function-name>
       <function-sql>(?1 || ?2)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>substring</function-name>
       <function-sql>substring(?1 FROM ?2 FOR ?3)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>lcase</function-name>
       <function-sql>lower(?1)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>length</function-name>
       <function-sql>length(?1)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>locate</function-name>
       <function-sql>(CASE position(?1 in substring(?2 from ?3)) WHEN 0 THEN 0 ELSE position(?1 in substring(?2 from ?3)) + ?3 - 1 END)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>abs</function-name>
       <function-sql>abs(?1)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>sqrt</function-name>
       <function-sql>sqrt(CAST(?1 AS double precision))</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>ucase</function-name>
       <function-sql>upper(?1)</function-sql>
       </function-mapping>
       <function-mapping>
       <function-name>count</function-name>
       <function-sql>count(?1)</function-sql>
       </function-mapping>
      
       <mapping>
       <java-type>java.lang.Boolean</java-type>
       <jdbc-type>CHAR</jdbc-type>
       <sql-type>BOOLEAN</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Byte</java-type>
       <jdbc-type>TINYINT</jdbc-type>
       <sql-type>INT2</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Short</java-type>
       <jdbc-type>SMALLINT</jdbc-type>
       <sql-type>INT2</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Integer</java-type>
       <jdbc-type>INTEGER</jdbc-type>
       <sql-type>INT4</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Long</java-type>
       <jdbc-type>BIGINT</jdbc-type>
       <sql-type>INT8</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Float</java-type>
       <jdbc-type>FLOAT</jdbc-type>
       <sql-type>FLOAT(7)</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Double</java-type>
       <jdbc-type>DOUBLE</jdbc-type>
       <sql-type>FLOAT8</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Character</java-type>
       <jdbc-type>CHAR</jdbc-type>
       <sql-type>CHAR(1)</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.String</java-type>
       <jdbc-type>VARCHAR</jdbc-type>
       <sql-type>TEXT</sql-type>
       </mapping>
       <mapping>
       <java-type>java.util.Date</java-type>
       <jdbc-type>TIMESTAMP</jdbc-type>
       <sql-type>TIMESTAMP with time zone</sql-type>
       </mapping>
       <mapping>
       <java-type>java.sql.Date</java-type>
       <jdbc-type>DATE</jdbc-type>
       <sql-type>DATE</sql-type>
       </mapping>
       <mapping>
       <java-type>java.sql.Time</java-type>
       <jdbc-type>TIME</jdbc-type>
       <sql-type>TIME</sql-type>
       </mapping>
       <mapping>
       <java-type>java.sql.Timestamp</java-type>
       <jdbc-type>TIMESTAMP</jdbc-type>
       <sql-type>TIMESTAMP with time zone</sql-type>
       </mapping>
       <mapping>
       <java-type>java.math.BigDecimal</java-type>
       <jdbc-type>NUMERIC</jdbc-type>
       <sql-type>NUMERIC</sql-type>
       </mapping>
       <mapping>
       <java-type>java.lang.Object</java-type>
       <jdbc-type>VARBINARY</jdbc-type>
       <sql-type>BYTEA</sql-type>
       </mapping>
       </type-mapping>
      
      (snip)-------------------------------------------------
      
       <!-- reserved words that should not be used as table names -->
       <!-- JBoss will escape them by prepending a 'X' -->
       <reserved-words>
       <word>alias</word>
       <word>alter</word>
       <word>between</word>
       <word>char</word>
       <word>column</word>
       <word>commit</word>
       <word>create</word>
       <word>date</word>
       <word>default</word>
       <word>delete</word>
       <word>drop</word>
       <word>file</word>
       <word>from</word>
       <word>grant</word>
       <word>group</word>
       <word>index</word>
       <word>integer</word>
       <word>join</word>
       <word>number</word>
       <word>on</word>
       <word>order</word>
       <word>primary</word>
       <word>public</word>
       <word>revoke</word>
       <word>rollback</word>
       <word>row</word>
       <word>select</word>
       <word>set</word>
       <word>session</word>
       <word>size</word>
       <word>table</word>
       <word>trigger</word>
       <word>update</word>
       <word>unique</word>
       <word>user</word>
       <word>varchar</word>
       <word>view</word>
       </reserved-words>
      </jbosscmp-jdbc>