Code Igniter : Log all queries

Developing applications in Code Igniter is fun and easy, as we do not have to write much of the actual queries because of its Active Record Implementation. Moreover, we can find plugins for CI which will write all the model classes for you in a click.

As we keep on developing application, we might sometimes need to keep track of the queries that have been run during each page load. It might seem minimal but while developing large enterprise applications, even the smallest optimizations will speed up things a lot.

Below you’ll find a CodeIgniter hook that logs all database queries to a simple text file.

/* config/hooks.php */
$hook['post_system'][] = array(
	'class' => 'LogQueryHook',
	'function' => 'log_queries',
	'filename' => 'LogQueryHook.php',
	'filepath' => 'hooks'
);

/* application/hooks/LogQueryHook.php */
class LogQueryHook {

	function log_queries() {
		$CI => get_instance();
		$times = $CI->query_times;
		$dbs    = array();
		$output = NULL;
		$queries = $CI->queries;

		if (count($queries) == 0){
			$output .= "no queries\n";
		}else{
			foreach ($queries as $key=>$query){
				$output .= $query . "\n";
			}
			$took = round(doubleval($times[$key]), 3);
			$output .= "===[took:{$took}]\n\n";
		}

		$CI->helper('file');
		if ( ! write_file(APPPATH  . "/logs/queries.log.txt", $output, 'a+')){
			log_message('debug','Unable to write query the file');
		}
	}
}

Tired of writing Stored Procedures for regular CRUD operations? Here is useful PHP script, which generates the script for the required stored procedures. This code studies the table structure and creates the stored procedures accordingly.

<?php

$tableName = "Your Desired Table";

// Connection String
$conn = mysql_connect('localhost', 'root','');

// Select database
mysql_select_db('<DBNAME>', $conn);

$result = mysql_query('select * from `'.$tableName.'`');

$paramList = '(<br/>';

$tableIdField = mysql_field_name($result, 0);

$insertFields = "(";
$insertValues = "(";

$updateList = "";

for($i=0;$i< mysql_num_fields($result);$i++){
     $fieldName = mysql_field_name($result, $i);
     $fieldType = mysql_field_type($result, $i);

     if($i>0){
          $insertFields = $insertFields.$fieldName.',';
          $insertValues = $insertValues.' _'.$fieldName.',';
     }

     $updateList = $updateList.' '.$fieldName.'='.' _'.$fieldName.',';

     $fieldFormat = '';
     $fieldFormat = $fieldFormat.' _'.$fieldName;
     $fieldFormat = $fieldFormat.' '.$fieldType;
     $fieldFormat = $fieldFormat.' '.',<br/>';

     $paramList = $paramList.' '.$fieldFormat;
}

$insertFields = $insertFields.')';
$insertValues = $insertValues.')';

$updateList = $updateList.')';

$paramList = $paramList.')<br/>';
$paramList = str_replace('int','int(11)',$paramList);
$paramList = str_replace('string','text',$paramList);
$paramList = str_replace(',)',')',$paramList);

$insertFields = str_replace(',)',')',$insertFields);
$insertValues = str_replace(',)',')',$insertValues);

$updateList = str_replace(',)','',$updateList);

$spText = '<br/><br/>DELIMITER
lt;br/>';

$spText .= ' <br/>DROP PROCEDURE IF EXISTS Manage'.$tableName.'
lt;br/>';

$spText .= ' <br/>CREATE PROCEDURE Manage'.$tableName.'<br/>';

$spText .= $paramList;

$spText .= ' <br/>BEGIN<br/>';

$spText .= ' <br/>IF _'.mysql_field_name($result, 0).' = 0 THEN<br/>';

$spText .= ' INSERT INTO '.$tableName.' '.$insertFields;

$spText .= ' VALUES '.$insertValues.';';

$spText .= '<br/> ELSE<br/>';

$spText .= ' UPDATE '.$tableName.' SET '.$updateList;

$spText .= ' WHERE '.$tableIdField.'=_'.$tableIdField.';';

$spText .= ' <br/>END IF;<br/>';

$spText .= ' <br/>END
lt;br/>';

$spText .= ' <br/>DELIMITER ;<br/>';
echo '<br/><strong>Insert/Update stored Procedure: </strong><br/>';
echo $spText;

$spText='';
echo '<br/><br/><br/><br/>';
$spText = '<br/><br/>DELIMITER
lt;br/>';

$spText .= ' <br/>DROP PROCEDURE IF EXISTS Read'.$tableName.'
lt;br/>';

$spText .= ' <br/>CREATE PROCEDURE Read'.$tableName.'<br/>';

$spText .= '(_id int(11))';

$spText .= ' <br/>BEGIN<br/>';

$spText .= ' <br/>IF _id = 0 THEN<br/>';

$spText .= ' SELECT * FROM '.$tableName.';';

$spText .= '<br/> ELSE<br/>';

$spText .= ' SELECT * FROM '.$tableName.' WHERE ID = _ID;';

$spText .= ' <br/>END IF;<br/>';

$spText .= ' <br/>END
lt;br/>';

$spText .= ' <br/>DELIMITER ;<br/>';
echo '<br/><strong>Select All/specific record stored Procedure: </strong><br/>';
echo $spText;
?>

Hello,

Recently, I had to work on a rather out of the scope issue, regarding the backup of mysql databases automatically. I mean manual backing of data or databases can be trouble some, and one might forget usually. So, why not write a script that can be scheduled to run automatically. This will ensure that the data is backed up regularly, and there is no loss of data at least for 1 day interval.

Step 1:

The first thing to do here is to create a database backup from the command prompt. To accomplish this you need to have MySql installed on your computer (the one where the backup needs to be done). The server to be backed up can be on any other computer on the network, doesnt matter. You can always change the host. So, if you have Mysql installed on your machine, you should be able to find a mysqldump.exe file located in the bin folder. Lets say I have installed it inside the C:\Program Files, then the location of the mysqldump.exe should be

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe

This is the particular file required, that will connect to the mysql database server, and retrieve the dump file.

Step 2:

The next step is to actually write the dos command to perform the backup of the database. Let us open the command prompt. This can be done by Clicking on

Start > All Programs > Accessories > Command Prompt
or
Start > Run > type “cmd” > OK

Next, point your prompt to the location of the mysqldump.exe file using the following command

      cd C:\Program Files\MySQL\MySQL Server 5.5\bin

The syntax for the dos command can be seen as below

      mysqldump.exe –e –u[username] -p[password] -h[hostname] [database name] > C:\[filename].sql

For Example if my database name is “testdb”, username is “testuser”, password is “pass” and the hostname is “localhost”, then my dos command will look like

      mysqldump.exe -e -u testuser -p pass testdb>C:\testdb.sql

Once, we confirm that the above command works, we are ready create our batch file, which will be put up into the scheduler.

Creating a Batch file. Open up notepad from

Start > All Programs > Accessories > Notepad
Or
Start > run > type “notepad” > OK

Type the above code into the notepad as give below

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe -e -u testuser -p pass testdb>C:\testdb.sql

Save the file as auto_backup.bat, where bat stands for batch. Place the batch file in a safe location, where it cannot be edited easily.

Another line that we need to add to the file is the renaming of the backup file according to the date. This will help keep the backup files according to the date. Add the following line to rename the backup file, assuming the file name is auto_backup.bat.

      ren C:\auto_backup.sql auto_backup%date:~4,2%-%date:~7,2%-%date:~10,4%.sql

The above command will rename the file as auto_backup_01_03_2012.sql

With the addition of the above code, you batch file will look like

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe -e -u testuser -p pass testdb>C:\testdb.sql

ren C:\auto_backup.sql auto_backup%date:~4,2%-%date:~7,2%-%date:~10,4%.sql

Save the file after that.

Step 4:

Assigning the batch file to be run at a certain time, using the Task Scheduler.

In Windows 7, it is found inside Control Panel > Administrative Tools > Task Scheduler

In Windows XP, it is found inside Control Panel > Scheduled Tasks

Create a new task, and when it asks for a program to run, choose the batch file that we just created. (auto_backup.bat), select a suitable repeat interval, and the time to run the file.

With that, we are done.