betweenGo

Growing by Simon Peckham

Delete From One Table Whose Values Don’t Appear in Another Table

Sometimes you will find that you have items in a table whose values reference items in another table that no longer exist.  For example in ATG you may have orders that reference profiles that no longer exist.  This could happen if an order is for an anonymous profile that was deleted.

Here is an example of how to delete items in a table whose values reference items in another table that no longer exist, in this case ATG orders whose profiles no longer exist.

DELETE FROM dcspp_order
WHERE profile_id
NOT IN
(
  SELECT id
  FROM dps_user
);

This example does not actually work because of the dependencies on the dcspp_order table so please don’t try it.

Delete From One Table Based on Values in Another Table

Sometimes you want to delete items in one tables based on values in another table.  You can do this similarly to the previous case.

DELETE FROM dcspp_order
WHERE profile_id
IN
(
  SELECT id
  FROM dps_user
  WHERE id LIKE '6%'
);

This example also does not actually work because of the dependencies on the dcspp_order table so please don’t try it.

For further reading please see How to delete records from a SQL Server database and SQL Delete Rows Based on Another Table.

(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

Stone Table by WorldWanderlust

To get the table definition.

describe foo;

Or simply

desc foo

To get the constraints of a table.

select * from user_constraints where table_name='foo';

Similarly to find the table for a constraint.

select table_name from user_constraints where constraint_name='bar';

To find all the tables that have foreign key constraints for a given table you can use this query.  It first find the name of the unique and/or primary key constraints in the given table.  Then it finds all the constraints in other tables that reference this constraint. Note that the table names are usually uppercase.

select owner,constraint_name,constraint_type,table_name,r_owner,
r_constraint_name
from all_constraints
where constraint_type='R'
and r_constraint_name
in (select constraint_name
from all_constraints
where constraint_type in ('P','U')
and table_name='BAZ');

A simpler way which also constrains the owner would be like this.

select table_name,constraint_name
from all_constraints
where constraint_type='R'
and owner='FRANK'
and r_constraint_name
in (select constraint_name
from all_constraints
where constraint_type in ('P','U')
and table_name='BAZ');

For further reading please see Finding Foreign Key Constraints in Oracle and Oracle Constraints.

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>