PHP Facade::exec方法代码示例

 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");
//.........这里部分代码省略.........

    /**
     * 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);
//.........这里部分代码省略.........

 /**
  * 
  * 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", 'your@email.com');

            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());
     }
 }