Ejemplo n.º 1
0
	public function __construct($tableName, NConnection $connection, IReflection $reflection)
	{
		$this->tableName = $tableName;
		$this->databaseReflection = $reflection;
		$this->driver = $connection->getSupplementalDriver();
		$this->driverName = $connection->getAttribute(PDO::ATTR_DRIVER_NAME);
		$this->delimitedTable = $this->tryDelimite($tableName);
	}
Ejemplo n.º 2
0
 /**
  * 
  * @return NTableSelection
  */
 public function getTable()
 {
     // název tabulky odvodit z názvu třídy
     preg_match('#(\\w+)Repository$#', get_class($this), $m);
     $string = $m[1];
     $string[0] = strtolower($string[0]);
     return $this->connection->table($string);
 }
Ejemplo n.º 3
0
	/**
	 * Returns metadata for all indexes in a table.
	 */
	public function getIndexes($table)
	{
		$columns = array();
		foreach ($this->connection->query("
			SELECT ordinal_position, column_name
			FROM information_schema.columns
			WHERE table_name = {$this->connection->quote($table)} AND table_schema = current_schema()
			ORDER BY ordinal_position
		") as $row) {
			$columns[$row['ordinal_position']] = $row['column_name'];
		}

		$indexes = array();
		foreach ($this->connection->query("
			SELECT pg_class2.relname, indisunique, indisprimary, indkey
			FROM pg_class
			LEFT JOIN pg_index on pg_class.oid = pg_index.indrelid
			INNER JOIN pg_class as pg_class2 on pg_class2.oid = pg_index.indexrelid
			WHERE pg_class.relname = {$this->connection->quote($table)}
		") as $row) {
			$indexes[$row['relname']]['name'] = $row['relname'];
			$indexes[$row['relname']]['unique'] = $row['indisunique'] === 't';
			$indexes[$row['relname']]['primary'] = $row['indisprimary'] === 't';
			foreach (explode(' ', $row['indkey']) as $index) {
				$indexes[$row['relname']]['columns'][] = $columns[$index];
			}
		}
		return array_values($indexes);
	}
Ejemplo n.º 4
0
	protected function reloadForeignKeys($table)
	{
		foreach ($this->connection->getSupplementalDriver()->getForeignKeys($table) as $row) {
			$this->structure['belongsTo'][strtolower($table)][$row['local']] = $row['table'];
			$this->structure['hasMany'][strtolower($row['table'])][$row['local'] . $table] = array($row['local'], $table);
		}

		if (isset($this->structure['belongsTo'][$table])) {
			uksort($this->structure['belongsTo'][$table], create_function('$a, $b', '
				return strlen($a) - strlen($b);
			'));
		}
	}
Ejemplo n.º 5
0
	/**
	 * Returns list of tables.
	 */
	public function getTables()
	{
		$tables = array();
		foreach ($this->connection->query('SELECT * FROM cat', PDO::FETCH_NUM) as $row) {
			if ($row[1] === 'TABLE' || $row[1] === 'VIEW') {
				$tables[] = array(
					'name' => $row[0],
					'view' => $row[1] === 'VIEW',
				);
			}
		}
		return $tables;
	}
Ejemplo n.º 6
0
	private function detectColumnTypes()
	{
		if ($this->types === NULL) {
			$this->types = array();
			if ($this->connection->getSupplementalDriver()->supports['meta']) { // workaround for PHP bugs #53782, #54695
				$col = 0;
				while ($meta = $this->getColumnMeta($col++)) {
					if (isset($meta['native_type'])) {
						$this->types[$meta['name']] = self::detectType($meta['native_type']);
					}
				}
			}
		}
		return $this->types;
	}
Ejemplo n.º 7
0
	/**
	 * Returns metadata for all foreign keys in a table.
	 */
	public function getForeignKeys($table)
	{
		$keys = array();
		$query = 'SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE '
			. 'WHERE TABLE_SCHEMA = DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_NAME = ' . $this->connection->quote($table);

		foreach ($this->connection->query($query) as $id => $row) {
			$keys[$id]['name'] = $row['CONSTRAINT_NAME']; // foreign key name
			$keys[$id]['local'] = $row['COLUMN_NAME']; // local columns
			$keys[$id]['table'] = $row['REFERENCED_TABLE_NAME']; // referenced table
			$keys[$id]['foreign'] = $row['REFERENCED_COLUMN_NAME']; // referenced columns
		}

		return array_values($keys);
	}
	protected function reloadTableReference($table)
	{
		$tables = array();
		$query = 'SELECT COLUMN_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE
			WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ' . $this->connection->quote($table);

		foreach ($this->connection->query($query) as $row) {
			$tables[strtolower($row[0])] = $row[1];
		}

		uksort($tables, create_function('$a, $b', '
			return strlen($a) - strlen($b);
		'));

		$this->structure['belongsTo'][$table] = $tables;
	}
Ejemplo n.º 9
0
	private function detectColumnTypes()
	{
		if ($this->types === NULL) {
			$this->types = array();
			if ($this->connection->getSupplementalDriver()->isSupported(ISupplementalDriver::SUPPORT_COLUMNS_META)) { // workaround for PHP bugs #53782, #54695
				$count = $this->columnCount();
				for ($col = 0; $col < $count; $col++) {
					$meta = $this->getColumnMeta($col);
					if (isset($meta['native_type'])) {
						$this->types[$meta['name']] = NDatabaseHelpers::detectType($meta['native_type']);
					}
				}
			}
		}
		return $this->types;
	}
Ejemplo n.º 10
0
	/**
	 * Returns metadata for all foreign keys in a table.
	 */
	public function getForeignKeys($table)
	{
		$keys = array();
		foreach ($this->connection->query("PRAGMA foreign_key_list({$this->delimite($table)})") as $row) {
			$keys[$row['id']]['name'] = $row['id']; // foreign key name
			$keys[$row['id']]['local'][$row['seq']] = $row['from']; // local columns
			$keys[$row['id']]['table'] = $row['table']; // referenced table
			$keys[$row['id']]['foreign'][$row['seq']] = $row['to']; // referenced columns
			$keys[$row['id']]['onDelete'] = $row['on_delete'];
			$keys[$row['id']]['onUpdate'] = $row['on_update'];

			if ($keys[$row['id']]['foreign'][0] == NULL) {
				$keys[$row['id']]['foreign'] = NULL;
			}
		}
		return array_values($keys);
	}
Ejemplo n.º 11
0
	/**
	 * Returns metadata for all indexes in a table.
	 */
	public function getIndexes($table)
	{
		/*$this->connection->query("
			SELECT *
			FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
			WHERE TABLE_NAME = {$this->connection->quote($table)} AND TABLE_SCHEMA = DATABASE()
			AND REFERENCED_COLUMN_NAME IS NULL
		");*/
		$indexes = array();
		foreach ($this->connection->query('SHOW INDEX FROM ' . $this->delimite($table)) as $row) {
			$indexes[$row['Key_name']]['name'] = $row['Key_name'];
			$indexes[$row['Key_name']]['unique'] = !$row['Non_unique'];
			$indexes[$row['Key_name']]['primary'] = $row['Key_name'] === 'PRIMARY';
			$indexes[$row['Key_name']]['columns'][$row['Seq_in_index'] - 1] = $row['Column_name'];
		}
		return array_values($indexes);
	}
Ejemplo n.º 12
0
	/**
	 * Updates all rows in result set.
	 * @param  array|Traversable ($column => $value)
	 * @return int number of affected rows or FALSE in case of an error
	 */
	public function update($data)
	{
		if ($data instanceof Traversable) {
			$data = iterator_to_array($data);

		} elseif (!is_array($data)) {
			throw new InvalidArgumentException;
		}

		if (!$data) {
			return 0;
		}
		// joins in UPDATE are supported only in MySQL
		return $this->connection->queryArgs(
			'UPDATE' . $this->topString() . " $this->delimitedName SET ?" . $this->whereString(),
			array_merge(array($data), $this->parameters)
		)->rowCount();
	}
Ejemplo n.º 13
0
	/**
	 * Updates all rows in result set.
	 * Joins in UPDATE are supported only in MySQL
	 * @param  array|Traversable ($column => $value)
	 * @return int number of affected rows or FALSE in case of an error
	 */
	public function update($data)
	{
		if ($data instanceof Traversable) {
			$data = iterator_to_array($data);

		} elseif (!is_array($data)) {
			throw new InvalidArgumentException;
		}

		if (!$data) {
			return 0;
		}

		return $this->connection->queryArgs(
			$this->sqlBuilder->buildUpdateQuery(),
			array_merge(array($data), $this->sqlBuilder->getParameters())
		)->rowCount();
	}
Ejemplo n.º 14
0
	/**
	 * Returns metadata for all foreign keys in a table.
	 */
	public function getForeignKeys($table)
	{
		/* Does't work with multicolumn foreign keys */
		return $this->connection->query("
			SELECT
				co.conname::varchar AS name,
				al.attname::varchar AS local,
				cf.relname::varchar AS table,
				af.attname::varchar AS foreign
			FROM
				pg_catalog.pg_constraint AS co
				JOIN pg_catalog.pg_namespace AS n ON co.connamespace = n.oid
				JOIN pg_catalog.pg_class AS cl ON co.conrelid = cl.oid
				JOIN pg_catalog.pg_class AS cf ON co.confrelid = cf.oid
				JOIN pg_catalog.pg_attribute AS al ON al.attrelid = cl.oid AND al.attnum = co.conkey[1]
				JOIN pg_catalog.pg_attribute AS af ON af.attrelid = cf.oid AND af.attnum = co.confkey[1]
			WHERE
				n.nspname = current_schema()
				AND co.contype = 'f'
				AND cl.relname = {$this->connection->quote($table)}
		")->fetchAll();
	}
Ejemplo n.º 15
0
	public function getPanel()
	{
		$this->disabled = TRUE;
		$s = '';
		$h = 'htmlSpecialChars';
		foreach ($this->queries as $i => $query) {
			list($sql, $params, $time, $rows, $connection, $source) = $query;

			$explain = NULL; // EXPLAIN is called here to work SELECT FOUND_ROWS()
			if ($this->explain && preg_match('#\s*SELECT\s#iA', $sql)) {
				try {
					$explain = $connection->queryArgs('EXPLAIN ' . $sql, $params)->fetchAll();
				} catch (PDOException $e) {}
			}

			$s .= '<tr><td>' . sprintf('%0.3f', $time * 1000);
			if ($explain) {
				static $counter;
				$counter++;
				$s .= "<br /><a href='#' class='nette-toggler' rel='#nette-DbConnectionPanel-row-$counter'>explain&nbsp;&#x25ba;</a>";
			}

			$s .= '</td><td class="nette-DbConnectionPanel-sql">' . NConnection::highlightSql(self::$maxLength ? NStrings::truncate($sql, self::$maxLength) : $sql);
			if ($explain) {
				$s .= "<table id='nette-DbConnectionPanel-row-$counter' class='nette-collapsed'><tr>";
				foreach ($explain[0] as $col => $foo) {
					$s .= "<th>{$h($col)}</th>";
				}
				$s .= "</tr>";
				foreach ($explain as $row) {
					$s .= "<tr>";
					foreach ($row as $col) {
						$s .= "<td>{$h($col)}</td>";
					}
					$s .= "</tr>";
				}
				$s .= "</table>";
			}
			if ($source) {
				$s .= NDebugHelpers::editorLink($source[0], $source[1])->class('nette-DbConnectionPanel-source');
			}

			$s .= '</td><td>';
			foreach ($params as $param) {
				$s .= NDebugger::dump($param, TRUE);
			}

			$s .= '</td><td>' . $rows . '</td></tr>';
		}

		return empty($this->queries) ? '' :
			'<style> #nette-debug td.nette-DbConnectionPanel-sql { background: white !important }
			#nette-debug .nette-DbConnectionPanel-source { color: #BBB !important }
			#nette-debug nette-DbConnectionPanel tr table { margin: 8px 0; max-height: 150px; overflow:auto } </style>
			<h1>Queries: ' . count($this->queries) . ($this->totalTime ? ', time: ' . sprintf('%0.3f', $this->totalTime * 1000) . ' ms' : '') . '</h1>
			<div class="nette-inner nette-DbConnectionPanel">
			<table>
				<tr><th>Time&nbsp;ms</th><th>SQL Statement</th><th>Params</th><th>Rows</th></tr>' . $s . '
			</table>
			</div>';
	}
	/**
	 * @return NConnection
	 */
	protected function createServiceNette__database__default()
	{
		$service = new NConnection('mysql:host=localhost;dbname=rs', 'root', NULL, NULL);
		$service->setCacheStorage($this->getService('cacheStorage'));
		NDebugger::$blueScreen->addPanel('NDatabasePanel::renderException');
		$service->setDatabaseReflection(new NDiscoveredReflection($this->getService('cacheStorage')));
		return $service;
	}
Ejemplo n.º 17
0
	/**
	 * Import SQL dump from file - extreme fast.
	 * @return int  count of commands
	 */
	public static function loadFromFile(NConnection $connection, $file)
	{
		@set_time_limit(0); // intentionally @

		$handle = @fopen($file, 'r'); // intentionally @
		if (!$handle) {
			throw new FileNotFoundException("Cannot open file '$file'.");
		}

		$count = 0;
		$sql = '';
		while (!feof($handle)) {
			$s = fgets($handle);
			$sql .= $s;
			if (substr(rtrim($s), -1) === ';') {
				$connection->query($sql); // native query without logging
				$sql = '';
				$count++;
			}
		}
		if (trim($sql) !== '') {
			$connection->query($sql);
			$count++;
		}
		fclose($handle);
		return $count;
	}
Ejemplo n.º 18
0
 public function createAuthenticatorService()
 {
     return new Authenticator($this->database->table('users'));
 }
Ejemplo n.º 19
0
	private function formatValue($value)
	{
		if (is_string($value)) {
			if (strlen($value) > 20) {
				$this->remaining[] = $value;
				return '?';

			} else {
				return $this->connection->quote($value);
			}

		} elseif (is_int($value)) {
			return (string) $value;

		} elseif (is_float($value)) {
			return rtrim(rtrim(number_format($value, 10, '.', ''), '0'), '.');

		} elseif (is_bool($value)) {
			$this->remaining[] = $value;
			return '?';

		} elseif ($value === NULL) {
			return 'NULL';

		} elseif ($value instanceof NTableRow) {
			return $value->getPrimary();

		} elseif (is_array($value) || $value instanceof Traversable) {
			$vx = $kx = array();

			if (isset($value[0])) { // non-associative; value, value, value
				foreach ($value as $v) {
					$vx[] = $this->formatValue($v);
				}
				return implode(', ', $vx);

			} elseif ($this->arrayMode === 'values') { // (key, key, ...) VALUES (value, value, ...)
				$this->arrayMode = 'multi';
				foreach ($value as $k => $v) {
					$kx[] = $this->driver->delimite($k);
					$vx[] = $this->formatValue($v);
				}
				return '(' . implode(', ', $kx) . ') VALUES (' . implode(', ', $vx) . ')';

			} elseif ($this->arrayMode === 'assoc') { // key=value, key=value, ...
				foreach ($value as $k => $v) {
					$vx[] = $this->driver->delimite($k) . '=' . $this->formatValue($v);
				}
				return implode(', ', $vx);

			} elseif ($this->arrayMode === 'multi') { // multiple insert (value, value, ...), ...
				foreach ($value as $k => $v) {
					$vx[] = $this->formatValue($v);
				}
				return '(' . implode(', ', $vx) . ')';
			}

		} elseif ($value instanceof DateTime) {
			return $this->driver->formatDateTime($value);

		} elseif ($value instanceof NSqlLiteral) {
			return $value->value;

		} else {
			$this->remaining[] = $value;
			return '?';
		}
	}
	/**
	 * @return NConnection
	 */
	protected function createServiceNette__database__default()
	{
		$service = new NConnection('mysql:host=sql.satoya.cz;dbname=gymfed_demo', 'gymfed_demo', 'HuuJNUw6GpjMjqfx', NULL);
		$service->setCacheStorage($this->getService('cacheStorage'));
		NDebugger::$blueScreen->addPanel('NDatabasePanel::renderException');
		$service->setDatabaseReflection(new NDiscoveredReflection($this->getService('cacheStorage')));
		return $service;
	}