Simple XML parser + SQL Generation

Problem: Quick way to parse an XML stream from a website and generate a SQL script to populate a MSSQL table.

Solution:

import java.io.InputStream;
import java.net.URL;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class XMLReader {

public static void main(String argv[]) {

try {
URL url = new URL("http://www.treasurydirect.gov/xml/CPI_20090814.xml");
InputStream is = url.openStream();

DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
DocumentBuilder db = dbf.newDocumentBuilder();
Document doc = db.parse(is);
doc.getDocumentElement().normalize();
NodeList dailyIndexes = doc.getElementsByTagName("DailyIndexRatio");

/*
* <DailyIndexRatio>
*   <CUSIP>912810FD5</CUSIP>
*   <IssueDate>1998-04-15</IssueDate>
*   <Date>2009-09-01</Date>
*   <RefCPI>215.69300</RefCPI>
*   <IndexRatio>1.33358</IndexRatio>
* </DailyIndexRatio>
*/

for (int s = 0; s < dailyIndexes.getLength(); s++) {
    Node currentIndexNode = dailyIndexes.item(s);
    if (currentIndexNode.getNodeType() == Node.ELEMENT_NODE) {
    // DailyIndexRatio Element
    Element currentIndexElement = (Element) currentIndexNode;

    // List of CUSIP Nodes
    NodeList cusipNodeList = currentIndexElement.getElementsByTagName("CUSIP");
    // CUSIP Element (1st one)
    Element cusipElmnt = (Element) cusipNodeList.item(0);
    // List of child nodes for CUSIP Element
    NodeList cusipChildNodes = cusipElmnt.getChildNodes();
    // Get the value of the 1st node (Text of CUSIP Element)
    //((Node) cusipChildNodes.item(0)).getNodeValue()

    NodeList dateNodeList = currentIndexElement.getElementsByTagName("Date");
    Element dateElmnt = (Element) dateNodeList.item(0);
    NodeList dateChildNodes = dateElmnt.getChildNodes();

    NodeList indexRatioNodeList = currentIndexElement.getElementsByTagName("IndexRatio");
    Element indexRatioElmnt = (Element) indexRatioNodeList.item(0);
    NodeList indexRatioChildNodes = indexRatioElmnt.getChildNodes();

    // SQL SCRIPT GENERATION
    System.out.print("INSERT INTO [CPIData] (Date, CUSIP, CPI) VALUES ('"
    + ((Node) dateChildNodes.item(0)).getNodeValue() + "','");
    System.out.print(((Node) cusipChildNodes.item(0)).getNodeValue() + "',");
    System.out.println(((Node) indexRatioChildNodes.item(0)).getNodeValue() + ")");
    }

}
} catch (Exception e) {
    e.printStackTrace();
}
}
}

Basically, I just needed the Consumer Price Indexes (CPI) that needs to be applied to a particular security (TIPS).
This simple java app returns (console) the SQL script that we need to execute in order to have the CPI’s stored in the DB.

Note: This code has been tested using Eclipse 3.3 and JDK 6.

Example of Partitioned View by Date

Let’s begin with the creation of the partitioned tables:


IF EXISTS (
        SELECT *
        FROM dbo.sysobjects
        WHERE NAME = 'PartitionedTable1' )
            DROP TABLE [dbo].[PartitionedTable1]
GO
IF EXISTS (
        SELECT *
        FROM dbo.sysobjects
        WHERE NAME = 'PartitionedTable2' )
            DROP TABLE [dbo].[PartitionedTable2]
GO

-- TABLE CREATION
CREATE TABLE [dbo].[PartitionedTable1](
    ID INT NOT NULL,
    Date DATETIME NOT NULL CHECK (Date BETWEEN '20090101' AND '20090131'), -- Partitioned Column Check !
    Field1 VARCHAR(25) NULL,
    Field2 VARCHAR(25) NULL,
    CONSTRAINT [PT1_PK] PRIMARY KEY CLUSTERED (ID,Date)
)
CREATE TABLE [dbo].[PartitionedTable2](
    ID INT NOT NULL,
    Date DATETIME NOT NULL CHECK (Date BETWEEN '20090201' AND '20090228'), -- Partitioned Column Check !
    Field1 VARCHAR(25) NULL,
    Field2 VARCHAR(25) NULL,
    CONSTRAINT [PT2_PK] PRIMARY KEY CLUSTERED (ID,Date)
)


Some Notes:
* Partitioned field (Date) should be part of the PK of the table.
* The CHECK constraint can only use these operators:BETWEEN, AND, OR, <, <=, >, >=, =.

Now, let’s create the view for our recently created partitioned tables:

-- VIEW
IF EXISTS (
    SELECT *
    FROM dbo.sysobjects
    WHERE id = OBJECT_ID(N'[dbo].[PartitionedView]') AND type in (N'V', N'PC'))
        DROP VIEW [dbo].[PartitionedView]
GO

CREATE VIEW [dbo].[PartitionedView]
AS
    SELECT *
    FROM [dbo].[PartitionedTable1]
    UNION ALL
    SELECT *
    FROM [dbo].[PartitionedTable2]


voilà! Let’s run a simple test:

-- TEST
INSERT INTO [dbo].[PartitionedView] (ID,Date,Field1,Field2) VALUES (1,'01/05/2009','Value1','Value2')
INSERT INTO [dbo].[PartitionedView] (ID,Date,Field1,Field2) VALUES (2,'02/10/2009','Value3','Value4')
INSERT INTO [dbo].[PartitionedView] (ID,Date,Field1,Field2) VALUES (3,'01/12/2009','Value5','Value6')
INSERT INTO [dbo].[PartitionedView] (ID,Date,Field1,Field2) VALUES (4,'03/12/2009','Value7','Value8') -- This one FAILS because the date in the new row is not considered by any of the conditions


and check our partitined tables.

SELECT * FROM [dbo].[PartitionedTable1]
SELECT * FROM [dbo].[PartitionedTable2]

You should get something like this:

Enjoy.

MySQL, change column encoding

I was having a problem when using characters with accents in a web application. After spending some time investigating the issue, I found out that the problem was in the default encoding applied by MySQL in the tables. I solved the issue using the following command to change the encoding of the desired column (I’ll convert the entire DB in the future):

UPDATE [Table] SET [column] = CONVERT(CONVERT(CONVERT([column] USING latin1) USING binary) USING utf8);

Note that It converts the data first from latin1 to binary and then from binary to utf8.

Now the web application is showing the appropriate characters.

MySql Database Backup & E-Mail (2)

Well, now that we have the email script working (at least it Worked for me), let’s create a job using cron to execute it on a regular basis.

First of all, I created a new cron file under /etc/cron.d/ called dbBackup.
The file contains a simple cron expression:

0 20 * * 5 root /etc/init.d/dbBackup &

Basically, this will:

Execute the command /etc/init.d/dbBackup

The execution will be as a background process (&)

The schedule will be every friday at 8:00pm

The user that will execute it will be root.

In this case, /etc/init.d/dbBackup is the script that I posted previously to make a backup of the MySql database.
I’ve just received the first scheduled email an hour ago!

For more info on cron syntax, you can visit this site.