function getConnection()
{
$conn = NULL;
\Murphy\Fixture::load(dirname(__FILE__) . '/../on_clause.class.php.murphy/fixture.php')->execute();
\Murphy\Fixture::load(dirname(__FILE__) . '/../query_iterator.class.php.murphy/fixture.php')->execute(function ($aliases) use(&$conn) {
$aliases = $aliases['plusql'];
$host = $aliases[0];
$username = $aliases[1];
$password = $aliases[2];
$dbname = $aliases[3];
$conn = new Connection($host, $username, $password, $dbname);
$conn->connect();
});
return $conn;
}
<?php
namespace PluSQL;
\Murphy\Test::add(function ($runner) {
\Murphy\Fixture::load(dirname(__FILE__) . '/../on_clause.class.php.murphy/fixture.php')->execute();
$conn = new Connection('localhost', 'plusql', 'plusql', 'plusql');
$conn->connect();
$query = new Query('SELECT * FROM strong_guy', $conn->link());
if ($query->strong_guy instanceof QueryIterator) {
$runner->pass();
} else {
$runner->fail('Did not get a QueryIterator for a table we should have been able to');
}
try {
$query->non_existant;
$runner->fail('Why was I able to try and get a non-existant member variable that isn\'t a valid table or field?');
} catch (TableInspectorException $exc) {
$runner->pass();
}
$row = $query->nextRow();
if (count($row)) {
$runner->fail('You called nextRow() on a query from which you\'d already extracted a QueryIterator - you should only be able to call rowAtIndex');
}
$row = $query->rowAtIndex(0);
if ($row['strong_name'] == 'Strongy Strongo') {
$runner->pass();
} else {
$runner->fail('The row returned from your query did not contain the right data');
}
$query = new Query('SELECT * FROM strong_guy', $conn->link());
public function run()
{
echo 'EXPECTED OUTPUT:' . PHP_EOL;
echo 'non db fixture
non db fixture
Non db fixture 2
Non db fixture 2
iain@workingsoftware.com.au
iaindooley@gmail.com' . PHP_EOL . PHP_EOL;
if (!($dbconfig_path = Args::get('dbconfig', Args::argv))) {
echo 'You need to include dbconfig in the command line arguments' . PHP_EOL;
exit(1);
}
if (!($dbconfig = (include $dbconfig_path))) {
echo 'You need to include dbconfig in the command line arguments' . PHP_EOL;
exit(1);
}
$this->link = mysqli_connect($dbconfig['db_host'], $dbconfig['db_user'], $dbconfig['db_pass']) or die(mysqli_error($this->link));
$this->link->query('DROP DATABASE IF EXISTS test_fixture1');
$this->link->query('DROP DATABASE IF EXISTS test_fixture2');
$this->link->query('CREATE DATABASE test_fixture1');
$this->link->query('CREATE DATABASE test_fixture2');
$this->link->select_db('test_fixture1');
$this->link->query('
CREATE TABLE `user` (
`user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL DEFAULT \'\',
`password` varchar(255) NOT NULL DEFAULT \'\',
`date_created` datetime DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1');
$this->link->query('
CREATE TABLE `group` (
`group_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`group_name` varchar(255) NOT NULL DEFAULT \'\',
PRIMARY KEY (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1');
$this->link->query('
CREATE TABLE `user_in_group` (
`user_id` bigint(20) unsigned NOT NULL DEFAULT \'0\',
`group_id` bigint(20) unsigned NOT NULL DEFAULT \'0\',
PRIMARY KEY (`user_id`,`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1');
$this->link->select_db('test_fixture2');
$this->link->query('
CREATE TABLE `user` (
`user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL DEFAULT \'\',
`password` varchar(255) NOT NULL DEFAULT \'\',
`date_created` datetime DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1');
$this->link->query('
CREATE TABLE `group` (
`group_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`group_name` varchar(255) NOT NULL DEFAULT \'\',
PRIMARY KEY (`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1');
$this->link->query('
CREATE TABLE `user_in_group` (
`user_id` bigint(20) unsigned NOT NULL DEFAULT \'0\',
`group_id` bigint(20) unsigned NOT NULL DEFAULT \'0\',
PRIMARY KEY (`user_id`,`group_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1');
Fixture::load(dirname(__FILE__) . '/sample.fixture.php')->also(dirname(__FILE__) . '/sample2.fixture.php')->execute(function ($db_aliases) {
$credential_names = array('test_fixture1' => 'live', 'test_fixture2' => 'dev');
foreach ($db_aliases as $src => $credentials) {
Plusql::credentials($credential_names[$src], $credentials);
}
});
Fixture::load(dirname(__FILE__) . '/sample3.fixture.php')->execute();
foreach (Plusql::from('live')->user->select('user_id,username')->run()->user as $client) {
echo $client->username . PHP_EOL;
}
}
<?php
namespace PluSQL;
use Plusql, mysqli;
/**
* Testing the quoting behaviour and default values
*/
\Murphy\Test::add(function ($runner) {
$sample_values = array(3, 3.5, '3,5', 3.55, 20120101131313.0, 20120101, '2012-01-01 13:13:13', 0, '', 'this is a string', '1234567890-09876543456y7uioijuhygfdszxcvbnjml,;/.,mn,.,mnbnm\'\\"\'\'\\\'\\\\"[]}{].......3.3.3.3.3.3.3.3.3.3.3.3.3.3.3.3=-0987654321!@#$%^&*(~~!@#$%^&*()+_)OP{}|}{P;\';lkl;KJHGVBCDRFGHbvcxszaswsadfGVBHN');
$sample_queries = array('INSERT INTO `type_test`(`int_auto_field`,`varchar_field_default_null`,`varchar_field_default_something`,`int_field_default_10`,`int_field_default_null`,`float_field_default_null`,`float_field_default_2point5`,`double_field_default_null`,`double_field_default_2point555`,`decimal_field_default_null`,`decimal_field_default_10point2`,`datetime_field_default_null`,`datetime_field_default_something`) VALUES(3,\'3\',\'3\',3,3,3,3,3,3,\'3\',\'3\',\'3\',\'3\')', 'INSERT INTO `type_test`(`int_auto_field`,`varchar_field_default_null`,`varchar_field_default_something`,`int_field_default_10`,`int_field_default_null`,`float_field_default_null`,`float_field_default_2point5`,`double_field_default_null`,`double_field_default_2point555`,`decimal_field_default_null`,`decimal_field_default_10point2`,`datetime_field_default_null`,`datetime_field_default_something`) VALUES(3,\'3.5\',\'3.5\',3,3,3.5,3.5,3.5,3.5,\'3.5\',\'3.5\',\'3.5\',\'3.5\')', 'INSERT INTO `type_test`(`int_auto_field`,`varchar_field_default_null`,`varchar_field_default_something`,`int_field_default_10`,`int_field_default_null`,`float_field_default_null`,`float_field_default_2point5`,`double_field_default_null`,`double_field_default_2point555`,`decimal_field_default_null`,`decimal_field_default_10point2`,`datetime_field_default_null`,`datetime_field_default_something`) VALUES(3,\'3,5\',\'3,5\',3,3,35,35,35,35,\'3,5\',\'3,5\',\'3,5\',\'3,5\')', 'INSERT INTO `type_test`(`int_auto_field`,`varchar_field_default_null`,`varchar_field_default_something`,`int_field_default_10`,`int_field_default_null`,`float_field_default_null`,`float_field_default_2point5`,`double_field_default_null`,`double_field_default_2point555`,`decimal_field_default_null`,`decimal_field_default_10point2`,`datetime_field_default_null`,`datetime_field_default_something`) VALUES(3,\'3.55\',\'3.55\',3,3,3.55,3.55,3.55,3.55,\'3.55\',\'3.55\',\'3.55\',\'3.55\')', 'INSERT INTO `type_test`(`int_auto_field`,`varchar_field_default_null`,`varchar_field_default_something`,`int_field_default_10`,`int_field_default_null`,`float_field_default_null`,`float_field_default_2point5`,`double_field_default_null`,`double_field_default_2point555`,`decimal_field_default_null`,`decimal_field_default_10point2`,`datetime_field_default_null`,`datetime_field_default_something`) VALUES(20120101131313,\'20120101131313\',\'20120101131313\',20120101131313,20120101131313,20120101131313,20120101131313,20120101131313,20120101131313,\'20120101131313\',\'20120101131313\',\'20120101131313\',\'20120101131313\')', 'INSERT INTO `type_test`(`int_auto_field`,`varchar_field_default_null`,`varchar_field_default_something`,`int_field_default_10`,`int_field_default_null`,`float_field_default_null`,`float_field_default_2point5`,`double_field_default_null`,`double_field_default_2point555`,`decimal_field_default_null`,`decimal_field_default_10point2`,`datetime_field_default_null`,`datetime_field_default_something`) VALUES(20120101,\'20120101\',\'20120101\',20120101,20120101,20120101,20120101,20120101,20120101,\'20120101\',\'20120101\',\'20120101\',\'20120101\')', 'INSERT INTO `type_test`(`int_auto_field`,`varchar_field_default_null`,`varchar_field_default_something`,`int_field_default_10`,`int_field_default_null`,`float_field_default_null`,`float_field_default_2point5`,`double_field_default_null`,`double_field_default_2point555`,`decimal_field_default_null`,`decimal_field_default_10point2`,`datetime_field_default_null`,`datetime_field_default_something`) VALUES(2012,\'2012-01-01 13:13:13\',\'2012-01-01 13:13:13\',2012,2012,20120101131313,20120101131313,20120101131313,20120101131313,\'2012-01-01 13:13:13\',\'2012-01-01 13:13:13\',\'2012-01-01 13:13:13\',\'2012-01-01 13:13:13\')', 'INSERT INTO `type_test`(`int_auto_field`,`varchar_field_default_null`,`varchar_field_default_something`,`int_field_default_10`,`int_field_default_null`,`float_field_default_null`,`float_field_default_2point5`,`double_field_default_null`,`double_field_default_2point555`,`decimal_field_default_null`,`decimal_field_default_10point2`,`datetime_field_default_null`,`datetime_field_default_something`) VALUES(0,\'0\',\'0\',0,0,0,0,0,0,\'0\',\'0\',\'0\',\'0\')', 'INSERT INTO `type_test`(`int_auto_field`,`varchar_field_default_null`,`varchar_field_default_something`,`int_field_default_10`,`int_field_default_null`,`float_field_default_null`,`float_field_default_2point5`,`double_field_default_null`,`double_field_default_2point555`,`decimal_field_default_null`,`decimal_field_default_10point2`,`datetime_field_default_null`,`datetime_field_default_something`) VALUES(0,\'\',\'\',0,0,0,0,0,0,\'\',\'\',\'\',\'\')', 'INSERT INTO `type_test`(`int_auto_field`,`varchar_field_default_null`,`varchar_field_default_something`,`int_field_default_10`,`int_field_default_null`,`float_field_default_null`,`float_field_default_2point5`,`double_field_default_null`,`double_field_default_2point555`,`decimal_field_default_null`,`decimal_field_default_10point2`,`datetime_field_default_null`,`datetime_field_default_something`) VALUES(0,\'this is a string\',\'this is a string\',0,0,0,0,0,0,\'this is a string\',\'this is a string\',\'this is a string\',\'this is a string\')', 'INSERT INTO `type_test`(`int_auto_field`,`varchar_field_default_null`,`varchar_field_default_something`,`int_field_default_10`,`int_field_default_null`,`float_field_default_null`,`float_field_default_2point5`,`double_field_default_null`,`double_field_default_2point555`,`decimal_field_default_null`,`decimal_field_default_10point2`,`datetime_field_default_null`,`datetime_field_default_something`) VALUES(1234567890,\'1234567890-09876543456y7uioijuhygfdszxcvbnjml,;/.,mn,.,mnbnm\\\'\\\\\\"\\\'\\\'\\\\\\\'\\\\\\\\\\"[]}{].......3.3.3.3.3.3.3.3.3.3.3.3.3.3.3.3=-0987654321!@#$%^&*(~~!@#$%^&*()+_)OP{}|}{P;\\\';lkl;KJHGVBCDRFGHbvcxszaswsadfGVBHN\',\'1234567890-09876543456y7uioijuhygfdszxcvbnjml,;/.,mn,.,mnbnm\\\'\\\\\\"\\\'\\\'\\\\\\\'\\\\\\\\\\"[]}{].......3.3.3.3.3.3.3.3.3.3.3.3.3.3.3.3=-0987654321!@#$%^&*(~~!@#$%^&*()+_)OP{}|}{P;\\\';lkl;KJHGVBCDRFGHbvcxszaswsadfGVBHN\',1234567890,1234567890,1234567890098765434567.33333333333333330987654321,1234567890098765434567.33333333333333330987654321,1234567890098765434567.33333333333333330987654321,1234567890098765434567.33333333333333330987654321,\'1234567890-09876543456y7uioijuhygfdszxcvbnjml,;/.,mn,.,mnbnm\\\'\\\\\\"\\\'\\\'\\\\\\\'\\\\\\\\\\"[]}{].......3.3.3.3.3.3.3.3.3.3.3.3.3.3.3.3=-0987654321!@#$%^&*(~~!@#$%^&*()+_)OP{}|}{P;\\\';lkl;KJHGVBCDRFGHbvcxszaswsadfGVBHN\',\'1234567890-09876543456y7uioijuhygfdszxcvbnjml,;/.,mn,.,mnbnm\\\'\\\\\\"\\\'\\\'\\\\\\\'\\\\\\\\\\"[]}{].......3.3.3.3.3.3.3.3.3.3.3.3.3.3.3.3=-0987654321!@#$%^&*(~~!@#$%^&*()+_)OP{}|}{P;\\\';lkl;KJHGVBCDRFGHbvcxszaswsadfGVBHN\',\'1234567890-09876543456y7uioijuhygfdszxcvbnjml,;/.,mn,.,mnbnm\\\'\\\\\\"\\\'\\\'\\\\\\\'\\\\\\\\\\"[]}{].......3.3.3.3.3.3.3.3.3.3.3.3.3.3.3.3=-0987654321!@#$%^&*(~~!@#$%^&*()+_)OP{}|}{P;\\\';lkl;KJHGVBCDRFGHbvcxszaswsadfGVBHN\',\'1234567890-09876543456y7uioijuhygfdszxcvbnjml,;/.,mn,.,mnbnm\\\'\\\\\\"\\\'\\\'\\\\\\\'\\\\\\\\\\"[]}{].......3.3.3.3.3.3.3.3.3.3.3.3.3.3.3.3=-0987654321!@#$%^&*(~~!@#$%^&*()+_)OP{}|}{P;\\\';lkl;KJHGVBCDRFGHbvcxszaswsadfGVBHN\')');
$field_names = array('int_auto_field', 'varchar_field_default_null', 'varchar_field_default_something', 'int_field_default_10', 'int_field_default_null', 'float_field_default_null', 'float_field_default_2point5', 'double_field_default_null', 'double_field_default_2point555', 'decimal_field_default_null', 'decimal_field_default_10point2', 'datetime_field_default_null', 'datetime_field_default_something');
foreach ($sample_values as $index => $sv) {
$expected_query = $sample_queries[$index];
\Murphy\Fixture::load(dirname(__FILE__) . '/fixture.php')->execute();
$to = 'plusql';
$conn = new Connection('localhost', $to, $to, $to);
$conn->connect();
$ins = new Insert($conn);
$cur_array = array();
foreach ($field_names as $fn) {
$cur_array[$fn] = $sv;
}
$sql = $ins->type_test($cur_array)->insertSql();
//WE SHOULDN'T GET ANY SQL ERRORS - INJECTION FREE
$conn->query($sql);
if ($sql !== $expected_query) {
$runner->fail('The sample value: ' . $sv . ' produced an unexpected query result: ' . $sql . ' !== ' . $expected_query);
} else {
$runner->pass();
}
<?php
namespace PluSQL;
/**
* Test accessing fields and related tables, both existing and non-existant
*/
\Murphy\Test::add(function ($runner) {
$conn = NULL;
\Murphy\Fixture::load(dirname(__FILE__) . '/../on_clause.class.php.murphy/fixture.php')->execute();
\Murphy\Fixture::load(dirname(__FILE__) . '/../query_iterator.class.php.murphy/fixture.php')->execute(function ($aliases) use(&$conn) {
$deets = $aliases['plusql'];
$conn = new Connection($deets[0], $deets[1], $deets[2], $deets[3]);
$conn->connect();
});
$query = new Query('SELECT * FROM strong_guy', $conn->link());
$row = new QueryRow($query, 'strong_guy', 0);
if ($row->keySignature() == 2) {
$runner->pass();
} else {
$runner->fail('Got the incorrect key signature for the strong_guy at index 0');
}
if ($row->strong_name == 'Strong 1') {
$runner->pass();
} else {
$runner->fail('Got the wrong strong_name for strong_guy at index 0');
}
$query = new Query('SELECT * FROM weak_guy', $conn->link());
$row = new QueryRow($query, 'weak_guy', 0);
if ($row->keySignature() == '2::1') {
$runner->pass();
*/
\Murphy\Test::add(function ($runner) {
\Murphy\Fixture::load(dirname(__FILE__) . '/../on_clause.class.php.murphy/fixture.php')->execute();
$conn = new Connection('localhost', 'plusql', 'plusql', 'plusql');
$conn->connect();
$query = new Query('SELECT * FROM strong_guy', $conn->link());
$iterator = new QueryIterator($query, 'strong_guy', 0);
$pairs = array('strong_guy_id' => 1);
$iterator->constrainKeys($pairs);
if ($iterator->strong_name == 'Strongy Strongo') {
$runner->pass();
} else {
$runner->fail('Could not get value for strong_name from strong_guy iterator');
}
$conn = NULL;
\Murphy\Fixture::load(dirname(__FILE__) . '/../on_clause.class.php.murphy/fixture.php')->also(dirname(__FILE__) . '/fixture.php')->execute(function ($aliases) use(&$conn) {
$deets = $aliases['plusql'];
$conn = new Connection($deets[0], $deets[1], $deets[2], $deets[3]);
$conn->connect();
});
$expected_output = trim('
Strong 1:Weak 1:Rogue 1:French 1
Strong 1:Weak 2:Rogue 1:French 1
Strong 2:Weak 3:Rogue 1:French 2
Strong 2:Weak 3:Rogue 2:French 2
Strong 2:Weak 4:Rogue 2:French 2
');
$query = new Query('SELECT * FROM strong_guy INNER JOIN weak_guy USING(strong_guy_id) INNER JOIN is_rogue USING(strong_guy_id,weak_guy_id) INNER JOIN rogue_guy USING (rogue_guy_id) INNER JOIN french_guy USING(french_guy_id) ORDER BY strong_guy_id,weak_guy_id,rogue_guy_id', $conn->link());
$iterator = new QueryIterator($query, 'strong_guy', 0);
$start = microtime(true);
ob_start();
<?php
namespace PluSQL\fixture\query_iterator;
require_once dirname(__FILE__) . '/common.php';
/**
* @database plusql
* @tables strong_guy,weak_guy,rogue_guy,is_rogue,french_guy
* strong_name | weak_name | rogue_name | french_name
* Strong 1 | Weak 1 | Rogue 1 | French 1
* Strong 1 | Weak 2 | Rogue 1 | French 1
* Strong 2 | Weak 3 | Rogue 1 | French 2
* Strong 2 | Weak 3 | Rogue 2 | French 2
* Strong 2 | Weak 4 | Rogue 2 | French 2
*/
\Murphy\Fixture::add(function ($row) {
$strong_guy_id = createOrRetrieveGuy('strong_guy', array('strong_guy_id'), 'strong_name', $row['strong_name']);
$weak_guy_id = createOrRetrieveGuy('weak_guy', array('strong_guy_id', 'weak_guy_id'), 'weak_name', $row['weak_name'], array('strong_guy_id' => $strong_guy_id));
$rogue_guy_id = createOrRetrieveGuy('rogue_guy', array('rogue_guy_id'), 'rogue_name', $row['rogue_name']);
$french_guy_id = createOrRetrieveGuy('french_guy', array('french_guy_id'), 'french_name', $row['french_name']);
mysql_query('UPDATE weak_guy SET french_guy_id = ' . $french_guy_id . ' WHERE strong_guy_id = ' . $strong_guy_id . ' AND weak_guy_id = ' . $weak_guy_id);
mysql_query('REPLACE INTO is_rogue VALUES(' . $strong_guy_id . ',' . $weak_guy_id . ',' . $rogue_guy_id . ')');
});
