Database Notes

Wanna build your schema online and share?

WW SQL Designer

Wanna test your SQL query online and share?

SQL Fiddle

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

Common MySQL Queries

If you don't fully understand JOINs:

A Visual Explanation of SQL Joins

Wanna hot-backup your InnoDB database?

Percona XtraBackup (open source)

Need to recover a corrupt InnoDB table?

Percona Data Recovery Tool for InnoDB (open source)

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

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();
}

Here is my new blog about learning Ext JS

ExtJS is a great rich internet application library, written in javascript

http://extjs-tutorials.blogspot.com/

Abandoning Jquery and Server-Generated HTML, moving to ExtJS + Yii backend

Our team wasted far too long trying to get our web app working with jQuery. Finally we discovered the joys of ExtJS 4, and are moving all development there.

Still using Yii as the backend for its nice models and controllers, as well as RBAC.

Which databases does Yii support?

Yii uses PDO, which is good, as it supports parameters, which can help to prevent SQL injection attacks.

As of Yii 1.1.8, it supports the following:

• PostgreSQL
• MySQL
• sqlite 3 and sqlite 2
• Microsoft SQL Server
• Oracle

Yii also supports dblib

Some other useful database features supported by Yii include:

• transactions
• schema caching for ActiveRecord
• query caching
• null conversion
• database cache dependencies
• performance profiling

Use session instead of stateful forms

Here's why:

1. If you decide to use AJAX later on (in particular I mean to HIJAX your forms), you'll have to set and get the YII_PAGE_STATE variable for every action, which is a pain in the ass. 
2. page state is broken when you try to use partial rendering. Even if the partially rendered form is a complete CActiveForm, YII_PAGE_STATE will end up blank. 

I am referring to pages that post back to themselves and that have related sub-items for which you need to maintain state. 

How to install phpunit on Windows

the PHPUnit installer appears to be written by someone with mild autism....there's no other explanation for it.

either way, here's how i was able to install phpunit:


open the command prompt, type: (enter for each line)

1. pear channel-update pear.php.net
2. pear upgrade-all
3. pear channel-discover pear.phpunit.de
4. pear channel-discover components.ez.no
5. pear channel-discover pear.symfony-project.com
6. pear update-channels
7. pear install -a -f phpunit/PHPUnit