public function index(Request $request)
{
$beans = R::findAll($model);
$records = R::exportAll($beans, true);
$tables = R::getAll('SELECT name FROM sqlite_master WHERE type = "table"');
return view('umodel.index', ['records' => $records, 'tables' => $tables]);
}
/**
* Tests the various ways to fetch (select queries)
* data using adapter methods in the facade.
* Also tests the new R::getAssocRow() method,
* as requested in issue #324.
*/
public function testFetchTypes()
{
R::nuke();
$page = R::dispense('page');
$page->a = 'a';
$page->b = 'b';
R::store($page);
$page = R::dispense('page');
$page->a = 'c';
$page->b = 'd';
R::store($page);
$expect = '[{"id":"1","a":"a","b":"b"},{"id":"2","a":"c","b":"d"}]';
asrt(json_encode(R::getAll('SELECT * FROM page')), $expect);
$expect = '{"1":"a","2":"c"}';
asrt(json_encode(R::getAssoc('SELECT id, a FROM page')), $expect);
asrt(json_encode(R::getAssoc('SELECT id, a, b FROM page')), $expect);
$expect = '[{"id":"1","a":"a"},{"id":"2","a":"c"}]';
asrt(json_encode(R::getAssocRow('SELECT id, a FROM page')), $expect);
$expect = '[{"id":"1","a":"a","b":"b"},{"id":"2","a":"c","b":"d"}]';
asrt(json_encode(R::getAssocRow('SELECT id, a, b FROM page')), $expect);
$expect = '{"id":"1","a":"a","b":"b"}';
asrt(json_encode(R::getRow('SELECT * FROM page WHERE id = 1')), $expect);
$expect = '"a"';
asrt(json_encode(R::getCell('SELECT a FROM page WHERE id = 1')), $expect);
$expect = '"b"';
asrt(json_encode(R::getCell('SELECT b FROM page WHERE id = 1')), $expect);
$expect = '"c"';
asrt(json_encode(R::getCell('SELECT a FROM page WHERE id = 2')), $expect);
$expect = '["a","c"]';
asrt(json_encode(R::getCol('SELECT a FROM page')), $expect);
$expect = '["b","d"]';
asrt(json_encode(R::getCol('SELECT b FROM page')), $expect);
}
/**
* Tests whether we can send results of a query to meta data
* when converting to bean.
*/
public function testImportMeta()
{
R::nuke();
$book = R::dispense(array('_type' => 'book', 'title' => 'Bean Recipes', 'author' => 'Meastro de la Bean'));
$pages = R::dispenseAll('page*2');
$book->ownPageList = reset($pages);
R::store($book);
$data = R::getRow('SELECT book.*,
COUNT(page.id) AS meta_count,
1234 AS meta_extra
FROM book
LEFT JOIN page ON page.book_id = book.id
GROUP BY book.id
');
$bean = R::convertToBean('book', $data, 'meta_');
asrt(isset($bean->title), TRUE);
asrt(isset($bean->author), TRUE);
asrt(isset($bean->meta_count), FALSE);
asrt(isset($bean->meta_extra), FALSE);
$data = $bean->getMeta('data.bundle');
asrt(intval($data['meta_count']), 2);
asrt(intval($data['meta_extra']), 1234);
//now with multiple beans
$book = R::dispense(array('_type' => 'book', 'title' => 'Bean Adventures', 'author' => 'Mr Adventure'));
$pages = R::dispenseAll('page*3');
$book->ownPageList = reset($pages);
R::store($book);
$data = R::getAll('SELECT book.*,
COUNT(page.id) AS meta_pages
FROM book
LEFT JOIN page ON page.book_id = book.id
GROUP BY book.id
');
$books = R::convertToBeans('book', $data, 'meta_');
$found = 0;
foreach ($books as $book) {
if ($book->title == 'Bean Recipes') {
$found++;
asrt(isset($book->title), TRUE);
asrt(isset($book->author), TRUE);
asrt(isset($book->meta_count), FALSE);
asrt(isset($book->meta_extra), FALSE);
$data = $book->getMeta('data.bundle');
asrt(intval($data['meta_pages']), 2);
}
if ($book->title == 'Bean Adventures') {
$found++;
asrt(isset($book->title), TRUE);
asrt(isset($book->author), TRUE);
asrt(isset($book->meta_pages), FALSE);
asrt(isset($book->meta_extra), FALSE);
$data = $book->getMeta('data.bundle');
asrt(intval($data['meta_pages']), 3);
}
}
asrt($found, 2);
}
/**
* In the past it was not possible to export beans
* like 'feed' (Model_Feed).
*
* @return void
*/
public function testExportIssue()
{
R::nuke();
$feed = R::dispense('feed');
$feed->post = array('first', 'second');
R::store($feed);
$rows = R::getAll('SELECT * FROM feed');
asrt($rows[0]['post'], '["first","second"]');
$feed = $feed->fresh();
asrt(is_array($feed->post), TRUE);
asrt($feed->post[0], 'first');
asrt($feed->post[1], 'second');
R::store($feed);
$rows = R::getAll('SELECT * FROM feed');
asrt($rows[0]['post'], '["first","second"]');
$feed = R::load('feed', $feed->id);
$feed->post[] = 'third';
R::store($feed);
$rows = R::getAll('SELECT * FROM feed');
asrt($rows[0]['post'], '["first","second","third"]');
$feed = $feed->fresh();
asrt(is_array($feed->post), TRUE);
asrt($feed->post[0], 'first');
asrt($feed->post[1], 'second');
asrt($feed->post[2], 'third');
//now the catch: can we use export?
//PHP Fatal error: Call to a member function export() on a non-object
$feeds = R::exportAll(R::find('feed'));
asrt(is_array($feeds), TRUE);
$feed = reset($feeds);
asrt($feed['post'][0], 'first');
asrt($feed['post'][1], 'second');
asrt($feed['post'][2], 'third');
//can we also dup()?
$feedOne = R::findOne('feed');
R::store(R::dup($feedOne));
asrt(R::count('feed'), 2);
//can we delete?
R::trash($feedOne);
asrt(R::count('feed'), 1);
$feedTwo = R::findOne('feed');
$feed = $feedTwo->export();
asrt($feed['post'][0], 'first');
asrt($feed['post'][1], 'second');
asrt($feed['post'][2], 'third');
}
/**
* Data used in index listing.
* @return array
*/
protected function listData()
{
$sql = "SELECT * FROM {$this->model()} ORDER BY position";
$instances = R::getAll($sql);
$instanceRows = array();
if (!empty($instances)) {
foreach ($instances as $instanceArray) {
$instanceRow = array($instanceArray['field_name'], $this->listActions($instanceArray));
if ($this->setupSortable) {
$instanceRow[] = $instanceArray['id'];
array_unshift($instanceRow, $instanceArray[$this->dragField]);
}
$instanceRows[] = $instanceRow;
}
}
return $instanceRows;
}
/**
* Data used in index listing.
* @return array
*/
protected function listData()
{
$sql = "SELECT * FROM {$this->model()} ORDER BY position";
$instances = R::getAll($sql);
$instanceRows = array();
if (!empty($instances)) {
foreach ($instances as $instanceArray) {
$instance = $this->app->createModel($this->model(), $instanceArray);
$image1 = $instance->get('multiple_many_types');
if (empty($image1)) {
$image1 = 'no image';
} else {
$image1 = $image1[0]['smartphone'];
}
$image2 = $instance->get('multiple_one_type');
if (empty($image2)) {
$image2 = 'no image';
} else {
$image2 = $image2[0];
}
$image3 = $instance->get('single_many_types');
if (empty($image3)) {
$image3 = 'no image';
} else {
$image3 = $image3['smartphone'];
}
$image4 = $instance->get('single_one_type');
if (empty($image4)) {
$image4 = 'no image';
}
$instanceRow = array($image1, $image2, $image3, $image4, $this->listActions($instanceArray));
if ($this->setupSortable) {
$instanceRow[] = $instanceArray['id'];
array_unshift($instanceRow, $instanceArray[$this->dragField]);
}
$instanceRows[] = $instanceRow;
}
}
return $instanceRows;
}
/**
* Test common Facade usage scenarios.
*
* @return void
*/
public function testCommonUsageFacade()
{
$toolbox = R::getToolBox();
$adapter = $toolbox->getDatabaseAdapter();
$writer = $toolbox->getWriter();
$redbean = $toolbox->getRedBean();
$pdo = $adapter->getDatabase();
$a = new AssociationManager($toolbox);
asrt(R::getRedBean() instanceof OODB, TRUE);
asrt(R::getToolBox() instanceof ToolBox, TRUE);
asrt(R::getDatabaseAdapter() instanceof Adapter, TRUE);
asrt(R::getWriter() instanceof QueryWriter, TRUE);
$book = R::dispense("book");
asrt($book instanceof OODBBean, TRUE);
$book->title = "a nice book";
$id = R::store($book);
asrt($id > 0, TRUE);
$book = R::load("book", (int) $id);
asrt($book->title, "a nice book");
asrt(R::load('book', 999)->title, NULL);
R::freeze(TRUE);
try {
R::load('bookies', 999);
fail();
} catch (\Exception $e) {
pass();
}
R::freeze(FALSE);
$author = R::dispense("author");
$author->name = "me";
R::store($author);
$book9 = R::dispense("book");
$author9 = R::dispense("author");
$author9->name = "mr Nine";
$a9 = R::store($author9);
$book9->author_id = $a9;
$bk9 = R::store($book9);
$book9 = R::load("book", $bk9);
$author = R::load("author", $book9->author_id);
asrt($author->name, "mr Nine");
R::trash($author);
R::trash($book9);
pass();
$book2 = R::dispense("book");
$book2->title = "second";
R::store($book2);
$book3 = R::dispense("book");
$book3->title = "third";
R::store($book3);
asrt(count(R::find("book")), 3);
asrt(count(R::findAll("book")), 3);
asrt(count(R::findAll("book", " LIMIT 2")), 2);
asrt(count(R::find("book", " id=id ")), 3);
asrt(count(R::find("book", " title LIKE ?", array("third"))), 1);
asrt(count(R::find("book", " title LIKE ?", array("%d%"))), 2);
// Find without where clause
asrt(count(R::findAll('book', ' order by id')), 3);
R::trash($book3);
R::trash($book2);
asrt(count(R::getAll("SELECT * FROM book ")), 1);
asrt(count(R::getCol("SELECT title FROM book ")), 1);
asrt((int) R::getCell("SELECT 123 "), 123);
$book = R::dispense("book");
$book->title = "not so original title";
$author = R::dispense("author");
$author->name = "Bobby";
R::store($book);
$aid = R::store($author);
$author = R::findOne("author", " name = ? ", array("Bobby"));
}
/**
* 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 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);
}
/**
* This method retrieves a SellerAd record from the
* db.
* @param $id The id of the record to fetch.
* @return assoc array
*/
public function fetchGetItem($id)
{
if (v::int()->min(0)->validate($id)) {
$id = intval($id);
} else {
throw new InvalidArgumentException("Invalid id");
}
$ads = R::getAll("SELECT * FROM sellerads WHERE id = :id", array(':id' => $id));
//->export();
if (count($ads) == 0) {
return NULL;
} else {
return $ads[0];
}
}
/**
* Test duplication and tainting.
*
* @return void
*/
public function testDupAndExportNonTainting()
{
testpack('Dup() and Export() should not taint beans');
$p = R::dispense('page');
$b = R::dispense('book');
$b->ownPage[] = $p;
$b->title = 'a';
$id = R::store($b);
$b = R::load('book', $id);
asrt(!$b->getMeta('tainted'), TRUE);
R::exportAll($b);
asrt(!$b->getMeta('tainted'), TRUE);
R::dup($b);
asrt(!$b->getMeta('tainted'), TRUE);
testpack('Test issue with ownItems and stealing Ids.');
R::nuke();
$bill = R::dispense('bill');
$item = R::dispense('item');
$element = R::dispense('element');
$bill->ownItem[] = $item;
$bill->sharedElement[] = $element;
R::store($bill);
$bill = R::load('bill', 1);
$bill->ownItem;
$bill->sharedElement;
$copy = R::dup($bill);
R::store($copy);
$rows = R::getAll('select * from bill_element');
asrt(count($rows), 2);
$rows = R::getAll('select * from item');
foreach ($rows as $row) {
asrt($row['bill_id'] > 0, TRUE);
}
R::nuke();
$this->runOnce();
R::freeze(TRUE);
$this->runOnce(FALSE);
R::freeze(FALSE);
}
/**
* 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);
}
/**
* Test FUSE and model formatting.
*
* @todo move tagging tests to tag tester.
*
* @return void
*/
public function testFUSE()
{
$toolbox = R::getToolBox();
$adapter = $toolbox->getDatabaseAdapter();
$blog = R::dispense('blog');
$blog->title = 'testing';
$blog->blog = 'tesing';
R::store($blog);
$blogpost = R::load("blog", 1);
$post = R::dispense("post");
$post->message = "hello";
$blog->sharedPost[] = $post;
R::store($blog);
$a = R::getAll("select * from blog ");
R::tag($post, "lousy,smart");
asrt(implode(',', R::tag($post)), "lousy,smart");
R::tag($post, "clever,smart");
$tagz = implode(',', R::tag($post));
asrt($tagz == "smart,clever" || $tagz == "clever,smart", TRUE);
R::tag($blog, array("smart", "interesting"));
asrt(implode(',', R::tag($blog)), "smart,interesting");
try {
R::tag($blog, array("smart", "interesting", "lousy!"));
pass();
} catch (RedException $e) {
fail();
}
asrt(implode(',', R::tag($blog)), "smart,interesting,lousy!");
asrt(implode(",", R::tag($blog)), "smart,interesting,lousy!");
R::untag($blog, array("smart", "interesting"));
asrt(implode(",", R::tag($blog)), "lousy!");
asrt(R::hasTag($blog, array("lousy!")), TRUE);
asrt(R::hasTag($blog, array("lousy!", "smart")), TRUE);
asrt(R::hasTag($blog, array("lousy!", "smart"), TRUE), FALSE);
R::tag($blog, FALSE);
asrt(count(R::tag($blog)), 0);
R::tag($blog, array("funny", "comic"));
asrt(count(R::tag($blog)), 2);
R::addTags($blog, array("halloween"));
asrt(count(R::tag($blog)), 3);
asrt(R::hasTag($blog, array("funny", "commic", "halloween"), TRUE), FALSE);
R::unTag($blog, array("funny"));
R::addTags($blog, "horror");
asrt(count(R::tag($blog)), 3);
asrt(R::hasTag($blog, array("horror", "commic", "halloween"), TRUE), FALSE);
// No double tags
R::addTags($blog, "horror");
asrt(R::hasTag($blog, array("horror", "commic", "halloween"), TRUE), FALSE);
asrt(count(R::tag($blog)), 3);
}
public static function getAllpage($tbname, $startIndex = 0, $numRows = 20, $type = null, $userid = 'system')
{
$sql = 'Select * from ' . $tbname . ' Limit ? , ? ';
// echo $sql;
$rows = R::getAll($sql, array($startIndex, $numRows));
if ($type) {
$rows = self::prepareForAMF($rows, array('0' => $type));
}
return $rows;
}
public function index()
{
$items = R::findAll('item');
$tables = R::getAll('SELECT name FROM sqlite_master WHERE type = "table"');
return view('item.index', ['items' => $items, 'tables' => $tables]);
}
/**
* 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);
}
protected function listDataServerSide()
{
$request = $_GET;
$fields = $this->getFields();
// Build the SQL query string from the request
$limit = $this->limit($request, $fields);
$order = $this->order($request, $fields);
$where = $this->filter($request, $fields);
$data = R::getAll("SELECT SQL_CALC_FOUND_ROWS " . $this->pluckString($fields, 'db') . "\n\t\t\t FROM `{$this->getTableName()}` t\n\t\t\t {$where}\n\t\t\t {$order}\n\t\t\t {$limit}");
$resFilterLength = R::getAll("SELECT FOUND_ROWS() AS foundRows");
$recordsFiltered = $resFilterLength[0]['foundRows'];
$resTotalLength = R::getAll("SELECT COUNT(t.`{$this->getPrimaryKey()}`) AS count\n\t\t\t FROM `{$this->getTableName()}` t");
$recordsTotal = $resTotalLength[0]['count'];
return array("draw" => intval($request['draw']), "recordsTotal" => intval($recordsTotal), "recordsFiltered" => intval($recordsFiltered), "data" => $this->dataOutput($fields, $data));
}
/**
* @param int $qid
*
* @return string
*/
public function edit($qid)
{
$question = Facade::load('questions', $qid);
if (!$question->isEmpty()) {
$answers = Facade::getAll('SELECT * FROM answers WHERE qid=? ORDER BY id', array($qid));
return $this->factory->getTwigAdminObj()->render('edit_poll.html', array('qid' => $qid, 'question' => $question->question, 'answers' => $answers));
} else {
return General::ref('index.php');
}
}