Database: Unique Identifier or Identity

In most of the RDBMS, DBA or DB developer has always considering whether he/she should use Unique Identifier (GUID) or Identity (Auto-Increment, sequence etc).


To be frank, many experienced DBAs have already known the answer long long time ago. Perhaps, some of them might have already posted this topic somewhere else on the Internet.


So, what do experts recommend? IDENTITY, if possible.


Why?
1. IDENTITY column is always unique and sorted in a sequence that we want (ascending or descending)
2. With these unique characteristics, it saves a lot of resources when INSERTING data into the table
    i.   new data always goes to the bottom of the BTree/Heap
    ii.  database engine itself does not need to adjust the BTree when new data come in
    iii. it is much easier to partition the data (well, it depends on your data partitioning strategy)
    iv. if this column is a PK or clustered index, it will impact the storage requirement of your non-clustered indexes. (For MSSQL, Unique Identifier is 16 bytes while BIGINT is 8 bytes) As you know, each non-clustered index will contain your clustered index key.
3. You hardly perform UPDATE on IDENTITY column (and it is not a good practice to update IDENTITY column)


However, there are some challenges that we might face when using IDENTITY
1. Limit of BIGINT is 9,223,372,036,854,775,807
    - in BIG DATA era, it becomes possible to hit the limit
2. Data migration between databases is not straight forward
    - we have to put extra effort to ensure 100% data integrity

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)