betweenGo

(Photo: Monta driving by Yogma)

Specifying one-to-many relationships is ridiculously easy in Ruby on Rails.  Unfortunately it’s not so straight-forward in ATG repositories.

First you specify the “belongs to” relationship.  In this example the player belongs to a team.

<item-descriptor name="player">
  <table name="team_players" type="auxiliary" id-column-names="team_id" shared-table-sequence="1">
    <property name="team" column-name="team_id" item-type="team" />
  </table>
</item-descriptor>

Then you specify the “has many” relationship.  In this example the team has many players.

<item-descriptor name="team">
  <table name="team_players" type="multi" id-column-names="player_id" shared-table-sequence="2">
    <property name="players" column-name="player_id" data-type="set" component-item-type="player" />
  </table>
</item-descriptor>

Note the trick is specifying the “shared-table-sequence.”

Here is the SQL for the table that specifies this relationship in our example.

CREATE TABLE team_players
(
  team_id     VARCHAR2(40)  NOT NULL,
  player_id   VARCHAR2(40)  NOT NULL,
  CONSTRAINT team_players_pk PRIMARY KEY (team_id, player_id),
  CONSTRAINT team_players_players_fk foreign key (player_id) references players (id),
  CONSTRAINT team_players_team_fk foreign key (team_id) references teams (id)
);

(Photo: Love’s Old Sweet Song by linda yvonne)

The syntax for doing this is similar to doing an update in one table based on values in another table yet simpler.

INSERT INTO suppliers (name)
SELECT customers.name
  FROM customers
  WHERE customers.id = suppliers.id);

If you want to add constant values into the insert you can do something like this.

INSERT INTO suppliers (name, city)
SELECT customers.name, 'Toronto'
  FROM customers
  WHERE customers.id = suppliers.id);

For further reading please see SQL INSERT INTO.

(Photo: Rain on a window in Sunnyvale by basictheory)

There are various ways to alter a table and I usually forget what they are so I am writing this post to remind me.

Columns

ALTER TABLE foo DROP COLUMN nickname;
ALTER TABLE foo RENAME COLUMN name TO nickname;
ALTER TABLE foo ADD name VARCHAR2(254) DEFAULT 'Frank' NOT NULL;
ALTER TABLE foo ADD age INTEGER DEFAULT 0 NOT NULL;
ALTER TABLE foo MODIFY name VARCHAR2(500);

Constraints

ALTER TABLE foo DROP CONSTRAINT foo_a_f;
ALTER TABLE foo ADD CONSTRAINT foo_b_f FOREIGN KEY (bar_id) REFERENCES bar (id);

If you forget the name of a constraint and you can try to find it using this handy piece of SQL.

SELECT constraint_name FROM user_constraints WHERE constraint_name LIKE 'foo_%_f%';

I was always doing a commit before exiting SQL*Plus when it occurred to me today that maybe I didn’t need to do that.  Doing a Google search quickly answered that for me.

If you issue a graceful exit (via the “exit” or “quit” command), sqlplus will always issue a commit. However, if you were to be ungracefully disconnected, for example by closing your terminal window, then PMON will issue a rollback like it does with any other disconnected session.

sqlplus commit-on-exit?

Therefore there is no need to do a commit before you exit.

You can also set autocommit on, it is off by default, but I would not recommend doing this.

During interactive usage with sqlplus, Oracle also supports an AUTOCOMMIT option. With this option set to ON each individual SQL statement is treated as a transaction an will be automatically commited right after it is executed. A user can change the AUTOCOMMIT option by typing

 SET AUTOCOMMIT ON

or

 SET AUTOCOMMIT OFF

whereas by typing

 SHOW ALL

a user can see the current setting for the option (including other ones).

Oracle SQL Transactions

Edit the log4j.xml in the conf directory as shown below to turn on SQL debugging of the JDBC CMP plugin.

/apps/jboss/server/default/conf :->diff -c log4j.xml~ log4j.xml
*** log4j.xml~  Mon Sep 30 18:09:27 2002
--- log4j.xml   Tue Apr  4 20:41:18 2006
***************
*** 61,73 ****
    <!-- ============================== -->

    <appender name="CONSOLE" class="org.apache.log4j.ConsoleAppender">
!     <param name="Threshold" value="INFO"/>
      <param name="Target" value="System.out"/>

      <layout class="org.apache.log4j.PatternLayout">
        <!-- The default pattern: Date Priority [Category] Message\\n -->
        <param name="ConversionPattern" value="%d{ABSOLUTE} %-5p [%c{1}] %m%n"/>

      </layout>
    </appender>

--- 61,79 ----
    <!-- ============================== -->

    <appender name="CONSOLE" class="org.apache.log4j.ConsoleAppender">
!     <!--<param name="Threshold" value="INFO"/>-->
!     <param name="Threshold" value="DEBUG"/>
      <param name="Target" value="System.out"/>

      <layout class="org.apache.log4j.PatternLayout">
        <!-- The default pattern: Date Priority [Category] Message\\n -->
        <param name="ConversionPattern" value="%d{ABSOLUTE} %-5p [%c{1}] %m%n"/>

      </layout>
+
+     <category name="org.jboss.ejb.plugins.cmp.jdbc">
+       <priority value="DEBUG"/>
+     </category>
+
    </appender>

If you want to log Hibernate SQL statements:

    <category name="org.hibernate.SQL">
      <priority value="DEBUG"/>
    </category>

If you want to log everything Hibernate’s doing, including SQL statements, schema export, transactions, etc.:

    <category name="org.hibernate.SQL">
      <priority value="DEBUG"/>
    </category>