Converting a year end month to a date in the current year.

 Say we have a company's year end stored as a month field in a column in a database. The aim is to convert this integer year end month to a date in the current year.

For example:
The year end of company ABC is February. This is represented as a 2 in the database. The goal is to convert this to a date in the current year. So if it is 2011 then the aim is to generate the date, 2011-02-28. The next year the date should be 2011-02-28 etc.

The query:

select dateadd(day,-1, dateadd(month,1, convert(datetime,convert(char(4),year(getdate()))+'-'+convert(varchar,c.YEM)+'-'+'1'))) from (select 2 as YEM) c

Enjoy.

Referential Integrity in xml

In this post I will try to explain a very useful feature of xsd; the ability to detect referential integrity constraint violations. Let's start of with some simple xml :

<root>

  <type name="A"/>

  <type name="B"/>

  <type name="C"/>

  <item type="C"/>

  <item type="A"/>

</root>

And the xsd defining this xml :

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <xsd:complexType name="type">

    <xsd:attribute name="name" type="xsd:string"/>

  </xsd:complexType>

  <xsd:complexType name="item">

    <xsd:attribute name="type" type="xsd:string"/>

  </xsd:complexType>

  <xsd:element name="root">

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="type" minOccurs="0" maxOccurs="unbounded" type="type"/>

        <xsd:element name="item" minOccurs="0" maxOccurs="unbounded" type="item"/>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

</xsd:schema>

Again nothing new. At this stage, we have a definition in xsd format which can be used to test xml for conformance to our schema. However, any validation would be strictly structural. If we wanted to validate things like whether the items have type attributes which have been defined as type elements, or that there is only one type of element for each name, then we need something more than just the above xsd. In short, we need a definition which would encapsulate the fact that the following xml should be invalid (since the type D is not defined, and since type C is defined twice):

<root>

  <type name="A"/>

  <type name="B"/>

  <type name="C"/>

  <type name="C"/>

  <item type="D"/>

</root>

This can very simply be done by using the unique,key and keyref  xsd elements. Here is the xsd after the required modifications:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <xsd:complexType name="type">

    <xsd:attribute name="name" type="xsd:string"/>

  </xsd:complexType>

  <xsd:complexType name="item">

    <xsd:attribute name="type" type="xsd:string"/>

  </xsd:complexType>

  <xsd:element name="root">

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="type" minOccurs="0" maxOccurs="unbounded" type="type"/>

        <xsd:element name="item" minOccurs="0" maxOccurs="unbounded" type="item"/>

      </xsd:sequence>

    </xsd:complexType>

    <xsd:unique name="unique_type_constraint">

      <xsd:selector xpath="type"/>

      <xsd:field xpath="@name"/>

    </xsd:unique>

    <xsd:key name="typeKey">

      <xsd:selector xpath="type"/>

      <xsd:field xpath="@name"/>

    </xsd:key>

    <xsd:keyref name="type_constraint" refer="typeKey">

      <xsd:selector xpath="item"/>

      <xsd:field xpath="@type"/>

    </xsd:keyref>

  </xsd:element>

</xsd:schema>

What we are doing is defining a 'lookup' using the key element. The key is defined by selecting the name attribute from the type elements under the root element. This 'lookup' is then linked to the type attributes of the items under root via the keyref element. Thus, restricting the values of the type attributes of the item element to the values found int the name attributes of the type elements.

The unique element is used to apply a uniquenesses constraint over the values of name attributes of the type elements.

I have written a simple Python script to test the above concepts. This script can be found here. Please note that you will have to install lxml in order to run the script.

Writing a Python xinetd Server

Yesterday I discovered the xinetd; an internet services daemon. I immediately liked the idea of writing simple services that work on stdin and stdout but that can be accessed over the internet via sockets. So, I set out to write a simple Python server that can be integrated with xinetd. Here is the server:

#!/usr/bin/python
import sys

request = ''
while True:
  data = sys.stdin.readline().strip()
  request = request + data + '<br>'
  if data == "":
    print 'HTTP/1.0 200 OK'
    print ''
    print '<html><body><p>'+request+'</p></body></html>'
    sys.stdout.flush()
    break;

I am assuming that a web browser will connect to my server, the server will then 'echo' the request back to the browser allowing the  browser to display the request. As you can see the input is received via stdin and output is returned via stdout.

If xinetd is not already installed then you will obviously have to install it first. Since I am doing this on Ubuntu the following works for me:

sudo apt-get install xinetd

After installing xinetd you need to create a config file for the service. I called my service http_echo and my config file (located in /etc/xinetd.d) is named similarly; http_echo. My configuration file looks like this:

service http_echo
{
    protocol       = tcp
    disable        = no
    port           = 9991
    flags          = REUSE
    socket_type    = stream
    wait           = no
    user           = johan
    server         = /home/johan/code/http_echo/http_echo
    log_on_failure += USERID
}

Most of this file is quite self explanatory. Please refer to the xinetd documentation for more information.The port property should make the service run on the specified port without having to add an entry in the services file (/etc/services) . I have had to add an entry in my services file to make this setup work:

http_echo    9991/tcp

Then simply restart the xinetd service:

sudo /etc/init.d/xinetd restart

Pointing a browser to the server on the specified port (9991), will yield the pleasing results below:

GET / HTTP/1.1
Host: localhost:9991
Connection: keep-alive
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US) AppleWebKit/532.0 (KHTML, like Gecko) Chrome/3.0.195.33 Safari/532.0
Cache-Control: max-age=0
Accept: application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5
Accept-Encoding: gzip,deflate,sdch
Accept-Language: en-US,en;q=0.8
Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.3

And that is how simple it is to write a service in Python that runs under xinetd.

Simple Input Box Style

Whenever I browse the web I usually keep my eyes open for interesting styles or style components. Today I found a very nice looking input box; here. Here is my reproduction:

Please type here :

Here is the style for the above text box:

.simple
{
              border-width: 0 0 1px;
              border-style:none none solid;
              background:transparent;
              border-color:black; 
}

Anyway, I like the look of this simple input box and will be using it more.

Convenient Python Command Runner

Here is a little piece of code which I tend to reuse quite often. It basically runs a command and return True if the command executed successfully or False if it did not. The second item in the return tuple is the stdout output of the command if any or the error if there was an error executing the command.

import subprocess

def runcmd(cmd):
  try:
    f = subprocess.Popen(cmd,shell=False,stdout=subprocess.PIPE,stderr=subprocess.PIPE)
    stdout = f.stdout.read();
    stderr = f.stderr.read();
   
    if f.stdout.close() == None and  f.stderr.close() == None:
      return True,stdout,stderr
    else:
      return False,stdout,stderr
  except OSError,x:
    return False,x

As usual the source code can be found here.