When pet projects bite back!

Decisions in Your Tables

2008-06-30T22:00:00.002+08:00

So how do I start explaining about decision tables? Well, its an interesting subject..

Introduction

What does wikipedia say ?
"Decision tables are a precise yet compact way to model complicated logic. Decision tables, like if-then-else and switch-case statements, associate conditions with actions to perform. But, unlike the control structures found in traditional programming languages, decision tables can associate many independent conditions with several actions in an elegant way."

Decision tables, for me, seem to make sense in that they model logic into an understandable table-diagram that can be checked easily.

Advantages to Using Decision Tables

The first advantage of decision tables, in my opinion, would be to replace if-then-else statements with something more manageable.

**Printer troubleshooter**
		Rules
Conditions	Printer does not print	Y	Y	Y	Y	N	N	N	N
	A red light is flashing	Y	Y	N	N	Y	Y	N	N
	Printer is unrecognized	Y	N	Y	N	Y	N	Y	N
Actions	Check the power cable			X
	Check the printer-computer cable	X		X
	Ensure printer software is installed	X		X		X		X
	Check/replace ink	X	X			X	X
	Check for paper jam		X		X

Here you can see an example of a decision table for a printer trouble shooter. Above are the conditions (the if-then-else) and below are the actions to take according to the conditions.
While this table contains quite a few options, you can see that its easy to handle them because it is more visual then if-then-else.

Now, since this is a blog about MySQL, lets pretend for a minute that it would be a good idea to put your decision table in your database. Why? well, because databases hold tables. Think of it as a regular configuration file (like an XML file or an INI file), but you just decided to keep it in your database.
So what have you done exactly? Well, you have seperated your if-then-else statements (your logic) and then convert it into a diagram (which happens to fit nicely into a database table). So basically, you have abstracted your logic from your application code into a manageable place. This is very important.
By abstracting your logic, you can now manage changes in your application.What this means is, in the future, if you need to make a change to your logic, you can simply change your decision table and the logic will be updated in your application. In theory, you can also let non-technical people make changes in these tables also.
So the second advantage for using decision tables is for managing change and the third advantage, again in my opinion is debugging. Just the fact that its more visual and you can see the paths for conditions and actions, helps to debug errors.

Example

An insurance company wants to setup a system that will determine if their already existing customers deserve a discont on their next policy and/or get money back.

For a customer to get a discount on his next policy he/she needs to:

Already have an existing policy with the insurance company
Want to renew his/her policy with the insurance company
Must have paid most of the monthly insurance payements on time. Atleast 75% of the time.
Customers who have big insurance policies (that are over 10,000 USD) can also get discount, but only if those customers paid on time not less then 50% of the time.
Customers who are very good can also get some money back. Meaning, they must have paid more then 90% of the payments on time and not made any claims on their insurance policy.
If a customer has made claims on their policy over 50% of the value of the policy, they will not get a discount or money back.

Here is the code for these conditions:

-If HasPolicy(Person) = True {
--If Wants2Renew(Person) = True {
---If Not(PaidOnTime(Person) = ‘Less50%’) and Not(UsedPolicy(Person) = ‘More50%’) {
----If (PaidOnTime(Person) = ‘More75%’ and BigPolicy = True) {
-----Discount = True;
-----MoneyBack = False;
----} Elseif PaidOnTime(Person) = ‘More90%’ {
-----Discount = True;
-----If UsedPolice(Person) = ‘NotUsed’ {
------MoneyBack = True;
-----} Else {
------MoneyBack = False;
-----}
----} Else {
-----Discount = False;
-----MoneyBack = False;
----}
---} Else {
----Discount = False;
----MoneyBack = False;
---}
--} Else {
---Discount = False;
---MoneyBack = False;
--}
-} Else {
--Discount = False;
--MoneyBack = False;
-}

Here is a condition table for the same example. Minus (-) equals all the options.

Conclusion

As you can see decision tables are much much better then writing code, because its half the size...
.
.
.
HEY! wait a minute! that code is waaay too long, I can do much better than you!

Ok Ok, lets refactor the code:

-If HasPolicy(Person) and Wants2Renew(Person) and ((PaidOnTime(Person) = ‘More75%’ and BigPolicy(Person)) or (PaidOnTime(Person) = ‘More90%’)) and Not(UsedPolicy(Person) = ‘More50%) {
--Discount = True;
--If PaidOnTime(Person) = ‘More90%’ and UsedPolicy(Person) = ‘NotUsed’{
---MoneyBack = True;
--} Else {
---MoneyBack = False;
--}
-}

So here the code if a lot smaller. I do admit that it did take me some time to check the code (and I might have mistakes) and I didn't get the chance to make tests for it. Normally, after writing code, you would test it many many times over to see that it does what its supposed to do.

Conclusion

Decision tables can help you make things clearer when it comes to implementing logic in your application. It also helps debug and manage changes.

For the example above,

The insurance company decided that giving money back will now also be given to people who already have an insurance policy, but may or may not want to renew their policy with the insurance company.

Please change the program for this new requirement.

I got Married

2008-06-07T01:27:00.002+08:00

Well, I haven't blogged for a while, but I have a good excuse.
I got married!

We flew out to get married in a nice Mediterranean country.
It was quite romantic and we are very happy.
I married the most beautiful and amazing girl in the world.

(P.S. If you intended to get married in another country, plan ahead 3-4 months to get the right documents)

Useful ways of using Views

2008-05-16T13:55:00.005+08:00

Here are a few ways I found Views to be useful for me.

Data Cleansing

If I have a column that needs to be cleaned or tested in some way. For example:

Select mycolumn, if(mycolumn is null, TRUE, FALSE) as mycolumnisnull from mytable

This example is not that amazing, but it lets you place conditions for the data. Then instead of re-writing these conditions in your application layer, you can re-use the View from the database.
Another example:

Select email, regex ['some amazing email verification/cleaning regex line'] as cleaned_emails from emails

Here again, we have some condition that placed in the database. We can now call the emails table and tell it to give us only the cleaned emails according to the defined conditions.

Decision Making

You can place some business logic in your Views. I wrote about it in my blog before.
This time I will just use an example:

Select business_loan, if (business_loan > 30000, 'Allow Discount', 'No Discount') as discount from loans

So here, you have an embedded rule that says if the loan is over 30,000, then give a discount. Otherwise, no discount.
There are advantages and disadvantages to using business rules in your database. One advantage is the visual way of verifying that your code is working. You can see next to your data if your code works or if you need to make changes.

Displaying parts of many-column tables

Very simply, if you know you only need a few columns from a table with many columns, you can just specify it in a View. It just keeps things tidier.

Create View contact_details
Select col11 as firstname, col52 as lastname, col37 phonenumber from 100columntable

I have used this (a lot) for finding tables to extract data from when I did some ETL-data warehouse work. This is an example:

Create View src_thetableiwaslookingfor
Select * from database1.tablethatiwant

This helped me a lot in the past and also helped when I worked with other developers on the same project. We just all used the same interfaces (Views).

Fashioning your data

You can use some basic text manipulation on your data to help you display it better.
For example:

Select concat(title,'. ',firstname, ' ', lastname) as fullname from contact_details.

Here, we just added the title, first name and last name into one string (example, Mr. John Smith).
If you really want to go crazy, you can do this

Create View HTML_export
Select concat('[tr] [td] ',firstname,'[/td] [td] ',lastname,'[/td] [/tr] ') as list from contact_details

So, if in the future you want to get a quick list of all the contact names in HTML format, you can just use the View.

Conclusion
Views are very useful :)
Especially if you dislike long-winded languages when you can do it in 1 line of SQL.

I plan on writing a more detailed article soon about this topic

A Blog about Blogging

2008-05-13T15:30:00.003+08:00

Many of you may have already noticed that blogs are becoming more and more influential as mediums of information. I personally read tens of blogs everyday to keep up with events.

But who decided that blogs are good sources of information?
After all, a blog is someone's opinion. It might be a better educated opinion with more facts then other opinions, but its still someone's opinion.
A blog can be defined as:

Short for "Web log," a specialized site that allows an individual or group of individuals to share a running log of events and personal insights with online audiences.

News

News is still someone's opinion. News companies try to be neutral and say that they report the news the way it is. But anyone probably knows that if they open the TV to a certain news channel, they will see the news in a different way then other channels.
Whether it be if the news channel is usually more right, more left, if the news anchors are tougher then news anchors on other channels, etc..
So there is no "actual" news, there is just how people see the world. Truth is relative to how you see the facts.
In a sense, you can validate blogs as being reasonably "true" by saying that news channels are not 100% "true" anyway.

Chatting with Friends

But not everyone is a news journalist. So what motivates people to write blogs?
Obviously, some people have different reasons. But since its my blog, I will express my opinion to what I think it is.
I think that people like to chat with their friends or other people and share ideas.
It is always nice to talk to a friend who thinks a bit like you and discuss issues that you find interesting.
This is where I think the motivation lies. People want to share ideas and instead of maybe sharing it with one person, they can share it with many people. People who think this issue is interesting. People who either want to learn the issue or people who already know enough about the issue to comment on it.

Facts and Opinions

Opinions are ways of trying to understand the world around us. If there were facts to help us understand the world around us, then we don't need someone's opinion about the issue, we already know or can find out.
If however, there is no formal way of finding out something, we use informal ways. We have opinions about things because we don't have any other formal way of understanding them. We like to give our opinions and discuss them to either affirm them with other people (if they agree) or change them, if we are found out to be wrong.
In this spirit of co-operation, we discover new things when we discuss our opinions.

My Opinion

I think blogs are now at a very mature stage. Already, blogs are compared to news channels and news channels reference blogs some of the time.
A good point to take from this, I think, is that even with news, its important to remember that its still someone's opinion. Its always good to take many different opinions from different sources as well as share your own opinions before coming to your own conclusions.
And its even more important to note, that a lot of our current conclusions are based on opinions. With that, we should be open to new opinions that could possibly change our previous conclusions.

Thank You Ubuntu

2008-04-28T12:25:00.003+08:00

Well, I decided to take the plunge and migrate from Windows XP to Ubuntu. Now, technically, I have been wanting to do this for a while, but I was dependant on my windows to dial up to my internet (some problem with my ISP here). I tried to do it in Ubuntu a few months ago, but it was too complicated for me. However, it left me with a yearning to try again.
So, I waited for the new Ubuntu to come out and in parallel fixed my router.

Why?
The reason I thought it was a good idea to move to linux was mainly for self-improvement.

I recently read some blogs (couldn't find them now) that mentioned that with all the new web apps, there will be less and less emphasis on the OS. In 2011 (gartner says), this will hit a crucial point.
I was thinking, maybe more people will adopt free linux when everything important will be in "the cloud" and all you really need is a browser anyway. So I know how to use linux, I will be ready for that time and help people start adopting it.

Another 2 important reasons for me were:
1) That all servers use linux for their web apps and to be more comfortable in that environment will be a big plus.
2) To get myself more into the developing frame of mind. This maybe just me, but I noticed that the hardcore developers use linux or macs. Not that I am knocking windows, but I don't really feel that developers in windows are as serious as linux or mac for some reason (did you see some online presentation videos for a new technology. ALL the presenters use macs. Especially ones from Google. Am I missing something?)

Installing
I did some research, downloaded some pdfs and was all ready to go. I decided to make my current windows into a VMware image in case I needed something from it.
I downloaded Ubuntu and tried to figure it out. Bascially, you get 700mbs which you download of the net and anything else you want, you need to download. Fedora, comes with 4gbs and you need to download very little.

I had real problems understanding how to install things.. until I discovered DAM DAM DAM... repositories. Ok, so forgive me if you now are saying to yourself "Duh, I already knew that". I am new to it.

What I learned was, if you type "sudo apt-get install the-whole-universe-for-free-woohoo-I-love-it", you just magically get it. No need to download, configure (well, most dont) and all that trouble. You just use apt-get. You can even do it visually with the package manager.

This for me, is a huge advantage over other platforms. Its just so welcoming and encourages you to improve.
For example, I could download MySQL off the website, then install it manually. Or, I could just write 1 line and it will download, install itself, do some configuring and automatically upgrade (I think) when a new version goes into the repository. How cool is that?

The Tipping Point
So I was trying to install VMware and this was really difficult. Opening the bin/rpm file was difficult enough, but after succeeding in that, it gave me some error.
I almost gave up, until I found this article. It shows you that you can do everything with command lines and after playing around with linux, I am becoming more and more comfortable with them.
However, the important part is in the comments.
Someone posted the exact error I had.. and the guy told him how to solve the problem... by changing the ACTUAL CODE. Oh my god! change the actual code of the supplier? Unheard of in windows. If you have an error, you file a report somewhere and wait a week if your lucky.

Just the fact that I was allowed to look into someone else's code (and VMware is a big company) and learn and/or change it (I wont go into licenses), really makes me feel part of something.
I now really understand that open source doesn't mean free as in free beer, but free as in freedom of expression (sorry, that was a bit deep).

In conclusion.
I really like Ubuntu. Its not perfect and it needs some work, but this time, I feel that I can be part of it and help it improve.

A useful article to install Ubuntu <--

China and Blogs equals Page Cannot Be Found

2008-04-21T12:24:00.006+08:00

Hello Fellow Bloggers

I am hereby officially requesting if you could kindly enable your blog to have subscription via email.
The reason I am asking this is because I like to get information from your wonderful blogs, but unfortunately, feedburner is blocked in china (and wordpress sometimes, blogspost sometimes, typepad...etc). This makes it difficult and highly annoying to get feeds from blogs.
The usual method to overcome this difficulty is to find proxies and sometimes install browser plugins to connect to the various proxies (vidalia is also good), but this slows down the process of trying to view the blog.
Now, I realize that most of you have no problem at all viewing blogs and are used to reading them every day with no delay in speed or increase of blood pressure.
But, please, consider the poor unfortunate people that live in "restrictive" parts of the world and would still like to be informed as to whats going on in outside their country of residence.

To help you enable email subscription for your blog, please use either feedburner or feedblitz.

Thank you for your understanding.

My First Data Warehouse

2008-04-17T18:02:00.005+08:00

I finally finished my first data warehouse! and it only took me 3 days!

Well, to be fair, the data warehouse design was already planned and it wasn't really that big anyway, but I am still happy about it.

I was asked on Monday to do a data warehouse for my company's head quarters in Germany. I work in Beijing, so its like.... very slow to connect to there. They gave me the database design, some SQL statements to generate a few dimensions and "rough" business rules for the data.

Now, I haven't done anything like this before, but I really wanted to try. So I did it my way.

My way is to use a lot of Views with long SQL statements instead of cursors or stored procedures. I like it this way, because I feel like I can see the data and catch problems instead of programming blindly to find out later that there is a big problem. So basically, for me, its more comfortable.

I took the SQL statements that loaded the dimensions, cleaned them a bit and put them in Views. The Views in Oracle (we used Oracle for this data warehouse. I told them MySQL is better, but they preferred Oracle)... the views are very restrictive. I thought it was good, because it made me confident that they will work properly. However, Views don't allow for UNION, so for that I used Materialized Views.

Materialized Views was something I learned while doing this data warehouse and I think they are very cool, to be honest. It allowed me to create a sort of temp table that more or less needed no code from me to maintain. I can refresh it with 1 line in a stored procedure. I could also schedule it to refresh, but I didn't learn how to do that. I also used Materialized Views to replace views that needed too much calculations and I got REALLY annoyed waiting for it finish from the server in Germany.

I checked my SQL statements with looking at the Views and using a lot of count()'s on many different columns. Count()'s don't count NULL values and NULL is something that cant go into a fact table of a data warehouse. I also brushed up on my AND/OR conditions when I had missing data from one table and I had to find it in maybe another table. After that, I took this value and INNER JOIN'd it with another table. So an example that worked well for me is:

left join src_dim_team_all dt on ((u2dt.TEAM = dt.TEAM)) or
((u2dt.TEAM is null) and (dt.TEAM = f.TEAM))

In this example, the join takes the data from the first table.. or.. if the first table's value is null, it takes it from the second table. A simple OR didn't work for me and I only found that out when I tested it with some count()'s.

In the end, 99.99% of the work was done using Views and 1 stored procedure to do the:

insert into mydimension from select * from myview

which made it very very easy for me to abstract the complexity into several Views.

At the end, the final extracting, transforming and loading was ridiculous. EVERY tiny thing took 5-10 mins to run on the German server and after that I had to test it if its correct.

However, I enjoyed this experience and it would be one more thing I can add on my CV.

Thanks for reading my blog.

My Adventures With Erlang Have Come to an End

2008-04-14T12:56:00.004+08:00

After trying very hard to install the Yaws webserver for Erlang on windows, asking 2 friends of mine to try to install it and pleading with the original creator to make an installer, I have given up.

So my very short lived relationship with Erlang has come to an end also. I am just too busy to sit down and learn how to install a program that seems too complicated to me before I even start programming in it.

I hope one day, Yaws will use an installer like Bitrock. Until then, I will spend my time either improving the languages I know or learning new ones that seem cool and not that complicated to get into.

BitRock Lamp Installer

2008-04-04T11:19:00.003+08:00

I was complaining in an earlier post that I have problems with linux style installation.
I found a company that can help me solve that!

BitRock makes open source software easier to use by providing a complete automated solution for Open Source Application Deployment.

Its quite cool and they have a LAMP stack installer here.

Now I am suggesting that someone at Erlang does it too. They have a Lyme stack which is Linux + Yaws + Mnesia + Erlang. (a comparison can be found here)

Anyway, I am still stuck on installing yaws :(
I asked a friend to help until someone makes a cool installer.

The Code Monkey Song

2008-04-03T20:57:00.003+08:00

For those who haven't heard this song, please go to here.
Its a very cool song about programmers, done in an "open-source" way, in that the guy who wrote the song isn't signed by any record label and hes trying to promote himself through his site.

Truly an anthem song.

SQL is in Fact a Programming Language

2008-04-03T16:39:00.007+08:00

While I was updating my blog recently, I read the top part of it which said "My adventures in database developing". I realized that I have really neglected database developing with all my research into other programming languages.
So I was trying to play around with SQL statements to brush up on my skills.

Luckily (or unluckily), someone at my work, a java developer, needed to do a data export and thought that I can do it in 1 SQL line. He said.... and I quote "You only need 1 SQL line. Whats the problem?".
Whenever I hear the "whats the problem" line, there usually is one.
So, with him having great confidence in my SQL-ing ability or a complete misunderstanding of database developing, I was forced to do the data export.

I realized early on that it would take a long time. Just to start off, I needed like 20+ columns, but each column was a row somewhere in 4 different tables.

To make a long story short, I did complete the task and to simply pat myself on the back, I will paste it here. Just to show that, yes, you can program in SQL.

(but next time, please give me rights on the server to do it with several views).

The SQL statement is about 2-3 pages long, by the way and you don't really need to read all of the statement

SELECT
CONTACT.CON_ID as contact_id,
contact.CON_EMAIL_LOWER as email,
contact.CON_CREATION_DATE as creation_date,
lastname.CONATTR_VALUE as givenname,
firstname.CONATTR_VALUE as familyname,
languages.CONATTR_VALUE as "LANGUAGE",
gender2.SELVAL_VALUE as gender,
mobile.CONATTR_VALUE as mobilephone,
office.CONATTR_VALUE as officephone,
home.CONATTR_VALUE as home,
address.CONATTR_VALUE as address,
zipcode.CONATTR_VALUE as zipcode,
province2.SELVAL_VALUE as province,
city2.SELVAL_VALUE as city,
intendedpurchase2.SELVAL_VALUE as intendedpurchase,
ownedcarbrand2.SELVAL_VALUE as ownedcarbrand,
minimodel3.SELVAL_VALUE as minimodel,
dealer3.SELVAL_VALUE as dealer,
subject3.SELVAL_VALUE as subject,
SUBSCRIPTION.sub_id as subscription_id,
brochure13.SELVAL_VALUE as brochure1,
brochure23.SELVAL_VALUE as brochure2,
brochure33.SELVAL_VALUE as brochure3,
message2.CAMATTR_VALUE as message
from CONTACT contact
left join CONTACT_ATTRIBUTE lastname on lastname.CONATTR_CON_ID = CONTACT.CON_ID and lastname.CONATTR_ATTR_ID = 1010
left join CONTACT_ATTRIBUTE firstname on firstname.CONATTR_CON_ID = CONTACT.CON_ID and firstname.CONATTR_ATTR_ID = 1020
left join CONTACT_ATTRIBUTE languages on languages.CONATTR_CON_ID = CONTACT.CON_ID and languages.CONATTR_ATTR_ID = 1000
left join
(SELECTION_VALUE gender2 INNER JOIN CONTACT_ATTRIBUTE gender on gender.CONATTR_SEL_ID=gender2.SELVAL_SEL_ID and gender2.SELVAL_LANGUAGE = 'zh' )
on gender.CONATTR_CON_ID = CONTACT.CON_ID and gender.CONATTR_ATTR_ID = 1030
left join CONTACT_ATTRIBUTE mobile on mobile.CONATTR_CON_ID = CONTACT.CON_ID and mobile.CONATTR_ATTR_ID = 1100
left join CONTACT_ATTRIBUTE office on office.CONATTR_CON_ID = CONTACT.CON_ID and office.CONATTR_ATTR_ID = 1110
left join CONTACT_ATTRIBUTE home on home.CONATTR_CON_ID = CONTACT.CON_ID and home.CONATTR_ATTR_ID = 1120
left join CONTACT_ATTRIBUTE address on address.CONATTR_CON_ID = CONTACT.CON_ID and address.CONATTR_ATTR_ID = 1200
left join CONTACT_ATTRIBUTE zipcode on zipcode.CONATTR_CON_ID = CONTACT.CON_ID and zipcode.CONATTR_ATTR_ID = 1210
left join
(SELECTION_VALUE province2 INNER JOIN CONTACT_ATTRIBUTE province on province.CONATTR_SEL_ID=province2.SELVAL_SEL_ID and province2.SELVAL_LANGUAGE = 'zh' )
on province.CONATTR_CON_ID = CONTACT.CON_ID and province.CONATTR_ATTR_ID = 510
left join (SELECTION_VALUE city2 INNER JOIN CONTACT_ATTRIBUTE city on city.CONATTR_SEL_ID=city2.SELVAL_SEL_ID and city2.SELVAL_LANGUAGE = 'zh' )
on city.CONATTR_CON_ID = CONTACT.CON_ID and city.CONATTR_ATTR_ID = 520
left join (SELECTION_VALUE intendedpurchase2 INNER JOIN CONTACT_ATTRIBUTE intendedpurchase on intendedpurchase.CONATTR_SEL_ID=intendedpurchase2.SELVAL_SEL_ID and intendedpurchase2.SELVAL_LANGUAGE = 'zh' )
on intendedpurchase.CONATTR_CON_ID = CONTACT.CON_ID and intendedpurchase.CONATTR_ATTR_ID = 1300
left join (SELECTION_VALUE ownedcarbrand2 INNER JOIN CONTACT_ATTRIBUTE ownedcarbrand on ownedcarbrand.CONATTR_SEL_ID=ownedcarbrand2.SELVAL_SEL_ID and ownedcarbrand2.SELVAL_LANGUAGE = 'zh' )
on ownedcarbrand.CONATTR_CON_ID = CONTACT.CON_ID and ownedcarbrand.CONATTR_ATTR_ID = 1400

left join SUBSCRIPTION on CONTACT.CON_ID=SUBSCRIPTION.SUB_CON_ID
left join
(SELECTION_VALUE minimodel3 INNER JOIN
(CAMPAIGN_ATTRIBUTE minimodel2 INNER JOIN SUBSCRIPTION minimodel on minimodel2.CAMATTR_SUB_ID=minimodel.SUB_ID)
on minimodel2.CAMATTR_ATTR_ID=52001)
on minimodel2.CAMATTR_SEL_ID = minimodel3.SELVAL_SEL_ID and minimodel3.SELVAL_LANGUAGE = 'zh' and minimodel.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)
left join
(SELECTION_VALUE dealer3 INNER JOIN
(CAMPAIGN_ATTRIBUTE dealer2 INNER JOIN SUBSCRIPTION dealer on dealer2.CAMATTR_SUB_ID=dealer.SUB_ID)
on dealer2.CAMATTR_ATTR_ID=52002)
on dealer2.CAMATTR_SEL_ID = dealer3.SELVAL_SEL_ID and dealer3.SELVAL_LANGUAGE = 'zh' and dealer.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)
left join
(SELECTION_VALUE subject3 INNER JOIN
(CAMPAIGN_ATTRIBUTE subject2 INNER JOIN SUBSCRIPTION subject on subject2.CAMATTR_SUB_ID=subject.SUB_ID)
on subject2.CAMATTR_ATTR_ID=50001)
on subject2.CAMATTR_SEL_ID = subject3.SELVAL_SEL_ID and subject3.SELVAL_LANGUAGE = 'zh' and subject.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)
left join
(SELECTION_VALUE brochure13 INNER JOIN
(CAMPAIGN_ATTRIBUTE brochure12 INNER JOIN SUBSCRIPTION brochure1 on brochure12.CAMATTR_SUB_ID=brochure1.SUB_ID)
on brochure12.CAMATTR_ATTR_ID=51001 and brochure12.CAMATTR_SEL_ID=5100101)
on brochure12.CAMATTR_SEL_ID = brochure13.SELVAL_SEL_ID and brochure13.SELVAL_LANGUAGE = 'zh' and brochure1.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)
left join
(SELECTION_VALUE brochure23 INNER JOIN
(CAMPAIGN_ATTRIBUTE brochure22 INNER JOIN SUBSCRIPTION brochure2 on brochure22.CAMATTR_SUB_ID=brochure2.SUB_ID)
on brochure22.CAMATTR_ATTR_ID=51001 and brochure22.CAMATTR_SEL_ID=5100102)
on brochure22.CAMATTR_SEL_ID = brochure23.SELVAL_SEL_ID and brochure23.SELVAL_LANGUAGE = 'zh' and brochure2.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)
left join
(SELECTION_VALUE brochure33 INNER JOIN
(CAMPAIGN_ATTRIBUTE brochure32 INNER JOIN SUBSCRIPTION brochure3 on brochure32.CAMATTR_SUB_ID=brochure3.SUB_ID)
on brochure32.CAMATTR_ATTR_ID=51001 and brochure32.CAMATTR_SEL_ID=5100103)
on brochure32.CAMATTR_SEL_ID = brochure33.SELVAL_SEL_ID and brochure33.SELVAL_LANGUAGE = 'zh' and brochure3.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)

left join
(CAMPAIGN_ATTRIBUTE message2 INNER JOIN SUBSCRIPTION message on message2.CAMATTR_SUB_ID=message.SUB_ID and message2.CAMATTR_ATTR_ID=50002)
on message.SUB_CON_ID=CONTACT.CON_ID
and SUBSCRIPTION.SUB_ID in (select distinct(CAMATTR_SUB_ID) from CAMPAIGN_ATTRIBUTE group by CAMATTR_SUB_ID)

I apologize in advance if I get anyone's feed-reader stuck.

Thank you for reading my blog.

Searching Problems with Dates and Midnight

2008-04-02T20:32:00.003+08:00

Today, we run into an issue when a client of ours. They wanted to see some of the data on our website and when doing a search, they didnt see all the days they asked for in the search

Why didn't they see all the data? Because they did a search by date and some of the dates were stored via the website in the form of '2008-01-01 00:00:00' and some were stored via the database in the form of '2008-01-01 12:35:49'.
Now, for some magical reason, if you hide the time in the date in your searches, like so:

where signupdate between '2008-01-01' and '2008-01-02'
or
where signupdate between date('2008-01-01 00:00:00') and date('2008-01-02 00:00:00')

then you might not see all the data between the days 01 to 02.
Depending on which way your date was stored, you might only see the dates between those 2 dates and not equal to those dates as well.

So for example, if you had:

2008-01-01 00:00:00
2008-01-01 00:00:00
2008-01-02 00:00:00
2008-01-02 00:00:00
2008-01-03 00:00:00
2008-01-03 00:00:00

and you ran the search above, then you would see:

2008-01-01 00:00:00
2008-01-01 00:00:00

(I once worked for another company that booked hotel rooms online. For them when you booked the room, the nights were counted. So if you would do a search, on the website, all the date allocations would be set to midnight - 00:00:00.)

In our situation we had a mix of dates which caused use problems with 1 table and was ok with another.

We decided to solve the problem like this

where signupdate between '2008-01-01 00:00:00' and ' 2008-01-02 23:59:59'

That is, from what I understand, the time a day starts (00:00:00) and the time a day ends (23:59:59). So we figured we covered all the issues with this statement and we are happy with it.
I hope it helps you to if you also run into these kind of problems.

Yet Another Idea for GSoC - New Datatypes

2008-03-26T13:54:00.004+08:00

Well, as most of you may know, Google Summer of Code is nearing and I have seen many
MySQL blogs urging people to contribute ideas or mentor projects.

My Idea
So I'm going to suggest an idea and as the title already mentions, I think it would be a good idea to add some new Datatypes. Specifically, a new Email datatype.

Now I already hear you saying "Email is a string, why would you need a datatype for it?", but hear me out.

So basically, I personally, have been dealing a lot with trying to clean email addresses and customer data so that my company can help their biggest client use this data in their new CRM system.

I thought it would have been a good idea to implement an email datatype inside MySQL that already has the standard regex for emails and has a defined varchar for the standard of emails.
Later, I became aware that there can be different types of emails addresses.
For example, you can have the standard latin a-z 0-9 with . and _ in them.
You can have an email with the european a u o with the 2 dots above them.
You can also have emails with characters like / in them for example 24/7@domain.com (someone that posted on my blog mentioned that one).

To solve this, you could have an Email datatype like this:
Email(latin)
Email(german)
Email(utf-8)

The Benefits
So what would be the benefits of using this kind of datatype?

Its out of the box solution for a common problem.

MySQL is used by many many websites and most of them record email addresses, so its relevant to them.
You wouldn't need to develop something yourself, go look at MySQL forge for snippets, download and use any libarys, even bother to read about it. Its just there provided for you in the database.

It helps improve data quality. After you have many tens of thousands of emails, it would have been nice to have more data quality. Thats not to say that you can develop some solution on the front end with Javascript for example, but it would still be nice to just have it in the database.
You wouldn't need to write webinars telling people how to optimize or shrink their email field in the database. Maybe even you can store it better on the harddisk for queries if you know it will be used for email address, who knows.

Downside
The downside would be, that you will get a database error if you didn't implement any checks when the user is entering their email address on their website. For this you might want to make your email field a string and worry about it later. But I assure you, later will be for more annoying.

More Options
Now, you can also say "Well, you can also have a datatype for phone". Well, you can.
Maybe you can have a phone datatype like this:
phone(US)
phone(world)

Depends what you want, but I'd like to start with email addresses. Maybe you can throw in there ip(raw), ip(full)...
I saw a webiner from Jay Pipes writing about that to improve query lookup speed specifically for ip addresses.

Conclusion
I think it will be useful for many MySQL users to have an out-of-the-box solution for a very generic problem in the form of new datatypes that come with their own validation rules.

Please write your thoughts in the comments. Your opinion is valued.

Thank you for reading my blog.

A list of Open-Source Alternatives to Business Applications

2008-03-24T11:32:00.005+08:00

If you are part of a big company and would like to recommend to them a list of open-source and cheap commercial alternatives, I have compiled a list that I use for my company.

These options might make your company more competitive in certain markets and might let you react faster to changes.

Here is the list:

Database - DataWarehouse
InfoBright - Commercial DataWarehouse Engine (highly recommended) that runs inside MySQL. review about it

EnterpriseDB - Commercial Enterprise Scale Database that runs inside PostgreSQL

Vertica - A very high-scale, commercial, analytical and column-based Database. (Was made by the man who invented relational databases in 1970s)

BI Suite
Pentaho - Commercial Open-Source BI suite. Has ETL tools and Reporting (like informatica and cognos)

CRM
SugarCRM - Open Source CRM and on-demand CRM

XRMS - Web-based Open Source CRM

OpenCRX - Enterprise Open Source CRM

ESB
Mule - Commercial Open Source Enterprise Service Bus. Connects between many different webservices. (highly recommended)

ERP
OpenBravo - Web-based Open Source ERP

ERP + CRM
OFBiz - Apache "Open-For-Business" Open Source ERP and CRM

Compiere - Open Source ERP and CRM

I hope it helps.

Thank you for reading my blog.

Pentaho Kettle vs Informatica PowerCenter

2008-03-21T12:05:00.006+08:00

After having a successful presentation to a major car manufacturer in China, I have decided to upload my presentation so that other people may benefit and recommend it to their bosses.

In the presentation I research Talend, Pentaho, Informatica and Inaport(CRM data importer).

So the presentation is biased to Pentaho (and the Use Cases inside the presentation are from the Pentaho website).

My Presentation

Please enjoy and distribute.

Open Source ETL tools vs Commerical ETL tools

2008-03-17T22:55:00.006+08:00

Recently I have been asked by my company to make a case for open-source ETL-data integration tools as an alternative for the commercial data integration tool, Informatica PowerCenter.
So I did a lot of research and I'm going to try my best, considering I have never used the open-source tools nor the commercial one.

I found plenty of information about comparisons between Pentaho Kettle and Talend, which were 2 of the open-source tools I was supposed to research.
Now, without getting in a big arguement (or matt casters posting on my blog), I'd like to attempt to compare the two, very briefly.
And again, this is ONLY from the research I did online and not based on my experience using the tools (since I dont really have any).

Pentaho Kettle vs Talend

Pentaho
Pentaho is a commerical open-source BI suite that has a product called Kettle for data integration.
It uses an innovative meta-driven approach and has a strong and very easy-to-use GUI.
The company started around 2001 (2002 was when kettle was integrated into it).
It has a strong community of 13,500 registered users.
It has a stand-alone java engine that process the jobs and tasks for moving data between many different databases and files.
It can schedule tasks (but you need a schedular for that - cron).
It can run remote jobs on "slave servers" on other machines.
It has data quality features: from its own GUI, writing more customised SQL queries, Javascript and regular expressions.

Talend
Talend is an open-source data integration tool (not a full BI suite).
It uses a code-generating approach. Uses a GUI, but within Eclipse RC.
It started around October 2006
It has a much smaller community then Pentaho but has 2 finance companies supporting it.
It generates java or perl code which you later run on your server.
It can schedule tasks (also with using schedulars like cron).
It has data quality features: from its own GUI, writing more customised SQL queries and Java.

Comparison - (from my understanding)
Pentaho is faster (twice as fast maybe) then Talend.
Pentaho's GUI is easier to use then Talend's GUI and takes less time to learn.

My impression
Pentaho is easier to use because of its GUI.
Talend is more a tool for people who are making already a Java program and want to save lots and lots of time with a tool that generates code for them.

Assuming Pentaho made it to the next round....

Pentaho Kettle vs Informatica

Informatica
Informatica is a very good commercial data integration suite.
It was founded in 1993
It is the market share leader in data integration (Gartner Dataquest)
It has 2600 customers. Of those, there are fortune 100 companies, companies on the Dow Jones and government organization.
The company's sole focus is data integration.
It has quite a big package for enterprises to integrate their systems, cleanse their data and can connect to a vast number of current and legacy systems.
Its very expensive, will require training some of your staff to use it and probably require hiring consultants as well. (I hear Informatica consultants are well paid).
Its very fast and can scale for large systems. It has "Pushdown Optimization" which uses an ELT approach that uses the source database to do the transforming - like Oracle Warehouse Builder.

Comparison
Pentaho's Javascipt is very powerful when writing transformation tasks.
Informatica has many more enterprise features, for example, load balancing between database servers.
Pentaho's GUI requires less training then Informatica.
Penatho doesn't require huge upfront costs as Informatica does. (that part you saw coming, I'm sure)
(edited)Informatica is faster then Pentaho. Infromatica has Pushdown Optimization, but with some tweaking to Pentaho and some knowledge of the source database, you can improve the speed of Pentaho. (also see line below)
(new)You can place Pentaho Kettle on many different servers (as many as you like, its free) and use it as a cluster.
Informatica has much better monitoring tools then Pentaho.

My Impression
Informatica is a really good enterprise ETL suite, but is very big and expensive.
If the system is small enough, I would rather give Pentaho a try and there are many many use cases where big companies used Pentaho (an airport, a hospital..).

Conclusion

I think matt casters said it best when he said:

The flood of open source software is going to wash away the proprietary ones..

If you want to add (or correct) to the information I wrote here, then please consider doing so, as I am still trying to understand these products myself.
Your opinion is valued.

Thank you for reading my blog.

Computed/Virtual Columns

2008-03-07T19:09:00.005+08:00

Some days a go I discovered a wonderful thing called computer columns when I stumbling on MS SQL server. There is a free MS SQL server 2005 express that you can download off a Microsoft site. Yes, I know, FREE and from microsoft.

(P.S. for those who didn’t read, Bill Gates is now the 3^rd richest man in the world after 13 years of being number one.)

I also read a really good article that explains indexes on computed columns. The benefits of speeding up searches with them and adding business rules. Obviously, the business rules were particularly interesting to me.

Jay Pipes had a similar webinar about the benefits of speeding up searches, but he suggested you do that by creating a new column and placing in it there. His example was, you can speed up a search for “SELECT * FROM Contacts WHERE family_name LIKE ‘%stone’” by making a column that reverses the family name and then you search by “SELECT * FROM Contacts WHERE reverse_family_name LIKE ‘enots%’”. This will save the query to go through all the records to find the results.

So I looked a bit more into it how to developing business rules in computed columns and I found out that I can’t use “If”s but I can use “Case”. I also found out that the values you are looking at must be in the same table and the same row. Otherwise its non-deterministic and cannot work.

I am pretty happy with these computed columns and I consider it to cover most of what I have been looking for. I am also aware that there is some work being done on this to be implemented into MySQL, since I saw some information about it on the MySQL forge.

I would also like to add some business rules in the form of validations for data to keep data quality high. I was thinking something along the lines of creating new data types with some constraints and limits inside them, but that didn’t work out at all in MySQL.

For example, creating an “email” data-type that will have some regular expressions check on it and default size.

I found something else in the land of XML which is kind of what I was looking for, called schematron, but I need to look into it more.

I’m going to learn some more about this when I have time. In the near future I know I have to learn about installing a CRM system that uses MS SQL and I want to see if I can use any computed columns to help with this system. So if I find anything interesting, I'll blog about it.

Thank you for reading my blog

BI users can't wean themselves off Excel

2008-02-28T10:47:00.003+08:00

I read this article recently about how BI users just really love and are used to their Excel sheets for almost anything they do. My first reaction was "duh". My second reaction was that even I used Excel recently to help me data cleanse some contact information.
Its easy to use, the functions are clear, I can develop my own functions in VBA, I can use colors to see problems with values, etc...
I mean I can bet you that "even" if you use MySQL and you want to play around with the data to see if you can find anything, you would use Excel.

But as the article says, there are some drawbacks in terms of time spent fixing errors and if you use Excel sheets that connect to other Excel sheet and then you go and insert a column or something.

Anyway, the article is worth the read and maybe if we could take some of the advantages of Excel and add it to MySQL, maybe we can get some of the marketshare of Excel to move over a bit to MySQL

Execution – The Art of “Cut the Crap and Just Do It”

2008-02-27T11:29:00.004+08:00

I recently reread the book called “Execution – The Art of Getting Things Done” and although its somewhat old, it has surprisingly good and practical advise about putting your focus more on getting things done and less on thinking about strategies.

Or where I read from somewhere recently “The goal is to work, not think about working” which was in reference to keeping a planning meeting fixed to a certain number of hours.

Of course, you do need strategy, but if you don’t actually make sure that what you planned get done, or alternately, notice that your strategy may not be working then it just won’t work.

Hey! Wait a minute! Don’t you do Theory all the Time

Yes, I am very guilty of it myself about just thinking of things and not doing them. I even wrote about it at the top of one of my posts.

So recently, I was looking at all my talk about business rules on my blog and I was thinking “if I take a regular database and I wanted to add some business logic/rules to it, how many rules would I need to add?”.

I took a database that was connected to some web site forms that basically collect information about users for campaigns and news letters. I could really only found one rule to add and that’s because I knew how the data will be used later.

Now I do agree that this is not really a web application that requires a lot of logic. This system is pretty much taking data from point A to point B.

However, I was thinking about past projects and even when it was relatively complicated web application and even then, I could only think, at the top of my head, 3 main rules that might have been used in the database instead of across 10s of PHP files. Everything else in those PHP file was again, taking data from point A and fashioning it in a nice viewable way to point B, which is on your screen.

My Question

So my question is, why is adding business rules to your database such a controversial issue?

Maybe it’s something to do with adopted practices that tell you to not put any logic in the database. Maybe people are afraid to commit to one database and might change it in the future. So keeping the logic separate is more convenient.

I myself have come to realize that a large part of the reasons I thought this was a good idea was when I was working on a MySQL/PHP system and wanted to save time coding PHP (or just couldn’t even found where the lines of code were located in the mess that was there) by making long and complicated SQL statements or by creating Views. This seemed easier to me and I found it more comfortable to control my work, monitor the data and (which was the best part for me) show me the speed in which the queries were generated - so that I can speed up the website.

Conclusion

So I think that if you really take a look at it, (and you are not making an Expert System or Decision Support System) you only really need a few (that’s just my crazy assumption) business rules which can relatively easily be implemented in the database. Why not just try it and see if it works for you.

Using Business Rules in MySQL

2008-02-20T14:21:00.002+08:00

Overview
While everyone tries to improve speed and performance in MySQL, other databases have realized that adding features that cut down on development time and improving time-to-market is what some people are looking for. MySQL is well known for great performance and it might be time to discover other parts of it that will speed up your over-all development process.

This article tries to explain how you can save time and effort on the development process by moving some of that development to the database. It does this by recommending you apply business rules to the database.

Additional Notes:

This example was taken from an actual database. Some of the table designs from that database were not ideally optimized and normalized. Please refer to the theory of the example and not the exact technical detail.

What is a Business Rule?

"Business rules represent policies, procedures and constraints regarding how an enterprise conducts its business." www.isr.uci.edu/~alspaugh/glossary.html

“Business rules describe the operations, definitions and constraints that apply to an organization in achieving its goals” http://en.wikipedia.org/wiki/Business_rules

As you can see, business rules help you “achieve your goals”. So what are your goals?

Many people do not take a few minutes to ask some simple but penetrating questions.

For example, your goal is to create an amazing online hotel reservation system.

You will do this by registering hotels and hotel rooms on your system and then letting the customer book them through a web portal.

What kind of information do I need to collect?

The hotel details
The hotel room details
The customer details
The allocation details

Why do I need to collect information about allocation details?

To know which rooms the customer allocated and on which dates.
To know which rooms the hotel needs to reserve for the customer.
To know which rooms have already been booked and to not book them to someone else.

What kind of information do I need to collect for the allocations?

The date – which day has the room been booked
The hotel info – which hotel was used so that you can pay them later
The room info
And is the room available to be booked?

So how do you know if the room is available to be booked?

You might need other types of information that will tell you if the room is available.

For example:

Has the room already been booked on the same days that you want it? Is it sold out? (sold out on your system or the hotel notified you that its sold out)
Is it currently available to be booked or is it being redecorated, fumigated for cockroaches or under repair? – What’s the status of the room?
How many rooms are available for this room type? I mentioned the room type (like suite or with balcony) because we don’t allocate specific rooms directly in the hotel; we just get a certain number of rooms reserved from the hotel (at least that’s what I understood from someone who does have an online reservation system).

The requirements for a room to be available are a sort of business rule. Once you have the requirements, all that interests you is the result of the business rule (the result for the question, is it available?). Meaning, after you know what you need to determine if the room is available, you don’t really “care” about the extra bits of information. All you care about is the result that the room is available.

This is what you normally do in your head and if you explain it to the database, it can also do that for you. You can ask the database to give you all the available rooms between 2 dates and it will give you the results because it already knows what other requirements to look for to determine if a room is available.

Decision Trees

“A decision tree (or tree diagram) is a decision support tool that uses a graph or model of decisions and their possible consequences… A decision tree is used to identify the strategy most likely to reach a goal.” http://en.wikipedia.org/wiki/Decision_tree

In order for me to draw business rules, I like to use decision trees. They are simple and very helpful to see problems as well as explain them to other people. They are also a sort of visual method for brain storming problems (example http://www.mindtools.com/dectree.html). There is also another reason to use decision trees and I will get to that later on.

This is a decision tree for our business rule. The diagram shows all the conditions for when a room is available and when it is not.

Off to the Code

Many people don’t know, but SQL is in fact a programming language. It is a “declarative query and data manipulation language… that has procedural constructs, control-of-flow statements, user-defined data types, and various other language extensions” (http://en.wikipedia.org/wiki/SQL). We are interested in the “control-of-flow” part of SQL to help us place conditions so when a room is available.

Here is our allocations table.

CREATE TABLE `allocations` (

`DayID` DATETIME NOT NULL,

`HotelID` INTEGER UNSIGNED NOT NULL,

`RoomID` INTEGER UNSIGNED NOT NULL,

`SoldOut` BOOLEAN NOT NULL,

`Status` BOOLEAN NOT NULL,

`RoomsAvailable` TINYINT UNSIGNED NOT NULL,

PRIMARY KEY (`DayID`, `HotelID`, `RoomID`)

);

To enable the business rule I will use the following piece of code:

SELECT DayID, HotelID, RoomType,

if((SoldOut=false)and(`Status`=true)and(RoomsAvailable>0),True,False) as Available FROM allocations

We have simplified things a bit. We no longer need to look at the other 3 columns that we hid. The data in those columns is still being collected, but for the purpose of finding out which rooms are available, they can remain hidden.

After creating a View with the above code, we can run a search like this:

SELECT * FROM allocations_view WHERE Available = True

And that’s really all we are concerned about.

What did we just do?

We placed a business rule in the database, next to the data. We took out the need for this to be programmed outside the database using another programming language (PHP, Java, Perl, ASP, etc… ).

Is this a good idea?

I think it is for 2 reasons:

If (and only if) developing this in the database saves you time and simplifies things, then you’ve saved the time and money asking a developer to do the same thing in the application layer.
Instead of the application layer retrieve all the bits of data to determine what is the result of the business rule (meaning more data has to be transferred from the database to the application layer), only the data of the result is transferred to the application layer. This can speed things up by reducing network traffic.

Decision Tables

“Decision tables are a precise yet compact way to model complicated logic. Decision tables, like if-then-else and switch-case statements, associate conditions with actions to perform. But, unlike the control structures found in traditional programming languages, decision tables can associate many independent conditions with several actions in an elegant way.” http://en.wikipedia.org/wiki/Decision_tables

Once you have a decision tree, you can easily make a decision table. Since a decision table is a… table, you can place it in the database.

This may seem more complicated then the previous approach, but bare with me.

If you compare this decision table to the decision tree diagram from before, it covers all the possible outcomes (2 to the power of 3 equals 8 outcomes). In this case, there is only 1 outcome where the room will be available

You now need to join the original table and this decision table, using this code:

SELECT allocations.DayID, allocations.HotelID, allocations.RoomType,

allocations_decision.Available

FROM allocations Left Join allocations_decision on (allocations.SoldOut=allocations_decision.SoldOut)

and (allocations.`Status`=allocations_decision.`Status`)

and (if((allocations.RoomsAvailable>0),True,False)=allocations_decision.AboveZeroRooms)

You have the same results as before, but the approach you used was somewhat more difficult to do.

What did we just do?

We used a decision table to display the possible outcomes of finding out if a room is available and then joined it to the original table.

Is this a good idea?

That is for you to decide. The main advantage is the ability to make changes in the future. You can actually configure the business rules of the system by making changes to the decision tables. Non-developers in particular, can make these changes.

If something happens during the life time of the system, for example changes in the market, then you can react to these changes by configuring your system’s business rules.

You can also use decision tables when using too many “if-then-else” statements that confuse you and you prefer to have a more simplified look at all the conditions.

Conclusion

In this article I have tried to show that you can add business rules to the database. I try to show that using the SQL language helps and simplifies data management and data manipulation. I have also tried to show how to make your database to be configurable so that it can change its business rules when they need to be changed.

Above all, I tried to show that database developing is not so difficult and encourage people to put more emphasis on the database as part of the overall software development process.

Thank you for reading my article.

Email Validators - My Contribution

2008-01-28T12:24:00.002+08:00

So after my recent battle with "dirty" email addresses, I decided to find some validating functions to help people and myself in the future. Most of them use regular expressions.
So here they are:

MySQL stored function
Taken from - http://forge.mysql.com/snippets/view.php?id=62

DELIMITER $$
DROP FUNCTION IF EXISTS `test`.`is_valid_email` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `is_valid_email`(p_email varchar(64)) RETURNS tinyint(1)
BEGIN
CASE
WHEN NOT (SELECT p_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$')
THEN
-- bad data
RETURN FALSE;
ELSE
-- good email
RETURN TRUE;
END CASE;
END $$
DELIMITER ;

In VBA (for excel, access and SQL server) – you need to enable “Microsoft VBScript Regular Expressions 5.5” in the preference

Dim myRegExp As VBScript_RegExp_55.RegExp

Function ValidEmail(ByVal email As String) As Boolean
Set myRegExp = New RegExp
myRegExp.IgnoreCase = True
myRegExp.Global = True
myRegExp.Pattern = "^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$"
ValidEmail = myRegExp.Test(email)
End Function

In JavaScript
Taken from - http://neural.cs.nthu.edu.tw/jang/sandbox/javascript/examples/chkemail.asp

function CheckEmail(str) {

var testresults=true
var filter=/^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*\.(\w{2}|(com|net|org|edu|int|mil|gov|arpa|biz|aero|name|coop|info|pro|museum))$/
if (filter.test(str)){
var tempstring = str.split("@")
tempstring = tempstring[1].split(".")
} else {
testresults=false
}
return (testresults)
}

In Flash ActionScript
Taken from someone at my work. No regular expression.

private function checkEmail(value:String):Boolean{
var mail1= value.split("@")[0]
var mail2= value.split("@")[1]
var mail2_end = mail2.split(".")[mail2.split(".").length-1]
var lastCharASCII = value.charCodeAt(value.length-1)
if(value.split("@").length!=2) return false
if(mail1=="" || mail2== "" || mail2.split(".").length<2)>3) return false;
if((lastCharASCII<65>122)||(lastCharASCII>90 && lastCharASCII<97))>

Hope it helps.

The Journals of a Self Confessed Data Warehousing Noob

2008-01-23T14:55:00.001+08:00

Data Warehousing Continued

Part of the reason I was hired by my company was to help the company’s largest customer connect their data warehouse to the company’s website database. I have data basing experience but I have never been involved in such a large project (don’t tell my bosses). Luckily for me, I wont be doing everything, but for starters, I had to get some customer data, mainly email addresses and build a newsletter mailing list. Sounds simple enough..
Unfortunately for me, I had no idea how difficult it is to take even simple data fields from 2-3 sources and merge it into one. Some of the systems were so old, that back when they recorded the email address, there were no validation rules to enforce the input from the users. I saw emails like this http://www.163.com@yingying837.cn (163.com is a Chinese email server). It seemed to me that the people who inputted this email address only recently purchased a computer.

Anyway, after looking at the long list, running some filters and changing by hand some things, I figured I have 95% cleaned and the rest will be done with the email program to check if the email address still exists.

I was amazed how complicated it is to data cleanse old systems and afterwards make it comply with a data warehouse schema. Especially in the case of data that’s in more the one languages and even more so when it’s a language that uses characters like Chinese.

Conclusion

From now on I plan to put a strong emphasis on data quality from any data base I start working on.
I think it would be good to use data quality methods in my simplified business rules engine idea and this whole thing made me realize about possible directions my “new pet project” might go. I have already written a few things down about it on paper.

Thank you for reading my blog.