/**
* Build the schemadiff with Doctrine
*
* @param \Doctrine\DBAL\Schema\Schema $leftDbalSchemas
* @param \Doctrine\DBAL\Schema\Schema $rightdbalSchemas
* @param array $options
*
* @return \Doctrine\DBAL\Schema\SchemaDiff
*/
private function buildSchemaDiff(\Doctrine\DBAL\Schema\Schema $leftDbalSchemas, \Doctrine\DBAL\Schema\Schema $rightdbalSchemas, array $options)
{
// Compare Schemas
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($leftDbalSchemas, $rightdbalSchemas);
// Create missing tables ?
if ($options['create_missing_tables'] === false) {
$schemaDiff->newTables = [];
}
// Drop existing tables ?
if ($options['delete_extra_tables'] === false) {
$schemaDiff->removedTables = [];
}
// Other Options
if ($options['alter_table_options'] === false) {
$schemaDiff->changedTables = [];
} else {
// Remove all Columns alteration in changedtables
if ($options['alter_columns'] === false) {
foreach ($schemaDiff->changedTables as $name => $props) {
$schemaDiff->changedTables[$name]->addedColumns = $schemaDiff->changedTables[$name]->changedColumns = $schemaDiff->changedTables[$name]->removedColumns = $schemaDiff->changedTables[$name]->renamedColumns = [];
}
}
// Remove all index definitions in changedtables
if ($options['alter_indexes'] === false) {
foreach ($schemaDiff->changedTables as $name => $props) {
$schemaDiff->changedTables[$name]->addedIndexes = $schemaDiff->changedTables[$name]->changedIndexes = $schemaDiff->changedTables[$name]->removedIndexes = $schemaDiff->changedTables[$name]->renamedIndexes = [];
}
}
// Remove all FK definitions in changedtables
if ($options['alter_foreign_keys'] === false) {
foreach ($schemaDiff->newTables as $name => $props) {
$fks = $schemaDiff->newTables[$name]->getForeignKeys();
foreach ($fks as $fkName => $dbalFk) {
$schemaDiff->newTables[$name]->removeForeignKey($fkName);
}
}
foreach ($schemaDiff->changedTables as $name => $props) {
$schemaDiff->changedTables[$name]->addedForeignKeys = $schemaDiff->changedTables[$name]->changedForeignKeys = $schemaDiff->changedTables[$name]->removedForeignKeys = [];
}
$schemaDiff->orphanedForeignKeys = [];
}
}
return $schemaDiff;
}
protected function execute(InputInterface $input, OutputInterface $output)
{
$db = \Database::connection();
$em = $db->getEntityManager();
$cacheDriver = $em->getConfiguration()->getMetadataCacheImpl();
$cacheDriver->flushAll();
$tool = new \Doctrine\ORM\Tools\SchemaTool($em);
$schemas = [];
/**
* @var $sm MySqlSchemaManager
*/
$sm = $db->getSchemaManager();
$dbSchema = $sm->createSchema();
// core xml tables
$schemas[] = Schema::getCoreXMLSchema();
// core entities
$sm = new DatabaseStructureManager($em);
$entities = $sm->getMetadatas();
$schemas[] = $tool->getSchemaFromMetadata($entities);
// core, application and package block types
$env = Environment::get();
$list = new BlockTypeList();
$list->includeInternalBlockTypes();
foreach ($list->get() as $bt) {
$r = $env->getRecord(DIRNAME_BLOCKS . '/' . $bt->getBlockTypeHandle() . '/' . FILENAME_BLOCK_DB, $bt->getPackageHandle());
if ($r->exists()) {
$parser = Schema::getSchemaParser(simplexml_load_file($r->file));
$parser->setIgnoreExistingTables(false);
$schemas[] = $parser->parse($db);
}
}
// packages
$packages = Package::getInstalledList();
foreach ($packages as $pkg) {
$xmlFile = $pkg->getPackagePath() . '/' . FILENAME_BLOCK_DB;
if (file_exists($xmlFile)) {
$parser = Schema::getSchemaParser(simplexml_load_file($xmlFile));
$parser->setIgnoreExistingTables(false);
$schemas[] = $parser->parse($db);
}
}
// Finalize output.
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$saveQueries = array();
foreach ($schemas as $schema) {
$schemaDiff = $comparator->compare($dbSchema, $schema);
$saveQueries = array_merge($saveQueries, $schemaDiff->toSaveSql($db->getDatabasePlatform()));
}
$saveQueries = $this->filterQueries($saveQueries);
if (count($saveQueries)) {
$output->writeln(t2('%s query found', '%s queries found', count($saveQueries)));
$i = 1;
foreach ($saveQueries as $query) {
$output->writeln(sprintf('%s: %s', $i, $query));
$i++;
}
} else {
$output->writeln(t('No differences found between schema and database.'));
}
}
private function createDatabase($input, $output, $path_schema)
{
$fs = new Filesystem();
$dateTime = new \DateTime();
$output->write(PHP_EOL . " Actualizando la base de datos..." . PHP_EOL . PHP_EOL);
if (!is_file($path_schema . '/schema.php')) {
$output->writeln(" <error>ATENCION: El esquema no fue creado.</error>" . PHP_EOL);
return;
}
$DriverManager = Service::get('database.manager')->getConnectionManager()->getConnection();
$sm = $DriverManager->getSchemaManager();
// Obtiene el esquema de la base de datos.
$schema_current = $sm->createSchema();
// Se Obtiene el objeto del esquema creado.
$schema = (include $path_schema . '/schema.php');
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compareSchemas($schema_current, $schema);
$queries = $schemaDiff->toSql($DriverManager->getDatabasePlatform());
if (count($queries) == 0) {
$output->writeln(PHP_EOL . " <info>No hay nada para actualizar.</info>");
return;
}
$_q = "set foreign_key_checks = 0;";
$DriverManager->query($_q);
$output->writeln(PHP_EOL . " {$_q}" . PHP_EOL);
foreach ($queries as $query) {
$DriverManager->query($query);
$output->writeln(" - {$query}");
}
$_q = "set foreign_key_checks = 1;";
$output->writeln(PHP_EOL . " {$_q}" . PHP_EOL);
$DriverManager->query($_q);
$output->writeln("<info>La base de datos fue actualizada correctamente.</info>");
}
/**
* @param StandardSearchIndexerInterface $category
* @param Key $key
* @param $previousHandle
*/
public function updateSearchIndexKeyColumns(CategoryInterface $category, AttributeKeyInterface $key, $previousHandle = null)
{
$controller = $key->getController();
/*
* Added this for some backward compatibility reason – but it's obviously not
* right because it makes it so no search index columns get created.
if (!$previousHandle) {
$previousHandle = $key->getAttributeKeyHandle();
}*/
if ($key->getAttributeKeyHandle() == $previousHandle || $key->isAttributeKeySearchable() == false || $category->getIndexedSearchTable() == false || $controller->getSearchIndexFieldDefinition() == false) {
return false;
}
$fields = array();
$dropColumns = array();
$definition = $controller->getSearchIndexFieldDefinition();
$sm = $this->connection->getSchemaManager();
$toTable = $sm->listTableDetails($category->getIndexedSearchTable());
if ($previousHandle) {
if (isset($definition['type'])) {
$dropColumns[] = 'ak_' . $previousHandle;
} else {
foreach ($definition as $name => $column) {
$dropColumns[] = 'ak_' . $previousHandle . '_' . $name;
}
}
}
if (isset($definition['type'])) {
if (!$toTable->hasColumn('ak_' . $key->getAttributeKeyHandle())) {
$fields[] = array('name' => 'ak_' . $key->getAttributeKeyHandle(), 'type' => $definition['type'], 'options' => $definition['options']);
}
} else {
foreach ($definition as $name => $column) {
if (!$toTable->hasColumn('ak_' . $key->getAttributeKeyHandle() . '_' . $name)) {
$fields[] = array('name' => 'ak_' . $key->getAttributeKeyHandle() . '_' . $name, 'type' => $column['type'], 'options' => $column['options']);
}
}
}
$fromTable = $sm->listTableDetails($category->getIndexedSearchTable());
$parser = new \Concrete\Core\Database\Schema\Parser\ArrayParser();
$comparator = new \Doctrine\DBAL\Schema\Comparator();
if ($previousHandle != false) {
foreach ($dropColumns as $column) {
$toTable->dropColumn($column);
}
}
$toTable = $parser->addColumns($toTable, $fields);
$diff = $comparator->diffTable($fromTable, $toTable);
if ($diff !== false) {
$sql = $this->connection->getDatabasePlatform()->getAlterTableSQL($diff);
$arr = array();
foreach ($sql as $q) {
$arr[] = $q;
$this->connection->exec($q);
}
}
}
public static function refreshCoreXMLSchema($tables = array())
{
$db = \Database::get();
$toSchema = static::getCoreXMLSchema($tables);
$fromSchema = $db->getSchemaManager()->createSchema();
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($fromSchema, $toSchema);
$saveQueries = $schemaDiff->toSaveSql($db->getDatabasePlatform());
foreach ($saveQueries as $query) {
$db->query($query);
}
}
/**
* Check diffrent in schemas (current and modified by migrate script) and executed sql
*
* @return Doctrine\DBAL\Schema\Schema $schema
*/
protected function updateSchema($schema)
{
$manager = $this->container->getDoctrine()->getEntityManager();
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$fromSchema = $this->schema;
$schemaDiff = $comparator->compare($fromSchema, $schema);
$platform = $manager->getConnection()->getDatabasePlatform();
$sqls = $schemaDiff->toSql($platform);
foreach ($sqls as $sql) {
$manager->getConnection()->exec($sql);
}
}
public function testUniquePrimaryKey()
{
$keyTable = new Table("foo");
$keyTable->addColumn("bar", "integer");
$keyTable->addColumn("baz", "string");
$keyTable->setPrimaryKey(array("bar"));
$keyTable->addUniqueIndex(array("baz"));
$oldTable = new Table("foo");
$oldTable->addColumn("bar", "integer");
$oldTable->addColumn("baz", "string");
$c = new \Doctrine\DBAL\Schema\Comparator();
$diff = $c->diffTable($oldTable, $keyTable);
$sql = $this->_platform->getAlterTableSQL($diff);
$this->assertEquals(array("ALTER TABLE foo ADD PRIMARY KEY (bar)", "CREATE UNIQUE INDEX UNIQ_8C73652178240498 ON foo (baz)"), $sql);
}
public function assertCreatedSchemaNeedsNoUpdates($classes)
{
$classMetadata = array();
foreach ($classes as $class) {
$classMetadata[] = $this->_em->getClassMetadata($class);
}
$this->schemaTool->dropDatabase();
$this->schemaTool->createSchema($classMetadata);
$sm = $this->_em->getConnection()->getSchemaManager();
$fromSchema = $sm->createSchema();
$toSchema = $this->schemaTool->getSchemaFromMetadata($classMetadata);
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($fromSchema, $toSchema);
$sql = $schemaDiff->toSql($this->_em->getConnection()->getDatabasePlatform());
$this->assertEquals(0, count($sql), "SQL: " . implode(PHP_EOL, $sql));
}
/**
* @group DBAL-37
*/
public function testAlterTableAutoIncrementDrop()
{
$tableFrom = new \Doctrine\DBAL\Schema\Table('autoinc_table_drop');
$column = $tableFrom->addColumn('id', 'integer');
$column->setAutoincrement(true);
$this->_sm->createTable($tableFrom);
$tableFrom = $this->_sm->listTableDetails('autoinc_table_drop');
$this->assertTrue($tableFrom->getColumn('id')->getAutoincrement());
$tableTo = new \Doctrine\DBAL\Schema\Table('autoinc_table_drop');
$column = $tableTo->addColumn('id', 'integer');
$c = new \Doctrine\DBAL\Schema\Comparator();
$diff = $c->diffTable($tableFrom, $tableTo);
$this->assertType('Doctrine\\DBAL\\Schema\\TableDiff', $diff, "There should be a difference and not false being returned from the table comparison");
$this->assertEquals(array("ALTER TABLE autoinc_table_drop ALTER id DROP DEFAULT"), $this->_conn->getDatabasePlatform()->getAlterTableSQL($diff));
$this->_sm->alterTable($diff);
$tableFinal = $this->_sm->listTableDetails('autoinc_table_drop');
$this->assertFalse($tableFinal->getColumn('id')->getAutoincrement());
}
public static function refreshDatabase(Package $package)
{
if (version_compare(APP_VERSION, '5.7.4', '<')) {
if (file_exists($package->getPackagePath() . '/' . FILENAME_PACKAGE_DB)) {
$db = Database::get();
$db->beginTransaction();
$parser = Schema::getSchemaParser(simplexml_load_file($package->getPackagePath() . '/' . FILENAME_PACKAGE_DB));
$parser->setIgnoreExistingTables(false);
$toSchema = $parser->parse($db);
$fromSchema = $db->getSchemaManager()->createSchema();
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($fromSchema, $toSchema);
$saveQueries = $schemaDiff->toSaveSql($db->getDatabasePlatform());
foreach ($saveQueries as $query) {
$db->query($query);
}
$db->commit();
}
}
}
public function testDiffTableBug()
{
$schema = new Schema();
$table = $schema->createTable('diffbug_routing_translations');
$table->addColumn('id', 'integer');
$table->addColumn('route', 'string');
$table->addColumn('locale', 'string');
$table->addColumn('attribute', 'string');
$table->addColumn('localized_value', 'string');
$table->addColumn('original_value', 'string');
$table->setPrimaryKey(array('id'));
$table->addUniqueIndex(array('route', 'locale', 'attribute'));
$table->addIndex(array('localized_value'));
// this is much more selective than the unique index
$this->_sm->createTable($table);
$tableFetched = $this->_sm->listTableDetails("diffbug_routing_translations");
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$diff = $comparator->diffTable($tableFetched, $table);
$this->assertFalse($diff, "no changes expected.");
}
public function assertCreatedSchemaNeedsNoUpdates($classes)
{
$classMetadata = array();
foreach ($classes as $class) {
$classMetadata[] = $this->_em->getClassMetadata($class);
}
try {
$this->schemaTool->createSchema($classMetadata);
} catch (\Exception $e) {
// was already created
}
$sm = $this->_em->getConnection()->getSchemaManager();
$fromSchema = $sm->createSchema();
$toSchema = $this->schemaTool->getSchemaFromMetadata($classMetadata);
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($fromSchema, $toSchema);
$sql = $schemaDiff->toSql($this->_em->getConnection()->getDatabasePlatform());
$sql = array_filter($sql, function ($sql) {
return strpos($sql, 'DROP') === false;
});
$this->assertEquals(0, count($sql), "SQL: " . implode(PHP_EOL, $sql));
}
public function getDatabaseDiff()
{
$configuration = $this->getMigrationConfiguration();
$up = NULL;
$down = NULL;
$connection = $this->entityManager->getConnection();
$platform = $connection->getDatabasePlatform();
$metadata = $this->entityManager->getMetadataFactory()->getAllMetadata();
if (empty($metadata)) {
return 'No mapping information to process.';
}
$tool = new \Doctrine\ORM\Tools\SchemaTool($this->entityManager);
$fromSchema = $connection->getSchemaManager()->createSchema();
$toSchema = $tool->getSchemaFromMetadata($metadata);
$up = $this->buildCodeFromSql($configuration, $fromSchema->getMigrateToSql($toSchema, $platform));
$down = $this->buildCodeFromSql($configuration, $fromSchema->getMigrateFromSql($toSchema, $platform));
if (!$up && !$down) {
return 'No changes detected in your mapping information.';
}
$comparator = new \Doctrine\DBAL\Schema\Comparator();
return $comparator->compare($fromSchema, $toSchema);
}
public static function refreshCoreXMLSchema($tables)
{
$xml = simplexml_load_file(DIR_BASE_CORE . '/config/db.xml');
$output = new \SimpleXMLElement('<schema xmlns="http://www.concrete5.org/doctrine-xml/0.5" />');
$th = \Core::make('helper/text');
foreach ($xml->table as $t) {
$name = (string) $t['name'];
if (in_array($name, $tables)) {
$th->appendXML($output, $t);
}
}
$db = \Database::get();
$parser = static::getSchemaParser($output);
$parser->setIgnoreExistingTables(false);
$toSchema = $parser->parse($db);
$fromSchema = $db->getSchemaManager()->createSchema();
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($fromSchema, $toSchema);
$saveQueries = $schemaDiff->toSaveSql($db->getDatabasePlatform());
foreach ($saveQueries as $query) {
$db->query($query);
}
}
/**
* @group DDC-887
*/
public function testUpdateSchemaWithForeignKeyRenaming()
{
if (!$this->_sm->getDatabasePlatform()->supportsForeignKeyConstraints()) {
$this->markTestSkipped('This test is only supported on platforms that have foreign keys.');
}
$table = new \Doctrine\DBAL\Schema\Table('test_fk_base');
$table->addColumn('id', 'integer');
$table->setPrimaryKey(array('id'));
$tableFK = new \Doctrine\DBAL\Schema\Table('test_fk_rename');
$tableFK->setSchemaConfig($this->_sm->createSchemaConfig());
$tableFK->addColumn('id', 'integer');
$tableFK->addColumn('fk_id', 'integer');
$tableFK->setPrimaryKey(array('id'));
$tableFK->addIndex(array('fk_id'), 'fk_idx');
$tableFK->addForeignKeyConstraint('test_fk_base', array('fk_id'), array('id'));
$this->_sm->createTable($table);
$this->_sm->createTable($tableFK);
$tableFKNew = new \Doctrine\DBAL\Schema\Table('test_fk_rename');
$tableFKNew->setSchemaConfig($this->_sm->createSchemaConfig());
$tableFKNew->addColumn('id', 'integer');
$tableFKNew->addColumn('rename_fk_id', 'integer');
$tableFKNew->setPrimaryKey(array('id'));
$tableFKNew->addIndex(array('rename_fk_id'), 'fk_idx');
$tableFKNew->addForeignKeyConstraint('test_fk_base', array('rename_fk_id'), array('id'));
$c = new \Doctrine\DBAL\Schema\Comparator();
$tableDiff = $c->diffTable($tableFK, $tableFKNew);
$this->_sm->alterTable($tableDiff);
}
public static function upgradeDatabase($pkg)
{
$dbm = $pkg->getDatabaseStructureManager();
$pkg->destroyProxyClasses();
if ($dbm->hasEntities()) {
$dbm->generateProxyClasses();
//$dbm->dropObsoleteDatabaseTables(camelcase($this->getPackageHandle()));
$dbm->installDatabase();
}
if (file_exists($pkg->getPackagePath() . '/' . FILENAME_PACKAGE_DB)) {
// Legacy db.xml
// currently this is just done from xml
$db = Database::get();
$db->beginTransaction();
$parser = Schema::getSchemaParser(simplexml_load_file($pkg->getPackagePath() . '/' . FILENAME_PACKAGE_DB));
$parser->setIgnoreExistingTables(false);
$toSchema = $parser->parse($db);
$fromSchema = $db->getSchemaManager()->createSchema();
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($fromSchema, $toSchema);
$saveQueries = $schemaDiff->toSaveSql($db->getDatabasePlatform());
foreach ($saveQueries as $query) {
$db->query($query);
}
$db->commit();
}
}
/**
* @group DDC-2843
*/
public function testBooleanDefault()
{
$table = new \Doctrine\DBAL\Schema\Table('ddc2843_bools');
$table->addColumn('id', 'integer');
$table->addColumn('checked', 'boolean', array('default' => false));
$this->_sm->createTable($table);
$databaseTable = $this->_sm->listTableDetails($table->getName());
$c = new \Doctrine\DBAL\Schema\Comparator();
$diff = $c->diffTable($table, $databaseTable);
$this->assertFalse($diff);
}
public function delete()
{
$at = $this->getAttributeType();
$at->controller->setAttributeKey($this);
$at->controller->deleteKey();
$cnt = $this->getController();
$db = Loader::db();
$db->Execute('delete from AttributeKeys where akID = ?', array($this->getAttributeKeyID()));
$db->Execute('delete from AttributeSetKeys where akID = ?', array($this->getAttributeKeyID()));
if ($this->getIndexedSearchTable()) {
$definition = $cnt->getSearchIndexFieldDefinition();
$prefix = $this->akHandle;
$sm = $db->getSchemaManager();
$platform = $db->getDatabasePlatform();
$fromTable = $sm->listTableDetails($this->getIndexedSearchTable());
$toTable = $sm->listTableDetails($this->getIndexedSearchTable());
$dropColumns = array();
if (isset($definition['type'])) {
$dropColumns[] = 'ak_' . $prefix;
} else {
if (is_array($definition)) {
foreach ($definition as $name => $column) {
$dropColumns[] = 'ak_' . $prefix . '_' . $name;
}
}
}
$comparator = new \Doctrine\DBAL\Schema\Comparator();
foreach ($dropColumns as $dc) {
$toTable->dropColumn($dc);
}
$diff = $comparator->diffTable($fromTable, $toTable);
if ($diff) {
$sql = $platform->getAlterTableSQL($diff);
foreach ($sql as $q) {
$db->exec($q);
}
}
}
}
public function updateTable($schema, $saveMode = true)
{
$appId = $this->target_app->app_id;
$db = app::get($appId)->database();
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$real_table_name = $this->real_table_name();
//echo $real_table_name.PHP_EOL;
$toSchema = $this->createTableSchema();
// 如果存在原始表, 则通过原始表建立schema对象
if ($db->getSchemaManager()->tablesExist($real_table_name)) {
$fromSchema = new \Doctrine\DBAL\Schema\Schema([$db->getSchemaManager()->listTableDetails($real_table_name)], [], $db->getSchemaManager()->createSchemaConfig());
} else {
$fromSchema = new \Doctrine\DBAL\Schema\Schema();
}
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($fromSchema, $toSchema);
// 非安全模式
if (!$saveMode) {
$queries = $schemaDiff->toSql($db->getDatabasePlatform());
} else {
$queries = $schemaDiff->toSaveSql($db->getDatabasePlatform());
// var_dump($queries);
if ($queries) {
reset($schemaDiff->changedTables);
$changeTable = current($schemaDiff->changedTables);
$changeTable->removedColumns = [];
$queries = $schemaDiff->toSaveSql($db->getDatabasePlatform());
}
}
foreach ($queries as $sql) {
logger::info($sql);
$db->exec($sql);
}
}
public function testDiffListTableColumns()
{
$offlineTable = $this->createTableSchema();
$onlineTable = $this->sm->listTableDetails('points');
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$diff = $comparator->diffTable($offlineTable, $onlineTable);
$this->assertFalse($diff, 'No differences should be detected with the offline vs online schema.');
}
/**
* @group DBAL-365
*/
public function testDroppingConstraintsBeforeColumns()
{
$newTable = new Table('mytable');
$newTable->addColumn('id', 'integer');
$newTable->setPrimaryKey(array('id'));
$oldTable = clone $newTable;
$oldTable->addColumn('parent_id', 'integer');
$oldTable->addUnnamedForeignKeyConstraint('mytable', array('parent_id'), array('id'));
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$tableDiff = $comparator->diffTable($oldTable, $newTable);
$sql = $this->_platform->getAlterTableSQL($tableDiff);
$expectedSql = array('ALTER TABLE mytable DROP CONSTRAINT FK_6B2BD609727ACA70', 'DROP INDEX IDX_6B2BD609727ACA70', 'ALTER TABLE mytable DROP parent_id');
$this->assertEquals($expectedSql, $sql);
}
/**
* Installs the database tables for all entity classes contained within the
* $metadatas array. Returns true if new tables were created or existing
* ones altered. Otherwise this will return false if there were no database
* migrations needed.
*
* @param array $metadatas
*
* @return bool
*/
public function installDatabaseFor(array $metadatas)
{
if (count($metadatas) > 0) {
// We need to create the SchemaDiff manually here because we want
// to avoid calling the execution for two separate SchemaDiff
// objects (one for missing tables and one for new ones).
// Also, while $tool->createSchema($missingEntities) works great
// for new tables, $tool->updateSchema($updateEntities) would
// actually delete all the DB tables that the DB contains and are
// not part of the entity tables passed to the function. Therefore,
// we do this manually here.
$em = $this->getEntityManager();
$conn = $em->getConnection();
$sm = $conn->getSchemaManager();
$cmf = $em->getMetadataFactory();
$tool = new \Doctrine\ORM\Tools\SchemaTool($em);
$comparator = new \Doctrine\DBAL\Schema\Comparator();
// NOTE: $newSchema != $toSchema because $toSchema would actually
// contain each and every table in the database. We'll only need
// to traverse the $newSchema for the purposes of the desired
// functionality but we also need $fromSchema to check whether
// the table already exists and also to get the current schema
// for that table to figure out the changes to the new table.
$fromSchema = $sm->createSchema();
$newSchema = $tool->getSchemaFromMetadata($metadatas);
$newTables = array();
$changedTables = array();
foreach ($newSchema->getTables() as $newTable) {
// Check if the table already exists
if ($fromSchema->hasTable($newTable->getName())) {
$diff = $comparator->diffTable($fromSchema->getTable($newTable->getName()), $newTable);
if ($diff) {
$changedTables[] = $diff;
}
} else {
$newTables[] = $newTable;
}
}
if (count($newTables) > 0 || count($changedTables) > 0) {
// If we have new or changed tables (or both), we'll gather
// these DB changes into a SchemaDiff object and get all the
// necessary DB migration queries for that diff object.
// Finally, those queries are executed against the DB.
$schemaDiff = new SchemaDiff($newTables, $changedTables);
$platform = $conn->getDatabasePlatform();
$migrateSql = $schemaDiff->toSql($platform);
foreach ($migrateSql as $sql) {
$conn->executeQuery($sql);
}
return true;
}
}
return false;
}
/**
* remove all tables defined in a database structure xml file
*
* @param string $file the xml file describing the tables
*/
public function removeDBStructure($file)
{
$schemaReader = new MDB2SchemaReader(\OC_Config::getObject(), $this->conn->getDatabasePlatform());
$fromSchema = $schemaReader->loadSchemaFromFile($file);
$toSchema = clone $fromSchema;
/** @var $table \Doctrine\DBAL\Schema\Table */
foreach ($toSchema->getTables() as $table) {
$toSchema->dropTable($table->getName());
}
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($fromSchema, $toSchema);
$this->executeSchemaChange($schemaDiff);
}
public function testaInsertSerialize()
{
$accountShopModel = app::get('sysshop')->model('account');
$data = array('login_account' => 'xinxin', 'createtime' => '1432187042', 'modified_time' => '1432187042', 'login_password' => '$2y$10$kAuKY2zfMiseYU9s6ejS.OSpmcZswlkP0dy6hFE');
$sellerId = $accountShopModel->insert($data);
var_dump($sellerId);
exit;
echo 99;
$dbtable = new base_application_dbtable();
$schema = $dbtable->detect('base', 'apps')->getCreateTableSql();
var_dump($schema);
exit;
/*
$db = db::connection();
$db1 = db::connection('test');
$platform = $db->getDatabasePlatform();
$platform1 = $db1->getDatabasePlatform();
$schema = $db->getSchemaManager()->createSchema();
$schema1 = $db1->getSchemaManager()->createSchema();
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($schema, $schema1);
var_dump($schemaDiff->toSql($platform));
exit;
var_dump($schema->toSql($platform));
exit;
*/
$dbtable = new base_application_dbtable();
$schema = $dbtable->detect('base', 'apps')->getCreateTableSql();
var_dump($schema);
exit;
// var_dump($dbinfo->toSql(db::connection()->getDatabasePlatform()));
//----
$platform = db::connection()->getDatabasePlatform();
$db1 = db::connection('test');
$platform1 = $db1->getDatabasePlatform();
$schemaManager = $db1->getSchemaManager();
// var_dump($db1->getSchemaManager()->createSchemaConfig());exit;
$schema1 = new \Doctrine\DBAL\Schema\Schema([$schemaManager->listTableDetails('base_apps')], [], $db1->getSchemaManager()->createSchemaConfig());
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($schema1, $schema);
var_dump($schemaDiff->toSql($platform));
exit;
// var_dump($dbinfo);exit;
exit;
$define = ['precision' => 10, 'aa' => 2, 'fixed' => 'dddd', 'fff'];
// var_dump(array_merge($a, $b));
exit;
var_dump(array_intersect_key($a, array_flip(['precision', 'scale', 'fixed'])));
exit;
$app_id = 'base';
$db = kernel::database();
//$rows = $db->select(sprintf("show tables like '%s'", $app_id.'\_%'));
$rows = $db->select("show tables like 'base%'", $app_id . '\\_%');
var_dump($rows);
exit;
$app = 'base';
$db = app::get('base')->database();
$rows = $db->executeQuery('SHOW TABLE STATUS like ' . $db->quote($app . '\\_%'))->fetchAll();
var_dump($rows);
exit;
$app = 'base';
$db = app::get('base')->database();
//$tables = $db->executeQuery('SHOW TABLE STATUS like '. $db->quote($app.'%'))->fetchAll();
$tables = $db->executeQuery('SHOW TABLE STATUS like ?', [$app . '%'], [\PDO::PARAM_STR])->fetchAll();
var_dump($tables);
exit;
$time = time();
$db = app::get('base')->database();
$count = $db->executeQuery('SELECT count(*) FROM base_kvstore WHERE ttl>0 AND (dateline+ttl)<?', [$db->quote($time, \PDO::PARAM_INT)])->fetchColumn();
var_dump($count);
exit;
$qb = app::get('sysstat')->database()->createQueryBuilder();
$catId = 'asdf';
echo $qb->getConnection()->delete('syscategory_cat', ['cat_id' => $catId], [\PDO::PARAM_INT]);
exit;
$file = 'wapmall/index.html';
$slots['wapmall/index.html'] = array(120, 121);
$model = app::get('site')->model('widgets_instance');
// $rows = $model->database()->executeQuery('select * from site_widgets_instance where widgets_id not in(?) and core_file=?', [$slots[$file], $file], [\Doctrine\DBAL\Connection::PARAM_INT_ARRAY, \PDO::PARAM_STR])->fetchAll();
echo $model->database()->delete('site_widgets_instance', ['core_file' => $file]);
exit;
var_dump($rows);
exit;
$sTheme = 'a';
$flag = app::get('site')->database()->executeUpdate('delete from site_widgets_instance where core_file like ?', [$sTheme . '%']);
var_dump($flag);
exit;
var_dump(app::get('image')->database()->executeUpdate('update image_image SET last_modified = last_modified + 1'));
exit;
$tids = [10];
$status = 'WAIT_SELLER_SEND_GOODS';
$db = app::get('systrade')->database();
return $db->executeQuery('SELECT count(*) as ready_send_trade ,O.shop_id as shop_id ,sum(O.payment) as ready_send_fee FROM
systrade_trade as O where O.tid in (?) and O.status=? group by shop_id', [$tids, $status], [\Doctrine\DBAL\Connection::PARAM_INT_ARRAY])->fetchAll();
exit;
$db = app::get('systrade')->database();
var_dump(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY);
$rows = $db->executeQuery('select * from sysitem_item where item_id in (?)', [[29]], [\Doctrine\DBAL\Connection::PARAM_INT_ARRAY])->fetchAll();
var_dump($rows);
exit;
$qb = app::get('sysstat')->database()->createQueryBuilder();
$model = app::get('sysshop')->model('shop_info');
$data = ['info_id' => 1, 'company_name' => 'kkajsdf', 'license_num' => 3334, 'bank_name' => 'kkajsdf'];
var_dump($model->save($data));
exit;
$theme = 'luckymall';
$qb = app::get('site')->database()->createQueryBuilder();
$aWidget['widgets'] = $qb->select('*')->from('site_widgets_instance')->where($qb->expr()->like('core_file', $qb->createPositionalParameter($theme . '%')))->execute()->fetchAll();
var_dump($aWidget);
exit;
$aWidget['widgets'] = app::get('site')->model('widgets_instance')->select()->where("core_file LIKE '" . $theme . "/%'")->instance()->fetch_all();
$db = app::get('sysstat')->database();
$rows = $db->executeQuery('select already_send_fee as order_amount,already_send_trade as order_nums,createtime as mydate from sysstat_trade_statics where createtime>=? and createtime<? group by createtime', [$to, $from], [\PDO::PARAM_INT, \PDO::PARAM_INT])->fetchAll();
var_dump($rows);
exit;
$rows = $db->select('select already_send_fee as order_amount,already_send_trade as order_nums,createtime as mydate from sysstat_trade_statics where createtime>=' . intval($to) . ' and createtime<' . intval($from) . ' group by createtime');
$data = app::get('systrade')->database()->executeQuery('SELECT count(*) as num,result FROM `sysrate_traderate` WHERE `shop_id`=? and created_time >=? and created_time < ? group by result', [$shopId, $startTime, $endTime])->fetchAll();
var_dump($data);
exit;
$data = app::get('systrade')->database()->executeQuery('SELECT count(*) as num,result FROM `sysrate_traderate` WHERE `shop_id`=? and created_time >=? and created_time < ? group by result', [$shopId, $startTime, $endTime])->fetchAll();
exit;
var_dump($itemList);
exit;
app::get('base')->model('apps')->update(['app_name' => ''], ['app_id' => 'toputil']);
exit;
$qb = app::get('site')->database()->createQueryBuilder();
$theme = 'luckymall';
$selectObj = $qb->select('*')->from('site_widgets_instance')->where('core_file=' . $qb->createPositionalParameter($file))->orderBy('widgets_order', 'asc');
$selectObj = app::get('site')->model('widgets_instance')->select()->where('core_file = ?', $file)->order('widgets_order ASC');
exit;
$data = $qb->select('*')->from('site_widgets')->where('theme=' . $qb->createPositionalParameter($theme))->execute()->fetchAll();
var_dump($data);
//$data = app::get('site')->model('widgets')->select()->where('theme = ?', $theme)->instance()->fetch_all();
exit;
$data = app::get('site')->model('widgets')->select()->where('theme = ?', $theme)->instance()->fetch_all();
$data = $qb->select('*')->from('site_widgets')->where('app!=\'\'')->orWhere('theme=' . $qb->createPositionalParameter($theme))->execute()->fetchAll();
var_dump($data);
exit;
//$data = app::get('site')->model('widgets')->select()->where('app != ?', '')->or_where('theme = ?', $theme)->instance()->fetch_all();
exit;
$theme = 'luckymall';
$db = app::get('site')->database();
$qb = $db->createQueryBuilder();
kernel::database()->exec('DELETE FROM base_kvstore WHERE `prefix` IN ("cache/template", "cache/theme")');
exit;
$cache_keys = $db->executeQuery('SELECT `prefix`, `key` FROM base_kvstore WHERE `prefix` IN ("cache/template", "cache/theme")')->fetchAll();
var_dump($cache_keys);
exit;
$rows = $qb->select('*')->from('site_themes_tmpl')->where('theme=' . $qb->createPositionalParameter($theme))->execute()->fetchAll();
var_dump($rows);
exit;
$qb = app::get('site')->database()->createQueryBuilder();
$theme = 'luckymall';
echo $qb->select('tmpl_path')->from('site_themes_tmpl')->where('theme=' . $qb->createPositionalParameter($theme))->execute()->fetchColumn();
exit;
$themeData = $qb->select('*')->from('site_themes')->where('theme=' . $qb->createPositionalParameter($entry))->execute()->fetch();
var_dump($themeData);
exit;
$qb = app::get('site')->database()->createQueryBuilder();
$themeData = app::get('site')->model('themes')->select()->where('theme = ?', $entry)->instance()->fetch_row();
exit;
$rows = $qb->select('*')->from('site_themes')->where('theme=' . $qb->createPositionalParameter($theme))->execute()->fetch();
var_dump($rows);
exit;
return app::get('site')->model('themes')->select()->where('theme = ?', $theme)->instance()->fetch_row();
exit;
$menu = $qb->select('*')->from('site_menus')->where('id=' . $qb->createPositionalParameter($id))->execute()->fetch();
var_dump($menu);
exit;
exit;
$menu = app::get('site')->model('menus')->select()->where('id = ?', $id)->instance()->fetch_row();
$app_id = 'topcx';
$content_path = 'view';
$qb = app::get('site')->database()->createQueryBuilder();
var_dump($qb->select('app_id')->from('base_apps')->where('app_id like "t%"')->execute()->fetchColumn());
exit;
var_dump($qb->select('id')->from('site_explorers')->where('app=' . $qb->createPositionalParameter($app_id))->andWhere('path=' . $qb->createPositionalParameter(str_replace('-', '/', $content_path)))->execute()->fetchColumn());
exit;
return app::get('site')->model('explorers')->select()->columns('id')->where('app = ?', $app_id)->where('path = ?', str_replace('-', '/', $content_path))->instance()->fetch_one() ? true : false;
exit;
$db = app::get('base')->database();
$aStatus = $db->executeQuery('show status')->fetchAll();
var_dump($aStatus);
exit;
$rows = app::get('base')->database()->executeQuery('show tables')->fetchAll();
var_dump($rows);
exit;
$qb = app::get('base')->database()->createQueryBuilder();
$data = $qb->select('cat_id,count(cat_id) as count')->from('sysitem_item')->where(app::get('sysitem')->model('item')->_filter($filter))->groupBy('cat_id')->execute()->fetchAll();
var_dump($qb->getSql());
exit;
var_dump($data);
exit;
/*
$sfilter = 'select cat_id,count(cat_id) as count from sysitem_item WHERE ';
$sfilter .= app::get('sysitem')->model('item')->_filter($filter);
$sfilter .= ' group by cat_id';
$data = app::get('sysitem')->model('item')->db->select($sfilter);
*/
exit;
$qb = app::get('base')->database()->createQueryBuilder();
$qb->insert('ectools_analysis')->values(array('service' => $qb->createPositionalParameter('aaa'), '`interval`' => $qb->createPositionalParameter('xxddd')))->execute();
$db = app::get('sysitem')->database();
$params = ['num' => 2, 'item_id' => 'success'];
var_dump($db->executeUpdate('UPDATE sysitem_item_count SET sold_quantity = sold_quantity + ?? WHERE item_id = ?', [$params['num'], $params['item_id']]));
var_dump(app::get('systrade')->database()->createQueryBuilder()->select('count(1) as saleTimes,sum(payment) as salePrice ,shop_id as shopname')->from('systrade_trade')->where($this->_filter($filter))->execute()->fetch());
exit;
$whereSql = 1;
$qb = app::get('sysitem')->database()->createQueryBuilder();
echo $qb->select('count(*) as _count')->from('sysitem_item', 'I')->leftJoin('I', 'sysitem_item_status', 'S', 'I.item_id=S.item_id')->where($whereSql)->execute()->fetchColumn();
exit;
$limit = 2;
if ($params['filter']['image_id'] == '_ALL_' || $params['filter']['image_id'] == '_ALL_') {
unset($params['filter']['image_id']);
}
$qb = app::get('image')->database()->createQueryBuilder();
$rows = $qb->select('image_id')->from('image_image')->where(app::get('image')->model('image')->_filter($params['filter']))->andWhere('last_modified<=' . $qb->createPositionalParameter($params['queue_time']))->setMaxResults($limit)->orderBy('last_modified', 'desc')->execute()->fetchAll();
var_dump($qb->getSql());
exit;
var_dump($r = app::get('image')->database()->executeQuery('select count(*) as c from image_image')->fetchColumn());
exit;
var_dump(app::get('ectools')->database()->executeQuery('SELECT DISTINCT bank, account FROM ectools_payments where status="succ"')->fetchAll());
exit;
$params['service'] = 'aaa';
$params['target'] = 'kk';
$params['time_to'] = 3333;
$params['time_from'] = 3333;
// $params[''] ='aaa';
$qb = app::get('ectools')->database()->createQueryBuilder();
if ($analysis_id = $qb->select('id')->from('ectools_analysis')->where('service=' . $qb->createPositionalParameter($params['service']))->execute()->fetchColumn()) {
$qb = app::get('ectools')->database()->createQueryBuilder();
$qb->select('target,flag,value,time')->from('ectools_analysis_logs')->where('analysis_id=' . $qb->createPositionalParameter($analysis_id))->andWhere('target =' . $qb->createPositionalParameter($params['target']))->andWhere('time>=' . $qb->createPositionalParameter(strtotime(sprintf('%s 00:00:00', $params['time_from']))))->andWhere('time<=' . $qb->createPositionalParameter(strtotime(sprintf('%s 23:59:59', $params['time_to']))));
if (isset($this->_params['type'])) {
$qb->andWhere('type = ' . $qb->createPositionalParameter($params['type']));
}
$rows = $qb->execute()->fetchAll();
} else {
return array('categories' => array(), 'data' => array());
}
var_dump($qb->getSql());
var_dump($rows);
exit;
/*
$qb = app::get('base')->database()->createQueryBuilder();
$qb->insert('ectools_analysis')
->values(array(
'service' => $qb->createPositionalParameter('aaa'),
'`interval`' => $qb->createPositionalParameter('xxddd')
))->execute();
;
exit;
*/
//---
$this->_service = 'aaa';
$this->_params = ['type' => 'type', 'target' => 'target'];
$qb = app::get('base')->database()->createQueryBuilder();
if ($analysis_id = $qb->select('id')->from('ectools_analysis')->where('service=' . $qb->createPositionalParameter($this->_service))->execute()->fetchColumn()) {
$qb = app::get('base')->database()->createQueryBuilder();
$qb->select('target, sum(value)')->from('ectools_analysis_logs')->where('analysis_id = ' . $qb->createPositionalParameter($analysis_id))->andWhere('flag = 0')->groupBy('target');
if (isset($this->_params['type'])) {
$qb->andWhere('type = ' . $qb->createPositionalParameter($this->_params['type']));
}
if (isset($this->_params['target'])) {
$qb->andWhere('target = ' . $qb->createPositionalParameter($this->_params['target']));
}
if (isset($this->_params['time_from'])) {
$qb->andWhere('time_from = ' . $qb->createPositionalParameter(strtotime(sprintf('%s 00:00:00', $this->_params['time_from']))));
}
if (isset($this->_params['time_to'])) {
$qb->andWhere('time_to = ' . $qb->createPositionalParameter(strtotime(sprintf('%s 23:59:59', $this->_params['time_to']))));
}
$rows = $qb->execute()->fetchAll();
}
var_dump($rows);
echo $qb->getSql();
exit;
/*
$analysis_id = app::get('ectools')->model('analysis')->select()->columns('id')->where('service = ?', $this->_service)->instance()->fetch_one();
$obj = app::get('ectools')->model('analysis_logs')->select()->columns('target, sum(value) AS value')->where('analysis_id = ?', $analysis_id);
if(isset($this->_params['type'])) $obj->where('type = ?', $this->_params['type']);
if(isset($this->_params['target'])) $obj->where('target = ?', $this->_params['target']);
if(isset($this->_params['time_from'])) $obj->where('time >= ?', strtotime(sprintf('%s 00:00:00', $this->_params['time_from'])));
if(isset($this->_params['time_to'])) $obj->where('time <= ?', strtotime(sprintf('%s 23:59:59', $this->_params['time_to'])));
$rows = $obj->where('flag = ?', 0)->group(array('target'))->instance()->fetch_all();
foreach($rows AS $row){
$tmp[$row['target']] = $row['value'];
}
foreach($this->logs_options AS $target=>$option){
$detail[$option['name']]['value'] = ($tmp[$target]) ? $tmp[$target] : 0;
$detail[$option['name']]['memo'] = $this->logs_options[$target]['memo'];
$detail[$option['name']]['icon'] = $this->logs_options[$target]['icon'];
}
*/
$this->_service = 'aa';
$qb = app::get('base')->database()->createQueryBuilder();
if ($analysis_id = $qb->select('id')->from('ectools_analysis')->where($qb->expr()->eq('service', $qb->createPositionalParameter($this->_service)))->execute()->fetchColumn()) {
}
var_dump($qb->getSql());
var_dump($analysis_id);
exit;
//$analysis_id = app::get('base')->model('apps')->select()->columns(', app_name')->where('app_id = ?', 'base')->instance()->fetch_one();
$model = app::get('ectools')->model('analysis');
$model->select('id')->where($model->expr()->eq('service', $model->quote()));
$qb = app::get('ectools')->database();
$qb->select('id')->from('ectools_analysis')->where($qb->expr);
// $analysis_id = $model->select('id')->where();
exit;
/*
$sTheme = 'luckymall';
$db = app::get($app)->database();
$data = $db->executeQuery('select count("widgets_id") as num from site_widgets_instance where core_file like ?', [$sTheme.'%'])->fetchColumn();
var_dump($data);
exit;
*/
$app = 'base';
$db = app::get($app)->database();
$sm = $db->getSchemaManager();
var_dump($sm->listTableNames());
exit;
$rows = $db->executeQuery('SHOW TABLE STATUS like ' . $db->quote($app . '%'))->fetchAll();
var_dump($rows);
exit;
$db = kernel::database();
$rows = $db->select('SHOW TABLE STATUS like "' . $app . '%"');
var_dump(app::get('base')->database()->getDatabasePlatform()->getName());
exit;
var_dump(app::get('base')->database()->getDriver()->getName());
exit;
var_dump($sm->listTableDetails('user'));
exit;
var_dump($rows);
exit;
$rows = kernel::database()->select('select app_id from base_apps where status != "uninstalled"');
$db = app::get('base')->database();
$rows = app::get('base')->database()->executeQuery('select content_name,content_path from base_app_content where content_type=? and disabled!=?', ['service', 1])->fetchAll();
var_dump($rows);
exit;
$sql = 'select content_name,content_path from base_app_content where content_type="service" and disabled!=1';
if ($filter) {
$sql .= ' and content_name like ' . $db->quote($filter);
}
/*
$db = kernel::database();
$sql = 'select content_name,content_path from base_app_content where content_type="service" and disabled!=1';
if($filter){
$sql.=' and content_name like '.$db->quote($filter);
}
*/
if ($count) {
if ($joinTable && $obj_id) {
if (!$where) {
$sql = "select t.tag_id,t.tag_name,t.tag_type,count(o.{$obj_id}) as rel_count,{$obj_id} as ss,t.is_system\n FROM base_tag t\n LEFT JOIN base_tag_rel r ON r.tag_id=t.tag_id\n LEFT JOIN {$joinTable} o ON r.rel_id=o.{$obj_id} and o.disabled!=1\n where tag_type='{$type}' group by t.tag_id";
} else {
$sql = "select {$obj_id} as trel_id\n FROM base_tag_rel r\n LEFT JOIN {$joinTable} o ON r.rel_id=o.{$obj_id} and o.disabled!=1\n where r.tag_id = {$where}";
}
} else {
$sql = "select t.tag_id,t.tag_name,t.tag_type,count(r.rel_id) as rel_count,t.is_system FROM base_tag t LEFT JOIN base_tag_rel r ON r.tag_id=t.tag_id where tag_type='{$type}' group by t.tag_id";
}
} else {
$sql = "select * FROM base_tag where tag_type='{$type}'";
}
/*
if($count){
if($joinTable && $obj_id){
if(!$where){
$sql = "select t.tag_id,t.tag_name,t.tag_type,count(o.{$obj_id}) as rel_count,$obj_id as ss,t.is_system
FROM base_tag t
LEFT JOIN base_tag_rel r ON r.tag_id=t.tag_id
LEFT JOIN {$joinTable} o ON r.rel_id=o.{$obj_id} and o.disabled!=1
where tag_type='$type' group by t.tag_id";
}else{
$sql = "select $obj_id as trel_id
FROM base_tag_rel r
LEFT JOIN {$joinTable} o ON r.rel_id=o.{$obj_id} and o.disabled!=1
where r.tag_id = {$where}";
}
}else{
$sql = "select t.tag_id,t.tag_name,t.tag_type,count(r.rel_id) as rel_count,t.is_system FROM base_tag t LEFT JOIN base_tag_rel r ON r.tag_id=t.tag_id where tag_type='$type' group by t.tag_id";
}
}else{
$sql = "select * FROM base_tag where tag_type='$type'";
}
*/
exit;
$rows = $qb->select('action_id')->from('base_lnk_acts')->where($qb->expr()->in('tag_id', $tag))->execute()->fetchAll();
$rows = $this->db->select('select action_id from base_lnk_acts where role_id in (' . implode(',', $role_id) . ')');
exit;
$tag = [1000, 2000];
$qb = app::get('desktop')->database()->createQueryBuilder();
$rows = $qb->select('rel_id')->from('desktop_tag_rel')->where($qb->expr()->in('tag_id', $tag))->execute()->fetchAll();
var_dump($rows);
exit;
echo $qb->getSql();
exit;
var_dump($rows);
exit;
$len = 10;
$startid = 0;
$app = 'base';
$model = 'kvstore';
$tname = "{$app}_{$model}";
$qb = app::get($app)->database()->createQueryBuilder()->select('*')->from($tname);
if (strtolower($app) == 'base' && strtolower($model) == 'kvstore') {
$qb->where($qb->expr()->notLike('prefix', $qb->getConnection()->quote('cache/%')));
}
$qb->setFirstResult($startid)->setMaxResults($len);
$aData = $qb->execute()->fetchAll();
var_dump($aData);
exit;
/*
$limit = sprintf( 'LIMIT %s,%s', $startid, $len );
if( strtolower($app)=='base' && strtolower($model)=='kvstore' )
$where = ' WHERE prefix NOT LIKE "cache/%"';
$sql = "SELECT * FROM $tname $where $limit";
$aData = $this->_db->select( $sql );
*/
$tables = app::get('base')->database()->executeQuery('SELECT app_id FROM base_apps WHERE status=?', ['active'])->fetchAll();
var_dump($tables);
exit;
$pri_settings = app::get('base')->database()->executeQuery('select app, `key`, value from base_setting')->fetchAll();
var_dump($pri_settings);
exit;
/*
$db->select($s='select image_id,url,s_url,m_url,l_url,last_modified,width,height from image_image where image_id in(\''.
implode("','",array_keys($img)).'\')');
exit;
*/
$img = array('ff6485392d25f0499eb08941d22ceeab' => 2, 'asdfsadf' => 4);
$qb = app::get('image')->database()->createQueryBuilder();
$rows = $qb->select('image_id,url,s_url,m_url,l_url,last_modified,width,height')->from('image_image')->where($qb->expr()->in('image_id', array_map(function ($image_id) use($qb) {
return $qb->getConnection()->quote($image_id);
}, array_keys($img))))->execute()->fetchAll();
var_dump($rows);
var_dump($qb->getSql());
exit;
/*
foreach($db->select($s='select image_id,url,s_url,m_url,l_url,last_modified,width,height from image_image where image_id in(\''.
implode("','",array_keys($img)).'\')') as $r){
$imglib[$r['image_id']] = $r;
}
*/
exit;
$keywords = array('base', 'ectools');
$qb = app::get('base')->database()->createQueryBuilder()->select('app_id,app_name,description,local_ver,remote_ver')->from('base_apps');
foreach ($keywords as $word) {
$where[] = "app_id like '%{$word}%' or app_name like '%{$word}%' or `description` like '%{$word}%'";
}
$rows = $qb->where(call_user_func_array(array($qb->expr(), 'orX'), $where))->execute()->fetchAll();
var_dump($rows);
exit;
/*
foreach($keywords as $word){
$where[] = "app_id like '%{$word}%' or app_name like '%{$word}%' or `description` like '%{$word}%'";
}
$sql = 'select app_id,app_name,description,local_ver,remote_ver from base_apps where 1 and '.implode(' and ',$where);
$rows = kernel::database()->select($sql);
var_dump($rows);exit;
*/
exit;
$prefix = 'tbdefine';
$key = 'basesyscache_resources';
$rows = app::get('base')->database()->executeQuery('SELECT * FROM `base_kvstore` WHERE `prefix` = ? AND `key` = ?', [$prefix, $key])->fetchAll();
var_dump($rows);
exit;
$rows = kernel::database()->select(sprintf("SELECT * FROM `base_kvstore` WHERE `prefix` = %s AND `key` = %s", kernel::database()->quote($this->prefix), kernel::database()->quote($key)), true);
$db = app::get('base')->database();
//$rows = $db->executeQuery();
$app_id = '\'\';exit;;;;a/dsajfk""';
$count = app::get('base')->database()->executeQuery('select count(*) from base_apps where app_id = ? AND status = "active"', [$app_id])->fetchColumn();
echo $count;
exit;
$rows = app::get('base')->database()->executeQuery('select app_id,app_name from base_apps where status <> "uninstalled"')->fetchAll;
var_dump($rows);
exit;
//$count = $db->count('SELECT count(*) AS count FROM base_kvstore', true);
$count = $db->executeQuery('SELECT count(*) AS count FROM base_kvstore')->fetchColumn();
echo $count;
exit;
$data = array('fullminus_id' => '16', 'fullminus_name' => '99-9', 'canjoin_repeat' => 0, 'join_limit' => 3, 'used_platform' => '0', 'free_postage' => 1, 'condition_value' => '99|9', 'shop_id' => 2, 'start_time' => 1429632000, 'end_time' => 1430150400, 'valid_grade' => '1,2,3', 'fullminus_rel_itemids' => '44,46,123', 'fullminus_desc' => '', 'test1' => 0, 'promotion_tag' => '满减');
$model = app::get('syspromotion')->model('fullminus');
$model->save($data);
exit;
$data = $db->executeQuery('select count("widgets_id") as num from site_widgets_instance where core_file like ?', [$sTheme . '%'])->fetchColumn();
$db = app::get('sysitem')->database();
var_dump($db->quote(3333, \PDO::PARAM_BOOL));
exit;
$qb = $db->createQueryBuilder();
$qb->where($qb->expr()->andX($qb->expr()->eq('a', 'fff'), 'a like ' . $db->quote('kkk%'), ''));
echo $qb->getSql();
exit;
$params = ['num' => 2, 'item_id' => 'success'];
var_dump($db->executeUpdate('UPDATE sysitem_item_count SET sold_quantity = sold_quantity + ? WHERE item_id = ?', [$params['num'], $params['item_id']]));
exit;
$sql = "UPDATE sysitem_item_count SET sold_quantity = sold_quantity + " . intval($params['num']) . " WHERE item_id = " . intval($params['item_id']);
$db = app::get('systrade')->database();
$qb = $db->createQueryBuilder();
$subQb = $db->createQueryBuilder();
$subQb->select('I.item_id')->from('systrade_order', 'O')->leftJoin('O', ' sysitem_item', 'I', 'O.item_id=I.item_id')->where($qb->expr()->andX($qb->expr()->neq('O.status', $db->quote('WAIT_BUYER_PAY'))))->groupBy('I.item_id');
$qb->select('count(*) as _count')->from('(' . $subQb->getSql() . ')', 'dd');
echo $qb->getSql();
exit;
echo $subQb->getSql();
exit;
//$stmt = $subQb->execute();
// var_dump($subQb->)
$conn = db::connection();
$qb = $conn->createQueryBuilder()->select('u.id')->addSelect('p.id')->from('users', 'u')->leftJoin('u', 'phonenumbers', 'u.id = p.user_id');
var_dump($qb->getSql());
/*
$sql = 'SELECT count(*) as _count FROM (SELECT login_account FROM pam_user as M
where '.$this->_filter($filter).' Group By M.user_id) as tb';
*/
$sql = 'SELECT count(*) as _count FROM pam_user where ' . $this->_filter($filter) . ' Group By M.user_id) as tb';
$db = app::get('site')->database();
// var_dump($db->quote('luckymall%'));exit;
$sTheme = 'luckymall';
$data = $db->executeQuery('select count("widgets_id") as num from site_widgets_instance where core_file like ?', [$sTheme . '%'])->fetchColumn();
var_dump($data);
exit;
// $rows = kernel::database()->selectlimit('SELECT `prefix`, `key` FROM base_kvstore WHERE ttl>0 AND (dateline+ttl)<'.$time, $pagesize, $i*$pagesize);
// $rows = kernel::database()->selectlimit('SELECT `prefix`, `key` FROM base_kvstore WHERE ttl>0 AND (dateline+ttl)<'.$time, $pagesize, $i*$pagesize);
$i = 0;
$pagesize = 2;
$rows = app::get('base')->database()->executeQuery('SELECT `prefix`, `key` FROM base_kvstore WHERE ttl>0 AND (dateline+ttl)<? limit ? offset ?', [time(), $pagesize, $pagesize * $i])->fetchAll();
var_dump($rows);
exit;
exit;
$conn = db::connection();
$qb = $conn->createQueryBuilder();
$qb->insert('desktop_menus');
$qb->setValue('display', 'true');
$qb->execute();
exit;
$theme = app::get('site')->model('themes');
$data = ['theme' => 'test', 'config' => array(1, 2, 3, 4, 5, 6, 7)];
var_dump($theme->insert($data));
exit;
app::get('base')->database()->rollback();
exit;
$time = time();
$count = kernel::database()->count('SELECT count(*) FROM base_kvstore WHERE ttl>0 AND (dateline+ttl)<' . $time);
$pagesize = 100;
$page = ceil($count / 100);
$rows = kernel::database()->selectlimit('SELECT `prefix`, `key` FROM base_kvstore WHERE ttl>0 AND (dateline+ttl)<' . $time, $pagesize, $i * $pagesize);
var_dump(app::get('base')->database()->executeQuery('SELECT count(*) FROM base_kvstore WHERE ttl>0 AND (dateline+ttl)<?', [222])->fetchColumn());
exit;
exit;
var_dump($rows);
$app_id = 'topc';
// $row = kernel::database()->selectrow('select status from base_apps where app_id = "'.$app_id.'" AND status IN ("uninstalled", "paused") ');
/*
$conn = db::connection();
$qb = $conn->createQueryBuilder();
$row = $qb->select('status')
->from('base_apps')
->where($qb->expr()->andX(
$qb->expr()->eq('app_id', $qb->createPositionalParameter($app_id)),
$qb->expr()->in('status', array($conn->quote('uninstalled'), $conn->quote('paused')))
))->execute()->fetch();
//$sql = 'select status from base_apps where app_id = ? AND status IN ("uninstalled", "paused")';
// $sql = sprintf('select status from base_apps where app_id = ? AND status IN ("uninstalled", "paused")');
$row = app::get('base')->model('apps')->getRow('status',
['app_id' => $app_id, array('uninstalled', 'paused')]);
// var_dump($row);exit;
echo '---'.PHP_EOL;
$stmt = $conn->prepare('select status from base_apps where app_id = ? AND status IN ("uninstalled", "paused")');
$stmt->bindValue(1, 'dev');
$stmt->execute();
var_dump($stmt->fetchColumn());
*/
$app_id = 'topc';
$data = ['queue_name' => 'asasss', 'worker' => 'xxxx', 'params' => 'paaa', 'create_time' => time()];
app::get('system')->model('queue_mysql')->insert($data);
$a = app::get('system')->database()->executeUpdate('UPDATE system_queue_mysql force index(PRIMARY) SET owner_thread_id=GREATEST(CONNECTION_ID() ,(@msgID:=id)*0),last_cosume_time=? WHERE queue_name=? and owner_thread_id=-1 order by id LIMIT 1;', [time(), 'asasss']);
var_dump($a);
if ($a) {
var_dump(app::get('system')->database()->executeQuery('select id, worker, params from system_queue_mysql where id=@msgID')->fetch());
}
exit;
echo '==';
// $row = app::get('image')->database()->executeQuery('select count(*) as c from image_image where storage="filesystem" ')->fetch();
$row = app::get('system')->database()->executeQuery('select CONNECTION_ID()')->fetch();
var_dump($row);
exit;
// var_dump($row);exit;
var_dump($row);
var_dump(app::get('topc')->status());
exit;
var_dump($row);
/*
var_dump($conn->executeQuery('select * from base_apps where app_id = ? AND status IN ("uninstalled", "paused")',
['dev'])->fetch());
*/
exit;
/*
$sql = 'select status from base_apps where app_id = ? AND status IN ("uninstalled", "paused")';
$stmt = $conn->prepare($sql);
$stmt->bindValue(1, 'dev');
$stmt->execute();
*/
echo 333;
var_dump($stmt->fetchColumn());
exit;
var_dump($row);
exit;
$conn = db::connection();
var_dump($conn->executeUpdate('set @msgId=-1'));
$stmt = $conn->executeQuery('select @msgID');
var_dump($stmt->fetchAll());
exit;
$quoted = $conn->quoteIdentifier('id');
var_dump($quoted);
$qb = $conn->createQueryBuilder();
$qb->select('show tables like \'%a%\'');
$stmt = $qb->execute();
var_dump($stmt->fetchAll());
exit;
$theme = app::get('site')->model('themes');
$data = ['theme' => 'test', 'config' => array(1, 2, 3, 4, 5, 6, 7)];
//$theme->database()->beginTransaction();
db::connection()->beginTransaction();
var_dump($theme->insert($data));
$theme->database()->commit();
echo '---' . PHP_EOL;
// $theme->database()->rollback();
echo 10;
exit;
$data = ['theme' => 'test', 'config' => array(1, 2, 3)];
var_dump($theme->replace($data, ['theme' => 'test']));
}
/**
* refreshes the BlockType's database schema throws an Exception if error
*
* @return void
*/
public function refresh()
{
$db = DB::get();
$pkgHandle = false;
if ($this->pkgID > 0) {
$pkgHandle = $this->getPackageHandle();
}
$class = static::getBlockTypeMappedClass($this->btHandle, $pkgHandle);
$bta = new $class();
$this->loadFromController($bta);
$em = $db->getEntityManager();
$em->persist($this);
$em->flush();
$env = Environment::get();
$r = $env->getRecord(DIRNAME_BLOCKS . '/' . $this->btHandle . '/' . FILENAME_BLOCK_DB, $this->getPackageHandle());
if ($r->exists()) {
$parser = Schema::getSchemaParser(simplexml_load_file($r->file));
$parser->setIgnoreExistingTables(false);
$toSchema = $parser->parse($db);
$fromSchema = $db->getSchemaManager()->createSchema();
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($fromSchema, $toSchema);
$saveQueries = $schemaDiff->toSaveSql($db->getDatabasePlatform());
foreach ($saveQueries as $query) {
$db->query($query);
}
}
}
/**
* Installs a package's database from an XML file.
*
* @param string $xmlFile Path to the database XML file
*
* @return bool|\stdClass Returns false if the XML file could not be found
*
* @throws \Doctrine\DBAL\ConnectionException
*/
public static function installDB($xmlFile)
{
if (!file_exists($xmlFile)) {
return false;
}
$db = \Database::connection();
$db->beginTransaction();
$parser = Schema::getSchemaParser(simplexml_load_file($xmlFile));
$parser->setIgnoreExistingTables(false);
$toSchema = $parser->parse($db);
$fromSchema = $db->getSchemaManager()->createSchema();
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($fromSchema, $toSchema);
$saveQueries = $schemaDiff->toSaveSql($db->getDatabasePlatform());
foreach ($saveQueries as $query) {
$db->query($query);
}
$db->commit();
$result = new \stdClass();
$result->result = false;
return $result;
}
/**
* Gets the sequence of SQL statements that need to be performed in order
* to bring the given class mappings in-synch with the relational schema.
* If $saveMode is set to true the command is executed in the Database,
* else SQL is returned.
*
* @param array $classes The classes to consider.
* @param boolean $saveMode True for writing to DB, false for SQL string
* @return array The sequence of SQL statements.
*/
public function getUpdateSchemaSql(array $classes, $saveMode = false)
{
$sm = $this->em->getConnection()->getSchemaManager();
$fromSchema = $sm->createSchema();
$toSchema = $this->getSchemaFromMetadata($classes);
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($fromSchema, $toSchema);
if ($saveMode) {
return $schemaDiff->toSaveSql($this->platform);
} else {
return $schemaDiff->toSql($this->platform);
}
}
public function parse_table($formid, $add_fields)
{
$tname = self::tname("form_data_" . $formid);
$conn = Schema::getConnection();
$manager = $conn->getDoctrineSchemaManager();
$exist = $manager->tablesExist($tname);
$schema = new \Doctrine\DBAL\Schema\Schema();
$table = $schema->createTable($tname);
$table->addColumn("id", "bigint", array("unsigned" => true, 'autoincrement' => true, 'comment' => '自动编号'));
$table->addColumn("user_id", "bigint", array("unsigned" => true, 'comment' => '用户ID'));
$table->addColumn("form_id", "bigint", array("unsigned" => true, 'comment' => '外键ID'));
$comment = $add_fields['comment'];
foreach ($add_fields['fields'] as $key => $value) {
$leipiplugins = $value['leipiplugins'];
if ($leipiplugins == 'textarea' || $leipiplugins == 'listctrl') {
$table->addColumn($value['name'], "text", array("notnull" => true, 'comment' => $comment[$value['name']]));
} else {
if ($leipiplugins == 'checkboxs') {
$table->addColumn($value['name'], "smallint", array("unsigned" => true, 'comment' => $comment[$value['name']]));
} else {
$table->addColumn($value['name'], "string", array("length" => 255, 'comment' => $comment[$value['name']]));
}
}
}
$table->addColumn("created_at", "datetime", array("default" => 'CURRENT_TIMESTAMP', 'onUpdate' => 'CURRENT_TIMESTAMP', 'comment' => '创建时间'));
$table->addColumn("update_at", "datetime", array('comment' => '修改时间'));
$table->addIndex(['user_id']);
$table->addIndex(['form_id']);
$table->setPrimaryKey(array("id"));
$form = $manager->listTableDetails(config('database.connections.mysql.prefix') . 'form');
$table->addForeignKeyConstraint($form, ['form_id'], ['id']);
$user = $manager->listTableDetails(config('database.connections.mysql.prefix') . 'user');
$table->addForeignKeyConstraint($user, ['user_id'], ['id']);
if ($exist === false) {
$queries = $schema->toSql($manager->getDatabasePlatform());
$conn = DB::connection();
try {
$conn->beginTransaction();
foreach ($queries as $key => $query) {
$query = str_replace('DATETIME', 'timestamp', $query);
$conn->statement($query);
}
$conn->commit();
} catch (\Illuminate\Database\QueryException $exc) {
Log::error($exc->getMessage());
$conn->rollBack();
return false;
}
return true;
} else {
$platform = $manager->getDatabasePlatform();
$namespaces = array();
if ($platform->supportsSchemas()) {
$namespaces = $manager->listNamespaceNames();
}
$sequences = array();
if ($platform->supportsSequences()) {
$sequences = $manager->listSequences();
}
$s = new \Doctrine\DBAL\Schema\Schema([$manager->listTableDetails($tname)], $sequences, $manager->createSchemaConfig(), $namespaces);
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$schemaDiff = $comparator->compare($s, $schema);
$queries = $schemaDiff->toSql($manager->getDatabasePlatform());
if (!$queries) {
return true;
}
$conn = DB::connection();
try {
$conn->beginTransaction();
foreach ($queries as $key => $query) {
$query = str_replace('DATETIME', 'timestamp', $query);
if (strstr($query, 'CHANGE update_at update_at DATETIME')) {
continue;
}
$conn->statement($query);
}
$conn->commit();
} catch (\Illuminate\Database\QueryException $exc) {
Log::error($exc->getMessage());
$conn->rollBack();
return false;
}
return true;
}
return false;
}
public function testDiffListTableColumns()
{
if ($this->_sm->getDatabasePlatform()->getName() == 'oracle') {
$this->markTestSkipped('Does not work with Oracle, since it cannot detect DateTime, Date and Time differenecs (at the moment).');
}
$offlineTable = $this->createListTableColumns();
$onlineTable = $this->_sm->listTableDetails('list_table_columns');
$comparator = new \Doctrine\DBAL\Schema\Comparator();
$diff = $comparator->diffTable($offlineTable, $onlineTable);
$this->assertFalse($diff, "No differences should be detected with the offline vs online schema.");
}
