reportData
The reportData class stores all compiled report results. The results themselves are held in an Sqlite database file. The main report table, named "report" is comprised of a primary key: id and then each column from the report column definition array (see reportColumn for details).
Internally the reportData automatically attempts to connect to the reports data store, creating it if it does not already exist. The database file contains three tables:
* report * metadata * cachedata
report is the main data while metadata contains additional information about a specific rows and columns data. This is used to store any object data that has been bound to the result set e.g. reportDataAbstract objects that contain Excel formatting instructions. cachedata contains the creation date of the database and any other properties that may be useful for caching purposes.
Several prepared statements are used in reportData - each is prepared when first accessed but is cached afterwards for performance. Upon destruction each statement is closed and the connection to the database file destroyed.
reportData implements the Iterator interface allowing it to be used in foreach loops. Data is added via the {@link reportData::addRow} method. Rows start at 1 and increment from there. When adding a row of data it must contain the named keys as described by the report columns.
Report data can come from any source, database via SQL query, XML, PHP array, SOAP API calls etc. This container just holds the formatted results.
Example:
array( 1 => array(field1 => value1, field2 => value2), 2 => array(field1 => value1, field2 => value2), ... );
Some additional helper methods are included for manipulating the data. These include being able to sum a row of data or a whole column. Only the numeric columns are summed.
$oReport = new report();
// sum a row
$oData = new reportData($oReport);
// add some data
$oData->addRow(array('field1' => 1, 'field2' => 2, 'field3' => 3, 'field4' => 'string'));
$total = $oData->sumRow(1);
echo $total; // outputs 6
// sum a column
$oData = new reportData();
$oData
->addRow(array('field1' => 1, 'field2' => 2, 'field3' => 3))
->addRow(array('field1' => 1, 'field2' => 2, 'field3' => 3))
->addRow(array('field1' => 1, 'field2' => 2, 'field3' => 3));
$total = $oData->sumColumn('field1');
echo $total; // outputs 3
Once compiled, additional queries can be run on the reportData by using the {@link reportData::query} method. This returns the PDO connection instance allowing any valid Sqlite query to be run.
Improving Performance
By default the reportData PDO connection to SQLite is run in auto-commit mode. This is because the object can be used for both inserting and iterating data and it is impossible to know what context is being used.
To improve insertion performance when inserting data be sure to call: beginTransaction() before adding rows to the reportData object, and then afterwards call commit() (both from reportData->query(). This will wrap all inserts into a single transaction, greatly increasing the speed. For example: 23K records in auto-commit might take 53 seconds, as a single transaction 2.3 seconds (of course your mileage may vary).
You need to set the beginTransaction() and commit() inside the reportBase->_run() method before and after you start adding rows to the data object.
// inside a report extending reportBase, in method _run()
function _run() {
// do some setup and prep report data query or collate
// ready to being storing data, start transaction
$this->getReportData()->query()->beginTransaction();
foreach ( $oResultSet as $row ) {
$this->getReportData()->addRow($row);
}
// finished collating data, commit changes
$this->getReportData()->query()->commit();
// finish up report stuff
return true;
}
Methods
public __construct($inReport)
Creates a new reportData object
public __destruct()
Clean up all connections and optimise the data cache
public initialise()
Sets up the reportData object creating structures as needed
public reset()
Resets the object
public isModified()
Returns true if object has been modified
public setModified([$status = true])
Set the status of the object if it has been changed
public getReport()
Returns the report
public setReport($inReport)
Set the report instance
public getCurrentRow()
Returns $_CurrentRow
public setCurrentRow($inCurrentRow)
Set $_CurrentRow to $inCurrentRow
public getCurrentRowData()
Returns $_CurrentRowData
public setCurrentRowData($inCurrentRowData)
Set $_CurrentRowData to $inCurrentRowData
public getFileStore()
Returns $_FileStore
public setFileStore($inFileStore)
Set $_FileStore to $inFileStore
public getDbFile()
Returns $_DbFile
public setDbFile($inDbFile)
Set $_DbFile to $inDbFile
public getCreateDate()
Returns the creation date of this database
public query()
Returns the PDO connection to the SQLite database allowing arbitrary queries to be run
public setRows([$inArray = array()])
Sets all rows for result set
public addRow([$inArray = array()])
Adds a row to the end of the set
public getRow($inRowNum)
Returns row number $inRowNum
public sumColumn($inColName)
Sums all values tagged with $inColName, but only if $inColName is numeric
public sumRow($inRow)
Sums a rows integer or float values, returns 0 if nothing to sum.
private getDbInstance()
Returns an instance of dbDriverSqlite
private _createDbStructure()
Checks and builds as necessary the Sqlite DB structure
public dbExists()
Returns true if the report DB is created
public optimise()
Runs a vacuum on the SQLite database, optimising it
public purge()
Drops the report table and cleans up the db file
protected _hasObject($inRow, $inColumn)
Returns true if there is an object record for $inRow:$inColumn
protected _getObject($inRow, $inColumn)
Returns the stored object for $inRow:$inColumn, throws exception is cannot restore object
protected _storeObject($inRow, $inColumn, $inObject)
Stores an object in the metadata table
Inherited Methods
- Iterator::current()
- Iterator::key()
- Iterator::next()
- Iterator::rewind()
- Iterator::valid()
- Countable::count()
- baseSet::getCount()
- Iterator::rewind()
- Iterator::valid()
- Iterator::key()
- Iterator::next()
- Iterator::current()
- Countable::count()
Posted by: Scorpio Documentor (Writer), in Report on 19 Nov 2009 @ 20:31
Tags: baseset, countable, iteratoraggregate, report, reportdata, traversable,
This
work is licenced under a
Creative Commons Licence.