Tuesday, July 10, 2012

How to make a symlink in Windows 7


  1. open cmd (as an admin)
  2. type:
mklink /d linkpath targetpath

example:

mklink /d c:\easytoremember c:\users\neil\local\applicationdata\temp\hardtoremember

Monday, April 16, 2012

on Database Trees

This is more a note to myself, but it looks like the best tree option in a database is Recursive Query, at least in Postgres, which supports the WITH clause.

Closure Table is the next best option.

Sunday, April 15, 2012

Optimistic Concurrency Control with Yii's Activerecord

Here's how to do Optimistic Concurrency Control with Yii's ActiveRecord:
class OCCActiveRecord extends CActiveRecord{

  public $checksum = null;

  public function afterFind(){

    /* get a checksum of all the attribute values after reading a record from db */

    $this->checksum = md5(implode('', $this->getAttributes(false)));

    parent::afterFind();
  }

    public function rules(){

/* use an exist validator to make sure that a record with the same checksum is still in db. if it's been modified, then checksums will be different */

 return array(
     array('id', 'exist', 'message'=>'This record was modified after you read it', 'on'=>'update', 'criteria'=>array('condition'=>'md5(concat('.implode(',',$this->attributeNames()).'))=:checksum', 'params'=>array('checksum'=>$this->checksum)))
 );
    }

}
Just a rough sketch, but you get the idea.

You could also modify this to be a behavior. Works only with MySQL AFAIK.

Table Inheritance with Yii ActiveRecord

Need to use Table Inheritance with ActiveRecord? Here's how:

This is Single Table Inheritance only.

Imagine that you want People contacts and Organization contacts. You don't want to keep them in different tables, as sometimes you sell to people, and sometimes you sell to organizations, and you want to keep a single (foreign) key to the party that you sold to.

We will create a class called Party, with subtypes Person and Organization. They will share some of Party's rules and relationships.

Here is the database schema:

CREATE TABLE `tbl_party` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `type` char(1) NOT NULL comment 'p=person,o=organization',
 `name` varchar(255) NOT NULL comment 'surname if a person',
 `given_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;

and here is the model code:

class Party extends CActiveRecord {

    public static function model($className=__CLASS__){
 return parent::model($className);
    }

    public function tableName(){
 return 'tbl_party';
    }

    

    public function relations(){
 return array(
     
 );
    }

    public function rules(){
 return array(
     array('name,type','required'),
     array('type','in', 'range'=>array('o','p'))
 );
    }
}

class Person extends Party {

    /* this is required for subtypes */
    public static function model($className=__CLASS__){
 return parent::model($className);
    }

    public function defaultScope(){
        /* only read Parties that are People */
 return array(
     'condition'=>"type='p'"
 );
    }

    public function relations(){
        /* combine parent and own relations. can have 'People' only relations  */

 $parentRelations = parent::relations();

 $myRelations = array(

 );

 return array_merge($myRelations, $parentRelations);
    }

    public function rules(){
        /* combine parent and own rules */

 $parentRules = parent::rules();

 $myRules = array(
     array('type', 'default', 'value'=>'p'), /* set type to Person */
     array('type', 'in', 'range'=>array('p')), /* allow only Person type */
     array('given_name', 'required') /* new rule for this subtype only */
 );

 /* you want to apply parent rules last, delete them here if necessary */
 return array_merge($myRules, $parentRules);
    }

}

class Organization extends Party {

    public static function model($className=__CLASS__){
 return parent::model($className);
    }

    public function defaultScope(){
 return array(
     'condition'=>"type='o'"
 );
    }

    public function relations(){
 $parentRelations = parent::relations();

 $myRelations = array(
     
 );

 return array_merge($myRelations, $parentRelations);
    }

    public function rules(){
 $parentRules = parent::rules();

 $myRules = array(
     array('type', 'default', 'value'=>'o'),
     array('type', 'in', 'range'=>array('o'))
 );

 /* you want to apply parent rules last. delete them if necessary */
 return array_merge($myRules, $parentRules);
    }
}

Thursday, February 9, 2012

Database Notes

Wanna build your schema online and share?


Wanna test your SQL query online and share?


This might come in handy for you, if you are struggling with a SQL query:


If you don't fully understand JOINs:


Wanna hot-backup your InnoDB database?

Percona XtraBackup (open source)

Need to recover a corrupt InnoDB table?


Need to generate massive test data?

Benerator

Want to Validate your SQL?

Mimer SQL Validator



Random:
  1. if you want decent full-text search, choose PostgreSQL over MySQL, as MySQL doesn't support free-text search at the same time as foreign keys and transactions.
  2. Set your database to use the UTC timezone




Wednesday, November 23, 2011

How to read a JSON POST with Yii, and save it to the database

Let's say you are sending a json-encoded object to your create action, and want to save it in your database. here's how:

public function actionCreate() {
 
//read the post input (use this technique if you have no post variable name):
  $post = file_get_contents("php://input");

  //decode json post input as php array:
  $data = CJSON::decode($post, true);

  //contact is a Yii model:
  $contact = new Contact();

  //load json data into model:
  $contact->attributes = $data;
//this is for responding to the client:
  $response = array();

  //save model, if that fails, get its validation errors:
  if ($contact->save() === false) {
    $response['success'] = false;
    $response['errors'] = $contact->errors;
  } else {
    $response['success'] = true;
    
    //respond with the saved contact in case the model/db changed any values
    $response['contacts'] = $contact; 
  }

  //respond with json content type:
  header('Content-type:application/json');
  
//encode the response as json:
  echo CJSON::encode($response);

  //use exit() if in debug mode and don't want to return debug output
  exit();
}