Recently in Learning Category

We came across a need to save just the Time in the database, the requirement is to store time of the day, like say the user likes to have Breakfast at 8.15AM and Lunch at 12.32PM etc. Off course oracle does not have a time only data type. So we ended up using DATE as the data type and just setting the time. for example:

CREATE TABLE FOO (PREFERRED_TIME DATE NULL);
INSERT INTO FOO (TO_DATE('11:34','HH24:MI'));

SELECT TO_CHAR(PREFERRED_TIME, 'dd-mon-yyyy hh24:mi:ss') from FOO;
-----------------------
01-feb-2009 11:24:00

on the client side you will have to know to ignore the date component, this is possible area for confusion in future, since I could have many preferences over different months and my preferred time would have different date components, ideally I would just want the time, otherwise I would think having a time component and the date being a constant known value like 01/01/0001. We could achieve this using a BEFORE INSERT/UPDATE trigger which keeps the time component but updates the date component to a known constant value or you can also use your OR mapping layer like hibernate to set the value as such. If you are using hibernate, you can map the filed using the java.sql.Time object and the date is automatically set to 01/01/1970. SqlServer 2008 seems has a Time data type.

Couple of weeks back I was given a choice to upgrade my work Laptop to a Mac Book Pro or a Windows Laptop. I choose Mac ( I know everyone is into macs nowadays). The transition was pretty good, with the exception of moving my oracle database from windows to mac, since there is no native installation of oracle on mac I had to use VMWare fusion to install oracle.

After having oracle run inside the VM, I started setting up the dev environment, I ran into issues with ANT version and JAVA version but they where resolved by pointing to the right location.

Overall this has been a positive experience so far.

I'm always on the lookout for better tool support to do database refactoring. Just noticed that LiquiBase has come out with a IntelliJ plugin to support database refactoring.

This is really cool and hopefully one of long list of tools that will support database refactoring in the future. so enjoy

We had a weird requirement on our project recently..

Find all the Rows in All the tables that do not comply with the Constraints that we have in development but not in QA environments

Best way to do this we thought was to write a SQL statement against the table for each column that was going to have a Foreign Key constrained column and find out what data was not right or did not match the constraint. For example: If we have a INVOICE table that has a ITEMID on it. I want to find all the rows in the INVOICE table that have a ITEMID that does not exist in the ITEM table. Writing this SQL for our 400+ tables database was going to be huge task.

Oracles (or for that matter any databases metadata) metadata to the rescue and we ended up writing a SQL that would generate our above SQL.

here is the SQL that generated the above SQL

SELECT 'SELECT '''||table_name||'-'||column_name||''', count(*) FROM '|| 
table_name|| ' WHERE not exists (select 1 from '|| remote_table ||' where '||
remote_table||'.'||remote_column||' = '||table_name||'.'||column_name||') AND '
||table_name||'.'||column_name||' IS NOT NULL
UNION ALL' 
FROM (
   SELECT a.table_name,
      column_name,
      ( SELECT table_name FROM user_constraints 
      		WHERE constraint_name = a.R_CONSTRAINT_NAME) remote_table, 
      ( SELECT column_name FROM user_cons_columns 
      		WHERE constraint_name = a.R_CONSTRAINT_NAME) remote_column 
   FROM user_constraints a, user_cons_columns b 
   WHERE a.constraint_name = b.constraint_name 
   AND a.constraint_type = 'R' )

This SQL generates SQL that when run will give us data about tables that do not match our constraints requirements. If you have a CUSTOMER table which has CUSTOMERTYPEID and STATUSID on it, then the SQL generated would be.

SELECT 'CUSTOMER-CUSTOMERTYPEID', COUNT(*) FROM CUSTOMER  WHERE NOT EXISTS 
      (SELECT 1 FROM CUSTOMERTYPE WHERE 
             CUSTOMERTYPE.CUSTOMERTYPEID = CUSTOMER.CUSTOMERTYPEID) 
  AND CUSTOMER.CUSTOMERTYPEID IS NOT NULL
UNION ALL
SELECT 'CUSTOMER-STATUSID', COUNT(*) FROM CUSTOMER WHERE NOT EXISTS 
       (SELECT 1 FROM STATUS WHERE 
             STATUS.STATUSID = CUSTOMER.STATUSID) 
  AND CUSTOMER.STATUSID IS NOT NULL

Once the above SQL is run, the results will show us data that does not match the constraints we want to introduce into the QA environments.

Why do Evolutionary Design or Iterative Design or Incremental Design?
Everyone who has not worked in an evolutionary manner asks this? My answer, if you think the system you designed is NOT GOING TO CHANGE EVER then sure you can do design once and deploy once and you are done, move on to next project. But tell me one project you have been on, that does not have any changes in requirements, changes in technology, changes in look and feel etc after it was deployed.

So if every project changes after it was deployed, why live in the fallacy that nothing is going to change ever.

Instead, since you know requirements change, why not get better at managing change, which mean get better at Evolutionary Design or Iterative Design or Incremental Design, so that you are ready for the next requirement change that comes along.

The following SELECT statement in code

stmt = DB.prepare("select id,name,state,zip " +
        "from customer " +
        "where " +
        "phone = ? " +
        "and active = ?");
stmt.setString(1, customerPhone);
stmt.setBoolean(2, isActive);
stmt.execute();

where customerPhone and isActive are values you would pass in to the SELECT before its executed. Everything is fine when one day the value passed for customerPhone is NULL. For a database (Oracle is what I know most) a NULL will never be equal to NULL , the SELECT will not return rows where the customer.phone is NULL, leading to wrong results. The SELECT will have to be changed to

stmt = DB.prepare("select id,name,state,zip " +
        "from customer " +
        "where " +
        "(phone IS NULL or phone = ?) " +
        "and " +
        "active = ?");
stmt.setString(1, customerPhone);
stmt.setBoolean(2, isActive);
stmt.execute();

We could dynamically write the SELECT so that we don't have to do the OR in the where clause, which could be expensive.

boolean hasPhone = false;
StringBuffer sqlQuery = new StringBuffer();
sqlQuery.append("select id,name,state,zip " +
                "from customer " +
                "where 1=1 ");
if (phoneNumber != null) {
    sqlQuery.append("and phone=? ");
    hasPhone = true;
} else {
    sqlQuery.append("and phone IS NULL ");
}
sqlQuery.append("and isActive=?");
stmt = DB.prepare(sqlQuery.toString());
if (hasPhone) {
    stmt.setString(1, phoneNumber);
    stmt.setBoolean(2, isActive);
} else {
    stmt.setBoolean(1, isActive);
}
stmt.execute();

Recently we had peculiar problem. Some of the data in the database was not being created in a proper fashion. Once we found that out we fixed the problem in the application. The customer still had the perception that the code is still broken, because the fixed code was now interacting with the data that was broken (since it was created much earlier by code that was broken). Data has a life of its own (more on this later)

So I started thinking ( this happens very rarely) about data quality. Does improved data quality give a perception of improved code quality? Does poor data quality drag down the perception of code quality? Anyway the effort invested in code quality can be negated if Data Quality is not thought about!.

When unit tests could be written to flush out design/code quality problems in the application. what could be done to improve data quality, what kind of tests could be written? Would these tests apply to the design of the database or to the quality of the data?

You learn a lot from kids, and this lesson I will never forget.

So I'm at Lyon (in France) airport and there are a couple of kids (4-5 year olds) playing in the airport play area which had slides, call these two kids TA and TB my daughter call her AA joins these kids and they all start playing with the slide.

All is going well when a family with two kids, mom, dad and grandma join in, mom and dad are staying away and grandma takes the kids lets call them FA and FB to play on the slides. Grandma makes the previous kids (AA, AB, AA) stop playing and clears the slide for her grand kids (EA and EB) to play the slide. Grandkids start playing for a while and TA and TB are just waiting around not knowing what to do. AA by now has moved on to a different toy in the airport.

Sometime later TB opens his bottle of water to get a drink and pours it all on FA. FA is now totally wet and grandma is furious at TA and TB. Now she has to take FA away to get a new shirt and get FA dry. TA and TB by now start playing with the slide, happy that they have control of the slide.

The incident may sound run of the mill, if I don't revel the nationalities of the kids involved TA and TB are from a Arabic country. FA and FB are from a European country. This little incident has taught me that when you push people around they are going to react and the reaction may sometimes be violent. It has also taught me to stay out and let the kids play and resolve their issues themselves.

These lessons are so relevant in these times. Wish our leaders learnt something from kids.

This last week I received my copies of the book, for the first time I could touch what I had worked on for almost a year and half now, I could see it flip the pages. When I opened the UPS box and saw the book for the first time it felt great but in a minute or two I was not feeling anything, after some tea and some walking around I took the book in my hands and started flipping the pages this is when the real feeling started to sink in, but still I was not sure of the situation, was not really sure if it was real, after about 2-3 hours when I saw the book and held the book and started reading, I was started feeling the joy the happiness and all the goodness. I think when I see it being read by others then I will feel a lot different, the other day I saw Nick take out the book out of his backpack I was thrilled to see it being read.

Why is that many a time I say something and the person hears something else.

I have been wondering is it me or is it just the way people listen or interpret my words. How can I communicate better, making it easier for myself and cause less of an hassle for myself and all the ones around me.

When I get to the bottom of this, it will be one heck of an achivement.