public function testTransactions()
{
$this->assertEquals(0, $this->db->Execute('CREATE TABLE IF NOT EXISTS t_test (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB'));
$this->assertEquals(0, $this->db->Execute('TRUNCATE TABLE t_test'));
$this->assertEquals(0, $this->db->StartTrans());
$this->assertTrue($this->db->Execute('INSERT INTO t_test VALUES(1)'));
$this->assertEquals(0, $this->db->StartTrans());
$this->assertTrue($this->db->Execute('INSERT INTO t_test VALUES(2)'));
$this->assertEquals(array(array('id' => 1), array('id' => 2)), $this->db->FetchMode('ASSOC')->GetAll('SELECT * FROM t_test'));
$this->assertEquals(0, $this->db->StartTrans());
$this->assertTrue($this->db->Execute('INSERT INTO t_test VALUES(3)'));
$this->assertEquals(array(array('id' => 1), array('id' => 2), array('id' => 3)), $this->db->FetchMode('ASSOC')->GetAll('SELECT * FROM t_test'));
$this->assertEquals(0, $this->db->StartTrans());
$this->assertTrue($this->db->Execute('INSERT INTO t_test VALUES(7)'));
$this->assertEquals(array(array('id' => 1), array('id' => 2), array('id' => 3), array('id' => 7)), $this->db->FetchMode('ASSOC')->GetAll('SELECT * FROM t_test'));
$this->assertEquals(0, $this->db->RollBackTrans());
$this->assertTrue($this->db->CompleteTrans());
$this->assertTrue($this->db->CompleteTrans());
$this->assertTrue($this->db->CompleteTrans());
$this->assertEquals(array(array('id' => 1), array('id' => 2), array('id' => 3)), $this->db->FetchMode('ASSOC')->GetAll('SELECT * FROM t_test'));
}
<?php
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
$rs = $db->FetchMode('ASSOC')->PGetAll('SELECT @i:=@i+1 AS iterator, t.* from Country as t, (SELECT @i:=0) AS r');
print_r($rs);
# ------------------------------------------------------------------------------
echo PHP_EOL, str_repeat('-', 80), PHP_EOL;
echo str_repeat('-', 80), PHP_EOL, 'Time: ', $timer->getPageLoadTime(), ' - Memory: ', $timer->getMemoryUsage(1), PHP_EOL, str_repeat('-', 80), PHP_EOL;
<?php
error_reporting(-1);
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# -----------------------------------------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
$dsns = array('utf8://root:mysql@127.0.0.1/dalmp3', 'utf8://root:mysql@127.0.0.1/dalmp4', 'utf8://root:mysql@127.0.0.1/dalmp5', "utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
$cdb = new DALMP\Database($dsns[0]);
$result = false;
while (!$result) {
try {
$result = $cdb->FetchMode('ASSOC')->PgetAll('SELECT NOW()');
} catch (\Exception $e) {
array_shift($dsns);
$cdb = new DALMP\Database($dsns[0]);
if ($dsns) {
$cdb = new DALMP\Database($dsns[0]);
} else {
throw $e;
}
echo $e->getMessage(), PHP_EOL;
}
}
print_r($result);
#------------------------------------------------------------------------------
echo PHP_EOL, str_repeat('-', 80), PHP_EOL, 'Time: ', $timer->getPageLoadTime(), ' - Memory: ', $timer->getMemoryUsage(1), PHP_EOL, str_repeat('-', 80), PHP_EOL;
<?php
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$db1 = new DALMP\Database('utf8://dalmp:password@127.0.0.1:3306/dalmptest');
$db2 = new DALMP\Database('uft8://root:' . rawurlencode('pass-?/:word') . '@remote.localbox.org:3307/dalmptest');
$rs1 = $db1->FetchMode('ASSOC')->getall('SELECT * FROM Country limit 1');
print_r($rs1);
$rs2 = $db2->FetchMode('NUM')->getall('SELECT * FROM City limit 1');
print_r($rs2);
# ------------------------------------------------------------------------------
echo PHP_EOL, str_repeat('-', 80), PHP_EOL, 'Time: ', $timer->getPageLoadTime(), ' - Memory: ', $timer->getMemoryUsage(1), PHP_EOL, str_repeat('-', 80), PHP_EOL;
/**
* delSessionRef - delete sessions containing a specific reference
*
* @param string $ref
* @return boolean
*/
public function delSessionRef($ref)
{
return $this->DB->PExecute('DELETE FROM ' . $this->dalmp_sessions_table . ' WHERE ref=?', $ref);
}
<?php
error_reporting(-1);
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
$db->debug();
$db->Execute('CREATE TABLE IF NOT EXISTS t_test (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB');
$db->Execute('TRUNCATE TABLE t_test');
$db->FetchMode('ASSOC');
$db->StartTrans();
$db->Execute('INSERT INTO t_test VALUES(1)');
$db->StartTrans();
$db->Execute('INSERT INTO t_test VALUES(2)');
print_r($db->GetAll('SELECT * FROM t_test'));
$db->StartTrans();
$db->Execute('INSERT INTO t_test VALUES(3)');
print_r($db->GetAll('SELECT * FROM t_test'));
$db->StartTrans();
$db->Execute('INSERT INTO t_test VALUES(7)');
print_r($db->GetALL('SELECT * FROM t_test'));
$db->RollBackTrans();
print_r($db->GetALL('SELECT * FROM t_test'));
$db->CompleteTrans();
$db->CompleteTrans();
<?php
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
/**
* load zone files to mysql
* mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
*/
$db->PExecute('SET time_zone=?', '+00:00');
$db->FetchMode('ASSOC');
$sql = 'SELECT Name, Continent FROM Country WHERE Population > ? AND Code LIKE ? LIMIT ?';
$rs = $db->PGetAll($sql, 10000000, '%P%', 2);
print_r($rs);
$rs = $db->Execute('DROP TABLE IF EXISTS `tests`');
$rs = $db->Execute('CREATE TABLE `tests` (id INT(11) unsigned NOT NULL AUTO_INCREMENT, col1 varchar(255), col2 varchar(255), col3 varchar(255), status iNT(1), PRIMARY KEY (id))');
$rs = $db->AutoExecute('tests', array('col1' => 'ai eu', 'col2' => 2, 'status' => 0));
/**
* status value is 0 or 1 on table
* NOTICE the use of ===
*/
$sql = 'SELECT status FROM tests WHERE id=?';
$rs = $db->PgetOne($sql, 3);
if ($rs === false) {
echo "no result" . $timer->isCli(1);
case 2:
echo "In process: 2", PHP_EOL;
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
$db->StartTrans();
$credit = $db->PGetOne('SELECT credit FROM t_test2 WHERE id=? FOR UPDATE', 1);
if ($credit > 0) {
$db->PExecute('UPDATE t_test2 SET credit=credit - ? WHERE id = ?', 100, 1);
}
echo "process {$i} credit: ", $db->PGetOne('SELECT credit FROM t_test2'), PHP_EOL;
$rs = $db->CompleteTrans();
echo 'Transaction returned: ', (bool) $rs, PHP_EOL;
exit($i);
break;
case 3:
echo "In process: 3", PHP_EOL;
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
$db->StartTrans();
$credit = $db->PGetOne('SELECT credit FROM t_test2 WHERE id=? FOR UPDATE', 1);
if ($credit > 0) {
$db->PExecute('UPDATE t_test2 SET credit=credit - ? WHERE id = ?', 100, 1);
}
echo "process {$i} credit: ", $db->PGetOne('SELECT credit FROM t_test2'), PHP_EOL;
$rs = $db->CompleteTrans();
echo 'Transaction returned: ', (bool) $rs, PHP_EOL;
exit($i);
}
}
}
while (pcntl_waitpid(0, $status) != -1) {
$status = pcntl_wexitstatus($status);
echo "Child {$status} completed", PHP_EOL;
<?php
error_reporting(-1);
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# -----------------------------------------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
$dsn = "utf8://{$user}:{$password}@{$host}:{$port}/dalmp";
$db = new DALMP\Database($dsn);
$rs = $db->GetOne('SELECT DATABASE()');
echo $rs, PHP_EOL;
$db->X()->select_db('mysql');
$rs = $db->GetOne('SELECT DATABASE()');
echo $rs, PHP_EOL;
#------------------------------------------------------------------------------
echo PHP_EOL, str_repeat('-', 80), PHP_EOL, 'Time: ', $timer->getPageLoadTime(), ' - Memory: ', $timer->getMemoryUsage(1), PHP_EOL, str_repeat('-', 80), PHP_EOL;
<?php
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
$db->FetchMode('ASSOC');
$ors = $db->map('SELECT * FROM City WHERE Name="Toluca"');
echo sprintf('ID: %d CountryCode: %s', $ors->ID, $ors->CountryCode), PHP_EOL;
print_r($ors);
# ------------------------------------------------------------------------------
echo PHP_EOL, str_repeat('-', 80), PHP_EOL;
echo str_repeat('-', 80), PHP_EOL, 'Time: ', $timer->getPageLoadTime(), ' - Memory: ', $timer->getMemoryUsage(1), PHP_EOL, str_repeat('-', 80), PHP_EOL;
<?php
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
/**
* CSV - export to comma separated values.
*/
// simple query
$db->csv("SELECT * FROM Country WHERE Continent = 'Europe'");
// prepared statements
$db->csv('SELECT * FROM Country WHERE Continent = ?', 'Europe');
# ------------------------------------------------------------------------------
echo PHP_EOL, str_repeat('-', 80), PHP_EOL, 'Time: ', $timer->getPageLoadTime(), ' - Memory: ', $timer->getMemoryUsage(1), PHP_EOL, str_repeat('-', 80), PHP_EOL;
<?php
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
$db->debug();
$values = array(array(1, 2, 3), array(1, 3), array('date', 'select', 3), array('niño', 'coraçao', 'Ú'), array(null, 5, 7));
$rs = $db->multipleInsert('tests', array('col1', 'col2', 'col3'), $values);
var_dump($rs);
print_r($db->FetchMode('ASSOC')->GetALL('SELECT * FROM tests'));
# ------------------------------------------------------------------------------
echo PHP_EOL, str_repeat('-', 80), PHP_EOL, 'Time: ', $timer->getPageLoadTime(), ' - Memory: ', $timer->getMemoryUsage(1), PHP_EOL, str_repeat('-', 80), PHP_EOL;
<?php
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
$DSN = "utf8://{$user}:{$password}@{$host}:{$port}/dalmp?redis:127.0.0.1:6379";
$db = new DALMP\Database($DSN);
$db->FetchMode('ASSOC');
$rs = $db->CacheGetAll('SELECT * FROM City');
print_r($rs);
# ------------------------------------------------------------------------------
echo PHP_EOL, str_repeat('-', 80), PHP_EOL;
echo str_repeat('-', 80), PHP_EOL, 'Time: ', $timer->getPageLoadTime(), ' - Memory: ', $timer->getMemoryUsage(1), PHP_EOL, str_repeat('-', 80), PHP_EOL;
/**
* redis cache instance
*/
$redis = new DALMP\Cache(new DALMP\Cache\Redis());
/**
* disk cache instance
*/
$disk = new DALMP\Cache(new DALMP\Cache\Disk());
/**
* database instance
*/
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
$sql = 'SELECT * FROM Country LIMIT 2';
/**
* Cache for 5 minutes with key: mykey using memcache cache
*/
$db->useCache($memcache);
$rs = $db->CacheGetAll(300, $sql, 'mykey');
$timer->setMark('memcache');
echo count($rs), PHP_EOL;
$rs = $db->CacheGetAll(300, $sql, 'mykey');
$timer->setMark('memcache2');
echo count($rs), PHP_EOL;
/**
* Cache for 5 minutes with key: mykey using redis cache
*/
$db->debug();
(
[Variable_name] => Ssl_cipher
[Value] => DHE-RSA-AES256-SHA
)
* otherwise
Array
(
[Variable_name] => Ssl_cipher
[Value] =>
)
*/
/**
* example using a socket for the connection
*
* charset: utf8
* user: $user
* password: $password
* socket path: /tmp/mysql.sock
* database: dalmp
*/
$db = new DALMP\Database("utf8://{$user}:{$password}" . '@unix_socket=\\tmp\\mysql.sock/dalmp');
$db->debug(1);
try {
echo PHP_EOL, 'example using unix_socket: ', $db->getOne('SELECT NOW()'), PHP_EOL;
} catch (\Exception $e) {
print_r($e->getMessage());
}
echo $db;
// will print: DALMP :: connected to: db4, Character set: utf8, Localhost via UNIX socket,...
# -----------------------------------------------------------------------------------------------------------------
echo PHP_EOL, str_repeat('-', 80), PHP_EOL, 'Time: ', $timer->getPageLoadTime(), ' - Memory: ', $timer->getMemoryUsage(1), PHP_EOL, str_repeat('-', 80), PHP_EOL;
<?php
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
define('DALMP_MYSQLI_INIT_COMMAND', 'SET time_zone="-05:00"');
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
echo "-05:00 time: ", $db->GetOne('SELECT NOW()');
/**
* load zone files to mysql
* mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
*/
$db->PExecute('SET time_zone=?', '+00:00');
echo PHP_EOL, 'UTC time: ', $db->GetOne('SELECT NOW()');
echo PHP_EOL, 'lc_time_names: ', $db->GetOne('SELECT @@lc_time_names');
echo ': ', $db->PGetOne("SELECT DATE_FORMAT(?,'%W %a %M %b')", '2010-01-01');
$db->PExecute('SET lc_time_names=?', 'es_MX');
echo PHP_EOL, 'lc_time_names: ', $db->GetOne('SELECT @@lc_time_names');
echo ': ', $db->PGetOne("SELECT DATE_FORMAT(?,'%W %a %M %b')", '2010-01-01');
$db->PExecute('SET lc_time_names=?', 'pt_BR');
echo PHP_EOL, 'lc_time_names: ', $db->GetOne('SELECT @@lc_time_names');
echo ': ', $db->PGetOne("SELECT DATE_FORMAT(?,'%W %a %M %b')", '2010-01-01');
# ------------------------------------------------------------------------------
echo PHP_EOL, str_repeat('-', 80), PHP_EOL, 'Time: ', $timer->getPageLoadTime(), ' - Memory: ', $timer->getMemoryUsage(1), PHP_EOL, str_repeat('-', 80), PHP_EOL;
public function testCacheFlushEnd()
{
$rs = $this->db->CacheFlush();
$this->assertTrue($rs);
}
<?php
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_HOST') ?: '3306';
/**
* Cache engine defined on DSN
*/
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp?redis:127.0.0.1:6379");
$db->FetchMode('ASSOC');
/**
* Cache for 5 minutes, group A
*/
$rs = $db->CachePGetAll(300, 'SELECT * FROM Country WHERE Region = ?', 'Caribbean', 'group:A');
echo count($rs), PHP_EOL;
$timer->setMark('300');
/**
* Cache for 1 day (86400 seconds), group B
*/
$rs = $db->CachePGetAll(86400, 'SELECT * FROM Country WHERE Continent = ?', 'Europe', 'group:B');
echo count($rs), PHP_EOL;
$timer->setMark('86400');
/**
* Cache for 1 hour (default), group C
*/
$rs = $db->CachePGetAll('SELECT * FROM Country WHERE Population <= ?', 100000, 'group:C');
error_reporting(-1);
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
/**
* path of the queue
*/
define('DALMP_QUEUE_DB', '/tmp/queue.db');
# optional if you want to encrypt the sqlite db
#define('DALMP_SQLITE_ENC_KEY', 'na1ujhrjhqev{5#nyxx~oaV9aqrf3kll');
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
/**
* In case something goes wrong, the database is unavailable, fields missing, etc, you can save 'sql query' and later process it again.
*/
$sql = "INSERT INTO testX SET colA=(NOW())";
try {
$rs = $db->Execute($sql);
} catch (\Exception $e) {
$db->queue($sql, 'my-queue');
}
/**
* Save some $_POST/$_GET data in json format
*/
$get = array('uuid' => $db->UUID(), 'cdate' => @date('c'), 'field1' => 1, 'field2' => 2);
$db->queue(json_encode($get), 'json');
/**
<?php
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
foreach ($db->GetCol('SHOW TABLES') as $table) {
$rs = $db->Execute("OPTIMIZE TABLE {$table}");
echo "optimizing {$table}: {$rs}", PHP_EOL;
$rs = $db->Execute("REPAIR TABLE {$table} QUICK");
echo "repairing {$table}: {$rs}", PHP_EOL;
}
# ------------------------------------------------------------------------------
echo PHP_EOL, str_repeat('-', 80), PHP_EOL, 'Time: ', $timer->getPageLoadTime(), ' - Memory: ', $timer->getMemoryUsage(1), PHP_EOL, str_repeat('-', 80), PHP_EOL;
<?php
require_once '../../MPLT.php';
$timer = new MPLT();
require_once '../../src/dalmp.php';
# ------------------------------------------------------------------------------
$timer->setMark('start');
$user = getenv('MYSQL_USER') ?: 'root';
$password = getenv('MYSQL_PASS') ?: '';
$host = getenv('MYSQL_HOST') ?: '127.0.0.1';
$port = getenv('MYSQL_PORT') ?: '3306';
$db = new DALMP\Database("utf8://{$user}:{$password}@{$host}:{$port}/dalmp");
$db->FetchMode('NUM');
$sql = 'SELECT * FROM City';
$rs = $db->Execute($sql);
if ($rs) {
while (($rows = $db->query()) != false) {
list($r1, $r2, $r3) = $rows;
echo "w1: {$r1}, w2: {$r2}, w3: {$r3}", $timer->isCli(1);
}
}
$timer->setMark('while');
/**
* doing the same but consuming more memory.
* Below the returned $rs2 array is not referential. Because of that, the system
* will use excesive memory. With large columns.
*/
$rs2 = $db->GetAll($sql);
foreach ($rs2 as $value) {
list($r1, $r2, $r3) = $value;
echo "f1: {$r1}, f2: {$r2}, f3: {$r3}", $timer->isCli(1);