Database Backup - Differences between Oracle, MSSQL, and MySQL

Oracle 10g
(refer http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch23.htm)


A. Full Backup
i)  NOARCHIVELOG mode - best for one-time database restore e.g. create testing/development database
ii) ARCHIVELOG mode - best for database recovery


B. Partial Backup
i)   backup all datafiles under ONE tablespace
ii)  backup SINGLE datafile
iii) backup a control file
> useful under ARCHIVELOG mode so that upon restore, changes will be applied based on archived redo log.


C. Incremental Backup
i)  differential incremental backup - changes in data blocks since most recent backup at the same level or lower
ii) cumulative incremental backup - changes in data blocks since most recent backup of the next lowest level


MSSQL 2005/2008/2012
Concepts of Recovery Model
i)   Simple - no support for log backup
ii)  Bulk-Logged - minimal logging for bulk operation. DMLs are still logged. Bulk operation after last backup cannot be recovered.
iii) Full - support point-in-time recovery
(refer http://msdn.microsoft.com/en-us/library/ms189275.aspx)


A. Full Backup - data backup (all online data files and transaction logs) at certain point of time.
                        - if there is any offline datafile, full backup operation will fail. administrator has to perform partial backup instead.
    (refer http://msdn.microsoft.com/en-us/library/ms175477.aspx)


B. Log Backup - changes in transaction log based on last full/differential/log backup (only available when recovery model is set to FULL or Bulk-Logged)
    (refer http://msdn.microsoft.com/en-us/library/ms191429.aspx)


C. File Backup - backup all the data in one or more online files or filegroups (a filegroup that contains 1 or more offline datafile will be considered as offline filegroup. therefore, it cannot be backed up)
    (refer http://msdn.microsoft.com/en-us/library/ms189860.aspx)


D. Partial Backup - useful when we want to exclude read-only filegroups
    (refer http://msdn.microsoft.com/en-us/library/ms191539.aspx)


E. Differential Backup - changes since latest full/partial backup (a.k.a. cumulative incremental backup)


MySQL 5.0/5.1/5.5
(refer http://dev.mysql.com/doc/refman/5.0/en/backup-types.html)


A. Full Backup 
    - complete database backup
    - Command: mysqldump (for MyISAM/Archive table, can use mysqlhotcopy)
        - does not include INFORMATION_SCHEMA
        - does not include PERFORMANCE_SCHEMA
        - does not include ndbinfo
        - does not include log/configuration file
(refer http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html and http://dev.mysql.com/doc/refman/5.5/en/mysqlhotcopy.html)


B. Incremental Backup 
    - MUST enable --log-bin on startup (or specify log_bin and expire_log_days in my.cnf)
    - flush bin log to file for database recovery later (similar to log backup)


C. Snapshot Backup - capability of file system. provided by third party (Veritas, LVM, ZFS)


Note: physical backup vs logical backup, online backup vs offline backup, import/export data and compressed backup are not covered in this article.


next post will discuss how to use database backups to perform recovery.

PHP Yii SWFUpload 302

Possible Root Cause:
SWFUpload opens a new PHP session when connecting to Apache and uploading file(s) to web server's file system.


Solution:
1. Remember to pass in postParams
    $this->widget('application.extensions.swfupload.CSwfUpload', array(
        'jsHandlerUrl'=>'js/swfupload/handlers.js',
        'postParams'=>array('PHPSESSID'=>session_id()),
        'config'=>array( ...


2. In the Controller, modify accessRules() to allow any user to call the action that is going to be used by SWFUpload.
    Below is are sample codes in my View file:
    
$uploadUrl=$this->createUrl('photo/swfupload');
$this->widget('application.extensions.swfupload.CSwfUpload', array(
   'jsHandlerUrl'=>'js/swfupload/handlers.js',
   'postParams'=>array('PHPSESSID'=>session_id()),
   'config'=>array(
'debug'=>false,
       'use_query_string'=>true,
       'upload_url'=>$uploadUrl,
       'file_size_limit'=>'5 MB',
       'file_types'=>'*.jpg;*.png;*.gif',
       'file_types_description'=>'Image Files',
       'file_upload_limit'=>0,
       'file_queue_error_handler'=>'js:fileQueueError',
       'file_dialog_complete_handler'=>'js:fileDialogComplete',
       'upload_progress_handler'=>'js:uploadProgress',
       'upload_error_handler'=>'js:uploadError',
       'upload_success_handler'=>'js:uploadSuccess',
       'upload_complete_handler'=>'js:uploadComplete',
       'custom_settings'=>array('progressTarget'=>'divFileProgressContainer','cancelButtonId'=>'btnCancel'),
       'button_placeholder_id'=>'swfupload',
        'button_image_url'=>'js/swfupload/images/TestImageNoText_100x20.png',
       'button_width'=>50,
       'button_height'=>20,
       'button_text'=>'  Select File(s)  ',
       'button_text_style'=>'.button { font-family: "Lucida Sans Unicode", "Lucida Grande", sans-serif; font-size: 9pt; text-align: center; }',
       'button_text_top_padding'=>0,
       'button_text_left_padding'=>0,
       'button_window_mode'=>'js:SWFUpload.WINDOW_MODE.TRANSPARENT',
       'button_cursor'=>'js:SWFUpload.CURSOR.HAND',
       ),
   )
);
?>


$uploadUrl); ?>

   

   

   

   

    As you can see, photo/swfupload is my controller/action pair。
    So, in photoController file, we shall change the accessRules() function to allow anyone to call actionSwfupload(). Below are my sample codes:
    public function accessRules()
{
return array(
array('allow',  // allow all users to perform 'index' and 'view' actions
'actions'=>array('index','view','swfupload'),
'users'=>array('*'),
),
);
}


Please feel free to share your experience on this issue.

PHP Yii Complex SQL

Yii allows developers to construct SQL statement.
This will help advanced developer to tune his/her SQL query (by using appropriate indexes).


Example:
$connection=Yii::app()->db;

$sql = "SELECT t1.colA, t2.colB, t3.colC, t3.colD 
           FROM tableA t1 
           LEFT JOIN tableB t2 ON t1.id=t2.id 
           LEFT JOIN tableC t3 ON t2.pid=t3.pid
           WHERE t1.id BETWEEN 10 AND 100 
           ORDER BY t1.modifyDate DESC";
$command = $connection->createCommand($sql);
$dataReader = $command->query();
$result = $dataReader->readAll();


Internal:
$command=createCommand($query)
Note: $command is a CDbCommand
|- $dataReader = query()
   Note: $dataReader is a CDbDataReader
   |- CDBDataReader->readAll() returns array()

PHP Yii ActiveRecord FindAllBySql()

Definition:
public array findAllBySql(string $sql, array $params=array ( ))
(refer http://www.yiiframework.com/doc/api/1.1/CActiveRecord#findAllBySql-detail)


Explanation:
Find all active records using specified SQL statement


Example:
1. $model = MyModel::model()->findAllBySql('SELECT colA, colB FROM any_table');
2. $model = MyModel::model()->findAllBySql($sqlString);
3. $model = MyModel::model()->findAllBySql('SELECT * FROM any_table WHERE colA=:param', array(':param'=>$value));


Internal:

findAllBySql($sql, $param)
|-$command = createSqlCommand($sql,$params)
  Note: $command is a CDbCommand
  |-populateRecords($command->queryAll())

PHP Yii ActiveRecord FindAllByPk()

Definition:
public array findAllByPk(mixed $pk, mixed $condition='', array $params=array ( ))
(refer http://www.yiiframework.com/doc/api/1.1/CActiveRecord#findAllByPk-detail)


Explanation:
Find all active records with specified primary key


$pk can be either 
1. string -> single-key primary key
2. array  -> single-composite-key primary key
(refer createPkCriteria in yii\framework\db\schema\CDbCommandBuilder.php)


Example:
1. $model = MyModel::model()->findAllByPk($id);
2. $model = MyModel::model()->findAllByPk(array('pkCol1'=>$col1, 'pkCol2'=>$col2));


Internal:

findAllByPk($pk, $condition, $param)
|-$criteria = createPkCriteria($this->getTableSchema(),$pk,$condition,$params,$prefix)
  Note: $criteria is a CDbCriteria
  |-query($criteria, TRUE)
    Note: TRUE means select all active records

PHP Yii ActiveRecord findAllByAttributes()

Definition:
public array findAllByAttributes(array $attributes, mixed $condition='', array $params=array ( ))
(refer http://www.yiiframework.com/doc/api/1.1/CActiveRecord#findAllByAttributes-detail)


Explanation:
Finds all active records that have specific attribute values


Example:
1. $model = MyModel::model()->findAllByAttributes(array('id'=>1, 'colA'='mystring'));
2. $model = MyModel::model()->findAllByAttributes(array('id'=>1, 'colA'='mystring'), "colB='mystring2'");
3. $model = MyModel::model()->findAllByAttributes(array('id'=>1, 'colA'='mystring'), "colB=:param", array(":param"=>'mystring2'));


Internal:

findAllByAttributes($attribute, $condition, $param)
|-$criteria = createColumnCriteria($this->getTableSchema(), $attributes, $condition, $param, $prefix)
  Note: $criteria is a CDbCriteria
           $prefix is defaulted to NULL. When calling findAllByAttributes, this function will not pass in $prefix. Therefore, in this case, $prefix is always NULL.
  |-query($criteria, TRUE)

PHP Yii ActiveRecord FindAll()

Definition:
public array findAll(mixed $condition='', array $params=array ( ))
(refer http://www.yiiframework.com/doc/api/1.1/CActiveRecord#findAll-detail)


Explanation:
Find all active records that satisfy specific conditions
findAll() returns array instead of CAtiveRecord


Example:
1. $model = MyModel::model()->findAll("id=1");



Internal:

please refer find() for explanation of $condition.

findAll($condition, $param)

|-$criteria = createCriteria($condition, $param)

  > Note: $criteria is a CDbCriteria

  |-query($criteria, TRUE)

  Note: TRUE has been passed in so that system will retrieve all active records

(refer yii\framework\db\ar\CActiveRecord.php)

PHP Yii ActiveRecord Find()

Definition:

public CActiveRecord find(mixed $condition='', array $params=array ( ))

(refer http://www.yiiframework.com/doc/api/1.1/CActiveRecord#find-detail)

Explanation:

Find a single active record with specified conditions

Always return first record in the result set

$condition can be either

1. array

2. CDbCriteria

3. string

When $condition is an array, CDbCriteria class will map each key to predefined field name. Below is a list of predefined field name:

a. $select [default = '*']

b. $distinct [default = FALSE]

c. $condition [default = '']

d. $param [default = array()]

e. $limit [default = -1]

f. $offset [default = -1]

g. $order [default = '']

h. $group [default = '']

i. $join [default = '']

j. $having [default = '']

k. $with - alias of the table name [default = 't']
l. others - $alias, $together, $index, and $scopes

When $condition is a string, system will map it to CDbCriteria->$condition (item c above)

Example:

1. $model = MyModel::model()->find("id=1");

2. $model = MyModel::model()->find("id={$id}");

3. $model = MyModel::model()->find("value='mystring'");

4. $model = MyModel::model()->find("value='{$mystring}'");

5. $model = MyModel::model()->find("id=1 ORDER BY colA ASC");

6. $model = MyModel::model()->find("id=:param", array(":param"=>1));

7. $model = MyModel::model()->find("id=:param1 AND colA=:param2", array(":param1"=>1, ":param2"=>'mystring'));
8. $model = MyModel::model()->find(array('select'=>'colA', 'condition'=>'id=1', 'order'=>'colB DESC'));

Internal:

find($condition, $param)

|-$criteria = createCriteria($condition, $param)

  Note: $criteria is a CDbCriteria

  |-query($criteria, FALSE)

    Note: FALSE means add "LIMIT 1" to SELECT statement

(refer yii\framework\db\ar\CActiveRecord.php)