Thursday, June 26, 2014

Example of MySQL foreign key constraint in relational database design

In relational database design, some of tables have several foreign keys, which point to parent tables.
When the rows in  parent tables  are deleted or updated, what is the behavior of child tables?
Are they deleted or updated accordingly, or et to NULL, or reject the parent table delete/update?
Suppose we have users table:
user_id int NOT NULL,
user_name varchar(45) NOT NULL,
PRIMARY KEY (user_id)

We have another table  pictures,  user_id is foreign key which is primary key in table users.
Using foreign key to prevent  insert user_id in table pictures which is invalid in table users.
We can use  naming of foreign key constraint, which can be used in drop foreign key.

pic_id  int NOT NULL,
user_id int NOT NULL,
PRIMARY KEY (pic_id),
CONSTRAINT fk_pictures FOREIGN KEY (user_id) REFERENCES users(user_id)

 Now we can drop the FOREIGN KEY constraint using the following SQL:
ALTER TABLE pictures DROP FOREIGN KEY fk_pictures

When the primary key is updated or deleted on parent table, what is the behavior of the child table?
 We have four options:

CASCADE: automatically delete or update the child table when the parent table is deleted or rejected
SET NULL: the foreign key table is set NULL  child table when the parent table is deleted or rejected
RESTRICT:  Rejects the delete or update operation for the parent table

('1', 'jiansen'), ('2', 'andy')

INSERT INTO `pictures`  VALUES('1', '1')
will be OK
INSERT INTO `pictures`  VALUES('2', '3')
will fail due to that   user_id=3 is not in users table.

Video:  MySQL foreign key constraint

Monday, June 9, 2014

Run Linux command asking input or password in background

When we run a Linux command in background such as
scp test.sql &
or  ignore the hangup signal
nohup scp test.sql &
We can not input  password.
To make an input for  background job, First we use
scp test.sql
input password, then type Ctrl +Z to suspend the job
then type
to put it in background.
To list all process
ps aux
To list process containing scp and find process id
ps aux |grep scp
To kill a process
kill processid
To force to kill, this is strong:
kill -9 processid

Friday, June 6, 2014

Install PHP ibm_db2 extension in Linux (redHat)

Install PHP ibm_db2 extension in Linux (redHat) to make sb2_connect function worinkg in Linux:
1) check Linux version
uname -a
 uname -m
2) Go to ibm db2 download website to Download Data Server Driver Package

fix pack: DSClients-linuxx64-dsdriver-

IBM Data Server Driver Package (Linux/x86-64 64 bit) V10.5 Fix Pack 3
This is for PHP5.3
3) sudo mkdir /opt/ibm
cd /opt/ibm

download v10.5fp3_linuxx64_dsdriver.tar.gz

and upload to /opt/ibm
4) sudo tar -xvf v10.5fp3_linuxx64_dsdriver.tar.gz
cd /opt/ibm/dsddriver
sudo chmod 755 installDSDriver
5) Install ksh
sudo yum install ksh
6) Install ibm_db2 driver
sudo ksh installDSDriver
7) To make  phpize working which is needed for sudo pecl install ibm_db2
sudo yum install php-devel
8) sudo pecl install ibm_db2
export IBM_DB_HOME=/opt/ibm/dsdriver
sudo pecl install ibm_db2
9) in db2 install directory message, enter
10) return result
Build process completed successfully
Installing '/usr/lib64/php/modules/'
install ok: channel://
configuration option "php_ini" is not set to php.ini location
You should add "" to php.ini

11)  locate php.ini
 sudo vi  /etc/php.ini
13) Restart Apache
 sudo service httpd restart


Thursday, June 5, 2014

PHP connect IBM db2 database in XAMPP

php_ibm_db2.dll is needed for PHP db2_connect function to connect to IBM db2 database.
1) Go to (for Windows)
2) Click PHP 5.4._
Latest xampp is PHP5.5, but latest php_ibm_db2.dll is for PHP5.4
I have to install two version of XAMPP, one is PHP5.5, another PHP5.4 for php_ibm_db2.dll
3) Download
and unzip
4) Copy php_ibm_db2.dll to php/ext/ directory
5) In php.ini, add
6)Restart Apache in XAMPP
7) My PHP test code to connect to IBM db2 database, replace to your database name, user name and password
//db2 express c  (v10.5) in local
$database = "SAMPLE";
$user = "db2admin";
$password = "admin123";

$conn = db2_connect($database, $user, $password);

if ($conn) {
echo "Connection succeeded.";
else {
echo "Connection failed.";

For Linux:
Video: PHP connect IBM db2 database in XAMPP

Tuesday, June 3, 2014

Linux, automatically backup MySQL database daily

Backup MySQL database daily using cron job
1) create a bk directory under home directory, in my case /home/jiansen,
cd bk
2) Create a shell scrip
mysqldump  -u root -pmypass mydb | gzip >MySQLDB_`date +"%Y%m%d"`.sql.gz
replace root,  mypass, mydb to your databse username, password and database name
date +"%Y%m%d
is shell comand to produce year, month and day, for example  20140603
example output
3) Create a cron job
 crontab -e
30 23 * * * /home/jiansen/bk/ 2>&1>>/home/jiansen/bk/mysqbackup.log

each night  23:30pm run  /home/jiansen/bk/ and dump errors
to logfile 
4) to list cron job
  crontab -l
5) To remove cron job
  crontab -r
Video: Linux, automatically backup MySQL database daily