MySQL Data Types Digest

Type Storage Minimum Value Maximum Value
(Bytes) (Signed/Unsigned) Signed/Unsigned)
TINYINT 1 -128 127
0 255
SMALLINT 2 -32768 32767
0 65535
MEDIUMINT 3 -8388608 8388607
0 16777215
INT 4 -2147483648 2147483647
0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
0 18446744073709551615

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. As of MySQL 5.0.3, they also differ in maximum length and in whether trailing spaces are retained.

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column’s maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode.

For VARCHAR columns, trailing spaces in excess of the column length are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.

VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values.

Value CHAR(4) Storage Required VARCHAR(4) Storage Required
'' '    ' 4 bytes '' 1 byte
'ab' 'ab  ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

for more info:

http://dev.mysql.com/doc/refman/5.0/en/char.html

===============

 

Agile Toolkit Hooks ad Controllers Digest

There are the following hooks available for the model (at least):

  • beforeLoad($model, $query) – called before loading SQL query is executed. You have a chance to modify that query. This is called for both model->load() and for iterating through model with foreach($model). This hook is great for applying extra options to your SQL query.
  • afterLoad($model) – called after data have been loaded from SQL. You can now access $model->get() and the model will appear to be loaded. Called for both model->load() and iterating. This hook is great for performing data manipulation and normalization.
  • beforeSave($model) – called when $model->save() is called. This is called inside SQL transaction, so database changes you perform here will be rolled back if save would be unsuccessful. This hook is great for performing data modification before it’s been saved. You can check $model->loaded() to see if a new record is being stored or updated
  • beforeInsert($model, $query) – called when inserting new data and after the insert query is being formed. That query is passed as 2nd argument. This hook is great for changing insert query options.
  • afterInsert($model,$id) – called after insertion method is performed successfully, but before model is re-loaded. You can break out of this hook and return a substitute model. Great for overriding how model is reloaded after insert.
  • beforeModify($model,$query) – called before update SQL query is executed. This hook is great for changing update query options.
  • afterModify($model) – called after SQL query is executed but before reloading has taken place. Note that if you access set() / get() here it will be reloaded by a subsequental reload.
  • afterSave($model) – called after model have been successfully reloaded. This is the last hook to be executed before SQL transaction is finished with commit. Please note taht beforeLoad / afterLoad will also be called during the reloading of a model. This hook is great for hiding some fields from a model after they are being saved such as wiping your password field.

Few more recommendation on query use.

  • to perform additional validation use beforeSave().
  • to measure speed of your query use beforeInsert / afterInsert and beforeModify and afterModify respectively.
  • beforeLoad will be called once per query, but afterLoad may be called several times when iterating through results.
  • apply SQL options in beforeLoad, beforeInsert and beforeModify
  • if you want to divert the query to a different database connection beforeLoad, beforeInsert and beforeModify are good place, but you must enhance DSQL to support switching before databsae handles. If this is what you need, please discuss on Agile Toolkit Development Forum.

 

class Model_Book extends Model_Table {
  function init(){
    parent::init();

    $this->addHook('beforeSave,afterLoad',$this);
  }

  function beforeSave(){
    // manually update some fields before saving. This is to create a indexable field for full-text search
    $this['book_search_field'] = $this['title'].' '.$this['descr'].' '.$this['author_name'];

    // let's also perform some validation
    if(strlen($this['book_name']<10))throw $this->exception('Name of the book is too short');

    // normalization will modify field to match some internal rules
    $this['book_url']=$this['book_url'] ?: preg_replace('/[^a-zA-Z0-9]/','-',trim($this['name']));
  }
  function afterLoad(){
    $this['name'] = $this->api->_($this['name']); // wraps name through localization function
  }
}

======

 

Quotes Digest!

Your skin is not paper, don’t cut it.
Your face isn’t a mask, don’t hide it.
Your size isn’t a book, don’t judge it.
Your life isn’t a film, don’t end it.

=======================

I hate to see you leave, and love to watch you go 🙂

=======================

I wake up every morning, blessed. Why? Because, I woke up.

=======================

‘H.O.P.E. = Hold On, Pain Ends!’

=======================

Continue reading “Quotes Digest!”

Agile Toolkit Database Digest

Connecting to Database

Agile Toolkit API has a function dbConnect() which will automatically read DB configuration from your configuration file and initialize connection. When connection is created, the connection object is accessible through through $api->db property. If you wish, you can create connections to other databases by calling:

$dsn=array('mysql:host=localhost;dbname=testdb', $username, $password, $options);
$mydb=$this->add('DB')->connect($dsn);

.

Creating Query Object

DSQL objects are created by calling dsql() function of either DB object or other DSQL object. This function always returns empty query.

// use default connection
$q = $this->api->db->dsql();
// or
$q = $mydb->dsql();

You may also call $model->dsql() which will return initialized Query Object with your particular Model settings.

.

Query Config:

$db = $this->api->db->dsql();
$db
  ->table('user')
  ->where('type','admin')
  ->field('id');
  // configure more
$data = $db
  ->order('created_dts')
  ->field('name,surname')
  ->getAll();
// Produces: $data=array(
//   array('id'=>1, 'name'=>'John', 'surname'=>'Smith'),
//   array('id'=>2, 'name'=>'Joe', 'surname'=>'Blogs')
// );
//
foreach($this->api->db->dsql()->expr('show tables') as $row){
  $table_name = pop($row);
  $this->add('Text')->set('Table: '.$table_name);
}

 

Debugging:

$q->debug();

 

.

Fetching ways in DSQL:

$data = $q->get();    /* same as getAll();*/
$data = $q->getAll();   /* returns all data as array of hashes, array() if query produced no results.*/
$data = $q->getRow();     /* returns only first row of data, null if query produced no results.*/
$data = $q->getOne();     /* returns only single value, null if query produced no results or result was NULL.*/

//////////////////////////
while($row = $q->fetch()){
  /* Will loop through results fetching one row at a time.
 You can access your data through $row['fieldname'];*/
}
foreach($q as $row){
  // $row is associative array.
}
/////////////////////////

//PDO statement...
$q->execute();    /*Prepares and Executes statement*/
$stmt = $q->stmt;
/////////////////////////

.

Lister class and all derived classes (CompleteLister, Grid) accept Iterate-able classes through setSource() method.

$q=$this->api->db->dsql();
$q->table('user')->field('name')->field('surname');

$grid = $this->add('Grid');
$grid->addColumn('text','name');
$grid->addColumn('text','surname');

$grid->setSource( $q );   // Associate Grid with data-source.

.

=====================================

.

Adding condition to  DSQL

$q->where('id',1);        // where id=:a    'a'=>1
$q->where('id>',1);       // where id>:a    'a'=>1
$q->where('id!=',1);      // where id!=:a   'a'=>1
$q->where('id like',1);   // where id like :a   'a'=>1
$q->where('id in',array(1,2));      // where id in(:a,:b)   'a'=>1, 'b'=>2
/////////////////////////////////

$q->where('id',null);       /* where id is NULL*/
$q->where('id is',null);    /* where id is NULL*/
$q->where('id!=',null);     /* where id is NOT NULL*/
$q->where('id is not',null); /*where id is NOT NULL*/
////////////////////////////////

/*Using with Expressions: expr()
 Single argument mode*/
$q->where($q->expr('a=b'));

// Using operator with the first argument
$q->where('date>',$q->expr('DATE_SUB(CURDATE(), INTERVAL 2 MONTH)');

/* Expression may contain parameters. 
Unlike where('id',1) this will not use equation operator*/
$q->where('age',$q->expr('between 5 and 10'));

// both arguments may be expressions
$q->where($this->expr('length(password)'),$q->expr('between 3 and 10'));

// Alternative way to specify parameter
$q->where($this->expr('length(password)'),'>',5);

.

AND conditions: where(..)->where()

Calling where() multiple times will require all of the conditions to be met. Using “AND” operator.

.

OR conditions: where(array)

Callng where() with a single array argument will use OR to join those conditions. The same principles apply on the array as no the actual where() call. You can even specify arrays recursively.

$q->where(array(
  array('id',1),  
  array('id',2)
  ));/* where (id=:a or id=:b) array('a'=>1, 'b'=>2)*/

$q->where(array(
  array($q->expr('len(name)'),'>',5),
  array($q->expr('a=b'))    
  ));// where (len(name)>:a or a=b) array('a'=>5)

There is alternative way to use OR conditions. Use whichever you like more. on() method relies on expr() to produce a new query. (I think this way is better!)

$q->where( $q->or()->where('a',1)->where('b>',5) );

 

.

Subqueries

You may use $q->dsql() as a quick way to produce sub-queries. Calling this method will create a new DSQL object, which you can use similarly as expression.

$q
 ->table('author')
 ->field('name')
 ->where('book_id', $q->dsql()->table('book')->where('is_rented','Y') ); 
  /*by default "id" field is used.
   produces: 
select name from author where book_id in (select id from book where is_rented=:a)  array('a'=>'Y')
  Note: This is quite ineffective way for listing all authors who's books are rented */

$q
  ->table('author')
  ->field('name')
  ->where(
    $q->dsql()
      ->table('book')
      ->where('author_id', $q->getField('id'))
      ->field('count(*)'),
    '>',5);    
  /* produces:  
select name from author where (select count(*) from book where author_id=author.id)>5
   Displays names of authors who have more than 5 books. */

 

The second argument can be used to specify which table field is queried from. That’s handy when you are joining tables.

$q
  ->table('user')
  ->table('address')
  ->where($q->expr('address.user_id=user.id'))

  ->field('name','user')
  ->field('postcode','address');

/* Produces: 
select user.name, postcode.address from user,address where address.user_id=user.id
Similarly to expressions, you may use subqueries.*/
$q
->table('author')
->field('name')
->field( $q->dsql() ->table('book')->where('author_id',$q->getField('id')) ->field($q->expr('sum(pages)')) , 'total_pages');
/* Produces: select name,(select sum(pages) from book where author_id=author.id) total_pages from author */

 

.

.

.

Querying Data From Multiple Tables. Joins

$q->table('user');
$q->join('address');

echo $q->getField('name');   // will output `user`.`name`
echo $q->getField('name','address');   // will output `address`.`name`

The method join() has several ways to call it. The simplest is by specifying only one string argument.

In this case the table is joined and the table_id from main_table will be used in the “ON” condition.

$q->table('user')->join('address');
/* will produce select user.id from user join address on address.id=user.address_id;*/

/*
By default the "id" of JOINED table is linked with the table_id in the main table. 
This can be changed, however. The "id" of JOINED table can be set to a different 
field if you specify that field with a dot when joining:
*/
$q->table('user')->join('address.user_id');

 // will produce select user.id from user join address on address.user_id=user.id

/*
As you noticed, the field from the main_table was changed to "id". 
If we want to specify that field manually, we can use second argument in the join() method: 
*/

$q->table('user')->join('address.code','code');

 // will produce select user.id from user join address on address.code=user.code

/*Or we can also specify the table which should participate with the join if we use 
the dot in the 2nd argument also*/

$q->table('user')->join('manager');
$q->join('address.code','manager.code');

// will produce select user.id from user
//   join manager on manager.id=user.manager_id
//   join address.code=manager.code

.

Specifying join type

The third argument in the “join” method can be used to specify the type of join. You can use “left”, “right” or “outer” or whatever join type is supported by your SQL. By default the join type is not specified.

$q->table('user')->join('manager',null,'left');
$q->join('address.code','manager.code');

// will produce select user.id from user
//   left join manager on manager.id=user.manager_id
//   join address.code=manager.code

٫

Building a UNION support

First you would need to use a new template for union like this:

$q=$this->useExpr('[q1] UNION [q2]');

Next you need to assign the arguments with setCustom():

$q->setCustom('q1',$q->dsql()->table('book'))
$q->setCustom('q2',$q->dsql()->table('news'));

/* Result:
select * from `book` UNION select * from `news` 

*/

============

Insert:

$this->api->db->dsql()->table('table')->set($associative_array)->do_insert();
$this->api->db->dsql()->table('table')->set('field1','a')->set('field2','b')->do_insert();

Update:

   $this->api->db->dsql()->table('table1 x')
             ->where('id',$somevariable)
             ->set('col1',$somevalue)
             ->debug()
             ->do_update();

Delete:

  $this->api->db->dsql()->table('table1 x')
             ->where('id',$somevariable)
             ->do_delete();

Other Examples:

    $s=$p->add('Model_Story')->loadData($story);
       $s->set('points', $value);
       $s->update();

 

$authors = $this->add('Model_Author'); // selects ALL authors

foreach($authors as $junk){
  echo $authors['name'].': sold '.$authors['books_sold']."\n";
}

 

  $p=$this->add('Model_Purchase');
    $p->addCondition('user_id','in',$m->dsql()->field('id'));
    $p->deleteAll();

============================

$model->load($id);

$model->tryLoad($id)

$model->loadAny()

$model->tryLoadAny()

$model->loadBy($field,$condition,$value);
This have received a significant flexibility in the arguments. Actually it relies on addCondition(), but it’s still powerful.

 loadBy($model->dsql->expr(‘rand() > 0.2’) );
 loadBy($model->dsql->expr(‘rand()’), ‘>‘, $_GET[‘id’] ); // safe against injeciotn
 loadBy(‘calc_field’, 200); // using calculated field automatically switches to “having” clause.

$model->tryLoadBy($field,$condition,$value);

=====================

Another Examples:

$model->addCondition(‘gender’,’M’);
$model->loadAny();
$model->set(‘gender’,’F’);
$model->save();  
//

$boy=$this->add(‘Model_Person’)->addCondition(‘gender’,’M’);
$girl=$this->add(‘Model_Person’)->addCondition(‘gender’,’F’);

$boy->loadAny();
$boy->saveAs($girl);
//

$model->saveAndUnload();
This will save model into the database but will not load data back. The model loaded() will return false. Slightly better if you need performance.

$model->saveLater(); Will not save right away, but will save when model is being destroyed by Garbage Collector;

$model->loadBy('age','>','25');
$model->loadBy($model->dsql->expr('expire_dts>now()'));

//
//getBy($field,[$cond],$value)

/*This similar syntax will return array of hashes produced the model's select.*/
$data = $model->getBy($model->dsql->expr('expire_dts>now()'));
echo json_encode($data);


//getRows(array)
/*
This is similar to getBy but it allows to define which fields you are willing to retrieve:*/
$data = $model->getRows(array('id','name'))
echo json_encode($data);

Iterating Through Entities

foreach($this->add('Model_Book') as $book){
    $book['sale']++;
    $book->save();
}

…..