SyntaxHighlighter JS

2016-03-06

Oracle foreign key analysis

Find Oracle foreign key by name
For Oracle error

SQL Error: ORA-02291: integrity constraint (SCHEMA_OWNER.FOREIGN_KEY_NAME) violated - parent key not found

Find the source and reference Oracle table of the foreign key name using the SQL query below

select uc.CONSTRAINT_NAME, 
  src_ucc.OWNER source_schema ,src_ucc.TABLE_NAME source_table, 
  src_ucc.COLUMN_NAME source_column, src_ucc.POSITION, 
  dest_ucc.TABLE_NAME reference_table, dest_ucc.COLUMN_NAME reference_column,
  dest_ucc.POSITION reference_position,
  uc.DELETE_RULE, uc.STATUS, uc.DEFERRABLE, uc.DEFERRED, uc.VALIDATED  
from sys.USER_CONSTRAINTS uc 
  join sys.USER_CONS_COLUMNS src_ucc 
  on uc.OWNER=src_ucc.OWNER and uc.CONSTRAINT_NAME=src_ucc.CONSTRAINT_NAME
  join sys.USER_CONS_COLUMNS dest_ucc 
  on uc.OWNER=dest_ucc.OWNER and uc.R_CONSTRAINT_NAME=dest_ucc.CONSTRAINT_NAME  
where uc.OWNER='SCHEMA_OWNER' and uc.CONSTRAINT_NAME='FOREIGN_KEY_NAME'
  and uc.CONSTRAINT_TYPE='R' 
  and src_ucc.POSITION=dest_ucc.POSITION
order by src_ucc.TABLE_NAME ,src_ucc.POSITION, dest_ucc.POSITION;

Find all Oracle foreign keys on a table
To find all the foreign keys that a Oracle table has, use the SQL query below

select uc.CONSTRAINT_NAME, 
  src_ucc.OWNER source_schema ,src_ucc.TABLE_NAME source_table, 
  src_ucc.COLUMN_NAME source_column, src_ucc.POSITION, 
  dest_ucc.TABLE_NAME reference_table, dest_ucc.COLUMN_NAME reference_column,
  dest_ucc.POSITION reference_position,
  uc.DELETE_RULE, uc.STATUS, uc.DEFERRABLE, uc.DEFERRED, uc.VALIDATED  
from sys.USER_CONSTRAINTS uc 
  join sys.USER_CONS_COLUMNS src_ucc 
  on uc.OWNER=src_ucc.OWNER and uc.CONSTRAINT_NAME=src_ucc.CONSTRAINT_NAME
  join sys.USER_CONS_COLUMNS dest_ucc 
  on uc.OWNER=dest_ucc.OWNER and uc.R_CONSTRAINT_NAME=dest_ucc.CONSTRAINT_NAME  
where src_ucc.OWNER='SCHEMA_OWNER' and src_ucc.TABLE_NAME='SOURCE_TABLE_NAME'
  and uc.CONSTRAINT_TYPE='R' 
  and src_ucc.POSITION=dest_ucc.POSITION
order by src_ucc.TABLE_NAME ,src_ucc.POSITION, dest_ucc.POSITION;

Find all Oracle foreign keys referenced to a table
To find all the Oracle tables that references a table via foreign key, use the SQL query below

select uc.CONSTRAINT_NAME, 
  src_ucc.OWNER source_schema ,src_ucc.TABLE_NAME source_table, 
  src_ucc.COLUMN_NAME source_column, src_ucc.POSITION, 
  dest_ucc.TABLE_NAME reference_table, dest_ucc.COLUMN_NAME reference_column,
  dest_ucc.POSITION reference_position,
  uc.DELETE_RULE, uc.STATUS, uc.DEFERRABLE, uc.DEFERRED, uc.VALIDATED  
from sys.USER_CONSTRAINTS uc 
  join sys.USER_CONS_COLUMNS src_ucc 
  on uc.OWNER=src_ucc.OWNER and uc.CONSTRAINT_NAME=src_ucc.CONSTRAINT_NAME
  join sys.USER_CONS_COLUMNS dest_ucc 
  on uc.OWNER=dest_ucc.OWNER and uc.R_CONSTRAINT_NAME=dest_ucc.CONSTRAINT_NAME  
where dest_ucc.OWNER='SCHEMA_OWNER' and dest_ucc.TABLE_NAME='REFERENCE_TABLE_NAME'
  and uc.CONSTRAINT_TYPE='R' 
  and src_ucc.POSITION=dest_ucc.POSITION
order by src_ucc.TABLE_NAME ,src_ucc.POSITION, dest_ucc.POSITION;
Column meaning
CONSTRAINT_NAMEThe foreign key name
SOURCE_SCHEMA The schema of the foreign key source table
SOURCE_TABLE The table that has the foreign key
SOURCE_COLUMN The column in the source table enforced by the foreign key
POSITION For foreign keys with multiple columns, the order of the columns in the foreign key
REFERENCE_TABLE The table referenced by the foreign key
REFERENCE_COLUMN The column of the table referenced by the foreign key
REFERENCE_POSITION For foreign keys with multiple columns, the order of the referenced columns in the foreign key
DELETE_RULE The action Oracle performs with the row associated with the foreign key in the reference table is deleted
  • NO ACTION: Oracle respond with a referential integrity error and will not allow the row to be removed
  • CASCADE: Oracle automatically deletes the row in the tables with the associated foreign key
  • SET NULL: Oracle automatically sets the foreign key column value to NULL
STATUS Is the foreign key enforced
  • ENABLED: The foreign key is enforced and active
  • DISABLED: The foreign key is not enforced and inactive
DEFERRABLE If a transaction can use SET CONSTRAINT[S] to dynamically defer a foreign key referential integrity check
  • DEFERRABLE: Yes you can use SET CONSTRAINTS
  • NOT DEFERRABLE: No you cannot use SET CONSTRAINTS
DEFERRED If a foreign key is DEFERRABLE, when in the transaction will Oracle check for foreign key referential integrity violations by default. NOT DEFERRABLE is always IMMEDIATE.
  • DEFERRED: Oracle will check when after a commit has been issued
  • IMMEDIATELY Oracle will immediately check when the SQL is executed.
VALIDATED When adding foreign keys to a Oracle table with existing data, checks to see if the existing data violates foreign key referential integrity.
  • VALIDATED: Oracle will check to see if the existing data violates integrity. If it does then the foreign key will not be created
  • NOT VALIDATED: Oracle will not check to see if the existing data violates integrity. The foreign key will be created even if there is existing bad data

See the Oracle language reference on constraints for more details.

2015-10-04

OSGi Tutorial: Make Classes and Interfaces public

What you will do:
  • Create an OSGi bundle that exports classes and interfaces
What you will learn:
  • What is an OSGi bundle
  • How to create an OSGi bundle using maven-bundle-plugin
  • How to install an OSGi bundle
  • How to expose classes and interfaces so other bundles can use it
  • Learn to use Export-Package in MANIFEST.MF
Code at: https://github.com/juttayaya/osgi/tree/master/api
This code is primarily tested on JBoss Fuse. ServiceMix and Karaf 3 and 4 are also supported.

An OSGi bundle is just a JAR file with custom entries in the META-INF/MANIFEST.MF file.

For this example, we want to make the Java interface com.jirawat.osgi.api.Echo and Java class com.jirawat.osgi.exception.EchoException public so that it can be reused. The end result is a JAR file with Echo and EchoException in it, just like you would normally package any Java classes in a JAR file.  The OSGi difference is that this JAR file contains an entry in META-INF/MANIFEST.MF called Export-Package.  As the name suggests, the Export-Package entry tells OSGi that classes in this Java package are public.

Step 1: Configure maven-bundle-plugin to export public packages
Configure maven-bundle-plugin to specify which Java package to make public by adding it to the Export-Package entries.

Step 2: Configure pom.xml for bundle packaging
Set maven-bundle-plugin extensions to true. Then add bundle as the packaging option

The pom.xml should look like this
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.jirawat.osgi</groupId>
    <artifactId>api</artifactId>
    <version>1.0.0</version>
    <packaging>bundle</packaging>
    <name>jirawat.com :: tutorial :: ${project.artifactId} Bundle</name>
    <description>Tutorial Bundle for ${project.artifactId}</description>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.felix</groupId>
                <artifactId>maven-bundle-plugin</artifactId>
                <version>3.0.0</version>
                <extensions>true</extensions>
                <configuration>
                    <instructions>
                        <Bundle-SymbolicName>${project.groupId}.${project.artifactId}</Bundle-SymbolicName>
                        <Export-Package>
                            com.jirawat.osgi.api,
                            com.jirawat.osgi.exception
                        </Export-Package>
                        <Import-Package>*</Import-Package>
                    </instructions>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Step 3: Build it
mvn clean package

This produces a JAR file with the Java classes and interfaces and a META-INF/MANIFEST.MF .  Notice the Export-Package entry exports the classes in the com.jirawat.osgi.api and com.jirawat.osgi.exception as public and reusable by other bundles. The MANIFEST.MF looks like

Manifest-Version: 1.0
Bnd-LastModified: 1443617839644
Build-Jdk: 1.7.0_45
Built-By: juttayaya
Bundle-Description: Tutorial Bundle for api
Bundle-ManifestVersion: 2
Bundle-Name: jirawat.com :: tutorial :: api Bundle
Bundle-SymbolicName: com.jirawat.osgi.api
Bundle-Version: 1.0.0
Created-By: Apache Maven Bundle Plugin
Export-Package: com.jirawat.osgi.api;version="1.0.0",com.jirawat.osgi.exception;version="1.0.0"
Require-Capability: osgi.ee;filter:="(&(osgi.ee=JavaSE)(version=1.5))"
Tool: Bnd-3.0.0.201509101326

Step 4: Install it
Put the JAR file from step 3 in any directory and use the osgi:install JBoss Fuse command to install it

For example:
osgi:install -s file:/usr/local/osgi/api-1.0.0.jar

Note for ServiceMix and Karaf 3 and 4, use the bundle:install command

bundle:install -s file:/usr/local/osgi/api-1.0.0.jar

The osgi:list command shows the bundle installed. (bundle:list for ServiceMix and Karaf)

osgi:list

[ 268] [Active     ] [            ] [       ] [   80] jirawat.com :: tutorial :: api Bundle (1.0.0)

2015-09-29

How to build an offline Karaf server

What you will do:
  • Create an offline version of Apache Karaf
What you will learn:
  • Configure karaf-maven-plugin and maven-dependency-plugin
  • Override Karaf default behavior with Maven 
The default behavior of Apache Karaf is to download Java library dependencies as needed from Maven repositories such as http://repo1.maven.org/ . To perform this functionality, Karaf will use the Maven ~/.m2/settings.xml file and store dependencies in the ~/.m2/repository directory.

The confirm this behavior empirically
mv ~/.m2 ~/.m2_save

And disable internet access.
If you start a new version of Karaf and try to install any feature such as
feature:install jndi

you will get the error message
Error executing command: Can't install feature jndi/0.0.0: 
Error resolving artifact org.apache.xbean:xbean-naming:jar:3.18: Could not transfer artifact org.apache.xbean:xbean-naming:jar:3.18 from/to central (http://repo1.maven.org/maven2/): repo1.maven.org: nodename nor servname provided, or not known

If you turn internet access back on and rerun feature:install jndi, Karaf will create the directory ~/.m2/repository and put the library dependencies for Karaf jndi in that directory.

This may be undesirable behavior, especially in secured production, when servers typically cannot download artifacts freely from the internet.  From a developer perspective, it can be surprising that changing Maven settings.xml for another unrelated project can affect the runtime behavior of Karaf.

Follow the instructions below to create an offline version of Apache Karaf where the dependencies are preloaded at compile time and is not dependent on Maven.

This tutorial is based on Karaf 3.0.4 . The procedure for Karaf 2 and 4 are similar
Karaf 2: Coming Soon
Karaf 3: https://github.com/juttayaya/karaf/tree/master/karaf3/offline-karaf
Karaf 4: https://github.com/juttayaya/karaf/tree/master/karaf4/offline-karaf4

Step 1: Configure karaf-maven-plugin to generate an offline version of Karaf
Configure the setting ignoreDependencyFlag of karaf-maven-plugin to true. This configures the karaf-maven-plugin to download library dependencies into the directory ${karaf.home}/system
<plugin>
    <groupId>org.apache.karaf.tooling</groupId>
    <artifactId>karaf-maven-plugin</artifactId>
    <!-- Plugin requires at minimum 3.0.3 version for dependency=true bug fix
    https://issues.apache.org/jira/browse/KARAF-2596 -->
    <version>${karaf.plugin.version}</version>
    <extensions>true</extensions>
    <configuration>
      <karafVersion>${karaf.version}</karafVersion>
      <!-- ignoreDependencyFlag is true forces plugin to also
           download feature dependent libraries -->
      <ignoreDependencyFlag>true</ignoreDependencyFlag>
    </configuration>
</plugin>

Step 2: Add the Karaf features.xml you want to install as dependencies.
For example, to add camel to Karaf, add this dependency
<!-- https://repo1.maven.org/maven2/org/apache/camel/karaf/apache-camel/2.15.2/apache-camel-2.15.2-features.xml -->
<dependency>
    <groupId>org.apache.camel.karaf</groupId>
    <artifactId>apache-camel</artifactId>
    <version>${camel.version}</version>
    <classifier>features</classifier>
    <type>xml</type>
    <scope>runtime</scope>
</dependency>

Step 3:  Add the feature as bootFeatures or installedFeatures in karaf-maven-plugin
BootFeatures automatically begins on Karaf startup. InstalledFeatures just installs the library dependencies in the ${karaf.home}/system directory. The user will have to start it up manually via the command feature:install

For example, to start up camel at boot time but manually start up camel-quartz2
<bootFeatures>
    <feature>camel</feature>
</bootFeatures>
<installedFeatures>
    <feature>camel-quartz2</feature>
</installedFeatures>


Step 4: Add Maven repository http://svn.apache.org/repos/asf/servicemix/m2-repo
The dependency org.eclipse.equinox:region:jar:1.0.0.v20110506 for the "region" Karaf feature is not uploaded to the normal repo1.maven.org. Add this servicemix repository so Karaf can download the correct file
<repositories>
    <repository>
      <id>servicemix</id>
      <name>ServiceMix Repo for Karaf</name>
      <url>http://svn.apache.org/repos/asf/servicemix/m2-repo</url>
    </repository>
</repositories>

Step 5: Configure Karaf not to use external Maven
Create file src/main/resources/etc/org.ops4j.pax.url.mvn.cfg and add the following lines
org.ops4j.pax.url.mvn.settings=${karaf.home}/etc/karaf_maven_settings.xml
org.ops4j.pax.url.mvn.repositories=

This configures Karaf to not use the default Maven ~/.m2/settings.xml and not to try to download from any external Maven repositories like repo1.maven.org

See https://github.com/juttayaya/karaf/blob/master/karaf3/offline-karaf/src/main/resources/etc/org.ops4j.pax.url.mvn.cfg for a full example.

Create file src/main/resources/etc/karaf_maven_settings.xml with no Maven settings
<settings 
    xmlns="http://maven.apache.org/SETTINGS/1.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http:/maven.apache.org/SETTINGS/1.0.0 
        http://maven.apache.org/xsd/settings-1.0.0.xsd">
</settings>

See https://github.com/juttayaya/karaf/blob/master/karaf3/offline-karaf/src/main/resources/etc/karaf_maven_settings.xml for a full example

Step 6: Manually add any missing dependencies
Not all Karaf runtime dependencies are listed in features.xml (Probably a bug). We have to help offline Karaf by manually downloading the missing dependencies.

For example, https://repo1.maven.org/maven2/org/apache/karaf/features/standard/3.0.4/standard-3.0.4-features.xml is missing org.apache.karaf.jaas.boot.jar. To download it manually and put it in the ${karaf.home}/system directory, configure the maven-dependency-plugin
<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-dependency-plugin</artifactId>
    <executions>
      <execution>
        <id>copy</id>
        <phase>generate-resources</phase>
        <goals>
         <goal>copy</goal>
        </goals>
        <configuration>
         <artifactItems>
          <artifactItem>
          <groupId>org.apache.karaf.jaas</groupId>
          <artifactId>org.apache.karaf.jaas.boot</artifactId>
          <version>${karaf.version}</version>
          <outputDirectory>
          target/assembly/system/org/apache/karaf/jaas/org.apache.karaf.jaas.boot
          </outputDirectory>
          </artifactItem>
         </artifactItems>
        </configuration>
      </execution>
    </executions>
</plugin>



2015-09-28

How to uncompress tar.gz with one command

Problem:
I have a file called "thefiles.tar.gz" and I want to uncompress and unarchive in one Unix command.

Solution:
Use the command

tar xzpvf thefiles.tar.gz


Problem:
I have a directory called "thefiles" and I want to compress and archive it into a file called "thefiles.tar.gz" in one command.

Solution:
Use the command

tar czpvf thefiles.tar.gz thefiles

2015-09-21

Change Apache Karaf default logging options

What you will do:
  • Change the location of the Karaf log file
  • Change the Karaf log file name, size, and retention rate
  • Write Karaf system output to a log file
What you will learn:
  • Overwrite default Karaf configuration files
  • Create new directories in custom Karaf server
  • Set Karaf environment variables
Please see the post "Create a custom Apache Karaf server" for the initial setup.

Apache Karaf has poor initial logging configurations for production.  Below are instructions on how to change it.

Step 1: Overwrite default Karaf logging configuration file
The default Karaf logging configuration file is etc/org.ops4j.pax.logging.cfg . The default Karaf log settings is to write to data/log/karaf.log , with a 1MB file size limit and a 10 file retention. To overwrite, create the file src/main/resources/etc/org.ops4j.pax.logging.cfg .  We are changing the log file to logs/dekantar.log, with a daily rotating log. 

The contents of etc/org.ops4j.pax.logging.cfg should look like
# Root logger
log4j.rootLogger=INFO, daily, osgi:*
log4j.throwableRenderer=org.apache.log4j.OsgiThrowableRenderer

# Daily File appender
log4j.appender.daily=org.apache.log4j.DailyRollingFileAppender
log4j.appender.daily.layout=org.apache.log4j.PatternLayout
log4j.appender.daily.layout.ConversionPattern=%d{ISO8601} | %-5.5p | %-16.16t | %-32.32c{1} | %X{bundle.id} - %X{bundle.name} - %X{bundle.version} | %m%n
log4j.appender.daily.file=${karaf.home}/logs/dekantar.log
log4j.appender.daily.datePattern='.'yyyy-MM-dd


Step 2: Create the logs directory
Create the logs directory in /src/main/resources/logs.


Step 3: Create the Karaf system output logfile
In Karaf 3.0.4, the Karaf system output log file is defined in the file bin/setenv as the variable KARAF_REDIRECT . If the variable is not defined, then the system output goes to /dev/null . In previous versions of Karaf 3, the system output logfile was hardcoded as data/log/karaf.out . 

To change the system output logfile to logs/dekantar.out , create the file src/main/resources/bin/setenv 

The content of bin/setenv should look like
#!/bin/sh

ENVHOME=`dirname $0`
LOG_HOME=`cd $ENVHOME/..;pwd`
export KARAF_REDIRECT=${LOG_HOME}/logs/dekantar.out