protected function setUp()
{
$this->db = dibi::getConnection("ormion");
$this->db->delete("pages")->execute();
// id, name, description, text, created, allowed
$this->db->query("insert into [pages]", array("name" => "Clanek", "description" => "Popis", "text" => "Text", "allowed" => true), array("name" => "Article", "description" => "Description", "text" => "Text emericky.", "allowed" => false), array("name" => "Nepovolený článek", "description" => "Popis nepovoleného článku", "text" => "Dlouhý text. By byl delší než tento.", "allowed" => false), array("name" => "Jinačí článek", "description" => "Ryze alternativní popis", "text" => "Duchaplný text.", "allowed" => true));
$this->object = new Page();
}
/**
* @return int
*/
public function count()
{
if ($this->count === NULL) {
$this->count = $this->connection->query('
SELECT COUNT(*) FROM', $this->sql)->fetchSingle();
}
return $this->count;
}
protected function setUp()
{
$this->db = dibi::getConnection("ormion");
$this->db->delete("pages")->execute();
$this->db->delete("connections")->execute();
$this->db->delete("tags")->execute();
$this->db->query("insert into [pages]", array("name" => "Clanek", "description" => "Popis", "text" => "Text", "visits" => 0, "allowed" => true), array("name" => "Article", "description" => "Description", "text" => "Text emericky.", "visits" => 5, "allowed" => false), array("name" => "Nepovolený článek", "description" => "Popis nepovoleného článku", "text" => "Dlouhý text. By byl delší než tento.", "visits" => 3, "allowed" => false), array("name" => "Jinačí článek", "description" => "Ryze alternativní popis", "text" => "Duchaplný text.", "visits" => 8, "allowed" => true));
$this->db->query("insert into [tags]", array("name" => "Osobní", "url" => "osobni"), array("name" => "Technologie", "url" => "technologie"), array("name" => "Společnost", "url" => "spolecnost"));
}
/**
* Can client use given grant type
* @param string $clientId
* @param string $grantType
* @return bool
*/
public function canUseGrantType($clientId, $grantType)
{
$result = $this->context->query('
SELECT g.name
FROM oauth_client_grant AS cg
RIGHT JOIN oauth_grant AS g ON cg.grant_id = cg.grant_id AND g.name = %s
WHERE cg.client_id = %i
', $grantType, $clientId);
return (bool) $result->fetch();
}
public function update($values)
{
if (empty($this->table)) {
throw new \Natsu\Model\Exception('Cannot update - missing table name in model class');
}
//die('update');
$this->database->query("UPDATE `{$this->table}` SET ", $values, ' WHERE `id`= ?', $values->id);
// $context = new Nette\Database\Context($this->database);
// $context->table($this->table)->getPrimary($values->id)->update($values);
// $this->table()->wherePrimary($values->id)->update($values);
}
protected function setup()
{
parent::setup();
// Load from DB
$rules = $this->db->query("SELECT * FROM %n", $this->tableName);
foreach ($rules as $rule) {
// If querying the compound name, ensure it exists
if ($rule->type == 'allow') {
$this->allow($rule->role, $rule->resource, $rule->privilege);
} else {
$this->deny($rule->role, $rule->resource, $rule->privilege);
}
}
}
/**
* @inheritDoc
*/
public function authenticate($clientId, $clientSecret = NULL)
{
$clientData = $this->dbConnection->query("SELECT * FROM %n", $this->tableName, "WHERE [clientId] = %s", $clientId)->fetch();
if ($clientData === FALSE) {
return FALSE;
}
// Check client secret
if ($clientData->secret !== NULL || $clientSecret !== NULL) {
if (!$this->hasher->checkPassword($clientSecret, $clientData->secret)) {
return FALSE;
}
}
return new Client($clientData->clientId);
}
/**
* Generates, executes SQL query and fetches the single row.
* @return array|FALSE array on success, FALSE if no next record
* @throws DibiException
*/
public function fetch()
{
if ($this->command === 'SELECT') {
$this->clauses['LIMIT'] = array(1);
}
return $this->connection->query($this->_export())->fetch();
}
/**
* Fetches single row.
* @param scalar|array primary key value
* @return array|object row
*/
public function fetch($conditions)
{
if (is_array($conditions)) {
return $this->complete($this->connection->query('SELECT * FROM %n', $this->name, 'WHERE %and', $conditions))->fetch();
}
return $this->complete($this->connection->query('SELECT * FROM %n', $this->name, 'WHERE %n=' . $this->primaryModifier, $this->primary, $conditions))->fetch();
}
/**
* Run after smoke test
*
* Drop test database.
*
* @return NULL
*/
private function finishSmokeTest()
{
if (!$this->smokeTest) {
return;
}
$this->dibi->query('DROP DATABASE IF EXISTS %n;', $this->databaseName);
}
/**
* @return DibiResult
*/
private function query($args)
{
$res = $this->connection->query($args);
foreach ($this->setups as $setup) {
call_user_func_array(array($res, array_shift($setup)), $setup);
}
return $res;
}
public function match(\Nette\Http\IRequest $request)
{
/**
* @var $appRequest \Nette\Application\Request
*/
$appRequest = parent::match($request);
if (!isset($appRequest->parameters['id'])) {
return NULL;
}
if (!is_numeric($appRequest->parameters['id'])) {
$page = $this->database->query("SELECT contentId FROM route WHERE url = ?", $appRequest->parameters['id'])->fetch();
if ($page == NULL) {
return NULL;
}
$appRequest->parameters['id'] = $page->contentId;
}
return $appRequest;
}
public function addContact($contact)
{
try {
$this->database->query('INSERT INTO [' . self::TABLE_NAME_CONTACT . ']', [self::COLUMN_ID => $contact['id'], self::COLUMN_EMAIL => $contact['email']]);
return $this->database->getInsertId();
} catch (Nette\Database\DriverException $e) {
throw new \Nette\Database\DriverException();
}
}
public function __construct(\DibiConnection $db)
{
//static roles
$this->addRole('guest');
$this->addRole('authenticated', 'guest');
$this->addRole('manager', 'authenticated');
$this->addRole('administrator', 'manager');
$this->addRole('student', 'authenticated');
$this->addRole('teacher', 'authenticated');
//dynamic roles
$groups = $db->query("SELECT * FROM `group`")->fetchAll();
foreach ($groups as $group) {
if (!$this->hasRole($group->role_name)) {
$this->addRole($group->role_name, 'authenticated');
}
}
// resources
$this->addResource('Front:Homepage');
$this->addResource('Front:Files');
$this->addResource('Service:Sign');
$this->addResource('Service:Error');
$this->addResource('Dashboard:Homepage');
$this->addResource('Dashboard:Users');
$this->addResource('Dashboard:Groups');
$this->addResource('Dashboard:My');
$this->addResource('Dashboard:Files');
$this->addResource('Works:Homepage');
$this->addResource('Works:Sets');
$this->addResource('Works:Ideas');
$this->addResource('Works:Assignments');
$this->addResource('School:Homepage');
$this->addResource('School:Classes');
$this->addResource('School:Students');
$this->addResource('School:Teachers');
$this->addResource('School:Subjects');
$this->addResource('School:Groups');
$this->addResource('School:Import');
$this->addResource('Delivery:Homepage');
$this->addResource('Practice:Homepage');
$this->addResource('Activity:Homepage');
// privileges
$this->allow('guest', array('Front:Homepage', 'Service:Sign', "Service:Error"), Permission::ALL);
$this->allow('authenticated', array('Dashboard:My'), Permission::ALL);
$this->allow('authenticated', array('Dashboard:Groups'), Permission::ALL);
$this->allow('authenticated', array('Dashboard:Files'), Permission::ALL);
$this->allow('authenticated', array('Dashboard:Homepage'), array('default'));
$this->allow('student', array('Works:Homepage'), array('default'));
$this->allow('teacher', array('Works:Homepage'), Permission::ALL);
$this->allow('student', array('Works:Ideas'), array('default', 'add', 'id', 'edit', 'delete', 'clone'));
$this->allow('teacher', array('Works:Ideas'), Permission::ALL);
$this->allow('student', array('Works:Assignments'), array('default', 'application'));
$this->allow('teacher', array('Works:Assignments'), array('default', 'add', 'id', 'edit', 'delete', 'print'));
$this->allow('teacher', array('School:Homepage', 'School:Teachers', 'School:Classes', 'School:Students', 'School:Subjects', 'School:Groups'), Permission::ALL);
$this->allow('administrator', Permission::ALL, Permission::ALL);
}
public static function createConnection(DI\Container $container)
{
$dibiConnection = new \DibiConnection($container->params['database']);
$dibiConnection->query('SET NAMES UTF8');
$substitutions = array('core' => 'cms_', 'vd' => 'cms_vd_', 'c' => 'cgf_', 'media' => 'media_');
foreach ($substitutions as $sub => $prefix) {
$dibiConnection->getSubstitutes()->{$sub} = $prefix;
}
// $profiler = new \DibiProfiler();
// $dibiConnection->setProfiler($profiler);
// $dibiConnection->setFile(APP_DIR.'/../log/dibi.log');
return $dibiConnection;
}
/**
* Helper function for generation and getting lock for new
* random token
*
* @return string new token
*/
protected function generateNewToken()
{
// Generate new key
for ($i = 0;; $i++) {
if ($i > 10) {
$this->dbConnection->rollback();
throw new Nette\InvalidStateException("Couldn't generate unused token");
}
$newToken = $this->generateToken();
if ($this->dbConnection->query('SELECT * FROM %n', $this->tableName, 'WHERE [token] = %s', $newToken, 'FOR UPDATE')->fetch() === FALSE) {
break;
}
}
return $newToken;
}
/**
* @return Nette\Application\IRouter
*/
public static function createRouter(\DibiConnection $database = null)
{
$router = new RouteList();
$router[] = new Route('2013', array('presenter' => 'Content', 'action' => 'view', 'id' => 28));
$router[] = new Route('2014', array('presenter' => 'Content', 'action' => 'view', 'id' => 27));
$pageRoute = new PageRoute('<id>', array('id' => array(Route::PATTERN => ".*", Route::FILTER_IN => function ($id) use($database) {
if (is_numeric($id)) {
return $id;
} else {
$page = $database->query("SELECT * FROM route WHERE url = ?", $id)->fetch();
if ($page == NULL) {
return NULL;
}
return $page->contentId;
}
}, Route::FILTER_OUT => function ($id) use($database) {
if (!is_numeric($id)) {
return $id;
} else {
//dump($id);exit;
$fetchContent = $database->query("SELECT * FROM route WHERE contentId = ?", $id)->fetch();
if ($fetchContent) {
return $fetchContent->url;
} else {
return NULL;
}
}
}), 'presenter' => 'Content', 'action' => 'view'));
$pageRoute->database = $database;
$router[] = $pageRoute;
$router[] = new Route('butaneko', 'Sign:in');
$router[] = new Route('sitemap.xml', 'Export:sitemap');
$router[] = new Route('<presenter>/<action>[/<id>]', 'Homepage:default');
//$router[] = new Route('index.php', 'Homepage:default');
return $router;
}
/**
* @param string $repositoryName
* @param array $entities
* @return void
* @throws \obo\Exceptions\Exception
*/
public function removeRelationshipBetweenEntities($repositoryName, array $entities)
{
if (\obo\obo::$developerMode) {
if (!$this->existsRepositoryWithName($repositoryName)) {
throw new \obo\Exceptions\Exception("Relationship can't deleted repository with the name '{$repositoryName}' does not exist");
}
if (\count($entities) !== 2) {
throw new \obo\Exceptions\Exception("Relationship can't be deleted. Two entities were expected but " . \count($entities) . " given. ");
}
foreach ($entities as $entity) {
if (!$entity instanceof \obo\Entity) {
throw new \obo\Exceptions\Exception("Relationship can't be deleted. Entities must be of \\obo\\Entity instance.");
}
}
}
$this->dibiConnection->query("DELETE FROM [{$repositoryName}] WHERE [{$entities[0]->entityInformation()->repositoryName}] = {$entities[0]->primaryPropertyValue()} AND [{$entities[1]->entityInformation()->repositoryName}] = " . $this->informationForEntity($entities[1]->entityInformation())["columns"][$entities[1]->entityInformation()->informationForPropertyWithName($entities[1]->entityInformation()->primaryPropertyName)->columnName]["placeholder"], $entities[1]->primaryPropertyValue());
}
public function onLoggedIn(User $service)
{
// Only for real users (no PSK, etc...s)
try {
if (!$service->isInRole('user')) {
return;
}
// If role does not exist
} catch (Nette\InvalidStateException $e) {
return;
}
$remoteAddr = $this->httpRequest->getRemoteAddress();
$uid = $service->getId();
// Sanity check
if ($uid == NULL) {
return;
}
$this->db->query('INSERT INTO %n', $this->tableName, '([userId], [time], [ip], [time2], [ip2]) VALUES(' . '%i', $uid, ', NOW(), %s', $remoteAddr, ', NULL, NULL) ' . 'ON DUPLICATE KEY UPDATE [time2] = [time], [ip2] = [ip], [time] = NOW(), [ip] = %s', $remoteAddr);
}
/**
* Inserts topic
*
* @access public
* @param \Nette\ArrayHash $data data
* @param int $topicId topic ID to reply
* @return void
* @uses getTopic()
* @since 1.0.0
*/
public function insert(\Nette\ArrayHash $data, $topicId)
{
$data->id_forum = $this->forumId;
$this->connection->query('LOCK TABLES [' . $this->tThreads . '] WRITE');
$re = $this->getTopic($topicId);
if ($topicId && $re !== FALSE) {
$re = $this->connection->select('MIN([sequence]) - 1')->as('new_sequence')->select('%i + %i', $re->depth, 1)->as('new_depth')->from($this->tThreads)->where('[id_forum] = %i', $this->forumId)->and('[sequence] > %i', $re->sequence)->and('[depth] <= %i', $re->depth)->fetch();
if ($re->new_sequence) {
$this->connection->query('UPDATE [' . $this->tThreads . '] SET
[sequence] = [sequence] + %i
WHERE [id_forum] = %i
AND [sequence] > %i', 1, $this->forumId, $re->new_sequence);
} else {
$re = $this->connection->select('MAX([sequence])')->as('new_sequence')->select('%i', $re->new_depth)->as('new_depth')->from($this->tThreads)->where('[id_forum] = %i', $this->forumId)->fetch();
}
} else {
$re = $this->connection->select('MAX([sequence])')->as('new_sequence')->select('%i', 0)->as('new_depth')->from($this->tThreads)->where('[id_forum] = %i', $this->forumId)->fetch();
}
$data->sequence = $re->new_sequence + 1;
$data->depth = $re->new_depth;
$this->connection->insert($this->tThreads, $data)->execute();
$this->connection->query('UNLOCK TABLES');
}
<?php
require_once 'vendor/autoload.php';
require_once 'config.php';
use Dibi\Dibi;
$db = new DibiConnection($database_configuration);
if (isset($_COOKIE['language'])) {
$locale = $db->fetchSingle('SELECT locale FROM Languages WHERE code="' . $_COOKIE['language'] . '"');
putenv("LC_ALL={$locale}");
setlocale(LC_ALL, $locale);
bindtextdomain($text_domain, $base_dir . "/i18n");
textdomain($text_domain);
}
switch (isset($_GET['q']) ? $_GET['q'] : '') {
case "selectlanguage":
$rows = $db->query("SELECT code, name FROM Languages WHERE supported = 1");
echo "<option value='' selected></option>";
foreach ($rows as $row) {
echo "<option value='" . $row['code'] . "'>" . $row['name'] . "</option>";
}
break;
case "savetext":
$language = mysql_real_escape_string(isset($_POST['language']) ? $_POST['language'] : '');
// It will be possible to use Null Coalesce Operator in PHP 7, see: https://wiki.php.net/rfc/isset_ternary
$title = mysql_real_escape_string(isset($_POST['title']) ? $_POST['title'] : '');
$text = mysql_real_escape_string(isset($_POST['text']) ? $_POST['text'] : '');
$level = mysql_real_escape_string(isset($_POST['level']) ? $_POST['level'] : '');
$source = mysql_real_escape_string(isset($_POST['source']) ? $_POST['source'] : '');
$url = mysql_real_escape_string(isset($_POST['url']) ? $_POST['url'] : '');
$public = mysql_real_escape_string(isset($_POST['pub']) ? $_POST['pub'] : '');
$db->query("INSERT INTO Texts (language,title,text,level,source,url,public) VALUES ('" . $language . "','" . $title . "','" . $text . "','" . $level . "','" . $source . "','" . $url . "','" . $public . "')");
public function insert(array $data)
{
$this->dibi->query('INSERT INTO %n', $this->table_name, $data);
return $this->dibi->getInsertId();
}
/**
* Returns roles for given user
*
* @warning Method does NOT check if user actually exists
*
* @param int
* @return array
*/
public function getUserRoles($userId)
{
$roles = $this->db->query("SELECT [role] FROM %n", $this->tableName[self::TABLE_ROLES], "WHERE [user] = %i", $userId)->fetchAssoc('[]=role');
array_unshift($roles, "user:{$userId}");
return array_unique($roles);
}
public function reconnectPages($newPageId, $oldPageId)
{
return $this->connection->query("UPDATE [:vd:pages_files] SET [page_id]=%i WHERE [page_id]=%i", $newPageId, $oldPageId);
}
$db = new DibiConnection($database_configuration);
$language = isset($_GET['lang']) ? $_GET['lang'] : '';
$rows = $db->fetchAll('SELECT url FROM Texts WHERE language="' . $language . '"');
$publishedUrls = array();
foreach ($rows as $row) {
array_push($publishedUrls, $row['url']);
}
switch ($language) {
case "en":
$reports = simplexml_load_file("https://en.wikinews.org/wiki/Special:NewsFeed");
foreach ($reports->url as $report_url) {
$link = $report_url->loc;
$report = new WikinewsReport($link, $publishedUrls);
$extracted_report = $report->getTitleTextLevel();
if (!$extracted_report['published']) {
$db->query("INSERT INTO Texts (language,title,text,level,source,url,public) VALUES ('" . $language . "','" . $extracted_report['title'] . "','" . $extracted_report['text'] . "','" . $extracted_report['level'] . "','Wikinews','" . $link . "','1')");
}
}
break;
case "de":
$reports = simplexml_load_file("https://de.wikinews.org/w/api.php?format=xml&action=query&prop=extracts&exchars=99999999&explaintext&exsectionformat=wiki&titles=Kategorie:Ver%C3%B6ffentlicht");
$titles = $reports->query->pages->page->extract;
$titles = trim($titles, " \n\t\r\v…");
$links = preg_split("/[\n\r]+/", $titles);
foreach ($links as &$link) {
$link = substr($link, stripos($link, ": ") + 2);
$link = str_replace(" ", "_", $link);
$link = urlencode($link);
$link = "https://de.wikinews.org/wiki/" . $link;
$report = new WikinewsReport($link, $publishedUrls);
$extracted_report = $report->getTitleTextLevel();
/**
* @param DibiConnection
* @param string table name
* @param string file path
*
* @return int
* @throws DibiException
* @throws Nette\IOException
*/
function dumpData(DibiConnection $db, $table, $file)
{
$rows = $db->query("SELECT * FROM %n", $table)->fetchAll();
if (count($rows) == 0) {
if (file_exists($file) && !unlink($file)) {
throw new Nette\IOException("Failed to delete {$file}");
}
return;
}
$sql = "" . "--\n" . "-- Data for table: {$table}\n" . "-- Generated: " . date("Y-m-d H:i:s") . "\n" . "--\n" . "START TRANSACTION;\n";
$perCommand = 10;
for ($i = 0; $i < ceil(count($rows) / $perCommand); $i++) {
$slice = array_slice($rows, $i * $perCommand, $perCommand);
$sql .= $db->translate("INSERT INTO %n %ex", $table, $slice) . ";\n";
}
$sql .= "COMMIT;\n";
if (file_put_contents($file, $sql) === FALSE) {
throw new Nette\IOException("Cannot write to file '{$file}'");
}
}
/**
* @return int
*/
public function count()
{
return (int) $this->connection->query('SELECT COUNT(*) FROM (%ex', $this->_export(), ') AS [data]')->fetchSingle();
}
$textTitle = '';
$textOrigin = '';
$textSource = '';
}
$row = $db->fetch('SELECT * FROM TestTypes WHERE id="' . $testTypeId . '" LIMIT 1');
$testTitle = $row->title;
$testDescription = $row->description;
$testTerms = $row->terms;
$testScript = $row->script;
if ($row->css == "") {
$ownStyle = "";
} else {
$ownStyle = "\n <link rel='stylesheet' type='text/css' href='" . $base_url . "scripts/" . $row->css . $minimise . ".css'>";
}
$externalCodes = "";
$rows = $db->query("SELECT ec.type, ec.src, ec.relative_url FROM ExternalCodes ec INNER JOIN TestTypes2ExternalCodes tt2ec ON ec.id = tt2ec.external_code_id WHERE tt2ec.test_type_id = {$testTypeId} ORDER BY ec.type, ec.id ASC");
foreach ($rows as $row) {
if ($row['relative_url'] == 1) {
$extCodeSrc = $base_url . $row['src'];
} else {
$extCodeSrc = $row['src'];
}
switch ($row['type']) {
case "js":
$externalCodes .= "\n <script type='text/javascript' src='{$extCodeSrc}'></script>";
break;
case "css":
$externalCodes .= "\n <link rel='stylesheet' type='text/css' href='{$extCodeSrc}'>";
break;
}
}