<?php
/*==-==-==-==-==-==-==-==-==-==-==-==-==-==-==+
| flashPash PHP demo |
+==-==-==-==-==-==-==-==-==-==-==-==-==-==-==+
| version 1.0 *RC1* |
| |
| last update on 2003-07-07 by si*sshnug.com |
| |
| Copyright (c) 2003, Simon Mckenna |
| All rights reserved |
+==-==-==-==-==-==-==-==-==-==-==-==-==-==-==+
| source best viewed with tab space set to 2 |
+==-==-==-==-==-==-==-==-==-==-==-==-==-==-==*/
require_once 'flashPash/flashPash.php';
require_once 'flashPash/flashPash.firefly.php'; /* only used to demo client meta-data output */
/*
This is a demo of how you can use the FlashPashServerClass just within PHP.
With the advent of joins, aliases and multi-table updates in RC1, the complexity and
power of this demo has also increased, but at first this also makes it harder to
understand what's going...so please study the logs and code...it should make sense :)
See flashPash.firefly.demo.php for demo of PHP script interacting with a client through XML.
Note that I assume you're using MySQL, if not, then you may have to change SQL syntax
for creation of database, user, tables and records. The task has been started for interbase
and firebird databases. Please send any changes you make back to si*sshnug.com so I can build
it into the standard demo.
Note: For interbase/firebird DB's I haven't found a way to create DB or USER from ADOdb:
CREATE DATABASE "D:/FirebirdDB/flashpashtest.gdb" PAGE_SIZE 4096 USER "sysdba" PASSWORD "masterkey"
*/
if ( array_key_exists ( 'dbType', $_POST ))
{
startDemo( @$_POST[ 'dbType' ], @$_POST[ 'dbHost' ], @$_POST[ 'dbName' ], @$_POST[ 'dbUser' ], @$_POST[ 'dbPass' ], @$_POST[ 'checkRole' ], @$_POST[ 'role' ], @$_POST[ 'errorReporting' ] );
}
else
{
$dbHost = 'localhost';
//$dbType = 'firebird';
//$dbName = 'C:/Documents and Settings/si/My Documents/db/Firebird/FLASHPASHTEST.GDB';
//$dbUser = 'sysdba';
//$dbPass = 'masterkey';
$dbType = 'mysql';
$dbName = 'flashPashTest';
$dbUser = 'user';
$dbPass = 'userpass';
$checkRole = true;
$role = 'PHPdemo';
$errorReporting = FP_ERROR_REPORTING_DEFAULT;
?>
<html>
<head>
<title>flashPash PHP test</title>
<meta http-equiv="Content-Type" content="text/html" />
</head>
<body>
<font face="Arial, Helvetica, sans-serif">
<form method="post">
<p><h2> flashPash PHP demo</h2>
This is an example of how you can use the FlashPashServerClass in PHP.<br/><br/>
Debug messages are always shown, along with the PHP code used with flashPash.<br/>
You can also choose the level of error reporting and which role is used.<br/><br/>
Please note that this demo is now quite advanced, with multi-table updates,<br/>
joins, aliases and SQL computations being used. Whilst this is good to<br/>
show off the power of flashPash, it may be daunting to the uninitiated.<br/><br/>
Review the <a href="demo.php?menu=site">website</a> or <a href="demo.php?menu=rss">RSS feed</a> demo's for simpler examples.<br/>
</p>
<table width="500" border="0">
<tr>
<td width="200"> Database Host</td>
<td> <input name="dbHost" type="text" value="<?php echo $dbHost; ?>"></td>
</tr>
<tr>
<td width="200"> Database Type</td>
<td>
<select name="dbType" size="1" id="dbType" title="<?php echo $dbType; ?>">
<option value="mysql" selected>MySQL</option>
<option value="mysqlt">MySQL with transaction support</option>
<option value="firebird">Firebird</option>
<option value="postgres">Postgres</option>
<option value="postgres7">Posgres v7 with limit suport</option>
<option value="postgres64">Postgres v6.4 or earlier</option>
<option value="access">Access/Jet ODBC DSN required</option>
<option value="ado">ADO</option>
<option value="ado_access">ADO for Access</option>
<option value="ado_mssql">ADO for MS SQL</option>
<option value="db2">DB2</option>
<option value="vfp">FoxPro</option>
<option value="fbsql">FrontBase</option>
<option value="borland_ibase">Interbase Borland v6.5 or later</option>
<option value="ibase">Interbase v6 or earlier</option>
<option value="informix">Informix</option>
<option value="informix72">Informix before v7.3</option>
<option value="mssql">MS SQL v7 or later</option>
<option value="mssqlpo">MS SQL portable</option>
<option value="oci8">Oracle v8 or v9</option>
<option value="oci8po">Oracle portable</option>
<option value="oci805">Oracle v8.05</option>
<option value="odbc">ODBC</option>
<option value="odbc_mssql">ODBC for MS SQL</option>
<option value="odbc_oracle">ODBC for Oracle</option>
<option value="sqlanywhere">SQL Anywhere</option>
<option value="sybase">Sybase</option>
</select>
</td>
</tr>
<tr>
<td width="200"> Database Name</td>
<td> <input name="dbName" type="text" value="<?php echo $dbName; ?>"></td>
</tr>
<tr>
<td width="200"> Database User Name</td>
<td> <input name="dbUser" type="text" value="<?php echo $dbUser; ?>"></td>
</tr>
<tr>
<td width="200"> Database User Password</td>
<td> <input name="dbPass" type="text" value="<?php echo $dbPass; ?>"> </td>
</tr>
<tr>
<td width="200"> Role Check?</td>
<td> <input name="checkRole" type="checkbox" value="<?php echo $checkRole; ?>" checked></td>
</tr>
<tr>
<td width="200"> Role name</td>
<td> <input name="role" type="text" value="<?php echo $role; ?>"></td>
</tr>
<tr>
<td width="200"> Error Reporting</td>
<td>
<select name="errorReporting" size="1" id="errorReporting" title="<?php echo $errorReporting; ?>">
<option value="0">FP_ERROR_REPORTING_DEFAULT</option>
<option value="1" selected>FP_ERROR_REPORTING_VERBOSE</option>
<option value="2">FP_ERROR_REPORTING_ONLY_NUMBER</option>
<option value="3">FP_ERROR_REPORTING_NONE</option>
</select>
</td>
</tr>
</table>
<br />
<input name="Submit" type="submit" value=" Start demo ">
</form>
</font>
</body>
</html>
<?php
}
function startDemo( $dbType, $dbHost, $dbName, $dbUser, $dbPass, $checkRole, $role, $errorReporting )
{
/* create new flashPash object */
$fpDemo = new FlashPashServerClass( FP_SOURCE_PHP, $dbType, $dbHost, $dbName, $dbUser, $dbPass );
/* define roles if wanted */
$fpDemo->checkRole = $checkRole;
$fpDemo->role = $role;
/* define level of error reporting */
$fpDemo->errorReporting = $errorReporting;
/* turn logging on...if you want both .html and .txt log files, just set = FP_LOG_HTML + FP_LOG_TEXT */
$fpDemo->debug = FP_LOG_HTML;
/* point our logfile to where the public can see */
$fpDemo->logfile = $_SERVER[ 'DOCUMENT_ROOT' ] . '/flashPash/demo/logs/log.PHP.demo';
/* create shortcut for html logfile */
$logfile = $fpDemo->logfile . '.html';
/* see if this demo has been run before */
?><font class="small" face="Arial, Helvetica, sans-serif"><h2>flashPash PHP demo started</h2><?php
title( 'Searching for tables in database' );
/* The `run` method just shows the user what's being run */
run( '$fpDemo->doMeta( FP_META_TYPE_TABLES );' );
$fpDemo->doMeta( FP_META_TYPE_TABLES );
/* output the result */
readfile( $logfile );
/* see what happened */
if (( $fpDemo->error != 0 ) || ( $fpDemo->recordCount == 0 ))
{
title( 'No tables found, attempting to create database' );
switch ( $fpDemo->dbType )
{
case 'ibase' :
case 'firebird' :
case 'borland_ibase' :
run( '$fpDemo->doSQL( \'CREATE DATABASE \"\' . $fpDemo->dbName . \'\" PAGE_SIZE 4096 USER \"\' . $fpDemo->dbUser . \'\" PASSWORD \"\' . $fpDemo->dbPass . \'\"; commit;\' );' );
$fpDemo->doSQL( 'CREATE DATABASE "' . $fpDemo->dbName . '" PAGE_SIZE 4096 USER "' . $fpDemo->dbUser . '" PASSWORD "' . $fpDemo->dbPass . '"; commit;' );
break;
default :
run( '$fpDemo->doSQL( \'CREATE DATABASE \' . $fpDemo->dbName );' );
$fpDemo->doSQL( 'CREATE DATABASE ' . $fpDemo->dbName );
}
readfile( $logfile );
if ( $fpDemo->error != 0 )
title( 'Error whilst creating database, but will try to create user anyway...' );
title( 'Creating user' );
switch ( $fpDemo->dbType )
{
case 'mysql' :
case 'maxsql' :
run( '$fpDemo->doSQL( \'GRANT ALL PRIVILEGES ON \' . $fpDemo->dbName . \'.* TO \\\'\' . $fpDemo->dbUser . \'\\\'@\\\'\' . $fpDemo->dbHost . \'\\\' IDENTIFIED BY \\\'\' . $fpDemo->dbPass . \'\\\'\' );' );
$fpDemo->doSQL( 'GRANT ALL PRIVILEGES ON ' . $fpDemo->dbName . '.* TO \'' . $fpDemo->dbUser . '\'@\'' . $fpDemo->dbHost . '\' IDENTIFIED BY \'' . $fpDemo->dbPass . '\'' );
break;
default :
run( '$fpDemo->doSQL( \'GRANT ALL PRIVILEGES ON \' . $fpDemo->dbName . \' TO \\\'\' . $fpDemo->dbUser . \'\\\'\' );' );
$fpDemo->doSQL( 'GRANT ALL PRIVILEGES ON ' . $fpDemo->dbName . ' TO \'' . $fpDemo->dbUser . '\'' );
}
readfile( $logfile );
if ( $fpDemo->error != 0 )
title( 'Error whilst creating user, but will try to create tables anyway...' );
title( 'Creating tables...' );
switch ( $fpDemo->dbType )
{
case 'ibase' :
case 'firebird' :
case 'borland_ibase' :
run( '$fpDemo->doSQL( \'CREATE GENERATOR TEST_GEN\' );' );
$fpDemo->doSQL( 'CREATE GENERATOR TEST_GEN' );
run( '$fpDemo->doSQL( \'SET GENERATOR TEST_GEN TO 0\' );' );
$fpDemo->doSQL( 'SET GENERATOR TEST_GEN TO 0' );
run( '$fpDemo->doSQL( \'CREATE TABLE table1 ( t1integer INTEGER NOT NULL, t1boolean CHAR, t1varchar VARCHAR(42) DEFAULT NULL, t1float FLOAT, PRIMARY KEY (t1integer))\' );' );
$fpDemo->doSQL( 'CREATE TABLE table1 (t1integer INTEGER NOT NULL, t1boolean CHAR, t1varchar VARCHAR(42) DEFAULT NULL, t1float FLOAT, PRIMARY KEY (t1integer))' );
run( '$fpDemo->doSQL( \'CREATE TABLE table2 ( t2key INTEGER NOT NULL, t2dateTime TIMESTAMP, t2date DATE, PRIMARY KEY (t2key))\' );' );
$fpDemo->doSQL( 'CREATE TABLE table2 (t2key INTEGER NOT NULL, t2datetime TIMESTAMP, t2date DATE, PRIMARY KEY (t2key))' );
break;
default :
run( '$fpDemo->doSQL( \'CREATE TABLE table1 ( t1integer INTEGER NOT NULL AUTO_INCREMENT, t1boolean CHAR, t1varchar VARCHAR(42) DEFAULT NULL, t1float FLOAT, PRIMARY KEY (t1integer))\' );' );
$fpDemo->doSQL( 'CREATE TABLE table1 (t1integer INTEGER NOT NULL AUTO_INCREMENT, t1boolean CHAR, t1varchar VARCHAR(42) DEFAULT NULL, t1float FLOAT, PRIMARY KEY (t1integer))' );
run( '$fpDemo->doSQL( \'CREATE TABLE table2 ( t2key INTEGER NOT NULL AUTO_INCREMENT, t2dateTime TIMESTAMP, t2date DATE, PRIMARY KEY (t2key))\' );' );
$fpDemo->doSQL( 'CREATE TABLE table2 (t2key INTEGER NOT NULL AUTO_INCREMENT, t2datetime TIMESTAMP, t2date DATE, PRIMARY KEY (t2key))' );
}
readfile( $logfile );
if ( $fpDemo->error != 0 )
title( 'Error whilst creating tables, quitting demo, check log', true );
title( 'Creating debugLog table...' );
switch ( $fpDemo->dbType )
{
case 'ibase' :
case 'firebird' :
case 'borland_ibase' :
run( '$fpDemo->doSQL( \'CREATE TABLE debugLog (dtKey DATE DEFAULT \\\'now\\\' NOT NULL, side char(6) NOT NULL, debugText VARCHAR(42), PRIMARY KEY (dtKey))\' );' );
$fpDemo->doSQL( 'CREATE TABLE debugLog (dtKey DATE DEFAULT \'now\' NOT NULL, side char(6) NOT NULL, debugText VARCHAR(42), PRIMARY KEY (dtKey))' );
break;
default :
run( '$fpDemo->doSQL( \'CREATE TABLE debugLog (dtKey datetime NOT NULL DEFAULT \\\'0000-00-00 00:00:00\\\',side char(6) NOT NULL,debugText text,PRIMARY KEY (dtKey))\' );' );
$fpDemo->doSQL( 'CREATE TABLE debugLog (dtKey datetime NOT NULL DEFAULT \'0000-00-00 00:00:00\',side char(6) NOT NULL,debugText text,PRIMARY KEY (dtKey))' );
}
readfile( $logfile );
if ( $fpDemo->error != 0 )
title( 'Error whilst creating debugLog table, quitting demo, check log', true );
}
/* Removed as I don't want to expose all the other db's when running live on sshnug.com :) */
//title( 'Retrieving list of databases' );
//$fpDemo->doMeta( FP_META_TYPE_DATABASES );
//readfile( $logfile );
title( 'Retrieving field list in debugLog table...XML output is firefly friendly' );
/* set out datasource to output firefly friendly XML result */
run( '$fpDemo->dataSourceType = FP_DATA_SOURCE_FIREFLY;', false );
$fpDemo->dataSourceType = FP_DATA_SOURCE_FIREFLY;
/* On MySQL or any DB which return field meta-data, you could just use SELECT * FROM debugLog */
run( '$fpDemo->doMeta( FP_META_TYPE_SQL, \'SELECT \' . \'debugLog\' . FP_SQL_ALIAS . \'dtKey, \'. \'debugLog\' . FP_SQL_ALIAS . \'side, \' . \'debugLog\' . FP_SQL_ALIAS . \'debugText \' . \'FROM debugLog\' );' );
$fpDemo->doMeta( FP_META_TYPE_SQL, 'SELECT ' . 'debugLog' . FP_SQL_ALIAS . 'dtKey, '. 'debugLog' . FP_SQL_ALIAS . 'side, ' . 'debugLog' . FP_SQL_ALIAS . 'debugText ' . 'FROM debugLog' );
readfile( $logfile );
/*
Add some fields to our flashPash object, to do this we use the setField method.
Note that the first field is a key field which is of type auto_increment (or equivalent),
this is set to a value of NULL so that flashPash doesnt think you want to filter on it.
*/
/* Insert a few records...note that ibase/firebird needs generator reference */
title( 'Inserting 2 rows into table1 and table2' );
/* table1 */
run( '$fpDemo->setField( \'table1\', \'t1integer\', FP_ADODB_NUMERIC, false );', false );
$fpDemo->setField( 'table1', 't1integer', FP_ADODB_NUMERIC, false );
run( '$fpDemo->setKey( \'table1\', \'t1integer\' );', false );
$fpDemo->setKey( 'table1', 't1integer' );
run( '$fpDemo->setField( \'table1\', \'t1boolean\', FP_ADODB_BOOLEAN, false, 0 );', false );
$fpDemo->setField( 'table1', 't1boolean', FP_ADODB_BOOLEAN, false, 0 );
run( '$fpDemo->setField( \'table1\', \'t1varchar\', FP_ADODB_CHARACTER, true, \'Hello World\' );', false );
$fpDemo->setField( 'table1', 't1varchar', FP_ADODB_CHARACTER, true, "Hello World" );
run( '$fpDemo->setField( \'table1\', \'t1float\', FP_ADODB_NUMERIC, 3.14159 );', true );
$fpDemo->setField( 'table1', 't1float', FP_ADODB_NUMERIC, false, 3.14159 );
/* table2 */
run( '$fpDemo->setField( \'table2\', \'t2key\', FP_ADODB_NUMERIC, false );', false );
$fpDemo->setField( 'table2', 't2key', FP_ADODB_NUMERIC, false );
run( '$fpDemo->setKey( \'table2\', \'t2key\' );', false );
$fpDemo->setKey( 'table2', 't2key' );
run( '$fpDemo->setField( \'table2\', \'t2datetime\', FP_ADODB_TIMESTAMP, false, strftime( \"%Y-%m-%d %H:%M:%S\" ));', false );
$fpDemo->setField( 'table2', 't2datetime', FP_ADODB_TIMESTAMP, false, strftime( "%Y-%m-%d %H:%M:%S" ));
/* set date value to zero, we will update a record in doUpdate */
run( '$fpDemo->setField( \'table2\', \'t2date\', FP_ADODB_DATE, false, \'0000-00-00\' );', false );
$fpDemo->setField( 'table2', 't2date', FP_ADODB_DATE, false, '0000-00-00' );
/* now join table1 to table2 */
run( '$fpDemo->setJoin( \'table1\', \'t1integer\', FP_JOIN_INNER, \'table2\', \'t2key\' );', true );
$fpDemo->setJoin( 'table1', 't1integer', FP_JOIN_INNER, 'table2', 't2key' );
for ( $i = 0; $i < 2; $i++ )
{
switch ( $fpDemo->dbType )
{
case 'ibase' :
case 'firebird' :
case 'borland_ibase' :
run( '$fpDemo->setField( \'table1\', \'t1integer\', FP_ADODB_NUMERIC, \'GEN_ID(TEST_GEN, 1)\' );', false );
$fpDemo->setField( 'table1', 't1integer', FP_ADODB_NUMERIC, false, 'GEN_ID(TEST_GEN, 1)' );
break;
default:
/* ::To-Do:: Until flashPash handles auto-inc/counter field types nicely,
since there is a join will have to fudge and use current time as key... */
$currentTime = substr( mktime() + $i, 3 );
run( '$fpDemo->setField( \'table1\', \'t1integer\', FP_ADODB_NUMERIC, false, '. $currentTime . ' );', false );
$fpDemo->setField( 'table1', 't1integer', FP_ADODB_NUMERIC, false, $currentTime );
}
run( '$fpDemo->doInsert();', true );
$fpDemo->doInsert();
readfile( $logfile );
}
/* doSelect should now should return last row...since it's key field value was set it will be filtered by it :) */
title( 'Selecting last row inserted...' );
run( '$fpDemo->doSelect();' );
$fpDemo->doSelect();
readfile( $logfile );
/* now update first row in table if there was a row found */
if ( $fpDemo->recordCount > 0 )
{
title( 'Updating some fields in table1 and table2 from selected row' );
/* set key to first row in result */
run( '$fpDemo->setField( \'table1\', \'t1integer\', FP_ADODB_NUMERIC, false, $fpDemo->SQLresult[ 0 ][ \'table1\' ][ \'t1integer\' ] );', false );
$fpDemo->setField( 'table1', 't1integer', FP_ADODB_NUMERIC, false, $fpDemo->SQLresult[ 0 ][ 'table1' ][ 't1integer' ] );
run( '$fpDemo->setField( \'table2\', \'t2key\', FP_ADODB_NUMERIC, false );', false );
$fpDemo->setField( 'table2', 't2key', FP_ADODB_NUMERIC, false );
/* add some variable values to both tables so you can see multi-table updates working */
run( '$fpDemo->setField( \'table1\', \'t1float\', FP_ADODB_NUMERIC, false, ( 10000 / rand( 1, 1000 ));', false );
$fpDemo->setField( 'table1', 't1float', FP_ADODB_NUMERIC, false, ( 10000 / rand( 1, 1000 )));
/* remove value in t2datetime, as it has already been set when insert and we don't need to update */
run( '$fpDemo->setField( \'table2\', \'t2datetime\', FP_ADODB_TIMESTAMP, false );', false );
$fpDemo->setField( 'table2', 't2datetime', FP_ADODB_TIMESTAMP, false );
/* set the date field to today's date */
run( '$fpDemo->setField( \'table2\', \'t2date\', FP_ADODB_DATE, false, strftime( "%Y-%m-%d" ));', false );
$fpDemo->setField( 'table2', 't2date', FP_ADODB_DATE, false, strftime( "%Y-%m-%d" ));
run( '$fpDemo->doUpdate();' );
$fpDemo->doUpdate();
readfile( $logfile );
}
else
{
title( 'Error whilst inserting row, quitting demo, check log', true );
}
/* do another select, but only on 3 rows, and include some aliases */
title( 'Adding table and field aliases, selecting rows 2 to 4, ordered by t1float...' );
/* (re)set our key values so flashPash doesnt filter on them */
run( '$fpDemo->setField( \'table1\', \'t1integer\' );', false );
$fpDemo->setField( 'table1', 't1integer' );
run( '$fpDemo->setField( \'table2\', \'t2key\' );', false );
$fpDemo->setField( 'table2', 't2key' );
/* create a field alias for table1.t1float field */
run( '$fpDemo->setAlias( \'table1\', \'t1float\', null, \'YabbaDabbaDo\' );', false );
$fpDemo->setAlias( 'table1', 't1float', null, 'YabbaDabbaDo' );
/* create a table alias for table2.t2date field */
run( '$fpDemo->setAlias( \'table2\', \'t2date\', \'tableAlias\' );', false );
$fpDemo->setAlias( 'table2', 't2date', 'tableAlias' );
/* create a field and table alias for table2.t2datetime field */
run( '$fpDemo->setAlias( \'table2\', \'t2datetime\', \'AlienIs\', \'Beautiful\' );', false );
$fpDemo->setAlias( 'table2', 't2datetime', 'AlienIs', 'Beautiful' );
run( '$fpDemo->recordLimit = 3;', false );
$fpDemo->recordLimit = 3;
run( '$fpDemo->limitFrom = 1;', false );
$fpDemo->limitFrom = 1;
run( '$fpDemo->doSelect( \'ORDER BY t1float\' );' );
$fpDemo->doSelect( 'ORDER BY t1float' );
readfile( $logfile );
/* finally, a brief demo of the SQL computation introduced in RC1 */
title( 'Creating alias and running SQL for COUNT computation on table1' );
/* first we delete all our hard work :) */
run( '$fpDemo->deleteField( \'table1\' );', false );
$fpDemo->deleteField( 'table1' );
/* Note that deleteField can delete all fields in a table, just leave field name out of the method call */
run( '$fpDemo->deleteField( \'table2\' );', false );
$fpDemo->deleteField( 'table2' );
/* now create our alias with computation */
run( '$fpDemo->setAlias( \'table1\', \'t1integer\', null, \'recordcount\', \'COUNT( t1integer )\' );', false );
$fpDemo->setAlias( 'table1', 't1integer', null, 'recordcount', 'COUNT( t1integer )' );
run( '$fpDemo->recordLimit = 0;', false );
$fpDemo->recordLimit = 0;
run( '$fpDemo->limitFrom = 0;', false );
$fpDemo->limitFrom = 0;
run( '$fpDemo->doSelect();' );
$fpDemo->doSelect();
readfile( $logfile );
title( 'flashPash demo finished!' );
/* so that all made perfect sense? Good...as there will be a test on this first thing Monday morning! ;-) */
}
/* common functions */
function title( $text, $quit = false )
{
print '<br/><font color="#000000" size="+1"><strong><em>' . $text . '</em></strong></font><br/>';
if ( $quit ) die();
}
function run( $text, $br = true )
{
print '<br/>run> <font color="#990000">' . $text . '</font>';
if ( $br ) print '<br/>';
}
?>
|
Stumble It!
Bookmark in del.icio.us
Download
