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)

Conexant High Definition Audio SmartAudio 221 driver

I have re-formatted my laptop last weekend. Well, my laptop model is HP Pavilion dv2715tx. The reason I rebuild my laptop is to upgrade operating system from Vista to Windows 7 (64 bit). Basically, everything went smooth. dv2715tx is an entertainment laptop. So, there is a HDMI port for me to connect my laptop to my HD TV.

Ops, after I connected my laptop to TV, I have no problem to see my desktop gets populated on my TV. However, audio can only be played via my Altec Lansing speaker, which comes together with my laptop.

I am not a perfectionist. But, I do hope that this issue can be resolved. I had been googling around and find no luck. It seems that Coxenant has no public web site for user to download its audio driver directly.

I went to HP official web site to download driver for Vista 64 bit. I also went to Lenovo web site to download the same driver. In the end, I was not able to get any of the driver works.

Okay, I am done with my research. I was thinking to update my windows first then only come back to this issue. Ah huh ..... I found a Coxenant High Definition SmartAudio 221 driver in Windows Update! Cool! I unchecked all other windows updates and install only Coxenant High Definition SmartAudio 221 driver. Bingo! It works! Existing "High Definition Audio" driver has been replaced by "Coxenant High Definition SmartAudio 221 driver" when I performed this update (of course, I need to restart my laptop when the update is done).

Phew ... finally, me and my family can continue to enjoy watching movie via my laptop. Thanks to Microsoft!

Oracle 9i: Introduction to SQL - SQL Fundamentals

Types of SQL

1. Data Manipulation Language (DML)

    a. SELECT - retrieve data

    b. INSERT  - store data

    c. UPDATE - modify data

    d. DELETE - remove data

    e. MERGE - store/modify data

    f. EXPLAIN PLAN - show execution plan

    g. LOCK TABLE - restrict access to a table

2. Data Definition Langauge (DDL)

    a. CREATE - create new database object

    b. ALTER - alter existing database object

    c. DROP - remove an existing object permenantly

    d. RENAME - modify the name of an existing object

    e. TRUNCATE - remove all rows from a table without dropping its structure

    f. GRANT

    g. REVOKE

    h. AUDIT

    i. NOAUDIT

    j. COMMENT - add info into data dictionary

3. Transaction Control

    a. COMMIT

    b. ROLLBACK

    c. SAVE POINT

    d. SET TRANSACTION

4. Session Control

    a. ALTER SESSION

    b. SET ROLE

5. System Control

    a. ALTER SYSTEM

Data Types

1. Character

    a. CHAR (n); 1 <= n <= 2000 bytes, default = 1 

    b. NCHAR (n)

    c. VARCHAR2 (n); 1 <= n <= 4000 bytes; MUST specify value for n

    d. NVARCHAR2 (n)

2. Number

   a. NUMBER (n,m); 1 <= n <= 38, -84 <= m <= 127; IF n and m are missing, assume NUMBER (38, 127) or NUMBER (38, -84)

3. Long and Raw

4. Date and Time

5. Large Object

6. Row ID

Operators

1. Arithmetic Operators

    -> +/-, +, -, *, /

2. Concatenation Operator

    -> ||

3. Set Operators

    -> UNION, UNION ALL, INTERSECT, MINUS

4. Logical Operators

    -> NOT, AND, OR

5. Others

    -> IN, NOT IN, BETWEEN...AND, EXISTS, IS NULL, IS NOT NULL, LIKE

** Operator precedence: +/- then *, / then +, -, ||

Literals (constant values)

1. Text: 'this is a line of text'

2. Integer: 12, -90 (maximum up to 38 digits)

3. Number: 3, -4.23, 7E-10

4. Interval

CASE Expression

1. CASE column WHEN equal_to_value THEN return_value ELSE default_return_value END

2. CASE WHEN condition THEN return_value ELSE default_return_value END

Notes:

1. Keywords, table names and column names are case insensitive

2. DUAL table is a single row and single column table

3. By default, NULL values are sorted last (unless NULLS FIRST is specified)

4. Equility test for NULL will return 0 row (e.g. WHERE column = NULL)

About My SQL Server Performance Tuning - Day 4 & Day 5

Day 4:

 1. Turn on SQL Trace to capture performance of tuned scripts

Day 5:

 1. Present tuning results

     a. CPU usage

     b. Memory usage

     c. Hard disk I/O

     d. Network usage

     e. Overall server performance

     f. Overall database performance (peak & non-peak hours)

     g. Tuned scripts' performance

About My SQL Server Performance Tuning - Day 3

Day 3:

 1. Apply all scripts into database

     a. Index tuning scripts

     b. Plan guide

     c. Add/Drop/Move data/log files

     d. Adjust TempDB

     e. Create database maintenance plans

Note:

 1. OLTP and OLAP have different tuning objectives. OLTP aims for faster insertion while OLAP aims for faster data retrieval.

 2. Under certain circumstances, we may need to create new table(s) to achieve certain objectives (normally for reporting purposely)

 3. Sometimes, we, as DBAs, may not have the influence over front  end application. Plan guide may only provide limited capability to tune the queries.

 4. It is very rare that we need to modify existing database structure (this require too much programming effort)

 

About My SQL Server Performance Tuning - Day 2

Day 2:

 1. Group similar queries together (by replacing parameters with @var1...n)

     a. Calculate AVERAGE READS, WRITES, CPU and DURATION for each group of queries

         -> serve as baseline of tuning

         -> tuned queries should be executed with less resource, which means lower than baseline

     b. Count frequency of execution for each group of queries

         -> perform impact analysis

     c. Select queries that can represent overall picture of database performance

         -> Approach 1: select a single query from each group that is close to AVERAGE

         -> Approach 2: select the slowest query from each group

         -> Approach 3: select MOD (highest frequency) query from each group (have to divide same group of queries into multiple ranges)

 2. Schedule a SQL Job to run selected queries at certain interval (hourly, 2-hourly etc.)

     -> make sure we cover both peak and non-peak hours of a day

     -> one job step runs ONLY ONE query

 3. Turn on SQL Trace

 4. On separate environment, prepare scripts to tune queries

     a. Index tuning

         i.   Update statistics

         ii.  Rebuild index (adjust fill factor if necessary)

         iii. Create/Drop indexes

         -> SQL Server 2005 has a dynamic management view to check index usage

         -> remove unused indexes

         -> check recommendation for missing index

     b. Create plan guide

     c. Create/Remove/Move data/log files

     d. TempDB adjustment

     e. Create database maintenance plans

End of the day:

 1. Capture all values for selected baseline queries