public function testComplexConditions()
{
$qc = new QC('users');
$qc->and('age < :d', 10)->use('name')->indexBy('id')->orderBy('id DESC')->limit(1, 2)->groupBy('age');
$this->assertEquals('SELECT * FROM `users` WHERE ((age < 10)) GROUP BY `age` ORDER BY id DESC LIMIT 1, 2', $qc->getSQL(), '');
$sql = QC::create('users')->where('age > :d', 12)->update('title = :s and id = :d', 'new title', 1)->getSQL();
$this->assertEquals('UPDATE `users` SET title = \'new title\' and id = 1 WHERE ((age > 12))', $sql, 'multiple update with parameters');
}
protected static function putTestContent()
{
QC::executeSQL('DROP TABLE IF EXISTS products');
$storagePath = __DIR__ . '/storage/';
$mo = ModelOperator::getInstance($storagePath);
$mo->generateBasicStructure('Product');
$ms = new ModelStructure('Product');
$ms->addColumn('id_brand', array('type' => 'int(11) unsigned'));
$ms->saveStructure();
$mo->generateAllModelClasses();
$mo->updateDBForAllModels();
require_once $storagePath . 'bases/BaseProduct.php';
require_once $storagePath . 'classes/Product.php';
QC::create('products')->insert(array('title' => 'Macbook air'))->execute();
$testProductFileContent = <<<TEXT
<?php
class ProductCustom extends BaseProduct {
private \$_internalManufacturer = 'solve';
public function getManufacturer() { return \$this->_internalManufacturer; }
public function setManufacturer(\$value) { \$this->_internalManufacturer = \$value; return \$this; }
}
TEXT;
file_put_contents($storagePath . 'classes/ProductCustom.php', $testProductFileContent);
require_once $storagePath . 'classes/ProductCustom.php';
$mo->setStructureForModel('ProductCustom', $mo->generateBasicStructure('Product', false), false);
}
/**
* Update database and models
*/
public function updateAllAction()
{
QC::executeSQL('SET FOREIGN_KEY_CHECKS = 0');
ModelOperator::getInstance(DC::getEnvironment()->getUserClassesRoot() . 'db/')->updateDBForAllModels();
ModelOperator::getInstance()->generateAllModelClasses();
$this->writeln('DB updated');
}
public static function setUpBeforeClass()
{
DatabaseService::configProfile(array('Product' => 'root', 'pass' => 'root'));
self::$_storagePath = __DIR__ . '/storage/';
DBOperator::getInstance()->dropDB(self::$_DBName)->createDB(self::$_DBName)->useDB(self::$_DBName);
FSService::unlinkRecursive(self::$_storagePath);
QC::executeSQL('SET FOREIGN_KEY_CHECKS = 0');
call_user_func(array(get_called_class(), 'putTestContent'));
}
protected static function putTestContent()
{
QC::executeSQL('SET FOREIGN_KEY_CHECKS = 0');
QC::executeSQL('DROP TABLE IF EXISTS brands');
$storagePath = __DIR__ . '/storage/';
$mo = ModelOperator::getInstance($storagePath);
$mo->generateBasicStructure('Brand');
ModelStructure::getInstanceForModel('Brand')->addAbility('slug')->saveStructure();
$mo->generateAllModelClasses();
require_once $storagePath . 'bases/BaseBrand.php';
require_once $storagePath . 'classes/Brand.php';
$mo->updateDBForAllModels();
}
public function defaultAction()
{
$this->view->name = 'Alexandr';
$this->view->city = 'Kiev';
var_dump(QC::create('users')->execute());
// $this->view->setVar('name', 'AlexandrHTML', View::FORMAT_HTML);
// $this->view->setNoLayout();
// $this->view->setStandaloneTemplate('index/default');
// echo "index default \n";
// $this->forwardToRoute('test');
// $this->redirectToUri('about/');
// $this->redirectSelf();
// var_dump($this->view->fetchTemplate('index/default'));die();
}
protected static function putTestContent()
{
QC::executeSQL('SET FOREIGN_KEY_CHECKS = 0');
QC::executeSQL('DROP TABLE IF EXISTS brands');
$storagePath = __DIR__ . '/storage/';
$mo = ModelOperator::getInstance($storagePath);
$mo->generateBasicStructure('User');
ModelStructure::getInstanceForModel('User')->dropColumn('title')->addColumn('name', array('type' => 'varchar(255)', 'validation' => array('CustomName' => array('class' => 'Solve\\Database\\Tests\\CustomNameValidationRule', 'error' => 'invalid name')), 'process' => array('trim')))->addColumn('email', array('type' => 'varchar(255)'))->saveStructure();
$mo->generateDataProcessorRules('User');
$mo->generateAllModelClasses();
require_once $storagePath . 'bases/BaseUser.php';
require_once $storagePath . 'classes/User.php';
$mo->updateDBForAllModels();
}
protected static function putTestContent()
{
QC::executeSQL('DROP TABLE IF EXISTS products');
$storagePath = __DIR__ . '/storage/';
$mo = ModelOperator::getInstance($storagePath);
$mo->generateBasicStructure('Product');
$ms = new ModelStructure('Product');
$ms->addColumn('info', array('type' => 'array'));
$ms->saveStructure();
$mo->generateAllModelClasses();
$mo->updateDBForAllModels();
require_once $storagePath . 'bases/BaseProduct.php';
require_once $storagePath . 'classes/Product.php';
QC::create('products')->insert(array('title' => 'Macbook air'))->execute();
}
protected static function putTestContent()
{
QC::executeSQL('SET FOREIGN_KEY_CHECKS = 0');
QC::executeSQL('DROP TABLE IF EXISTS brands');
$storagePath = __DIR__ . '/storage/';
$mo = ModelOperator::getInstance($storagePath);
$mo->generateBasicStructure('Brand');
ModelStructure::getInstanceForModel('Brand')->addAbility('files', array('logo' => array('name' => 'original', 'multiple' => true), 'info' => array(), 'avatar' => array('sizes' => array('small' => array('size' => '100x100', 'method' => 'fitOut')))))->saveStructure();
$mo->generateAllModelClasses();
require_once $storagePath . 'bases/BaseBrand.php';
require_once $storagePath . 'classes/Brand.php';
$webRoot = __DIR__ . '/upload/';
FilesAbility::setBaseStoreLocation($webRoot);
FSService::setWebRoot($webRoot);
FSService::unlinkRecursive($webRoot);
$mo->updateDBForAllModels();
}
public function testBasicOperations()
{
$data = QC::create('users')->execute();
$this->assertEmpty($data, 'Empty select');
$id = QC::create('users')->insert(array('age' => 10, 'name' => 'Alexandr', 'position' => 'developer'))->execute();
$this->assertEquals(1, $id, 'Insert returns id 1');
QC::create('users')->insert(array('age' => 11, 'name' => 'Sergey', 'position' => 'developer'))->execute();
$data = QC::create('users')->select('id, name')->execute();
$this->assertEquals(array(array('id' => '1', 'name' => 'Alexandr'), array('id' => '2', 'name' => 'Sergey')), $data, 'select fields of two simple rows');
$data = QC::create('users')->select('name')->where('id = 1')->executeOne();
$this->assertEquals(array('name' => 'Alexandr'), $data, 'Select one value');
$data = QC::create('users')->select('id, name, position')->indexBy('id')->foldBy('position')->use('name')->execute();
$this->assertEquals(array('developer' => array('1' => 'Alexandr', '2' => 'Sergey')), $data, 'Select with foldBy, indexBy and use modifiers');
$data = QC::create()->rawSelect('select id,name from users')->indexBy('id')->use('name')->execute();
$this->assertEquals(array(1 => 'Alexandr', 2 => 'Sergey'), $data, 'Custom select with modifiers');
$countDeleted = QC::create('users')->delete('id < :d', 3)->execute();
$this->assertEquals(2, $countDeleted, 'Deleted 2 items');
}
public function checkCredentials($params, $modelName = null)
{
if (empty($modelName)) {
$modelName = $this->_userModelName;
}
if (!empty($params['password'])) {
$params['password'] = md5($params['password']);
}
/**
* @var Model $user
*/
$user = call_user_func(array($modelName, 'loadOne'), QC::createFromCondition($params));
if ($user->isExists()) {
$this->_storage['user'] = $user->getArray();
return true;
} else {
return false;
}
}
protected static function putTestContent()
{
QC::executeSQL('SET FOREIGN_KEY_CHECKS = 0');
QC::executeSQL('DROP TABLE IF EXISTS products');
QC::executeSQL('DROP TABLE IF EXISTS brands');
QC::executeSQL('DROP TABLE IF EXISTS brands_translate');
$storagePath = __DIR__ . '/storage/';
$mo = ModelOperator::getInstance($storagePath);
$mo->generateBasicStructure('Product');
$mo->generateBasicStructure('Brand');
ModelStructure::getInstanceForModel('Product')->addColumn('id_brand', array('type' => 'int(11) unsigned'))->addRelation('brand')->saveStructure();
ModelStructure::getInstanceForModel('Brand')->addRelation('products')->addAbility('translate', array('columns' => array('title')))->saveStructure();
$mo->generateAllModelClasses();
require_once $storagePath . 'bases/BaseProduct.php';
require_once $storagePath . 'bases/BaseBrand.php';
require_once $storagePath . 'classes/Product.php';
require_once $storagePath . 'classes/Brand.php';
ModelOperator::getAbilityInstanceForModel('Brand', 'Translate')->cleanup();
$mo->updateDBForAllModels();
}
public function testBasic()
{
$mo = ModelOperator::getInstance(self::$_storagePath);
$this->assertEmpty($mo->getModelStructure('User'), 'Empty structure returns array()');
$mo->generateBasicStructure('User');
$data = $mo->getModelStructure('User');
$this->assertEquals(array('table' => 'users', 'columns' => array('id' => array('type' => 'int(11) unsigned', 'auto_increment' => true), 'title' => array('type' => 'varchar(255)')), 'indexes' => array('primary' => array('columns' => array('id')))), $data, 'Basic structure generator is ok');
$mo->saveModelStructure('User');
$this->assertFileExists(self::$_storagePath . 'structure/User.yml', 'Save model structure is ok');
$mo->generateModelClass('User');
require_once __DIR__ . '/storage/bases/BaseUser.php';
require_once __DIR__ . '/storage/classes/User.php';
$this->assertFileExists(self::$_storagePath . 'bases/BaseUser.php', 'BaseModel generated');
$this->assertFileExists(self::$_storagePath . 'classes/User.php', 'Model generated');
$this->assertTrue(class_exists('\\User'), 'Generated model class is available');
QC::executeSQL('DROP TABLE IF EXISTS users');
$mo->updateDBForModel('User');
$data = DBOperator::getInstance()->getTableStructure('users');
$this->assertNotEmpty($data, 'Table generation is ok');
}
protected static function putTestContent()
{
QC::executeSQL('DROP TABLE IF EXISTS products');
QC::executeSQL('DROP TABLE IF EXISTS categories');
$storagePath = __DIR__ . '/storage/';
$mo = ModelOperator::getInstance($storagePath);
$mo->generateBasicStructure('Product');
$mo->generateBasicStructure('Category');
ModelStructure::getInstanceForModel('Product')->addColumn('id_category', array('type' => 'int(11) unsigned'))->addRelation('category')->addRelation('category_title', array('table' => 'categories', 'fields' => array('title'), 'use' => 'title', 'type' => 'many_to_one', 'local_field' => 'id_category'))->saveStructure();
ModelStructure::getInstanceForModel('Category')->addRelation('products')->saveStructure();
$mo->generateAllModelClasses();
require_once $storagePath . 'bases/BaseProduct.php';
require_once $storagePath . 'bases/BaseCategory.php';
require_once $storagePath . 'classes/Product.php';
require_once $storagePath . 'classes/Category.php';
$mo->updateDBForAllModels();
QC::create('products')->insert(array('title' => 'Macbook Air', 'id_category' => 1))->execute();
QC::create('products')->insert(array('title' => 'Macbook Pro', 'id_category' => 1))->execute();
QC::create('products')->insert(array('title' => 'iMac 27"', 'id_category' => 2))->execute();
QC::create('categories')->insert(array('title' => 'Notebooks'))->execute();
QC::create('categories')->insert(array('title' => 'Computers'))->execute();
$categoriesCollectionText = <<<TEXT
<?php
use Solve\\Database\\Models\\ModelCollection;
class CategoriesCollection extends ModelCollection {
public function getProductsCount() {
return 12;
}
}
TEXT;
file_put_contents($storagePath . 'classes/CategoriesCollection.php', $categoriesCollectionText);
require_once $storagePath . 'classes/CategoriesCollection.php';
}
public function delete()
{
QC::create($this->_tableName)->delete(array($this->_primaryKey => $this->getIDs()))->execute();
}
protected function processRequestFilters(QC $qc)
{
// paging
$page = $this->request->getVar('page', 1);
$count = $this->request->getVar('count', 10);
$qc->limit(($page - 1) * $count, $count);
// sorting
$sorting = $this->request->getVar('sorting', null);
if (is_array($sorting)) {
foreach ($sorting as $key => $type) {
$qc->orderBy($key . ' ' . ($type == 'asc' ? 'ASC' : 'DESC'));
}
}
$this->setData(array('total' => QC::create('users')->select('count(*) cnt')->use('cnt')->executeOne(), 'page' => $page, 'count' => $count), 'paging');
$this->setData($sorting, 'sorting');
}
public function delete()
{
if ($this->isNew()) {
return false;
}
$this->_preDelete();
unset($this);
return QC::create($this->_tableName)->delete(array($this->_primaryKey => $this->getID()))->execute();
}
public function preDelete($caller)
{
$ids = ModelOperator::getIDs($caller);
QC::create($this->_tableName)->delete(array('id_object' => $ids))->execute();
}
/**
* Updates many to many tables
* @param $info
* @return mixed
*/
public function updateManyTable($info)
{
$structure = array('table' => $info['manyTable'], 'columns' => array(), 'indexes' => array(), 'constraints' => array());
$localName = Inflector::singularize($info['localTable']);
$foreignName = Inflector::singularize($info['foreignTable']);
$structure['columns']['id'] = array('type' => 'int(11) unsigned', 'auto_increment' => true);
$structure['columns']['id_' . $localName] = 'int(11) unsigned';
$structure['columns']['id_' . $foreignName] = 'int(11) unsigned';
$structure['indexes']['primary'] = array('columns' => array('id'), 'type' => 'primary');
$structure['indexes']['unique_id_' . $localName . '_id_' . $foreignName] = array('columns' => array('id_' . $localName, 'id_' . $foreignName), 'type' => 'unique');
$foreignKeysInfo = array('local_table' => $info['manyTable'], 'foreign_table' => $info['localTable'], 'local_field' => 'id_' . $localName, 'foreign_field' => $info['foreignKey']);
$structure['constraints'][$this->generateForeignKeyName($foreignKeysInfo)] = $foreignKeysInfo;
$foreignKeysInfo = array('local_table' => $info['manyTable'], 'foreign_table' => $info['foreignTable'], 'local_field' => 'id_' . $foreignName, 'foreign_field' => $info['foreignKey']);
$structure['constraints'][$this->generateForeignKeyName($foreignKeysInfo)] = $foreignKeysInfo;
$diffs = $this->getDifferenceSQL($structure, $info['manyTable']);
if ($diffs['result'] === true) {
QC::executeSQL('SET FOREIGN_KEY_CHECKS = 0');
if (!empty($diffs['sql']['ADD'])) {
try {
QC::executeArrayOfSQL($diffs['sql']['ADD']);
} catch (\Exception $e) {
var_dump($e->getMessage());
die;
}
}
}
return $diffs['result'];
}
private function processModifiers(QC $q)
{
$sql = '';
if ($groupBy = $q->getModifier('groupBy')) {
$sql .= ' GROUP BY ' . $this->escapeSqlName($groupBy[0]);
}
if ($orderBy = $q->getModifier('orderBy')) {
$sql .= ' ORDER BY ' . $this->escapeSqlName($orderBy[0]);
}
if ($q->getModifier('one')) {
$sql .= ' LIMIT 1';
} elseif ($limit = $q->getModifier('limit')) {
if (count($limit) > 1) {
$limit[0] = implode(', ', $limit);
}
$sql .= ' LIMIT ' . $limit[0];
}
return $sql;
}
/**
* @param Model|ModelCollection $caller
* @param $relationName
* @return $this
* @throws \Exception
*/
public function _loadRelated($caller, $relationName)
{
/**
* @var $localKey
* @var $localField
* @var $foreignKey
* @var $foreignField
* @var $localTable
* @var $foreignTable
* @var $manyTable
* @var $relationType
* @var $relatedModelName
* @var $hydration
* @var $fieldsToRetrieve
* @var $relationToMany
*/
$varsToExport = ModelOperator::calculateRelationVariables($caller->_getName(), $relationName);
extract($varsToExport);
$foreignIds = array();
$idsMap = array();
$originalCaller = null;
if ($caller instanceof Model && $caller->_hasCollectionReference()) {
$caller = $caller->_getCollectionReference();
}
if ($relationType == 'many_to_one') {
$foreignIds = ModelOperator::getFieldArray($caller, $localField);
} elseif ($relationToMany) {
$localIds = ModelOperator::getIDs($caller);
$tableToSelect = $foreignTable;
$fieldToSelect = $foreignKey;
if ($relationType == 'many_to_many') {
$tableToSelect = $manyTable;
$fieldToSelect = $localField;
}
$idsMap = QC::create($tableToSelect)->where(array($tableToSelect . '.' . $foreignField => $localIds))->select($fieldToSelect . ', ' . $foreignField)->foldBy($foreignField)->use($fieldToSelect)->execute();
foreach ($idsMap as $value) {
$foreignIds = array_merge($foreignIds, $value);
}
}
$foreignIds = array_unique($foreignIds);
if (empty($foreignIds)) {
return false;
} elseif ($foreignKey == 'id') {
$ids = array();
foreach ($foreignIds as $key => $value) {
$ids[] = intval($value);
}
$foreignIds = $ids;
}
/**
* @var ModelCollection $relatedCollection
*/
$relatedCollection = call_user_func(array($relatedModelName, 'loadList'), $foreignIds);
$callerObjects = $caller instanceof ModelCollection ? $caller : array($caller);
/**
* @var Model $object
*/
foreach ($callerObjects as $object) {
if (substr($relationType, '-6') == 'to_one') {
$relationValue = $relatedCollection->getOneByPK($object[$localField]);
} else {
$relationValue = $relatedCollection->getSubCollectionByPKs($idsMap[$object->getID()]);
}
$object->_setRawFieldValue($relationName, $relationValue);
}
return $this;
}
/**
* Load data from YML files into database with replacing
* @param string|array $models
*/
public function dataLoad($models = null)
{
if (!empty($models)) {
if (!is_array($models)) {
$models = array($models);
}
} else {
$models = array_keys($this->_structures);
}
$data_dir = $this->_storagePath . 'data/';
foreach ($models as $model) {
$model = ucfirst($model);
$file_name = $data_dir . strtolower($model) . '.yml';
if (!isset($this->_structures[$model]) || !is_file($file_name)) {
continue;
}
$data = Yaml::parse(file_get_contents($file_name));
if (!is_array($data)) {
continue;
}
QC::executeSQL('SET FOREIGN_KEY_CHECKS=0');
QC::executeSQL('TRUNCATE `' . $this->_structures[$model]['table'] . '`');
QC::create($this->_structures[$model]['table'])->insert($data)->execute();
}
}
public function importQC(QC $qc)
{
if ($modifiers = $qc->getModifiers()) {
foreach ($modifiers as $modifierName => $params) {
$this->_params['modifiers'][$modifierName] = $params;
}
}
return $this;
}