Tag: sql
Specifying One-to-Many Relationship in ATG Repositories
by Frank Kim on Jan.21, 2010, under Repository
(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) );
SQL Insert in One Table Based on Values in Another Table
by Frank Kim on Jan.18, 2010, under Oracle
(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.
How to Alter Table
by Frank Kim on Oct.06, 2009, under Oracle
(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%';
SQL*Plus Commit on Exit
by Frank Kim on Aug.25, 2009, under Oracle
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.
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 ONor
SET AUTOCOMMIT OFFwhereas by typing
SHOW ALLa user can see the current setting for the option (including other ones).
How to Log SQL on JBoss
by Frank Kim on Apr.04, 2006, under Hibernate, JBoss, JDBC, Logging
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>


