/**
* @return ExtendedPdo
*/
public static function getInstance()
{
$dbopts = parse_url(getenv('DATABASE_URL'));
$db = new ExtendedPdo("pgsql:host={$dbopts["host"]};port={$dbopts["port"]};dbname=" . ltrim($dbopts["path"], '/'), $dbopts['user'], $dbopts['pass']);
$db->exec("CREATE TABLE IF NOT EXISTS users\n(\n id serial NOT NULL,\n username character varying(50) NOT NULL,\n registered_by integer NOT NULL,\n created timestamp with time zone NOT NULL DEFAULT now(),\n CONSTRAINT user_pk PRIMARY KEY (id),\n CONSTRAINT users_username_uk UNIQUE (username)\n);");
return $db;
}
/**
* @inheritdoc
*
* @param Task $task
*/
public function handle(Task $task)
{
$config = $task->getData();
if ($config["driver"] === "mysql") {
$config += ["host" => "127.0.0.1", "port" => 3306, "charset" => "utf8", "socket" => null];
}
if ($config["remit"]["driver"] === "zeromq") {
$config["remit"]["server"] += ["host" => "127.0.0.1"];
$config["remit"]["client"] += ["host" => "127.0.0.1"];
$server = new ZeroMqServer(new InMemoryLocation($config["remit"]["client"]["host"], $config["remit"]["client"]["port"]));
$client = new ZeroMqClient(new InMemoryLocation($config["remit"]["server"]["host"], $config["remit"]["server"]["port"]));
}
$connection = new ExtendedPdo(new PDO($this->newConnectionString($config), $config["username"], $config["password"]));
$server->addListener("q", function ($query, $values, $id) use($client, $connection) {
$client->emit("r", [$connection->fetchAll($query, $values), $id]);
});
$server->addListener("d", function () use($connection, $server, $client) {
$client->emit("dd");
try {
$connection->disconnect();
} catch (Exception $exception) {
// TODO: find an elegant way to deal with this
}
$server->disconnect();
$client->disconnect();
Loop\stop();
});
Loop\periodic(0, function () use($server) {
$server->tick();
});
Loop\run();
}
public static function getInstance()
{
$basepath = realpath(__DIR__ . '/../../');
$db = new ExtendedPdo('sqlite:' . $basepath . '/weights.db');
$db->exec("CREATE TABLE IF NOT EXISTS persons (id INTEGER PRIMARY KEY, timeadded INTEGER, currentweight REAL, telegramid INTEGER, firstname TEXT, username TEXT, token TEXT)");
$db->exec("CREATE TABLE IF NOT EXISTS weights (id INTEGER PRIMARY KEY, timestamp INTEGER, weight REAL, personid INTEGER)");
return $db;
}
public static function setUpBeforeClass()
{
$extendedPdo = new ExtendedPdo('sqlite::memory:');
$extendedPdo->exec("ATTACH DATABASE `jpemeric_blog.db` AS `jpemeric_blog`");
$extendedPdo->exec("\n CREATE TABLE IF NOT EXISTS `jpemeric_blog`.`introduction` (\n `id` integer PRIMARY KEY AUTOINCREMENT,\n `type` varchar(10) NOT NULL,\n `value` varchar(25) NOT NULL,\n `title` varchar(100) NOT NULL,\n `content` text NOT NULL,\n `image` image\n )");
self::$connection = new ConnectionLocator(function () use($extendedPdo) {
return $extendedPdo;
});
}
public static function setUpBeforeClass()
{
$extendedPdo = new ExtendedPdo('sqlite::memory:');
$extendedPdo->exec("ATTACH DATABASE `jpemeric_stream.db` AS `jpemeric_stream`");
$extendedPdo->exec("\n CREATE TABLE IF NOT EXISTS `jpemeric_stream`.`activity` (\n `id` integer PRIMARY KEY AUTOINCREMENT,\n `message` text NOT NULL,\n `message_long` text NOT NULL,\n `datetime` datetime NOT NULL,\n `metadata` text NOT NULL,\n `type` varchar(10) NOT NULL,\n `type_id` integer NOT NULL,\n `created_at` datetime,\n `updated_at` datetime\n )");
self::$connection = new ConnectionLocator(function () use($extendedPdo) {
return $extendedPdo;
});
}
public static function setUpBeforeClass()
{
$extendedPdo = new ExtendedPdo('sqlite::memory:');
$extendedPdo->exec("ATTACH DATABASE `jpemeric_stream.db` AS `jpemeric_stream`");
$extendedPdo->exec("\n CREATE TABLE IF NOT EXISTS `jpemeric_stream`.`changelog` (\n `id` integer PRIMARY KEY AUTOINCREMENT,\n `hash` char(40) NOT NULL,\n `message` text,\n `message_short` varchar(100),\n `datetime` datetime NOT NULL,\n `author` varchar(50) NOT NULL,\n `commit_link` varchar(100) NOT NULL,\n `created_at` datetime,\n `updated_at` datetime\n )");
self::$connection = new ConnectionLocator(function () use($extendedPdo) {
return $extendedPdo;
});
}
public function modifyWebDispatcher($di)
{
$dispatcher = $di->get('aura/web-kernel:dispatcher');
$dispatcher->setObject('hello', function () use($di) {
$response = $di->get('aura/web-kernel:response');
$response->content->set('Hello World!');
});
$dispatcher->setObject('about', function () use($di) {
$view_factory = new \Aura\View\ViewFactory();
$view = $view_factory->newInstance();
$layout_registry = $view->getLayoutRegistry();
$layout_registry->set('default', './../src/templates/default.layout.php');
$view_registry = $view->getViewRegistry();
$view_registry->set('about', './../src/templates/about.php');
$view->setView('about');
$view->setLayout('default');
$response = $di->get('aura/web-kernel:response');
$response->content->set($view());
});
$dispatcher->setObject('data-view-sales', function () use($di) {
$view_factory = new \Aura\View\ViewFactory();
$view = $view_factory->newInstance();
$pdo = new ExtendedPdo('sqlite:./../db/database.sqlite');
$extended_pdo = new ExtendedPdo($pdo);
$stm = '
SELECT t.TrackId, sum(il.unitprice) as "TotalSales", t.Name as "TrackName", g.Name as Genre, a.Title as "AlbumTitle", at.Name as "ArtistName"
from InvoiceLine il
inner join track t on (t.TrackId = il.TrackId)
INNER JOIN genre g on (g.GenreId = t.GenreId)
inner join album a on (a.AlbumId = t.AlbumId)
INNER JOIN artist at on (at.ArtistId = a.ArtistId)
WHERE g.Name like :genre
group by t.TrackId
HAVING at.Name = :artist_name
order by sum(il.UnitPrice) desc, t.Name asc
';
$bind = array('genre' => 'TV%', 'artist_name' => 'Lost');
$sth = $pdo->prepare($stm);
$sth->execute($bind);
$layout_registry = $view->getLayoutRegistry();
$layout_registry->set('default', './../src/templates/sales.layout.php');
$view_registry = $view->getViewRegistry();
$view_registry->set('sales-data', './../src/templates/data/sales/view.php');
$view->setView('sales-data');
$view->setLayout('default');
// the "sub" template
$view_registry->set('_result', './../src/templates/data/sales/result.php');
$view->setData(['results' => $pdo->fetchObjects($stm, $bind, '\\DatabaseObjects\\Entity\\SalesData')]);
$response = $di->get('aura/web-kernel:response');
$response->content->set($view());
});
}
private function initConnection($dbname)
{
$cfg = Config::getSingleton()->get($this->configKey);
if (!$cfg) {
throw new \Exception("No configuration found for '{$this->configKey}'");
}
$cfg = $cfg[$dbname];
if (!$cfg) {
throw new \Exception("No configuration found for connection '{$dbname}'");
}
switch ($cfg['driver']) {
case 'pgsql':
case 'mysql':
if (!isset($cfg['additional_dsn'])) {
$cfg['additional_dsn'] = '';
}
$connstr = "{$cfg['driver']}:host={$cfg['host']} dbname={$cfg['database']} {$cfg['additional_dsn']}";
if (isset($cfg['port'])) {
$connstr .= ' port=' . $cfg['port'];
}
break;
case 'sqlite':
$connstr = "sqlite:{$cfg['filename']}";
break;
default:
throw new \Exception("Unsupported driver '{$cfg['driver']} given for connection '{$dbname}'.");
}
if (!isset($cfg['user'])) {
$cfg['user'] = '';
}
if (!isset($cfg['password'])) {
$cfg['password'] = '';
}
if (class_exists("Aura\\Sql\\ExtendedPdo")) {
$conn = new ExtendedPdo($connstr, $cfg['user'], $cfg['password']);
if (isset($cfg['profiling']) && $cfg['profiling'] == true) {
$conn->setProfiler(new Profiler());
$conn->getProfiler()->setActive(true);
}
} else {
$conn = new PDO($connstr, $cfg['user'], $cfg['password']);
}
if ($conn) {
$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
}
$this->connections[$dbname] = $conn;
}
/**
* Returns db for given repository.
*
* @param string $repository_url Repository url.
* @param ConsoleIO $io Console IO.
*
* @return ExtendedPdoInterface
*/
public function getDatabase($repository_url, ConsoleIO $io = null)
{
if (preg_match(Connector::URL_REGEXP, $repository_url, $regs)) {
$sub_folder = $regs[2] . $regs[3] . $regs[4];
} else {
$sub_folder = 'misc';
}
$parent_path = $this->_workingDirectory . '/' . $sub_folder;
if (!file_exists($parent_path)) {
mkdir($parent_path, 0777, true);
}
$db = new ExtendedPdo('sqlite:' . $parent_path . '/log_' . crc32($repository_url) . '.sqlite');
$profiler = clone $this->_statementProfiler;
$profiler->setIO($io);
$db->setProfiler($profiler);
return $db;
}
public function __construct($dsn, $username = null, $password = null, array $options = array(), array $attributes = array())
{
parent::__construct($dsn, $username, $password, $options, $attributes);
if (\App::getInstance()->singleton('container')->isDebug()) {
$this->setProfiler(new \Aura\Sql\Profiler());
$this->getProfiler()->setActive(true);
$this->debug = true;
}
}
public function connect()
{
if ($this->pdo) {
return;
}
// Parent connect
parent::connect();
// Loop through post connect commands
foreach ($this->aPostConnectCommands as $sCommand) {
$this->query($sCommand);
}
}
public static function setUpBeforeClass()
{
$extendedPdo = new ExtendedPdo('sqlite::memory:');
$extendedPdo->exec('ATTACH DATABASE `jpemeric_blog.db` AS `jpemeric_blog`');
$extendedPdo->exec("\n CREATE TABLE IF NOT EXISTS `jpemeric_blog`.`post` (\n `id` integer PRIMARY KEY AUTOINCREMENT,\n `title` varchar(60) NOT NULL,\n `path` varchar(60) NOT NULL,\n `category` varchar(15) NOT NULL,\n `date` datetime,\n `body` text,\n `display` integer(1) NOT NULL\n )");
$extendedPdo->exec("\n CREATE TABLE IF NOT EXISTS `jpemeric_blog`.`ptlink` (\n `post_id` integer NOT NULL,\n `tag_id` integer NOT NULL\n )");
$extendedPdo->exec("\n CREATE TABLE IF NOT EXISTS `jpemeric_blog`.`series_post` (\n `series` integer NOT NULL,\n `post` integer NOT NULL,\n `order` integer(1) NOT NULL\n )");
$extendedPdo->exec("\n CREATE TABLE IF NOT EXISTS `jpemeric_blog`.`tag` (\n `id` integer PRIMARY KEY AUTOINCREMENT,\n `tag` varchar(25) NOT NULL\n )");
self::$connection = new ConnectionLocator(function () use($extendedPdo) {
return $extendedPdo;
});
}
/**
* Remove relationship.
*
* @param \Percy\Entity\EntityInterface $entity
* @param array $rels
* @param array $map
*
* @return void
*/
public function deleteRelationship(EntityInterface $entity, array $rels, array $map)
{
$this->dbal->beginTransaction();
foreach ($rels as $rel) {
$delete = $this->query->newDelete();
$delete->from($map['defined_in']['table']);
$delete->where(sprintf('%s = :%s', $map['defined_in']['primary'], $map['defined_in']['entity']));
$delete->where(sprintf('%s = :%s', $map['target']['relationship'], 'relationship'));
$delete->limit(1);
$delete->bindValue('uuid', $entity[$map['defined_in']['entity']]);
$delete->bindValue('relationship', $rel);
$this->dbal->perform($delete->getStatement(), $delete->getBindValues());
}
$this->dbal->commit();
}
/**
* Saves a set of data to the table
* This function will either insert or update, depending on if the entity passed already has an identifier set. The
* generated/passed ID will be returned.
*
* @param object|array $data Data to save
* @param string $table Table to save to
* @param string $identifierColumn Identifier column to work against
* @return int|string
*/
public function save($data, $table, $identifierColumn = 'id')
{
$data = $this->convertToArray($data);
if (!empty($data[$identifierColumn])) {
$update = $this->queryHandler->newUpdate();
$update->table($table)->cols(array_keys($data))->where($identifierColumn . ' = :' . $identifierColumn)->bindValues($data);
$this->db->perform($update->__toString(), $update->getBindValues());
return $data[$identifierColumn];
} else {
$insert = $this->queryHandler->newInsert();
$insert->into($table)->cols(array_keys($data))->bindValues($data);
$this->db->perform($insert->__toString(), $insert->getBindValues());
$name = $insert->getLastInsertIdName($identifierColumn);
return $this->db->lastInsertId($name);
}
}
/**
* @param string $table
* @param array $where
*
* @return bool
*/
protected final function delete($table, $where = array())
{
$sql = 'DELETE FROM ' . $table . ' WHERE 1 ';
$binds = array();
if (is_array($where)) {
if (empty($where)) {
return false;
} else {
foreach ($where as $key => $value) {
$sql .= ' AND ' . $key . ' = :' . $key;
$binds[$key] = $value;
}
}
} elseif (is_int($where)) {
$sql .= ' AND Id = :Id';
$binds['Id'] = $where;
}
$this->pdo->perform($sql, $binds);
}
public function __construct(Config $config)
{
$dsn = $config->get('db.driver') . ':dbname=' . $config->get('db.name', 'eden') . ';host=' . $config->get('db.host', 'localhost');
parent::__construct($dsn, $config->get('db.user'), $config->get('db.pass'), $config->get('db.options'), $config->get('db.attributes'));
}
/**
* @inheritdoc
*
* @param string $query
* @param array $values
*
* @return PromiseInterface
*
* @throws InvalidArgumentException
*/
public function query($query, $values)
{
return Promise\resolve($this->connection->fetchAll($query, $values));
}
/**
* @param string $statement
* @param array $values
* @return array
*/
public function fetchOne($statement, array $values = [])
{
$return = parent::fetchOne($statement, $values);
return is_array($return) ? $return : [];
}
<?php
require '../vendor/autoload.php';
use Aura\Sql\ExtendedPdo;
$pdo = new ExtendedPdo('sqlite:../database.sqlite');
$pdo->exec('CREATE TABLE records (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, animal_type TEXT, animal_name TEXT, animal_age INTEGER, timestamp NUMERIC);');
if (file_exists('../database.sqlite')) {
echo 'Database and tables created.';
} else {
echo 'Database was not created. Please check to make sure this directory is writeable.';
}
public static function postInstall(Event $oEvent)
{
// Initialize
$sAppDirPath = __DIR__ . '/../../../app';
$sLocalDistConfigPath = sprintf('%s/%s', $sAppDirPath, 'install/local.php.dist');
$sLocalConfigPath = sprintf('%s/%s', $sAppDirPath, 'config/local.php');
// Create file manager
$oFileManager = new FileManager([]);
$oFileManager->addHandler('local', 'PHP', []);
// Make sure console is executable
$sOutput = 'Make sure the deployment manager console is executable';
$sErrorMessage = '';
try {
ExtendedShell::exec(sprintf('chmod +x %s', sprintf('%s/%s', $sAppDirPath, 'console')));
} catch (Exception $oException) {
// Update error message
$sErrorMessage = $oException->getMessage();
}
// No error
if ($sErrorMessage === '') {
$sResult = 'OK';
} else {
$sResult = 'KO';
}
// Output
$oEvent->getIO()->write(sprintf("\n%s: %s", $sOutput, $sResult));
// Copy dist config
$sOutput = 'Copy the local dist config';
$sErrorMessage = '';
try {
$oFileManager->copy($sLocalDistConfigPath, $sLocalConfigPath);
} catch (Exception $oException) {
// Update error message
$sErrorMessage = $oException->getMessage();
}
// Error
if ($sErrorMessage === '') {
// Output
$oEvent->getIO()->write(sprintf("\n%s: OK", $sOutput));
// Explain
$oEvent->getIO()->write("\nTo install the manager, you need a valid UTF-8 database as well as a user with " . "read/write privileges on it. Once you have it, please fill in the information below:\n");
// Set ouput
$sOutput = 'Update local config parameters';
// Get local config content
$sLocalConfigContent = $oFileManager->read($sLocalConfigPath);
// Get values to ask
$aValuesToAsk = ['%DATASOURCE_HOSTNAME%' => ['label' => 'database host', 'default' => 'localhost', 'mandatory' => true], '%DATASOURCE_DATABASE%' => ['label' => 'database name', 'default' => 'deployment', 'mandatory' => true], '%DATASOURCE_USERNAME%' => ['label' => 'database user name', 'mandatory' => true], '%DATASOURCE_PASSWORD%' => ['label' => 'database user password', 'mandatory' => false], '%BUILD_nb_backups_per_project%' => ['label' => 'number of backups kept per project', 'default' => '2', 'mandatory' => true], '%BUILD_BIN_COMPOSER%' => ['label' => 'full path to composer binary', 'default' => '/usr/bin/composer', 'mandatory' => true, 'binary' => ['check_command' => '%s -v', 'name' => 'composer']], '%BUILD_BIN_GIT%' => ['label' => 'full path to git binary', 'default' => '/usr/local/bin/git', 'mandatory' => true, 'binary' => ['check_command' => '%s --version', 'name' => 'git']], '%BUILD_BIN_PHP%' => ['label' => 'full path to php binary', 'default' => '/usr/bin/php', 'mandatory' => true, 'binary' => ['check_command' => '%s -v', 'name' => 'php']]];
// Loop through values to ask
foreach ($aValuesToAsk as $sKeyToReplace => $aValueToAsk) {
// Initialize
$sDefault = isset($aValueToAsk['default']) ? $aValueToAsk['default'] : null;
// Binary path
if (isset($aValueToAsk['binary'])) {
// Get value
$sValue = ExtendedComposer::askBinaryPath($oEvent, $aValueToAsk['label'], $aValueToAsk['binary']['name'], $aValueToAsk['binary']['check_command'], $sDefault, true, $aValueToAsk['mandatory']);
} else {
// Get value
$sValue = ExtendedComposer::askString($oEvent, $aValueToAsk['label'], $sDefault, $aValueToAsk['mandatory']);
}
// Replace config
$sLocalConfigContent = preg_replace(sprintf('/%s/', ExtendedString::pregQuote($sKeyToReplace)), $sValue, $sLocalConfigContent);
}
// Put local config content
$oFileManager->write($sLocalConfigContent, $sLocalConfigPath, WriteMethod::OVERWRITE);
// Output
$oEvent->getIO()->write(sprintf("\n%s: OK", $sOutput));
// Get config
$aConfig = ExtendedArray::extendWithDefaultValues(require __DIR__ . '/../../../app/config/local.php', require __DIR__ . '/../../../app/config/global.php');
// Build extended PDO
$aDatasourceConfig = $aConfig['datasources']['write']['deployment'];
$oExtendedPDO = new ExtendedPdo("mysql:host={$aDatasourceConfig['hostname']};" . "dbname={$aDatasourceConfig['database']};", $aDatasourceConfig['username'], $aDatasourceConfig['password'], $aConfig['pdo_options']);
// Execute SQL commands
$sOutput = 'Execute SQL commands';
$sErrorMessage = '';
try {
// Get SQL files
$aSQLFiles = $oFileManager->explore(__DIR__ . '/../../../sql', OrderField::BASENAME);
// Loop through SQL files
/** @var $oSQLFile \Asticode\FileManager\Entity\File */
foreach ($aSQLFiles as $oSQLFile) {
// Split statements
$aStatements = explode(';', $oFileManager->read($oSQLFile->getPath()));
// Loop through statements
foreach ($aStatements as $sStatement) {
if ($sStatement !== '') {
$oExtendedPDO->exec($sStatement);
}
}
}
} catch (Exception $oException) {
// Get error message
$sErrorMessage = $oException->getMessage();
}
// Error
if ($sErrorMessage === '') {
// Output
$oEvent->getIO()->write(sprintf("\n%s: OK", $sOutput));
// Create dirs
$sOutput = 'Create directories';
$aDirsToCreate = ['backups', 'gits', 'tmp'];
// Check keys exist
ExtendedArray::checkRequiredKeys($aConfig['build']['dirs'], $aDirsToCreate);
// Loop through dirs to create
foreach ($aDirsToCreate as $sDirToCreate) {
// Create dir
$oFileManager->createDir($aConfig['build']['dirs'][$sDirToCreate]);
}
// Output
$oEvent->getIO()->write(sprintf("\n%s: OK", $sOutput));
// Conclude
$oEvent->getIO()->write("\n\nInstallation successful!\n\n\nYou can now add a new project with\n\n" . " \$ <your path>/app/console project:add\n\nOr remove a project with\n\n" . " \$ <your path>/app/console project:remove\n");
} else {
// Output
$oEvent->getIO()->write(sprintf("\n%s: KO\n", $sOutput));
// Throw exception
throw new RuntimeException($sErrorMessage);
}
} else {
// Output
$oEvent->getIO()->write(sprintf("\n%s: KO\n", $sOutput));
// Throw exception
throw new RuntimeException($sErrorMessage);
}
}
<?php
namespace database;
use Aura\SqlQuery\QueryFactory;
use Aura\Sql\ExtendedPdo;
$query_factory = new QueryFactory('sqlite');
$pdo = new ExtendedPdo('sqlite:database.sqlite');
$pdo->connect();
/**
* destroy connection
*
* @throws \Aura\Sql\Exception\CannotDisconnect
*/
public function __destruct()
{
self::$_connection->disconnect();
}
<?php
require dirname(__FILE__) . '/../vendor/autoload.php';
use Aura\Sql\ExtendedPdo;
use GuzzleHttp\Client;
use SparkPost\SparkPost;
use Http\Adapter\Guzzle6\Client as GuzzleAdapter;
$dotenv = new Dotenv\Dotenv(dirname(__DIR__));
$dotenv->load();
$httpAdapter = new GuzzleAdapter(new Client());
$sparky = new SparkPost($httpAdapter, ['key' => getenv('SPARKPOST_API_KEY'), 'async' => false]);
$htmlTpl = file_get_contents(dirname(__DIR__) . "/templates/email/rewards-sizing-form-2016.tpl");
$pdo = new ExtendedPdo(getenv('PDO_DB_CONNECTION'), getenv('PDO_DB_USERNAME'), getenv('PDO_DB_PASSWORD'));
$stm = "SELECT * FROM `indiegogo` WHERE `Perk ID` = 3613098 OR `Perk ID` = 3633662";
$sth = $pdo->query($stm);
$rs = $sth->fetchAll(PDO::FETCH_ASSOC);
if (!$rs) {
throw new Exception('No results for matching perk customers');
}
foreach ($rs as $row) {
echo "Sending to {$row['Email']}...\n";
$results = $sparky->transmissions->post(['content' => ['from' => ['name' => 'OSMIHelp', 'email' => '*****@*****.**'], 'subject' => 'OSMI Shirts and Hoodies: We need your size info!', 'html' => $htmlTpl], 'substitution_data' => ['name' => $row['Name'], 'email' => $row['Email']], 'recipients' => [['address' => ['name' => $row['Name'], 'email' => $row['Email']]]], 'cc' => null, 'bcc' => null]);
}
echo "done.\n";