public function logout()
{
$adminId = $this->app->getSession()->get('adminId');
$sql = "delete from `adminsession` where `admin_id` = '" . $adminId . "'";
R::exec($sql);
$this->app->getSession()->remove('adminId');
}
/**
* Test parameter binding.
*
* @return void
*/
public function testParamBindingWithPostgres()
{
testpack("param binding pgsql");
$page = R::dispense("page");
$page->name = "abc";
$page->number = 2;
R::store($page);
R::exec("insert into page (name) values(:name) ", array(":name" => "my name"));
R::exec("insert into page (number) values(:one) ", array(":one" => 1));
R::exec("insert into page (number) values(:one) ", array(":one" => "1"));
R::exec("insert into page (number) values(:one) ", array(":one" => "1234"));
R::exec("insert into page (number) values(:one) ", array(":one" => "-21"));
pass();
testpack('Test whether we can properly bind and receive NULL values');
$adapter = R::getDatabaseAdapter();
asrt($adapter->getCell('SELECT TEXT( :nil ) ', array(':nil' => 'NULL')), 'NULL');
asrt($adapter->getCell('SELECT TEXT( :nil ) ', array(':nil' => NULL)), NULL);
asrt($adapter->getCell('SELECT TEXT( ? ) ', array('NULL')), 'NULL');
asrt($adapter->getCell('SELECT TEXT( ? ) ', array(NULL)), NULL);
}
/**
* All kinds of tests for basic CRUD.
*
* Does the data survive?
*
* @return void
*/
public function testUpdatingBeans()
{
testpack('Test basic support UUID/override ID default value');
$bean = R::dispense('bean');
R::store($bean);
if ($this->currentlyActiveDriverID === 'mysql') {
//otherwise UTF8 causes index overflow in mysql: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
R::exec('alter table bean modify column id char(3);');
} else {
R::getWriter()->widenColumn('bean', 'id', R::getWriter()->scanType('abc'));
}
$bean->id = 'abc';
R::store($bean);
asrt($bean->id, 'abc');
testpack('Test Update');
try {
R::store(array());
fail();
} catch (RedException $e) {
pass();
}
$toolbox = R::getToolBox();
$adapter = $toolbox->getDatabaseAdapter();
$writer = $toolbox->getWriter();
$redbean = $toolbox->getRedBean();
$pdo = $adapter->getDatabase();
$page = $redbean->dispense("page");
$page->name = "old name";
$id = $redbean->store($page);
asrt($page->getMeta('tainted'), FALSE);
$page->setAttr('name', "new name");
asrt($page->getMeta('tainted'), TRUE);
$id = $redbean->store($page);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
// Null should == NULL after saving
$page->rating = NULL;
$newid = $redbean->store($page);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
asrt($page->rating === NULL, TRUE);
asrt(!$page->rating, TRUE);
$page->rating = FALSE;
$newid = $redbean->store($page);
asrt($newid, $id);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
asrt((bool) $page->rating, FALSE);
asrt($page->rating == FALSE, TRUE);
asrt(!$page->rating, TRUE);
$page->rating = TRUE;
$newid = $redbean->store($page);
asrt($newid, $id);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
asrt((bool) $page->rating, TRUE);
asrt($page->rating == TRUE, TRUE);
asrt($page->rating == TRUE, TRUE);
$page->rating = NULL;
R::store($page);
$page = R::load('page', $page->id);
asrt($page->rating, NULL);
$page->rating = "1";
$newid = $redbean->store($page);
asrt($newid, $id);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
asrt($page->rating, "1");
$page->rating = "0";
$newid = $redbean->store($page);
asrt($page->rating, "0");
$page->rating = 0;
$newid = $redbean->store($page);
asrt($page->rating, 0);
$page->rating = "0";
$newid = $redbean->store($page);
asrt($newid, $id);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
asrt(!$page->rating, TRUE);
asrt($page->rating == 0, TRUE);
asrt($page->rating == FALSE, TRUE);
$page->rating = 5;
$newid = $redbean->store($page);
asrt($newid, $id);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
asrt(strval($page->rating), "5");
$page->rating = 300;
$newid = $redbean->store($page);
asrt($newid, $id);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
asrt(strval($page->rating), "300");
$page->rating = -2;
$newid = $redbean->store($page);
asrt($newid, $id);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
asrt(strval($page->rating), "-2");
$page->rating = 2.5;
$newid = $redbean->store($page);
asrt($newid, $id);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
asrt($page->rating == 2.5, TRUE);
$page->rating = -3.3;
$newid = $redbean->store($page);
asrt($newid, $id);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
asrt($page->rating == -3.3, TRUE);
$page->rating = "good";
$newid = $redbean->store($page);
asrt($newid, $id);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
asrt($page->rating, "good");
$longtext = str_repeat('great! because..', 100);
$page->rating = $longtext;
$newid = $redbean->store($page);
asrt($newid, $id);
$page = $redbean->load("page", $id);
asrt($page->name, "new name");
asrt($page->rating, $longtext);
// Test leading zeros
$numAsString = "0001";
$page->numasstring = $numAsString;
$redbean->store($page);
$page = $redbean->load("page", $id);
asrt($page->numasstring, "0001");
$page->numnotstring = "0.123";
$redbean->store($page);
$page = $redbean->load("page", $id);
asrt($page->numnotstring == 0.123, TRUE);
$page->numasstring2 = "00.123";
$redbean->store($page);
$page = $redbean->load("page", $id);
asrt($page->numasstring2, "00.123");
try {
$redbean->trash(array());
fail();
} catch (RedException $e) {
pass();
}
$redbean->trash($page);
asrt((int) $pdo->GetCell("SELECT count(*) FROM page"), 0);
}
/**
* Test BIG INT primary key support.
*
* @return void
*/
public function testBigIntSupport()
{
R::nuke();
$createPageTableSQL = '
CREATE TABLE
`page`
(
id BIGINT(20) UNSIGNED NOT NULL,
book_id BIGINT(20) UNSIGNED NOT NULL,
magazine_id BIGINT(20) UNSIGNED NOT NULL,
title VARCHAR(255),
PRIMARY KEY ( id )
)
ENGINE = InnoDB DEFAULT
CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci
AUTO_INCREMENT = 1223372036854775808';
$createBookTableSQL = '
CREATE TABLE
`book`
(
id BIGINT(20) UNSIGNED NOT NULL,
title VARCHAR(255),
PRIMARY KEY ( id )
)
ENGINE = InnoDB DEFAULT
CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci
AUTO_INCREMENT = 2223372036854775808';
$createPagePageTableSQL = '
CREATE TABLE
`page_page`
(
id BIGINT(20) UNSIGNED NOT NULL,
page_id BIGINT(20) UNSIGNED NOT NULL,
page2_id BIGINT(20) UNSIGNED NOT NULL,
PRIMARY KEY ( id )
)
ENGINE = InnoDB DEFAULT
CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci
AUTO_INCREMENT = 3223372036854775808';
R::exec($createBookTableSQL);
R::exec($createPageTableSQL);
R::exec($createPagePageTableSQL);
//insert some records
$book1ID = '2223372036854775808';
$book2ID = '2223372036854775809';
$page1ID = '1223372036854775808';
$page2ID = '1223372036854775809';
$page3ID = '1223372036854775890';
$pagePage1ID = '3223372036854775808';
$insertBook1SQL = "\n\t\t\tINSERT INTO book (id, title) VALUES( '{$book1ID}', 'book 1' );\n\t\t";
$insertBook2SQL = "\n\t\t\tINSERT INTO book (id, title) VALUES( '{$book2ID}', 'book 2' );\n\t\t";
$insertPage1SQL = "\n\t\t\tINSERT INTO page (id, book_id, title, magazine_id) VALUES( '{$page1ID}', '{$book1ID}', 'page 1 of book 1', '{$book2ID}' );\n\t\t";
$insertPage2SQL = "\n\t\t\tINSERT INTO page (id, book_id, title) VALUES( '{$page2ID}', '{$book1ID}', 'page 2 of book 1' );\n\t\t";
$insertPage3SQL = "\n\t\t\tINSERT INTO page (id, book_id, title) VALUES( '{$page3ID}', '{$book2ID}', 'page 1 of book 2' );\n\t\t";
$insertPagePage1SQL = "\n\t\t\tINSERT INTO page_page (id, page_id, page2_id) VALUES( '{$pagePage1ID}', '{$page2ID}', '{$page3ID}' );\n\t\t";
R::exec($insertBook1SQL);
R::exec($insertBook2SQL);
R::exec($insertPage1SQL);
R::exec($insertPage2SQL);
R::exec($insertPage3SQL);
R::exec($insertPagePage1SQL);
//basic tour of basic functions....
$book1 = R::load('book', $book1ID);
asrt($book1->id, $book1ID);
asrt($book1->title, 'book 1');
$book2 = R::load('book', $book2ID);
asrt($book2->id, $book2ID);
asrt($book2->title, 'book 2');
asrt(count($book1->ownPage), 2);
asrt(count($book1->fresh()->with('LIMIT 1')->ownPage), 1);
asrt(count($book1->fresh()->withCondition(' title = ? ', array('page 2 of book 1'))->ownPage), 1);
asrt(count($book2->ownPage), 1);
asrt($book2->fresh()->countOwn('page'), 1);
$page1 = R::load('page', $page1ID);
asrt(count($page1->sharedPage), 0);
asrt($page1->fetchAs('book')->magazine->id, $book2ID);
$page2 = R::load('page', $page2ID);
asrt(count($page2->sharedPage), 1);
asrt($page2->fresh()->countShared('page'), 1);
$page3 = R::findOne('page', ' title = ? ', array('page 1 of book 2'));
asrt($page3->id, $page3ID);
asrt($page3->book->id, $book2ID);
}
/**
* Test Read-support.
*
* @return void
*/
public function testUUIDReadSupport()
{
R::nuke();
$createPageTableSQL = '
CREATE TABLE
page
(
id UUID PRIMARY KEY,
book_id UUID,
magazine_id UUID,
title VARCHAR(255)
)';
$createBookTableSQL = '
CREATE TABLE
book
(
id UUID PRIMARY KEY,
title VARCHAR(255)
)';
$createPagePageTableSQL = '
CREATE TABLE
page_page
(
id UUID PRIMARY KEY,
page_id UUID,
page2_id UUID
)';
R::exec($createBookTableSQL);
R::exec($createPageTableSQL);
R::exec($createPagePageTableSQL);
//insert some records
$book1ID = '6ccd780c-baba-1026-9564-0040f4311e21';
$book2ID = '6ccd780c-baba-1026-9564-0040f4311e22';
$page1ID = '6ccd780c-baba-1026-9564-0040f4311e23';
$page2ID = '6ccd780c-baba-1026-9564-0040f4311e24';
$page3ID = '6ccd780c-baba-1026-9564-0040f4311e25';
$pagePage1ID = '6ccd780c-baba-1026-9564-0040f4311e26';
$insertBook1SQL = "\n\t\t\tINSERT INTO book (id, title) VALUES( '{$book1ID}', 'book 1' );\n\t\t";
$insertBook2SQL = "\n\t\t\tINSERT INTO book (id, title) VALUES( '{$book2ID}', 'book 2' );\n\t\t";
$insertPage1SQL = "\n\t\t\tINSERT INTO page (id, book_id, title, magazine_id) VALUES( '{$page1ID}', '{$book1ID}', 'page 1 of book 1', '{$book2ID}' );\n\t\t";
$insertPage2SQL = "\n\t\t\tINSERT INTO page (id, book_id, title) VALUES( '{$page2ID}', '{$book1ID}', 'page 2 of book 1' );\n\t\t";
$insertPage3SQL = "\n\t\t\tINSERT INTO page (id, book_id, title) VALUES( '{$page3ID}', '{$book2ID}', 'page 1 of book 2' );\n\t\t";
$insertPagePage1SQL = "\n\t\t\tINSERT INTO page_page (id, page_id, page2_id) VALUES( '{$pagePage1ID}', '{$page2ID}', '{$page3ID}' );\n\t\t";
R::exec($insertBook1SQL);
R::exec($insertBook2SQL);
R::exec($insertPage1SQL);
R::exec($insertPage2SQL);
R::exec($insertPage3SQL);
R::exec($insertPagePage1SQL);
//basic tour of basic functions....
$book1 = R::load('book', $book1ID);
asrt($book1->id, $book1ID);
asrt($book1->title, 'book 1');
$book2 = R::load('book', $book2ID);
asrt($book2->id, $book2ID);
asrt($book2->title, 'book 2');
asrt(count($book1->ownPage), 2);
asrt(count($book1->fresh()->with('LIMIT 1')->ownPage), 1);
asrt(count($book1->fresh()->withCondition(' title = ? ', array('page 2 of book 1'))->ownPage), 1);
asrt(count($book2->ownPage), 1);
asrt($book2->fresh()->countOwn('page'), 1);
$page1 = R::load('page', $page1ID);
asrt(count($page1->sharedPage), 0);
asrt($page1->fetchAs('book')->magazine->id, $book2ID);
$page2 = R::load('page', $page2ID);
asrt(count($page2->sharedPage), 1);
asrt($page2->fresh()->countShared('page'), 1);
$page3 = R::findOne('page', ' title = ? ', array('page 1 of book 2'));
asrt($page3->id, $page3ID);
asrt($page3->book->id, $book2ID);
}
/**
* Test whether we can manually create indexes.
*
* @return void
*/
public function testAddingIndex()
{
R::nuke();
$sql = 'CREATE TABLE song (
id INTEGER PRIMARY KEY AUTOINCREMENT,
album_id INTEGER,
category TEXT
) ';
R::exec($sql);
$writer = R::getWriter();
$indexes = R::getAll('PRAGMA index_list("song") ');
asrt(count($indexes), 0);
$writer->addIndex('song', 'index1', 'album_id');
$indexes = R::getAll('PRAGMA index_list("song") ');
asrt(count($indexes), 1);
$writer->addIndex('song', 'index1', 'album_id');
$indexes = R::getAll('PRAGMA index_list("song") ');
asrt(count($indexes), 1);
$writer->addIndex('song', 'index2', 'category');
$indexes = R::getAll('PRAGMA index_list("song") ');
asrt(count($indexes), 2);
try {
$writer->addIndex('song', 'index1', 'nonexistant');
pass();
} catch (\Exception $ex) {
fail();
}
$indexes = R::getAll('PRAGMA index_list("song") ');
asrt(count($indexes), 2);
try {
$writer->addIndex('nonexistant', 'index1', 'nonexistant');
pass();
} catch (\Exception $ex) {
fail();
}
$indexes = R::getAll('PRAGMA index_list("song") ');
asrt(count($indexes), 2);
}
/**
* Test Facade transactions.
*
* @return void
*
* @throws\Exception
*/
public function testTransactionInFacade()
{
testpack('Test transaction in facade');
$bean = R::dispense('bean');
$bean->name = 'a';
R::store($bean);
R::trash($bean);
R::freeze(TRUE);
$bean = R::dispense('bean');
$bean->name = 'a';
R::store($bean);
asrt(R::count('bean'), 1);
R::trash($bean);
asrt(R::count('bean'), 0);
$bean = R::dispense('bean');
$bean->name = 'a';
$id = R::transaction(function () use(&$bean) {
return R::transaction(function () use(&$bean) {
return R::store($bean);
});
});
asrt((int) $id, (int) $bean->id);
R::trash($bean);
$bean = R::dispense('bean');
$bean->name = 'a';
$id = R::transaction(function () use(&$bean) {
return R::store($bean);
});
asrt((int) $id, (int) $bean->id);
R::trash($bean);
$bean = R::dispense('bean');
$bean->name = 'a';
try {
R::transaction(function () use($bean) {
R::store($bean);
R::transaction(function () {
throw new \Exception();
});
});
} catch (\Exception $e) {
pass();
}
asrt(R::count('bean'), 0);
$bean = R::dispense('bean');
$bean->name = 'a';
try {
R::transaction(function () use($bean) {
R::transaction(function () use($bean) {
R::store($bean);
throw new \Exception();
});
});
} catch (\Exception $e) {
pass();
}
asrt(R::count('bean'), 0);
$bean = R::dispense('bean');
$bean->name = 'a';
try {
R::transaction(function () use($bean) {
R::transaction(function () use($bean) {
R::store($bean);
});
});
} catch (\Exception $e) {
pass();
}
asrt(R::count('bean'), 1);
R::freeze(FALSE);
try {
R::transaction('nope');
fail();
} catch (\Exception $e) {
pass();
}
testpack('Test Camelcase 2 underscore');
$names = array('oneACLRoute' => 'one_acl_route', 'ALLUPPERCASE' => 'alluppercase', 'clientServerArchitecture' => 'client_server_architecture', 'camelCase' => 'camel_case', 'peer2peer' => 'peer2peer', 'fromUs4You' => 'from_us4_you', 'lowercase' => 'lowercase', 'a1A2b' => 'a1a2b');
$bean = R::dispense('bean');
foreach ($names as $name => $becomes) {
$bean->{$name} = 1;
asrt(isset($bean->{$becomes}), TRUE);
}
testpack('Misc Tests');
R::debug(1);
flush();
ob_start();
R::exec('SELECT 123');
$out = ob_get_contents();
ob_end_clean();
flush();
pass();
asrt(strpos($out, 'SELECT 123') !== FALSE, TRUE);
R::debug(0);
flush();
ob_start();
R::exec('SELECT 123');
$out = ob_get_contents();
ob_end_clean();
flush();
pass();
asrt($out, '');
R::debug(0);
pass();
testpack('test to string override');
$band = R::dispense('band');
$str = strval($band);
asrt($str, 'bigband');
testpack('test whether we can use isset/set in model');
$band->setProperty('property1', 123);
asrt($band->property1, 123);
asrt($band->checkProperty('property1'), TRUE);
asrt($band->checkProperty('property2'), FALSE);
$band = new \Model_Band();
$bean = R::dispense('band');
$bean->property3 = 123;
$band->loadBean($bean);
$bean->property4 = 345;
$band->setProperty('property1', 123);
asrt($band->property1, 123);
asrt($band->checkProperty('property1'), TRUE);
asrt($band->checkProperty('property2'), FALSE);
asrt($band->property3, 123);
asrt($band->property4, 345);
testpack('Can we pass a\\PDO object to Setup?');
$pdo = new \PDO('sqlite:test.db');
R::addDatabase('pdo', $pdo);
R::selectDatabase('pdo');
R::getCell('SELECT 123;');
testpack('Test array interface of beans');
$bean = R::dispense('bean');
$bean->hello = 'hi';
$bean->world = 'planet';
asrt($bean['hello'], 'hi');
asrt(isset($bean['hello']), TRUE);
asrt(isset($bean['bye']), FALSE);
$bean['world'] = 'sphere';
asrt($bean->world, 'sphere');
foreach ($bean as $key => $el) {
if ($el == 'sphere' || $el == 'hi' || $el == 0) {
pass();
} else {
fail();
}
if ($key == 'hello' || $key == 'world' || $key == 'id') {
pass();
} else {
fail();
}
}
asrt(count($bean), 3);
unset($bean['hello']);
asrt(count($bean), 2);
asrt(count(R::dispense('countable')), 1);
// Otherwise untestable...
$bean->setBeanHelper(new SimpleFacadeBeanHelper());
R::getRedBean()->setBeanHelper(new SimpleFacadeBeanHelper());
pass();
// Test whether properties like owner and shareditem are still possible
testpack('Test Bean Interface for Lists');
$bean = R::dispense('bean');
// Must not be list, because first char after own is lowercase
asrt(is_array($bean->owner), FALSE);
// Must not be list, because first char after shared is lowercase
asrt(is_array($bean->shareditem), FALSE);
asrt(is_array($bean->own), FALSE);
asrt(is_array($bean->shared), FALSE);
asrt(is_array($bean->own_item), FALSE);
asrt(is_array($bean->shared_item), FALSE);
asrt(is_array($bean->{'own item'}), FALSE);
asrt(is_array($bean->{'shared Item'}), FALSE);
}
/**
*
* Same as above.. test keep cache.
*
* @return void
*/
public function testInstructNoDrop()
{
$str = 'SELECT * FROM ' . R::getWriter()->esc('bean', TRUE) . ' -- keep-cache';
$bean = R::dispense('bean');
$bean->title = 'abc';
$id = R::store($bean);
$bean = R::load('bean', $id);
$bean->title = 'xxx';
R::store($bean);
R::exec($str);
$bean = R::load('bean', $id);
asrt($bean->title, 'abc');
R::nuke();
// Now INSTRUCT the cache to not drop the cache CASE 2
$str = 'SELECT * FROM ' . R::getWriter()->esc('bean', TRUE) . ' -- keep-cache';
$bean = R::dispense('bean');
$bean->title = 'abc';
$id = R::store($bean);
$bean = R::load('bean', $id);
$bean->title = 'xxx';
R::store($bean);
R::findOne('bean', ' title = ? ', array('cache'));
$bean = R::load('bean', $id);
asrt($bean->title, 'xxx');
}
/**
* Test filtering relations on links (using columns in the link table).
*
* @return void
*/
public function testSharedLinkCond()
{
testpack('Test new shared relations with link conditions');
$w = R::getWriter();
list($b1, $b2) = R::dispense('book', 2);
$b1->name = 'book1';
$b2->name = 'book2';
list($p1, $p2, $p3) = R::dispense('page', 3);
$p1->text = 'page1';
$p1->number = 3;
$p2->text = 'page2';
$p3->text = 'page3';
$b1->link('book_page', array('order' => 1))->page = $p1;
$b1->link('bookPage', array('order' => 2))->page = $p2;
$b2->link('book_page', array('order' => 1))->page = $p3;
$b2->link('bookPage', array('order' => 2))->page = $p2;
$b2->link('book_page', array('order' => 3))->page = $p1;
R::storeAll(array($b1, $b2));
$b1 = R::load('book', $b1->id);
$b2 = R::load('book', $b2->id);
$pages = $b1->withCondition(' book_page.' . $w->esc('order') . ' = 2 ')->sharedPage;
$page = reset($pages);
asrt($page->text, 'page2');
$pages = $b2->withCondition(' ' . $w->esc('order') . ' = 3 ')->sharedPage;
$page = reset($pages);
asrt($page->text, 'page1');
$b1 = R::load('book', $b1->id);
$b2 = R::load('book', $b2->id);
$pages = $b1->withCondition(' book_page.' . $w->esc('order') . ' < 3 AND page.number = 3')->sharedPage;
$page = reset($pages);
asrt($page->text, 'page1');
$pages = $b2->withCondition(' ' . $w->esc('order') . ' > 1 ORDER BY book_page.' . $w->esc('order') . ' ASC ')->sharedPage;
$page = array_shift($pages);
asrt($page->text, 'page2');
$page = array_shift($pages);
asrt($page->text, 'page1');
testpack('Test new shared relations and cache');
/**
* why does this not destroy cache in psql?
* ah: An error occurred: SQLSTATE[42703]: Undefined column: 7
* ERROR: column "page" of relation "page" does not exist
*/
R::exec('UPDATE page SET ' . $w->esc('number') . ' = 1 ');
R::getWriter()->setUseCache(TRUE);
$p1 = R::load('page', (int) $p1->id);
// Someone else changes the records. Cache remains.
R::exec(' UPDATE page SET ' . $w->esc('number') . ' = 9 -- keep-cache');
$b1 = R::load('book', $b1->id);
$p1 = R::load('page', (int) $p1->id);
// Yupz a stale cache, phantom read!
asrt((int) $p1->number, 1);
$pages = $b1->withCondition(' book_page.' . $w->esc('order') . ' = 1 ')->sharedPage;
$page = reset($pages);
// Inconsistent, sad but TRUE, different query -> cache key is different
asrt((int) $page->number, 9);
// However, cache must have been invalidated by this query
$p1 = R::load('page', (int) $p1->id);
// Yes! we're consistent again! -- as if the change just happened later!
asrt((int) $page->number, 9);
// By doing this we keep getting 9 instead of 8
$b1->fresh()->withCondition(' book_page.' . $w->esc('order') . ' = 1 ')->sharedPage;
// Someone else is busy again...
R::exec(' UPDATE page SET ' . $w->esc('number') . ' = 8 -- keep-cache');
$b1 = R::load('book', $b1->id);
$pages = $b1->withCondition(' book_page.' . $w->esc('order') . ' = 1 ')->sharedPage;
$page = reset($pages);
/**
* yes! we get 9 instead of 8, why because the cache key has not changed,
* our last query was PAGE-BOOK-RELATION and now we ask for
* PAGE-BOOK-RELATION again. if we would have used just a load page
* query we would have gotten the new value (8).... let's test that!
*/
asrt((int) $page->number, 9);
R::exec(' UPDATE page SET ' . $w->esc('number') . ' = 9');
$p1 = R::load('page', (int) $p1->id);
asrt((int) $page->number, 9);
// Someone else is busy again...
R::exec(' UPDATE page SET ' . $w->esc('number') . ' = 8 -- keep-cache');
$b1 = R::load('book', $b1->id);
$pages = $b1->withCondition(' book_page.' . $w->esc('order') . ' = 1 ')->sharedPage;
$page = reset($pages);
// Yes, keep-cache wont help, cache key changed!
asrt((int) $page->number, 8);
R::getWriter()->setUseCache(FALSE);
}
public function reorderServerSide()
{
if ($this->app->isAjax()) {
$oldPosition = $_POST['oldPosition'];
$newPosition = $_POST['newPosition'];
$id = $_POST['id'];
if ($oldPosition > $newPosition) {
R::exec("UPDATE {$this->getTableName()} SET {$this->getPositionFieldName()} = ({$this->getPositionFieldName()} + 1) WHERE {$this->getPositionFieldName()} < {$oldPosition} AND {$this->getPositionFieldName()} >= {$newPosition}");
} else {
if ($oldPosition < $newPosition) {
R::exec("UPDATE {$this->getTableName()} SET {$this->getPositionFieldName()} = ({$this->getPositionFieldName()} - 1) WHERE {$this->getPositionFieldName()} > {$oldPosition} AND {$this->getPositionFieldName()} <= {$newPosition}");
}
}
R::exec("UPDATE {$this->getTableName()} SET {$this->getPositionFieldName()} = {$newPosition} WHERE id = {$id}");
}
}
/**
* Test whether we can manually create indexes.
*
* @return void
*/
public function testAddingIndex()
{
R::nuke();
$sql = 'CREATE TABLE song (
id SERIAL PRIMARY KEY,
album_id INTEGER,
category VARCHAR(255)
)';
R::exec($sql);
$indexes = R::getAll(" SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
asrt(count($indexes), 1);
$writer = R::getWriter();
$writer->addIndex('song', 'index1', 'album_id');
$indexes = R::getAll(" SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
asrt(count($indexes), 2);
//Cant add the same index twice
$writer->addIndex('song', 'index1', 'album_id');
$indexes = R::getAll(" SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
asrt(count($indexes), 2);
$writer->addIndex('song', 'index2', 'category');
$indexes = R::getAll(" SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
asrt(count($indexes), 3);
//Dont fail, just dont
try {
$writer->addIndex('song', 'index3', 'nonexistant');
pass();
} catch (\Exception $e) {
fail();
}
$indexes = R::getAll(" SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
asrt(count($indexes), 3);
try {
$writer->addIndex('nonexistant', 'index4', 'nonexistant');
pass();
} catch (\Exception $e) {
fail();
}
$indexes = R::getAll(" SELECT * FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'song' ");
asrt(count($indexes), 3);
}
CREATE table `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar (300) NOT NULL,
`user_pass` varchar (300) NOT NULL,
`email` varchar (300) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
INSERT INTO `users` VALUES (1, 'admin', "\$2\\y\$10\$xdB/UcprN3.7g.K7F.dKFOTsPGg/vVYQYh7OcL.k0.te7x8h8rKEG", '*****@*****.**');
CREATE table `settings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`site_name` varchar (300) NOT NULL,
`site_resultsnumber` int (11) NOT NULL,
`site_cookies` int (11) NOT NULL,
`site_cache` int (11) NOT NULL,
`site_maxanswers` int (11) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
INSERT INTO `settings` VALUES (1, 'MyPoll System V 0.1', '10', '0', '0', '4');
STR;
try {
\RedBeanPHP\Facade::exec($aquery);
echo 'Database Created';
} catch (Exception $e) {
echo 'Error : ' . $e->getMessage();
}
public function reorder()
{
// Reorder positions first to fix broken data
R::exec("SET @ordering = 0;");
R::exec("UPDATE {$this->model()} SET\n\t\t position = (@ordering := @ordering + 1)\n\t\t ORDER BY {$this->dragField}, id ASC");
$toPosition = $this->params['toPosition'];
$fromPosition = $this->params['fromPosition'];
$direction = $this->params['direction'];
$id = $this->params['id'];
/** @var \RedBeanPHP\SimpleModel $instanceBean */
$instanceBean = R::findOne($this->model(), 'id = ?', array($id));
if (!empty($instanceBean)) {
/** @var \Skully\App\Models\BaseModel $instance */
$instance = $instanceBean->box();
if ($direction == 'back') {
// Adds all rows after this one's final position by 1
$sql = "UPDATE `{$this->model()}`\n \t\t\t SET `{$this->dragField}` = `{$this->dragField}` + 1\n \t\t\t WHERE `{$this->dragField}` >= '" . $toPosition . "'\n \t\t\t AND `{$this->dragField}` < '{$fromPosition}'";
} else {
// Reduce all rows before this one's final position by 1
$sql = "UPDATE `{$this->model()}`\n \t\t\t SET `{$this->dragField}` = `{$this->dragField}` - 1\n \t\t\t WHERE `{$this->dragField}` > '" . $fromPosition . "'\n \t\t\t AND `{$this->dragField}` <= '{$toPosition}'";
}
R::exec($sql);
$instance->set($this->dragField, $toPosition);
try {
R::store($instance);
} catch (\Exception $e) {
$message = $e->getMessage();
if (!empty($message)) {
$instance->addError($message);
}
$this->app->getLogger()->log("error happened when updating from CRUDController (model " . $this->model() . "): " . $message);
$this->app->getLogger()->debugBacktrace();
$this->app->getLogger()->log("params are: " . print_r($this->getParams(), true));
}
}
}
<?php
ini_set('max_execution_time', 0);
require 'vendor/autoload.php';
require_once 'vendor/fzaninotto/faker/src/autoload.php';
$faker = Faker\Factory::create('fr_FR');
use RedBeanPHP\Facade as R;
R::setup('mysql:host=localhost;dbname=gsb_cost_managment', 'root', 'pwsio');
R::exec('CREATE VIEW visitor AS SELECT * FROM gsb_human_ressources.employee WHERE employee.job_id=3');
$status = R::dispense('status');
$status->libelle = 'Créée';
R::store($status);
$status = R::dispense('status');
$status->libelle = 'Clôturée';
R::store($status);
$status = R::dispense('status');
$status->libelle = 'Validée';
R::store($status);
$status = R::dispense('status');
$status->libelle = 'Mise en paiement';
R::store($status);
$status = R::dispense('status');
$status->libelle = 'Remboursée';
R::store($status);
for ($i = 0; $i < 12960; $i++) {
$cost_sheet = R::dispense('costsheet');
$cost_sheet->month = $faker->month;
$cost_sheet->visitor = R::findOne('visitor', 'id=?', [$faker->numberBetween($min = 1, $max = 540)]);
$cost_sheet->status = R::findOne('status', 'id=?', [$faker->numberBetween($min = 1, $max = 5)]);
$cost_sheet->justification_number = $faker->randomDigitNotNull;
$cost_sheet->valid_amount = $faker->randomNumber($nbDigits = 3);
public function clearlogbydate($startDate, $endDate, $userid)
{
try {
$buser = (object) R::getRow('select * from users where user_name = ? ', array($userid));
$sql = "DELETE FROM `systemlog` WHERE DATE_FORMAT(create_date,'%Y-%m-%d') BETWEEN '{$startDate}' and '{$endDate}'";
$item = new Systemlog();
$item->query = $sql;
$item->userid = $userid;
$item->tbname = $this->tbname;
if ($buser->groups_id == 1 && $buser->user_level > 5) {
$rows = R::exec($sql);
$item->parametor = "{$startDate}/{$endDate}/numrows = " . $rows;
$item->logs = 'Clear Log between ' . $startDate . ' to ' . $endDate;
$item->types = 'CLEAR LOGS';
$this->insertlog($item, $userid);
} else {
$item->parametor = "{$startDate}/{$endDate}";
$item->logs = 'Warning::Clear Log between ' . $startDate . ' to ' . $endDate;
$item->types = 'Warning::CLEAR LOGS';
$this->insertlog($item, $userid);
throw new Exception('คุณไม่มีสิทธิ์ ในการ ลย');
}
} catch (Exception $e) {
throw new Exception($e->getMessage());
}
}
/**
* Test whether we can manually create indexes.
*
* @return void
*/
public function testAddingIndex()
{
R::nuke();
$sql = '
CREATE TABLE song (
id INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
album_id INT( 11 ) UNSIGNED NOT NULL,
category VARCHAR( 255 ),
PRIMARY KEY ( id )
)
ENGINE = InnoDB
';
R::exec($sql);
$sql = 'SHOW INDEX FROM song';
$indexes = R::getAll($sql);
asrt(count($indexes), 1);
asrt($indexes[0]['Table'], 'song');
asrt($indexes[0]['Key_name'], 'PRIMARY');
$writer = R::getWriter();
$writer->addIndex('song', 'index1', 'album_id');
$indexes = R::getAll('SHOW INDEX FROM song');
asrt(count($indexes), 2);
asrt($indexes[0]['Table'], 'song');
asrt($indexes[0]['Key_name'], 'PRIMARY');
asrt($indexes[1]['Table'], 'song');
asrt($indexes[1]['Key_name'], 'index1');
//Cant add the same index twice
$writer->addIndex('song', 'index2', 'category');
$indexes = R::getAll('SHOW INDEX FROM song');
asrt(count($indexes), 3);
//Dont fail, just dont
try {
$writer->addIndex('song', 'index3', 'nonexistant');
pass();
} catch (\Exception $e) {
fail();
}
asrt(count($indexes), 3);
try {
$writer->addIndex('nonexistant', 'index4', 'nonexistant');
pass();
} catch (\Exception $e) {
fail();
}
asrt(count($indexes), 3);
try {
$writer->addIndex('nonexistant', '', 'nonexistant');
pass();
} catch (\Exception $e) {
fail();
}
asrt(count($indexes), 3);
}
/**
* Test parameter binding with\PDO.
*
* @return void
*/
public function testPDOParameterBinding()
{
$toolbox = R::getToolBox();
$adapter = $toolbox->getDatabaseAdapter();
$writer = $toolbox->getWriter();
$redbean = $toolbox->getRedBean();
$pdo = $adapter->getDatabase();
R::getDatabaseAdapter()->getDatabase()->setUseStringOnlyBinding(TRUE);
try {
R::getAll("select * from job limit ? ", array(1));
fail();
} catch (\Exception $e) {
pass();
}
try {
R::getAll("select * from job limit :l ", array(":l" => 1));
fail();
} catch (\Exception $e) {
pass();
}
try {
R::exec("select * from job limit ? ", array(1));
fail();
} catch (\Exception $e) {
pass();
}
try {
R::exec("select * from job limit :l ", array(":l" => 1));
fail();
} catch (\Exception $e) {
pass();
}
R::getDatabaseAdapter()->getDatabase()->setUseStringOnlyBinding(FALSE);
try {
R::getAll("select * from job limit ? ", array(1));
pass();
} catch (\Exception $e) {
print_r($e);
fail();
}
try {
R::getAll("select * from job limit :l ", array(":l" => 1));
pass();
} catch (\Exception $e) {
fail();
}
try {
R::exec("select * from job limit ? ", array(1));
pass();
} catch (\Exception $e) {
fail();
}
try {
R::exec("select * from job limit :l ", array(":l" => 1));
pass();
} catch (\Exception $e) {
fail();
}
testpack("Test findOrDispense");
$person = R::findOrDispense("person", " job = ? ", array("developer"));
asrt(count($person) > 0, TRUE);
$person = R::findOrDispense("person", " job = ? ", array("musician"));
asrt(count($person) > 0, TRUE);
$musician = array_pop($person);
asrt(intval($musician->id), 0);
try {
$adapter->exec("an invalid query");
fail();
} catch (SQL $e) {
pass();
}
asrt((int) $adapter->getCell("SELECT 123"), 123);
asrt((int) $adapter->getCell("SELECT ?", array("987")), 987);
asrt((int) $adapter->getCell("SELECT ?+?", array("987", "2")), 989);
asrt((int) $adapter->getCell("SELECT :numberOne+:numberTwo", array(":numberOne" => 42, ":numberTwo" => 50)), 92);
$pair = $adapter->getAssoc("SELECT 'thekey','thevalue' ");
asrt(is_array($pair), TRUE);
asrt(count($pair), 1);
asrt(isset($pair["thekey"]), TRUE);
asrt($pair["thekey"], "thevalue");
testpack('Test whether we can properly bind and receive NULL values');
asrt($adapter->getCell('SELECT :nil ', array(':nil' => 'NULL')), 'NULL');
asrt($adapter->getCell('SELECT :nil ', array(':nil' => NULL)), NULL);
asrt($adapter->getCell('SELECT ? ', array('NULL')), 'NULL');
asrt($adapter->getCell('SELECT ? ', array(NULL)), NULL);
}
/**
* Test BIG INT primary key support.
*
* @return void
*/
public function testBigIntSupport()
{
R::nuke();
$createPageTableSQL = '
CREATE TABLE
page
(
id BIGSERIAL PRIMARY KEY,
book_id BIGSERIAL,
magazine_id BIGSERIAL,
title VARCHAR(255)
)';
$createBookTableSQL = '
CREATE TABLE
book
(
id BIGSERIAL PRIMARY KEY,
title VARCHAR(255)
)';
$createPagePageTableSQL = '
CREATE TABLE
page_page
(
id BIGSERIAL PRIMARY KEY,
page_id BIGSERIAL,
page2_id BIGSERIAL
) ';
R::exec($createBookTableSQL);
R::exec($createPageTableSQL);
R::exec($createPagePageTableSQL);
//insert some records
$book1ID = '2223372036854775808';
$book2ID = '2223372036854775809';
$page1ID = '1223372036854775808';
$page2ID = '1223372036854775809';
$page3ID = '1223372036854775890';
$pagePage1ID = '3223372036854775808';
R::exec("ALTER SEQUENCE book_id_seq RESTART WITH {$book1ID}");
R::exec("ALTER SEQUENCE page_id_seq RESTART WITH {$page1ID}");
R::exec("ALTER SEQUENCE page_page_id_seq RESTART WITH {$pagePage1ID}");
$insertBook1SQL = "\n\t\t\tINSERT INTO book (title) VALUES( 'book 1' );\n\t\t";
$insertBook2SQL = "\n\t\t\tINSERT INTO book (title) VALUES( 'book 2' );\n\t\t";
$insertPage1SQL = "\n\t\t\tINSERT INTO page (id, book_id, title, magazine_id) VALUES( '{$page1ID}', '{$book1ID}', 'page 1 of book 1', '{$book2ID}' );\n\t\t";
$insertPage2SQL = "\n\t\t\tINSERT INTO page (id, book_id, title) VALUES( '{$page2ID}', '{$book1ID}', 'page 2 of book 1' );\n\t\t";
$insertPage3SQL = "\n\t\t\tINSERT INTO page (id, book_id, title) VALUES( '{$page3ID}', '{$book2ID}', 'page 1 of book 2' );\n\t\t";
$insertPagePage1SQL = "\n\t\t\tINSERT INTO page_page (id, page_id, page2_id) VALUES( '{$pagePage1ID}', '{$page2ID}', '{$page3ID}' );\n\t\t";
R::exec($insertBook1SQL);
R::exec($insertBook2SQL);
R::exec($insertPage1SQL);
R::exec($insertPage2SQL);
R::exec($insertPage3SQL);
R::exec($insertPagePage1SQL);
//basic tour of basic functions....
$book1 = R::load('book', $book1ID);
asrt($book1->id, $book1ID);
asrt($book1->title, 'book 1');
$book2 = R::load('book', $book2ID);
asrt($book2->id, $book2ID);
asrt($book2->title, 'book 2');
asrt(count($book1->ownPage), 2);
asrt(count($book1->fresh()->with('LIMIT 1')->ownPage), 1);
asrt(count($book1->fresh()->withCondition(' title = ? ', array('page 2 of book 1'))->ownPage), 1);
asrt(count($book2->ownPage), 1);
asrt($book2->fresh()->countOwn('page'), 1);
$page1 = R::load('page', $page1ID);
asrt(count($page1->sharedPage), 0);
asrt($page1->fetchAs('book')->magazine->id, $book2ID);
$page2 = R::load('page', $page2ID);
asrt(count($page2->sharedPage), 1);
asrt($page2->fresh()->countShared('page'), 1);
$page3 = R::findOne('page', ' title = ? ', array('page 1 of book 2'));
asrt($page3->id, $page3ID);
asrt($page3->book->id, $book2ID);
}
public static function delbyID($tbname, $where, $arrayparam, $userid = 'system')
{
$sql = 'delete from ' . $tbname . ' where 1 = 1';
$sql = self::chkSql($sql, $where);
if ($where && $arrayparam) {
$rows = R::exec($sql, $arrayparam);
// $rows = R::getAll($sql, $arrayparam);
} else {
$rows = R::getAll($sql);
}
return $rows;
}
