Free/open source DB Modelling tools

Hi all,

A quick overview of free and/or open source DB modelling tools.
I use them quite often, depending on the work I have to do and features I need.

• ARGO UML : http://www.argouml.org/
• AZZURI : http://www.azzurri.jp/en/software/clay/index.jsp
• DB DESIGNER 4 : http://www.fabforce.net/dbdesigner4/
• DIA : http://www.gnome.org/gnome-office/dia.shtml
• DRUID : http://druid.sourceforge.net/
• TCM : http://wwwhome.cs.utwente.nl/~tcm/

Feel free to send a comment if you use another tool.

Kettle : transforming number into hh:mm:ss

Hi all,

Another little code for closing the week.
I'm gathering data coming out of PABX systems. Time is stored with numbers.
Sometimes you just don't want to see 3662 seconds but 01:01:02, with [hh:mm:ss] format.

Be carefull, hh:mm:ss is a string format : you won't be able to do calculations nor apply maths functions on it anymore.

Javascript code (sorry for poor blogspot code formating) :

var time_nb = time.getNumber();

var hh,mm,ss;

var TimeFormat;



ss = time_nb % 60;

mm = time_nb / 60;

hh = mm / 60;

mm = mm % 60;



if(hh<10)(hh="0" + hh);

if(mm<10)(mm="0" + mm);

if(ss<10)(ss="0" + ss);



TimeFormat = hh + ":" + mm + ":" + ss;

Email spelling check with Kettle !

Hi all,

Today, I had to check email spelling across some very large files before loading into database.
Kettle helped me here, with some javascript, regular expression and a conditional branch to route the good and bad emails.
Below is the transformation :

And the javascript code ....

//Javascript with regular expressions to test email spelling

// Code by Vincent Teyssier

var email_string = email.getString();

var pattern=/^([a-zA-Z0-9_\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9]{2,4})+$/;

if(pattern.test(email_string))

{

var Emails_status = "good";

}

else

{

var Emails_status = "bad";

}

Database comparison

Hi all,

I want to start a large post dedicated to relational database comparison.
I will focus on Open Source, of course, and will try to do something graphical.
For the moment, have a look to this very interesting wiki, showing some very large comparison matrix.
Worth a look !

Hiring again !!

Hi all,

I'm hiring again for a CDI contract in France : a Business Objects developper.

Mission :
Reporting and BI Development for Belgium clients and day to day support for internal clients.
Mainly Business Objects.
Development is 70% and day to day support is 30%.

Activity :
Call center.

Location :
Paris, Malakoff. With travels to Belgium.

Profile :
Junior, experienced with 2 years using BO in BI projects.
Good knowledge of BO 6.5.x, Webi, Infoview, Designer, Reporter and Supervisor.
Databases : Oracle 9i (development)
ETL : BO DataIntegrator (BODI XI) is a must have or any other significant experience with other ETL (Powercenter, Datastage ...).
VB, .Net ... would be nice.

If you want to apply / meet me, please reach me here (cut nospam) :
vincent.teyssierNOSPAM@sitel.com

Hiring !

Hi all,

I'm looking for an ESSBASE consultant for a 15 days mission in Madrid.
Objectives are :
- Analysis and diagnosis of existing architecture,
- Add multi currency features in 2 cubes,
- Training for actual administrator.
English spoken mandatory.
If you want to apply, please reach me here (cut nospam) : vincent.teyssierNOSPAM@sitel.com

Publishing data from datawarehouse to HTTPS

Hi all,

Last week, I had to publish a datafile to a HTTPS location, for one of our clients here in France.
Easy to do with gui, but how to automate and embed in a DTS or ETL process under Windows ? Not easy.
What did I use ? Open source cURL !!
Here is the recipe I followed.

1 - Install OpenSSL on your machine, Windows compilation of course.
2 - Take cURL, a famous Unix / Linux tool. Take a Windows compilation with SSL support and forget the cygwin version (beurk).
3 - The certificate part, the moste tricky :
Windows / IE use certificates in the form P12 (PKCS12), which is different from the cURL Unix certificate : PEM.
So you have to convert the certificate and extract the userkey file, the usercertificate file and the ca file !
To do the conversion, use the following commands, with OpenSSL. I did it on a Linux box :

First, extract the userkey.pem from the certificate :
vince@fc: openssl pkcs12 -nocerts -in cert.p12 -out userkey.pemEnter Import Password: (insert your certificate password)MAC verified OKEnter PEM pass phrase: (insert your Enter PEM pass phrase)Verifying - Enter PEM pass phrase: (reinsert your Enter PEM pass phrase)

Then extract the usercert.pem file:
vince@fc: openssl pkcs12 -clcerts -nokeys -in cert.p12 -out usercert.pemEnter Import Password: (insert your certificate password)MAC verified OK

Finally, extract the ca.pem file :
vince@fc: openssl pkcs12 -cacerts -nokeys -in cert.p12 -out ca.pemEnter Import Password: (insert your certificate password)MAC verified OK

4 - Then you can type your cURL command :
curl -u toto:tata -E certif.pem:passphrase --cacert ca.pem --proxy-ntlm -U FRLAR01\toto:tata -x proxyserver:8080 https://213.41.176.116/quix -v -T D:\FLATFILES\Client\Export\File_to_export.txt

Explanation of the command :
curl is the command, of course
-u is for user:password to the HTTPS site. Here I anonymised of course ...
-E is for the new converted client certificate in pem format
--cacert is for giving the ca certificate in pem format
--proxy-ntlm is for allowing proxy to connect in case of a proxy usage
-U is the credentials for the proxy. Here again I anonymised the data
-x is the proxy:port adress
-https ... is the destination adress on https
-v is for verbose mode. Usefull.
-T is for indicating the file to transfert. Here the file is name File_to_export.txt and is a txt datafile

You can see here I choose to use the proxy on each command, instead of setting up the proxy on my server. This is why I don't need any ISA proxy client on my datawarehouse server, so by using a direct proxy connection when needed, I save some server resources.

I hope this will help the community for any next HTTPS data push.