Creating a Report Type

This is a short tutorial on creating a new Report Type. The included Report Types are MySql, MongoDB, and PHP.

We will create a Sqlite Report Type.

The ReportType Class

Create the file classes/local/SqliteReportType.php

<?php
class SqliteReportType extends ReportTypeBase {
	//run after parsing all the headers
	//this should do validation that the report is able to run in the current environment
	//it should also handle Included Reports
	public static function init(&$report) {
		//make sure there is SQLite connection info defined for the current environment
		if(!isset(PhpReports::$config['environments'][$report->options['Environment']][$report->options['Database']])) {
			throw new Exception("No ".$report->options['Database']." info defined for environment '".$report->options['Environment']."'");
		}
		
		//if there are any included reports, add the includedreport sql to the top
		if(isset($report->options['Includes'])) {
			$included_sql = '';
			foreach($report->options['Includes'] as &$included_report) {
				$included_sql .= trim($included_report->raw_query)."\n";
			}
			
			$report->raw_query = $included_sql . $report->raw_query;
		}
	}
	
	//called before running the report
	public static function openConnection(&$report) {
		if(isset($report->conn)) return;
		
		//get the connection info for the current environment
		$environments = PhpReports::$config['environments'];
		$config = $environments[$report->options['Environment']][$report->options['Database']];
		
		//store the database connection in the report object
		$error = null;
		if(!($report->conn = sqlite_open($config['file'], 0666, $error))) {
			throw new Exception('Could not connect to SQLite: '.$error);
		}		
	}
	
	//called after running the report
	public static function closeConnection(&$report) {
		if(!isset($report->conn)) return;
		sqlite_close($report->conn);
		unset($report->conn);
	}
	
	//actually runs the report
	public static function run(&$report) {
		$rows = array();
		
		//expand macros in query using Twig
		$sql = PhpReports::render($report->raw_query, $report->macros);
		
		//store the original query and formatted query in the report object
		//this is used to display debugging info if there is an error
		$report->options['Query'] = $sql;
		$report->options['Query_Formatted'] = SqlFormatter::highlight($sql);
		
		//a report can have multiple queries separated by semi-colons
		//split into individual queries and run each one, saving the last result		
		$queries = SqlFormatter::splitQuery($sql);
		
		foreach($queries as $query) {
			//skip empty queries
			$query = trim($query);
			if(!$query) continue;
			
			$error = null;
			$result = sqlite_exec($report->conn, $query, $error);
			
			if(!$result) {
				throw new Exception("Query failed: ".$error);
			}
		}
		
		//fetch the rows as associative arrays
		while($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
			$rows[] = $row;
		}
		
		return $rows;
	}
}

Set the Configuration

In the SqliteReportType class, we use the configuration option 'file'. We must define this in config/config.php

<?php
return array(
	...
	'environments' => array(
		'main'=>array(
			...
			'sqlite'=>array(
				'file'=>'data.db'
			),
			...
		),
		...
	)
	...
);

Testing it Out

Create a test report samples_reports/test/sqlite_test.sql

-- Testing out SQLite reports
-- TYPE: Sqlite

-- First, we'll create a test table
CREATE TABLE IF NOT EXISTS testing (
	Id integer PRIMARY KEY,
	Name text NOT NULL
);

-- Insert some test data
INSERT IGNORE INTO testing VALUES(1,"John");
INSERT IGNORE INTO testing VALUES(2,"Mary");
INSERT IGNORE INTO testing VALUES(3,"Steven");

-- Do the actual report
SELECT * FROM testing;

View this report in your browser at http://localhost/report/html/?report=test/sqlite_test.php

Setting Default File Extensions

By default, '.sql' files are mapped to the MySql report type. This is why we needed the TYPE header in the test report.

You can set your own default file extension mappings with a config option in config/config.php.

return array(
	...
	'default_file_extension_mapping' => array(
		'sql'=>'Sqlite',
		...
	),
	...
);

Now, any report file ending in ".sql" will default to the Sqlite Report Type. You can now remove the TYPE header from the test report and it will still work as expected.