public function gc($maxlifetime) { $sql = 'DELETE FROM ' . $this->dalmp_sessions_table . ' WHERE expiry < UNIX_TIMESTAMP()'; $this->DB->Execute($sql); $sql = 'OPTIMIZE TABLE ' . $this->dalmp_sessions_table; $this->DB->Execute($sql); return true; }
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"); 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 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->Execute('CREATE TABLE IF NOT EXISTS t_test2 (id INT NOT NULL PRIMARY KEY, credit DECIMAL(9,2)) ENGINE=InnoDB'); $db->Execute('TRUNCATE TABLE t_test2'); $db->FetchMode('ASSOC'); $db->Execute('INSERT INTO t_test2 VALUES(1, 100)'); for ($i = 1; $i <= 3; ++$i) { $pid = pcntl_fork(); if (!$pid) { switch ($i) { case 1: echo "In process: {$i}", 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;
$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'); /** * this can be called on a cron or manually */ foreach ($db->readQueue() as $key => $value) { $queue = $value['queue']; $data = base64_decode($value['data']); $cdate = $value['cdate'];
# ------------------------------------------------------------------------------ $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); } elseif ($rs == 0) { echo "{$rs} = 0" . $timer->isCli(1); } else { echo "{$rs} > 0" . $timer->isCli(1); }
<?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();