GarciaPL Notes - Java and Others

If constraint on column STATUS was created without a name during creating a table, Oracle will assign a random name for it. Unfortunately, we cannot modify the constraint directly.

Steps involved of dropping unnamed constraint linked to column STATUS

1. Duplicate STATUS field into a new field STATUS2
2. Define CHECK constraints on STATUS2
3. Migrate data from STATUS into STATUS2
4. Drop STATUS column
5. Rename STATUS2 to STATUS

ALTER TABLE MY_TABLE ADD STATUS2 NVARCHAR2(10) DEFAULT 'OPEN';
ALTER TABLE MY_TABLE ADD CONSTRAINT MY_TABLE_CHECK_STATUS CHECK (STATUS2 IN ('OPEN', 'CLOSED'));
UPDATE MY_TABLE SET STATUS2 = STATUS;
ALTER TABLE MY_TABLE DROP COLUMN STATUS;
ALTER TABLE MY_TABLE RENAME COLUMN STATUS2 TO STATUS;

Recently, I was trying to move some files, mostly .java's and.sql's, from one maven module into another one. We were just trying to merge two modules into one module, because of we thought that keeping two modules with pretty the same functionality is odd.

The main point around this movement was to preserve the history of changes made to those files in the past. Everything was fine until we tried to deploy the application on the server. Of course, I forgot to mention that we were using Flyway as a database migration tool. Unexpectedly, one of the file's checksum was not the same as the checksum saved in the database for that SQL file (flyway_schema_history table). We were astonished because of the content of the file did not change at all... but Pull Request on BitBucket was saying that there is a difference. We checked the MD5 for that file before and after the move, it was the same, but for some reason the checksum detected by Flyway was different! To be honest, we spent a lot of time trying to understand why it's happening, and then I found this post made by Richard Tuin [1].

The solution was just to revert moving of those SQL files up to the previous location and try to create a file called .gitattributes. We defined over there just a single line which was disabling CRLF for that single file for which checksum was not the same as this one stored in flyway schema history table. After moving SQL files once again, using git mv [2] command and uploading results on BitBucket, everything was fine!

The content of .gitattributes for that single file should look as below :

fileName.sql -crlf


References :
[1] Richard Tuin - How to make git ignore different line endings
[2] Git mv
[3] Git attributes

I was trying recently to add unique constraint on few fields on Oracle. At the beginning I thought that it might be a easy task to do, but at the end some workaround was needed. So, in case of error you might get below message which indicates that Oracle found some duplicate records in your table, even though you specify NOVALIDATE flag which allows you to do not check existing data against that constraint.

ALTER TABLE MY_TABLE ADD CONSTRAINT MY_UNIQUE_CONSTRAINT UNIQUE (ID, CUSTOM_ID, VALUE, UNITS, AMOUNT, ITEM_ID, DATE) NOVALIDATE
Error report -
SQL Error: ORA-02299: cannot validate (DB.MY_UNIQUE_CONSTRAINT) - duplicate keys found
02299. 00000 - "cannot validate (%s.%s) - duplicate keys found"
*Cause:    an alter table validating constraint failed because the table has
           duplicate key values.
*Action:   Obvious

CREATE INDEX INDEX_UNIQUE_CONSTRAINT ON MY_TABLE (ID, CUSTOM_ID, VALUE, UNITS, AMOUNT, ITEM_ID, DATE);
ALTER TABLE MY_TABLE ADD CONSTRAINT MY_UNIQUE_CONSTRAINT UNIQUE (ID, CUSTOM_ID, VALUE, UNITS, AMOUNT, ITEM_ID, DATE) NOVALIDATE;

Some of you might thinking how to create sequence for table which contains some records. You might always export all records from database, then eventually create sequence and after all import those records once again into database. Nevertheless there is a solution how to create this sequence using PL/SQL.

DECLARE
  seq_value_holder        NUMBER;
BEGIN
  -- create a sequence for YOUR_TABLE
  SELECT NVL(MAX(id), 0) + 1
  INTO seq_value_holder
  FROM YOUR_TABLE;
  EXECUTE immediate 'create sequence SEQ_YOUR_TABLE start with ' || seq_value_holder || ' increment by 1';
END;

I had a chance to work with Spring Test DBUnit [1] (integration between Spring testing framework and DBUnit) with few tests related with Spring Integration. Most of them use the same common text context which contains embedded database H2. Funny thing was that some tests failed, because of some tables already exist. The issue is that embedded database is not cleared between tests and it is reused within the same context. That's why you should use @DirtiesContext(classMode = DirtiesContext.ClassMode.AFTER_CLASS) with @TestExecutionListeners({DependencyInjectionTestExecutionListener.class, DirtiesContextTestExecutionListener.class}) like below :

@ContextConfiguration
@RunWith(SpringJUnit4ClassRunner.class)
@TestExecutionListeners({DependencyInjectionTestExecutionListener.class, DirtiesContextTestExecutionListener.class})
@DirtiesContext(classMode = DirtiesContext.ClassMode.AFTER_CLASS)
public class YourTest {

}

At this moment I am looking for some Java library for SQL-DSL. I have two requirements for this library. First of all it must be able to build queries using Builder pattern. Second requirement is that it should be available in Maven central repository. I must to add that I am not looking for some bigger framework to do it, but some small tool to build those queries dynamically. I found very good library called jOOQ. Unfortunately it will apply overhead in my project. I found library called sql-dsl from kantega [2]. Unfortunately it is not available in Maven repository. Additionally I found that this library does not support distinct statement (maybe owner forgot about this...). Nevertheless there was perfect time and place for me to make pull request with hotfix on GitHub [1]. I hope that owner of this library will apply those changes and put this library in Maven repository that it will be more accessible for me and other developers.


Reference :
[1] https://github.com/GarciaPL/sql-dsl
[2] https://github.com/kantega/sql-dsl

Some time ago I have solved another technical test for one company. As the same before this test was performed for recruitment process.

This application is dedicated for tellers which provides functionalities like :

• Spring Beans – 4.0.1.RELEASE
• Spring Tx – 4.0.1.RELEASE
• Spring Context – 4.0.1.RELEASE
• Spring Context Support – 4.0.1.RELEASE
• Spring Orm – 4.0.1.RELEASE
• Spring Jdbc – 4.0.1.RELEASE
• Spring Web – 4.0.1.RELEASE
• Spring Web MVC – 4.0.1.RELEASE
• Spring Test - 4.0.1.RELEASE
• Joda Money - 0.10.0
• Jackson Core - 2.5.0
• Jackson Databind - 2.5.0
• Jackson Annotations - 2.5.0
• Javax Servlet API – 3.1.0
• JSTL – 1.2
• Hibernate Core - 4.3.5.Final
• Hibernate Entitymanager - 4.3.5.Final
• HSQLDB - 2.3.3
• SLF4J - 1.7.8
• Commons Logging - 1.2
• JUnit - 4.10
• Mockito - 1.9.5
• Hamcrest - 1.3
• Twitter Bootstrap - 3.3.4
• DataTables - 1.10.7
• jqBootstrapValidation - 1.3.6

Home screen

Create new account

Deposit money

Transfer money

Transactions

Some of you have faced the problem when SQL Developer on Ubuntu which just freeze after some idle time. More information about this issue and solution can be found under [1]. I must to admit that this solution is quite complex, so I decided to prepare some very simple tool called SQLDeveloper Killer. Below I paste some source code in Java which aims to list all processes which are alive in system and find particular process which corresponds to SQL Developer process (contains "sqldeveloper.conf" in CMD description).

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package killsqldeveloper;

import java.io.BufferedReader;
import java.io.InputStreamReader;

/**
 *
 * @author lciesluk
 */
public class KillSQLDeveloper {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        try {
            Process exec = Runtime.getRuntime().exec(new String[]{"ps", "-ef"});
            exec.waitFor();

            BufferedReader reader
                    = new BufferedReader(new InputStreamReader(exec.getInputStream()));

            String line = "";
            while ((line = reader.readLine()) != null) {
                if (line.contains("sqldeveloper.conf")) {
                    System.out.println(line);
                    String[] split = line.split(" ");
                    if (split.length > 2) {
                        System.out.println(split[1]);
                        if (!split[1].equals("")) {
                            Process execKill = Runtime.getRuntime().exec(new String[]{"kill", "-9", split[1]});
                            execKill.waitFor();
                        } else {
                            Process execKill = Runtime.getRuntime().exec(new String[]{"kill", "-9", split[2]});
                            execKill.waitFor();
                        }
                    }
                    break;
                }
            }

        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
}

After you paste this code to your IDE, you can install very useful tool called alacarte to create Unity launcher on Ubuntu.

SQL Developer Killer

Reference : [1] Alexeymoseyev.wordpress.com - Oracle SQL Developer hangs on Linux [2] Pastebin.com - Source Code [3] Alacarte - Ubuntu menu editor

If you have difficulties with running SQL Developer (in my case 4.0.2.15.21) from Oracle in console on Linux you can see such stack trace :

# A fatal error has been detected by the Java Runtime Environment:
#
#  SIGSEGV (0xb) at pc=0x00007feb22ed8be0, pid=32116, tid=140649711318784
#
# JRE version: Java(TM) SE Runtime Environment (7.0_65-b17) (build 1.7.0_65-b17)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (24.65-b04 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C  0x00007feb22ed8be0
#
# Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java again
#
# An error report file with more information is saved as:
# /home/lukasz/sqldeveloper/sqldeveloper/bin/hs_err_pid32116.log
#
# If you would like to submit a bug report, please visit:
#   http://bugreport.sun.com/bugreport/crash.jsp
#
/home/lukasz/sqldeveloper/sqldeveloper/bin/../../ide/bin/launcher.sh: line 1193: 32116 Przerwane               (core dumped) ${JAVA} "${APP_VM_OPTS[@]}" ${APP_ENV_VARS} -classpath ${APP_CLASSPATH} ${APP_MAIN_CLASS} "${APP_APP_OPTS[@]}"

SQLDeveloper.sh

I would like only to announce that I have created a small application in Android called Oracle DB 11g Errors Guide. This application helps you as a developer or administrator of Oracle ® Database 11g Release 1 find solutions of problems which you are faced with during your work. Each error message contains the message statement, an explanation of the probable causes and a recommended action.

More information about Oracle ® Database 11g Release 1 Error Messages you can find under hyperlink - Oracle® Database Error Messages 11g Release 1 (11.1) Documentation

Hyperlink to Google Play Store - Google Play Store GarciaPL

A few days ago I found very useful solution (thanks for Biomathematicus!) for deleting tables from a MySQL schema. I know that it could be quite difficult for some developers (also for me) to manually remove every table from schema without removing this schema itself. So, there is a two-step solution which is going to help you removing a lot of tables from SQL database.

1) List all tables in the MySQL schema :

SELECT CONCAT('drop table ',table_name,'; ')
FROM information_schema.tables
WHERE table_schema = 'yourDatabaseName';

drop table table1; 
drop table table2;

I must say that this one diagram of SQL Joins is one of the most memorizing image about how joins in sql works. Enjoy :)

SQL Joins Explain

In last few days I had a problem with some SQL queries in Oracle database. I won't give you this particular SQL query which has ended with error which you can find in the header of this post, but i will give solution! :)

Suppose that your SQL queries use data in format for instance YYYY-MM-DD, so before them execute below command which specifies the default date format which can be used in TO_CHAR and TO_DATE functions :

alter session  set NLS_DATE_FORMAT='YYYY-MM-DD';

I used to find many times duplicates in my table in MySQL database this time. First of all, I will paste here my table structure below (yes i know that it is difficult to read) :

mysql> describe proxy_parser_statistics;
+-----------------------+---------+------+-----+---------+----------------+
| Field                 | Type    | Null | Key | Default | Extra          |
+-----------------------+---------+------+-----+---------+----------------+
| ID                    | int(11) | NO   | PRI | NULL    | auto_increment |
| file_name             | text    | NO   |     | NULL    |                |
| processing_stamp_date | date    | YES  |     | NULL    |                |
| processing_stamp_time | time    | YES  |     | NULL    |                |
| processed_records     | int(11) | YES  |     | NULL    |                |
+-----------------------+---------+------+-----+---------+----------------+

I just use this query :

SELECT a.id, a.file_name FROM proxy_parser_statistics a INNER JOIN proxy_parser_statistics b ON a.file_name = b.file_name WHERE a.id <> b.id