Monday, August 24, 2015

Be aware of the count=0 when using Python's re.sub() to find and replace text

I have an xml file and I want to remove a whole section of element without actually parsing the XML file. I wrote an Python script to do just that. I first tested it out like this:

import re
input_text = open('my.xml').read()
result = re.findall(r'<library-ref>.*</library-ref>', input_text, re.S)
print(result)


The result IS what I wanted, so I thought I can quickly replace it with something like this:

result = re.sub(r'<library-ref>.*</library-ref>', '', input_text, re.S)

And to my surprise this does not work. It took me a while to notice that I mis used the method according to the documentation, which has this signature:

re.sub(pattern, repl, string, count=0, flags=0)

At first glace, I thought that count is already default to zero and I do not need to set it, but because that is a positional parameter, you must include it! So you suppose to call it explicit like this:

result = re.sub(r'<library-ref>.*</library-ref>', '', input_text, 0, re.S)

Or, if you like, you can use the "flags" parameter name explicitly without the odd looking zero.

result = re.sub(r'<library-ref>.*</library-ref>', '', input_text, flags=re.S)

So here is an example of flexible dynamic typing of optional parameters can bite if you are not careful.

Saturday, August 22, 2015

View and terminate Oracle Database user sessions

Verify sessions:
SELECT USERNAME FROM V$SESSION;

SELECT USERNAME, SID, SERIAL#, STATUS
  FROM V$SESSION
  WHERE USERNAME like 'SCOTT%';

Generate SQL to terminate sessions:
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';'
  FROM V$SESSION
  WHERE USERNAME like 'SCOTT%';

Wednesday, August 19, 2015

How to import a Oracle database dump file

Here is an example on how to import a Oracle database dump file (a binary file that's exported from a Oracle database using the Oracle data pump utility). For this instruction example, let's say I was given a myapp.dmp.gz file.

NOTE: If you do not use the same db user/tablespace for export and import, then you need to know its names used during export to perform the remapping during import.

Step 1: If you don't have an Oracle database instance to work with, create one.

Step 2: Create a new db user in a Oracle database instance. The easiest way to do this is to use the SqlDeveloper tool and use the "Create New User" dialog prompt. (For this example, I will call my new user MYAPP08182015, password Password1, and tablespace named MY_TABLE_SPACE.)

Step 3: Now you have a new db User (Schema) and password, you may start the data import using the command line prompt. Ssh into the database server where you can find the impdp command utility.

scp myapp.dmp.gz zemian@mydbserver:/tmp
ssh zemian@mydbserver
cd /tmp
gunzip myapp.dmp.gz
/app/oracle/dbhome/bin/impdp MYAPP08182015/Password1 REMAP_SCHEMA=MYAPP:MYAPP08182015 REMAP_TABLESPACE=USERS:MY_TABLE_SPACE LOGFILE=/tmp/myapp.dmp.log DUMPFILE=/tmp/myapp.dmp

References:
http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_overview.htm

Tuesday, August 18, 2015

How to check Oracle Database tablespace

When creating a new users in Oracle database (new schema), you need to verify the existing tablespace availability. This query will show you what's there and how much space are free to use.

SELECT df.tablespace_name "Tablespace",
  totalusedspace "Used MB",
  (df.totalspace - tu.totalusedspace) "Free MB",
  df.totalspace "Total MB",
  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "% Free"
FROM
  (SELECT tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace
  FROM dba_data_files
  GROUP BY tablespace_name
  ) df,
  (SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
    tablespace_name
  FROM dba_segments
  GROUP BY tablespace_name
  ) tu
WHERE df.tablespace_name = tu.tablespace_name;

Also, this query will show where the tablespace file are located:

SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME
   FROM DBA_DATA_FILES;

Here are some references on how Oracle manages user, schema and tablespace.



Thursday, July 23, 2015

How to manage SQL statements more effectively with Java

If you work with plain Java JDBC without any external libraries, you will need to manage your own SQL statements. Unfortunately Java String does not support muti-lines construct, and you have to use many "quotes" + "concatenation" and makes the SQL very hard to read and manage. This makes it hard to maintain and test (try to copy a SQL from Java code into your SQL client). It would be so nice to keep the entire SQL block of text intact without these Java noise.

Here is a solution. Store your SQL queries in XML inside CDATA:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<sqlMap>
    <sqls>
        <entry>
            <key>getUser</key>
            <value><![CDATA[
SELECT *
FROM USERS
WHERE ID = ?
            ]]></value>
        </entry>
        <entry>
            <key>getSpecialCodeByUserId</key>
            <value><![CDATA[
SELECT u.EMAIL, p.ID as PROFILEID, p.SPECIALCODE, a.MANAGERID
FROM USERS u
  LEFT JOIN PROFILE p ON p.USERID = u.ID
  LEFT JOIN ACCOUNT a ON a.PROFILEID = p.ID
WHERE u.ID = ?  ]]></value>
        </entry>  </sqls>
</sqlMap>

Now you just need to read it. One way to do this is with built-in JAXB

import javax.xml.bind.annotation.XmlRootElement;
import java.util.HashMap;
import java.util.Map;

@XmlRootElement
public class SqlMap {
    Map<String, String> sqls = new HashMap<>();

    public Map<String, String> getSqls() {
        return sqls;
    }

    public void setSqls(Map<String, String> sqls) {
        this.sqls = sqls;
    }

    public String getSql(String name) {
        return sqls.get(name);
    }

    public static SqlMap load(String name) throws Exception {
        InputStream inStream = Thread.currentThread().getContextClassLoader().getResourceAsStream(name);
        SqlMap sqlMap = JAXB.unmarshal(inStream, SqlMap.class);
        return sqlMap;
    }
}


Another way is to simply use java.util.Properties#loadFromXML by following their schema DTD. Here is an example of XML:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <comment>XML Props</comment>
    <!-- Foo entry -->
    <entry key="foo">bar</entry>
    <!-- Query entry -->
    <entry key="query">
        <![CDATA[
                SELECT * FROM USERS
        ]]>
    </entry>
</properties>

Both of these are built-in from your JDK!

Saturday, June 13, 2015

Java RegEx: How to replace all with pre-processing on a captured group

Need to replace all occurances of a pattern text and replace it with a captured group? Something like this in Java works nicely:

       
        String html = "<a href='myurl?id=1123'>myurl</a>\n" +
                "<a href='myurl2?id=2123'>myurl2</a>\n" +
                "<a href='myurl3?id=3123'>myurl3</a>";
        html = html.replaceAll("id=(\\w+)'?", "productId=$1'");


Here I swapped the query name from "id" to "productId" on all the links that matched my criteria. But what happen if I needed to pre-process the captured ID value before replacing it? Let's say now I want to do a lookup and transform the ID value to something else?

This extra requirement would lead us to dig deeper into Java RegEx package. Here is what I come up with:

import java.util.regex.*;
...
    public String replaceAndLookupIds(String html) {
        StringBuffer newHtml = new StringBuffer();
        Pattern p = Pattern.compile("id=(\\w+)'?");
        Matcher m = p.matcher(html);
        while (m.find()) {
            String id= m.group(1);
            String newId = lookup(id);
            String rep = "productId=" + newId + "'";
            m.appendReplacement(newHtml, rep);
        }
        m.appendTail(newHtml);
        return newHtml.toString();
    }

Saturday, June 6, 2015

Example of plain Jdbc Support Class

Need to fetch some data from DB quickly with Java? Here is a quick example of plain JDBC JdbcSupport Class to help you.

package zemian.jdbcexample

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public abstract class JdbcSupport {
    private static final Logger LOGGER = LoggerFactory.getLogger(JdbcSupport.class);

    protected DataSource dataSource;

    public JdbcSupport(DataSource dataSource) throws Exception {
        this.dataSource = dataSource;
    }

    protected Row toRowMap(ResultSet rs) throws Exception {
        Row row = new Row();
        ResultSetMetaData meta = rs.getMetaData();
        int count = meta.getColumnCount();
        for (int i=1; i <= count; i++) {
            row.map.put(meta.getColumnName(i), rs.getObject(i));
        }
        return row;
    }

    protected List<Row> queryRowList(Connection conn, String sql, Object... params) throws Exception {
        LOGGER.debug("Query sql={}, params={}", sql, Arrays.asList(params));
        List<Row> result = new ArrayList<>();
        try(PreparedStatement st = conn.prepareStatement(sql)) {
            for (int i = 1; i <= params.length; i++) {
                st.setObject(i, params[i -1]);
            }
            try (ResultSet rs = st.executeQuery()) {
                while(rs.next()) {
                    result.add(toRowMap(rs));
                }
            }
        }
        return result;
    }

    protected Row queryRow(Connection conn, String sql, Object... params) throws Exception {
        List<Row> rowList = queryRowList(conn, sql, params);
        if (rowList.size() != 1) {
            throw new RuntimeException("No unique result from query.");
        }
        return rowList.get(0);
    }
   
    public static class Row {
        public Map<String, Object> map = new HashMap<>();

        public <T> T get(String name) {
            T result = (T)map.get(name);
            return result;
        }

        public <T> T get(String name, T defVal) {
            T result = (T)map.get(name);
            if (result == null)
                return defVal;
            return result;
        }
    }
}


To use it, one may do something like this:

public class AccountStore extends JdbcSupport {
    public AccountStore(DataSource dataSource) {
        super(dataSource);
    }
   
    public void printAccountProfiles() {
        try(Connection conn = dataSource.getConnection()) {
            String sql = "SELECT ACCOUNTID FROM USERS WHERE USERNAME=?";
            Row row = queryRow(conn, sql, "zemian");
           
            String accountId = row.get("ACCOUNTID");
            String department = "IT";
            sql = "SELECT * FROM ACCOUNTPROFILES WHERE ID=? AND DEPARTMENT=?";
            List<Row> rowList = queryRowList(conn, sql, accountId, department);
           
            for (Row row : rowList) {
                System.out.println("Got profile: " + row.map);
            }
        }
    }
}